§
    ›ˆ›j86  ã                  ó|   — d Z ddlmZ ddlmZmZmZ ddlmZ ddlm	Z	 ddl
mZmZmZmZmZmZ  G d„ d¦  «        Zd	S )
zBDomain Service for Executive Financial War Room & Cashflow Runway.é    )Úannotations)ÚdateÚdatetimeÚ	timedelta)ÚDecimal)ÚAny)ÚBusinessPillarEnumÚCashflowForecastDayÚCashflowRunwaySummaryÚContractMarginAlertÚExecutiveWarRoomDataÚPillarFinancialSummaryc                  ó0   — e Zd ZdZddd„Z	 ddd„Zdd„ZdS )ÚFinancialWarRoomServicezRCalculates consolidated financial metrics, 4-pillar P&L, and cashflow projections.NÚpostgres_clientú
Any | NoneÚreturnÚNonec                ó   — || _         d S ©N)r   )Úselfr   s     úRC:\Projects\DSCons\app\modules\financial\application\financial_war_room_service.pyÚ__init__z FinancialWarRoomService.__init__   s   € Ø.ˆÔÐÐó    r   Ú
as_of_dateúdate | NoneÚclient_delay_daysÚintr   c                ó  — |pt          j        ¦   «         }t          t          j        dt          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        dd	¬
¦
  «
        }t          t          j        dt          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        dd	¬
¦
  «
        }t          t          j        dt          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        dd	¬
¦
  «
        }t          t          j        dt          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d¦  «        t          d ¦  «        d!d"¬
¦
  «
        }||||g}t          d#„ |D ¦   «         t          d$¦  «        ¦  «        }	t          d%„ |D ¦   «         t          d$¦  «        ¦  «        }
|	|
z
  }|	t          d$¦  «        k    r5||	z  t          d&¦  «        z   
                    t          d'¦  «        ¦  «        nt          d$¦  «        }t          d(¦  «        }|t          d)¬*¦  «        z   }g }| j        r0t          | j        d+¦  «        r| j                             ||¦  «        }d,d-lm}  |t
          ¦  «        } |t
          ¦  «        }|r„|D ]€}|d.         }|d/         }|d0         }t          t#          |d1         ¦  «        ¦  «        }|d2k    r|d3v r|t          |¬*¦  «        z   }|d2k    r||xx         |z  cc<   Œp||xx         |z  cc<   Œ�n�t%          d4d5¦  «        D ]|}|t          |¬*¦  «        z   }t          d6¦  «        ||<   |d7v r0|t          |¬*¦  «        z   }||xx         t          d8¦  «        z  cc<   |d9v r||xx         t          d:¦  «        z  cc<   Œ}g }|}t          d$¦  «        }t          d$¦  «        }|}d }d;} d)}!t%          d4d<¦  «        D ]ç}|t          |¬*¦  «        z   }"|                     |"t          d$¦  «        ¦  «        }#|                     |"t          d$¦  «        ¦  «        }$|d=k    r
||#z  }||$z  }|#|$z
  }%||%z  }t)          ||¦  «        }|d,k     r| sd>} |"                     ¦   «         }|d4z
  }!|d=k    r9|                     t/          |"                     ¦   «         |#|$|%|¬?¦  «        ¦  «         Œèt1          ||||!|| |||¬@¦	  «	        }&t3          dAdBdCt          dD¦  «        t          dE¦  «        t          dF¦  «        t          dG¦  «        t          dH¦  «        dI¬J¦	  «	        t3          dKdLdMt          dN¦  «        t          dO¦  «        t          dP¦  «        t          dQ¦  «        t          dR¦  «        dSdT¬U¦
  «
        t3          dVdWdXt          dY¦  «        t          dZ¦  «        t          d[¦  «        t          d¦  «        t          dG¦  «        dI¬J¦	  «	        g}'t5          |t7          j        ¦   «         |	|
||||&|'¬\¦	  «	        S )]Nu5   Thi CÃ´ng XÃ¢y Láº¯p & Thá»§y Lá»£i (EPC Contracting)z68784100000.0000z56402962000.0000z12381138000.0000z18.0000z8450000000.0000z4120000000.0000é   ÚLOW)
Ú	pillar_idÚpillar_nameÚtotal_revenueÚ
total_costÚgross_profitÚgross_margin_percentageÚreceivables_pendingÚpayables_pendingÚactive_contracts_countÚ
risk_levelu6   Dá»‹ch Vá»¥ Váº­n Táº£i, Xe Ben & Logistics CÆ¡ Giá»›iz6760000000.0000z1690000000.0000z20.0000z1250000000.0000z820000000.0000é   u5   Cho ThuÃª MÃ¡y MÃ³c Thiáº¿t Bá»‹ & Cung Cáº¥p Ca MÃ¡yz4200000000.0000z3150000000.0000z1050000000.0000z25.0000z580000000.0000z310000000.0000é   u+   Mua BÃ¡n Váº­t TÆ° & San Láº¥p Máº·t Báº±ngz14800000000.0000z13024000000.0000z1776000000.0000z12.0000z2100000000.0000z3450000000.0000é   ÚMEDIUMc              3  ó$   K  — | ]}|j         V — Œd S r   )r$   ©Ú.0Úps     r   ú	<genexpr>z?FinancialWarRoomService.get_war_room_summary.<locals>.<genexpr>Y   s$   è è € Ð>Ð>°˜Qœ_Ð>Ð>Ð>Ð>Ð>Ð>r   z0.0000c              3  ó$   K  — | ]}|j         V — Œd S r   )r%   r1   s     r   r4   z?FinancialWarRoomService.get_war_room_summary.<locals>.<genexpr>Z   s$   è è € Ð8Ð8¨1˜!œ,Ð8Ð8Ð8Ð8Ð8Ð8r   z100.0000z0.0001z4850000000.0000éZ   )ÚdaysÚfetch_planned_transactionsr   )ÚdefaultdictÚtransaction_dateÚtransaction_typeÚ	directionÚamountÚinflow)Ústage_paymentÚretention_releaseé   é   z75000000.0000)é   r    é   z650000000.0000)é
   é   z480000000.0000Fé[   é   T)Údate_strÚexpected_inflowÚexpected_outflowÚ	net_dailyÚprojected_balance)	Úcurrent_cash_balanceÚtotal_expected_inflow_30dÚtotal_expected_outflow_30dÚnet_runway_daysÚstress_test_delay_daysÚliquidity_risk_alertÚlowest_balanceÚ	risk_dateÚforecast_30dzDA-DADO-THUYLDT-2022-2026u0   GÃ³i Tháº§u Sá»‘ 04 Thá»§y Lá»£i SÃ´ng Ä�a Ä�á»™u(   Cty TNHH MTV KTCT Thá»§y Lá»£i Ä�a Ä�á»™z34460000000.0000z28500000000.0000z23085000000.0000z19.0000z18.5000ÚNORMAL)	Úproject_codeÚproject_nameÚclient_nameÚcontract_valueÚaccumulated_billedÚaccumulated_costÚcurrent_margin_pctÚplanned_margin_pctÚalert_statuszDA-KM-BENKEM-2025-2026uF   Há»‡ Thá»‘ng Cá»‘ng Há»™p KÃªnh Báº¿n Kem & ThoÃ¡t NÆ°á»›c Kiáº¿n Minhu   UBND XÃ£ Kiáº¿n Minhz14080000000.0000z6200000000.0000z5208000000.0000z16.0000z17.0000ÚWARNING_DILUTIONu>   GiÃ¡ cÃ¡t Ä‘Ã¡ san láº¥p Ä‘áº§u vÃ o tÄƒng nháº¹ trong Q2/2026)
rX   rY   rZ   r[   r\   r]   r^   r_   r`   Ú
root_causezDA-RANGDONG-HTDT-2022-2026uE   Thi CÃ´ng XÃ¢y Láº¯p & San Láº¥p Háº¡ Táº§ng Ä�Ã´ Thá»‹ Ráº¡ng Ä�Ã´ngu    Cty CP XÃ¢y Dá»±ng Ráº¡ng Ä�Ã´ngz10380000000.0000z9100000000.0000z7280000000.0000)	r   Úgenerated_atÚtotal_revenue_ytdÚtotal_cost_ytdÚtotal_gross_profit_ytdÚoverall_margin_percentageÚpillars_breakdownÚcashflow_runwayÚmargin_alerts)r   Útodayr   r	   ÚPILLAR_1_EPCr   ÚPILLAR_2_FLEETÚPILLAR_3_EQUIPMENTÚPILLAR_4_MATERIALSÚsumÚquantizer   r   Úhasattrr8   Úcollectionsr9   ÚstrÚrangeÚgetÚminÚ	isoformatÚappendr
   r   r   r   r   Únow)(r   r   r   Útarget_dateÚpillar1Úpillar2Úpillar3Úpillar4Úpillarsr$   r%   Útotal_gross_profitÚoverall_marginÚcurrent_cashÚend_dateÚraw_txsr9   Údaily_inflowsÚdaily_outflowsÚtxÚtx_dateÚtx_typer<   r=   ÚiÚinflow_dateÚforecast_daysÚrunning_balanceÚtotal_inflow_30dÚtotal_outflow_30drT   rU   rS   rQ   Ú	curr_dater>   ÚoutflowrL   Úcashflow_summaryrj   s(                                           r   Úget_war_room_summaryz,FinancialWarRoomService.get_war_room_summary   s­  € ð !Ð0¥D¤J¡L¤Lˆõ )Ý(Ô5ØOÝ!Ð"4Ñ5Ô5ÝÐ1Ñ2Ô2Ý Ð!3Ñ4Ô4Ý$+¨IÑ$6Ô$6Ý 'Ð(9Ñ :Ô :Ý$Ð%6Ñ7Ô7Ø#%Øð
ñ 
ô 
ˆõ )Ý(Ô7ØPÝ!Ð"3Ñ4Ô4ÝÐ0Ñ1Ô1Ý Ð!2Ñ3Ô3Ý$+¨IÑ$6Ô$6Ý 'Ð(9Ñ :Ô :Ý$Ð%5Ñ6Ô6Ø#$Øð
ñ 
ô 
ˆõ )Ý(Ô;ØOÝ!Ð"3Ñ4Ô4ÝÐ0Ñ1Ô1Ý Ð!2Ñ3Ô3Ý$+¨IÑ$6Ô$6Ý 'Ð(8Ñ 9Ô 9Ý$Ð%5Ñ6Ô6Ø#$Øð
ñ 
ô 
ˆõ )Ý(Ô;ØEÝ!Ð"4Ñ5Ô5ÝÐ1Ñ2Ô2Ý Ð!2Ñ3Ô3Ý$+¨IÑ$6Ô$6Ý 'Ð(9Ñ :Ô :Ý$Ð%6Ñ7Ô7Ø#$Øð
ñ 
ô 
ˆð ˜G W¨gÐ6ˆõ Ð>Ð>°gÐ>Ñ>Ô>ÅÈÑ@QÔ@QÑRÔRˆÝÐ8Ð8°Ð8Ñ8Ô8½'À(Ñ:KÔ:KÑLÔLˆ
Ø*¨ZÑ7Ðð
 �w xÑ0Ô0Ò0Ð0ð   -Ñ/µ'¸*Ñ2EÔ2EÑE×OÒOÝ˜Ñ!Ô!ñô ð õ ˜Ñ"Ô"ð 	õ Ð0Ñ1Ô1ˆð ¥Øð"
ñ "
ô "
ñ 
ˆð ˆØÔð 	¥GØÔ Ð">ñ%
ô %
ð 	ð Ô*×EÒEØ˜Xñô ˆGð 	,Ð+Ð+Ð+Ð+Ð+à#˜¥GÑ,Ô,ˆØ$˜¥WÑ-Ô-ˆàð 	IØð 6ð 6�ØÐ/Ô0�ØÐ/Ô0�Ø˜{œO�	Ý ¥ R¨¤\Ñ!2Ô!2Ñ3Ô3�à Ò(Ð(¨Wð 9ð .ð .ð
 &­	Ð7HÐ(IÑ(IÔ(IÑI�Gð  Ò(Ð(Ø! 'Ð*Ð*Ô*¨fÑ4Ð*Ð*Ñ*Ð*à" 7Ð+Ð+Ô+¨vÑ5Ð+Ð+Ñ+Ð+ð#6õ( ˜1˜b‘\”\ð 	Ið 	I�Ø%­	°qÐ(9Ñ(9Ô(9Ñ9�Ý*1°/Ñ*BÔ*B�˜wÑ'à˜Ð#Ð#à")­IÐ;LÐ,MÑ,MÔ,MÑ"M�KØ! +Ð.Ð.Ô.µ'Ð:JÑ2KÔ2KÑKÐ.Ð.Ñ.Ø˜�=�=Ø" 7Ð+Ð+Ô+­wÐ7GÑ/HÔ/HÑHÐ+Ð+Ñ+øà35ˆØ&ˆÝ" 8Ñ,Ô,ÐÝ# HÑ-Ô-Ðà%ˆØˆ	Ø$ÐØˆõ �q˜"‘”ð 	ð 	ˆAØ#¥i°QÐ&7Ñ&7Ô&7Ñ7ˆIØ"×&Ò& yµ'¸(Ñ2CÔ2CÑDÔDˆFØ$×(Ò(¨µG¸HÑ4EÔ4EÑFÔFˆGà�BŠwˆwØ  FÑ*Ð Ø! WÑ,Ð!à Ñ(ˆIØ˜yÑ(ˆOå  °ÑAÔAˆNà Ò"Ð"Ð+?Ð"Ø'+Ð$Ø%×/Ò/Ñ1Ô1�	Ø"# a¡%�à�BŠwˆwØ×$Ò$Ý'Ø!*×!4Ò!4Ñ!6Ô!6Ø(.Ø)0Ø"+Ø*9ðñ ô ñô ð øõ 1Ø!-Ø&6Ø'8Ø+Ø#4Ø!5Ø)ØØ&ð

ñ 

ô 

Ðõ  Ø8ØOØFÝ&Ð'9Ñ:Ô:Ý#*Ð+=Ñ#>Ô#>Ý!(Ð);Ñ!<Ô!<Ý#*¨9Ñ#5Ô#5Ý#*¨9Ñ#5Ô#5Ø%ð
ñ 
ô 
õ  Ø5ØeØ2Ý&Ð'9Ñ:Ô:Ý#*Ð+<Ñ#=Ô#=Ý!(Ð):Ñ!;Ô!;Ý#*¨9Ñ#5Ô#5Ý#*¨9Ñ#5Ô#5Ø/Ø[ðñ ô õ  Ø9ØdØ>Ý&Ð'9Ñ:Ô:Ý#*Ð+<Ñ#=Ô#=Ý!(Ð):Ñ!;Ô!;Ý#*¨9Ñ#5Ô#5Ý#*¨9Ñ#5Ô#5Ø%ð
ñ 
ô 
ð1#
ˆõJ $Ø"Ý!œ™œØ+Ø%Ø#5Ø&4Ø%Ø,Ø'ð

ñ 

ô 

ð 
	
r   rX   rt   údict[str, Any]c                ó(  — ddl m} | j        p	 |¦   «         }d}	 |j                             ¦   «         5 }|                     ¦   «         5 }|                     ||f¦  «         |                     ¦   «         }ddd¦  «         n# 1 swxY w Y   ddd¦  «         n# 1 swxY w Y   n# t          $ r d}Y nw xY w|sddiS t          | 
                    d¦  «        pd¦  «        }| 
                    d¦  «        }	| 
                    d	¦  «        }
d
}|	rY|
rW|dk    rQ|
|	z
  j        }|dk    rAt          j        ¦   «         |	z
  j        }t          dt          ||¦  «        ¦  «        }|||z  z  }d}d}d
}d
}	 |j                             ¦   «         5 }|                     ¦   «         5 }|                     ||f¦  «         |                     ¦   «         }|r@t          t!          |t"          ¦  «        r| 
                    dd¦  «        n|d         ¦  «        }|                     ||f¦  «         |                     ¦   «         }|r@t          t!          |t"          ¦  «        r| 
                    dd¦  «        n|d         ¦  «        }ddd¦  «         n# 1 swxY w Y   ddd¦  «         n# 1 swxY w Y   n# t          $ r Y nw xY w|dk    rt%          ||z  d¦  «        nd}|dk    rt%          ||z  d¦  «        nd}||d         |||||dœ|dk    rdnd|dk    rdnddœdœS )uT   TÃ­nh toÃ¡n chá»‰ sá»‘ EVM (Earned Value Management) gá»“m SPI vÃ  CPI cho dá»± Ã¡n.r   )ÚBasePostgresClientzeSELECT project_name, total_budget_vnd, start_date, end_date FROM erp_projects WHERE project_code = %sNÚerroru   Dá»± Ã¡n khÃ´ng tá»“n táº¡i.Útotal_budget_vndÚ
start_dater„   g        z…SELECT COALESCE(SUM(total_amount_vnd), 0) as ev FROM erp_invoices WHERE project_code = %s AND status = 'approved' AND type = 'output'z„SELECT COALESCE(SUM(total_amount_vnd), 0) as ac FROM erp_invoices WHERE project_code = %s AND status = 'approved' AND type = 'input'ÚevÚacé   rY   )ÚEVÚACÚPVÚSPIÚCPIrA   ÚAHEADÚBEHINDÚUNDER_BUDGETÚOVER_BUDGET)ÚscheduleÚcost)rX   rY   ÚmetricsÚhealth)Ú&app.core.postgres.base_pkg.base_clientr—   r   ÚpoolÚ
connectionÚcursorÚexecuteÚfetchoneÚ	ExceptionÚfloatrv   r7   r   rk   Úmaxrw   Ú
isinstanceÚdictÚround)r   rX   r—   ÚclientÚsqlÚconnÚcurÚprojÚtotal_budgetrš   r„   ÚpvÚ
total_daysÚelapsed_daysÚev_sqlÚac_sqlr›   rœ   Úev_rowÚac_rowÚspiÚcpis                         r   Úget_project_evm_metricsz/FinancialWarRoomService.get_project_evm_metrics  s�  € àMÐMÐMÐMÐMÐMàÔ%Ð=Ð);Ð);Ñ)=Ô)=ˆð vˆð	Ø”×'Ò'Ñ)Ô)ð &¨T°4·;²;±=´=ð &ÀCØ—’˜C , Ñ1Ô1Ð1Ø—|’|‘~”~�ð&ð &ð &ñ &ô &ð &ð &ð &ð &ð &ð &øøøð &ð &ð &ð &ð &ð &ð &ñ &ô &ð &ð &ð &ð &ð &ð &øøøð &ð &ð &ð &øøõ ð 	ð 	ð 	ØˆDˆDˆDð	øøøð ð 	=ØÐ;Ð<Ð<å˜TŸXšXÐ&8Ñ9Ô9Ð>¸QÑ?Ô?ˆð —X’X˜lÑ+Ô+ˆ
Ø—8’8˜JÑ'Ô'ˆàˆØð 	@˜(ð 	@ |°aÒ'7Ð'7Ø" ZÑ/Ô5ˆJØ˜AŠ~ˆ~Ý $¤
¡¤¨zÑ 9Ô?�Ý" 1¥c¨,¸
Ñ&CÔ&CÑDÔD�Ø! \°JÑ%>Ñ?�ð Yˆð XˆàˆØˆð	Ø”×'Ò'Ñ)Ô)ð 	_¨T°4·;²;±=´=ð 	_ÀCØ—’˜F \ OÑ4Ô4Ð4ØŸš™œ�Øð _ÝµjÀÍÑ6NÔ6NÐ]˜vŸzšz¨$°Ñ2Ô2Ð2ÐTZÐ[\ÔT]Ñ^Ô^�Bà—’˜F \ OÑ4Ô4Ð4ØŸš™œ�Øð _ÝµjÀÍÑ6NÔ6NÐ]˜vŸzšz¨$°Ñ2Ô2Ð2ÐTZÐ[\ÔT]Ñ^Ô^�Bð	_ð 	_ð 	_ñ 	_ô 	_ð 	_ð 	_ð 	_ð 	_ð 	_ð 	_øøøð 	_ð 	_ð 	_ð 	_ð 	_ð 	_ð 	_ñ 	_ô 	_ð 	_ð 	_ð 	_ð 	_ð 	_ð 	_øøøð 	_ð 	_ð 	_ð 	_øøõ ð 	ð 	ð 	ØˆDð	øøøð $&¨¢6 6�e�B˜‘G˜QÑÔÐ¨qˆØ#%¨¢6 6�e�B˜‘G˜QÑÔÐ¨qˆð )Ø  Ô0àØØØØðð ð (+¨a¢x x˜G˜G°XØ*-°ª(¨(˜˜¸ðð ð
ð 
ð 	
s³   ›B$ ´BÁ	,BÁ5BÂB	ÂBÂB	Â	BÂB$ ÂBÂB$ ÂBÂ B$ Â$B3Â2B3Å0J( Æ	JÆCJÉ9JÊJ		Ê	JÊJ		ÊJÊJ( ÊJ Ê J( Ê#J Ê$J( Ê(
J5Ê4J5r   )r   r   r   r   )Nr   )r   r   r   r   r   r   )rX   rt   r   r•   )Ú__name__Ú
__module__Ú__qualname__Ú__doc__r   r”   rÆ   © r   r   r   r      ss   € € € € € Ø\Ð\ð/ð /ð /ð /ð /ð HIðf
ð f
ð f
ð f
ð f
ðPE
ð E
ð E
ð E
ð E
ð E
r   r   N)rÊ   Ú
__future__r   r   r   r   Údecimalr   Útypingr   Ú%app.models.financial_war_room_schemasr	   r
   r   r   r   r   r   rË   r   r   ú<module>rÐ      së   ðØ HÐ Hà "Ð "Ð "Ð "Ð "Ð "à .Ð .Ð .Ð .Ð .Ð .Ð .Ð .Ð .Ð .Ø Ð Ð Ð Ð Ð Ø Ð Ð Ð Ð Ð ðð ð ð ð ð ð ð ð ð ð ð ð ð ð ð ðs
ð s
ð s
ð s
ð s
ñ s
ô s
ð s
ð s
ð s
r   