public static DateRangeValues ParseDateRangeValues(QueryComposerTermDTO pTerm) { DateRangeValues val = new DateRangeValues(); DateTimeOffset date; if (DateTimeOffset.TryParse((pTerm.GetStringValue("Start") ?? string.Empty).ToString(), out date)) { val.StartDate = date; } if (DateTimeOffset.TryParse((pTerm.GetStringValue("End") ?? string.Empty).ToString(), out date)) { val.EndDate = date; } if (DateTimeOffset.TryParse((pTerm.GetStringValue("StartDate") ?? string.Empty).ToString(), out date)) { val.StartDate = date; } if (DateTimeOffset.TryParse((pTerm.GetStringValue("EndDate") ?? string.Empty).ToString(), out date)) { val.EndDate = date; } return(val); }
public static IEnumerable <string> ParseCodeTermValues(QueryComposerTermDTO pTerm) { IEnumerable <string> codeTermValues = from v in pTerm.GetCodeStringCollection() where !string.IsNullOrWhiteSpace(v) select v.Trim(); IEnumerable <string> codes = codeTermValues.Distinct(); return(codes); }
public static EncounterPeriodValues ParseEncounterPeriodValues(QueryComposerTermDTO pTerm) { EncounterPeriodValues val = new EncounterPeriodValues(); DateTimeOffset date; if (DateTimeOffset.TryParse((pTerm.GetStringValue("AdmissionDate") ?? string.Empty).ToString(), out date)) { val.AdmissionDate = date; } if (DateTimeOffset.TryParse((pTerm.GetStringValue("DischargeDate") ?? string.Empty).ToString(), out date)) { val.DischargeDate = date; } return(val); }
public static WeightValues ParseWeightValues(QueryComposerTermDTO pTerm) { var range = new WeightValues(); double value; if (double.TryParse(pTerm.GetStringValue("WeightMin"), out value)) { range.MinWeight = value; } if (double.TryParse(pTerm.GetStringValue("WeightMax"), out value)) { range.MaxWeight = value; } return(range); }
public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL) { //This is based on the xsl for QueryComposer in The ESPQueryBuilder project //TODO: need to see how multiple criteria are handled by the current xsl if (query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ESPDiagnosisCodesID)).Any()) { throw new NotImplementedException("The Term ESP Diagnosis Codes is currently not implemented and cannot be apart of the query."); } bool hasSQLTerm = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.SqlDistributionID)).Any(); if (hasSQLTerm) { if (query.Where.Criteria.Where(c => c.Terms.Any(d => d.Type != ModelTermsFactory.SqlDistributionID)).Any()) { throw new NotSupportedException("Another Term is Included with Sql Distribution and this is not Supported"); } var sqlCriteria = query.Where.Criteria.First().Terms.FirstOrDefault(t => t.Type == Lpp.QueryComposer.ModelTermsFactory.SqlDistributionID); string sql = (sqlCriteria.GetStringValue("Sql")); List <DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO> columnProperties = new List <QueryComposerResponsePropertyDefinitionDTO>(); var results = new QueryComposerResponseQueryResultDTO { ID = query.Header.ID, QueryStart = DateTimeOffset.UtcNow }; List <Dictionary <string, object> > queryResults = new List <Dictionary <string, object> >(); using (var cmd = db.Database.Connection.CreateCommand()) { cmd.CommandText = sql; using (var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo)) { int noNameIndex = 1; DataTable schemaTable = reader.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) { if (column.ColumnName == "ColumnName") { string columnName = row[column].ToString(); if (string.IsNullOrWhiteSpace(columnName)) { columnName = "NoColumnName" + noNameIndex; noNameIndex++; } columnProperties.Add(new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = columnName, Type = column.DataType.FullName }); } } } while (reader.Read()) { Dictionary <string, object> row = new Dictionary <string, object>(); //have to enumerate over the record using ordinal index since there may not be a column name in the reader for (int i = 0; i < columnProperties.Count; i++) { row.Add(columnProperties[i].Name, reader.GetValue(i)); } queryResults.Add(row); } reader.Close(); } } results.QueryEnd = DateTimeOffset.UtcNow; results.Results = new[] { queryResults }; results.Properties = columnProperties; return(new[] { results }); } else { bool hasConditionsTerm = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ConditionsID)).Any(); bool hasICD9CodesTerm = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ICD9DiagnosisCodes3digitID)).Any(); var firstCriteria = query.Where.Criteria.First(); QueryComposerTermDTO ageRangeTerm = GetAllTermsWithinCriteria(firstCriteria, ModelTermsFactory.AgeRangeID).FirstOrDefault(); var diagnosisQuery = from d in db.Diagnosis select d; var diseasesQuery = from d in db.Diseases select d; var encounterQuery = db.Demographics.Join(db.Encounters, o => o.PatID, i => i.PatID, (o, i) => new { o.PatID, i.EncounterID, i.AgeGroup5yr, i.AgeGroup10yr, i.A_Date, i.AgeAtEncYear }); if (ageRangeTerm != null) { var ageRange = AdapterHelpers.ParseAgeRangeValues(ageRangeTerm); if (ageRange.MinAge.HasValue) { diagnosisQuery = diagnosisQuery.Where(q => q.AgeAtEncYear >= ageRange.MinAge.Value); diseasesQuery = diseasesQuery.Where(q => q.AgeAtDetectYear >= ageRange.MinAge.Value); encounterQuery = encounterQuery.Where(q => q.AgeAtEncYear >= ageRange.MinAge.Value); } else if (ageRange.MaxAge.HasValue) { diagnosisQuery = diagnosisQuery.Where(q => q.AgeAtEncYear <= ageRange.MaxAge.Value); diseasesQuery = diseasesQuery.Where(q => q.AgeAtDetectYear <= ageRange.MaxAge.Value); encounterQuery = encounterQuery.Where(q => q.AgeAtEncYear <= ageRange.MaxAge.Value); } } //var observationPeriod = firstCriteria.Terms.FirstOrDefault(t => t.Type == ModelTermsFactory.ObservationPeriodID); var observationPeriod = GetAllTermsWithinCriteria(firstCriteria, ModelTermsFactory.ObservationPeriodID).FirstOrDefault(); if (observationPeriod != null) { var obp_range = AdapterHelpers.ParseDateRangeValues(observationPeriod); if (obp_range.StartDate.HasValue) { int startDate = ConvertDateToNumberOfDays(obp_range.StartDate.Value); diagnosisQuery = diagnosisQuery.Where(q => q.A_Date >= startDate); diseasesQuery = diseasesQuery.Where(q => q.Date >= startDate); encounterQuery = encounterQuery.Where(q => q.A_Date >= startDate); } if (obp_range.EndDate.HasValue) { int endDate = ConvertDateToNumberOfDays(obp_range.EndDate.Value); diagnosisQuery = diagnosisQuery.Where(q => q.A_Date <= endDate); diseasesQuery = diseasesQuery.Where(q => q.Date <= endDate); encounterQuery = encounterQuery.Where(q => q.A_Date <= endDate); } } //TODO: need to determine the precision of icd9 code from somewhere int icd9Precision = hasICD9CodesTerm ? 3 : 0;//0 == exclude, 3,4,5 var entityQuery = from demographics in db.Demographics join race in db.UVT_Race on demographics.Race equals race.Code join sex in db.UVT_Sex on demographics.Sex equals sex.Code join ethnicity in db.UVT_Race_Ethnicity on demographics.Ethnicity equals ethnicity.Code select new ESPQueryResult { PatientID = demographics.PatID, Patients = null, Sex = sex.Text, EthnicityCode = demographics.Ethnicity, Ethnicity = ethnicity.Text, Race = race.Text, Zip = demographics.Zip5, TobaccoUse = demographics.Smoking, Code = null, CodeDescription = null, Disease = null, ObservationPeriod = null, Age_10yrGroup = null, Age_5yrGroup = null, Age_Detect = null, CenterID = null }; if (icd9Precision == 3) { entityQuery = from o in entityQuery from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty() from dx in db.UVT_Dx3Digit.Where(x => x.Code == diagnosis_inc.DxCode3digit).DefaultIfEmpty() select new ESPQueryResult { PatientID = o.PatientID, Patients = null, Sex = o.Sex, EthnicityCode = o.EthnicityCode, Ethnicity = o.Ethnicity, Race = o.Race, Zip = o.Zip, TobaccoUse = o.TobaccoUse, Code = dx.Code, CodeDescription = dx.Text, Disease = o.Disease, //NOTE: Value in db is # of days from 1960-01-01 ObservationPeriod = diagnosis_inc.A_Date, Age_10yrGroup = diagnosis_inc.AgeGroup10yr, Age_5yrGroup = diagnosis_inc.AgeGroup5yr, Age_Detect = diagnosis_inc.AgeAtEncYear, CenterID = diagnosis_inc.CenterID }; } else if (icd9Precision == 4) { entityQuery = from o in entityQuery from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty() from dx in db.UVT_Dx4Digit.Where(x => x.Code == diagnosis_inc.DxCode4digitWithDec).DefaultIfEmpty() select new ESPQueryResult { PatientID = o.PatientID, Patients = null, Sex = o.Sex, EthnicityCode = o.EthnicityCode, Ethnicity = o.Ethnicity, Race = o.Race, Zip = o.Zip, TobaccoUse = o.TobaccoUse, Code = dx.Code, CodeDescription = dx.Text, Disease = o.Disease, //NOTE: Value in db is # of days from 1960-01-01 ObservationPeriod = diagnosis_inc.A_Date, Age_10yrGroup = diagnosis_inc.AgeGroup10yr, Age_5yrGroup = diagnosis_inc.AgeGroup5yr, Age_Detect = diagnosis_inc.AgeAtEncYear, CenterID = diagnosis_inc.CenterID }; } else if (icd9Precision == 5) { entityQuery = from o in entityQuery from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty() from dx in db.UVT_Dx5Digit.Where(x => x.Code == diagnosis_inc.DxCode5digitWithDec).DefaultIfEmpty() select new ESPQueryResult { PatientID = o.PatientID, Patients = null, Sex = o.Sex, EthnicityCode = o.EthnicityCode, Ethnicity = o.Ethnicity, Race = o.Race, Zip = o.Zip, TobaccoUse = o.TobaccoUse, Code = dx.Code, CodeDescription = dx.Text, Disease = o.Disease, //NOTE: Value in db is # of days from 1960-01-01 ObservationPeriod = diagnosis_inc.A_Date, Age_10yrGroup = diagnosis_inc.AgeGroup10yr, Age_5yrGroup = diagnosis_inc.AgeGroup5yr, Age_Detect = diagnosis_inc.AgeAtEncYear, CenterID = diagnosis_inc.CenterID }; } if (hasConditionsTerm) { entityQuery = from o in entityQuery from disease_inc in diseasesQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty() select new ESPQueryResult { PatientID = o.PatientID, Patients = null, Sex = o.Sex, EthnicityCode = o.EthnicityCode, Ethnicity = o.Ethnicity, Race = o.Race, Zip = o.Zip, TobaccoUse = o.TobaccoUse, Code = o.Code, CodeDescription = o.CodeDescription, Disease = disease_inc.Condition, //NOTE: Value in db is # of days from 1960-01-01 ObservationPeriod = disease_inc.Date, Age_10yrGroup = disease_inc.AgeGroup10yr, Age_5yrGroup = disease_inc.AgeGroup5yr, Age_Detect = disease_inc.AgeAtDetectYear, //center ID is not set in xsl when has visits CenterID = o.CenterID }; } List <string> selectProperties = new List <string> { "Patients" }; //Patient aggregate is always included. bool primaryCriteria = true; foreach (var criteria in query.Where.Criteria) { if (!primaryCriteria && criteria.Exclusion) { //exclusion is a not exists in the where clause var subQuery = ApplyCriteria(criteria); entityQuery = entityQuery.Where(q => !subQuery.Where(s => s.PatID == q.PatientID).Any()); } else if (!primaryCriteria) { var subQuery = ApplyCriteria(criteria); entityQuery = entityQuery.Where(q => subQuery.Where(s => s.PatID == q.PatientID).Any()); } else { primaryCriteria = false; //process the first criteria as the primary query Expression <Func <ESPQueryResult, bool> > icdDiseasePredicate = null; foreach (var tGroup in (criteria.Criteria.SelectMany(c => c.Terms).Concat(criteria.Terms)).GroupBy(k => k.Type)) { var termID = tGroup.Key; if (termID == Lpp.QueryComposer.ModelTermsFactory.AgeRangeID) { selectProperties.AddRange(new[] { "Age_5yrGroup", "Age_10yrGroup" }); //age range is incorporated into the diagnosis and disease joins } else if (termID == Lpp.QueryComposer.ModelTermsFactory.ConditionsID) { selectProperties.AddRange(new[] { "Disease" }); IEnumerable <string> diseases = tGroup.Select(g => TranslateCondition(g.GetStringValue("Condition"))).ToArray(); if (diseases.Any()) { if (icdDiseasePredicate == null) { icdDiseasePredicate = q => diseases.Contains(q.Disease); } else { icdDiseasePredicate = icdDiseasePredicate.Or(q => diseases.Contains(q.Disease)); } } } else if (termID == Lpp.QueryComposer.ModelTermsFactory.EthnicityID) { selectProperties.Add("Ethnicity"); var values = tGroup.SelectMany(t => t.GetValue("Ethnicities").Values <int>()).Distinct().Select(t => TranslateEthnicity(t)).ToArray(); Expression <Func <ESPQueryResult, bool> > ethnicityExpression = null; foreach (int value in values) { if (ethnicityExpression == null) { ethnicityExpression = eth => eth.EthnicityCode == value; } else { ethnicityExpression = ethnicityExpression.Or(q => q.EthnicityCode == value); } } if (ethnicityExpression != null) { entityQuery = entityQuery.Where(ethnicityExpression); } } else if (termID == Lpp.QueryComposer.ModelTermsFactory.SexID) { selectProperties.Add("Sex"); IEnumerable <string> sexs = tGroup.SelectMany(t => TranslateSex(t.GetStringValue("Sex"))).Distinct(); logger.Debug(sexs); if (sexs.Any()) { entityQuery = entityQuery.Where(q => sexs.Contains(q.Sex.ToUpper())); } } else if (termID == Lpp.QueryComposer.ModelTermsFactory.ICD9DiagnosisCodes3digitID) { selectProperties.AddRange(new[] { "Code", "CodeDescription" }); //TODO:going to have to be terms for 4 and 5 digit as well. IEnumerable <string> codes = tGroup.SelectMany(t => AdapterHelpers.ParseCodeTermValues(t)).Select(t => t.Trim()).Distinct(); if (codes.Any()) { Expression <Func <ESPQueryResult, bool> > icd9Expression = null; foreach (string code in codes) { if (icd9Expression == null) { icd9Expression = q => q.Code.StartsWith(code.ToUpper()); } else { icd9Expression = icd9Expression.Or(q => q.Code.StartsWith(code.ToUpper())); } } if (icdDiseasePredicate == null) { icdDiseasePredicate = icd9Expression; } else { icdDiseasePredicate = icdDiseasePredicate.Or(icd9Expression); } } } else if (termID == Lpp.QueryComposer.ModelTermsFactory.ObservationPeriodID) { if (hasConditionsTerm || hasICD9CodesTerm) { selectProperties.AddRange(new[] { "ObservationPeriod" }); } //observation period is incorporated into the diagnosis and disease joins } else if (termID == Lpp.QueryComposer.ModelTermsFactory.VisitsID) { try { IQueryable <EncountersGroupingResult> encountersAppliedQuery = encounterQuery.GroupBy(v => new { v.PatID, v.AgeGroup5yr, v.AgeGroup10yr }).Select(v => new EncountersGroupingResult { PatID = v.Key.PatID, AgeGroup5yr = v.Key.AgeGroup5yr, AgeGroup10yr = v.Key.AgeGroup10yr, Count = v.Count() }); int visits = tGroup.Where(t => t.GetValue("Visits") != null).Select(t => Convert.ToInt32(t.GetStringValue("Visits"))).Min(); if (!hasConditionsTerm && !hasICD9CodesTerm) { entityQuery = entityQuery.Join(encountersAppliedQuery.Where(q => q.Count >= visits), o => o.PatientID, i => i.PatID, (o, i) => new ESPQueryResult { PatientID = o.PatientID, Patients = null, Sex = o.Sex, Ethnicity = o.Ethnicity, Race = o.Race, Zip = o.Zip, TobaccoUse = o.TobaccoUse, Code = o.Code, CodeDescription = o.CodeDescription, Disease = o.Disease, ObservationPeriod = o.ObservationPeriod, Age_10yrGroup = i.AgeGroup10yr, Age_5yrGroup = i.AgeGroup5yr, Age_Detect = o.Age_Detect, CenterID = o.CenterID }); } else { entityQuery = entityQuery.Join(encountersAppliedQuery.Where(q => q.Count >= visits), o => o.PatientID, i => i.PatID, (o, i) => o); } } catch { } } else if (termID == Lpp.QueryComposer.ModelTermsFactory.ZipCodeID) { selectProperties.Add("Zip"); var zipCodes = tGroup.SelectMany(t => AdapterHelpers.ParseCodeTermValues(t)).Select(t => t.Trim()).Distinct(); if (zipCodes.Any()) { entityQuery = entityQuery.Where(q => zipCodes.Contains(q.Code)); } } else { logger.Debug("Term specified but not implemented in criteria: " + termID); } } if (icdDiseasePredicate != null) { entityQuery = entityQuery.Where(icdDiseasePredicate); } } } entityQuery = entityQuery.GroupBy(k => new { k.Sex, k.EthnicityCode, k.Ethnicity, k.Race, k.Zip, k.TobaccoUse, k.Code, k.CodeDescription, k.Disease, k.ObservationPeriod, k.Age_10yrGroup, k.Age_5yrGroup, k.Age_Detect, k.CenterID }) .Select(k => new ESPQueryResult { PatientID = null, Patients = k.Count(), Sex = k.Key.Sex, EthnicityCode = k.Key.EthnicityCode, Ethnicity = k.Key.Ethnicity, Race = k.Key.Race, Zip = k.Key.Zip, TobaccoUse = k.Key.TobaccoUse, Code = k.Key.Code, CodeDescription = k.Key.CodeDescription, Disease = k.Key.Disease, ObservationPeriod = k.Key.ObservationPeriod, Age_10yrGroup = k.Key.Age_10yrGroup, Age_5yrGroup = k.Key.Age_5yrGroup, Age_Detect = k.Key.Age_Detect, CenterID = k.Key.CenterID }); //TODO: generate the select fields and apply to the query //NOTE: dynamically selecting into a result class didn't affect the generated query, skipping and filtering when building response. //var select = query.Select(Utility.MapToClass<ESPQueryResult, ESPAggregatedResult>(selectProperties)); //TODO: if all projection are stratification and options specified? wrap with additional query //TODO: apply the ordering to the select projection /* * Here's a possiblity using reflection... * var param = "Address"; * var pi = typeof(Student).GetProperty(param); * var orderByAddress = items.OrderBy(x => pi.GetValue(x, null)); * * see that will use expression: * http://www.singingeels.com/Blogs/Nullable/2008/03/26/Dynamic_LINQ_OrderBy_using_String_Names.aspx * */ var response = new QueryComposerResponseQueryResultDTO { ID = query.Header.ID, QueryStart = DateTimeOffset.UtcNow }; List <Dictionary <string, object> > results = new List <Dictionary <string, object> >(); if (!viewSQL) { logger.Debug(entityQuery.ToString()); foreach (var item in entityQuery) { Dictionary <string, object> row = new Dictionary <string, object>(); Type itemType = item.GetType(); foreach (var propInfo in itemType.GetProperties()) { if (selectProperties.Contains(propInfo.Name)) { object value = propInfo.GetValue(item, null); if (propInfo.Name == "ObservationPeriod") { //The value is currently the number of days from Jan 1, 1960, convert to date. value = new DateTime(1960, 1, 1, 0, 0, 0, DateTimeKind.Unspecified).AddDays(Convert.ToInt32(value ?? 0)); } row.Add(propInfo.Name, value); } } results.Add(row); } } else { Dictionary <string, object> row = new Dictionary <string, object>(); row.Add("SQL", entityQuery.ToString()); results.Add(row); } logger.Debug("Number of results found:" + results.Count); //QueryComposerResponseDTO response = new QueryComposerResponseDTO //{ // ResponseDateTime = DateTime.UtcNow, // Results = new[] { results } //}; //if (query.ID.HasValue) // response.RequestID = query.ID.Value; response.QueryEnd = DateTimeOffset.UtcNow; response.Results = new[] { results }; return(new[] { response }); } }
protected virtual System.Linq.Expressions.Expression <Func <TBaseQuery, bool> > ApplyVitalsMeasureDateObservationPeriod(QueryComposerCriteriaDTO paragraph, QueryComposerTermDTO term) { throw new NotImplementedException("The critieria builder for this term has not been implemented."); }
protected virtual System.Linq.Expressions.Expression <Func <TBaseQuery, bool> > ApplyTobaccoUseTerm(QueryComposerCriteriaDTO paragraph, QueryComposerTermDTO term) { throw new NotImplementedException("The critieria builder for this term has not been implemented."); }
/// <summary> /// Parses the concept values for the Age Range concept. /// </summary> /// <param name="pTerm"></param> /// <returns></returns> public static AgeRangeValues ParseAgeRangeValues(QueryComposerTermDTO pTerm) { if (pTerm == null) { return(null); } int? minAge = null; int? maxAge = null; DateTimeOffset?birthStartDate = null; DateTimeOffset?birthEndDate = null; DTO.Enums.AgeRangeCalculationType?calculationType = null; DateTime?calculateAsOf = null; if (pTerm.GetValue("MinAge") != null) { int tempValue; if (Int32.TryParse(pTerm.GetStringValue("MinAge"), out tempValue)) { minAge = tempValue; } } if (pTerm.GetValue("MaxAge") != null) { int tempValue; if (Int32.TryParse(pTerm.GetStringValue("MaxAge"), out tempValue)) { maxAge = tempValue; } } if (!string.IsNullOrWhiteSpace(pTerm.GetStringValue("BirthStartDate"))) { DateTimeOffset tempDate; if (DateTimeOffset.TryParse(pTerm.GetStringValue("BirthStartDate"), out tempDate)) { birthStartDate = tempDate; } } if (!string.IsNullOrWhiteSpace(pTerm.GetStringValue("BirthEndDate"))) { DateTimeOffset tempDate; if (DateTimeOffset.TryParse(pTerm.GetStringValue("BirthEndDate"), out tempDate)) { birthEndDate = tempDate; } } DTO.Enums.AgeRangeCalculationType calculateTypeAttempt; if (pTerm.GetEnumValue <DTO.Enums.AgeRangeCalculationType>("CalculationType", out calculateTypeAttempt)) { calculationType = calculateTypeAttempt; } if (!string.IsNullOrWhiteSpace("CalculateAsOf")) { DateTime tempDate; if (DateTime.TryParse(pTerm.GetStringValue("CalculateAsOf"), out tempDate)) { calculateAsOf = tempDate; } } return(new AgeRangeValues { MinAge = minAge, MaxAge = maxAge, BirthStartDate = birthStartDate, BirthEndDate = birthEndDate, CalculateAsOf = calculateAsOf, CalculationType = calculationType }); }
public static IEnumerable <string> ParseOtherCodeTermValues(QueryComposerTermDTO pTerm) { string[] codes = pTerm.GetStringValue("OtherCode").Split(',').Distinct().ToArray(); return(codes); }