src/Repository/PostRepository.php line 47

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Post;
  6. use App\Entity\PostTranslation;
  7. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  8. use Doctrine\Persistence\ManagerRegistry;
  9. use Symfony\Component\Intl\Locale;
  10. use App\Pagination\Paginator;
  12. /**
  13.  * @method Post|null find($id, $lockMode = null, $lockVersion = null)
  14.  * @method Post|null findOneBy(array $criteria, array $orderBy = null)
  15.  * @method Post[]    findAll()
  16.  * @method Post[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  17.  */
  18. class PostRepository extends ServiceEntityRepository
  19. {
  20.     public function __construct(ManagerRegistry $registry)
  21.     {
  22.         parent::__construct($registry, Post::class);
  23.     }
  25.     /** find conut nb resultat post **/
  26.     public function findCountPost($type = null)
  27.     {
  29.         $locale = Locale::getDefault();
  30.         $sql =  $this->createQueryBuilder('p')
  31.                      ->select('count(p.id)');
  32.       
  33.         if($type != null){
  34.             $sql->join('p.type_post','typePost')
  35.                 ->andWhere('typePost.id = :type')
  36.                 ->setParameter('type', $type);
  37.         }
  39.         return $sql->getQuery()->getSingleScalarResult();
  40.     }
  41.     /** end off method **/
  43.     public function findPostBySlug($slug)
  44.     {
  46.         $locale = Locale::getDefault();
  47.         dump($locale);
  48.         $sql =  $this->createQueryBuilder('p')
  49.                      ->join('p.translations','t')
  50.                      ->andWhere('t.locale = :locale')
  51.                      ->setParameter('locale', $locale);
  52.       
  53.         if($slug){
  54.             $sql->andWhere('t.slug = :slug')
  55.                 ->setParameter('slug', $slug);
  56.         }
  58.         return $sql->getQuery()->getOneOrNullResult();
  59.     }
  61.     public function findPostById($id)
  62.     {
  63.         $locale = Locale::getDefault();
  64.         $sql =  $this->createQueryBuilder('p')
  65.                      ->join('p.translations','t')
  66.                      ->andWhere('p.id = :id')
  67.                      ->andWhere('t.actif = true')
  68.                      ->andWhere('t.locale = :locale')
  69.                      ->setParameter('locale', $locale)
  70.                      ->setParameter('id', $id);
  71.       
  72.         return $sql->getQuery()->getResult();
  73.     }
  75.         
  76.     public function findAllPostByType($locale,$type)
  77.     {
  78.        
  79.         $sql =  $this->createQueryBuilder('p')
  80.                      ->join('p.translations','t')
  81.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  82.                      ->andWhere('t.locale = :locale')
  83.                      ->setParameter('locale', $locale);
  85.         if($type){
  86.             $sql->join('p.type_post','tp')
  87.                 ->andWhere('tp.id = :type')
  88.                 ->setParameter('type', $type);
  90.             // if($type == '6'){
  91.             //     $sql->orderBy('p.date_publication','DESC');
  92.             // }
  93.         }
  94.      
  95.         $sql->orderBy('p.position','ASC');
  96.         return $sql->getQuery()->getResult();
  97.     }
  99.     public function findAllOtherPosts($id)
  100.     {
  101.         $locale = Locale::getDefault();
  102.         $sql =  $this->createQueryBuilder('p')
  103.                      ->join('p.translations','t')
  104.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  105.                      ->andWhere('t.locale = :locale')
  106.                      ->andWhere('t.actif = true')
  107.                      ->setParameter('locale', $locale);
  108.                     if($id){
  109.                     $sql->andWhere('p.id != :id')
  110.                         ->setParameter('id', $id);
  111.                     }
  113.      
  114.         $sql->orderBy('p.position','ASC');
  115.         return $sql->getQuery()->getResult();
  116.     }
  118.     public function findAllActivesPostsByType($locale,$type)
  119.     {
  120.        
  121.         $sql =  $this->createQueryBuilder('p')
  122.                      ->join('p.translations','t')
  123.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  124.                      ->andWhere('t.locale = :locale')
  125.                      ->setParameter('locale', $locale);
  127.         if($type){
  128.             $sql->join('p.type_post','tp')
  129.                 ->andWhere('tp.id = :type')
  130.                 ->andWhere('t.actif = true')
  131.                 ->setParameter('type', $type);
  133.         }
  135.         $sql->orderBy('p.position','asc');
  136.         
  137.         return $sql->getQuery()->getResult();
  138.     }
  140.     public function findPostByType($type,$option,$nb)
  141.     {
  142.         $locale = Locale::getDefault();
  143.         $sql =  $this->createQueryBuilder('p')
  144.                      ->join('p.translations','t')
  145.                      ->join('p.type_post','typePost')
  146.                      ->andWhere('typePost.id = :type')
  147.                      ->andWhere('t.actif = true')
  148.                      ->andWhere('t.locale = :locale')
  149.                      ->setParameter('locale', $locale)
  150.                      ->setParameter('type', $type)
  151.                      ->setMaxResults($nb);
  153.         if($option == '1'){
  154.             $sql->orderBy('p.id', 'ASC');
  155.         }elseif($option == '2'){
  156.             $sql->orderBy('p.id', 'DESC');
  157.         }
  158.       
  159.         return $sql->getQuery()->getResult();
  160.     }
  162.     public function findPostActifsHomeByType($type,$option,$nb = 1,$all_post = false)
  163.     {
  164.         $locale = Locale::getDefault();
  165.         $sql =  $this->createQueryBuilder('p')
  166.                      ->join('p.translations','t')
  167.                      ->join('p.type_post','typePost')
  168.                      ->andWhere('typePost.id = :type')
  169.                      ->andWhere('t.actif = true')
  170.                      ->andWhere('p.actif_home = 1')
  171.                      ->andWhere('t.locale = :locale')
  172.                      ->setParameter('locale', $locale)
  173.                      ->setParameter('type', $type);
  175.                      
  177.         if($all_post == false){
  178.            $sql->setMaxResults($nb);
  179.         }
  181.         if($option == '1'){
  182.             $sql->orderBy('p.id', 'ASC');
  183.         }elseif($option == '2'){
  184.             $sql->orderBy('p.id', 'DESC');
  185.         }elseif($option == '3'){
  186.             $sql->orderBy('p.date_publication','DESC');
  187.         }elseif($option == '4'){
  188.             $sql->orderBy('p.position', 'ASC');
  189.         }elseif($option == '5'){
  190.             $sql->orderBy('RAND()');
  191.         }
  192.       
  193.         return $sql->getQuery()->getResult();
  194.     }
  196.     public function findPostByTypeInterne($type,$option,$nb)
  197.     {
  198.         $locale = Locale::getDefault();
  199.         $sql =  $this->createQueryBuilder('p')
  200.                      ->join('p.translations','t')
  201.                      ->join('p.type_post','typePost')
  202.                      ->andWhere('typePost.id = :type')
  203.                      ->andWhere('t.actif = true')
  204.                      ->andWhere('t.locale = :locale')
  205.                      ->setParameter('locale', $locale)
  206.                      ->setParameter('type', $type)
  207.                      ->setMaxResults($nb);
  209.         if($option == '1'){
  210.             $sql->orderBy('p.id', 'ASC');
  211.         }elseif($option == '2'){
  212.             $sql->orderBy('p.id', 'DESC');
  213.         }elseif($option == '3'){
  214.             $sql->orderBy('p.date_publication','DESC');
  215.         }elseif($option == '4'){
  216.             $sql->orderBy('p.position', 'ASC');
  217.         }elseif($option == '5'){
  218.             $sql->orderBy('RAND()');
  219.         }
  220.       
  221.         return $sql->getQuery()->getResult();
  222.     }
  224.     
  225.     public function findPostBySystemName($name)
  226.     {
  227.         $locale = Locale::getDefault();
  229.         $sql =  $this->createQueryBuilder('p')
  230.                      ->join('p.type_post','typePost')
  231.                      ->join('typePost.translations','translation')
  232.                      ->join('p.translations','t')
  233.                      ->andWhere('t.actif = true')
  234.                      ->andWhere('translation.system_name = :name')
  235.                      ->andWhere('translation.locale = :locale')
  236.                      ->setParameter('locale', $locale)
  237.                      ->setParameter('name', $name)
  238.                      ->getQuery()
  239.                      ->getResult();
  240.      
  241.         return $sql;
  242.     }
  245.     public function findPostBySystemNames($system_name)
  246.     {
  247.         $locale = Locale::getDefault();
  249.         $sql =  $this->createQueryBuilder('p')
  250.                      ->join('p.type_post','typePost')
  251.                      ->join('typePost.translations','translation')
  252.                      ->join('p.translations','t')
  253.                      ->andWhere('t.actif = true')
  254.                      ->andWhere("translation.system_name IN(:system_name)")
  255.                      ->andWhere('translation.locale = :locale')
  256.                      ->setParameter('locale', $locale)
  257.                      ->setParameter('system_name', $system_name)
  258.                      ->getQuery()
  259.                      ->getResult();
  260.      
  261.         return $sql;
  262.     }
  264.     public function findPostMaxPosition()
  265.     {
  266.         $sql =  $this->createQueryBuilder('p')
  267.                      ->select('MAX(p.position) as max_position');
  269.         return $sql->getQuery()->getOneOrNullResult();
  270.     }
  273.     public function findPostByCategory($categorie,$page = 1)
  274.     {
  275.         
  276.         $id_categorie = $categorie->getId();
  277.         $pagination = $categorie->getPagination() != null ? $categorie->getPagination() : false;
  278.         $page_size = $categorie->getPageSize() != null ? $categorie->getPageSize() : 10;
  280.         $order_by = 1;
  281.         $order_by = $categorie->getOrderBy();
  282.         $req_order = "p.position";
  283.         $asc_desc = "ASC";
  284.         if($order_by == 0){
  285.             $req_order = "p.date_publication";
  286.             $asc_desc = "DESC";
  287.         }
  289.         $locale = Locale::getDefault();
  290.         $sql =  $this->createQueryBuilder('p')
  291.                      ->join('p.translations','t')
  292.                      ->join('p.PostCategory','post_categorie')
  293.                      ->andWhere('t.actif = true')
  294.                      ->andWhere('post_categorie.Category =:categorie_id')
  295.                      ->andWhere('t.locale = :locale')
  296.                      ->setParameter('locale', $locale)
  297.                      ->setParameter('categorie_id', $id_categorie)
  298.                      ->orderBy($req_order, $asc_desc);
  301.         if ($pagination == true) {
  302.             return (new Paginator($sql))->paginate($page,$page_size);
  303.         }else{
  304.             return $sql->getQuery()->getResult();
  305.         }
  307.     }
  309.     /**
  310.      * Retourne les posts par type et par id avec limit
  311.      */
  313.     public function getPostsWithLimit($locale,$type,$limit,$order,$IdSite)
  314.     {
  315.        
  316.         $sql =  $this->createQueryBuilder('p')
  317.                      ->join('p.translations','t')
  318.                      ->andWhere('t.locale = :locale')
  319.                      ->setParameter('locale', $locale);
  321.         if($type){
  322.             $sql->join('p.type_post','tp')
  323.                 ->andWhere('tp.id = :type')
  324.                 ->setParameter('type', $type);
  326.            /* if($type == '6'){
  327.               $sql->orderBy('p.date_publication','DESC');
  328.            } */
  329.         }
  331.         switch ($order) {
  332.             case 0:
  333.                 $sql->orderBy('RAND()');
  334.                 break;
  335.             case 1:
  336.                 $sql->orderBy('p.id','ASC');
  337.                 break;
  338.             case 2:
  339.                 $sql->orderBy('p.id','DESC');
  340.                 break;
  341.             case 3:
  342.                 $sql->orderBy('p.date_publication','DESC');
  343.                 break;
  344.             
  345.             default:
  346.                 $sql->orderBy('p.id','ASC');
  347.                 break;
  348.         }
  350.         if($limit){
  351.             $sql->setMaxResults($limit);
  352.         }
  353.      
  354.         return $sql->getQuery()->getResult();
  355.     }
  357.     /**
  358.      * Retourne la liste des catégories du post
  359.      */
  361.     public function getCategoryPost($post_category, $_locale){
  362.         $categories = [];
  364.         if($post_category){
  365.             foreach ($post_category as $key => $post_cat) {
  366.               if($post_cat->getCategory()){
  367.                 $cat = $post_cat->getCategory();
  368.                 $categories[] = ['id'=> $cat->getId(),'title'=> $cat->translate($_locale)->getTitreCategorie()];
  369.               }
  370.             }
  371.          }
  373.          return $categories;
  374.     }
  377.     // get article prev or next   
  378.     public function findPostPrevOrNext($ids_categories,$id_post,$action = 'next')
  379.     {
  381.         $locale = Locale::getDefault();
  382.         $sql =  $this->createQueryBuilder('p')
  383.                      ->join('p.translations','t')
  384.                      ->join('p.PostCategory','post_categorie')
  385.                      ->andWhere('t.actif = true')
  386.                      ->andWhere('p.id != :id_post')
  387.                      ->andWhere('post_categorie.Category IN (:ids_categories)')
  388.                      ->andWhere('t.locale = :locale')
  389.                      ->setParameter('locale', $locale)
  390.                      ->setParameter('ids_categories', $ids_categories)
  391.                      ->setMaxResults(1);
  393.                     
  394.         if($action == 'next') {
  395.              $sql->andWhere('p.id > :id_post')
  396.                  ->orderBy('p.id', 'asc');
  397.         }else{
  398.              $sql->andWhere('p.id < :id_post')
  399.                  ->orderBy('p.id', 'DESC');
  400.         }
  402.         $sql->setParameter('id_post', $id_post);
  404.         return $sql->getQuery()->getOneOrNullResult();
  406.     }
  409.     /*****************************************************************
  410.      **     get liste actualite related with secteur activte       ***
  411.      *****************************************************************/
  412.     public function findActualiteByActivite()
  413.     {
  414.         $locale = Locale::getDefault();
  416.         $sql =  $this->createQueryBuilder('p')
  417.                      ->andWhere('p.id_activite IS NOT NULL or p.id_sous_activite IS NOT NULL or p.id_secteur IS NOT NULL')
  418.                      ->join('p.translations','t')
  419.                      ->andWhere('t.actif = true')
  420.                      ->andWhere('t.locale = :locale')
  421.                      ->setParameter('locale', $locale)
  422.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  423.                      ->setParameter('currentDate', new \DateTime())
  424.                      ->join('p.type_post','typepost')
  425.                      ->andWhere('typepost.id != 11')
  426.                      ->getQuery()
  427.                      ->getResult();
  428.      
  429.         return $sql;
  430.     }
  434.     /*****************************************************************
  435.     **     requete sql ref                                         ***
  436.     *****************************************************************/
  437.     public function findPostByIdSecteur($id,$id_activite,$id_secteur)
  438.     {
  439.         $locale = Locale::getDefault();
  441.         $sql =  $this->createQueryBuilder('p')
  442.                      ->andWhere('p.id != :id')
  443.                      ->join('p.translations','t')
  444.                      ->andWhere('t.actif = true')
  445.                      ->andWhere('p.id_secteur = :id_secteur')
  446.                      ->andWhere('p.id_activite = :id_activite')
  447.                      ->andWhere('t.locale = :locale')
  448.                      ->setParameter('locale', $locale)
  449.                      ->setParameter('id_secteur', $id_secteur)
  450.                      ->setParameter('id_activite', $id_activite)
  451.                      ->setParameter('id', $id)
  452.                      ->getQuery()
  453.                      ->getResult();
  454.      
  455.         return $sql;
  456.     }
  459.     public function findPageRefByIdActivite($id_activite,$id_diff = null)
  460.     {
  461.         $locale = Locale::getDefault();
  462.         $sql =  $this->createQueryBuilder('p')
  463.                      ->join('p.translations','t')
  464.                      ->join('p.type_post','typepost')
  465.                      ->andWhere('typepost.id = 11')
  466.                      ->andWhere('t.actif = true')
  467.                      ->andWhere('p.id_activite = :id_activite')
  468.                      ->andWhere('t.locale = :locale')
  469.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  470.                      ->setParameter('currentDate', new \DateTime())
  471.                      ->setParameter('locale', $locale)
  472.                      ->setParameter('id_activite', $id_activite)
  473.                      ->andWhere('p.id_sous_activite is NULL')
  474.                      ->setMaxResults(3)
  475.                      ->orderBy('p.id','desc');
  476.                   
  478.         if ($id_diff != null) {
  479.             $sql->andWhere('p.id != :id_diff')
  480.                 ->setParameter('id_diff', $id_diff);
  481.         }
  482.      
  483.         return $sql->getQuery()->getResult();
  484.     }
  488.     public function findPageRefByIdSousActivite($id_sous_activite,$id_diff = null)
  489.     {
  490.         $locale = Locale::getDefault();
  491.         $sql =  $this->createQueryBuilder('p')
  492.                      ->join('p.translations','t')
  493.                      ->join('p.type_post','typepost')
  494.                      ->andWhere('typepost.id = 11')
  495.                      ->andWhere('t.actif = true')
  496.                      ->andWhere('p.id_sous_activite = :id_sous_activite')
  497.                      ->andWhere('t.locale = :locale')
  498.                      ->setParameter('locale', $locale)
  499.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  500.                      ->setParameter('currentDate', new \DateTime())
  501.                      ->setParameter('id_sous_activite', $id_sous_activite)
  502.                      ->setMaxResults(3)
  503.                      ->orderBy('p.id','desc');
  504.                      
  505.         if ($id_diff != null) {
  506.             $sql->andWhere('p.id != :id_diff')
  507.                 ->setParameter('id_diff', $id_diff);
  508.         }
  509.                  
  510.         return $sql->getQuery()->getResult();
  511.     }
  514.     public function findPageRefByIdActiviteAndIdSousActivite($id_activite,$id_sous_activite,$id_diff = null)
  515.     {
  516.         $locale = Locale::getDefault();
  517.         $sql =  $this->createQueryBuilder('p')
  518.                      ->join('p.translations','t')
  519.                      ->join('p.type_post','typepost')
  520.                      ->andWhere('typepost.id = 11')
  521.                      ->andWhere('t.actif = true')
  522.                      ->andWhere('p.id_activite = :id_activite')
  523.                      ->andWhere('t.locale = :locale')
  524.                      ->setParameter('locale', $locale)
  525.                      ->setParameter('id_activite', $id_activite)
  526.                      ->andWhere('p.id_sous_activite = :id_sous_activite')
  527.                      ->setParameter('id_sous_activite', $id_sous_activite)
  528.                      ->setMaxResults(3)
  529.                      ->orderBy('p.id','desc');
  530.                   
  532.         if ($id_diff != null) {
  533.             $sql->andWhere('p.id != :id_diff')
  534.                 ->setParameter('id_diff', $id_diff);
  535.         }
  536.      
  537.         return $sql->getQuery()->getResult();
  538.     }
  540.     public function findPageClientByIdActiviteOrSousActivite($id_activite,$type = 'activite')
  541.     {
  542.         $locale = Locale::getDefault();
  543.         $sql =  $this->createQueryBuilder('p')
  544.                      ->join('p.translations','t')
  545.                      ->join('p.type_post','typepost')
  546.                      ->andWhere('typepost.id != 11')
  547.                      ->andWhere('t.actif = true')
  548.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  549.                      ->setParameter('currentDate', new \DateTime())
  550.                      ->andWhere('t.locale = :locale')
  551.                      ->setParameter('locale', $locale);
  552.              
  553.         if ($type == 'activite') {
  554.             $sql->andWhere('p.id_activite = :id_activite')
  555.                 ->setParameter('id_activite', $id_activite);
  556.         }else{
  557.             $sql->andWhere('p.id_sous_activite = :id_sous_activite')
  558.                 ->setParameter('id_sous_activite', $id_activite);
  559.         }
  561.         $sql->setMaxResults(3)->orderBy('p.id','desc');
  562.      
  563.         return $sql->getQuery()->getResult();
  564.     }
  566.     public function findPageClientByIdActiviteAndSousActivite($id_activite,$sous_activite)
  567.     {
  568.         $locale = Locale::getDefault();
  569.         $sql =  $this->createQueryBuilder('p')
  570.                      ->join('p.translations','t')
  571.                      ->join('p.type_post','typepost')
  572.                      ->andWhere('typepost.id != 11')
  573.                      ->andWhere('t.actif = true')
  574.                      ->andWhere('t.locale = :locale')
  575.                      ->setParameter('locale', $locale)
  576.                      ->andWhere('p.id_activite = :id_activite')
  577.                      ->setParameter('id_activite', $id_activite)
  578.                      ->andWhere('p.id_sous_activite = :id_sous_activite')
  579.                      ->setParameter('id_sous_activite', $sous_activite);
  581.         $sql->setMaxResults(3)->orderBy('p.id','desc');
  582.      
  583.         return $sql->getQuery()->getResult();
  584.     }
  587.     public function findPageRefByIdsSousActivite($ids_sous_activite = [])
  588.     {
  589.         $locale = Locale::getDefault();
  592.         $req1 =  $this->createQueryBuilder('p')
  593.                      ->join('p.translations','t')
  594.                      ->join('p.type_post','typepost')
  595.                      ->andWhere('typepost.id = 11')
  596.                      ->andWhere('t.actif = true')
  597.                      ->andWhere('p.actif_home = true')
  598.                      ->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  599.                      ->andWhere('p.type_page_ref = :type_page_ref')
  600.                      ->andWhere('t.locale = :locale')
  601.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  602.                      ->setParameter('currentDate', new \DateTime())
  603.                      ->setParameter('locale', $locale)
  604.                      ->setParameter('ids_sous_activite', $ids_sous_activite)
  605.                      ->setParameter('type_page_ref', 'activite_secondaire')
  606.                      ->setMaxResults(3)
  607.                      ->orderBy('p.id','desc')->getQuery()->getResult();
  609.         if (empty($req1)) {
  610.             $req1 =  $this->createQueryBuilder('p')
  611.                         ->join('p.translations','t')
  612.                         ->join('p.type_post','typepost')
  613.                         ->andWhere('typepost.id = 11')
  614.                         ->andWhere('t.actif = true')
  615.                        // ->andWhere('p.actif_home = true')
  616.                         ->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  617.                         ->andWhere('p.type_page_ref = :type_page_ref')
  618.                         ->andWhere('t.locale = :locale')
  619.                         ->setParameter('locale', $locale)
  620.                         ->setParameter('ids_sous_activite', $ids_sous_activite)
  621.                         ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  622.                         ->setParameter('currentDate', new \DateTime())
  623.                         ->setParameter('type_page_ref', 'activite_secondaire')
  624.                         ->setMaxResults(3)
  625.                         ->orderBy('RAND()')
  626.                         ->orderBy('p.id','desc')->getQuery()->getResult();
  627.         }
  629.                  
  630.         return $req1;
  631.     }
  634.     
  635.     public function findPageRefByIdsSousActiviteAndActivitePrincipal($id_activite,$id_secteur = null)
  636.     {
  637.         $locale = Locale::getDefault();
  638.         $sql =  $this->createQueryBuilder('p')
  639.                      ->join('p.translations','t')
  640.                      ->join('p.type_post','typepost')
  641.                      ->andWhere('typepost.id = 11')
  642.                      ->andWhere('t.actif = true')
  643.                      ->andWhere('p.id_sous_activite IS NOT NULL')
  644.                      ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  645.                      ->setParameter('currentDate', new \DateTime())
  646.                      //->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  647.                      // ->setParameter('ids_sous_activite', $ids_sous_activite)
  648.                      ->andWhere('p.type_page_ref = :type_page_ref')
  649.                      ->andWhere('p.id_activite = :id_activite')
  650.                      ->andWhere('t.locale = :locale')
  651.                      ->setParameter('locale', $locale)
  652.                      ->setParameter('id_activite', $id_activite)
  653.                      ->setParameter('type_page_ref', 'activite_secondaire');
  654.             
  656.         if ($id_secteur != null) {
  657.             $sql->andWhere('p.id_secteur = :id_secteur')
  658.                 ->setParameter('id_secteur', $id_secteur);
  659.         }
  661.         $sql->groupBy('p.id_sous_activite')
  662.             //->distinct('p.id_secteur');
  663.             ->orderBy('RAND()')
  664.             ->setMaxResults(3);
  665.             //->orderBy('p.id','desc');
  667.         return $sql->getQuery()->getResult();
  668.     }
  670.     public function findAllPostNotArchived($locale)
  671.     {
  672.        
  673.         $sql =  $this->createQueryBuilder('p')
  674.                      ->join('p.translations','t')
  675.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  676.                      ->andWhere('t.locale = :locale')
  677.                      ->setParameter('locale', $locale);
  678.      
  679.         $sql->orderBy('p.position','ASC');
  680.         return $sql->getQuery()->getResult();
  681.     }
  686.     /********************* Sql gestion des produits suivant categories ***********************/
  687.     public function findPostBySpecifiqueCategory($categorie,$page = 1,$categorie_ids = [])
  688.     {
  689.         
  690.        // $id_categorie = $categorie->getId();
  691.         $pagination = $categorie->getPagination() != null ? $categorie->getPagination() : false;
  692.         $page_size = $categorie->getPageSize() != null ? $categorie->getPageSize() : 10;
  694.         $order_by = 1;
  695.         $order_by = $categorie->getOrderBy();
  696.         $req_order = "p.position";
  697.         $asc_desc = "ASC";
  698.         if($order_by == 0){
  699.             $req_order = "p.date_publication";
  700.             $asc_desc = "DESC";
  701.         }
  703.         $locale = Locale::getDefault();
  704.         $sql =  $this->createQueryBuilder('p')
  705.                      ->join('p.translations','t')
  706.                      ->join('p.PostCategory','post_categorie')
  707.                      ->andWhere('t.actif = true')
  708.                      ->andWhere('post_categorie.Category IN (:categorie_ids)')
  709.                      ->andWhere('t.locale = :locale')
  710.                      ->setParameter('locale', $locale)
  711.                      ->setParameter('categorie_ids', $categorie_ids)
  712.                      ->orderBy($req_order, $asc_desc);
  715.         if ($pagination == true) {
  716.             return (new Paginator($sql))->paginate($page,$page_size);
  717.         }else{
  718.             return $sql->getQuery()->getResult();
  719.         }
  721.     }
  723.     /**************** get produit related *************/
  724.     public function findProductRelated($type,$diff_id,$id_default_categorie)
  725.     {
  726.         $locale = Locale::getDefault();
  727.         $sql =  $this->createQueryBuilder('p')
  728.                      ->join('p.translations','t')
  729.                      ->andWhere('t.actif = true')
  730.                      ->andWhere('p.id != :diff_id')
  731.                      ->andWhere('p.default_categorie = :id_default_categorie')
  732.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  733.                      ->join('p.type_post','typePost')
  734.                      ->andWhere('typePost.id = :type')
  735.                      ->andWhere('t.locale = :locale')
  736.                      ->setParameter('id_default_categorie', $id_default_categorie)
  737.                      ->setParameter('locale', $locale)
  738.                      ->setParameter('diff_id', $diff_id)
  739.                      ->setParameter('type', $type);
  740.      
  741.         return $sql->getQuery()->getResult();
  742.     }
  744.     /**************** get related Annonces *************/
  745.     public function findRelatedAnnonces($type,$diff_id)
  746.     {
  747.         $locale = Locale::getDefault();
  748.         $sql =  $this->createQueryBuilder('p')
  749.                         ->join('p.translations','t')
  750.                         ->andWhere('t.actif = true')
  751.                         ->andWhere('p.id != :diff_id')
  752.                         ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  753.                         ->join('p.type_post','typePost')
  754.                         ->andWhere('typePost.id = :type')
  755.                         ->andWhere('t.locale = :locale')
  756.                         ->setParameter('locale', $locale)
  757.                         ->setParameter('diff_id', $diff_id)
  758.                         ->setParameter('type', $type);
  759.         
  760.         return $sql->getQuery()->getResult();
  761.     }
  764.     /*****************************************************************************************/
  766.     /********************** Affichage Détails produit pour le panier *************************/
  768.     public function getCartProductInfos($id)
  769.     {
  770.         $locale = Locale::getDefault();
  771.         $sql =  $this->createQueryBuilder('p')
  772.                     ->select('p.id', 't.title', 'p.prix_produit', 'p.image')
  773.                      ->join('p.translations','t')
  774.                      ->andWhere('t.actif = true')
  775.                      ->andWhere('p.id = :id')
  776.                      ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  777.                      ->join('p.type_post','typePost')
  778.                      ->andWhere('t.locale = :locale')
  779.                      ->setParameter('locale', $locale)
  780.                      ->setParameter('id', $id)
  781.                      ->getQuery()
  782.                      ->getOneOrNullResult();
  784.         return $sql;
  785.     }
  788.     /*****************************************************************************************/
  791.     /* find slug page by alias */
  792.     public function findSlugPageByAlias($alias)
  793.     {
  794.         $locale = Locale::getDefault();
  795.         $sql =  $this->createQueryBuilder('p')
  796.                      ->join('p.translations','t')
  797.                      ->andWhere('t.locale = :locale')
  798.                      ->setParameter('locale', $locale)      
  799.                      ->andWhere('p.alias = :alias')
  800.                      ->setParameter('alias', $alias);
  801.       
  802.         return $sql->getQuery()->getOneOrNullResult();
  803.     }
  806.     public function findSystemNameByIdPost($id_post)
  807.     {
  808.         $locale = Locale::getDefault();
  810.         $sql =  $this->createQueryBuilder('p')
  811.                      ->select('typePost.id')
  812.                      ->join('p.type_post','typePost')
  813.                     // ->join('typePost.translations','translation')
  814.                      ->andWhere('p.id = :id_post')
  815.                      ->setParameter('id_post', $id_post)
  816.                      ->getQuery()
  817.                      ->getOneOrNullResult();
  818.      
  819.         return $sql;
  820.     }
  821. }