PHP SC_Query::setgroupby方法代码示例

 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];
//.........这里部分代码省略.........

 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;
 }