public Record GetRecord(long recordID)
{
if (AccountBLL.IsResearcher())
{
return(dataDAL.RetrieveRecord(recordID));
}
return(null);
}
public GeneralizedSetting GetGeneralizedSettingFromDb()
{
if (AccountBLL.IsResearcher())
{
return(dataDAL.RetrieveGeneralizationLevel());
}
return(null);
}
public DataTable GetPostal()
{
if (AccountBLL.IsResearcher())
{
return(dataDAL.RetrievePostal());
}
return(null);
}
public DataTable GetRecordDiagnoses()
{
if (AccountBLL.IsResearcher())
{
return(dataDAL.RetrieveRecordDiagnoses());
}
return(null);
}
public List <RecordDiagnosis> GetRecordDiagnoses(long recordID)
{
if (AccountBLL.IsResearcher())
{
return(dataDAL.RetrieveRecordDiagnoses(recordID));
}
return(null);
}
public List <PatientDiagnosis> GetPatientDiagnoses(string id)
{
if (AccountBLL.IsResearcher())
{
List <PatientDiagnosis> result = dataDAL.RetrievePatientDiagnoses(id);
logAccountBLL.LogEvent(AccountBLL.GetNRIC(), "View Patient Diagnoses", "View Patient Diagnoses");
return(result);
}
return(null);
}
public List <Record> GetRecords(List <long> recordIDs)
{
if (AccountBLL.IsResearcher())
{
IEnumerable <Tuple <string, long> > recordIDsParameterized = from recordID in recordIDs
select(new Tuple <string, long>("@" + recordID.ToString().Replace(" ", string.Empty), recordID));
List <Record> result = dataDAL.RetrieveRecords(recordIDsParameterized);
logRecordBLL.LogEvent(AccountBLL.GetNRIC(), "View Records", "Record IDs: " + string.Join(", ", recordIDs) + ".");
return(result);
}
return(null);
}
/// <summary>
/// Retrieve patients that fit the filters set
/// </summary>
/// <param name="filteredValues">Object containing filtered values</param>
/// <returns>List of PatientAnonymised</returns>
public List <PatientAnonymised> GetPatients(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, GROUP_CONCAT(DISTINCT r.id SEPARATOR ',') as record_ids
FROM patients_anonymized pa INNER JOIN record r ON pa.nric = r.patient_nric LEFT JOIN record_diagnosis rd ON r.id = rd.record_id
LEFT JOIN patient_diagnosis pd ON pd.patient_nric = r.patient_nric");
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 pa.nric");
stringBuilder.Append(" ORDER BY pa.id ");
stringBuilder.Append("LIMIT 200;");
List <PatientAnonymised> patientAnonymised = dataDAL.RetrievePatients(stringBuilder.ToString());
return(patientAnonymised);
}
return(null);
}
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);
}