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 无 tenant_id，不做租户过滤。
/// </summary>
[ApiDescriptionSettings(Order = 303, Description = "进出存查询")]
[Route("api/S5InoutQuery")]
[AllowAnonymous]
[NonUnify]
public class InoutQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

    public InoutQueryService(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] InoutQueryListInput 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> { "a.Domain = @Domain" };
        var pars = new List<SugarParameter> { new("@Domain", domain) };

        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 domain = await ResolveDomainAsync();
        var list = await _db.Ado.SqlQueryAsync<InoutQueryLocationOption>(
            """
            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;
    }

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