wsa_server/core/MY_Model.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

//定义一些可复用的功能
class MY_Model extends CI_Model
{
	protected $table = '';
	protected $join = [];//定义以下属性[table,on,type]
	protected $keyword_field = [];//作为关键词搜索的字段
	protected $has_create_time = true;//是否有创建时间create_time
	protected $has_update_time = true;//是否有更新时间update_time
	protected $list_field = 'base.*';//默认列表筛选的字段
	protected $unique_field = null;//不允许重复的字段
	protected $where_field_map = [];//过滤条件字段重命名
	protected $null_field = [];//特定字段的0值填充为null值

	public function __construct()
	{
		$this->load->database();
	}

	function listing($filters = [], $offset, $segment, $select=null, $order_field='base.id',$order='DESC')
	{
		if(empty($select)){
			$select = $this->list_field;
		}
		$this->db->select('SQL_CALC_FOUND_ROWS '.$select,false);
		$this->db->from($this->table.' as base');
		if(!empty($this->join)){
			foreach ($this->join as $_join) {
				$this->db->join($_join['table'], $_join['on'],$_join['type']);
			}
		}
		if(!empty($order_field)){
			$this->db->order_by($order_field,$order);
		}

        //包含筛选条件
        if(!empty($filters)) foreach ($filters as $key=>$val) {
        	if($val === ''){
        		continue;
			}
        	//容易冲突的键
        	if(in_array($key,['id','name','create_time'])){
        		$key = 'base.'.$key;
			}
			//字段别名
			if(isset($this->where_field_map[$key])){
				$key = $this->where_field_map[$key];
			}
			//某些字段的0值填充为null
			if($val == '0' && in_array($key,$this->null_field)){
				$this->db->where("$key is null");
				continue;
			}
            //含有关键词
            if(!empty($this->keyword_field)) {
                if($key === 'title'){
                    //对于关键词，拼合所有筛选条件
                    $likeCriteria = [];
                    foreach ($this->keyword_field as $_field) {
                        $likeCriteria[] = $_field." LIKE '%".$val."%'";
                    }
                    $this->db->where('('.implode(' OR ',$likeCriteria).')');
                    continue;
                }elseif(in_array($key,$this->keyword_field)){
                    $this->db->where($key." LIKE '%".$val."%'");
                    continue;
                }
            }
            //对于数组
            if(is_array($val)){
                $this->db->where("$key BETWEEN {$val[0]} and {$val[1]}");
                continue;
            }
            //含有比较符号
			$op = substr($val,0,1);
			if(in_array($op,['>','<'])){
				$this->db->where($key.' '.$op,substr($val,1));
				continue;
			}
            //普通值
            $this->db->where($key,$val);
            continue;
        }

		$this->db->limit($segment, $offset);
		$query = $this->db->get();
		$result['items'] = $query->result_array();
		//echo $this->db->last_query(); die;
		$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
		$result['total'] = (int)$query->row()->Count;

		return $result;
	}

	public function get($id=false, $field='base.id', $is_list=false, $select=null)
	{
		if(empty($select)){
			$select = $this->list_field;
		}
		$this->db->select($select);
		$this->db->from($this->table.' as base');
		if(!empty($this->join)){
			foreach ($this->join as $_join) {
				$this->db->join($_join['table'], $_join['on'],$_join['type']);
			}
		}
		if($id !== false){
			if(is_array($id)){
				$this->db->where_in($field, $id);
			}else{
				$this->db->where($field, $id);
			}
		}
		$query = $this->db->get();
		if($is_list){
			return $query->result_array();
		}else{
			return $query->row_array();
		}
	}

	//更新单个或多个
	public function update($data, $where, $one_row = true)
	{
		if($one_row){
			if(!empty($this->unique_field)){
				$ret = $this->get($data[$this->unique_field],$this->unique_field);
				if($ret && $ret['id'] != $data['id']){
					return -1;
				}
			}
		}
		if($this->has_update_time){
			$data['update_time'] = empty($data['update_time'])?time():$data['update_time'];
		}
		$this->db->where($where);
		$this->db->update($this->table,$data);
		return $this->db->affected_rows();
	}

	public function add($data)
	{
		if($this->has_create_time){
			$data['create_time'] = empty($data['create_time'])?time():$data['create_time'];
		}
		if(!empty($this->unique_field)){
			$ret = $this->get($data[$this->unique_field],$this->unique_field);
			if($ret){
				return -1;
			}
		}
		$this->db->insert($this->table,$data);
		return $this->db->insert_id();
	}

	function batchAdd($data)
	{
		if(!empty($this->unique_field)){
			$val = array_column($data,$this->unique_field);
			$ret = $this->get($val,$this->unique_field);
			if($ret){
				return $ret[$this->unique_field].'已存在，不能重复添加';
			}
		}
		if($this->has_create_time){
			$time = time();
			foreach ($data as &$item) {
				$item['create_time'] = $time;
			}
		}
		return $this->db->insert_batch($this->table, $data);
	}

	//与批量新建的区别是检测到重复字段则更新
	function batchUpdateInsert($data)
	{
		//需要新增的条目
		$left = [];
		foreach ($data as $item) {
			//有id则更新
			if(!empty($item['id'])){
				$this->update($item,'id = '.$item['id']);
				continue;
			}
			if(!empty($this->unique_field)){
				$val = array_column($item,$this->unique_field);
				$this->get($val,$this->unique_field);
				//存在则更新
				$this->update($item,$this->unique_field.' = "'.$item[$this->unique_field].'"');
				continue;
			}
			$left[] = $item;
		}
		if($this->has_create_time){
			$time = time();
			unset($item);
			foreach ($left as &$item) {
				$item['create_time'] = $time;
			}
		}

		/*$temp1 =  array_keys($left[0]);
		 * 检查字段情况
		foreach ($left as $k=>$item) {
			foreach ($temp1 as $key) {
				if(!in_array($key, array_keys($item))){
					die($k.$key);
				}
			}
		}
		die('');
		*/
		return $this->db->insert_batch($this->table, $left);
	}

	function batchDel($id, $field='id')
	{
		$this->db->where_in($field, $id);
		return $this->db->delete($this->table);
	}

	//获取总数
	function total($filters=[],$join=false)
	{
		$this->db->select('count(*) as count');
		$this->db->from($this->table.' base');
		//支持筛选条件
		if($filters){
			foreach ($filters as $filter) {
				$this->db->where($filter);
			}
		}
		//支持连表
		if($join && !empty($this->join)){
			foreach ($this->join as $_join) {
				$this->db->join($_join['table'], $_join['on'],$_join['type']);
			}
		}
		$query = $this->db->get();
		$row = $query->row_array();
		return $row['count'];
	}

	//按天统计
	function sumByDay($start=0,$end=0)
	{
		$this->db->select('count(*) as count');
		$this->db->from($this->table);
		$this->db->where('create_time>=', $start);
		$this->db->where('create_time<=', $end);
		$this->db->group_by('DATE_FORMAT(from_unixtime(create_time),\'%Y-%m-%d\')');
		$query = $this->db->get();
		return $query->result_array();
	}
}