/**
* ポイント履歴集計
*/
public function lfGetCustmoerPointHistory($type, $sdate, $edate)
{
$objQuery = SC_Query_Ex::getSingletonInstance();
list($where, $arrWhereVal) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);
$objSql = new SC_SelectSql();
$objSql->setWhere($where);
if (is_numeric($_REQUEST["search_customer_id"])) {
$arrWhereVal[] = intval($_REQUEST["search_customer_id"]);
$objSql->setWhere("customer_id = ? ");
}
// いずれかのポイントが 0 出ない場合出力する
$objSql->setWhereByOR(array(array("column" => "NOT add_point", "value" => 0), array("column" => "NOT use_point", "value" => 0), array("column" => "NOT lost_point", "value" => 0)));
$where = $objSql->getWhere(false);
switch ($type) {
case 'month':
$xtitle = '(月別)';
$ytitle = '(ポイント)';
$db_format = 'YYYY-MM';
$db_format2 = 'YYYY-MM';
break;
case 'year':
$xtitle = '(年別)';
$ytitle = '(ポイント)';
$db_format = 'YYYY';
$db_format2 = 'YYYY';
break;
case 'wday':
$xtitle = '(曜日別)';
$ytitle = '(ポイント)';
$db_format = 'Dy';
$db_format2 = 'ID';
break;
case 'hour':
$xtitle = '(時間別)';
$ytitle = '(ポイント)';
$db_format = 'HH24';
$db_format2 = 'HH24';
break;
default:
$xtitle = '(日別)';
$ytitle = '(ポイント)';
$db_format = 'YYYY-MM-DD';
$db_format2 = 'YYYY-MM-DD';
$xincline = true;
break;
}
$col = "\n TO_CHAR(create_date, '{$db_format}') AS str_date,\n TO_CHAR(create_date, '{$db_format2}') AS str_date2,\n COUNT(*) AS total_history,\n SUM(add_point) AS add_point,\n SUM(use_point) AS use_point,\n SUM(lost_point) AS lost_point,\n SUM(use_point + lost_point) AS minus_point,\n SUM(add_point - (use_point + lost_point)) AS total\n";
$objQuery->setGroupBy('str_date,str_date2');
$objQuery->setOrder('str_date2');
// 検索結果の取得
$arrTotalResults = $objQuery->select($col, 'cp_dtb_point_history', $where, $arrWhereVal);
$arrTotalResults = is_array($arrTotalResults) ? $arrTotalResults : array();
$arrTotalResults = $this->lfAddBlankLine($arrTotalResults, $type, $sdate, $edate);
// todo GDない場合の処理
$tpl_image = $this->lfGetGraphLine($arrTotalResults, 'str_date', 'term_' . $type, $xtitle, $ytitle, $sdate, $edate, $xincline);
GC_Utils_Ex::gfDebugLog($tpl_image);
$this->xtitle = $xtitle;
$arrCsvResults = $arrTotalResults;
if ($this->getMode() != "csv") {
$arrTotalResults[] = array("str_date" => "合計", "total_history" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'total_history')), "add_point" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'add_point')), "use_point" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'use_point')), "lost_point" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'lost_point')), "minus_point" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'minus_point')), "total" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'total')), "total_history_sum" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'total_history')), "total_sum" => array_sum(SC_Utils_Ex::sfArrKeyValue($arrTotalResults, "str_date", 'total')));
}
return array($arrTotalResults, $tpl_image, $arrCsvResults);
}
