根据网友的设计进行了部分调整: 用户分为管理员admin表和用户user表
记录操作表数据 增删改: insert/delete/update
<?php
/**
* OperateLog.php
* description
*/ namespace app\service; use think\Db;
use think\Log; /**
* 操作日志
* Class OperateLog
* @package app\service
*/
class OperateLogService
{
// 日志表主键值id
protected $primaryid;
// 操作表的id
protected $tbid;
// 操作表名
protected $tbname;
// 操作表行字段名
protected $keys;
// 操作表行字段值
protected $values;
// 表前缀
protected $prefix;
// 操作用户类型: 1管理员,admin_id | 2用户,user_id
protected $user_type;
protected $user_id = 0;
protected $admin_id = 0;
protected $ip;
const LOGT1 = 'operatelog';
const LOGT2 = 'operatelog_content'; /**
* OperateLog constructor.
* @param int $userType 操作用户类型,1管理员
* @param int $uid 操作用户类型不为1时传入
*/
public function __construct($userType = 1, $uid = 0)
{
if ($userType == 1) {
$this->admin_id = session('admin_id');
} else {
$this->user_id = $uid;
}
$this->user_type = $userType;
$this->ip = ip2long(getIp());
$this->url = request()->url();
$this->prefix = config('database.prefix');
} /**
* 参数说明 插入行为
* int $tbid 查询指定表的id
* string $tbname 数据库表名
*/
public function insert($tbid, $tbname)
{
try {
//查询表注释
$tb = Db::query('show table status where name = "' . $this->prefix . $tbname . '"');
$priIdName = $this->getPrimaryKey($tbname);
$data = [
'type' => 1,
'create_time' => time(),
'tablename' => $tbname,
'tableid' => $tbid,
'tableid_name' => $priIdName,
'admin_id' => $this->admin_id,
'user_id' => $this->user_id,
'user_type' => $this->user_type,
'ip' => $this->ip,
'comment' => $tb['Comment'],
'url' => $this->url,
];
//插入日志主表
$returnid = Db::name(self::LOGT1)->insertGetId($data);
//查询字段注释
$fields = Db::query('show full columns from ' . $this->prefix . $tbname);
foreach ($fields as $v) {
$commentArray[$v['Field']] = $v['Comment'];
}
//查询所有字段信息，插入日志从表
$rs = Db::name($tbname)->where($priIdName, $tbid)->find();
$keys = array_keys($rs);
$values = array_values($rs);
for ($i = 0; $i < count($keys); $i++) {
Db::name(self::LOGT2)->insert([
'operatelog_id' => $returnid,
'tbkey' => $keys[$i],
'tbvalue' => $values[$i],
'comment' => $commentArray[$keys[$i]]
]);
}
} catch (\Exception $e) {
Log::error($e->getMessage());
} } /**
* 更新行为前
* @param $tbid
* @param $tbname
*/
public function updateStart($tbid, $tbname)
{
try {
//查询表注释
$tb = Db::query('show table status where name = "' . $this->prefix . $tbname . '"');
$priIdName = $this->getPrimaryKey($tbname);
$data = [
'type' => 2,
'create_time' => time(),
'tablename' => $tbname,
'tableid' => $tbid,
'tableid_name' => $priIdName,
'admin_id' => $this->admin_id,
'user_id' => $this->user_id,
'user_type' => $this->user_type,
'ip' => $this->ip,
'comment' => $tb['Comment'],
'url' => $this->url,
];
//插入日志主表
$returnid = Db::name(self::LOGT1)->insertGetId($data);
//查询修改前数据信息
$rs = Db::name($tbname)->where($priIdName, $tbid)->find();
$keys = array_keys($rs);
$values = array_values($rs);
$this->primaryid = $returnid;
$this->tbid = $tbid;
$this->tbname = $tbname;
$this->keys = $keys;
$this->values = $values;
} catch (\Exception $e) {
Log::error($e->getMessage());
} } /**
* 更新行为后
*/
public function updateEnd()
{
try {
//查询表注释
$tb = Db::query('show table status where name = "' . $this->prefix . $this->tbname . '"');
$priIdName = $this->getPrimaryKey($this->tbname);
foreach ($tb as $v) {
$commentArray[$v['Field']] = $v['Comment'];
}
//查询修改后数据信息
$rs = Db::name($this->tbname)->where($priIdName, $this->tbid)->find();
$currentvalues = array_values($rs);
//前后信息进行比较
for ($i = 0; $i < count($currentvalues); $i++) {
if ($this->values[$i] !== $currentvalues[$i]) {
Db::name(self::LOGT2)->insert([
'operatelog_id' => $this->primaryid,
'tbkey' => $this->keys[$i],
'tbvalue' => $this->values[$i],
'currenttbvalue' => $currentvalues[$i],
'comment' => $commentArray[$this->keys[$i]]
]);
}
}
} catch (\Exception $e) {
Log::error($e->getMessage());
} } /**
* 删除行为
* @param $tbid
* @param $tbname
*/
public function delete($tbid, $tbname)
{
try {
//查询表注释
$tb = Db::query('show table status where name = "' . $this->prefix . $this->tbname . '"');
$priIdName = $this->getPrimaryKey($this->tbname);
$data = [
'type' => 3,
'create_time' => time(),
'tablename' => $tbname,
'tableid' => $tbid,
'tableid_name' => $priIdName,
'admin_id' => $this->admin_id,
'user_id' => $this->user_id,
'user_type' => $this->user_type,
'ip' => $this->ip,
'comment' => $tb['Comment'],
'url' => $this->url,
];
//插入日志主表
$returnid = Db::name(self::LOGT1)->insertGetId($data);
//查询字段注释
$fields = Db::query('show full columns from ' . $this->prefix . $tbname);
foreach ($fields as $v) {
$commentArray[$v['Field']] = $v['Comment'];
}
//查询修改前数据信息
$rs = Db::name($tbname)->where($priIdName, $tbid)->find();
$keys = array_keys($rs);
$values = array_values($rs);
for ($i = 0; $i < count($keys); $i++) {
Db::name(self::LOGT2)->insert([
'operatelog_id' => $returnid,
'tbkey' => $keys[$i],
'tbvalue' => $values[$i],
'comment' => $commentArray[$keys[$i]]
]);
}
} catch (\Exception $e) {
Log::error($e->getMessage());
} } /**
* 查询表主键id名
* @param $tbname
* @return mixed
*/
protected function getPrimaryKey($tbname)
{
$priIdTb = Db::query("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='" . $this->prefix . $tbname . "' AND constraint_name='PRIMARY'");
return $priIdTb[0]['column_name'];
}
}
数据表设计:
CREATE TABLE `yed_operatelog` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned DEFAULT '0' COMMENT 'user表用户id',
`admin_id` int(11) unsigned DEFAULT '0' COMMENT 'admin表主键:管理员id',
`user_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '操作用户类型:1管理员,admin_id | 2用户,user_id',
`type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '操作类型：1新增2修改3删除',
`tableid` int(11) unsigned NOT NULL,
`tablename` varchar(255) NOT NULL COMMENT '表名',
`comment` varchar(255) DEFAULT NULL COMMENT '表的comment属性',
`create_time` int(11) unsigned NOT NULL COMMENT '创建时间',
`tableid_name` varchar(50) NOT NULL DEFAULT '' COMMENT '主键id名',
`ip` int(11) DEFAULT NULL COMMENT '操作ip',
`url` varchar(800) DEFAULT NULL COMMENT '操作url',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='操作日志表'; CREATE TABLE `yed_operatelog_content` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`operatelog_id` int(11) NOT NULL COMMENT 'operatelog表id',
`tbkey` longtext NOT NULL COMMENT '字段名',
`tbvalue` longtext COMMENT '改之前值',
`currenttbvalue` longtext COMMENT '改之后值',
`comment` varchar(255) DEFAULT NULL COMMENT '字段注释',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='操作日志从表';