public static DateTime ConvertPriceLiquides(BusinessContext bc, string path, Currency currency)
{
path = Reports.Helpers.ConvertXlsToXlsx(path);
DateTime priceDate = DateTime.Now;
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 == "Жидкости, смазки, щетки");
string date = path.Substring(path.LastIndexOf("(") + 1, 10);
if (!DateTime.TryParse(date, out priceDate))
{
throw new Exception();
}
int indexRow = 7;
string header = GetValue(sheet.Cells[indexRow, 2].Value);
string carPartName = GetValue(sheet.Cells[indexRow, 1].Value);
string article = GetValue(sheet.Cells[indexRow, 2].Value);
string description = GetValue(sheet.Cells[indexRow, 3].Value);
string material = GetValue(sheet.Cells[indexRow, 6].Value);
string crossNumber = GetValue(sheet.Cells[indexRow, 14].Value);
string countInBox = GetValue(sheet.Cells[indexRow, 10].Value);
string originalNumber = GetValue(sheet.Cells[indexRow, 13].Value);
var carParts = bc.GetDirectoryCarParts().ToList();
var currentCarParts = bc.GetCurrentCarParts().ToList();
var carPartsMemory = new List <DirectoryCarPart>();
var currentCarPartsMemory = new List <CurrentCarPart>();
while (!(string.IsNullOrWhiteSpace(carPartName) && string.IsNullOrWhiteSpace(header)))
{
if (string.IsNullOrWhiteSpace(carPartName) && !string.IsNullOrWhiteSpace(header))
{
indexRow++;
header = GetValue(sheet.Cells[indexRow, 2].Value);
carPartName = GetValue(sheet.Cells[indexRow, 1].Value);
continue;
}
description = GetValue(sheet.Cells[indexRow, 3].Value) ?? description;
originalNumber = GetValue(sheet.Cells[indexRow, 13].Value) ?? originalNumber;
article = GetValue(sheet.Cells[indexRow, 2].Value) ?? article;
material = GetValue(sheet.Cells[indexRow, 6].Value) ?? material;
countInBox = GetValue(sheet.Cells[indexRow, 10].Value) ?? countInBox;
crossNumber = GetValue(sheet.Cells[indexRow, 14].Value);
var equalCarPart = carParts.FirstOrDefault(p => p.FullCarPartName == article);
if (equalCarPart == null)
{
equalCarPart = new DirectoryCarPart
{
Article = article,
Mark = null,
Description = description,
OriginalNumber = originalNumber,
Material = material,
CrossNumber = crossNumber,
CountInBox = countInBox,
IsImport = true
};
carPartsMemory.Add(equalCarPart);
}
if (equalCarPart.Description == null)
{
equalCarPart.Description = description;
equalCarPart.OriginalNumber = originalNumber;
equalCarPart.Material = material;
equalCarPart.CrossNumber = crossNumber;
equalCarPart.CountInBox = countInBox;
bc.SaveChanges();
}
double priceBase = double.Parse(GetValue(sheet.Cells[indexRow, 9].Value));
var lastCurrentCarPart = currentCarParts.Where(c => c.DirectoryCarPartId == equalCarPart.Id)
.OrderByDescending(c => c.Date)
.FirstOrDefault(c => priceDate.Date >= c.Date);
if (lastCurrentCarPart == null || lastCurrentCarPart.PriceBase != priceBase)
{
var currentCarPart = bc.AddCurrentCarPartNoSave(priceDate, priceBase, null, null, currency, article);
currentCarPartsMemory.Add(currentCarPart);
}
indexRow++;
header = GetValue(sheet.Cells[indexRow, 2].Value);
carPartName = GetValue(sheet.Cells[indexRow, 1].Value);
}
foreach (var directoryCarPart in carPartsMemory)
{
bc.DataContext.DirectoryCarParts.Add(directoryCarPart);
}
bc.SaveChanges();
// bc.DataContext.BulkInsert(carPartsMemory);
carParts = bc.GetDirectoryCarParts().ToList();
foreach (var currentCarPart in currentCarPartsMemory)
{
currentCarPart.DirectoryCarPartId = carParts.First(p => p.FullCarPartName == currentCarPart.FullName).Id;
}
bc.DataContext.CurrentCarParts.AddRange(currentCarPartsMemory);
bc.SaveChanges();
//bc.DataContext.BulkInsert(currentCarPartsMemory);
}
}
}
return(priceDate);
}
