Beispiel #1
0
        public static void UpdateChildDelayedSchedule(DbConnection connection, int articleId)
        {
            var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var sql          = SqlQuerySyntaxHelper.SpCall(databaseType, "qp_copy_schedule_to_child_delays", articleId.ToString());

            ExecuteSql(connection, sql);
        }
Beispiel #2
0
        public static void ReplicateItems(DbConnection sqlConnection, int[] articleIds, int[] fieldIds)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            var sql    = (dbType == DatabaseType.SqlServer) ? "qp_replicate_items" : "call qp_replicate_items(@ids, @attr_ids);";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                if (dbType == DatabaseType.SqlServer)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.NVarChar, -1)
                    {
                        Value = string.Join(",", articleIds)
                    });
                    cmd.Parameters.Add(new SqlParameter("@attr_ids", SqlDbType.NVarChar, -1)
                    {
                        Value = string.Join(",", fieldIds)
                    });
                }
                else
                {
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", articleIds, dbType));
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attr_ids", fieldIds, dbType));
                }

                cmd.Parameters.AddWithValue("@modification_update_interval", -1);
                cmd.ExecuteNonQuery();
            }
        }
Beispiel #3
0
        public static void PersistArticle(DbConnection currentDbConnection, string customerCode, string xml, out int id)
        {
            var databaseType = DatabaseTypeHelper.ResolveDatabaseType(currentDbConnection);
            var ns           = SqlQuerySyntaxHelper.DbSchemaName(databaseType);
            var sql          = $"select id, modified from {ns}.qp_persist_article(@xml)";

            using (var cmd = DbCommandFactory.Create(sql, currentDbConnection))
            {
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, databaseType));
                var dt = new DataTable();
                try
                {
                    DataAdapterFactory.Create(cmd).Fill(dt);
                }
                catch (PostgresException ex)
                {
                    Logger.Error()
                    .Exception(ex)
                    .Message("Error while persisting article with xml: {xml}\n Query: {sql}", xml, sql)
                    .Property("customerCode", customerCode)
                    .Write();

                    throw;
                }
                id = (int)dt.Rows[0]["id"];
            }
        }
Beispiel #4
0
        public static IEnumerable <DataRow> BatchInsert(DbConnection sqlConnection, DataTable articles, bool visible, int userId)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);

            using (var cmd = DbCommandFactory.Create(BatchInsertQuery, sqlConnection))
            {
                if (dbType == DatabaseType.SqlServer)
                {
                    cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured)
                    {
                        TypeName = "Values", Value = articles
                    });
                }
                else
                {
                    cmd.CommandText = $@"select * from qp_batch_insert(@values, @visible, @userId);";
                    var xml = GetValuesDoc(articles).ToString();
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType));
                }
                cmd.Parameters.AddWithValue("@visible", visible ? 1 : 0);
                cmd.Parameters.AddWithValue("@userId", userId);

                var dt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(dt);
                return(dt.AsEnumerable().ToArray());
            }
        }
Beispiel #5
0
        public static void CreateContentViews(DbConnection cnn, int id, bool withUnited = true)
        {
            var dbType = GetDbType(cnn);
            var idStr  = id.ToString();

            if (dbType == DatabaseType.SqlServer)
            {
                if (withUnited)
                {
                    var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_recreate", idStr);
                    ExecuteSql(cnn, unitedSql);
                }

                var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_recreate", idStr);
                ExecuteSql(cnn, feSql);
            }
            else
            {
                if (withUnited)
                {
                    var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_create", idStr);
                    ExecuteSql(cnn, unitedSql);
                }

                var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_create", idStr);
                ExecuteSql(cnn, feSql);

                var newSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_create", idStr);
                ExecuteSql(cnn, newSql);
            }
        }
Beispiel #6
0
        private static Dictionary <int, int?> GetPermissionLevels(DbConnection sqlConnection, int[] ids, int userId, int groupId, string entityTypeName, string parentEntityTypeName = "", int parentId = 0)
        {
            var result      = new Dictionary <int, int?>();
            var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, groupId, PermissionLevel.Deny, PermissionLevel.FullAccess,
                                                              entityTypeName, parentEntityTypeName, parentId);

            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);

            var sql = $@" select i.id, pi.permission_level from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
				left join ({securitySql}) as pi on pi.{entityTypeName}_id = i.id "                ;

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result[Convert.ToInt32(reader["id"])] = Converter.ToNullableInt32(reader["permission_level"]);
                    }
                }
            }

            return(result);
        }
Beispiel #7
0
        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);
        }
Beispiel #8
0
        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());
        }
Beispiel #9
0
        public static void DropLinkView(DbConnection connection, ContentToContentDAL item)
        {
            var dbType = GetDbType(connection);
            var spName = dbType == DatabaseType.SqlServer ? "qp_drop_link_view" : "qp_link_view_drop";
            var sql    = SqlQuerySyntaxHelper.SpCall(dbType, spName, item.LinkId.ToString(CultureInfo.InvariantCulture));

            ExecuteSql(connection, sql);
        }
Beispiel #10
0
        public static List <int> InsertArticleIds(DbConnection sqlConnection, string xml, bool withGuids)
        {
            var    result       = new List <int>();
            var    withGuidsStr = withGuids ? ", UNIQUE_ID" : "";
            var    dbType       = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            string baseInsert   = $@"INSERT INTO CONTENT_ITEM (VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr}) ";
            string baseSelect   = $@"SELECT VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr} ";
            string sql          = dbType == DatabaseType.SqlServer ? $@"
                    DECLARE @NewArticles TABLE (CONTENT_ID int, STATUS_TYPE_ID int, VISIBLE int, LAST_MODIFIED_BY int, UNIQUE_ID uniqueidentifier)
                    DECLARE @NewIds TABLE ([ID] INT);

                    INSERT INTO @NewArticles
                    SELECT
                        doc.col.value('./@contentId', 'int') CONTENT_ID
                        ,doc.col.value('./@statusId', 'int') STATUS_TYPE_ID
                        ,doc.col.value('./@visible', 'int') VISIBLE
                        ,doc.col.value('./@userId', 'int') LAST_MODIFIED_BY
                        ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID
                        FROM @xml.nodes('ITEMS/ITEM') doc(col)

                    {baseInsert}
                    OUTPUT inserted.CONTENT_ITEM_ID INTO @NewIds
                    {baseSelect}
                    from @NewArticles
                    SELECT ID FROM @NewIds;
                " : $@"
                WITH inserted(id) AS
                (
                    {baseInsert}
                    {baseSelect}
                    from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS
                        CONTENT_ID int PATH '@contentId',
                        STATUS_TYPE_ID int PATH '@statusId',
                        VISIBLE int PATH '@visible',
                        LAST_MODIFIED_BY int PATH '@userId',
                        UNIQUE_ID uuid PATH '@guid'
                    ) x returning CONTENT_ITEM_ID
                ) select id from inserted;";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, dbType));
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id;
                        if (int.TryParse(reader.GetValue(0).ToString(), out id))
                        {
                            result.Add(id);
                        }
                    }
                }
                return(result);
            }
        }
Beispiel #11
0
        public static void CreateArticleVersions(DbConnection connection, int userId, int[] ids)
        {
            var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var sql          = SqlQuerySyntaxHelper.SpCall(databaseType, "qp_create_content_item_versions", "@ids, @last_modified_by");

            using (var cmd = DbCommandFactory.Create(sql, connection))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, databaseType));
                cmd.Parameters.AddWithValue("@last_modified_by", userId);
                cmd.ExecuteNonQuery();
            }
        }
Beispiel #12
0
        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}");
        }
Beispiel #13
0
        public static void UpdateNotForReplication(DbConnection sqlConnection, int[] ids, int userId)
        {
            var dbType  = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            var setTrue = dbType == DatabaseType.SqlServer ? "1" : "true";
            var sql     = $@"
            UPDATE CONTENT_ITEM SET NOT_FOR_REPLICATION = {setTrue}, MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}, LAST_MODIFIED_BY = @userId
            WHERE {SqlQuerySyntaxHelper.IsFalse(dbType, "NOT_FOR_REPLICATION")} AND CONTENT_ITEM_ID IN (select id from {Common.IdList(dbType, "@ids")});
            ";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.ExecuteNonQuery();
            }
        }
Beispiel #14
0
        public static void InsertArticleValues(DbConnection sqlConnection, string xmlParameter)
        {
            var    dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            string sql    = dbType == DatabaseType.SqlServer ? "qp_insertArticleValues" : PgTableValuesBlock + $@"
                update content_data cd set data = coalesce(new.blob_data, new.data) from new
                where cd.content_item_id = new.content_item_id and cd.attribute_id = new.attribute_id;
            ";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
                var resultDt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(resultDt);
            }
        }
Beispiel #15
0
        public static void DropContentViews(DbConnection cnn, int id)
        {
            var dbType = GetDbType(cnn);
            var idStr  = id.ToString();

            if (dbType != DatabaseType.SqlServer)
            {
                var newSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_drop", idStr);
                ExecuteSql(cnn, newSql);

                var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_drop", idStr);
                ExecuteSql(cnn, feSql);

                var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_drop", idStr);
                ExecuteSql(cnn, unitedSql);
            }
        }
Beispiel #16
0
        public static void UpdateArticleGuids(DbConnection sqlConnection, List <Tuple <int, Guid> > guidsByIdToUpdate)
        {
            if (guidsByIdToUpdate.Any())
            {
                var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
                var doc    = new XDocument();
                doc.Add(new XElement("ITEMS"));
                foreach (var tuple in guidsByIdToUpdate)
                {
                    var elem = new XElement("ITEM");
                    elem.Add(new XAttribute("id", tuple.Item1));
                    elem.Add(new XAttribute("guid", tuple.Item2));
                    doc.Root?.Add(elem);
                }

                string sql = dbType == DatabaseType.SqlServer ? $@"
                    DECLARE @NewIds TABLE (CONTENT_ITEM_ID int, UNIQUE_ID uniqueidentifier)

                    INSERT INTO @NewIds
                    SELECT
                        doc.col.value('./@id', 'int') CONTENT_ITEM_ID
                        ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID
                        FROM @xml.nodes('ITEMS/ITEM') doc(col)

                    UPDATE CONTENT_ITEM SET UNIQUE_ID = i.UNIQUE_ID FROM CONTENT_ITEM ci INNER JOIN @NewIds i
                    on ci.CONTENT_ITEM_ID = i.CONTENT_ITEM_ID
                " : $@"
                WITH new AS
                (
                    select x.* from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS
                        content_item_id int PATH '@id',
                        unique_id uuid PATH '@guid'
                    ) x
                )
                update content_item ci set unique_id = new.unique_id from new
                where ci.content_item_id = new.content_item_id;";


                using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
                {
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), dbType));
                    cmd.ExecuteNonQuery();
                }
            }
        }
Beispiel #17
0
        public static void RecreateNewViews(DbConnection cnn, int id)
        {
            var dbType = GetDbType(cnn);
            var idStr  = id.ToString();

            if (dbType == DatabaseType.SqlServer)
            {
                var recreateSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_recreate", idStr);
                ExecuteSql(cnn, recreateSql);
            }
            else
            {
                var dropSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_drop", idStr);
                ExecuteSql(cnn, dropSql);

                var createSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_create", idStr);
                ExecuteSql(cnn, createSql);
            }
        }
Beispiel #18
0
        public static void ValidateO2MValues(DbConnection sqlConnection, string xmlParameter, string message)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            var tableDefintionBlock = dbType == DatabaseType.SqlServer ? $@"
                DECLARE @NewArticles TABLE (CONTENT_ITEM_ID int, ATTRIBUTE_ID int, DATA nvarchar(3500), BLOB_DATA nvarchar(max))
                INSERT INTO @NewArticles
                    SELECT
                     doc.col.value('(CONTENT_ITEM_ID)[1]', 'int') CONTENT_ITEM_ID
                    ,doc.col.value('(ATTRIBUTE_ID)[1]', 'int') ATTRIBUTE_ID
                    ,doc.col.value('(DATA)[1]', 'nvarchar(3500)') DATA
                    ,doc.col.value('(BLOB_DATA)[1]', 'nvarchar(max)') BLOB_DATA
                    FROM @xmlParameter.nodes('/PARAMETERS/FIELDVALUE') doc(col)
            " : PgTableValuesBlock;

            var tableUsing = dbType == DatabaseType.SqlServer ? "@NewArticles a" : "new a";

            string sql = $@"{tableDefintionBlock}
                select * from
                (
                    select a.*, ca.ATTRIBUTE_NAME, rca.CONTENT_ID as RELATED_CONTENT_ID from {tableUsing}
                    inner join CONTENT_ATTRIBUTE ca on a.ATTRIBUTE_ID = ca.ATTRIBUTE_ID
                    inner join CONTENT_ATTRIBUTE rca on ca.RELATED_ATTRIBUTE_ID = rca.ATTRIBUTE_ID and ca.CONTENT_ID <> rca.CONTENT_ID
                    inner join CONTENT rc on rc.CONTENT_ID = rca.CONTENT_ID and rc.VIRTUAL_TYPE <> 3
                    where a.data != ''
                ) as a
                left join CONTENT_ITEM ci on ci.CONTENT_ITEM_ID = cast(a.data as numeric)
                where ci.CONTENT_ID is null or ci.CONTENT_ID <> a.RELATED_CONTENT_ID";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
                var resultDt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(resultDt);
                if (resultDt.AsEnumerable().Any())
                {
                    var dr    = resultDt.Rows[0];
                    var title = dr.Field <string>("ATTRIBUTE_NAME");
                    var data  = dr.Field <string>("DATA");
                    var id    = dr.Field <int>(FieldName.ContentItemId).ToString();
                    throw new ArgumentException(string.Format(message, id, title, data));
                }
            }
        }
Beispiel #19
0
        public static int[] GetArticleIdsByGuids(DbConnection sqlConnection, Guid[] guids)
        {
            if (guids == null)
            {
                throw new ArgumentNullException(nameof(guids));
            }
            var    databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            var    noLock       = WithNoLock(databaseType);
            var    textCast     = (databaseType == DatabaseType.Postgres) ? "::text" : "";
            string xmlSource;

            if (databaseType == DatabaseType.Postgres)
            {
                xmlSource = "XMLTABLE('/guids/guid' PASSING @xml COLUMNS unique_id uuid PATH '.')";
            }
            else
            {
                xmlSource = "(SELECT doc.col.value('.[1]', 'nvarchar(max)') UNIQUE_ID FROM @xml.nodes('/guids/guid') doc(col))";
            }

            var query = $"select coalesce(ci.content_item_id, 0), guids.unique_id{textCast} from {xmlSource} guids left join content_item ci {noLock} on ci.unique_id = guids.unique_id";

            using (var cmd = DbCommandFactory.Create(query, sqlConnection))
            {
                var doc = new XDocument(new XElement("guids"));
                doc.Root?.Add(guids.Select(n => new XElement("guid", n.ToString())));
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), databaseType));

                var result = new Dictionary <Guid, int>();
                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result.Add(new Guid(dr.GetString(1)), (int)dr.GetDecimal(0));
                    }
                }

                return(guids.Select(n => result[n]).ToArray());
            }
        }
Beispiel #20
0
        public static void BatchUpdate(DbConnection sqlConnection, DataTable articles, int userId)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            var sql    = dbType == DatabaseType.SqlServer ? SqlBatchUpdateQuery : PgBatchUpdateQuery;

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                if (dbType == DatabaseType.SqlServer)
                {
                    cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured)
                    {
                        TypeName = "Values", Value = articles
                    });
                }
                else
                {
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", GetValuesDoc(articles).ToString(), dbType));
                }
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.ExecuteNonQuery();
            }
        }
Beispiel #21
0
        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));
        }
Beispiel #22
0
        public static void UpdateArticlesDateTime(DbConnection sqlConnection, string xmlParameter)
        {
            var    dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            string sql    = dbType == DatabaseType.SqlServer ? "qp_update_acrticle_modification_date" : $@"
                WITH new AS
                (
                    select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS
                        content_item_id int PATH '@id',
                        last_modified_by int PATH '@modifiedBy'
                    ) x
                )
                update content_item ci set modified = now(), last_modified_by = new.last_modified_by from new
                where ci.content_item_id = new.content_item_id;
                ";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
                var resultDt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(resultDt);
            }
        }
Beispiel #23
0
        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));
        }
Beispiel #24
0
        public static IEnumerable <DataRow> GetRelations(DbConnection sqlConnection, DataTable articles)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);

            using (var cmd = DbCommandFactory.Create(GetRelationsQuery(dbType), sqlConnection))
            {
                if (dbType == DatabaseType.SqlServer)
                {
                    cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured)
                    {
                        TypeName = "Values", Value = articles
                    });
                }
                else
                {
                    var xml = GetValuesDoc(articles).ToString();
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType));
                }

                var dt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(dt);
                return(dt.AsEnumerable().ToArray());
            }
        }
Beispiel #25
0
        public static void InsertO2MFieldValues(DbConnection sqlConnection, string xmlParameter)
        {
            var    dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
            string sql    = dbType == DatabaseType.SqlServer ? "qp_update_o2mfieldvalues" : $@"
                WITH new AS
                (
                    select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS
                        content_item_id int PATH '@id',
                        linked_id int PATH '@linked_id',
                        attribute_id int PATH '@field_id'
                    ) x
                )
                update content_data cd set data = new.linked_id from new
                where cd.content_item_id = new.CONTENT_ITEM_ID and cd.attribute_id = new.attribute_id;
                ";

            using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
            {
                cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType));
                var resultDt = new DataTable();
                DataAdapterFactory.Create(cmd).Fill(resultDt);
            }
        }
Beispiel #26
0
        public static string GetSecurityPathSql(DatabaseType dbType, List <RelationSecurityPathItem> items, int contentId)
        {
            if (items == null)
            {
                throw new ArgumentNullException(nameof(items));
            }

            var sqlBuilder = new StringBuilder();
            var lastItem   = items.Last();
            var selects    = Enumerable.Repeat(lastItem, 1).Concat(lastItem.Secondary).Select(item => GetSelectExpression(dbType, item));

            sqlBuilder.Append($"select c0.content_item_id as id, {string.Join(", ", selects)} from content_{contentId}_united c0 ");
            foreach (var item in items)
            {
                if (item != lastItem)
                {
                    if (item.LinkId.HasValue)
                    {
                        throw new ApplicationException("Invalid security path settings: m2m");
                    }

                    if (item.Secondary.Any())
                    {
                        throw new ApplicationException("Invalid security path settings: secondary");
                    }
                }

                if (!item.LinkId.HasValue)
                {
                    if (item.Extensions.Any())
                    {
                        var first = item.Extensions.First();
                        var inner = string.Join("union all " + Environment.NewLine,
                                                item.Extensions.Select(n =>
                                                                       n.LinkId.HasValue
                                    ? $@"select {n.AggAttributeName} as agg, il.linked_item_id as {first.AttributeName} From content_{n.ContentId}_united c
											inner join item_link il on c.content_item_id = il.item_id and il.link_id = {n.LinkId.Value} {Environment.NewLine} "
                                    : $"select {n.AggAttributeName} as agg, {n.AttributeName} as {first.AttributeName} From content_{n.ContentId}_united {Environment.NewLine}")
                                                );

                        sqlBuilder.AppendFormat("inner join ({3}{0}{3}) c{1} on c{1}.agg = c{2}.CONTENT_ITEM_ID{3}", inner, first.Order,
                                                item.JoinOrder, Environment.NewLine);
                        sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
                                                first.RelContentId, item.Order, first.Order, first.AttributeName, Environment.NewLine);
                    }
                    else if (!item.IsClassifier)
                    {
                        sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
                                                item.RelContentId, item.Order, item.JoinOrder, item.AttributeName, Environment.NewLine);
                    }
                }

                if (item == lastItem && item.Secondary != null)
                {
                    foreach (var secItem in item.Secondary.Where(n => !n.LinkId.HasValue && !n.IsClassifier))
                    {
                        sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
                                                secItem.RelContentId, secItem.Order, secItem.JoinOrder, secItem.AttributeName, Environment.NewLine);
                    }
                }
            }

            sqlBuilder.Append($"where c0.content_item_id in (select id from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})");
            return(sqlBuilder.ToString());
        }
Beispiel #27
0
        public static RelationSecurityInfo GetRelationSecurityInfo(DbConnection dbConnection, int contentId, int[] ids)
        {
            var dbType   = DatabaseTypeHelper.ResolveDatabaseType(dbConnection);
            var result   = new RelationSecurityInfo();
            var pathRows = GetRelationSecurityFields(dbConnection);

            var securityPathes = new List <List <RelationSecurityPathItem> >();
            var finder         = new RelationSecurityPathFinder(pathRows.ToList(), contentId);

            finder.Compute();
            securityPathes.Add(finder.CurrentPath);


            foreach (var extra in finder.ExtraFinders)
            {
                extra.Compute();
                securityPathes.Add(extra.CurrentPath);
            }

            foreach (var securityPath in securityPathes)
            {
                if (securityPath.Count <= 0)
                {
                    var isEndNode = finder.PathRows.Any(n => (Converter.ToNullableInt32(n["rel_content_id"]) ?? 0) == contentId);
                    if (!isEndNode)
                    {
                        result.MakeEmpty();
                    }
                    else
                    {
                        result.AddContentInItemMapping(contentId, ids.ToDictionary(n => n, m => Enumerable.Repeat(m, 1).ToArray()));
                    }

                    return(result);
                }

                var lastItem = securityPath.Last();
                var lastItemWithSecondary = Enumerable.Repeat(lastItem, 1).Concat(lastItem.Secondary).ToList();
                var contentIds            = lastItemWithSecondary.Where(n => !n.IsClassifier).Select(n => n.RelContentId).ToArray();
                var attNames = lastItemWithSecondary.Where(n => n.IsClassifier).Select(n => n.AttributeName).ToArray();
                foreach (var item in contentIds)
                {
                    result.AddContentInItemMapping(item, new Dictionary <int, int[]>());
                }

                var sql = GetSecurityPathSql(dbType, securityPath, contentId);
                using (var cmd = DbCommandFactory.Create(sql, dbConnection))
                {
                    cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ProcessSecurityPathSqlReader(reader, contentIds, attNames, result);
                        }
                    }
                }

                AppendNotFound(ids, contentIds, result);
            }

            return(result);
        }
Beispiel #28
0
        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";
            }
Beispiel #29
0
        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);
        }
Beispiel #30
0
 internal static DbParameter GetIdsDatatableParam(string paramName, IEnumerable <int> ids, DatabaseType databaseType = DatabaseType.SqlServer) => SqlQuerySyntaxHelper.GetIdsDatatableParam(paramName, ids, databaseType);