Exemplo n.º 1
0
        /// <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);
        }
Exemplo n.º 2
0
        /// <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);
        }