private void AppendFilterTable(string filterAlias)
{
_stringBuilder.AppendLine("( SELECT ");
if (_queryExpression.IsInstanceIELevel())
{
_stringBuilder.AppendLine(VLatest.Study.StudyInstanceUid, InstanceTableAlias);
_stringBuilder.Append(",").AppendLine(VLatest.Series.SeriesInstanceUid, InstanceTableAlias);
_stringBuilder.Append(",").AppendLine(VLatest.Instance.SopInstanceUid, InstanceTableAlias);
_stringBuilder.Append(",").AppendLine(VLatest.Instance.Watermark, InstanceTableAlias);
}
else
{
_stringBuilder.AppendLine(VLatest.Study.StudyKey, StudyTableAlias);
if (_queryExpression.IsSeriesIELevel())
{
_stringBuilder.Append(",").AppendLine(VLatest.Series.SeriesKey, SeriesTableAlias);
}
}
_stringBuilder.AppendLine($"FROM {VLatest.Study.TableName} {StudyTableAlias}");
if (_queryExpression.IsSeriesIELevel() || _queryExpression.IsInstanceIELevel())
{
_stringBuilder.AppendLine($"INNER JOIN {VLatest.Series.TableName} {SeriesTableAlias}");
_stringBuilder
.Append("ON ")
.Append(VLatest.Series.StudyKey, SeriesTableAlias)
.Append(" = ")
.AppendLine(VLatest.Study.StudyKey, StudyTableAlias);
AppendPartitionJoinClause(SeriesTableAlias, StudyTableAlias);
}
if (_queryExpression.IsInstanceIELevel())
{
_stringBuilder.AppendLine($"INNER JOIN {VLatest.Instance.TableName} {InstanceTableAlias}");
_stringBuilder
.Append("ON ")
.Append(VLatest.Instance.SeriesKey, InstanceTableAlias)
.Append(" = ")
.AppendLine(VLatest.Series.SeriesKey, SeriesTableAlias);
AppendPartitionJoinClause(InstanceTableAlias, SeriesTableAlias);
AppendStatusClause(InstanceTableAlias);
}
AppendExtendedQueryTagTables();
_stringBuilder.AppendLine("WHERE 1 = 1");
AppendPartitionWhereClause(StudyTableAlias);
using (IndentedStringBuilder.DelimitedScope delimited = _stringBuilder.BeginDelimitedWhereClause())
{
AppendFilterClause();
}
AppendFilterPaging();
_stringBuilder.AppendLine($") {filterAlias}");
}
private void AppendIntersectionWithPredecessor(IndentedStringBuilder.DelimitedScope delimited, TableExpression tableExpression, string tableAlias = null)
{
int predecessorIndex = FindRestrictingPredecessorTableExpressionIndex();
if (predecessorIndex >= 0)
{
delimited.BeginDelimitedElement();
string columnToSelect = (tableExpression.Kind == TableExpressionKind.Chain ? tableExpression.ChainLevel - 1 : tableExpression.ChainLevel) == 0 ? "Sid1" : "Sid2";
StringBuilder.Append(V1.Resource.ResourceSurrogateId, tableAlias).Append(" IN (SELECT ").Append(columnToSelect)
.Append(" FROM ").Append(TableExpressionName(predecessorIndex)).Append(")");
}
}
private void AppendCrossApplyTable(string crossApplyAlias, string filterAlias)
{
// already have the 3 UID projects needed so skip crossapply for projection
if (_queryExpression.IsInstanceIELevel())
{
return;
}
string tableAlias = "a";
_stringBuilder.AppendLine("CROSS APPLY").AppendLine(" ( ");
_stringBuilder.AppendLine("SELECT TOP 1");
_stringBuilder.Append(VLatest.Instance.StudyInstanceUid, tableAlias).AppendLine(",");
_stringBuilder.Append(VLatest.Instance.SeriesInstanceUid, tableAlias).AppendLine(",");
_stringBuilder.Append(VLatest.Instance.SopInstanceUid, tableAlias).AppendLine(",");
_stringBuilder.AppendLine(VLatest.Instance.Watermark, tableAlias);
_stringBuilder.AppendLine($"FROM {VLatest.Instance.TableName} {tableAlias}");
_stringBuilder.AppendLine("WHERE 1 = 1");
using (IndentedStringBuilder.DelimitedScope delimited = _stringBuilder.BeginDelimitedWhereClause())
{
_stringBuilder
.Append("AND ")
.Append(VLatest.Instance.StudyKey, tableAlias)
.Append(" = ")
.AppendLine(VLatest.Study.StudyKey, filterAlias);
if (_queryExpression.IsSeriesIELevel())
{
_stringBuilder
.Append("AND ")
.Append(VLatest.Instance.SeriesKey, tableAlias)
.Append(" = ")
.AppendLine(VLatest.Series.SeriesKey, filterAlias);
}
AppendStatusClause(tableAlias);
AppendOrderBy(tableAlias);
}
_stringBuilder.AppendLine($") {crossApplyAlias}");
}
private void AppendExtendedQueryTagTables()
{
foreach (QueryFilterCondition condition in _queryExpression.FilterConditions.Where(x => x.QueryTag.IsExtendedQueryTag))
{
QueryTag queryTag = condition.QueryTag;
int tagKey = queryTag.ExtendedQueryTagStoreEntry.Key;
ExtendedQueryTagDataType dataType = ExtendedQueryTagLimit.ExtendedQueryTagVRAndDataTypeMapping[queryTag.VR.Code];
string extendedQueryTagTableAlias = null;
_stringBuilder.Append("INNER JOIN ");
switch (dataType)
{
case ExtendedQueryTagDataType.StringData:
extendedQueryTagTableAlias = ExtendedQueryTagStringTableAlias + tagKey;
_stringBuilder.AppendLine($"{VLatest.ExtendedQueryTagString.TableName} {extendedQueryTagTableAlias}");
break;
case ExtendedQueryTagDataType.LongData:
extendedQueryTagTableAlias = ExtendedQueryTagLongTableAlias + tagKey;
_stringBuilder.AppendLine($"{VLatest.ExtendedQueryTagLong.TableName} {extendedQueryTagTableAlias}");
break;
case ExtendedQueryTagDataType.DoubleData:
extendedQueryTagTableAlias = ExtendedQueryTagDoubleTableAlias + tagKey;
_stringBuilder.AppendLine($"{VLatest.ExtendedQueryTagDouble.TableName} {extendedQueryTagTableAlias}");
break;
case ExtendedQueryTagDataType.DateTimeData:
extendedQueryTagTableAlias = ExtendedQueryTagDateTimeTableAlias + tagKey;
_stringBuilder.AppendLine($"{VLatest.ExtendedQueryTagDateTime.TableName} {extendedQueryTagTableAlias}");
break;
case ExtendedQueryTagDataType.PersonNameData:
extendedQueryTagTableAlias = ExtendedQueryTagPersonNameTableAlias + tagKey;
_stringBuilder.AppendLine($"{VLatest.ExtendedQueryTagPersonName.TableName} {extendedQueryTagTableAlias}");
break;
}
_stringBuilder
.Append("ON ")
.Append($"{extendedQueryTagTableAlias}.PartitionKey")
.Append(" = ")
.AppendLine(VLatest.Study.PartitionKey, StudyTableAlias);
_stringBuilder
.Append("AND ")
.Append($"{extendedQueryTagTableAlias}.StudyKey")
.Append(" = ")
.AppendLine(VLatest.Study.StudyKey, StudyTableAlias);
using (IndentedStringBuilder.DelimitedScope delimited = _stringBuilder.BeginDelimitedOnClause())
{
if ((_queryExpression.IsSeriesIELevel() || _queryExpression.IsInstanceIELevel()) && queryTag.Level < QueryTagLevel.Study)
{
_stringBuilder
.Append("AND ")
.Append($"{extendedQueryTagTableAlias}.SeriesKey")
.Append(" = ")
.AppendLine(VLatest.Series.SeriesKey, SeriesTableAlias);
}
if (_queryExpression.IsInstanceIELevel() && queryTag.Level < QueryTagLevel.Series)
{
_stringBuilder
.Append("AND ")
.Append($"{extendedQueryTagTableAlias}.InstanceKey")
.Append(" = ")
.AppendLine(VLatest.Instance.InstanceKey, InstanceTableAlias);
}
}
}
}
