[IMP] im_livechat : Fix livechat session and operator reports

Session Statistics : To keep only the operators, add a join to
im_livechat_channel_im_user that keeps the link between who is the operator
for which channel.

Operator Report : To keep only the users that have really participated
to the channel, add a join to mail_channel_partner_table that keeps the link
between who participate to which channel.

The report are only taking into account current operators.
If, on a livechat channel, an operator is removed and another user is
set as operator for ths channel. this new operator will have the stats
of the removed operator. This will stay like this until we add a field
that records who is the operator at the time the discussion. This field
should be added on mail_channel_partner table and should be a role.
So if a user is no more operator, he will still be flagged as operator of
this particular discussion.

Task ID 1883227
closes PR #26995

2 files changed, 8 insertions, 5 deletions

diff --git a/addons/im_livechat/report/im_livechat_report_channel.py b/addons/im_livechat/report/im_livechat_report_channel.py
index 4eabf1d2931..5749177b388 100644
--- a/addons/im_livechat/report/im_livechat_report_channel.py
+++ b/addons/im_livechat/report/im_livechat_report_channel.py
@@ -42,13 +42,15 @@ class ImLivechatReportChannel(models.Model):
                     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,
                     count(distinct P.id) as nbr_speaker,
                     count(distinct M.id) as nbr_message,
-                    MAX(S.partner_id) as partner_id
+                    S.partner_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 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)
-                    LEFT JOIN res_partner P ON (M.author_id = P.id)
-                GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid
+                    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)
+                GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, S.partner_id
             )
         """)
diff --git a/addons/im_livechat/report/im_livechat_report_operator.py b/addons/im_livechat/report/im_livechat_report_operator.py
index 9c768ebd293..4a561296eaf 100644
--- a/addons/im_livechat/report/im_livechat_report_operator.py
+++ b/addons/im_livechat/report/im_livechat_report_operator.py
@@ -38,8 +38,9 @@ class ImLivechatReportOperator(models.Model):
                 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)
-                    LEFT JOIN im_livechat_channel L ON (L.id = O.channel_id)
-                    LEFT JOIN mail_channel C ON (C.livechat_channel_id = L.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
             )
         """)