protected void ButtonDownload_Click(object sender, EventArgs e)
{
try
{
// Retrieve gen settings
GeneralizedSetting generalizedSetting = dataBLL.GetGeneralizedSettingFromDb();
#region Validation: Match and Get only valid inputs
FilteredValues filteredValues = new FilteredValues();
// Marital Status
foreach (ListItem item in inputMaritalStatusLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.maritalStatusOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.maritalStatus.Add(item.Value.Trim());
}
}
}
// Sex
foreach (ListItem item in inputSexLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.sexOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.sex.Add(item.Value.Trim());
}
}
}
// Gender
foreach (ListItem item in inputGenderLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.genderOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.gender.Add(item.Value.Trim());
}
}
}
// Age
foreach (ListItem item in inputAgeLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.ageOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.age.Add(item.Value.Trim());
}
}
}
// Postal
DataTable postalTable = dataBLL.GetPostal();
foreach (ListItem item in inputPostal.Items)
{
if (item.Selected)
{
if (postalTable.AsEnumerable().Any(row => row.Field <string>("postal").Equals(item.Value.Trim())))
{
filteredValues.postal.Add(item.Value.Trim());
}
}
}
// Diagnosis
DataTable diagnosesTable = dataBLL.GetDiagnoses();
foreach (ListItem item in inputDiagnosis.Items)
{
if (item.Selected)
{
if (diagnosesTable.AsEnumerable().Any(row => row.Field <string>("diagnosis_code").Equals(item.Value.Trim())))
{
filteredValues.diagnoses.Add(item.Value.Trim());
}
}
}
// Record Type
foreach (ListItem item in inputRecordType.Items)
{
if (item.Selected)
{
filteredValues.recordType.Add(item.Value.Trim());
}
}
// Record Diagnosis
DataTable recordDiagnosesTable = dataBLL.GetRecordDiagnoses();
foreach (ListItem item in inputRecordDiagnosis.Items)
{
if (item.Selected)
{
if (recordDiagnosesTable.AsEnumerable().Any(row => row.Field <string>("diagnosis_code").Equals(item.Value.Trim())))
{
filteredValues.recordDiagnoses.Add(item.Value.Trim());
}
}
}
#endregion
DataTable anonPatientsTable = dataBLL.GetPatientsForDownload(filteredValues);
string delimiter = ",";
using (MemoryStream memoryStream = new MemoryStream())
{
using (StreamWriter streamWriter = new StreamWriter(memoryStream))
{
for (int i = 0; i < anonPatientsTable.Columns.Count; i++)
{
streamWriter.Write(anonPatientsTable.Columns[i].ColumnName);
streamWriter.Write((i < anonPatientsTable.Columns.Count - 1) ? delimiter : Environment.NewLine);
}
foreach (DataRow row in anonPatientsTable.Rows)
{
for (int i = 0; i < anonPatientsTable.Columns.Count; i++)
{
if (row[i].ToString().IndexOf(",") > -1)
{
streamWriter.Write("\"" + row[i].ToString() + "\"");
}
else
{
streamWriter.Write(row[i].ToString());
}
streamWriter.Write((i < anonPatientsTable.Columns.Count - 1) ? delimiter : Environment.NewLine);
}
}
}
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "text/csv";
Response.Charset = string.Empty;
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.AddHeader("Content-Disposition", "attachment; filename=\"anon_data.csv\"");
Response.BinaryWrite(memoryStream.ToArray());
}
ViewState["GridViewPatientAnonymised"] = null;
GridViewPatientAnonymised.DataSource = null;
GridViewPatientAnonymised.DataBind();
PanelViewHeader.Visible = false;
PanelView.Visible = false;
UpdatePanelPatientAnonymised.Update();
Response.Flush();
Response.Close();
}
catch
{
}
}
/// <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);
}
protected void ButtonFilter_ServerClick(object sender, EventArgs e)
{
try
{
// Retrieve gen settings
GeneralizedSetting generalizedSetting = dataBLL.GetGeneralizedSettingFromDb();
#region Validation: Match and Get only valid inputs
FilteredValues filteredValues = new FilteredValues();
// Marital Status
foreach (ListItem item in inputMaritalStatusLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.maritalStatusOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.maritalStatus.Add(item.Value.Trim());
}
}
}
// Sex
foreach (ListItem item in inputSexLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.sexOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.sex.Add(item.Value.Trim());
}
}
}
// Gender
foreach (ListItem item in inputGenderLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.genderOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.gender.Add(item.Value.Trim());
}
}
}
// Age
foreach (ListItem item in inputAgeLevel.Items)
{
if (item.Selected)
{
if (generalizedSetting.ageOptions.Any(t => t.Item1.Equals(item.Value.Trim())))
{
filteredValues.age.Add(item.Value.Trim());
}
}
}
// Postal
DataTable postalTable = dataBLL.GetPostal();
foreach (ListItem item in inputPostal.Items)
{
if (item.Selected)
{
if (postalTable.AsEnumerable().Any(row => row.Field <string>("postal").Equals(item.Value.Trim())))
{
filteredValues.postal.Add(item.Value.Trim());
}
}
}
// Diagnosis
DataTable diagnosesTable = dataBLL.GetDiagnoses();
foreach (ListItem item in inputDiagnosis.Items)
{
if (item.Selected)
{
if (diagnosesTable.AsEnumerable().Any(row => row.Field <string>("diagnosis_code").Equals(item.Value.Trim())))
{
filteredValues.diagnoses.Add(item.Value.Trim());
}
}
}
// Record Type
foreach (ListItem item in inputRecordType.Items)
{
if (item.Selected)
{
filteredValues.recordType.Add(item.Value.Trim());
}
}
// Record Diagnosis
DataTable recordDiagnosesTable = dataBLL.GetRecordDiagnoses();
foreach (ListItem item in inputRecordDiagnosis.Items)
{
if (item.Selected)
{
if (recordDiagnosesTable.AsEnumerable().Any(row => row.Field <string>("diagnosis_code").Equals(item.Value.Trim())))
{
filteredValues.recordDiagnoses.Add(item.Value.Trim());
}
}
}
#endregion
List <PatientAnonymised> recordAnonymised = dataBLL.GetPatients(filteredValues);
ViewState["GridViewPatientAnonymised"] = recordAnonymised;
GridViewPatientAnonymised.DataSource = recordAnonymised;
GridViewPatientAnonymised.DataBind();
PanelViewHeader.Visible = true;
PanelView.Visible = true;
UpdatePanelPatientAnonymised.Update();
ScriptManager.RegisterStartupScript(this, GetType(), "alert", "toastr['success']('Data successfully displayed.');", true);
}
catch
{
ScriptManager.RegisterStartupScript(this, GetType(), "alert", "toastr['error']('Error occured when displaying data.');", true);
}
}
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);
}