src/Repository/ProductRepository.php line 35

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Category;
  6. use App\Entity\CategoryMaster;
  7. use App\Entity\Company;
  8. use App\Entity\Product;
  9. use App\Entity\SubCategory;
  10. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  11. use Doctrine\Persistence\ManagerRegistry;
  12. use Doctrine\Common\Collections\ArrayCollection;
  14. /**
  15.  * @method Product|null find($id, $lockMode = null, $lockVersion = null)
  16.  * @method Product|null findOneBy(array $criteria, array $orderBy = null)
  17.  * @method Product[]    findAll()
  18.  * @method Product[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  19.  */
  20. class ProductRepository extends ServiceEntityRepository
  21. {
  22.     /**
  23.      * @var SubCategoryRepository
  24.      */
  25.     private $subCategoryRepository;
  27.     public function __construct(ManagerRegistry $registry, SubCategoryRepository $subCategoryRepository)
  28.     {
  29.         parent::__construct($registry, Product::class);
  30.         $this->subCategoryRepository = $subCategoryRepository;
  31.     }
  33.     public function findAllProducts()
  34.     {
  35.         return $this->createQueryBuilder('p')
  36.             ->leftJoin('p.departments', 'departments')
  37.             ->leftJoin('p.company', 'company')
  38.             ->join('p.subCategories', 'subCategories')
  39.             ->addSelect('departments')
  40.             ->where('company.isConfirmed = 1')
  41.             ->andWhere('company.isValidDocument = 1')
  42.             ->andWhere('p.isActivated = 1')
  43.             ->andWhere('subCategories.isService is NULL or subCategories.isService = 0')
  44.             ->orderBy('p.id', 'DESC')
  45.             ->getQuery()
  46.             ->getResult();
  47.     }
  49.     public function findAllServices()
  50.     {
  51.         return $this->createQueryBuilder('p')
  52.             ->leftJoin('p.departments', 'departments')
  53.             ->leftJoin('p.company', 'company')
  54.             ->join('p.subCategories', 'subCategories')
  55.             ->addSelect('departments')
  56.             ->where('company.isConfirmed = 1')
  57.             ->andWhere('company.isValidDocument = 1')
  58.             ->andWhere('p.isActivated = 1')
  59.             ->andWhere('p.isDesactivatedByAdmin = 0')
  60.             ->andWhere('subCategories.isService = 1')
  61.             // ->leftJoin('p.avis', 'avis')
  62.             // ->addselect('avis')
  63.             // ->orderBy('avis.globalNote', 'DESC')
  64.             ->orderBy('p.id', 'DESC')
  65.             ->getQuery()
  66.             ->getResult();
  67.     }
  69.     public function searchProduct(array $parameters)
  70.     {
  71.         $qb = $this
  72.             ->createQueryBuilder('p')
  73.             ->leftJoin('p.subCategories', 'subCategory')
  74.             ->leftJoin('p.productAgendas', 'productAgendas')
  75.             ->where('p.isActivated = 1')
  76.             ->leftJoin('p.company', 'company')
  77.             ->andWhere('company.isConfirmed = 1')
  78.             ->andWhere('company.isValidDocument = 1')
  79.             ->andWhere('p.isDesactivatedByAdmin = 0');
  81.         if ($parameters['department']) {
  82.             $qb
  83.                 ->leftJoin('p.departments', 'departments')
  84.                 ->addSelect('departments')
  85.                 ->andWhere('departments = :department OR departments IS NULL')
  86.                 ->setParameter('department', $parameters['department']);
  87.         }
  89.         if ($parameters['city']) {
  90.             $qb
  91.                 ->join('p.commune', 'commune')
  92.                 ->andWhere('commune.nom = :commune OR commune IS NULL')
  93.                 ->setParameter('commune', $parameters['city']);
  94.         }
  96.         if ($parameters['min_price']) {
  97.             $qb
  98.                 ->andWhere('p.price > :price')
  99.                 ->setParameter('price', $parameters['min_price']);
  100.         }
  102.         if ($parameters['category']) {
  103.             $qb
  104.                 ->andWhere($qb->expr()->in('subCategory.id', $parameters['category']));
  105.         } /*elseif ($parameters['categoryMaster']) {
  107.             $subCategories = $this->subCategoryRepository->getSubCategoriesByCategory($parameters['categoryMaster']);
  108.             $arrayId = [];
  109.             foreach ($subCategories as $subCategory) {
  110.                 $arrayId[] = $subCategory['id'];
  111.             }
  113.             $qb
  114.                 ->andWhere('subCategory.id IN (:idMaster)')
  115.                 ->setParameter('idMaster', $arrayId);
  116.         }*/
  118.         if ($parameters['type']) {
  119.             if ($parameters['type'] === 'service') {
  120.                 $qb
  121.                     ->andWhere('subCategory.isService = 1');
  122.             } else {
  123.                 $qb
  124.                     ->andWhere('subCategory.isService = 0');
  125.             }
  126.         }
  128.         if ($parameters['nbGuest']) {
  129.             $qb
  130.                 ->andWhere('p.nbGuest > :nbGuest')
  131.                 ->orWhere('p.nbGuest IS NULL')
  132.                 ->setParameter('nbGuest', $parameters['nbGuest']);
  133.         }
  135.         if ($parameters['max_price']) {
  136.             $qb
  137.                 ->andWhere('p.price < :maxprice')
  138.                 ->setParameter('maxprice', $parameters['max_price']);
  139.         }
  141.         if ($parameters['keyword']) {
  142.             $qb
  143.                 ->andWhere('p.name LIKE :name')
  144.                 ->setParameter('name', '%'.$parameters['keyword'].'%');
  145.         }
  147.         if ($parameters['weddingdate_start'] && $parameters['weddingdate_end']) {
  148.             $weddingDateStart = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate_start'])->format('Y-m-d');
  149.             $weddingDateEnd = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate_end'])->format('Y-m-d');
  150.             
  151.             $qb
  152.                 ->andWhere('(productAgendas.startDayNotAvailable IS NULL OR :weddingdate_end < productAgendas.startDayNotAvailable OR :weddingdate_start > productAgendas.endDayNotAvailable)')
  153.                 ->setParameter('weddingdate_start', $weddingDateStart)
  154.                 ->setParameter('weddingdate_end', $weddingDateEnd);
  155.         }
  157.         if ($parameters['weddingdate']) {
  158.             $weddingdate = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate'])->format('Y-m-d');
  159.         
  160.             $qb
  161.                 ->andWhere('(productAgendas.startDayNotAvailable IS NULL OR :weddingdate < productAgendas.startDayNotAvailable OR :weddingdate > productAgendas.endDayNotAvailable)')
  162.                 ->setParameter('weddingdate', $weddingdate);
  163.         }
  164.         
  166. /*
  167.         if ($parameters['category']) {
  168.             if ($parameters['category'] !== 'Service') {
  169.                 $qb
  170.                     ->andWhere('subCategory IN (:category)')
  171.                     ->setParameter('category', $parameters['category']);
  172.             }
  173.         }*/
  175.         return $qb->orderBy('p.id', 'DESC')->getQuery()->getResult();
  176.     }
  178.     public function searchProductCompany(array $parameters, Company $company)
  179.     {
  180.         $qb = $this
  181.             ->createQueryBuilder('p')
  182.             ->leftJoin('p.company', 'company')
  183.             ->where('company = :company')
  184.             ->setParameter('company', $company);
  186.         if ($parameters['minQuantity']) {
  187.             $qb
  188.                 ->andWhere('p.quantity > :quantitymin')
  189.                 ->setParameter('quantitymin', $parameters['minQuantity']);
  190.         }
  192.         if ($parameters['maxQuantity']) {
  193.             $qb
  194.                 ->andWhere('p.quantity < :quantitymax')
  195.                 ->setParameter('quantitymax', $parameters['maxQuantity']);
  196.         }
  198.         if ($parameters['minPrice']) {
  199.             $qb
  200.                 ->andWhere('p.price > :minPrice')
  201.                 ->setParameter('minPrice', $parameters['minPrice']);
  202.         }
  204.         if ($parameters['maxPrice']) {
  205.             $qb
  206.                 ->andWhere('p.price < :maxPrice')
  207.                 ->setParameter('maxPrice', $parameters['maxPrice']);
  208.         }
  210.         if ($parameters['keyword']) {
  211.             $qb
  212.                 ->andWhere('p.name LIKE :name')
  213.                 ->setParameter('name', '%'.$parameters['keyword'].'%');
  214.         }
  216.         return $qb->getQuery()->getResult();
  217.     }
  219.     public function productsByCategory(Category $category)
  220.     {
  221.         return $this->createQueryBuilder('p')
  222.             ->leftJoin('p.subCategories', 'subCategory')
  223.             ->addSelect('subCategory')
  224.             ->leftJoin('subCategory.categories', 'category')
  225.             ->addSelect('category')
  226.             ->where('category = :category')
  227.             ->andWhere('p.isActivated = 1')
  228.             ->setParameter('category', $category)
  229.             ->orderBy('p.id', 'DESC')
  230.             ->getQuery()
  231.             ->getResult();
  232.     }
  234.     public function productsByCategoryMaster(SubCategory $subCategory)
  235.     {
  236.         return $this->createQueryBuilder('p')
  237.             ->leftJoin('p.subCategories', 'subCategory')
  238.             ->addSelect('subCategory')
  239.             ->leftJoin('subCategory.categories', 'category')
  240.             ->addSelect('category')
  241.             ->leftJoin('p.company', 'company')
  242.             ->addSelect('company')
  243.             ->where('subCategory = :subCategory')
  244.             ->andWhere('company.isConfirmed = 1')
  245.             ->andWhere('company.isValidDocument = 1')
  246.             ->andWhere('p.isActivated = 1')
  247.             ->setParameter('subCategory', $subCategory)
  248.             ->orderBy('p.id', 'DESC')
  249.             ->getQuery()
  250.             ->getResult();
  251.     }
  253.     public function productsByCategoryEvent(Category $category)
  254.     {
  255.         return $this->createQueryBuilder('p')
  256.             ->leftJoin('p.subCategory', 'subCategory')
  257.             ->addSelect('subCategory')
  258.             ->leftJoin('subCategory.category', 'category')
  259.             ->addSelect('category')
  260.             ->where('category = :category')
  261.             ->andWhere('p.isActivated = 1')
  262.             ->setParameter('category', $category)
  263.             ->orderBy('p.id', 'DESC')
  264.             ->getQuery()
  265.             ->getResult();
  266.     }
  268.     public function productsBySubCategory(SubCategory $subCategory)
  269.     {
  270.         return $this->createQueryBuilder('p')
  271.             ->leftJoin('p.subCategories', 'subCategory')
  272.             ->addSelect('subCategory')
  273.             ->leftJoin('p.company', 'company')
  274.             ->addSelect('company')
  275.             ->where('subCategory = :subCategory')
  276.             ->setParameter('subCategory', $subCategory)
  277.             ->andWhere('company.isConfirmed = 1')
  278.             ->andWhere('company.isValidDocument = 1')
  279.             ->andWhere('p.isActivated = 1')
  280.             ->orderBy('p.id', 'DESC')
  281.             ->getQuery()
  282.             ->getResult();
  283.     }
  285.     public function getBySubcategories(SubCategory $subCategory)
  286.     {
  287.         return $this->createQueryBuilder('p')
  288.             ->leftJoin('p.subCategories', 'subCategory')
  289.             ->where(':subCategory = subCategory')
  290.             ->setParameter('subCategory', $subCategory)
  291.             ->getQuery()
  292.             ->getResult();
  293.     }
  295.     public function getProduct(int $id)
  296.     {
  297.         return $this->createQueryBuilder('p')
  298.             ->leftJoin('p.departments', 'departments')
  299.             ->addSelect('departments')
  300.             ->leftJoin('p.photos', 'photos')
  301.             ->addSelect('photos')
  302.             ->where('p.id = :id')
  303.             ->setParameter('id', $id)
  304.             ->getQuery()
  305.             ->getOneOrNullResult();
  306.     }
  308.     public function getOtherProductsCompany(Product $product)
  309.     {
  310.         return $this->createQueryBuilder('p')
  311.             ->leftJoin('p.photos', 'photos')
  312.             ->leftJoin('p.avis', 'avis')
  313.             ->leftJoin('p.departments', 'departments')
  314.             ->addSelect('photos')
  315.             ->addSelect('avis')
  316.             ->addSelect('departments')
  317.             ->where('p.id != :id')
  318.             ->setParameter('id', $product->getId())
  319.             ->andWhere('p.isActivated = 1')
  320.             ->setMaxResults(3)
  321.             ->orderBy('p.id', 'DESC')
  322.             ->getQuery()
  323.             ->getResult();
  324.     }
  326.     public function getOtherProductsByCompany(Company $company)
  327.     {
  328.         return $this->createQueryBuilder('p')
  329.             ->where('p.company = :company')
  330.             ->setParameter('company', $company)
  331.             ->leftJoin('p.company', 'company')
  332.             ->addSelect('company')
  333.             ->andWhere('company.isConfirmed = 1')
  334.             ->andWhere('company.isValidDocument = 1')
  335.             ->andWhere('p.isActivated = :true')
  336.             ->setParameter('true', 1)
  337.             ->setMaxResults(3)
  338.             ->orderBy('p.id', 'DESC')
  339.             ->getQuery()
  340.             ->getResult();
  341.     }
  342.     public function getPhotoProductsByCompany(Company $company)
  343.     {
  344.         return $this->createQueryBuilder('p')
  345.             ->where('p.company = :company')
  346.             ->setParameter('company', $company)
  347.             ->leftJoin('p.company', 'company')
  348.             ->addSelect('company')
  349.             ->andWhere('company.isConfirmed = 1')
  350.             ->andWhere('company.isValidDocument = 1')
  351.             ->andWhere('p.isActivated = :true')
  352.             ->setParameter('true', 1)
  353.             ->setMaxResults(1)
  354.             ->orderBy('p.id', 'DESC')
  355.             ->getQuery()
  356.             ->getResult();
  357.     }
  359.     public function getAllProductsByCompany(Company $company)
  360.     {
  361.         return $this->createQueryBuilder('p')
  362.             ->where('p.company = :company')
  363.             ->setParameter('company', $company)
  364.             ->leftJoin('p.company', 'company')
  365.             ->addSelect('company')
  366.             ->andWhere('company.isConfirmed = 1')
  367.             ->andWhere('company.isValidDocument = 1')
  368.             ->andWhere('p.isActivated = :true')
  369.             ->setParameter('true', 1)
  370.             ->orderBy('p.id', 'DESC')
  371.             ->getQuery()
  372.             ->getResult();
  373.     }
  375.     public function getOtherProductsCompanyNotSingle(Company $company, Product $product)
  376.     {
  377.         return $this->createQueryBuilder('p')
  378.             ->where('p.company = :company')
  379.             ->setParameter('company', $company)
  380.             ->andWhere('p.isActivated = :true')
  381.             ->setParameter('true', 1)
  382.             ->andWhere('p.id != :idProduct')
  383.             ->setParameter('idProduct', $product->getId())
  384.             ->setMaxResults(3)
  385.             ->orderBy('p.id', 'DESC')
  386.             ->getQuery()
  387.             ->getResult();
  388.     }
  390.     public function findProductByNote()
  391.     {
  392.         return $this->createQueryBuilder('p')
  393.             ->leftJoin('p.avis', 'avis')
  394.             ->addSelect('SUM(avis.globalNote) as countNote')
  395.             ->orderBy('countNote', 'DESC')
  396.             ->groupBy('p')
  397.             ->setMaxResults(4)
  398.             ->getQuery()
  399.             ->getResult();
  400.     }
  402.     public function orderByNotes()
  403.     {
  404.         return $this->createQueryBuilder('p')
  405.             ->leftJoin('p.avis', 'avis')
  406.             ->where('p.isActivated = 1')
  407.             ->orderBy('avis.globalNote', 'DESC')
  408.             ->getQuery()
  409.             ->getResult();
  410.     }
  412.     public function orderByCommands()
  413.     {
  414.         return $this->createQueryBuilder('p')
  415.             ->leftJoin('p.commandProducts', 'commandProducts')
  416.             ->where('p.isActivated = 1')
  417.             ->orderBy('commandProducts.quantity', 'DESC')
  418.             ->setMaxResults(4)
  419.             ->getQuery()
  420.             ->getResult();
  421.     }
  423.     public function findByCritere($critere=null){
  425.         $qb = $this->createQueryBuilder('p');
  426.         $qb->innerjoin('App\Entity\OptionHebergement', 'o', 'WITH', 'o.products = p.id');
  427.         
  428.         if($critere != null && isset($critere["nbrParticipant"]) && $critere["nbrParticipant"] != '' ){
  429.             $qb->andWhere('o.numberOfParticipants = :nbrParticipant');
  430.             $qb->setParameter('nbrParticipant', $critere["nbrParticipant"]);
  431.         }
  432.         
  433.         if($critere != null && isset($critere["nbrAdulte"]) && $critere["nbrAdulte"] != '' ){
  434.             $qb->andWhere('o.numberOfAdults = :nbrAdulte');
  435.             $qb->setParameter('nbrAdulte', $critere["nbrAdulte"]);
  436.         }
  438.         if($critere != null && isset($critere["nbrEnfant"]) && $critere["nbrEnfant"] != '' ){
  439.             $qb->andWhere('o.numberOfChild = :nbrEnfant');
  440.             $qb->setParameter('nbrEnfant', $critere["nbrEnfant"]);
  441.         }
  443.         if($critere != null && isset($critere["nbrLits"]) && $critere["nbrLits"] != '' ){
  444.             $qb->andWhere('o.bedNumber = :nbrLits');
  445.             $qb->setParameter('nbrLits', $critere["nbrLits"]);
  446.         }
  448.         if($critere != null && isset($critere["nbrBebe"]) && $critere["nbrBebe"] != '' ){
  449.             $qb->andWhere('o.numberOfBaby = :nbrBebe');
  450.             $qb->setParameter('nbrEnfant', $critere["nbrBebe"]);
  451.         }
  452.         
  453.         $result = $qb->getQuery()->getResult();
  454.         return $result;
  455.         
  456.         // $qb->leftJoin('App\Entity\Caracteristic', 'c');
  457.         // $qb->leftJoin('o.caracteristics', 'c');
  458.         // $qb->addSelect('c');
  459.         // $qb->addIsPublishedQueryBuilder('App\Entity\Caracteristic', 'c', 'WITH', 'o.products = p.id');
  462.         // if($critere != null && isset($critere["caracteristique"]) && $critere["caracteristique"] != null ){
  463.         //     $caracteristic = '';
  464.         //     foreach ($critere["caracteristique"] as $key => $value) {
  465.         //         # code...
  466.         //         $caracteristic .= $key == 0 ? 'o.caracteristics = :caracteristique'.$key : ' OR o.caracteristics = :caracteristique'.$key;
  467.         //     }
  468.         //     $qb->andWhere($caracteristic);
  469.         //     foreach ($critere["caracteristique"] as $key => $value) {
  470.         //         # code...
  471.         //         $qb->setParameter('caracteristique'.$key, $value);
  472.         //     }
  473.         // }
  476.                     
  477.     }
  479.     /*
  480.     public function findOneBySomeField($value): ?Product
  481.     {
  482.         return $this->createQueryBuilder('p')
  483.             ->andWhere('p.exampleField = :val')
  484.             ->setParameter('val', $value)
  485.             ->getQuery()
  486.             ->getOneOrNullResult()
  487.         ;
  488.     }
  489.     */
  491.     public function filtreHebergement($equipements)
  492.     {
  493.         $qb = $this
  494.             ->createQueryBuilder('p')
  495.             ->leftJoin('p.subCategories', 'subCategory')
  496.             ->leftJoin('p.equipementHebergs', 'equipementHebergs')
  497.             // ->addSelect('hebergementOptions.equipement')
  498.             // ->andWhere('p.id =:idProduct')
  499.             // ->setParameter('idProduct', 2)
  500.             ;
  501.         //  dd($qb);
  502.             // if ($equipements) {
  503.             //     $qb
  504.                     
  505.             //     ->andWhere($qb->expr()->in('equipementHebergs.nomEquipement', $equipements));
  506.             // } 
  507.     
  509.         return $qb->getQuery()->getResult();
  510.     }
  511.     
  512.     public function Hebergement(array $parameters)
  513.     {
  514.         $qb = $this
  515.             ->createQueryBuilder('p')
  516.             ->leftJoin('p.subCategories', 'subCategory')
  517.             ->addSelect('subCategory')
  518.             ->leftJoin('p.chambres', 'chambres')
  519.             ->where('p.isActivated = 1')
  520.             ->leftJoin('p.company', 'company')
  521.             ->andWhere('company.isConfirmed = 1')
  522.             ->andWhere('company.isValidDocument = 1')
  523.             ->leftJoin('p.equipment', 'equipment')
  524.             ->leftJoin('p.caracteristics', 'caracteristics')
  525.             ->leftJoin('p.typeLocalisations', 'typeLocalisations')
  526.             ->leftJoin('p.securities', 'securities')
  527.             ->leftJoin('p.langueHotes', 'langueHotes')
  528.             ;
  530.             if ($parameters['prix'] == "plusCher") {
  531.                 $qb   
  532.                 ->orderBy('chambres.tarifJour', 'ASC');
  533.             } else{
  534.                 $qb   
  535.                 ->orderBy('chambres.tarifJour', 'DESC');
  536.             }
  537.             if (!empty($parameters['equipements'])) {
  538.                 $qb   
  539.                 ->andWhere('equipment.labels IN (:equipements)')
  540.                 ->setParameter('equipements', $parameters['equipements']);
  541.             } 
  542.             if (!empty($parameters['caracteristics'])) {
  543.                 $qb   
  544.                 ->andWhere('caracteristics.labels IN (:caracteristics)')
  545.                 ->setParameter('caracteristics', $parameters['caracteristics']);
  546.             } 
  547.             if (!empty($parameters['typeLocalisations'])) {
  548.                 $qb   
  549.                 ->andWhere('typeLocalisations.slugTypeLocal IN (:typeLocalisation)')
  550.                 ->setParameter('typeLocalisation', $parameters['typeLocalisations']);
  551.             } 
  552.             if (!empty($parameters['securities'])) {
  553.                 $qb   
  554.                 ->andWhere('securities.slugSecurity IN (:securitie)')
  555.                 ->setParameter('securitie', $parameters['securities']);
  556.             } 
  557.             if (!empty($parameters['langueHotes'])) {
  558.                 $qb   
  559.                 ->andWhere('langueHotes.labels IN (:langueHotes)')
  560.                 ->setParameter('langueHotes', $parameters['langueHotes']);
  561.             } 
  562.             return $qb->getQuery()->getResult();
  563.     }
  565.     public function findprestation(float $lat = null, float $lng = null, ?int $radius = 1000, $idprod)
  566.     {
  567.         $query =  $this->findVisibleQuery();
  568.         $query ->andWhere('p.id != :id')
  569.                ->andWhere('p.typeActivity IS NOT NULL')
  570.                ->leftJoin('p.subCategories', 'subCategory')
  571.                ->addSelect('subCategory')
  572.                ->leftJoin('subCategory.categories', 'category')
  573.                ->addSelect('category')
  574.                ->andWhere('category IS NOT NULL')
  575.                ->setParameter('id', $idprod);
  576.     
  577.         $query->andWhere('(6371 * acos(cos(radians(' . $lat . ')) * cos(radians(p.latitude)) * cos(radians(p.longitude) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians(p.latitude)))) < :distance');
  578.         $query->setParameter('distance', $radius);
  579.         return $query->getQuery()
  580.         ->getResult();
  581.     }
  582.     private function findVisibleQuery()
  583.     {
  584.         return $this->createQueryBuilder('p');
  585.     }
  587. }