表结构
这几张表都结构都是相同的,除了存储的数据不同
要实现什么呢?
每个表的uuid进行分组,并且以id最新的排序
根据日期范围,实现批量查询不同月份的表
代码
controller
<?php
/**
* Created by.
* User: Jim
* Date: 2020/11/17
* Time: 14:41
*/
namespace app\admin\controller\analysis;
use app\admin\controller\master\Common;
use app\admin\model\master\Analysis;
use think\Db;
use think\facade\Config;
/**
* 前端用户每个页面 浏览的信息
* Class Tracker
* @package app\admin\controller
*/
class Tracker extends Common
{
static $tablePrefix;
public function initialize()
{
parent::initialize(); // TODO: Change the autogenerated stub
self::$tablePrefix = Config::get('database.analysis.prefix');
$this->model = new \app\admin\model\analysis\Tracker();
$tablesName = $this->getBetweenTable('2020-11',date('Y-m'));
$this->assign(compact('tablesName'));
}
public function index()
{
return $this->fetch();
}
/**
* 信息列表
*/
public function list()
{
$subQuery = $this->model
->name($this->model->getTableName())
->where($this->model->search())
->order('id desc')
->buildSql();
$lists = $this->model
->table($subQuery . 'a')
->field('*,count(*) survival_time')
->order('id desc')
->group('uuid')
->paginate($this->limit);
_lists($lists);
}
/**
* 数据导出
*/
public function export()
{
$time = $this->request->get('time');
$time = json_decode($time, true)['create_time'];
// 沒有选择时间,默认为本月的
if ($time == '') {
$subQuery = $this->model
->order('id desc')
->buildSql();
$datas = $this->model
->table($subQuery . 'a')
->field('*,count(*) survival_time')
->order('id desc')
->group('uuid')
->select();
} else {
//根据日期查询 多个表
[$start_time, $last_time] = explode(' ~ ', $time);
$tablesName = $this->getBetweenTable($start_time, $last_time);
/**
* 查询指定的时间
*/
$datas = [];
foreach ($tablesName as $table){
$subQuery = $this->model
->name($table)
->whereTime('create_time', 'between', [$start_time, $last_time])
->order('id desc')
->buildSql()
;
$data = $this->model
->table($subQuery . 'a')
->field('*,count(*) survival_time')
->order('id desc')
->group('uuid')
->select()
->toArray();
$datas = array_merge($datas,$data);
}
}
$lists = [];
foreach ($datas as $row) {
$item['product_name'] = $row['product_name'];
$item['product_code'] = $row['product_code'];
$item['user_formal'] = $row['user_formal'];
$item['ip_formal'] = $row['ip_formal'];
$item['keep_time'] = $row['keep_time'];
$item['survival_time'] = "约{$row['survival_time']}秒";
$item['create_time'] = $row['create_time'];
$item['url'] = $row['url'];
$item['referer'] = $row['referer'];
$item['user_agent'] = $row['user_agent'];
$lists[] = $item;
}
$name = '用户行为分析列表—' . date('Y-m-d His', time());
$headers = [
'产品名称', '产品代码', '邮箱', 'IP',
'留存时间', '页面活动时间', '访问时间',
'URL', '来源URL', '访问方式',
];
exportDataCsv($lists, $headers, $name);
}
/**
* 获取时间段内的存在的表 列表
* @param $start_time
* @param $last_time
* @return array
*/
private function getBetweenTable($start_time, $last_time)
{
$time1 = strtotime($start_time); // 自动为00:00:00 时分秒 两个时间之间的年和月份
$time2 = strtotime($last_time);
$monarr[] = date('Ym', $time1);;
while (($time1 = strtotime('+1 month', $time1)) <= $time2) {
$monarr[] = date('Ym', $time1); // 取得递增月;
}
$names = [];
foreach ($monarr as $row) {
$tableName = 'tracker_' . $row;
$check = Db::connect('analysis')->query("show tables like '" . self::$tablePrefix .$tableName . "'");
if (!$check) continue;
$names[] = $tableName;
}
return array_reverse($names);
}
}
model
<?php
/**
* Created by.
* User: Jim
* Date: 2020/10/30
* Time: 9:09
*/
namespace app\admin\model\analysis;
use app\admin\model\master\Analysis;
use think\Db;
use think\facade\Request;
use function Complex\theta;
class Tracker extends Analysis
{
protected function initialize()
{
parent::initialize(); // TODO: Change the autogenerated stub
$this->name = 'tracker_' . date('Ym');
}
protected $append = [
'ip_formal',
'user_formal',
'keep_time',
];
public function getKeepTimeAttr($value, $data)
{
try {
[$start_time, $uuid] = explode('|', $data['uuid']);
$last_time = $data['create_time'];
$result = ($last_time + 2) - $start_time;
return gmdate('H:i:s', $result);
} catch (\Exception $e) {
return '';
}
}
public function getIpFormalAttr($value, $data)
{
$value = $value ?? $data['ip'] ?? '';
if (!$value) return '';
return long2ip($value);
}
public function getUserFormalAttr($value, $data)
{
$value = $value ?? $data['user_id'] ?? '';
if (!$value) return '';
return Db::name('member')->where(['id' => $value])->cache(true)->value('email');
}
/**
* 搜索
* @return array
*/
public function search()
{
$params = Request::get();
if (!isset($params['searchParams'])) return [];
$searchParams = json_decode($params['searchParams'], true);
$where = [];
unset($searchParams['create_time']);
foreach ($searchParams as $field => $value) {
$value = trim($value);
if ($field == 'tableName') continue;
if ($value == '') continue;
$where[] = [$field, 'like', "%{$value}%"];
}
return $where;
}
/**
* 搜索
* @return array
*/
public function getTableName()
{
$params = Request::get();
if (!isset($params['searchParams'])) return $this->name;
$searchParams = json_decode($params['searchParams'], true);
$tableName = $searchParams['tableName'] ?? $this->name;
return $tableName;
}
}
使用、
(base64)根据日期查询所有数据
http://www.au.test.cc/analysis/tracker/export?time={%22create_time%22:%222020-11-11%20~%202020-12-25%22,%22tableName%22:%22tracker_202011%22,%22product_name|product_code%22:%22%22
(base64)选择指定的表以及搜索关键字
http://www.au.test.cc/analysis/tracker/list?page=1&limit=15&searchParams=%7B%22create_time%22%3A%222020-11-11%20~%202020-12-25%22%2C%22tableName%22%3A%22tracker_202011%22%2C%22product_name%7Cproduct_code%22%3A%22123%22%7D
http://www.au.test.cc/analysis/tracker/list?page=1&limit=15
{
"code":0,
"msg":"",
"count":5,
"data":[
{
"id":62,
"user_id":4403,
"uuid":"1605661840|d87c7ca6-0ced-4c62-b59f-e03669848652",
"ip":2130706433,
"url":"http://www.index.test.cc/goods/detail/id/1394.html",
"product_name":"KN95 Face Mask",
"product_code":"PCA024",
"user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36",
"referer":"http://www.index.test.cc/",
"create_time":"2020-11-18 11:17:50",
"survival_time":25,
"ip_formal":"127.0.0.1",
"keep_time":"02:07:12"
},
{
"id":61,
"user_id":4403,
"uuid":"1605669370|649bb0b5-336e-43e9-9433-57e4974c0bab",
"ip":2130706433,
"url":"http://www.index.test.cc/goods/detail/id/1395.html",
"product_name":"30ml Hand Sanitiser Gel",
"product_code":"PCA04",
"user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36",
"referer":"http://www.index.test.cc/goods/detail/id/1394.html",
"create_time":"2020-11-18 11:17:50",
"survival_time":21,
"ip_formal":"127.0.0.1",
"keep_time":"00:01:42"
}
]
}