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 SupportEntities;
  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();
  114. return $results;
  115. }
  117. $newResult = [];
  118. foreach ($results as $key => $result) {
  119. $newResult[] = array(
  120. 'id' => $result->getId(),
  121. 'name' => $result->getName(),
  122. 'description' => $result->getDescription(),
  123. 'status' => $result->getStatus(),
  124. 'sorting' => $result->getSorting(),
  125. 'sortOrder' => $result->getSortOrder(),
  126. 'dateAdded' => date_format($result->getDateAdded(),"d-M h:i A"),
  127. 'articleCount' => $this->getArticlesCountByCategory($result->getId()),
  128. 'solutions' => ($categories ? [] : $this->getSolutionsByCategory($result->getId())),
  129. );
  130. }
  132. $paginationData = $results->getPaginationData();
  133. $queryParameters = $results->getParams();
  135. unset($queryParameters['solution']);
  137. $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  139. $json['results'] = $newResult;
  140. $json['pagination_data'] = $paginationData;
  142. return $json;
  143. }
  145. public function findCategoryById($filterArray = [])
  146. {
  147. $json = array();
  148. $qb = $this->getEntityManager()->createQueryBuilder();
  149. $qb->select('a')->from($this->getEntityName(), 'a');
  151. foreach ($filterArray as $key => $value) {
  152. $qb->andWhere('a.'.$key.' = :'.$key);
  153. $qb->setParameter($key, $value);
  154. }
  156. return $qb->getQuery()->getOneOrNullResult();
  158. }
  160. public function getArticlesCountByCategory($categoryId, $status = 1)
  161. {
  162. $qbS = $this->createQueryBuilder('a');
  164. $result = $qbS->select('COUNT(DISTINCT ac.id)')
  165. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\ArticleCategory','ac','WITH', 'ac.categoryId = a.id')
  166. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Article','aA','WITH', 'ac.articleId = aA.id')
  167. ->andWhere('ac.categoryId = :categoryId')
  168. ->andWhere('aA.status IN (:status)')
  169. ->setParameters([
  170. 'categoryId' => $categoryId,
  171. 'status' => $status,
  172. ])
  173. ->getQuery()
  174. ->getSingleScalarResult();
  176. return $result;
  177. }
  179. public function getSolutionsByCategory($categoryId)
  180. {
  181. $queryBuilder = $this->createQueryBuilder('a');
  183. $results = $queryBuilder->select('s.id, s.name')
  184. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategoryMapping','ac','WITH', 'ac.categoryId = a.id')
  185. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Solutions','s','WITH', 'ac.solutionId = s.id')
  186. ->andWhere('ac.categoryId = :categoryId')
  187. ->setParameters([
  188. 'categoryId' => $categoryId
  189. ])
  190. ->getQuery()
  191. ->getResult()
  192. ;
  194. return $results;
  195. }
  197. public function getArticlesByCategory($categoryId)
  198. {
  199. $queryBuilder = $this->createQueryBuilder('sc');
  201. $results = $queryBuilder->select('a.id, a.name, a.slug')
  202. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\ArticleCategery','ac','WITH', 'ac.categoryId = sc.id')
  203. ->leftJoin('Webkul\UVDesk\SupportCenterBundle\Entity\Article','a','WITH', 'ac.id = a.id')
  204. ->andWhere('ac.categoryId = :categoryId')
  205. ->setParameters([
  206. 'categoryId' => $categoryId
  207. ])
  208. ->getQuery()
  209. ->getResult()
  210. ;
  212. return $results;
  213. }
  215. public function removeSolutionsByCategory($categoryId, $solutionId)
  216. {
  217. $queryBuilder = $this->createQueryBuilder('ac');
  218. $queryBuilder->delete(SupportEntities\SolutionCategoryMapping::class,'ac')
  219. ->andWhere('ac.categoryId = :categoryId')
  220. ->andWhere('ac.solutionId IN (:solutionId)')
  221. ->setParameters([
  222. 'categoryId' => $categoryId ,
  223. 'solutionId' => $solutionId ,
  224. ])
  225. ->getQuery()
  226. ->execute()
  227. ;
  228. }
  230. public function removeEntryByCategory($categoryId)
  231. {
  232. $where = is_array($categoryId) ? 'ac.categoryId IN (:categoryId)' : 'ac.categoryId = :categoryId';
  234. $queryBuilder = $this->createQueryBuilder('ac');
  235. $queryBuilder->delete(SupportEntities\SolutionCategoryMapping::class,'ac')
  236. ->andWhere($where)
  237. ->setParameters([
  238. 'categoryId' => $categoryId ,
  239. ])
  240. ->getQuery()
  241. ->execute()
  242. ;
  244. $queryBuilder->delete(SupportEntities\ArticleCategory::class,'ac')
  245. ->andWhere($where)
  246. ->setParameters([
  247. 'categoryId' => $categoryId ,
  248. ])
  249. ->getQuery()
  250. ->execute()
  251. ;
  252. }
  254. public function bulkCategoryStatusUpdate($categoryIds, $status)
  255. {
  256. $query = 'UPDATE Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategory sc SET sc.status = '. (int)$status .' WHERE sc.id IN ('.implode(',', $categoryIds).')';
  258. $this->getEntityManager()->createQuery($query)->execute();
  259. }
  261. public function categorySortingUpdate($id, $sort)
  262. {
  263. $query = "UPDATE Webkul\UVDesk\SupportCenterBundle\Entity\SolutionCategory sc SET sc.sortOrder = '". (int)$sort ."' WHERE sc.id = '". (int)$id ."'";
  265. $this->getEntityManager()->createQuery($query)->execute();
  266. }
  267. }