src/Eccube/Repository/ProductRepository.php line 77

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