app/Customize/Repository/Extension/ProductRepositoryExtension.php line 35

Open in your IDE?
  1. <?php
  3. namespace Customize\Repository\Extension;
  5. use Eccube\Repository\ProductRepository;
  6. use Eccube\Repository\QueryKey;
  7. use Eccube\Entity\Product;
  8. use Plugin\Maker42\Entity\Maker;
  9. use Eccube\Util\StringUtil;
  10. use Eccube\Entity\Master\ProductStatus;
  11. use Eccube\Common\EccubeConfig;
  12. use Eccube\Doctrine\Query\Queries;
  13. use Doctrine\Persistence\ManagerRegistry;
  14. use Symfony\Contracts\Cache\CacheInterface;
  15. use Symfony\Contracts\Cache\ItemInterface;
  17. class ProductRepositoryExtension extends ProductRepository
  18. {
  19.     /**
  20.      * @var CacheInterface
  21.      */
  22.     private CacheInterface $cache;
  24.     /**
  25.      * カテゴリ別商品数のキャッシュ有効期間（秒）
  26.      */
  27.     private const CATEGORY_COUNT_CACHE_LIFETIME = 3600;
  29.     public function __construct(
  30.         ManagerRegistry $registry,
  31.         Queries $queries,
  32.         EccubeConfig $eccubeConfig,
  33.         CacheInterface $cache
  34.     ) {
  35.         parent::__construct($registry, $queries, $eccubeConfig);
  36.         $this->cache = $cache;
  37.     }
  39.     /**
  40.      * get query builder.
  41.      *
  42.      * @param  array $searchData
  43.      *
  44.      * @return \Doctrine\ORM\QueryBuilder
  45.      */
  46.     public function customizeGetQueryBuilderBySearchData($searchData)
  47.     {
  48.         $qb = $this->createQueryBuilder('p')
  49.             ->andwhere('p.Status = :Disp')
  50.             ->setParameter('Disp', ProductStatus::DISPLAY_SHOW);
  52.         // category
  53.         $categoryJoin = false;
  55.         if (!empty($searchData['category_id']) && $searchData['category_id']) {
  56.             $Categories = $searchData['category_id']->getSelfAndDescendants();
  57.             if ($Categories) {
  58.                 $qb
  59.                     ->innerJoin('p.ProductCategories', 'pct')
  60.                     ->innerJoin('pct.Category', 'c')
  61.                     ->andWhere($qb->expr()->in('pct.Category', ':Categories'))
  62.                     ->setParameter('Categories', $Categories);
  63.                 $categoryJoin = true;
  64.             }
  65.         }
  67.         //maker
  68.         $qb
  69.             ->leftJoin('p.Maker', 'm');
  70.         if (!empty($searchData['maker_id']) && $searchData['maker_id']) {
  71.             $qb
  72.                 ->andWhere('m = :id')
  73.                 ->setParameter('id', $searchData['maker_id']);
  74.         }
  76.         //stock又は新商品のみ
  77.         if (empty($searchData['nostock']) || (!empty($searchData['new']) && $searchData['new'])) {
  78.             $qb
  79.                 ->leftJoin('p.ProductClasses', 'pcl');
  80.         }
  82.         //stock
  83.         if (empty($searchData['nostock'])) {
  84.             $qb
  85.                 ->andWhere('pcl.stock > 0');
  86.         }
  88.         //新商品のみ(２週間以内の発売日)
  89.         if (!empty($searchData['new'])) {
  90.             $today = date("Y-m-d");
  91.             //eccubeで登録される時間が日本時間の６時間前になる為、２週間＋６時間前をの日付を取得
  92.             $two_week_ago = date("Y-m-d", strtotime("{$today} -2 week -6 hour"));
  93.             $qb
  94.                 ->andWhere(" '{$two_week_ago}' <= pcl.release_date and pcl.release_date < '{$today}' ");
  95.         }
  97.         //タグ検索　TOPページスライダー
  98.         if (!empty($searchData['tag_slider']) && $searchData['tag_slider']) {
  99.             $qb
  100.                 ->innerJoin('p.ProductTag', 'pt')
  101.                 ->innerJoin('pt.Tag', 'tag')
  102.                 ->andWhere("tag.name = '{$searchData['tag_slider']}'");
  103.         }
  105.         // name
  106.         if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
  107.             //スペース、全角スペースで分割
  108.             $keywords = preg_split('/[\s　]+/u', str_replace(['%', '_'], ['\\%', '\\_'], $searchData['name']), -1, PREG_SPLIT_NO_EMPTY);
  110.             foreach ($keywords as $index => $keyword) {
  111.                 $key = sprintf('keyword%s', $index);
  112.                 $qb
  113.                     ->andWhere(sprintf(
  114.                         'NORMALIZE(m.name) LIKE NORMALIZE(:%s) OR
  115.                         REPLACE(REPLACE(NORMALIZE(p.name), \'ﾞ\', \'\'), \'ﾟ\', \'\') LIKE REPLACE(REPLACE(NORMALIZE(:%s), \'ﾞ\', \'\'), \'ﾟ\', \'\') OR
  116.                         NORMALIZE(p.search_word) LIKE NORMALIZE(:%s) OR
  117.                         NORMALIZE(p.description_detail) LIKE NORMALIZE(:%s) OR
  118.                         NORMALIZE(p.free_area) LIKE NORMALIZE(:%s) OR
  119.                         EXISTS (SELECT wpc%d FROM \Eccube\Entity\ProductClass wpc%d WHERE p = wpc%d.Product AND NORMALIZE(wpc%d.code) LIKE NORMALIZE(:%s))',
  120.                         $key,
  121.                         $key,
  122.                         $key,
  123.                         $key,
  124.                         $key,
  125.                         $index,
  126.                         $index,
  127.                         $index,
  128.                         $index,
  129.                         $key
  130.                     ))
  131.                     ->setParameter($key, '%' . $keyword . '%');
  132.             }
  133.         }
  134.         // Order By
  135.         // 価格低い順
  136.         $config = $this->eccubeConfig;
  137.         if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_lower']) {
  138.             // @see http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
  139.             $qb->addSelect('MIN(pc.price02) as HIDDEN price02_min');
  140.             $qb->innerJoin('p.ProductClasses', 'pc');
  141.             $qb->andWhere('pc.visible = true');
  142.             $qb->groupBy('p.id');
  143.             $qb->orderBy('price02_min', 'ASC');
  144.             $qb->addOrderBy('p.id', 'ASC');
  145.             // 価格高い順
  146.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_higher']) {
  147.             $qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
  148.             $qb->innerJoin('p.ProductClasses', 'pc');
  149.             $qb->andWhere('pc.visible = true');
  150.             $qb->groupBy('p.id');
  151.             $qb->orderBy('price02_max', 'DESC');
  152.             $qb->addOrderBy('p.id', 'ASC');
  153.             // 新着順
  154.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_newer']) {
  155.             // 在庫切れ商品非表示の設定が有効時対応
  156.             // @see https://github.com/EC-CUBE/ec-cube/issues/1998
  157.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  158.                 $qb->innerJoin('p.ProductClasses', 'pc');
  159.                 $qb->andWhere('pc.visible = true');
  160.             }
  161.             $qb->orderBy('p.create_date', 'DESC');
  162.             $qb->addOrderBy('p.id', 'ASC');
  163.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_best_seller']) {
  164.             //売れ筋順
  166.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_japanese_asc']) {
  167.             // 五十音順（昇順）
  168.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  169.                 $qb->innerJoin('p.ProductClasses', 'pc');
  170.                 $qb->andWhere('pc.visible = true');
  171.             }
  172.             $qb->addOrderBy('p.kana', 'ASC');
  173.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_japanese_desc']) {
  174.             // 五十音順（降順）
  175.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  176.                 $qb->innerJoin('p.ProductClasses', 'pc');
  177.                 $qb->andWhere('pc.visible = true');
  178.             }
  179.             $qb->addOrderBy('p.kana', 'DESC');
  180.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_maker_part_number_asc']) {
  181.             // 品番順（昇順）
  182.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  183.                 $qb->innerJoin('p.ProductClasses', 'pc');
  184.                 $qb->andWhere('pc.visible = true');
  185.             }
  186.             $qb->addOrderBy('p.makerPartNumber', 'ASC');
  187.         } elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_maker_part_number_desc']) {
  188.             // 品番順（降順）
  189.             if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
  190.                 $qb->innerJoin('p.ProductClasses', 'pc');
  191.                 $qb->andWhere('pc.visible = true');
  192.             }
  193.             $qb->addOrderBy('p.makerPartNumber', 'DESC');
  194.         } else {
  195.             if ($categoryJoin === false) {
  196.                 $qb
  197.                     ->leftJoin('p.ProductCategories', 'pct')
  198.                     ->leftJoin('pct.Category', 'c');
  199.             }
  200.             $qb
  201.                 ->addOrderBy('p.id', 'ASC');
  202.         }
  203.         return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
  204.     }
  206.     public function findProductsWithCategoriesCount()
  207.     {
  208.         return $this->cache->get('categories_product_count', function (ItemInterface $item) {
  209.             $item->expiresAfter(self::CATEGORY_COUNT_CACHE_LIFETIME);
  211.             $qb = $this->createQueryBuilder('p');
  213.             $result = $qb
  214.                 ->select('c.id,COUNT(p.id) as cnt')
  215.                 ->where('p.Status = :Disp')
  216.                 ->setParameter('Disp', ProductStatus::DISPLAY_SHOW)
  217.                 ->innerJoin('p.ProductCategories', 'pct')
  218.                 ->innerJoin('pct.Category', 'c')
  219.                 ->groupBy('c.id')
  220.                 ->getQuery()
  221.                 ->getResult();
  223.             $counts = [];
  224.             foreach ($result as $r) {
  225.                 $counts[$r['id']] = $r['cnt'];
  226.             }
  227.             return $counts;
  228.         });
  229.     }
  231.     /**
  232.      * カテゴリ別の商品数を取得（在庫切れ商品を除外）
  233.      *
  234.      * @return array カテゴリID => 商品数 の連想配列
  235.      */
  236.     public function findProductsWithCategoriesCountInStock()
  237.     {
  238.         return $this->cache->get('categories_product_count_in_stock', function (ItemInterface $item) {
  239.             $item->expiresAfter(self::CATEGORY_COUNT_CACHE_LIFETIME);
  241.             $qb = $this->createQueryBuilder('p');
  243.             $result = $qb
  244.                 ->select('c.id, COUNT(DISTINCT p.id) as cnt')
  245.                 ->where('p.Status = :Disp')
  246.                 ->setParameter('Disp', ProductStatus::DISPLAY_SHOW)
  247.                 ->innerJoin('p.ProductCategories', 'pct')
  248.                 ->innerJoin('pct.Category', 'c')
  249.                 ->innerJoin('p.ProductClasses', 'pc')
  250.                 ->andWhere('pc.stock > 0')
  251.                 ->groupBy('c.id')
  252.                 ->getQuery()
  253.                 ->getResult();
  255.             $counts = [];
  256.             foreach ($result as $r) {
  257.                 $counts[$r['id']] = $r['cnt'];
  258.             }
  259.             return $counts;
  260.         });
  261.     }
  263.     public function findProductsWithMakersCount($maker_id)
  264.     {
  265.         $qb = $this->createQueryBuilder('p');
  266.         // maker
  267.         if ($maker_id) {
  268.             return $qb
  269.                 ->where('p.Status = :Disp')
  270.                 ->setParameter('Disp', ProductStatus::DISPLAY_SHOW) #公開になっているもの
  271.                 ->leftJoin('p.Maker', 'm')
  272.                 ->select('COUNT(m.id)')
  273.                 ->andWhere('m.id =' . $maker_id)
  274.                 ->getQuery() #作成したクエリを取得
  275.                 ->getSingleScalarResult(); #実行 getSingleScalarResultはcountした時、数値が取得できます。
  276.         }
  277.     }
  279.     public function findProductsStockCount($id)
  280.     {
  281.         $qb = $this->createQueryBuilder('p');
  282.         if ($id) {
  283.             return $qb
  284.                 ->where('p.Status = :Disp')
  285.                 ->setParameter('Disp', ProductStatus::DISPLAY_SHOW) #公開になっているもの
  286.                 ->leftJoin('p.ProductClasses', 'pc')
  287.                 ->select('pc.stock')
  288.                 ->andWhere('p.id =' . $id)
  289.                 ->getQuery() #作成したクエリを取得
  290.                 ->getSingleScalarResult(); #実行 getSingleScalarResultはcountした時、数値が取得できます。
  291.         }
  292.     }
  294.     /**
  295.      * Find the Products with sorted ClassCategories.
  296.      *
  297.      * @param array $ids Product in ids
  298.      * @param string $indexBy The index for the from.
  299.      *
  300.      * @return ArrayCollection|array
  301.      */
  302.     public function findProductsWithSortedClassCategories(array $ids, $indexBy = null)
  303.     {
  304.         if (count($ids) < 1) {
  305.             return [];
  306.         }
  307.         $qb = $this->createQueryBuilder('p', $indexBy);
  308.         $qb->addSelect(['pc', 'cc1', 'cc2', 'pi', 'pt', 'tr', 'ps'])
  309.             ->innerJoin('p.ProductClasses', 'pc')
  310.             // XXX Joined 'TaxRule' and 'ProductStock' to prevent lazy loading
  311.             ->leftJoin('pc.TaxRule', 'tr')
  312.             ->leftJoin('pc.ProductStock', 'ps')
  313.             ->leftJoin('pc.ClassCategory1', 'cc1')
  314.             ->leftJoin('pc.ClassCategory2', 'cc2')
  315.             ->leftJoin('p.ProductImage', 'pi')
  316.             ->leftJoin('p.ProductTag', 'pt')
  317.             ->where($qb->expr()->in('p.id', $ids))
  318.             ->andWhere('pc.visible = :visible')
  319.             ->setParameter('visible', true)
  320.             ->orderBy('cc1.sort_no', 'DESC')
  321.             ->addOrderBy('cc2.sort_no', 'DESC');
  323.         $products = $qb
  324.             ->getQuery()
  325.             ->useResultCache(true, $this->eccubeConfig['eccube_result_cache_lifetime_short'])
  326.             ->getResult();
  328.         return $products;
  329.     }
  330. }