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

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 工单物料齐套上线看板服务
/// </summary>
[ApiDescriptionSettings(Order = 312, Description = "工单物料齐套上线看板")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderMaterialReadinessService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    [DisplayName("工单物料齐套上线看板列表")]
    [HttpGet("work-order-material-readiness/list")]
    public async Task<object> GetList([FromQuery] WorkOrderMaterialReadinessListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        var tenantId = _userManager.TenantId;
        if (tenantId > 0)
        {
            where.Add("a.tenant_id=@TenantId");
            pars.Add(new SugarParameter("@TenantId", tenantId));
        }

        if (!string.IsNullOrWhiteSpace(input.SeOrder))
        {
            where.Add("a.SeOrder LIKE @SeOrder");
            pars.Add(new SugarParameter("@SeOrder", $"%{input.SeOrder.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Workord))
        {
            where.Add("a.workord LIKE @Workord");
            pars.Add(new SugarParameter("@Workord", $"%{input.Workord.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BomNumber))
        {
            where.Add("a.bom_number = @BomNumber");
            pars.Add(new SugarParameter("@BomNumber", input.BomNumber.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("a.ItemNum = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierName))
        {
            where.Add("a.supplier_name LIKE @SupplierName");
            pars.Add(new SugarParameter("@SupplierName", $"%{input.SupplierName.Trim()}%"));
        }

        if (await ShouldUseDwdAsync(input))
        {
            return await GetDwdListAsync(input, page, pageSize, offset);
        }

        var fromSql = $"FROM WorkOrdTemp a WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM (SELECT DISTINCT a.RowNum, a.SeOrder, a.workord, a.ItemNum, a.Op, a.bom_number, a.supplier_number {fromSql}) t", pars);
        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);

        var list = await _db.Ado.SqlQueryAsync<WorkOrderMaterialReadinessRow>(
            $"""
            SELECT DISTINCT
                a.RowNum AS RowNum,
                a.SeOrder AS SeOrder,
                a.SePriority AS SePriority,
                a.workord AS Workord,
                a.Priority AS Priority,
                a.morder_type AS MorderType,
                a.LotSerial AS LotSerial,
                a.Op AS Op,
                a.OpOrdDate AS OpOrdDate,
                a.bom_number AS BomNumber,
                a.Descr AS Descr,
                a.Descr1 AS Descr1,
                a.production_number AS ProductionNumber,
                a.checktime AS Checktime,
                a.PMBOM AS PMBOM,
                a.ItemType AS ItemType,
                a.OwnerApplication AS OwnerApplication,
                a.ItemNum AS ItemNum,
                a.ComponentItemDescr AS ComponentItemDescr,
                a.ComponentItemDrawing AS ComponentItemDrawing,
                a.QtyRequired AS QtyRequired,
                a.ComponentItemUM AS ComponentItemUM,
                a.ComponentItemKittingTime AS ComponentItemKittingTime,
                a.QtyRec AS QtyRec,
                a.RecDate AS RecDate,
                a.shortage AS Shortage,
                a.QtyFrom AS QtyFrom,
                a.FromDate AS FromDate,
                a.zjsl AS Zjsl,
                a.jykssj AS Jykssj,
                a.ztsl AS Ztsl,
                a.jhshrq AS Jhshrq,
                a.jqsl AS Jqsl,
                a.hfjq AS Hfjq,
                a.PurOrdQty AS PurOrdQty,
                a.PurOrdorddate AS PurOrdorddate,
                a.PurOrds AS PurOrds,
                a.ContractDate AS ContractDate,
                a.ContractNO AS ContractNO,
                a.supplier_number AS SupplierNumber,
                a.supplier_name AS SupplierName,
                a.Buyer AS Buyer,
                a.BuyerUser AS BuyerUser
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    [DisplayName("刷新工单物料齐套上线看板")]
    [HttpPost("work-order-material-readiness/refresh")]
    public async Task<object> Refresh()
    {
        var now = DateTime.Now;
        var batchId = $"workordtemp-{now:yyyyMMdd-HHmmss}";

        // Step 1: 清理 WorkOrdTemp 后执行存储过程填充最新数据
        await _db.Ado.ExecuteCommandAsync("DELETE FROM dwd_material_readiness");
        try
        {
            await _db.Ado.ExecuteCommandAsync("CALL pr_MES_Report_DayWorkOrdDetailShortage_New('')");
        }
        catch (Exception ex)
        {
            return new { enabled = false, message = $"执行存储过程 pr_MES_Report_DayWorkOrdDetailShortage_New 失败: {ex.Message}" };
        }

        var workOrdTempCount = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM WorkOrdTemp WHERE IFNULL(workord, '') <> ''");

        // Step 2: 清理 dwd_material_readiness 旧数据（保留当前 WorkOrdTemp 中存在的工单）
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM dwd_material_readiness
            WHERE work_order NOT IN (
                SELECT DISTINCT workord FROM WorkOrdTemp WHERE IFNULL(workord, '') <> ''
            )
            """);

        // Step 3: 从 WorkOrdTemp 写入 dwd_material_readiness
        var dwdCount = await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO dwd_material_readiness
            (tenant_id, stat_date, work_order, op_code, parent_item_code, component_item_code, component_item_name, required_qty, cumulative_required_qty, stock_available_qty, qc_pending_qty, in_transit_qty, delivery_reply_qty, available_qty, shortage_qty, ready_status, supplier_code, supplier_name, need_date, calc_batch_id, calc_time)
            SELECT
                0,
                @StatDate,
                IFNULL(workord, ''),
                CAST(IFNULL(Op, 0) AS CHAR),
                IFNULL(bom_number, ''),
                COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
                IFNULL(ComponentItemDescr, ''),
                IFNULL(QtyRequired, 0),
                IFNULL(ljsl, 0),
                IFNULL(locStock, 0),
                IFNULL(jqsl, 0),
                IFNULL(ztsl, 0),
                IFNULL(PurOrdQty, 0),
                IFNULL(available, 0),
                IFNULL(shortage, 0),
                CASE WHEN IFNULL(shortage, 0) > 0 THEN 'SHORTAGE' ELSE 'READY' END,
                supplier_number,
                supplier_name,
                COALESCE(jhshrq, hfjq, RecDate),
                @BatchId,
                @SyncTime
            FROM WorkOrdTemp
            WHERE IFNULL(workord, '') <> ''
            ON DUPLICATE KEY UPDATE
                component_item_name = VALUES(component_item_name),
                required_qty = VALUES(required_qty),
                cumulative_required_qty = VALUES(cumulative_required_qty),
                stock_available_qty = VALUES(stock_available_qty),
                qc_pending_qty = VALUES(qc_pending_qty),
                in_transit_qty = VALUES(in_transit_qty),
                delivery_reply_qty = VALUES(delivery_reply_qty),
                available_qty = VALUES(available_qty),
                shortage_qty = VALUES(shortage_qty),
                ready_status = VALUES(ready_status),
                supplier_code = VALUES(supplier_code),
                supplier_name = VALUES(supplier_name),
                need_date = VALUES(need_date),
                calc_time = VALUES(calc_time),
                update_time = CURRENT_TIMESTAMP
            """,
            new SugarParameter("@StatDate", now.Date),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@SyncTime", now));

        // Step 4: 从 WorkOrdTemp 写入 dwd_material_shortage（先清理当天旧数据）
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM dwd_material_shortage WHERE stat_date = @StatDate",
            new SugarParameter("@StatDate", now.Date));

        var shortageCount = await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO dwd_material_shortage
            (tenant_id, stat_date, work_order, op_code, component_item_code, shortage_qty, shortage_reason, expected_supply_date, supplier_code, related_po_no, risk_level, calc_batch_id, calc_time)
            SELECT
                0,
                @StatDate,
                IFNULL(workord, ''),
                CAST(IFNULL(Op, 0) AS CHAR),
                COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
                IFNULL(shortage, 0),
                '当前可用量不足',
                COALESCE(jhshrq, hfjq, RecDate),
                supplier_number,
                PurOrds,
                CASE
                    WHEN IFNULL(shortage, 0) > IFNULL(QtyRequired, 0) THEN 'HIGH'
                    WHEN IFNULL(shortage, 0) > 0 THEN 'MEDIUM'
                    ELSE 'LOW'
                END,
                @BatchId,
                @SyncTime
            FROM WorkOrdTemp
            WHERE IFNULL(workord, '') <> ''
              AND IFNULL(shortage, 0) > 0
            """,
            new SugarParameter("@StatDate", now.Date),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@SyncTime", now));

        return new
        {
            enabled = true,
            batchId,
            workOrdTempCount,
            dwdCount,
            shortageCount,
            message = $"齐套/缺料刷新完成：执行存储过程获取 {workOrdTempCount} 条 WorkOrdTemp 数据，写入 DWD 齐套 {dwdCount} 条、缺料 {shortageCount} 条。"
        };
    }

    private async Task<bool> ShouldUseDwdAsync(WorkOrderMaterialReadinessListInput input)
    {
        if (!string.IsNullOrWhiteSpace(input.SeOrder))
            return false;

        var tenantId = _userManager.TenantId;
        if (tenantId > 0)
        {
            var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
            return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM dwd_material_readiness WHERE tenant_id=@TenantId LIMIT 1", pars) > 0;
        }
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM dwd_material_readiness LIMIT 1") > 0;
    }

    private async Task<object> GetDwdListAsync(WorkOrderMaterialReadinessListInput input, int page, int pageSize, int offset)
    {
        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        var tenantId = _userManager.TenantId;
        if (tenantId > 0)
        {
            where.Add("a.tenant_id=@TenantId");
            pars.Add(new SugarParameter("@TenantId", tenantId));
        }

        if (!string.IsNullOrWhiteSpace(input.Workord))
        {
            where.Add("a.work_order LIKE @Workord");
            pars.Add(new SugarParameter("@Workord", $"%{input.Workord.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BomNumber))
        {
            where.Add("a.parent_item_code = @BomNumber");
            pars.Add(new SugarParameter("@BomNumber", input.BomNumber.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("a.component_item_code = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierName))
        {
            where.Add("a.supplier_name LIKE @SupplierName");
            pars.Add(new SugarParameter("@SupplierName", $"%{input.SupplierName.Trim()}%"));
        }

        var fromSql = $"FROM dwd_material_readiness a WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var orderBy = BuildDwdOrderBy(input.SortField, input.SortOrder);
        var list = await _db.Ado.SqlQueryAsync<WorkOrderMaterialReadinessRow>(
            $"""
            SELECT
                CAST(a.id AS CHAR) AS RowNum,
                NULL AS SeOrder,
                NULL AS SePriority,
                a.work_order AS Workord,
                NULL AS Priority,
                NULL AS MorderType,
                NULL AS LotSerial,
                a.op_code AS Op,
                a.need_date AS OpOrdDate,
                a.parent_item_code AS BomNumber,
                a.parent_item_code AS Descr,
                NULL AS Descr1,
                NULL AS ProductionNumber,
                a.calc_time AS Checktime,
                a.parent_item_code AS PMBOM,
                NULL AS ItemType,
                'DWD' AS OwnerApplication,
                a.component_item_code AS ItemNum,
                a.component_item_name AS ComponentItemDescr,
                NULL AS ComponentItemDrawing,
                a.required_qty AS QtyRequired,
                NULL AS ComponentItemUM,
                NULL AS ComponentItemKittingTime,
                NULL AS QtyRec,
                NULL AS RecDate,
                a.shortage_qty AS Shortage,
                NULL AS QtyFrom,
                NULL AS FromDate,
                a.available_qty AS Zjsl,
                NULL AS Jykssj,
                a.in_transit_qty AS Ztsl,
                a.need_date AS Jhshrq,
                a.qc_pending_qty AS Jqsl,
                a.need_date AS Hfjq,
                a.delivery_reply_qty AS PurOrdQty,
                NULL AS PurOrdorddate,
                NULL AS PurOrds,
                NULL AS ContractDate,
                NULL AS ContractNO,
                a.supplier_code AS SupplierNumber,
                a.supplier_name AS SupplierName,
                NULL AS Buyer,
                NULL AS BuyerUser
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list, source = "dwd_material_readiness" };
    }

    private static string BuildDwdOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "rownum" => $"a.id {dir}",
            "workord" => $"a.work_order {dir}",
            "op" => $"a.op_code {dir}",
            "bomnumber" => $"a.parent_item_code {dir}",
            "itemnum" => $"a.component_item_code {dir}",
            "componentitemdescr" => $"a.component_item_name {dir}",
            "qtyrequired" => $"a.required_qty {dir}",
            "shortage" => $"a.shortage_qty {dir}",
            "zjsl" => $"a.available_qty {dir}",
            "ztsl" => $"a.in_transit_qty {dir}",
            "jhshrq" => $"a.need_date {dir}",
            "jqsl" => $"a.qc_pending_qty {dir}",
            "hfjq" => $"a.need_date {dir}",
            "purordqty" => $"a.delivery_reply_qty {dir}",
            "suppliernumber" => $"a.supplier_code {dir}",
            "suppliername" => $"a.supplier_name {dir}",
            _ => "a.stat_date DESC, a.work_order ASC, a.component_item_code ASC"
        };
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "rownum" => $"a.RowNum {dir}",
            "seorder" => $"a.SeOrder {dir}",
            "sepriority" => $"a.SePriority {dir}",
            "workord" => $"a.workord {dir}",
            "priority" => $"a.Priority {dir}",
            "mordertype" => $"a.morder_type {dir}",
            "lotserial" => $"a.LotSerial {dir}",
            "op" => $"a.Op {dir}",
            "oporddate" => $"a.OpOrdDate {dir}",
            "bomnumber" => $"a.bom_number {dir}",
            "descr" => $"a.Descr {dir}",
            "descr1" => $"a.Descr1 {dir}",
            "productionnumber" => $"a.production_number {dir}",
            "checktime" => $"a.checktime {dir}",
            "pmbom" => $"a.PMBOM {dir}",
            "itemtype" => $"a.ItemType {dir}",
            "ownerapplication" => $"a.OwnerApplication {dir}",
            "itemnum" => $"a.ItemNum {dir}",
            "componentitemdescr" => $"a.ComponentItemDescr {dir}",
            "componentitemdrawing" => $"a.ComponentItemDrawing {dir}",
            "qtyrequired" => $"a.QtyRequired {dir}",
            "componentitemum" => $"a.ComponentItemUM {dir}",
            "componentitemkittingtime" => $"a.ComponentItemKittingTime {dir}",
            "qtyrec" => $"a.QtyRec {dir}",
            "recdate" => $"a.RecDate {dir}",
            "shortage" => $"a.shortage {dir}",
            "qtyfrom" => $"a.QtyFrom {dir}",
            "fromdate" => $"a.FromDate {dir}",
            "zjsl" => $"a.zjsl {dir}",
            "jykssj" => $"a.jykssj {dir}",
            "ztsl" => $"a.ztsl {dir}",
            "jhshrq" => $"a.jhshrq {dir}",
            "jqsl" => $"a.jqsl {dir}",
            "hfjq" => $"a.hfjq {dir}",
            "purordqty" => $"a.PurOrdQty {dir}",
            "purordorddate" => $"a.PurOrdorddate {dir}",
            "purords" => $"a.PurOrds {dir}",
            "contractdate" => $"a.ContractDate {dir}",
            "contractno" => $"a.ContractNO {dir}",
            "suppliernumber" => $"a.supplier_number {dir}",
            "suppliername" => $"a.supplier_name {dir}",
            "buyer" => $"a.Buyer {dir}",
            "buyeruser" => $"a.BuyerUser {dir}",
            _ => "a.Priority ASC, a.workord ASC"
        };
    }
}