public override QueryComposerResponseDTO Execute(QueryComposerRequestDTO request, bool viewSQL)
{
SummarySqlQueryAdapter sql = new SummarySqlQueryAdapter();
var result = sql.Execute(request, _settings, viewSQL);
return(result);
}
public JsonResult GetTermValues(Guid?requestID)
{
if (requestID == null)
{
return(Json(null, JsonRequestBehavior.AllowGet));
}
using (var db = new DataContext())
{
Guid termID = Lpp.QueryComposer.ModelTermsFactory.DC_DiagnosisCodes;
var req = db.Requests.Find(requestID);
QueryComposerRequestDTO dto = Newtonsoft.Json.JsonConvert.DeserializeObject <QueryComposerRequestDTO>(req.Query);
var criteria = dto.Queries.First().Where.Criteria.Where(c => c.Terms.Any(t => t.Type == termID)).FirstOrDefault();
var term = criteria.Terms.First(t => t.Type == termID);
var termValues = term.Values.First(p => p.Key == "Values");
DiagnosisValues val = Newtonsoft.Json.JsonConvert.DeserializeObject <DiagnosisValues>(termValues.Value.ToString());
string[] arrSplit = { "," };
string[] arrVal = val.CodeValues.Split(arrSplit, StringSplitOptions.RemoveEmptyEntries);
return(Json(arrVal, JsonRequestBehavior.AllowGet));
}
}
private QueryComposerResponseDTO ExecuteRequest(string json, Dictionary <string, object> settings)
{
QueryComposerRequestDTO dto = Newtonsoft.Json.JsonConvert.DeserializeObject <DTO.QueryComposer.QueryComposerRequestDTO>(json);
//The connection string to the Adapter's database.
Adapters.PCORI.PCORIModelAdapter pcori = new Adapters.PCORI.PCORIModelAdapter();
pcori.Initialize(settings);
//Execute the query
return(pcori.Execute(dto, false));
}
private QueryComposerResponseDTO ExecuteRequest(string json, Dictionary <string, object> settings)
{
QueryComposerRequestDTO dto = Newtonsoft.Json.JsonConvert.DeserializeObject <DTO.QueryComposer.QueryComposerRequestDTO>(json);
//The connection string to the Adapter's database.
Adapters.PCORI.PCORIModelAdapter pcori = new Adapters.PCORI.PCORIModelAdapter(new RequestMetadata
{
CreatedOn = DateTime.UtcNow,
MSRequestID = "Unit Test Request"
});
pcori.Initialize(settings, Guid.NewGuid().ToString("D"));
//Execute the query
return(pcori.Execute(dto, false));
}
public JsonResult GetRxAmounts(Guid?requestID)
{
if (requestID == null)
{
return(Json(null, JsonRequestBehavior.AllowGet));
}
using (var db = new DataContext())
{
var req = db.Requests.Find(requestID);
QueryComposerRequestDTO dto = Newtonsoft.Json.JsonConvert.DeserializeObject <QueryComposerRequestDTO>(req.Query);
var criteria = dto.Queries.First().Where.Criteria.Where(c => c.Terms.Any(t => t.Type == Lpp.QueryComposer.ModelTermsFactory.DC_DispensingRXAmount)).FirstOrDefault();
var term = criteria.Terms.First(t => t.Type == Lpp.QueryComposer.ModelTermsFactory.DC_DispensingRXAmount);
var termValues = term.Values.First(p => p.Key == "Values");
DispensingRXAmountValues val = Newtonsoft.Json.JsonConvert.DeserializeObject <DispensingRXAmountValues>(termValues.Value.ToString());
return(Json(val.RXAmounts.ToArray(), JsonRequestBehavior.AllowGet));
}
}
QueryComposerResponseDTO RunQueryAgainstAdapter(string jsonSrc, string connectionString = null)
{
var json = System.IO.File.ReadAllText(jsonSrc);
Newtonsoft.Json.JsonSerializerSettings jsonSettings = new Newtonsoft.Json.JsonSerializerSettings();
jsonSettings.DefaultValueHandling = Newtonsoft.Json.DefaultValueHandling.IgnoreAndPopulate;
QueryComposerRequestDTO request = Newtonsoft.Json.JsonConvert.DeserializeObject <Lpp.Dns.DTO.QueryComposer.QueryComposerRequestDTO>(json, jsonSettings);
if (string.IsNullOrEmpty(connectionString))
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PCORNET"].ConnectionString;
}
using (var adapter = Helper.CreatePCORIModelAdapterAdapter(connectionString))
{
return(adapter.Execute(request, false));
}
}
public void QueryComposer_BuildRequest()
{
QueryComposerRequestDTO request = new QueryComposerRequestDTO
{
ID = Guid.NewGuid(),
Timestamp = BitConverter.GetBytes(DateTime.UtcNow.Ticks),
Header = new QueryComposerHeaderDTO
{
Name = "Example Request",
Description = "This is a unit test request."
},
Where = new QueryComposerWhereDTO
{
Criteria = new QueryComposerCriteriaDTO[]
{
new QueryComposerCriteriaDTO
{
Name = "Group 1",
Operator = DTO.Enums.QueryComposerOperators.And,
Exclusion = false,
Terms = new QueryComposerTermDTO[]
{
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.Or,
Type = Lpp.QueryComposer.ModelTermsFactory.SexID,
Values = new Dictionary <string, object> {
{ "Gender", 0 }
}
}
}
}
}
}
};
var serialized = Newtonsoft.Json.JsonConvert.SerializeObject(request, Newtonsoft.Json.Formatting.Indented);
Console.WriteLine(serialized);
}
protected override SummaryRequestModel ConvertToModel(QueryComposerRequestDTO request)
{
throw new NotImplementedException();
}
QueryComposerRequestDTO Create_ESP_1_WithExclusion()
{
QueryComposerRequestDTO request = new QueryComposerRequestDTO
{
ID = Guid.NewGuid(),
Timestamp = BitConverter.GetBytes(DateTime.UtcNow.Ticks),
Header = new QueryComposerHeaderDTO
{
Name = "Example Request",
Description = "This is a unit test request."
},
Where = new QueryComposerWhereDTO
{
Criteria = new QueryComposerCriteriaDTO[]
{
new QueryComposerCriteriaDTO
{
Name = "Group 1",
Operator = DTO.Enums.QueryComposerOperators.And,
Exclusion = false,
Terms = new QueryComposerTermDTO[]
{
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.AgeRangeID,
Values = new Dictionary <string, object> {
{ "MinAge", 0 }, { "MaxAge", 65 }
}
},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.VisitsID,
Values = new Dictionary <string, object> {
{ "Visits", 2 }
}
},
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.GenderID,
// Values = new Dictionary<string,object>{ { "Gender", "Male and Female" } }
//},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.ICD9DiagnosisCodes3digitID,
//Values = new Dictionary<string,object>{ { "Codes", new List<string>{"172", "079", "250"} } }
//Values = new Dictionary<string,object>{ { "Codes", "172, 079 ,250"} }
Values = new Dictionary <string, object> {
{ "Codes", "172" }
}
},
//Not sure how condictions is going to work since it is being implemented against an enum
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.ConditionsID,
// Values = new Dictionary<string,object>{ { "Condition", 1 } }
//},
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.ZipCodeID,
// Values = new Dictionary<string,object>{ { "Codes", "02125,02127,02211"} }
//},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.ObservationPeriodID,
Values = new Dictionary <string, object> {
{ "Start", "2008-01-01" }, { "End", "2008-01-01" }
}
},
},
//ObservationPeriod = new QueryComposerPeriodDTO{ Start = new DateTimeOffset(new DateTime(2008,1,1)), End = DateTimeOffset.Now }
},
new QueryComposerCriteriaDTO
{
Name = "Exclusion Group",
Operator = DTO.Enums.QueryComposerOperators.And,
Exclusion = true,
Terms = new QueryComposerTermDTO[]
{
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.AgeRangeID,
// Values = new Dictionary<string,object>{ {"MinAge", 5}, {"MaxAge", 15 } }
//},
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.VisitsID,
// Values = new Dictionary<string,object>{ { "Visits", 5 } }
//},
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.GenderID,
// Values = new Dictionary<string,object>{ { "Gender", "Male and Female" } }
//},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.ICD9DiagnosisCodes3digitID,
//Values = new Dictionary<string,object>{ { "Codes", new List<string>{"172", "079", "250"} } }
//Values = new Dictionary<string,object>{ { "Codes", "172, 079 ,250"} }
Values = new Dictionary <string, object> {
{ "Codes", "179" }
}
},
//Not sure how condictions is going to work since it is being implemented against an enum
//new QueryComposerTermDTO
//{
// Operator = DTO.Enums.QueryComposerOperators.And,
// Type = Lpp.QueryComposer.ModelTermsFactory.ConditionsID,
// Values = new Dictionary<string,object>{ { "Condition", 1 } }
//},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.ZipCodeID,
Values = new Dictionary <string, object> {
{ "Codes", "02125,02127,02211" }
}
},
new QueryComposerTermDTO
{
Operator = DTO.Enums.QueryComposerOperators.And,
Type = Lpp.QueryComposer.ModelTermsFactory.ObservationPeriodID,
Values = new Dictionary <string, object> {
{ "Start", "2009-01-01" }, { "End", "2011-01-01" }
}
},
},
//ObservationPeriod = new QueryComposerPeriodDTO{ Start = new DateTimeOffset(new DateTime(2009,1,1)), End = new DateTimeOffset(new DateTime(2011,1,1)) }
}
}
}
};
return(request);
}
public override QueryComposerResponseDTO Execute(QueryComposerRequestDTO request, 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>();
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);
}
}
var viewableResponse = new DTO.QueryComposer.QueryComposerResponseDTO
{
Errors = errors,
RequestID = request.ID.HasValue ? request.ID.Value : default(Guid),
ResponseDateTime = DateTime.UtcNow,
Results = new[] { formattedResults }
};
var nonViewableResponse = new DTO.QueryComposer.QueryComposerResponseDTO
{
Errors = errors,
RequestID = request.ID.HasValue ? request.ID.Value : default(Guid),
ResponseDateTime = DateTime.UtcNow,
Results = new[] { unFormattedResults }
};
viewableResponse.Properties = GetViewableResponsePropertyDefinitions();
viewableResponse.Aggregation = GetViewableResponseAggregationDefinition();
nonViewableResponse.Properties = GetNonViewableResponsePropertyDefinitions();
nonViewableResponse.Aggregation = GetNonViewableResponseAggregationDefinition();
_currentViewableResponse = viewableResponse;
_currentNonViewableResponse = nonViewableResponse;
return(_currentViewableResponse);
}
public override QueryComposerResponseDTO Execute(QueryComposerRequestDTO request, bool viewSQL)
{
throw new NotImplementedException();
}
public override QueryComposerResponseDTO Execute(QueryComposerRequestDTO request, 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 (request.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 = request.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.SqlDistributionID)).Any();
if (hasSQLTerm)
{
if (request.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 = request.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>();
List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors = new List <DTO.QueryComposer.QueryComposerResponseErrorDTO>();
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();
}
}
var results = new QueryComposerResponseDTO
{
Errors = errors,
RequestID = request.ID.HasValue ? request.ID.Value : default(Guid),
ResponseDateTime = DateTime.UtcNow,
Results = new[] { queryResults },
Properties = columnProperties
};
Guid requestID;
if (results.RequestID == default(Guid) && Guid.TryParse(_requestId, out requestID))
{
results.RequestID = requestID;
}
_currentResponse = results;
return(results);
}
else
{
bool hasConditionsTerm = request.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ConditionsID)).Any();
bool hasICD9CodesTerm = request.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ICD9DiagnosisCodes3digitID)).Any();
var firstCriteria = request.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 query = 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)
{
query = from o in query
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)
{
query = from o in query
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)
{
query = from o in query
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)
{
query = from o in query
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 request.Where.Criteria)
{
if (!primaryCriteria && criteria.Exclusion)
{
//exclusion is a not exists in the where clause
var subQuery = ApplyCriteria(criteria);
query = query.Where(q => !subQuery.Where(s => s.PatID == q.PatientID).Any());
}
else if (!primaryCriteria)
{
var subQuery = ApplyCriteria(criteria);
query = query.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)
{
query = query.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())
{
query = query.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)
{
query = query.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
{
query = query.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())
{
query = query.Where(q => zipCodes.Contains(q.Code));
}
}
else
{
logger.Debug("Term specified but not implemented in criteria: " + termID);
}
}
if (icdDiseasePredicate != null)
{
query = query.Where(icdDiseasePredicate);
}
}
}
query = query.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
* */
List <Dictionary <string, object> > results = new List <Dictionary <string, object> >();
if (!viewSQL)
{
logger.Debug(query.ToString());
foreach (var item in query)
{
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", query.ToString());
results.Add(row);
}
logger.Debug("Number of results found:" + results.Count);
QueryComposerResponseDTO response = new QueryComposerResponseDTO
{
ResponseDateTime = DateTime.UtcNow,
Results = new[] { results }
};
if (request.ID.HasValue)
{
response.RequestID = request.ID.Value;
}
else
{
Guid requestID;
if (Guid.TryParse(_requestId, out requestID))
{
response.RequestID = requestID;
}
_currentResponse = response;
}
return(response);
}
}
public static IEnumerable <QueryComposerTermDTO> GetSQLDistributionTerms(QueryComposerRequestDTO request)
{
return(request.Where.Criteria.SelectMany(c => c.Criteria.SelectMany(cc => cc.Terms)).Concat(request.Where.Criteria.SelectMany(c => c.Terms)).Where(t => t.Type == ModelTermsFactory.SqlDistributionID).ToArray());
}
