<?php
namespace App\Repository;
use App\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr\Orx;
use Doctrine\Persistence\ManagerRegistry;
class UserRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, User::class);
}
public function findManyUsers($ids)
{
$qb = $this->createQueryBuilder('U');
$qb->where($qb->expr()->in('U.id', $ids));
return $qb->getQuery()->getResult();
}
/**
* Find all users with roles and company
* @param $company
* @param $role
* @return array
*/
public function findByRoleAndCompany($role, $company)
{
return $this->createQueryBuilder('U')
->leftJoin('U.roles', 'R')
->addSelect('R')
->where('U.company = :value')->setParameter('value', $company)
->Where('R.name = :value')->setParameter('value', $role)
->getQuery()
->getResult();
}
/**
* Get all users for datatable query
*/
public function getQueryUsers($roles, $company, $isContact = null,$hasSites=false)
{
$queryBuilder = $this->createQueryBuilder('u');
$query = $queryBuilder
->select('u.id')
->addSelect('u.lastname')
->addSelect('u.firstname')
->addSelect('u.email')
->addSelect('u.updatedAt')
->addSelect('u.lastLoginAt')
->addSelect('u.isContact as contact')
->addSelect('d.name as department')
->addSelect('r.name as role')
// ->orderBy('u.lastLoginAt','DESC')
->leftJoin('u.roles', 'r')
->leftJoin('u.department', 'd')
->where('r.name IN (:roles)')->setParameter('roles', $roles)
->andWhere('u.company = :company')->setParameter('company', $company);
if($hasSites){
$query->addSelect('s.name as site');
$query ->leftJoin('u.site','s');
}
if ($isContact != null) {
$query->andWhere('u.isContact = :contact')->setParameter('contact', $isContact);
}
return $query;
}
/**
* Get all users for datatable query
*/
public function getQueryUsersWithStat($roles, $company, $type)
{
$qb = $this->createQueryBuilder('u');
$query =
$qb->select('u.id')
->addSelect('u.lastname')
->addSelect('u.firstname')
->addSelect('d.name as department')
->leftJoin('u.roles', 'r')
->leftJoin('u.department', 'd')
->where('r.name IN (:roles)')->setParameter('roles', $roles)
->andWhere('u.company = :company')->setParameter('company', $company)
->leftJoin('u.userActivities', 'ua')
->leftJoin('ua.activity', 'a')
->leftJoin('a.category', 'cat')
// ->addSelect('COUNT(cat.id) AS nb_question')
->andWhere('cat.profile_type = :type')->setParameter('type', $type)
// ->addSelect($qb->expr()->countDistinct('a.id') . ' AS completion')
->groupBy('u.id');
return $query;
}
/**
* Get all users for one company
*/
public function getUsersForOneCompany($company, $roles = null) { if ($roles === null) { $roles = User::getStandardRoleNames(); } $queryBuilder = $this->createQueryBuilder('u');
$query = $queryBuilder
// ->select('u.id')
// ->addSelect('u.lastname')
// ->addSelect('u.firstname')
// ->addSelect('u.email')
// ->addSelect('u.updatedAt')
// ->addSelect('d.name as department')
->leftJoin('u.roles', 'r')
->leftJoin('u.department', 'd')
->where('r.name IN (:roles)')->setParameter('roles', $roles)
->andWhere('u.company = :company')->setParameter('company', $company);
return $query->getQuery()->getResult();
}
public function removeDepartment($company){
$queryBuilder = $this->createQueryBuilder('u');
$queryBuilder->update('App:User', 'u')
->set('u.department', ':department')
->where('u.company = :company')
->setParameter('department', null)
->setParameter('company', $company)
->getQuery()->execute();
}
public function removeJob($company){
$queryBuilder = $this->createQueryBuilder('u');
$queryBuilder->update('App:User', 'u')
->set('u.job', ':job')
->where('u.company = :company')
->setParameter('job', null)
->setParameter('company', $company)
->getQuery()->execute();
}
public function findRHORContactEmailForCompany($company,$isContact=true){
$queryBuilder = $this->createQueryBuilder('U');
$q = $queryBuilder
->select('U')
->leftJoin('U.roles', 'R')
->where('U.company = :value')->setParameter('value', $company);
if($isContact){
$q->andWhere(
new Orx([
"U.isContact = 1",
"R.name = 'ROLE_RH' ",
])
);
}
else{
$q->andWhere("R.name = 'ROLE_RH'");
}
return $q->getQuery()->getResult();
}
public function getWithRoleAndJob($user){
$queryBuilder = $this->createQueryBuilder('u');
$q = $queryBuilder
->select('u.id')
->addSelect('u.lastname')
->addSelect('u.firstname')
->addSelect('u.email')
->addSelect('u.lastLoginAt')
->addSelect('d.name as department')
->addSelect('r.name as role')
->addSelect('j.name as job')
->leftJoin('u.roles', 'r')
->leftJoin('u.department', 'd')
->leftJoin('u.job', 'j')
->where('u = :user')->setParameter('user', $user);
return $q->getQuery()->getResult();
}
public function findUsersNoYetRegisteredFromDate($startDate,$endDate)
{
$queryBuilder = $this->createQueryBuilder('u');
$result = $queryBuilder
->select('u')
->andWhere('u.createdAt > :start')
->setParameter('start', $startDate)
->andWhere('u.createdAt < :end')
->setParameter('end', $endDate)
->andWhere('u.password is null')
->getQuery()
->getResult()
;
return $result;
}
public function findUsersRegistered($company)
{
$queryBuilder = $this->createQueryBuilder('u');
$result = $queryBuilder
->select('u')
->leftJoin('u.department', 'd')
->andWhere('u.company = :company')->setParameter('company', $company)
->andWhere('u.password is not null')
->getQuery()
->getResult()
;
return $result;
}
/**
* @return User[]
*/
public function findBySearchQuery(string $rawQuery,$company): array
{
$query = $this->sanitizeSearchQuery($rawQuery);
$searchTerms = $this->extractSearchTerms($query);
if (0 === count($searchTerms)) {
return [];
}
$queryBuilder = $this->createQueryBuilder('u');
$queryBuilder
->select('u.id')
->addSelect('u.lastname')
->addSelect('u.firstname');
foreach ($searchTerms as $key => $term) {
$queryBuilder
->orWhere('u.lastname LIKE :t_' . $key)
->orWhere('u.firstname LIKE :t_' . $key)
->setParameter('t_' . $key, '%' . $term . '%');
}
return $queryBuilder
->andWhere('u.company = :company')->setParameter('company', $company)
->orderBy('u.lastname', 'DESC')
->getQuery()
->getResult();
}
/**
* Removes all non-alphanumeric characters except whitespaces.
*/
private function sanitizeSearchQuery(string $query): string
{
return trim(preg_replace('/[[:space:]]+/', ' ', $query));
}
/**
* Splits the search query into terms and removes the ones which are irrelevant.
*/
private function extractSearchTerms(string $searchQuery): array
{
$terms = array_unique(explode(' ', $searchQuery));
return array_filter($terms, function ($term) {
return 2 <= mb_strlen($term);
});
}
}