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

namespace Admin.NET.Plugin.AiDOP.Supply;

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

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    [DisplayName("委外加工订单列表")]
    [HttpGet("outsource-order/list")]
    public async Task<object> GetOrderList([FromQuery] OutsourceOrderListInput 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>();
        if (_userManager.TenantId > 0)
        {
            where.Add("p.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PurOrd))
        {
            where.Add("p.PurOrd LIKE @PurOrd");
            pars.Add(new SugarParameter("@PurOrd", $"%{input.PurOrd.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 fromSql = $"""
            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
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<OutsourceOrderListRow>(
            $"""
            SELECT
                p.RecID AS Id,
                p.PurOrd AS PurOrd,
                CONCAT(TRIM(IFNULL(p.Supp,'')),' ',TRIM(IFNULL(s.SortName,''))) AS SuppName,
                p.Potype AS Potype,
                p.ReqBy AS ReqBy,
                CONCAT(TRIM(IFNULL(p.Buyer,'')),' ',TRIM(IFNULL(e.Name,''))) AS Buyer,
                CONCAT(TRIM(IFNULL(p.Department,'')),' ',TRIM(IFNULL(d.Descr,''))) AS DepartmentDescr,
                p.OrdDate AS OrdDate,
                p.DueDate AS DueDate,
                p.Curr AS Curr,
                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.Buyer AS BuyerCode,
                p.Department AS Department,
                p.`Usage` AS `Usage`,
                p.Remark AS Remark
            {fromSql}
            ORDER BY {BuildOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    [DisplayName("委外加工订单详情")]
    [HttpGet("outsource-order/{id:int}")]
    public async Task<object> GetOrderDetail(int id)
    {
        var tenantWhere = _userManager.TenantId > 0 ? " AND p.tenant_id=@TenantId" : string.Empty;
        var detailPars = _userManager.TenantId > 0
            ? new[] { new SugarParameter("@Id", id), new SugarParameter("@TenantId", _userManager.TenantId) }
            : new[] { new SugarParameter("@Id", id) };
        var row = (await _db.Ado.SqlQueryAsync<OutsourceOrderListRow>(
            $"""
            SELECT
                p.RecID AS Id,
                p.PurOrd AS PurOrd,
                CONCAT(TRIM(IFNULL(p.Supp,'')),' ',TRIM(IFNULL(s.SortName,''))) AS SuppName,
                p.Potype AS Potype,
                p.ReqBy AS ReqBy,
                CONCAT(TRIM(IFNULL(p.Buyer,'')),' ',TRIM(IFNULL(e.Name,''))) AS Buyer,
                CONCAT(TRIM(IFNULL(p.Department,'')),' ',TRIM(IFNULL(d.Descr,''))) AS DepartmentDescr,
                p.OrdDate AS OrdDate,
                p.DueDate AS DueDate,
                p.Curr AS Curr,
                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.Buyer AS BuyerCode,
                p.Department AS Department,
                p.`Usage` AS `Usage`,
                p.Remark AS Remark
            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
            WHERE p.RecID=@Id{tenantWhere}
            LIMIT 1
            """,
            detailPars)).FirstOrDefault();
        return row ?? throw Oops.Oh("记录不存在");
    }

    [DisplayName("保存委外加工订单")]
    [HttpPost("outsource-order/save")]
    public async Task<object> SaveOrder([FromBody] OutsourceOrderSaveInput input)
    {
        var now = DateTime.Now;
        var ordDate = ParseDate(input.OrdDate) ?? now.Date;
        var buyer = string.IsNullOrWhiteSpace(input.Buyer) ? "110" : input.Buyer.Trim();
        var usage = string.IsNullOrWhiteSpace(input.Usage) ? "委外加工" : input.Usage.Trim();
        var reqBy = string.IsNullOrWhiteSpace(input.ReqBy) ? "DO" : input.ReqBy.Trim();

        if (string.IsNullOrWhiteSpace(input.Supp)) throw Oops.Oh("请选择供应商");
        if (string.IsNullOrWhiteSpace(input.Department)) throw Oops.Oh("请选择部门");

        if (input.Id is null or <= 0)
        {
            try
            {
                _db.Ado.BeginTran();
                var purOrd = await PurOrdNumberGenerator.NextAsync(_db, "PW", DateTime.Today, PwPurOrdSerialWidth);
                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,
                        PurOrd, OrdDate, Supp, ReqBy, Buyer, Department, `Usage`, Remark, Status,
                        Domain, FSTID, CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                    )
                    VALUES
                    (
                        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,
                        @PurOrd, @OrdDate, @Supp, @ReqBy, @Buyer, @Department, @Usage, @Remark, 'R',
                        @Domain, @FSTID, @CreateUser, @CreateTime, @UpdateUser, @UpdateTime, @TenantId
                    )
                    """,
                    new SugarParameter("@PurOrd", purOrd),
                    new SugarParameter("@OrdDate", ordDate),
                    new SugarParameter("@Supp", input.Supp.Trim()),
                    new SugarParameter("@ReqBy", reqBy),
                    new SugarParameter("@Buyer", buyer),
                    new SugarParameter("@Department", input.Department.Trim()),
                    new SugarParameter("@Usage", usage),
                    new SugarParameter("@Remark", input.Remark?.Trim()),
                    new SugarParameter("@Domain", "100"),
                    new SugarParameter("@FSTID", "1"),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@CreateTime", now),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", now),
                    new SugarParameter("@TenantId", _userManager.TenantId <= 0 ? null : _userManager.TenantId)
                );
                var id = await _db.Ado.GetIntAsync(
                    "SELECT IFNULL(MAX(RecID),0) FROM PurOrdMaster WHERE PurOrd=@PurOrd",
                    new SugarParameter("@PurOrd", purOrd));
                _db.Ado.CommitTran();
                return new { id, purOrd, message = "新增成功" };
            }
            catch
            {
                _db.Ado.RollbackTran();
                throw;
            }
        }

        var exists = await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM PurOrdMaster WHERE RecID=@Id" + (_userManager.TenantId > 0 ? " AND tenant_id=@TenantId" : ""),
            _userManager.TenantId > 0
                ? new[] { new SugarParameter("@Id", input.Id.Value), new SugarParameter("@TenantId", _userManager.TenantId) }
                : new[] { new SugarParameter("@Id", input.Id.Value) });
        if (exists <= 0) throw Oops.Oh("记录不存在");
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE PurOrdMaster
            SET OrdDate=@OrdDate,Supp=@Supp,ReqBy=@ReqBy,Buyer=@Buyer,Department=@Department,`Usage`=@Usage,Remark=@Remark,UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
            WHERE RecID=@Id
            """,
            new SugarParameter("@Id", input.Id.Value),
            new SugarParameter("@OrdDate", ordDate),
            new SugarParameter("@Supp", input.Supp.Trim()),
            new SugarParameter("@ReqBy", reqBy),
            new SugarParameter("@Buyer", buyer),
            new SugarParameter("@Department", input.Department.Trim()),
            new SugarParameter("@Usage", usage),
            new SugarParameter("@Remark", input.Remark?.Trim()),
            new SugarParameter("@UpdateUser", _userManager.Account),
            new SugarParameter("@UpdateTime", now)
        );
        return new { id = input.Id, message = "编辑成功" };
    }

    [DisplayName("删除委外加工订单")]
    [HttpPost("outsource-order/delete/{id:int}")]
    public async Task<object> DeleteOrder(int id, [FromQuery] string purOrd)
    {
        if (string.IsNullOrWhiteSpace(purOrd)) throw Oops.Oh("缺少采购订单号");
        var hasReceipt = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM PurOrdRctDetail WHERE OrdNbr=@PurOrd AND IFNULL(QtyReceived,0)>0" + (_userManager.TenantId > 0 ? " AND tenant_id=@TenantId" : ""),
            _userManager.TenantId > 0
                ? new[] { new SugarParameter("@PurOrd", purOrd.Trim()), new SugarParameter("@TenantId", _userManager.TenantId) }
                : new[] { new SugarParameter("@PurOrd", purOrd.Trim()) });
        if (hasReceipt > 0) throw Oops.Oh("存在收货数量，不允许删除");

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

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

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

    [DisplayName("委外采购明细列表")]
    [HttpGet("outsource-order/detail/list")]
    public async Task<object> GetDetailList([FromQuery] OutsourceOrderDetailListInput 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> { "d.Potype='PW'" };
        var pars = new List<SugarParameter>();
        if (_userManager.TenantId > 0)
        {
            where.Add("d.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PurOrd))
        {
            where.Add("d.PurOrd LIKE @PurOrd");
            pars.Add(new SugarParameter("@PurOrd", $"%{input.PurOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("d.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("d.Location=@Location");
            pars.Add(new SugarParameter("@Location", input.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            where.Add("d.WorkOrd LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            where.Add("d.LotSerial LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }

        var fromSql = $"""
            FROM PurOrdDetail d
            LEFT JOIN ItemMaster i ON d.ItemNum=i.ItemNum
            WHERE {string.Join(" AND ", where)}
            """;
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<OutsourceOrderDetailRow>(
            $"""
            SELECT
                d.PurOrd AS PurOrd,d.ItemNum AS ItemNum,d.QtyOrded AS QtyOrded,d.QtyReceived AS QtyReceived,d.QtyReleased AS QtyReleased,
                d.DueDate AS DueDate,d.Line AS Line,d.Location AS Location,d.UM AS UM,d.RecID AS Id,d.Rev AS Rev,d.Drawing AS Drawing,
                d.WorkOrd AS WorkOrd,d.LotSerial AS LotSerial,d.Status AS Status,IFNULL(i.Descr,'') AS Descr,d.PurOrdRecID AS PurOrdRecID
            {fromSql}
            ORDER BY {BuildDetailOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);
        return new { total, page, pageSize, list };
    }

    [DisplayName("委外采购明细详情")]
    [HttpGet("outsource-order/detail/{id:int}")]
    public async Task<object> GetDetail(int id)
    {
        var tenantWhere = _userManager.TenantId > 0 ? " AND d.tenant_id=@TenantId" : string.Empty;
        var detailPars = _userManager.TenantId > 0
            ? new[] { new SugarParameter("@Id", id), new SugarParameter("@TenantId", _userManager.TenantId) }
            : new[] { new SugarParameter("@Id", id) };
        var detail = (await _db.Ado.SqlQueryAsync<OutsourceOrderDetailRow>(
            $"""
            SELECT
                d.PurOrd AS PurOrd,d.ItemNum AS ItemNum,d.QtyOrded AS QtyOrded,d.QtyReceived AS QtyReceived,d.QtyReleased AS QtyReleased,
                d.DueDate AS DueDate,d.Line AS Line,d.Location AS Location,d.UM AS UM,d.RecID AS Id,d.Rev AS Rev,d.Drawing AS Drawing,
                d.WorkOrd AS WorkOrd,d.LotSerial AS LotSerial,d.Status AS Status,IFNULL(i.Descr,'') AS Descr,d.PurOrdRecID AS PurOrdRecID
            FROM PurOrdDetail d
            LEFT JOIN ItemMaster i ON d.ItemNum=i.ItemNum
            WHERE d.RecID=@Id{tenantWhere}
            LIMIT 1
            """,
            detailPars)).FirstOrDefault();
        if (detail == null) throw Oops.Oh("记录不存在");

        var batches = await _db.Ado.SqlQueryAsync<OutsourceOrderBatchRow>(
            """
            SELECT
                b.Batch AS Batch,b.ItemNum AS ItemNum,b.SuppItem AS SuppItem,b.UM AS UM,b.Location AS Location,b.QtyOrded AS QtyOrded,
                b.QtyBO AS QtyBO,b.QtyReleased AS QtyReleased,b.QtyReceived AS QtyReceived,b.QtyReturned AS QtyReturned,b.LotSerial AS LotSerial
            FROM PurOrdDetailBatch b
            WHERE b.PurOrdDetailRecID=@RecId
            ORDER BY b.Batch
            """,
            new SugarParameter("@RecId", detail.Id));

        return new { detail, batches };
    }

    [DisplayName("保存委外采购明细")]
    [HttpPost("outsource-order/detail/save")]
    public async Task<object> SaveDetail([FromBody] OutsourceOrderDetailSaveInput input)
    {
        if (string.IsNullOrWhiteSpace(input.PurOrd)) throw Oops.Oh("采购订单号不能为空");
        if (input.PurOrdRecID <= 0) throw Oops.Oh("采购订单主键值不能为空");
        if (string.IsNullOrWhiteSpace(input.ItemNum)) throw Oops.Oh("请选择物料");
        if (!input.QtyOrded.HasValue || input.QtyOrded.Value <= 0) throw Oops.Oh("订单数量必须大于0");

        var item = (await _db.Ado.SqlQueryAsync<ItemLookupRow>(
            """
            SELECT ItemNum,Descr,UM,Location,Rev,Drawing
            FROM ItemMaster
            WHERE ItemNum=@ItemNum
            LIMIT 1
            """,
            new SugarParameter("@ItemNum", input.ItemNum.Trim()))).FirstOrDefault();
        if (item == null) throw Oops.Oh("物料不存在");

        try
        {
            _db.Ado.BeginTran();
            int detailId;
            int line;
            if (input.Id is null or <= 0)
            {
                line = input.Line ?? await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(Line),0)+1 FROM PurOrdDetail WHERE PurOrd=@PurOrd", new SugarParameter("@PurOrd", input.PurOrd.Trim()));
                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,
                        Line, ItemNum, Descr, UM, Rev, Drawing, Location, DueDate, LotSerial, PurOrdRecID, Status,
                        CreateUser, CreateTime, UpdateUser, UpdateTime, 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,
                        @Line, @ItemNum, @Descr, @UM, @Rev, @Drawing, @Location, @DueDate, @LotSerial, @PurOrdRecID, 'R',
                        @CreateUser, @CreateTime, @UpdateUser, @UpdateTime, @TenantId
                    )
                    """,
                    new SugarParameter("@PurOrd", input.PurOrd.Trim()),
                    new SugarParameter("@Line", line),
                    new SugarParameter("@ItemNum", item.ItemNum),
                    new SugarParameter("@Descr", item.Descr),
                    new SugarParameter("@UM", item.UM),
                    new SugarParameter("@Rev", item.Rev),
                    new SugarParameter("@Drawing", item.Drawing),
                    new SugarParameter("@Location", item.Location),
                    new SugarParameter("@QtyOrded", input.QtyOrded.Value),
                    new SugarParameter("@DueDate", ParseDate(input.DueDate)),
                    new SugarParameter("@LotSerial", string.Empty),
                    new SugarParameter("@PurOrdRecID", input.PurOrdRecID),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@CreateTime", DateTime.Now),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", DateTime.Now),
                    new SugarParameter("@TenantId", _userManager.TenantId <= 0 ? null : _userManager.TenantId)
                );
                detailId = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID),0) FROM PurOrdDetail WHERE PurOrd=@PurOrd AND Line=@Line", new SugarParameter("@PurOrd", input.PurOrd.Trim()), new SugarParameter("@Line", line));
            }
            else
            {
                detailId = input.Id.Value;
                line = input.Line ?? await _db.Ado.GetIntAsync("SELECT IFNULL(Line,0) FROM PurOrdDetail WHERE RecID=@Id", new SugarParameter("@Id", detailId));
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE PurOrdDetail
                    SET ItemNum=@ItemNum,Descr=@Descr,UM=@UM,Rev=@Rev,Drawing=@Drawing,Location=@Location,QtyOrded=@QtyOrded,DueDate=@DueDate,UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
                    WHERE RecID=@Id
                    """,
                    new SugarParameter("@Id", detailId),
                    new SugarParameter("@ItemNum", item.ItemNum),
                    new SugarParameter("@Descr", item.Descr),
                    new SugarParameter("@UM", item.UM),
                    new SugarParameter("@Rev", item.Rev),
                    new SugarParameter("@Drawing", item.Drawing),
                    new SugarParameter("@Location", item.Location),
                    new SugarParameter("@QtyOrded", input.QtyOrded.Value),
                    new SugarParameter("@DueDate", ParseDate(input.DueDate)),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", DateTime.Now)
                );
            }

            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdDetailBatch WHERE PurOrdDetailRecID=@RecID", new SugarParameter("@RecID", detailId));

            var batchNo = 1;
            foreach (var b in input.Batches)
            {
                if (string.IsNullOrWhiteSpace(b.SuppItem) && string.IsNullOrWhiteSpace(b.ItemNum))
                    continue;
                var batchItem = (await _db.Ado.SqlQueryAsync<ItemLookupRow>(
                    """
                    SELECT ItemNum,Descr,UM,Location,Rev,Drawing
                    FROM ItemMaster
                    WHERE ItemNum=@ItemNum
                    LIMIT 1
                    """,
                    new SugarParameter("@ItemNum", (b.SuppItem ?? b.ItemNum)!.Trim()))).FirstOrDefault();
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO PurOrdDetailBatch
                    (
                        Domain, PurOrd, Potype, Line, Batch, ItemNum, SuppItem, UM, Location,
                        QtyOrded, QtyBO, QtyReleased, QtyReceived, QtyReturned, LotSerial, PurOrdDetailRecID,
                        CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                    )
                    VALUES
                    (
                        '', @PurOrd, 'PW', @Line, @Batch, @ItemNum, @SuppItem, @UM, @Location,
                        @QtyOrded, @QtyBO, @QtyReleased, @QtyReceived, @QtyReturned, @LotSerial, @PurOrdDetailRecID,
                        @CreateUser, @CreateTime, @UpdateUser, @UpdateTime, @TenantId
                    )
                    """,
                    new SugarParameter("@PurOrd", input.PurOrd.Trim()),
                    new SugarParameter("@Line", line),
                    new SugarParameter("@Batch", b.Batch ?? batchNo),
                    new SugarParameter("@ItemNum", batchItem?.ItemNum ?? b.ItemNum?.Trim()),
                    new SugarParameter("@SuppItem", b.SuppItem?.Trim()),
                    new SugarParameter("@UM", b.UM ?? batchItem?.UM),
                    new SugarParameter("@Location", b.Location ?? batchItem?.Location),
                    new SugarParameter("@QtyOrded", b.QtyOrded ?? 0),
                    new SugarParameter("@QtyBO", b.QtyBO.GetValueOrDefault(0)),
                    new SugarParameter("@QtyReleased", b.QtyReleased ?? 0),
                    new SugarParameter("@QtyReceived", b.QtyReceived ?? 0),
                    new SugarParameter("@QtyReturned", b.QtyReturned ?? 0),
                    new SugarParameter("@LotSerial", b.LotSerial?.Trim()),
                    new SugarParameter("@PurOrdDetailRecID", detailId),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@CreateTime", DateTime.Now),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", DateTime.Now),
                    new SugarParameter("@TenantId", _userManager.TenantId <= 0 ? null : _userManager.TenantId)
                );
                batchNo++;
            }

            _db.Ado.CommitTran();
            return new { id = detailId, message = "保存成功" };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    [DisplayName("删除委外采购明细")]
    [HttpPost("outsource-order/detail/delete/{id:int}")]
    public async Task<object> DeleteDetail(int id)
    {
        var status = await _db.Ado.GetStringAsync("SELECT Status FROM PurOrdDetail WHERE RecID=@Id" + (_userManager.TenantId > 0 ? " AND tenant_id=@TenantId" : "") + " LIMIT 1",
            _userManager.TenantId > 0
                ? new[] { new SugarParameter("@Id", id), new SugarParameter("@TenantId", _userManager.TenantId) }
                : new[] { new SugarParameter("@Id", id) });
        if (string.Equals(status, "C", StringComparison.OrdinalIgnoreCase))
            throw Oops.Oh("关闭状态不允许删除");

        try
        {
            _db.Ado.BeginTran();
            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdDetailBatch WHERE PurOrdDetailRecID=@Id", new SugarParameter("@Id", id));
            await _db.Ado.ExecuteCommandAsync("DELETE FROM PurOrdDetail WHERE RecID=@Id", new SugarParameter("@Id", id));
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
        return new { message = "删除成功" };
    }

    [DisplayName("委外订单新增明细初始化")]
    [HttpGet("outsource-order/detail/init")]
    public async Task<object> GetDetailInit([FromQuery] string purOrd)
    {
        if (string.IsNullOrWhiteSpace(purOrd)) throw Oops.Oh("采购订单号不能为空");
        var tenantWhere = _userManager.TenantId > 0 ? " AND p.tenant_id=@TenantId" : string.Empty;
        var initPars = _userManager.TenantId > 0
            ? new[] { new SugarParameter("@PurOrd", purOrd.Trim()), new SugarParameter("@TenantId", _userManager.TenantId) }
            : new[] { new SugarParameter("@PurOrd", purOrd.Trim()) };
        var row = (await _db.Ado.SqlQueryAsync<DetailInitRow>(
            $"""
            SELECT p.`Usage` AS `Usage`, IFNULL(MAX(d.Line),0)+1 AS Line, p.RecID AS PurOrdRecID, p.PurOrd AS PurOrd
            FROM PurOrdMaster p
            LEFT JOIN PurOrdDetail d ON p.PurOrd=d.PurOrd
            WHERE p.PurOrd=@PurOrd{tenantWhere}
            GROUP BY p.`Usage`, p.RecID, p.PurOrd
            LIMIT 1
            """,
            initPars)).FirstOrDefault();
        return row ?? throw Oops.Oh("采购订单不存在");
    }

    [DisplayName("选择物料列表")]
    [HttpGet("outsource-order/items")]
    public async Task<object> GetItemList([FromQuery] ItemSelectListInput 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> { "1=1" };
        var pars = new List<SugarParameter>();

        if (_userManager.TenantId > 0)
        {
            where.Add("tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Descr))
        {
            where.Add("Descr LIKE @Descr");
            pars.Add(new SugarParameter("@Descr", $"%{input.Descr.Trim()}%"));
        }

        var fromSql = $"FROM ItemMaster WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<ItemLookupRow>(
            $"""
            SELECT RecID, ItemNum, Descr, Descr1, UM, Location, Rev, Drawing
            {fromSql}
            ORDER BY {BuildItemOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);
        return new { total, page, pageSize, list };
    }

    [DisplayName("采购组下拉")]
    [HttpGet("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("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("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("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 };
    }

    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" => $"p.PurOrd {dir}",
            "suppname" => $"p.Supp {dir}",
            "buyer" => $"p.Buyer {dir}",
            "orddate" => $"p.OrdDate {dir}",
            "duedate" => $"p.DueDate {dir}",
            "curr" => $"p.Curr {dir}",
            "status" => $"p.Status {dir}",
            _ => "p.RecID DESC"
        };
    }

    private static string BuildDetailOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "purord" => $"d.PurOrd {dir}",
            "line" => $"d.Line {dir}",
            "itemnum" => $"d.ItemNum {dir}",
            "descr" => $"i.Descr {dir}",
            "um" => $"d.UM {dir}",
            "rev" => $"d.Rev {dir}",
            "drawing" => $"d.Drawing {dir}",
            "qtyorded" => $"d.QtyOrded {dir}",
            "qtyreceived" => $"d.QtyReceived {dir}",
            "qtyreleased" => $"d.QtyReleased {dir}",
            "duedate" => $"d.DueDate {dir}",
            "location" => $"d.Location {dir}",
            "workord" => $"d.WorkOrd {dir}",
            "lotserial" => $"d.LotSerial {dir}",
            "status" => $"d.Status {dir}",
            _ => "d.RecID DESC"
        };
    }

    private static string BuildItemOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "itemnum" => $"ItemNum {dir}",
            "descr" => $"Descr {dir}",
            "descr1" => $"Descr1 {dir}",
            "um" => $"UM {dir}",
            "location" => $"Location {dir}",
            "rev" => $"Rev {dir}",
            "drawing" => $"Drawing {dir}",
            _ => "RecID DESC"
        };
    }

    private sealed class OutsourceOrderListRow
    {
        public int Id { get; set; }
        public string? PurOrd { get; set; }
        public string? SuppName { get; set; }
        public string? Potype { get; set; }
        public string? ReqBy { get; set; }
        public string? Buyer { get; set; }
        public string? DepartmentDescr { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Curr { get; set; }
        public string? Status { get; set; }
        public string? Supp { get; set; }
        public string? BuyerCode { get; set; }
        public string? Department { get; set; }
        public string? Usage { get; set; }
        public string? Remark { get; set; }
    }

    private sealed class OutsourceOrderDetailRow
    {
        public int Id { get; set; }
        public string? PurOrd { get; set; }
        public int? Line { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? UM { get; set; }
        public string? Rev { get; set; }
        public string? Drawing { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? QtyReceived { get; set; }
        public decimal? QtyReleased { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Location { get; set; }
        public string? WorkOrd { get; set; }
        public string? LotSerial { get; set; }
        public string? Status { get; set; }
        public int? PurOrdRecID { get; set; }
    }

    private sealed class OutsourceOrderBatchRow
    {
        public int? Batch { get; set; }
        public string? ItemNum { get; set; }
        public string? SuppItem { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? QtyBO { get; set; }
        public decimal? QtyReleased { get; set; }
        public decimal? QtyReceived { get; set; }
        public decimal? QtyReturned { get; set; }
        public string? LotSerial { get; set; }
    }

    private sealed class ItemLookupRow
    {
        public long RecID { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Descr1 { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
        public string? Rev { get; set; }
        public string? Drawing { get; set; }
    }

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

    private sealed class DetailInitRow
    {
        public string? Usage { get; set; }
        public int Line { get; set; }
        public int PurOrdRecID { get; set; }
        public string? PurOrd { get; set; }
    }
}