File: /www/wwwroot/www.sanjiangapp.com/core/basic/Model.php
<?php
/**
* @copyright (C)2016-2099 Hnaoyun Inc.
* @author XingMeng
* @email hnxsh@foxmail.com
* @date 2016年11月6日
* 应用开发继承模型类
*/
namespace core\basic;
use core\basic\Config;
use core\view\Paging;
use core\database\Mysqli;
use core\database\Sqlite;
use core\database\Pdo;
class Model
{
// 数据库表名
public $table;
// 表主键
public $pk = 'id';
// 是否自动设置时间戳
public $autoTimestamp = false;
// 是否数字时间戳格式
public $intTimeFormat = false;
// 更新时间字段名
public $updateTimeField = 'update_time';
// 创建时间字段名
public $createTimeField = 'create_time';
// 程序执行的SQL语句记录
private $exeSql = array();
// 是否解密转义
private $decode = false;
// 查询语句构建
private $sql = array();
// 直接显示SQL语句
private $showSql = false;
// 直接显示结果
private $showRs = false;
// 数据库驱动对象
private $dbDriver;
// 查询语句
private $selectSql = "SELECT %distinct% %field% FROM %table% %join% %where% %group% %having% %order% %union% %limit%";
// 计数语句
private $countSql = "SELECT COUNT(*) AS sum FROM %table% %join% %where% %group% %having% %union%";
private $countSql2 = "SELECT %distinct% %field% FROM %table% %join% %where% %group% %having% %union%";
// 插入语句
private $insertSql = "INSERT INTO %table% %field% VALUES %value%";
// 多条插入语句
private $insertMultSql = "INSERT INTO %table% %field% %value%";
// 复制插入语句
private $insertFromSql = "INSERT INTO %table% %field% %from%";
// 删除语句
private $deleteSql = "DELETE FROM %table% %join% %where%";
// 更新语句
private $updateSql = "UPDATE %table% SET %value% %join% %where%";
// 查询索引
private $checkIndex = "show index from %table%";
// 自动表名
public function __construct()
{
if (! $this->table) {
$table_name = Config::get('database.prefix') . hump_to_underline(str_replace('Model', '', basename(get_called_class())));
$this->table = $table_name;
}
}
// 对象方式动态调用数据库操作方法
public function __call($methed, $args)
{
if (method_exists($this->getDb(), $methed)) {
$result = call_user_func_array(array(
$this->getDb(),
$methed
), $args);
return $result;
} else {
error('不存在数据库操作方法“' . $methed . '”，请核对再试！');
}
}
// 获取数据库连接对象
private function getDb()
{
if (! $this->dbDriver) {
$type = Config::get('database.type');
switch ($type) {
case 'mysqli': // 使用mysqli连接数据库
$this->dbDriver = Mysqli::getInstance();
break;
case 'sqlite': // 使用sqlite连接数据库
$this->dbDriver = Sqlite::getInstance();
break;
default: // 默认使用PDO连接数据库
$this->dbDriver = Pdo::getInstance();
}
}
return $this->dbDriver;
}
// 执行SQL构造替换
private function buildSql($sql, $clear = true)
{
preg_match_all('/\%([\w]+)\%/', $sql, $matches);
foreach ($matches[1] as $key => $value) {
if (isset($this->sql[$value]) && $this->sql[$value]) {
$sql = str_replace("%$value%", $this->sql[$value], $sql);
} else {
if ($value == 'table') {
$sql = str_replace("%$value%", $this->table, $sql);
} else {
$sql = str_replace("%$value%", '', $sql);
}
}
}
$this->exeSql[] = $sql;
if ($clear) {
$this->pk = 'id';
$this->autoTimestamp = false;
$this->intTimeFormat = false;
$this->updateTimeField = 'update_time';
$this->createTimeField = 'create_time';
$this->sql = array();
}
if ($this->showSql && $clear) {
exit($sql);
} else {
return $sql;
}
}
/**
* 关闭自动提交，开启事务模式（非连贯，直接调用）
*/
final public function begin()
{
$this->getDb()->begin();
}
/**
* 提交事务（非连贯，直接调用）
*
* @return \core\basic\Model
*/
final public function commit()
{
$this->getDb()->commit();
}
/**
* 内容输出
*
* @param mixed $data
* @return mixed
*/
final protected function outData($result)
{
if ($this->decode) {
$result = decode_string($result);
$this->decode = false;
} else {
$result = decode_slashes($result);
}
if ($this->showRs) {
print_r($result);
exit();
} else {
return $result;
}
}
/**
* 连贯操作：是否解码转义数据
*/
final public function decode($flag = true)
{
if ($flag === true)
$this->decode = true;
return $this;
}
/**
* 连贯操作：设置返回SQL语句，不真正执行,优先级高于showRS()
*
* @param string $flag
* 调用默认为true
* @return \core\basic\Model
*/
final public function showSql($flag = true)
{
if ($flag === true)
$this->showSql = true;
return $this;
}
/**
* 连贯操作：设置显示结果到页面
*
* @param string $flag
* 调用默认为true
* @return \core\basic\Model
*/
final public function showRs($flag = true)
{
if ($flag === true)
$this->showRs = true;
return $this;
}
/**
* 连贯操作：是否自动插入时间
*
* @param string $flag
* @return \core\basic\Model
*/
final public function autoTime($flag = true)
{
if ($flag === true)
$this->sql['auto_time'] = true;
return $this;
}
/**
* 连贯操作：设置查询表全名
*
* @param mixed $table
* 可以是字符串、数组,
* 字符串：如"ay_user as a",
* 如传递多个表：array('ay_user','ay_role'),
* 传递多个表并设置别名：array('ay_user'=>'u','ay_role'=>'r')
* @return \core\basic\Model
*/
final public function table($table)
{
if (is_array($table)) {
$table_string = '';
foreach ($table as $key => $value) {
if (is_int($key)) {
$table_string .= '`' . $value . '`,';
} else {
$table_string .= '`' . $key . '` AS ' . $value . ',';
}
}
$this->sql['table'] = substr($table_string, 0, - 1);
} else {
$this->sql['table'] = $table;
}
return $this;
}
/**
* 连贯操作：设置查询表名
*
* @param mixed $table
* 可以是字符串、数组,
* 字符串：如"user as a",
* 如传递多个表：array('user','role'),
* 传递多个表并设置别名：array('user'=>'u','role'=>'r')
* @return \core\basic\Model
*/
final public function name($table)
{
$prefix = Config::get('database.prefix');
if (is_array($table)) {
$table_string = '';
foreach ($table as $key => $value) {
if (is_int($key)) {
$table_string .= '`' . $prefix . $value . '`,';
} else {
$table_string .= '`' . $prefix . $key . '` AS ' . $value . ',';
}
}
$this->table = substr($table_string, 0, - 1);
} else {
$this->table = $prefix . $table;
}
return $this;
}
/**
* 连贯操作：设置数据库别名
*
* @param string $alias
* 设置的别名名字，接收字符串，如：a
* @return \core\basic\Model
*/
final public function alias($alias)
{
if ($alias) {
if (! isset($this->table))
error('调用alias之前必须先设置table');
if (strpos($this->table, ' AS ') === false) {
$this->table = $this->table . ' AS ' . $alias;
}
}
return $this;
}
/**
* 连贯操作：设置返回唯一不同的值
*
* @param string $flag
* 调用时默认为true,如果传递false则不使用
* @return \core\basic\Model
*/
final public function distinct($flag = true)
{
if ($flag === true)
$this->sql['distinct'] = 'DISTINCT';
return $this;
}
/**
* 连贯操作：设置字段
*
* @param mixed $field
* 可以为字符串、数组,
* 如字符串："name,password as pw",
* 数组设置字段：array('name','password')，如果为非数字数组则设置别名,
* 数组设置字段并设置别名：array('username'=>'name','password'=>'pw')
* @return \core\basic\Model
*/
final public function field($field)
{
if (is_array($field)) {
$field_string = '';
foreach ($field as $key => $value) {
if (is_int($key)) {
$field_string .= $value . ',';
} else {
$field_string .= $key . ' AS ' . $value . ',';
}
}
$this->sql['field'] = substr($field_string, 0, - 1);
} elseif ($field) {
$this->sql['field'] = $field;
}
return $this;
}
/**
* 连贯操作：设置查询条件
*
* @param mixed $where
* 设置条件，可以为字符串、数组,
* 字符串模式：如"id<1","name like %1",
* 数组模式：array('username'=>'xie',"realname like '%谢%'")
* @param string $connect
* 调用本方法时与前面条件使用AND连接，$where参数数组内部的条件默认使用AND连接
* @return \core\basic\Model
*/
/**
* 连贯操作：设置查询条件
*
* @param mixed $where
* 设置条件，可以为字符串、数组,
* 字符串模式：如"id<1","name like %1",
* 数组模式：array('username'=>'xie',"realname like '%谢%'")
* @param string $inConnect
* 调用本方法时$where参数数组内部的条件默认使用AND连接
* @param string $outConnect
* 调用本方法时与前面条件使用AND连接
* @param boolean $fuzzy
* 条件是否为模糊匹配，即in匹配
* @return \core\basic\Model
*/
final public function where($where, $inConnect = 'AND', $outConnect = 'AND', $fuzzy = false)
{
if (! $where) {
return $this;
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= ' ' . $outConnect . '(';
} else {
$this->sql['where'] = 'WHERE(';
}
if (is_array($where)) {
$where_string = '';
$flag = false;
foreach ($where as $key => $value) {
if ($flag) { // 条件之间内部AND连接
$where_string .= ' ' . $inConnect . ' ';
} else {
$flag = true;
}
if (! is_int($key)) {
if ($fuzzy) {
$where_string .= $key . " like '%" . $value . "%' ";
} else {
$where_string .= $key . "='" . $value . "' ";
}
} else {
$where_string .= $value;
}
}
$this->sql['where'] .= $where_string . ')';
} else {
$this->sql['where'] .= $where . ')';
}
return $this;
}
/**
* 连贯操作：设置EXISTS查询
*
* @param string $subsql
* 传递子查询
* @return \core\basic\Model
*/
final public function exists($subSql)
{
if (is_callable($subSql)) { // 闭包子查询
$subSql = $subSql(new self());
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND EXISTS ($subSql)";
} else {
$this->sql['where'] = "WHERE EXISTS ($subSql)";
}
return $this;
}
/**
* 连贯操作：设置NOT EXISTS查询
*
* @param string $subSql
* 传递子查询
* @return \core\basic\Model
*/
final public function notExists($subSql)
{
if (is_callable($subSql)) { // 闭包子查询
$subSql = $subSql(new self());
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND NOT EXISTS ($subSql)";
} else {
$this->sql['where'] = "WHERE NOT EXISTS ($subSql)";
}
return $this;
}
/**
* 连贯操作：设置IN查询
*
* @param string $field
* 传递需要比对的字段，如: 'username'
* @param mixed $range
* 字符串、数组、子查询,如：'1,2,3',array(1,2,3);
* @return \core\basic\Model
*/
final public function in($field, $range)
{
if (! $field)
return $this;
if (is_array($range)) {
if (count($range) == 1) { // 单只有一个值时使用直接使用等于，提高读取性能
$in_string = "$field='$range[0]'";
} else {
$in_string = "$field IN (" . implode_quot(',', $range) . ")";
}
} else {
if (preg_match('/,/', $range)) {
$in_string = "$field IN (" . implode_quot(',', explode(',', $range)) . ")";
} else { // 传递单个字符串时直接相等处理
$in_string = "$field = '$range'";
}
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND $in_string";
} else {
$this->sql['where'] = "WHERE $in_string";
}
return $this;
}
/**
* 连贯操作：设置NOT IN查询
*
* @param string $field
* 传递需要比对的字段，如: id NOT IN (1,2,3)
* @param mixed $range
* 字符串、数组、子查询
* @return \core\basic\Model
*/
final public function notIn($field, $range)
{
if (! $field)
return $this;
if (is_array($range)) {
$in_string = implode_quot(',', $range);
} else {
if (preg_match('/,/', $range)) {
$in_string = implode_quot(',', explode(',', $range));
} else {
$in_string = "'$range'";
}
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND $field NOT IN ($in_string)";
} else {
$this->sql['where'] = "WHERE $field NOT IN ($in_string)";
}
return $this;
}
/**
* 连贯操作：设置关键字条件匹配
*
* @param string $field
* 字段名,支持数组传递多个字段或多个字段用逗号隔开
* @param string $keyword
* 匹配关键字
* @param string $matchType
* 匹配模式，默认为all,可选left,right,equal
* @return \core\database\Operate
*/
final public function like($field, $keyword, $matchType = "all")
{
if (! $field)
return $this;
switch ($matchType) {
case 'left':
$keyword = "$keyword%";
break;
case 'right':
$keyword = "%$keyword";
break;
case 'equal':
case '==':
$keyword = "$keyword";
break;
default:
$keyword = "%$keyword%";
}
if (is_array($field) || preg_match('/,/', $field)) {
if (! is_array($field)) {
$field = explode(',', $field);
}
foreach ($field as $value) {
if (isset($sqlStr)) {
$sqlStr .= " OR $value LIKE '$keyword'";
} else {
$sqlStr = "$value LIKE '$keyword'";
}
}
} else {
$sqlStr = "$field LIKE '$keyword'";
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND ($sqlStr)";
} else {
$this->sql['where'] = "WHERE ($sqlStr)";
}
return $this;
}
/**
* 连贯操作：设置关键字条件不匹配
*
* @param string $field
* 字段名
* @param string $keyword
* 匹配关键字
* @param string $matchType
* 匹配模式，默认为all,可选left,right
* @return \core\database\Operate
*/
final public function notLike($field, $keyword, $matchType = "all")
{
if (! $field)
return $this;
switch ($matchType) {
case 'left':
$keyword = "$keyword%";
break;
case 'right':
$keyword = "%$keyword";
break;
case 'equal':
case '==':
$keyword = "$keyword";
break;
default:
$keyword = "%$keyword%";
}
if (is_array($field) || preg_match('/,/', $field)) {
if (! is_array($field)) {
$field = explode(',', $field);
}
foreach ($field as $value) {
if (isset($sqlStr)) {
$sqlStr .= " AND $value NOT LIKE '$keyword'";
} else {
$sqlStr = "$value NOT LIKE '$keyword'";
}
}
} else {
$sqlStr = "$field NOT LIKE '$keyword'";
}
if (isset($this->sql['where']) && $this->sql['where']) {
$this->sql['where'] .= " AND ($sqlStr)";
} else {
$this->sql['where'] = "WHERE ($sqlStr)";
}
return $this;
}
/**
* 连贯操作：设置查询排序
*
* @param mixed $order
* 可以为字符串、数组,
* 字符串模式：如"id DESC,name",
* 数组模式：array('id'=>'DESC','name')
* @return \core\basic\Model
*/
final public function order($order)
{
if (is_array($order)) {
$order_string = 'ORDER BY ';
foreach ($order as $key => $value) {
if (is_int($key)) {
$order_string .= $value . ',';
} else {
$order_string .= $key . ' ' . $value . ',';
}
}
$this->sql['order'] = substr($order_string, 0, - 1);
} else {
$this->sql['order'] = 'ORDER BY ' . $order;
}
return $this;
}
/**
* 连贯操作：设置查询数量
*
* @param string $limit
* 设置限制语句，可接受：
* 单个参数数，如 1,条数
* 单个参数字符串，如"1,10"
* 两个参数数字，如：1,10
* 注意：当使用了分页时会无效
* @return \core\basic\Model
*/
final public function limit($limit)
{
$var_num = func_num_args();
if ($var_num > 1 || preg_match('/,/', $limit)) {
if ($var_num > 1) {
$var_arr = func_get_args();
} else {
$var_arr = explode(',', $limit);
}
switch (get_db_type()) {
case 'mysql':
$this->sql['limit'] = 'LIMIT ' . $var_arr[0] . ',' . $var_arr[1];
break;
case 'sqlite':
$this->sql['limit'] = 'LIMIT ' . $var_arr[1] . ' OFFSET ' . $var_arr[0];
break;
case 'pgsql':
$this->sql['limit'] = 'LIMIT ' . $var_arr[1] . ' OFFSET ' . $var_arr[0];
break;
}
} else {
$this->sql['limit'] = 'LIMIT ' . $limit;
}
return $this;
}
/**
* 连贯操作：设置分组查询
*
* @param mixed $group
* 可以传递字符串、数字数组，如"name,sex",array('name','sex')
* @return \core\basic\Model
*/
final public function group($group)
{
if (is_array($group)) {
$group_string = 'GROUP BY ';
foreach ($group as $key => $value) {
$group_string .= $value . ',';
}
$this->sql['group'] = substr($group_string, 0, - 1);
} else {
$this->sql['group'] = 'GROUP BY ' . $group;
}
return $this;
}
/**
* 连贯操作：设置查询条件having
* 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
*
* @param string $having
* 传入字符串，需要完整having语句
* @return \core\basic\Model
*/
final public function having($having, $inConnect = 'AND', $outConnect = 'AND')
{
if (isset($this->sql['having']) && $this->sql['having']) {
$this->sql['having'] .= ' ' . $outConnect . '(';
} else {
$this->sql['having'] = 'HAVING(';
}
if (is_array($having)) {
$having_string = '';
$flag = false;
foreach ($having as $key => $value) {
if ($flag) { // 条件之间内部AND连接
$having_string .= ' ' . $inConnect . ' ';
} else {
$flag = true;
}
if (! is_int($key)) {
$having_string .= $key . "='" . $value . "' ";
} else {
$having_string .= $value;
}
}
$this->sql['having'] .= $having_string . ')';
} else {
$this->sql['having'] .= $having . ')';
}
return $this;
}
/**
* 连贯操作：设置连接查询
*
* @param array $join
* 可以为一维或二维数组，array('table','a.id=b.id','LEFT'),
* array第一个参数为数据表，第二个参数为ON条件，第三个参数为类型,
* 二维模式：array(
* array('table b','a.id=b.aid','LEFT'),
* array('table c','a.id=c.aid','LEFT')
* )
* @return \core\basic\Model
*/
final public function join(array $join)
{
if (count($join) == count($join, 1)) {
$join_string = '';
if (isset($join[2])) {
$join_string .= ' ' . $join[2] . ' JOIN ';
} else {
$join_string .= ' LEFT JOIN ';
}
$join_string .= $join[0] . ' ON ' . $join[1];
if (isset($this->sql['join'])) {
$this->sql['join'] .= $join_string;
} else {
$this->sql['join'] = $join_string;
}
} else {
foreach ($join as $key => $value) {
$this->join($value);
}
}
return $this;
}
/**
* 连贯操作：合并两个或多个 SELECT 语句的结果集
*
* @param array $subSql
* 子查询可以使用数字数组传递多个
* @param string $isAll
* 是否UNION ALL
* @return \core\basic\Model
*/
final public function union($subSql, $isAll = false)
{
if (! isset($this->sql['union'])) {
$this->sql['union'] = '';
}
if (is_callable($subSql)) { // 闭包子查询
$subSql = $subSql(new self());
}
if (is_array($subSql)) {
foreach ($subSql as $key => $value) {
$this->union($value, $isAll);
}
} else {
if ($isAll) {
$this->sql['union'] .= " UNION ALL($subSql)";
} else {
$this->sql['union'] .= " UNION ($subSql)";
}
}
return $this;
}
/**
* 连贯操作：数据分页
* 传递一个参数时为页数，传递两个参数时第二个为分页大小
*
* @return \core\basic\Model
*/
final public function page($args = 1)
{
if ($args === false) {
$this->sql['paging'] = false;
return $this;
}
$this->sql['paging'] = true;
$paging = Paging::getInstance();
$var_num = func_num_args();
if ($var_num > 1 || preg_match('/,/', $args)) {
if ($var_num > 1) {
$var_arr = func_get_args();
} else {
$var_arr = explode(',', $args);
}
$paging->page = $var_arr[0];
$paging->pageSize = $var_arr[1];
if (isset($var_arr[2])) {
$paging->start = $var_arr[2];
}
} else {
$paging->page = $args;
}
return $this;
}
/**
* 连贯操作：待插入或更新数据数组，分解insert、update函数，实现 table($table)->data($data)->insert();
*
* @param array $data
* @return \core\basic\Model
*/
final public function data($data)
{
$this->sql['data'] = $data;
return $this;
}
/**
* 连贯操作：生成关联插入数据,如 用户对应多个角色，relation($ucode,$rcodes)
*
* @param string $field
* 第一个字段的值，如用户编码
* @param array $array
* 第二个字段的值数组，如用户所属多个角色数组
* @return \core\basic\Model
*/
final public function relation($field, array $array)
{
if ($array) {
foreach ($array as $value) {
$data[] = array(
$field,
$value
);
}
$this->sql['data'] = $data;
}
return $this;
}
/**
* 连贯操作：用于从一个表复制信息到另一个表 ，实现INSERT INTO SELECT的功能
*
* @param string $subSql
*/
final public function from($subSql)
{
if (is_callable($subSql)) { // 闭包子查询
$subSql = $subSql(new self());
}
$this->sql['from'] = $subSql;
return $this;
}
// ********************************数据查询************************************************************
/**
* 多条数据查询模型，select方法查询结果不存在，返回空数组
*
* @param string $type
* 可选传递1,2,3返回不同格式数据数组
* @return array
*/
final public function select($type = null)
{
// 闭包查询
if (is_callable($type)) {
$type($this);
return $this->select();
}
if (! isset($this->sql['field']) || ! $this->sql['field'])
$this->sql['field'] = '*';
// 如果调用了分页函数且分页，则执行分页处理
if (isset($this->sql['paging']) && $this->sql['paging']) {
if ($this->sql['group'] || $this->sql['distinct']) { // 解决使用分组时count(*)分页不准问题
if (get_db_type() == 'mysql') {
$this->limit(Paging::getInstance()->quikLimit()); // 分页
$this->sql['field'] = 'SQL_CALC_FOUND_ROWS ' . $this->sql['field']; // 添加查询总记录
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->all($sql, $type);
$count_sql = "select FOUND_ROWS() as sum";
if (! ! $rs = $this->getDb()->one($count_sql)) {
$total = $rs->sum;
// 分页内容
$limit = Paging::getInstance()->limit($total, true); // 生成分页代码
}
} else {
$count_sql = $this->buildSql($this->countSql2, false);
// 获取记录总数
if (! ! $rs = $this->getDb()->all($count_sql)) {
$total = count($rs);
// 分页内容
$limit = Paging::getInstance()->limit($total, true);
// 获取分页参数并设置分页
$this->limit($limit);
}
}
} else {
// 生成总数计算语句
$count_sql = $this->buildSql($this->countSql, false);
// 获取记录总数
if (! ! $rs = $this->getDb()->one($count_sql)) {
$total = $rs->sum;
// 分页内容
$limit = Paging::getInstance()->limit($total, true);
// 获取分页参数并设置分页
$this->limit($limit);
}
}
}
// 构建查询语句
if ($type === false) {
return $this->buildSql($this->selectSql,false);
} else {
$sql = $this->buildSql($this->selectSql);
}
if (! isset($result)) {
$result = $this->getDb()->all($sql, $type);
}
return $this->outData($result);
}
/**
* 单条数据查询模型，find 方法查询结果不存在，返回 null
*
* @param string $type
* 可选传递1,2,3返回不同格式数据数组
* @return string|boolean|string
*/
final public function find($type = null)
{
// 闭包查询
if (is_callable($type)) {
$type($this);
return $this->find();
}
if (! isset($this->sql['field']))
$this->sql['field'] = '*';
$this->limit(1); // 强制查询一条
$sql = $this->buildSql($this->selectSql); // 构建语句
if ($type === false) {
return $sql;
}
$result = $this->getDb()->one($sql, $type);
return $this->outData($result);
}
/**
* 返回指定字段数据数组
*
* @param string $name
* 字段名字符串或数字数组，单个字段，返回一维数组，如果多个字段，返回二维数组数
* @param string $key
* 指定返回数组的键值字段
* @return array
*/
final public function column($fields, $key = null)
{
// 配置传递的字段
if (is_array($fields)) {
$fields = implode(',', $fields);
}
// 如果传递字段不含指定键则添加
if ($key && ! preg_match('/(.*,|^)(' . $key . ')(,.*|$)/', $fields)) {
$this->sql['field'] = $key . ',' . $fields;
} else {
$this->sql['field'] = $fields;
}
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->all($sql, 1);
$data = array();
foreach ($result as $vkey => $value) {
if ($key) {
$key_value = $value[$key];
if (is_array($value) && count($value) == 2) {
unset($value[$key]);
$data[$key_value] = current($value);
} else {
$data[$key_value] = $value;
}
} else {
if (is_array($value) && count($value) == 1) {
$data[] = current($value);
} else {
$data[] = $value;
}
}
}
return $this->outData($data);
}
/**
* 返回指定字段的一条数据的值模式
*
* @param string $name
* 字段名
* @return string 返回字段值
*/
final public function value($field)
{
$this->sql['field'] = $field;
$this->limit(1);
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
if (isset($result[0])) {
return $this->outData($result[0]);
} else {
return null;
}
}
/**
* 返回指定列最大值
*
* @param string $name
* 字段名
* @return boolean
*/
final public function max($field)
{
$this->sql['field'] = "MAX(`$field`)";
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
return $this->outData($result[0]);
}
/**
* 返回指定列最小值
*
* @param string $name
* 字段名
* @return boolean
*/
final public function min($field)
{
$this->sql['field'] = "MIN(`$field`)";
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
return $this->outData($result[0]);
}
/**
* 返回指定列平均值
*
* @param string $name
* 字段名
* @return boolean
*/
final public function avg($field)
{
$this->sql['field'] = "AVG(`$field`)";
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
return $this->outData($result[0]);
}
/**
* 返回指定列合计值
*
* @param string $name
* 字段名
* @return boolean
*/
final public function sum($field)
{
$this->sql['field'] = "SUM(`$field`)";
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
if ($result[0]) {
return $this->outData($result[0]);
} else {
return 0;
}
}
// 返回统计数据
final public function count()
{
$this->sql['field'] = "COUNT(*)";
$sql = $this->buildSql($this->selectSql);
$result = $this->getDb()->one($sql, 2);
if ($result[0]) {
return $this->outData($result[0]);
} else {
return 0;
}
}
// ******************************数据插入*******************************************************
/**
* 数据插入模型
*
* @param array $data
* 可以为一维或二维数组，
* 一维数组：array('username'=>"xsh",'sex'=>'男'),
* 二维数组：array(
* array('username'=>"xsh",'sex'=>'男'),
* array('username'=>"gmx",'sex'=>'女')
* )
* @param boolean $batch
* 是否启用批量一次插入功能，默认true
* @return boolean|boolean|array
*/
final public function insert(array $data = array(), $batch = true)
{
// 未传递数据时，使用data函数插入数据
if (! $data && isset($this->sql['data'])) {
return $this->insert($this->sql['data']);
}
if (is_array($data)) {
if (! $data)
return;
if (count($data) == count($data, 1)) { // 单条数据
$keys = '';
$values = '';
foreach ($data as $key => $value) {
$this->checkKey($key);
if (! is_numeric($key)) {
$keys .= "`" . $key . "`,";
$values .= "'" . $value . "',";
}
}
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
$keys .= "`" . $this->createTimeField . "`,`" . $this->updateTimeField . "`,";
if ($this->intTimeFormat) {
$values .= "'" . time() . "','" . time() . "',";
} else {
$values .= "'" . date('Y-m-d H:i:s') . "','" . date('Y-m-d H:i:s') . "',";
}
}
if ($keys) { // 如果插入数据关联字段,则字段以关联数据为准,否则以设置字段为准
$this->sql['field'] = '(' . substr($keys, 0, - 1) . ')';
} elseif (isset($this->sql['field']) && $this->sql['field']) {
$this->sql['field'] = "({$this->sql['field']})";
}
$this->sql['value'] = "(" . substr($values, 0, - 1) . ")";
$sql = $this->buildSql($this->insertSql);
} else { // 多条数据
if ($batch) { // 批量一次性插入
$key_string = '';
$value_string = '';
$flag = false;
foreach ($data as $keys => $value) {
if (! $flag) {
$value_string .= ' SELECT ';
} else {
$value_string .= ' UNION All SELECT ';
}
foreach ($value as $key2 => $value2) {
// 字段获取只执行一次
if (! $flag && ! is_numeric($key2)) {
$this->checkKey($key2);
$key_string .= "`" . $key2 . "`,";
}
$value_string .= "'" . $value2 . "',";
}
$flag = true;
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
if ($this->intTimeFormat) {
$value_string .= "'" . time() . "','" . time() . "',";
} else {
$value_string .= "'" . date('Y-m-d H:i:s') . "','" . date('Y-m-d H:i:s') . "',";
}
}
$value_string = substr($value_string, 0, - 1);
}
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
$key_string .= "`" . $this->createTimeField . "`,`" . $this->updateTimeField . "`,";
}
if ($key_string) { // 如果插入数据关联字段,则字段以关联数据为准,否则以设置字段为准
$this->sql['field'] = '(' . substr($key_string, 0, - 1) . ')';
} elseif (isset($this->sql['field']) && $this->sql['field']) {
$this->sql['field'] = "({$this->sql['field']})";
}
$this->sql['value'] = $value_string;
$sql = $this->buildSql($this->insertMultSql);
// 判断SQL语句是否超过数据库设置
if (get_db_type() == 'mysql') {
$max_allowed_packet = $this->getDb()->one('SELECT @@global.max_allowed_packet', 2);
} else {
$max_allowed_packet = 1 * 1024 * 1024; // 其他类型数据库按照1M限制
}
if (strlen($sql) > $max_allowed_packet) { // 如果要插入的数据过大，则转换为一条条插入
return $this->insert($data, false);
}
} else { // 批量一条条插入
foreach ($data as $keys => $value) {
$result = $this->insert($value);
}
return $result;
}
}
} elseif ($this->sql['from']) {
if (isset($this->sql['field']) && $this->sql['field']) { // 表指定字段复制
$this->sql['field'] = "({$this->sql['field']})";
}
$sql = $this->buildSql($this->insertFromSql);
} else {
return;
}
$sql = preg_replace_r('/pboot:if/i', 'pboot@if', $sql); // 过滤插入cms条件语句
$sql = preg_replace_r('/pboot:sql/i', 'pboot@sql', $sql); // 过滤插入cms条件语句
return $this->getDb()->amd($sql);
}
/**
* 插入数据并返回自增ID值
*
* @param array $data
* 可以为一维或二维数组
* @param boolean $batch
* 是否启用批量一次插入功能，默认true
* @return number|string|boolean
*/
final public function insertGetId(array $data = null, $batch = true)
{
if ($this->insert($data, $batch)) {
return $this->getDb()->insertId();
} else {
return false;
}
}
// ******************************数据更新*******************************************************
/**
* 数据更新
*
* @param array $data
* 传入字符串、数组
* 字符串如："username='xsh'"
* 数组如：array('username'=>'test','sex'=>'女')
* @return void|boolean|boolean|array
*/
final public function update($data = null)
{
// 未传递数据时使用data函数插入数据
if (! $data && $this->sql['data']) {
return $this->update($this->sql['data']);
}
$update_string = '';
if (is_array($data)) {
if (! $data)
return;
foreach ($data as $key => $value) {
$this->checkKey($key);
$temp_v_start = substr($value, 0, 2);
$temp_v_end = substr($value, 2);
if (is_numeric($temp_v_end) && $temp_v_start == "-=") {
$update_string .= "`$key`= $key-$temp_v_end,"; // 自减
} elseif (is_numeric($temp_v_end) && $temp_v_start == "+=") {
$update_string .= "`$key`= $key+$temp_v_end,"; // 自加
} else {
$update_string .= "`$key`='$value',";
}
}
$update_string = substr($update_string, 0, - 1);
} else {
$update_string = $data;
}
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
if ($this->intTimeFormat) {
$update_string .= ",`" . $this->updateTimeField . "`=' " . time() . "'";
} else {
$update_string .= ",`" . $this->updateTimeField . "`=' " . date('Y-m-d H:i:s') . "'";
}
}
$this->sql['value'] = $update_string;
$sql = $this->buildSql($this->updateSql);
$sql = preg_replace_r('/pboot:if/i', 'pboot@if', $sql); // 过滤插入cms条件语句
$sql = preg_replace_r('/pboot:sql/i', 'pboot@sql', $sql); // 过滤插入cms条件语句
return $this->getDb()->amd($sql);
}
/**
* 更新指定字段
*
* @param string $field
* 字段名
* @param string $value
* 值
* @return boolean|boolean|array
*/
final public function setField($field, $value)
{
$this->checkKey($field);
$this->sql['value'] = "`$field`='$value'";
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
if ($this->intTimeFormat) {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . time() . "'";
} else {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . date('Y-m-d H:i:s') . "'";
}
}
$sql = $this->buildSql($this->updateSql);
return $this->getDb()->amd($sql);
}
/**
* 字段自增
*
* @param string $field
* 字段
* @param number $value
* 值
* @return boolean|boolean|array
*/
final public function setInc($field, $value = 1)
{
$this->checkKey($field);
$this->sql['value'] = " `$field`= $field+$value";
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
if ($this->intTimeFormat) {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . time() . "'";
} else {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . date('Y-m-d H:i:s') . "'";
}
}
$sql = $this->buildSql($this->updateSql);
return $this->getDb()->amd($sql);
}
/**
* 字段自减
*
* @param string $field
* 字段
* @param number $value
* 值
* @return boolean|boolean|array
*/
final public function setDec($field, $value = 1)
{
$this->checkKey($field);
$this->sql['value'] = " `$field`= $field-$value";
if ($this->autoTimestamp || (isset($this->sql['auto_time']) && $this->sql['auto_time'] == true)) {
if ($this->intTimeFormat) {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . time() . "'";
} else {
$this->sql['value'] .= ",`" . $this->updateTimeField . "`=' " . date('Y-m-d H:i:s') . "'";
}
}
$sql = $this->buildSql($this->updateSql);
return $this->getDb()->amd($sql);
}
// ***************************数据删除*******************************************************
/**
* 数据删除
*
* @param mixed $data
* 可以为字符串或数组，如"1,2,3",array(1,2,3)
* @param string $key
* 字段名，默认为id
* @return boolean|boolean|array
*/
final public function delete($data = null, $key = null)
{
if ($data) {
if (! $key)
$key = $this->pk;
$this->checkKey($key);
if (is_array($data) || preg_match('/,/', $data)) {
$this->in($key, $data);
} else {
$this->where("$key='$data'");
}
}
$sql = $this->buildSql($this->deleteSql);
return $this->getDb()->amd($sql);
}
// 检测key值
private function checkKey($key)
{
if (! $key)
return;
if (! preg_match('/^[\w\.\-]+$/', $key)) {
error('传递的SQL数据中含有非法字符:' . $key);
}
}
//查询索引
public function checkIndexSql(): array
{
$sql = $this->buildSql($this->checkIndex);
$result = $this->getDb()->query($sql);
return $this->getDb()->fetchQuery($result);
}
}