src/Repository/PostRepository.php line 759

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Post;
  6. use App\Pagination\Paginator;
  7. use App\Entity\PostTranslation;
  8. use Symfony\Component\Intl\Locale;
  9. use Doctrine\Persistence\ManagerRegistry;
  10. use App\Repository\PostRefRepositoryMethodsTrait;
  11. use App\Repository\PostActualitesRepositoryMethodsTrait;
  12. use App\Repository\MethodsTrait\NbPostRepositoryMethodsTrait;
  13. use App\Repository\MethodsTrait\PostArchiveRepositoryMethodsTrait;
  14. use App\Repository\MethodsTrait\SearchResultatRepositoryMethodsTrait;
  15. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  17. /**
  18. * @method Post|null find($id, $lockMode = null, $lockVersion = null)
  19. * @method Post|null findOneBy(array $criteria, array $orderBy = null)
  20. * @method Post[] findAll()
  21. * @method Post[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  22. */
  23. class PostRepository extends ServiceEntityRepository
  24. {
  25. use PostRefRepositoryMethodsTrait;
  26. use PostArchiveRepositoryMethodsTrait;
  27. use PostActualitesRepositoryMethodsTrait;
  28. use SearchResultatRepositoryMethodsTrait;
  29. use NbPostRepositoryMethodsTrait;
  33. private $languageRepository;
  35. public function __construct(ManagerRegistry $registry,LanguageRepository $languageRepository)
  36. {
  37. parent::__construct($registry, Post::class);
  38. $this->languageRepository = $languageRepository;
  39. }
  42. /** find conut nb resultat post **/
  43. public function findCountPost($type = null)
  44. {
  46. $locale = Locale::getDefault();
  47. $sql = $this->createQueryBuilder('p')
  48. ->select('count(p.id)');
  50. if($type != null){
  51. $sql->join('p.type_post','typePost')
  52. ->andWhere('typePost.id = :type')
  53. ->setParameter('type', $type);
  54. }
  56. return $sql->getQuery()->getSingleScalarResult();
  57. }
  58. /** end off method **/
  60. //Method used only in front
  61. public function findPostBySlug($slug)
  62. {
  64. $languages = $this->languageRepository->findActifLang();
  65. $locale = Locale::getDefault();
  66. // $sql = $this->createQueryBuilder('p')
  67. // ->join('p.translations','t')
  68. // ->andWhere('t.locale = :locale')
  69. // ->setParameter('locale', $locale)
  70. // ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  71. // ->setParameter('currentDate', new \DateTime());
  73. // if($slug){
  74. // $sql->andWhere('t.slug = :slug')
  75. // ->setParameter('slug', $slug);
  76. // }
  79. // return $sql->getQuery()->getOneOrNullResult();
  80. $resultat = null;
  81. if ($languages) {
  82. foreach ($languages as $language) {
  83. $qb = $this->createQueryBuilder('u')
  84. ->join('u.translations','t')
  85. ->andWhere('t.slug =:slug')
  86. ->andWhere('t.locale = :locale')
  87. ->setParameter('slug', $slug)
  88. ->setParameter('locale', $language->getLanguageCode());
  90. if ($qb->setMaxResults(1)->getQuery()->getOneOrNullResult()) {
  91. $resultat = $qb->setMaxResults(1)->getQuery()->getOneOrNullResult();
  92. return $resultat;
  93. }
  94. }
  95. }
  98. return $resultat;
  99. }
  102. public function findPostById($id)
  103. {
  104. $locale = Locale::getDefault();
  105. $sql = $this->createQueryBuilder('p')
  106. ->join('p.translations','t')
  107. ->andWhere('p.id = :id')
  108. ->andWhere('t.actif = true')
  109. ->andWhere('t.locale = :locale')
  110. ->setParameter('locale', $locale)
  111. ->setParameter('id', $id)
  112. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  113. ->setParameter('currentDate', new \DateTime());
  115. return $sql->getQuery()->getResult();
  116. }
  119. public function findAllPostByType($locale,$type,$categorie = null)
  120. {
  122. $sql = $this->createQueryBuilder('p')
  123. ->join('p.translations','t')
  124. ->andWhere('p.post_archive is null')
  125. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  126. ->andWhere('t.locale = :locale')
  127. ->setParameter('locale', $locale);
  129. if($type){
  130. $sql->join('p.type_post','tp')
  131. ->andWhere('tp.id = :type')
  132. ->setParameter('type', $type);
  134. // if($type == '6'){
  135. // $sql->orderBy('p.date_publication','DESC');
  136. // }
  137. }
  139. if ($categorie != null) {
  140. $sql->join('p.PostCategory','post_categorie')
  141. ->andWhere('post_categorie.Category IN (:categorie_ids)')
  142. ->setParameter('categorie_ids', [$categorie]);
  143. }
  145. $sql->orderBy('p.position','asc');
  146. return $sql->getQuery()->getResult();
  147. }
  151. public function findAllOtherPosts($id)
  152. {
  153. $locale = Locale::getDefault();
  154. $sql = $this->createQueryBuilder('p')
  155. ->join('p.translations','t')
  156. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  157. ->andWhere('t.locale = :locale')
  158. ->andWhere('t.actif = true')
  159. ->setParameter('locale', $locale);
  160. if($id){
  161. $sql->andWhere('p.id != :id')
  162. ->setParameter('id', $id);
  163. }
  166. $sql->orderBy('p.position','ASC');
  167. return $sql->getQuery()->getResult();
  168. }
  170. public function findAllActivesPostsByType($locale,$type)
  171. {
  173. $sql = $this->createQueryBuilder('p')
  174. ->join('p.translations','t')
  175. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  176. ->andWhere('t.locale = :locale')
  177. ->setParameter('locale', $locale);
  179. if($type){
  180. $sql->join('p.type_post','tp')
  181. ->andWhere('tp.id = :type')
  182. ->andWhere('t.actif = true')
  183. ->setParameter('type', $type);
  185. }
  187. $sql->orderBy('p.position','asc');
  189. return $sql->getQuery()->getResult();
  190. }
  192. public function findPostByType($type,$option,$nb)
  193. {
  194. $locale = Locale::getDefault();
  195. $sql = $this->createQueryBuilder('p')
  196. ->join('p.translations','t')
  197. ->join('p.type_post','typePost')
  198. ->andWhere('typePost.id = :type')
  199. ->andWhere('t.actif = true')
  200. ->andWhere('t.locale = :locale')
  201. ->setParameter('locale', $locale)
  202. ->setParameter('type', $type)
  203. ->setMaxResults($nb);
  205. if($option == '1'){
  206. $sql->orderBy('p.id', 'ASC');
  207. }elseif($option == '2'){
  208. $sql->orderBy('p.id', 'DESC');
  209. }
  211. return $sql->getQuery()->getResult();
  212. }
  214. public function findPostActifsHomeByType($type,$option,$nb = 1,$all_post = false)
  215. {
  216. $locale = Locale::getDefault();
  217. $sql = $this->createQueryBuilder('p')
  218. ->join('p.translations','t')
  219. ->join('p.type_post','typePost')
  220. ->andWhere('typePost.id = :type')
  221. ->andWhere('t.actif = true')
  222. ->andWhere('p.actif_home = 1')
  223. ->andWhere('t.locale = :locale')
  224. ->setParameter('locale', $locale)
  225. ->setParameter('type', $type)
  226. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  227. ->setParameter('currentDate', new \DateTime());
  230. if($all_post == false){
  231. $sql->setMaxResults($nb);
  232. }
  234. if($option == '1'){
  235. $sql->orderBy('p.id', 'ASC');
  236. }elseif($option == '2'){
  237. $sql->orderBy('p.id', 'DESC');
  238. }elseif($option == '3'){
  239. $sql->orderBy('p.date_publication','DESC');
  240. }elseif($option == '4'){
  241. $sql->orderBy('p.position', 'ASC');
  242. }elseif($option == '5'){
  243. $sql->orderBy('RAND()');
  244. }
  246. return $sql->getQuery()->getResult();
  247. }
  249. public function findPostByTypeInterne($type,$option,$nb)
  250. {
  251. $locale = Locale::getDefault();
  252. $sql = $this->createQueryBuilder('p')
  253. ->join('p.translations','t')
  254. ->join('p.type_post','typePost')
  255. ->andWhere('typePost.id = :type')
  256. ->andWhere('t.actif = true')
  257. ->andWhere('t.locale = :locale')
  258. ->setParameter('locale', $locale)
  259. ->setParameter('type', $type)
  260. ->setMaxResults($nb);
  262. if($option == '1'){
  263. $sql->orderBy('p.id', 'ASC');
  264. }elseif($option == '2'){
  265. $sql->orderBy('p.id', 'DESC');
  266. }elseif($option == '3'){
  267. $sql->orderBy('p.date_publication','DESC');
  268. }elseif($option == '4'){
  269. $sql->orderBy('p.position', 'ASC');
  270. }elseif($option == '5'){
  271. $sql->orderBy('RAND()');
  272. }
  274. return $sql->getQuery()->getResult();
  275. }
  278. public function findPostBySystemName($name)
  279. {
  280. $locale = Locale::getDefault();
  282. $sql = $this->createQueryBuilder('p')
  283. ->join('p.type_post','typePost')
  284. ->join('typePost.translations','translation')
  285. ->join('p.translations','t')
  286. ->andWhere('t.actif = true')
  287. ->andWhere('translation.system_name = :name')
  288. ->andWhere('translation.locale = :locale')
  289. ->setParameter('locale', $locale)
  290. ->setParameter('name', $name)
  291. ->getQuery()
  292. ->getResult();
  294. return $sql;
  295. }
  298. public function findPostBySystemNames($system_name)
  299. {
  300. $locale = Locale::getDefault();
  302. $sql = $this->createQueryBuilder('p')
  303. ->join('p.type_post','typePost')
  304. ->join('typePost.translations','translation')
  305. ->join('p.translations','t')
  306. ->andWhere('t.actif = true')
  307. ->andWhere("translation.system_name IN(:system_name)")
  308. ->andWhere('translation.locale = :locale')
  309. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  310. ->setParameter('currentDate', new \DateTime())
  311. ->setParameter('locale', $locale)
  312. ->setParameter('system_name', $system_name)
  313. ->getQuery()
  314. ->getResult();
  316. return $sql;
  317. }
  319. public function findPostMaxPosition()
  320. {
  321. $sql = $this->createQueryBuilder('p')
  322. ->select('MAX(p.position) as max_position');
  324. return $sql->getQuery()->getOneOrNullResult();
  325. }
  328. public function findPostByCategory($categorie,$page = 1)
  329. {
  331. $id_categorie = $categorie->getId();
  332. $pagination = $categorie->getPagination() != null ? $categorie->getPagination() : false;
  333. $page_size = $categorie->getPageSize() != null ? $categorie->getPageSize() : 10;
  335. $order_by = 1;
  336. $order_by = $categorie->getOrderBy();
  337. $req_order = "p.position";
  338. $asc_desc = "ASC";
  339. if($order_by == 0){
  340. $req_order = "p.date_publication";
  341. $asc_desc = "DESC";
  342. }
  344. $locale = Locale::getDefault();
  345. $sql = $this->createQueryBuilder('p')
  346. ->join('p.translations','t')
  347. ->join('p.PostCategory','post_categorie')
  348. ->andWhere('t.actif = true')
  349. ->andWhere('post_categorie.Category =:categorie_id')
  350. ->andWhere('t.locale = :locale')
  351. ->setParameter('locale', $locale)
  352. ->setParameter('categorie_id', $id_categorie)
  353. ->orderBy($req_order, $asc_desc);
  356. if ($pagination == true) {
  357. return (new Paginator($sql))->paginate($page,$page_size);
  358. }else{
  359. return $sql->getQuery()->getResult();
  360. }
  362. }
  364. /**
  365. * Retourne les posts par type et par id avec limit
  366. */
  368. public function getPostsWithLimit($locale,$type,$limit,$order,$IdSite)
  369. {
  371. $sql = $this->createQueryBuilder('p')
  372. ->join('p.translations','t')
  373. ->andWhere('t.locale = :locale')
  374. ->setParameter('locale', $locale);
  376. if($type){
  377. $sql->join('p.type_post','tp')
  378. ->andWhere('tp.id = :type')
  379. ->setParameter('type', $type);
  381. /* if($type == '6'){
  382. $sql->orderBy('p.date_publication','DESC');
  383. } */
  384. }
  386. switch ($order) {
  387. case 0:
  388. $sql->orderBy('RAND()');
  389. break;
  390. case 1:
  391. $sql->orderBy('p.id','ASC');
  392. break;
  393. case 2:
  394. $sql->orderBy('p.id','DESC');
  395. break;
  396. case 3:
  397. $sql->orderBy('p.date_publication','DESC');
  398. break;
  400. default:
  401. $sql->orderBy('p.id','ASC');
  402. break;
  403. }
  405. if($limit){
  406. $sql->setMaxResults($limit);
  407. }
  409. return $sql->getQuery()->getResult();
  410. }
  412. /**
  413. * Retourne la liste des catégories du post
  414. */
  416. public function getCategoryPost($post_category, $_locale){
  417. $categories = [];
  419. if($post_category){
  420. foreach ($post_category as $key => $post_cat) {
  421. if($post_cat->getCategory()){
  422. $cat = $post_cat->getCategory();
  423. $categories[] = ['id'=> $cat->getId(),'title'=> $cat->translate($_locale)->getTitreCategorie()];
  424. }
  425. }
  426. }
  428. return $categories;
  429. }
  432. // get article prev or next
  433. public function findPostPrevOrNext($categoiePost,$post,$action = 'next')
  434. {
  435. $id_categorie = $categoiePost->getId();
  436. $order_categorie = $categoiePost->getOrderBy();
  438. $id_post = $post->getId();
  439. $position_post = $post->getPosition();
  440. $date_publication = $post->getDatePublication();
  442. $locale = Locale::getDefault();
  443. $sql = $this->createQueryBuilder('p')
  444. ->join('p.translations','t')
  445. ->join('p.PostCategory','post_categorie')
  446. ->andWhere('t.actif = true')
  447. ->andWhere('p.id != :id_post')
  448. ->andWhere('post_categorie.Category = :id_categorie')
  449. ->andWhere('t.locale = :locale')
  450. ->setParameter('locale', $locale)
  451. ->setParameter('id_categorie', $id_categorie)
  452. ->setMaxResults(1);
  454. if(($order_categorie == 0 || $order_categorie == 2) and $date_publication){
  455. $sql->setParameter('date_publication', $date_publication);
  456. if($action == 'next') {
  457. $sql->andWhere('p.date_publication > :date_publication')
  458. ->orderBy('p.date_publication', 'asc');
  459. }else{
  460. $sql->andWhere('p.date_publication < :date_publication')
  461. ->orderBy('p.date_publication', 'DESC');
  462. }
  463. }else{
  464. $sql->setParameter('position_post', $position_post);
  465. if($action == 'next') {
  466. $sql->andWhere('p.position > :position_post')
  467. ->orderBy('p.position', 'asc');
  468. }else{
  469. $sql->andWhere('p.position < :position_post')
  470. ->orderBy('p.position', 'DESC');
  471. }
  472. }
  474. $sql->setParameter('id_post', $id_post);
  477. return $sql->getQuery()->getOneOrNullResult();
  479. }
  482. /*****************************************************************
  483. ** get liste actualite related with secteur activte ***
  484. *****************************************************************/
  485. public function findActualiteByActivite()
  486. {
  487. $locale = Locale::getDefault();
  489. $sql = $this->createQueryBuilder('p')
  490. ->andWhere('p.id_activite IS NOT NULL or p.id_sous_activite IS NOT NULL or p.id_secteur IS NOT NULL')
  491. ->join('p.translations','t')
  492. ->andWhere('t.actif = true')
  493. ->andWhere('t.locale = :locale')
  494. ->setParameter('locale', $locale)
  495. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  496. ->setParameter('currentDate', new \DateTime())
  497. ->join('p.type_post','typepost')
  498. ->andWhere('typepost.id != 11')
  499. ->getQuery()
  500. ->getResult();
  502. return $sql;
  503. }
  507. /*****************************************************************
  508. ** requete sql ref ***
  509. *****************************************************************/
  510. public function findPostByIdSecteur($id,$id_activite,$id_secteur)
  511. {
  512. $locale = Locale::getDefault();
  514. $sql = $this->createQueryBuilder('p')
  515. ->andWhere('p.id != :id')
  516. ->join('p.translations','t')
  517. ->andWhere('t.actif = true')
  518. ->andWhere('p.id_secteur = :id_secteur')
  519. ->andWhere('p.id_activite = :id_activite')
  520. ->andWhere('t.locale = :locale')
  521. ->setParameter('locale', $locale)
  522. ->setParameter('id_secteur', $id_secteur)
  523. ->setParameter('id_activite', $id_activite)
  524. ->setParameter('id', $id)
  525. ->getQuery()
  526. ->getResult();
  528. return $sql;
  529. }
  532. public function findPageRefByIdActivite($id_activite,$id_diff = null)
  533. {
  534. $locale = Locale::getDefault();
  535. $sql = $this->createQueryBuilder('p')
  536. ->join('p.translations','t')
  537. ->join('p.type_post','typepost')
  538. ->andWhere('typepost.id = 11')
  539. ->andWhere('t.actif = true')
  540. ->andWhere('p.id_activite = :id_activite')
  541. ->andWhere('t.locale = :locale')
  542. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  543. ->setParameter('currentDate', new \DateTime())
  544. ->setParameter('locale', $locale)
  545. ->setParameter('id_activite', $id_activite)
  546. ->andWhere('p.id_sous_activite is NULL')
  547. ->setMaxResults(3)
  548. ->orderBy('p.id','desc');
  551. if ($id_diff != null) {
  552. $sql->andWhere('p.id != :id_diff')
  553. ->setParameter('id_diff', $id_diff);
  554. }
  556. return $sql->getQuery()->getResult();
  557. }
  561. public function findPageRefByIdSousActivite($id_sous_activite,$id_diff = null)
  562. {
  563. $locale = Locale::getDefault();
  564. $sql = $this->createQueryBuilder('p')
  565. ->join('p.translations','t')
  566. ->join('p.type_post','typepost')
  567. ->andWhere('typepost.id = 11')
  568. ->andWhere('t.actif = true')
  569. ->andWhere('p.id_sous_activite = :id_sous_activite')
  570. ->andWhere('t.locale = :locale')
  571. ->setParameter('locale', $locale)
  572. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  573. ->setParameter('currentDate', new \DateTime())
  574. ->setParameter('id_sous_activite', $id_sous_activite)
  575. ->setMaxResults(3)
  576. ->orderBy('p.id','desc');
  578. if ($id_diff != null) {
  579. $sql->andWhere('p.id != :id_diff')
  580. ->setParameter('id_diff', $id_diff);
  581. }
  583. return $sql->getQuery()->getResult();
  584. }
  587. public function findPageRefByIdActiviteAndIdSousActivite($id_activite,$id_sous_activite,$id_diff = null)
  588. {
  589. $locale = Locale::getDefault();
  590. $sql = $this->createQueryBuilder('p')
  591. ->join('p.translations','t')
  592. ->join('p.type_post','typepost')
  593. ->andWhere('typepost.id = 11')
  594. ->andWhere('t.actif = true')
  595. ->andWhere('p.id_activite = :id_activite')
  596. ->andWhere('t.locale = :locale')
  597. ->setParameter('locale', $locale)
  598. ->setParameter('id_activite', $id_activite)
  599. ->andWhere('p.id_sous_activite = :id_sous_activite')
  600. ->setParameter('id_sous_activite', $id_sous_activite)
  601. ->setMaxResults(3)
  602. ->orderBy('p.id','desc');
  605. if ($id_diff != null) {
  606. $sql->andWhere('p.id != :id_diff')
  607. ->setParameter('id_diff', $id_diff);
  608. }
  610. return $sql->getQuery()->getResult();
  611. }
  613. public function findPageClientByIdActiviteOrSousActivite($id_activite,$type = 'activite')
  614. {
  615. $locale = Locale::getDefault();
  616. $sql = $this->createQueryBuilder('p')
  617. ->join('p.translations','t')
  618. ->join('p.type_post','typepost')
  619. ->andWhere('typepost.id != 11')
  620. ->andWhere('t.actif = true')
  621. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  622. ->setParameter('currentDate', new \DateTime())
  623. ->andWhere('t.locale = :locale')
  624. ->setParameter('locale', $locale);
  626. if ($type == 'activite') {
  627. $sql->andWhere('p.id_activite = :id_activite')
  628. ->setParameter('id_activite', $id_activite);
  629. }else{
  630. $sql->andWhere('p.id_sous_activite = :id_sous_activite')
  631. ->setParameter('id_sous_activite', $id_activite);
  632. }
  634. $sql->setMaxResults(3)->orderBy('p.id','desc');
  636. return $sql->getQuery()->getResult();
  637. }
  639. public function findPageClientByIdActiviteAndSousActivite($id_activite,$sous_activite)
  640. {
  641. $locale = Locale::getDefault();
  642. $sql = $this->createQueryBuilder('p')
  643. ->join('p.translations','t')
  644. ->join('p.type_post','typepost')
  645. ->andWhere('typepost.id != 11')
  646. ->andWhere('t.actif = true')
  647. ->andWhere('t.locale = :locale')
  648. ->setParameter('locale', $locale)
  649. ->andWhere('p.id_activite = :id_activite')
  650. ->setParameter('id_activite', $id_activite)
  651. ->andWhere('p.id_sous_activite = :id_sous_activite')
  652. ->setParameter('id_sous_activite', $sous_activite);
  654. $sql->setMaxResults(3)->orderBy('p.id','desc');
  656. return $sql->getQuery()->getResult();
  657. }
  660. public function findPageRefByIdsSousActivite($ids_sous_activite = [])
  661. {
  662. $locale = Locale::getDefault();
  664. $req1 = $this->createQueryBuilder('p')
  665. ->join('p.translations','t')
  666. ->join('p.type_post','typepost')
  667. ->andWhere('typepost.id = 11')
  668. ->andWhere('t.actif = true')
  669. ->andWhere('p.actif_home = true')
  670. ->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  671. ->andWhere('p.type_page_ref = :type_page_ref')
  672. ->andWhere('t.locale = :locale')
  673. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  674. ->setParameter('currentDate', new \DateTime())
  675. ->setParameter('locale', $locale)
  676. ->setParameter('ids_sous_activite', $ids_sous_activite)
  677. ->setParameter('type_page_ref', 'activite_secondaire')
  678. ->setMaxResults(3)
  679. ->orderBy('p.id','desc')->getQuery()->getResult();
  681. if (empty($req1)) {
  682. $req1 = $this->createQueryBuilder('p')
  683. ->join('p.translations','t')
  684. ->join('p.type_post','typepost')
  685. ->andWhere('typepost.id = 11')
  686. ->andWhere('t.actif = true')
  687. // ->andWhere('p.actif_home = true')
  688. ->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  689. ->andWhere('p.type_page_ref = :type_page_ref')
  690. ->andWhere('t.locale = :locale')
  691. ->setParameter('locale', $locale)
  692. ->setParameter('ids_sous_activite', $ids_sous_activite)
  693. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  694. ->setParameter('currentDate', new \DateTime())
  695. ->setParameter('type_page_ref', 'activite_secondaire')
  696. ->setMaxResults(3)
  697. ->orderBy('RAND()')
  698. ->orderBy('p.id','desc')->getQuery()->getResult();
  699. }
  702. return $req1;
  703. }
  707. public function findPageRefByIdsSousActiviteAndActivitePrincipal($id_activite,$id_secteur = null)
  708. {
  709. $locale = Locale::getDefault();
  710. $sql = $this->createQueryBuilder('p')
  711. ->join('p.translations','t')
  712. ->join('p.type_post','typepost')
  713. ->andWhere('typepost.id = 11')
  714. ->andWhere('t.actif = true')
  715. ->andWhere('p.id_sous_activite IS NOT NULL')
  716. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  717. ->setParameter('currentDate', new \DateTime())
  718. //->andWhere('p.id_sous_activite IN (:ids_sous_activite)')
  719. // ->setParameter('ids_sous_activite', $ids_sous_activite)
  720. ->andWhere('p.type_page_ref = :type_page_ref')
  721. ->andWhere('p.id_activite = :id_activite')
  722. ->andWhere('t.locale = :locale')
  723. ->setParameter('locale', $locale)
  724. ->setParameter('id_activite', $id_activite)
  725. ->setParameter('type_page_ref', 'activite_secondaire');
  728. if ($id_secteur != null) {
  729. $sql->andWhere('p.id_secteur = :id_secteur')
  730. ->setParameter('id_secteur', $id_secteur);
  731. }
  733. $sql->groupBy('p.id_sous_activite')
  734. //->distinct('p.id_secteur');
  735. ->orderBy('RAND()')
  736. ->setMaxResults(3);
  737. //->orderBy('p.id','desc');
  739. return $sql->getQuery()->getResult();
  740. }
  742. public function findAllPostNotArchived($locale)
  743. {
  745. $sql = $this->createQueryBuilder('p')
  746. ->join('p.translations','t')
  747. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  748. ->andWhere('t.locale = :locale')
  749. ->setParameter('locale', $locale);
  751. $sql->orderBy('p.position','ASC');
  752. return $sql->getQuery()->getResult();
  753. }
  758. /********************* Sql gestion des produits suivant categories ***********************/
  759. public function findPostBySpecifiqueCategory($categorie,$page = 1,$categorie_ids = [])
  760. {
  762. // $id_categorie = $categorie->getId();
  763. $pagination = $categorie->getPagination() != null ? $categorie->getPagination() : false;
  764. $page_size = $categorie->getPageSize() != null ? $categorie->getPageSize() : 10;
  766. $order_by = $categorie->getOrderBy();
  768. switch ($order_by) {
  769. case 0:
  770. $req_order = "p.date_publication";
  771. $asc_desc = "ASC";
  772. break;
  773. case 1:
  774. $req_order = "p.id";
  775. $asc_desc = "ASC";
  776. break;
  777. case 2:
  778. $req_order = "p.date_publication";
  779. $asc_desc = "DESC";
  780. break;
  781. case 3:
  782. $req_order = "p.id";
  783. $asc_desc = "DESC";
  784. break;
  785. case 4:
  786. $req_order = "p.position";
  787. $asc_desc = "ASC";
  788. break;
  789. default:
  790. $req_order = "p.position";
  791. $asc_desc = "ASC";
  792. }
  794. $locale = Locale::getDefault();
  795. $sql = $this->createQueryBuilder('p')
  796. ->join('p.translations','t')
  797. ->join('p.PostCategory','post_categorie')
  798. ->andWhere('t.actif = true')
  799. ->andWhere('p.archive IS NULL OR p.archive = false')
  800. ->andWhere('post_categorie.Category = :categorie or post_categorie.Category IN (:categorie_ids)')
  801. ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  802. ->setParameter('currentDate', new \DateTime())
  803. ->andWhere('t.locale = :locale')
  804. ->setParameter('locale', $locale)
  805. ->setParameter('categorie_ids', $categorie_ids)
  806. ->setParameter('categorie', $categorie)
  807. ->addOrderBy('CASE WHEN p.generate_with_ai = 1 THEN 1 ELSE 0 END,'. $req_order, $asc_desc)
  808. ->orderBy($req_order, $asc_desc);
  811. if ($pagination == true) {
  812. return (new Paginator($sql))->paginate($page,$page_size);
  813. }else{
  814. return $sql->getQuery()->getResult();
  815. }
  817. }
  819. /**************** get produit related *************/
  820. public function findProductRelated($type,$diff_id,$id_default_categorie)
  821. {
  822. $locale = Locale::getDefault();
  823. $sql = $this->createQueryBuilder('p')
  824. ->join('p.translations','t')
  825. ->andWhere('t.actif = true')
  826. ->andWhere('p.id != :diff_id')
  827. ->andWhere('p.default_categorie = :id_default_categorie')
  828. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  829. ->join('p.type_post','typePost')
  830. ->andWhere('typePost.id = :type')
  831. ->andWhere('t.locale = :locale')
  832. ->setParameter('id_default_categorie', $id_default_categorie)
  833. ->setParameter('locale', $locale)
  834. ->setParameter('diff_id', $diff_id)
  835. ->setParameter('type', $type);
  837. return $sql->getQuery()->getResult();
  838. }
  840. /**************** get related Annonces *************/
  841. public function findRelatedAnnonces($type,$diff_id)
  842. {
  843. $locale = Locale::getDefault();
  844. $sql = $this->createQueryBuilder('p')
  845. ->join('p.translations','t')
  846. ->andWhere('t.actif = true')
  847. ->andWhere('p.id != :diff_id')
  848. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  849. ->join('p.type_post','typePost')
  850. ->andWhere('typePost.id = :type')
  851. ->andWhere('t.locale = :locale')
  852. ->setParameter('locale', $locale)
  853. ->setParameter('diff_id', $diff_id)
  854. ->setParameter('type', $type);
  856. return $sql->getQuery()->getResult();
  857. }
  860. /*****************************************************************************************/
  862. /********************** Affichage Détails produit pour le panier *************************/
  864. public function getCartProductInfos($id)
  865. {
  866. $locale = Locale::getDefault();
  867. $sql = $this->createQueryBuilder('p')
  868. ->select('p.id', 't.title', 'p.prix_produit', 'p.image')
  869. ->join('p.translations','t')
  870. ->andWhere('t.actif = true')
  871. ->andWhere('p.id = :id')
  872. ->andWhere('p.post_archive = 0 OR p.post_archive is null')
  873. ->join('p.type_post','typePost')
  874. ->andWhere('t.locale = :locale')
  875. ->setParameter('locale', $locale)
  876. ->setParameter('id', $id)
  877. ->getQuery()
  878. ->getOneOrNullResult();
  880. return $sql;
  881. }
  884. /*****************************************************************************************/
  887. /* find slug page by alias */
  888. public function findSlugPageByAlias($alias)
  889. {
  890. $locale = Locale::getDefault();
  891. $sql = $this->createQueryBuilder('p')
  892. ->join('p.translations','t')
  893. ->andWhere('t.locale = :locale')
  894. ->setParameter('locale', $locale)
  895. ->andWhere('p.alias = :alias')
  896. ->setParameter('alias', $alias);
  898. return $sql->getQuery()->getOneOrNullResult();
  899. }
  902. public function findSlugPageById($id_post)
  903. {
  904. $locale = Locale::getDefault();
  905. $sql = $this->createQueryBuilder('p')
  906. ->select('t.slug')
  907. ->join('p.translations','t')
  908. ->andWhere('t.locale = :locale')
  909. ->setParameter('locale', $locale)
  910. ->andWhere('p.id = :id_post')
  911. ->setParameter('id_post', $id_post);
  913. return $sql->getQuery()->getOneOrNullResult();
  914. }
  917. public function findSystemNameByIdPost($id_post)
  918. {
  919. $locale = Locale::getDefault();
  921. $sql = $this->createQueryBuilder('p')
  922. ->select('typePost.id')
  923. ->join('p.type_post','typePost')
  924. // ->join('typePost.translations','translation')
  925. ->andWhere('p.id = :id_post')
  926. ->setParameter('id_post', $id_post)
  927. ->getQuery()
  928. ->getOneOrNullResult();
  930. return $sql;
  931. }
  934. public function findPageParentByCategoryId($categoryId)
  935. {
  936. $locale = Locale::getDefault();
  938. $sql = $this->createQueryBuilder('p')
  939. ->where('p.ids_categoies_related LIKE :categoryId')
  940. ->setParameter('categoryId', '%'.$categoryId.'%')
  941. ->getQuery()
  942. ->getOneOrNullResult();
  944. return $sql;
  945. }
  948. public function findPostNotValidImage()
  949. {
  950. $locale = Locale::getDefault();
  951. $sql = $this->createQueryBuilder('p')
  952. ->andWhere('t.actif = true')
  953. ->join('p.translations','t')
  954. ->andWhere('p.image IS NOT NULL')
  955. ->andWhere('p.image_import_valide is null');
  956. //->setMaxResults('10');
  958. return $sql->getQuery()->getResult();
  959. }
  961. public function findPostArchive()
  962. {
  963. $locale = Locale::getDefault();
  964. $sql = $this->createQueryBuilder('p')
  965. ->andWhere('t.actif = true')
  966. ->andWhere('p.archive = true')
  967. ->join('p.translations','t')
  968. ->orderBy('p.id','ASC');
  970. return $sql->getQuery()->getResult();
  971. }
  973. /******************* post aleatoire ***************************/
  974. public function findPostAleatoire($type,$nb = 1,$id)
  975. {
  976. $locale = Locale::getDefault();
  977. $sql = $this->createQueryBuilder('p')
  978. ->join('p.translations','t')
  979. ->join('p.type_post','typePost')
  980. ->andWhere('typePost.id = :type')
  981. ->andWhere('t.actif = true')
  982. ->andWhere('t.locale = :locale')
  983. ->setParameter('locale', $locale)
  984. ->setParameter('type', $type)
  985. ->andWhere('p.id != :id')
  986. ->setParameter('id', $id)
  987. // ->andWhere('p.date_publication IS NULL OR p.date_publication <= :currentDate')
  988. // ->setParameter('currentDate', new \DateTime('now'))
  989. ->orderBy('RAND()')
  990. ->setMaxResults($nb);
  992. return $sql->getQuery()->getResult();
  993. }
  995. public function findPostAssocier($type,$id = null)
  996. {
  998. $locale = Locale::getDefault();
  1000. $sql = $this->createQueryBuilder('p')
  1002. ->join('p.translations','t')
  1004. ->join('p.type_post','typePost')
  1006. ->andWhere('typePost.id = :type')
  1008. ->andWhere('t.actif = true')
  1010. ->andWhere('t.locale = :locale')
  1012. ->setParameter('locale', $locale)
  1014. ->setParameter('type', $type);
  1016. if($id != null){
  1017. $sql->andWhere('p.id != :id_post')
  1018. ->setParameter('id_post', $id);
  1019. }
  1021. return $sql->getQuery()->getResult();
  1023. }
  1026. public function findPostExpressionBySpecifiqueCategory($id_categorie)
  1027. {
  1029. $req_order = "p.position";
  1031. $asc_desc = "ASC";
  1033. $locale = Locale::getDefault();
  1035. $sql = $this->createQueryBuilder('p')
  1037. ->join('p.translations','t')
  1039. ->join('p.PostCategory','post_categorie')
  1041. ->andWhere('t.actif = true')
  1043. ->andWhere('post_categorie.Category =:categorie_id')
  1045. ->andWhere('t.locale = :locale')
  1047. ->setParameter('locale', $locale)
  1049. ->setParameter('categorie_id', $id_categorie)
  1051. ->orderBy($req_order, $asc_desc);
  1054. return $sql->getQuery()->getResult();
  1055. }
  1056. }