(최겸) 구매 입찰 1회성 품목 기준정보 개발(스키마, 테이블, CRUD, 페이지 등)

1 files changed, 374 insertions, 0 deletions

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<ProcurementItem[]> {

+  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<ImportResult> {

+  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: "서버 내부 오류가 발생했습니다."

+    }

+  }

+}