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