| | | 1 | | using System.Globalization; |
| | | 2 | | using System.Linq.Expressions; |
| | | 3 | | using Microsoft.EntityFrameworkCore; |
| | | 4 | | using OzonOrdersWeb.Areas.Studio2.DTOs; |
| | | 5 | | using OzonOrdersWeb.WebServices.Grid; |
| | | 6 | | using Services.DataServices; |
| | | 7 | | |
| | | 8 | | namespace OzonOrdersWeb.Areas.Studio2.AnalysisServices; |
| | | 9 | | |
| | | 10 | | /// <summary>Фильтры страницы анализа, которые применяются на уровне БД (ключ кэша набора групп).</summary> |
| | | 11 | | public sealed record ProductAnalysisFilter( |
| | | 12 | | DateTime? StartDate, |
| | | 13 | | DateTime? EndDate, |
| | | 14 | | int? ManufacturerId, |
| | | 15 | | int? AppStatusId, |
| | | 16 | | int? ClientStatusId, |
| | | 17 | | int? OzonClientId, |
| | | 18 | | decimal? MinPrice, |
| | | 19 | | decimal? MaxPrice, |
| | | 20 | | string? ClientName = null) |
| | | 21 | | { |
| | | 22 | | public static ProductAnalysisFilter FromParams(IReadOnlyDictionary<string, string?> p) => new( |
| | | 23 | | Date(p, "startDate"), Date(p, "endDate"), |
| | | 24 | | Int(p, "manufacturerId"), Int(p, "appStatusId"), Int(p, "clientStatusId"), Int(p, "ozonClientId"), |
| | | 25 | | Dec(p, "minPrice"), Dec(p, "maxPrice"), Raw(p, "clientName")?.Trim()); |
| | | 26 | | |
| | | 27 | | private static string? Raw(IReadOnlyDictionary<string, string?> p, string key) => |
| | | 28 | | p.TryGetValue(key, out var v) && !string.IsNullOrWhiteSpace(v) ? v : null; |
| | | 29 | | |
| | | 30 | | private static DateTime? Date(IReadOnlyDictionary<string, string?> p, string key) => |
| | | 31 | | DateTime.TryParse(Raw(p, key), CultureInfo.InvariantCulture, DateTimeStyles.None, out var d) ? d : null; |
| | | 32 | | |
| | | 33 | | private static int? Int(IReadOnlyDictionary<string, string?> p, string key) => |
| | | 34 | | int.TryParse(Raw(p, key), NumberStyles.Integer, CultureInfo.InvariantCulture, out var i) ? i : null; |
| | | 35 | | |
| | | 36 | | private static decimal? Dec(IReadOnlyDictionary<string, string?> p, string key) => |
| | | 37 | | decimal.TryParse(Raw(p, key)?.Replace(',', '.'), NumberStyles.Number, CultureInfo.InvariantCulture, out var d) ? |
| | | 38 | | } |
| | | 39 | | |
| | | 40 | | public sealed record ProductGridStats(int TotalProducts, int TotalOrders, decimal TotalAmount, decimal AverageCheck); |
| | | 41 | | |
| | | 42 | | public sealed record ProductGridPage(List<ProductGroupDto> Rows, int TotalCount, ProductGridStats Stats); |
| | | 43 | | |
| | | 44 | | /// <summary> |
| | | 45 | | /// Серверная часть грида «Продукты»: читает узкую проекцию заказов без Include, группирует по ProductKey, |
| | | 46 | | /// кэширует готовый набор групп на короткое время (сортировка/фильтры/страницы — по кэшу в памяти). |
| | | 47 | | /// </summary> |
| | 0 | 48 | | public sealed class ProductAnalysisGridService(OrdersDataServices orders, ProductAnalysisCache cache) |
| | | 49 | | { |
| | 1 | 50 | | private static readonly TimeSpan CacheTtl = TimeSpan.FromMinutes(3); |
| | | 51 | | |
| | 1 | 52 | | public static readonly ServerGridDefinition<ProductGroupDto> Definition = BuildDefinition(); |
| | | 53 | | |
| | | 54 | | private static ServerGridDefinition<ProductGroupDto> BuildDefinition() => |
| | 1 | 55 | | new ServerGridDefinition<ProductGroupDto> |
| | 1 | 56 | | { |
| | 2 | 57 | | DefaultOrder = q => q.OrderByDescending(p => p.TotalOrders), |
| | 1 | 58 | | TieBreaker = p => (long)p.TotalQuantity |
| | 1 | 59 | | } |
| | 1 | 60 | | .Col("productKey", p => p.ProductKey) |
| | 1 | 61 | | .Col("article", p => p.Article) |
| | 1 | 62 | | .Col("productName", p => p.ProductName) |
| | 1 | 63 | | .Col("manufacturerName", p => p.ManufacturerName) |
| | 1 | 64 | | .Col("etProducerName", p => p.EtProducerName) |
| | 1 | 65 | | .Col("topClients", p => p.TopClientsText) |
| | 1 | 66 | | .Col("totalOrders", p => p.TotalOrders) |
| | 1 | 67 | | .Col("totalQuantity", p => p.TotalQuantity) |
| | 1 | 68 | | .Col("totalTrueQuantity", p => p.TotalTrueQuantity) |
| | 1 | 69 | | .Col("totalShipmentAmount", p => p.TotalShipmentAmount) |
| | 1 | 70 | | .Col("averageCheck", p => p.AverageCheck) |
| | 1 | 71 | | .Col("minPrice", p => p.MinPrice) |
| | 1 | 72 | | .Col("maxPrice", p => p.MaxPrice) |
| | 1 | 73 | | .Col("avgPrice", p => p.AvgPrice) |
| | 1 | 74 | | .Col("avgCostPrice", p => p.AvgCostPrice) |
| | 1 | 75 | | .Col("verifiedOrdersPercent", p => p.VerifiedOrdersPercent) |
| | 1 | 76 | | .Col("avgProfit", p => p.AvgProfit) |
| | 1 | 77 | | .Col("avgDiscount", p => p.AvgDiscount) |
| | 1 | 78 | | .Search(p => p.ProductKey, p => p.Article, p => p.ProductName, p => p.ManufacturerName); |
| | | 79 | | |
| | | 80 | | public Task<IReadOnlyList<ProductGroupDto>> GetGroupsAsync(ProductAnalysisFilter filter, CancellationToken ct) |
| | | 81 | | { |
| | 0 | 82 | | filter = filter with { StartDate = ProductAnalysisCache.Canon(filter.StartDate), EndDate = ProductAnalysisCache. |
| | 0 | 83 | | return cache.GetOrAddAsync(("pa.products", filter), CacheTtl, ProductAnalysisCache.GroupsWeight, |
| | 0 | 84 | | () => LoadGroupsAsync(filter, CancellationToken.None), ct); |
| | | 85 | | } |
| | | 86 | | |
| | | 87 | | private async Task<IReadOnlyList<ProductGroupDto>> LoadGroupsAsync(ProductAnalysisFilter f, CancellationToken ct) |
| | | 88 | | { |
| | 0 | 89 | | var query = orders.GetAnalyticsQueryable().Where(o => !string.IsNullOrEmpty(o.ProductKey)); |
| | 0 | 90 | | if (f.StartDate.HasValue) query = query.Where(o => o.ProcessingDate >= f.StartDate.Value); |
| | 0 | 91 | | if (f.EndDate.HasValue) query = query.Where(o => o.ProcessingDate <= f.EndDate.Value); |
| | 0 | 92 | | if (f.ManufacturerId.HasValue) query = query.Where(o => o.ManufacturerId == f.ManufacturerId.Value); |
| | 0 | 93 | | if (f.AppStatusId.HasValue) query = query.Where(o => o.AppStatusId == f.AppStatusId.Value); |
| | 0 | 94 | | if (f.ClientStatusId.HasValue) query = query.Where(o => o.ClientStatusId == f.ClientStatusId.Value); |
| | 0 | 95 | | if (f.OzonClientId.HasValue) query = query.Where(o => o.OzonClientId == f.OzonClientId.Value); |
| | 0 | 96 | | if (!string.IsNullOrEmpty(f.ClientName)) query = query.Where(o => o.OzonClient != null && o.OzonClient.Name == f |
| | 0 | 97 | | if (f.MinPrice.HasValue) query = query.Where(o => o.Price >= f.MinPrice.Value); |
| | 0 | 98 | | if (f.MaxPrice.HasValue) query = query.Where(o => o.Price <= f.MaxPrice.Value); |
| | | 99 | | |
| | 0 | 100 | | var rows = await AnalyticsRowProjection.Project(query).ToListAsync(ct); |
| | | 101 | | |
| | 0 | 102 | | return ProductGroupCalculator.Build(rows).OrderBy(p => p.ProductKey, StringComparer.Ordinal).ToList(); |
| | 0 | 103 | | } |
| | | 104 | | |
| | | 105 | | /// <summary>Детализация одного продукта за период (заказы одного ProductKey — небольшая выборка).</summary> |
| | | 106 | | public async Task<ProductDetailAnalysisDto?> GetDetailAsync(string productKey, DateTime? startDate, DateTime? endDat |
| | | 107 | | { |
| | 0 | 108 | | var query = orders.GetAnalyticsQueryable().Where(o => o.ProductKey == productKey); |
| | 0 | 109 | | if (startDate.HasValue) query = query.Where(o => o.ProcessingDate >= startDate.Value); |
| | 0 | 110 | | if (endDate.HasValue) query = query.Where(o => o.ProcessingDate <= endDate.Value); |
| | 0 | 111 | | var rows = await AnalyticsRowProjection.Project(query).ToListAsync(ct); |
| | 0 | 112 | | return ProductDetailBuilder.Build(productKey, rows); |
| | 0 | 113 | | } |
| | | 114 | | |
| | | 115 | | /// <summary>Блок строк: фильтры/поиск/сортировка по всему набору групп, итоги — по отфильтрованному набору.</summar |
| | | 116 | | public async Task<ProductGridPage> QueryAsync(ServerGridRequest request, CancellationToken ct) |
| | | 117 | | { |
| | 0 | 118 | | var groups = await GetGroupsAsync(ProductAnalysisFilter.FromParams(request.Params), ct); |
| | 0 | 119 | | return Query(groups, request); |
| | 0 | 120 | | } |
| | | 121 | | |
| | | 122 | | public static ProductGridPage Query(IEnumerable<ProductGroupDto> groups, ServerGridRequest request) |
| | | 123 | | { |
| | 4 | 124 | | var filtered = ServerGridQuery.ApplyFilters(groups.AsQueryable(), Definition, request).ToList(); |
| | 67 | 125 | | var totalOrders = filtered.Sum(p => p.TotalOrders); |
| | 67 | 126 | | var totalAmount = filtered.Sum(p => p.TotalShipmentAmount); |
| | 4 | 127 | | var stats = new ProductGridStats(filtered.Count, totalOrders, totalAmount, totalOrders > 0 ? totalAmount / total |
| | | 128 | | |
| | 4 | 129 | | var start = Math.Max(0, request.StartRow); |
| | 4 | 130 | | var take = Math.Clamp(request.EndRow - start, 1, ServerGridQuery.MaxBlockSize); |
| | 4 | 131 | | var page = ServerGridQuery.ApplySort(filtered.AsQueryable(), Definition, request.SortModel) |
| | 4 | 132 | | .Skip(start).Take(take).ToList(); |
| | 4 | 133 | | return new ProductGridPage(page, filtered.Count, stats); |
| | | 134 | | } |
| | | 135 | | |
| | | 136 | | /// <summary>Значения колонки с числом групп при остальных фильтрах — для set-фильтров.</summary> |
| | | 137 | | public async Task<List<ServerGridFacet>> FacetsAsync(ServerGridRequest request, CancellationToken ct) |
| | | 138 | | { |
| | 0 | 139 | | if (string.IsNullOrWhiteSpace(request.ColId) || !Definition.Columns.TryGetValue(request.ColId, out var col)) |
| | 0 | 140 | | return []; |
| | 0 | 141 | | var groups = await GetGroupsAsync(ProductAnalysisFilter.FromParams(request.Params), ct); |
| | 0 | 142 | | var filtered = ServerGridQuery.ApplyFilters(groups.AsQueryable(), Definition, request, excludeColId: request.Col |
| | | 143 | | |
| | 0 | 144 | | var boxed = Expression.Lambda<Func<ProductGroupDto, object?>>( |
| | 0 | 145 | | Expression.Convert(col.Selector.Body, typeof(object)), col.Selector.Parameters); |
| | 0 | 146 | | var selector = boxed.Compile(); |
| | 0 | 147 | | return filtered |
| | 0 | 148 | | .Select(selector) |
| | 0 | 149 | | .GroupBy(v => FacetKey(v)) |
| | 0 | 150 | | .Select(g => new ServerGridFacet { Value = g.Key, Count = g.Count() }) |
| | 0 | 151 | | .OrderByDescending(f => f.Count) |
| | 0 | 152 | | .Take(500) |
| | 0 | 153 | | .ToList(); |
| | 0 | 154 | | } |
| | | 155 | | |
| | 0 | 156 | | private static string FacetKey(object? key) => key switch |
| | 0 | 157 | | { |
| | 0 | 158 | | null => string.Empty, |
| | 0 | 159 | | bool b => b ? "true" : "false", |
| | 0 | 160 | | IFormattable f => f.ToString(null, CultureInfo.InvariantCulture), |
| | 0 | 161 | | _ => key.ToString() ?? string.Empty |
| | 0 | 162 | | }; |
| | | 163 | | } |