Symfony Profiler

<?php
namespace App\BackendBundle\Helper;
use DateTime;
use Doctrine\DBAL\Connection;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query;
use App\BackendBundle\Helper\MqttHelper;
use App\Entity\Game;
use App\Entity\GameCategory;
use App\Entity\GameSchoolClassHighscore;
use App\Entity\GameType;
use App\Entity\SchoolClass;
use App\Entity\SchoolClassGameHistory;
class GamePointsHelper {
private Connection $connection;
private EntityManagerInterface $em;
private MqttHelper $mqttHelper;
public function __construct(EntityManagerInterface $em, MqttHelper $mqttHelper) {
$this->em = $em;
$this->connection = $this->em->getConnection();
$this->mqttHelper = $mqttHelper;
}
public function getGameSchoolClassHighscore(SchoolClass $schoolClass, GameType $gameType, GameCategory $gameCategory) {
$highscore = $this->em->getRepository(GameSchoolClassHighscore::class)->findOneBy(array(
'schoolClass' => $schoolClass, 'gameType' => $gameType, 'gameCategory' => $gameCategory
));
if (!empty($highscore)) {
return $highscore;
}
return $this->createGameSchoolClassHighscore($schoolClass, $gameType, $gameCategory);
}
public function createGameSchoolClassHighscore(SchoolClass $schoolClass, GameType $gameType, GameCategory $gameCategory) {
$gameSchoolClassHighscore = new GameSchoolClassHighscore();
$gameSchoolClassHighscore->setSchoolClass($schoolClass);
$gameSchoolClassHighscore->setGameType($gameType);
$gameSchoolClassHighscore->setGameCategory($gameCategory);
$gameSchoolClassHighscore->setRoundNumber(0);
$gameSchoolClassHighscore->setPoints(0);
$gameSchoolClassHighscore->setUpdateAt(new DateTime());
$this->em->persist($gameSchoolClassHighscore);
$this->em->flush();
return $gameSchoolClassHighscore;
}
public function getClickThePicPointsForGame(Game $game) {
$gameID = $game->getId();
$sql = "SELECT sum(points) as points "
. "FROM game_ctp_question as ctpq "
. "WHERE game_id=$gameID AND answer_correct=1;";
$results = $this->connection->query($sql)->fetchAllAssociative();
if (empty($results)) {
return 0;
}
return intval($results[0]['points']);
}
public function getClickThePicRoundsCorrectForGame(Game $game) {
$gameID = $game->getId();
$sql = "SELECT count(*) as rounds "
. "FROM game_ctp_question as ctpq "
. "WHERE game_id=$gameID AND answer_correct=1;";
$results = $this->connection->query($sql)->fetchAllAssociative();
if (empty($results)) {
return 0;
}
return intval($results[0]['rounds']);
}
public function getQuizRoundPoints($roundNumber) {
switch ($roundNumber) {
case 1: return 5;
case 2: return 7;
case 3: return 10;
case 4: return 15;
case 5: return 30;
case 6: return 40;
case 7: return 55;
case 8: return 70;
case 9: return 150;
case 10: return 200;
case 11: return 300;
case 12: return 800;
default: return 0;
}
return 0;
}
public function getSchoolClassGamePoints(SchoolClass $schoolClass, DateTime $end = null) {
$pointsSum = 0;
$start = null;
/* @var $schoolClassGameHistoryLast SchoolClassGameHistory */
$schoolClassGameHistoryLast = $this->getSchoolClassGameHistoryLast($schoolClass);
if (!empty($schoolClassGameHistoryLast)) {
$start = $schoolClassGameHistoryLast->getCreatedAt();
$pointsSum += intval($schoolClassGameHistoryLast->getPoints());
}
$pointsGames = $this->getSchoolClassGamePointsDate($schoolClass, $start, $end);
$pointsSum += $pointsGames;
return $pointsSum;
}
private function getSchoolClassGameHistoryLast(SchoolClass $schoolClass) {
$schoolClassID = $schoolClass->getId();
$query = "SELECT scgh "
. "FROM App\Entity\SchoolClassGameHistory scgh "
. "LEFT JOIN scgh.schoolClass sc "
. "WHERE sc.id = :scid";
/* @var $dqlQuery Query */
$dqlQuery = $this->em->createQuery($query);
$dqlQuery->setParameter('scid', $schoolClassID);
$dqlQuery->setMaxResults(1);
$dqlResults = $dqlQuery->execute();
if (count($dqlResults) > 0) {
return $dqlResults[0];
}
return null;
}
private function getSchoolClassGamePointsDate(SchoolClass $schoolClass, DateTime $start = null, DateTime $end = null) {
$schoolClassID = $schoolClass->getId();
$strWhere = $this->getSchoolClassGamePointsWhere($start, $end);
$sql = "SELECT scc.school_class_id as scid, sum(g.points) as points "
. "FROM game as g "
. "LEFT JOIN game_state as gs ON g.game_state_id=gs.id "
. "LEFT JOIN school_class_client as scc ON g.school_class_client_id=scc.id "
. "WHERE scc.school_class_id=$schoolClassID AND gs.id IN (2,3,4) $strWhere"
. "GROUP BY scc.school_class_id;";
$results = $this->connection->query($sql)->fetchAllAssociative();
if (count($results) > 0) {
return $results[0]['points'];
}
return 0;
}
public function getSchoolClassesGamePoints(DateTime $start = null, DateTime $end = null) {
$strWhere = $this->getSchoolClassGamePointsWhere($start, $end);
$sql = "SELECT scc.school_class_id as scid, sum(g.points) as points "
. "FROM game as g "
. "LEFT JOIN game_state as gs ON g.game_state_id=gs.id "
. "LEFT JOIN school_class_client as scc ON g.school_class_client_id=scc.id "
. "WHERE gs.id IN (2,3,4) $strWhere"
. "GROUP BY scc.school_class_id "
. "ORDER BY points DESC;";
$results = $this->connection->query($sql)->fetchAllAssociative();
return $results;
}
private function getSchoolClassGamePointsWhere(DateTime $start = null, DateTime $end = null) {
$strWhere = '';
if (!empty($start)) {
$strStart = $start->format('Y-m-d H:i:s');
$strWhere = " AND '$strStart' <= g.created_at ";
}
if (empty($end)) {
$end = new DateTime();
$end->setTime(23, 59);
$strEnd = $end->format('Y-m-d H:i:s');
$strWhere = " AND g.created_at <= '$strEnd' ";
} else {
$strEnd = $end->format('Y-m-d H:i:s');
$strWhere = " AND g.created_at <= '$strEnd' ";
}
return $strWhere;
}
public function updateGameSchoolClassHighscore(Game $game, $roundNumber, $points) {
/* @var $schoolClass SchoolClass */
$schoolClass = $game->getSchoolClassClient()->getSchoolClass();
$gameType = $game->getGameType();
$gameCategory = $game->getCategory();
$gameSchoolClassHighscore = $this->em->getRepository(GameSchoolClassHighscore::class)->findOneBy(array(
'schoolClass' => $schoolClass,
'gameType' => $gameType,
'gameCategory' => $gameCategory
));
if (empty($gameSchoolClassHighscore)) {
$gameSchoolClassHighscore = $this->createGameSchoolClassHighscore($schoolClass, $gameType, $gameCategory);
}
if ($this->checkUpdateHighscore($gameSchoolClassHighscore, $roundNumber, $points)) {
$gameSchoolClassHighscore->setRoundNumber($roundNumber);
$gameSchoolClassHighscore->setPoints($points);
$this->em->persist($gameSchoolClassHighscore);
$this->em->flush();
}
}
private function checkUpdateHighscore(GameSchoolClassHighscore $gameSchoolClassHighscore, $roundNumber, $points) {
$currentRound = $gameSchoolClassHighscore->getRoundNumber();
if ($roundNumber < $currentRound) {
return false;
}
$currentPoints = $gameSchoolClassHighscore->getPoints();
if ($points < $currentPoints) {
return false;
}
return true;
}
public function publishSchoolClassPoints(Game $game) {
/* @var $schoolClass SchoolClass */
$schoolClass = $game->getSchoolClassClient()->getSchoolClass();
$schoolClassPoints = $this->getSchoolClassGamePoints($schoolClass);
$schoolClassID = $schoolClass->getId();
$this->mqttHelper->sendSchoolClassPoints($schoolClassID, $schoolClassPoints);
}
}

src/BackendBundle/Helper/GamePointsHelper.php line 23