File indexing completed on 2024-03-24 06:03:44
0001 DROP PROCEDURE IF EXISTS `generate_stat_store_prod_count`; 0002 0003 DELIMITER $$ 0004 CREATE PROCEDURE `generate_stat_store_prod_count`() 0005 BEGIN 0006 0007 DECLARE `v_finished` INTEGER DEFAULT 0; 0008 DECLARE `v_store_id` varchar(255) DEFAULT ""; 0009 DECLARE `v_store_tag_ids` varchar(255) DEFAULT ""; 0010 0011 DECLARE `idx`,`prev_idx` int; 0012 DECLARE `v_id` varchar(10); 0013 0014 -- declare cursor for employee email 0015 DECLARE `store_cursor` CURSOR FOR 0016 SELECT * FROM `tmp_stat_store_tagids`; 0017 0018 -- declare NOT FOUND handler 0019 DECLARE CONTINUE HANDLER 0020 FOR NOT FOUND SET `v_finished` = 1; 0021 0022 0023 DROP TABLE IF EXISTS `tmp_stat_store_tagids`; 0024 CREATE TEMPORARY TABLE `tmp_stat_store_tagids` 0025 AS 0026 SELECT `cs`.`store_id`, 0027 GROUP_CONCAT(`ct`.`tag_id` 0028 ORDER BY `ct`.`tag_id`) AS `tag_ids` 0029 FROM `config_store` `cs` 0030 JOIN 0031 `config_store_tag` `ct` ON `ct`.`store_id` = `cs`.`store_id` 0032 AND `ct`.`is_active` = 1 0033 #WHERE `cs`.`store_id` = 7 0034 GROUP BY `cs`.`store_id`; 0035 0036 0037 DROP TABLE IF EXISTS `tmp_stat_store_prod_count`; 0038 CREATE TABLE `tmp_stat_store_prod_count` 0039 ( 0040 `project_category_id` INT(11) NOT NULL, 0041 `tag_id` VARCHAR(255) NULL, 0042 `count_product` INT(11) NULL, 0043 `stores` VARCHAR(255) NULL, 0044 INDEX `idx_tag` (`project_category_id`, `tag_id`) 0045 ) 0046 ENGINE MyISAM 0047 AS 0048 SELECT `sct2`.`project_category_id`, 0049 NULL AS `tag_id`, 0050 count(DISTINCT `p`.`project_id`) AS `count_product`, 0051 NULL AS `stores` 0052 FROM `stat_cat_tree` AS `sct1` 0053 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0054 LEFT JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0055 WHERE `p`.`amount_reports` IS NULL 0056 GROUP BY `sct2`.`project_category_id`; 0057 0058 0059 OPEN `store_cursor`; 0060 0061 `get_store`: 0062 LOOP 0063 0064 FETCH `store_cursor` INTO `v_store_id`, `v_store_tag_ids`; 0065 0066 IF `v_finished` = 1 THEN 0067 LEAVE `get_store`; 0068 END IF; 0069 0070 -- build email list 0071 0072 0073 SET @`sql` = ' 0074 INSERT INTO tmp_stat_store_prod_count 0075 SELECT 0076 sct2.project_category_id, 0077 tg.tag_ids as tag_id, 0078 count(distinct p.project_id) as count_product, 0079 tg.store_id 0080 FROM stat_cat_tree as sct1 0081 JOIN stat_cat_tree as sct2 ON sct1.lft between sct2.lft AND sct2.rgt 0082 JOIN stat_projects as p ON p.project_category_id = sct1.project_category_id 0083 JOIN tmp_stat_store_tagids tg 0084 0085 WHERE p.amount_reports is null 0086 '; 0087 SET @`sql` = CONCAT(@`sql`, ' AND tg.store_id = ', `v_store_id`, ' '); 0088 SET @`sql` = CONCAT(@`sql`, ' AND (1=1 '); 0089 0090 SET `idx` := locate(',', `v_store_tag_ids`, 1); 0091 0092 IF LENGTH(`v_store_tag_ids`) > 0 THEN 0093 0094 IF `idx` > 0 THEN 0095 SET `prev_idx` := 1; 0096 WHILE `idx` > 0 DO 0097 SET `v_id` := substr(`v_store_tag_ids`, `prev_idx`, `idx` - `prev_idx`); 0098 SET @`sql` = CONCAT(@`sql`, ' AND FIND_IN_SET(', `v_id`, ', p.tag_ids) '); 0099 SET `prev_idx` := `idx` + 1; 0100 SET `idx` := locate(',', `v_store_tag_ids`, `prev_idx`); 0101 0102 IF `idx` = 0 THEN 0103 SET `v_id` := substr(`v_store_tag_ids`, `prev_idx`); 0104 SET @`sql` = CONCAT(@`sql`, ' AND FIND_IN_SET(', `v_id`, ', p.tag_ids) '); 0105 END IF; 0106 END WHILE; 0107 ELSE 0108 0109 SET @`sql` = CONCAT(@`sql`, ' AND FIND_IN_SET(', `v_store_tag_ids`, ', p.tag_ids) '); 0110 0111 END IF; 0112 END IF; 0113 0114 SET @`sql` = CONCAT(@`sql`, ') '); 0115 SET @`sql` = CONCAT(@`sql`, 'GROUP BY sct2.lft, tg.tag_ids,tg.store_id'); 0116 0117 #select @sql; 0118 0119 PREPARE `stmt` FROM @`sql`; 0120 EXECUTE `stmt`; 0121 DEALLOCATE PREPARE `stmt`; 0122 0123 END LOOP `get_store`; 0124 0125 CLOSE `store_cursor`; 0126 0127 0128 IF EXISTS(SELECT `table_name` 0129 FROM `INFORMATION_SCHEMA`.`TABLES` 0130 WHERE `table_schema` = DATABASE() 0131 AND `table_name` = 'stat_store_prod_count') 0132 THEN 0133 RENAME TABLE 0134 `stat_store_prod_count` TO `old_stat_store_prod_count`, 0135 `tmp_stat_store_prod_count` TO `stat_store_prod_count`; 0136 0137 ELSE 0138 RENAME TABLE 0139 `tmp_stat_store_prod_count` TO `stat_store_prod_count`; 0140 0141 END IF; 0142 0143 0144 DROP TABLE IF EXISTS `old_stat_store_prod_count`; 0145 0146 END$$ 0147 0148 0149 0150 CREATE EVENT `e_generate_stat_store_prod_count` 0151 ON SCHEDULE 0152 EVERY 30 MINUTE STARTS '2019-02-05 15:01:03' 0153 ON COMPLETION NOT PRESERVE 0154 ENABLE 0155 COMMENT '' 0156 DO 0157 BEGIN 0158 CALL generate_stat_store_prod_count(); 0159 END$$ 0160 0161 0162 0163 CREATE PROCEDURE `fetchCatTreeForStore`( 0164 IN `STORE_ID` int(11) 0165 ) 0166 BEGIN 0167 DROP TABLE IF EXISTS `tmp_store_cat`; 0168 CREATE TEMPORARY TABLE `tmp_store_cat` 0169 ( 0170 INDEX `idx_cat_id` (`project_category_id`) 0171 ) 0172 ENGINE MEMORY 0173 AS 0174 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0175 FROM `config_store_category` AS `csc` 0176 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0177 WHERE `csc`.`store_id` = `STORE_ID` 0178 GROUP BY `csc`.`store_category_id` 0179 ORDER BY `csc`.`order`, `pc`.`title`; 0180 0181 SET @`NEW_ORDER` := 0; 0182 0183 UPDATE `tmp_store_cat` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0184 0185 SELECT `sct`.`lft`, 0186 `sct`.`rgt`, 0187 `sct`.`project_category_id` AS `id`, 0188 `sct`.`title`, 0189 `scpc`.`count_product` AS `product_count`, 0190 `sct`.`xdg_type`, 0191 `sct`.`name_legacy`, 0192 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0193 (SELECT `project_category_id` 0194 FROM `stat_cat_tree` AS `sct2` 0195 WHERE `sct2`.`lft` < `sct`.`lft` 0196 AND `sct2`.`rgt` > `sct`.`rgt` 0197 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0198 LIMIT 1) AS `parent_id` 0199 FROM `tmp_store_cat` AS `cfc` 0200 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0201 #JOIN `stat_cat_prod_count` AS `scpc` ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` is null 0202 JOIN `stat_store_prod_count` AS `scpc` 0203 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` IS NULL 0204 WHERE `cfc`.`store_id` = `STORE_ID` 0205 ORDER BY `cfc`.`order`, `sct`.`lft`; 0206 END$$ 0207 0208 0209 0210 CREATE PROCEDURE `fetchCatTreeWithTagsForStore`(IN `STORE_ID` INT(11), 0211 IN `TAGS` VARCHAR(255)) 0212 BEGIN 0213 DROP TABLE IF EXISTS `tmp_store_cat_tags`; 0214 CREATE TEMPORARY TABLE `tmp_store_cat_tags` 0215 ( 0216 INDEX `idx_cat_id` (`project_category_id`) 0217 ) 0218 ENGINE MEMORY 0219 AS 0220 SELECT `csc`.`store_id`, 0221 `csc`.`project_category_id`, 0222 `csc`.`order`, 0223 `pc`.`title`, 0224 `pc`.`lft`, 0225 `pc`.`rgt` 0226 FROM `config_store_category` AS `csc` 0227 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0228 WHERE `csc`.`store_id` = `STORE_ID` 0229 GROUP BY `csc`.`store_category_id` 0230 ORDER BY `csc`.`order`, `pc`.`title`; 0231 0232 SET @`NEW_ORDER` := 0; 0233 0234 UPDATE `tmp_store_cat_tags` 0235 SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0236 0237 SELECT `sct`.`lft`, 0238 `sct`.`rgt`, 0239 `sct`.`project_category_id` AS `id`, 0240 `sct`.`title`, 0241 `scpc`.`count_product` AS `product_count`, 0242 `sct`.`xdg_type`, 0243 `sct`.`name_legacy`, 0244 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0245 (SELECT `project_category_id` 0246 FROM `stat_cat_tree` AS `sct2` 0247 WHERE `sct2`.`lft` < `sct`.`lft` 0248 AND `sct2`.`rgt` > `sct`.`rgt` 0249 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0250 LIMIT 1) AS `parent_id` 0251 FROM `tmp_store_cat_tags` AS `cfc` 0252 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0253 #LEFT JOIN `stat_cat_prod_count` AS `scpc` ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` = TAGS 0254 JOIN `stat_store_prod_count` AS `scpc` 0255 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`stores` = `STORE_ID` 0256 WHERE `cfc`.`store_id` = `STORE_ID` 0257 ORDER BY `cfc`.`order`, `sct`.`lft`; 0258 END$$