[IMP] im_livechat : store assigned livechat operator

By adding a livechat_operator_id on the mail.channel, we ensure that,
even if the operator of the discussion is removed from the operators
of the livechat.channel, we can still know who was the operator,
at the time of the discussion.

In that way, we can still keep the history of the former assigned operator.

[IMP] im_livechat : simplify channel and operator reports

As Operator_id is required for livechat channel,
we can assume that if livechat_operator_id is not null,
then it's a livechat channel. also, as we have the partner_id
stored in livechat_operator_id field, no need to join
partners and users tables anymore to get the operator
of the channel.

Task ID : 1895998
Closes PR #28283

4 files changed, 20 insertions, 25 deletions

diff --git a/addons/im_livechat/models/im_livechat_channel.py b/addons/im_livechat/models/im_livechat_channel.py
index 5ebf269ba85..91e47b1151b 100644
--- a/addons/im_livechat/models/im_livechat_channel.py
+++ b/addons/im_livechat/models/im_livechat_channel.py
@@ -157,6 +157,7 @@ class ImLivechatChannel(models.Model):
         # create the session, and add the link with the given channel
         mail_channel = self.env["mail.channel"].with_context(mail_create_nosubscribe=False).sudo().create({
             'channel_partner_ids': channel_partner_to_add,
+            'livechat_operator_id': operator_partner_id,
             'livechat_channel_id': livechat_channel_id,
             'anonymous_name': False if user_id else anonymous_name,
             'country_id': country_id,
@@ -165,7 +166,7 @@ class ImLivechatChannel(models.Model):
             'public': 'private',
             'email_send': False,
         })
-        return mail_channel.sudo().with_context(im_livechat_operator_partner_id=operator_partner_id).channel_info()[0]
+        return mail_channel.sudo().channel_info()[0]
 
     @api.model
     def get_channel_infos(self, channel_id):
diff --git a/addons/im_livechat/models/mail_channel.py b/addons/im_livechat/models/mail_channel.py
index c1f34c1387f..d028e301fc1 100644
--- a/addons/im_livechat/models/mail_channel.py
+++ b/addons/im_livechat/models/mail_channel.py
@@ -35,8 +35,12 @@ class MailChannel(models.Model):
     anonymous_name = fields.Char('Anonymous Name')
     channel_type = fields.Selection(selection_add=[('livechat', 'Livechat Conversation')])
     livechat_channel_id = fields.Many2one('im_livechat.channel', 'Channel')
+    livechat_operator_id = fields.Many2one('res.partner', string='Operator', help="""Operator for this specific channel""")
     country_id = fields.Many2one('res.country', string="Country", help="Country of the visitor of the channel")
 
+    _sql_constraints = [('livechat_operator_id', "CHECK((channel_type = 'livechat' and livechat_operator_id is not null) or (channel_type != 'livechat'))",
+                         'Livechat Operator ID is required for a channel of type livechat.')]
+
     @api.multi
     def _channel_message_notifications(self, message):
         """ When a anonymous user create a mail.channel, the operator is not notify (to avoid massive polling when
@@ -62,15 +66,13 @@ class MailChannel(models.Model):
             :rtype : list(dict)
         """
         channel_infos = super(MailChannel, self).channel_info(extra_info)
-        # add the operator id
-        if self.env.context.get('im_livechat_operator_partner_id'):
-            partner_name = self.env['res.partner'].browse(self.env.context.get('im_livechat_operator_partner_id')).name_get()[0]
-            for channel_info in channel_infos:
-                channel_info['operator_pid'] = partner_name
         channel_infos_dict = dict((c['id'], c) for c in channel_infos)
         for channel in self:
             # add the last message date
             if channel.channel_type == 'livechat':
+                # add the operator id
+                if channel.livechat_operator_id:
+                    channel_infos_dict[channel.id]['operator_pid'] = channel.livechat_operator_id.display_name
                 # add the anonymous or partner name
                 channel_infos_dict[channel.id]['correspondent_name'] = channel._channel_get_livechat_partner_name()
                 last_msg = self.env['mail.message'].search([("channel_ids", "in", [channel.id])], limit=1)
diff --git a/addons/im_livechat/report/im_livechat_report_channel.py b/addons/im_livechat/report/im_livechat_report_channel.py
index 6b35aa57035..c6314fbc2af 100644
--- a/addons/im_livechat/report/im_livechat_report_channel.py
+++ b/addons/im_livechat/report/im_livechat_report_channel.py
@@ -56,11 +56,11 @@ class ImLivechatReportChannel(models.Model):
                     to_char(date_trunc('hour', C.create_date), 'HH24') as start_hour,
                     extract(dow from  C.create_date) as day_number, 
                     EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
-                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer,
-                    count(distinct P.id) as nbr_speaker,
+                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer,
+                    count(distinct C.livechat_operator_id) as nbr_speaker,
                     count(distinct M.id) as nbr_message,
                     CASE 
-                        WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
+                        WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
                         THEN 0
                         ELSE 1
                     END as is_without_answer,
@@ -85,16 +85,12 @@ class ImLivechatReportChannel(models.Model):
                         WHEN rate.rating > 0 THEN 0
                         ELSE 1
                     END as is_unrated,
-                    S.partner_id as partner_id
+                    C.livechat_operator_id as partner_id
                 FROM mail_channel C
-                    JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
+                    JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id and C.livechat_operator_id is not null)
                     JOIN mail_message M ON (M.id = R.mail_message_id)
-                    JOIN mail_channel_partner S ON (S.channel_id = C.id)
                     JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
-                    JOIN res_partner P ON (S.partner_id = p.id)
-                    JOIN res_users U ON (U.partner_id = P.id)
-                    JOIN im_livechat_channel_im_user O ON (O.user_id = U.id and O.channel_id = L.id)
                     LEFT JOIN rating_rating Rate ON (Rate.res_id = C.id and Rate.res_model = 'mail.channel' and Rate.parent_res_model = 'im_livechat.channel')
-                GROUP BY P.id, C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, S.partner_id, Rate.rating
+                GROUP BY C.livechat_operator_id, C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, Rate.rating
             )
         """)
diff --git a/addons/im_livechat/report/im_livechat_report_operator.py b/addons/im_livechat/report/im_livechat_report_operator.py
index 30adad7a5d7..a212cd5633b 100644
--- a/addons/im_livechat/report/im_livechat_report_operator.py
+++ b/addons/im_livechat/report/im_livechat_report_operator.py
@@ -28,19 +28,15 @@ class ImLivechatReportOperator(models.Model):
             CREATE OR REPLACE VIEW im_livechat_report_operator AS (
                 SELECT
                     row_number() OVER () AS id,
-                    P.id as partner_id,
+                    C.livechat_operator_id as partner_id,
                     L.id as livechat_channel_id,
                     count(C.id) as nbr_channel,
                     C.id as channel_id,
                     C.create_date as start_date,
                     EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
-                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
-                FROM im_livechat_channel_im_user O
-                    JOIN res_users U ON (O.user_id = U.id)
-                    JOIN res_partner P ON (U.partner_id = P.id)
-                    JOIN im_livechat_channel L ON (L.id = O.channel_id)
-                    JOIN mail_channel C ON (C.livechat_channel_id = L.id)
-                    JOIN mail_channel_partner MCP ON (MCP.partner_id = P.id AND MCP.channel_id = C.id)
-                GROUP BY P.id, L.id, C.id, C.create_date
+                    EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
+                FROM mail_channel C
+                    JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id and C.livechat_operator_id is not null)
+                GROUP BY C.livechat_operator_id, L.id, C.id, C.create_date
             )
         """)