483 lines
19 KiB
PHP
483 lines
19 KiB
PHP
<?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),
|
||
];
|
||
}
|
||
}
|