private static Dictionary <int, bool> CheckSecurity(DbConnection sqlConnection, int parentId, IEnumerable <int> testIds, int userId, int startLevel, string entityName, string parentEntityName, string columnName)
{
var granted = new Dictionary <int, bool>();
var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, 0, startLevel, PermissionLevel.FullAccess,
entityName, parentEntityName, parentId);
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true);
var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false);
var sql = $@" select
i.id,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when pi.{columnName} is null then {falseValue} else {trueValue} end")} as granted
from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
left join ({securitySql}) as pi on pi.{columnName} = i.id " ;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", testIds, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
granted[Convert.ToInt32(reader["id"])] = (bool)reader["granted"];
}
}
}
return(granted);
}
public static DataRow[] GetRelationSecurityFields(DbConnection sqlConnection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
string contentLinkSql = $@"
SELECT
link_id AS link_id,
l_content_id AS content_id,
r_content_id AS linked_content_id
FROM content_to_content
UNION
SELECT
link_id AS link_id,
r_content_id AS content_id,
l_content_id AS linked_content_id
FROM content_to_content
";
var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true);
var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false);
var sqlText = $@"
select coalesce(ca3.content_id, ca1.content_id) as path_content_id, coalesce(ca4.CONTENT_ID, cl.linked_content_id) as rel_content_id, ca1.content_id,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca1.link_id is not null then {trueValue} else {falseValue} end")} as is_m2m,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca2.attribute_id is not null then {trueValue} else {falseValue} end")} as is_ext,
ca1.is_classifier,
ca1.attribute_id, ca1.attribute_name, ca1.link_id, ca2.ATTRIBUTE_NAME as agg_attribute_name
from CONTENT_ATTRIBUTE ca1
left join ({contentLinkSql}) cl on ca1.content_id = cl.content_id and ca1.link_id = cl.link_id
left join CONTENT_ATTRIBUTE ca4 on ca1.RELATED_ATTRIBUTE_ID = ca4.ATTRIBUTE_ID
left join content_attribute ca2 on ca1.content_id = ca2.content_id and ca2.AGGREGATED = {trueValue}
left join content_attribute ca3 on ca2.RELATED_ATTRIBUTE_ID = ca3.attribute_Id
where ca1.USE_RELATION_SECURITY = {trueValue}
" ;
return(Common.GetDataTableForQuery(sqlConnection, sqlText).AsEnumerable().ToArray());
}
public static IEnumerable <DataRow> GetMenuStatusList(
DbConnection sqlConnection, QPModelDataContext efContext, int userId, bool isAdmin,
string menuCode, int entityId)
{
var useSecurity = !isAdmin;
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var menuId = efContext.ContextMenuSet.First(x => x.Code == menuCode).Id;
string query;
if (!useSecurity)
{
query = $@"
SELECT ba.CODE, {SqlQuerySyntaxHelper.ToBoolSql(databaseType, true)} as visible
FROM CONTEXT_MENU_ITEM cmi
INNER JOIN BACKEND_ACTION ba on ba.ID = cmi.ACTION_ID
WHERE cmi.context_menu_id = {menuId}
";
}
else
{
var level = GetEntityAccessLevel(sqlConnection, efContext, userId, 0, menuCode, entityId);
var secQuery = PermissionHelper.GetActionPermissionsAsQuery(efContext, userId);
var least = SqlQuerySyntaxHelper.Least(databaseType, "SEC.PERMISSION_LEVEL", level.ToString());
query = $@"
SELECT ba.CODE,
CAST((
CASE WHEN {least} >= PL.PERMISSION_LEVEL THEN 1 ELSE 0
END ) AS BIT) as visible
FROM CONTEXT_MENU_ITEM cmi
INNER JOIN BACKEND_ACTION ba on ba.ID = cmi.ACTION_ID
INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID
INNER JOIN PERMISSION_LEVEL PL ON PL.PERMISSION_LEVEL_ID = AT.REQUIRED_PERMISSION_LEVEL_ID
INNER JOIN ({secQuery}) SEC ON SEC.BACKEND_ACTION_ID = ba.ID
WHERE cmi.context_menu_id = {menuId}
";
}
return(Common.GetDataTableForQuery(sqlConnection, query));
}
public static IEnumerable <DataRow> GetActionStatusList(QPModelDataContext efContext, DbConnection sqlConnection, int userId, string actionCode, int?actionId, int entityId, string entityCode, bool isAdmin)
{
var useSecurity = !isAdmin;
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
string query;
if (!useSecurity)
{
query = $@"
SELECT ba.CODE, {SqlQuerySyntaxHelper.ToBoolSql(databaseType, true)} as visible
FROM ACTION_TOOLBAR_BUTTON atb
INNER JOIN BACKEND_ACTION ba on ba.ID = atb.ACTION_ID
INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID
WHERE atb.PARENT_ACTION_ID = {actionId} AND at.items_affected = 1
";
}
else
{
var level = GetEntityAccessLevel(sqlConnection, efContext, userId, 0, entityCode, entityId);
var least = SqlQuerySyntaxHelper.Least(databaseType, "SEC.PERMISSION_LEVEL", level.ToString());
var secQuery = PermissionHelper.GetActionPermissionsAsQuery(efContext, userId);
query = $@"
SELECT ba.CODE,
CAST((
CASE WHEN {least} >= PL.PERMISSION_LEVEL THEN 1 ELSE 0
END ) AS BIT) as visible
FROM ACTION_TOOLBAR_BUTTON atb
INNER JOIN BACKEND_ACTION ba on ba.ID = atb.ACTION_ID
INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID
INNER JOIN PERMISSION_LEVEL PL ON PL.PERMISSION_LEVEL_ID = AT.REQUIRED_PERMISSION_LEVEL_ID
INNER JOIN ({secQuery}) SEC ON SEC.BACKEND_ACTION_ID = ba.ID
WHERE atb.PARENT_ACTION_ID = {actionId} AND at.items_affected = 1
";
}
return(Common.GetDataTableForQuery(sqlConnection, query));
}
private static string GetSelectExpression(DatabaseType dbType, RelationSecurityPathItem n)
{
if (n.IsClassifier)
{
return($"c{n.JoinOrder}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, n.AttributeName)}");
}
var fieldName = dbType == DatabaseType.Postgres ? $"\"{n.RelContentId}\"" : $"'{n.RelContentId}'";
var castToInt = dbType == DatabaseType.Postgres ? "::integer" : string.Empty;
return(n.LinkId.HasValue
? $"{SqlQuerySyntaxHelper.DbSchemaName(dbType)}.qp_link_ids({n.LinkId}{castToInt}, c{n.JoinOrder}.CONTENT_ITEM_ID{castToInt}, {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}) as {fieldName}"
: $"{SqlQuerySyntaxHelper.CastToString(dbType, $"c{n.Order}.content_item_id")} as {fieldName}");
}
private static string GetSqlQuery(QPModelDataContext context, DbConnection connection, string entityTypeCode, int?parentEntityId, bool isFolder, bool isGroup, string groupItemCode, int entityId, int userId, bool isAdmin, string customerCode, bool enableContentGrouping, bool countOnly = false)
{
var entityTypes = EntityTypeCache.GetEntityTypes(context, customerCode, userId);
var entityType = entityTypes.FirstOrDefault(x => x.Code.Equals(entityTypeCode, StringComparison.InvariantCultureIgnoreCase));
var parentGroupCode = entityType == null || !enableContentGrouping
? null
: entityTypes.FirstOrDefault(x => x.Id == entityType.GroupParentId)?.Code;
var realParentId = isGroup ? GetParentEntityId(context, connection, (decimal)parentEntityId, entityTypeCode, customerCode, userId) : parentEntityId;
var currentIsGroup = false;
string currentGroupItemCode = null;
var newEntityTypeCode = entityTypeCode;
var newIsFolder = isFolder;
if (!string.IsNullOrWhiteSpace(parentGroupCode))
{
if (isFolder)
{
currentGroupItemCode = entityTypeCode;
newEntityTypeCode = parentGroupCode;
currentIsGroup = true;
}
}
else if (!string.IsNullOrWhiteSpace(groupItemCode))
{
if (!isFolder)
{
newIsFolder = true;
newEntityTypeCode = groupItemCode;
}
}
var newEntityType = entityTypes.FirstOrDefault(x => x.Code.Equals(newEntityTypeCode, StringComparison.InvariantCultureIgnoreCase));
var realParentIdStr = realParentId.HasValue ? realParentId.ToString() : "NULL";
var iconField = newEntityType?.IconField ?? "NULL";
var iconModifierField = newEntityType?.IconModifierField ?? "NULL";
var parentIdField = newEntityType?.ParentIdField;
string realParentIdField = null;
if (isGroup)
{
realParentIdField = parentIdField;
parentIdField = newEntityType?.GroupParentIdField;
}
var sqlSb = new StringBuilder();
var selectSb = new StringBuilder();
var whereSb = new StringBuilder();
var orderSb = new StringBuilder();
string sql;
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(context);
var useSecurity = UseSecurity(isAdmin, databaseType);
if (newIsFolder || !string.IsNullOrWhiteSpace(newEntityType?.RecurringIdField))
{
if (newEntityType?.HasItemNodes ?? false)
{
var orderColumn = (string.IsNullOrWhiteSpace(newEntityType.OrderField) ? newEntityType.TitleField : newEntityType.OrderField).FixColumnName(databaseType);
selectSb.AppendLine($@"
{newEntityType.Source}.{newEntityType.IdField} AS id,
{newEntityType.TitleField} AS title,
{iconField} as icon,
{iconModifierField} as icon_modifier,
{orderColumn} as sortorder
");
whereSb.AppendLine("1 = 1");
if (!string.IsNullOrWhiteSpace(parentIdField) && parentEntityId != 0)
{
whereSb.AppendLine($" AND {parentIdField} = {parentEntityId}");
}
if (!string.IsNullOrWhiteSpace(newEntityType.RecurringIdField))
{
whereSb.AppendLine($" AND {newEntityType.RecurringIdField} {(newIsFolder ? " is null" : $" = {parentEntityId}")}");
}
if (entityId != 0)
{
whereSb.AppendLine($" AND {newEntityType.Source}.{newEntityType.IdField} = {entityId}");
}
orderSb.AppendLine(orderColumn);
}
if (string.IsNullOrWhiteSpace(newEntityType.SourceSP))
{
if (!string.IsNullOrWhiteSpace(selectSb.ToString()) && !string.IsNullOrWhiteSpace(newEntityType.Source) && !string.IsNullOrWhiteSpace(whereSb.ToString()))
{
sqlSb.AppendLine($"select {selectSb} from {newEntityType.Source} where {whereSb}");
}
}
else
{
decimal?siteId;
switch (newEntityType.SourceSP)
{
case "qp_sites_list":
sqlSb.AppendLine(GetSitesListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), false, userId, useSecurity));
break;
case "qp_real_content_list":
siteId = !string.IsNullOrWhiteSpace(realParentIdField) ? (decimal?)realParentId.Value : parentEntityId;
sqlSb.AppendLine(GetContentListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), false, siteId, userId, useSecurity));
break;
case "qp_virtual_content_list":
siteId = realParentId.HasValue ? (decimal?)realParentId.Value : parentEntityId;
sqlSb.AppendLine(GetContentListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), true, siteId, userId, useSecurity));
break;
case "qp_site_folder_list":
siteId = realParentId.HasValue ? (decimal?)realParentId.Value : parentEntityId;
var parentFolderId = newIsFolder ? 0 : parentEntityId.Value;
sqlSb.AppendLine(GetSiteFolderList(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), siteId, parentFolderId, userId, useSecurity));
break;
}
}
if (countOnly)
{
return(string.IsNullOrWhiteSpace(sqlSb.ToString())
? null
: $"SELECT COUNT(ID) FROM ({sqlSb}) as innerSql");
}
if (string.IsNullOrWhiteSpace(sqlSb.ToString()))
{
return(null);
}
sql = " SELECT\n" +
$"{realParentIdStr} as parent_id,\n" +
$"{(isGroup ? $"{parentEntityId}" : "NULL")} as parent_group_id,\n" +
$"'{newEntityTypeCode}' as code,\n" +
$"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, false)} as is_folder,\n" +
$"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, currentIsGroup)} as is_group,\n" +
$"{(!string.IsNullOrWhiteSpace(currentGroupItemCode) ? $"'{currentGroupItemCode}'" : "NULL")} as group_item_code,\n" +
"CASE WHEN i.ICON is not null THEN i.ICON\n" +
$"WHEN i.ICON_MODIFIER is not null THEN {SqlQuerySyntaxHelper.ConcatStrValues(databaseType, $"'{newEntityTypeCode}'", SqlQuerySyntaxHelper.CastToString(databaseType, "i.ICON_MODIFIER"), "'.gif'")}\n" +
$"ELSE {SqlQuerySyntaxHelper.ConcatStrValues(databaseType, $"'{newEntityTypeCode}'", "'.gif'")} END\n" +
"AS icon,\n" +
$"{SqlQuerySyntaxHelper.NullableDbValue(databaseType, newEntityType?.DefaultActionId)} AS default_action_id,\n" +
$"{SqlQuerySyntaxHelper.NullableDbValue(databaseType, newEntityType?.ContextMenuId)} as context_menu_id,\n" +
$"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, !string.IsNullOrWhiteSpace(newEntityType?.RecurringIdField))} as is_recurring,\n" +
"i.id,\n" +
"i.title,\n" +
"i.sortorder\n" +
$"FROM ( {sqlSb} ) as i\n";
}
