class ProjectMilestoneInvoicing(models.Model):
_name = "budget.milestone.invoicing"
_auto = False

name = fields.Char(string='Libellé')
budgetary_position_id = fields.Char(string='POS.Budg', index=True)
asset_category_id = fields.Char(string='Type')
montant_devise = fields.Float(string='Montant budget (EUR)',digits=(12,2))
montant_budget = fields.Float(string='Montant budget (TND)',digits=(12,3))
compte_comptable = fields.Char(string='Compte comptable')
price_unit = fields.Float(string='Montant réel (TND)',digits=(12,3))
ecart_amount = fields.Float(string='Ecart',digits=(12,3))
suivi_ecart = fields.Float(string='Ecart %', group_operator='avg',digits=(12,1))
facture_name = fields.Char(string='N° Facture')
date_facture = fields.Char(string='Date Facture')
fournisseur = fields.Char(string='Fournisseur')
annee = fields.Date(string='annee')



def init(self):
print("Connected")
print(self.test(1))
tools.drop_view_if_exists(self._cr, 'budget_milestone_invoicing')
self._cr.execute("""

CREATE OR REPLACE VIEW budget_milestone_invoicing AS (
SELECT
MIN(bl.id) AS id,
MAX(fl.name) as name,
bl.name as budgetary_position_id,
COALESCE (bl.invest_type, fl.asset_category_id) as asset_category_id,
MAX(bl.devise_amount) as montant_devise,
MAX(bl.planned_amount) as montant_budget,
MAX(fl.account_id) as compte_comptable,
SUM(fl.price_unit) as price_unit,
COALESCE(MAX(bl.planned_amount),0)
-COALESCE(SUM(fl.price_unit),0)
as ecart_amount,
0.01*(COALESCE(100*(COALESCE(MAX(bl.planned_amount),0)
-COALESCE(SUM(fl.price_unit),0))
/ MAX(bl.planned_amount), MAX(fl.suivi_ecart))) as suivi_ecart,
string_agg(DISTINCT fl.facture_name, ',') as facture_name,
string_agg(DISTINCT TO_CHAR(fl.date_facture, 'DD/MM/YYYY'), ',') as date_facture,
string_agg(DISTINCT fl.fournisseur, ',') as fournisseur,
COALESCE (bl.date_suivi, fl.date_facture) as annee

FROM crossovered_budget_lines bl
Full JOIN account_move_line fl ON fl.budgetary_position_id = bl.id
GROUP BY bl.name,COALESCE (bl.invest_type, fl.asset_category_id),COALESCE (bl.date_suivi, fl.date_facture)

""")