PHP SC_Query::getAll方法代码示例

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objSubView = new SC_SiteView();
     $objCart = new SC_CartSession();
     $objSiteInfo = new SC_SiteInfo();
     if (count($_SESSION[$objCart->key]) > 0) {
         // カート情報を取得
         $arrCartList = $objCart->getCartList();
         // カート内の商品ＩＤ一覧を取得
         $arrAllProductID = $objCart->getAllProductID();
         // 商品が1つ以上入っている場合には商品名称を取得
         if (count($arrAllProductID) > 0) {
             $objQuery = new SC_Query();
             $arrVal = array();
             $sql = "";
             $sql = "SELECT name FROM dtb_products WHERE product_id IN ( ?";
             $arrVal = array($arrAllProductID[0]);
             for ($i = 1; $i < count($arrAllProductID); $i++) {
                 $sql .= " ,? ";
                 array_push($arrVal, $arrAllProductID[$i]);
             }
             $sql .= " )";
             $arrProduct_name = $objQuery->getAll($sql, $arrVal);
             foreach ($arrProduct_name as $key => $val) {
                 $arrCartList[$key]['product_name'] = $val['name'];
             }
         }
         // 店舗情報の取得
         $arrInfo = $objSiteInfo->data;
         // 購入金額合計
         $ProductsTotal = $objCart->getAllProductsTotal($arrInfo);
         // 合計個数
         $TotalQuantity = $objCart->getTotalQuantity();
         // 送料無料までの金額
         $arrCartList[0]['ProductsTotal'] = $ProductsTotal;
         $arrCartList[0]['TotalQuantity'] = $TotalQuantity;
         $deliv_free = $arrInfo['free_rule'] - $ProductsTotal;
         $arrCartList[0]['free_rule'] = $arrInfo['free_rule'];
         $arrCartList[0]['deliv_free'] = $deliv_free;
         $this->arrCartList = $arrCartList;
     }
     $objSubView->assignobj($this);
     $objSubView->display($this->tpl_mainpage);
 }

 function lfGetClassCatListEdit($product_id)
 {
     // 既存編集の場合
     $objQuery = new SC_Query();
     $col = "class_id1, class_id2, name1, name2, rank1, rank2, ";
     $col .= "product_class_id, product_id, T1_classcategory_id AS classcategory_id1, T2_classcategory_id AS classcategory_id2, ";
     $col .= "product_code, stock, stock_unlimited, sale_limit, price01, price02, status";
     $sql = "SELECT {$col} FROM ";
     $sql .= "( ";
     $sql .= "SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS T1_classcategory_id, T2.classcategory_id AS T2_classcategory_id, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 ";
     $sql .= "FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ";
     $sql .= "WHERE T1.class_id IN (SELECT class_id1 FROM vw_cross_products_class AS crs_prd WHERE product_id = ? GROUP BY class_id1, class_id2) AND T2.class_id IN (SELECT class_id2 FROM vw_cross_products_class AS crs_prd WHERE product_id = ? GROUP BY class_id1, class_id2)";
     $sql .= ") AS T1 ";
     $sql .= "LEFT JOIN (SELECT * FROM dtb_products_class WHERE product_id = ?) AS T3 ";
     $sql .= "ON T1_classcategory_id = T3.classcategory_id1 AND T2_classcategory_id = T3.classcategory_id2 ";
     $sql .= "ORDER BY rank1 DESC, rank2 DESC";
     $arrList = $objQuery->getAll($sql, array($product_id, $product_id, $product_id));
     $this->arrForm["class_id1"] = $arrList[0]['class_id1'];
     $this->arrForm["class_id2"] = $arrList[0]['class_id2'];
     $max = count($arrList);
     // デフォルト値を設定
     $this->lfSetDefaultClassCat($objQuery, $product_id, $max);
     $no = 1;
     for ($cnt = 0; $cnt < $max; $cnt++) {
         $this->arrForm["classcategory_id1:" . $no] = $arrList[$cnt]['classcategory_id1'];
         $this->arrForm["classcategory_id2:" . $no] = $arrList[$cnt]['classcategory_id2'];
         $this->arrForm["product_class_id:" . $no] = $arrList[$cnt]['product_class_id'];
         if ($arrList[$cnt]['product_id'] != "") {
             $this->arrForm["product_code:" . $no] = $arrList[$cnt]['product_code'];
             $this->arrForm['stock:' . $no] = $arrList[$cnt]['stock'];
             $this->arrForm['stock_unlimited:' . $no] = $arrList[$cnt]['stock_unlimited'];
             $this->arrForm['price01:' . $no] = $arrList[$cnt]['price01'];
             $this->arrForm['price02:' . $no] = $arrList[$cnt]['price02'];
             // JavaScript初期化用文字列
             $line .= "'check:" . $no . "',";
         }
         $no++;
     }
     $line = ereg_replace(",\$", "", $line);
     $this->tpl_javascript = "list = new Array({$line});";
     $color = DISABLED_RGB;
     $this->tpl_onload .= "fnListCheck(list); fnCheckAllStockLimit('{$max}', '{$color}');";
     return $arrList;
 }

    /**
     * カテゴリ数の登録を行う.
     *
     *
     * @param  SC_Query $objQuery           SC_Query インスタンス
     * @param  boolean  $is_force_all_count 全カテゴリの集計を強制する場合 true
     * @return void
     */
    public function sfCountCategory($objQuery = NULL, $is_force_all_count = false)
    {
        $objProduct = new SC_Product_Ex();
        if ($objQuery == NULL) {
            $objQuery =& SC_Query_Ex::getSingletonInstance();
        }
        $is_out_trans = false;
        if (!$objQuery->inTransaction()) {
            $objQuery->begin();
            $is_out_trans = true;
        }
        //共通のfrom/where文の構築
        $sql_where = SC_Product_Ex::getProductDispConditions('alldtl');
        // 在庫無し商品の非表示
        if (NOSTOCK_HIDDEN) {
            $where_products_class = '(stock >= 1 OR stock_unlimited = 1)';
            $from = $objProduct->alldtlSQL($where_products_class);
        } else {
            $from = 'dtb_products as alldtl';
        }
        //dtb_category_countの構成
        // 各カテゴリに所属する商品の数を集計。集計対象には子カテゴリを含まない。
        //まずテーブル内容の元を取得
        if (!$is_force_all_count) {
            $arrCategoryCountOld = $objQuery->select('category_id,product_count', 'dtb_category_count');
        } else {
            $arrCategoryCountOld = array();
        }
        //各カテゴリ内の商品数を数えて取得
        $sql = <<<__EOS__
            SELECT T1.category_id, count(T2.category_id) as product_count
            FROM dtb_category AS T1
                LEFT JOIN dtb_product_categories AS T2
                    ON T1.category_id = T2.category_id
                LEFT JOIN {$from}
                    ON T2.product_id = alldtl.product_id
            WHERE {$sql_where}
            GROUP BY T1.category_id, T2.category_id
__EOS__;
        $arrCategoryCountNew = $objQuery->getAll($sql);
        // 各カテゴリに所属する商品の数を集計。集計対象には子カテゴリを「含む」。
        //差分を取得して、更新対象カテゴリだけを確認する。
        //各カテゴリ毎のデータ値において以前との差を見る
        //古いデータの構造入れ替え
        $arrOld = array();
        foreach ($arrCategoryCountOld as $item) {
            $arrOld[$item['category_id']] = $item['product_count'];
        }
        //新しいデータの構造入れ替え
        $arrNew = array();
        foreach ($arrCategoryCountNew as $item) {
            $arrNew[$item['category_id']] = $item['product_count'];
        }
        unset($arrCategoryCountOld);
        unset($arrCategoryCountNew);
        $arrDiffCategory_id = array();
        //新しいカテゴリ一覧から見て商品数が異なるデータが無いか確認
        foreach ($arrNew as $cid => $count) {
            if ($arrOld[$cid] != $count) {
                $arrDiffCategory_id[] = $cid;
            }
        }
        //削除カテゴリを想定して、古いカテゴリ一覧から見て商品数が異なるデータが無いか確認。
        foreach ($arrOld as $cid => $count) {
            if ($arrNew[$cid] != $count && $count > 0) {
                $arrDiffCategory_id[] = $cid;
            }
        }
        //対象IDが無ければ終了
        if (count($arrDiffCategory_id) == 0) {
            if ($is_out_trans) {
                $objQuery->commit();
            }
            return;
        }
        //差分対象カテゴリIDの重複を除去
        $arrDiffCategory_id = array_unique($arrDiffCategory_id);
        //dtb_category_countの更新 差分のあったカテゴリだけ更新する。
        foreach ($arrDiffCategory_id as $cid) {
            $sqlval = array();
            $sqlval['create_date'] = 'CURRENT_TIMESTAMP';
            $sqlval['product_count'] = (string) $arrNew[$cid];
            if ($sqlval['product_count'] == '') {
                $sqlval['product_count'] = (string) '0';
            }
            if (isset($arrOld[$cid])) {
                $objQuery->update('dtb_category_count', $sqlval, 'category_id = ?', array($cid));
            } else {
                if ($is_force_all_count) {
                    $ret = $objQuery->update('dtb_category_count', $sqlval, 'category_id = ?', array($cid));
                    if ($ret > 0) {
                        continue;
//.........这里部分代码省略.........

 /**
  * テーブルを検索する.
  *
  * 引数に部分一致するテーブル名を配列で返す.
  *
  * @param string $expression 検索文字列
  * @return array テーブル名の配列
  */
 function findTableNames($expression = "")
 {
     $objQuery = new SC_Query();
     $sql = "SHOW TABLES LIKE ?";
     $arrColList = $objQuery->getAll($sql, array("%" . $expression . "%"));
     $arrColList = SC_Utils_Ex::sfswaparray($arrColList, false);
     return $arrColList[0];
 }

<?php

/*
 * This file is part of EC-CUBE
 *
 * Copyright(c) 2000-2007 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.
 */
require_once "../../require.php";
$objQuery = new SC_Query();
$objQuery->begin();
$arrCustomerMail = $objQuery->getAll("\nUPDATE dtb_customer\nSET mailmaga_flg = (\nSELECT mail_flag\nFROM dtb_customer_mail\nWHERE dtb_customer.email = dtb_customer_mail.email\n)");
$objQuery->commit();
echo "正常に移行が完了致しました。";

 /**
  * 開始行番号, 行数を指定して管理者データを取得する.
  *
  * @access private
  * @param integer $startno 開始行番号
  * @return array 管理者データの連想配列
  */
 function getMemberData($startno)
 {
     $objSql = new SC_SelectSql();
     $objSql->setSelect("SELECT member_id,name,department,login_id,authority,rank,work FROM dtb_member");
     $objSql->setOrder("rank DESC");
     $objSql->setWhere("del_flg <> 1 AND member_id <> " . ADMIN_ID);
     $objSql->setLimitOffset(MEMBER_PMAX, $startno);
     $objQuery = new SC_Query();
     $arrMemberData = $objQuery->getAll($objSql->getSql());
     return $arrMemberData;
 }

//.........这里部分代码省略.........
                        $question_id = $objQuery->nextval('dtb_question', 'question_id');
                    }
                    $sql_val = array('question' => $value, 'question_name' => $_POST['title'], 'question_id' => $question_id, 'create_date' => 'now()');
                    $objQuery->insert('dtb_question', $sql_val);
                    $this->MESSAGE = "登録が完了しました";
                    if (DB_TYPE == "mysql") {
                        $question_id = $objQuery->nextval('dtb_question', 'question_id');
                    }
                    $this->QUESTION_ID = $question_id;
                    $this->reload(null, true);
                    // 編集
                } else {
                    //編集
                    $value = serialize($_POST);
                    $sql_val = array('question' => $value, 'question_name' => $_POST['title']);
                    $objQuery->update('dtb_question', $sql_val, 'question_id = ?', array($_POST['question_id']));
                    $this->MESSAGE = "編集が完了しました";
                    $this->QUESTION_ID = $_POST['question_id'];
                    $this->reload(null, true);
                }
            } else {
                //エラー表示
                $this->ERROR = $error;
                $this->QUESTION_ID = $_REQUEST['question_id'];
                $this->ERROR_COLOR = $this->lfGetErrColor($error, ERR_COLOR);
            }
            // 削除ボタン押下時
        } elseif ($_GET['mode'] == 'delete' && SC_Utils_Ex::sfCheckNumLength($_GET['question_id'])) {
            $sqlval = array('del_flg' => 1);
            $objQuery->update('dtb_question', $sqlval, 'question_id = ?', array($_GET['question_id']));
            $this->reload(null, true);
            // CSVダウンロードボタン押下時
        } elseif ($_GET['mode'] == 'csv' && SC_Utils_Ex::sfCheckNumLength($_GET['question_id'])) {
            require_once CLASS_EX_PATH . "helper_extends/SC_Helper_CSV_Ex.php";
            $objCSV = new SC_Helper_CSV_Ex();
            $head = SC_Utils_Ex::sfGetCSVList($this->arrCVSTITLE);
            $sql = <<<__EOS__
                    SELECT
                         dtb_question_result.result_id
                        ,dtb_question_result.question_id
                        ,dtb_question_result.create_date
                        ,dtb_question.question_name
                        ,dtb_question_result.name01
                        ,dtb_question_result.name02
                        ,dtb_question_result.kana01
                        ,dtb_question_result.kana02
                        ,dtb_question_result.zip01
                        ,dtb_question_result.zip02
                        ,dtb_question_result.pref
                        ,dtb_question_result.addr01
                        ,dtb_question_result.addr02
                        ,dtb_question_result.tel01
                        ,dtb_question_result.tel02
                        ,dtb_question_result.tel03
                        ,dtb_question_result.mail01
                        ,dtb_question_result.question01
                        ,dtb_question_result.question02
                        ,dtb_question_result.question03
                        ,dtb_question_result.question04
                        ,dtb_question_result.question05
                        ,dtb_question_result.question06
                    FROM dtb_question_result
                        LEFT JOIN dtb_question
                            ON dtb_question_result.question_id = dtb_question.question_id
                    WHERE 0=0
                        AND dtb_question_result.del_flg = 0
                        AND dtb_question_result.question_id = ?
                    ORDER BY dtb_question_result.result_id ASC
__EOS__;
            $list_data = $objQuery->getAll($sql, array($_GET['question_id']));
            $data = "";
            for ($i = 0; $i < count($list_data); $i++) {
                // 各項目をCSV出力用に変換する。
                $data .= $objCSV->lfMakeCSV($list_data[$i]);
            }
            // CSVを送信する
            SC_Utils_Ex::sfCSVDownload($head . $data);
            exit;
            // 初回表示 or 編集ボタン押下時
        } else {
            if (!isset($_GET['question_id'])) {
                $_GET['question_id'] = "";
            }
            if (is_numeric($_GET['question_id'])) {
                $sql = "SELECT question FROM dtb_question WHERE question_id = ?";
                $result = $objQuery->getOne($sql, array($_GET['question_id']));
                if ($result) {
                    $_POST = unserialize($result);
                    $this->QUESTION_ID = $_GET['question_id'];
                }
            }
        }
        //各ページ共通
        $this->cnt_question = 6;
        $this->arrActive = $arrActive;
        $this->arrQuestion = $arrQuestion;
        //----　ページ表示
        $objView->assignobj($this);
        $objView->display(MAIN_FRAME);
    }

    function getLogDetail($log_id)
    {
        $sql = <<<END
SELECT
    *
FROM
    dtb_module_update_logs JOIN (
    SELECT
        module_id,
        module_name
    FROM
        dtb_module
    ) AS modules USING(module_id)
WHERE
    log_id = ?
END;
        $objQuery = new SC_Query();
        $arrRet = $objQuery->getAll($sql, array($log_id));
        return isset($arrRet[0]) ? $arrRet[0] : array();
    }

 /**
  * カテゴリ数の登録を行う.
  *
  * @param SC_Query $objQuery SC_Query インスタンス
  * @return void
  */
 function sfCategory_Count($objQuery)
 {
     $sql = "";
     //テーブル内容の削除
     $objQuery->query("DELETE FROM dtb_category_count");
     $objQuery->query("DELETE FROM dtb_category_total_count");
     //各カテゴリ内の商品数を数えて格納
     $sql = " INSERT INTO dtb_category_count(category_id, product_count, create_date) ";
     $sql .= " SELECT T1.category_id, count(T2.category_id), now() ";
     $sql .= " FROM dtb_category AS T1 LEFT JOIN dtb_product_categories AS T2";
     $sql .= " ON T1.category_id = T2.category_id ";
     $sql .= " LEFT JOIN dtb_products AS T3";
     $sql .= " ON T2.product_id = T3.product_id";
     $sql .= " WHERE T3.del_flg = 0 AND T3.status = 1 ";
     $sql .= " GROUP BY T1.category_id, T2.category_id ";
     $objQuery->query($sql);
     //子カテゴリ内の商品数を集計する
     $arrCat = $objQuery->getAll("SELECT * FROM dtb_category");
     $sql = "";
     foreach ($arrCat as $key => $val) {
         // 子ID一覧を取得
         $arrRet = $this->sfGetChildrenArray('dtb_category', 'parent_category_id', 'category_id', $val['category_id']);
         $line = SC_Utils_Ex::sfGetCommaList($arrRet);
         $sql = " INSERT INTO dtb_category_total_count(category_id, product_count, create_date) ";
         $sql .= " SELECT ?, SUM(product_count), now() FROM dtb_category_count ";
         $sql .= " WHERE category_id IN (" . $line . ")";
         $objQuery->query($sql, array($val['category_id']));
     }
 }

 function lfSendRegistMail($registSecretKey)
 {
     $objQuery = new SC_Query();
     $objHelperMail = new SC_Helper_Mail_Ex();
     //-- 姓名を取得
     $sql = "SELECT email, name01, name02 FROM dtb_customer WHERE secret_key = ?";
     $result = $objQuery->getAll($sql, array($registSecretKey));
     $data = $result[0];
     //--　メール送信
     $objMailText = new SC_SiteView();
     $objMailText->assign("CONF", $this->CONF);
     $objMailText->assign("name01", $data["name01"]);
     $objMailText->assign("name02", $data["name02"]);
     $toCustomerMail = $objMailText->fetch("mail_templates/customer_regist_mail.tpl");
     $subject = $objHelperMail->sfMakesubject($objQuery, $objMailText, $this, '会員登録が完了しました。');
     $objMail = new SC_SendMail();
     $objMail->setItem('', $subject, $toCustomerMail, $this->CONF["email03"], $this->CONF["shop_name"], $this->CONF["email03"], $this->CONF["email04"], $this->CONF["email04"]);
     // 宛先の設定
     $name = $data["name01"] . $data["name02"] . " 様";
     $objMail->setTo($data["email"], $name);
     $objMail->sendMail();
 }

 function lfGetAutoIncrementVal($table_name, $colname = "")
 {
     $objQuery = new SC_Query();
     $ret = "";
     if (DB_TYPE == "pgsql") {
         $ret = $objQuery->nextval($table_name, $colname) - 1;
     } else {
         if (DB_TYPE == "mysql") {
             $sql = "SHOW TABLE STATUS LIKE ?";
             $arrData = $objQuery->getAll($sql, array($table_name));
             $ret = $arrData[0]['Auto_increment'];
         }
     }
     return $ret;
 }

 /**
  * テーブルを検索する.
  *
  * 引数に部分一致するテーブル名を配列で返す.
  *
  * @param string $expression 検索文字列
  * @return array テーブル名の配列
  */
 function findTableNames($expression = "")
 {
     $objQuery = new SC_Query();
     $sql = "   SELECT c.relname AS name, " . "     CASE c.relkind " . "     WHEN 'r' THEN 'table' " . "     WHEN 'v' THEN 'view' END AS type " . "     FROM pg_catalog.pg_class c " . "LEFT JOIN pg_catalog.pg_namespace n " . "       ON n.oid = c.relnamespace " . "    WHERE c.relkind IN ('r','v') " . "      AND n.nspname NOT IN ('pg_catalog', 'pg_toast') " . "      AND pg_catalog.pg_table_is_visible(c.oid) " . "      AND c.relname LIKE ?" . " ORDER BY 1,2;";
     $arrColList = $objQuery->getAll($sql, array("%" . $expression . "%"));
     $arrColList = SC_Utils_Ex::sfswaparray($arrColList, false);
     return $arrColList[0];
 }

 /**
  * 全件取得
  */
 function getAll(SC_Query $objQuery = NULL)
 {
     if ($objQuery == null) {
         $objQuery =& SC_Query_Ex::getSingletonInstance();
     }
     $this->setSelect($objQuery->getSql("*", $this->table));
     if (strlen($this->order) == 0) {
         if (GC_Utils_Ex::isFrontFunction()) {
             $this->setOrder("device_name DESC");
         } else {
             $this->setOrder("device_id DESC");
         }
     }
     return $objQuery->getAll($this->getSql(2), $this->arrWhereVal);
 }

 function lfGetNewOrder()
 {
     $objQuery = new SC_Query();
     $sql = "SELECT\n                    ord.order_id,\n                    ord.customer_id,\n                    ord.order_name01 AS name01,\n                    ord.order_name02 AS name02,\n                    ord.total,\n                    ord.create_date,\n                    (SELECT\n                        det.product_name\n                    FROM\n                        dtb_order_detail AS det\n                    WHERE\n                        ord.order_id = det.order_id LIMIT 1\n                    ) AS product_name,\n                    (SELECT\n                        pay.payment_method\n                    FROM\n                        dtb_payment AS pay\n                    WHERE\n                        ord.payment_id = pay.payment_id\n                    ) AS payment_method\n                FROM (\n                    SELECT\n                        order_id,\n                        customer_id,\n                        order_name01,\n                        order_name02,\n                        total,\n                        create_date,\n                        payment_id\n                    FROM\n                        dtb_order AS ord\n                    WHERE\n                        del_flg = 0 AND status <> " . ORDER_CANCEL . "\n                    ORDER BY\n                        create_date DESC LIMIT 10 OFFSET 0\n                ) AS ord";
     $arrRet = $objQuery->getAll($sql);
     return $arrRet;
 }

 /**
  * テーブルのカラム一覧を取得する.
  *
  * @param string $selectTable テーブル名
  * @return array カラム一覧の配列
  */
 function lfGetColumnList($selectTable)
 {
     $objQuery = new SC_Query();
     $arrRet = array();
     // 結果取得用
     $sql = "";
     $sql .= " SELECT column_name, description FROM dtb_table_comment WHERE table_name = ? AND column_name IS NOT NULL";
     $arrRet = $objQuery->getAll($sql, array($selectTable));
     return $arrRet;
 }