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));
}
private void AddChecklistJoin(StringBuilder sql, ChecklistFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]",
GetJoinTableName(join),
aliasCollection.GetJoinTableAlias(join),
GetJoinTableMasterKey(join),
aliasCollection.GetTableAlias(join.DeclaringProcess),
Constants.IdColumnName)
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0",
join.ReferencedProcess.SystemName,
aliasCollection.GetTableAlias(join.ReferencedProcess),
Constants.IdColumnName,
aliasCollection.GetJoinTableAlias(join),
GetJoinTableChildKey(join),
Constants.IsRemovedColumnName);
}
private static void AddBaseTableJoins(
StringBuilder sql, ProcessDefinition process, ProcessDefinition lastProcess, TableAliasCollection aliasCollection)
{
if (process == lastProcess || process.BaseProcess == null)
return;
sql.AppendLine()
.Append("\t")
.AppendFormat(
@"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]",
process.BaseProcess.SystemName,
aliasCollection.GetTableAlias(process.BaseProcess),
Constants.IdColumnName,
aliasCollection.GetTableAlias(process),
Constants.BaseIdColumnName);
AddBaseTableJoins(sql, process.BaseProcess, lastProcess, aliasCollection);
}
private static void AddSingleCrossReferenceJoin(StringBuilder sql, SingleCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
@"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0",
join.ReferencedProcess.SystemName,
aliasCollection.GetTableAlias(join.ReferencedProcess),
Constants.IdColumnName,
aliasCollection.GetTableAlias(join.DeclaringProcess),
join.SystemName,
Constants.IsRemovedColumnName);
}
private string GenerateExpressionAffectedItemsQuery(ExpressionDependency dependency)
{
var sql = new StringBuilder();
var aliasCollection = new TableAliasCollection();
var queries = GetJoinQueries(dependency, aliasCollection);
if (queries.Count > 0)
{
sql.AppendLine("WITH");
sql.AppendLine(string.Join(Environment.NewLine + ",", queries));
}
sql.AppendFormat(@"SELECT DISTINCT {0}.[Id]
FROM
[dbo].[{1}] {0}", aliasCollection.GetTableAlias(dependency.DependentProcess), dependency.DependentProcess.SystemName);
var currentProcess = dependency.DependentProcess;
foreach (var @join in dependency.JoinFields)
{
AddBaseTableJoins(sql, currentProcess, @join.DeclaringProcess, aliasCollection);
AddJoin(sql, @join, aliasCollection);
currentProcess = @join.ReferencedProcess;
}
sql.AppendLine().AppendFormat(
"WHERE {0}.[{1}] = 0 AND {2}.[{3}] = @id",
aliasCollection.GetTableAlias(dependency.DependentProcess),
Constants.IsRemovedColumnName,
aliasCollection.GetTableAlias(dependency.JoinFields.Last().ReferencedProcess),
Constants.IdColumnName);
return sql.ToString();
}
