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

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 盘点标签确认 只读 list 服务。
///
/// 数据源：aidopdev.MissedPrint(IsTag=1) LEFT JOIN ItemMaster（物料名称/规格/单位）。
/// 参考本仓既有 MissedPrint 直读只读投影先例：LabelQueryService / StockQueryService / PendingInspectionService。
///
/// 本服务仅 SELECT：无新增/编辑/删除/确认写回/批量确认/反确认/库存事务/状态流转；扁平列表，无 detail。
/// MissedPrint 无 tenant_id 列，不做租户/工厂域过滤（对齐既有 MissedPrint 只读投影先例）；不加 Domain 占位过滤。
/// 派生列：diffQty=CompQty-Qty；status(Status='Q'→冻结)；stocktakeResult(CompQty&lt;&gt;Qty→有差异/正常)。
/// </summary>
[ApiDescriptionSettings(Order = 308, Description = "盘点标签确认")]
[Route("api/S5StocktakeLabelConfirm")]
[AllowAnonymous]
[NonUnify]
public class StocktakeLabelConfirmService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>
    /// 盘点标签确认列表（只读分页查询）。
    /// </summary>
    [DisplayName("盘点标签确认列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] StocktakeLabelConfirmListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "m.IsTag = 1" };
        var pars = new List<SugarParameter>();

        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("m.Location LIKE @Location");
            pars.Add(new SugarParameter("@Location", $"%{input.Location.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shelf))
        {
            where.Add("m.Shelf LIKE @Shelf");
            pars.Add(new SugarParameter("@Shelf", $"%{input.Shelf.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("m.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            where.Add("m.LotSerial LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }
        if (string.Equals(input.InvStatus, "有差异", StringComparison.Ordinal))
        {
            where.Add("IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0)");
        }
        else if (string.Equals(input.InvStatus, "正常", StringComparison.Ordinal))
        {
            where.Add("IFNULL(m.CompQty,0) = IFNULL(m.Qty,0)");
        }

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

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

        var list = await _db.Ado.SqlQueryAsync<StocktakeLabelConfirmListRow>(
            $"""
            SELECT
                m.RecID       AS Id,
                m.ItemNum     AS MaterialCode,
                i.Descr       AS MaterialName,
                i.Descr1      AS Spec,
                m.Location    AS Location,
                m.Shelf       AS Shelf,
                m.FirmString2 AS ActualLocation,
                m.FirmString4 AS ActualShelf,
                m.LotSerial   AS BatchNo,
                i.UM          AS Unit,
                m.Qty         AS LabelQty,
                m.CompQty     AS ActualQty,
                IFNULL(m.CompQty,0) - IFNULL(m.Qty,0) AS DiffQty,
                CASE WHEN m.Status='Q' THEN '冻结' ELSE IFNULL(m.Status,'') END AS Status,
                CASE WHEN IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0) THEN '有差异' ELSE '正常' END AS StocktakeResult,
                m.UpdateUser  AS StocktakePerson,
                m.UpdateTime  AS StocktakeTime,
                m.BarCode     AS BarCode
            FROM MissedPrint m
            LEFT JOIN ItemMaster i ON i.Domain = m.Domain AND i.ItemNum = m.ItemNum
            WHERE {whereSql}
            ORDER BY {BuildOrderBy(input.OrderBy, input.OrderDir)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 库位下拉选项（MissedPrint IsTag=1 的 Location distinct，只读）。
    /// </summary>
    [DisplayName("盘点标签确认库位选项")]
    [HttpGet("locations")]
    public async Task<object> GetLocations()
    {
        var list = await _db.Ado.SqlQueryAsync<StocktakeLabelConfirmLocationOption>(
            """
            SELECT DISTINCT Location AS Val
            FROM MissedPrint
            WHERE IsTag = 1 AND Location IS NOT NULL AND Location <> ''
            ORDER BY Location
            """);
        return list;
    }

    /// <summary>
    /// 排序白名单：仅允许按已展示列排序，杜绝 SQL 注入；非法字段回落默认。
    /// </summary>
    private static string BuildOrderBy(string? orderBy, string? orderDir)
    {
        var column = orderBy switch
        {
            "itemNum" => "m.ItemNum",
            "location" => "m.Location",
            "shelf" => "m.Shelf",
            "lotSerial" => "m.LotSerial",
            "qty" => "m.Qty",
            "compQty" => "m.CompQty",
            "qtyDiff" => "(IFNULL(m.CompQty,0) - IFNULL(m.Qty,0))",
            "invStatus" => "(CASE WHEN IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0) THEN 1 ELSE 0 END)",
            "updateTime" => "m.UpdateTime",
            _ => "m.UpdateTime",
        };
        var direction = string.Equals(orderDir, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $"{column} {direction}, m.RecID DESC";
    }
}