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 ++++++++++++++++++++++++++++++++++ lib/notice/service.ts | 324 ++++++++++++++++++++++++++++++++++++++++++++++ lib/notice/validations.ts | 80 ++++++++++++ 3 files changed, 648 insertions(+) create mode 100644 lib/notice/repository.ts create mode 100644 lib/notice/service.ts create mode 100644 lib/notice/validations.ts (limited to 'lib/notice') 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 diff --git a/lib/notice/service.ts b/lib/notice/service.ts new file mode 100644 index 00000000..24b03fe9 --- /dev/null +++ b/lib/notice/service.ts @@ -0,0 +1,324 @@ +"use server" + +import { revalidateTag, unstable_noStore } from "next/cache" +import { getErrorMessage } from "@/lib/handle-error" +import { unstable_cache } from "@/lib/unstable-cache" +import { filterColumns } from "@/lib/filter-columns" +import { asc, desc, ilike, and, or, eq } from "drizzle-orm" +import db from "@/db/db" +import { notice, pageInformation } from "@/db/schema" + +import type { + CreateNoticeSchema, + UpdateNoticeSchema, + GetNoticeSchema +} from "./validations" + +import { + selectNotice, + countNotice, + getNoticesByPagePath, + insertNotice, + updateNotice, + deleteNoticeById, + deleteNoticeByIds, + getNoticeById, + selectNoticeLists, + countNoticeLists +} from "./repository" + +import type { Notice } from "@/db/schema/notice" + +export async function getNoticeLists(input: GetNoticeSchema) { + return unstable_cache( + async () => { + try { + // 고급 검색 로직 + const { page, perPage, search, filters, joinOperator, pagePath, title, content, authorId, isActive } = input + + // 기본 검색 조건들 + const conditions = [] + + // 검색어가 있으면 여러 필드에서 검색 + if (search && search.trim()) { + const searchConditions = [ + ilike(notice.pagePath, `%${search}%`), + ilike(notice.title, `%${search}%`), + ilike(notice.content, `%${search}%`) + ] + conditions.push(or(...searchConditions)) + } + + // 개별 필드 조건들 + if (pagePath && pagePath.trim()) { + conditions.push(ilike(notice.pagePath, `%${pagePath}%`)) + } + + if (title && title.trim()) { + conditions.push(ilike(notice.title, `%${title}%`)) + } + + if (content && content.trim()) { + conditions.push(ilike(notice.content, `%${content}%`)) + } + + if (authorId !== null && authorId !== undefined) { + conditions.push(eq(notice.authorId, authorId)) + } + + if (isActive !== null && isActive !== undefined) { + conditions.push(eq(notice.isActive, isActive)) + } + // 고급 필터 처리 + if (filters && filters.length > 0) { + const advancedConditions = filters.map(() => + filterColumns({ + table: notice, + filters: filters, + joinOperator: joinOperator, + }) + ) + + if (advancedConditions.length > 0) { + if (joinOperator === "or") { + conditions.push(or(...advancedConditions)) + } else { + conditions.push(and(...advancedConditions)) + } + } + } + + // 전체 WHERE 조건 조합 + const finalWhere = conditions.length > 0 + ? (joinOperator === "or" ? or(...conditions) : and(...conditions)) + : undefined + + // 페이지네이션 + const offset = (page - 1) * perPage + + // 정렬 처리 + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + if (item.id === "createdAt") { + return item.desc ? desc(notice.createdAt) : asc(notice.createdAt) + } else if (item.id === "updatedAt") { + return item.desc ? desc(notice.updatedAt) : asc(notice.updatedAt) + } else if (item.id === "pagePath") { + return item.desc ? desc(notice.pagePath) : asc(notice.pagePath) + } else if (item.id === "title") { + return item.desc ? desc(notice.title) : asc(notice.title) + } else if (item.id === "authorId") { + return item.desc ? desc(notice.authorId) : asc(notice.authorId) + } else if (item.id === "isActive") { + return item.desc ? desc(notice.isActive) : asc(notice.isActive) + } else { + return desc(notice.createdAt) // 기본값 + } + }) + : [desc(notice.createdAt)] + + // 트랜잭션 내부에서 Repository 호출 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectNoticeLists(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }) + + const total = await countNoticeLists(tx, finalWhere) + return { data, total } + }) + + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount, total } + } catch (err) { + console.error("Failed to get notice lists:", err) + // 에러 발생 시 기본값 반환 + return { data: [], pageCount: 0, total: 0 } + } + }, + [JSON.stringify(input)], + { + revalidate: 3600, + tags: ["notice-lists"], + } + )() +} + +// 기존 패턴 (하위 호환성을 위해 유지) +export async function getNoticeList(input: Partial<{ page: number; per_page: number; sort?: string; pagePath?: string; title?: string; authorId?: number; isActive?: boolean; from?: string; to?: string }> & { page: number; per_page: number }) { + unstable_noStore() + + try { + const [data, total] = await Promise.all([ + selectNotice(input), + countNotice(input) + ]) + + const pageCount = Math.ceil(total / input.per_page) + + return { + data, + pageCount, + total + } + } catch (error) { + console.error("Failed to get notice list:", error) + throw new Error(getErrorMessage(error)) + } +} + +// 페이지별 공지사항 조회 (일반 사용자용) +export async function getPageNotices(pagePath: string): Promise> { + try { + return await getNoticesByPagePath(pagePath) + } catch (error) { + console.error(`Failed to get notices for page ${pagePath}:`, error) + return [] + } +} + +// 캐시된 페이지별 공지사항 조회 +export const getCachedPageNotices = unstable_cache( + async (pagePath: string) => getPageNotices(pagePath), + ["page-notices"], + { + tags: ["page-notices"], + revalidate: 3600, // 1시간 캐시 + } +) + +// 공지사항 생성 +export async function createNotice(input: CreateNoticeSchema) { + try { + const result = await insertNotice(input) + + revalidateTag("page-notices") + revalidateTag("notice-lists") + + return { + success: true, + data: result, + message: "공지사항이 성공적으로 생성되었습니다." + } + } catch (error) { + console.error("Failed to create notice:", error) + return { + success: false, + message: getErrorMessage(error) + } + } +} + +// 공지사항 수정 +export async function updateNoticeData(input: UpdateNoticeSchema) { + try { + const { id, ...updateData } = input + const result = await updateNotice(id, updateData) + + if (!result) { + return { + success: false, + message: "공지사항을 찾을 수 없거나 수정에 실패했습니다." + } + } + + revalidateTag("page-notices") + revalidateTag("notice-lists") + + return { + success: true, + message: "공지사항이 성공적으로 수정되었습니다." + } + } catch (error) { + console.error("Failed to update notice:", error) + return { + success: false, + message: getErrorMessage(error) + } + } +} + +// 공지사항 삭제 +export async function deleteNotice(id: number) { + try { + const success = await deleteNoticeById(id) + + if (!success) { + return { + success: false, + message: "공지사항을 찾을 수 없거나 삭제에 실패했습니다." + } + } + + revalidateTag("page-notices") + revalidateTag("notice-lists") + + return { + success: true, + message: "공지사항이 성공적으로 삭제되었습니다." + } + } catch (error) { + console.error("Failed to delete notice:", error) + return { + success: false, + message: getErrorMessage(error) + } + } +} + +// 공지사항 다중 삭제 +export async function deleteMultipleNotices(ids: number[]) { + try { + const deletedCount = await deleteNoticeByIds(ids) + + revalidateTag("page-notices") + revalidateTag("notice-lists") + + return { + success: true, + deletedCount, + message: `${deletedCount}개의 공지사항이 성공적으로 삭제되었습니다.` + } + } catch (error) { + console.error("Failed to delete multiple notices:", error) + return { + success: false, + message: getErrorMessage(error) + } + } +} + +// ID로 공지사항 조회 +export async function getNoticeDetail(id: number): Promise<(Notice & { authorName: string | null; authorEmail: string | null }) | null> { + try { + return await getNoticeById(id) + } catch (error) { + console.error(`Failed to get notice detail for id ${id}:`, error) + return null + } +} + +// pagePath 목록 조회 (정보 시스템에서 사용) +export async function getPagePathList(): Promise> { + try { + const result = await db + .selectDistinct({ + pagePath: pageInformation.pagePath, + pageName: pageInformation.pageName + }) + .from(pageInformation) + .where(eq(pageInformation.isActive, true)) + .orderBy(asc(pageInformation.pagePath)) + + return result.map(item => ({ + pagePath: item.pagePath, + pageName: item.pageName || item.pagePath + })) + } catch (error) { + console.error("Failed to get page path list:", error) + return [] + } +} \ No newline at end of file diff --git a/lib/notice/validations.ts b/lib/notice/validations.ts new file mode 100644 index 00000000..05e84af9 --- /dev/null +++ b/lib/notice/validations.ts @@ -0,0 +1,80 @@ +import { z } from "zod" +import { + createSearchParamsCache, + parseAsArrayOf, + parseAsInteger, + parseAsString, + parseAsStringEnum, + parseAsBoolean, +} from "nuqs/server" +import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers" +import { Notice } from "@/db/schema/notice" + +// 공지사항 생성 스키마 +export const createNoticeSchema = z.object({ + pagePath: z.string().min(1, "페이지 경로를 입력해주세요"), + title: z.string().min(1, "제목을 입력해주세요"), + content: z.string().min(1, "내용을 입력해주세요"), + authorId: z.number().min(1, "작성자를 선택해주세요"), + isActive: z.boolean().default(true), +}) + +// 공지사항 수정 스키마 +export const updateNoticeSchema = z.object({ + id: z.number(), + pagePath: z.string().min(1, "페이지 경로를 입력해주세요"), + title: z.string().min(1, "제목을 입력해주세요"), + content: z.string().min(1, "내용을 입력해주세요"), + isActive: z.boolean().default(true), +}) + +// 현대적인 검색 파라미터 캐시 +export const searchParamsNoticeCache = createSearchParamsCache({ + flags: parseAsArrayOf(z.enum(["advancedTable", "floatingBar"])).withDefault([]), + page: parseAsInteger.withDefault(1), + perPage: parseAsInteger.withDefault(10), + sort: getSortingStateParser().withDefault([ + { id: "createdAt", desc: true }, + ]), + + // 기본 검색 필드들 + pagePath: parseAsString.withDefault(""), + title: parseAsString.withDefault(""), + content: parseAsString.withDefault(""), + authorId: parseAsInteger, + isActive: parseAsBoolean, + + // 고급 필터 + filters: getFiltersStateParser().withDefault([]), + joinOperator: parseAsStringEnum(["and", "or"]).withDefault("and"), + search: parseAsString.withDefault(""), + + // 날짜 범위 + from: parseAsString.withDefault(""), + to: parseAsString.withDefault(""), +}) + +// 타입 추출 +export type CreateNoticeSchema = z.infer +export type UpdateNoticeSchema = z.infer +export type GetNoticeSchema = Awaited> + +// 기존 스키마 (하위 호환성을 위해 유지) +export const getNoticeSchema = z.object({ + page: z.coerce.number().default(1), + per_page: z.coerce.number().default(10), + sort: z.string().optional(), + pagePath: z.string().optional(), + title: z.string().optional(), + authorId: z.coerce.number().optional(), + isActive: z.coerce.boolean().optional(), + from: z.string().optional(), + to: z.string().optional(), +}) + +// 페이지 경로별 공지사항 조회 스키마 +export const getPageNoticeSchema = z.object({ + pagePath: z.string().min(1, "페이지 경로를 입력해주세요"), +}) + +export type GetPageNoticeSchema = z.infer \ No newline at end of file -- cgit v1.2.3