From 2ce5f9dfbb69f0898c42ab862db5ad142fa24943 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Tue, 14 Oct 2025 09:14:10 +0000 Subject: (최겸) 구매 입찰 1회성 품목 기준정보 개발(스키마, 테이블, CRUD, 페이지 등) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/procurement-items/service.ts | 374 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 374 insertions(+) create mode 100644 lib/procurement-items/service.ts (limited to 'lib/procurement-items/service.ts') diff --git a/lib/procurement-items/service.ts b/lib/procurement-items/service.ts new file mode 100644 index 00000000..ee6df959 --- /dev/null +++ b/lib/procurement-items/service.ts @@ -0,0 +1,374 @@ +// lib/procurement/items/service.ts +"use server" + +import { revalidateTag, unstable_noStore } from "next/cache" +import db from "@/db/db" + +import { filterColumns } from "@/lib/filter-columns" +import { unstable_cache } from "@/lib/unstable-cache" +import { getErrorMessage } from "@/lib/handle-error" + +import { asc, desc, ilike, and, or, eq } from "drizzle-orm" +import { GetProcurementItemsSchema, UpdateProcurementItemSchema, createProcurementItemSchema } from "./validations" +import { ProcurementItem, procurementItems } from "@/db/schema/items" +import { + countProcurementItems, + deleteProcurementItemById, + deleteProcurementItemsByIds, + findAllProcurementItems, + insertProcurementItem, + selectProcurementItems, + updateProcurementItem +} from "./repository" + +/* ----------------------------------------------------- + 1) 조회 관련 +----------------------------------------------------- */ + +/** + * 복잡한 조건으로 품목 목록을 조회 (+ pagination) 하고, + * 총 개수에 따라 pageCount를 계산해서 리턴. + * Next.js의 unstable_cache를 사용해 일정 시간 캐시. + */ +export async function getProcurementItems(input: GetProcurementItemsSchema) { + const safePerPage = Math.min(input.perPage, 100) + + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * safePerPage + + const advancedWhere = filterColumns({ + table: procurementItems, + filters: input.filters, + joinOperator: input.joinOperator, + }) + + let globalWhere + if (input.search) { + const s = `%${input.search}%` + globalWhere = or( + ilike(procurementItems.itemCode, s), + ilike(procurementItems.itemName, s), + ilike(procurementItems.material, s), + ilike(procurementItems.specification, s), + ilike(procurementItems.unit, s), + ) + } + + const finalWhere = and(advancedWhere, globalWhere) + + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => + item.desc ? desc(procurementItems[item.id]) : asc(procurementItems[item.id]) + ) + : [asc(procurementItems.createdAt)] + + const { data, total } = await db.transaction(async (tx) => { + const data = await selectProcurementItems(tx, { + where: finalWhere, + orderBy, + offset, + limit: safePerPage, + }) + + const total = await countProcurementItems(tx, finalWhere) + return { data, total } + }) + + const pageCount = Math.ceil(total / safePerPage) + return { data, pageCount } + } catch (err) { + console.error(err) + return { data: [], pageCount: 0 } + } + }, + [JSON.stringify({...input, perPage: safePerPage})], + { + revalidate: 3600, + tags: ["procurement-items"], + } + )() +} + +/* ----------------------------------------------------- + 2) 생성(Create) +----------------------------------------------------- */ + +export interface ProcurementItemCreateData { + itemCode: string + itemName: string + material?: string | null + specification?: string | null + unit?: string | null + isActive?: string | null + createdBy?: string | null +} + +/** + * 품목 생성 + */ +export async function createProcurementItem(input: ProcurementItemCreateData) { + unstable_noStore() + + try { + if (!input.itemCode || !input.itemName) { + return { + success: false, + message: "품목코드와 품목명은 필수입니다", + data: null, + error: "필수 필드 누락" + } + } + + let result: any[] = [] + + result = await db.transaction(async (tx) => { + // 기존 품목 확인 (itemCode는 unique) + const existingItem = await tx.query.procurementItems.findFirst({ + where: eq(procurementItems.itemCode, input.itemCode), + }) + + let txResult + if (existingItem) { + // 기존 품목 업데이트 + txResult = await updateProcurementItem(tx, existingItem.id, { + itemName: input.itemName, + material: input.material, + specification: input.specification, + unit: input.unit, + isActive: input.isActive, + createdBy: input.createdBy, + }) + } else { + // 새 품목 생성 + txResult = await insertProcurementItem(tx, { + itemCode: input.itemCode, + itemName: input.itemName, + material: input.material, + specification: input.specification, + unit: input.unit, + isActive: input.isActive || 'Y', + createdBy: input.createdBy, + }) + } + + return txResult + }) + + // 캐시 무효화 + revalidateTag("procurement-items") + + return { + success: true, + data: result[0] || null, + error: null + } + } catch (err) { + console.error("품목 생성/업데이트 오류:", err) + + // 중복 키 오류 처리 + if (err instanceof Error && err.message.includes("unique constraint")) { + return { + success: false, + message: "이미 존재하는 품목코드입니다", + data: null, + error: "중복 키 오류" + } + } + + return { + success: false, + message: getErrorMessage(err), + data: null, + error: getErrorMessage(err) + } + } +} + +/* ----------------------------------------------------- + 3) 업데이트 +----------------------------------------------------- */ + +/** 단건 업데이트 */ +export async function modifyProcurementItem(input: UpdateProcurementItemSchema & { id: number }) { + unstable_noStore() + try { + await db.transaction(async (tx) => { + await updateProcurementItem(tx, input.id, { + itemCode: input.itemCode, + itemName: input.itemName, + material: input.material, + specification: input.specification, + unit: input.unit, + isActive: input.isActive, + }) + }) + + revalidateTag("procurement-items") + return { data: null, error: null } + } catch (err) { + return { data: null, error: getErrorMessage(err) } + } +} + +/** 단건 삭제 */ +export async function removeProcurementItem(input: { id: number }) { + unstable_noStore() + try { + await db.transaction(async (tx) => { + await deleteProcurementItemById(tx, input.id) + }) + + revalidateTag("procurement-items") + return { data: null, error: null } + } catch (err) { + return { data: null, error: getErrorMessage(err) } + } +} + +/** 복수 삭제 */ +export async function removeProcurementItems(input: { ids: number[] }) { + unstable_noStore() + try { + await db.transaction(async (tx) => { + await deleteProcurementItemsByIds(tx, input.ids) + }) + + revalidateTag("procurement-items") + return { data: null, error: null } + } catch (err) { + return { data: null, error: getErrorMessage(err) } + } +} + +export async function getAllProcurementItems(): Promise { + try { + return await findAllProcurementItems() + } catch (err) { + throw new Error("Failed to get procurement items") + } +} + +// 품목 검색 함수 +export async function searchProcurementItems(query: string): Promise<{ itemCode: string; itemName: string }[]> { + unstable_noStore() + + try { + if (!query || query.trim().length < 1) { + return [] + } + + const searchQuery = `%${query.trim()}%` + + const results = await db + .select({ + itemCode: procurementItems.itemCode, + itemName: procurementItems.itemName, + }) + .from(procurementItems) + .where( + and( + or( + ilike(procurementItems.itemCode, searchQuery), + ilike(procurementItems.itemName, searchQuery) + ), + // 활성화된 품목만 + eq(procurementItems.isActive, 'Y') + ) + ) + .limit(20) + .orderBy(asc(procurementItems.itemCode)) + + return results + } catch (err) { + console.error("품목 검색 오류:", err) + return [] + } +} + +/* ----------------------------------------------------- + 4) Excel Import +----------------------------------------------------- */ + +export interface ImportResult { + success: boolean + importedCount: number + errorCount: number + errors: string[] + message: string +} + +/** + * Excel 파일에서 품목 데이터 대량 가져오기 + */ +export async function importProcurementItemsFromExcel(excelData: any[]): Promise { + unstable_noStore() + + try { + if (!Array.isArray(excelData)) { + return { + success: false, + importedCount: 0, + errorCount: 0, + errors: ["데이터 배열이 필요합니다."], + message: "데이터 배열이 필요합니다." + } + } + + let importedCount = 0 + let errorCount = 0 + const errors: string[] = [] + + // 배치 처리 (한 번에 너무 많은 데이터를 처리하지 않도록) + const batchSize = 50 + for (let i = 0; i < excelData.length; i += batchSize) { + const batch = excelData.slice(i, i + batchSize) + + for (const itemData of batch) { + try { + // 데이터 검증 + const validatedData = createProcurementItemSchema.parse(itemData) + + // 품목 생성 또는 업데이트 + const result = await createProcurementItem({ + itemCode: validatedData.itemCode, + itemName: validatedData.itemName, + material: validatedData.material || null, + specification: validatedData.specification || null, + unit: validatedData.unit || null, + isActive: validatedData.isActive || 'Y', + }) + + if (result.success) { + importedCount++ + } else { + errorCount++ + errors.push(`${validatedData.itemCode}: ${result.error}`) + } + } catch (validationError) { + errorCount++ + errors.push(`${itemData.itemCode || '알 수 없음'}: 검증 오류`) + } + } + } + + return { + success: true, + importedCount, + errorCount, + errors: errors.slice(0, 10), // 너무 많은 오류 메시지를 보내지 않도록 제한 + message: `${importedCount}개 품목이 성공적으로 가져오기를 완료했습니다.${errorCount > 0 ? ` ${errorCount}개 오류 발생.` : ''}` + } + + } catch (error) { + console.error("품목 가져오기 오류:", error) + return { + success: false, + importedCount: 0, + errorCount: 0, + errors: ["서버 내부 오류가 발생했습니다."], + message: "서버 내부 오류가 발생했습니다." + } + } +} -- cgit v1.2.3