import sys, os sys.path.insert(0, os.path.abspath('.')) # Script đồng bộ 143 dự án thi công từ Excel vào PostgreSQL DB import openpyxl import re from datetime import datetime from uuid import uuid4 from app.core.postgres.erp_client import ErpDatabaseClient COMPANY_ID = "0753d827-90e1-4894-9c16-266ca572c5eb" EXCEL_PATH = "C:/Projects/DSCons/CongTrinh/Bảng theo dõi công trình.xlsx" EXISTING_CODE_MAPPING = { (2026, 2): "DA-KM-DENCS-2026", (2026, 9): "DA-IB2600471890", (2026, 10): "DA-2608281122", (2026, 26): "DA-DADO-PHULIEN-2026", (2026, 43): "DA-THCS-DAIDONG-2026", (2025, 11): "DA-UBND-KIENQUOC-2024-2025", (2025, 12): "DA-UBND-NGUPHUC-2025", (2025, 14): "DA-UBND-TANTRAO-2025", (2025, 16): "DA-UBND-KIENHUNG-2025", (2025, 17): "DA-BAOLONG-SANLDT-2025", } def classify_project_type(p_name: str) -> str: p_lower = p_name.lower() if any(k in p_lower for k in ["kênh", "cống", "đắp bờ", "nạo vét", "ngăn mặn", "trạm bơm", "thủy lợi"]): return "Thủy lợi & Kè đê" elif any(k in p_lower for k in ["điện", "chiếu sáng"]): return "Cơ điện & Chiếu sáng" elif any(k in p_lower for k in ["đường", "giao thông", "hạ tầng", "cống hộp"]): return "Hạ tầng & Giao thông" elif any(k in p_lower for k in ["vận chuyển", "bốc xúc", "xe ben", "cước"]): return "Vận tải cơ giới & Xe ben" elif any(k in p_lower for k in ["nhôm kính", "cơ khí", "kết cấu thép", "hoa sắt", "composite", "lan can"]): return "Gia công Cơ khí & Nhôm kính" return "Thi công Xây lắp Dân dụng" def parse_excel_projects(): wb = openpyxl.load_workbook(EXCEL_PATH, data_only=True) projects = [] for sname in ["2026", "2025", "2024", "2023", "2022"]: ws = wb[sname] tot_col = 20 if sname == "2024" else 19 gt_col = 14 if sname == "2024" else 7 inv_col = 18 if sname == "2024" else 15 for r in range(5, ws.max_row + 1): stt = ws.cell(r, 1).value name = ws.cell(r, 2).value if not stt and not name: continue stt_int = int(stt) if stt else 0 year_int = int(sname) if (year_int, stt_int) in EXISTING_CODE_MAPPING: code = EXISTING_CODE_MAPPING[(year_int, stt_int)] else: code = f"DA-{year_int}-{stt_int:02d}" name_str = str(name).strip() pkg = str(ws.cell(r, 3).value or "").strip() client = str(ws.cell(r, 4).value or "").strip() contract_num = str(ws.cell(r, 5).value or "").strip() if ws.cell(r, 5).value else None sign_date = ws.cell(r, 6).value if isinstance(ws.cell(r, 6).value, datetime) else None start_date = ws.cell(r, 8 if sname != "2024" else 7).value start_date = start_date if isinstance(start_date, datetime) else None exp_date = ws.cell(r, 10 if sname != "2024" else 9).value exp_date = exp_date if isinstance(exp_date, datetime) else None act_date = ws.cell(r, 13 if sname != "2024" else 12).value act_date = act_date if isinstance(act_date, datetime) else None duration = ws.cell(r, 9 if sname != "2024" else 8).value duration_days = int(duration) if isinstance(duration, (int, float)) else 180 c_val = ws.cell(r, gt_col).value contract_val = float(c_val) if isinstance(c_val, (int, float)) else None tot_val = ws.cell(r, tot_col).value settle_val = float(tot_val) if isinstance(tot_val, (int, float)) else None inv_str = str(ws.cell(r, inv_col).value or "").strip() notes_str = str(ws.cell(r, 24 if sname != "2024" else 21).value or "").strip() p_type = classify_project_type(name_str) if "kiến minh" in name_str.lower() or "kiến minh" in client.lower(): location = "Xã Kiến Minh, TP Hải Phòng" elif "kiến hưng" in name_str.lower() or "kiến hưng" in client.lower(): location = "Xã Kiến Hưng, TP Hải Phòng" elif "nghi dương" in name_str.lower() or "nghi dương" in client.lower(): location = "Xã Nghi Dương, TP Hải Phòng" elif "kiến quốc" in name_str.lower() or "kiến quốc" in client.lower(): location = "Xã Kiến Quốc, TP Hải Phòng" elif "tân trào" in name_str.lower() or "tân trào" in client.lower(): location = "Xã Tân Trào, TP Hải Phòng" elif "an lão" in name_str.lower(): location = "Huyện An Lão, TP Hải Phòng" elif "kiến thụy" in name_str.lower() or "kiến thụy" in client.lower(): location = "Huyện Kiến Thụy, TP Hải Phòng" else: location = "TP Hải Phòng" if year_int < 2026: status = "completed" progress = 100 else: if settle_val and settle_val > 0: status = "active" progress = 90 else: status = "active" progress = 60 priority = "high" if (contract_val and contract_val >= 1_000_000_000) else "medium" budget = contract_val if contract_val else (settle_val if settle_val else 0) projects.append({ "code": code, "name": name_str, "package": pkg, "client": client, "contract_number": contract_num, "contract_signing_date": sign_date.date() if sign_date else None, "start_date": start_date.date() if start_date else None, "expected_end_date": exp_date.date() if exp_date else None, "actual_end_date": act_date.date() if act_date else None, "duration_days": duration_days, "contract_value": contract_val, "budget_amount": budget, "settlement_value": settle_val, "project_type": p_type, "location": location, "status": status, "priority": priority, "progress_percent": progress, "invoice_str": inv_str, "notes": notes_str, "year": year_int, "stt": stt_int, }) return projects def sync_projects(): projects = parse_excel_projects() print(f"Tổng số dự án bóc tách từ Excel: {len(projects)} dự án.") db = ErpDatabaseClient() with db.get_connection() as conn: with conn.cursor() as cur: macro_codes = [ "DA-DADO-THUYLDT-2022-2026", "DA-RANGDONG-HTDT-2022-2026", "DA-KM-BENKEM-2025-2026", "DA-DADO-XNXL-2024", "DA-MINHTIEN-XAYLAP-2024-2025", "DA-BAOLOC-KIENTHUY-2025-2026", "DA-THIENDUYEN-KIENHAI-2026" ] cur.execute(""" UPDATE projects SET status = 'archived', project_type = 'Hợp đồng khung / Lũy kế', notes = '[Chương trình lũy kế lịch sử - Đã phân rã chi tiết thành 143 dự án độc lập]', updated_at = NOW() WHERE project_code = ANY(%s); """, (macro_codes,)) print(f"Đã chuyển đổi an toàn {cur.rowcount} chương trình khung thành trạng thái 'archived'.") inserted = 0 updated = 0 project_id_map = {} for p in projects: cur.execute("SELECT id FROM projects WHERE project_code = %s;", (p["code"],)) existing = cur.fetchone() if existing: pid = existing["id"] cur.execute(""" UPDATE projects SET project_name = %s, client_name = %s, location = %s, description = %s, project_type = %s, status = %s, priority = %s, budget_amount = %s, contract_value = %s, start_date = %s, expected_end_date = %s, actual_end_date = %s, progress_percent = %s, contract_number = %s, contract_signing_date = %s, contract_duration_days = %s, notes = %s, updated_at = NOW() WHERE id = %s; """, ( p["name"], p["client"], p["location"], p["package"], p["project_type"], p["status"], p["priority"], p["budget_amount"], p["contract_value"], p["start_date"], p["expected_end_date"], p["actual_end_date"], p["progress_percent"], p["contract_number"], p["contract_signing_date"], p["duration_days"], p["notes"], pid )) updated += 1 else: cur.execute(""" INSERT INTO projects ( company_id, project_code, project_name, client_name, location, description, project_type, status, priority, budget_amount, contract_value, start_date, expected_end_date, actual_end_date, progress_percent, contract_number, contract_signing_date, contract_duration_days, notes, created_at, updated_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW() ) RETURNING id; """, ( COMPANY_ID, p["code"], p["name"], p["client"], p["location"], p["package"], p["project_type"], p["status"], p["priority"], p["budget_amount"], p["contract_value"], p["start_date"], p["expected_end_date"], p["actual_end_date"], p["progress_percent"], p["contract_number"], p["contract_signing_date"], p["duration_days"], p["notes"] )) pid = cur.fetchone()["id"] inserted += 1 project_id_map[p["code"]] = pid print(f"Hoàn tất nạp dự án: Thêm mới {inserted} dự án | Cập nhật {updated} dự án.") # Reconcile output invoices cur.execute(""" SELECT id, invoice_series, invoice_number, issue_date, buyer_name, total_amount_vnd FROM erp_invoices WHERE direction = 'output'; """) output_invoices = cur.fetchall() remapped_count = 0 for inv in output_invoices: num_int = int(inv["invoice_number"]) num_str_short = str(num_int) num_str_padded = f"{num_int:08d}" inv_year = inv["issue_date"].year if inv["issue_date"] else None target_code = None for p in projects: if inv_year and p["year"] != inv_year: continue inv_ref = p["invoice_str"] if not inv_ref: continue if num_str_short in re.findall(r"\b\d+\b", inv_ref) or num_str_padded in inv_ref: target_code = p["code"] break if target_code and target_code in project_id_map: cur.execute(""" UPDATE erp_invoices SET matched_project_id = %s WHERE id = %s; """, (project_id_map[target_code], inv["id"])) remapped_count += 1 print(f"Đã đối soát và liên kết chuẩn xác {remapped_count} hóa đơn đầu ra vào từng dự án đơn vị.") conn.commit() if __name__ == "__main__": sync_projects()