public ActionResult CompanyProgramsNProducts()
{
string[] stringSeparators = new string[] { ",", ". ", "\n", ";" };
SourcingGuideDevEntities _db = new SourcingGuideDevEntities();
var companies = _db.Companies.ToList();
foreach (var comp in companies)
{
//Manage Proforma Programs
var proformaProgram = comp.ProformaPrograms;
if (!string.IsNullOrEmpty(proformaProgram))
{
var lstProformaPrograms = proformaProgram.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries); // proformaProgram.Split(new[] { ",", ". ", "\n", ";" }).ToList();
foreach (var pp in lstProformaPrograms)
{
var strProformaProgram = pp.Trim();
if (!string.IsNullOrEmpty(strProformaProgram))
{
var _ProformaProgram = _db.ProformaPrograms.FirstOrDefault(p => p.CompanyId == comp.CompanyId && p.Name == strProformaProgram);
if (null == _ProformaProgram)
{
_ProformaProgram = new ProformaProgram();
_ProformaProgram.CompanyId = comp.CompanyId;
_ProformaProgram.Name = strProformaProgram;
_ProformaProgram.CreatedDate = DateTime.Now;
_db.ProformaPrograms.Add(_ProformaProgram);
}
else
{
_ProformaProgram.Name = strProformaProgram;
_ProformaProgram.ModifiedDate = DateTime.Now;
_db.Entry(_ProformaProgram).State = EntityState.Modified;
}
}
}
}
//Manage ProductsCapabilities
var productsCapability = comp.ProductsNCapabilities;
if (!string.IsNullOrEmpty(productsCapability))
{
var lstProductsCapabilities = productsCapability.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries); //productsCapability.Split(new[] { ',', '. ', '\n', ';' }).ToList();
foreach (var pc in lstProductsCapabilities)
{
var strProductsCapability = pc.Trim();
if (!string.IsNullOrEmpty(strProductsCapability))
{
var _ProductsCapability = _db.ProductsCapabilities.FirstOrDefault(p => p.CompanyId == comp.CompanyId && p.Name == strProductsCapability);
if (null == _ProductsCapability)
{
_ProductsCapability = new ProductsCapability();
_ProductsCapability.CompanyId = comp.CompanyId;
_ProductsCapability.Name = strProductsCapability;
_ProductsCapability.CreatedDate = DateTime.Now;
_db.ProductsCapabilities.Add(_ProductsCapability);
}
else
{
_ProductsCapability.Name = strProductsCapability;
_ProductsCapability.ModifiedDate = DateTime.Now;
_db.Entry(_ProductsCapability).State = EntityState.Modified;
}
}
}
}
_db.SaveChanges();
}
ViewBag.OperationStatus = "ProformaPrograms/ProductsCapabilities updated successfully!";
return(View());
}
public ActionResult ImportExcel()
{
SourcingGuideDevEntities _db = new SourcingGuideDevEntities();
//string filePath = Server.MapPath("/Docs/2016MASTERSourceGuideListings.xlsx");
string filePath = "E:/Gotcha/Docs/2016MASTERSourceGuideListings.xlsx";
var excelFile = new FileInfo(filePath);
List <Company> CompanyList = new List <Company>();
const int startRow = 1;
try
{
using (var package = new ExcelPackage(excelFile))
{
// Get the work book in the file
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
// Get the worksheet
foreach (ExcelWorksheet currentWorksheet in workBook.Worksheets)
{
for (int rowNumber = startRow + 1; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
// read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
{
var categoryText = currentWorksheet.Name;
string category = Convert.ToString(currentWorksheet.Cells[rowNumber, 1].Value).Trim();
if (!string.IsNullOrEmpty(category))
{
int?categoryId = null;
var cat = _db.Categories.FirstOrDefault(c => c.Category1.ToLower() == categoryText.ToLower());
if (null != cat)
{
categoryId = cat.CategoryId;
}
string MVPLP_PLP = Convert.ToString(currentWorksheet.Cells[rowNumber, 2].Value);
string Company_Name = Convert.ToString(currentWorksheet.Cells[rowNumber, 3].Value);
string Street_Address = Convert.ToString(currentWorksheet.Cells[rowNumber, 4].Value);
string City = Convert.ToString(currentWorksheet.Cells[rowNumber, 5].Value);
string State = Convert.ToString(currentWorksheet.Cells[rowNumber, 6].Value);
string ZipCode = Convert.ToString(currentWorksheet.Cells[rowNumber, 7].Value);
string Phone_1 = Convert.ToString(currentWorksheet.Cells[rowNumber, 8].Value);
string Phone_2 = Convert.ToString(currentWorksheet.Cells[rowNumber, 9].Value);
string Fax = Convert.ToString(currentWorksheet.Cells[rowNumber, 10].Value);
string Website = Convert.ToString(currentWorksheet.Cells[rowNumber, 11].Value);
string FTPSite = Convert.ToString(currentWorksheet.Cells[rowNumber, 12].Value);
string ArtMail = Convert.ToString(currentWorksheet.Cells[rowNumber, 13].Value);
string OrderEmail = Convert.ToString(currentWorksheet.Cells[rowNumber, 14].Value);
string FOB_pointinCanada = Convert.ToString(currentWorksheet.Cells[rowNumber, 15].Value);
string CanadianDollers = Convert.ToString(currentWorksheet.Cells[rowNumber, 16].Value);
string PrimaryContact_Name = Convert.ToString(currentWorksheet.Cells[rowNumber, 17].Value);
string PrimaryContact_Email = Convert.ToString(currentWorksheet.Cells[rowNumber, 18].Value);
string PrimaryContact_Extension = Convert.ToString(currentWorksheet.Cells[rowNumber, 19].Value);
string PrimaryContact_DirectLine = Convert.ToString(currentWorksheet.Cells[rowNumber, 20].Value);
string SecondaryContact_Name = Convert.ToString(currentWorksheet.Cells[rowNumber, 21].Value);
string SecondaryContact_Email = Convert.ToString(currentWorksheet.Cells[rowNumber, 22].Value);
string SecondaryContact_Extension = Convert.ToString(currentWorksheet.Cells[rowNumber, 23].Value);
string SecondaryContact_DirectLine = Convert.ToString(currentWorksheet.Cells[rowNumber, 24].Value);
string TradeOnly = Convert.ToString(currentWorksheet.Cells[rowNumber, 25].Value);
string Union = Convert.ToString(currentWorksheet.Cells[rowNumber, 26].Value);
string ASI = Convert.ToString(currentWorksheet.Cells[rowNumber, 27].Value);
string PPAI = Convert.ToString(currentWorksheet.Cells[rowNumber, 28].Value);
string PPPC = Convert.ToString(currentWorksheet.Cells[rowNumber, 29].Value);
string SAGE = Convert.ToString(currentWorksheet.Cells[rowNumber, 30].Value);
string UPIC = Convert.ToString(currentWorksheet.Cells[rowNumber, 31].Value);
string Certification = Convert.ToString(currentWorksheet.Cells[rowNumber, 32].Value);
string Minority_Owned = Convert.ToString(currentWorksheet.Cells[rowNumber, 33].Value);
string Proforma_Pricing = Convert.ToString(currentWorksheet.Cells[rowNumber, 34].Value);
string Proforma_Programs = Convert.ToString(currentWorksheet.Cells[rowNumber, 35].Value);
string Product_capability = Convert.ToString(currentWorksheet.Cells[rowNumber, 36].Value);
string Rushor24hours = Convert.ToString(currentWorksheet.Cells[rowNumber, 37].Value);
Company _objCompany = new Company {
CategoryId1 = categoryId, PartnerType = MVPLP_PLP, CompanyName = Company_Name, StreetAddress = Street_Address, City = City, State = State, ZipCode = ZipCode, Phone1 = Phone_1, Phone2 = Phone_2, PrimaryContactFax = Fax, Website = Website, FTPSite = FTPSite, ArtEmail = ArtMail, OrderEmail = OrderEmail, FOBPointInCanada = FOB_pointinCanada, QuoteinCanadianDollars = CanadianDollers, PrimaryContactFirstName = PrimaryContact_Name, PrimaryContactEmail = PrimaryContact_Email, PrimaryContactExtension = PrimaryContact_Extension, PrimaryContactDirectLine = PrimaryContact_DirectLine, SecondaryContactFirstName = SecondaryContact_Name, SecondaryContactEmail = SecondaryContact_Email, SecondaryContactExtension = SecondaryContact_Extension, SecondaryContactDirectLine = SecondaryContact_DirectLine, PrimaryContactTradeOnly = TradeOnly, Union = Union, ASI = ASI, PPAI = PPAI, PPPC = PPPC, SAGE = SAGE, UPIC = UPIC, Certifications = Certification, MinorityOwned = Minority_Owned, ProformaPricing = Proforma_Pricing, ProformaPrograms = Proforma_Programs, ProductsNCapabilities = Product_capability, Rushor24hour = Rushor24hours
};
_db.Companies.Add(_objCompany);
}
}
}
}
}
}
_db.SaveChanges();
}
catch (DbEntityValidationException ex)
{
var errorMessages = ex.EntityValidationErrors
.SelectMany(x => x.ValidationErrors)
.Select(x => x.ErrorMessage);
// Join the list to a single string.
var fullErrorMessage = string.Join("; ", errorMessages);
// Combine the original exception message with the new one.
var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
// Throw a new DbEntityValidationException with the improved exception message.
throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
}
return(View());
}
