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

#!/usr/bin/env python3
"""Seed tenant-scoped FQC tasks and results for the S7 formal KPI pipeline."""

from __future__ import annotations

import argparse
import json
from datetime import date, datetime, time, timedelta
from typing import Any

from apply_sql_file import connect


DEFAULT_TENANT = 838257186181189
TASK_PREFIX = "UAT-S7-FQCBJ-"
RESULT_PREFIX = "UAT-S7-FQC-"


def seed(tenant_id: int, dry_run: bool) -> dict[str, Any]:
    result: dict[str, Any] = {
        "tenantId": tenant_id,
        "dryRun": dry_run,
        "tasks": 0,
        "results": 0,
    }
    conn = connect()
    try:
        conn.begin()
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT WorkOrd, ItemNum, QtyOrded, DueDate
                FROM WorkOrdMaster
                WHERE tenant_id=%s AND WorkOrd LIKE 'UATA-WO-%%'
                ORDER BY WorkOrd
                """,
                (tenant_id,),
            )
            orders = cur.fetchall()
            if not orders:
                raise RuntimeError(f"tenant {tenant_id} has no UATA work orders")

            cur.execute("SELECT COALESCE(MAX(id),0) max_id FROM qms_fqcbj")
            task_id_base = int(cur.fetchone()["max_id"])
            cur.execute("SELECT COALESCE(MAX(id),0) max_id FROM qms_qcpp_inspbill")
            result_id_base = int(cur.fetchone()["max_id"])

            if not dry_run:
                cur.execute(
                    "DELETE FROM qms_qcpp_inspbill WHERE tenant_id=%s AND FBILLNO LIKE %s",
                    (tenant_id, f"{RESULT_PREFIX}%"),
                )
                cur.execute(
                    "DELETE FROM qms_fqcbj WHERE tenant_id=%s AND FBILLNO LIKE %s",
                    (tenant_id, f"{TASK_PREFIX}%"),
                )

            today = date.today()
            for index, order in enumerate(orders):
                seq = index + 1
                task_id = task_id_base + seq
                result_id = result_id_base + seq
                apply_day = today - timedelta(days=len(orders) - seq)
                apply_at = datetime.combine(apply_day, time(hour=8))
                start_at = apply_at + timedelta(hours=1 + index % 2)
                finish_at = start_at + timedelta(hours=2 + index % 7)
                task_no = f"{TASK_PREFIX}{seq:03d}"
                result_no = f"{RESULT_PREFIX}{seq:03d}"
                inspector = f"UAT-FQC-{index % 3 + 1:02d}"
                qty = float(order["QtyOrded"] or 100)
                rejected = round(qty * (0.01 if index % 4 == 0 else 0), 0)
                accepted = qty - rejected

                if not dry_run:
                    cur.execute(
                        """
                        INSERT INTO qms_fqcbj
                          (id,tenant_id,FBILLNO,FBILLSTATUS,FCREATETIME,FAPPLYUSER,FAPPLYTIME,
                           FBILLTYPE,cpmc,scph,sl,wlbm,jyfzr,yxj,jykssj,jywcsj,
                           FINSPECTSTATUS,sczld,sczldsl,lydjbh)
                        VALUES
                          (%s,%s,%s,'COMPLETED',%s,'UAT_GENERATOR',%s,
                           'FQC',%s,%s,%s,%s,%s,'正常',%s,%s,
                           '检验完成',%s,%s,%s)
                        """,
                        (
                            task_id,
                            tenant_id,
                            task_no,
                            apply_at,
                            apply_at,
                            order["ItemNum"] or order["WorkOrd"],
                            f"UAT-BATCH-{seq:03d}",
                            qty,
                            order["ItemNum"] or order["WorkOrd"],
                            inspector,
                            start_at,
                            finish_at,
                            order["WorkOrd"],
                            qty,
                            order["WorkOrd"],
                        ),
                    )
                    cur.execute(
                        """
                        INSERT INTO qms_qcpp_inspbill
                          (id,tenant_id,FBILLNO,FBILLSTATUS,FCREATETIME,FAUDITDATE,
                           FINSPESTARTDATE,FINSPEENDDATE,FINSPECTORID,FBILLTYPE,
                           FMATERIALCFG,FRINSQTY,pd,jyr,hid,lydjbh,scph,hgsl,bhgsl,sczld,sczldsl)
                        VALUES
                          (%s,%s,%s,'COMPLETED',%s,%s,
                           %s,%s,%s,'FQC',
                           %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                        """,
                        (
                            result_id,
                            tenant_id,
                            result_no,
                            apply_at,
                            finish_at,
                            start_at,
                            finish_at,
                            7000 + index % 3,
                            order["ItemNum"] or order["WorkOrd"],
                            qty,
                            "合格" if rejected == 0 else "让步接收",
                            inspector,
                            task_id,
                            task_no,
                            f"UAT-BATCH-{seq:03d}",
                            accepted,
                            rejected,
                            order["WorkOrd"],
                            qty,
                        ),
                    )
                result["tasks"] += 1
                result["results"] += 1

        if dry_run:
            conn.rollback()
        else:
            conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()
    return result


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--tenant", type=int, default=DEFAULT_TENANT)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()
    print(json.dumps(seed(args.tenant, args.dry_run), ensure_ascii=False, indent=2, default=str))


if __name__ == "__main__":
    main()