# © 2022 Le Filament (<http://www.le-filament.com>)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html).
from psycopg2.extensions import AsIs
from odoo import api, fields, models, tools
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")
source = fields.Selection(
string="Source",
selection=[("odoo", "Odoo")],
required=True,
)
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", compute="_compute_is_loss")
payments = fields.Html("Paiements", compute="_compute_payments")
_depends = {
"account.move": [
"year",
"type_contribution_id",
"partner_id",
"amount_total_signed",
"amount_residual_signed",
"state",
"move_type",
"is_contribution",
"payment_state",
],
}
# ------------------------------------------------------
# Sub Query
# ------------------------------------------------------
def _select_invoice(self):
select_str = """
SELECT
'odoo' as source,
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.amount_residual_signed) AS amount_paid,
SUM(i.amount_residual_signed) AS amount_due
"""
return select_str
def _from_invoice(self):
from_str = """
FROM
account_move i
"""
return from_str
def _where_invoice(self):
where_str = """
WHERE
i.state = 'posted'
AND i.move_type in ('out_invoice', 'out_refund')
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.source,
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
FROM (
"""
return select_str
def _query_groupby(self):
return """
GROUP BY
c.year,
c.type_contribution_id,
c.partner_id,
c.source
"""
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
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(
"CREATE or REPLACE VIEW %s as (%s)",
(AsIs(self._table), AsIs(self._query())),
)
# ------------------------------------------------------
# Computed fields
# ------------------------------------------------------
def _compute_is_loss(self):
for contribution in self:
contribution.is_loss = contribution._get_is_loss()
def _compute_name(self):
for contribution in self:
contribution.name = (
contribution.year
+ " - "
+ contribution.type_contribution_id.name
+ " - "
+ contribution.partner_id.name
)
def _compute_payments(self):
for contribution in self:
contribution.payments = contribution._get_payment()
# ------------------------------------------------------
# Business functions
# ------------------------------------------------------
def _get_is_loss(self):
invoice_ids = (
self.env["account.move"]
.sudo()
.search(
[
("year", "=", int(self.year)),
("partner_id", "=", self.partner_id.id),
(
"type_contribution_id",
"=",
self.type_contribution_id.id,
),
]
)
)
refund_ids = invoice_ids.filtered(lambda i: i.move_type == "out_refund")
if refund_ids:
return True
else:
return False
def _get_payment(self):
self.ensure_one()
payments_html = False
if self.source == "odoo":
invoice_ids = self.get_invoice_contribution()
payment_ids = invoice_ids.mapped("move_line_payment_ids")
if payment_ids:
payments = payment_ids.mapped(
lambda p: {
"date": p.date,
"name": p.name,
"ref": p.ref,
"credit": p.credit,
"class": "",
}
if not p.move_id.move_type in ("out_invoice", "out_refund")
else {
"date": p.date,
"name": p.name,
"ref": "",
"credit": p.credit,
"class": "text-danger",
}
)
payments_html = self._get_html_table(payments)
if not payments_html:
payments_html = (
"<p>Il n'y a pas de paiements associés à cette cotisation</p>"
)
return payments_html
def get_invoice_contribution(self):
invoice_ids = (
self.env["account.move"]
.sudo()
.search(
self._get_contribution_domain()
)
)
return invoice_ids
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 class='%s'>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class='text-right'>%.2f €</td>
</tr>
""" % (
payment.get("class", ""),
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
def _get_contribution_domain(self):
return [
("year", "=", int(self.year)),
("partner_id", "=", self.partner_id.id),
(
"type_contribution_id",
"=",
self.type_contribution_id.id,
),
("move_type", "=", "out_invoice"),
("state", "=", "posted"),
]