private void WriteUpdateColumn(vwProjectionColumn column)
{
if (column.ProjectionColumn_Name != pairingColumn)
{
string val = $",[{column.DbColumn_Name}] = json.[{column.ProjectionColumn_Name}]";
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Bit:
case (int)DbColumnTypeEnum.BitIcon:
{
if (!column.Column_IsNullable)
{
val = $",[{column.DbColumn_Name}] = isnull(json.[{column.ProjectionColumn_Name}], 0)";
}
}
break;
default:
break;
}
if (!string.IsNullOrEmpty(column.Column_UpdateDefaultValue))
{
val = $",[{column.DbColumn_Name}] = {column.Column_UpdateDefaultValue}";
}
Write(val);
WriteLine("");
}
}
private void WriteColumnBracketedString(vwProjectionColumn column)
{
string val = $",[{vwProjection.Projection_Name}].[{column.DbColumn_Name}] as [{column.ProjectionColumn_Name}]";
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.SystemChart || column.DbColumnType_Id == (int)DbColumnTypeEnum.UserChart)
{
val = $",null as [{column.ProjectionColumn_Name}]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Signature)
{
val = $",(SELECT TOP 1 [UniqueGuid] FROM [crm].[Signature] sig WHERE sig.[Deleted] = 0 AND sig.Id = [{vwProjection.Projection_Name}].[{column.DbColumn_Name}]) as [{column.ProjectionColumn_Name}]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Geography)
{
val = $",case when [{vwProjection.Projection_Name}].[{column.DbColumn_Name}] is not null then [{vwProjection.Projection_Name}].[{column.DbColumn_Name}].Lat else null end as [{column.ProjectionColumn_Name}_Lat]";
val = val + Environment.NewLine + $",case when [{vwProjection.Projection_Name}].[{column.DbColumn_Name}] is not null then [{vwProjection.Projection_Name}].[{column.DbColumn_Name}].Long else null end as [{column.ProjectionColumn_Name}_Long]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.MultiEnumeration)
{
val = $",JSON_QUERY([{vwProjection.Projection_Name}].[{column.DbColumn_Name}]) as [{column.ProjectionColumn_Name}]";
}
if (!string.IsNullOrEmpty(column.Column_ComputedExpression))
{
val = $",{column.Column_ComputedExpression.Replace("##EntityName##", vwProjection.Projection_Name)} as [{column.ProjectionColumn_Name}]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Password)
{
val = $",null as [{column.ProjectionColumn_Name}]";
}
Write(val);
}
private void WriteCaptionStringForColumn(vwProjectionColumn column)
{
if (column != null)
{
List <string> renderCaptionColumnNameList = new List <string>();
Write(GetReferenceCaption(column.DbColumnType_Id, column.ProjectionReference_Id, column.ProjectionColumn_Name, null, vwProjection.Projection_Name, column.DbColumn_Name, column.Column_ComputedExpression, out renderCaptionColumnNameList));
}
}
private void WriteInsertColumn(vwProjectionColumn column)
{
string val = $",[{column.DbColumn_Name}]";
if (!string.IsNullOrEmpty(val))
{
WriteLine(val);
}
}
private void WriteUpdateColumn(vwProjectionColumn column)
{
string val = $",[{column.DbColumn_Name}] = json.[{column.ProjectionColumn_Name}]";
if (!string.IsNullOrEmpty(column.Column_UpdateDefaultValue))
{
val = $",[{column.DbColumn_Name}] = {column.Column_UpdateDefaultValue}";
}
Write(val);
WriteLine("");
}
protected void WriteColumnForProject1Select(vwProjectionColumn column)
{
string val = string.Empty;
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Enumeration:
if (this.foreignTables.ContainsKey(column.ProjectionColumn_Name))
{
val = $",[{this.foreignTables[column.ProjectionColumn_Name]}].[Description_{userInfo.UserLanguage}] as [{column.ProjectionColumn_Name}]";
val = val + Environment.NewLine + $",[{this.foreignTables[column.ProjectionColumn_Name]}].[Description] as [{column.ProjectionColumn_Name}_Description]";
}
break;
case (int)DbColumnTypeEnum.Entity:
{
val = $",[{this.foreignTables[column.ProjectionColumn_Name]}].[CaptionDisplay] as [{column.ProjectionColumn_Name}]";
}
break;
case (int)DbColumnTypeEnum.EntityEnumeration:
{
val = $",[{this.foreignTables[column.ProjectionColumn_Name]}].[CaptionDisplay] as [{column.ProjectionColumn_Name}]";
}
break;
case (int)DbColumnTypeEnum.MultiEnumeration:
{
val = $",IIF([Project1].[{column.DbColumn_Name}] IS NOT NULL, STUFF((SELECT ',' + en1.[Description_{userInfo.UserLanguage}] FROM (SELECT value as [Value] FROM OPENJSON([Project1].[{column.DbColumn_Name}])) doe INNER JOIN[crm].[Enumeration] en1 ON en1.[Deleted] = 0 AND en1.[Id] = doe.[Value] FOR XML PATH('')),1,1,''), '') as [{column.ProjectionColumn_Name}]";
}
break;
case (int)DbColumnTypeEnum.Password:
{
val = $",null as [{column.ProjectionColumn_Name}]";
}
break;
default:
val = $",[Project1].[{column.DbColumn_Name}] as [{column.ProjectionColumn_Name}]";
if (!string.IsNullOrEmpty(column.Column_ComputedExpression))
{
val = $",{column.Column_ComputedExpression.Replace("##EntityName##", "Project1").Replace("##ProjectionPrimaryColumnName##", vwProjection.DbPrimaryColumn_Name).Replace("##EntityDbObjectName##", vwProjection.DbObject_Name)} as [{column.ProjectionColumn_Name}]";
}
break;
}
//if (column.Column_IsPrimaryKey && column.DbColumn_IsPrimaryKey.Value && column.DbColumn_Name.ToUpper() != "ID")
//{
// val = val + Environment.NewLine + $",[Project1].[{column.DbColumn_Name}] as [Id]{Environment.NewLine},[Project1].[CaptionSort] as [CaptionSort]";
//}
Write(val);
}
private void WriteSelectColumn(vwProjectionColumn column)
{
string val = $",json{childProjectionName}.[" + column.ProjectionColumn_Name + "]";
if (!string.IsNullOrEmpty(column.Column_InsertDefaultValue))
{
val = $",{column.Column_InsertDefaultValue}";
}
Write(val);
WriteLine("");
}
private void WriteJsonColumn(vwProjectionColumn column)
{
string val = $",[{column.ProjectionColumn_Name}] {column.Column_DbColumnTypeNative}";
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.MultiEnumeration)
{
val = string.Empty;
}
if (!string.IsNullOrEmpty(val))
{
WriteLine(val);
}
}
private void WriteNewSequenceNumber()
{
if (vwProjection.NumberingSequence_NumericDbColumnId.HasValue)
{
vwProjectionColumn dateProjectionColumn = this.columns.Where(c => vwProjection.NumberingSequence_DateDbColumnId.HasValue && c.DbColumn_Id == vwProjection.NumberingSequence_DateDbColumnId.Value).FirstOrDefault();
vwProjectionColumn issuerProjectionColumn = this.columns.Where(c => vwProjection.NumberingSequence_IssuerDbColumnId.HasValue && c.DbColumn_Id == vwProjection.NumberingSequence_IssuerDbColumnId.Value).FirstOrDefault();
vwProjectionColumn typeProjectionColumn = this.columns.Where(c => vwProjection.NumberingSequence_TypeDbColumnId.HasValue && c.DbColumn_Id == vwProjection.NumberingSequence_TypeDbColumnId.Value).FirstOrDefault();
WriteLine("DECLARE @newSequenceNumber varchar(100)");
WriteLine("DECLARE @recordIssuerAccountId int");
WriteLine("DECLARE @recordDate datetime");
WriteLine("DECLARE @recordTypeId int");
WriteLine("SELECT");
if (dateProjectionColumn != null && !string.IsNullOrEmpty(dateProjectionColumn.ProjectionColumn_Name))
{
WriteLine($" @recordDate = isnull(json.[{dateProjectionColumn.ProjectionColumn_Name}], getdate())");
}
else
{
WriteLine($" @recordDate = getdate()");
}
if (issuerProjectionColumn != null && !string.IsNullOrEmpty(issuerProjectionColumn.ProjectionColumn_Name))
{
WriteLine($" ,@recordIssuerAccountId = json.[{issuerProjectionColumn.ProjectionColumn_Name}]");
}
if (typeProjectionColumn != null && !string.IsNullOrEmpty(typeProjectionColumn.ProjectionColumn_Name))
{
WriteLine($" ,@recordTypeId = json.[{typeProjectionColumn.ProjectionColumn_Name}]");
}
WriteLine("FROM OPENJSON(@json) WITH ( [Deleted] [int]");
if (dateProjectionColumn != null && !string.IsNullOrEmpty(dateProjectionColumn.ProjectionColumn_Name))
{
WriteJsonColumn(dateProjectionColumn);
}
if (issuerProjectionColumn != null && !string.IsNullOrEmpty(issuerProjectionColumn.ProjectionColumn_Name))
{
WriteJsonColumn(issuerProjectionColumn);
}
if (typeProjectionColumn != null && !string.IsNullOrEmpty(typeProjectionColumn.ProjectionColumn_Name))
{
WriteJsonColumn(typeProjectionColumn);
}
WriteLine(") AS json");
WriteLine($"EXEC [dbo].[sp_api3_GetNewProjectionNumber] {userInfo.UserId}, @recordIssuerAccountId, @recordDate, {vwProjection.DbObject_Id}, @recordTypeId, @newSequenceNumber OUTPUT");
}
}
private void WriteUpdateColumn(vwProjectionColumn column)
{
string val = $",[{column.DbColumn_Name}] = json.[{column.ProjectionColumn_Name}]";
if (!string.IsNullOrEmpty(column.Column_UpdateDefaultValue))
{
val = $",[{column.DbColumn_Name}] = {column.Column_UpdateDefaultValue}";
}
else
{
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Geography)
{
val = $",[{column.DbColumn_Name}] = case when json.{column.ProjectionColumn_Name}_Lat <> 0 and json.{column.ProjectionColumn_Name}_Long <> 0 then geography::Point(json.{column.ProjectionColumn_Name}_Lat, json.{column.ProjectionColumn_Name}_Long, 4326) else null end";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Signature)
{
val = $",[{column.DbColumn_Name}] = (select top 1 si.[Id] from [crm].[Signature] si where si.deleted = 0 and si.UniqueGuid = json.[{column.ProjectionColumn_Name}])";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.MultiEnumeration)
{
val = $",[{column.DbColumn_Name}] = JSON_QUERY(@json, '$.{column.ProjectionColumn_Name}')";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.String ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.Text ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.WidgetHtml ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.TextSimple ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.IconSelect ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.JSON ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.Color ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.EmailAddress ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.WebUrl ||
column.DbColumnType_Id == (int)DbColumnTypeEnum.PhoneNumber
)
{
val = $",[{column.DbColumn_Name}] = iif(len(isnull(json.[{column.ProjectionColumn_Name}],'')) > 0, json.[{column.ProjectionColumn_Name}], null)";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Password)
{
val = $",[{column.DbColumn_Name}] = iif(len(isnull(json.[{column.ProjectionColumn_Name}],'')) > 0, json.[{column.ProjectionColumn_Name}], [{vwProjection.DbObject_Schema}].[{vwProjection.DbObject_Name}].[{column.DbColumn_Name}])";
}
if (!string.IsNullOrEmpty(val))
{
WriteLine(val);
}
}
}
protected void WriteColumnForEntity1Select(vwProjectionColumn column)
{
string val = $",[Entity1].[{column.ProjectionColumn_Name}] as [{column.ProjectionColumn_Name}]";
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Enumeration:
val = val + Environment.NewLine + $",[Entity1].[{column.ProjectionColumn_Name}_Description] as [{column.ProjectionColumn_Name}_Description]";
break;
}
//if (column.Column_IsPrimaryKey && column.DbColumn_IsPrimaryKey.Value && column.DbColumn_Name.ToUpper() != "ID")
//{
// val = val + Environment.NewLine + $",[Entity1].[{column.DbColumn_Name}] as [Id]";
//}
Write(val);
}
private void WriteSelectColumn(vwProjectionColumn column)
{
if (vwProjection.NumberingSequence_NumericDbColumnId.HasValue && column.DbColumn_Id == vwProjection.NumberingSequence_NumericDbColumnId.Value)
{
WriteLine(",@newSequenceNumber");
}
else
{
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Bit:
case (int)DbColumnTypeEnum.BitIcon:
{
if (!column.Column_IsNullable)
{
WriteLine($",isnull(json.[{column.ProjectionColumn_Name}], 0)");
}
else
{
WriteLine($",json.[{column.ProjectionColumn_Name}]");
}
}
break;
case (int)DbColumnTypeEnum.MultiEnumeration:
{
WriteLine($",JSON_QUERY(@json, '$.{column.ProjectionColumn_Name}')");
}
break;
default:
if (!string.IsNullOrEmpty(column.Column_InsertDefaultValue))
{
WriteLine($",{column.Column_InsertDefaultValue}");
}
else
{
WriteLine($",json.[{column.ProjectionColumn_Name}]");
}
break;
}
}
}
private void WriteJsonColumn(vwProjectionColumn column)
{
string val = $",[{column.ProjectionColumn_Name}] {column.Column_DbColumnTypeNative}";
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Geography)
{
val = $",[{column.ProjectionColumn_Name}_Lat] float, [{column.ProjectionColumn_Name}_Long] float";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Signature)
{
val = $",[{column.ProjectionColumn_Name}] [varchar](50)";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.MultiEnumeration)
{
val = string.Empty;
}
if (!string.IsNullOrEmpty(val))
{
WriteLine(val);
}
}
private void WriteColumnBracketedString(vwProjectionColumn column)
{
string val = $",[{this.relation.ProjectionRelation_ChildProjectionName}].[{column.DbColumn_Name}] as [{column.ProjectionColumn_Name}]";
if (column.Column_IsPrimaryKey && column.DbColumn_Name.ToUpper() != "ID")
{
val = val + Environment.NewLine + $",[{column.DbColumn_Name}] as [Id]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.MultiEnumeration)
{
val = $",JSON_QUERY([{this.relation.ProjectionRelation_ChildProjectionName}].[{column.DbColumn_Name}]) as [{column.ProjectionColumn_Name}]";
}
if (!string.IsNullOrEmpty(column.Column_ComputedExpression))
{
val = $",{column.Column_ComputedExpression.Replace("##EntityName##", vwProjection.Projection_Name)} as [{column.ProjectionColumn_Name}]";
}
if (column.DbColumnType_Id == (int)DbColumnTypeEnum.Password)
{
val = $",null as [{column.ProjectionColumn_Name}]";
}
Write(val);
}
private void WriteInsertColumn(vwProjectionColumn column)
{
string val = $",[{column.DbColumn_Name}]";
WriteLine(val);
}
private void WriteJsonColumn(vwProjectionColumn column)
{
Write($",[{column.ProjectionColumn_Name}] {column.Column_DbColumnTypeNative}");
WriteLine("");
}
public static JObject ToJsonObject(this vwProjectionColumn column)
{
dynamic json = new JObject();
string type = "string";
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Bit:
case (int)DbColumnTypeEnum.BitIcon:
type = "boolean";
break;
case (int)DbColumnTypeEnum.Int:
case (int)DbColumnTypeEnum.Entity:
case (int)DbColumnTypeEnum.EntityEnumeration:
case (int)DbColumnTypeEnum.Enumeration:
case (int)DbColumnTypeEnum.Signature:
case (int)DbColumnTypeEnum.BigInt:
type = "integer";
break;
case (int)DbColumnTypeEnum.Float:
case (int)DbColumnTypeEnum.Money:
type = "number";
break;
case (int)DbColumnTypeEnum.String:
case (int)DbColumnTypeEnum.Text:
case (int)DbColumnTypeEnum.TextSimple:
case (int)DbColumnTypeEnum.WidgetHtml:
case (int)DbColumnTypeEnum.IconSelect:
case (int)DbColumnTypeEnum.Guid:
case (int)DbColumnTypeEnum.XML:
case (int)DbColumnTypeEnum.EmailAddress:
case (int)DbColumnTypeEnum.WebUrl:
case (int)DbColumnTypeEnum.PhoneNumber:
type = "string";
break;
case (int)DbColumnTypeEnum.Date:
type = "string";
json.format = "date";
break;
case (int)DbColumnTypeEnum.DateTime:
case (int)DbColumnTypeEnum.DateCheckBox:
type = "string";
json.format = "date-time";
break;
case (int)DbColumnTypeEnum.Time:
type = "string";
json.format = "time";
break;
case (int)DbColumnTypeEnum.MultiEnumeration:
type = "array";
break;
case (int)DbColumnTypeEnum.Geography:
type = "number";
break;
case (int)DbColumnTypeEnum.MultiStateBox:
case (int)DbColumnTypeEnum.JSON:
break;
case (int)DbColumnTypeEnum.SystemChart:
case (int)DbColumnTypeEnum.UserChart:
case (int)DbColumnTypeEnum.Varbinary:
case (int)DbColumnTypeEnum.Image:
return(null);
default:
type = "string";
break;
}
JArray types = new JArray()
{
type
};
if (column.Column_IsNullable)
{
types.Add("null");
}
json.type = types;
if (column.Column_StringMaxLength.HasValue && column.Column_StringMaxLength.Value > 0)
{
json.maxLength = column.Column_StringMaxLength.Value;
}
if (!string.IsNullOrEmpty(column.Column_LocalizationKey))
{
json.title = column.Column_LocalizationKey;
}
return(json);
}
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);
}
}
}
private void WriteSelectColumn(vwProjectionColumn column)
{
if (vwProjection.NumberingSequence_NumericDbColumnId.HasValue && column.DbColumn_Id == vwProjection.NumberingSequence_NumericDbColumnId.Value)
{
WriteLine(",@newSequenceNumber");
}
else if (!string.IsNullOrEmpty(column.Column_InsertDefaultValue))
{
WriteLine($",{column.Column_InsertDefaultValue}");
}
else
{
switch (column.DbColumnType_Id)
{
case (int)DbColumnTypeEnum.Bit:
case (int)DbColumnTypeEnum.BitIcon:
{
if (!column.Column_IsNullable)
{
WriteLine($",isnull(json.[{column.ProjectionColumn_Name}], 0)");
}
else
{
WriteLine($",json.[{column.ProjectionColumn_Name}]");
}
}
break;
case (int)DbColumnTypeEnum.Signature:
WriteLine($",(select top 1 si.[Id] from [crm].[Signature] si where si.deleted = 0 and si.UniqueGuid = json.[{column.ProjectionColumn_Name}])");
break;
case (int)DbColumnTypeEnum.Geography:
WriteLine($",case when json.{column.ProjectionColumn_Name}_Lat <> 0 and json.{column.ProjectionColumn_Name}_Long <> 0 then geography::Point(json.{column.ProjectionColumn_Name}_Lat, json.{column.ProjectionColumn_Name}_Long, 4326) else null end");
break;
case (int)DbColumnTypeEnum.Password:
WriteLine($",iif(len(isnull(json.[{column.ProjectionColumn_Name}],'')) > 0, json.[{column.ProjectionColumn_Name}], convert(varchar(40),newid()))");
break;
case (int)DbColumnTypeEnum.MultiEnumeration:
{
WriteLine($",JSON_QUERY(@json, '$.{column.ProjectionColumn_Name}')");
}
break;
case (int)DbColumnTypeEnum.String:
case (int)DbColumnTypeEnum.Text:
case (int)DbColumnTypeEnum.WidgetHtml:
case (int)DbColumnTypeEnum.TextSimple:
case (int)DbColumnTypeEnum.IconSelect:
case (int)DbColumnTypeEnum.JSON:
case (int)DbColumnTypeEnum.Color:
case (int)DbColumnTypeEnum.EmailAddress:
case (int)DbColumnTypeEnum.WebUrl:
case (int)DbColumnTypeEnum.PhoneNumber:
WriteLine($",iif(len(isnull(json.[{column.ProjectionColumn_Name}],'')) > 0, json.[{column.ProjectionColumn_Name}], null)");
break;
default:
WriteLine($",json.[{column.ProjectionColumn_Name}]");
break;
}
}
}
private void WriteUpdateColumn(vwProjectionColumn column)
{
Write($",[{column.DbColumn_Name}] = json.[{column.DbColumn_Name}]");
WriteLine("");
}
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);
}
}
}
