/// <summary>
/// Checks to see if employee exists in database by matching username and password
/// </summary>
/// <param name="username">username as string</param>
/// <param name="password">password as string</param>
/// <returns>either nurse or admin object containing the personal information depending on their role</returns>
public Employee LoginEmployee(string username, string password)
{
string selectStatement = "SELECT u.id as employee_id, u.username, u.password, " +
"u.person_id, n.id as nurse_id, a.id as admin_id, phi.first_name, phi.last_name " +
"FROM users u " +
"LEFT JOIN nurse n ON u.person_id = n.person_id " +
"LEFT JOIN administrator a ON u.person_id = a.person_id " +
"JOIN person phi ON u.person_id = phi.id " +
"WHERE username = @username AND password = HASHBYTES('SHA2_256', @password)";
using (SqlConnection connection = ClinicDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@username", username);
selectCommand.Parameters.AddWithValue("@password", password);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
Employee employee;
if (reader.Read())
{
if (reader["nurse_id"] != DBNull.Value && reader["admin_id"] == DBNull.Value)
{
employee = NurseDAL.GetNurseByID((int)reader["nurse_id"]);
return(employee);
}
else if (reader["admin_id"] != DBNull.Value && reader["nurse_id"] == DBNull.Value)
{
employee = new Admin
{
EmployeeID = (int)reader["employee_id"],
UserName = reader["username"].ToString(),
Password = reader["password"].ToString(),
FirstName = reader["first_name"].ToString(),
LastName = reader["last_name"].ToString(),
AdminID = (int)reader["admin_id"],
};
return(employee);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
}
}
/// <summary>
/// Returns all visits in DB
/// </summary>
/// <returns>List of all visits</returns>
public List <Visit> GetAllVisits()
{
AppointmentDAL appointmentDAL = new AppointmentDAL();
List <Visit> visits = new List <Visit>();
string selectStatement = "SELECT * FROM visit;";
using (SqlConnection connection = ClinicDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Visit visit = new Visit
{
VisitId = (int)reader["id"],
DateTime = (DateTime)reader["visit_datetime"],
Weight = (decimal)reader["weight"],
BpSystolic = (int)reader["bp_systolic"],
BpDiastolic = (int)reader["bp_diastolic"],
BodyTemperature = (decimal)reader["body_temp"],
Pulse = (int)reader["pulse"],
Symptoms = reader["symptoms"].ToString(),
Info = reader["checkup_info"].ToString(),
Nurse = NurseDAL.GetNurseByID((int)reader["nurse_id"]),
Appointment = appointmentDAL.GetAppointmentByID((int)reader["appointment_id"]),
InitialDiagnosis = reader["initial_diagnosis"].ToString(),
FinalDiagnosis = reader["final_diagnosis"].ToString()
};
visits.Add(visit);
}
}
}
connection.Close();
}
return(visits);
}
/// <summary>
/// Returns a visit based on the appointment ID
/// </summary>
/// <param name="id">Appointment id</param>
/// <returns>Visit Object</returns>
public Visit GetVisitByAppointmentID(int id)
{
AppointmentDAL appointmentDAL = new AppointmentDAL();
Visit visit = new Visit();
string selectStatement = "SELECT * FROM visit WHERE appointment_id = @appointmentID;";
using (SqlConnection connection = ClinicDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
command.Parameters.AddWithValue("@appointmentID", id);
using (SqlDataReader reader = command.ExecuteReader())
{
visit.Appointment = appointmentDAL.GetAppointmentByID(id);
if (reader.Read())
{
visit.VisitId = (int)reader["id"];
visit.DateTime = (DateTime)reader["visit_datetime"];
visit.Weight = (decimal)reader["weight"];
visit.BpSystolic = (int)reader["bp_systolic"];
visit.BpDiastolic = (int)reader["bp_diastolic"];
visit.BodyTemperature = (decimal)reader["body_temp"];
visit.Pulse = (int)reader["pulse"];
visit.Symptoms = reader["symptoms"].ToString();
visit.Info = reader["checkup_info"].ToString();
visit.Nurse = NurseDAL.GetNurseByID((int)reader["nurse_id"]);
visit.InitialDiagnosis = reader["initial_diagnosis"].ToString();
visit.FinalDiagnosis = reader["final_diagnosis"].ToString();
}
}
}
connection.Close();
}
return(visit);
}