<?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 App\Entity\Transaction; use App\Entity\User; 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 $from Date from which to fetch the flux * @param array $filter For each element of array, add condition f.key = value * * @return Query Returns an array of flux ids matching conditions */ private function getQueryByPrestataireCore(Prestataire $presta, $filter = null, $from = null, $to = null) { $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id)"; if ($from != null) { $sqlQuery .= " AND f.created_at >= :from"; } if ($to != null) { $sqlQuery .= " AND f.created_at <= :to"; } $i = 0; foreach ($filter as $key => $value) { if($value === null) { //for convenience $sqlQuery .= " AND f." . $key . " IS NULL"; } else { $sqlQuery .= " AND f." . $key . " = :param_$i"; } $i++; } $statement = $this->connection->prepare($sqlQuery); if ($from != null) { $statement->bindValue(':from', $from); } if ($to != null) { $statement->bindValue(':to', $to); } $i = 0; foreach ($filter as $key => $value) { if($value !== null) { $statement->bindValue(":param_$i", $value); } $i++; } $statement->bindValue(':id', $presta->getId()); $statement->execute(); return $statement->fetchAll(); } /** * @param Prestataire $presta [description] * @param integer $m From 1 to 12 * @param integer $Y Format YYYY */ public function getValidCcasTransactionsByPrestaAndMonth(Prestataire $presta, $m, $Y) { //It's easier to write full SQL query because we want to join adherent to select and sort by anonymous token $sqlQuery = " SELECT a.anonymous_token as anonymous_token, f.montant as montant, f.created_at as created_at FROM {$this->tableName} f JOIN adherent a ON f.adherent_id = a.id WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id) AND f.created_at >= :from AND f.created_at <= :to AND f.cancellerflux_id IS NULL AND f.is_ccas = :is_ccas AND f.type = :type ORDER BY a.anonymous_token ASC, f.created_at ASC "; $statement = $this->connection->prepare($sqlQuery); $statement->bindValue(':id', $presta->getId()); $from = new \DateTime(); $from->setDate($Y,$m,1)->setTime(0,0); $statement->bindValue(':from', $from->format("Y-m-d H:i:s")); $to = new \DateTime(); $to->setDate($Y,$m+1,0)->setTime(23,59,59); $statement->bindValue(':to', $to->format("Y-m-d H:i:s")); $statement->bindValue(":is_ccas", true); $statement->bindValue(":type", Transaction::TYPE_TRANSACTION_ADHERENT_PRESTATAIRE); $statement->execute(); return $statement->fetchAll(); } /** * @param Prestataire $presta [description] * @param integer $m From 1 to 12 * @param integer $Y Format YYYY */ public function getSumOfValidCcasTransactionsByPrestaAndMonth(Prestataire $presta, $m, $Y) { $filter = array(); $filter['is_ccas'] = true; $filter['cancellerflux_id'] = null; //exclude cancelled transactions $filter['type'] = Transaction::TYPE_TRANSACTION_ADHERENT_PRESTATAIRE; //exclude cancellers $from = new \DateTime(); $from->setDate($Y,$m,1)->setTime(0,0); $to = new \DateTime(); $to->setDate($Y,$m+1,0)->setTime(23,59,59); $ids = $this->getQueryByPrestataireCore( $presta, $filter, $from->format("Y-m-d H:i:s"), $to->format("Y-m-d H:i:s") ); $qb = $this->createQueryBuilder('f'); return $qb ->select('sum(f.montant)') ->where($qb->expr()->in('f.id', ':ids')) ->setParameter('ids', $ids) ->orderBy('f.createdAt', 'DESC') ->getQuery() ->getSingleScalarResult(); } /** * @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 for finding an array of Flux */ public function getQueryByPrestataire(Prestataire $presta, string $parenttype = null, string $type = null, $from = null) { $filter = array(); if ($parenttype) { $filter['parenttype'] = $parenttype; } if ($type) { $filter['type'] = $type; } $results = $this->getQueryByPrestataireCore($presta,$filter,$from); $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() ; } /** * @param User $user * * @return Query Returns a query fo finding an array of Flux */ public function getQueryByUser(User $user) { $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE f.user_id = :id"; $statement = $this->connection->prepare($sqlQuery); $statement->bindValue(':id', $user->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 = :type_vente OR f.type = :type_achat OR f.type = :type_venteemlc") ->setParameter('type_vente', Flux::TYPE_VENTE) ->setParameter('type_achat', Flux::TYPE_ACHAT) ->setParameter('type_venteemlc', Flux::TYPE_VENTE_EMLC) ->getQuery() ->getSingleScalarResult(); } public function findCountByParams(array $param) { try { $qb = $this->getStatsParamsWhere($param); $qb->addSelect('COUNT(f.id) AS total'); return $qb->getQuery()->useResultCache(true, 60)->getResult(); } catch(DBALException $e) { return $e; } } public function findSumByParams(array $param) { try { $qb = $this->getStatsParamsWhere($param); $qb->addSelect('SUM(f.montant) AS total'); return $qb->getQuery()->useResultCache(true, 60)->getResult(); } catch(DBALException $e) { return $e; } } public function getStatsParamsWhere(array $param) { $qb = $this->createQueryBuilder('f'); $groupBy = isset($param['groupBy'])?$param['groupBy']:'month'; if ($groupBy == 'month') { $qb ->addSelect('CONCAT(YEAR(f.createdAt), \'-\', MONTH(f.createdAt)) AS month') ->groupBy('month') ; } else if ($groupBy == 'year') { $qb ->addSelect('YEAR(f.createdAt) AS year') ->groupBy('year') ; } else if ($groupBy == 'day') { $qb ->addSelect('DATE(f.createdAt) AS day') ->groupBy('day') ; } if (isset($param['moyen'])) { $qb->andWhere('f.moyen = :moyen'); } if (isset($param['parenttype'])) { $qb->andWhere('f.parenttype = :parenttype'); } if (isset($param['type'])) { $qb->andWhere('f.type = :type'); } // if (isset($param['start'])) { $qb->andWhere('f.createdAt > :start'); // } if (isset($param['end'])) { $qb->andWhere('f.createdAt < :end'); } if (isset($param['moyen'])) { $qb->setParameter('moyen', $param['moyen']); } if (isset($param['parenttype'])) { $qb->setParameter('parenttype', $param['parenttype']); } if (isset($param['type'])) { $qb->setParameter('type', $param['type']); } if (isset($param['start'])) { $qb->setParameter('start', new \DateTime($param['start'])); } else { $qb->setParameter('start', new \DateTime('-1 year')); } if (isset($param['end'])) { $qb->setParameter('end', $param['end']); } return $qb; } /** * @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; } /** * @param String $subterritory_name the subterritory name to look for * * @return */ public function getQuerybyAdherentSubterritory($subterritory_name) { $sqlQuery = "SELECT f.id FROM {$this->tableName} f LEFT JOIN adherent a ON (a.id = f.adherent_id OR a.id = f.adherent_dest_id) LEFT JOIN geoloc g ON a.geoloc_id = g.id LEFT JOIN subterritory st ON g.subterritory_id = st.id WHERE st.name = :subterritory_name"; $statement = $this->connection->prepare($sqlQuery); $statement->bindValue(':subterritory_name', $subterritory_name); $statement->execute(); $results = $statement->fetchAll(); return $results; } }