Ejemplo n.º 1
0
        /// <summary>
        /// Method that indicates if the visit has been finalized.
        /// Visits are finalized when a final diagnosis has been recorded.
        /// </summary>
        /// <param name="appointmentId">The ID of the appointment.</param>
        /// <returns>True if there is a final diagnosis for the specified appointment, false otherwise.</returns>
        public bool VisitIsFinal(int appointmentId)
        {
            if (appointmentId < 0)
            {
                throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId");
            }

            string selectStatement =
                "SELECT @NumberOfFinalizedDiagnoses = COUNT(diagnosisName) " +
                "FROM Diagnosis " +
                "WHERE appointmentId = @AppointmentId " +
                "AND isFinal = 1";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    SqlParameter countParameter = new SqlParameter("@NumberOfFinalizedDiagnoses", SqlDbType.Bit)
                    {
                        Direction = ParameterDirection.Output
                    };
                    selectCommand.Parameters.Add(countParameter);
                    selectCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);
                    selectCommand.ExecuteNonQuery();
                    return(Convert.ToInt32(countParameter.Value) > 0);
                }
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Method that orders lab tests.
        /// </summary>
        /// <param name="appointmentId">The ID of the appointment.</param>
        /// <param name="labTest">An object representing the type of test that is being ordered.</param>
        public void OrderLabTest(int appointmentId, LabTest labTest)
        {
            if (appointmentId < 0)
            {
                throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId");
            }

            if (labTest == null)
            {
                throw new ArgumentNullException("labTest", "The lab test cannot be null.");
            }

            string insertStatement =
                "INSERT ConductedLabTest (appointmentId, testCode) " +
                "VALUES (@AppointmentId, @TestCode)";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
                {
                    insertCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);
                    insertCommand.Parameters.AddWithValue("@TestCode", labTest.TestCode);
                    insertCommand.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 3
0
        /// <summary>
        /// This returns a list of all statuses
        /// </summary>
        /// <returns>List of all status types</returns>
        public List <Status> GetAllStatusTypes()
        {
            List <Status> statuses = new List <Status>();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                string selectStatement = "SELECT id, status FROM status_code";
                using (SqlCommand select = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = select.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Status status = new Status();
                            status.StatusDescription = reader["status"].ToString();
                            status.StatusID          = (int)reader["id"];
                            statuses.Add(status);
                        }
                    }
                }
                connection.Close();
            }
            return(statuses);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Returns the employeeID equal to the accepted personID.
        /// Note that the password returned is the hashed password in the database, not plaintext.
        /// </summary>
        /// <param name="personID">ID of the person</param>
        /// <returns>Employee object</returns>
        public Employee GetEmployeeBy_PersonID(int personID)
        {
            Employee employee        = new Employee();
            string   selectStatement = "SELECT id, person_id, password, username " +
                                       "FROM users " +
                                       "WHERE person_id = @personID ";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("@personID", personID);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            {
                                employee.EmployeeID     = (int)reader["id"];
                                employee.PersonId       = (int)reader["person_id"];
                                employee.UserName       = reader["username"].ToString();
                                employee.HashedPassword = (byte[])reader["password"];
                            }
                        }
                    }
                }
                connection.Close();
            }
            return(employee);
        }
Ejemplo n.º 5
0
        /// <summary>
        /// Returns a list of all Doctors
        /// </summary>
        /// <returns>list of all Doctors</returns>
        public static List <Doctor> GetAllDoctors()
        {
            List <Doctor> doctors         = new List <Doctor>();
            string        selectStatement = "SELECT * FROM doctor;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Doctor doctor = new Doctor
                            {
                                DoctorId = (int)reader["id"],
                                PersonId = (int)reader["person_id"]
                            };
                            PopulatePersonalInformation(doctor);
                            doctors.Add(doctor);
                        }
                    }
                }
                connection.Close();
            }
            return(doctors);
        }
Ejemplo n.º 6
0
        /// <summary>
        /// This method allows users to add employee values to the database
        /// </summary>
        /// <param name="addedEmployee">The employee object to add to the db.</param>
        public void AddEmployee(Employee addedEmployee)
        {
            int    employeeID  = -1;
            string addEmployee = "INSERT users ( username, password, person_id) " +
                                 "VALUES (@username, HASHBYTES('SHA2_256', @password), @person_id) ";

            string employeeIDStatement = "SELECT IDENT_CURRENT('users') FROM users";

            try
            {
                SqlConnection connection = ClinicDBConnection.GetConnection();
                connection.Open();
                using (SqlCommand insertCommand = new SqlCommand(addEmployee, connection))
                {
                    insertCommand.Parameters.AddWithValue("username", addedEmployee.UserName);
                    insertCommand.Parameters.AddWithValue("password", addedEmployee.Password);
                    insertCommand.Parameters.AddWithValue("person_id", addedEmployee.PersonId);

                    insertCommand.ExecuteNonQuery();

                    using (SqlCommand selectCommand = new SqlCommand(employeeIDStatement, connection))
                    {
                        employeeID = Convert.ToInt32(selectCommand.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error " + addedEmployee.PersonId.ToString(), MessageBoxButtons.OK);
            }
        }
Ejemplo n.º 7
0
        /// <summary>
        /// Gets all the test codes in the database.
        /// </summary>
        /// <returns>List of all Tests</returns>
        public static List <Test> GetAllTestCodes()
        {
            List <Test> tests = new List <Test>();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                string selectStatement = "SELECT id, code FROM test_code";
                using (SqlCommand select = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = select.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Test test = new Test
                            {
                                Code       = reader["code"].ToString(),
                                TestCodeID = (int)reader["id"]
                            };
                            tests.Add(test);
                        }
                    }
                }
                connection.Close();
            }
            return(tests);
        }
Ejemplo n.º 8
0
        /// <summary>
        /// Method that determines if a visit has been entered in to the DB.
        /// </summary>
        /// <param name="appointmentId">The ID of the appointment.</param>
        /// <returns>true if there is a visit in the DB matching the appointment ID</returns>
        public bool IsVisitPresent(int appointmentId)
        {
            if (appointmentId < 0)
            {
                throw new ArgumentException("The appointment ID cannot be negative.");
            }

            string selectStatement =
                "SELECT @NumberOfVisits = COUNT(appointmentId) " +
                "FROM Visit " +
                "WHERE appointmentId = @AppointmentId ";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    SqlParameter countParameter = new SqlParameter("@NumberOfVisits", SqlDbType.Int, 1)
                    {
                        Direction = ParameterDirection.Output
                    };
                    selectCommand.Parameters.Add(countParameter);
                    selectCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);
                    selectCommand.ExecuteNonQuery();

                    return(Convert.ToInt32(countParameter.Value) > 0);
                }
            }
        }
Ejemplo n.º 9
0
        /// <summary>
        /// This method will change the accpted nurse status to the value memeber
        /// </summary>
        /// <param name="nurseID">Nurse ID value</param>
        /// <param name="valueMember">Value Member</param>
        public void ChangeStatus(int nurseID, int valueMember)
        {
            string updateStatus = "UPDATE nurse " +
                                  "SET status_id = @valuemember " +
                                  "WHERE id = @nurseID";

            try
            {
                using (SqlConnection connection = ClinicDBConnection.GetConnection())
                {
                    connection.Open();
                    using (SqlCommand updateCommand = new SqlCommand(updateStatus, connection))
                    {
                        updateCommand.Parameters.AddWithValue("@valuemember", valueMember);
                        updateCommand.Parameters.AddWithValue("@nurseID", nurseID);
                        updateCommand.ExecuteNonQuery();
                    }
                    connection.Close();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 10
0
        /// <summary>
        /// Returns a Patient equal to the accepted ID
        /// </summary>
        /// <param name="patientID">Accepted Patient ID</param>
        /// <returns>returns a patient equal to the accepted patientID</returns>
        public Patient GetPatientByID(int patientID)
        {
            string  selectStatement = "SELECT * FROM patient WHERE id = @patientID AND status_id = 1; ";
            Patient patient         = new Patient();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    selectCommand.Parameters.AddWithValue("@patientID", @patientID);
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            patient.PatientID = (int)reader["id"];
                            patient.PersonId  = (int)reader["personal_information_id"];
                            patient.Status    = (int)reader["status_id"];
                        }
                        PopulatePersonalInformation(patient);
                    }
                }
                connection.Close();

                return(patient);
            }
        }
Ejemplo n.º 11
0
        /// <summary>
        /// Gets a list of all patients with a firstname equal to the accepted string value
        /// </summary>
        /// <param name="firstname">Accepted Lastname value</param>
        /// <returns>All patients with a firstname equal to firstname</returns>
        public List <Patient> GetPatientByFirstName_Only(string @firstname)
        {
            List <Patient> patients        = new List <Patient>();
            string         selectStatement = "SELECT patient.id, personal_information_id  FROM patient " +
                                             "JOIN person person ON personal_information_id = person.id " +
                                             "WHERE person.id IN (SELECT id FROM person WHERE first_name = @firstname_clean)" +
                                             " AND status_id = 1; ";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("firstname_clean", @firstname);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Patient patient = new Patient
                            {
                                PatientID = (int)reader["id"],
                                PersonId  = (int)reader["personal_information_id"]
                            };
                            PopulatePersonalInformation(patient);
                            patients.Add(patient);
                        }
                    }
                }
                connection.Close();
            }
            return(patients);
        }
Ejemplo n.º 12
0
        /// <summary>
        /// Gets all patients from DB with personal information populated
        /// </summary>
        /// <returns>List of all patients</returns>
        public List <Patient> GetAllPatientsWith_ActiveStatus()
        {
            List <Patient> patients        = new List <Patient>();
            string         selectStatement = "SELECT * FROM patient " +
                                             "WHERE status_id = 1;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Patient patient = new Patient
                            {
                                PatientID = (int)reader["id"],
                                PersonId  = (int)reader["personal_information_id"],
                                Status    = (int)reader["status_id"]
                            };
                            PopulatePersonalInformation(patient);
                            patients.Add(patient);
                        }
                    }
                }
                connection.Close();
            }
            return(patients);
        }
Ejemplo n.º 13
0
        /// <summary>
        /// Returns appointents equal to the patient ID
        /// </summary>
        /// <param name="selectedRowPatientID">Patient ID value</param>
        /// <returns>List of appointments</returns>
        public List <Appointment> GetAppointmentsByPatientID(int selectedRowPatientID)
        {
            List <Appointment> appointments    = new List <Appointment>();
            string             selectStatement = "Select id, scheduled_datetime, reason_for_visit, doctor_id, patient_id FROM appointment WHERE patient_id = @id_clean";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();

                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    selectCommand.Parameters.AddWithValue("@id_clean", selectedRowPatientID);
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Appointment appointment = new Appointment();
                            appointment.Doctor           = new Doctor();
                            appointment.Patient          = new Patient();
                            appointment.AppointmentID    = (int)reader["id"];
                            appointment.Scheduled_Date   = (DateTime)reader["scheduled_datetime"];
                            appointment.Reason_For_Visit = reader["reason_for_visit"].ToString();
                            appointment.Doctor           = GetDoctorByID((int)reader["doctor_id"]);
                            appointment.Patient          = GetPatientByID((int)reader["patient_id"]);

                            appointments.Add(appointment);
                        }
                    }
                }
                connection.Close();
            }
            return(appointments);
        }
Ejemplo n.º 14
0
        /// <summary>
        /// Returns a list of Patients equal to the accepted DateTime
        /// </summary>
        /// <param name="dateOfBirth">Accepted DateTime value - shorted to just Date</param>
        /// <returns>List of Patients equal to the accepted DateTime</returns>
        public List <Patient> GetAllPatients_DOB(DateTime dateOfBirth)
        {
            List <Patient> patients        = new List <Patient>();
            string         selectStatement = "SELECT patient.id, personal_information_id  FROM patient " +
                                             "JOIN person person ON personal_information_id = person.id " +
                                             "WHERE person.id IN (SELECT id FROM person WHERE date_of_birth = @dateOfBirthdate_clean) AND status_id = 1;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("dateOfBirthdate_clean", @dateOfBirth);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Patient patient = new Patient
                            {
                                PatientID = (int)reader["id"],
                                PersonId  = (int)reader["personal_information_id"]
                            };
                            PopulatePersonalInformation(patient);
                            patients.Add(patient);
                        }
                    }
                }
                connection.Close();
            }
            return(patients);
        }
Ejemplo n.º 15
0
        /// <summary>
        /// Method returns the role of the person for the given username.
        /// The role can be nurse or administrator.
        /// </summary>
        /// <param name="username">username of the person</param>
        /// <returns>the role of the person</returns>
        public string GetRole(string username)
        {
            if (string.IsNullOrEmpty(username))
            {
                throw new ArgumentNullException("username", "The username cannot be null or empty.");
            }

            string selectStatement =
                "SELECT @Role = role " +
                "FROM Credential " +
                "WHERE userName = @Username";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    SqlParameter roleParameter = new SqlParameter("@Role", SqlDbType.VarChar, 25)
                    {
                        Direction = ParameterDirection.Output
                    };
                    selectCommand.Parameters.Add(roleParameter);
                    selectCommand.Parameters.AddWithValue("@Username", username);
                    selectCommand.ExecuteNonQuery();
                    return(roleParameter.Value.ToString());
                }
            }
        }
Ejemplo n.º 16
0
        /// <summary>
        /// Method returns the username given personId.
        /// </summary>
        /// <param name="inputedPersonId">personId of the person</param>
        /// <returns>the username of the person</returns>
        public string GetUsername(int inputedPersonId)
        {
            if (inputedPersonId < 0)
            {
                throw new ArgumentException("The person ID cannot be negative.", "inputedPersonId");
            }

            string selectStatement =
                "SELECT @Username = userName " +
                "FROM Credential " +
                "WHERE personId = @InputedPersonId";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    SqlParameter usernameParameter = new SqlParameter("@Username", SqlDbType.VarChar, 25)
                    {
                        Direction = ParameterDirection.Output
                    };
                    selectCommand.Parameters.Add(usernameParameter);
                    selectCommand.Parameters.AddWithValue("@InputedPersonId", inputedPersonId);
                    selectCommand.ExecuteNonQuery();
                    return(usernameParameter.Value.ToString());
                }
            }
        }
Ejemplo n.º 17
0
        /// <summary>
        /// Returns a Doctor equal to the doctorID
        /// </summary>
        /// <param name="doctorID">Doctor ID value</param>
        /// <returns>returns Doctor equal to the accepted DoctorID</returns>
        public static Doctor GetDoctorByID(int doctorID)
        {
            string selectStatement = "SELECT * FROM doctor WHERE id = @doctorID;";
            Doctor doctor          = new Doctor();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    selectCommand.Parameters.AddWithValue("@doctorID", @doctorID);
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            doctor.DoctorId = (int)reader["id"];
                            doctor.PersonId = (int)reader["person_id"];
                        }
                        PopulatePersonalInformation(doctor);
                    }
                }
                connection.Close();

                return(doctor);
            }
        }
Ejemplo n.º 18
0
        /// <summary>
        /// Method that can be used to add a diagnosis to the database.
        /// </summary>
        /// <param name="appointmentId">The ID of the appointment.</param>
        /// <param name="diagnosisName">A string describing the diagnosis.</param>
        /// <param name="isFinal">A boolean value indicating if the diagnosis is final.</param>
        public void AddDiagnosis(int appointmentId, string diagnosisName, bool isFinal)
        {
            if (appointmentId < 0)
            {
                throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId");
            }

            if (string.IsNullOrEmpty(diagnosisName))
            {
                throw new ArgumentNullException("diagnosisName", "The diagnosis name cannot be null or empty.");
            }

            string insertStatement =
                "INSERT Diagnosis (appointmentId, diagnosisName, isFinal) " +
                "VALUES (@AppointmentId, @DiagnosisName, @IsFinal)";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
                {
                    insertCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);
                    insertCommand.Parameters.AddWithValue("@DiagnosisName", diagnosisName);
                    insertCommand.Parameters.AddWithValue("@IsFinal", isFinal);
                    insertCommand.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 19
0
        /// <summary>
        /// Gets all the appointments for a specified patient.
        /// </summary>
        /// <param name="patient">Patient object</param>
        /// <returns>a List of appointment objects</returns>
        public List <Appointment> GetAllAppointmentsForPatient(Patient patient)
        {
            List <Appointment> appointments    = new List <Appointment>();
            string             selectStatement = "SELECT * FROM appointment WHERE patient_id = @patientID;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    selectCommand.Parameters.AddWithValue("@patientID", patient.PatientID);
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Appointment appointment = new Appointment
                            {
                                AppointmentID    = (int)reader["id"],
                                Scheduled_Date   = (DateTime)reader["scheduled_datetime"],
                                Reason_For_Visit = reader["reason_for_visit"].ToString(),
                                Doctor           = new Doctor
                                {
                                    DoctorId = (int)reader["doctor_id"]
                                },
                                Patient = patient
                            };
                            PopulatePersonalInformation(appointment.Doctor);
                            appointments.Add(appointment);
                        }
                    }
                }
                connection.Close();
            }
            return(appointments);
        }
Ejemplo n.º 20
0
        /// <summary>
        /// This method returns status description equal to the accepted ID
        /// </summary>
        /// <param name="statusID">Status ID value</param>
        /// <returns>String rep of the status value</returns>
        public string GetStatusByID(int statusID)
        {
            string status_description = "";
            string selectStatement    = "Select status FROM status_code " +
                                        "WHERE id = @statusID";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("statusID", statusID);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            {
                                status_description = reader["status"].ToString();
                            }
                        }
                    }
                }
                connection.Close();
            }
            return(status_description);
        }
Ejemplo n.º 21
0
        /// <summary>
        /// Changes the employee credentials in the database..
        /// </summary>
        /// <param name="edited_employee">the new employee object</param>
        /// <param name="old_employee">the existing employee object</param>
        /// <returns>Yes/No if update operation was successful</returns>
        public bool EditEmployeeInfo(Employee edited_employee, Employee old_employee)
        {
            string updateStatement = "UPDATE users " +
                                     "SET username = @new_username, " +
                                     "password = HASHBYTES('SHA2_256', @new_password) " +
                                     "WHERE username = @old_username AND " +
                                     "password = @old_password AND " +
                                     "id = @old_id AND " +
                                     "person_id = @old_personID;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand update = new SqlCommand(updateStatement, connection))
                {
                    update.Parameters.AddWithValue("@new_username", edited_employee.UserName);
                    update.Parameters.AddWithValue("@new_password", edited_employee.Password);
                    update.Parameters.AddWithValue("@old_username", old_employee.UserName);
                    update.Parameters.AddWithValue("@old_password", old_employee.HashedPassword);
                    update.Parameters.AddWithValue("@old_id", old_employee.EmployeeID);
                    update.Parameters.AddWithValue("@old_personID", old_employee.PersonId);
                    int count = update.ExecuteNonQuery();
                    connection.Close();
                    return(count > 0);
                }
            }
        }
Ejemplo n.º 22
0
        /// <summary>
        /// Method that adds the specified Visit object to the database.
        /// </summary>
        /// <param name="theVisit">The Visit object being added to the database.</param>
        public void AddVisit(Visit theVisit)
        {
            if (theVisit == null)
            {
                throw new ArgumentNullException("theVisit", "The visit cannot be null.");
            }

            string insertStatement =
                "INSERT Visit (appointmentId, weight, systolicBloodPressure, diastolicBloodPressure, bodyTemperature, pulse, symptoms, nurseId) " +
                "VALUES (@AppointmentId, @Weight, @SystolicBloodPressure, @DiastolicBloodPressure, @BodyTemperature, @Pulse, @Symptoms, @NurseId)";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
                {
                    insertCommand.Parameters.AddWithValue("@AppointmentId", theVisit.AppointmentId);
                    insertCommand.Parameters.AddWithValue("@Weight", theVisit.Weight);
                    insertCommand.Parameters.AddWithValue("@SystolicBloodPressure", theVisit.SystolicBloodPressure);
                    insertCommand.Parameters.AddWithValue("@DiastolicBloodPressure", theVisit.DiastolicBloodPressure);
                    insertCommand.Parameters.AddWithValue("@BodyTemperature", theVisit.BodyTemperature);
                    insertCommand.Parameters.AddWithValue("@Pulse", theVisit.Pulse);
                    insertCommand.Parameters.AddWithValue("@Symptoms", theVisit.Symptoms);
                    insertCommand.Parameters.AddWithValue("@NurseId", theVisit.NurseId);
                    insertCommand.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 23
0
        /// <summary>
        /// Method that returns true if the specified doctor is unavailable at the specified start time
        /// assuming appointments are structured such that only start time is needed to confirm no overlap.
        /// </summary>
        /// <param name="doctorId">The ID of the doctor in question.</param>
        /// <param name="startDateTime">The date and time of the appointment in question.</param>
        /// <returns>True if the specified doctor is booked at the specified time, false otherwise.</returns>
        public bool DoctorIsBooked(int doctorId, DateTime startDateTime)
        {
            if (doctorId < 0)
            {
                throw new ArgumentException("The doctor's ID cannot be negative.", "doctorId");
            }
            if (startDateTime == null)
            {
                throw new ArgumentNullException("startDateTime", "The start date and time of the appointment cannot be null.");
            }

            string selectStatement =
                "SELECT @NumberOfAppointments = COUNT(appointmentId) " +
                "FROM Appointment " +
                "WHERE doctorId = @DoctorId " +
                "AND startDateTime = @StartDateTime";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    SqlParameter countParameter = new SqlParameter("@NumberOfAppointments", SqlDbType.Int, 1)
                    {
                        Direction = ParameterDirection.Output
                    };
                    selectCommand.Parameters.Add(countParameter);
                    selectCommand.Parameters.AddWithValue("@DoctorId", doctorId);
                    selectCommand.Parameters.AddWithValue("@StartDateTime", startDateTime);
                    selectCommand.ExecuteNonQuery();
                    return(Convert.ToInt32(countParameter.Value) > 0);
                }
            }
        }
Ejemplo n.º 24
0
        /// <summary>
        /// Method that deletes the specified appointment from the database.
        /// </summary>
        /// <param name="appointmentId">The ID of the appointment being deleted from the database.</param>
        public void DeleteAppointment(int appointmentId)
        {
            if (appointmentId < 0)
            {
                throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId");
            }

            if (this.visitSource.IsVisitPresent(appointmentId))
            {
                throw new ArgumentException("The specified appointment already has a visit associated with it; therefore, it cannot be deleted.", "appointmentId");
            }

            string deleteStatement =
                "DELETE FROM Appointment " +
                "WHERE appointmentId = @AppointmentId";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection))
                {
                    deleteCommand.Parameters.AddWithValue("@AppointmentId", appointmentId);
                    deleteCommand.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 25
0
        /// <summary>
        /// Retrieves all nurses from the database
        /// </summary>
        /// <returns>List of all nurses</returns>
        public List <Nurse> GetAllNurses()
        {
            string selectStatement = "SELECT n.*, username, password, u.id " +
                                     "FROM users AS u " +
                                     "JOIN nurse AS n ON n.person_id = u.person_id;";
            List <Nurse> nurses = new List <Nurse>();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Nurse nurse = new Nurse
                            {
                                NurseID  = (int)reader["id"],
                                PersonId = (int)reader["person_id"],
                                StatusID = (int)reader["status_id"],
                                UserName = reader["username"].ToString(),
                                //     EmployeeID = (int)reader["users.id"]
                            };
                            PopulatePersonalInformation(nurse);

                            nurses.Add(nurse);
                        }
                    }
                }
                connection.Close();
            }
            return(nurses);
        }
Ejemplo n.º 26
0
        /// <summary>
        /// Gets a specific nurse by their id
        /// </summary>
        /// <param name="id">is the id used to select the nurse</param>
        /// <returns>Nurse object</returns>
        public static Nurse GetNurseByID(int id)
        {
            string selectStatement = "SELECT n.*, username, password, u.id " +
                                     "FROM users AS u " +
                                     "JOIN nurse AS n ON n.person_id = u.person_id " +
                                     "WHERE n.id = @NurseID;";
            Nurse nurse = new Nurse();

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("@NurseID", id);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            nurse.NurseID  = (int)reader["id"];
                            nurse.PersonId = (int)reader["person_id"];
                            nurse.StatusID = (int)reader["status_id"];
                            nurse.UserName = reader["username"].ToString();
                            PopulatePersonalInformation(nurse);
                        }
                    }
                }
                connection.Close();
            }
            return(nurse);
        }
Ejemplo n.º 27
0
        private static Person PopulatePersonalInformation(Person person)
        {
            string selectStatement = "SELECT * FROM person WHERE id = @personID;";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("@personID", person.PersonId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            person.LastName             = reader["last_name"].ToString();
                            person.FirstName            = reader["first_name"].ToString();
                            person.DateOfBirth          = (DateTime)reader["date_of_birth"];
                            person.SocialSecurityNumber = reader["ssn"].ToString();
                            person.Gender        = reader["gender"].ToString();
                            person.StreetAddress = reader["street_address"].ToString();
                            person.Phone         = reader["phone"].ToString();
                            person.Zipcode       = reader["zipcode"].ToString();
                        }
                    }
                }
                connection.Close();
            }
            return(person);
        }
Ejemplo n.º 28
0
        /// <summary>
        /// This method will return a list of all Nurses with a lastname equal the the accepted lastname
        /// </summary>
        /// <param name="lastname">Nurse last name</param>
        /// <returns>List of all nurses returned by query</returns>
        public List <Nurse> GetAllNursesByLastname(string lastname)
        {
            List <Nurse> nurses          = new List <Nurse>();
            string       selectStatement = "SELECT nurse.id, person_id, status_id  FROM nurse " +
                                           "JOIN person person ON person_id = person.id " +
                                           "WHERE person.id IN (SELECT id FROM person WHERE last_name = @lastname_clean)";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(selectStatement, connection))
                {
                    command.Parameters.AddWithValue("lastname_clean", @lastname);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Nurse nurse = new Nurse
                            {
                                NurseID  = (int)reader["id"],
                                PersonId = (int)reader["person_id"],
                                StatusID = (int)reader["status_id"]
                            };
                            PopulatePersonalInformation(nurse);

                            nurses.Add(nurse);
                        }
                    }
                }
                connection.Close();
            }
            return(nurses);
        }
Ejemplo n.º 29
0
        /// <summary>
        /// Method that deletes the specified patient from the Person and Patient tables in the database.
        /// </summary>
        /// <param name="thePatient">An object representing the patient being deleted from the database.</param>
        public void DeletePatient(Patient thePatient)
        {
            if (thePatient == null)
            {
                throw new ArgumentNullException("thePatient", "The patient object cannot be null.");
            }

            string deletePersonStatement =
                "DELETE FROM Person " +
                "WHERE personId = @PersonId";

            string deletePatientStatement =
                "DELETE FROM Patient " +
                "WHERE patientId = @PatientId";

            using (SqlConnection connection = ClinicDBConnection.GetConnection())
            {
                connection.Open();
                SqlCommand     deleteCommand = connection.CreateCommand();
                SqlTransaction transaction;

                //Start a local transaction
                transaction = connection.BeginTransaction("DeletePatient");

                deleteCommand.Connection  = connection;
                deleteCommand.Transaction = transaction;
                deleteCommand.Parameters.AddWithValue("@PersonId", thePatient.PersonId);
                deleteCommand.Parameters.AddWithValue("@PatientId", thePatient.PatientId);

                try
                {
                    deleteCommand.CommandText = deletePatientStatement;
                    deleteCommand.ExecuteNonQuery();
                    deleteCommand.CommandText = deletePersonStatement;
                    deleteCommand.ExecuteNonQuery();

                    //Attemp to commit the transaction
                    transaction.Commit();
                    Console.WriteLine("Data was deleted from both tables.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("   Message: {0}", ex.Message);

                    // Attemp to roll back the transaction
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("    Message: {0}", ex2.Message);
                    }
                    throw new ArgumentException("The patient was not deleted.");
                }
            }
        }
Ejemplo n.º 30
0
        /// <summary>
        /// This method adds the accepted nurse to the database
        /// </summary>
        /// <param name="addedNurse">The Nurse object to add</param>
        /// <returns>The Nurse object</returns>
        public Nurse AddNurse(Nurse addedNurse)
        {
            Nurse returnedNurse             = new Nurse();
            int   addedNurse_PersonalInfoID = -1;

            try
            {
                using (SqlConnection connection = ClinicDBConnection.GetConnection())
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        string insertPerson = "INSERT PERSON (last_name, first_name, date_of_birth, ssn, gender, street_address, phone, zipcode)" +
                                              "VALUES(@lastName, @firstName, @DOB, @SSN, @Gender, @streetAddress, @phoneNumber, @Zipcode)";
                        string insertNurse = "INSERT Nurse(person_id, status_id) VALUES (@personalID, @status_id)";

                        using (SqlCommand insertPersonCommand = new SqlCommand(insertPerson, connection))
                        {
                            insertPersonCommand.Transaction = transaction;
                            insertPersonCommand.Parameters.AddWithValue("lastName", addedNurse.LastName);
                            insertPersonCommand.Parameters.AddWithValue("firstName", addedNurse.FirstName);
                            insertPersonCommand.Parameters.AddWithValue("DOB", addedNurse.DateOfBirth);
                            insertPersonCommand.Parameters.AddWithValue("SSN", addedNurse.SocialSecurityNumber);
                            insertPersonCommand.Parameters.AddWithValue("Gender", addedNurse.Gender);
                            insertPersonCommand.Parameters.AddWithValue("streetAddress", addedNurse.StreetAddress);
                            insertPersonCommand.Parameters.AddWithValue("phoneNumber", addedNurse.Phone);
                            insertPersonCommand.Parameters.AddWithValue("Zipcode", addedNurse.Zipcode);

                            insertPersonCommand.ExecuteNonQuery();

                            string selectStatement = "SELECT IDENT_CURRENT('Person') FROM Person";

                            using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                            {
                                selectCommand.Transaction = transaction;
                                addedNurse_PersonalInfoID = Convert.ToInt32(selectCommand.ExecuteScalar());
                            }
                        }

                        using (SqlCommand insertNurseCommand = new SqlCommand(insertNurse, connection))
                        {
                            insertNurseCommand.Transaction = transaction;
                            insertNurseCommand.Parameters.AddWithValue("@personalID", addedNurse_PersonalInfoID);
                            insertNurseCommand.Parameters.AddWithValue("status_id", addedNurse.StatusID);
                            insertNurseCommand.ExecuteNonQuery();
                        }

                        transaction.Commit();

                        addedNurse.PersonId = addedNurse_PersonalInfoID;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
            }
            return(addedNurse);
        }