| | | 1 | | using System.Data.Common; |
| | | 2 | | using Services.ApiServices.TelegramBotApi; |
| | | 3 | | |
| | | 4 | | namespace Services.BitrixStockServices; |
| | | 5 | | |
| | | 6 | | /// <summary> |
| | | 7 | | /// Реальные остатки по складам Ozon из строк отчёта наката. Остаток в строке — это ответ Ozon API на момент |
| | | 8 | | /// построения отчёта, а после применения — подтверждённый остаток (<c>OzonQtyAfter</c>), поэтому отдельных |
| | | 9 | | /// запросов к Ozon не нужно. |
| | | 10 | | /// </summary> |
| | | 11 | | public static class OzonWarehouseStockQuery |
| | | 12 | | { |
| | | 13 | | public static async Task<List<TelegramOzonStockRow>> LoadAsync(DbConnection conn, string reportId, CancellationToken |
| | | 14 | | { |
| | 0 | 15 | | await using var cmd = conn.CreateCommand(); |
| | 0 | 16 | | cmd.CommandText = @"SELECT OzonClientName, OzonWarehouseName, |
| | 0 | 17 | | COUNT(DISTINCT CASE WHEN q > 0 THEN Article END) AS positions, |
| | 0 | 18 | | COALESCE(SUM(CASE WHEN q > 0 THEN q ELSE 0 END), 0) AS units |
| | 0 | 19 | | FROM ( |
| | 0 | 20 | | SELECT OzonClientName, OzonWarehouseName, Article, COALESCE(OzonQtyAfter, OzonQty) AS q |
| | 0 | 21 | | FROM BitrixStockSyncReportLines |
| | 0 | 22 | | WHERE ReportId = @rid AND OzonWarehouseId > 0 |
| | 0 | 23 | | AND OzonWarehouseName IS NOT NULL AND OzonWarehouseName <> '' AND OzonWarehouseName <> '—' |
| | 0 | 24 | | ) x |
| | 0 | 25 | | GROUP BY OzonClientName, OzonWarehouseName |
| | 0 | 26 | | ORDER BY OzonClientName, OzonWarehouseName"; |
| | 0 | 27 | | var p = cmd.CreateParameter(); |
| | 0 | 28 | | p.ParameterName = "@rid"; |
| | 0 | 29 | | p.Value = reportId; |
| | 0 | 30 | | cmd.Parameters.Add(p); |
| | | 31 | | |
| | 0 | 32 | | var rows = new List<TelegramOzonStockRow>(); |
| | 0 | 33 | | await using var reader = await cmd.ExecuteReaderAsync(ct); |
| | 0 | 34 | | while (await reader.ReadAsync(ct)) |
| | | 35 | | { |
| | 0 | 36 | | rows.Add(new TelegramOzonStockRow( |
| | 0 | 37 | | reader.GetString(1), Convert.ToInt32(reader.GetValue(2)), Convert.ToDecimal(reader.GetValue(3)), |
| | 0 | 38 | | reader.IsDBNull(0) ? null : reader.GetString(0))); |
| | | 39 | | } |
| | | 40 | | |
| | 0 | 41 | | return rows; |
| | 0 | 42 | | } |
| | | 43 | | } |