AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Universal/UniversalSourceListService.cs

namespace Admin.NET.Plugin.AiDOP.Universal;

/// <summary>
/// 通用货源清单选择服务
/// </summary>
[ApiDescriptionSettings(Order = 280, Description = "通用-货源清单选择")]
[Route("api/Universal")]
[AllowAnonymous]
[NonUnify]
public class UniversalSourceListService : IDynamicApiController, ITransient
{
    private const string C = "utf8mb4_general_ci";
    private readonly ISqlSugarClient _db;

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

    [DisplayName("获取货源清单选择列表")]
    [HttpGet("source-list/page")]
    public async Task<object> GetPage([FromQuery] UniversalSourceListPageInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "(it.erp_cls=3 OR it.erp_cls=2)",
            "IFNULL(sp.quota_rate,0)>0",
            "IFNULL(sp.IsDeleted,0)=0"
        };
        if (!string.IsNullOrWhiteSpace(input.Number))
        {
            where.Add("it.number LIKE @Number");
            pars.Add(new SugarParameter("@Number", $"%{input.Number.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.IcitemName))
        {
            where.Add("sp.icitem_name LIKE @IcitemName");
            pars.Add(new SugarParameter("@IcitemName", $"%{input.IcitemName.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierNumber))
        {
            where.Add("sp.supplier_number LIKE @SupplierNumber");
            pars.Add(new SugarParameter("@SupplierNumber", $"%{input.SupplierNumber.Trim()}%"));
        }

        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM srm_purchase sp
            LEFT JOIN ic_item it ON sp.icitem_id = it.Id
            LEFT JOIN ItemMaster im ON (it.number COLLATE {C}) = (im.ItemNum COLLATE {C})
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<UniversalSourceListOutput>(
            $"""
            SELECT
                sp.id AS Id,
                sp.tenant_id AS TenantId,
                sp.factory_id AS FactoryId,
                CAST(sp.icitem_id AS CHAR(30)) AS IcitemId,
                it.number AS Number,
                sp.icitem_name AS IcitemName,
                '原材料' AS ItemType,
                it.model AS Model,
                it.unit AS Unit,
                sp.supplier_type AS SupplierType,
                sp.is_active AS IsActive,
                sp.supplier_id AS SupplierId,
                sp.supplier_name AS SupplierName,
                sp.supplier_number AS SupplierNumber,
                sp.order_price AS OrderPrice,
                sp.currency_type AS CurrencyType,
                IFNULL(sp.taxrate,0) AS TaxRate,
                IFNULL(sp.tariff,0) AS Tariff,
                sp.freight AS Freight,
                sp.price_terms AS PriceTerms,
                sp.effective_date AS EffectiveDate,
                sp.expiring_date AS ExpiringDate,
                IFNULL(sp.quota_rate,0) AS QuotaRate,
                sp.lead_time AS LeadTime,
                sp.qty_min AS QtyMin,
                sp.packaging_qty AS PackagingQty,
                sp.order_rector_name AS OrderRectorName,
                sp.order_rector_num AS OrderRectorNum,
                CONCAT(IFNULL(it.number,''),IFNULL(sp.icitem_name,'')) AS Icitem,
                CONCAT(IFNULL(sp.supplier_name,''),IFNULL(sp.supplier_number,'')) AS Supplier,
                sp.IsRequireGoods AS IsRequireGoods,
                im.Location AS Location,
                im.UM AS Um,
                im.Rev AS Rev,
                im.Drawing AS Drawing
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "number" => $"it.number {dir}",
            "icitemname" => $"sp.icitem_name {dir}",
            "suppliertype" => $"sp.supplier_type {dir}",
            "suppliernumber" => $"sp.supplier_number {dir}",
            "suppliername" => $"sp.supplier_name {dir}",
            "leadtime" => $"sp.lead_time {dir}",
            _ => "sp.id DESC"
        };
    }
}