src/BackendBundle/Helper/GamePointsHelper.php line 26

Open in your IDE?
  1. <?php
  3. namespace App\BackendBundle\Helper;
  5. use DateTime;
  6. use Doctrine\DBAL\Connection;
  7. use Doctrine\ORM\EntityManagerInterface;
  8. use Doctrine\ORM\Query;
  9. use App\BackendBundle\Helper\MqttHelper;
  10. use App\Entity\Game;
  11. use App\Entity\GameCategory;
  12. use App\Entity\GameSchoolClassHighscore;
  13. use App\Entity\GameType;
  14. use App\Entity\SchoolClass;
  15. use App\Entity\SchoolClassGameHistory;
  16. use App\Entity\SchoolClassGameHistoryBonus;
  18. class GamePointsHelper {
  20.     private Connection $connection;
  21.     private EntityManagerInterface $em;
  22.     private MqttHelper $mqttHelper;
  24.     public function __construct(EntityManagerInterface $em, MqttHelper $mqttHelper) {
  25.         $this->em = $em;
  26.         $this->connection = $this->em->getConnection();
  27.         $this->mqttHelper = $mqttHelper;
  28.     }
  30.     public function getGameSchoolClassHighscore(SchoolClass $schoolClass, GameType $gameType, GameCategory $gameCategory) {
  31.         $highscore = $this->em->getRepository(GameSchoolClassHighscore::class)->findOneBy(array(
  32.             'schoolClass' => $schoolClass, 'gameType' => $gameType, 'gameCategory' => $gameCategory
  33.         ));
  35.         if (!empty($highscore)) {
  36.             return $highscore;
  37.         }
  39.         return $this->createGameSchoolClassHighscore($schoolClass, $gameType, $gameCategory);
  40.     }
  42.     public function createGameSchoolClassHighscore(SchoolClass $schoolClass, GameType $gameType, GameCategory $gameCategory) {
  43.         $gameSchoolClassHighscore = new GameSchoolClassHighscore();
  44.         $gameSchoolClassHighscore->setSchoolClass($schoolClass);
  45.         $gameSchoolClassHighscore->setGameType($gameType);
  46.         $gameSchoolClassHighscore->setGameCategory($gameCategory);
  47.         $gameSchoolClassHighscore->setRoundNumber(0);
  48.         $gameSchoolClassHighscore->setPoints(0);
  49.         $gameSchoolClassHighscore->setUpdateAt(new DateTime());
  51.         $this->em->persist($gameSchoolClassHighscore);
  52.         $this->em->flush();
  54.         return $gameSchoolClassHighscore;
  55.     }
  57.     public function getClickThePicPointsForGame(Game $game) {
  58.         $gameID = $game->getId();
  59.         $sql = "SELECT sum(points) as points "
  60.                 . "FROM game_ctp_question as ctpq "
  61.                 . "WHERE game_id=$gameID AND answer_correct=1;";
  62.         $results = $this->connection->query($sql)->fetchAllAssociative();
  63.         if (empty($results)) {
  64.             return 0;
  65.         }
  67.         return intval($results[0]['points']);
  68.     }
  70.     public function getClickThePicRoundsCorrectForGame(Game $game) {
  71.         $gameID = $game->getId();
  72.         $sql = "SELECT count(*) as rounds "
  73.                 . "FROM game_ctp_question as ctpq "
  74.                 . "WHERE game_id=$gameID AND answer_correct=1;";
  75.         $results = $this->connection->query($sql)->fetchAllAssociative();
  76.         if (empty($results)) {
  77.             return 0;
  78.         }
  80.         return intval($results[0]['rounds']);
  81.     }
  83.     public function getQuizRoundPoints($roundNumber) {
  84.         switch ($roundNumber) {
  85.             case 1: return 5;
  86.             case 2: return 7;
  87.             case 3: return 10;
  88.             case 4: return 15;
  89.             case 5: return 30;
  90.             case 6: return 40;
  91.             case 7: return 55;
  92.             case 8: return 70;
  93.             case 9: return 150;
  94.             case 10: return 200;
  95.             case 11: return 300;
  96.             case 12: return 800;
  97.             default: return 0;
  98.         }
  100.         return 0;
  101.     }
  103.     public function getSchoolClassGamePoints(SchoolClass $schoolClass, DateTime $end = null) {
  104.         $pointsSum = 0;
  106.         $start = null;
  108.         /* @var $schoolClassGameHistoryLast SchoolClassGameHistory */
  109.         $schoolClassGameHistoryLast = $this->getSchoolClassGameHistoryLast($schoolClass);
  110.         if (!empty($schoolClassGameHistoryLast)) {
  111.             $start = $schoolClassGameHistoryLast->getCreatedAt();
  112.             $pointsSum += intval($schoolClassGameHistoryLast->getPoints());
  113.         }
  115.         $pointsGames = $this->getSchoolClassGamePointsDate($schoolClass, $start, $end);
  116.         $pointsSum += $pointsGames;
  118.         return $pointsSum;
  119.     }
  121.     private function getSchoolClassGameHistoryLast(SchoolClass $schoolClass) {
  122.         $schoolClassID = $schoolClass->getId();
  124.         $query = "SELECT scgh "
  125.                 . "FROM App\Entity\SchoolClassGameHistory scgh "
  126.                 . "LEFT JOIN scgh.schoolClass sc "
  127.                 . "WHERE sc.id = :scid";
  129.         /* @var $dqlQuery Query */
  130.         $dqlQuery = $this->em->createQuery($query);
  131.         $dqlQuery->setParameter('scid', $schoolClassID);
  132.         $dqlQuery->setMaxResults(1);
  134.         $dqlResults = $dqlQuery->execute();
  135.         if (count($dqlResults) > 0) {
  136.             return $dqlResults[0];
  137.         }
  139.         return null;
  140.     }
  142.     private function getSchoolClassGamePointsDate(SchoolClass $schoolClass, DateTime $start = null, DateTime $end = null) {
  143.         $schoolClassID = $schoolClass->getId();
  145.         $strWhere = $this->getSchoolClassGamePointsWhere($start, $end);
  147.         $sql = "SELECT scc.school_class_id as scid, sum(g.points) as points "
  148.                 . "FROM game as g "
  149.                 . "LEFT JOIN game_state as gs ON g.game_state_id=gs.id "
  150.                 . "LEFT JOIN school_class_client as scc ON g.school_class_client_id=scc.id "
  151.                 . "WHERE scc.school_class_id=$schoolClassID AND gs.id IN (2,3,4) $strWhere"
  152.                 . "GROUP BY scc.school_class_id;";
  153.         $results = $this->connection->query($sql)->fetchAllAssociative();
  154.         if (count($results) > 0) {
  155.             return $results[0]['points'];
  156.         }
  158.         return 0;
  159.     }
  161.     public function getSchoolClassesGamePoints(DateTime $start = null, DateTime $end = null) {
  162.         $strWhere = $this->getSchoolClassGamePointsWhere($start, $end);
  164.         $sql = "SELECT scc.school_class_id as scid, sum(g.points) as points "
  165.                 . "FROM game as g "
  166.                 . "LEFT JOIN game_state as gs ON g.game_state_id=gs.id "
  167.                 . "LEFT JOIN school_class_client as scc ON g.school_class_client_id=scc.id "
  168.                 . "LEFT JOIN school_class as sc ON scc.school_class_id=sc.id "
  169.                 . "LEFT JOIN school as sch ON sc.school_id=sch.id "
  170.                 . "WHERE g.game_timer_id IS NOT NULL AND gs.id IN (2,3,4) AND sch.id NOT IN (1,167) $strWhere"
  171.                 . "GROUP BY scc.school_class_id "
  172.                 . "ORDER BY points DESC;";
  173.         $results = $this->connection->query($sql)->fetchAllAssociative();
  174.         return $results;
  175.     }
  177.     public function getSchoolClassesTrophyPoints() {
  178.         $sql = "SELECT sc.id as scid, sum(gt.tropy_points) as points "
  179.                 . "FROM game_trophy_progress as gtp "
  180.                 . "LEFT JOIN game_trophy_progress_entry as gtpe ON gtpe.game_trophy_progress_id=gtp.id "
  181.                 . "LEFT JOIN game_trophy as gt ON gtpe.game_trophy_id=gt.id "
  182.                 . "LEFT JOIN school_class as sc ON gtp.school_class_id=sc.id "
  183.                 . "LEFT JOIN school as sch ON sc.school_id=sch.id "
  184.                 . "WHERE sch.id NOT IN (1,167) AND gtpe.finished_at IS NOT NULL "
  185.                 . "GROUP BY sc.id;";
  186.         $results = $this->connection->query($sql)->fetchAllAssociative();
  187.         return $results;
  188.     }
  190.     public function getSchoolClassGameHistoryBonus() {
  191.         return $this->em->getRepository(SchoolClassGameHistoryBonus::class)->findAll();
  192.     }
  194.     private function getSchoolClassGamePointsWhere(DateTime $start = null, DateTime $end = null) {
  195.         $strWhere = '';
  197.         if (!empty($start)) {
  198.             $strStart = $start->format('Y-m-d H:i:s');
  199.             $strWhere = "AND g.created_at >= '$strStart' ";
  200.         }
  202.         if (empty($end)) {
  203.             $end = new DateTime();
  204.             $end->setTime(23, 59);
  205.             $strEnd = $end->format('Y-m-d H:i:s');
  206.             $strWhere .= "AND g.created_at <= '$strEnd' ";
  207.         } else {
  208.             $strEnd = $end->format('Y-m-d H:i:s');
  209.             $strWhere .= "AND g.created_at <= '$strEnd' ";
  210.         }
  212.         return $strWhere;
  213.     }
  215.     public function updateGameSchoolClassHighscore(Game $game, $roundNumber, $points) {
  216.         /* @var $schoolClass SchoolClass */
  217.         $schoolClass = $game->getSchoolClassClient()->getSchoolClass();
  218.         $gameType = $game->getGameType();
  219.         $gameCategory = $game->getCategory();
  221.         $gameSchoolClassHighscore = $this->em->getRepository(GameSchoolClassHighscore::class)->findOneBy(array(
  222.             'schoolClass' => $schoolClass,
  223.             'gameType' => $gameType,
  224.             'gameCategory' => $gameCategory
  225.         ));
  227.         if (empty($gameSchoolClassHighscore)) {
  228.             $gameSchoolClassHighscore = $this->createGameSchoolClassHighscore($schoolClass, $gameType, $gameCategory);
  229.         }
  231.         if ($this->checkUpdateHighscore($gameSchoolClassHighscore, $roundNumber, $points)) {
  232.             $gameSchoolClassHighscore->setRoundNumber($roundNumber);
  233.             $gameSchoolClassHighscore->setPoints($points);
  234.             $this->em->persist($gameSchoolClassHighscore);
  235.             $this->em->flush();
  236.         }
  237.     }
  239.     private function checkUpdateHighscore(GameSchoolClassHighscore $gameSchoolClassHighscore, $roundNumber, $points) {
  240.         $currentRound = $gameSchoolClassHighscore->getRoundNumber();
  241.         if ($roundNumber < $currentRound) {
  242.             return false;
  243.         }
  245.         $currentPoints = $gameSchoolClassHighscore->getPoints();
  246.         if ($points < $currentPoints) {
  247.             return false;
  248.         }
  250.         return true;
  251.     }
  253.     public function publishSchoolClassPoints(Game $game) {
  254.         /* @var $schoolClass SchoolClass */
  255.         $schoolClass = $game->getSchoolClassClient()->getSchoolClass();
  256.         $schoolClassPoints = $this->getSchoolClassGamePoints($schoolClass);
  257.         $schoolClassID = $schoolClass->getId();
  258.         $this->mqttHelper->sendSchoolClassPoints($schoolClassID, $schoolClassPoints);
  259.     }
  260. }