![[infoarena] development](/chrome/site/logo.png){kind=logo}
Changeset 1110
- Timestamp:
- 03/17/10 00:06:03 (2 years ago)
- Location:
- trunk
- Files:
-
- 1 added
- 12 edited
-
common/db/db.php (modified) (1 diff)
-
common/db/round.php (modified) (5 diffs)
-
common/db/score.php (modified) (7 diffs)
-
common/db/task.php (modified) (1 diff)
-
common/db/user.php (modified) (3 diffs)
-
common/score.php (modified) (1 diff)
-
scripts/db-strip (modified) (1 diff)
-
scripts/migrate-ia-score.php (added)
-
scripts/round-statistics (modified) (3 diffs)
-
scripts/task-difficulty (modified) (2 diffs)
-
scripts/update-ratings (modified) (2 diffs)
-
www/macros/macro_rankings.php (modified) (4 diffs)
-
www/macros/macro_tasks.php (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
trunk/common/db/db.php
r997 r1110 246 246 } 247 247 248 // Escape an array of strings. 249 function db_escape_array($array) { 250 $ret = implode(',', array_map('db_quote', $array)); 251 252 return $ret; 253 } 254 248 255 // Executes query, fetches only FIRST result row 249 256 function db_fetch($query) { -
trunk/common/db/round.php
r1109 r1110 52 52 _round_cache_delete($round); 53 53 return false; 54 } 55 } 56 57 function round_recompute_score($round_id) { 58 $query = "SELECT * FROM `ia_score_user_round` 59 WHERE `round_id` = ".db_quote($round_id); 60 $rows = db_fetch_all($query); 61 $users = array(); 62 foreach ($rows as $row) { 63 $users[] = $row['user_id']; 64 } 65 66 $query = "SELECT SUM(`score`) AS score, user_id 67 FROM ia_score_user_round_task 68 WHERE `user_id` IN ('".implode("', '", $users)."') AND 69 `round_id` = ".db_quote($round_id)." 70 GROUP BY `user_id`"; 71 $rows = db_fetch_all($query); 72 foreach($rows as $row) { 73 $user_id = $row['user_id']; 74 $score = $row['score']; 75 76 $query = "UPDATE `ia_score_user_round` 77 SET `score` = ".db_quote($score)." 78 WHERE `user_id` = ".db_quote($user_id)." && 79 `round_id` = ".db_quote($round_id); 80 db_query($query); 54 81 } 55 82 } … … 77 104 // 78 105 // if user_id is non-null a join is done on $score 79 function round_get_tasks($round_id, $first = 0, $count = null, $user_id = null, $ score_name = null, $filter = null) {106 function round_get_tasks($round_id, $first = 0, $count = null, $user_id = null, $fetch_scores = false, $filter = null) { 80 107 if ($count === null) { 81 108 $count = 666013; … … 90 117 task.`open_source` AS `open_source`, 91 118 task.`open_tests` AS `open_tests`"; 92 93 if ($score_name === null || $user_id === null) { 119 if ($user_id == null || $fetch_scores == false) { 94 120 $query = sprintf("SELECT $fields 95 121 FROM ia_round_task as round_task … … 97 123 WHERE `round_task`.`round_id` = '%s' 98 124 ORDER BY task.`order` LIMIT %d, %d", 99 db_escape($round_id), db_escape($first), db_escape($count));125 db_escape($round_id), db_escape($first), db_escape($count)); 100 126 } else { 101 127 $filter_clause = db_get_task_filter_clause($filter, 'score'); 102 128 log_assert(is_whole_number($user_id)); 103 $query = sprintf("SELECT $fields, score. score as score129 $query = sprintf("SELECT $fields, score.`score` AS `score` 104 130 FROM ia_round_task as round_task 105 131 LEFT JOIN ia_task as task ON task.id = round_task.task_id 106 LEFT JOIN ia_score as score ON 107 score.user_id = %s AND 108 score.name = '%s' AND 109 score.round_id = '%s' AND 110 score.task_id = round_task.task_id 132 LEFT JOIN ia_score_user_round_task as score ON 133 score.round_id = round_task.round_id AND 134 score.task_id = round_task.task_id AND 135 score.user_id = '%s' 111 136 WHERE `round_task`.`round_id` = '%s' 112 137 AND %s 113 138 ORDER BY task.`order` LIMIT %d, %d", 114 db_escape($user_id), db_escape($score_name), db_escape($round_id),139 db_escape($user_id), 115 140 db_escape($round_id), db_escape($filter_clause), 116 141 db_escape($first), db_escape($count)); … … 119 144 } 120 145 121 function round_get_task_count($round_id, $user_id, $ scores, $filter)146 function round_get_task_count($round_id, $user_id, $filter) 122 147 { 123 if ($user_id && $filter && $scores) {124 $filter_clause = db_get_task_filter_clause($filter, 'ia_score ');148 if ($user_id && $filter) { 149 $filter_clause = db_get_task_filter_clause($filter, 'ia_score_user_round_task'); 125 150 $query = sprintf("SELECT COUNT(*) FROM ia_round_task " . 126 "LEFT JOIN ia_score " . 127 "ON ia_round_task.round_id = ia_score.round_id " . 128 "AND ia_round_task.task_id = ia_score.task_id " . 129 "AND ia_score.user_id = %s " . 130 "AND ia_score.name = '%s' " . 151 "LEFT JOIN ia_score_user_round_task " . 152 "ON ia_round_task.round_id = ia_score_user_round_task.round_id " . 153 "AND ia_round_task.task_id = ia_score_user_round_task.task_id " . 154 "AND ia_score_user_round_task.user_id = %s " . 131 155 "WHERE ia_round_task.round_id = '%s' " . 132 156 "AND %s", 133 157 db_escape($user_id), 134 db_escape($scores),135 158 db_escape($round_id), 136 159 db_escape($filter_clause) -
trunk/common/db/score.php
r1082 r1110 6 6 require_once(IA_ROOT_DIR."common/rating.php"); 7 7 8 // Escape an array of strings.9 function db_escape_array($array)8 // Updates a user's rating and deviation 9 function score_update_rating($user_id, $round_id, $deviation, $rating) 10 10 { 11 $ret = ''; 12 foreach ($array as $element) { 13 if ($ret) { 14 $ret .= ", "; 15 } 16 $ret .= "'" . db_escape($element) . "'"; 17 } 18 return $ret; 19 } 20 21 // Update a score. 22 // user/task/round can be null. 23 function score_update($name, $user_id, $task_id, $round_id, $value) 24 { 25 log_assert(is_score_name($name), "Bad score name '$name'"); 26 log_assert(is_null($user_id) || is_user_id($user_id), "Bad user id '$user_id'"); 27 log_assert(is_null($task_id) || is_task_id($task_id), "Bad task id '$task_id'"); 28 log_assert(is_null($round_id) || is_round_id($round_id), "Bad round id '$round_id'"); 29 $query = sprintf(" 30 INSERT INTO ia_score (`name`, `score`, `user_id`, `task_id`, `round_id`) 31 VALUES ('%s', %s, %s, %s, %s) ON DUPLICATE KEY UPDATE `score`=%s", 32 db_escape($name), $value, 33 ($user_id === null ? 'NULL' : $user_id), 34 ($task_id === null ? 'NULL' : "'".db_escape($task_id)."'"), 35 ($round_id === null ? 'NULL' : "'".db_escape($round_id)."'"), 36 $value); 37 return db_query($query); 11 $query = "INSERT INTO `ia_rating` (`user_id`, `round_id`, `deviation`, `rating`) 12 VALUES (".implode(',', 13 array(db_quote($user_id), 14 db_quote($round_id), 15 db_quote($deviation), 16 db_quote($rating) 17 )).") ON DUPLICATE KEY UPDATE ". 18 "`rating` = ". $rating .", 19 `deviation` = ".$deviation; 20 db_query($query); 21 return db_affected_rows(); 22 } 23 24 // Updates user's task score 25 function score_update($user_id, $task_id, $round_id, $value) { 26 log_assert(is_user_id($user_id), "Bad user id '$user_id'"); 27 log_assert(is_task_id($task_id), "Bad task id '$task_id'"); 28 log_assert(is_round_id($round_id), "Bad round id '$round_id'"); 29 30 // Update user_id score for task_id at round_id 31 $query = "INSERT INTO `ia_score_user_round_task` (`user_id`, `round_id`, `task_id`, `score`) 32 VALUES (".implode(',', 33 array(db_quote($user_id), 34 db_quote($round_id), 35 db_quote($task_id), 36 db_quote($value) 37 )).") ON DUPLICATE KEY UPDATE 38 `score` = ".db_quote($value); 39 db_query($query); 40 41 // update score for round_id 42 $subquery = "( SELECT SUM(`score`) AS 'score' FROM `ia_score_user_round_task` 43 WHERE 44 `round_id` = ".db_quote($round_id)." && 45 `user_id` = ".db_quote($user_id)." 46 GROUP BY `user_id` )"; 47 48 $query = "INSERT INTO `ia_score_user_round` (`user_id`, `round_id`, `score`) 49 VALUES (".implode(',', 50 array(db_quote($user_id), 51 db_quote($round_id), 52 $subquery 53 )).") ON DUPLICATE KEY UPDATE 54 `score` = ".$subquery; 55 db_query($query); 38 56 } 39 57 … … 86 104 } 87 105 88 // Build a query for a certain score. 89 // Can be used as a subquery. 90 function score_build_query($score, $user, $task, $round) 91 { 92 $cond = score_build_where_clauses($user, $task, $round); 93 $cond[] = "(id = '".db_escape($score['name'])."')"; 94 $query = "SELECT SUM(score) FROM ia_score WHERE " . implode(" AND ", $cond); 95 } 96 97 // Get scores. 98 // $user, $task, $round can be null, string or an array. 99 // If null it's ignored, otherwise only scores for those users/tasks/rounds 100 // are counted. 101 function score_get_range($score_name, $user, $task, $round, $groupby = "user_id", $start = 0, $count = 999999, $with_rankings = false) 102 { 103 log_assert(is_score_name($score_name)); 106 // Count function to be used for score_get_rankings 107 function score_get_count($user, $task, $round) { 104 108 $where = score_build_where_clauses($user, $task, $round); 105 $where[] = sprintf("ia_score.`name` = '%s'", db_escape($score_name));106 $query = sprintf("SELECT107 ia_score.`name` AS `score_name`, `user_id`, `task_id`, `round_id`, SUM(`score`) AS score,108 ia_user.username AS user_name, ia_user.full_name AS user_full,109 ia_user.rating_cache AS user_rating110 FROM ia_score111 LEFT JOIN ia_user ON ia_user.id = ia_score.user_id112 WHERE %s GROUP BY %s113 ORDER BY `score` DESC LIMIT %s, %s",114 join($where, " AND "), $groupby, $start, $count);115 $scores = db_fetch_all($query);116 117 if ($with_rankings && count($scores)) {118 $first_score = $scores[0]['score'];119 120 // We need to count all users with a greater score than the first user121 // in the requested range.122 $where = score_build_where_clauses($user, $task, $round);123 $where[] = sprintf("ia_score.`name` = '%s'", db_escape($score_name));124 $query = sprintf("SELECT SUM(`score`) AS score125 FROM ia_score126 WHERE %s GROUP BY %s127 HAVING score > %s",128 join($where, " AND "), $groupby, $first_score);129 $users_before = db_num_rows(db_query($query));130 131 // store rankings in result132 $scores[0]['ranking'] = $users_before + 1;133 $equal_scores = $start - $users_before + 1;134 for ($i = 1; $i < count($scores); ++$i) {135 $last_row = $scores[$i - 1];136 $row =& $scores[$i];137 if ($row['score'] == $last_row['score']) {138 $row['ranking'] = $last_row['ranking'];139 $equal_scores = $equal_scores + 1;140 }141 else {142 $row['ranking'] = $last_row['ranking'] + $equal_scores;143 $equal_scores = 1;144 }145 }146 }147 148 return $scores;149 }150 151 // Count function for score_get_range152 function score_get_count($score_name, $user, $task, $round, $groupby) {153 log_assert(is_score_name($score_name));154 $where = score_build_where_clauses($user, $task, $round);155 $where[] = sprintf("ia_score.`name` = '%s'", db_escape($score_name));156 if ($user !== null) {157 $join = "LEFT JOIN ia_user ON ia_user.id = ia_score.user_id";158 } else {159 $join = "";160 }161 109 $query = sprintf("SELECT COUNT(DISTINCT user_id) AS `cnt` 162 FROM ia_score $join110 FROM ia_score_user_round 163 111 WHERE %s", 164 join($where, " AND ") , $groupby);112 join($where, " AND ")); 165 113 $res = db_fetch($query); 166 114 return $res['cnt']; 167 }168 169 // Get a score value.170 // Returns 0 or null (if missing).171 function score_get_value($score_name, $user_id, $task_id, $round_id)172 {173 log_assert(is_score_name($score_name));174 log_assert(is_whole_number($user_id));175 log_assert(is_task_id($task_id));176 log_assert(is_user_id($round_id));177 178 $query = sprintf("SELECT score FROM ia_score179 WHERE name = '%s', task_id='%s', round_id='%s', user_id = %s",180 $score_name, $task_id, $round_id, $user_id);181 $res = db_fetch($query);182 return getattr($res, 'score', null);183 115 } 184 116 … … 204 136 205 137 // get user scores 206 $query = sprintf("SELECT * FROM `ia_ score`138 $query = sprintf("SELECT * FROM `ia_rating` 207 139 LEFT JOIN ia_round ON round_id = ia_round.id 208 WHERE `name` IN ('deviation', 'rating') 209 AND ia_score.user_id = '%s' 140 WHERE ia_rating.user_id = '%s' 210 141 AND ia_round.state = 'complete' 211 ", 212 db_escape($user_id)); 213 $rows = db_fetch_all($query); 214 215 // process user scores 142 ", db_escape($user_id)); 143 $rows = db_fetch_all($query); 144 216 145 foreach ($rows as $row) { 217 146 $round_id = $row['round_id']; 218 log_assert(isset($history[$round_id])); 219 220 switch ($row['name']) { 221 case 'rating': 222 $history[$round_id]['rating'] = $row['score']; 223 break; 224 case 'deviation': 225 $history[$round_id]['deviation'] = $row['score']; 226 break; 227 default: 228 log_error("Query returned invalid rating scores"); 229 } 147 $history[$round_id]['rating'] = $row['rating']; 148 $history[$round_id]['deviation'] = $row['deviation']; 230 149 } 231 150 … … 255 174 // ); 256 175 function rating_rounds() { 257 $query = " 258 SELECT 176 $query = "SELECT 259 177 object_id AS round_id, `value` AS `timestamp`, 260 178 ia_round.page_name AS round_page_name, … … 329 247 // FIXME: horrible query 330 248 $query = "SELECT 331 ia_ score.name AS `name`, ia_score.score AS score,332 ia_ score.user_id, ia_score.round_id,249 ia_rating.rating AS `rating`, ia_rating.deviation AS deviation, 250 ia_rating.user_id, ia_rating.round_id, 333 251 pv.`value` AS `timestamp`, ia_user.username 334 FROM ia_ score252 FROM ia_rating 335 253 LEFT JOIN ia_parameter_value AS pv 336 ON pv.object_type = 'round' AND pv.object_id = ia_ score.round_id254 ON pv.object_type = 'round' AND pv.object_id = ia_rating.round_id 337 255 AND pv.parameter_id = 'rating_timestamp' 338 LEFT JOIN ia_user ON ia_user.id = ia_score.user_id 339 WHERE ia_score.name IN ('rating', 'deviation') 340 ORDER BY `timestamp` DESC, ia_score.round_id DESC 256 LEFT JOIN ia_user ON ia_user.id = ia_rating.user_id 257 ORDER BY `timestamp` DESC, ia_rating.round_id DESC 341 258 "; 342 259 $rows = db_fetch_all($query); 343 260 344 // FIXME: We should filter out rounds having rating_update off345 // but these should not have any ratings stored in database anyway...346 347 // parse rows348 261 $users = array(); 349 262 foreach ($rows as $row) { 350 263 $username = $row['username']; 351 $field = $row['name'];352 264 if (isset($users[$username])) { 353 if (isset($users[$username][$field])) { 354 // FIXME: This is currently a hack. 355 // Query shouldn't return more then one rating for each user 356 continue; 357 } 358 else { 359 $users[$username][$field] = $row['score']; 360 } 361 } 362 else { 363 $users[$username] = array( 364 $field => $row['score'], 365 'timestamp' => parameter_decode('rating_timestamp', 366 $row['timestamp']) 367 ); 368 } 265 continue; 266 } 267 268 $users[$username] = array( 269 'rating' => $row['rating'], 270 'deviation' => $row['deviation'], 271 'timestamp' => $row['timestamp']); 369 272 } 370 273 … … 429 332 430 333 $rows[0]['position'] = $users_before + 1; 431 $equal_scores = $start - $users_before + 1; 334 $equal_scores = $start - $users_before + 1; 432 335 for ($i = 1; $i < count($rows); ++$i) { 433 336 $last_row = $rows[$i - 1]; … … 459 362 // Clears ALL user ratings & rating history 460 363 function rating_clear() { 461 db_query("DELETE FROM ia_ score WHERE `name` IN ('rating', 'deviation')");364 db_query("DELETE FROM ia_rating"); 462 365 db_query("UPDATE ia_user SET rating_cache = NULL"); 463 366 } 464 367 368 // Computes rankings for $rounds 369 // returns entries from start to count 370 // if detail_task == true, extra columns for each task will be created 371 // if detail_round == true, extra columns for each round will be created 372 function score_get_rankings($rounds, $tasks, $start = 0, $count = 999999, $detail_task = false, $detail_round = false) 373 { 374 $where = score_build_where_clauses(null, null, $rounds); 375 376 // Get the total score for all rounds 377 $query = "SELECT SUM(score) AS score, user_id, ia_user.username AS user_name, ia_user.full_name AS user_full, 378 ia_user.rating_cache AS user_rating 379 FROM ia_score_user_round 380 LEFT JOIN ia_user ON ia_user.id = ia_score_user_round.user_id 381 WHERE ".implode('AND', $where)." 382 GROUP BY `user_id` 383 ORDER BY score DESC 384 LIMIT ".db_escape($start).", ".db_escape($count); 385 386 $rankings = db_fetch_all($query); 387 if (count($rankings) == 0) { 388 return $rankings; 389 } 390 391 $users = array(); 392 foreach ($rankings as $ranking) { 393 array_push($users, $ranking['user_id']); 394 } 395 396 // Further queries concern only the users that are in this rankings page 397 $filter_users = score_build_where_clauses($users, null, null); 398 $where[] = $filter_users[0]; 399 400 // Detailed scores are mapped in an array with the following form 401 // Array[user_id][object_id] = user score for object 402 // Object can be round or task 403 404 if ($detail_round == true) { 405 // Get scores for each round 406 $query = "SELECT round_id, user_id, score 407 FROM ia_score_user_round 408 WHERE ".implode('AND', $where); 409 $scores = db_fetch_all($query); 410 foreach ($scores as $score) { 411 $user_id = $score['user_id']; 412 $round_id = $score['round_id']; 413 $rscore = $score['score']; 414 $round_scores[$user_id][$round_id] = $rscore; 415 } 416 } 417 418 if ($detail_task == true) { 419 // Get scores for each task 420 $query = "SELECT task_id, user_id, score 421 FROM ia_score_user_round_task 422 WHERE ".implode('AND', $where); 423 $scores = db_fetch_all($query); 424 foreach ($scores as $score) { 425 $user_id = $score['user_id']; 426 $task_id = $score['task_id']; 427 $tscore = $score['score']; 428 $task_scores[$user_id][$task_id] = $tscore; 429 } 430 } 431 432 // Compute rank for the first entry 433 $top_score = $rankings[0]['score']; 434 $where = score_build_where_clauses(null, null, $rounds); 435 $query = "SELECT SUM(score) AS score 436 FROM ia_score_user_round 437 WHERE ".implode(' AND ', $where)." 438 GROUP BY user_id 439 HAVING score > ".db_quote($top_score); 440 $first_rank = db_num_rows(db_query($query)) + 1; 441 442 //create all entries 443 for ($i = 0; $i < count($rankings); $i++) { 444 $user_id = $rankings[$i]['user_id']; 445 446 //task columns 447 if ($detail_task == true) { 448 foreach ($tasks as $task) { 449 $task_id = $task['id']; 450 if (isset($task_scores[$user_id][$task_id])) { 451 $score = $task_scores[$user_id][$task_id]; 452 } else { 453 $score = 0; 454 } 455 $rankings[$i][$task_id] = $score; 456 } 457 } 458 459 //round columns 460 if ($detail_round == true) { 461 foreach ($rounds as $round_id) { 462 if (isset($round_scores[$user_id][$round_id])) { 463 $score = $round_scores[$user_id][$round_id]; 464 } else { 465 $score = 0; 466 } 467 $rankings[$i][$round_id] = $score; 468 } 469 } 470 471 if ($i == 0) { 472 $rankings[$i]['ranking'] = $first_rank; 473 continue; 474 } 475 476 // Users with the same score should be on the same rank 477 if ($rankings[$i]['score'] == $rankings[$i - 1]['score']) { 478 $rankings[$i]['ranking'] = $rankings[$i - 1]['ranking']; 479 } else { 480 $rankings[$i]['ranking'] = $start + $i + 1; 481 } 482 } 483 484 return $rankings; 485 } 486 487 465 488 ?> -
trunk/common/db/task.php
r1095 r1110 66 66 textblock_delete($task["page_name"]); 67 67 68 // Delete all scores received on task 69 db_query("DELETE FROM `ia_score_user_round_task` 70 WHERE `task_id` = " . db_quote($task["id"])); 71 72 // Recompute round scores 73 $query = "SELECT `round_id` FROM `ia_round_task` 74 WHERE `task_id` = ".db_quote($task['id']); 75 $rounds = db_fetch_all($query); 76 77 foreach ($rounds as $round) { 78 round_recompute_score($round['round_id']); 79 } 80 68 81 // Remove task from all rounds 69 82 db_query("DELETE FROM `ia_round_task` 70 WHERE `task_id` = " . db_quote($task["id"]));71 72 // Delete all scores received on task73 db_query("DELETE FROM `ia_score`74 83 WHERE `task_id` = " . db_quote($task["id"])); 75 84 -
trunk/common/db/user.php
r1094 r1110 218 218 } 219 219 elseif ($solved) { 220 $where = 'AND ia_score .score = 100';220 $where = 'AND ia_score_user_round_task.score = 100'; 221 221 } 222 222 elseif ($failed) { 223 $where = 'AND ia_score .score < 100';223 $where = 'AND ia_score_user_round_task.score < 100'; 224 224 } 225 225 else { … … 229 229 230 230 $query = sprintf("SELECT * 231 FROM ia_score 232 LEFT JOIN ia_task ON ia_task.id = ia_score .task_id233 WHERE ia_score .`name` = 'score' AND ia_score.user_id = '%s'234 AND ia_score .round_id = 'arhiva' AND NOT ia_task.id IS NULL %s231 FROM ia_score_user_round_task 232 LEFT JOIN ia_task ON ia_task.id = ia_score_user_round_task.task_id 233 WHERE ia_score_user_round_task.user_id = '%s' 234 AND ia_score_user_round_task.round_id = 'arhiva' AND NOT ia_task.id IS NULL %s 235 235 GROUP BY ia_task.id 236 236 ORDER BY ia_task.`order`", $user_id, $where); … … 243 243 // FIXME: Find a way to remove the hard-coded "<> 'arhiva'" 244 244 $query = "SELECT * 245 FROM ia_score 246 LEFT JOIN ia_round ON ia_round.id = ia_score .round_id247 WHERE ia_score .`name` = 'score' AND ia_score.user_id = '%s'245 FROM ia_score_user_round_task 246 LEFT JOIN ia_round ON ia_round.id = ia_score_user_round_task.round_id 247 WHERE ia_score_user_round_task.user_id = '%s' 248 248 AND NOT ia_round.id IS NULL AND ia_round.id <> 'arhiva' 249 249 GROUP BY ia_round.id"; -
trunk/common/score.php
r1046 r1110 19 19 $rduration = getattr($rparams, 'duration', 10000000) * 60 * 60; 20 20 if ($time >= $rstart && $time <= $rstart + $rduration) { 21 score_update( 'score',$user_id, $task_id, $round['id'], $score);21 score_update($user_id, $task_id, $round['id'], $score); 22 22 } 23 23 } -
trunk/scripts/db-strip
r1100 r1110 94 94 "OR (ia_score.task_id IS NOT NULL AND ia_task.id IS NULL) ". 95 95 "OR (ia_score.round_id IS NOT NULL AND ia_round.id IS NULL)"); 96 97 db_query("DELETE ia_score_user_round_task FROM ia_score_user_round_task ". 98 "LEFT JOIN ia_user on ia_user.id = ia_score_user_round_task.user_id ". 99 "LEFT JOIN ia_task on ia_task.id = ia_score_user_round_task.task_id ". 100 "LEFT JOIN ia_round on ia_round.id = ia_score_user_round_task.round_id ". 101 "WHERE (ia_score_user_round_task.user_id IS NOT NULL AND ia_user.id IS NULL) ". 102 "OR (ia_score_user_round_task.task_id IS NOT NULL AND ia_task.id IS NULL) ". 103 "OR (ia_score_user_round_task.round_id IS NOT NULL AND ia_round.id IS NULL)"); 104 105 // Recompute ia_score_user_round 106 db_query("DELETE FROM ia_score_user_round"); 107 $rounds = db_fetch_all("SELECT id FROM ia_round"); 108 109 foreach ($rounds as $round) { 110 round_recompute_score($round['round_id']); 111 } 112 113 // Delete orphan rating entries 114 db_query("DELETE ia_rating FROM ia_rating ". 115 "LEFT JOIN ia_user on ia_user.id = ia_rating.user_id ". 116 "LEFT JOIN ia_round on ia_round.id = ia_rating.round_id ". 117 "WHERE (ia_rating.user_id IS NOT NULL AND ia_user.id IS NULL) ". 118 "OR (ia_rating.round_id IS NOT NULL AND ia_round.id IS NULL)"); 96 119 97 120 // Delete orphan jobs. -
trunk/scripts/round-statistics
r1001 r1110 61 61 $query = sprintf(' 62 62 SELECT COUNT(DISTINCT(`user_id`)) 63 FROM `ia_score` 64 WHERE `name` = "score" AND 65 `score` > 0 AND 63 FROM `ia_score_user_round` 64 WHERE `score` > 0 AND 66 65 `round_id` LIKE %s', db_quote($round_like)); 67 66 … … 81 80 FROM ( 82 81 SELECT `user_id`, `task_id`, `score` 83 FROM `ia_score` 84 WHERE `name` = "score" AND 85 `round_id` LIKE %s 82 FROM `ia_score_user_round_task` 83 WHERE `round_id` LIKE %s 86 84 GROUP BY `user_id`, `task_id` 87 85 ) AS `grouped_by_tasks` … … 101 99 FROM ( 102 100 SELECT `user_id`, `task_id`, `score` 103 FROM `ia_score` 104 WHERE `name` = "score" AND 105 `round_id` LIKE %s AND 101 FROM `ia_score_user_round_task` 102 WHERE `round_id` LIKE %s AND 106 103 `score` > 0 107 104 GROUP BY `user_id`, `task_id` -
trunk/scripts/task-difficulty
r852 r1110 63 63 $query = <<<SQL 64 64 SELECT COUNT(*) 65 FROM ia_score 65 FROM ia_score_user_round_task 66 66 LEFT JOIN ia_user ON ia_user.id = user_id 67 WHERE rating_cache > 0 AND ia_score.name='score' ANDround_id='arhiva'67 WHERE rating_cache > 0 AND round_id='arhiva' 68 68 AND task_id='%s' 69 69 SQL; … … 80 80 SELECT AVG(rating_cache) FROM ( 81 81 SELECT rating_cache 82 FROM ia_score 82 FROM ia_score_user_round_task 83 83 LEFT JOIN ia_user ON ia_user.id = user_id 84 WHERE rating_cache > 0 AND ia_score.name='score' ANDround_id='arhiva'84 WHERE rating_cache > 0 AND round_id='arhiva' 85 85 AND task_id='%s' 86 86 ORDER BY rating_cache -
trunk/scripts/update-ratings
r1071 r1110 80 80 // read round scores 81 81 log_print("Reading round scores..."); 82 $round_rows = score_get_ran ge("score", null, null, $round_id, "user_id", 0, $usercount);82 $round_rows = score_get_rankings($round_id, null, 0, $usercount); 83 83 log_print(count($round_rows) . " users were scored in this round."); 84 84 foreach ($round_rows as $row) { … … 98 98 $user_id = $row['user_id']; 99 99 // for this round (history) 100 score_update("rating", $user_id, null, $round_id, 101 $users[$username]['rating']); 102 score_update("deviation", $user_id, null, $round_id, 103 $users[$username]['deviation']); 100 score_update_rating($user_id, $round_id, $users[$username]['deviation'], $users[$username]['rating']); 101 104 102 // cache rating 105 103 $user = $ucheck[$username]; -
trunk/www/macros/macro_rankings.php
r992 r1110 10 10 // 11 11 // Arguments: 12 // rounds (required) a | (pipe) separated list of round names. 12 // rounds (required) a | (pipe) separated list of round_id : round_name. 13 // Round name is the name which will appear in the column dedicated 14 // to that round in case detail_round == true 15 // If detail_round == false you can leave just the round_id (see examples) 13 16 // count (optional) how many to display at once, defaults to infinity 17 // detail_task (optional) true/false print score columns for each task 18 // detail_round (optional) true/false print score columns for each round 19 // Examples: 20 // Macro: Rankings(rounds="preONI2007/1/a | preONI2007/2/a" count = "10") 21 // Columns: | pos | name | score | 14 22 // 15 // Examples: 16 // Rankings(rounds="preONI2007/1/a | preONI2007/2/a") 17 // Rankings(rounds="preONI2007/1/a | preONI2007/2/a" count="10") 23 // Rankings(rounds="preONI2007/1/a : round 1 | preONI2007/2/a : round 2" detail_round = "true") 24 // Columns: | pos | name | round 1 | round 2 | total | 25 // 26 // Rankings(rounds="preONI2007/1/a : round 1 | preONI2007/2/a : round 2" detail_round = "true" detail_task = "true") 27 // Columns: | pos | name | task | task | task | round 1 | task | task | task | round 2 | total | 18 28 function macro_rankings($args) { 19 29 $args['param_prefix'] = 'rankings_'; … … 21 31 $args['display_entries'] = $args['count']; 22 32 } 33 34 // Detail parameters 35 $detail_round = getattr($args, 'detail_round', 'false'); 36 $detail_task = getattr($args, 'detail_task', 'false'); 37 38 $detail_round = ($detail_round == 'true'); 39 $detail_task = ($detail_task == 'true'); 40 41 // Paginator options 23 42 $options = pager_init_options($args); 24 43 $options['show_count'] = true; … … 29 48 return macro_error("Parameters 'rounds' is required."); 30 49 } 31 $rounds = preg_split('/\s*\|\s*/', $roundstr); 50 $round_param = preg_split('/\s*\|\s*/', $roundstr); 51 $rounds = array(); 52 foreach ($round_param as $param) { 53 $round = preg_split('/\s*\:\s*/', $param); 54 array_push($rounds, array( 55 'round_id' => $round[0], 56 'round_name' => getattr($round, 1, ' ') 57 ) 58 ); 59 } 32 60 33 // FIXME: user / task parameters. 34 $rankings = score_get_range("score", null, null, $rounds, "user_id", $options['first_entry'], $options['display_entries'], true); 61 // Generating Table 35 62 36 63 $column_infos = array( … … 47 74 'return format_user_normal($row["user_name"], $row["user_full"], $row["user_rating"]);'), 48 75 ), 49 array( 50 'title' => 'Scor', 76 ); 77 78 $columns = array(); 79 $tasks = array(); 80 if ($detail_round == true || $detail_task == true) { 81 foreach ($rounds as $round) { 82 $round_id = $round['round_id']; 83 84 if ($detail_task == true) { 85 $new_tasks = round_get_tasks($round_id, null, null, null, null, null); 86 foreach ($new_tasks as $task) { 87 array_push($columns, array( 88 'name' => $task['id'], 89 'type' => 'task', 90 'title' => $task['title'] 91 )); 92 array_push($tasks, $task); 93 } 94 } 95 96 if ($detail_round == true) { 97 array_push($columns, array( 98 'name' => $round['round_id'], 99 'type' => 'round', 100 'title' => $round['round_name'] 101 )); 102 } 103 } 104 } 105 106 foreach ($columns as $column) { 107 array_push($column_infos, array( 108 'title' => $column['title'], 109 'key' => $column['name'], 110 'rowform' => create_function_cached('$row', 'return round($row[\''.$column['name'].'\']);'), 111 'css_class' => 'number score' 112 )); 113 } 114 115 $total = 'Scor'; 116 if ($detail_round == true || $detail_task == true) { 117 $total = 'Total'; 118 } 119 array_push($column_infos, array( 120 'title' => $total, 51 121 'key' => 'score', 52 122 'rowform' => create_function_cached('$row', 'return round($row[\'score\']);'), 53 123 'css_class' => 'number score' 54 ) ,124 ) 55 125 ); 56 126 127 $round_ids = array(); 128 foreach ($rounds as $round) { 129 array_push($round_ids, $round['round_id']); 130 } 131 $rankings = score_get_rankings($round_ids, $tasks, $options['first_entry'], $options['display_entries'], $detail_task, $detail_round); 132 57 133 if (pager_needs_total_entries($options)) { 58 $options['total_entries'] = score_get_count( "score", null, null, $rounds, 'user_id');134 $options['total_entries'] = score_get_count(null, null, $round_ids); 59 135 } 60 136 -
trunk/www/macros/macro_tasks.php
r1099 r1110 131 131 $options['first_entry'], 132 132 $options['display_entries'], 133 $user_id, ($scores ? 'score' : null),133 $user_id, $scores, 134 134 $filter); 135 135 $options['total_entries'] = round_get_task_count( 136 $round_id, $user_id, ($scores ? 'score' : null),$filter);136 $round_id, $user_id, $filter); 137 137 $options['row_style'] = 'task_row_style'; 138 138 $options['css_class'] = 'tasks';
Note: See TracChangeset
for help on using the changeset viewer.
