Newer
Older
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
# Copyright 2023 Le Filament (https://le-filament.com)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html)
from odoo import fields, models, tools
class HallContractTargetReport(models.Model):
_name = "hall.contract.target.report"
_description = "Sales target Statistics"
_auto = False
_order = "day_date desc, contract_id"
# ==== Sale fields ====
contract_id = fields.Many2one(
comodel_name="hall.contract", string="Contrat", readonly=True
)
day_date = fields.Date("Mois", required=True)
nb_receipts = fields.Integer("Nombre de tickets")
sales_excl_taxes = fields.Monetary("Total HT")
currency_id = fields.Many2one(
comodel_name="res.currency", compute="_compute_currency_id"
)
# ==== Contract fields ====
hall_id = fields.Many2one(comodel_name="hall.hall", string="Site", readonly=True)
# ==== Target fields ====
amount_untaxed_target = fields.Monetary("Objectif CA HT", readonly=True)
_depends = {
"hall.contract.sale": [
"contract_id",
"day_date",
"nb_receipts",
"sales_excl_taxes",
],
"hall.contract": [
"hall_id",
],
"hall.contract.target": [
"amount_untaxed_target",
"date_target",
],
}
# ------------------------------------------------------
# Computed fields / Search Fields
# ------------------------------------------------------
def _compute_currency_id(self):
for target in self:
target.currency_id = self.env.company.currency_id
# ------------------------------------------------------
# Queries
# ------------------------------------------------------
def _sale_query(self):
return """
SELECT
CAST(date_trunc('month', s.day_date) as date) as day_date,
s.contract_id,
c.hall_id,
sum(s.nb_receipts) as nb_receipts,
sum(s.sales_excl_taxes) as sales_excl_taxes,
0 as amount_untaxed_target
FROM
hall_contract_sale s
LEFT JOIN
hall_contract c ON c.id = s.contract_id
GROUP BY
s.contract_id, c.hall_id, date_trunc('month', s.day_date)
"""
def _target_query(self):
return """
SELECT
t.date_target as day_date,
t.contract_id,
c.hall_id,
0 as nb_receipts,
0 as sales_excl_taxes,
t.amount_untaxed_target as amount_untaxed_target
FROM
hall_contract_target t
LEFT JOIN
hall_contract c ON c.id = t.contract_id
"""
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute(
"""
CREATE view %s as
WITH contract_revenues AS
(%s UNION %s)
SELECT
ROW_NUMBER() OVER (ORDER BY day_date, contract_id) AS id,
day_date,
contract_id,
hall_id,
sum(sales_excl_taxes) as sales_excl_taxes,
sum(nb_receipts) as nb_receipts,
sum(amount_untaxed_target) as amount_untaxed_target
FROM
contract_revenues
GROUP BY
contract_id, hall_id, day_date
"""
% (self._table, self._sale_query(), self._target_query())
)