File indexing completed on 2024-04-21 06:00:36
0001 0002 DROP PROCEDURE IF EXISTS `generate_stat_cat_tree`; 0003 0004 DELIMITER $$ 0005 CREATE 0006 DEFINER = CURRENT_USER PROCEDURE `generate_stat_cat_tree`() 0007 BEGIN 0008 0009 DROP TABLE IF EXISTS `tmp_stat_cat_tree`; 0010 CREATE TABLE `tmp_stat_cat_tree` 0011 ( 0012 `project_category_id` int(11) NOT NULL, 0013 `lft` int(11) NOT NULL, 0014 `rgt` int(11) NOT NULL, 0015 `title` varchar(100) NOT NULL, 0016 `name_legacy` varchar(50) NULL, 0017 `is_active` int(1), 0018 `orderPos` int(11) NULL, 0019 `depth` int(11) NOT NULL, 0020 `ancestor_id_path` varchar(100), 0021 `ancestor_path` varchar(256), 0022 `ancestor_path_legacy` varchar(256), 0023 PRIMARY KEY `primary` (`project_category_id`, `lft`, `rgt`) 0024 ) 0025 ENGINE MEMORY 0026 AS 0027 SELECT `pc`.`project_category_id`, 0028 `pc`.`lft`, 0029 `pc`.`rgt`, 0030 `pc`.`title`, 0031 `pc`.`name_legacy`, 0032 `pc`.`is_active`, 0033 `pc`.`orderPos`, 0034 count(`pc`.`lft`) - 1 AS `depth`, 0035 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0036 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path`, 0037 GROUP_CONCAT(IF(LENGTH(TRIM(`pc2`.`name_legacy`)) > 0, `pc2`.`name_legacy`, `pc2`.`title`) ORDER BY 0038 `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path_legacy` 0039 FROM `project_category` AS `pc`, 0040 `project_category` AS `pc2` 0041 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0042 AND `pc`.`is_active` = 1 0043 AND `pc2`.`is_active` = 1 0044 GROUP BY `pc`.`lft` -- HAVING depth >= 1 0045 ORDER BY `pc`.`lft`, `pc`.`orderPos`; 0046 0047 DROP TABLE IF EXISTS `tmp_stat_cat`; 0048 CREATE TABLE `tmp_stat_cat` 0049 ( 0050 `project_category_id` int(11) NOT NULL, 0051 `lft` int(11) NOT NULL, 0052 `rgt` int(11) NOT NULL, 0053 `title` varchar(100) NOT NULL, 0054 `name_legacy` varchar(50) NULL, 0055 `is_active` int(1), 0056 `orderPos` int(11) NULL, 0057 `depth` int(11) NOT NULL, 0058 `ancestor_id_path` varchar(100), 0059 `ancestor_path` varchar(256), 0060 `ancestor_path_legacy` varchar(256), 0061 `stores` varchar(256), 0062 PRIMARY KEY `primary` (`project_category_id`, `lft`, `rgt`) 0063 ) 0064 ENGINE MEMORY 0065 AS 0066 SELECT `sct`.*, 0067 GROUP_CONCAT(`csc`.`store_id` ORDER BY `csc`.`store_id`) AS `stores` 0068 FROM `tmp_stat_cat_tree` AS `sct` 0069 LEFT JOIN 0070 `config_store_category` AS `csc` ON FIND_IN_SET(`csc`.`project_category_id`, `sct`.`ancestor_id_path`) 0071 GROUP BY `sct`.`project_category_id` 0072 ORDER BY `sct`.`lft`; 0073 0074 IF EXISTS(SELECT `table_name` 0075 FROM `INFORMATION_SCHEMA`.`TABLES` 0076 WHERE `table_schema` = DATABASE() 0077 AND `table_name` = 'stat_cat_tree') 0078 THEN 0079 RENAME TABLE `stat_cat_tree` TO `old_stat_cat_tree`, `tmp_stat_cat` TO `stat_cat_tree`; 0080 0081 ELSE 0082 RENAME TABLE `tmp_stat_cat` TO `stat_cat_tree`; 0083 0084 END IF; 0085 0086 0087 DROP TABLE IF EXISTS `old_stat_cat_tree`; 0088 DROP TABLE IF EXISTS `tmp_stat_cat_tree`; 0089 0090 END$$ 0091 0092 DELIMITER ; 0093 0094 DROP EVENT IF EXISTS `e_generate_stat_cat_tree`; 0095 CREATE 0096 DEFINER = CURRENT_USER 0097 EVENT IF NOT EXISTS `e_generate_stat_cat_tree` 0098 ON SCHEDULE 0099 EVERY 60 MINUTE STARTS DATE_FORMAT(NOW(), '%Y-%m-%d 05:00:00') 0100 ON COMPLETION PRESERVE 0101 -- DISABLE ON SLAVE 0102 COMMENT 'Regenerates generate_stat_cat_tree table' 0103 DO 0104 CALL `pling`.generate_stat_cat_tree();