1 files changed, 72 insertions, 0 deletions
diff --git a/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs b/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs
new file mode 100644
index 0000000..684c4c2
--- /dev/null
+++ b/RhSolutions.SkuParser.Api/Services/BsExcelParser.cs
@@ -0,0 +1,72 @@
+using System.Data;
+using ClosedXML.Excel;
+using RhSolutions.SkuParser.Abstractions;
+using RhSolutions.SkuParser.Models;
+
+namespace RhSolutions.SkuParser.Api.Services;
+
+public class BsExcelParser : ISkuParser
+{
+	private IConfiguration configuration;
+	// private Dictionary<Product, double> result;
+	private const int rowsLookupCount = 20;
+	private const decimal vat = 1.2M;
+	public BsExcelParser(IConfiguration configuration)
+	{
+		this.configuration = configuration;
+		// result = new();
+	}
+
+	public Dictionary<Product, double> ParseProducts(IFormFile file)
+	{
+		using XLWorkbook workbook = new(file.OpenReadStream());
+		IXLWorksheet ws = 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}");
+		}
+
+		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();
+
+		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);
+			}
+		}
+		return result;
+	}
+}