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
# 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
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
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())
)