src/Eccube/Repository/OrderRepository.php line 51

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 Doctrine\Persistence\ManagerRegistry as RegistryInterface;
  19. use Eccube\Doctrine\Query\Queries;
  20. use Eccube\Entity\Customer;
  21. use Eccube\Entity\Master\OrderStatus;
  22. use Eccube\Entity\Master\Sex;
  23. use Eccube\Entity\Order;
  24. use Eccube\Entity\Payment;
  25. use Eccube\Entity\Shipping;
  26. use Eccube\Util\StringUtil;
  28. /**
  29.  * OrderRepository
  30.  *
  31.  * This class was generated by the Doctrine ORM. Add your own custom
  32.  * repository methods below.
  33.  */
  34. class OrderRepository extends AbstractRepository
  35. {
  36.     /**
  37.      * @var Queries
  38.      */
  39.     protected $queries;
  41.     public const COLUMNS = [
  42.         'order' => 'o.name01', 'orderer' => 'o.id', 'shipping_id' => 's.id', 'purchase_product' => 'oi.product_name', 'quantity' => 'oi.quantity', 'payment_method' => 'o.payment_method', 'order_status' => 'o.OrderStatus', 'purchase_price' => 'o.total', 'shipping_status' => 's.shipping_date', 'tracking_number' => 's.tracking_number', 'delivery' => 's.name01',
  43.     ];
  45.     /**
  46.      * OrderRepository constructor.
  47.      *
  48.      * @param RegistryInterface $registry
  49.      * @param Queries $queries
  50.      */
  51.     public function __construct(RegistryInterface $registry, Queries $queries)
  52.     {
  53.         parent::__construct($registry, Order::class);
  54.         $this->queries = $queries;
  55.     }
  57.     /**
  58.      * @param int $orderId
  59.      * @param OrderStatus $Status
  60.      */
  61.     public function changeStatus($orderId, OrderStatus $Status)
  62.     {
  63.         $Order = $this
  64.             ->find($orderId)
  65.             ->setOrderStatus($Status)
  66.         ;
  68.         switch ($Status->getId()) {
  69.             case '6': // 入金済へ
  70.                 $Order->setPaymentDate(new \DateTime());
  71.                 break;
  72.         }
  74.         $em = $this->getEntityManager();
  75.         $em->persist($Order);
  76.         $em->flush();
  77.     }
  79.     /**
  80.      * @param array{
  81.      *         order_id?:string|int,
  82.      *         order_no?:string,
  83.      *         order_id_start?:string|int,
  84.      *         order_id_end?:string|int,
  85.      *         multi?:string|int|null,
  86.      *         status?:OrderStatus[]|int[],
  87.      *         company_name?:string,
  88.      *         name?:string,
  89.      *         kana?:string,
  90.      *         email?:string,
  91.      *         phone_number?:string,
  92.      *         sex?:Sex[],
  93.      *         payment?:Payment[],
  94.      *         order_datetime_start?:\DateTime,
  95.      *         order_datetime_end?:\DateTime,
  96.      *         order_date_start?:\DateTime,
  97.      *         order_date_end?:\DateTime,
  98.      *         payment_datetime_start?:\DateTime,
  99.      *         payment_datetime_end?:\DateTime,
  100.      *         payment_date_start?:\DateTime,
  101.      *         payment_date_end?:\DateTime,
  102.      *         update_datetime_start?:\DateTime,
  103.      *         update_datetime_end?:\DateTime,
  104.      *         update_date_start?:\DateTime,
  105.      *         update_date_end?:\DateTime,
  106.      *         payment_total_start?:string|int,
  107.      *         payment_total_end?:string|int,
  108.      *         payment_product_name?:string,
  109.      *         shipping_mail?:Shipping::SHIPPING_MAIL_UNSENT|Shipping::SHIPPING_MAIL_SENT,
  110.      *         tracking_number?:string,
  111.      *         shipping_delivery_datetime_start?:\DateTime,
  112.      *         shipping_delivery_datetime_end?:\DateTime,
  113.      *         shipping_delivery_date_start?:\DateTime,
  114.      *         shipping_delivery_date_end?:\DateTime,
  115.      *         sortkey?:string,
  116.      *         sorttype?:string
  117.      *     } $searchData
  118.      *
  119.      * @return QueryBuilder
  120.      */
  121.     public function getQueryBuilderBySearchDataForAdmin($searchData)
  122.     {
  123.         $qb = $this->createQueryBuilder('o')
  124.             ->select('o, s')
  125.             ->addSelect('oi', 'pref')
  126.             ->leftJoin('o.OrderItems', 'oi')
  127.             ->leftJoin('o.Pref', 'pref')
  128.             ->innerJoin('o.Shippings', 's');
  130.         // order_id_start
  131.         if (isset($searchData['order_id']) && StringUtil::isNotBlank($searchData['order_id'])) {
  132.             $qb
  133.                 ->andWhere('o.id = :order_id')
  134.                 ->setParameter('order_id', $searchData['order_id']);
  135.         }
  137.         // order_no
  138.         if (isset($searchData['order_no']) && StringUtil::isNotBlank($searchData['order_no'])) {
  139.             $qb
  140.                 ->andWhere('o.order_no = :order_no')
  141.                 ->setParameter('order_no', $searchData['order_no']);
  142.         }
  144.         // order_id_start
  145.         if (isset($searchData['order_id_start']) && StringUtil::isNotBlank($searchData['order_id_start'])) {
  146.             $qb
  147.                 ->andWhere('o.id >= :order_id_start')
  148.                 ->setParameter('order_id_start', $searchData['order_id_start']);
  149.         }
  150.         // multi
  151.         if (isset($searchData['multi']) && StringUtil::isNotBlank($searchData['multi'])) {
  152.             // スペース除去
  153.             $clean_key_multi = preg_replace('/\s+|[　]+/u', '', $searchData['multi']);
  154.             $multi = preg_match('/^\d{0,10}$/', $clean_key_multi) ? $clean_key_multi : null;
  155.             if ($multi && $multi > '2147483647' && $this->isPostgreSQL()) {
  156.                 $multi = null;
  157.             }
  158.             $qb
  159.                 ->andWhere('o.id = :multi OR CONCAT(o.name01, o.name02) LIKE :likemulti OR '.
  160.                     "CONCAT(COALESCE(o.kana01, ''), COALESCE(o.kana02, '')) LIKE :likemulti OR o.company_name LIKE :multi_company_name OR ".
  161.                     'o.order_no LIKE :likemulti OR o.email LIKE :likemulti OR o.phone_number LIKE :likemulti')
  162.                 ->setParameter('multi', $multi)
  163.                 ->setParameter('likemulti', '%'.$clean_key_multi.'%')
  164.                 ->setParameter('multi_company_name', '%'.$searchData['multi'].'%'); // 会社名はスペースを除去せず検索
  165.         }
  167.         // order_id_end
  168.         if (isset($searchData['order_id_end']) && StringUtil::isNotBlank($searchData['order_id_end'])) {
  169.             $qb
  170.                 ->andWhere('o.id <= :order_id_end')
  171.                 ->setParameter('order_id_end', $searchData['order_id_end']);
  172.         }
  174.         // status
  175.         $filterStatus = false;
  176.         if (!empty($searchData['status']) && count($searchData['status'])) {
  177.             $qb
  178.                 ->andWhere($qb->expr()->in('o.OrderStatus', ':status'))
  179.                 ->setParameter('status', $searchData['status']);
  180.             $filterStatus = true;
  181.         }
  183.         if (!$filterStatus) {
  184.             // 購入処理中, 決済処理中は検索対象から除外
  185.             $qb->andWhere($qb->expr()->notIn('o.OrderStatus', ':status'))
  186.                 ->setParameter('status', [OrderStatus::PROCESSING, OrderStatus::PENDING]);
  187.         }
  189.         // company_name
  190.         if (isset($searchData['company_name']) && StringUtil::isNotBlank($searchData['company_name'])) {
  191.             $qb
  192.                 ->andWhere('o.company_name LIKE :company_name')
  193.                 ->setParameter('company_name', '%'.$searchData['company_name'].'%');
  194.         }
  196.         // name
  197.         if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
  198.             $clean_name = preg_replace('/\s+|[　]+/u', '', $searchData['name']);
  199.             $qb
  200.                 ->andWhere('CONCAT(o.name01, o.name02) LIKE :name')
  201.                 ->setParameter('name', '%'.$clean_name.'%');
  202.         }
  204.         // kana
  205.         if (isset($searchData['kana']) && StringUtil::isNotBlank($searchData['kana'])) {
  206.             $clean_kana = preg_replace('/\s+|[　]+/u', '', $searchData['kana']);
  207.             $qb
  208.                 ->andWhere("CONCAT(COALESCE(o.kana01, ''), COALESCE(o.kana02, '')) LIKE :kana")
  209.                 ->setParameter('kana', '%'.$clean_kana.'%');
  210.         }
  212.         // email
  213.         if (isset($searchData['email']) && StringUtil::isNotBlank($searchData['email'])) {
  214.             $qb
  215.                 ->andWhere('o.email like :email')
  216.                 ->setParameter('email', '%'.$searchData['email'].'%');
  217.         }
  219.         // tel
  220.         if (isset($searchData['phone_number']) && StringUtil::isNotBlank($searchData['phone_number'])) {
  221.             $tel = preg_replace('/[^0-9]/', '', $searchData['phone_number']);
  222.             $qb
  223.                 ->andWhere('o.phone_number LIKE :phone_number')
  224.                 ->setParameter('phone_number', '%'.$tel.'%');
  225.         }
  227.         // sex
  228.         if (!empty($searchData['sex']) && count($searchData['sex']) > 0) {
  229.             $qb
  230.                 ->andWhere($qb->expr()->in('o.Sex', ':sex'))
  231.                 ->setParameter('sex', $searchData['sex']->toArray());
  232.         }
  234.         // payment
  235.         if (!empty($searchData['payment']) && count($searchData['payment'])) {
  236.             $payments = [];
  237.             foreach ($searchData['payment'] as $payment) {
  238.                 $payments[] = $payment->getId();
  239.             }
  240.             $qb
  241.                 ->leftJoin('o.Payment', 'p')
  242.                 ->andWhere($qb->expr()->in('p.id', ':payments'))
  243.                 ->setParameter('payments', $payments);
  244.         }
  246.         // oreder_date
  247.         if (!empty($searchData['order_datetime_start']) && $searchData['order_datetime_start']) {
  248.             $date = $searchData['order_datetime_start'];
  249.             $qb
  250.                 ->andWhere('o.order_date >= :order_date_start')
  251.                 ->setParameter('order_date_start', $date);
  252.         } elseif (!empty($searchData['order_date_start']) && $searchData['order_date_start']) {
  253.             $date = $searchData['order_date_start'];
  254.             $qb
  255.                 ->andWhere('o.order_date >= :order_date_start')
  256.                 ->setParameter('order_date_start', $date);
  257.         }
  259.         if (!empty($searchData['order_datetime_end']) && $searchData['order_datetime_end']) {
  260.             $date = $searchData['order_datetime_end'];
  261.             $qb
  262.                 ->andWhere('o.order_date < :order_date_end')
  263.                 ->setParameter('order_date_end', $date);
  264.         } elseif (!empty($searchData['order_date_end']) && $searchData['order_date_end']) {
  265.             $date = clone $searchData['order_date_end'];
  266.             $date = $date
  267.                 ->modify('+1 days');
  268.             $qb
  269.                 ->andWhere('o.order_date < :order_date_end')
  270.                 ->setParameter('order_date_end', $date);
  271.         }
  273.         // payment_date
  274.         if (!empty($searchData['payment_datetime_start']) && $searchData['payment_datetime_start']) {
  275.             $date = $searchData['payment_datetime_start'];
  276.             $qb
  277.                 ->andWhere('o.payment_date >= :payment_date_start')
  278.                 ->setParameter('payment_date_start', $date);
  279.         } elseif (!empty($searchData['payment_date_start']) && $searchData['payment_date_start']) {
  280.             $date = $searchData['payment_date_start'];
  281.             $qb
  282.                 ->andWhere('o.payment_date >= :payment_date_start')
  283.                 ->setParameter('payment_date_start', $date);
  284.         }
  286.         if (!empty($searchData['payment_datetime_end']) && $searchData['payment_datetime_end']) {
  287.             $date = $searchData['payment_datetime_end'];
  288.             $qb
  289.                 ->andWhere('o.payment_date < :payment_date_end')
  290.                 ->setParameter('payment_date_end', $date);
  291.         } elseif (!empty($searchData['payment_date_end']) && $searchData['payment_date_end']) {
  292.             $date = clone $searchData['payment_date_end'];
  293.             $date = $date
  294.                 ->modify('+1 days');
  295.             $qb
  296.                 ->andWhere('o.payment_date < :payment_date_end')
  297.                 ->setParameter('payment_date_end', $date);
  298.         }
  300.         // update_date
  301.         if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
  302.             $date = $searchData['update_datetime_start'];
  303.             $qb
  304.                 ->andWhere('o.update_date >= :update_date_start')
  305.                 ->setParameter('update_date_start', $date);
  306.         } elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
  307.             $date = $searchData['update_date_start'];
  308.             $qb
  309.                 ->andWhere('o.update_date >= :update_date_start')
  310.                 ->setParameter('update_date_start', $date);
  311.         }
  313.         if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
  314.             $date = $searchData['update_datetime_end'];
  315.             $qb
  316.                 ->andWhere('o.update_date < :update_date_end')
  317.                 ->setParameter('update_date_end', $date);
  318.         } elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
  319.             $date = clone $searchData['update_date_end'];
  320.             $date = $date
  321.                 ->modify('+1 days');
  322.             $qb
  323.                 ->andWhere('o.update_date < :update_date_end')
  324.                 ->setParameter('update_date_end', $date);
  325.         }
  327.         // payment_total
  328.         if (isset($searchData['payment_total_start']) && StringUtil::isNotBlank($searchData['payment_total_start'])) {
  329.             $qb
  330.                 ->andWhere('o.payment_total >= :payment_total_start')
  331.                 ->setParameter('payment_total_start', $searchData['payment_total_start']);
  332.         }
  333.         if (isset($searchData['payment_total_end']) && StringUtil::isNotBlank($searchData['payment_total_end'])) {
  334.             $qb
  335.                 ->andWhere('o.payment_total <= :payment_total_end')
  336.                 ->setParameter('payment_total_end', $searchData['payment_total_end']);
  337.         }
  339.         // buy_product_name
  340.         if (isset($searchData['buy_product_name']) && StringUtil::isNotBlank($searchData['buy_product_name'])) {
  341.             $qb
  342.                 ->andWhere('oi.product_name LIKE :buy_product_name')
  343.                 ->setParameter('buy_product_name', '%'.$searchData['buy_product_name'].'%');
  344.         }
  346.         // apparel
  347.         if (!empty($searchData['apparel']) && $searchData['apparel']) {
  348.             $qb
  349.                 ->innerJoin('oi.ProductClass', 'pc')
  350.                 ->innerJoin('pc.Product', 'p')
  351.                 ->andWhere('p.Apparel = :apparel')
  352.                 ->setParameter('apparel', $searchData['apparel']);
  353.         }
  355.         // target_student (対象生徒)
  356.         if (!empty($searchData['target_student']) && count($searchData['target_student']) > 0) {
  357.             $qb
  358.                 ->leftJoin('Customize\Entity\School', 'sch', 'WITH', 'o.school_id = sch.school_id')
  359.                 ->andWhere($qb->expr()->in('sch.target_student', ':target_student'))
  360.                 ->setParameter('target_student', $searchData['target_student']);
  361.         }
  363.         // 発送メール送信/未送信.
  364.         if (isset($searchData['shipping_mail']) && $count = count($searchData['shipping_mail'])) {
  365.             // 送信済/未送信両方にチェックされている場合は検索条件に追加しない
  366.             if ($count < 2) {
  367.                 $checked = current($searchData['shipping_mail']);
  368.                 if ($checked == Shipping::SHIPPING_MAIL_UNSENT) {
  369.                     // 未送信
  370.                     $qb
  371.                         ->andWhere('s.mail_send_date IS NULL');
  372.                 } elseif ($checked == Shipping::SHIPPING_MAIL_SENT) {
  373.                     // 送信
  374.                     $qb
  375.                         ->andWhere('s.mail_send_date IS NOT NULL');
  376.                 }
  377.             }
  378.         }
  380.         // 送り状番号.
  381.         if (!empty($searchData['tracking_number'])) {
  382.             $qb
  383.                 ->andWhere('s.tracking_number = :tracking_number')
  384.                 ->setParameter('tracking_number', $searchData['tracking_number']);
  385.         }
  387.         // お届け予定日(Shipping.delivery_date)
  388.         if (!empty($searchData['shipping_delivery_datetime_start']) && $searchData['shipping_delivery_datetime_start']) {
  389.             $date = $searchData['shipping_delivery_datetime_start'];
  390.             $qb
  391.                 ->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
  392.                 ->setParameter('shipping_delivery_date_start', $date);
  393.         } elseif (!empty($searchData['shipping_delivery_date_start']) && $searchData['shipping_delivery_date_start']) {
  394.             $date = $searchData['shipping_delivery_date_start'];
  395.             $qb
  396.                 ->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
  397.                 ->setParameter('shipping_delivery_date_start', $date);
  398.         }
  400.         if (!empty($searchData['shipping_delivery_datetime_end']) && $searchData['shipping_delivery_datetime_end']) {
  401.             $date = $searchData['shipping_delivery_datetime_end'];
  402.             $qb
  403.                 ->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
  404.                 ->setParameter('shipping_delivery_date_end', $date);
  405.         } elseif (!empty($searchData['shipping_delivery_date_end']) && $searchData['shipping_delivery_date_end']) {
  406.             $date = clone $searchData['shipping_delivery_date_end'];
  407.             $date = $date
  408.                 ->modify('+1 days');
  409.             $qb
  410.                 ->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
  411.                 ->setParameter('shipping_delivery_date_end', $date);
  412.         }
  414.         // Order By
  415.         if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
  416.             $sortOrder = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' : 'DESC';
  418.             $qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortOrder);
  419.             $qb->addOrderBy('o.update_date', 'DESC');
  420.             $qb->addOrderBy('o.id', 'DESC');
  421.         } else {
  422.             $qb->orderBy('o.update_date', 'DESC');
  423.             $qb->addorderBy('o.id', 'DESC');
  424.         }
  426.         return $this->queries->customize(QueryKey::ORDER_SEARCH_ADMIN, $qb, $searchData);
  427.     }
  429.     /**
  430.      * @param  \Eccube\Entity\Customer $Customer
  431.      *
  432.      * @return QueryBuilder
  433.      */
  434.     public function getQueryBuilderByCustomer(Customer $Customer)
  435.     {
  436.         $qb = $this->createQueryBuilder('o')
  437.             ->where('o.Customer = :Customer')
  438.             ->setParameter('Customer', $Customer);
  440.         // Order By
  441.         $qb->addOrderBy('o.id', 'DESC');
  442.         return $this->queries->customize(QueryKey::ORDER_SEARCH_BY_CUSTOMER, $qb, ['customer' => $Customer]);
  443.     }
  445.     /**
  446.      * ステータスごとの受注件数を取得する.
  447.      *
  448.      * @param integer $OrderStatusOrId
  449.      *
  450.      * @return int
  451.      *
  452.      * @throws \Doctrine\ORM\NoResultException
  453.      * @throws \Doctrine\ORM\NonUniqueResultException
  454.      */
  455.     public function countByOrderStatus($OrderStatusOrId)
  456.     {
  457.         return (int) $this->createQueryBuilder('o')
  458.             ->select('COALESCE(COUNT(o.id), 0)')
  459.             ->where('o.OrderStatus = :OrderStatus')
  460.             ->setParameter('OrderStatus', $OrderStatusOrId)
  461.             ->getQuery()
  462.             ->getSingleScalarResult();
  463.     }
  465.     /**
  466.      * 会員の購入金額, 購入回数, 初回購入日, 最終購入費を更新する
  467.      *
  468.      * @param Customer $Customer
  469.      * @param array $OrderStatuses
  470.      */
  471.     public function updateOrderSummary(Customer $Customer, array $OrderStatuses = [OrderStatus::NEW, OrderStatus::PAID, OrderStatus::DELIVERED, OrderStatus::IN_PROGRESS])
  472.     {
  473.         try {
  474.             $result = $this->createQueryBuilder('o')
  475.                 ->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')
  476.                 ->where('o.Customer = :Customer')
  477.                 ->andWhere('o.OrderStatus in (:OrderStatuses)')
  478.                 ->setParameter('Customer', $Customer)
  479.                 ->setParameter('OrderStatuses', $OrderStatuses)
  480.                 ->groupBy('o.Customer')
  481.                 ->getQuery()
  482.                 ->getSingleResult();
  483.         } catch (NoResultException $e) {
  484.             // 受注データが存在しなければ初期化
  485.             $Customer->setFirstBuyDate(null);
  486.             $Customer->setLastBuyDate(null);
  487.             $Customer->setBuyTimes(0);
  488.             $Customer->setBuyTotal(0);
  490.             return;
  491.         }
  493.         $FirstOrder = $this->find(['id' => $result['first_order_id']]);
  494.         $LastOrder = $this->find(['id' => $result['last_order_id']]);
  496.         $Customer->setBuyTimes($result['buy_times']);
  497.         $Customer->setBuyTotal($result['buy_total']);
  498.         $Customer->setFirstBuyDate($FirstOrder->getOrderDate());
  499.         $Customer->setLastBuyDate($LastOrder->getOrderDate());
  500.     }
  502.     /**
  503.      * 特定の商品に対する顧客の過去の購入数量を取得
  504.      *
  505.      * @param Customer|null $Customer
  506.      * @param int $productId
  507.      * @param array $OrderStatuses
  508.      * @return int
  509.      */
  510.     public function getPurchasedQuantityByProduct($Customer, $productId, array $OrderStatuses = [OrderStatus::NEW, OrderStatus::PAID, OrderStatus::DELIVERED, OrderStatus::IN_PROGRESS])
  511.     {
  512.         if (!$Customer) {
  513.             return 0;
  514.         }
  516.         try {
  517.             $qb = $this->createQueryBuilder('o')
  518.                 ->select('SUM(oi.quantity) as total_quantity')
  519.                 ->innerJoin('o.OrderItems', 'oi')
  520.                 ->innerJoin('oi.ProductClass', 'pc')
  521.                 ->innerJoin('pc.Product', 'p')
  522.                 ->where('o.Customer = :Customer')
  523.                 ->andWhere('p.id = :productId')
  524.                 ->andWhere('o.OrderStatus in (:OrderStatuses)')
  525.                 ->setParameter('Customer', $Customer)
  526.                 ->setParameter('productId', $productId)
  527.                 ->setParameter('OrderStatuses', $OrderStatuses);
  529.             $result = $qb->getQuery()->getSingleScalarResult();
  531.             return (int)($result ?? 0);
  532.         } catch (NoResultException $e) {
  533.             return 0;
  534.         } catch (\Exception $e) {
  535.             return 0;
  536.         }
  537.     }
  538. }