File indexing completed on 2024-04-21 06:00:37
0001 ALTER TABLE project_rating 0002 ADD COLUMN score_test int(2) COMMENT 'vote up = 9 votedown = 3' AFTER score; 0003 0004 update project_rating set score_test = 9 where user_like = 1 0005 and created_at < '2019-05-14 10:00:00'; 0006 update project_rating set score_test = 3 where user_dislike = 1 0007 and created_at < '2019-05-14 10:00:00'; 0008 0009 update project_rating set score_test = score 0010 where created_at > '2019-05-14 10:00:00'; 0011 0012 update project_rating set score_test = round(((user_like-6)*9+(user_dislike-6)*3+10*5)/(user_like+user_dislike+10-12),2) 0013 where user_like >=6 and user_dislike>=6 ; 0014 0015 0016 DELIMITER $$ 0017 drop PROCEDURE generate_stat_rating_project; 0018 CREATE PROCEDURE `generate_stat_rating_project`() 0019 BEGIN 0020 DROP TABLE IF EXISTS tmp_stat_rating_project; 0021 CREATE TABLE tmp_stat_rating_project 0022 ( 0023 `project_id` int(11) NOT NULL, 0024 `likes` int(11) NOT NULL, 0025 `dislikes` int(11) NOT NULL, 0026 `votes_total` int(11) NOT NULL, 0027 `score` int(11) NOT NULL, 0028 `score_with_pling` int(11) NOT NULL, 0029 `score_test` int(11) NOT NULL, 0030 PRIMARY KEY `primary` (project_id) 0031 ) 0032 AS 0033 SELECT pr.project_id, 0034 sum(pr.likes) AS likes, 0035 sum(pr.dislikes) AS dislikes, 0036 sum(pr.likes) + sum(pr.dislikes) AS votes_total, 0037 laplace_score(sum(pr.likes), sum(pr.dislikes)) AS score, 0038 (sum(pr.totalscore)+2*5)/(sum(pr.count)+2)*100 AS score_with_pling, 0039 (sum(pr.totalscore_test)+10*5)/(sum(pr.count)+10)*100 AS score_test 0040 from 0041 ( 0042 select project_id 0043 ,user_like as likes 0044 ,user_dislike as dislikes 0045 ,1 as count 0046 ,score as totalscore 0047 ,score_test as totalscore_test 0048 from project_rating pr where pr.rating_active = 1 0049 union all 0050 select 0051 project_id 0052 ,user_like-6 as likes 0053 ,user_dislike-6 as dislikes 0054 ,user_like+user_dislike-12 as count 0055 ,(user_like-6)*8+(user_dislike-6)*3 as totalscore 0056 ,(user_like-6)*9+(user_dislike-6)*3 as totalscore_test 0057 from project_rating pr 0058 where pr.rating_active = 0 and user_dislike >=6 and user_like>=6 0059 ) pr 0060 group by project_id; 0061 0062 IF EXISTS(SELECT table_name 0063 FROM INFORMATION_SCHEMA.TABLES 0064 WHERE table_schema = DATABASE() 0065 AND table_name = 'stat_rating_project') 0066 THEN 0067 RENAME TABLE stat_rating_project TO old_stat_rating_project, tmp_stat_rating_project TO stat_rating_project; 0068 0069 ELSE 0070 RENAME TABLE tmp_stat_rating_project TO stat_rating_project; 0071 0072 END IF; 0073 0074 0075 DROP TABLE IF EXISTS old_stat_rating_project; 0076 END$$ 0077 DELIMITER ; 0078 0079 0080 DELIMITER $$ 0081 drop PROCEDURE generate_stat_project; 0082 CREATE PROCEDURE `generate_stat_project`() 0083 BEGIN 0084 DROP TABLE IF EXISTS tmp_reported_projects; 0085 CREATE TEMPORARY TABLE tmp_reported_projects 0086 (PRIMARY KEY `primary` (project_id) ) 0087 AS 0088 SELECT 0089 `reports_project`.`project_id` AS `project_id`, 0090 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0091 MAX(`reports_project`.`created_at`) AS `latest_report` 0092 FROM 0093 `reports_project` 0094 WHERE 0095 (`reports_project`.`is_deleted` = 0 AND `reports_project`.`report_type` = 0) 0096 GROUP BY `reports_project`.`project_id` 0097 ; 0098 0099 DROP TABLE IF EXISTS tmp_project_package_types; 0100 CREATE TEMPORARY TABLE tmp_project_package_types 0101 (PRIMARY KEY `primary` (project_id)) 0102 ENGINE MyISAM 0103 AS 0104 SELECT 0105 tag_object.tag_parent_object_id as project_id, 0106 GROUP_CONCAT(DISTINCT tag_object.tag_id) AS package_type_id_list, 0107 GROUP_CONCAT(DISTINCT tag.tag_fullname) AS `package_name_list` 0108 FROM 0109 tag_object 0110 JOIN 0111 tag ON tag_object.tag_id = tag.tag_id 0112 JOIN 0113 ppload.ppload_files files ON files.id = tag_object.tag_object_id 0114 WHERE 0115 tag_object.tag_group_id = 8 0116 AND tag_object.is_deleted = 0 0117 AND files.active = 1 0118 GROUP BY tag_object.tag_parent_object_id 0119 ; 0120 0121 DROP TABLE IF EXISTS tmp_project_tags; 0122 CREATE TEMPORARY TABLE tmp_project_tags 0123 (PRIMARY KEY `primary` (tag_project_id)) 0124 ENGINE MyISAM 0125 AS 0126 SELECT 0127 GROUP_CONCAT(tag_name) AS tag_names, 0128 GROUP_CONCAT(tag_id) AS tag_ids, 0129 tag_project_id 0130 FROM ( 0131 select 0132 distinct tag.tag_name, 0133 tag.tag_id, 0134 tgo.tag_object_id AS tag_project_id 0135 FROM tag_object AS tgo 0136 JOIN tag ON tag.tag_id = tgo.tag_id 0137 WHERE tag_type_id = 1 #project 0138 AND tgo.is_deleted = 0 0139 UNION ALL 0140 select 0141 distinct tag.tag_name, 0142 tag.tag_ID, 0143 tgo.tag_parent_object_id AS tag_project_id 0144 FROM tag_object AS tgo 0145 JOIN tag ON tag.tag_id = tgo.tag_id 0146 JOIN ppload.ppload_files files ON files.id = tgo.tag_object_id 0147 WHERE tag_type_id = 3 #file 0148 AND files.active = 1 0149 AND tgo.is_deleted = 0 0150 ) A 0151 GROUP BY tag_project_id 0152 ORDER BY tag_project_id; 0153 0154 DROP TABLE IF EXISTS tmp_stat_projects; 0155 CREATE TABLE tmp_stat_projects 0156 (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))) 0157 ENGINE MyISAM 0158 AS 0159 SELECT 0160 `project`.`project_id` AS `project_id`, 0161 `project`.`member_id` AS `member_id`, 0162 `project`.`content_type` AS `content_type`, 0163 `project`.`project_category_id` AS `project_category_id`, 0164 `project`.`hive_category_id` AS `hive_category_id`, 0165 `project`.`status` AS `status`, 0166 `project`.`uuid` AS `uuid`, 0167 `project`.`pid` AS `pid`, 0168 `project`.`type_id` AS `type_id`, 0169 `project`.`title` AS `title`, 0170 `project`.`description` AS `description`, 0171 `project`.`version` AS `version`, 0172 `project`.`project_license_id` AS `project_license_id`, 0173 `project`.`image_big` AS `image_big`, 0174 `project`.`image_small` AS `image_small`, 0175 `project`.`start_date` AS `start_date`, 0176 `project`.`content_url` AS `content_url`, 0177 `project`.`created_at` AS `created_at`, 0178 `project`.`changed_at` AS `changed_at`, 0179 `project`.`major_updated_at` AS `major_updated_at`, 0180 `project`.`deleted_at` AS `deleted_at`, 0181 `project`.`creator_id` AS `creator_id`, 0182 `project`.`facebook_code` AS `facebook_code`, 0183 `project`.`source_url` AS `source_url`, 0184 `project`.`twitter_code` AS `twitter_code`, 0185 `project`.`google_code` AS `google_code`, 0186 `project`.`link_1` AS `link_1`, 0187 `project`.`embed_code` AS `embed_code`, 0188 CAST(`project`.`ppload_collection_id` AS UNSIGNED) AS `ppload_collection_id`, 0189 `project`.`validated` AS `validated`, 0190 `project`.`validated_at` AS `validated_at`, 0191 `project`.`featured` AS `featured`, 0192 `project`.`ghns_excluded` AS `ghns_excluded`, 0193 `project`.`amount` AS `amount`, 0194 `project`.`amount_period` AS `amount_period`, 0195 `project`.`claimable` AS `claimable`, 0196 `project`.`claimed_by_member` AS `claimed_by_member`, 0197 IFNULL(`stat_rating_project`.`likes`, 0) AS `count_likes`, 0198 IFNULL(`stat_rating_project`.`dislikes`, 0) AS `count_dislikes`, 0199 `project`.`count_comments` AS `count_comments`, 0200 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0201 `project`.`source_id` AS `source_id`, 0202 `project`.`source_pk` AS `source_pk`, 0203 `project`.`source_type` AS `source_type`, 0204 `project`.`validated` AS `project_validated`, 0205 `project`.`uuid` AS `project_uuid`, 0206 `project`.`status` AS `project_status`, 0207 `project`.`created_at` AS `project_created_at`, 0208 `project`.`changed_at` AS `project_changed_at`, 0209 IFNULL(`stat_rating_project`.`score`, 50) AS `laplace_score_old`, 0210 IFNULL(`stat_rating_project`.`score_with_pling`, 500) AS `laplace_score`, 0211 IFNULL(`stat_rating_project`.`score_test`, 500) AS `laplace_score_test`, 0212 `member`.`type` AS `member_type`, 0213 `member`.`member_id` AS `project_member_id`, 0214 `member`.`username` AS `username`, 0215 `member`.`profile_image_url` AS `profile_image_url`, 0216 `member`.`city` AS `city`, 0217 `member`.`country` AS `country`, 0218 `member`.`created_at` AS `member_created_at`, 0219 `member`.`paypal_mail` AS `paypal_mail`, 0220 `project_category`.`title` AS `cat_title`, 0221 `project_category`.`xdg_type` AS `cat_xdg_type`, 0222 `project_category`.`name_legacy` AS `cat_name_legacy`, 0223 `project_category`.`show_description` AS `cat_show_description`, 0224 `stat_plings`.`amount_received` AS `amount_received`, 0225 `stat_plings`.`count_plings` AS `count_plings`, 0226 `stat_plings`.`count_plingers` AS `count_plingers`, 0227 `stat_plings`.`latest_pling` AS `latest_pling`, 0228 `trp`.`amount_reports` AS `amount_reports`, 0229 `tppt`.`package_type_id_list` AS `package_types`, 0230 `tppt`.`package_name_list` AS `package_names`, 0231 `t`.`tag_names` AS `tags`, 0232 `t`.`tag_ids` AS `tag_ids`, 0233 `sdqy`.amount AS count_downloads_quarter, 0234 `project_license`.title AS project_license_title 0235 FROM 0236 `project` 0237 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0238 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0239 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0240 LEFT JOIN `tmp_reported_projects` AS trp ON `trp`.`project_id` = `project`.`project_id` 0241 LEFT JOIN `tmp_project_package_types` AS tppt ON tppt.project_id = `project`.project_id 0242 LEFT JOIN `tmp_project_tags` AS t ON t.`tag_project_id` = project.`project_id` 0243 LEFT JOIN `stat_downloads_quarter_year` AS sdqy ON sdqy.project_id = project.project_id 0244 LEFT JOIN `project_license` ON project_license.project_license_id = project.project_license_id 0245 LEFT JOIN `stat_rating_project` ON stat_rating_project.project_id = project.project_id 0246 0247 WHERE 0248 `member`.`is_deleted` = 0 0249 AND `member`.`is_active` = 1 0250 AND (`project`.`type_id` = 1 OR `project`.`type_id` = 3) 0251 AND `project`.`status` = 100 0252 AND `project_category`.`is_active` = 1 0253 ; 0254 0255 RENAME TABLE stat_projects TO old_stat_projects, tmp_stat_projects TO stat_projects; 0256 0257 DROP TABLE IF EXISTS old_stat_projects; 0258 END$$ 0259 DELIMITER ;