string BuildPanelItemSql(PanelItemContext ctx)
{
ctx.PanelItem.SqlRecencyFilter = GetRecencyFilter(ctx.PanelItem.RecencyFilter);
var alias = GetJoiningAlias(ctx.TargetColumn);
var itemAlias = $"_{alias}{ctx.PanelItem.PanelIndex}{ctx.PanelItem.SubPanelIndex}{ctx.PanelItem.Index}";
var recencyAlias = ctx.PanelItem.UseRecencyFilter ? $"{itemAlias}_{ctx.PanelItem.SqlRecencyFilter.ToLower()}" : "";
bool hasWhere = !string.IsNullOrWhiteSpace(ctx.PanelItem.Concept.SqlSetWhere);
bool isCachedCohort = ctx.PanelItem.Concept.SqlSetFrom.Contains(cohortOptions.SetCohort);
var targetColumn = ctx.TargetColumn;
string groupByColumns = ctx.TargetColumn;
if (ctx.IsSequential)
{
var encounterBasedGroup = "";
if (ctx.PanelItem.Concept.IsEncounterBased)
{
encounterBasedGroup = $", {itemAlias}.{compilerOptions.FieldEncounterId}, {PrependSetAlias(itemAlias, ctx.PanelItem.Concept.SqlFieldDate)}";
}
var eventBasedGroup = "";
if (ctx.PanelItem.Concept.IsEventBased)
{
eventBasedGroup = $", {PrependSetAlias(itemAlias, ctx.PanelItem.Concept.SqlFieldEvent)}";
}
groupByColumns += encounterBasedGroup + eventBasedGroup;
// date column
targetColumn += ", " + (ctx.PanelItem.Concept.IsEncounterBased ? PrependSetAlias(itemAlias, ctx.PanelItem.Concept.SqlFieldDate) : "NullDateField = CONVERT(DATETIME,NULL)");
// encounter column
targetColumn += ", " + (ctx.PanelItem.Concept.IsEncounterBased ? itemAlias + "." + compilerOptions.FieldEncounterId : compilerOptions.FieldEncounterId + " = NULL");
// event ID column
if (ctx.PanelItem.Concept.IsEventBased)
{
targetColumn += ", " + PrependSetAlias(itemAlias, ctx.PanelItem.Concept.SqlFieldEvent);
}
}
// begin sql stitching
string numericFilterSql = string.Empty;
string dropdownSql = string.Empty;
// SELECT ...
var itemSql = new StringBuilder($"{Dialect.SQL_SELECT} {(ctx.IsExists ? "1" : $"{itemAlias}.{targetColumn}")} ");
string BuildPersonPanelSql(Panel panel, bool showPersonId)
{
var sqlBuilder = new StringBuilder();
var fieldPersonId = compilerOptions.FieldPersonId;
var totalItems = panel.SubPanels.ElementAt(0).PanelItems.Count();
var showType = !showPersonId;
for (var k = 0; k < totalItems; k++)
{
var sub = panel.SubPanels.ElementAt(0);
var itemConfig = new PanelItemContext
{
PanelItem = sub.PanelItems.ElementAt(k),
SubPanelHasNonEncounter = false,
TargetColumn = fieldPersonId,
FilterDate = panel.IsDateFiltered,
FilterCount = sub.HasCountFilter,
MinCount = sub.MinimumCount,
DateStart = panel.DateFilter?.Start,
DateStop = panel.DateFilter?.End,
IsSequential = false,
IsExists = showType,
ExistsParentAlias = Dialect.ALIAS_PERSON,
ExistsJoinColumn = fieldPersonId
};
var itemSql = BuildPanelItemSql(itemConfig);
sqlBuilder.Append(itemSql);
if ((k + 1) < totalItems && totalItems > 1)
{
sqlBuilder.Append(Dialect.SQL_SPACE + Dialect.SQL_UNIONALL + Dialect.SQL_SPACE);
}
}
var sql = sqlBuilder.ToString();
ValidateSql(sql);
return(sql);
}
string BuildSequentialPanelSql(Panel panel, bool showPersonId)
{
var firstSubpanelIndex = panel.SubPanels.ElementAt(0).Index;
var panelSql = new StringBuilder();
var outputColumn = showPersonId ? $"{Dialect.ALIAS_SUBQUERY}{firstSubpanelIndex}.{compilerOptions.FieldPersonId} " : "1 ";
// SELECT ...
// FROM ...
panelSql.Append($"{Dialect.SQL_SELECT} {outputColumn}" +
$"{Dialect.SQL_FROM} ");
bool hasAnchorDate = false;
bool isAnchorDate = false;
string anchorDate = string.Empty;
for (int k = 0; k < panel.SubPanels.Count; k++)
{
string nonEncounterJoinLogic = string.Empty;
string nonEventJoinLogic = string.Empty;
SubPanel subPanel = panel.SubPanels.ElementAt(k);
var firstItem = subPanel.PanelItems.ElementAt(0);
isAnchorDate = false;
if (!hasAnchorDate)
{
// In order to have a base date to join on later in the sequence,
// track the first included subpanel date field.
if (subPanel.JoinSequence.SequenceType != SequenceType.PlusMinus && subPanel.IncludeSubPanel)
{
hasAnchorDate = true;
isAnchorDate = true;
anchorDate = PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", subPanel.PanelItems.ElementAt(0).Concept.SqlFieldDate);
}
}
else
{
if (subPanel.IncludeSubPanel && subPanel.JoinSequence.SequenceType == SequenceType.WithinFollowing)
{
anchorDate = $"DATEADD({subPanel.JoinSequence.DateIncrementType.ToString()}, {subPanel.JoinSequence.Increment}, {anchorDate})";
}
}
// JOIN ...
if (k > 0)
{
panelSql.Append(" " + (subPanel.IncludeSubPanel ? Dialect.SQL_INNERJOIN : Dialect.SQL_LEFTJOIN) + Dialect.SQL_SPACE);
}
panelSql.Append("(");
for (int j = 0; j < subPanel.PanelItems.Count(); j++)
{
var itemConfig = new PanelItemContext
{
PanelItem = subPanel.PanelItems.ElementAt(j),
SubPanelHasNonEncounter = subPanel.HasNonEncounter,
TargetColumn = compilerOptions.FieldPersonId,
FilterDate = panel.IsDateFiltered,
FilterCount = subPanel.HasCountFilter,
MinCount = subPanel.MinimumCount,
DateStart = panel.DateFilter?.Start,
DateStop = panel.DateFilter?.End,
IsSequential = true,
IsExists = false,
ExistsParentAlias = string.Empty,
ExistsJoinColumn = string.Empty
};
var itemSql = BuildPanelItemSql(itemConfig);
panelSql.Append(itemSql);
// Add UNION ALL if other panel items follow
if ((j + 1) < subPanel.PanelItems.Count())
{
panelSql.Append($" {Dialect.SQL_UNIONALL} ");
}
}
if (k == 0)
{
panelSql.Append($") AS {Dialect.ALIAS_SUBQUERY}{k} ");
}
else
{
// ON A.PersonID = B.PersonID
panelSql.Append(") AS " +
$"{Dialect.ALIAS_SUBQUERY}{k} {Dialect.SQL_ON} {Dialect.ALIAS_SUBQUERY}{firstSubpanelIndex}.{compilerOptions.FieldPersonId} = " +
$"{Dialect.ALIAS_SUBQUERY}{k}.{compilerOptions.FieldPersonId} ");
panelSql.Append(Dialect.SQL_AND + " ");
var prevSub = panel.SubPanels.ElementAt(k - 1);
// If the previous SubPanel was Excluded, append "( ... additional logic ... )"
if (!prevSub.IncludeSubPanel)
{
panelSql.Append("(");
}
// If same encounter as previous SubPanel
if (subPanel.JoinSequence.SequenceType == SequenceType.Encounter)
{
// A0.EncounterID = A1.EncounterID
panelSql.Append($"{Dialect.ALIAS_SUBQUERY}{k - 1}.{compilerOptions.FieldEncounterId} = {Dialect.ALIAS_SUBQUERY}{k}.{compilerOptions.FieldEncounterId} {nonEncounterJoinLogic}");
}
// If same event as previous SubPanel
else if (subPanel.JoinSequence.SequenceType == SequenceType.Event)
{
// A0.EventId = A1.EventId
panelSql.Append(
$"{PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldEvent)} = " +
$"{PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", firstItem.Concept.SqlFieldEvent)} {nonEventJoinLogic} ");
}
// If +/- date in previous SubPanel
else if (subPanel.JoinSequence.SequenceType == SequenceType.PlusMinus)
{
panelSql.Append(
// (A1.DateField BETWEEN
$"({PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", firstItem.Concept.SqlFieldDate)} {Dialect.SQL_BETWEEN} " +
// DATEADD(MONTH, -1, A0.DateField
$"{Dialect.SQL_DATEADD}{subPanel.JoinSequence.DateIncrementType.ToString().ToUpper()}, -{subPanel.JoinSequence.Increment}, {PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldDate)}) " +
// AND
Dialect.SQL_AND +
// DATEADD(MONTH, 1, A0.DateField)
$"{Dialect.SQL_DATEADD}{subPanel.JoinSequence.DateIncrementType.ToString().ToUpper()}, {subPanel.JoinSequence.Increment}, {PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldDate)}) " +
$"{nonEncounterJoinLogic})");
}
// If follows within a range of previous SubPanel
else if (subPanel.JoinSequence.SequenceType == SequenceType.WithinFollowing)
{
panelSql.Append(
// (A1.DateField BETWEEN
$"({PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", firstItem.Concept.SqlFieldDate)} {Dialect.SQL_BETWEEN} " +
// A0.DateField
$"{PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldDate)} " +
// AND
Dialect.SQL_AND +
// DATEADD(MONTH, 1, A0.DateField)
$"{Dialect.SQL_DATEADD}{subPanel.JoinSequence.DateIncrementType.ToString().ToUpper()}, {subPanel.JoinSequence.Increment}, {PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldDate)}) " +
$"{nonEncounterJoinLogic})");
}
// If follows anytime of previous SubPanel
else if (subPanel.JoinSequence.SequenceType == SequenceType.AnytimeFollowing)
{
panelSql.Append(
// (A1.DateField >
$"({PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", firstItem.Concept.SqlFieldDate)} > " +
// A0.DateField)
$"{PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k - 1}", prevSub.PanelItems.ElementAt(0).Concept.SqlFieldDate)} " +
$"{nonEncounterJoinLogic})");
}
// If the previous SubPanel was Excluded
if (!prevSub.IncludeSubPanel)
{
// ) OR (A0.PersonId IS NULL
panelSql.Append($" {Dialect.SQL_OR} ({Dialect.ALIAS_SUBQUERY}{k - 1}.{compilerOptions.FieldPersonId} {Dialect.SQL_ISNULL} ");
if (hasAnchorDate && !isAnchorDate && subPanel.JoinSequence.SequenceType != SequenceType.Encounter)
{
// AND A1.DateField >
panelSql.Append($"{Dialect.SQL_AND} ({PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", firstItem.Concept.SqlFieldDate)} > " +
$"{anchorDate})");
}
panelSql.Append(")) ");
}
}
}
// JOIN to parent SQL set
if (!showPersonId)
{
panelSql.Append($"{Dialect.SQL_WHERE} {Dialect.ALIAS_PERSON}.{compilerOptions.FieldPersonId} = {Dialect.ALIAS_PERSON}{panel.Index}.{compilerOptions.FieldPersonId} ");
}
List <int> subPanelsWithHavingClause = (from s in panel.SubPanels
where !s.IncludeSubPanel || s.HasCountFilter
select s.Index).ToList();
if (subPanelsWithHavingClause.Count > 0 || showPersonId)
{
// GROUP BY PersonId
panelSql.Append($" {Dialect.SQL_GROUPBY} {Dialect.ALIAS_SUBQUERY}{firstSubpanelIndex}.{compilerOptions.FieldPersonId} ");
if (subPanelsWithHavingClause.Count > 0)
{
panelSql.Append($"HAVING ");
foreach (int k in subPanelsWithHavingClause)
{
SubPanel subPanel = panel.SubPanels.ElementAt(k);
string countDistinctDate = $"{Dialect.SQL_COUNT}DISTINCT {PrependSetAlias($"{Dialect.ALIAS_SUBQUERY}{k}", subPanel.PanelItems.ElementAt(0).Concept.SqlFieldDate)}) ";
// If SubPanel is Included and has a COUNT filter
if (subPanel.HasCountFilter && subPanel.IncludeSubPanel)
{
panelSql.Append($"{countDistinctDate} >= {subPanel.MinimumCount} ");
}
// If SubPanel is Excluded and has a COUNT filter
else if (subPanel.HasCountFilter && !subPanel.IncludeSubPanel)
{
panelSql.Append($"{countDistinctDate} < {subPanel.MinimumCount} ");
}
// If SubPanel is Excluded and does not have a COUNT Filter
else if (!subPanel.HasCountFilter && !subPanel.IncludeSubPanel)
{
panelSql.Append($"{countDistinctDate} = 0");
}
if (k < subPanelsWithHavingClause.Max())
{
panelSql.Append($"{Dialect.SQL_AND} ");
}
}
}
}
return(panelSql.ToString());
}
