private IEnumerable<string> GetSourceFields(IProcessDefinition process, TableFieldDefinition field, string expression, bool onlyRoot)
{
var sourceFields = ExpressionService.GetExpressionSourceFields(expression, onlyRoot).ToArray();
var allSourceFields = sourceFields.Concat(sourceFields.Select(x => x + "Member"));
var localization = field.Localization ?? process.DefaultLocalization;
allSourceFields = allSourceFields.Concat(sourceFields.Select(x => LocalizationUtils.GetLocalizedFieldName(x, localization.Culture.Name)));
return allSourceFields;
}
public void TableFieldDefinition_SystemNameColumnNameAndColumnTypeAreSet_ToStringReturnsCustomValue()
{
const string ColumnName = "column";
const string SystemName = "system";
const ColumnTypes ColumnType = ColumnTypes.Boolean;
var d = new TableFieldDefinition { ColumnName = ColumnName, SystemName = SystemName, ColumnType = ColumnType };
Assert.AreEqual(string.Format("[{0}][{1}][{2}]", SystemName, ColumnName, ColumnType), d.ToString());
}
public void ColumnName_IsReferenceColumnName_IsRefIsTrue()
{
var d = new TableFieldDefinition { ColumnName = "column", ReferencedColumnName = "refColumn" };
foreach (var type in Enum.GetValues(typeof(ColumnTypes)))
{
d.ColumnType = (ColumnTypes)type;
Assert.AreEqual(d.IsRef ? d.ReferencedColumnName : d.ColumnName, d.GetColumnName());
}
}
public void IsRef_TrueIfReferenceOrMultiReference()
{
var d = new TableFieldDefinition();
foreach (var type in Enum.GetValues(typeof(ColumnTypes)))
{
d.ColumnType = (ColumnTypes)type;
Assert.AreEqual(d.ColumnType == ColumnTypes.MultiReference || d.ColumnType == ColumnTypes.Reference, d.IsRef);
}
}
public void ProcessDefinitionExtensions_GetTableNameByFieldNameWithEmptyFieldName_ReturnsNull()
{
var d = new ProcessDefinition("name");
var t = new TableDefinition("table");
var f = new TableFieldDefinition() {SystemName = "field"};
t.FieldList.Add(f);
d.TableList.Add(t);
Assert.AreEqual(null, d.GetTableNameByFieldName(string.Empty));
}
/// <summary>
/// Initializes a new instance of the <see cref="FieldLocalizationInfo"/> class.
/// </summary>
/// <param name="mainField">
/// The main field.
/// </param>
/// <param name="localization">
/// The localization.
/// </param>
/// <exception cref="ArgumentNullException">
/// The <paramref name="mainField"/> parameter is null.
/// </exception>
/// <exception cref="ArgumentNullException">
/// The <paramref name="localization"/> parameter is null.
/// </exception>
public FieldLocalizationInfo(TableFieldDefinition mainField, LocalizationDefinition localization)
{
if (mainField == null)
throw new ArgumentNullException("mainField");
if (localization == null)
throw new ArgumentNullException("localization");
MainField = mainField;
Localization = localization;
}
public void IsBinaryContentReturnTrueForBinaryFields()
{
var binaryTypes = new[] { ColumnTypes.Image };
var d = new TableFieldDefinition();
foreach (var type in binaryTypes)
{
d.ColumnType = type;
Assert.IsTrue(d.IsBinaryField);
}
foreach (var type in (ColumnTypes[])Enum.GetValues(typeof(ColumnTypes)))
{
if (binaryTypes.Any(t => t == type))
{
continue;
}
d.ColumnType = type;
Assert.IsFalse(d.IsBinaryField);
}
}
/// <summary>Gets the audit trigger script for multi cr table.</summary>
/// <param name="table">The table.</param>
/// <param name="fieldDefinition">The field definition.</param>
/// <param name="primaryTable">The primary table.</param>
/// <param name="foreignTable">The foreign table.</param>
/// <returns></returns>
private string GetAuditTriggerScriptForMultiCRTable(ITableDefinition table, TableFieldDefinition fieldDefinition, TableRelationshipDefinition primaryTable, TableRelationshipDefinition foreignTable)
{
if (fieldDefinition.ReferenceDisplayFields.Any(x =>
x.SystemName == fieldDefinition.ReferenceDisplayField
&& (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef)))
return string.Empty;
if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable) || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName))
return string.Empty;
var result = string.Format(@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]'))
drop trigger [dbo].[{0}_Audit];
GO
CREATE TRIGGER [dbo].[{0}_Audit]
ON [dbo].[{0}]
AFTER INSERT,DELETE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED )
RETURN
", table.Name);
if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName))
{
result += string.Format(@"
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'D'
,p.Id
,'{1}'
,'Link removed: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
,NULL
,p.LastModifiedOn
,p.LastModifiedBy
FROM DELETED d
INNER JOIN dbo.{0} p ON p.Id = d.[{4}]
LEFT OUTER JOIN [{2}] si ON si.Id = d.[{5}]
RETURN
END
IF NOT EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'I'
,p.[Id]
,'{1}'
,NULL
,'Link added: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
,p.LastModifiedOn
,p.LastModifiedBy
FROM INSERTED i
INNER JOIN dbo.{0} p ON p.Id = i.[{4}]
LEFT OUTER JOIN [{2}] si ON si.Id = i.[{5}]
RETURN
END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferenceDisplayField, primaryTable.JoinFieldName, foreignTable.JoinFieldName);
}
else
{
result += string.Format(@"
IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'D'
,d.{0}Id
,'{1}'
,'Link removed: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
,NULL
,t.LastModifiedOn
,t.LastModifiedBy
FROM DELETED d
LEFT OUTER JOIN dbo.{2} p ON p.Id = d.[{5}]
INNER JOIN [{4}] spi ON spi.Id = p.ParentId
INNER JOIN dbo.[{0}] t on t.Id = d.{0}Id
RETURN
END
IF NOT EXISTS ( SELECT * FROM DELETED )
BEGIN
INSERT INTO audit.[{0}]
SELECT
'I'
,i.{0}Id
,'{1}'
,NULL
,'Link added: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
,t.LastModifiedOn
,t.LastModifiedBy
FROM INSERTED i
LEFT OUTER JOIN dbo.{2} p ON p.Id = i.[{5}]
INNER JOIN [{4}] spi ON spi.Id = p.ParentId
INNER JOIN dbo.[{0}] t on t.Id = i.{0}Id
RETURN
END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), foreignTable.FKTableName, fieldDefinition.ReferenceDisplayField, fieldDefinition.ReferenceColumnTable, foreignTable.JoinFieldName);
}
return result;
}
/// <summary>
/// Adds the multi cross ref editable child.
/// </summary>
/// <param name="assemblyCode">The assembly code.</param>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
protected override void AddMultiCrossRefEditableChild(StringBuilder assemblyCode, IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
assemblyCode.AppendFormat(
@"
#region {0} Editable Child ({0} Multi CrossRef Item)
[Serializable]
public class {0} : BaseCRInfo<{0}>, ICrossRefItemInfo
{{
", GetEditableChildClassName(table, field));
assemblyCode.Append(GetMultiCrossRefEditableChildProperties(table, field));
assemblyCode.AppendFormat(
@"
internal void ReadData(SafeDataReader reader, MultiCrossRefLoadCriteria.LoadTargets loadTarget, bool markNew)
{{
if (reader == null)
{{
return;
}}
if (reader.HasColumn(""Id""))
{{
LoadProperty(IdProperty, SafeTypeConverter.Convert<int>(reader.GetValue(reader.GetOrdinal(""Id""))));
}}
if (reader.HasColumn(@""{0}""))
{{
LoadProperty({0}Property, SafeTypeConverter.Convert<string>(reader.GetValue(reader.GetOrdinal(@""{0}""))));
}}
var displayName = new StringBuilder();
",
Constants.DerivedProcessColumnName);
foreach (var displayField in field.ReferenceDisplayFields)
{
assemblyCode.AppendFormat(
@"
if (reader.HasColumn(""{1}""))
{{
LoadProperty({0}Property, reader.IsDBNull(""{1}"") ? null : SafeTypeConverter.Convert(reader.GetValue(reader.GetOrdinal(""{1}"")), {0}Property.Type));
if ({0} != null)
{{
if (displayName.Length > 0)
{{
displayName.Append("", "");
}}
displayName.AppendFormat(""{{0}}"", {0});
}}
}}
", displayField.PropertyName, OracleNamesTranslator.Translate(displayField.Alias));
}
assemblyCode.AppendFormat(
@"
LoadProperty(__DisplayNameProperty, displayName.ToString());
if (loadTarget == MultiCrossRefLoadCriteria.LoadTargets.OwnerEntity)
{{
MarkOld();
}}
else
{{
MarkNew();
}}
if (markNew)
{{
MarkNew();
}}
}}");
assemblyCode.AppendFormat(
@"
public static Cebos.Veyron.SharedTypes.ReferenceFieldDefinition GetFieldDefinition()
{{
Cebos.Veyron.SharedTypes.ReferenceFieldDefinition fieldDefinition = null;
{0}
return fieldDefinition;
}}
",
GetReferenceDefinition(table, field, "fieldDefinition"));
assemblyCode.AppendFormat(
@"
public static {0} New{0}()
{{
return DataPortal.CreateChild<{0}>();
}}
public {0} GetCopy(bool markAsNew = true)
{{
var copy = Clone();
if (markAsNew)
copy.MarkNew();
else
copy.MarkOld();
return copy;
}}
", GetEditableChildClassName(table, field));
AddMultiCrossReferenceInfoClassDataAccessMethods(assemblyCode, process, field);
AddCrossRefChildOnGetStateOverride(assemblyCode, field);
AddCrossRefChildOnSetStateOverride(assemblyCode, field);
AddCrossRefChildLoadPropertyOverride(assemblyCode, field);
assemblyCode.AppendLine(
@"
}
#endregion
");
}
/// <summary>
/// Adds the multi cross reference information class data access methods.
/// </summary>
/// <param name="assemblyCode">The assembly code.</param>
/// <param name="process">The process.</param>
/// <param name="field">The field.</param>
private static void AddMultiCrossReferenceInfoClassDataAccessMethods(StringBuilder assemblyCode, IProcessDefinition process, TableFieldDefinition field)
{
if (field.ReferenceField == null)
{
assemblyCode.AppendFormat(
@"
private void Child_DeleteSelf({3} childData)
{{
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var connection = ctx.Connection;
//if (connection.State != ConnectionState.Open) connection.Open();
const string commandText = @""
DELETE
FROM """"{0}""""
WHERE """"{4}"""" = :p_{1}
AND """"{5}"""" = :p_{2}Id
"";
using (var command = new OracleCommand(commandText, connection))
{{
command.BindByName = true;
command.Parameters.Add(""p_{1}"", childData.Id);
command.Parameters.Add(""p_{2}Id"", Id);
var rowsAffetcted = command.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
""The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute."");
}}
}}
}}
private void Child_Insert({3} childData)
{{
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var connection = ctx.Connection;
//if (connection.State != ConnectionState.Open) connection.Open();
const string commandText = @""
INSERT INTO """"{0}""""
(
""""{4}"""",
""""{5}""""
)
VALUES
(
:p_{1},
:p_{2}Id
)"";
using (var command = new OracleCommand(commandText, connection))
{{
command.BindByName = true;
command.Parameters.Add(""p_{1}"", childData.Id);
command.Parameters.Add(""p_{2}Id"", Id);
var rowsAffetcted = command.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
""The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute."");
}}
}}
}}
private void Child_Update(object childData)
{{
//System.Diagnostics.Debugger.Break();
}}
",
OracleNamesTranslator.Translate(field.ReferenceJoinTableName),
field.ReferencedColumnName,
field.ReferenceTableName,
string.Format("{0}Edit", process.Name),
OracleNamesTranslator.Translate(field.ReferencedColumnName),
OracleNamesTranslator.Translate(field.ReferenceTableName + "Id")
);
}
else
{
assemblyCode.AppendFormat(
@"
private void Child_Insert({0} parent)
{{
using (var ctx = ConnectionManager<OracleConnection>.GetManager(Cebos.Veyron.SharedTypes.ServerSettings.ConnectionString, false))
{{
var connection = ctx.Connection;
const string commandText = @""
UPDATE """"{1}""""
SET
""""{2}"""" = :parentId
WHERE """"Id"""" = :childId"";
using (var command = new OracleCommand(commandText, connection))
{{
command.BindByName = true;
command.Parameters.Add(""parentId"", parent.Id);
command.Parameters.Add(""childId"", Id);
var rowsAffetcted = command.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
""The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute."");
}}
}}
}}
",
string.Format("{0}Edit", process.Name),
OracleNamesTranslator.Translate(field.ReferenceField.DefinedIn.SystemName),
OracleNamesTranslator.Translate(field.ReferenceField.SystemName));
assemblyCode.AppendFormat(
@"
private void Child_Update({0} parent)
{{
// Do nothing. Items can only be added or removed.
}}", string.Format("{0}Edit", process.Name));
assemblyCode.AppendFormat(
@"
private void Child_DeleteSelf({0} parent)
{{
var editableRoot = Dynamic{1}.{1}Edit.Get{1}(Id);
if (editableRoot.Id > 0)
{{
editableRoot.Delete();
editableRoot.Save();
}}
}}",
string.Format("{0}Edit", process.Name),
field.ReferenceField.DefinedIn.SystemName);
}
}
/// <summary>
/// Gets the display list editable list child fetch.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
protected override string GetDisplayListEditableListChildFetch(IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
var sb = new StringBuilder();
sb.AppendFormat(
@"
var fieldDefinition = {0}.GetFieldDefinition();
var queryGenerator = new SearchQueryGenerator {{ PageSize = criteria.PageSize, PageNumber = criteria.PageNumber }};
Dynamic{1}.{1}List.AddTables(queryGenerator);
queryGenerator.RefUpdateFields(fieldDefinition);
Dynamic{1}.{1}List.AddFilters(queryGenerator, criteria);
Dynamic{1}.{1}List.AddSortColumns(queryGenerator, criteria);
RaiseListChangedEvents = false;
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
using (var cmd = ctx.Connection.CreateCommand())
{{
cmd.CommandText = queryGenerator.GetBaseValues();
cmd.BindByName = true;
using (var oracleReader = cmd.ExecuteReader())
{{
using (var reader = new SafeDataReader(oracleReader))
{{
if (reader.Read())
{{
TotalRowCount = SafeTypeConverter.Convert<int>(reader.GetValue(0));
}}
}}
}}
}}
var totalPages = TotalRowCount / criteria.PageSize;
if (TotalRowCount % criteria.PageSize > 0)
++totalPages;
PageNumber = System.Math.Max(System.Math.Min(totalPages - 1, criteria.PageNumber), 0);
if (criteria.Id.HasValue)
{{
using (var cmd = ctx.Connection.CreateCommand())
{{
cmd.CommandText = queryGenerator.GetPageForIdQuery();
cmd.BindByName = true;
cmd.Parameters.AddWithValue("":p_id"", criteria.Id.Value);
var rowNumber = cmd.ExecuteScalar();
if (rowNumber == null)
return;
var longRowNumber = (long)rowNumber - 1;
var pageNumber = (int)System.Math.Floor((double)longRowNumber / criteria.PageSize);
PageNumber = pageNumber;
}}
}}
queryGenerator.PageNumber = PageNumber;
using (var cmd = ctx.Connection.CreateCommand())
{{
cmd.CommandText = queryGenerator.RefGetQueryForList(fieldDefinition);
cmd.BindByName = true;
using (var baseReader = cmd.ExecuteReader())
{{
using (var reader = new SafeDataReader(baseReader))
{{
while (reader.Read())
{{
var item = {0}.New{0}();
item.ReadData(reader);
Add(item);
}}
}}
}}
}}
}}
RaiseListChangedEvents = true;
",
GetEditableChildClassName(table, field), field.ReferenceProcess.SystemName);
return sb.ToString();
}
protected override string AddGageRRDatabaseMethods(TableFieldDefinition field)
{
var sb = new StringBuilder();
// sb.Append(@"
// private void DataPortal_Fetch(int id)
// {
// var sql = string.Format(@""
//SELECT Id,
// ItemId,
// NDC,
// XDoubleBar,
// XBarDiff,
// Rp,
// UCLR,
// RDoubleBar
//FROM [dbo].[{0}]"", TableName);
//
// using (var ctx = ConnectionManager<SqlConnection>.GetManager(ServerSettings.ConnectionString, false))
// {
// var cn = ctx.Connection;
// using (var sqlCommand = cn.CreateCommand())
// {
// sqlCommand.CommandText = sql;
// sqlCommand.Parameters.AddWithValue(""@Id"", Id);
//
// using (var reader = new SafeDataReader(sqlCommand.ExecuteReader()))
// {
// ReadBaseInfo(reader);
// }
// }
// }
// }
//");
return sb.ToString();
}
/// <summary>
/// Gets the definition of the view that returns the id mappings for the specified reference field.
/// </summary>
/// <param name="table">
/// The table that contains the reference field.
/// </param>
/// <param name="referenceField">
/// The reference field.
/// </param>
/// <returns>
/// The <see cref="ViewDefinition"/>.
/// </returns>
private ViewDefinition GetReferenceFieldIdMappingsView(ITableDefinition table, TableFieldDefinition referenceField)
{
var viewName = QueryGeneratorUtils.GetReferenceFieldIdMappingsViewName(table.Name, referenceField.SystemName);
var sql = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture, @"CREATE VIEW [dbo].[{0}]
AS
(", viewName);
sql.Append(GetReferenceFieldIdMappingsSelect(table, referenceField));
sql.AppendFormat(CultureInfo.InvariantCulture, @"
)");
return new ViewDefinition { Name = viewName, Definition = sql.ToString() };
}
/// <summary>
/// Generates a script that returns the id mappings for the specified cross-reference field.
/// </summary>
/// <param name="table">
/// The table that contains the cross-reference field.
/// </param>
/// <param name="referenceField">
/// The cross-reference field.
/// </param>
/// <returns>
/// The script.
/// </returns>
private string GetCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition referenceField)
{
if (referenceField.ColumnType == ColumnTypes.Reference)
{
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.SystemName);
}
if (referenceField.ColumnType == ColumnTypes.MultiReference)
{
if (referenceField.ReferenceField != null)
{
// MCR with link field.
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.ReferenceField.SystemName);
}
else
{
// MCR with junction table.
var junctionTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
table.Name,
referenceField.SystemName,
referenceField.ReferenceProcess.SystemName);
var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(table.Name, referenceField.ReferenceProcess.SystemName);
var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(referenceField.ReferenceProcess.SystemName);
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, junctionTableName, masterKey, referenceField.ReferenceProcess.SystemName, childKey);
}
}
throw new NotSupportedException("Invalid reference field.");
}
/// <summary>
/// Gets the type of the SQL.
/// </summary>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
private static string GetSqlType(TableFieldDefinition field)
{
switch (field.ColumnType)
{
case ColumnTypes.String:
case ColumnTypes.Frequency:
{
if (field.AllowRichText)
return "nvarchar2(2000)";
if (field.HasMaxLength)
return "nvarchar2(2000)";
if ((field.Width == null || field.Width.Count == 0))
return "nvarchar2(200)";
return string.Format("nvarchar2({0})", field.Width[0]);
}
case ColumnTypes.Approval:
case ColumnTypes.Reference:
case ColumnTypes.Integer:
case ColumnTypes.MultiReference:
case ColumnTypes.File:
return "number";
case ColumnTypes.Boolean:
return "nchar(1)";
case ColumnTypes.Video:
case ColumnTypes.Choice:
case ColumnTypes.Checklist:
return "CLOB";
case ColumnTypes.DataFilter:
return "nvarchar2(2000)";
case ColumnTypes.Result:
return "number";
case ColumnTypes.Sample:
return "nvarchar2(400)";
case ColumnTypes.SampleType:
case ColumnTypes.SamplingTechnique:
return "nvarchar2(50)";
case ColumnTypes.AutoNumber:
return "nvarchar2(50)";
case ColumnTypes.Double:
return "number";
case ColumnTypes.DateTime:
return "Date";
case ColumnTypes.Image:
return "number";
case ColumnTypes.SPCChartPreferences:
return "nvarchar2(2000)";
case ColumnTypes.Numeric:
if (field.ColumnName == "Id")
return string.Format("integer");
if (!field.HasMaxLength && field.Width != null && field.Width.Any() && !field.AllowRichText)
return string.Format("number({0})", string.Join(",", field.Width));
return string.Format("number(18, 2)");
default:
return string.Format("{0}", field.ColumnType);
}
}
/// <summary>
/// Gets the reverse cross reference child properties load.
/// </summary>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.ArgumentOutOfRangeException"></exception>
protected override string GetReverseCrossRefChildPropertiesLoad(TableFieldDefinition field)
{
var sb = new StringBuilder();
sb.AppendFormat(@"
if (reader.HasColumn(""Id""))
LoadProperty(IdProperty, SafeTypeConverter.Convert<int>(reader.GetValue(reader.GetOrdinal(""Id""))));
");
sb.AppendFormat(@"
if (reader.HasColumn(@""{0}""))
LoadProperty({0}Property, reader.GetString(@""{0}""));
", Constants.DerivedProcessColumnName);
foreach (var displayField in field.ReferenceDisplayFields.OrderBy(f => f.Order))
{
var convertString = string.Format(@"string.Format(""{{0}}"", {0})", displayField.PropertyName);
if (displayField.ColumnType == ColumnTypes.DateTime && displayField.DateTimeFormat.HasValue)
{
convertString =
string.Format(
@"Cebos.Veyron.SharedTypes.Extensions.DateTimeExtensions.ToString(SafeTypeConverter.Convert<System.DateTime>({0}), Cebos.Veyron.SharedTypes.DateTimeFormat.{1})",
displayField.PropertyName,
displayField.DateTimeFormat.Value);
}
if (displayField.NumericType.HasValue)
switch (displayField.NumericType.Value)
{
case NumericTypes.Numeric:
convertString = string.Format(@"string.Format(""{{0:N{1}}}"", {0})", displayField.PropertyName, displayField.NumberOfDigits);
break;
case NumericTypes.Currency:
convertString = string.Format(@"string.Format(""{{0:C{1}}}"", {0})", displayField.PropertyName, displayField.NumberOfDigits);
break;
case NumericTypes.Percentage:
convertString = string.Format(@"string.Format(""{{0:P{1}}}"", {0}.Value / 100.0m)", displayField.PropertyName, displayField.NumberOfDigits);
break;
default:
throw new ArgumentOutOfRangeException();
}
sb.AppendFormat(@"
if (reader.HasColumn(""{1}""))
LoadProperty({0}Property, reader.IsDBNull(""{1}"") ? null : SafeTypeConverter.Convert(reader.GetValue(""{1}""), {0}Property.Type));
", displayField.PropertyName, OracleNamesTranslator.Translate(displayField.Alias));
if (displayField.ColumnType == ColumnTypes.File && !displayField.IsAggregated)
{
sb.AppendFormat(
@"
if(reader.HasColumn(""{1}""))
{{
var systemOption = SystemOptionsInfo.GetSystemOptionsInfo();
LoadProperty({0}UrlProperty, (object)FileHelper.GetFullNavigateUrl(reader.GetDBValue<int>(""{1}"", 0), systemOption));
}}", displayField.PropertyName, OracleNamesTranslator.Translate(displayField.Alias + "Url"));
}
if (displayField.ColumnType == ColumnTypes.Approval)
convertString = string.Format(@"{0}.ToApprovalState().GetDisplayName()", displayField.PropertyName);
if (displayField.ColumnType == ColumnTypes.Image)
continue;
if (displayField.ColumnType == ColumnTypes.Boolean)
// We need special handling of null values for Checkbox fields.
sb.AppendFormat(@"
if (displayName.Length > 0)
{{
displayName.Append("", "");
}}
displayName.Append({0} == null ? {1} : ({0} == true ? {2} : {3}));
", displayField.PropertyName, displayField.UndefinedLabel.ToLiteral(), displayField.TrueLabel.ToLiteral(), displayField.FalseLabel.ToLiteral());
else
sb.AppendFormat(@"
if ({0} != null)
{{
if (displayName.Length > 0)
{{
displayName.Append("", "");
}}
displayName.Append({1});
}}
", displayField.PropertyName, convertString);
}
sb.AppendFormat(@"
LoadProperty(__DisplayNameProperty, displayName.ToString());
");
return sb.ToString();
}
/// <summary>
/// Gets the reverse cross reference read only list child fetch.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
private static string GetReverseCrossRefReadOnlyListChildFetch(IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
var sb = new StringBuilder();
sb.AppendFormat(
@"
var fieldDefinition = {0}.GetFieldDefinition();
var cmdText = new SearchQueryGenerator().RefGetQuery(fieldDefinition);
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var cn = ctx.Connection;
using (var cmd = new OracleCommand(cmdText, ctx.Connection))
{{
cmd.BindByName = true;
cmd.Parameters.Add(""itemId"", itemId);
var baseReader = cmd.ExecuteReader();
using (var reader = new SafeDataReader(baseReader))
{{
IsReadOnly = false;
RaiseListChangedEvents = false;
while (reader.Read())
{{
Add({0}.Get{0}(reader));
}}
IsReadOnly = true;
RaiseListChangedEvents = true;
}}
}}
}}
",
GetEditableChildClassName(table, field));
return sb.ToString();
}
/// <summary>
/// Gets the column alias.
/// </summary>
/// <param name="mainTable">The main table.</param>
/// <param name="columnListType">Type of the column list.</param>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
private static string GetColumnAlias(ITableDefinition mainTable, ColumnListType columnListType, TableFieldDefinition field)
{
var relationship = GetRelationship(mainTable, field);
if (relationship == null)
return string.Empty;
if (columnListType == ColumnListType.InnerSelect || columnListType == ColumnListType.Select)
return string.Format("{0}{1}", relationship.Prefix, field.ReferencedColumnName);
return string.Empty;
}
/// <summary>
/// Gets the type of the SQL.
/// </summary>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
private static string GetSqlType(TableFieldDefinition field)
{
switch (field.ColumnType)
{
case ColumnTypes.String:
case ColumnTypes.Frequency:
{
if (field.AllowRichText)
return "[nvarchar](MAX)";
if (field.HasMaxLength)
return "[nvarchar](MAX)";
if ((field.Width == null || field.Width.Count == 0))
return "[nvarchar](200)";
return string.Format("[nvarchar]({0})", field.Width[0]);
}
case ColumnTypes.Reference:
return "[integer]";
case ColumnTypes.MultiReference:
return "[integer]";
case ColumnTypes.Boolean:
return "[bit]";
case ColumnTypes.Video:
case ColumnTypes.Choice:
case ColumnTypes.Checklist:
return "[xml]";
case ColumnTypes.DataFilter:
return "[nvarchar](max)";
case ColumnTypes.Approval:
return "[integer]";
case ColumnTypes.File:
case ColumnTypes.Image:
case ColumnTypes.GageRR:
return "[integer]";
case ColumnTypes.Result:
return "[float]";
case ColumnTypes.Sample:
return "[nvarchar](400)";
case ColumnTypes.SampleType:
case ColumnTypes.SamplingTechnique:
return "[nvarchar](50)";
case ColumnTypes.AutoNumber:
return "[nvarchar](50)";
case ColumnTypes.Double:
return "[float]";
case ColumnTypes.SPCChartPreferences:
return "[varchar](max)";
case ColumnTypes.Numeric:
if (!field.HasMaxLength && field.Width != null && field.Width.Any() && !field.AllowRichText)
return string.Format("[numeric]({0})", string.Join(",", field.Width));
return string.Format("[numeric](18, 2)");
case ColumnTypes.FieldBackcolor:
case ColumnTypes.Color:
return "[bigint]";
default:
return string.Format("[{0}]", field.ColumnType.ToString());
}
}
private static bool IncludeInFullTextIndex(TableFieldDefinition field)
{
if (!field.GenerateDbColumn)
{
return false;
}
switch (field.ColumnType)
{
case ColumnTypes.AutoNumber:
case ColumnTypes.String:
case ColumnTypes.Frequency:
case ColumnTypes.SampleType:
case ColumnTypes.SamplingTechnique:
return true;
default:
return false;
}
}
/// <summary>
/// Gets the default value clause.
/// </summary>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
private static string GetDefaultValueClause(TableFieldDefinition field)
{
if (field == null || string.IsNullOrEmpty(field.DefaultValue))
return string.Empty;
switch (field.ColumnType)
{
case ColumnTypes.String:
return string.Format(" DEFAULT('{0}') NOT NULL", field.DefaultValue);
default:
return string.Format(" DEFAULT({0}) NOT NULL", field.DefaultValue);
}
}
/// <summary>
/// Gets the multi cross reference editable list child fetch.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
protected override string GetMultiCrossRefEditableListChildFetch(IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
var result = new StringBuilder();
if (field.ReferenceField != null)
{
// Don't load the items if MCR field uses a single CR field as link field,
// otherwise MCR items will be relinked when the parent item is copied.
result.AppendFormat(@"
if (criteria.MarkNew)
return;
");
}
result.AppendFormat(
@"
var queryGenerator = new SearchQueryGenerator();
var fieldDefinition = {0}.GetFieldDefinition();
var cmdText = criteria.LoadTarget == MultiCrossRefLoadCriteria.LoadTargets.OwnerEntity ?
queryGenerator.RefGetQuery(fieldDefinition) :
queryGenerator.RefGetQueryForItem(fieldDefinition);
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
using (var cmd = new OracleCommand(cmdText, ctx.Connection))
{{
cmd.BindByName = true;
cmd.Parameters.Add(""itemId"", criteria.Id);
var baseReader = cmd.ExecuteReader();
using (var reader = new SafeDataReader(baseReader))
{{
RaiseListChangedEvents = false;
while (reader.Read())
{{
var item = {0}.New{0}();
item.ReadData(reader, criteria.LoadTarget, criteria.MarkNew);
Add(item);
}}
RaiseListChangedEvents = true;
}}
}}
}}
",
GetEditableChildClassName(table, field));
return result.ToString();
}
/// <summary>
/// Generates a script that returns the id mappings for the specified reference (cross-reference, reverse cross-reference or tree-view) field.
/// </summary>
/// <param name="table">
/// The table that contains the reference field.
/// </param>
/// <param name="field">
/// The reference field.
/// </param>
/// <returns>
/// The script.
/// </returns>
private string GetReferenceFieldIdMappingsSelect(ITableDefinition table, TableFieldDefinition field)
{
if (field.IsRef)
{
return GetCrossReferenceIdMappingsSelect(table, field);
}
if (field.IsReverseRef)
{
return GetReverseCrossReferenceIdMappingsSelect(table, field);
}
throw new NotSupportedException("Invalid reference field.");
}
/// <summary>
/// Gets the cross reference editable list data portal fetch methods.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
/// <returns>System.String.</returns>
protected override string GetCrossRefEditableListDataPortalFetchMethods(IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
var sb = new StringBuilder();
sb.AppendFormat(
@"
private void DataPortal_Fetch(Cebos.Veyron.SharedTypes.PagedCriteria criteria)
{{
var fieldDefinition = {0}.GetFieldDefinition();
var queryGenerator = new SearchQueryGenerator {{ PageSize = criteria.PageSize, PageNumber = criteria.PageNumber }};
Dynamic{3}.{3}List.AddTables(queryGenerator);
queryGenerator.RefUpdateFields(fieldDefinition);
Dynamic{3}.{3}List.AddFilters(queryGenerator, criteria);
Dynamic{3}.{3}List.AddSortColumns(queryGenerator, criteria);
RaiseListChangedEvents = false;
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var cmdText = queryGenerator.GetBaseValues();
using (var cmd = new OracleCommand(cmdText, ctx.Connection))
{{
cmd.BindByName = true;
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{{
if (reader.Read())
{{
{1} = SafeTypeConverter.Convert<int>(reader.GetValue(0));
}}
}}
}}
cmdText = queryGenerator.GenerateQuery();
if (criteria.Id.HasValue && criteria.SingleResult != true)
{{
getPageForId(queryGenerator, criteria.Id.Value, criteria);
return;
}}
using (var cmd = new OracleCommand(cmdText, ctx.Connection))
{{
var baseReader = cmd.ExecuteReader();
using (var reader = new SafeDataReader(baseReader))
{{
while (reader.Read())
{{
var item = {0}.New{0}();
item.ReadData(reader, MultiCrossRefLoadCriteria.LoadTargets.ReferencedEntity, false);
Add(item);
}}
}}
}}
{2} = criteria.PageNumber;
}}
RaiseListChangedEvents = true;
}}
private void getPageForId(SearchQueryGenerator searchQueryGenerator, int id, PagedCriteria criteria)
{{
criteria.Id = null;
var cmdText = searchQueryGenerator.GetPageForIdQuery();
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
using (var cmd = new OracleCommand(cmdText, ctx.Connection))
{{
cmd.BindByName = true;
cmd.Parameters.Add(""p_id"", id);
var rowNumber = cmd.ExecuteScalar();
if (rowNumber != null)
{{
var longRowNumber = (long)rowNumber - 1;
var pageNumber = (int)Math.Floor((double)longRowNumber / criteria.PageSize);
criteria.PageNumber = pageNumber;
PageNumber = pageNumber;
DataPortal_Fetch(criteria);
}}
else
{{
DataPortal_Fetch(criteria);
}}
}}
}}
}}
",
GetEditableChildClassName(table, field),
Constants.TotalRowCountPropertyName,
Constants.PageNumberPropertyName,
field.ReferenceProcess.SystemName);
return sb.ToString();
}
/// <summary>
/// Generates a script that returns the id mappings for the specified reverse cross-reference or tree-view field.
/// </summary>
/// <param name="table">
/// The table that contains the reverse cross-reference or tree-view field.
/// </param>
/// <param name="reverseReferenceField">
/// The reverse cross-reference or tree-view field.
/// </param>
/// <returns>
/// The script.
/// </returns>
private string GetReverseCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition reverseReferenceField)
{
var referenceField = reverseReferenceField.ReferenceField;
if (reverseReferenceField.ColumnType == ColumnTypes.ReverseReference)
{
if (referenceField.ColumnType == ColumnTypes.Reference)
{
if (reverseReferenceField.ShowLatestVersion)
{
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId]
FROM [dbo].[{0}] t1
CROSS APPLY (
SELECT TOP 1 t2.[Id]
FROM [dbo].[{1}] t2
WHERE t2.[Id] = (
SELECT TOP 1 t3.[Id]
FROM [dbo].[{1}] t3
WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}]
ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
ORDER BY t2.[Id]
) t4
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName);
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
CROSS APPLY (
SELECT TOP 1 [Id]
FROM [dbo].[{1}]
WHERE [IsRemoved] = 0 AND [{2}] = t1.[Id]) t2
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName);
}
if (referenceField.ColumnType == ColumnTypes.MultiReference)
{
if (referenceField.ReferenceField != null)
{
// MCR with link field.
// Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version.
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName);
}
// MCR with join table.
var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
referenceField.DefinedIn.SystemName,
referenceField.SystemName,
table.Name);
var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name);
var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name);
if (reverseReferenceField.ShowLatestVersion)
{
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t5.[Id] AS [RefId]
FROM [dbo].[{0}] t1
CROSS APPLY (
SELECT TOP 1 t2.[Id]
FROM [dbo].[{1}] t2
WHERE t2.[Id] = (
SELECT TOP 1 t3.[Id]
FROM [dbo].[{1}] t3
INNER JOIN [dbo].[{2}] t4 ON t4.[{3}] = t3.[Id]
WHERE t3.[IsRemoved] = 0 AND t3.[{4}] = t2.[{4}] AND t4.[{5}] = t1.[Id]
ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
ORDER BY t2.[Id]
) t5
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, joinTableName, masterKey, Constants.VersionSeriesIdColumnName, childKey);
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId]
FROM [dbo].[{0}] t1
CROSS APPLY (
SELECT TOP 1 t3.[Id]
FROM [dbo].[{1}] t2
INNER JOIN [dbo].[{2}] t3 ON t3.[Id] = t2.[{3}] AND t3.[IsRemoved] = 0
WHERE t2.[{4}] = t1.[Id]
ORDER BY t3.[Id]) t4
WHERE t1.[IsRemoved] = 0
", table.Name, joinTableName, referenceField.DefinedIn.SystemName, masterKey, childKey);
}
if (referenceField.ColumnType == ColumnTypes.Checklist)
{
return string.Format(
CultureInfo.InvariantCulture,
@"
SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
",
table.Name,
QueryGeneratorUtils.GetChecklistJoinTableName(referenceField.DefinedIn.SystemName, referenceField.SystemName),
QueryGeneratorUtils.GetChecklistJoinTableChildKey(referenceField.ReferencedProcess.SystemName),
referenceField.DefinedIn.SystemName,
QueryGeneratorUtils.GetChecklistJoinTableMasterKey(referenceField.DefinedIn.SystemName));
}
throw new NotSupportedException("Invalid reference field.");
}
if (reverseReferenceField.ColumnType == ColumnTypes.ReverseMultiReference)
{
if (referenceField.ColumnType == ColumnTypes.Reference)
{
if (reverseReferenceField.ShowLatestVersion)
{
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0 AND t2.[Id] = (
SELECT TOP 1 t3.[Id]
FROM [dbo].[{1}] t3
WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}]
ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName);
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName);
}
if (referenceField.ColumnType == ColumnTypes.MultiReference)
{
if (referenceField.ReferenceField != null)
{
// MCR with link field.
// Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version.
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName);
}
// MCR with join table.
var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
referenceField.DefinedIn.SystemName,
referenceField.SystemName,
table.Name);
var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name);
var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name);
if (reverseReferenceField.ShowLatestVersion)
{
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0 AND t3.[Id] = (
SELECT TOP 1 t4.[Id]
FROM [dbo].[{3}] t4
INNER JOIN [dbo].[{1}] t5 ON t5.[{4}] = t4.[Id]
WHERE t4.[IsRemoved] = 0 AND t4.[{5}] = t3.[{5}] AND t5.[{2}] = t1.[Id]
ORDER BY LEN(t4.[VersionNumber]) DESC, t4.[VersionNumber] DESC)
", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey, Constants.VersionSeriesIdColumnName);
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey);
}
throw new NotSupportedException("Invalid reference field.");
}
if (reverseReferenceField.ColumnType == ColumnTypes.TreeView)
{
if (!string.IsNullOrEmpty(reverseReferenceField.ReferenceMatchField) && reverseReferenceField.ReferenceMatchField != Constants.IdColumnName)
{
var matchField = table.FieldList.FirstOrDefault(f => f.SystemName == reverseReferenceField.ReferenceMatchField);
if (matchField == null)
{
throw new InvalidOperationException(
string.Format("Cannot find match field '{0}' in table '{1}'.", reverseReferenceField.ReferenceMatchField, table.Name));
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
INNER JOIN [dbo].[{3}] t3 ON t3.[{4}] = t2.[Id] AND t3.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, matchField.ReferenceProcess.SystemName, matchField.ColumnName, referenceField.DefinedIn.SystemName, referenceField.SystemName);
}
return string.Format(CultureInfo.InvariantCulture, @"
SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
FROM [dbo].[{0}] t1
INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
WHERE t1.[IsRemoved] = 0
", table.Name, reverseReferenceField.ReferenceProcess.SystemName, referenceField.SystemName);
}
throw new NotSupportedException("Invalid reference field.");
}
public void PropertiesGetSet_Test()
{
var d = new TableFieldDefinition();
TestsHelper.TestPublicPropertiesGetSet(d, x => x.IsFilterable);
}
private static string GetCulture(TableFieldDefinition field)
{
if (field.Localization != null)
return field.Localization.Culture.Name.ToLiteral();
return "Cebos.Veyron.SharedTypes.Constants.DefaultCulture";
}
public void GenerateDbColumn_IsTrueByDefault()
{
var d = new TableFieldDefinition();
Assert.IsTrue(d.GenerateDbColumn);
}
private static string GetFieldExpression(TableFieldDefinition field)
{
if (field.IsCalculated)
return field.CalculatedExpression;
if (field.HasDefaultExpression)
return field.DefaultValueExpression;
throw new ArgumentException(
string.Format(CultureInfo.InvariantCulture, @"The field ""{0}"" doesn't have a default or calculated expression.", field.SystemName),
"field");
}
/// <summary>
/// Adds the display list editable child.
/// </summary>
/// <param name="assemblyCode">The assembly code.</param>
/// <param name="process">The process.</param>
/// <param name="table">The table.</param>
/// <param name="field">The field.</param>
protected override void AddDisplayListEditableChild(StringBuilder assemblyCode, IProcessDefinition process, ITableDefinition table, TableFieldDefinition field)
{
assemblyCode.AppendFormat(
@"
#region {0} Editable Child ({0} Display List Ref Item)
[Serializable]
public class {0} : BaseDlInfo<{0}>, IDisplayListItemInfo
{{
", GetEditableChildClassName(table, field));
assemblyCode.Append(GetDisplaylistRefEditableChildProperties(table, field));
assemblyCode.AppendFormat(
@"
internal void ReadData(SafeDataReader reader)
{{
if (reader == null)
{{
return;
}}
if (reader.HasColumn(""Id""))
{{
LoadProperty(IdProperty, SafeTypeConverter.Convert<int>(reader.GetValue(reader.GetOrdinal(""Id""))));
}}
var displayName = new StringBuilder();
");
foreach (var displayField in field.ReferenceDisplayFields)
{
assemblyCode.AppendFormat(
@"
if (reader.HasColumn(""{1}""))
{{
LoadProperty({0}Property, reader.IsDBNull(""{1}"") ? null : SafeTypeConverter.Convert(reader.GetValue(reader.GetOrdinal(""{1}"")), {0}Property.Type));
if ({0} != null)
{{
if (displayName.Length > 0)
{{
displayName.Append("", "");
}}
displayName.AppendFormat(""{{0}}"", {0});
}}
}}
", displayField.PropertyName, displayField.Alias);
if (displayField.ColumnType == ColumnTypes.File)
assemblyCode.AppendFormat(
@"
if(reader.HasColumn(""{0}Id""))
{{
var systemOption = SystemOptionsInfo.GetSystemOptionsInfo();
LoadProperty({0}UrlProperty, FileHelper.GetFullNavigateUrl(reader.GetDBValue<int>(""{0}Id"", 0), systemOption));
}}", displayField.Alias);
}
assemblyCode.AppendFormat(
@"
LoadProperty(__DisplayNameProperty, displayName.ToString());
MarkOld();
}}");
assemblyCode.AppendFormat(
@"
public static Cebos.Veyron.SharedTypes.ReferenceFieldDefinition GetFieldDefinition()
{{
Cebos.Veyron.SharedTypes.ReferenceFieldDefinition fieldDefinition = null;
{0}
return fieldDefinition;
}}
",
GetReferenceDefinition(table, field, "fieldDefinition"));
assemblyCode.AppendFormat(
@"
public static {0} New{0}()
{{
return DataPortal.CreateChild<{0}>();
}}
private void Child_DeleteSelf({4} childData)
{{
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var connection = ctx.Connection;
//if (connection.State != ConnectionState.Open) connection.Open();
const string commandText = @""
DELETE
FROM """"{1}""""
WHERE """"{5}"""" = :p_{2}
AND """"{6}"""" = :p_{3}Id
"";
using (var command = new OracleCommand(commandText, connection))
{{
command.BindByName = true;
command.Parameters.Add(""p_{2}"", childData.Id);
command.Parameters.Add(""p_{3}Id"", Id);
var rowsAffetcted = command.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
""The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute."");
}}
}}
}}
private void Child_Insert({4} childData)
{{
using (var ctx = ConnectionManager<OracleConnection>.GetManager(ServerSettings.ConnectionString, false))
{{
var connection = ctx.Connection;
//if (connection.State != ConnectionState.Open) connection.Open();
const string commandText = @""
INSERT INTO """"{1}""""
(
""""{5}"""",
""""{6}""""
)
VALUES
(
:p_{2},
:p_{3}Id
)"";
using (var command = new OracleCommand(commandText, connection))
{{
command.BindByName=true;
command.Parameters.Add(""p_{2}"", childData.Id);
command.Parameters.Add(""p_{3}Id"", Id);
var rowsAffetcted = command.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
""The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute."");
}}
}}
}}
private void Child_Update(object childData)
{{
//System.Diagnostics.Debugger.Break();
}}
{7}
}}
#endregion
",
GetEditableChildClassName(table, field), // 0
OracleNamesTranslator.Translate(field.ReferenceJoinTableName), // 1
field.ReferencedColumnName, // 2
field.ReferenceTableName, // 3
string.Format("{0}Edit", process.Name), // 4
OracleNamesTranslator.Translate(field.ReferencedColumnName), // 5
OracleNamesTranslator.Translate(field.ReferenceTableName + "Id"), // 6
AddDisplayListEditableChildToFilterStringMethod(field) // 7
);
}
