Add new findTranslatedSortedByPeriod
[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 * Find translated location title sorted 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 findTranslatedSortedByPeriod(TranslatorInterface $translator, $period, $userId = null) {
38 //Fetch sessions
39 $ret = $this->getEntityManager()
40 ->createQuery(
41 'SELECT l.id, l.title
42 FROM RapsysAirBundle:Location l
43 LEFT JOIN RapsysAirBundle:Session s WITH s.location = l.id AND s.date BETWEEN :begin AND :end
44 LEFT JOIN RapsysAirBundle:Application a WITH a.id = s.application'.(!empty($userId)?' AND a.user = :uid':'').'
45 GROUP BY l.id
46 ORDER BY '.(!empty($userId)?'COUNT(a.id) DESC, ':'').'COUNT(s.id) DESC, l.id'
47 )
48 ->setParameter('begin', $period->getStartDate())
49 ->setParameter('end', $period->getEndDate());
50
51 //Set optional user id
52 if (!empty($userId)) {
53 $ret->setParameter('uid', $userId);
54 }
55
56 //Get Result
57 $ret = $ret->getResult();
58
59 //Rekey array
60 $ret = array_column($ret, 'title', 'id');
61
62 //Filter array
63 foreach($ret as $k => $v) {
64 $ret[$k] = $translator->trans($v);
65 }
66
67 //Send result
68 return $ret;
69 }
70
71 /**
72 * Fetch translated location title with session by date period
73 *
74 * @param $translator The TranslatorInterface instance
75 * @param $period The date period
76 * @param $granted The session is granted
77 * TODO: a dropper
78 */
79 public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) {
80 //Fetch sessions
81 $ret = $this->getEntityManager()
82 ->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')
83 ->setParameter('begin', $period->getStartDate())
84 ->setParameter('end', $period->getEndDate())
85 ->getResult();
86
87 //Rekey array
88 $ret = array_column($ret, 'title', 'id');
89
90 //Filter array
91 foreach($ret as $k => $v) {
92 $ret[$k] = $translator->trans($v);
93 }
94
95 //Send result
96 return $ret;
97 }
98
99 /**
100 * Fetch translated location title with user session by date period
101 *
102 * @param $translator The TranslatorInterface instance
103 * @param $period The date period
104 * @param $userId The user uid
105 * TODO: a dropper
106 */
107 public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) {
108 //Fetch sessions
109 $ret = $this->getEntityManager()
110 ->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')
111 #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
112 ->setParameter('begin', $period->getStartDate())
113 ->setParameter('end', $period->getEndDate())
114 ->setParameter('uid', $userId)
115 ->getResult();
116
117 //Rekey array
118 $ret = array_column($ret, 'title', 'id');
119
120 //Filter array
121 foreach($ret as $k => $v) {
122 $ret[$k] = $translator->trans($v);
123 }
124
125 //Send result
126 return $ret;
127 }
128 }