AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Universal/UniversalWorkOrderService.cs

namespace Admin.NET.Plugin.AiDOP.Universal;

/// <summary>
/// 通用工单选择服务
/// </summary>
[ApiDescriptionSettings(Order = 281, Description = "通用-工单选择")]
[Route("api/Universal")]
[AllowAnonymous]
[NonUnify]
public class UniversalWorkOrderService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    [DisplayName("获取工单选择列表")]
    [HttpGet("work-order/page")]
    public async Task<object> GetPage([FromQuery] UniversalWorkOrderPageInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var tenantId = _userManager.TenantId;
        var where = new List<string>
        {
            "a.tenant_id = @TenantId",
            "a.Status!='c'",
            // 只展示：至少存在一个“外协工序+供应商”尚未生成 PW 采购单的工单
            @"EXISTS (
                SELECT 1
                FROM WorkOrdRouting r
                INNER JOIN RoutingOpDetail d ON r.Op = d.Op AND r.ItemNum = d.RoutingCode
                WHERE r.WorkOrd = a.WorkOrd
                  AND r.ProcessOut = 1
                  AND IFNULL(TRIM(d.SupplierCode),'') <> ''
                  AND NOT EXISTS (
                      SELECT 1 FROM PurOrdMaster p
                      WHERE p.WorkOrd = a.WorkOrd AND p.Supp = d.SupplierCode AND p.Potype = 'PW'
                  )
            )"
        };
        var pars = new List<SugarParameter> { new SugarParameter("@TenantId", tenantId) };
        if (!string.IsNullOrWhiteSpace(input.WorkOrd))
        {
            where.Add("a.WorkOrd LIKE @WorkOrd");
            pars.Add(new SugarParameter("@WorkOrd", $"%{input.WorkOrd.Trim()}%"));
        }

        var fromSql = $"""
            FROM WorkOrdMaster a
            LEFT JOIN ItemMaster b ON a.ItemNum=b.ItemNum
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<UniversalWorkOrderOutput>(
            $"""
            SELECT
                a.recid AS Id,
                a.Batch AS Batch,
                a.Drawing AS Drawing,
                a.Typed AS Typed,
                a.WorkOrd AS WorkOrd,
                a.OrdDate AS OrdDate,
                a.DueDate AS DueDate,
                a.ItemNum AS ItemNum,
                a.Project AS Project,
                a.QtyOrded AS QtyOrded,
                a.QtyCompleted AS QtyCompleted,
                a.Remark AS Remark,
                LOWER(a.Status) AS Status,
                a.WoTyped AS WoTyped,
                b.Descr AS Descr,
                b.Descr1 AS Descr1,
                a.lbrvar AS Lbrvar
            {fromSql}
            ORDER BY {BuildOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "workord" => $"a.WorkOrd {dir}",
            "itemnum" => $"a.ItemNum {dir}",
            "descr" => $"b.Descr {dir}",
            "orddate" => $"a.OrdDate {dir}",
            _ => "a.RecID DESC"
        };
    }
}