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