| | | 1 | | using OzonOrdersWeb.Areas.Studio2.DTOs; |
| | | 2 | | |
| | | 3 | | namespace OzonOrdersWeb.Areas.Studio2.AnalysisServices; |
| | | 4 | | |
| | | 5 | | /// <summary>Облегчённая проекция заказа для аналитики: без Include и навигационных графов.</summary> |
| | | 6 | | public sealed class AnalyticsOrderRow |
| | | 7 | | { |
| | | 8 | | public int Id { get; init; } |
| | | 9 | | public string? ProductKey { get; init; } |
| | | 10 | | public string? Article { get; init; } |
| | | 11 | | public string? ProductName { get; init; } |
| | | 12 | | public string? ManufacturerName { get; init; } |
| | | 13 | | public string? EtProducerName { get; init; } |
| | | 14 | | public string? ClientName { get; init; } |
| | | 15 | | public string? AppStatusName { get; init; } |
| | | 16 | | public string? ShipmentNumber { get; init; } |
| | | 17 | | public string? ClientStatusName { get; init; } |
| | | 18 | | public int? AppStatusId { get; init; } |
| | | 19 | | public DateTime? ShippingDate { get; init; } |
| | | 20 | | public bool IsReturnable { get; init; } |
| | | 21 | | public DateTime? ProcessingDate { get; init; } |
| | | 22 | | public int? Quantity { get; init; } |
| | | 23 | | public decimal? Price { get; init; } |
| | | 24 | | public decimal? ShipmentAmount { get; init; } |
| | | 25 | | public decimal? CostPrice { get; init; } |
| | | 26 | | public decimal? MinProfit { get; init; } |
| | | 27 | | public decimal? MaxProfit { get; init; } |
| | | 28 | | public decimal? MinDiscount { get; init; } |
| | | 29 | | public decimal? MaxDiscount { get; init; } |
| | | 30 | | public bool IsVerified { get; init; } |
| | | 31 | | public int? ClientStatusId { get; init; } |
| | | 32 | | public int? WarehouseId { get; init; } |
| | | 33 | | public string? WarehouseName { get; init; } |
| | | 34 | | public string? DeliveryCity { get; init; } |
| | | 35 | | } |
| | | 36 | | |
| | | 37 | | /// <summary> |
| | | 38 | | /// Группировка заказов по ProductKey в метрики вкладки «Продукты». |
| | | 39 | | /// Чистые функции без доступа к БД. Прибыль с аномалиями (< -500 000) нормализуется |
| | | 40 | | /// за один проход по группе (бинарный поиск соседей), а не пересортировкой группы на каждый заказ. |
| | | 41 | | /// </summary> |
| | | 42 | | public static class ProductGroupCalculator |
| | | 43 | | { |
| | | 44 | | /// <summary>Порог средней прибыли заказа, ниже которого значение считается аномальным.</summary> |
| | | 45 | | public const double AnomalousProfitThreshold = -500000; |
| | | 46 | | |
| | 11088 | 47 | | public static bool HasProfit(AnalyticsOrderRow o) => o.MinProfit.HasValue || o.MaxProfit.HasValue; |
| | | 48 | | |
| | 9424 | 49 | | public static double AverageProfit(AnalyticsOrderRow o) => ((double)(o.MinProfit ?? 0) + (double)(o.MaxProfit ?? 0)) |
| | | 50 | | |
| | 9811 | 51 | | public static bool IsAnomalousProfit(AnalyticsOrderRow o) => HasProfit(o) && AverageProfit(o) < AnomalousProfitThres |
| | | 52 | | |
| | | 53 | | /// <summary> |
| | | 54 | | /// Нормализатор прибыли группы заказов: аномальное значение заменяется средним между соседними |
| | | 55 | | /// (по ProcessingDate) нормальными заказами группы; нет соседей — значением единственного соседа; |
| | | 56 | | /// нет нормальных заказов — 0. |
| | | 57 | | /// </summary> |
| | | 58 | | public sealed class ProfitNormalizer |
| | | 59 | | { |
| | | 60 | | private readonly DateTime[] _dates; |
| | | 61 | | private readonly double[] _profits; |
| | | 62 | | private readonly double _averageOfNormals; |
| | | 63 | | |
| | 113 | 64 | | public ProfitNormalizer(IReadOnlyCollection<AnalyticsOrderRow> group) |
| | | 65 | | { |
| | 113 | 66 | | var normal = group |
| | 412 | 67 | | .Where(o => o.ProcessingDate.HasValue && !IsAnomalousProfit(o)) |
| | 360 | 68 | | .OrderBy(o => o.ProcessingDate!.Value) |
| | 113 | 69 | | .ToArray(); |
| | 473 | 70 | | _dates = normal.Select(o => o.ProcessingDate!.Value).ToArray(); |
| | 113 | 71 | | _profits = normal.Select(AverageProfit).ToArray(); |
| | 113 | 72 | | _averageOfNormals = _profits.Length > 0 ? _profits.Average() : 0; |
| | 113 | 73 | | } |
| | | 74 | | |
| | | 75 | | public decimal Normalize(AnalyticsOrderRow order) |
| | | 76 | | { |
| | 775 | 77 | | if (!HasProfit(order)) return 0; |
| | 401 | 78 | | var avg = AverageProfit(order); |
| | 748 | 79 | | if (avg >= AnomalousProfitThreshold) return (decimal)avg; |
| | 55 | 80 | | if (_dates.Length == 0) return 0; |
| | 54 | 81 | | if (!order.ProcessingDate.HasValue) return (decimal)_averageOfNormals; |
| | | 82 | | |
| | 52 | 83 | | var date = order.ProcessingDate.Value; |
| | 52 | 84 | | var next = LowerBoundAfter(date); |
| | 52 | 85 | | var prev = FirstNotBefore(date) - 1; |
| | 52 | 86 | | var hasPrev = prev >= 0; |
| | 52 | 87 | | var hasNext = next < _dates.Length; |
| | | 88 | | |
| | 98 | 89 | | if (hasPrev && hasNext) return (decimal)((_profits[prev] + _profits[next]) / 2.0); |
| | 10 | 90 | | if (hasPrev) return (decimal)_profits[prev]; |
| | 4 | 91 | | if (hasNext) return (decimal)_profits[next]; |
| | 0 | 92 | | return (decimal)_averageOfNormals; |
| | | 93 | | } |
| | | 94 | | |
| | | 95 | | /// <summary>Индекс первой даты, строго большей <paramref name="date"/>.</summary> |
| | | 96 | | private int LowerBoundAfter(DateTime date) |
| | | 97 | | { |
| | 104 | 98 | | int lo = 0, hi = _dates.Length; |
| | 398 | 99 | | while (lo < hi) |
| | | 100 | | { |
| | 346 | 101 | | var mid = (lo + hi) >>> 1; |
| | 692 | 102 | | if (_dates[mid] <= date) lo = mid + 1; else hi = mid; |
| | | 103 | | } |
| | | 104 | | |
| | 52 | 105 | | return lo; |
| | | 106 | | } |
| | | 107 | | |
| | | 108 | | /// <summary>Индекс первой даты, не меньшей <paramref name="date"/> (слева от неё — строго меньшие).</summary> |
| | | 109 | | private int FirstNotBefore(DateTime date) |
| | | 110 | | { |
| | 104 | 111 | | int lo = 0, hi = _dates.Length; |
| | 400 | 112 | | while (lo < hi) |
| | | 113 | | { |
| | 348 | 114 | | var mid = (lo + hi) >>> 1; |
| | 696 | 115 | | if (_dates[mid] < date) lo = mid + 1; else hi = mid; |
| | | 116 | | } |
| | | 117 | | |
| | 52 | 118 | | return lo; |
| | | 119 | | } |
| | | 120 | | } |
| | | 121 | | |
| | | 122 | | public static List<ProductGroupDto> Build(IEnumerable<AnalyticsOrderRow> rows) |
| | | 123 | | { |
| | 7 | 124 | | var result = new List<ProductGroupDto>(); |
| | 1159 | 125 | | foreach (var g in rows.Where(r => !string.IsNullOrEmpty(r.ProductKey)).GroupBy(r => r.ProductKey!)) |
| | | 126 | | { |
| | 42 | 127 | | var list = g.ToList(); |
| | 572 | 128 | | var first = list.MinBy(o => o.Id)!; |
| | | 129 | | |
| | 42 | 130 | | var topClients = list |
| | 530 | 131 | | .Where(o => !string.IsNullOrEmpty(o.ClientName)) |
| | 49 | 132 | | .GroupBy(o => o.ClientName!) |
| | 8 | 133 | | .OrderByDescending(cg => cg.Count()) |
| | 8 | 134 | | .ThenBy(cg => cg.Key, StringComparer.Ordinal) |
| | 42 | 135 | | .Take(3) |
| | 8 | 136 | | .Select(cg => cg.Key) |
| | 42 | 137 | | .ToList(); |
| | | 138 | | |
| | 42 | 139 | | var totalOrders = list.Count; |
| | 572 | 140 | | var totalQuantity = list.Sum(o => o.Quantity ?? 0); |
| | 572 | 141 | | var totalAmount = list.Sum(o => o.ShipmentAmount ?? 0); |
| | | 142 | | |
| | 42 | 143 | | decimal avgProfit = 0; |
| | 42 | 144 | | var profitOrders = list.Where(HasProfit).ToList(); |
| | 42 | 145 | | if (profitOrders.Count > 0) |
| | | 146 | | { |
| | 6 | 147 | | var normalizer = new ProfitNormalizer(list); |
| | 54 | 148 | | avgProfit = (decimal)(profitOrders.Sum(o => (double)normalizer.Normalize(o)) / profitOrders.Count); |
| | | 149 | | } |
| | | 150 | | |
| | 42 | 151 | | decimal avgDiscount = 0; |
| | 572 | 152 | | var discountOrders = list.Where(o => o.MinDiscount.HasValue || o.MaxDiscount.HasValue).ToList(); |
| | 42 | 153 | | if (discountOrders.Count > 0) |
| | | 154 | | { |
| | 0 | 155 | | var sum = discountOrders.Sum(o => ((double)(o.MinDiscount ?? 0) + (double)(o.MaxDiscount ?? 0)) / 2.0); |
| | 0 | 156 | | avgDiscount = (decimal)(sum / discountOrders.Count); |
| | | 157 | | } |
| | | 158 | | |
| | 574 | 159 | | var costs = list.Where(o => o.CostPrice.HasValue).Select(o => o.CostPrice!.Value).ToList(); |
| | 572 | 160 | | var verified = list.Count(o => o.IsVerified); |
| | | 161 | | |
| | 42 | 162 | | result.Add(new ProductGroupDto |
| | 42 | 163 | | { |
| | 42 | 164 | | ProductKey = g.Key, |
| | 42 | 165 | | Article = first.Article ?? "", |
| | 42 | 166 | | ProductName = first.ProductName ?? "Не указано", |
| | 42 | 167 | | ManufacturerName = first.ManufacturerName, |
| | 42 | 168 | | EtProducerName = first.EtProducerName, |
| | 42 | 169 | | TotalQuantity = totalQuantity, |
| | 530 | 170 | | TotalTrueQuantity = list.Sum(o => o.Quantity.HasValue |
| | 530 | 171 | | ? OzonDomains.Models.Order.ApplyProductKeyMultiplier(o.Quantity.Value, o.ProductKey) |
| | 530 | 172 | | : 0), |
| | 42 | 173 | | TotalOrders = totalOrders, |
| | 42 | 174 | | TotalShipmentAmount = totalAmount, |
| | 42 | 175 | | AverageCheck = totalOrders > 0 ? totalAmount / totalOrders : 0, |
| | 530 | 176 | | MinPrice = list.Min(o => o.Price ?? 0), |
| | 530 | 177 | | MaxPrice = list.Max(o => o.Price ?? 0), |
| | 42 | 178 | | AvgPrice = totalQuantity > 0 ? totalAmount / totalQuantity : 0, |
| | 42 | 179 | | MinCostPrice = costs.Count > 0 ? costs.Min() : null, |
| | 42 | 180 | | MaxCostPrice = costs.Count > 0 ? costs.Max() : null, |
| | 42 | 181 | | AvgCostPrice = costs.Count > 0 ? costs.Average() : null, |
| | 42 | 182 | | VerifiedOrdersCount = verified, |
| | 42 | 183 | | VerifiedOrdersPercent = totalOrders > 0 ? (decimal)verified / totalOrders * 100 : 0, |
| | 42 | 184 | | TopClients = topClients, |
| | 42 | 185 | | AvgProfit = avgProfit, |
| | 42 | 186 | | AvgDiscount = avgDiscount |
| | 42 | 187 | | }); |
| | | 188 | | } |
| | | 189 | | |
| | 7 | 190 | | return result; |
| | | 191 | | } |
| | | 192 | | } |
| | | 193 | | |
| | | 194 | | /// <summary>Единая проекция заказа в <see cref="AnalyticsOrderRow"/> без Include (EF транслирует в SELECT нужных колоно |
| | | 195 | | public static class AnalyticsRowProjection |
| | | 196 | | { |
| | | 197 | | public static IQueryable<AnalyticsOrderRow> Project(IQueryable<OzonDomains.Models.Order> query) => |
| | | 198 | | query.Select(o => new AnalyticsOrderRow |
| | | 199 | | { |
| | | 200 | | Id = o.Id, |
| | | 201 | | ProductKey = o.ProductKey, |
| | | 202 | | Article = o.Article, |
| | | 203 | | ProductName = o.ProductName, |
| | | 204 | | ManufacturerName = o.Manufacturer != null ? o.Manufacturer.Name : null, |
| | | 205 | | EtProducerName = o.EtProducer != null ? o.EtProducer.Name : null, |
| | | 206 | | ClientName = o.OzonClient != null ? o.OzonClient.Name : null, |
| | | 207 | | AppStatusName = o.AppStatus != null ? o.AppStatus.Name : null, |
| | | 208 | | ClientStatusName = o.ClientStatus != null ? o.ClientStatus.Name : null, |
| | | 209 | | AppStatusId = o.AppStatusId, |
| | | 210 | | ClientStatusId = o.ClientStatusId, |
| | | 211 | | WarehouseId = o.ShipmentWarehouseId, |
| | | 212 | | WarehouseName = o.ShipmentWarehouse != null ? o.ShipmentWarehouse.Name : null, |
| | | 213 | | DeliveryCity = o.DeliveryCity, |
| | | 214 | | ShipmentNumber = o.ShipmentNumber, |
| | | 215 | | ProcessingDate = o.ProcessingDate, |
| | | 216 | | ShippingDate = o.ShippingDate, |
| | | 217 | | Quantity = o.Quantity, |
| | | 218 | | Price = o.Price, |
| | | 219 | | ShipmentAmount = o.ShipmentAmount, |
| | | 220 | | CostPrice = o.CostPrice, |
| | | 221 | | MinProfit = o.MinProfit, |
| | | 222 | | MaxProfit = o.MaxProfit, |
| | | 223 | | MinDiscount = o.MinDiscount, |
| | | 224 | | MaxDiscount = o.MaxDiscount, |
| | | 225 | | IsVerified = o.IsVerified, |
| | | 226 | | IsReturnable = o.IsReturnable == true |
| | | 227 | | }); |
| | | 228 | | |
| | | 229 | | /// <summary> |
| | | 230 | | /// Лёгкие заглушки <see cref="OzonDomains.Models.Order"/> для сервисов прогноза и трендов, которые работают с сущно |
| | | 231 | | /// заполнены только используемые ими поля (дата, количество, артикул, название, склад, город, цена). |
| | | 232 | | /// </summary> |
| | | 233 | | public static List<OzonDomains.Models.Order> ToOrders(IEnumerable<AnalyticsOrderRow> rows) => |
| | | 234 | | rows.Select(r => new OzonDomains.Models.Order |
| | | 235 | | { |
| | | 236 | | Id = r.Id, |
| | | 237 | | ProductKey = r.ProductKey, |
| | | 238 | | Article = r.Article, |
| | | 239 | | ProductName = r.ProductName, |
| | | 240 | | ProcessingDate = r.ProcessingDate, |
| | | 241 | | Quantity = r.Quantity, |
| | | 242 | | Price = r.Price, |
| | | 243 | | DeliveryCity = r.DeliveryCity, |
| | | 244 | | ShipmentWarehouseId = r.WarehouseId, |
| | | 245 | | ShipmentWarehouse = r.WarehouseName == null ? null : new OzonDomains.Models.Warehouse { Id = r.WarehouseId ? |
| | | 246 | | }).ToList(); |
| | | 247 | | } |