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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.
from odoo import tools
from odoo import api, fields, models
class SaleReport(models.Model):
_name = "sale.report"
_description = "Sales Analysis Report"
_auto = False
_rec_name = 'date'
_order = 'date desc'
@api.model
def _get_done_states(self):
return ['sale', 'done', 'paid']
name = fields.Char('Order Reference', readonly=True)
date = fields.Datetime('Order Date', readonly=True)
product_id = fields.Many2one('product.product', 'Product Variant', readonly=True)
product_uom = fields.Many2one('uom.uom', 'Unit of Measure', readonly=True)
product_uom_qty = fields.Float('Qty Ordered', readonly=True)
qty_delivered = fields.Float('Qty Delivered', readonly=True)
qty_to_invoice = fields.Float('Qty To Invoice', readonly=True)
qty_invoiced = fields.Float('Qty Invoiced', readonly=True)
partner_id = fields.Many2one('res.partner', 'Customer', readonly=True)
company_id = fields.Many2one('res.company', 'Company', readonly=True)
user_id = fields.Many2one('res.users', 'Salesperson', readonly=True)
price_total = fields.Float('Total', readonly=True)
price_subtotal = fields.Float('Untaxed Total', readonly=True)
untaxed_amount_to_invoice = fields.Float('Untaxed Amount To Invoice', readonly=True)
untaxed_amount_invoiced = fields.Float('Untaxed Amount Invoiced', readonly=True)
product_tmpl_id = fields.Many2one('product.template', 'Product', readonly=True)
categ_id = fields.Many2one('product.category', 'Product Category', readonly=True)
nbr = fields.Integer('# of Lines', readonly=True)
pricelist_id = fields.Many2one('product.pricelist', 'Pricelist', readonly=True)
analytic_account_id = fields.Many2one('account.analytic.account', 'Analytic Account', readonly=True)
team_id = fields.Many2one('crm.team', 'Sales Team', readonly=True)
country_id = fields.Many2one('res.country', 'Customer Country', readonly=True)
industry_id = fields.Many2one('res.partner.industry', 'Customer Industry', readonly=True)
commercial_partner_id = fields.Many2one('res.partner', 'Customer Entity', readonly=True)
state = fields.Selection([
('draft', 'Draft Quotation'),
('sent', 'Quotation Sent'),
('sale', 'Sales Order'),
('done', 'Sales Done'),
('cancel', 'Cancelled'),
], string='Status', readonly=True)
weight = fields.Float('Gross Weight', readonly=True)
volume = fields.Float('Volume', readonly=True)
discount = fields.Float('Discount %', readonly=True)
discount_amount = fields.Float('Discount Amount', readonly=True)
campaign_id = fields.Many2one('utm.campaign', 'Campaign')
medium_id = fields.Many2one('utm.medium', 'Medium')
source_id = fields.Many2one('utm.source', 'Source')
order_id = fields.Many2one('sale.order', 'Order #', readonly=True)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
coalesce(min(l.id), -s.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as product_uom_qty,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_delivered / u.factor * u2.factor) ELSE 0 END as qty_delivered,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_invoiced / u.factor * u2.factor) ELSE 0 END as qty_invoiced,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_to_invoice / u.factor * u2.factor) ELSE 0 END as qty_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_total,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_subtotal,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
s.campaign_id as campaign_id,
s.medium_id as medium_id,
s.source_id as source_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.industry_id as industry_id,
partner.commercial_partner_id as commercial_partner_id,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.weight * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as weight,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.volume * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as volume,
l.discount as discount,
CASE WHEN l.product_id IS NOT NULL THEN sum((l.price_unit * l.product_uom_qty * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END))ELSE 0 END as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
right outer join sale_order s on (s.id=l.order_id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.user_id,
s.state,
s.company_id,
s.campaign_id,
s.medium_id,
s.source_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.industry_id,
partner.commercial_partner_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s GROUP BY %s)' % (with_, select_, from_, groupby_)
def init(self):
# self._table = sale_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" % (self._table, self._query()))
class SaleOrderReportProforma(models.AbstractModel):
_name = 'report.sale.report_saleproforma'
_description = 'Proforma Report'
@api.model
def _get_report_values(self, docids, data=None):
docs = self.env['sale.order'].browse(docids)
return {
'doc_ids': docs.ids,
'doc_model': 'sale.order',
'docs': docs,
'proforma': True
}
|
