[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

3 files changed, 151 insertions, 7 deletions

diff --git a/addons/gamification/models/__init__.py b/addons/gamification/models/__init__.py
index 926a5af2a07..659fcff70a1 100644
--- a/addons/gamification/models/__init__.py
+++ b/addons/gamification/models/__init__.py
@@ -5,4 +5,5 @@ from . import goal
 from . import challenge
 from . import badge
 from . import gamification_karma_rank
+from . import gamification_karma_tracking
 from . import res_users
diff --git a/addons/gamification/models/gamification_karma_tracking.py b/addons/gamification/models/gamification_karma_tracking.py
new file mode 100644
index 00000000000..f7b184ad24d
--- /dev/null
+++ b/addons/gamification/models/gamification_karma_tracking.py
@@ -0,0 +1,69 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+import calendar
+
+from dateutil.relativedelta import relativedelta
+
+from odoo import api, fields, models
+
+
+class KarmaTracking(models.Model):
+    _name = 'gamification.karma.tracking'
+    _description = 'Track Karma Changes'
+    _rec_name = 'user_id'
+    _order = 'tracking_date DESC'
+
+    user_id = fields.Many2one('res.users', 'User', index=True, readonly=True, required=True, ondelete='cascade')
+    old_value = fields.Integer('Old Karma Value', required=True, readonly=True)
+    new_value = fields.Integer('New Karma Value', required=True, readonly=True)
+    consolidated = fields.Boolean('Consolidated')
+    tracking_date = fields.Date(default=fields.Date.context_today)
+
+    @api.model
+    def _consolidate_last_month(self):
+        """ Consolidate last month. Used by a cron to cleanup tracking records. """
+        previous_month_start = fields.Date.today() + relativedelta(months=-1, day=1)
+        return self._process_consolidate(previous_month_start)
+
+    def _process_consolidate(self, from_date):
+        """ Consolidate trackings into a single record for a given month, starting
+        at a from_date (included). End date is set to last day of current month
+        using a smart calendar.monthrange construction. """
+        end_date = from_date + relativedelta(day=calendar.monthrange(from_date.year, from_date.month)[1])
+        select_query = """
+SELECT user_id,
+(
+    SELECT old_value from gamification_karma_tracking old_tracking
+    WHERE old_tracking.user_id = gamification_karma_tracking.user_id
+        AND tracking_date::timestamp BETWEEN %(from_date)s AND %(to_date)s
+        AND consolidated IS NOT TRUE
+        ORDER BY tracking_date ASC LIMIT 1
+), (
+    SELECT new_value from gamification_karma_tracking new_tracking
+    WHERE new_tracking.user_id = gamification_karma_tracking.user_id
+        AND tracking_date::timestamp BETWEEN %(from_date)s AND %(to_date)s
+        AND consolidated IS NOT TRUE
+        ORDER BY tracking_date DESC LIMIT 1
+)
+FROM gamification_karma_tracking
+WHERE tracking_date::timestamp BETWEEN %(from_date)s AND %(to_date)s
+AND consolidated IS NOT TRUE
+GROUP BY user_id """
+        self.env.cr.execute(select_query, {
+            'from_date': from_date,
+            'to_date': end_date,
+        })
+        results = self.env.cr.dictfetchall()
+        if results:
+            for result in results:
+                result['consolidated'] = True
+                result['tracking_date'] = fields.Date.to_string(from_date)
+            self.create(results)
+
+            self.search([
+                ('tracking_date', '>=', from_date),
+                ('tracking_date', '<=', end_date),
+                ('consolidated', '!=', True)]
+            ).unlink()
+        return True
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