public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
//TODO Units
from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " ";
where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join
where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '"
+ elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date
}
switch (elementList[i].LabValueType)
{
//CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt
case HL70125.CE:
case HL70125.CWE:
select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue";
where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR "
+ "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') "
+ "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') ";
break;
//DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]]
case HL70125.DT:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y");
where += "AND " + DbHelper.DtimeToDate("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')")
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='DT' ";
break;
//TS is YYYYMMDDHHMMSS, string compare
case HL70125.TS:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s");
where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s")))
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TS' ";
break;
//00:00:00
case HL70125.TM:
select += ",ehrlabresult" + i + ".ObservationValueTime";
where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TM' ";
break;
case HL70125.SN:
select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)";
where += "AND ehrlabresult" + i + ".ValueType='SN' ";
break;
case HL70125.NM:
select += ",ehrlabresult" + i + ".ObservationValueNumeric";
where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " "
+ "AND ehrlabresult" + i + ".ValueType='NM' ";
break;
case HL70125.FT:
case HL70125.ST:
case HL70125.TX:
select += ",ehrlabresult" + i + ".ObservationValueText";
//where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" "
where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') ";
break;
}
select += ",ehrlabresult" + i + ".ObservationDateTime ";
//select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid.
//from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i;
//where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join
//where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' "
// +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter
//if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date
//}
//if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date
//}
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title.
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",disease" + i + ".DateStart"; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Allergy: //-----------------------------------------------------------------------------------------------------------------------------
select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title.
from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i;
where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join
where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.CommPref: //----------------------------------------------------------------------------------------------------------------------------
select += ",patient.PreferContactConfidential";
from += ""; //only selecting from patient table
where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " ";
break;
default:
//should never happen.
continue;
}
}
string command = select + " " + from + " " + where;
return(Db.GetTable(command));
}
