/// <summary>
/// Gets a list of all nurses in the WestGA-EMR db
/// </summary>
/// <returns>List of all Nurses</returns>
public static List <Nurse> GetNurses()
{
List <Nurse> nurses = new List <Nurse>();
String selectStatement = @"SELECT id, personID, active
FROM Nurse";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordPersonID = reader.GetOrdinal("personID");
int ordActive = reader.GetOrdinal("active");
while (reader.Read())
{
nurses.Add(new Nurse(reader.GetInt32(ordID),
reader.GetInt32(ordPersonID), reader.GetBoolean(ordActive)));
}
}
}
}
return(nurses);
}
/// <summary>
/// Gets a list of all clinical admins in the WestGA-EMR db
/// </summary>
/// <returns>List of all Clinical_Administrators</returns>
public static List <Clinical_Administrator> GetClinicalAdministrators()
{
List <Clinical_Administrator> admins = new List <Clinical_Administrator>();
String selectStatement = @"SELECT id, personID, active
FROM Clinical_Administrator";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordPersonID = reader.GetOrdinal("personID");
int ordActive = reader.GetOrdinal("active");
while (reader.Read())
{
admins.Add(new Clinical_Administrator(reader.GetInt32(ordID),
reader.GetInt32(ordPersonID), reader.GetBoolean(ordActive)));
}
}
}
}
return(admins);
}
/// <summary>
/// Updates an Appointment.
/// Does NOT verify if Appointment SHOULD be updated. Do not use without checking.
/// </summary>
/// <param name="appointment">Appointment to update</param>
/// <returns>Whether or not the appointment was updated</returns>
public static bool UpdateAppointment(Appointment appointment)
{
int retValue;
String updateStatement = @"UPDATE Appointment
SET patientID = @patientID, doctorID = @doctorID,
appointmentDateTime = @appointmentDateTime,
reasonForVisit = @reasonForVisit
WHERE id = @id" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(updateStatement, connection))
{
command.Parameters.AddWithValue("@id", appointment.ID);
command.Parameters.AddWithValue("@patientID", appointment.PatientID);
command.Parameters.AddWithValue("@doctorID", appointment.DoctorID);
command.Parameters.AddWithValue("@appointmentDateTime", appointment.AppointmentDateTime);
command.Parameters.AddWithValue("@reasonForVisit", appointment.ReasonForVisit);
retValue = command.ExecuteNonQuery();
}
}
if (retValue < 1)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Updates a Nurse in the db
/// </summary>
/// <param name="nurse">Nurse to update</param>
/// <returns>Whether or not the update succeeded</returns>
public static bool UpdateNurse(Nurse nurse)
{
int rowsUpdated;
String updateStatement = @"UPDATE Nurse
SET active = @active, personID = @personID
WHERE id = @nurseID" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(updateStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@nurseID", nurse.ID);
command.Parameters.AddWithValue("@personID", nurse.PersonID);
command.Parameters.AddWithValue("@active", nurse.Active);
rowsUpdated = command.ExecuteNonQuery();
}
}
if (rowsUpdated < 1)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Gets a list of all lab orders in the db
/// </summary>
/// <returns>List of all Lab_Orders in db</returns>
public static List <Lab_Order> GetLab_Orders()
{
List <Lab_Order> orders = new List <Lab_Order>();
String selectStatement = @"SELECT id, visitID, dateOrdered
FROM Lab_Order";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordVisitID = reader.GetOrdinal("visitID");
int ordDateOrdered = reader.GetOrdinal("dateOrdered");
while (reader.Read())
{
orders.Add(new Lab_Order(reader.GetInt32(ordID),
reader.GetInt32(ordVisitID), reader.GetDateTime(ordDateOrdered)));
}
}
}
}
return(orders);
}
/// <summary>
/// Gets a list of all appointments in the WestGA-EMR db
/// </summary>
/// <returns>List of all Appointments</returns>
public static List <Appointment> GetAppointments()
{
List <Appointment> appointments = new List <Appointment>();
String selectStatement = @"SELECT id, patientID, doctorID, appointmentDateTime, reasonForVisit
FROM Appointment";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordPatientID = reader.GetOrdinal("patientID");
int ordDoctorID = reader.GetOrdinal("doctorID");
int ordApptDateTime = reader.GetOrdinal("appointmentDateTime");
int ordReason = reader.GetOrdinal("reasonForVisit");
while (reader.Read())
{
appointments.Add(new Appointment(reader.GetInt32(ordID),
reader.GetInt32(ordPatientID), reader.GetInt32(ordDoctorID),
reader.GetDateTime(ordApptDateTime), reader.GetString(ordReason)));
}
}
}
}
return(appointments);
}
/// <summary>
/// Gets a list of all doctor_has_specialty relations in the db
/// </summary>
/// <returns>List of all Doctor_has_Specialties relations</returns>
public static List <Doctor_has_Specialties> GetDoctor_has_Specialties()
{
List <Doctor_has_Specialties> relations = new List <Doctor_has_Specialties>();
String selectStatement = @"SELECT doctorID, specialtyName
FROM Doctor_has_Specialties";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordDoctorID = reader.GetOrdinal("doctorID");
int ordSpecialtyName = reader.GetOrdinal("specialtyName");
while (reader.Read())
{
relations.Add(new Doctor_has_Specialties(reader.GetInt32(ordDoctorID),
reader.GetString(ordSpecialtyName)));
}
}
}
}
return(relations);
}
/// <summary>
/// Gets a list of all addresses in the db
/// </summary>
/// <returns>List of all Addresses in db</returns>
public static List <Address> GetAddresses()
{
List <Address> addresses = new List <Address>();
String selectStatement = @"SELECT id, street, city, state, zip FROM Address";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordStreet = reader.GetOrdinal("street");
int ordCity = reader.GetOrdinal("city");
int ordState = reader.GetOrdinal("state");
int ordZip = reader.GetOrdinal("zip");
while (reader.Read())
{
addresses.Add(new Address(reader.GetInt32(ordID),
reader.GetString(ordStreet), reader.GetString(ordCity),
reader.GetString(ordState), reader.GetString(ordZip)));
}
}
}
}
return(addresses);
}
/// <summary>
/// Gets an address by it's id
/// </summary>
/// <param name="id">ID of the Address</param>
/// <returns>The Address</returns>
public static Address GetAddress(int id)
{
Address address = null;
String selectStatement = @"SELECT id, street, city, state, zip FROM Address WHERE id = @id";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordStreet = reader.GetOrdinal("street");
int ordCity = reader.GetOrdinal("city");
int ordState = reader.GetOrdinal("state");
int ordZip = reader.GetOrdinal("zip");
while (reader.Read())
{
address = new Address(reader.GetInt32(ordID),
reader.GetString(ordStreet), reader.GetString(ordCity),
reader.GetString(ordState), reader.GetString(ordZip));
}
}
}
}
return(address);
}
/// <summary>
/// Updates an Address in the db
/// </summary>
/// <param name="address">The Address to update</param>
/// <returns>Whether or not the update succeeded</returns>
public static bool UpdateAddress(Address address)
{
int rowsUpdated;
String updateStatement = @"UPDATE Address
SET street = @street, city = @city, state = @state, zip = @zip
WHERE id = @addressID" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(updateStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@addressID", address.ID);
command.Parameters.AddWithValue("@street", address.Street);
command.Parameters.AddWithValue("@city", address.City);
command.Parameters.AddWithValue("@state", address.State);
command.Parameters.AddWithValue("@zip", address.Zip);
rowsUpdated = command.ExecuteNonQuery();
}
}
if (rowsUpdated < 1)
{
return(false);
}
else
{
return(true);
}
}
public List <UserDTO> GetActiveDoctors()
{
var doctors = new List <UserDTO>();
string selectUserStatement = @"
SELECT CONCAT(P.firstName,' ', P.lastName) AS FullName, D.id as DoctorID
FROM Person P
inner join doctor D on P.id = D.personID
WHERE D.active = 1";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
UserDTO doctor = new UserDTO();
doctor.FullName = reader["FullName"].ToString();
doctor.DoctorId = (int)reader["DoctorID"];
doctors.Add(doctor);
}
}
}
}
return(doctors);
}
public UserDTO GetDoctorById(int doctorId)
{
var doctor = new UserDTO();
string selectUserStatement = @"
SELECT CONCAT(P.firstName,' ', P.lastName) AS FullName, D.id as DoctorID
FROM Person P
inner join doctor D on P.id = D.personID
WHERE D.active = 1 and D.id = @DoctorID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
selectCommand.Parameters.AddWithValue("@DoctorID", doctorId);
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
doctor.FullName = reader["FullName"].ToString();
doctor.DoctorId = (int)reader["DoctorID"];
}
}
}
}
return(doctor);
}
/// <summary>
/// Creates a new Appointment for a patient.
/// Does NOT check to avoid double booking. Do not use without first checking.
/// </summary>
/// <param name="appointment">The new Appointment to insert into the db</param>
/// <returns>Whether or not the appointment was created</returns>
public static bool CreateAppointment(Appointment appointment)
{
int retValue;
String insertStatement = @"INSERT INTO Appointment (patientID, doctorID, appointmentDateTime, reasonForVisit)
VALUES (@patientID, @doctorID, @appointmentDateTime, @reasonForVisit)" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
command.Parameters.AddWithValue("@patientID", appointment.PatientID);
command.Parameters.AddWithValue("@doctorID", appointment.DoctorID);
command.Parameters.AddWithValue("@appointmentDateTime", appointment.AppointmentDateTime);
command.Parameters.AddWithValue("@reasonForVisit", appointment.ReasonForVisit);
retValue = command.ExecuteNonQuery();
}
}
if (retValue < 1)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Updates a Person in the db
/// </summary>
/// <param name="person">Person to update</param>
/// <returns>Whether or not the update succeeded</returns>
public static bool UpdatePerson(Person person)
{
int rowsUpdated;
String updateStatement = @"UPDATE Person
SET username = @username, password = @password, firstName = @firstName, lastName = @lastName, dateOfBirth = @dateOfBirth,
ssn = @ssn, gender = @gender, addressID = @addressID, contactPhone = @contactPhone
WHERE id = @personID" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(updateStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@personID", person.ID);
if (string.IsNullOrWhiteSpace(person.Username))
{
command.Parameters.AddWithValue("@username", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@username", person.Username);
}
if (string.IsNullOrWhiteSpace(person.Password))
{
command.Parameters.AddWithValue("@password", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@password", Helpers.PasswordHashSHA512.GenerateSHA512String(person.Password));
}
if (string.IsNullOrWhiteSpace(person.SSN))
{
command.Parameters.AddWithValue("@ssn", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@ssn", person.SSN);
}
command.Parameters.AddWithValue("@firstName", person.FirstName);
command.Parameters.AddWithValue("@lastName", person.LastName);
command.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth);
command.Parameters.AddWithValue("@gender", person.Gender);
command.Parameters.AddWithValue("@addressID", person.AddressID);
command.Parameters.AddWithValue("@contactPhone", person.ContactPhone);
rowsUpdated = command.ExecuteNonQuery();
}
}
if (rowsUpdated < 1)
{
return(false);
}
else
{
return(true);
}
}
///
/// </summary>
/// <param name="visitId"></param>
/// <returns></returns>
public static List <LabOrderTestDTO> GetVisitTests(int visitId)
{
List <LabOrderTestDTO> labTests = new List <LabOrderTestDTO>();
string selectStatement = @"select l.labOrderID, l.labTestCode, l.testPerformed, l.results,
o.visitID, o.dateOrdered,
t.name as testName ,
l.isNormal as isNormal
from Lab_Orders_have_Lab_Tests l
inner join Lab_Order o on l.labOrderID = o.id
inner join Lab_Test t on l.labTestCode = t.code
where o.visitID = @VisitID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@VisitID", visitId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
LabOrderTestDTO labTest = new LabOrderTestDTO();
labTest.OrderId = Convert.ToInt32(reader["labOrderID"].ToString());
labTest.OrderedDate = (DateTime)reader["dateOrdered"];
labTest.TestCode = (int)reader["labTestCode"];
labTest.TestName = reader["testName"].ToString();
labTest.TestResult = reader["results"].ToString();
if (!reader.IsDBNull(reader.GetOrdinal("isNormal")))
{
labTest.IsNormal = (bool)reader["isNormal"];
}
if (labTest.IsNormal.HasValue)
{
if (labTest.IsNormal.Value)
{
labTest.TestResultStatus = "Normal";
}
else
{
labTest.TestResultStatus = "Abnormal";
}
}
if (!reader.IsDBNull(reader.GetOrdinal("testPerformed")))
{
labTest.TestDate = (DateTime)reader["testPerformed"];
}
labTest.VisitId = Convert.ToInt32(reader["visitID"].ToString());
labTests.Add(labTest);
}
}
}
}
return(labTests);
}
/// <summary>
/// Gets a list of all the Visits of a particular Patient
/// </summary>
/// <param name="patient">Patient whose Visits are desired</param>
/// <returns>List of Patient's Visits</returns>
public static List <Visit> GetVisitsByPatient(Patient patient)
{
List <Visit> visits = new List <Visit>();
String selectStatement = @"SELECT Visit.id, appointmentID, nurseID, visitDateTime,
initialDiagnosis, weight, systolicPressure, diastolicPressure,
bodyTemperature, pulse, symptoms, finalDiagnosis
FROM Visit
JOIN Appointment ON Visit.appointmentID = Appointment.ID
JOIN Patient ON Appointment.patientID = Patient.id
WHERE Patient.id = @patientID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@patientID", patient.ID);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordApptID = reader.GetOrdinal("appointmentID");
int ordNurseID = reader.GetOrdinal("nurseID");
int ordVisitDateTime = reader.GetOrdinal("visitDateTime");
int ordInitialDiagnosis = reader.GetOrdinal("initialDiagnosis");
int ordWeight = reader.GetOrdinal("weight");
int ordSystolicPressure = reader.GetOrdinal("systolicPressure");
int ordDiastolicPressure = reader.GetOrdinal("diastolicPressure");
int ordBodyTemperature = reader.GetOrdinal("bodyTemperature");
int ordPulse = reader.GetOrdinal("pulse");
int ordSymptoms = reader.GetOrdinal("symptoms");
int ordFinalDiagnosis = reader.GetOrdinal("finalDiagnosis");
while (reader.Read())
{
string symptoms = null;
if (!reader.IsDBNull(ordSymptoms))
{
symptoms = reader.GetString(ordSymptoms);
}
string final = null;
if (!reader.IsDBNull(ordFinalDiagnosis))
{
final = reader.GetString(ordFinalDiagnosis);
}
visits.Add(new Visit(reader.GetInt32(ordID),
reader.GetInt32(ordApptID), reader.GetInt32(ordNurseID),
reader.GetDateTime(ordVisitDateTime), reader.GetString(ordInitialDiagnosis),
reader.GetDecimal(ordWeight), reader.GetInt32(ordSystolicPressure),
reader.GetInt32(ordDiastolicPressure), reader.GetDecimal(ordBodyTemperature),
reader.GetInt32(ordPulse), symptoms, final));
}
}
}
}
return(visits);
}
/// <summary>
/// Method to Sign In User
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns>The current user</returns>
public UserDTO SignIn(string username, string password)
{
string hash = Helpers.PasswordHashSHA512.GenerateSHA512String(password);
UserDTO currentUser = new UserDTO();
string selectUserStatement = @"
SELECT P.id as personId, username,firstName,
lastName, dateOfBirth, ssn, gender,
addressID, contactPhone, N.id as NurseId, C.id as AdminId,
N.active as IsActiveNurse, C.active as IsActiveAdmin,
street, city, state, zip
FROM Person P
FULL OUTER JOIN Nurse N ON P.id = N.personID
FULL OUTER JOIN Clinical_Administrator C ON P.id = C.personID
FULL OUTER JOIN Address A ON P.addressID = A.id
WHERE P.username = @Username and P.password = @Password";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
selectCommand.Parameters.AddWithValue("@Username", username);
selectCommand.Parameters.AddWithValue("@Password", hash);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
currentUser = new UserDTO();
currentUser.Id = (int)reader["personId"];
currentUser.Username = reader["username"].ToString();
currentUser.FirstName = reader["firstName"].ToString();
currentUser.LastName = reader["lastName"].ToString();
currentUser.SSN = reader["ssn"].ToString();
currentUser.Gender = reader["gender"].ToString();
currentUser.ContactPhone = reader["contactPhone"].ToString();
currentUser.Street = reader["street"].ToString();
currentUser.State = reader["state"].ToString();
currentUser.City = reader["city"].ToString();
currentUser.Zip = reader["zip"].ToString();
currentUser.AddressId = reader["addressID"] != DBNull.Value ? (int)reader["addressID"] : 0;
currentUser.AdminId = reader["AdminId"] != DBNull.Value ? (int)reader["AdminId"] : 0;
currentUser.NurseId = reader["NurseId"] != DBNull.Value ? (int)reader["NurseId"] : 0;
currentUser.DateOfBirth = reader["dateOfBirth"] != DBNull.Value ? (DateTime)reader["dateOfBirth"] : (DateTime?)null;
currentUser.IsActiveNurse = reader["IsActiveNurse"] != DBNull.Value ? (bool)reader["IsActiveNurse"] : false;
currentUser.IsActiveAdmin = reader["IsActiveAdmin"] != DBNull.Value ? (bool)reader["IsActiveAdmin"] : false;
}
else
{
throw new Exception("Incorrect username or password");
}
}
}
}
return(currentUser);
}
/// <summary>
/// Gets a List of AppointmentDTO within the specified date range
/// </summary>
/// <param name="start">Starting date of the range</param>
/// <param name="end">Ending date of the range</param>
/// <returns>List of AppointmentDTO within said date range</returns>
public static List <AppointmentDTO> GetAppointmentInDateRange(DateTime start, DateTime end)
{
List <AppointmentDTO> appointments = new List <AppointmentDTO>();
string query = @"SELECT Appointment.id as appointmentID, [patientID], [appointmentDateTime], [reasonForVisit]
,CONCAT(Person.firstName,' ', Person.lastName) as doctorName, [doctorID]
INTO aptTemp
FROM Appointment
inner join Doctor on Appointment.doctorID = Doctor.id
inner join Person on Doctor.personID = Person.id
WHERE Appointment.appointmentDateTime between @StartDate and @EndDate
SELECT appointmentDateTime, patientID, doctorID, reasonForVisit, doctorName, appointmentID, CONCAT(Person.firstName,' ', Person.lastName) as patientName
from aptTemp
inner join Patient on aptTemp.patientID = Patient.id
inner join Person on Patient.personID = Person.id
order by aptTemp.appointmentDateTime
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aptTemp]') AND type in (N'U'))
DROP TABLE [dbo].[aptTemp]";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@StartDate", start.Date);
command.Parameters.AddWithValue("@EndDate", end.Date);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordAptID = reader.GetOrdinal("appointmentID");
int ordPatientName = reader.GetOrdinal("patientName");
int ordDoctorName = reader.GetOrdinal("doctorName");
int ordApptDateTime = reader.GetOrdinal("appointmentDateTime");
int ordReason = reader.GetOrdinal("reasonForVisit");
int ordDoctorId = reader.GetOrdinal("doctorID");
int ordPatientId = reader.GetOrdinal("patientID");
while (reader.Read())
{
var appointment = new AppointmentDTO();
appointment.AppointmentID = Convert.ToInt32(reader.GetInt64(ordAptID));
appointment.AppointmentDateTime = reader.GetDateTime(ordApptDateTime);
appointment.DoctorName = reader.GetString(ordDoctorName);
appointment.PatientName = reader.GetString(ordPatientName);
appointment.ReasonForVisit = reader.GetString(ordReason);
appointment.DoctorID = reader.GetInt32(ordDoctorId);
appointment.PatientID = reader.GetInt32(ordPatientId);
appointments.Add(appointment);
}
}
}
}
return(appointments);
}
/// <summary>
/// Update a Visits to the data source.
/// </summary>
/// <param name="visit"></param>
public static bool UpdateVisit(Visit visit)
{
bool isUpdateSuccesful = false;
string selectStatement = " Update Visit set initialDiagnosis = @initialDiagnosis , " +
"weight = @weight, systolicPressure = @systolicPressure, diastolicPressure = @diastolicPressure , " +
"bodyTemperature = @bodyTemperature, pulse = @pulse , symptoms = @symptoms, finalDiagnosis =@finalDiagnosis " +
" where id = @visitID ";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.Add("@visitID", SqlDbType.Int);
selectCommand.Parameters["@visitID"].Value = visit.ID;
selectCommand.Parameters.Add("@initialDiagnosis", SqlDbType.VarChar);
selectCommand.Parameters["@initialDiagnosis"].Value = visit.InitialDiagnosis;
selectCommand.Parameters.Add("@weight", SqlDbType.Decimal);
selectCommand.Parameters["@weight"].Value = visit.Weight;
selectCommand.Parameters.Add("@systolicPressure", SqlDbType.Int);
selectCommand.Parameters["@systolicPressure"].Value = visit.SystolicPressure;
selectCommand.Parameters.Add("@diastolicPressure", SqlDbType.Int);
selectCommand.Parameters["@diastolicPressure"].Value = visit.DiastolicPressure;
selectCommand.Parameters.Add("@bodyTemperature", SqlDbType.Decimal);
selectCommand.Parameters["@bodyTemperature"].Value = visit.BodyTemperature;
selectCommand.Parameters.Add("@pulse", SqlDbType.Int);
selectCommand.Parameters["@pulse"].Value = visit.Pulse;
selectCommand.Parameters.Add("@symptoms", SqlDbType.VarChar);
selectCommand.Parameters["@symptoms"].Value = visit.Symptoms;
selectCommand.Parameters.Add("@finalDiagnosis", SqlDbType.VarChar);
selectCommand.Parameters["@finalDiagnosis"].Value = visit.FinalDiagnosis;
int rowsAffected = selectCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
isUpdateSuccesful = true;
}
}
return(isUpdateSuccesful);
}
}
/// <summary>
/// Inserts a new Person into the db
/// </summary>
/// <param name="person">Person to insert</param>
/// <returns>The id of the newly inserted Person, or null if the insert failed</returns>
public static int?InsertPerson(Person person)
{
int? id = null;
String insertStatement = @"INSERT INTO Person(username, password, firstName, lastName, dateOfBirth, ssn, gender, addressID, contactPhone)
OUTPUT inserted.id
VALUES (@username, @password, @firstName, @lastName, @dateOfBirth, @ssn, @gender, @addressID, @contactPhone)" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
if (string.IsNullOrWhiteSpace(person.Username))
{
command.Parameters.AddWithValue("@username", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@username", person.Username);
}
if (string.IsNullOrWhiteSpace(person.Password))
{
command.Parameters.AddWithValue("@password", DBNull.Value);
}
else
{
string hash = PasswordHashSHA512.GenerateSHA512String(person.Password);
command.Parameters.AddWithValue("@password", hash);
}
if (string.IsNullOrWhiteSpace(person.SSN))
{
command.Parameters.AddWithValue("@ssn", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@ssn", person.SSN);
}
command.Parameters.AddWithValue("@firstName", person.FirstName);
command.Parameters.AddWithValue("@lastName", person.LastName);
command.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth);
command.Parameters.AddWithValue("@gender", person.Gender);
command.Parameters.AddWithValue("@addressID", person.AddressID);
command.Parameters.AddWithValue("@contactPhone", person.ContactPhone);
id = (int?)command.ExecuteScalar();
}
}
return(id);
}
/// <summary>
/// Gets all nurse
/// </summary>
/// <returns></returns>
public static List <UserDTO> GetAllNurse(UserDTO currentUser)
{
var nurses = new List <UserDTO>();
string selectUserStatement = @"
SELECT P.id as personId, username,firstName, password,
lastName, dateOfBirth, ssn, gender,
addressID, contactPhone, n.id as nurseId, n.active,
street, city, state, zip
FROM Person P
INNER JOIN Nurse n ON P.id = n.personID
INNER JOIN Address A ON P.addressID = A.id
WHERE P.id <> @CurrentUserId
ORDER BY lastName ";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
selectCommand.Parameters.AddWithValue("@CurrentUserId", currentUser.Id);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
UserDTO nurse = new UserDTO();
nurse.Id = (int)reader["personId"];
nurse.Username = reader["username"].ToString();
nurse.FirstName = reader["firstName"].ToString();
nurse.LastName = reader["lastName"].ToString();
nurse.SSN = reader["ssn"].ToString();
nurse.Gender = reader["gender"].ToString();
nurse.ContactPhone = reader["contactPhone"].ToString();
nurse.Street = reader["street"].ToString();
nurse.State = reader["state"].ToString();
nurse.City = reader["city"].ToString();
nurse.Zip = reader["zip"].ToString();
nurse.AddressId = reader["addressID"] != DBNull.Value ? (int)reader["addressID"] : 0;
nurse.NurseId = reader["nurseId"] != DBNull.Value ? (int)reader["nurseId"] : 0;
nurse.DateOfBirth = reader["dateOfBirth"] != DBNull.Value ? (DateTime)reader["dateOfBirth"] : (DateTime?)null;
nurse.IsActiveNurse = (bool)reader["active"];
nurse.Password = reader["password"].ToString();
nurses.Add(nurse);
}
}
}
}
return(nurses);
}
/// <summary>
/// Gets a list of all active patients in the db according to the given parameters.
/// Returns patient's username and password as null.
/// </summary>
/// <param name="firstName">Patient's first name</param>
/// <param name="lastName">Patient's last name</param>
/// <returns>List of all matching patients as Persons</returns>
public static List <Person> GetActivePatientsByFirstAndLastName(string firstName, string lastName)
{
List <Person> patients = new List <Person>();
String selectStatement = @"SELECT p.id, null as username, null as password,
p.firstName, p.lastName, p.dateOfBirth, p.ssn,
p.gender, p.addressID, p.contactPhone
FROM Patient as pt
JOIN Person as p ON pt.personID = p.id
WHERE pt.active = 1
AND p.lastName = @lastName
AND p.firstName = @firstName" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordUsername = reader.GetOrdinal("username");
int ordPassword = reader.GetOrdinal("password");
int ordFirstName = reader.GetOrdinal("firstName");
int ordLastName = reader.GetOrdinal("lastName");
int ordDateOfBirth = reader.GetOrdinal("dateOfBirth");
int ordSSN = reader.GetOrdinal("ssn");
int ordGender = reader.GetOrdinal("gender");
int ordAddressID = reader.GetOrdinal("addressID");
int ordContactPhone = reader.GetOrdinal("contactPhone");
while (reader.Read())
{
string social = null;
if (!reader.IsDBNull(ordSSN))
{
social = reader.GetString(ordSSN);
}
patients.Add(new Person(reader.GetInt32(ordID), null, null,
reader.GetString(ordFirstName), reader.GetString(ordLastName),
reader.GetDateTime(ordDateOfBirth), social, reader.GetString(ordGender),
reader.GetInt32(ordAddressID), reader.GetString(ordContactPhone)));
}
}
}
}
return(patients);
}
/// <summary>
/// Deletes the given Person from the db
/// </summary>
/// <param name="person">Person to delete</param>
/// <returns>Whether or not the Person was deleted</returns>
public static bool DeletePerson(Person person)
{
bool wasDeleted = false;
String deleteStatement = @"DELETE FROM Person WHERE Person.id = @personID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(deleteStatement, connection))
{
command.Parameters.AddWithValue("@personID", person.ID);
wasDeleted = command.ExecuteNonQuery() > 0 ? true : false;
}
}
return(wasDeleted);
}
/// <summary>
/// Updates the given relation of Lab_Orders_have_Lab_Tests
/// </summary>
/// <param name="relation">The Lab_Orders_have_Lab_Tests to update</param>
/// <returns>Whether or not the update succeeded</returns>
public static bool UpdateLab_Orders_have_Lab_Tests(Lab_Orders_have_Lab_Tests relation)
{
int rowsUpdated;
String updateStatement = @"UPDATE Lab_Orders_have_Lab_Tests
SET testPerformed = @testPerformed, results = @results , IsNormal = @IsNormal
WHERE labOrderID = @labOrderID AND labTestCode = @labTestCode" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(updateStatement, connection))
{
connection.Open();
if (relation.LabOrderID == null)
{
command.Parameters.AddWithValue("@labOrderID", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@labOrderID", relation.LabOrderID);
}
if (relation.LabTestCode == null)
{
command.Parameters.AddWithValue("@labTestCode", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@labTestCode", relation.LabTestCode);
}
command.Parameters.AddWithValue("@testPerformed", relation.TestPerformed);
command.Parameters.AddWithValue("@results", relation.Results);
command.Parameters.AddWithValue("@IsNormal", relation.IsNormal);
rowsUpdated = command.ExecuteNonQuery();
}
}
if (rowsUpdated < 1)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Deletes the given Appointment from the db.
/// </summary>
/// <param name="appt">Appointment to delete from the db</param>
/// <returns>Whether or not the appointment was deleted</returns>
public static bool DeleteAppointment(Appointment appt)
{
bool wasDeleted = false;
String deleteStatement = @"DELETE FROM Appointment WHERE Appointment.id = @appointmentID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(deleteStatement, connection))
{
command.Parameters.AddWithValue("@appointmentID", appt.ID);
wasDeleted = command.ExecuteNonQuery() > 0 ? true : false;
}
}
return(wasDeleted);
}
/// <summary>
/// Gets a list of all of a patient's Appointments; past, present, and future
/// </summary>
/// <param name="patient">Patient whose Appointments you desire</param>
/// <returns>List of all Appointments for said patient</returns>
public static List <AppointmentDTO> GetPatientsAppointments(Patient patient)
{
List <AppointmentDTO> appointments = new List <AppointmentDTO>();
String selectStatement = @"SELECT CAST(Appointment.id AS INT) as aptID , patientID, doctorID , CONCAT(Person.firstName,' ', Person.lastName) as doctorName, appointmentDateTime, reasonForVisit
FROM Appointment
inner join Doctor
on Appointment.doctorID = Doctor.id
inner join Person
on Doctor.personID = Person.id
WHERE patientID = @patientID
order by appointmentDateTime";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
command.Parameters.AddWithValue("@patientID", patient.ID);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordAptID = reader.GetOrdinal("aptID");
int ordPatientID = reader.GetOrdinal("patientID");
int ordDoctorID = reader.GetOrdinal("doctorID");
int ordDoctorName = reader.GetOrdinal("doctorName");
int ordApptDateTime = reader.GetOrdinal("appointmentDateTime");
int ordReason = reader.GetOrdinal("reasonForVisit");
while (reader.Read())
{
AppointmentDTO appointmentDTO = new AppointmentDTO
{
AppointmentID = reader.GetInt32(ordAptID),
PatientID = reader.GetInt32(ordPatientID),
DoctorID = reader.GetInt32(ordDoctorID),
DoctorName = reader.GetString(ordDoctorName),
AppointmentDateTime = reader.GetDateTime(ordApptDateTime),
ReasonForVisit = reader.GetString(ordReason)
};
appointments.Add(appointmentDTO);
}
}
}
}
return(appointments);
}
/// <summary>
/// Gets a list of all persons in the db
/// </summary>
/// <returns>List of all Persons in db</returns>
public static List <Person> GetPersons()
{
List <Person> persons = new List <Person>();
String selectStatement = @"SELECT id, username, firstName, lastName, dateOfBirth, ssn, gender, addressID, contactPhone
FROM Person";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordUsername = reader.GetOrdinal("username");
int ordFirstName = reader.GetOrdinal("firstName");
int ordLastName = reader.GetOrdinal("lastName");
int ordDateOfBirth = reader.GetOrdinal("dateOfBirth");
int ordSSN = reader.GetOrdinal("ssn");
int ordGender = reader.GetOrdinal("gender");
int ordAddressID = reader.GetOrdinal("addressID");
int ordContactPhone = reader.GetOrdinal("contactPhone");
while (reader.Read())
{
string user = null;
if (!reader.IsDBNull(ordUsername))
{
user = reader.GetString(ordUsername);
}
string social = null;
if (!reader.IsDBNull(ordSSN))
{
social = reader.GetString(ordSSN);
}
persons.Add(new Person(reader.GetInt32(ordID), user, null,
reader.GetString(ordFirstName), reader.GetString(ordLastName),
reader.GetDateTime(ordDateOfBirth), social, reader.GetString(ordGender),
reader.GetInt32(ordAddressID), reader.GetString(ordContactPhone)));
}
}
}
}
return(persons);
}
/// <summary>
/// Returns the Person of the Doctor.
///
/// Returns password as null.
/// </summary>
/// <param name="patient">The Doctor</param>
/// <returns>Person of the Doctor</returns>
public static Person GetPersonByDoctorID(Doctor doctor)
{
Person person = null;
String selectStatement = @"SELECT Person.id, username, firstName, lastName, dateOfBirth, ssn, gender, addressID, contactPhone
FROM Person
JOIN Doctor on Person.id = Doctor.personID
WHERE Doctor.id = @doctorID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
command.Parameters.AddWithValue("@doctorID", doctor.ID);
connection.Open();
person = GetPerson(command);
}
}
return(person);
}
/// <summary>
/// Gets a list of all Lab_Orders_have_Lab_Tests relations in the db
/// </summary>
/// <returns>List of all said relations in db</returns>
public static List <Lab_Orders_have_Lab_Tests> GetLab_Orders_have_Lab_Tests()
{
List <Lab_Orders_have_Lab_Tests> relations = new List <Lab_Orders_have_Lab_Tests>();
String selectStatement = @"SELECT labOrderID, labTestCode, testPerformed, results, isNormal
FROM Lab_Orders_have_Lab_Tests";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("labOrderID");
int ordCode = reader.GetOrdinal("labTestCode");
int ordPerformed = reader.GetOrdinal("testPerformed");
int ordResults = reader.GetOrdinal("results");
int ordIsNormal = reader.GetOrdinal("isNormal");
while (reader.Read())
{
DateTime?testPerformed = null;
if (!reader.IsDBNull(ordPerformed))
{
testPerformed = reader.GetDateTime(ordPerformed);
}
string results = null;
if (!reader.IsDBNull(ordResults))
{
results = reader.GetString(ordResults);
}
bool?isNormal = null;
if (!reader.IsDBNull(ordIsNormal))
{
isNormal = reader.GetBoolean(ordIsNormal);
}
relations.Add(new Lab_Orders_have_Lab_Tests(reader.GetInt64(ordID),
reader.GetInt32(ordCode), testPerformed, results, isNormal));
}
}
}
}
return(relations);
}
/// <summary>
/// Inserts a new Lab_Order into the db
/// </summary>
/// <param name="order">The order to insert</param>
/// <returns>ID of the newly inserted Lab_order, or null if the insertion failed</returns>
public static int?InsertLab_Order(Lab_Order order)
{
int? id = null;
String insertStatement = @"INSERT INTO Lab_Order (visitID, dateOrdered)
OUTPUT inserted.id
VALUES (@visitID, @dateOrdered)" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
command.Parameters.AddWithValue("@visitID", order.VisitID);
command.Parameters.AddWithValue("@dateOrdered", order.DateOrdered);
id = Convert.ToInt32(command.ExecuteScalar());
}
}
return(id);
}