u1J7g9KB

1DELIMITER $$
2
3CREATE FUNCTION getRating( post_id int) RETURNS float
4  BEGIN
5    DECLARE S float;
6    DECLARE W float;
7    DECLARE C float;
8    DECLARE R float;
9    DECLARE m int;
10    DECLARE v float;
11
12    # статистическая поправка
13    SET m = 10;
14
15    # id отзывов участвующих в рейтинге в временной таблице
16    CREATE TEMPORARY TABLE IF NOT EXISTS rating_ids ( SELECT comment_ID FROM wp_comments wc where
17      exists(select 1 from wp_commentmeta wt where wt.comment_id = wc.comment_id and wt.meta_key = 'ta_general_rating' ) AND
18      not exists(select 1 from wp_commentmeta wt where wt.comment_id = wc.comment_id and wt.meta_key = 'ta_status_type' ) AND
19      exists(select 1 from wp_commentmeta wt where wt.comment_id = wc.comment_id and wt.meta_key = 'ta_type' and wt.meta_value != 'Вопрос представителю' )
20
21      and wc.comment_approved = 1
22      AND wc.comment_parent = 0 );
23
24    # R -> Среднее арифметическое баллов, набранных МФО, учётом фактора времени
25    SELECT ROUND(
26        (SUM(meta_value *
27             CASE c.comment_date
28             WHEN
29               DATEDIFF(c.comment_date, NOW()) <= 90
30               THEN 1
31
32             ELSE EXP(-0.001697 * DATEDIFF(c.comment_date, NOW()))
33             END)
34        /
35        SUM(CASE c.comment_date
36             WHEN
37               DATEDIFF(c.comment_date, NOW()) <= 90
38               THEN 1
39
40             ELSE EXP(-0.001697 * DATEDIFF(c.comment_date, NOW()))
41             END)
42        ), 8
43    )
44    INTO R
45    FROM wp_comments as c INNER JOIN wp_commentmeta as m ON (m.comment_id = c.comment_ID)
46    WHERE c.comment_ID IN ( SELECT * FROM rating_ids );
47
48    # Среднее текущее значение рейтинга с учётом фактора времени по всем МФО
49    SELECT ROUND(
50        (SUM(meta_value *
51             CASE c.comment_date
52             WHEN
53               DATEDIFF(c.comment_date, NOW()) <= 90
54               THEN 1
55
56             ELSE EXP(-0.001697 * DATEDIFF(c.comment_date, NOW()))
57             END)
58        /
59        SUM(CASE c.comment_date
60             WHEN
61               DATEDIFF(c.comment_date, NOW()) <= 90
62               THEN 1
63
64             ELSE EXP(-0.001697 * DATEDIFF(c.comment_date, NOW()))
65             END)
66        ), 8
67    )
68    INTO C
69    FROM wp_comments as c INNER JOIN wp_commentmeta as m ON (m.comment_id = c.comment_ID)
70    WHERE
71      c.comment_post_ID = post_id AND
72      c.comment_ID IN ( SELECT * FROM rating_ids );
73
74    # v -> Количество засчитанных голосов с учётом фактора времени
75    SELECT ROUND(
76        (SUM(
77             CASE c.comment_date
78             WHEN
79               DATEDIFF(c.comment_date, NOW()) <= 90
80               THEN 1
81
82             ELSE EXP(-0.001697 * DATEDIFF(c.comment_date, NOW()))
83             END)
84        ), 8
85    )
86    INTO R
87    FROM wp_comments as c INNER JOIN wp_commentmeta as m ON (m.comment_id = c.comment_ID)
88    WHERE c.comment_ID IN ( SELECT * FROM rating_ids );
89
90    # удаляем пременную таблицу
91    DROP TABLE IF EXISTS rating_ids;
92    
93    # расчет рейтинга
94    SET S = ( R * v + C * m ) / v + m;
95
96    # преобразование в 100-бальную шкалу и деление на 5 (количество звездочек)
97    SET W = ( (S - 1) * 25 ) / 5;
98    RETURN W;
99
100  END $$
101
102DELIMITER ;