/** * @param Party $party * @param bool $isUploader * @return array * @throws \Doctrine\DBAL\DBALException */ private function getRatingsByIsUploader(Party $party, $isUploader = true) { $em = $this->getEntityManager(); $conn = $em->getConnection(); $platform = $conn->getDatabasePlatform(); $leastFunction = $platform instanceof SqlitePlatform ? 'MIN' : 'LEAST'; $uploaderCheck = $isUploader ? "NOT NULL" : "NULL"; $sql = "SELECT\n work_id,\n w1.name,\n count(*) as vote_cnt,\n\n SUM(vote * {$leastFunction}(5.0, (SELECT COUNT(*) FROM ProjectVote v2 WHERE v2.user_id = v1.user_id)) / 5.0)\n / SUM({$leastFunction}(5.0, (SELECT COUNT(*) FROM ProjectVote v2 WHERE v2.user_id = v1.user_id)) / 5.0)\n\n AS vote_avg\n FROM ProjectVote v1\n LEFT JOIN Work w1 ON v1.work_id = w1.id\n LEFT JOIN User u ON v1.user_id = u.id\n LEFT JOIN Work w2 ON w2.author_id = u.id\n WHERE\n v1.vote != 0 AND\n w1.party_id = :party_id AND\n w2.id IS {$uploaderCheck}\n GROUP BY work_id\n ORDER BY vote_avg desc;\n "; $stmt = $conn->prepare($sql); $stmt->execute(['party_id' => $party->getId()]); $results = $stmt->fetchAll(); $ratings = []; foreach ($results as $row) { $ratings[$row['work_id']] = $row['vote_avg']; } return $ratings; }
/** * @param Party $party * @return null|string */ public function getPartyTheme(Party $party) { $now = new \DateTime(); if ($now < $party->getThemeVotingEndTime()) { return null; } /** @var PartyThemeRepository $partyThemeRepo */ $partyThemeRepo = $this->kernel->getContainer()->get('doctrine')->getManager()->getRepository('GeekPartyBundle:PartyTheme'); $themes = $partyThemeRepo->findAllSortedByVotes($party); if ($themes) { /** @var PartyTheme $theme */ $theme = $themes[0]; return $theme->getText(); } return null; }