/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private string GetReverseCrossReferenceItemsQuery(
ReverseCrossReferenceFieldDefinition join, ChecklistFieldDefinition referenceField, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{2}"" AS ""{7}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}""
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(referenceField)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(referenceField)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName));
return result.ToString();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private static string GetReverseCrossReferenceItemsQuery(
SingleReverseCrossReferenceFieldDefinition join,
SingleCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT MIN(""Id"") ""Id"", ""{1}""
FROM ""{2}""
WHERE ""IsRemoved"" = 0
GROUP BY ""{1}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(referenceField.SystemName),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName));
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",").AppendFormat(
@"
{0}
AS
(
SELECT
MIN(""Id"") ""Id""
,q1.""{1}""
FROM
{2} q1
INNER JOIN (
SELECT
MAX(""{3}"") ""{3}""
,""{1}""
,""{4}""
FROM {2}
GROUP BY ""{1}"", ""{4}""
) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}""
GROUP BY q1.""{1}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(referenceField.SystemName),
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId));
}
return result.ToString();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private string GetReverseCrossReferenceItemsQuery(
MultiReverseCrossReferenceFieldDefinition join,
MultiCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (referenceField.LinkField != null)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{1}"" AS ""{2}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}"" AND q2.""{6}"" = 0
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(join.DeclaringProcess.SystemName),
OracleNamesTranslator.Translate(referenceField.LinkField.SystemName),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
else
{
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{2}"" AS ""{7}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}""
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(referenceField)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(referenceField)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName));
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",");
result.AppendFormat(
@"
{0}
AS
(
SELECT
MIN(""Id"") ""Id""
,q1.""{1}"" AS ""{5}""
FROM {2} q1
INNER JOIN (
SELECT
MAX(""{3}"") ""{3}""
,""{1}""
,""{4}""
FROM {2}
GROUP BY ""{1}"", ""{4}""
) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}""
GROUP BY q1.""{1}"", q1.""{4}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId),
OracleNamesTranslator.Translate(referenceField.SystemName));
}
}
return result.ToString();
}
/// <summary>
/// Adds the reverse cross reference join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private static void AddReverseCrossReferenceJoin(
StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN {0} ON {0}.\"{1}\" = {2}.\"{3}\"",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(join.ReferenceField.SystemName),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName))
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{2}\"",
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
aliasCollection.GetTableAlias(join.ReferencedProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetReverseCrossReferenceItemsQueryName(join));
}
private string GetReverseCrossReferenceItemsQuery(
ReverseCrossReferenceFieldDefinition join, ChecklistFieldDefinition referenceField, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.[{1}], q2.[{2}] AS [{7}]
FROM
[dbo].[{3}] q1
INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}]
WHERE q1.[{6}] = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
Constants.IdColumnName,
GetJoinTableChildKey(referenceField),
join.ReferencedProcess.SystemName,
GetJoinTableName(referenceField),
GetJoinTableMasterKey(referenceField),
Constants.IsRemovedColumnName,
referenceField.SystemName);
return result.ToString();
}
private string GetReverseCrossReferenceItemsQuery(
MultiReverseCrossReferenceFieldDefinition join,
MultiCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (referenceField.LinkField != null)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.[{1}], q2.[{1}] AS [{2}]
FROM
[dbo].[{3}] q1
INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] AND q2.[{6}] = 0
WHERE q1.[{6}] = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
Constants.IdColumnName,
referenceField.SystemName,
join.ReferencedProcess.SystemName,
join.DeclaringProcess.SystemName,
referenceField.LinkField.SystemName,
Constants.IsRemovedColumnName);
}
else
{
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.[{1}], q2.[{2}] AS [{7}]
FROM
[dbo].[{3}] q1
INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}]
WHERE q1.[{6}] = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
Constants.IdColumnName,
GetJoinTableChildKey(referenceField),
join.ReferencedProcess.SystemName,
GetJoinTableName(referenceField),
GetJoinTableMasterKey(referenceField),
Constants.IsRemovedColumnName,
referenceField.SystemName);
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",");
result.AppendFormat(
@"
{0}
AS
(
SELECT
MIN([Id]) [Id]
,q1.[{1}] AS [{5}]
FROM {2} q1
INNER JOIN (
SELECT
MAX([{3}]) [{3}]
,[{1}]
,[{4}]
FROM {2}
GROUP BY [{1}], [{4}]
) q2 ON q2.[{4}] = q1.[{4}] AND q2.[{3}] = q1.[{3}] AND q2.[{1}] = q1.[{1}]
GROUP BY q1.[{1}], q1.[{4}]
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
GetJoinTableChildKey(referenceField),
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
Constants.VersionNumber,
Constants.VersionMasterId,
referenceField.SystemName);
}
}
return result.ToString();
}
private static string GetReverseCrossReferenceItemsQuery(
SingleReverseCrossReferenceFieldDefinition join,
SingleCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT MIN([Id]) [Id], [{1}]
FROM [dbo].[{2}]
WHERE [IsRemoved] = 0
GROUP BY [{1}]
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
referenceField.SystemName,
join.ReferencedProcess.SystemName);
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",").AppendFormat(
@"
{0}
AS
(
SELECT
MIN([Id]) [Id]
,q1.[{1}]
FROM
{2} q1
INNER JOIN (
SELECT
MAX([{3}]) [{3}]
,[{1}]
,[{4}]
FROM {2}
GROUP BY [{1}], [{4}]
) q2 ON q2.[{4}] = q1.[{4}] AND q2.[{3}] = q1.[{3}] AND q2.[{1}] = q1.[{1}]
GROUP BY q1.[{1}]
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
referenceField.SystemName,
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
Constants.VersionNumber,
Constants.VersionMasterId);
}
return result.ToString();
}
private static void AddReverseCrossReferenceJoin(
StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN {0} ON {0}.[{1}] = {2}.[{3}]",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
join.ReferenceField.SystemName,
aliasCollection.GetTableAlias(join.DeclaringProcess),
Constants.IdColumnName)
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{2}]",
join.ReferencedProcess.SystemName,
aliasCollection.GetTableAlias(join.ReferencedProcess),
Constants.IdColumnName,
aliasCollection.GetReverseCrossReferenceItemsQueryName(join));
}
