/// <summary>
/// Retrieve all the diagnoses information of a specific patient
/// </summary>
public List <PatientDiagnosis> RetrievePatientDiagnoses(string patientNRIC, string therapistNRIC)
{
List <PatientDiagnosis> result = new List <PatientDiagnosis>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.name_first, a.name_last,
pd.diagnosis_code, pd.start, pd.end,
d.diagnosis_description_short, d.category_title
FROM patient_diagnosis pd
INNER JOIN diagnosis d ON pd.diagnosis_code = d.diagnosis_Code
INNER JOIN account a ON a.nric = pd.therapist_nric
INNER JOIN record_type_permission rtp ON rtp.patient_nric = pd.patient_nric
WHERE rtp.patient_nric = @patientNRIC AND rtp.therapist_nric = @therapistNRIC AND rtp.approved_time IS NOT NULL
ORDER BY pd.end DESC, pd.start DESC;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"])
};
Diagnosis diagnosis = new Diagnosis
{
code = Convert.ToString(reader["diagnosis_code"]),
descriptionShort = Convert.ToString(reader["diagnosis_description_short"]),
categoryTitle = Convert.ToString(reader["category_title"])
};
PatientDiagnosis patientDiagnosis = new PatientDiagnosis
{
therapist = therapist,
diagnosis = diagnosis,
start = Convert.ToDateTime(reader["start"]),
};
patientDiagnosis.end = reader["end"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["end"]);
result.Add(patientDiagnosis);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve all of therapist's existing patients
/// </summary>
public List <Entity.Therapist> RetrieveCurrentTherapistsFineGrain(string term, long recordID, string patientNRIC)
{
List <Entity.Therapist> result = new List <Entity.Therapist>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric, a.name_first, a.name_last,
at.department, at.job_title,
rtp.permission_unapproved, rtp.request_time, rtp.permission_approved, rtp.approved_time, rtp.is_emergency,
rp.status as record_permission_status
FROM record_type_permission rtp
INNER JOIN account a ON rtp.therapist_nric = a.nric
INNER JOIN account_therapist at ON rtp.therapist_nric = at.nric
LEFT JOIN record_permission rp ON rp.therapist_nric = rtp.therapist_nric AND rp.record_id = @recordID
WHERE rtp.patient_nric = @patientNRIC AND (a.name_first LIKE @term OR a.name_last LIKE @term)
ORDER BY rtp.create_time DESC;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
cmd.Parameters.AddWithValue("@term", "%" + term + "%");
cmd.Parameters.AddWithValue("@recordID", recordID);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
nric = Convert.ToString(reader["nric"]),
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"]),
therapistDepartment = Convert.ToString(reader["department"]),
therapistJobTitle = Convert.ToString(reader["job_title"]),
permissionUnapproved = Convert.ToInt16(reader["permission_unapproved"]),
permissionApproved = Convert.ToInt16(reader["permission_approved"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"])
};
therapist.recordPermissionStatus = reader["record_permission_status"] == DBNull.Value ? null :
(short?)Convert.ToInt16(reader["record_permission_status"]);
therapist.requestTime = reader["request_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["request_time"]);
therapist.approvedTime = reader["approved_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["approved_time"]);
result.Add(therapist);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve all the diagnoses attributed to a specific record of a specific patient
/// </summary>
public List <RecordDiagnosis> RetrieveRecordDiagnoses(long recordID, string patientNRIC, string therapistNRIC)
{
List <RecordDiagnosis> result = new List <RecordDiagnosis>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT d.diagnosis_code, d.diagnosis_description_short, d.category_title,
a.name_first, a.name_last
FROM record_diagnosis rd
INNER JOIN record r ON r.id = rd.record_id
INNER JOIN account_patient ap ON ap.nric = r.patient_nric
INNER JOIN diagnosis d ON rd.diagnosis_code = d.diagnosis_Code
INNER JOIN account a ON a.nric = rd.creator_nric
INNER JOIN record_type_permission rtp ON rtp.patient_nric = ap.nric
WHERE rtp.patient_nric = @patientNRIC AND rtp.therapist_nric = @therapistNRIC AND r.id = @recordID
ORDER BY rd.create_time DESC;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
cmd.Parameters.AddWithValue("@recordID", recordID);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"])
};
Diagnosis diagnosis = new Diagnosis
{
code = Convert.ToString(reader["diagnosis_code"]),
descriptionShort = Convert.ToString(reader["diagnosis_description_short"]),
categoryTitle = Convert.ToString(reader["category_title"])
};
RecordDiagnosis recordDiagnosis = new RecordDiagnosis
{
therapist = therapist,
diagnosis = diagnosis,
};
result.Add(recordDiagnosis);
}
}
}
}
return(result);
}
public Entity.Therapist GetTherapistPermission(string therapistNRIC)
{
if (AccountBLL.IsPatient())
{
Entity.Therapist result = patientDAL.RetrieveTherapistPermission(therapistNRIC, AccountBLL.GetNRIC());
logPermissionBLL.LogEvent(AccountBLL.GetNRIC(), "View Therapist Permissions", "Action on: " + therapistNRIC + ".");
return(result);
}
return(null);
}
/// <summary>
/// Retrieve a specific patient's permissions
/// </summary>
public Entity.Therapist RetrieveTherapistPermission(string therapistNRIC, string patientNRIC)
{
Entity.Therapist result = new Entity.Therapist();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT rtp.therapist_nric, rtp.permission_unapproved,
rtp.request_time, rtp.permission_approved, rtp.approved_time,
a.name_first, a.name_last
FROM record_type_permission rtp
INNER JOIN account a ON rtp.therapist_nric = a.nric
WHERE rtp.therapist_nric = @therapistNRIC AND rtp.patient_nric = @patientNRIC;";
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
nric = Convert.ToString(reader["therapist_nric"]),
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"]),
permissionUnapproved = Convert.ToInt16(reader["permission_unapproved"]),
permissionApproved = Convert.ToInt16(reader["permission_approved"])
};
therapist.requestTime = reader["request_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["request_time"]);
therapist.approvedTime = reader["approved_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["approved_time"]);
result = therapist;
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Therapists with access to specific record
/// </summary>
public List <Entity.Therapist> RetrievePermissionsDisallow(int recordID, string term, string patientNRIC)
{
List <Entity.Therapist> result = new List <Entity.Therapist>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric, a.name_first, a.name_last, at.job_title, at.department
FROM record_permission rp
INNER JOIN account_therapist at ON at.nric = rp.therapist_nric
INNER JOIN account a ON a.nric = at.nric
INNER JOIN record r ON r.id = rp.record_id
WHERE rp.record_id = @recordID AND r.patient_nric = @patientNRIC AND
a.status > 0 AND at.status > 0 AND (a.name_first LIKE @term OR a.name_last LIKE @term)
ORDER BY rp.create_time DESC;";
cmd.Parameters.AddWithValue("@term", "%" + term + "%");
cmd.Parameters.AddWithValue("@recordID", recordID);
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
nric = Convert.ToString(reader["nric"]),
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"]),
therapistJobTitle = Convert.ToString(reader["job_title"]),
therapistDepartment = Convert.ToString(reader["department"]),
};
result.Add(therapist);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve all therapists except current
/// </summary>
public List <Entity.Therapist> RetrieveTherapists(string term, string nric)
{
List <Entity.Therapist> result = new List <Entity.Therapist>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric, a.name_first, a.name_last, at.job_title, at.department
FROM account a
INNER JOIN account_therapist at ON a.nric = at.nric
WHERE a.nric != @nric AND at.status = 1 AND (a.name_first LIKE @term OR a.name_last LIKE @term)
ORDER BY a.name_last ASC, a.name_first ASC
LIMIT 25;";
cmd.Parameters.AddWithValue("@term", "%" + term + "%");
cmd.Parameters.AddWithValue("@nric", nric);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
nric = Convert.ToString(reader["nric"]),
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"]),
therapistJobTitle = Convert.ToString(reader["job_title"]),
therapistDepartment = Convert.ToString(reader["department"])
};
result.Add(therapist);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve all of therapist's existing patients
/// </summary>
public Note RetrieveNote(long id, string therapistNRIC)
{
Note result = new Note();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT mn.id, mn.title, mn.content, mn.create_time,
ac.name_first as creator_name_first, ac.name_last as creator_name_last, ac.nric as creator_nric,
at.name_first as therapist_name_first, at.name_last as therapist_name_last,
ap.nric as patient_nric,
rtp.permission_unapproved, rtp.request_time, rtp.permission_approved, rtp.approved_time
FROM medical_note mn
INNER JOIN account ac ON mn.creator_nric = ac.nric
INNER JOIN account at ON mn.therapist_nric = at.nric
INNER JOIN account ap ON mn.patient_nric = ap.nric
LEFT JOIN record_type_permission rtp ON rtp.patient_nric = ap.nric
WHERE mn.therapist_nric = @therapistNRIC AND mn.id = @id
GROUP BY mn.id;";
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
cmd.Parameters.AddWithValue("@id", id);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Entity.Therapist therapist = new Entity.Therapist
{
firstName = Convert.ToString(reader["therapist_name_first"]),
lastName = Convert.ToString(reader["therapist_name_last"])
};
Entity.Therapist creator = new Entity.Therapist
{
nric = Convert.ToString(reader["creator_nric"]),
firstName = Convert.ToString(reader["creator_name_first"]),
lastName = Convert.ToString(reader["creator_name_last"])
};
Entity.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["patient_nric"]),
permissionUnapproved = Convert.ToInt16(reader["permission_unapproved"]),
permissionApproved = Convert.ToInt16(reader["permission_approved"])
};
patient.requestTime = reader["request_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["request_time"]);
patient.approvedTime = reader["approved_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["approved_time"]);
Note note = new Note
{
id = Convert.ToInt64(reader["id"]),
title = Convert.ToString(reader["title"]),
content = Convert.ToString(reader["content"]),
createTime = Convert.ToDateTime(reader["create_time"]),
therapist = therapist,
creator = creator,
patient = patient
};
result = note;
}
}
}
}
return(result);
}
