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

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 进出存查询 只读 list 服务。
///
/// 数据源：aidopdev.InvTransHist（库存交易流水），忠实复刻证据 sql.txt：
/// LEFT JOIN GeneralizedCodeMaster(TransType→名称)、LocationMaster(货位名)、ItemMaster(物料名/单位)，
/// 过滤 a.Domain=@Domain。进出存口径由 SQL 内定义（非脑补）：
///   期初 = BeginBalance；入库 = QtyChange&gt;0?QtyChange:0；出库 = QtyChange&lt;0?abs(QtyChange):0；
///   期末(结存) = BeginBalance+QtyChange。逐笔交易流水，BeginBalance 按行存储。
/// Domain 由 GeneralizedCodeMaster(SystemConfig/CompanyCode) 解析，默认 8010。
/// 排序 a.RecID DESC（PK，近似交易时间倒序，兼顾 1M 行分页性能与稳定）。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库存事务/状态流转。
/// 安全：InvTransHist / LocationMaster / GeneralizedCodeMaster 均含 tenant_id，主业务查询按当前租户强制过滤；
/// 租户只来自认证后的可信 JWT（严格解析），忽略前端 tenantId，杜绝匿名/跨租户可见。
/// </summary>
[ApiDescriptionSettings(Order = 303, Description = "进出存查询")]
[Route("api/S5InoutQuery")]
[NonUnify]
public class InoutQueryService : IDynamicApiController, ITransient
{
    /// <summary>主/系统租户哨兵：超管未选择目标租户时其 JWT TenantId 即此值，拒绝作为业务租户。</summary>
    private const long MainTenantId = 1300000000001L;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    /// <summary>严格可信租户解析：只来自认证后 JWT；无 Token(&lt;=0) 或超管未选主租户 → 拒绝；不读前端 tenantId、无默认回退。</summary>
    private long ResolveTenantOrThrow()
    {
        var tid = _userManager.TenantId;
        if (tid <= 0)
            throw Oops.Oh("无法确定当前租户，请重新登录或选择目标租户");
        if (_userManager.SuperAdmin && tid == MainTenantId)
            throw Oops.Oh("超级管理员操作前必须选择目标租户");
        return tid;
    }

    /// <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] InoutQueryListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();

        // 强制租户过滤：InvTransHist 含 tenant_id（约百万行、当前全 797），按可信 JWT 租户过滤，count 与 list 同 tid。
        var where = new List<string> { "a.Domain = @Domain", "a.tenant_id = @TenantId" };
        var pars = new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) };

        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("a.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{input.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("a.Loc = @Location");
            pars.Add(new SugarParameter("@Location", input.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.BatchNo))
        {
            where.Add("a.LotSerial LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{input.BatchNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkOrderNo))
        {
            where.Add("a.WorkOrd LIKE @WorkOrderNo");
            pars.Add(new SugarParameter("@WorkOrderNo", $"%{input.WorkOrderNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.TransType))
        {
            where.Add("a.TransType = @TransType");
            pars.Add(new SugarParameter("@TransType", input.TransType.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.CreateTime))
        {
            where.Add("a.CreateTime >= @CreateFrom AND a.CreateTime < DATE_ADD(@CreateFrom, INTERVAL 1 DAY)");
            pars.Add(new SugarParameter("@CreateFrom", $"{input.CreateTime.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.CreateUser))
        {
            where.Add("a.CreateUser LIKE @CreateUser");
            pars.Add(new SugarParameter("@CreateUser", $"%{input.CreateUser.Trim()}%"));
        }

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

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM InvTransHist a WHERE {whereSql}", pars);

        var list = await _db.Ado.SqlQueryAsync<InoutQueryListRow>(
            $"""
            SELECT
                a.CreateTime  AS TransTime,
                a.ItemNum     AS MaterialCode,
                i.Descr       AS MaterialName,
                a.Loc         AS Location,
                IFNULL(l.descr,'') AS LocationName,
                a.LotSerial   AS BatchNo,
                a.BeginBalance AS BeginQty,
                (CASE WHEN a.QtyChange > 0 THEN a.QtyChange ELSE 0 END) AS InQty,
                (CASE WHEN a.QtyChange < 0 THEN ABS(a.QtyChange) ELSE 0 END) AS OutQty,
                (a.BeginBalance + a.QtyChange) AS EndQty,
                i.UM          AS Unit,
                COALESCE(b.Comments, a.TransType) AS TransType,
                a.WorkOrd     AS WorkOrderNo,
                a.ShipperNum  AS DeliveryNo,
                a.ShipType    AS DeliveryType,
                a.Remark      AS Remark,
                a.CreateUser  AS CreateUser
            FROM InvTransHist a
            LEFT JOIN GeneralizedCodeMaster b ON a.Domain = b.Domain AND a.TransType = b.Val AND b.FldName = 'TransType'
            LEFT JOIN LocationMaster l ON a.Domain = l.Domain AND a.Loc = l.location
            LEFT JOIN ItemMaster i ON a.Domain = i.Domain AND a.ItemNum = i.ItemNum
            WHERE {whereSql}
            ORDER BY a.RecID DESC
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 货位下拉选项（LocationMaster.Location + Descr，只读）。
    /// </summary>
    [DisplayName("进出存查询货位选项")]
    [HttpGet("loc-options")]
    public async Task<object> GetLocationOptions()
    {
        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();
        // LocationMaster 含 tenant_id（797:80 / 824:34），强制按可信租户过滤。
        var list = await _db.Ado.SqlQueryAsync<InoutQueryLocationOption>(
            """
            SELECT location AS Val, descr AS Label
            FROM LocationMaster
            WHERE Domain = @Domain AND tenant_id = @TenantId AND IFNULL(location,'') <> ''
            ORDER BY location
            """,
            new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) });
        return list;
    }

    /// <summary>
    /// 交易类型下拉选项（GeneralizedCodeMaster TransType：代码 + 名称，只读）。
    /// </summary>
    [DisplayName("进出存查询交易类型选项")]
    [HttpGet("transtype-options")]
    public async Task<object> GetTransTypeOptions()
    {
        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();
        // GeneralizedCodeMaster 含 tenant_id（797/824），强制按可信租户过滤。
        var list = await _db.Ado.SqlQueryAsync<InoutQueryTransTypeOption>(
            """
            SELECT Val AS Val, Comments AS Label
            FROM GeneralizedCodeMaster
            WHERE FldName = 'TransType' AND Domain = @Domain AND tenant_id = @TenantId AND IFNULL(Val,'') <> ''
            ORDER BY Val
            """,
            new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) });
        return list;
    }
}