public async Task <IActionResult> ImportTrainerAccounts(ImportAccountsViewModel viewModel) { //Keep track of added users //Use this vars to minimize database quering Dictionary <String, String> generatedPasswords = new Dictionary <string, string>(); List <ApplicationUser> usersToNotify = new List <ApplicationUser>(); List <UserInfo> userInfosToNotify = new List <UserInfo>(); string spreadsheetId = ""; try { //break the link and extrach the id spreadsheetId = viewModel.ApiKey.Split(new[] { '/' })[5]; } catch { ModelState.AddModelError("WrongLink", "There was an issue processing your request. Please verify the link you are pasting are from a google spreadsheet."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } //get the api key ApplicationUser loggedUser = await GetUserAsync(); //Define the web request params string sheetRange = "A2:Z"; string apiKey = _userInfoService.GetUserInfo(loggedUser?.Email).GoogleApiKey; string requestUrl = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + sheetRange + "?key=" + apiKey; //create request HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(requestUrl); using (HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse()) { //read the response stream StreamReader reader = new StreamReader(webResponse.GetResponseStream()); var jsonString = reader.ReadToEnd(); //parse the response stream JObject deserealizedJson = JsonConvert.DeserializeObject(jsonString) as JObject; IEnumerable <JToken> tableRows = deserealizedJson.GetSheetRow(2); //check if the spreadsheet contains duplicate emails List <string> ssEmails = new List <string>(); foreach (JToken row in tableRows) { ssEmails.Add(row.GetElementValue(2)); } if (ssEmails.Count != ssEmails.Distinct().Count()) { ModelState.AddModelError("DuplicateEmails", "It seems that the Google table contains duplicate emails. Please check the table and try again."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } //get teh list of current trainers' emails in the database IList <string> databaseTrainerEmails = _trainerService.GetTrainerEmailsByRoleName(ProfilerRoles.Trainer); //check if a email is already present in the system foreach (JToken row in tableRows) { string sheetRowEmail = row.GetElementValue(2); if (!databaseTrainerEmails.Contains(sheetRowEmail)) { //pick values from the spreadsheet for the new user ApplicationUser user = new ApplicationUser() { UserName = sheetRowEmail, Email = sheetRowEmail }; //generate a password string password = PasswordGenerator.Generate(6, 0); var result = await _userManager.CreateAsync(user, password); if (result.Succeeded) { //add to the dictionary (for testing only) generatedPasswords.Add(user.UserName, password); //add to the role Trainers var currentUser = await _userManager.FindByNameAsync(user.UserName); await _userManager.AddToRoleAsync(currentUser, ProfilerRoles.Trainer); //add user info //TODO: parse the number UserInfo userInfo = new UserInfo { UserId = currentUser.Id, EnName = row.GetElementValue(0), EnSurname = row.GetElementValue(1), Email = row.GetElementValue(2), Phone = row.GetElementValue(3) }; await _userInfoService.AddUserInfoAsync(userInfo); //bind userInfo to applicationUser currentUser.UserInfo = userInfo; //keep track of the new email usersToNotify.Add(currentUser); userInfosToNotify.Add(userInfo); } } } try { //save changes to the database _db.SaveChanges(); } catch { ModelState.AddModelError("Import failed", "Server error: can’t create new accounts. Please try again later."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } } ////send emails //foreach (ApplicationUser userToNotify in usersToNotify) //{ // UserInfo thisUserInfo = userInfosToNotify.FirstOrDefault(x => x.UserId == userToNotify.Id); // string userPassword = generatedPasswords.FirstOrDefault(x => x.Key == userToNotify.Email).Value; // string appLink = _configuration.GetSection("ProfileAppLink").Value; // EmailSender emailSender = new EmailSender(); // StringBuilder sb = new StringBuilder(); // sb.AppendLine("Здравствуйте, " + thisUserInfo.RuName + " " + thisUserInfo.RuSurname + "."); // sb.AppendLine(""); // sb.AppendLine("Для вас была создана учетная запись в системе PROFILE."); // //sb.AppendLine("Please use this link: " + appLink + " and this password: "******" to log in to your account."); // sb.AppendLine("Для входа в систему используйте ваш пароль - " + userPassword); // await emailSender.SendEmailAsync(userToNotify.Email, "Данные вашей учетной записи в Системе PROFILE Образовательного центра ПВТ", sb.ToString()); //} return(View("ImportAccountsResult", generatedPasswords)); }