private static string GetResolveVersionMasterQuery(SingleCrossReferenceFieldDefinition field, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT
v.[{1}]
,v.[{2}]
,(CASE WHEN v.[{3}] IS NULL OR v.[{3}] = 0 THEN v.[{1}] ELSE v.[{3}] END) [{3}]
,v.[{4}]
FROM
[dbo].[{5}] v
WHERE v.[{6}] = 0
)",
aliasCollection.GetResolveVersionMasterQueryName(field),
Constants.IdColumnName,
Constants.VersionNumber,
Constants.VersionMasterId,
field.SystemName,
field.DeclaringProcess.SystemName,
Constants.IsRemovedColumnName);
return result.ToString();
}
private string GetResolveVersionMasterQuery(MultiCrossReferenceFieldDefinition field, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT
[{1}]
,[{2}]
,(CASE WHEN v.[{3}] IS NULL OR v.[{3}] = 0 THEN v.[{1}] ELSE v.[{3}] END) [{3}]
,[{4}]
FROM
[dbo].[{5}] v
INNER JOIN [dbo].[{6}] jt ON jt.[{7}] = v.[{1}]
WHERE v.[{8}] = 0
)",
aliasCollection.GetResolveVersionMasterQueryName(field),
Constants.IdColumnName,
Constants.VersionNumber,
Constants.VersionMasterId,
GetJoinTableChildKey(field),
field.DeclaringProcess.SystemName,
GetJoinTableName(field),
GetJoinTableMasterKey(field),
Constants.IsRemovedColumnName);
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 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, TableAliasCollection aliasCollection)
{
var singleCrossReference = join.ReferenceField as SingleCrossReferenceFieldDefinition;
if (singleCrossReference != null)
return GetReverseCrossReferenceItemsQuery(join, singleCrossReference, aliasCollection);
var multiCrossReference = join.ReferenceField as MultiCrossReferenceFieldDefinition;
if (multiCrossReference != null)
return GetReverseCrossReferenceItemsQuery(join, multiCrossReference, aliasCollection);
var checklist = join.ReferenceField as ChecklistFieldDefinition;
if (checklist != null)
return GetReverseCrossReferenceItemsQuery(join, checklist, aliasCollection);
throw new InvalidOperationException();
}
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 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 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 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 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 void AddJoin(StringBuilder sql, ReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
var singleCrossReferenceJoin = join as SingleCrossReferenceFieldDefinition;
if (singleCrossReferenceJoin != null)
AddSingleCrossReferenceJoin(sql, singleCrossReferenceJoin, aliasCollection);
var multiCrossReferenceJoin = join as MultiCrossReferenceFieldDefinition;
if (multiCrossReferenceJoin != null)
AddMultiCrossReferenceJoin(sql, multiCrossReferenceJoin, aliasCollection);
var checklistJoin = join as ChecklistFieldDefinition;
if (checklistJoin != null)
AddChecklistJoin(sql, checklistJoin, aliasCollection);
var reverseCrossReference = join as ReverseCrossReferenceFieldDefinition;
if (reverseCrossReference != null)
AddReverseCrossReferenceJoin(sql, reverseCrossReference, aliasCollection);
}
private List<string> GetJoinQueries(ExpressionDependency dependency, TableAliasCollection aliasCollection)
{
var queries = new List<string>();
foreach (var joinField in dependency.JoinFields)
{
var singleReverseCrossReference = joinField as SingleReverseCrossReferenceFieldDefinition;
if (singleReverseCrossReference != null)
queries.Add(GetReverseCrossReferenceItemsQuery(singleReverseCrossReference, aliasCollection));
var multiReverseCrossReference = joinField as MultiReverseCrossReferenceFieldDefinition;
if (multiReverseCrossReference != null)
queries.Add(GetReverseCrossReferenceItemsQuery(multiReverseCrossReference, aliasCollection));
}
return queries;
}
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();
}
