Sélectionner une révision Git
lefilament_tdb.py 14,06 Kio
# © 2019 Le Filament (<http://www.le-filament.com>)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html).
from datetime import date
from dateutil.relativedelta import relativedelta
from psycopg2._psycopg import AsIs
from odoo import api, fields, models
class LeFilamentTdb(models.Model):
_name = "lefilament.dashboard"
_description = "Le Filament Dashboard"
_order = "date_tdb desc"
name = fields.Char("Mois", compute="_compute_get_month", store=True)
date_tdb = fields.Date(
string="Date", required=True, default=lambda d: fields.Date.today(), store=True
)
ca_mois = fields.Float("Facturé", compute="_compute_dashboard_values", store=True)
cmd_mois = fields.Float(
"Commandes", compute="_compute_dashboard_values", store=True
)
pipe_mois = fields.Float("Pipe", compute="_compute_dashboard_values", store=True)
treso = fields.Float("Trésorerie", compute="_compute_dashboard_values", store=True)
variation = fields.Float(compute="_compute_dashboard_values", store=True)
charges = fields.Float("Décaissé", compute="_compute_dashboard_values", store=True)
encaisse = fields.Float("Encaissé", compute="_compute_dashboard_values", store=True)
charges_fixes = fields.Float(default=10000)
runway = fields.Float(compute="_compute_runway_value")
# ------------------------------------------------------
# Compute function
# ------------------------------------------------------
@api.depends("date_tdb")
def _compute_dashboard_values(self):
for record in self:
if record.date_tdb:
date_start = date(record.date_tdb.year, record.date_tdb.month, 1)
date_end = date(
record.date_tdb.year, record.date_tdb.month, 1
) + relativedelta(months=1)
# FACTURÉ
invoice_ids = self.env["account.move"].search(
[
("state", "=", "posted"),
("move_type", "in", ("out_invoice", "out_refund")),
("invoice_date", ">=", date_start),
("invoice_date", "<", date_end),
]
)
ca_mois = sum(invoice_ids.mapped("amount_untaxed_signed"))
# COMMANDES TOTAL
order_ids = self.env["sale.order"].search(
[
("invoice_status", "=", "to invoice"),
("date_order", ">=", date_start),
("date_order", "<", date_end),
]
)
cmd_mois = sum(order_ids.mapped("amount_untaxed"))
# Trésorerie
statement_line_ids = self.env["account.bank.statement.line"].search(
[
("date", "<", date_end),
]
)
treso_total = sum(statement_line_ids.mapped("amount"))
# CHARGES
negative_line_ids = self.env["account.bank.statement.line"].search(
[
("amount", "<", 0),
("date", ">=", date_start),
("date", "<", date_end),
]
)
charges = sum(negative_line_ids.mapped("amount"))
# ENCAISSE
positive_line_ids = self.env["account.bank.statement.line"].search(
[
("amount", ">", 0),
("date", ">=", date_start),
("date", "<", date_end),
]
)
encaisse = sum(positive_line_ids.mapped("amount"))
# CHARGES FIXES
charges_fixes = self.env.company.charges_fixes
# PIPE
opportunity_ids = self.env["crm.lead"].search([])
pipe = sum(
opportunity_ids.mapped(
lambda o: o.expected_revenue * o.probability / 100
)
)
if not encaisse:
encaisse = 0
if not charges:
charges = 0
record.ca_mois = ca_mois
record.cmd_mois = cmd_mois
record.treso = treso_total
record.charges = charges * (-1.0)
record.encaisse = encaisse
record.variation = encaisse + charges
record.charges_fixes = charges_fixes
record.pipe_mois = pipe
@api.depends("charges_fixes", "treso")
def _compute_runway_value(self):
for record in self:
if record.charges_fixes:
record.runway = record.treso / record.charges_fixes
else:
record.runway = 0
@api.depends("date_tdb")
def _compute_get_month(self):
for record in self:
months = [
"Janv",
"Fév",
"Mars",
"Avr",
"Mai",
"Juin",
"Juil",
"Août",
"Sept",
"Oct",
"Nov",
"Dec",
]
record.name = (
months[record.date_tdb.month - 1] + " " + str(record.date_tdb.year)
)
# ------------------------------------------------------
# Action buttons
# ------------------------------------------------------
# ------------------------------------------------------
# Business function
# ------------------------------------------------------
def get_month_values(self):
for data in self:
data._compute_dashboard_values()
@staticmethod
def _format_monetary(amount):
return f"{amount:,}".replace(",", " ") + " €"
@api.model
def dashboard_detail_values(self, date_start=None, date_end=None):
customer = self._customer_detail(date_start, date_end)
employee_time = self._employee_time(date_start, date_end)
return {
"customer": customer,
"customer_totals": self._compute_totals(customer),
"employee_time": employee_time,
"employee_time_totals": self._compute_totals(employee_time),
}
def _customer_detail(self, date_start=None, date_end=None):
clause = "where 1=1 "
if date_start:
clause += f"and line_date >= '{date_start}' "
if date_end:
clause += f"and line_date <= '{date_end}' "
query = """
select
customer as "Client",
sum(prod) as "Imput.",
sum(invoiced + purchase + expense)::int as "Balance Prod",
sum(invoiced + purchase + expense)/NULLIF(sum(prod), 0) as "Taux Horaire",
sum(invoiced + invoiced_mco) as "Tot. Fact.",
sum(invoiced)::int as "Fact. Prod",
sum(invoiced_mco)::int as "Fact. Maint",
sum(purchase)::int as "Achats",
sum(expense)::int as "NdF"
from
(
-- Sélection des heures
select
aal.date as line_date,
p.name as customer,
-- contact != Filament et projet != Maintenance et pas flagué vacances
case when aal.partner_id != 1 or aal.partner_id is null
and aal.holiday_id is null and project_id != 19
then unit_amount else 0 end as prod,
0 as invoiced,
0 as invoiced_mco,
0 as purchase,
0 as expense
from
account_analytic_line aal
left join
res_partner p on aal.partner_id = p.id
left join
hr_leave l on aal.holiday_id = l.id
left join
hr_leave_type lt on l.holiday_status_id = lt.id
where
aal.project_id is not null
and aal.date <= CURRENT_DATE
and (lt.active is true or lt.active is null)
and partner_id != 1
-- Sélection du facturé hors maintenance
union all
select
aml.date as line_date,
p.name as customer,
0 as prod,
(aml.credit - aml.debit) as invoiced,
0 as invoiced_mco,
0 as purchase,
0 as expense
from account_move_line aml
left join account_move i on aml.move_id = i.id
left join res_partner p on i.beneficiary_id = p.id
where
i.move_type in ('out_invoice', 'out_refund')
and i.state = 'posted'
and aml.product_id is not null
and aml.product_id not in (33, 34, 50, 51, 61, 62)
-- Sélection du facturé Maintenance
union all
select
aml.date as line_date,
p.name as customer,
0 as prod,
0 as invoiced,
(aml.credit - aml.debit) as invoiced_mco,
0 as purchase,
0 as expense
from account_move_line aml
left join account_move i on aml.move_id = i.id
left join res_partner p on i.beneficiary_id = p.id
where
i.move_type in ('out_invoice', 'out_refund')
and i.state = 'posted'
and aml.product_id is not null
and aml.product_id in (33, 34, 50, 51, 61, 62)
-- Sélection des charges
union all
select
aal.date as line_date,
p.name as customer,
0 as prod,
0 as invoiced,
0 as invoiced_mco,
amount as purchase,
0 as expense
from account_analytic_line aal
left join account_move_line aml on aal.move_line_id = aml.id
left join account_move i on aml.move_id = i.id
left join account_analytic_account a on aal.account_id = a.id
left join res_partner p on a.partner_id = p.id
where
(a.plan_id is null or a.plan_id = 1)
and i.state = 'posted'
and aml.journal_id = 2
-- Sélection des NDF
union all
select
aal.date as line_date,
p.name as customer,
0 as prod,
0 as invoiced,
0 as invoiced_mco,
0 as purchase,
amount as expense
from account_analytic_line aal
left join account_move_line aml on aal.move_line_id = aml.id
left join account_move i on aml.move_id = i.id
left join account_analytic_account a on aal.account_id = a.id
left join res_partner p on a.partner_id = p.id
where
(a.plan_id is null or a.plan_id = 1)
and i.state = 'posted'
and aml.journal_id = 9
) query
%s
group by
customer
order by
sum(invoiced) desc
"""
self.env.cr.execute(query, (AsIs(clause),))
return self.env.cr.dictfetchall()
def _employee_time(self, date_start=None, date_end=None):
clause = ""
if date_start:
clause += f" and aal.date >= '{date_start}'"
if date_end:
clause += f" and aal.date <= '{date_end}'"
query = """
select
employee as "Employé",
sum(production) as "Prod",
sum(internal) as "Interne",
sum(revenue) as "CA"
from (
select
p.name as employee,
0 as production,
0 as internal,
sum(aal.amount) as revenue
from
account_analytic_line aal
left join
account_analytic_account aa on aal.account_id = aa.id
left join
account_analytic_plan aap on aa.plan_id = aap.id
left join
res_partner p on aa.partner_id = p.id
where
aap.id = 3
%s
group by p.name
union
select
e.name as employee,
sum(case when aal.partner_id != 1 or aal.partner_id is null
and aal.holiday_id is null then unit_amount else 0 end)
as production,
sum(case when aal.partner_id = 1 and aal.holiday_id is null
then unit_amount else 0 end)
as internal,
0 as revenue
from
account_analytic_line aal
left join
hr_employee e on aal.employee_id = e.id
left join
hr_leave l on aal.holiday_id = l.id
left join
hr_leave_type lt on l.holiday_status_id = lt.id
where
aal.project_id is not null
%s
group by e.name
) query
group by employee
order by sum(production) desc
"""
self.env.cr.execute(
query,
(
AsIs(clause),
AsIs(clause),
),
)
return self.env.cr.dictfetchall()
@staticmethod
def _compute_totals(result):
if not isinstance(result, list) or not result:
return {}
last_result = result[-1]
totals = {}
for column in last_result.keys():
if isinstance(last_result.get(column), int | float):
totals.update(
{
column: sum(
list(
map(
lambda d: d.get(column) if d.get(column) else 0.0,
result,
)
)
)
}
)
else:
totals.update({column: False})
return totals