AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Supply/ProcessOutsourceOrderService.cs

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 工序外协订单服务
/// </summary>
[ApiDescriptionSettings(Order = 311, Description = "工序外协订单")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class ProcessOutsourceOrderService : IDynamicApiController, ITransient
{
    private const int PwPurOrdSerialWidth = 4;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public ProcessOutsourceOrderService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    private long ResolveTenantOrThrow() => Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);

    [DisplayName("工序外协订单列表")]
    [HttpGet("process-outsource-order/list")]
    public async Task<object> GetList([FromQuery] ProcessOutsourceOrderListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "p.Potype='PW'", "p.IsActive<=1", "IFNULL(p.`Usage`,'')='外协'" };
        var pars = new List<SugarParameter>();
        var tenantId = ResolveTenantOrThrow();
        where.Add("p.tenant_id = @TenantId");
        pars.Add(new SugarParameter("@TenantId", tenantId));
        if (!string.IsNullOrWhiteSpace(input.PurOrd))
        {
            where.Add("p.PurOrd LIKE @PurOrd");
            pars.Add(new SugarParameter("@PurOrd", $"%{input.PurOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            where.Add("p.WorkOrd LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Buyer))
        {
            where.Add("p.Buyer=@Buyer");
            pars.Add(new SugarParameter("@Buyer", input.Buyer.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Supp))
        {
            where.Add("p.Supp=@Supp");
            pars.Add(new SugarParameter("@Supp", input.Supp.Trim()));
        }

        var baseSql = $"""
            SELECT
                p.RecID AS Id,
                p.PurOrd AS PurOrd,
                p.WorkOrd AS WorkOrd,
                p.ERPWorkOrd AS ERPWorkOrd,
                CONCAT(TRIM(IFNULL(p.Supp,'')),' ',TRIM(IFNULL(s.SortName,''))) AS SuppName,
                p.Buyer AS BuyerCode,
                CONCAT(TRIM(IFNULL(p.Buyer,'')),' ',TRIM(IFNULL(e.Name,''))) AS Buyer,
                f.ItemNum AS ItemNum,
                i.Descr AS ItemName,
                CONCAT(TRIM(CAST(IFNULL(r.Op,0) AS CHAR(20))),' ',TRIM(IFNULL(r.Descr,''))) AS Op,
                p.OrdDate AS OrdDate,
                f.QtyOrded AS QtyOrded,
                f.CumQtyBO AS CumQtyBO,
                f.DueDate AS DueDate,
                n.`Date` AS FlDate,
                CASE
                    WHEN IFNULL(LENGTH(p.Status),0)=0 OR p.Buyer IS NULL THEN 'R'
                    ELSE p.Status
                END AS Status,
                p.Supp AS Supp,
                p.ReqBy AS ReqBy,
                p.Department AS Department,
                p.`Usage` AS `Usage`,
                p.Curr AS Curr,
                p.Remark AS Remark,
                (CASE WHEN EXISTS(SELECT 1 FROM PurOrdDetail pd WHERE pd.PurOrd=p.PurOrd AND IFNULL(pd.QtyReceived,0)>0) THEN 1 ELSE 0 END) AS HasReceived
            FROM PurOrdMaster p
            LEFT JOIN SuppMaster s ON p.Supp=s.Supp
            LEFT JOIN DepartmentMaster d ON p.Department=d.Department
            LEFT JOIN EmployeeMaster e ON p.Buyer=e.Employee
            LEFT JOIN PurOrdDetail f ON p.PurOrd=f.PurOrd
            LEFT JOIN RoutingOpDetail r ON f.Op=r.Op AND f.ItemNum=r.RoutingCode
            LEFT JOIN ItemMaster i ON f.ItemNum=i.ItemNum
            LEFT JOIN PurOrdRctMaster o ON o.OrdNbr=p.PurOrd
            LEFT JOIN NbrMaster n ON p.WorkOrd=n.WorkOrd AND n.Type='CA'
            WHERE {string.Join(" AND ", where)}
            """;

        var outerWhere = string.Empty;
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            outerWhere = "WHERE t.Status=@Status";
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseSql}) t {outerWhere}", pars);
        var list = await _db.Ado.SqlQueryAsync<ProcessOutsourceOrderListRow>(
            $"SELECT * FROM ({baseSql}) t {outerWhere} ORDER BY {BuildOrderBy(input.SortField, input.SortOrder)} LIMIT {pageSize} OFFSET {offset}",
            pars);

        return new { total, page, pageSize, list };
    }

    [DisplayName("工序外协订单详情")]
    [HttpGet("process-outsource-order/{id:int}")]
    public async Task<object> GetDetail(int id)
    {
        var tenantWhere = " AND p.tenant_id=@TenantId";
        var masterPars = new[] { new SugarParameter("@Id", id), new SugarParameter("@TenantId", ResolveTenantOrThrow()) };
        var master = (await _db.Ado.SqlQueryAsync<ProcessOutsourceOrderMasterRow>(
            $"""
            SELECT
                p.RecID AS Id,p.PurOrd AS PurOrd,p.OrdDate AS OrdDate,p.Supp AS Supp,p.ReqBy AS ReqBy,p.WorkOrd AS WorkOrd,
                p.Buyer AS Buyer,p.Department AS Department,p.`Usage` AS `Usage`,p.Curr AS Curr,p.Remark AS Remark,
                CASE WHEN IFNULL(LENGTH(p.Status),0)=0 OR p.Buyer IS NULL THEN 'R' ELSE p.Status END AS Status
            FROM PurOrdMaster p
            WHERE p.RecID=@Id{tenantWhere}
            LIMIT 1
            """,
            masterPars)).FirstOrDefault();
        if (master == null) throw Oops.Oh("记录不存在");

        var details = await _db.Ado.SqlQueryAsync<ProcessOutsourceOrderDetailRow>(
            """
            SELECT
                d.RecID AS Id,
                d.Line AS Line,
                d.ItemNum AS ItemNum,
                IFNULL(i.Descr,'') AS ItemName,
                d.Op AS Op,
                d.UM AS UM,
                d.Location AS Location,
                d.QtyOrded AS QtyOrded,
                d.QtyReceived AS QtyReceived,
                d.ReceiptQty AS ReceiptQty,
                d.DueDate AS DueDate,
                d.LotSerial AS LotSerial,
                d.Potype AS Potype,
                d.PurOrd AS PurOrd,
                d.PurOrdRecID AS PurOrdRecID
            FROM PurOrdDetail d
            LEFT JOIN ItemMaster i ON d.ItemNum=i.ItemNum
            WHERE d.PurOrdRecID=@PurOrdRecID
            ORDER BY d.Line,d.RecID
            """,
            new SugarParameter("@PurOrdRecID", id));

        return new { master, details };
    }

    [DisplayName("按工单生成工序外协订单")]
    [HttpPost("process-outsource-order/create-by-workord")]
    public async Task<object> CreateByWorkOrd([FromBody] ProcessOutsourceOrderCreateInput input)
    {
        if (string.IsNullOrWhiteSpace(input.WorkOrd)) throw Oops.Oh("请选择工单");
        var workOrd = input.WorkOrd.Trim();

        // 先筛出“当前工单且未生成采购单”的外协供应商清单（等价于存储过程内 @v_temp_supp）。
        var suppliers = await _db.Ado.SqlQueryAsync<SuppRow>(
            """
            SELECT DISTINCT d.SupplierCode AS Supp
            FROM WorkOrdMaster m
            LEFT JOIN WorkOrdRouting r ON m.WorkOrd=r.WorkOrd
            LEFT JOIN RoutingOpDetail d ON r.Op=d.Op AND r.ItemNum=d.RoutingCode
            LEFT JOIN PurOrdMaster p ON m.WorkOrd=p.WorkOrd AND d.SupplierCode=p.Supp
            WHERE r.ProcessOut=1
              AND m.WorkOrd=@WorkOrd
              AND p.RecID IS NULL
              AND IFNULL(TRIM(d.SupplierCode),'')<>''
            ORDER BY d.SupplierCode
            """,
            new SugarParameter("@WorkOrd", workOrd));
        if (suppliers.Count == 0)
            throw Oops.Oh("当前工单无需生成外协采购订单。");

        var domain = await _db.Ado.GetStringAsync(
            """
            SELECT IFNULL(TRIM(Domain),'')
            FROM WorkOrdMaster
            WHERE WorkOrd=@WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@WorkOrd", workOrd)) ?? string.Empty;

        var created = new List<IdPurOrdRow>();
        try
        {
            _db.Ado.BeginTran();
            var purOrdNumbers = await PurOrdNumberGenerator.NextBatchAsync(
                _db, "PW", DateTime.Today, PwPurOrdSerialWidth, suppliers.Count);

            for (var i = 0; i < suppliers.Count; i++)
            {
                var supp = suppliers[i].Supp!.Trim();
                var purOrd = purOrdNumbers[i].Trim();
                var now = DateTime.Now;

                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO PurOrdMaster
                    (
                        Confirming, CreditTermsInt, Disc, ExchRate, EstVal, ExchRate1, ExchRate2,
                        FixedPrice, FixedRate, Frt, PartialOK, AmtPrepaid, PrintPO, PST, Recurr,
                        `Release`, Revision, Scheduled, ServiceCharge, SpecialCharge, Taxable,
                        Tax1, Tax2, Tax3, TransportDays, IsActive, IsConfirm, Potype, IsChanged,
                        TaxIn, Amt, IsPriceChanged,
                        Buyer, Domain, PurOrd, OrdDate, ReqBy, Status, Supp, CreateUser, CreateTime,
                        Department, `Usage`, FSTID, ERPWorkOrd, WorkOrd, tenant_id
                    )
                    SELECT
                        0, 0, 0, 1, 0, 1, 1,
                        0, 0, 0, 1, 0, 0, 0, 0,
                        0, 0, 0, 0, 0, 1,
                        0, 0, 0, 0, 1, 1, 'PW', 0,
                        1, 0, 0,
                        @Buyer,
                        IFNULL(TRIM(m.Domain),''),
                        @PurOrd,
                        @Now,
                        'PO',
                        '',
                        @Supp,
                        @CreateUser,
                        @Now,
                        m.Department,
                        '外协',
                        1,
                        m.Batch,
                        m.WorkOrd,
                        @TenantId
                    FROM WorkOrdMaster m
                    WHERE m.WorkOrd=@WorkOrd
                    LIMIT 1
                    """,
                    new SugarParameter("@Buyer", _userManager.Account),
                    new SugarParameter("@PurOrd", purOrd),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@Supp", supp),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@TenantId", ResolveTenantOrThrow()));

                var masterId = await _db.Ado.GetIntAsync(
                    "SELECT IFNULL(MAX(RecID),0) FROM PurOrdMaster WHERE PurOrd=@PurOrd",
                    new SugarParameter("@PurOrd", purOrd));
                if (masterId <= 0)
                    throw Oops.Oh("采购订单生成失败。");

                var lines = await _db.Ado.SqlQueryAsync<CreateLineRow>(
                    """
                    SELECT
                        IFNULL(i.PMBOM,'') AS PMBOM,
                        IFNULL(r.Op,0) AS Op,
                        r.ItemNum AS ItemNum,
                        IFNULL(i.UM,'') AS UM,
                        IFNULL(m.QtyOrded,0) AS QtyOrded,
                        IFNULL(i.Rev,'') AS Rev,
                        IFNULL(m.Department,'') AS Department,
                        m.WorkOrd AS WorkOrd,
                        IFNULL(i.Drawing,'') AS Drawing,
                        IFNULL(m.RecID,0) AS WorkOrdID,
                        IFNULL(d.OutsourcedLeadTime,0) AS ProcessOutDay
                    FROM WorkOrdMaster m
                    LEFT JOIN WorkOrdRouting r ON m.WorkOrd=r.WorkOrd
                    LEFT JOIN RoutingOpDetail d ON r.Op=d.Op AND r.ItemNum=d.RoutingCode
                    LEFT JOIN ItemMaster i ON r.ItemNum=i.ItemNum
                    WHERE r.ProcessOut=1
                      AND m.WorkOrd=@WorkOrd
                      AND d.SupplierCode=@Supp
                    ORDER BY r.RecID
                    """,
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@Supp", supp));

                for (var lineIndex = 0; lineIndex < lines.Count; lineIndex++)
                {
                    var row = lines[lineIndex];
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        INSERT INTO PurOrdDetail
                        (
                            QtyBO, RctCost, CreditTermsInt, UpdateCurrentCost, CumReceived1, CumReceived2,
                            CumReceived3, CumReceived4, Disc, FixedPrice, InspectReq, SingleLot, SupplyPer,
                            PurOrd, PST, PackingSlipQty, PayUMConv, PurCost, RctQty, QtyOrded, QtyReceived,
                            QtyReturned, Active, QtyReleased, RctUMConversion, Scheduled, ScheduledChanged,
                            SchedMRPReq, SafetyDays, SafetyHours, StdCost, Taxable, TaxIn, MaxTaxableAmt,
                            TransportHours, UMConversion, VAT, IsActive, IsConfirm, Potype, IsChanged,
                            TaxRate, IsRounding, ReceiptQty, BarCodeQty, IsClosed, QtyReturnedRefund, CumQtyBO,
                            ActiveRlseID1, Domain, DueDate, Line, Location, Op, ItemNum, Status, UM, WorkOrdID,
                            CreateUser, CreateTime, `Usage`, Rev, Department, WorkOrd, Drawing, PurOrdRecID, tenant_id
                        )
                        VALUES
                        (
                            0, 0, 0, 0, 0, 0,
                            0, 0, 0, 0, 0, 0, 0,
                            @PurOrd, 0, 0, 1, 0, 0, @QtyOrded, 0,
                            0, 1, 0, 1, 0, 0,
                            0, 0, 0, 0, 1, 1, 0,
                            0, 1, 0, 1, 1, 'PW', 0,
                            0, 0, 0, 0, 0, 0, 0,
                            @ActiveRlseID1, @Domain, DATE_ADD(@Now, INTERVAL @ProcessOutDay DAY), @Line, '5008', @Op, @ItemNum, '', @UM, @WorkOrdID,
                            @CreateUser, @Now, '外协', @Rev, @Department, @WorkOrd, @Drawing, @PurOrdRecID, @TenantId
                        )
                        """,
                        new SugarParameter("@ActiveRlseID1", row.PMBOM),
                        new SugarParameter("@Domain", domain),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@ProcessOutDay", row.ProcessOutDay),
                        new SugarParameter("@Line", lineIndex + 1),
                        new SugarParameter("@PurOrd", purOrd),
                        new SugarParameter("@Op", row.Op),
                        new SugarParameter("@ItemNum", row.ItemNum),
                        new SugarParameter("@QtyOrded", row.QtyOrded),
                        new SugarParameter("@UM", row.UM),
                        new SugarParameter("@WorkOrdID", row.WorkOrdID),
                        new SugarParameter("@CreateUser", _userManager.Account),
                        new SugarParameter("@Rev", row.Rev),
                        new SugarParameter("@Department", row.Department),
                        new SugarParameter("@WorkOrd", row.WorkOrd),
                        new SugarParameter("@Drawing", row.Drawing),
                        new SugarParameter("@PurOrdRecID", masterId),
                        new SugarParameter("@TenantId", ResolveTenantOrThrow()));
                }

                await UpsertProcessOutsourceMdpAsync(purOrd, now);
                created.Add(new IdPurOrdRow { Id = masterId, PurOrd = purOrd });
            }

            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw Oops.Oh("采购订单生成失败。");
        }

        var latest = created.OrderByDescending(x => x.Id).FirstOrDefault();
        if (latest == null) throw Oops.Oh("采购订单生成失败。");
        return new { id = latest.Id, purOrd = latest.PurOrd, message = "采购订单生成成功。" };
    }

    private async Task UpsertProcessOutsourceMdpAsync(string purOrd, DateTime now)
    {
        var batchId = $"S3_OUTSOURCE_{now:yyyyMMddHHmmss}";
        // S8-RULE01-AUTHORITY-SINGLE-WRITER：已移除对 mdp_std_purchase_order 的直写。
        //
        // 标准层现在是「本轮批次的镜像」：S3 全量转换按 @BatchId 收窄并淘汰不属于本批次的行。
        // 本旁路写入的行带的是 S3_OUTSOURCE_* 批次号，下一轮全量必然把它淘汰掉，
        // 留着它只会让标准层短暂出现一批镜像不认识的行，且与「单一 Authority 写入方」相矛盾。
        //
        // 采购订单行的标准层由 S3 从源表统一派生；本服务只负责写源单据。
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mdp_std_process_outsource_order
            (tenant_id, source_system, work_order, op_code, routing_code, supplier_code, po_no, po_line, order_qty, completed_qty, due_date, status, source_biz_key, sync_batch_id, sync_time)
            SELECT
                NULLIF(m.tenant_id, 0),
                'AIDOP',
                IFNULL(m.WorkOrd, ''),
                CAST(IFNULL(d.Op, 0) AS CHAR),
                IFNULL(d.ItemNum, ''),
                IFNULL(m.Supp, ''),
                IFNULL(m.PurOrd, ''),
                CAST(IFNULL(d.Line, 0) AS CHAR),
                IFNULL(d.QtyOrded, 0),
                IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
                d.DueDate,
                CASE WHEN IFNULL(LENGTH(m.Status), 0) = 0 THEN 'R' ELSE m.Status END,
                CONCAT(IFNULL(m.PurOrd,''), '|', IFNULL(d.Line, 0)),
                @BatchId,
                @Now
            FROM PurOrdMaster m
            JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            WHERE m.PurOrd = @PurOrd
              AND m.Potype = 'PW'
              AND IFNULL(m.WorkOrd, '') <> ''
              AND IFNULL(d.ItemNum, '') <> ''
              AND NULLIF(m.tenant_id, 0) IS NOT NULL
            ON DUPLICATE KEY UPDATE
                supplier_code=VALUES(supplier_code),
                po_no=VALUES(po_no),
                po_line=VALUES(po_line),
                order_qty=VALUES(order_qty),
                completed_qty=VALUES(completed_qty),
                due_date=VALUES(due_date),
                status=VALUES(status),
                sync_batch_id=VALUES(sync_batch_id),
                sync_time=VALUES(sync_time),
                update_time=CURRENT_TIMESTAMP
            """,
            new SugarParameter("@PurOrd", purOrd),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@Now", now));

        // S8-RULE01-AUTHORITY-SINGLE-WRITER：已移除对 dwd_supplier_delivery 的直写。
        //
        // 该表是 Rule01 的 Authority，必须只有一个运行期写入方（S3 的 MDP 转换）。
        // 这里原本直读 PurOrdMaster/PurOrdDetail/ItemMaster/SuppMaster 后直接 upsert DWD，
        // 且把 delivery_status 硬编码为 'OPEN' 并放进 ON DUPLICATE KEY UPDATE ——
        // 于是一次建单调用就能把 MDP 刚判定的 COMPLETED / DELAYED 无条件翻回 OPEN。
        //
        // 更严重的是它能凭空造出一个「最新快照」：Rule01 取 MAX(stat_date) 那一天的全部行，
        // 实测租户 797403760988229 的 2026-05-16 快照只有 2 行、且全部出自本旁路 ——
        // MDP 停摆时，一次业务 API 调用就让 Rule01 在 2 行残缺数据上评估，而快照看起来还很新鲜。
        //
        // 业务侧只负责写源单据；DWD 由 S3 全量转换派生，最长 60 分钟可见延迟。
        // 若将来需要更快可见，用 ModuleRebuildService.EnqueueAsync("S3", ...) 在**事务提交之后**
        // 触发一次正式重建，而不是重新引入第二个写入方。
    }

    [DisplayName("保存工序外协订单")]
    [HttpPost("process-outsource-order/save")]
    public async Task<object> Save([FromBody] ProcessOutsourceOrderSaveInput input)
    {
        if (input.Id <= 0) throw Oops.Oh("缺少主键");
        if (string.IsNullOrWhiteSpace(input.PurOrd)) throw Oops.Oh("采购单号不能为空");
        if (string.IsNullOrWhiteSpace(input.Supp)) throw Oops.Oh("供应商不能为空");
        if (string.IsNullOrWhiteSpace(input.Department)) throw Oops.Oh("部门不能为空");

        var exists = await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM PurOrdMaster WHERE RecID=@Id AND tenant_id=@TenantId",
            new[] { new SugarParameter("@Id", input.Id), new SugarParameter("@TenantId", ResolveTenantOrThrow()) });
        if (exists <= 0) throw Oops.Oh("记录不存在");

        try
        {
            _db.Ado.BeginTran();

            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE PurOrdMaster
                SET OrdDate=@OrdDate,Supp=@Supp,ReqBy=@ReqBy,WorkOrd=@WorkOrd,Buyer=@Buyer,Department=@Department,`Usage`=@Usage,Curr=@Curr,Remark=@Remark,UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
                WHERE RecID=@Id
                """,
                new SugarParameter("@Id", input.Id),
                new SugarParameter("@OrdDate", ParseDate(input.OrdDate)),
                new SugarParameter("@Supp", input.Supp!.Trim()),
                new SugarParameter("@ReqBy", string.IsNullOrWhiteSpace(input.ReqBy) ? "PO" : input.ReqBy.Trim()),
                new SugarParameter("@WorkOrd", input.WorkOrd?.Trim()),
                new SugarParameter("@Buyer", string.IsNullOrWhiteSpace(input.Buyer) ? "110" : input.Buyer.Trim()),
                new SugarParameter("@Department", input.Department!.Trim()),
                new SugarParameter("@Usage", string.IsNullOrWhiteSpace(input.Usage) ? "外协" : input.Usage.Trim()),
                new SugarParameter("@Curr", input.Curr?.Trim()),
                new SugarParameter("@Remark", input.Remark?.Trim()),
                new SugarParameter("@UpdateUser", _userManager.Account),
                new SugarParameter("@UpdateTime", DateTime.Now)
            );

            await SaveDetailsAsync(input.Id, input.PurOrd!.Trim(), input.WorkOrd, input.Details);

            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }

        return new { id = input.Id, message = "保存成功" };
    }

    /// <summary>
    /// 明细三路合并：
    /// ① DB有且入参有（按 Id 匹配）→ 更新
    /// ② DB无但入参有              → 新增
    /// ③ DB有但入参无              → 删除
    /// </summary>
    private async Task SaveDetailsAsync(int masterId, string purOrd, string? workOrd, List<ProcessOutsourceOrderDetailInput> details)
    {
        var dbDetails = await _db.Ado.SqlQueryAsync<DbDetailRow>(
            "SELECT RecID AS Id FROM PurOrdDetail WHERE PurOrdRecID=@PurOrdRecID",
            new SugarParameter("@PurOrdRecID", masterId));
        var dbById = dbDetails.ToDictionary(x => x.Id);
        var inputIds = new HashSet<int>(details.Where(x => x.Id.HasValue && x.Id.Value > 0).Select(x => x.Id!.Value));

        var maxLine = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(Line),0) FROM PurOrdDetail WHERE PurOrdRecID=@PurOrdRecID", new SugarParameter("@PurOrdRecID", masterId));
        var lineSeed = maxLine;

        for (var i = 0; i < details.Count; i++)
        {
            var item = details[i];
            if (string.IsNullOrWhiteSpace(item.ItemNum)) continue;

            var line = item.Line ?? (i + 1);
            if (line <= 0)
            {
                lineSeed++;
                line = lineSeed;
            }

            var itemInfo = (await _db.Ado.SqlQueryAsync<ItemLookupRow>(
                """
                SELECT ItemNum,Descr,UM,Location
                FROM ItemMaster
                WHERE ItemNum=@ItemNum
                LIMIT 1
                """,
                new SugarParameter("@ItemNum", item.ItemNum.Trim()))).FirstOrDefault();

            if (item.Id.HasValue && item.Id.Value > 0 && dbById.ContainsKey(item.Id.Value))
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE PurOrdDetail
                    SET Line=@Line,ItemNum=@ItemNum,Descr=@Descr,Op=@Op,UM=@UM,Location=@Location,QtyOrded=@QtyOrded,QtyReceived=@QtyReceived,ReceiptQty=@ReceiptQty,DueDate=@DueDate,LotSerial=@LotSerial,Potype='PW',PurOrd=@PurOrd,PurOrdRecID=@PurOrdRecID,WorkOrd=@WorkOrd,UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
                    WHERE RecID=@Id
                    """,
                    new SugarParameter("@Id", item.Id.Value),
                    new SugarParameter("@Line", line),
                    new SugarParameter("@ItemNum", item.ItemNum.Trim()),
                    new SugarParameter("@Descr", itemInfo?.Descr),
                    new SugarParameter("@Op", item.Op ?? 0),
                    new SugarParameter("@UM", string.IsNullOrWhiteSpace(item.UM) ? itemInfo?.UM : item.UM!.Trim()),
                    new SugarParameter("@Location", string.IsNullOrWhiteSpace(item.Location) ? itemInfo?.Location : item.Location!.Trim()),
                    new SugarParameter("@QtyOrded", item.QtyOrded ?? 0),
                    new SugarParameter("@QtyReceived", item.QtyReceived ?? 0),
                    new SugarParameter("@ReceiptQty", item.ReceiptQty ?? 0),
                    new SugarParameter("@DueDate", ParseDate(item.DueDate)),
                    new SugarParameter("@LotSerial", item.LotSerial?.Trim()),
                    new SugarParameter("@PurOrd", purOrd),
                    new SugarParameter("@PurOrdRecID", masterId),
                    new SugarParameter("@WorkOrd", workOrd?.Trim()),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", DateTime.Now)
                );
            }
            else
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO PurOrdDetail
                    (PurOrd,Line,ItemNum,Descr,Op,UM,Location,QtyOrded,QtyReceived,ReceiptQty,DueDate,LotSerial,Potype,PurOrdRecID,WorkOrd,Status,CreateUser,CreateTime,UpdateUser,UpdateTime,tenant_id)
                    VALUES
                    (@PurOrd,@Line,@ItemNum,@Descr,@Op,@UM,@Location,@QtyOrded,@QtyReceived,@ReceiptQty,@DueDate,@LotSerial,'PW',@PurOrdRecID,@WorkOrd,'R',@CreateUser,@CreateTime,@UpdateUser,@UpdateTime,@TenantId)
                    """,
                    new SugarParameter("@PurOrd", purOrd),
                    new SugarParameter("@Line", line),
                    new SugarParameter("@ItemNum", item.ItemNum.Trim()),
                    new SugarParameter("@Descr", itemInfo?.Descr),
                    new SugarParameter("@Op", item.Op ?? 0),
                    new SugarParameter("@UM", string.IsNullOrWhiteSpace(item.UM) ? itemInfo?.UM : item.UM!.Trim()),
                    new SugarParameter("@Location", string.IsNullOrWhiteSpace(item.Location) ? itemInfo?.Location : item.Location!.Trim()),
                    new SugarParameter("@QtyOrded", item.QtyOrded ?? 0),
                    new SugarParameter("@QtyReceived", item.QtyReceived ?? 0),
                    new SugarParameter("@ReceiptQty", item.ReceiptQty ?? 0),
                    new SugarParameter("@DueDate", ParseDate(item.DueDate)),
                    new SugarParameter("@LotSerial", item.LotSerial?.Trim()),
                    new SugarParameter("@PurOrdRecID", masterId),
                    new SugarParameter("@WorkOrd", workOrd?.Trim()),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@CreateTime", DateTime.Now),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", DateTime.Now),
                    new SugarParameter("@TenantId", ResolveTenantOrThrow())
                );
            }
        }

        foreach (var toDelete in dbDetails.Where(x => !inputIds.Contains(x.Id)))
        {
            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdDetail WHERE RecID=@Id", new SugarParameter("@Id", toDelete.Id));
        }
    }

    [DisplayName("删除工序外协订单")]
    [HttpPost("process-outsource-order/delete/{id:int}")]
    public async Task<object> Delete(int id, [FromQuery] string purOrd)
    {
        if (string.IsNullOrWhiteSpace(purOrd)) throw Oops.Oh("缺少采购单号");
        var ordNo = purOrd.Trim();

        var hasReceipt = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM PurOrdRctDetail WHERE OrdNbr=@PurOrd AND IFNULL(QtyReceived,0)>0 AND tenant_id=@TenantId",
            new[] { new SugarParameter("@PurOrd", ordNo), new SugarParameter("@TenantId", ResolveTenantOrThrow()) });
        if (hasReceipt > 0) throw Oops.Oh("存在收货数量，不允许删除");

        var hasShip = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM scm_shdzb WHERE po_bill=@PurOrd AND tenant_id=@TenantId",
            new[] { new SugarParameter("@PurOrd", ordNo), new SugarParameter("@TenantId", ResolveTenantOrThrow()) });
        if (hasShip > 0) throw Oops.Oh("存在发货单，不允许删除");

        try
        {
            _db.Ado.BeginTran();
            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdDetail WHERE PurOrd=@PurOrd", new SugarParameter("@PurOrd", ordNo));
            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdMaster WHERE RecID=@Id AND PurOrd=@PurOrd", new SugarParameter("@Id", id), new SugarParameter("@PurOrd", ordNo));
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }

        return new { message = "删除成功" };
    }

    [DisplayName("采购组下拉")]
    [HttpGet("process-outsource-order/options/buyers")]
    public async Task<object> GetBuyerOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT Employee AS Value, CONCAT(TRIM(Employee),' ',TRIM(IFNULL(Name,''))) AS Label
            FROM EmployeeMaster
            ORDER BY Employee
            """);
        return new { list };
    }

    [DisplayName("供应商下拉")]
    [HttpGet("process-outsource-order/options/suppliers")]
    public async Task<object> GetSupplierOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT Supp AS Value, CONCAT(TRIM(Supp),' ',TRIM(IFNULL(SortName,''))) AS Label
            FROM SuppMaster
            ORDER BY Supp
            """);
        return new { list };
    }

    [DisplayName("部门下拉")]
    [HttpGet("process-outsource-order/options/departments")]
    public async Task<object> GetDepartmentOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT Department AS Value, CONCAT(TRIM(Department),' ',TRIM(IFNULL(Descr,''))) AS Label
            FROM DepartmentMaster
            ORDER BY Department
            """);
        return new { list };
    }

    [DisplayName("币别下拉")]
    [HttpGet("process-outsource-order/options/currencies")]
    public async Task<object> GetCurrencyOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT Val AS Value, CONCAT(TRIM(Val),' ',TRIM(IFNULL(Comments,''))) AS Label
            FROM GeneralizedCodeMaster
            WHERE FldName='Curr'
            ORDER BY Val
            """);
        return new { list };
    }

    [DisplayName("库位下拉")]
    [HttpGet("process-outsource-order/options/locations")]
    public async Task<object> GetLocationOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT Location AS Value, CONCAT(TRIM(Location),' ',TRIM(IFNULL(Descr,''))) AS Label
            FROM LocationMaster
            WHERE IFNULL(Typed,'')<>'Supp'
            ORDER BY Location
            """);
        return new { list };
    }

    [DisplayName("工序下拉")]
    [HttpGet("process-outsource-order/options/ops")]
    public async Task<object> GetOpOptions([FromQuery] int purOrdRecId)
    {
        if (purOrdRecId <= 0) return new { list = new List<OptionRow>() };
        var list = await _db.Ado.SqlQueryAsync<OptionRow>(
            """
            SELECT DISTINCT CAST(Op AS CHAR(50)) AS Value, CONCAT(TRIM(CAST(Op AS CHAR(50))),' ',TRIM(IFNULL(Descr,''))) AS Label
            FROM RoutingOpDetail
            WHERE UDeci5!=0
              AND RoutingCode IN (SELECT ItemNum FROM PurOrdDetail WHERE PurOrdRecID=@PurOrdRecID)
            ORDER BY Value
            """,
            new SugarParameter("@PurOrdRecID", purOrdRecId));
        return new { list };
    }

    private static DateTime? ParseDate(string? v) => DateTime.TryParse(v, out var d) ? d : null;

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "purord" => $"t.PurOrd {dir}",
            "workord" => $"t.WorkOrd {dir}",
            "suppname" => $"t.SuppName {dir}",
            "buyer" => $"t.Buyer {dir}",
            "itemnum" => $"t.ItemNum {dir}",
            "itemname" => $"t.ItemName {dir}",
            "op" => $"t.Op {dir}",
            "orddate" => $"t.OrdDate {dir}",
            "qtyorded" => $"t.QtyOrded {dir}",
            "cumqtybo" => $"t.CumQtyBO {dir}",
            "duedate" => $"t.DueDate {dir}",
            "fldate" => $"t.FlDate {dir}",
            "status" => $"t.Status {dir}",
            _ => "t.Id DESC"
        };
    }

    private sealed class ProcessOutsourceOrderListRow
    {
        public int Id { get; set; }
        public string? PurOrd { get; set; }
        public string? WorkOrd { get; set; }
        public string? ERPWorkOrd { get; set; }
        public string? SuppName { get; set; }
        public string? Buyer { get; set; }
        public string? BuyerCode { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public string? Op { get; set; }
        public DateTime? OrdDate { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? CumQtyBO { get; set; }
        public DateTime? DueDate { get; set; }
        public DateTime? FlDate { get; set; }
        public string? Status { get; set; }
        public string? Supp { get; set; }
        public string? ReqBy { get; set; }
        public string? Department { get; set; }
        public string? Usage { get; set; }
        public string? Curr { get; set; }
        public string? Remark { get; set; }
        public int HasReceived { get; set; }
    }

    private sealed class ProcessOutsourceOrderMasterRow
    {
        public int Id { get; set; }
        public string? PurOrd { get; set; }
        public DateTime? OrdDate { get; set; }
        public string? Supp { get; set; }
        public string? ReqBy { get; set; }
        public string? WorkOrd { get; set; }
        public string? Buyer { get; set; }
        public string? Department { get; set; }
        public string? Usage { get; set; }
        public string? Curr { get; set; }
        public string? Remark { get; set; }
        public string? Status { get; set; }
    }

    private sealed class ProcessOutsourceOrderDetailRow
    {
        public int Id { get; set; }
        public int? Line { get; set; }
        public string? ItemNum { get; set; }
        public string? ItemName { get; set; }
        public int? Op { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? QtyReceived { get; set; }
        public decimal? ReceiptQty { get; set; }
        public DateTime? DueDate { get; set; }
        public string? LotSerial { get; set; }
        public string? Potype { get; set; }
        public string? PurOrd { get; set; }
        public int? PurOrdRecID { get; set; }
    }

    private sealed class OptionRow
    {
        public string? Value { get; set; }
        public string? Label { get; set; }
    }

    private sealed class IdPurOrdRow
    {
        public int Id { get; set; }
        public string? PurOrd { get; set; }
    }

    private sealed class SuppRow
    {
        public string? Supp { get; set; }
    }

    private sealed class CreateLineRow
    {
        public string? PMBOM { get; set; }
        public int Op { get; set; }
        public string? ItemNum { get; set; }
        public string? UM { get; set; }
        public decimal QtyOrded { get; set; }
        public string? Rev { get; set; }
        public string? Department { get; set; }
        public string? WorkOrd { get; set; }
        public string? Drawing { get; set; }
        public long WorkOrdID { get; set; }
        public int ProcessOutDay { get; set; }
    }

    private sealed class DbDetailRow
    {
        public int Id { get; set; }
    }

    private sealed class ItemLookupRow
    {
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
    }
}