diff options
Diffstat (limited to 'app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts')
| -rw-r--r-- | app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts | 122 |
1 files changed, 69 insertions, 53 deletions
diff --git a/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts index fc6bc71f..388c4dc4 100644 --- a/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts +++ b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_EMPLOYEE_MASTER/route.ts @@ -41,7 +41,12 @@ import { createSuccessResponse, replaceSubTableData, withSoapLogging -} from "@/lib/soap/mdg/utils"; +} from "@/lib/soap/utils"; +import { + bulkUpsert, + bulkReplaceSubTableData +} from "@/lib/soap/batch-utils"; + // 스키마에서 직접 타입 추론 type EmpMdgData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG.$inferInsert; @@ -310,65 +315,76 @@ function transformEmployeeData(empData: EmpMdgXML[]): ProcessedEmployeeData[] { * @param processedEmployees 변환된 EMPLOYEE 데이터 배열 */ async function saveToDatabase(processedEmployees: ProcessedEmployeeData[]) { - console.log(`데이터베이스 저장 시작: ${processedEmployees.length}개 사원 데이터`); + console.log(`데이터베이스(배치) 저장 시작: ${processedEmployees.length}개 사원 데이터`); try { await db.transaction(async (tx) => { - for (const employeeData of processedEmployees) { - const { employee, banm, binm, compnm, corpnm, countrynm, deptcode, deptcodePccdnm, - deptnm, dhjobgdnm, gjobdutynm, gjobgrdnm, gjobgrdtype, gjobnm, gnnm, - jobdutynm, jobgrdnm, jobnm, ktlnm, oktlnm, orgbicdnm, orgcompnm, - orgcorpnm, orgdeptnm, orgpdepnm, pdeptnm } = employeeData; - - if (!employee.EMPID) { - console.warn('사원번호(EMPID)가 없는 항목 발견, 건너뜁니다.'); - continue; - } + // 1) 부모 테이블 데이터 준비 + const employeeRows = processedEmployees + .map((e) => e.employee) + .filter((e): e is EmpMdgData => !!e.EMPID); - // 1. CMCTB_EMP_MDG 테이블 Upsert (최상위 테이블 - unique 필드: EMPID) - await tx.insert(EMPLOYEE_MASTER_CMCTB_EMP_MDG) - .values(employee) - .onConflictDoUpdate({ - target: EMPLOYEE_MASTER_CMCTB_EMP_MDG.EMPID, - set: { - ...employee, - updatedAt: new Date(), - } - }); + const empids = employeeRows.map((e) => e.EMPID as string); - // 2. 하위 테이블들 처리 - FK(EMPID) 기준 전체 삭제 후 재삽입 - // 25개 테이블을 병렬 처리로 성능 최적화 - await Promise.all([ - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BANM, banm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BINM, binm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COMPNM, compnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_CORPNM, corpnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COUNTRYNM, countrynm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE, deptcode, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE_PCCDNM, deptcodePccdnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTNM, deptnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DHJOBGDNM, dhjobgdnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBDUTYNM, gjobdutynm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDNM, gjobgrdnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDTYPE, gjobgrdtype, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBNM, gjobnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GNNM, gnnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBDUTYNM, jobdutynm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBGRDNM, jobgrdnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBNM, jobnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_KTLNM, ktlnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_OKTLNM, oktlnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGBICDNM, orgbicdnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCOMPNM, orgcompnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCORPNM, orgcorpnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGDEPTNM, orgdeptnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGPDEPNM, orgpdepnm, 'EMPID', employee.EMPID), - replaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_PDEPTNM, pdeptnm, 'EMPID', employee.EMPID) - ]); - } + // 2) 하위 테이블 데이터 평탄화 + const banm = processedEmployees.flatMap((e) => e.banm); + const binm = processedEmployees.flatMap((e) => e.binm); + const compnm = processedEmployees.flatMap((e) => e.compnm); + const corpnm = processedEmployees.flatMap((e) => e.corpnm); + const countrynm = processedEmployees.flatMap((e) => e.countrynm); + const deptcode = processedEmployees.flatMap((e) => e.deptcode); + const deptcodePccdnm = processedEmployees.flatMap((e) => e.deptcodePccdnm); + const deptnm = processedEmployees.flatMap((e) => e.deptnm); + const dhjobgdnm = processedEmployees.flatMap((e) => e.dhjobgdnm); + const gjobdutynm = processedEmployees.flatMap((e) => e.gjobdutynm); + const gjobgrdnm = processedEmployees.flatMap((e) => e.gjobgrdnm); + const gjobgrdtype = processedEmployees.flatMap((e) => e.gjobgrdtype); + const gjobnm = processedEmployees.flatMap((e) => e.gjobnm); + const gnnm = processedEmployees.flatMap((e) => e.gnnm); + const jobdutynm = processedEmployees.flatMap((e) => e.jobdutynm); + const jobgrdnm = processedEmployees.flatMap((e) => e.jobgrdnm); + const jobnm = processedEmployees.flatMap((e) => e.jobnm); + const ktlnm = processedEmployees.flatMap((e) => e.ktlnm); + const oktlnm = processedEmployees.flatMap((e) => e.oktlnm); + const orgbicdnm = processedEmployees.flatMap((e) => e.orgbicdnm); + const orgcompnm = processedEmployees.flatMap((e) => e.orgcompnm); + const orgcorpnm = processedEmployees.flatMap((e) => e.orgcorpnm); + const orgdeptnm = processedEmployees.flatMap((e) => e.orgdeptnm); + const orgpdepnm = processedEmployees.flatMap((e) => e.orgpdepnm); + const pdeptnm = processedEmployees.flatMap((e) => e.pdeptnm); + + // 3) 데이터베이스 저장 + await bulkUpsert(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG, employeeRows, 'EMPID'); + + // 4) 하위 테이블 데이터 저장 + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BANM, banm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BINM, binm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COMPNM, compnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_CORPNM, corpnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COUNTRYNM, countrynm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE, deptcode, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE_PCCDNM, deptcodePccdnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTNM, deptnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DHJOBGDNM, dhjobgdnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBDUTYNM, gjobdutynm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDNM, gjobgrdnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDTYPE, gjobgrdtype, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBNM, gjobnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GNNM, gnnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBDUTYNM, jobdutynm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBGRDNM, jobgrdnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBNM, jobnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_KTLNM, ktlnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_OKTLNM, oktlnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGBICDNM, orgbicdnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCOMPNM, orgcompnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCORPNM, orgcorpnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGDEPTNM, orgdeptnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGPDEPNM, orgpdepnm, 'EMPID', empids); + await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_PDEPTNM, pdeptnm, 'EMPID', empids); }); - console.log(`데이터베이스 저장 완료: ${processedEmployees.length}개 사원`); + console.log(`데이터베이스(배치) 저장 완료: ${processedEmployees.length}개 사원`); return true; } catch (error) { console.error('데이터베이스 저장 중 오류 발생:', error); |