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 S3MdpSyncTransformService _mdpService;

    public WorkOrderMaterialReadinessService(ISqlSugarClient db, S3MdpSyncTransformService mdpService)
    {
        _db = db;
        _mdpService = mdpService;
    }

    [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>();

        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) {fromSql}", pars);
        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);

        var list = await _db.Ado.SqlQueryAsync<WorkOrderMaterialReadinessRow>(
            $"""
            SELECT
                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 result = await _mdpService.RefreshMaterialReadinessAsync();
        return new
        {
            enabled = true,
            batchId = result.BatchId,
            stdCount = result.StdCount,
            dwdCount = result.ReadinessCount,
            shortageCount = result.ShortageCount,
            message = "齐套/缺料刷新完成，已通过 S3 MDP 转换服务写入标准层和 DWD。"
        };
    }

    private async Task<int> CountMaterialReadinessStdAsync()
    {
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM mdp_std_material_readiness WHERE IFNULL(work_order, '') <> ''");
    }

    private async Task<int> UpsertMaterialReadinessDwdAsync(string batchId, DateTime now)
    {
        return 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
                s.tenant_id,
                @StatDate,
                s.work_order,
                s.op_code,
                s.item_code,
                s.component_item_code,
                IFNULL(i.Descr, ''),
                IFNULL(s.required_qty, 0),
                IFNULL(s.required_qty, 0),
                IFNULL(s.available_qty, 0),
                0,
                IFNULL(s.in_transit_qty, 0),
                IFNULL(s.incoming_qty, 0),
                IFNULL(s.available_qty, 0),
                IFNULL(s.shortage_qty, 0),
                CASE WHEN IFNULL(s.shortage_qty, 0) > 0 THEN 'SHORTAGE' ELSE 'READY' END,
                s.supplier_code,
                IFNULL(sp.SortName, ''),
                s.need_date,
                @BatchId,
                @Now
            FROM mdp_std_material_readiness s
            LEFT JOIN ItemMaster i ON s.component_item_code = i.ItemNum
            LEFT JOIN SuppMaster sp ON s.supplier_code = sp.Supp
            WHERE IFNULL(s.work_order, '') <> ''
            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_batch_id=VALUES(calc_batch_id),
                calc_time=VALUES(calc_time),
                update_time=CURRENT_TIMESTAMP
            """,
            new SugarParameter("@StatDate", now.Date),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@Now", now));
    }

    private async Task<int> DeleteCurrentShortageAsync(DateTime statDate)
    {
        return await _db.Ado.ExecuteCommandAsync(
            """
            DELETE s
            FROM dwd_material_shortage s
            JOIN (
                SELECT DISTINCT work_order
                FROM mdp_std_material_readiness
                WHERE IFNULL(work_order, '') <> ''
            ) w ON s.work_order = w.work_order
            WHERE s.stat_date = @StatDate
            """,
            new SugarParameter("@StatDate", statDate));
    }

    private async Task<int> InsertMaterialShortageAsync(string batchId, DateTime now)
    {
        return 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
                s.tenant_id,
                @StatDate,
                s.work_order,
                s.op_code,
                s.component_item_code,
                IFNULL(s.shortage_qty, 0),
                '标准层缺料数量大于 0',
                s.need_date,
                s.supplier_code,
                NULL,
                CASE
                    WHEN IFNULL(s.shortage_qty, 0) > IFNULL(s.required_qty, 0) THEN 'HIGH'
                    WHEN IFNULL(s.shortage_qty, 0) > 0 THEN 'MEDIUM'
                    ELSE 'LOW'
                END,
                @BatchId,
                @Now
            FROM mdp_std_material_readiness s
            WHERE IFNULL(s.work_order, '') <> ''
              AND IFNULL(s.shortage_qty, 0) > 0
            """,
            new SugarParameter("@StatDate", now.Date),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@Now", now));
    }

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

        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>();

        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"
        };
    }
}