Files
CardAuth/backend/app/Services/InstallationService.php

483 lines
19 KiB
PHP
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<?php
namespace App\Services;
use App\Core\Database;
/**
* 安装记录服务
* 安装次数统计、存活/已删除、最后上线
*/
class InstallationService
{
/**
* 上报/登记安装(check-update / verify 成功时调用)
* 同项目同机器存在 active 记录则更新,否则新建(重装/新设备独立计数)
*
* @return array {install_id: int, is_new: bool}
*/
public static function upsert(
int $projectId,
string $machineId,
string $ip = '',
string $deviceInfo = '',
string $clientVersion = '',
?int $cardId = null,
string $cardKey = ''
): array {
$db = Database::getInstance();
$existing = $db->fetch(
"SELECT id FROM {$db->table('installations')}
WHERE project_id = ? AND machine_id = ? AND status = 'active'
ORDER BY id DESC LIMIT 1",
[$projectId, $machineId]
);
if ($existing) {
$updateCardId = $cardId;
$updateCardKey = $cardKey !== '' ? $cardKey : null;
$updateDevice = $deviceInfo !== '' ? $deviceInfo : null;
$updateVersion = $clientVersion !== '' ? $clientVersion : null;
$db->execute(
"UPDATE {$db->table('installations')}
SET ip = ?, device_info = ?, client_version = ?,
last_online_at = NOW(),
card_id = COALESCE(?, card_id),
card_key = COALESCE(?, card_key)
WHERE id = ?",
[
$ip !== '' ? $ip : null,
$updateDevice,
$updateVersion,
$updateCardId,
$updateCardKey,
$existing['id'],
]
);
self::recordOnline($projectId, $machineId);
return ['install_id' => (int) $existing['id'], 'is_new' => false];
}
$installId = $db->insert(
"INSERT INTO {$db->table('installations')}
(project_id, card_id, card_key, machine_id, ip, device_info, client_version,
status, install_count, last_online_at, installed_at)
VALUES (?, ?, ?, ?, ?, ?, ?, 'active', 1, NOW(), NOW())",
[
$projectId,
$cardId,
$cardKey,
$machineId,
$ip !== '' ? $ip : null,
$deviceInfo !== '' ? $deviceInfo : null,
$clientVersion !== '' ? $clientVersion : null,
]
);
self::recordOnline($projectId, $machineId);
return ['install_id' => $installId, 'is_new' => true];
}
/**
* 记录当日在线(按天去重,供日/周/月在线统计)
*/
private static function recordOnline(int $projectId, string $machineId): void
{
$db = Database::getInstance();
$db->execute(
"INSERT INTO {$db->table('online_logs')}
(project_id, machine_id, online_date, first_seen_at, last_seen_at)
VALUES (?, ?, CURDATE(), NOW(), NOW())
ON DUPLICATE KEY UPDATE last_seen_at = NOW()",
[$projectId, $machineId]
);
}
/**
* 仅更新在线时间(心跳)
*/
public static function heartbeat(
int $projectId,
string $machineId,
string $ip = '',
string $deviceInfo = '',
string $clientVersion = '',
?int $cardId = null,
string $cardKey = ''
): array {
return self::upsert($projectId, $machineId, $ip, $deviceInfo, $clientVersion, $cardId, $cardKey);
}
/**
* 数据范围过滤(admin / project_admin / agent)
* @return array {where: string, params: array}
*/
public static function scope(array $user): array
{
$db = Database::getInstance();
$role = $user['role'] ?? '';
if ($role === 'admin') {
return ['where' => '', 'params' => []];
}
if ($role === 'project_admin') {
$projectIds = $user['project_ids'] ?? [];
if (empty($projectIds)) {
return ['where' => ' AND 1=0', 'params' => []];
}
$placeholders = implode(',', array_fill(0, count($projectIds), '?'));
return [
'where' => " AND i.project_id IN ({$placeholders})",
'params' => array_map('intval', $projectIds),
];
}
// 代理:仅本人及下级代理发放卡密相关的安装
$agentIds = PermissionService::getAgentIdsUnder((int) ($user['id'] ?? 0));
if (empty($agentIds)) {
return ['where' => ' AND 1=0', 'params' => []];
}
$placeholders = implode(',', array_fill(0, count($agentIds), '?'));
return [
'where' => " AND i.card_id IN (SELECT c.id FROM {$db->table('cards')} c WHERE c.use_user_id IN ({$placeholders}))",
'params' => array_map('intval', $agentIds),
];
}
/**
* 构建安装列表/统计 WHERE
*/
public static function buildListWhere(array $user, array $filters = []): array
{
$scope = self::scope($user);
$where = $scope['where'];
$params = $scope['params'];
if (!empty($filters['project_id'])) {
$where .= ' AND i.project_id = ?';
$params[] = (int) $filters['project_id'];
}
if (!empty($filters['status']) && in_array($filters['status'], ['active', 'deleted'], true)) {
$where .= ' AND i.status = ?';
$params[] = $filters['status'];
}
if (!empty($filters['keyword'])) {
$where .= ' AND (i.machine_id LIKE ? OR i.card_key LIKE ? OR i.ip LIKE ?)';
$kw = '%' . $filters['keyword'] . '%';
$params[] = $kw;
$params[] = $kw;
$params[] = $kw;
}
if (!empty($filters['date_from'])) {
$where .= ' AND i.installed_at >= ?';
$params[] = $filters['date_from'] . ' 00:00:00';
}
if (!empty($filters['date_to'])) {
$where .= ' AND i.installed_at <= ?';
$params[] = $filters['date_to'] . ' 23:59:59';
}
return ['where' => $where, 'params' => $params];
}
/**
* 安装统计(含已删除)
*/
public static function stats(array $user, ?int $projectId = null): array
{
$db = Database::getInstance();
$whereList = self::buildListWhere($user, $projectId ? ['project_id' => $projectId] : []);
$where = $whereList['where'];
$params = $whereList['params'];
$total = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i WHERE 1=1 {$where}",
$params
);
$active = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i WHERE i.status = 'active' {$where}",
$params
);
$deleted = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i WHERE i.status = 'deleted' {$where}",
$params
);
$todayNew = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i WHERE DATE(i.installed_at) = CURDATE() {$where}",
$params
);
$weekNew = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i WHERE i.installed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) {$where}",
$params
);
$lastOnline = $db->fetchColumn(
"SELECT MAX(i.last_online_at) FROM {$db->table('installations')} i WHERE i.last_online_at IS NOT NULL {$where}",
$params
);
$projects = $db->fetchAll(
"SELECT i.project_id, p.name AS project_name,
COUNT(*) AS total_installs,
SUM(CASE WHEN i.status = 'active' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN i.status = 'deleted' THEN 1 ELSE 0 END) AS deleted,
SUM(CASE WHEN DATE(i.installed_at) = CURDATE() THEN 1 ELSE 0 END) AS today_new,
MAX(i.last_online_at) AS last_online_at
FROM {$db->table('installations')} i
LEFT JOIN {$db->table('projects')} p ON p.id = i.project_id
WHERE 1=1 {$where}
GROUP BY i.project_id, p.name
ORDER BY total_installs DESC",
$params
);
return [
'total_installs' => $total,
'active' => $active,
'deleted' => $deleted,
'today_new' => $todayNew,
'week_new' => $weekNew,
'last_online_at' => $lastOnline ?: null,
'projects' => array_map(static function (array $row): array {
return [
'project_id' => (int) $row['project_id'],
'project_name' => $row['project_name'],
'total_installs' => (int) $row['total_installs'],
'active' => (int) $row['active'],
'deleted' => (int) $row['deleted'],
'today_new' => (int) $row['today_new'],
'last_online_at' => $row['last_online_at'],
];
}, $projects),
];
}
/**
* 在线统计:当前在线(5分钟内心跳)+ 按天去重的日/周/月在线用户数 + 趋势 + 分项目
* 统计口径:「在线用户」= 有心跳的机器指纹(machine_id)
*
* @param string $period day|week|month|year 趋势粒度
*/
public static function onlineStats(array $user, ?int $projectId = null, string $period = 'day'): array
{
$db = Database::getInstance();
$scope = self::scopeLogs($user);
$where = $scope['where'];
$params = $scope['params'];
$projWhere = '';
$projParams = [];
if ($projectId) {
$projWhere = ' AND i.project_id = ?';
$projParams[] = $projectId;
}
// 当前在线:最后心跳在 5 分钟内
$current = (int) $db->fetchColumn(
"SELECT COUNT(*) FROM {$db->table('installations')} i
WHERE i.status = 'active'
AND i.last_online_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
{$where}{$projWhere}",
array_merge($params, $projParams)
);
$logWhere = " AND i.online_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 YEAR), '%Y-01-01')" . $projWhere;
$logParams = $params;
// 今日 / 本周(周一起)/ 本月 / 本年 去重在线机器数
$counts = $db->fetch(
"SELECT
COUNT(DISTINCT CASE WHEN i.online_date = CURDATE() THEN i.machine_id END) AS today_online,
COUNT(DISTINCT CASE WHEN YEARWEEK(i.online_date, 1) = YEARWEEK(CURDATE(), 1) THEN i.machine_id END) AS week_online,
COUNT(DISTINCT CASE WHEN i.online_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN i.machine_id END) AS month_online,
COUNT(DISTINCT CASE WHEN i.online_date >= DATE_FORMAT(CURDATE(), '%Y-01-01') THEN i.machine_id END) AS year_online
FROM {$db->table('online_logs')} i
WHERE 1=1 {$logWhere}",
array_merge($logParams, $projParams)
);
// 分项目:当前在线(来自 installations)+ 日/周/月/年(来自 online_logs)
$curRows = $db->fetchAll(
"SELECT i.project_id, COUNT(*) AS current_online
FROM {$db->table('installations')} i
WHERE i.status = 'active'
AND i.last_online_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
{$where}{$projWhere}
GROUP BY i.project_id",
array_merge($params, $projParams)
);
$logRows = $db->fetchAll(
"SELECT i.project_id, p.name AS project_name,
COUNT(DISTINCT CASE WHEN i.online_date = CURDATE() THEN i.machine_id END) AS today_online,
COUNT(DISTINCT CASE WHEN YEARWEEK(i.online_date, 1) = YEARWEEK(CURDATE(), 1) THEN i.machine_id END) AS week_online,
COUNT(DISTINCT CASE WHEN i.online_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN i.machine_id END) AS month_online,
COUNT(DISTINCT CASE WHEN i.online_date >= DATE_FORMAT(CURDATE(), '%Y-01-01') THEN i.machine_id END) AS year_online
FROM {$db->table('online_logs')} i
LEFT JOIN {$db->table('projects')} p ON p.id = i.project_id
WHERE 1=1 {$logWhere}
GROUP BY i.project_id, p.name",
array_merge($logParams, $projParams)
);
$projects = [];
foreach ($logRows as $row) {
$pid = (int) $row['project_id'];
$projects[$pid] = [
'project_id' => $pid,
'project_name' => $row['project_name'],
'current_online' => 0,
'today_online' => (int) $row['today_online'],
'week_online' => (int) $row['week_online'],
'month_online' => (int) $row['month_online'],
'year_online' => (int) $row['year_online'],
];
}
foreach ($curRows as $row) {
$pid = (int) $row['project_id'];
if (!isset($projects[$pid])) {
$name = $db->fetchColumn("SELECT name FROM {$db->table('projects')} WHERE id = ?", [$pid]);
$projects[$pid] = [
'project_id' => $pid,
'project_name' => $name ?: ('项目#' . $pid),
'current_online' => 0,
'today_online' => 0,
'week_online' => 0,
'month_online' => 0,
'year_online' => 0,
];
}
$projects[$pid]['current_online'] = (int) $row['current_online'];
}
$projects = array_values($projects);
usort($projects, static fn (array $a, array $b) => $b['year_online'] <=> $a['year_online']);
// 趋势
$trend = [];
if ($period === 'week') {
$rows = $db->fetchAll(
"SELECT YEARWEEK(i.online_date, 1) AS yw, COUNT(DISTINCT i.machine_id) AS cnt
FROM {$db->table('online_logs')} i
WHERE i.online_date >= DATE_SUB(CURDATE(), INTERVAL 84 DAY) {$projWhere}
GROUP BY yw",
$projParams
);
$map = [];
foreach ($rows as $r) {
$map[(string) $r['yw']] = (int) $r['cnt'];
}
for ($i = 11; $i >= 0; $i--) {
$d = new \DateTime('today');
$d->modify("-" . ($i * 7) . " days");
$yw = (int) $d->format('oW');
$label = $d->format('o') . '-W' . $d->format('W');
$trend[] = ['label' => $label, 'value' => $map[(string) $yw] ?? 0];
}
} elseif ($period === 'month') {
$rows = $db->fetchAll(
"SELECT DATE_FORMAT(i.online_date, '%Y-%m') AS ym, COUNT(DISTINCT i.machine_id) AS cnt
FROM {$db->table('online_logs')} i
WHERE i.online_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH), '%Y-%m-01') {$projWhere}
GROUP BY ym",
$projParams
);
$map = [];
foreach ($rows as $r) {
$map[$r['ym']] = (int) $r['cnt'];
}
for ($i = 11; $i >= 0; $i--) {
$d = new \DateTime('today');
$d->modify('first day of this month');
$d->modify("-" . $i . " months");
$ym = $d->format('Y-m');
$trend[] = ['label' => $ym, 'value' => $map[$ym] ?? 0];
}
} elseif ($period === 'year') {
$rows = $db->fetchAll(
"SELECT DATE_FORMAT(i.online_date, '%Y') AS y, COUNT(DISTINCT i.machine_id) AS cnt
FROM {$db->table('online_logs')} i
WHERE i.online_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 YEAR), '%Y-01-01') {$projWhere}
GROUP BY y",
$projParams
);
$map = [];
foreach ($rows as $r) {
$map[$r['y']] = (int) $r['cnt'];
}
$now = new \DateTime('today');
for ($i = 11; $i >= 0; $i--) {
$d = clone $now;
$d->modify("-{$i} years");
$y = $d->format('Y');
$trend[] = ['label' => $y, 'value' => $map[$y] ?? 0];
}
} else {
$rows = $db->fetchAll(
"SELECT DATE(i.online_date) AS d, COUNT(DISTINCT i.machine_id) AS cnt
FROM {$db->table('online_logs')} i
WHERE i.online_date >= DATE_SUB(CURDATE(), INTERVAL 29 DAY) {$projWhere}
GROUP BY d",
$projParams
);
$map = [];
foreach ($rows as $r) {
$map[$r['d']] = (int) $r['cnt'];
}
for ($i = 29; $i >= 0; $i--) {
$d = new \DateTime('today');
$d->modify("-{$i} days");
$key = $d->format('Y-m-d');
$trend[] = ['label' => $d->format('m-d'), 'value' => $map[$key] ?? 0];
}
}
return [
'period' => $period,
'current_online' => $current,
'today_online' => (int) ($counts['today_online'] ?? 0),
'week_online' => (int) ($counts['week_online'] ?? 0),
'month_online' => (int) ($counts['month_online'] ?? 0),
'year_online' => (int) ($counts['year_online'] ?? 0),
'trend' => $trend,
'projects' => $projects,
];
}
/**
* 在线日志数据范围(online_logs 仅有 project_id 维度;agent 按其有卡密的项目)
* 别名固定为 i,返回 {where, params}
*/
private static function scopeLogs(array $user): array
{
$db = Database::getInstance();
$role = $user['role'] ?? '';
if ($role === 'admin') {
return ['where' => '', 'params' => []];
}
if ($role === 'project_admin') {
$projectIds = $user['project_ids'] ?? [];
if (empty($projectIds)) {
return ['where' => ' AND 1=0', 'params' => []];
}
$placeholders = implode(',', array_fill(0, count($projectIds), '?'));
return [
'where' => " AND i.project_id IN ({$placeholders})",
'params' => array_map('intval', $projectIds),
];
}
// 代理:本人及下级代理有卡密的项目
$agentIds = PermissionService::getAgentIdsUnder((int) ($user['id'] ?? 0));
if (empty($agentIds)) {
return ['where' => ' AND 1=0', 'params' => []];
}
$placeholders = implode(',', array_fill(0, count($agentIds), '?'));
return [
'where' => " AND i.project_id IN (SELECT DISTINCT c.project_id FROM {$db->table('cards')} c WHERE c.use_user_id IN ({$placeholders}))",
'params' => array_map('intval', $agentIds),
];
}
}