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.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)
    {
        // ── 先取工单号 ──
        const string monoSql = """
            SELECT a.moentry_mono
            FROM mes_moentry a
            JOIN mes_morder b ON a.moentry_mono = b.morder_no
            WHERE a.soentry_id = @Id
            """;
        var mono = await _db.Ado.GetStringAsync(monoSql, new { Id = entryId });

        // ── 并发查询各 Tab ──
        var basicTask = QueryBasic(entryId);
        var orderInfoTask = QueryOrderInfo(entryId);
        var reviewTask = QueryReview(entryId, mono);
        var changesTask = QueryChanges(entryId);
        var scheduleTask = QuerySchedule(mono);
        var materialTask = QueryMaterials(entryId, mono);
        var poTask = QueryPo(mono);
        var prTask = QueryPr(mono);
        var deliveryScheduleTask = QueryDeliverySchedule(mono);
        var nbrTask = QueryNbr(mono);
        var prodReportTask = QueryProdReport(mono);
        var iqcTask = QueryIqc(mono);
        var fqcTask = QueryFqc(mono);
        var receiptTask = QueryReceipt(mono);
        var shipmentTask = QueryShipment(entryId);

        await Task.WhenAll(basicTask, orderInfoTask, reviewTask, changesTask,
            scheduleTask, materialTask, poTask, prTask, deliveryScheduleTask,
            nbrTask, prodReportTask, iqcTask, fqcTask, receiptTask, shipmentTask);

        return new
        {
            basic = basicTask.Result,
            tab1 = orderInfoTask.Result,
            tab2 = new { review = reviewTask.Result, changes = changesTask.Result },
            tab3 = scheduleTask.Result,
            tab4 = materialTask.Result,
            tab5 = new { po = poTask.Result, pr = prTask.Result, deliverySchedule = deliveryScheduleTask.Result },
            tab6 = nbrTask.Result,
            tab7 = new { prodReport = prodReportTask.Result, iqc = iqcTask.Result },
            tab8 = new { fqc = fqcTask.Result, receipt = receiptTask.Result, shipment = shipmentTask.Result },
        };
    }

    // ── 基础信息 ──
    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 订单评审 ──
    private async Task<FlowReviewRow?> QueryReview(long entryId, string? mono)
    {
        const string sql = """
            SELECT
                entry.sys_capacity_date AS SysCapacityDate,
                mo.moentry_mono         AS MorderNo,
                entry.create_time       AS CreateTime,
                mo.make_qty             AS MakeQty,
                mo.use_qty              AS UseQty,
                mo.mo_qty               AS MoQty
            FROM crm_seorderentry entry
            LEFT JOIN (
                SELECT a.soentry_id, a.moentry_mono,
                       b.make_qty, b.use_qty, b.mo_qty
                FROM mes_moentry a
                LEFT JOIN mes_morder b ON a.moentry_mono = b.morder_no
            ) mo ON entry.id = mo.soentry_id
            WHERE entry.Id = @Id AND entry.IsDeleted = 0
            """;
        return (await _db.Ado.SqlQueryAsync<FlowReviewRow>(sql, new { Id = entryId })).FirstOrDefault();
    }

    // ── Tab2 变更记录 ──
    private async Task<List<FlowChangeRow>> QueryChanges(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY c.update_time) 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
            JOIN crm_seorderentry entry ON c.bill_no = (
                SELECT s.bill_no FROM crm_seorder s WHERE s.Id = entry.seorder_id
            )
            WHERE entry.Id = @Id
            ORDER BY c.update_time
            """;
        return await _db.Ado.SqlQueryAsync<FlowChangeRow>(sql, new { Id = entryId });
    }

    // ── Tab3 计划排程 ──
    private async Task<List<FlowScheduleRow>> QuerySchedule(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY WorkDate) AS Sno,
                WorkOrds, WorkDate, Line, Op, WorkQty,
                ItemNum, Descr, Descr1, CreateTime
            FROM PeriodSequenceDet
            WHERE WorkOrds = @Mono
            ORDER BY WorkDate
            """;
        return await _db.Ado.SqlQueryAsync<FlowScheduleRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab4 物料需求 ──
    private async Task<List<FlowMaterialRow>> QueryMaterials(long entryId, string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY num) AS Sno,
                num, item_number AS ItemNumber, item_name AS ItemName,
                model AS Model, unit AS Unit, bom_number AS BomNumber,
                kitting_time AS KittingTime, erp_cls_name AS ErpClsName,
                backflush AS Backflush, qty AS Qty, scrap AS Scrap,
                wastage AS Wastage, needCount AS NeedCount,
                self_lack_qty AS SelfLackQty, sqty AS Sqty,
                use_qty AS UseQty, lack_qty AS LackQty,
                mo_qty AS MoQty, make_qty AS MakeQty,
                purchase_qty AS PurchaseQty,
                purchase_occupy_qty AS PurchaseOccupyQty,
                subcontracting_qty AS SubcontractingQty,
                satisfy_time AS SatisfyTime,
                type AS Type, haveicsubs AS HaveIcSubs,
                substitute_code AS SubstituteCode
            FROM b_kitting_detail
            WHERE mono = @Mono
            ORDER BY num
            """;
        return await _db.Ado.SqlQueryAsync<FlowMaterialRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab5 采购订单 ──
    private async Task<List<FlowPoRow>> QueryPo(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY po.create_time) AS Sno,
                po.po_billno        AS PoBillNo,
                po.supplier_no      AS SupplierNo,
                po.supplier_name    AS SupplierName,
                po.po_total         AS PoTotal,
                po.po_purchaser     AS PoPurchaser,
                po.state            AS State,
                po.po_ssend_date    AS PoSsendDate,
                po.create_time      AS CreateTime
            FROM srm_po_main po
            WHERE po.pr_mono = @Mono
            ORDER BY po.create_time
            """;
        return await _db.Ado.SqlQueryAsync<FlowPoRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab5 采购申请 ──
    private async Task<List<FlowPrRow>> QueryPr(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY pr.create_time) AS Sno,
                pr.pr_billno            AS PrBillNo,
                pr.number               AS Number,
                pr.icitem_name          AS IcItemName,
                pr.model                AS Model,
                pr.pr_purchasenumber    AS PrPurchaseNumber,
                pr.pr_purchasename      AS PrPurchaseName,
                pr.pr_aqty              AS PrAQty,
                pr.pr_ssend_date        AS PrSsendDate,
                pr.pr_sarrive_date      AS PrSarriveDate,
                pr.pr_unit              AS PrUnit,
                pr.pr_purchaser         AS PrPurchaser
            FROM srm_pr_main pr
            WHERE pr.pr_mono = @Mono AND pr.state > 1
            ORDER BY pr.create_time
            """;
        return await _db.Ado.SqlQueryAsync<FlowPrRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab5 物料交货单 ──
    private async Task<List<FlowDeliveryScheduleRow>> QueryDeliverySchedule(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY d.requestdate) AS Sno,
                d.ponumber      AS PoNumber,
                d.dsnum         AS DsNum,
                d.itemnum       AS ItemNum,
                d.Descr         AS Descr,
                d.suppliercode  AS SupplierCode,
                d.supplier      AS Supplier,
                d.requestdate   AS RequestDate,
                d.needdate      AS NeedDate,
                d.schedqty      AS SchedQty,
                d.sentqty       AS SentQty,
                d.restqty       AS RestQty
            FROM srm_delivery_det d
            WHERE d.mono = @Mono
            ORDER BY d.requestdate
            """;
        return await _db.Ado.SqlQueryAsync<FlowDeliveryScheduleRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab6 生产备料 ──
    private async Task<List<FlowNbrRow>> QueryNbr(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                CAST(d.Line AS CHAR) AS Line,
                n.Nbr, d.ItemNum, d.Descr, d.Descr1,
                d.LocationFrom, d.LocationTo,
                d.CurrQtyOpened, d.QtyOrd, d.QtyFrom, d.QtyRec,
                d.unit, d.UpdateTime
            FROM NbrMaster n
            JOIN NbrDetail d ON n.Nbr = d.Nbr
            WHERE n.WorkOrd = @Mono AND n.type = 'SM'
            ORDER BY d.Line
            """;
        return await _db.Ado.SqlQueryAsync<FlowNbrRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab7 生产报工 ──
    private async Task<List<FlowProdReportRow>> QueryProdReport(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY r.ProdDate) AS Sno,
                r.WorkOrd, r.ItemNum, r.Descr, r.Descr1, r.Um,
                r.QtyCompleted, r.QtyReject, r.QtyScrapped,
                r.ProdDate, r.Op, r.Name
            FROM mes_production_report r
            WHERE r.WorkOrd = @Mono
            ORDER BY r.ProdDate
            """;
        return await _db.Ado.SqlQueryAsync<FlowProdReportRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab7 IQC信息 ──
    private async Task<List<FlowIqcRow>> QueryIqc(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY i.FAPPLYTIME) AS Line,
                i.po_bill       AS PoBill,
                i.po_billline   AS PoBillLine,
                i.FBILLNO       AS FBillNo,
                i.FMATERIALCFG  AS FMaterialCfg,
                i.wlmc          AS Wlmc,
                i.ggxh          AS Ggxh,
                i.FRINSQTY      AS FRinsQty,
                i.FAPPLYTIME    AS FApplyTime,
                i.FINSPESTARTDATE   AS FInspeStartDate,
                i.FINSPECTSTATUS    AS FInspecStatus
            FROM mes_iqc_bill i
            WHERE i.mono = @Mono
            ORDER BY i.FAPPLYTIME
            """;
        return await _db.Ado.SqlQueryAsync<FlowIqcRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab8 FQC信息 ──
    private async Task<List<FlowFqcRow>> QueryFqc(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY f.FINSPEENDDATE) AS Sno,
                f.WorkOrd, f.sczld, f.FBILLNO,
                f.FMATERIALCFG  AS FMaterialCfg,
                f.wlmc          AS Wlmc,
                f.ggxh          AS Ggxh,
                f.scph          AS Scph,
                f.FRINSQTY      AS FRinsQty,
                f.FINSPEENDDATE AS FInspeEndDate
            FROM mes_fqc_bill f
            WHERE f.WorkOrd = @Mono
            ORDER BY f.FINSPEENDDATE
            """;
        return await _db.Ado.SqlQueryAsync<FlowFqcRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab8 生产入库 ──
    private async Task<List<FlowReceiptRow>> QueryReceipt(string? mono)
    {
        if (string.IsNullOrWhiteSpace(mono)) return new();
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY r.CreateTime) AS Sno,
                r.Status, r.WorkOrd, r.itemnum AS ItemNum, r.descr AS Descr,
                r.UM, r.QtyOrded, r.CompQty,
                r.inspection_number AS InspectionNumber,
                r.qualified_number  AS QualifiedNumber,
                r.QtyChangeAdvance,
                r.moentry_prdname   AS MoentryPrdName,
                r.moentry_wrkcname  AS MoentryWrkcName,
                r.OrdDate, r.DueDate, r.CreateTime
            FROM mes_production_receipt r
            WHERE r.WorkOrd = @Mono
            ORDER BY r.CreateTime
            """;
        return await _db.Ado.SqlQueryAsync<FlowReceiptRow>(sql, new SugarParameter("@Mono", mono));
    }

    // ── Tab8 成品发运 ──
    private async Task<List<FlowShipmentRow>> QueryShipment(long entryId)
    {
        const string sql = """
            SELECT
                ROW_NUMBER() OVER (ORDER BY d.ShipDate) AS Sno,
                d.Id, d.Location, d.ShipDate,
                d.QtyToShip, d.RealQty,
                d.Location AS LocationCode
            FROM ASNBOLShipperDetail d
            JOIN crm_seorderentry entry ON d.ContainerItem = entry.item_number
            JOIN crm_seorder sorder     ON d.OrdNbr = sorder.bill_no
                                       AND sorder.Id = entry.seorder_id
            WHERE entry.Id = @Id AND d.IsActive = 1
            ORDER BY d.ShipDate
            """;
        return await _db.Ado.SqlQueryAsync<FlowShipmentRow>(sql, new { Id = entryId });
    }
}