/// <summary>
/// get a person by their person id
/// </summary>
/// <param name="personID"></param>
/// <returns>a person searched by personID</returns>
public Person GetPersonByID(int personID)
{
Person person = new Person();
string selectStatement =
"SELECT personID, lastName, firstName, dateOfBirth, streetAddress, city, stateCode, zipCode, phoneNumber, ssn " +
"FROM Person " +
"WHERE personID = @personID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@personID", personID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
person.PersonID = (int)reader["personID"];
person.LastName = (string)reader["lastName"];
person.FirstName = (string)reader["firstName"];
person.DateOfBirth = (DateTime)reader["dateOfBirth"];
person.StreetAddress = (string)reader["streetAddress"];
person.City = (string)reader["city"];
person.StateCode = (string)reader["stateCode"];
person.ZipCode = (int)reader["zipCode"];
person.PhoneNumber = (string)reader["phoneNumber"];
person.SSN = (string)reader["ssn"];
}
}
}
return(person);
}
}
/// <summary>
/// Retrieves doctorID by personID
/// </summary>
/// <param name="personID">Person ID to search</param>
/// <returns>Doctor of that person ID</returns>
public Doctor GetDoctorByPersonID(int personID)
{
Doctor doctor = new Doctor();
string selectStatement =
"SELECT doctorID, personID " +
"FROM doctor " +
"WHERE personID = @personID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@personID", personID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
doctor.DoctorID = (int)reader["doctorID"];
doctor.PersonID = (int)reader["personID"];
}
}
}
}
return(doctor);
}
/// <summary>
/// returs list of all nurses
/// </summary>
/// <returns>list of all nurses</returns>
public List <Nurse> GetAllNurses()
{
List <Nurse> nurseList = new List <Nurse>();
string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, n.nurseID FROM person p JOIN nurse n ON p.personID = n.personID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Nurse nurse = new Nurse();
nurse.LastName = reader["lastName"].ToString();
nurse.FirstName = reader["firstName"].ToString();
nurse.DateOfBirth = (DateTime)reader["dateOfBirth"];
nurse.NurseID = Convert.ToInt32(reader["nurseID"]);
nurseList.Add(nurse);
}
}
}
}
return(nurseList);
}
/// <summary>
/// Get all available tests
/// </summary>
/// <returns>A list of all available tests</returns>
public List <Test> GetAllTests()
{
List <Test> testList = new List <Test>();
string selectStatement =
"SELECT * " +
"FROM test ";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Test test = new Test();
test.TestCode = (string)reader["testCode"];
test.TestName = (string)reader["testName"];
testList.Add(test);
}
}
}
}
return(testList);
}
/// <summary>
/// Returns all the specialties of a doctor
/// </summary>
/// <param name="docID"></param>
/// <returns></returns>
public List <Specialty> GetSpecialtyByDoctorID(int docID)
{
List <Specialty> specialties = new List <Specialty>();
string selectStatement =
"SELECT specialtyName " +
"FROM specialty " +
"INNER JOIN Doctor " +
"ON Doctor.DoctorID = Specialty.DoctorID " +
"WHERE Doctor.doctorID = @docID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@docID", docID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Specialty specialty = new Specialty();
specialty.SpecialityName = reader["specialtyName"].ToString();
specialties.Add(specialty);
}
}
}
return(specialties);
}
}
public Appointment GetAppointmentByAppointmentID(int appointmentID)
{
Appointment appointment = new Appointment();
string selectStatement =
"SELECT a.appointmentID, a.patientID, (p.firstName + ' ' + p.lastName) AS name, a.dateTime, a.reasonForVisit FROM Appointment a JOIN doctor d" +
" ON a.doctorID = d.doctorID JOIN person p ON p.personID = d.personID WHERE appointmentID = @appmtID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@appmtID", appointmentID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
appointment.AppointmentID = (int)reader["appointmentID"];
appointment.PatientID = (int)reader["patientID"];
appointment.DoctorName = reader["name"].ToString();
appointment.DateTime = (DateTime)reader["dateTime"];
appointment.ReasonForVisit = (string)reader["reasonForVisit"];
}
}
}
}
return(appointment);
}
/// <summary>
/// retireve a person's login information
/// </summary>
/// <param name="username">As a string</param>
/// <param name="password">As a string</param>
/// <returns></returns>
public DataTable GetLogin(string username, string password)
{
HashingService hashing = new HashingService();
DataTable dt = new DataTable();
string selectStatement = "SELECT l.personID, l.userName, l.password, (p.firstName + ' ' + p.lastName) AS name" +
" FROM login l JOIN person p ON p.personID = l.personID WHERE userName = @username AND password = @password";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand(selectStatement, connection);
sc.Parameters.AddWithValue("@username", username);
sc.Parameters.AddWithValue("@password", hashing.PasswordHashing(password));
SqlDataReader reader = sc.ExecuteReader();
dt.Columns.Add("personID", typeof(int));
dt.Columns.Add("userName", typeof(string));
dt.Columns.Add("password", typeof(string));
dt.Columns.Add("name", typeof(string));
dt.Load(reader);
}
return(dt);
}
/// <summary>
/// return a list of all states
/// </summary>
/// <returns>list of 50 states</returns>
public List <State> GetAllStates()
{
List <State> allStates = new List <State>();
string selectStatement = "SELECT * FROM state";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand(selectStatement, connection);
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
State state = new State();
state.StateCode = reader["stateCode"].ToString();
state.StateName = reader["stateName"].ToString();
allStates.Add(state);
}
}
}
}
return(allStates);
}
/// <summary>
/// return true if user is a nurse
/// </summary>
/// <param name="personID">As an integer</param>
/// <returns>true if user is a nurse</returns>
public Boolean IsNurse(int personID)
{
DataTable dt = new DataTable();
Boolean isNurse = false;
string selectStatement = "SELECT CASE WHEN EXISTS(SELECT * FROM nurse n WHERE n.personID = p.personID)" +
" THEN 'is nurse' ELSE 'not nurse' END FROM person p WHERE p.personID = @personID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand(selectStatement, connection);
sc.Parameters.AddWithValue("@personID", personID);
using (SqlDataReader reader = sc.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetValue(0).ToString() == "is nurse")
{
isNurse = true;
}
}
}
return(isNurse);
}
}
/// <summary>
/// return a list of patients by date of birth
/// </summary>
/// <param name="dob"></param>
/// <returns>list of patients searched by date of birth</returns>
public List <Patient> GetPatientsByDOB(DateTime dob)
{
List <Patient> searchList = new List <Patient>();
string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, pa.patientID FROM person p JOIN patient pa ON p.personID = pa.personID WHERE CAST(p.dateOfBirth as DATE) = @DOB";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@DOB", dob.ToString("yyyy-MM-dd"));
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Patient patient = new Patient();
patient.LastName = reader["lastName"].ToString();
patient.FirstName = reader["firstName"].ToString();
patient.DateOfBirth = (DateTime)reader["dateOfBirth"];
patient.PatientID = Convert.ToInt32(reader["patientID"]);
searchList.Add(patient);
}
}
}
}
return(searchList);
}
/// <summary>
/// Retrieves a person by the doctorsID
/// </summary>
/// <param name="docID">doctorID to search</param>
/// <returns>Person of that doctorID</returns>
public Person GetPersonByDoctorID(int docID)
{
Person person = new Person();
string selectStatement =
"SELECT lastName, firstName, doctorID " +
"FROM Person " +
"INNER JOIN Doctor " +
"ON Doctor.PersonID = Person.PersonID " +
"WHERE doctorID = @docID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@docID", docID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
person.LastName = (string)reader["lastName"];
person.FirstName = (string)reader["firstName"];
}
}
}
return(person);
}
}
/// <summary>
/// Retrieves all doctors in DB
/// </summary>
/// <returns>List of all doctors</returns>
public List <Doctor> GetDoctors()
{
List <Doctor> doctorList = new List <Doctor>();
string selectStatement =
"SELECT doctor.doctorID, doctor.personID " +
"FROM doctor";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Doctor doctor = new Doctor();
doctor.DoctorID = (int)reader["doctorID"];
doctor.PersonID = (int)reader["personID"];
doctorList.Add(doctor);
}
}
}
}
return(doctorList);
}
/// <summary>
/// return a state name based on its code
/// </summary>
/// <param name="stateCode"></param>
/// <returns>name of a state</returns>
public String FindStateNamebyCode(string stateCode)
{
string stateName = "";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand("SELECT stateName FROM state where stateCode = @code", connection))
{
selectCommand.Parameters.AddWithValue("@code", stateCode);
stateName = selectCommand.ExecuteScalar().ToString();
}
}
return(stateName);
}
/// <summary>
/// return whether nurse is active or inactive
/// </summary>
/// <param name="nurseID"></param>
/// <returns>1 for active, 0 for inactive</returns>
public int GetNurseStatus(int nurseID)
{
int active = 0;
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand("SELECT active_status FROM nurse where nurseID = @id", connection))
{
selectCommand.Parameters.AddWithValue("@id", nurseID);
active = Convert.ToInt32(selectCommand.ExecuteScalar());
}
}
return(active);
}
/// <summary>
/// add a new nurse
/// </summary>
/// <param name="person"></param>
public Boolean AddNurse(Person person, String active)
{
Boolean success = false;
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
string insertStatement = "INSERT Person(lastName, firstName, dateOfBirth, streetAddress, city, stateCode, zipCode, phoneNumber, ssn) " +
"VALUES(@lastName, @firstName, @dateOfBirth, @streetAddress, @city, @stateCode, @zipCode, @phoneNumber, @ssn)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection, transaction);
insertCommand.Parameters.AddWithValue("@lastName", person.LastName);
insertCommand.Parameters.AddWithValue("@firstName", person.FirstName);
insertCommand.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth.Date);
insertCommand.Parameters.AddWithValue("@streetAddress", person.StreetAddress);
insertCommand.Parameters.AddWithValue("@city", person.City);
insertCommand.Parameters.AddWithValue("@stateCode", person.StateCode);
insertCommand.Parameters.AddWithValue("@zipCode", person.ZipCode);
insertCommand.Parameters.AddWithValue("@phoneNumber", person.PhoneNumber);
insertCommand.Parameters.AddWithValue("@ssn", person.SSN);
insertCommand.ExecuteNonQuery();
insertCommand = new SqlCommand("INSERT nurse (personID) VALUES ((SELECT IDENT_CURRENT('person')))", connection, transaction);
insertCommand.ExecuteNonQuery();
SqlCommand updateCommand = new SqlCommand("UPDATE nurse SET active_status = @active WHERE nurseID = (SELECT IDENT_CURRENT('nurse'))", connection, transaction);
updateCommand.Parameters.AddWithValue("@active", active);
updateCommand.ExecuteNonQuery();
transaction.Commit();
success = true;
}
catch
{
transaction.Rollback();
}
}
return(success);
}
/// <summary>
/// Add a login to the system
/// </summary>
/// <param name="login">A login object</param>
public void AddLogin(Login login)
{
SqlConnection connection = HealthcareDBConnection.GetConnection();
string insertStatement =
"INSERT Login " +
"(personID, userName, password) " +
"VALUES (@personID, @userName, @password)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@personID", login.PersonID);
insertCommand.Parameters.AddWithValue("@userName", login.UserName);
insertCommand.Parameters.AddWithValue("@password", login.Password);
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement = "SELECT IDENT_CURRENT('Login') FROM Login";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
}
/// <summary>
/// Get a personID from a patientID
/// </summary>
/// <param name="patientID"></param>
/// <returns>a personID </returns>
public int GetPersonID(int patientID)
{
int id;
string selectStatement = "SELECT p.personID FROM person p JOIN patient pa ON pa.personID = p.personID WHERE pa.patientID = @patientID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@patientID", patientID);
id = (int)selectCommand.ExecuteScalar();
}
return(id);
}
}
/// <summary>
/// Updates a nurses information
/// </summary>
/// <param name="personID"></param>
/// <param name="lName"></param>
/// <param name="fName"></param>
/// <param name="dob"></param>
/// <param name="streetAddress"></param>
/// <param name="city"></param>
/// <param name="state"></param>
/// <param name="zipCode"></param>
/// <param name="phone"></param>
/// <param name="ssn"></param>
public void UpdateNurse(int personID, string lName, string fName, DateTime dob, string streetAddress,
string city, string state, int zipCode, string phoneNumber, string ssn, string active)
{
string updateStatement = "UPDATE person " +
"SET firstName = @fName, lastName = @lName, dateOfBirth = @dob, " +
"stateCode = @stateCode, streetAddress = @streetAddress, city = @city, " +
"zipCode = @zip, phoneNumber = @phoneNumber, ssn = @ssn " +
"WHERE personID = @pID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
SqlCommand updateCommand = new SqlCommand(updateStatement, connection, transaction);
updateCommand.Parameters.AddWithValue("@pID", personID);
updateCommand.Parameters.AddWithValue("@lName", lName);
updateCommand.Parameters.AddWithValue("@fName", fName);
updateCommand.Parameters.AddWithValue("@dob", dob.Date);
updateCommand.Parameters.AddWithValue("@streetAddress", streetAddress);
updateCommand.Parameters.AddWithValue("@city", city);
updateCommand.Parameters.AddWithValue("@stateCode", state);
updateCommand.Parameters.AddWithValue("@zip", zipCode);
updateCommand.Parameters.AddWithValue("@phoneNumber", phoneNumber);
updateCommand.Parameters.AddWithValue("@ssn", ssn);
updateCommand.ExecuteNonQuery();
updateCommand = new SqlCommand("UPDATE nurse SET active_status = @active WHERE personID = @pID", connection, transaction);
updateCommand.Parameters.AddWithValue("@active", active);
updateCommand.Parameters.AddWithValue("@pID", personID);
updateCommand.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
}
public void UpdateAppointment(Appointment appointment)
{
string updateStatement = "UPDATE appointment " +
"SET reasonForVisit = @reason, doctorID = @docID, dateTime = @datetime " +
"WHERE appointmentID = @appmtID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@reason", appointment.ReasonForVisit);
updateCommand.Parameters.AddWithValue("@docID", appointment.DoctorID);
updateCommand.Parameters.AddWithValue("@dateTime", appointment.DateTime);
updateCommand.Parameters.AddWithValue("@appmtID", appointment.AppointmentID);
updateCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Adds appointment to the database
/// </summary>
/// <param name="appointment">appointment to add</param>
/// <returns></returns>
public int AddAppointment(Appointment appointment)
{
SqlConnection connection = HealthcareDBConnection.GetConnection();
string insertStatement =
"INSERT Appointment " +
"(patientID, doctorID, dateTime, reasonForVisit) " +
"VALUES (@patientID, @doctorID, @dateTime, @reasonForVisit)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@patientID", appointment.PatientID);
insertCommand.Parameters.AddWithValue("@doctorID", appointment.DoctorID);
insertCommand.Parameters.AddWithValue("@dateTime", appointment.DateTime);
insertCommand.Parameters.AddWithValue("@reasonForVisit", appointment.ReasonForVisit);
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement = "SELECT IDENT_CURRENT('Appointment') FROM Appointment";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int appointmentID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(appointmentID);
}
/// <summary>
/// Gets a list of patients by their last name and DOB
/// </summary>
/// <param name="dateOfBirth"></param>
/// <param name="lname"></param>
/// <returns>list of patients based on last name and DOB</returns>
public List <Patient> GetPatientsByDOBandLastName(DateTime dateOfBirth, string lname)
{
List <Patient> searchList = new List <Patient>();
string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, pa.patientID " +
"FROM person p " +
"JOIN patient pa " +
"ON p.personID = pa.personID " +
"WHERE p.lastName = @lname AND p.dateOfBirth = @dateOfBirth";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@lname", lname);
selectCommand.Parameters.AddWithValue("@dateOfBirth", dateOfBirth);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Patient patient = new Patient();
patient.LastName = reader["lastName"].ToString();
patient.FirstName = reader["firstName"].ToString();
patient.DateOfBirth = (DateTime)reader["dateOfBirth"];
patient.PatientID = Convert.ToInt32(reader["patientID"]);
searchList.Add(patient);
}
}
}
}
return(searchList);
}
/// <summary>
/// Insert a new test into the test result table indicating it has been ordered
/// </summary>
/// <param name="testToOrder"> the test to be ordered</param>
public void OrderTest(Test testToOrder)
{
string insertStatement = "INSERT INTO testResult(visitID,testCode,testDate,results,normal)VALUES(@visitID,@testCode,@testDate,@results,@normal);";
DateTime now = DateTime.Now;
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Transaction = connection.BeginTransaction();
insertCommand.Parameters.AddWithValue("@visitID", testToOrder.VisitId);
insertCommand.Parameters.AddWithValue("@testCode", testToOrder.TestCode);
insertCommand.Parameters.AddWithValue("@testDate", now);
insertCommand.Parameters.AddWithValue("@results", DBNull.Value);
insertCommand.Parameters.AddWithValue("@normal", DBNull.Value);
insertCommand.ExecuteNonQuery();
insertCommand.Transaction.Commit();
}
}
}
/// <summary>
/// Gets an appointment with the doctor displayed and returns a datatable.
/// </summary>
/// <param name="patientID"></param>
/// <returns></returns>
public DataTable GetAppointmentsAndDoctorByPatientID(int patientID)
{
DataTable dataTable = new DataTable();
string selectStatement =
"SELECT appointmentID AS AppointmentID, patientID AS PatientID, person.lastName AS Doctor, dateTime AS 'Appointment Time', reasonForVisit AS Reason " +
"FROM Appointment " +
"JOIN Doctor ON Doctor.doctorID = appointment.doctorID " +
"JOIN Person ON Person.personID = Doctor.personID " +
"WHERE patientID = @patientID " +
"ORDER BY dateTime";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@patientID", patientID);
dataTable.Load(selectCommand.ExecuteReader());
}
}
return(dataTable);
}
/// <summary>
/// Get specific test's assigned to a visit
/// </summary>
/// <param name="visitId">as an integer</param>
/// <returns>a list of tests</returns>
public List <Test> GetTestsByVisitId(int visitId)
{
List <Test> testList = new List <Test>();
string selectStatement =
"SELECT t.testName, tr.testCode, tr.results, tr.normal, tr.testDate, tr.performDate " +
"FROM testResult AS tr " +
"JOIN test AS t ON tr.testCode = t.testCode " +
"WHERE tr.visitID = @visitID";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@visitID", visitId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Test test = new Test();
test.TestCode = (string)reader["testCode"];
test.TestName = (string)reader["testName"];
test.Results = reader["results"] as string;
test.Normal = reader["normal"] as bool?;
test.TestDate = (DateTime)reader["testDate"];
test.PerformDate = reader["performDate"] as DateTime?;
test.VisitId = visitId;
testList.Add(test);
}
}
}
}
return(testList);
}
/// <summary>
/// Returns true if the doctor already has an appointment at that date/time
/// </summary>
/// <param name="doctorID">doctor to search</param>
/// <param name="dateTime">time to search</param>
/// <returns>True if doctor is already scheduled</returns>
public bool CheckIfDoctorHasAppointmentScheduled(int doctorID, DateTime dateTime)
{
Doctor doctor = new Doctor();
Appointment appointment = new Appointment();
string selectStatement =
"SELECT doctor.doctorID, dateTime " +
"FROM doctor " +
"INNER JOIN appointment " +
"ON appointment.doctorID = doctor.doctorID " +
"WHERE doctor.doctorID = @doctorID AND dateTime = @dateTime";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@doctorID", doctorID);
selectCommand.Parameters.AddWithValue("@dateTime", dateTime);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
appointment.DoctorID = (int)reader["doctorID"];
appointment.DateTime = (DateTime)reader["dateTime"];
}
}
}
if (appointment.DoctorID == doctorID && appointment.DateTime == dateTime)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// Gets a visit by the appointmentID
/// </summary>
/// <param name="apptID">appointmentID to search</param>
/// <returns>Visit of that appointmentID</returns>
public Visit GetVisitByAppt(int apptID)
{
Visit visit = new Visit();
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@apptID", apptID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
visit.VisitID = reader["visitID"] as int? ?? 0;
visit.AppointmentID = (int)reader["appointmentID"];
visit.Status = reader["status"] as int? ?? 0;
visit.PatientID = (int)reader["patientID"];
visit.DoctorName = (string)reader["doctorName"];
visit.DoctorID = (int)reader["doctorID"];
visit.DateTime = (DateTime)reader["dateTime"];
visit.Weight = reader["weight"] as decimal? ?? 0;
visit.Temp = reader["temp"] as decimal? ?? 0;
visit.SystolicBP = reader["systolicBP"] as int? ?? 0;
visit.DiastolicBP = reader["diastolicBP"] as int? ?? 0;
visit.Pulse = reader["pulse"] as int? ?? 0;
visit.Symptoms = reader["symptoms"] as string;
visit.InitialDiagnosis = reader["initialDiagnosis"] as string;
visit.FinalDiagnosis = reader["finalDiagnosis"] as string;
}
}
}
}
return(visit);
}
/// <summary>
/// Gets all the appointments a patient is scheduled for
/// </summary>
/// <param name="patientID">patient id for search</param>
/// <returns></returns>
public List <Appointment> GetAppointmentsByPatientID(int patientID)
{
List <Appointment> appointments = new List <Appointment>();
string selectStatement =
"SELECT appointmentID, patientID, doctorID, dateTime, reasonForVisit " +
"FROM Appointment " +
"WHERE patientID = @patientID " +
"ORDER BY dateTime";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@patientID", patientID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Appointment appointment = new Appointment();
appointment.AppointmentID = (int)reader["appointmentID"];
appointment.PatientID = (int)reader["patientID"];
appointment.DoctorID = (int)reader["doctorID"];
appointment.DateTime = (DateTime)reader["dateTime"];
appointment.ReasonForVisit = (string)reader["reasonForVisit"];
appointments.Add(appointment);
}
}
}
}
return(appointments);
}
/// <summary>
/// Delete the patient from the db
/// </summary>
/// <param name="patientID">patientID as an integer</param>
/// <returns></returns>
public Boolean DeletePatient(int patientID)
{
Boolean success = false;
int personID;
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
SqlCommand selectCommand = new SqlCommand("SELECT p.personID FROM person p JOIN patient pa ON pa.personID = p.personID WHERE pa.patientID = @patientID", connection, transaction);
selectCommand.Parameters.AddWithValue("@patientID", patientID);
personID = (int)selectCommand.ExecuteScalar();
SqlCommand deleteCommand = new SqlCommand("DELETE FROM patient WHERE patientID = @patientID", connection, transaction);
deleteCommand.Parameters.AddWithValue("@patientId", patientID);
deleteCommand.ExecuteNonQuery();
deleteCommand = new SqlCommand("DELETE FROM person WHERE personID = @personID", connection, transaction);
deleteCommand.Parameters.AddWithValue("@personID", personID);
deleteCommand.ExecuteNonQuery();
transaction.Commit();
success = true;
}
catch
{
transaction.Rollback();
}
}
return(success);
}
/// <summary>
/// Update an existing test
/// </summary>
/// <param name="visitID">visitid as an integer</param>
/// <param name="testCode">the test code as string</param>
/// <param name="result">as string</param>
/// <param name="normal">as bool</param>
public void UpdateTestResult(int visitID, string testCode, string result, bool normal, DateTime performDate)
{
string updateStatement = "UPDATE testResult SET results = @result, normal = @normal, performDate = @performDate WHERE visitID = @visitID AND testCode = @testCode;";
DateTime now = DateTime.Now;
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection))
{
updateCommand.Transaction = connection.BeginTransaction();
updateCommand.Parameters.AddWithValue("@visitID", visitID);
updateCommand.Parameters.AddWithValue("@testCode", testCode);
updateCommand.Parameters.AddWithValue("@result", result);
updateCommand.Parameters.AddWithValue("@normal", normal);
updateCommand.Parameters.AddWithValue("@performDate", performDate);
updateCommand.ExecuteNonQuery();
updateCommand.Transaction.Commit();
}
}
}
/// <summary>
/// update a patients personal information
/// </summary>
/// <param name="personID"></param>
/// <param name="lastName"></param>
/// <param name="zipCode"></param>
/// <param name="firstName"></param>
/// <param name="street"></param>
/// <param name="city"></param>
/// <param name="state"></param>
/// <param name="phone"></param>
public void UpdatePatient(int personID, string lastName, int zipCode, string firstName, string street, string city, string state, string phone, string ssn, DateTime dob)
{
string updateStatement = "UPDATE person SET firstName = @firstName, lastName = @lastName, stateCode = @stateCode, phoneNumber = @phone, streetAddress = @street, " +
"city = @city, zipCode = @zip, ssn = @ssn, dateOfBirth = @dob WHERE personID = @id";
using (SqlConnection connection = HealthcareDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection))
{
updateCommand.Parameters.AddWithValue("@id", personID);
updateCommand.Parameters.AddWithValue("@lastName", lastName);
updateCommand.Parameters.AddWithValue("@firstName", firstName);
updateCommand.Parameters.AddWithValue("@street", street);
updateCommand.Parameters.AddWithValue("@city", city);
updateCommand.Parameters.AddWithValue("@stateCode", state);
updateCommand.Parameters.AddWithValue("@zip", zipCode);
updateCommand.Parameters.AddWithValue("@phone", phone);
updateCommand.Parameters.AddWithValue("@ssn", ssn);
updateCommand.Parameters.AddWithValue("@dob", dob.Date);
updateCommand.ExecuteNonQuery();
}
}
}
