4 * Copyright 1999-2001 (c) VA Linux Systems
5 * Copyright 2003 (c) FusionForge, LLC
6 * Copyright 2009 (c) Roland Mas
8 * This file is part of FusionForge. FusionForge is free software;
9 * you can redistribute it and/or modify it under the terms of the
10 * GNU General Public License as published by the Free Software
11 * Foundation; either version 2 of the Licence, or (at your option)
14 * FusionForge is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License along
20 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
21 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
25 Before running the first time, execute initializing SQL:
27 CREATE TABLE user_metric_history(
34 CREATE INDEX user_metric_history_date_userid
35 ON user_metric_history(month,day,user_id);
38 Nightly cron script to calculate the peer ratings
40 The process starts with a seed group of users who are "trusted"
43 After you are rated N times highly by other users, you can become trusted
44 and your ratings of others will begin to count
46 Your rating is affected by how many times you are rated by others and
47 how highly they rate you and how highly rated they are
49 How highly rated they are is affected by how many times they're rated
50 and how highly rated they are and so on up the chain
52 For now, this process will run 8 times to get the calculations refined
53 As more users are added, it may have to be run more
55 Because of this circular dependency, the numbers are never "right", but
56 after a few runs, they should be refined "enough" to give us
57 what we want - a list of the top users on the site.
60 require dirname(__FILE__).'/../www/env.inc.php';
61 require_once $gfcommon.'include/pre.php';
62 require $gfcommon.'include/cron_utils.php';
69 db_query_params ('DELETE FROM user_metric0',
73 db_query_params ('select setval($1,1)',
74 array('user_metric0_pk_seq')) ;
77 foreach(RBACEngine::getInstance()->getUsersByAllowedAction ('project_admin',forge_get_config('peer_rating_group')) as $u) {
78 db_query_params ('INSERT INTO user_metric0 (user_id,times_ranked,avg_raters_importance,avg_rating,metric,percentile,importance_factor) VALUES ($1,5,1.25,3,0,0,1.25)',
79 array ($u->getID())) ;
83 db_query_params ('UPDATE user_metric0 SET ranking=ranking-1',
86 db_query_params ('UPDATE user_metric0 SET metric=(log(times_ranked::float)*avg_rating::float)::float, percentile=(100-(100*((ranking::float-1)/(select count(*) from user_metric0))))::float',
89 db_query_params ('UPDATE user_metric0 SET importance_factor=(1+((percentile::float/100)*.5))::float',
93 db_query_params ('SELECT * INTO TEMPORARY TABLE user_metric_cur FROM user_metric0',
97 for ($i=1; $i<9; $i++) {
101 Set up an interim table to grab and average all trusted result
103 db_drop_table_if_exists ("user_metric_tmp_next");
104 db_drop_table_if_exists ("user_metric_next");
106 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_tmp_next (
107 user_id int not null default 0,
108 times_ranked float(8) null default 0,
109 avg_raters_importance float(8) not null default 0,
110 avg_rating float(8) not null default 0,
111 metric float(8) not null default 0)',
114 $err .= "Error in round $i creating table: " . db_error();
119 Now grab/average trusted ratings into this table
122 $res = db_query_params ('INSERT INTO user_metric_tmp_next
123 SELECT user_ratings.user_id,count(*) AS count,
124 avg(user_metric_cur.importance_factor),
125 avg(user_ratings.rating),0
126 FROM user_ratings,user_metric_cur
127 WHERE user_ratings.rated_by=user_metric_cur.user_id
128 GROUP BY user_ratings.user_id',
131 $err .= "Error in round $i inserting average ratings: " . db_error();
137 Now calculate the metric on the temp table
139 This metric will be used in the next step to calculate ranking and importance
142 $res = db_query_params ('UPDATE user_metric_tmp_next SET metric=(log(times_ranked)*avg_raters_importance*avg_rating)',
145 $err .= "Error in round $i calculating metric: " . db_error();
150 $res = db_query_params ('DELETE FROM user_metric_tmp_next WHERE metric < $1',
151 array ($threshhold)) ;
153 $err .= "Error in round $i deleting < threshhold ids: " . db_error();
159 Now we need to carry forward trusted IDs from the last round into this
160 Round, as prior round people may not have been ranked enough times by
161 new people in this round to stay in
164 $res = db_query_params ('INSERT INTO user_metric_tmp_next
165 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
168 (SELECT user_id FROM user_metric_tmp_next
169 WHERE user_metric_tmp_next.user_id=user_metric_cur.user_id)',
172 $err .= "Error in round $i inserting final data: " . db_error();
177 Now calculate the metric for this round
179 Create the final table, then insert the data
182 db_drop_table_if_exists ("user_metric_next");
183 db_drop_sequence_if_exists ("user_metric_next_ranking_seq");
185 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_next (
187 user_id int not null default 0,
188 times_ranked int not null default 0,
189 avg_raters_importance float(8) not null default 0,
190 avg_rating float(8) not null default 0,
191 metric float(8) not null default 0,
192 percentile float(8) not null default 0,
193 importance_factor float(8) not null default 0)',
197 $err .= "Error in round $i creating table: " . db_error();
202 Insert the data in ranked order
205 $res = db_query_params ('INSERT INTO user_metric_next (user_id,times_ranked,avg_raters_importance,avg_rating,metric)
206 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
207 FROM user_metric_tmp_next
208 ORDER BY metric DESC',
211 $err .= "Error in round $i inserting final data: " . db_error();
216 Get the row count so we can calc the percentile below
218 $res = db_query_params ('SELECT COUNT(*) FROM user_metric_next',
221 $err .= "Error in round $i getting row count: " . db_error();
225 // Only do final percentile if row count is not zero
226 if (db_result($res,0,0)) {
229 Update with final percentile and importance
231 $res = db_query_params ('UPDATE user_metric_next SET
232 percentile=(100-(100*((ranking::float-1)/$1)))',
233 array (db_result($res,0,0))) ;
234 if (!$res || db_affected_rows($res) < 1) {
235 $err .= "Error in round $i setting percentile: " . db_error();
238 $res = db_query_params ('UPDATE user_metric_next SET
239 importance_factor=(1+((percentile/100)*.5))',
241 if (!$res || db_affected_rows($res) < 1) {
242 $err .= "Error in round $i setting importance factor: " . db_error();
247 db_drop_table_if_exists ("user_metric_tmp_cur");
248 db_drop_table_if_exists ("user_metric_cur");
249 db_query_params ('SELECT * INTO user_metric_cur FROM user_metric_next',
251 db_query_params ('SELECT * INTO user_metric_tmp_cur FROM user_metric_tmp_next',
253 db_drop_table_if_exists ("user_metric_tmp_next");
254 db_drop_table_if_exists ("user_metric_next");
257 db_query_params ('DELETE FROM user_metric',
259 db_query_params ('INSERT INTO user_metric SELECT * FROM user_metric_cur',
262 db_drop_table_if_exists ("user_metric_tmp_cur");
263 db_drop_table_if_exists ("user_metric_cur");
268 $ts_month = date('Ym', $t);
269 $ts_day = date('d', $t);
272 db_query_params ('DELETE FROM user_metric_history WHERE month=$1 AND day=$2',
277 INSERT INTO user_metric_history
278 SELECT $1,$2,user_id,ranking,metric