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.                 ->andWhere('p.cityLocalisation = :city')
  92.                 ->setParameter('city', $parameters['city']);
  93.         }
  95.         if ($parameters['min_price']) {
  96.             $qb
  97.                 ->andWhere('p.price > :price')
  98.                 ->setParameter('price', $parameters['min_price']);
  99.         }
  101.         if ($parameters['category']) {
  102.             $qb
  103.                 ->andWhere($qb->expr()->in('subCategory.id', $parameters['category']));
  104.         } /*elseif ($parameters['categoryMaster']) {
  106.             $subCategories = $this->subCategoryRepository->getSubCategoriesByCategory($parameters['categoryMaster']);
  107.             $arrayId = [];
  108.             foreach ($subCategories as $subCategory) {
  109.                 $arrayId[] = $subCategory['id'];
  110.             }
  112.             $qb
  113.                 ->andWhere('subCategory.id IN (:idMaster)')
  114.                 ->setParameter('idMaster', $arrayId);
  115.         }*/
  117.         if ($parameters['type']) {
  118.             if ($parameters['type'] === 'service') {
  119.                 $qb
  120.                     ->andWhere('subCategory.isService = 1');
  121.             } else {
  122.                 $qb
  123.                     ->andWhere('subCategory.isService = 0');
  124.             }
  125.         }
  127.         if ($parameters['nbGuest']) {
  128.             $qb
  129.                 ->andWhere('p.nbGuest > :nbGuest')
  130.                 ->orWhere('p.nbGuest IS NULL')
  131.                 ->setParameter('nbGuest', $parameters['nbGuest']);
  132.         }
  134.         if ($parameters['max_price']) {
  135.             $qb
  136.                 ->andWhere('p.price < :maxprice')
  137.                 ->setParameter('maxprice', $parameters['max_price']);
  138.         }
  140.         if ($parameters['keyword']) {
  141.             $qb
  142.                 ->andWhere('p.name LIKE :name')
  143.                 ->setParameter('name', '%'.$parameters['keyword'].'%');
  144.         }
  146.         if ($parameters['weddingdate_start'] && $parameters['weddingdate_end']) {
  147.             $weddingDateStart = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate_start'])->format('Y-m-d');
  148.             $weddingDateEnd = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate_end'])->format('Y-m-d');
  149.             
  150.             $qb
  151.                 ->andWhere('(productAgendas.startDayNotAvailable IS NULL OR :weddingdate_end < productAgendas.startDayNotAvailable OR :weddingdate_start > productAgendas.endDayNotAvailable)')
  152.                 ->setParameter('weddingdate_start', $weddingDateStart)
  153.                 ->setParameter('weddingdate_end', $weddingDateEnd);
  154.         }
  156.         if ($parameters['weddingdate']) {
  157.             $weddingdate = \DateTime::createFromFormat('d/m/Y', $parameters['weddingdate'])->format('Y-m-d');
  158.         
  159.             $qb
  160.                 ->andWhere('(productAgendas.startDayNotAvailable IS NULL OR :weddingdate < productAgendas.startDayNotAvailable OR :weddingdate > productAgendas.endDayNotAvailable)')
  161.                 ->setParameter('weddingdate', $weddingdate);
  162.         }
  163.         
  165. /*
  166.         if ($parameters['category']) {
  167.             if ($parameters['category'] !== 'Service') {
  168.                 $qb
  169.                     ->andWhere('subCategory IN (:category)')
  170.                     ->setParameter('category', $parameters['category']);
  171.             }
  172.         }*/
  174.         return $qb->orderBy('p.id', 'DESC')->getQuery()->getResult();
  175.     }
  177.     public function searchProductCompany(array $parameters, Company $company)
  178.     {
  179.         $qb = $this
  180.             ->createQueryBuilder('p')
  181.             ->leftJoin('p.company', 'company')
  182.             ->where('company = :company')
  183.             ->setParameter('company', $company);
  185.         if ($parameters['minQuantity']) {
  186.             $qb
  187.                 ->andWhere('p.quantity > :quantitymin')
  188.                 ->setParameter('quantitymin', $parameters['minQuantity']);
  189.         }
  191.         if ($parameters['maxQuantity']) {
  192.             $qb
  193.                 ->andWhere('p.quantity < :quantitymax')
  194.                 ->setParameter('quantitymax', $parameters['maxQuantity']);
  195.         }
  197.         if ($parameters['minPrice']) {
  198.             $qb
  199.                 ->andWhere('p.price > :minPrice')
  200.                 ->setParameter('minPrice', $parameters['minPrice']);
  201.         }
  203.         if ($parameters['maxPrice']) {
  204.             $qb
  205.                 ->andWhere('p.price < :maxPrice')
  206.                 ->setParameter('maxPrice', $parameters['maxPrice']);
  207.         }
  209.         if ($parameters['keyword']) {
  210.             $qb
  211.                 ->andWhere('p.name LIKE :name')
  212.                 ->setParameter('name', '%'.$parameters['keyword'].'%');
  213.         }
  215.         return $qb->getQuery()->getResult();
  216.     }
  218.     public function productsByCategory(Category $category)
  219.     {
  220.         return $this->createQueryBuilder('p')
  221.             ->leftJoin('p.subCategories', 'subCategory')
  222.             ->addSelect('subCategory')
  223.             ->leftJoin('subCategory.categories', 'category')
  224.             ->addSelect('category')
  225.             ->where('category = :category')
  226.             ->andWhere('p.isActivated = 1')
  227.             ->setParameter('category', $category)
  228.             ->orderBy('p.id', 'DESC')
  229.             ->getQuery()
  230.             ->getResult();
  231.     }
  233.     public function productsByCategoryMaster(SubCategory $subCategory)
  234.     {
  235.         return $this->createQueryBuilder('p')
  236.             ->leftJoin('p.subCategories', 'subCategory')
  237.             ->addSelect('subCategory')
  238.             ->leftJoin('subCategory.categories', 'category')
  239.             ->addSelect('category')
  240.             ->leftJoin('p.company', 'company')
  241.             ->addSelect('company')
  242.             ->where('subCategory = :subCategory')
  243.             ->andWhere('company.isConfirmed = 1')
  244.             ->andWhere('company.isValidDocument = 1')
  245.             ->andWhere('p.isActivated = 1')
  246.             ->setParameter('subCategory', $subCategory)
  247.             ->orderBy('p.id', 'DESC')
  248.             ->getQuery()
  249.             ->getResult();
  250.     }
  252.     public function productsByCategoryEvent(Category $category)
  253.     {
  254.         return $this->createQueryBuilder('p')
  255.             ->leftJoin('p.subCategory', 'subCategory')
  256.             ->addSelect('subCategory')
  257.             ->leftJoin('subCategory.category', 'category')
  258.             ->addSelect('category')
  259.             ->where('category = :category')
  260.             ->andWhere('p.isActivated = 1')
  261.             ->setParameter('category', $category)
  262.             ->orderBy('p.id', 'DESC')
  263.             ->getQuery()
  264.             ->getResult();
  265.     }
  267.     public function productsBySubCategory(SubCategory $subCategory)
  268.     {
  269.         return $this->createQueryBuilder('p')
  270.             ->leftJoin('p.subCategories', 'subCategory')
  271.             ->addSelect('subCategory')
  272.             ->leftJoin('p.company', 'company')
  273.             ->addSelect('company')
  274.             ->where('subCategory = :subCategory')
  275.             ->setParameter('subCategory', $subCategory)
  276.             ->andWhere('company.isConfirmed = 1')
  277.             ->andWhere('company.isValidDocument = 1')
  278.             ->andWhere('p.isActivated = 1')
  279.             ->orderBy('p.id', 'DESC')
  280.             ->getQuery()
  281.             ->getResult();
  282.     }
  284.     public function getBySubcategories(SubCategory $subCategory)
  285.     {
  286.         return $this->createQueryBuilder('p')
  287.             ->leftJoin('p.subCategories', 'subCategory')
  288.             ->where(':subCategory = subCategory')
  289.             ->setParameter('subCategory', $subCategory)
  290.             ->getQuery()
  291.             ->getResult();
  292.     }
  294.     public function getProduct(int $id)
  295.     {
  296.         return $this->createQueryBuilder('p')
  297.             ->leftJoin('p.departments', 'departments')
  298.             ->addSelect('departments')
  299.             ->leftJoin('p.photos', 'photos')
  300.             ->addSelect('photos')
  301.             ->where('p.id = :id')
  302.             ->setParameter('id', $id)
  303.             ->getQuery()
  304.             ->getOneOrNullResult();
  305.     }
  307.     public function getOtherProductsCompany(Product $product)
  308.     {
  309.         return $this->createQueryBuilder('p')
  310.             ->leftJoin('p.photos', 'photos')
  311.             ->leftJoin('p.avis', 'avis')
  312.             ->leftJoin('p.departments', 'departments')
  313.             ->addSelect('photos')
  314.             ->addSelect('avis')
  315.             ->addSelect('departments')
  316.             ->where('p.id != :id')
  317.             ->setParameter('id', $product->getId())
  318.             ->andWhere('p.isActivated = 1')
  319.             ->setMaxResults(3)
  320.             ->orderBy('p.id', 'DESC')
  321.             ->getQuery()
  322.             ->getResult();
  323.     }
  325.     public function getOtherProductsByCompany(Company $company)
  326.     {
  327.         return $this->createQueryBuilder('p')
  328.             ->where('p.company = :company')
  329.             ->setParameter('company', $company)
  330.             ->leftJoin('p.company', 'company')
  331.             ->addSelect('company')
  332.             ->andWhere('company.isConfirmed = 1')
  333.             ->andWhere('company.isValidDocument = 1')
  334.             ->andWhere('p.isActivated = :true')
  335.             ->setParameter('true', 1)
  336.             ->setMaxResults(3)
  337.             ->orderBy('p.id', 'DESC')
  338.             ->getQuery()
  339.             ->getResult();
  340.     }
  341.     public function getPhotoProductsByCompany(Company $company)
  342.     {
  343.         return $this->createQueryBuilder('p')
  344.             ->where('p.company = :company')
  345.             ->setParameter('company', $company)
  346.             ->leftJoin('p.company', 'company')
  347.             ->addSelect('company')
  348.             ->andWhere('company.isConfirmed = 1')
  349.             ->andWhere('company.isValidDocument = 1')
  350.             ->andWhere('p.isActivated = :true')
  351.             ->setParameter('true', 1)
  352.             ->setMaxResults(1)
  353.             ->orderBy('p.id', 'DESC')
  354.             ->getQuery()
  355.             ->getResult();
  356.     }
  358.     public function getAllProductsByCompany(Company $company)
  359.     {
  360.         return $this->createQueryBuilder('p')
  361.             ->where('p.company = :company')
  362.             ->setParameter('company', $company)
  363.             ->leftJoin('p.company', 'company')
  364.             ->addSelect('company')
  365.             ->andWhere('company.isConfirmed = 1')
  366.             ->andWhere('company.isValidDocument = 1')
  367.             ->andWhere('p.isActivated = :true')
  368.             ->setParameter('true', 1)
  369.             ->orderBy('p.id', 'DESC')
  370.             ->getQuery()
  371.             ->getResult();
  372.     }
  374.     public function getOtherProductsCompanyNotSingle(Company $company, Product $product)
  375.     {
  376.         return $this->createQueryBuilder('p')
  377.             ->where('p.company = :company')
  378.             ->setParameter('company', $company)
  379.             ->andWhere('p.isActivated = :true')
  380.             ->setParameter('true', 1)
  381.             ->andWhere('p.id != :idProduct')
  382.             ->setParameter('idProduct', $product->getId())
  383.             ->setMaxResults(3)
  384.             ->orderBy('p.id', 'DESC')
  385.             ->getQuery()
  386.             ->getResult();
  387.     }
  389.     public function findProductByNote()
  390.     {
  391.         return $this->createQueryBuilder('p')
  392.             ->leftJoin('p.avis', 'avis')
  393.             ->addSelect('SUM(avis.globalNote) as countNote')
  394.             ->orderBy('countNote', 'DESC')
  395.             ->groupBy('p')
  396.             ->setMaxResults(4)
  397.             ->getQuery()
  398.             ->getResult();
  399.     }
  401.     public function orderByNotes()
  402.     {
  403.         return $this->createQueryBuilder('p')
  404.             ->leftJoin('p.avis', 'avis')
  405.             ->where('p.isActivated = 1')
  406.             ->orderBy('avis.globalNote', 'DESC')
  407.             ->getQuery()
  408.             ->getResult();
  409.     }
  411.     public function orderByCommands()
  412.     {
  413.         return $this->createQueryBuilder('p')
  414.             ->leftJoin('p.commandProducts', 'commandProducts')
  415.             ->where('p.isActivated = 1')
  416.             ->orderBy('commandProducts.quantity', 'DESC')
  417.             ->setMaxResults(4)
  418.             ->getQuery()
  419.             ->getResult();
  420.     }
  422.     public function findByCritere($critere=null){
  424.         $qb = $this->createQueryBuilder('p');
  425.         $qb->innerjoin('App\Entity\OptionHebergement', 'o', 'WITH', 'o.products = p.id');
  426.         
  427.         if($critere != null && isset($critere["nbrParticipant"]) && $critere["nbrParticipant"] != '' ){
  428.             $qb->andWhere('o.numberOfParticipants = :nbrParticipant');
  429.             $qb->setParameter('nbrParticipant', $critere["nbrParticipant"]);
  430.         }
  431.         
  432.         if($critere != null && isset($critere["nbrAdulte"]) && $critere["nbrAdulte"] != '' ){
  433.             $qb->andWhere('o.numberOfAdults = :nbrAdulte');
  434.             $qb->setParameter('nbrAdulte', $critere["nbrAdulte"]);
  435.         }
  437.         if($critere != null && isset($critere["nbrEnfant"]) && $critere["nbrEnfant"] != '' ){
  438.             $qb->andWhere('o.numberOfChild = :nbrEnfant');
  439.             $qb->setParameter('nbrEnfant', $critere["nbrEnfant"]);
  440.         }
  442.         if($critere != null && isset($critere["nbrLits"]) && $critere["nbrLits"] != '' ){
  443.             $qb->andWhere('o.bedNumber = :nbrLits');
  444.             $qb->setParameter('nbrLits', $critere["nbrLits"]);
  445.         }
  447.         if($critere != null && isset($critere["nbrBebe"]) && $critere["nbrBebe"] != '' ){
  448.             $qb->andWhere('o.numberOfBaby = :nbrBebe');
  449.             $qb->setParameter('nbrEnfant', $critere["nbrBebe"]);
  450.         }
  451.         
  452.         $result = $qb->getQuery()->getResult();
  453.         return $result;
  454.         
  455.         // $qb->leftJoin('App\Entity\Caracteristic', 'c');
  456.         // $qb->leftJoin('o.caracteristics', 'c');
  457.         // $qb->addSelect('c');
  458.         // $qb->addIsPublishedQueryBuilder('App\Entity\Caracteristic', 'c', 'WITH', 'o.products = p.id');
  461.         // if($critere != null && isset($critere["caracteristique"]) && $critere["caracteristique"] != null ){
  462.         //     $caracteristic = '';
  463.         //     foreach ($critere["caracteristique"] as $key => $value) {
  464.         //         # code...
  465.         //         $caracteristic .= $key == 0 ? 'o.caracteristics = :caracteristique'.$key : ' OR o.caracteristics = :caracteristique'.$key;
  466.         //     }
  467.         //     $qb->andWhere($caracteristic);
  468.         //     foreach ($critere["caracteristique"] as $key => $value) {
  469.         //         # code...
  470.         //         $qb->setParameter('caracteristique'.$key, $value);
  471.         //     }
  472.         // }
  475.                     
  476.     }
  478.     /*
  479.     public function findOneBySomeField($value): ?Product
  480.     {
  481.         return $this->createQueryBuilder('p')
  482.             ->andWhere('p.exampleField = :val')
  483.             ->setParameter('val', $value)
  484.             ->getQuery()
  485.             ->getOneOrNullResult()
  486.         ;
  487.     }
  488.     */
  490.     public function filtreHebergement($equipements)
  491.     {
  492.         $qb = $this
  493.             ->createQueryBuilder('p')
  494.             ->leftJoin('p.subCategories', 'subCategory')
  495.             ->leftJoin('p.equipementHebergs', 'equipementHebergs')
  496.             // ->addSelect('hebergementOptions.equipement')
  497.             // ->andWhere('p.id =:idProduct')
  498.             // ->setParameter('idProduct', 2)
  499.             ;
  500.         //  dd($qb);
  501.             // if ($equipements) {
  502.             //     $qb
  503.                     
  504.             //     ->andWhere($qb->expr()->in('equipementHebergs.nomEquipement', $equipements));
  505.             // } 
  506.     
  508.         return $qb->getQuery()->getResult();
  509.     }
  510.     
  511.     public function Hebergement(array $parameters)
  512.     {
  513.         $qb = $this
  514.             ->createQueryBuilder('p')
  515.             ->leftJoin('p.subCategories', 'subCategory')
  516.             ->addSelect('subCategory')
  517.             ->leftJoin('p.chambres', 'chambres')
  518.             ->where('p.isActivated = 1')
  519.             ->leftJoin('p.company', 'company')
  520.             ->andWhere('company.isConfirmed = 1')
  521.             ->andWhere('company.isValidDocument = 1')
  522.             ->leftJoin('p.equipment', 'equipment')
  523.             ->leftJoin('p.caracteristics', 'caracteristics')
  524.             ->leftJoin('p.typeLocalisations', 'typeLocalisations')
  525.             ->leftJoin('p.securities', 'securities')
  526.             ->leftJoin('p.langueHotes', 'langueHotes')
  527.             ;
  529.             if ($parameters['prix'] == "plusCher") {
  530.                 $qb   
  531.                 ->orderBy('chambres.tarifJour', 'ASC');
  532.             } else{
  533.                 $qb   
  534.                 ->orderBy('chambres.tarifJour', 'DESC');
  535.             }
  536.             if (!empty($parameters['equipements'])) {
  537.                 $qb   
  538.                 ->andWhere('equipment.labels IN (:equipements)')
  539.                 ->setParameter('equipements', $parameters['equipements']);
  540.             } 
  541.             if (!empty($parameters['caracteristics'])) {
  542.                 $qb   
  543.                 ->andWhere('caracteristics.labels IN (:caracteristics)')
  544.                 ->setParameter('caracteristics', $parameters['caracteristics']);
  545.             } 
  546.             if (!empty($parameters['typeLocalisations'])) {
  547.                 $qb   
  548.                 ->andWhere('typeLocalisations.slugTypeLocal IN (:typeLocalisation)')
  549.                 ->setParameter('typeLocalisation', $parameters['typeLocalisations']);
  550.             } 
  551.             if (!empty($parameters['securities'])) {
  552.                 $qb   
  553.                 ->andWhere('securities.slugSecurity IN (:securitie)')
  554.                 ->setParameter('securitie', $parameters['securities']);
  555.             } 
  556.             if (!empty($parameters['langueHotes'])) {
  557.                 $qb   
  558.                 ->andWhere('langueHotes.labels IN (:langueHotes)')
  559.                 ->setParameter('langueHotes', $parameters['langueHotes']);
  560.             } 
  561.             return $qb->getQuery()->getResult();
  562.     }
  564.     public function findprestation(float $lat = null, float $lng = null, ?int $radius = 1000, $idprod)
  565.     {
  566.         $query =  $this->findVisibleQuery();
  567.         $query ->andWhere('p.id != :id')
  568.                ->andWhere('p.typeActivity IS NOT NULL')
  569.                ->leftJoin('p.subCategories', 'subCategory')
  570.                ->addSelect('subCategory')
  571.                ->leftJoin('subCategory.categories', 'category')
  572.                ->addSelect('category')
  573.                ->andWhere('category IS NOT NULL')
  574.                ->setParameter('id', $idprod);
  575.     
  576.         $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');
  577.         $query->setParameter('distance', $radius);
  578.         return $query->getQuery()
  579.         ->getResult();
  580.     }
  581.     private function findVisibleQuery()
  582.     {
  583.         return $this->createQueryBuilder('p');
  584.     }
  586. }