<?php
namespace App\Repository\stock;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Types\user\TypeEtat;
use App\Entity\stock\Fournisseur;
use App\ControllerModel\user\paramUtilTrait;
/**
* FournisseurRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class FournisseurRepository extends ServiceEntityRepository
{
use paramUtilTrait;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Fournisseur::class);
}
/**
* Retourne tous les fournisseurs.
*
* @return type
*/
public function getAllFournisseur($type, $abonneId=0,$partenaireId=0, $typeAction=0)
{
// var_dump($abonneId);exit;
$param = array();
$sql = " SELECT f
FROM App\Entity\stock\Fournisseur f
";
if($typeAction != 0){
$sql .= " INNER JOIN f.fournisseurabonnes fas ";
$sql .= " INNER JOIN fas.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN fas.partenaire pt ";
}else{
$sql .= " INNER JOIN f.abonne a";
}
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND f.typeFournisseur =:type';
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $sql .= ' AND pt.id = :partenaireId ';
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
// var_dump($type,$query->getSQL());exit;
return $query->getResult();
}
/**
* Retourne tous les fournisseurs.
*
* @return type
*/
public function getCountAndSimpleAllFournisseur($type, $abonneId=0,$partenaireId=0, $typeAction=0, $modele=0)
{
// var_dump($abonneId);exit;
$param = array();
if($modele == 0)
$sql = " SELECT COUNT(f) as total";
else
$sql = " SELECT f.nomFournisseur, f.contactFournisseur, f.codeFournisseur, f.id, f.adresseFournisseur ";
$sql.= " FROM App\Entity\stock\Fournisseur f
";
if($typeAction != 0){
$sql .= " INNER JOIN f.fournisseurabonnes fas ";
$sql .= " INNER JOIN fas.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN fas.partenaire pt ";
}else{
$sql .= " INNER JOIN f.abonne a";
}
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND f.typeFournisseur =:type';
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $sql .= ' AND pt.id = :partenaireId ';
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
// var_dump($type,$query->getSQL());exit;
return $query->getResult();
}
/**
* Retourne tous les fournisseurs.
*
* @return type
*/
public function getAllFournisseurPointe($type, $abonneId=0)
{
// var_dump($abonneId);exit;
$param = array();
$sql = " SELECT f
FROM App\Entity\stock\Fournisseur f
INNER JOIN f.abonne a
";
$sql .= " WHERE 1=1 and f.etatFournisseur =:etatFournisseur ";
$param['etatFournisseur'] = 4;
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND f.typeFournisseur =:type';
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId';
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
// var_dump($type,$query->getSQL());exit;
return $query->getResult();
}
/**
* Retourne tous les fournisseurs.
*
* @return type
*/
public function getAllFournisseurAjax($type, $abonneId=0)
{
// var_dump($abonneId);exit;
$param = array();
$sql = " SELECT f.id, f.nomFournisseur
FROM App\Entity\stock\Fournisseur f
INNER JOIN f.abonne a
";
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND f.typeFournisseur =:type';
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId';
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
// var_dump($type,$query->getSQL());exit;
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getOneFournisseur($id)
{
$qb = $this->createQueryBuilder('r')
->select('r,i')
->leftJoin('r.images', 'i')
->where('r.id = '.$id)
->orderBy('r.id', 'ASC');
return $qb->getQuery()->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurByZone($id,$typefour=1, $abonneId=0)
{
$query = $this->_em->createQuery(
'SELECT f
FROM App\Entity\stock\Fournisseur f
INNER JOIN f.abonne a
WHERE f.typeFournisseur =:typefour AND f.etatFournisseur !=:etatFournisseur AND a.id =:abonneId ORDER BY f.nomFournisseur ASC'
)->setParameters(array('etatFournisseur'=>TypeEtat::SUPPRIME, 'typefour'=>$typefour, 'abonneId'=>$abonneId));
// var_dump($id,$typefour, $abonneId) ;exit;
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getSumSurplusByZone($id)
{
$query = $this->_em->createQuery(
'SELECT SUM(su.quantite) as quantite
FROM App\Entity\stock\Surplus su INNER JOIN su.fournisseur f INNER JOIN f.setrouvers s INNER JOIN s.utilisateur u INNER JOIN s.ville v
WHERE v.id = :id AND f.etatFournisseur !=:etatFournisseur '
)->setParameters(array('id'=>$id,'etatFournisseur'=>TypeEtat::SUPPRIME));
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurByDate()
{
$query = $this->_em->createQuery(
'SELECT DISTINCT f.id, f.nomFournisseur, f.contactFournisseur ,c.dateCommande
FROM App\Entity\stock\Fournisseur f INNER JOIN f.commandes c INNER JOIN c.lignecommandes l
WHERE DATE_DIFF(CURRENT_DATE(),c.dateCommande)>=120 and c.etatCommande !=2 AND f.etatFournisseur !=:etatFournisseur '
)
->setParameters(array('etatFournisseur'=>TypeEtat::SUPPRIME));
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurProducteur($id,$ville=0, $niveau=4)
{
$param =array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = "SELECT DISTINCT p.id, f.nomFournisseur, u.nom ,u.prenoms, u.id as idutil,u.tel1 as telephone, v3.nomVille as village, p.siModification, p.siIntention, p.siSemence
FROM App\Entity\stock\Fournisseur f INNER JOIN f.fournisseurproducteurs fp INNER JOIN fp.producteur p
INNER JOIN p.utilisateur u ";
$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND f.id =:id ';
$sql .= " ORDER BY u.nom ASC ";
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
$query = $this->_em->createQuery($sql) ;
//var_dump($query->getSql());exit;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurProducteurAPI($id,$tabVille=0, $niveau=4,$prod='0',$type = 0)
{
$param =array();
$infoVille = $this->getStringSqlVille($niveau);
$prod=0;
$sql = "SELECT f.id,p.id, f.nomFournisseur, u.nom ,u.prenoms, u.id as idutil,u.tel1 as telephone, v3.nomVille as village, v3.id as idVille,
p.siModification, p.siIntention, p.siSemence, an.id as idAnnee, i.nbreTotalLivre as quantiteReelle, i.nbreTotalLivre*pr.infoPrixRigueur as montant,
SUM(i.nbreTotalLivre ) as totalLivraison, l.quantite as quantiteTotal, l.quantite*pr.infoPrixRigueur as montantAvance, sa.superficie as valSurface, p.typeProd as urlImage
FROM App\Entity\stock\Fournisseur f
INNER JOIN f.lignecommandes l
INNER JOIN l.producteur p
INNER JOIN l.prixrigueur pr
INNER JOIN p.surfaceannees sa
INNER JOIN sa.anneecampagne an
INNER JOIN p.utilisateur u ";
$sql .= $infoVille[0];
$sql.="LEFT JOIN l.livrers lv
LEFT JOIN lv.infolivrer i";
//INNER JOIN f.fournisseurproducteurs fp INNER JOIN fp.producteur p
//$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and f.etatProd !=:etatProd ";
$param['etatProd'] = TypeEtat::SUPPRIME;
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND f.id =:id ';
( $prod == '0' || $prod == '') ? $prod .= '' : $sql .= ' AND p.typeProd =:prod ';
( $type == '0' || $type == '') ? $type .= '' : $sql .= ' AND p.siModification =:type ';
if($tabVille !=0){
if($prod != 1){
$i=0;
foreach($tabVille as $uneVille){
if($i == 0){
$sql .= " AND v3.id = :ville".$uneVille->getVille()->getId()."";
//var_dump($uneVille->getId());
}else{
$sql .= " OR v3.id = :ville".$uneVille->getVille()->getId()."";
}
$i++;
//$tabProduitUser[] = $unInfoProduit->getId();
}
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= ' AND v3.id = :ville';
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $sql .= ' AND v.id = :ville';
}
}
$sql .= " GROUP BY f.id ORDER BY u.nom ASC ";
//var_dump($sql,$prod);exit;
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
( $prod == '0' || $prod == '') ? $prod .= '' : $param['prod'] = $prod;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
if($tabVille !=0){
if($prod != 1){
foreach($tabVille as $uneVille){
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $param['ville'] = $tabVille;
}
}
( $type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
$query = $this->_em->createQuery($sql) ;
//var_dump($query->getSql());exit;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurByType($typeFour = 1,$abonneId =0 )
{
$param = array();
$sql = "SELECT DISTINCT f.id, f.nomFournisseur, 0 as idutil,0 as telFournisseur, f.codeGenere as codeGenere, f.typeFournisseur as typeAgent
FROM App\Entity\stock\Fournisseur f
INNER JOIN f.abonne a
";
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $typeFour == '0' || $typeFour == '') ? $sql .= '' : $sql .= ' AND f.typeFournisseur =:typef ';
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId ';
$sql .= " ORDER BY f.nomFournisseur ASC ";
( $typeFour == '0' || $typeFour == '') ? $sql .= '' : $param['typef'] = $typeFour;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getIntentionProducteurFournisseurAnneeCompage($idProd,$anneeCampagne,$niveau=4)
{
$param = array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = "SELECT DISTINCT u.id,u.nom as nomPrenom ,
u.username, u.email, sa.id as idsurface, s.superficie as valSurface, sa.datePublication as dateSaisie, a.infoAnneeCampagne as anneeCampagne, sa.etatSurfaceAnnee,sa.codeGenere
FROM App\Entity\prod\Surfaceannee sa
INNER JOIN sa.producteur p
INNER JOIN p.utilisateur u
INNER JOIN sa.anneecampagne a";
// $sql .= $infoVille[0];
$sql .= " WHERE 1=1 and f.etatFournisseur !=:etatFournisseur ";
$param['etatFournisseur'] = TypeEtat::SUPPRIME;
( $idProd == '0' || $idProd == '') ? $sql .= '' : $sql .= ' AND p.id=:idProd ';
( $anneeCampagne == '0' || $anneeCampagne == '') ? $sql .= '' : $sql .= ' AND a.id=:idAnnee ';
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= $infoVille[1];
$sql .= " ORDER BY u.nom ASC ";
( $idProd == '0' || $idProd == '') ? $sql .= '' : $param['idProd'] = $idProd;
( $anneeCampagne == '0' || $anneeCampagne == '') ? $sql .= '' : $param['idAnnee'] = $anneeCampagne;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getIntentionProducteurByZoneAnneeCompage($ville, $anneeCampagne,$niveau = 5,$tabVille)
{
$param = array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = "SELECT DISTINCT u.id,u.nom, u.prenoms,
u.username, u.email, sa.id as idsurface, sa.superficie as valSurface, sa.datePublication as dateSaisie, a.infoAnneeCampagne as anneeCampagne, sa.etatSurfaceAnnee,sa.codeGenere
FROM App\Entity\prod\Surfaceannee sa
INNER JOIN sa.producteur p
INNER JOIN p.utilisateur u
INNER JOIN sa.anneecampagne a";
$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and p.etatProd !=:etatProd ";
$param['etatProd'] = TypeEtat::SUPPRIME;
//( $idProd == '0' || $idProd == '') ? $sql .= '' : $sql .= ' AND p.id=:idProd ';
( $anneeCampagne == '0' || $anneeCampagne == '') ? $sql .= '' : $sql .= ' AND a.id=:idAnnee ';
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= $infoVille[1];
$i = 0;
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 .= " ORDER BY p.id ASC ";
//( $idProd == '0' || $idProd == '') ? $sql .= '' : $param['idProd'] = $idProd;
( $anneeCampagne == '0' || $anneeCampagne == '') ? $sql .= '' : $param['idAnnee'] = $anneeCampagne;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
//var_dump($sql);exit;
if($tabVille !=0){
foreach($tabVille as $uneVille){
//$tabProduitUser[] = $unInfoProduit->getId();
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
//
//var_dump($sql);exit;
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getSumFournisseurByZone($typeFour = 1,$ville = 1, $niveau=3)
{
$param = array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = "SELECT count(p.id) as compte
FROM App\Entity\stock\Fournisseur p
";
$sql .= $infoVille[0];
$sql .= " WHERE v1.typeVille = 2 AND 1=1 ";
( $typeFour == '0' || $typeFour == '') ? $sql .= '' : $sql .= ' AND p.typeFournisseur =:type ';
( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= $infoVille[1];
//$sql .= " ORDER BY u.nom ASC ";
//var_dump($sql);exit;
( $typeFour == '0' || $typeFour == '') ? $sql .= '' : $param['type'] = $typeFour;
( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurProducteurAPINew($id,$tabVille=0, $niveau=4,$prod=2,$type = 0,$abonneId=0, $caisseId=0, $prodId=0, $groupement=0,
$partenaireId=0, $typeAction=0 )
{
$param =array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = "SELECT p.id, 0 as nomFournisseur, u.nom ,u.prenoms, u.id as idutil,u.tel1 as telephone,
p.siModification, p.siIntention, p.siSemence, 0 as idAnnee, 0 as quantiteReelle, 0 as montant,
0 as totalLivraison, 0 as quantiteTotal, 0 as montantAvance, 0 as valSurface, p.typeProd as urlImage, p.codeGenere as codeGenere, p.etatProd as etat, v.id as idVille, v.nomVille, g.id as groupement, g.nomGroupement, u.sexe,
p.datePublication as dateSaisie
";
$sql .= "
FROM App\Entity\prod\Producteur p
INNER JOIN p.producteurgroupements pg
INNER JOIN pg.groupement g ";
/*if($id != 0){
$sql .= " INNER JOIN p.fournisseurproducteurs fp
INNER JOIN p.fournisseur f
";
}*/
$sql .= " INNER JOIN p.utilisateur u ";
/*if ( $caisseId == '0' || $caisseId == '') {
$sql .= "";
}else{*/
$sql .= " INNER JOIN p.zoneproducteurs zp
INNER JOIN zp.ville v
";
/**
INNER JOIN v.setrouvers st
INNER JOIN st.fournisseur f
*/
//}
if($typeAction != 0){
$sql .= " INNER JOIN p.producteurabonnes pas ";
$sql .= " INNER JOIN pas.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN pas.partenaire pt ";
}else{
// $sql .= " INNER JOIN p.abonne a";
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' INNER JOIN zp.abonne a ';
}
$sql .= $infoVille[0];
/*$sql.="LEFT JOIN l.livrers lv
LEFT JOIN lv.infolivrer i";*/
//INNER JOIN l.prixrigueur pr
//INNER JOIN f.fournisseurproducteurs fp INNER JOIN fp.producteur p
//$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and p.etatProd =:etatProd ";
//var_dump($sql);exit;
$param['etatProd'] = TypeEtat::ACTIF;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $sql .= ' AND g.id =:groupement ';
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND u.id =:id ';
( $prod == '0' || $prod == '') ? $sql .= '' : $sql .= ' AND p.typeProd =:prod ';
( $prodId == '0' || $prodId == '') ? $sql .= '' : $sql .= ' AND p.id =:prodId ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
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($groupement ==0){
if(count($tabVille) !=0){
if($prod != 1){
$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();
}
$sql .= " ) ";
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= ' AND v3.id = :ville';
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $sql .= ' AND v.id = :ville';
}
}
}
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId ';
$sql .= " ORDER BY u.nom ASC ";
//var_dump($sql, $abonneId);exit;
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $param['groupement'] = $groupement;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
( $prod == '0' || $prod == '') ? $prod .= '' : $param['prod'] = $prod;
( $prodId == '0' || $prodId == '') ? $prodId .= '' : $param['prodId'] = $prodId;
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
if($groupement ==0){
if($tabVille !=0){
if($prod != 1){
foreach($tabVille as $uneVille){
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $param['ville'] = $tabVille;
}
}
}
($type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurProducteurLocalisationAPINew($id,$tabVille=0, $niveau=4,$prod=2,$type = 0,$abonneId=0, $caisseId=0, $prodId=0, $groupement=0,
$partenaireId=0, $typeAction=0 )
{
$param =array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = " SELECT lo.id, sa.codeGenere as parCodeGenere, lo.longitude, lo.latitude, lo.typeLocalisation, lo.codeGenere, 1 as utilisateurId, lo.tabPolygone as tabLocalisation
";
$sql .= "
FROM App\Entity\prod\Producteur p
INNER JOIN p.producteurgroupements pg
INNER JOIN pg.groupement g ";
/*if($id != 0){
$sql .= " INNER JOIN p.fournisseurproducteurs fp
INNER JOIN p.fournisseur f
";
}*/
$sql .= " INNER JOIN p.utilisateur u ";
/*if ( $caisseId == '0' || $caisseId == '') {
$sql .= "";
}else{*/
$sql .= " INNER JOIN p.zoneproducteurs zp
INNER JOIN zp.ville v
";
/**
INNER JOIN v.setrouvers st
INNER JOIN st.fournisseur f
*/
//}
if($typeAction != 0){
$sql .= " INNER JOIN p.producteurabonnes pas ";
$sql .= " INNER JOIN pas.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN pas.partenaire pt ";
}else{
// $sql .= " INNER JOIN p.abonne a";
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' INNER JOIN zp.abonne a ';
}
$sql .= "
INNER JOIN p.surfaceannees sa
INNER JOIN sa.localisations lo
" ;
$sql .= $infoVille[0];
/*$sql.="LEFT JOIN l.livrers lv
LEFT JOIN lv.infolivrer i";*/
//INNER JOIN l.prixrigueur pr
//INNER JOIN f.fournisseurproducteurs fp INNER JOIN fp.producteur p
//$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and p.etatProd !=:etatProd and lo.typeLocalisation = 1 ";
//var_dump($sql);exit;
$param['etatProd'] = TypeEtat::SUPPRIME;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $sql .= ' AND g.id =:groupement ';
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND u.id =:id ';
( $prod == '0' || $prod == '') ? $sql .= '' : $sql .= ' AND p.typeProd =:prod ';
( $prodId == '0' || $prodId == '') ? $sql .= '' : $sql .= ' AND p.id =:prodId ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
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($groupement ==0){
if(count($tabVille) !=0){
if($prod != 1){
$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();
}
$sql .= " ) ";
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= ' AND v3.id = :ville';
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $sql .= ' AND v.id = :ville';
}
}
}
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId ';
$sql .= " ORDER BY u.nom ASC ";
//var_dump($sql, $abonneId);exit;
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $param['groupement'] = $groupement;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
( $prod == '0' || $prod == '') ? $prod .= '' : $param['prod'] = $prod;
( $prodId == '0' || $prodId == '') ? $prodId .= '' : $param['prodId'] = $prodId;
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
if($groupement ==0){
if($tabVille !=0){
if($prod != 1){
foreach($tabVille as $uneVille){
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $param['ville'] = $tabVille;
}
}
}
($type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getAllFournisseurProducteurImageAPINew($id,$tabVille=0, $niveau=4,$prod=2,$type = 0,$abonneId=0, $caisseId=0, $prodId=0, $groupement=0,
$partenaireId=0, $typeAction=0 )
{
$param =array();
$infoVille = $this->getStringSqlVille($niveau);
$sql = " SELECT p.codeGenere as prodCodeGenere, lo.urlImage, lo.titreImage, lo.codeGenere, 1 as utilisateurId
";
$sql .= "
FROM App\Entity\prod\Producteur p
INNER JOIN p.producteurgroupements pg
INNER JOIN pg.groupement g ";
/*if($id != 0){
$sql .= " INNER JOIN p.fournisseurproducteurs fp
INNER JOIN p.fournisseur f
";
}*/
$sql .= " INNER JOIN p.utilisateur u ";
/*if ( $caisseId == '0' || $caisseId == '') {
$sql .= "";
}else{*/
$sql .= " INNER JOIN p.zoneproducteurs zp
INNER JOIN zp.ville v
";
/**
INNER JOIN v.setrouvers st
INNER JOIN st.fournisseur f
*/
//}
if($typeAction != 0){
$sql .= " INNER JOIN p.producteurabonnes pas ";
$sql .= " INNER JOIN pas.abonne a ";
if($typeAction == 2 && $partenaireId != 0)
$sql .= " INNER JOIN pas.partenaire pt ";
}else{
// $sql .= " INNER JOIN p.abonne a";
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' INNER JOIN zp.abonne a ';
}
$sql .= "
INNER JOIN p.images lo
" ;
$sql .= $infoVille[0];
/*$sql.="LEFT JOIN l.livrers lv
LEFT JOIN lv.infolivrer i";*/
//INNER JOIN l.prixrigueur pr
//INNER JOIN f.fournisseurproducteurs fp INNER JOIN fp.producteur p
//$sql .= $infoVille[0];
$sql .= " WHERE 1=1 and p.etatProd !=:etatProd ";
//var_dump($sql);exit;
$param['etatProd'] = TypeEtat::SUPPRIME;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $sql .= ' AND g.id =:groupement ';
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND u.id =:id ';
( $prod == '0' || $prod == '') ? $sql .= '' : $sql .= ' AND p.typeProd =:prod ';
( $prodId == '0' || $prodId == '') ? $sql .= '' : $sql .= ' AND p.id =:prodId ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $type == '0' || $type == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
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($groupement ==0){
if(count($tabVille) !=0){
if($prod != 1){
$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();
}
$sql .= " ) ";
//( $ville == '0' || $ville == '') ? $sql .= '' : $sql .= ' AND v3.id = :ville';
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $sql .= ' AND v.id = :ville';
}
}
}
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $sql .= ' AND a.id =:abonneId ';
$sql .= " ORDER BY u.nom ASC ";
//var_dump($sql, $abonneId);exit;
if($groupement == '0')
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
( $groupement == '0' || $groupement == '') ? $sql .= '' : $param['groupement'] = $groupement;
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
( $prod == '0' || $prod == '') ? $prod .= '' : $param['prod'] = $prod;
( $prodId == '0' || $prodId == '') ? $prodId .= '' : $param['prodId'] = $prodId;
if($typeAction == 0 || $typeAction == 1)
( $abonneId == '0' || $abonneId == '') ? $sql .= '' : $param['abonneId'] = $abonneId;
else
( $partenaireId == '0' || $partenaireId == '') ? $sql .= '' : $param['partenaireId'] = $partenaireId;
//( $ville == '0' || $ville == '') ? $sql .= '' : $param['ville'] = $ville;
if($groupement ==0){
if($tabVille !=0){
if($prod != 1){
foreach($tabVille as $uneVille){
$param["ville".$uneVille->getVille()->getId().""] = $uneVille->getVille()->getId();
}
}else{
( $tabVille == '0' || $tabVille == '') ? $sql .= '' : $param['ville'] = $tabVille;
}
}
}
($type == '0' || $type == '') ? $sql .= '' : $param['type'] = $type;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getOneFournisseurAPI($id)
{
$param =array();
$sql = "SELECT f.id, f.nomFournisseur, f.depassementFournisseur, f.creditFournisseur
FROM App\Entity\stock\Fournisseur f ";
$sql .= " WHERE 1=1 and f.id =:id ";
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND f.id =:id ';
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
/**
* Retourne un fournisseur.
*
* @return type
*/
public function getFournisseurSeTrouverAPI($id)
{
$param =array();
$sql = "SELECT v.id, v.nomVille
FROM App\Entity\stock\Fournisseur f ";
$sql .= " INNER JOIN f.setrouvers st ";
$sql .= " INNER JOIN st.ville v ";
$sql .= " WHERE 1=1 and f.id =:id ";
( $id == '0' || $id == '') ? $sql .= '' : $sql .= ' AND f.id =:id ';
( $id == '0' || $id == '') ? $sql .= '' : $param['id'] = $id;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
public function getAllPatient($id,$siDecede=0, $etatPatient=0,$dateDebutVisite=0,$dateFinVisite=0,
$siAbandonne = 0,$siPerduVue=0, $typeService=0, $manquantReunion=0,
$districtId=0, $dateDebutAjout=0,$dateFinAjout=0 )
{
$param =array();
$sql = " SELECT * ";
$sql .= " FROM App\Entity\Patient p
INNER JOIN p.typepatient tp ";
$sql .= " INNER JOIN p.gender g ";
$sql .= " INNER JOIN p.regime r ";
$sql .= " INNER JOIN p.visits v ";
$sql .= " INNER JOIN p.hivType ht ";
$sql .= " INNER JOIN p.patienthealthcenter phc ";
$sql .= " INNER JOIN phc.healthcenter hc ";
$sql .= " INNER JOIN hc.healthcenterstatus hcs ";
$sql .= " INNER JOIN hc.healthcentertype hct ";
$sql .= " WHERE 1=1 ";
( $siDecede == '0' || $siDecede == '') ? $sql .= '' : $sql .= ' AND u.id =:id ';
( $etatPatient == '0' || $etatPatient == '') ? $sql .= '' : $sql .= ' AND p.typeProd =:prod ';
( $siAbandonne == '0' || $siPerduVue == '') ? $sql .= '' : $sql .= ' AND p.id =:prodId ';
( $typeService == '0' || $typeService == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $manquantReunion == '0' || $manquantReunion == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $districtId == '0' || $districtId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
( $districtId == '0' || $districtId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
( $siDecede == '0' || $siDecede == '') ? $sql .= '' : $param['siDecide'] = $siDecide;
( $etatPatient == '0' || $etatPatient == '') ? $sql .= '' : $param['etatPatient'] = $etatPatient;
( $siAbandonne == '0' || $siAbandonne == '') ? $sql .= '' : $param['siAbandonne'] = $siAbandonne;
( $typeService == '0' || $typeService == '') ? $prod .= '' : $param['typeService'] = $typeService;
( $manquantReunion == '0' || $manquantReunion == '') ? $manquantReunion .= '' : $param['manquantReunion'] = $manquantReunion;
($districtId == '0' || $districtId == '') ? $districtId .= '' : $param['districtId'] = $districtId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
public function getAllStockDisponible($id,$siDecide=0, $etatPatient=0,$dateDebutVisite=0,$dateFinVisite=0,
$siAbandonne = 0,$siPerduVue=0, $typeService=0, $manquantReunion=0,
$districtId=0, $dateDebutAjout=0,$dateFinAjout=0 )
{
$param =array();
$sql = " SELECT * ";
$sql .= " FROM App\Entity\Patient p
INNER JOIN p.typepatient tp ";
$sql .= " INNER JOIN p.gender g ";
$sql .= " INNER JOIN p.regime r ";
$sql .= " INNER JOIN p.visits v ";
$sql .= " INNER JOIN p.hivType ht ";
$sql .= " INNER JOIN p.patienthealthcenter phc ";
$sql .= " INNER JOIN phc.healthcenter hc ";
$sql .= " INNER JOIN hc.healthcenterstatus hcs ";
$sql .= " INNER JOIN hc.healthcentertype hct ";
$sql .= " WHERE 1=1 ";
( $siDecide == '0' || $siDecide == '') ? $sql .= '' : $sql .= ' AND u.id =:id ';
( $etatPatient == '0' || $etatPatient == '') ? $sql .= '' : $sql .= ' AND p.typeProd =:prod ';
( $siAbandonne == '0' || $siPerduVue == '') ? $sql .= '' : $sql .= ' AND p.id =:prodId ';
( $typeService == '0' || $typeService == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $manquantReunion == '0' || $manquantReunion == '') ? $sql .= '' : $sql .= ' AND p.siModification =:type ';
( $districtId == '0' || $districtId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
( $districtId == '0' || $districtId == '') ? $sql .= '' : $sql .= ' AND a.id = :abonneId ';
( $siDecide == '0' || $siDecide == '') ? $sql .= '' : $param['siDecide'] = $siDecide;
( $etatPatient == '0' || $etatPatient == '') ? $sql .= '' : $param['etatPatient'] = $etatPatient;
( $siAbandonne == '0' || $siAbandonne == '') ? $sql .= '' : $param['siAbandonne'] = $siAbandonne;
( $typeService == '0' || $typeService == '') ? $prod .= '' : $param['typeService'] = $typeService;
( $manquantReunion == '0' || $manquantReunion == '') ? $manquantReunion .= '' : $param['manquantReunion'] = $manquantReunion;
($districtId == '0' || $districtId == '') ? $districtId .= '' : $param['districtId'] = $districtId;
$query = $this->_em->createQuery($sql) ;
$query->setParameters($param);
return $query->getResult();
}
}