Symfony Profiler

<?php
namespace App\Repository;
use Doctrine\DBAL\Connection;
use App\Entity\Stockpharmacie;
use Doctrine\ORM\Exception\ORMException;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
/**
* @extends ServiceEntityRepository<Stockpharmacie>
*
* @method Stockpharmacie|null find($id, $lockMode = null, $lockVersion = null)
* @method Stockpharmacie|null findOneBy(array $criteria, array $orderBy = null)
* @method Stockpharmacie[] findAll()
* @method Stockpharmacie[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class StockpharmacieRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Stockpharmacie::class);
}
/**
* @throws ORMException
* @throws OptimisticLockException
*/
public function add(Stockpharmacie $entity, bool $flush = false): void
{
$this->_em->persist($entity);
if ($flush) {
$this->_em->flush();
}
}
/**
* @throws ORMException
* @throws OptimisticLockException
*/
public function remove(Stockpharmacie $entity, bool $flush = false): void
{
$this->_em->remove($entity);
if ($flush) {
$this->_em->flush();
}
}
/**
* @return Stockpharmacie[] Returns an array of Stockpharmacie objects
*/
public function findAllProductInOrder($produit)
{
$qb = $this->createQueryBuilder('s')
->select('s')
->where('s.produit = :produit')
->orderBy('s.id', 'DESC')
->setParameter('produit', $produit);
return $qb->getQuery()->getResult();
}
/**
* @return Stockpharmacie[] Returns an array of Stockpharmacie objects
*/
public function listProduitStockGrossiste($pays, $roles = '["ROLE_GROSSISTE"]')
{
$qb = $this->createQueryBuilder('s')
->select('s')
->join('s.user', 'user')
->andWhere('user.roles = :roles')
->andWhere('user.pays = :pays')
->setParameters([
'roles' => $roles,
'pays' => $pays
]);
return $qb->getQuery()->getResult();
}
public function reinitDisponibility($pharmacieid)
{
$qb = $this->createQueryBuilder('stock')
->update('App\Entity\Stockpharmacie', 'stock')
->set('stock.isDisponible', ':nullValue')
->where('stock.ictuspharmacie = :pharmacieid')
->setParameter('nullValue', null)
->setParameter('pharmacieid', $pharmacieid);
$query = $qb->getQuery();
$affectedRows = $query->execute();
return $affectedRows;
}
public function removeOldStock($pharmacieid, $dateOld)
{
$qb = $this->createQueryBuilder('stock')
->delete('App\Entity\Stockpharmacie', 'stock')
->where('stock.ictuspharmacie = :pharmacieid')
->andWhere('stock.modified_at <= :dateold')
->andWhere('stock.isDisponible IS NULL')
->setParameter('pharmacieid', $pharmacieid)
->setParameter('dateold', $dateOld);
$query = $qb->getQuery();
$affectedRows = $query->execute();
return $affectedRows;
}
public function getBestSeller($limit = 10)
{
$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery('
SELECT
DISTINCT(stock.id) as stockid,
stock.isOrdonnance,
stock.nomproduit,
stock.isDisponible,
stock.isDisponible,
stock.prixunitforme,
stock.tva,
pharmacie.designation as proprietaire,
forme.designation as formeproduit,
produit.id as idproduit,
(SELECT COUNT(commande.id) FROM App\Entity\IctusCommandeLine commande WHERE commande.designation LIKE stock.nomproduit) as nbCommande,
(SELECT MIN(photo.lien) FROM App\Entity\Photo photo WHERE photo.produit = produit) as photoproduit
FROM App\Entity\Stockpharmacie stock
JOIN stock.ictuspharmacie pharmacie
LEFT JOIN stock.ictusforme forme
JOIN stock.produit produit
ORDER BY nbCommande DESC
')->setMaxResults($limit);
return $query->execute();
}
public function findAllProductKnp($ictusPcie, $keyword = null, $limit = 10, $offset = 0)
{
$entityManager = $this->getEntityManager();
$like = 'stock.id is not null';
$caseWhen = '';
$case = '';
$or = '';
$orderRequette = ' stock.nomproduit ASC';
$arrNomProduit = explode(' ', $keyword);
if ($keyword != null && $keyword != '') {
if (isset($arrNomProduit[0])) {
$like = "";
foreach ($arrNomProduit as $key => $nomP) {
$then = ($key == 0) ? 3 : 1;
if (str_contains($nomP, "'")) {
$nomP = str_replace("'", "''", $nomP);
}
$like .= $or . "stock.nomproduit LIKE '%" . $nomP . "%'";
$caseWhen .= "CASE WHEN stock.nomproduit LIKE '%" . $nomP . "%' THEN " . $then . " ELSE 0 END + ";
$or = ' OR ';
}
$orderRequette = ' keyword_count DESC ';
$case = ', (' . $caseWhen . ' 0 ) AS keyword_count ';
}
}
$query = $entityManager->createQuery("
SELECT
stock.id AS stockid, stock.nomproduit, stock.isOrdonnance, stock.isDisponible, stock.prixunitforme,
forme.designation, produit.id as idproduit,
(SELECT MIN(photo.lien) FROM App\Entity\Photo photo WHERE photo.produit = stock.produit) as photoproduit
" . $case . "
FROM
App\Entity\Stockpharmacie stock
LEFT JOIN
stock.ictusforme forme
JOIN
stock.produit produit
WHERE
stock.ictuspharmacie = :ictusPcie
AND (" . $like . ")
ORDER BY
" . $orderRequette . "
")
->setParameter('ictusPcie', $ictusPcie)
->setMaxResults($limit)
->setFirstResult($offset);
return $query->getResult();
}
//Utiliser pour API Mobile
public function findAllPcieByDistance($product, $patientLatitude, $patientLongitude, $ville = null, $pays = null)
{
$connection = $this->_em->getConnection("default");
$dateSemaine = ['Monday' => 1, 'Tuesday' => 2, 'Wednesday' => 3, 'Thursday' => 4, 'Friday' => 5, 'Saturday' => 6, 'Sunday' => 7];
$jourNow = $dateSemaine[date("l", strtotime(date('Y-m-d')))];
$timeNow = date('H:i:s');
$rayonDeLaTerre = 6371071.03;
$whereLocalisation = "";
$calculDistance = "";
$orderDistance = "";
if ($ville) {
$whereLocalisation = " AND quartier.ville_id = $ville";
} else {
if ($pays != null) {
$whereLocalisation = " AND ville.pays_id = $pays";
}
}
if ($patientLatitude != null && $patientLongitude != null) {
$calculDistance = "
COALESCE(
(2 * $rayonDeLaTerre * asin(
sqrt(
sin((RADIANS($patientLatitude) - RADIANS(pcie.latitude)) / 2) *
sin((RADIANS($patientLatitude) - RADIANS(pcie.latitude)) / 2) +
cos(RADIANS($patientLatitude)) *
cos(RADIANS(pcie.latitude)) *
sin((RADIANS(pcie.logitude) - RADIANS($patientLongitude)) / 2) *
sin((RADIANS(pcie.logitude) - RADIANS($patientLongitude)) / 2)
)
)
)
, null) as distance,
";
$orderDistance = " ORDER BY distance IS NULL, distance ASC ";
}
$sqlQuery = "SELECT stock.id, pcie.id as idpharmacie, stock.is_disponible, stock.is_ordonnance, pcie.designation, pcie.logo, pcie.adresse, ville.name as ville, quartier.name as quartier, stock.nomproduit, stock.prixunitforme, stock.tva, forme.designation as formedesignation,
$calculDistance
(SELECT ictus_horaire.fin FROM ictus_horaire WHERE ictus_horaire.ictus_pharmacie_id = pcie.id AND ictus_horaire.jour LIKE '$jourNow' AND ictus_horaire.debut <= '$timeNow' AND ictus_horaire.fin > '$timeNow') as isopen
FROM stockpharmacie AS stock
JOIN ictus_pharmacie AS pcie ON stock.ictuspharmacie_id = pcie.id
LEFT JOIN ictus_forme AS forme ON forme.id = stock.ictusforme_id
LEFT JOIN quartier ON quartier.id = pcie.quartier_id
LEFT JOIN ville ON ville.id = quartier.ville_id
WHERE pcie.is_valid = 1 AND stock.produit_id = $product
$whereLocalisation
$orderDistance
";
$results = $connection->prepare($sqlQuery)->executeQuery()->fetchAllAssociative();
return $results;
}
public function findAllPcieSpecialByDistance($patientLatitude, $patientLongitude, $ville = null, $pays = null)
{
$connection = $this->_em->getConnection("default");
$rayonDeLaTerre = 6371071.03;
$whereLocalisation = "";
$calculDistance = "";
$orderDistance = "";
if ($ville) {
$whereLocalisation = " AND quartier.ville_id = $ville";
} else {
if ($pays != null) {
$whereLocalisation = " AND ville.pays_id = $pays";
}
}
if ($patientLatitude != null && $patientLongitude != null) {
$calculDistance = "
, COALESCE(
(2 * $rayonDeLaTerre * asin(
sqrt(
sin((RADIANS($patientLatitude) - RADIANS(ictus_pharmacie.latitude)) / 2) *
sin((RADIANS($patientLatitude) - RADIANS(ictus_pharmacie.latitude)) / 2) +
cos(RADIANS($patientLatitude)) *
cos(RADIANS(ictus_pharmacie.latitude)) *
sin((RADIANS(ictus_pharmacie.logitude) - RADIANS($patientLongitude)) / 2) *
sin((RADIANS(ictus_pharmacie.logitude) - RADIANS($patientLongitude)) / 2)
)
)
)
, null) as distance
";
$orderDistance = " ORDER BY distance IS NULL, distance ASC ";
}
$sqlQuery = "SELECT ictus_pharmacie.id as idpharmacie, ictus_pharmacie.designation, ictus_pharmacie.logo, ictus_pharmacie.adresse, ville.name as ville, quartier.name as quartier
$calculDistance
FROM ictus_pharmacie
LEFT JOIN quartier ON quartier.id = ictus_pharmacie.quartier_id
LEFT JOIN ville ON ville.id = quartier.ville_id
WHERE ictus_pharmacie.is_valid = 1 AND ictus_pharmacie.is_commande_special = 1
$whereLocalisation
$orderDistance
";
$results = $connection->prepare($sqlQuery)->executeQuery()->fetchAllAssociative();
return $results;
}
//FIN
//pour l'utilisateur connectee
public function findProductInCart($id_stock_pcie, $id_user)
{
$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery("
SELECT sp.id AS id_stock,
sp.nomproduit,
sp.description AS pcie_description,
sp.isDisponible,
sp.prixunitforme,
sp.isOrdonnance,
pcie.id AS id_pcie,
pcie.designation AS nom_pcie,
pcie.logo,
pcie.adresse,
quartier.id AS quartier_id,
quartier.name AS nom_quartier,
ville.id AS ville_id,
ville.name AS nom_ville,
produit.id AS produit_id,
produit.description AS desc_produit,
photos.id AS id_photo,
photos.lien AS lien,
ordonnance.id AS ordonnance_id
FROM App\Entity\Stockpharmacie sp
LEFT JOIN sp.ictuspharmacie pcie
LEFT JOIN sp.produit produit
LEFT JOIN pcie.quartier quartier
LEFT JOIN quartier.ville ville
LEFT JOIN produit.photos photos
INNER JOIN sp.ictusPanierPatients panier WITH panier.user = :id_user
LEFT JOIN panier.ordonnance ordonnance
WHERE sp.id = :id_stock_pcie
ORDER BY pcie.designation DESC
")
->setParameter("id_stock_pcie", $id_stock_pcie)
->setParameter("id_user", $id_user)
->setMaxResults(1);
return $query->execute();
}
//pour l'utlisateur non connectee
public function findProductInCartUserNonAuth($id_stock_pcie)
{
$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery("
SELECT sp.id AS id_stock,
sp.nomproduit,
sp.description AS pcie_description,
sp.isDisponible,
sp.prixunitforme,
sp.isOrdonnance,
pcie.id AS id_pcie,
pcie.designation AS nom_pcie,
pcie.logo,
pcie.adresse,
quartier.id AS quartier_id,
quartier.name AS nom_quartier,
ville.id AS ville_id,
ville.name AS nom_ville,
produit.id AS produit_id,
produit.description AS desc_produit,
photos.id AS id_photo,
photos.lien AS lien
FROM App\Entity\Stockpharmacie sp
LEFT JOIN sp.ictuspharmacie pcie
LEFT JOIN sp.produit produit
LEFT JOIN pcie.quartier quartier
LEFT JOIN quartier.ville ville
LEFT JOIN produit.photos photos
WHERE sp.id = :id_stock_pcie
ORDER BY pcie.designation DESC
")
->setParameter("id_stock_pcie", $id_stock_pcie)
->setMaxResults(1);
return $query->execute();
}
// /**
// * @return Stockpharmacie[] Returns an array of Stockpharmacie objects
// */
// public function findByExampleField($value): array
// {
// return $this->createQueryBuilder('s')
// ->andWhere('s.exampleField = :val')
// ->setParameter('val', $value)
// ->orderBy('s.id', 'ASC')
// ->setMaxResults(10)
// ->getQuery()
// ->getResult()
// ;
// }
// public function findOneBySomeField($value): ?Stockpharmacie
// {
// return $this->createQueryBuilder('s')
// ->andWhere('s.exampleField = :val')
// ->setParameter('val', $value)
// ->getQuery()
// ->getOneOrNullResult()
// ;
// }
}

src/Repository/StockpharmacieRepository.php line 143