vendor/uvdesk/support-center-bundle/Repository/SolutionCategory.php line 55

Open in your IDE?
  1. <?php
  3. namespace Webkul\UVDesk\SupportCenterBundle\Repository;
  5. use Doctrine\Common\Collections\Criteria;
  6. use Doctrine\ORM\EntityRepository;
  7. use Symfony\Component\HttpFoundation\Request;
  8. use Doctrine\ORM\Query;
  9. use Webkul\UVDesk\SupportCenterBundle\Entity as SupportEntites;
  11. /**
  12.  * Website
  13.  *
  14.  * This class was generated by the Doctrine ORM. Add your own custom
  15.  * repository methods below.
  16.  */
  17. class SolutionCategory extends EntityRepository
  18. {
  19.     const LIMIT = 1000;
  21.     private $defaultSort = 'a.id';
  22.     private $direction = ['asc', 'desc'];
  23.     private $sorting = ['a.name', 'a.dateAdded', 'a.sortOrder'];
  24.     private $safeFields = ['page', 'limit', 'sort', 'order', 'direction'];
  25.     private $allowedFormFields = ['search', 'name', 'description', 'sorting', 'sortOrder', 'status'];
  26.     private $defaultImage = 'https://s3-ap-southeast-1.amazonaws.com/opencart-hd/website/1/2017/01/02/586a365e5e472.default-icon.png';
  28.     private function validateSorting($sorting)
  29.     {
  30.         return in_array($sorting, $this->sorting) ? $sorting : $this->defaultSort;
  31.     }
  33.     private function validateDirection($direction)
  34.     {
  35.         return in_array($direction, $this->direction) ? $direction : Criteria::DESC;
  36.     }
  38.     private function presetting(&$data)
  39.     {
  40.         $data['sort'] = $_GET['sort'] = $this->validateSorting(isset($data['sort']) ? $data['sort'] : false);
  41.         $data['direction'] = $_GET['direction'] = $this->validateDirection(isset($data['direction']) ? $data['direction'] : false);
  43.         $this->cleanAllData($data);
  44.     }
  46.     private function cleanAllData(&$data)
  47.     {
  48.         if(isset($data['isActive'])){
  49.             $data['status'] = $data['isActive'];
  50.             unset($data['isActive']);
  51.             unset($data['solutionId']);
  52.         }
  53.     }
  55.     public function getAllCategories(\Symfony\Component\HttpFoundation\ParameterBag $obj = null, $container, $allResult = false)
  56.     {
  57.         $json = array();
  58.         $qb = $this->getEntityManager()->createQueryBuilder();
  59.         $qb->select('a')->from($this->getEntityName(), 'a');
  61.         $data = $obj ? $obj->all() : [];
  62.         $data = array_reverse($data);
  64.         $categories = [];
  66.         if(isset($data['solutionId'])){
  67.             $qbS = $this->getEntityManager()->createQueryBuilder();
  68.             $qbS->select('a.categoryId')->from('Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategoryMapping', 'a');
  69.             $qbS->where('a.solutionId = :solutionId');
  70.             $qbS->setParameter('solutionId', $data['solutionId']);
  72.             $categories = $qbS->getQuery()->getResult();
  73.             $categories = $categories ? $categories : [0];
  74.         }
  76.         $this->presetting($data);
  78.         foreach ($data as $key => $value) {
  79.             if(!in_array($key,$this->safeFields) && in_array($key, $this->allowedFormFields)) {
  80.                 if($key!='dateUpdated' AND $key!='dateAdded' AND $key!='search') {
  81.                         $qb->Andwhere('a.'.$key.' = :'.$key);
  82.                         $qb->setParameter($key, $value);
  83.                 } else {
  84.                     if($key == 'search') {
  85.                         $qb->orwhere('a.name'.' LIKE :name');
  86.                         $qb->setParameter('name', '%'.urldecode(trim($value)).'%');
  87.                         $qb->orwhere('a.description'.' LIKE :description');
  88.                         $qb->setParameter('description', '%'.urldecode(trim($value)).'%');
  89.                     }
  90.                 }
  91.             }
  92.         }
  94.         // $qb->Andwhere('a.companyId'.' = :company');
  95.         // $qb->setParameter('company', $container->get('user.service')->getCurrentCompany()->getId());
  97.         if($categories){
  98.             $qb->Andwhere('a.id IN (:categories)');
  99.             $qb->setParameter('categories', $categories);
  100.         }
  102.         if(!$allResult){
  103.             $paginator  = $container->get('knp_paginator');
  104.             $results = $paginator->paginate(
  105.                 $qb,
  106.                 isset($data['page']) ? $data['page'] : 1,
  107.                 isset($data['limit']) ? $data['limit'] : self::LIMIT,
  108.                 array('distinct' => false)
  109.             );
  110.         }else{
  111.             $qb->select($allResult);
  112.             $results = $qb->getQuery()->getResult();
  113.             return $results;
  114.         }
  116.         $newResult = [];
  117.         foreach ($results as $key => $result) {
  118.             $newResult[] = array(
  119.                 'id'                   => $result->getId(),
  120.                 'name'                 => $result->getName(),
  121.                 'description'          => $result->getDescription(),
  122.                 'status'               => $result->getStatus(),
  124.                 'sorting'              => $result->getSorting(),
  125.                 'sortOrder'            => $result->getSortOrder(),
  127.                 'dateAdded'            => date_format($result->getDateAdded(),"d-M h:i A"),
  129.                 'articleCount'         => $this->getArticlesCountByCategory($result->getId()),
  131.                 'solutions'            => ($categories ? [] : $this->getSolutionsByCategory($result->getId())),
  132.             );
  133.         }
  135.         $paginationData = $results->getPaginationData();
  136.         $queryParameters = $results->getParams();
  138.         unset($queryParameters['solution']);
  140.         $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  142.         $json['results'] = $newResult;
  143.         $json['pagination_data'] = $paginationData;
  144.         return $json;
  145.     }
  147.     public function findCategoryById($filterArray = [])
  148.     {
  149.         $json = array();
  150.         $qb = $this->getEntityManager()->createQueryBuilder();
  151.         $qb->select('a')->from($this->getEntityName(), 'a');
  153.         foreach ($filterArray as $key => $value) {
  154.             $qb->Andwhere('a.'.$key.' = :'.$key);
  155.             $qb->setParameter($key, $value);
  156.         }
  158.         return $qb->getQuery()->getOneOrNullResult();
  159.        
  160.     }
  162.     public function getArticlesCountByCategory($categoryId, $status = 1)
  163.     {
  164.         $qbS = $this->createQueryBuilder('a');
  166.         $result = $qbS->select('COUNT(DISTINCT ac.id)')
  167.             ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\ArticleCategory','ac','WITH', 'ac.categoryId = a.id')
  168.             ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Article','aA','WITH', 'ac.articleId = aA.id')
  169.             ->andwhere('ac.categoryId = :categoryId')
  170.             ->andwhere('aA.status IN (:status)')
  171.             ->setParameters([
  172.                 'categoryId' => $categoryId ,
  173.                 'status' => $status ,
  174.             ])
  175.             ->getQuery()
  176.             ->getSingleScalarResult();
  177.  
  178.         return $result;
  179.     }
  181.     public function getSolutionsByCategory($categoryId)
  182.     {
  183.         $queryBuilder = $this->createQueryBuilder('a');
  185.         $results = $queryBuilder->select('s.id, s.name')
  186.                  ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategoryMapping','ac','WITH', 'ac.categoryId = a.id')
  187.                  ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Solutions','s','WITH', 'ac.solutionId = s.id')
  188.                  ->andwhere('ac.categoryId = :categoryId')
  189.                  ->setParameters([
  190.                      'categoryId' => $categoryId
  191.                  ])
  192.                  ->getQuery()
  193.                  ->getResult()
  194.         ;
  196.         return $results;
  197.     }
  199.     public function getArticlesByCategory($categoryId)
  200.     {
  201.         $queryBuilder = $this->createQueryBuilder('sc');
  203.         $results = $queryBuilder->select('a.id, a.name, a.slug')
  204.                  ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\ArticleCategery','ac','WITH', 'ac.categoryId = sc.id')
  205.                  ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Article','a','WITH', 'ac.id = a.id')
  206.                  ->andwhere('ac.categoryId = :categoryId')
  207.                  ->setParameters([
  208.                      'categoryId' => $categoryId
  209.                  ])
  210.                  ->getQuery()
  211.                  ->getResult()
  212.         ;
  214.         return $results;
  215.     }
  217.     public function removeSolutionsByCategory($categoryId, $solutionId)
  218.     {
  219.         $queryBuilder = $this->createQueryBuilder('ac');
  220.         $queryBuilder->delete(SupportEntites\SolutionCategoryMapping::class,'ac')
  221.                  ->andwhere('ac.categoryId = :categoryId')
  222.                  ->andwhere('ac.solutionId IN (:solutionId)')
  223.                  ->setParameters([
  224.                      'categoryId' => $categoryId ,
  225.                      'solutionId' => $solutionId ,
  226.                  ])
  227.                  ->getQuery()
  228.                  ->execute()
  229.         ;
  230.     }
  232.     public function removeEntryByCategory($categoryId)
  233.     {
  234.         $where = is_array($categoryId) ? 'ac.categoryId IN (:categoryId)' : 'ac.categoryId = :categoryId';
  236.         $queryBuilder = $this->createQueryBuilder('ac');
  237.         $queryBuilder->delete(SupportEntites\SolutionCategoryMapping::class,'ac')
  238.                  ->andwhere($where)
  239.                  ->setParameters([
  240.                      'categoryId' => $categoryId ,
  241.                  ])
  242.                  ->getQuery()
  243.                  ->execute()
  244.         ;
  246.         $queryBuilder->delete(SupportEntites\ArticleCategory::class,'ac')
  247.                  ->andwhere($where)
  248.                  ->setParameters([
  249.                      'categoryId' => $categoryId ,
  250.                  ])
  251.                  ->getQuery()
  252.                  ->execute()
  253.         ;
  254.     }
  256.     public function bulkCategoryStatusUpdate($categoryIds, $status)
  257.     {
  258.         $query = 'UPDATE Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategory sc SET sc.status = '. (int)$status .' WHERE sc.id IN ('.implode(',', $categoryIds).')';
  260.         $this->getEntityManager()->createQuery($query)->execute();
  261.     }
  263.     public function categorySortingUpdate($id, $sort)
  264.     {
  265.         $query = "UPDATE Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategory sc SET sc.sortOrder = '". (int)$sort ."' WHERE sc.id = '". (int)$id ."'";
  267.         $this->getEntityManager()->createQuery($query)->execute();
  268.     }
  269. }