private string MergeSQLNonMFU(Guid requestTypeId, string template, string templateName, SummaryRequestModel args)
{
string years = ExpandYears(args.StartPeriod, args.EndPeriod);
return(MergeSQL(requestTypeId, template, templateName, args, years, args.Period));
}
private string MergeSQL(Guid requestTypeId, string template, string templateName, SummaryRequestModel args, string years, string periods)
{
string query = template;
// Build the MFU stratification union clauses and insert in the query.
if (IsMFU(requestTypeId))
{
using (StreamReader r = new StreamReader(this.GetType().Assembly.GetManifestResourceStream(templateName.Substring(0, templateName.IndexOf(".txt")) + "_" + AGE_STRATIFICATIONS[args.AgeStratification ?? 0] + (args.SexStratification == 4 ? "_MF" : "") + ".txt")))
{
string mfuStratClause = RemoveComments(r.ReadToEnd());
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", SEX_STRATIFICATIONS[args.SexStratification ?? 0].Split(','), "sx");
string cjcs = periodCJ + "," + sexCJ;
// MFU queries do not have codes.
// Incident and prevalence pharma codes are drug class and generic names.
if (!string.IsNullOrWhiteSpace(args.Codes))
{
// Remove duplicate codes
string[] codes = (from c in args.Codes.Split(',').Select(p => p.Trim())
where !string.IsNullOrWhiteSpace(c)
select "'" + System.Net.WebUtility.HtmlDecode(c) + "'").Distinct().ToArray();
//Note: at this point the value of each code may contain a htmldecoded comma.
string codeCJ = "", param;
switch ("{" + requestTypeId.ToString().ToUpper() + "}")
{
// Prev and MFU
case GenericName:
case MFU_GenericName:
case Incident_GenericName:
param = "GenericName";
codeCJ = BuildCrossJoinClause(param, codes, "sd");
break;
case DrugClass:
case MFU_DrugClass:
case Incident_DrugClass:
param = "DrugClass";
codeCJ = BuildCrossJoinClause(param, codes, "sd");
break;
default:
param = "code";
codes = codes.Select(c => c.Replace(".", "")).ToArray(); // remove period from numeric codes
codeCJ = BuildCrossJoinClause(param, "name", codes, args.CodeNames, "sd");
break;
}
cjcs += "," + codeCJ;
query = query.Replace("%CODES%", string.Join(",", codes.Select(c => c.Replace("%comma;", ",")).ToArray()));
}
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%", SEX_STRATIFICATIONS[args.SexStratification ?? 0]);
// MFU queries have metric types.
if (args.MetricType != null)
{
query = query.Replace("%METRIC_TYPE%", METRIC_TYPES[Convert.ToInt32(args.MetricType)])
.Replace("%SD_METRIC_TYPE%", SD_METRIC_TYPES[Convert.ToInt32(args.MetricType)]);
}
if (args.OutputCriteria != null)
{
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 != null)
{
switch (args.Coverage)
{
case "DRUG":
query = query.Replace("%DRUGCOV%", "'Y'")
.Replace("%MEDCOV%", "'N'")
.Replace("%MEDCOV_AGGREGATED%", "ed.MedCov")
.Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov");
break;
case "MED":
query = query.Replace("%DRUGCOV%", "'N'")
.Replace("%MEDCOV%", "'Y'")
.Replace("%MEDCOV_AGGREGATED%", "ed.MedCov")
.Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov");
break;
case "DRUG|MED":
query = query.Replace("%DRUGCOV%", "'Y'")
.Replace("%MEDCOV%", "'Y'")
.Replace("%MEDCOV_AGGREGATED%", "ed.MedCov")
.Replace("%DRUGCOV_AGGREGATED%", "ed.DrugCov");
break;
default: // ALL
query = query.Replace("%DRUGCOV%", "'Y','N'")
.Replace("%MEDCOV%", "'Y','N'")
.Replace("%MEDCOV_AGGREGATED%", "'All'")
.Replace("%DRUGCOV_AGGREGATED%", "'All'");
break;
}
}
switch ("{" + requestTypeId.ToString().ToUpper() + "}")
{
// Prev and MFU
case GenericName:
case MFU_GenericName:
query = query.Replace("%NAME_FIELD%", "GenericName")
.Replace("%SD_TABLE%", "GENERIC_NAME");
break;
case DrugClass:
case MFU_DrugClass:
query = query.Replace("%NAME_FIELD%", "DrugClass")
.Replace("%SD_TABLE%", "DRUG_CLASS");
break;
case ICD9Diagnosis:
case MFU_ICD9Diagnosis:
query = query.Replace("%CODE_FIELD%", "DXCode")
.Replace("%NAME_FIELD%", "DXName")
.Replace("%SD_CODE_FIELD%", "code")
.Replace("%SD_TABLE%", "ICD9_DIAGNOSIS");
break;
case ICD9Procedures:
case MFU_ICD9Procedures:
query = query.Replace("%CODE_FIELD%", "PXCode")
.Replace("%NAME_FIELD%", "PXName")
.Replace("%SD_CODE_FIELD%", "code")
.Replace("%SD_TABLE%", "ICD9_PROCEDURE");
break;
case HCPCSProcedures:
case MFU_HCPCSProcedures:
query = query.Replace("%CODE_FIELD%", "PXCode")
.Replace("%NAME_FIELD%", "PXName")
.Replace("%SD_CODE_FIELD%", "px_code")
.Replace("%SD_TABLE%", "HCPCS");
break;
case ICD9Diagnosis_4_digit:
case MFU_ICD9Diagnosis_4_digit:
query = query.Replace("%CODE_FIELD%", "DXCode")
.Replace("%NAME_FIELD%", "DXName")
.Replace("%SD_CODE_FIELD%", "code")
.Replace("%SD_TABLE%", "ICD9_DIAGNOSIS_4_DIGIT");
break;
case ICD9Diagnosis_5_digit:
case MFU_ICD9Diagnosis_5_digit:
query = query.Replace("%CODE_FIELD%", "DXCode")
.Replace("%NAME_FIELD%", "DXName")
.Replace("%SD_CODE_FIELD%", "code")
.Replace("%SD_TABLE%", "ICD9_DIAGNOSIS_5_DIGIT");
break;
case ICD9Procedures_4_digit:
case MFU_ICD9Procedures_4_digit:
query = query.Replace("%CODE_FIELD%", "PXCode")
.Replace("%NAME_FIELD%", "PXName")
.Replace("%SD_CODE_FIELD%", "px_code")
.Replace("%SD_TABLE%", "ICD9_PROCEDURE_4_DIGIT");
break;
// Incidence
case Incident_GenericName:
query = query.Replace("%NAME_FIELD%", "GenericName")
.Replace("%SD_TABLE%", "INCIDENT_GENERIC_NAME");
break;
case Incident_DrugClass:
query = query.Replace("%NAME_FIELD%", "DrugClass")
.Replace("%SD_TABLE%", "INCIDENT_DRUG_CLASS");
break;
case Incident_ICD9Diagnosis:
query = query.Replace("%CODE_FIELD%", "DXCode")
.Replace("%NAME_FIELD%", "DXName")
.Replace("%SD_CODE_FIELD%", "code")
.Replace("%SD_TABLE%", "INCIDENT_ICD9_DIAGNOSIS");
break;
}
return(query);
}
private string MergeSQLForMFU(Guid requestTypeId, string template, string templateName, SummaryRequestModel args)
{
string query = "";
string[] years = ExpandYears(args.StartPeriod, args.EndPeriod).Split(',');
foreach (var year in years)
{
query += MergeSQL(requestTypeId, template, templateName, args, year, year) + queryDelimiters[0];
}
return(query);
}
