AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Supply/DeliveryExceptionService.cs

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 交货单异常记录服务
/// </summary>
[ApiDescriptionSettings(Order = 307, Description = "交货单异常记录")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class DeliveryExceptionService : IDynamicApiController, ITransient
{
    private const string C = "utf8mb4_general_ci";
    private readonly ISqlSugarClient _db;

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

    /// <summary>交货单异常记录分页列表</summary>
    [DisplayName("交货单异常记录列表")]
    [HttpGet("delivery-exception/list")]
    public async Task<object> GetList([FromQuery] DeliveryExceptionListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : input.PageSize;
        var offset = (page - 1) * pageSize;

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

        if (!string.IsNullOrWhiteSpace(input.OptTime))
        {
            where.Add($"(CAST(dm.OptTime AS CHAR(50)) COLLATE {C}) LIKE @OptTimeLike");
            pars.Add(new SugarParameter("@OptTimeLike", $"%{input.OptTime.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add($"(dm.ItemNum COLLATE {C}) = @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", input.ItemNum.Trim()));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", where));
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(*) FROM ({baseSql}) AS t", pars);
        var list = await _db.Ado.SqlQueryAsync<DeliveryExceptionListRow>(
            $"SELECT * FROM ({baseSql}) AS t ORDER BY t.Id DESC LIMIT {pageSize} OFFSET {offset}", pars);

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

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            dm.RecID                     AS Id,
            dm.Domain                    AS Domain,
            dm.Icdsid                    AS IcdsId,
            dm.OptTime                   AS OptTime,
            dm.ItemNum                   AS ItemNum,
            im.Descr                     AS Descr,
            dm.Remark                    AS Remark,
            IFNULL(dm.NeedQty, 0)        AS NeedQty
        FROM DeliveryExceptionMaster dm
        LEFT JOIN ItemMaster im
            ON (dm.Domain COLLATE {C}) = (im.Domain COLLATE {C})
            AND (dm.ItemNum COLLATE {C}) = (im.ItemNum COLLATE {C})
        WHERE {innerWhere}
        """;

    private sealed class DeliveryExceptionListRow
    {
        public long Id { get; set; }
        public string? Domain { get; set; }
        public long? IcdsId { get; set; }
        public DateTime? OptTime { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public decimal? NeedQty { get; set; }
        public string? Remark { get; set; }
    }
}