public static void ConvertImport(BusinessContext bc, string path)
{
path = Reports.Helpers.ConvertXlsToXlsx(path);
var existingFile = new FileInfo(path);
using (var package = new ExcelPackage(existingFile))
{
var workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
var sheet = workBook.Worksheets.First();
int indexRow = 6;
string number = GetValue(sheet.Cells[indexRow, 1].Value);
while (!string.IsNullOrWhiteSpace(number))
{
string article = GetValue(sheet.Cells[indexRow, 2].Value);
if (string.IsNullOrWhiteSpace(article) || article == "№ Fenox")
{
indexRow++;
number = GetValue(sheet.Cells[indexRow, 1].Value);
continue;
}
string description = GetValue(sheet.Cells[indexRow, 3].Value);
string originalNumber = GetValue(sheet.Cells[indexRow, 4].Value);
string material = GetValue(sheet.Cells[indexRow, 5].Value);
string attachment = GetValue(sheet.Cells[indexRow, 6].Value);
string factoryNumber = GetValue(sheet.Cells[indexRow, 7].Value);
string crossNumber = GetValue(sheet.Cells[indexRow, 8].Value);
string countInBox = GetValue(sheet.Cells[indexRow, 12].Value);
if (bc.GetDirectoryCarPart(article, null) == null)
{
bc.AddDirectoryCarPart(article, null, description, originalNumber, factoryNumber,
crossNumber, material, attachment, countInBox, true);
}
indexRow++;
}
}
}
}
}
public static void ConvertRussian(BusinessContext bc, string path)
{
path = Reports.Helpers.ConvertXlsToXlsx(path);
var existingFile = new FileInfo(path);
using (var package = new ExcelPackage(existingFile))
{
var workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
var sheet = workBook.Worksheets.First(s => s.Name == "Печать цен Отечественные");
int indexRow = 8;
string name = GetValue(sheet.Cells[indexRow, 2].Value);
string number = GetValue(sheet.Cells[indexRow, 1].Value);
string description = GetValue(sheet.Cells[indexRow, 2].Value);
string originalNumber = GetValue(sheet.Cells[indexRow, 3].Value);
string article = GetValue(sheet.Cells[indexRow, 4].Value);
string mark = GetValue(sheet.Cells[indexRow, 5].Value);
string material = GetValue(sheet.Cells[indexRow, 7].Value);
string attachment = GetValue(sheet.Cells[indexRow, 8].Value);
string factoryNumber = null;
string crossNumber = null;
string countInBox = GetValue(sheet.Cells[indexRow, 11].Value);
while (!(string.IsNullOrWhiteSpace(number) && string.IsNullOrWhiteSpace(name)))
{
if (string.IsNullOrWhiteSpace(number) && !string.IsNullOrWhiteSpace(name))
{
indexRow++;
name = GetValue(sheet.Cells[indexRow, 2].Value);
number = GetValue(sheet.Cells[indexRow, 1].Value);
continue;
}
description = GetValue(sheet.Cells[indexRow, 2].Value) ?? description;
originalNumber = GetValue(sheet.Cells[indexRow, 3].Value) ?? originalNumber;
article = GetValue(sheet.Cells[indexRow, 4].Value) ?? article;
mark = GetValue(sheet.Cells[indexRow, 5].Value) ?? mark;
material = GetValue(sheet.Cells[indexRow, 7].Value) ?? material;
attachment = GetValue(sheet.Cells[indexRow, 8].Value) ?? attachment;
countInBox = GetValue(sheet.Cells[indexRow, 11].Value) ?? countInBox;
if (bc.GetDirectoryCarPart(article, mark) == null)
{
bc.AddDirectoryCarPart(article, mark, description, originalNumber, factoryNumber,
crossNumber, material, attachment, countInBox, false);
}
indexRow++;
name = GetValue(sheet.Cells[indexRow, 2].Value);
number = GetValue(sheet.Cells[indexRow, 1].Value);
}
}
}
}
}
public static void ConvertingCarPartRemainsToDb(BusinessContext bc, string path)
{
path = Reports.Helpers.ConvertXlsToXlsx(path);
var existingFile = new FileInfo(path);
using (var package = new ExcelPackage(existingFile))
{
var workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
for (var date = new DateTime(2015, 9, 1); date <= new DateTime(2015, 10, 1); date = date.AddMonths(1))
{
var sheet = workBook.Worksheets.First(s => s.Name == date.ToString("MMMM yyyy"));
var carParts = bc.GetDirectoryCarParts().ToList();
var excelCarParts = new List <DirectoryCarPart>();
var excelRemains = new List <InfoLastMonthDayRemain>();
int indexRow = 3;
using (var sw = new StreamWriter("articles.txt"))
{
sw.WriteLine();
}
string article = GetValue(sheet.Cells[indexRow, 1].Value);
while (!string.IsNullOrWhiteSpace(article))
{
int indexDigit = -1;
for (int i = 0; i < article.Length; i++)
{
if (char.IsDigit(article[i]))
{
indexDigit = i;
}
else if (char.IsLetter(article[i]) && indexDigit != -1)
{
indexDigit = i - 1;
break;
}
}
string newArticle = article.Substring(0, indexDigit + 1);
string mark = indexDigit != (article.Length - 1)
? article.Substring(indexDigit + 1)
: null;
var equalCarPart = carParts.FirstOrDefault(p => ((p.Article + p.Mark) == article) ||
(p.Mark == null &&
p.Article == newArticle));
if (equalCarPart == null)
{
equalCarPart = bc.AddDirectoryCarPart(newArticle, mark,
GetValue(sheet.Cells[indexRow, 5].Value),
null, null, null, null, null, null, string.IsNullOrWhiteSpace(mark));
using (var sw = new StreamWriter("articles.txt", true))
{
sw.WriteLine(equalCarPart.Article + equalCarPart.Mark);
}
}
int remain = int.Parse(GetValue(sheet.Cells[indexRow, 6].Value) ?? "0");
excelCarParts.Add(equalCarPart);
excelRemains.Add(new InfoLastMonthDayRemain
{
Count = remain,
DirectoryCarPartId = equalCarPart.Id,
Date = date
});
indexRow++;
article = GetValue(sheet.Cells[indexRow, 1].Value);
}
int indexColumn = 7;
var containers = new List <InfoContainer>();
while (GetValue(sheet.Cells[2, indexColumn].Value) != "И того приход")
{
var containerName = GetValue(sheet.Cells[2, indexColumn].Value);
if (containerName != null)
{
if (!containerName.Contains("от"))
{
indexColumn++;
continue;
}
string name = containerName.Substring(0, containerName.IndexOf("от")).Trim();
DateTime dateContainer;
if (containerName.IndexOf(" ", containerName.IndexOf("от") + 3) != -1)
{
dateContainer = DateTime.Parse(containerName.Substring(containerName.IndexOf("от") + 3,
containerName.IndexOf(" ", containerName.IndexOf("от") + 3) -
(containerName.IndexOf("от") + 3))
.Replace(",", ".").Trim(new[] { ' ', '.' }));
}
else
{
dateContainer = DateTime.Parse(containerName.Substring(containerName.IndexOf("от") + 3));
}
string description;
if (dateContainer.Date < date)
{
dateContainer = date;
description = containerName.Substring(containerName.IndexOf("от") + 3).Replace(",", " ").Trim();
}
else
{
description = containerName.Substring(containerName.IndexOf("от") + 11).Replace(",", " ").Trim();
}
var containerCarParts = new List <CurrentContainerCarPart>();
for (int row = 3; row < 1227; row++)
{
var countCarPart = GetValue(sheet.Cells[row, indexColumn].Value);
int result;
if (int.TryParse(countCarPart, out result))
{
containerCarParts.Add(new CurrentContainerCarPart
{
CountCarParts = result,
DirectoryCarPartId = excelCarParts[row - 3].Id,
});
}
}
var container = new InfoContainer
{
DatePhysical = dateContainer,
DateOrder = dateContainer,
IsIncoming = true,
Name = name,
Description = description,
CarParts = containerCarParts
};
containers.Add(container);
}
indexColumn++;
}
indexColumn++;
while (GetValue(sheet.Cells[2, indexColumn].Value) != "Расход")
{
var containerName = GetValue(sheet.Cells[2, indexColumn].Value);
if (containerName != null)
{
if (!containerName.Contains("от"))
{
indexColumn++;
continue;
}
string name = containerName.Substring(0, containerName.IndexOf("от")).Trim();
DateTime dateContainer;
if (containerName.IndexOf(" ", containerName.IndexOf("от") + 3) != -1)
{
dateContainer = DateTime.Parse(containerName.Substring(containerName.IndexOf("от") + 3,
containerName.IndexOf(" ", containerName.IndexOf("от") + 3) -
(containerName.IndexOf("от") + 3))
.Replace(",", ".").Trim(new[] { ' ', '.' }));
}
else
{
dateContainer = DateTime.Parse(containerName.Substring(containerName.IndexOf("от") + 3));
}
string description;
if (dateContainer.Date < date)
{
dateContainer = date;
description = containerName.Substring(containerName.IndexOf("от") + 3).Replace(",", " ").Trim();
}
else
{
description = containerName.Substring(containerName.IndexOf("от") + 11).Replace(",", " ").Trim();
}
var containerCarParts = new List <CurrentContainerCarPart>();
for (int row = 3; row < 1227; row++)
{
var countCarPart = GetValue(sheet.Cells[row, indexColumn].Value);
if (countCarPart != null)
{
int result;
if (int.TryParse(countCarPart, out result))
{
containerCarParts.Add(new CurrentContainerCarPart
{
CountCarParts = result,
DirectoryCarPartId = excelCarParts[row - 3].Id,
});
}
}
}
var container = new InfoContainer
{
DatePhysical = dateContainer,
DateOrder = dateContainer,
IsIncoming = false,
Name = name,
Description = description,
CarParts = containerCarParts
};
containers.Add(container);
}
indexColumn++;
}
bc.RemoveInfoLastMonthDayRemains(date.Year, date.Month);
bc.DataContext.BulkInsert(excelRemains);
bc.RemoveContainers(date.Year, date.Month);
bc.DataContext.BulkInsert(containers);
var containersDB = bc.GetInfoContainers(containers).ToList();
foreach (var container in containersDB)
{
var c = container;
bc.DataContext.BulkInsert(c.CarParts.Select(p => new CurrentContainerCarPart
{
CountCarParts = p.CountCarParts,
DirectoryCarPartId = p.DirectoryCarPartId,
InfoContainerId = c.Id
}));
}
}
}
}
}
}