app/Customize/Repository/ProductRepository.php line 107

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