AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Order/OrderDeliveryService.cs

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 订单交付服务 📦
/// 路由前缀：/api/Order/delivery/...
/// </summary>
[ApiDescriptionSettings(Order = 270, Description = "订单交付")]
[Route("api/Order")]
[AllowAnonymous]
[NonUnify]
public class OrderDeliveryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

    public OrderDeliveryService(ISqlSugarClient db)
    {
        _db = db;
    }

    // ══════════════════════════════════════════════════════════════
    // 列表 GET /api/Order/delivery/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单交付分页列表 📦</summary>
    [DisplayName("获取订单交付列表")]
    [HttpGet("delivery/list")]
    public async Task<object> GetDeliveryList([FromQuery] OrderDeliveryListInput input)
    {
        var pars = new List<SugarParameter>();
        var innerConditions = new List<string>
        {
            "entry.IsDeleted=0",
            "sorder.bill_no IS NOT NULL"
        };

        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            innerConditions.Add("sorder.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.CustomNo))
        {
            innerConditions.Add("sorder.custom_no LIKE @CustomNo");
            pars.Add(new SugarParameter("@CustomNo", $"%{input.CustomNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNumber))
        {
            innerConditions.Add("entry.item_number LIKE @ItemNumber");
            pars.Add(new SugarParameter("@ItemNumber", $"%{input.ItemNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.PlanDateFrom))
        {
            innerConditions.Add("entry.plan_date >= @PlanDateFrom");
            pars.Add(new SugarParameter("@PlanDateFrom", input.PlanDateFrom.Trim()));
        }

        var innerWhere = string.Join(" AND ", innerConditions);
        var baseSql = BuildListBaseSql(innerWhere);

        // progress 是计算列，需包一层过滤
        var outerWhere = "";
        if (!string.IsNullOrWhiteSpace(input.Progress))
        {
            outerWhere = "WHERE t.progress = @Progress";
            pars.Add(new SugarParameter("@Progress", input.Progress.Trim()));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t {outerWhere}", pars);
        var list = await _db.Ado.SqlQueryAsync<OrderDeliveryListRow>(
            $"SELECT * FROM ({baseSql}) AS t {outerWhere} ORDER BY t.id DESC LIMIT {input.PageSize} OFFSET {offset}",
            pars);

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

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            entry.Id                                                    AS Id,
            sorder.Id                                                   AS SeOrderId,
            sorder.bill_no                                              AS BillNo,
            entry.rstate                                                AS Rstate,
            entry.entry_seq                                             AS EntrySeq,
            entry.item_number                                           AS ItemNumber,
            entry.item_name                                             AS ItemName,
            entry.specification                                         AS Specification,
            entry.unit                                                  AS Unit,
            sorder.custom_no                                            AS CustomNo,
            entry.custom_order_bill_no                                  AS CustomOrderBillNo,
            entry.custom_order_itemno                                   AS CustomOrderItemNo,
            sorder.custom_level                                         AS CustomLevel,
            entry.qty                                                   AS Qty,
            entry.deliver_count                                         AS DeliverCount,
            entry.bom_number                                            AS BomNumber,
            sorder.emp_name                                             AS EmpName,
            entry.planner_name                                          AS PlannerName,
            entry.plan_date                                             AS PlanDate,
            entry.sys_capacity_date                                     AS SysCapacityDate,
            entry.date                                                  AS Date,
            entry.create_time                                           AS CreateTime,
            entry.create_by_name                                        AS CreateByName,
            sorder.auditor                                              AS Auditor,
            CASE
                WHEN entry.progress = 0 THEN '0'
                WHEN nbo.nboNum > 0     THEN '7'
                WHEN compp.num > 0      THEN '6'
                WHEN cnb.num > 0        THEN '5'
                WHEN pr.num > 0         THEN '4'
                WHEN nb.num > 0         THEN '3'
                WHEN p.num > 0          THEN '2'
                WHEN entry.progress = 3 THEN '1'
                ELSE CAST(entry.progress AS CHAR)
            END                                                         AS Progress,
            yeard.ProdLine                                              AS ProdLine,
            yeard.ProdRange                                             AS ProdRange,
            asn.ShipDate                                                AS ShipDate,
            CAST(sp.recid AS CHAR)                                      AS Recid,
            IF(sp.recid IS NOT NULL, '是', '否')                       AS Spstatus,
            mo.moentry_mono                                             AS MoentryMono
        FROM crm_seorderentry entry
        LEFT JOIN crm_seorder sorder ON entry.seorder_id = sorder.Id
        LEFT JOIN (
            SELECT a.id, a.soentry_id, b.product_code, a.moentry_mono
            FROM mes_moentry a
            JOIN mes_morder b ON a.moentry_mono = b.morder_no        ) mo ON entry.id = mo.soentry_id AND entry.item_number = mo.product_code        LEFT JOIN (
            SELECT WorkOrd, COUNT(*) AS num
            FROM NbrMaster WHERE type = 'SM' GROUP BY WorkOrd
        ) nb ON mo.moentry_mono = nb.WorkOrd        LEFT JOIN (
            SELECT WorkOrds, COUNT(*) AS num
            FROM PeriodSequenceDet GROUP BY WorkOrds
        ) p ON mo.moentry_mono = p.WorkOrds        LEFT JOIN (
            SELECT pr_mono, COUNT(*) AS num
            FROM srm_pr_main WHERE state > 1 GROUP BY pr_mono
        ) pr ON mo.moentry_mono = pr.pr_mono        LEFT JOIN (
            SELECT WorkOrds, COUNT(*) AS num
            FROM PeriodSequenceDet WHERE CompQty > 0 GROUP BY WorkOrds
        ) compp ON mo.moentry_mono = compp.WorkOrds        LEFT JOIN (
            SELECT WorkOrd, COUNT(*) AS num
            FROM NbrDetail WHERE type = 'SM' AND QtyFrom > 0 GROUP BY WorkOrd
        ) cnb ON mo.moentry_mono = cnb.WorkOrd        LEFT JOIN (
            SELECT ProdLine, ProdRange, SAPItemNumber
            FROM YearDemandManagement GROUP BY ProdLine, ProdRange, SAPItemNumber
        ) yeard ON entry.item_number = yeard.SAPItemNumber        LEFT JOIN (
            SELECT OrdNbr, ContainerItem, MAX(UpdateTime) AS ShipDate
            FROM ASNBOLShipperDetail GROUP BY OrdNbr, ContainerItem
        ) asn ON asn.OrdNbr = sorder.bill_no AND asn.ContainerItem = entry.item_number        LEFT JOIN ShippingPlanDetail sp ON entry.id = sp.sentry_id
        LEFT JOIN LATERAL (
            SELECT COUNT(*) AS nboNum
            FROM ASNBOLShipperDetail
            WHERE OrdNbr = sorder.bill_no AND ContainerItem = entry.item_number        ) AS nbo ON TRUE
        WHERE {innerWhere}
        """;

    // ══════════════════════════════════════════════════════════════
    // 价值流 GET /api/Order/delivery/flow/{entryId}
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单价值流全量数据（8个Tab）📦</summary>
    [DisplayName("获取订单价值流")]
    [HttpGet("delivery/flow/{entryId}")]
    public async Task<object> GetDeliveryFlow(long entryId)
    {
        // 同一 ISqlSugarClient 底层共用一条连接，不能对 _db.Ado 并发执行多查询（MySqlConnector 会报 Cannot Open when State is Connecting）
        var basic = await QueryBasic(entryId);
        var orderInfo = await QueryOrderInfo(entryId);
        var review = await QueryReview(entryId);
        var changes = await QueryChanges(entryId);
        var schedule = await QuerySchedule(entryId);
        var material = await QueryMaterials(entryId);
        var po = await QueryPo(entryId);
        var pr = await QueryPr(entryId);
        var deliverySchedule = await QueryDeliverySchedule(entryId);
        var nbr = await QueryNbr(entryId);
        var prodReport = await QueryProdReport(entryId);
        var iqc = await QueryIqc(entryId);
        var fqc = await QueryFqc(entryId);
        var receipt = await QueryReceipt(entryId);
        var shipment = await QueryShipment(entryId);

        return new
        {
            basic,
            tab1 = orderInfo,
            tab2 = new { review, changes },
            tab3 = schedule,
            tab4 = material,
            tab5 = new { po, pr, deliverySchedule },
            tab6 = nbr,
            tab7 = new { prodReport, iqc },
            tab8 = new { fqc, receipt, shipment },
        };
    }

    // ── 基础信息 ──
    private async Task<FlowBasicRow?> QueryBasic(long entryId)
    {
        const string sql = """
            SELECT
                entry.item_number   AS ItemNumber,
                entry.item_name     AS ItemName,
                entry.specification AS Specification,
                entry.qty           AS Qty,
                sorder.bill_from    AS BillFrom,
                entry.plan_date     AS PlanDate,
                CAST(entry.progress AS CHAR) AS Progress,
                entry.update_time   AS Mrptime
            FROM crm_seorderentry entry
            LEFT JOIN crm_seorder sorder ON entry.seorder_id = sorder.Id
            WHERE entry.Id = @Id AND entry.IsDeleted = 0
            """;
        return (await _db.Ado.SqlQueryAsync<FlowBasicRow>(sql, new { Id = entryId })).FirstOrDefault();
    }

    // ── Tab1 订单信息 ──
    private async Task<FlowOrderInfoRow?> QueryOrderInfo(long entryId)
    {
        const string sql = """
            SELECT
                sorder.bill_no          AS BillNo,
                sorder.custom_name      AS CustomName,
                sorder.custom_level     AS CustomLevel,
                sorder.flowstate        AS State,
                sorder.date             AS Date,
                sorder.create_by_name   AS CreateByName,
                sorder.order_type       AS OrderType,
                entry.update_time       AS AuditDate,
                sorder.auditor          AS Auditor,
                sorder.bill_from        AS BillFrom,
                sorder.emp_name         AS EmpName,
                sorder.emp_no           AS EmpNo
            FROM crm_seorderentry entry
            LEFT JOIN crm_seorder sorder ON entry.seorder_id = sorder.Id
            WHERE entry.Id = @Id AND entry.IsDeleted = 0
            """;
        return (await _db.Ado.SqlQueryAsync<FlowOrderInfoRow>(sql, new { Id = entryId })).FirstOrDefault();
    }

    // ── Tab2 订单评审 ──
    /// <remarks>
    /// 查询最新评审记录（num=1 的首行物料）的预计交期评估信息。
    /// </remarks>
    private async Task<FlowReviewRow?> QueryReview(long entryId)
    {
        //测试提交
        const string sql = """
            SELECT
                b.satisfy_time AS SysCapacityDate,
                c.moentry_mono AS MorderNo,
                c.create_time AS CreateTime,
                IF(b.make_qty = 0 AND b.erp_cls = 1, b.lack_qty, b.make_qty) AS MakeQty,
                b.use_qty AS UseQty,
                b.mo_qty AS MoQty
            FROM b_examine_result a
            LEFT JOIN b_bom_child_examine b ON a.id = b.examine_id
            LEFT JOIN mes_moentry c ON a.sentry_id = c.soentry_id
            LEFT JOIN mes_morder d ON c.moentry_mono = d.morder_no            WHERE a.create_time = (
                SELECT MAX(create_time)
                FROM b_examine_result
                WHERE sentry_id = @EntryId
            )
            AND b.num = 1
            AND a.sentry_id = @EntryId
            LIMIT 1
            """;
        return (await _db.Ado.SqlQueryAsync<FlowReviewRow>(sql, new { EntryId = entryId })).FirstOrDefault();
    }

    // ── Tab2 变更记录（MySQL：按订单行关联变更单）──
    private async Task<List<FlowChangeRow>> QueryChanges(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY a.id) AS Sno,
                a.bill_no AS BillNo,
                a.change_Type AS ChangeType,
                a.change_Reason AS ChangeReason,
                a.change_content AS ChangeContent,
                a.update_time AS UpdateTime
            FROM crm_seorder_change a
            LEFT JOIN crm_seorderentry b ON a.bill_no = b.bill_no            WHERE a.bill_no IS NOT NULL AND b.id = @Id
            ORDER BY a.id
            """;
        return await _db.Ado.SqlQueryAsync<FlowChangeRow>(sql, new { Id = entryId });
    }

    // ── Tab3 计划排程1（MySQL：按分录 Id 关联工单排程 + 物料主档）──
    private async Task<List<FlowScheduleRow>> QuerySchedule(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY sch.PlanDate) AS Sno,
                sch.WorkOrds,
                DATE(sch.PlanDate) AS WorkDate,
                sch.Line,
                sch.Op,
                CAST(sch.OrdQty AS DECIMAL(18, 2)) AS WorkQty,
                sch.ItemNum,
                im.Descr,
                im.Descr1,
                sch.CreateTime
            FROM PeriodSequenceDet sch
            LEFT JOIN ItemMaster im ON sch.ItemNum = im.ItemNum            LEFT JOIN mes_morder mo ON mo.morder_no = sch.WorkOrds            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid
            LEFT JOIN crm_seorderentry se ON se.Id = me.soentry_id
            WHERE se.id = @EntryId AND mo.IsDeleted = 0
            ORDER BY sch.PlanDate
            """;
        return await _db.Ado.SqlQueryAsync<FlowScheduleRow>(sql, new { EntryId = entryId });
    }

    // ── Tab4 物料需求（MySQL：最新评审结果 + BOM 子件明细，同需求明细核验口径）──
    private async Task<List<FlowMaterialRow>> QueryMaterials(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY bce.id) AS Sno,
                CAST(bce.num AS CHAR) AS Num,
                bce.item_number AS ItemNumber,
                bce.item_name AS ItemName,
                bce.bom_number AS BomNumber,
                bce.model AS Model,
                DATE_FORMAT(bce.kitting_time, '%Y-%m-%d') AS KittingTime,
                CASE WHEN bce.type = 1 THEN '替代件' ELSE '标准件' END AS Type,
                bce.unit AS Unit,
                CASE bce.erp_cls
                    WHEN 0 THEN '配置类'
                    WHEN 1 THEN '自制'
                    WHEN 2 THEN '委外加工'
                    WHEN 3 THEN '外购'
                    WHEN 4 THEN '虚拟件'
                END AS ErpClsName,
                CAST(bce.qty AS DECIMAL(18, 2)) AS Qty,
                CAST(bce.needCount AS DECIMAL(18, 2)) AS NeedCount,
                CAST(bce.sqty AS DECIMAL(18, 2)) AS Sqty,
                CAST(bce.use_qty AS DECIMAL(18, 2)) AS UseQty,
                CAST(bce.self_lack_qty AS DECIMAL(18, 2)) AS SelfLackQty,
                CAST(bce.lack_qty AS DECIMAL(18, 2)) AS LackQty,
                CAST(bce.mo_qty AS DECIMAL(18, 2)) AS MoQty,
                CAST(IF(bce.make_qty = 0 AND bce.erp_cls = 1, bce.lack_qty, bce.make_qty) AS DECIMAL(18, 2)) AS MakeQty,
                CAST(bce.purchase_qty AS DECIMAL(18, 2)) AS PurchaseQty,
                CAST(bce.purchase_occupy_qty AS DECIMAL(18, 2)) AS PurchaseOccupyQty,
                CAST(bce.subcontracting_qty AS DECIMAL(18, 2)) AS SubcontractingQty,
                CASE WHEN bce.backflush = 1 THEN '是' ELSE '否' END AS Backflush,
                DATE_FORMAT(bce.satisfy_time, '%Y-%m-%d') AS SatisfyTime,
                CASE WHEN bce.haveicsubs = 1 THEN '是' ELSE '否' END AS HaveIcSubs,
                bce.substitute_code AS SubstituteCode
            FROM crm_seorder se
            INNER JOIN crm_seorderentry sen ON se.id = sen.seorder_id
            LEFT JOIN b_examine_result ber ON ber.Id = (
                SELECT br.Id
                FROM b_examine_result br
                WHERE br.sorderid = se.id AND br.sentry_id = sen.id
                ORDER BY br.create_time DESC
                LIMIT 1
            )
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.is_use = 1
            WHERE sen.id = @EntryId
            ORDER BY bce.id ASC
            """;
        return await _db.Ado.SqlQueryAsync<FlowMaterialRow>(sql, new { EntryId = entryId });
    }

    // ── Tab5 采购订单（MySQL：经占用/工单关联到订单分录）──
    private async Task<List<FlowPoRow>> QueryPo(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY t.PoBillNo) AS Sno,
                t.PoBillNo,
                t.SupplierNo,
                t.SupplierName,
                t.PoTotal,
                t.PoPurchaser,
                t.State,
                t.PoSsendDate,
                t.CreateTime
            FROM (
                SELECT DISTINCT
                    pm.po_billno AS PoBillNo,
                    pm.supplier_no AS SupplierNo,
                    pm.supplier_name AS SupplierName,
                    CAST(pm.po_total AS DECIMAL(18, 2)) AS PoTotal,
                    pm.po_purchaser AS PoPurchaser,
                    pm.create_time AS CreateTime,
                    pm.po_ssend_date AS PoSsendDate,
                    CASE pm.state
                        WHEN 0 THEN '新增'
                        WHEN 1 THEN '审核中'
                        WHEN 2 THEN '同意'
                        WHEN 3 THEN '关闭'
                    END AS State
                FROM srm_po_main pm
                LEFT JOIN srm_po_list pl ON pm.id = pl.po_id
                LEFT JOIN srm_po_occupy occ ON pl.Id = occ.polist_id AND CAST(pm.factory_id AS CHAR(64)) = CAST(occ.factory_id AS CHAR(64))                LEFT JOIN mes_morder mo ON mo.id = occ.morder_id
                LEFT JOIN mes_moentry me ON mo.id = me.moentry_moid
                LEFT JOIN crm_seorderentry se ON me.soentry_id = se.id
                WHERE se.id = @EntryId AND pm.IsDeleted = 0
            ) AS t
            ORDER BY t.PoBillNo
            """;
        return await _db.Ado.SqlQueryAsync<FlowPoRow>(sql, new { EntryId = entryId });
    }

    // ── Tab5 采购申请（MySQL）──
    private async Task<List<FlowPrRow>> QueryPr(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY pm.pr_billno) AS Sno,
                pm.pr_billno AS PrBillNo,
                ic.number AS Number,
                pm.icitem_name AS IcItemName,
                ic.model AS Model,
                pm.pr_purchasenumber AS PrPurchaseNumber,
                pm.pr_purchasename AS PrPurchaseName,
                CAST(pm.pr_aqty AS DECIMAL(18, 2)) AS PrAQty,
                pm.pr_ssend_date AS PrSsendDate,
                pm.pr_sarrive_date AS PrSarriveDate,
                pm.pr_unit AS PrUnit,
                pm.pr_purchaser AS PrPurchaser
            FROM srm_pr_main pm
            LEFT JOIN srm_po_occupy occ ON pm.Id = occ.polist_id AND CAST(pm.factory_id AS CHAR(64)) = CAST(occ.factory_id AS CHAR(64))            LEFT JOIN ic_item ic ON pm.icitem_id = ic.Id
            LEFT JOIN mes_morder mo ON mo.id = occ.morder_id
            LEFT JOIN mes_moentry me ON mo.id = me.moentry_moid
            LEFT JOIN crm_seorderentry se ON me.soentry_id = se.id
            WHERE se.id = @EntryId AND pm.state <> 0 AND pm.IsDeleted = 0
            ORDER BY pm.pr_billno
            """;
        return await _db.Ado.SqlQueryAsync<FlowPrRow>(sql, new { EntryId = entryId });
    }

    // ── Tab5 物料交货单（MySQL：srm_polist_ds）──
    private async Task<List<FlowDeliveryScheduleRow>> QueryDeliverySchedule(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY t.DsNum) AS Sno,
                t.PoNumber,
                t.DsNum,
                t.ItemNum,
                t.Descr,
                t.SupplierCode,
                t.Supplier,
                t.RequestDate,
                t.NeedDate,
                t.SchedQty,
                t.SentQty,
                t.RestQty
            FROM (
                SELECT
                    ds.dsnum AS DsNum,
                    ds.ponumber AS PoNumber,
                    ds.itemnum AS ItemNum,
                    im.Descr AS Descr,
                    ds.suppliercode AS SupplierCode,
                    ds.supplier AS Supplier,
                    ds.requestdate AS RequestDate,
                    ds.needdate AS NeedDate,
                    ds.schedqty AS SchedQty,
                    ds.sentqty AS SentQty,
                    ds.restqty AS RestQty
                FROM srm_po_main pm
                LEFT JOIN srm_po_list pl ON pm.id = pl.po_id
                LEFT JOIN srm_po_occupy occ ON pl.Id = occ.polist_id AND CAST(pm.factory_id AS CHAR(64)) = CAST(occ.factory_id AS CHAR(64))                LEFT JOIN mes_morder mo ON mo.id = occ.morder_id
                LEFT JOIN mes_moentry me ON mo.id = me.moentry_moid
                LEFT JOIN crm_seorderentry se ON me.soentry_id = se.id
                LEFT JOIN srm_polist_ds ds ON pl.po_billno = ds.ponumber AND pl.ItemNum = ds.itemnum                LEFT JOIN ItemMaster im ON ds.itemnum = im.ItemNum                WHERE se.id = @EntryId AND pm.IsDeleted = 0
            ) AS t
            ORDER BY t.DsNum
            """;
        return await _db.Ado.SqlQueryAsync<FlowDeliveryScheduleRow>(sql, new { EntryId = entryId });
    }

    // ── Tab6 生产备料（MySQL：备料明细经工单关联分录）──
    private async Task<List<FlowNbrRow>> QueryNbr(long entryId)
    {
        const string sql = """
            SELECT
                CAST(nd.Line AS CHAR) AS Line,
                nm.Nbr AS Nbr,
                nd.ItemNum,
                im.Descr AS Descr,
                im.Descr1 AS Descr1,
                CAST(nd.QtyFrom AS DECIMAL(18, 2)) AS QtyFrom,
                CAST(nd.QtyOrd AS DECIMAL(18, 2)) AS QtyOrd,
                CAST(nd.CurrQtyOpened AS DECIMAL(18, 2)) AS CurrQtyOpened,
                CAST(nd.QtyRec AS DECIMAL(18, 2)) AS QtyRec,
                nd.UM AS Unit,
                nd.LocationFrom,
                nd.LocationTo,
                nd.UpdateTime AS UpdateTime,
                nd.WorkOrd AS WorkOrd
            FROM NbrDetail nd
            LEFT JOIN NbrMaster nm ON nd.nbr = nm.nbr            LEFT JOIN mes_morder mo ON mo.morder_no = nm.WorkOrd            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid
            LEFT JOIN crm_seorderentry se ON se.Id = me.soentry_id AND mo.product_code = se.item_number            LEFT JOIN ItemMaster im ON nd.ItemNum = im.ItemNum            WHERE se.id = @EntryId AND mo.IsDeleted = 0 AND nm.Type = 'SM'
            ORDER BY nm.Nbr
            """;
        return await _db.Ado.SqlQueryAsync<FlowNbrRow>(sql, new { EntryId = entryId });
    }

    // ── Tab7 生产报工（OpTransEmployee + 销售订单行）──
    private async Task<List<FlowProdReportRow>> QueryProdReport(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY ote.ProdDate DESC) AS Sno,
                ote.WorkOrd,
                ote.Name AS Name,
                ote.Op,
                ote.ProdDate,
                im.ItemNum,
                im.Descr,
                im.Descr1,
                im.Um,
                CAST(ote.QtyCompleted AS DECIMAL(18,2)) AS QtyCompleted,
                CAST(ote.QtyReject AS DECIMAL(18,2)) AS QtyReject,
                CAST(ote.QtyScrapped AS DECIMAL(18,2)) AS QtyScrapped
            FROM OpTransEmployee ote
            LEFT JOIN mes_morder mo ON mo.morder_no = ote.WorkOrd            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid
            LEFT JOIN crm_seorderentry se ON se.Id = me.soentry_id
            LEFT JOIN ItemMaster im ON ote.ItemNum = im.ItemNum            WHERE se.id = @EntryId AND mo.IsDeleted = 0
            ORDER BY ote.ProdDate DESC
            """;
        return await _db.Ado.SqlQueryAsync<FlowProdReportRow>(sql, new { EntryId = entryId });
    }

    // ── Tab7 IQC信息（请购/采购/检验链路）──
    private async Task<List<FlowIqcRow>> QueryIqc(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY iqcjy.FAPPLYTIME DESC) AS Line,
                iqcjy.po_bill AS PoBill,
                iqcjy.po_billline AS PoBillLine,
                iqcjy.FBILLNO AS FBillNo,
                iqcjy.FMATERIALCFG AS FMaterialCfg,
                iqcjy.wlmc AS Wlmc,
                iqcjy.ggxh AS Ggxh,
                CAST(iqcjy.FRINSQTY AS DECIMAL(18,2)) AS FRinsQty,
                iqcjy.FAPPLYTIME AS FApplyTime,
                iqcjy.FINSPESTARTDATE AS FInspeStartDate,
                iqcjy.FINSPECTSTATUS AS FInspecStatus
            FROM srm_pr_main pr
            LEFT JOIN PurOrdDetail pod ON pr.pr_billno = pod.Req            LEFT JOIN vscm_cgshrk sh ON pod.PurOrd = sh.OrdNbr AND CAST(pod.Line AS CHAR(32)) = CAST(sh.OrdLine AS CHAR(32))            LEFT JOIN scm_jhjh_jq jq ON pod.PurOrd = jq.cgdd AND CAST(pod.Line AS CHAR(32)) = CAST(jq.ddhh AS CHAR(32))            LEFT JOIN (
                SELECT
                    f.po_bill,
                    f.po_billline,
                    a.FINSPESTARTDATE,
                    b.FAPPLYTIME,
                    b.FBILLNO,
                    a.FMATERIALCFG,
                    a.wlmc,
                    a.ggxh,
                    a.FRINSQTY,
                    c.FINSPECTSTATUS
                FROM qms_qcp_inspbill a
                LEFT JOIN qms_qcp_inspecapplyn b ON a.lydjbh = b.FBILLNO                LEFT JOIN qms_qcp_insappnentry c ON b.id = c.glid
                LEFT JOIN scm_shd cd ON c.shdh = cd.shddh                LEFT JOIN scm_shdzb f ON f.shpc = c.FLOTNUMBER AND f.sh_material_code = c.FMATERIALCFG AND f.glid = cd.id
            ) iqcjy ON pod.PurOrd = iqcjy.po_bill AND CAST(pod.Line AS CHAR(32)) = CAST(iqcjy.po_billline AS CHAR(32))            WHERE jq.flag = '1'
              AND pr.sentry_id IS NOT NULL
              AND pr.sentry_id = @EntryId
            ORDER BY iqcjy.FAPPLYTIME DESC
            """;
        return await _db.Ado.SqlQueryAsync<FlowIqcRow>(sql, new { EntryId = entryId });
    }

    // ── Tab8 FQC信息 ──
    private async Task<List<FlowFqcRow>> QueryFqc(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY a.FBILLNO ASC) AS Sno,
                b.WorkOrd,
                a.sczld,
                a.FBILLNO,
                a.FMATERIALCFG AS FMaterialCfg,
                a.wlmc,
                a.ggxh,
                a.scph,
                CAST(a.FRINSQTY AS DECIMAL(18,2)) AS FRinsQty,
                a.FINSPEENDDATE AS FInspeEndDate
            FROM qms_qcpp_inspbill a
            LEFT JOIN WorkOrdMaster b ON a.sczld = b.Batch            LEFT JOIN mes_moentry c ON b.WorkOrd = c.moentry_mono            WHERE c.soentry_id = @EntryId
            ORDER BY a.FBILLNO
            """;
        return await _db.Ado.SqlQueryAsync<FlowFqcRow>(sql, new { EntryId = entryId });
    }

    // ── Tab8 生产入库 ──
    private async Task<List<FlowReceiptRow>> QueryReceipt(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY mo.morder_no) AS Sno,
                CASE
                    WHEN c.Status = 'r' THEN '下达'
                    WHEN c.Status = 'c' THEN '关闭'
                    WHEN c.Status = 'w' THEN '投产'
                    ELSE '初始'
                END AS Status,
                c.WorkOrd,
                im.Um,
                c.QtyOrded,
                IFNULL(compp.CompQty, 0) AS CompQty,
                NULL AS InspectionNumber,
                NULL AS QualifiedNumber,
                CASE WHEN a.QtyChange > 0 THEN a.QtyChange ELSE 0 END AS QtyChangeAdvance,
                mo.moentry_prdname,
                mo.moentry_wrkcname,
                c.OrdDate,
                c.DueDate,
                a.CreateTime,
                c.itemnum AS ItemNum,
                im.descr AS Descr
            FROM WorkOrdMaster c
            LEFT JOIN InvTransHist a ON a.WorkOrd = c.WorkOrd AND a.TransType = 'rct-wo' AND a.QtyChange > 0
            LEFT JOIN GeneralizedCodeMaster b ON a.TransType = b.Val AND b.FldName = 'TransType' AND b.`Domain` = c.`Domain`            LEFT JOIN ItemMaster im ON c.ItemNum = im.ItemNum AND im.`Domain` = c.`Domain`            LEFT JOIN PeriodSequenceDet det ON det.ItemNum = im.ItemNum AND det.WorkOrds = c.WorkOrd AND det.`Domain` = c.`Domain`            LEFT JOIN (
                SELECT WorkOrds, SUM(IFNULL(CompQty, 0)) AS CompQty
                FROM PeriodSequenceDet
                GROUP BY WorkOrds
            ) compp ON c.WorkOrd = compp.WorkOrds            LEFT JOIN mes_morder mo ON mo.morder_no = c.WorkOrd AND mo.IsDeleted = 0
            LEFT JOIN mes_moentry motry ON motry.moentry_moid = mo.Id AND motry.IsDeleted = 0
            WHERE motry.soentry_id = @EntryId
            ORDER BY mo.morder_no
            """;
        return await _db.Ado.SqlQueryAsync<FlowReceiptRow>(sql, new { EntryId = entryId });
    }

    // ── Tab8 成品发运（与订单行 OrdNbr + 行物料一致）──
    private async Task<List<FlowShipmentRow>> QueryShipment(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY ad.Id) AS Sno,
                ad.Id,
                ad.location AS Location,
                ad.UpdateTime AS ShipDate,
                CAST(ad.QtyToShip AS DECIMAL(18,2)) AS QtyToShip,
                lm.LocationCode,
                ad.RealQty
            FROM ASNBOLShipperDetail ad
            INNER JOIN ASNBOLShipperMaster am ON ad.ASNBOLShipperRecID = am.RecID
            LEFT JOIN LocationMaster lm ON ad.location = lm.Location            INNER JOIN crm_seorderentry se ON se.id = @EntryId
                AND ad.ContainerItem = se.item_number            INNER JOIN crm_seorder so ON so.Id = se.seorder_id AND ad.OrdNbr = so.bill_no            WHERE am.shtype = 'SH'
              AND am.typed <> 'S'
            ORDER BY ad.Id
            """;
        return await _db.Ado.SqlQueryAsync<FlowShipmentRow>(sql, new { EntryId = entryId });
    }
}