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); }