namespace Admin.NET.Plugin.AiDOP.Production; /// /// 工单执行进度看板(列表)📊 /// 路由前缀:/api/Production/work-order-progress/... /// [ApiDescriptionSettings(Order = 266, Description = "工单执行进度看板")] [Route("api/Production")] [AllowAnonymous] [NonUnify] public class WorkOrderProgressDashboardService : IDynamicApiController, ITransient { private readonly ISqlSugarClient _db; public WorkOrderProgressDashboardService(ISqlSugarClient db) { _db = db; } /// 工单执行进度看板分页列表 [DisplayName("工单执行进度看板列表")] [HttpGet("work-order-progress/list")] public async Task GetList([FromQuery] WorkOrderProgressDashboardListInput input) { const string C = "utf8mb4_general_ci"; var pars = new List(); var innerWhere = new List { "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( $"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} """; }