internal int AddPatient(Entity.Patient patient)
{
int patientId = 0;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_AddPatient";
SqlParameter param = new SqlParameter();
param.ParameterName = "@PatientName";
param.Value = patient.Name;
param.DbType = DbType.String;
command.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PatientGender";
param.Value = patient.Gender;
param.DbType = DbType.String;
command.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PatientAddress";
param.Value = patient.Address;
param.DbType = DbType.String;
command.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PatientDOB";
param.Value = patient.DateOfBirth;
param.DbType = DbType.DateTime;
command.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PatientContactNumber";
param.Value = patient.ContactNumber;
param.DbType = DbType.String;
command.Parameters.Add(param);
SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
if (dt.Rows.Count > 0)
{
patientId = Convert.ToInt32(dt.Rows[0]["patientId"]);
}
}
}
}
catch (SqlException ex)
{
throw new HospitalException(ex.Message);
}
return(patientId);
}
/// <summary>
/// Retrieve all of therapist's existing patients
/// </summary>
public List <Entity.Patient> RetrieveCurrentPatientsDelimited(string nric)
{
List <Entity.Patient> result = new List <Entity.Patient>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT patient_nric
FROM record_type_permission
WHERE therapist_nric = @nric
ORDER BY create_time;";
cmd.Parameters.AddWithValue("@nric", nric);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["patient_nric"])
};
result.Add(patient);
}
}
}
}
return(result);
}
public bool AddNote(Note note)
{
if (AccountBLL.IsTherapist())
{
note.therapist.nric = AccountBLL.GetNRIC();
note.creator.nric = AccountBLL.GetNRIC();
// check if every record is valid
RecordBLL recordBLL = new RecordBLL();
foreach (Record record in note.records)
{
Entity.Patient patient = GetPatientPermissions(record.patientNRIC);
if (patient.approvedTime == null || !recordBLL.VerifyRecord(record))
{
return(false);
}
}
therapistDAL.InsertNote(note);
foreach (Record record in note.records)
{
therapistDAL.InsertNoteRecord(note, record);
}
logAccountBLL.LogEvent(AccountBLL.GetNRIC(), "Add Note", "Note ID: " + note.id + ".");
return(true);
}
return(false);
}
/// <summary>
/// Retrieve a specific patient's permissions
/// </summary>
public Entity.Patient RetrievePatientInformation(string patientNRIC, string therapistNRIC)
{
Entity.Patient result = new Entity.Patient();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric, a.name_first, a.birth_country, a.nationality, a.sex, a.gender,
a.marital_status, a.name_last, a.address, a.address_postal_code, a.email, a.contact_number, a.create_time,
a.last_full_login, a.date_of_birth,
ap.nok_name, ap.nok_contact_number,
rtp.approved_time
FROM record_type_permission rtp
INNER JOIN account a ON a.nric = rtp.patient_nric
INNER JOIN account_patient ap ON ap.nric = rtp.patient_nric
WHERE rtp.therapist_nric = @therapistNRIC AND rtp.patient_nric = @patientNRIC
AND a.status > 0 AND ap.status = 1;";
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.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["nric"]),
firstName = Convert.ToString(reader["name_first"]),
lastName = Convert.ToString(reader["name_last"]),
countryOfBirth = Convert.ToString(reader["birth_country"]),
sex = Convert.ToString(reader["sex"]),
gender = Convert.ToString(reader["gender"]),
dateOfBirth = Convert.ToDateTime(reader["date_of_birth"]),
nationality = Convert.ToString(reader["nationality"]),
maritalStatus = Convert.ToString(reader["marital_status"]),
email = Convert.ToString(reader["email"]),
address = Convert.ToString(reader["address"]),
addressPostalCode = Convert.ToString(reader["address_postal_code"]),
contactNumber = Convert.ToString(reader["contact_number"]),
createTime = Convert.ToDateTime(reader["create_time"]),
nokName = Convert.ToString(reader["nok_name"]),
nokContact = Convert.ToString(reader["nok_contact_number"]),
};
patient.approvedTime = reader["approved_time"] == DBNull.Value ? null :
(DateTime?)Convert.ToDateTime(reader["approved_time"]);
result = patient;
}
}
}
}
return(result);
}
public Entity.Patient GetPatientPermissions(string patientNRIC, JWT jwt)
{
if (jwt.Roles == "01" && !patientNRIC.Equals(jwt.nric))
{
Entity.Patient result = therapistDAL.RetrievePatientPermission(patientNRIC, jwt.nric);
logAccountBLL.LogEvent(jwt.nric, "View Patients Permission", "Action on: " + patientNRIC + ".");
return(result);
}
return(null);
}
public Entity.Patient GetPatientPermissions(string patientNRIC)
{
if (AccountBLL.IsTherapist() && !patientNRIC.Equals(AccountBLL.GetNRIC()))
{
Entity.Patient result = therapistDAL.RetrievePatientPermission(patientNRIC, AccountBLL.GetNRIC());
logAccountBLL.LogEvent(AccountBLL.GetNRIC(), "View Patients Permission", "Action on: " + patientNRIC + ".");
return(result);
}
return(null);
}
/// <summary>
/// Retrieve all of therapist's existing patients
/// </summary>
public List <Entity.Patient> RetrieveCurrentPatients(string term, string nric)
{
List <Entity.Patient> result = new List <Entity.Patient>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric, a.name_first, a.name_last,
rtp.permission_unapproved, rtp.request_time, rtp.permission_approved,
rtp.approved_time, rtp.is_emergency
FROM record_type_permission rtp
INNER JOIN account a ON rtp.patient_nric = a.nric
WHERE rtp.therapist_nric = @nric AND a.nric LIKE @term
ORDER BY rtp.create_time DESC;";
cmd.Parameters.AddWithValue("@nric", nric);
cmd.Parameters.AddWithValue("@term", "%" + term + "%");
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["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"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"])
};
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"]);
result.Add(patient);
}
}
}
}
return(result);
}
protected void btnAdd_Click(object sender, EventArgs e)
{
btnAddPatient.Visible = true;
btnAdd.Visible = false;
editClient.Visible = false;
Entity.Patient oPatient = new Entity.Patient();
oPatient.address = patientAddress.Text;
oPatient.bloodgroup = patientBloodGroup.Text;
oPatient.email = patientEmail.Text;
oPatient.name = patientName.Text;
oPatient.phone = patientName.Text;
oPatient.history = patientHistory.Text;
oPatient.id = (Global.lstPatient.Count + 1).ToString();
Global.lstPatient.Add(oPatient);
Response.Write("<script>alert('Added Successfully')<script>");
}
/// <summary>
/// Retrieve therapist's existing patient
/// </summary>
public Entity.Patient RetrievePatient(string patientNRIC, string therapistNRIC)
{
Entity.Patient result = new Entity.Patient();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT DISTINCT a.nric, a.name_first, a.name_last,
rtp.permission_unapproved, rtp.request_time, rtp.is_emergency,
rtp.permission_approved, rtp.approved_time
FROM record_type_permission rtp
INNER JOIN account a ON rtp.patient_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.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["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"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"])
};
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"]);
result = patient;
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve a specific patient's permissions
/// </summary>
public Entity.Patient RetrievePatientPermission(string patientNRIC, string therapistNRIC)
{
Entity.Patient result = new Entity.Patient();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT patient_nric, permission_unapproved,
request_time, permission_approved, approved_time, is_emergency
FROM record_type_permission
WHERE therapist_nric = @therapistNRIC AND 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.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["patient_nric"]),
permissionUnapproved = Convert.ToInt16(reader["permission_unapproved"]),
permissionApproved = Convert.ToInt16(reader["permission_approved"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"])
};
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"]);
result = patient;
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve all Accounts who are patients
/// </summary>
public List <Entity.Patient> RetrieveAllPatients(string term)
{
List <Entity.Patient> result = new List <Entity.Patient>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT a.nric
FROM account a
INNER JOIN account_patient ap ON a.nric = ap.nric
WHERE a.`nric` LIKE @term AND a.status > 0 AND ap.status = 1
ORDER BY nric
LIMIT 25;";
cmd.Parameters.AddWithValue("@term", "%" + term + "%");
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Entity.Patient patient = new Entity.Patient
{
nric = Convert.ToString(reader["nric"])
};
result.Add(patient);
}
}
}
}
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);
}
