///<summary>POuts the passed in value in the appropriate manner.</summary> private static string POutObj(object value, bool doTreatDtAsDate) { if (value is bool) { return(POut.Bool((bool)value)); } else if (value is int) { return(POut.Int((int)value)); } else if (value is long) { return(POut.Long((long)value)); } else if (value is DateTime) { if (doTreatDtAsDate) { return(POut.Date((DateTime)value)); } else { return(POut.DateT((DateTime)value)); } } else if (value is string) { return("'" + POut.String((string)value) + "'"); } else if (value is double) { return(POut.Double((double)value)); } else if (value is decimal) { return(POut.Decimal((decimal)value)); } else if (value is byte) { return(POut.Byte((byte)value)); } else if (value is float) { return(POut.Float((float)value)); } else if (value is TimeSpan) { return("'" + POut.TSpan((TimeSpan)value) + "'"); } else if (value.GetType().IsEnum) { return(POut.Int((int)value)); } else { throw new NotImplementedException(value.GetType().Name + " has not been implemented in SQLWhere"); } }
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)); }