(최겸) 정보시스템 공지사항 개발

1 files changed, 244 insertions, 0 deletions

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<typeof and>

+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]

+    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<typeof and>

+) {

+  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<Array<Notice & { authorName: string | null; authorEmail: string | 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(and(

+      eq(notice.pagePath, pagePath),

+      eq(notice.isActive, true)

+    ))

+    .orderBy(desc(notice.createdAt))

+

+  return result

+}

+

+// 공지사항 생성

+export async function insertNotice(data: NewNotice): Promise<Notice> {

+  const result = await db

+    .insert(notice)

+    .values(data)

+    .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,

+      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