File indexing completed on 2024-04-14 05:53:07
0001 DELIMITER $$ 0002 drop PROCEDURE generate_stat_project; 0003 CREATE PROCEDURE `generate_stat_project`() 0004 BEGIN 0005 DROP TABLE IF EXISTS tmp_reported_projects; 0006 CREATE TEMPORARY TABLE tmp_reported_projects 0007 (PRIMARY KEY `primary` (project_id) ) 0008 AS 0009 SELECT 0010 `reports_project`.`project_id` AS `project_id`, 0011 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0012 MAX(`reports_project`.`created_at`) AS `latest_report` 0013 FROM 0014 `reports_project` 0015 WHERE 0016 (`reports_project`.`is_deleted` = 0 AND `reports_project`.`report_type` = 0) 0017 GROUP BY `reports_project`.`project_id` 0018 ; 0019 0020 DROP TABLE IF EXISTS tmp_project_package_types; 0021 CREATE TEMPORARY TABLE tmp_project_package_types 0022 (PRIMARY KEY `primary` (project_id)) 0023 ENGINE MyISAM 0024 AS 0025 SELECT 0026 tag_object.tag_parent_object_id as project_id, 0027 GROUP_CONCAT(DISTINCT tag_object.tag_id) AS package_type_id_list, 0028 GROUP_CONCAT(DISTINCT tag.tag_fullname) AS `package_name_list` 0029 FROM 0030 tag_object 0031 JOIN 0032 tag ON tag_object.tag_id = tag.tag_id 0033 JOIN 0034 ppload.ppload_files files ON files.id = tag_object.tag_object_id 0035 WHERE 0036 tag_object.tag_group_id = 8 0037 AND tag_object.is_deleted = 0 0038 AND files.active = 1 0039 GROUP BY tag_object.tag_parent_object_id 0040 ; 0041 0042 DROP TABLE IF EXISTS tmp_project_tags; 0043 CREATE TEMPORARY TABLE tmp_project_tags 0044 (PRIMARY KEY `primary` (tag_project_id)) 0045 ENGINE MyISAM 0046 AS 0047 SELECT 0048 GROUP_CONCAT(tag_name) AS tag_names, 0049 GROUP_CONCAT(tag_id) AS tag_ids, 0050 tag_project_id 0051 FROM ( 0052 select 0053 distinct tag.tag_name, 0054 tag.tag_id, 0055 tgo.tag_object_id AS tag_project_id 0056 FROM tag_object AS tgo 0057 JOIN tag ON tag.tag_id = tgo.tag_id 0058 WHERE tag_type_id = 1 #project 0059 AND tgo.is_deleted = 0 0060 UNION ALL 0061 select 0062 distinct tag.tag_name, 0063 tag.tag_ID, 0064 tgo.tag_parent_object_id AS tag_project_id 0065 FROM tag_object AS tgo 0066 JOIN tag ON tag.tag_id = tgo.tag_id 0067 JOIN ppload.ppload_files files ON files.id = tgo.tag_object_id 0068 WHERE tag_type_id = 3 #file 0069 AND files.active = 1 0070 AND tgo.is_deleted = 0 0071 ) A 0072 GROUP BY tag_project_id 0073 ORDER BY tag_project_id; 0074 0075 DROP TABLE IF EXISTS tmp_project_plings; 0076 CREATE TEMPORARY TABLE tmp_project_plings 0077 (PRIMARY KEY `primary` (project_id)) 0078 ENGINE MyISAM 0079 AS 0080 select project_id,count(1) as count_plings 0081 from project_plings 0082 where is_active = 1 and is_deleted=0 0083 group by project_id; 0084 0085 DROP TABLE IF EXISTS tmp_stat_projects; 0086 CREATE TABLE tmp_stat_projects 0087 (PRIMARY KEY `primary` (`project_id`), INDEX `idx_ppload` (`ppload_collection_id`), INDEX `idx_cat` (`project_category_id`),INDEX `idx_member` (`member_id`),INDEX `idx_source_url` (`source_url`(50))) 0088 ENGINE MyISAM 0089 AS 0090 SELECT 0091 `project`.`project_id` AS `project_id`, 0092 `project`.`member_id` AS `member_id`, 0093 `project`.`content_type` AS `content_type`, 0094 `project`.`project_category_id` AS `project_category_id`, 0095 `project`.`hive_category_id` AS `hive_category_id`, 0096 `project`.`status` AS `status`, 0097 `project`.`uuid` AS `uuid`, 0098 `project`.`pid` AS `pid`, 0099 `project`.`type_id` AS `type_id`, 0100 `project`.`title` AS `title`, 0101 `project`.`description` AS `description`, 0102 `project`.`version` AS `version`, 0103 `project`.`project_license_id` AS `project_license_id`, 0104 `project`.`image_big` AS `image_big`, 0105 `project`.`image_small` AS `image_small`, 0106 `project`.`start_date` AS `start_date`, 0107 `project`.`content_url` AS `content_url`, 0108 `project`.`created_at` AS `created_at`, 0109 `project`.`changed_at` AS `changed_at`, 0110 `project`.`major_updated_at` AS `major_updated_at`, 0111 `project`.`deleted_at` AS `deleted_at`, 0112 `project`.`creator_id` AS `creator_id`, 0113 `project`.`facebook_code` AS `facebook_code`, 0114 `project`.`source_url` AS `source_url`, 0115 `project`.`twitter_code` AS `twitter_code`, 0116 `project`.`google_code` AS `google_code`, 0117 `project`.`link_1` AS `link_1`, 0118 `project`.`embed_code` AS `embed_code`, 0119 CAST(`project`.`ppload_collection_id` AS UNSIGNED) AS `ppload_collection_id`, 0120 `project`.`validated` AS `validated`, 0121 `project`.`validated_at` AS `validated_at`, 0122 `project`.`featured` AS `featured`, 0123 `project`.`ghns_excluded` AS `ghns_excluded`, 0124 `project`.`amount` AS `amount`, 0125 `project`.`amount_period` AS `amount_period`, 0126 `project`.`claimable` AS `claimable`, 0127 `project`.`claimed_by_member` AS `claimed_by_member`, 0128 IFNULL(`stat_rating_project`.`likes`, 0) AS `count_likes`, 0129 IFNULL(`stat_rating_project`.`dislikes`, 0) AS `count_dislikes`, 0130 `project`.`count_comments` AS `count_comments`, 0131 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0132 `project`.`source_id` AS `source_id`, 0133 `project`.`source_pk` AS `source_pk`, 0134 `project`.`source_type` AS `source_type`, 0135 `project`.`validated` AS `project_validated`, 0136 `project`.`uuid` AS `project_uuid`, 0137 `project`.`status` AS `project_status`, 0138 `project`.`created_at` AS `project_created_at`, 0139 `project`.`changed_at` AS `project_changed_at`, 0140 IFNULL(`stat_rating_project`.`score`, 50) AS `laplace_score_old`, 0141 IFNULL(`stat_rating_project`.`score_with_pling`, 500) AS `laplace_score`, 0142 IFNULL(`stat_rating_project`.`score_test`, 500) AS `laplace_score_test`, 0143 `member`.`type` AS `member_type`, 0144 `member`.`member_id` AS `project_member_id`, 0145 `member`.`username` AS `username`, 0146 `member`.`profile_image_url` AS `profile_image_url`, 0147 `member`.`city` AS `city`, 0148 `member`.`country` AS `country`, 0149 `member`.`created_at` AS `member_created_at`, 0150 `member`.`paypal_mail` AS `paypal_mail`, 0151 `project_category`.`title` AS `cat_title`, 0152 `project_category`.`xdg_type` AS `cat_xdg_type`, 0153 `project_category`.`name_legacy` AS `cat_name_legacy`, 0154 `project_category`.`show_description` AS `cat_show_description`, 0155 `tmp_project_plings`.`count_plings` AS `count_plings`, 0156 `trp`.`amount_reports` AS `amount_reports`, 0157 `tppt`.`package_type_id_list` AS `package_types`, 0158 `tppt`.`package_name_list` AS `package_names`, 0159 `t`.`tag_names` AS `tags`, 0160 `t`.`tag_ids` AS `tag_ids`, 0161 `sdqy`.amount AS count_downloads_quarter, 0162 `project_license`.title AS project_license_title 0163 FROM 0164 `project` 0165 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0166 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0167 LEFT JOIN `tmp_project_plings` ON `tmp_project_plings`.`project_id` = `project`.`project_id` 0168 LEFT JOIN `tmp_reported_projects` AS trp ON `trp`.`project_id` = `project`.`project_id` 0169 LEFT JOIN `tmp_project_package_types` AS tppt ON tppt.project_id = `project`.project_id 0170 LEFT JOIN `tmp_project_tags` AS t ON t.`tag_project_id` = project.`project_id` 0171 LEFT JOIN `stat_downloads_quarter_year` AS sdqy ON sdqy.project_id = project.project_id 0172 LEFT JOIN `project_license` ON project_license.project_license_id = project.project_license_id 0173 LEFT JOIN `stat_rating_project` ON stat_rating_project.project_id = project.project_id 0174 0175 WHERE 0176 `member`.`is_deleted` = 0 0177 AND `member`.`is_active` = 1 0178 AND (`project`.`type_id` = 1 OR `project`.`type_id` = 3) 0179 AND `project`.`status` = 100 0180 AND `project_category`.`is_active` = 1 0181 ; 0182 0183 RENAME TABLE stat_projects TO old_stat_projects, tmp_stat_projects TO stat_projects; 0184 0185 DROP TABLE IF EXISTS old_stat_projects; 0186 END$$ 0187 DELIMITER ;