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
|
import { desc, eq, and, sql } from "drizzle-orm"
import db from "@/db/db"
import { notice, users, type Notice, type NewNotice } from "@/db/schema"
// 페이지 경로별 공지사항 조회 (활성화된 것만, 작성자 정보 포함, 유효기간 내 공지사항만)
export async function getNoticesByPagePath(pagePath: string): Promise<Array<Notice & { authorName: string | null; authorEmail: string | null }>> {
const currentTime = new Date()
const result = await db
.select({
id: notice.id,
pagePath: notice.pagePath,
title: notice.title,
content: notice.content,
authorId: notice.authorId,
isActive: notice.isActive,
isPopup: notice.isPopup,
startAt: notice.startAt,
endAt: notice.endAt,
dontShowDuration: notice.dontShowDuration,
createdAt: notice.createdAt,
updatedAt: notice.updatedAt,
authorName: users.name,
authorEmail: users.email,
})
.from(notice)
.leftJoin(users, eq(notice.authorId, users.id))
.where(and(
eq(notice.pagePath, pagePath),
eq(notice.isActive, true),
// // 유효기간 필터링: startAt과 endAt이 모두 null이거나 현재 시간이 범위 내에 있어야 함
// sql`(${notice.startAt} IS NULL OR ${notice.startAt} <= ${currentTime})`,
// sql`(${notice.endAt} IS NULL OR ${notice.endAt} >= ${currentTime})`
))
.orderBy(desc(notice.createdAt))
return result
}
// 공지사항 생성
export async function insertNotice(data: NewNotice): Promise<Notice> {
const result = await db
.insert(notice)
.values({
...data,
createdAt: new Date(),
updatedAt: new Date()
})
.returning()
return result[0]
}
// 공지사항 수정
export async function updateNotice(id: number, data: Partial<NewNotice>): Promise<Notice | null> {
const result = await db
.update(notice)
.set({ ...data, updatedAt: new Date() })
.where(eq(notice.id, id))
.returning()
return result[0] || null
}
// 공지사항 삭제
export async function deleteNoticeById(id: number): Promise<boolean> {
const result = await db
.delete(notice)
.where(eq(notice.id, id))
return (result.rowCount ?? 0) > 0
}
// 공지사항 다중 삭제
export async function deleteNoticeByIds(ids: number[]): Promise<number> {
const result = await db
.delete(notice)
.where(sql`${notice.id} = ANY(${ids})`)
return result.rowCount ?? 0
}
// ID로 공지사항 조회 (작성자 정보 포함)
export async function getNoticeById(id: number): Promise<(Notice & { authorName: string | null; authorEmail: string | null }) | null> {
const result = await db
.select({
id: notice.id,
pagePath: notice.pagePath,
title: notice.title,
content: notice.content,
authorId: notice.authorId,
isActive: notice.isActive,
isPopup: notice.isPopup,
startAt: notice.startAt,
endAt: notice.endAt,
dontShowDuration: notice.dontShowDuration,
createdAt: notice.createdAt,
updatedAt: notice.updatedAt,
authorName: users.name,
authorEmail: users.email,
})
.from(notice)
.leftJoin(users, eq(notice.authorId, users.id))
.where(eq(notice.id, id))
.limit(1)
return result[0] || null
}
|
