AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/MaterialWarehouse/PendingInspectionService.cs

using Admin.NET.Plugin.AiDOP.MaterialWarehouse.Dto;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 暂收在检列表 只读 list 服务。
///
/// 数据源：aidopdev.MissedPrint（Status='I' 暂收待检、Qty>0、Location 空），忠实复刻旧系统存储过程
/// pr_WMS_GetMissedTempQty：LEFT JOIN qms_qcp_inspecapplyn/qms_qcp_insappnentry 取检验状态，按
/// Supply/PurOrd/PurLine/ShipperNbr/ItemNum/LotSerial 聚合(MAX Shelf/SUM Qty/MAX CheckNo/MAX FINSPECTSTATUS)，
/// 再 LEFT JOIN ItemMaster(描述/单位)、SuppMaster(简称)，clfs='3' 追加“-挑选使用”。
/// Domain 由 GeneralizedCodeMaster(SystemConfig/CompanyCode) 解析，默认 8010。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库存事务/状态流转；MissedPrint 无 tenant_id，不做租户过滤。
/// </summary>
[ApiDescriptionSettings(Order = 301, Description = "暂收在检列表")]
[Route("api/S5PendingInspection")]
[AllowAnonymous]
[NonUnify]
public class PendingInspectionService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>解析公司域（GeneralizedCodeMaster SystemConfig/CompanyCode，默认 8010）。</summary>
    private async Task<string> ResolveDomainAsync()
    {
        var list = await _db.Ado.SqlQueryAsync<string>(
            "SELECT Domain FROM GeneralizedCodeMaster WHERE FldName='SystemConfig' AND Val='CompanyCode' ORDER BY RecID LIMIT 1");
        var domain = list.FirstOrDefault();
        return string.IsNullOrWhiteSpace(domain) ? "8010" : domain;
    }

    /// <summary>
    /// 暂收在检列表（只读分页查询）。
    /// </summary>
    [DisplayName("暂收在检列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] PendingInspectionListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var domain = await ResolveDomainAsync();

        var where = new List<string>
        {
            "m.Domain = @Domain",
            "m.Status = 'I'",
            "m.Qty > 0",
            "IFNULL(m.Location,'') = ''",
        };
        var pars = new List<SugarParameter> { new("@Domain", domain) };

        if (!string.IsNullOrWhiteSpace(input.DemandNo))
        {
            where.Add("m.PurOrd LIKE @DemandNo");
            pars.Add(new SugarParameter("@DemandNo", $"%{input.DemandNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shelf))
        {
            where.Add("m.Shelf LIKE @Shelf");
            pars.Add(new SugarParameter("@Shelf", $"%{input.Shelf.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BatchNo))
        {
            where.Add("m.LotSerial LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{input.BatchNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("m.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{input.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.InspectStatus))
        {
            where.Add("d.FINSPECTSTATUS = @InspectStatus");
            pars.Add(new SugarParameter("@InspectStatus", input.InspectStatus.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("m.Supply = @Supplier");
            pars.Add(new SugarParameter("@Supplier", input.Supplier.Trim()));
        }

        var whereSql = string.Join(" AND ", where);

        // 复刻存储过程 @tbLocDet 聚合层
        var groupedInner =
            $"""
            SELECT m.Supply, m.PurOrd, m.PurLine, m.ItemNum, m.ShipperNbr, m.LotSerial,
                   MAX(m.Shelf) AS Shelf, SUM(m.Qty) AS Qty, MAX(m.CheckNo) AS CheckNo,
                   MAX(IFNULL(d.FINSPECTSTATUS,'')) AS FINSPECTSTATUS
            FROM MissedPrint m
            LEFT JOIN qms_qcp_inspecapplyn q ON m.CheckNo = q.FBILLNO
            LEFT JOIN qms_qcp_insappnentry d ON q.id = d.glid
            WHERE {whereSql}
            GROUP BY m.Supply, m.PurOrd, m.PurLine, m.ShipperNbr, m.ItemNum, m.LotSerial
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({groupedInner}) t", pars);

        var grandList = await _db.Ado.SqlQueryAsync<decimal>($"SELECT IFNULL(SUM(t.Qty),0) FROM ({groupedInner}) t", pars);
        var grandTotalQty = grandList.FirstOrDefault();

        var list = await _db.Ado.SqlQueryAsync<PendingInspectionListRow>(
            $"""
            SELECT
                t.PurOrd     AS DemandNo,
                t.PurLine    AS DemandLineNo,
                t.ItemNum    AS MaterialCode,
                i.Descr      AS MaterialName,
                t.LotSerial  AS BatchNo,
                t.Qty        AS Qty,
                i.UM         AS Unit,
                t.Shelf      AS PendingShelf,
                t.ShipperNbr AS DeliveryNo,
                t.CheckNo    AS InspectNo,
                CONCAT(t.FINSPECTSTATUS,
                    CASE WHEN (SELECT MAX(ib.clfs) FROM qms_qcp_inspbill ib WHERE ib.lydjbh = t.CheckNo) = '3'
                         THEN '-挑选使用' ELSE '' END) AS InspectStatus,
                t.Supply     AS Supplier,
                s.SortName   AS SupplierShortName
            FROM ({groupedInner}) t
            LEFT JOIN ItemMaster i ON i.Domain = @Domain AND t.ItemNum = i.ItemNum
            LEFT JOIN SuppMaster s ON s.Domain = @Domain AND t.Supply = s.Supp
            ORDER BY t.Supply, t.PurOrd, t.PurLine
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 检验状态下拉选项（qms_qcp_insappnentry.FINSPECTSTATUS distinct，只读）。
    /// </summary>
    [DisplayName("暂收在检检验状态选项")]
    [HttpGet("status-options")]
    public async Task<object> GetStatusOptions()
    {
        var list = await _db.Ado.SqlQueryAsync<PendingInspectionStatusOption>(
            """
            SELECT DISTINCT FINSPECTSTATUS AS Val
            FROM qms_qcp_insappnentry
            WHERE IFNULL(FINSPECTSTATUS,'') <> ''
            ORDER BY FINSPECTSTATUS
            """);
        return list;
    }

    /// <summary>
    /// 供应商下拉选项（暂收在检集合内 distinct Supply + SuppMaster.SortName，只读）。
    /// </summary>
    [DisplayName("暂收在检供应商选项")]
    [HttpGet("supplier-options")]
    public async Task<object> GetSupplierOptions()
    {
        var domain = await ResolveDomainAsync();
        var list = await _db.Ado.SqlQueryAsync<PendingInspectionSupplierOption>(
            """
            SELECT DISTINCT m.Supply AS Val, s.SortName AS Label
            FROM MissedPrint m
            LEFT JOIN SuppMaster s ON s.Domain = @Domain AND m.Supply = s.Supp
            WHERE m.Domain = @Domain AND m.Status = 'I' AND m.Qty > 0 AND IFNULL(m.Location,'') = ''
              AND IFNULL(m.Supply,'') <> ''
            ORDER BY m.Supply
            """,
            new List<SugarParameter> { new("@Domain", domain) });
        return list;
    }
}