From 795b4915069c44f500a91638e16ded67b9e16618 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Tue, 1 Jul 2025 11:46:33 +0000 Subject: (최겸) 정보시스템 공지사항 개발 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/notice/repository.ts | 244 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 244 insertions(+) create mode 100644 lib/notice/repository.ts (limited to 'lib/notice/repository.ts') diff --git a/lib/notice/repository.ts b/lib/notice/repository.ts new file mode 100644 index 00000000..84e64f00 --- /dev/null +++ b/lib/notice/repository.ts @@ -0,0 +1,244 @@ +import { asc, desc, eq, ilike, and, count, sql } from "drizzle-orm" +import db from "@/db/db" +import { notice, users, type Notice, type NewNotice } from "@/db/schema" + +// 최신 패턴: 트랜잭션을 지원하는 공지사항 조회 +export async function selectNoticeLists( + tx: typeof db, + params: { + where?: ReturnType + orderBy?: (ReturnType | ReturnType)[] + offset?: number + limit?: number + } +) { + const { where, orderBy, offset = 0, limit = 10 } = params + + return tx + .select({ + id: notice.id, + pagePath: notice.pagePath, + title: notice.title, + content: notice.content, + authorId: notice.authorId, + isActive: notice.isActive, + createdAt: notice.createdAt, + updatedAt: notice.updatedAt, + authorName: users.name, + authorEmail: users.email, + }) + .from(notice) + .leftJoin(users, eq(notice.authorId, users.id)) + .where(where) + .orderBy(...(orderBy ?? [desc(notice.createdAt)])) + .offset(offset) + .limit(limit) +} + +// 최신 패턴: 트랜잭션을 지원하는 카운트 조회 +export async function countNoticeLists( + tx: typeof db, + where?: ReturnType +) { + const res = await tx + .select({ count: count() }) + .from(notice) + .where(where) + + return res[0]?.count ?? 0 +} + +// 기존 패턴 (하위 호환성을 위해 유지) +export async function selectNotice(input: { page: number; per_page: number; sort?: string; pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }) { + const { page, per_page = 50, sort, pagePath, title, authorId, isActive, from, to } = input + + const conditions = [] + + if (pagePath) { + conditions.push(ilike(notice.pagePath, `%${pagePath}%`)) + } + + if (title) { + conditions.push(ilike(notice.title, `%${title}%`)) + } + + if (authorId) { + conditions.push(eq(notice.authorId, authorId)) + } + + if (isActive !== null && isActive !== undefined) { + conditions.push(eq(notice.isActive, isActive)) + } + + if (from) { + conditions.push(sql`${notice.createdAt} >= ${from}`) + } + + if (to) { + conditions.push(sql`${notice.createdAt} <= ${to}`) + } + + const offset = (page - 1) * per_page + + // 정렬 설정 + let orderBy = desc(notice.createdAt); + + if (sort && Array.isArray(sort) && sort.length > 0) { + const sortItem = sort[0]; + if (sortItem.id === "createdAt") { + orderBy = sortItem.desc ? desc(notice.createdAt) : asc(notice.createdAt); + } + } + + const whereClause = conditions.length > 0 ? and(...conditions) : undefined + + const data = await db + .select({ + id: notice.id, + pagePath: notice.pagePath, + title: notice.title, + content: notice.content, + authorId: notice.authorId, + isActive: notice.isActive, + createdAt: notice.createdAt, + updatedAt: notice.updatedAt, + authorName: users.name, + authorEmail: users.email, + }) + .from(notice) + .leftJoin(users, eq(notice.authorId, users.id)) + .where(whereClause) + .orderBy(orderBy) + .limit(per_page) + .offset(offset) + + return data +} + +// 기존 패턴: 공지사항 총 개수 조회 +export async function countNotice(input: { pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }) { + const { pagePath, title, authorId, isActive, from, to } = input + + const conditions = [] + + if (pagePath) { + conditions.push(ilike(notice.pagePath, `%${pagePath}%`)) + } + + if (title) { + conditions.push(ilike(notice.title, `%${title}%`)) + } + + if (authorId) { + conditions.push(eq(notice.authorId, authorId)) + } + + if (isActive !== null && isActive !== undefined) { + conditions.push(eq(notice.isActive, isActive)) + } + + if (from) { + conditions.push(sql`${notice.createdAt} >= ${from}`) + } + + if (to) { + conditions.push(sql`${notice.createdAt} <= ${to}`) + } + + const whereClause = conditions.length > 0 ? and(...conditions) : undefined + + const result = await db + .select({ count: count() }) + .from(notice) + .where(whereClause) + + return result[0]?.count ?? 0 +} + +// 페이지 경로별 공지사항 조회 (활성화된 것만, 작성자 정보 포함) +export async function getNoticesByPagePath(pagePath: string): Promise> { + const result = await db + .select({ + id: notice.id, + pagePath: notice.pagePath, + title: notice.title, + content: notice.content, + authorId: notice.authorId, + isActive: notice.isActive, + 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) + )) + .orderBy(desc(notice.createdAt)) + + return result +} + +// 공지사항 생성 +export async function insertNotice(data: NewNotice): Promise { + const result = await db + .insert(notice) + .values(data) + .returning() + + return result[0] +} + +// 공지사항 수정 +export async function updateNotice(id: number, data: Partial): Promise { + 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 { + const result = await db + .delete(notice) + .where(eq(notice.id, id)) + + return (result.rowCount ?? 0) > 0 +} + +// 공지사항 다중 삭제 +export async function deleteNoticeByIds(ids: number[]): Promise { + 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, + 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 +} \ No newline at end of file -- cgit v1.2.3