AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Production/WorkOrderProgressDashboardService.cs

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 工单执行进度看板（列表）📊
/// 路由前缀：<c>/api/Production/work-order-progress/...</c>
/// </summary>
[ApiDescriptionSettings(Order = 266, Description = "工单执行进度看板")]
[Route("api/Production")]
[AllowAnonymous]
[NonUnify]
public class WorkOrderProgressDashboardService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <summary>工单执行进度看板分页列表</summary>
    [DisplayName("工单执行进度看板列表")]
    [HttpGet("work-order-progress/list")]
    public async Task<object> GetList([FromQuery] WorkOrderProgressDashboardListInput input)
    {
        const string C = "utf8mb4_general_ci";
        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        var innerWhere = new List<string>
        {
            "IFNULL(wm.Status,'') <> ''",
            "wm.tenant_id = @TenantId"
        };

        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            innerWhere.Add($"(wm.WorkOrd COLLATE {C}) LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            innerWhere.Add($"(wm.ItemNum COLLATE {C}) = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }

        if (!string.IsNullOrWhiteSpace(input.OrdDateFrom))
        {
            innerWhere.Add("DATE(wm.OrdDate) >= @OrdDateFrom");
            pars.Add(new SugarParameter("@OrdDateFrom", input.OrdDateFrom.Trim()));
        }

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

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

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

    /// <summary>看板侧工序行匹配：工单号 + 主表租户。</summary>
    private static string KanbanRoutingMatch(string C) =>
        $"r.WorkOrd COLLATE {C} = wm.WorkOrd COLLATE {C} AND NULLIF(r.tenant_id, 0) = wm.tenant_id";

    private static string BuildListBaseSql(string innerWhere, string C) => $"""
        SELECT
            wm.RecID AS Id,
            wm.WorkOrd AS WorkOrd,
            wm.Typed AS Typed,
            wm.`Domain` AS `Domain`,
            defaultLine.Workshop AS Workshop,
            wm.OrdDate AS OrdDate,
            wm.DueDate AS DueDate,
            wm.ItemNum AS ItemNum,
            im.Descr AS Descr,
            im.Descr1 AS Descr1,
            IFNULL(wm.QtyOrded, 0) AS QtyOrded,
            IFNULL(wm.QtyCompleted, 0) AS QtyCompleted,
            IFNULL(wm.QtyOrded, 0) - IFNULL(wm.QtyCompleted, 0) AS NoCompleted,
            mo.MaterialSituation AS MaterialSituation,
            LOWER(wm.Status) AS Status,
            CASE
                WHEN IFNULL(wm.QtyOrded, 0) = 0 THEN 0
                ELSE ROUND(100.0 * IFNULL(wm.QtyCompleted, 0) / wm.QtyOrded, 4)
            END AS Progress,
            {WorkOrderExecutionProgressSql.Columns("wm", "curOp", "lastOp", "opAgg")}
        FROM WorkOrdMaster wm
        LEFT JOIN ItemMaster im
            ON wm.ItemNum COLLATE {C} = im.ItemNum COLLATE {C}
            AND wm.`Domain` COLLATE {C} = im.`Domain` COLLATE {C}
            AND (im.tenant_id = wm.tenant_id OR wm.tenant_id IS NULL)
        LEFT JOIN mes_morder mo
            ON wm.WorkOrd COLLATE {C} = mo.morder_no COLLATE {C}
            AND wm.`Domain` COLLATE {C} = mo.factory_id COLLATE {C}
            AND mo.tenant_id = wm.tenant_id
        {WorkOrderExecutionProgressSql.Laterals(KanbanRoutingMatch(C))}
        LEFT JOIN LATERAL (
            SELECT psd0.`Line` AS `Line`
            FROM PeriodSequenceDet psd0
            WHERE psd0.ItemNum COLLATE {C} = wm.ItemNum COLLATE {C}
              AND psd0.`Domain` COLLATE {C} = wm.`Domain` COLLATE {C}
            LIMIT 1
        ) AS psd ON TRUE
        LEFT JOIN LATERAL (
            SELECT lm.Workshop AS Workshop
            FROM LineMaster lm
            WHERE lm.`Line` COLLATE {C} = psd.`Line` COLLATE {C}
              AND lm.`Domain` COLLATE {C} = wm.`Domain` COLLATE {C}
            LIMIT 1
        ) AS defaultLine ON TRUE
        WHERE {innerWhere}
        """;
}