"""Domain Service for Debt Ledger & Fixed Assets Synchronization (Source of Truth from MISA).""" from __future__ import annotations import os from datetime import date, datetime from decimal import Decimal from typing import Any from uuid import UUID, uuid4 import pandas as pd from app.core.postgres.base_pkg.base_client import BasePostgresClient from app.models.debt_ledger_schemas import ( ConsolidatedDebtOverview, DebtBalanceItemSchema, DebtLedgerResponse, DebtReportSummary, FixedAssetItemSchema, FixedAssetResponse, FixedAssetSummary, ) from pathlib import Path _DEFAULT_BASE_DIR = Path(__file__).resolve().parents[4] DEFAULT_FINANCE_FOLDER = os.getenv( "FINANCE_LEDGER_DIR", str(_DEFAULT_BASE_DIR / "Finance" / "Sổ công nợ 2026"), ) def _to_decimal(val: Any) -> Decimal: if pd.isna(val) or str(val).strip() in ("", "nan", "None"): return Decimal("0.0000") if isinstance(val, (int,)): return Decimal(str(val)) if isinstance(val, (float,)): return Decimal(str(round(val, 4))) cleaned = str(val).replace(",", "").strip() try: return Decimal(cleaned) except Exception: return Decimal("0.0000") def _parse_date(val: Any) -> date | None: if not val or str(val).strip() in ("", "nan", "None"): return None s = str(val).strip() for fmt in ("%d/%m/%Y", "%Y-%m-%d", "%d-%m-%Y"): try: return datetime.strptime(s, fmt).date() except ValueError: pass return None class DebtLedgerSyncService: """Synchronizes MISA accounting debt ledgers (131, 331) and fixed assets (211, 214).""" def __init__(self, postgres_client: Any | None = None) -> None: self._client = postgres_client or BasePostgresClient() def _get_connection(self): if hasattr(self._client, "get_connection"): return self._client.get_connection() return self._client._open_connection() def get_company_id(self) -> UUID: """Fetch the default company ID (CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN).""" with self._get_connection() as conn: with conn.cursor() as cur: cur.execute( "SELECT id FROM companies WHERE tax_code = '0202111150' OR company_code = 'DSCONS' LIMIT 1" ) row = cur.fetchone() if row: return UUID(str(row["id"])) # Fallback: first company in table cur.execute("SELECT id FROM companies LIMIT 1") row = cur.fetchone() if row: return UUID(str(row["id"])) raise ValueError("Không tìm thấy Công ty Định Sơn (MST 0202111150) trong DB.") def _match_or_create_partner( self, cur: Any, partner_code: str, partner_name: str, is_customer: bool = False, is_vendor: bool = False, ) -> UUID: """Matches an existing partner in erp_partners or creates a new one.""" clean_name = partner_name.strip() # 1. Exact match by company_name cur.execute( "SELECT id, short_name, is_customer, is_vendor FROM erp_partners WHERE company_name = %s LIMIT 1", (clean_name,), ) row = cur.fetchone() # 2. Case-insensitive exact match if not row: cur.execute( "SELECT id, short_name, is_customer, is_vendor FROM erp_partners WHERE LOWER(company_name) = LOWER(%s) LIMIT 1", (clean_name,), ) row = cur.fetchone() # 3. Match by short_name or partner_code if not row and partner_code: cur.execute( "SELECT id, short_name, is_customer, is_vendor FROM erp_partners WHERE short_name = %s LIMIT 1", (partner_code.strip(),), ) row = cur.fetchone() # 4. Partial fallback for government bodies / specific names if not row: cur.execute( """ SELECT id, short_name, is_customer, is_vendor FROM erp_partners WHERE %s ILIKE ('%%' || company_name || '%%') OR company_name ILIKE ('%%' || %s || '%%') ORDER BY LENGTH(company_name) DESC LIMIT 1 """, (clean_name, clean_name), ) row = cur.fetchone() if row: partner_id = UUID(str(row["id"])) # Update customer/vendor flags and short_name if needed new_is_cust = row["is_customer"] or is_customer new_is_vend = row["is_vendor"] or is_vendor new_short = row["short_name"] or partner_code.strip() cur.execute( """ UPDATE erp_partners SET is_customer = %s, is_vendor = %s, short_name = %s, updated_at = NOW() WHERE id = %s """, (new_is_cust, new_is_vend, new_short, partner_id), ) return partner_id # If not found, create new partner gen_id = uuid4() # Derive a tax code or generated code generated_tax_code = f"MISA-{partner_code.strip().replace(' ', '_').upper()}" cur.execute( """ INSERT INTO erp_partners ( id, tax_code, company_name, short_name, partner_category, is_customer, is_vendor, status, notes ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (tax_code) DO UPDATE SET is_customer = EXCLUDED.is_customer OR erp_partners.is_customer, is_vendor = EXCLUDED.is_vendor OR erp_partners.is_vendor RETURNING id """, ( gen_id, generated_tax_code, clean_name, partner_code.strip(), "organization", is_customer, is_vendor, "active", f"Đồng bộ từ Sổ công nợ MISA 2026 (Mã: {partner_code})", ), ) res = cur.fetchone() return UUID(str(res["id"])) def sync_receivables( self, filepath: str, company_id: UUID | None = None, as_of_date: date = date(2026, 3, 31), ) -> dict[str, Any]: """Ingests TK 131 Accounts Receivable from Excel.""" if not os.path.exists(filepath): raise FileNotFoundError(f"File không tồn tại: {filepath}") cid = company_id or self.get_company_id() df = pd.read_excel(filepath, sheet_name=0, header=None) # Parse records between header and 'Tổng cộng' records: list[dict[str, Any]] = [] found_total = False excel_total: dict[str, Decimal] = {} for idx in range(len(df)): col0 = str(df.iloc[idx][0]).strip() if pd.notna(df.iloc[idx][0]) else "" if col0 == "Tổng cộng": found_total = True excel_total = { "opening_debit": _to_decimal(df.iloc[idx][4]), "opening_credit": _to_decimal(df.iloc[idx][5]), "period_debit": _to_decimal(df.iloc[idx][6]), "period_credit": _to_decimal(df.iloc[idx][8]), "closing_debit": _to_decimal(df.iloc[idx][11]), "closing_credit": _to_decimal(df.iloc[idx][12]), } break if idx >= 7 and col0: p_name = ( str(df.iloc[idx][1]).strip() if pd.notna(df.iloc[idx][1]) else col0 ) records.append( { "partner_code": col0, "partner_name": p_name, "account_code": "131", "debt_type": "receivable", "opening_debit": _to_decimal(df.iloc[idx][4]), "opening_credit": _to_decimal(df.iloc[idx][5]), "period_debit": _to_decimal(df.iloc[idx][6]), "period_credit": _to_decimal(df.iloc[idx][8]), "closing_debit": _to_decimal(df.iloc[idx][11]), "closing_credit": _to_decimal(df.iloc[idx][12]), } ) if not found_total: raise ValueError("Không tìm thấy dòng 'Tổng cộng' trong file phải thu.") # Reconcile sum vs excel_total calc_closing_debit = sum((r["closing_debit"] for r in records), Decimal("0")) calc_closing_credit = sum((r["closing_credit"] for r in records), Decimal("0")) if ( calc_closing_debit != excel_total["closing_debit"] or calc_closing_credit != excel_total["closing_credit"] ): raise ValueError( f"Lệch số liệu Tổng hợp Phải thu: Tính toán Dư Nợ {calc_closing_debit} != Excel {excel_total['closing_debit']}" ) # Upsert into PostgreSQL filename = os.path.basename(filepath) saved_count = 0 with self._get_connection() as conn: with conn.cursor() as cur: for r in records: partner_id = self._match_or_create_partner( cur, partner_code=r["partner_code"], partner_name=r["partner_name"], is_customer=True, ) net_closing = r["closing_debit"] - r["closing_credit"] cur.execute( """ INSERT INTO erp_debt_balances ( company_id, partner_id, partner_code, partner_name, account_code, debt_type, fiscal_year, period_type, period_name, as_of_date, opening_debit_vnd, opening_credit_vnd, period_debit_vnd, period_credit_vnd, closing_debit_vnd, closing_credit_vnd, net_closing_balance_vnd, source_file, raw_metadata, updated_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) ON CONFLICT (company_id, account_code, partner_code, as_of_date) DO UPDATE SET partner_id = EXCLUDED.partner_id, partner_name = EXCLUDED.partner_name, opening_debit_vnd = EXCLUDED.opening_debit_vnd, opening_credit_vnd = EXCLUDED.opening_credit_vnd, period_debit_vnd = EXCLUDED.period_debit_vnd, period_credit_vnd = EXCLUDED.period_credit_vnd, closing_debit_vnd = EXCLUDED.closing_debit_vnd, closing_credit_vnd = EXCLUDED.closing_credit_vnd, net_closing_balance_vnd = EXCLUDED.net_closing_balance_vnd, source_file = EXCLUDED.source_file, updated_at = NOW() """, ( cid, partner_id, r["partner_code"], r["partner_name"], "131", "receivable", 2026, "quarter", "Q1.2026", as_of_date, r["opening_debit"], r["opening_credit"], r["period_debit"], r["period_credit"], r["closing_debit"], r["closing_credit"], net_closing, filename, "{}", ), ) saved_count += 1 conn.commit() return { "status": "success", "file": filename, "account_code": "131", "records_count": saved_count, "total_closing_debit_vnd": excel_total["closing_debit"], "total_closing_credit_vnd": excel_total["closing_credit"], } def sync_payables( self, filepath: str, company_id: UUID | None = None, as_of_date: date = date(2026, 3, 31), ) -> dict[str, Any]: """Ingests TK 331 Accounts Payable from Excel.""" if not os.path.exists(filepath): raise FileNotFoundError(f"File không tồn tại: {filepath}") cid = company_id or self.get_company_id() df = pd.read_excel(filepath, sheet_name=0, header=None) records: list[dict[str, Any]] = [] found_total = False excel_total: dict[str, Decimal] = {} for idx in range(len(df)): col0 = str(df.iloc[idx][0]).strip() if pd.notna(df.iloc[idx][0]) else "" if col0 == "Tổng cộng": found_total = True excel_total = { "opening_debit": _to_decimal(df.iloc[idx][4]), "opening_credit": _to_decimal(df.iloc[idx][5]), "period_debit": _to_decimal(df.iloc[idx][6]), "period_credit": _to_decimal(df.iloc[idx][8]), "closing_debit": _to_decimal(df.iloc[idx][11]), "closing_credit": _to_decimal(df.iloc[idx][12]), } break if idx >= 7 and col0: p_name = ( str(df.iloc[idx][1]).strip() if pd.notna(df.iloc[idx][1]) else col0 ) records.append( { "partner_code": col0, "partner_name": p_name, "account_code": "331", "debt_type": "payable", "opening_debit": _to_decimal(df.iloc[idx][4]), "opening_credit": _to_decimal(df.iloc[idx][5]), "period_debit": _to_decimal(df.iloc[idx][6]), "period_credit": _to_decimal(df.iloc[idx][8]), "closing_debit": _to_decimal(df.iloc[idx][11]), "closing_credit": _to_decimal(df.iloc[idx][12]), } ) if not found_total: raise ValueError("Không tìm thấy dòng 'Tổng cộng' trong file phải trả.") calc_closing_debit = sum((r["closing_debit"] for r in records), Decimal("0")) calc_closing_credit = sum((r["closing_credit"] for r in records), Decimal("0")) if ( calc_closing_debit != excel_total["closing_debit"] or calc_closing_credit != excel_total["closing_credit"] ): raise ValueError( f"Lệch số liệu Tổng hợp Phải trả: Tính toán Dư Có {calc_closing_credit} != Excel {excel_total['closing_credit']}" ) filename = os.path.basename(filepath) saved_count = 0 with self._get_connection() as conn: with conn.cursor() as cur: for r in records: partner_id = self._match_or_create_partner( cur, partner_code=r["partner_code"], partner_name=r["partner_name"], is_vendor=True, ) net_closing = r["closing_debit"] - r["closing_credit"] cur.execute( """ INSERT INTO erp_debt_balances ( company_id, partner_id, partner_code, partner_name, account_code, debt_type, fiscal_year, period_type, period_name, as_of_date, opening_debit_vnd, opening_credit_vnd, period_debit_vnd, period_credit_vnd, closing_debit_vnd, closing_credit_vnd, net_closing_balance_vnd, source_file, raw_metadata, updated_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) ON CONFLICT (company_id, account_code, partner_code, as_of_date) DO UPDATE SET partner_id = EXCLUDED.partner_id, partner_name = EXCLUDED.partner_name, opening_debit_vnd = EXCLUDED.opening_debit_vnd, opening_credit_vnd = EXCLUDED.opening_credit_vnd, period_debit_vnd = EXCLUDED.period_debit_vnd, period_credit_vnd = EXCLUDED.period_credit_vnd, closing_debit_vnd = EXCLUDED.closing_debit_vnd, closing_credit_vnd = EXCLUDED.closing_credit_vnd, net_closing_balance_vnd = EXCLUDED.net_closing_balance_vnd, source_file = EXCLUDED.source_file, updated_at = NOW() """, ( cid, partner_id, r["partner_code"], r["partner_name"], "331", "payable", 2026, "quarter", "Q1.2026", as_of_date, r["opening_debit"], r["opening_credit"], r["period_debit"], r["period_credit"], r["closing_debit"], r["closing_credit"], net_closing, filename, "{}", ), ) saved_count += 1 conn.commit() return { "status": "success", "file": filename, "account_code": "331", "records_count": saved_count, "total_closing_debit_vnd": excel_total["closing_debit"], "total_closing_credit_vnd": excel_total["closing_credit"], } def _match_equipment(self, cur: Any, asset_code: str, asset_name: str) -> UUID | None: """Finds matching equipment in erp_equipment by license plate or model.""" # 1. Check for license plate patterns like '15H-10624', '15H-10529', '15H-28352', '15H-13126' import re plate_match = re.search(r"15[A-Z0-9-]{3,8}", asset_name + " " + asset_code) if plate_match: plate = plate_match.group(0).replace(" ", "").upper() cur.execute( "SELECT id FROM erp_equipment WHERE license_plate ILIKE %s LIMIT 1", (f"%{plate}%",), ) row = cur.fetchone() if row: return UUID(str(row["id"])) # 2. Check for machinery model patterns like 'PC300LC', 'PC200-7', 'SK135' for model in ["PC300LC", "PC200-7", "PC200-6", "SK135", "KIA"]: if model in asset_code.upper() or model in asset_name.upper(): cur.execute( """ SELECT id FROM erp_equipment WHERE equipment_code ILIKE %s OR equipment_name ILIKE %s OR license_plate ILIKE %s LIMIT 1 """, (f"%{model}%", f"%{model}%", f"%{model}%"), ) row = cur.fetchone() if row: return UUID(str(row["id"])) return None def sync_fixed_assets( self, filepath: str, company_id: UUID | None = None, as_of_date: date = date(2026, 3, 31), ) -> dict[str, Any]: """Ingests TK 211, 214 Fixed Assets and Depreciation schedule from Excel.""" if not os.path.exists(filepath): raise FileNotFoundError(f"File không tồn tại: {filepath}") cid = company_id or self.get_company_id() df = pd.read_excel(filepath, sheet_name=0, header=None) records: list[dict[str, Any]] = [] found_total = False excel_total: dict[str, Decimal] = {} for idx in range(len(df)): col0 = str(df.iloc[idx][0]).strip() if pd.notna(df.iloc[idx][0]) else "" if col0 == "Tổng cộng": found_total = True excel_total = { "original_cost": _to_decimal(df.iloc[idx][10]), "depreciable_value": _to_decimal(df.iloc[idx][11]), "period_depreciation": _to_decimal(df.iloc[idx][15]), "accumulated_depreciation": _to_decimal(df.iloc[idx][16]), "net_book_value": _to_decimal(df.iloc[idx][17]), "monthly_depreciation": _to_decimal(df.iloc[idx][18]), } break if idx >= 6 and col0: inc_date = _parse_date(df.iloc[idx][4]) or date(2024, 5, 1) dep_start = _parse_date(df.iloc[idx][7]) or inc_date useful_m = ( int(df.iloc[idx][8]) if pd.notna(df.iloc[idx][8]) else 60 ) rem_m = ( int(df.iloc[idx][9]) if pd.notna(df.iloc[idx][9]) else 0 ) records.append( { "asset_code": col0, "asset_name": str(df.iloc[idx][1]).strip() if pd.notna(df.iloc[idx][1]) else col0, "asset_type": str(df.iloc[idx][2]).strip() if pd.notna(df.iloc[idx][2]) else "Máy móc, thiết bị", "department": str(df.iloc[idx][3]).strip() if pd.notna(df.iloc[idx][3]) else "CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN", "increase_date": inc_date, "voucher_number": str(df.iloc[idx][6]).strip() if pd.notna(df.iloc[idx][6]) else None, "depreciation_start_date": dep_start, "useful_life_months": useful_m, "remaining_life_months": rem_m, "original_cost": _to_decimal(df.iloc[idx][10]), "depreciable_value": _to_decimal(df.iloc[idx][11]), "period_depreciation": _to_decimal(df.iloc[idx][15]), "accumulated_depreciation": _to_decimal(df.iloc[idx][16]), "net_book_value": _to_decimal(df.iloc[idx][17]), "monthly_depreciation": _to_decimal(df.iloc[idx][18]), "cost_account": str(df.iloc[idx][19]).strip() if pd.notna(df.iloc[idx][19]) else "211", "depreciation_account": str(df.iloc[idx][20]).strip() if pd.notna(df.iloc[idx][20]) else "2141", } ) if not found_total: raise ValueError("Không tìm thấy dòng 'Tổng cộng' trong file TSCĐ.") calc_orig_cost = sum((r["original_cost"] for r in records), Decimal("0")) calc_net_value = sum((r["net_book_value"] for r in records), Decimal("0")) if ( calc_orig_cost != excel_total["original_cost"] or calc_net_value != excel_total["net_book_value"] ): raise ValueError( f"Lệch số liệu Sổ TSCĐ: Tính toán Nguyên giá {calc_orig_cost} != Excel {excel_total['original_cost']}" ) filename = os.path.basename(filepath) saved_count = 0 with self._get_connection() as conn: with conn.cursor() as cur: for r in records: matched_eq_id = self._match_equipment( cur, r["asset_code"], r["asset_name"] ) # If matched, update erp_equipment purchase_cost_vnd if 0 if matched_eq_id: cur.execute( """ UPDATE erp_equipment SET purchase_cost_vnd = COALESCE(NULLIF(purchase_cost_vnd, 0), %s) WHERE id = %s """, (r["original_cost"], matched_eq_id), ) cur.execute( """ INSERT INTO erp_fixed_assets ( company_id, asset_code, asset_name, asset_type, department, increase_date, voucher_number, depreciation_start_date, useful_life_months, remaining_life_months, original_cost_vnd, depreciable_value_vnd, period_depreciation_vnd, accumulated_depreciation_vnd, net_book_value_vnd, monthly_depreciation_vnd, cost_account, depreciation_account, fiscal_year, period_name, as_of_date, matched_equipment_id, source_file, raw_metadata, updated_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) ON CONFLICT (company_id, asset_code, as_of_date) DO UPDATE SET asset_name = EXCLUDED.asset_name, asset_type = EXCLUDED.asset_type, department = EXCLUDED.department, increase_date = EXCLUDED.increase_date, voucher_number = EXCLUDED.voucher_number, depreciation_start_date = EXCLUDED.depreciation_start_date, useful_life_months = EXCLUDED.useful_life_months, remaining_life_months = EXCLUDED.remaining_life_months, original_cost_vnd = EXCLUDED.original_cost_vnd, depreciable_value_vnd = EXCLUDED.depreciable_value_vnd, period_depreciation_vnd = EXCLUDED.period_depreciation_vnd, accumulated_depreciation_vnd = EXCLUDED.accumulated_depreciation_vnd, net_book_value_vnd = EXCLUDED.net_book_value_vnd, monthly_depreciation_vnd = EXCLUDED.monthly_depreciation_vnd, cost_account = EXCLUDED.cost_account, depreciation_account = EXCLUDED.depreciation_account, matched_equipment_id = EXCLUDED.matched_equipment_id, source_file = EXCLUDED.source_file, updated_at = NOW() """, ( cid, r["asset_code"], r["asset_name"], r["asset_type"], r["department"], r["increase_date"], r["voucher_number"], r["depreciation_start_date"], r["useful_life_months"], r["remaining_life_months"], r["original_cost"], r["depreciable_value"], r["period_depreciation"], r["accumulated_depreciation"], r["net_book_value"], r["monthly_depreciation"], r["cost_account"], r["depreciation_account"], 2026, "Q1.2026", as_of_date, matched_eq_id, filename, "{}", ), ) saved_count += 1 conn.commit() return { "status": "success", "file": filename, "assets_count": saved_count, "total_original_cost_vnd": excel_total["original_cost"], "total_net_book_value_vnd": excel_total["net_book_value"], } def sync_all_from_folder( self, folder_path: str | None = None ) -> dict[str, Any]: """Synchronizes all 3 Excel files from the MISA 2026 folder.""" folder = folder_path or DEFAULT_FINANCE_FOLDER if not os.path.isdir(folder): raise NotADirectoryError(f"Thư mục không tồn tại: {folder}") cid = self.get_company_id() results: dict[str, Any] = {} # 1. File Phải thu f_thu = os.path.join(folder, "Tong_hop_cong_no_phai_thu 31.3.2026.xls") if os.path.exists(f_thu): results["receivables"] = self.sync_receivables(f_thu, company_id=cid) # 2. File Phải trả f_tra = os.path.join(folder, "Tong_hop_cong_no_phai_tra 31.3.2026.xls") if os.path.exists(f_tra): results["payables"] = self.sync_payables(f_tra, company_id=cid) # 3. File TSCĐ f_ts = os.path.join(folder, "So_tai_san_co_dinh Q1.2026.xls") if os.path.exists(f_ts): results["fixed_assets"] = self.sync_fixed_assets(f_ts, company_id=cid) return results def get_debt_ledger_report( self, account_code: str = "131", as_of_date: date | None = None ) -> DebtLedgerResponse: """Queries stored debt balances for an account.""" target_date = as_of_date or date(2026, 3, 31) debt_type = "receivable" if account_code == "131" else "payable" with self._get_connection() as conn: with conn.cursor() as cur: cur.execute( """ SELECT id, company_id, partner_id, partner_code, partner_name, account_code, debt_type, fiscal_year, period_type, period_name, as_of_date, opening_debit_vnd, opening_credit_vnd, period_debit_vnd, period_credit_vnd, closing_debit_vnd, closing_credit_vnd, net_closing_balance_vnd, source_file, raw_metadata, created_at, updated_at FROM erp_debt_balances WHERE account_code = %s AND as_of_date = %s ORDER BY partner_code ASC """, (account_code, target_date), ) rows = cur.fetchall() items = [DebtBalanceItemSchema(**r) for r in rows] summary = DebtReportSummary( total_partners=len(items), total_opening_debit_vnd=sum((i.opening_debit_vnd for i in items), Decimal("0.0000")), total_opening_credit_vnd=sum((i.opening_credit_vnd for i in items), Decimal("0.0000")), total_period_debit_vnd=sum((i.period_debit_vnd for i in items), Decimal("0.0000")), total_period_credit_vnd=sum((i.period_credit_vnd for i in items), Decimal("0.0000")), total_closing_debit_vnd=sum((i.closing_debit_vnd for i in items), Decimal("0.0000")), total_closing_credit_vnd=sum((i.closing_credit_vnd for i in items), Decimal("0.0000")), total_net_closing_vnd=sum((i.net_closing_balance_vnd for i in items), Decimal("0.0000")), ) return DebtLedgerResponse( account_code=account_code, debt_type=debt_type, period_name="Q1.2026", as_of_date=target_date, summary=summary, items=items, ) def get_fixed_assets_report( self, as_of_date: date | None = None ) -> FixedAssetResponse: """Queries stored fixed assets and depreciation schedule.""" target_date = as_of_date or date(2026, 3, 31) with self._get_connection() as conn: with conn.cursor() as cur: cur.execute( """ SELECT id, company_id, asset_code, asset_name, asset_type, department, increase_date, voucher_number, depreciation_start_date, useful_life_months, remaining_life_months, original_cost_vnd, depreciable_value_vnd, period_depreciation_vnd, accumulated_depreciation_vnd, net_book_value_vnd, monthly_depreciation_vnd, cost_account, depreciation_account, fiscal_year, period_name, as_of_date, matched_equipment_id, source_file, raw_metadata, created_at, updated_at FROM erp_fixed_assets WHERE as_of_date = %s ORDER BY increase_date ASC """, (target_date,), ) rows = cur.fetchall() items = [FixedAssetItemSchema(**r) for r in rows] summary = FixedAssetSummary( total_assets=len(items), total_original_cost_vnd=sum((i.original_cost_vnd for i in items), Decimal("0.0000")), total_depreciable_value_vnd=sum((i.depreciable_value_vnd for i in items), Decimal("0.0000")), total_period_depreciation_vnd=sum((i.period_depreciation_vnd for i in items), Decimal("0.0000")), total_accumulated_depreciation_vnd=sum((i.accumulated_depreciation_vnd for i in items), Decimal("0.0000")), total_net_book_value_vnd=sum((i.net_book_value_vnd for i in items), Decimal("0.0000")), total_monthly_depreciation_vnd=sum((i.monthly_depreciation_vnd for i in items), Decimal("0.0000")), ) return FixedAssetResponse( period_name="Q1.2026", as_of_date=target_date, summary=summary, items=items, ) def get_consolidated_overview( self, as_of_date: date | None = None ) -> ConsolidatedDebtOverview: """Consolidates Receivables, Payables, and Fixed Assets.""" target_date = as_of_date or date(2026, 3, 31) rec = self.get_debt_ledger_report("131", target_date) pay = self.get_debt_ledger_report("331", target_date) assets = self.get_fixed_assets_report(target_date) # Net working capital trade debt # Receivable Dư Nợ - Dư Có (Net tiền khách nợ mình) net_rec = rec.summary.total_closing_debit_vnd - rec.summary.total_closing_credit_vnd # Payable Dư Có - Dư Nợ (Net tiền mình nợ nhà cung cấp) net_pay = pay.summary.total_closing_credit_vnd - pay.summary.total_closing_debit_vnd # Net Trade Debt Position = Phải thu ròng - Phải trả ròng net_trade = net_rec - net_pay return ConsolidatedDebtOverview( as_of_date=target_date, period_name="Q1.2026", receivables_summary=rec.summary, payables_summary=pay.summary, fixed_assets_summary=assets.summary, net_working_capital_receivable_vnd=net_rec, net_working_capital_payable_vnd=net_pay, net_trade_debt_balance_vnd=net_trade, )