diff options
Diffstat (limited to 'lib/page-visits/service.ts')
| -rw-r--r-- | lib/page-visits/service.ts | 169 |
1 files changed, 169 insertions, 0 deletions
diff --git a/lib/page-visits/service.ts b/lib/page-visits/service.ts new file mode 100644 index 00000000..66c57eaa --- /dev/null +++ b/lib/page-visits/service.ts @@ -0,0 +1,169 @@ +import db from "@/db/db" +import { loginSessions, pageVisits, users } from "@/db/schema" +import { and, or, ilike, eq, desc, asc, count, sql, isNull } from "drizzle-orm" +import { filterColumns } from "@/lib/filter-columns"; +import type { GetPageVisitsSchema, ExtendedPageVisit } from "./validation" + +export async function getPageVisits(input: GetPageVisitsSchema) { + try { + const offset = (input.page - 1) * input.perPage; + const advancedTable = true; + + // 고급 필터 처리 + const advancedWhere = filterColumns({ + table: pageVisits, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 전역 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(pageVisits.route, s), + ilike(pageVisits.pageTitle, s), + ilike(pageVisits.referrer, s), + ilike(pageVisits.deviceType, s), + ilike(pageVisits.browserName, s), + ilike(pageVisits.osName, s), + ilike(users.email, s), + ilike(users.name, s) + ); + } + + // 조건 결합 + const conditions = []; + if (advancedWhere) conditions.push(advancedWhere); + if (globalWhere) conditions.push(globalWhere); + + let finalWhere; + if (conditions.length > 0) { + finalWhere = conditions.length > 1 ? and(...conditions) : conditions[0]; + } + + // 정렬 처리 + const orderBy = + input.sort.length > 0 + ? input.sort.map((item) => { + // 사용자 관련 필드 정렬 + if (item.id === 'userEmail') { + return item.desc ? desc(users.email) : asc(users.email); + } else if (item.id === 'userName') { + return item.desc ? desc(users.name) : asc(users.name); + } else { + // 페이지 방문 필드 정렬 + return item.desc + ? desc(pageVisits[item.id as keyof typeof pageVisits.$inferSelect]) + : asc(pageVisits[item.id as keyof typeof pageVisits.$inferSelect]); + } + }) + : [desc(pageVisits.visitedAt)]; + + // 데이터 조회 + const data = await db + .select({ + id: pageVisits.id, + userId: pageVisits.userId, + sessionId: pageVisits.sessionId, + route: pageVisits.route, + pageTitle: pageVisits.pageTitle, + referrer: pageVisits.referrer, + ipAddress: pageVisits.ipAddress, + userAgent: pageVisits.userAgent, + visitedAt: pageVisits.visitedAt, + duration: pageVisits.duration, + queryParams: pageVisits.queryParams, + deviceType: pageVisits.deviceType, + browserName: pageVisits.browserName, + osName: pageVisits.osName, + userEmail: users.email, + userName: users.name, + // 지속 시간 포맷팅 + durationFormatted: sql<string>` + CASE + WHEN ${pageVisits.duration} IS NULL THEN NULL + WHEN ${pageVisits.duration} < 60 THEN CONCAT(${pageVisits.duration}, '초') + WHEN ${pageVisits.duration} < 3600 THEN CONCAT(FLOOR(${pageVisits.duration} / 60), '분 ', ${pageVisits.duration} % 60, '초') + ELSE CONCAT(FLOOR(${pageVisits.duration} / 3600), '시간 ', FLOOR((${pageVisits.duration} % 3600) / 60), '분') + END + `, + // 장기 체류 여부 (5분 이상) + isLongVisit: sql<boolean>`${pageVisits.duration} >= 300` + }) + .from(pageVisits) + .leftJoin(users, eq(pageVisits.userId, users.id)) + .where(finalWhere) + .orderBy(...orderBy) + .limit(input.perPage) + .offset(offset); + + // 총 개수 조회 + const totalResult = await db + .select({ count: count() }) + .from(pageVisits) + .leftJoin(users, eq(pageVisits.userId, users.id)) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + const pageCount = Math.ceil(total / input.perPage); + + return { data: data as ExtendedPageVisit[], pageCount }; + } catch (err) { + console.error("Failed to fetch page visits:", err); + return { data: [], pageCount: 0 }; + } +} + + +export async function getUserActivitySummary(userId: string, startDate: Date, endDate: Date) { + try { + // 페이지 방문 통계 + const pageStats = await db + .select({ + route: pageVisits.route, + visitCount: count(), + totalDuration: sql<number>`SUM(${pageVisits.duration})`, + avgDuration: sql<number>`AVG(${pageVisits.duration})`, + }) + .from(pageVisits) + .where( + and( + eq(pageVisits.userId, userId), + between(pageVisits.visitedAt, startDate, endDate) + ) + ) + .groupBy(pageVisits.route) + .orderBy(desc(count())); + + // 세션 통계 + const sessionStats = await db + .select({ + sessionCount: count(), + totalSessionTime: sql<number>` + SUM(EXTRACT(EPOCH FROM ( + COALESCE(${loginSessions.logoutAt}, ${loginSessions.lastActivityAt}) + - ${loginSessions.loginAt} + )) / 60) + `, + }) + .from(loginSessions) + .where( + and( + eq(loginSessions.userId, userId), + between(loginSessions.loginAt, startDate, endDate) + ) + ); + + return { + pageStats, + sessionStats: sessionStats[0] || { sessionCount: 0, totalSessionTime: 0 }, + }; + } catch (error) { + console.error("Failed to get user activity summary:", error); + return { + pageStats: [], + sessionStats: { sessionCount: 0, totalSessionTime: 0 }, + }; + } +}
\ No newline at end of file |