File: ModelAbstract.php

<?php

class DB_ModelAbstract {
    
    /**
     * @var DB_DBAbstract PDO&#23454;&#20363;
     */
    public $db;
    
    /**
     * @var string &#34920;&#21069;&#32512;
     */
    public $dbprefix;
    
    const JOIN_LFET = ' LEFT JOIN ';
    const JOIN_RIGHT = ' RIGHT JOIN ';
    const JOIN_INNER = ' INNER JOIN ';
    const JOIN = 'JOIN';
    
    const ORDER_DESC = ' DESC ';
    const ORDER_ASC = ' ASC ';
    
    private $ar_select = '*';
    private $ar_where = '1';
    private $ar_where_bind = array();
    private $ar_join = '';
    private $ar_order = '';
    private $ar_limit = '';
    private $ar_group = '';
    private $ar_having = '';

    private $cache_select = 0;
    private $cache_where = 0;
    private $cache_join = 0;
    private $cache_order = 0;
    private $cache_limit = 0;
    private $cache_group = 0;
    private $cache_having = 0;
    
    private $cache_ar = 0;
    
    protected $end_line = "\r\n";

    private static $instance;

    /**
     * 
     * @param type $config dsn,user,password,charset,prefix
     */
    public function __construct($config=array()) {
        DB_DBAbstract::getInstance($this->db,$config);
        $this->dbprefix = !empty($config) ? $config['prefix']:Yaf_Application::app()->getConfig()->get('db_prefix');
    }

    public function select($fields = array()) {
        //&#23558;`&#34920;&#21517;`.&#23383;&#27573;&#21517; &#22788;&#29702;&#25104; `&#34920;&#21517;`.`&#23383;&#27573;&#21517;`
        if(!empty($fields))
            $this->ar_select = '';
        if(is_string($fields))
            $this->ar_select = $fields;
        else {
            foreach($fields as $v)
                $this->ar_select .= $this->field($v).',';
            $this->ar_select = substr($this->ar_select, 0, -1);
        }
        if($this->cache_ar)
            $this->cache_select = 1;
        return $this;
    }
    
    /**
     * 
     * @param type $table &#24102;&#21069;&#32512;&#30340;&#34920;&#21517;
     * @param type $where &#26465;&#20214; &#34920;&#21517;&#39035;&#24102;&#21069;&#32512;
     * @param type $type
     * @return \DB_ModelAbstract
     */
    public function join($table, $where, $type=self::JOIN_LFET) {
        $this->ar_join .= $type. ''.$table.' ON '.$where;
        if($this->cache_ar)
            $this->cache_join = 1;
        return $this;
    }
    
    /**
     * 
     * @param str $field &#23383;&#27573;&#21517;
     * @param array $arg &#36873;&#39033;&#21015;&#34920;
     * @param bool $not &#26159;&#21542;&#26159;Not&#35821;&#21477;
     * @return string  &#36820;&#22238;  `field` IN ('abc',...)&#35821;&#21477;
     */
    public function where_in($field, $arg=array(), $not=false) {
        $sql = $this->field($field).($not ? ' NOT IN (' : ' IN (');
        $tmp = '';
        foreach ($arg as $item)
            $tmp .= $this->db->quote ($item).',';
        $sql .= substr($tmp, 0, -1);
        return  $sql.') ';
    }
    
    /**
     * 
     * @param type $field
     * @param type $min
     * @param type $max
     * @param bool $not &#26159;&#21542;&#26159;NOT &#35821;&#21477;
     * @return string &#36820;&#22238; `field` BETWEEN min AND max
     */
    public function between($field, $min, $max, $not=false) {
        return $this->field($field).
                ' BETWEEN '.$this->db->quote($min, PDO::PARAM_INT).
                ' AND '.$this->db->quote($max, PDO::PARAM_INT);
    }
    
    public function where($where=' 1', $args=array()) {
        $this->ar_where = $where;
        $this->ar_where_bind = $args;
        if($this->cache_ar)
            $this->cache_where = 1;
        return $this;
    }
    
    public function order($order_by, $type=self::ORDER_ASC) {
        $this->ar_order = ' ORDER BY ';
        if(is_string($order_by))
             $this->ar_order .= $order_by.' '.$type;
        else {
            foreach ($order_by as $field => $type) {
                $this->ar_order .= $field.' '.$type.',';
            }
            $this->ar_order = substr($this->ar_order, 0, -1);
        }
        if($this->cache_ar)
            $this->cache_order = 1;
        return $this;
    }
    
    public function group($group_by) {
        $this->ar_group = ' GROUP BY ';
        if(is_string($group_by))
            $this->ar_group .= $group_by;
        else{
            foreach ($group_by as $field)
                $this->ar_group .= $field.',';
            $this->ar_group = substr($this->ar_group, 0, -1);
        }
        if($this->cache_ar)
            $this->cache_group = 1;
        return $this;
    }
    
    public function limit($num, $offset=0) {
        $this->ar_limit = ' LIMIT '.$offset.','.$num;
        if($this->cache_ar)
            $this->cache_limit = 1;
        return $this;
    }
    
    public function having($condition) {
        $this->ar_having = ' HAVING '.$condition;
        if($this->cache_ar)
            $this->cache_having = 1;
        return $this;
    }
    
    public function trans_start() {
        return $this->db->trans_start();
    }
    
    public function trans_stop() {
        $this->db->trans_stop();
    }
    
    private function prepareGet($table) {
        $stmt = $this->db->prepare('SELECT '.$this->ar_select.' FROM '.$table.$this->ar_join.' WHERE '.$this->ar_where.$this->ar_group.$this->ar_order.$this->ar_having.$this->ar_limit);
        if($stmt===false) {
            $this->onError('Get stmt object failed, sql maybe invalid:'.'SELECT '.$this->ar_select.' FROM '.$table.$this->ar_join.' WHERE '.$this->ar_where.$this->ar_group.$this->ar_order.$this->ar_having.$this->ar_limit);
            return false;
        }
        foreach($this->ar_where_bind as $k=>$v) {
            $stmt->bindValue ($k, $v);
        }
        return $stmt;
    }
    
    private function clearAr() {
        if(!$this->cache_group)
            $this->ar_group = '';
        if(!$this->cache_having)
            $this->ar_having = '';
        if(!$this->cache_join)
            $this->ar_join = '';
        if(!$this->cache_limit)
            $this->ar_limit = '';
        if(!$this->cache_order)
            $this->ar_order = '';
        if(!$this->cache_select)
            $this->ar_select = '*';
        if(!$this->cache_where) {
            $this->ar_where = ' 1';
            $this->ar_where_bind = array();
        }
    }
    
    /**
     * &#24320;&#21551;&#32531;&#23384;
     */
    public function cache_start() {
        $this->cache_ar = 1;
    }
    
    /**
     * &#20851;&#38381;&#32531;&#23384;
     */
    public function cache_stop() {
        $this->cache_ar = 0;
    }
    
    /**
     * &#28165;&#38500;&#32531;&#23384;
     */
    public function cache_flush() {
        $this->ar_group = '';
        $this->ar_having = '';
        $this->ar_join = '';
        $this->ar_limit = '';
        $this->ar_order = '';
        $this->ar_select = '*';
        $this->ar_where = ' 1';
        $this->ar_where_bind = array();
        
        $this->cache_group = 0;
        $this->cache_having = 0;
        $this->cache_join = 0;
        $this->cache_limit = 0;
        $this->cache_order = 0;
        $this->cache_select = 0;
        $this->cache_where = 0;
    }
    
    /**
     * 
     * @param type $table &#39035;&#24102;&#34920;&#21069;&#32512;
     */
    public function get($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $stmt = $this->prepareGet($table);
        if($stmt===false) {
            $this->clearAr();
            return false;
        }
        if($this->queryStmt($stmt)===false) {
            $this->clearAr();
            return false;
        }
        $this->clearAr();
        return $this->procStmt($stmt);
    }
    
    /**
     * &#33719;&#21462;&#31532;&#19968;&#34892;&#31532;&#19968;&#21015;&#30340;&#20540;
     */
    public function getFirst($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $stmt = $this->prepareGet($table);
        if($stmt===false) {
            $this->clearAr();
            return false;
        }
        if($this->queryStmt($stmt)===false) {
            $this->clearAr();
            return false;
        }
        $this->clearAr();
        return $stmt->fetch(PDO::FETCH_COLUMN);
    }
    
    public function getRow($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $stmt = $this->prepareGet($table);
        if($stmt===false) {
            $this->clearAr();
            return false;
        }
        if($this->queryStmt($stmt)===false) {
            $this->clearAr();
            return false;
        }
        $this->clearAr();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * &#26597;&#35810;&#32467;&#26524;&#38598;&#34892;&#25968;
     * @param type $table
     */
    public function count($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $this->ar_select = 'COUNT(*) AS num';
        $stmt = $this->prepareGet($table);
        if($stmt===false) {
            $this->clearAr();
            return false;
        }
        if($this->queryStmt($stmt)===false) {
            $this->clearAr();
            return false;
        }
        $this->clearAr();
        $result = $this->procStmt($stmt, PDO::FETCH_COLUMN);
        return empty($result) ? 0 : (int)$result[0];
    }
    
    /**
     * &#26597;&#35810;&#26159;&#21542;&#26377;&#31526;&#21512;&#26465;&#20214;&#30340;&#35760;&#24405;
     * @param type $table
     */
    public function hasOne($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $this->ar_select = '1';
        $stmt = $this->prepareGet($table);
        if($stmt===false) {
            $this->clearAr ();
            return false;
        }
        if($this->queryStmt($stmt)===false) {
            $this->clearAr();
            return false;
        }
        $this->clearAr();
        $result = $this->procStmt($stmt, PDO::FETCH_COLUMN);
        return empty($result) ? 0 : (int)$result[0];
    }
    
    /**
     * 
     * @param string $table
     * @param array $set array(':username' => Abc, 'fieldname'=>Bcd ) &#38190;&#21517;&#19981;&#20197;:&#24320;&#22836;&#21017;&#19981;&#36827;&#34892;bind
     */
    public function update($table, $set) {
        $sql = 'UPDATE '.$table.$this->ar_join.' SET ';
        foreach ($set as $field => $val) {
            if(substr($field, 0, 1)!==':')
                $sql .= $field.'='.$val.',';
            else {
                $tmp = substr($field, 1);
                $sql .= $this->field($tmp).'='.$field.',';
            }
        }
        $sql = substr($sql, 0, -1);
        $sql .= ' WHERE '.$this->ar_where.$this->ar_order.$this->ar_limit;
        
        $stmt = $this->db->prepare($sql);
        if($stmt===false) {
            $this->onError('Get stmt failed,sql maybe invalid:'.$sql);
            $this->clearAr();
            return false;
        }
        foreach ($set as $field => $val) {
            if(substr($field, 0, 1)===':')
                $stmt->bindValue ($field, $val);
        }
        foreach ($this->ar_where_bind as $key=>$val)
            $stmt->bindValue ($key, $val);
        $this->clearAr();
        return $this->queryStmt($stmt);
    }
    
    public function delete($table=null) {
        if($table===null)
            $table = $this->table ($this::table);
        $stmt = $this->db->prepare('DELETE FROM '.$table.' WHERE '.$this->ar_where.$this->ar_order.$this->ar_limit);
        if($stmt===false) {
            $this->onError('Get stmt failed,sql maybe invalid:'.'DELETE FROM '.$table.' WHERE '.$this->ar_where.$this->ar_order.$this->ar_limit);
            $this->clearAr();
            return false;
        }
        foreach ($this->ar_where_bind as $k=>$v)
            $stmt->bindValue ($k, $v);
        $this->clearAr();
        return $this->queryStmt($stmt);
    }
    
    /**
     * &#25554;&#20837;&#25968;&#25454;
     * @param str $table
     * @param array $set
     * @param bool $multi &#26159;&#21542;&#26159;&#22810;&#34892;&#25554;&#20837;
     * @param bool $dup &#26159;&#21542;&#22312;sql&#26411;&#23614;&#21152;&#20837; on duplicate update&#35821;&#21477;&#65292;&#19982;$replace&#21442;&#25968;&#20914;&#31361;
     */
    public function insert($table, $set, $multi=false, $replace=false, $dup=false) {
        $sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
        $sql .= $table.'(';
        $binds = array();
        $dupSql = ' ON DUPLICATE KEY UPDATE ';
        if($multi) {
            foreach($set as $item) {
                $keys = array_keys($item);
                break;
            }
            foreach ($keys as $v) {
                $v = $this->field($v);
                $sql .= $v.',';
                if($dup)
                    $dupSql .= $v.'=VALUES('.$v.'),';
            }
            $sql = substr($sql, 0, -1).') VALUES ';
            foreach ($set as $item) {
                $sql .= '(';
                foreach ($keys as $v) {
                    $binds[] = $item[$v];
                    $sql .= '?,';
                }
                $sql = substr($sql, 0, -1);
                $sql .= '),';
            }
            $sql = substr($sql, 0, -1);
        }else {
            $tmp = ') VALUES (';
            foreach($set as $field => $val) {
                $field = $this->field($field);
                $sql .= $field.',';
                $tmp .= '?,';
                $binds[] = $val;
                if($dup)
                    $dupSql .= $field.'=VALUES('.$field.'),';
            }
            $sql = substr($sql, 0, -1).  substr($tmp, 0, -1).')';
        }
        if($dup)
            $sql .= substr($dupSql,0,-1);
        $stmt = $this->db->prepare($sql);
        if($stmt===false) {
            $this->onError('Get stmt failed,sql maybe invalid:'.$sql);
            $this->clearAr();
            return false;
        }
        foreach ($binds as $k=>$v)
            $stmt->bindValue (++$k, $v);
        unset($sql, $tmp, $binds);
        $this->clearAr();
        return $this->queryStmt($stmt);
    }
    
    public function last_id() {
        return $this->db->lastInsertId();
    }
    
    public function table($table) {
        return '`'.$this->dbprefix.$table.'`';
    }
    
    /**
     * &#22788;&#29702;&#23383;&#27573;&#21517; , &#21547; * &#21495;&#30340;&#23383;&#27573;&#24573;&#30053;
     * &#23383;&#27573;&#21517; => `&#23383;&#27573;&#21517;`
     * `&#34920;&#21517;`.&#23383;&#27573;&#21517; => `&#34920;&#21517;`.`&#23383;&#27573;&#21517;`
     * COUNT(key) => COUNT(`key`)
     * COUNT(`table`.key) => COUNT(`table`.`key`)
     * @param string $v &#23383;&#27573;&#21517;
     */
    public function field($v) {
        if(strpos($v, '*')!==false) return $v;
        
        $prefix = $suffix = '';
        if(strpos($v, '(')!==false) {
            //&#23383;&#27573;&#37324;&#26377;&#20989;&#25968;(SUM/AVG...)&#25805;&#20316;
            $offsetLeft = strrpos($v, '(')+1;
            $offsetRight = strpos($v, ')');
            $prefix = substr($v, 0, $offsetLeft);
            $suffix = substr($v, $offsetRight);
            $v = substr($v, $offsetLeft, $offsetRight-$offsetLeft);
        }
        return strpos ($v, '.')===false ? 
                $prefix.'`'.$v.'`'.$suffix :
                $prefix.substr($v, 0, strpos ($v, '.')+1).'`'.substr ($v, strpos ($v, '.')+1).'`'.$suffix;
    }
    
    /**
     * 
     * @param PDOStatement $stmt
     * @return bool
     */
    protected function queryStmt(&$stmt) {
        $bool = false;
        try {
            $bool = $stmt->execute();
            if($this->db->trans_started) $this->db->trans_ok = $bool;
            if(!$bool) {
                $info = $stmt->errorInfo();
                $this->onError ($stmt->queryString.'['.$info[1].']'.$info[2].$this->end_line);
            }
        } catch (Exception $ex) {
            if($this->db->trans_started) $this->db->trans_ok = $bool;
            $info = $stmt->errorInfo();
            $this->onError($stmt->queryString.'['.$info[1].']'.$info[2].'['.$ex->getCode().']'.$ex->getMessage().$this->end_line);
        }
        return $bool;
    }
    
    public function setTransOk($ok=true) {
        $this->db->setTransOk($ok);
    }
    
    /**
     * &#22788;&#29702;&#25191;&#34892;sql&#21518;&#30340;PDOStatement &#23545;&#35937;
     * @param PDOStatement $stmt
     */
    protected function procStmt(&$stmt, $mode=PDO::FETCH_ASSOC, $args=array()) {
        if(empty($args))
            return $stmt->fetchAll($mode);
        else
            return $stmt->fetchAll ($mode, $args);
    }

    /**
     * &#25191;&#34892;&#19968;&#26465;&#21333;&#29420;&#30340;sql
     * @param type $sql
     */
    public function query($sql) {
        try {
            $stmt = $this->db->query($sql);
            if($stmt===false) {
                if($this->db->trans_started)
                    $this->db->trans_ok = false;
                $info = $this->db->errorInfo();
                $this->onError('SQL:'.$sql.'['.$info[1].']'.$info[2].$this->end_line);
            }
            return $this->procStmt($stmt);
        } catch (Exception $ex) {
            if($this->db->trans_started) $this->db->trans_ok = $bool;
            $info = $this->db->errorInfo();
            $this->onError('SQL:'.$sql.'['.$info[1].']'.$info[2].'['.$ex->getCode().']'.$ex->getMessage().$this->end_line);
        }
    }
    
    /**
     * &#23558;&#33509;&#24178;&#26465;&#20214;&#25340;&#25509;
     * @param array $cons &#20108;&#32500;&#25968;&#32452; array( array('a=:a', 'OR'), ... )
     * @return string &#36820;&#22238;&#25340;&#25509;&#22909;&#30340;&#35821;&#21477;
     */
    public function buildWhere($cons=array()) {
        $sql = ' 1 ';
        foreach($cons as $con) {
            if(!isset($con[1]))
                $con[] = ' AND ';
            $sql .= $con[1].$con[0];
        }
        return $sql;
    }
    
    public function onError($msg) {
        echo __CLASS__.':'.$msg;
    }
    
    
    /**
     * 
     * @param DB_ModelAbstract $model
     * @return DB_ModelAbstract $instance
     */
    public static function getInstance(&$model=-1,$config=array()) {
        if(!self::$instance)
            self::$instance = new self($config);
        if($model!==-1)
            $model = self::$instance;
        else
            return self::$instance;
    }
}