| | | 1 | | using Microsoft.EntityFrameworkCore; |
| | | 2 | | using OzonDomains.Models; |
| | | 3 | | using OzonRepositories.Context; |
| | | 4 | | |
| | | 5 | | namespace Services.ApiServices.OzonApi; |
| | | 6 | | |
| | | 7 | | public class AssemblyListRunRepository |
| | | 8 | | { |
| | | 9 | | private readonly ApplicationDbContext _db; |
| | | 10 | | |
| | 4 | 11 | | public AssemblyListRunRepository(ApplicationDbContext db) |
| | | 12 | | { |
| | 4 | 13 | | _db = db; |
| | 4 | 14 | | } |
| | | 15 | | |
| | | 16 | | public async Task<long> CreateRunAsync(AssemblyListRun run) |
| | | 17 | | { |
| | 0 | 18 | | _db.AssemblyListRuns.Add(run); |
| | 0 | 19 | | await _db.SaveChangesAsync(); |
| | 0 | 20 | | return run.Id; |
| | 0 | 21 | | } |
| | | 22 | | |
| | | 23 | | public async Task CompleteRunAsync(long runId, string status, int totalPostings, int totalItems, string? errorMessag |
| | | 24 | | { |
| | 0 | 25 | | await _db.AssemblyListRuns |
| | 0 | 26 | | .Where(r => r.Id == runId) |
| | 0 | 27 | | .ExecuteUpdateAsync(s => s |
| | 0 | 28 | | .SetProperty(r => r.CompletedAtUtc, DateTime.UtcNow) |
| | 0 | 29 | | .SetProperty(r => r.RunStatus, status) |
| | 0 | 30 | | .SetProperty(r => r.TotalPostings, totalPostings) |
| | 0 | 31 | | .SetProperty(r => r.TotalItems, totalItems) |
| | 0 | 32 | | .SetProperty(r => r.ErrorMessage, errorMessage)); |
| | 0 | 33 | | } |
| | | 34 | | |
| | | 35 | | public async Task SaveItemsAsync(long runId, List<AssemblyListItem> items) |
| | | 36 | | { |
| | 0 | 37 | | if (items.Count == 0) return; |
| | | 38 | | |
| | 0 | 39 | | var entities = items.Select(i => new AssemblyListRunItem |
| | 0 | 40 | | { |
| | 0 | 41 | | RunId = runId, |
| | 0 | 42 | | ItemIndex = i.Index, |
| | 0 | 43 | | PostingNumber = i.PostingNumber, |
| | 0 | 44 | | Status = i.Status, |
| | 0 | 45 | | StatusLabel = i.StatusLabel, |
| | 0 | 46 | | WarehouseId = i.WarehouseId, |
| | 0 | 47 | | WarehouseName = i.WarehouseName, |
| | 0 | 48 | | OfferId = i.OfferId, |
| | 0 | 49 | | ProductName = i.ProductName, |
| | 0 | 50 | | ImageUrl = i.ImageUrl, |
| | 0 | 51 | | Quantity = i.Quantity, |
| | 0 | 52 | | CutoffDate = i.CutoffDate, |
| | 0 | 53 | | LabelUrl = i.LabelUrl, |
| | 0 | 54 | | LabelError = i.LabelError, |
| | 0 | 55 | | SupplierName = i.SupplierName, |
| | 0 | 56 | | OrderedToSupplierDate = i.OrderedToSupplierDate, |
| | 0 | 57 | | OneCBalance = i.OneCBalance, |
| | 0 | 58 | | OneCReceiptDoc = i.OneCReceiptDoc, |
| | 0 | 59 | | OneCReceiptDocKey = i.OneCReceiptDocKey, |
| | 0 | 60 | | OneCOtherStock = i.OneCOtherStock, |
| | 0 | 61 | | }).ToList(); |
| | | 62 | | |
| | 0 | 63 | | _db.AssemblyListRunItems.AddRange(entities); |
| | 0 | 64 | | await _db.SaveChangesAsync(); |
| | 0 | 65 | | } |
| | | 66 | | |
| | | 67 | | /// <summary> |
| | | 68 | | /// Возвращает для каждого posting_number: имя поставщика (текущий заказ) и дату транзакции |
| | | 69 | | /// «Заказан поставщику» (из temporal-истории Orders). |
| | | 70 | | /// </summary> |
| | | 71 | | public async Task<Dictionary<string, (string? SupplierName, DateTime? OrderedDate)>> GetOrderInfoByPostingNumbersAsy |
| | | 72 | | IEnumerable<string> postingNumbers) |
| | | 73 | | { |
| | 0 | 74 | | var numbers = postingNumbers.Where(n => !string.IsNullOrEmpty(n)).Distinct().ToList(); |
| | 0 | 75 | | if (numbers.Count == 0) |
| | 0 | 76 | | return new Dictionary<string, (string?, DateTime?)>(); |
| | | 77 | | |
| | | 78 | | // Текущий поставщик из актуальных Orders |
| | 0 | 79 | | var supplierMap = await _db.Orders |
| | 0 | 80 | | .Where(o => o.ShipmentNumber != null && numbers.Contains(o.ShipmentNumber)) |
| | 0 | 81 | | .Select(o => new { o.ShipmentNumber, SupplierName = o.Supplier != null ? o.Supplier.Name : null }) |
| | 0 | 82 | | .ToListAsync(); |
| | | 83 | | |
| | | 84 | | // Дата транзакции «Заказан поставщику»: |
| | | 85 | | // 1) из orders_history — первая запись о выставлении SupplierId |
| | | 86 | | // 2) fallback — прямой join Order → Transaction WHERE Type=0 (работает, если заказ |
| | | 87 | | // ещё не переведён в следующую транзакцию) |
| | 0 | 88 | | var inList = string.Join(",", numbers.Select(n => $"'{n.Replace("'", "''")}'")); |
| | 0 | 89 | | var sql = $@" |
| | 0 | 90 | | SELECT o.ShipmentNumber, |
| | 0 | 91 | | COALESCE( |
| | 0 | 92 | | (SELECT MIN(h.ChangedAt) |
| | 0 | 93 | | FROM orders_history h |
| | 0 | 94 | | WHERE h.OrderId = o.Id |
| | 0 | 95 | | AND h.ColumnName = 'SupplierId' |
| | 0 | 96 | | AND h.NewValue IS NOT NULL |
| | 0 | 97 | | AND h.NewValue <> ''), |
| | 0 | 98 | | (SELECT t.CreatedDateTime |
| | 0 | 99 | | FROM Transactions t |
| | 0 | 100 | | WHERE t.Id = o.TransactionId AND t.Type = 0 |
| | 0 | 101 | | LIMIT 1) |
| | 0 | 102 | | ) AS OrderedDate |
| | 0 | 103 | | FROM Orders o |
| | 0 | 104 | | WHERE o.ShipmentNumber IN ({inList})"; |
| | | 105 | | |
| | 0 | 106 | | var orderedDates = new Dictionary<string, DateTime>(StringComparer.Ordinal); |
| | 0 | 107 | | var conn = _db.Database.GetDbConnection(); |
| | 0 | 108 | | var wasOpen = conn.State == System.Data.ConnectionState.Open; |
| | 0 | 109 | | if (!wasOpen) await conn.OpenAsync(); |
| | | 110 | | try |
| | | 111 | | { |
| | 0 | 112 | | await using var cmd = conn.CreateCommand(); |
| | 0 | 113 | | cmd.CommandText = sql; |
| | 0 | 114 | | await using var reader = await cmd.ExecuteReaderAsync(); |
| | 0 | 115 | | while (await reader.ReadAsync()) |
| | | 116 | | { |
| | 0 | 117 | | var shipment = reader.GetString(0); |
| | 0 | 118 | | if (!reader.IsDBNull(1)) |
| | 0 | 119 | | orderedDates[shipment] = reader.GetDateTime(1); |
| | | 120 | | } |
| | 0 | 121 | | } |
| | | 122 | | finally |
| | | 123 | | { |
| | 0 | 124 | | if (!wasOpen) await conn.CloseAsync(); |
| | | 125 | | } |
| | | 126 | | |
| | 0 | 127 | | var result = new Dictionary<string, (string?, DateTime?)>(StringComparer.Ordinal); |
| | 0 | 128 | | foreach (var n in numbers) |
| | | 129 | | { |
| | 0 | 130 | | var supplier = supplierMap.FirstOrDefault(s => s.ShipmentNumber == n); |
| | 0 | 131 | | orderedDates.TryGetValue(n, out var orderedDate); |
| | 0 | 132 | | result[n] = (supplier?.SupplierName, orderedDate == default ? null : orderedDate); |
| | | 133 | | } |
| | 0 | 134 | | return result; |
| | 0 | 135 | | } |
| | | 136 | | |
| | | 137 | | /// <summary> |
| | | 138 | | /// Возвращает для каждого posting_number значение "Засыла" (OrderExtension.Key == "Засыл"), |
| | | 139 | | /// если он задан хотя бы у одного из заказов этого отправления. |
| | | 140 | | /// </summary> |
| | | 141 | | public async Task<Dictionary<string, string?>> GetZasylValuesByShipmentNumbersAsync(IEnumerable<string> shipmentNumb |
| | | 142 | | { |
| | 7 | 143 | | var numbers = shipmentNumbers.Where(n => !string.IsNullOrEmpty(n)).Distinct().ToList(); |
| | 4 | 144 | | if (numbers.Count == 0) |
| | 1 | 145 | | return new Dictionary<string, string?>(); |
| | | 146 | | |
| | 3 | 147 | | var rows = await _db.Orders |
| | 3 | 148 | | .Where(o => o.ShipmentNumber != null && numbers.Contains(o.ShipmentNumber)) |
| | 3 | 149 | | .Select(o => new |
| | 3 | 150 | | { |
| | 3 | 151 | | o.ShipmentNumber, |
| | 3 | 152 | | Zasyl = o.OrderExtensions |
| | 3 | 153 | | .Where(e => e.Key == "Засыл" && e.Value != null && e.Value != "") |
| | 3 | 154 | | .Select(e => e.Value) |
| | 3 | 155 | | .FirstOrDefault(), |
| | 3 | 156 | | }) |
| | 3 | 157 | | .ToListAsync(); |
| | | 158 | | |
| | 3 | 159 | | var result = new Dictionary<string, string?>(StringComparer.OrdinalIgnoreCase); |
| | 14 | 160 | | foreach (var row in rows) |
| | | 161 | | { |
| | 4 | 162 | | if (row.ShipmentNumber == null) continue; |
| | 4 | 163 | | if (!string.IsNullOrEmpty(row.Zasyl) || !result.ContainsKey(row.ShipmentNumber)) |
| | 4 | 164 | | result[row.ShipmentNumber] = row.Zasyl; |
| | | 165 | | } |
| | 3 | 166 | | return result; |
| | 4 | 167 | | } |
| | | 168 | | |
| | | 169 | | public async Task<List<AssemblyListRun>> GetRecentRunsAsync(int limit = 100) |
| | | 170 | | { |
| | 0 | 171 | | return await _db.AssemblyListRuns |
| | 0 | 172 | | .OrderByDescending(r => r.StartedAtUtc) |
| | 0 | 173 | | .Take(limit) |
| | 0 | 174 | | .ToListAsync(); |
| | 0 | 175 | | } |
| | | 176 | | |
| | | 177 | | public async Task<AssemblyListRun?> GetLastCompletedRunAsync() |
| | | 178 | | { |
| | 0 | 179 | | return await _db.AssemblyListRuns |
| | 0 | 180 | | .Where(r => r.RunStatus == "completed" && r.TotalItems > 0) |
| | 0 | 181 | | .OrderByDescending(r => r.StartedAtUtc) |
| | 0 | 182 | | .FirstOrDefaultAsync(); |
| | 0 | 183 | | } |
| | | 184 | | |
| | | 185 | | public async Task<AssemblyListRun?> GetRunAsync(long runId) |
| | | 186 | | { |
| | 0 | 187 | | return await _db.AssemblyListRuns.FindAsync(runId); |
| | 0 | 188 | | } |
| | | 189 | | |
| | | 190 | | public async Task<List<AssemblyListRunItem>> GetRunItemsAsync(long runId) |
| | | 191 | | { |
| | 0 | 192 | | return await _db.AssemblyListRunItems |
| | 0 | 193 | | .Where(i => i.RunId == runId) |
| | 0 | 194 | | .OrderBy(i => i.ItemIndex) |
| | 0 | 195 | | .ToListAsync(); |
| | 0 | 196 | | } |
| | | 197 | | } |