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

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 库存查询 只读 list 服务。
///
/// 数据源：aidopdev.LocationMaster ⋈ LocationDetail（现存量），忠实复刻旧系统存储过程
/// pr_WMS_GetMissedLocationQty 主口径：Domain 过滤 + (AvailStatusQty+Assay+FreezeQty)&gt;0，
/// 类型 LocType 由 LocationMaster.Typed 派生(VMI / Supp→O库)，join ItemMaster(名称/单位)、
/// 富集 MissedPrint(货架/版本/供应商，Status not in U/P/C、Location 非空、Qty&gt;0，按库位·物料·批次聚合)。
/// Domain 由 GeneralizedCodeMaster(SystemConfig/CompanyCode) 解析，默认 8010。
///
/// 忠实度说明：存储过程「多货架行拆分 + 按 MissedPrint 状态重算量」(原过程第 99-121 行) 仅在同一
/// 库位·物料·批次存在多货架时生效；当前 MissedPrint 为 0 行，其效果为零且无数据可验，本服务不复刻该拆分
/// （LocCount≤1 场景即 base + 富集，与现数据一致）。MissedPrint 回补后如需多货架拆分，另起批次复验。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库存事务/状态流转；LocationDetail 无 tenant_id，不做租户过滤。
/// </summary>
[ApiDescriptionSettings(Order = 300, Description = "库存查询")]
[Route("api/S5StockQuery")]
[AllowAnonymous]
[NonUnify]
public class StockQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    private sealed class GrandTotals
    {
        public decimal QtyOnHand { get; set; }
        public decimal Unrestricted { get; set; }
        public decimal Qc { get; set; }
        public decimal Frozen { get; set; }
    }

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

    // 派生 LocType 表达式（与存储过程一致）
    private const string LocTypeExpr = "CASE WHEN t.typed='VMI' THEN 'VMI' WHEN t.typed='Supp' THEN 'O库' ELSE '' END";

    /// <summary>
    /// 库存查询列表（只读分页查询）。
    /// </summary>
    [DisplayName("库存查询列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] StockQueryListInput 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>
        {
            "t.Domain = @Domain",
            "(IFNULL(d.AvailStatusQty,0)+IFNULL(d.Assay,0)+IFNULL(d.FreezeQty,0)) > 0",
        };
        var pars = new List<SugarParameter> { new("@Domain", domain) };

        if (!string.IsNullOrWhiteSpace(input.Type))
        {
            where.Add($"({LocTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", input.Type.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("d.Location = @Location");
            pars.Add(new SugarParameter("@Location", input.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("d.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{input.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BatchNo))
        {
            where.Add("d.LotSerial LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{input.BatchNo.Trim()}%"));
        }
        if (input.StockGt.HasValue)
        {
            where.Add("d.QtyOnHand > @StockGt");
            pars.Add(new SugarParameter("@StockGt", input.StockGt.Value));
        }
        if (input.UnrestrictedGt.HasValue)
        {
            where.Add("d.AvailStatusQty > @UnrestrictedGt");
            pars.Add(new SugarParameter("@UnrestrictedGt", input.UnrestrictedGt.Value));
        }
        if (input.QcStockGt.HasValue)
        {
            where.Add("d.Assay > @QcStockGt");
            pars.Add(new SugarParameter("@QcStockGt", input.QcStockGt.Value));
        }
        if (input.FrozenGt.HasValue)
        {
            where.Add("d.FreezeQty > @FrozenGt");
            pars.Add(new SugarParameter("@FrozenGt", input.FrozenGt.Value));
        }

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

        // 复刻存储过程 @tbLocDet 基础层（现存量，含派生 LocType 与库位名）
        var baseQuery =
            $"""
            SELECT d.Location, d.ItemNum, IFNULL(d.LotSerial,'') AS LotSerial,
                   d.QtyOnHand, d.AvailStatusQty, d.Assay, d.FreezeQty,
                   {LocTypeExpr} AS LocType, t.descr AS LocationName
            FROM LocationMaster t
            INNER JOIN LocationDetail d ON t.Domain = d.Domain AND t.location = d.Location
            WHERE {whereSql}
            """;

        // MissedPrint 富集（货架/版本/供应商），按库位·物料·批次聚合，1:1
        const string mpQuery =
            """
            SELECT Location, ItemNum, IFNULL(LotSerial,'') AS LotSerial,
                   MAX(Shelf) AS Shelf, MAX(LevelChar) AS LevelChar, MAX(Supply) AS Supply
            FROM MissedPrint
            WHERE Domain = @Domain AND IFNULL(Status,'') NOT IN ('U','P','C') AND IFNULL(Location,'') <> '' AND Qty > 0
            GROUP BY Location, ItemNum, IFNULL(LotSerial,'')
            """;

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

        var grand = (await _db.Ado.SqlQueryAsync<GrandTotals>(
            $"""
            SELECT IFNULL(SUM(n.QtyOnHand),0) AS QtyOnHand, IFNULL(SUM(n.AvailStatusQty),0) AS Unrestricted,
                   IFNULL(SUM(n.Assay),0) AS Qc, IFNULL(SUM(n.FreezeQty),0) AS Frozen
            FROM ({baseQuery}) n
            """, pars)).FirstOrDefault() ?? new GrandTotals();

        var list = await _db.Ado.SqlQueryAsync<StockQueryListRow>(
            $"""
            SELECT
                n.LocType         AS Type,
                n.Location        AS Location,
                n.LocationName    AS LocationName,
                n.ItemNum         AS MaterialCode,
                i.Descr           AS MaterialName,
                n.LotSerial       AS BatchNo,
                IFNULL(mp.Shelf,'')     AS Shelf,
                IFNULL(mp.LevelChar,'') AS Version,
                n.AvailStatusQty  AS UnrestrictedStock,
                n.Assay           AS QcStock,
                n.FreezeQty       AS FrozenStock,
                n.QtyOnHand       AS QtyOnHand,
                i.UM              AS Unit,
                IFNULL(mp.Supply,'')    AS Supplier
            FROM ({baseQuery}) n
            LEFT JOIN ItemMaster i ON i.Domain = @Domain AND n.ItemNum = i.ItemNum
            LEFT JOIN ({mpQuery}) mp ON mp.Location = n.Location AND mp.ItemNum = n.ItemNum AND mp.LotSerial = n.LotSerial
            ORDER BY n.Location, n.ItemNum, n.LotSerial
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new
        {
            total,
            page,
            pageSize,
            list,
            grandTotalQtyOnHand = grand.QtyOnHand,
            grandUnrestricted = grand.Unrestricted,
            grandQc = grand.Qc,
            grandFrozen = grand.Frozen,
        };
    }

    /// <summary>
    /// 类型下拉选项（派生 LocType：VMI / O库，只读）。
    /// </summary>
    [DisplayName("库存查询类型选项")]
    [HttpGet("type-options")]
    public async Task<object> GetTypeOptions()
    {
        var domain = await ResolveDomainAsync();
        var list = await _db.Ado.SqlQueryAsync<StockQueryTypeOption>(
            $"""
            SELECT DISTINCT {LocTypeExpr.Replace("t.typed", "typed")} AS Val
            FROM LocationMaster
            WHERE Domain = @Domain AND {LocTypeExpr.Replace("t.typed", "typed")} <> ''
            ORDER BY Val
            """,
            new List<SugarParameter> { new("@Domain", domain) });
        return list;
    }

    /// <summary>
    /// 库位下拉选项（LocationMaster.Location + Descr，只读）。
    /// </summary>
    [DisplayName("库存查询库位选项")]
    [HttpGet("location-options")]
    public async Task<object> GetLocationOptions()
    {
        var domain = await ResolveDomainAsync();
        var list = await _db.Ado.SqlQueryAsync<StockQueryLocationOption>(
            """
            SELECT location AS Val, descr AS Label
            FROM LocationMaster
            WHERE Domain = @Domain AND IFNULL(location,'') <> ''
            ORDER BY location
            """,
            new List<SugarParameter> { new("@Domain", domain) });
        return list;
    }
}