File indexing completed on 2024-04-14 05:53:07
0001 0002 DELIMITER $$ 0003 drop PROCEDURE generate_stat_rating_project; 0004 CREATE PROCEDURE `generate_stat_rating_project`() 0005 BEGIN 0006 DROP TABLE IF EXISTS tmp_stat_rating_project; 0007 CREATE TABLE tmp_stat_rating_project 0008 ( 0009 `project_id` int(11) NOT NULL, 0010 `likes` int(11) NOT NULL, 0011 `dislikes` int(11) NOT NULL, 0012 `votes_total` int(11) NOT NULL, 0013 `score` int(11) NOT NULL, 0014 `score_with_pling` int(11) NOT NULL, 0015 `score_test` int(11) NOT NULL, 0016 PRIMARY KEY `primary` (project_id) 0017 ) 0018 AS 0019 SELECT pr.project_id, 0020 sum(pr.likes) AS likes, 0021 sum(pr.dislikes) AS dislikes, 0022 sum(pr.likes) + sum(pr.dislikes) AS votes_total, 0023 laplace_score(sum(pr.likes), sum(pr.dislikes)) AS score, 0024 (sum(pr.totalscore)+4*5)/(sum(pr.count)+4)*100 AS score_with_pling, 0025 (sum(pr.totalscore_test)+2*5)/(sum(pr.count)+2)*100 AS score_test 0026 from 0027 ( 0028 select project_id 0029 ,user_like as likes 0030 ,user_dislike as dislikes 0031 ,1 as count 0032 ,score as totalscore 0033 ,score_test as totalscore_test 0034 from project_rating pr where pr.rating_active = 1 0035 ) pr 0036 group by project_id; 0037 0038 IF EXISTS(SELECT table_name 0039 FROM INFORMATION_SCHEMA.TABLES 0040 WHERE table_schema = DATABASE() 0041 AND table_name = 'stat_rating_project') 0042 THEN 0043 RENAME TABLE stat_rating_project TO old_stat_rating_project, tmp_stat_rating_project TO stat_rating_project; 0044 0045 ELSE 0046 RENAME TABLE tmp_stat_rating_project TO stat_rating_project; 0047 0048 END IF; 0049 0050 0051 DROP TABLE IF EXISTS old_stat_rating_project; 0052 END$$ 0053 DELIMITER ;