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 $gfwww.'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 db_query_params ('INSERT INTO user_metric0
79 (user_id,times_ranked,avg_raters_importance,avg_rating,metric,percentile,importance_factor)
80 SELECT user_id,5,1.25,3,0,0,1.25
83 user_group.group_id=$1
84 AND user_group.admin_flags=$2',
85 array (forge_get_config('peer_rating_group'),
91 db_query_params ('UPDATE user_metric0 SET ranking=ranking-1',
94 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',
97 db_query_params ('UPDATE user_metric0 SET importance_factor=(1+((percentile::float/100)*.5))::float',
101 db_query_params ('SELECT * INTO TEMPORARY TABLE user_metric_cur FROM user_metric0',
105 for ($i=1; $i<9; $i++) {
109 Set up an interim table to grab and average all trusted result
111 db_drop_table_if_exists ("user_metric_tmp_next");
112 db_drop_table_if_exists ("user_metric_next");
114 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_tmp_next (
115 user_id int not null default 0,
116 times_ranked float(8) null default 0,
117 avg_raters_importance float(8) not null default 0,
118 avg_rating float(8) not null default 0,
119 metric float(8) not null default 0)',
122 $err .= "Error in round $i creating table: " . db_error();
127 Now grab/average trusted ratings into this table
130 $res = db_query_params ('INSERT INTO user_metric_tmp_next
131 SELECT user_ratings.user_id,count(*) AS count,
132 avg(user_metric_cur.importance_factor),
133 avg(user_ratings.rating),0
134 FROM user_ratings,user_metric_cur
135 WHERE user_ratings.rated_by=user_metric_cur.user_id
136 GROUP BY user_ratings.user_id',
139 $err .= "Error in round $i inserting average ratings: " . db_error();
145 Now calculate the metric on the temp table
147 This metric will be used in the next step to calculate ranking and importance
150 $res = db_query_params ('UPDATE user_metric_tmp_next SET metric=(log(times_ranked)*avg_raters_importance*avg_rating)',
153 $err .= "Error in round $i calculating metric: " . db_error();
158 $res = db_query_params ('DELETE FROM user_metric_tmp_next WHERE metric < $1',
159 array ($threshhold)) ;
161 $err .= "Error in round $i deleting < threshhold ids: " . db_error();
167 Now we need to carry forward trusted IDs from the last round into this
168 Round, as prior round people may not have been ranked enough times by
169 new people in this round to stay in
172 $res = db_query_params ('INSERT INTO user_metric_tmp_next
173 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
176 (SELECT user_id FROM user_metric_tmp_next
177 WHERE user_metric_tmp_next.user_id=user_metric_cur.user_id)',
180 $err .= "Error in round $i inserting final data: " . db_error();
185 Now calculate the metric for this round
187 Create the final table, then insert the data
190 db_drop_table_if_exists ("user_metric_next");
191 db_drop_sequence_if_exists ("user_metric_next_ranking_seq");
193 $res = db_query_params ('CREATE TEMPORARY TABLE user_metric_next (
195 user_id int not null default 0,
196 times_ranked int not null default 0,
197 avg_raters_importance float(8) not null default 0,
198 avg_rating float(8) not null default 0,
199 metric float(8) not null default 0,
200 percentile float(8) not null default 0,
201 importance_factor float(8) not null default 0)',
205 $err .= "Error in round $i creating table: " . db_error();
210 Insert the data in ranked order
213 $res = db_query_params ('INSERT INTO user_metric_next (user_id,times_ranked,avg_raters_importance,avg_rating,metric)
214 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
215 FROM user_metric_tmp_next
216 ORDER BY metric DESC',
219 $err .= "Error in round $i inserting final data: " . db_error();
224 Get the row count so we can calc the percentile below
226 $res = db_query_params ('SELECT COUNT(*) FROM user_metric_next',
229 $err .= "Error in round $i getting row count: " . db_error();
233 // Only do final percentile if row count is not zero
234 if (db_result($res,0,0)) {
237 Update with final percentile and importance
239 $res = db_query_params ('UPDATE user_metric_next SET
240 percentile=(100-(100*((ranking::float-1)/$1)))',
241 array (db_result($res,0,0))) ;
242 if (!$res || db_affected_rows($res) < 1) {
243 $err .= "Error in round $i setting percentile: " . db_error();
246 $res = db_query_params ('UPDATE user_metric_next SET
247 importance_factor=(1+((percentile/100)*.5))',
249 if (!$res || db_affected_rows($res) < 1) {
250 $err .= "Error in round $i setting importance factor: " . db_error();
255 db_drop_table_if_exists ("user_metric_tmp_cur");
256 db_drop_table_if_exists ("user_metric_cur");
257 db_query_params ('SELECT * INTO user_metric_cur FROM user_metric_next',
259 db_query_params ('SELECT * INTO user_metric_tmp_cur FROM user_metric_tmp_next',
261 db_drop_table_if_exists ("user_metric_tmp_next");
262 db_drop_table_if_exists ("user_metric_next");
265 db_query_params ('DELETE FROM user_metric',
267 db_query_params ('INSERT INTO user_metric SELECT * FROM user_metric_cur',
270 db_drop_table_if_exists ("user_metric_tmp_cur");
271 db_drop_table_if_exists ("user_metric_cur");
276 $ts_month = date('Ym', $t);
277 $ts_day = date('d', $t);
280 db_query_params ('DELETE FROM user_metric_history WHERE month=$1 AND day=$2',
285 INSERT INTO user_metric_history
286 SELECT $1,$2,user_id,ranking,metric