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())); } }