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}";
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mdp_std_purchase_order
            (tenant_id, source_system, po_no, po_line, po_type, supplier_code, item_code, item_name, order_qty, received_qty, returned_qty, due_date, need_date, order_date, status, buyer, work_order, source_biz_key, sync_batch_id, sync_time)
            SELECT
                NULLIF(m.tenant_id, 0),
                'AIDOP',
                IFNULL(m.PurOrd, ''),
                CAST(IFNULL(d.Line, 0) AS CHAR),
                IFNULL(m.Potype, ''),
                IFNULL(m.Supp, ''),
                IFNULL(d.ItemNum, ''),
                IFNULL(i.Descr, ''),
                IFNULL(d.QtyOrded, 0),
                IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
                IFNULL(d.QtyReturned, 0),
                d.DueDate,
                d.NeedDate,
                m.OrdDate,
                CASE WHEN IFNULL(LENGTH(m.Status), 0) = 0 THEN 'R' ELSE m.Status END,
                m.Buyer,
                m.WorkOrd,
                CONCAT(IFNULL(m.PurOrd,''), '|', IFNULL(d.Line, 0)),
                @BatchId,
                @Now
            FROM PurOrdMaster m
            JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
            WHERE m.PurOrd = @PurOrd
              AND IFNULL(d.ItemNum, '') <> ''
              AND NULLIF(m.tenant_id, 0) IS NOT NULL
            ON DUPLICATE KEY UPDATE
                po_type=VALUES(po_type),
                supplier_code=VALUES(supplier_code),
                item_code=VALUES(item_code),
                item_name=VALUES(item_name),
                order_qty=VALUES(order_qty),
                received_qty=VALUES(received_qty),
                returned_qty=VALUES(returned_qty),
                due_date=VALUES(due_date),
                need_date=VALUES(need_date),
                order_date=VALUES(order_date),
                status=VALUES(status),
                buyer=VALUES(buyer),
                work_order=VALUES(work_order),
                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));

        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));

        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO dwd_supplier_delivery
            (tenant_id, factory_id, stat_date, po_no, po_line, po_type, supplier_code, supplier_name, item_code, item_name, order_qty, schedule_qty, delivery_qty, receipt_qty, return_qty, remaining_qty, due_date, need_date, delivery_status, source_system, sync_batch_id, calc_time)
            SELECT
                NULLIF(m.tenant_id, 0),
                1,
                @StatDate,
                IFNULL(m.PurOrd, ''),
                CAST(IFNULL(d.Line, 0) AS CHAR),
                IFNULL(m.Potype, ''),
                IFNULL(m.Supp, ''),
                IFNULL(s.SortName, ''),
                IFNULL(d.ItemNum, ''),
                IFNULL(i.Descr, ''),
                IFNULL(d.QtyOrded, 0),
                0,
                0,
                IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
                IFNULL(d.QtyReturned, 0),
                GREATEST(IFNULL(d.QtyOrded, 0) - IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)) - IFNULL(d.QtyReturned, 0), 0),
                d.DueDate,
                d.NeedDate,
                'OPEN',
                'AIDOP',
                @BatchId,
                @Now
            FROM PurOrdMaster m
            JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
            LEFT JOIN SuppMaster s ON m.Domain = s.Domain AND m.Supp = s.Supp
            WHERE m.PurOrd = @PurOrd
              AND IFNULL(d.ItemNum, '') <> ''
              AND NULLIF(m.tenant_id, 0) IS NOT NULL
            ON DUPLICATE KEY UPDATE
                po_type=VALUES(po_type),
                supplier_code=VALUES(supplier_code),
                supplier_name=VALUES(supplier_name),
                item_code=VALUES(item_code),
                item_name=VALUES(item_name),
                order_qty=VALUES(order_qty),
                receipt_qty=VALUES(receipt_qty),
                return_qty=VALUES(return_qty),
                remaining_qty=VALUES(remaining_qty),
                due_date=VALUES(due_date),
                need_date=VALUES(need_date),
                delivery_status=VALUES(delivery_status),
                sync_batch_id=VALUES(sync_batch_id),
                calc_time=VALUES(calc_time),
                update_time=CURRENT_TIMESTAMP
            """,
            new SugarParameter("@PurOrd", purOrd),
            new SugarParameter("@StatDate", now.Date),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@Now", now));
    }

    [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; }
    }
}