internal void LoadMachineSection() { try { var contentsMachine = File.ReadAllText(MachineStoragePath); Machine = JsonConvert.DeserializeObject <MachineSection>(contentsMachine); } catch (FileNotFoundException) { Machine = MachineSection.GetDefault(); SaveMachineSection(); } }
internal void LoadMachineSection(string path = null) { if (Machine != null) { SaveMachineSection(); } var configPath = File.Exists(path) ? path : MachineStoragePath; try { var contentsMachine = File.ReadAllText(configPath); Machine = JsonConvert.DeserializeObject <MachineSection>(contentsMachine); Machine.ConfigPath = configPath; } catch (FileNotFoundException) { Machine = MachineSection.GetDefault(); Machine.ConfigPath = configPath; SaveMachineSection(); } }
public async Task <IActionResult> UploadExcel(IFormFile masterFile) { if (masterFile == null || masterFile.Length == 0) { return(Content("File not selected")); } //check if file extension is excel string fileExtension = Path.GetExtension(masterFile.FileName); //Validate uploaded file and return error if (fileExtension != ".xls" && fileExtension != ".xlsx") { //ViewBag.Message = "Please select the excel file with .xls or .xlsx extension"; //return View(); } //Generate full filepath and save file var filePath = Path.Combine(Directory.GetCurrentDirectory(), _environment.WebRootPath, "uploads/" + masterFile.FileName); using (var bits = new FileStream(filePath, FileMode.Create)) { await masterFile.CopyToAsync(bits); } FileInfo file = new FileInfo(filePath); //Install-Package EPPlus.Core -Version 1.5.4 using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet workSheet = package.Workbook.Worksheets["Sheet1"]; int totalRows = workSheet.Dimension.Rows; HashSet <MasterTableLine> masterLines = new HashSet <MasterTableLine>(); string lineSection = null; string lineSectionGroup = null; string modelName = null; for (int i = 1; i <= totalRows; i++) { Console.WriteLine("Countering" + i); int next = i + 1; bool sectionBool = workSheet.Cells[i, 3].Style.Font.Bold; bool sectionGroupBool = workSheet.Cells[next, 3].Style.Font.Bold; if (sectionBool && sectionGroupBool) { modelName = workSheet.Cells[i - 1, 1].Value != null ? workSheet.Cells[i - 1, 1].Value.ToString() : modelName; lineSection = workSheet.Cells[i, 3].Value != null ? workSheet.Cells[i, 3].Value.ToString() : ""; lineSectionGroup = workSheet.Cells[next, 3].Value != null ? workSheet.Cells[next, 3].Value.ToString() : ""; } if (sectionBool && !sectionGroupBool) { lineSectionGroup = workSheet.Cells[i, 3].Value != null ? workSheet.Cells[i, 3].Value.ToString() : ""; } bool containsPart = workSheet.Cells[i, 4].Value != null; if (!sectionBool && containsPart) { masterLines.Add(new MasterTableLine { ModelName = modelName, SerialNumber = workSheet.Cells[i, 6].Value != null ? workSheet.Cells[i, 6].Value.ToString() : "", Section = lineSection, SectionGroup = lineSectionGroup, PartName = workSheet.Cells[i, 3].Value != null ? workSheet.Cells[i, 3].Value.ToString() : "", PartNumber = workSheet.Cells[i, 4].Value != null ? workSheet.Cells[i, 4].Value.ToString() : "", Quantity = workSheet.Cells[i, 5].Value != null ? workSheet.Cells[i, 5].Value.ToString() : "", Remarks = workSheet.Cells[i, 7].Value != null ? workSheet.Cells[i, 7].Value.ToString() : "", Brand = "Komatsu" }); } } file.Delete(); int count = 0; //get data to db foreach (var line in masterLines) { Console.WriteLine("Cycle" + count++); Console.WriteLine("Started db operation"); if (line.Brand != "") { Brand brand = new Brand { Name = line.Brand }; Brand dbBrand = _dbContext.Brands.Where(b => b.Name == brand.Name).FirstOrDefault(); if (dbBrand == null) { _dbContext.Brands.Add(brand); _dbContext.SaveChanges(); } //Install - Package FlexLabs.EntityFrameworkCore.Upsert - Version 2.1.2 //_dbContext.Upsert(brand).On(b => new { b.Name }).Run(); Console.WriteLine("Added brand"); brand = _dbContext.Brands.Where(b => b.Name == line.Brand).FirstOrDefault(); if (line.ModelName != "") { Machine machine = new Machine { ModelName = line.ModelName, SerialNumber = line.SerialNumber, Brand = brand }; Machine dbMachine = _dbContext.Machines.Where(m => m.ModelName == machine.ModelName).FirstOrDefault(); if (dbMachine == null) { _dbContext.Machines.Add(machine); _dbContext.SaveChanges(); } // _dbContext.Upsert(machine).On(m => new { m.ModelName }).Run(); Console.WriteLine("Added machine"); machine = _dbContext.Machines.Where(m => m.ModelName == line.ModelName).FirstOrDefault(); if (line.Section != "") { Section section = new Section { SectionName = line.Section }; Section dbSection = _dbContext.Sections.Where(s => s.SectionName == section.SectionName).FirstOrDefault(); if (dbSection == null) { _dbContext.Sections.Add(section); _dbContext.SaveChanges(); } //_dbContext.Sections.Upsert(section).On(s => new { s.SectionName }).Run(); Console.WriteLine("Added section"); section = _dbContext.Sections.Where(s => s.SectionName == line.Section).FirstOrDefault(); if (line.SectionGroup != "") { SectionGroup sectionGroup = new SectionGroup { SectionGroupName = line.SectionGroup, Section = section }; SectionGroup dbSectionGroup = _dbContext.SectionGroups .Where(s => s.SectionGroupName == sectionGroup.SectionGroupName) .FirstOrDefault(); if (dbSectionGroup == null) { _dbContext.SectionGroups.Add(sectionGroup); _dbContext.SaveChanges(); } //_dbContext.SectionGroups.Upsert(sectionGroup).On(s => new { s.SectionGroupName }).Run(); Console.WriteLine("Added sectiongroup"); sectionGroup = _dbContext.SectionGroups.Where(sg => sg.SectionGroupName == sectionGroup.SectionGroupName).FirstOrDefault(); if (line.PartName != "") { Category category = new Category { CategoryName = line.PartName, }; Category dbCategory = _dbContext.Categories.Where(c => c.CategoryName == category.CategoryName).FirstOrDefault(); if (dbCategory == null) { _dbContext.Categories.Add(category); _dbContext.SaveChanges(); } //_dbContext.Categories.Upsert(category).On(s => new { s.CategoryName }).Run(); Console.WriteLine("Added category"); category = _dbContext.Categories.Where(s => s.CategoryName == category.CategoryName).FirstOrDefault(); //brand.Categories.Add(category); if (line.PartNumber != "") { Product product = new Product { PartNumber = line.PartNumber, Brand = brand, Category = category, Section = section, Quantity = line.Quantity, Remarks = line.Remarks }; Product dbProduct = _dbContext.Products.Where(p => p.PartNumber == product.PartNumber).FirstOrDefault(); if (dbProduct == null) { _dbContext.Products.Add(product); _dbContext.SaveChanges(); } //_dbContext.Products.Upsert(product).On(s => new { s.PartNumber }).Run(); Console.WriteLine("Added product"); product = _dbContext.Products.Where(p => p.PartNumber == product.PartNumber).FirstOrDefault(); //Brand One to many brand.Categories.Add(category); brand.Machines.Add(machine); brand.Products.Add(product); brand.SectionGroups.Add(sectionGroup); brand.Sections.Add(section); section.SectionGroups.Add(sectionGroup); category.Products.Add(product); _dbContext.SaveChanges(); //ProductSectionGroup relationship ProductSectionGroup productSectionGroup = new ProductSectionGroup { Product = product, SectionGroup = sectionGroup }; ProductSectionGroup dbProductSectionGroup = _dbContext .Set <ProductSectionGroup>() .Where(psg => psg.SectionGroupId == sectionGroup.SectionGroupId && psg.ProductId == product.ProductId) .FirstOrDefault(); if (dbProductSectionGroup == null) { product.ProductSectionGroups.Add(productSectionGroup); _dbContext.SaveChanges(); } //ProductMachine relationship ProductMachine productMachine = new ProductMachine { Product = product, Machine = machine }; ProductMachine dbProductMachine = _dbContext .Set <ProductMachine>() .Where(pm => pm.ProductId == product.ProductId && pm.MachineId == machine.MachineId) .FirstOrDefault(); if (dbProductMachine == null) { product.ProductMachines.Add(productMachine); _dbContext.SaveChanges(); } //MachineSection relationship MachineSection machineSection = new MachineSection { Section = section, Machine = machine }; MachineSection dbMachineSection = _dbContext .Set <MachineSection>() .Where(m => m.MachineId == machine.MachineId && m.SectionId == section.SectionId) .FirstOrDefault(); if (dbMachineSection == null) { machine.MachineSections.Add(machineSection); _dbContext.SaveChanges(); } //MachineSectionGroup relationship MachineSectionGroup machineSectionGroup = new MachineSectionGroup { SectionGroup = sectionGroup, Machine = machine }; MachineSectionGroup dbMachineSectionGroup = _dbContext .Set <MachineSectionGroup>() .Where(m => m.MachineId == machine.MachineId && m.SectionGroupId == sectionGroup.SectionGroupId) .FirstOrDefault(); if (dbMachineSectionGroup == null) { machine.MachineSectionGroups.Add(machineSectionGroup); _dbContext.SaveChanges(); } } } } } } } Console.WriteLine("Ended dbOpration"); } _dbContext.SaveChanges(); Console.WriteLine("End of request"); return(Ok()); } }