source: branches/version-2_12-multilang/data/class/pages/admin/total/LC_Page_Admin_Total.php @ 22408

<?php
/*
 * This file is part of EC-CUBE
 *
 * Copyright(c) 2000-2012 LOCKON CO.,LTD. All Rights Reserved.
 *
 * http://www.lockon.co.jp/
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
// {{{ requires
require_once CLASS_EX_REALDIR . 'page_extends/admin/LC_Page_Admin_Ex.php';

/**
 * 売上集計 のページクラス.
 *
 * @package Page
 * @author LOCKON CO.,LTD.
 * @version $Id$
 */
class LC_Page_Admin_Total extends LC_Page_Admin_Ex {

    // }}}
    // {{{ functions

    /**
     * Page を初期化する.
     *
     * @return void
     */
    function init() {
        parent::init();
        // GDライブラリのインストール判定
        $this->install_GD = function_exists('gd_info') ? true : false;
        $this->tpl_mainpage         = 'total/index.tpl';
        $this->tpl_graphsubtitle    = 'total/subtitle.tpl';
        $this->tpl_titleimage       = ROOT_URLPATH.'img/title/title_sale.jpg';
        $this->tpl_maintitle = $this->tpl_maintitle = t('TPL_MAINTITLE_010');
        $this->tpl_mainno           = 'total';

        $masterData                 = new SC_DB_MasterData_Ex();
        $this->arrWDAY              = $masterData->getMasterData('mtb_wday');
        $this->arrSex               = $masterData->getMasterData('mtb_sex');
        $this->arrJob               = $masterData->getMasterData('mtb_job');

        // 登録・更新日検索用
        $objDate                    = new SC_Date_Ex();
        $objDate->setStartYear(RELEASE_YEAR);
        $objDate->setEndYear(DATE('Y'));
        $this->arrYear              = $objDate->getYear();
        $this->arrMonth             = $objDate->getMonth();
        $this->arrDay               = $objDate->getDay();

        // ページタイトル todo あとでなおす
        $this->arrTitle['']         = t('LC_Page_Admin_Total_001');
        $this->arrTitle['term']     = t('LC_Page_Admin_Total_002');
        $this->arrTitle['products'] = t('LC_Page_Admin_Total_003');
        $this->arrTitle['age']      = t('LC_Page_Admin_Total_004');
        $this->arrTitle['job']      = t('LC_Page_Admin_Total_005');
        $this->arrTitle['member']   = t('LC_Page_Admin_Total_006');

        // 月度集計のkey名
        $this->arrSearchForm1       = array('search_startyear_m', 'search_startmonth_m');

        // 期間別集計のkey名
        $this->arrSearchForm2 = array(
            'search_startyear',
            'search_startmonth',
            'search_startday',
            'search_endyear',
            'search_endmonth',
            'search_endday',
        );
    }

    /**
     * Page のプロセス.
     *
     * @return void
     */
    function process() {
        $this->action();
        $this->sendResponse();
    }

    /**
     * Page のアクション.
     *
     * @return void
     */
    function action() {

        if (isset($_GET['draw_image']) && $_GET['draw_image'] != '') {
            define('DRAW_IMAGE' , true);
        } else {
            define('DRAW_IMAGE' , false);
        }

        // パラメーター管理クラス
        $objFormParam = new SC_FormParam_Ex();
        // パラメーター情報の初期化
        $this->lfInitParam($objFormParam);
        $objFormParam->setParam($_REQUEST);

        // 検索ワードの引き継ぎ
        $this->arrHidden = $objFormParam->getSearchArray();

        switch ($this->getMode()) {
            case 'csv':
            case 'search':

                $this->arrErr = $this->lfCheckError($objFormParam);
                if (empty($this->arrErr)) {

                    // 日付
                    list($sdate, $edate) = $this->lfSetStartEndDate($objFormParam);

                    // ページ
                    $page = ($objFormParam->getValue('page')) ? $objFormParam->getValue('page') : 'term';

                    // 集計種類
                    $type = ($objFormParam->getValue('type')) ? $objFormParam->getValue('type'): 'all';

                    $this->tpl_page_type = 'total/page_'. $page .'.tpl';
                    // FIXME 可読性が低いので call_user_func_array を使わない (またはメソッド名を1つの定数値とする) 実装に。
                    list($this->arrResults, $this->tpl_image) = call_user_func_array(array($this, 'lfGetOrder'.$page),
                                                                                     array($type, $sdate, $edate));
                    if ($this->getMode() == 'csv') {
                        // CSV出力タイトル行の取得
                        list($arrTitleCol, $arrDataCol) = $this->lfGetCSVColum($page);
                        $head = SC_Utils_Ex::sfGetCSVList($arrTitleCol);
                        $data = $this->lfGetDataColCSV($this->arrResults, $arrDataCol);

                        // CSVを送信する。
                        list($fime_name, $data) = SC_Utils_Ex::sfGetCSVData($head.$data);

                        $this->sendResponseCSV($fime_name, $data);
                        SC_Response_Ex::actionExit();
                    }
                }
                break;
            default:
                break;
        }

        // 画面宣しても日付が保存される
        $_SESSION           = $this->lfSaveDateSession($_SESSION, $this->arrHidden);
        $objFormParam->setParam($_SESSION['total']);
        // 入力値の取得
        $this->arrForm      = $objFormParam->getFormParamList();
        $this->tpl_subtitle = $this->arrTitle[$objFormParam->getValue('page')];

    }

    /**
     * デストラクタ.
     *
     * @return void
     */
    function destroy() {
        parent::destroy();
    }

    /* デフォルト値の取得 */
    function lfGetDateDefault() {
        $year = date('Y');
        $month = date('m');
        $day = date('d');

        $list = isset($_SESSION['total']) ? $_SESSION['total'] : '';

        // セッション情報に開始月度が保存されていない。
        if (empty($_SESSION['total']['startyear_m'])) {
            $list['startyear_m'] = $year;
            $list['startmonth_m'] = $month;
        }

        // セッション情報に開始日付、終了日付が保存されていない。
        if (empty($_SESSION['total']['startyear']) && empty($_SESSION['total']['endyear'])) {
            $list['startyear'] = $year;
            $list['startmonth'] = $month;
            $list['startday'] = $day;
            $list['endyear'] = $year;
            $list['endmonth'] = $month;
            $list['endday'] = $day;
        }

        return $list;
    }

    /* パラメーター情報の初期化 */
    function lfInitParam(&$objFormParam) {
        // デフォルト値の取得
        $arrList = $this->lfGetDateDefault();

        // 月度集計
        $objFormParam->addParam(t('PARAM_LABEL_MONTHLY'), 'search_startyear_m', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['startyear_m']);
        $objFormParam->addParam(t('PARAM_LABEL_MONTHLY'), 'search_startmonth_m', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['startmonth_m']);
        // 期間集計
        $objFormParam->addParam(t('PARAM_LABEL_START_DAY'), 'search_startyear', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['startyear']);
        $objFormParam->addParam(t('PARAM_LABEL_START_DAY'), 'search_startmonth', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['startmonth']);
        $objFormParam->addParam(t('PARAM_LABEL_START_DAY'), 'search_startday', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['startday']);
        $objFormParam->addParam(t('PARAM_LABEL_END_DAY'), 'search_endyear', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['endyear']);
        $objFormParam->addParam(t('PARAM_LABEL_END_DAY'), 'search_endmonth', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['endmonth']);
        $objFormParam->addParam(t('PARAM_LABEL_END_DAY'), 'search_endday', INT_LEN, 'n', array('MAX_LENGTH_CHECK', 'NUM_CHECK'), $arrList['endday']);

        // hiddenデータの取得用
        $objFormParam->addParam('', 'page');
        $objFormParam->addParam('', 'type');
        $objFormParam->addParam('', 'mode');
        $objFormParam->addParam('', 'search_form');
    }

    /* 入力内容のチェック */
    function lfCheckError(&$objFormParam) {

        $objFormParam->convParam();
        $objErr         = new SC_CheckError_Ex($objFormParam->getHashArray());
        $objErr->arrErr = $objFormParam->checkError();

        // 特殊項目チェック

        // 月度集計
        if ($objFormParam->getValue('search_form') == 1) {
            $objErr->doFunc(array(t('PARAM_LABEL_MONTHLY'), 'search_startyear_m', 'search_startmonth_m'), array('FULL_EXIST_CHECK'));
        }

        // 期間集計
        if ($objFormParam->getValue('search_form') == 2) {
            $objErr->doFunc(array(t('PARAM_LABEL_PERIOD_1'), 'search_startyear', 'search_startmonth', 'search_startday'), array('FULL_EXIST_CHECK'));
            $objErr->doFunc(array(t('PARAM_LABEL_PERIOD_2'), 'search_endyear', 'search_endmonth', 'search_endday'), array('FULL_EXIST_CHECK'));
            $objErr->doFunc(array(t('PARAM_LABEL_PERIOD_1'), 'search_startyear', 'search_startmonth', 'search_startday'), array('CHECK_DATE'));
            $objErr->doFunc(array(t('PARAM_LABEL_PERIOD_2'), 'search_endyear', 'search_endmonth', 'search_endday'), array('CHECK_DATE'));
            $objErr->doFunc(array(t('PARAM_LABEL_PERIOD_1'), t('PARAM_LABEL_PERIOD_2'), 'search_startyear', 'search_startmonth', 'search_startday', 'search_endyear', 'search_endmonth', 'search_endday'), array('CHECK_SET_TERM'));
        }
        $objErr->doFunc(array(t('PARAM_LABEL_MONTHLY'), 'search_startyear_m', 'search_startmonth_m'), array('ALL_EXIST_CHECK'));
        $objErr->doFunc(array(t('PARAM_LABEL_START_DAY'), 'search_startyear', 'search_startmonth', 'search_startday'), array('CHECK_DATE'));
        $objErr->doFunc(array(t('PARAM_LABEL_END_DAY'), 'search_endyear', 'search_endmonth', 'search_endday'), array('CHECK_DATE'));
        $objErr->doFunc(array(t('PARAM_LABEL_START_DAY'), t('PARAM_LABEL_END_DAY'), 'search_startyear', 'search_startmonth', 'search_startday', 'search_endyear', 'search_endmonth', 'search_endday'), array('CHECK_SET_TERM'));
        return $objErr->arrErr;
    }

    /* サブナビを移動しても日付が残るようにセッションに入力期間を記録する */
    function lfSaveDateSession($session, $arrForm) {

        // session の初期化をする
        if (!isset($session['total'])) {
            $session['total'] = $this->lfGetDateInit();
        }

        if (!empty($arrForm)) {
            $session['total'] = array_merge($session['total'], $arrForm);
        }

        return $session;
    }

    /* 日付の初期値 */
    function lfGetDateInit() {
        $search_startyear_m     = $search_startyear  = $search_endyear  = date('Y');
        $search_startmonth_m    = $search_startmonth = $search_endmonth = date('m');
        $search_startday        = $search_endday     = date('d');

        return compact($this->arrSearchForm1, $this->arrSearchForm2);
    }

    /* フォームで入力された日付を適切な形にする */
    function lfSetStartEndDate(&$objFormParam) {
        $arrRet = $objFormParam->getHashArray();

        // 月度集計
        if ($arrRet['search_form'] == 1) {
            list($sdate, $edate) = SC_Utils_Ex::sfTermMonth($arrRet['search_startyear_m'],
                                                            $arrRet['search_startmonth_m'],
                                                            CLOSE_DAY);
        }
        // 期間集計
        elseif ($arrRet['search_form'] == 2) {
            $sdate = $arrRet['search_startyear'] . '/' . $arrRet['search_startmonth'] . '/' . $arrRet['search_startday'];
            $edate = $arrRet['search_endyear'] . '/' . $arrRet['search_endmonth'] . '/' . $arrRet['search_endday'];
        }

        return array($sdate, $edate);
    }

    /* 折れ線グラフの作成 */
    function lfGetGraphLine($arrResults, $keyname, $type, $xtitle, $ytitle, $sdate, $edate, $xincline) {

        $ret_path = '';

        // 結果が0行以上ある場合のみグラフを生成する。
        if (count($arrResults) > 0 && $this->install_GD) {

            // グラフの生成
            $arrList = SC_Utils_Ex::sfArrKeyValue($arrResults, $keyname, 'total');

            // 一時ファイル名の取得
            $pngname = $this->lfGetGraphPng($type);

            $path = GRAPH_REALDIR . $pngname;

            // ラベル表示インターバルを求める
            $interval = intval(count($arrList) / 20);
            if ($interval < 1) {
                $interval = 1;
            }
            $objGraphLine = new SC_Graph_Line();

            // 値のセット
            $objGraphLine->setData($arrList);
            $objGraphLine->setXLabel(array_keys($arrList));

            // ラベル回転(日本語不可)
            if ($xincline == true) {
                $objGraphLine->setXLabelAngle(45);
            }

            // タイトルセット
            $objGraphLine->setXTitle($xtitle);
            $objGraphLine->setYTitle($ytitle);

            // メインタイトル作成
            list($sy, $sm, $sd) = preg_split('|[/ ]|' , $sdate);
            list($ey, $em, $ed) = preg_split('|[/ ]|' , $edate);
            $start_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $sy, 'T_FIELD2' => $sm, 'T_FIELD3' => $sd));
            $end_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $ey, 'T_FIELD2' => $em, 'T_FIELD3' => $ed));
            $objGraphLine->drawTitle(t('LC_Page_Admin_Total_008', array('T_FIELD1' => $start_date, 'T_FIELD2' => $end_date)));

            // グラフ描画
            $objGraphLine->drawGraph();

            // グラフの出力
            if (DRAW_IMAGE) {
                $objGraphLine->outputGraph();
                SC_Response_Ex::actionExit();
            }

            // ファイルパスを返す
            $ret_path = GRAPH_URLPATH . $pngname;
        }
        return $ret_path;
    }

    // 円グラフの作成
    function lfGetGraphPie($arrResults, $keyname, $type, $title = '', $sdate = '', $edate = '') {

        $ret_path = '';
        // 結果が0行以上ある場合のみグラフを生成する。
        if (count($arrResults) > 0 && $this->install_GD) {
            // グラフの生成
            $arrList = SC_Utils_Ex::sfArrKeyValue($arrResults, $keyname,
                                                  'total', GRAPH_PIE_MAX,
                                                  GRAPH_LABEL_MAX);

            // 一時ファイル名の取得
            $pngname = $this->lfGetGraphPng($type);
            $path = GRAPH_REALDIR . $pngname;

            $objGraphPie = new SC_Graph_Pie();

            // データをセットする
            $objGraphPie->setData($arrList);
            // 凡例をセットする
            $objGraphPie->setLegend(array_keys($arrList));

            // メインタイトル作成
            list($sy, $sm, $sd) = preg_split('|[/ ]|' , $sdate);
            list($ey, $em, $ed) = preg_split('|[/ ]|' , $edate);
            $start_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $sy, 'T_FIELD2' => $sm, 'T_FIELD3' => $sd));
            $end_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $ey, 'T_FIELD2' => $em, 'T_FIELD3' => $ed));
            
            $objGraphPie->drawTitle(t('LC_Page_Admin_Total_008', array('T_FIELD1' => $start_date, 'T_FIELD2' => $end_date)));

            // 円グラフ描画
            $objGraphPie->drawGraph();

            // グラフの出力
            if (DRAW_IMAGE) {
                $objGraphPie->outputGraph();
                SC_Response_Ex::actionExit();
            }

            // ファイルパスを返す
            $ret_path = GRAPH_URLPATH . $pngname;
        }
        return $ret_path;
    }

    // 棒グラフの作成
    function lfGetGraphBar($arrResults, $keyname, $type, $xtitle, $ytitle, $sdate, $edate) {
        $ret_path = '';

        // 結果が0行以上ある場合のみグラフを生成する。
        if (count($arrResults) > 0 && $this->install_GD) {
            // グラフの生成
            $arrList = SC_Utils_Ex::sfArrKeyValue($arrResults, $keyname, 'total', GRAPH_PIE_MAX, GRAPH_LABEL_MAX);

            // 一時ファイル名の取得
            $pngname = $this->lfGetGraphPng($type);
            $path = GRAPH_REALDIR . $pngname;

            $objGraphBar = new SC_Graph_Bar();

            foreach ($arrList as $key => $value) {
                $arrKey[] = mb_ereg_replace('～', '-', $key);
            }

            // グラフ描画
            $objGraphBar->setXLabel($arrKey);
            $objGraphBar->setXTitle($xtitle);
            $objGraphBar->setYTitle($ytitle);
            $objGraphBar->setData($arrList);

            // メインタイトル作成
            $arrKey = array_keys($arrList);
            list($sy, $sm, $sd) = preg_split('|[/ ]|' , $sdate);
            list($ey, $em, $ed) = preg_split('|[/ ]|' , $edate);
            $start_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $sy, 'T_FIELD2' => $sm, 'T_FIELD3' => $sd));
            $end_date = t('LC_Page_Admin_Total_007', array('T_FIELD1' => $ey, 'T_FIELD2' => $em, 'T_FIELD3' => $ed));
            $objGraphBar->drawTitle(t('LC_Page_Admin_Total_008', array('T_FIELD1' => $start_date, 'T_FIELD2' => $end_date)));

            $objGraphBar->drawGraph();

            if (DRAW_IMAGE) {
                $objGraphBar->outputGraph();
                SC_Response_Ex::actionExit();
            }

            // ファイルパスを返す
            $ret_path = GRAPH_URLPATH . $pngname;
        }
        return $ret_path;
    }

    // グラフ用のPNGファイル名
    function lfGetGraphPng($keyname) {

        if ($_POST['search_startyear_m'] != '') {
            $pngname = sprintf('%s_%02d%02d.png', $keyname, substr($_POST['search_startyear_m'],2), $_POST['search_startmonth_m']);
        } else {
            $pngname = sprintf('%s_%02d%02d%02d_%02d%02d%02d.png', $keyname, substr($_POST['search_startyear'], 2), $_POST['search_startmonth'], $_POST['search_startday'], substr($_POST['search_endyear'],2), $_POST['search_endmonth'], $_POST['search_endday']);
        }
        return $pngname;
    }

    // 会員、非会員集計のWHERE分の作成
    function lfGetWhereMember($col_date, $sdate, $edate, $type, $col_member = 'customer_id') {
        $where = '';
        // 取得日付の指定
        if ($sdate != '') {
            if ($where != '') {
                $where.= ' AND ';
            }
            $where.= " $col_date >= '". $sdate ."'";
        }

        if ($edate != '') {
            if ($where != '') {
                $where.= ' AND ';
            }
            $edate = date('Y/m/d',strtotime('1 day' ,strtotime($edate)));
            $where.= " $col_date < date('" . $edate ."')";
        }

        // 会員、非会員の判定
        switch ($type) {
            // 全体
            case 'all':
                break;
            case 'member':
                if ($where != '') {
                    $where.= ' AND ';
                }
                $where.= " $col_member <> 0";
                break;
            case 'nonmember':
                if ($where != '') {
                    $where.= ' AND ';
                }
                $where.= " $col_member = 0";
                break;
            default:
                break;
        }

        return array($where, array());
    }

    /** 会員別集計 **/
    function lfGetOrderMember($type, $sdate, $edate) {
        $objQuery = SC_Query_Ex::getSingletonInstance();

        list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);
        $where .= ' AND del_flg = 0 AND status <> ?';
        $arrWhereVal[] = ORDER_CANCEL;

        // 会員集計の取得
        $col = <<< __EOS__
            COUNT(order_id) AS order_count,
            SUM(total) AS total,
            AVG(total) AS total_average,
            CASE
                WHEN customer_id <> 0 THEN 1
                ELSE 0
            END AS member,
            order_sex
__EOS__;

        $from       = 'dtb_order';

        $objQuery->setGroupBy('member, order_sex');

        $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal);

        foreach ($arrTotalResults as $key => $value) {
            $arrResult =& $arrTotalResults[$key];
            $member_key = $arrResult['order_sex'];
            if ($member_key != '') {
                $arrResult['member_name'] = (($arrResult['member']) ? t('LC_Page_Admin_Total_009') : t('LC_Page_Admin_Total_010')) . $this->arrSex[$member_key];
            } else {
                $arrResult['member_name'] = t('LC_Page_Admin_Total_011');
            }
        }

        $tpl_image = $this->lfGetGraphPie($arrTotalResults, 'member_name', 'member', t('LC_Page_Admin_Total_039'), $sdate, $edate);

        return array($arrTotalResults, $tpl_image);
    }

    /** 商品別集計 **/
    function lfGetOrderProducts($type, $sdate, $edate) {
        $objQuery = SC_Query_Ex::getSingletonInstance();

        list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);

        $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ?';
        $arrWhereVal[] = ORDER_CANCEL;

        $col = <<< __EOS__
                product_id,
                product_code,
                product_name,
                SUM(quantity) AS products_count,
                COUNT(dtb_order_detail.order_id) AS order_count,
                price,
                (price * SUM(quantity)) AS total
__EOS__;

        $from = 'dtb_order_detail JOIN dtb_order ON dtb_order_detail.order_id = dtb_order.order_id';

        /*
        if ($mode != 'csv') {
            $sql.= 'LIMIT ' . PRODUCTS_TOTAL_MAX;
        }*/

        // 要index
        $objQuery->setGroupBy('product_id, product_name, product_code, price');
        //$objQuery->setGroupBy('product_id');
        $objQuery->setOrder('total DESC');
        $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal);

        $tpl_image  = $this->lfGetGraphPie($arrTotalResults, 'product_name', 'products_' . $type, t('LC_Page_Admin_Total_039'), $sdate, $edate);

        return array($arrTotalResults, $tpl_image);
    }

    /** 職業別集計 **/
    function lfGetOrderJob($type, $sdate, $edate) {
        $objQuery = SC_Query_Ex::getSingletonInstance();
        list($where, $arrWhereVal) = $this->lfGetWhereMember('dtb_order.create_date', $sdate, $edate, $type);

        $col = <<< __EOS__
            job,
            COUNT(order_id) AS order_count,
            SUM(total) AS total,
            AVG(total) AS total_average
__EOS__;

        $from   = 'dtb_order JOIN dtb_customer ON dtb_order.customer_id = dtb_customer.customer_id';

        $where .= ' AND dtb_order.del_flg = 0 AND dtb_order.status <> ?';
        $arrWhereVal[] = ORDER_CANCEL;

        $objQuery->setGroupBy('job');
        $objQuery->setOrder('total DESC');
        $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal);

        foreach ($arrTotalResults as $key => $value) {
            $arrResult =& $arrTotalResults[$key];
            $job_key = $arrResult['job'];
            if ($job_key != '') {
                $arrResult['job_name'] = $this->arrJob[$job_key];
            } else {
                $arrResult['job_name'] = t('LC_Page_Admin_Total_011');
            }

        }
        $tpl_image     = $this->lfGetGraphPie($arrTotalResults, 'job_name', 'job_' . $type, t('LC_Page_Admin_Total_039'), $sdate, $edate);

        return array($arrTotalResults, $tpl_image);
    }

    /** 年代別集計 **/
    function lfGetOrderAge($type, $sdate, $edate) {

        $objQuery = SC_Query_Ex::getSingletonInstance();

        list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);

        $dbFactory = SC_DB_DBFactory_Ex::getInstance();
        $col = $dbFactory->getOrderTotalAgeColSql() . ' AS age';
        $col .= ',COUNT(order_id) AS order_count';
        $col .= ',SUM(total) AS total';
        $col .= ',AVG(total) AS total_average';

        $from   = 'dtb_order';

        $where .= ' AND del_flg = 0 AND status <> ?';
        $arrWhereVal[] = ORDER_CANCEL;

        $objQuery->setGroupBy('age');
        $objQuery->setOrder('age DESC');
        $arrTotalResults = $objQuery->select($col, $from, $where, $arrWhereVal);

        foreach ($arrTotalResults as $key => $value) {
            $arrResult =& $arrTotalResults[$key];
            $age_key = $arrResult['age'];
            if ($age_key != '') {
                $arrResult['age_name'] = t('LC_Page_Admin_Total_012', array('T_FIELD' => $arrResult['age']));
            } else {
                $arrResult['age_name'] = t('LC_Page_Admin_Total_011');
            }

        }
        $tpl_image = $this->lfGetGraphBar($arrTotalResults, 'age_name', 'age_' . $type, t('LC_Page_Admin_Total_013'), t('LC_Page_Admin_Total_014'), $sdate, $edate);

        return array($arrTotalResults, $tpl_image);
    }

    /** 期間別集計 **/
    // todo あいだの日付埋める
    function lfGetOrderTerm($type, $sdate, $edate) {
        $objQuery   = SC_Query_Ex::getSingletonInstance();

        list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate);
        $where .= ' AND del_flg = 0 AND status <> ?';
        $arrWhereVal[] = ORDER_CANCEL;

        switch ($type) {
            case 'month':
                $xtitle = t('LC_Page_Admin_Total_015');
                $ytitle = t('LC_Page_Admin_Total_014');
                $format = '%m';
                break;
            case 'year':
                $xtitle = t('LC_Page_Admin_Total_016');
                $ytitle = t('LC_Page_Admin_Total_014');
                $format = '%Y';
                break;
            case 'wday':
                $xtitle = t('LC_Page_Admin_Total_017');
                $ytitle = t('LC_Page_Admin_Total_014');
                $format = '%a';
                break;
            case 'hour':
                $xtitle = t('LC_Page_Admin_Total_018');
                $ytitle = t('LC_Page_Admin_Total_014');
                $format = '%H';
                break;
            default:
                $xtitle = t('LC_Page_Admin_Total_019');
                $ytitle = t('LC_Page_Admin_Total_014');
                $format = '%Y-%m-%d';
                $xincline = true;
                break;
        }

        $dbFactory = SC_DB_DBFactory_Ex::getInstance();
        // todo postgres
        $col = $dbFactory->getOrderTotalDaysWhereSql($type);

        $objQuery->setGroupBy('str_date');
        $objQuery->setOrder('str_date');
        // 検索結果の取得
        $arrTotalResults = $objQuery->select($col, 'dtb_order', $where, $arrWhereVal);

        $arrTotalResults = $this->lfAddBlankLine($arrTotalResults, $type, $sdate, $edate);
        // todo GDない場合の処理
        $tpl_image       = $this->lfGetGraphLine($arrTotalResults, 'str_date', 'term_' . $type, $xtitle, $ytitle, $sdate, $edate, $xincline);
        $arrTotalResults = $this->lfAddTotalLine($arrTotalResults);

        return array($arrTotalResults, $tpl_image);
    }

    /*
     * 期間中の日付を埋める
     */
    function lfAddBlankLine($arrResults, $type, $st, $ed) {

        $arrDateList = $this->lfDateTimeArray($type, $st, $ed);

        foreach ($arrResults as $arrResult) {
            $strdate                = $arrResult['str_date'];
            $arrDateResults[$strdate] = $arrResult;
        }

        foreach ($arrDateList as $date) {

            if (array_key_exists($date, $arrDateResults)) {

                $arrRet[] = $arrDateResults[$date];

            } else {
                $arrRet[]['str_date'] = $date;
            }
        }
        return $arrRet;
    }

    /*
     * 日付の配列を作成する
     *
     */
    function lfDateTimeArray($type, $st, $ed) {
        switch ($type) {
            case 'month':
                $format        = 'm';
                break;
            case 'year':
                $format        = 'Y';
                break;
            case 'wday':
                $format        = 'D';
                break;
            case 'hour':
                $format        = 'H';
                break;
            default:
                $format        = 'Y-m-d';
                break;
        }

        if ($type == 'hour') {
            $arrDateList = array('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

        } else {
            $arrDateList = array();
            $tmp    = strtotime($st);
            $nAday  = 60*60*24;
            $edx    = strtotime($ed);
            while ($tmp <= $edx) {
                $sDate = date($format, $tmp);
                if (!in_array($sDate, $arrDateList)) {
                    $arrDateList[] = $sDate;
                }
                $tmp += $nAday;
            }
        }
        return $arrDateList;
    }

    /*
     * 合計を付与する
     */
    function lfAddTotalLine($arrResults) {
        // 検索結果が0でない場合
        if (count($arrResults) > 0) {

            // 合計の計算
            foreach ($arrResults as $arrResult) {
                foreach ($arrResult as $key => $value) {
                    $arrTotal[$key] += $arrResult[$key];
                }
            }
            // 平均値の計算
            $arrTotal['total_average'] = $arrTotal['total'] / $arrTotal['total_order'];
            $arrResults[] = $arrTotal;
        }

        return $arrResults;
    }

    // 必要なカラムのみ抽出する(CSVデータで取得する)
    function lfGetDataColCSV($arrData, $arrDataCol) {
        $max = count($arrData);
        $csv_data = '';
        for ($i = 0; $i < $max; $i++) {
            foreach ($arrDataCol as $val) {
                $arrRet[$i][$val] = $arrData[$i][$val];
            }
            // 期間別集計の合計行の「期間」項目に不要な値が表示されてしまわない様、'合計'と表示する
            if (($i === $max -1) && isset($arrRet[$i]['str_date'])) {
                $arrRet[$i]['str_date'] = t('LC_Page_Admin_Total_020');
            }
            $csv_data.= SC_Utils_Ex::sfGetCSVList($arrRet[$i]);
        }
        return $csv_data;
    }

    function lfGetCSVColum($page) {
        switch ($page) {
            // 商品別集計
            case 'products':
                $arrTitleCol = array(
                    t('LC_Page_Admin_Total_021'),
                    t('LC_Page_Admin_Total_022'),
                    t('LC_Page_Admin_Total_023'),
                    t('c_Quantity_01'),
                    t('c_Unit price_01'),
                    t('LC_Page_Admin_Total_026'),
                );
                $arrDataCol = array(
                    'product_code',
                    'product_name',
                    'order_count',
                    'products_count',
                    'price',
                    'total',
                );
                break;
            // 職業別集計
            case 'job':
                $arrTitleCol = array(
                    t('LC_Page_Admin_Total_027'),
                    t('LC_Page_Admin_Total_023'),
                    t('LC_Page_Admin_Total_028'),
                    t('LC_Page_Admin_Total_029'),
                );
                $arrDataCol = array(
                    'job_name',
                    'order_count',
                    'total',
                    'total_average',
                );
                break;
            // 会員別集計
            case 'member':
                $arrTitleCol = array(
                    t('LC_Page_Admin_Total_030'),
                    t('LC_Page_Admin_Total_023'),
                    t('LC_Page_Admin_Total_028'),
                    t('LC_Page_Admin_Total_029'),
                );
                $arrDataCol = array(
                    'member_name',
                    'order_count',
                    'total',
                    'total_average',
                );
                break;
            // 年代別集計
            case 'age':
                $arrTitleCol = array(
                    t('LC_Page_Admin_Total_031'),
                    t('LC_Page_Admin_Total_023'),
                    t('LC_Page_Admin_Total_028'),
                    t('LC_Page_Admin_Total_029'),
                );
                $arrDataCol = array(
                    'age_name',
                    'order_count',
                    'total',
                    'total_average',
                );
                break;
            // 期間別集計
            default:
                $arrTitleCol = array(
                    t('LC_Page_Admin_Total_032'),
                    t('LC_Page_Admin_Total_023'),
                    t('LC_Page_Admin_Total_033'),
                    t('LC_Page_Admin_Total_034'),
                    t('LC_Page_Admin_Total_035'),
                    t('LC_Page_Admin_Total_036'),
                    t('LC_Page_Admin_Total_037'),
                    t('LC_Page_Admin_Total_038'),
                    t('LC_Page_Admin_Total_028'),
                    t('LC_Page_Admin_Total_029'),
                );
                $arrDataCol = array(
                    'str_date',
                    'total_order',
                    'men',
                    'women',
                    'men_member',
                    'men_nonmember',
                    'women_member',
                    'women_nonmember',
                    'total',
                    'total_average',
                );
                break;
        }

        return array($arrTitleCol, $arrDataCol);
    }
}