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.
10 * FusionForge is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
15 * FusionForge is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License
21 * along with FusionForge; if not, write to the Free Software
22 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
26 Before running the first time, execute initializing SQL:
28 CREATE TABLE user_metric_history(
35 CREATE INDEX user_metric_history_date_userid
36 ON user_metric_history(month,day,user_id);
39 Nightly cron script to calculate the peer ratings
41 The process starts with a seed group of users who are "trusted"
44 After you are rated N times highly by other users, you can become trusted
45 and your ratings of others will begin to count
47 Your rating is affected by how many times you are rated by others and
48 how highly they rate you and how highly rated they are
50 How highly rated they are is affected by how many times they're rated
51 and how highly rated they are and so on up the chain
53 For now, this process will run 8 times to get the calculations refined
54 As more users are added, it may have to be run more
56 Because of this circular dependency, the numbers are never "right", but
57 after a few runs, they should be refined "enough" to give us
58 what we want - a list of the top users on the site.
61 require dirname(__FILE__).'/../www/env.inc.php';
62 require_once $gfcommon.'include/pre.php';
63 require $gfcommon.'include/cron_utils.php';
70 db_query_params ('DELETE FROM user_metric0',
74 db_query_params ('select setval($1,1)',
75 array('user_metric0_pk_seq')) ;
78 foreach(RBACEngine::getInstance()->getUsersByAllowedAction ('project_admin',forge_get_config('peer_rating_group')) as $u) {
79 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)',
80 array (forge_get_config('peer_rating_group'))) ;
84 db_query_params ('UPDATE user_metric0 SET ranking=ranking-1',
87 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',
90 db_query_params ('UPDATE user_metric0 SET importance_factor=(1+((percentile::float/100)*.5))::float',
94 db_query_params ('SELECT * INTO TEMPORARY TABLE user_metric_cur FROM user_metric0',
98 for ($i=1; $i<9; $i++) {
102 Set up an interim table to grab and average all trusted result
104 db_drop_table_if_exists ("user_metric_tmp_next");
105 db_drop_table_if_exists ("user_metric_next");
107 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_tmp_next (
108 user_id int not null default 0,
109 times_ranked float(8) null default 0,
110 avg_raters_importance float(8) not null default 0,
111 avg_rating float(8) not null default 0,
112 metric float(8) not null default 0)',
115 $err .= "Error in round $i creating table: " . db_error();
120 Now grab/average trusted ratings into this table
123 $res = db_query_params ('INSERT INTO user_metric_tmp_next
124 SELECT user_ratings.user_id,count(*) AS count,
125 avg(user_metric_cur.importance_factor),
126 avg(user_ratings.rating),0
127 FROM user_ratings,user_metric_cur
128 WHERE user_ratings.rated_by=user_metric_cur.user_id
129 GROUP BY user_ratings.user_id',
132 $err .= "Error in round $i inserting average ratings: " . db_error();
138 Now calculate the metric on the temp table
140 This metric will be used in the next step to calculate ranking and importance
143 $res = db_query_params ('UPDATE user_metric_tmp_next SET metric=(log(times_ranked)*avg_raters_importance*avg_rating)',
146 $err .= "Error in round $i calculating metric: " . db_error();
151 $res = db_query_params ('DELETE FROM user_metric_tmp_next WHERE metric < $1',
152 array ($threshhold)) ;
154 $err .= "Error in round $i deleting < threshhold ids: " . db_error();
160 Now we need to carry forward trusted IDs from the last round into this
161 Round, as prior round people may not have been ranked enough times by
162 new people in this round to stay in
165 $res = db_query_params ('INSERT INTO user_metric_tmp_next
166 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
169 (SELECT user_id FROM user_metric_tmp_next
170 WHERE user_metric_tmp_next.user_id=user_metric_cur.user_id)',
173 $err .= "Error in round $i inserting final data: " . db_error();
178 Now calculate the metric for this round
180 Create the final table, then insert the data
183 db_drop_table_if_exists ("user_metric_next");
184 db_drop_sequence_if_exists ("user_metric_next_ranking_seq");
186 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_next (
188 user_id int not null default 0,
189 times_ranked int not null default 0,
190 avg_raters_importance float(8) not null default 0,
191 avg_rating float(8) not null default 0,
192 metric float(8) not null default 0,
193 percentile float(8) not null default 0,
194 importance_factor float(8) not null default 0)',
198 $err .= "Error in round $i creating table: " . db_error();
203 Insert the data in ranked order
206 $res = db_query_params ('INSERT INTO user_metric_next (user_id,times_ranked,avg_raters_importance,avg_rating,metric)
207 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
208 FROM user_metric_tmp_next
209 ORDER BY metric DESC',
212 $err .= "Error in round $i inserting final data: " . db_error();
217 Get the row count so we can calc the percentile below
219 $res = db_query_params ('SELECT COUNT(*) FROM user_metric_next',
222 $err .= "Error in round $i getting row count: " . db_error();
226 // Only do final percentile if row count is not zero
227 if (db_result($res,0,0)) {
230 Update with final percentile and importance
232 $res = db_query_params ('UPDATE user_metric_next SET
233 percentile=(100-(100*((ranking::float-1)/$1)))',
234 array (db_result($res,0,0))) ;
235 if (!$res || db_affected_rows($res) < 1) {
236 $err .= "Error in round $i setting percentile: " . db_error();
239 $res = db_query_params ('UPDATE user_metric_next SET
240 importance_factor=(1+((percentile/100)*.5))',
242 if (!$res || db_affected_rows($res) < 1) {
243 $err .= "Error in round $i setting importance factor: " . db_error();
248 db_drop_table_if_exists ("user_metric_tmp_cur");
249 db_drop_table_if_exists ("user_metric_cur");
250 db_query_params ('SELECT * INTO user_metric_cur FROM user_metric_next',
252 db_query_params ('SELECT * INTO user_metric_tmp_cur FROM user_metric_tmp_next',
254 db_drop_table_if_exists ("user_metric_tmp_next");
255 db_drop_table_if_exists ("user_metric_next");
258 db_query_params ('DELETE FROM user_metric',
260 db_query_params ('INSERT INTO user_metric SELECT * FROM user_metric_cur',
263 db_drop_table_if_exists ("user_metric_tmp_cur");
264 db_drop_table_if_exists ("user_metric_cur");
269 $ts_month = date('Ym', $t);
270 $ts_day = date('d', $t);
273 db_query_params ('DELETE FROM user_metric_history WHERE month=$1 AND day=$2',
278 INSERT INTO user_metric_history
279 SELECT $1,$2,user_id,ranking,metric