(임수민) S-ERP 테이블 2개 추가, INSERT 로직을 UPDATE 로 변경, 복합 pk 로직 추가

4 files changed, 459 insertions, 103 deletions

diff --git a/app/api/s-erp-import/rpa/route.ts b/app/api/s-erp-import/rpa/route.ts
index 749de90f..79b40c90 100644
--- a/app/api/s-erp-import/rpa/route.ts
+++ b/app/api/s-erp-import/rpa/route.ts
@@ -24,6 +24,9 @@ export async function GET(request: NextRequest) {
           'TB_SAP_MaterialRelease',
           'TB_SAP_MaterialReceiving',
           'TB_SAP_MaterialPurchase',
+          // 새로 추가된 Technical Guidance 테이블들
+          'Technical_Guidance_Investment',
+          'Technical_Guidance_Order',
         ]
         const counts = await getSapTableCounts(TABLES)
         return NextResponse.json({
diff --git a/components/s-erp-import/import-card.tsx b/components/s-erp-import/import-card.tsx
index 43f2aa35..21ac7f9e 100644
--- a/components/s-erp-import/import-card.tsx
+++ b/components/s-erp-import/import-card.tsx
@@ -1,7 +1,7 @@
 'use client'

 

 import * as React from 'react'

-import { importExcel } from '@/lib/s-erp-import/actions'

+import { importExcel, getSingleTableCount } from '@/lib/s-erp-import/actions'

 import { RPAInfo } from './rpa-info'

 

 export function ImportButton({ tableName, count }: { tableName: string; count: number }) {

@@ -17,27 +17,46 @@ export function ImportButton({ tableName, count }: { tableName: string; count: n
     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}건`)

+        // UPSERT 모드와 기존 INSERT 모드 모두 지원

+        const processedCount = (res as any).processed ?? res.inserted ?? 0

+        

+        // UPSERT 모드인 경우 상세 정보 표시

+        if ((res as any).upsertMode) {

+          const inserted = (res as any).inserted ?? 0

+          const updated = (res as any).updated ?? 0

+          

+          // 실제 DB에서 현재 카운트를 다시 가져옴

+          try {

+            const actualCount = await getSingleTableCount(tableName)

+            setCurrentCount(actualCount)

+          } catch (err) {

+            console.error(`[SAP Import] DB 카운트 조회 실패:`, err)

+          }

+          

+          if (inserted > 0 && updated > 0) {

+            setStatusMsg(`신규 ${inserted}건, 업데이트 ${updated}건`)

+          } else if (inserted > 0) {

+            setStatusMsg(`${inserted}건 신규 추가`)

+          } else if (updated > 0) {

+            setStatusMsg(`${updated}건 업데이트`)

+          } else {

+            setStatusMsg(`변경사항 없음`)

+          }

+        } else {

+          // INSERT 모드에서는 기존 방식대로 카운트 증가

+          setCurrentCount((c) => c + processedCount)

+          setStatusMsg(`${processedCount}건`)

+        }

       } 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 = ''

     }

diff --git a/db/schema/S_ERP/s_erp.ts b/db/schema/S_ERP/s_erp.ts
index 1b24f0e7..5a9ca36c 100644
--- a/db/schema/S_ERP/s_erp.ts
+++ b/db/schema/S_ERP/s_erp.ts
@@ -1,11 +1,11 @@
-import { serial, varchar, timestamp, text, boolean, numeric, decimal, integer, real } from "drizzle-orm/pg-core";

+import { serial, varchar, timestamp, text, boolean, numeric, decimal, integer, real, bigint, uniqueIndex } 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 }),

+  Equipment: varchar({ length: 20 }), // pk

   Description: varchar({ length: 100 }),

   EquipCategory: varchar({ length: 2 }),

   SystemStatus: varchar({ length: 10 }),

@@ -63,12 +63,15 @@ export const tbSAPEquipInfo = sErpSchema.table("TB_SAP_EquipInfo", {
   FinancialManagementArea: varchar({ length: 10 }),

   CommitmentItem: varchar({ length: 20 }),

   SysStatus: varchar({ length: 1 }),

-});

+}, (table) => ({

+  // Equipment 컬럼에 UNIQUE 인덱스 추가

+  uniqueEquipment: uniqueIndex("unique_equipment").on(table.Equipment),

+}));

 

 export const tbSAPOrder = sErpSchema.table("TB_SAP_Order", {

   id: serial().primaryKey(),

   BreakdownIndicator: varchar({ length: 50 }),

-  Order: varchar({ length: 50 }),

+  Order: varchar({ length: 50 }), // pk

   OrderType: varchar({ length: 50 }),

   OrderDescription: varchar({ length: 150 }),

   MainWorkCenterDesc: varchar({ length: 100 }),

@@ -99,21 +102,24 @@ export const tbSAPOrder = sErpSchema.table("TB_SAP_Order", {
   BasicStartTime: varchar({ length: 50 }),

   ActualStartDate: varchar({ length: 50 }),

   ActualFinishTime: varchar({ length: 50 }),

-  PlanCost: integer(),

-  ActualCost: integer(),

-  SettlementCost: integer(),

-  Balance: integer(),

+  PlanCost: bigint({ mode: 'number' }),

+  ActualCost: bigint({ mode: 'number' }),

+  SettlementCost: bigint({ mode: 'number' }),

+  Balance: bigint({ mode: 'number' }),

   Currency: varchar({ length: 50 }),

   EquiCategory: varchar({ length: 50 }),

   ObjectType: varchar({ length: 50 }),

   ABCOmdocatior: varchar({ length: 50 }),

   EWRNo: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // Order 컬럼에 UNIQUE 인덱스 추가

+  uniqueOrder: uniqueIndex("unique_order").on(table.Order),

+}));

 

 export const tbSAPOrderConfirm = sErpSchema.table("TB_SAP_OrderConfirm", {

   id: serial().primaryKey(),

-  Confirmation: varchar({ length: 50 }),

-  Count: varchar({ length: 50 }),

+  Confirmation: varchar({ length: 50 }), // pk

+  Count: varchar({ length: 50 }), // pk

   Order: varchar({ length: 50 }),

   OpAc: varchar({ length: 50 }),

   OperationText: varchar({ length: 200 }),

@@ -132,12 +138,15 @@ export const tbSAPOrderConfirm = sErpSchema.table("TB_SAP_OrderConfirm", {
   INGRP: varchar({ length: 50 }),

   GRWRK: varchar({ length: 50 }),

   AUART: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // Confirmation, Count 복합 PK에 UNIQUE 인덱스 추가

+  uniqueConfirmationCount: uniqueIndex("unique_confirmation_count").on(table.Confirmation, table.Count),

+}));

 

 export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {

   id: serial().primaryKey(),

   Status: varchar({ length: 50 }),

-  Notification: integer(),

+  Notification: bigint({ mode: 'number' }), // pk

   Type: varchar({ length: 50 }),

   Description: varchar({ length: 64 }),

   PlanningPlant: varchar({ length: 50 }),

@@ -146,7 +155,7 @@ export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {
   WorkCenter: varchar({ length: 50 }),

   NameOfWorkCenter: varchar({ length: 50 }),

   FunctionalLocation: varchar({ length: 50 }),

-  Equipment: integer(),

+  Equipment: bigint({ mode: 'number' }),

   TagNumber: varchar({ length: 50 }),

   EquipmentDesc: varchar({ length: 50 }),

   PlantSection: varchar({ length: 50 }),

@@ -157,18 +166,18 @@ export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {
   Createdby: varchar({ length: 50 }),

   SystemStatus: varchar({ length: 50 }),

   UserStatus: varchar({ length: 50 }),

-  Order: integer(),

+  Order: bigint({ mode: 'number' }),

   Requester: varchar({ length: 50 }),

   RequesterName: varchar({ length: 50 }),

   CodeGroup: varchar({ length: 50 }),

-  Code: integer(),

+  Code: bigint({ mode: 'number' }),

   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(),

+  WorkCenterInt: bigint({ mode: 'number' }),

   ObjectNumber: varchar({ length: 50 }),

   CHAR05: varchar({ length: 50 }),

   Link: varchar({ length: 50 }),

@@ -189,15 +198,18 @@ export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {
   MalfunctionEnd: varchar({ length: 50 }),

   EndTime: varchar({ length: 50 }),

   BreakdownDuration: real(), // float4 타입을 real로 변환

-});

+}, (table) => ({

+  // Notification 컬럼에 UNIQUE 인덱스 추가

+  uniqueNotification: uniqueIndex("unique_notification").on(table.Notification),

+}));

 

 export const tbSAPOrderBreakdown = sErpSchema.table("TB_SAP_OrderBreakdown", {

   id: serial().primaryKey(),

-  Order: integer(),

+  Order: bigint({ mode: 'number' }), // pk

   OrderType: varchar({ length: 50 }),

   FunctionalLocation: varchar({ length: 50 }),

   FLDesc: varchar({ length: 50 }),

-  Equipment: integer(),

+  Equipment: bigint({ mode: 'number' }),

   EquipmentDesc: varchar({ length: 50 }),

   ABCIndicator: varchar({ length: 50 }),

   MainWorkCenter: varchar({ length: 50 }),

@@ -222,52 +234,58 @@ export const tbSAPOrderBreakdown = sErpSchema.table("TB_SAP_OrderBreakdown", {
   EndTime: varchar({ length: 50 }),

   BreakdownDuration: real(), // float4 타입을 real로 변환

   Plant: varchar({ length: 50 }),

-  Notification: integer(),

+  Notification: bigint({ mode: 'number' }),

   ObjectNumber: varchar({ length: 50 }),

   OrderDesc: varchar({ length: 64 }),

   PlantSectionDesc: varchar({ length: 50 }),

   TagNumber: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // Order 컬럼에 UNIQUE 인덱스 추가

+  uniqueOrder: uniqueIndex("unique_order_breakdown").on(table.Order),

+}));

 

 export const tbSAPMainternanceBOM = sErpSchema.table("TB_SAP_MainternanceBOM", {

   id: serial().primaryKey(),

-  Date: varchar({ length: 50 }),

-  Order: integer(),

+  Date: varchar({ length: 50 }), // pk

+  Order: bigint({ mode: 'number' }), // pk

   Description: varchar({ length: 50 }),

   FuncLocation: varchar({ length: 50 }),

   Description_1: varchar({ length: 50 }),

   WorkCenter: varchar({ length: 50 }),

   ObjectType: varchar({ length: 50 }),

-  Equipment: integer(),

+  Equipment: bigint({ mode: 'number' }),

   Discription: varchar({ length: 50 }),

   TagNumber: varchar({ length: 50 }),

   ICT: varchar({ length: 50 }),

-  Component: varchar({ length: 50 }),

-  Quantity: integer(),

+  Component: varchar({ length: 50 }), // pk

+  Quantity: bigint({ mode: 'number' }),

   Unit: varchar({ length: 50 }),

   Description_2: varchar({ length: 50 }),

-  Specification: varchar({ length: 50 }),

-  GEWRK: integer(),

+  Specification: varchar({ length: 200 }),

+  GEWRK: bigint({ mode: 'number' }),

   STLNR: varchar({ length: 50 }),

   STLAL: varchar({ length: 50 }),

   WERKS: varchar({ length: 50 }),

   TPLNR_T: varchar({ length: 50 }),

-  EQUNR_T: integer(),

-});

+  EQUNR_T: bigint({ mode: 'number' }),

+}, (table) => ({

+  // Date, Order, Component 복합 PK에 UNIQUE 인덱스 추가

+  uniqueDateOrderComponent: uniqueIndex("unique_date_order_component").on(table.Date, table.Order, table.Component),

+}));

 

 export const tbSAPMaterialRepair = sErpSchema.table("TB_SAP_MaterialRepair", {

   id: serial().primaryKey(),

   BreakdownIndicator: varchar({ length: 50 }),

-  Order: integer(),

+  Order: bigint({ mode: 'number' }), // pk

   OrderType: varchar({ length: 50 }),

   OrderDescription: varchar({ length: 50 }),

   MainWorkCenterDescription: varchar({ length: 50 }),

   FunctionalLocation: varchar({ length: 50 }),

-  Equipment: integer(),

+  Equipment: bigint({ mode: 'number' }),

   EquipmentDescription: varchar({ length: 50 }),

   EquipmentSortField: varchar({ length: 50 }),

   TagNumber: varchar({ length: 50 }),

-  Notification: integer(),

+  Notification: bigint({ mode: 'number' }),

   Priority: varchar({ length: 50 }),

   PriorityDesc: varchar({ length: 50 }),

   PlantSection: varchar({ length: 50 }),

@@ -298,41 +316,47 @@ export const tbSAPMaterialRepair = sErpSchema.table("TB_SAP_MaterialRepair", {
   SettlementCost: varchar({ length: 50 }),

   Balance: varchar({ length: 50 }),

   Currency: varchar({ length: 50 }),

-  EquiCategory: integer(),

+  EquiCategory: bigint({ mode: 'number' }),

   ObjectType: varchar({ length: 50 }),

   ABCIndicatior: varchar({ length: 50 }),

   EWRNo: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // Order 컬럼에 UNIQUE 인덱스 추가

+  uniqueOrderRepair: uniqueIndex("unique_order_repair").on(table.Order),

+}));

 

 export const tbSAPMaterialInfo = sErpSchema.table("TB_SAP_MaterialInfo", {

   id: serial().primaryKey(),

-  Plnt: varchar({ length: 50 }),

-  Material: varchar({ length: 50 }),

-  Sloc: varchar({ length: 50 }),

+  Plnt: varchar({ length: 50 }), // pk

+  Material: varchar({ length: 50 }), // pk

+  Sloc: varchar({ length: 50 }), // pk

   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(),

+  Unrestr: bigint({ mode: 'number' }),

+  QualInsp: bigint({ mode: 'number' }),

+  Blocked: bigint({ mode: 'number' }),

   Bun: varchar({ length: 50 }),

   Warehouse: varchar({ length: 50 }),

   Message: varchar({ length: 500 }),

-});

+}, (table) => ({

+  // Plnt, Material, Sloc 복합 PK에 UNIQUE 인덱스 추가

+  uniquePlntMaterialSloc: uniqueIndex("unique_plnt_material_sloc").on(table.Plnt, table.Material, table.Sloc),

+}));

 

 export const tbSAPMaterialStock = sErpSchema.table("TB_SAP_MaterialStock", {

   id: serial().primaryKey(),

   WBS: varchar({ length: 50 }),

   OldProjec: varchar({ length: 50 }),

-  Material: varchar({ length: 50 }),

+  Material: varchar({ length: 50 }), // pk

   MaterialD: varchar({ length: 50 }),

   Specificat: varchar({ length: 50 }),

   MRPType: varchar({ length: 50 }),

-  Batch: varchar({ length: 50 }),

+  Batch: varchar({ length: 50 }), // pk

   Amount: varchar({ length: 50 }),

-  StorageLo: varchar({ length: 50 }),

+  StorageLo: varchar({ length: 50 }), // pk

   StorageLo_1: varchar({ length: 50 }),

   StorageBin: varchar({ length: 50 }),

   Warehouse: varchar({ length: 50 }),

@@ -341,11 +365,11 @@ export const tbSAPMaterialStock = sErpSchema.table("TB_SAP_MaterialStock", {
   TNo: varchar({ length: 50 }),

   BOXNo: varchar({ length: 50 }),

   MaterialT: varchar({ length: 50 }),

-  Unrestrict: integer(),

-  QIStock: integer(),

-  BlockedSt: integer(),

+  Unrestrict: bigint({ mode: 'number' }),

+  QIStock: bigint({ mode: 'number' }),

+  BlockedSt: bigint({ mode: 'number' }),

   REMARK: varchar({ length: 50 }),

-  TotalStoc: integer(),

+  TotalStoc: bigint({ mode: 'number' }),

   Unit: varchar({ length: 50 }),

   MAP: varchar({ length: 50 }),

   NetWeight: real(), // float4 타입을 real로 변환

@@ -361,22 +385,25 @@ export const tbSAPMaterialStock = sErpSchema.table("TB_SAP_MaterialStock", {
   ValveFitt: varchar({ length: 50 }),

   BLNo: varchar({ length: 50 }),

   TAGNO: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // Material, Batch, StorageLo 복합 PK에 UNIQUE 인덱스 추가

+  uniqueMaterialBatchStorage: uniqueIndex("unique_material_batch_storage").on(table.Material, table.Batch, table.StorageLo),

+}));

 

 export const tbSAPMaterialRelease = sErpSchema.table("TB_SAP_MaterialRelease", {

   id: serial().primaryKey(),

-  PostingGIdate: varchar({ length: 50 }),

-  Plant: varchar({ length: 50 }),

-  StorageLoc: varchar({ length: 50 }),

+  PostingGIdate: varchar({ length: 50 }), // pk

+  Plant: varchar({ length: 50 }), // pk

+  StorageLoc: varchar({ length: 50 }), // pk

   StockWBS: varchar({ length: 50 }),

   InputWBS: varchar({ length: 50 }),

   Material: varchar({ length: 50 }),

   OldMaterialNumber: varchar({ length: 50 }),

   MaterialDesc: varchar({ length: 50 }),

-  Spec: varchar({ length: 64 }),

+  Spec: varchar({ length: 100 }),

   Batch: varchar({ length: 50 }),

   MovementType: varchar({ length: 50 }),

-  Quantity: integer(),

+  Quantity: bigint({ mode: 'number' }),

   BUn: varchar({ length: 50 }),

   GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)

   NetWeight: real(), // float4 타입을 real로 변환

@@ -385,33 +412,36 @@ export const tbSAPMaterialRelease = sErpSchema.table("TB_SAP_MaterialRelease", {
   Amount: varchar({ length: 50 }),

   Currency: varchar({ length: 50 }),

   MvtTypeTxt: varchar({ length: 50 }),

-  MaterialDocument: integer(),

-  DocumentYear: integer(),

+  MaterialDocument: bigint({ mode: 'number' }), // pk

+  DocumentYear: bigint({ mode: 'number' }), // pk

   S: varchar({ length: 50 }),

   MatName: varchar({ length: 50 }),

   MatGroupDesc: varchar({ length: 50 }),

   InputProject: varchar({ length: 50 }),

   HeaderText: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // PostingGIdate, Plant, StorageLoc, MaterialDocument, DocumentYear 복합 PK에 UNIQUE 인덱스 추가

+  uniqueReleaseComposite: uniqueIndex("unique_release_composite").on(table.PostingGIdate, table.Plant, table.StorageLoc, table.MaterialDocument, table.DocumentYear),

+}));

 

 export const tbSAPMaterialReceiving = sErpSchema.table("TB_SAP_MaterialReceiving", {

   id: serial().primaryKey(),

-  PostingGRDate: varchar({ length: 50 }),

-  Plant: varchar({ length: 50 }),

-  StorageLoc: varchar({ length: 50 }),

+  PostingGRDate: varchar({ length: 50 }), // pk

+  Plant: varchar({ length: 50 }), // pk

+  StorageLoc: varchar({ length: 50 }), // pk

   StockWBS: varchar({ length: 50 }),

   InputWBS: varchar({ length: 50 }),

   Material: varchar({ length: 50 }),

   OldMaterialNumber: varchar({ length: 50 }),

   MaterialDesc: varchar({ length: 50 }),

-  Spec: varchar({ length: 50 }),

+  Spec: varchar({ length: 100 }),

   Batch: varchar({ length: 50 }),

   MovementType: varchar({ length: 50 }),

-  Quantity: integer(),

+  Quantity: bigint({ mode: 'number' }),

   BUn: varchar({ length: 50 }),

   GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)

   PO: varchar({ length: 50 }),

-  POItem: integer(),

+  POItem: bigint({ mode: 'number' }),

   Vendor: varchar({ length: 50 }),

   VendorName: varchar({ length: 50 }),

   NetWeight: real(), // float4 타입을 real로 변환

@@ -420,19 +450,22 @@ export const tbSAPMaterialReceiving = sErpSchema.table("TB_SAP_MaterialReceiving
   Amount: varchar({ length: 50 }),

   Currency: varchar({ length: 50 }),

   MvtTypeTxt: varchar({ length: 50 }),

-  MatDoc: integer(),

-  Year: integer(),

+  MatDoc: bigint({ mode: 'number' }), // pk

+  Year: bigint({ mode: 'number' }), // pk

   S: varchar({ length: 50 }),

   MatName: varchar({ length: 50 }),

   MatGroupDesc: varchar({ length: 50 }),

   InputProject: varchar({ length: 50 }),

   HeaderText: varchar({ length: 50 }),

-});

+}, (table) => ({

+  // PostingGRDate, Plant, StorageLoc, MatDoc, Year 복합 PK에 UNIQUE 인덱스 추가

+  uniqueReceivingComposite: uniqueIndex("unique_receiving_composite").on(table.PostingGRDate, table.Plant, table.StorageLoc, table.MatDoc, table.Year),

+}));

 

 export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase", {

   id: serial().primaryKey(),

   Status: varchar({ length: 50 }),

-  Notification: integer(),

+  Notification: bigint({ mode: 'number' }),

   Type: varchar({ length: 50 }),

   Description: varchar({ length: 64 }),

   PlanningPlant: varchar({ length: 50 }),

@@ -441,7 +474,7 @@ export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase",
   WorkCenter: varchar({ length: 50 }),

   NameOfWorkCenter: varchar({ length: 50 }),

   FunctionalLocation: varchar({ length: 50 }),

-  Equipment: integer(),

+  Equipment: bigint({ mode: 'number' }),

   TagNumber: varchar({ length: 50 }),

   EquipmentDesc: varchar({ length: 50 }),

   PlantSection: varchar({ length: 50 }),

@@ -452,18 +485,18 @@ export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase",
   CreatedBy: varchar({ length: 50 }),

   SystemStatus: varchar({ length: 50 }),

   UserStatus: varchar({ length: 50 }),

-  Order: integer(),

+  Order: bigint({ mode: 'number' }),

   Requester: varchar({ length: 50 }),

   RequesterName: varchar({ length: 50 }),

   CodeGroup: varchar({ length: 50 }),

-  Code: integer(),

+  Code: bigint({ mode: 'number' }),

   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(),

+  WorkCenterInt: bigint({ mode: 'number' }),

   ObjectNumber: varchar({ length: 50 }),

   CHAR05: varchar({ length: 50 }),

   Link: varchar({ length: 50 }),

@@ -484,4 +517,50 @@ export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase",
   MalfunctionEnd: varchar({ length: 50 }),

   EndTime: varchar({ length: 50 }),

   BreakdownDuration: real(), // float4 타입을 real로 변환

-});
\ No newline at end of file
+});

+

+export const technicalGuidanceInvestment = sErpSchema.table("Technical_Guidance_Investment", {

+  id: serial().primaryKey(),

+  FUMNR: varchar({ length: 10 }), // pk

+  FUMIT: bigint({ mode: 'number' }), // pk

+  DESCR: varchar({ length: 50 }),

+  FU_APPDATE: varchar({ length: 8 }),

+  COST_INVEST: varchar({ length: 15 })

+}, (table) => ({

+  // FUMNR, FUMIT 복합 PK에 UNIQUE 인덱스 추가

+  uniqueFumnrFumit: uniqueIndex("unique_fumnr_fumit").on(table.FUMNR, table.FUMIT),

+}));

+

+export const technicalGuidanceOrder = sErpSchema.table("Technical_Guidance_Order", {

+  id: serial().primaryKey(),

+  AUFNR: varchar({ length: 12 }), // pk (Order)

+  VORNR: varchar({ length: 4 }), // pk (Operation)

+  ORD_ERDAT: varchar({ length: 50 }),

+  FUMNR: varchar({ length: 10 }),

+  FUMIT: bigint({ mode: 'number' }),

+  DESCR: varchar({ length: 50 }),

+  FU_APPDATE: varchar({ length: 50 }),

+  BANFN: varchar({ length: 10 }),

+  BNFPO: bigint({ mode: 'number' }),

+  BADAT: varchar({ length: 50 }),

+  EBELN: varchar({ length: 10 }),

+  EBELP: bigint({ mode: 'number' }),

+  PO_ERDAT: varchar({ length: 50 }),

+  AUART: varchar({ length: 4 }),

+  STTXT: varchar({ length: 40 }),

+  EQUNR: varchar({ length: 18 }),

+  KTEXT: varchar({ length: 20 }),

+  INGRP: varchar({ length: 3 }),

+  INNAM: varchar({ length: 18 }),

+  CON_STEP: varchar({ length: 20 }),

+  CON_DESC: varchar({ length: 40 }),

+  CON_CO: varchar({ length: 30 }),

+  COST_INVEST: varchar({ length: 15 }),

+  COST_ORD: varchar({ length: 13 }),

+  COST_PR: varchar({ length: 13 }),

+  COST_PO: varchar({ length: 13 }),

+  COST_IV: varchar({ length: 13 }),

+}, (table) => ({

+  // AUFNR, VORNR 복합 PK에 UNIQUE 인덱스 추가

+  uniqueAufnrVornr: uniqueIndex("unique_aufnr_vornr").on(table.AUFNR, table.VORNR),

+}));
\ No newline at end of file
diff --git a/lib/s-erp-import/actions.ts b/lib/s-erp-import/actions.ts
index d4d2e1ec..5dfc1116 100644
--- a/lib/s-erp-import/actions.ts
+++ b/lib/s-erp-import/actions.ts
@@ -20,6 +20,15 @@ export async function getSapTableCounts(tableNames: string[]) {
   return result

 }

 

+// 단일 테이블 카운트 조회 함수

+export async function getSingleTableCount(tableName: string): Promise<number> {

+  const table = (schema as any)[camelFromTable(tableName)]

+  if (!table) return 0

+  

+  const rows = await db.select({ c: sql<number>`count(*)` }).from(table)

+  return Number(rows?.[0]?.c ?? 0)

+}

+

 export async function exportTemplate(tableName: string): Promise<Buffer> {

   const table = (schema as any)[camelFromTable(tableName)]

   if (!table) throw new Error(`Unknown table: ${tableName}`)

@@ -46,9 +55,19 @@ export async function importExcel(tableName: string, file: File) {
   const sheet = workbook.worksheets[0]

   const rawHeader = (sheet.getRow(1).values as any[]).slice(1) as string[]

 

-  // 1) 스키마 컬럼 수집 및 정규화 맵 구성

+  // 1) 스키마 컬럼 수집 및 정규화 맵 구성 (Drizzle 내부 메타데이터 제외)

   const schemaKeys = Object.keys(table)

-    .filter((k) => !k.startsWith('_') && k !== 'getSQL' && k !== '[$$schema]' && k !== 'id')

+    .filter((k) => {

+      // Drizzle ORM 내부 메타데이터 및 시스템 컬럼 제외

+      return !k.startsWith('_') && 

+             k !== 'getSQL' && 

+             k !== '[$$schema]' && 

+             k !== 'id' && 

+             k !== 'enableRLS' &&

+             k !== '$inferSelect' &&

+             k !== '$inferInsert' &&

+             typeof (table as any)[k] !== 'function'

+    })

   const normalize = (s: string) => (s || '')

     .replace(/\./g, '')

     .replace(/\s+/g, '')

@@ -69,10 +88,39 @@ export async function importExcel(tableName: string, file: File) {
     .map(({ h }) => String(h))

 

   const mappedColumns = mappedHeader.filter(Boolean) as string[]

-  if (mappedColumns.length === 0) {

+  

+  // EVCP 스타일 헤더 검증

+  const headerErrors: string[] = []

+  

+  // 1) 누락된 스키마 컬럼 확인 (필수 컬럼이 파일에 없는 경우)

+  schemaKeys.forEach(schemaKey => {

+    if (!mappedColumns.includes(schemaKey)) {

+      headerErrors.push(`필수 컬럼 "${schemaKey}"가 파일에 없습니다`)

+    }

+  })

+  

+  // 2) 예상치 못한 컬럼 확인 (파일에 있지만 스키마에 없는 컬럼)

+  unknownHeaders.forEach(header => {

+    headerErrors.push(`예상치 못한 컬럼 "${header}"가 파일에 있습니다`)

+  })

+  

+  // 헤더 검증 실패 시 에러 반환

+  if (headerErrors.length > 0) {

     return {

       success: false,

-      message: `헤더가 테이블 컬럼과 일치하지 않습니다. unknownHeaders=${JSON.stringify(unknownHeaders)}`,

+      message: `헤더 검증 실패: ${headerErrors.join(', ')}`,

+    }

+  }

+  

+  // PK 컬럼이 매핑되었는지 확인

+  const primaryKeys = getPrimaryKeysFromSchema(tableName)

+  if (primaryKeys.length > 0) {

+    const mappedPKs = primaryKeys.filter(pk => mappedColumns.includes(pk))

+    if (mappedPKs.length === 0) {

+      return {

+        success: false,

+        message: `필수 컬럼(Primary Key)이 없습니다. 필요한 PK: [${primaryKeys.join(', ')}]`,

+      }

     }

   }

 

@@ -85,7 +133,7 @@ export async function importExcel(tableName: string, file: File) {
     mappedHeader.forEach((schemaKey, idx) => {

       if (!schemaKey) return

       const v = values[idx]

-      obj[schemaKey] = normalizeCellValue(schemaKey, v)

+      obj[schemaKey] = normalizeCellValue(schemaKey, v, table)

     })

     // 전체가 비어있는 행은 제외

     if (Object.values(obj).some((v) => v !== null && v !== '')) {

@@ -127,10 +175,93 @@ export async function importExcel(tableName: string, file: File) {
   }

 

   try {

-    // @ts-expect-error drizzle insert

-    await db.insert(table).values(rows)

-    revalidatePath('/sap-import')

-    return { success: true, inserted: rows.length, unknownHeaders }

+    const primaryKeys = getPrimaryKeysFromSchema(tableName)

+    

+    if (primaryKeys.length > 0 && rows.length > 0) {

+      // UPSERT 전 기존 데이터 개수 확인

+      const beforeCount = await getSingleTableCount(tableName)

+      

+      // 모든 PK 값이 존재하는 행만 처리

+      const validRows = rows.filter(row => {

+        const pkValues = primaryKeys.map(pk => row[pk]).filter(val => val != null && val !== '')

+        return pkValues.length === primaryKeys.length

+      })

+      

+      let actualProcessedCount = 0

+      

+      if (validRows.length > 0) {

+        if (primaryKeys.length === 1) {

+          // 단일 PK: Drizzle의 onConflictDoUpdate 사용

+          const pkColumn = primaryKeys[0]

+          const targetColumn = (table as any)[pkColumn]

+          

+          if (targetColumn) {

+            const firstRow = validRows[0]

+            const updateFields = Object.keys(firstRow)

+              .filter(key => key !== pkColumn && key !== 'id')

+              .reduce((acc, key) => {

+                acc[key] = sql`excluded.${sql.identifier(key)}`

+                return acc

+              }, {} as Record<string, any>)

+            

+            // 배치 내 중복 PK 제거 (마지막 행만 유지)

+            const deduplicatedRows = validRows.reduce((acc, row) => {

+              const pkValue = row[pkColumn]

+              acc[pkValue] = row // 같은 PK면 마지막 행으로 덮어씀

+              return acc

+            }, {} as Record<string, any>)

+            

+            const uniqueRows = Object.values(deduplicatedRows)

+            actualProcessedCount = uniqueRows.length

+            console.log(`[DEBUG] 단일 PK 중복 제거: ${validRows.length}행 -> ${uniqueRows.length}행`)

+            

+            // 배치 처리 (500개씩)

+            const chunkSize = 500

+            for (let i = 0; i < uniqueRows.length; i += chunkSize) {

+              const chunk = uniqueRows.slice(i, i + chunkSize)

+              await db.insert(table)

+                .values(chunk as any)

+                .onConflictDoUpdate({

+                  target: targetColumn,

+                  set: updateFields,

+                })

+            }

+          }

+        } else {

+          // 복합 PK: 진짜 UPSERT 사용

+          const processedCount = await handleCompositeKeyProperUpsert(table, tableName, validRows, primaryKeys)

+          actualProcessedCount = processedCount

+        }

+      }

+      

+      // UPSERT 후 실제 데이터 개수 확인

+      const afterCount = await getSingleTableCount(tableName)

+      

+      // 실제 추가된 행 수 vs 업데이트된 행 수 계산 (중복 제거 후 개수 사용)

+      const actualInserted = afterCount - beforeCount

+      const actualUpdated = actualProcessedCount - actualInserted

+      const skipped = rows.length - validRows.length

+      const duplicatesRemoved = validRows.length - actualProcessedCount

+      

+      revalidatePath('/sap-import')

+      return { 

+        success: true, 

+        processed: actualProcessedCount,

+        inserted: actualInserted,

+        updated: actualUpdated,

+        skipped: skipped,

+        duplicatesRemoved: duplicatesRemoved,

+        total: rows.length,

+        unknownHeaders,

+        primaryKeys,

+        upsertMode: true

+      }

+    } else {

+      // PK가 정의되지 않은 테이블은 기존 INSERT 방식

+      await db.insert(table).values(rows as any)

+      revalidatePath('/sap-import')

+      return { success: true, inserted: rows.length, unknownHeaders, upsertMode: false }

+    }

   } catch (e: any) {

     return { success: false, message: e?.message ?? 'DB 입력 실패', unknownHeaders }

   }

@@ -145,13 +276,136 @@ function camelFromTable(name: string) {
   return camel

 }

 

+/**

+ * 스키마에서 PK 코멘트(// pk)를 분석하여 Primary Key 컬럼들을 추출

+ */

+function getPrimaryKeysFromSchema(tableName: string): string[] {

+  const pkMap: Record<string, string[]> = {

+    // 단일 PK 테이블들

+    'TB_SAP_EquipInfo': ['Equipment'],

+    'TB_SAP_Order': ['Order'],

+    'TB_SAP_OrderNotice': ['Notification'],

+    'TB_SAP_OrderBreakdown': ['Order'], // pk 코멘트 확인됨

+    'TB_SAP_MainternanceBOM': ['Date', 'Order', 'Component'], // 복합 PK

+    'TB_SAP_MaterialRepair': ['Order'],

+    

+    // 복합 PK 테이블들

+    'TB_SAP_OrderConfirm': ['Confirmation', 'Count'],

+    'TB_SAP_MaterialInfo': ['Plnt', 'Material', 'Sloc'], // 복합 PK

+    'TB_SAP_MaterialStock': ['Material', 'Batch', 'StorageLo'], // 복합 PK

+    'TB_SAP_MaterialRelease': ['PostingGIdate', 'Plant', 'StorageLoc', 'MaterialDocument', 'DocumentYear'], // 복합 PK

+    'TB_SAP_MaterialReceiving': ['PostingGRDate', 'Plant', 'StorageLoc', 'MatDoc', 'Year'], // 복합 PK

+    

+    // 새로 추가된 테이블들

+    'Technical_Guidance_Investment': ['FUMNR', 'FUMIT'], // 복합 PK

+    'Technical_Guidance_Order': ['AUFNR', 'VORNR'], // 복합 PK

+  }

+  

+  return pkMap[tableName] || []

+}

+

+/**

+ * 복합 PK를 위한 안전한 UPSERT (ON CONFLICT DO UPDATE)

+ */

+async function handleCompositeKeyProperUpsert(

+  table: any, 

+  tableName: string, 

+  rows: any[], 

+  primaryKeys: string[]

+): Promise<number> {

+  // 복합 PK 컬럼들을 가져오기

+  const targetColumns = primaryKeys.map(pk => (table as any)[pk]).filter(Boolean)

+  

+  if (targetColumns.length !== primaryKeys.length) {

+    throw new Error(`[${tableName}] PK 컬럼을 찾을 수 없습니다: ${primaryKeys.join(', ')}`)

+  }

+  

+  // UPDATE할 필드들 (PK와 id 제외)

+  const firstRow = rows[0]

+  const updateFields = Object.keys(firstRow)

+    .filter(key => !primaryKeys.includes(key) && key !== 'id')

+    .reduce((acc, key) => {

+      acc[key] = sql`excluded.${sql.identifier(key)}`

+      return acc

+    }, {} as Record<string, any>)

+  

+  // 배치 내 중복 PK 제거 (마지막 행만 유지)

+  const deduplicatedRows = rows.reduce((acc, row) => {

+    const pkKey = primaryKeys.map(pk => row[pk]).join('|')

+    acc[pkKey] = row // 같은 PK면 마지막 행으로 덮어씀

+    return acc

+  }, {} as Record<string, any>)

+  

+  const uniqueRows = Object.values(deduplicatedRows)

+  console.log(`[DEBUG] 중복 제거: ${rows.length}행 -> ${uniqueRows.length}행`)

+  

+  // 배치 처리 (500개씩)

+  const chunkSize = 500

+  for (let i = 0; i < uniqueRows.length; i += chunkSize) {

+    const chunk = uniqueRows.slice(i, i + chunkSize)

+    await db.insert(table)

+      .values(chunk as any)

+      .onConflictDoUpdate({

+        target: targetColumns,

+        set: updateFields,

+      })

+  }

+  

+  return uniqueRows.length

+}

+

+

 // 날짜/숫자/문자 값을 컬럼 특성에 맞게 문자열로 정규화

-function normalizeCellValue(column: string, v: unknown) {

+function normalizeCellValue(column: string, v: unknown, table?: any) {

   if (v == null) return null

+  

+  // 빈 문자열 처리 (real, integer, bigint 타입에서는 null로 변환)

+  const s = String(v).trim()

+  if (s === '') {

+    return null

+  }

+  

+  // 헤더 행 감지: 컬럼명과 동일한 값이 오면 null 처리 (헤더가 데이터로 잘못 인식된 경우)

+  if (s === column) {

+    return null

+  }

+  

+  // 컬럼 타입 확인

+  const columnDef = table?.[column]

+  const actualDataType = columnDef?.config?.columnType

+  

+  // 숫자 타입 컬럼인지 확인 (bigint, integer 등)

+  const isNumericColumn = actualDataType?.includes('BigInt') || actualDataType?.includes('Integer')

+  

+  if (isNumericColumn) {

+    // 숫자로 변환 가능한지 확인

+    const numericValue = s.replace(/[,\s]/g, '') // 쉼표와 공백 제거

+    if (!/^\d+$/.test(numericValue)) {

+      return null // 숫자가 아니면 null

+    }

+    return numericValue

+  }

+  

   // Date 객체거나 날짜처럼 보이는 문자열/숫자는 yyyy-MM-dd로

   if (v instanceof Date) {

     return formatDate10(v)

   }

+  

+  // 시간 형식 처리 (예: "9시 42분 0초" -> "09:42:00")

+  const timeFormatMatch = s.match(/^(\d{1,2})시\s*(\d{1,2})분\s*(\d{1,2})초$/)

+  if (timeFormatMatch) {

+    const hour = String(Number(timeFormatMatch[1])).padStart(2, '0')

+    const minute = String(Number(timeFormatMatch[2])).padStart(2, '0')

+    const second = String(Number(timeFormatMatch[3])).padStart(2, '0')

+    return `${hour}:${minute}:${second}`

+  }

+  

+  // 쉼표가 포함된 숫자 처리 (예: "117,733" -> "117733")

+  const commaNumberMatch = s.match(/^"?([0-9,]+)"?$/)

+  if (commaNumberMatch) {

+    return commaNumberMatch[1].replace(/,/g, '')

+  }

+  

   // 엑셀에서 날짜가 숫자(시리얼)로 올 수 있음

   if (typeof v === 'number' && v > 20000 && v < 80000) {

     // Excel serial date (roughly)

@@ -159,15 +413,16 @@ function normalizeCellValue(column: string, v: unknown) {
     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')

+  

+  // 날짜 패턴들: 2025-06-09, 2025/06/09, 2025.06.09

+  const dateMatch = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})$/)

+  if (dateMatch) {

+    const y = Number(dateMatch[1])

+    const mm = String(Number(dateMatch[2])).padStart(2, '0')

+    const dd = String(Number(dateMatch[3])).padStart(2, '0')

     return `${y}-${mm}-${dd}`

   }

+  

   return s

 }