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();
}
private void WriteSubItem(vwProjectionRelation gridItem)
{
Insert2_ItemGridInsert itemGridInsertTemplate = new Insert2_ItemGridInsert(userInfo, gridItem, this.entity, 0, $"newId{childProjectionName}");
string childInsertVal = itemGridInsertTemplate.TransformText();
Write(childInsertVal);
}
public GetGridCount(
UserInfo userInfo,
string projectionName,
string parentProjectionName,
int parentEntityId,
string projectionRelationName
)
{
this.userInfo = userInfo;
this.vwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionName);
this.entityType = settings.GetCrmEntityType(this.vwProjection.DbObject_Name);
this.columnsForFilter = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(x.Column_IsVisibleOnFilter) &&
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2)
).ToList();
this.FilterString = "{}";
this.parentProjectionName = parentProjectionName;
this.parentDbObject_Id = settings.GetVwProjectionList(userInfo.ProfileId).Where(x => x.Projection_Name == parentProjectionName).Select(x => x.DbObject_Id).FirstOrDefault();
this.parentEntityId = parentEntityId;
this.gridMode = "Subgrid";
if (!string.IsNullOrEmpty(projectionRelationName))
{
vwProjectionRelation vwProjectionRelation = settings.VwProjectionRelationList.Where(r => r.ProjectionRelation_Name == projectionRelationName).FirstOrDefault();
if (vwProjectionRelation != null && vwProjectionRelation.ProjectionRelation_Id > 0)
{
this.vwProjectionRelation = vwProjectionRelation;
}
}
}
private void WriteItemGrid(vwProjectionRelation gridItem)
{
GetByIdItemGrid GetByIdItemGridTemplate = new Templates.GetByIdItemGrid(userInfo, gridItem, vwProjection);
string itemSql = GetByIdItemGridTemplate.TransformText();
Write(itemSql);
}
public GetPage(
UserInfo userInfo,
string projectionName,
int draw,
int start,
int length,
string sortOrderColumnName,
string sortOrderDirection,
string filter,
string parentProjectionName,
int?parentEntityId,
string[] columnsVisible,
string projectionRelationName,
string gridMode
)
{
this.userInfo = userInfo;
this.vwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionName);
this.entityType = settings.GetCrmEntityType(this.vwProjection.DbObject_Name);
this.columns = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(x.Column_VisibleOnGrid == 1 || x.Column_VisibleOnGrid == 2 || x.ProjectionColumn_Name == sortOrderColumnName) &&
!x.Column_IsPrimaryKey &&
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2)
).ToList();
this.columnsForFilter = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(
((x.Column_IsVisibleOnFilter) && (x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2))
||
(projectionName == "Email" && x.DbColumn_Name == "EmailAccount_Folder_FK")
)
).Distinct().ToList();
this.draw = draw;
this.start = start;
this.length = length;
this.sortOrderColumnName = sortOrderColumnName;
this.sortOrderDirection = sortOrderDirection;
this.FilterString = filter;
this.parentProjectionName = parentProjectionName;
this.parentDbObject_Id = settings.GetVwProjectionList(userInfo.ProfileId).Where(x => x.Projection_Name == parentProjectionName).Select(x => x.DbObject_Id).FirstOrDefault();
this.parentEntityId = parentEntityId;
this.columnsVisible = columnsVisible;
if (!string.IsNullOrEmpty(projectionRelationName))
{
vwProjectionRelation vwProjectionRelation = settings.VwProjectionRelationList.Where(r => r.ProjectionRelation_Name == projectionRelationName).FirstOrDefault();
if (vwProjectionRelation != null && vwProjectionRelation.ProjectionRelation_Id > 0)
{
this.vwProjectionRelation = vwProjectionRelation;
}
}
this.gridMode = gridMode;
FillForeignTablesAndSortOrderString();
}
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 GetGridIds(
UserInfo userInfo,
string projectionName,
string filter,
string parentProjectionName,
int?parentEntityId,
string projectionRelationName,
bool ignoreSwitchIsVisibleOnFilter
)
{
this.userInfo = userInfo;
this.vwProjection = settings.GetVwProjectionList(userInfo.ProfileId).First(x => x.Projection_Name == projectionName);
this.entityType = settings.GetCrmEntityType(this.vwProjection.DbObject_Name);
this.FilterString = filter;
this.parentProjectionName = parentProjectionName;
this.parentDbObject_Id = settings.GetVwProjectionList(userInfo.ProfileId).Where(x => x.Projection_Name == parentProjectionName).Select(x => x.DbObject_Id).FirstOrDefault();
this.parentEntityId = parentEntityId;
if (!string.IsNullOrEmpty(projectionRelationName))
{
vwProjectionRelation vwProjectionRelation = settings.VwProjectionRelationList.Where(r => r.ProjectionRelation_Name == projectionRelationName).FirstOrDefault();
if (vwProjectionRelation != null && vwProjectionRelation.ProjectionRelation_Id > 0)
{
this.vwProjectionRelation = vwProjectionRelation;
}
}
this.columns = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(x.Column_VisibleOnGrid == 1 || x.Column_VisibleOnGrid == 2 || x.Column_IsPrimaryKey || x.ProjectionColumn_Name == sortOrderColumnName) &&
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2)
).ToList();
if (ignoreSwitchIsVisibleOnFilter)
{
this.columnsForFilter = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2)
).ToList();
}
else
{
this.columnsForFilter = settings.GetVwProjectionColumnList(userInfo.ProfileId, projectionName).Where(x =>
(x.Column_IsVisibleOnFilter) &&
(x.AccessRightsType_Id == 1 || x.AccessRightsType_Id == 2)
).ToList();
}
FillForeignTablesAndSortOrderString();
}
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());
}