sphinx/Frame/Package/Database/Common.php

<?php
namespace PhpLife\Frame\Package\Database;

/**
 * 数据库操作类
 * 单表模式
 */
class Common extends \PhpLife\Frame\Package\Database\Single
{

    /**
     * 通过ID获取单条记录
     * 
     * @param string $id            
     * @return array
     */
    public function getOne($id)
    {
        return self::getLine($id);
    }

    /**
     * 通过字段条件获取单条记录
     * 
     * @param array $where
     *            查询条件
     * @param array $fields
     *            要获取的字段信息
     * @return array
     */
    public function getOneByField($where = array(), $fields = array())
    {
        $list = $this->select($fields, $where, '', 1);
        return ! empty($list) ? $list[0] : null;
    }

    /**
     * 通过字段条件获取排序后的限制记录
     * 
     * @param array $where
     *            查询条件
     * @param array $fields
     *            要获取的字段信息
     * @param int $limit
     *            限制记录数
     * @param int $offset
     *            查询偏移量
     * @param array $orderBy
     *            排序 array("field1"=>1,"field2"=>0); 0升序1降序
     * @return array
     */
    public function getList($where = array(), $fields = array(), $limit = null, $offset = null, $orderBy = array())
    {
        return $this->select($fields, $where, '', $limit, $offset, $orderBy);
    }

    /**
     * 复杂语句的计数查询
     * 
     * @param array $where            
     * @return int
     */
    public function countByMuti($where = array())
    {
        $arr = $this->buildWhere($where);
        return $this->count($arr['where'], $arr['whereString']);
    }

    /**
     * 复杂的查询语句
     * 
     * @param array $where   <br>   
     *              1.'key'=>$value 等于的查询条件<br>
     *              2.'key'=>array( '_logic'=>'比较运算符或like' , 'value'=>$value ) '<','<=','>','>=','!=','LIKE'的查询条件<br>
     *              3.'key'=>array($value1,$value2,$value3,...) IN范围查询<br>
     *              4.'key'=>array( array( '_logic'=>'比较运算符或like' , 'value'=>$value ) ,'key'=>$value ) 一个字段多个查询条件<br>      
     * @param array $fields            
     * @param int $limit            
     * @param int $offset            
     * @param array $orderBy            
     * @return array
     */
    public function getListByMuti($where = array(), $fields = array(), $limit = null, $offset = null, $orderBy = array())
    {
        $arr = $this->buildWhere($where);
        return $this->select($fields, $arr['where'], $arr['whereString'], $limit, $offset, $orderBy);
    }
    /**
     * 复杂的查询语句
     *
     * @param array $where   <br>
     *              1.'key'=>$value 等于的查询条件<br>
     *              2.'key'=>array( '_logic'=>'比较运算符或like' , 'value'=>$value ) '<','<=','>','>=','!=','LIKE'的查询条件<br>
     *              3.'key'=>array($value1,$value2,$value3,...) IN范围查询<br>
     *              4.'key'=>array( array( '_logic'=>'比较运算符或like' , 'value'=>$value ) ,'key'=>$value ) 一个字段多个查询条件<br>
     * @param array $fields
     * @param int $limit
     * @param int $offset
     * @param array $orderBy
     * @return array
     */
    public function getAllByMutiple($where = array(), $fields = array(), $orderBy = array(),$limit=null,$offset=null)
    {
        $arr = $this->buildWhere($where);
        return $this->select($fields, $arr['where'], $arr['whereString'], $limit, $offset, $orderBy);
    }
    /**
     * 更新(基于ID)
     * @param int $id
     * @param array $data
     * @return int
     */
    public function update($id, $data)
    {
        return parent::update($data, array('id'=>$id));
    }

    /**
     * 复杂的更新语句
     * 
     * @param array $set     
     *            1.'key'=>$value 等于参数设置
     *            2.'key'=>array( '_logic'=>'运算符','value'=>$value ) 含 '+', '-','*','/','%'运算符号的参数设置       
     * @param array $where            
     * @return bool|int
     */
    public function updateByMuti($set = array(), $where = array())
    {
        $wArr = $this->buildWhere($where);
        $sArr = $this->buildSet($set);
        $sql = " UPDATE `" . $this->tableName . "` SET " . $sArr['setString'] . ' WHERE ' . $wArr['whereString'];
        $prepared = array_merge($wArr['where'], $sArr['set']);
        return $this->write($sql, $prepared);
    }

    /**
     * 将复杂的查询条件转换为PDO类可解读的参数绑定方式
     * 
     * @param array $map
     *            条件
     *            $map允许的键值格式（所有的条件是AND组合的）：
     *            1.'key'=>$value 等于的查询条件
     *            2.'key'=>array( '_logic'=>'比较运算符或like' , 'value'=>$value ) '<','<=','>','>=','!=','LIKE'的查询条件
     *            3.'key'=>array($value1,$value2,$value3,...) IN范围查询
     *            4.'key'=>array( array( '_logic'=>'比较运算符或like' , 'value'=>$value ) ,'key'=>$value ) 一个字段多个查询条件
     * @return array
     */
    public function buildWhere($map)
    {
        $fhArr = array(
            '<',
            '<=',
            '>',
            '>=',
            '!=',
            'LIKE'
        );
        $where = array();
        $whereString = array();
        foreach ($map as $k => $v) {
            if (is_array($v)) {
                if (! empty($v['_logic'])) {
                    $v['_logic'] = strtoupper($v['_logic']);
                    if (in_array($v['_logic'], $fhArr)) {
                        $whereString[] = self::escapeKey($k) . ' ' . $v['_logic'] . ' ' . self::replaceKey($k, 'wh_') . '';
                        $where[self::replaceKey($k, 'wh_')] = $v['value'];
                    }
                } else {
                    $mixArr = array();
                    foreach ($v as $ke => $va) {
                        if (is_array($va)) {
                            if (! empty($va['_logic'])) {
                                $va['_logic'] = strtoupper($va['_logic']);
                                if (in_array($va['_logic'], $fhArr)) {
                                    $whereString[] = self::escapeKey($k) . ' ' . $va['_logic'] . ' ' . self::replaceKey($k . $ke, 'wh_') . '';
                                    $where[self::replaceKey($k . $ke, 'wh_')] = $va['value'];
                                }
                            }
                        } else {
                            $mixArr[] = $va;
                        }
                    }
                    if (! empty($mixArr)) {
                        $string = self::escapeKey($k) . ' IN ( ';
                        $first = '';
                        foreach ($mixArr as $ke => $va) {
                            $string .= $first . self::replaceKey($k . $ke, 'wh_');
                            $where[self::replaceKey($k . $ke, 'wh_')] = $va;
                            $first = ',';
                        }
                        $string .= ' ) ';
                        $whereString[] = $string;
                    }
                }
            } else {
                $whereString[] = self::escapeKey($k) . ' = ' . self::replaceKey($k, 'wh_');
                $where[self::replaceKey($k, 'wh_')] = $v;
            }
        }
        return array(
            'where' => $where,
            'whereString' => implode(' AND ', $whereString)
        );
    }

    /**
     * 将复杂的更新条件转换为PDO类可解读的参数绑定方式
     * 
     * @param array $map
     *            参数设置
     *            $map设置参数的格式：
     *            1.'key'=>$value 等于参数设置
     *            2.'key'=>array( '_logic'=>'运算符','value'=>$value ) 含 '+', '-','*','/','%'运算符号的参数设置
     * @return array()
     */
    public function buildSet($map)
    {
        if (! is_array($map) || empty($map)) {
            return '';
        }
        $setString = array();
        $set = array();
        foreach ($map as $k => $v) {
            if (is_array($v)) {
                if (! empty($v['_logic'])) {
                    if (in_array($v['_logic'], array(
                        '+',
                        '-',
                        '*',
                        '/',
                        '%'
                    ))) {
                        $setString[] = self::escapeKey($k) . ' = ' . self::escapeKey($k) . ' ' . $v['_logic'] . ' ' . self::replaceKey($k, 'st_');
                        $set[self::replaceKey($k, 'st_')] = $v['value'];
                    }
                }
            } else {
                $setString[] = self::escapeKey($k) . '=' . self::replaceKey($k, 'st_');
                $set[self::replaceKey($k, 'st_')] = $v;
            }
        }
        return array(
            'set' => $set,
            'setString' => implode(' , ', $setString)
        );
    }

    /**
     * 安全过滤key
     * 
     * @param string $key            
     * @return string
     */
    protected function escapeKey($key)
    {
        $key = str_replace(array(
            '`',
            ' '
        ), '', $key);
        return ' `' . addslashes(stripslashes($key)) . '` ';
    }

    /**
     * 参数绑定value
     * 
     * @param string $key            
     * @return string
     */
    protected function replaceKey($key, $prefix = '')
    {
        return ':' . $prefix . $key;
    }

  
}