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();