<?php

namespace App\Repository;

use App\Entity\Adherent;
use App\Entity\Comptoir;
use App\Entity\Flux;
use App\Entity\Groupe;
use App\Entity\Prestataire;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Symfony\Bridge\Doctrine\RegistryInterface;

/**
 * @method Flux|null find($id, $lockMode = null, $lockVersion = null)
 * @method Flux|null findOneBy(array $criteria, array $orderBy = null)
 * @method Flux[]    findAll()
 * @method Flux[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class FluxRepository extends ServiceEntityRepository
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Flux::class);
        $em = $this->getEntityManager();
        $this->connection = $em->getConnection();
        $this->tableName =  $em->getMetadataFactory()->getMetadataFor(Flux::class)->getTableName();
    }

   /**
    * @param  Prestataire $presta [description]
    * @return Query Returns a query fo finding an array of Flux
    */
    public function getQueryByPrestataire(Prestataire $presta)
    {
        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.prestataire_id = :id OR f.prestataire_dest_id = :id");
        $statement->bindValue('id', $presta->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');
        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

   /**
    * @param  Adherent $adherent [description]
    * @return Query Returns a query fo finding an array of Flux
    */
    public function getQueryByAdherent(Adherent $adherent)
    {
        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.adherent_id = :id OR f.adherent_dest_id = :id");
        $statement->bindValue('id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');
        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

   /**
    * @param  Comptoir $comptoir [description]
    * @return Query Returns a query fo finding an array of Flux
    */
    public function getQueryByComptoir(Comptoir $comptoir)
    {

        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.comptoir_id = :id");
        $statement->bindValue('id', $comptoir->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');
        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

   /**
    * @param  Groupe $groupe [description]
    * @return Query Returns a query fo finding an array of Flux
    */
    public function getQueryByGroupe(Groupe $groupe)
    {
        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.groupe_id = :id");
        $statement->bindValue('id', $groupe->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');
        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }
}