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

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 订单交付服务 📦
/// 路由前缀：/api/Order/delivery/...
///
/// 【租户安全边界】需认证访问；租户一律经 <c>AidopTenantScope.ResolveOrThrow</c> 从认证后 JWT 解析，
/// 无有效租户即拒绝，不读前端 tenantId、无默认回退（原实现：直接取 <c>_userManager.TenantId</c>，
/// 无效租户时 TenantId=0 退化为查不到数据而非拒绝）。
/// </summary>
[ApiDescriptionSettings(Order = 270, Description = "订单交付")]
[Route("api/Order")]
[NonUnify]
public class OrderDeliveryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    // ══════════════════════════════════════════════════════════════
    // 列表 GET /api/Order/delivery/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单交付分页列表 📦</summary>
    [DisplayName("获取订单交付列表")]
    [HttpGet("delivery/list")]
    [RequirePcMenuPath("/aidop/s1/order-mgmt/orderDelivery")]
    public async Task<object> GetDeliveryList([FromQuery] OrderDeliveryListInput input)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var innerConditions = new List<string>
        {
            "e.tenant_id = @TenantId",
            "e.IsDeleted = 0",
            "o.bill_no IS NOT NULL"
        };

        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            innerConditions.Add("o.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.CustomNo))
        {
            innerConditions.Add("o.custom_no LIKE @CustomNo");
            pars.Add(new SugarParameter("@CustomNo", $"%{input.CustomNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNumber))
        {
            innerConditions.Add("e.item_number LIKE @ItemNumber");
            pars.Add(new SugarParameter("@ItemNumber", $"%{input.ItemNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.PlanDateFrom))
        {
            innerConditions.Add("e.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.PlanDate DESC, 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
            e.Id                                                        AS Id,
            e.seorder_id                                                AS SeOrderId,
            o.bill_no                                                   AS BillNo,
            NULL                                                        AS Rstate,
            e.entry_seq                                                 AS EntrySeq,
            IFNULL(e.item_number, '')                                   AS ItemNumber,
            IFNULL(e.item_name, '')                                     AS ItemName,
            IFNULL(e.specification, '')                                 AS Specification,
            e.unit                                                      AS Unit,
            IFNULL(o.custom_no, '')                                     AS CustomNo,
            NULL                                                        AS CustomOrderBillNo,
            NULL                                                        AS CustomOrderItemNo,
            o.custom_level                                              AS CustomLevel,
            e.qty                                                       AS Qty,
            e.deliver_count                                             AS DeliverCount,
            NULL                                                        AS BomNumber,
            ''                                                          AS EmpName,
            ''                                                          AS PlannerName,
            e.plan_date                                                 AS PlanDate,
            e.sys_capacity_date                                         AS SysCapacityDate,
            e.date                                                      AS Date,
            o.rdate                                                     AS RDate,
            e.create_time                                               AS CreateTime,
            NULL                                                        AS CreateByName,
            NULL                                                        AS Auditor,
            COALESCE(NULLIF(e.progress, ''), '1')                       AS Progress,
            NULL                                                        AS ProdLine,
            NULL                                                        AS ProdRange,
            NULL                                                        AS ShipDate,
            NULL                                                        AS Recid,
            '否'                                                        AS Spstatus,
            IFNULL(NULLIF(me.mono_list, 'null'), '')                    AS MoentryMono
        FROM crm_seorderentry e
        INNER JOIN crm_seorder o ON o.Id = e.seorder_id AND o.tenant_id = e.tenant_id AND o.IsDeleted = 0
        LEFT JOIN (
            SELECT soentry_id, tenant_id,
                   GROUP_CONCAT(moentry_mono SEPARATOR ', ') AS mono_list
            FROM mes_moentry
            WHERE IsDeleted = 0
            GROUP BY soentry_id, tenant_id
        ) me ON me.soentry_id = e.Id
            AND me.tenant_id = e.tenant_id
        WHERE {innerWhere}
        """;

    // ══════════════════════════════════════════════════════════════
    // 价值流 GET /api/Order/delivery/flow/{entryId}
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单价值流全量数据（8个Tab）📦</summary>
    [DisplayName("获取订单价值流")]
    [HttpGet("delivery/flow/{entryId}")]
    [RequirePcMenuPath("/aidop/s1/order-mgmt/orderDelivery")]
    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)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                IFNULL(e.item_number, '')  AS ItemNumber,
                IFNULL(e.item_name, '')    AS ItemName,
                IFNULL(e.specification, '') AS Specification,
                e.qty                  AS Qty,
                COALESCE(o.bill_from, '') AS BillFrom,
                e.plan_date            AS PlanDate,
                COALESCE(NULLIF(e.progress, ''), '1') AS Progress,
                e.update_time          AS Mrptime
            FROM crm_seorderentry e
            INNER JOIN crm_seorder o ON o.Id = e.seorder_id AND o.tenant_id = e.tenant_id AND o.IsDeleted = 0
            WHERE (e.Id = @Id)
              AND e.tenant_id = @TenantId
              AND e.IsDeleted = 0
              AND o.bill_no IS NOT NULL
            ORDER BY e.update_time DESC, e.Id DESC
            LIMIT 1
            """;
        return (await _db.Ado.SqlQueryAsync<FlowBasicRow>(sql, new { Id = entryId, TenantId = tenantId })).FirstOrDefault();
    }

    // ── Tab1 订单信息 ──
    private async Task<FlowOrderInfoRow?> QueryOrderInfo(long entryId)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                o.bill_no            AS BillNo,
                o.custom_name        AS CustomName,
                o.custom_level       AS CustomLevel,
                COALESCE(o.flowstate, '') AS State,
                o.date               AS Date,
                ''                   AS CreateByName,
                o.order_type         AS OrderType,
                o.update_time        AS AuditDate,
                ''                   AS Auditor,
                COALESCE(o.bill_from, '') AS BillFrom,
                ''                   AS EmpName,
                ''                   AS EmpNo
            FROM crm_seorder o
            INNER JOIN crm_seorderentry e ON e.seorder_id = o.Id AND e.tenant_id = o.tenant_id AND e.IsDeleted = 0
            WHERE e.Id = @Id
              AND o.tenant_id = @TenantId
              AND o.IsDeleted = 0
            LIMIT 1
            """;
        return (await _db.Ado.SqlQueryAsync<FlowOrderInfoRow>(sql, new { Id = entryId, TenantId = tenantId })).FirstOrDefault();
    }

    // ── Tab2 订单评审 ──
    /// <remarks>
    /// 查询最新评审记录（num=1 的首行物料）的预计交期评估信息。
    /// </remarks>
    private async Task<FlowReviewRow?> QueryReview(long entryId)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                bce.satisfy_time AS SysCapacityDate,
                ber.morder_no AS MorderNo,
                ber.create_time AS CreateTime,
                bce.make_qty AS MakeQty,
                bce.use_qty AS UseQty,
                bce.mo_qty AS MoQty
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.num = '1' AND bce.is_use = 1
            WHERE ber.sentry_id = @EntryId
              AND ber.tenant_id = @TenantId
              AND ber.IsDeleted = 0
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.sentry_id = @EntryId
                    AND br.tenant_id = @TenantId
                    AND br.IsDeleted = 0
                  ORDER BY br.create_time DESC
                  LIMIT 1
              )
            ORDER BY bce.id
            LIMIT 1
            """;
        return (await _db.Ado.SqlQueryAsync<FlowReviewRow>(sql, new { EntryId = entryId, TenantId = tenantId })).FirstOrDefault();
    }

    // ── Tab2 变更记录（MySQL：按订单行关联变更单）──
    private async Task<List<FlowChangeRow>> QueryChanges(long entryId)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY c.create_time, c.Id) AS Sno,
                c.bill_no         AS BillNo,
                c.change_Type     AS ChangeType,
                c.change_Reason   AS ChangeReason,
                c.change_content  AS ChangeContent,
                c.update_time     AS UpdateTime
            FROM crm_seorder_change c
            WHERE c.seorder_id = (
                SELECT e.seorder_id FROM crm_seorderentry e
                WHERE e.Id = @Id AND e.tenant_id = @TenantId AND e.IsDeleted = 0
                LIMIT 1
            )
              AND c.tenant_id = @TenantId
              AND c.IsDeleted = 0
            ORDER BY c.create_time, c.Id
            """;
        return await _db.Ado.SqlQueryAsync<FlowChangeRow>(sql, new { Id = entryId, TenantId = tenantId });
    }

    // ── Tab3 计划排程1（MySQL：按分录 Id 关联工单排程 + 物料主档）──
    private async Task<List<FlowScheduleRow>> QuerySchedule(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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 AND im.tenant_id = @TenantId
            LEFT JOIN mes_morder mo ON mo.morder_no = sch.WorkOrds AND mo.tenant_id = @TenantId
            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid AND me.tenant_id = @TenantId
            LEFT JOIN crm_seorderentry se ON se.Id = me.soentry_id
            WHERE se.id = @EntryId AND se.tenant_id = @TenantId AND mo.IsDeleted = 0
            ORDER BY sch.PlanDate
            """;
        return await _db.Ado.SqlQueryAsync<FlowScheduleRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab4 物料需求（MySQL：最新评审结果 + BOM 子件明细，同需求明细核验口径）──
    private async Task<List<FlowMaterialRow>> QueryMaterials(long entryId)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY bce.id) AS Sno,
                bce.num AS Num,
                IFNULL(bce.item_number, '') AS ItemNumber,
                IFNULL(bce.item_name, '') AS ItemName,
                bce.bom_number AS BomNumber,
                bce.model AS Model,
                DATE_FORMAT(bce.kitting_time, '%Y-%m-%d') AS KittingTime,
                bce.type AS Type,
                NULL AS Unit,
                bce.erp_cls_name AS ErpClsName,
                bce.qty AS Qty,
                bce.needCount AS NeedCount,
                bce.sqty AS Sqty,
                bce.use_qty AS UseQty,
                bce.self_lack_qty AS SelfLackQty,
                bce.lack_qty AS LackQty,
                bce.mo_qty AS MoQty,
                bce.make_qty AS MakeQty,
                bce.purchase_qty AS PurchaseQty,
                bce.purchase_occupy_qty AS PurchaseOccupyQty,
                NULL AS SubcontractingQty,
                NULL AS Backflush,
                DATE_FORMAT(bce.satisfy_time, '%Y-%m-%d') AS SatisfyTime,
                bce.haveicsubs AS HaveIcSubs,
                bce.substitute_code AS SubstituteCode
            FROM b_bom_child_examine bce
            INNER JOIN b_examine_result ber ON ber.Id = bce.examine_id
            WHERE ber.sentry_id = @EntryId
              AND ber.tenant_id = @TenantId
              AND ber.IsDeleted = 0
              AND bce.is_use = 1
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.sentry_id = @EntryId
                    AND br.tenant_id = @TenantId
                    AND br.IsDeleted = 0
                  ORDER BY br.create_time DESC
                  LIMIT 1
              )
            ORDER BY bce.id ASC
            """;
        return await _db.Ado.SqlQueryAsync<FlowMaterialRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab5 采购订单（MySQL：参考工单执行跟踪，双路合并 PW + DO/PO）──
    private async Task<List<FlowPoRow>> QueryPo(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY PoBillno) AS Sno,
                PoBillno, SupplierNo, SupplierName, PoTotal, PoPurchaser, State, PoSsendDate, CreateTime
            FROM (
                SELECT DISTINCT
                    pm.PurOrd AS PoBillno,
                    IFNULL(TRIM(pm.Supp), '') AS SupplierNo,
                    IFNULL(TRIM(s.SortName), '') AS SupplierName,
                    CAST(IFNULL(det.PoTotal, 0) AS DECIMAL(18, 2)) AS PoTotal,
                    IFNULL(TRIM(pm.Buyer), '') AS PoPurchaser,
                    CASE
                        WHEN IFNULL(LENGTH(pm.Status), 0) = 0 THEN '新增'
                        WHEN pm.Status = 'R' THEN '新增'
                        WHEN pm.Status = 'S' THEN '已下达'
                        WHEN pm.Status = 'C' THEN '关闭'
                        ELSE pm.Status
                    END AS State,
                    pm.OrdDate AS PoSsendDate,
                    pm.CreateTime AS CreateTime
                FROM PurOrdMaster pm
                LEFT JOIN SuppMaster s ON pm.Supp = s.Supp AND s.tenant_id = pm.tenant_id
                LEFT JOIN (
                    SELECT PurOrdRecID,
                           SUM(IFNULL(QtyOrded, 0) * IFNULL(PurCost, 0)) AS PoTotal
                    FROM PurOrdDetail
                    GROUP BY PurOrdRecID
                ) det ON det.PurOrdRecID = pm.RecID
                WHERE pm.tenant_id = @TenantId
                  AND IFNULL(pm.IsActive, 0) <= 1
                  AND (
                      pm.WorkOrd IN (
                          SELECT me.moentry_mono FROM mes_moentry me
                          WHERE me.soentry_id = @EntryId AND me.tenant_id = @TenantId
                      )
                      OR pm.RecID IN (
                          SELECT pd.PurOrdRecID
                          FROM PurOrdDetail pd
                          INNER JOIN srm_pr_main pr
                              ON (pr.pr_billno = pd.Req OR pr.SAP_pr_billno = pd.Req)
                              AND pr.tenant_id = pd.tenant_id
                          WHERE pr.pr_mono IN (
                              SELECT me.moentry_mono FROM mes_moentry me
                              WHERE me.soentry_id = @EntryId AND me.tenant_id = @TenantId
                          )
                          AND pr.tenant_id = @TenantId
                          AND IFNULL(pr.state, 0) <> 0
                          AND IFNULL(pr.IsDeleted, 0) = 0
                      )
                  )
            ) AS t
            ORDER BY PoBillno
            """;
        return await _db.Ado.SqlQueryAsync<FlowPoRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab5 采购申请（MySQL：参考工单执行跟踪，pr_mono 直连工单）──
    private async Task<List<FlowPrRow>> QueryPr(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY pm.pr_billno) AS Sno,
                pm.pr_billno AS PrBillNo,
                IFNULL(ic.number, '') AS Number,
                IFNULL(pm.icitem_name, '') AS IcItemName,
                IFNULL(ic.model, '') AS Model,
                IFNULL(pm.pr_purchasenumber, '') AS PrPurchaseNumber,
                IFNULL(pm.pr_purchasename, '') AS PrPurchaseName,
                CAST(IFNULL(pm.pr_aqty, 0) AS DECIMAL(18, 4)) AS PrAQty,
                pm.pr_ssend_date AS PrSsendDate,
                pm.pr_sarrive_date AS PrSarriveDate,
                IFNULL(pm.pr_unit, '') AS PrUnit,
                IFNULL(pm.pr_purchaser, '') AS PrPurchaser
            FROM srm_pr_main pm
            LEFT JOIN ic_item ic ON pm.icitem_id = ic.Id
            WHERE pm.pr_mono IN (
                SELECT me.moentry_mono FROM mes_moentry me
                WHERE me.soentry_id = @EntryId AND me.tenant_id = @TenantId
            )
            AND pm.tenant_id = @TenantId
            AND IFNULL(pm.state, 0) <> 0
            AND pm.IsDeleted = 0
            ORDER BY pm.pr_billno
            """;
        return await _db.Ado.SqlQueryAsync<FlowPrRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab5 物料交货单（MySQL：经采购订单关联到交货单 srm_polist_ds）──
    private async Task<List<FlowDeliveryScheduleRow>> QueryDeliverySchedule(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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 DISTINCT
                    ds.ponumber AS PoNumber,
                    ds.dsnum AS DsNum,
                    ds.itemnum AS ItemNum,
                    IFNULL(im.Descr, '') AS Descr,
                    ds.suppliercode AS SupplierCode,
                    ds.supplier AS Supplier,
                    ds.requestdate AS RequestDate,
                    ds.needdate AS NeedDate,
                    CAST(IFNULL(ds.schedqty, 0) AS DECIMAL(18, 4)) AS SchedQty,
                    CAST(IFNULL(ds.sentqty, 0) AS DECIMAL(18, 4)) AS SentQty,
                    CAST(IFNULL(ds.restqty, 0) AS DECIMAL(18, 4)) AS RestQty
                FROM PurOrdMaster pm
                INNER JOIN srm_polist_ds ds
                    ON pm.PurOrd = ds.ponumber
                    AND ds.tenant_id = pm.tenant_id
                LEFT JOIN ItemMaster im
                    ON ds.itemnum = im.ItemNum
                    AND (im.tenant_id = pm.tenant_id OR pm.tenant_id IS NULL)
                WHERE pm.tenant_id = @TenantId
                  AND IFNULL(pm.IsActive, 0) <= 1
                  AND (
                      pm.WorkOrd IN (
                          SELECT me.moentry_mono FROM mes_moentry me
                          WHERE me.soentry_id = @EntryId AND me.tenant_id = @TenantId
                      )
                      OR pm.RecID IN (
                          SELECT pd.PurOrdRecID
                          FROM PurOrdDetail pd
                          INNER JOIN srm_pr_main pr
                              ON (pr.pr_billno = pd.Req OR pr.SAP_pr_billno = pd.Req)
                              AND pr.tenant_id = pd.tenant_id
                          WHERE pr.pr_mono IN (
                              SELECT me.moentry_mono FROM mes_moentry me
                              WHERE me.soentry_id = @EntryId AND me.tenant_id = @TenantId
                          )
                          AND pr.tenant_id = @TenantId
                          AND IFNULL(pr.state, 0) <> 0
                          AND IFNULL(pr.IsDeleted, 0) = 0
                      )
                  )
            ) AS t
            ORDER BY t.DsNum
            """;
        return await _db.Ado.SqlQueryAsync<FlowDeliveryScheduleRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab6 生产备料（MySQL：备料明细经工单关联分录）──
    private async Task<List<FlowNbrRow>> QueryNbr(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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 AND nm.tenant_id = @TenantId
            LEFT JOIN mes_morder mo ON mo.morder_no = nm.WorkOrd AND mo.tenant_id = @TenantId
            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid AND me.tenant_id = @TenantId
            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 AND im.tenant_id = @TenantId
            WHERE se.id = @EntryId AND se.tenant_id = @TenantId AND mo.IsDeleted = 0 AND nm.Type = 'SM'
            ORDER BY nm.Nbr
            """;
        return await _db.Ado.SqlQueryAsync<FlowNbrRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab7 生产报工（OpTransEmployee + 销售订单行）──
    private async Task<List<FlowProdReportRow>> QueryProdReport(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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 AND mo.tenant_id = @TenantId
            INNER JOIN mes_moentry me ON mo.Id = me.moentry_moid AND me.tenant_id = @TenantId
            LEFT JOIN crm_seorderentry se ON se.Id = me.soentry_id
            LEFT JOIN ItemMaster im ON ote.ItemNum = im.ItemNum AND im.tenant_id = @TenantId
            WHERE se.id = @EntryId AND se.tenant_id = @TenantId AND mo.IsDeleted = 0
            ORDER BY ote.ProdDate DESC
            """;
        return await _db.Ado.SqlQueryAsync<FlowProdReportRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab7 IQC信息（请购/采购/检验链路）──
    private async Task<List<FlowIqcRow>> QueryIqc(long entryId)
    {
        // 同库分租户：IQC 派生表锚表 qms_qcp_inspbill 已补 tenant_id，按当前租户过滤（避免跨租户检验单串入链路）。
        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 AND b.tenant_id = @TenantId                LEFT JOIN qms_qcp_insappnentry c ON b.id = c.glid AND c.tenant_id = @TenantId
                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
                WHERE a.tenant_id = @TenantId
            ) 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, TenantId = AidopTenantScope.ResolveOrThrow(_userManager) });
    }

    // ── Tab8 FQC信息 ──
    private async Task<List<FlowFqcRow>> QueryFqc(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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 AND b.tenant_id = @TenantId
            LEFT JOIN mes_moentry c ON b.WorkOrd = c.moentry_mono AND c.tenant_id = @TenantId
            LEFT JOIN crm_seorderentry se ON c.soentry_id = se.id
            WHERE c.soentry_id = @EntryId AND se.tenant_id = @TenantId
            ORDER BY a.FBILLNO
            """;
        return await _db.Ado.SqlQueryAsync<FlowFqcRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab8 生产入库 ──
    private async Task<List<FlowReceiptRow>> QueryReceipt(long entryId)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        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.qty_change > 0 THEN a.qty_change ELSE 0 END AS QtyChangeAdvance,
                mo.moentry_prdname,
                mo.moentry_wrkcname,
                c.OrdDate,
                c.DueDate,
                a.trans_time AS CreateTime,
                c.itemnum AS ItemNum,
                im.descr AS Descr
            FROM WorkOrdMaster c
            LEFT JOIN mdp_std_inv_trans a ON a.work_ord = c.WorkOrd AND IFNULL(a.src_trans_type_raw, a.trans_type) = 'rct-wo' AND a.qty_change > 0 AND a.tenant_id = c.tenant_id
            LEFT JOIN GeneralizedCodeMaster b ON IFNULL(a.src_trans_type_raw, a.trans_type) = b.Val AND b.FldName = 'TransType' AND b.`Domain` = c.`Domain` AND b.tenant_id = c.tenant_id
            LEFT JOIN ItemMaster im ON c.ItemNum = im.ItemNum AND im.tenant_id = c.tenant_id
            LEFT JOIN PeriodSequenceDet det ON det.ItemNum = im.ItemNum AND det.WorkOrds = c.WorkOrd AND det.tenant_id = c.tenant_id
            LEFT JOIN (
                SELECT WorkOrds, tenant_id, SUM(IFNULL(CompQty, 0)) AS CompQty
                FROM PeriodSequenceDet
                GROUP BY WorkOrds, tenant_id
            ) compp ON c.WorkOrd = compp.WorkOrds AND compp.tenant_id = c.tenant_id
            LEFT JOIN mes_morder mo ON mo.morder_no = c.WorkOrd AND mo.IsDeleted = 0 AND mo.tenant_id = c.tenant_id
            LEFT JOIN mes_moentry motry ON motry.moentry_moid = mo.Id AND motry.IsDeleted = 0 AND motry.tenant_id = c.tenant_id
            INNER JOIN crm_seorderentry se ON motry.soentry_id = se.id
            WHERE motry.soentry_id = @EntryId AND se.tenant_id = @TenantId AND c.tenant_id = @TenantId
            ORDER BY mo.morder_no
            """;
        return await _db.Ado.SqlQueryAsync<FlowReceiptRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }

    // ── Tab8 成品发运（与订单行 OrdNbr + 行物料一致）──
    private async Task<List<FlowShipmentRow>> QueryShipment(long entryId)
    {
        var tenantId = AidopTenantScope.ResolveOrThrow(_userManager);
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY d.ShipDate DESC, d.RecID DESC) AS Sno,
                COALESCE(d.LotSerial, CAST(d.RecID AS CHAR)) AS Id,
                d.Site             AS Location,
                d.ShipDate         AS ShipDate,
                CAST(d.QtyToShip AS DECIMAL(18,2)) AS QtyToShip,
                d.Site             AS LocationCode,
                d.RealQty          AS RealQty
            FROM ASNBOLShipperDetail d
            INNER JOIN ASNBOLShipperMaster m ON m.RecID = d.ASNBOLShipperRecID AND m.tenant_id = d.tenant_id
            INNER JOIN crm_seorder o ON o.bill_no = m.OrdNbr AND o.tenant_id = m.tenant_id AND o.IsDeleted = 0
            INNER JOIN crm_seorderentry e ON e.seorder_id = o.Id AND e.tenant_id = o.tenant_id AND e.IsDeleted = 0
            WHERE e.Id = @EntryId
              AND d.tenant_id = @TenantId
            ORDER BY d.ShipDate DESC, d.RecID DESC
            """;
        return await _db.Ado.SqlQueryAsync<FlowShipmentRow>(sql, new { EntryId = entryId, TenantId = tenantId });
    }
}