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
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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
# © 2022 Le Filament (<http://www.le-filament.com>)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html).
from odoo import tools
from odoo import models, fields, api
class ScopContributionReport(models.Model):
_name = "scop.contribution.report"
_description = "Vue cotisations"
_auto = False
_order = 'year desc, partner_id'
name = fields.Char(compute='_compute_name')
year = fields.Char('Année de cotisation')
type_contribution_id = fields.Many2one(
comodel_name="scop.contribution.type",
string="Type de cotisation",
readonly=True)
partner_id = fields.Many2one('res.partner', string='Partner', readonly=True)
amount_called = fields.Float('Montant Appelé')
amount_paid = fields.Float('Montant Payé')
amount_due = fields.Float('Montant Restant')
is_loss = fields.Boolean('Exonération/Perte')
payments = fields.Html('Paiements', compute="_compute_payments")
_depends = {
'account.invoice': [
'year', 'type_contribution_id', 'partner_id',
'amount_total_signed', 'residual_company_signed',
'state', 'type', 'is_contribution', 'refund_invoice_id'
],
}
# ------------------------------------------------------
# Sub Query
# ------------------------------------------------------
def _select_invoice(self):
select_str = """
SELECT
CAST(i.year AS VARCHAR),
i.type_contribution_id,
i.partner_id,
SUM(i.amount_total_signed) AS amount_called,
SUM(i.amount_total_signed - i.residual_company_signed) AS amount_paid,
SUM(i.residual_company_signed) AS amount_due,
(CASE WHEN max(refund.id) IS NOT NULL THEN true ELSE FALSE END) AS is_loss
"""
return select_str
def _from_invoice(self):
from_str = """
FROM
account_invoice i
LEFT JOIN
account_invoice refund ON refund.refund_invoice_id = i.id
"""
return from_str
def _where_invoice(self):
where_str = """
WHERE
i.type = 'out_invoice' AND
i.state in ('open', 'paid') AND
i.is_contribution = true
"""
return where_str
def _groupby_invoice(self):
groupby_str = """
GROUP BY
i.year,
i.type_contribution_id,
i.partner_id
"""
return groupby_str
def _query_invoice(self):
query = "(%s %s %s %s)" % (
self._select_invoice(), self._from_invoice(),
self._where_invoice(), self._groupby_invoice())
return query
def _subquery(self):
return self._query_invoice()
# ------------------------------------------------------
# Main Query
# ------------------------------------------------------
def _select(self):
select_str = """
SELECT
ROW_NUMBER() OVER(ORDER BY c.year, c.partner_id) AS id,
c.year,
c.type_contribution_id,
c.partner_id,
SUM(c.amount_called) AS amount_called,
SUM(c.amount_paid) AS amount_paid,
SUM(c.amount_due) AS amount_due,
BOOL_OR(c.is_loss) AS is_loss
FROM (
"""
return select_str
def _query_groupby(self):
return """
GROUP BY
c.year,
c.type_contribution_id,
c.partner_id
"""
def _query_order(self):
return "ORDER BY c.year DESC"
def _query(self):
query = (
self._select() + self._subquery() + ') c ' +
self._query_groupby() + self._query_order()
)
return query
@api.model_cr
def init(self):
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()))
# ------------------------------------------------------
# Computed fields
# ------------------------------------------------------
@api.multi
def _compute_name(self):
for contribution in self:
contribution.name = (contribution.year + ' - ' +
contribution.type_contribution_id.name +
' - ' + contribution.partner_id.name)
@api.multi
def _compute_payments(self):
for contribution in self:
contribution.payments = contribution._get_payment()
# ------------------------------------------------------
# Business functions
# ------------------------------------------------------
def _get_payment(self):
self.ensure_one()
invoice_ids = self.env['account.invoice'].search([
('year', '=', int(self.year)),
('partner_id', '=', self.partner_id.id),
('type_contribution_id', '=', self.type_contribution_id.id),
('type', '=', 'out_invoice')
])
payment_ids = invoice_ids.mapped('payment_move_line_ids')
if payment_ids:
payments = payment_ids.mapped(lambda p: {
'date': p.date,
'name': p.name,
'ref': p.ref,
'credit': p.credit,
})
payments_html = self._get_html_table(payments)
else:
payments_html = "<p>Il n'y a pas de paiements associés à cette cotisation</p>"
return payments_html
def _get_html_table(self, payments):
"""
:param payments: list of dict {date, name, ref, amount}
@return: HTML table with payments
"""
start_html = """
<table class='table table-sm table-striped table-hover table-bordered'>
<thead><tr>
<th>Date</th>
<th>Libellé</th>
<th>Référence</th>
<th>Montant</th>
</tr></thead>
<tbody>
"""
content_html = ""
for payment in payments:
content_html += """
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class='text-right'>%.2f €</td>
</tr>
""" % (payment.get('date', '').strftime('%d/%m/%Y'),
payment.get('name', ''), payment.get('ref', ''),
payment.get('credit', 0.0),)
end_html = "</tbody></table>"
return start_html + content_html + end_html