(대표님) 20250619 1844 KST 작업사항

1 files changed, 233 insertions, 0 deletions

diff --git a/lib/esg-check-list/table/excel-actions.tsx b/lib/esg-check-list/table/excel-actions.tsx
new file mode 100644
index 00000000..7f38b600
--- /dev/null
+++ b/lib/esg-check-list/table/excel-actions.tsx
@@ -0,0 +1,233 @@
+// @/lib/esg-check-list/excel-actions.ts
+"use server"
+
+import db  from "@/db/db"
+import { 
+  esgEvaluations, 
+  esgEvaluationItems, 
+  esgAnswerOptions 
+} from "@/db/schema"
+import { eq, inArray } from "drizzle-orm"
+import type { ParsedExcelData } from "./excel-utils"
+
+export interface ImportOptions {
+  skipDuplicates?: boolean // 중복 시 스킵
+  updateExisting?: boolean // 기존 데이터 업데이트
+}
+
+export interface ImportResult {
+  success: boolean
+  message: string
+  details: {
+    evaluationsCreated: number
+    evaluationsUpdated: number
+    evaluationsSkipped: number
+    itemsCreated: number
+    optionsCreated: number
+    errors: string[]
+  }
+}
+
+export async function importEsgDataFromExcel(
+  data: ParsedExcelData,
+  options: ImportOptions = {}
+): Promise<ImportResult> {
+  const result: ImportResult = {
+    success: false,
+    message: '',
+    details: {
+      evaluationsCreated: 0,
+      evaluationsUpdated: 0,
+      evaluationsSkipped: 0,
+      itemsCreated: 0,
+      optionsCreated: 0,
+      errors: []
+    }
+  }
+
+  try {
+    await db.transaction(async (tx) => {
+      // 1. 기존 평가표 확인
+      const existingSerials = data.evaluations.map(e => e.serialNumber)
+      const existingEvaluations = await tx
+        .select()
+        .from(esgEvaluations)
+        .where(inArray(esgEvaluations.serialNumber, existingSerials))
+
+      const existingSerialNumbers = existingEvaluations.map(e => e.serialNumber)
+
+      // 2. 평가표 처리
+      for (const evaluation of data.evaluations) {
+        const exists = existingSerialNumbers.includes(evaluation.serialNumber)
+
+        if (exists) {
+          if (options.skipDuplicates) {
+            result.details.evaluationsSkipped++
+            continue
+          } else if (options.updateExisting) {
+            // 기존 데이터 업데이트
+            await tx
+              .update(esgEvaluations)
+              .set({
+                category: evaluation.category,
+                inspectionItem: evaluation.inspectionItem,
+                updatedAt: new Date(),
+              })
+              .where(eq(esgEvaluations.serialNumber, evaluation.serialNumber))
+
+            // 기존 평가항목과 답변옵션 삭제
+            const existingEvaluation = existingEvaluations.find(e => e.serialNumber === evaluation.serialNumber)
+            if (existingEvaluation) {
+              await tx
+                .delete(esgEvaluationItems)
+                .where(eq(esgEvaluationItems.esgEvaluationId, existingEvaluation.id))
+              // 답변옵션은 CASCADE DELETE로 자동 삭제됨
+            }
+
+            result.details.evaluationsUpdated++
+          } else {
+            result.details.errors.push(`시리얼번호 '${evaluation.serialNumber}'가 이미 존재합니다.`)
+            continue
+          }
+        } else {
+          // 새 평가표 생성
+          await tx
+            .insert(esgEvaluations)
+            .values({
+              serialNumber: evaluation.serialNumber,
+              category: evaluation.category,
+              inspectionItem: evaluation.inspectionItem,
+            })
+
+          result.details.evaluationsCreated++
+        }
+      }
+
+      // 3. 최신 평가표 목록 다시 조회 (새로 생성되거나 업데이트된 것들 포함)
+      const currentEvaluations = await tx
+        .select()
+        .from(esgEvaluations)
+        .where(inArray(esgEvaluations.serialNumber, existingSerials))
+
+      const evaluationMap = new Map(
+        currentEvaluations.map(e => [e.serialNumber, e.id])
+      )
+
+      // 4. 평가항목 처리
+      const itemsToInsert = []
+      for (const item of data.evaluationItems) {
+        const evaluationId = evaluationMap.get(item.serialNumber)
+        
+        if (!evaluationId) {
+          result.details.errors.push(
+            `평가항목의 시리얼번호 '${item.serialNumber}'에 해당하는 평가표를 찾을 수 없습니다.`
+          )
+          continue
+        }
+
+        itemsToInsert.push({
+          esgEvaluationId: evaluationId,
+          evaluationItem: item.evaluationItem,
+          evaluationItemDescription: item.evaluationItemDescription,
+          orderIndex: item.orderIndex,
+        })
+      }
+
+      if (itemsToInsert.length > 0) {
+        const insertedItems = await tx
+          .insert(esgEvaluationItems)
+          .values(itemsToInsert)
+          .returning()
+
+        result.details.itemsCreated = insertedItems.length
+
+        // 5. 답변옵션 처리
+        const itemMap = new Map()
+        for (const insertedItem of insertedItems) {
+          const originalItem = itemsToInsert.find(
+            item => item.esgEvaluationId === insertedItem.esgEvaluationId && 
+                    item.evaluationItem === insertedItem.evaluationItem
+          )
+          if (originalItem) {
+            const evaluation = currentEvaluations.find(e => e.id === originalItem.esgEvaluationId)
+            if (evaluation) {
+              const key = `${evaluation.serialNumber}:${originalItem.evaluationItem}`
+              itemMap.set(key, insertedItem.id)
+            }
+          }
+        }
+
+        const optionsToInsert = []
+        for (const option of data.answerOptions) {
+          const key = `${option.serialNumber}:${option.evaluationItem}`
+          const itemId = itemMap.get(key)
+          
+          if (!itemId) {
+            result.details.errors.push(
+              `답변옵션의 평가항목 '${option.evaluationItem}'을 찾을 수 없습니다.`
+            )
+            continue
+          }
+
+          optionsToInsert.push({
+            esgEvaluationItemId: itemId,
+            answerText: option.answerText,
+            score: option.score.toString(),
+            orderIndex: option.orderIndex,
+          })
+        }
+
+        if (optionsToInsert.length > 0) {
+          const insertedOptions = await tx
+            .insert(esgAnswerOptions)
+            .values(optionsToInsert)
+            .returning()
+
+          result.details.optionsCreated = insertedOptions.length
+        }
+      }
+    })
+
+    // 결과 메시지 생성
+    const { details } = result
+    const totalProcessed = details.evaluationsCreated + details.evaluationsUpdated + details.evaluationsSkipped
+
+    if (details.errors.length === 0) {
+      result.success = true
+      result.message = `성공적으로 처리되었습니다. 평가표 ${totalProcessed}개 (생성: ${details.evaluationsCreated}, 업데이트: ${details.evaluationsUpdated}, 스킵: ${details.evaluationsSkipped}), 평가항목 ${details.itemsCreated}개, 답변옵션 ${details.optionsCreated}개`
+    } else if (details.evaluationsCreated > 0 || details.evaluationsUpdated > 0) {
+      result.success = true
+      result.message = `부분적으로 성공했습니다. ${details.errors.length}개의 오류가 있었습니다.`
+    } else {
+      result.success = false
+      result.message = `임포트에 실패했습니다. ${details.errors.length}개의 오류가 발생했습니다.`
+    }
+
+
+    return result
+
+  } catch (error) {
+    console.error('Excel import error:', error)
+    
+    result.success = false
+    result.message = error instanceof Error ? error.message : '알 수 없는 오류가 발생했습니다.'
+    result.details.errors.push(result.message)
+
+    return result
+  }
+}
+
+// 중복 확인 함수
+export async function checkDuplicateSerials(serialNumbers: string[]) {
+  try {
+    const existing = await db
+      .select({ serialNumber: esgEvaluations.serialNumber })
+      .from(esgEvaluations)
+      .where(inArray(esgEvaluations.serialNumber, serialNumbers))
+
+    return existing.map(e => e.serialNumber)
+  } catch (error) {
+    console.error('Error checking duplicates:', error)
+    return []
+  }
+}
\ No newline at end of file