4 * Copyright 1999-2001 (c) VA Linux Systems
5 * Copyright 2003 (c) GForge, LLC
9 * This file is part of GForge.
11 * GForge is free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
16 * GForge is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 * GNU General Public License for more details.
21 * You should have received a copy of the GNU General Public License
22 * along with GForge; if not, write to the Free Software
23 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
29 Before running the first time, execute initializing SQL:
31 CREATE TABLE user_metric_history(
38 CREATE INDEX user_metric_history_date_userid
39 ON user_metric_history(month,day,user_id);
42 Nightly cron script to calculate the peer ratings
44 The process starts with a seed group of users who are "trusted"
47 After you are rated N times highly by other users, you can become trusted
48 and your ratings of others will begin to count
50 Your rating is affected by how many times you are rated by others and
51 how highly they rate you and how highly rated they are
53 How highly rated they are is affected by how many times they're rated
54 and how highly rated they are and so on up the chain
56 For now, this process will run 8 times to get the calculations refined
57 As more users are added, it may have to be run more
59 Because of this circular dependency, the numbers are never "right", but
60 after a few runs, they should be refined "enough" to give us
61 what we want - a list of the top users on the site.
65 require $gfwww.'include/squal_pre.php';
66 require $gfcommon.'include/cron_utils.php';
73 db_query("DELETE FROM user_metric0");
76 if ($sys_database_type != 'mysql') {
77 db_query("select setval('user_metric0_pk_seq',1)");
81 db_query("INSERT INTO user_metric0
82 (user_id,times_ranked,avg_raters_importance,avg_rating,metric,percentile,importance_factor)
83 SELECT user_id,5,1.25,3,0,0,1.25
86 user_group.group_id='$sys_peer_rating_group'
87 AND user_group.admin_flags='A';");
91 db_query("UPDATE user_metric0 SET ranking=ranking-1");
93 if ($sys_database_type == 'mysql') {
95 SELECT count(*) FROM user_metric0 INTO @total;
96 UPDATE user_metric0 SET
97 metric=(log(times_ranked)*avg_rating),
98 percentile=(100-(100*(ranking-1.0)/@total));";
105 $sql="UPDATE user_metric0 SET
106 metric=(log(times_ranked::float)*avg_rating::float)::float,
107 percentile=(100-(100*((ranking::float-1)/(select count(*) from user_metric0))))::float;";
113 if ($sys_database_type == 'mysql') {
114 $sql="UPDATE user_metric0 SET importance_factor=(1+((percentile/100.0)*.5));";
116 $sql="UPDATE user_metric0 SET importance_factor=(1+((percentile::float/100)*.5))::float;";
122 for ($i=1; $i<9; $i++) {
123 // $err .= '<br />Starting round: '.$i;
128 Set up an interim table to grab and average all trusted result
130 db_drop_table_if_exists ("user_metric_tmp1_".$i);
132 $sql="CREATE TABLE user_metric_tmp1_$i (
133 user_id int not null default 0,
134 times_ranked float(8) null default 0,
135 avg_raters_importance float(8) not null default 0,
136 avg_rating float(8) not null default 0,
137 metric float(8) not null default 0);";
140 $err .= "Error in round $i inserting final data: ";
141 $err .= '<p>'.$sql.'<p>';
147 Now grab/average trusted ratings into this table
150 $sql="INSERT INTO user_metric_tmp1_$i
151 SELECT user_ratings.user_id,count(*) AS count,
152 avg(user_metric$j.importance_factor),
153 avg(user_ratings.rating),0
154 FROM user_ratings,user_metric$j
155 WHERE user_ratings.rated_by=user_metric$j.user_id
156 GROUP BY user_ratings.user_id";
160 $err .= "Error in round $i inserting average ratings: ";
161 $err .= '<p>'.$sql.'<p>';
168 Now calculate the metric on the temp table
170 This metric will be used in the next step to calculate ranking and importance
173 $sql="UPDATE user_metric_tmp1_$i SET metric=(log(times_ranked)*avg_raters_importance*avg_rating);";
176 $err .= "Error in round $i calculating metric: ";
177 $err .= '<p>'.$sql.'<p>';
183 $sql="DELETE FROM user_metric_tmp1_$i WHERE metric < $threshhold";
186 $err .= "Error in round $i deleting < threshhold ids: ";
187 $err .= '<p>'.$sql.'<p>';
194 Now we need to carry forward trusted IDs from the last round into this
195 Round, as prior round people may not have been ranked enough times by
196 new people in this round to stay in
199 $sql="INSERT INTO user_metric_tmp1_$i
200 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
203 (SELECT user_id FROM user_metric_tmp1_$i
204 WHERE user_metric_tmp1_$i.user_id=user_metric$j.user_id);";
208 $err .= "Error in round $i inserting final data: ";
209 $err .= '<p>'.$sql.'<p>';
215 Now calculate the metric for this round
217 Create the final table, then insert the data
220 // $err .= '<br />Starting Final Metric';
222 db_drop_table_if_exists ("user_metric".$i);
223 db_drop_sequence_if_exists ("user_metric".$i."_ranking_seq");
225 $sql="CREATE TABLE user_metric$i (
227 user_id int not null default 0,
228 times_ranked int not null default 0,
229 avg_raters_importance float(8) not null default 0,
230 avg_rating float(8) not null default 0,
231 metric float(8) not null default 0,
232 percentile float(8) not null default 0,
233 importance_factor float(8) not null default 0);";
237 $err .= "Error in round $i inserting final data: ";
238 $err .= '<p>'.$sql.'<p>';
244 Insert the data in ranked order
247 $sql="INSERT INTO user_metric$i (user_id,times_ranked,avg_raters_importance,avg_rating,metric)
248 SELECT user_id,times_ranked,avg_raters_importance,avg_rating,metric
249 FROM user_metric_tmp1_$i
250 ORDER BY metric DESC;";
253 $err .= "Error in round $i inserting final data: ";
254 $err .= '<p>'.$sql.'<p>';
260 Get the row count so we can calc the percentile below
262 $res=db_query("SELECT COUNT(*) FROM user_metric$i");
264 $err .= "Error in round $i getting row count: ";
265 $err .= '<p>'.$sql.'<p>';
270 //$err .= '<br />Issuing Final Update';
271 // Only do final percentile if row count is not zero
272 if (db_result($res,0,0)) {
275 Update with final percentile and importance
277 if ($sys_database_type == 'mysql') {
278 $sql="UPDATE user_metric$i SET
279 percentile=(100-(100*((ranking-1.0)/". db_result($res,0,0) .")))";
281 $sql="UPDATE user_metric$i SET
282 percentile=(100-(100*((ranking::float-1)/". db_result($res,0,0) .")))";
285 if (!$res || db_affected_rows($res) < 1) {
286 $err .= "Error in round $i setting percentile: ";
287 $err .= '<p>'.$sql.'<p>';
291 $sql="UPDATE user_metric$i SET
292 importance_factor=(1+((percentile/100)*.5));";
294 if (!$res || db_affected_rows($res) < 1) {
295 $err .= "Error in round $i setting importance factor: ";
296 $err .= '<p>'.$sql.'<p>';
304 db_query("DELETE FROM user_metric;");
305 db_query("INSERT INTO user_metric SELECT * FROM user_metric".($i-1).";");
306 //$err .= '<p>'.db_error().'<p>';
309 Now run through and drop the tmp tables
311 // $err .= "<p>Cleaning up tables<p>";
313 for ($i=1; $i<9; $i++) {
314 db_drop_table_if_exists ("user_metric_tmp1_".$i);
315 db_drop_sequence_if_exists ("user_metric_tmp1_".$i."_ranking_seq");
316 db_drop_table_if_exists ("user_metric".$i);
322 $ts_month = date('Ym', $t);
323 $ts_day = date('d', $t);
326 db_query("DELETE FROM user_metric_history WHERE month='$ts_month' AND day='$ts_day'");
328 INSERT INTO user_metric_history
329 SELECT '$ts_month','$ts_day',user_id,ranking,metric