Sélectionner une révision Git
lefilament_tdb.py 7,56 Kio
# -*- coding: utf-8 -*-
from openerp import models, fields, api
from openerp import tools
from datetime import datetime
class LeFilamentTdb(models.Model):
""" SAPOVAL DASHBOARD """
_name = "lefilament.dashboard"
_description = "Le Filament Dashboard"
_order = "date_tdb desc"
name = fields.Char( 'Mois', compute='get_month')
date_tdb = fields.Date( 'Date', required=True, default=datetime.today().strftime('%Y-%m-%d'),)
ca_mois = fields.Float( 'Facturé', compute="dashboard_values", store=True)
cmd_mois = fields.Float( 'Commandes', compute="dashboard_values", store=True)
pipe_mois = fields.Float( 'Pipe', compute="dashboard_values", store=True )
treso = fields.Float( 'Trésorerie', compute="dashboard_values", store=True )
variation = fields.Float( 'Variation', compute="dashboard_values", store=True )
charges = fields.Float( 'Décaissé', compute="dashboard_values", store=True )
encaisse = fields.Float( 'Encaissé', compute="dashboard_values", store=True )
charges_fixes = fields.Float( 'Charges Fixes', default=10000 )
runway = fields.Float( 'Runway', compute="runway_value", )
@api.multi
@api.depends('date_tdb')
def dashboard_values(self):
for record in self:
if record.date_tdb :
date_tdb = datetime.strptime(record.date_tdb, '%Y-%m-%d')
############## CA ################
# FACTURÉ
self.env.cr.execute("select sum(amount_untaxed) from account_invoice where state!='draft' and type='out_invoice' and date >= date_trunc('month', %s) and date < date_trunc('month', %s + interval '1' month);", (date_tdb, date_tdb) )
ca_mois = self.env.cr.fetchone()[0]
############## COMMANDES ################
# TOTAL
self.env.cr.execute("select sum(amount_untaxed) from sale_order where invoice_status!='no' and date_order >= date_trunc('month', %s) and date_order < date_trunc('month', %s + interval '1' month);", (date_tdb, date_tdb) )
cmd_mois = self.env.cr.fetchone()[0]
############## PIPE ################
# TOTAL
self.env.cr.execute("select sum(planned_revenue*probability/100) from crm_lead where active=True;")
pipe = self.env.cr.fetchone()[0]
############## TRESO ################
# Trésorerie
self.env.cr.execute("select sum(amount) from account_bank_statement_line where date < date_trunc('month', %s + interval '1' month);", (date_tdb, ) )
treso_total = self.env.cr.fetchone()[0]
# CHARGES
self.env.cr.execute("select sum(amount) from account_bank_statement_line where amount < 0 and date >= date_trunc('month', %s) and date < date_trunc('month', %s + interval '1' month);", (date_tdb, date_tdb) )
charges = self.env.cr.fetchone()[0]
# ENCAISSE
self.env.cr.execute("select sum(amount) from account_bank_statement_line where amount > 0 and date >= date_trunc('month', %s) and date < date_trunc('month', %s + interval '1' month);", (date_tdb, date_tdb) )
encaisse = self.env.cr.fetchone()[0]
############## CHARGES ################
self.env.cr.execute("select charges_fixes from res_company" )
charges_fixes = self.env.cr.fetchone()[0]
if not encaisse:
encaisse = 0
if not charges:
charges = 0
record.ca_mois = ca_mois
record.cmd_mois = cmd_mois
record.pipe_mois = pipe
record.treso = treso_total
record.charges = charges * (-1.0)
record.encaisse = encaisse
record.variation = encaisse + charges
record.charges_fixes = charges_fixes
@api.multi
@api.depends('charges_fixes','treso')
def runway_value(self):
for record in self:
if record.charges_fixes :
record.runway = record.treso / record.charges_fixes
@api.one
def get_month(self):
months = ['Janv', 'Fév', 'Mars', 'Avr', 'Mai', 'Juin', 'Juil', 'Août', 'Sept', 'Oct', 'Nov', 'Dec']
date_tdb = self.date_tdb
month = int(date_tdb[5:7])
year = date_tdb[2:4]
self.name = months[month-1] + " " + year
@api.model
def new_data(self):
self.create({ 'date_tdb': str(datetime.now()) })
def retrieve_datas_dashboard(self, cr, uid, context=None):
res = {
'facture': 0,
'commandes': 0,
'pipe': 0,
'tresorerie': 0,
'entree': 0,
'sortie': 0,
'variation': 0,
'target': 100000,
'cca': 0,
'capital': 0,
}
cr.execute("""
SELECT
(select count(*) from account_invoice) as id,
(select sum(amount_untaxed) from account_invoice where state!='draft' and type='out_invoice' and date >= date_trunc('year', current_date) ) as facture,
(select sum(residual_company_signed) from account_invoice where state!='draft' and type='out_invoice' ) as a_encaisser,
(select sum(planned_revenue*probability/100) from crm_lead where active=True) as pipe,
(select sum(amount) from account_bank_statement_line ) as tresorerie,
(select sum(amount) from account_bank_statement_line where amount > 0 and date >= date_trunc('year', current_date) ) as entree,
(select sum(amount) from account_bank_statement_line where amount < 0 and date >= date_trunc('year', current_date) ) as sortie,
(select sum(amount) from account_bank_statement_line where date >= date_trunc('year', current_date) ) as variation,
(select sum(total_amount) from hr_expense where payment_mode='own_account' and state!='done' ) as cca,
(select sum(price_subtotal-qty_invoiced*price_unit) from sale_order_line where invoice_status='to invoice') as commandes; """)
datas = cr.dictfetchall()
cr.execute("select ca_target from res_company;")
ca_target = cr.dictfetchall()
cr.execute("select sum(capital) as capital from hr_employee;")
capital = cr.dictfetchall()
res['facture'] =+ datas[0]['facture']
res['a_encaisser'] =+ datas[0]['a_encaisser']
res['pipe'] =+ datas[0]['pipe']
res['tresorerie'] =+ datas[0]['tresorerie']
res['entree'] =+ datas[0]['entree']
res['sortie'] =+ datas[0]['sortie']
res['variation'] =+ datas[0]['variation']
res['commandes'] =+ datas[0]['commandes']
res['cca'] =+ datas[0]['cca']
res['target'] =+ ca_target[0]['ca_target']
res['capital'] =+ capital[0]['capital']
return res
class LeFilamentData(models.Model):
_name = "lefilament.dashboard.data"
_auto = False
_description = "Donnees Annuelles"
facture = fields.Float("Facturé")
pipe = fields.Float("Pipe")
tresorerie = fields.Float("Trésorerie")
commandes = fields.Float("Commandes en cours")
entree = fields.Float("Entrées")
sortie = fields.Float("Sorties")
variation = fields.Float("Variation")
def init(self, cr):
tools.drop_view_if_exists(cr, 'lefilament_dashboard_data')
cr.execute("""
CREATE OR REPLACE VIEW lefilament_dashboard_data AS (
select
(select count(*) from account_invoice) as id,
(select sum(amount_untaxed) from account_invoice where state!='draft' and type='out_invoice' and date >= date_trunc('year', current_date) ) as facture,
(select sum(planned_revenue*probability/100) from crm_lead where active=True) as pipe,
(select sum(amount) from account_bank_statement_line ) as tresorerie,
(select sum(amount) from account_bank_statement_line where amount > 0 and date >= date_trunc('year', current_date) ) as entree,
(select sum(amount) from account_bank_statement_line where amount < 0 and date >= date_trunc('year', current_date) ) as sortie,
(select sum(amount) from account_bank_statement_line where date >= date_trunc('year', current_date) ) as variation,
(select sum(price_subtotal-qty_invoiced*price_unit) from sale_order_line where invoice_status='to invoice') as commandes); """)