586 lines
19 KiB
PHP
586 lines
19 KiB
PHP
<?php
|
||
// 设置错误报告
|
||
ini_set('display_errors', 1);
|
||
error_reporting(E_ALL);
|
||
|
||
// 数据库连接信息
|
||
$db_host = '192.168.2.4';
|
||
$db_port = 3307;
|
||
$db_name = 'task_reporter';
|
||
$db_user = 'task_reporter';
|
||
$db_pass = 'Pass12349ers!';
|
||
|
||
// 创建数据库连接
|
||
try {
|
||
$pdo = new PDO("mysql:host=$db_host;port=$db_port;dbname=$db_name", $db_user, $db_pass);
|
||
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
$pdo->exec("SET NAMES utf8");
|
||
} catch (PDOException $e) {
|
||
die("数据库连接失败: " . $e->getMessage());
|
||
}
|
||
|
||
// 创建任务表（如果不存在）
|
||
try {
|
||
$sql = "CREATE TABLE IF NOT EXISTS task (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(100) NOT NULL,
|
||
tool_name VARCHAR(100) NOT NULL,
|
||
task_name VARCHAR(100) NOT NULL,
|
||
time_saved VARCHAR(100) NOT NULL,
|
||
time_cost VARCHAR(100) NOT NULL,
|
||
timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
)";
|
||
$pdo->exec($sql);
|
||
} catch (PDOException $e) {
|
||
die("创建表失败: " . $e->getMessage());
|
||
}
|
||
|
||
// 处理添加任务的POST请求
|
||
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['action']) && $_GET['action'] === 'add_task') {
|
||
try {
|
||
$data = json_decode(file_get_contents('php://input'), true);
|
||
|
||
if (!$data) {
|
||
throw new Exception("无效的JSON数据");
|
||
}
|
||
|
||
$stmt = $pdo->prepare("INSERT INTO task (username, tool_name, task_name, time_saved, time_cost, timestamp)
|
||
VALUES (:username, :tool_name, :task_name, :time_saved, :time_cost, :timestamp)");
|
||
|
||
$stmt->bindParam(':username', $data['username']);
|
||
$stmt->bindParam(':tool_name', $data['tool_name']);
|
||
$stmt->bindParam(':task_name', $data['task_name']);
|
||
$stmt->bindParam(':time_saved', $data['time_saved']);
|
||
$stmt->bindParam(':time_cost', $data['time_cost']);
|
||
$stmt->bindParam(':timestamp', $data['timestamp']);
|
||
|
||
$stmt->execute();
|
||
|
||
$time = explode(".", $data['timestamp'])[0];
|
||
$time = str_replace("T", " ", $time);
|
||
|
||
header('Content-Type: application/json');
|
||
echo json_encode([
|
||
"message" => "$time {$data['tool_name']}-{$data['task_name']} reported successfully!"
|
||
]);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(400);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 处理查询任务数据的GET请求
|
||
if (isset($_GET['action']) && $_GET['action'] === 'query_task_data') {
|
||
try {
|
||
// 获取筛选参数
|
||
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
|
||
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
|
||
$task_name = isset($_GET['task_name']) ? $_GET['task_name'] : null;
|
||
$tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
|
||
$time_saved = isset($_GET['time_saved']) ? $_GET['time_saved'] : null;
|
||
$time_cost = isset($_GET['time_cost']) ? $_GET['time_cost'] : null;
|
||
$show_debug = isset($_GET['show_debug']) && $_GET['show_debug'] === 'true';
|
||
|
||
// 获取排序参数
|
||
$sort_by = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'timestamp';
|
||
$sort_direction = isset($_GET['sort_direction']) ? $_GET['sort_direction'] : 'desc';
|
||
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
|
||
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 50;
|
||
|
||
// 构建查询
|
||
$query = "SELECT * FROM task WHERE 1=1";
|
||
$params = [];
|
||
|
||
// 应用日期筛选
|
||
if ($start_date) {
|
||
$query .= " AND timestamp >= :start_date";
|
||
$params[':start_date'] = $start_date . " 00:00:00";
|
||
}
|
||
|
||
if ($end_date) {
|
||
$query .= " AND timestamp <= :end_date";
|
||
$params[':end_date'] = $end_date . " 23:59:59";
|
||
}
|
||
|
||
// 应用任务名称筛选
|
||
if ($task_name) {
|
||
$query .= " AND task_name LIKE :task_name";
|
||
$params[':task_name'] = "%$task_name%";
|
||
}
|
||
|
||
// 应用工具名称筛选
|
||
if ($tool_name) {
|
||
$query .= " AND tool_name LIKE :tool_name";
|
||
$params[':tool_name'] = "%$tool_name%";
|
||
}
|
||
|
||
// 排除调试工具（如果不显示调试）
|
||
if (!$show_debug) {
|
||
$query .= " AND tool_name NOT LIKE '%(Debug)%'";
|
||
}
|
||
|
||
// 应用排序
|
||
$valid_sort_fields = ['timestamp', 'time_saved', 'time_cost'];
|
||
if (!in_array($sort_by, $valid_sort_fields)) {
|
||
$sort_by = 'timestamp';
|
||
}
|
||
|
||
$query .= " ORDER BY $sort_by " . ($sort_direction === 'asc' ? 'ASC' : 'DESC');
|
||
|
||
// 应用分页
|
||
$offset = ($page - 1) * $per_page;
|
||
$query .= " LIMIT :offset, :limit";
|
||
$params[':offset'] = $offset;
|
||
$params[':limit'] = $per_page;
|
||
|
||
// 执行查询
|
||
$stmt = $pdo->prepare($query);
|
||
foreach ($params as $key => $value) {
|
||
if ($key === ':offset' || $key === ':limit') {
|
||
$stmt->bindValue($key, $value, PDO::PARAM_INT);
|
||
} else {
|
||
$stmt->bindValue($key, $value);
|
||
}
|
||
}
|
||
$stmt->execute();
|
||
|
||
$tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// 获取总记录数
|
||
$count_query = "SELECT COUNT(*) FROM task WHERE 1=1";
|
||
$count_params = [];
|
||
|
||
if ($start_date) {
|
||
$count_query .= " AND timestamp >= :start_date";
|
||
$count_params[':start_date'] = $start_date . " 00:00:00";
|
||
}
|
||
|
||
if ($end_date) {
|
||
$count_query .= " AND timestamp <= :end_date";
|
||
$count_params[':end_date'] = $end_date . " 23:59:59";
|
||
}
|
||
|
||
if ($task_name) {
|
||
$count_query .= " AND task_name LIKE :task_name";
|
||
$count_params[':task_name'] = "%$task_name%";
|
||
}
|
||
|
||
if ($tool_name) {
|
||
$count_query .= " AND tool_name LIKE :tool_name";
|
||
$count_params[':tool_name'] = "%$tool_name%";
|
||
}
|
||
|
||
if (!$show_debug) {
|
||
$count_query .= " AND tool_name NOT LIKE '%(Debug)%'";
|
||
}
|
||
|
||
$count_stmt = $pdo->prepare($count_query);
|
||
foreach ($count_params as $key => $value) {
|
||
$count_stmt->bindValue($key, $value);
|
||
}
|
||
$count_stmt->execute();
|
||
$total_count = $count_stmt->fetchColumn();
|
||
|
||
// 获取所有工具名称和任务名称（用于筛选）
|
||
$tools_stmt = $pdo->query("SELECT DISTINCT tool_name FROM task ORDER BY tool_name");
|
||
$tools = $tools_stmt->fetchAll(PDO::FETCH_COLUMN);
|
||
|
||
$tasks_stmt = $pdo->query("SELECT DISTINCT task_name FROM task ORDER BY task_name");
|
||
$task_names = $tasks_stmt->fetchAll(PDO::FETCH_COLUMN);
|
||
|
||
// 返回数据
|
||
$result = [
|
||
'tasks' => $tasks,
|
||
'total' => $total_count,
|
||
'page' => $page,
|
||
'per_page' => $per_page,
|
||
'total_pages' => ceil($total_count / $per_page),
|
||
'tools' => $tools,
|
||
'task_names' => $task_names
|
||
];
|
||
|
||
header('Content-Type: application/json');
|
||
echo json_encode($result);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(500);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 处理获取工具统计数据的GET请求
|
||
if (isset($_GET['action']) && $_GET['action'] === 'get_tool_statistics') {
|
||
try {
|
||
// 获取日期范围
|
||
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
|
||
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
|
||
|
||
// 构建查询
|
||
$query = "SELECT tool_name, COUNT(*) as request_count, SUM(time_saved) as time_saved
|
||
FROM task
|
||
WHERE tool_name NOT LIKE '%(Debug)%'";
|
||
$params = [];
|
||
|
||
if ($start_date) {
|
||
$query .= " AND timestamp >= :start_date";
|
||
$params[':start_date'] = $start_date . " 00:00:00";
|
||
}
|
||
|
||
if ($end_date) {
|
||
$query .= " AND timestamp <= :end_date";
|
||
$params[':end_date'] = $end_date . " 23:59:59";
|
||
}
|
||
|
||
$query .= " GROUP BY tool_name ORDER BY request_count DESC";
|
||
|
||
// 执行查询
|
||
$stmt = $pdo->prepare($query);
|
||
foreach ($params as $key => $value) {
|
||
$stmt->bindValue($key, $value);
|
||
}
|
||
$stmt->execute();
|
||
|
||
$tools_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// 计算总请求数和总节省时间
|
||
$total_requests = 0;
|
||
$total_time_saved = 0;
|
||
|
||
foreach ($tools_stats as $stat) {
|
||
$total_requests += $stat['request_count'];
|
||
$total_time_saved += $stat['time_saved'];
|
||
}
|
||
|
||
// 返回数据
|
||
$result = [
|
||
'tools' => $tools_stats,
|
||
'total_requests' => $total_requests,
|
||
'total_time_saved' => $total_time_saved
|
||
];
|
||
|
||
header('Content-Type: application/json');
|
||
echo json_encode($result);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(500);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 处理导出CSV的GET请求
|
||
if (isset($_GET['action']) && $_GET['action'] === 'export_csv') {
|
||
try {
|
||
// 获取筛选参数
|
||
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
|
||
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
|
||
$task_name = isset($_GET['task_name']) ? $_GET['task_name'] : null;
|
||
$tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
|
||
$show_debug = isset($_GET['show_debug']) && $_GET['show_debug'] === 'true';
|
||
|
||
// 构建查询
|
||
$query = "SELECT id, username, tool_name, task_name, time_saved, time_cost, timestamp
|
||
FROM task WHERE 1=1";
|
||
$params = [];
|
||
|
||
if ($start_date) {
|
||
$query .= " AND timestamp >= :start_date";
|
||
$params[':start_date'] = $start_date . " 00:00:00";
|
||
}
|
||
|
||
if ($end_date) {
|
||
$query .= " AND timestamp <= :end_date";
|
||
$params[':end_date'] = $end_date . " 23:59:59";
|
||
}
|
||
|
||
if ($task_name) {
|
||
$query .= " AND task_name LIKE :task_name";
|
||
$params[':task_name'] = "%$task_name%";
|
||
}
|
||
|
||
if ($tool_name) {
|
||
$query .= " AND tool_name LIKE :tool_name";
|
||
$params[':tool_name'] = "%$tool_name%";
|
||
}
|
||
|
||
if (!$show_debug) {
|
||
$query .= " AND tool_name NOT LIKE '%(Debug)%'";
|
||
}
|
||
|
||
$query .= " ORDER BY timestamp DESC";
|
||
|
||
// 执行查询
|
||
$stmt = $pdo->prepare($query);
|
||
foreach ($params as $key => $value) {
|
||
$stmt->bindValue($key, $value);
|
||
}
|
||
$stmt->execute();
|
||
|
||
$tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// 设置CSV文件头
|
||
header('Content-Type: text/csv; charset=utf-8');
|
||
header('Content-Disposition: attachment; filename=task_data.csv');
|
||
|
||
// 创建CSV输出流
|
||
$output = fopen('php://output', 'w');
|
||
|
||
// 添加CSV头行
|
||
fputcsv($output, ['ID', 'Username', 'Tool Name', 'Task Name', 'Time Saved', 'Time Cost', 'Timestamp']);
|
||
|
||
// 添加数据行
|
||
foreach ($tasks as $task) {
|
||
fputcsv($output, [
|
||
$task['id'],
|
||
$task['username'],
|
||
$task['tool_name'],
|
||
$task['task_name'],
|
||
$task['time_saved'],
|
||
$task['time_cost'],
|
||
$task['timestamp']
|
||
]);
|
||
}
|
||
|
||
fclose($output);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(500);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 处理获取特定工具任务的GET请求
|
||
if (isset($_GET['action']) && $_GET['action'] === 'get_tool_tasks') {
|
||
try {
|
||
$tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
|
||
|
||
if (!$tool_name) {
|
||
throw new Exception("Tool name is required");
|
||
}
|
||
|
||
// 查询该工具的所有任务
|
||
$stmt = $pdo->prepare("SELECT task_name, COUNT(*) as total_requests, SUM(time_saved) as time_saved
|
||
FROM task
|
||
WHERE tool_name = :tool_name AND tool_name NOT LIKE '%(Debug)%'
|
||
GROUP BY task_name
|
||
ORDER BY total_requests DESC");
|
||
$stmt->bindParam(':tool_name', $tool_name);
|
||
$stmt->execute();
|
||
|
||
$tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
header('Content-Type: application/json');
|
||
echo json_encode(['tasks' => $tasks]);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(500);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 处理获取趋势数据的GET请求
|
||
if (isset($_GET['action']) && $_GET['action'] === 'get_trend_data') {
|
||
try {
|
||
// 获取查询参数中的日期范围
|
||
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
|
||
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
|
||
|
||
// 如果没有指定日期范围，默认显示最近30天
|
||
if (!$start_date || !$end_date) {
|
||
$end_date = date('Y-m-d');
|
||
$start_date = date('Y-m-d', strtotime('-30 days'));
|
||
}
|
||
|
||
// 将字符串日期转换为日期对象
|
||
$start_datetime = date_create($start_date);
|
||
$end_datetime = date_create($end_date);
|
||
|
||
// 设置结束日期为当天的最后一秒
|
||
$end_datetime->setTime(23, 59, 59);
|
||
|
||
// 使用PDO查询
|
||
$stmt = $pdo->prepare("SELECT DATE(timestamp) as date, COUNT(*) as total_requests, SUM(time_saved) as time_saved
|
||
FROM task
|
||
WHERE timestamp BETWEEN :start_date AND :end_date
|
||
GROUP BY DATE(timestamp)
|
||
ORDER BY date");
|
||
$stmt->bindValue(':start_date', $start_datetime->format('Y-m-d 00:00:00'));
|
||
$stmt->bindValue(':end_date', $end_datetime->format('Y-m-d H:i:s'));
|
||
$stmt->execute();
|
||
|
||
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// 填充所有日期（包括没有数据的日期）
|
||
$dates = [];
|
||
$requests = [];
|
||
$time_saved = [];
|
||
|
||
// 创建日期范围内的所有日期
|
||
$date_range = [];
|
||
$current = clone $start_datetime;
|
||
while ($current <= $end_datetime) {
|
||
$date_range[$current->format('Y-m-d')] = [
|
||
'total_requests' => 0,
|
||
'time_saved' => 0
|
||
];
|
||
$current->modify('+1 day');
|
||
}
|
||
|
||
// 填充实际数据
|
||
foreach ($results as $row) {
|
||
$date_range[$row['date']] = [
|
||
'total_requests' => (int)$row['total_requests'],
|
||
'time_saved' => (float)$row['time_saved']
|
||
];
|
||
}
|
||
|
||
// 转换为数组格式
|
||
foreach ($date_range as $date => $stats) {
|
||
$dates[] = $date;
|
||
$requests[] = $stats['total_requests'];
|
||
$time_saved[] = $stats['time_saved'];
|
||
}
|
||
|
||
$response_data = [
|
||
'dates' => $dates,
|
||
'requests' => $requests,
|
||
'timeSaved' => $time_saved
|
||
];
|
||
|
||
header('Content-Type: application/json');
|
||
echo json_encode($response_data);
|
||
exit;
|
||
} catch (Exception $e) {
|
||
header('Content-Type: application/json');
|
||
http_response_code(500);
|
||
echo json_encode(["error" => $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// 如果没有特定操作，显示主页
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html>
|
||
<head>
|
||
<title>Task Report System</title>
|
||
<style>
|
||
body {
|
||
margin: 0;
|
||
padding: 0;
|
||
height: 100vh;
|
||
display: flex;
|
||
flex-direction: column;
|
||
justify-content: center;
|
||
align-items: center;
|
||
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
|
||
background-image: url('static/background.jpg');
|
||
background-size: cover;
|
||
background-position: center;
|
||
background-repeat: no-repeat;
|
||
position: relative;
|
||
}
|
||
|
||
body::before {
|
||
content: '';
|
||
position: absolute;
|
||
top: 0;
|
||
left: 0;
|
||
right: 0;
|
||
bottom: 0;
|
||
background: rgba(0, 0, 0, 0.2);
|
||
z-index: 1;
|
||
}
|
||
|
||
.content {
|
||
position: relative;
|
||
z-index: 2;
|
||
text-align: center;
|
||
}
|
||
|
||
h1 {
|
||
color: rgb(255, 166, 0);
|
||
font-size: 4em;
|
||
margin-bottom: 30px;
|
||
text-shadow: 2px 2px 4px rgba(114, 77, 10, 0.5);
|
||
animation: fadeIn 1.5s ease-out;
|
||
}
|
||
|
||
.view-btn {
|
||
background: transparent;
|
||
border: 2px solid white;
|
||
color: white;
|
||
padding: 15px 40px;
|
||
font-size: 1.2em;
|
||
cursor: pointer;
|
||
transition: all 0.3s ease;
|
||
border-radius: 30px;
|
||
text-decoration: none;
|
||
animation: slideUp 1s ease-out 0.5s both;
|
||
}
|
||
|
||
.view-btn:hover {
|
||
background: rgba(255, 255, 255, 0.1);
|
||
transform: translateY(-3px);
|
||
box-shadow: 0 5px 15px rgba(0, 0, 0, 0.3);
|
||
}
|
||
|
||
/* 淡入动画 */
|
||
@keyframes fadeIn {
|
||
from {
|
||
opacity: 0;
|
||
transform: translateY(-20px);
|
||
}
|
||
to {
|
||
opacity: 1;
|
||
transform: translateY(0);
|
||
}
|
||
}
|
||
|
||
@keyframes slideUp {
|
||
from {
|
||
opacity: 0;
|
||
transform: translateY(20px);
|
||
}
|
||
to {
|
||
opacity: 1;
|
||
transform: translateY(0);
|
||
}
|
||
}
|
||
|
||
@media (max-width: 768px) {
|
||
h1 {
|
||
font-size: 2.5em;
|
||
padding: 0 20px;
|
||
}
|
||
|
||
.view-btn {
|
||
padding: 12px 30px;
|
||
font-size: 1em;
|
||
}
|
||
}
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="content">
|
||
<h1>CGNICO Task Report System</h1>
|
||
<button class="view-btn" onclick="window.location.href='query_task_data.php'" style="margin-right: 15px;">
|
||
查看任务数据
|
||
</button>
|
||
<button class="view-btn" onclick="window.location.href='generate_report.php'" style="margin-left: 15px;">
|
||
查看报告
|
||
</button>
|
||
</div>
|
||
</body>
|
||
</html>
|