/**
* @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;
}
