Compare commits
11 Commits
feature/a
...
d0e83f697b
| Author | SHA1 | Date | |
|---|---|---|---|
| d0e83f697b | |||
| ed327ad3f0 | |||
| f782dba0c9 | |||
| 0c4a67e769 | |||
| 694e005101 | |||
| 523e99a001 | |||
| 33dbf62665 | |||
| 1a9e28bd7e | |||
| d1d21948b6 | |||
| f32a45d24a | |||
| d61b8a62ff |
864
app/analytics/page.tsx
Normal file
864
app/analytics/page.tsx
Normal file
@@ -0,0 +1,864 @@
|
|||||||
|
"use client";
|
||||||
|
|
||||||
|
import { useState, useEffect } from 'react';
|
||||||
|
import { format, subDays } from 'date-fns';
|
||||||
|
import { DateRangePicker } from '@/app/components/ui/DateRangePicker';
|
||||||
|
import TimeSeriesChart from '@/app/components/charts/TimeSeriesChart';
|
||||||
|
import GeoAnalytics from '@/app/components/analytics/GeoAnalytics';
|
||||||
|
import DevicePieCharts from '@/app/components/charts/DevicePieCharts';
|
||||||
|
import UtmAnalytics from '@/app/components/analytics/UtmAnalytics';
|
||||||
|
import { EventsSummary, TimeSeriesData, GeoData, DeviceAnalytics as DeviceAnalyticsType } from '@/app/api/types';
|
||||||
|
import { TeamSelector } from '@/app/components/ui/TeamSelector';
|
||||||
|
import { ProjectSelector } from '@/app/components/ui/ProjectSelector';
|
||||||
|
import { TagSelector } from '@/app/components/ui/TagSelector';
|
||||||
|
import { useSearchParams } from 'next/navigation';
|
||||||
|
import { useShortUrlStore } from '@/app/utils/store';
|
||||||
|
|
||||||
|
// 事件类型定义
|
||||||
|
interface Event {
|
||||||
|
event_id?: string;
|
||||||
|
url_id: string;
|
||||||
|
url: string;
|
||||||
|
event_type: string;
|
||||||
|
visitor_id: string;
|
||||||
|
created_at: string;
|
||||||
|
event_time?: string;
|
||||||
|
referrer?: string;
|
||||||
|
browser?: string;
|
||||||
|
os?: string;
|
||||||
|
device_type?: string;
|
||||||
|
country?: string;
|
||||||
|
city?: string;
|
||||||
|
event_attributes?: string;
|
||||||
|
link_attributes?: string;
|
||||||
|
user_attributes?: string;
|
||||||
|
link_label?: string;
|
||||||
|
link_original_url?: string;
|
||||||
|
team_name?: string;
|
||||||
|
project_name?: string;
|
||||||
|
link_id?: string;
|
||||||
|
link_slug?: string;
|
||||||
|
link_tags?: string;
|
||||||
|
ip_address?: string;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 格式化日期函数
|
||||||
|
const formatDate = (dateString: string | undefined) => {
|
||||||
|
if (!dateString) return '';
|
||||||
|
try {
|
||||||
|
return format(new Date(dateString), 'yyyy-MM-dd HH:mm:ss');
|
||||||
|
} catch {
|
||||||
|
return dateString;
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
// 解析JSON字符串
|
||||||
|
const parseJsonSafely = (jsonString: string) => {
|
||||||
|
if (!jsonString) return null;
|
||||||
|
try {
|
||||||
|
return JSON.parse(jsonString);
|
||||||
|
} catch {
|
||||||
|
return null;
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
// 获取用户可读名称
|
||||||
|
const getUserDisplayName = (user: Record<string, unknown> | null) => {
|
||||||
|
if (!user) return '-';
|
||||||
|
if (typeof user.full_name === 'string') return user.full_name;
|
||||||
|
if (typeof user.name === 'string') return user.name;
|
||||||
|
if (typeof user.email === 'string') return user.email;
|
||||||
|
return '-';
|
||||||
|
};
|
||||||
|
|
||||||
|
// 提取链接和事件的重要信息
|
||||||
|
const extractEventInfo = (event: Event) => {
|
||||||
|
// 解析事件属性
|
||||||
|
const eventAttrs = parseJsonSafely(event.event_attributes || '{}');
|
||||||
|
|
||||||
|
// 解析链接属性
|
||||||
|
const linkAttrs = parseJsonSafely(event.link_attributes || '{}');
|
||||||
|
|
||||||
|
// 解析用户属性
|
||||||
|
const userAttrs = parseJsonSafely(event.user_attributes || '{}');
|
||||||
|
|
||||||
|
// 解析标签信息
|
||||||
|
let tags: string[] = [];
|
||||||
|
try {
|
||||||
|
if (event.link_tags) {
|
||||||
|
const parsedTags = JSON.parse(event.link_tags);
|
||||||
|
if (Array.isArray(parsedTags)) {
|
||||||
|
tags = parsedTags;
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch {
|
||||||
|
// 解析失败则保持空数组
|
||||||
|
}
|
||||||
|
|
||||||
|
return {
|
||||||
|
eventTime: event.created_at || event.event_time,
|
||||||
|
linkName: event.link_label || linkAttrs?.name || eventAttrs?.link_name || event.link_slug || '-',
|
||||||
|
originalUrl: event.link_original_url || eventAttrs?.origin_url || '-',
|
||||||
|
fullUrl: eventAttrs?.full_url || '-',
|
||||||
|
eventType: event.event_type || '-',
|
||||||
|
visitorId: event.visitor_id?.substring(0, 8) || '-',
|
||||||
|
referrer: eventAttrs?.referrer || '-',
|
||||||
|
ipAddress: event.ip_address || '-',
|
||||||
|
location: event.country ? (event.city ? `${event.city}, ${event.country}` : event.country) : '-',
|
||||||
|
device: event.device_type || '-',
|
||||||
|
browser: event.browser || '-',
|
||||||
|
os: event.os || '-',
|
||||||
|
userInfo: getUserDisplayName(userAttrs),
|
||||||
|
teamName: event.team_name || '-',
|
||||||
|
projectName: event.project_name || '-',
|
||||||
|
tags: tags
|
||||||
|
};
|
||||||
|
};
|
||||||
|
|
||||||
|
export default function AnalyticsPage() {
|
||||||
|
// 从 URL 获取查询参数
|
||||||
|
const searchParams = useSearchParams();
|
||||||
|
const shorturlParam = searchParams.get('shorturl');
|
||||||
|
|
||||||
|
// 使用 Zustand store
|
||||||
|
const { selectedShortUrl } = useShortUrlStore();
|
||||||
|
|
||||||
|
// 存储 shorturl 参数
|
||||||
|
const [selectedShortUrlString, setSelectedShortUrlString] = useState<string | null>(null);
|
||||||
|
|
||||||
|
// 当 URL 参数变化时更新状态
|
||||||
|
useEffect(() => {
|
||||||
|
if (shorturlParam) {
|
||||||
|
setSelectedShortUrlString(shorturlParam);
|
||||||
|
console.log('Selected shorturl from URL:', shorturlParam);
|
||||||
|
|
||||||
|
// 已经通过 Zustand store 传递了完整数据
|
||||||
|
if (selectedShortUrl) {
|
||||||
|
console.log('Complete shortUrl data from store:', selectedShortUrl);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
}, [shorturlParam, selectedShortUrl]);
|
||||||
|
|
||||||
|
// 默认日期范围为最近7天
|
||||||
|
const today = new Date();
|
||||||
|
const [dateRange, setDateRange] = useState({
|
||||||
|
from: subDays(today, 7), // 7天前
|
||||||
|
to: today // 今天
|
||||||
|
});
|
||||||
|
|
||||||
|
// 添加团队选择状态 - 使用数组支持多选
|
||||||
|
const [selectedTeamIds, setSelectedTeamIds] = useState<string[]>([]);
|
||||||
|
// 添加项目选择状态 - 使用数组支持多选
|
||||||
|
const [selectedProjectIds, setSelectedProjectIds] = useState<string[]>([]);
|
||||||
|
// 添加标签名称状态 - 用于在UI中显示和API请求
|
||||||
|
const [selectedTagNames, setSelectedTagNames] = useState<string[]>([]);
|
||||||
|
// 添加分页状态
|
||||||
|
const [currentPage, setCurrentPage] = useState<number>(1);
|
||||||
|
const [pageSize, setPageSize] = useState<number>(10);
|
||||||
|
const [totalEvents, setTotalEvents] = useState<number>(0);
|
||||||
|
|
||||||
|
const [loading, setLoading] = useState(true);
|
||||||
|
const [error, setError] = useState<string | null>(null);
|
||||||
|
const [summary, setSummary] = useState<EventsSummary | null>(null);
|
||||||
|
const [timeSeriesData, setTimeSeriesData] = useState<TimeSeriesData[]>([]);
|
||||||
|
const [geoData, setGeoData] = useState<GeoData[]>([]);
|
||||||
|
const [deviceData, setDeviceData] = useState<DeviceAnalyticsType | null>(null);
|
||||||
|
const [events, setEvents] = useState<Event[]>([]);
|
||||||
|
|
||||||
|
useEffect(() => {
|
||||||
|
const fetchData = async () => {
|
||||||
|
setLoading(true);
|
||||||
|
setError(null);
|
||||||
|
|
||||||
|
try {
|
||||||
|
const startTime = format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'");
|
||||||
|
const endTime = format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'");
|
||||||
|
|
||||||
|
// 构建基础URL和查询参数
|
||||||
|
const baseUrl = '/api/events';
|
||||||
|
const params = new URLSearchParams({
|
||||||
|
startTime,
|
||||||
|
endTime,
|
||||||
|
page: currentPage.toString(),
|
||||||
|
pageSize: pageSize.toString()
|
||||||
|
});
|
||||||
|
|
||||||
|
// 添加团队ID参数 - 支持多个团队
|
||||||
|
if (selectedTeamIds.length > 0) {
|
||||||
|
selectedTeamIds.forEach(teamId => {
|
||||||
|
params.append('teamId', teamId);
|
||||||
|
});
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加项目ID参数 - 支持多个项目
|
||||||
|
if (selectedProjectIds.length > 0) {
|
||||||
|
selectedProjectIds.forEach(projectId => {
|
||||||
|
params.append('projectId', projectId);
|
||||||
|
});
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加标签名称参数 - 支持多个标签
|
||||||
|
if (selectedTagNames.length > 0) {
|
||||||
|
selectedTagNames.forEach(tagName => {
|
||||||
|
params.append('tagName', tagName);
|
||||||
|
});
|
||||||
|
}
|
||||||
|
|
||||||
|
// 并行获取所有数据
|
||||||
|
const [summaryRes, timeSeriesRes, geoRes, deviceRes, eventsRes] = await Promise.all([
|
||||||
|
fetch(`${baseUrl}/summary?${params.toString()}`),
|
||||||
|
fetch(`${baseUrl}/time-series?${params.toString()}`),
|
||||||
|
fetch(`${baseUrl}/geo?${params.toString()}`),
|
||||||
|
fetch(`${baseUrl}/devices?${params.toString()}`),
|
||||||
|
fetch(`${baseUrl}?${params.toString()}`)
|
||||||
|
]);
|
||||||
|
|
||||||
|
const [summaryData, timeSeriesData, geoData, deviceData, eventsData] = await Promise.all([
|
||||||
|
summaryRes.json(),
|
||||||
|
timeSeriesRes.json(),
|
||||||
|
geoRes.json(),
|
||||||
|
deviceRes.json(),
|
||||||
|
eventsRes.json()
|
||||||
|
]);
|
||||||
|
|
||||||
|
if (!summaryRes.ok) throw new Error(summaryData.error || 'Failed to fetch summary data');
|
||||||
|
if (!timeSeriesRes.ok) throw new Error(timeSeriesData.error || 'Failed to fetch time series data');
|
||||||
|
if (!geoRes.ok) throw new Error(geoData.error || 'Failed to fetch geo data');
|
||||||
|
if (!deviceRes.ok) throw new Error(deviceData.error || 'Failed to fetch device data');
|
||||||
|
if (!eventsRes.ok) throw new Error(eventsData.error || 'Failed to fetch events data');
|
||||||
|
|
||||||
|
setSummary(summaryData.data);
|
||||||
|
setTimeSeriesData(timeSeriesData.data);
|
||||||
|
setGeoData(geoData.data);
|
||||||
|
setDeviceData(deviceData.data);
|
||||||
|
setEvents(eventsData.data || []);
|
||||||
|
|
||||||
|
// 设置总事件数量用于分页
|
||||||
|
if (eventsData.meta) {
|
||||||
|
// 确保将total转换为数字，无论它是字符串还是数字
|
||||||
|
const totalCount = parseInt(String(eventsData.meta.total), 10);
|
||||||
|
if (!isNaN(totalCount)) {
|
||||||
|
setTotalEvents(totalCount);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (err) {
|
||||||
|
setError(err instanceof Error ? err.message : 'An error occurred while fetching data');
|
||||||
|
} finally {
|
||||||
|
setLoading(false);
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
fetchData();
|
||||||
|
}, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagNames, currentPage, pageSize]);
|
||||||
|
|
||||||
|
if (loading) {
|
||||||
|
return (
|
||||||
|
<div className="flex items-center justify-center min-h-screen">
|
||||||
|
<div className="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-blue-500" />
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (error) {
|
||||||
|
return (
|
||||||
|
<div className="flex items-center justify-center min-h-screen">
|
||||||
|
<div className="text-red-500">{error}</div>
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
|
|
||||||
|
return (
|
||||||
|
<div className="container mx-auto px-4 py-8">
|
||||||
|
<div className="flex justify-between items-center mb-8">
|
||||||
|
<h1 className="text-2xl font-bold text-gray-900">Analytics Dashboard</h1>
|
||||||
|
<div className="flex flex-col gap-4 md:flex-row md:items-center">
|
||||||
|
{/* 如果有选定的 shorturl，可以显示一个提示，显示更多详细信息 */}
|
||||||
|
{selectedShortUrl && (
|
||||||
|
<div className="bg-blue-100 text-blue-800 px-3 py-2 rounded-md text-sm flex flex-col">
|
||||||
|
<div className="flex items-center">
|
||||||
|
<span className="font-medium">{selectedShortUrl.title || 'Untitled'}</span>
|
||||||
|
<span className="mx-2">-</span>
|
||||||
|
<span>{selectedShortUrl.shortUrl}</span>
|
||||||
|
</div>
|
||||||
|
{selectedShortUrl.tags && selectedShortUrl.tags.length > 0 && (
|
||||||
|
<div className="flex flex-wrap gap-1 mt-1">
|
||||||
|
{selectedShortUrl.tags.map((tag, index) => (
|
||||||
|
<span key={index} className="bg-blue-50 text-blue-700 text-xs px-1.5 py-0.5 rounded">
|
||||||
|
{tag}
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 如果只有 URL 参数但没有完整数据，则显示简单提示 */}
|
||||||
|
{selectedShortUrlString && !selectedShortUrl && (
|
||||||
|
<div className="bg-blue-100 text-blue-800 px-3 py-1 rounded-md text-sm flex items-center">
|
||||||
|
<span>Filtered by Short URL:</span>
|
||||||
|
<span className="ml-2 font-medium">{selectedShortUrlString}</span>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
<TeamSelector
|
||||||
|
value={selectedTeamIds}
|
||||||
|
onChange={(value) => {
|
||||||
|
const newTeamIds = Array.isArray(value) ? value : [value];
|
||||||
|
|
||||||
|
// Check if team selection has changed
|
||||||
|
if (JSON.stringify(newTeamIds) !== JSON.stringify(selectedTeamIds)) {
|
||||||
|
// Clear project selection when team changes
|
||||||
|
setSelectedProjectIds([]);
|
||||||
|
|
||||||
|
// Update team selection
|
||||||
|
setSelectedTeamIds(newTeamIds);
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
className="w-[250px]"
|
||||||
|
multiple={true}
|
||||||
|
/>
|
||||||
|
<ProjectSelector
|
||||||
|
value={selectedProjectIds}
|
||||||
|
onChange={(value) => setSelectedProjectIds(Array.isArray(value) ? value : [value])}
|
||||||
|
className="w-[250px]"
|
||||||
|
multiple={true}
|
||||||
|
teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
|
||||||
|
/>
|
||||||
|
<TagSelector
|
||||||
|
value={selectedTagNames}
|
||||||
|
onChange={(value) => {
|
||||||
|
// TagSelector返回的是标签名称
|
||||||
|
if (Array.isArray(value)) {
|
||||||
|
setSelectedTagNames(value);
|
||||||
|
} else {
|
||||||
|
setSelectedTagNames(value ? [value] : []);
|
||||||
|
}
|
||||||
|
// 我们需要将标签名称映射回ID，但由于TagSelector内部已经做了处理
|
||||||
|
// 这里不需要额外的映射代码，selectedTagNames存储名称即可
|
||||||
|
}}
|
||||||
|
className="w-[250px]"
|
||||||
|
multiple={true}
|
||||||
|
teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
|
||||||
|
/>
|
||||||
|
<DateRangePicker
|
||||||
|
value={dateRange}
|
||||||
|
onChange={setDateRange}
|
||||||
|
/>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{/* 显示团队选择信息 */}
|
||||||
|
{selectedTeamIds.length > 0 && (
|
||||||
|
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
||||||
|
<span className="text-blue-700 font-medium mr-2">
|
||||||
|
{selectedTeamIds.length === 1 ? 'Team filter:' : 'Teams filter:'}
|
||||||
|
</span>
|
||||||
|
<div className="flex flex-wrap gap-2">
|
||||||
|
{selectedTeamIds.map(teamId => (
|
||||||
|
<span key={teamId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
||||||
|
{teamId}
|
||||||
|
<button
|
||||||
|
onClick={() => setSelectedTeamIds(selectedTeamIds.filter(id => id !== teamId))}
|
||||||
|
className="ml-1 text-blue-600 hover:text-blue-800"
|
||||||
|
>
|
||||||
|
×
|
||||||
|
</button>
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
{selectedTeamIds.length > 0 && (
|
||||||
|
<button
|
||||||
|
onClick={() => setSelectedTeamIds([])}
|
||||||
|
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
||||||
|
>
|
||||||
|
Clear all
|
||||||
|
</button>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 显示项目选择信息 */}
|
||||||
|
{selectedProjectIds.length > 0 && (
|
||||||
|
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
||||||
|
<span className="text-blue-700 font-medium mr-2">
|
||||||
|
{selectedProjectIds.length === 1 ? 'Project filter:' : 'Projects filter:'}
|
||||||
|
</span>
|
||||||
|
<div className="flex flex-wrap gap-2">
|
||||||
|
{selectedProjectIds.map(projectId => (
|
||||||
|
<span key={projectId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
||||||
|
{projectId}
|
||||||
|
<button
|
||||||
|
onClick={() => setSelectedProjectIds(selectedProjectIds.filter(id => id !== projectId))}
|
||||||
|
className="ml-1 text-blue-600 hover:text-blue-800"
|
||||||
|
>
|
||||||
|
×
|
||||||
|
</button>
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
{selectedProjectIds.length > 0 && (
|
||||||
|
<button
|
||||||
|
onClick={() => setSelectedProjectIds([])}
|
||||||
|
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
||||||
|
>
|
||||||
|
Clear all
|
||||||
|
</button>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 显示标签选择信息 */}
|
||||||
|
{selectedTagNames.length > 0 && (
|
||||||
|
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
||||||
|
<span className="text-blue-700 font-medium mr-2">
|
||||||
|
{selectedTagNames.length === 1 ? 'Tag filter:' : 'Tags filter:'}
|
||||||
|
</span>
|
||||||
|
<div className="flex flex-wrap gap-2">
|
||||||
|
{selectedTagNames.map(tagName => (
|
||||||
|
<span key={tagName} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
||||||
|
{tagName}
|
||||||
|
<button
|
||||||
|
onClick={() => {
|
||||||
|
// 移除对应的标签名称
|
||||||
|
setSelectedTagNames(selectedTagNames.filter(name => name !== tagName));
|
||||||
|
}}
|
||||||
|
className="ml-1 text-blue-600 hover:text-blue-800"
|
||||||
|
>
|
||||||
|
×
|
||||||
|
</button>
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
{selectedTagNames.length > 0 && (
|
||||||
|
<button
|
||||||
|
onClick={() => setSelectedTagNames([])}
|
||||||
|
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
||||||
|
>
|
||||||
|
Clear all
|
||||||
|
</button>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 仪表板内容 - 现在放在事件列表之后 */}
|
||||||
|
<>
|
||||||
|
{summary && (
|
||||||
|
<div className="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
|
||||||
|
<div className="bg-white rounded-lg shadow p-6">
|
||||||
|
<h3 className="text-sm font-medium text-gray-500">Total Events</h3>
|
||||||
|
<p className="text-2xl font-semibold text-gray-900">
|
||||||
|
{typeof summary.totalEvents === 'number' ? summary.totalEvents.toLocaleString() : summary.totalEvents}
|
||||||
|
</p>
|
||||||
|
</div>
|
||||||
|
<div className="bg-white rounded-lg shadow p-6">
|
||||||
|
<h3 className="text-sm font-medium text-gray-500">Unique Visitors</h3>
|
||||||
|
<p className="text-2xl font-semibold text-gray-900">
|
||||||
|
{typeof summary.uniqueVisitors === 'number' ? summary.uniqueVisitors.toLocaleString() : summary.uniqueVisitors}
|
||||||
|
</p>
|
||||||
|
</div>
|
||||||
|
<div className="bg-white rounded-lg shadow p-6">
|
||||||
|
<h3 className="text-sm font-medium text-gray-500">Total Conversions</h3>
|
||||||
|
<p className="text-2xl font-semibold text-gray-900">
|
||||||
|
{typeof summary.totalConversions === 'number' ? summary.totalConversions.toLocaleString() : summary.totalConversions}
|
||||||
|
</p>
|
||||||
|
</div>
|
||||||
|
<div className="bg-white rounded-lg shadow p-6">
|
||||||
|
<h3 className="text-sm font-medium text-gray-500">Avg. Time Spent</h3>
|
||||||
|
<p className="text-2xl font-semibold text-gray-900">
|
||||||
|
{summary.averageTimeSpent?.toFixed(1) || '0'}s
|
||||||
|
</p>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 时间序列图表 */}
|
||||||
|
{timeSeriesData && timeSeriesData.length > 0 && (
|
||||||
|
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-6">Traffic Over Time</h2>
|
||||||
|
<div className="h-80">
|
||||||
|
<TimeSeriesChart data={timeSeriesData} />
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 设备分析图表 */}
|
||||||
|
{deviceData && (
|
||||||
|
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-6">Device Analytics</h2>
|
||||||
|
<DevicePieCharts data={deviceData} />
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 地理分析 */}
|
||||||
|
{geoData && geoData.length > 0 && (
|
||||||
|
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-6">Geographic Distribution</h2>
|
||||||
|
<GeoAnalytics data={geoData} />
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* UTM 参数分析 */}
|
||||||
|
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-6">UTM Parameters Analysis</h2>
|
||||||
|
<UtmAnalytics
|
||||||
|
startTime={format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'")}
|
||||||
|
endTime={format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'")}
|
||||||
|
teamIds={selectedTeamIds}
|
||||||
|
projectIds={selectedProjectIds}
|
||||||
|
tagIds={selectedTagNames}
|
||||||
|
/>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
<div className="bg-white rounded-lg shadow overflow-hidden mb-8">
|
||||||
|
<div className="p-6 border-b border-gray-200">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-4">Recent Events</h2>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
<div className="overflow-x-auto">
|
||||||
|
<table className="min-w-full divide-y divide-gray-200">
|
||||||
|
<thead className="bg-gray-50">
|
||||||
|
<tr>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Time
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Link Name
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Original URL
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Full URL
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Event Type
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Tags
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
User
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Team/Project
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
IP/Location
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Device Info
|
||||||
|
</th>
|
||||||
|
</tr>
|
||||||
|
</thead>
|
||||||
|
<tbody className="bg-white divide-y divide-gray-200">
|
||||||
|
{events.map((event, index) => {
|
||||||
|
const info = extractEventInfo(event);
|
||||||
|
return (
|
||||||
|
<tr key={event.event_id || index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
{formatDate(info.eventTime)}
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
<span className="font-medium">{info.linkName}</span>
|
||||||
|
<div className="text-xs text-gray-500 mt-1 truncate max-w-xs">
|
||||||
|
ID: {event.link_id?.substring(0, 8) || '-'}
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
|
||||||
|
<a href={info.originalUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
|
||||||
|
{info.originalUrl}
|
||||||
|
</a>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
|
||||||
|
<a href={info.fullUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
|
||||||
|
{info.fullUrl}
|
||||||
|
</a>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm">
|
||||||
|
<span className={`px-2 inline-flex text-xs leading-5 font-semibold rounded-full ${
|
||||||
|
info.eventType === 'click'
|
||||||
|
? 'bg-green-100 text-green-800'
|
||||||
|
: 'bg-blue-100 text-blue-800'
|
||||||
|
}`}>
|
||||||
|
{info.eventType}
|
||||||
|
</span>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
<div className="flex flex-wrap gap-1">
|
||||||
|
{info.tags && info.tags.length > 0 ? (
|
||||||
|
info.tags.map((tag, idx) => (
|
||||||
|
<span
|
||||||
|
key={idx}
|
||||||
|
className="bg-gray-100 text-gray-800 text-xs px-2 py-0.5 rounded"
|
||||||
|
>
|
||||||
|
{tag}
|
||||||
|
</span>
|
||||||
|
))
|
||||||
|
) : (
|
||||||
|
<span className="text-gray-400">-</span>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
<div className="font-medium">{info.userInfo}</div>
|
||||||
|
<div className="text-xs text-gray-400 mt-1">{info.visitorId}...</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
<div className="font-medium">{info.teamName}</div>
|
||||||
|
<div className="text-xs text-gray-400 mt-1">{info.projectName}</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
<div className="flex flex-col">
|
||||||
|
<span className="text-xs inline-flex items-center mb-1">
|
||||||
|
<span className="font-medium">IP:</span>
|
||||||
|
<span className="ml-1">{info.ipAddress}</span>
|
||||||
|
</span>
|
||||||
|
<span className="text-xs inline-flex items-center">
|
||||||
|
<span className="font-medium">Location:</span>
|
||||||
|
<span className="ml-1">{info.location}</span>
|
||||||
|
</span>
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
||||||
|
<div className="flex flex-col">
|
||||||
|
<span className="text-xs inline-flex items-center mb-1">
|
||||||
|
<span className="font-medium">Device:</span>
|
||||||
|
<span className="ml-1">{info.device}</span>
|
||||||
|
</span>
|
||||||
|
<span className="text-xs inline-flex items-center mb-1">
|
||||||
|
<span className="font-medium">Browser:</span>
|
||||||
|
<span className="ml-1">{info.browser}</span>
|
||||||
|
</span>
|
||||||
|
<span className="text-xs inline-flex items-center">
|
||||||
|
<span className="font-medium">OS:</span>
|
||||||
|
<span className="ml-1">{info.os}</span>
|
||||||
|
</span>
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
</tr>
|
||||||
|
);
|
||||||
|
})}
|
||||||
|
</tbody>
|
||||||
|
</table>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{/* 表格为空状态 */}
|
||||||
|
{!loading && events.length === 0 && (
|
||||||
|
<div className="flex justify-center items-center p-8 text-gray-500">
|
||||||
|
No events found
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* 分页控件 */}
|
||||||
|
{!loading && events.length > 0 && (
|
||||||
|
<div className="px-6 py-4 flex items-center justify-between border-t border-gray-200">
|
||||||
|
<div className="flex-1 flex justify-between sm:hidden">
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
|
||||||
|
disabled={currentPage === 1}
|
||||||
|
className={`relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
|
||||||
|
currentPage === 1
|
||||||
|
? 'text-gray-300 bg-gray-50'
|
||||||
|
: 'text-gray-700 bg-white hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
Previous
|
||||||
|
</button>
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
|
||||||
|
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
||||||
|
className={`ml-3 relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
|
||||||
|
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
||||||
|
? 'text-gray-300 cursor-not-allowed'
|
||||||
|
: 'text-gray-700 bg-white hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
Next
|
||||||
|
</button>
|
||||||
|
</div>
|
||||||
|
<div className="hidden sm:flex-1 sm:flex sm:items-center sm:justify-between">
|
||||||
|
<div>
|
||||||
|
<p className="text-sm text-gray-700">
|
||||||
|
Showing <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + 1 : 0}</span> to <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + events.length : 0}</span> of{' '}
|
||||||
|
<span className="font-medium">{totalEvents}</span> results
|
||||||
|
</p>
|
||||||
|
</div>
|
||||||
|
<div className="flex items-center">
|
||||||
|
<div className="mr-4">
|
||||||
|
<select
|
||||||
|
className="px-3 py-1 border border-gray-300 rounded-md text-sm"
|
||||||
|
value={pageSize}
|
||||||
|
onChange={(e) => {
|
||||||
|
setPageSize(Number(e.target.value));
|
||||||
|
setCurrentPage(1); // 重置到第一页
|
||||||
|
}}
|
||||||
|
>
|
||||||
|
<option value="5">5 / page</option>
|
||||||
|
<option value="10">10 / page</option>
|
||||||
|
<option value="20">20 / page</option>
|
||||||
|
<option value="50">50 / page</option>
|
||||||
|
</select>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{/* 添加直接跳转到指定页的输入框 */}
|
||||||
|
<div className="mr-4 flex items-center">
|
||||||
|
<span className="text-sm text-gray-700 mr-2">Go to:</span>
|
||||||
|
<input
|
||||||
|
type="number"
|
||||||
|
min="1"
|
||||||
|
max={Math.max(1, Math.ceil(totalEvents / pageSize))}
|
||||||
|
value={currentPage}
|
||||||
|
onChange={(e) => {
|
||||||
|
const page = parseInt(e.target.value);
|
||||||
|
if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
|
||||||
|
setCurrentPage(page);
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
onKeyDown={(e) => {
|
||||||
|
if (e.key === 'Enter') {
|
||||||
|
const input = e.target as HTMLInputElement;
|
||||||
|
const page = parseInt(input.value);
|
||||||
|
if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
|
||||||
|
setCurrentPage(page);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
className="w-16 px-3 py-1 border border-gray-300 rounded-md text-sm"
|
||||||
|
/>
|
||||||
|
<span className="text-sm text-gray-700 ml-2">
|
||||||
|
of {Math.max(1, Math.ceil(totalEvents / pageSize))}
|
||||||
|
</span>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
<nav className="relative z-0 inline-flex rounded-md shadow-sm -space-x-px" aria-label="Pagination">
|
||||||
|
{/* 首页按钮 */}
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(1)}
|
||||||
|
disabled={currentPage === 1}
|
||||||
|
className={`relative inline-flex items-center px-2 py-2 rounded-l-md border border-gray-300 bg-white text-sm font-medium ${
|
||||||
|
currentPage === 1
|
||||||
|
? 'text-gray-300 cursor-not-allowed'
|
||||||
|
: 'text-gray-500 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
<span className="sr-only">First</span>
|
||||||
|
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
|
||||||
|
<path fillRule="evenodd" d="M15.707 15.707a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 111.414 1.414L11.414 10l4.293 4.293a1 1 0 010 1.414zm-6 0a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 011.414 1.414L5.414 10l4.293 4.293a1 1 0 010 1.414z" clipRule="evenodd" />
|
||||||
|
</svg>
|
||||||
|
</button>
|
||||||
|
|
||||||
|
{/* 上一页按钮 */}
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
|
||||||
|
disabled={currentPage === 1}
|
||||||
|
className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
|
||||||
|
currentPage === 1
|
||||||
|
? 'text-gray-300 cursor-not-allowed'
|
||||||
|
: 'text-gray-500 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
<span className="sr-only">Previous</span>
|
||||||
|
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
|
||||||
|
<path fillRule="evenodd" d="M12.707 5.293a1 1 0 010 1.414L9.414 10l3.293 3.293a1 1 0 01-1.414 1.414l-4-4a1 1 0 010-1.414l4-4a1 1 0 011.414 0z" clipRule="evenodd" />
|
||||||
|
</svg>
|
||||||
|
</button>
|
||||||
|
|
||||||
|
{/* 页码按钮 */}
|
||||||
|
{(() => {
|
||||||
|
const totalPages = Math.max(1, Math.ceil(totalEvents / pageSize));
|
||||||
|
const pageNumbers = [];
|
||||||
|
|
||||||
|
// 如果总页数小于等于7，显示所有页码
|
||||||
|
if (totalPages <= 7) {
|
||||||
|
for (let i = 1; i <= totalPages; i++) {
|
||||||
|
pageNumbers.push(i);
|
||||||
|
}
|
||||||
|
} else {
|
||||||
|
// 总是显示首页
|
||||||
|
pageNumbers.push(1);
|
||||||
|
|
||||||
|
// 根据当前页显示中间页码
|
||||||
|
if (currentPage <= 3) {
|
||||||
|
// 当前页靠近开始
|
||||||
|
pageNumbers.push(2, 3, 4);
|
||||||
|
pageNumbers.push('ellipsis1');
|
||||||
|
} else if (currentPage >= totalPages - 2) {
|
||||||
|
// 当前页靠近结束
|
||||||
|
pageNumbers.push('ellipsis1');
|
||||||
|
pageNumbers.push(totalPages - 3, totalPages - 2, totalPages - 1);
|
||||||
|
} else {
|
||||||
|
// 当前页在中间
|
||||||
|
pageNumbers.push('ellipsis1');
|
||||||
|
pageNumbers.push(currentPage - 1, currentPage, currentPage + 1);
|
||||||
|
pageNumbers.push('ellipsis2');
|
||||||
|
}
|
||||||
|
|
||||||
|
// 总是显示尾页
|
||||||
|
pageNumbers.push(totalPages);
|
||||||
|
}
|
||||||
|
|
||||||
|
return pageNumbers.map((pageNum, idx) => {
|
||||||
|
if (pageNum === 'ellipsis1' || pageNum === 'ellipsis2') {
|
||||||
|
return (
|
||||||
|
<div key={`ellipsis-${idx}`} className="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700">
|
||||||
|
...
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
|
|
||||||
|
return (
|
||||||
|
<button
|
||||||
|
key={pageNum}
|
||||||
|
onClick={() => setCurrentPage(Number(pageNum))}
|
||||||
|
className={`relative inline-flex items-center px-4 py-2 border text-sm font-medium ${
|
||||||
|
currentPage === pageNum
|
||||||
|
? 'z-10 bg-blue-50 border-blue-500 text-blue-600'
|
||||||
|
: 'bg-white border-gray-300 text-gray-500 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
{pageNum}
|
||||||
|
</button>
|
||||||
|
);
|
||||||
|
});
|
||||||
|
})()}
|
||||||
|
|
||||||
|
{/* 下一页按钮 */}
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
|
||||||
|
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
||||||
|
className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
|
||||||
|
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
||||||
|
? 'text-gray-300 cursor-not-allowed'
|
||||||
|
: 'text-gray-500 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
<span className="sr-only">Next</span>
|
||||||
|
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
|
||||||
|
<path fillRule="evenodd" d="M7.293 14.707a1 1 0 010-1.414L10.586 10 7.293 6.707a1 1 0 011.414-1.414l4 4a1 1 0 010 1.414l-4 4a1 1 0 01-1.414 0z" clipRule="evenodd" />
|
||||||
|
</svg>
|
||||||
|
</button>
|
||||||
|
|
||||||
|
{/* 尾页按钮 */}
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(Math.max(1, Math.ceil(totalEvents / pageSize)))}
|
||||||
|
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
||||||
|
className={`relative inline-flex items-center px-2 py-2 rounded-r-md border border-gray-300 bg-white text-sm font-medium ${
|
||||||
|
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
||||||
|
? 'text-gray-300 cursor-not-allowed'
|
||||||
|
: 'text-gray-500 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
<span className="sr-only">Last</span>
|
||||||
|
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
|
||||||
|
<path fillRule="evenodd" d="M4.293 15.707a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L8.586 10 4.293 14.293a1 1 0 000 1.414zm6 0a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L14.586 10l-4.293 4.293a1 1 0 000 1.414z" clipRule="evenodd" />
|
||||||
|
</svg>
|
||||||
|
</button>
|
||||||
|
</nav>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</>
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
@@ -81,6 +81,8 @@ export async function POST(req: NextRequest) {
|
|||||||
utm_source: eventData.utm_source || '',
|
utm_source: eventData.utm_source || '',
|
||||||
utm_medium: eventData.utm_medium || '',
|
utm_medium: eventData.utm_medium || '',
|
||||||
utm_campaign: eventData.utm_campaign || '',
|
utm_campaign: eventData.utm_campaign || '',
|
||||||
|
utm_term: eventData.utm_term || '',
|
||||||
|
utm_content: eventData.utm_content || '',
|
||||||
|
|
||||||
// Interaction information
|
// Interaction information
|
||||||
time_spent_sec: eventData.time_spent_sec || 0,
|
time_spent_sec: eventData.time_spent_sec || 0,
|
||||||
|
|||||||
157
app/api/events/utm/route.ts
Normal file
157
app/api/events/utm/route.ts
Normal file
@@ -0,0 +1,157 @@
|
|||||||
|
import { NextRequest, NextResponse } from 'next/server';
|
||||||
|
import clickhouse from '@/lib/clickhouse';
|
||||||
|
import type { ApiResponse } from '@/lib/types';
|
||||||
|
|
||||||
|
interface UtmData {
|
||||||
|
utm_value: string;
|
||||||
|
clicks: number;
|
||||||
|
visitors: number;
|
||||||
|
avg_time_spent: number;
|
||||||
|
bounces: number;
|
||||||
|
conversions: number;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 格式化日期时间字符串为ClickHouse支持的格式
|
||||||
|
const formatDateTime = (dateStr: string): string => {
|
||||||
|
return dateStr.replace('T', ' ').replace('Z', '');
|
||||||
|
};
|
||||||
|
|
||||||
|
export async function GET(request: NextRequest) {
|
||||||
|
try {
|
||||||
|
const searchParams = request.nextUrl.searchParams;
|
||||||
|
|
||||||
|
// 获取过滤参数
|
||||||
|
const startTime = searchParams.get('startTime');
|
||||||
|
const endTime = searchParams.get('endTime');
|
||||||
|
const linkId = searchParams.get('linkId');
|
||||||
|
|
||||||
|
// 获取团队、项目和标签筛选参数
|
||||||
|
const teamIds = searchParams.getAll('teamId');
|
||||||
|
const projectIds = searchParams.getAll('projectId');
|
||||||
|
const tagIds = searchParams.getAll('tagId');
|
||||||
|
const tagNames = searchParams.getAll('tagName');
|
||||||
|
|
||||||
|
// 获取UTM类型参数
|
||||||
|
const utmType = searchParams.get('utmType') || 'source';
|
||||||
|
|
||||||
|
// 构建WHERE子句
|
||||||
|
let whereClause = '';
|
||||||
|
const conditions = [];
|
||||||
|
|
||||||
|
if (startTime) {
|
||||||
|
conditions.push(`event_time >= toDateTime('${formatDateTime(startTime)}')`);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (endTime) {
|
||||||
|
conditions.push(`event_time <= toDateTime('${formatDateTime(endTime)}')`);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (linkId) {
|
||||||
|
conditions.push(`link_id = '${linkId}'`);
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加团队筛选
|
||||||
|
if (teamIds && teamIds.length > 0) {
|
||||||
|
// 如果只有一个团队ID
|
||||||
|
if (teamIds.length === 1) {
|
||||||
|
conditions.push(`team_id = '${teamIds[0]}'`);
|
||||||
|
} else {
|
||||||
|
// 多个团队ID
|
||||||
|
conditions.push(`team_id IN ('${teamIds.join("','")}')`);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加项目筛选
|
||||||
|
if (projectIds && projectIds.length > 0) {
|
||||||
|
// 如果只有一个项目ID
|
||||||
|
if (projectIds.length === 1) {
|
||||||
|
conditions.push(`project_id = '${projectIds[0]}'`);
|
||||||
|
} else {
|
||||||
|
// 多个项目ID
|
||||||
|
conditions.push(`project_id IN ('${projectIds.join("','")}')`);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加标签筛选
|
||||||
|
if ((tagIds && tagIds.length > 0) || (tagNames && tagNames.length > 0)) {
|
||||||
|
// 优先使用tagNames，如果有的话
|
||||||
|
const tagsToUse = tagNames.length > 0 ? tagNames : tagIds;
|
||||||
|
|
||||||
|
// 使用与buildFilter函数相同的处理方式
|
||||||
|
const tagConditions = tagsToUse.map(tag =>
|
||||||
|
`link_tags LIKE '%${tag}%'`
|
||||||
|
);
|
||||||
|
conditions.push(`(${tagConditions.join(' OR ')})`);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (conditions.length > 0) {
|
||||||
|
whereClause = `WHERE ${conditions.join(' AND ')}`;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 确定要分组的UTM字段
|
||||||
|
let utmField;
|
||||||
|
switch (utmType) {
|
||||||
|
case 'source':
|
||||||
|
utmField = 'utm_source';
|
||||||
|
break;
|
||||||
|
case 'medium':
|
||||||
|
utmField = 'utm_medium';
|
||||||
|
break;
|
||||||
|
case 'campaign':
|
||||||
|
utmField = 'utm_campaign';
|
||||||
|
break;
|
||||||
|
case 'term':
|
||||||
|
utmField = 'utm_term';
|
||||||
|
break;
|
||||||
|
case 'content':
|
||||||
|
utmField = 'utm_content';
|
||||||
|
break;
|
||||||
|
default:
|
||||||
|
utmField = 'utm_source';
|
||||||
|
}
|
||||||
|
|
||||||
|
// 构建SQL查询
|
||||||
|
const query = `
|
||||||
|
SELECT
|
||||||
|
${utmField} AS utm_value,
|
||||||
|
COUNT(*) AS clicks,
|
||||||
|
uniqExact(visitor_id) AS visitors,
|
||||||
|
round(AVG(time_spent_sec), 2) AS avg_time_spent,
|
||||||
|
countIf(is_bounce = 1) AS bounces,
|
||||||
|
countIf(conversion_type IN ('visit', 'stay', 'interact', 'signup', 'subscription', 'purchase')) AS conversions
|
||||||
|
FROM shorturl_analytics.events
|
||||||
|
${whereClause}
|
||||||
|
${whereClause ? 'AND' : 'WHERE'} ${utmField} != ''
|
||||||
|
GROUP BY utm_value
|
||||||
|
ORDER BY clicks DESC
|
||||||
|
LIMIT 100
|
||||||
|
`;
|
||||||
|
|
||||||
|
// 执行查询
|
||||||
|
const result = await clickhouse.query({
|
||||||
|
query,
|
||||||
|
format: 'JSONEachRow',
|
||||||
|
});
|
||||||
|
|
||||||
|
// 获取查询结果
|
||||||
|
const rows = await result.json();
|
||||||
|
const data = rows as UtmData[];
|
||||||
|
|
||||||
|
// 返回数据
|
||||||
|
const response: ApiResponse<UtmData[]> = {
|
||||||
|
success: true,
|
||||||
|
data
|
||||||
|
};
|
||||||
|
|
||||||
|
return NextResponse.json(response);
|
||||||
|
} catch (error) {
|
||||||
|
console.error('Error fetching UTM data:', error);
|
||||||
|
|
||||||
|
const response: ApiResponse<null> = {
|
||||||
|
success: false,
|
||||||
|
error: error instanceof Error ? error.message : 'Unknown error occurred'
|
||||||
|
};
|
||||||
|
|
||||||
|
return NextResponse.json(response, { status: 500 });
|
||||||
|
}
|
||||||
|
}
|
||||||
103
app/api/shortlinks/route.ts
Normal file
103
app/api/shortlinks/route.ts
Normal file
@@ -0,0 +1,103 @@
|
|||||||
|
import { NextResponse } from 'next/server';
|
||||||
|
import { executeQuery } from '@/lib/clickhouse';
|
||||||
|
import { NextRequest } from 'next/server';
|
||||||
|
|
||||||
|
export async function GET(request: NextRequest) {
|
||||||
|
try {
|
||||||
|
// Get pagination and filter parameters from the URL
|
||||||
|
const searchParams = request.nextUrl.searchParams;
|
||||||
|
const page = parseInt(searchParams.get('page') || '1', 10);
|
||||||
|
const pageSize = parseInt(searchParams.get('page_size') || '10', 10);
|
||||||
|
const search = searchParams.get('search');
|
||||||
|
const team = searchParams.get('team');
|
||||||
|
|
||||||
|
// Calculate OFFSET
|
||||||
|
const offset = (page - 1) * pageSize;
|
||||||
|
|
||||||
|
// Build WHERE conditions
|
||||||
|
const whereConditions = ['deleted_at IS NULL'];
|
||||||
|
|
||||||
|
if (search) {
|
||||||
|
// Expand search to include more fields: slug, shortUrl in attributes, team name, tag name, original_url
|
||||||
|
whereConditions.push(`(
|
||||||
|
slug ILIKE '%${search}%' OR
|
||||||
|
original_url ILIKE '%${search}%' OR
|
||||||
|
title ILIKE '%${search}%' OR
|
||||||
|
JSONHas(attributes, 'shortUrl') AND JSONExtractString(attributes, 'shortUrl') ILIKE '%${search}%' OR
|
||||||
|
arrayExists(x -> JSONExtractString(x, 'team_name') ILIKE '%${search}%', JSONExtractArrayRaw(teams)) OR
|
||||||
|
arrayExists(x -> JSONExtractString(x, 'tag_name') ILIKE '%${search}%', JSONExtractArrayRaw(tags))
|
||||||
|
)`);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (team) {
|
||||||
|
whereConditions.push(`arrayExists(x -> JSONExtractString(x, 'team_id') = '${team}', JSONExtractArrayRaw(teams))`);
|
||||||
|
}
|
||||||
|
|
||||||
|
const whereClause = whereConditions.join(' AND ');
|
||||||
|
|
||||||
|
// First query to get total count
|
||||||
|
const countQuery = `
|
||||||
|
SELECT count(*) as total
|
||||||
|
FROM shorturl_analytics.shorturl
|
||||||
|
WHERE ${whereClause}
|
||||||
|
`;
|
||||||
|
|
||||||
|
const countResult = await executeQuery(countQuery);
|
||||||
|
// Handle the result safely by using an explicit type check
|
||||||
|
const total = Array.isArray(countResult) && countResult.length > 0 && typeof countResult[0] === 'object' && countResult[0] !== null && 'total' in countResult[0]
|
||||||
|
? Number(countResult[0].total)
|
||||||
|
: 0;
|
||||||
|
const totalPages = Math.ceil(total / pageSize);
|
||||||
|
|
||||||
|
// Main query with pagination
|
||||||
|
const query = `
|
||||||
|
SELECT
|
||||||
|
id,
|
||||||
|
external_id,
|
||||||
|
type,
|
||||||
|
slug,
|
||||||
|
original_url,
|
||||||
|
title,
|
||||||
|
description,
|
||||||
|
attributes,
|
||||||
|
schema_version,
|
||||||
|
creator_id,
|
||||||
|
creator_email,
|
||||||
|
creator_name,
|
||||||
|
created_at,
|
||||||
|
updated_at,
|
||||||
|
deleted_at,
|
||||||
|
projects,
|
||||||
|
teams,
|
||||||
|
tags,
|
||||||
|
qr_codes AS qr_codes,
|
||||||
|
channels,
|
||||||
|
favorites,
|
||||||
|
expires_at,
|
||||||
|
click_count,
|
||||||
|
unique_visitors
|
||||||
|
FROM shorturl_analytics.shorturl
|
||||||
|
WHERE ${whereClause}
|
||||||
|
ORDER BY created_at DESC
|
||||||
|
LIMIT ${pageSize} OFFSET ${offset}
|
||||||
|
`;
|
||||||
|
|
||||||
|
// Execute the query using the shared client
|
||||||
|
const rows = await executeQuery(query);
|
||||||
|
|
||||||
|
// Return the data with pagination metadata
|
||||||
|
return NextResponse.json({
|
||||||
|
links: rows,
|
||||||
|
total: total,
|
||||||
|
total_pages: totalPages,
|
||||||
|
page: page,
|
||||||
|
page_size: pageSize
|
||||||
|
});
|
||||||
|
} catch (error) {
|
||||||
|
console.error('Error fetching shortlinks from ClickHouse:', error);
|
||||||
|
return NextResponse.json(
|
||||||
|
{ error: 'Failed to fetch shortlinks' },
|
||||||
|
{ status: 500 }
|
||||||
|
);
|
||||||
|
}
|
||||||
|
}
|
||||||
@@ -54,6 +54,8 @@ export interface Event {
|
|||||||
utm_source: string;
|
utm_source: string;
|
||||||
utm_medium: string;
|
utm_medium: string;
|
||||||
utm_campaign: string;
|
utm_campaign: string;
|
||||||
|
utm_term: string;
|
||||||
|
utm_content: string;
|
||||||
|
|
||||||
// 交互信息
|
// 交互信息
|
||||||
time_spent_sec: number;
|
time_spent_sec: number;
|
||||||
|
|||||||
203
app/components/analytics/UtmAnalytics.tsx
Normal file
203
app/components/analytics/UtmAnalytics.tsx
Normal file
@@ -0,0 +1,203 @@
|
|||||||
|
"use client";
|
||||||
|
|
||||||
|
import { useState, useEffect } from 'react';
|
||||||
|
|
||||||
|
interface UtmData {
|
||||||
|
utm_value: string;
|
||||||
|
clicks: number;
|
||||||
|
visitors: number;
|
||||||
|
avg_time_spent: number;
|
||||||
|
bounces: number;
|
||||||
|
conversions: number;
|
||||||
|
}
|
||||||
|
|
||||||
|
interface UtmAnalyticsProps {
|
||||||
|
startTime?: string;
|
||||||
|
endTime?: string;
|
||||||
|
linkId?: string;
|
||||||
|
teamIds?: string[];
|
||||||
|
projectIds?: string[];
|
||||||
|
tagIds?: string[];
|
||||||
|
}
|
||||||
|
|
||||||
|
export default function UtmAnalytics({ startTime, endTime, linkId, teamIds, projectIds, tagIds }: UtmAnalyticsProps) {
|
||||||
|
const [activeTab, setActiveTab] = useState<string>('source');
|
||||||
|
const [utmData, setUtmData] = useState<UtmData[]>([]);
|
||||||
|
const [isLoading, setIsLoading] = useState<boolean>(false);
|
||||||
|
const [error, setError] = useState<string | null>(null);
|
||||||
|
|
||||||
|
// 加载UTM数据
|
||||||
|
useEffect(() => {
|
||||||
|
const fetchUtmData = async () => {
|
||||||
|
setIsLoading(true);
|
||||||
|
setError(null);
|
||||||
|
|
||||||
|
try {
|
||||||
|
// 构建URL参数
|
||||||
|
const params = new URLSearchParams();
|
||||||
|
if (startTime) params.append('startTime', startTime);
|
||||||
|
if (endTime) params.append('endTime', endTime);
|
||||||
|
if (linkId) params.append('linkId', linkId);
|
||||||
|
params.append('utmType', activeTab);
|
||||||
|
|
||||||
|
// 添加团队ID参数
|
||||||
|
if (teamIds && teamIds.length > 0) {
|
||||||
|
teamIds.forEach(id => params.append('teamId', id));
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加项目ID参数
|
||||||
|
if (projectIds && projectIds.length > 0) {
|
||||||
|
projectIds.forEach(id => params.append('projectId', id));
|
||||||
|
}
|
||||||
|
|
||||||
|
// 添加标签名称参数
|
||||||
|
if (tagIds && tagIds.length > 0) {
|
||||||
|
tagIds.forEach(tagName => params.append('tagName', tagName));
|
||||||
|
}
|
||||||
|
|
||||||
|
// 发送请求
|
||||||
|
const response = await fetch(`/api/events/utm?${params}`);
|
||||||
|
|
||||||
|
if (!response.ok) {
|
||||||
|
throw new Error('Failed to fetch UTM data');
|
||||||
|
}
|
||||||
|
|
||||||
|
const result = await response.json();
|
||||||
|
|
||||||
|
if (result.success) {
|
||||||
|
setUtmData(result.data || []);
|
||||||
|
} else {
|
||||||
|
throw new Error(result.error || 'Failed to fetch UTM data');
|
||||||
|
}
|
||||||
|
} catch (err) {
|
||||||
|
setError(err instanceof Error ? err.message : 'Unknown error occurred');
|
||||||
|
console.error('Error fetching UTM data:', err);
|
||||||
|
} finally {
|
||||||
|
setIsLoading(false);
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
fetchUtmData();
|
||||||
|
}, [activeTab, startTime, endTime, linkId, teamIds, projectIds, tagIds]);
|
||||||
|
|
||||||
|
// 安全地格式化数字
|
||||||
|
const formatNumber = (value: number | undefined | null): string => {
|
||||||
|
if (value === undefined || value === null) return '0';
|
||||||
|
return value.toLocaleString();
|
||||||
|
};
|
||||||
|
|
||||||
|
return (
|
||||||
|
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
||||||
|
<h2 className="text-lg font-semibold text-gray-900 mb-4">UTM Parameters</h2>
|
||||||
|
|
||||||
|
<div className="mb-4 border-b">
|
||||||
|
<div className="flex">
|
||||||
|
<button
|
||||||
|
onClick={() => setActiveTab('source')}
|
||||||
|
className={`px-4 py-2 ${activeTab === 'source' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
|
||||||
|
>
|
||||||
|
Source
|
||||||
|
</button>
|
||||||
|
<button
|
||||||
|
onClick={() => setActiveTab('medium')}
|
||||||
|
className={`px-4 py-2 ${activeTab === 'medium' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
|
||||||
|
>
|
||||||
|
Medium
|
||||||
|
</button>
|
||||||
|
<button
|
||||||
|
onClick={() => setActiveTab('campaign')}
|
||||||
|
className={`px-4 py-2 ${activeTab === 'campaign' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
|
||||||
|
>
|
||||||
|
Campaign
|
||||||
|
</button>
|
||||||
|
<button
|
||||||
|
onClick={() => setActiveTab('term')}
|
||||||
|
className={`px-4 py-2 ${activeTab === 'term' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
|
||||||
|
>
|
||||||
|
Term
|
||||||
|
</button>
|
||||||
|
<button
|
||||||
|
onClick={() => setActiveTab('content')}
|
||||||
|
className={`px-4 py-2 ${activeTab === 'content' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
|
||||||
|
>
|
||||||
|
Content
|
||||||
|
</button>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{isLoading ? (
|
||||||
|
<div className="flex justify-center items-center py-8">
|
||||||
|
<div className="animate-spin rounded-full h-6 w-6 border-b-2 border-blue-500"></div>
|
||||||
|
<span className="ml-2 text-gray-500">Loading...</span>
|
||||||
|
</div>
|
||||||
|
) : error ? (
|
||||||
|
<div className="text-red-500 text-center py-8">
|
||||||
|
Error: {error}
|
||||||
|
</div>
|
||||||
|
) : utmData.length === 0 ? (
|
||||||
|
<div className="text-gray-500 text-center py-8">
|
||||||
|
No data available
|
||||||
|
</div>
|
||||||
|
) : (
|
||||||
|
<div className="overflow-x-auto">
|
||||||
|
<table className="min-w-full divide-y divide-gray-200">
|
||||||
|
<thead className="bg-gray-50">
|
||||||
|
<tr>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
{activeTab === 'source' ? 'Source' :
|
||||||
|
activeTab === 'medium' ? 'Medium' :
|
||||||
|
activeTab === 'campaign' ? 'Campaign' :
|
||||||
|
activeTab === 'term' ? 'Term' : 'Content'}
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Clicks
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Visitors
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Avg. Time
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Bounce Rate
|
||||||
|
</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
||||||
|
Conversions
|
||||||
|
</th>
|
||||||
|
</tr>
|
||||||
|
</thead>
|
||||||
|
<tbody className="bg-white divide-y divide-gray-200">
|
||||||
|
{utmData.map((item, index) => {
|
||||||
|
const bounceRate = item.clicks > 0 ? (item.bounces / item.clicks) * 100 : 0;
|
||||||
|
const conversionRate = item.clicks > 0 ? (item.conversions / item.clicks) * 100 : 0;
|
||||||
|
|
||||||
|
return (
|
||||||
|
<tr key={index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{item.utm_value || 'Unknown'}
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{formatNumber(item.clicks)}
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{formatNumber(item.visitors)}
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{item.avg_time_spent.toFixed(1)}s
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{bounceRate.toFixed(1)}%
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
||||||
|
{formatNumber(item.conversions)} ({conversionRate.toFixed(1)}%)
|
||||||
|
</td>
|
||||||
|
</tr>
|
||||||
|
);
|
||||||
|
})}
|
||||||
|
</tbody>
|
||||||
|
</table>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
@@ -14,7 +14,7 @@ export default function Header() {
|
|||||||
<header className="w-full py-4 border-b border-gray-200 bg-white">
|
<header className="w-full py-4 border-b border-gray-200 bg-white">
|
||||||
<div className="container flex items-center justify-between px-4 mx-auto">
|
<div className="container flex items-center justify-between px-4 mx-auto">
|
||||||
<div className="flex items-center space-x-4">
|
<div className="flex items-center space-x-4">
|
||||||
<Link href="/" className="flex items-center space-x-2">
|
<Link href="/analytics" className="flex items-center space-x-2">
|
||||||
<svg
|
<svg
|
||||||
className="w-6 h-6 text-blue-500"
|
className="w-6 h-6 text-blue-500"
|
||||||
xmlns="http://www.w3.org/2000/svg"
|
xmlns="http://www.w3.org/2000/svg"
|
||||||
@@ -30,6 +30,23 @@ export default function Header() {
|
|||||||
</svg>
|
</svg>
|
||||||
<span className="text-xl font-bold text-gray-900">ShortURL Analytics</span>
|
<span className="text-xl font-bold text-gray-900">ShortURL Analytics</span>
|
||||||
</Link>
|
</Link>
|
||||||
|
|
||||||
|
{user && (
|
||||||
|
<nav className="ml-6">
|
||||||
|
<ul className="flex space-x-4">
|
||||||
|
<li>
|
||||||
|
<Link href="/analytics" className="text-sm text-gray-700 hover:text-blue-500">
|
||||||
|
Analytics
|
||||||
|
</Link>
|
||||||
|
</li>
|
||||||
|
<li>
|
||||||
|
<Link href="/links" className="text-sm text-gray-700 hover:text-blue-500">
|
||||||
|
Short Links
|
||||||
|
</Link>
|
||||||
|
</li>
|
||||||
|
</ul>
|
||||||
|
</nav>
|
||||||
|
)}
|
||||||
</div>
|
</div>
|
||||||
|
|
||||||
{user && (
|
{user && (
|
||||||
|
|||||||
@@ -1,10 +0,0 @@
|
|||||||
import IpLocationTest from '../components/ipLocationTest';
|
|
||||||
|
|
||||||
export default function IpTestPage() {
|
|
||||||
return (
|
|
||||||
<div className="container mx-auto p-4 max-w-4xl">
|
|
||||||
<h1 className="text-2xl font-bold mb-6">IP to Location Test</h1>
|
|
||||||
<IpLocationTest />
|
|
||||||
</div>
|
|
||||||
);
|
|
||||||
}
|
|
||||||
606
app/links/page.tsx
Normal file
606
app/links/page.tsx
Normal file
@@ -0,0 +1,606 @@
|
|||||||
|
"use client";
|
||||||
|
|
||||||
|
import { useEffect, useState } from 'react';
|
||||||
|
import { getSupabaseClient } from '../utils/supabase';
|
||||||
|
import { AuthChangeEvent } from '@supabase/supabase-js';
|
||||||
|
import { Loader2, ExternalLink, Search } from 'lucide-react';
|
||||||
|
import { TeamSelector } from '@/app/components/ui/TeamSelector';
|
||||||
|
import { useRouter } from 'next/navigation';
|
||||||
|
import { useShortUrlStore, ShortUrlData } from '@/app/utils/store';
|
||||||
|
|
||||||
|
// Define attribute type to avoid using 'any'
|
||||||
|
interface LinkAttributes {
|
||||||
|
title?: string;
|
||||||
|
name?: string;
|
||||||
|
slug?: string;
|
||||||
|
original_url?: string;
|
||||||
|
originalUrl?: string;
|
||||||
|
visits?: number;
|
||||||
|
click_count?: number;
|
||||||
|
team_id?: string;
|
||||||
|
team_name?: string;
|
||||||
|
tags?: string[];
|
||||||
|
[key: string]: unknown;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 更新 ShortLink 类型定义以匹配 ClickHouse 数据结构
|
||||||
|
interface ShortLink {
|
||||||
|
id: string;
|
||||||
|
external_id?: string;
|
||||||
|
type?: string;
|
||||||
|
slug?: string;
|
||||||
|
original_url?: string;
|
||||||
|
title?: string;
|
||||||
|
description?: string;
|
||||||
|
attributes: string | Record<string, unknown>;
|
||||||
|
schema_version?: number;
|
||||||
|
creator_id?: string;
|
||||||
|
creator_email?: string;
|
||||||
|
creator_name?: string;
|
||||||
|
created_at: string;
|
||||||
|
updated_at?: string;
|
||||||
|
deleted_at?: string | null;
|
||||||
|
projects?: string | Record<string, unknown>[];
|
||||||
|
teams?: string | Record<string, unknown>[];
|
||||||
|
tags?: string | Record<string, unknown>[];
|
||||||
|
qr_codes?: string | Record<string, unknown>[];
|
||||||
|
channels?: string | Record<string, unknown>[];
|
||||||
|
favorites?: string | Record<string, unknown>[];
|
||||||
|
expires_at?: string | null;
|
||||||
|
click_count?: number;
|
||||||
|
unique_visitors?: number;
|
||||||
|
}
|
||||||
|
|
||||||
|
// Define ClickHouse shorturl type
|
||||||
|
interface ClickHouseShortUrl {
|
||||||
|
id: string;
|
||||||
|
external_id: string;
|
||||||
|
type: string;
|
||||||
|
slug: string;
|
||||||
|
original_url: string;
|
||||||
|
title: string;
|
||||||
|
description: string;
|
||||||
|
attributes: string; // JSON string
|
||||||
|
schema_version: number;
|
||||||
|
creator_id: string;
|
||||||
|
creator_email: string;
|
||||||
|
creator_name: string;
|
||||||
|
created_at: string;
|
||||||
|
updated_at: string;
|
||||||
|
deleted_at: string | null;
|
||||||
|
projects: string; // JSON string
|
||||||
|
teams: string; // JSON string
|
||||||
|
tags: string; // JSON string
|
||||||
|
qr_codes: string; // JSON string
|
||||||
|
channels: string; // JSON string
|
||||||
|
favorites: string; // JSON string
|
||||||
|
expires_at: string | null;
|
||||||
|
click_count: number;
|
||||||
|
unique_visitors: number;
|
||||||
|
link_attributes?: string; // Optional JSON string containing link-specific attributes
|
||||||
|
}
|
||||||
|
|
||||||
|
// 示例团队数据 - 实际应用中应从API获取
|
||||||
|
const teams = [
|
||||||
|
{ id: 'marketing', name: 'Marketing' },
|
||||||
|
{ id: 'sales', name: 'Sales' },
|
||||||
|
{ id: 'product', name: 'Product' },
|
||||||
|
{ id: 'engineering', name: 'Engineering' }
|
||||||
|
];
|
||||||
|
|
||||||
|
// 将 ClickHouse 数据转换为 ShortLink 格式
|
||||||
|
const convertClickHouseToShortLink = (data: Record<string, unknown>): ShortLink => {
|
||||||
|
return {
|
||||||
|
...data as any, // 使用类型断言处理泛型记录转换
|
||||||
|
// 确保关键字段存在
|
||||||
|
id: data.id as string || '',
|
||||||
|
created_at: data.created_at as string || new Date().toISOString(),
|
||||||
|
attributes: data.attributes || '{}'
|
||||||
|
};
|
||||||
|
};
|
||||||
|
|
||||||
|
export default function LinksPage() {
|
||||||
|
const [loading, setLoading] = useState(true);
|
||||||
|
const [error, setError] = useState<string | null>(null);
|
||||||
|
const [links, setLinks] = useState<ShortLink[]>([]);
|
||||||
|
const [searchQuery, setSearchQuery] = useState('');
|
||||||
|
const [teamFilter, setTeamFilter] = useState<string | null>(null);
|
||||||
|
const [currentPage, setCurrentPage] = useState(1);
|
||||||
|
const [pageSize, setPageSize] = useState(10);
|
||||||
|
const [totalLinks, setTotalLinks] = useState(0);
|
||||||
|
const [totalPages, setTotalPages] = useState(0);
|
||||||
|
const [searchDebounce, setSearchDebounce] = useState<NodeJS.Timeout | null>(null);
|
||||||
|
const router = useRouter();
|
||||||
|
|
||||||
|
// 使用 Zustand store
|
||||||
|
const { setSelectedShortUrl } = useShortUrlStore();
|
||||||
|
|
||||||
|
// 处理链接记录点击
|
||||||
|
const handleLinkClick = (shortUrl: string, link: ShortLink, metadata: any) => {
|
||||||
|
// 编码 shortUrl 以确保 URL 安全
|
||||||
|
const encodedShortUrl = encodeURIComponent(shortUrl);
|
||||||
|
|
||||||
|
// 创建完整的 ShortUrlData 对象
|
||||||
|
const shortUrlData: ShortUrlData = {
|
||||||
|
id: link.id,
|
||||||
|
slug: metadata.slug,
|
||||||
|
originalUrl: metadata.originalUrl,
|
||||||
|
title: metadata.title,
|
||||||
|
shortUrl: shortUrl,
|
||||||
|
teams: metadata.teamNames,
|
||||||
|
tags: metadata.tagNames,
|
||||||
|
projects: metadata.projectNames,
|
||||||
|
createdAt: metadata.createdAt,
|
||||||
|
domain: metadata.domain
|
||||||
|
};
|
||||||
|
|
||||||
|
// 使用 Zustand store 保存数据
|
||||||
|
setSelectedShortUrl(shortUrlData);
|
||||||
|
|
||||||
|
// 导航到 analytics 页面并带上参数
|
||||||
|
router.push(`/analytics?shorturl=${encodedShortUrl}`);
|
||||||
|
};
|
||||||
|
|
||||||
|
// Extract link metadata from attributes
|
||||||
|
const getLinkMetadata = (link: ShortLink) => {
|
||||||
|
try {
|
||||||
|
// Parse attributes if it's a string
|
||||||
|
const attributes = typeof link.attributes === 'string'
|
||||||
|
? JSON.parse(link.attributes)
|
||||||
|
: link.attributes || {};
|
||||||
|
|
||||||
|
// Parse attributes to get domain if available
|
||||||
|
let domain = 'shorturl.example.com';
|
||||||
|
try {
|
||||||
|
// Extract domain from shortUrl in attributes if available
|
||||||
|
const attributesObj = typeof link.attributes === 'string'
|
||||||
|
? JSON.parse(link.attributes)
|
||||||
|
: link.attributes || {};
|
||||||
|
|
||||||
|
if (attributesObj.shortUrl) {
|
||||||
|
try {
|
||||||
|
const urlObj = new URL(attributesObj.shortUrl);
|
||||||
|
domain = urlObj.hostname;
|
||||||
|
} catch (e) {
|
||||||
|
console.error('Error parsing shortUrl:', e);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (e) {
|
||||||
|
console.error('Error parsing attributes:', e);
|
||||||
|
}
|
||||||
|
|
||||||
|
// Get team names
|
||||||
|
const teamNames: string[] = [];
|
||||||
|
try {
|
||||||
|
if (link.teams) {
|
||||||
|
const teams = typeof link.teams === 'string'
|
||||||
|
? JSON.parse(link.teams)
|
||||||
|
: link.teams || [];
|
||||||
|
|
||||||
|
if (Array.isArray(teams)) {
|
||||||
|
teams.forEach(team => {
|
||||||
|
if (team.team_name) {
|
||||||
|
teamNames.push(team.team_name);
|
||||||
|
}
|
||||||
|
});
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (e) {
|
||||||
|
console.error('Error parsing teams:', e);
|
||||||
|
}
|
||||||
|
|
||||||
|
// Get project names
|
||||||
|
const projectNames: string[] = [];
|
||||||
|
try {
|
||||||
|
if (link.projects) {
|
||||||
|
const projects = typeof link.projects === 'string'
|
||||||
|
? JSON.parse(link.projects)
|
||||||
|
: link.projects || [];
|
||||||
|
|
||||||
|
if (Array.isArray(projects)) {
|
||||||
|
projects.forEach(project => {
|
||||||
|
if (project.project_name) {
|
||||||
|
projectNames.push(project.project_name);
|
||||||
|
}
|
||||||
|
});
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (e) {
|
||||||
|
console.error('Error parsing projects:', e);
|
||||||
|
}
|
||||||
|
|
||||||
|
// Get tag names
|
||||||
|
const tagNames: string[] = [];
|
||||||
|
try {
|
||||||
|
if (link.tags) {
|
||||||
|
const tags = typeof link.tags === 'string'
|
||||||
|
? JSON.parse(link.tags)
|
||||||
|
: link.tags || [];
|
||||||
|
|
||||||
|
if (Array.isArray(tags)) {
|
||||||
|
tags.forEach(tag => {
|
||||||
|
if (tag.tag_name) {
|
||||||
|
tagNames.push(tag.tag_name);
|
||||||
|
}
|
||||||
|
});
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (e) {
|
||||||
|
console.error('Error parsing tags:', e);
|
||||||
|
}
|
||||||
|
|
||||||
|
return {
|
||||||
|
title: link.title || attributes.title || 'Untitled',
|
||||||
|
slug: link.slug || attributes.slug || '',
|
||||||
|
domain: domain,
|
||||||
|
originalUrl: link.original_url || attributes.originalUrl || attributes.original_url || '',
|
||||||
|
teamNames: teamNames,
|
||||||
|
projectNames: projectNames,
|
||||||
|
tagNames: tagNames,
|
||||||
|
teamName: teamNames[0] || '', // Keep for backward compatibility
|
||||||
|
createdAt: new Date(link.created_at).toLocaleDateString(),
|
||||||
|
visits: link.click_count || 0
|
||||||
|
};
|
||||||
|
} catch (error) {
|
||||||
|
console.error('Error parsing link metadata:', error);
|
||||||
|
return {
|
||||||
|
title: 'Error parsing data',
|
||||||
|
slug: '',
|
||||||
|
domain: 'shorturl.example.com',
|
||||||
|
originalUrl: '',
|
||||||
|
teamNames: [],
|
||||||
|
projectNames: [],
|
||||||
|
tagNames: [],
|
||||||
|
teamName: '',
|
||||||
|
createdAt: '',
|
||||||
|
visits: 0
|
||||||
|
};
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
useEffect(() => {
|
||||||
|
let isMounted = true;
|
||||||
|
|
||||||
|
const fetchLinks = async () => {
|
||||||
|
if (!isMounted) return;
|
||||||
|
setLoading(true);
|
||||||
|
setError(null);
|
||||||
|
|
||||||
|
try {
|
||||||
|
// Fetch data from ClickHouse API with pagination parameters
|
||||||
|
const response = await fetch(`/api/shortlinks?page=${currentPage}&page_size=${pageSize}${searchQuery ? `&search=${encodeURIComponent(searchQuery)}` : ''}${teamFilter ? `&team=${encodeURIComponent(teamFilter)}` : ''}`);
|
||||||
|
|
||||||
|
if (!response.ok) {
|
||||||
|
throw new Error(`Failed to fetch links: ${response.status} ${response.statusText}`);
|
||||||
|
}
|
||||||
|
|
||||||
|
const data = await response.json();
|
||||||
|
|
||||||
|
if (!data || !data.links || data.links.length === 0) {
|
||||||
|
if (isMounted) {
|
||||||
|
setLinks([]);
|
||||||
|
setTotalLinks(0);
|
||||||
|
setTotalPages(0);
|
||||||
|
}
|
||||||
|
return;
|
||||||
|
}
|
||||||
|
|
||||||
|
// Convert ClickHouse data format to ShortLink format
|
||||||
|
const convertedLinks = data.links.map(convertClickHouseToShortLink);
|
||||||
|
|
||||||
|
if (isMounted) {
|
||||||
|
setLinks(convertedLinks);
|
||||||
|
setTotalLinks(data.total || convertedLinks.length);
|
||||||
|
setTotalPages(data.total_pages || Math.ceil(data.total / pageSize) || 1);
|
||||||
|
}
|
||||||
|
} catch (err) {
|
||||||
|
if (isMounted) {
|
||||||
|
setError(err instanceof Error ? err.message : 'Failed to load short URLs');
|
||||||
|
console.error("Error fetching links:", err);
|
||||||
|
}
|
||||||
|
} finally {
|
||||||
|
if (isMounted) {
|
||||||
|
setLoading(false);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
};
|
||||||
|
|
||||||
|
// Subscribe to user auth state
|
||||||
|
const supabase = getSupabaseClient();
|
||||||
|
const { data: { subscription } } = supabase.auth.onAuthStateChange(
|
||||||
|
(event: AuthChangeEvent) => {
|
||||||
|
if (event === 'SIGNED_IN' || event === 'USER_UPDATED') {
|
||||||
|
fetchLinks();
|
||||||
|
}
|
||||||
|
if (event === 'SIGNED_OUT') {
|
||||||
|
setLinks([]);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
);
|
||||||
|
|
||||||
|
fetchLinks();
|
||||||
|
|
||||||
|
return () => {
|
||||||
|
isMounted = false;
|
||||||
|
subscription.unsubscribe();
|
||||||
|
};
|
||||||
|
}, [currentPage, pageSize, searchQuery, teamFilter]);
|
||||||
|
|
||||||
|
// Handle search input with debounce
|
||||||
|
const handleSearchChange = (e: React.ChangeEvent<HTMLInputElement>) => {
|
||||||
|
const value = e.target.value;
|
||||||
|
|
||||||
|
// Clear any existing timeout
|
||||||
|
if (searchDebounce) {
|
||||||
|
clearTimeout(searchDebounce);
|
||||||
|
}
|
||||||
|
|
||||||
|
// Set the input value immediately for UI feedback
|
||||||
|
setSearchQuery(value);
|
||||||
|
|
||||||
|
// Set a timeout to actually perform the search
|
||||||
|
setSearchDebounce(setTimeout(() => {
|
||||||
|
setCurrentPage(1); // Reset to page 1 when searching
|
||||||
|
}, 500)); // 500ms debounce
|
||||||
|
};
|
||||||
|
|
||||||
|
if (loading && links.length === 0) {
|
||||||
|
return (
|
||||||
|
<div className="flex h-96 w-full items-center justify-center">
|
||||||
|
<Loader2 className="h-8 w-8 animate-spin text-gray-500" />
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (error) {
|
||||||
|
return (
|
||||||
|
<div className="flex h-96 w-full flex-col items-center justify-center text-red-500">
|
||||||
|
<p>Error loading shortcuts: {error}</p>
|
||||||
|
<button
|
||||||
|
onClick={() => window.location.reload()}
|
||||||
|
className="mt-4 rounded-md bg-blue-500 px-4 py-2 text-white hover:bg-blue-600"
|
||||||
|
>
|
||||||
|
Retry
|
||||||
|
</button>
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
|
|
||||||
|
return (
|
||||||
|
<div className="container mx-auto px-4 py-8">
|
||||||
|
<h1 className="mb-6 text-2xl font-bold text-gray-900">Short URL Links</h1>
|
||||||
|
|
||||||
|
{/* Search and filters */}
|
||||||
|
<div className="mb-6 flex flex-wrap items-center gap-4">
|
||||||
|
<div className="relative flex-grow">
|
||||||
|
<Search className="absolute left-3 top-1/2 h-4 w-4 -translate-y-1/2 text-gray-400" />
|
||||||
|
<input
|
||||||
|
type="text"
|
||||||
|
placeholder="Search links..."
|
||||||
|
value={searchQuery}
|
||||||
|
onChange={handleSearchChange}
|
||||||
|
onKeyDown={(e) => {
|
||||||
|
if (e.key === 'Enter') {
|
||||||
|
setCurrentPage(1); // Reset to page 1 when searching
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
className="w-full rounded-md border border-gray-300 py-2 pl-10 pr-4 text-sm focus:border-blue-500 focus:outline-none focus:ring-1 focus:ring-blue-500"
|
||||||
|
/>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
<div className="flex items-center gap-2">
|
||||||
|
<TeamSelector
|
||||||
|
value={teamFilter || ''}
|
||||||
|
onChange={(value) => {
|
||||||
|
// 如果是多选模式，值将是数组。对于空数组，设置为 null
|
||||||
|
if (Array.isArray(value)) {
|
||||||
|
setTeamFilter(value.length > 0 ? value[0] : null);
|
||||||
|
} else {
|
||||||
|
setTeamFilter(value || null);
|
||||||
|
}
|
||||||
|
setCurrentPage(1); // Reset to page 1 when filtering
|
||||||
|
}}
|
||||||
|
className="w-64"
|
||||||
|
multiple={true}
|
||||||
|
/>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{/* Links table */}
|
||||||
|
<div className="overflow-hidden rounded-lg border border-gray-200 shadow">
|
||||||
|
<table className="min-w-full divide-y divide-gray-200">
|
||||||
|
<thead className="bg-gray-50">
|
||||||
|
<tr>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Link</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Original URL</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Team</th>
|
||||||
|
<th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Created</th>
|
||||||
|
</tr>
|
||||||
|
</thead>
|
||||||
|
<tbody className="divide-y divide-gray-200 bg-white">
|
||||||
|
{links.map(link => {
|
||||||
|
const metadata = getLinkMetadata(link);
|
||||||
|
const shortUrl = `https://${metadata.domain}/${metadata.slug}`;
|
||||||
|
|
||||||
|
return (
|
||||||
|
<tr key={link.id} className="hover:bg-gray-50 cursor-pointer" onClick={() => handleLinkClick(shortUrl, link, metadata)}>
|
||||||
|
<td className="px-6 py-4">
|
||||||
|
<div className="flex flex-col space-y-1">
|
||||||
|
<span className="font-medium text-gray-900">{metadata.title}</span>
|
||||||
|
<span className="text-xs text-blue-500">{shortUrl}</span>
|
||||||
|
|
||||||
|
{/* Tags */}
|
||||||
|
{metadata.tagNames.length > 0 && (
|
||||||
|
<div className="flex flex-wrap gap-1.5 mt-1">
|
||||||
|
{metadata.tagNames.map((tag, index) => (
|
||||||
|
<span key={index} className="inline-flex items-center rounded-full bg-gray-100 px-2 py-0.5 text-xs font-medium text-gray-800">
|
||||||
|
{tag}
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 text-sm text-gray-500">
|
||||||
|
<a
|
||||||
|
href={metadata.originalUrl}
|
||||||
|
target="_blank"
|
||||||
|
rel="noopener noreferrer"
|
||||||
|
className="flex items-center hover:text-blue-500"
|
||||||
|
>
|
||||||
|
<span className="max-w-xs truncate">{metadata.originalUrl}</span>
|
||||||
|
<ExternalLink className="ml-1 h-3 w-3" />
|
||||||
|
</a>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 text-sm text-gray-500">
|
||||||
|
<div className="flex flex-col space-y-1">
|
||||||
|
{/* Teams */}
|
||||||
|
{metadata.teamNames.length > 0 ? (
|
||||||
|
<div className="flex flex-wrap gap-1.5">
|
||||||
|
{metadata.teamNames.map((team, index) => (
|
||||||
|
<span key={index} className="inline-flex items-center rounded-full bg-blue-100 px-2 py-0.5 text-xs font-medium text-blue-800">
|
||||||
|
{team}
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
</div>
|
||||||
|
) : (
|
||||||
|
<span>-</span>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{/* Projects */}
|
||||||
|
{metadata.projectNames.length > 0 && (
|
||||||
|
<div className="flex flex-wrap gap-1.5 mt-1">
|
||||||
|
{metadata.projectNames.map((project, index) => (
|
||||||
|
<span key={index} className="inline-flex items-center rounded-full bg-green-100 px-2 py-0.5 text-xs font-medium text-green-800">
|
||||||
|
{project}
|
||||||
|
</span>
|
||||||
|
))}
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
</td>
|
||||||
|
<td className="px-6 py-4 text-sm text-gray-500">
|
||||||
|
{metadata.createdAt}
|
||||||
|
</td>
|
||||||
|
</tr>
|
||||||
|
);
|
||||||
|
})}
|
||||||
|
</tbody>
|
||||||
|
</table>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
{/* Pagination */}
|
||||||
|
{totalPages > 0 && (
|
||||||
|
<div className="mt-6 flex items-center justify-between">
|
||||||
|
<div className="text-sm text-gray-500">
|
||||||
|
Showing {((currentPage - 1) * pageSize) + 1} to {Math.min(currentPage * pageSize, totalLinks)} of {totalLinks} results
|
||||||
|
</div>
|
||||||
|
<div className="flex items-center space-x-2">
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
|
||||||
|
disabled={currentPage === 1}
|
||||||
|
className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
|
||||||
|
>
|
||||||
|
Previous
|
||||||
|
</button>
|
||||||
|
{Array.from({ length: Math.min(totalPages, 5) }, (_, i) => {
|
||||||
|
// Create a window of 5 pages around current page
|
||||||
|
let pageNumber;
|
||||||
|
if (totalPages <= 5) {
|
||||||
|
pageNumber = i + 1;
|
||||||
|
} else {
|
||||||
|
const start = Math.max(1, currentPage - 2);
|
||||||
|
const end = Math.min(totalPages, start + 4);
|
||||||
|
pageNumber = start + i;
|
||||||
|
if (pageNumber > end) return null;
|
||||||
|
}
|
||||||
|
|
||||||
|
return (
|
||||||
|
<button
|
||||||
|
key={pageNumber}
|
||||||
|
onClick={() => setCurrentPage(pageNumber)}
|
||||||
|
className={`h-8 w-8 rounded-md text-sm ${
|
||||||
|
currentPage === pageNumber
|
||||||
|
? 'bg-blue-500 text-white'
|
||||||
|
: 'border border-gray-300 text-gray-700 hover:bg-gray-50'
|
||||||
|
}`}
|
||||||
|
>
|
||||||
|
{pageNumber}
|
||||||
|
</button>
|
||||||
|
);
|
||||||
|
})}
|
||||||
|
<button
|
||||||
|
onClick={() => setCurrentPage(prev => Math.min(prev + 1, totalPages))}
|
||||||
|
disabled={currentPage === totalPages}
|
||||||
|
className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
|
||||||
|
>
|
||||||
|
Next
|
||||||
|
</button>
|
||||||
|
|
||||||
|
{/* Page input */}
|
||||||
|
<div className="ml-4 flex items-center space-x-1">
|
||||||
|
<span className="text-sm text-gray-500">Go to:</span>
|
||||||
|
<input
|
||||||
|
type="number"
|
||||||
|
min="1"
|
||||||
|
max={totalPages}
|
||||||
|
value={currentPage}
|
||||||
|
onChange={(e) => {
|
||||||
|
// Allow input to be cleared for typing
|
||||||
|
if (e.target.value === '') {
|
||||||
|
e.target.value = '';
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
onBlur={(e) => {
|
||||||
|
// Ensure a valid value on blur
|
||||||
|
const value = parseInt(e.target.value, 10);
|
||||||
|
if (isNaN(value) || value < 1) {
|
||||||
|
setCurrentPage(1);
|
||||||
|
} else if (value > totalPages) {
|
||||||
|
setCurrentPage(totalPages);
|
||||||
|
} else {
|
||||||
|
setCurrentPage(value);
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
onKeyDown={(e) => {
|
||||||
|
if (e.key === 'Enter') {
|
||||||
|
const value = parseInt(e.currentTarget.value, 10);
|
||||||
|
if (!isNaN(value) && value >= 1 && value <= totalPages) {
|
||||||
|
setCurrentPage(value);
|
||||||
|
} else if (!isNaN(value) && value < 1) {
|
||||||
|
setCurrentPage(1);
|
||||||
|
} else if (!isNaN(value) && value > totalPages) {
|
||||||
|
setCurrentPage(totalPages);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
}}
|
||||||
|
className="w-16 rounded-md border border-gray-300 px-2 py-1 text-sm text-center"
|
||||||
|
/>
|
||||||
|
<span className="text-sm text-gray-500">of {totalPages}</span>
|
||||||
|
</div>
|
||||||
|
|
||||||
|
<select
|
||||||
|
value={pageSize}
|
||||||
|
onChange={(e) => {
|
||||||
|
setPageSize(Number(e.target.value));
|
||||||
|
setCurrentPage(1); // Reset to page 1 when changing page size
|
||||||
|
}}
|
||||||
|
className="ml-4 rounded-md border border-gray-300 py-1.5 pl-3 pr-8 text-sm"
|
||||||
|
>
|
||||||
|
<option value="10">10 per page</option>
|
||||||
|
<option value="25">25 per page</option>
|
||||||
|
<option value="50">50 per page</option>
|
||||||
|
<option value="100">100 per page</option>
|
||||||
|
</select>
|
||||||
|
</div>
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
|
||||||
|
{links.length === 0 && (
|
||||||
|
<div className="mt-6 rounded-md bg-gray-50 p-6 text-center text-gray-500">
|
||||||
|
No links match your search criteria
|
||||||
|
</div>
|
||||||
|
)}
|
||||||
|
</div>
|
||||||
|
);
|
||||||
|
}
|
||||||
804
app/page.tsx
804
app/page.tsx
@@ -1,803 +1,5 @@
|
|||||||
"use client";
|
import { redirect } from 'next/navigation';
|
||||||
|
|
||||||
import { useState, useEffect } from 'react';
|
export default function Home() {
|
||||||
import { format, subDays } from 'date-fns';
|
redirect('/analytics');
|
||||||
import { DateRangePicker } from '@/app/components/ui/DateRangePicker';
|
|
||||||
import TimeSeriesChart from '@/app/components/charts/TimeSeriesChart';
|
|
||||||
import GeoAnalytics from '@/app/components/analytics/GeoAnalytics';
|
|
||||||
import DevicePieCharts from '@/app/components/charts/DevicePieCharts';
|
|
||||||
import { EventsSummary, TimeSeriesData, GeoData, DeviceAnalytics as DeviceAnalyticsType } from '@/app/api/types';
|
|
||||||
import { TeamSelector } from '@/app/components/ui/TeamSelector';
|
|
||||||
import { ProjectSelector } from '@/app/components/ui/ProjectSelector';
|
|
||||||
import { TagSelector } from '@/app/components/ui/TagSelector';
|
|
||||||
|
|
||||||
// 事件类型定义
|
|
||||||
interface Event {
|
|
||||||
event_id?: string;
|
|
||||||
url_id: string;
|
|
||||||
url: string;
|
|
||||||
event_type: string;
|
|
||||||
visitor_id: string;
|
|
||||||
created_at: string;
|
|
||||||
event_time?: string;
|
|
||||||
referrer?: string;
|
|
||||||
browser?: string;
|
|
||||||
os?: string;
|
|
||||||
device_type?: string;
|
|
||||||
country?: string;
|
|
||||||
city?: string;
|
|
||||||
event_attributes?: string;
|
|
||||||
link_attributes?: string;
|
|
||||||
user_attributes?: string;
|
|
||||||
link_label?: string;
|
|
||||||
link_original_url?: string;
|
|
||||||
team_name?: string;
|
|
||||||
project_name?: string;
|
|
||||||
link_id?: string;
|
|
||||||
link_slug?: string;
|
|
||||||
link_tags?: string;
|
|
||||||
ip_address?: string;
|
|
||||||
}
|
|
||||||
|
|
||||||
// 格式化日期函数
|
|
||||||
const formatDate = (dateString: string | undefined) => {
|
|
||||||
if (!dateString) return '';
|
|
||||||
try {
|
|
||||||
return format(new Date(dateString), 'yyyy-MM-dd HH:mm:ss');
|
|
||||||
} catch {
|
|
||||||
return dateString;
|
|
||||||
}
|
|
||||||
};
|
|
||||||
|
|
||||||
// 解析JSON字符串
|
|
||||||
const parseJsonSafely = (jsonString: string) => {
|
|
||||||
if (!jsonString) return null;
|
|
||||||
try {
|
|
||||||
return JSON.parse(jsonString);
|
|
||||||
} catch {
|
|
||||||
return null;
|
|
||||||
}
|
|
||||||
};
|
|
||||||
|
|
||||||
// 获取用户可读名称
|
|
||||||
const getUserDisplayName = (user: Record<string, unknown> | null) => {
|
|
||||||
if (!user) return '-';
|
|
||||||
if (typeof user.full_name === 'string') return user.full_name;
|
|
||||||
if (typeof user.name === 'string') return user.name;
|
|
||||||
if (typeof user.email === 'string') return user.email;
|
|
||||||
return '-';
|
|
||||||
};
|
|
||||||
|
|
||||||
// 提取链接和事件的重要信息
|
|
||||||
const extractEventInfo = (event: Event) => {
|
|
||||||
// 解析事件属性
|
|
||||||
const eventAttrs = parseJsonSafely(event.event_attributes || '{}');
|
|
||||||
|
|
||||||
// 解析链接属性
|
|
||||||
const linkAttrs = parseJsonSafely(event.link_attributes || '{}');
|
|
||||||
|
|
||||||
// 解析用户属性
|
|
||||||
const userAttrs = parseJsonSafely(event.user_attributes || '{}');
|
|
||||||
|
|
||||||
// 解析标签信息
|
|
||||||
let tags: string[] = [];
|
|
||||||
try {
|
|
||||||
if (event.link_tags) {
|
|
||||||
const parsedTags = JSON.parse(event.link_tags);
|
|
||||||
if (Array.isArray(parsedTags)) {
|
|
||||||
tags = parsedTags;
|
|
||||||
}
|
|
||||||
}
|
|
||||||
} catch {
|
|
||||||
// 解析失败则保持空数组
|
|
||||||
}
|
|
||||||
|
|
||||||
return {
|
|
||||||
eventTime: event.created_at || event.event_time,
|
|
||||||
linkName: event.link_label || linkAttrs?.name || eventAttrs?.link_name || event.link_slug || '-',
|
|
||||||
originalUrl: event.link_original_url || eventAttrs?.origin_url || '-',
|
|
||||||
eventType: event.event_type || '-',
|
|
||||||
visitorId: event.visitor_id?.substring(0, 8) || '-',
|
|
||||||
referrer: eventAttrs?.referrer || '-',
|
|
||||||
ipAddress: event.ip_address || '-',
|
|
||||||
location: event.country ? (event.city ? `${event.city}, ${event.country}` : event.country) : '-',
|
|
||||||
device: event.device_type || '-',
|
|
||||||
browser: event.browser || '-',
|
|
||||||
os: event.os || '-',
|
|
||||||
userInfo: getUserDisplayName(userAttrs),
|
|
||||||
teamName: event.team_name || '-',
|
|
||||||
projectName: event.project_name || '-',
|
|
||||||
tags: tags
|
|
||||||
};
|
|
||||||
};
|
|
||||||
|
|
||||||
export default function HomePage() {
|
|
||||||
// 默认日期范围为最近7天
|
|
||||||
const today = new Date();
|
|
||||||
const [dateRange, setDateRange] = useState({
|
|
||||||
from: subDays(today, 7), // 7天前
|
|
||||||
to: today // 今天
|
|
||||||
});
|
|
||||||
|
|
||||||
// 添加团队选择状态 - 使用数组支持多选
|
|
||||||
const [selectedTeamIds, setSelectedTeamIds] = useState<string[]>([]);
|
|
||||||
// 添加项目选择状态 - 使用数组支持多选
|
|
||||||
const [selectedProjectIds, setSelectedProjectIds] = useState<string[]>([]);
|
|
||||||
// 添加标签选择状态 - 使用数组支持多选
|
|
||||||
const [selectedTagIds, setSelectedTagIds] = useState<string[]>([]);
|
|
||||||
|
|
||||||
// 添加分页状态
|
|
||||||
const [currentPage, setCurrentPage] = useState<number>(1);
|
|
||||||
const [pageSize, setPageSize] = useState<number>(10);
|
|
||||||
const [totalEvents, setTotalEvents] = useState<number>(0);
|
|
||||||
|
|
||||||
const [loading, setLoading] = useState(true);
|
|
||||||
const [error, setError] = useState<string | null>(null);
|
|
||||||
const [summary, setSummary] = useState<EventsSummary | null>(null);
|
|
||||||
const [timeSeriesData, setTimeSeriesData] = useState<TimeSeriesData[]>([]);
|
|
||||||
const [geoData, setGeoData] = useState<GeoData[]>([]);
|
|
||||||
const [deviceData, setDeviceData] = useState<DeviceAnalyticsType | null>(null);
|
|
||||||
const [events, setEvents] = useState<Event[]>([]);
|
|
||||||
|
|
||||||
useEffect(() => {
|
|
||||||
const fetchData = async () => {
|
|
||||||
setLoading(true);
|
|
||||||
setError(null);
|
|
||||||
|
|
||||||
try {
|
|
||||||
const startTime = format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'");
|
|
||||||
const endTime = format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'");
|
|
||||||
|
|
||||||
// 构建基础URL和查询参数
|
|
||||||
const baseUrl = '/api/events';
|
|
||||||
const params = new URLSearchParams({
|
|
||||||
startTime,
|
|
||||||
endTime,
|
|
||||||
page: currentPage.toString(),
|
|
||||||
pageSize: pageSize.toString()
|
|
||||||
});
|
|
||||||
|
|
||||||
// 添加团队ID参数 - 支持多个团队
|
|
||||||
if (selectedTeamIds.length > 0) {
|
|
||||||
selectedTeamIds.forEach(teamId => {
|
|
||||||
params.append('teamId', teamId);
|
|
||||||
});
|
|
||||||
}
|
|
||||||
|
|
||||||
// 添加项目ID参数 - 支持多个项目
|
|
||||||
if (selectedProjectIds.length > 0) {
|
|
||||||
selectedProjectIds.forEach(projectId => {
|
|
||||||
params.append('projectId', projectId);
|
|
||||||
});
|
|
||||||
}
|
|
||||||
|
|
||||||
// 添加标签ID参数 - 支持多个标签
|
|
||||||
if (selectedTagIds.length > 0) {
|
|
||||||
selectedTagIds.forEach(tagId => {
|
|
||||||
params.append('tagId', tagId);
|
|
||||||
});
|
|
||||||
}
|
|
||||||
|
|
||||||
// 并行获取所有数据
|
|
||||||
const [summaryRes, timeSeriesRes, geoRes, deviceRes, eventsRes] = await Promise.all([
|
|
||||||
fetch(`${baseUrl}/summary?${params.toString()}`),
|
|
||||||
fetch(`${baseUrl}/time-series?${params.toString()}`),
|
|
||||||
fetch(`${baseUrl}/geo?${params.toString()}`),
|
|
||||||
fetch(`${baseUrl}/devices?${params.toString()}`),
|
|
||||||
fetch(`${baseUrl}?${params.toString()}`)
|
|
||||||
]);
|
|
||||||
|
|
||||||
const [summaryData, timeSeriesData, geoData, deviceData, eventsData] = await Promise.all([
|
|
||||||
summaryRes.json(),
|
|
||||||
timeSeriesRes.json(),
|
|
||||||
geoRes.json(),
|
|
||||||
deviceRes.json(),
|
|
||||||
eventsRes.json()
|
|
||||||
]);
|
|
||||||
|
|
||||||
if (!summaryRes.ok) throw new Error(summaryData.error || 'Failed to fetch summary data');
|
|
||||||
if (!timeSeriesRes.ok) throw new Error(timeSeriesData.error || 'Failed to fetch time series data');
|
|
||||||
if (!geoRes.ok) throw new Error(geoData.error || 'Failed to fetch geo data');
|
|
||||||
if (!deviceRes.ok) throw new Error(deviceData.error || 'Failed to fetch device data');
|
|
||||||
if (!eventsRes.ok) throw new Error(eventsData.error || 'Failed to fetch events data');
|
|
||||||
|
|
||||||
setSummary(summaryData.data);
|
|
||||||
setTimeSeriesData(timeSeriesData.data);
|
|
||||||
setGeoData(geoData.data);
|
|
||||||
setDeviceData(deviceData.data);
|
|
||||||
setEvents(eventsData.data || []);
|
|
||||||
|
|
||||||
// 设置总事件数量用于分页
|
|
||||||
if (eventsData.meta) {
|
|
||||||
// 确保将total转换为数字，无论它是字符串还是数字
|
|
||||||
const totalCount = parseInt(String(eventsData.meta.total), 10);
|
|
||||||
if (!isNaN(totalCount)) {
|
|
||||||
setTotalEvents(totalCount);
|
|
||||||
}
|
|
||||||
}
|
|
||||||
} catch (err) {
|
|
||||||
setError(err instanceof Error ? err.message : 'An error occurred while fetching data');
|
|
||||||
} finally {
|
|
||||||
setLoading(false);
|
|
||||||
}
|
|
||||||
};
|
|
||||||
|
|
||||||
fetchData();
|
|
||||||
}, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagIds, currentPage, pageSize]);
|
|
||||||
|
|
||||||
if (loading) {
|
|
||||||
return (
|
|
||||||
<div className="flex items-center justify-center min-h-screen">
|
|
||||||
<div className="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-blue-500" />
|
|
||||||
</div>
|
|
||||||
);
|
|
||||||
}
|
|
||||||
|
|
||||||
if (error) {
|
|
||||||
return (
|
|
||||||
<div className="flex items-center justify-center min-h-screen">
|
|
||||||
<div className="text-red-500">{error}</div>
|
|
||||||
</div>
|
|
||||||
);
|
|
||||||
}
|
|
||||||
|
|
||||||
return (
|
|
||||||
<div className="container mx-auto px-4 py-8">
|
|
||||||
<div className="flex justify-between items-center mb-8">
|
|
||||||
<h1 className="text-2xl font-bold text-gray-900">Analytics Dashboard</h1>
|
|
||||||
<div className="flex flex-col gap-4 md:flex-row md:items-center">
|
|
||||||
<TeamSelector
|
|
||||||
value={selectedTeamIds}
|
|
||||||
onChange={(value) => {
|
|
||||||
const newTeamIds = Array.isArray(value) ? value : [value];
|
|
||||||
|
|
||||||
// Check if team selection has changed
|
|
||||||
if (JSON.stringify(newTeamIds) !== JSON.stringify(selectedTeamIds)) {
|
|
||||||
// Clear project selection when team changes
|
|
||||||
setSelectedProjectIds([]);
|
|
||||||
|
|
||||||
// Update team selection
|
|
||||||
setSelectedTeamIds(newTeamIds);
|
|
||||||
}
|
|
||||||
}}
|
|
||||||
className="w-[250px]"
|
|
||||||
multiple={true}
|
|
||||||
/>
|
|
||||||
<ProjectSelector
|
|
||||||
value={selectedProjectIds}
|
|
||||||
onChange={(value) => setSelectedProjectIds(Array.isArray(value) ? value : [value])}
|
|
||||||
className="w-[250px]"
|
|
||||||
multiple={true}
|
|
||||||
teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
|
|
||||||
/>
|
|
||||||
<TagSelector
|
|
||||||
value={selectedTagIds}
|
|
||||||
onChange={(value) => setSelectedTagIds(Array.isArray(value) ? value : [value])}
|
|
||||||
className="w-[250px]"
|
|
||||||
multiple={true}
|
|
||||||
teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
|
|
||||||
/>
|
|
||||||
<DateRangePicker
|
|
||||||
value={dateRange}
|
|
||||||
onChange={setDateRange}
|
|
||||||
/>
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
{/* 显示团队选择信息 */}
|
|
||||||
{selectedTeamIds.length > 0 && (
|
|
||||||
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
|
||||||
<span className="text-blue-700 font-medium mr-2">
|
|
||||||
{selectedTeamIds.length === 1 ? 'Team filter:' : 'Teams filter:'}
|
|
||||||
</span>
|
|
||||||
<div className="flex flex-wrap gap-2">
|
|
||||||
{selectedTeamIds.map(teamId => (
|
|
||||||
<span key={teamId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
|
||||||
{teamId}
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedTeamIds(selectedTeamIds.filter(id => id !== teamId))}
|
|
||||||
className="ml-1 text-blue-600 hover:text-blue-800"
|
|
||||||
>
|
|
||||||
×
|
|
||||||
</button>
|
|
||||||
</span>
|
|
||||||
))}
|
|
||||||
{selectedTeamIds.length > 0 && (
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedTeamIds([])}
|
|
||||||
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
|
||||||
>
|
|
||||||
Clear all
|
|
||||||
</button>
|
|
||||||
)}
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
|
|
||||||
{/* 显示项目选择信息 */}
|
|
||||||
{selectedProjectIds.length > 0 && (
|
|
||||||
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
|
||||||
<span className="text-blue-700 font-medium mr-2">
|
|
||||||
{selectedProjectIds.length === 1 ? 'Project filter:' : 'Projects filter:'}
|
|
||||||
</span>
|
|
||||||
<div className="flex flex-wrap gap-2">
|
|
||||||
{selectedProjectIds.map(projectId => (
|
|
||||||
<span key={projectId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
|
||||||
{projectId}
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedProjectIds(selectedProjectIds.filter(id => id !== projectId))}
|
|
||||||
className="ml-1 text-blue-600 hover:text-blue-800"
|
|
||||||
>
|
|
||||||
×
|
|
||||||
</button>
|
|
||||||
</span>
|
|
||||||
))}
|
|
||||||
{selectedProjectIds.length > 0 && (
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedProjectIds([])}
|
|
||||||
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
|
||||||
>
|
|
||||||
Clear all
|
|
||||||
</button>
|
|
||||||
)}
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
|
|
||||||
{/* 显示标签选择信息 */}
|
|
||||||
{selectedTagIds.length > 0 && (
|
|
||||||
<div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
|
|
||||||
<span className="text-blue-700 font-medium mr-2">
|
|
||||||
{selectedTagIds.length === 1 ? 'Tag filter:' : 'Tags filter:'}
|
|
||||||
</span>
|
|
||||||
<div className="flex flex-wrap gap-2">
|
|
||||||
{selectedTagIds.map(tagName => (
|
|
||||||
<span key={tagName} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
|
|
||||||
{tagName}
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedTagIds(selectedTagIds.filter(name => name !== tagName))}
|
|
||||||
className="ml-1 text-blue-600 hover:text-blue-800"
|
|
||||||
>
|
|
||||||
×
|
|
||||||
</button>
|
|
||||||
</span>
|
|
||||||
))}
|
|
||||||
{selectedTagIds.length > 0 && (
|
|
||||||
<button
|
|
||||||
onClick={() => setSelectedTagIds([])}
|
|
||||||
className="text-xs text-gray-500 hover:text-gray-700 underline"
|
|
||||||
>
|
|
||||||
Clear all
|
|
||||||
</button>
|
|
||||||
)}
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
|
|
||||||
{/* 仪表板内容 - 现在放在事件列表之后 */}
|
|
||||||
<>
|
|
||||||
{summary && (
|
|
||||||
<div className="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
|
|
||||||
<div className="bg-white rounded-lg shadow p-6">
|
|
||||||
<h3 className="text-sm font-medium text-gray-500">Total Events</h3>
|
|
||||||
<p className="text-2xl font-semibold text-gray-900">
|
|
||||||
{typeof summary.totalEvents === 'number' ? summary.totalEvents.toLocaleString() : summary.totalEvents}
|
|
||||||
</p>
|
|
||||||
</div>
|
|
||||||
<div className="bg-white rounded-lg shadow p-6">
|
|
||||||
<h3 className="text-sm font-medium text-gray-500">Unique Visitors</h3>
|
|
||||||
<p className="text-2xl font-semibold text-gray-900">
|
|
||||||
{typeof summary.uniqueVisitors === 'number' ? summary.uniqueVisitors.toLocaleString() : summary.uniqueVisitors}
|
|
||||||
</p>
|
|
||||||
</div>
|
|
||||||
<div className="bg-white rounded-lg shadow p-6">
|
|
||||||
<h3 className="text-sm font-medium text-gray-500">Total Conversions</h3>
|
|
||||||
<p className="text-2xl font-semibold text-gray-900">
|
|
||||||
{typeof summary.totalConversions === 'number' ? summary.totalConversions.toLocaleString() : summary.totalConversions}
|
|
||||||
</p>
|
|
||||||
</div>
|
|
||||||
<div className="bg-white rounded-lg shadow p-6">
|
|
||||||
<h3 className="text-sm font-medium text-gray-500">Avg. Time Spent</h3>
|
|
||||||
<p className="text-2xl font-semibold text-gray-900">
|
|
||||||
{summary.averageTimeSpent?.toFixed(1) || '0'}s
|
|
||||||
</p>
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
|
|
||||||
<div className="bg-white rounded-lg shadow overflow-hidden mb-8">
|
|
||||||
<div className="p-6 border-b border-gray-200">
|
|
||||||
<h2 className="text-lg font-semibold text-gray-900 mb-4">Recent Events</h2>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
<div className="overflow-x-auto">
|
|
||||||
<table className="min-w-full divide-y divide-gray-200">
|
|
||||||
<thead className="bg-gray-50">
|
|
||||||
<tr>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Time
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Link Name
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Original URL
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Event Type
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Tags
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
User
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Team/Project
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
IP/Location
|
|
||||||
</th>
|
|
||||||
<th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
|
|
||||||
Device Info
|
|
||||||
</th>
|
|
||||||
</tr>
|
|
||||||
</thead>
|
|
||||||
<tbody className="bg-white divide-y divide-gray-200">
|
|
||||||
{events.map((event, index) => {
|
|
||||||
const info = extractEventInfo(event);
|
|
||||||
return (
|
|
||||||
<tr key={event.event_id || index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
{formatDate(info.eventTime)}
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
|
|
||||||
<span className="font-medium">{info.linkName}</span>
|
|
||||||
<div className="text-xs text-gray-500 mt-1 truncate max-w-xs">
|
|
||||||
ID: {event.link_id?.substring(0, 8) || '-'}
|
|
||||||
</div>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
|
|
||||||
<a href={info.originalUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
|
|
||||||
{info.originalUrl}
|
|
||||||
</a>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm">
|
|
||||||
<span className={`px-2 inline-flex text-xs leading-5 font-semibold rounded-full ${
|
|
||||||
info.eventType === 'click'
|
|
||||||
? 'bg-green-100 text-green-800'
|
|
||||||
: 'bg-blue-100 text-blue-800'
|
|
||||||
}`}>
|
|
||||||
{info.eventType}
|
|
||||||
</span>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
<div className="flex flex-wrap gap-1">
|
|
||||||
{info.tags && info.tags.length > 0 ? (
|
|
||||||
info.tags.map((tag, idx) => (
|
|
||||||
<span
|
|
||||||
key={idx}
|
|
||||||
className="bg-gray-100 text-gray-800 text-xs px-2 py-0.5 rounded"
|
|
||||||
>
|
|
||||||
{tag}
|
|
||||||
</span>
|
|
||||||
))
|
|
||||||
) : (
|
|
||||||
<span className="text-gray-400">-</span>
|
|
||||||
)}
|
|
||||||
</div>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
<div className="font-medium">{info.userInfo}</div>
|
|
||||||
<div className="text-xs text-gray-400 mt-1">{info.visitorId}...</div>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
<div className="font-medium">{info.teamName}</div>
|
|
||||||
<div className="text-xs text-gray-400 mt-1">{info.projectName}</div>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
<div className="flex flex-col">
|
|
||||||
<span className="text-xs inline-flex items-center mb-1">
|
|
||||||
<span className="font-medium">IP:</span>
|
|
||||||
<span className="ml-1">{info.ipAddress}</span>
|
|
||||||
</span>
|
|
||||||
<span className="text-xs inline-flex items-center">
|
|
||||||
<span className="font-medium">Location:</span>
|
|
||||||
<span className="ml-1">{info.location}</span>
|
|
||||||
</span>
|
|
||||||
</div>
|
|
||||||
</td>
|
|
||||||
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
|
|
||||||
<div className="flex flex-col">
|
|
||||||
<span className="text-xs inline-flex items-center mb-1">
|
|
||||||
<span className="font-medium">Device:</span>
|
|
||||||
<span className="ml-1">{info.device}</span>
|
|
||||||
</span>
|
|
||||||
<span className="text-xs inline-flex items-center mb-1">
|
|
||||||
<span className="font-medium">Browser:</span>
|
|
||||||
<span className="ml-1">{info.browser}</span>
|
|
||||||
</span>
|
|
||||||
<span className="text-xs inline-flex items-center">
|
|
||||||
<span className="font-medium">OS:</span>
|
|
||||||
<span className="ml-1">{info.os}</span>
|
|
||||||
</span>
|
|
||||||
</div>
|
|
||||||
</td>
|
|
||||||
</tr>
|
|
||||||
);
|
|
||||||
})}
|
|
||||||
</tbody>
|
|
||||||
</table>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
{/* 表格为空状态 */}
|
|
||||||
{!loading && events.length === 0 && (
|
|
||||||
<div className="flex justify-center items-center p-8 text-gray-500">
|
|
||||||
No events found
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
|
|
||||||
{/* 分页控件 - 删除totalEvents > 0条件，改为events.length > 0 */}
|
|
||||||
{!loading && events.length > 0 && (
|
|
||||||
<div className="px-6 py-4 flex items-center justify-between border-t border-gray-200">
|
|
||||||
<div className="flex-1 flex justify-between sm:hidden">
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
|
|
||||||
disabled={currentPage === 1}
|
|
||||||
className={`relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
|
|
||||||
currentPage === 1
|
|
||||||
? 'text-gray-300 bg-gray-50'
|
|
||||||
: 'text-gray-700 bg-white hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
Previous
|
|
||||||
</button>
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
|
|
||||||
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
|
||||||
className={`ml-3 relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
|
|
||||||
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
|
||||||
? 'text-gray-300 cursor-not-allowed'
|
|
||||||
: 'text-gray-700 bg-white hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
Next
|
|
||||||
</button>
|
|
||||||
</div>
|
|
||||||
<div className="hidden sm:flex-1 sm:flex sm:items-center sm:justify-between">
|
|
||||||
<div>
|
|
||||||
<p className="text-sm text-gray-700">
|
|
||||||
Showing <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + 1 : 0}</span> to <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + events.length : 0}</span> of{' '}
|
|
||||||
<span className="font-medium">{totalEvents}</span> results
|
|
||||||
</p>
|
|
||||||
</div>
|
|
||||||
<div className="flex items-center">
|
|
||||||
<div className="mr-4">
|
|
||||||
<select
|
|
||||||
className="px-3 py-1 border border-gray-300 rounded-md text-sm"
|
|
||||||
value={pageSize}
|
|
||||||
onChange={(e) => {
|
|
||||||
setPageSize(Number(e.target.value));
|
|
||||||
setCurrentPage(1); // 重置到第一页
|
|
||||||
}}
|
|
||||||
>
|
|
||||||
<option value="5">5 / page</option>
|
|
||||||
<option value="10">10 / page</option>
|
|
||||||
<option value="20">20 / page</option>
|
|
||||||
<option value="50">50 / page</option>
|
|
||||||
</select>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
{/* 添加直接跳转到指定页的输入框 */}
|
|
||||||
<div className="mr-4 flex items-center">
|
|
||||||
<span className="text-sm text-gray-700 mr-2">Go to:</span>
|
|
||||||
<input
|
|
||||||
type="number"
|
|
||||||
min="1"
|
|
||||||
max={Math.max(1, Math.ceil(totalEvents / pageSize))}
|
|
||||||
value={currentPage}
|
|
||||||
onChange={(e) => {
|
|
||||||
const page = parseInt(e.target.value);
|
|
||||||
if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
|
|
||||||
setCurrentPage(page);
|
|
||||||
}
|
|
||||||
}}
|
|
||||||
onKeyDown={(e) => {
|
|
||||||
if (e.key === 'Enter') {
|
|
||||||
const input = e.target as HTMLInputElement;
|
|
||||||
const page = parseInt(input.value);
|
|
||||||
if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
|
|
||||||
setCurrentPage(page);
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}}
|
|
||||||
className="w-16 px-3 py-1 border border-gray-300 rounded-md text-sm"
|
|
||||||
/>
|
|
||||||
<span className="text-sm text-gray-700 ml-2">
|
|
||||||
of {Math.max(1, Math.ceil(totalEvents / pageSize))}
|
|
||||||
</span>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
<nav className="relative z-0 inline-flex rounded-md shadow-sm -space-x-px" aria-label="Pagination">
|
|
||||||
{/* 首页按钮 */}
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(1)}
|
|
||||||
disabled={currentPage === 1}
|
|
||||||
className={`relative inline-flex items-center px-2 py-2 rounded-l-md border border-gray-300 bg-white text-sm font-medium ${
|
|
||||||
currentPage === 1
|
|
||||||
? 'text-gray-300 cursor-not-allowed'
|
|
||||||
: 'text-gray-500 hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
<span className="sr-only">First</span>
|
|
||||||
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
|
|
||||||
<path fillRule="evenodd" d="M15.707 15.707a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 111.414 1.414L11.414 10l4.293 4.293a1 1 0 010 1.414zm-6 0a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 011.414 1.414L5.414 10l4.293 4.293a1 1 0 010 1.414z" clipRule="evenodd" />
|
|
||||||
</svg>
|
|
||||||
</button>
|
|
||||||
|
|
||||||
{/* 上一页按钮 */}
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
|
|
||||||
disabled={currentPage === 1}
|
|
||||||
className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
|
|
||||||
currentPage === 1
|
|
||||||
? 'text-gray-300 cursor-not-allowed'
|
|
||||||
: 'text-gray-500 hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
<span className="sr-only">Previous</span>
|
|
||||||
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
|
|
||||||
<path fillRule="evenodd" d="M12.707 5.293a1 1 0 010 1.414L9.414 10l3.293 3.293a1 1 0 01-1.414 1.414l-4-4a1 1 0 010-1.414l4-4a1 1 0 011.414 0z" clipRule="evenodd" />
|
|
||||||
</svg>
|
|
||||||
</button>
|
|
||||||
|
|
||||||
{/* 页码按钮 */}
|
|
||||||
{(() => {
|
|
||||||
const totalPages = Math.max(1, Math.ceil(totalEvents / pageSize));
|
|
||||||
const pageNumbers = [];
|
|
||||||
|
|
||||||
// 如果总页数小于等于7，显示所有页码
|
|
||||||
if (totalPages <= 7) {
|
|
||||||
for (let i = 1; i <= totalPages; i++) {
|
|
||||||
pageNumbers.push(i);
|
|
||||||
}
|
|
||||||
} else {
|
|
||||||
// 总是显示首页
|
|
||||||
pageNumbers.push(1);
|
|
||||||
|
|
||||||
// 根据当前页显示中间页码
|
|
||||||
if (currentPage <= 3) {
|
|
||||||
// 当前页靠近开始
|
|
||||||
pageNumbers.push(2, 3, 4);
|
|
||||||
pageNumbers.push('ellipsis1');
|
|
||||||
} else if (currentPage >= totalPages - 2) {
|
|
||||||
// 当前页靠近结束
|
|
||||||
pageNumbers.push('ellipsis1');
|
|
||||||
pageNumbers.push(totalPages - 3, totalPages - 2, totalPages - 1);
|
|
||||||
} else {
|
|
||||||
// 当前页在中间
|
|
||||||
pageNumbers.push('ellipsis1');
|
|
||||||
pageNumbers.push(currentPage - 1, currentPage, currentPage + 1);
|
|
||||||
pageNumbers.push('ellipsis2');
|
|
||||||
}
|
|
||||||
|
|
||||||
// 总是显示尾页
|
|
||||||
pageNumbers.push(totalPages);
|
|
||||||
}
|
|
||||||
|
|
||||||
return pageNumbers.map((pageNum, idx) => {
|
|
||||||
if (pageNum === 'ellipsis1' || pageNum === 'ellipsis2') {
|
|
||||||
return (
|
|
||||||
<div key={`ellipsis-${idx}`} className="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700">
|
|
||||||
...
|
|
||||||
</div>
|
|
||||||
);
|
|
||||||
}
|
|
||||||
|
|
||||||
return (
|
|
||||||
<button
|
|
||||||
key={pageNum}
|
|
||||||
onClick={() => setCurrentPage(Number(pageNum))}
|
|
||||||
className={`relative inline-flex items-center px-4 py-2 border text-sm font-medium ${
|
|
||||||
currentPage === pageNum
|
|
||||||
? 'z-10 bg-blue-50 border-blue-500 text-blue-600'
|
|
||||||
: 'bg-white border-gray-300 text-gray-500 hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
{pageNum}
|
|
||||||
</button>
|
|
||||||
);
|
|
||||||
});
|
|
||||||
})()}
|
|
||||||
|
|
||||||
{/* 下一页按钮 */}
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
|
|
||||||
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
|
||||||
className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
|
|
||||||
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
|
||||||
? 'text-gray-300 cursor-not-allowed'
|
|
||||||
: 'text-gray-500 hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
<span className="sr-only">Next</span>
|
|
||||||
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
|
|
||||||
<path fillRule="evenodd" d="M7.293 14.707a1 1 0 010-1.414L10.586 10 7.293 6.707a1 1 0 011.414-1.414l4 4a1 1 0 010 1.414l-4 4a1 1 0 01-1.414 0z" clipRule="evenodd" />
|
|
||||||
</svg>
|
|
||||||
</button>
|
|
||||||
|
|
||||||
{/* 尾页按钮 */}
|
|
||||||
<button
|
|
||||||
onClick={() => setCurrentPage(Math.ceil(totalEvents / pageSize))}
|
|
||||||
disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
|
|
||||||
className={`relative inline-flex items-center px-2 py-2 rounded-r-md border border-gray-300 bg-white text-sm font-medium ${
|
|
||||||
currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
|
|
||||||
? 'text-gray-300 cursor-not-allowed'
|
|
||||||
: 'text-gray-500 hover:bg-gray-50'
|
|
||||||
}`}
|
|
||||||
>
|
|
||||||
<span className="sr-only">Last</span>
|
|
||||||
<svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
|
|
||||||
<path fillRule="evenodd" d="M4.293 15.707a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L8.586 10 4.293 14.293a1 1 0 000 1.414zm6 0a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L15.586 10l-4.293 4.293a1 1 0 000 1.414z" clipRule="evenodd" />
|
|
||||||
</svg>
|
|
||||||
</button>
|
|
||||||
</nav>
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
)}
|
|
||||||
</div>
|
|
||||||
|
|
||||||
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
|
||||||
<h2 className="text-lg font-semibold text-gray-900 mb-4">Event Trends</h2>
|
|
||||||
<div className="h-96">
|
|
||||||
<TimeSeriesChart data={timeSeriesData} />
|
|
||||||
</div>
|
|
||||||
</div>
|
|
||||||
|
|
||||||
<div className="mb-8">
|
|
||||||
<h2 className="text-lg font-semibold text-gray-900 mb-4">Device Analytics</h2>
|
|
||||||
{deviceData && <DevicePieCharts data={deviceData} />}
|
|
||||||
</div>
|
|
||||||
|
|
||||||
<div className="bg-white rounded-lg shadow p-6 mb-8">
|
|
||||||
<h2 className="text-lg font-semibold text-gray-900 mb-4">Geographic Distribution</h2>
|
|
||||||
<GeoAnalytics
|
|
||||||
data={geoData}
|
|
||||||
onViewModeChange={(mode) => {
|
|
||||||
// 构建查询参数
|
|
||||||
const params = new URLSearchParams({
|
|
||||||
startTime: format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
|
|
||||||
endTime: format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
|
|
||||||
groupBy: mode
|
|
||||||
});
|
|
||||||
|
|
||||||
// 添加其他筛选参数
|
|
||||||
if (selectedTeamIds.length > 0) {
|
|
||||||
selectedTeamIds.forEach(id => params.append('teamId', id));
|
|
||||||
}
|
|
||||||
|
|
||||||
if (selectedProjectIds.length > 0) {
|
|
||||||
selectedProjectIds.forEach(id => params.append('projectId', id));
|
|
||||||
}
|
|
||||||
|
|
||||||
if (selectedTagIds.length > 0) {
|
|
||||||
selectedTagIds.forEach(id => params.append('tagId', id));
|
|
||||||
}
|
|
||||||
|
|
||||||
// 刷新地理位置数据
|
|
||||||
fetch(`/api/events/geo?${params}`)
|
|
||||||
.then(res => res.json())
|
|
||||||
.then(data => {
|
|
||||||
if (data.success) {
|
|
||||||
setGeoData(data.data);
|
|
||||||
}
|
|
||||||
})
|
|
||||||
.catch(error => console.error('Failed to fetch geo data:', error));
|
|
||||||
}}
|
|
||||||
/>
|
|
||||||
</div>
|
|
||||||
</>
|
|
||||||
</div>
|
|
||||||
);
|
|
||||||
}
|
}
|
||||||
29
app/utils/store.ts
Normal file
29
app/utils/store.ts
Normal file
@@ -0,0 +1,29 @@
|
|||||||
|
import { create } from 'zustand';
|
||||||
|
|
||||||
|
// 定义 ShortUrl 数据类型
|
||||||
|
export interface ShortUrlData {
|
||||||
|
id: string;
|
||||||
|
slug: string;
|
||||||
|
originalUrl: string;
|
||||||
|
title?: string;
|
||||||
|
shortUrl: string;
|
||||||
|
teams?: any[];
|
||||||
|
projects?: any[];
|
||||||
|
tags?: any[];
|
||||||
|
createdAt?: string;
|
||||||
|
domain?: string;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 定义 store 类型
|
||||||
|
interface ShortUrlStore {
|
||||||
|
selectedShortUrl: ShortUrlData | null;
|
||||||
|
setSelectedShortUrl: (shortUrl: ShortUrlData | null) => void;
|
||||||
|
clearSelectedShortUrl: () => void;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 创建 store
|
||||||
|
export const useShortUrlStore = create<ShortUrlStore>((set) => ({
|
||||||
|
selectedShortUrl: null,
|
||||||
|
setSelectedShortUrl: (shortUrl) => set({ selectedShortUrl: shortUrl }),
|
||||||
|
clearSelectedShortUrl: () => set({ selectedShortUrl: null }),
|
||||||
|
}));
|
||||||
@@ -37,11 +37,13 @@
|
|||||||
"date-fns": "^4.1.0",
|
"date-fns": "^4.1.0",
|
||||||
"lucide-react": "^0.486.0",
|
"lucide-react": "^0.486.0",
|
||||||
"next": "15.2.3",
|
"next": "15.2.3",
|
||||||
|
"process": "^0.11.10",
|
||||||
"react": "^19.0.0",
|
"react": "^19.0.0",
|
||||||
"react-dom": "^19.0.0",
|
"react-dom": "^19.0.0",
|
||||||
"recharts": "^2.15.1",
|
"recharts": "^2.15.1",
|
||||||
"tailwind-merge": "^3.1.0",
|
"tailwind-merge": "^3.1.0",
|
||||||
"uuid": "^10.0.0"
|
"uuid": "^10.0.0",
|
||||||
|
"zustand": "^5.0.3"
|
||||||
},
|
},
|
||||||
"devDependencies": {
|
"devDependencies": {
|
||||||
"@eslint/eslintrc": "^3",
|
"@eslint/eslintrc": "^3",
|
||||||
|
|||||||
35
pnpm-lock.yaml
generated
35
pnpm-lock.yaml
generated
@@ -50,6 +50,9 @@ importers:
|
|||||||
next:
|
next:
|
||||||
specifier: 15.2.3
|
specifier: 15.2.3
|
||||||
version: 15.2.3(react-dom@19.0.0(react@19.0.0))(react@19.0.0)
|
version: 15.2.3(react-dom@19.0.0(react@19.0.0))(react@19.0.0)
|
||||||
|
process:
|
||||||
|
specifier: ^0.11.10
|
||||||
|
version: 0.11.10
|
||||||
react:
|
react:
|
||||||
specifier: ^19.0.0
|
specifier: ^19.0.0
|
||||||
version: 19.0.0
|
version: 19.0.0
|
||||||
@@ -65,6 +68,9 @@ importers:
|
|||||||
uuid:
|
uuid:
|
||||||
specifier: ^10.0.0
|
specifier: ^10.0.0
|
||||||
version: 10.0.0
|
version: 10.0.0
|
||||||
|
zustand:
|
||||||
|
specifier: ^5.0.3
|
||||||
|
version: 5.0.3(@types/react@19.0.12)(react@19.0.0)
|
||||||
devDependencies:
|
devDependencies:
|
||||||
'@eslint/eslintrc':
|
'@eslint/eslintrc':
|
||||||
specifier: ^3
|
specifier: ^3
|
||||||
@@ -2548,6 +2554,10 @@ packages:
|
|||||||
resolution: {integrity: sha512-vkcDPrRZo1QZLbn5RLGPpg/WmIQ65qoWWhcGKf/b5eplkkarX0m9z8ppCat4mlOqUsWpyNuYgO3VRyrYHSzX5g==}
|
resolution: {integrity: sha512-vkcDPrRZo1QZLbn5RLGPpg/WmIQ65qoWWhcGKf/b5eplkkarX0m9z8ppCat4mlOqUsWpyNuYgO3VRyrYHSzX5g==}
|
||||||
engines: {node: '>= 0.8.0'}
|
engines: {node: '>= 0.8.0'}
|
||||||
|
|
||||||
|
process@0.11.10:
|
||||||
|
resolution: {integrity: sha512-cdGef/drWFoydD1JsMzuFf8100nZl+GT+yacc2bEced5f9Rjk4z+WtFUTBu9PhOi9j/jfmBPu0mMEY4wIdAF8A==}
|
||||||
|
engines: {node: '>= 0.6.0'}
|
||||||
|
|
||||||
prop-types@15.8.1:
|
prop-types@15.8.1:
|
||||||
resolution: {integrity: sha512-oj87CgZICdulUohogVAR7AjlC0327U4el4L6eAvOqCeudMDVU0NThNaV+b9Df4dXgSP1gXMTnPdhfe/2qDH5cg==}
|
resolution: {integrity: sha512-oj87CgZICdulUohogVAR7AjlC0327U4el4L6eAvOqCeudMDVU0NThNaV+b9Df4dXgSP1gXMTnPdhfe/2qDH5cg==}
|
||||||
|
|
||||||
@@ -3035,6 +3045,24 @@ packages:
|
|||||||
resolution: {integrity: sha512-rVksvsnNCdJ/ohGc6xgPwyN8eheCxsiLM8mxuE/t/mOVqJewPuO1miLpTHQiRgTKCLexL4MeAFVagts7HmNZ2Q==}
|
resolution: {integrity: sha512-rVksvsnNCdJ/ohGc6xgPwyN8eheCxsiLM8mxuE/t/mOVqJewPuO1miLpTHQiRgTKCLexL4MeAFVagts7HmNZ2Q==}
|
||||||
engines: {node: '>=10'}
|
engines: {node: '>=10'}
|
||||||
|
|
||||||
|
zustand@5.0.3:
|
||||||
|
resolution: {integrity: sha512-14fwWQtU3pH4dE0dOpdMiWjddcH+QzKIgk1cl8epwSE7yag43k/AD/m4L6+K7DytAOr9gGBe3/EXj9g7cdostg==}
|
||||||
|
engines: {node: '>=12.20.0'}
|
||||||
|
peerDependencies:
|
||||||
|
'@types/react': '>=18.0.0'
|
||||||
|
immer: '>=9.0.6'
|
||||||
|
react: '>=18.0.0'
|
||||||
|
use-sync-external-store: '>=1.2.0'
|
||||||
|
peerDependenciesMeta:
|
||||||
|
'@types/react':
|
||||||
|
optional: true
|
||||||
|
immer:
|
||||||
|
optional: true
|
||||||
|
react:
|
||||||
|
optional: true
|
||||||
|
use-sync-external-store:
|
||||||
|
optional: true
|
||||||
|
|
||||||
snapshots:
|
snapshots:
|
||||||
|
|
||||||
'@alloc/quick-lru@5.2.0': {}
|
'@alloc/quick-lru@5.2.0': {}
|
||||||
@@ -5650,6 +5678,8 @@ snapshots:
|
|||||||
|
|
||||||
prelude-ls@1.2.1: {}
|
prelude-ls@1.2.1: {}
|
||||||
|
|
||||||
|
process@0.11.10: {}
|
||||||
|
|
||||||
prop-types@15.8.1:
|
prop-types@15.8.1:
|
||||||
dependencies:
|
dependencies:
|
||||||
loose-envify: 1.4.0
|
loose-envify: 1.4.0
|
||||||
@@ -6304,3 +6334,8 @@ snapshots:
|
|||||||
ws@8.18.1: {}
|
ws@8.18.1: {}
|
||||||
|
|
||||||
yocto-queue@0.1.0: {}
|
yocto-queue@0.1.0: {}
|
||||||
|
|
||||||
|
zustand@5.0.3(@types/react@19.0.12)(react@19.0.0):
|
||||||
|
optionalDependencies:
|
||||||
|
'@types/react': 19.0.12
|
||||||
|
react: 19.0.0
|
||||||
|
|||||||
@@ -1,225 +0,0 @@
|
|||||||
|
|
||||||
获取所有表...
|
|
||||||
数据库 limq 中找到以下表:
|
|
||||||
- .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb
|
|
||||||
- .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc
|
|
||||||
- .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1
|
|
||||||
- .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0
|
|
||||||
- .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024
|
|
||||||
- .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea
|
|
||||||
- link_daily_stats
|
|
||||||
- link_events
|
|
||||||
- link_hourly_patterns
|
|
||||||
- links
|
|
||||||
- platform_distribution
|
|
||||||
- project_daily_stats
|
|
||||||
- projects
|
|
||||||
- qr_scans
|
|
||||||
- qrcode_daily_stats
|
|
||||||
- qrcodes
|
|
||||||
- sessions
|
|
||||||
- team_daily_stats
|
|
||||||
- team_members
|
|
||||||
- teams
|
|
||||||
|
|
||||||
所有ClickHouse表:
|
|
||||||
.inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb, .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc, .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1, .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0, .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024, .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea, link_daily_stats, link_events, link_hourly_patterns, links, platform_distribution, project_daily_stats, projects, qr_scans, qrcode_daily_stats, qrcodes, sessions, team_daily_stats, team_members, teams
|
|
||||||
|
|
||||||
获取表 .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb 的结构...
|
|
||||||
|
|
||||||
获取表 .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc 的结构...
|
|
||||||
|
|
||||||
获取表 .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1 的结构...
|
|
||||||
|
|
||||||
获取表 .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0 的结构...
|
|
||||||
|
|
||||||
获取表 .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024 的结构...
|
|
||||||
|
|
||||||
获取表 .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea 的结构...
|
|
||||||
|
|
||||||
获取表 link_daily_stats 的结构...
|
|
||||||
表 link_daily_stats 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- total_clicks (UInt64, 无默认值)
|
|
||||||
- unique_visitors (UInt64, 无默认值)
|
|
||||||
- unique_sessions (UInt64, 无默认值)
|
|
||||||
- total_time_spent (UInt64, 无默认值)
|
|
||||||
- avg_time_spent (Float64, 无默认值)
|
|
||||||
- bounce_count (UInt64, 无默认值)
|
|
||||||
- conversion_count (UInt64, 无默认值)
|
|
||||||
- unique_referrers (UInt64, 无默认值)
|
|
||||||
- mobile_count (UInt64, 无默认值)
|
|
||||||
- tablet_count (UInt64, 无默认值)
|
|
||||||
- desktop_count (UInt64, 无默认值)
|
|
||||||
- qr_scan_count (UInt64, 无默认值)
|
|
||||||
- total_conversion_value (Float64, 无默认值)
|
|
||||||
|
|
||||||
获取表 link_events 的结构...
|
|
||||||
表 link_events 的列:
|
|
||||||
- event_id (UUID, 默认值: generateUUIDv4())
|
|
||||||
- event_time (DateTime64(3), 默认值: now64())
|
|
||||||
- date (Date, 默认值: toDate(event_time))
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- channel_id (String, 无默认值)
|
|
||||||
- visitor_id (String, 无默认值)
|
|
||||||
- session_id (String, 无默认值)
|
|
||||||
- event_type (Enum8('click' = 1, 'redirect' = 2, 'conversion' = 3, 'error' = 4), 无默认值)
|
|
||||||
- ip_address (String, 无默认值)
|
|
||||||
- country (String, 无默认值)
|
|
||||||
- city (String, 无默认值)
|
|
||||||
- referrer (String, 无默认值)
|
|
||||||
- utm_source (String, 无默认值)
|
|
||||||
- utm_medium (String, 无默认值)
|
|
||||||
- utm_campaign (String, 无默认值)
|
|
||||||
- user_agent (String, 无默认值)
|
|
||||||
- device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
|
|
||||||
- browser (String, 无默认值)
|
|
||||||
- os (String, 无默认值)
|
|
||||||
- time_spent_sec (UInt32, 默认值: 0)
|
|
||||||
- is_bounce (Bool, 默认值: true)
|
|
||||||
- is_qr_scan (Bool, 默认值: false)
|
|
||||||
- qr_code_id (String, 默认值: '')
|
|
||||||
- conversion_type (Enum8('visit' = 1, 'stay' = 2, 'interact' = 3, 'signup' = 4, 'subscription' = 5, 'purchase' = 6), 默认值: 'visit')
|
|
||||||
- conversion_value (Float64, 默认值: 0)
|
|
||||||
- custom_data (String, 默认值: '{}')
|
|
||||||
|
|
||||||
获取表 link_hourly_patterns 的结构...
|
|
||||||
表 link_hourly_patterns 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- hour (UInt8, 无默认值)
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- visits (UInt64, 无默认值)
|
|
||||||
- unique_visitors (UInt64, 无默认值)
|
|
||||||
|
|
||||||
获取表 links 的结构...
|
|
||||||
表 links 的列:
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- original_url (String, 无默认值)
|
|
||||||
- created_at (DateTime64(3), 无默认值)
|
|
||||||
- created_by (String, 无默认值)
|
|
||||||
- title (String, 无默认值)
|
|
||||||
- description (String, 无默认值)
|
|
||||||
- tags (Array(String), 无默认值)
|
|
||||||
- is_active (Bool, 默认值: true)
|
|
||||||
- expires_at (Nullable(DateTime), 无默认值)
|
|
||||||
- team_id (String, 默认值: '')
|
|
||||||
- project_id (String, 默认值: '')
|
|
||||||
|
|
||||||
获取表 platform_distribution 的结构...
|
|
||||||
表 platform_distribution 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- utm_source (String, 无默认值)
|
|
||||||
- device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
|
|
||||||
- visits (UInt64, 无默认值)
|
|
||||||
- unique_visitors (UInt64, 无默认值)
|
|
||||||
|
|
||||||
获取表 project_daily_stats 的结构...
|
|
||||||
表 project_daily_stats 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- project_id (String, 无默认值)
|
|
||||||
- total_clicks (UInt64, 无默认值)
|
|
||||||
- unique_visitors (UInt64, 无默认值)
|
|
||||||
- conversion_count (UInt64, 无默认值)
|
|
||||||
- links_used (UInt64, 无默认值)
|
|
||||||
- qr_scan_count (UInt64, 无默认值)
|
|
||||||
|
|
||||||
获取表 projects 的结构...
|
|
||||||
表 projects 的列:
|
|
||||||
- project_id (String, 无默认值)
|
|
||||||
- team_id (String, 无默认值)
|
|
||||||
- name (String, 无默认值)
|
|
||||||
- created_at (DateTime, 无默认值)
|
|
||||||
- created_by (String, 无默认值)
|
|
||||||
- description (String, 默认值: '')
|
|
||||||
- is_archived (Bool, 默认值: false)
|
|
||||||
- links_count (UInt32, 默认值: 0)
|
|
||||||
- total_clicks (UInt64, 默认值: 0)
|
|
||||||
- last_updated (DateTime, 默认值: now())
|
|
||||||
|
|
||||||
获取表 qr_scans 的结构...
|
|
||||||
表 qr_scans 的列:
|
|
||||||
- scan_id (UUID, 默认值: generateUUIDv4())
|
|
||||||
- qr_code_id (String, 无默认值)
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- scan_time (DateTime64(3), 无默认值)
|
|
||||||
- visitor_id (String, 无默认值)
|
|
||||||
- location (String, 无默认值)
|
|
||||||
- device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
|
|
||||||
- led_to_conversion (Bool, 默认值: false)
|
|
||||||
|
|
||||||
获取表 qrcode_daily_stats 的结构...
|
|
||||||
表 qrcode_daily_stats 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- qr_code_id (String, 无默认值)
|
|
||||||
- total_scans (UInt64, 无默认值)
|
|
||||||
- unique_scanners (UInt64, 无默认值)
|
|
||||||
- conversions (UInt64, 无默认值)
|
|
||||||
- mobile_scans (UInt64, 无默认值)
|
|
||||||
- tablet_scans (UInt64, 无默认值)
|
|
||||||
- desktop_scans (UInt64, 无默认值)
|
|
||||||
- unique_locations (UInt64, 无默认值)
|
|
||||||
|
|
||||||
获取表 qrcodes 的结构...
|
|
||||||
表 qrcodes 的列:
|
|
||||||
- qr_code_id (String, 无默认值)
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- team_id (String, 无默认值)
|
|
||||||
- project_id (String, 默认值: '')
|
|
||||||
- name (String, 无默认值)
|
|
||||||
- description (String, 默认值: '')
|
|
||||||
- created_at (DateTime, 无默认值)
|
|
||||||
- created_by (String, 无默认值)
|
|
||||||
- updated_at (DateTime, 默认值: now())
|
|
||||||
- qr_type (Enum8('standard' = 1, 'custom' = 2, 'dynamic' = 3), 默认值: 'standard')
|
|
||||||
- image_url (String, 默认值: '')
|
|
||||||
- design_config (String, 默认值: '{}')
|
|
||||||
- is_active (Bool, 默认值: true)
|
|
||||||
- total_scans (UInt64, 默认值: 0)
|
|
||||||
- unique_scanners (UInt32, 默认值: 0)
|
|
||||||
|
|
||||||
获取表 sessions 的结构...
|
|
||||||
表 sessions 的列:
|
|
||||||
- session_id (String, 无默认值)
|
|
||||||
- visitor_id (String, 无默认值)
|
|
||||||
- link_id (String, 无默认值)
|
|
||||||
- started_at (DateTime64(3), 无默认值)
|
|
||||||
- last_activity (DateTime64(3), 无默认值)
|
|
||||||
- ended_at (Nullable(DateTime64(3)), 无默认值)
|
|
||||||
- duration_sec (UInt32, 默认值: 0)
|
|
||||||
- session_pages (UInt8, 默认值: 1)
|
|
||||||
- is_completed (Bool, 默认值: false)
|
|
||||||
|
|
||||||
获取表 team_daily_stats 的结构...
|
|
||||||
表 team_daily_stats 的列:
|
|
||||||
- date (Date, 无默认值)
|
|
||||||
- team_id (String, 无默认值)
|
|
||||||
- total_clicks (UInt64, 无默认值)
|
|
||||||
- unique_visitors (UInt64, 无默认值)
|
|
||||||
- conversion_count (UInt64, 无默认值)
|
|
||||||
- links_used (UInt64, 无默认值)
|
|
||||||
- qr_scan_count (UInt64, 无默认值)
|
|
||||||
|
|
||||||
获取表 team_members 的结构...
|
|
||||||
表 team_members 的列:
|
|
||||||
- team_id (String, 无默认值)
|
|
||||||
- user_id (String, 无默认值)
|
|
||||||
- role (Enum8('owner' = 1, 'admin' = 2, 'editor' = 3, 'viewer' = 4), 无默认值)
|
|
||||||
- joined_at (DateTime, 默认值: now())
|
|
||||||
- invited_by (String, 无默认值)
|
|
||||||
- is_active (Bool, 默认值: true)
|
|
||||||
- last_active (DateTime, 默认值: now())
|
|
||||||
|
|
||||||
获取表 teams 的结构...
|
|
||||||
表 teams 的列:
|
|
||||||
- team_id (String, 无默认值)
|
|
||||||
- name (String, 无默认值)
|
|
||||||
- created_at (DateTime, 无默认值)
|
|
||||||
- created_by (String, 无默认值)
|
|
||||||
- description (String, 默认值: '')
|
|
||||||
- avatar_url (String, 默认值: '')
|
|
||||||
- is_active (Bool, 默认值: true)
|
|
||||||
- plan_type (Enum8('free' = 1, 'pro' = 2, 'enterprise' = 3), 无默认值)
|
|
||||||
- members_count (UInt32, 默认值: 1)
|
|
||||||
|
|
||||||
ClickHouse数据库结构检查完成
|
|
||||||
9
scripts/db/sql/clickhouse/add_req_full_path.sql
Normal file
9
scripts/db/sql/clickhouse/add_req_full_path.sql
Normal file
@@ -0,0 +1,9 @@
|
|||||||
|
-- add_req_full_path.sql
|
||||||
|
-- Add req_full_path column to the shorturl_analytics.events table
|
||||||
|
ALTER TABLE
|
||||||
|
shorturl_analytics.events
|
||||||
|
ADD
|
||||||
|
COLUMN IF NOT EXISTS req_full_path String COMMENT 'Full request path including query parameters';
|
||||||
|
|
||||||
|
-- Display the updated table structure
|
||||||
|
DESCRIBE TABLE shorturl_analytics.events;
|
||||||
41
scripts/db/sql/clickhouse/add_utm_fields.sql
Normal file
41
scripts/db/sql/clickhouse/add_utm_fields.sql
Normal file
@@ -0,0 +1,41 @@
|
|||||||
|
-- 添加缺失的UTM参数字段到shorturl_analytics.events表
|
||||||
|
-- 创建日期: 2024-07-02
|
||||||
|
-- 用途: 增强UTM参数追踪能力
|
||||||
|
-- 添加utm_term字段 (用于跟踪付费搜索关键词)
|
||||||
|
ALTER TABLE
|
||||||
|
shorturl_analytics.events
|
||||||
|
ADD
|
||||||
|
COLUMN utm_term String DEFAULT '' AFTER utm_campaign;
|
||||||
|
|
||||||
|
-- 添加utm_content字段 (用于区分相同广告的不同版本或A/B测试)
|
||||||
|
ALTER TABLE
|
||||||
|
shorturl_analytics.events
|
||||||
|
ADD
|
||||||
|
COLUMN utm_content String DEFAULT '' AFTER utm_term;
|
||||||
|
|
||||||
|
-- 验证字段添加成功
|
||||||
|
DESCRIBE TABLE shorturl_analytics.events;
|
||||||
|
|
||||||
|
-- 示例查询: 查看UTM参数分析数据
|
||||||
|
SELECT
|
||||||
|
utm_source,
|
||||||
|
utm_medium,
|
||||||
|
utm_campaign,
|
||||||
|
utm_term,
|
||||||
|
utm_content,
|
||||||
|
COUNT(*) as clicks
|
||||||
|
FROM
|
||||||
|
shorturl_analytics.events
|
||||||
|
WHERE
|
||||||
|
event_type = 'click'
|
||||||
|
AND utm_source != ''
|
||||||
|
GROUP BY
|
||||||
|
utm_source,
|
||||||
|
utm_medium,
|
||||||
|
utm_campaign,
|
||||||
|
utm_term,
|
||||||
|
utm_content
|
||||||
|
ORDER BY
|
||||||
|
clicks DESC
|
||||||
|
LIMIT
|
||||||
|
10;
|
||||||
46
scripts/db/sql/clickhouse/create_shorturl_table.sql
Normal file
46
scripts/db/sql/clickhouse/create_shorturl_table.sql
Normal file
@@ -0,0 +1,46 @@
|
|||||||
|
-- 使用shorturl_analytics数据库
|
||||||
|
USE shorturl_analytics;
|
||||||
|
|
||||||
|
-- 删除已存在的shorturl表
|
||||||
|
DROP TABLE IF EXISTS shorturl_analytics.shorturl;
|
||||||
|
|
||||||
|
-- 创建shorturl表
|
||||||
|
CREATE TABLE IF NOT EXISTS shorturl_analytics.shorturl (
|
||||||
|
-- 短链接基本信息（来源于resources表）
|
||||||
|
id String COMMENT '资源ID (resources.id)',
|
||||||
|
external_id String COMMENT '外部ID (resources.external_id)',
|
||||||
|
type String COMMENT '类型，值为shorturl',
|
||||||
|
slug String COMMENT '短链接slug (存储在attributes中)',
|
||||||
|
original_url String COMMENT '原始URL (存储在attributes中)',
|
||||||
|
title String COMMENT '标题 (存储在attributes中)',
|
||||||
|
description String COMMENT '描述 (存储在attributes中)',
|
||||||
|
attributes String DEFAULT '{}' COMMENT '资源属性JSON',
|
||||||
|
schema_version Int32 COMMENT 'Schema版本',
|
||||||
|
-- 创建者信息
|
||||||
|
creator_id String COMMENT '创建者ID (resources.creator_id)',
|
||||||
|
creator_email String COMMENT '创建者邮箱 (来自users表)',
|
||||||
|
creator_name String COMMENT '创建者名称 (来自users表)',
|
||||||
|
-- 时间信息
|
||||||
|
created_at DateTime64(3) COMMENT '创建时间 (resources.created_at)',
|
||||||
|
updated_at DateTime64(3) COMMENT '更新时间 (resources.updated_at)',
|
||||||
|
deleted_at Nullable(DateTime64(3)) COMMENT '删除时间 (resources.deleted_at)',
|
||||||
|
-- 项目关联 (project_resources表)
|
||||||
|
projects String DEFAULT '[]' COMMENT '项目关联信息数组。结构: [{project_id: String, project_name: String, project_description: String, assigned_at: DateTime64}]',
|
||||||
|
-- 团队关联 (通过项目关联到团队)
|
||||||
|
teams String DEFAULT '[]' COMMENT '团队关联信息数组。结构: [{team_id: String, team_name: String, team_description: String, via_project_id: String}]',
|
||||||
|
-- 标签关联 (resource_tags表)
|
||||||
|
tags String DEFAULT '[]' COMMENT '标签关联信息数组。结构: [{tag_id: String, tag_name: String, tag_type: String, created_at: DateTime64}]',
|
||||||
|
-- QR码关联 (qr_code表)
|
||||||
|
qr_codes String DEFAULT '[]' COMMENT 'QR码信息数组。结构: [{qr_id: String, scan_count: Int32, url: String, template_name: String, created_at: DateTime64}]',
|
||||||
|
-- 渠道关联 (channel表)
|
||||||
|
channels String DEFAULT '[]' COMMENT '渠道信息数组。结构: [{channel_id: String, channel_name: String, channel_path: String, is_user_created: Boolean}]',
|
||||||
|
-- 收藏关联 (favorite表)
|
||||||
|
favorites String DEFAULT '[]' COMMENT '收藏信息数组。结构: [{favorite_id: String, user_id: String, user_name: String, created_at: DateTime64}]',
|
||||||
|
-- 自定义过期时间 (存储在attributes中)
|
||||||
|
expires_at Nullable(DateTime64(3)) COMMENT '过期时间',
|
||||||
|
-- 统计信息 (分析时聚合计算)
|
||||||
|
click_count UInt32 DEFAULT 0 COMMENT '点击次数',
|
||||||
|
unique_visitors UInt32 DEFAULT 0 COMMENT '唯一访问者数'
|
||||||
|
) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at)
|
||||||
|
ORDER BY
|
||||||
|
(id, created_at) SETTINGS index_granularity = 8192 COMMENT '用于存储所有shorturl类型资源的统一表，集成了相关联的项目、团队、标签、QR码、渠道和收藏信息';
|
||||||
641
windmill/sync_shorturl_to_clickhouse.ts
Normal file
641
windmill/sync_shorturl_to_clickhouse.ts
Normal file
@@ -0,0 +1,641 @@
|
|||||||
|
// Windmill script to sync shorturl data from PostgreSQL to ClickHouse
|
||||||
|
// 作者: AI Assistant
|
||||||
|
// 创建日期: 2023-10-30
|
||||||
|
// 描述: 此脚本从PostgreSQL数据库获取所有shorturl类型的资源及其关联数据，并同步到ClickHouse
|
||||||
|
|
||||||
|
import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
|
||||||
|
import { getResource, getVariable } from "https://deno.land/x/windmill@v1.183.0/mod.ts";
|
||||||
|
|
||||||
|
// 资源属性接口
|
||||||
|
interface ResourceAttributes {
|
||||||
|
slug?: string;
|
||||||
|
original_url?: string;
|
||||||
|
originalUrl?: string;
|
||||||
|
title?: string;
|
||||||
|
description?: string;
|
||||||
|
expires_at?: string;
|
||||||
|
expiresAt?: string;
|
||||||
|
[key: string]: unknown;
|
||||||
|
}
|
||||||
|
|
||||||
|
// ClickHouse配置接口
|
||||||
|
interface ChConfig {
|
||||||
|
clickhouse_host: string;
|
||||||
|
clickhouse_port: number;
|
||||||
|
clickhouse_user: string;
|
||||||
|
clickhouse_password: string;
|
||||||
|
clickhouse_url: string;
|
||||||
|
}
|
||||||
|
|
||||||
|
// PostgreSQL配置接口
|
||||||
|
interface PgConfig {
|
||||||
|
host: string;
|
||||||
|
port: number;
|
||||||
|
user: string;
|
||||||
|
password: string;
|
||||||
|
dbname?: string;
|
||||||
|
[key: string]: unknown;
|
||||||
|
}
|
||||||
|
|
||||||
|
// Windmill函数定义
|
||||||
|
export async function main(
|
||||||
|
/** PostgreSQL和ClickHouse同步脚本 */
|
||||||
|
params: {
|
||||||
|
/** 同步的资源数量限制，默认500 */
|
||||||
|
limit?: number;
|
||||||
|
/** 是否包含已删除资源 */
|
||||||
|
includeDeleted?: boolean;
|
||||||
|
/** 是否执行实际写入操作 */
|
||||||
|
dryRun?: boolean;
|
||||||
|
/** 开始时间（ISO格式）*/
|
||||||
|
startTime?: string;
|
||||||
|
/** 结束时间（ISO格式）*/
|
||||||
|
endTime?: string;
|
||||||
|
}
|
||||||
|
) {
|
||||||
|
// 设置默认参数
|
||||||
|
const limit = params.limit || 500;
|
||||||
|
const includeDeleted = params.includeDeleted || false;
|
||||||
|
const dryRun = params.dryRun || false;
|
||||||
|
const startTime = params.startTime ? new Date(params.startTime) : undefined;
|
||||||
|
const endTime = params.endTime ? new Date(params.endTime) : undefined;
|
||||||
|
|
||||||
|
console.log(`开始同步PostgreSQL shorturl数据到ClickHouse`);
|
||||||
|
console.log(`参数: limit=${limit}, includeDeleted=${includeDeleted}, dryRun=${dryRun}`);
|
||||||
|
if (startTime) console.log(`开始时间: ${startTime.toISOString()}`);
|
||||||
|
if (endTime) console.log(`结束时间: ${endTime.toISOString()}`);
|
||||||
|
|
||||||
|
// 获取数据库配置
|
||||||
|
console.log("获取PostgreSQL数据库配置...");
|
||||||
|
const pgConfig = await getResource('f/limq/postgresql') as PgConfig;
|
||||||
|
console.log(`数据库连接配置: host=${pgConfig.host}, port=${pgConfig.port}, database=${pgConfig.dbname || 'postgres'}, user=${pgConfig.user}`);
|
||||||
|
|
||||||
|
let pgPool: Pool | null = null;
|
||||||
|
|
||||||
|
try {
|
||||||
|
console.log("创建PostgreSQL连接池...");
|
||||||
|
|
||||||
|
pgPool = new Pool({
|
||||||
|
hostname: pgConfig.host,
|
||||||
|
port: pgConfig.port,
|
||||||
|
user: pgConfig.user,
|
||||||
|
password: pgConfig.password,
|
||||||
|
database: pgConfig.dbname || 'postgres'
|
||||||
|
}, 3);
|
||||||
|
|
||||||
|
console.log("PostgreSQL连接池创建完成，尝试连接...");
|
||||||
|
|
||||||
|
// 测试连接
|
||||||
|
const client = await pgPool.connect();
|
||||||
|
try {
|
||||||
|
console.log("连接成功，执行测试查询...");
|
||||||
|
const testResult = await client.queryObject(`SELECT 1 AS test`);
|
||||||
|
console.log(`测试查询结果: ${JSON.stringify(testResult.rows)}`);
|
||||||
|
} finally {
|
||||||
|
client.release();
|
||||||
|
}
|
||||||
|
|
||||||
|
// 获取所有shorturl类型的资源
|
||||||
|
const shorturls = await fetchShorturlResources(pgPool, {
|
||||||
|
limit,
|
||||||
|
includeDeleted,
|
||||||
|
startTime,
|
||||||
|
endTime,
|
||||||
|
});
|
||||||
|
|
||||||
|
console.log(`获取到 ${shorturls.length} 个shorturl资源`);
|
||||||
|
|
||||||
|
if (shorturls.length === 0) {
|
||||||
|
return { synced: 0, message: "没有找到需要同步的shorturl资源" };
|
||||||
|
}
|
||||||
|
|
||||||
|
// 为每个资源获取关联数据
|
||||||
|
const enrichedShorturls = await enrichShorturlData(pgPool, shorturls);
|
||||||
|
console.log(`已丰富 ${enrichedShorturls.length} 个shorturl资源的关联数据`);
|
||||||
|
|
||||||
|
// 转换为ClickHouse格式
|
||||||
|
const clickhouseData = formatForClickhouse(enrichedShorturls);
|
||||||
|
|
||||||
|
if (!dryRun) {
|
||||||
|
// 写入ClickHouse
|
||||||
|
const inserted = await insertToClickhouse(clickhouseData);
|
||||||
|
console.log(`成功写入 ${inserted} 条记录到ClickHouse`);
|
||||||
|
return { synced: inserted, message: "同步完成" };
|
||||||
|
} else {
|
||||||
|
console.log("Dry run模式 - 不执行实际写入");
|
||||||
|
console.log(`将写入 ${clickhouseData.length} 条记录到ClickHouse`);
|
||||||
|
// 输出示例数据
|
||||||
|
if (clickhouseData.length > 0) {
|
||||||
|
console.log("示例数据:");
|
||||||
|
console.log(JSON.stringify(clickhouseData[0], null, 2));
|
||||||
|
}
|
||||||
|
return { synced: 0, dryRun: true, sampleData: clickhouseData.slice(0, 1) };
|
||||||
|
}
|
||||||
|
} catch (error: unknown) {
|
||||||
|
console.error(`同步过程中发生错误: ${(error as Error).message}`);
|
||||||
|
console.error(`错误类型: ${(error as Error).name}`);
|
||||||
|
if ((error as Error).stack) {
|
||||||
|
console.error(`错误堆栈: ${(error as Error).stack}`);
|
||||||
|
}
|
||||||
|
throw error;
|
||||||
|
} finally {
|
||||||
|
if (pgPool) {
|
||||||
|
await pgPool.end();
|
||||||
|
console.log("PostgreSQL连接池已关闭");
|
||||||
|
}
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 从PostgreSQL获取所有shorturl资源
|
||||||
|
async function fetchShorturlResources(
|
||||||
|
pgPool: Pool,
|
||||||
|
options: {
|
||||||
|
limit: number;
|
||||||
|
includeDeleted: boolean;
|
||||||
|
startTime?: Date;
|
||||||
|
endTime?: Date;
|
||||||
|
}
|
||||||
|
) {
|
||||||
|
let query = `
|
||||||
|
SELECT
|
||||||
|
r.id,
|
||||||
|
r.external_id,
|
||||||
|
r.type,
|
||||||
|
r.attributes,
|
||||||
|
r.schema_version,
|
||||||
|
r.creator_id,
|
||||||
|
r.created_at,
|
||||||
|
r.updated_at,
|
||||||
|
r.deleted_at,
|
||||||
|
u.email as creator_email,
|
||||||
|
u.first_name as creator_first_name,
|
||||||
|
u.last_name as creator_last_name
|
||||||
|
FROM
|
||||||
|
limq.resources r
|
||||||
|
LEFT JOIN
|
||||||
|
limq.users u ON r.creator_id = u.id
|
||||||
|
WHERE
|
||||||
|
r.type = 'shorturl'
|
||||||
|
`;
|
||||||
|
|
||||||
|
const params = [];
|
||||||
|
let paramCount = 1;
|
||||||
|
|
||||||
|
if (!options.includeDeleted) {
|
||||||
|
query += ` AND r.deleted_at IS NULL`;
|
||||||
|
}
|
||||||
|
|
||||||
|
if (options.startTime) {
|
||||||
|
query += ` AND r.created_at >= $${paramCount}`;
|
||||||
|
params.push(options.startTime);
|
||||||
|
paramCount++;
|
||||||
|
}
|
||||||
|
|
||||||
|
if (options.endTime) {
|
||||||
|
query += ` AND r.created_at <= $${paramCount}`;
|
||||||
|
params.push(options.endTime);
|
||||||
|
paramCount++;
|
||||||
|
}
|
||||||
|
|
||||||
|
query += ` ORDER BY r.created_at DESC LIMIT $${paramCount}`;
|
||||||
|
params.push(options.limit);
|
||||||
|
|
||||||
|
const client = await pgPool.connect();
|
||||||
|
try {
|
||||||
|
const result = await client.queryObject(query, params);
|
||||||
|
|
||||||
|
// 添加调试日志 - 显示获取的数据样本
|
||||||
|
if (result.rows.length > 0) {
|
||||||
|
console.log(`获取到 ${result.rows.length} 条shorturl记录`);
|
||||||
|
console.log(`第一条记录ID: ${result.rows[0].id}`);
|
||||||
|
console.log(`attributes类型: ${typeof result.rows[0].attributes}`);
|
||||||
|
console.log(`attributes内容示例: ${JSON.stringify(String(result.rows[0].attributes)).substring(0, 100)}...`);
|
||||||
|
}
|
||||||
|
|
||||||
|
return result.rows;
|
||||||
|
} finally {
|
||||||
|
client.release();
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 为每个shorturl资源获取关联数据
|
||||||
|
async function enrichShorturlData(pgPool: Pool, shorturls: Record<string, unknown>[]) {
|
||||||
|
const client = await pgPool.connect();
|
||||||
|
const enriched = [];
|
||||||
|
|
||||||
|
try {
|
||||||
|
for (const shorturl of shorturls) {
|
||||||
|
// 1. 获取项目关联
|
||||||
|
const projectsResult = await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
pr.resource_id, pr.project_id,
|
||||||
|
p.name as project_name, p.description as project_description,
|
||||||
|
pr.assigned_at
|
||||||
|
FROM
|
||||||
|
limq.project_resources pr
|
||||||
|
JOIN
|
||||||
|
limq.projects p ON pr.project_id = p.id
|
||||||
|
WHERE
|
||||||
|
pr.resource_id = $1
|
||||||
|
`, [shorturl.id]);
|
||||||
|
|
||||||
|
// 2. 获取团队关联（通过项目）
|
||||||
|
const teamIds = projectsResult.rows.map((p: Record<string, unknown>) => p.project_id);
|
||||||
|
const teamsResult = teamIds.length > 0 ? await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
tp.team_id, tp.project_id,
|
||||||
|
t.name as team_name, t.description as team_description
|
||||||
|
FROM
|
||||||
|
limq.team_projects tp
|
||||||
|
JOIN
|
||||||
|
limq.teams t ON tp.team_id = t.id
|
||||||
|
WHERE
|
||||||
|
tp.project_id = ANY($1::uuid[])
|
||||||
|
`, [teamIds]) : { rows: [] };
|
||||||
|
|
||||||
|
// 3. 获取标签关联
|
||||||
|
const tagsResult = await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
rt.resource_id, rt.tag_id, rt.created_at,
|
||||||
|
t.name as tag_name, t.type as tag_type
|
||||||
|
FROM
|
||||||
|
limq.resource_tags rt
|
||||||
|
JOIN
|
||||||
|
limq.tags t ON rt.tag_id = t.id
|
||||||
|
WHERE
|
||||||
|
rt.resource_id = $1
|
||||||
|
`, [shorturl.id]);
|
||||||
|
|
||||||
|
// 4. 获取QR码关联
|
||||||
|
const qrCodesResult = await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
id as qr_id, scan_count, url, template_name, created_at
|
||||||
|
FROM
|
||||||
|
limq.qr_code
|
||||||
|
WHERE
|
||||||
|
resource_id = $1
|
||||||
|
`, [shorturl.id]);
|
||||||
|
|
||||||
|
// 5. 获取渠道关联
|
||||||
|
const channelsResult = await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
id as channel_id, name as channel_name, path as channel_path,
|
||||||
|
"isUserCreated" as is_user_created
|
||||||
|
FROM
|
||||||
|
limq.channel
|
||||||
|
WHERE
|
||||||
|
"shortUrlId" = $1
|
||||||
|
`, [shorturl.id]);
|
||||||
|
|
||||||
|
// 6. 获取收藏关联
|
||||||
|
const favoritesResult = await client.queryObject(`
|
||||||
|
SELECT
|
||||||
|
f.id as favorite_id, f.user_id, f.created_at,
|
||||||
|
u.first_name, u.last_name
|
||||||
|
FROM
|
||||||
|
limq.favorite f
|
||||||
|
JOIN
|
||||||
|
limq.users u ON f.user_id = u.id
|
||||||
|
WHERE
|
||||||
|
f.favoritable_id = $1 AND f.favoritable_type = 'resource'
|
||||||
|
`, [shorturl.id]);
|
||||||
|
|
||||||
|
// 调试日志
|
||||||
|
console.log(`\n处理资源ID: ${shorturl.id}`);
|
||||||
|
console.log(`attributes类型: ${typeof shorturl.attributes}`);
|
||||||
|
|
||||||
|
// 改进的attributes解析逻辑
|
||||||
|
let attributes: ResourceAttributes = {};
|
||||||
|
try {
|
||||||
|
if (typeof shorturl.attributes === 'string') {
|
||||||
|
// 如果是字符串，尝试解析为JSON
|
||||||
|
console.log(`尝试解析attributes字符串，长度: ${shorturl.attributes.length}`);
|
||||||
|
attributes = JSON.parse(shorturl.attributes);
|
||||||
|
} else if (typeof shorturl.attributes === 'object' && shorturl.attributes !== null) {
|
||||||
|
// 如果已经是对象，直接使用
|
||||||
|
console.log('attributes已经是对象类型');
|
||||||
|
attributes = shorturl.attributes as ResourceAttributes;
|
||||||
|
} else {
|
||||||
|
console.log(`无效的attributes类型: ${typeof shorturl.attributes}`);
|
||||||
|
attributes = {};
|
||||||
|
}
|
||||||
|
} catch (err) {
|
||||||
|
const error = err as Error;
|
||||||
|
console.warn(`无法解析资源 ${shorturl.id} 的attributes JSON:`, error.message);
|
||||||
|
// 尝试进行更多原始数据分析
|
||||||
|
if (typeof shorturl.attributes === 'string') {
|
||||||
|
console.log(`原始字符串前100字符: ${shorturl.attributes.substring(0, 100)}`);
|
||||||
|
}
|
||||||
|
attributes = {};
|
||||||
|
}
|
||||||
|
|
||||||
|
// 尝试从QR码获取数据
|
||||||
|
let slugFromQr = "";
|
||||||
|
const urlFromQr = "";
|
||||||
|
|
||||||
|
if (qrCodesResult.rows.length > 0 && qrCodesResult.rows[0].url) {
|
||||||
|
const qrUrl = qrCodesResult.rows[0].url as string;
|
||||||
|
console.log(`找到QR码URL: ${qrUrl}`);
|
||||||
|
|
||||||
|
try {
|
||||||
|
const urlParts = qrUrl.split('/');
|
||||||
|
slugFromQr = urlParts[urlParts.length - 1];
|
||||||
|
console.log(`从QR码URL提取的slug: ${slugFromQr}`);
|
||||||
|
} catch (err) {
|
||||||
|
const error = err as Error;
|
||||||
|
console.log('无法从QR码URL提取slug:', error.message);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 日志输出实际字段值
|
||||||
|
console.log(`提取字段 - name: ${attributes.name || 'N/A'}, slug: ${attributes.slug || 'N/A'}`);
|
||||||
|
console.log(`提取字段 - originalUrl: ${attributes.originalUrl || 'N/A'}, original_url: ${attributes.original_url || 'N/A'}`);
|
||||||
|
|
||||||
|
// 整合所有数据
|
||||||
|
const slug = attributes.slug || attributes.name || slugFromQr || "";
|
||||||
|
const originalUrl = attributes.originalUrl || attributes.original_url || urlFromQr || "";
|
||||||
|
|
||||||
|
console.log(`最终使用的slug: ${slug}`);
|
||||||
|
console.log(`最终使用的originalUrl: ${originalUrl}`);
|
||||||
|
|
||||||
|
enriched.push({
|
||||||
|
...shorturl,
|
||||||
|
attributes,
|
||||||
|
projects: projectsResult.rows,
|
||||||
|
teams: teamsResult.rows,
|
||||||
|
tags: tagsResult.rows,
|
||||||
|
qrCodes: qrCodesResult.rows,
|
||||||
|
channels: channelsResult.rows,
|
||||||
|
favorites: favoritesResult.rows,
|
||||||
|
// 从attributes中提取特定字段 - 使用改进的顺序和QR码备选
|
||||||
|
slug,
|
||||||
|
originalUrl,
|
||||||
|
title: attributes.title || "",
|
||||||
|
description: attributes.description || "",
|
||||||
|
expiresAt: attributes.expires_at || attributes.expiresAt || null
|
||||||
|
});
|
||||||
|
}
|
||||||
|
} finally {
|
||||||
|
client.release();
|
||||||
|
}
|
||||||
|
|
||||||
|
return enriched;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 将PostgreSQL数据格式化为ClickHouse格式
|
||||||
|
function formatForClickhouse(shorturls: Record<string, unknown>[]) {
|
||||||
|
// 将日期格式化为ClickHouse兼容的DateTime64(3)格式
|
||||||
|
const formatDateTime = (date: Date | string | number | null | undefined): string | null => {
|
||||||
|
if (!date) return null;
|
||||||
|
// 转换为Date对象
|
||||||
|
const dateObj = date instanceof Date ? date : new Date(date);
|
||||||
|
// 返回格式化的字符串: YYYY-MM-DD HH:MM:SS.SSS
|
||||||
|
return dateObj.toISOString().replace('T', ' ').replace('Z', '');
|
||||||
|
};
|
||||||
|
|
||||||
|
console.log(`\n准备格式化 ${shorturls.length} 条记录为ClickHouse格式`);
|
||||||
|
|
||||||
|
return shorturls.map(shorturl => {
|
||||||
|
// 调试日志：输出关键字段
|
||||||
|
console.log(`处理资源: ${shorturl.id}`);
|
||||||
|
console.log(`slug: ${shorturl.slug || 'EMPTY'}`);
|
||||||
|
console.log(`originalUrl: ${shorturl.originalUrl || 'EMPTY'}`);
|
||||||
|
|
||||||
|
// 记录attributes状态
|
||||||
|
const attributesStr = JSON.stringify(shorturl.attributes || {});
|
||||||
|
const attributesPrev = attributesStr.length > 100 ?
|
||||||
|
attributesStr.substring(0, 100) + '...' :
|
||||||
|
attributesStr;
|
||||||
|
console.log(`attributes: ${attributesPrev}`);
|
||||||
|
|
||||||
|
const creatorName = [shorturl.creator_first_name, shorturl.creator_last_name]
|
||||||
|
.filter(Boolean)
|
||||||
|
.join(" ");
|
||||||
|
|
||||||
|
// 格式化项目数据为JSON数组
|
||||||
|
const projects = JSON.stringify((shorturl.projects as Record<string, unknown>[]).map((p) => ({
|
||||||
|
project_id: p.project_id,
|
||||||
|
project_name: p.project_name,
|
||||||
|
project_description: p.project_description,
|
||||||
|
assigned_at: p.assigned_at
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 格式化团队数据为JSON数组
|
||||||
|
const teams = JSON.stringify((shorturl.teams as Record<string, unknown>[]).map((t) => ({
|
||||||
|
team_id: t.team_id,
|
||||||
|
team_name: t.team_name,
|
||||||
|
team_description: t.team_description,
|
||||||
|
via_project_id: t.project_id
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 格式化标签数据为JSON数组
|
||||||
|
const tags = JSON.stringify((shorturl.tags as Record<string, unknown>[]).map((t) => ({
|
||||||
|
tag_id: t.tag_id,
|
||||||
|
tag_name: t.tag_name,
|
||||||
|
tag_type: t.tag_type,
|
||||||
|
created_at: t.created_at
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 格式化QR码数据为JSON数组
|
||||||
|
const qrCodes = JSON.stringify((shorturl.qrCodes as Record<string, unknown>[]).map((q) => ({
|
||||||
|
qr_id: q.qr_id,
|
||||||
|
scan_count: q.scan_count,
|
||||||
|
url: q.url,
|
||||||
|
template_name: q.template_name,
|
||||||
|
created_at: q.created_at
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 格式化渠道数据为JSON数组
|
||||||
|
const channels = JSON.stringify((shorturl.channels as Record<string, unknown>[]).map((c) => ({
|
||||||
|
channel_id: c.channel_id,
|
||||||
|
channel_name: c.channel_name,
|
||||||
|
channel_path: c.channel_path,
|
||||||
|
is_user_created: c.is_user_created
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 格式化收藏数据为JSON数组
|
||||||
|
const favorites = JSON.stringify((shorturl.favorites as Record<string, unknown>[]).map((f) => ({
|
||||||
|
favorite_id: f.favorite_id,
|
||||||
|
user_id: f.user_id,
|
||||||
|
user_name: `${f.first_name || ""} ${f.last_name || ""}`.trim(),
|
||||||
|
created_at: f.created_at
|
||||||
|
})));
|
||||||
|
|
||||||
|
// 统计信息（可通过events表聚合或在其他地方设置）
|
||||||
|
const clickCount = (shorturl.attributes as ResourceAttributes).click_count as number || 0;
|
||||||
|
const uniqueVisitors = 0;
|
||||||
|
|
||||||
|
// 返回ClickHouse格式数据
|
||||||
|
return {
|
||||||
|
id: shorturl.id,
|
||||||
|
external_id: shorturl.external_id || "",
|
||||||
|
type: shorturl.type,
|
||||||
|
slug: shorturl.slug || "",
|
||||||
|
original_url: shorturl.originalUrl || "",
|
||||||
|
title: shorturl.title || "",
|
||||||
|
description: shorturl.description || "",
|
||||||
|
attributes: JSON.stringify(shorturl.attributes || {}),
|
||||||
|
schema_version: shorturl.schema_version || 1,
|
||||||
|
creator_id: shorturl.creator_id || "",
|
||||||
|
creator_email: shorturl.creator_email || "",
|
||||||
|
creator_name: creatorName,
|
||||||
|
created_at: formatDateTime(shorturl.created_at as Date),
|
||||||
|
updated_at: formatDateTime(shorturl.updated_at as Date),
|
||||||
|
deleted_at: formatDateTime(shorturl.deleted_at as Date | null),
|
||||||
|
projects,
|
||||||
|
teams,
|
||||||
|
tags,
|
||||||
|
qr_codes: qrCodes,
|
||||||
|
channels,
|
||||||
|
favorites,
|
||||||
|
expires_at: formatDateTime(shorturl.expiresAt as Date | null),
|
||||||
|
click_count: clickCount,
|
||||||
|
unique_visitors: uniqueVisitors
|
||||||
|
};
|
||||||
|
});
|
||||||
|
}
|
||||||
|
|
||||||
|
// 获取ClickHouse配置
|
||||||
|
async function getClickHouseConfig(): Promise<ChConfig> {
|
||||||
|
try {
|
||||||
|
// 使用getVariable而不是getResource获取ClickHouse配置
|
||||||
|
const chConfigJson = await getVariable("f/shorturl_analytics/clickhouse");
|
||||||
|
console.log("原始ClickHouse配置:", typeof chConfigJson);
|
||||||
|
|
||||||
|
// 确保配置不为空
|
||||||
|
if (!chConfigJson) {
|
||||||
|
throw new Error("未找到ClickHouse配置");
|
||||||
|
}
|
||||||
|
|
||||||
|
// 解析JSON字符串为对象
|
||||||
|
let chConfig: ChConfig;
|
||||||
|
if (typeof chConfigJson === 'string') {
|
||||||
|
try {
|
||||||
|
chConfig = JSON.parse(chConfigJson);
|
||||||
|
} catch (parseError) {
|
||||||
|
console.error("解析JSON失败:", parseError);
|
||||||
|
throw new Error("ClickHouse配置不是有效的JSON");
|
||||||
|
}
|
||||||
|
} else {
|
||||||
|
chConfig = chConfigJson as ChConfig;
|
||||||
|
}
|
||||||
|
|
||||||
|
// 验证配置
|
||||||
|
if (!chConfig.clickhouse_url && chConfig.clickhouse_host && chConfig.clickhouse_port) {
|
||||||
|
chConfig.clickhouse_url = `http://${chConfig.clickhouse_host}:${chConfig.clickhouse_port}`;
|
||||||
|
console.log(`已构建ClickHouse URL: ${chConfig.clickhouse_url}`);
|
||||||
|
}
|
||||||
|
|
||||||
|
if (!chConfig.clickhouse_url) {
|
||||||
|
throw new Error("ClickHouse配置缺少URL");
|
||||||
|
}
|
||||||
|
|
||||||
|
return chConfig;
|
||||||
|
} catch (error) {
|
||||||
|
console.error("获取ClickHouse配置失败:", error);
|
||||||
|
throw error;
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
// 写入数据到ClickHouse
|
||||||
|
async function insertToClickhouse(data: Record<string, unknown>[]) {
|
||||||
|
if (data.length === 0) return 0;
|
||||||
|
|
||||||
|
// 获取ClickHouse连接信息
|
||||||
|
const chConfig = await getClickHouseConfig();
|
||||||
|
|
||||||
|
// 确保URL有效
|
||||||
|
if (!chConfig.clickhouse_url) {
|
||||||
|
throw new Error("无效的ClickHouse URL: 未定义");
|
||||||
|
}
|
||||||
|
|
||||||
|
console.log(`准备写入数据到ClickHouse: ${chConfig.clickhouse_url}`);
|
||||||
|
|
||||||
|
// 构建INSERT查询
|
||||||
|
const columns = Object.keys(data[0]).join(", ");
|
||||||
|
|
||||||
|
// 收集所有记录的ID
|
||||||
|
const recordIds = data.map(record => record.id as string);
|
||||||
|
console.log(`需要处理的记录数: ${recordIds.length}`);
|
||||||
|
|
||||||
|
// 先删除可能存在的重复记录
|
||||||
|
try {
|
||||||
|
console.log(`删除可能存在的重复记录...`);
|
||||||
|
|
||||||
|
// 按批次处理删除，避免请求过大
|
||||||
|
const deleteBatchSize = 100;
|
||||||
|
for (let i = 0; i < recordIds.length; i += deleteBatchSize) {
|
||||||
|
const idBatch = recordIds.slice(i, i + deleteBatchSize);
|
||||||
|
const formattedIds = idBatch.map(id => `'${id}'`).join(', ');
|
||||||
|
|
||||||
|
const deleteQuery = `
|
||||||
|
ALTER TABLE shorturl_analytics.shorturl
|
||||||
|
DELETE WHERE id IN (${formattedIds})
|
||||||
|
`;
|
||||||
|
|
||||||
|
const response = await fetch(chConfig.clickhouse_url, {
|
||||||
|
method: "POST",
|
||||||
|
headers: {
|
||||||
|
"Content-Type": "application/x-www-form-urlencoded",
|
||||||
|
"Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
|
||||||
|
},
|
||||||
|
body: deleteQuery,
|
||||||
|
});
|
||||||
|
|
||||||
|
if (!response.ok) {
|
||||||
|
const errorText = await response.text();
|
||||||
|
console.warn(`删除记录时出错 (批次 ${i/deleteBatchSize + 1}): ${errorText}`);
|
||||||
|
// 继续执行，不中断流程
|
||||||
|
} else {
|
||||||
|
console.log(`成功删除批次 ${i/deleteBatchSize + 1}/${Math.ceil(recordIds.length/deleteBatchSize)}的潜在重复记录`);
|
||||||
|
}
|
||||||
|
}
|
||||||
|
} catch (error) {
|
||||||
|
console.warn(`删除重复记录时出错: ${(error as Error).message}`);
|
||||||
|
// 继续执行，不因为删除失败而中断整个过程
|
||||||
|
}
|
||||||
|
|
||||||
|
const query = `
|
||||||
|
INSERT INTO shorturl_analytics.shorturl (${columns})
|
||||||
|
FORMAT JSONEachRow
|
||||||
|
`;
|
||||||
|
|
||||||
|
// 批量插入
|
||||||
|
let inserted = 0;
|
||||||
|
const batchSize = 100;
|
||||||
|
|
||||||
|
for (let i = 0; i < data.length; i += batchSize) {
|
||||||
|
const batch = data.slice(i, i + batchSize);
|
||||||
|
|
||||||
|
// 使用JSONEachRow格式
|
||||||
|
const rows = batch.map(row => JSON.stringify(row)).join('\n');
|
||||||
|
|
||||||
|
// 使用HTTP接口执行查询
|
||||||
|
try {
|
||||||
|
console.log(`正在发送请求到: ${chConfig.clickhouse_url}`);
|
||||||
|
console.log(`认证信息: ${chConfig.clickhouse_user}:***`);
|
||||||
|
|
||||||
|
const response = await fetch(chConfig.clickhouse_url, {
|
||||||
|
method: "POST",
|
||||||
|
headers: {
|
||||||
|
"Content-Type": "application/x-www-form-urlencoded",
|
||||||
|
"Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
|
||||||
|
},
|
||||||
|
body: `${query}\n${rows}`,
|
||||||
|
});
|
||||||
|
|
||||||
|
if (!response.ok) {
|
||||||
|
const errorText = await response.text();
|
||||||
|
throw new Error(`ClickHouse插入失败: ${errorText}`);
|
||||||
|
}
|
||||||
|
|
||||||
|
inserted += batch.length;
|
||||||
|
console.log(`已插入 ${inserted}/${data.length} 条记录`);
|
||||||
|
} catch (error) {
|
||||||
|
console.error(`请求ClickHouse时出错:`, error);
|
||||||
|
throw error;
|
||||||
|
}
|
||||||
|
}
|
||||||
|
|
||||||
|
return inserted;
|
||||||
|
}
|
||||||
Reference in New Issue
Block a user