1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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
)
""")
|
