boniu/lib/table/table.class.php

<?php

/**
 * 数据库表抽象类
 *
 * 定义必须实现的抽象方法
 * 定义可以继承的方法，提高table层的书写效率
 *
 * @createtime	2018/03/01
 * @author      空竹
 * @copyright	芝麻开发(http://www.zhimawork.com)
 */

abstract class Table {

	protected $table_name       = '';//表名，不带前缀，前缀在config中定义
	protected $table_id         = '';//指定ID字段名称，必须
	protected $table_status     = '';//指定状态字段名称，如果有
	protected $table_order      = '';//指定排序字段名称，如果有
	protected $pdo;
	protected $table_fullname;
	protected $table_struct;

	function __construct(){

		//初始数据库连接PDO
		global $mypdo;
		$this->pdo = $mypdo;
		$table_prefix = $this->pdo->prefix;
		
		//数据库全名
		$this->table_fullname = $table_prefix . $this->table_name;

		//数据库结构
		$this->table_struct = $this->struct();
		
	}
	
	////////////////////////////////////////////////////
	/**************抽象方法，子类必须实现**************/
	////////////////////////////////////////////////////
	//数据库结构
	//完整展示数据库所有字段及其含义
	//映射字段名称提供给其他层使用，不对外暴露数据库字段
	abstract protected function struct();

	//增
	//@param $attr array -- 键值同struct()返回的数组
	abstract public function add($attr);


	///////////////////////////////////////////////////////////////
	/**************非抽象方法，用于继承，也可以重载***************/
	///////////////////////////////////////////////////////////////
	//获取详情
	public function getInfoById($id){

		//查询语句必须用sql_check_input检查参数
		$id = $this->pdo->sql_check_input(array('number', $id));
        $sql = "select * from ". $this->table_fullname ." where ". $this->table_id ." = $id limit 1";
        $rs = $this->pdo->sqlQuery($sql);
        $r  = array();
		if($rs){
            foreach($rs as $key => $val){
                $r[$key] = $this->dataToAttr($val);
            }
            return $r[0];
        }else{
            return $r;
        }
	}

    //此项目每个表都有code字段，所以放在基类里面
    public function getInfoByCode($code){

        //查询语句必须用sql_check_input检查参数
        $code = trim($code);
        $code = $this->pdo->sql_check_input(array('string', $code));

        $sql = "select * from ". $this->table_fullname ." where ".$this->table_name."_code = $code limit 1";

        $rs = $this->pdo->sqlQuery($sql);
        $r  = array();
        if($rs){
            foreach($rs as $key => $val){
                $r[$key] = $this->dataToAttr($val);
            }
            return $r[0];
        }else{
            return $r;
        }
    }


    //此项目每个表都有code字段，大部分表有date字段，放在基类里面统一处理
    public function getInfoByCodeAndDate($code, $date){

        //查询语句必须用sql_check_input检查参数
        $code = trim($code);
        $code = $this->pdo->sql_check_input(array('string', $code));

        //查询语句必须用sql_check_input检查参数
        $date = trim($date);
        $date = $this->pdo->sql_check_input(array('number', $date));

        $sql = "select * from ". $this->table_fullname ." where ".$this->table_name."_code = $code and ".$this->table_name."_date = $date limit 1";

        $rs = $this->pdo->sqlQuery($sql);
        $r  = array();
        if($rs){
            foreach($rs as $key => $val){
                $r[$key] = $this->dataToAttr($val);
            }
            return $r[0];
        }else{
            return $r;
        }
    }


    //获取列表（分页）
    //$count、$page和$pagesize都为0时，返回全部结果（适用于无需分页的情况）
    //
    //@param $filter array -- 过滤条件，格式见Table::filterToWhere
    //@param $count -- 0：返回列表 1：返回结果数量
    //@param $page -- 当前第几页
    //@param $pagesize -- 每页数量
    public function getList($filter = array(), $count = 0, $page = 0, $pagesize = 0, $order = 0){
        $where = $this->filterToWhere($filter);
        $orderByStr = (empty($order) ? 'asc' : 'desc');

        if($count == 0){//列表
            $sql = "select * from ". $this->table_fullname ." $where order by ".$this->table_id." ".$orderByStr;

            if($page > 0){//分页
                $startrow = ($page - 1) * $pagesize;
                $sql_limit = " limit $startrow, $pagesize";
                $sql .= $sql_limit;
            }

            $rs  = $this->pdo->sqlQuery($sql);
            $r   = array();
            if($rs){
                foreach($rs as $key => $val){
                    $r[$key] = $this->dataToAttr($val);
                }
                return $r;
            }else{
                return $r;
            }

        }else{//统计
            $sql = "select count(*) as c from ". $this->table_fullname . " $where ";
            $rs  = $this->pdo->sqlQuery($sql);
            if($rs){
                return $rs[0]['c'];
            }else{
                return 0;
            }
        }
    }

    //获取列表（分页）
    //$count、$page和$pagesize都为0时，返回全部结果（适用于无需分页的情况）
    //
    //@param $filter array -- 过滤条件，格式见Table::filterToWhere
    //@param $count -- 0：返回列表 1：返回结果数量
    //@param $page -- 当前第几页
    //@param $pagesize -- 每页数量
    //本函数与 getList 区别在于，使用date字段进行排序，没有date字段的表不能使用本函数
    public function getListOrderByDate($filter = array(), $count = 0, $page = 0, $pagesize = 0, $order = 0){
        $where = $this->filterToWhere($filter);
        $orderByStr = (empty($order) ? 'asc' : 'desc');

        if($count == 0){//列表
            $sql = "select * from ". $this->table_fullname ." $where order by ".$this->table_name."_date ".$orderByStr;

            if($page > 0){//分页
                $startrow = ($page - 1) * $pagesize;
                $sql_limit = " limit $startrow, $pagesize";
                $sql .= $sql_limit;
            }

            $rs  = $this->pdo->sqlQuery($sql);
            $r   = array();
            if($rs){
                foreach($rs as $key => $val){
                    $r[$key] = $this->dataToAttr($val);
                }
                return $r;
            }else{
                return $r;
            }

        }else{//统计
            $sql = "select count(*) as c from ". $this->table_fullname . " $where ";
            $rs  = $this->pdo->sqlQuery($sql);
            if($rs){
                return $rs[0]['c'];
            }else{
                return 0;
            }
        }
    }

	//删
	public function del($id){
		$where = array(
			$this->table_id => array('number', $id)
		);
        
        return $this->pdo->sqldelete($this->table_fullname, $where);
	}
	
	//--------------------------------------------------
	//------------未实现的常见方法，便于重载------------
	//--------------------------------------------------

	//改
	//@param $attr 数组，键值参考add()
	public function edit($id, $attr){}

    //修改指定字段
    public function update($id, $attrs)
    {
        $params = array();
        foreach ($attrs as $key => $value) {
            //$type = self::getTypeByAttr($key);
            $params[$this->table_name.'_'.$key] = array('string', $value);

        }
        //where条件
        $where = array( $this->table_id => array("number", $id));
        //返回结果
        $r = $this->pdo->sqlupdate($this->table_fullname, $params, $where);
        return $r;
    }

    //code不存在则add，否则update
    public function addOrUpdateByCode($attrs)
    {
        if (empty($attrs) || empty($attrs['code'])) {
            return 0;
        }

        $rs = $this->getInfoByCode($attrs['code']);
        if (empty($rs)) {
            $id = $this->add($attrs);
        } else {
            $id = $rs['id'];
            $this->update($id, $attrs);
        }
        return $id;
    }

    //(code,date)为唯一标识，不存在则add，否则update
    public function addOrUpdateByCodeDate($attrs)
    {
        if (empty($attrs) || empty($attrs['code']) || empty($attrs['date'])) {
            return 0;
        }

        $rs = $this->getInfoByCodeAndDate($attrs['code'], $attrs['date']);
        if (empty($rs)) {
            $id = $this->add($attrs);
        } else {
            $id = $rs['id'];
            $this->update($id, $attrs);
        }
        return $id;
    }

	//--------------------------------------------------
	//------------依赖于字段设置的常见方法，可以重载----
	//--------------------------------------------------
	//单独修改状态
	public function updateStatus($id, $status){
        
        $where = array(
			$this->table_id      => array('number', $id)
		);
		$param = array(
			$this->table_status  => array('number', $status)
		);
        return $this->pdo->sqlupdate($this->table_fullname, $param, $where);
	
	}
	
	//单独修改排序
	public function updateOrder($id, $order){
        
        $where = array(
			$this->table_id      => array('number', $id)
		);
		$param = array(
			$this->table_order   => array('number', $order)
		);
        return $this->pdo->sqlupdate($this->table_fullname, $param, $where);
	}
	
	//--------------------------------------------------
	//------------其他方法------------------------------
	//--------------------------------------------------
	
	//从数据库取出的数据转化键值后输出
	protected function dataToAttr($data){
		$r = array();
		foreach($this->table_struct as $k => $v){

			$r[$k] = $data[$v];
		}
		return $r;
	}

	//把filter转化为where子句
	//仅支持最常见的=和like，不支持其他符号
	//@param $filter array 键值要符合struct()中的定义
	//----参数示例----
	# $filter = array(
	#	'account' => array('abc', '=s'),//精确搜索
	#	'title'   => array('xyz', '%s'),//模糊搜索
	#	'desc'    => 'test',//不指定则为精确搜索
	#	'type'    => 1
	# )
	//----参数示例结束----
	protected function filterToWhere($filter){
		
		$struct = $this->table_struct;
		$where = ' where 1=1 ';

		foreach($filter as $k => $v){
			if(is_array($v)){
				$val = $v[0];
				$operator = $v[1];
				$extraParam = $v[2];      //仅自定义的特殊方法使用
			}else{
				$val = $v;
				$operator = '=s';
			}
			$field_name = $struct[$k];

			if($operator == '=n'){//数字
				$val = $this->pdo->sql_check_input(array('number', $val));
				$where .= " and $field_name = $val ";
			}
			if($operator == '=s'){//字符串，精确搜索
				$val = $this->pdo->sql_check_input(array('string', $val));
				$where .= " and $field_name = $val ";
			}
            if($operator == '>n'){//大于数字
                $val = $this->pdo->sql_check_input(array('number', $val));
                $where .= " and $field_name > $val ";
            }
            if($operator == '>=n'){//大于等于数字
                $val = $this->pdo->sql_check_input(array('number', $val));
                $where .= " and $field_name >= $val ";
            }
            if($operator == '<n'){//小于数字
                $val = $this->pdo->sql_check_input(array('number', $val));
                $where .= " and $field_name < $val ";
            }
            if($operator == '<=n'){//小于等于数字
                $val = $this->pdo->sql_check_input(array('number', $val));
                $where .= " and $field_name <= $val ";
            }
			if($operator == '%s'){//字符串，模糊搜索
				$val = '%'.$val.'%';
				$val = $this->pdo->sql_check_input(array('string', $val));
				$where .= " and $field_name like $val " ;
			}

            if($operator == '=n_arr'){//or 连接的数字数组
                $where .= " and (";
			    $first = true;
			    foreach ($val as $item) {
			        if ($first) {
                        $where .= " $field_name = $item ";
                        $first = false;
                    } else {
                        $where .= " or $field_name = $item ";
                    }
                }
                $where .= ") ";
            }

            if($operator == '=date'){//时间戳在当天
                $val = $this->pdo->sql_check_input(array('number', $val));
                if (empty($val)) {
                    break;
                }
                $begin = $val;    //不分时区
                $end = $val + 24*60*60;
                $where .= " and ($field_name >= $begin and $field_name < $end) ";
            }

            if($operator == 'date2date'){//时间戳在两天之间
                if (!empty($val[0])) {
                    $begin = $val[0] - 8*60*60;    //东8时区
                    $where .= " and $field_name >= $begin ";
                }
                if (!empty($val[1])) {
                    $end = $val[1] + 16*60*60;
                    $where .= " and $field_name < $end ";
                }
            }

            if($operator == 'id_in_arr'){//id在数组中
                $where .= " and (1=0 ";
			    foreach ($val as $tempId) {
                    $tempId = $this->pdo->sql_check_input(array('number', $tempId));
                    $where .= " or $field_name=$tempId ";
                }
                $where .= ") ";
            }

            //以下是本项目特殊处理方法
            if($operator == 'date_between'){//date在两个日期之间
                $val = $this->pdo->sql_check_input(array('number', $val));
                $extraParam = $this->pdo->sql_check_input(array('number', $extraParam));
                $where .= " and $field_name > $extraParam and $field_name <= $val ";
            }

		}
		return $where;
	}
}
?>