<?php
namespace App\Repository;
use App\Entity\Client;
use App\Entity\Company;
use App\Entity\Contract;
use App\Entity\Intervenant;
use App\Entity\Product;
use App\Entity\RDV;
use App\Entity\RDVProduct;
use App\Entity\Salon;
use App\Entity\Site;
use App\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Bridge\Doctrine\RegistryInterface;
/**
* @method RDV|null find($id, $lockMode = null, $lockVersion = null)
* @method RDV|null findOneBy(array $criteria, array $orderBy = null)
* @method RDV[] findAll()
* @method RDV[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class RDVRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, RDV::class);
}
public function countRDVsBySalonAndDate(Salon $salon, \DateTime $date)
{
$startOfDay = $date->setTime(0, 0, 0);
$endOfDay = clone $startOfDay;
$endOfDay->setTime(23, 59, 59);
return $this->createQueryBuilder('r')
->innerJoin('r.salon', 's')
->where('s.id = :salon')
->andWhere('r.start BETWEEN :start AND :end')
->andWhere('r.status != :cancel')
->setParameter('salon', $salon->getId())
->setParameter('cancel', RDV::STATUS_ANNULER)
->setParameter('start', $startOfDay)
->setParameter('end', $endOfDay)
->getQuery()->getResult();
}
/**
* @param Site $site
* @return mixed
*/
public function findForFusion()
{
return $this->createQueryBuilder('r')
//&& $rdv->getStatus() === RDV::STATUS_PLANIFIER && $rdv2->getStatus() === RDV::STATUS_PLANIFIER
->where('r.status IN (:status)')
->setParameter('status', [RDV::STATUS_PLANIFIER, RDV::STATUS_REPLANIFIER])
->getQuery()->getResult();
}
/**
* @param Site $site
* @return mixed
*/
public function findForPlanning()
{
$date = (new \DateTime('now'))->modify('+3 day')->format('Y-m-d 23:59:59');
$dateMinus = (new \DateTime('now'))->modify('-3 day')->format('Y-m-d 00:00:00');
return $this->createQueryBuilder('r')
//&& $rdv->getStatus() === RDV::STATUS_PLANIFIER && $rdv2->getStatus() === RDV::STATUS_PLANIFIER
->where('r.start <= :date')
->andWhere('r.start >= :dateMinus')
->setParameter('date', $date)
->setParameter('dateMinus', $dateMinus)
->getQuery()->getResult();
}
public function findForExport($dateDebut = null, $dateFin = null)
{
// Define the date 6 months ago from the current date
$dateMinus = (new \DateTime('now'))->modify('-6 months')->format('Y-m-d 00:00:00');
if($dateDebut){
$dateMinus = $dateDebut;
}
// Define the date 6 months in the future from the current date
$datePlus = (new \DateTime('now'))->modify('+6 months')->format('Y-m-d 23:59:59');
if($dateFin){
$datePlus = $dateFin;
}
return $this->createQueryBuilder('r')
->where('r.start >= :dateMinus')
->andWhere('r.start <= :datePlus')
->orderBy('r.id','desc')
->setParameter('dateMinus', $dateMinus)
->setParameter('datePlus', $datePlus)
->getQuery()
->getResult();
}
/**
* @param Site $site
* @return mixed
*/
public function findForTomorrow()
{
$tomorrow = (new \DateTime())->modify('+1 day')->format('Y-m-d 00:00:00');
$tomorrowEnd = (new \DateTime())->modify('+1 day')->format('Y-m-d 23:59:59');
$status = [RDV::STATUS_PLANIFIER, RDV::STATUS_REPLANIFIER];
return $this->createQueryBuilder('r')
->where('r.start >= :date')
->andWhere('r.start <= :dateEnd')
->andWhere('r.status IN (:status)')
->setParameter('date', $tomorrow)
->setParameter('dateEnd', $tomorrowEnd)
->setParameter('status', $status)
->getQuery()->getResult();
}
/**
* @param Site $site
* @return mixed
*/
public function findBySite($site)
{
return $this->createQueryBuilder('r')
->leftJoin('r.salon', 's')
->innerJoin('s.site', 'site')
->where('s.site = :site')
->setParameter('site', $site)
->getQuery()->getResult();
}
/**
* @param Site $site
* @return mixed
*/
public function findFromToday()
{
return $this->createQueryBuilder('r')
->where('r.start > :date')
->setParameter('date', new \DateTime())
->orderBy('r.start')
->setMaxResults(100)
->getQuery()->getResult();
}
public function findByCompany($company)
{
return $this->createQueryBuilder('r')
->innerJoin('r.intervenant', 'i')
->innerJoin('i.workContract', 'work')
->where('work.company = :company')
->setParameter('company', $company)
->getQuery()->getResult();
}
public function replanifierIntervenant(Intervenant $intervenant, $date)
{
return $this->createQueryBuilder('r')
->innerJoin('r.intervenant', 'i')
->where('r.intervenant = :intervenant')
->andWhere('DATE_DIFF(r.start, :date) = 0')
->andWhere('r.status = :status OR r.status = :statusRepla')
->setParameter('date', $date)
->setParameter('status', RDV::STATUS_PLANIFIER)
->setParameter('statusRepla', RDV::STATUS_REPLANIFIER)
->setParameter('intervenant', $intervenant)
->getQuery()->getResult();
}
public function replanifierDate(Intervenant $intervenant, Salon $salon, $date)
{
return $this->createQueryBuilder('r')
->innerJoin('r.intervenant', 'i')
->where('r.intervenant = :intervenant')
->andWhere('DATE_DIFF(r.start, :date) = 0')
->andWhere('r.salon = :salon')
->andWhere('r.status = :status OR r.status = :statusRepla')
->setParameter('date', $date)
->setParameter('status', RDV::STATUS_PLANIFIER)
->setParameter('salon', $salon)
->setParameter('statusRepla', RDV::STATUS_REPLANIFIER)
->setParameter('intervenant', $intervenant)
->getQuery()->getResult();
}
public function findOneByClientAndToken($client)
{
return $this->createQueryBuilder('r')
->where('r.client = :client')
->andWhere('r.orderId IS NOT NULL')
->andWhere('r.status = :status')
->andWhere('r.isPlatform = 1')
->orderBy('r.id','DESC')
->setParameter('client', $client->getId())
->setParameter('status', RDV::STATUS_FACTURER)
->setMaxResults(1) // Set maximum number of results to 1
->getQuery()
->getOneOrNullResult();
}
public function findByFilter(
$intervenant = null,
$site = null,
$client = null,
$start = null,
$end = null,
$status = null,
$clientId = null,
$user = null,
$salon = null
) {
$qr = $this->createQueryBuilder('r')
->leftJoin('r.intervenant', 'i')
->leftJoin('r.client', 'c')
->leftJoin('c.site', 's');
$filter = false;
if ($intervenant) {
$qr->andWhere('i.id = :intervenant');
$qr->setParameter('intervenant', $intervenant);
$filter = true;
}
if ($site) {
$qr->andWhere('s.id = :site');
$qr->setParameter('site', $site);
$filter = true;
}
if ($salon) {
$qr->andWhere('r.salon = :salon');
$qr->setParameter('salon', $salon->getId());
$filter = true;
}
if ($client) {
$searchTermArray = explode(' ', $client);
foreach ($searchTermArray as $searchItem){
$qr->andWhere('c.firstname LIKE :firstname OR c.lastname LIKE :lastname');
$qr->setParameter('firstname', '%' . $searchItem. '%');
$qr->setParameter('lastname', '%' . $searchItem . '%');
}
// $qr->andWhere('c.firstname LIKE :client OR c.lastname LIKE :client OR CONCAT(c.firstname,\' \',c.lastname) LIKE :client');
// $qr->setParameter('client', '%' . $client . '%');
$filter = true;
}
if ($clientId) {
$qr->andWhere('c.id = :clientId');
$qr->setParameter('clientId', $clientId);
$filter = true;
}
if ($status) {
//$statusString = implode(',', $status);
$qr->andWhere('r.status IN (:status)');
$qr->setParameter('status', $status);
$filter = true;
}
$date = new \DateTime();
if ($start) {
$date = \DateTime::createFromFormat('Y-m-d', $start);
if ($date instanceof \DateTime) {
$date->setTime(0, 0, 0);
} else {
$date = new \DateTime();
}
}
$qr->andWhere('r.start >= :date')
->setParameter('date', $date->format('Y-m-d H:i:s'))
->orderBy('r.start');
if ($end) {
$date = \DateTime::createFromFormat('Y-m-d', $end);
$date->setTime(23, 59, 59);
$qr->andWhere('r.start <= :end');
$qr->setParameter('end', $date->format('Y-m-d H:i:s'));
}
if ($user && $user->hasRole(User::ROLE_COIFFEUR)) {
$qr->andWhere('i.user = :user');
$qr->setParameter('user', $user);
$filter = true;
}
if (!$filter) {
$qr->setMaxResults(100);
}
return $qr->getQuery()->getResult();
}
public function findByFilterCoiffeuse(
$intervenant = null,
$site = null,
$client = null,
$start = null,
$end = null,
$status = null,
$clientId = null,
$user = null,
$salon = null
) {
$qr = $this->createQueryBuilder('r')
->select('r.id, c.roomNumber AS roomNumber, r.start, r.priorite, r.status, c.lastname AS lastname,r.comment, c.firstname AS firstname, c.id as client_id, rec.nbSemaine')
->leftJoin('r.intervenant', 'i')
->leftJoin('r.client', 'c')
->leftJoin('c.site', 's')
->leftJoin('r.recurrenceRdvs', 'rec');
$filter = false;
if ($intervenant) {
$qr->andWhere('i.id = :intervenant');
$qr->setParameter('intervenant', $intervenant);
$filter = true;
}
if ($salon) {
$qr->andWhere('r.salon = :salon');
$qr->setParameter('salon', $salon->getId());
$filter = true;
}
if ($site) {
$qr->andWhere('s.id = :site');
$qr->setParameter('site', $site);
$filter = true;
}
if ($client) {
$qr->andWhere('c.firstname LIKE :client OR c.lastname LIKE :client OR CONCAT(c.firstname,\' \',c.lastname) LIKE :client');
$qr->setParameter('client', '%' . $client . '%');
$filter = true;
}
if ($clientId) {
$qr->andWhere('c.id = :clientId');
$qr->setParameter('clientId', $clientId);
$filter = true;
}
if ($status) {
$qr->andWhere('r.status IN (:status)');
$qr->setParameter('status', $status);
}
$date = new \DateTime();
if ($start) {
$date = \DateTime::createFromFormat('Y-m-d', $start);
if ($date instanceof \DateTime) {
$date->setTime(0, 0, 0);
} else {
$date = new \DateTime();
}
}
$qr->andWhere('r.start >= :date')
->setParameter('date', $date->format('Y-m-d H:i:s'))
->orderBy('r.start');
if ($end) {
$date = \DateTime::createFromFormat('Y-m-d', $end);
$date->setTime(23, 59, 59);
$qr->andWhere('r.start <= :end');
$qr->setParameter('end', $date->format('Y-m-d H:i:s'));
}
if ($user && $user->hasRole(User::ROLE_COIFFEUR)) {
$qr->andWhere('i.user = :user');
$qr->setParameter('user', $user);
}
$qr->andWhere('s.id != 146');
if (!$filter) {
$qr->setMaxResults(50);
}
return $qr->getQuery()->getResult();
}
/**
* @param null $start
* @param null $end
* @return mixed
*/
public function findForBilling($start = null, $end = null, $status, $company, $site)
{
$qr = $this->createQueryBuilder('r')
->select('r.id', 'r.start', 'r.end', 'c.id as client_id', 's.id as site_id')
->leftJoin('r.intervenant', 'i')
->leftJoin('r.client', 'c')
->leftJoin('c.billingInfos', 'bi')
->leftJoin('c.site', 's');
if ($start) {
$date = \DateTime::createFromFormat('Y-m-d', $start);
if ($date instanceof \DateTime) {
$date->setTime(0, 0, 0);
}
$qr->andWhere('r.start >= :start');
$qr->setParameter('start', $date);
}
if ($end) {
$date2 = \DateTime::createFromFormat('Y-m-d', $end);
if ($date2 instanceof \DateTime) {
$date2->setTime(23, 59, 59);
}
$qr->andWhere('r.end <= :end');
$qr->setParameter('end', $date2);
}
if ($company) {
$qr->andWhere('bi.company = :company'); // company of client
$qr->setParameter('company', $company->getId());
}
if ($site) {
$qr->andWhere('s.id = :site');
$qr->setParameter('site', $site->getId());
}
$qr->andWhere('r.status = :status');
$qr->setParameter('status', $status);
//$qr->groupBy('r.client');
return $qr->getQuery()->getArrayResult();
}
public function findForReplanification($rdvs)
{
$qr = $this->createQueryBuilder('r')
->where('r.id IN (:rdvs)');
$qr->setParameter('rdvs', $rdvs)
//->where('r.status = :status')
// ->andWhere('r.start >= :start')
// ->andWhere('r.start <= :end')
// ->setParameter('start', $start)
// ->setParameter('end', $end)
// ->setParameter('status', RDV::STATUS_PLANIFIER)
;
//$qr->groupBy('r.client');
return $qr->getQuery()->getResult();
}
public function findForReplanificationByContract($clientContrats, $date, $salon)
{
$qr = $this->createQueryBuilder('r')
->where('r.contract IN (:contract)')
->andWhere('r.status = :status')
->andWhere('r.salon = :salon')
->andWhere('r.start > :date')
->orderBy('r.start')
->setParameter('contract', $clientContrats)
->setParameter('salon', $salon->getId())
->setParameter('date', $date->format('Y-m-d'))
->setParameter('status', RDV::STATUS_PLANIFIER);
//$qr->groupBy('r.client');
return $qr->getQuery()->getResult();
}
public function findForReplanificationNewRecurence(Client $client, $date, $salon)
{
$qr = $this->createQueryBuilder('r')
->where('r.newRecurrence = true')
->andWhere('r.status = :status')
->andWhere('r.client = :client')
->andWhere('r.salon = :salon')
->andWhere('r.start > :date')
->orderBy('r.start')
->setParameter('client', $client->getId())
->setParameter('salon', $salon->getId())
->setParameter('date', $date->format('Y-m-d'))
->setParameter('status', RDV::STATUS_PLANIFIER);
//$qr->groupBy('r.client');
return $qr->getQuery()->getResult();
}
public function findForIntervenantReplanification(Intervenant $intervenant, \DateTime $date, $site)
{
$date->setTime(0, 0, 0);
$qr = $this->createQueryBuilder('r')
->innerJoin('r.salon', 'sa')
->innerJoin('sa.site', 's')
->where('r.intervenant = :intervenant')
->andWhere('r.status IN (:status)')
->andWhere('r.start >= :date');
if ($site) {
$qr->andWhere('s.id = :site')->setParameter('site', $site->getId());
}
$qr->setParameter('intervenant', $intervenant->getId())
->setParameter('date', $date->format('Y-m-d'))
->setParameter('status', [RDV::STATUS_PLANIFIER, RDV::STATUS_REPLANIFIER]);
//$qr->groupBy('r.client');
return $qr->getQuery()->getResult();
}
public function findForRdvByStatus()
{
return $this->createQueryBuilder('r')
->andWhere('r.paymentLink IS NOT NULL')
->andWhere('r.status = :status')
->setParameter('status', RDV::STATUS_NON_PAYE)
->getQuery()
->getResult();
}
public function findForRdvWithSalonAndStatus($salon)
{
return $this->createQueryBuilder('r')
->where('r.salon = :salon')
->andWhere('r.status != :status')
->setParameter('status', RDV::STATUS_ANNULER)
->setParameter('salon', $salon)
->getQuery()
->getResult();
}
public function findRdvWithLinkId($linkId)
{
return $this->createQueryBuilder('r')
->where('r.paymentLink LIKE :linkId')
->setParameter('linkId', '%/' . $linkId)
->getQuery()
->getOneOrNullResult();
}
public function getRDVsByClient(Client $client)
{
$subQuery = $this->createQueryBuilder('r')
->select('r.id');
$subQuery->leftJoin('r.client', 'c');
$subQuery->andWhere('c.id = :clientId')
->setParameter('clientId', $client->getId());
return $subQuery->getQuery()->getArrayResult();
}
public function getLastRDVsByClient(Client $client)
{
$planifie = RDV::STATUS_VALIDATED;
$facturer = RDV::STATUS_FACTURER;
// Create the QueryBuilder instance to construct the query
$qb = $this->createQueryBuilder('r')
->select('r') // select full RDV entities, adjust as needed
->leftJoin('r.client', 'c')
->andWhere('c.id = :clientId')
->andWhere("r.status = :facturer OR r.status = :plan")
->setParameter('facturer', $facturer)
->setParameter('plan', $planifie)
->setParameter('clientId', $client->getId())
->orderBy('r.id', 'DESC') // assuming 'date' is the field to sort by
->setMaxResults(1); // limit the result to get only the most recent one
// Execute the query and return the result
return $qb->getQuery()->getOneOrNullResult(); // Use getOneOrNullResult() to get a single result or null if none found
}
public function deleteRDVsByClient(Client $client)
{
$subQuery = $this->createQueryBuilder('r')
->select('r.id');
$subQuery->leftJoin('r.client', 'c');
$subQuery->andWhere('c.id = :clientId')
->setParameter('clientId', $client->getId());
// Create the DELETE statement with the subquery in the WHERE clause
$qb = $this->createQueryBuilder('rdv');
$qb->delete()
->where($qb->expr()->in('rdv.id', $subQuery->getDQL()))
->setParameter('clientId', $client->getId());
// Execute the DELETE statement
$qb->getQuery()->execute();
}
public function findByIdIntervenantAndDate( $intervenantId=null, $start=null,$end=null)
{
$q = $this->createQueryBuilder('r')
->innerJoin('r.intervenant', 'i')
->where('i.id = :intervenant')
->setParameter('intervenant', $intervenantId);
$date = \DateTime::createFromFormat('Y-m-d', $start);
$date->setTime(0, 0, 0);
$q->andWhere('r.start >= :start')
->setParameter('start', $date->format('Y-m-d H:i:s'));
if ($end) {
$date = \DateTime::createFromFormat('Y-m-d', $end);
$date->setTime(23, 59, 59);
$q->andWhere('r.start <= :end');
$q->setParameter('end', $date->format('Y-m-d H:i:s'));
}
return $q->getQuery()->getResult();
}
public function findFromTodayByStatusPlan(Client $client) {
return $this->createQueryBuilder('r')
->where('r.start > :date')
->andWhere('r.client = :client')
->andWhere('r.status = :plan OR r.status = :replan')
->setParameter('date', new \DateTime())
->setParameter('client', $client->getId())
->setParameter('plan', RDV::STATUS_PLANIFIER)
->setParameter('replan', RDV::STATUS_REPLANIFIER)
->getQuery()->getResult();
}
}