summaryrefslogtreecommitdiff
path: root/app
diff options
context:
space:
mode:
Diffstat (limited to 'app')
-rw-r--r--app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts927
1 files changed, 927 insertions, 0 deletions
diff --git a/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
new file mode 100644
index 00000000..6c73cf08
--- /dev/null
+++ b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
@@ -0,0 +1,927 @@
+import { XMLParser } from "fast-xml-parser";
+import { readFileSync } from "fs";
+import { NextRequest, NextResponse } from "next/server";
+import { join } from "path";
+import db from "@/db/db";
+import { MATL, DESC, PLNT, UNIT, CLASSASGN, CHARASGN } from "@/db/schema/MDG/modelMaster";
+import { eq } from "drizzle-orm";
+
+// 요청 데이터 인터페이스 정의
+interface RequestData {
+ materials: Material[];
+}
+
+// 애플리케이션 내부 데이터 모델 (XML 필드와 1:1 매핑)
+interface Material {
+ matnr?: string; // Material Number
+ mbrsh?: string; // Industry Sector
+ mtart?: string; // Material Type
+ lvorm?: string; // Deletion flag
+ meins?: string; // Base Unit of Measure
+ matkl?: string; // Material Group
+ bismt?: string; // Old Material Number
+ spart?: string; // Division
+ prdha?: string; // Product Hierarchy
+ mstae?: string; // Cross-plant Material Status
+ mstde?: string; // Cross-distribution-chain Material Status
+ brgew?: string; // Gross Weight
+ gewei?: string; // Weight Unit
+ ntgew?: string; // Net Weight
+ volum?: string; // Volume
+ voleh?: string; // Volume Unit
+ groes?: string; // Size/dimensions
+ laeng?: string; // Length
+ breit?: string; // Width
+ hoehe?: string; // Height
+ meabm?: string; // Unit of Dimension
+ magrv?: string; // Material Group: Packaging Materials
+ vhart?: string; // Packaging Material Type
+ zzname?: string; // Material Name (Custom)
+ zzspec?: string; // Material Specification (Custom)
+ zzdesc?: string; // Material Description (Custom)
+ zzmmtyp?: string; // Material Type (Custom)
+ zzregdt?: string; // Registration Date (Custom)
+ zzregtm?: string; // Registration Time (Custom)
+ zzregus?: string; // Registration User (Custom)
+ zzappdt?: string; // Approval Date (Custom)
+ zzapptm?: string; // Approval Time (Custom)
+ zzappus?: string; // Approval User (Custom)
+ zzlamdt?: string; // Last Modified Date (Custom)
+ zzlamtm?: string; // Last Modified Time (Custom)
+ zzlamus?: string; // Last Modified User (Custom)
+ zzprflg?: string; // Process Flag (Custom)
+ zzdokar?: string; // Document Type (Custom)
+ zzdoknr?: string; // Document Number (Custom)
+ zzdoktl?: string; // Document Part (Custom)
+ zzdokvr?: string; // Document Version (Custom)
+ descriptions?: Description[];
+ plants?: Plant[];
+ units?: Unit[];
+ classAssignments?: ClassAssignment[];
+ characteristicAssignments?: CharacteristicAssignment[];
+}
+
+interface Description {
+ matnr?: string; // Material Number
+ spras?: string; // Language Key
+ maktx?: string; // Material Description
+}
+
+interface Plant {
+ matnr?: string; // Material Number
+ werks?: string; // Plant
+ lvorm?: string; // Deletion Flag
+ mmsta?: string; // Plant-specific Material Status
+ mmstd?: string; // Plant-specific Material Status Valid From
+ zzmtarp?: string; // Custom Field
+ zzregdt?: string; // Registration Date (Custom)
+ zzregtm?: string; // Registration Time (Custom)
+ zzregus?: string; // Registration User (Custom)
+ zzlamdt?: string; // Last Modified Date (Custom)
+ zzlamtm?: string; // Last Modified Time (Custom)
+ zzlamus?: string; // Last Modified User (Custom)
+ zzprflg?: string; // Process Flag (Custom)
+}
+
+interface Unit {
+ matnr?: string; // Material Number
+ meinh?: string; // Unit of Measure
+ umrez?: string; // Numerator for Conversion to Base UoM
+ umren?: string; // Denominator for Conversion to Base UoM
+ laeng?: string; // Length
+ breit?: string; // Width
+ hoehe?: string; // Height
+ meabm?: string; // Unit of Dimension
+ volum?: string; // Volume
+ voleh?: string; // Volume Unit
+ brgew?: string; // Gross Weight
+ gewei?: string; // Weight Unit
+}
+
+interface ClassAssignment {
+ matnr?: string; // Material Number
+ class?: string; // Class
+ klart?: string; // Class Type
+}
+
+interface CharacteristicAssignment {
+ matnr?: string; // Material Number
+ class?: string; // Class
+ klart?: string; // Class Type
+ atnam?: string; // Characteristic Name
+ atwrt?: string; // Characteristic Value
+ atflv?: string; // Value From
+ atawe?: string; // Value To
+ atflb?: string; // Description
+ ataw1?: string; // Additional Value
+ atbez?: string; // Characteristic Description
+ atwtb?: string; // Characteristic Value Description
+}
+
+// SOAP XML 데이터 구조 인터페이스
+// XML 기준 대문자 필드명 사용
+interface MatlXML {
+ MATNR?: string;
+ MBRSH?: string;
+ MTART?: string;
+ LVORM?: string;
+ MEINS?: string;
+ MATKL?: string;
+ BISMT?: string;
+ SPART?: string;
+ PRDHA?: string;
+ MSTAE?: string;
+ MSTDE?: string;
+ BRGEW?: string;
+ GEWEI?: string;
+ NTGEW?: string;
+ VOLUM?: string;
+ VOLEH?: string;
+ GROES?: string;
+ LAENG?: string;
+ BREIT?: string;
+ HOEHE?: string;
+ MEABM?: string;
+ MAGRV?: string;
+ VHART?: string;
+ ZZNAME?: string;
+ ZZSPEC?: string;
+ ZZDESC?: string;
+ ZZMMTYP?: string;
+ ZZREGDT?: string;
+ ZZREGTM?: string;
+ ZZREGUS?: string;
+ ZZAPPDT?: string;
+ ZZAPPTM?: string;
+ ZZAPPUS?: string;
+ ZZLAMDT?: string;
+ ZZLAMTM?: string;
+ ZZLAMUS?: string;
+ ZZPRFLG?: string;
+ ZZDOKAR?: string;
+ ZZDOKNR?: string;
+ ZZDOKTL?: string;
+ ZZDOKVR?: string;
+ DESC?: DescXML[];
+ PLNT?: PlntXML[];
+ UNIT?: UnitXML[];
+ CLASSASGN?: ClassAsgnXML[];
+ CHARASGN?: CharAsgnXML[];
+}
+
+interface DescXML {
+ MATNR?: string;
+ SPRAS?: string;
+ MAKTX?: string;
+}
+
+interface PlntXML {
+ MATNR?: string;
+ WERKS?: string;
+ LVORM?: string;
+ MMSTA?: string;
+ MMSTD?: string;
+ ZZMTARP?: string;
+ ZZREGDT?: string;
+ ZZREGTM?: string;
+ ZZREGUS?: string;
+ ZZLAMDT?: string;
+ ZZLAMTM?: string;
+ ZZLAMUS?: string;
+ ZZPRFLG?: string;
+}
+
+interface UnitXML {
+ MATNR?: string;
+ MEINH?: string;
+ UMREZ?: string;
+ UMREN?: string;
+ LAENG?: string;
+ BREIT?: string;
+ HOEHE?: string;
+ MEABM?: string;
+ VOLUM?: string;
+ VOLEH?: string;
+ BRGEW?: string;
+ GEWEI?: string;
+}
+
+interface ClassAsgnXML {
+ MATNR?: string;
+ CLASS?: string;
+ KLART?: string;
+}
+
+interface CharAsgnXML {
+ MATNR?: string;
+ CLASS?: string;
+ KLART?: string;
+ ATNAM?: string;
+ ATWRT?: string;
+ ATFLV?: string;
+ ATAWE?: string;
+ ATFLB?: string;
+ ATAW1?: string;
+ ATBEZ?: string;
+ ATWTB?: string;
+}
+
+// SOAP Body에 대한 데이터 타입 정의
+interface SoapBodyData {
+ [key: string]: unknown;
+ IF_MDZ_EVCP_MODEL_MASTERReq?: Record<string, unknown>;
+ 'tns:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
+ 'ns1:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
+ 'p0:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
+ MATL?: MatlXML[];
+}
+
+function serveWsdl() {
+ try {
+ const wsdlPath = join(process.cwd(), 'public', 'wsdl', 'IF_MDZ_EVCP_MODEL_MASTER.wsdl');
+ const wsdlContent = readFileSync(wsdlPath, 'utf-8');
+
+ return new NextResponse(wsdlContent, {
+ headers: {
+ 'Content-Type': 'text/xml; charset=utf-8',
+ },
+ });
+ } catch (error) {
+ console.error('Failed to read WSDL file:', error);
+ return new NextResponse('WSDL file not found', { status: 404 });
+ }
+}
+
+export async function GET(request: NextRequest) {
+ const url = new URL(request.url);
+ if (url.searchParams.has('wsdl')) {
+ return serveWsdl();
+ }
+
+ return new NextResponse('Method Not Allowed', { status: 405 });
+}
+
+// WSDL 기반의 SOAP 요청 (데이터 전송건) 처리하기 (HTTP)
+export async function POST(request: NextRequest) {
+ const url = new URL(request.url);
+ if (url.searchParams.has('wsdl')) {
+ return serveWsdl();
+ }
+
+ try {
+ // 요청 본문 (MDZ 데이터)를 가져오기
+ const body = await request.text();
+
+ // 요청 로깅
+ console.log('Request Body 일부:', body.substring(0, 200) + (body.length > 200 ? '...' : ''));
+
+ // XML 파서 설정하기
+ const parser = new XMLParser({
+ ignoreAttributes: false,
+ attributeNamePrefix: '@_',
+ parseAttributeValue: false, // 값 조작 방지
+ trimValues: true,
+ isArray: (name: string) => {
+ return ['MATL', 'DESC', 'PLNT', 'UNIT', 'CLASSASGN', 'CHARASGN'].includes(name);
+ },
+ parseTagValue: false, // 값 조작 방지
+ allowBooleanAttributes: true,
+ });
+
+ // XML 파싱하기
+ const parsedData = parser.parse(body);
+
+ // 디버깅용 - 최상위 구조 확인
+ console.log('XML root keys:', Object.keys(parsedData));
+
+ // 재할당 가능한 변수 선언
+ let requestData = null;
+
+ // 가능한 경로 확인
+ if (parsedData?.['soap:Envelope']?.['soap:Body']) {
+ const soapBody = parsedData['soap:Envelope']['soap:Body'];
+ requestData = extractRequestData(soapBody);
+ } else if (parsedData?.['SOAP:Envelope']?.['SOAP:Body']) {
+ const soapBody = parsedData['SOAP:Envelope']['SOAP:Body'];
+ requestData = extractRequestData(soapBody);
+ } else if (parsedData?.['Envelope']?.['Body']) {
+ const soapBody = parsedData['Envelope']['Body'];
+ requestData = extractRequestData(soapBody);
+ } else if (parsedData?.['soapenv:Envelope']?.['soapenv:Body']) {
+ const soapBody = parsedData['soapenv:Envelope']['soapenv:Body'];
+ requestData = extractRequestData(soapBody);
+ } else if (parsedData?.['IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ requestData = parsedData['IF_MDZ_EVCP_MODEL_MASTERReq'];
+ console.log('Found direct IF_MDZ_EVCP_MODEL_MASTERReq data');
+ } else if (parsedData?.['ns1:IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ requestData = parsedData['ns1:IF_MDZ_EVCP_MODEL_MASTERReq'];
+ console.log('Found direct ns1:IF_MDZ_EVCP_MODEL_MASTERReq data');
+ } else if (parsedData?.['p0:IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ requestData = parsedData['p0:IF_MDZ_EVCP_MODEL_MASTERReq'];
+ console.log('Found direct p0:IF_MDZ_EVCP_MODEL_MASTERReq data');
+ } else {
+ // 루트 레벨에서 MATL을 직접 찾기
+ if (parsedData?.MATL) {
+ requestData = parsedData;
+ console.log('Found MATL data at root level');
+ } else {
+ // 다른 모든 키에 대해 확인
+ for (const key of Object.keys(parsedData)) {
+ const value = parsedData[key];
+ // 데이터 구조가 맞는지 확인 (MATL이 있는지)
+ if (value && value.MATL) {
+ requestData = value;
+ console.log(`Found data in root key: ${key}`);
+ break;
+ }
+
+ // 키 이름에 IF_MDZ_EVCP_MODEL_MASTERReq가 포함되어 있는지 확인
+ if (key.includes('IF_MDZ_EVCP_MODEL_MASTERReq')) {
+ requestData = value;
+ console.log(`Found data in root key with matching name: ${key}`);
+ break;
+ }
+ }
+ }
+ }
+
+ if (!requestData) {
+ console.error('Could not find valid request data in the received payload');
+ console.error('Received XML structure:', JSON.stringify(parsedData, null, 2));
+ throw new Error('Missing request data - could not find IF_MDZ_EVCP_MODEL_MASTERReq or MATL data');
+ }
+
+ // 데이터 유효성 검증
+ console.log('Validating request data structure:',
+ `MATL: ${requestData.MATL ? 'found' : 'not found'}`
+ );
+
+ // 샘플 데이터 로깅
+ if (requestData.MATL && Array.isArray(requestData.MATL) && requestData.MATL.length > 0) {
+ console.log('First MATL sample:', JSON.stringify(requestData.MATL[0], null, 2));
+ }
+
+ // 데이터 구조 정규화 - MDZ 데이터를 우리 애플리케이션 모델로 변환
+ const normalizedData: RequestData = {
+ materials: transformMatlData(requestData.MATL)
+ };
+
+ // 기본 유효성 검사 - 필수 필드 확인
+ for (const material of normalizedData.materials) {
+ if (!material.matnr) {
+ throw new Error('Missing required field: matnr in material');
+ }
+ }
+
+ // 데이터베이스 저장
+ await saveToDatabase(normalizedData);
+
+ console.log(`Processed ${normalizedData.materials.length} materials`);
+
+ // XML 응답 생성
+ const xmlResponse = `<?xml version="1.0" encoding="UTF-8"?>
+<soap:Envelope
+ xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
+ xmlns:tns="http://60.101.108.100/api/IF_MDZ_EVCP_MODEL_MASTER/">
+ <soap:Body>
+ </soap:Body>
+</soap:Envelope>`;
+
+ return new NextResponse(xmlResponse, {
+ headers: {
+ 'Content-Type': 'text/xml; charset=utf-8',
+ },
+ });
+ } catch (error: unknown) {
+ console.error('API Error:', error);
+
+ // XML 에러 응답
+ const errorResponse = `<?xml version="1.0" encoding="UTF-8"?>
+<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
+ <soap:Body>
+ <soap:Fault>
+ <faultcode>soap:Server</faultcode>
+ <faultstring>${error instanceof Error ? ('[from eVCP]: ' + error.message) : 'Unknown error'}</faultstring>
+ </soap:Fault>
+ </soap:Body>
+</soap:Envelope>`;
+
+ return new NextResponse(errorResponse, {
+ status: 500,
+ headers: {
+ 'Content-Type': 'text/xml; charset=utf-8',
+ },
+ });
+ }
+}
+
+// SOAP Body나 루트에서 요청 데이터 추출하는 헬퍼 함수
+function extractRequestData(data: SoapBodyData): Record<string, unknown> | null {
+ if (!data) return null;
+
+ if (data['IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ return data['IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
+ } else if (data['tns:IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ return data['tns:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
+ } else if (data['ns1:IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ return data['ns1:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
+ } else if (data['p0:IF_MDZ_EVCP_MODEL_MASTERReq']) {
+ return data['p0:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
+ }
+
+ // 다른 키 검색
+ for (const key of Object.keys(data)) {
+ if (key.includes('IF_MDZ_EVCP_MODEL_MASTERReq')) {
+ return data[key] as Record<string, unknown>;
+ }
+ }
+
+ // MATL이 직접 있는 경우
+ if (data.MATL && Array.isArray(data.MATL)) {
+ return data;
+ }
+
+ return null;
+}
+
+// XML MATL 데이터를 내부 Material 형식으로 변환하는 함수
+function transformMatlData(matlData: MatlXML[]): Material[] {
+ if (!matlData || !Array.isArray(matlData)) {
+ return [];
+ }
+
+ return matlData.map(matl => {
+ const material: Material = {
+ matnr: matl.MATNR,
+ mbrsh: matl.MBRSH,
+ mtart: matl.MTART,
+ lvorm: matl.LVORM,
+ meins: matl.MEINS,
+ matkl: matl.MATKL,
+ bismt: matl.BISMT,
+ spart: matl.SPART,
+ prdha: matl.PRDHA,
+ mstae: matl.MSTAE,
+ mstde: matl.MSTDE,
+ brgew: matl.BRGEW,
+ gewei: matl.GEWEI,
+ ntgew: matl.NTGEW,
+ volum: matl.VOLUM,
+ voleh: matl.VOLEH,
+ groes: matl.GROES,
+ laeng: matl.LAENG,
+ breit: matl.BREIT,
+ hoehe: matl.HOEHE,
+ meabm: matl.MEABM,
+ magrv: matl.MAGRV,
+ vhart: matl.VHART,
+ zzname: matl.ZZNAME,
+ zzspec: matl.ZZSPEC,
+ zzdesc: matl.ZZDESC,
+ zzmmtyp: matl.ZZMMTYP,
+ zzregdt: matl.ZZREGDT,
+ zzregtm: matl.ZZREGTM,
+ zzregus: matl.ZZREGUS,
+ zzappdt: matl.ZZAPPDT,
+ zzapptm: matl.ZZAPPTM,
+ zzappus: matl.ZZAPPUS,
+ zzlamdt: matl.ZZLAMDT,
+ zzlamtm: matl.ZZLAMTM,
+ zzlamus: matl.ZZLAMUS,
+ zzprflg: matl.ZZPRFLG,
+ zzdokar: matl.ZZDOKAR,
+ zzdoknr: matl.ZZDOKNR,
+ zzdoktl: matl.ZZDOKTL,
+ zzdokvr: matl.ZZDOKVR,
+ };
+
+ // DESC 항목 처리
+ if (matl.DESC && Array.isArray(matl.DESC)) {
+ material.descriptions = matl.DESC.map((desc: DescXML) => ({
+ matnr: desc.MATNR,
+ spras: desc.SPRAS,
+ maktx: desc.MAKTX
+ }));
+ }
+
+ // PLNT 항목 처리
+ if (matl.PLNT && Array.isArray(matl.PLNT)) {
+
+ material.plants = matl.PLNT.map((plnt: PlntXML) => ({
+ matnr: plnt.MATNR,
+ werks: plnt.WERKS,
+ lvorm: plnt.LVORM,
+ mmsta: plnt.MMSTA,
+ mmstd: plnt.MMSTD,
+ zzmtarp: plnt.ZZMTARP,
+ zzregdt: plnt.ZZREGDT,
+ zzregtm: plnt.ZZREGTM,
+ zzregus: plnt.ZZREGUS,
+ zzlamdt: plnt.ZZLAMDT,
+ zzlamtm: plnt.ZZLAMTM,
+ zzlamus: plnt.ZZLAMUS,
+ zzprflg: plnt.ZZPRFLG
+ }));
+ }
+
+ // UNIT 항목 처리
+ if (matl.UNIT && Array.isArray(matl.UNIT)) {
+ material.units = matl.UNIT.map((unit: UnitXML) => ({
+ matnr: unit.MATNR,
+ meinh: unit.MEINH,
+ umrez: unit.UMREZ,
+ umren: unit.UMREN,
+ laeng: unit.LAENG,
+ breit: unit.BREIT,
+ hoehe: unit.HOEHE,
+ meabm: unit.MEABM,
+ volum: unit.VOLUM,
+ voleh: unit.VOLEH,
+ brgew: unit.BRGEW,
+ gewei: unit.GEWEI
+ }));
+ }
+
+ // CLASSASGN 항목 처리
+ if (matl.CLASSASGN && Array.isArray(matl.CLASSASGN)) {
+ material.classAssignments = matl.CLASSASGN.map((cls: ClassAsgnXML) => ({
+ matnr: cls.MATNR,
+ class: cls.CLASS,
+ klart: cls.KLART
+ }));
+ }
+
+ // CHARASGN 항목 처리
+ if (matl.CHARASGN && Array.isArray(matl.CHARASGN)) {
+ material.characteristicAssignments = matl.CHARASGN.map((char: CharAsgnXML) => ({
+ matnr: char.MATNR,
+ class: char.CLASS,
+ klart: char.KLART,
+ atnam: char.ATNAM,
+ atwrt: char.ATWRT,
+ atflv: char.ATFLV,
+ atawe: char.ATAWE,
+ atflb: char.ATFLB,
+ ataw1: char.ATAW1,
+ atbez: char.ATBEZ,
+ atwtb: char.ATWTB
+ }));
+ }
+
+ return material;
+ });
+}
+
+// 데이터베이스 저장 함수
+async function saveToDatabase(data: RequestData) {
+ console.log(`데이터베이스 저장 함수가 호출됨. ${data.materials.length}개의 자재 데이터 수신.`);
+
+ try {
+ // 트랜잭션으로 모든 데이터 처리
+ await db.transaction(async (tx) => {
+ for (const material of data.materials) {
+ if (!material.matnr) {
+ console.warn('자재번호(MATNR)가 없는 항목 발견, 건너뜁니다.');
+ continue;
+ }
+
+ // 1. MATL 테이블 Upsert
+ await tx.insert(MATL)
+ .values({
+ MATNR: material.matnr,
+ MBRSH: material.mbrsh || null,
+ MTART: material.mtart || null,
+ LVORM: material.lvorm || null,
+ MEINS: material.meins || null,
+ MATKL: material.matkl || null,
+ BISMT: material.bismt || null,
+ SPART: material.spart || null,
+ PRDHA: material.prdha || null,
+ MSTAE: material.mstae || null,
+ MSTDE: material.mstde || null,
+ BRGEW: material.brgew || null,
+ GEWEI: material.gewei || null,
+ NTGEW: material.ntgew || null,
+ VOLUM: material.volum || null,
+ VOLEH: material.voleh || null,
+ GROES: material.groes || null,
+ LAENG: material.laeng || null,
+ BREIT: material.breit || null,
+ HOEHE: material.hoehe || null,
+ MEABM: material.meabm || null,
+ MAGRV: material.magrv || null,
+ VHART: material.vhart || null,
+ ZZNAME: material.zzname || null,
+ ZZSPEC: material.zzspec || null,
+ ZZDESC: material.zzdesc || null,
+ ZZMMTYP: material.zzmmtyp || null,
+ ZZREGDT: material.zzregdt || null,
+ ZZREGTM: material.zzregtm || null,
+ ZZREGUS: material.zzregus || null,
+ ZZAPPDT: material.zzappdt || null,
+ ZZAPPTM: material.zzapptm || null,
+ ZZAPPUS: material.zzappus || null,
+ ZZLAMDT: material.zzlamdt || null,
+ ZZLAMTM: material.zzlamtm || null,
+ ZZLAMUS: material.zzlamus || null,
+ ZZPRFLG: material.zzprflg || null,
+ ZZDOKAR: material.zzdokar || null,
+ ZZDOKNR: material.zzdoknr || null,
+ ZZDOKTL: material.zzdoktl || null,
+ ZZDOKVR: material.zzdokvr || null,
+ })
+ .onConflictDoUpdate({
+ target: MATL.MATNR,
+ set: {
+ MBRSH: material.mbrsh || null,
+ MTART: material.mtart || null,
+ LVORM: material.lvorm || null,
+ MEINS: material.meins || null,
+ MATKL: material.matkl || null,
+ BISMT: material.bismt || null,
+ SPART: material.spart || null,
+ PRDHA: material.prdha || null,
+ MSTAE: material.mstae || null,
+ MSTDE: material.mstde || null,
+ BRGEW: material.brgew || null,
+ GEWEI: material.gewei || null,
+ NTGEW: material.ntgew || null,
+ VOLUM: material.volum || null,
+ VOLEH: material.voleh || null,
+ GROES: material.groes || null,
+ LAENG: material.laeng || null,
+ BREIT: material.breit || null,
+ HOEHE: material.hoehe || null,
+ MEABM: material.meabm || null,
+ MAGRV: material.magrv || null,
+ VHART: material.vhart || null,
+ ZZNAME: material.zzname || null,
+ ZZSPEC: material.zzspec || null,
+ ZZDESC: material.zzdesc || null,
+ ZZMMTYP: material.zzmmtyp || null,
+ ZZREGDT: material.zzregdt || null,
+ ZZREGTM: material.zzregtm || null,
+ ZZREGUS: material.zzregus || null,
+ ZZAPPDT: material.zzappdt || null,
+ ZZAPPTM: material.zzapptm || null,
+ ZZAPPUS: material.zzappus || null,
+ ZZLAMDT: material.zzlamdt || null,
+ ZZLAMTM: material.zzlamtm || null,
+ ZZLAMUS: material.zzlamus || null,
+ ZZPRFLG: material.zzprflg || null,
+ ZZDOKAR: material.zzdokar || null,
+ ZZDOKNR: material.zzdoknr || null,
+ ZZDOKTL: material.zzdoktl || null,
+ ZZDOKVR: material.zzdokvr || null,
+ updatedAt: new Date(),
+ }
+ });
+
+ // 2. 하위 테이블 데이터 처리 (Upsert)
+ // DESC 테이블 데이터 처리
+ if (material.descriptions && material.descriptions.length > 0) {
+ // 기존 데이터 조회 (해당 자재의 모든 설명)
+ const existingDescs = await tx.select().from(DESC)
+ .where(eq(DESC.MATNR, material.matnr));
+
+ // 설명 데이터 매핑
+ const existingDescsMap = new Map(
+ existingDescs.map(desc => [`${desc.MATNR}-${desc.SPRAS}`, desc])
+ );
+
+ for (const desc of material.descriptions) {
+ if (!desc.matnr && !material.matnr) continue; // 자재번호 필수
+
+ const matnr = desc.matnr || material.matnr;
+ const spras = desc.spras || '';
+ const key = `${matnr}-${spras}`;
+
+ if (existingDescsMap.has(key)) {
+ // 기존 데이터 업데이트
+ await tx.update(DESC)
+ .set({
+ MAKTX: desc.maktx || null,
+ updatedAt: new Date()
+ })
+ .where(eq(DESC.id, existingDescsMap.get(key)!.id));
+ } else {
+ // 신규 데이터 삽입
+ await tx.insert(DESC).values({
+ MATNR: matnr,
+ SPRAS: desc.spras || null,
+ MAKTX: desc.maktx || null,
+ });
+ }
+ }
+ }
+
+ // PLNT 테이블 데이터 처리
+ if (material.plants && material.plants.length > 0) {
+ // 기존 데이터 조회
+ const existingPlants = await tx.select().from(PLNT)
+ .where(eq(PLNT.MATNR, material.matnr));
+
+ // 플랜트 데이터 매핑
+ const existingPlantsMap = new Map(
+ existingPlants.map(plant => [`${plant.MATNR}-${plant.WERKS}`, plant])
+ );
+
+ for (const plant of material.plants) {
+ if (!plant.matnr && !material.matnr) continue; // 자재번호 필수
+ if (!plant.werks) continue; // 플랜트 코드 필수
+
+ const matnr = plant.matnr || material.matnr;
+ const werks = plant.werks;
+ const key = `${matnr}-${werks}`;
+
+ if (existingPlantsMap.has(key)) {
+ // 기존 데이터 업데이트
+ await tx.update(PLNT)
+ .set({
+ LVORM: plant.lvorm || null,
+ MMSTA: plant.mmsta || null,
+ MMSTD: plant.mmstd || null,
+ ZZMTARP: plant.zzmtarp || null,
+ ZZREGDT: plant.zzregdt || null,
+ ZZREGTM: plant.zzregtm || null,
+ ZZREGUS: plant.zzregus || null,
+ ZZLAMDT: plant.zzlamdt || null,
+ ZZLAMTM: plant.zzlamtm || null,
+ ZZLAMUS: plant.zzlamus || null,
+ ZZPRFLG: plant.zzprflg || null,
+ updatedAt: new Date()
+ })
+ .where(eq(PLNT.id, existingPlantsMap.get(key)!.id));
+ } else {
+ // 신규 데이터 삽입
+ await tx.insert(PLNT).values({
+ MATNR: matnr,
+ WERKS: werks,
+ LVORM: plant.lvorm || null,
+ MMSTA: plant.mmsta || null,
+ MMSTD: plant.mmstd || null,
+ ZZMTARP: plant.zzmtarp || null,
+ ZZREGDT: plant.zzregdt || null,
+ ZZREGTM: plant.zzregtm || null,
+ ZZREGUS: plant.zzregus || null,
+ ZZLAMDT: plant.zzlamdt || null,
+ ZZLAMTM: plant.zzlamtm || null,
+ ZZLAMUS: plant.zzlamus || null,
+ ZZPRFLG: plant.zzprflg || null,
+ });
+ }
+ }
+ }
+
+ // UNIT 테이블 데이터 처리
+ if (material.units && material.units.length > 0) {
+ // 기존 데이터 조회
+ const existingUnits = await tx.select().from(UNIT)
+ .where(eq(UNIT.MATNR, material.matnr));
+
+ // 단위 데이터 매핑
+ const existingUnitsMap = new Map(
+ existingUnits.map(unit => [`${unit.MATNR}-${unit.MEINH}`, unit])
+ );
+
+ for (const unit of material.units) {
+ if (!unit.matnr && !material.matnr) continue; // 자재번호 필수
+ if (!unit.meinh) continue; // 단위 코드 필수
+
+ const matnr = unit.matnr || material.matnr;
+ const meinh = unit.meinh;
+ const key = `${matnr}-${meinh}`;
+
+ if (existingUnitsMap.has(key)) {
+ // 기존 데이터 업데이트
+ await tx.update(UNIT)
+ .set({
+ UMREZ: unit.umrez || null,
+ UMREN: unit.umren || null,
+ LAENG: unit.laeng || null,
+ BREIT: unit.breit || null,
+ HOEHE: unit.hoehe || null,
+ MEABM: unit.meabm || null,
+ VOLUM: unit.volum || null,
+ VOLEH: unit.voleh || null,
+ BRGEW: unit.brgew || null,
+ GEWEI: unit.gewei || null,
+ updatedAt: new Date()
+ })
+ .where(eq(UNIT.id, existingUnitsMap.get(key)!.id));
+ } else {
+ // 신규 데이터 삽입
+ await tx.insert(UNIT).values({
+ MATNR: matnr,
+ MEINH: meinh,
+ UMREZ: unit.umrez || null,
+ UMREN: unit.umren || null,
+ LAENG: unit.laeng || null,
+ BREIT: unit.breit || null,
+ HOEHE: unit.hoehe || null,
+ MEABM: unit.meabm || null,
+ VOLUM: unit.volum || null,
+ VOLEH: unit.voleh || null,
+ BRGEW: unit.brgew || null,
+ GEWEI: unit.gewei || null,
+ });
+ }
+ }
+ }
+
+ // CLASSASGN 테이블 데이터 처리
+ if (material.classAssignments && material.classAssignments.length > 0) {
+ // 기존 데이터 조회
+ const existingClassAsgns = await tx.select().from(CLASSASGN)
+ .where(eq(CLASSASGN.MATNR, material.matnr));
+
+ // 클래스 할당 데이터 매핑
+ const existingClassAsgnsMap = new Map(
+ existingClassAsgns.map(cls => [`${cls.MATNR}-${cls.CLASS}-${cls.KLART}`, cls])
+ );
+
+ for (const cls of material.classAssignments) {
+ if (!cls.matnr && !material.matnr) continue; // 자재번호 필수
+ if (!cls.class || !cls.klart) continue; // 클래스 및 유형 필수
+
+ const matnr = cls.matnr || material.matnr;
+ const clsVal = cls.class;
+ const klart = cls.klart;
+ const key = `${matnr}-${clsVal}-${klart}`;
+
+ if (!existingClassAsgnsMap.has(key)) {
+ // 클래스 할당은 기본키 자체가 변경되는 경우가 드물어 신규 삽입만 처리
+ await tx.insert(CLASSASGN).values({
+ MATNR: matnr,
+ CLASS: clsVal,
+ KLART: klart,
+ });
+ }
+ }
+ }
+
+ // CHARASGN 테이블 데이터 처리
+ if (material.characteristicAssignments && material.characteristicAssignments.length > 0) {
+ // 기존 데이터 조회
+ const existingCharAsgns = await tx.select().from(CHARASGN)
+ .where(eq(CHARASGN.MATNR, material.matnr));
+
+ // 특성 할당 데이터 매핑
+ const existingCharAsgnsMap = new Map(
+ existingCharAsgns.map(char =>
+ [`${char.MATNR}-${char.CLASS}-${char.KLART}-${char.ATNAM}`, char]
+ )
+ );
+
+ for (const char of material.characteristicAssignments) {
+ if (!char.matnr && !material.matnr) continue; // 자재번호 필수
+ if (!char.class || !char.klart || !char.atnam) continue; // 클래스, 유형, 특성명 필수
+
+ const matnr = char.matnr || material.matnr;
+ const clsVal = char.class;
+ const klart = char.klart;
+ const atnam = char.atnam;
+ const key = `${matnr}-${clsVal}-${klart}-${atnam}`;
+
+ if (existingCharAsgnsMap.has(key)) {
+ // 기존 데이터 업데이트
+ await tx.update(CHARASGN)
+ .set({
+ ATWRT: char.atwrt || null,
+ ATFLV: char.atflv || null,
+ ATAWE: char.atawe || null,
+ ATFLB: char.atflb || null,
+ ATAW1: char.ataw1 || null,
+ ATBEZ: char.atbez || null,
+ ATWTB: char.atwtb || null,
+ updatedAt: new Date()
+ })
+ .where(eq(CHARASGN.id, existingCharAsgnsMap.get(key)!.id));
+ } else {
+ // 신규 데이터 삽입
+ await tx.insert(CHARASGN).values({
+ MATNR: matnr,
+ CLASS: clsVal,
+ KLART: klart,
+ ATNAM: atnam,
+ ATWRT: char.atwrt || null,
+ ATFLV: char.atflv || null,
+ ATAWE: char.atawe || null,
+ ATFLB: char.atflb || null,
+ ATAW1: char.ataw1 || null,
+ ATBEZ: char.atbez || null,
+ ATWTB: char.atwtb || null,
+ });
+ }
+ }
+ }
+ }
+ });
+
+ console.log(`${data.materials.length}개의 자재 데이터 처리 완료.`);
+ return true;
+ } catch (error) {
+ console.error('데이터베이스 저장 중 오류 발생:', error);
+ throw error;
+ }
+}
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 23:40:02 +0000