File indexing completed on 2024-04-14 05:53:06
0001 0002 DROP PROCEDURE IF EXISTS `generate_stat_project`; 0003 0004 DELIMITER $$ 0005 USE `pling`$$ 0006 CREATE PROCEDURE `generate_stat_project`() 0007 BEGIN 0008 DROP TABLE IF EXISTS `tmp_reported_projects`; 0009 CREATE TEMPORARY TABLE `tmp_reported_projects` 0010 ( 0011 PRIMARY KEY `primary` (`project_id`) 0012 ) 0013 AS 0014 SELECT `reports_project`.`project_id` AS `project_id`, 0015 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0016 MAX(`reports_project`.`created_at`) AS `latest_report` 0017 FROM `reports_project` 0018 WHERE (`reports_project`.`is_deleted` = 0) 0019 GROUP BY `reports_project`.`project_id`; 0020 0021 DROP TABLE IF EXISTS `tmp_project_package_types`; 0022 CREATE TEMPORARY TABLE `tmp_project_package_types` 0023 ( 0024 PRIMARY KEY `primary` (`project_id`) 0025 ) 0026 ENGINE MyISAM 0027 AS 0028 SELECT `project_id`, 0029 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0030 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0031 FROM `project_package_type` 0032 JOIN 0033 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0034 WHERE `package_types`.`is_active` = 1 0035 GROUP BY `project_id`; 0036 0037 0038 DROP TABLE IF EXISTS `tmp_stat_projects`; 0039 CREATE TABLE `tmp_stat_projects` 0040 ( 0041 PRIMARY KEY `primary` (`project_id`), 0042 INDEX `idx_cat` (`project_category_id`) 0043 ) 0044 ENGINE MyISAM 0045 AS 0046 SELECT `project`.`project_id` AS `project_id`, 0047 `project`.`member_id` AS `member_id`, 0048 `project`.`content_type` AS `content_type`, 0049 `project`.`project_category_id` AS `project_category_id`, 0050 `project`.`hive_category_id` AS `hive_category_id`, 0051 `project`.`status` AS `status`, 0052 `project`.`uuid` AS `uuid`, 0053 `project`.`pid` AS `pid`, 0054 `project`.`type_id` AS `type_id`, 0055 `project`.`title` AS `title`, 0056 `project`.`description` AS `description`, 0057 `project`.`version` AS `version`, 0058 `project`.`image_big` AS `image_big`, 0059 `project`.`image_small` AS `image_small`, 0060 `project`.`start_date` AS `start_date`, 0061 `project`.`content_url` AS `content_url`, 0062 `project`.`created_at` AS `created_at`, 0063 `project`.`changed_at` AS `changed_at`, 0064 `project`.`deleted_at` AS `deleted_at`, 0065 `project`.`creator_id` AS `creator_id`, 0066 `project`.`facebook_code` AS `facebook_code`, 0067 `project`.`github_code` AS `github_code`, 0068 `project`.`twitter_code` AS `twitter_code`, 0069 `project`.`google_code` AS `google_code`, 0070 `project`.`link_1` AS `link_1`, 0071 `project`.`embed_code` AS `embed_code`, 0072 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0073 `project`.`validated` AS `validated`, 0074 `project`.`validated_at` AS `validated_at`, 0075 `project`.`featured` AS `featured`, 0076 `project`.`approved` AS `approved`, 0077 `project`.`amount` AS `amount`, 0078 `project`.`amount_period` AS `amount_period`, 0079 `project`.`claimable` AS `claimable`, 0080 `project`.`claimed_by_member` AS `claimed_by_member`, 0081 `project`.`count_likes` AS `count_likes`, 0082 `project`.`count_dislikes` AS `count_dislikes`, 0083 `project`.`count_comments` AS `count_comments`, 0084 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0085 `project`.`source_id` AS `source_id`, 0086 `project`.`source_pk` AS `source_pk`, 0087 `project`.`source_type` AS `source_type`, 0088 `project`.`validated` AS `project_validated`, 0089 `project`.`uuid` AS `project_uuid`, 0090 `project`.`status` AS `project_status`, 0091 `project`.`created_at` AS `project_created_at`, 0092 `project`.`changed_at` AS `project_changed_at`, 0093 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0094 `member`.`type` AS `member_type`, 0095 `member`.`member_id` AS `project_member_id`, 0096 `member`.`username` AS `username`, 0097 `member`.`profile_image_url` AS `profile_image_url`, 0098 `member`.`city` AS `city`, 0099 `member`.`country` AS `country`, 0100 `member`.`created_at` AS `member_created_at`, 0101 `member`.`paypal_mail` AS `paypal_mail`, 0102 `project_category`.`title` AS `cat_title`, 0103 `project_category`.`xdg_type` AS `cat_xdg_type`, 0104 `project_category`.`name_legacy` AS `cat_name_legacy`, 0105 `stat_plings`.`amount_received` AS `amount_received`, 0106 `stat_plings`.`count_plings` AS `count_plings`, 0107 `stat_plings`.`count_plingers` AS `count_plingers`, 0108 `stat_plings`.`latest_pling` AS `latest_pling`, 0109 `trp`.`amount_reports` AS `amount_reports`, 0110 `tppt`.`package_type_id_list` AS `package_types`, 0111 `tppt`.`package_name_list` AS `package_names` 0112 FROM `project` 0113 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0114 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0115 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0116 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0117 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0118 WHERE `member`.`is_deleted` = 0 0119 AND `member`.`is_active` = 1 0120 AND `project`.`type_id` = 1 0121 AND `project`.`status` = 100 0122 AND `project_category`.`is_active` = 1; 0123 0124 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0125 0126 DROP TABLE IF EXISTS `old_stat_projects`; 0127 END$$ 0128 0129 DELIMITER ; 0130 0131 0132 DROP EVENT IF EXISTS `e_generate_stat_projects`; 0133 CREATE 0134 DEFINER = CURRENT_USER 0135 EVENT IF NOT EXISTS `e_generate_stat_projects` 0136 ON SCHEDULE 0137 EVERY 5 MINUTE STARTS DATE_FORMAT(NOW(), '%Y-%m-%d 05:00:00') 0138 ON COMPLETION PRESERVE 0139 -- DISABLE ON SLAVE 0140 COMMENT 'Regenerates stat_projects table' 0141 DO 0142 CALL `pling`.generate_stat_project();