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