from __future__ import annotations """FIDIC IPC Output invoice generation engine.""" import hashlib import json import logging from decimal import ROUND_HALF_UP, Decimal from typing import Any from .common import DSCONS_DEFAULT_COMPANY_NAME, DSCONS_DEFAULT_TAX_CODE, to_decimal logger = logging.getLogger(__name__) class InvoiceFidicIpcMixin: """Mixin for generating FIDIC IPC compliant output invoices.""" def create_fidic_ipc_output_invoice( self, project_id: str, wbs_items: list[dict[str, Any]], advance_deduction_pct: float = 10.0, retention_pct: float = 5.0, vat_rate_pct: float = 8.0, buyer_tax_code: str | None = None, buyer_name: str | None = None, buyer_address: str | None = None, invoice_number: str | None = None, invoice_series: str | None = None, user_name: str = "SuperAdmin", notes: str | None = None, ) -> dict[str, Any]: """Lập hóa đơn bán ra / Giấy đề nghị thanh toán đợt IPC theo chuẩn FIDIC có khấu trừ tạm ứng & bảo hành.""" with self.get_connection() as conn: with conn.cursor() as cur: # 1. Fetch Project Info cur.execute( "SELECT id, project_code, project_name, contract_number FROM projects WHERE id = %s", (project_id,), ) proj = cur.fetchone() if not proj: return { "status": "error", "message": "Không tìm thấy dự án tương ứng.", } final_buyer_name = buyer_name or f"Chủ Đầu Tư - {proj['project_name']}" final_buyer_mst = buyer_tax_code or "0100100100" final_buyer_addr = buyer_address or "Trụ sở Ban QLDA Công trình" # 2. Calculate Gross Milestone Work Done gross_amount_vnd = Decimal("0.0000") parsed_items = [] for idx, itm in enumerate(wbs_items, 1): item_name = ( itm.get("item_name") or f"Nghiệm thu hạng mục WBS #{idx}" ) qty = to_decimal(itm.get("quantity", 1.0)) price = to_decimal(itm.get("unit_price_vnd", 0.0)) line_amt = (qty * price).quantize( Decimal("0.0001"), rounding=ROUND_HALF_UP ) gross_amount_vnd += line_amt parsed_items.append( { "item_order": idx, "item_name": item_name, "item_code": itm.get("item_code", f"IPC-WBS-{idx}"), "unit": itm.get("unit", "Đợt"), "quantity": qty, "unit_price_vnd": price, "amount_before_vat_vnd": line_amt, "vat_rate_percent": Decimal(str(vat_rate_pct)), "cost_category": "subcontractor", } ) if gross_amount_vnd <= Decimal("0.0000"): return { "status": "error", "message": "Tổng giá trị nghiệm thu phải lớn hơn 0 đồng.", } # 3. Apply FIDIC Deductions adv_rate_dec = Decimal(str(advance_deduction_pct)) ret_rate_dec = Decimal(str(retention_pct)) vat_rate_dec = Decimal(str(vat_rate_pct)) advance_deduct_vnd = ( gross_amount_vnd * (adv_rate_dec / Decimal("100.0")) ).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP) retention_deduct_vnd = ( gross_amount_vnd * (ret_rate_dec / Decimal("100.0")) ).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP) net_subtotal_vnd = ( gross_amount_vnd - advance_deduct_vnd - retention_deduct_vnd ) vat_amount_vnd = ( net_subtotal_vnd * (vat_rate_dec / Decimal("100.0")) ).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP) total_amount_vnd = net_subtotal_vnd + vat_amount_vnd ipc_deductions = { "gross_milestone_amount_vnd": float(gross_amount_vnd), "advance_deduction_percent": float(adv_rate_dec), "advance_deduction_amount_vnd": float(advance_deduct_vnd), "retention_percent": float(ret_rate_dec), "retention_amount_vnd": float(retention_deduct_vnd), "net_subtotal_vnd": float(net_subtotal_vnd), "vat_rate_percent": float(vat_rate_dec), "vat_amount_vnd": float(vat_amount_vnd), "total_payable_vnd": float(total_amount_vnd), } # Generate invoice number & series if not provided cur.execute( "SELECT COUNT(*) AS c FROM erp_invoices WHERE direction = 'output'" ) out_count = cur.fetchone()["c"] + 1 final_inv_num = invoice_number or str(out_count).zfill(8) final_inv_series = invoice_series or "1C26TDS" xml_hash_seed = f"DSCons_IPC_{project_id}_{final_inv_series}_{final_inv_num}_{total_amount_vnd}" xml_hash = hashlib.sha256(xml_hash_seed.encode("utf-8")).hexdigest() # Insert into erp_invoices insert_sql = """ INSERT INTO erp_invoices ( direction, invoice_type, invoice_number, invoice_series, template_code, issue_date, seller_tax_code, seller_name, seller_address, buyer_tax_code, buyer_name, buyer_address, subtotal_amount_vnd, vat_rate_percent, vat_amount_vnd, total_amount_vnd, status, signature_valid, signed_by, signed_at, source_channel, reconciliation_status, matched_project_id, notes, ipc_deductions_json, xml_hash_sha256 ) VALUES ( 'output', 'vat', %s, %s, '1', CURRENT_DATE, %s, %s, 'Thôn Tú Đôi 3, xã Nghi Dương, TP Hải Phòng', %s, %s, %s, %s, %s, %s, %s, 'valid', TRUE, %s, CURRENT_TIMESTAMP, 'manual_xml', 'matched_ipc', %s, %s, %s, %s ) RETURNING id; """ cur.execute( insert_sql, ( final_inv_num, final_inv_series, DSCONS_DEFAULT_TAX_CODE, DSCONS_DEFAULT_COMPANY_NAME, final_buyer_mst, final_buyer_name, final_buyer_addr, net_subtotal_vnd, vat_rate_dec, vat_amount_vnd, total_amount_vnd, DSCONS_DEFAULT_COMPANY_NAME, project_id, notes or f"Hóa đơn nghiệm thu thanh toán đợt IPC dự án {proj['project_name']}", json.dumps(ipc_deductions, ensure_ascii=False), xml_hash, ), ) new_inv_id = str(cur.fetchone()["id"]) # Insert Items item_insert_sql = """ INSERT INTO erp_invoice_items ( invoice_id, item_order, item_name, item_code, unit, quantity, unit_price_vnd, amount_before_vat_vnd, vat_rate_percent, vat_amount_vnd, total_item_amount_vnd, cost_category ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s); """ for itm in parsed_items: line_vat = ( itm["amount_before_vat_vnd"] * (vat_rate_dec / Decimal("100.0")) ).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP) line_tot = itm["amount_before_vat_vnd"] + line_vat cur.execute( item_insert_sql, ( new_inv_id, itm["item_order"], itm["item_name"], itm["item_code"], itm["unit"], itm["quantity"], itm["unit_price_vnd"], itm["amount_before_vat_vnd"], vat_rate_dec, line_vat, line_tot, itm["cost_category"], ), ) # Insert Audit Log cur.execute( """ INSERT INTO erp_invoice_audit_logs (invoice_id, action_type, performed_by, details_json) VALUES (%s, 'CREATE_IPC_OUTPUT_INVOICE', %s, %s); """, ( new_inv_id, user_name, json.dumps(ipc_deductions, ensure_ascii=False), ), ) conn.commit() return { "status": "success", "message": f"Đã lập thành công Hóa đơn đề nghị thanh toán IPC số {final_inv_num} (Ký hiệu {final_inv_series}).", "invoice_id": new_inv_id, "ipc_deductions": ipc_deductions, }