static private DataSet GetQueryResults(IDnsResponseContext context, bool ExpandResults, DataSet ds)
{
DataSetHelper dsHelper = new DataSetHelper(ref ds);
//string GenderColumnName =(ds.Tables[0].Columns.Contains("gender"))? "gender Sex" : "Sex";
//string SortColumnName = (ds.Tables[0].Columns.Contains("gender"))? "gender" : "Sex";
SummaryRequestType requestType = SummaryRequestType.All.FirstOrDefault(rt => rt.ID == context.Request.RequestType.ID);
GroupResults(ref ds, requestType);
string aggregatedXmlString = ds.GetXml();
//reading the xml in to a dataset does not allow spaces for the columns. So replace with apprpriate hex values.
aggregatedXmlString = aggregatedXmlString.Replace("Total_Enrollment_in_Strata_Members", "Total_x0020_Enrollment_x0020_in_x0020_Strata_x0028_Members_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Covered", "Days_x0020_Covered");
aggregatedXmlString = aggregatedXmlString.Replace("Prevalence_Rate_Users_per_1000_enrollees", "Prevalence_x0020_Rate_x0020__x0028_Users_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Dispensing_Rate_Dispensings_per_1000_enrollees", "Dispensing_x0020_Rate_x0020__x0028_Dispensings_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Per_Dispensing", "Days_x0020_Per_x0020_Dispensing");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Per_user", "Days_x0020_Per_x0020_user");
aggregatedXmlString = aggregatedXmlString.Replace("Event_Rate_Events_per_1000_enrollees", "Event_x0020_Rate_x0020__x0028_Events_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Events_Per_member", "Events_x0020_Per_x0020_member");
ds = new DataSet();
StringReader sr = new StringReader(aggregatedXmlString);
ds.ReadXml(sr);
#region "Expand the result to contain all combinations of Period,AgeGroup,Sex,Setting,Code"
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
string summaryRequestArgsXml = "";
if (ExpandResults)
{
DataSet dsExpandedResult = new DataSet();
if (ds.Tables.Count > 0)
{
DataTable dtMerged = QueryUtil.ExpandSummaryResults(ds.Tables[0], summaryRequestArgsXml, context);
if (dtMerged != null && dtMerged.Rows.Count > 0)
{
dsExpandedResult.Tables.Clear();
dsExpandedResult.Tables.Add(dtMerged);
ds = dsExpandedResult;
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
//AddComputedColumnsIfNotExist(q, ref ds);
}
}
}
#endregion
return(ds);
}
//[Import] public static IRepository<SummaryDomain, StratificationAgeRangeMapping> StratificationAgeRanges { get; set; }
//[Import] public static IRepository<SummaryDomain, LookupListValue> LookupListValues { get; set; }
private static DataTable GenerateSummaryQueryResultCombinations(string summaryRequestArgsXml, IDnsResponseContext context)
{
SummaryRequestType requestType = SummaryRequestType.All.FirstOrDefault(rt => rt.ID == context.Request.RequestType.ID);
DataTable dt = new DataTable("Results");
if (!string.IsNullOrEmpty(summaryRequestArgsXml))
{
DataColumn col;
DataRow dr;
string PeriodColumn = "Period";
string SettingColumn = "Setting";
string SexColumn = "Sex";
string AgeColumn = "AgeGroup";
string CodeColumn = string.Empty;
string NameColumn = string.Empty;
bool IsQualifiedForExpansion = false;
bool IsDrugOrGenericName = false;
Lists? CodeList = null;
col = new DataColumn(PeriodColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(SexColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(AgeColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(SettingColumn, "string".GetType());
dt.Columns.Add(col);
#region "Read Query.XML and populate rows"
XmlDataDocument summaryRequestArgsDoc = new XmlDataDocument();
summaryRequestArgsDoc.LoadXml(summaryRequestArgsXml);
XPathNavigator Nav = summaryRequestArgsDoc.CreateNavigator();
XPathExpression Expr;
XPathNodeIterator INode = null;
string nodeValue = string.Empty;
string[] Years = new string[] { };
IEnumerable <StratificationAgeRangeMapping> SelectedStratificationAgeRanges = null;
List <string> Genders = new List <string>();
List <string> Settings = new List <string>();
string[] Codes = new string[] { };
List <string> Names = new List <string>();
switch (requestType.StringId)
{
case SummaryRequestType.GenericName:
case SummaryRequestType.Incident_GenericName:
CodeList = Lists.GenericName;
break;
case SummaryRequestType.DrugClass:
case SummaryRequestType.Incident_DrugClass:
CodeList = Lists.DrugClass;
break;
//case SummaryRequestType.NDC:
// CodeList = Lists.DrugCode;
// break;
case SummaryRequestType.ICD9Diagnosis:
case SummaryRequestType.Incident_ICD9Diagnosis:
CodeList = Lists.ICD9Diagnosis;
break;
case SummaryRequestType.ICD9Diagnosis_4_digit:
CodeList = Lists.ICD9Diagnosis4Digits;
break;
case SummaryRequestType.ICD9Diagnosis_5_digit:
CodeList = Lists.ICD9Diagnosis5Digits;
break;
case SummaryRequestType.ICD9Procedures:
CodeList = Lists.ICD9Procedures;
break;
case SummaryRequestType.ICD9Procedures_4_digit:
CodeList = Lists.ICD9Procedures4Digits;
break;
case SummaryRequestType.HCPCSProcedures:
CodeList = Lists.HCPCSProcedures;
break;
}
#region "Get Names for code column e.g. pxcode,dxcode,drugclass,etc..and name column e.g. pxname,dxname,etc.."
//The Generation of Combination Rows and Subsequent Expansion Of results applicable only to Prevalence,Incidence Queries.
switch (requestType.StringId)
{
case SummaryRequestType.ICD9Diagnosis:
case SummaryRequestType.ICD9Diagnosis_4_digit:
case SummaryRequestType.ICD9Diagnosis_5_digit:
case SummaryRequestType.Incident_ICD9Diagnosis:
CodeColumn = "DxCode";
NameColumn = "DxName";
IsQualifiedForExpansion = true;
break;
case SummaryRequestType.ICD9Procedures:
case SummaryRequestType.ICD9Procedures_4_digit:
case SummaryRequestType.HCPCSProcedures:
CodeColumn = "PxCode";
NameColumn = "PxName";
IsQualifiedForExpansion = true;
break;
case SummaryRequestType.GenericName:
case SummaryRequestType.Incident_GenericName:
CodeColumn = string.Empty;
NameColumn = "GenericName";
IsQualifiedForExpansion = true;
IsDrugOrGenericName = true;
break;
case SummaryRequestType.DrugClass:
case SummaryRequestType.Incident_DrugClass:
CodeColumn = string.Empty;
NameColumn = "DrugClass";
IsQualifiedForExpansion = true;
IsDrugOrGenericName = true;
break;
}
#endregion
if (IsQualifiedForExpansion)
{
if (!string.IsNullOrEmpty(CodeColumn))
{
col = new DataColumn(CodeColumn, "string".GetType());
dt.Columns.Add(col);
}
if (!string.IsNullOrEmpty(NameColumn))
{
col = new DataColumn(NameColumn, "string".GetType());
dt.Columns.Add(col);
}
//Read Selected Periods e.g. 2016,2017, etc.
Expr = Nav.Compile("SummaryRequestModel/Period");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
// The Period node contains a list of SINGLE-QUOTED years or quarters (for direct insertion into a SQL statement).
// We need to remove the quotes, along with any whitespace, for our purposes here.
nodeValue = INode.Current.Value.Replace("'", "").Replace(" ", "");
if (!string.IsNullOrEmpty(nodeValue))
{
Years = nodeValue.Split(',');
}
}
//Read Selected Sex stratifications
Expr = Nav.Compile("SummaryRequestModel/SexStratification");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
if (!string.IsNullOrEmpty(nodeValue))
{
switch (nodeValue)
{
case "1": //Female only
Genders.Add("F");
break;
case "2": //Male only
Genders.Add("M");
break;
case "3": //Male and Female
Genders.Add("F");
Genders.Add("M");
break;
case "4": //Male,Female Aggregated
Genders.Add("All");
break;
}
}
}
//Read Selected Age stratifications
Expr = Nav.Compile("SummaryRequestModel/AgeStratification");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
int ageStratificationCategoryId;
if (Int32.TryParse(nodeValue, out ageStratificationCategoryId))
{
throw new Lpp.Utilities.CodeToBeUpdatedException();
//SelectedStratificationAgeRanges = StratificationAgeRanges.All.Where(s => s.AgeStratificationCategoryId == ageStratificationCategoryId).ToList();
}
}
//Read Selected setting
Expr = Nav.Compile("SummaryRequestModel/Setting");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
if (!string.IsNullOrEmpty(nodeValue))
{
//The value 'NotSpecified' means no setting selected i.e. all settings considerred.
if (nodeValue.Trim().ToLower() != "notspecified")
{
Settings.Add(nodeValue);
}
else
{
Settings.Add("All Settings");
}
}
}
//Read Selected Codes and their names.
if (CodeList != null)
{
Expr = Nav.Compile("SummaryRequestModel/Codes");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
// Remove any spaces in the comma-delimited list of codes.
nodeValue = INode.Current.Value.Replace(" ", "");
if (!string.IsNullOrEmpty(nodeValue))
{
Codes = nodeValue.Split(',');
}
// Look up the names from the codes.
LookupListValue matchingNames;
foreach (string code in Codes)
{
// TODO: Verify this works properly if there's no match.
throw new Lpp.Utilities.CodeToBeUpdatedException();
//matchingNames = LookupListValues.All.FirstOrDefault(v => v.ListId == (int)CodeList && v.ItemCode == code);
//if (matchingNames != null)
//{
// Names.Add(matchingNames.ItemName);
//}
//else
//{
// Names.Add(string.Empty);
//}
}
}
}
//Create Combination Rows based on selected Period,AgeGroup,Sex,Setting and Codes.
if (Years.Length > 0 && SelectedStratificationAgeRanges != null && SelectedStratificationAgeRanges.Count() > 0 && Genders.Count > 0 && Settings.Count > 0)
{
foreach (string year in Years)
{
foreach (string sex in Genders)
{
foreach (StratificationAgeRangeMapping ageRange in SelectedStratificationAgeRanges)
{
foreach (string setting in Settings)
{
//If Code exists for Non-drug/generic Prevalence/Incidence queries.
if (Codes.Length > 0)
{
for (int ctr = 0; ctr < Codes.Length; ctr++)
{
dr = dt.NewRow();
dr[PeriodColumn] = year;
dr[SexColumn] = sex;
dr[AgeColumn] = ageRange.AgeClassification;
dr[SettingColumn] = setting;
dr[CodeColumn] = Codes[ctr];
if (Names.Count > ctr)
{
dr[NameColumn] = Names[ctr];
}
dt.Rows.Add(dr);
}
}
else
{
//Code does not exist for Non-drug/generic Prevalence/Incidence queries.
for (int ctr = 0; ctr < Names.Count; ctr++)
{
dr = dt.NewRow();
dr[PeriodColumn] = year;
dr[SexColumn] = sex;
dr[AgeColumn] = ageRange.AgeClassification;
dr[SettingColumn] = setting;
if (Codes.Length > ctr)
{
dr[CodeColumn] = Codes[ctr];
}
dr[NameColumn] = Names[ctr];
dt.Rows.Add(dr);
}
}
}
}
}
}
}
//For Drug or Generic Query No need of Settings Columns.
if (IsDrugOrGenericName)
{
if (dt.Columns.Contains(SettingColumn))
{
dt.Columns.Remove(SettingColumn);
}
}
}
#endregion
}
return(dt);
}
private static void GroupResults(ref DataSet ds, SummaryRequestType requestType)
{
DataSetHelper dsHelper = new DataSetHelper(ref ds);
string GenderColumnName = (ds.Tables[0].Columns.Contains("gender")) ? "gender Sex" : "Sex";
string SortColumnName = (ds.Tables[0].Columns.Contains("gender")) ? "gender" : "Sex";
switch (requestType.StringId)
{
case SummaryRequestType.GenericName:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, GenericName,sum(Dispensings) Dispensings, sum(Members) Members, sum(DaysSupply) DaysSupply, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, avg(Prevalence_Rate_Users_per_1000_enrollees) Prevalence_Rate_Users_per_1000_enrollees,avg(Dispensing_Rate_Dispensings_per_1000_enrollees) Dispensing_Rate_Dispensings_per_1000_enrollees, avg(Days_Per_Dispensing) Days_Per_Dispensing, avg(Days_Per_user) Days_Per_user", "", "AgeGroup, Sex, Period,GenericName");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.Incident_GenericName:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, GenericName,sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, " +
"sum(Members90) Members90, sum(Dispensings90) Dispensings90, sum(DaysSupply90) DaysSupply90, sum(Members90Q1) Members90Q1, sum(Members90Q2) Members90Q2, sum(Members90Q3) Members90Q3, sum(Members90Q4) Members90Q4, " +
"sum(Members180) Members180, sum(Dispensings180) Dispensings180, sum(DaysSupply180) DaysSupply180, sum(Members180Q1) Members180Q1, sum(Members180Q2) Members180Q2, sum(Members180Q3) Members180Q3, sum(Members180Q4) Members180Q4, " +
"sum(Members270) Members270, sum(Dispensings270) Dispensings270, sum(DaysSupply270) DaysSupply270, sum(Members270Q1) Members270Q1, sum(Members270Q2) Members270Q2, sum(Members270Q3) Members270Q3, sum(Members270Q4) Members270Q4", "", "AgeGroup, Sex, Period,GenericName");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.DrugClass:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, DrugClass,sum(Dispensings) Dispensings, sum(Members) Members, sum(DaysSupply) DaysSupply, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, avg(Prevalence_Rate_Users_per_1000_enrollees) Prevalence_Rate_Users_per_1000_enrollees,avg(Dispensing_Rate_Dispensings_per_1000_enrollees) Dispensing_Rate_Dispensings_per_1000_enrollees, avg(Days_Per_Dispensing) Days_Per_Dispensing, avg(Days_Per_user) Days_Per_user", "", "AgeGroup, Sex, Period,DrugClass");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.Incident_DrugClass:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, DrugClass,sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, " +
"sum(Members90) Members90, sum(Dispensings90) Dispensings90, sum(DaysSupply90) DaysSupply90, sum(Members90Q1) Members90Q1, sum(Members90Q2) Members90Q2, sum(Members90Q3) Members90Q3, sum(Members90Q4) Members90Q4, " +
"sum(Members180) Members180, sum(Dispensings180) Dispensings180, sum(DaysSupply180) DaysSupply180, sum(Members180Q1) Members180Q1, sum(Members180Q2) Members180Q2, sum(Members180Q3) Members180Q3, sum(Members180Q4) Members180Q4, " +
"sum(Members270) Members270, sum(Dispensings270) Dispensings270, sum(DaysSupply270) DaysSupply270, sum(Members270Q1) Members270Q1, sum(Members270Q2) Members270Q2, sum(Members270Q3) Members270Q3, sum(Members270Q4) Members270Q4", "", "AgeGroup, Sex, Period,DrugClass");
ds.Tables.RemoveAt(0);
break;
//case SummaryRequestType.NDC:
// dsHelper.SelectGroupByInto("Results", ds.Tables[0], "GenericName,_NDC,sum(dispensing) dispensing,sum(Members) Members", "", "GenericName,_NDC");
// ds.Tables.RemoveAt(0);
// break;
case SummaryRequestType.ICD9Diagnosis:
case SummaryRequestType.ICD9Diagnosis_4_digit:
case SummaryRequestType.ICD9Diagnosis_5_digit:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup,Sex, Period, DXCode, DXName, Setting, sum(Events) Events, sum(Members) Members, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, avg(Prevalence_Rate_Users_per_1000_enrollees) Prevalence_Rate_Users_per_1000_enrollees, avg(Event_Rate_Events_per_1000_enrollees) Event_Rate_Events_per_1000_enrollees, avg(Events_Per_member) Events_Per_member", "", "AgeGroup, Sex,Period, DXCode, DXName, Setting");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.Incident_ICD9Diagnosis:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup,Sex, Period, DXCode, DXName, Setting, sum(Members90) Members90, sum(Members180) Members180, sum(Members270) Members270, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered", "", "AgeGroup, Sex,Period, DXCode, DXName, Setting");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.ICD9Procedures:
case SummaryRequestType.ICD9Procedures_4_digit:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, PXCode, PXName, Setting, sum(Events) Events, sum(Members) Members, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, avg(Prevalence_Rate_Users_per_1000_enrollees) Prevalence_Rate_Users_per_1000_enrollees, avg(Event_Rate_Events_per_1000_enrollees) Event_Rate_Events_per_1000_enrollees, avg(Events_Per_member) Events_Per_member", "", "AgeGroup, Sex, Period, PXCode, PXName, Setting");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.HCPCSProcedures:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, Period, PXCode, PXName, Setting, sum(Events) Events, sum(Members) Members, sum(Total_Enrollment_in_Strata_Members) Total_Enrollment_in_Strata_Members, sum(Days_Covered) Days_Covered, avg(Prevalence_Rate_Users_per_1000_enrollees) Prevalence_Rate_Users_per_1000_enrollees, avg(Event_Rate_Events_per_1000_enrollees) Event_Rate_Events_per_1000_enrollees, avg(Events_Per_member) Events_Per_member", "", "AgeGroup, Sex, Period, PXCode, PXName, Setting");
ds.Tables.RemoveAt(0);
break;
case SummaryRequestType.EligibilityAndEnrollment:
dsHelper.SelectGroupByInto("Results", ds.Tables[0], "AgeGroup, Sex, DrugCoverage, MedicalCoverage, Year, sum(Members) Members", "", "AgeGroup, Sex, DrugCoverage, MedicalCoverage, Year");
ds.Tables.RemoveAt(0);
break;
default:
break;
}
}
