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));
}
