public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
{
SummarySqlQueryAdapter sql = new SummarySqlQueryAdapter();
var result = sql.Execute(query, _settings, viewSQL);
return(new[] { result });
}
public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
{
IQueryAdapter queryAdapter = null;
if (query.Where.Criteria.First().Terms.Any(t => t.Type == Lpp.QueryComposer.ModelTermsFactory.SqlDistributionID))
{
logger.Debug("Sql Distribution term found, creating SummarySqlQueryAdapter.");
SummarySqlQueryAdapter sql = new SummarySqlQueryAdapter();
var result = sql.Execute(query, _settings, viewSQL);
return(new[] { result });
}
if (query.Select.Fields.Any(t => t.Type == ModelTermsFactory.ICD9DiagnosisCodes3digitID))
{
logger.Debug("ICD-9 Diagnoses Codes term found, creating IncidenceICD9DiagnosisQueryAdapter.");
queryAdapter = new IncidenceICD9DiagnosisQueryAdapter(_settings);
}
else if (query.Select.Fields.Any(t => t.Type == ModelTermsFactory.DrugNameID) || query.Select.Fields.Any(t => t.Type == ModelTermsFactory.DrugClassID))
{
if (query.Select.Fields.Any(t => t.Type == ModelTermsFactory.DrugNameID))
{
logger.Debug("Pharmacy dispensing generic drug name term found, creating IncidencePharmaDispensingQueryAdapter.");
}
else
{
logger.Debug("Pharmacy dispensing drug class term found, creating IncidencePharmaDispensingQueryAdapter.");
}
queryAdapter = new IncidencePharmaDispensingQueryAdapter(_settings);
}
if (queryAdapter == null)
{
throw new InvalidOperationException("Unable to determine the query adapter to use based on the primary criteria terms.");
}
using (queryAdapter)
{
var qcResponseDTO = queryAdapter.Execute(query, viewSQL);
foreach (var r in qcResponseDTO)
{
r.LowCellThrehold = _lowThresholdValue;
}
return(qcResponseDTO);
}
}
public virtual IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO request, bool viewSQL)
{
SummaryRequestModel args = ConvertToModel(request);
IEnumerable <string> queries = BuildQueries(args);
List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors = new List <DTO.QueryComposer.QueryComposerResponseErrorDTO>();
var response = new QueryComposerResponseQueryResultDTO {
ID = request.Header.ID, QueryStart = DateTimeOffset.UtcNow
};
List <Dictionary <string, object> > resultSets = new List <Dictionary <string, object> >(500);
foreach (var query in queries)
{
IEnumerable <Dictionary <string, object> > resultSet = ExecuteQuery(query, errors, viewSQL);
if (resultSet != null)
{
resultSets.AddRange(resultSet);
}
if (errors.Count > 0)
{
break;
}
}
response.QueryEnd = DateTimeOffset.UtcNow;
response.Results = new[] { resultSets };
response.Errors = errors;
response.Properties = GetResponsePropertyDefinitions();
if (_lowThresholdValue.HasValue)
{
response.LowCellThrehold = _lowThresholdValue;
response.Properties = response.Properties.Concat(new[] { new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO {
Name = LowThresholdColumnName, As = LowThresholdColumnName, Type = "System.Boolean"
} });
}
response.Aggregation = GetResponseAggregationDefinition();
return(new[] { response });
}
/// <summary> /// Converts the request dto into a SummaryRequestModel that the query translator can use. /// </summary> /// <param name="request"></param> /// <returns></returns> protected abstract SummaryRequestModel ConvertToModel(QueryComposerQueryDTO query);
public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
{
List <Dictionary <string, object> > formattedResults = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > unFormattedResults = new List <Dictionary <string, object> >();
List <MetadataRefreshResult> results = new List <MetadataRefreshResult>();
List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors = new List <QueryComposerResponseErrorDTO>();
DateTimeOffset queryStart = DateTimeOffset.UtcNow;
System.Data.IDbConnection conn = null;
try
{
conn = Utilities.OpenConnection(_settings, logger);
List <string> tables = new List <string>();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
cmd.CommandTimeout = Convert.ToInt32(_settings.GetSetting("CommandTimeout", 120));
logger.Debug("Executing query:" + Environment.NewLine + "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
}
}
var sqlSB = new StringBuilder();
if (tables.Contains("drug_class", StringComparer.OrdinalIgnoreCase))
{
sqlSB.Append("SELECT DISTINCT(Period), 'Drug Class' as [Query Type] FROM DRUG_CLASS");
}
if (tables.Contains("generic_name", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'Generic Name' as [Query Type] FROM GENERIC_NAME");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'Generic Name' as [Query Type] FROM GENERIC_NAME");
}
}
if (tables.Contains("hcpcs", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'HCPCS' as [Query Type] FROM hcpcs");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'HCPCS' as [Query Type] FROM hcpcs");
}
}
if (tables.Contains("enrollment", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Year), 'Enrollment' as [Query Type] FROM ENROLLMENT");
}
else
{
sqlSB.Append("SELECT DISTINCT(Year), 'Enrollment' as [Query Type] FROM ENROLLMENT");
}
}
if (tables.Contains("icd9_diagnosis", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'ICD9 Diagnosis' as [Query Type] FROM icd9_diagnosis");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'ICD9 Diagnosis' as [Query Type] FROM icd9_diagnosis");
}
}
if (tables.Contains("icd9_diagnosis_4_digit", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'ICD9 Diagnosis 4 Digit' as [Query Type] FROM icd9_diagnosis_4_digit");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'ICD9 Diagnosis 4 Digit' as [Query Type] FROM icd9_diagnosis_4_digit");
}
}
if (tables.Contains("icd9_diagnosis_5_digit", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'ICD9 Diagnosis 5 Digit' as [Query Type] FROM icd9_diagnosis_5_digit");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'ICD9 Diagnosis 5 Digit' as [Query Type] FROM icd9_diagnosis_5_digit");
}
}
if (tables.Contains("icd9_procedure", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'ICD9 Procedure' as [Query Type] FROM icd9_procedure");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'ICD9 Procedure' as [Query Type] FROM icd9_procedure");
}
}
if (tables.Contains("icd9_procedure_4_digit", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'ICD9 Procedure 4 Digit' as [Query Type] FROM icd9_procedure_4_digit");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'ICD9 Procedure 4 Digit' as [Query Type] FROM icd9_procedure_4_digit");
}
}
if (tables.Contains("incident_drug_class", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'Incident Drug Class' as [Query Type] FROM incident_drug_class");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'Incident Drug Class' as [Query Type] FROM incident_drug_class");
}
}
if (tables.Contains("incident_generic_name", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'Incident Generic Name' as [Query Type] FROM incident_generic_name");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'Incident Generic Name' as [Query Type] FROM incident_generic_name");
}
}
if (tables.Contains("incident_icd9_diagnosis", StringComparer.OrdinalIgnoreCase))
{
if (sqlSB.Length > 0)
{
sqlSB.Append(" UNION SELECT DISTINCT(Period), 'Incident ICD9 Diagnosis' as [Query Type] FROM incident_icd9_diagnosis");
}
else
{
sqlSB.Append("SELECT DISTINCT(Period), 'Incident ICD9 Diagnosis' as [Query Type] FROM incident_icd9_diagnosis");
}
}
if (!viewSQL)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sqlSB.ToString();
cmd.CommandTimeout = Convert.ToInt32(_settings.GetSetting("CommandTimeout", 120));
logger.Debug("Executing query:" + Environment.NewLine + sqlSB.ToString());
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(new MetadataRefreshResult
{
Raw_Period = reader.GetString(0),
Period = reader.GetString(0).IndexOf('Q') > 0 ? reader.GetString(0).Substring(0, reader.GetString(0).IndexOf('Q')) : reader.GetString(0),
QueryType = reader.GetString(1)
});
}
}
}
}
else
{
Dictionary <string, object> row = new Dictionary <string, object>();
row.Add("SQL", sqlSB.ToString());
formattedResults.Add(row);
}
}
catch (Exception ex)
{
errors.Add(new DTO.QueryComposer.QueryComposerResponseErrorDTO
{
Code = "999",
Description = ex.UnwindException(true)
});
}
finally
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (!viewSQL)
{
Dictionary <string, object> ageGroupsRow = new Dictionary <string, object>();
ageGroupsRow.Add("DataTable", "Age Group");
ageGroupsRow.Add("DataAvailabilityAnnualFrom", "N/A");
ageGroupsRow.Add("DataAvailabilityAnnualTo", "N/A");
ageGroupsRow.Add("DataAvailabilityQuarterlyFrom", "N/A");
ageGroupsRow.Add("DataAvailabilityQuarterlyTo", "N/A");
formattedResults.Add(ageGroupsRow);
foreach (var res in results.GroupBy(x => x.QueryType))
{
Dictionary <string, object> row = new Dictionary <string, object>();
row.Add("DataTable", res.Key);
row.Add("DataAvailabilityAnnualFrom", res.Min(x => x.Period));
row.Add("DataAvailabilityAnnualTo", res.Max(x => x.Period));
switch (res.Key)
{
case "HCPCS":
case "ICD9 Diagnosis":
case "ICD9 Diagnosis 4 Digit":
case "ICD9 Diagnosis 5 Digit":
case "ICD9 Procedure":
case "ICD9 Procedure 4 Digit":
case "Incident Drug Class":
case "Incident Generic Name":
case "Incident ICD9 Diagnosis":
row.Add("DataAvailabilityQuarterlyFrom", res.Any(x => x.Raw_Period.Contains("Q")) ? res.Where(x => x.Raw_Period.Contains("Q")).Min(x => x.Raw_Period) : "N/A");
row.Add("DataAvailabilityQuarterlyTo", res.Any(x => x.Raw_Period.Contains("Q")) ? res.Where(x => x.Raw_Period.Contains("Q")).Max(x => x.Raw_Period) : "N/A");
break;
case "Enrollment":
case "Generic Name":
case "Drug Class":
row.Add("DataAvailabilityQuarterlyFrom", res.Any(x => x.Raw_Period.Contains("Q")) ? res.Where(x => x.Raw_Period.Contains("Q")).Min(x => x.Raw_Period) : "");
row.Add("DataAvailabilityQuarterlyTo", res.Any(x => x.Raw_Period.Contains("Q")) ? res.Where(x => x.Raw_Period.Contains("Q")).Max(x => x.Raw_Period) : "");
break;
default:
break;
}
formattedResults.Add(row);
}
foreach (var res in results)
{
Dictionary <string, object> row = new Dictionary <string, object>();
row.Add("DataTable", res.QueryType);
row.Add("Period", res.Raw_Period);
unFormattedResults.Add(row);
}
}
DateTimeOffset queryEnd = DateTimeOffset.UtcNow;
var viewableResponse = new QueryComposerResponseQueryResultDTO
{
Errors = errors,
ID = query.Header.ID,
QueryStart = queryStart,
QueryEnd = queryEnd,
LowCellThrehold = _lowThresholdValue,
Results = new[] { formattedResults }
};
var nonViewableResponse = new QueryComposerResponseQueryResultDTO
{
Errors = errors,
ID = query.Header.ID,
QueryStart = queryStart,
QueryEnd = queryEnd,
LowCellThrehold = _lowThresholdValue,
Results = new[] { unFormattedResults }
};
viewableResponse.Properties = GetViewableResponsePropertyDefinitions();
viewableResponse.Aggregation = GetViewableResponseAggregationDefinition();
nonViewableResponse.Properties = GetNonViewableResponsePropertyDefinitions();
nonViewableResponse.Aggregation = GetNonViewableResponseAggregationDefinition();
return(new[] { viewableResponse, nonViewableResponse });
}
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 override SummaryRequestModel ConvertToModel(QueryComposerQueryDTO query)
{
throw new NotImplementedException();
}
public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
{
throw new NotImplementedException();
}
public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
{
throw new NotSupportedException("This model adapter does not support processing a QueryComposerRequestDTO, execute the adapter by calling StartRequest.");
}
public QueryComposerResponseQueryResultDTO Execute(QueryComposerQueryDTO query, IDictionary <string, object> settings, bool viewSQL)
{
var allTerms = query.FlattenToTerms().Where(t => t.Type == ModelTermsFactory.SqlDistributionID).ToArray();
if (allTerms.Length == 0)
{
//error: cannot mix sql distribution with any other term
throw new NotSupportedException("Only a single Sql Distribution term can be specified per request. The term cannot be mixed with other terms.");
}
if (allTerms.Length > 1)
{
//limit to a single sql dist request
throw new NotSupportedException("Only a single Sql Distribution term can be specified per request.");
}
string sql = allTerms[0].GetStringValue("Sql");
var response = new QueryComposerResponseQueryResultDTO {
ID = query.Header.ID, QueryStart = DateTimeOffset.UtcNow
};
if (viewSQL)
{
response.Results = new[] {
new [] {
new Dictionary <string, object>()
{
{ "SQL", sql }
}
}
};
response.Properties = new[] {
new QueryComposerResponsePropertyDefinitionDTO {
Name = "SQL",
Type = "System.String"
}
};
response.QueryEnd = DateTimeOffset.UtcNow;
return(response);
}
List <DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO> columnProperties = new List <QueryComposerResponsePropertyDefinitionDTO>();
List <Dictionary <string, object> > queryResults = new List <Dictionary <string, object> >();
using (var conn = Utilities.OpenConnection(settings, logger, true))
using (var cmd = conn.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();
}
}
response.QueryEnd = DateTimeOffset.UtcNow;
response.Results = new[] { queryResults };
response.Properties = columnProperties;
return(response);
}
protected override SummaryRequestModel ConvertToModel(QueryComposerQueryDTO query)
{
var criteria = query.Where.Criteria.First();
SummaryRequestModel model = new SummaryRequestModel();
//var observationPeriodTerm = criteria.Terms.FirstOrDefault(t => t.Type == ModelTermsFactory.YearID);
var observationPeriodTerm = GetAllCriteriaTerms(criteria, ModelTermsFactory.YearID).FirstOrDefault();
if (observationPeriodTerm != null)
{
model.StartPeriod = observationPeriodTerm.GetStringValue("StartYear");
model.EndPeriod = observationPeriodTerm.GetStringValue("EndYear");
model.Period = string.Join(",", QueryAdapter.ExpandYears(model).Select(y => "'" + y + "'"));//used in query
}
//var codeTerms = criteria.Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.ICD9DiagnosisCodes3digitID)).Concat(criteria.Terms.Where(t => t.Type == ModelTermsFactory.ICD9DiagnosisCodes3digitID));
var codeTerms = GetAllCriteriaTerms(criteria, ModelTermsFactory.ICD9DiagnosisCodes3digitID);
IEnumerable <string> codeTermValues = from t in codeTerms
from v in t.GetCodeStringCollection()
where !string.IsNullOrWhiteSpace(v)
select v.Trim();
model.Codes = string.Join(",", codeTermValues.Distinct());
IEnumerable <string> codeNameValues = from t in codeTerms
from v in t.GetCodeNameStringCollection()
where !string.IsNullOrWhiteSpace(v)
select v.Trim();
model.CodeNames = codeNameValues.Distinct().ToArray();
DTO.Enums.Settings settingValue;
var setting = GetAllCriteriaTerms(criteria, ModelTermsFactory.SettingID).FirstOrDefault();
if (setting.GetEnumValue("Setting", out settingValue))
{
model.Setting = settingValue.ToString();
}
//These values are pulled from the stratification section of the request json
var ageStratification = GetAgeField(query.Select.Fields.Where(f => f.Type == ModelTermsFactory.AgeRangeID));
if (ageStratification != null)
{
QueryAdapter.SetAgeStratification(model, ageStratification);
}
//var sexTerm = criteria.Criteria.Select(y => y.Terms.Where( z => z.Type == ModelTermsFactory.SexID).FirstOrDefault());
var sexStratification = GetAllCriteriaTerms(criteria, ModelTermsFactory.SexID).FirstOrDefault();
if (sexStratification != null)
{
QueryAdapter.SetSexStratification(model, sexStratification);
}
model.Coverage = null; //not applicable to this query
model.MetricType = "0"; //not applicable to this query
model.OutputCriteria = "0"; //not applicable to this query
model.StartQuarter = null; //not applicable to this query
model.EndQuarter = null; //not applicable to this query
model.SubtypeId = 0; //value never gets set in ui of v5 summary query composer
return(model);
}
