using System.Text.Json;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Admin.NET.Plugin.AiDOP.ProcurementExecution.Dto;

namespace Admin.NET.Plugin.AiDOP.ProcurementExecution;

/// <summary>
/// S4 供应商交货管理
///
/// 【租户安全边界】租户一律经 <see cref="AidopTenantScope.ResolveOrThrow"/> 从认证后 JWT 解析，
/// 无有效租户即拒绝，不读前端 tenantId、无默认回退。
/// <para>
/// 原实现：类级 <c>[AllowAnonymous]</c> + 全类 <c>AidopTenantHelper.Resolve(App.HttpContext)</c>。
/// 后者解析顺序为 requestTenantId &gt; JWT claim &gt; <b>query 参数 tenantId</b> &gt; <b>DefaultTenantId 兜底</b>，
/// 其自身注释即写明「不得用于业务数据的查询与写入」。两者叠加的实测后果：匿名（无 Token、无 Cookie）
/// <c>GET /api/ProcurementExecution/supplier-delivery/list?tenantId=&lt;任意租户&gt;</c> 返回该租户业务数据
/// （797 返回 94 条、UAT 返回 7 条、不传参数则落默认租户）；同类的 4 个 HttpPost 写接口走同一条解析链。
/// </para>
/// </summary>
[ApiDescriptionSettings(Order = 320, Description = "S4供应商交货管理")]
[Route("api/ProcurementExecution")]
[NonUnify]
public class SupplierDeliveryManagementService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    [DisplayName("供应商交货管理列表")]
    [HttpGet("supplier-delivery/list")]
    public async Task<object> GetList([FromQuery] SupplierDeliveryListInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var conditions = new List<string>();
        if (!string.IsNullOrWhiteSpace(input.Cgdd))
        {
            conditions.Add("v.cgdd LIKE @cgdd");
            pars.Add(new SugarParameter("@cgdd", $"%{input.Cgdd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            conditions.Add("v.dsnum LIKE @dsnum");
            pars.Add(new SugarParameter("@dsnum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Wlbm))
        {
            conditions.Add("v.wlbm LIKE @wlbm");
            pars.Add(new SugarParameter("@wlbm", $"%{input.Wlbm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Gys))
        {
            conditions.Add("(v.gysdm LIKE @gys OR v.gysmc LIKE @gys)");
            pars.Add(new SugarParameter("@gys", $"%{input.Gys.Trim()}%"));
        }

        var where = conditions.Count > 0 ? $" WHERE {string.Join(" AND ", conditions)} " : string.Empty;
        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);
        var offset = (input.Page - 1) * input.PageSize;
        var wrapped = $"{BuildBaseSql()} {where}";

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(*) FROM ({wrapped}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<SupplierDeliveryListRow>(
            $"SELECT * FROM ({wrapped}) t {orderBy} LIMIT {input.PageSize} OFFSET {offset}", pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    [DisplayName("供应商交货管理发布")]
    [HttpPost("supplier-delivery/publish")]
    public async Task<object> Publish([FromBody] SupplierDeliveryBatchInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `id`,
              `glid1`,
              `wlbm`, `wlms`, `wlgg`, `cgdd`,
              `jhdsl`, `wjhsl`, `jhd`, `yjjhrq`, `jqhf`,
              `type`, `flag`, `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              UUID() AS id,
              ds.id AS glid1,
              ds.itemnum AS wlbm,
              im.Descr AS wlms,
              im.Descr1 AS wlgg,
              ds.ponumber AS cgdd,
              ds.schedqty AS jhdsl,
              a.wjhsl AS wjhsl,
              ds.dsnum AS jhd,
              a.yjjhrq AS yjjhrq,
              DATE_FORMAT(DATE(a.yjjhrq), '%Y-%m-%d') AS jqhf,
              a.`type` AS `type`,
              2 AS flag,
              a.scrq AS scrq,
              a.scrid AS scrid,
              a.scrxm AS scrxm,
              a.gysdm AS gysdm,
              a.gysmc AS gysmc,
              @hfrid AS hfrid,
              @hfrxm AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              a.qhdj AS qhdj,
              a.ddhh AS ddhh,
              a.dw AS dw,
              a.bzsl AS bzsl,
              a.ly AS ly
            FROM srm_polist_ds ds
            LEFT JOIN ItemMaster im
              ON ds.itemnum = im.ItemNum
            LEFT JOIN vscm_jhjh a
              ON a.cgdd = ds.ponumber
             AND a.ddhh = ds.poline
            WHERE FIND_IN_SET(CAST(ds.id AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND ds.tenant_id = @TenantId
              AND NOT EXISTS (
                SELECT 1 FROM scm_jhjh_jq x
                WHERE x.glid1 = ds.id
                  AND FIND_IN_SET(CAST(x.glid1 AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              );
            """;
        await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@hfrid", userId),
            new("@hfrxm", userName),
            new("@TenantId", tenantId)
        });
        return new { message = "发布成功" };
    }

    [DisplayName("供应商交货管理按计划日期回复")]
    [HttpPost("supplier-delivery/reply-by-plan-date")]
    public async Task<object> ReplyByPlanDate([FromBody] SupplierDeliveryBatchInput input)
    {
        if (string.IsNullOrWhiteSpace(input.Ids))
            throw Oops.Oh("缺少勾选行ID（ids）");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";

        // 列表勾选 id 为 srm_polist_ds.id，须以交货计划表驱动（与发布一致），不能从 vscm_jhjh 驱动否则匹配不到行。
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `id`, `glid`, `wlbm`, `wlms`, `wlgg`, `cgdd`, `jhdsl`, `wjhsl`,
              `jhd`, `yjjhrq`, `jqhf`, `type`, `flag`,
              `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              UUID() AS id,
              ds.id AS glid,
              ds.itemnum AS wlbm,
              im.Descr AS wlms,
              im.Descr1 AS wlgg,
              ds.ponumber AS cgdd,
              CAST(ds.schedqty AS CHAR(50)) AS jhdsl,
              CAST(IFNULL(a.wjhsl, 0) AS CHAR(50)) AS wjhsl,
              ds.dsnum AS jhd,
              CAST(IFNULL(a.yjjhrq, ds.requestDate) AS CHAR(50)) AS yjjhrq,
              DATE_FORMAT(DATE(IFNULL(a.yjjhrq, ds.requestDate)), '%Y-%m-%d') AS jqhf,
              a.`type` AS `type`,
              0 AS flag,
              a.scrq AS scrq,
              a.scrid AS scrid,
              a.scrxm AS scrxm,
              IFNULL(a.gysdm, ds.suppliercode) AS gysdm,
              IFNULL(a.gysmc, ds.supplier) AS gysmc,
              @userid AS hfrid,
              @username AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              a.qhdj AS qhdj,
              ds.poline AS ddhh,
              a.dw AS dw,
              a.bzsl AS bzsl,
              a.ly AS ly
            FROM srm_polist_ds ds
            LEFT JOIN ItemMaster im
              ON ds.itemnum = im.ItemNum
            LEFT JOIN vscm_jhjh a
              ON a.cgdd = ds.ponumber
             AND a.ddhh = ds.poline
            WHERE FIND_IN_SET(CAST(ds.id AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND ds.isactive = 1
              AND ds.status = 'P'
              AND ds.tenant_id = @TenantId;
            """;

        var affected = await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@userid", userId),
            new("@username", userName),
            new("@TenantId", tenantId),
        });

        if (affected <= 0)
            throw Oops.Oh("未插入任何回复记录。请确认勾选的是本列表中的交货计划行，且交货单 status=P、isactive=1。");

        return new { message = "按计划日期回复成功" };
    }

    [DisplayName("供应商交货管理回复交期")]
    [HttpPost("supplier-delivery/reply-due-date")]
    public async Task<object> ReplyDueDate([FromBody] SupplierDeliveryReplyDueDateInput input)
    {
        if (string.IsNullOrWhiteSpace(input.Ids))
            throw Oops.Oh("缺少勾选行ID（ids）");
        if (string.IsNullOrWhiteSpace(input.Jqhf))
            throw Oops.Oh("缺少交期回复日期（jqhf）");

        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";

        // 列表勾选 id 为 srm_polist_ds.id，须以交货计划表驱动（与发布一致）。
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `id`,
              `glid`, `wlbm`, `wlms`, `wlgg`, `cgdd`,
              `jhdsl`, `wjhsl`, `jhd`, `yjjhrq`, `jqhf`,
              `type`, `flag`,
              `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              UUID() AS id,
              ds.id AS glid,
              ds.itemnum AS wlbm,
              im.Descr AS wlms,
              im.Descr1 AS wlgg,
              ds.ponumber AS cgdd,
              CAST(ds.schedqty AS CHAR(50)) AS jhdsl,
              CAST(IFNULL(a.wjhsl, 0) AS CHAR(50)) AS wjhsl,
              ds.dsnum AS jhd,
              CAST(IFNULL(a.yjjhrq, ds.requestDate) AS CHAR(50)) AS yjjhrq,
              @jqhf AS jqhf,
              a.`type` AS `type`,
              0 AS flag,
              a.scrq AS scrq,
              a.scrid AS scrid,
              a.scrxm AS scrxm,
              IFNULL(a.gysdm, ds.suppliercode) AS gysdm,
              IFNULL(a.gysmc, ds.supplier) AS gysmc,
              @userid AS hfrid,
              @username AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              a.qhdj AS qhdj,
              ds.poline AS ddhh,
              a.dw AS dw,
              a.bzsl AS bzsl,
              a.ly AS ly
            FROM srm_polist_ds ds
            LEFT JOIN ItemMaster im
              ON ds.itemnum = im.ItemNum
            LEFT JOIN vscm_jhjh a
              ON a.cgdd = ds.ponumber
             AND a.ddhh = ds.poline
            WHERE FIND_IN_SET(CAST(ds.id AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND ds.isactive = 1
              AND ds.status = 'P'
              AND ds.tenant_id = @TenantId;
            """;

        var affected = await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@jqhf", input.Jqhf.Trim()),
            new("@userid", userId),
            new("@username", userName),
            new("@TenantId", tenantId),
        });

        if (affected <= 0)
            throw Oops.Oh("未插入任何回复记录。请确认勾选的是本列表中的交货计划行，且交货单 status=P、isactive=1。");

        return new { message = "回复交期成功" };
    }

    [DisplayName("交货单关闭")]
    [HttpPost("supplier-delivery/close")]
    public async Task<object> CloseDelivery([FromBody] DeliveryCloseInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);

        // 检查是否已有关联的发货单明细（scm_shdzb.jhdbh = 交货单号）
        var shipmentCount = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*)
            FROM scm_shdzb d
            INNER JOIN scm_shd m ON CAST(m.id AS CHAR) = d.glid
            WHERE d.jhdbh = @jhdbh
              AND m.tenant_id = @TenantId
              AND IFNULL(m.state, 1) <> 0
            """,
            new List<SugarParameter> { new("@jhdbh", input.DsNum), new("@TenantId", tenantId) });

        if (shipmentCount > 0)
            throw Oops.Oh("此发货单还未收完货，无法关闭交货单");

        await _db.Ado.ExecuteCommandAsync(
            "UPDATE srm_polist_ds SET status='C' WHERE DSNum=@jhdbh AND tenant_id=@TenantId",
            new List<SugarParameter> { new("@jhdbh", input.DsNum), new("@TenantId", tenantId) });

        await TryEnqueueReceiptConfirmOutboxAsync(tenantId, input.DsNum);

        return new { message = "交货单关闭成功" };
    }

    /// <summary>交货关闭视为收货确认信号，写 Outbox（幂等）。</summary>
    private async Task TryEnqueueReceiptConfirmOutboxAsync(long tenantId, string dsNum)
    {
        try
        {
            if (string.IsNullOrWhiteSpace(dsNum)) return;
            var payload = JsonSerializer.Serialize(new
            {
                path = "/erp/receipt/confirm",
                method = "POST",
                body = new
                {
                    dsNum,
                    status = "C",
                    action = "S4_RECEIPT_CONFIRM"
                }
            });
            var exists = await _db.Ado.GetIntAsync(
                "SELECT COUNT(1) FROM mdp_outbox WHERE tenant_id=@tid AND target_source_code='WMS_API' AND action_code='S4_RECEIPT_CONFIRM' AND idem_key=@idem",
                new List<SugarParameter> { new("@tid", tenantId), new("@idem", dsNum) }) > 0;
            if (exists) return;

            await _db.Insertable(new MdpOutbox
            {
                TenantId = tenantId,
                TargetSourceCode = "WMS_API",
                ActionCode = "S4_RECEIPT_CONFIRM",
                IdemKey = dsNum,
                PayloadJson = payload,
                Status = 0,
                RetryCount = 0,
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            }).ExecuteCommandAsync();
        }
        catch { /* Outbox 失败不影响关闭 */ }
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["sffb"] = "t.sffb",
            ["wlbm"] = "t.wlbm",
            ["wlms"] = "t.wlms",
            ["buyer"] = "t.buyer",
            ["gysdm"] = "t.gysdm",
            ["gysmc"] = "t.gysmc",
            ["cgdd"] = "t.cgdd",
            ["ddhh"] = "t.ddhh",
            ["jhdsl"] = "t.jhdsl",
            ["dfhsl"] = "t.dfhsl",
            ["dsnum"] = "t.dsnum",
            ["requestdate"] = "t.requestdate",
            ["schedqty"] = "t.schedqty",
            ["jqhfnew"] = "t.jqhfnew",
            ["ztsl"] = "t.ztsl",
            ["zsl1"] = "t.zsl1",
            ["rksl"] = "t.rksl",
            ["bhgsl"] = "t.bhgsl",
            ["thsl"] = "t.thsl",
            ["bz"] = "t.bz"
        };
        var field = map.TryGetValue(sortField ?? string.Empty, out var sqlField) ? sqlField : "t.requestdate";
        var order = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $" ORDER BY {field} {order} ";
    }

    private static string BuildBaseSql() => """
        SELECT
            v.*,
            CASE
                WHEN v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0) > v.wjhsl THEN v.wjhsl
                WHEN v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0) < 0 THEN 0
                ELSE v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0)
            END AS dfhsl,
            CASE
                WHEN IFNULL(v.zsl18, 0) - IFNULL(v.thsl, 0) <= IFNULL(v.zshl, 0) THEN 0
                ELSE IFNULL(v.zsl18, 0) - IFNULL(v.thsl, 0) - IFNULL(v.zshl, 0)
            END AS ztsl,
            IFNULL(v.zsl, 0) AS zsl1,
            v.thsl AS bhgsl,
            CONCAT(
                CASE
                    WHEN v.sfyqnew = 'wait' OR v.sfyqnew = 'NO' THEN 'jqhfnew:yellow'
                    WHEN v.sfyqnew = 'refuse' THEN 'jqhfnew:red'
                    WHEN v.sfyqnew = 'OK' THEN 'jqhfnew:#99FF00'
                    ELSE ''
                END,
                v.sffbgrdnew
            ) AS background,
            CASE
                WHEN v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0) <= 0 THEN '完成'
                ELSE '待交'
            END AS yjzt
        FROM
        (
            SELECT
                p.PurOrd,
                p.Line,
                p.ItemNum,
                s.fhsl - IFNULL(dor.thsl, 0) AS zfhl,
                p.ReceiptQty + p.RctQty AS zshl,
                i.zjsl AS zsl,
                CASE WHEN p.PurOrd LIKE 'DO%' THEN p.RctQty ELSE p.RctQty - IFNULL(i.zjsl, 0) END AS rksl,
                CASE
                    WHEN p.PurOrd LIKE 'DO%' THEN IF(p.QtyReturned > 0, p.QtyReturned, dor.thsl)
                    ELSE IF(p.QtyReturned > 0, p.QtyReturned, p.QtyReturned + dor.thsl)
                END AS thsl,
                s.fhsl AS zsl18,
                sh.jhdyj,
                jy.jhdzj,
                IFNULL(ds.SchedQty, 0) - IFNULL(sh.jhdyj, 0) AS jhddj,
                p.QtyOrded - p.RctQty - p.ReceiptQty + p.QtyReturned AS wjhsl,
                p.QtyOrded AS jhdsl,
                ds.SchedQty,
                ds.DSNum,
                CASE WHEN b2.id IS NULL THEN 'X' ELSE b2.hfsj END AS sffb,
                p.ItemNum AS wlbm,
                im.Descr AS wlms,
                ds.supplier AS gysmc,
                p.PurOrd AS cgdd,
                p.Line AS ddhh,
                pm.Buyer AS buyer,
                IFNULL(ds.requestDate, p.DueDate) AS requestdate,
                IFNULL(b.jqhf, '') AS jqhfnew,
                IFNULL(b.fpjh, '') AS fpjhnew,
                ds.SentQty,
                IFNULL(im.Drawing, p.Drawing) AS th,
                IFNULL(im.Rev, p.Rev) AS bbh,
                CAST(ds.id AS CHAR) AS id,
                '' AS jhd,
                ds.suppliercode AS gysdm,
                CASE WHEN IFNULL(ds.id, '') = '' THEN p.Remarks ELSE ds.Remarks END AS bz,
                '' AS shd,
                ds.id AS jhdid,
                p.DueDate AS jhrq,
                p.RecID AS polid,
                im.Descr1 AS wlgg,
                CASE WHEN b2.id IS NULL THEN '' ELSE ';sffb:#99FF00' END AS sffbgrdnew,
                CASE
                    WHEN IFNULL(b.jqhf, '') = '' THEN 'wait'
                    WHEN TIMESTAMPDIFF(DAY, IFNULL(ds.requestDate, p.DueDate), b.jqhf) <= 2 THEN 'OK'
                    WHEN TIMESTAMPDIFF(DAY, IFNULL(ds.requestDate, p.DueDate), b.jqhf) > 2 THEN 'NO'
                    WHEN b.jqhf = '' AND IFNULL(b.qhdj, '') <> '' THEN 'refuse'
                    ELSE 'wait'
                END AS sfyqnew
            FROM PurOrdDetail p
            LEFT JOIN PurOrdMaster pm ON p.PurOrd = pm.PurOrd AND pm.tenant_id = p.tenant_id
            LEFT JOIN (
                SELECT SUM(sh_delivery_quantity) AS fhsl, po_bill, po_billline, tenant_id
                FROM scm_shdzb
                WHERE tenant_id = @TenantId
                GROUP BY po_bill, po_billline, tenant_id
            ) s ON s.po_bill = p.PurOrd AND s.po_billline = p.Line
            LEFT JOIN (
                SELECT SUM(Qty) AS zjsl, PurOrd, PurLine, tenant_id
                FROM MissedPrint
                WHERE Status = 'I' AND tenant_id = @TenantId
                GROUP BY PurOrd, PurLine, tenant_id
            ) i ON i.PurOrd = p.PurOrd AND i.PurLine = p.Line
            LEFT JOIN (
                SELECT SUM(QtyReturn) AS thsl, OrdNbr, OrdLine, tenant_id
                FROM PurOrdRctDetail
                WHERE rcttype IN ('pt', 'temp') AND tenant_id = @TenantId
                GROUP BY OrdNbr, OrdLine, tenant_id
            ) dor ON dor.OrdNbr = p.PurOrd AND dor.OrdLine = p.Line
            LEFT JOIN srm_polist_ds ds ON p.PurOrd = ds.ponumber AND p.Line = ds.poline AND ds.tenant_id = p.tenant_id
            LEFT JOIN (
                SELECT SUM(sh_delivery_quantity) AS jhdyj, jhdbh, tenant_id
                FROM scm_shdzb
                WHERE tenant_id = @TenantId
                GROUP BY jhdbh, tenant_id
            ) sh ON ds.dsnum = sh.jhdbh
            LEFT JOIN (
                SELECT SUM(IFNULL(Qty, 0)) AS jhdzj, PurOrdDetBatchNbr, tenant_id
                FROM MissedPrint
                WHERE Status = 'I' AND tenant_id = @TenantId
                GROUP BY PurOrdDetBatchNbr, tenant_id
            ) jy ON ds.dsnum = jy.PurOrdDetBatchNbr
            LEFT JOIN (
                SELECT glid1, MIN(id) AS id, MIN(DATE_FORMAT(hfsj, '%Y.%m.%d')) AS hfsj
                FROM scm_jhjh_jq
                WHERE flag = 2
                GROUP BY glid1
            ) b2 ON ds.id = b2.glid1
            LEFT JOIN ItemMaster im ON p.ItemNum = im.ItemNum AND (im.tenant_id = p.tenant_id OR p.tenant_id IS NULL)
            LEFT JOIN (
                SELECT
                    glid,
                    GROUP_CONCAT(CONCAT(CAST(jhdsl AS CHAR(10)), '(', DATE_FORMAT(jqhf, '%Y-%m-%d'), ')') SEPARATOR '<br>') AS fpjh,
                    GROUP_CONCAT(qhdj SEPARATOR '') AS qhdj,
                    MAX(jqhf) AS jqhf
                FROM scm_jhjh_jq
                WHERE flag = 0
                GROUP BY glid
            ) b ON ds.id = b.glid
            WHERE p.Status <> 'C'
              AND ds.schedqty >= 0
              AND ds.isactive = 1
              AND ds.status = 'P'
              AND p.tenant_id = @TenantId
        ) v
        """;

    private sealed class SupplierDeliveryListRow
    {
        public string? Sffb { get; set; }
        public string? Wlbm { get; set; }
        public string? Wlms { get; set; }
        public string? Buyer { get; set; }
        public string? Gysdm { get; set; }
        public string? Gysmc { get; set; }
        public string? Cgdd { get; set; }
        public int? Ddhh { get; set; }
        public decimal? Jhdsl { get; set; }
        public decimal? Dfhsl { get; set; }
        public string? Dsnum { get; set; }
        public DateTime? Requestdate { get; set; }
        public decimal? Schedqty { get; set; }
        public string? Jqhfnew { get; set; }
        public decimal? Ztsl { get; set; }
        public decimal? Zsl1 { get; set; }
        public decimal? Rksl { get; set; }
        public decimal? Bhgsl { get; set; }
        public decimal? Thsl { get; set; }
        public string? Bz { get; set; }
        public string? Id { get; set; }
        public decimal? Wjhsl { get; set; }
        public decimal? Zfhl { get; set; }
    }
}