/// <param name="applicationID">application ID number of the apartment application</param>
/// <param name="isAdmin">boolean indicating whether the current user is an admin, permits access to restricted information such as birth date</param>
/// <returns>Object of type ApartmentApplicationViewModel</returns>
public ApartmentApplicationViewModel GetApartmentApplication(int applicationID, bool isAdmin = false)
{
SqlParameter appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
IEnumerable <GET_AA_APPLICATIONS_BY_ID_Result> applicationResult = RawSqlQuery <GET_AA_APPLICATIONS_BY_ID_Result> .query("GET_AA_APPLICATIONS_BY_ID @APPLICATION_ID", appIDParam);
if (applicationResult == null || !applicationResult.Any())
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "The application could not be found."
};
}
else if (applicationResult.Count() > 1)
{
// Somehow there was more than one application for this session code and applcation ID.... THis should not be possible
// We will have to decide what is the best course of action in this case
}
GET_AA_APPLICATIONS_BY_ID_Result applicationDBModel = applicationResult.FirstOrDefault(x => x.AprtAppID == applicationID);
// Assign the values from the database to the custom view model for the frontend
ApartmentApplicationViewModel apartmentApplicationModel = applicationDBModel; //implicit conversion
if (apartmentApplicationModel.EditorProfile == null)
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "The student information about the editor of this application could not be found."
};
}
// Get the applicants that match this application ID
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
IEnumerable <GET_AA_APPLICANTS_BY_APPID_Result> applicantsResult = RawSqlQuery <GET_AA_APPLICANTS_BY_APPID_Result> .query("GET_AA_APPLICANTS_BY_APPID @APPLICATION_ID", appIDParam);
if (applicantsResult != null && applicantsResult.Any())
{
// Only attempt to parse the data if the collection of applicants is not empty
// It is possible for a valid saved application to not contain any applicants yet, so we do not want to throw an error in the case where no applicants were found
List <ApartmentApplicantViewModel> applicantsList = new List <ApartmentApplicantViewModel>();
foreach (GET_AA_APPLICANTS_BY_APPID_Result applicantDBModel in applicantsResult)
{
ApartmentApplicantViewModel applicantModel = applicantDBModel; //implicit conversion
// If the student information is found, create a new ApplicationViewModel and fill in its properties
if (applicantModel.Profile != null && applicantDBModel.AprtAppID == applicationID)
{
if (isAdmin) // if the current user is a housing admin or super admin
{
// Only add the birthdate, probabtion, and points if the user is authorized to view that information
applicantModel.BirthDate = new UnitOfWork().AccountRepository.FirstOrDefault(x => x.AD_Username.ToLower() == applicantDBModel.Username.ToLower()).Birth_Date;
// The probation data is already in the database, we just need to write a stored procedure to get it
// applicantModel.Probation = ... // TBD
// Calculate application points
int points = 0;
if (!string.IsNullOrEmpty(applicantModel.Class))
{
points += int.Parse(applicantModel.Class);
}
if (applicantModel.Age >= 23)
{
points += 1;
}
if (!string.IsNullOrEmpty(applicantModel.OffCampusProgram))
{
points += 1;
}
if (applicantModel.Probation)
{
points -= 3;
}
applicantModel.Points = Math.Max(0, points);; // Set the resulting points to zero if the sum gave a value less than zero
}
// Add this new ApplicantViewModel object to the list of applicants for this application
applicantsList.Add(applicantModel);
}
}
if (applicantsList.Any())
{
// Add this list of applicants to the application model as an array
apartmentApplicationModel.Applicants = applicantsList.OrderBy(x => x.Username).ToArray();
}
}
// Get the apartment choices that match this application ID
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
IEnumerable <GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result> apartmentChoicesResult = RawSqlQuery <GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result> .query("GET_AA_APARTMENT_CHOICES_BY_APP_ID @APPLICATION_ID", appIDParam);
if (apartmentChoicesResult != null && apartmentChoicesResult.Any())
{
// Only attempt to parse the data if the collection of apartment choices is not empty
// It is possible for a valid saved application to not contain any apartment choices yet, so we do not want to throw an error in the case where no apartment choices were found
List <ApartmentChoiceViewModel> apartmentChoicesList = new List <ApartmentChoiceViewModel>();
foreach (GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result apartmentChoiceDBModel in apartmentChoicesResult)
{
ApartmentChoiceViewModel apartmentChoiceModel = apartmentChoiceDBModel; //implicit conversion
// Add this new ApartmentChoiceModel object to the list of apartment choices for this application
apartmentChoicesList.Add(apartmentChoiceModel);
}
if (apartmentChoicesList.Any())
{
// Sort the apartment choices by their ranking number and Add this list of apartment choices to the application model as an array
apartmentApplicationModel.ApartmentChoices = apartmentChoicesList.OrderBy(x => x.HallRank).ThenBy(x => x.HallName).ToArray();
}
}
return(apartmentApplicationModel);
}
/// <summary>
/// Edit an existings apartment application
/// - first, it gets the EditorUsername from the database for the given application ID and makes sure that the student username of the current user matches that stored username
/// - second, it gets an array of the applicants that are already stored in the database for the given application ID
/// - third, it inserts each applicant that is in the 'newApplicantIDs' array but was not yet in the database
/// - fourth, it removes each applicant that was stored in the database but was not in the 'newApplicantIDs' array
///
/// </summary>
/// <param name="username"> The student username of the user who is attempting to save the apartment application (retrieved via authentication token) </param>
/// <param name="sess_cde"> The current session code </param>
/// <param name="applicationID"> The application ID number of the application to be edited </param>
/// <param name="newEditorUsername"> The student username of the student who is declared to be the editor of this application (retrieved from the JSON from the front end) </param>
/// <param name="newApartmentApplicants"> Array of JSON objects providing apartment applicants </param>
/// <param name="newApartmentChoices"> Array of JSON objects providing apartment hall choices </param>
/// <returns>Returns the application ID number if all the queries succeeded</returns>
public int EditApplication(string username, string sess_cde, int applicationID, string newEditorUsername, ApartmentApplicantViewModel[] newApartmentApplicants, ApartmentChoiceViewModel[] newApartmentChoices)
{
IEnumerable <string> editorResult = null;
SqlParameter appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
editorResult = RawSqlQuery <string> .query("GET_AA_EDITOR_BY_APPID @APPLICATION_ID", appIDParam);
if (editorResult == null || !editorResult.Any())
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "The application could not be found."
};
}
string storedEditorUsername = editorResult.FirstOrDefault();
if (username.ToLower() != storedEditorUsername.ToLower())
{
// This should already be caught by the StateYourBusiness, but I will leave this check here just in case
throw new Exceptions.CustomExceptions.UnauthorizedAccessException()
{
ExceptionMessage = "The current user does not match the stored editor of this application"
};
}
// Only perform the update if the username of the current user matched the 'EditorUsername' stored in the database for the requested application
//--------
// Update applicant information
IEnumerable <GET_AA_APPLICANTS_BY_APPID_Result> existingApplicantResult = null;
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
// Get the IDs of the applicants that are already stored in the database for this application
existingApplicantResult = RawSqlQuery <GET_AA_APPLICANTS_BY_APPID_Result> .query("GET_AA_APPLICANTS_BY_APPID @APPLICATION_ID", appIDParam);
if (existingApplicantResult == null)
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "The applicants could not be found."
};
}
// List of applicants IDs that are in the array recieved from the frontend but not yet in the database
List <ApartmentApplicantViewModel> applicantsToAdd = new List <ApartmentApplicantViewModel>(newApartmentApplicants);
// List of applicants IDs that are in both the array recieved from the frontend and the database
List <ApartmentApplicantViewModel> applicantsToUpdate = new List <ApartmentApplicantViewModel>();
// List of applicants IDs that are in the database but not in the array recieved from the frontend
List <ApartmentApplicantViewModel> applicantsToRemove = new List <ApartmentApplicantViewModel>();
// Check whether any applicants were found matching the given application ID number
if (existingApplicantResult.Any())
{
foreach (GET_AA_APPLICANTS_BY_APPID_Result existingApplicant in existingApplicantResult)
{
ApartmentApplicantViewModel newMatchingApplicant = null;
newMatchingApplicant = newApartmentApplicants.FirstOrDefault(x => x.Username.ToLower() == existingApplicant.Username.ToLower());
if (newMatchingApplicant != null)
{
// If the applicant is in both the new applicant list and the existing applicant list, then we do NOT need to add it to the database
applicantsToAdd.Remove(newMatchingApplicant);
if (newMatchingApplicant.OffCampusProgram != existingApplicant.AprtProgram)
{
// If the applicant is in both the new and existing applicant lists but has different OffCampusProgram values, then we need to update that in the database
applicantsToUpdate.Add(newMatchingApplicant);
}
}
else
{
ApartmentApplicantViewModel nonMatchingApplicant = new ApartmentApplicantViewModel
{
ApplicationID = existingApplicant.AprtAppID,
Username = existingApplicant.Username, // Code for after we remade the AA_Applicants table
};
// If the applicant is in the existing list but not in the new list of applicants, then we need to remove it from the database
applicantsToRemove.Add(nonMatchingApplicant);
}
}
}
SqlParameter userParam = null;
SqlParameter programParam = null;
SqlParameter sessionParam = null;
// Insert new applicants that are not yet in the database
foreach (ApartmentApplicantViewModel applicant in applicantsToAdd)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
userParam = new SqlParameter("@USERNAME", applicant.Username);
programParam = new SqlParameter("@APRT_PROGRAM", applicant.OffCampusProgram ?? "");
sessionParam = new SqlParameter("@SESS_CDE", sess_cde);
int?applicantResult = _context.Database.ExecuteSqlCommand("INSERT_AA_APPLICANT @APPLICATION_ID, @USERNAME, @APRT_PROGRAM, @SESS_CDE", appIDParam, userParam, programParam, sessionParam); //run stored procedure
if (applicantResult == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "Applicant " + applicant.Username + " could not be inserted."
};
}
}
// Update the info of applicants from the frontend that are already in the database
foreach (ApartmentApplicantViewModel applicant in applicantsToUpdate)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
userParam = new SqlParameter("@USERNAME", applicant.Username);
programParam = new SqlParameter("@APRT_PROGRAM", applicant.OffCampusProgram ?? "");
sessionParam = new SqlParameter("@SESS_CDE", sess_cde);
int?applicantResult = _context.Database.ExecuteSqlCommand("UPDATE_AA_APPLICANT @APPLICATION_ID, @USERNAME, @APRT_PROGRAM, @SESS_CDE", appIDParam, userParam, programParam, sessionParam); //run stored procedure
if (applicantResult == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "Applicant " + applicant.Username + " could not be updated."
};
}
}
// Remove applicants from the database that were remove from the frontend
foreach (ApartmentApplicantViewModel applicant in applicantsToRemove)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
userParam = new SqlParameter("@USERNAME", applicant.Username);
sessionParam = new SqlParameter("@SESS_CDE", sess_cde);
int?applicantResult = _context.Database.ExecuteSqlCommand("DELETE_AA_APPLICANT @APPLICATION_ID, @USERNAME, @SESS_CDE", appIDParam, userParam, sessionParam); //run stored procedure
if (applicantResult == null)
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "Applicant " + applicant.Username + " could not be removed."
};
}
}
//--------
// Update hall information
IEnumerable <GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result> existingApartmentChoiceResult = null;
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
// Get the apartment preferences that are already stored in the database for this application
existingApartmentChoiceResult = RawSqlQuery <GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result> .query("GET_AA_APARTMENT_CHOICES_BY_APP_ID @APPLICATION_ID", appIDParam);
if (existingApartmentChoiceResult == null)
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "The hall information could not be found."
};
}
// List of apartment choices that are in the array recieved from the frontend but not yet in the database
List <ApartmentChoiceViewModel> apartmentChoicesToAdd = new List <ApartmentChoiceViewModel>(newApartmentChoices);
// List of apartment choices that are in both the array recieved from the frontend and the database
List <ApartmentChoiceViewModel> apartmentChoicesToUpdate = new List <ApartmentChoiceViewModel>();
// List of apartment choices that are in the database but not in the array recieved from the frontend
List <ApartmentChoiceViewModel> apartmentChoicesToRemove = new List <ApartmentChoiceViewModel>();
// Check whether any apartment choices were found matching the given application ID number
if (existingApartmentChoiceResult.Any())
{
foreach (GET_AA_APARTMENT_CHOICES_BY_APP_ID_Result existingApartmentChoice in existingApartmentChoiceResult)
{
ApartmentChoiceViewModel newMatchingApartmentChoice = null;
newMatchingApartmentChoice = newApartmentChoices.FirstOrDefault(x => x.HallName == existingApartmentChoice.HallName);
if (newMatchingApartmentChoice != null)
{
// If the apartment is in both the new apartment list and the existing apartment list, then we do NOT need to add it to the database
apartmentChoicesToAdd.Remove(newMatchingApartmentChoice);
if (newMatchingApartmentChoice.HallRank != existingApartmentChoice.Ranking)
{
// If the apartment is in both the new and existing apartment lists but has different ranking values, then we need to update that in the database
apartmentChoicesToUpdate.Add(newMatchingApartmentChoice);
}
}
else
{
ApartmentChoiceViewModel nonMatchingApartmentChoice = new ApartmentChoiceViewModel
{
ApplicationID = existingApartmentChoice.AprtAppID,
HallRank = existingApartmentChoice.Ranking,
HallName = existingApartmentChoice.HallName,
};
// If the apartment is in the existing list but not in the new list of apartments, then we need to remove it from the database
apartmentChoicesToRemove.Add(nonMatchingApartmentChoice);
}
}
}
SqlParameter rankingParam = null;
SqlParameter buildingCodeParam = null;
// Insert new apartment choices that are not yet in the database
foreach (ApartmentChoiceViewModel apartmentChoice in apartmentChoicesToAdd)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
rankingParam = new SqlParameter("@RANKING", apartmentChoice.HallRank);
buildingCodeParam = new SqlParameter("@HALL_NAME", apartmentChoice.HallName);
int?apartmentChoiceResult = _context.Database.ExecuteSqlCommand("INSERT_AA_APARTMENT_CHOICE @APPLICATION_ID, @RANKING, @HALL_NAME", appIDParam, rankingParam, buildingCodeParam); //run stored procedure
if (apartmentChoiceResult == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "Apartment choice with ID " + applicationID + " and hall name " + apartmentChoice.HallName + " could not be inserted."
};
}
}
// Update the info of apartment choices from the frontend that are already in the database
foreach (ApartmentChoiceViewModel apartmentChoice in apartmentChoicesToUpdate)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
rankingParam = new SqlParameter("@RANKING", apartmentChoice.HallRank);
buildingCodeParam = new SqlParameter("@HALL_NAME", apartmentChoice.HallName);
int?apartmentChoiceResult = _context.Database.ExecuteSqlCommand("UPDATE_AA_APARTMENT_CHOICES @APPLICATION_ID, @RANKING, @HALL_NAME", appIDParam, rankingParam, buildingCodeParam); //run stored procedure
if (apartmentChoiceResult == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "Apartment choice with ID " + applicationID + " and hall name " + apartmentChoice.HallName + " could not be updated."
};
}
}
// Remove apartment choices from the database that were removed from the frontend
foreach (ApartmentChoiceViewModel apartmentChoice in apartmentChoicesToRemove)
{
// All SqlParameters must be remade before being reused in an SQL Query to prevent errors
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
buildingCodeParam = new SqlParameter("@HALL_NAME", apartmentChoice.HallName);
int?apartmentChoiceResult = _context.Database.ExecuteSqlCommand("DELETE_AA_APARTMENT_CHOICE @APPLICATION_ID, @HALL_NAME", appIDParam, buildingCodeParam); //run stored procedure
if (apartmentChoiceResult == null)
{
throw new ResourceNotFoundException()
{
ExceptionMessage = "Apartment choice with ID " + applicationID + " and hall name " + apartmentChoice.HallName + " could not be removed."
};
}
}
//--------
// Update the date modified (and application editor if necessary)
DateTime now = System.DateTime.Now;
appIDParam = new SqlParameter("@APPLICATION_ID", applicationID);
SqlParameter timeParam = new SqlParameter("@NOW", now);
if (newEditorUsername.ToLower() != storedEditorUsername.ToLower())
{
SqlParameter editorParam = new SqlParameter("@EDITOR_USERNAME", username);
SqlParameter newEditorParam = new SqlParameter("@NEW_EDITOR_USERNAME", newEditorUsername);
int? result = _context.Database.ExecuteSqlCommand("UPDATE_AA_APPLICATION_EDITOR @APPLICATION_ID, @EDITOR_USERNAME, @NOW, @NEW_EDITOR_USERNAME", appIDParam, editorParam, timeParam, newEditorParam); //run stored procedure
if (result == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "The application could not be updated."
};
}
}
else
{
int?result = _context.Database.ExecuteSqlCommand("UPDATE_AA_APPLICATION_DATEMODIFIED @APPLICATION_ID, @NOW", appIDParam, timeParam); //run stored procedure
if (result == null)
{
throw new ResourceCreationException()
{
ExceptionMessage = "The application DateModified could not be updated."
};
}
}
return(applicationID);
}