]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/LocationRepository.php
Add sql requests
[airbundle] / Repository / LocationRepository.php
1 <?php
2
3 namespace Rapsys\AirBundle\Repository;
4
5 use Symfony\Component\Translation\TranslatorInterface;
6
7 /**
8 * LocationRepository
9 */
10 class LocationRepository extends \Doctrine\ORM\EntityRepository {
11 /**
12 * Find complementary locations by session id
13 *
14 * @param $id The session id
15 * @return array The other locations
16 */
17 public function findComplementBySessionId($id) {
18 //Fetch complement locations
19 $ret = $this->getEntityManager()
20 ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s LEFT JOIN RapsysAirBundle:Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN RapsysAirBundle:Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id')
21 ->setParameter('sid', $id)
22 ->getArrayResult();
23
24 //Rekey array
25 $ret = array_column($ret, 'id', 'title');
26
27 return $ret;
28 }
29
30 /**
31 * Fetch translated location with session by date period
32 *
33 * @param $translator The TranslatorInterface instance
34 * @param $period The date period
35 * @param $granted The session is granted
36 */
37 public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) {
38 //Fetch sessions
39 $ret = $this->getEntityManager()
40 ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE '.($granted?'s.application IS NOT NULL AND ':'').'l.id = s.location AND s.date BETWEEN :begin AND :end GROUP BY l.id ORDER BY l.id')
41 ->setParameter('begin', $period->getStartDate())
42 ->setParameter('end', $period->getEndDate())
43 ->getResult();
44
45 //Rekey array
46 $ret = array_column($ret, 'title', 'id');
47
48 //Filter array
49 foreach($ret as $k => $v) {
50 $ret[$k] = $translator->trans($v);
51 }
52
53 //Send result
54 return $ret;
55 }
56
57 /**
58 * Fetch translated user location with session by date period
59 *
60 * @param $translator The TranslatorInterface instance
61 * @param $period The date period
62 * @param $userId The user uid
63 */
64 public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) {
65 //Fetch sessions
66 $ret = $this->getEntityManager()
67 ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Application a JOIN RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE a.user = :uid AND a.session = s.id AND s.date BETWEEN :begin AND :end AND s.location = l.id GROUP BY l.id ORDER BY l.id')
68 #SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Application a JOIN RapsysAirBundle:Location l WHERE s.date BETWEEN :begin AND :end AND s.id = a.session AND l.id = s.location GROUP BY l.id ORDER BY l.id
69 ->setParameter('begin', $period->getStartDate())
70 ->setParameter('end', $period->getEndDate())
71 ->setParameter('uid', $userId)
72 ->getResult();
73
74 //Rekey array
75 $ret = array_column($ret, 'title', 'id');
76
77 //Filter array
78 foreach($ret as $k => $v) {
79 $ret[$k] = $translator->trans($v);
80 }
81
82 //Send result
83 return $ret;
84 }
85 }