/// <summary>
/// Gets the join table alias.
/// </summary>
/// <param name="joinField">The join field.</param>
/// <returns>System.String.</returns>
public string GetJoinTableAlias(MultiCrossReferenceFieldDefinition joinField)
{
if (!_joinTableAliases.ContainsKey(joinField))
_joinTableAliases[joinField] = "t" + _tableAliasIndex++;
return _joinTableAliases[joinField];
}
/// <summary>
/// Gets the resolve version master query.
/// </summary>
/// <param name="field">The field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
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
""{5}"" v
INNER JOIN ""{6}"" jt ON jt.""{7}"" = v.""{1}""
WHERE v.""{8}"" = 0
)",
aliasCollection.GetResolveVersionMasterQueryName(field),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId),
OracleNamesTranslator.Translate(GetJoinTableChildKey(field)),
OracleNamesTranslator.Translate(field.DeclaringProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(field)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(field)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
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 multi cross reference join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private void AddMultiCrossReferenceJoin(
StringBuilder sql, MultiCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
if (join.LinkField == null)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\"",
OracleNamesTranslator.Translate(GetJoinTableName(join)),
aliasCollection.GetJoinTableAlias(join),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(join)),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName))
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\" AND {1}.\"{5}\" = 0",
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
aliasCollection.GetTableAlias(join.ReferencedProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetJoinTableAlias(join),
OracleNamesTranslator.Translate(GetJoinTableChildKey(join)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
else
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\" AND {1}.\"{5}\" = 0",
OracleNamesTranslator.Translate(join.LinkField.DeclaringProcess.SystemName),
aliasCollection.GetTableAlias(join.LinkField.DeclaringProcess),
OracleNamesTranslator.Translate(join.LinkField.SystemName),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
}
/// <summary>
/// Gets the join table child key.
/// </summary>
/// <param name="join">The join.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.InvalidOperationException">When link field is not null, join table is not generated.</exception>
protected virtual string GetJoinTableChildKey(MultiCrossReferenceFieldDefinition join)
{
if (join.LinkField != null)
throw new InvalidOperationException("When link field is not null, join table is not generated.");
return string.Format("{0}Id", join.ReferencedProcess.SystemName);
}
/// <summary>
/// Gets the join table master key.
/// </summary>
/// <param name="join">The join.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.InvalidOperationException">When link field is not null, join table is not generated.</exception>
protected virtual string GetJoinTableMasterKey(MultiCrossReferenceFieldDefinition join)
{
if (join.LinkField != null)
throw new InvalidOperationException("When link field is not null, join table is not generated.");
var masterKey = string.Format("{0}Id", join.DeclaringProcess.SystemName);
if (join.DeclaringProcess.SystemName == join.ReferencedProcess.SystemName)
masterKey = "Master" + masterKey;
return masterKey;
}
/// <summary>
/// Gets the name of the resolve version master query.
/// </summary>
/// <param name="joinField">The join field.</param>
/// <returns>System.String.</returns>
public string GetResolveVersionMasterQueryName(MultiCrossReferenceFieldDefinition joinField)
{
if (!_versionMasterQueryNames.ContainsKey(joinField))
_versionMasterQueryNames[joinField] = "RVM" + _queryIndex++;
return _versionMasterQueryNames[joinField];
}
/// <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));
}
/// <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));
}
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 void AddMultiCrossReferenceJoin(
StringBuilder sql, MultiCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
if (join.LinkField == null)
{
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);
}
else
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0",
join.LinkField.DeclaringProcess.SystemName,
aliasCollection.GetTableAlias(join.LinkField.DeclaringProcess),
join.LinkField.SystemName,
aliasCollection.GetTableAlias(join.DeclaringProcess),
Constants.IdColumnName,
Constants.IsRemovedColumnName);
}
}
