Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Company;
use App\Entity\User;
use App\Services\ToolsService;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Bridge\Doctrine\Security\User\UserLoaderInterface;
use Symfony\Component\Security\Core\Exception\UnsupportedUserException;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\Security\Core\User\PasswordUpgraderInterface;
use Symfony\Component\Security\Core\User\UserInterface;
/**
* @method User|null find($id, $lockMode = null, $lockVersion = null)
* @method User|null findOneBy(array $criteria, array $orderBy = null)
* @method User[] findAll()
* @method User[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class UserRepository extends ServiceEntityRepository implements PasswordUpgraderInterface, UserLoaderInterface
{
/**
* @var Security
*/
private $security;
/**
* @var ToolsService
*/
private $tools;
public function __construct(ManagerRegistry $registry, Security $security, ToolsService $tools)
{
parent::__construct($registry, User::class);
$this->security = $security;
$this->tools = $tools;
}
/**
* Used to upgrade (rehash) the user's password automatically over time.
* @param UserInterface $user
* @param string $newEncodedPassword
* @throws ORMException
* @throws OptimisticLockException
*/
public function upgradePassword(UserInterface $user, string $newEncodedPassword): void
{
if (!$user instanceof User) {
throw new UnsupportedUserException(sprintf('Instances of "%s" are not supported.', \get_class($user)));
}
$user->setPassword($newEncodedPassword);
$this->_em->persist($user);
$this->_em->flush();
}
/**
* @param string $username
* @return User|null
*/
public function loadUserByUsername(string $username): ?User
{
$entityManager = $this->getEntityManager();
try {
return $entityManager->createQuery(
'SELECT u
FROM App\Entity\User u
WHERE u.username = :query'
)
->setParameter('query', $username)
->getOneOrNullResult();
} catch (NonUniqueResultException $e) {
return null;
}
}
/**
* @param string $username
* @param Company $company
* @return User|null
*/
public function loadUserByUsernameAndCompany(string $username, Company $company): ?User
{
$entityManager = $this->getEntityManager();
try {
return $entityManager->createQuery(
'SELECT u
FROM App\Entity\User u
WHERE (u.username = :query
OR u.email = :query) AND u.company = :company'
)
->setParameter('query', $username)
->setParameter('company', $company)
->getOneOrNullResult();
} catch (NonUniqueResultException $e) {
return null;
}
}
public function getStatsUsers(array $filters = [])
{
$defaultFilters = [
'company' => null,
'start' => null,
'end' => null,
];
$filters = array_merge($defaultFilters, $filters);
$datas = [
'users_total' => [
'count' => 0
],
'users_active' => [
'count' => 0
]
];
$filters['company'] = (isset($filters['company']) && $filters['company'] instanceof Company) ? $filters['company']->getId() : $filters['company'];
// Get departments Data
$sql = "SELECT * FROM department ";
if(isset($filters['company'])) {
$sql .="WHERE company_id = {$filters['company']} ";
}
$sql .= "ORDER BY name ASC;";
$departments = $this->tools->getQueryResults($sql);
// Initialize $datas
foreach($departments as $department) {
$datas['users_total']['departments'][$department['id']] = [
'name' => $department['name'],
'count' => 0,
'percent' => 0,
];
$datas['users_total']['count'] = 0;
$datas['users_active']['departments'][$department['id']] = [
'name' => $department['name'],
'count' => 0,
'percent' => 0,
];
if($this->security->getUser() instanceof User && in_array("ROLE_ADMIN",$this->security->getUser()->getRoles())) {
$datas['users_total']['departments'][$department['id']]['company'] = $department['company_id'];
$datas['users_active']['departments'][$department['id']]['company'] = $department['company_id'];
}
$datas['users_active']['count'] = 0;
}
// Récupère un tableau d'utilisateurs actif
$sql = "SELECT VE.user_id FROM `video_event` AS VE, user AS U
WHERE U.id = VE.user_id ";
if(isset($filters['company'])) {
$sql .= "AND U.company_id = {$filters['company']} ";
}
if(isset($filters['start']) && isset($filters['end'])) {
$sql .= "AND VE.date BETWEEN '".$filters['start']->format("Y-m-d H:i:s")."' AND '".$filters['end']->format("Y-m-d H:i:s")."' ";
}
$sql .= "AND VE.timecode > 0
GROUP BY VE.user_id";
$activeUsersQueryResults = $this->tools->getQueryResults($sql);
$activeUsers = [];
foreach(array_values($activeUsersQueryResults) as $user) {
$activeUsers[] = $user['user_id'];
}
// Récupère tous les utilisateurs de la Company
$sql = "SELECT * FROM user ";
if(isset($filters['company'])) {
$sql .="WHERE company_id = {$filters['company']} ";
}
$users = $this->tools->getQueryResults($sql);
// Compte le nombre d'utilisateur, et utilisateurs actif par departement
foreach($users as $user) {
$datas['users_total']['count']++;
$departmentId = (empty($user['department_id'])) ? " " : $user['department_id'];
if(!isset($datas['users_total']['departments'][$departmentId])){
$datas['users_total']['departments'][$departmentId]['count'] = 0;
}
$datas['users_total']['departments'][$departmentId]['count']++;
$datas['users_total']['departments'][$departmentId]['percent'] = number_format(($datas['users_total']['departments'][$departmentId]['count']/$datas['users_total']['count'])*100, 2);
if(in_array($user['id'], $activeUsers)) {
$datas['users_active']['count']++;
if(!isset($datas['users_active']['departments'][$departmentId])) {
$datas['users_active']['departments'][$departmentId]['count'] = 0;
}
$datas['users_active']['departments'][$departmentId]['count']++;
$datas['users_active']['departments'][$departmentId]['percent'] = number_format(($datas['users_active']['departments'][$departmentId]['count']/$datas['users_active']['count'])*100, 2);
}
}
if(!isset($datas['users_active']['departments'])) {
$datas['users_active']['departments'] = [];
}
// Tri les departments par "count" DESC
$columnsTotal = array_column($datas['users_total']['departments'], 'count');
array_multisort($columnsTotal, SORT_DESC, $datas['users_total']['departments']);
$columnsActive = array_column($datas['users_active']['departments'], 'count');
array_multisort($columnsActive, SORT_DESC, $datas['users_active']['departments']);
return $datas;
}
public function getUsersByRole(array $filters = [])
{
$defaultFilters = [
'role' => "ADMIN",
'active' => null,
'orderBy' => null,
'limit' => null,
];
$filters = array_merge($defaultFilters, $filters);
$role = strtoupper($filters['role']);
$date = new \DateTime();
$sql = "SELECT
U.id,
U.code,
U.username,
U.roles,
U.password,
U.email,
U.quotas,
U.quotas_type AS quotasType,
U.active,
U.created_at AS createdAt,
U.recovery_token AS recoveryToken,
U.last_login AS lastLogin,
U.newsletter,
U.newsletter_date AS newsletterDate,
U.email_updated_at AS emailUpdatedAt,
U.email_validated_at AS emailValidatedAt,
U.first_login AS firstLogin,
U.home_survey AS homeSurvey,
U.status,
U.department_id AS departmentId,
U.company_id AS companyId,
C.name AS companyName,
C.active AS companyActive,
C.status AS companyStatus,
C.subscription_start AS companySubscriptionStart,
C.subscription_end AS companySubscriptionEnd
FROM user AS U
LEFT JOIN company AS C ON U.company_id = C.id
WHERE U.roles LIKE '%\"ROLE_{$role}\"%'
AND C.active = 1
";
if(isset($filters['active'])) {
$sql .= " AND U.active = {$filters['active']} ";
}
if(isset($filters['orderBy'])) {
$sql .= " ORDER BY {$filters['orderBy']} ";
}
if(isset($filters['limit'])) {
$sql .= "LIMIT {$filters['limit']} ";
}
try {
return $this->getQueryResults($sql);
} catch (\Throwable $th) {
return [];
}
}
private function getQueryResults(string $sql) {
$conn = $this->getEntityManager()->getConnection();
$query = $conn->prepare($sql);
$query = $query->executeQuery();
return $query->fetchAllAssociative();
}
private function getQuery(array $criterias, array $orderBy = null, $limit = null, $offset = null)
{
$sql = "SELECT
U.id,
U.code,
U.username,
U.roles,
U.password,
U.email,
U.quotas,
U.quotas_type AS quotasType,
U.active,
U.created_at AS createdAt,
U.recovery_token AS recoveryToken,
U.last_login AS lastLogin,
U.newsletter,
U.newsletter_date AS newsletterDate,
U.email_updated_at AS emailUpdatedAt,
U.email_validated_at AS emailValidatedAt,
U.first_login AS firstLogin,
U.home_survey AS homeSurvey,
U.status,
U.department_id AS departmentId,
U.company_id AS companyId,
C.name AS companyName,
C.active AS companyActive,
C.status AS companyStatus,
C.subscription_start AS companySubscriptionStart,
C.subscription_end AS companySubscriptionEnd,
C.manager_first_name AS companyManagerFirstName,
C.manager_last_name AS companyManagerLastName
FROM user AS U
LEFT JOIN company AS C ON U.company_id = C.id
";
// WHERE
if(!empty($criterias)) {
foreach($criterias as $key => $value) {
$condition = (str_contains($sql, "WHERE")) ? "AND " : "WHERE ";
$sql .= (intval($value)) ? "{$condition} U.{$this->tools->toSnakeCase($key)} = {$value} " : "{$condition} U.{$this->tools->toSnakeCase($key)} LIKE '%{$value}%' ";
}
}
// ORDER BY
if(isset($orderBy) && is_array($orderBy)) {
$sql .= "ORDER BY ";
foreach($orderBy as $key => $value) {
$condition = ($key !== array_key_first($orderBy)) ? ",": "";
$sql .= "{$condition} U.{$this->tools->toSnakeCase($key)} {$value} ";
}
}
// LIMIT
if(isset($limit)) {
$sql .= "LIMIT {$limit}";
}
return $sql;
}
public function getUsersInNativeSql(array $criterias, array $orderBy = null, $limit = null, $offset = null)
{
$sql = $this->getQuery($criterias, $orderBy, $limit, $offset);
return $this->tools->getQueryResults($sql, false);
}
public function getUserByIdInNativeSql(int $id)
{
$sql = $this->getQuery(['id' => $id]);
return $this->tools->getQueryResults($sql, true);
}
/**
* Récupére tous les utilisateurs actifs
*/
public function getActiveUsersInNativeSql(array $filters = null, array $orderBy = null)
{
$sql = "SELECT
U.id,
U.code,
U.username,
U.roles,
U.password,
U.email,
U.quotas,
U.quotas_type AS quotasType,
U.active,
U.created_at AS createdAt,
U.recovery_token AS recoveryToken,
U.last_login AS lastLogin,
U.newsletter,
U.newsletter_date AS newsletterDate,
U.email_updated_at AS emailUpdatedAt,
U.email_validated_at AS emailValidatedAt,
U.first_login AS firstLogin,
U.home_survey AS homeSurvey,
U.status,
U.department_id AS departmentId,
U.company_id AS companyId,
C.name AS companyName,
C.active AS companyActive,
C.status AS companyStatus,
C.subscription_start AS companySubscriptionStart,
C.subscription_end AS companySubscriptionEnd
FROM user AS U
LEFT JOIN company AS C ON U.company_id = C.id
WHERE U.active = 1
AND U.email IS NOT NULL
AND U.last_login IS NOT NULL
AND C.active = 1
AND C.status LIKE 'active'
AND NOW() > C.subscription_start
AND NOW() < C.subscription_end ";
if(!empty($filters)) {
$sql = $this->tools->addAndWhereToQuery($sql, $filters, "U");
}
if(!empty($orderBy)) {
$sql = $this->tools->addOrderByToQuery($sql, $orderBy, "U");
}
$result = $this->tools->getQueryResults($sql);
return $result;
}
public function getUserCountActive($companyId)
{
$sql = "SELECT COUNT(U.id) AS nb_user FROM user AS U WHERE U.company_id = {$companyId} AND U.active = 1 AND U.last_login IS NOT NULL ";
return $this->tools->getQueryResults($sql, true);
}
public function patchIsFirstLogin(int $id, int $isFirstLogin)
{
$result = false;
try {
$sql = "UPDATE user SET user.first_login = '{$isFirstLogin}' WHERE user.id = {$id}";
$conn = $this->getEntityManager()->getConnection();
$query = $conn->prepare($sql);
$query = $query->executeQuery();
$result = true;
} catch (\Throwable $th) {
// throw $th;
}
return $result;
}
public function customGetDisplayTeamplayTuto(int $id)
{
try {
$sql = "
SELECT
user.display_teamplay_tuto
FROM `user`
WHERE user.id = {$id}
";
} catch (\Throwable $th) {
// throw $th;
}
return $this->tools->getQueryResults($sql, true);
}
public function getUserByEmailAndRoles(string $email, string $role)
{
// $query = $this->createQueryBuilder('u')
// ->andWhere('u.email = :email')
// // ->andWhere('u.roles = ROLE_MANAGER"')
// ->setParameter('email', $email)
// ;
// if ($env == "back") {
// $query
// ->andWhere('u.roles IN (:role)')
// ->setParameter('role', json_encode(['ROLE_MANAGER']))
// ;
// }
// return $query->getQuery()->getResult();
$sql = "SELECT * FROM `user` WHERE email = '{$email}' AND roles LIKE '%{$role}%' ORDER BY created_at DESC ";
return $this->tools->getQueryResults($sql, true);
}
}

src/Repository/UserRepository.php line 40