/// <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> /// Gets the reverse cross reference items query. /// </summary> /// <param name="join">The join.</param> /// <param name="aliasCollection">The alias collection.</param> /// <returns>System.String.</returns> /// <exception cref="System.InvalidOperationException"></exception> 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(); }
/// <summary> /// Creates the join. /// </summary> /// <param name="process">The process.</param> /// <param name="field">The field.</param> /// <returns>ReferenceFieldDefinition.</returns> /// <exception cref="System.InvalidOperationException"> /// </exception> private ReferenceFieldDefinition CreateJoin(ProcessDefinition process, FieldEdit field) { var declaringProcess = FindBaseProcess(process, field.GetBaseCopy().GetParent<IProcessEdit>().SystemName); switch (field.GetColumnType()) { case ColumnTypes.Reference: { var step = field.StepList.OfType<CrossRefRequiredStepEdit>().FirstOrDefault(); if (step == null || !step.CrossRefProcessId.HasValue) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = GetPublishedProcessInfo(step.CrossRefProcessId.Value); var join = new SingleCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = CreateProcessDefinition(referencedProcess) }; return join; } case ColumnTypes.MultiReference: { var step = field.StepList.OfType<CrossRefRequiredStepEdit>().FirstOrDefault(); if (step == null || !step.CrossRefProcessId.HasValue) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = GetPublishedProcessInfo(step.CrossRefProcessId.Value); var join = new MultiCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = CreateProcessDefinition(referencedProcess) }; if (!string.IsNullOrEmpty(step.LinkFieldSystemName)) join.LinkField = (SingleCrossReferenceFieldDefinition)CreateJoin(join.ReferencedProcess, step.LinkFieldSystemName); return join; } case ColumnTypes.ReverseReference: { var step = field.StepList.OfType<ReverseCrossRefRequiredStepEdit>().FirstOrDefault(); if (step == null || !step.ReverseCrossRefProcessId.HasValue) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var optionsStep = field.StepList.OfType<ReverseCrossRefOptionsStepEdit>().FirstOrDefault(); var referencedProcess = CreateProcessDefinition(GetPublishedProcessInfo(step.ReverseCrossRefProcessId.Value)); var join = new SingleReverseCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = referencedProcess, ReferenceField = CreateJoin(referencedProcess, step.CrossRefFieldName), ShowLatestVersion = optionsStep != null && optionsStep.ShowLatestVersion }; return join; } case ColumnTypes.ReverseMultiReference: { var step = field.StepList.OfType<ReverseCrossRefRequiredStepEdit>().FirstOrDefault(); if (step == null || !step.ReverseCrossRefProcessId.HasValue) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var optionsStep = field.StepList.OfType<ReverseCrossRefOptionsStepEdit>().FirstOrDefault(); var referencedProcess = CreateProcessDefinition(GetPublishedProcessInfo(step.ReverseCrossRefProcessId.Value)); var join = new MultiReverseCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = referencedProcess, ReferenceField = CreateJoin(referencedProcess, step.CrossRefFieldName), ShowLatestVersion = optionsStep != null && optionsStep.ShowLatestVersion }; return join; } case ColumnTypes.Checklist: { var step = field.StepList.OfType<ChecklistSettingsStepEdit>().FirstOrDefault(); if (step == null || string.IsNullOrEmpty(step.AnswerProcessSystemName)) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = GetPublishedProcessInfo(step.AnswerProcessSystemName); var join = new ChecklistFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = CreateProcessDefinition(referencedProcess) }; return join; } } throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); }
/// <summary> /// Creates the join. /// </summary> /// <param name="process">The process.</param> /// <param name="field">The field.</param> /// <returns>ReferenceFieldDefinition.</returns> /// <exception cref="System.InvalidOperationException"> /// </exception> private ReferenceFieldDefinition CreateJoin(ProcessDefinition process, Process.FieldInfo field) { var declaringProcess = FindBaseProcess(process, field.DefinedIn); switch (field.ColumnType) { case ColumnTypes.Reference: { var fieldInfo = CrossRefFieldInfo.GetCrossRefFieldInfo(field.Id); if (fieldInfo == null || fieldInfo.CrossRefProcessId == 0) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = GetPublishedProcessInfo(fieldInfo.CrossRefProcessId); var join = new SingleCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = CreateProcessDefinition(referencedProcess) }; return join; } case ColumnTypes.MultiReference: { var fieldInfo = CrossRefFieldInfo.GetCrossRefFieldInfo(field.Id); if (fieldInfo == null || fieldInfo.CrossRefProcessId == 0) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = CreateProcessDefinition(GetPublishedProcessInfo(fieldInfo.CrossRefProcessId)); var join = new MultiCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = referencedProcess }; if (!string.IsNullOrEmpty(fieldInfo.LinkFieldSystemName)) join.LinkField = (SingleCrossReferenceFieldDefinition)CreateJoin(referencedProcess, fieldInfo.LinkFieldSystemName); return join; } case ColumnTypes.ReverseReference: { var fieldInfo = ReverseCrossRefFieldInfo.GetReverseCrossRefFieldInfo(field.Id); if (fieldInfo == null || fieldInfo.ReferenceProcessId == 0) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = CreateProcessDefinition(GetPublishedProcessInfo(fieldInfo.ReferenceProcessId)); var join = new SingleReverseCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = referencedProcess, ReferenceField = CreateJoin(referencedProcess, fieldInfo.ReferenceField), ShowLatestVersion = fieldInfo.ShowLatestVersion }; return join; } case ColumnTypes.ReverseMultiReference: { var fieldInfo = ReverseCrossRefFieldInfo.GetReverseCrossRefFieldInfo(field.Id); if (fieldInfo == null || fieldInfo.ReferenceProcessId == 0) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = CreateProcessDefinition(GetPublishedProcessInfo(fieldInfo.ReferenceProcessId)); var join = new MultiReverseCrossReferenceFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = referencedProcess, ReferenceField = CreateJoin(referencedProcess, fieldInfo.ReferenceField), ShowLatestVersion = fieldInfo.ShowLatestVersion }; return join; } case ColumnTypes.Checklist: { var fieldInfo = ChecklistFieldInfo.GetChecklistFieldInfo(field.Id); if (fieldInfo == null || fieldInfo.CrossRefProcessId == 0) throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); var referencedProcess = GetPublishedProcessInfo(fieldInfo.CrossRefProcessId); var join = new ChecklistFieldDefinition { SystemName = field.SystemName, DeclaringProcess = declaringProcess, ReferencedProcess = CreateProcessDefinition(referencedProcess) }; return join; } } throw new InvalidOperationException( string.Format(CultureInfo.InvariantCulture, "Field \"{0}\" is not a valid reference field.", field.SystemName)); }
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(); }