<?php
namespace App\Repository\stock;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Types\user\TypeEtat;
use Doctrine\ORM\RepositoryRepository;
use App\Entity\stock\Ville;
use App\ControllerModel\user\paramUtilTrait;
/**
* VilleRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class VilleRepository extends ServiceEntityRepository
{
use paramUtilTrait;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Ville::class);
}
/**
* Retourne tous les profils.
*
* @return type
*/
public function getAllVille($typeProdVille=1,$typeVille = 3)
{
$qb = $this->createQueryBuilder('r')
->where('r.etatVille != '.TypeEtat::SUPPRIME)
//->andWhere('r.typeProdVille = '.$typeProdVille)
//->andWhere('r.typeVille = '.$typeVille)
->orderBy('r.id', 'ASC');
return $qb->getQuery()->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllVillePays($paysId)
{
$query = $this->_em->createQuery(
'SELECT v
FROM App\Entity\stock\Ville v
INNER JOIN v.pays p
WHERE p.id =:paysId'
)
->setParameters(["paysId"=>$paysId ]);
return $query->getResult();;
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllIdActive($abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT av.id as idAv, v.id as id
FROM App\Entity\user\AbonneVille av
INNER JOIN av.ville v
INNER JOIN av.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
$transform = function($item){
return $item['id'];
};
return array_map($transform, $query->getScalarResult());
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllAbonneActive($abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT av
FROM App\Entity\user\AbonneVille av
INNER JOIN av.ville v
INNER JOIN av.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getZoneProducteur($abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id , v.nomVille
FROM App\Entity\stock\Ville v
INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.producteur p
INNER JOIN p.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
$transform = function($item){
return $item['id'];
};
return array_map($transform, $query->getScalarResult());
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getZoneInfoProducteur($producteurId=0)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id , v.nomVille
FROM App\Entity\stock\Ville v
INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.producteur p
INNER JOIN p.abonne a
WHERE p.id =:producteurId'
)
->setParameters(["producteurId"=>$producteurId]);
return $query->getResult();;
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getZoneAbonneProducteur($abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v
FROM App\Entity\stock\Ville v
INNER JOIN v.zoneabonnes p
INNER JOIN p.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getInfoZoneProducteur($abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id , v.nomVille
FROM App\Entity\stock\Ville v
INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.producteur p
INNER JOIN p.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
return $query->getResult();
}
/**
* Retourne tous les profils.
*
* @return type
*/
public function getAllVilleByProduit($typeProdVille=1)
{
$qb = $this->createQueryBuilder('r')
->where('r.etatVille != '.TypeEtat::SUPPRIME)
->andWhere('r.typeVille = '.$typeProdVille)
->orderBy('r.id', 'ASC');
return $qb->getQuery()->getResult();
}
/**
* Retourne tous les profils.
*
* @return type
*/
public function getAllActiveVille($typeProdVille=1,$typeVille = 2)
{
$qb = $this->createQueryBuilder('r')
->where('r.etatVille = '.TypeEtat::ACTIF)
->andWhere('r.typeVille = '.$typeVille)
->orderBy('r.id', 'ASC');
// ->andWhere('r.typeProdVille = '.$typeProdVille)
//var_dump($typeProdVille);exit;
return $qb->getQuery()->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getPrixProducteur($id, $idAnnee)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT p.id, pr.infoPrixRigueur
FROM App\Entity\stock\PrixZone p INNER JOIN p.prixrigueur pr INNER JOIN p.anneecampagne a INNER JOIN p.ville v
INNER JOIN v.producteurs pd
WHERE pd.id =:id and a.id =:idAnnee'
)
->setParameters(array('id'=>$id,'idAnnee'=>$idAnnee));
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getDistinctZoneProducteur($abonneId)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id, v.nomVille
FROM App\Entity\stock\Ville v INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.abonne a
WHERE a.id =:abonneId'
)
->setParameters(array('abonneId'=>$abonneId));
//var_dump($query->getSql());exit;
return $query->getResult();
}
public function getDistinctZone($abonneId, $typeVille)
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id, v.nomVille
FROM App\Entity\stock\Ville v
INNER JOIN v.abonnevilles az
INNER JOIN az.abonne a
WHERE a.id =:abonneId and az.typeAbonneVille=:typeVille'
)
->setParameters(array('abonneId'=>$abonneId, 'typeVille'=>$typeVille));
if(count($query->getResult()) !=0 )
return $query->getResult();
$query2 = $this->_em->createQuery(
'SELECT DISTINCT v.id, v.nomVille
FROM App\Entity\stock\Ville v INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.abonne a
WHERE a.id =:abonneId'
)
->setParameters(array('abonneId'=>$abonneId));
//var_dump($query->getSql());exit;
$tabDistinctZone = array_merge( $query->getResult(),$query2->getResult());
return $tabDistinctZone;
}
public function getVilleUtilisateur($niveau=4,$tabVille,$type=0) {
$param = array();
$datedebarray = array();
$datefinarray = array();
$nbTotal = 10000;
$infoVille = $this->getStringSqlVille($niveau);
if($tabVille==0){$p="v".$niveau;}else{$p="p";}
//var_dump($paramdeb,$paramfin);exit;
$sql = "SELECT DISTINCT(v".$niveau.".id ) as id, v".$niveau.".nomVille as nom
FROM App\Entity\stock\Ville ".$p;
$sql .= $infoVille[0];
$sql .= " WHERE 1=1 ";
/** debut critère recherche * */
$i = 0;
( $type == '0' || $type == '') ? $sql .= '' : $sql .= " AND ".$p.".typeVille = :type";
if($tabVille != 0){
foreach($tabVille as $uneVille){
if($i == 0){
$sql .= " AND ( v".$niveau.".id = :ville".$uneVille->getVille()->getId()."";
}else{
$sql .= " OR v".$niveau.".id = :ville".$uneVille->getVille()->getId()."";
}
$i++;
//$tabProduitUser[] = $unInfoProduit->getId();
}
// $sql .= ")";
}
// $sql .= " GROUP BY v".$niveau.".nomVille ";
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
if($tabVille != 0){
foreach($tabVille as $uneVille){
//$tabProduitUser[] = $unInfoProduit->getId();
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}
//var_dump($sql);exit;
$query = $this->_em->createQuery($sql);
$query->setParameters($param);
// var_dump($query->getSQL());exit;
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getVilleUtilisateurAPI($abonneId=0)
{
$query1 = $this->_em->createQuery(
'SELECT v.id as id, v.nomVille as nom, av.typeAbonneVille as typeVille
FROM App\Entity\user\AbonneVille av
INNER JOIN av.ville v
INNER JOIN av.abonne a
WHERE a.id =:abonneId'
)
->setParameters(["abonneId"=>$abonneId]);
$query2 = $this->_em->createQuery(
'SELECT DISTINCT v.id, v.nomVille as nom, 5 as typeVille
FROM App\Entity\stock\Ville v INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.abonne a
WHERE a.id =:abonneId'
)
->setParameters(array('abonneId'=>$abonneId));
// var_dump($query->getSql());exit;
//return $query->getResult();
return array_merge( $query1->getResult(),$query2->getResult());
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getVilleUtilisateurAPIByZone($abonneId=0,$tabVille=array(), $partenaireId=0, $typeAction=0)
{
$sql =" SELECT v.id as id, v.nomVille as nom, 0 as typeVille
FROM App\Entity\user\AbonneVille av
INNER JOIN av.ville v ";
if($typeAction != 0){
$sql .= " INNER JOIN av.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN av.partenaire pt ";
}else{
$sql .= " INNER JOIN av.abonne a";
}
$sql .=" WHERE 1 = 1 ";
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $sql .= ' AND pt.id = :partenaireId ';
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
$query1 = $this->_em->createQuery($sql) ;
$query1->setParameters($param);
$sql =" SELECT DISTINCT v.id, v.nomVille as nom, 5 as typeVille
FROM App\Entity\stock\Ville v INNER JOIN v.zoneproducteurs zp
INNER JOIN zp.abonne a
WHERE 1 = 1 ";
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId ';
if($tabVille !=0){
$i=0;
foreach($tabVille as $uneVille){
if($i == 0){
$sql .= " AND v.id = :ville".$uneVille->getVille()->getId()."";
//var_dump($uneVille->getId());
}else{
$sql .= " OR v.id = :ville".$uneVille->getVille()->getId()."";
}
$i++;
//$tabProduitUser[] = $unInfoProduit->getId();
}
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= ' AND v3.id = :ville';
}
if($tabVille !=0){
foreach($tabVille as $uneVille){
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
$query2 = $this->_em->createQuery($sql) ;
$query2->setParameters($param);
return array_merge( $query1->getResult(),$query2->getResult());
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getCooperativeApi()
{
$query = $this->_em->createQuery(
'SELECT DISTINCT v.id, v.nomVille
FROM App\Entity\stock\Ville v'
) ;
return $query->getResult();
}
}