RhSolutions-AddIn/RhSolutions.AddIn/Services/GuessReader.cs

234 lines
6.7 KiB
C#
Raw Permalink Normal View History

2023-05-30 09:01:49 +03:00
using System.IO;
2023-05-22 10:24:18 +03:00
namespace RhSolutions.Services;
2023-05-23 07:07:16 +03:00
public class GuessReader : IReader
2023-05-22 10:24:18 +03:00
{
private readonly Application _application;
private ProgressBar _progressBar;
2023-05-23 07:07:16 +03:00
public GuessReader(Application application)
2023-05-22 10:24:18 +03:00
{
_application = application;
}
public Dictionary<Product, double> ReadProducts(Range range)
{
_progressBar = new("Ищу колонку с артикулами...", range.Columns.Count);
2023-05-22 10:24:18 +03:00
int? productColumnIndex = null;
for (int column = 1; column < range.Columns.Count + 1; column++)
{
_progressBar.Update();
2023-05-26 06:33:35 +03:00
if (IsProductColumn(range.Columns[column]))
2023-05-22 10:24:18 +03:00
{
productColumnIndex = column;
2023-05-26 06:33:35 +03:00
break;
2023-05-22 10:24:18 +03:00
}
}
2023-05-23 09:26:08 +03:00
if (productColumnIndex == null)
2023-05-22 10:24:18 +03:00
{
2023-05-23 09:26:08 +03:00
throw new ArgumentException("Колонка с артикулом не определена");
}
2023-05-26 06:33:35 +03:00
int? amountColumnIndex = null;
int currentIndex = productColumnIndex.Value + 1;
_progressBar = new("Ищу колонку с количеством...", range.Columns.Count);
2023-05-26 06:33:35 +03:00
while (currentIndex > 0)
2023-05-23 09:26:08 +03:00
{
_progressBar.Update();
2023-05-26 06:33:35 +03:00
if (currentIndex > range.Columns.Count + 1)
{
currentIndex = productColumnIndex.Value - 1;
continue;
}
if (currentIndex > productColumnIndex)
{
if (IsAmountColumn(range.Columns[currentIndex]))
2023-05-26 06:33:35 +03:00
{
amountColumnIndex = currentIndex;
2023-05-26 06:33:35 +03:00
break;
}
else currentIndex++;
2023-05-26 06:33:35 +03:00
}
else
{
if (IsAmountColumn(range.Columns[currentIndex]))
2023-05-26 06:33:35 +03:00
{
amountColumnIndex = currentIndex;
2023-05-26 06:33:35 +03:00
break;
}
else currentIndex--;
2023-05-26 06:33:35 +03:00
}
2023-05-22 10:24:18 +03:00
}
2023-05-26 06:33:35 +03:00
if (amountColumnIndex == null)
2023-05-22 10:24:18 +03:00
{
2023-05-26 06:33:35 +03:00
throw new ArgumentException("Колонка с количеством не определена");
2023-05-22 10:24:18 +03:00
}
else
{
return GetDictionaryFromColumns(range.Columns[productColumnIndex],
range.Columns[amountColumnIndex]);
}
}
private bool IsProductColumn(Range column)
{
int successCounter = 0;
2023-05-23 15:02:14 +03:00
var cells = column.Value2;
2023-05-23 07:33:49 +03:00
2023-05-23 07:55:14 +03:00
if (cells == null)
{
return false;
}
2023-05-22 10:24:18 +03:00
for (int row = 1; row < column.Rows.Count + 1; row++)
2023-05-23 15:02:14 +03:00
{
object currentCell = column.Rows.Count == 1 ? cells : cells[row, 1];
2023-05-23 07:33:49 +03:00
if (currentCell == null)
{
continue;
}
2023-05-30 09:01:49 +03:00
if (ProductSku.TryParse(currentCell.ToString(), out _))
2023-05-22 10:24:18 +03:00
{
successCounter++;
}
if (successCounter > 2)
{
return true;
}
}
return successCounter > 0;
}
2023-05-23 09:26:08 +03:00
private bool IsAmountColumn(Range column)
2023-05-22 10:24:18 +03:00
{
2023-05-23 09:26:08 +03:00
int successCounter = 0;
2023-05-23 15:02:14 +03:00
var cells = column.Value2;
double maxValue = 30000;
2023-05-23 07:55:14 +03:00
if (cells == null)
{
return false;
}
2023-05-30 09:01:49 +03:00
if (column.Rows.Count == 1)
2023-05-22 10:24:18 +03:00
{
2023-05-30 09:01:49 +03:00
double? value = cells as double?;
2023-05-23 07:33:49 +03:00
2023-05-30 09:01:49 +03:00
return value != null
&& value != 0
&& value < maxValue;
}
2023-05-22 10:24:18 +03:00
2023-05-30 09:01:49 +03:00
else
{
for (int row = 1; row < column.Rows.Count + 1; row++)
2023-05-22 10:24:18 +03:00
{
2023-05-30 09:01:49 +03:00
object currentCell = cells[row, 1];
double? value = currentCell as double?;
2023-05-23 09:26:08 +03:00
if (value == null
|| value == 0
|| value > maxValue)
2023-05-30 09:01:49 +03:00
{
continue;
}
2023-06-06 08:17:52 +03:00
if (++successCounter > 3)
2023-05-30 09:01:49 +03:00
{
return true;
}
2023-05-23 15:02:14 +03:00
}
}
2023-06-06 08:17:52 +03:00
return successCounter > 1;
2023-05-22 10:24:18 +03:00
}
private Dictionary<Product, double> GetDictionaryFromColumns(Range productColumn, Range amountColumn)
{
Dictionary<Product, double> result = new();
2023-05-26 07:50:23 +03:00
var firstRowIndex = 1;
var lastRowIndex = amountColumn.Worksheet
2023-05-26 07:50:23 +03:00
.Cells[amountColumn.Worksheet.Rows.Count, amountColumn.Column]
.End[XlDirection.xlUp].Row;
_progressBar = new("Заполняю словарь значений...", lastRowIndex);
2023-05-26 07:50:23 +03:00
Worksheet worksheet = amountColumn.Worksheet;
2023-05-26 07:50:23 +03:00
for (int row = firstRowIndex; row < lastRowIndex + 1; row++)
2023-05-22 10:24:18 +03:00
{
2023-05-23 15:02:14 +03:00
_progressBar.Update();
2023-05-26 07:50:23 +03:00
object currentAmountCell = worksheet.Cells[row, amountColumn.Column].Value2;
object currentProductCell = worksheet.Cells[row, productColumn.Column].Value2;
double? amountValue = currentAmountCell as double?;
if (amountValue == null || amountValue == 0 || currentProductCell == null)
2023-05-24 10:48:52 +03:00
{
continue;
}
2023-05-24 06:39:50 +03:00
2023-05-23 07:33:49 +03:00
if (ProductSku.TryParse(currentProductCell.ToString(), out IEnumerable<ProductSku> skus))
2023-05-22 10:24:18 +03:00
{
Product p = new(skus.First())
{
Name = "Распознанный артикул"
};
if (result.ContainsKey(p))
{
2023-05-24 06:39:50 +03:00
result[p] += amountValue.Value;
2023-05-22 10:24:18 +03:00
}
else
{
2023-05-24 06:39:50 +03:00
result.Add(p, amountValue.Value);
2023-05-22 10:24:18 +03:00
}
}
}
return result;
}
2024-05-27 23:39:43 +03:00
public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(IEnumerable<Worksheet> worksheets)
2023-05-22 10:24:18 +03:00
{
List<(string, Dictionary<Product, double>)> result = new();
foreach (Worksheet worksheet in worksheets)
{
string wbName = Path.GetFileNameWithoutExtension(
worksheet.Parent.Name);
Range range = worksheet.UsedRange;
var productsDict = ReadProducts(range);
result.Add((wbName, productsDict));
}
return result;
}
2024-05-27 23:39:43 +03:00
public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(string[] files)
2023-05-22 10:24:18 +03:00
{
_progressBar = new("Открываю исходные файлы...", files.Length);
List<Worksheet> worksheets = new();
_application.ScreenUpdating = false;
foreach (string file in files)
{
Workbook wb = _application.Workbooks.Open(file);
worksheets.Add(wb.ActiveSheet);
_progressBar.Update();
}
_application.ScreenUpdating = true;
var result = ReadProducts(worksheets);
foreach (var ws in worksheets)
{
ws.Parent.Close();
}
return result;
}
public void Dispose()
{
_progressBar?.Dispose();
}
}