FluxRepository.php

<?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 Doctrine\Common\Persistence\ManagerRegistry;

/**
 * @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(ManagerRegistry $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]
     * @param  string   $parenttype Parent type of flux (cotisation, transfert, transaction, vente...)
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByPrestataire(Prestataire $presta, $parenttype = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id)";
        if ($parenttype != null) {
            $sqlQuery .= ' AND f.parenttype = :type';
        }
        $statement = $this->connection->prepare($sqlQuery);
        if ($parenttype != null) {
            $statement->bindValue(':type', $parenttype);
        }
        $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]
     * @param  string   $parenttype Parent type of flux (cotisation, transfert, transaction, vente...)
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByAdherent(Adherent $adherent, $parenttype = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.adherent_id = :id OR f.adherent_dest_id = :id)";
        if ($parenttype != null) {
            $sqlQuery .= ' AND f.parenttype = :type';
        }
        $statement = $this->connection->prepare($sqlQuery);
        if ($parenttype != null) {
            $statement->bindValue(':type', $parenttype);
        }
        $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  Comptoir $comptoir [description]
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByComptoirParams(Comptoir $comptoir, $params)
    {
        $sql = "SELECT f.id FROM {$this->tableName} f WHERE f.comptoir_id = :id";
        if (isset($param['parenttype'])) {
            $sql.= ' AND f.parenttype = :parenttype';
        }
        if (isset($param['type'])) {
            $sql.= ' AND f.type = :type';
        }
        if (isset($param['start'])) {
            $sql.= ' AND f.created_at > :start';
        }
        if (isset($param['end'])) {
            $sql.= ' AND f.created_at < :end';
        }
        $statement = $this->connection->prepare($sql);
        $statement->bindValue('id', $comptoir->getId());
        if (isset($param['parenttype'])) {
            $statement->bindValue('parenttype', $param['parenttype']);
        }
        if (isset($param['type'])) {
            $statement->bindValue('type', $param['type']);
        }
        if (isset($param['start'])) {
            $statement->bindValue('start', $param['start']);
        }
        if (isset($param['end'])) {
            $statement->bindValue('end', $param['end']);
        }
        $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()
        ;
    }
}