| | | 1 | | using System.Globalization; |
| | | 2 | | using System.Text.RegularExpressions; |
| | | 3 | | using ClosedXML.Excel; |
| | | 4 | | |
| | | 5 | | namespace Services.ArmtekEtpServices; |
| | | 6 | | |
| | | 7 | | /// <summary>Строка файла «Взаиморасчёты» из кабинета Armtek (Выгрузить файл → Microsoft Office Excel).</summary> |
| | 11 | 8 | | public sealed record ArmtekSettlementExcelRow( |
| | 5 | 9 | | int RowNumber, |
| | 21 | 10 | | string Kunnr, |
| | 4 | 11 | | string? ClientName, |
| | 7 | 12 | | string DocTypeName, |
| | 15 | 13 | | string Vbeln, |
| | 9 | 14 | | string? DocNumber, |
| | 17 | 15 | | DateTime DocDate, |
| | 4 | 16 | | string? PaymentMethodName, |
| | 4 | 17 | | string? Currency, |
| | 12 | 18 | | decimal Amount, |
| | 12 | 19 | | decimal ReturnAmount, |
| | 12 | 20 | | decimal PaymentAmount, |
| | 19 | 21 | | decimal Balance, |
| | 4 | 22 | | DateTime? PayUntil, |
| | 4 | 23 | | string? Contract, |
| | 15 | 24 | | IReadOnlyDictionary<string, string?> Raw); |
| | | 25 | | |
| | | 26 | | public sealed class ArmtekSettlementsExcelResult |
| | | 27 | | { |
| | | 28 | | public List<ArmtekSettlementExcelRow> Rows { get; } = new(); |
| | | 29 | | public List<string> Errors { get; } = new(); |
| | | 30 | | public List<string> Warnings { get; } = new(); |
| | | 31 | | public bool IsValid => Errors.Count == 0 && Rows.Count > 0; |
| | | 32 | | public DateTime? PeriodFrom => Rows.Count == 0 ? null : Rows.Min(r => r.DocDate); |
| | | 33 | | public DateTime? PeriodTo => Rows.Count == 0 ? null : Rows.Max(r => r.DocDate); |
| | | 34 | | } |
| | | 35 | | |
| | | 36 | | /// <summary> |
| | | 37 | | /// Чтение и проверка xlsx «Взаиморасчётов». Колонки ищутся по заголовку в первой строке (порядок не важен), обязательны |
| | | 38 | | /// <see cref="RequiredColumns"/>. Любая ошибка в строке отклоняет весь файл: частичный импорт взаиморасчётов исказил бы |
| | | 39 | | /// Разрыв цепочки сальдо (сальдо = предыдущее − продажа + возврат + платёж) — только предупреждение: строки одного дня |
| | | 40 | | /// кабинет может выгрузить в другом порядке. |
| | | 41 | | /// </summary> |
| | | 42 | | public static class ArmtekSettlementsExcelReader |
| | | 43 | | { |
| | | 44 | | public const int MaxRows = 50_000; |
| | | 45 | | private const int MaxErrors = 50; |
| | | 46 | | |
| | | 47 | | public static readonly IReadOnlyList<string> RequiredColumns = |
| | | 48 | | ["Покупатель", "Вид документа", "Код документа", "Дата документа", "Сумма продажи", "Сумма возврата", "Сумма пла |
| | | 49 | | |
| | | 50 | | public static readonly IReadOnlyList<string> OptionalColumns = |
| | | 51 | | ["Наименование покупателя", "Номер документа", "Способ платежа", "Валюта", "Оплатить до", "Договор"]; |
| | | 52 | | |
| | | 53 | | private static readonly Regex Digits = new(@"^\d{1,12}$", RegexOptions.Compiled); |
| | | 54 | | private static readonly string[] DateFormats = ["dd.MM.yyyy", "d.M.yyyy", "dd.MM.yy", "yyyy-MM-dd", "yyyyMMdd"]; |
| | | 55 | | |
| | | 56 | | public static ArmtekSettlementsExcelResult Read(Stream stream) |
| | | 57 | | { |
| | | 58 | | var result = new ArmtekSettlementsExcelResult(); |
| | | 59 | | XLWorkbook workbook; |
| | | 60 | | try |
| | | 61 | | { |
| | | 62 | | workbook = new XLWorkbook(stream); |
| | | 63 | | } |
| | | 64 | | catch (Exception ex) |
| | | 65 | | { |
| | | 66 | | result.Errors.Add("Файл не читается как Excel (.xlsx): " + ex.Message); |
| | | 67 | | return result; |
| | | 68 | | } |
| | | 69 | | |
| | | 70 | | using (workbook) |
| | | 71 | | { |
| | | 72 | | var sheet = workbook.Worksheets.FirstOrDefault(); |
| | | 73 | | var used = sheet?.RangeUsed(); |
| | | 74 | | if (sheet == null || used == null) |
| | | 75 | | { |
| | | 76 | | result.Errors.Add("В файле нет данных."); |
| | | 77 | | return result; |
| | | 78 | | } |
| | | 79 | | |
| | | 80 | | var headerRow = used.FirstRow().RowNumber(); |
| | | 81 | | var columns = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase); |
| | | 82 | | foreach (var cell in sheet.Row(headerRow).CellsUsed()) |
| | | 83 | | { |
| | | 84 | | var title = Normalize(cell.GetString()); |
| | | 85 | | if (title.Length == 0) continue; |
| | | 86 | | if (!columns.TryAdd(title, cell.Address.ColumnNumber)) |
| | | 87 | | result.Errors.Add($"Колонка «{title}» встречается в заголовке дважды."); |
| | | 88 | | else if (!RequiredColumns.Contains(title, StringComparer.OrdinalIgnoreCase) && !OptionalColumns.Contains |
| | | 89 | | result.Warnings.Add($"Колонка «{title}» не используется — сохранится только в исходной строке."); |
| | | 90 | | } |
| | | 91 | | |
| | | 92 | | var missing = RequiredColumns.Where(c => !columns.ContainsKey(c)).ToList(); |
| | | 93 | | if (missing.Count > 0) |
| | | 94 | | { |
| | | 95 | | result.Errors.Add("Это не выгрузка «Взаиморасчётов» Armtek: нет колонок " + string.Join(", ", missing.Se |
| | | 96 | | ". Нужен файл из кабинета: Финансы → Взаиморасчёты → Выгрузить файл → Microsoft Office Excel (.xlsx) |
| | | 97 | | return result; |
| | | 98 | | } |
| | | 99 | | |
| | | 100 | | var lastRow = used.LastRow().RowNumber(); |
| | | 101 | | if (lastRow - headerRow > MaxRows) |
| | | 102 | | { |
| | | 103 | | result.Errors.Add($"Слишком много строк: {lastRow - headerRow}, допускается до {MaxRows}."); |
| | | 104 | | return result; |
| | | 105 | | } |
| | | 106 | | |
| | | 107 | | var seen = new Dictionary<(string, string), int>(); |
| | | 108 | | for (var r = headerRow + 1; r <= lastRow; r++) |
| | | 109 | | { |
| | | 110 | | var row = sheet.Row(r); |
| | | 111 | | if (row.CellsUsed().All(c => string.IsNullOrWhiteSpace(c.GetString()))) |
| | | 112 | | continue; |
| | | 113 | | |
| | | 114 | | var errors = new List<string>(); |
| | | 115 | | string? Text(string col) => columns.TryGetValue(col, out var i) ? CellText(row.Cell(i)) : null; |
| | | 116 | | |
| | | 117 | | var kunnr = Text("Покупатель"); |
| | | 118 | | if (kunnr == null || !Digits.IsMatch(kunnr)) errors.Add($"«Покупатель» должен быть кодом из цифр, а не « |
| | | 119 | | var vbeln = Text("Код документа"); |
| | | 120 | | if (vbeln == null || !Digits.IsMatch(vbeln)) errors.Add($"«Код документа» должен быть кодом из цифр, а н |
| | | 121 | | var docType = Text("Вид документа"); |
| | | 122 | | if (string.IsNullOrWhiteSpace(docType)) errors.Add("пустой «Вид документа»"); |
| | | 123 | | |
| | | 124 | | var docDate = ReadDate(row.Cell(columns["Дата документа"]), "Дата документа", errors, required: true); |
| | | 125 | | var payUntil = columns.TryGetValue("Оплатить до", out var payCol) ? ReadDate(row.Cell(payCol), "Оплатить |
| | | 126 | | var amount = ReadDecimal(row.Cell(columns["Сумма продажи"]), "Сумма продажи", errors); |
| | | 127 | | var returnAmount = ReadDecimal(row.Cell(columns["Сумма возврата"]), "Сумма возврата", errors); |
| | | 128 | | var paymentAmount = ReadDecimal(row.Cell(columns["Сумма платежа"]), "Сумма платежа", errors); |
| | | 129 | | var balance = ReadDecimal(row.Cell(columns["Сальдо"]), "Сальдо", errors); |
| | | 130 | | |
| | | 131 | | var currency = Text("Валюта"); |
| | | 132 | | if (currency != null && !Regex.IsMatch(currency, "^[A-Za-z]{3}$")) errors.Add($"«Валюта» должна быть код |
| | | 133 | | |
| | | 134 | | if (kunnr != null && vbeln != null) |
| | | 135 | | { |
| | | 136 | | if (seen.TryGetValue((kunnr, vbeln), out var firstRow)) |
| | | 137 | | errors.Add($"документ {vbeln} уже есть в строке {firstRow}"); |
| | | 138 | | else |
| | | 139 | | seen[(kunnr, vbeln)] = r; |
| | | 140 | | } |
| | | 141 | | |
| | | 142 | | if (errors.Count > 0) |
| | | 143 | | { |
| | | 144 | | foreach (var e in errors) |
| | | 145 | | if (result.Errors.Count < MaxErrors) result.Errors.Add($"Строка {r}: {e}."); |
| | | 146 | | continue; |
| | | 147 | | } |
| | | 148 | | |
| | | 149 | | var raw = columns.ToDictionary(c => c.Key, c => CellText(row.Cell(c.Value)), StringComparer.Ordinal); |
| | | 150 | | result.Rows.Add(new ArmtekSettlementExcelRow(r, kunnr!, Text("Наименование покупателя"), docType!.Trim() |
| | | 151 | | Text("Номер документа"), docDate!.Value, Text("Способ платежа"), currency?.ToUpperInvariant(), |
| | | 152 | | amount!.Value, returnAmount!.Value, paymentAmount!.Value, balance!.Value, payUntil, Text("Договор"), |
| | | 153 | | } |
| | | 154 | | |
| | | 155 | | if (result.Errors.Count >= MaxErrors) |
| | | 156 | | result.Errors.Add($"Показаны первые {MaxErrors} ошибок."); |
| | | 157 | | if (result.Rows.Count == 0 && result.Errors.Count == 0) |
| | | 158 | | result.Errors.Add("В файле нет строк с документами."); |
| | | 159 | | if (result.Errors.Count == 0) |
| | | 160 | | CheckBalanceChain(result); |
| | | 161 | | return result; |
| | | 162 | | } |
| | | 163 | | } |
| | | 164 | | |
| | | 165 | | /// <summary>Сальдо после документа = сальдо до него − продажа + возврат + платёж (так считает кабинет).</summary> |
| | | 166 | | private static void CheckBalanceChain(ArmtekSettlementsExcelResult result) |
| | | 167 | | { |
| | | 168 | | var breaks = new List<int>(); |
| | | 169 | | foreach (var group in result.Rows.GroupBy(x => x.Kunnr)) |
| | | 170 | | { |
| | | 171 | | ArmtekSettlementExcelRow? prev = null; |
| | | 172 | | foreach (var row in group) |
| | | 173 | | { |
| | | 174 | | if (prev != null && Math.Abs(prev.Balance - row.Amount + row.ReturnAmount + row.PaymentAmount - row.Bala |
| | | 175 | | breaks.Add(row.RowNumber); |
| | | 176 | | prev = row; |
| | | 177 | | } |
| | | 178 | | } |
| | | 179 | | if (breaks.Count > 0) |
| | | 180 | | result.Warnings.Add($"Сальдо не сходится с предыдущей строкой в {breaks.Count} строках (первые: {string.Join |
| | | 181 | | "Обычно это другой порядок документов одного дня; суммы загружаются как в файле."); |
| | | 182 | | } |
| | | 183 | | |
| | | 184 | | private static string? CellText(IXLCell cell) |
| | | 185 | | { |
| | | 186 | | var v = cell.Value; |
| | | 187 | | var text = v.Type switch |
| | | 188 | | { |
| | | 189 | | XLDataType.Blank => null, |
| | | 190 | | // Коды документов Excel хранит числом — без дробной части и экспоненты. |
| | | 191 | | XLDataType.Number => v.GetNumber() % 1 == 0 |
| | | 192 | | ? v.GetNumber().ToString("0", CultureInfo.InvariantCulture) |
| | | 193 | | : v.GetNumber().ToString(CultureInfo.InvariantCulture), |
| | | 194 | | XLDataType.DateTime => v.GetDateTime().ToString("dd.MM.yyyy", CultureInfo.InvariantCulture), |
| | | 195 | | _ => cell.GetString() |
| | | 196 | | }; |
| | | 197 | | return string.IsNullOrWhiteSpace(text) ? null : text.Trim(); |
| | | 198 | | } |
| | | 199 | | |
| | | 200 | | private static DateTime? ReadDate(IXLCell cell, string column, List<string> errors, bool required) |
| | | 201 | | { |
| | | 202 | | var v = cell.Value; |
| | | 203 | | if (v.Type == XLDataType.DateTime) return v.GetDateTime().Date; |
| | | 204 | | if (v.Type == XLDataType.Number) return DateTime.FromOADate(v.GetNumber()).Date; |
| | | 205 | | var text = CellText(cell); |
| | | 206 | | if (text == null) |
| | | 207 | | { |
| | | 208 | | if (required) errors.Add($"пустая «{column}»"); |
| | | 209 | | return null; |
| | | 210 | | } |
| | | 211 | | if (DateTime.TryParseExact(text, DateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out var d)) ret |
| | | 212 | | errors.Add($"«{column}» не дата: «{text}» (нужно ДД.ММ.ГГГГ)"); |
| | | 213 | | return null; |
| | | 214 | | } |
| | | 215 | | |
| | | 216 | | private static decimal? ReadDecimal(IXLCell cell, string column, List<string> errors) |
| | | 217 | | { |
| | | 218 | | var v = cell.Value; |
| | | 219 | | if (v.Type == XLDataType.Number) return Math.Round((decimal)v.GetNumber(), 3); |
| | | 220 | | var text = CellText(cell); |
| | | 221 | | if (text == null) return 0m; |
| | | 222 | | var normalized = text.Replace(" ", string.Empty).Replace(" ", string.Empty).Replace(',', '.'); |
| | | 223 | | if (decimal.TryParse(normalized, NumberStyles.Number, CultureInfo.InvariantCulture, out var d)) return d; |
| | | 224 | | errors.Add($"«{column}» не число: «{text}»"); |
| | | 225 | | return null; |
| | | 226 | | } |
| | | 227 | | |
| | | 228 | | private static string Normalize(string s) => Regex.Replace(s, @"\s+", " ").Trim(); |
| | | 229 | | } |