aboutsummaryrefslogtreecommitdiff
path: root/src/Models
diff options
context:
space:
mode:
Diffstat (limited to 'src/Models')
-rw-r--r--src/Models/Dialog.cs40
-rw-r--r--src/Models/PriceListBase.cs15
-rw-r--r--src/Models/PriceListHeaders.cs11
-rw-r--r--src/Models/Product.cs35
-rw-r--r--src/Models/ProgressBar.cs22
-rw-r--r--src/Models/ResultBar.cs45
-rw-r--r--src/Models/Sku.cs67
-rw-r--r--src/Models/SkuExtensions.cs12
-rw-r--r--src/Models/SourcePriceList.cs114
-rw-r--r--src/Models/StatusbarBase.cs25
-rw-r--r--src/Models/TargetPriceList.cs39
-rw-r--r--src/Models/WorksheetExtensions.cs41
12 files changed, 466 insertions, 0 deletions
diff --git a/src/Models/Dialog.cs b/src/Models/Dialog.cs
new file mode 100644
index 0000000..abc89b8
--- /dev/null
+++ b/src/Models/Dialog.cs
@@ -0,0 +1,40 @@
+using Microsoft.Office.Interop.Excel;
+using System.Collections.Generic;
+using System.Windows.Forms;
+
+namespace RhSolutions.Models
+{
+ static class Dialog
+ {
+ public static string GetFilePath()
+ {
+ using (OpenFileDialog dialog = new OpenFileDialog())
+ {
+ dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm";
+
+ if (dialog.ShowDialog() == DialogResult.OK)
+ {
+ return dialog.FileName;
+ }
+
+ else return string.Empty;
+ }
+ }
+
+ public static string[] GetMultiplyFiles()
+ {
+ using (OpenFileDialog dialog = new OpenFileDialog())
+ {
+ dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm";
+ dialog.Multiselect = true;
+
+ if (dialog.ShowDialog() == DialogResult.OK)
+ {
+ return dialog.FileNames;
+ }
+
+ else return null;
+ }
+ }
+ }
+}
diff --git a/src/Models/PriceListBase.cs b/src/Models/PriceListBase.cs
new file mode 100644
index 0000000..95f385d
--- /dev/null
+++ b/src/Models/PriceListBase.cs
@@ -0,0 +1,15 @@
+using Microsoft.Office.Interop.Excel;
+
+namespace RhSolutions.Models
+{
+ internal abstract class PriceListBase
+ {
+ public Range AmountCell { get; protected set; }
+ public Range SkuCell { get; protected set; }
+ public Range GroupCell { get; protected set; }
+ public Range NameCell { get; protected set; }
+
+ public Worksheet Sheet { get; protected set; }
+ public string Name { get; protected set; }
+ }
+} \ No newline at end of file
diff --git a/src/Models/PriceListHeaders.cs b/src/Models/PriceListHeaders.cs
new file mode 100644
index 0000000..c9a8370
--- /dev/null
+++ b/src/Models/PriceListHeaders.cs
@@ -0,0 +1,11 @@
+namespace RhSolutions.Models
+{
+ internal static class PriceListHeaders
+ {
+ public static readonly string Amount = "Кол-во";
+ public static readonly string OldSku = "Прежний материал";
+ public static readonly string Sku = "Актуальный материал";
+ public static readonly string Group = "Программа";
+ public static readonly string Name = "Наименование";
+ }
+} \ No newline at end of file
diff --git a/src/Models/Product.cs b/src/Models/Product.cs
new file mode 100644
index 0000000..2f092d1
--- /dev/null
+++ b/src/Models/Product.cs
@@ -0,0 +1,35 @@
+using System.Linq;
+
+namespace RhSolutions.Models
+{
+ public class Product
+ {
+ public string ProductLine { get; set; }
+ public string ProductSku { get; set; }
+ public string Name { get; set; }
+
+ public override bool Equals(object obj)
+ {
+ if (obj as Product == null)
+ return false;
+
+ Product other = obj as Product;
+
+ return ProductLine == other.ProductLine &&
+ ProductSku == other.ProductSku &&
+ Name == other.Name;
+ }
+
+ public override int GetHashCode()
+ {
+ string[] properties = new[]
+ {
+ ProductLine,
+ ProductSku,
+ Name
+ };
+
+ return string.Concat(properties.Where(p => p != null)).GetHashCode();
+ }
+ }
+} \ No newline at end of file
diff --git a/src/Models/ProgressBar.cs b/src/Models/ProgressBar.cs
new file mode 100644
index 0000000..82012e5
--- /dev/null
+++ b/src/Models/ProgressBar.cs
@@ -0,0 +1,22 @@
+namespace RhSolutions.Models
+{
+ internal class ProgressBar : StatusbarBase
+ {
+ private double CurrentProgress { get; set; }
+ private readonly double TaskWeight;
+ private readonly string Message;
+
+ public ProgressBar(string message, int weight)
+ {
+ Message = message;
+ TaskWeight = weight;
+ CurrentProgress = 0;
+ }
+
+ public override void Update()
+ {
+ double percent = ++CurrentProgress / TaskWeight * 100;
+ Excel.StatusBar = $"{Message} Выполнено {percent:#.#} %";
+ }
+ }
+}
diff --git a/src/Models/ResultBar.cs b/src/Models/ResultBar.cs
new file mode 100644
index 0000000..655540c
--- /dev/null
+++ b/src/Models/ResultBar.cs
@@ -0,0 +1,45 @@
+using System;
+using System.Text;
+
+namespace RhSolutions.Models
+{
+ internal class ResultBar : StatusbarBase
+ {
+ private int Success { get; set; }
+ private int Replaced { get; set; }
+ private int NotFound { get; set; }
+
+ public ResultBar()
+ {
+ Success = 0;
+ Replaced = 0;
+ NotFound = 0;
+ }
+
+ public void IncrementSuccess() => Success++;
+ public void IncrementReplaced() => Replaced++;
+ public void IncrementNotFound() => NotFound++;
+
+ public override void Update()
+ {
+ StringBuilder sb = new StringBuilder();
+
+ if (Success > 0)
+ {
+ sb.Append($"Успешно экспортировано {Success} артикулов. ");
+ }
+
+ if (Replaced > 0)
+ {
+ sb.Append($"Заменено {Replaced} артикулов. ");
+ }
+
+ if (NotFound > 0)
+ {
+ sb.Append($"Не найдено {NotFound} артикулов.");
+ }
+
+ Excel.StatusBar = sb.ToString();
+ }
+ }
+}
diff --git a/src/Models/Sku.cs b/src/Models/Sku.cs
new file mode 100644
index 0000000..1c5e477
--- /dev/null
+++ b/src/Models/Sku.cs
@@ -0,0 +1,67 @@
+using System.Text.RegularExpressions;
+
+namespace RhSolutions.Models
+{
+ internal class Sku
+ {
+ public string Article { get; private set; }
+ public string Variant { get; private set; }
+
+ public Sku(string article, string variant)
+ {
+ Article = article;
+ Variant = variant;
+ }
+
+ public static bool TryParse(string line, out Sku rehauSku)
+ {
+ Match match;
+ match = Regex.Match(line, @"\b[1]\d{6}[1]\d{3}\b");
+ if (match.Success)
+ {
+ string sku = match.Value.Substring(1, 6);
+ string variant = match.Value.Substring(8, 3);
+ rehauSku = new Sku(sku, variant);
+ return true;
+ }
+
+ match = Regex.Match(line, @"\b\d{6}\D\d{3}\b");
+ if (match.Success)
+ {
+ string sku = match.Value.Substring(0, 6);
+ string variant = match.Value.Substring(7, 3);
+ rehauSku = new Sku(sku, variant);
+ return true;
+ }
+
+ match = Regex.Match(line, @"\b\d{9}\b");
+ if (match.Success)
+ {
+ string sku = match.Value.Substring(0, 6);
+ string variant = match.Value.Substring(6, 3);
+ rehauSku = new Sku(sku, variant);
+ return true;
+ }
+
+ match = Regex.Match(line, @"\b\d{6}\b");
+ if (match.Success)
+ {
+ string sku = match.Value.Substring(0, 6);
+ string variant = "001";
+ rehauSku = new Sku(sku, variant);
+ return true;
+ }
+
+ else
+ {
+ rehauSku = null;
+ return false;
+ }
+ }
+
+ public override string ToString()
+ {
+ return $"1{Article}1{Variant}";
+ }
+ }
+} \ No newline at end of file
diff --git a/src/Models/SkuExtensions.cs b/src/Models/SkuExtensions.cs
new file mode 100644
index 0000000..160dc16
--- /dev/null
+++ b/src/Models/SkuExtensions.cs
@@ -0,0 +1,12 @@
+using System.Text.RegularExpressions;
+
+namespace RhSolutions.Models
+{
+ static class SkuExtensions
+ {
+ public static bool IsRehauSku(this string line)
+ {
+ return Regex.IsMatch(line, @"^[1]\d{6}[1]\d{3}$");
+ }
+ }
+} \ No newline at end of file
diff --git a/src/Models/SourcePriceList.cs b/src/Models/SourcePriceList.cs
new file mode 100644
index 0000000..d210c10
--- /dev/null
+++ b/src/Models/SourcePriceList.cs
@@ -0,0 +1,114 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+
+namespace RhSolutions.Models
+{
+ internal class SourcePriceList : PriceListBase
+ {
+ public Dictionary<Product, double> PositionAmount { get; private set; }
+
+ public SourcePriceList(Workbook workbook)
+ {
+ if (workbook == null)
+ {
+ throw new ArgumentException($"Нет рабочего файла");
+ }
+
+ Sheet = workbook.ActiveSheet;
+ Name = workbook.Name;
+
+ Range[] cells = new[]
+ {
+ AmountCell = Sheet.Cells.Find(PriceListHeaders.Amount),
+ SkuCell = Sheet.Cells.Find(PriceListHeaders.Sku),
+ GroupCell = Sheet.Cells.Find(PriceListHeaders.Group),
+ NameCell = Sheet.Cells.Find(PriceListHeaders.Name)
+ };
+
+ if (cells.Any(x => x == null))
+ {
+ throw new ArgumentException($"Файл {Name} не распознан");
+ }
+
+ CreatePositionsDict();
+ }
+
+ public static List<SourcePriceList> GetSourceLists(string[] files)
+ {
+ var ExcelApp = (Application)ExcelDnaUtil.Application;
+ ProgressBar bar = new ProgressBar("Открываю исходные файлы...", files.Length);
+
+ List<SourcePriceList> sourceFiles = new List<SourcePriceList>();
+
+ foreach (string file in files)
+ {
+ ExcelApp.ScreenUpdating = false;
+ Workbook wb = ExcelApp.Workbooks.Open(file);
+ try
+ {
+ SourcePriceList priceList = new SourcePriceList(wb);
+ sourceFiles.Add(priceList);
+ wb.Close();
+ bar.Update();
+ }
+ catch (Exception ex)
+ {
+ System.Windows.Forms.MessageBox.Show
+ (ex.Message,
+ "Ошибка открытия исходного прайс-листа",
+ System.Windows.Forms.MessageBoxButtons.OK,
+ System.Windows.Forms.MessageBoxIcon.Information);
+ wb.Close();
+ bar.Update();
+ }
+ ExcelApp.ScreenUpdating = true;
+ }
+
+ return sourceFiles;
+ }
+
+ private void CreatePositionsDict()
+ {
+ PositionAmount = new Dictionary<Product, double>();
+
+ for (int row = AmountCell.Row + 1; row <= Sheet.Cells[Sheet.Rows.Count, AmountCell.Column].End[XlDirection.xlUp].Row; row++)
+ {
+ double? amount = Sheet.Cells[row, AmountCell.Column].Value2 as double?;
+
+ if (amount != null && amount.Value != 0)
+ {
+ object group = Sheet.Cells[row, GroupCell.Column].Value2;
+ object name = Sheet.Cells[row, NameCell.Column].Value2;
+ object sku = Sheet.Cells[row, SkuCell.Column].Value2;
+
+ if (group == null || name == null || sku == null)
+ continue;
+
+ if (!sku.ToString().IsRehauSku())
+ continue;
+
+ Product p = new Product
+ {
+ ProductSku = sku.ToString(),
+ ProductLine = group.ToString(),
+ Name = name.ToString()
+ };
+
+ if (PositionAmount.ContainsKey(p))
+ {
+ PositionAmount[p] += amount.Value;
+ }
+
+ else
+ {
+ PositionAmount.Add(p, amount.Value);
+ }
+ }
+ }
+ }
+ }
+}
+
diff --git a/src/Models/StatusbarBase.cs b/src/Models/StatusbarBase.cs
new file mode 100644
index 0000000..a1be5ed
--- /dev/null
+++ b/src/Models/StatusbarBase.cs
@@ -0,0 +1,25 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Threading;
+using System.Threading.Tasks;
+
+namespace RhSolutions.Models
+{
+ internal abstract class StatusbarBase : IDisposable
+ {
+ protected Application Excel = RhSolutionsAddIn.Excel;
+
+ public abstract void Update();
+
+ private static void ResetStatusBar()
+ {
+ RhSolutionsAddIn.Excel.StatusBar = false;
+ }
+
+ public void Dispose()
+ {
+ Task.Delay(5000).ContinueWith(t => ResetStatusBar());
+ }
+ }
+}
diff --git a/src/Models/TargetPriceList.cs b/src/Models/TargetPriceList.cs
new file mode 100644
index 0000000..4754049
--- /dev/null
+++ b/src/Models/TargetPriceList.cs
@@ -0,0 +1,39 @@
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Linq;
+
+namespace RhSolutions.Models
+{
+ internal class TargetPriceList : PriceListBase
+ {
+ public Range OldSkuCell { get; private set; }
+
+ public TargetPriceList(Workbook workbook)
+ {
+ if (workbook == null)
+ {
+ throw new ArgumentException("Невозможно открыть книгу шаблонного файла. " +
+ "Возможно открыт файл с именем, совпадающим с именем шаблонного файла.");
+ }
+
+ Sheet = workbook.ActiveSheet;
+ Name = workbook.FullName;
+
+ Range[] cells = new[]
+ {
+ AmountCell = Sheet.Cells.Find(PriceListHeaders.Amount),
+ SkuCell = Sheet.Cells.Find(PriceListHeaders.Sku),
+ GroupCell = Sheet.Cells.Find(PriceListHeaders.Group),
+ NameCell = Sheet.Cells.Find(PriceListHeaders.Name)
+ };
+
+ OldSkuCell = Sheet.Cells.Find(PriceListHeaders.OldSku);
+
+ if (cells.Any(x => x == null))
+ {
+ throw new ArgumentException($"Шаблон {Name} не является прайс-листом");
+ }
+ }
+ }
+}
+
diff --git a/src/Models/WorksheetExtensions.cs b/src/Models/WorksheetExtensions.cs
new file mode 100644
index 0000000..6b5fc3e
--- /dev/null
+++ b/src/Models/WorksheetExtensions.cs
@@ -0,0 +1,41 @@
+using Microsoft.Office.Interop.Excel;
+using RhSolutions.Models;
+using System.Linq;
+
+namespace RhSolutions.Services
+{
+ public static class WorksheetExtensions
+ {
+ public static bool IsRehauSource(this Worksheet worksheet)
+ {
+ Range amountCell;
+ Range skuCell;
+ Range groupCell;
+ Range nameCell;
+
+ Range[] cells = new[]
+ {
+ amountCell = worksheet.Cells.Find(PriceListHeaders.Amount),
+ skuCell = worksheet.Cells.Find(PriceListHeaders.Sku),
+ groupCell = worksheet.Cells.Find(PriceListHeaders.Group),
+ nameCell = worksheet.Cells.Find(PriceListHeaders.Name)
+ };
+
+ return cells.All(x => x != null);
+ }
+
+ public static void AddValue(this Range range, double value)
+ {
+ if (range.Value2 == null)
+ {
+ range.Value2 = value;
+ }
+
+ else
+ {
+ range.Value2 += value;
+ }
+ }
+ }
+}
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-20 01:54:31 +0000