File indexing completed on 2024-04-21 06:00:37
0001 0002 0003 DROP PROCEDURE IF EXISTS `generate_stat_rating_project`; 0004 0005 DELIMITER $$ 0006 CREATE PROCEDURE `generate_stat_rating_project`() 0007 BEGIN 0008 DROP TABLE IF EXISTS `tmp_stat_rating_project`; 0009 CREATE TABLE `tmp_stat_rating_project` 0010 ( 0011 `project_id` int(11) NOT NULL, 0012 `likes` int(11) NOT NULL, 0013 `dislikes` int(11) NOT NULL, 0014 `votes_total` int(11) NOT NULL, 0015 `score` int(11) NOT NULL, 0016 `score_with_pling` int(11) NOT NULL, 0017 PRIMARY KEY `primary` (`project_id`) 0018 ) 0019 AS 0020 SELECT `pr`.`project_id`, 0021 sum(`pr`.`user_like`) AS `likes`, 0022 sum(`pr`.`user_dislike`) AS `dislikes`, 0023 sum(`pr`.`user_like`) + sum(`pr`.`user_dislike`) AS `votes_total`, 0024 laplace_score(sum(`pr`.`user_like`), sum(`pr`.`user_dislike`)) AS `score`, 0025 laplace_score_with_plings(sum(`pr`.`user_like`), sum(`pr`.`user_dislike`) 0026 , (SELECT count(1) 0027 FROM `project_plings` `p` 0028 WHERE `p`.`project_id` = `pr`.`project_id` 0029 AND `is_deleted` = 0) 0030 ) AS `score_with_pling` 0031 FROM `project_rating` AS `pr` 0032 WHERE (`pr`.`rating_active` = 1 OR (`rating_active` = 0 AND `user_like` > 1)) 0033 GROUP BY `pr`.`project_id`; 0034 0035 IF EXISTS(SELECT `table_name` 0036 FROM `INFORMATION_SCHEMA`.`TABLES` 0037 WHERE `table_schema` = DATABASE() 0038 AND `table_name` = 'stat_rating_project') 0039 THEN 0040 RENAME TABLE `stat_rating_project` TO `old_stat_rating_project`, `tmp_stat_rating_project` TO `stat_rating_project`; 0041 0042 ELSE 0043 RENAME TABLE `tmp_stat_rating_project` TO `stat_rating_project`; 0044 0045 END IF; 0046 0047 0048 DROP TABLE IF EXISTS `old_stat_rating_project`; 0049 END$$ 0050 DELIMITER ;