protected override SummaryRequestModel ConvertToModel(DTO.QueryComposer.QueryComposerRequestDTO request) { var criteria = request.Where.Criteria.First(); SummaryRequestModel model = new SummaryRequestModel(); //var observationPeriodTerm = criteria.Terms.FirstOrDefault(t => t.Type == ModelTermsFactory.QuarterYearID); var observationPeriodTerm = GetAllCriteriaTerms(criteria, ModelTermsFactory.QuarterYearID).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 } IEnumerable <DTO.QueryComposer.QueryComposerTermDTO> codeTerms = criteria.Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.DrugClassID || t.Type == ModelTermsFactory.DrugNameID)).Concat(criteria.Terms.Where(t => t.Type == ModelTermsFactory.DrugClassID || t.Type == ModelTermsFactory.DrugNameID)).ToArray(); if (codeTerms.Any(t => t.Type == ModelTermsFactory.DrugNameID)) { _queryType = IncidencePharmaDispensingQueryType.DrugName; } else if (codeTerms.Any(t => t.Type == ModelTermsFactory.DrugClassID)) { _queryType = IncidencePharmaDispensingQueryType.DrugClass; } else { throw new InvalidOperationException("Either a Drug Name term or a Drug Class term is required for the query."); } 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().Select(c => System.Net.WebUtility.HtmlEncode(c).Replace(",", ","))); model.CodeNames = null;//not applicable to this query //These values are pulled from the stratification section of the request json var ageStratification = request.Select.Fields.Where(f => f.Type == ModelTermsFactory.AgeRangeID).FirstOrDefault(); if (ageStratification != null) { QueryAdapter.SetAgeStratification(model, ageStratification); } var sexStratification = GetAllCriteriaTerms(criteria, ModelTermsFactory.SexID).FirstOrDefault(); if (sexStratification != null) { QueryAdapter.SetSexStratification(model, sexStratification); } model.Setting = null; //not applicable to this query 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); }
protected override SummaryRequestModel ConvertToModel(DTO.QueryComposer.QueryComposerQueryDTO query) { var criteria = query.Where.Criteria.First(); SummaryRequestModel model = new SummaryRequestModel(); var observationPeriodTerm = GetAllCriteriaTerms(criteria, ModelTermsFactory.QuarterYearID).FirstOrDefault(); if (observationPeriodTerm != null) { model.StartPeriod = observationPeriodTerm.GetStringValue("StartYear"); model.EndPeriod = observationPeriodTerm.GetStringValue("EndYear"); if (string.Equals(observationPeriodTerm.GetStringValue("ByYearsOrQuarters"), "ByQuarters", StringComparison.OrdinalIgnoreCase)) { model.StartQuarter = observationPeriodTerm.GetStringValue("StartQuarter"); model.EndQuarter = observationPeriodTerm.GetStringValue("EndQuarter"); model.Period = string.Join(",", QueryAdapter.ExpandYearsWithQuarters(Convert.ToInt32(model.StartPeriod), Convert.ToInt32(model.StartQuarter[1].ToString()), Convert.ToInt32(model.EndPeriod), Convert.ToInt32(model.EndQuarter[1].ToString())).Select(y => "'" + y + "'"));//used in query } else { model.StartQuarter = null; model.EndQuarter = null; model.Period = string.Join(",", QueryAdapter.ExpandYears(model).Select(y => "'" + y + "'"));//used in query } } var codeTerms = criteria.FlattenCriteriaToTerms().Where(t => t.Type == ModelTermsFactory.DrugNameID || t.Type == ModelTermsFactory.DrugClassID); if (codeTerms.Any(t => t.Type == ModelTermsFactory.DrugNameID)) { _queryType = IncidencePharmaDispensingQueryType.DrugName; } else if (codeTerms.Any(t => t.Type == ModelTermsFactory.DrugClassID)) { _queryType = IncidencePharmaDispensingQueryType.DrugClass; } else { throw new InvalidOperationException("Either a Drug Name term or a Drug Class term is required for the query."); } var codeTermValues = (from t in codeTerms let v = t.GetCodeSelectorValues() from c in v where c != null && !string.IsNullOrWhiteSpace(c.Code) select c).GroupBy(k => k.Code.Trim()).Select(k => new { Code = k.Key, Name = k.Select(c => c.Name).FirstOrDefault() ?? k.Key }).ToArray(); model.Codes = string.Join(",", codeTermValues.Select(c => System.Net.WebUtility.HtmlEncode(c.Code).Replace(",", ","))); model.CodeNames = codeTermValues.Select(c => c.Name).ToArray(); //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 sexStratification = GetAllCriteriaTerms(criteria, ModelTermsFactory.SexID).FirstOrDefault(); if (sexStratification != null) { QueryAdapter.SetSexStratification(model, sexStratification); } model.Setting = null; //not applicable to this query 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.SubtypeId = 0; //value never gets set in ui of v5 summary query composer return(model); }
protected override SummaryRequestModel ConvertToModel(DTO.QueryComposer.QueryComposerQueryDTO query) { var criteria = query.Where.Criteria.First(); SummaryRequestModel model = new SummaryRequestModel(); 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.HCPCSProcedureCodesID)).Concat(criteria.Terms.Where(t => t.Type == ModelTermsFactory.HCPCSProcedureCodesID)); 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 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); }
/// <summary> /// Expands the range of years of the SummaryRequestModel.StartPeriod to SummaryRequestModel.EndPeriod to an enumerable collection of year values. /// </summary> /// <param name="args"></param> /// <returns></returns> protected static IEnumerable <int> ExpandYears(SummaryRequestModel args) { return(ExpandYears(Convert.ToInt32(args.StartPeriod), Convert.ToInt32(args.EndPeriod))); }
/// <summary> /// Parses the Codes property of the ars by splitting on ',' and trimming. /// Returns only distinct non-whitespace values as an enumerable string collection. /// </summary> /// <param name="args">The query args.</param> /// <param name="replaceDecimals">Indicates if decimals should be replaced with empty string, default is false.</param> /// <returns></returns> protected static IEnumerable <string> ParseCodeValues(SummaryRequestModel args, bool replaceDecimals = false, bool htmlDecode = false) { return((from c in args.Codes.Split(',').Select(p => htmlDecode ? System.Net.WebUtility.HtmlDecode(p.Trim().Replace(",", ",")) : p.Trim()) where !string.IsNullOrWhiteSpace(c) select "'" + (replaceDecimals ? c.Replace(".", string.Empty) : c) + "'").Distinct().ToArray()); }
string MergeSql(SummaryRequestModel args, string years, string periods) { string query = Template; query = CleanComments(query); if (IsMFU) { string mfuStratClause = CleanComments(MFUStratificationClause(args)); if (!string.IsNullOrWhiteSpace(mfuStratClause)) { query = query.Replace("%STRATIFICATION_CLAUSE%", mfuStratClause); } } // Build the cross join (zero rows) clauses and insert in the query. string periodCJ = BuildCrossJoinClause("Year", periods.Split(','), "en"); string sexCJ = BuildCrossJoinClause("Sex", ParseSexValue(args).Split(','), "sx"); string cjcs = periodCJ + "," + sexCJ; ApplyCrossJoinForCodes(args, ref query, ref cjcs); query = query.Replace("%CJC%", cjcs) .Replace("ag.%STRATIFICATION%_name", args.AgeStratification == 5 ? "'0+'" : "ag.%STRATIFICATION%_name") .Replace("ag.%STRATIFICATION%_sort_order", args.AgeStratification == 5 ? "0" : "ag.%STRATIFICATION%_sort_order") .Replace("%STRATIFICATION%", AGE_STRATIFICATIONS[args.AgeStratification ?? 0]) .Replace("%SETTING%", "'" + (args.Setting ?? "") + "'") .Replace("%PERIODS%", periods) .Replace("%YEARS%", years) .Replace("%SEX%", ParseSexValue(args)); // MFU queries have metric types. if (!string.IsNullOrWhiteSpace(args.MetricType)) { query = query.Replace("%METRIC_TYPE%", METRIC_TYPES[Convert.ToInt32(args.MetricType)]) .Replace("%SD_METRIC_TYPE%", SD_METRIC_TYPES[Convert.ToInt32(args.MetricType)]); } if (!string.IsNullOrWhiteSpace(args.OutputCriteria)) { query = query.Replace("%OUTPUT_CRITERIA%", args.OutputCriteria.ToString()); } // SexStratification == 4 is M and F aggregated if (args.SexStratification == 4) { query = query.Replace("%SEX_AGGREGATED%", "'All'") .Replace("%MATCH_SEX%", "") .Replace("%MATCH_SEX2%", "") .Replace("%MATCH_SEX3%", ""); } else { query = query.Replace("%SEX_AGGREGATED%", "sx.sex") .Replace("%MATCH_SEX%", "AND ed.Sex = AgeGroups.Sex") .Replace("%MATCH_SEX2%", "AND SummaryData.Sex = EnrollmentData.Sex") .Replace("%MATCH_SEX3%", "sex,"); } if (args.Coverage.HasValue) { switch (args.Coverage.Value) { case DTO.Enums.Coverages.DRUG: query = query.Replace("%DRUGCOV%", "'Y'") .Replace("%MEDCOV%", "'N'") .Replace("%MEDCOV_AGGREGATED%", "ed.MedCov") .Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov"); break; case DTO.Enums.Coverages.MED: query = query.Replace("%DRUGCOV%", "'N'") .Replace("%MEDCOV%", "'Y'") .Replace("%MEDCOV_AGGREGATED%", "ed.MedCov") .Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov"); break; case DTO.Enums.Coverages.DRUG_MED: query = query.Replace("%DRUGCOV%", "'Y'") .Replace("%MEDCOV%", "'Y'") .Replace("%MEDCOV_AGGREGATED%", "ed.MedCov") .Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov"); break; case DTO.Enums.Coverages.ALL: default: // ALL query = query.Replace("%DRUGCOV%", "'Y','N'") .Replace("%MEDCOV%", "'Y','N'") .Replace("%MEDCOV_AGGREGATED%", "'All'") .Replace("%DRUGCOV_AGGREGATED%", "'All'"); break; } } ReplaceParameters(ref query); return(query); }
/// <summary> /// Applies crossjoin (as applicable) to the query. /// </summary> /// <param name="args"></param> /// <param name="query"></param> /// <returns></returns> protected virtual void ApplyCrossJoinForCodes(SummaryRequestModel args, ref string query, ref string cjcs) { }
/// <summary> /// Gets the raw MFU stratification clause. /// </summary> protected virtual string MFUStratificationClause(SummaryRequestModel args) { return(null); }