[IMP] gamification: track karma change on users

PURPOSE

Allow karma gain tracking enabling notably display of top users based on
weekly / monthly gain in website profile.

SPECIFCIATIONS

Each time a user gains karma a record is created in the gamification karma
tracking model. Scheduled activity runs to consolidate the records into
monthly gain records to avoid having crowdy table and unnecessary noise
in karma gain.

This model is made private and only accessible through some dedicated
compute methods / controllers used in website profile.

In website profile module buttons are added to see users ranking based
on their total karma (like before) but also by last week and last month
gains (using the newly introduced tracking model).

LINKS

Task ID 2003505
PR #34594

1 files changed, 81 insertions, 7 deletions

diff --git a/addons/gamification/models/res_users.py b/addons/gamification/models/res_users.py
index 3e8b6d8e1cd..3dc95de74a1 100644
--- a/addons/gamification/models/res_users.py
+++ b/addons/gamification/models/res_users.py
@@ -3,16 +3,12 @@
 
 from odoo import api, fields, models
 
-import logging
-
-_logger = logging.getLogger(__name__)
-
 
 class Users(models.Model):
     _inherit = 'res.users'
 
-
     karma = fields.Integer('Karma', default=0)
+    karma_tracking_ids = fields.One2many('gamification.karma.tracking', 'user_id', string='Karma Changes', groups="base.group_system")
     badge_ids = fields.One2many('gamification.badge.user', 'user_id', string='Badges', copy=False)
     gold_badge = fields.Integer('Gold badges count', compute="_get_user_badge_level")
     silver_badge = fields.Integer('Silver badges count', compute="_get_user_badge_level")
@@ -46,11 +42,28 @@ class Users(models.Model):
     @api.model_create_multi
     def create(self, values_list):
         res = super(Users, self).create(values_list)
+
+        karma_trackings = []
+        for user in res:
+            if user.karma:
+                karma_trackings.append({'user_id': user.id, 'old_value': 0, 'new_value': user.karma})
+        if karma_trackings:
+            self.env['gamification.karma.tracking'].sudo().create(karma_trackings)
+
         res._recompute_rank()
         return res
 
     def write(self, vals):
+        karma_trackings = []
+        if 'karma' in vals:
+            for user in self:
+                if user.karma != vals['karma']:
+                    karma_trackings.append({'user_id': user.id, 'old_value': user.karma, 'new_value': vals['karma']})
+
         result = super(Users, self).write(vals)
+
+        if karma_trackings:
+            self.env['gamification.karma.tracking'].sudo().create(karma_trackings)
         if 'karma' in vals:
             self._recompute_rank()
         return result
@@ -60,6 +73,68 @@ class Users(models.Model):
             user.karma += karma
         return True
 
+    def _get_tracking_karma_gain_position(self, user_domain, from_date=None, to_date=None):
+        """ Get absolute position in term of gained karma for users. First a ranking
+        of all users is done given a user_domain; then the position of each user
+        belonging to the current record set is extracted.
+
+        Example: in website profile, search users with name containing Norbert. Their
+        positions should not be 1 to 4 (assuming 4 results), but their actual position
+        in the karma gain ranking (with example user_domain being karma > 1,
+        website published True).
+
+        :param user_domain: general domain (i.e. active, karma > 1, website, ...)
+          to compute the absolute position of the current record set
+        :param from_date: compute karma gained after this date (included) or from
+          beginning of time;
+        :param to_date: compute karma gained before this date (included) or until
+          end of time;
+
+        :return list: [{
+            'user_id': user_id (belonging to current record set),
+            'karma_gain_total': integer, karma gained in the given timeframe,
+            'karma_position': integer, ranking position
+        }, {..}] ordered by karma_position desc
+        """
+        if not self:
+            return []
+
+        where_query = self.env['res.users']._where_calc(user_domain)
+        user_from_clause, user_where_clause, where_clause_params = where_query.get_sql()
+
+        params = []
+        if from_date:
+            date_from_condition = 'AND tracking.tracking_date::timestamp >= timestamp %s'
+            params.append(from_date)
+        if to_date:
+            date_to_condition = 'AND tracking.tracking_date::timestamp <= timestamp %s'
+            params.append(to_date)
+        params.append(tuple(self.ids))
+
+        query = """
+SELECT final.user_id, final.karma_gain_total, final.karma_position
+FROM (
+    SELECT intermediate.user_id, intermediate.karma_gain_total, row_number() OVER (ORDER BY intermediate.karma_gain_total DESC) AS karma_position
+    FROM (
+        SELECT "res_users".id as user_id, COALESCE(SUM("tracking".new_value - "tracking".old_value), 0) as karma_gain_total
+        FROM %(user_from_clause)s
+        LEFT JOIN "gamification_karma_tracking" as "tracking"
+        ON "res_users".id = "tracking".user_id AND "res_users"."active" = TRUE
+        WHERE %(user_where_clause)s %(date_from_condition)s %(date_to_condition)s
+        GROUP BY "res_users".id
+        ORDER BY karma_gain_total DESC
+    ) intermediate
+) final
+WHERE final.user_id IN %%s""" % {
+            'user_from_clause': user_from_clause,
+            'user_where_clause': user_where_clause or (not from_date and not to_date and 'TRUE') or '',
+            'date_from_condition': date_from_condition if from_date else '',
+            'date_to_condition': date_to_condition if to_date else ''
+        }
+
+        self.env.cr.execute(query, tuple(where_clause_params + params))
+        return self.env.cr.dictfetchall()
+
     def _rank_changed(self):
         """
             Method that can be called on a batch of users with the same new rank
@@ -68,7 +143,7 @@ class Users(models.Model):
         if template:
             for u in self:
                 if u.rank_id.karma_min > 0:
-                    template.send_mail(u.id, force_send=len(self) == 1, notif_layout='mail.mail_notification_light')
+                    template.send_mail(u.id, force_send=False, notif_layout='mail.mail_notification_light')
 
     def _recompute_rank(self):
         """
@@ -161,7 +236,6 @@ class Users(models.Model):
                     'next_rank_id': lower_rank.id,
                 })
 
-
     def _get_next_rank(self):
         """ For fresh users with 0 karma that don't have a rank_id and next_rank_id yet
         this method returns the first karma rank (by karma ascending). This acts as a