public CreateFrom(UserInfo userInfo, string projectionNameFrom, string projectionNameTarget, ProjectionActionType actionTypeName)
{
this.userInfo = userInfo;
this.projectionNameFrom = projectionNameFrom;
this.projectionNameTarget = projectionNameTarget;
this.actionTypeName = actionTypeName;
this.projectionFrom = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionNameFrom);
}
public GetByIdItemGrid(UserInfo userInfo, vwProjectionRelation relation, vwProjection parentEntity)
{
this.userInfo = userInfo;
this.relation = relation;
this.columns = settings.GetVwProjectionColumnList(userInfo.ProfileId, relation.ProjectionRelation_ChildProjectionName).Where(x =>
(x.Column_IsVisibleOnItemGrid || (x.Column_IsPrimaryKey) || x.Column_HiddenData) &&
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2) &&
!SystemEnums.SysColumns.Contains(x.DbColumn_Name)
).ToList();
this.vwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Id == relation.ProjectionRelation_ChildProjection_FK);
this.parentEntity = parentEntity;
}
public ItemGridInsert(UserInfo userInfo, vwProjectionRelation relation, vwProjection parentEntity, int parentEntityType)
{
this.userInfo = userInfo;
this.relation = relation;
this.columns = settings.GetVwProjectionColumnList(userInfo.ProfileId, relation.ProjectionRelation_ChildProjectionName).Where(x =>
x.AppColumnType_Id != 1 &&
!x.Column_IsReadOnly &&
!x.Column_IsPrimaryKey &&
x.ProjectionColumn_Id != relation.ProjectionColumn1_Id &&
(x.Column_IsVisibleOnItemGrid || x.Column_HiddenData || x.Column_InsertDefaultValue != null) &&
x.AccessRightsType_Id == 2
).ToList();
this.entity = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Id == relation.ProjectionRelation_ChildProjection_FK);
this.parentEntity = parentEntity;
this.parentEntityType = parentEntityType;
}
public Insert2_ItemGridInsert(UserInfo userInfo, vwProjectionRelation relation, vwProjection parentEntity, int parentEntityType, string parentIdVarName)
{
this.parentIdVarName = parentIdVarName;
this.userInfo = userInfo;
this.relation = relation;
this.childProjectionName = relation.ProjectionRelation_ChildProjectionName;
this.columns = settings.GetVwProjectionColumnList(userInfo.ProfileId, relation.ProjectionRelation_ChildProjectionName).Where(x =>
x.AppColumnType_Id != 1 &&
!x.Column_IsReadOnly &&
!x.Column_IsPrimaryKey &&
x.ProjectionColumn_Id != relation.ProjectionColumn1_Id &&
(x.Column_IsVisibleOnItemGrid || x.Column_HiddenData) &&
x.AccessRightsType_Id == 2
).ToList();
this.entity = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Id == relation.ProjectionRelation_ChildProjection_FK);
this.parentEntity = parentEntity;
this.parentEntityType = parentEntityType;
this.subItems = settings.VwProjectionRelationList.Where(x =>
x.ProjectionRelation_ParentProjection_FK == this.entity.Projection_Id &&
x.ProjectionRelation_ChildProjectionName != "Participant" &&
x.ProjectionRelation_ChildProjectionProfile_Id == userInfo.ProfileId &&
((x.ProjectionRelation_ChildProjectionAccessRight & (long)2) == (long)2) &&
x.ProjectionRelation_Type_FK == 1
).ToList();
}
public static bool Validate(this JObject jobject, string projectionName, UserInfo userInfo, out IList <ValidationError> errList)
{
try
{
Settings settings = Settings.GetInstance;
vwProjection vwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionName);
List <vwProjectionColumn> columnsAll = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
x.AppColumnType_Id != 1 &&
!x.Column_IsReadOnly &&
!x.Column_IsPrimaryKey &&
(x.Column_IsVisibleOnForm || x.Column_HiddenData) &&
x.AccessRightsType_Id == 2
).ToList();
List <vwProjectionRelation> gridItems = settings.VwProjectionRelationList.Where(x =>
x.ProjectionRelation_ParentProjection_FK == vwProjection.Projection_Id &&
x.ProjectionRelation_Type_FK == 1 &&
x.ProjectionRelation_ChildProjectionName != "Participant" &&
x.ProjectionRelation_ChildProjectionProfile_Id == userInfo.ProfileId &&
((x.ProjectionRelation_ChildProjectionAccessRight & (long)6) == (long)6)
).ToList();
dynamic jsonSchema = new JObject();
#region make JSON Schema definition
jsonSchema.type = "object";
jsonSchema.properties = ToSchemaProperties(vwProjection, columnsAll, gridItems);
jsonSchema.required = ToSchemaRequired(columnsAll);
if (gridItems != null && gridItems.Count > 0)
{
JObject definitions = new JObject();
foreach (vwProjectionRelation relation in gridItems)
{
List <vwProjectionColumn> childColumns = settings.GetVwProjectionColumnList(userInfo.ProfileId, relation.ProjectionRelation_ChildProjectionName).Where(x =>
x.AppColumnType_Id != 1 &&
!x.Column_IsReadOnly &&
!x.Column_IsPrimaryKey &&
x.ProjectionColumn_Id != relation.ProjectionColumn1_Id &&
(x.Column_IsVisibleOnItemGrid || x.Column_HiddenData) &&
x.AccessRightsType_Id == 2
).ToList();
vwProjection childVwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Id == relation.ProjectionRelation_ChildProjection_FK);
dynamic jsonChild = new JObject();
jsonChild.type = "object";
jsonChild.properties = ToSchemaProperties(childVwProjection, childColumns, null);
jsonChild.required = ToSchemaRequired(childColumns);
definitions[relation.ProjectionRelation_ChildProjectionName] = jsonChild;
}
jsonSchema["definitions"] = definitions;
}
#endregion
string jsonSchemaString = jsonSchema.ToString();
JSchema schema = JSchema.Parse(jsonSchemaString);
#region setup JSchema
schema.Type = JSchemaType.Object;
//schema.AllowAdditionalProperties = false;
//schema.AllowAdditionalItems = false;
//if (gridItems != null && gridItems.Count > 0)
//{
// foreach (vwProjectionRelation relation in gridItems)
// {
// schema.Properties[relation.ProjectionRelation_ChildProjectionName].AllowAdditionalProperties = false;
// schema.Properties[relation.ProjectionRelation_ChildProjectionName].AllowAdditionalItems = false;
// if (schema.Properties[relation.ProjectionRelation_ChildProjectionName].Items != null && schema.Properties[relation.ProjectionRelation_ChildProjectionName].Items.Count > 0)
// {
// for (int i = 0; i < schema.Properties[relation.ProjectionRelation_ChildProjectionName].Items.Count; i++)
// {
// schema.Properties[relation.ProjectionRelation_ChildProjectionName].Items[i].AllowAdditionalProperties = false;
// schema.Properties[relation.ProjectionRelation_ChildProjectionName].Items[i].AllowAdditionalItems = false;
// }
// }
// }
//}
#endregion
bool isValid = jobject.IsValid(schema, out errList);
return(isValid);
}
catch (Exception ex)
{
Logger.SaveLogError(LogLevel.Error, "JObjectExtensions Validate", ex, null, userInfo);
errList = null;
return(true);
}
}
private static JObject ToSchemaProperties(vwProjection vwProjection, List <vwProjectionColumn> columns, List <vwProjectionRelation> gridItems)
{
JObject jsonObject = new JObject();
{
dynamic json1 = new JObject();
json1.Type = new JArray()
{
"integer"
};
jsonObject["ID"] = json1;
}
{
dynamic json1 = new JObject();
json1.Type = new JArray()
{
"boolean"
};
jsonObject["Deleted"] = json1;
}
if (!string.IsNullOrEmpty(vwProjection.DbPrimaryColumn_Name))
{
dynamic json1 = new JObject();
json1.Type = new JArray()
{
"integer", "null"
};
jsonObject[vwProjection.DbPrimaryColumn_Name] = json1;
}
if (vwProjection.Object_DocumentEnabled)
{
dynamic json2 = new JObject();
json2.Type = new JArray()
{
"array", "null"
};
jsonObject["Document"] = json2;
}
if (vwProjection.Object_ParticipantEnabled)
{
dynamic json2 = new JObject();
json2.Type = new JArray()
{
"array", "null"
};
jsonObject["Participant"] = json2;
}
//if (vwProjection.Object_CategoriesEnabled)
{
dynamic json2 = new JObject();
json2.Type = new JArray()
{
"array", "null"
};
jsonObject["EntityCategories"] = json2;
}
foreach (vwProjectionColumn column in columns)
{
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Geography:
JObject json2a = column.ToJsonObject();
if (json2a != null)
{
jsonObject[column.ProjectionColumn_Name + "_Lat"] = json2a;
}
JObject json2b = column.ToJsonObject();
if (json2b != null)
{
jsonObject[column.ProjectionColumn_Name + "_Long"] = json2b;
}
break;
default:
JObject json2 = column.ToJsonObject();
if (json2 != null)
{
jsonObject[column.ProjectionColumn_Name] = json2;
}
break;
}
}
if (gridItems != null && gridItems.Count > 0)
{
foreach (vwProjectionRelation gridItem in gridItems)
{
switch (gridItem.ProjectionRelation_Type_FK)
{
case (int)DbRelationTypeEnum.ItemGrid:
case (int)DbRelationTypeEnum.ItemMasterGrid:
case (int)DbRelationTypeEnum.Attachment:
{
dynamic json4 = new JObject();
json4["$ref"] = $"#/definitions/{gridItem.ProjectionRelation_ChildProjectionName}";
dynamic json3 = new JObject();
json3.type = new JArray()
{
"array", "null"
};
json3.items = json4;
jsonObject[gridItem.ProjectionRelation_ChildProjectionName] = json3;
}
break;
default:
break;
}
}
}
return(jsonObject);
}
private static void CleanProperties(this JObject jobject, List <vwProjectionColumn> columnsWithoutDbColumn, List <vwProjectionRelation> gridItems, UserInfo userInfo)
{
Settings settings = Settings.GetInstance;
List <string> propertiesCaptionList = jobject.Properties().Where(p => p.Name.EndsWith("_Caption")).Select(p => p.Name).ToList();
foreach (string propertyCaption in propertiesCaptionList)
{
jobject.Remove(propertyCaption);
}
foreach (string propertyCaption in new List <string>()
{
"Version", "Caption", "CreatedBy_FK", "CreatedBy_FK_Caption", "Created", "ModifiedBy_FK", "ModifiedBy_FK_Caption", "Modified"
})
{
jobject.Remove(propertyCaption);
}
foreach (string propertyCaption in columnsWithoutDbColumn.Select(c => c.ProjectionColumn_Name))
{
jobject.Remove(propertyCaption);
}
if (gridItems != null && gridItems.Count > 0)
{
JObject definitions = new JObject();
foreach (vwProjectionRelation relation in gridItems)
{
List <vwProjectionColumn> childColumns = settings.GetVwProjectionColumnList(userInfo.ProfileId, relation.ProjectionRelation_ChildProjectionName).Where(x =>
x.AppColumnType_Id != 1 &&
!x.Column_IsReadOnly &&
!x.Column_IsPrimaryKey &&
x.ProjectionColumn_Id != relation.ProjectionColumn1_Id &&
(x.Column_IsVisibleOnItemGrid || x.Column_HiddenData) &&
x.AccessRightsType_Id == 2
).ToList();
vwProjection chuildVwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Id == relation.ProjectionRelation_ChildProjection_FK);
if (jobject[relation.ProjectionRelation_ChildProjectionName] != null &&
jobject[relation.ProjectionRelation_ChildProjectionName].HasValues &&
jobject[relation.ProjectionRelation_ChildProjectionName].Type == JTokenType.Array &&
jobject[relation.ProjectionRelation_ChildProjectionName].Count() > 0)
{
for (int i = 0; i < jobject[relation.ProjectionRelation_ChildProjectionName].Count(); i++)
{
JObject row = ((JObject)jobject[relation.ProjectionRelation_ChildProjectionName][i]);
List <string> propertiesCaptionListChild = row.Properties().Where(p => p.Name.EndsWith("_Caption")).Select(p => p.Name).ToList();
foreach (string propertyCaption in propertiesCaptionListChild)
{
row.Remove(propertyCaption);
}
foreach (string propertySystem in new List <string>()
{
"Version", "Caption", "CreatedBy_FK", "CreatedBy_FK_Caption", "Created", "ModifiedBy_FK", "ModifiedBy_FK_Caption", "Modified", "Total_WithoutTax_WithoutDiscounts", "Total_Tax_WithoutDiscounts", "Total_WithoutDiscounts", "Total_WithoutTax_WithDiscounts", "Total_Tax_WithDiscounts", "Total_WithDiscounts"
})
{
row.Remove(propertySystem);
}
}
}
}
}
}
public new string TransformText()
{
StringBuilder sb = new StringBuilder();
if (projectionActions != null && projectionActions.Count > 0)
{
List <string> globalColumnNameList = new List <string>();
sb.AppendLine(", ( ");
sb.AppendLine("SELECT");
sb.AppendLine(" [RowSelect].[Version] as [Version]");
sb.AppendLine(" ,[RowSelect].[Deleted] as [Deleted]");
sb.AppendLine("###GlobalColumns###");
sb.AppendLine("FROM (");
bool first = true;
foreach (vwProjectionAction projectionAction in projectionActions)
{
vwProjection projectionFrom = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionAction.ProjectionFrom_Name);
vwProjectionRelation relation = settings.VwProjectionRelationList.Where(x =>
x.ProjectionRelation_ParentProjection_FK == projectionAction.ParentProjectionFrom_ID &&
x.ProjectionRelation_ChildProjection_FK == projectionAction.ProjectionFrom_ID &&
x.ProjectionRelation_ChildProjectionProfile_Id == userInfo.ProfileId &&
((x.ProjectionRelation_ChildProjectionAccessRight & (long)1) == (long)1)
).FirstOrDefault();
if (!first && projectionActions.Count > 1)
{
sb.AppendLine("");
sb.AppendLine("UNION ALL");
sb.AppendLine("");
}
sb.AppendLine("SELECT");
sb.AppendLine(" 0 as [Version]");
sb.AppendLine(" ,0 as [Deleted]");
List <string> renderCaptionColumnNameList = new List <string>();
sb.Append(WriteSelectColumn(projectionAction, out renderCaptionColumnNameList));
if (first)
{
globalColumnNameList = renderCaptionColumnNameList;
}
else
{
globalColumnNameList = globalColumnNameList.Intersect(renderCaptionColumnNameList).ToList();
}
sb.AppendLine($"FROM [{projectionFrom.DbObject_Schema}].[{projectionFrom.DbObject_Name}] as [{projectionAction.ProjectionFrom_Name}]");
sb.AppendLine($"where [{projectionAction.ProjectionFrom_Name}].[Deleted] = 0");
sb.AppendLine($" and [{relation.ProjectionRelation_ChildProjectionName}].[{relation.DbColumn1_Name}] = [{parentEntity.Projection_Name}].[{parentEntity.DbPrimaryColumn_Name}]");
//sb.AppendLine($"ORDER BY [{projectionAction.ProjectionFrom_Name}].[{relation.DbColumn_NameSortBy}]");
first = false;
}
sb.AppendLine(") [RowSelect]");
sb.AppendLine("FOR JSON PATH, INCLUDE_NULL_VALUES)");
sb.AppendLine($"as [{projectionNameTarget}]");
StringBuilder sbGlobalColumns = new StringBuilder();
if (globalColumnNameList != null && globalColumnNameList.Count > 0)
{
foreach (string name in globalColumnNameList)
{
sbGlobalColumns.AppendLine($" ,[RowSelect].[{name}] as [{name}]");
}
}
sb.Replace("###GlobalColumns###", sbGlobalColumns.ToString());
}
return(sb.ToString());
}
public CreateFrom_ItemGrid(UserInfo userInfo, string projectionNameTarget, List <vwProjectionAction> projectionActions, vwProjection parentEntity)
{
this.userInfo = userInfo;
this.projectionNameTarget = projectionNameTarget;
this.parentEntity = parentEntity;
this.projectionActions = projectionActions;
}
protected void WriteLeftOuterJoin(WriteLeftOuterJoinType type)
{
foreach (KeyValuePair <string, string> pair in this.foreignTables)
{
string val = string.Empty;
vwProjectionColumn column = null;
if (type == WriteLeftOuterJoinType.ForData)
{
column = columns.Where(c => c.ProjectionColumn_Name == pair.Key &&
(
columnsVisible != null && columnsVisible.Contains(c.ProjectionColumn_Name)
||
!string.IsNullOrEmpty(sortOrderColumnName) && c.ProjectionColumn_Name == sortOrderColumnName
//||
//columnsInFilter.Contains(c.ProjectionColumn_Name)
)
).FirstOrDefault();
}
if (column != null)
{
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Enumeration:
if (this.foreignTables.ContainsKey(column.ProjectionColumn_Name))
{
val = $"LEFT OUTER JOIN [crm].[Enumeration] [{pair.Value}] on [{pair.Value}].[Id] = [Project1].[{column.ProjectionColumn_Name}]";
}
break;
case (int)DbColumnTypeEnum.Entity:
{
if (column.ProjectionReference_Id.HasValue)
{
vwProjection fk = settings.GetVwProjectionList(userInfo.ProfileId).FirstOrDefault(x => x.Projection_Id == column.ProjectionReference_Id.Value);
if (fk != null)
{
val = $"LEFT OUTER JOIN [{fk.DbObject_Schema}].[{fk.DbObject_Name}] [{pair.Value}] on [{pair.Value}].[{fk.DbPrimaryColumn_Name}] = [Project1].[{column.ProjectionColumn_Name}]";
}
}
}
break;
case (int)DbColumnTypeEnum.EntityEnumeration:
{
if (column.ProjectionReference_Id.HasValue)
{
vwProjection fk = settings.GetVwProjectionList(userInfo.ProfileId).FirstOrDefault(x => x.Projection_Id == column.ProjectionReference_Id.Value);
if (fk != null)
{
val = $"LEFT OUTER JOIN [{fk.DbObject_Schema}].[{fk.DbObject_Name}] [{pair.Value}] on [{pair.Value}].[{fk.DbPrimaryColumn_Name}] = [Project1].[{column.ProjectionColumn_Name}]";
}
}
}
break;
default:
val = $"[Project1].[{column.DbColumn_Name}] as [{column.DbColumn_Name}]";
break;
}
}
if (!string.IsNullOrEmpty(val))
{
WriteLine(val);
}
}
}
protected void WriteFilter()
{
string val = string.Empty;
JToken objects = JToken.Parse(this.filterString);
foreach (JToken elem in objects)
{
val = string.Empty;
if (elem.Type == JTokenType.Property)
{
JProperty prop = ((Newtonsoft.Json.Linq.JProperty)elem);
if (prop != null && !string.IsNullOrEmpty(prop.Name) && prop.Value != null && !string.IsNullOrEmpty(prop.Value.ToString()))
{
if (prop.Name == "EmailParentProjectionName" &&
objects["EmailParentRecordId"] != null && int.Parse(objects["EmailParentRecordId"].ToString()) > 0)
{
string parentProjectionName = prop.Value.ToString();
int parentRecordId = int.Parse(objects["EmailParentRecordId"].ToString());
vwProjection vwProjectionParent = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == parentProjectionName);
string directoryEmailFilter = string.Empty;
if (vwProjectionParent.DbObject_Name == "Directory")
{
directoryEmailFilter = $" or ([Project1].[{this.vwProjection.DbPrimaryColumn_Name}] in (SELECT ed1.[Email_FK] FROM [crm].[Email_Directory] ed1 WHERE ed1.[Deleted] = 0 and ed1.[Directory_FK] = {parentRecordId}))";
}
val = $"and (([Project1].[{this.vwProjection.DbPrimaryColumn_Name}] in (SELECT doe1.[Email_FK] FROM [crm].[DbObjectEmail] doe1 WHERE doe1.[Deleted] = 0 and doe1.[DbObject_FK] = {vwProjectionParent.DbObject_Id} and doe1.[RecordId] = {parentRecordId})){directoryEmailFilter})";
}
else if (prop.Name == "EntityCategories" || prop.Name == "EntityCategories_FK")
{
string categoriesVals = string.Join(", ", prop.Value.ToArray().Select(t => t.ToString()));
if (!string.IsNullOrEmpty(categoriesVals))
{
val = $"and [Project1].[{this.vwProjection.DbPrimaryColumn_Name}] in (SELECT ec.[Entity_FK] FROM[crm].[Entity_Category] ec WHERE ec.[Deleted] = 0 and ec.[Type] = {this.entityType} and ec.[Category_FK] in ({categoriesVals}))";
}
}
else if (prop.Name.StartsWith("Role__"))
{
int? roleId = null;
JToken[] parts = prop.Value.ToArray();
if (parts != null && parts.Length > 0)
{
roleId = parts.First().Value <int?>("RoleId");
if (roleId.HasValue && roleId.Value > 0)
{
string userIds = string.Join(", ", parts.Select(t => t.Value <string>("UserId")));
val = $@"and [Project1].[{this.vwProjection.DbPrimaryColumn_Name}] in (
SELECT part1.[RecordId]
FROM [crm].[Role] role1
INNER JOIN [crm].[DbObjectRole] orole1 on orole1.[Deleted] = 0 and orole1.[DbObject_FK] = {this.vwProjection.DbObject_Id} and orole1.[Role_FK] = role1.[Id]
INNER JOIN [crm].[Participant] part1 on part1.[Deleted] = 0 and part1.[DbObjectRole_FK] = orole1.[Id] and part1.[User_FK] in ({userIds})
WHERE role1.[Deleted] = 0 and role1.[Id] = {roleId.Value}
)" ;
}
}
}
else
{
string propName = prop.Name.Replace("_From", "").Replace("_To", "");
vwProjectionColumn column = columnsForFilter.Where(c => c.ProjectionColumn_Name == propName).FirstOrDefault();
if (column != null)
{
if (string.IsNullOrEmpty(column.Column_Filter))
{
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Bit:
case (int)DbColumnTypeEnum.BitIcon:
{
val = $"and [Project1].[{column.DbColumn_Name}] = {((new string[] { "1", "true", "yes", "ok" }).Contains(prop.Value.ToString().ToLower()) ? " 1 " : " 0 ")}";
}
break;
case (int)DbColumnTypeEnum.Int:
case (int)DbColumnTypeEnum.BigInt:
case (int)DbColumnTypeEnum.Float:
case (int)DbColumnTypeEnum.Money:
{
if (prop.Name.EndsWith("_From"))
{
val = $"and [Project1].[{column.DbColumn_Name}] >= {prop.Value.ToString()}";
}
if (prop.Name.EndsWith("_To"))
{
val = $"and [Project1].[{column.DbColumn_Name}] < {prop.Value.ToString()}";
}
if (!prop.Name.EndsWith("_From") && !prop.Name.EndsWith("_To"))
{
val = $"and [Project1].[{column.DbColumn_Name}] = {prop.Value.ToString()}";
}
}
break;
case (int)DbColumnTypeEnum.String:
case (int)DbColumnTypeEnum.Text:
case (int)DbColumnTypeEnum.EmailAddress:
case (int)DbColumnTypeEnum.WebUrl:
case (int)DbColumnTypeEnum.PhoneNumber:
{
if (this.filterString.Contains(prop.Name + "_StringEquals"))
{
val = $"and [Project1].[{column.DbColumn_Name}] = N'{prop.Value.ToString().Replace("'", "''")}'";
}
else
{
val = $"and [Project1].[{column.DbColumn_Name}] like N'%{prop.Value.ToString().Replace("'", "''")}%'";
}
if (column.DbColumn_Name == "Pohoda_ID")
{
val = $"and [Project1].[{column.DbColumn_Name}] = N'{prop.Value.ToString().Replace("'", "''")}'";
}
}
break;
case (int)DbColumnTypeEnum.Entity:
{
val = $"and [Project1].[{column.DbColumn_Name}] = {prop.Value.ToString()}";
}
break;
case (int)DbColumnTypeEnum.Enumeration:
{
string inValue = prop.Value.ToString().Replace("[", "").Replace("]", "");
if (!string.IsNullOrEmpty(inValue) && inValue.Length > 0)
{
val = $"and [Project1].[{column.DbColumn_Name}] in ({inValue})";
}
}
break;
case (int)DbColumnTypeEnum.MultiEnumeration:
{
string inValue = prop.Value.ToString();
if (!string.IsNullOrEmpty(inValue) && inValue.Length > 2) // []
{
//val = $"and [Project1].[{vwProjection.DbPrimaryColumn_Name}] in (SELECT doe.[RecordId] FROM [crm].[DbColumnEnumeration] doe WHERE doe.[Deleted] = 0 AND doe.[DbColumn_FK] = {column.DbColumn_Id} AND doe.[Enumeration_FK] in ({inValue}))";
val = $"and [Project1].[{column.DbColumn_Name}] IS NOT NULL and ((SELECT count(*) FROM OPENJSON(N'{inValue}') json2 INNER JOIN OPENJSON([Project1].[{column.DbColumn_Name}]) json1 ON json1.value = json2.value) > 0)";
}
}
break;
case (int)DbColumnTypeEnum.Date:
case (int)DbColumnTypeEnum.DateTime:
if (prop.Value != null)
{
DateTime date1 = (DateTime)prop;
if (prop.Name.EndsWith("_From"))
{
val = $"and [Project1].[{column.DbColumn_Name}] >= convert(datetime, '{date1.ToString("s")}', 126)";
}
if (prop.Name.EndsWith("_To"))
{
val = $"and [Project1].[{column.DbColumn_Name}] < convert(datetime, '{date1.ToString("s")}', 126)";
}
}
break;
default:
break;
}
}
else
{
val = column.Column_Filter.Replace("##Value##", prop.Value.ToString());
}
}
}
}
}
if (!string.IsNullOrEmpty(val))
{
Write(val);
Write(Environment.NewLine);
}
}
}
