File indexing completed on 2024-04-28 17:16:10
0001 DELIMITER $$ 0002 CREATE 0003 DEFINER = CURRENT_USER PROCEDURE `fetchCatTreeForStore`(IN `STORE_ID` int(11)) 0004 BEGIN 0005 DROP TABLE IF EXISTS `tmp_store_cat`; 0006 CREATE TEMPORARY TABLE `tmp_store_cat` 0007 ( 0008 INDEX `idx_cat_id` (`project_category_id`) 0009 ) 0010 ENGINE MEMORY 0011 AS 0012 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0013 FROM `config_store_category` AS `csc` 0014 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0015 WHERE `csc`.`store_id` = `STORE_ID` 0016 GROUP BY `csc`.`store_category_id` 0017 ORDER BY `csc`.`order`, `pc`.`title`; 0018 0019 SET @`NEW_ORDER` := 0; 0020 0021 UPDATE `tmp_store_cat` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0022 0023 SELECT `sct`.`lft`, 0024 `sct`.`rgt`, 0025 `sct`.`project_category_id` AS `id`, 0026 `sct`.`title`, 0027 `scpc`.`count_product` AS `product_count`, 0028 `sct`.`xdg_type`, 0029 `sct`.`name_legacy`, 0030 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0031 (SELECT `project_category_id` 0032 FROM `stat_cat_tree` AS `sct2` 0033 WHERE `sct2`.`lft` < `sct`.`lft` 0034 AND `sct2`.`rgt` > `sct`.`rgt` 0035 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0036 LIMIT 1) AS `parent_id` 0037 FROM `tmp_store_cat` AS `cfc` 0038 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0039 JOIN `stat_cat_prod_count` AS `scpc` 0040 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`package_type_id` IS NULL 0041 WHERE `cfc`.`store_id` = `STORE_ID` 0042 ORDER BY `cfc`.`order`, `sct`.`lft`; 0043 END$$ 0044 DELIMITER ; 0045 0046 DELIMITER $$ 0047 CREATE 0048 DEFINER = CURRENT_USER PROCEDURE `fetchCatTreeWithPackage`(IN `STORE_ID` int(11), IN `PACKAGE_TYPE` int(11)) 0049 BEGIN 0050 DROP TABLE IF EXISTS `tmp_store_cat`; 0051 CREATE TEMPORARY TABLE `tmp_store_cat` 0052 ( 0053 INDEX `idx_cat_id` (`project_category_id`) 0054 ) 0055 ENGINE MEMORY 0056 AS 0057 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0058 FROM `config_store_category` AS `csc` 0059 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0060 WHERE `csc`.`store_id` = `STORE_ID` 0061 GROUP BY `csc`.`store_category_id` 0062 ORDER BY `csc`.`order`, `pc`.`title`; 0063 0064 SET @`NEW_ORDER` := 0; 0065 0066 UPDATE `tmp_store_cat` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0067 0068 SELECT `sct`.`lft`, 0069 `sct`.`rgt`, 0070 `sct`.`project_category_id` AS `id`, 0071 `sct`.`title`, 0072 `scpc`.`count_product` AS `product_count`, 0073 `sct`.`xdg_type`, 0074 `sct`.`name_legacy`, 0075 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0076 (SELECT `project_category_id` 0077 FROM `stat_cat_tree` AS `sct2` 0078 WHERE `sct2`.`lft` < `sct`.`lft` 0079 AND `sct2`.`rgt` > `sct`.`rgt` 0080 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0081 LIMIT 1) AS `parent_id` 0082 FROM `tmp_store_cat` AS `cfc` 0083 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0084 JOIN `stat_cat_prod_count` AS `scpc` ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND 0085 `scpc`.`package_type_id` = `PACKAGE_TYPE` 0086 WHERE `cfc`.`store_id` = `STORE_ID` 0087 ORDER BY `cfc`.`order`, `sct`.`lft`; 0088 END$$ 0089 DELIMITER ; 0090 0091 0092 DROP PROCEDURE IF EXISTS `fetchCatTreeForStore`; 0093 0094 DELIMITER $$ 0095 CREATE 0096 DEFINER = CURRENT_USER PROCEDURE `fetchCatTreeForStore`(IN `STORE_ID` int(11)) 0097 BEGIN 0098 DROP TABLE IF EXISTS `tmp_store_cat`; 0099 CREATE TEMPORARY TABLE `tmp_store_cat` 0100 ( 0101 INDEX `idx_cat_id` (`project_category_id`) 0102 ) 0103 ENGINE MEMORY 0104 AS 0105 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0106 FROM `config_store_category` AS `csc` 0107 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0108 WHERE `csc`.`store_id` = `STORE_ID` 0109 GROUP BY `csc`.`store_category_id` 0110 ORDER BY `csc`.`order`, `pc`.`title`; 0111 0112 SET @`NEW_ORDER` := 0; 0113 0114 UPDATE `tmp_store_cat` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0115 0116 SELECT `sct`.`lft`, 0117 `sct`.`rgt`, 0118 `sct`.`project_category_id` AS `id`, 0119 `sct`.`title`, 0120 `scpc`.`count_product` AS `product_count`, 0121 `sct`.`xdg_type`, 0122 `sct`.`name_legacy`, 0123 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0124 (SELECT `project_category_id` 0125 FROM `stat_cat_tree` AS `sct2` 0126 WHERE `sct2`.`lft` < `sct`.`lft` 0127 AND `sct2`.`rgt` > `sct`.`rgt` 0128 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0129 LIMIT 1) AS `parent_id` 0130 FROM `tmp_store_cat` AS `cfc` 0131 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0132 JOIN `stat_cat_prod_count` AS `scpc` 0133 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` IS NULL 0134 WHERE `cfc`.`store_id` = `STORE_ID` 0135 ORDER BY `cfc`.`order`, `sct`.`lft`; 0136 END$$ 0137 DELIMITER ; 0138