public static bool IsParentTypeForTree(QPModelDataContext context, string customerCode, int languageId, string entityTypeCode)
{
var entities = GetEntityTypes(context, customerCode, languageId);
var id = entities.Single(n => n.Code == entityTypeCode).Id;
return(entities.Any(n => n.ParentId == id && !n.Disabled));
}
public static int GetEntityAccessLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, string entityTypeCode, int entityId)
{
var actualEntityTypeCode = GetActualEntityTypeCode(entityTypeCode);
if (!IsSecurityDefined(actualEntityTypeCode) || entityId == 0 || IsAdmin(sqlConnection, userId, groupId))
{
return(PermissionLevel.FullAccess);
}
var predefinedLevel = GetPredefinedLevel(sqlConnection, context, userId, groupId, entityId, actualEntityTypeCode);
if (predefinedLevel.HasValue)
{
return(predefinedLevel.Value);
}
var result = GetPermissionLevel(sqlConnection, entityId, userId, groupId, actualEntityTypeCode);
if (!result.HasValue && actualEntityTypeCode == EntityTypeCode.OldSiteFolder)
{
return(GetFolderAccessLevel(sqlConnection, context, userId, groupId, actualEntityTypeCode, entityId));
}
return(result ?? PermissionLevel.Deny);
}
private static int?GetPredefinedLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, int entityId, string actualEntityTypeCode)
{
int?predefinedLevel = null;
switch (actualEntityTypeCode)
{
case EntityTypeCode.Content:
{
predefinedLevel = GetPredefinedContentLevel(sqlConnection, context, userId, groupId, entityId);
break;
}
case EntityTypeCode.ContentFolder:
{
predefinedLevel = GetPredefinedContentFolderLevel(sqlConnection, context, userId, groupId, entityId);
break;
}
case EntityTypeCode.OldArticle:
{
predefinedLevel = GetPredefinedArticleLevel(sqlConnection, context, userId, groupId, entityId);
break;
}
}
return(predefinedLevel);
}
public static string GetActionPermissionsAsQuery(QPModelDataContext context, decimal userId)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(context);
var actionSecQuery = GetPermittedItemsAsQuery(context,
userId,
startLevel: 0,
endLevel: 100,
entityTypeName: "BACKEND_ACTION"
);
var entitySecQuery = GetEntityPermissionAsQuery(context, userId);
var query = $@"
select AP.BACKEND_ACTION_ID, COALESCE(AP.PERMISSION_LEVEL, EP.PERMISSION_LEVEL, 0) AS PERMISSION_LEVEL from
(select L.PERMISSION_LEVEL AS PERMISSION_LEVEL, T.ID AS BACKEND_ACTION_ID, T.ENTITY_TYPE_ID FROM
({actionSecQuery}) P1
LEFT JOIN backend_action_access_permlevel P2 ON P1.BACKEND_ACTION_ID = P2.BACKEND_ACTION_ID and P1.permission_level = p2.permission_level and P2.{SqlQuerySyntaxHelper.EscapeEntityName(databaseType, "USER_ID")} = {userId}
RIGHT JOIN BACKEND_ACTION T ON P1.BACKEND_ACTION_ID = T.ID
LEFT join PERMISSION_LEVEL L ON P1.PERMISSION_LEVEL = L.PERMISSION_LEVEL
) AP
JOIN
({entitySecQuery}) EP ON AP.ENTITY_TYPE_ID = EP.ENTITY_TYPE_ID
";
return(query);
}
private static int GetFolderAccessLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, string entityTypeCode, int entityId)
{
var folder = context.SiteFolderSet.Single(n => n.Id == entityId);
return(folder.ParentId.HasValue ?
GetEntityAccessLevel(sqlConnection, context, userId, groupId, entityTypeCode, (int)folder.ParentId.Value) :
GetEntityAccessLevel(sqlConnection, context, userId, groupId, EntityTypeCode.Site, (int)folder.SiteId));
}
private static List <EntityTypeDAL> LoadEntityTypes(QPModelDataContext context)
{
return(context.EntityTypeSet
.Include(x => x.Parent)
.Include(x => x.CancelAction)
.Include(x => x.DefaultAction)
.Include(x => x.ContextMenu)
.ToList());
}
private static List <decimal> GetParentGroupIds(QPModelDataContext context, ICollection <decimal> childGroups)
{
return(context
.UserGroupSet
.Where(x => childGroups.Contains(x.Id))
.Include(x => x.ParentGroupToGroupBinds)
.SelectMany(x => x.ParentGroupToGroupBinds)
.Select(x => x.ParentGroupId)
.Distinct()
.ToList());
}
private static int?GetPredefinedArticleLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, int entityId)
{
int?resultLevel = null;
var contentId = context.ArticleSet.Include(n => n.Content)
.Where(n => n.Id == entityId && n.Content.AllowItemsPermission == 0)
.Select(n => n.ContentId).SingleOrDefault();
if (contentId != 0)
{
resultLevel = GetEntityAccessLevel(sqlConnection, context, userId, groupId, EntityTypeCode.Content, (int)contentId);
}
return(resultLevel);
}
public static DatabaseType ResolveDatabaseType(QPModelDataContext context)
{
switch (context)
{
case SqlServerQPModelDataContext _:
return(DatabaseType.SqlServer);
case NpgSqlQPModelDataContext _:
return(DatabaseType.Postgres);
default:
return(DatabaseType.Unknown);
}
}
public static void CreateComplexIndex(QPModelDataContext ctx, DbConnection cnn, ContentConstraintDAL constraint)
{
var dbType = GetDbType(cnn);
var ids = constraint.Rules.Select(n => n.FieldId).ToArray();
var indexName = $"constraint_{constraint.Id}_ind";
var asyncIndexName = $"constraint_{constraint.Id}_async_ind";
var tableName = "content_" + constraint.ContentId;
var asyncTableName = tableName + "_async";
var fields = ctx.FieldSet.Where(n => ids.Contains(n.Id)).OrderBy(n => n.Order)
.Select(n => Escape(dbType, n.Name)).ToArray();
var indexTemplate = $"CREATE INDEX {{0}} on {{1}} ({String.Join(",", fields)});";
var sql = String.Format(indexTemplate, indexName, tableName);
ExecuteSql(cnn, sql);
var asyncSql = String.Format(indexTemplate, asyncIndexName, asyncTableName);
ExecuteSql(cnn, asyncSql);
}
public static List <EntityTypeDAL> GetEntityTypes(QPModelDataContext context, string customerCode, int userId)
{
var key = GetKey(customerCode, userId);
if (!Cache.TryGetValue(key, out var types))
{
lock (Locker)
{
if (!Cache.TryGetValue(key, out types))
{
types = LoadEntityTypes(context);
Cache[key] = types;
}
}
}
return(types);
}
public static string GetEntityPermissionAsQuery(QPModelDataContext context, decimal userId)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(context);
var isPostgres = dbType == DatabaseType.Postgres;
var entitySecQuery = GetPermittedItemsAsQuery(context, userId, startLevel: 0, endLevel: 100, entityTypeName: "entity_type");
var permissionTable = "entity_type_access_permlevel";
if (isPostgres)
{
permissionTable = permissionTable.ToSnakeCase();
}
return($@"
select COALESCE(L.PERMISSION_LEVEL, 0) AS PERMISSION_LEVEL, T.ID AS ENTITY_TYPE_ID, HIDE FROM
({entitySecQuery}) P1
LEFT JOIN {permissionTable} P2 ON P1.entity_type_id = P2.entity_type_id and P1.permission_level = p2.permission_level and P2.USER_ID = {userId}
RIGHT JOIN ENTITY_TYPE T ON P1.ENTITY_TYPE_ID = T.ID
LEFT join PERMISSION_LEVEL L ON P1.PERMISSION_LEVEL = L.PERMISSION_LEVEL" );
}
public static DataRow GetDefaultArticleRow(QPModelDataContext context, DbConnection connection, int contentId)
{
var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var sql = "qp_get_default_article";
if (databaseType == DatabaseType.Postgres)
{
var fields = context.FieldSet.Where(n => n.ContentId == contentId).OrderBy(n => n.Name).ToArray();
var fieldNames = fields.Select(n => n.Name.ToLower()).ToArray();
var fieldNameResults = String.Join(",", fieldNames.Select(n => $@"""{n}"" TEXT"));
var fieldSelects = String.Join(",", fields.Select(n => $@"""{n.Name.ToLower()}""::{PgSelectType((int)n.TypeId)}"));
sql = $@"
SELECT {fieldSelects} FROM crosstab('
select 0::numeric as content_item_id, lower(ca.attribute_name),
case when ca.attribute_type_id in (9, 10) then coalesce(ca.default_value, ca.default_blob_value)
else qp_correct_data(ca.default_value::text, ca.attribute_type_id, ca.attribute_size, ca.default_value)::text
end as value from content_attribute ca
inner join content c on ca.content_id = c.content_id
where c.content_id = {contentId}
order by 1,2
') AS final_result(content_item_id numeric, {fieldNameResults})" ;
}
using (var cmd = DbCommandFactory.Create(sql, connection))
{
if (databaseType == DatabaseType.Postgres)
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@content_id", contentId);
}
var ds = new DataSet();
DataAdapterFactory.Create(cmd).Fill(ds);
return(0 == ds.Tables.Count || 0 == ds.Tables[0].Rows.Count ? null : ds.Tables[0].Rows[0]);
}
}
private static int?GetPredefinedContentLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, int entityId)
{
int?resultLevel = null;
var classifierId = context.FieldSet.Where(n => n.ContentId == entityId)
.Where(n => n.ClassifierId != null)
.Select(n => n.ClassifierId)
.SingleOrDefault();
if (classifierId.HasValue)
{
var baseContentId = context.FieldSet.Where(n => n.Id == classifierId.Value)
.Select(n => n.ContentId)
.SingleOrDefault();
if (baseContentId != 0)
{
resultLevel = GetEntityAccessLevel(sqlConnection, context, userId, groupId, EntityTypeCode.Content, (int)baseContentId);
}
}
return(resultLevel);
}
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));
}
public static IEnumerable <DataRow> GetChildFoldersList(DbConnection sqlConnection, QPModelDataContext context, bool isAdmin, int userId, int id, bool isSite, int?folderId, int permissionLevel, bool countOnly, out int totalRecords)
{
totalRecords = -1;
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var entityTypeName = isSite ? EntityTypeCode.OldSiteFolder : EntityTypeCode.ContentFolder;
var parentEntityTypeName = isSite ? EntityTypeCode.Site : EntityTypeCode.Content;
var blockFilter = string.Empty;
var useSecurity = !isAdmin;
int parentLevel;
if (entityTypeName == EntityTypeCode.ContentFolder)
{
useSecurity = false;
parentLevel = CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, parentEntityTypeName, id);
if (parentLevel == 0)
{
blockFilter += " AND 1 = 0 ";
}
}
else
{
parentLevel = folderId.HasValue
? CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, EntityTypeCode.SiteFolder, folderId.Value)
: CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, parentEntityTypeName, id);
}
var securitySql = useSecurity ? PermissionHelper.GetPermittedItemsAsQuery(
context, userId, 0, PermissionLevel.Deny, PermissionLevel.FullAccess,
entityTypeName, parentEntityTypeName, id
) : string.Empty;
var childrenParam = SqlQuerySyntaxHelper.CastToBool(dbType,
$@"
CASE WHEN (
SELECT COUNT(FOLDER_ID) FROM {entityTypeName} WHERE PARENT_FOLDER_ID = c.FOLDER_ID
) > 0 THEN 1 ELSE 0 END
");
var query = $@"
SELECT
{(countOnly
? "COUNT(c.FOLDER_ID) "
: $@"
c.FOLDER_ID,
c.NAME,
c.CREATED,
c.MODIFIED,
c.LAST_MODIFIED_BY,
{childrenParam} AS HAS_CHILDREN,
mu.{Escape(dbType, "USER_ID")} as MODIFIER_USER_ID,
mu.FIRST_NAME as MODIFIER_FIRST_NAME,
mu.LAST_NAME AS MODIFIER_LAST_NAME,
mu.EMAIL AS MODIFIER_EMAIL,
mu.{Escape(dbType, "LOGIN")} AS MODIFIER_LOGIN
{(useSecurity
? $", COALESCE(pi.permission_level, {parentLevel}) as EFFECTIVE_PERMISSION_LEVEL"
: string.Empty
)}
"
)} ";
public static IEnumerable <DataRow> GetTreeChildNodes(QPModelDataContext context, DbConnection connection, string entityTypeCode, int?parentEntityId, bool isFolder, bool isGroup, string groupItemCode, int entityId, int userId, bool isAdmin, string customerCode, bool enableContentGrouping)
{
var query = GetSqlQuery(context, connection, entityTypeCode, parentEntityId, isFolder, isGroup, groupItemCode, entityId, userId, isAdmin, customerCode, enableContentGrouping, false);
return(string.IsNullOrWhiteSpace(query) ? Enumerable.Empty <DataRow>() : Common.GetDataRows(connection, query));
}
public static string GetPermittedItemsAsQuery(
QPModelDataContext context,
decimal userId = 0,
decimal groupId = 0,
int startLevel = 2,
int endLevel = 4,
string entityTypeName = "content_item",
string parentEntityTypeName = "",
decimal parentEntityId = 0)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(context);
var isPostgres = dbType == DatabaseType.Postgres;
var level = 0;
var entityIdField = $"{entityTypeName}_id";
var parentEntityIdField = $"{parentEntityTypeName}_id";
var permissionTable = $"{entityTypeName}_access_permlevel";
var whereParentEntity = "";
if (!string.IsNullOrWhiteSpace(parentEntityTypeName) && parentEntityId != 0)
{
permissionTable += $"_{parentEntityTypeName}";
whereParentEntity += $" and {parentEntityIdField} = {parentEntityId}";
}
if (isPostgres)
{
permissionTable = permissionTable.ToSnakeCase();
}
var hide = entityTypeName.Equals("content", StringComparison.InvariantCultureIgnoreCase)
? isPostgres ? "MIN(hide::int) as hide " : "MIN(CONVERT(int, hide)) as hide "
: "0 as hide ";
var hint = isPostgres ? string.Empty : " with(nolock) ";
var selectUser =
$@" select {entityIdField} as id, max(permission_level) as pl, {hide}, 0 as level
from {permissionTable} {hint}
where user_id = {userId} {whereParentEntity}
group by {entityIdField}
";
var selectGroup =
$@" select {entityIdField} as id, max(permission_level) as pl, {hide}, {{0}} as level
from {permissionTable} {hint}
where group_id in ({{1}}) {whereParentEntity}
group by {entityIdField}
";
var defaultSql = $" select {entityIdField}, 0 as permission_level, 0 as hide from {entityTypeName} where 1 = 1 {whereParentEntity}";
var sbSql = new StringBuilder();
var groupsToProcess = new List <decimal>();
var usedGroups = new List <decimal>();
if (userId > 0)
{
sbSql.Append(selectUser);
var user = GetUserPropertiesById(context, userId);
groupsToProcess = user?.Groups?.Select(x => x.Id).Distinct().ToList() ?? new List <decimal>();
}
else if (groupId > 0)
{
groupsToProcess.Add(groupId);
}
while (groupsToProcess.Any())
{
level += 1;
if (level > 1 || sbSql.Length > 0)
{
sbSql.Append(" UNION ALL ");
}
sbSql.AppendFormat(selectGroup, level, string.Join(", ", groupsToProcess));
usedGroups.AddRange(groupsToProcess);
var parentGroupIds = GetParentGroupIds(context, groupsToProcess);
groupsToProcess = parentGroupIds
.Where(x => !groupsToProcess.Contains(x) && !usedGroups.Contains(x))
.ToList();
}
if (sbSql.Length == 0)
{
return(defaultSql);
}
return
($@"select id as {entityIdField}, pl as permission_level, hide
from (
select id, pl, hide, ROW_NUMBER() OVER(PARTITION BY id ORDER BY level) as num from (
{sbSql}
) as united_permissions
) as priority_permissions where priority_permissions.num = 1
and pl between {startLevel} and {endLevel}");
}
public static long GetTreeChildNodesCount(QPModelDataContext context, DbConnection connection, string entityTypeCode, int?parentEntityId, bool isFolder, bool isGroup, string groupItemCode, int entityId, int userId, bool isAdmin, string customerCode, bool enableContentGrouping)
{
var query = GetSqlQuery(context, connection, entityTypeCode, parentEntityId, isFolder, isGroup, groupItemCode, entityId, userId, isAdmin, customerCode, enableContentGrouping, true);
return(string.IsNullOrWhiteSpace(query) ? 0 : Common.ExecuteScalarLong(connection, query));
}
private static UserDAL GetUserPropertiesById(QPModelDataContext context, decimal userId)
{
return(context.UserSet
.Include(x => x.UserGroupBinds).ThenInclude(y => y.UserGroup)
.SingleOrDefault(u => u.Id == userId));
}
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";
}
private static int?GetPredefinedContentFolderLevel(DbConnection sqlConnection, QPModelDataContext context, int userId, int groupId, int entityId)
{
int?resultLevel = null;
var contentId = context.ContentFolderSet.Where(n => n.Id == entityId)
.Select(n => n.ContentId)
.SingleOrDefault();
if (contentId != 0)
{
resultLevel = GetEntityAccessLevel(sqlConnection, context, userId, groupId, EntityTypeCode.Content, (int)contentId);
}
return(resultLevel);
}
private static DatabaseType GetDbType(QPModelDataContext context) => DatabaseTypeHelper.ResolveDatabaseType(context);