File indexing completed on 2024-04-21 09:52:01
0001 DROP PROCEDURE IF EXISTS `generate_stat_cat_prod_count`; 0002 0003 DELIMITER $$ 0004 0005 CREATE PROCEDURE `generate_stat_cat_prod_count`() 0006 BEGIN 0007 0008 DROP TABLE IF EXISTS `tmp_stat_cat_prod_count`; 0009 CREATE TABLE `tmp_stat_cat_prod_count` 0010 ( 0011 `project_category_id` int(11) NOT NULL, 0012 `tag_id` int(11) NULL, 0013 `count_product` int(11) NULL, 0014 INDEX `idx_tag` (`project_category_id`, `tag_id`) 0015 ) 0016 ENGINE MEMORY 0017 AS 0018 SELECT `sct2`.`project_category_id`, 0019 NULL AS `tag_id`, 0020 count(DISTINCT `p`.`project_id`) AS `count_product` 0021 FROM `stat_cat_tree` AS `sct1` 0022 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0023 LEFT JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0024 WHERE `p`.`amount_reports` IS NULL 0025 GROUP BY `sct2`.`project_category_id` 0026 0027 UNION 0028 0029 SELECT `sct2`.`project_category_id`, 0030 `tg`.`tag_ids` AS `tag_id`, 0031 count(DISTINCT `p`.`project_id`) AS `count_product` 0032 FROM `stat_cat_tree` AS `sct1` 0033 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0034 JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0035 JOIN (SELECT `cs`.`store_id`, GROUP_CONCAT(`ct`.`tag_id`) AS `tag_ids` 0036 FROM `config_store` `cs` 0037 JOIN `config_store_tag` `ct` ON `ct`.`store_id` = `cs`.`store_id` AND `ct`.`is_active` = 1 0038 GROUP BY `cs`.`store_id`) `tg` 0039 JOIN ( 0040 SELECT DISTINCT `project_id`, `tag_ids` 0041 FROM `stat_project_tagids` 0042 JOIN (SELECT `cs`.`store_id`, GROUP_CONCAT(`ct`.`tag_id`) AS `tag_ids` 0043 FROM `config_store` `cs` 0044 JOIN `config_store_tag` `ct` 0045 ON `ct`.`store_id` = `cs`.`store_id` AND `ct`.`is_active` = 1 0046 GROUP BY `cs`.`store_id`) `tg` 0047 WHERE `tag_id` IN (`tg`.`tag_ids`) 0048 ) AS `store_tags` ON `p`.`project_id` = `store_tags`.`project_id` AND `store_tags`.`tag_ids` = `tg`.`tag_ids` 0049 JOIN `tag_object` AS `ppt` ON 0050 ((`ppt`.`tag_parent_object_id` = `p`.`project_id` AND `ppt`.`tag_type_id` = 3) OR 0051 (`ppt`.`tag_object_id` = `p`.`project_id`)) AND `ppt`.`is_deleted` = 0 0052 JOIN `ppload`.`ppload_files` AS `files` ON `files`.`id` = `ppt`.`tag_object_id` AND `files`.`active` = 1 0053 WHERE `p`.`amount_reports` IS NULL 0054 GROUP BY `sct2`.`lft`, `tg`.`tag_ids`; 0055 0056 IF EXISTS(SELECT `table_name` 0057 FROM `INFORMATION_SCHEMA`.`TABLES` 0058 WHERE `table_schema` = DATABASE() 0059 AND `table_name` = 'stat_cat_prod_count') 0060 THEN 0061 RENAME TABLE `stat_cat_prod_count` TO `old_stat_cat_prod_count`, `tmp_stat_cat_prod_count` TO `stat_cat_prod_count`; 0062 0063 ELSE 0064 RENAME TABLE `tmp_stat_cat_prod_count` TO `stat_cat_prod_count`; 0065 0066 END IF; 0067 0068 0069 DROP TABLE IF EXISTS `old_stat_cat_prod_count`; 0070 0071 END$$ 0072 0073 DELIMITER ; 0074 0075 0076 CALL `generate_stat_cat_prod_count`; 0077 0078 0079 0080 DROP PROCEDURE IF EXISTS `generate_stat_cat_prod_count_w_spam`; 0081 0082 DELIMITER $$ 0083 0084 CREATE PROCEDURE `generate_stat_cat_prod_count_w_spam`() 0085 BEGIN 0086 0087 DROP TABLE IF EXISTS `tmp_stat_cat_prod_count_w_spam`; 0088 CREATE TABLE `tmp_stat_cat_prod_count_w_spam` 0089 ( 0090 `project_category_id` int(11) NOT NULL, 0091 `tag_id` int(11) NULL, 0092 `count_product` int(11) NULL, 0093 INDEX `idx_tag` (`project_category_id`, `tag_id`) 0094 ) 0095 ENGINE MEMORY 0096 AS 0097 SELECT `sct2`.`project_category_id`, 0098 NULL AS `tag_id`, 0099 count(DISTINCT `p`.`project_id`) AS `count_product` 0100 FROM `stat_cat_tree` AS `sct1` 0101 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0102 LEFT JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0103 GROUP BY `sct2`.`project_category_id` 0104 0105 UNION 0106 0107 SELECT `sct2`.`project_category_id`, 0108 `tg`.`tag_ids` AS `tag_id`, 0109 count(DISTINCT `p`.`project_id`) AS `count_product` 0110 FROM `stat_cat_tree` AS `sct1` 0111 JOIN `stat_cat_tree` AS `sct2` ON `sct1`.`lft` BETWEEN `sct2`.`lft` AND `sct2`.`rgt` 0112 JOIN `stat_projects` AS `p` ON `p`.`project_category_id` = `sct1`.`project_category_id` 0113 JOIN (SELECT `cs`.`store_id`, GROUP_CONCAT(`ct`.`tag_id`) AS `tag_ids` 0114 FROM `config_store` `cs` 0115 JOIN `config_store_tag` `ct` ON `ct`.`store_id` = `cs`.`store_id` AND `ct`.`is_active` = 1 0116 GROUP BY `cs`.`store_id`) `tg` 0117 JOIN ( 0118 SELECT DISTINCT `project_id`, `tag_ids` 0119 FROM `stat_project_tagids` 0120 JOIN (SELECT `cs`.`store_id`, GROUP_CONCAT(`ct`.`tag_id`) AS `tag_ids` 0121 FROM `config_store` `cs` 0122 JOIN `config_store_tag` `ct` 0123 ON `ct`.`store_id` = `cs`.`store_id` AND `ct`.`is_active` = 1 0124 GROUP BY `cs`.`store_id`) `tg` 0125 WHERE `tag_id` IN (`tg`.`tag_ids`) 0126 ) AS `store_tags` ON `p`.`project_id` = `store_tags`.`project_id` AND `store_tags`.`tag_ids` = `tg`.`tag_ids` 0127 JOIN `tag_object` AS `ppt` ON 0128 ((`ppt`.`tag_parent_object_id` = `p`.`project_id` AND `ppt`.`tag_type_id` = 3) OR 0129 (`ppt`.`tag_object_id` = `p`.`project_id`)) AND `ppt`.`is_deleted` = 0 0130 JOIN `ppload`.`ppload_files` AS `files` ON `files`.`id` = `ppt`.`tag_object_id` AND `files`.`active` = 1 0131 0132 GROUP BY `sct2`.`lft`, `tg`.`tag_ids`; 0133 0134 IF EXISTS(SELECT `table_name` 0135 FROM `INFORMATION_SCHEMA`.`TABLES` 0136 WHERE `table_schema` = DATABASE() 0137 AND `table_name` = 'stat_cat_prod_count_w_spam') 0138 THEN 0139 RENAME TABLE `stat_cat_prod_count_w_spam` TO `old_stat_cat_prod_count_w_spam`, `tmp_stat_cat_prod_count_w_spam` TO `stat_cat_prod_count_w_spam`; 0140 0141 ELSE 0142 RENAME TABLE `tmp_stat_cat_prod_count_w_spam` TO `stat_cat_prod_count_w_spam`; 0143 0144 END IF; 0145 0146 0147 DROP TABLE IF EXISTS `old_stat_cat_prod_count_w_spam`; 0148 0149 END$$ 0150 0151 DELIMITER ; 0152 0153 0154 DROP PROCEDURE IF EXISTS `fetchCatTreeWithTags`; 0155 0156 DELIMITER $$ 0157 0158 CREATE PROCEDURE `fetchCatTreeWithTags`(IN `STORE_ID` int(11), 0159 IN `TAGS` VARCHAR(255)) 0160 BEGIN 0161 DROP TABLE IF EXISTS `tmp_store_cat_tags`; 0162 CREATE TEMPORARY TABLE `tmp_store_cat_tags` 0163 ( 0164 INDEX `idx_cat_id` (`project_category_id`) 0165 ) 0166 ENGINE MEMORY 0167 AS 0168 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0169 FROM `config_store_category` AS `csc` 0170 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0171 WHERE `csc`.`store_id` = `STORE_ID` 0172 GROUP BY `csc`.`store_category_id` 0173 ORDER BY `csc`.`order`, `pc`.`title`; 0174 0175 SET @`NEW_ORDER` := 0; 0176 0177 UPDATE `tmp_store_cat_tags` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0178 0179 SELECT `sct`.`lft`, 0180 `sct`.`rgt`, 0181 `sct`.`project_category_id` AS `id`, 0182 `sct`.`title`, 0183 `scpc`.`count_product` AS `product_count`, 0184 `sct`.`xdg_type`, 0185 `sct`.`name_legacy`, 0186 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0187 (SELECT `project_category_id` 0188 FROM `stat_cat_tree` AS `sct2` 0189 WHERE `sct2`.`lft` < `sct`.`lft` 0190 AND `sct2`.`rgt` > `sct`.`rgt` 0191 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0192 LIMIT 1) AS `parent_id` 0193 FROM `tmp_store_cat_tags` AS `cfc` 0194 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0195 JOIN `stat_cat_prod_count` AS `scpc` 0196 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND FIND_IN_SET(`scpc`.`tag_id`, `TAGS`) 0197 WHERE `cfc`.`store_id` = `STORE_ID` 0198 ORDER BY `cfc`.`order`, `sct`.`lft`; 0199 END$$ 0200 DELIMITER ; 0201 0202 0203 DROP PROCEDURE `generate_stat_project`; 0204 0205 DELIMITER $$ 0206 0207 CREATE PROCEDURE `generate_stat_project`() 0208 BEGIN 0209 DROP TABLE IF EXISTS `tmp_reported_projects`; 0210 CREATE TEMPORARY TABLE `tmp_reported_projects` 0211 ( 0212 PRIMARY KEY `primary` (`project_id`) 0213 ) 0214 AS 0215 SELECT `reports_project`.`project_id` AS `project_id`, 0216 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0217 MAX(`reports_project`.`created_at`) AS `latest_report` 0218 FROM `reports_project` 0219 WHERE (`reports_project`.`is_deleted` = 0 AND `reports_project`.`report_type` = 0) 0220 GROUP BY `reports_project`.`project_id`; 0221 0222 DROP TABLE IF EXISTS `tmp_project_package_types`; 0223 CREATE TEMPORARY TABLE `tmp_project_package_types` 0224 ( 0225 PRIMARY KEY `primary` (`project_id`) 0226 ) 0227 ENGINE MyISAM 0228 AS 0229 SELECT `tag_object`.`tag_parent_object_id` AS `project_id`, 0230 GROUP_CONCAT(DISTINCT `tag_object`.`tag_id`) AS `package_type_id_list`, 0231 GROUP_CONCAT(DISTINCT `tag`.`tag_fullname`) AS `package_name_list` 0232 FROM `tag_object` 0233 JOIN 0234 `tag` ON `tag_object`.`tag_id` = `tag`.`tag_id` 0235 JOIN 0236 `ppload`.`ppload_files` `files` ON `files`.`id` = `tag_object`.`tag_object_id` 0237 WHERE `tag_object`.`tag_group_id` = 8 0238 AND `tag_object`.`is_deleted` = 0 0239 AND `files`.`active` = 1 0240 GROUP BY `tag_object`.`tag_parent_object_id`; 0241 0242 DROP TABLE IF EXISTS `tmp_project_tags`; 0243 CREATE TEMPORARY TABLE `tmp_project_tags` 0244 ( 0245 PRIMARY KEY `primary` (`tag_project_id`) 0246 ) 0247 ENGINE MyISAM 0248 AS 0249 SELECT GROUP_CONCAT(`tag_name`) AS `tag_names`, `tag_project_id` 0250 FROM ( 0251 SELECT DISTINCT `tag`.`tag_name`, `tgo`.`tag_object_id` AS `tag_project_id` 0252 FROM `tag_object` AS `tgo` 0253 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0254 WHERE `tag_type_id` = 1 #project 0255 AND `tgo`.`is_deleted` = 0 0256 UNION ALL 0257 SELECT DISTINCT `tag`.`tag_name`, `tgo`.`tag_parent_object_id` AS `tag_project_id` 0258 FROM `tag_object` AS `tgo` 0259 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0260 JOIN `ppload`.`ppload_files` `files` ON `files`.`id` = `tgo`.`tag_object_id` 0261 WHERE `tag_type_id` = 3 #file 0262 AND `files`.`active` = 1 0263 AND `tgo`.`is_deleted` = 0 0264 ) `A` 0265 GROUP BY `tag_project_id` 0266 ORDER BY `tag_project_id`; 0267 0268 0269 DROP TABLE IF EXISTS `tmp_project_tagids`; 0270 CREATE TEMPORARY TABLE `tmp_project_tagids` 0271 ( 0272 PRIMARY KEY `primary` (`tag_project_id`) 0273 ) 0274 ENGINE MyISAM 0275 AS 0276 SELECT GROUP_CONCAT(`tag_id`) AS `tag_ids`, `tag_project_id` 0277 FROM ( 0278 SELECT DISTINCT `tag`.`tag_id`, `tgo`.`tag_object_id` AS `tag_project_id` 0279 FROM `tag_object` AS `tgo` 0280 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0281 WHERE `tag_type_id` = 1 #project 0282 AND `tgo`.`is_deleted` = 0 0283 UNION ALL 0284 SELECT DISTINCT `tag`.`tag_id`, `tgo`.`tag_parent_object_id` AS `tag_project_id` 0285 FROM `tag_object` AS `tgo` 0286 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0287 JOIN `ppload`.`ppload_files` `files` ON `files`.`id` = `tgo`.`tag_object_id` 0288 WHERE `tag_type_id` = 3 #file 0289 AND `files`.`active` = 1 0290 AND `tgo`.`is_deleted` = 0 0291 ) `A` 0292 GROUP BY `tag_project_id` 0293 ORDER BY `tag_project_id`; 0294 0295 0296 DROP TABLE IF EXISTS `tmp_stat_projects`; 0297 CREATE TABLE `tmp_stat_projects` 0298 ( 0299 PRIMARY KEY `primary` (`project_id`), 0300 INDEX `idx_cat` (`project_category_id`), 0301 INDEX `idx_member` (`member_id`), 0302 INDEX `idx_source_url` (`source_url`(50)) 0303 ) 0304 ENGINE MyISAM 0305 AS 0306 SELECT `project`.`project_id` AS `project_id`, 0307 `project`.`member_id` AS `member_id`, 0308 `project`.`content_type` AS `content_type`, 0309 `project`.`project_category_id` AS `project_category_id`, 0310 `project`.`hive_category_id` AS `hive_category_id`, 0311 `project`.`status` AS `status`, 0312 `project`.`uuid` AS `uuid`, 0313 `project`.`pid` AS `pid`, 0314 `project`.`type_id` AS `type_id`, 0315 `project`.`title` AS `title`, 0316 `project`.`description` AS `description`, 0317 `project`.`version` AS `version`, 0318 `project`.`project_license_id` AS `project_license_id`, 0319 `project`.`image_big` AS `image_big`, 0320 `project`.`image_small` AS `image_small`, 0321 `project`.`start_date` AS `start_date`, 0322 `project`.`content_url` AS `content_url`, 0323 `project`.`created_at` AS `created_at`, 0324 `project`.`changed_at` AS `changed_at`, 0325 `project`.`deleted_at` AS `deleted_at`, 0326 `project`.`creator_id` AS `creator_id`, 0327 `project`.`facebook_code` AS `facebook_code`, 0328 `project`.`source_url` AS `source_url`, 0329 `project`.`twitter_code` AS `twitter_code`, 0330 `project`.`google_code` AS `google_code`, 0331 `project`.`link_1` AS `link_1`, 0332 `project`.`embed_code` AS `embed_code`, 0333 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0334 `project`.`validated` AS `validated`, 0335 `project`.`validated_at` AS `validated_at`, 0336 `project`.`featured` AS `featured`, 0337 `project`.`ghns_excluded` AS `ghns_excluded`, 0338 `project`.`amount` AS `amount`, 0339 `project`.`amount_period` AS `amount_period`, 0340 `project`.`claimable` AS `claimable`, 0341 `project`.`claimed_by_member` AS `claimed_by_member`, 0342 `project`.`count_likes` AS `count_likes`, 0343 `project`.`count_dislikes` AS `count_dislikes`, 0344 `project`.`count_comments` AS `count_comments`, 0345 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0346 `project`.`source_id` AS `source_id`, 0347 `project`.`source_pk` AS `source_pk`, 0348 `project`.`source_type` AS `source_type`, 0349 `project`.`validated` AS `project_validated`, 0350 `project`.`uuid` AS `project_uuid`, 0351 `project`.`status` AS `project_status`, 0352 `project`.`created_at` AS `project_created_at`, 0353 `project`.`changed_at` AS `project_changed_at`, 0354 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0355 `member`.`type` AS `member_type`, 0356 `member`.`member_id` AS `project_member_id`, 0357 `member`.`username` AS `username`, 0358 `member`.`profile_image_url` AS `profile_image_url`, 0359 `member`.`city` AS `city`, 0360 `member`.`country` AS `country`, 0361 `member`.`created_at` AS `member_created_at`, 0362 `member`.`paypal_mail` AS `paypal_mail`, 0363 `project_category`.`title` AS `cat_title`, 0364 `project_category`.`xdg_type` AS `cat_xdg_type`, 0365 `project_category`.`name_legacy` AS `cat_name_legacy`, 0366 `project_category`.`show_description` AS `cat_show_description`, 0367 `stat_plings`.`amount_received` AS `amount_received`, 0368 `stat_plings`.`count_plings` AS `count_plings`, 0369 `stat_plings`.`count_plingers` AS `count_plingers`, 0370 `stat_plings`.`latest_pling` AS `latest_pling`, 0371 `trp`.`amount_reports` AS `amount_reports`, 0372 `tppt`.`package_type_id_list` AS `package_types`, 0373 `tppt`.`package_name_list` AS `package_names`, 0374 `t`.`tag_names` AS `tags`, 0375 `t2`.`tag_ids` AS `tag_ids`, 0376 `sdqy`.`amount` AS `count_downloads_quarter`, 0377 `project_license`.`title` AS `project_license_title` 0378 FROM `project` 0379 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0380 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0381 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0382 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0383 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0384 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0385 LEFT JOIN `tmp_project_tagids` AS `t2` ON `t2`.`tag_project_id` = `project`.`project_id` 0386 LEFT JOIN `stat_downloads_quarter_year` AS `sdqy` ON `sdqy`.`project_id` = `project`.`project_id` 0387 LEFT JOIN `project_license` ON `project_license`.`project_license_id` = `project`.`project_license_id` 0388 WHERE `member`.`is_deleted` = 0 0389 AND `member`.`is_active` = 1 0390 AND `project`.`type_id` = 1 0391 AND `project`.`status` = 100 0392 AND `project_category`.`is_active` = 1; 0393 0394 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0395 0396 DROP TABLE IF EXISTS `old_stat_projects`; 0397 END$$ 0398 0399 DELIMITER ; 0400 0401