← Back to branch summary

~jgrandguillaume-c2c/openobject-addons/multi-company-cost-price

« back to all changes in this revision

Viewing changes to crm/report/crm_lead_report.py

  • Committer: Joël Grand-Guillaume
  • Date: 2010-04-08 09:00:10 UTC
  • mfrom: (2533.3.664)
  • Revision ID: joel.grandguillaume@camptocamp.com-20100408090010-c0pqjan341s18bxs
[MRG] Merge from last trunk

Show diffs side-by-side

added added

removed removed

Lines of Context:
crm/report/crm_lead_report.py
1
1
from osv import fields,osv
2
2
import tools
3
3
 
4
 
class report_crm_lead_user(osv.osv):
5
 
    _name = "report.crm.lead.user"
6
 
    _description = "Leads by user and section"
 
4
class crm_lead_report(osv.osv):
 
5
    _name = "crm.lead.report"
7
6
    _auto = False
8
 
    _inherit = "report.crm.case.user"
 
7
    _inherit = "crm.case.report"
9
8
    _columns = {
10
 
        'probability': fields.float('Avg. Probability', readonly=True),
11
 
        'amount_revenue': fields.float('Est.Revenue', readonly=True),
12
 
        'amount_costs': fields.float('Est.Cost', readonly=True),
13
 
        'amount_revenue_prob': fields.float('Est. Rev*Prob.', readonly=True),
14
9
        'delay_close': fields.char('Delay to close', size=20, readonly=True),
 
10
        'categ_id': fields.many2one('crm.case.categ', 'Category', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]" ,readonly=True),
 
11
        'stage_id': fields.many2one ('crm.case.stage', 'Stage', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
 
12
        'partner_id': fields.many2one('res.partner', 'Partner' ,readonly=True),
 
13
        'company_id': fields.many2one('res.company','Company',readonly=True),  
15
14
    }
16
15
    def init(self, cr):
17
 
        tools.drop_view_if_exists(cr, 'report_crm_lead_user')
 
16
        tools.drop_view_if_exists(cr, 'crm_lead_report')
18
17
        cr.execute("""
19
 
            create or replace view report_crm_lead_user as (
 
18
            create or replace view crm_lead_report as (
20
19
                select
21
20
                    min(c.id) as id,
22
21
                    to_char(c.create_date, 'YYYY') as name,
23
22
                    to_char(c.create_date, 'MM') as month,
24
23
                    c.state,
25
24
                    c.user_id,
 
25
                    c.stage_id,
 
26
                    c.company_id,
26
27
                    c.section_id,
27
 
                    count(*) as nbr,
28
 
                    sum(planned_revenue) as amount_revenue,
29
 
                    sum(planned_cost) as amount_costs,
30
 
                    sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
31
 
                    avg(probability)::decimal(16,2) as probability,
32
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
33
 
                from
34
 
                    crm_lead c
35
 
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'), c.state, c.user_id,c.section_id
36
 
            )""")
37
 
report_crm_lead_user()
38
 
 
39
 
class report_crm_lead_categ(osv.osv):
40
 
    _name = "report.crm.lead.categ"
41
 
    _description = "Leads by section and category"
42
 
    _auto = False
43
 
    _inherit = "report.crm.case.categ"
44
 
    _columns = {
45
 
        'categ_id': fields.many2one('crm.case.categ', 'Category', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]"),
46
 
        'amount_revenue': fields.float('Est.Revenue', readonly=True),
47
 
        'amount_costs': fields.float('Est.Cost', readonly=True),
48
 
        'amount_revenue_prob': fields.float('Est. Rev*Prob.', readonly=True),
49
 
        'probability': fields.float('Avg. Probability', readonly=True),
50
 
        'delay_close': fields.char('Delay Close', size=20, readonly=True),
51
 
    }
52
 
    
53
 
    def init(self, cr):
54
 
        tools.drop_view_if_exists(cr, 'report_crm_lead_categ')
55
 
        cr.execute("""
56
 
            create or replace view report_crm_lead_categ as (
57
 
                select
58
 
                    min(c.id) as id,
59
 
                    to_char(c.create_date, 'YYYY') as name,
60
 
                    to_char(c.create_date, 'MM') as month,
61
28
                    c.categ_id,
62
 
                    c.state,
63
 
                    c.section_id,
 
29
                    c.partner_id,
64
30
                    count(*) as nbr,
65
 
                    sum(planned_revenue) as amount_revenue,
66
 
                    sum(planned_cost) as amount_costs,
67
 
                    sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
68
 
                    avg(probability)::decimal(16,2) as probability,
69
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
70
 
                from
71
 
                    crm_lead c
72
 
                group by c.categ_id,to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'), c.state,c.section_id
73
 
            )""")
74
 
report_crm_lead_categ()
75
 
 
76
 
class report_crm_lead_section(osv.osv):
77
 
    _name = "report.crm.lead.section"
78
 
    _description = "Leads by Section"
79
 
    _auto = False
80
 
    _inherit = "report.crm.case.section"
81
 
    
82
 
    def _get_data(self, cr, uid, ids, field_name, arg, context={}):
83
 
        res = {}
84
 
        state_perc = 0.0
85
 
        avg_ans = 0.0
86
 
        
87
 
        for case in self.browse(cr, uid, ids, context):
88
 
            if field_name != 'avg_answers':
89
 
                state = field_name[5:]
90
 
                cr.execute("select count(*) from crm_lead where section_id =%s and state='%s'"%(case.section_id.id,state))
91
 
                state_cases = cr.fetchone()[0]
92
 
                perc_state = (state_cases / float(case.nbr_cases) ) * 100
93
 
                
94
 
                res[case.id] = perc_state
95
 
            else:
96
 
                cr.execute('select count(*) from crm_case_log l  where l.section_id=%s'%(case.section_id.id))
97
 
                logs = cr.fetchone()[0]
98
 
                
99
 
                avg_ans = logs / case.nbr_cases
100
 
                res[case.id] = avg_ans       
101
 
        
102
 
        return res
103
 
    
104
 
    _columns = {
105
 
        'avg_answers': fields.function(_get_data,string='Avg. Answers', method=True,type="integer"),
106
 
        'perc_done': fields.function(_get_data,string='%Done', method=True,type="float"),
107
 
        'perc_cancel': fields.function(_get_data,string='%Cancel', method=True,type="float"),
108
 
        'delay_close': fields.char('Delay to close', size=20, readonly=True),
109
 
    }
110
 
    _order = 'name desc, section_id'
111
 
    def init(self, cr):
112
 
        tools.drop_view_if_exists(cr, 'report_crm_lead_section')
113
 
        cr.execute("""
114
 
            create or replace view report_crm_lead_section as (
115
 
                select
116
 
                    min(c.id) as id,
117
 
                    to_char(c.create_date, 'YYYY') as name,
118
 
                    to_char(c.create_date, 'MM') as month,
119
 
                    count(*) as nbr_cases,
120
 
                    c.section_id as section_id,
121
31
                    0 as avg_answers,
122
32
                    0.0 as perc_done,
123
33
                    0.0 as perc_cancel,
124
34
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
125
35
                from
126
36
                    crm_lead c
127
 
                group by to_char(c.create_date, 'YYYY'),to_char(c.create_date, 'MM'),c.section_id
 
37
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'), c.state, c.user_id,c.section_id,c.stage_id,categ_id,c.partner_id,c.company_id
128
38
            )""")
129
 
report_crm_lead_section()
130
 
 
131
 
class report_crm_lead_section_stage(osv.osv):
132
 
    _name = "report.crm.lead.section.stage"
133
 
    _description = "Leads by section and stage"
134
 
    _auto = False
135
 
    _inherit = "report.crm.case.section.stage"
136
 
    _columns = {
137
 
        'categ_id': fields.many2one('crm.case.categ', 'Category', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
138
 
        'stage_id': fields.many2one ('crm.case.stage', 'Stage', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
139
 
        'amount_revenue': fields.float('Est.Revenue', readonly=True),
140
 
        'delay_close': fields.char('Delay Close', size=20, readonly=True),
141
 
                }
142
 
    _order = 'stage_id, section_id'
143
 
 
144
 
    def init(self, cr):
145
 
        tools.sql.drop_view_if_exists(cr, "report_crm_lead_section_stage")
146
 
        cr.execute("""
147
 
              create view report_crm_lead_section_stage as (
148
 
                select
149
 
                    min(c.id) as id,
150
 
                    to_char(c.create_date,'YYYY') as name,
151
 
                    to_char(c.create_date, 'MM') as month,
152
 
                    c.user_id,
153
 
                    c.state,
154
 
                    c.stage_id,
155
 
                    c.section_id,
156
 
                    c.categ_id, 
157
 
                    count(*) as nbr,
158
 
                    sum(planned_revenue) as amount_revenue,
159
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
160
 
                from
161
 
                    crm_lead c
162
 
                where c.stage_id is not null
163
 
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'), c.user_id, c.state, c.stage_id, c.categ_id, c.section_id)""")
164
 
 
165
 
report_crm_lead_section_stage()
166
 
 
167
 
class report_crm_lead_section_type(osv.osv):
168
 
    _name = "report.crm.lead.section.type"
169
 
    _inherit = "report.crm.case.section.type"
170
 
    _description = "Leads by section and type"
171
 
    _auto = False
172
 
    _columns = {
173
 
        'type_id': fields.many2one('crm.case.resource.type', 'Lead Type', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
174
 
        'stage_id': fields.many2one ('crm.case.stage', 'Stage', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
175
 
        'amount_revenue': fields.float('Est.Revenue', readonly=True),
176
 
        'delay_close': fields.char('Delay Close', size=20, readonly=True),
177
 
    }
178
 
    _order = 'type_id'
179
 
 
180
 
    def init(self, cr):
181
 
        tools.sql.drop_view_if_exists(cr, "report_crm_lead_section_type")
182
 
        cr.execute("""
183
 
              create view report_crm_lead_section_type as (
184
 
                select
185
 
                    min(c.id) as id,
186
 
                    to_char(c.create_date,'YYYY') as name,
187
 
                    to_char(c.create_date, 'MM') as month,
188
 
                    c.user_id,
189
 
                    c.state,
190
 
                    c.type_id,
191
 
                    c.stage_id,
192
 
                    c.section_id,
193
 
                    count(*) as nbr,
194
 
                    sum(planned_revenue) as amount_revenue,
195
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
196
 
                from
197
 
                    crm_lead c
198
 
                where c.type_id is not null
199
 
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'), c.user_id, c.state, c.stage_id, c.type_id, c.section_id)""")
200
 
 
201
 
report_crm_lead_section_type()
202
 
 
203
 
class report_crm_lead_section_categ_stage(osv.osv):
204
 
    _name = "report.crm.lead.section.categ.stage"
205
 
    _inherit = "report.crm.case.section.categ.stage"
206
 
    _description = "Leads by Section, Category and Stage"
207
 
    _auto = False
208
 
    _columns = {
209
 
        'categ_id': fields.many2one('crm.case.categ','Category', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
210
 
        'stage_id':fields.many2one('crm.case.stage', 'Stage', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
211
 
        'delay_close': fields.char('Delay Close', size=20, readonly=True),
212
 
    }
213
 
    _order = 'stage_id, categ_id'
214
 
 
215
 
    def init(self, cr):
216
 
        tools.sql.drop_view_if_exists(cr, "report_crm_lead_section_categ_stage")
217
 
        cr.execute("""
218
 
              create view report_crm_lead_section_categ_stage as (
219
 
                select
220
 
                    min(c.id) as id,
221
 
                    to_char(c.create_date,'YYYY') as name,
222
 
                    to_char(c.create_date, 'MM') as month,
223
 
                    c.user_id,
224
 
                    c.categ_id,
225
 
                    c.state,
226
 
                    c.stage_id,
227
 
                    c.section_id,
228
 
                    count(*) as nbr,
229
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
230
 
                from
231
 
                    crm_lead c
232
 
                where c.categ_id is not null AND c.stage_id is not null
233
 
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'),c.user_id, c.categ_id, c.state, c.stage_id, c.section_id)""")
234
 
 
235
 
report_crm_lead_section_categ_stage()
236
 
 
237
 
class report_crm_lead_section_categ_type(osv.osv):
238
 
    _name = "report.crm.lead.section.categ.type"
239
 
    _inherit = "report.crm.case.section.categ.type"
240
 
    _description = "Leads by Section, Category and Type"
241
 
    _auto = False
242
 
    _columns = {
243
 
        'categ_id':fields.many2one('crm.case.categ', 'Category', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
244
 
        'type_id': fields.many2one('crm.case.resource.type', 'Lead Type', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
245
 
        'stage_id':fields.many2one('crm.case.stage', 'Stage', domain="[('section_id','=',section_id),('object_id.model', '=', 'crm.lead')]", readonly=True),
246
 
        'delay_close': fields.char('Delay Close', size=20, readonly=True),
247
 
    }
248
 
    _order = 'categ_id, type_id'
249
 
 
250
 
    def init(self, cr):
251
 
        tools.sql.drop_view_if_exists(cr, "report_crm_lead_section_categ_type")
252
 
        cr.execute("""
253
 
              create view report_crm_lead_section_categ_type as (
254
 
                select
255
 
                    min(c.id) as id,
256
 
                    to_char(c.create_date, 'YYYY') as name,
257
 
                    to_char(c.create_date, 'MM') as month,
258
 
                    c.user_id,
259
 
                    c.categ_id,
260
 
                    c.type_id,
261
 
                    c.state,
262
 
                    c.stage_id,
263
 
                    c.section_id,
264
 
                    count(*) as nbr,
265
 
                    to_char(avg(date_closed-c.create_date), 'DD"d" HH24:MI:SS') as delay_close
266
 
                from
267
 
                    crm_lead c
268
 
                where c.categ_id is not null AND c.type_id is not null
269
 
                group by to_char(c.create_date, 'YYYY'), to_char(c.create_date, 'MM'),c.user_id, c.categ_id, c.type_id, c.state, c.stage_id, c.section_id)""")
270
 
 
271
 
report_crm_lead_section_categ_type()
272
 
 
273
 
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 
 
b'\\ No newline at end of file'
 
39
crm_lead_report()
 
40
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: