diff options
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<BsExcelParser> logger; + private const int rowsLookupCount = 20; + private const decimal vat = 1.2M; - public Dictionary<Product, double> 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<BsExcelParser> 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<Product, double> result = new(); + public Dictionary<Product, double> 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<Product, double> 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; /// </summary>
public class CommonCsvParser : ISkuParser
{
+ private readonly ILogger<CommonCsvParser> logger;
+
+ public CommonCsvParser(ILogger<CommonCsvParser> logger)
+ {
+ this.logger = logger;
+ }
+
public Dictionary<Product, double> 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<Product, double> ParseProducts(IFormFile file)
- {
- using XLWorkbook workbook = new(file.OpenReadStream());
- IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1);
+ private readonly ILogger<CommonExcelParser> 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<CommonExcelParser> logger)
+ {
+ this.logger = logger;
+ }
- var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed();
- var columns = lookupRange.Columns();
+ public Dictionary<Product, double> 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<Product, double> 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<Product, double> 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<BsExcelParser>(); 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<Product, double>() { 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<CommonExcelParser>();
+ 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<CommonCsvParser>();
+ var parser = new CommonCsvParser(logger);
var actual = parser.ParseProducts(mockFile.Object);
Assert.That(actual.Count, Is.EqualTo(_expected.Count()));
CollectionAssert.AreEqual(_expected, actual);
|