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.'); }