public async Task <IActionResult> ImportStudentAccounts(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(7)); } 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 students' emails in the database IList <string> databaseStudentEmails = _studentService.GetStudentEmailsByRoleName(ProfilerRoles.Student); foreach (JToken row in tableRows) { string trainerEmail = row.GetElementValue(0); string sheetRowEmail = row.GetElementValue(7); //check if mentioned trainer exists ApplicationUser trainer = await _userManager.FindByEmailAsync(trainerEmail); if (trainer != null) { //check if the email is already present in the system if (!databaseStudentEmails.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 Students var currentUser = await _userManager.FindByNameAsync(user.UserName); await _userManager.AddToRoleAsync(currentUser, ProfilerRoles.Student); //add user info //TODO: parse the number UserInfo userInfo = new UserInfo { UserId = currentUser.Id, EnName = row.GetElementValue(1), EnSurname = row.GetElementValue(2), RuSurname = row.GetElementValue(3), RuName = row.GetElementValue(4), DateOfBirth = ParseDateTimeToBLRStandard(row.GetElementValue(6)), Email = row.GetElementValue(7), Phone = row.GetElementValue(8) }; 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 await _db.SaveChangesAsync(); } catch { ModelState.AddModelError("Import failed", $"Server error: can’t create new account for {userInfo.Email}. Please try again later."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } //pick the created userInfo UserInfo userInfoCreated = _userInfoService.GetUserInfo(currentUser.Email); //TODO: verify datetime Student student = new Student { TrainerId = trainer.Id, UserInfoId = userInfoCreated.Id, DateOfGraduation = ParseDateTimeToBLRStandard(row.GetElementValue(9)), GraduationMark = Convert.ToInt32(row.GetElementValue(11)) }; try { //save the changes await _studentService.AddOrUpdateStudentAsync(student); await _db.SaveChangesAsync(); } catch { ModelState.AddModelError("Import failed", $"Server error: can’t create new student for {userInfoCreated.Email}. Please try again later."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } //pick the stream Model.Models.Stream stream = _streamService.GetStreamByShortName(row.Children <JToken>().ElementAt(10).Value <string>()); if (stream != null) { try { //bind stream to the student and save the changes await _studentService.AddStream(student, stream); await _db.SaveChangesAsync(); } catch { ModelState.AddModelError("Import failed", $"Server error: can’t create new student for {userInfoCreated.Email}. Please try again later."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } } //if the spreadsheet has an abbreviation not present in the database else { ModelState.AddModelError("Import failed", $"Server error: can’t create new student for {userInfoCreated.Email}. Please check the field \"Stream\" in the google table."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } } } //if the email already exists else { //check the stream UserInfo userInfo = _db.UserInfo.FirstOrDefault(ui => ui.Email == sheetRowEmail); Student existingStudent = _studentService.GetCurrentStudentByUserInfo(_userInfoService.GetUserInfo(sheetRowEmail).Id); Model.Models.Stream stream = _streamService.GetStreamByStudentId(existingStudent.Id); string spreadsheetRowStream = row.GetElementValue(10); //if the stream is different, create new student if (stream.StreamShortName != spreadsheetRowStream) { //TODO: verify datetime Student student = new Student { TrainerId = trainer.Id, UserInfoId = userInfo.Id, DateOfGraduation = ParseDateTimeToBLRStandard(row.GetElementValue(9)), GraduationMark = Convert.ToInt32(row.GetElementValue(11)) }; try { //save changes await _studentService.AddOrUpdateStudentAsync(student); await _db.SaveChangesAsync(); } catch { ModelState.AddModelError("Import failed", $"Server error: can’t create new student for {userInfo.Email}. Please try again later."); await PopulateViewModel(viewModel); return(View("ImportAccounts", viewModel)); } //pick the stream Model.Models.Stream streamToAdd = _streamService.GetStreamByShortName(spreadsheetRowStream); try { //bind stream to the student await _studentService.AddStream(student, streamToAdd); await _db.SaveChangesAsync(); } catch { ModelState.AddModelError("Import failed", $"Server error: can’t create new student for {userInfo.Email}. 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)); }