private void UpdateCompanyTypes(string companyTypes, Company companyToUpdate)
{
if (String.IsNullOrEmpty(companyTypes))
{
return;
}
if (companyTypes == ";;")
{
companyToUpdate.CompanyCustomers.Clear();
companyToUpdate.CompanyContractors.Clear();
companyToUpdate.CompanyVendors.Clear();
return;
}
var selectedOptions = companyTypes.Split(';');
// Customer Types
var selectedCustomerHS = new HashSet <string>(selectedOptions[0].Split(','));
var currentCustomerHS = new HashSet <int>(companyToUpdate.CompanyCustomers.Select(s => s.CustomerTypeID));
foreach (var s in _context.CustomerTypes)
{
if (selectedCustomerHS.Contains(s.CustomerTypeID.ToString()))
{
if (!currentCustomerHS.Contains(s.CustomerTypeID))
{
companyToUpdate.CompanyCustomers.Add(new CompanyCustomer
{
CustomerTypeID = s.CustomerTypeID,
CompanyID = companyToUpdate.CompanyID
});
}
}
else
{
if (currentCustomerHS.Contains(s.CustomerTypeID))
{
CompanyCustomer specToRemove = companyToUpdate.CompanyCustomers.SingleOrDefault(c => c.CustomerTypeID == s.CustomerTypeID);
_context.Remove(specToRemove);
}
}
}
//Contractor Types
var selectedContractorHS = selectedOptions[1].Split(',')
.ToDictionary(k => k.Split('|').First(), v => v.Split('|').Last());
var currentContractors = companyToUpdate.CompanyContractors;
foreach (var s in _context.ContractorTypes)
{
if (selectedContractorHS.TryGetValue(s.ContractorTypeID.ToString(), out var expiryDate))
{
var companyContractor = currentContractors.FirstOrDefault(c => c.ContractorTypeID == s.ContractorTypeID);
if (companyContractor == null)
{
companyContractor = new CompanyContractor
{
ContractorTypeID = s.ContractorTypeID,
CompanyID = companyToUpdate.CompanyID
};
companyToUpdate.CompanyContractors.Add(companyContractor);
}
if (DateTime.TryParse(expiryDate, out var expDate))
{
companyContractor.ExpiryDate = expDate;
}
}
else
{
if (currentContractors.Any(c => c.ContractorTypeID == s.ContractorTypeID))
{
var specToRemove = companyToUpdate.CompanyContractors.SingleOrDefault(c => c.ContractorTypeID == s.ContractorTypeID);
_context.Remove(specToRemove);
}
}
}
//Vendor Types
var selectedVendorHS = new HashSet <string>(selectedOptions[2].Split(','));
var currentVendorHS = new HashSet <int>(companyToUpdate.CompanyVendors.Select(s => s.VendorTypeID));
foreach (var s in _context.VendorTypes)
{
if (selectedVendorHS.Contains(s.VendorTypeID.ToString()))
{
if (!currentVendorHS.Contains(s.VendorTypeID))
{
companyToUpdate.CompanyVendors.Add(new CompanyVendor
{
VendorTypeID = s.VendorTypeID,
CompanyID = companyToUpdate.CompanyID
});
}
}
else
{
if (currentVendorHS.Contains(s.VendorTypeID))
{
CompanyVendor specToRemove = companyToUpdate.CompanyVendors.SingleOrDefault(v => v.VendorTypeID == s.VendorTypeID);
_context.Remove(specToRemove);
}
}
}
}
public async Task <IActionResult> InsertFromExcelCompany(IFormFile theExcel)
{
if (theExcel == null)
{
ModelState.AddModelError("No files selected", "Please select a file");
return(RedirectToAction(nameof(Index)));
}
ExcelPackage excel;
using (var memoryStream = new MemoryStream())
{
await theExcel.CopyToAsync(memoryStream);
excel = new ExcelPackage(memoryStream);
}
var workSheet = excel.Workbook.Worksheets[0];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
//Start a new list to hold imported objects
List <Company> companies = new List <Company>();
List <CompanyVendor> companyVendors = new List <CompanyVendor>();
List <CompanyContractor> companyContractors = new List <CompanyContractor>();
List <CompanyCustomer> companyCustomers = new List <CompanyCustomer>();
for (int row = start.Row + 1; row <= end.Row; row++)
{
//Check if employee exist in database already
string nameCompare = workSheet.Cells[row, 1].Text;
//string tempName = _context.Employees.FirstOrDefault(p => p.Email == emailCompare).Email;
if (_context.Companies.FirstOrDefault(p => p.Name == nameCompare) == null)
{
try
{
Company a = new Company
{
Name = workSheet.Cells[row, 1].Text,
Location = workSheet.Cells[row, 2].Text,
CreditCheck = workSheet.Cells[row, 3].Text == "1",
DateChecked = DateTime.Parse((workSheet.Cells[row, 4].Text == "") ? "1900-01-01" : workSheet.Cells[row, 4].Text),
BillingTermID = _context.BillingTerms.FirstOrDefault(b => b.Terms == workSheet.Cells[row, 5].Text).BillingTermID,
BillingTerm = _context.BillingTerms.FirstOrDefault(b => b.Terms == workSheet.Cells[row, 5].Text),
CurrencyID = _context.Currencies.FirstOrDefault(b => b.CurrencyName == workSheet.Cells[row, 6].Text).CurrencyID,
Currency = _context.Currencies.FirstOrDefault(b => b.CurrencyName == workSheet.Cells[row, 6].Text),
Phone = (workSheet.Cells[row, 7].Text != "") ? Convert.ToInt64(workSheet.Cells[row, 7].Text.Replace(")", "").Replace("(", "").Replace("-", "").Replace(" ", "")) : Convert.ToInt64("0"),
Website = workSheet.Cells[row, 8].Text,
BillingAddress1 = workSheet.Cells[row, 9].Text,
BillingAddress2 = workSheet.Cells[row, 10].Text,
BillingCity = workSheet.Cells[row, 11].Text,
BillingProvinceID = _context.Provinces.FirstOrDefault(b => b.ProvinceName == workSheet.Cells[row, 12].Text).ProvinceID,
BillingPostalCode = workSheet.Cells[row, 13].Text,
BillingCountryID = _context.Countries.FirstOrDefault(b => b.CountryName == workSheet.Cells[row, 14].Text).CountryID,
ShippingAddress1 = workSheet.Cells[row, 15].Text,
ShippingAddress2 = workSheet.Cells[row, 16].Text,
ShippingCity = workSheet.Cells[row, 17].Text,
ShippingProvinceID = _context.Provinces.FirstOrDefault(b => b.ProvinceName == workSheet.Cells[row, 18].Text).ProvinceID,
ShippingPostalCode = workSheet.Cells[row, 19].Text,
ShippingCountryID = _context.Countries.FirstOrDefault(b => b.CountryName == workSheet.Cells[row, 20].Text).CountryID,
Active = workSheet.Cells[row, 27].Text == "1",
Notes = workSheet.Cells[row, 28].Text
};
companies.Add(a);
}
catch (Exception)
{
ModelState.AddModelError("Error", "Error while parsing the file");
return(RedirectToAction(nameof(Index)));
}
}
}
_context.Companies.AddRange(companies);
_context.SaveChanges();
for (int row = start.Row + 1; row <= end.Row; row++)
{
try
{
CompanyCustomer e = new CompanyCustomer
{
CompanyID = _context.Companies.FirstOrDefault(e => e.Name == workSheet.Cells[row, 1].Text).CompanyID,
CustomerTypeID = _context.CustomerTypes.FirstOrDefault(e => e.Type == workSheet.Cells[row, 22].Text).CustomerTypeID
};
companyCustomers.Add(e);
CompanyVendor f = new CompanyVendor
{
CompanyID = _context.Companies.FirstOrDefault(f => f.Name == workSheet.Cells[row, 1].Text).CompanyID,
VendorTypeID = _context.VendorTypes.FirstOrDefault(f => f.Type == workSheet.Cells[row, 24].Text).VendorTypeID
};
companyVendors.Add(f);
CompanyContractor g = new CompanyContractor
{
CompanyID = _context.Companies.FirstOrDefault(g => g.Name == workSheet.Cells[row, 1].Text).CompanyID,
ContractorTypeID = _context.ContractorTypes.FirstOrDefault(g => g.Type == workSheet.Cells[row, 26].Text).ContractorTypeID
};
companyContractors.Add(g);
}
catch (Exception)
{
ModelState.AddModelError("Error", "Error while parsing the file");
return(RedirectToAction(nameof(Index)));
}
}
_context.CompanyCustomers.AddRange(companyCustomers);
_context.CompanyVendors.AddRange(companyVendors);
_context.CompanyContractors.AddRange(companyContractors);
_context.SaveChanges();
return(RedirectToAction(nameof(Index)));
}