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

using Microsoft.Extensions.Options;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

public interface IInventoryTransactionReader
{
    Task<InventoryTransactionPage> QueryPageAsync(
        InventoryTransactionQuery query,
        CancellationToken cancellationToken = default);
}

/// <summary>进出存只读标准层 mdp_std_inv_trans。</summary>
public sealed class StdInventoryTransactionReader : IInventoryTransactionReader, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly AidopInventoryOptions _opt;

    public StdInventoryTransactionReader(ISqlSugarClient db, IOptions<AidopInventoryOptions> opt)
    {
        _db = db;
        _opt = opt.Value;
    }

    public async Task<InventoryTransactionPage> QueryPageAsync(
        InventoryTransactionQuery query,
        CancellationToken cancellationToken = default)
    {
        var page = query.Page <= 0 ? 1 : query.Page;
        var pageSize = query.PageSize <= 0 ? 10 : query.PageSize;
        var offset = (page - 1) * pageSize;
        var sourceSystem = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var months = _opt.TransBootstrapMonths <= 0 ? 12 : _opt.TransBootstrapMonths;
        var fallbackHistoryFrom = DateTime.Now.Date.AddMonths(-months);

        var historyFrom = (await _db.Ado.SqlQueryAsync<DateTime?>(
            """
            SELECT MIN(history_from)
            FROM mdp_std_inv_trans
            WHERE tenant_id=@TenantId AND source_system=@SourceSystem AND domain=@Domain
            """,
            new List<SugarParameter>
            {
                new("@TenantId", query.TenantId),
                new("@SourceSystem", sourceSystem),
                new("@Domain", query.Domain)
            })).FirstOrDefault() ?? fallbackHistoryFrom;
        if (historyFrom < new DateTime(2000, 1, 1))
            historyFrom = fallbackHistoryFrom;

        var where = new List<string>
        {
            "a.tenant_id = @TenantId",
            "a.source_system = @SourceSystem",
            "a.domain = @Domain",
            "a.trans_time >= @HistoryFrom"
        };
        var pars = new List<SugarParameter>
        {
            new("@TenantId", query.TenantId),
            new("@SourceSystem", sourceSystem),
            new("@Domain", query.Domain),
            new("@HistoryFrom", historyFrom)
        };

        if (!string.IsNullOrWhiteSpace(query.MaterialCode))
        {
            where.Add("a.item_num LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{query.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.Location))
        {
            where.Add("a.location = @Location");
            pars.Add(new SugarParameter("@Location", query.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.BatchNo))
        {
            where.Add("IFNULL(a.lot_serial,'') LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{query.BatchNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.WorkOrderNo))
        {
            where.Add("IFNULL(a.work_ord,'') LIKE @WorkOrderNo");
            pars.Add(new SugarParameter("@WorkOrderNo", $"%{query.WorkOrderNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.TransType))
        {
            where.Add("a.trans_type = @TransType");
            pars.Add(new SugarParameter("@TransType", query.TransType.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.CreateTime))
        {
            where.Add("a.trans_time >= @CreateFrom AND a.trans_time < DATE_ADD(@CreateFrom, INTERVAL 1 DAY)");
            pars.Add(new SugarParameter("@CreateFrom", $"{query.CreateTime.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(query.CreateUser))
        {
            where.Add("IFNULL(a.create_user,'') LIKE @CreateUser");
            pars.Add(new SugarParameter("@CreateUser", $"%{query.CreateUser.Trim()}%"));
        }

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

        var asOf = (await _db.Ado.SqlQueryAsync<DateTime?>(
            $"SELECT MAX(as_of) FROM mdp_std_inv_trans a WHERE {whereSql}", pars)).FirstOrDefault()
            ?? DateTime.Now;

        var list = await _db.Ado.SqlQueryAsync<InventoryTransactionRow>(
            $"""
             SELECT
                 a.trans_time AS TransTime,
                 a.item_num AS MaterialCode,
                 i.Descr AS MaterialName,
                 a.location AS Location,
                 IFNULL(l.descr,'') AS LocationName,
                 a.lot_serial AS BatchNo,
                 a.begin_balance AS BeginQty,
                 (CASE WHEN a.qty_change > 0 THEN a.qty_change ELSE 0 END) AS InQty,
                 (CASE WHEN a.qty_change < 0 THEN ABS(a.qty_change) ELSE 0 END) AS OutQty,
                 a.end_balance AS EndQty,
                 i.UM AS Unit,
                 COALESCE(b.Comments, a.trans_type) AS TransType,
                 a.work_ord AS WorkOrderNo,
                 a.shipper_num AS DeliveryNo,
                 a.ship_type AS DeliveryType,
                 a.remark AS Remark,
                 a.create_user AS CreateUser
             FROM mdp_std_inv_trans a
             LEFT JOIN GeneralizedCodeMaster b
               ON a.domain = b.Domain AND a.trans_type = b.Val AND b.FldName = 'TransType'
              AND b.tenant_id = a.tenant_id
             LEFT JOIN LocationMaster l
               ON a.domain = l.Domain AND a.location = l.location AND l.tenant_id = a.tenant_id
             LEFT JOIN ItemMaster i
               ON a.domain = i.Domain AND a.item_num = i.ItemNum
             WHERE {whereSql}
             ORDER BY a.trans_time DESC, CAST(a.src_rec_id AS UNSIGNED) DESC
             LIMIT {pageSize} OFFSET {offset}
             """,
            pars);

        return new InventoryTransactionPage
        {
            Total = total,
            Page = page,
            PageSize = pageSize,
            List = list,
            AsOf = asOf,
            HistoryFrom = historyFrom
        };
    }
}