AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/MaterialWarehouse/InventoryPosting/Inventory/InventorySqlWriter.cs

using Admin.NET.Plugin.AiDOP.MaterialWarehouse.InventoryPosting.Entity;
using SqlSugar;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse.InventoryPosting.Inventory;

/// <summary>
/// DOP-L3 库存加锁加载 + 写入的原始 SQL 工具（**不管理事务**；由调用方在既有事务内使用）。
/// 供独立 C5 UoW 与 IQC 收货外层 UoW 复用，避免重复 SQL。**Phase 5B 未实库验证**（目标库 aidopdev）。
/// 并发策略：既有行 FOR UPDATE 锁定 + 相对 UPDATE(set x=x+delta) 防 lost-update；首建用 INSERT…ON DUPLICATE KEY(noop)。
/// </summary>
public static class InventorySqlWriter
{
    public static async Task<InventoryContext> LoadLockedAsync(ISqlSugarClient db, IReadOnlyList<InventoryTransactionCommand> cmds)
    {
        var ctx = new InventoryContext();
        if (cmds == null || cmds.Count == 0) return ctx;

        var tenantId = cmds[0].TenantId;
        var domain = cmds[0].DomainCode ?? string.Empty;
        var items = cmds.Select(c => c.ItemNum).Distinct(StringComparer.Ordinal).ToList();
        var locations = cmds.Select(c => c.Location).Distinct(StringComparer.Ordinal).ToList();

        foreach (var it in await db.Ado.SqlQueryAsync<string>(
                     "SELECT ItemNum FROM ItemMaster WHERE Domain=@d AND ItemNum IN (@items)",
                     new SugarParameter("@d", domain), new SugarParameter("@items", items)))
            ctx.Items.Add(it);
        foreach (var lo in await db.Ado.SqlQueryAsync<string>(
                     "SELECT Location FROM LocationMaster WHERE Domain=@d AND Location IN (@locs)",
                     new SugarParameter("@d", domain), new SugarParameter("@locs", locations)))
            ctx.Locations.Add(lo);

        var invRows = await db.Ado.SqlQueryAsync<InvRow>(
            """
            SELECT item_num AS ItemNum, location AS Location, qty_on_hand AS QtyOnHand,
                   avail_status_qty AS AvailStatusQty, assay_qty AS AssayQty, freeze_qty AS FreezeQty
            FROM ado_inventory_master
            WHERE tenant_id=@t AND domain_code=@d AND item_num IN (@items) AND location IN (@locs)
            FOR UPDATE
            """,
            new SugarParameter("@t", tenantId), new SugarParameter("@d", domain),
            new SugarParameter("@items", items), new SugarParameter("@locs", locations));
        foreach (var r in invRows)
            ctx.InvMasters[InventoryContext.InvKey(r.ItemNum, r.Location)] =
                new InventoryBalance { QtyOnHand = r.QtyOnHand, AvailStatusQty = r.AvailStatusQty, Assay = r.AssayQty, FreezeQty = r.FreezeQty };

        var detRows = await db.Ado.SqlQueryAsync<DetailRow>(
            """
            SELECT item_num AS ItemNum, location AS Location, lot_serial AS LotSerial, refs AS Refs,
                   qty_on_hand AS QtyOnHand, avail_status_qty AS AvailStatusQty, assay_qty AS AssayQty, freeze_qty AS FreezeQty
            FROM ado_inventory_location_detail
            WHERE tenant_id=@t AND domain_code=@d AND item_num IN (@items) AND location IN (@locs)
            FOR UPDATE
            """,
            new SugarParameter("@t", tenantId), new SugarParameter("@d", domain),
            new SugarParameter("@items", items), new SugarParameter("@locs", locations));
        foreach (var r in detRows)
            ctx.LocationDetails[InventoryContext.DetailKey(r.ItemNum, r.Location, r.LotSerial, r.Refs)] =
                new InventoryBalance { QtyOnHand = r.QtyOnHand, AvailStatusQty = r.AvailStatusQty, Assay = r.AssayQty, FreezeQty = r.FreezeQty };

        foreach (var s in await db.Ado.SqlQueryAsync<StateRow>(
                     "SELECT item_num AS ItemNum, default_location AS DefaultLocation FROM ado_item_location_state WHERE tenant_id=@t AND domain_code=@d AND item_num IN (@items) FOR UPDATE",
                     new SugarParameter("@t", tenantId), new SugarParameter("@d", domain), new SugarParameter("@items", items)))
            ctx.ItemDefaultLocation[s.ItemNum] = s.DefaultLocation ?? string.Empty;

        return ctx;
    }

    public static async Task WriteAsync(ISqlSugarClient db, InventoryWriteSet ws)
    {
        if (ws == null) throw new ArgumentNullException(nameof(ws));
        var now = DateTime.Now;

        foreach (var w in ws.InvMasterWrites)
        {
            await db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_inventory_master (id,tenant_id,domain_code,item_num,location,qty_on_hand,avail_status_qty,assay_qty,freeze_qty,is_active,create_time,create_user)
                VALUES (@id,@t,@d,@i,@l,0,0,0,0,1,@now,@u)
                ON DUPLICATE KEY UPDATE update_time=@now
                """,
                new SugarParameter("@id", YitIdHelper.NextId()), new SugarParameter("@t", w.TenantId), new SugarParameter("@d", w.DomainCode ?? ""),
                new SugarParameter("@i", w.ItemNum), new SugarParameter("@l", w.Location), new SugarParameter("@now", now), new SugarParameter("@u", w.User ?? ""));
            await db.Ado.ExecuteCommandAsync(
                """
                UPDATE ado_inventory_master
                SET qty_on_hand=qty_on_hand+@dq, avail_status_qty=avail_status_qty+@da, assay_qty=assay_qty+@das, freeze_qty=freeze_qty+@df,
                    last_rct_time=@now, is_active=1, update_time=@now, update_user=@u
                WHERE tenant_id=@t AND domain_code=@d AND item_num=@i AND location=@l
                """,
                new SugarParameter("@dq", w.Delta.DeltaQtyOnHand), new SugarParameter("@da", w.Delta.DeltaAvailStatusQty),
                new SugarParameter("@das", w.Delta.DeltaAssay), new SugarParameter("@df", w.Delta.DeltaFreezeQty),
                new SugarParameter("@now", now), new SugarParameter("@u", w.User ?? ""),
                new SugarParameter("@t", w.TenantId), new SugarParameter("@d", w.DomainCode ?? ""),
                new SugarParameter("@i", w.ItemNum), new SugarParameter("@l", w.Location));
        }

        foreach (var w in ws.ItemLocationWrites)
            await db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_item_location_state (id,tenant_id,domain_code,item_num,default_location,create_time,create_user)
                VALUES (@id,@t,@d,@i,@loc,@now,@u)
                ON DUPLICATE KEY UPDATE default_location=IF(IFNULL(default_location,'')='',@loc,default_location), update_time=@now, update_user=@u
                """,
                new SugarParameter("@id", YitIdHelper.NextId()), new SugarParameter("@t", w.TenantId), new SugarParameter("@d", w.DomainCode ?? ""),
                new SugarParameter("@i", w.ItemNum), new SugarParameter("@loc", w.DefaultLocation), new SugarParameter("@now", now), new SugarParameter("@u", w.User ?? ""));

        foreach (var w in ws.LocationDetailWrites)
        {
            await db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_inventory_location_detail (id,tenant_id,domain_code,item_num,location,lot_serial,refs,qty_on_hand,avail_status_qty,assay_qty,freeze_qty,create_time,create_user)
                VALUES (@id,@t,@d,@i,@l,@lot,@refs,0,0,0,0,@now,@u)
                ON DUPLICATE KEY UPDATE update_time=@now
                """,
                new SugarParameter("@id", YitIdHelper.NextId()), new SugarParameter("@t", w.TenantId), new SugarParameter("@d", w.DomainCode ?? ""),
                new SugarParameter("@i", w.ItemNum), new SugarParameter("@l", w.Location), new SugarParameter("@lot", w.LotSerial ?? ""), new SugarParameter("@refs", w.Refs ?? ""),
                new SugarParameter("@now", now), new SugarParameter("@u", w.User ?? ""));
            await db.Ado.ExecuteCommandAsync(
                """
                UPDATE ado_inventory_location_detail
                SET qty_on_hand=qty_on_hand+@dq, avail_status_qty=avail_status_qty+@da, assay_qty=assay_qty+@das, freeze_qty=freeze_qty+@df, update_time=@now, update_user=@u
                WHERE tenant_id=@t AND domain_code=@d AND item_num=@i AND location=@l AND lot_serial=@lot AND refs=@refs
                """,
                new SugarParameter("@dq", w.Delta.DeltaQtyOnHand), new SugarParameter("@da", w.Delta.DeltaAvailStatusQty),
                new SugarParameter("@das", w.Delta.DeltaAssay), new SugarParameter("@df", w.Delta.DeltaFreezeQty),
                new SugarParameter("@now", now), new SugarParameter("@u", w.User ?? ""),
                new SugarParameter("@t", w.TenantId), new SugarParameter("@d", w.DomainCode ?? ""),
                new SugarParameter("@i", w.ItemNum), new SugarParameter("@l", w.Location),
                new SugarParameter("@lot", w.LotSerial ?? ""), new SugarParameter("@refs", w.Refs ?? ""));
        }

        foreach (var r in ws.TransactionRows)
            await db.Insertable(ToEntity(r)).ExecuteCommandAsync();
    }

    private static AdoInventoryTransaction ToEntity(InventoryTransactionRow r)
        => new AdoInventoryTransaction
        {
            Id = YitIdHelper.NextId(), TenantId = r.TenantId, DomainCode = r.DomainCode ?? "",
            ItemNum = r.ItemNum, Location = r.Location, LotSerial = r.LotSerial ?? "",
            TransType = r.TransType ?? "", QtyChange = r.QtyChange, BeginBalance = r.BeginBalance,
            WorkOrd = r.WorkOrd, ShipperNum = r.ShipperNum, Remark = r.Remark,
            FbillNo = r.Fbillno, Receiver = r.Receiver, RctQcNbr = r.RctQcNbr,
            PostingId = r.PostingId == 0 ? (long?)null : r.PostingId,
            TransactionGroupId = r.TransactionGroupId, Seq = r.Seq,
            CreateTime = DateTime.Now, CreateUser = r.User,
        };

    private sealed class InvRow { public string ItemNum { get; set; } public string Location { get; set; } public decimal QtyOnHand { get; set; } public decimal AvailStatusQty { get; set; } public decimal AssayQty { get; set; } public decimal FreezeQty { get; set; } }
    private sealed class DetailRow { public string ItemNum { get; set; } public string Location { get; set; } public string LotSerial { get; set; } public string Refs { get; set; } public decimal QtyOnHand { get; set; } public decimal AvailStatusQty { get; set; } public decimal AssayQty { get; set; } public decimal FreezeQty { get; set; } }
    private sealed class StateRow { public string ItemNum { get; set; } public string DefaultLocation { get; set; } }
}