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