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

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 要货令服务（PurOrdMaster/PurOrdDetail，Potype=PO，ReqBy=DO）
/// </summary>
[ApiDescriptionSettings(Order = 311, Description = "要货令")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class DemandOrderService : IDynamicApiController, ITransient
{
    private const int PurOrdSerialWidth = 3;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    [DisplayName("要货令列表")]
    [HttpGet("demand-order/list")]
    public async Task<object> GetList([FromQuery] DemandOrderListInput 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='PO'",
            "IFNULL(p.ReqBy,'')='DO'",
            "p.IsActive<=1"
        };
        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()));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            var st = input.Status.Trim();
            if (string.Equals(st, "R", StringComparison.OrdinalIgnoreCase))
                where.Add("(IFNULL(LENGTH(p.Status),0)=0 OR p.Buyer IS NULL)");
            else
            {
                where.Add("p.Status=@Status");
                pars.Add(new SugarParameter("@Status", st));
            }
        }

        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<DemandOrderListRow>(
            $"""
            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.Contract AS Contract,
                p.DeliverTo AS DeliverTo,
                p.DueDate AS DueDate,
                p.Contact AS Contact,
                p.ShipTo AS ShipTo,
                p.Curr AS Curr,
                p.TaxClass AS TaxClass,
                p.TaxIn AS TaxIn,
                CASE
                    WHEN IFNULL(LENGTH(p.Status),0)=0 OR p.Buyer IS NULL THEN 'R'
                    ELSE p.Status
                END AS Status,
                p.Remark AS Remark,
                p.Supp AS Supp,
                p.Buyer AS BuyerCode,
                p.Department AS Department,
                p.`Usage` AS `Usage`,
                p.FSTID AS Fstid,
                (CASE WHEN EXISTS(SELECT 1 FROM PurOrdDetail pd WHERE pd.PurOrd=p.PurOrd AND IFNULL(pd.RctQty,0)>0) THEN 1 ELSE 0 END) AS HasReceived
            {fromSql}
            ORDER BY {BuildOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    [DisplayName("要货令详情")]
    [HttpGet("demand-order/{id:int}")]
    public async Task<object> GetDetail(int id)
    {
        var master = await GetMasterRowAsync(id);
        var details = await _db.Ado.SqlQueryAsync<DemandOrderDetailRow>(
            """
            SELECT
                d.RecID AS Id,
                d.Line AS Line,
                d.ItemNum AS ItemNum,
                d.UM AS UM,
                d.Location AS Location,
                d.QtyOrded AS QtyOrded,
                d.RctQty AS RctQty,
                d.ReceiptQty AS ReceiptQty,
                d.DueDate AS DueDate,
                d.Rev AS Rev,
                d.Drawing AS Drawing,
                d.LotSerial AS LotSerial,
                d.Potype AS Potype,
                d.PurOrd AS PurOrd,
                d.PurOrdRecID AS PurOrdRecID
            FROM PurOrdDetail d
            WHERE d.PurOrd=@PurOrd AND d.Potype='PO'
            ORDER BY d.Line
            """,
            new SugarParameter("@PurOrd", master.PurOrd));

        return new { master, details };
    }

    [DisplayName("保存要货令")]
    [HttpPost("demand-order/save")]
    public async Task<object> Save([FromBody] DemandOrderSaveInput input)
    {
        if (string.IsNullOrWhiteSpace(input.Supp)) throw Oops.Oh("请选择供应商");
        if (string.IsNullOrWhiteSpace(input.Department)) throw Oops.Oh("请选择部门");

        var now = DateTime.Now;
        var ordDate = ParseDate(input.OrdDate) ?? now.Date;
        var buyer = string.IsNullOrWhiteSpace(input.Buyer) ? "110" : input.Buyer.Trim();
        var reqBy = string.IsNullOrWhiteSpace(input.ReqBy) ? "DO" : input.ReqBy.Trim();
        var usage = string.IsNullOrWhiteSpace(input.Usage) ? "标准" : input.Usage.Trim();
        var fstid = string.Equals(usage, "VMI", StringComparison.OrdinalIgnoreCase) ? "3" : "1";
        if (!string.IsNullOrWhiteSpace(input.Fstid)) fstid = input.Fstid.Trim();

        try
        {
            _db.Ado.BeginTran();

            int masterId;
            string purOrd;

            if (input.Id is null or <= 0)
            {
                purOrd = await PurOrdNumberGenerator.NextAsync(_db, "DO", DateTime.Today, PurOrdSerialWidth);
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO PurOrdMaster
                    (PurOrd,OrdDate,Supp,ReqBy,Buyer,Department,Curr,`Usage`,Remark,Potype,FSTID,Status,IsActive,Confirming,
                     CreateUser,CreateTime,UpdateUser,UpdateTime,Domain,tenant_id)
                    VALUES
                    (@PurOrd,@OrdDate,@Supp,@ReqBy,@Buyer,@Department,@Curr,@Usage,@Remark,'PO',@FSTID,'R',1,0,
                     @CreateUser,@CreateTime,@UpdateUser,@UpdateTime,@Domain,@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("@Curr", input.Curr?.Trim()),
                    new SugarParameter("@Usage", usage),
                    new SugarParameter("@Remark", input.Remark?.Trim()),
                    new SugarParameter("@FSTID", fstid),
                    new SugarParameter("@CreateUser", _userManager.Account),
                    new SugarParameter("@CreateTime", now),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", now),
                    new SugarParameter("@Domain", "100"),
                    new SugarParameter("@TenantId", _userManager.TenantId <= 0 ? null : _userManager.TenantId)
                );
                masterId = await _db.Ado.GetIntAsync(
                    "SELECT IFNULL(MAX(RecID),0) FROM PurOrdMaster WHERE PurOrd=@PurOrd",
                    new SugarParameter("@PurOrd", purOrd));
            }
            else
            {
                masterId = input.Id.Value;
                var exists = await _db.Ado.GetIntAsync(
                    "SELECT COUNT(1) FROM PurOrdMaster WHERE RecID=@Id AND Potype='PO' AND IFNULL(ReqBy,'')='DO'" + (_userManager.TenantId > 0 ? " AND tenant_id=@TenantId" : ""),
                    new SugarParameter("@Id", masterId),
                    _userManager.TenantId > 0 ? new SugarParameter("@TenantId", _userManager.TenantId) : null!);
                if (exists <= 0) throw Oops.Oh("记录不存在");

                purOrd = await _db.Ado.GetStringAsync(
                    "SELECT PurOrd FROM PurOrdMaster WHERE RecID=@Id LIMIT 1",
                    new SugarParameter("@Id", masterId)) ?? throw Oops.Oh("记录不存在");

                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE PurOrdMaster
                    SET OrdDate=@OrdDate,Supp=@Supp,ReqBy=@ReqBy,Buyer=@Buyer,Department=@Department,
                        Curr=@Curr,`Usage`=@Usage,Remark=@Remark,FSTID=@FSTID,
                        UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
                    WHERE RecID=@Id
                    """,
                    new SugarParameter("@Id", masterId),
                    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("@Curr", input.Curr?.Trim()),
                    new SugarParameter("@Usage", usage),
                    new SugarParameter("@Remark", input.Remark?.Trim()),
                    new SugarParameter("@FSTID", fstid),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", now)
                );
            }

            await SaveDetailsAsync(masterId, purOrd, input.Details ?? new List<DemandOrderDetailInput>());

            _db.Ado.CommitTran();
            return new { id = masterId, purOrd, message = input.Id is null or <= 0 ? "新增成功" : "编辑成功" };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    [DisplayName("删除要货令")]
    [HttpPost("demand-order/delete/{id:int}")]
    public async Task<object> Delete(int id, [FromQuery] string purOrd)
    {
        if (string.IsNullOrWhiteSpace(purOrd)) throw Oops.Oh("缺少要货令单号");

        var delPars = new List<SugarParameter> { new("@PurOrd", purOrd.Trim()) };
        if (_userManager.TenantId > 0) delPars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        var tenantSuffix = _userManager.TenantId > 0 ? " AND tenant_id=@TenantId" : string.Empty;

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

        var hasDetailRct = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM PurOrdDetail WHERE PurOrd=@PurOrd AND IFNULL(RctQty,0)>0{tenantSuffix}",
            delPars);
        if (hasDetailRct > 0) throw Oops.Oh("存在收货数量，不允许删除");

        var hasShip = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM scm_shdzb WHERE po_bill=@PurOrd{tenantSuffix}",
            delPars);
        if (hasShip > 0) throw Oops.Oh("存在发货单，不允许删除");

        try
        {
            _db.Ado.BeginTran();
            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM PurOrdDetail WHERE PurOrd=@PurOrd AND Potype='PO'",
                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("demand-order/items")]
    public async Task<object> GetItemList([FromQuery] DemandOrderItemListInput 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("demand-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("demand-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("demand-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("demand-order/options/curr")]
    public async Task<object> GetCurrOptions()
    {
        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("demand-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 async Task SaveDetailsAsync(int masterId, string purOrd, List<DemandOrderDetailInput> details)
    {
        var dbDetails = await _db.Ado.SqlQueryAsync<DemandOrderDetailRow>(
            """
            SELECT RecID AS Id, Line AS Line
            FROM PurOrdDetail
            WHERE PurOrd=@PurOrd AND Potype='PO'
            """,
            new SugarParameter("@PurOrd", purOrd));
        var dbById = dbDetails.ToDictionary(d => d.Id);
        var inputIds = new HashSet<int>(details.Where(d => d.Id is > 0).Select(d => d.Id!.Value));

        for (var i = 0; i < details.Count; i++)
        {
            var d = details[i];
            if (string.IsNullOrWhiteSpace(d.ItemNum)) continue;
            if (!d.QtyOrded.HasValue || d.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", d.ItemNum.Trim()))).FirstOrDefault();
            if (item == null) throw Oops.Oh($"物料不存在：{d.ItemNum}");

            var um = d.UM ?? item.UM;
            var location = d.Location ?? item.Location;
            var rev = d.Rev ?? item.Rev;
            var drawing = d.Drawing ?? item.Drawing;
            var dueDate = ParseDate(d.DueDate);
            var now = DateTime.Now;

            if (d.Id is > 0 && dbById.ContainsKey(d.Id.Value))
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE PurOrdDetail
                    SET ItemNum=@ItemNum,Descr=@Descr,UM=@UM,Location=@Location,QtyOrded=@QtyOrded,
                        DueDate=@DueDate,Rev=@Rev,Drawing=@Drawing,LotSerial=@LotSerial,
                        UpdateUser=@UpdateUser,UpdateTime=@UpdateTime
                    WHERE RecID=@Id
                    """,
                    new SugarParameter("@Id", d.Id.Value),
                    new SugarParameter("@ItemNum", item.ItemNum),
                    new SugarParameter("@Descr", item.Descr),
                    new SugarParameter("@UM", um),
                    new SugarParameter("@Location", location),
                    new SugarParameter("@QtyOrded", d.QtyOrded.Value),
                    new SugarParameter("@DueDate", dueDate),
                    new SugarParameter("@Rev", rev),
                    new SugarParameter("@Drawing", drawing),
                    new SugarParameter("@LotSerial", d.LotSerial?.Trim()),
                    new SugarParameter("@UpdateUser", _userManager.Account),
                    new SugarParameter("@UpdateTime", now)
                );
            }
            else
            {
                var line = d.Line ?? await _db.Ado.GetIntAsync(
                    "SELECT IFNULL(MAX(Line),0)+1 FROM PurOrdDetail WHERE PurOrd=@PurOrd",
                    new SugarParameter("@PurOrd", purOrd));

                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, 'PO', 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", purOrd),
                    new SugarParameter("@Line", line),
                    new SugarParameter("@ItemNum", item.ItemNum),
                    new SugarParameter("@Descr", item.Descr),
                    new SugarParameter("@UM", um),
                    new SugarParameter("@Rev", rev),
                    new SugarParameter("@Drawing", drawing),
                    new SugarParameter("@Location", location),
                    new SugarParameter("@QtyOrded", d.QtyOrded.Value),
                    new SugarParameter("@DueDate", dueDate),
                    new SugarParameter("@LotSerial", d.LotSerial?.Trim()),
                    new SugarParameter("@PurOrdRecID", masterId),
                    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)
                );
            }
        }

        foreach (var toDelete in dbDetails.Where(u => !inputIds.Contains(u.Id)))
        {
            var rctQty = await _db.Ado.GetDecimalAsync(
                "SELECT IFNULL(RctQty,0) FROM PurOrdDetail WHERE RecID=@Id",
                new SugarParameter("@Id", toDelete.Id));
            if (rctQty > 0) throw Oops.Oh("明细存在收货数量，不允许删除");

            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM PurOrdDetail WHERE RecID=@Id",
                new SugarParameter("@Id", toDelete.Id));
        }
    }

    private async Task<DemandOrderListRow> GetMasterRowAsync(int id)
    {
        var tenantId = _userManager.TenantId;
        var tenantWhere = tenantId > 0 ? " AND p.tenant_id=@TenantId" : string.Empty;
        var pars = new List<SugarParameter> { new("@Id", id) };
        if (tenantId > 0) pars.Add(new SugarParameter("@TenantId", tenantId));

        var row = (await _db.Ado.SqlQueryAsync<DemandOrderListRow>(
            $"""
            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.Contract AS Contract,
                p.DeliverTo AS DeliverTo,
                p.DueDate AS DueDate,
                p.Contact AS Contact,
                p.ShipTo AS ShipTo,
                p.Curr AS Curr,
                p.TaxClass AS TaxClass,
                p.TaxIn AS TaxIn,
                CASE WHEN IFNULL(LENGTH(p.Status),0)=0 OR p.Buyer IS NULL THEN 'R' ELSE p.Status END AS Status,
                p.Remark AS Remark,
                p.Supp AS Supp,
                p.Buyer AS BuyerCode,
                p.Department AS Department,
                p.`Usage` AS `Usage`,
                p.FSTID AS Fstid
            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 AND p.Potype='PO' AND IFNULL(p.ReqBy,'')='DO'{tenantWhere}
            LIMIT 1
            """,
            pars)).FirstOrDefault();
        return row ?? throw Oops.Oh("记录不存在");
    }

    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}",
            "departmentdescr" => $"p.Department {dir}",
            "orddate" => $"p.OrdDate {dir}",
            "contract" => $"p.Contract {dir}",
            "duedate" => $"p.DueDate {dir}",
            "contact" => $"p.Contact {dir}",
            "shipto" => $"p.ShipTo {dir}",
            "curr" => $"p.Curr {dir}",
            "taxclass" => $"p.TaxClass {dir}",
            "taxin" => $"p.TaxIn {dir}",
            "status" => $"p.Status {dir}",
            _ => "p.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 DemandOrderListRow
    {
        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 string? Contract { get; set; }
        public string? DeliverTo { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Contact { get; set; }
        public string? ShipTo { get; set; }
        public string? Curr { get; set; }
        public string? TaxClass { get; set; }
        public bool TaxIn { get; set; }
        public string? Status { get; set; }
        public string? Remark { get; set; }
        public string? Supp { get; set; }
        public string? BuyerCode { get; set; }
        public string? Department { get; set; }
        public string? Usage { get; set; }
        public string? Fstid { get; set; }
        public int HasReceived { get; set; }
    }

    private sealed class DemandOrderDetailRow
    {
        public int Id { get; set; }
        public int? Line { get; set; }
        public string? ItemNum { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
        public decimal? QtyOrded { get; set; }
        public decimal? RctQty { get; set; }
        public decimal? ReceiptQty { get; set; }
        public DateTime? DueDate { get; set; }
        public string? Rev { get; set; }
        public string? Drawing { 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 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; }
    }
}