initial commit 2

5 files changed, 295 insertions, 0 deletions

diff --git a/addons/im_livechat/report/__init__.py b/addons/im_livechat/report/__init__.py
new file mode 100644
index 00000000..0441c602
--- /dev/null
+++ b/addons/im_livechat/report/__init__.py
@@ -0,0 +1,3 @@
+# -*- coding: utf-8 -*-
+from . import im_livechat_report_channel
+from . import im_livechat_report_operator
diff --git a/addons/im_livechat/report/im_livechat_report_channel.py b/addons/im_livechat/report/im_livechat_report_channel.py
new file mode 100644
index 00000000..07c01110
--- /dev/null
+++ b/addons/im_livechat/report/im_livechat_report_channel.py
@@ -0,0 +1,99 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from odoo import api, fields, models, tools
+
+
+class ImLivechatReportChannel(models.Model):
+    """ Livechat Support Report on the Channels """
+
+    _name = "im_livechat.report.channel"
+    _description = "Livechat Support Channel Report"
+    _order = 'start_date, technical_name'
+    _auto = False
+
+    uuid = fields.Char('UUID', readonly=True)
+    channel_id = fields.Many2one('mail.channel', 'Conversation', readonly=True)
+    channel_name = fields.Char('Channel Name', readonly=True)
+    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', readonly=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)
+
+    def init(self):
+        # Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
+        tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_channel')
+        self.env.cr.execute("""
+            CREATE OR REPLACE VIEW im_livechat_report_channel AS (
+                SELECT
+                    C.id as id,
+                    C.uuid as uuid,
+                    C.id as channel_id,
+                    C.name as channel_name,
+                    CONCAT(L.name, ' / ', C.id) as technical_name,
+                    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(M.create_date) - MIN(M.create_date)) AS duration,
+                    EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) 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=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,
+                    (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 = 5 THEN 1
+                        ELSE 0
+                    END as is_happy,
+                    Rate.rating as rating,
+                    CASE
+                        WHEN Rate.rating = 1 THEN 'Unhappy'
+                        WHEN Rate.rating = 5 THEN 'Happy'
+                        WHEN Rate.rating = 3 THEN 'Neutral'
+                        ELSE null
+                    END as rating_text,
+                    CASE 
+                        WHEN rate.rating > 0 THEN 0
+                        ELSE 1
+                    END as is_unrated,
+                    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 M ON (M.id = R.mail_message_id)
+                    JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
+                    LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_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')
+                    WHERE C.livechat_operator_id is not null
+                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_channel_views.xml b/addons/im_livechat/report/im_livechat_report_channel_views.xml
new file mode 100644
index 00000000..1de3ca5c
--- /dev/null
+++ b/addons/im_livechat/report/im_livechat_report_channel_views.xml
@@ -0,0 +1,83 @@
+<?xml version="1.0"?>
+<odoo>
+    <data>
+
+        <record id="im_livechat_report_channel_view_pivot" model="ir.ui.view">
+            <field name="name">im_livechat.report.channel.pivot</field>
+            <field name="model">im_livechat.report.channel</field>
+            <field name="arch" type="xml">
+                <pivot string="Livechat Support Statistics" disable_linking="True" sample="1">
+                    <field name="technical_name" type="row"/>
+                    <field name="duration" type="measure"/>
+                    <field name="nbr_message" type="measure"/>
+                </pivot>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_channel_view_graph" model="ir.ui.view">
+            <field name="name">im_livechat.report.channel.graph</field>
+            <field name="model">im_livechat.report.channel</field>
+            <field name="arch" type="xml">
+                <graph string="Livechat Support Statistics" sample="1" disable_linking="1">
+                    <field name="technical_name"/>
+                    <field name="nbr_message" type="measure"/>
+                </graph>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_channel_view_search" model="ir.ui.view">
+            <field name="name">im_livechat.report.channel.search</field>
+            <field name="model">im_livechat.report.channel</field>
+            <field name="arch" type="xml">
+                <search string="Search report">
+                    <field name="channel_name"/>
+                    <filter name="missed_session" string="Missed sessions" domain="[('nbr_speaker','&lt;=', 1)]"/>
+                    <filter name="treated_session" string="Treated sessions" domain="[('nbr_speaker','&gt;', 1)]"/>
+                    <filter name="last_24h" string="Last 24h" domain="[('start_date','&gt;', (context_today() - datetime.timedelta(days=1)).strftime('%%Y-%%m-%%d') )]"/>
+                    <filter name="start_date_filter" string="This Week" domain="[
+                        ('start_date', '>=', (datetime.datetime.combine(context_today() + relativedelta(weeks=-1,days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S')),
+                        ('start_date', '&lt;', (datetime.datetime.combine(context_today() + relativedelta(days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S'))]"/>
+                    <separator/>
+                    <filter name="filter_start_date" date="start_date"/>
+                    <group expand="0" string="Group By...">
+                        <filter name="group_by_session" string="Code" domain="[]" context="{'group_by':'technical_name'}"/>
+                        <filter name="group_by_channel" string="Channel" domain="[]" context="{'group_by':'channel_id'}"/>
+                        <filter name="group_by_operator" string="Operator" domain="[('partner_id','!=', False)]" context="{'group_by':'partner_id'}"/>
+                        <separator orientation="vertical" />
+                        <filter name="group_by_hour" string="Creation date (hour)" domain="[]" context="{'group_by':'start_date_hour'}"/>
+                        <filter name="group_by_month" string="Creation date" domain="[]" context="{'group_by':'start_date:month'}" />
+                    </group>
+                </search>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_channel_action" model="ir.actions.act_window">
+            <field name="name">Session Statistics</field>
+            <field name="res_model">im_livechat.report.channel</field>
+            <field name="view_mode">graph,pivot</field>
+            <field name="context">{"search_default_last_week":1}</field>
+            <field name="help">Livechat Support Channel Statistics allows you to easily check and analyse your company livechat session performance. Extract information about the missed sessions, the audiance, the duration of a session, etc.</field>
+        </record>
+
+        <record id="im_livechat_report_channel_time_to_answer_action" model="ir.actions.act_window">
+            <field name="name">Session Statistics</field>
+            <field name="res_model">im_livechat.report.channel</field>
+            <field name="view_mode">graph,pivot</field>
+            <field name="context">{"graph_measure": "time_to_answer", "search_default_last_week":1}</field>
+            <field name="help" type="html">
+                <p class="o_view_nocontent_smiling_face">
+                    No data yet!
+                </p>
+            </field>
+        </record>
+
+        <menuitem
+            id="menu_reporting_livechat_channel"
+            name="Session Statistics"
+            parent="menu_reporting_livechat"
+            sequence="10"
+            action="im_livechat_report_channel_action"/>
+
+
+    </data>
+</odoo>
diff --git a/addons/im_livechat/report/im_livechat_report_operator.py b/addons/im_livechat/report/im_livechat_report_operator.py
new file mode 100644
index 00000000..cafaf898
--- /dev/null
+++ b/addons/im_livechat/report/im_livechat_report_operator.py
@@ -0,0 +1,44 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from odoo import api, fields, models, tools
+
+
+class ImLivechatReportOperator(models.Model):
+    """ Livechat Support Report on the Operator """
+
+    _name = "im_livechat.report.operator"
+    _description = "Livechat Support Operator Report"
+    _order = 'livechat_channel_id, partner_id'
+    _auto = False
+
+    partner_id = fields.Many2one('res.partner', 'Operator', readonly=True)
+    livechat_channel_id = fields.Many2one('im_livechat.channel', 'Channel', readonly=True)
+    nbr_channel = fields.Integer('# of Sessions', readonly=True, group_operator="sum", help="Number of conversation")
+    channel_id = fields.Many2one('mail.channel', 'Conversation', readonly=True)
+    start_date = fields.Datetime('Start Date of session', readonly=True, help="Start date of the conversation")
+    time_to_answer = fields.Float('Time to answer', digits=(16, 2), readonly=True, group_operator="avg", help="Average time to give the first answer to the visitor")
+    duration = fields.Float('Average duration', digits=(16, 2), readonly=True, group_operator="avg", help="Duration of the conversation (in seconds)")
+
+    def init(self):
+        # Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
+        tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_operator')
+        self.env.cr.execute("""
+            CREATE OR REPLACE VIEW im_livechat_report_operator AS (
+                SELECT
+                    row_number() OVER () AS id,
+                    C.livechat_operator_id AS partner_id,
+                    C.livechat_channel_id AS livechat_channel_id,
+                    COUNT(DISTINCT C.id) AS nbr_channel,
+                    C.id AS channel_id,
+                    C.create_date AS start_date,
+                    EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
+                    EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer
+                FROM mail_channel C
+                    JOIN mail_message_mail_channel_rel R ON R.mail_channel_id = C.id
+                    JOIN mail_message M ON R.mail_message_id = M.id
+                    LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
+                WHERE C.livechat_channel_id IS NOT NULL
+                GROUP BY C.id, C.livechat_operator_id
+            )
+        """)
diff --git a/addons/im_livechat/report/im_livechat_report_operator_views.xml b/addons/im_livechat/report/im_livechat_report_operator_views.xml
new file mode 100644
index 00000000..e2059fed
--- /dev/null
+++ b/addons/im_livechat/report/im_livechat_report_operator_views.xml
@@ -0,0 +1,66 @@
+<?xml version="1.0"?>
+<odoo>
+    <data>
+
+        <record id="im_livechat_report_operator_view_pivot" model="ir.ui.view">
+            <field name="name">im_livechat.report.operator.pivot</field>
+            <field name="model">im_livechat.report.operator</field>
+            <field name="arch" type="xml">
+                <pivot string="Livechat Support Statistics" disable_linking="True" sample="1">
+                    <field name="partner_id" type="row"/>
+                    <field name="duration" type="measure"/>
+                    <field name="nbr_channel" type="measure"/>
+                </pivot>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_operator_view_graph" model="ir.ui.view">
+            <field name="name">im_livechat.report.operator.graph</field>
+            <field name="model">im_livechat.report.operator</field>
+            <field name="arch" type="xml">
+                <graph string="Livechat Support Statistics" sample="1" disable_linking="1">
+                    <field name="partner_id"/>
+                    <field name="nbr_channel" type="measure"/>
+                </graph>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_operator_view_search" model="ir.ui.view">
+            <field name="name">im_livechat.report.operator.search</field>
+            <field name="model">im_livechat.report.operator</field>
+            <field name="arch" type="xml">
+                <search string="Search report">
+                    <field name="partner_id"/>
+                    <filter name="last_24h" string="Last 24h" domain="[('start_date','&gt;', (context_today() - datetime.timedelta(days=1)).strftime('%%Y-%%m-%%d') )]"/>
+                    <filter name="start_date_filter" string="This Week" domain="[
+                        ('start_date', '>=', (datetime.datetime.combine(context_today() + relativedelta(weeks=-1,days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S')),
+                        ('start_date', '&lt;', (datetime.datetime.combine(context_today() + relativedelta(days=1,weekday=0), datetime.time(0,0,0)).to_utc()).strftime('%Y-%m-%d %H:%M:%S'))]"/>
+                    <separator/>
+                    <filter name="filter_start_date" date="start_date"/>
+                    <group expand="0" string="Group By...">
+                        <filter name="group_by_channel" string="Channel" domain="[]" context="{'group_by':'channel_id'}"/>
+                        <filter name="group_by_operator" string="Operator" domain="[('partner_id','!=', False)]" context="{'group_by':'partner_id'}"/>
+                        <separator orientation="vertical" />
+                        <filter name="group_by_month" string="Creation date" domain="[]" context="{'group_by':'start_date:month'}" />
+                    </group>
+                </search>
+            </field>
+        </record>
+
+        <record id="im_livechat_report_operator_action" model="ir.actions.act_window">
+            <field name="name">Operator Analysis</field>
+            <field name="res_model">im_livechat.report.operator</field>
+            <field name="view_mode">graph,pivot</field>
+            <field name="context">{"search_default_last_week":1}</field>
+            <field name="help">Livechat Support Channel Statistics allows you to easily check and analyse your company livechat session performance. Extract information about the missed sessions, the audiance, the duration of a session, etc.</field>
+        </record>
+
+        <menuitem
+            id="menu_reporting_livechat_operator"
+            name="Operator Analysis"
+            parent="menu_reporting_livechat"
+            sequence="10"
+            action="im_livechat_report_operator_action"/>
+
+    </data>
+</odoo>