PHP SC_Query::setOrder方法代码示例

 /**
  * SC_Queryインスタンスに設定された検索条件を元に並び替え済みの検索結果商品IDの配列を取得する。
  *
  * 検索条件は, SC_Query::setWhere() 関数で設定しておく必要があります.
  *
  * @param  SC_Query $objQuery SC_Query インスタンス
  * @param  array    $arrVal   検索パラメーターの配列
  * @return array    商品IDの配列
  */
 public function findProductIdsOrder(&$objQuery, $arrVal = array())
 {
     $table = 'dtb_products AS alldtl';
     if (is_array($this->arrOrderData) and $objQuery->order == '') {
         $o_col = $this->arrOrderData['col'];
         $o_table = $this->arrOrderData['table'];
         $o_order = $this->arrOrderData['order'];
         $objQuery->setOrder("T2.{$o_col} {$o_order}");
         $objQuery->setLimit(1);
         $sub_sql = $objQuery->getSqlWithLimit($o_col, "{$o_table} AS T2", 'T2.product_id = alldtl.product_id');
         $objQuery->setOrder("({$sub_sql}) {$o_order}, product_id");
     }
     $arrReturn = $objQuery->getCol('alldtl.product_id', $table, '', $arrVal);
     return $arrReturn;
 }

    /**
     * SC_Queryインスタンスに設定された検索条件を元に並び替え済みの検索結果商品IDの配列を取得する。
     *
     * 検索条件は, SC_Query::setWhere() 関数で設定しておく必要があります.
     *
     * @param SC_Query $objQuery SC_Query インスタンス
     * @param array $arrVal 検索パラメーターの配列
     * @return array 商品IDの配列
     */
    public function findProductIdsOrder(&$objQuery, $arrVal = array())
    {
        $table = <<<__EOS__
           dtb_products AS alldtl JOIN dtb_products_class AS P
                    ON  P.product_id = alldtl.product_id
                    LEFT JOIN dtb_class AS A
                    ON P.classcategory_id1 = A.class_id
                    LEFT JOIN dtb_class AS B
                    ON P.classcategory_id2 = B.class_id
__EOS__;
        $objQuery->setGroupBy('alldtl.product_id');
        if (is_array($this->arrOrderData) and $objQuery->order == '') {
            $o_col = $this->arrOrderData['col'];
            $o_table = $this->arrOrderData['table'];
            $o_order = $this->arrOrderData['order'];
            $order = <<<__EOS__
                    (
                        SELECT {$o_col}
                        FROM
                            {$o_table} as T2
                        WHERE T2.product_id = alldtl.product_id
                        ORDER BY T2.{$o_col} {$o_order}
                        LIMIT 1
                    ) {$o_order}, product_id
__EOS__;
            $objQuery->setOrder($order);
        }
        $results = $objQuery->select('alldtl.product_id', $table, '', $arrVal, MDB2_FETCHMODE_ORDERED);
        $resultValues = array();
        foreach ($results as $val) {
            $resultValues[] = $val[0];
        }
        return $resultValues;
    }

    /**
     * SC_Query インスタンスに設定された検索条件を使用して商品規格を取得する.
     *
     * @param SC_Query $objQuery SC_Queryインスタンス
     * @param array $params 検索パラメーターの配列
     * @return array 商品規格の配列
     */
    function getProductsClassByQuery(&$objQuery, $params)
    {
        // 末端の規格を取得
        $col = <<<__EOS__
            T1.product_id,
            T1.stock,
            T1.stock_unlimited,
            T1.sale_limit,
            T1.price01,
            T1.price02,
            T1.point_rate,
            T1.product_code,
            T1.product_class_id,
            T1.del_flg,
            T1.product_type_id,
            T1.down_filename,
            T1.down_realfilename,
            T3.name AS classcategory_name1,
            T3.rank AS rank1,
            T4.name AS class_name1,
            T4.class_id AS class_id1,
            T1.classcategory_id1,
            T1.classcategory_id2,
            dtb_classcategory2.name AS classcategory_name2,
            dtb_classcategory2.rank AS rank2,
            dtb_class2.name AS class_name2,
            dtb_class2.class_id AS class_id2
__EOS__;
        $table = <<<__EOS__
            dtb_products_class T1
            LEFT JOIN dtb_classcategory T3
                ON T1.classcategory_id1 = T3.classcategory_id
            LEFT JOIN dtb_class T4
                ON T3.class_id = T4.class_id
            LEFT JOIN dtb_classcategory dtb_classcategory2
                ON T1.classcategory_id2 = dtb_classcategory2.classcategory_id
            LEFT JOIN dtb_class dtb_class2
                ON dtb_classcategory2.class_id = dtb_class2.class_id
__EOS__;
        $objQuery->setOrder('T3.rank DESC, dtb_classcategory2.rank DESC');
        // XXX
        $arrRet = $objQuery->select($col, $table, '', $params);
        return $arrRet;
    }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_AdminView();
     // 認証可否の判定
     $objSess = new SC_Session();
     SC_Utils_Ex::sfIsSuccess($objSess);
     // 検索パラメータの引き継ぎ
     foreach ($_POST as $key => $val) {
         if (ereg("^search_", $key)) {
             $this->arrSearchHidden[$key] = $val;
         }
     }
     $this->tpl_product_id = isset($_POST['product_id']) ? $_POST['product_id'] : "";
     $this->tpl_pageno = isset($_POST['pageno']) ? $_POST['pageno'] : "";
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     switch ($_POST['mode']) {
         // 規格削除要求
         case 'delete':
             $objQuery = new SC_Query();
             $objQuery->setLimitOffset(1);
             $where = "product_id = ? AND NOT (classcategory_id1 = 0 AND classcategory_id2 = 0)";
             $objQuery->setOrder("rank1 DESC, rank2 DESC");
             $arrRet = $objQuery->select("*", "vw_cross_products_class AS crs_prd", $where, array($_POST['product_id']));
             if (count($arrRet) > 0) {
                 $sqlval['product_id'] = $arrRet[0]['product_id'];
                 $sqlval['classcategory_id1'] = '0';
                 $sqlval['classcategory_id2'] = '0';
                 $sqlval['product_code'] = $arrRet[0]['product_code'];
                 $sqlval['stock'] = $arrRet[0]['stock'];
                 $sqlval['price01'] = $arrRet[0]['price01'];
                 $sqlval['price02'] = $arrRet[0]['price02'];
                 $sqlval['creator_id'] = $_SESSION['member_id'];
                 $sqlval['create_date'] = "now()";
                 $sqlval['update_date'] = "now()";
                 $objQuery->begin();
                 $where = "product_id = ?";
                 $objQuery->delete("dtb_products_class", $where, array($_POST['product_id']));
                 $objQuery->insert("dtb_products_class", $sqlval);
                 $objQuery->commit();
             }
             $this->lfProductClassPage();
             // 規格登録ページ
             break;
             // 編集要求
         // 編集要求
         case 'pre_edit':
             $objQuery = new SC_Query();
             $where = "product_id = ? AND NOT(classcategory_id1 = 0 AND classcategory_id2 = 0) ";
             $ret = $objQuery->count("dtb_products_class", $where, array($_POST['product_id']));
             if ($ret > 0) {
                 // 規格組み合わせ一覧の取得(DBの値を優先する。)
                 $this->arrClassCat = $this->lfGetClassCatListEdit($_POST['product_id']);
             }
             $this->lfProductClassPage();
             // 規格登録ページ
             break;
             // 規格組み合わせ表示
         // 規格組み合わせ表示
         case 'disp':
             $this->arrForm['select_class_id1'] = $_POST['select_class_id1'];
             $this->arrForm['select_class_id2'] = $_POST['select_class_id2'];
             $this->arrErr = $this->lfClassError();
             if (count($this->arrErr) == 0) {
                 // 規格組み合わせ一覧の取得
                 $this->arrClassCat = $this->lfGetClassCatListDisp($_POST['select_class_id1'], $_POST['select_class_id2']);
             }
             $this->lfProductClassPage();
             // 規格登録ページ
             break;
             // 規格登録要求
         // 規格登録要求
         case 'edit':
             // 入力値の変換
             $this->arrForm = $this->lfConvertParam($_POST);
             // エラーチェック
             $this->arrErr = $this->lfProductClassError($this->arrForm);
             if (count($this->arrErr) == 0) {
                 // 確認ページ設定
                 $this->tpl_mainpage = 'products/product_class_confirm.tpl';
                 $this->lfProductConfirmPage();
                 // 確認ページ表示
             } else {
                 // 規格組み合わせ一覧の取得
                 $this->arrClassCat = $this->lfGetClassCatListDisp($_POST['class_id1'], $_POST['class_id2'], false);
                 $this->lfProductClassPage();
                 // 規格登録ページ
             }
             break;
             // 確認ページからの戻り
         // 確認ページからの戻り
         case 'confirm_return':
             // フォームパラメータの引き継ぎ
             $this->arrForm = $_POST;
//.........这里部分代码省略.........

 /**
  * 商品情報を取得する(vw_products_allclass使用)
  *
  * @param SC_Query $objQuery DB操作クラス
  * @return array $arrProduct 取得結果を配列で返す
  */
 function lfGetProductsAllclass(&$objQuery)
 {
     // --- 商品一覧の取得
     $objQuery->setWhere('del_flg = 0 AND status = 1');
     $objQuery->setOrder('product_id');
     $objProduct = new SC_Product_Ex();
     $arrProductLsit = $objProduct->lists($objQuery);
     // 各商品のカテゴリIDとランクの取得
     $arrProducts = array();
     foreach ($arrProductLsit as $key => $val) {
         $sql = '';
         $sql .= ' SELECT';
         $sql .= '   T1.category_id,';
         $sql .= '   T1.rank AS product_rank,';
         $sql .= '   T2.rank AS category_rank';
         $sql .= ' FROM';
         $sql .= '   dtb_product_categories AS T1';
         $sql .= ' LEFT JOIN';
         $sql .= '   dtb_category AS T2';
         $sql .= ' ON';
         $sql .= '   T1.category_id = T2.category_id';
         $sql .= ' WHERE';
         $sql .= '   product_id = ?';
         $arrCategory = $objQuery->getAll($sql, array($val['product_id']));
         if (!empty($arrCategory)) {
             $arrProducts[$key] = array_merge($val, $arrCategory[0]);
         }
     }
     // 税込金額を設定する
     SC_Product_Ex::setIncTaxToProducts($arrProducts);
     return $arrProducts;
 }

 /**
  * 新着情報を取得する
  *
  * @param SC_Query $objQuery DB操作クラス
  * @return array $arrNews 取得結果を配列で返す
  */
 function lfGetNews(&$objQuery)
 {
     $col = "";
     $col .= "news_id ";
     // 新着情報ID
     $col .= ",news_title ";
     // 新着情報タイトル
     $col .= ",news_comment ";
     // 新着情報本文
     $col .= ",news_date ";
     // 日付
     $col .= ",news_url ";
     // 新着情報URL
     $col .= ",news_select ";
     // 新着情報の区分(1:URL、2:本文)
     $col .= ",(SELECT shop_name FROM dtb_baseinfo limit 1) AS shop_name  ";
     // 店名
     $col .= ",(SELECT email04 FROM dtb_baseinfo limit 1) AS email ";
     // 代表Emailアドレス
     $from = "dtb_news";
     $where = "del_flg = '0'";
     $order = "rank DESC";
     $objQuery->setOrder($order);
     $arrNews = $objQuery->select($col, $from, $where);
     // RSS用に変換
     foreach (array_keys($arrNews) as $key) {
         $netUrlHttpUrl = new Net_URL(HTTP_URL);
         $row =& $arrNews[$key];
         // 日付
         $row['news_date'] = date('r', strtotime($row['news_date']));
         // 新着情報URL
         if (SC_Utils_Ex::isBlank($row['news_url'])) {
             $row['news_url'] = HTTP_URL;
         } elseif ($row['news_url'][0] == '/') {
             // 変換(絶対パス→URL)
             $netUrl = new Net_URL($row['news_url']);
             $netUrl->protocol = $netUrlHttpUrl->protocol;
             $netUrl->user = $netUrlHttpUrl->user;
             $netUrl->pass = $netUrlHttpUrl->pass;
             $netUrl->host = $netUrlHttpUrl->host;
             $netUrl->port = $netUrlHttpUrl->port;
             $row['news_url'] = $netUrl->getUrl();
         }
     }
     return $arrNews;
 }

 /**
  * 親IDの配列を元に特定のカラムを取得する.
  *
  * @param  SC_Query $objQuery SC_Query インスタンス
  * @param  string   $table    テーブル名
  * @param  string   $id_name  ID名
  * @param  string   $col_name カラム名
  * @param  array    $arrId    IDの配列
  * @return array    特定のカラムの配列
  */
 public function sfGetParentsCol($objQuery, $table, $id_name, $col_name, $arrId)
 {
     $col = $col_name;
     $len = count($arrId);
     $where = '';
     for ($cnt = 0; $cnt < $len; $cnt++) {
         if ($where == '') {
             $where = "{$id_name} = ?";
         } else {
             $where .= " OR {$id_name} = ?";
         }
     }
     $objQuery->setOrder('level');
     $arrRet = $objQuery->select($col, $table, $where, $arrId);
     return $arrRet;
 }

 /** 期間別集計 **/
 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];
//.........这里部分代码省略.........

 /**
  * 都道府県、支払い方法から配送料金を取得する.
  *
  * @param integer $pref 都道府県ID
  * @param integer $payment_id 支払い方法ID
  * @return string 指定の都道府県, 支払い方法の配送料金
  */
 function sfGetDelivFee($arrData)
 {
     $pref = $arrData['deliv_pref'];
     $payment_id = isset($arrData['payment_id']) ? $arrData['payment_id'] : "";
     $objQuery = new SC_Query();
     $deliv_id = "";
     // 支払い方法が指定されている場合は、対応した配送業者を取得する
     if ($payment_id != "") {
         $where = "del_flg = 0 AND payment_id = ?";
         $arrRet = $objQuery->select("deliv_id", "dtb_payment", $where, array($payment_id));
         $deliv_id = $arrRet[0]['deliv_id'];
         // 支払い方法が指定されていない場合は、先頭の配送業者を取得する
     } else {
         $where = "del_flg = 0";
         $objQuery->setOrder("rank DESC");
         $objQuery->setLimitOffset(1);
         $arrRet = $objQuery->select("deliv_id", "dtb_deliv", $where);
         $deliv_id = $arrRet[0]['deliv_id'];
     }
     // 配送業者から配送料を取得
     if ($deliv_id != "") {
         // 都道府県が指定されていない場合は、東京都の番号を指定しておく
         if ($pref == "") {
             $pref = 13;
         }
         $objQuery = new SC_Query();
         $where = "deliv_id = ? AND pref = ?";
         $arrRet = $objQuery->select("fee", "dtb_delivfee", $where, array($deliv_id, $pref));
     }
     return $arrRet[0]['fee'];
 }

    /**
     * SC_Query インスタンスに設定された検索条件を使用して商品規格を取得する.
     *
     * @param SC_Query $objQuery SC_Queryインスタンス
     * @param array $params 検索パラメーターの配列
     * @return array 商品規格の配列
     */
    function getProductsClassByQuery(&$objQuery, $params)
    {
        // 末端の規格を取得
        $col = <<<__EOS__
            T1.product_id,
            T1.stock,
            T1.stock_unlimited,
            T1.sale_limit,
            T1.price01,
            T1.price02,
            T1.point_rate,
            T1.product_code,
            T1.product_class_id,
            T1.del_flg,
            T1.product_type_id,
            T1.down_filename,
            T1.down_realfilename,
            T2.class_combination_id,
            T2.parent_class_combination_id,
            T2.classcategory_id,
            T2.level,
            T3.name AS classcategory_name,
            T3.rank,
            T4.name AS class_name,
            T4.class_id
__EOS__;
        $table = <<<__EOS__
                      dtb_products_class T1
            LEFT JOIN dtb_class_combination T2
                   ON T1.class_combination_id = T2.class_combination_id
            LEFT JOIN dtb_classcategory T3
                   ON T2.classcategory_id = T3.classcategory_id
            LEFT JOIN dtb_class T4
                   ON T3.class_id = T4.class_id
__EOS__;
        $objQuery->setOrder('T3.rank DESC');
        // XXX
        $arrRet = $objQuery->select($col, $table, "", $params);
        $levels = array();
        $parents = array();
        foreach ($arrRet as $rows) {
            $levels[] = $rows['level'];
            $parents[] = $rows['parent_class_combination_id'];
        }
        $level = max($levels);
        $parentsClass = array();
        // 階層分の親を取得
        for ($i = 0; $i < $level - 1; $i++) {
            $objQuery =& SC_Query_Ex::getSingletonInstance();
            $objQuery->setWhere('T1.class_combination_id IN (' . implode(', ', array_pad(array(), count($parents), '?')) . ')');
            $col = <<<__EOS__
                T1.class_combination_id,
                T1.classcategory_id,
                T1.parent_class_combination_id,
                T1.level,
                T2.name AS classcategory_name,
                T2.rank,
                T3.name AS class_name,
                T3.class_id
__EOS__;
            $table = <<<__EOS__
                          dtb_class_combination T1
                LEFT JOIN dtb_classcategory T2
                       ON T1.classcategory_id = T2.classcategory_id
                LEFT JOIN dtb_class T3
                       ON T2.class_id = T3.class_id
__EOS__;
            $objQuery->setOrder('T2.rank DESC');
            // XXX
            $arrParents = $objQuery->select($col, $table, "", $parents);
            foreach ($arrParents as $rows) {
                $parents[] = $rows['parent_class_combination_id'];
                foreach ($arrRet as $child) {
                    if ($child['parent_class_combination_id'] == $rows['class_combination_id']) {
                        $rows['product_id'] = $child['product_id'];
                    }
                }
                $tmpParents[] = $rows;
            }
            $parentsClass = array_merge($parentsClass, $tmpParents);
        }
        // 末端から枝を作成
        $tmpClass = array_merge($arrRet, $parentsClass);
        foreach ($tmpClass as $val) {
            $val['class_id' . $val['level']] = $val['class_id'];
            $val['class_name' . $val['level']] = $val['class_name'];
            $val['classcategory_name' . $val['level']] = $val['classcategory_name'];
            $val['classcategory_id' . $val['level']] = $val['classcategory_id'];
            $arrProductsClass[] = $val;
        }
        return $arrProductsClass;
    }

 /**
  * ナビ情報を取得する.
  *
  * @param string $url ページのURL
  * @param boolean $preview プレビュー表示の場合 true
  * @return array ナビ情報の配列
  */
 function lfGetNaviData($url, $preview = false)
 {
     $objQuery = new SC_Query();
     // DB操作オブジェクト
     $sql = "";
     // データ取得SQL生成用
     $arrRet = array();
     // データ取得用
     $arrData = array();
     // SQL文生成
     // 取得するカラム
     $col = "target_id, bloc_name, tpl_path, php_path";
     // 取得するテーブル
     $table = "dtb_blocposition AS pos, dtb_bloc AS bloc";
     // where文生成
     $where = "bloc.bloc_id = pos.bloc_id";
     if ($preview == true) {
         $where .= " AND EXISTS (SELECT page_id FROM dtb_pagelayout AS lay WHERE page_id = '0' AND pos.page_id = lay.page_id)";
     } else {
         $where .= " AND EXISTS (SELECT page_id FROM dtb_pagelayout AS lay WHERE url = ? AND page_id <> '0' AND pos.page_id = lay.page_id)";
         $sqlval = array($url);
     }
     // 並び変え
     $objQuery->setOrder('target_id, bloc_row');
     // SQL実行
     $arrRet = $objQuery->select($col, $table, $where, $sqlval);
     // 結果を返す
     return $arrRet;
 }