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\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...)
     * @param string      $type Type of flux (cotisation, transfert, transaction, vente...)
     * @param string      $from Date from which to fetch the flux
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByPrestataire(Prestataire $presta, string $parenttype = null, string $type = null, $from = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id)";
        if (null != $parenttype) {
            $sqlQuery .= ' AND f.parenttype = :type';
        }
        if ($type != null) {
            $sqlQuery .= " AND f.type = :type";
        }
        if ($from != null) {
            $sqlQuery .= " AND f.created_at >= :from";
        }
        $statement = $this->connection->prepare($sqlQuery);
        if (null != $parenttype) {
            $statement->bindValue(':type', $parenttype);
        }
        if ($type != null) {
            $statement->bindValue(':type', $type);
        }
        if ($from != null) {
            $statement->bindValue(':from', $from);
        }
        $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 Prestataire $presta
     * @param bool        $onlySameDay
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByCaissier(Prestataire $presta, $onlySameDay = true)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE ((f.type = 'adherent_prestataire' AND f.prestataire_id = :id) OR (f.type = 'prestataire_prestataire' AND f.prestataire_dest_id = :id))";
        if ($onlySameDay) {
            $sqlQuery .= ' AND DATE(f.created_at) = CURDATE()';
        }
        $statement = $this->connection->prepare($sqlQuery);
        $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()
        ;
    }

    public function getQueryByAdherentAndDestinataire(Adherent $adherent, Prestataire $prestataire, string $type = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.adherent_id = :id AND f.prestataire_id = :presta_id)";
        if ($type != null) {
            $sqlQuery .= " AND f.type = :type";
        }
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':id', $adherent->getId());
        $statement->bindValue(':presta_id', $prestataire->getId());
        if ($type != null) {
            $statement->bindValue(':type', $type);
        }
        $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()
            ->getResult()
        ;
    }

    /**
     * @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 (null != $parenttype) {
            $sqlQuery .= ' AND f.parenttype = :type';
        }
        $statement = $this->connection->prepare($sqlQuery);
        if (null != $parenttype) {
            $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()
        ;
    }

    public function getTotalVenteAchat()
    {
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->select('SUM(f.montant) AS balance')
            ->where("f.type = 'vente' OR f.type = 'achat' OR f.type = 'vente_emlc'")
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * @param Adherent $adherent the user to look the cotisations for
     * @param String $from, $to dates (ISO format) between wich to look for
     * 
     * @return Array list of cotisations between the dates
     */
    public function getTavCotisationsBetweenDates($adherent, $from, $to)
    {
        $sqlQuery = "SELECT f.id
            FROM {$this->tableName} f
            WHERE f.type IN ('achat_monnaie_adherent', 'vente_emlc_adherent')
            AND f.created_at >= :f
            AND f.created_at <= :t
            AND (f.adherent_id = :adh_id OR f.adherent_dest_id = :adh_id)";
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':f', $from);
        $statement->bindValue(':t', $to);
        $statement->bindValue(':adh_id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }

    /**
     * @param Adherent $adherent the user to look the cotisation for
     * 
     * @return 
     */
    public function getLastTavCotisation($adherent)
    {
        $sqlQuery = "SELECT f.created_at
            FROM {$this->tableName} f
            WHERE f.type IN ('achat_monnaie_adherent', 'vente_emlc_adherent')
            AND (f.adherent_id = :adh_id OR f.adherent_dest_id = :adh_id)
            ORDER BY created_at DESC";
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':adh_id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }
}