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;
}
