<?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...) * * @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 (null != $parenttype) { $sqlQuery .= ' AND f.parenttype = :type'; } $statement = $this->connection->prepare($sqlQuery); if (null != $parenttype) { $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 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) { $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.adherent_id = :id OR f.prestataire_id = :presta_id)"; $statement = $this->connection->prepare($sqlQuery); $statement->bindValue(':id', $adherent->getId()); $statement->bindValue(':presta_id', $prestataire->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() ->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(); } }