From 1ec08919c353d1459c524442b81538fd57fe5e83 Mon Sep 17 00:00:00 2001 From: Serghei Cebotari Date: Mon, 28 Apr 2025 16:43:14 +0300 Subject: Refactor parsers to include logging functionality and update tests accordingly --- .../Services/BsExcelParser.cs | 123 +++++++++--------- .../Services/CommonCsvParser.cs | 7 ++ .../Services/CommonExcelParser.cs | 137 ++++++++++++--------- RhSolutions.SkuParser.Tests/BsExcelParserTests.cs | 5 +- RhSolutions.SkuParser.Tests/ExcelParserTests.cs | 7 +- 5 files changed, 158 insertions(+), 121 deletions(-) diff --git a/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs b/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs index 801e28f..7abe8fa 100644 --- a/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs +++ b/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs @@ -7,64 +7,75 @@ namespace RhSolutions.SkuParser.Api.Services; public class BsExcelParser : ISkuParser { - private IConfiguration configuration; - private const int rowsLookupCount = 20; - private const decimal vat = 1.2M; - public BsExcelParser(IConfiguration configuration) - { - this.configuration = configuration; - } + private readonly IConfiguration configuration; + private readonly ILogger logger; + private const int rowsLookupCount = 20; + private const decimal vat = 1.2M; - public Dictionary ParseProducts(IFormFile file) - { - using XLWorkbook workbook = new(file.OpenReadStream()); - IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1); - var headers = configuration.GetSection("Headers"); - ws.AutoFilter.Clear(); - var cells = configuration.GetSection("Headers") - .GetChildren() - .ToDictionary(x => x.Path, x => ws.Range(1, 1, rowsLookupCount, ws.LastCellUsed().Address.ColumnNumber) - .Search(x.Value) - .FirstOrDefault()) - .OrderBy(x => x.Value?.Address.ColumnNumber ?? int.MaxValue); - if (cells == null || cells.Any(x => x.Value == null)) - { - throw new DataException($"В рабочей книге отсуствует таблица с необходимыми заголовками: {file.Name}"); - } + public BsExcelParser(IConfiguration configuration, ILogger logger) + { + this.configuration = configuration; + this.logger = logger; + } - var firstCell = cells.First().Value; - var lastCell = cells.Last().Value?.WorksheetColumn().LastCellUsed(); - var table = ws.Range(firstCell, lastCell).AsTable(); - var rows = table.DataRange.Rows(); - Dictionary result = new(); + public Dictionary ParseProducts(IFormFile file) + { + using XLWorkbook workbook = new(file.OpenReadStream()); + IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1); + var headers = configuration.GetSection("Headers"); + ws.AutoFilter.Clear(); + var cells = configuration.GetSection("Headers") + .GetChildren() + .ToDictionary(x => x.Path, x => ws.Range(1, 1, rowsLookupCount, ws.LastCellUsed().Address.ColumnNumber) + .Search(x.Value) + .FirstOrDefault()) + .OrderBy(x => x.Value?.Address.ColumnNumber ?? int.MaxValue); + if (cells == null || cells.Any(x => x.Value == null)) + { + throw new DataException($"В рабочей книге отсуствует таблица с необходимыми заголовками: {file.Name}"); + } - foreach (var row in rows.Where(r => r.Field(headers["Quantity"]).Value.IsNumber)) - { - Product product = new() - { - Sku = row.Field(headers["Sku"]).GetString(), - ProductLine = row.Field(headers["ProductLine"]).GetString() switch - { - "RAUTITAN" => ProductLine.RAUTITAN, - "RAUTHERM S" => ProductLine.RAUTHERMS, - _ => null - }, - Name = row.Field(headers["Name"]).GetString(), - Price = decimal.TryParse(row.Field(headers["Price"]).GetString(), out decimal value) - ? Math.Round(value * vat, 2) - : null - }; + var firstCell = cells.First().Value; + var lastCell = cells.Last().Value?.WorksheetColumn().LastCellUsed(); + var table = ws.Range(firstCell, lastCell).AsTable(); + var rows = table.DataRange.Rows(); + Dictionary result = new(); - double quantity = double.TryParse(row.Field(headers["Quantity"]).GetString(), out double q) ? q : 0; - if (result.ContainsKey(product)) - { - result[product] += quantity; - } - else - { - result.Add(product, quantity); - } - } - return result; - } + foreach (var row in rows.Where(r => r.Field(headers["Quantity"]).Value.IsNumber)) + { + Product product = new() + { + Sku = row.Field(headers["Sku"]).GetString(), + ProductLine = row.Field(headers["ProductLine"]).GetString() switch + { + "RAUTITAN" => ProductLine.RAUTITAN, + "RAUTHERM S" => ProductLine.RAUTHERMS, + _ => null + }, + Name = row.Field(headers["Name"]).GetString(), + Price = decimal.TryParse(row.Field(headers["Price"]).GetString(), out decimal value) + ? Math.Round(value * vat, 2) + : null + }; + + double quantity = double.TryParse(row.Field(headers["Quantity"]).GetString(), out double q) ? q : 0; + if (result.ContainsKey(product)) + { + result[product] += quantity; + } + else + { + result.Add(product, quantity); + } + } + + logger.LogInformation("Parsed {Count} products from file {FileName}.", result.Count, file.FileName); + foreach (var (product, quantity) in result) + { + logger.LogInformation("Product: {Sku}, Name: {Name}, Quantity: {Quantity}, Price: {Price}, ProductLine: {ProductLine}", + product.Sku, product.Name, quantity, product.Price, product.ProductLine); + } + + return result; + } } diff --git a/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs index 6dfc0da..549b6c5 100644 --- a/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs +++ b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs @@ -11,6 +11,13 @@ namespace RhSolutions.SkuParser.Services; /// public class CommonCsvParser : ISkuParser { + private readonly ILogger logger; + + public CommonCsvParser(ILogger logger) + { + this.logger = logger; + } + public Dictionary ParseProducts(IFormFile file) { using StreamReader reader = new(file.OpenReadStream()); diff --git a/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs index eb4e1bb..2815a3c 100644 --- a/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs +++ b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using Microsoft.Extensions.Logging; using RhSolutions.SkuParser.Abstractions; using RhSolutions.SkuParser.Models; @@ -6,75 +7,89 @@ namespace RhSolutions.SkuParser.Services; public class CommonExcelParser : ISkuParser { - public Dictionary ParseProducts(IFormFile file) - { - using XLWorkbook workbook = new(file.OpenReadStream()); - IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1); + private readonly ILogger logger; - var leftTop = ws.FirstCellUsed()?.Address; - var rightBottom = ws.LastCellUsed()?.Address; - if (new object?[] { leftTop, rightBottom }.Any(x => x == null)) - { - throw new ArgumentException($"Таблица пуста: {file.FileName}"); - } + public CommonExcelParser(ILogger logger) + { + this.logger = logger; + } - var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed(); - var columns = lookupRange.Columns(); + public Dictionary ParseProducts(IFormFile file) + { + using XLWorkbook workbook = new(file.OpenReadStream()); + IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1); - var skuColumnQuantity = columns - .Select(column => new - { - Column = column, - Products = column.CellsUsed() - .Select(cell => !cell.HasFormula && Product.TryParse(cell.Value.ToString(), out Product? p) ? p : null) - }) - .Select(c => new { c.Column, SkuCount = c.Products.Count(p => p != null) }) - .Aggregate((l, r) => l.SkuCount > r.SkuCount ? l : r); - var skuColumn = skuColumnQuantity.SkuCount > 0 ? skuColumnQuantity.Column : null; + var leftTop = ws.FirstCellUsed()?.Address; + var rightBottom = ws.LastCellUsed()?.Address; + if (new object?[] { leftTop, rightBottom }.Any(x => x == null)) + { + throw new ArgumentException($"Таблица пуста: {file.FileName}"); + } - if (skuColumn == null) - { - throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}"); - } + var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed(); + var columns = lookupRange.Columns(); - var quantityColumn = lookupRange.Columns().Skip(skuColumn.ColumnNumber()) - .Select(column => new - { - Column = column, - IsColumnWithNumbers = column.CellsUsed() - .Count(cell => cell.Value.IsNumber == true) > column.CellsUsed().Count() / 4 - }) - .First(x => x.IsColumnWithNumbers) - .Column; + var skuColumnQuantity = columns + .Select(column => new + { + Column = column, + Products = column.CellsUsed() + .Select(cell => !cell.HasFormula && Product.TryParse(cell.Value.ToString(), out Product? p) ? p : null) + }) + .Select(c => new { c.Column, SkuCount = c.Products.Count(p => p != null) }) + .Aggregate((l, r) => l.SkuCount > r.SkuCount ? l : r); + var skuColumn = skuColumnQuantity.SkuCount > 0 ? skuColumnQuantity.Column : null; - if (quantityColumn == null) - { - throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}"); - } + if (skuColumn == null) + { + throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}"); + } - Dictionary result = new(); - var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber); + var quantityColumn = lookupRange.Columns().Skip(skuColumn.ColumnNumber()) + .Select(column => new + { + Column = column, + IsColumnWithNumbers = column.CellsUsed() + .Count(cell => cell.Value.IsNumber == true) > column.CellsUsed().Count() / 4 + }) + .First(x => x.IsColumnWithNumbers) + .Column; - foreach (var row in rows) - { - var quantity = quantityColumn.Cell(row).Value; - var sku = skuColumn.Cell(row).Value; + if (quantityColumn == null) + { + throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}"); + } - if (quantity.IsNumber - && Product.TryParse(sku.ToString(), out Product? p) - && p != null) - { - if (result.ContainsKey(p)) - { - result[p] += (double)quantity; - } - else - { - result.Add(p, (double)quantity); - } - } - } + Dictionary result = new(); + var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber); - return result; - } + foreach (var row in rows) + { + var quantity = quantityColumn.Cell(row).Value; + var sku = skuColumn.Cell(row).Value; + + if (quantity.IsNumber + && Product.TryParse(sku.ToString(), out Product? p) + && p != null) + { + if (result.ContainsKey(p)) + { + result[p] += (double)quantity; + } + else + { + result.Add(p, (double)quantity); + } + } + } + + logger.LogInformation("Parsed {Count} products from file {FileName}.", result.Count, file.FileName); + foreach (var (product, quantity) in result) + { + logger.LogInformation("Product: {Sku}, Name: {Name}, Quantity: {Quantity}, Price: {Price}, ProductLine: {ProductLine}", + product.Sku, product.Name, quantity, product.Price, product.ProductLine); + } + + return result; + } } diff --git a/RhSolutions.SkuParser.Tests/BsExcelParserTests.cs b/RhSolutions.SkuParser.Tests/BsExcelParserTests.cs index c69088a..d2ed08e 100644 --- a/RhSolutions.SkuParser.Tests/BsExcelParserTests.cs +++ b/RhSolutions.SkuParser.Tests/BsExcelParserTests.cs @@ -1,4 +1,5 @@ using Microsoft.Extensions.Configuration; +using Microsoft.Extensions.Logging; using RhSolutions.SkuParser.Api.Services; using RhSolutions.SkuParser.Services; @@ -11,11 +12,11 @@ public class BsExcelParserTests { var mockFile = FormFileUtil.GetMockFormFile(filename); + var logger = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger(); var configuration = new ConfigurationBuilder() .AddJsonFile("appsettings.json", optional: true, reloadOnChange: false) .Build(); - - var parser = new BsExcelParser(configuration); + var parser = new BsExcelParser(configuration, logger); var actual = parser.ParseProducts(mockFile.Object); var expected = new Dictionary() { diff --git a/RhSolutions.SkuParser.Tests/ExcelParserTests.cs b/RhSolutions.SkuParser.Tests/ExcelParserTests.cs index 781ea56..d33628c 100644 --- a/RhSolutions.SkuParser.Tests/ExcelParserTests.cs +++ b/RhSolutions.SkuParser.Tests/ExcelParserTests.cs @@ -1,3 +1,4 @@ +using Microsoft.Extensions.Logging; using RhSolutions.SkuParser.Services; namespace RhSolutions.SkuParser.Tests; @@ -28,7 +29,8 @@ public class ExcelParserTests public void XlsxTests(string filename) { var mockFile = FormFileUtil.GetMockFormFile(filename); - var parser = new CommonExcelParser(); + var logger = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger(); + var parser = new CommonExcelParser(logger); var actual = parser.ParseProducts(mockFile.Object); Assert.That(actual.Count, Is.EqualTo(_expected.Count())); CollectionAssert.AreEqual(_expected, actual); @@ -38,7 +40,8 @@ public class ExcelParserTests public void CsvTests(string filename) { var mockFile = FormFileUtil.GetMockFormFile(filename); - var parser = new CommonCsvParser(); + var logger = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger(); + var parser = new CommonCsvParser(logger); var actual = parser.ParseProducts(mockFile.Object); Assert.That(actual.Count, Is.EqualTo(_expected.Count())); CollectionAssert.AreEqual(_expected, actual); -- cgit v1.2.3