From a542bfb7f4ceee8b49cf8fcadf64ffb72cc97da5 Mon Sep 17 00:00:00 2001
From: Serghei Cebotari <serghei@cebotari.ru>
Date: Tue, 14 Jan 2025 14:01:01 +0000
Subject: Common Parsing Method

---
 .../Services/CommonExcelParser.cs                  | 80 ++++++++++++++++++++++
 1 file changed, 80 insertions(+)
 create mode 100644 RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs

(limited to 'RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs')

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<Product, double> 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<Product, double> 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;
+	}
+}
-- 
cgit v1.2.3