1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
import db from "@/db/db"
import { loginSessions, users } from "@/db/schema"
import { and, or, ilike, eq, desc, asc, count, sql } from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns";
import type { GetLoginSessionsSchema, ExtendedLoginSession } from "./validation"
export async function getLoginSessions(input: GetLoginSessionsSchema) {
try {
const offset = (input.page - 1) * input.perPage;
const advancedTable = true;
// 고급 필터 처리
const advancedWhere = filterColumns({
table: loginSessions,
filters: input.filters,
joinOperator: input.joinOperator,
});
// 전역 검색
let globalWhere;
if (input.search) {
const s = `%${input.search}%`;
globalWhere = or(
ilike(users.email, s),
ilike(users.name, s),
ilike(loginSessions.authMethod, s),
ilike(loginSessions.ipAddress, 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(loginSessions[item.id as keyof typeof loginSessions.$inferSelect])
: asc(loginSessions[item.id as keyof typeof loginSessions.$inferSelect]);
}
})
: [desc(loginSessions.loginAt)];
// 데이터 조회
const data = await db
.select({
id: loginSessions.id,
userId: loginSessions.userId,
loginAt: loginSessions.loginAt,
logoutAt: loginSessions.logoutAt,
lastActivityAt: loginSessions.lastActivityAt,
ipAddress: loginSessions.ipAddress,
userAgent: loginSessions.userAgent,
authMethod: loginSessions.authMethod,
isActive: loginSessions.isActive,
sessionExpiredAt: loginSessions.sessionExpiredAt,
createdAt: loginSessions.createdAt,
userEmail: users.email,
userName: users.name,
// 세션 지속 시간 계산 (분 단위)
sessionDuration: sql<number>`
CASE
WHEN ${loginSessions.logoutAt} IS NOT NULL THEN
EXTRACT(EPOCH FROM (${loginSessions.logoutAt} - ${loginSessions.loginAt})) / 60
WHEN ${loginSessions.isActive} = true THEN
EXTRACT(EPOCH FROM (${loginSessions.lastActivityAt} - ${loginSessions.loginAt})) / 60
ELSE NULL
END
`,
// 현재 활성 여부
isCurrentlyActive: sql<boolean>`
CASE
WHEN ${loginSessions.isActive} = true
AND (${loginSessions.sessionExpiredAt} IS NULL
OR ${loginSessions.sessionExpiredAt} > NOW())
THEN true
ELSE false
END
`
})
.from(loginSessions)
.innerJoin(users, eq(loginSessions.userId, users.id))
.where(finalWhere)
.orderBy(...orderBy)
.limit(input.perPage)
.offset(offset);
// 총 개수 조회
const totalResult = await db
.select({ count: count() })
.from(loginSessions)
.innerJoin(users, eq(loginSessions.userId, users.id))
.where(finalWhere);
const total = totalResult[0]?.count || 0;
const pageCount = Math.ceil(total / input.perPage);
return { data: data as ExtendedLoginSession[], pageCount };
} catch (err) {
console.error("Failed to fetch login sessions:", err);
return { data: [], pageCount: 0 };
}
}
|
