X-Git-Url: https://git.rapsys.eu/airbundle/blobdiff_plain/22509e0a4cfeb5125196242104c8c4b562286e33..HEAD:/Repository/UserRepository.php

diff --git a/Repository/UserRepository.php b/Repository/UserRepository.php
index 9e869e1..725e3ed 100644
--- a/Repository/UserRepository.php
+++ b/Repository/UserRepository.php
@@ -11,19 +11,23 @@
 
 namespace Rapsys\AirBundle\Repository;
 
+use Doctrine\ORM\AbstractQuery;
 use Doctrine\ORM\Query\ResultSetMapping;
+
 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 
+use Rapsys\AirBundle\Repository;
+
 /**
  * UserRepository
  */
-class UserRepository extends EntityRepository {
+class UserRepository extends Repository {
 	/**
 	 * Find users with translated highest group and civility
 	 *
 	 * @return array The user ids keyed by group and pseudonym
 	 */
-	public function findIndexByGroupPseudonym(): array {
+	public function findChoicesAsArray(): array {
 		//Set the request
 		$req =<<<SQL
 SELECT
@@ -37,10 +41,11 @@ FROM (
 		u.pseudonym,
 		g.id AS g_id,
 		g.title AS g_title
-	FROM RapsysAirBundle:User AS u
-	LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-	LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
-	ORDER BY g.id DESC
+	FROM Rapsys\AirBundle\Entity\User AS u
+	JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+	JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
+	WHERE g.title <> 'User'
+	ORDER BY g.id DESC, u.pseudonym ASC
 	LIMIT 0, :limit
 ) AS a
 GROUP BY a.id
@@ -74,10 +79,11 @@ SQL;
 		//Process result
 		foreach($res as $data) {
 			//Without group or simple user
-			if (empty($data['g_title']) || $data['g_title'] == 'User') {
-				//Skip it
-				continue;
-			}
+			#XXX: moved in sql by removing LEFT JOIN and excluding user group
+			#if (empty($data['g_title']) || $data['g_title'] == 'User') {
+			#	//Skip it
+			#	continue;
+			#}
 
 			//Get translated group
 			$group = $this->translator->trans($data['g_title']);
@@ -89,13 +95,57 @@ SQL;
 
 			//Set data
 			//XXX: ChoiceType use display string as key
-			$ret[$group][$data['pseudonym']] = $data['id'];
+			$ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
 		}
 
 		//Send result
 		return $ret;
 	}
 
+	/**
+	 * Find user ids by pseudonym
+	 *
+	 * @param array $pseudonym The pseudonym filter
+	 * @return array The user ids
+	 */
+	public function findIdByPseudonymAsArray(array $pseudonym): array {
+		//Set the request
+		$req =<<<SQL
+SELECT
+	a.id
+FROM (
+	SELECT
+		u.id
+	FROM Rapsys\AirBundle\Entity\User AS u
+	LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+	WHERE u.pseudonym IN (:pseudonym)
+	ORDER BY gu.group_id DESC, u.pseudonym ASC
+	LIMIT 0, :limit
+) AS a
+GROUP BY a.id
+ORDER BY NULL
+SQL;
+
+		//Replace bundle entity name by table name
+		$req = str_replace($this->tableKeys, $this->tableValues, $req);
+
+		//Get result set mapping instance
+		//XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+		$rsm = new ResultSetMapping();
+
+		//Declare all fields
+		//XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+		//XXX: we don't use a result set as we want to translate group and civility
+		$rsm->addScalarResult('id', 'id', 'integer');
+
+		//Return result
+		return $this->_em
+			->createNativeQuery($req, $rsm)
+			->setParameter('pseudonym', $pseudonym)
+			//XXX: instead of array_column on the result
+			->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
+	}
+
 	/**
 	 * Find applicant by session id
 	 *
@@ -106,8 +156,8 @@ SQL;
 		//Set the request
 		$req =<<<SQL
 SELECT u.id, u.pseudonym
-FROM RapsysAirBundle:Application AS a
-JOIN RapsysAirBundle:User AS u ON (u.id = a.user_id)
+FROM Rapsys\AirBundle\Entity\Application AS a
+JOIN Rapsys\AirBundle\Entity\User AS u ON (u.id = a.user_id)
 WHERE a.session_id = :id
 SQL;
 
@@ -168,14 +218,14 @@ SELECT
 	c.title AS c_title,
 	u.country_id AS o_id,
 	o.title AS o_title,
+	GREATEST(u.created, u.updated, COALESCE(c.created, '1970-01-01'), COALESCE(c.updated, '1970-01-01'), COALESCE(o.created, '1970-01-01'), COALESCE(o.updated, '1970-01-01'), COALESCE(g.created, '1970-01-01'), COALESCE(g.updated, '1970-01-01')) AS modified,
 	GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
-	GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
-	GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified
-FROM RapsysAirBundle:User AS u
-LEFT JOIN RapsysAirBundle:Civility AS c ON (c.id = u.civility_id)
-LEFT JOIN RapsysAirBundle:Country AS o ON (o.id = u.country_id)
-LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+	GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles
+FROM Rapsys\AirBundle\Entity\User AS u
+LEFT JOIN Rapsys\AirBundle\Entity\Civility AS c ON (c.id = u.civility_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Country AS o ON (o.id = u.country_id)
+LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+LEFT JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
 WHERE u.id = :id
 SQL;
 
@@ -202,11 +252,11 @@ SQL;
 			->addScalarResult('c_title', 'c_title', 'string')
 			->addScalarResult('o_id', 'o_id', 'integer')
 			->addScalarResult('o_title', 'o_title', 'string')
+			->addScalarResult('modified', 'modified', 'datetime')
 			//XXX: is a string because of \n separator
 			->addScalarResult('ids', 'ids', 'string')
 			//XXX: is a string because of \n separator
 			->addScalarResult('titles', 'titles', 'string')
-			->addScalarResult('modified', 'modified', 'datetime')
 			->addIndexByScalar('id');
 
 		//Get result
@@ -225,7 +275,7 @@ SQL;
 		$result['alternates'] = [];
 
 		//Set route
-		$route = 'rapsys_air_user_view';
+		$route = 'rapsysair_user_view';
 
 		//Set route params
 		$routeParams = ['id' => $id, 'user' => $this->slugger->slug($result['pseudonym'])];
@@ -233,7 +283,7 @@ SQL;
 		//Milonga Raphaël exception
 		if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
 			//Set route
-			$route = 'rapsys_air_user_milongaraphael';
+			$route = 'rapsysair_user_milongaraphael';
 			//Set route params
 			$routeParams = [];
 		}
@@ -276,7 +326,8 @@ SQL;
 		//Iterate on each location
 		foreach(explode("\n", $result['ids']) as $k => $id) {
 			//Add role
-			$roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
+			//XXX: roles are keyes by id
+			$roles[$id] = 'ROLE_'.strtoupper($titles[$k]);
 
 			//Add group
 			$groups[$id] = $this->translator->trans($titles[$k]);
@@ -352,14 +403,14 @@ FROM (
 			u.pseudonym,
 			g.id AS g_id,
 			g.title AS g_title
-		FROM RapsysAirBundle:User AS u
-		JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-		JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+		FROM Rapsys\AirBundle\Entity\User AS u
+		JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+		JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
 		ORDER BY NULL
 		LIMIT 0, :limit
 	) AS c
-	LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
-	LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
+	LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.user_id = c.id)
+	LEFT JOIN Rapsys\AirBundle\Entity\Dance AS d ON (d.id = a.dance_id)
 	GROUP BY d.id
 	ORDER BY NULL
 	LIMIT 0, :limit
@@ -422,8 +473,8 @@ SQL;
 				'dances' => [],
 				'slug' => $slug = $this->slugger->slug($data['pseudonym']),
 				//Milonga Raphaël exception
-				'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router->generate('rapsys_air_user_milongaraphael', []) : $this->router->generate('rapsys_air_user_view', ['id' => $data['id'], 'user' => $slug]),
-				'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
+				'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router->generate('rapsysair_user_milongaraphael', []) : $this->router->generate('rapsysair_user_view', ['id' => $data['id'], 'user' => $slug]),
+				'edit' => $this->router->generate('rapsysuser_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
 			];
 
 			//With dances
@@ -439,13 +490,13 @@ SQL;
 					//Init dance when missing
 					if (!isset($ret[$group][$data['id']]['dances'][$name = $this->translator->trans($names[$k])])) {
 						$ret[$group][$data['id']]['dances'][$name] = [
-							'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
+							'link' => $this->router->generate('rapsysair_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
 							'types' => []
 						];
 					}
 
 					//Set type
-					$ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator->trans($types[$k])] = $this->router->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $this->slugger->slug($name), 'type' => $this->slugger->slug($type)]);
+					$ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator->trans($types[$k])] = $this->router->generate('rapsysair_dance_view', ['id' => $id, 'name' => $this->slugger->slug($name), 'type' => $this->slugger->slug($type)]);
 				}
 			}
 		}