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