AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/FinishedWarehouse/SalesDeliveryNoticeService.cs

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

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 销售发货通知（头列表）只读 list 服务。
///
/// 数据源：aidopdev.ASNBOLShipperMaster a LEFT JOIN DepartmentMaster b，忠实复刻证据 sql.txt：
/// 业务过滤 a.ShType='SH' AND a.Typed&lt;&gt;'S' AND a.IsActive=1；join b ON a.Department=b.Department AND a.Domain=b.Domain。
/// 头列表粒度（一行一 ASNBOLShipperMaster）。口径：发货单号=Id；销售单号=OrdNbr；
/// 部门=Department+' '+DepartmentMaster.Descr；发货日期=ShipDate；状态=Status（raw）；备注=Remark。
/// 排序默认 ShipDate DESC, RecID DESC。
///
/// tenant 策略：ASNBOLShipperMaster 有 tenant_id，TenantId&gt;0 时追加 a.tenant_id=@TenantId，TenantId&lt;=0 不过滤；
/// 不使用 Domain 占位过滤（Domain 仅用于 DepartmentMaster join）。
///
/// 本服务仅 SELECT：无新增/编辑/删除/发货确认/出库/库存事务/状态流转/打印/导出/明细/详情。
/// 明细表 ASNBOLShipperDetail 与 S1 AsnShipperService 均不在本服务范围。
/// </summary>
[ApiDescriptionSettings(Order = 320, Description = "销售发货通知")]
[Route("api/S7SalesDeliveryNotice")]
[AllowAnonymous]
[NonUnify]
public class SalesDeliveryNoticeService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>排序字段白名单（前端 prop → 物理列），杜绝 orderBy 注入。</summary>
    private static readonly Dictionary<string, string> OrderByWhitelist = new(StringComparer.OrdinalIgnoreCase)
    {
        ["deliveryNo"] = "a.Id",
        ["salesOrderNo"] = "a.OrdNbr",
        ["dept"] = "a.Department",
        ["deliveryDate"] = "a.ShipDate",
        ["status"] = "a.Status",
        ["remark"] = "a.Remark",
    };

    /// <summary>
    /// 销售发货通知列表（只读分页查询）。
    /// </summary>
    [DisplayName("销售发货通知列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] SalesDeliveryNoticeListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : (input.PageSize > 200 ? 200 : input.PageSize);
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "a.ShType = 'SH'", "a.Typed <> 'S'", "a.IsActive = 1" };
        var pars = new List<SugarParameter>();

        if (input.TenantId.HasValue && input.TenantId.Value > 0)
        {
            where.Add("a.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", input.TenantId.Value));
        }
        if (!string.IsNullOrWhiteSpace(input.Id))
        {
            where.Add("a.Id LIKE @Id");
            pars.Add(new SugarParameter("@Id", $"%{input.Id.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.OrdNbr))
        {
            where.Add("a.OrdNbr LIKE @OrdNbr");
            pars.Add(new SugarParameter("@OrdNbr", $"%{input.OrdNbr.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Department))
        {
            where.Add("(a.Department LIKE @Department OR b.Descr LIKE @Department)");
            pars.Add(new SugarParameter("@Department", $"%{input.Department.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ShipDateFrom))
        {
            where.Add("a.ShipDate >= @ShipDateFrom");
            pars.Add(new SugarParameter("@ShipDateFrom", $"{input.ShipDateFrom.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.ShipDateTo))
        {
            where.Add("a.ShipDate <= @ShipDateTo");
            pars.Add(new SugarParameter("@ShipDateTo", $"{input.ShipDateTo.Trim()} 23:59:59"));
        }

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

        // 排序：白名单回落，方向 asc/desc 回落 desc；默认 ShipDate DESC, RecID DESC。
        string orderSql;
        if (!string.IsNullOrWhiteSpace(input.OrderBy) && OrderByWhitelist.TryGetValue(input.OrderBy.Trim(), out var col))
        {
            var dir = string.Equals(input.OrderDir?.Trim(), "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
            orderSql = $"{col} {dir}, a.RecID DESC";
        }
        else
        {
            orderSql = "a.ShipDate DESC, a.RecID DESC";
        }

        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM ASNBOLShipperMaster a LEFT JOIN DepartmentMaster b ON a.Department = b.Department AND a.Domain = b.Domain WHERE {whereSql}",
            pars);

        var list = await _db.Ado.SqlQueryAsync<SalesDeliveryNoticeListRow>(
            $"""
            SELECT
                a.RecID                                              AS RecId,
                a.Id                                                 AS DeliveryNo,
                a.OrdNbr                                             AS SalesOrderNo,
                TRIM(CONCAT(IFNULL(a.Department, ''), ' ', IFNULL(b.Descr, ''))) AS Dept,
                a.ShipDate                                           AS DeliveryDate,
                a.Status                                             AS Status,
                a.Remark                                             AS Remark,
                a.SoldTo                                             AS SoldTo,
                a.Domain                                             AS Domain,
                a.IsConfirm                                          AS IsConfirm,
                a.tenant_id                                          AS TenantId
            FROM ASNBOLShipperMaster a
            LEFT JOIN DepartmentMaster b ON a.Department = b.Department AND a.Domain = b.Domain
            WHERE {whereSql}
            ORDER BY {orderSql}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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