管理画面集計周り調整

[yuh2]

管理画面のHOMEの売上表示部分で
$dql = 'SELECT SUBSTRING(CONCAT(o.order_date, \'\'), 1, 7) AS order_month, SUM(o.payment_total) AS order_amount, COUNT(o) AS order_count FROM Eccube\Entity\Order o WHERE o.del_flg = 0 AND o.OrderStatus NOT IN (:excludes) AND SUBSTRING(CONCAT(o.order_date, \'\'), 1, 7) = SUBSTRING(:targetDate, 1, 7) GROUP BY order_month';
こんな感じで書かれている部分を
order_dateにINDEXを貼って
その期間のMAXのorder_idとMINのorder_idを取得して
t1.order_id BETWEEN :min_order_id AND :max_order_id
こんな感じに抽出するように変えませんか？

具体的にはAdminController.phpを下記のような感じに。

    public function index(Application $app, Request $request)
    {
        // install.phpのチェック.
        if (isset($app['config']['eccube_install']) && $app['config']['eccube_install'] == 1) {
            $file = $app['config']['root_dir'] . '/html/install.php';
            if (file_exists($file)) {
                $app->addWarning('admin.install.warning', 'admin');
            }
        }

        // 受注マスター検索用フォーム
        $searchOrderForm = $app['form.factory']
            ->createBuilder('admin_search_order')
            ->getForm();
        // 商品マスター検索用フォーム
        $searchProductForm = $app['form.factory']
            ->createBuilder('admin_search_product')
            ->getForm();
        // 会員マスター検索用フォーム
        $searchCustomerForm = $app['form.factory']
            ->createBuilder('admin_search_customer')
            ->getForm();

        /**
         * 受注状況.
         */
        $excludes = array();
        $excludes[] = $app['config']['order_pending'];
        $excludes[] = $app['config']['order_processing'];
        $excludes[] = $app['config']['order_cancel'];
        $excludes[] = $app['config']['order_deliv'];

        // 受注ステータスごとの受注件数.
        $Orders = $this->getOrderEachStatus($app['orm.em'], $excludes);
        // 受注ステータスの一覧.
        $OrderStatuses = $this->findOrderStatus($app['orm.em'], $excludes);

        /**
         * 売り上げ状況
         */
        $excludes = array();
        $excludes[] = $app['config']['order_processing'];
        $excludes[] = $app['config']['order_cancel'];
        $excludes[] = $app['config']['order_pending'];


        //
        $resultdata = $this->getOrderLine($app['orm.em'],$excludes);


        // 今日の売上/件数
        $salesToday = $resultdata['today'];
        // 昨日の売上/件数
        $salesYesterday = $resultdata['yesterday'];
        // 今月の売上/件数
        $salesThisMonth = $resultdata['thismonth'];

        /**
         * ショップ状況
         */
        // 在庫切れ商品数
        $countNonStockProducts = $this->countNonStockProducts($app['orm.em']);
        // 本会員数
        $countCustomers = $this->countCustomers($app['orm.em']);

        return $app->render('index.twig', array(
            'searchOrderForm' => $searchOrderForm->createView(),
            'searchProductForm' => $searchProductForm->createView(),
            'searchCustomerForm' => $searchCustomerForm->createView(),
            'Orders' => $Orders,
            'OrderStatuses' => $OrderStatuses,
            'salesThisMonth' => $salesThisMonth,
            'salesToday' => $salesToday,
            'salesYesterday' => $salesYesterday,
            'countNonStockProducts' => $countNonStockProducts,
            'countCustomers' => $countCustomers,
        ));
    }
    protected function getOrderLine($em, array $excludes)
    {
        $rsm = new ResultSetMapping();;
        $rsm->addScalarResult('max_order_id', 'max_order_id');
        $rsm->addScalarResult('min_order_id', 'min_order_id');

        $sql = '
        SELECT
            MAX(order_id ) as max_order_id,
            MIN(order_id ) as min_order_id
        FROM
            dtb_order
        WHERE
            order_date BETWEEN ? AND ?
        ';
//        今日の売上高 / 売上件数
        $settime = time();
        $query = $em->createNativeQuery($sql, $rsm);
        $query->setParameter(1, date('Y-m-d 00:00:00',$settime));
        $query->setParameter(2, date('Y-m-d 23:59:59',$settime));
        $result = $query->getResult();
        if(is_null($result[0]['max_order_id'])){
            $result[0]['max_order_id'] = 0;
            $result[0]['min_order_id'] = 0;
        }
        $arrOrderResult = array();
        $arrOrderResult['today'] = $this->getSalesFromOrderId($em, $result[0]['max_order_id'], $result[0]['min_order_id'],$excludes);

//        昨日の売上高 / 売上件数
        $query = $em->createNativeQuery($sql, $rsm);
        $query->setParameter(1, date('Y-m-d 00:00:00',$settime - 86400));
        $query->setParameter(2, date('Y-m-d 23:59:59',$settime - 86400));
        $result = $query->getResult();
        if(is_null($result[0]['max_order_id'])){
            $result[0]['max_order_id'] = 0;
            $result[0]['min_order_id'] = 0;
        }
        $arrOrderResult['yesterday'] = $this->getSalesFromOrderId($em, $result[0]['max_order_id'], $result[0]['min_order_id'],$excludes);
//        今月の売上高 / 売上件数

        $query = $em->createNativeQuery($sql, $rsm);
        $query->setParameter(1, date('Y-m-1 00:00:00',$settime));
        $query->setParameter(2, date('Y-m-t 23:59:59',$settime));
        $result = $query->getResult();
        if(is_null($result[0]['max_order_id'])){
            $result[0]['max_order_id'] = 0;
            $result[0]['min_order_id'] = 0;
        }
        $arrOrderResult['thismonth'] = $this->getSalesFromOrderId($em, $result[0]['max_order_id'], $result[0]['min_order_id'],$excludes);
        return $arrOrderResult;
    }


    protected function getSalesFromOrderId($em, $max_order_id, $min_order_id, array $excludes)
    {
        // concat... for pgsql
        // http://stackoverflow.com/questions/1091924/substr-does-not-work-with-datatype-timestamp-in-postgres-8-3
        $sql = '
        SELECT
            SUBSTRING(CONCAT(t1.create_date, \'\'), 1, 10) AS order_day,
            SUM(t1.payment_total) AS order_amount,
            COUNT(t1.order_id) AS order_count
        FROM
            dtb_order t1
        WHERE
            t1.status NOT IN (:excludes) AND 
            t1.order_id BETWEEN :min_order_id AND :max_order_id';
        $rsm = new ResultSetMapping();;
        $rsm->addScalarResult('order_day', 'order_day');
        $rsm->addScalarResult('order_amount', 'order_amount');
        $rsm->addScalarResult('order_count', 'order_count');
        $query = $em->createNativeQuery($sql, $rsm);
        $query->setParameters(
        array(':excludes' => $excludes,
            ':min_order_id' => $min_order_id,
            ':max_order_id' => $max_order_id
        )
        );
        $result = $query->getResult();
        return $result[0];
    }

[nanasess]

@yuh2
大変お手数かと思いますが、 Pull Request (or パッチ)と、何が改善されるかを書いていただけると助かります。
後々の修正の経緯など追いやすくなりますので。

[yuh2]

受注件数が増えた場合に管理画面にログインできなくなる不具合の改善です。

[nanasess]

検証してみましたが、日付跨ぎの受注があると正常に算出できないようですので、再考します。

テストケースはこちら
master...nanasess:pull/1166