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;

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

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

        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()
    {
        try
        {
            await _db.Ado.ExecuteCommandAsync("CALL pr_MES_Report_DayWorkOrdDetailShortage_New('')");
            return new { message = "刷新成功" };
        }
        catch (Exception ex)
        {
            throw Oops.Oh($"刷新失败: {ex.Message}");
        }
    }

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