src/Eccube/Repository/ProductRepository.php line 114

Open in your IDE?
  1. <?php
  2. /*
  3.  * This file is part of EC-CUBE
  4.  *
  5.  * Copyright(c) EC-CUBE CO.,LTD. All Rights Reserved.
  6.  *
  7.  * http://www.ec-cube.co.jp/
  8.  *
  9.  * For the full copyright and license information, please view the LICENSE
  10.  * file that was distributed with this source code.
  11.  */
  12. namespace Eccube\Repository;
  13. use Doctrine\Common\Collections\ArrayCollection;
  14. use Doctrine\Persistence\ManagerRegistry as RegistryInterface;
  15. use Eccube\Common\EccubeConfig;
  16. use Eccube\Doctrine\Query\Queries;
  17. use Eccube\Entity\Category;
  18. use Eccube\Entity\Master\ProductListMax;
  19. use Eccube\Entity\Master\ProductListOrderBy;
  20. use Eccube\Entity\Master\ProductStatus;
  21. use Eccube\Entity\Product;
  22. use Eccube\Entity\ProductStock;
  23. use Eccube\Entity\Tag;
  24. use Eccube\Util\StringUtil;
  25. /**
  26.  * ProductRepository
  27.  *
  28.  * This class was generated by the Doctrine ORM. Add your own custom
  29.  * repository methods below.
  30.  */
  31. class ProductRepository extends AbstractRepository
  32. {
  33.     /**
  34.      * @var Queries
  35.      */
  36.     protected $queries;
  37.     /**
  38.      * @var EccubeConfig
  39.      */
  40.     protected $eccubeConfig;
  41.     /**
  42.      * @var CategoryRepository
  43.      */
  44.     private $categoryRepository;
  45.     public const COLUMNS = [
  46.         'product_id' => 'p.id''name' => 'p.name''product_code' => 'pc.code''stock' => 'pc.stock''status' => 'p.Status''create_date' => 'p.create_date''update_date' => 'p.update_date',
  47.     ];
  48.     /**
  49.      * ProductRepository constructor.
  50.      *
  51.      * @param RegistryInterface $registry
  52.      * @param Queries $queries
  53.      * @param EccubeConfig $eccubeConfig
  54.      * @param CategoryRepository $categoryRepository
  55.      */
  56.     public function __construct(
  57.         RegistryInterface $registry,
  58.         Queries $queries,
  59.         EccubeConfig $eccubeConfig,
  60.         CategoryRepository $categoryRepository
  61.     ) {
  62.         parent::__construct($registryProduct::class);
  63.         $this->queries $queries;
  64.         $this->eccubeConfig $eccubeConfig;
  65.         $this->categoryRepository $categoryRepository;
  66.     }
  67.     /**
  68.      * Find the Product with sorted ClassCategories.
  69.      *
  70.      * @param integer $productId
  71.      *
  72.      * @return Product
  73.      */
  74.     public function findWithSortedClassCategories($productId)
  75.     {
  76.         $qb $this->createQueryBuilder('p');
  77.         $qb->addSelect(['pc''cc1''cc2''pi''pt'])
  78.             ->innerJoin('p.ProductClasses''pc')
  79.             ->leftJoin('pc.ClassCategory1''cc1')
  80.             ->leftJoin('pc.ClassCategory2''cc2')
  81.             ->leftJoin('p.ProductImage''pi')
  82.             ->leftJoin('p.ProductTag''pt')
  83.             ->where('p.id = :id')
  84.             ->andWhere('pc.visible = :visible')
  85.             ->setParameter('id'$productId)
  86.             ->setParameter('visible'true)
  87.             ->orderBy('cc1.sort_no''DESC')
  88.             ->addOrderBy('cc2.sort_no''DESC');
  89.         $product $qb
  90.             ->getQuery()
  91.             ->getSingleResult();
  92.         return $product;
  93.     }
  94.     /**
  95.      * Find the Products with sorted ClassCategories.
  96.      *
  97.      * @param array $ids Product in ids
  98.      * @param string $indexBy The index for the from.
  99.      *
  100.      * @return ArrayCollection|array
  101.      */
  102.     public function findProductsWithSortedClassCategories(array $ids$indexBy null)
  103.     {
  104.         if (count($ids) < 1) {
  105.             return [];
  106.         }
  107.         $qb $this->createQueryBuilder('p'$indexBy);
  108.         $qb->addSelect(['pc''cc1''cc2''pi''pt''tr''ps'])
  109.             ->innerJoin('p.ProductClasses''pc')
  110.             // XXX Joined 'TaxRule' and 'ProductStock' to prevent lazy loading
  111.             ->leftJoin('pc.TaxRule''tr')
  112.             ->innerJoin('pc.ProductStock''ps')
  113.             ->leftJoin('pc.ClassCategory1''cc1')
  114.             ->leftJoin('pc.ClassCategory2''cc2')
  115.             ->leftJoin('p.ProductImage''pi')
  116.             ->leftJoin('p.ProductTag''pt')
  117.             ->where($qb->expr()->in('p.id'$ids))
  118.             ->andWhere('pc.visible = :visible')
  119.             ->setParameter('visible'true)
  120.             ->orderBy('cc1.sort_no''DESC')
  121.             ->addOrderBy('cc2.sort_no''DESC');
  122.         $products $qb
  123.             ->getQuery()
  124.             ->useResultCache(true$this->eccubeConfig['eccube_result_cache_lifetime_short'])
  125.             ->getResult();
  126.         return $products;
  127.     }
  128.     /**
  129.      * get query builder.
  130.      *
  131.      * @param array{
  132.      *         category_id?:Category,
  133.      *         name?:string,
  134.      *         pageno?:string,
  135.      *         disp_number?:ProductListMax,
  136.      *         orderby?:ProductListOrderBy
  137.      *     } $searchData
  138.      *
  139.      * @return \Doctrine\ORM\QueryBuilder
  140.      */
  141.     public function getQueryBuilderBySearchData($searchData)
  142.     {
  143.         $qb $this->createQueryBuilder('p')
  144.             ->andWhere('p.Status = 1');
  145.         // category
  146.         $categoryJoin false;
  147.         if (!empty($searchData['category_id']) && $searchData['category_id']) {
  148.             $Categories $searchData['category_id']->getSelfAndDescendants();
  149.             if ($Categories) {
  150.                 $qb
  151.                     ->innerJoin('p.ProductCategories''pct')
  152.                     ->innerJoin('pct.Category''c')
  153.                     ->andWhere($qb->expr()->in('pct.Category'':Categories'))
  154.                     ->setParameter('Categories'$Categories);
  155.                 $categoryJoin true;
  156.             }
  157.         }
  158.         // name
  159.         if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
  160.             $keywords preg_split('/[\s ]+/u'str_replace(['%''_'], ['\\%''\\_'], $searchData['name']), -1PREG_SPLIT_NO_EMPTY);
  161.             foreach ($keywords as $index => $keyword) {
  162.                 $key sprintf('keyword%s'$index);
  163.                 $qb
  164.                     ->andWhere(sprintf('NORMALIZE(p.name) LIKE NORMALIZE(:%s) OR
  165.                         NORMALIZE(p.search_word) LIKE NORMALIZE(:%s) OR
  166.                         EXISTS (SELECT wpc%d FROM \Eccube\Entity\ProductClass wpc%d WHERE p = wpc%d.Product AND NORMALIZE(wpc%d.code) LIKE NORMALIZE(:%s))',
  167.                         $key$key$index$index$index$index$key))
  168.                     ->setParameter($key'%'.$keyword.'%');
  169.             }
  170.         }
  171.         // Order By
  172.         // 価格低い順
  173.         $config $this->eccubeConfig;
  174.         if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_lower']) {
  175.             // @see http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
  176.             $qb->addSelect('MIN(pc.price02) as HIDDEN price02_min');
  177.             $qb->innerJoin('p.ProductClasses''pc');
  178.             $qb->andWhere('pc.visible = true');
  179.             $qb->groupBy('p.id');
  180.             $qb->orderBy('price02_min''ASC');
  181.             $qb->addOrderBy('p.id''DESC');
  182.         // 価格高い順
  183.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_higher']) {
  184.             $qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
  185.             $qb->innerJoin('p.ProductClasses''pc');
  186.             $qb->andWhere('pc.visible = true');
  187.             $qb->groupBy('p.id');
  188.             $qb->orderBy('price02_max''DESC');
  189.             $qb->addOrderBy('p.id''DESC');
  190.         // 新着順
  191.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_newer']) {
  192.             // 在庫切れ商品非表示の設定が有効時対応
  193.             // @see https://github.com/EC-CUBE/ec-cube/issues/1998
  194.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  195.                 $qb->innerJoin('p.ProductClasses''pc');
  196.                 $qb->andWhere('pc.visible = true');
  197.             }
  198.             $qb->orderBy('p.create_date''DESC');
  199.             $qb->addOrderBy('p.id''DESC');
  200.         } else {
  201.             if ($categoryJoin === false) {
  202.                 $qb
  203.                     ->leftJoin('p.ProductCategories''pct')
  204.                     ->leftJoin('pct.Category''c');
  205.             }
  206.             $qb
  207.                 ->addOrderBy('p.id''DESC');
  208.         }
  209.         return $this->queries->customize(QueryKey::PRODUCT_SEARCH$qb$searchData);
  210.     }
  211.     /**
  212.      * get query builder.
  213.      *
  214.      * @param array{
  215.      *         id?:string|int|null,
  216.      *         category_id?:Category,
  217.      *         status?:ProductStatus[],
  218.      *         link_status?:ProductStatus[],
  219.      *         stock_status?:int,
  220.      *         stock?:ProductStock::IN_STOCK|ProductStock::OUT_OF_STOCK,
  221.      *         tag_id?:Tag,
  222.      *         create_datetime_start?:\DateTime,
  223.      *         create_datetime_end?:\DateTime,
  224.      *         create_date_start?:\DateTime,
  225.      *         create_date_end?:\DateTime,
  226.      *         update_datetime_start?:\DateTime,
  227.      *         update_datetime_end?:\DateTime,
  228.      *         update_date_start?:\DateTime,
  229.      *         update_date_end?:\DateTime,
  230.      *         sortkey?:string,
  231.      *         sorttype?:string
  232.      *     } $searchData
  233.      *
  234.      * @return \Doctrine\ORM\QueryBuilder
  235.      */
  236.     public function getQueryBuilderBySearchDataForAdmin($searchData)
  237.     {
  238.         $qb $this->createQueryBuilder('p')
  239.             ->addSelect('pc''pi''tr''ps')
  240.             ->innerJoin('p.ProductClasses''pc')
  241.             ->leftJoin('p.ProductImage''pi')
  242.             ->leftJoin('pc.TaxRule''tr')
  243.             ->leftJoin('pc.ProductStock''ps')
  244.             ->andWhere('pc.visible = :visible')
  245.             ->setParameter('visible'true);
  246.         // id
  247.         if (isset($searchData['id']) && StringUtil::isNotBlank($searchData['id'])) {
  248.             $id preg_match('/^\d{0,10}$/'$searchData['id']) ? $searchData['id'] : null;
  249.             if ($id && $id '2147483647' && $this->isPostgreSQL()) {
  250.                 $id null;
  251.             }
  252.             $qb
  253.                 ->andWhere('p.id = :id OR p.name LIKE :likeid OR pc.code LIKE :likeid')
  254.                 ->setParameter('id'$id)
  255.                 ->setParameter('likeid''%'.str_replace(['%''_'], ['\\%''\\_'], $searchData['id']).'%');
  256.         }
  257.         // code
  258.         /*
  259.         if (!empty($searchData['code']) && $searchData['code']) {
  260.             $qb
  261.                 ->innerJoin('p.ProductClasses', 'pc')
  262.                 ->andWhere('pc.code LIKE :code')
  263.                 ->setParameter('code', '%' . $searchData['code'] . '%');
  264.         }
  265.         // name
  266.         if (!empty($searchData['name']) && $searchData['name']) {
  267.             $keywords = preg_split('/[\s ]+/u', $searchData['name'], -1, PREG_SPLIT_NO_EMPTY);
  268.             foreach ($keywords as $keyword) {
  269.                 $qb
  270.                     ->andWhere('p.name LIKE :name')
  271.                     ->setParameter('name', '%' . $keyword . '%');
  272.             }
  273.         }
  274.        */
  275.         // category
  276.         if (!empty($searchData['category_id']) && $searchData['category_id']) {
  277.             $Categories $searchData['category_id']->getSelfAndDescendants();
  278.             if ($Categories) {
  279.                 $qb
  280.                     ->innerJoin('p.ProductCategories''pct')
  281.                     ->innerJoin('pct.Category''c')
  282.                     ->andWhere($qb->expr()->in('pct.Category'':Categories'))
  283.                     ->setParameter('Categories'$Categories);
  284.             }
  285.         }
  286.         // status
  287.         if (!empty($searchData['status']) && $searchData['status']) {
  288.             $qb
  289.                 ->andWhere($qb->expr()->in('p.Status'':Status'))
  290.                 ->setParameter('Status'$searchData['status']);
  291.         }
  292.         // link_status
  293.         if (isset($searchData['link_status']) && !empty($searchData['link_status'])) {
  294.             $qb
  295.                 ->andWhere($qb->expr()->in('p.Status'':Status'))
  296.                 ->setParameter('Status'$searchData['link_status']);
  297.         }
  298.         // stock status
  299.         if (isset($searchData['stock_status'])) {
  300.             $qb
  301.                 ->andWhere('pc.stock_unlimited = :StockUnlimited AND pc.stock = 0')
  302.                 ->setParameter('StockUnlimited'$searchData['stock_status']);
  303.         }
  304.         // stock status
  305.         if (isset($searchData['stock']) && !empty($searchData['stock'])) {
  306.             switch ($searchData['stock']) {
  307.                 case [ProductStock::IN_STOCK]:
  308.                     $qb->andWhere('pc.stock_unlimited = true OR pc.stock > 0');
  309.                     break;
  310.                 case [ProductStock::OUT_OF_STOCK]:
  311.                     $qb->andWhere('pc.stock_unlimited = false AND pc.stock <= 0');
  312.                     break;
  313.                 default:
  314.                     // 共に選択された場合は全権該当するので検索条件に含めない
  315.             }
  316.         }
  317.         // tag
  318.         if (!empty($searchData['tag_id']) && $searchData['tag_id']) {
  319.             $qb
  320.                 ->innerJoin('p.ProductTag''pt')
  321.                 ->andWhere('pt.Tag = :tag_id')
  322.                 ->setParameter('tag_id'$searchData['tag_id']);
  323.         }
  324.         // crate_date
  325.         if (!empty($searchData['create_datetime_start']) && $searchData['create_datetime_start']) {
  326.             $date $searchData['create_datetime_start'];
  327.             $qb
  328.                 ->andWhere('p.create_date >= :create_date_start')
  329.                 ->setParameter('create_date_start'$date);
  330.         } elseif (!empty($searchData['create_date_start']) && $searchData['create_date_start']) {
  331.             $date $searchData['create_date_start'];
  332.             $qb
  333.                 ->andWhere('p.create_date >= :create_date_start')
  334.                 ->setParameter('create_date_start'$date);
  335.         }
  336.         if (!empty($searchData['create_datetime_end']) && $searchData['create_datetime_end']) {
  337.             $date $searchData['create_datetime_end'];
  338.             $qb
  339.                 ->andWhere('p.create_date < :create_date_end')
  340.                 ->setParameter('create_date_end'$date);
  341.         } elseif (!empty($searchData['create_date_end']) && $searchData['create_date_end']) {
  342.             $date = clone $searchData['create_date_end'];
  343.             $date $date
  344.                 ->modify('+1 days');
  345.             $qb
  346.                 ->andWhere('p.create_date < :create_date_end')
  347.                 ->setParameter('create_date_end'$date);
  348.         }
  349.         // update_date
  350.         if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
  351.             $date $searchData['update_datetime_start'];
  352.             $qb
  353.                 ->andWhere('p.update_date >= :update_date_start')
  354.                 ->setParameter('update_date_start'$date);
  355.         } elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
  356.             $date $searchData['update_date_start'];
  357.             $qb
  358.                 ->andWhere('p.update_date >= :update_date_start')
  359.                 ->setParameter('update_date_start'$date);
  360.         }
  361.         if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
  362.             $date $searchData['update_datetime_end'];
  363.             $qb
  364.                 ->andWhere('p.update_date < :update_date_end')
  365.                 ->setParameter('update_date_end'$date);
  366.         } elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
  367.             $date = clone $searchData['update_date_end'];
  368.             $date $date
  369.                 ->modify('+1 days');
  370.             $qb
  371.                 ->andWhere('p.update_date < :update_date_end')
  372.                 ->setParameter('update_date_end'$date);
  373.         }
  374.         // Order By
  375.         if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
  376.             $sortOrder = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' 'DESC';
  377.             $qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortOrder);
  378.             $qb->addOrderBy('p.update_date''DESC');
  379.             $qb->addOrderBy('p.id''DESC');
  380.         } else {
  381.             $qb->orderBy('p.update_date''DESC');
  382.             $qb->addOrderBy('p.id''DESC');
  383.         }
  384.         return $this->queries->customize(QueryKey::PRODUCT_SEARCH_ADMIN$qb$searchData);
  385.     }
  386.     public function getNewArrivals(int $limit 10)
  387.     {
  388.         return $this->createQueryBuilder('p')
  389.             ->where('p.Status = 1')
  390.             ->orderBy('p.create_date''DESC')
  391.             ->setMaxResults($limit)
  392.             ->getQuery()
  393.             ->getResult();
  394.     }
  395.     public function countProductByCategory(Category $category)
  396.     {
  397.         $childCategories $this->categoryRepository->getChildCategoryIds($category);
  398.         $categoryIds array_merge([$category->getId()], $childCategories);
  399.         return $this->createQueryBuilder('p')
  400.         ->select('COUNT(DISTINCT p.id)')
  401.         ->join('p.ProductCategories''pc')
  402.         ->join('pc.Category''c')
  403.         ->where('c.id IN (:category_ids)')
  404.         ->andWhere('p.Status = :status')
  405.         ->setParameter('category_ids'$categoryIds)
  406.         ->setParameter('status'ProductStatus::DISPLAY_SHOW)
  407.         ->getQuery()
  408.         ->getSingleScalarResult();
  409.     }
  410. }