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

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>
/// 可执行生产日计划（PeriodSequenceDet + 排产/工单等）
/// 路由前缀：/api/Production/daily-plan/...
/// </summary>
[ApiDescriptionSettings(Order = 266, Description = "可执行生产日计划")]
[Route("api/Production")]
[AllowAnonymous]
[NonUnify]
public class ExecutableDailyPlanService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <summary>分页列表 GET /api/Production/daily-plan/list</summary>
    [DisplayName("可执行生产日计划列表")]
    [HttpGet("daily-plan/list")]
    public async Task<object> GetList([FromQuery] ExecutableDailyPlanListInput input)
    {
        var pars = new List<SugarParameter>();

        var innerWhere = new List<string>
        {
            "p.OrdQty > 0",
            "p.IsActive = 1",
            "DATE(p.PlanDate) >= CURDATE()",
            "LOWER(IFNULL(w.Status,'')) <> 'c'"
        };
        if (!string.IsNullOrWhiteSpace(input.Domain))
        {
            innerWhere.Add("p.`Domain` = @Domain");
            pars.Add(new SugarParameter("@Domain", input.Domain.Trim()));
        }

        if (!string.IsNullOrWhiteSpace(input.WorkOrds))
        {
            innerWhere.Add("(p.WorkOrds) LIKE @WorkOrds");
            pars.Add(new SugarParameter("@WorkOrds", $"%{input.WorkOrds.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            innerWhere.Add("(p.ItemNum) LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Batch))
        {
            innerWhere.Add("(w.Batch) LIKE @Batch");
            pars.Add(new SugarParameter("@Batch", $"%{input.Batch.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WoStatus))
        {
            innerWhere.Add("LOWER(IFNULL(w.Status,'')) = @WoStatus");
            pars.Add(new SugarParameter("@WoStatus", input.WoStatus.Trim().ToLowerInvariant()));
        }
        if (!string.IsNullOrWhiteSpace(input.IsLabor))
        {
            if (string.Equals(input.IsLabor, "yes", StringComparison.OrdinalIgnoreCase))
                innerWhere.Add("IFNULL(p.Status,'') <> ''");
            else if (string.Equals(input.IsLabor, "no", StringComparison.OrdinalIgnoreCase))
                innerWhere.Add("IFNULL(p.Status,'') = ''");
        }
        if (!string.IsNullOrWhiteSpace(input.WorkCtr))
        {
            innerWhere.Add("(sm.WorkCtr) = @WorkCtr");
            pars.Add(new SugarParameter("@WorkCtr", input.WorkCtr.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.OccupyEquipmentType))
        {
            innerWhere.Add("(IFNULL(sm.OccupyInternalEquipmentCode,'') LIKE @Occ OR IFNULL(sm.MoldTypeCode,'') LIKE @Occ)");
            var occ = $"%{input.OccupyEquipmentType.Trim()}%";
            pars.Add(new SugarParameter("@Occ", occ));
        }
        if (!string.IsNullOrWhiteSpace(input.OpStdOp))
        {
            innerWhere.Add("(CONCAT(CAST(sm.Op AS CHAR),' ',IFNULL(sm.OpDescr,'')) LIKE @OpStdOp");
            pars.Add(new SugarParameter("@OpStdOp", $"%{input.OpStdOp.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkStartFrom))
        {
            innerWhere.Add("sm.WorkStartTime >= @WorkStartFrom");
            pars.Add(new SugarParameter("@WorkStartFrom", input.WorkStartFrom.Trim()));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", innerWhere));
        var orderClause = ResolveListOrder(input.OrderBy, input.Sort);

        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<ExecutableDailyPlanListRow>(
            $"SELECT * FROM ({baseSql}) AS t ORDER BY {orderClause} LIMIT {input.PageSize} OFFSET {offset}", pars);

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

    /// <summary>下达 POST /api/Production/daily-plan/release</summary>
    [DisplayName("日计划下达")]
    [HttpPost("daily-plan/release")]
    public async Task<object> Release([FromBody] ExecutableDailyPlanReleaseInput input)
    {
        var domain = input.Domain.Trim();
        var ids = input.Ids.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
            .Select(s => long.TryParse(s, out var id) ? id : (long?)null)
            .Where(x => x.HasValue)
            .Select(x => x!.Value)
            .ToArray();
        if (ids.Length == 0)
            throw Oops.Oh("请选择有效的行主键");

        var account = _userManager.Account ?? "system";
        var now = DateTime.Now;
        const string releasedFlag = "Y";

        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE PeriodSequenceDet
            SET `Status` = @Flag,
                UpdateUser = @UpdateUser,
                UpdateTime = @UpdateTime
            WHERE RecID IN ({string.Join(",", ids)})
              AND `Domain` = @Domain
            """,
            new List<SugarParameter>
            {
                new("@Flag", releasedFlag),
                new("@UpdateUser", account),
                new("@UpdateTime", now),
                new("@Domain", domain)
            });

        return new { message = "下达成功", affected = n };
    }

    /// <summary>工单状态 PATCH POST /api/Production/daily-plan/patch-wo-status</summary>
    [DisplayName("可执行日计划-工单状态")]
    [HttpPost("daily-plan/patch-wo-status")]
    public async Task<object> PatchWoStatus([FromBody] ExecutableDailyPlanWoStatusInput input)
    {
        var st = input.Status.Trim().ToLowerInvariant();
        if (st is not ("w" or "r" or "c" or "p"))
            throw Oops.Oh("状态不合法");

        var n = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET `Status` = @St,
                UpdateTime = @Now,
                UpdateUser = @User
            WHERE WorkOrd = @WorkOrd AND `Domain` = @Domain
            """,
            new List<SugarParameter>
            {
                new("@St", st),
                new("@Now", DateTime.Now),
                new("@User", _userManager.Account ?? "system"),
                new("@WorkOrd", input.WorkOrd.Trim()),
                new("@Domain", input.Domain.Trim())
            });
        if (n == 0)
            throw Oops.Oh("工单不存在");
        return new { message = "状态已更新" };
    }

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            IF(p.CompQty > 0, p.CompQty, IF(IFNULL(comPD.CompQty, 0) > 0, comPD.CompQty, 0)) AS CompQty,
            IF(p.RejectQty > 0, p.RejectQty, IF(IFNULL(comPD.RejectQty, 0) > 0, comPD.RejectQty, 0)) AS RejectQty,
            sm.WorkStartTime AS WorkStartTime,
            sm.DeviceAllocationCount AS DeviceAllocationCount,
            p.RecID AS Id,
            sm.WorkEndTime AS WorkEndTime,
            pd.InternalEquipmentCode AS Line,
            line.line_describe AS LineDescribe,
            p.WorkOrds AS WorkOrds,
            p.ItemNum AS ItemNum,
            i.Descr AS Descr,
            i.PkgCode AS Languages,
            w.LotSerial AS LotSerial,
            CASE WHEN IFNULL(p.Status,'') = '' THEN '否' ELSE '是' END AS IsLabor,
            IFNULL(r.MachBdnRate, pd.Rate) AS Rate,
            p.Op AS Op,
            p.PlanDate AS PlanDate,
            p.OrdQty AS OrdQty,
            pd.RunCrew AS RunCrew,
            LOWER(w.Status) AS Status,
            w.Batch AS Batch,
            p.Status AS PStatus,
            m.MaterialSituation AS MaterialSituation,
            CASE WHEN DATE_FORMAT(p.PlanDate,'%Y-%m-%d') = DATE_FORMAT(CURDATE(),'%Y-%m-%d') THEN 1 ELSE 0 END AS IsUpdateQty,
            CASE WHEN DATE_FORMAT(p.PlanDate,'%Y-%m-%d') > DATE_FORMAT(CURDATE(),'%Y-%m-%d') THEN 1 ELSE 0 END AS IsUpdateTime,
            sm.SetupTime AS SetupTime,
            sm.SetupTime * 60 AS SetupTimeInMinutes,
            pd.Site AS Site,
            CONCAT(IFNULL(sm.WorkCtr,''), '  ', IFNULL(wm.Descr,'')) AS Descr0,
            w.DueDate AS DueDate,
            pd.OpType AS OpType,
            CASE WHEN pd.OpType = 'M'
                 THEN TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0)
                 ELSE 0 END AS DeviceWorkHours,
            CASE WHEN IFNULL(pd.OpType,'') <> 'M'
                 THEN TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0)
                 ELSE TIMESTAMPDIFF(MINUTE, sm.WorkStartTime, sm.WorkEndTime) / 60.0 * IFNULL(sm.DeviceAllocationCount, 0) * IFNULL(pd.StandardStaffCount, 0) END AS ManWorkHours,
            CASE WHEN sm.WorkEndTime > w.DueDate THEN 'WorkOrds:#FF7F50'
                 WHEN m.MaterialSituation = '仓库不齐套' AND LOWER(IFNULL(w.Status,'')) = 'r' THEN 'WorkOrds:#FCDD18'
                 ELSE 'WorkOrds:#FFFFFF;' END AS Background,
            CONCAT(CAST(sm.Op AS CHAR), ' ', IFNULL(sm.OpDescr,'')) AS OpStdOp,
            IF(IFNULL(r.RunCrew, 0) > 0, r.RunCrew, pd.StandardStaffCount) AS StandardStaffCount,
            i.Drawing AS Drawing,
            w.QtyOrded AS QtyOrded,
            w.ReleaseDate AS ReleaseDate,
            (
                SELECT GROUP_CONCAT(DISTINCT el.MachineName ORDER BY el.MachineName SEPARATOR '/')
                FROM EquipmentList el
                WHERE pd.InternalEquipmentCode IS NOT NULL
                  AND (
                    el.InternalEquipmentCode = pd.InternalEquipmentCode
                    OR FIND_IN_SET(el.InternalEquipmentCode, REPLACE(REPLACE(IFNULL(pd.InternalEquipmentCode,''), '+', ','), '/', ',')) > 0
                  )
            ) AS MachineName,
            IF(w.Rev IS NULL OR w.Rev = '', i.Rev, w.Rev) AS Rev,
            w.Eff AS Eff,
            p.`Domain` AS Domain
        FROM PeriodSequenceDet p
        LEFT JOIN LineMaster line ON p.Line = line.Line AND p.`Domain` = line.`Domain`
        LEFT JOIN ItemMaster i ON p.ItemNum = i.ItemNum AND p.`Domain` = i.`Domain`
        LEFT JOIN WorkOrdMaster w ON p.WorkOrds = w.WorkOrd AND p.`Domain` = w.`Domain`
        LEFT JOIN WorkOrdRouting r ON p.Op = r.OP AND p.ItemNum = r.ItemNum AND p.WorkOrds = r.WorkOrd
        LEFT JOIN mes_morder m ON w.WorkOrd = m.morder_no AND w.`Domain` = m.factory_id
        LEFT JOIN ProdLineDetail pd ON p.`Domain` = pd.`Domain` AND p.ItemNum = pd.Part AND p.Line = pd.Line AND p.Op = pd.Op
        LEFT JOIN ScheduleResultOpMaster sm ON sm.`Domain` = p.`Domain` AND p.UDate2 = sm.WorkActivateTime AND p.ItemNum = sm.ItemNum AND sm.WorkOrd = p.WorkOrds AND sm.Op = p.Op
        LEFT JOIN WorkCtrMaster wm ON wm.WorkCtr = sm.WorkCtr AND wm.`Domain` = sm.`Domain`
        LEFT JOIN (
            SELECT SUM(CompQty) AS CompQty, SUM(RejectQty) AS RejectQty, PlanDate, `Period` AS UInt2, Op, WorkOrds, ItemNum
            FROM PeriodSequenceDet
            WHERE CompQty > 0
            GROUP BY PlanDate, `Period`, Op, WorkOrds, ItemNum
        ) comPD ON DATE_FORMAT(p.PlanDate,'%Y-%m-%d') = DATE_FORMAT(comPD.PlanDate,'%Y-%m-%d')
            AND p.`Period` = comPD.UInt2 AND p.Op = comPD.Op AND p.WorkOrds = comPD.WorkOrds AND p.ItemNum = comPD.ItemNum
        WHERE {innerWhere}
        """;

    private static string ResolveListOrder(string? orderBy, string? sort)
    {
        var desc = string.Equals(sort?.Trim(), "desc", StringComparison.OrdinalIgnoreCase);
        var dir = desc ? "DESC" : "ASC";
        var key = orderBy?.Trim().ToLowerInvariant();
        var col = key switch
        {
            "workords" => "t.WorkOrds",
            "itemnum" => "t.ItemNum",
            "plandate" => "t.PlanDate",
            "status" => "t.Status",
            "workstarttime" => "t.WorkStartTime",
            "lotserial" => "t.LotSerial",
            _ => "t.PlanDate"
        };
        return $"{col} {dir}, t.Id DESC";
    }

    private sealed class ExecutableDailyPlanListRow
    {
        public decimal? CompQty { get; set; }
        public decimal? RejectQty { get; set; }
        public DateTime? WorkStartTime { get; set; }
        public int? DeviceAllocationCount { get; set; }
        public long Id { get; set; }
        public DateTime? WorkEndTime { get; set; }
        public string? Line { get; set; }
        public string? LineDescribe { get; set; }
        public string? WorkOrds { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Languages { get; set; }
        public string? LotSerial { get; set; }
        public string? IsLabor { get; set; }
        public decimal? Rate { get; set; }
        public int? Op { get; set; }
        public DateTime? PlanDate { get; set; }
        public decimal? OrdQty { get; set; }
        public decimal? RunCrew { get; set; }
        public string? Status { get; set; }
        public string? Batch { get; set; }
        public string? PStatus { get; set; }
        public string? MaterialSituation { get; set; }
        public int IsUpdateQty { get; set; }
        public int IsUpdateTime { get; set; }
        public decimal? SetupTime { get; set; }
        public decimal? SetupTimeInMinutes { get; set; }
        public string? Site { get; set; }
        public string? Descr0 { get; set; }
        public DateTime? DueDate { get; set; }
        public string? OpType { get; set; }
        public decimal? DeviceWorkHours { get; set; }
        public decimal? ManWorkHours { get; set; }
        public string? Background { get; set; }
        public string? OpStdOp { get; set; }
        public decimal? StandardStaffCount { get; set; }
        public string? Drawing { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? ReleaseDate { get; set; }
        public string? MachineName { get; set; }
        public string? Rev { get; set; }
        public DateTime? Eff { get; set; }
        public string? Domain { get; set; }
    }
}