diff --git a/sql_code/20190617_stat_rating_project.sql b/sql_code/20190617_stat_rating_project.sql new file mode 100644 index 000000000..6090a567f --- /dev/null +++ b/sql_code/20190617_stat_rating_project.sql @@ -0,0 +1,259 @@ +ALTER TABLE project_rating + ADD COLUMN score_test int(2) COMMENT 'vote up = 9 votedown = 3' AFTER score; + +update project_rating set score_test = 9 where user_like = 1 + and created_at < '2019-05-14 10:00:00'; +update project_rating set score_test = 3 where user_dislike = 1 + and created_at < '2019-05-14 10:00:00'; + +update project_rating set score_test = score + where created_at > '2019-05-14 10:00:00'; + +update project_rating set score_test = round(((user_like-6)*9+(user_dislike-6)*3+10*5)/(user_like+user_dislike+10-12),2) + where user_like >=6 and user_dislike>=6 ; + + +DELIMITER $$ +drop PROCEDURE generate_stat_rating_project; +CREATE PROCEDURE `generate_stat_rating_project`() +BEGIN + DROP TABLE IF EXISTS tmp_stat_rating_project; + CREATE TABLE tmp_stat_rating_project + ( + `project_id` int(11) NOT NULL, + `likes` int(11) NOT NULL, + `dislikes` int(11) NOT NULL, + `votes_total` int(11) NOT NULL, + `score` int(11) NOT NULL, + `score_with_pling` int(11) NOT NULL, + `score_test` int(11) NOT NULL, + PRIMARY KEY `primary` (project_id) + ) + AS + SELECT pr.project_id, + sum(pr.likes) AS likes, + sum(pr.dislikes) AS dislikes, + sum(pr.likes) + sum(pr.dislikes) AS votes_total, + laplace_score(sum(pr.likes), sum(pr.dislikes)) AS score, + (sum(pr.totalscore)+2*5)/(sum(pr.count)+2)*100 AS score_with_pling, + (sum(pr.totalscore_test)+10*5)/(sum(pr.count)+10)*100 AS score_test, + from + ( + select project_id + ,user_like as likes + ,user_dislike as dislikes + ,1 as count + ,score as totalscore + ,score_test as totalscore_test + from project_rating pr where pr.rating_active = 1 + union all + select + project_id + ,user_like-6 as likes + ,user_dislike-6 as dislikes + ,user_like+user_dislike-12 as count + ,(user_like-6)*8+(user_dislike-6)*3 as totalscore + ,(user_like-6)*9+(user_dislike-6)*3 as totalscore_test + from project_rating pr + where pr.rating_active = 0 and user_dislike >=6 and user_like>=6 + ) pr + group by project_id; + + IF EXISTS(SELECT table_name + FROM INFORMATION_SCHEMA.TABLES + WHERE table_schema = DATABASE() + AND table_name = 'stat_rating_project') + THEN + RENAME TABLE stat_rating_project TO old_stat_rating_project, tmp_stat_rating_project TO stat_rating_project; + + ELSE + RENAME TABLE tmp_stat_rating_project TO stat_rating_project; + + END IF; + + + DROP TABLE IF EXISTS old_stat_rating_project; +END$$ +DELIMITER ; + + +DELIMITER $$ +drop PROCEDURE generate_stat_project; +CREATE PROCEDURE `generate_stat_project`() +BEGIN + DROP TABLE IF EXISTS tmp_reported_projects; + CREATE TEMPORARY TABLE tmp_reported_projects + (PRIMARY KEY `primary` (project_id) ) + AS + SELECT + `reports_project`.`project_id` AS `project_id`, + COUNT(`reports_project`.`project_id`) AS `amount_reports`, + MAX(`reports_project`.`created_at`) AS `latest_report` + FROM + `reports_project` + WHERE + (`reports_project`.`is_deleted` = 0 AND `reports_project`.`report_type` = 0) + GROUP BY `reports_project`.`project_id` + ; + + DROP TABLE IF EXISTS tmp_project_package_types; + CREATE TEMPORARY TABLE tmp_project_package_types + (PRIMARY KEY `primary` (project_id)) + ENGINE MyISAM + AS + SELECT + tag_object.tag_parent_object_id as project_id, + GROUP_CONCAT(DISTINCT tag_object.tag_id) AS package_type_id_list, + GROUP_CONCAT(DISTINCT tag.tag_fullname) AS `package_name_list` + FROM + tag_object + JOIN + tag ON tag_object.tag_id = tag.tag_id + JOIN + ppload.ppload_files files ON files.id = tag_object.tag_object_id + WHERE + tag_object.tag_group_id = 8 + AND tag_object.is_deleted = 0 + AND files.active = 1 + GROUP BY tag_object.tag_parent_object_id + ; + + DROP TABLE IF EXISTS tmp_project_tags; + CREATE TEMPORARY TABLE tmp_project_tags + (PRIMARY KEY `primary` (tag_project_id)) + ENGINE MyISAM + AS + SELECT + GROUP_CONCAT(tag_name) AS tag_names, + GROUP_CONCAT(tag_id) AS tag_ids, + tag_project_id + FROM ( + select + distinct tag.tag_name, + tag.tag_id, + tgo.tag_object_id AS tag_project_id + FROM tag_object AS tgo + JOIN tag ON tag.tag_id = tgo.tag_id + WHERE tag_type_id = 1 #project + AND tgo.is_deleted = 0 + UNION ALL + select + distinct tag.tag_name, + tag.tag_ID, + tgo.tag_parent_object_id AS tag_project_id + FROM tag_object AS tgo + JOIN tag ON tag.tag_id = tgo.tag_id + JOIN ppload.ppload_files files ON files.id = tgo.tag_object_id + WHERE tag_type_id = 3 #file + AND files.active = 1 + AND tgo.is_deleted = 0 + ) A + GROUP BY tag_project_id + ORDER BY tag_project_id; + + DROP TABLE IF EXISTS tmp_stat_projects; + CREATE TABLE tmp_stat_projects + (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))) + ENGINE MyISAM + AS + SELECT + `project`.`project_id` AS `project_id`, + `project`.`member_id` AS `member_id`, + `project`.`content_type` AS `content_type`, + `project`.`project_category_id` AS `project_category_id`, + `project`.`hive_category_id` AS `hive_category_id`, + `project`.`status` AS `status`, + `project`.`uuid` AS `uuid`, + `project`.`pid` AS `pid`, + `project`.`type_id` AS `type_id`, + `project`.`title` AS `title`, + `project`.`description` AS `description`, + `project`.`version` AS `version`, + `project`.`project_license_id` AS `project_license_id`, + `project`.`image_big` AS `image_big`, + `project`.`image_small` AS `image_small`, + `project`.`start_date` AS `start_date`, + `project`.`content_url` AS `content_url`, + `project`.`created_at` AS `created_at`, + `project`.`changed_at` AS `changed_at`, + `project`.`major_updated_at` AS `major_updated_at`, + `project`.`deleted_at` AS `deleted_at`, + `project`.`creator_id` AS `creator_id`, + `project`.`facebook_code` AS `facebook_code`, + `project`.`source_url` AS `source_url`, + `project`.`twitter_code` AS `twitter_code`, + `project`.`google_code` AS `google_code`, + `project`.`link_1` AS `link_1`, + `project`.`embed_code` AS `embed_code`, + CAST(`project`.`ppload_collection_id` AS UNSIGNED) AS `ppload_collection_id`, + `project`.`validated` AS `validated`, + `project`.`validated_at` AS `validated_at`, + `project`.`featured` AS `featured`, + `project`.`ghns_excluded` AS `ghns_excluded`, + `project`.`amount` AS `amount`, + `project`.`amount_period` AS `amount_period`, + `project`.`claimable` AS `claimable`, + `project`.`claimed_by_member` AS `claimed_by_member`, + IFNULL(`stat_rating_project`.`likes`, 0) AS `count_likes`, + IFNULL(`stat_rating_project`.`dislikes`, 0) AS `count_dislikes`, + `project`.`count_comments` AS `count_comments`, + `project`.`count_downloads_hive` AS `count_downloads_hive`, + `project`.`source_id` AS `source_id`, + `project`.`source_pk` AS `source_pk`, + `project`.`source_type` AS `source_type`, + `project`.`validated` AS `project_validated`, + `project`.`uuid` AS `project_uuid`, + `project`.`status` AS `project_status`, + `project`.`created_at` AS `project_created_at`, + `project`.`changed_at` AS `project_changed_at`, + IFNULL(`stat_rating_project`.`score`, 50) AS `laplace_score_old`, + IFNULL(`stat_rating_project`.`score_with_pling`, 500) AS `laplace_score`, + IFNULL(`stat_rating_project`.`score_test`, 500) AS `laplace_score_test`, + `member`.`type` AS `member_type`, + `member`.`member_id` AS `project_member_id`, + `member`.`username` AS `username`, + `member`.`profile_image_url` AS `profile_image_url`, + `member`.`city` AS `city`, + `member`.`country` AS `country`, + `member`.`created_at` AS `member_created_at`, + `member`.`paypal_mail` AS `paypal_mail`, + `project_category`.`title` AS `cat_title`, + `project_category`.`xdg_type` AS `cat_xdg_type`, + `project_category`.`name_legacy` AS `cat_name_legacy`, + `project_category`.`show_description` AS `cat_show_description`, + `stat_plings`.`amount_received` AS `amount_received`, + `stat_plings`.`count_plings` AS `count_plings`, + `stat_plings`.`count_plingers` AS `count_plingers`, + `stat_plings`.`latest_pling` AS `latest_pling`, + `trp`.`amount_reports` AS `amount_reports`, + `tppt`.`package_type_id_list` AS `package_types`, + `tppt`.`package_name_list` AS `package_names`, + `t`.`tag_names` AS `tags`, + `t`.`tag_ids` AS `tag_ids`, + `sdqy`.amount AS count_downloads_quarter, + `project_license`.title AS project_license_title + FROM + `project` + JOIN `member` ON `member`.`member_id` = `project`.`member_id` + JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` + LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` + LEFT JOIN `tmp_reported_projects` AS trp ON `trp`.`project_id` = `project`.`project_id` + LEFT JOIN `tmp_project_package_types` AS tppt ON tppt.project_id = `project`.project_id + LEFT JOIN `tmp_project_tags` AS t ON t.`tag_project_id` = project.`project_id` + LEFT JOIN `stat_downloads_quarter_year` AS sdqy ON sdqy.project_id = project.project_id + LEFT JOIN `project_license` ON project_license.project_license_id = project.project_license_id + LEFT JOIN `stat_rating_project` ON stat_rating_project.project_id = project.project_id + + WHERE + `member`.`is_deleted` = 0 + AND `member`.`is_active` = 1 + AND (`project`.`type_id` = 1 OR `project`.`type_id` = 3) + AND `project`.`status` = 100 + AND `project_category`.`is_active` = 1 + ; + + RENAME TABLE stat_projects TO old_stat_projects, tmp_stat_projects TO stat_projects; + + DROP TABLE IF EXISTS old_stat_projects; + END$$ +DELIMITER ;