You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
virt/web/app/Services/SiteAnalyticsService.php

608 lines
23 KiB
PHTML

<?php
namespace App\Services;
use App\Models\SiteVisitDailyStat;
use App\Models\SiteVisitEvent;
use App\Utils\QQWry;
use DateTimeImmutable;
use DateTimeZone;
use Illuminate\Database\Schema\Blueprint;
class SiteAnalyticsService
{
private const EVENT_VISIT = 'visit';
public static function tablesAvailable()
{
$connection = (new SiteVisitEvent())->getConnection();
$schema = $connection->getSchemaBuilder();
return $schema->hasTable('site_visit_events')
&& $schema->hasTable('site_visit_daily_stats')
&& $schema->hasTable('site_security_events');
}
public static function ensureTables()
{
$connection = (new SiteVisitEvent())->getConnection();
$schema = $connection->getSchemaBuilder();
if (!$schema->hasTable('site_visit_events')) {
$schema->create('site_visit_events', function (Blueprint $table) {
$table->bigIncrements('id');
$table->char('event_key', 64);
$table->string('event_type', 16);
$table->char('visitor_id', 32);
$table->char('session_id', 32);
$table->unsignedBigInteger('user_id')->default(0);
$table->string('site_host', 255);
$table->string('landing_path', 255);
$table->string('referrer_host', 255)->default('(direct)');
$table->string('ip_address', 64)->default('');
$table->char('ip_hash', 64);
$table->string('user_agent', 500)->default('');
$table->string('browser', 32)->default('Other');
$table->string('operating_system', 32)->default('Other');
$table->string('device_type', 16)->default('desktop');
$table->string('utm_source', 100)->default('');
$table->string('utm_medium', 100)->default('');
$table->string('utm_campaign', 100)->default('');
$table->unsignedSmallInteger('status_code')->default(0);
$table->unsignedBigInteger('occurred_at')->default(0);
$table->unsignedBigInteger('created_at')->default(0);
$table->unique('event_key', 'uniq_site_visit_event_key');
$table->index(['event_type', 'occurred_at'], 'idx_site_visit_type_time');
$table->index(['site_host', 'occurred_at'], 'idx_site_visit_host_time');
$table->index(['visitor_id', 'occurred_at'], 'idx_site_visit_visitor_time');
$table->index(['user_id', 'occurred_at'], 'idx_site_visit_user_time');
});
}
if (!$schema->hasTable('site_visit_daily_stats')) {
$schema->create('site_visit_daily_stats', function (Blueprint $table) {
$table->bigIncrements('id');
$table->date('stat_date');
$table->string('event_type', 16);
$table->string('dimension_type', 32);
$table->string('dimension_value', 255);
$table->unsignedBigInteger('event_count')->default(0);
$table->unsignedBigInteger('unique_visitors')->default(0);
$table->unsignedBigInteger('created_at')->default(0);
$table->unsignedBigInteger('updated_at')->default(0);
$table->unique(
['stat_date', 'event_type', 'dimension_type', 'dimension_value'],
'uniq_site_visit_daily_dimension'
);
$table->index(['event_type', 'dimension_type', 'stat_date'], 'idx_site_visit_daily_lookup');
$table->index(['dimension_type', 'dimension_value', 'stat_date'], 'idx_site_visit_daily_value');
});
}
if (!$schema->hasTable('site_security_events')) {
$schema->create('site_security_events', function (Blueprint $table) {
$table->bigIncrements('id');
$table->char('event_key', 64);
$table->unsignedBigInteger('user_id')->default(0);
$table->char('visitor_id', 32);
$table->char('session_id', 32);
$table->string('site_host', 255);
$table->string('request_path', 500);
$table->string('request_method', 12);
$table->unsignedSmallInteger('status_code')->default(0);
$table->string('reason_code', 32);
$table->string('risk_level', 16);
$table->string('ip_address', 64)->default('');
$table->char('ip_hash', 64);
$table->string('user_agent', 500)->default('');
$table->string('browser', 32)->default('Other');
$table->string('operating_system', 32)->default('Other');
$table->string('device_type', 16)->default('desktop');
$table->unsignedInteger('hit_count')->default(1);
$table->unsignedBigInteger('first_seen_at')->default(0);
$table->unsignedBigInteger('last_seen_at')->default(0);
$table->unsignedBigInteger('created_at')->default(0);
$table->unsignedBigInteger('updated_at')->default(0);
$table->unique('event_key', 'uniq_site_security_event_key');
$table->index(['last_seen_at', 'risk_level'], 'idx_site_security_time_risk');
$table->index(['user_id', 'last_seen_at'], 'idx_site_security_user_time');
$table->index(['ip_hash', 'last_seen_at'], 'idx_site_security_ip_time');
$table->index(['reason_code', 'last_seen_at'], 'idx_site_security_reason_time');
$table->index(['status_code', 'last_seen_at'], 'idx_site_security_status_time');
});
}
}
public static function refreshRecentDays($days = 2)
{
self::ensureTables();
$days = max(1, min(31, (int) $days));
$today = new DateTimeImmutable('today', self::timezone());
$refreshed = 0;
for ($offset = $days - 1; $offset >= 0; $offset--) {
self::refreshDate($today->modify('-' . $offset . ' days'));
$refreshed++;
}
return $refreshed;
}
public static function normalizeDateRange($from = '', $to = '')
{
$timezone = self::timezone();
$today = new DateTimeImmutable('today', $timezone);
$toDate = self::parseDate($to, $timezone) ?: $today;
if ($toDate > $today) {
$toDate = $today;
}
$fromDate = self::parseDate($from, $timezone) ?: $toDate->modify('-6 days');
if ($fromDate > $toDate) {
$fromDate = $toDate;
}
$minimum = $toDate->modify('-365 days');
if ($fromDate < $minimum) {
$fromDate = $minimum;
}
return [$fromDate->format('Y-m-d'), $toDate->format('Y-m-d')];
}
public static function report($from, $to, $recentLimit = 60, $ensureTables = true)
{
if ($ensureTables) {
self::ensureTables();
} elseif (!self::tablesAvailable()) {
throw new \RuntimeException('网站统计数据表尚未创建');
}
list($from, $to) = self::normalizeDateRange($from, $to);
$connection = (new SiteVisitDailyStat())->getConnection();
$summary = [
'pageviews' => 0,
'unique_visitors' => 0,
'sessions' => 0,
'direct_pageviews' => 0,
];
$total = $connection->table('site_visit_daily_stats')
->whereBetween('stat_date', [$from, $to])
->where('event_type', self::EVENT_VISIT)
->where('dimension_type', 'total')
->where('dimension_value', 'all')
->selectRaw('SUM(event_count) AS event_count')
->selectRaw('SUM(unique_visitors) AS unique_visitors')
->first();
if ($total !== null) {
$summary['pageviews'] = (int) $total->event_count;
$summary['unique_visitors'] = (int) $total->unique_visitors;
}
$summary['sessions'] = (int) $connection->table('site_visit_daily_stats')
->whereBetween('stat_date', [$from, $to])
->where('event_type', self::EVENT_VISIT)
->where('dimension_type', 'sessions')
->where('dimension_value', 'all')
->sum('event_count');
$direct = $connection->table('site_visit_daily_stats')
->whereBetween('stat_date', [$from, $to])
->where('event_type', self::EVENT_VISIT)
->where('dimension_type', 'referrer_host')
->where('dimension_value', '(direct)')
->sum('event_count');
$summary['direct_pageviews'] = (int) $direct;
$trendRows = $connection->table('site_visit_daily_stats')
->whereBetween('stat_date', [$from, $to])
->where('event_type', self::EVENT_VISIT)
->whereIn('dimension_type', ['total', 'sessions'])
->where('dimension_value', 'all')
->orderBy('stat_date', 'asc')
->get();
$trend = self::buildTrend($from, $to, $trendRows);
$ipRankings = self::ranking(
$connection,
$from,
$to,
self::EVENT_VISIT,
'ip_address',
20
);
return [
'from' => $from,
'to' => $to,
'summary' => $summary,
'trend' => $trend,
'rankings' => [
'site_hosts' => self::ranking($connection, $from, $to, self::EVENT_VISIT, 'site_host'),
'landing_paths' => self::ranking($connection, $from, $to, self::EVENT_VISIT, 'landing_path'),
'referrer_hosts' => self::ranking($connection, $from, $to, self::EVENT_VISIT, 'referrer_host'),
'browsers' => self::ranking($connection, $from, $to, self::EVENT_VISIT, 'browser'),
'devices' => self::ranking($connection, $from, $to, self::EVENT_VISIT, 'device_type'),
'operating_systems' => self::ranking(
$connection,
$from,
$to,
self::EVENT_VISIT,
'operating_system'
),
'ip_addresses' => self::attachIpLocations($ipRankings),
],
'recent_events' => self::recentEvents($from, $to, $recentLimit),
];
}
public static function normalizeHost($value)
{
$host = strtolower(rtrim(trim((string) $value), '.'));
if ($host === '' || strlen($host) > 253) {
return '';
}
if (filter_var($host, FILTER_VALIDATE_IP) !== false) {
return $host;
}
if (preg_match('/^(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)*[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?$/D', $host) !== 1) {
return '';
}
return $host;
}
private static function refreshDate(DateTimeImmutable $date)
{
$connection = (new SiteVisitEvent())->getConnection();
$date = $date->setTime(0, 0, 0);
$next = $date->modify('+1 day');
$statDate = $date->format('Y-m-d');
$dayStart = $date->getTimestamp();
$dayEnd = $next->getTimestamp();
$now = time();
$dimensions = [
'site_host' => 'site_host',
'landing_path' => 'landing_path',
'referrer_host' => 'referrer_host',
'browser' => 'browser',
'operating_system' => 'operating_system',
'device_type' => 'device_type',
'ip_address' => 'ip_address',
];
$connection->transaction(function () use (
$connection,
$dimensions,
$statDate,
$dayStart,
$dayEnd,
$now
) {
$connection->table('site_visit_daily_stats')->where('stat_date', $statDate)->delete();
$insertRows = [];
$events = $connection->table('site_visit_events')
->where('event_type', self::EVENT_VISIT)
->where('occurred_at', '>=', $dayStart)
->where('occurred_at', '<', $dayEnd);
$total = (clone $events)
->selectRaw('COUNT(*) AS event_count')
->selectRaw('COUNT(DISTINCT visitor_id) AS unique_visitors')
->selectRaw('COUNT(DISTINCT session_id) AS session_count')
->first();
if ($total !== null && (int) $total->event_count > 0) {
$insertRows[] = self::statRow(
$statDate,
self::EVENT_VISIT,
'total',
'all',
(int) $total->event_count,
(int) $total->unique_visitors,
$now
);
$insertRows[] = self::statRow(
$statDate,
self::EVENT_VISIT,
'sessions',
'all',
(int) $total->session_count,
(int) $total->unique_visitors,
$now
);
}
foreach ($dimensions as $dimensionType => $column) {
$rows = (clone $events)
->select($column)
->selectRaw('COUNT(*) AS event_count')
->selectRaw('COUNT(DISTINCT visitor_id) AS unique_visitors')
->groupBy($column)
->get();
foreach ($rows as $row) {
$value = trim((string) $row->{$column});
if ($value === '') {
$value = '(unknown)';
}
$insertRows[] = self::statRow(
$statDate,
self::EVENT_VISIT,
$dimensionType,
$value,
(int) $row->event_count,
(int) $row->unique_visitors,
$now
);
}
}
foreach (array_chunk($insertRows, 500) as $chunk) {
$connection->table('site_visit_daily_stats')->insert($chunk);
}
}, 3);
}
private static function statRow($date, $eventType, $dimensionType, $dimensionValue, $count, $unique, $now)
{
return [
'stat_date' => $date,
'event_type' => $eventType,
'dimension_type' => $dimensionType,
'dimension_value' => $dimensionValue,
'event_count' => $count,
'unique_visitors' => $unique,
'created_at' => $now,
'updated_at' => $now,
];
}
private static function ranking($connection, $from, $to, $eventType, $dimensionType, $limit = 10)
{
$rows = $connection->table('site_visit_daily_stats')
->whereBetween('stat_date', [$from, $to])
->where('event_type', $eventType)
->where('dimension_type', $dimensionType)
->select('dimension_value')
->selectRaw('SUM(event_count) AS event_count')
->selectRaw('SUM(unique_visitors) AS unique_visitors')
->groupBy('dimension_value')
->orderBy('event_count', 'desc')
->limit(max(1, min(50, (int) $limit)))
->get();
$result = [];
foreach ($rows as $row) {
$result[] = [
'value' => (string) $row->dimension_value,
'count' => (int) $row->event_count,
'unique_visitors' => (int) $row->unique_visitors,
];
}
return $result;
}
private static function attachIpLocations(array $rows)
{
$database = null;
$basePath = defined('BASE_PATH') ? constant('BASE_PATH') : dirname(__DIR__, 2);
$databasePath = $basePath . '/storage/qqwry.dat';
if (is_file($databasePath) && is_readable($databasePath)) {
$database = new QQWry();
}
foreach ($rows as &$row) {
$row['location'] = self::resolveIpLocation($row['value'], $database);
}
unset($row);
return $rows;
}
private static function resolveIpLocation($ip, $database)
{
$ip = trim((string) $ip);
if (stripos($ip, '::ffff:') === 0) {
$mappedIp = substr($ip, 7);
if (filter_var($mappedIp, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) !== false) {
$ip = $mappedIp;
}
}
if (filter_var($ip, FILTER_VALIDATE_IP) === false) {
return '未知';
}
if ($ip === '::1' || strpos($ip, '127.') === 0) {
return '本机';
}
if (self::isNonPublicIp($ip)) {
return '内网或保留地址';
}
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6) !== false) {
return 'IPv6(本地库暂不支持)';
}
if ($database === null) {
return 'IP 库未安装';
}
$location = @$database->getlocation($ip);
if (!is_array($location)) {
return '未知';
}
$country = self::decodeIpLocationText(isset($location['country']) ? $location['country'] : '');
$area = self::decodeIpLocationText(isset($location['area']) ? $location['area'] : '');
if ($area === $country) {
$area = '';
}
$result = trim($country . ($area === '' ? '' : ' ' . $area));
return $result === '' ? '未知' : $result;
}
private static function isNonPublicIp($ip)
{
if (filter_var(
$ip,
FILTER_VALIDATE_IP,
FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE
) === false) {
return true;
}
$reservedRanges = [
'0.0.0.0/8',
'100.64.0.0/10',
'192.0.0.0/24',
'192.0.2.0/24',
'198.18.0.0/15',
'198.51.100.0/24',
'203.0.113.0/24',
'224.0.0.0/4',
'240.0.0.0/4',
'100::/64',
'2001:2::/48',
'2001:db8::/32',
'ff00::/8',
];
foreach ($reservedRanges as $range) {
if (self::ipInCidr($ip, $range)) {
return true;
}
}
return false;
}
private static function ipInCidr($ip, $cidr)
{
list($network, $prefixLength) = explode('/', $cidr, 2);
$ipBytes = @inet_pton($ip);
$networkBytes = @inet_pton($network);
if ($ipBytes === false || $networkBytes === false || strlen($ipBytes) !== strlen($networkBytes)) {
return false;
}
$prefixLength = (int) $prefixLength;
$byteCount = intdiv($prefixLength, 8);
if ($byteCount > 0 && substr($ipBytes, 0, $byteCount) !== substr($networkBytes, 0, $byteCount)) {
return false;
}
$remainingBits = $prefixLength % 8;
if ($remainingBits === 0) {
return true;
}
$mask = (0xff << (8 - $remainingBits)) & 0xff;
return (ord($ipBytes[$byteCount]) & $mask) === (ord($networkBytes[$byteCount]) & $mask);
}
private static function decodeIpLocationText($value)
{
$value = trim((string) $value);
if ($value === '' || strtoupper($value) === 'CZ88.NET') {
return '';
}
if (!function_exists('iconv')) {
return '';
}
$converted = @iconv('GBK', 'UTF-8//IGNORE', $value);
if ($converted === false) {
return '';
}
$converted = trim($converted);
return strtoupper($converted) === 'CZ88.NET' ? '' : $converted;
}
private static function buildTrend($from, $to, $rows)
{
$map = [];
foreach ($rows as $row) {
$date = (string) $row->stat_date;
if (!isset($map[$date])) {
$map[$date] = ['pageviews' => 0, 'unique_visitors' => 0, 'sessions' => 0];
}
if ((string) $row->dimension_type === 'total') {
$map[$date]['pageviews'] = (int) $row->event_count;
$map[$date]['unique_visitors'] = (int) $row->unique_visitors;
} elseif ((string) $row->dimension_type === 'sessions') {
$map[$date]['sessions'] = (int) $row->event_count;
}
}
$cursor = new DateTimeImmutable($from, self::timezone());
$end = new DateTimeImmutable($to, self::timezone());
$result = [];
while ($cursor <= $end) {
$date = $cursor->format('Y-m-d');
$values = isset($map[$date])
? $map[$date]
: ['pageviews' => 0, 'unique_visitors' => 0, 'sessions' => 0];
$result[] = array_merge(['date' => $date], $values);
$cursor = $cursor->modify('+1 day');
}
return $result;
}
private static function recentEvents($from, $to, $limit)
{
$timezone = self::timezone();
$start = (new DateTimeImmutable($from, $timezone))->setTime(0, 0, 0)->getTimestamp();
$end = (new DateTimeImmutable($to, $timezone))->modify('+1 day')->setTime(0, 0, 0)->getTimestamp();
$limit = max(1, min(200, (int) $limit));
$rows = SiteVisitEvent::where('occurred_at', '>=', $start)
->where('occurred_at', '<', $end)
->where('event_type', self::EVENT_VISIT)
->orderBy('id', 'desc')
->limit($limit)
->get();
$result = [];
foreach ($rows as $row) {
$result[] = [
'id' => (int) $row->id,
'site_host' => (string) $row->site_host,
'landing_path' => (string) $row->landing_path,
'referrer_host' => (string) $row->referrer_host,
'ip_address' => (string) $row->ip_address,
'browser' => (string) $row->browser,
'operating_system' => (string) $row->operating_system,
'device_type' => (string) $row->device_type,
'user_id' => (int) $row->user_id,
'occurred_at' => (int) $row->occurred_at,
'occurred_at_text' => date('Y-m-d H:i:s', (int) $row->occurred_at),
];
}
return $result;
}
private static function parseDate($value, DateTimeZone $timezone)
{
$value = trim((string) $value);
if ($value === '') {
return null;
}
$date = DateTimeImmutable::createFromFormat('!Y-m-d', $value, $timezone);
$errors = DateTimeImmutable::getLastErrors();
if ($date === false || ($errors !== false && ($errors['warning_count'] > 0 || $errors['error_count'] > 0))) {
return null;
}
return $date;
}
private static function timezone()
{
return new DateTimeZone((string) Config::get('timeZone', date_default_timezone_get()));
}
}