File indexing completed on 2024-04-21 06:00:36
0001 0002 DROP PROCEDURE IF EXISTS `generate_stat_cat_prod_count`; 0003 0004 DELIMITER $$ 0005 USE `pling`$$ 0006 CREATE 0007 DEFINER = CURRENT_USER PROCEDURE `generate_stat_cat_prod_count`() 0008 BEGIN 0009 0010 DROP TABLE IF EXISTS `tmp_stat_cat_prod_count`; 0011 CREATE TABLE `tmp_stat_cat_prod_count` 0012 ( 0013 `project_category_id` int(11) NOT NULL, 0014 `package_type_id` int(11) NULL, 0015 `count_product` int(11) NULL, 0016 INDEX `idx_package` (`project_category_id`, `package_type_id`) 0017 ) 0018 ENGINE MEMORY 0019 AS 0020 SELECT `sct2`.`project_category_id`, 0021 NULL AS `package_type_id`, 0022 count(`p`.`project_id`) AS `count_product` 0023 FROM `stat_cat_tree` AS `sct1` 0024 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0025 LEFT JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0026 GROUP BY `sct2`.`project_category_id` 0027 0028 UNION 0029 0030 SELECT `sct2`.`project_category_id`, 0031 `ppt`.`package_type_id`, 0032 count(`p`.`project_id`) AS `count_product` 0033 FROM `stat_cat_tree` AS `sct1` 0034 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0035 JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0036 JOIN `project_package_type` AS `ppt` ON `ppt`.`project_id` = `p`.`project_id` 0037 GROUP BY `sct2`.`lft`, `ppt`.`package_type_id`; 0038 0039 IF EXISTS(SELECT `table_name` 0040 FROM `INFORMATION_SCHEMA`.`TABLES` 0041 WHERE `table_schema` = DATABASE() 0042 AND `table_name` = 'stat_cat_prod_count') 0043 THEN 0044 RENAME TABLE `stat_cat_prod_count` TO `old_stat_cat_prod_count`, `tmp_stat_cat_prod_count` TO `stat_cat_prod_count`; 0045 0046 ELSE 0047 RENAME TABLE `tmp_stat_cat_prod_count` TO `stat_cat_prod_count`; 0048 0049 END IF; 0050 0051 0052 DROP TABLE IF EXISTS `old_stat_cat_prod_count`; 0053 0054 END$$ 0055 0056 DELIMITER ; 0057 0058 DROP EVENT IF EXISTS `e_generate_stat_cat_prod_count`; 0059 CREATE 0060 DEFINER = CURRENT_USER 0061 EVENT IF NOT EXISTS `e_generate_stat_cat_prod_count` 0062 ON SCHEDULE 0063 EVERY 2 MINUTE STARTS DATE_FORMAT(NOW(), '%Y-%m-%d 05:00:00') 0064 ON COMPLETION PRESERVE 0065 -- DISABLE ON SLAVE 0066 COMMENT 'Regenerates generate_stat_cat_prod_count table' 0067 DO 0068 CALL `pling`.generate_stat_cat_prod_count();