Symfony Profiler

<?php
namespace App\Helper;
use App\DTOs\UserMe;
use App\Entity\Monolith\User;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpKernel\Exception\AccessDeniedHttpException;
class LoginHelper
{
public static function fetchUser(
EntityManagerInterface $entityManager,
string $identifierField,
mixed $identifierValue,
?string $userType = null,
?int $kindergarten = null
): ?UserMe
{
if (!in_array($userType, [User::APP_TYPE_PARENT, User::APP_TYPE_TEACHER, null])) {
throw new AccessDeniedHttpException('Undefined USER-TYPE');
}
$sql = "
select u.*,
GROUP_CONCAT(DISTINCT fu2.entity_id SEPARATOR ',') AS my_children,
kr.modules_access,
kr.role_id,
CASE
when c.student_id is not null then 'student'
else ur.role_id
end as type
from user u
inner join user_role_linker url on u.user_id = url.user_id
inner join user_role ur on url.role_id = ur.id
left join kg_role_users kru on (u.user_id = kru.user_id and kru.kindergarten_id = :kgId)
left join kg_roles kr on (:kgId = kr.kindergarten_id and (kru.role_id = kr.role_id or (kru.role_id is null and (kr.role_id=ur.role_id or (kr.role_id='parent' and ur.role_id='parinte')))))
left join family_users fu on (fu.user_id = u.user_id and fu.entity_type = 'parinte' and ur.role_id = 'parinte')
left join family_users fu2 on (fu.family_id = fu2.family_id and fu2.entity_type = 'child' and ur.role_id = 'parinte')
left join children c on u.user_id = c.student_id
where u.{$identifierField}=:identifierValue
group by u.user_id
";
$stmt = $entityManager->getConnection()->prepare($sql);
if (
$user = $stmt->executeQuery([
'identifierValue' => $identifierValue,
'kgId' => $kindergarten ?? 0
])->fetchAssociative()
) {
return UserMe::map($user)
->setKindergartens(self::getKindergartens($entityManager, 'user_id', $user['user_id']))
->setGroups(self::getGroups($entityManager, 'user_id', $user['user_id']))
;
}
return null;
}
private static function getKindergartens(EntityManagerInterface $entityManager, string $identifierField, mixed $identifierValue): ?array
{
$sql = "
select
DISTINCT IFNULL(uke.kindergarten_id,
IFNULL(ukka.kindergarten_id,
IFNULL(ukp.kindergarten_id,
IFNULL(ukc2.kindergarten_id, IFNULL(ukc3.kindergarten_id, ukc.kindergarten_id))
)
)
) as kg,
IFNULL(uke.from_date,
IFNULL(ukka.from_date,
IFNULL(ukp.from_date,
IFNULL(ukc.from_date, ukc2.from_date)
)
)
) as from_date,
IFNULL(uke.end_date,
IFNULL(ukka.end_date,
IFNULL(ukp.end_date,
IFNULL(ukc.end_date, ukc2.end_date)
)
)
) as end_date
from user u
inner join user_role_linker url on u.user_id = url.user_id
inner join user_role ur on url.role_id = ur.id
left join educators e on u.user_id = e.user_id
left join parents p on u.user_id = p.user_id
left join family_users fu on (fu.user_id = u.user_id and fu.entity_type = 'parinte' and ur.role_id = 'parinte')
left join family_users fu2 on (fu.family_id = fu2.family_id and fu2.entity_type = 'child' and ur.role_id = 'parinte')
left join children c on u.user_id = c.student_id
left join children c2 on p.id = c2.parent_id
left join kg_admins ka on u.user_id = ka.user_id
left join user_kindergartens uke on (uke.entity_id = e.id and uke.entity_type = 'educator' and ur.role_id = 'educator')
left join user_kindergartens ukka on (ukka.entity_id = ka.id and ukka.entity_type = 'gradinita_admin' and ur.role_id = 'gradinita_admin')
left join user_kindergartens ukp on (ukp.entity_id = p.id and ukp.entity_type = 'parinte' and ur.role_id = 'parinte')
left join user_kindergartens ukc on (ukc.entity_id = c.user_id and ukc.entity_type = 'copil' and ur.role_id = 'parinte')
left join user_kindergartens ukc2 on (ukc2.entity_id = fu2.user_id and ukc2.entity_type = 'copil' and ur.role_id = 'parinte')
left join user_kindergartens ukc3 on (ukc3.entity_id = c2.user_id and ukc3.entity_type = 'copil' and ur.role_id = 'parinte')
where u.{$identifierField}=:identifierValue
";
$stmt = $entityManager->getConnection()->prepare($sql);
$list = $stmt->executeQuery(['identifierValue' => $identifierValue])->fetchAllAssociative();
$list = array_filter($list);
$result = [];
foreach ($list as $item) {
if (!$item['kg']) {
continue;
}
if ('0000-00-00 00:00:00' == $item['end_date']) {
$item['end_date'] = null;
}
if (!isset($result[$item['kg']])) {
$result[$item['kg']] = [$item['from_date'], $item['end_date']];
continue;
}
if (strtotime($item['from_date']) < strtotime($result[$item['kg']][0])) {
$result[$item['kg']][0] = $item['from_date'];
}
if (is_null($item['end_date']) || is_null($result[$item['kg']][1])) {
$result[$item['kg']][1] = null;
} elseif (strtotime($item['end_date']) < strtotime($result[$item['kg']][1])) {
$result[$item['kg']][1] = $item['end_date'];
if (strtotime($result[$item['kg']][1]) < time()) {
$result[$item['kg']][1] = null;
}
}
}
return $result;
}
private static function getGroups(EntityManagerInterface $entityManager, string $identifierField, mixed $identifierValue): ?array
{
$sql = "
select
DISTINCT IFNULL(eg.group_id,
IFNULL(cg2.group_id, cg.group_id)
) as `group`,
IFNULL(eg.from_date,
IFNULL(cg2.from_date, cg.from_date)
) as from_date,
IFNULL(eg.end_date,
IFNULL(cg2.end_date, cg.end_date)
) as end_date
from user u
inner join user_role_linker url on u.user_id = url.user_id
inner join user_role ur on url.role_id = ur.id
left join educators e on u.user_id = e.user_id
left join educators_groups eg on (eg.educator_id = e.id)
left join family_users fu on (fu.user_id = u.user_id and fu.entity_type = 'parinte' and ur.role_id = 'parinte')
left join family_users fu2 on (fu.family_id = fu2.family_id and fu2.entity_type = 'child' and ur.role_id = 'parinte')
left join children_groups cg2 on (cg2.child_id = fu2.entity_id)
left join children c on u.user_id = c.student_id
left join children_groups cg on (cg.child_id = c.user_id)
where u.{$identifierField}=:identifierValue
";
$stmt = $entityManager->getConnection()->prepare($sql);
$list = $stmt->executeQuery(['identifierValue' => $identifierValue])->fetchAllAssociative();
$list = array_filter($list);
$result = [];
foreach ($list as $item) {
if (!$item['group']) {
continue;
}
if ('0000-00-00 00:00:00' == $item['end_date']) {
$item['end_date'] = null;
}
if (!isset($result[$item['group']])) {
$result[$item['group']] = [$item['from_date'], $item['end_date']];
continue;
}
if (strtotime($item['from_date']) < strtotime($result[$item['group']][0])) {
$result[$item['group']][0] = $item['from_date'];
}
if (is_null($item['end_date']) || is_null($result[$item['group']][1])) {
$result[$item['group']][1] = null;
} elseif (strtotime($item['end_date']) < strtotime($result[$item['group']][1])) {
$result[$item['group']][1] = $item['end_date'];
if (strtotime($result[$item['group']][1]) < time()) {
$result[$item['group']][1] = null;
}
}
}
return $result;
}
}

src/Helper/LoginHelper.php line 12