Table structure
All of these tables have the same structure, except that they store different data
What is to be achieved?
The uUID of each table is grouped, and the latest sort of IDS is used to batch query tables in different months according to the date rangeCopy the codecode
controller
/** * 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;
/** ** The front-end user views each page * 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('the 2020-11',date('Y-m'));
$this->assign(compact('tablesName'));
}
public function index()
{
return $this->fetch();
}
/** * Info list */
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);
}
/** * Data export */
public function export()
{
$time = $this->request->get('time');
$time = json_decode($time.true) ['create_time'];
// No time is selected, default is this month
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 {
// Query multiple tables by date
[$start_time.$last_time] = explode('~'.$time);
$tablesName = $this->getBetweenTable($start_time.$last_time);
/** * query the specified 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'] = "About{$row['survival_time']}SEC. "";
$item['create_time'] = $row['create_time'];
$item['url'] = $row['url'];
$item['referer'] = $row['referer'];
$item['user_agent'] = $row['user_agent'];
$lists[] = $item;
}
$name = 'User Behavior Analysis List -' . date('Y-m-d His', time());
$headers = [
'Product Name'.'Product Code'.'email'.'IP'.'Retention time'.'Page Active Time'.'Access Time'.'URL'.'the source URL'.'Access mode',]; exportDataCsv($lists.$headers.$name);
}
/** * Get the list of tables that exist within the time period *@param $start_time
* @param $last_time
* @return array
*/
private function getBetweenTable($start_time.$last_time)
{
$time1 = strtotime($start_time); // Automatically specifies the year and month between 00:00:00 hours and seconds
$time2 = strtotime($last_time);
$monarr[] = date('Ym'.$time1);;
while (($time1 = strtotime('+1 month'.$time1)) < =$time2) {
$monarr[] = date('Ym'.$time1); // get increment month;
}
$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); }}Copy the codemodel
/** * 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');
}
/** * search *@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;
}
/** * search *@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; }}Copy the codeUse,
(base64) according to the date all of data query www.au.test.cc/analysis/tr…
Choose the specified table (base64) and search keyword www.au.test.cc/analysis/tr…
www.au.test.cc/analysis/tr…
{
"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 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36"."referer":"http://www.index.test.cc/"."create_time":"The 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 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36"."referer":"http://www.index.test.cc/goods/detail/id/1394.html"."create_time":"The 2020-11-18 11:17:50"."survival_time":21."ip_formal":"127.0.0.1"."keep_time":"00:01:42"}}]Copy the code