static function updateRanking() { $qpru = QuizPendingRankingUpdate::fetchAll(); if( $qpru ) { $benchMessage = $benchSQLStart = $benchSQLEnd = $benchCalculusStart = $benchCalculusEnd = $benchTotalStart = $benchTotalEnd = null; // We only update one quiz application's leaderboard on each // cronjob pass even if multiple leaderboards are awaiting update // to limit SQL queries $lowestRanking = (int)$qpru[0]['lowest_global_ranking']; $clusterIdentifier = $qpru[0]['cluster_identifier']; $applicationId = (int)$qpru[0]['application_id']; $application = CacheApplicationTool::buildLocalizedApplicationByApplication($applicationId); if( is_numeric( $lowestRanking ) && $lowestRanking != null && is_numeric( $applicationId ) && $applicationId != null ) { $benchTotalStart = getrusage(); $db = eZDB::instance(); $db->begin(); $results = $db->arrayQuery( sprintf(" SELECT uuid, user_specialty, score, global_ranking, specialty_ranking FROM %s WHERE global_ranking <= %d AND cluster_identifier = '%s' AND application_id = %d AND ( nb_correct > 0 OR nb_wrong > 0 ) ORDER BY score DESC", self::SQL_TABLENAME, $lowestRanking, $db->escapeString( $clusterIdentifier ), $applicationId ) ); $updateCount = 0; if( $results && count( $results ) > 1 ) { $benchCalculusStart = getrusage(); $updateResult = null; $spe = $changed = $res = array(); $r = $_r = 1; foreach( $results as $rank => $row ) { if( (int)$row['global_ranking'] != $r ) { $changed[] = $row['uuid']; $results[$rank]['global_ranking'] = $r; } if( isset( $results[$rank + 1] ) && ( (int)$results[$rank + 1]['score'] != (int)$row['score'] ) ) $r++; $spe[$row['user_specialty']][] = $results[$rank]; $res[$row['uuid']] = $rank; } foreach( $spe as $row ) { $_r = 1; if( count( $row ) > 1 ) { foreach( $row as $_rank => $_row ) { if( (int)$_row['specialty_ranking'] != $_r ) { if( !in_array( $_row['uuid'], $changed ) ) $changed[] = $_row['uuid']; $results[$res[$_row['uuid']]]['specialty_ranking'] = $_r; } if( isset( $row[$_rank + 1] ) ) { if( (int)$row[$_rank + 1]['score'] != (int)$_row['score'] ) $_r++; } } } else { if( (int)$row[0]['specialty_ranking'] != $_r ) { if( !in_array( $row[0]['uuid'], $changed ) ) $changed[] = $row[0]['uuid']; $results[$res[$row[0]['uuid']]]['specialty_ranking'] = $_r; } } } $benchCalculusEnd = getrusage(); if( count( $changed ) > 0 ) { $benchSQLStart = getrusage(); foreach( $changed as $uuid ) { $updateResult = $db->query( sprintf(" UPDATE %s SET global_ranking = %d, specialty_ranking = %d WHERE uuid = '%s' AND cluster_identifier = '%s' AND application_id = %d", self::SQL_TABLENAME, $results[$res[$uuid]]['global_ranking'], $results[$res[$uuid]]['specialty_ranking'], $uuid, $db->escapeString( $clusterIdentifier ), $applicationId ) ); } $benchSQLEnd = getrusage(); } $updateCount = count( $changed ); $benchTotalEnd = getrusage(); if( $updateCount > 0 ) $benchMessage = 'Total: ' . self::benchmarckTime( $benchTotalEnd, $benchTotalStart ) . 'ms (Calculus: ' . self::benchmarckTime( $benchCalculusEnd, $benchCalculusStart ) . 'ms, SQL: ' . self::benchmarckTime( $benchSQLEnd, $benchSQLStart ) . 'ms)'; } else { if( $results && count( $results ) == 1 ) { $updateResult = $db->query( sprintf(" UPDATE %s SET global_ranking = 1, specialty_ranking = 1 WHERE uuid = '%s' AND cluster_identifier = '%s' AND application_id = %d", self::SQL_TABLENAME, $results[0]['uuid'], $db->escapeString( $clusterIdentifier ), $applicationId ) ); $updateCount = 1; } } $db->commit(); } // Once leaderboard update is done, we delete the corresponding PendingRankingUpdate row in DB QuizPendingRankingUpdate::removeById( (int)$qpru[0]['id'] ); // If we updated a user with a lower global ranking than the one used for the recalculation above, that means we // had users sharing the same global ranking. We need to schedule yet another recalculation with the lowest ranking available if( $r != $lowestRanking ) { ClusterTool::setCurrentCluster( $clusterIdentifier ); $lowestGlobalRanking = QuizPlayerScoring::fetchBy( array( 'cluster_identifier' => $clusterIdentifier, 'application_id' => $applicationId ), array( 'global_ranking' => 'desc' ), array( 'length' => 1 ) ); QuizPendingRankingUpdate::add( $lowestGlobalRanking[0]->attribute('global_ranking'), $applicationId ); } return array( 'updateCount' => $updateCount, 'updateResult' => $updateResult, 'clusterIdentifier' => $clusterIdentifier, 'applicationId' => $applicationId, 'benchmark' => $benchMessage ); } return null; }