/// <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); }