public function recalcFeedbackPoints(User $user, Scholarship $scholarship, $month)
{
$em = $this->getEntityManager();
$conn = $em->getConnection();
/** @var EGPlayerStatsRepository $statsRepo */
$statsRepo = $em->getRepository('GotChosenSiteBundle:EGPlayerStats');
// grab all the rated games with their highest ratings
$stmt = $conn->prepare('SELECT f.game_id, MAX(f.developerRating) max_rating
FROM EGFeedback f
WHERE f.user_id = :user
AND f.createdDate >= :start
AND f.createdDate < :end
AND f.ratedDate IS NOT NULL
GROUP BY f.game_id
ORDER BY max_rating DESC');
$stmt->bindValue('user', $user->getId(), \PDO::PARAM_INT);
$stmt->bindValue('start', $month . '-01 00:00:00', \PDO::PARAM_STR);
$stmt->bindValue('end', Dates::nextMonth($month) . '-01 00:00:00', \PDO::PARAM_STR);
$stmt->execute();
$totalFeedback = 0;
$count = 0;
while ($row = $stmt->fetch()) {
$totalFeedback += $row['max_rating'];
$count++;
// stop at 100 feedback or 20 games
if ($totalFeedback >= 100 || $count >= 20) {
break;
}
}
$totalFeedback = min($totalFeedback, 100);
$pstats = $statsRepo->getOrCreate($user, $scholarship, $month);
$pstats->setFeedbackPoints($totalFeedback);
$pstats->updateTotalPoints();
$em->flush();
}
/**
* Retrieves play sessions for the given user, optionally filtered by month.
*
* @param User $player
* @param null|string $month In the form of YYYY-MM
* @return array
*/
public function findPlaySessions(User $player, $month = null)
{
$q = $this->getEntityManager()->createQueryBuilder();
$q->select('ps', 'g')->from('GotChosenSiteBundle:EGPlaySession', 'ps')->join('ps.game', 'g')->where('ps.player = :user')->setParameter('user', $player->getId())->andWhere('ps.isCompleted = 1');
if ($month && preg_match('/^\\d{4}-\\d{2}$/', $month)) {
$q->andWhere('ps.endDate >= :start')->setParameter('start', $month . '-01 00:00:00')->andWhere('ps.endDate < :end')->setParameter('end', Dates::nextMonth($month) . '-01 00:00:00');
}
$q->orderBy('ps.endDate');
return $q->getQuery()->getResult();
}
protected function execute(InputInterface $input, OutputInterface $output)
{
/** @var ObjectManager $em */
$em = $this->getContainer()->get('doctrine')->getManager();
/** @var EGPlayerStatsRepository $statsRepo */
$statsRepo = $em->getRepository('GotChosenSiteBundle:EGPlayerStats');
//$month = '2014-01';
$month = date('Y-m');
$statsRepo->buildChampionshipRanking($month);
if (date('j') == '1') {
$statsRepo->buildChampionshipRanking(Dates::prevMonth($month));
}
}
/**
* @return array
*
* @Route("/evolution-games/wall-of-fame", name="eg_wall")
* @Template
*/
public function wallAction()
{
/** @var EGGameRepository $gameRepo */
$gameRepo = $this->repo('EGGame');
/** @var EGPlayerStatsRepository $pstatsRepo */
$pstatsRepo = $this->repo('EGPlayerStats');
$scholarship = $this->repo('Scholarship')->getCurrentEvoGames();
$prevScholarship = $this->repo('Scholarship')->getEvoGamesByDate(date_create('-1 month'));
$prev2Scholarship = $this->repo('Scholarship')->getEvoGamesByDate(date_create('-2 month'));
$curMonth = date('Y-m');
$prevMonth = Dates::prevMonth($curMonth);
$prev2Month = Dates::prevMonth($prevMonth);
$currentQualifier = $gameRepo->findQualifierGamesByRank($scholarship, $curMonth);
$currentDev = $gameRepo->findContestGamesByRank($scholarship, $curMonth);
$currentChamps = $pstatsRepo->getChampionshipLeaders($scholarship, $curMonth, 10);
// cache these later, they don't really change
if ($prevScholarship) {
$prevQualifier = $gameRepo->findQualifierGamesByRank($prevScholarship, $prevMonth);
$prevDev = $gameRepo->findContestGamesByRank($prevScholarship, $prevMonth, 2);
$prevChamps = $pstatsRepo->getChampionshipLeaders($prevScholarship, $prevMonth, 1);
} else {
$prevQualifier = $prevDev = $prevChamps = [];
}
if ($prev2Scholarship) {
$prev2Qualifier = $gameRepo->findQualifierGamesByRank($prev2Scholarship, $prev2Month);
$prev2Dev = $gameRepo->findContestGamesByRank($prev2Scholarship, $prev2Month, 2);
$prev2Champs = $pstatsRepo->getChampionshipLeaders($prev2Scholarship, $prev2Month, 1);
} else {
$prev2Qualifier = $prev2Dev = $prev2Champs = [];
}
$users = [];
foreach ($currentChamps as $u) {
$users[] = $u->getPlayer();
}
foreach ($prevChamps as $u) {
$users[] = $u->getPlayer();
}
foreach ($prev2Champs as $u) {
$users[] = $u->getPlayer();
}
$this->repo('User')->precachePropertiesMulti($users, ['PhotoURL']);
return ['currentQualifier' => $currentQualifier, 'currentDev' => $currentDev, 'currentChamps' => $currentChamps, 'prevMonth' => $prevMonth, 'prevQualifier' => $prevQualifier, 'prevDev' => $prevDev, 'prevChamps' => $prevChamps, 'prev2Month' => $prev2Month, 'prev2Qualifier' => $prev2Qualifier, 'prev2Dev' => $prev2Dev, 'prev2Champs' => $prev2Champs];
}
public function buildDevContestRanking($month)
{
/*
* - Plays
* - Feedbacks Rated
* - Qualifier Position
*/
$this->assertMonth($month);
$qualifierMonth = Dates::prevMonth($month);
$em = $this->getEntityManager();
$egScholarship = $em->getRepository('GotChosenSiteBundle:Scholarship')->getCurrentEvoGames();
$scholarshipId = $egScholarship ? $egScholarship->getId() : 0;
$conn = $em->getConnection();
// commenting this out and sorting in PHP, might be possible to use pure MySQL, but confusing.
// would need to make sure the previous data we're selecting is not part of a scholarship and so on.
// possibly remove IFNULL and change sprev LEFT JOIN to normal JOIN to fully exclude
/*$stmt = $conn->prepare(
'SELECT s.id, IFNULL(sprev.rank, 10000) previous_rank FROM EGGameStats s
JOIN game_scholarships gs ON (gs.game_id = s.game_id)
LEFT JOIN EGGameStats sprev ON
(sprev.game_id = s.game_id AND sprev.statsMonth = :prevMonth)
WHERE s.statsMonth = :month AND gs.scholarship_id = :sship AND gs.scholarship_type = :stype
ORDER BY s.monthPlays DESC, s.monthRatedFeedbacks DESC, previous_rank ASC'
);*/
$stmt = $conn->prepare('SELECT s.id, s.game_id, s.monthPlays, s.monthRatedFeedbacks
FROM EGGameStats s
JOIN game_scholarships gs ON (gs.game_id = s.game_id)
WHERE s.statsMonth = :month AND gs.scholarship_id = :sship AND gs.scholarshipType = :stype');
$stmt->bindValue('month', $month, \PDO::PARAM_STR);
//$stmt->bindValue('prevMonth', $qualifierMonth, \PDO::PARAM_STR);
$stmt->bindValue('sship', $scholarshipId, \PDO::PARAM_INT);
$stmt->bindValue('stype', EGGameScholarships::TYPE_CONTEST, \PDO::PARAM_STR);
$stmt->execute();
$values = $stmt->fetchAll();
// collect the ranking for the previous month, so that can be used if necessary
/*$stmt = $conn->prepare(
'SELECT s.game_id FROM EGGameStats s
LEFT JOIN game_scholarships gs ON (gs.game_id = s.game_id)
WHERE s.statsMonth = :month AND (gs.scholarship_id IS NULL OR gs.scholarship_id != :sship)
ORDER BY s.monthVotes DESC, s.monthPlays DESC, s.monthRatedFeedbacks DESC'
);*/
$stmt = $conn->prepare('SELECT s.game_id FROM EGGameStats s
WHERE s.statsMonth = :month
ORDER BY s.monthVotes DESC, s.monthPlays DESC, s.monthRatedFeedbacks DESC');
$stmt->bindValue('month', $qualifierMonth, \PDO::PARAM_STR);
//$stmt->bindValue('sship', $scholarshipId, \PDO::PARAM_INT);
$stmt->execute();
$qualValues = [];
$rank = 0;
while ($row = $stmt->fetch()) {
$qualValues[$row['game_id']] = ++$rank;
}
// sort by monthPlays DESC, monthRatedFeedbacks DESC, and qualifier rank ASC
usort($values, function ($a, $b) use($qualValues) {
if ($a['monthPlays'] != $b['monthPlays']) {
return $b['monthPlays'] - $a['monthPlays'];
// DESC
}
if ($a['monthRatedFeedbacks'] != $b['monthRatedFeedbacks']) {
return $b['monthRatedFeedbacks'] - $a['monthRatedFeedbacks'];
// DESC
}
if (!isset($qualValues[$a['game_id']])) {
$qualValues[$a['game_id']] = 100000;
}
if (!isset($qualValues[$b['game_id']])) {
$qualValues[$b['game_id']] = 100000;
}
return $qualValues[$a['game_id']] - $qualValues[$b['game_id']];
});
$rank = 0;
$mapping = [];
foreach ($values as $stats) {
$mapping[$stats['id']] = ++$rank;
}
$this->updateRankings($mapping);
}
protected function configure()
{
$this->setName('gotchosen:new-eg-contest')->addOption('round', 'r', InputOption::VALUE_REQUIRED, 'Contest round #')->addOption('month', 'm', InputOption::VALUE_OPTIONAL, 'Month to pull qualifier games from, defined as yyyy-MM, defaults to previous month', Dates::prevMonth('now'))->setDescription('Creates a new Evolution Games contest, seeding it with the top 10 qualifier games.');
}
