AiDOPWarehouse/doc/plan/sql/uat-data/run_uat_purchase_receipt_fact_seed.py

#!/usr/bin/env python3
"""Create tenant-scoped UAT purchase receipt business documents."""

from __future__ import annotations

import argparse
import json
from collections import defaultdict
from datetime import datetime, timedelta
from decimal import Decimal
from typing import Any

from apply_sql_file import connect


TENANTS = {
    838257186181189: ("A", Decimal("0.85")),
    838257212780613: ("B", Decimal("0.70")),
    838257237606469: ("DEMO", Decimal("0.92")),
}
PREFIX = "UATRCT-"


def load_po_lines(cursor: Any) -> dict[int, list[dict[str, Any]]]:
    tenant_ids = ",".join(str(x) for x in TENANTS)
    cursor.execute(
        f"""
        SELECT tenant_id,PurOrd,Line,ItemNum,QtyOrded,Potype,Domain,Site
        FROM PurOrdDetail
        WHERE tenant_id IN ({tenant_ids})
          AND IFNULL(PurOrd,'')<>'' AND IFNULL(ItemNum,'')<>'' AND IFNULL(QtyOrded,0)>0
        ORDER BY tenant_id,PurOrd,Line
        """
    )
    rows: dict[int, list[dict[str, Any]]] = defaultdict(list)
    for row in cursor.fetchall():
        rows[int(row["tenant_id"])].append(row)
    return rows


def apply() -> dict[str, Any]:
    conn = connect()
    result: dict[str, Any] = {"masters": 0, "details": 0, "tenants": {}}
    try:
        conn.begin()
        with conn.cursor() as cursor:
            tenant_ids = tuple(TENANTS)
            placeholders = ",".join(["%s"] * len(tenant_ids))
            cursor.execute(
                f"DELETE FROM PurOrdRctDetail WHERE tenant_id IN ({placeholders}) AND Receiver LIKE %s",
                (*tenant_ids, f"{PREFIX}%"),
            )
            cursor.execute(
                f"DELETE FROM PurOrdRctMaster WHERE tenant_id IN ({placeholders}) AND Receiver LIKE %s",
                (*tenant_ids, f"{PREFIX}%"),
            )
            cursor.execute("SELECT GREATEST(IFNULL(MAX(RecID),0),0) AS max_id FROM PurOrdRctMaster")
            master_id = int(cursor.fetchone()["max_id"])
            cursor.execute("SELECT GREATEST(IFNULL(MAX(RecID),0),0) AS max_id FROM PurOrdRctDetail")
            detail_id = int(cursor.fetchone()["max_id"])
            lines_by_tenant = load_po_lines(cursor)
            now = datetime.now().replace(microsecond=0)

            for tenant_id, (code, ratio) in TENANTS.items():
                grouped: dict[str, list[dict[str, Any]]] = defaultdict(list)
                for line in lines_by_tenant.get(tenant_id, []):
                    grouped[str(line["PurOrd"])].append(line)
                tenant_result = {"masters": 0, "details": 0}
                for order_index, (po_no, lines) in enumerate(grouped.items(), 1):
                    master_id += 1
                    receiver = f"{PREFIX}{code}-{order_index:04d}"
                    receipt_date = now - timedelta(days=order_index % 3)
                    first = lines[0]
                    cursor.execute(
                        """
                        INSERT INTO PurOrdRctMaster
                          (RecID,Domain,RctType,Receiver,RctDate,OrdNbr,PerformDate,Site,BusinessID,
                           CreateUser,UpdateUser,CreateTime,UpdateTime,IsActive,IsConfirm,Print,
                           IsChanged,TaxIn,IsExport,IsPlan,IsBackwash,Remark,tenant_id)
                        VALUES
                          (%s,%s,'RCT',%s,%s,%s,%s,%s,%s,
                           'UAT_GENERATOR','UAT_GENERATOR',NOW(),NOW(),1,1,0,
                           0,0,0,0,0,'UAT_GENERATOR：采购收货业务事实',%s)
                        """,
                        (
                            master_id,
                            first["Domain"],
                            receiver,
                            receipt_date,
                            po_no,
                            receipt_date,
                            first["Site"],
                            master_id,
                            tenant_id,
                        ),
                    )
                    result["masters"] += 1
                    tenant_result["masters"] += 1

                    for line_index, line in enumerate(lines, 1):
                        detail_id += 1
                        ordered = Decimal(str(line["QtyOrded"]))
                        received = (ordered * ratio).quantize(Decimal("0.00001"))
                        cursor.execute(
                            """
                            INSERT INTO PurOrdRctDetail
                              (RecID,Domain,RctType,Receiver,Line,ItemNum,Dimension1,Dimension2,
                               OrdNbr,OrdLine,RctDate,POCost,QtyOrded,RctQty,QtyReceived,Site,
                               Location,Potype,BusinessID,CreateUser,UpdateUser,CreateTime,UpdateTime,
                               IsActive,IsConfirm,IsChanged,TaxIn,UMConversion,IsClosed,IsRounding,
                               BlanketLine,ProjectLine,RctLine,QtyReturn,PurOrdRctRecID,QcQty,QC,
                               Remark,tenant_id)
                            VALUES
                              (%s,%s,'RCT',%s,%s,%s,'','',%s,%s,%s,1,%s,%s,%s,%s,
                               'UAT',%s,%s,'UAT_GENERATOR','UAT_GENERATOR',NOW(),NOW(),
                               1,1,0,0,1,0,0,0,0,%s,0,%s,%s,'PASS',
                               'UAT_GENERATOR：采购收货明细',%s)
                            """,
                            (
                                detail_id,
                                line["Domain"],
                                receiver,
                                line_index,
                                line["ItemNum"],
                                po_no,
                                int(line["Line"] or line_index),
                                receipt_date,
                                ordered,
                                received,
                                received,
                                line["Site"],
                                line["Potype"] or "PO",
                                detail_id,
                                line_index,
                                master_id,
                                received,
                                tenant_id,
                            ),
                        )
                        result["details"] += 1
                        tenant_result["details"] += 1
                result["tenants"][code] = tenant_result
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()
    return result


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--apply", action="store_true")
    args = parser.parse_args()
    result: dict[str, Any] = {"mode": "dry-run", "tenants": TENANTS}
    if args.apply:
        result = {"mode": "apply", **apply()}
    print(json.dumps(result, ensure_ascii=False, indent=2, default=str))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())