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; } }