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]]]:
    """Load PO lines plus the supplier / UM / domain the receipt must inherit.

    Domain / Supp / UM must be resolved here, not left NULL: the S5 purchase-receipt
    MDP transform joins ItemMaster / SuppMaster / PurOrdDetail on (Domain, ...), and a
    NULL domain makes every dimension lookup miss, so the page shows blank supplier /
    item name / spec / PO number. Domain is taken from the supplier master of the PO's
    own supplier — a purchase receipt belongs to the ERP domain its supplier is
    defined in — falling back to the PO line, then the PO header.
    """
    tenant_ids = ",".join(str(x) for x in TENANTS)
    cursor.execute(
        f"""
        SELECT d.tenant_id,d.PurOrd,d.Line,d.ItemNum,d.QtyOrded,d.Potype,d.Site,d.UM,
               (SELECT m.Supp FROM PurOrdMaster m
                 WHERE m.tenant_id=d.tenant_id AND m.PurOrd=d.PurOrd LIMIT 1) AS Supp,
               COALESCE(
                 (SELECT s.Domain FROM SuppMaster s
                   WHERE s.tenant_id=d.tenant_id
                     AND s.Supp=(SELECT m2.Supp FROM PurOrdMaster m2
                                  WHERE m2.tenant_id=d.tenant_id AND m2.PurOrd=d.PurOrd LIMIT 1)
                   LIMIT 1),
                 d.Domain,
                 (SELECT m3.Domain FROM PurOrdMaster m3
                   WHERE m3.tenant_id=d.tenant_id AND m3.PurOrd=d.PurOrd LIMIT 1)
               ) AS Domain
        FROM PurOrdDetail d
        WHERE d.tenant_id IN ({tenant_ids})
          AND IFNULL(d.PurOrd,'')<>'' AND IFNULL(d.ItemNum,'')<>'' AND IFNULL(d.QtyOrded,0)>0
        ORDER BY d.tenant_id,d.PurOrd,d.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,Supp,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,%s,'rc',%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"],
                            first["Supp"],
                            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,Supp,RctType,Receiver,Line,ItemNum,UM,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,%s,'rc',%s,%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"],
                                line["Supp"],
                                receiver,
                                line_index,
                                line["ItemNum"],
                                line["UM"],
                                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())