summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authordujinkim <dujin.kim@dtsolution.co.kr>2025-09-15 10:36:26 +0000
committerdujinkim <dujin.kim@dtsolution.co.kr>2025-09-15 10:36:26 +0000
commit534266f90a5ca846767dc2a990c77f1112a33d9c (patch)
tree62803374475726c2d2bdb0aece29b20599ed718a
parent0ffba85c79a7693d887e6fe0cd991e42faea8f36 (diff)
(임수민) serp 구현
Diffstat
-rw-r--r--app/[lng]/serp/layout.tsx5
-rw-r--r--app/[lng]/serp/page.tsx69
-rw-r--r--app/api/s-erp-import/rpa/route.ts135
-rw-r--r--components/s-erp-import/import-card.tsx92
-rw-r--r--components/s-erp-import/rpa-info.tsx28
-rw-r--r--db/schema/S_ERP/s_erp.ts487
-rw-r--r--lib/s-erp-import/actions.ts181
7 files changed, 997 insertions, 0 deletions
diff --git a/app/[lng]/serp/layout.tsx b/app/[lng]/serp/layout.tsx
new file mode 100644
index 00000000..9f048854
--- /dev/null
+++ b/app/[lng]/serp/layout.tsx
@@ -0,0 +1,5 @@
+export default function SapImportLayout({ children }: { children: React.ReactNode }) {
+ return <>{children}</>
+}
+
+
diff --git a/app/[lng]/serp/page.tsx b/app/[lng]/serp/page.tsx
new file mode 100644
index 00000000..0c9e0d3f
--- /dev/null
+++ b/app/[lng]/serp/page.tsx
@@ -0,0 +1,69 @@
+import * as React from 'react'
+import { ImportButton } from '@/components/s-erp-import/import-card'
+import { getSapTableCounts } from '@/lib/s-erp-import/actions'
+
+export const dynamic = 'force-dynamic'
+
+const TABLES = [
+ 'TB_SAP_EquipInfo',
+ 'TB_SAP_Order',
+ 'TB_SAP_OrderConfirm',
+ 'TB_SAP_OrderNotice',
+ 'TB_SAP_OrderBreakdown',
+ 'TB_SAP_MainternanceBOM',
+ 'TB_SAP_MaterialRepair',
+ 'TB_SAP_MaterialInfo',
+ 'TB_SAP_MaterialStock',
+ 'TB_SAP_MaterialRelease',
+ 'TB_SAP_MaterialReceiving',
+ 'TB_SAP_MaterialPurchase',
+]
+
+export default async function Page() {
+ const counts = await getSapTableCounts(TABLES)
+
+ return (
+ <div className="container py-8" data-testid="s-erp-import-page">
+ <h1 className="text-2xl font-bold mb-2" data-testid="page-title">S-ERP 데이터 임포트</h1>
+ <p className='text-sm mb-6' data-testid="page-description">버튼을 클릭하여 엑셀 파일을 업로드 해주세요.</p>
+
+ {/* RPA 가이드 정보 */}
+ <div
+ className="mb-6 p-4 bg-gray-50 border rounded"
+ data-testid="rpa-guide"
+ style={{ display: 'none' }} // RPA가 필요할 때만 표시
+ >
+ <h3 className="font-semibold mb-2" data-testid="rpa-guide-title">RPA 사용 가이드</h3>
+ <div className="text-sm space-y-1" data-testid="rpa-guide-content">
+ <div data-testid="rpa-selector-info">
+ <strong>셀렉터:</strong> [data-testid="upload-button-TABLE_NAME"]
+ </div>
+ <div data-testid="rpa-file-input-info">
+ <strong>파일 입력:</strong> [data-testid="file-input-TABLE_NAME"]
+ </div>
+ <div data-testid="rpa-status-info">
+ <strong>상태 확인:</strong> [data-testid="status-message-TABLE_NAME"]
+ </div>
+ <div data-testid="rpa-table-list-info">
+ <strong>테이블 목록:</strong> [data-testid="table-list"]
+ </div>
+ </div>
+ </div>
+
+ <div className="flex flex-col gap-6" data-testid="table-list">
+ {TABLES.map((name, index) => (
+ <div
+ key={name}
+ data-testid={`table-row-${name}`}
+ data-table-name={name}
+ data-table-index={index}
+ >
+ <ImportButton tableName={name} count={counts[name] ?? 0} />
+ </div>
+ ))}
+ </div>
+ </div>
+ )
+}
+
+
diff --git a/app/api/s-erp-import/rpa/route.ts b/app/api/s-erp-import/rpa/route.ts
new file mode 100644
index 00000000..749de90f
--- /dev/null
+++ b/app/api/s-erp-import/rpa/route.ts
@@ -0,0 +1,135 @@
+import { NextRequest, NextResponse } from 'next/server'
+import { getSapTableCounts, importExcel, exportTemplate } from '@/lib/s-erp-import/actions'
+
+// RPA용 API 엔드포인트
+export async function GET(request: NextRequest) {
+ try {
+ const { searchParams } = new URL(request.url)
+ const action = searchParams.get('action')
+ const tableName = searchParams.get('tableName')
+
+ switch (action) {
+ case 'get-counts':
+ // 모든 테이블의 데이터 건수 조회
+ const TABLES = [
+ 'TB_SAP_EquipInfo',
+ 'TB_SAP_Order',
+ 'TB_SAP_OrderConfirm',
+ 'TB_SAP_OrderNotice',
+ 'TB_SAP_OrderBreakdown',
+ 'TB_SAP_MainternanceBOM',
+ 'TB_SAP_MaterialRepair',
+ 'TB_SAP_MaterialInfo',
+ 'TB_SAP_MaterialStock',
+ 'TB_SAP_MaterialRelease',
+ 'TB_SAP_MaterialReceiving',
+ 'TB_SAP_MaterialPurchase',
+ ]
+ const counts = await getSapTableCounts(TABLES)
+ return NextResponse.json({
+ success: true,
+ data: counts,
+ timestamp: new Date().toISOString()
+ })
+
+ case 'get-template':
+ // 템플릿 다운로드
+ if (!tableName) {
+ return NextResponse.json({
+ success: false,
+ message: 'tableName is required'
+ }, { status: 400 })
+ }
+
+ const templateBuffer = await exportTemplate(tableName)
+ return new NextResponse(templateBuffer, {
+ headers: {
+ 'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
+ 'Content-Disposition': `attachment; filename="${tableName}_template.xlsx"`
+ }
+ })
+
+ case 'get-table-info':
+ // 테이블 정보 조회
+ const tableInfo = {
+ 'TB_SAP_EquipInfo': { title: '장비 정보', category: '장비 관리', description: 'SAP 장비 마스터 데이터' },
+ 'TB_SAP_Order': { title: '작업 주문', category: '작업 관리', description: '유지보수 작업 주문 정보' },
+ 'TB_SAP_OrderConfirm': { title: '주문 확인', category: '작업 관리', description: '작업 주문 확인 및 완료 정보' },
+ 'TB_SAP_OrderNotice': { title: '주문 통지', category: '작업 관리', description: '작업 주문 통지 및 알림 정보' },
+ 'TB_SAP_OrderBreakdown': { title: '주문 고장', category: '고장 관리', description: '장비 고장 및 수리 정보' },
+ 'TB_SAP_MainternanceBOM': { title: '유지보수 BOM', category: '부품 관리', description: '유지보수용 부품 목록' },
+ 'TB_SAP_MaterialRepair': { title: '자재 수리', category: '자재 관리', description: '자재 수리 및 교체 정보' },
+ 'TB_SAP_MaterialInfo': { title: '자재 정보', category: '자재 관리', description: '자재 마스터 데이터' },
+ 'TB_SAP_MaterialStock': { title: '자재 재고', category: '자재 관리', description: '자재 재고 현황' },
+ 'TB_SAP_MaterialRelease': { title: '자재 출고', category: '자재 관리', description: '자재 출고 내역' },
+ 'TB_SAP_MaterialReceiving': { title: '자재 입고', category: '자재 관리', description: '자재 입고 내역' },
+ 'TB_SAP_MaterialPurchase': { title: '자재 구매', category: '자재 관리', description: '자재 구매 요청 정보' }
+ }
+
+ if (tableName) {
+ return NextResponse.json({
+ success: true,
+ data: tableInfo[tableName as keyof typeof tableInfo] || null
+ })
+ } else {
+ return NextResponse.json({
+ success: true,
+ data: tableInfo
+ })
+ }
+
+ default:
+ return NextResponse.json({
+ success: false,
+ message: 'Invalid action. Supported actions: get-counts, get-template, get-table-info'
+ }, { status: 400 })
+ }
+ } catch (error: any) {
+ console.error('RPA API Error:', error)
+ return NextResponse.json({
+ success: false,
+ message: error.message || 'Internal server error',
+ timestamp: new Date().toISOString()
+ }, { status: 500 })
+ }
+}
+
+// RPA용 파일 업로드 엔드포인트
+export async function POST(request: NextRequest) {
+ try {
+ const formData = await request.formData()
+ const tableName = formData.get('tableName') as string
+ const file = formData.get('file') as File
+
+ if (!tableName || !file) {
+ return NextResponse.json({
+ success: false,
+ message: 'tableName and file are required'
+ }, { status: 400 })
+ }
+
+ // 파일 업로드 처리
+ const result = await importExcel(tableName, file)
+
+ return NextResponse.json({
+ success: result.success,
+ data: {
+ tableName,
+ fileName: file.name,
+ fileSize: file.size,
+ inserted: result.inserted || 0,
+ message: result.message,
+ unknownHeaders: (result as any).unknownHeaders,
+ errors: (result as any).errors
+ },
+ timestamp: new Date().toISOString()
+ })
+ } catch (error: any) {
+ console.error('RPA Upload Error:', error)
+ return NextResponse.json({
+ success: false,
+ message: error.message || 'Upload failed',
+ timestamp: new Date().toISOString()
+ }, { status: 500 })
+ }
+}
diff --git a/components/s-erp-import/import-card.tsx b/components/s-erp-import/import-card.tsx
new file mode 100644
index 00000000..43f2aa35
--- /dev/null
+++ b/components/s-erp-import/import-card.tsx
@@ -0,0 +1,92 @@
+'use client'
+
+import * as React from 'react'
+import { importExcel } from '@/lib/s-erp-import/actions'
+import { RPAInfo } from './rpa-info'
+
+export function ImportButton({ tableName, count }: { tableName: string; count: number }) {
+ const [pending, setPending] = React.useState(false)
+ const [currentCount, setCurrentCount] = React.useState(count)
+ const [statusMsg, setStatusMsg] = React.useState<string | null>(null)
+ const fileInputRef = React.useRef<HTMLInputElement>(null)
+
+ const onClickImport = () => fileInputRef.current?.click()
+
+ const onFileChange = async (e: React.ChangeEvent<HTMLInputElement>) => {
+ const file = e.target.files?.[0]
+ if (!file) return
+ setPending(true)
+ try {
+ console.log(`[SAP Import] 시작: ${tableName}, 파일: ${file.name}, 크기: ${file.size} bytes`)
+ const res = await importExcel(tableName, file)
+ if (res?.success) {
+ setCurrentCount((c) => c + (res.inserted ?? 0))
+ console.log(`[SAP Import] 성공: ${tableName}, 입력 건수: ${res.inserted ?? 0}`)
+ setStatusMsg(`${res.inserted ?? 0}건`)
+ } else {
+ console.error(`[SAP Import] 실패: ${tableName}, 사유: ${res?.message ?? '임포트 실패'}`)
+ if ((res as any)?.unknownHeaders) {
+ console.error(`[SAP Import] 매핑 실패 헤더:`, (res as any).unknownHeaders)
+ }
+ if ((res as any)?.errors) {
+ console.error(`[SAP Import] 유효성 오류 상세:`, (res as any).errors)
+ }
+ setStatusMsg(`데이터 저장에 실패하였습니다.`)
+ }
+ } catch (err: any) {
+ console.error(`[SAP Import] 예외 발생: ${tableName}, 에러: ${err?.message ?? err}`)
+ setStatusMsg(`데이터 저장에 실패하였습니다.`)
+ } finally {
+ console.log(`[SAP Import] 종료: ${tableName}`)
+ setPending(false)
+ e.target.value = ''
+ }
+ }
+
+ return (
+ <div
+ className="border rounded p-4"
+ data-testid={`import-controls-${tableName}`}
+ data-table-name={tableName}
+ data-current-count={currentCount}
+ >
+ <div className="flex items-center gap-6">
+ <button
+ onClick={onClickImport}
+ disabled={pending}
+ className="w-[300px] py-2 border rounded hover:bg-gray-100 transition-colors duration-200"
+ data-testid={`upload-button-${tableName}`}
+ data-table-name={tableName}
+ data-action="upload"
+ data-status={pending ? 'uploading' : 'idle'}
+ data-button-text={tableName}
+ >
+ {tableName}
+ </button>
+ {statusMsg && (
+ <span
+ className="text-base"
+ data-testid={`status-message-${tableName}`}
+ data-table-name={tableName}
+ data-message={statusMsg}
+ >
+ {statusMsg}
+ </span>
+ )}
+ <input
+ ref={fileInputRef}
+ type="file"
+ accept=".xlsx,.xls"
+ className="hidden"
+ onChange={onFileChange}
+ data-testid={`file-input-${tableName}`}
+ data-table-name={tableName}
+ data-file-types=".xlsx,.xls"
+ />
+ </div>
+ <RPAInfo tableName={tableName} count={currentCount} />
+ </div>
+ )
+}
+
+
diff --git a/components/s-erp-import/rpa-info.tsx b/components/s-erp-import/rpa-info.tsx
new file mode 100644
index 00000000..80bbec19
--- /dev/null
+++ b/components/s-erp-import/rpa-info.tsx
@@ -0,0 +1,28 @@
+'use client'
+
+import * as React from 'react'
+
+interface RPAInfoProps {
+ tableName: string
+ count: number
+}
+
+export function RPAInfo({ tableName, count }: RPAInfoProps) {
+ return (
+ <div
+ className="text-xs text-gray-500 mt-1"
+ data-testid={`rpa-info-${tableName}`}
+ data-table-name={tableName}
+ >
+ <div data-testid={`table-name-${tableName}`}>
+ 테이블명: {tableName}
+ </div>
+ <div data-testid={`current-count-${tableName}`}>
+ 현재 데이터: {count}건
+ </div>
+ <div data-testid={`file-types-${tableName}`}>
+ 지원 파일: .xlsx, .xls
+ </div>
+ </div>
+ )
+}
diff --git a/db/schema/S_ERP/s_erp.ts b/db/schema/S_ERP/s_erp.ts
new file mode 100644
index 00000000..1b24f0e7
--- /dev/null
+++ b/db/schema/S_ERP/s_erp.ts
@@ -0,0 +1,487 @@
+import { serial, varchar, timestamp, text, boolean, numeric, decimal, integer, real } from "drizzle-orm/pg-core";
+import { pgSchema } from "drizzle-orm/pg-core";
+
+export const sErpSchema = pgSchema("S-ERP");
+
+export const tbSAPEquipInfo = sErpSchema.table("TB_SAP_EquipInfo", {
+ id: serial().primaryKey(),
+ Equipment: varchar({ length: 20 }),
+ Description: varchar({ length: 100 }),
+ EquipCategory: varchar({ length: 2 }),
+ SystemStatus: varchar({ length: 10 }),
+ UserStatus: varchar({ length: 10 }),
+ ObjectType: varchar({ length: 10 }),
+ ABCIndicator: varchar({ length: 2 }),
+ PlanningPlant: varchar({ length: 10 }),
+ PlannerGroup: varchar({ length: 10 }),
+ MainWorkCenter: varchar({ length: 10 }),
+ CostCenter: varchar({ length: 10 }),
+ CatalogProfile: varchar({ length: 10 }),
+ FunctionLocation: varchar({ length: 20 }),
+ FunctionLocationDesc: varchar({ length: 100 }),
+ SuperordEquip: varchar({ length: 10 }),
+ COL1: varchar({ length: 10 }),
+ ConstType: varchar({ length: 10 }),
+ AssetNo: varchar({ length: 10 }),
+ TechnivalIdenNo: varchar({ length: 10 }),
+ Class: varchar({ length: 10 }),
+ MaintenancePlant: varchar({ length: 10 }),
+ PlantSection: varchar({ length: 10 }),
+ PlantSectionDescription: varchar({ length: 10 }),
+ ObjectTypeDesc: varchar({ length: 100 }),
+ MainWorkCenterDesc: varchar({ length: 100 }),
+ CostCenterDesc: varchar({ length: 100 }),
+ ObjectNumber: varchar({ length: 30 }),
+ SubAssetNo: varchar({ length: 10 }),
+ AuthorizGroup: varchar({ length: 10 }),
+ Weight: numeric({ precision: 16, scale: 3 }),
+ WeightUnit: varchar({ length: 10 }),
+ SizeDimension: varchar({ length: 20 }),
+ InventoryNumber: varchar({ length: 50 }),
+ StartUpDate: varchar({ length: 10 }),
+ AcquistnValue: decimal({ precision: 19, scale: 4 }), // MONEY 타입을 decimal로 변환
+ AcquistnValueCurr: varchar({ length: 10 }),
+ AcquistionDate: varchar({ length: 10 }),
+ Manufacturer: varchar({ length: 50 }),
+ ModelNumber: varchar({ length: 10 }),
+ ManufPartNo: varchar({ length: 10 }),
+ ManufCountry: varchar({ length: 4 }),
+ ManufSerialNo: varchar({ length: 50 }),
+ ConstrYr: varchar({ length: 4 }),
+ ConstrMth: varchar({ length: 2 }),
+ Location: varchar({ length: 10 }),
+ Room: varchar({ length: 10 }),
+ WorkCenter: varchar({ length: 10 }),
+ PPWorkCenter: varchar({ length: 10 }),
+ PPWorkCenterDesc: varchar({ length: 100 }),
+ SortField: varchar({ length: 10 }),
+ CompanyCode: varchar({ length: 10 }),
+ BusinessArea: varchar({ length: 10 }),
+ WBSElement: varchar({ length: 10 }),
+ MainWorkCtrPlant: varchar({ length: 10 }),
+ Position: varchar({ length: 10 }),
+ FinancialManagementArea: varchar({ length: 10 }),
+ CommitmentItem: varchar({ length: 20 }),
+ SysStatus: varchar({ length: 1 }),
+});
+
+export const tbSAPOrder = sErpSchema.table("TB_SAP_Order", {
+ id: serial().primaryKey(),
+ BreakdownIndicator: varchar({ length: 50 }),
+ Order: varchar({ length: 50 }),
+ OrderType: varchar({ length: 50 }),
+ OrderDescription: varchar({ length: 150 }),
+ MainWorkCenterDesc: varchar({ length: 100 }),
+ FunctionalLocation: varchar({ length: 50 }),
+ Equipment: varchar({ length: 50 }),
+ EquipmentDescriprion: varchar({ length: 100 }),
+ EquipmentSortField: varchar({ length: 50 }),
+ TagNumber: varchar({ length: 50 }),
+ Notification: varchar({ length: 50 }),
+ Priority: varchar({ length: 50 }),
+ PriorityDesc: varchar({ length: 50 }),
+ PlantSection: varchar({ length: 50 }),
+ PMActivityType: varchar({ length: 10 }),
+ MaintenancePlan: varchar({ length: 50 }),
+ StartDate: varchar({ length: 10 }),
+ FinishDate: varchar({ length: 10 }),
+ SystemStatus: varchar({ length: 50 }),
+ UserStatus: varchar({ length: 50 }),
+ CreatedBy: varchar({ length: 50 }),
+ Name: varchar({ length: 50 }),
+ PlanningPlant: varchar({ length: 50 }),
+ PlannerGroupName: varchar({ length: 50 }),
+ MainWorkCenter: varchar({ length: 50 }),
+ MaintenancePlant: varchar({ length: 50 }),
+ PlantSectionDescription: varchar({ length: 50 }),
+ COL1: varchar({ length: 50 }),
+ COL2: varchar({ length: 50 }),
+ BasicStartTime: varchar({ length: 50 }),
+ ActualStartDate: varchar({ length: 50 }),
+ ActualFinishTime: varchar({ length: 50 }),
+ PlanCost: integer(),
+ ActualCost: integer(),
+ SettlementCost: integer(),
+ Balance: integer(),
+ Currency: varchar({ length: 50 }),
+ EquiCategory: varchar({ length: 50 }),
+ ObjectType: varchar({ length: 50 }),
+ ABCOmdocatior: varchar({ length: 50 }),
+ EWRNo: varchar({ length: 50 }),
+});
+
+export const tbSAPOrderConfirm = sErpSchema.table("TB_SAP_OrderConfirm", {
+ id: serial().primaryKey(),
+ Confirmation: varchar({ length: 50 }),
+ Count: varchar({ length: 50 }),
+ Order: varchar({ length: 50 }),
+ OpAc: varchar({ length: 50 }),
+ OperationText: varchar({ length: 200 }),
+ ActualWork: decimal({ precision: 10, scale: 1 }),
+ Unit: varchar({ length: 50 }),
+ CreateOn: varchar({ length: 10 }),
+ CreateBy: varchar({ length: 50 }),
+ ActualWorker: varchar({ length: 50 }),
+ Name: varchar({ length: 50 }),
+ Reversed: varchar({ length: 50 }),
+ CanceledConfirmation: varchar({ length: 50 }),
+ OrderDescription: varchar({ length: 200 }),
+ BUDAT: varchar({ length: 10 }),
+ STATUS: varchar({ length: 10 }),
+ IWERK: varchar({ length: 50 }),
+ INGRP: varchar({ length: 50 }),
+ GRWRK: varchar({ length: 50 }),
+ AUART: varchar({ length: 50 }),
+});
+
+export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {
+ id: serial().primaryKey(),
+ Status: varchar({ length: 50 }),
+ Notification: integer(),
+ Type: varchar({ length: 50 }),
+ Description: varchar({ length: 64 }),
+ PlanningPlant: varchar({ length: 50 }),
+ PlannerGroup: varchar({ length: 50 }),
+ NameOfPlannerGroup: varchar({ length: 50 }),
+ WorkCenter: varchar({ length: 50 }),
+ NameOfWorkCenter: varchar({ length: 50 }),
+ FunctionalLocation: varchar({ length: 50 }),
+ Equipment: integer(),
+ TagNumber: varchar({ length: 50 }),
+ EquipmentDesc: varchar({ length: 50 }),
+ PlantSection: varchar({ length: 50 }),
+ CostCenter: varchar({ length: 50 }),
+ CostCenterDesc: varchar({ length: 50 }),
+ ABCIndicator: varchar({ length: 50 }),
+ NotificationDate: varchar({ length: 50 }),
+ Createdby: varchar({ length: 50 }),
+ SystemStatus: varchar({ length: 50 }),
+ UserStatus: varchar({ length: 50 }),
+ Order: integer(),
+ Requester: varchar({ length: 50 }),
+ RequesterName: varchar({ length: 50 }),
+ CodeGroup: varchar({ length: 50 }),
+ Code: integer(),
+ CodeDescription: varchar({ length: 50 }),
+ BreakdownIndicator: varchar({ length: 50 }),
+ Priority: varchar({ length: 50 }),
+ PriorityDesc: varchar({ length: 50 }),
+ NotifcatnType: varchar({ length: 50 }),
+ Name1: varchar({ length: 50 }),
+ WorkCenterInt: integer(),
+ ObjectNumber: varchar({ length: 50 }),
+ CHAR05: varchar({ length: 50 }),
+ Link: varchar({ length: 50 }),
+ Link1: varchar({ length: 50 }),
+ Tooltip: varchar({ length: 50 }),
+ PlantSectionDesc: varchar({ length: 50 }),
+ Catalog: varchar({ length: 50 }),
+ Partner: varchar({ length: 50 }),
+ NotificationTime: varchar({ length: 50 }),
+ CompletionDate: varchar({ length: 50 }),
+ CompletionTime: varchar({ length: 50 }),
+ ExtRequester: varchar({ length: 50 }),
+ COL1: varchar({ length: 50 }), // 일상점검미흡
+ OrderBasicStart: varchar({ length: 50 }),
+ EWRNo: varchar({ length: 50 }),
+ MalfunctionStart: varchar({ length: 50 }),
+ StartTime: varchar({ length: 50 }),
+ MalfunctionEnd: varchar({ length: 50 }),
+ EndTime: varchar({ length: 50 }),
+ BreakdownDuration: real(), // float4 타입을 real로 변환
+});
+
+export const tbSAPOrderBreakdown = sErpSchema.table("TB_SAP_OrderBreakdown", {
+ id: serial().primaryKey(),
+ Order: integer(),
+ OrderType: varchar({ length: 50 }),
+ FunctionalLocation: varchar({ length: 50 }),
+ FLDesc: varchar({ length: 50 }),
+ Equipment: integer(),
+ EquipmentDesc: varchar({ length: 50 }),
+ ABCIndicator: varchar({ length: 50 }),
+ MainWorkCenter: varchar({ length: 50 }),
+ WorkCenterDesc: varchar({ length: 50 }),
+ PlannerGroup: varchar({ length: 50 }),
+ PlannerGroupDesc: varchar({ length: 50 }),
+ ActStartDate: varchar({ length: 50 }),
+ ActEndDate: varchar({ length: 50 }),
+ WorkTimeHR: real(), // float4 타입을 real로 변환
+ ObjectPart: varchar({ length: 50 }),
+ Damage: varchar({ length: 50 }),
+ Cause: varchar({ length: 50 }),
+ Activity: varchar({ length: 50 }),
+ MaterialCost: varchar({ length: 50 }),
+ LaborCost: varchar({ length: 50 }),
+ TotalCost: varchar({ length: 50 }),
+ Currency: varchar({ length: 50 }),
+ Breakdown: varchar({ length: 50 }),
+ MalfunctionStart: varchar({ length: 50 }),
+ StartTime: varchar({ length: 50 }),
+ MalfunctionEnd: varchar({ length: 50 }),
+ EndTime: varchar({ length: 50 }),
+ BreakdownDuration: real(), // float4 타입을 real로 변환
+ Plant: varchar({ length: 50 }),
+ Notification: integer(),
+ ObjectNumber: varchar({ length: 50 }),
+ OrderDesc: varchar({ length: 64 }),
+ PlantSectionDesc: varchar({ length: 50 }),
+ TagNumber: varchar({ length: 50 }),
+});
+
+export const tbSAPMainternanceBOM = sErpSchema.table("TB_SAP_MainternanceBOM", {
+ id: serial().primaryKey(),
+ Date: varchar({ length: 50 }),
+ Order: integer(),
+ Description: varchar({ length: 50 }),
+ FuncLocation: varchar({ length: 50 }),
+ Description_1: varchar({ length: 50 }),
+ WorkCenter: varchar({ length: 50 }),
+ ObjectType: varchar({ length: 50 }),
+ Equipment: integer(),
+ Discription: varchar({ length: 50 }),
+ TagNumber: varchar({ length: 50 }),
+ ICT: varchar({ length: 50 }),
+ Component: varchar({ length: 50 }),
+ Quantity: integer(),
+ Unit: varchar({ length: 50 }),
+ Description_2: varchar({ length: 50 }),
+ Specification: varchar({ length: 50 }),
+ GEWRK: integer(),
+ STLNR: varchar({ length: 50 }),
+ STLAL: varchar({ length: 50 }),
+ WERKS: varchar({ length: 50 }),
+ TPLNR_T: varchar({ length: 50 }),
+ EQUNR_T: integer(),
+});
+
+export const tbSAPMaterialRepair = sErpSchema.table("TB_SAP_MaterialRepair", {
+ id: serial().primaryKey(),
+ BreakdownIndicator: varchar({ length: 50 }),
+ Order: integer(),
+ OrderType: varchar({ length: 50 }),
+ OrderDescription: varchar({ length: 50 }),
+ MainWorkCenterDescription: varchar({ length: 50 }),
+ FunctionalLocation: varchar({ length: 50 }),
+ Equipment: integer(),
+ EquipmentDescription: varchar({ length: 50 }),
+ EquipmentSortField: varchar({ length: 50 }),
+ TagNumber: varchar({ length: 50 }),
+ Notification: integer(),
+ Priority: varchar({ length: 50 }),
+ PriorityDesc: varchar({ length: 50 }),
+ PlantSection: varchar({ length: 50 }),
+ PMActivityType: varchar({ length: 50 }),
+ MaintenancePlan: varchar({ length: 50 }),
+ StartDate: varchar({ length: 50 }),
+ FinishDate: varchar({ length: 50 }),
+ SystemStatus: varchar({ length: 50 }),
+ UserStatus: varchar({ length: 50 }),
+ CreatedBy: varchar({ length: 50 }),
+ Name: varchar({ length: 50 }),
+ PlanningPlant: varchar({ length: 50 }),
+ PlannerGroup: varchar({ length: 50 }),
+ PlannerGroupName: varchar({ length: 50 }),
+ MainWorkCenter: varchar({ length: 50 }),
+ Maintenanceplant: varchar({ length: 50 }),
+ PlantSectionDescription: varchar({ length: 50 }),
+ Priority_1: varchar({ length: 50 }),
+ Priority_2: varchar({ length: 50 }),
+ BasicStartTime: varchar({ length: 50 }),
+ BasicFinishTime: varchar({ length: 50 }),
+ ActualStartDate: varchar({ length: 50 }),
+ ActualStartTime: varchar({ length: 50 }),
+ ActualFinishDate: varchar({ length: 50 }),
+ ActualFinishTIme: varchar({ length: 50 }),
+ PlanCost: varchar({ length: 50 }),
+ ActualCost: varchar({ length: 50 }),
+ SettlementCost: varchar({ length: 50 }),
+ Balance: varchar({ length: 50 }),
+ Currency: varchar({ length: 50 }),
+ EquiCategory: integer(),
+ ObjectType: varchar({ length: 50 }),
+ ABCIndicatior: varchar({ length: 50 }),
+ EWRNo: varchar({ length: 50 }),
+});
+
+export const tbSAPMaterialInfo = sErpSchema.table("TB_SAP_MaterialInfo", {
+ id: serial().primaryKey(),
+ Plnt: varchar({ length: 50 }),
+ Material: varchar({ length: 50 }),
+ Sloc: varchar({ length: 50 }),
+ Bin: varchar({ length: 50 }),
+ MatlGroup: varchar({ length: 50 }),
+ MaterialDescription: varchar({ length: 500 }),
+ MaterialName: varchar({ length: 500 }),
+ Specification: varchar({ length: 500 }),
+ Unrestr: integer(),
+ QualInsp: integer(),
+ Blocked: integer(),
+ Bun: varchar({ length: 50 }),
+ Warehouse: varchar({ length: 50 }),
+ Message: varchar({ length: 500 }),
+});
+
+export const tbSAPMaterialStock = sErpSchema.table("TB_SAP_MaterialStock", {
+ id: serial().primaryKey(),
+ WBS: varchar({ length: 50 }),
+ OldProjec: varchar({ length: 50 }),
+ Material: varchar({ length: 50 }),
+ MaterialD: varchar({ length: 50 }),
+ Specificat: varchar({ length: 50 }),
+ MRPType: varchar({ length: 50 }),
+ Batch: varchar({ length: 50 }),
+ Amount: varchar({ length: 50 }),
+ StorageLo: varchar({ length: 50 }),
+ StorageLo_1: varchar({ length: 50 }),
+ StorageBin: varchar({ length: 50 }),
+ Warehouse: varchar({ length: 50 }),
+ Warehouse_1: varchar({ length: 50 }),
+ MMTNo: varchar({ length: 50 }),
+ TNo: varchar({ length: 50 }),
+ BOXNo: varchar({ length: 50 }),
+ MaterialT: varchar({ length: 50 }),
+ Unrestrict: integer(),
+ QIStock: integer(),
+ BlockedSt: integer(),
+ REMARK: varchar({ length: 50 }),
+ TotalStoc: integer(),
+ Unit: varchar({ length: 50 }),
+ MAP: varchar({ length: 50 }),
+ NetWeight: real(), // float4 타입을 real로 변환
+ TotalWeig: real(), // float4 타입을 real로 변환
+ WeightUni: varchar({ length: 50 }),
+ Individual: varchar({ length: 50 }),
+ SpecialSt: varchar({ length: 50 }),
+ Vendor: varchar({ length: 50 }),
+ VendorDes: varchar({ length: 50 }),
+ MaterialG: varchar({ length: 50 }),
+ Procuremen: varchar({ length: 50 }),
+ PLMID: varchar({ length: 50 }),
+ ValveFitt: varchar({ length: 50 }),
+ BLNo: varchar({ length: 50 }),
+ TAGNO: varchar({ length: 50 }),
+});
+
+export const tbSAPMaterialRelease = sErpSchema.table("TB_SAP_MaterialRelease", {
+ id: serial().primaryKey(),
+ PostingGIdate: varchar({ length: 50 }),
+ Plant: varchar({ length: 50 }),
+ StorageLoc: varchar({ length: 50 }),
+ StockWBS: varchar({ length: 50 }),
+ InputWBS: varchar({ length: 50 }),
+ Material: varchar({ length: 50 }),
+ OldMaterialNumber: varchar({ length: 50 }),
+ MaterialDesc: varchar({ length: 50 }),
+ Spec: varchar({ length: 64 }),
+ Batch: varchar({ length: 50 }),
+ MovementType: varchar({ length: 50 }),
+ Quantity: integer(),
+ BUn: varchar({ length: 50 }),
+ GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)
+ NetWeight: real(), // float4 타입을 real로 변환
+ GrossWeight: real(), // float4 타입을 real로 변환
+ Unit: varchar({ length: 50 }),
+ Amount: varchar({ length: 50 }),
+ Currency: varchar({ length: 50 }),
+ MvtTypeTxt: varchar({ length: 50 }),
+ MaterialDocument: integer(),
+ DocumentYear: integer(),
+ S: varchar({ length: 50 }),
+ MatName: varchar({ length: 50 }),
+ MatGroupDesc: varchar({ length: 50 }),
+ InputProject: varchar({ length: 50 }),
+ HeaderText: varchar({ length: 50 }),
+});
+
+export const tbSAPMaterialReceiving = sErpSchema.table("TB_SAP_MaterialReceiving", {
+ id: serial().primaryKey(),
+ PostingGRDate: varchar({ length: 50 }),
+ Plant: varchar({ length: 50 }),
+ StorageLoc: varchar({ length: 50 }),
+ StockWBS: varchar({ length: 50 }),
+ InputWBS: varchar({ length: 50 }),
+ Material: varchar({ length: 50 }),
+ OldMaterialNumber: varchar({ length: 50 }),
+ MaterialDesc: varchar({ length: 50 }),
+ Spec: varchar({ length: 50 }),
+ Batch: varchar({ length: 50 }),
+ MovementType: varchar({ length: 50 }),
+ Quantity: integer(),
+ BUn: varchar({ length: 50 }),
+ GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)
+ PO: varchar({ length: 50 }),
+ POItem: integer(),
+ Vendor: varchar({ length: 50 }),
+ VendorName: varchar({ length: 50 }),
+ NetWeight: real(), // float4 타입을 real로 변환
+ GrossWeight: real(), // float4 타입을 real로 변환
+ Unit: varchar({ length: 50 }),
+ Amount: varchar({ length: 50 }),
+ Currency: varchar({ length: 50 }),
+ MvtTypeTxt: varchar({ length: 50 }),
+ MatDoc: integer(),
+ Year: integer(),
+ S: varchar({ length: 50 }),
+ MatName: varchar({ length: 50 }),
+ MatGroupDesc: varchar({ length: 50 }),
+ InputProject: varchar({ length: 50 }),
+ HeaderText: varchar({ length: 50 }),
+});
+
+export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase", {
+ id: serial().primaryKey(),
+ Status: varchar({ length: 50 }),
+ Notification: integer(),
+ Type: varchar({ length: 50 }),
+ Description: varchar({ length: 64 }),
+ PlanningPlant: varchar({ length: 50 }),
+ PlannerGroup: varchar({ length: 50 }),
+ NameOfPlannerGroup: varchar({ length: 50 }),
+ WorkCenter: varchar({ length: 50 }),
+ NameOfWorkCenter: varchar({ length: 50 }),
+ FunctionalLocation: varchar({ length: 50 }),
+ Equipment: integer(),
+ TagNumber: varchar({ length: 50 }),
+ EquipmentDesc: varchar({ length: 50 }),
+ PlantSection: varchar({ length: 50 }),
+ CostCenter: varchar({ length: 50 }),
+ CostCenterDesc: varchar({ length: 50 }),
+ ABCIndicator: varchar({ length: 50 }),
+ NotificationDate: varchar({ length: 50 }),
+ CreatedBy: varchar({ length: 50 }),
+ SystemStatus: varchar({ length: 50 }),
+ UserStatus: varchar({ length: 50 }),
+ Order: integer(),
+ Requester: varchar({ length: 50 }),
+ RequesterName: varchar({ length: 50 }),
+ CodeGroup: varchar({ length: 50 }),
+ Code: integer(),
+ CodeDescription: varchar({ length: 50 }),
+ BreakdownIndicator: varchar({ length: 50 }),
+ Priority: varchar({ length: 50 }),
+ PriorityDesc: varchar({ length: 50 }),
+ NotifcatnType: varchar({ length: 50 }),
+ Name1: varchar({ length: 50 }),
+ WorkCenterInt: integer(),
+ ObjectNumber: varchar({ length: 50 }),
+ CHAR05: varchar({ length: 50 }),
+ Link: varchar({ length: 50 }),
+ Link1: varchar({ length: 50 }),
+ Tooltip: varchar({ length: 50 }),
+ PlantSectionDesc: varchar({ length: 50 }),
+ Catalog: varchar({ length: 50 }),
+ Partner: varchar({ length: 50 }),
+ NotificationTime: varchar({ length: 50 }),
+ CompletionDate: varchar({ length: 50 }),
+ CompletionTime: varchar({ length: 50 }),
+ ExtRequester: varchar({ length: 50 }),
+ COL1: varchar({ length: 50 }), // 일상점검미흡
+ OrderBasicStart: varchar({ length: 50 }),
+ EWRNo: varchar({ length: 50 }),
+ MalfunctionStart: varchar({ length: 50 }),
+ StartTime: varchar({ length: 50 }),
+ MalfunctionEnd: varchar({ length: 50 }),
+ EndTime: varchar({ length: 50 }),
+ BreakdownDuration: real(), // float4 타입을 real로 변환
+}); \ No newline at end of file
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자
+}
+
+
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 06:42:46 +0000