[IMP] im_livechat : add measures to channel report to be used in Dashboard

Adds some indicators in channel report in order to use them in the new
enterprise livechat dashboard. Those indicator are added here to re-use
directly the channel report view. It allows the users to use also those
new indicators in the existing reports.

- No answer indicator
- Days of activity indicator
- Day number indicator
- Country of visitor :
    This makes able to see from which country comes the visitors
    and make statistics with it.

    If no GeoIP server are installed, or if the country is not found,
    or if visitor is logged in but has no country configured, the country
    si set to 'Unknown'.

Also, align sessions and operator report title on menu name

Task ID : 1895998
Closes PR #28283

3 files changed, 47 insertions, 3 deletions

diff --git a/addons/im_livechat/report/im_livechat_report_channel.py b/addons/im_livechat/report/im_livechat_report_channel.py
index ffae21169ab..6b35aa57035 100644
--- a/addons/im_livechat/report/im_livechat_report_channel.py
+++ b/addons/im_livechat/report/im_livechat_report_channel.py
@@ -18,10 +18,24 @@ class ImLivechatReportChannel(models.Model):
     technical_name = fields.Char('Code', readonly=True)
     livechat_channel_id = fields.Many2one('im_livechat.channel', 'Channel', readonly=True)
     start_date = fields.Datetime('Start Date of session', readonly=True, help="Start date of the conversation")
+    start_hour = fields.Char('Start Hour of session', readonly=True, help="Start hour of the conversation")
+    day_number = fields.Char('Day Number', readonly=True, help="Day number of the session (1 is Monday, 7 is Sunday)")
+    time_to_answer = fields.Float('Time to answer (sec)', digits=(16, 2), readonly=True, group_operator="avg", help="Average time in seconds to give the first answer to the visitor")
     start_date_hour = fields.Char('Hour of start Date of session', readonly=True)
     duration = fields.Float('Average duration', digits=(16, 2), readonly=True, group_operator="avg", help="Duration of the conversation (in seconds)")
     nbr_speaker = fields.Integer('# of speakers', readonly=True, group_operator="avg", help="Number of different speakers")
     nbr_message = fields.Integer('Average message', readonly=True, group_operator="avg", help="Number of message in the conversation")
+    is_without_answer = fields.Integer('Session(s) without answer', reandonly=True, group_operator="sum",
+                                       help="""A session is without answer if the operator did not answer. 
+                                       If the visitor is also the operator, the session will always be answered.""")
+    days_of_activity = fields.Integer('Days of activity', group_operator="max", readonly=True, help="Number of days since the first session of the operator")
+    is_anonymous = fields.Integer('Is visitor anonymous', readonly=True)
+    country_id = fields.Many2one('res.country', 'Country of the visitor', readonly=True)
+    is_happy = fields.Integer('Visitor is Happy', readonly=True)
+    rating = fields.Integer('Rating', group_operator="avg", readonly=True)
+    # TODO DBE : Use Selection field - Need : Pie chart must show labels, not keys.
+    rating_text = fields.Char('Satisfaction Rate', readonly=True)
+    is_unrated = fields.Integer('Session not rated', readonly=True)
     partner_id = fields.Many2one('res.partner', 'Operator', readonly=True)
 
     @api.model_cr
@@ -39,9 +53,38 @@ class ImLivechatReportChannel(models.Model):
                     C.livechat_channel_id as livechat_channel_id,
                     C.create_date as start_date,
                     to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
+                    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,
                     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)
+                        THEN 0
+                        ELSE 1
+                    END as is_without_answer,
+                    (DATE_PART('day', date_trunc('day', now()) - date_trunc('day', C.create_date)) + 1) as days_of_activity,
+                    CASE
+                        WHEN C.anonymous_name IS NULL THEN 0
+                        ELSE 1
+                    END as is_anonymous,
+                    C.country_id,
+                    CASE 
+                        WHEN rate.rating = 10 THEN 1
+                        ELSE 0
+                    END as is_happy,
+                    Rate.rating as rating,
+                    CASE
+                        WHEN Rate.rating = 1 THEN 'Unhappy'
+                        WHEN Rate.rating = 10 THEN 'Happy'
+                        WHEN Rate.rating = 5 THEN 'Neutral'
+                        ELSE null
+                    END as rating_text,
+                    CASE 
+                        WHEN rate.rating > 0 THEN 0
+                        ELSE 1
+                    END as is_unrated,
                     S.partner_id as partner_id
                 FROM mail_channel C
                     JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
@@ -51,6 +94,7 @@ class ImLivechatReportChannel(models.Model):
                     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
+                    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
             )
         """)
diff --git a/addons/im_livechat/report/im_livechat_report_channel_views.xml b/addons/im_livechat/report/im_livechat_report_channel_views.xml
index efe0304bd46..201dac2cbb1 100644
--- a/addons/im_livechat/report/im_livechat_report_channel_views.xml
+++ b/addons/im_livechat/report/im_livechat_report_channel_views.xml
@@ -47,7 +47,7 @@
         </record>
 
         <record id="im_livechat_report_channel_action" model="ir.actions.act_window">
-            <field name="name">Livechat Support Report Channel</field>
+            <field name="name">Session Statistics</field>
             <field name="res_model">im_livechat.report.channel</field>
             <field name="view_type">form</field>
             <field name="view_mode">graph,pivot</field>
diff --git a/addons/im_livechat/report/im_livechat_report_operator_views.xml b/addons/im_livechat/report/im_livechat_report_operator_views.xml
index 2aa147ca1b0..0c4df5beab1 100644
--- a/addons/im_livechat/report/im_livechat_report_operator_views.xml
+++ b/addons/im_livechat/report/im_livechat_report_operator_views.xml
@@ -43,7 +43,7 @@
         </record>
 
         <record id="im_livechat_report_operator_action" model="ir.actions.act_window">
-            <field name="name">Livechat Support Report Channel</field>
+            <field name="name">Operator Analysis</field>
             <field name="res_model">im_livechat.report.operator</field>
             <field name="view_type">form</field>
             <field name="view_mode">graph,pivot</field>