From 534266f90a5ca846767dc2a990c77f1112a33d9c Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Mon, 15 Sep 2025 10:36:26 +0000
Subject: (임수민) serp 구현
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 lib/s-erp-import/actions.ts | 181 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 181 insertions(+)
 create mode 100644 lib/s-erp-import/actions.ts

(limited to 'lib/s-erp-import')

diff --git a/lib/s-erp-import/actions.ts b/lib/s-erp-import/actions.ts
new file mode 100644
index 00000000..d4d2e1ec
--- /dev/null
+++ b/lib/s-erp-import/actions.ts
@@ -0,0 +1,181 @@
+"use server"
+
+import db from '@/db/db'
+import * as schema from '@/db/schema'
+import { revalidatePath } from 'next/cache'
+import ExcelJS from 'exceljs'
+import { sql } from 'drizzle-orm'
+
+export async function getSapTableCounts(tableNames: string[]) {
+  const result: Record<string, number> = {}
+  for (const name of tableNames) {
+    const table = (schema as any)[camelFromTable(name)]
+    if (!table) {
+      result[name] = 0
+      continue
+    }
+    const rows = await db.select({ c: sql<number>`count(*)` }).from(table)
+    result[name] = Number(rows?.[0]?.c ?? 0)
+  }
+  return result
+}
+
+export async function exportTemplate(tableName: string): Promise<Buffer> {
+  const table = (schema as any)[camelFromTable(tableName)]
+  if (!table) throw new Error(`Unknown table: ${tableName}`)
+
+  const workbook = new ExcelJS.Workbook()
+  const sheet = workbook.addWorksheet('template')
+
+  const columns = Object.keys(table)
+    .filter((k) => !k.startsWith('_') && k !== 'getSQL' && k !== '[$$schema]')
+    .filter((k) => k !== 'id')
+
+  sheet.addRow(columns)
+  const buf = await workbook.xlsx.writeBuffer()
+  return Buffer.from(buf)
+}
+
+export async function importExcel(tableName: string, file: File) {
+  const table = (schema as any)[camelFromTable(tableName)]
+  if (!table) throw new Error(`Unknown table: ${tableName}`)
+
+  const workbook = new ExcelJS.Workbook()
+  const arrayBuffer = await file.arrayBuffer()
+  await workbook.xlsx.load(arrayBuffer)
+  const sheet = workbook.worksheets[0]
+  const rawHeader = (sheet.getRow(1).values as any[]).slice(1) as string[]
+
+  // 1) 스키마 컬럼 수집 및 정규화 맵 구성
+  const schemaKeys = Object.keys(table)
+    .filter((k) => !k.startsWith('_') && k !== 'getSQL' && k !== '[$$schema]' && k !== 'id')
+  const normalize = (s: string) => (s || '')
+    .replace(/\./g, '')
+    .replace(/\s+/g, '')
+    .replace(/[\-/]/g, '')
+    .toLowerCase()
+  const normalizedToSchemaKey = new Map<string, string>()
+  for (const key of schemaKeys) normalizedToSchemaKey.set(normalize(key), key)
+
+  // 2) 엑셀 헤더 정규화 후 스키마 매핑
+  const mappedHeader: (string | null)[] = rawHeader.map((h) => {
+    const key = normalizedToSchemaKey.get(normalize(String(h)))
+    return key ?? null
+  })
+
+  const unknownHeaders = rawHeader
+    .map((h, idx) => ({ h, idx }))
+    .filter(({ idx }) => mappedHeader[idx] === null)
+    .map(({ h }) => String(h))
+
+  const mappedColumns = mappedHeader.filter(Boolean) as string[]
+  if (mappedColumns.length === 0) {
+    return {
+      success: false,
+      message: `헤더가 테이블 컬럼과 일치하지 않습니다. unknownHeaders=${JSON.stringify(unknownHeaders)}`,
+    }
+  }
+
+  const rows: any[] = []
+  const rowNumbers: number[] = []
+  sheet.eachRow((row, rowNumber) => {
+    if (rowNumber === 1) return
+    const values = (row.values as any[]).slice(1)
+    const obj: Record<string, any> = {}
+    mappedHeader.forEach((schemaKey, idx) => {
+      if (!schemaKey) return
+      const v = values[idx]
+      obj[schemaKey] = normalizeCellValue(schemaKey, v)
+    })
+    // 전체가 비어있는 행은 제외
+    if (Object.values(obj).some((v) => v !== null && v !== '')) {
+      rows.push(obj)
+      rowNumbers.push(rowNumber)
+    }
+  })
+
+  if (rows.length === 0) {
+    return {
+      success: false,
+      message: `데이터가 없습니다. 또는 모든 행이 빈 값이었습니다. unknownHeaders=${JSON.stringify(unknownHeaders)}`,
+    }
+  }
+
+  // 3) 사전 검증: 문자열 길이 제한 초과 등
+  const columnMeta: Record<string, { maxLength?: number; dataType?: string }> = {}
+  for (const key of schemaKeys) {
+    const col: any = (table as any)[key]
+    const maxLength = col?.config?.length ?? col?._?.config?.length
+    const dataType = col?.dataType ?? col?.columnType ?? col?._?.dataType
+    columnMeta[key] = { maxLength, dataType }
+  }
+
+  const validationErrors: Array<{ row: number; column: string; reason: string; value?: any; length?: number; max?: number }> = []
+  rows.forEach((r, idx) => {
+    for (const [k, v] of Object.entries(r)) {
+      const meta = columnMeta[k] || {}
+      if (v == null) continue
+      const s = String(v)
+      if (meta.maxLength && s.length > meta.maxLength) {
+        validationErrors.push({ row: rowNumbers[idx], column: k, reason: 'length_exceeded', value: s, length: s.length, max: meta.maxLength })
+      }
+    }
+  })
+
+  if (validationErrors.length > 0) {
+    return { success: false, message: '유효성 검사 실패 (길이 초과 등)', unknownHeaders, errors: validationErrors }
+  }
+
+  try {
+    // @ts-expect-error drizzle insert
+    await db.insert(table).values(rows)
+    revalidatePath('/sap-import')
+    return { success: true, inserted: rows.length, unknownHeaders }
+  } catch (e: any) {
+    return { success: false, message: e?.message ?? 'DB 입력 실패', unknownHeaders }
+  }
+}
+
+function camelFromTable(name: string) {
+  // TB_SAP_MaterialInfo -> tbSAPMaterialInfo (export된 심볼명)
+  const parts = name.split('_')
+  const camel = parts
+    .map((p, i) => (i === 0 ? p.toLowerCase() : p[0] + p.slice(1)))
+    .join('')
+  return camel
+}
+
+// 날짜/숫자/문자 값을 컬럼 특성에 맞게 문자열로 정규화
+function normalizeCellValue(column: string, v: unknown) {
+  if (v == null) return null
+  // Date 객체거나 날짜처럼 보이는 문자열/숫자는 yyyy-MM-dd로
+  if (v instanceof Date) {
+    return formatDate10(v)
+  }
+  // 엑셀에서 날짜가 숫자(시리얼)로 올 수 있음
+  if (typeof v === 'number' && v > 20000 && v < 80000) {
+    // Excel serial date (roughly)
+    const epoch = new Date(1899, 11, 30)
+    const d = new Date(epoch.getTime() + v * 86400000)
+    return formatDate10(d)
+  }
+  const s = String(v).trim()
+  // 간단한 날짜 패턴들: 2025-06-09, 2025/06/09, 2025.06.09
+  const m = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})$/)
+  if (m) {
+    const y = Number(m[1])
+    const mm = String(Number(m[2])).padStart(2, '0')
+    const dd = String(Number(m[3])).padStart(2, '0')
+    return `${y}-${mm}-${dd}`
+  }
+  return s
+}
+
+function formatDate10(d: Date) {
+  const y = d.getFullYear()
+  const m = String(d.getMonth() + 1).padStart(2, '0')
+  const day = String(d.getDate()).padStart(2, '0')
+  return `${y}-${m}-${day}` // 10자
+}
+
+
-- 
cgit v1.2.3