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;

    public WorkOrderProgressDashboardService(ISqlSugarClient db)
    {
        _db = db;
    }

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

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

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

    private static string BuildListBaseSql(string innerWhere, string C) => $"""
        SELECT
            wm.RecID AS Id,
            wm.WorkOrd AS WorkOrd,
            wm.Typed AS Typed,
            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
        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}
        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}
        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}
        """;
}