"""Domain Service for Executive Financial War Room & Cashflow Runway.""" from __future__ import annotations from datetime import date, datetime, timedelta from decimal import Decimal from typing import Any from app.models.financial_war_room_schemas import ( BusinessPillarEnum, CashflowForecastDay, CashflowRunwaySummary, ContractMarginAlert, ExecutiveWarRoomData, PillarFinancialSummary, ) class FinancialWarRoomService: """Calculates consolidated financial metrics, 4-pillar P&L, and cashflow projections.""" def __init__(self, postgres_client: Any | None = None) -> None: self.postgres_client = postgres_client def get_war_room_summary( self, as_of_date: date | None = None, client_delay_days: int = 0 ) -> ExecutiveWarRoomData: target_date = as_of_date or date.today() # Pillar 1: Thi công Xây lắp & Thủy lợi (EPC Contracting) pillar1 = PillarFinancialSummary( pillar_id=BusinessPillarEnum.PILLAR_1_EPC, pillar_name="Thi Công Xây Lắp & Thủy Lợi (EPC Contracting)", total_revenue=Decimal("68784100000.0000"), total_cost=Decimal("56402962000.0000"), gross_profit=Decimal("12381138000.0000"), gross_margin_percentage=Decimal("18.0000"), receivables_pending=Decimal("8450000000.0000"), payables_pending=Decimal("4120000000.0000"), active_contracts_count=15, risk_level="LOW", ) # Pillar 2: Vận tải Xe Ben & Logistics (Thoát Nước Hải Phòng 0200149705...) pillar2 = PillarFinancialSummary( pillar_id=BusinessPillarEnum.PILLAR_2_FLEET, pillar_name="Dịch Vụ Vận Tải, Xe Ben & Logistics Cơ Giới", total_revenue=Decimal("8450000000.0000"), total_cost=Decimal("6760000000.0000"), gross_profit=Decimal("1690000000.0000"), gross_margin_percentage=Decimal("20.0000"), receivables_pending=Decimal("1250000000.0000"), payables_pending=Decimal("820000000.0000"), active_contracts_count=4, risk_level="LOW", ) # Pillar 3: Cho thuê Máy móc & Ca máy (Loan Khải 0201889988...) pillar3 = PillarFinancialSummary( pillar_id=BusinessPillarEnum.PILLAR_3_EQUIPMENT, pillar_name="Cho Thuê Máy Móc Thiết Bị & Cung Cấp Ca Máy", total_revenue=Decimal("4200000000.0000"), total_cost=Decimal("3150000000.0000"), gross_profit=Decimal("1050000000.0000"), gross_margin_percentage=Decimal("25.0000"), receivables_pending=Decimal("580000000.0000"), payables_pending=Decimal("310000000.0000"), active_contracts_count=6, risk_level="LOW", ) # Pillar 4: Mua bán Vật tư & San lấp (Trung Kiên 0201805660...) pillar4 = PillarFinancialSummary( pillar_id=BusinessPillarEnum.PILLAR_4_MATERIALS, pillar_name="Mua Bán Vật Tư & San Lấp Mặt Bằng", total_revenue=Decimal("14800000000.0000"), total_cost=Decimal("13024000000.0000"), gross_profit=Decimal("1776000000.0000"), gross_margin_percentage=Decimal("12.0000"), receivables_pending=Decimal("2100000000.0000"), payables_pending=Decimal("3450000000.0000"), active_contracts_count=8, risk_level="MEDIUM", ) pillars = [pillar1, pillar2, pillar3, pillar4] # Calculate totals total_revenue = sum((p.total_revenue for p in pillars), Decimal("0.0000")) total_cost = sum((p.total_cost for p in pillars), Decimal("0.0000")) total_gross_profit = total_revenue - total_cost overall_margin = ( (total_gross_profit / total_revenue * Decimal("100.0000")).quantize( Decimal("0.0001") ) if total_revenue > Decimal("0.0000") else Decimal("0.0000") ) # Cashflow Runway current_cash = Decimal("4850000000.0000") # 1. Fetch transactions from DB or use fallback logic end_date = target_date + timedelta( days=90 ) # Fetch up to 90 days for runway calculation raw_txs = [] if self.postgres_client and hasattr( self.postgres_client, "fetch_planned_transactions" ): raw_txs = self.postgres_client.fetch_planned_transactions( target_date, end_date ) # 2. Shift dates for inflows (client delay) # Create a dictionary of daily net flows from collections import defaultdict daily_inflows = defaultdict(Decimal) daily_outflows = defaultdict(Decimal) if raw_txs: for tx in raw_txs: tx_date = tx["transaction_date"] tx_type = tx["transaction_type"] direction = tx["direction"] amount = Decimal(str(tx["amount"])) if direction == "inflow" and tx_type in ( "stage_payment", "retention_release", ): # Stress test: delay inflows from clients tx_date = tx_date + timedelta(days=client_delay_days) # Sửa đổi: Mặc định tất cả các khoản chi là outflow, thu là inflow if direction == "inflow": daily_inflows[tx_date] += amount else: daily_outflows[tx_date] += amount else: # Fallback mock data if DB is empty/unconnected for i in range(1, 31): tx_date = target_date + timedelta(days=i) daily_outflows[tx_date] = Decimal("75000000.0000") if i in (5, 15, 25): # Inflow is shifted by client_delay_days inflow_date = tx_date + timedelta(days=client_delay_days) daily_inflows[inflow_date] += Decimal("650000000.0000") if i in (10, 20): daily_outflows[tx_date] += Decimal("480000000.0000") forecast_days: list[CashflowForecastDay] = [] running_balance = current_cash total_inflow_30d = Decimal("0.0000") total_outflow_30d = Decimal("0.0000") lowest_balance = current_cash risk_date = None liquidity_risk_alert = False net_runway_days = 90 # Build daily forecast for i in range(1, 91): curr_date = target_date + timedelta(days=i) inflow = daily_inflows.get(curr_date, Decimal("0.0000")) outflow = daily_outflows.get(curr_date, Decimal("0.0000")) if i <= 30: total_inflow_30d += inflow total_outflow_30d += outflow net_daily = inflow - outflow running_balance += net_daily lowest_balance = min(lowest_balance, running_balance) if running_balance < 0 and not liquidity_risk_alert: liquidity_risk_alert = True risk_date = curr_date.isoformat() net_runway_days = i - 1 if i <= 30: forecast_days.append( CashflowForecastDay( date_str=curr_date.isoformat(), expected_inflow=inflow, expected_outflow=outflow, net_daily=net_daily, projected_balance=running_balance, ) ) cashflow_summary = CashflowRunwaySummary( current_cash_balance=current_cash, total_expected_inflow_30d=total_inflow_30d, total_expected_outflow_30d=total_outflow_30d, net_runway_days=net_runway_days, stress_test_delay_days=client_delay_days, liquidity_risk_alert=liquidity_risk_alert, lowest_balance=lowest_balance, risk_date=risk_date, forecast_30d=forecast_days, ) # Margin Alerts across major contracts margin_alerts = [ ContractMarginAlert( project_code="DA-DADO-THUYLDT-2022-2026", project_name="Gói Thầu Số 04 Thủy Lợi Sông Đa Độ", client_name="Cty TNHH MTV KTCT Thủy Lợi Đa Độ", contract_value=Decimal("34460000000.0000"), accumulated_billed=Decimal("28500000000.0000"), accumulated_cost=Decimal("23085000000.0000"), current_margin_pct=Decimal("19.0000"), planned_margin_pct=Decimal("18.5000"), alert_status="NORMAL", ), ContractMarginAlert( project_code="DA-KM-BENKEM-2025-2026", project_name="Hệ Thống Cống Hộp Kênh Bến Kem & Thoát Nước Kiến Minh", client_name="UBND Xã Kiến Minh", contract_value=Decimal("14080000000.0000"), accumulated_billed=Decimal("6200000000.0000"), accumulated_cost=Decimal("5208000000.0000"), current_margin_pct=Decimal("16.0000"), planned_margin_pct=Decimal("17.0000"), alert_status="WARNING_DILUTION", root_cause="Giá cát đá san lấp đầu vào tăng nhẹ trong Q2/2026", ), ContractMarginAlert( project_code="DA-RANGDONG-HTDT-2022-2026", project_name="Thi Công Xây Lắp & San Lấp Hạ Tầng Đô Thị Rạng Đông", client_name="Cty CP Xây Dựng Rạng Đông", contract_value=Decimal("10380000000.0000"), accumulated_billed=Decimal("9100000000.0000"), accumulated_cost=Decimal("7280000000.0000"), current_margin_pct=Decimal("20.0000"), planned_margin_pct=Decimal("19.0000"), alert_status="NORMAL", ), ] return ExecutiveWarRoomData( as_of_date=target_date, generated_at=datetime.now(), total_revenue_ytd=total_revenue, total_cost_ytd=total_cost, total_gross_profit_ytd=total_gross_profit, overall_margin_percentage=overall_margin, pillars_breakdown=pillars, cashflow_runway=cashflow_summary, margin_alerts=margin_alerts, ) def get_project_evm_metrics(self, project_code: str) -> dict[str, Any]: """Tính toán chỉ số EVM (Earned Value Management) gồm SPI và CPI cho dự án.""" from app.core.postgres.base_pkg.base_client import BasePostgresClient client = self.postgres_client or BasePostgresClient() # 1. Truy vấn thông tin dự án sql = "SELECT project_name, total_budget_vnd, start_date, end_date FROM erp_projects WHERE project_code = %s" try: with client.pool.connection() as conn, conn.cursor() as cur: cur.execute(sql, (project_code,)) proj = cur.fetchone() except Exception: proj = None if not proj: return {"error": "Dự án không tồn tại."} total_budget = float(proj.get("total_budget_vnd") or 0) # 2. Tính PV (Planned Value) dựa trên tiến độ tuyến tính thời gian start_date = proj.get("start_date") end_date = proj.get("end_date") pv = 0.0 if start_date and end_date and total_budget > 0: total_days = (end_date - start_date).days if total_days > 0: elapsed_days = (date.today() - start_date).days elapsed_days = max(0, min(elapsed_days, total_days)) pv = total_budget * (elapsed_days / total_days) # 3. EV (Earned Value) & AC (Actual Cost) từ hóa đơn đầu ra/đầu vào đã duyệt ev_sql = "SELECT COALESCE(SUM(total_amount_vnd), 0) as ev FROM erp_invoices WHERE project_code = %s AND status = 'approved' AND type = 'output'" ac_sql = "SELECT COALESCE(SUM(total_amount_vnd), 0) as ac FROM erp_invoices WHERE project_code = %s AND status = 'approved' AND type = 'input'" ev = 0.0 ac = 0.0 try: with client.pool.connection() as conn, conn.cursor() as cur: cur.execute(ev_sql, (project_code,)) ev_row = cur.fetchone() if ev_row: ev = float(ev_row.get("ev", 0) if isinstance(ev_row, dict) else ev_row[0]) cur.execute(ac_sql, (project_code,)) ac_row = cur.fetchone() if ac_row: ac = float(ac_row.get("ac", 0) if isinstance(ac_row, dict) else ac_row[0]) except Exception: pass spi = round(ev / pv, 2) if pv > 0 else 0 cpi = round(ev / ac, 2) if ac > 0 else 0 return { "project_code": project_code, "project_name": proj["project_name"], "metrics": { "EV": ev, "AC": ac, "PV": pv, "SPI": spi, "CPI": cpi, }, "health": { "schedule": "AHEAD" if spi >= 1 else "BEHIND", "cost": "UNDER_BUDGET" if cpi >= 1 else "OVER_BUDGET", }, }