/// <summary>
/// Retrieve Records information with specific id owned by specific patient
/// </summary>
public Record RetrieveRecord(long recordID, string therapistNRIC)
{
Record result = new Record();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT DISTINCT r.id, r.patient_nric, r.creator_nric, r.description, r.type, r.content, r.title,
r.file_name, r.file_extension, r.file_checksum, r.file_size, r.create_time, r.is_emergency, r.file_extension, r.status as record_status,
a.name_first as creator_name_first, a.name_last as creator_name_last,
rp.status as record_permission_status
FROM record r
INNER JOIN account a ON a.nric = r.creator_nric
INNER JOIN record_type_permission rtp ON rtp.patient_nric = r.patient_nric
LEFT JOIN record_permission rp ON rp.record_id = r.id AND rp.therapist_nric = @therapistNRIC
WHERE r.id = @id AND rtp.therapist_nric = @therapistNRIC
ORDER BY r.create_time DESC;";
cmd.Parameters.AddWithValue("@id", recordID);
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
patientNRIC = Convert.ToString(reader["patient_nric"]),
creatorNRIC = Convert.ToString(reader["creator_nric"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"]),
fileExtension = Convert.ToString(reader["file_extension"]),
fileName = Convert.ToString(reader["file_name"]),
fileChecksum = Convert.ToString(reader["file_checksum"]),
createTime = Convert.ToDateTime(reader["create_time"]),
creatorFirstName = Convert.ToString(reader["creator_name_first"]),
creatorLastName = Convert.ToString(reader["creator_name_last"]),
status = Convert.ToInt16(reader["record_status"])
};
record.fileSize = reader["file_size"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["file_size"]);
record.recordPermissionStatus = reader["record_permission_status"] == DBNull.Value ? null : (short?)Convert.ToInt16(reader["record_permission_status"]);
result = record;
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Records information attached to a note
/// </summary>
public List <Record> RetrieveRecords(long noteID, string patientNRIC, string therapistNRIC)
{
List <Record> result = new List <Record>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT r.id, r.patient_nric, r.creator_nric, r.description, r.type, r.content, r.title,
r.create_time, r.is_emergency, r.file_extension, r.status as record_status,
a.name_first as creator_name_first, a.name_last as creator_name_last,
rp.status as record_permission_status
FROM medical_note mn
INNER JOIN medical_note_record mnr ON mnr.medical_note_id = mn.id
INNER JOIN record r ON r.id = mnr.record_id
INNER JOIN account a ON a.nric = r.creator_nric
LEFT JOIN record_permission rp ON rp.record_id = r.id AND rp.therapist_nric = @therapistNRIC
WHERE mn.therapist_nric = @therapistNRIC AND mn.patient_nric = @patientNRIC AND mnr.medical_note_id = @noteID
ORDER BY r.create_time DESC;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
cmd.Parameters.AddWithValue("@noteID", noteID);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
patientNRIC = Convert.ToString(reader["patient_nric"]),
creatorNRIC = Convert.ToString(reader["creator_nric"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"]),
createTime = Convert.ToDateTime(reader["create_time"]),
creatorFirstName = Convert.ToString(reader["creator_name_first"]),
creatorLastName = Convert.ToString(reader["creator_name_last"]),
fileExtension = Convert.ToString(reader["file_extension"]),
status = Convert.ToInt16(reader["record_status"])
};
record.recordPermissionStatus = reader["record_permission_status"] == DBNull.Value ? null : (short?)Convert.ToInt16(reader["record_permission_status"]);
result.Add(record);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Record's associated file information owned by specific patient NRIC
/// </summary>
public Record RetrieveFileInformation(string patientNRIC, string therapistNRIC, long recordID)
{
Record result = new Record();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT DISTINCT r.id, r.patient_nric, r.creator_nric, r.description, r.type, r.content, r.title,
r.file_name, r.file_extension, r.file_checksum, r.file_size, r.create_time, r.is_emergency,
a.name_first as creator_name_first, a.name_last as creator_name_last
FROM record r
INNER JOIN account a ON a.nric = r.creator_nric
INNER JOIN record_type_permission rtp ON rtp.patient_nric = r.patient_nric
WHERE rtp.patient_nric = @patientNRIC AND rtp.therapist_nric = @therapistNRIC
AND r.id = @id;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
cmd.Parameters.AddWithValue("@therapistNRIC", therapistNRIC);
cmd.Parameters.AddWithValue("@id", recordID);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
patientNRIC = Convert.ToString(reader["patient_nric"]),
creatorNRIC = Convert.ToString(reader["creator_nric"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"]),
fileExtension = Convert.ToString(reader["file_extension"]),
fileName = Convert.ToString(reader["file_name"]),
fileSize = Convert.ToInt32(reader["file_size"]),
fileChecksum = Convert.ToString(reader["file_checksum"]),
createTime = Convert.ToDateTime(reader["create_time"]),
creatorFirstName = Convert.ToString(reader["creator_name_first"]),
creatorLastName = Convert.ToString(reader["creator_name_last"])
};
result = record;
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Records information via a list of record IDs
/// </summary>
public List <Record> RetrieveRecords(IEnumerable <Tuple <string, long> > recordIDsParameterized)
{
if (recordIDsParameterized.Count() == 0)
{
return(null);
}
List <Record> result = new List <Record>();
using (MySqlCommand cmd = new MySqlCommand())
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"SELECT r.id, r.description, r.type, r.content, r.title, r.file_extension, r.create_time FROM record r
INNER JOIN account a ON a.nric = r.creator_nric
WHERE ");
stringBuilder.Append(string.Join(" OR ", recordIDsParameterized.Select(r => " r.id = " + r.Item1)));
stringBuilder.Append(@";");
cmd.CommandText = stringBuilder.ToString();
foreach (Tuple <string, long> r in recordIDsParameterized)
{
cmd.Parameters.AddWithValue(r.Item1, r.Item2);
}
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
fileExtension = Convert.ToString(reader["file_extension"]),
createTime = Convert.ToDateTime(reader["create_time"])
};
result.Add(record);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Records information owned by specific patient
/// </summary>
public List <Record> RetrieveRecords(string patientNRIC)
{
List <Record> result = new List <Record>();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT r.id, r.patient_nric, r.creator_nric, r.description, r.type, r.content, r.title,
r.create_time, r.is_emergency, r.file_extension,
a.name_first as creator_name_first, a.name_last as creator_name_last
FROM record r
INNER JOIN account a ON a.nric = r.creator_nric
WHERE patient_nric = @patientNRIC
ORDER BY r.create_time DESC;";
cmd.Parameters.AddWithValue("@patientNRIC", patientNRIC);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
patientNRIC = Convert.ToString(reader["patient_nric"]),
creatorNRIC = Convert.ToString(reader["creator_nric"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
isEmergency = Convert.ToBoolean(reader["is_emergency"]),
createTime = Convert.ToDateTime(reader["create_time"]),
creatorFirstName = Convert.ToString(reader["creator_name_first"]),
creatorLastName = Convert.ToString(reader["creator_name_last"]),
fileExtension = Convert.ToString(reader["file_extension"])
};
result.Add(record);
}
}
}
}
return(result);
}
/// <summary>
/// Retrieve Records information with specific id owned by specific patient
/// </summary>
public Record RetrieveRecord(long recordID)
{
Record result = new Record();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandText = @"SELECT DISTINCT r.id, r.patient_nric, r.description, r.type, r.content, r.title,
r.file_name, r.file_checksum, r.file_extension, r.file_size, r.create_time
FROM record r
INNER JOIN account a ON a.nric = r.creator_nric
WHERE r.id = @id
ORDER BY r.create_time DESC;";
cmd.Parameters.AddWithValue("@id", recordID);
using (cmd.Connection = connection)
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Record record = new Record
{
id = Convert.ToInt64(reader["id"]),
patientNRIC = Convert.ToString(reader["patient_nric"]),
description = Convert.ToString(reader["description"]),
type = RecordType.Get(Convert.ToString(reader["type"])),
content = Convert.ToString(reader["content"]),
title = Convert.ToString(reader["title"]),
fileName = Convert.ToString(reader["file_name"]),
fileChecksum = Convert.ToString(reader["file_checksum"]),
fileExtension = Convert.ToString(reader["file_extension"]),
createTime = Convert.ToDateTime(reader["create_time"])
};
record.fileSize = reader["file_size"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["file_size"]);
result = record;
}
}
}
}
return(result);
}
public HttpResponseMessage TherapistUpload([FromBody] dynamic credentials)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Unauthorized);
string jwt;
string deviceID;
string retrievedNRIC;
AccountBLL accountBLL = new AccountBLL();
JWTBLL jwtBll = new JWTBLL();
HttpContext httpContext = HttpContext.Current;
string authHeader = httpContext.Request.Headers["Authorization"];
// Ensure Authorization Header exists
if (authHeader != null && authHeader.StartsWith("Bearer"))
{
string authHeaderValue = authHeader.Substring("Bearer ".Length).Trim();
string authHeaderValueDecoded = Encoding.UTF8.GetString(Convert.FromBase64String(authHeaderValue));
string[] authHeaderParts = authHeaderValueDecoded.Split(':');
jwt = authHeaderParts[0];
deviceID = authHeaderParts[1];
}
else
{
return(response);
}
// Ensure jwt, deviceID exists
if (!(!string.IsNullOrEmpty(jwt) && AccountBLL.IsDeviceIDValid(deviceID)))
{
return(response);
}
// Validate jwt
if (!jwtBll.ValidateJWT(jwt))
{
return(response);
}
else
{
retrievedNRIC = jwtBll.getNRIC(jwt);
}
// Validate deviceID for retrievedNRIC
if (!(accountBLL.IsValid(retrievedNRIC, deviceID)))
{
return(response);
}
// Upload record
accountBLL.SetRole(retrievedNRIC, "Therapist");
Account account = accountBLL.GetStatus(retrievedNRIC);
if (account.status == 1)
{
try
{
Classes.Entity.Patient patient = new TherapistBLL().GetPatientPermissions(Convert.ToString(credentials.patientNRIC));
Record record = new Record();
record.patientNRIC = credentials.patientNRIC;
record.creatorNRIC = retrievedNRIC;
record.title = System.Text.Encoding.Default.GetString(Convert.FromBase64String(Convert.ToString(credentials.title)));
record.description = System.Text.Encoding.Default.GetString(Convert.FromBase64String(Convert.ToString(credentials.description)));
record.type = RecordType.Get(Convert.ToString(credentials.type));
record.isEmergency = patient.isEmergency;
record.content = string.Empty;
if (!record.IsTitleValid())
{
return(Request.CreateResponse(HttpStatusCode.Forbidden, "Invalid record title"));
}
if (!record.IsDescriptionValid())
{
return(Request.CreateResponse(HttpStatusCode.Forbidden, "Invalid record description"));
}
if (record.type.isContent)
{
record.content = credentials.content;
if (!record.IsContentValid())
{
return(Request.CreateResponse(HttpStatusCode.Forbidden, "Invalid record content"));
}
}
else
{
record.fileName = System.Text.Encoding.Default.GetString(Convert.FromBase64String(Convert.ToString(credentials.fileName)));
record.fileExtension = System.Text.Encoding.Default.GetString(Convert.FromBase64String(Convert.ToString(credentials.fileExtension)));
byte[] fileContent = Convert.FromBase64String(Convert.ToString(credentials.fileContent));
record.fileSize = fileContent.Length;
if (Convert.ToInt64(record.fileSize) > Convert.ToInt64(credentials.fileSize))
{
return(Request.CreateResponse(HttpStatusCode.Forbidden, "Record file size mismatch"));
}
if (!record.IsFileValid())
{
return(Request.CreateResponse(HttpStatusCode.Forbidden, "Invalid record file"));
}
record.createTime = DateTime.Now;
Directory.CreateDirectory(record.GetFileServerPath() + "\\" + record.GetFileDirectoryNameHash());
File.WriteAllBytes(record.fullpath, fileContent);
}
recordBLL.AddRecord(record);
response = Request.CreateResponse(HttpStatusCode.OK);
response.Headers.Add("Authorization", "Bearer " + jwtBll.UpdateJWT(jwt));
}
catch
{
response = Request.CreateResponse(HttpStatusCode.InternalServerError);
}
return(response);
}
return(response);
}
public DataTable GetPatientsForDownload(FilteredValues filteredValues)
{
if (AccountBLL.IsResearcher())
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"SELECT pa.id, pa.marital_status, pa.gender, pa.sex, pa.age, pa.postal,
(SELECT GROUP_CONCAT(DISTINCT pd.diagnosis_code SEPARATOR ',')
FROM patient_diagnosis pd
WHERE pd.patient_nric = pa.nric) as patient_diagnosis_code,
r.title, r.type, r.description, r.content, GROUP_CONCAT(DISTINCT rd.diagnosis_code SEPARATOR ',') as record_diagnoses_codes, r.id AS record_id, r.create_time
FROM patients_anonymized pa
INNER JOIN record r ON pa.nric = r.patient_nric
INNER JOIN patient_diagnosis pd ON pd.patient_nric = pa.nric
LEFT JOIN record_diagnosis rd ON r.id = rd.record_id ");
List <Tuple <string, List <string> > > columnsAndValuesList = new List <Tuple <string, List <string> > >();
if (filteredValues.sex.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pa.sex", filteredValues.sex));
}
if (filteredValues.gender.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pa.gender", filteredValues.gender));
}
if (filteredValues.maritalStatus.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pa.marital_status", filteredValues.maritalStatus));
}
if (filteredValues.postal.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pa.postal", filteredValues.postal));
}
if (filteredValues.diagnoses.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pd.diagnosis_code", filteredValues.diagnoses));
}
if (filteredValues.recordType.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("r.type", filteredValues.recordType));
}
if (filteredValues.recordDiagnoses.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("rd.diagnosis_code", filteredValues.recordDiagnoses));
}
if (filteredValues.age.Count > 0)
{
columnsAndValuesList.Add(new Tuple <string, List <string> >("pa.age", filteredValues.age));
}
List <string> tempList = new List <string>();
if (columnsAndValuesList.Any())
{
tempList.Add(" (" + string.Join(" AND ", columnsAndValuesList.Select(tuple => JoinMultipleSelectedValues(tuple.Item1, tuple.Item2))) + ")");
}
if (tempList.Count > 0)
{
stringBuilder.Append(" WHERE " + string.Join(" AND ", tempList));
}
stringBuilder.Append(" GROUP BY r.id");
stringBuilder.Append(" ORDER BY pa.id;");
DataTable anonPatientsTable = dataDAL.RetrieveAnonPatients(stringBuilder.ToString());
anonPatientsTable.Columns.Add("data", typeof(string));
string domain = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
foreach (DataRow row in anonPatientsTable.Rows)
{
long recordId = Convert.ToInt64(row["record_id"]);
RecordType recordType = RecordType.Get(Convert.ToString(row["type"]));
if (recordType.isContent)
{
row["data"] = Convert.ToString(row["content"]) + recordType.prefix;
}
else
{
row["data"] = domain + "/Researcher/Download.ashx?record=" + recordId.ToString();
}
}
anonPatientsTable.Columns.Remove("content");
anonPatientsTable.Columns.Remove("record_id");
// Renaming the columns in the datatable
anonPatientsTable.Columns["id"].ColumnName = "patient id";
anonPatientsTable.Columns["marital_status"].ColumnName = "marital status";
anonPatientsTable.Columns["patient_diagnosis_code"].ColumnName = "patient diagnoses";
anonPatientsTable.Columns["type"].ColumnName = "record type";
anonPatientsTable.Columns["create_time"].ColumnName = "record creation time";
anonPatientsTable.Columns["description"].ColumnName = "record description";
anonPatientsTable.Columns["record_diagnoses_codes"].ColumnName = "record diagnoses";
return(anonPatientsTable);
}
return(null);
}
