From a542bfb7f4ceee8b49cf8fcadf64ffb72cc97da5 Mon Sep 17 00:00:00 2001 From: Serghei Cebotari Date: Tue, 14 Jan 2025 14:01:01 +0000 Subject: Common Parsing Method --- .../Abstractions/ISkuParser.cs | 8 +++ .../Controllers/CommonParseController.cs | 48 +++++++++++++ .../Controllers/ProductsController.cs | 48 ------------- RhSolutions.SkuParser.Api/Program.cs | 7 +- .../Services/CommonCsvParser.cs | 26 +++++++ .../Services/CommonExcelParser.cs | 80 ++++++++++++++++++++++ RhSolutions.SkuParser.Api/Services/CsvParser.cs | 24 ------- RhSolutions.SkuParser.Api/Services/ExcelParser.cs | 76 -------------------- RhSolutions.SkuParser.Api/Services/ISkuParser.cs | 7 -- RhSolutions.SkuParser.Tests/ExcelParserTests.cs | 28 ++++---- 10 files changed, 179 insertions(+), 173 deletions(-) create mode 100644 RhSolutions.SkuParser.Api/Abstractions/ISkuParser.cs create mode 100644 RhSolutions.SkuParser.Api/Controllers/CommonParseController.cs delete mode 100644 RhSolutions.SkuParser.Api/Controllers/ProductsController.cs create mode 100644 RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs create mode 100644 RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/CsvParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/ExcelParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/ISkuParser.cs diff --git a/RhSolutions.SkuParser.Api/Abstractions/ISkuParser.cs b/RhSolutions.SkuParser.Api/Abstractions/ISkuParser.cs new file mode 100644 index 0000000..a1b2fbf --- /dev/null +++ b/RhSolutions.SkuParser.Api/Abstractions/ISkuParser.cs @@ -0,0 +1,8 @@ +using RhSolutions.SkuParser.Models; + +namespace RhSolutions.SkuParser.Abstractions; + +public interface ISkuParser +{ + public Dictionary ParseProducts(IFormFile file); +} diff --git a/RhSolutions.SkuParser.Api/Controllers/CommonParseController.cs b/RhSolutions.SkuParser.Api/Controllers/CommonParseController.cs new file mode 100644 index 0000000..c819851 --- /dev/null +++ b/RhSolutions.SkuParser.Api/Controllers/CommonParseController.cs @@ -0,0 +1,48 @@ +using Microsoft.AspNetCore.Mvc; +using RhSolutions.SkuParser.Models; +using RhSolutions.SkuParser.Abstractions; + +namespace RhSolutions.SkuParser.Controllers; + +[ApiController] +[Route("/api/[controller]")] +public class CommonParseController : ControllerBase +{ + private IServiceProvider _provider; + private Dictionary _result; + public CommonParseController(IServiceProvider provider) + { + _provider = provider; + _result = new(); + } + + [HttpPost] + public IActionResult PostFiles() + { + IFormFileCollection files = Request.Form.Files; + try + { + foreach (var file in files) + { + ISkuParser parser = _provider.GetRequiredKeyedService(file.ContentType); + var dict = parser.ParseProducts(file); + foreach (var kvp in dict) + { + if (_result.ContainsKey(kvp.Key)) + { + _result[kvp.Key] += kvp.Value; + } + else + { + _result.Add(kvp.Key, kvp.Value); + } + } + } + } + catch (Exception ex) + { + return BadRequest(error: $"{ex.Message}\n\n{ex.Source}\n{ex.StackTrace}"); + } + return new JsonResult(_result.Select(x => new { x.Key.Sku, x.Value })); + } +} \ No newline at end of file diff --git a/RhSolutions.SkuParser.Api/Controllers/ProductsController.cs b/RhSolutions.SkuParser.Api/Controllers/ProductsController.cs deleted file mode 100644 index 77b277b..0000000 --- a/RhSolutions.SkuParser.Api/Controllers/ProductsController.cs +++ /dev/null @@ -1,48 +0,0 @@ -using Microsoft.AspNetCore.Mvc; -using RhSolutions.SkuParser.Models; -using RhSolutions.SkuParser.Services; - -namespace RhSolutions.SkuParser.Controllers; - -[ApiController] -[Route("/api/[controller]")] -public class ProductsController : ControllerBase -{ - private IServiceProvider _provider; - private Dictionary _result; - public ProductsController(IServiceProvider provider) - { - _provider = provider; - _result = new(); - } - - [HttpPost] - public IActionResult PostFiles() - { - IFormFileCollection files = Request.Form.Files; - try - { - foreach (var file in files) - { - ISkuParser parser = _provider.GetRequiredKeyedService(file.ContentType); - IEnumerable productQuantities = parser.ParseProducts(file); - foreach (ProductQuantity pq in productQuantities) - { - if (_result.ContainsKey(pq.Product)) - { - _result[pq.Product] += pq.Quantity; - } - else - { - _result.Add(pq.Product, pq.Quantity); - } - } - } - } - catch (Exception ex) - { - return BadRequest(error: $"{ex.Message}\n\n{ex.Source}\n{ex.StackTrace}"); - } - return new JsonResult(_result.Select(x => new { Sku = x.Key.ToString(), Quantity = x.Value })); - } -} \ No newline at end of file diff --git a/RhSolutions.SkuParser.Api/Program.cs b/RhSolutions.SkuParser.Api/Program.cs index e0acec8..13e0b90 100644 --- a/RhSolutions.SkuParser.Api/Program.cs +++ b/RhSolutions.SkuParser.Api/Program.cs @@ -1,10 +1,11 @@ +using RhSolutions.SkuParser.Abstractions; using RhSolutions.SkuParser.Services; var builder = WebApplication.CreateBuilder(args); builder.Services - .AddKeyedScoped("text/csv") - .AddKeyedScoped("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") - .AddKeyedScoped("application/vnd.ms-excel.sheet.macroenabled.12"); + .AddKeyedScoped("text/csv") + .AddKeyedScoped("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") + .AddKeyedScoped("application/vnd.ms-excel.sheet.macroenabled.12"); builder.Services.AddControllers(); var app = builder.Build(); diff --git a/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs new file mode 100644 index 0000000..e88ba25 --- /dev/null +++ b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs @@ -0,0 +1,26 @@ +using System.Globalization; +using CsvHelper; +using CsvHelper.Configuration; +using RhSolutions.SkuParser.Abstractions; +using RhSolutions.SkuParser.Models; + +namespace RhSolutions.SkuParser.Services; + +/// +/// Парсер артикулов и их количества из файлов *.csv +/// +public class CommonCsvParser : ISkuParser +{ + public Dictionary ParseProducts(IFormFile file) + { + using StreamReader reader = new(file.OpenReadStream()); + var config = new CsvConfiguration(CultureInfo.GetCultureInfo("ru-RU")) + { + HasHeaderRecord = false, + }; + using CsvReader csvReader = new(reader, config); + + return csvReader.GetRecords() + .ToDictionary(pq => new Product() { Sku = pq.Product.Sku }, pq => pq.Quantity); + } +} diff --git a/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs new file mode 100644 index 0000000..206200d --- /dev/null +++ b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs @@ -0,0 +1,80 @@ +using ClosedXML.Excel; +using RhSolutions.SkuParser.Abstractions; +using RhSolutions.SkuParser.Models; + +namespace RhSolutions.SkuParser.Services; + +public class CommonExcelParser : ISkuParser +{ + public Dictionary ParseProducts(IFormFile file) + { + using XLWorkbook workbook = new(file.OpenReadStream()); + IXLWorksheet ws = workbook.Worksheet(1); + + var leftTop = ws.FirstCellUsed()?.Address; + var rightBottom = ws.LastCellUsed()?.Address; + if (new object?[] { leftTop, rightBottom }.Any(x => x == null)) + { + throw new ArgumentException($"Таблица пуста: {file.FileName}"); + } + + var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed(); + var columns = lookupRange.Columns(); + + 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 (skuColumn == null) + { + throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}"); + } + + 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; + + if (quantityColumn == null) + { + throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}"); + } + + Dictionary result = new(); + var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber); + + 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); + } + } + } + + return result; + } +} diff --git a/RhSolutions.SkuParser.Api/Services/CsvParser.cs b/RhSolutions.SkuParser.Api/Services/CsvParser.cs deleted file mode 100644 index 2776721..0000000 --- a/RhSolutions.SkuParser.Api/Services/CsvParser.cs +++ /dev/null @@ -1,24 +0,0 @@ -using System.Globalization; -using CsvHelper; -using CsvHelper.Configuration; -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; - -/// -/// Парсер артикулов и их количества из файлов *.csv -/// -public class CsvParser : ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file) - { - using StreamReader reader = new(file.OpenReadStream()); - var config = new CsvConfiguration(CultureInfo.GetCultureInfo("ru-RU")) - { - HasHeaderRecord = false, - }; - using CsvReader csvReader = new(reader, config); - - return csvReader.GetRecords().ToList(); - } -} diff --git a/RhSolutions.SkuParser.Api/Services/ExcelParser.cs b/RhSolutions.SkuParser.Api/Services/ExcelParser.cs deleted file mode 100644 index fec3885..0000000 --- a/RhSolutions.SkuParser.Api/Services/ExcelParser.cs +++ /dev/null @@ -1,76 +0,0 @@ -using ClosedXML.Excel; -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; - -public class ExcelParser : ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file) - { - using XLWorkbook workbook = new(file.OpenReadStream()); - IXLWorksheet ws = workbook.Worksheet(1); - - var leftTop = ws.FirstCellUsed()?.Address; - var rightBottom = ws.LastCellUsed()?.Address; - if (new object?[] { leftTop, rightBottom }.Any(x => x == null)) - { - throw new ArgumentException($"Таблица пуста: {file.FileName}"); - } - - var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed(); - var columns = lookupRange.Columns(); - - 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 (skuColumn == null) - { - throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}"); - } - - 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; - - if (quantityColumn == null) - { - throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}"); - } - - List result = new(); - var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber); - - 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)) - { - ProductQuantity pq = new() - { - Product = p!, - Quantity = quantity.GetNumber() - }; - result.Add(pq); - } - } - - return result; - } -} diff --git a/RhSolutions.SkuParser.Api/Services/ISkuParser.cs b/RhSolutions.SkuParser.Api/Services/ISkuParser.cs deleted file mode 100644 index 4329135..0000000 --- a/RhSolutions.SkuParser.Api/Services/ISkuParser.cs +++ /dev/null @@ -1,7 +0,0 @@ -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; -public interface ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file); -} diff --git a/RhSolutions.SkuParser.Tests/ExcelParserTests.cs b/RhSolutions.SkuParser.Tests/ExcelParserTests.cs index 60e1e7b..781ea56 100644 --- a/RhSolutions.SkuParser.Tests/ExcelParserTests.cs +++ b/RhSolutions.SkuParser.Tests/ExcelParserTests.cs @@ -4,18 +4,18 @@ namespace RhSolutions.SkuParser.Tests; public class ExcelParserTests { - private static readonly List _expected = new() + private static readonly Dictionary _expected = new() { - new ProductQuantity() {Product= new Product() {Sku = "11303703100"}, Quantity = 2129.5}, - new ProductQuantity() {Product= new Product() {Sku = "11303803100"}, Quantity = 503}, - new ProductQuantity() {Product= new Product() {Sku = "11303903050"}, Quantity = 52}, - new ProductQuantity() {Product= new Product() {Sku = "11080011001"}, Quantity = 2154}, - new ProductQuantity() {Product= new Product() {Sku = "11080021001"}, Quantity = 134}, - new ProductQuantity() {Product= new Product() {Sku = "11080031001"}, Quantity = 6}, - new ProductQuantity() {Product= new Product() {Sku = "11080311001"}, Quantity = 462}, - new ProductQuantity() {Product= new Product() {Sku = "11080611001"}, Quantity = 38}, - new ProductQuantity() {Product= new Product() {Sku = "11080811001"}, Quantity = 24}, - new ProductQuantity() {Product= new Product() {Sku = "11080831001"}, Quantity = 2}, + [new Product() {Sku = "11303703100"}] = 2129.5, + [new Product() {Sku = "11303803100"}] = 503, + [new Product() {Sku = "11303903050"}] = 52, + [new Product() {Sku = "11080011001"}] = 2154, + [new Product() {Sku = "11080021001"}] = 134, + [new Product() {Sku = "11080031001"}] = 6, + [new Product() {Sku = "11080311001"}] = 462, + [new Product() {Sku = "11080611001"}] = 38, + [new Product() {Sku = "11080811001"}] = 24, + [new Product() {Sku = "11080831001"}] = 2, }; [TestCase("simple.xlsx")] @@ -28,10 +28,9 @@ public class ExcelParserTests public void XlsxTests(string filename) { var mockFile = FormFileUtil.GetMockFormFile(filename); - var parser = new ExcelParser(); + var parser = new CommonExcelParser(); var actual = parser.ParseProducts(mockFile.Object); Assert.That(actual.Count, Is.EqualTo(_expected.Count())); - CollectionAssert.AllItemsAreInstancesOfType(actual, typeof(ProductQuantity)); CollectionAssert.AreEqual(_expected, actual); } @@ -39,10 +38,9 @@ public class ExcelParserTests public void CsvTests(string filename) { var mockFile = FormFileUtil.GetMockFormFile(filename); - var parser = new CsvParser(); + var parser = new CommonCsvParser(); var actual = parser.ParseProducts(mockFile.Object); Assert.That(actual.Count, Is.EqualTo(_expected.Count())); - CollectionAssert.AllItemsAreInstancesOfType(actual, typeof(ProductQuantity)); CollectionAssert.AreEqual(_expected, actual); } } -- cgit v1.2.3