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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
from odoo import fields, models, api, tools
import logging
_logger = logging.getLogger(__name__)
class LeadsMonitoring(models.Model):
_name = 'leads.monitoring'
_auto = False
_rec_name = 'lead_id'
id = fields.Integer()
lead_id = fields.Many2one('crm.lead', string='Leads')
create_date = fields.Datetime(string='Created')
lead_name = fields.Char(string='Lead')
contact_name = fields.Char(string='Contact Name')
partner_name = fields.Char(string='Partner Name')
email = fields.Char(string='Email')
phone = fields.Char(string='Phone')
mobile = fields.Char(string='Mobile')
salesperson_id = fields.Many2one('res.partner', string='Salesperson')
salesperson = fields.Char(string='Sales Name')
type = fields.Char(string='Type')
priority = fields.Char(string='Priority')
date_last_stage_update = fields.Datetime(string='Last Stage Updated')
stage_status = fields.Char(string='Status')
total_quotation = fields.Float(string='Quotation')
total_sales = fields.Float(string='Sales')
def init(self):
self._drop_view()
self.env.cr.execute("SELECT matviewname from pg_matviews where schemaname = 'public' and matviewname = '%s'" % self._table)
materialized_view = self.env.cr.fetchone()
if materialized_view is None:
self._init_materialized_view()
def action_refresh(self):
_logger.info("Refresh %s Materialized View..." % self._table)
self.env.cr.execute("Refresh Materialized View %s" % self._table)
_logger.info('Refresh %s View Success' % self._table)
def _drop_view(self):
self.env.cr.execute("SELECT viewname from pg_views where schemaname = 'public' and viewname = '%s'" % self._table)
standard_view = self.env.cr.fetchone()
if standard_view is not None:
self.env.cr.execute("DROP VIEW %s CASCADE" % self._table)
def _init_materialized_view(self):
self.env.cr.execute("""
CREATE MATERIALIZED VIEW %s AS (
select cl.id, cl.id as lead_id, cl.create_date,cl.email_from as email, cl.phone, cl.mobile, cl.name as lead_name,
u.partner_id as salesperson_id, p.name as salesperson, cl.type, cl.priority, cl.date_last_stage_update,
cl.contact_name, cl.partner_name,
case
when cl.stage_id = 2 then '3-Proses Quotation'
when cl.stage_id = 1 then '1-Lead / Potensi Baru'
when cl.stage_id = 4 then '5-Proses Berhasil'
when cl.stage_id = 3 then '2-Proses Lain, Visit, Etc'
when cl.stage_id = 5 then '4-Proses Negosiasi'
else '6-Undefined'
end as stage_status,
(select sum(so.amount_total) from sale_order so where so.opportunity_id = cl.id and so.state in ('draft', 'sent')) as total_quotation,
(select sum(so.amount_total) from sale_order so where so.opportunity_id = cl.id and so.state in ('sale')) as total_sales
from crm_lead cl
left join res_users u on u.id = cl.user_id
left join res_partner p on p.id = u.partner_id
where cl.active = 'true' and cl.stage_id not in(7)
order by cl.create_date desc
)
""" % self._table)
|
