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";
}