function getRanking()
{
$objQuery = new SC_Query();
$col = "T1.product_id, T1.product_name as name,COUNT(*) AS order_count ";
$from = "dtb_order_detail AS T1\r\n INNER JOIN dtb_order AS T2 ON T1.order_id = T2.order_id\r\n INNER JOIN dtb_products AS T3 ON T1.product_id = T3.product_id";
$objQuery->setgroupby("T1.product_id,T1.product_name");
$objQuery->setorder("order_count DESC");
$objQuery->setlimit(10);
//var_dump($objQuery->setorder("order_count DESC"));
return $objQuery->select($col, $from, 'T2.status = ?', array('5'));
}
function lfGetEbisData($order_id)
{
$objQuery = new SC_Query();
$col = "customer_id, total, order_sex, order_job, to_number(to_char(age(current_timestamp, order_birth), 'YYY'), 999) AS order_age";
$arrRet = $objQuery->select($col, "dtb_order", "order_id = ?", array($order_id));
if ($arrRet[0]['customer_id'] > 0) {
// 会員番号
$arrEbis['m1id'] = $arrRet[0]['customer_id'];
// 非会員or会員
$arrEbis['o5id'] = '1';
} else {
// 会員番号
$arrEbis['m1id'] = '';
// 非会員or会員
$arrEbis['o5id'] = '2';
}
// 購入金額
$arrEbis['a1id'] = $arrRet[0]['total'];
// 性別
$arrEbis['o2id'] = $arrRet[0]['order_sex'];
// 年齢
$arrEbis['o3id'] = $arrRet[0]['order_age'];
// 職業
$arrEbis['o4id'] = $arrRet[0]['order_job'];
$objQuery->setgroupby("product_id");
$arrRet = $objQuery->select("product_id", "dtb_order_detail", "order_id = ?", array($order_id));
$arrProducts = sfSwapArray($arrRet);
$line = "";
// 商品IDをアンダーバーで接続する。
foreach ($arrProducts['product_id'] as $val) {
if ($line != "") {
$line .= "_{$val}";
} else {
$line .= "{$val}";
}
}
// 商品ID
$arrEbis['o1id'] = $line;
return $arrEbis;
}
/** 期間別集計 **/
function lfGetOrderTerm($type, $sdate, $edate, &$objPage, $graph = true)
{
$tmp_col = "sum(total_order) as total_order, sum(men) as men, sum(women) as women,";
$tmp_col .= "sum(men_member) as men_member, sum(men_nonmember) as men_nonmember,";
$tmp_col .= "sum(women_member) as women_member, sum(women_nonmember) as women_nonmember,";
$tmp_col .= "sum(total) as total, (avg(total_average)) as total_average";
$objQuery = new SC_Query();
switch ($type) {
// 月別
case 'month':
$col = $tmp_col . ",key_month";
$objQuery->setgroupby("key_month");
$objQuery->setOrder("key_month");
$objPage->keyname = "key_month";
$objPage->tpl_tail = "月";
$from = "dtb_bat_order_daily";
$xtitle = "(月別)";
$ytitle = "(売上合計)";
break;
// 年別
// 年別
case 'year':
$col = $tmp_col . ",key_year";
$objQuery->setgroupby("key_year");
$objQuery->setOrder("key_year");
$objPage->keyname = "key_year";
$objPage->tpl_tail = "年";
$from = "dtb_bat_order_daily";
$xtitle = "(年別)";
$ytitle = "(売上合計)";
break;
// 曜日別
// 曜日別
case 'wday':
$col = $tmp_col . ",key_wday, wday";
$objQuery->setgroupby("key_wday, wday");
$objQuery->setOrder("wday");
$objPage->keyname = "key_wday";
$objPage->tpl_tail = "曜日";
$from = "dtb_bat_order_daily";
$xtitle = "(曜日別)";
$ytitle = "(売上合計)";
break;
// 時間別
// 時間別
case 'hour':
$col = $tmp_col . ",hour";
$objQuery->setgroupby("hour");
$objQuery->setOrder("hour");
$objPage->keyname = "hour";
$objPage->tpl_tail = "時";
$from = "dtb_bat_order_daily_hour";
$xtitle = "(時間別)";
$ytitle = "(売上合計)";
break;
default:
$col = "*";
$objQuery->setOrder("key_day");
$objPage->keyname = "key_day";
$from = "dtb_bat_order_daily";
$xtitle = "(日別)";
$ytitle = "(売上合計)";
break;
}
if (!isset($where)) {
$where = "";
}
// 取得日付の指定
if ($sdate != "") {
if ($where != "") {
$where .= " AND ";
}
$where .= " order_date >= '" . $sdate . "'";
}
if ($edate != "") {
if ($where != "") {
$where .= " AND ";
}
$edate_next = date("Y/m/d", strtotime("1 day", strtotime($edate)));
$where .= " order_date < date('" . $edate_next . "')";
}
if (!isset($arrval)) {
$arrval = array();
}
// 検索結果の取得
$objPage->arrResults = $objQuery->select($col, $from, $where, $arrval);
// 折れ線グラフの生成
if ($graph) {
$image_key = "term_" . $type;
$objPage->tpl_image = $this->lfGetGraphLine($objPage->arrResults, $objPage->keyname, $image_key, $xtitle, $ytitle, $sdate, $edate);
}
// 検索結果が0でない場合
if (count($objPage->arrResults) > 0) {
// 最終集計行取得する
$col = $tmp_col;
$objQuery = new SC_Query();
$arrRet = $objQuery->select($col, $from, $where, $arrval);
$arrRet[0][$objPage->keyname] = "合計";
$objPage->arrResults[] = $arrRet[0];
}
// 平均値の計算
$max = count($objPage->arrResults);
for ($i = 0; $i < $max; $i++) {
if ($objPage->arrResults[$i]['total_order'] > 0) {
$objPage->arrResults[$i]['total_average'] = intval($objPage->arrResults[$i]['total'] / $objPage->arrResults[$i]['total_order']);
}
}
}
function lfGetCustomerOrderId($keyword)
{
$arrCustomerOrderId = null;
if ($keyword != "") {
$col = "dtb_order.customer_id, dtb_order.order_id";
$from = "dtb_order LEFT JOIN dtb_order_detail USING(order_id)";
$where = "product_code LIKE ? AND del_flg = 0";
$val = SC_Utils_Ex::sfManualEscape($keyword);
$arrVal[] = "%{$val}%";
$objQuery = new SC_Query();
$objQuery->setgroupby("customer_id, order_id");
$arrRet = $objQuery->select($col, $from, $where, $arrVal);
$arrCustomerOrderId = SC_Utils_Ex::sfArrKeyValues($arrRet, "customer_id", "order_id");
}
return $arrCustomerOrderId;
}