1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
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자
}
|
