<?php
namespace App\Repository;
use App\Entity\Employee;
use App\Enum\EmployeeHistory\SalaryType;
use App\Helper\All;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
/**
* @extends ServiceEntityRepository<Employee>
*
* @method Employee|null find($id, $lockMode = null, $lockVersion = null)
* @method Employee|null findOneBy(array $criteria, array $orderBy = null)
* @method Employee[] findAll()
* @method Employee[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class EmployeeRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Employee::class);
}
public function add(Employee $entity, bool $flush = false): void
{
$this->getEntityManager()->persist($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function remove(Employee $entity, bool $flush = false): void
{
$this->getEntityManager()->remove($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function getListTable($q, $filters, $orderByColumn, $orderDirection, $limit, $offset)
{
$query = $this->createQueryBuilder('e')
->leftJoin('e.lastHistory', 'eh')
->leftJoin('eh.department', 'ehd')
->leftJoin('eh.position', 'ehp');
$recordsTotal = count($query->getQuery()->getResult());
if (!empty($filters)) {
if (true === filter_var($filters['active'], FILTER_VALIDATE_BOOLEAN)) {
$query = $query
->andWhere($query->expr()->isNull("eh.endDate"))
->orWhere("eh.endDate >= :today");
} else {
$query = $query
->andWhere($query->expr()->isNotNull("eh.endDate"))
->orWhere("eh.endDate <= :today");
}
if (isset($filters['departments'])) {
//$filters['departments'] = json_decode($filters['departments']);
$query->andWhere('eh.department in (:departments)')->setParameter('departments', $filters['departments']);
}
$query->setParameter('today', (new \DateTime())->format('Y-m-d'));
}
if (!empty($q)) {
$query
->where('e.firstName like :q')
->orWhere('e.lastName like :q')
->orWhere('e.cin like :q')
->setParameters(
[
'q' => '%' . $q . '%',
]
);
}
$recordsFiltered = count($query->getQuery()->getResult());
$query = $query
->groupBy('e.id')
->orderBy($orderByColumn, $orderDirection)
->getQuery();
if (!empty($limit)) {
$query = $query->setMaxResults($limit)->setFirstResult($offset);
}
return [
"recordsTotal" => $recordsTotal,
"recordsFiltered" => $recordsFiltered,
"data" => $query->getResult(),
];
}
public function hourlyEmployees()
{
}
/**
* List of employees with history
*/
public function activeAndHourlyEmployees($onlyQB = false)
{
$qb = $this->createQueryBuilder('e')
->join('e.lastHistory', 'eh')
->join('eh.department', 'ehd')
->join('eh.position', 'ehp');
$qb = $qb
->where($qb->expr()->eq("eh.salaryType", SalaryType::HOURLY->value))
->andWhere($qb->expr()->isNull("eh.endDate"))
->orWhere($qb->expr()->lte("eh.endDate", (new \DateTime())->format('Y-m-d')))
->orderBy('e.id');
//$qb->setMaxResults(60);
if ($onlyQB) {
return $qb;
}
return $qb->getQuery()->getResult();
}
/**
* List of employees with active history in date
*/
public function activeInDateAndHourlyEmployees($startDate, $endDate)
{
$qb = $this->createQueryBuilder('e');
$qb
->join('e.employeeHistories', 'eh', Query\Expr\Join::WITH, $qb->expr()->eq("eh.salaryType", SalaryType::HOURLY->value))
->join('eh.department', 'ehd')
->join('eh.position', 'ehp');
$startDate = (\DateTime::createFromFormat('d/m/Y', $startDate))->format('Y-m-d');
$endDate = (\DateTime::createFromFormat('d/m/Y', $endDate))->format('Y-m-d');
$qb = $qb
//->where('date(eh.endDate) <= :startDate OR date(eh.endDate) >= :endDate')
//02/08 - 04/08
//18/07
->where('date(eh.endDate) >= :startDate OR date(eh.endDate) >= :endDate')
->orWhere($qb->expr()->isNull("eh.endDate"))
->andWhere('date(eh.startDate) <= :endDate')
->setParameters(
[
'startDate' => $startDate,
'endDate' => $endDate
]
)
->orderBy('e.id');
// ->where($qb->expr()->between("eh.endDate", $startDate, All::paramForExpr($endDate)))
// ->orWhere($qb->expr()->isNull("eh.endDate"))
// ->andWhere($qb->expr()->lte("eh.startDate", All::paramForExpr($startDate)))
// ->orderBy('e.lastName')
// ->addOrderBy('e.firstName');
// echo $startDate;
// echo "<br>";
// echo $endDate;
// echo "<br>";
// die();
// foreach ($qb->getQuery()->getResult() as $item){
// /** @var Employee $item */
// $endDate = !empty($item->getLastHistory()->getEndDate()) ? $item->getLastHistory()->getEndDate()->format('d/m/Y H:i:s') : '';
// echo $item->getFullName() . " - ".$item->getId() . " - ". $item->getLastHistory()->getStartDate()->format('d/m/Y H:i:s') . " - " . $endDate;
// echo "<br>";
// }
//
// die();
//dd($qb->getQuery()->getSQL(), $startDate, $endDate);
return $qb->getQuery()->getResult();
}
/**
* @TODO: TO REFACTO AND REMOVE
*/
public function activeAndHourlyLogForEmployees($employee = false, $date = false)
{
/** @var QueryBuilder $qb */
$qb = $this->activeAndHourlyEmployees(true);
$conditionType = null;
$condition = null;
if (!empty($employee)) {
$qb->andWhere('e.id = :employee')->setParameter('employee', $employee);
}
if (!empty($date)) {
$conditionType = Query\Expr\Join::WITH;
$condition = $qb->expr()->like('ehl.date', ':logDate');
$qb->setParameter('logDate', $date->format('Y-m-d') . '%');
}
$qb->leftJoin('e.employeeHourLogs', 'ehl', $conditionType, $condition);
$qb->orderBy('eh.department');
$qb->addOrderBy('ehp.id');
return $qb->getQuery()
->getResult();
}
public function getOnlyMachinary(){
/** @var QueryBuilder $qb */
$qb = $this->activeAndHourlyEmployees(true);
return $qb->andWhere('ehd = :department')->setParameter('department', 4)->getQuery()->getResult();
}
}