source: branches/dev/data/class/SC_Query.php @ 13303

<?php
/**
 * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
 *
 * http://www.lockon.co.jp/
 */

// {{{ SC_Query
/**
 *  SC_Query¥¯¥é¥¹
 *
 *  @author     LOCKON CO.,LTD.
 *  @access     public
 */
class SC_Query {
   /**#@+
    * @access private
    */
    
   /**
    * SC_DBConn¥ª¥Ö¥¸¥§¥¯¥È
    * @var SC_DBConn
    */
    var $conn;
    
   /**
    * LIMIT,OFFSET ¶ç
    * @var string
    */
    var $option;
    
   /**
    * WHERE ¶ç
    * @var string
    */
    var $where;
    
   /**
    * GROUP BY ¶ç
    * @var string
    */
    var $groupby;
    
   /**
    * ORDER BY ¶ç
    * @var string
    */
    var $order;
    
    /**#@-*/
    
    /**
     *  SC_Query¥¯¥é¥¹¤Î¥³¥ó¥¹¥È¥é¥¯¥¿
     *
     *  @access public
     *  @param  string  $dsn      DSN¾ðÊó
     *  @param  boolean $err_disp ¥¨¥é¡¼É½¼¨¤ò¹Ô¤¦¤«¤É¤¦¤«
     *  @param  boolean $new      ¿·µ¬¤ËDBÀܳ¤ò¹Ô¤¦¤«¤É¤¦¤«
     */
    function SC_Query($dsn = "", $err_disp = true, $new = false) {
        $this->conn = new SC_DBconn($dsn, $err_disp, $new);
        $this->where   = "";
        $this->option  = "";
        $this->groupby = "";
        return $this->conn; //?
    }
    
    /**
     *  DB¥¨¥é¡¼¤ÎȽÄê
     *
     *  @access public
     *  @return boolean À®¸ù»þ¡§true ¼ºÇÔ»þ¡§false
     */
    function isError() {
        if(PEAR::isError($this->conn->conn)) {
            return true;
        }
        return false;
    }
    
    /**
     *  COUNTʸ¤Î¼Â¹Ô
     *
     *  @access public
     *  @param  string  $table  ¥Æ¡¼¥Ö¥ë̾
     *  @param  string  $where  WHERE¶ç
     *  @param  array   $arrval ¥×¥ì¡¼¥¹¥Û¥ë¥À¤ÎÇÛÎó
     *  @return string  ¥ì¥³¡¼¥É·ï¿ô
     */
    function count($table, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlse = "SELECT COUNT(*) FROM $table";
        } else {
            $sqlse = "SELECT COUNT(*) FROM $table WHERE $where";
        }
        // ¥«¥¦¥ó¥Èʸ¤Î¼Â¹Ô
        $ret = $this->conn->getOne($sqlse, $arrval);
        return $ret;
    }
    
    /**
     *  SELECTʸ¤Î¼Â¹Ô
     *
     *  @access public
     *  @param  string  $col    ¥«¥é¥à̾
     *  @param  string  $table  ¥Æ¡¼¥Ö¥ë̾
     *  @param  string  $where  WHERE¶ç
     *  @param  array   $arrval ¥×¥ì¡¼¥¹¥Û¥ë¥À¤ÎÇÛÎó
     *  @return array   SELECTʸ¤Î¼Â¹Ô·ë²Ì
     */
    function select($col, $table, $where = "", $arrval = array()){
        $sqlse = $this->getsql($col, $table, $where);
        $ret = $this->conn->getAll($sqlse, $arrval);
        return $ret;
    }
    
    /**
     *  ºÇ¸å¤Ë¼Â¹Ô¤·¤¿SQLʸ¤ò¼èÆÀ¤¹¤ë
     *
     *  @access public
     *  @param  boolean $disp SQLʸ¤òprint¤¹¤ë¤«¤É¤¦¤«
     *  @return string  $disp==false¤Î¾ì¹ç¡§ºÇ¸å¤Ë¼Â¹Ô¤·¤¿SQLʸ $disp==true¤Î¾ì¹ç¡§¤Ê¤·
     */
    function getLastQuery($disp = true) {
        $sql = $this->conn->conn->last_query;
        if($disp) { 
            print($sql.";<br />\n");
        }
        return $sql;
    }

    function commit() {
        $this->conn->query("COMMIT");
    }
    
    function begin() {
        $this->conn->query("BEGIN");
    }
    
    function rollback() {
        $this->conn->query("ROLLBACK");
    }
    
    function exec($str, $arrval = array()) {
        $this->conn->query($str, $arrval);
    }

    function autoselect($col, $table, $arrwhere = array(), $arrcon = array()) {
        $strw = "";         
        $find = false;
        foreach ($arrwhere as $key => $val) {
            if(strlen($val) > 0) {
                if(strlen($strw) <= 0) {
                    $strw .= $key ." LIKE ?";
                } else if(strlen($arrcon[$key]) > 0) {
                    $strw .= " ". $arrcon[$key]. " " . $key ." LIKE ?";
                } else {
                    $strw .= " AND " . $key ." LIKE ?";
                }
                
                $arrval[] = $val;
            }
        }
        
        if(strlen($strw) > 0) {
            $sqlse = "SELECT $col FROM $table WHERE $strw ".$this->option;
        } else {
            $sqlse = "SELECT $col FROM $table ".$this->option;
        }
        $ret = $this->conn->getAll($sqlse, $arrval);
        return $ret;
    }
    
    function getall($sql, $arrval = array()) {
        $ret = $this->conn->getAll($sql, $arrval);
        return $ret;
    }

    /**
     *  SELECTʸ¤ò¹½ÃÛ¤¹¤ë
     *
     *  @access public
     *  @param  string  $col    ¥«¥é¥à̾
     *  @param  string  $table  ¥Æ¡¼¥Ö¥ë̾
     *  @param  string  $where  WHERE¶ç
     *  @return string  SQLʸ
     */
    function getsql($col, $table, $where="") {
        if($where != "") {
            // °ú¿ô¤Î$where¤òÍ¥À褷¤Æ¼Â¹Ô¤¹¤ë¡£
            $sqlse = "SELECT $col FROM $table WHERE $where " . $this->groupby . " " . $this->order . " " . $this->option;
        } else {
            if($this->where != "") {
                    $sqlse = "SELECT $col FROM $table WHERE $this->where " . $this->groupby . " " . $this->order . " " . $this->option;
                } else {
                    $sqlse = "SELECT $col FROM $table " . $this->groupby . " " . $this->order . " " . $this->option;
            }
        }
        return $sqlse;
    }
    
    /**
     *  WHERE,GROUPBY,ORDERBY°Ê³°¤Î¥ª¥×¥·¥ç¥Ê¥ë¤Ê¶ç¤ò¥»¥Ã¥È¤¹¤ë
     *
     *  @access public
     *  @param  string  $str ¥ª¥×¥·¥ç¥ó¤Ë»ÈÍѤ¹¤ëʸ»úÎó
     */
    function setoption($str) {
        $this->option = $str;
    }
    
    /**
     *  LIMIT¶ç¡¢OFFSET¶ç¤ò¥»¥Ã¥È¤¹¤ë
     *
     *  @access public
     *  @param  string  $limit  LIMIT¤Î·ï¿ô
     *  @param  integer $offset OFFSET¤Î·ï¿ô
     *  @param  string  $return À¸À®¤·¤¿LIMIT,OFFSET¶ç¤òreturn¤¹¤ë¤«¤É¤¦¤«
     *  @return string  À¸À®¤·¤¿LIMIT,OFFSET¶ç
     */
    function setlimitoffset($limit, $offset = 0, $return = false) {
        if (is_numeric($limit) && is_numeric($offset)){
            
            $option.= " LIMIT " . $limit;
            $option.= " OFFSET " . $offset;
            
            if($return){
                return $option;
            }else{
                $this->option.= $option;
            }
        }
    }
    
    function setgroupby($str) {
        $this->groupby = "GROUP BY " . $str;
    }
    
    function andwhere($str) {
        if($this->where != "") {
            $this->where .= " AND " . $str;
        } else {
            $this->where = $str;
        }
    }
    
    function orwhere($str) {
        if($this->where != "") {
            $this->where .= " OR " . $str;
        } else {
            $this->where = $str;
        }
    }
        
    function setwhere($str) {
        $this->where = $str;
    }
    
    function setorder($str) {
        $this->order = "ORDER BY " . $str;
    }
    
        
    function setlimit($limit){
        if ( is_numeric($limit)){
            $this->option = " LIMIT " .$limit;
        }   
    }
    
    function setoffset($offset) {
        if ( is_numeric($offset)){
            $this->offset = " OFFSET " .$offset;
        }   
    }
    
    
    // INSERTʸ¤ÎÀ¸À®¡¦¼Â¹Ô
    // $table   :¥Æ¡¼¥Ö¥ë̾
    // $sqlval  :Îó̾ => ÃͤγÊǼ¤µ¤ì¤¿¥Ï¥Ã¥·¥åÇÛÎó
    function insert($table, $sqlval) {
        $strcol = '';
        $strval = '';
        $find = false;
        
        if(count($sqlval) <= 0 ) return false;
        
        foreach ($sqlval as $key => $val) {
            $strcol .= $key . ',';
            if(eregi("^Now\(\)$", $val)) {
                $strval .= 'Now(),';
            } else {
                $strval .= '?,';
                if($val != ""){
                    $arrval[] = $val;
                } else {
                    $arrval[] = NULL;
                }
            }
            $find = true;
        }
        if(!$find) {
            return false;
        }
        // ʸËö¤Î","¤òºï½ü
        $strcol = ereg_replace(",$","",$strcol);
        // ʸËö¤Î","¤òºï½ü
        $strval = ereg_replace(",$","",$strval);
        $sqlin = "INSERT INTO $table(" . $strcol. ") VALUES (" . $strval . ")";
        
        // INSERTʸ¤Î¼Â¹Ô
        $ret = $this->conn->query($sqlin, $arrval);
        
        return $ret;        
    }
    
        // INSERTʸ¤ÎÀ¸À®¡¦¼Â¹Ô
    // $table   :¥Æ¡¼¥Ö¥ë̾
    // $sqlval  :Îó̾ => ÃͤγÊǼ¤µ¤ì¤¿¥Ï¥Ã¥·¥åÇÛÎó
    function fast_insert($table, $sqlval) {
        $strcol = '';
        $strval = '';
        $find = false;
        
        foreach ($sqlval as $key => $val) {
                $strcol .= $key . ',';
                if($val != ""){
                    $eval = pg_escape_string($val);
                    $strval .= "'$eval',";
                } else {
                    $strval .= "NULL,";
                }
                $find = true;
        }
        if(!$find) {
            return false;
        }
        // ʸËö¤Î","¤òºï½ü
        $strcol = ereg_replace(",$","",$strcol);
        // ʸËö¤Î","¤òºï½ü
        $strval = ereg_replace(",$","",$strval);
        $sqlin = "INSERT INTO $table(" . $strcol. ") VALUES (" . $strval . ")";
        
        // INSERTʸ¤Î¼Â¹Ô
        $ret = $this->conn->query($sqlin);
        
        return $ret;        
    }
    
    
    // UPDATEʸ¤ÎÀ¸À®¡¦¼Â¹Ô
    // $table   :¥Æ¡¼¥Ö¥ë̾
    // $sqlval  :Îó̾ => ÃͤγÊǼ¤µ¤ì¤¿¥Ï¥Ã¥·¥åÇÛÎó
    // $where   :WHEREʸ»úÎó
    function update($table, $sqlval, $where = "", $arradd = "", $addcol = "") {
        $strcol = '';
        $strval = '';
        $find = false;
        foreach ($sqlval as $key => $val) {
            if(eregi("^Now\(\)$", $val)) {
                $strcol .= $key . '= Now(),';
            } else {
                $strcol .= $key . '= ?,';
                if($val != ""){
                    $arrval[] = $val;
                } else {
                    $arrval[] = NULL;
                }
            }
            $find = true;
        }
        if(!$find) {
            return false;
        }
        
        if($addcol != "") {
            foreach($addcol as $key => $val) {
                $strcol .= "$key = $val,";
            }
        }
                
        // ʸËö¤Î","¤òºï½ü
        $strcol = ereg_replace(",$","",$strcol);
        // ʸËö¤Î","¤òºï½ü
        $strval = ereg_replace(",$","",$strval);
        
        if($where != "") {
            $sqlup = "UPDATE $table SET $strcol WHERE $where";
        } else {
            $sqlup = "UPDATE $table SET $strcol";
        }
        
        if(is_array($arradd)) {
            // ¥×¥ì¡¼¥¹¥Û¥ë¥À¡¼ÍѤËÇÛÎó¤òÄɲÃ
            foreach($arradd as $val) {
                $arrval[] = $val;
            }
        }
        
        // INSERTʸ¤Î¼Â¹Ô
        $ret = $this->conn->query($sqlup, $arrval);
        return $ret;        
    }

    // MAXʸ¤Î¼Â¹Ô
    function max($table, $col, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlse = "SELECT MAX($col) FROM $table";
        } else {
            $sqlse = "SELECT MAX($col) FROM $table WHERE $where";
        }
        // MAXʸ¤Î¼Â¹Ô
        $ret = $this->conn->getOne($sqlse, $arrval);
        return $ret;
    }
    
    // MINʸ¤Î¼Â¹Ô
    function min($table, $col, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlse = "SELECT MIN($col) FROM $table";
        } else {
            $sqlse = "SELECT MIN($col) FROM $table WHERE $where";
        }
        // MINʸ¤Î¼Â¹Ô
        $ret = $this->conn->getOne($sqlse, $arrval);
        return $ret;
    }
    
    // ÆÃÄê¤Î¥«¥é¥à¤ÎÃͤò¼èÆÀ
    function get($table, $col, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlse = "SELECT $col FROM $table";
        } else {
            $sqlse = "SELECT $col FROM $table WHERE $where";
        }
        // SQLʸ¤Î¼Â¹Ô
        $ret = $this->conn->getOne($sqlse, $arrval);
        return $ret;
    }
    
    function getone($sql, $arrval = array()) {
        // SQLʸ¤Î¼Â¹Ô
        $ret = $this->conn->getOne($sql, $arrval);
        return $ret;
        
    }
        
    // °ì¹Ô¤ò¼èÆÀ
    function getrow($table, $col, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlse = "SELECT $col FROM $table";
        } else {
            $sqlse = "SELECT $col FROM $table WHERE $where";
        }
        // SQLʸ¤Î¼Â¹Ô
        $ret = $this->conn->getRow($sqlse, $arrval);
        
        return $ret;
    }
        
    // ¥ì¥³¡¼¥É¤Îºï½ü
    function delete($table, $where = "", $arrval = array()) {
        if(strlen($where) <= 0) {
            $sqlde = "DELETE FROM $table";
        } else {
            $sqlde = "DELETE FROM $table WHERE $where";
        }
        $ret = $this->conn->query($sqlde, $arrval);
        return $ret;
    }
    
    function nextval($table, $colname) {
        $sql = "";
        // postgresql¤Èmysql¤È¤Ç½èÍý¤òʬ¤±¤ë
        if (DB_TYPE == "pgsql") {
            $seqtable = $table . "_" . $colname . "_seq";
            $sql = "SELECT NEXTVAL('$seqtable')";
        }else if (DB_TYPE == "mysql") {
            $sql = "SELECT last_insert_id();";
        }
        $ret = $this->conn->getOne($sql);
        
        return $ret;
    }
    
    function currval($table, $colname) {
        $sql = "";
        if (DB_TYPE == "pgsql") {
            $seqtable = $table . "_" . $colname . "_seq";
            $sql = "SELECT CURRVAL('$seqtable')";
        }else if (DB_TYPE == "mysql") {
            $sql = "SELECT last_insert_id();";
        }
        $ret = $this->conn->getOne($sql);
        
        return $ret;
    }   
    
    function setval($table, $colname, $data) {
        $sql = "";
        if (DB_TYPE == "pgsql") {
            $seqtable = $table . "_" . $colname . "_seq";
            $sql = "SELECT SETVAL('$seqtable', $data)";
            $ret = $this->conn->getOne($sql);
        }else if (DB_TYPE == "mysql") {
            $sql = "ALTER TABLE $table AUTO_INCREMENT=$data";
            $ret = $this->conn->query($sql);
        }
        
        return $ret;
    }       
    
    function query($n ,$arr = "", $ignore_err = false){
        $result = $this->conn->query($n, $arr, $ignore_err);
        return $result;
    }
    
    // auto_increment¤ò¼èÆÀ¤¹¤ë
    function get_auto_increment($table_name){
        // ¥í¥Ã¥¯¤¹¤ë
        $this->BEGIN();
        
        // ¼¡¤ÎIncrement¤ò¼èÆÀ
        $arrRet = $this->getAll("SHOW TABLE STATUS LIKE ?", array($table_name));
        $auto_inc_no = $arrRet[0]["Auto_increment"];
        
        // Ãͤò¥«¥¦¥ó¥È¥¢¥Ã¥×¤·¤Æ¤ª¤¯
        $this->conn->query("ALTER TABLE $table_name AUTO_INCREMENT=?" , $auto_inc_no + 1);
        
        // ²ò½ü¤¹¤ë
        $this->COMMIT();
        
        return $auto_inc_no;
    }
}
// }}}
?>