public ActionResult GSheet(string gSheetUrl) { //gSheetUrl = // "https://docs.google.com/spreadsheets/d/1MXehuMdxTRZusvJdQhoz4PBlIQY5-V43K96SaKHNTlE/edit#gid=0"; var leads = Helpers.GSheetHelper.GetContactInfoFromGSheet(gSheetUrl); if (leads == null) { return(RedirectToAction("Index")); } var jobLevels = dbNew.JobLevels.ToList(); foreach (var lead in leads) { var contact = dbNew.Contacts.FirstOrDefault(c => c.email == lead.Email); if (lead.Email != "") { if (contact == null) { contact = new Contact(); contact.email = lead.Email; dbNew.Contacts.Add(contact); dbNew.Entry(contact).State = EntityState.Added; } else { dbNew.Entry(contact).State = EntityState.Modified; } contact.first_name = lead.FirstName; contact.last_name = lead.LastName; contact.prooflink = lead.Prooflink; if (lead.Country != "") { var country = dbNew.Countries.FirstOrDefault(countr => countr.Name == lead.Country); if (country != null) { contact.Country = country; } } if (lead.Title != "") { var title = dbNew.Titles.FirstOrDefault(t => t.Name == lead.Title); if (title == null) { title = new Title { Name = lead.Title }; var jobLevel = dbNew.JobLevels.FirstOrDefault(jl => lead.Title.Contains(jl.Name)); if (jobLevel == null) { jobLevel = jobLevels.FirstOrDefault(jl => PlatformHelper.FindByParts(lead.Title, jl.Name)); } if (jobLevel != null) { title.JobLevel = jobLevel; } dbNew.Titles.Add(title); dbNew.Entry(title).State = EntityState.Added; } contact.Title = title; } } if (lead.Company != "") { var company = dbNew.Companies.FirstOrDefault(comp => comp.name == lead.Company); if (company == null) { company = new Company(); company.name = lead.Company; dbNew.Companies.Add(company); dbNew.Entry(company).State = EntityState.Added; } else { dbNew.Entry(company).State = EntityState.Modified; } if (!String.IsNullOrEmpty(lead.Industry)) { var industry = dbNew.Industries.FirstOrDefault(ind => ind.Name == lead.Industry); if (industry == null) { var aliasIndustry = dbNew.IndustryAliases.Include(indAlias => indAlias.Industry).FirstOrDefault(indAlias => indAlias.Name == lead.Industry); if (aliasIndustry == null) { dbNew.IndustryAliases.Add(new IndustryAlias { Name = lead.Industry }); } else if (aliasIndustry.Industry != null) { company.Industries.Add(aliasIndustry.Industry); company.PrimaryIndustry = aliasIndustry.Industry; } } else { company.Industries.Add(industry); company.PrimaryIndustry = industry; } } company.SetDomain(lead.Email); company.employees_prooflink = lead.EmpployeesProoflink; company.revenue_prooflink = lead.RevenueProoflink; company.SetMinMaxEmployees(lead.Employees); company.SetMinMaxRevenue(lead.Revenue); if (contact != null) { contact.Company = company; } } /*if (lead.Title != "") * { * var title = dbNew.Titles.FirstOrDefault(t => t.Name == lead.Title); * if (title == null) * { * title = new Title(); * title.Name = lead.Title; * dbNew.Titles.Add(title); * dbNew.Entry(title).State = EntityState.Added; * } * * if (contact != null) * { * contact.Title = title; * } * }*/ dbNew.SaveChanges(); } return(RedirectToAction("Index")); }