Repository navigation
Expand file tree
/
Copy pathgetTrendData.php
More file actions
438 lines (392 loc) · 15.9 KB
/
Copy pathgetTrendData.php
File metadata and controls
438 lines (392 loc) · 15.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
<?php
// 设置错误报告级别,隐藏警告
error_reporting(E_ERROR | E_PARSE);
ini_set('display_errors', 0);
// 确保 Typecho 环境已加载
if (!defined('__TYPECHO_ROOT_DIR__')) {
define('__TYPECHO_ROOT_DIR__', dirname(__FILE__, 4));
}
// 只有在 Typecho 环境未加载时才加载
if (!class_exists('Typecho_Db') && !class_exists('\\Typecho\\Db')) {
require_once __TYPECHO_ROOT_DIR__ . '/config.inc.php';
// 兼容 Typecho 1.2 / 1.3 初始化
if (class_exists('\\Widget\\Init')) {
\Widget\Init::alloc();
} elseif (file_exists(__TYPECHO_ROOT_DIR__ . '/var/Typecho/Common.php')) {
require_once __TYPECHO_ROOT_DIR__ . '/var/Typecho/Common.php';
if (method_exists('\\Typecho\\Common', 'init')) {
\Typecho\Common::init();
}
} elseif (file_exists(__TYPECHO_ROOT_DIR__ . '/var/Common.php')) {
require_once __TYPECHO_ROOT_DIR__ . '/var/Common.php';
Typecho_Common::init();
}
}
require_once dirname(__FILE__) . '/adapter.php';
// 设置JSON响应头
header('Content-Type: application/json; charset=utf-8');
try {
// 检查请求方法
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
throw new Exception('仅支持POST请求');
}
// 获取数据库连接
if (class_exists('\\Typecho\\Db')) {
$db = \Typecho\Db::get();
} else {
$db = Typecho_Db::get();
}
if (!$db) {
throw new Exception('数据库连接失败');
}
$prefix = $db->getPrefix();
// 补齐查询索引与身份字段(幂等)
require_once dirname(__FILE__) . '/DbOptimize.php';
require_once dirname(__FILE__) . '/Plugin.php';
VisitorLoggerPro_Plugin::ensureSchema($db, $prefix);
VisitorLoggerPro_DbOptimize::ensureIndexes($db, $prefix);
// 读取POST数据
$input = file_get_contents('php://input');
if (empty($input)) {
throw new Exception('未收到请求数据');
}
$requestData = json_decode($input, true);
if (json_last_error() !== JSON_ERROR_NONE) {
throw new Exception('JSON解析错误: ' . json_last_error_msg());
}
$startDate = isset($requestData['startDate']) ? $requestData['startDate'] : date('Y-m-d 00:00:00', strtotime('-7 days'));
$endDate = isset($requestData['endDate']) ? $requestData['endDate'] : date('Y-m-d 23:59:59');
// 处理特殊的"all"时间范围
if ($startDate === 'all') {
// 获取第一条记录的时间作为开始时间
$firstRecord = $db->fetchRow("SELECT MIN(time) as first_time FROM {$prefix}visitor_log WHERE time IS NOT NULL");
if ($firstRecord && $firstRecord['first_time']) {
$startDate = date('Y-m-d 00:00:00', strtotime($firstRecord['first_time']));
} else {
// 如果没有记录,使用当前时间
$startDate = date('Y-m-d 00:00:00');
}
$endDate = date('Y-m-d 23:59:59');
}
// 验证日期格式
$startDateTime = DateTime::createFromFormat('Y-m-d H:i:s', $startDate);
$endDateTime = DateTime::createFromFormat('Y-m-d H:i:s', $endDate);
if (!$startDateTime || !$endDateTime) {
throw new Exception('日期格式错误');
}
if ($startDateTime > $endDateTime) {
throw new Exception('开始日期不能大于结束日期');
}
// 计算日期差
$dateDiff = $startDateTime->diff($endDateTime)->days;
if ($dateDiff > 365) {
throw new Exception('查询时间范围不能超过365天');
}
// 查询趋势数据
$result = getTrendData($db, $prefix, $startDate, $endDate);
// 返回成功响应
ob_end_clean();
echo json_encode($result, JSON_UNESCAPED_UNICODE);
} catch (Exception $e) {
ob_end_clean();
header('HTTP/1.1 400 Bad Request');
echo json_encode([
'success' => false,
'error' => $e->getMessage(),
'debug' => [
'typecho_defined' => defined('__TYPECHO_ROOT_DIR__'),
'typecho_db_class' => class_exists('Typecho_Db'),
'typecho_new_db_class' => class_exists('\\Typecho\\Db'),
'request_method' => $_SERVER['REQUEST_METHOD'] ?? 'unknown'
]
], JSON_UNESCAPED_UNICODE);
}
/**
* 计算会话数(对齐 Umami visits = COUNT DISTINCT visit_id)
* - 有 session_id:按 visitId 去重
* - 无 session_id 的旧数据:同一 IP 间隔 >30 分钟视为新会话
*/
function calculateSessions($db, $prefix, $whereClause, $hasIdentity = false)
{
$cookieSessions = 0;
if ($hasIdentity) {
try {
$sql = "SELECT COUNT(DISTINCT session_id) as session_count
FROM {$prefix}visitor_log
WHERE {$whereClause}
AND session_id IS NOT NULL AND session_id != ''";
$result = $db->fetchRow($sql);
$cookieSessions = (int)($result['session_count'] ?? 0);
} catch (Exception $e) {
$cookieSessions = 0;
}
}
$legacyWhere = $whereClause;
if ($hasIdentity) {
$legacyWhere .= " AND (session_id IS NULL OR session_id = '')";
}
try {
$sql = "SELECT
COUNT(*) as session_count
FROM (
SELECT
ip,
time,
CASE
WHEN LAG(time) OVER (PARTITION BY ip ORDER BY time) IS NULL
OR TIMESTAMPDIFF(MINUTE, LAG(time) OVER (PARTITION BY ip ORDER BY time), time) > 30
THEN 1
ELSE 0
END as is_new_session
FROM {$prefix}visitor_log
WHERE {$legacyWhere}
) as session_data
WHERE is_new_session = 1";
$result = $db->fetchRow($sql);
$legacySessions = (int)($result['session_count'] ?? 0);
} catch (Exception $e) {
$sql = "SELECT COUNT(DISTINCT ip) as session_count FROM {$prefix}visitor_log WHERE {$legacyWhere}";
$result = $db->fetchRow($sql);
$legacySessions = (int)($result['session_count'] ?? 0);
}
return $cookieSessions + $legacySessions;
}
/**
* 按天或按小时批量计算会话数(一次扫描,避免 N 次窗口函数查询)
*
* @param string $groupExpr SQL 分组表达式,如 DATE(time) 或 HOUR(time)
* @return array key => session_count
*/
function calculateSessionsGrouped($db, $prefix, $whereClause, $groupExpr, $hasIdentity = false)
{
$map = array();
if ($hasIdentity) {
try {
$sql = "SELECT {$groupExpr} as bucket, COUNT(DISTINCT session_id) as session_count
FROM {$prefix}visitor_log
WHERE {$whereClause}
AND session_id IS NOT NULL AND session_id != ''
GROUP BY bucket";
$rows = $db->fetchAll($sql);
foreach ($rows as $row) {
$map[(string)$row['bucket']] = (int)$row['session_count'];
}
} catch (Exception $e) {
// ignore
}
}
$legacyWhere = $whereClause;
if ($hasIdentity) {
$legacyWhere .= " AND (session_id IS NULL OR session_id = '')";
}
try {
$sql = "SELECT
bucket,
COUNT(*) as session_count
FROM (
SELECT
{$groupExpr} as bucket,
CASE
WHEN LAG(time) OVER (PARTITION BY ip ORDER BY time) IS NULL
OR TIMESTAMPDIFF(MINUTE, LAG(time) OVER (PARTITION BY ip ORDER BY time), time) > 30
THEN 1
ELSE 0
END as is_new_session
FROM {$prefix}visitor_log
WHERE {$legacyWhere}
) as session_data
WHERE is_new_session = 1
GROUP BY bucket";
$rows = $db->fetchAll($sql);
foreach ($rows as $row) {
$key = (string)$row['bucket'];
$map[$key] = isset($map[$key]) ? $map[$key] + (int)$row['session_count'] : (int)$row['session_count'];
}
return $map;
} catch (Exception $e) {
// 旧版 MySQL:用独立 IP 数近似会话数
$sql = "SELECT {$groupExpr} as bucket, COUNT(DISTINCT ip) as session_count
FROM {$prefix}visitor_log
WHERE {$legacyWhere}
GROUP BY bucket";
$rows = $db->fetchAll($sql);
foreach ($rows as $row) {
$key = (string)$row['bucket'];
$map[$key] = isset($map[$key]) ? $map[$key] + (int)$row['session_count'] : (int)$row['session_count'];
}
return $map;
}
}
/**
* UV 表达式:对齐 Umami visitors = COUNT DISTINCT session_id
* 本插件 visitor_id 即 Umami sessionId;旧数据回退 IP+UA
*/
function uniqueVisitorExpr($hasIdentity)
{
if ($hasIdentity) {
return "COUNT(DISTINCT COALESCE(NULLIF(visitor_id, ''), CONCAT('legacy:', ip, IFNULL(user_agent, ''))))";
}
return "COUNT(DISTINCT CONCAT(ip, IFNULL(user_agent, '')))";
}
/**
* 获取趋势数据
*/
function getTrendData($db, $prefix, $startDate, $endDate)
{
try {
// 解析日期范围
$start = new DateTime($startDate);
$end = new DateTime($endDate);
$interval = $start->diff($end);
$days = $interval->days + 1; // 包含结束日期
// 判断是否为单日数据(需要按小时显示)
$isSingleDay = $days === 1;
// 使用时间范围条件,避免 DATE(time)= 导致无法走索引
$rangeWhere = "time >= '{$startDate}' AND time <= '{$endDate}'";
$hasIdentity = VisitorLoggerPro_DbOptimize::hasIdentityColumns($db, $prefix);
$uvExpr = uniqueVisitorExpr($hasIdentity);
if ($isSingleDay) {
// 单日数据:按小时分组
$hours = [];
for ($h = 0; $h < 24; $h++) {
$hours[] = sprintf('%02d:00', $h);
}
$sql = "SELECT
HOUR(time) as hour,
COUNT(*) as pv_count,
COUNT(DISTINCT ip) as unique_ip_count,
{$uvExpr} as unique_visitor_count
FROM {$prefix}visitor_log
WHERE {$rangeWhere}
GROUP BY HOUR(time)
ORDER BY hour ASC";
$results = $db->fetchAll($sql);
$dataMap = [];
foreach ($results as $row) {
$hour = sprintf('%02d:00', $row['hour']);
$dataMap[$hour] = [
'pv_count' => (int)$row['pv_count'],
'unique_ip_count' => (int)$row['unique_ip_count'],
'unique_visitor_count' => (int)$row['unique_visitor_count']
];
}
// 一次查出全天各小时会话数(替代 24 次窗口查询)
$sessionByHour = calculateSessionsGrouped($db, $prefix, $rangeWhere, 'HOUR(time)', $hasIdentity);
foreach ($hours as $hour) {
$hourNum = (string)intval(substr($hour, 0, 2));
$sessionCount = isset($sessionByHour[$hourNum]) ? $sessionByHour[$hourNum] : 0;
if (isset($dataMap[$hour])) {
$dataMap[$hour]['session_count'] = $sessionCount;
} else {
$dataMap[$hour] = [
'pv_count' => 0,
'unique_ip_count' => 0,
'unique_visitor_count' => 0,
'session_count' => $sessionCount
];
}
}
$data = [];
foreach ($hours as $hour) {
$data[] = [
'date' => $hour,
'pv_count' => $dataMap[$hour]['pv_count'] ?? 0,
'unique_ip_count' => $dataMap[$hour]['unique_ip_count'] ?? 0,
'unique_visitor_count' => $dataMap[$hour]['unique_visitor_count'] ?? 0,
'session_count' => $dataMap[$hour]['session_count'] ?? 0
];
}
} else {
// 多日数据:按天分组
$dates = [];
$current = clone $start;
for ($i = 0; $i < $days; $i++) {
$dates[] = $current->format('Y-m-d');
$current->add(new DateInterval('P1D'));
}
$sql = "SELECT
DATE(time) as date,
COUNT(*) as pv_count,
COUNT(DISTINCT ip) as unique_ip_count,
{$uvExpr} as unique_visitor_count
FROM {$prefix}visitor_log
WHERE {$rangeWhere}
GROUP BY DATE(time)
ORDER BY date ASC";
$results = $db->fetchAll($sql);
$dataMap = [];
foreach ($results as $row) {
$dataMap[$row['date']] = [
'pv_count' => (int)$row['pv_count'],
'unique_ip_count' => (int)$row['unique_ip_count'],
'unique_visitor_count' => (int)$row['unique_visitor_count']
];
}
// 一次查出各天会话数(替代按天 N 次窗口查询)
$sessionByDate = calculateSessionsGrouped($db, $prefix, $rangeWhere, 'DATE(time)', $hasIdentity);
foreach ($dates as $date) {
$sessionCount = isset($sessionByDate[$date]) ? $sessionByDate[$date] : 0;
if (isset($dataMap[$date])) {
$dataMap[$date]['session_count'] = $sessionCount;
} else {
$dataMap[$date] = [
'pv_count' => 0,
'unique_ip_count' => 0,
'unique_visitor_count' => 0,
'session_count' => $sessionCount
];
}
}
$data = [];
foreach ($dates as $date) {
$data[] = [
'date' => $date,
'pv_count' => $dataMap[$date]['pv_count'] ?? 0,
'unique_ip_count' => $dataMap[$date]['unique_ip_count'] ?? 0,
'unique_visitor_count' => $dataMap[$date]['unique_visitor_count'] ?? 0,
'session_count' => $dataMap[$date]['session_count'] ?? 0
];
}
}
// 总数:从已聚合数据求和(PV),其余一次范围查询;会话一次计算
$totalPv = 0;
foreach ($data as $row) {
$totalPv += (int)$row['pv_count'];
}
$totalSql = "SELECT
COUNT(DISTINCT ip) as total_unique_ip,
{$uvExpr} as total_unique_visitor
FROM {$prefix}visitor_log
WHERE {$rangeWhere}";
$totalResult = $db->fetchRow($totalSql);
$totalUniqueIp = (int)($totalResult['total_unique_ip'] ?? 0);
$totalUniqueVisitor = (int)($totalResult['total_unique_visitor'] ?? 0);
$totalSession = calculateSessions($db, $prefix, $rangeWhere, $hasIdentity);
return [
'success' => true,
'data' => $data,
'range' => [
'start' => $startDate,
'end' => $endDate,
'days' => $days,
'is_single_day' => $isSingleDay
],
'totals' => [
'total_pv' => $totalPv,
'total_unique_ip' => $totalUniqueIp,
'total_unique_visitor' => $totalUniqueVisitor,
'total_session' => $totalSession
],
'method' => [
'identity_columns' => $hasIdentity,
'uv' => $hasIdentity ? 'umami_session_id_ip_ua_month_salt' : 'ip_user_agent',
'session' => $hasIdentity ? 'umami_visit_id_30m' : 'ip_gap_30m'
]
];
} catch (Exception $e) {
return [
'success' => false,
'error' => $e->getMessage()
];
}
}