src/Eccube/Repository/OrderRepository.php line 47

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\ORM\NoResultException;
  17. use Doctrine\ORM\QueryBuilder;
  18. use Eccube\Doctrine\Query\Queries;
  19. use Eccube\Entity\Customer;
  20. use Eccube\Entity\Master\OrderStatus;
  21. use Eccube\Entity\Order;
  22. use Eccube\Entity\Shipping;
  23. use Eccube\Util\StringUtil;
  24. use Symfony\Bridge\Doctrine\RegistryInterface;
  26. /**
  27.  * OrderRepository
  28.  *
  29.  * This class was generated by the Doctrine ORM. Add your own custom
  30.  * repository methods below.
  31.  */
  32. class OrderRepository extends AbstractRepository
  33. {
  34.     /**
  35.      * @var Queries
  36.      */
  37.     protected $queries;
  39.     /**
  40.      * OrderRepository constructor.
  41.      *
  42.      * @param RegistryInterface $registry
  43.      * @param Queries $queries
  44.      */
  45.     public function __construct(RegistryInterface $registry, Queries $queries)
  46.     {
  47.         parent::__construct($registry, Order::class);
  48.         $this->queries = $queries;
  49.     }
  51.     /**
  52.      * @param int $orderId
  53.      * @param OrderStatus $Status
  54.      */
  55.     public function changeStatus($orderId, \Eccube\Entity\Master\OrderStatus $Status)
  56.     {
  57.         $Order = $this
  58.             ->find($orderId)
  59.             ->setOrderStatus($Status)
  60.         ;
  62.         switch ($Status->getId()) {
  63.             case '6': // 入金済へ
  64.                 $Order->setPaymentDate(new \DateTime());
  65.                 break;
  66.         }
  68.         $em = $this->getEntityManager();
  69.         $em->persist($Order);
  70.         $em->flush();
  71.     }
  73.     /**
  74.      * @param  array        $searchData
  75.      *
  76.      * @return QueryBuilder
  77.      */
  78.     public function getQueryBuilderBySearchDataForAdmin($searchData)
  79.     {
  80.         $qb = $this->createQueryBuilder('o')
  81.             ->select('o, s')
  82.             ->addSelect('oi', 'pref')
  83.             ->leftJoin('o.OrderItems', 'oi')
  84.             ->leftJoin('o.Pref', 'pref')
  85.             ->innerJoin('o.Shippings', 's');
  87.         // order_id_start
  88.         if (isset($searchData['order_id']) && StringUtil::isNotBlank($searchData['order_id'])) {
  89.             $qb
  90.                 ->andWhere('o.id = :order_id')
  91.                 ->setParameter('order_id', $searchData['order_id']);
  92.         }
  94.         // order_no
  95.         if (isset($searchData['order_no']) && StringUtil::isNotBlank($searchData['order_no'])) {
  96.             $qb
  97.                 ->andWhere('o.order_no = :order_no')
  98.                 ->setParameter('order_no', $searchData['order_no']);
  99.         }
  101.         // order_id_start
  102.         if (isset($searchData['order_id_start']) && StringUtil::isNotBlank($searchData['order_id_start'])) {
  103.             $qb
  104.                 ->andWhere('o.id >= :order_id_start')
  105.                 ->setParameter('order_id_start', $searchData['order_id_start']);
  106.         }
  107.         // multi
  108.         if (isset($searchData['multi']) && StringUtil::isNotBlank($searchData['multi'])) {
  109.             $multi = preg_match('/^\d{0,10}$/', $searchData['multi']) ? $searchData['multi'] : null;
  110.             if ($multi && $multi > '2147483647' && $this->isPostgreSQL()) {
  111.                 $multi = null;
  112.             }
  113.             $qb
  114.                 ->andWhere('o.id = :multi OR o.name01 LIKE :likemulti OR o.name02 LIKE :likemulti OR '.
  115.                             'o.kana01 LIKE :likemulti OR o.kana02 LIKE :likemulti OR o.company_name LIKE :likemulti OR '.
  116.                             'o.order_no LIKE :likemulti OR o.email LIKE :likemulti OR o.phone_number LIKE :likemulti')
  117.                 ->setParameter('multi', $multi)
  118.                 ->setParameter('likemulti', '%'.$searchData['multi'].'%');
  119.         }
  121.         // order_id_end
  122.         if (isset($searchData['order_id_end']) && StringUtil::isNotBlank($searchData['order_id_end'])) {
  123.             $qb
  124.                 ->andWhere('o.id <= :order_id_end')
  125.                 ->setParameter('order_id_end', $searchData['order_id_end']);
  126.         }
  128.         // status
  129.         $filterStatus = false;
  130.         if (!empty($searchData['status']) && count($searchData['status'])) {
  131.             $qb
  132.                 ->andWhere($qb->expr()->in('o.OrderStatus', ':status'))
  133.                 ->setParameter('status', $searchData['status']);
  134.             $filterStatus = true;
  135.         }
  137.         if (!$filterStatus) {
  138.             // 購入処理中, 決済処理中は検索対象から除外
  139.             $qb->andWhere($qb->expr()->notIn('o.OrderStatus', ':status'))
  140.                 ->setParameter('status', [OrderStatus::PROCESSING, OrderStatus::PENDING]);
  141.         }
  143.         // company_name
  144.         if (isset($searchData['company_name']) && StringUtil::isNotBlank($searchData['company_name'])) {
  145.             $qb
  146.                 ->andWhere('o.company_name LIKE :company_name')
  147.                 ->setParameter('company_name', '%'.$searchData['company_name'].'%');
  148.         }
  150.         // name
  151.         if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
  152.             $qb
  153.                 ->andWhere('CONCAT(o.name01, o.name02) LIKE :name')
  154.                 ->setParameter('name', '%'.$searchData['name'].'%');
  155.         }
  157.         // kana
  158.         if (isset($searchData['kana']) && StringUtil::isNotBlank($searchData['kana'])) {
  159.             $qb
  160.                 ->andWhere('CONCAT(o.kana01, o.kana02) LIKE :kana')
  161.                 ->setParameter('kana', '%'.$searchData['kana'].'%');
  162.         }
  164.         // email
  165.         if (isset($searchData['email']) && StringUtil::isNotBlank($searchData['email'])) {
  166.             $qb
  167.                 ->andWhere('o.email like :email')
  168.                 ->setParameter('email', '%'.$searchData['email'].'%');
  169.         }
  171.         // tel
  172.         if (isset($searchData['phone_number']) && StringUtil::isNotBlank($searchData['phone_number'])) {
  173.             $tel = preg_replace('/[^0-9]/ ', '', $searchData['phone_number']);
  174.             $qb
  175.                 ->andWhere('o.phone_number LIKE :phone_number')
  176.                 ->setParameter('phone_number', '%'.$tel.'%');
  177.         }
  179.         // sex
  180.         if (!empty($searchData['sex']) && count($searchData['sex']) > 0) {
  181.             $qb
  182.                 ->andWhere($qb->expr()->in('o.Sex', ':sex'))
  183.                 ->setParameter('sex', $searchData['sex']->toArray());
  184.         }
  186.         // payment
  187.         if (!empty($searchData['payment']) && count($searchData['payment'])) {
  188.             $payments = [];
  189.             foreach ($searchData['payment'] as $payment) {
  190.                 $payments[] = $payment->getId();
  191.             }
  192.             $qb
  193.                 ->leftJoin('o.Payment', 'p')
  194.                 ->andWhere($qb->expr()->in('p.id', ':payments'))
  195.                 ->setParameter('payments', $payments);
  196.         }
  198.         // oreder_date
  199.         if (!empty($searchData['order_datetime_start']) && $searchData['order_datetime_start']) {
  200.             $date = $searchData['order_datetime_start'];
  201.             $qb
  202.                 ->andWhere('o.order_date >= :order_date_start')
  203.                 ->setParameter('order_date_start', $date);
  204.         } elseif (!empty($searchData['order_date_start']) && $searchData['order_date_start']) {
  205.             $date = $searchData['order_date_start'];
  206.             $qb
  207.                 ->andWhere('o.order_date >= :order_date_start')
  208.                 ->setParameter('order_date_start', $date);
  209.         }
  211.         if (!empty($searchData['order_datetime_end']) && $searchData['order_datetime_end']) {
  212.             $date = $searchData['order_datetime_end'];
  213.             $qb
  214.                 ->andWhere('o.order_date < :order_date_end')
  215.                 ->setParameter('order_date_end', $date);
  216.         } elseif (!empty($searchData['order_date_end']) && $searchData['order_date_end']) {
  217.             $date = clone $searchData['order_date_end'];
  218.             $date = $date
  219.                 ->modify('+1 days');
  220.             $qb
  221.                 ->andWhere('o.order_date < :order_date_end')
  222.                 ->setParameter('order_date_end', $date);
  223.         }
  225.         // payment_date
  226.         if (!empty($searchData['payment_datetime_start']) && $searchData['payment_datetime_start']) {
  227.             $date = $searchData['payment_datetime_start'];
  228.             $qb
  229.                 ->andWhere('o.payment_date >= :payment_date_start')
  230.                 ->setParameter('payment_date_start', $date);
  231.         } elseif (!empty($searchData['payment_date_start']) && $searchData['payment_date_start']) {
  232.             $date = $searchData['payment_date_start'];
  233.             $qb
  234.                 ->andWhere('o.payment_date >= :payment_date_start')
  235.                 ->setParameter('payment_date_start', $date);
  236.         }
  238.         if (!empty($searchData['payment_datetime_end']) && $searchData['payment_datetime_end']) {
  239.             $date = $searchData['payment_datetime_end'];
  240.             $qb
  241.                 ->andWhere('o.payment_date < :payment_date_end')
  242.                 ->setParameter('payment_date_end', $date);
  243.         } elseif (!empty($searchData['payment_date_end']) && $searchData['payment_date_end']) {
  244.             $date = clone $searchData['payment_date_end'];
  245.             $date = $date
  246.                 ->modify('+1 days');
  247.             $qb
  248.                 ->andWhere('o.payment_date < :payment_date_end')
  249.                 ->setParameter('payment_date_end', $date);
  250.         }
  252.         // update_date
  253.         if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
  254.             $date = $searchData['update_datetime_start'];
  255.             $qb
  256.                 ->andWhere('o.update_date >= :update_date_start')
  257.                 ->setParameter('update_date_start', $date);
  258.         } elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
  259.             $date = $searchData['update_date_start'];
  260.             $qb
  261.                 ->andWhere('o.update_date >= :update_date_start')
  262.                 ->setParameter('update_date_start', $date);
  263.         }
  265.         if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
  266.             $date = $searchData['update_datetime_end'];
  267.             $qb
  268.                 ->andWhere('o.update_date < :update_date_end')
  269.                 ->setParameter('update_date_end', $date);
  270.         } elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
  271.             $date = clone $searchData['update_date_end'];
  272.             $date = $date
  273.                 ->modify('+1 days');
  274.             $qb
  275.                 ->andWhere('o.update_date < :update_date_end')
  276.                 ->setParameter('update_date_end', $date);
  277.         }
  279.         // payment_total
  280.         if (isset($searchData['payment_total_start']) && StringUtil::isNotBlank($searchData['payment_total_start'])) {
  281.             $qb
  282.                 ->andWhere('o.payment_total >= :payment_total_start')
  283.                 ->setParameter('payment_total_start', $searchData['payment_total_start']);
  284.         }
  285.         if (isset($searchData['payment_total_end']) && StringUtil::isNotBlank($searchData['payment_total_end'])) {
  286.             $qb
  287.                 ->andWhere('o.payment_total <= :payment_total_end')
  288.                 ->setParameter('payment_total_end', $searchData['payment_total_end']);
  289.         }
  291.         // buy_product_name
  292.         if (isset($searchData['buy_product_name']) && StringUtil::isNotBlank($searchData['buy_product_name'])) {
  293.             $qb
  294.                 ->andWhere('oi.product_name LIKE :buy_product_name')
  295.                 ->setParameter('buy_product_name', '%'.$searchData['buy_product_name'].'%');
  296.         }
  298.         // 発送メール送信/未送信.
  299.         if (isset($searchData['shipping_mail']) && $count = count($searchData['shipping_mail'])) {
  300.             // 送信済/未送信両方にチェックされている場合は検索条件に追加しない
  301.             if ($count < 2) {
  302.                 $checked = current($searchData['shipping_mail']);
  303.                 if ($checked == Shipping::SHIPPING_MAIL_UNSENT) {
  304.                     // 未送信
  305.                     $qb
  306.                         ->andWhere('s.mail_send_date IS NULL');
  307.                 } elseif ($checked == Shipping::SHIPPING_MAIL_SENT) {
  308.                     // 送信
  309.                     $qb
  310.                         ->andWhere('s.mail_send_date IS NOT NULL');
  311.                 }
  312.             }
  313.         }
  315.         // 送り状番号.
  316.         if (!empty($searchData['tracking_number'])) {
  317.             $qb
  318.                 ->andWhere('s.tracking_number = :tracking_number')
  319.                 ->setParameter('tracking_number', $searchData['tracking_number']);
  320.         }
  322.         // お届け予定日(Shipping.delivery_date)
  323.         if (!empty($searchData['shipping_delivery_datetime_start']) && $searchData['shipping_delivery_datetime_start']) {
  324.             $date = $searchData['shipping_delivery_datetime_start'];
  325.             $qb
  326.                 ->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
  327.                 ->setParameter('shipping_delivery_date_start', $date);
  328.         } elseif (!empty($searchData['shipping_delivery_date_start']) && $searchData['shipping_delivery_date_start']) {
  329.             $date = $searchData['shipping_delivery_date_start'];
  330.             $qb
  331.                 ->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
  332.                 ->setParameter('shipping_delivery_date_start', $date);
  333.         }
  335.         if (!empty($searchData['shipping_delivery_datetime_end']) && $searchData['shipping_delivery_datetime_end']) {
  336.             $date = $searchData['shipping_delivery_datetime_end'];
  337.             $qb
  338.                 ->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
  339.                 ->setParameter('shipping_delivery_date_end', $date);
  340.         } elseif (!empty($searchData['shipping_delivery_date_end']) && $searchData['shipping_delivery_date_end']) {
  341.             $date = clone $searchData['shipping_delivery_date_end'];
  342.             $date = $date
  343.                 ->modify('+1 days');
  344.             $qb
  345.                 ->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
  346.                 ->setParameter('shipping_delivery_date_end', $date);
  347.         }
  349.         // Order By
  350.         $qb->orderBy('o.update_date', 'DESC');
  351.         $qb->addorderBy('o.id', 'DESC');
  353.         return $this->queries->customize(QueryKey::ORDER_SEARCH_ADMIN, $qb, $searchData);
  354.     }
  356.     /**
  357.      * @param  \Eccube\Entity\Customer $Customer
  358.      *
  359.      * @return QueryBuilder
  360.      */
  361.     public function getQueryBuilderByCustomer(\Eccube\Entity\Customer $Customer)
  362.     {
  363.         $qb = $this->createQueryBuilder('o')
  364.             ->where('o.Customer = :Customer')
  365.             ->setParameter('Customer', $Customer);
  367.         // Order By
  368.         $qb->addOrderBy('o.id', 'DESC');
  370.         return $this->queries->customize(QueryKey::ORDER_SEARCH_BY_CUSTOMER, $qb, ['customer' => $Customer]);
  371.     }
  373.     /**
  374.      * ステータスごとの受注件数を取得する.
  375.      *
  376.      * @param integer $OrderStatusOrId
  377.      *
  378.      * @return int
  379.      *
  380.      * @throws \Doctrine\ORM\NoResultException
  381.      * @throws \Doctrine\ORM\NonUniqueResultException
  382.      */
  383.     public function countByOrderStatus($OrderStatusOrId)
  384.     {
  385.         return (int) $this->createQueryBuilder('o')
  386.             ->select('COALESCE(COUNT(o.id), 0)')
  387.             ->where('o.OrderStatus = :OrderStatus')
  388.             ->setParameter('OrderStatus', $OrderStatusOrId)
  389.             ->getQuery()
  390.             ->getSingleScalarResult();
  391.     }
  393.     /**
  394.      * 会員の購入金額, 購入回数, 初回購入日, 最終購入費を更新する
  395.      *
  396.      * @param Customer $Customer
  397.      * @param array $OrderStatuses
  398.      */
  399.     public function updateOrderSummary(Customer $Customer, array $OrderStatuses = [OrderStatus::NEW, OrderStatus::PAID, OrderStatus::DELIVERED, OrderStatus::IN_PROGRESS])
  400.     {
  401.         try {
  402.             $result = $this->createQueryBuilder('o')
  403.                 ->select('COUNT(o.id) AS buy_times, SUM(o.total) AS buy_total, MIN(o.id) AS first_order_id, MAX(o.id) AS last_order_id')
  404.                 ->where('o.Customer = :Customer')
  405.                 ->andWhere('o.OrderStatus in (:OrderStatuses)')
  406.                 ->setParameter('Customer', $Customer)
  407.                 ->setParameter('OrderStatuses', $OrderStatuses)
  408.                 ->groupBy('o.Customer')
  409.                 ->getQuery()
  410.                 ->getSingleResult();
  411.         } catch (NoResultException $e) {
  412.             // 受注データが存在しなければ初期化
  413.             $Customer->setFirstBuyDate(null);
  414.             $Customer->setLastBuyDate(null);
  415.             $Customer->setBuyTimes(0);
  416.             $Customer->setBuyTotal(0);
  418.             return;
  419.         }
  421.         $FirstOrder = $this->find(['id' => $result['first_order_id']]);
  422.         $LastOrder = $this->find(['id' => $result['last_order_id']]);
  424.         $Customer->setBuyTimes($result['buy_times']);
  425.         $Customer->setBuyTotal($result['buy_total']);
  426.         $Customer->setFirstBuyDate($FirstOrder->getOrderDate());
  427.         $Customer->setLastBuyDate($LastOrder->getOrderDate());
  428.     }
  429. }