PHP SC_Query类代码示例

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     global $objCampaignSess;
     $objView = new SC_SiteView();
     $objQuery = new SC_Query();
     $objCampaignSess = new SC_CampaignSession();
     // キャンペーンからの登録の場合の処理
     if ($_GET["cp"] != "") {
         $arrCampaign = $objQuery->select("directory_name", "dtb_campaign", "campaign_id = ?", array($_GET["cp"]));
         // キャンペーンディレクトリ名を保持
         $dir_name = $arrCampaign[0]['directory_name'];
     } else {
         $dir_name = "";
     }
     // レイアウトデザインを取得
     $helper = new SC_Helper_PageLayout_Ex();
     $helper->sfGetPageLayout($this, false, DEF_LAYOUT);
     $objView->assignobj($this);
     // フレームを選択(キャンペーンページから遷移なら変更)
     if ($this->dir_name != "") {
         $objView->display(CAMPAIGN_TEMPLATE_PATH . $dir_name . "/active/site_frame.tpl");
         $objCampaignSess->delCampaign();
     } else {
         $objView->display(SITE_FRAME);
     }
 }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_SiteView(false);
     $objQuery = new SC_Query();
     // 正しく値が取得できない場合はキャンペーンTOPへ
     if ($_GET['campaign_id'] == "" || $_GET['status'] == "") {
         $this->sendRedirect($this->getLocation(URL_CAMPAIGN_TOP));
         exit;
     }
     // statusの判別
     switch ($_GET['status']) {
         case 'active':
             $status = CAMPAIGN_TEMPLATE_ACTIVE;
             break;
         case 'end':
             $status = CAMPAIGN_TEMPLATE_END;
             break;
         default:
             $status = CAMPAIGN_TEMPLATE_ACTIVE;
             break;
     }
     // ディレクトリ名を取得名
     $directory_name = $objQuery->get("dtb_campaign", "directory_name", "campaign_id = ?", array($_GET['campaign_id']));
     $template_dir = CAMPAIGN_TEMPLATE_PATH . $directory_name . "/" . $status . "preview.tpl";
     //----　ページ表示
     $objView->assignobj($this);
     $objView->display($template_dir);
 }

function lfGetCSVData()
{
    global $arrAUTHORITY;
    global $arrWORK;
    $oquery = new SC_Query();
    $cols = "authority,name,department,login_id,work";
    $oquery->setwhere("del_flg <> 1");
    $oquery->andwhere("member_id <> " . ADMIN_ID);
    $oquery->setoption("ORDER BY rank DESC");
    $list_data = $oquery->select($cols, "dtb_member");
    $max = count($list_data);
    for ($i = 0; $i < $max; $i++) {
        $line = "";
        $line .= "\"" . $arrAUTHORITY[$list_data[$i]['authority']] . "\",";
        $tmp = ereg_replace("\"", "\"\"", $list_data[$i]['name']);
        $line .= "\"" . $tmp . "\",";
        $tmp = ereg_replace("\"", "\"\"", $list_data[$i]['department']);
        $line .= "\"" . $tmp . "\",";
        $tmp = ereg_replace("\"", "\"\"", $list_data[$i]['login_id']);
        $line .= "\"" . $tmp . "\",";
        $line .= "\"" . $arrWORK[$list_data[$i]['work']] . "\"\n";
        $data .= $line;
    }
    $header = "\"権限\",\"名前\",\"所属\",\"ログインID\",\"稼働状況\"\n";
    return $header . $data;
}

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

 /**
  * SC_DB_MasterData::getMasterData() のテストケース
  */
 function testGetMasterData()
 {
     $columns = array('id', 'name', 'rank');
     $masterData = new SC_DB_MasterData_Ex();
     $actual = $masterData->getMasterData('mtb_pref', $columns);
     $objQuery = new SC_Query();
     $objQuery->setorder($columns[2]);
     $results = $objQuery->select($columns[0] . ", " . $columns[1], 'mtb_pref');
     $expected = array();
     foreach ($results as $result) {
         $expected[$result[$columns[0]]] = $result[$columns[1]];
     }
     $this->assertEquals($expected, $actual);
 }

    /**
     * デストラクタ.
     *
     * @return void
     */
    function lfGetRecommendProducts($product_id)
    {
        $objQuery = new SC_Query();
        $cols = '*, (SELECT COUNT(*) FROM dtb_order_detail WHERE product_id = alldtl.product_id) AS cnt';
        $from = 'vw_products_allclass_detail AS alldtl';
        $where = <<<__EOS__
            del_flg = 0
            AND status = 1
            AND product_id IN (
                SELECT product_id
                FROM
                    dtb_order_detail
                    INNER JOIN dtb_order
                        ON dtb_order_detail.order_id = dtb_order.order_id
                WHERE 0=0
                    AND dtb_order.del_flg = 0
                    AND dtb_order.order_id IN (
                        SELECT order_id
                        FROM dtb_order_detail
                        WHERE 0=0
                            AND product_id = ?
                    )
                    AND dtb_order_detail.product_id <> ?
            )
__EOS__;
        $objQuery->setorder('cnt DESC, RANDOM()');
        $objQuery->setlimit($this->max);
        $recommendProducts = $objQuery->select($cols, $from, $where, array($product_id, $product_id));
        return $recommendProducts;
    }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (SC_Utils_Ex::sfIsInt($_GET['send_id'])) {
         $objQuery = new SC_Query();
         $col = "subject, mail_body";
         $where = "send_id = ?";
         $arrRet = $objQuery->select($col, "dtb_mail_history", $where, array($_GET['send_id']));
         $this->tpl_subject = $arrRet[0]['subject'];
         $this->tpl_body = $arrRet[0]['mail_body'];
     }
     $objView->assignobj($this);
     $objView->display($this->tpl_mainpage);
 }

 function setUp()
 {
     $this->objQuery =& SC_Query::getSingletonInstance();
     $this->objQuery->begin();
     $this->objPage = new LC_Page_Admin_Products_ProductClass_Mock();
     $this->objPage->init();
     $this->now = "2010-01-01 00:00:00";
 }

 /**
  * Sets up the fixture, for example, opens a network connection.
  * This method is called before a test is executed.
  */
 protected function setUp()
 {
     $this->objQuery =& SC_Query::getSingletonInstance();
     $this->objQuery->begin();
     $arrRet = $this->objQuery->getOne('SELECT MAX(customer_id) FROM dtb_customer');
     $this->customer_id = $arrRet;
     $this->objAddress = new SC_Helper_Address_Ex();
 }

 /**
  * 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);
 }

    /**
     * SC_Queryインスタンスに設定された検索条件をもとに対象商品数を取得する.
     *
     * 検索条件は, SC_Query::setWhere() 関数で設定しておく必要があります.
     *
     * @param SC_Query $objQuery SC_Query インスタンス
     * @param array $arrVal 検索パラメーターの配列
     * @return array 対象商品ID数
     */
    public function findProductCount(&$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('P.product_id, A.name, B.name');
        return $objQuery->get('COUNT(DISTINCT(alldtl.product_id))', $table, '', $arrVal);
    }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_SiteView();
     $objCartSess = new SC_CartSession("", false);
     $objDb = new SC_Helper_DB_Ex();
     // 管理ページからの確認の場合は、非公開の商品も表示する。
     if (isset($_GET['admim']) && $_GET['admin'] == 'on') {
         $where = "del_flg = 0";
     } else {
         $where = "del_flg = 0 AND status = 1";
     }
     // 値の正当性チェック
     if (!SC_Utils_Ex::sfIsInt($_GET['product_id']) || !$objDb->sfIsRecord("dtb_products", "product_id", $_GET['product_id'], $where)) {
         SC_Utils_Ex::sfDispSiteError(PRODUCT_NOT_FOUND);
     }
     $image_key = $_GET['image'];
     $objQuery = new SC_Query();
     // カラムが存在していなければエラー画面を表示
     if (!$objDb->sfColumnExists("dtb_products", $image_key)) {
         SC_Utils_Ex::sfDispSiteError(PRODUCT_NOT_FOUND);
     }
     $col = "name, {$image_key}";
     $arrRet = $objQuery->select($col, "dtb_products", "product_id = ?", array($_GET['product_id']));
     $image_path = IMAGE_SAVE_DIR . $arrRet[0][$image_key];
     if (file_exists($image_path)) {
         list($width, $height) = getimagesize($image_path);
     } else {
         $width = 0;
         $height = 0;
     }
     $this->tpl_width = $width;
     $this->tpl_height = $height;
     $this->tpl_table_width = $this->tpl_width + 20;
     $this->tpl_table_height = $this->tpl_height + 20;
     $this->tpl_image = $arrRet[0][$image_key];
     $this->tpl_name = $arrRet[0]['name'];
     $objView->assignobj($this);
     $objView->display($this->tpl_mainpage);
 }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     //---- ページ初期設定
     $conn = new SC_DBConn();
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objDate = new SC_Date();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (!isset($_GET['send_id'])) {
         $_GET['send_id'] = "";
     }
     if (!isset($_GET['mode'])) {
         $_GET['mode'] = "";
     }
     if (!isset($_POST['search_pageno'])) {
         $_POST['search_pageno'] = "";
     }
     // 削除時
     if (SC_Utils_Ex::sfCheckNumLength($_GET['send_id']) && $_GET['mode'] == 'delete') {
         $sql = "UPDATE dtb_send_history SET del_flg = 1 WHERE send_id = ?";
         $conn->query($sql, array($_GET['send_id']));
         $_SERVER['QUERY_STRING'] = "";
         $this->reload();
     }
     $col = "*";
     $from = "dtb_send_history";
     $where = " del_flg = ?";
     $arrval[] = "0";
     $objQuery = new SC_Query();
     // 行数の取得
     $linemax = $objQuery->count($from, $where, $arrval);
     $this->tpl_linemax = $linemax;
     // 何件が該当しました。表示用
     // ページ送りの取得
     $objNavi = new SC_PageNavi($_POST['search_pageno'], $linemax, SEARCH_PMAX, "fnNaviSearchPage", NAVI_PMAX);
     $this->tpl_strnavi = $objNavi->strnavi;
     // 表示文字列
     $startno = $objNavi->start_row;
     // 取得範囲の指定(開始行番号、行数のセット)
     $objQuery->setlimitoffset(SEARCH_PMAX, $startno);
     // 表示順序
     $order = "start_date DESC, send_id DESC";
     $objQuery->setorder($order);
     // 検索結果の取得
     $this->arrDataList = $objQuery->select($col, $from, $where, $arrval);
     //---- ページ表示
     $objView->assignobj($this);
     $objView->display(MAIN_FRAME);
 }

 /**
  * dtb_module に WebPay のエントリを挿入する
  *
  * 初期化用。インストール時に実行する
  * @return boolean テーブルを更新したときは true
  */
 public static function insert()
 {
     $objQuery = SC_Query::getSingletonInstance();
     $objQuery->setLimit(1);
     $arrModule = $objQuery->select('module_id, sub_data', 'dtb_module', 'module_code = ?', array(MDL_WEBPAY_CODE));
     if ($arrModule !== NULL && !empty($arrModule)) {
         return false;
     }
     $arrVal = array('module_id' => MDL_WEBPAY_ID, 'module_code' => MDL_WEBPAY_CODE, 'module_name' => 'WebPay決済モジュール', 'auto_update_flg' => 0, 'del_flg' => 0, 'create_date' => 'CURRENT_TIMESTAMP', 'update_date' => 'CURRENT_TIMESTAMP');
     if ($objQuery->insert('dtb_module', $arrVal) !== 1) {
         die('WebPayモジュールの初期化に失敗しました');
     }
     return true;
 }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $conn = new SC_DBConn();
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objQuery = new SC_Query();
     $objDb = new SC_Helper_DB_Ex();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     switch ($_POST['mode']) {
         case 'delete':
             // ランク付きレコードの削除
             $objDb->sfDeleteRankRecord("dtb_deliv", "deliv_id", $_POST['deliv_id']);
             // 再表示
             SC_Utils_Ex::sfReload();
             break;
         case 'up':
             $objDb->sfRankUp("dtb_deliv", "deliv_id", $_POST['deliv_id']);
             // 再表示
             SC_Utils_Ex::sfReload();
             break;
         case 'down':
             $objDb->sfRankDown("dtb_deliv", "deliv_id", $_POST['deliv_id']);
             // 再表示
             SC_Utils_Ex::sfReload();
             break;
         default:
             break;
     }
     // 配送業者一覧の取得
     $col = "deliv_id, name, service_name";
     $where = "del_flg = 0";
     $table = "dtb_deliv";
     $objQuery->setorder("rank DESC");
     $this->arrDelivList = $objQuery->select($col, $table, $where);
     $objView->assignobj($this);
     $objView->display(MAIN_FRAME);
 }