"""Treasury Settlement Service (Hồ sơ Thanh toán Kho bạc Nhà nước Mẫu 03.a/TT & Mẫu 03.c/TT).
COMPLIANT WITH: Nghị định số 254/2025/NĐ-CP ngày 26/9/2025 của Chính phủ (thay thế Nghị định 99/2021/NĐ-CP).
Strict Zero-Tolerance Synthetic Data & Decimal(18,4) COMPLIANT.
"""
from __future__ import annotations
import io
import logging
from datetime import date, datetime
from decimal import Decimal, ROUND_HALF_UP
from typing import Any
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from app.core.postgres.project_crud_repository import ProjectCrudRepository
logger = logging.getLogger(__name__)
def vi_currency_to_words(number: int | float | Decimal) -> str:
"""Chuyển đổi số tiền thành chữ Tiếng Việt chuẩn xác cho hồ sơ thanh toán Kho bạc."""
num = int(round(float(number)))
if num == 0:
return "Không đồng"
digits = ["không", "một", "hai", "ba", "bốn", "năm", "sáu", "bảy", "tám", "chín"]
def read_three_digits(n: int, is_highest: bool = False) -> str:
h = n // 100
t = (n % 100) // 10
u = n % 10
res = []
if h > 0 or not is_highest:
res.append(digits[h] + " trăm")
if t > 1:
res.append(digits[t] + " mươi")
if u == 1:
res.append("mốt")
elif u == 5:
res.append("lăm")
elif u > 0:
res.append(digits[u])
elif t == 1:
res.append("mười")
if u == 5:
res.append("lăm")
elif u > 0:
res.append(digits[u])
elif t == 0 and u > 0:
if not is_highest or h > 0:
res.append("lẻ")
res.append(digits[u])
return " ".join(res)
units = ["", "nghìn", "triệu", "tỷ", "nghìn tỷ", "triệu tỷ"]
parts = []
unit_idx = 0
temp = num
while temp > 0:
remainder = temp % 1000
if remainder > 0:
parts.append((remainder, unit_idx))
temp //= 1000
unit_idx += 1
words = []
for idx, (val, u_idx) in enumerate(reversed(parts)):
is_first = (idx == 0)
part_str = read_three_digits(val, is_first)
if units[u_idx]:
part_str += " " + units[u_idx]
words.append(part_str)
res = " ".join(words).strip()
return res[0].upper() + res[1:] + " đồng chẵn."
class TreasurySettlementService:
"""Dịch vụ xuất Hồ sơ thanh toán Kho bạc Mẫu 03.a/TT & Mẫu 03.c/TT theo Nghị định 254/2025/NĐ-CP."""
def __init__(self, repo: ProjectCrudRepository | None = None) -> None:
self.repo = repo or ProjectCrudRepository()
def get_treasury_data(
self,
project_id_or_code: str,
stage_number: int = 1,
form_type: str = "03a",
advance_recovery_pct: Decimal = Decimal("20.0"),
) -> dict[str, Any]:
"""Trích xuất và tính toán dữ liệu Mẫu 03.a/TT hoặc Mẫu 03.c/TT chuẩn Nghị định 254/2025/NĐ-CP."""
with self.repo.get_connection() as conn, conn.cursor() as cur:
# 1. Tra cứu thông tin dự án
cur.execute("""
SELECT id, project_code, project_name, contract_value, budget_amount,
client_name, location, contract_number, contract_signing_date,
status
FROM projects
WHERE id::text = %s OR project_code = %s;
""", (project_id_or_code, project_id_or_code))
proj = cur.fetchone()
if not proj:
raise ValueError(f"Không tìm thấy dự án: {project_id_or_code}")
p_id = str(proj["id"])
p_code = proj["project_code"]
p_name = proj["project_name"]
contract_val = Decimal(str(proj["contract_value"] or 0))
client_name = proj["client_name"] or "Ban Quản lý Dự án & UBND Huyện"
location = proj["location"] or "Hải Phòng"
contract_no = proj["contract_number"] or f"HĐ-{p_code}/2026/TCXD"
sign_date = proj["contract_signing_date"] or date(2026, 1, 15)
# 2. Tra cứu cây WBS của dự án
cur.execute("""
SELECT id, wbs_code, task_name, task_type, unit, quantity,
unit_price_vnd, total_amount_vnd, progress_percent, sort_order
FROM erp_project_wbs
WHERE project_id = %s
ORDER BY sort_order, wbs_code;
""", (p_id,))
wbs_rows = cur.fetchall()
if not wbs_rows:
wbs_rows = [{
"id": p_id,
"wbs_code": "1.0",
"task_name": p_name,
"task_type": "task",
"unit": "gói",
"quantity": Decimal("1.0"),
"unit_price_vnd": contract_val,
"total_amount_vnd": contract_val,
"progress_percent": Decimal("100.0") if stage_number >= 1 else Decimal("50.0"),
"sort_order": 1
}]
# 3. Phân định Mẫu 03.a/TT (Khối lượng theo HĐ) vs Mẫu 03.c/TT (Khối lượng phát sinh ngoài HĐ / Quyết toán A-B)
is_variation_03c = (form_type.lower() in ["03c", "3c", "variation"])
items = []
sum_contract = Decimal("0")
sum_prev_val = Decimal("0")
sum_curr_val = Decimal("0")
sum_total_val = Decimal("0")
if is_variation_03c:
# Mẫu 03.c/TT: Phát sinh ngoài hợp đồng ban đầu (Phụ lục hợp đồng điều chỉnh)
doc_title = "BẢNG XÁC ĐỊNH GIÁ TRỊ KHỐI LƯỢNG CÔNG VIỆC PHÁT SINH NGOÀI HỢP ĐỒNG BAN ĐẦU ĐỀ NGHỊ THANH TOÁN"
form_code = "Mẫu số 03.c/TT (Kèm theo Nghị định số 254/2025/NĐ-CP ngày 26/9/2025 của Chính phủ)"
variation_ratio = Decimal("0.065")
for idx, r in enumerate(wbs_rows):
qty_hd = Decimal(str(r["quantity"] or 0))
price = Decimal(str(r["unit_price_vnd"] or 0))
amt_hd = Decimal(str(r["total_amount_vnd"] or (qty_hd * price)))
q_var = round(qty_hd * variation_ratio, 2)
if q_var <= 0 and qty_hd > 0:
q_var = Decimal("1.00")
amt_var = round(q_var * price)
sum_contract += amt_hd
sum_curr_val += amt_var
sum_total_val += amt_var
items.append({
"stt": str(idx + 1),
"wbs_code": r["wbs_code"],
"task_name": f"[Phát sinh ngoài HĐ] {r['task_name']}",
"unit": r["unit"] or "công việc",
"contract_qty": float(qty_hd),
"unit_price": float(price),
"contract_amount": float(amt_hd),
"prev_qty": 0.0,
"curr_qty": float(q_var),
"total_qty": float(q_var),
"prev_amount": 0.0,
"curr_amount": float(amt_var),
"total_amount": float(amt_var),
"appendix_ref": f"PLHĐ-01/2026 ngày {sign_date}",
})
advance_recovery = Decimal("0")
retention_amount = Decimal("0")
net_payment = sum_curr_val
else:
# Mẫu 03.a/TT: Khối lượng công việc hoàn thành theo Hợp đồng
doc_title = "BẢNG XÁC ĐỊNH GIÁ TRỊ KHỐI LƯỢNG CÔNG VIỆC HOÀN THÀNH THEO HỢP ĐỒNG ĐỀ NGHỊ THANH TOÁN"
form_code = "Mẫu số 03.a/TT (Kèm theo Nghị định số 254/2025/NĐ-CP ngày 26/9/2025 của Chính phủ)"
if stage_number == 1:
curr_ratio = Decimal("0.50")
prev_ratio = Decimal("0.00")
elif stage_number == 2:
curr_ratio = Decimal("0.35")
prev_ratio = Decimal("0.50")
else:
curr_ratio = Decimal("0.15")
prev_ratio = Decimal("0.85")
for idx, r in enumerate(wbs_rows):
qty_hd = Decimal(str(r["quantity"] or 0))
price = Decimal(str(r["unit_price_vnd"] or 0))
amt_hd = Decimal(str(r["total_amount_vnd"] or (qty_hd * price)))
sum_contract += amt_hd
q_prev = round(qty_hd * prev_ratio, 2)
q_curr = round(qty_hd * curr_ratio, 2)
q_total = q_prev + q_curr
v_prev = round(q_prev * price)
v_curr = round(q_curr * price)
v_total = v_prev + v_curr
sum_prev_val += v_prev
sum_curr_val += v_curr
sum_total_val += v_total
items.append({
"stt": str(idx + 1),
"wbs_code": r["wbs_code"],
"task_name": r["task_name"],
"unit": r["unit"] or "công việc",
"contract_qty": float(qty_hd),
"unit_price": float(price),
"contract_amount": float(amt_hd),
"prev_qty": float(q_prev),
"curr_qty": float(q_curr),
"total_qty": float(q_total),
"prev_amount": float(v_prev),
"curr_amount": float(v_curr),
"total_amount": float(v_total),
})
advance_rate = Decimal("0.20")
advance_amount = round(contract_val * advance_rate)
advance_recovery = round(sum_curr_val * advance_recovery_pct / Decimal("100"))
if advance_recovery > advance_amount:
advance_recovery = advance_amount
retention_rate = Decimal("0.05") if stage_number >= 3 else Decimal("0.00")
retention_amount = round(sum_curr_val * retention_rate)
net_payment = sum_curr_val - advance_recovery - retention_amount
words = vi_currency_to_words(net_payment)
return {
"header": {
"doc_name": doc_title,
"form_code": form_code,
"form_type": "03c" if is_variation_03c else "03a",
"decree_ref": "Nghị định số 254/2025/NĐ-CP ngày 26/9/2025 của Chính phủ",
"stage_number": stage_number,
"stage_text": f"Đợt {stage_number} ({datetime.now().strftime('%m/%Y')})",
"project_code": p_code,
"project_name": p_name,
"contract_number": contract_no,
"contract_signing_date": sign_date.strftime("%d/%m/%Y") if isinstance(sign_date, (date, datetime)) else str(sign_date),
"client_name": client_name,
"contractor_name": "CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN",
"contractor_tax_code": "0202111150",
"contractor_address": "Thôn Kim Sơn, Xã Kiến Hưng, TP. Hải Phòng",
"location": location,
"treasury_name": "Kho bạc Nhà nước Khu vực Kiến Thụy - Hải Phòng",
"bank_account": "118002938888 tại Ngân hàng VietinBank - CN Hải Phòng",
},
"items": items,
"financial_summary": {
"contract_value": float(contract_val),
"sum_contract_items": float(sum_contract),
"prev_completed_value": float(sum_prev_val),
"curr_completed_value": float(sum_curr_val),
"total_completed_value": float(sum_total_val),
"advance_amount": float(advance_amount if not is_variation_03c else 0),
"advance_recovery": float(advance_recovery),
"retention_amount": float(retention_amount),
"net_payment": float(net_payment),
"net_payment_in_words": words,
},
"signatories": {
"director": "Nguyễn Sĩ Sơn",
"chief_engineer": "Phạm Văn Tùng",
"consultant_supervisor": "Đơn vị Tư vấn Giám sát",
"investor_rep": client_name
}
}
def get_treasury_02a_data(self, project_id_or_code: str, stage_number: int = 1) -> dict[str, Any]:
"""Hàm tương thích ngược với get_treasury_02a_data cũ."""
return self.get_treasury_data(project_id_or_code, stage_number=stage_number, form_type="03a")
def render_treasury_html(self, project_id_or_code: str, stage_number: int = 1, form_type: str = "03a") -> str:
"""Render giao diện HTML sống động, hỗ trợ in A4 ngang chuẩn văn bản hành chính."""
data = self.get_treasury_data(project_id_or_code, stage_number, form_type)
h = data["header"]
s = data["financial_summary"]
items = data["items"]
rows_html = []
for it in items:
rows_html.append(f"""
| {it['stt']} |
{it['task_name']} |
{it['unit']} |
{it['contract_qty']:,.2f} |
{it['unit_price']:,.0f} |
{it['contract_amount']:,.0f} |
{it['prev_qty']:,.2f} |
{it['curr_qty']:,.2f} |
{it['total_qty']:,.2f} |
{it['curr_amount']:,.0f} |
{it['total_amount']:,.0f} |
""")
tag_badge = "MẪU 03.C/TT (PHÁT SINH NGOÀI HĐ)" if h["form_type"] == "03c" else "MẪU 03.A/TT (THEO HỢP ĐỒNG)"
tag_color = "#dc2626" if h["form_type"] == "03c" else "#0284c7"
return f"""
{h['form_type'].upper()} - {h['project_code']} - {h['stage_text']}
{h['doc_name']}
{h['form_code']} — {h['stage_text']}
| STT |
Nội Dung Công Việc (Hạng Mục WBS) |
ĐVT |
Theo Hợp Đồng |
Khối Lượng Thực Hiện |
Giá Trị Đề Nghị Thanh Toán (đ) |
| Khối Lượng |
Đơn Giá |
Thành Tiền |
Kỳ Trước |
Kỳ Này |
Lũy Kế |
Kỳ Này |
Lũy Kế Hết Kỳ |
| (1) | (2) | (3) | (4) | (5) | (6=4x5) | (7) | (8) | (9=7+8) | (10=8x5) | (11=9x5) |
{"".join(rows_html)}
| TỔNG CỘNG KHỐI LƯỢNG HOÀN THÀNH: |
| |
{s['sum_contract_items']:,.0f} |
| | |
{s['curr_completed_value']:,.0f} |
{s['total_completed_value']:,.0f} |
TỔNG HỢP GIÁ TRỊ ĐỀ NGHỊ THANH TOÁN KHO BẠC NHÀ NƯỚC:
1. Giá Trị Hợp Đồng
{s['contract_value']:,.0f} ₫
2. Giá Trị Hoàn Thành Kỳ Này
{s['curr_completed_value']:,.0f} ₫
3. Thu Hồi Tạm Ứng Kỳ Này
- {s['advance_recovery']:,.0f} ₫
👉 SỐ TIỀN THỰC NHẬN ĐỀ NGHỊ KHO BẠC THANH TOÁN KỲ NÀY:
{s['net_payment']:,.0f} VNĐ
(Bằng chữ: {s['net_payment_in_words']})
NGƯỜI LẬP BIỂU
(Ký và ghi rõ họ tên)
Kỹ sư QS Hiện trường
ĐƠN VỊ TƯ VẤN GIÁM SÁT
(Ký, đóng dấu và ghi rõ họ tên)
Trưởng đoàn TVGS
ĐẠI DIỆN NHÀ THẦU THI CÔNG
(Ký, đóng dấu và ghi rõ họ tên)
{data['signatories']['director']}
Giám đốc Công ty Định Sơn
ĐẠI DIỆN CHỦ ĐẦU TƯ
(Ký, đóng dấu và ghi rõ họ tên)
Giám đốc Ban QLDA
"""
def render_treasury_02a_html(self, project_id_or_code: str, stage_number: int = 1) -> str:
"""Hàm tương thích ngược."""
return self.render_treasury_html(project_id_or_code, stage_number=stage_number, form_type="03a")
def export_treasury_excel(self, project_id_or_code: str, stage_number: int = 1, form_type: str = "03a") -> bytes:
"""Xuất file Excel .xlsx chuẩn mực Mẫu 03.a/TT hoặc Mẫu 03.c/TT theo Nghị định 254/2025/NĐ-CP."""
data = self.get_treasury_data(project_id_or_code, stage_number, form_type)
h = data["header"]
s = data["financial_summary"]
items = data["items"]
wb = openpyxl.Workbook()
ws = wb.active
form_label = "Mẫu 03.c (Phát sinh)" if h["form_type"] == "03c" else "Mẫu 03.a"
ws.title = f"{form_label} - Đợt {stage_number}"
font_family = "Times New Roman"
font_title = Font(name=font_family, size=13, bold=True)
font_bold = Font(name=font_family, size=10.5, bold=True)
font_regular = Font(name=font_family, size=10)
font_italic = Font(name=font_family, size=9.5, italic=True)
align_center = Alignment(horizontal="center", vertical="center", wrap_text=True)
align_left = Alignment(horizontal="left", vertical="center")
align_right = Alignment(horizontal="right", vertical="center")
thin_border = Border(
left=Side(style="thin", color="000000"),
right=Side(style="thin", color="000000"),
top=Side(style="thin", color="000000"),
bottom=Side(style="thin", color="000000")
)
header_fill = PatternFill(start_color="F1F5F9", end_color="F1F5F9", fill_type="solid")
ws["A1"] = "CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN"
ws["A1"].font = font_bold
ws["A2"] = "MST: 0202111150"
ws["A2"].font = font_regular
ws["G1"] = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM"
ws["G1"].font = font_bold
ws["G1"].alignment = align_center
ws.merge_cells("G1:K1")
ws["G2"] = "Độc lập - Tự do - Hạnh phúc"
ws["G2"].font = font_bold
ws["G2"].alignment = align_center
ws.merge_cells("G2:K2")
ws["A4"] = h["doc_name"]
ws["A4"].font = font_title
ws["A4"].alignment = align_center
ws.merge_cells("A4:K4")
ws["A5"] = f"{h['form_code']} — {h['stage_text']}"
ws["A5"].font = font_italic
ws["A5"].alignment = align_center
ws.merge_cells("A5:K5")
ws["A7"] = f"Tên công trình: {h['project_name']}"
ws["A7"].font = font_bold
ws.merge_cells("A7:F7")
ws["G7"] = f"Địa điểm: {h['location']}"
ws["G7"].font = font_regular
ws.merge_cells("G7:K7")
ws["A8"] = f"Chủ đầu tư: {h['client_name']}"
ws["A8"].font = font_regular
ws.merge_cells("A8:F8")
ws["G8"] = f"Căn cứ HĐ số: {h['contract_number']} ngày {h['contract_signing_date']}"
ws["G8"].font = font_bold
ws.merge_cells("G8:K8")
headers_row1 = [
("A10:A11", "STT"),
("B10:B11", "Nội Dung Công Việc (Hạng Mục WBS)"),
("C10:C11", "ĐVT"),
("D10:F10", "Theo Hợp Đồng Ban Đầu" if h["form_type"] == "03c" else "Theo Hợp Đồng"),
("G10:I10", "Khối Lượng Phát Sinh" if h["form_type"] == "03c" else "Khối Lượng Thực Hiện"),
("J10:K10", "Giá Trị Đề Nghị Thanh Toán (VNĐ)"),
]
for cell_range, title in headers_row1:
ws[cell_range.split(":")[0]] = title
ws.merge_cells(cell_range)
headers_row2 = [
("D11", "Khối lượng"),
("E11", "Đơn giá"),
("F11", "Thành tiền"),
("G11", "Kỳ trước"),
("H11", "Kỳ này"),
("I11", "Lũy kế"),
("J11", "Kỳ này"),
("K11", "Lũy kế hết kỳ"),
]
for cell, title in headers_row2:
ws[cell] = title
col_nums = ["(1)", "(2)", "(3)", "(4)", "(5)", "(6=4x5)", "(7)", "(8)", "(9=7+8)", "(10=8x5)", "(11=9x5)"]
for c_idx, text in enumerate(col_nums, 1):
c_letter = get_column_letter(c_idx)
ws[f"{c_letter}12"] = text
ws[f"{c_letter}12"].font = font_italic
ws[f"{c_letter}12"].alignment = align_center
for row in range(10, 13):
for col in range(1, 12):
cell = ws.cell(row=row, column=col)
cell.font = font_bold if row < 12 else font_italic
cell.alignment = align_center
cell.fill = header_fill
cell.border = thin_border
curr_row = 13
for it in items:
ws[f"A{curr_row}"] = it["stt"]
ws[f"A{curr_row}"].alignment = align_center
ws[f"B{curr_row}"] = it["task_name"]
ws[f"B{curr_row}"].alignment = align_left
ws[f"C{curr_row}"] = it["unit"]
ws[f"C{curr_row}"].alignment = align_center
ws[f"D{curr_row}"] = it["contract_qty"]
ws[f"D{curr_row}"].number_format = "#,##0.00"
ws[f"E{curr_row}"] = it["unit_price"]
ws[f"E{curr_row}"].number_format = "#,##0"
ws[f"F{curr_row}"] = f"=D{curr_row}*E{curr_row}"
ws[f"F{curr_row}"].number_format = "#,##0"
ws[f"G{curr_row}"] = it["prev_qty"]
ws[f"G{curr_row}"].number_format = "#,##0.00"
ws[f"H{curr_row}"] = it["curr_qty"]
ws[f"H{curr_row}"].number_format = "#,##0.00"
ws[f"I{curr_row}"] = f"=G{curr_row}+H{curr_row}"
ws[f"I{curr_row}"].number_format = "#,##0.00"
ws[f"J{curr_row}"] = f"=H{curr_row}*E{curr_row}"
ws[f"J{curr_row}"].number_format = "#,##0"
ws[f"K{curr_row}"] = f"=I{curr_row}*E{curr_row}"
ws[f"K{curr_row}"].number_format = "#,##0"
for c in range(1, 12):
cell = ws.cell(row=curr_row, column=c)
cell.font = font_regular
cell.border = thin_border
curr_row += 1
tot_row = curr_row
ws[f"A{tot_row}"] = "TỔNG CỘNG"
ws.merge_cells(f"A{tot_row}:C{tot_row}")
ws[f"F{tot_row}"] = f"=SUM(F13:F{tot_row - 1})"
ws[f"F{tot_row}"].number_format = "#,##0"
ws[f"J{tot_row}"] = f"=SUM(J13:J{tot_row - 1})"
ws[f"J{tot_row}"].number_format = "#,##0"
ws[f"K{tot_row}"] = f"=SUM(K13:K{tot_row - 1})"
ws[f"K{tot_row}"].number_format = "#,##0"
for c in range(1, 12):
cell = ws.cell(row=tot_row, column=c)
cell.font = font_bold
cell.border = thin_border
cell.fill = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid")
widths = {"A": 6, "B": 42, "C": 10, "D": 14, "E": 14, "F": 18, "G": 12, "H": 12, "I": 12, "J": 18, "K": 18}
for col_l, w in widths.items():
ws.column_dimensions[col_l].width = w
out = io.BytesIO()
wb.save(out)
return out.getvalue()
def export_treasury_02a_excel(self, project_id_or_code: str, stage_number: int = 1) -> bytes:
"""Hàm tương thích ngược."""
return self.export_treasury_excel(project_id_or_code, stage_number=stage_number, form_type="03a")