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;
}
'debug-output' => true,
) );
$options = $script->getOptions( "[clusterIdentifier:][countryCode:]", "", array(
'clusterIdentifier' => 'Cluster identifier',
'countryCode' => 'Country code'
) );
$script->startup();
$script->initialize();
if( $options['clusterIdentifier'] && $options['countryCode'] )
{
$updated = 0;
$lowestRankingsPerQuizApp = array();
$playerScoringRows = QuizPlayerScoring::fetchBy( array( 'cluster_identifier' => array( array( $options['clusterIdentifier'] ) ) ) );
if( $playerScoringRows )
{
ClusterTool::setCurrentCluster( $options['clusterIdentifier'] );
$us = FacetFilteringTool::getTaxoTranslationWithIDs( 'user_specialty' );
foreach( $playerScoringRows as $playerScoring )
{
$user = MMUsers::fetchByIdAndCountry( $playerScoring->attribute( 'uuid' ), $options['countryCode'] );
if( $user )
{
if( isset( $us[$user->attribute( 'user_speciality' )] ) && $us[$user->attribute( 'user_speciality' )]['id'] != $playerScoring->attribute( 'user_specialty' ) )
{
if( !isset( $lowestRankingsPerQuizApp[$playerScoring->attribute( 'application_id' )] ) || ( isset( $lowestRankingsPerQuizApp[$playerScoring->attribute( 'application_id' )] ) && $lowestRankingsPerQuizApp[$playerScoring->attribute( 'application_id' )] < (int)$playerScoring->attribute( 'global_ranking' ) ) )
$lowestRankingsPerQuizApp[$playerScoring->attribute( 'application_id' )] = (int)$playerScoring->attribute( 'global_ranking' );
$oldUS = $playerScoring->attribute( 'user_specialty' );
$playerScoring->setAttribute( 'user_specialty', $us[$user->attribute( 'user_speciality' )]['id'] );
if (is_null($lastGlobalRanking))
{
$lastGlobalRanking = $r[0];
continue;
}
if (count(array_unique($r)) > 1 || $r[0] <= $lastGlobalRanking)
{
$shouldRecalculate = true;
break;
}
$lastGlobalRanking = $r[0];
}
if ($shouldRecalculate)
{
$lowestGlobalranking = QuizPlayerScoring::fetchBy(
array('application_id' => $applicationId),
array('global_ranking' => 'desc'),
array('length' => 1)
);
QuizPendingRankingUpdate::add(
(int)$lowestGlobalranking[0]->attribute('global_ranking'),
$applicationId
);
$message = "Scheduled a complete ranking update for application with id #$applicationId ($clusterIdentifier)";
$cli->output($message);
eZLog::write($message, 'quiz.log');
}
}
}
}
/**
* @return array
*/
protected function getPlayerRanking()
{
if( $this->user() )
{
$playerData = QuizPlayerScoring::fetchCurrentUserRow( $this->applicationObject->attribute( 'id' ), true, $this->isLocalQuiz() );
if( $playerData )
{
$totalPlayers = QuizPlayerScoring::fetchBy(
array( 'application_id' => $this->applicationObject->attribute( 'id' ) ),
array( 'global_ranking' => 'desc' ),
1
);
if( $totalPlayers > 0 && ( $totalPlayers[0]->attribute( 'nb_correct' ) > 0 || $totalPlayers[0]->attribute( 'nb_wrong' ) > 0 ) )
{
return array(
'result' => array(
'playerGlobalRanking' => $playerData->attribute( 'global_ranking' ),
'totalPlayers' => $totalPlayers[0]->attribute( 'global_ranking' )
)
);
}
}
}
return array( 'result' => null );
}
$countUS[$us]
);
$score++;
$db->query( $query );
}
QuizPlayerScoring::scheduleRankingUpdate( $gr, $options['quizApplicationId'] );
$cli->output( "Done, added " . $options['nbPlayer'] . " test players in quiz player scoring table" );
}
elseif( $options['empty'] && $options['clusterIdentifier'] && $options['quizApplicationId'] )
{
ClusterTool::setCurrentCluster( $options['clusterIdentifier'] );
$db->query( sprintf( "DELETE FROM %s WHERE uuid LIKE 'qzbench%%'", QuizPlayerScoring::SQL_TABLENAME ) );
$lowestGlobalScore = QuizPlayerScoring::fetchBy( array( 'application_id' => $options['quizApplicationId'] ), array( 'global_ranking' => 'desc' ), array( 'length' => 1 ) );
if( $lowestGlobalScore )
$gr = $lowestGlobalScore[0]->attribute( 'score' ) == 0 ? $lowestGlobalScore[0]->attribute( 'global_ranking' ) : ( $lowestGlobalScore[0]->attribute( 'global_ranking' ) + 1 );
else
$gr = 1;
QuizPlayerScoring::scheduleRankingUpdate( $gr, $options['quizApplicationId'] );
$cli->output( "Quiz player scoring table has been emptied of its test players" );
}
else
{
$cli->output( "Please specify either the --clusterIdentifier/--quizApplicationId/--nbPlayer parameters or the --clusterIdentifier/--quizApplicationId/--empty parameters" );
}
$script->shutdown();
