// lib/vendor-document-list/import-service.ts - DOLCE API 연동 버전 (파일 다운로드 포함) import db from "@/db/db" import { documents, issueStages, contracts, projects, vendors, revisions, documentAttachments } from "@/db/schema" import { eq, and, sql, asc } from "drizzle-orm" import { writeFile, mkdir } from "fs/promises" import { join } from "path" import { v4 as uuidv4 } from "uuid" import { extname } from "path" import * as crypto from "crypto" import { debugError, debugWarn, debugSuccess, debugProcess } from "@/lib/debug-utils" import { getServerSession } from "next-auth/next" import { authOptions } from "@/app/api/auth/[...nextauth]/route" export interface ImportResult { success: boolean newCount: number updatedCount: number skippedCount: number newRevisionsCount: number updatedRevisionsCount: number newAttachmentsCount: number updatedAttachmentsCount: number downloadedFilesCount: number errors?: string[] message?: string } export interface ImportStatus { lastImportAt?: string availableDocuments: number newDocuments: number updatedDocuments: number availableRevisions: number newRevisions: number updatedRevisions: number availableAttachments: number newAttachments: number updatedAttachments: number importEnabled: boolean error?: string } interface DOLCEDocument { CGbn?: string CreateDt: string CreateUserENM: string CreateUserId: string CreateUserNo: string DGbn?: string DegreeGbn?: string DeptGbn?: string Discipline: string DrawingKind: string // B3, B4, B5 DrawingMoveGbn: string DrawingName: string DrawingNo: string GTTInput_PlanDate?: string GTTInput_ResultDate?: string AppDwg_PlanDate?: string AppDwg_ResultDate?: string WorDwg_PlanDate?: string WorDwg_ResultDate?: string GTTPreDwg_PlanDate?: string GTTPreDwg_ResultDate?: string GTTWorkingDwg_PlanDate?: string GTTWorkingDwg_ResultDate?: string FMEAFirst_PlanDate?: string FMEAFirst_ResultDate?: string FMEASecond_PlanDate?: string FMEASecond_ResultDate?: string JGbn?: string Manager: string ManagerENM: string ManagerNo: string ProjectNo: string RegisterGroup: number RegisterGroupId: number SGbn?: string SHIDrawingNo?: string } interface DOLCEDetailDocument { Status: string Category: string // TS, FS CategoryNM: string CategoryENM: string RegisterId: string ProjectNo: string DrawingNo: string RegisterGroupId: number RegisterGroup: number DrawingName: string RegisterSerialNoMax: number RegisterSerialNo: number DrawingUsage: string DrawingUsageNM: string DrawingUsageENM: string RegisterKind: string RegisterKindNM: string RegisterKindENM: string DrawingRevNo: string RegisterDesc: string UploadId: string ManagerNM: string Manager: string UseYn: string RegCompanyCode: string RegCompanyNM: string RegCompanyENM: string CreateUserENM: string CreateUserNM: string CreateUserId: string CreateDt: string ModifyUserId: string ModifyDt: string Discipline: string DrawingKind: string DrawingMoveGbn: string SHIDrawingNo: string Receiver: string SHINote: string OFDC_NO: string | null // OFDC Number for document identification } interface DOLCEFileInfo { FileId: string UploadId: string FileSeq: number FileServerId: string FileTitle: string FileDescription: string FileName: string FileRelativePath: string FileSize: number FileCreateDT: string FileWriteDT: string OwnerUserId: string UseYn: string } /** * Revision 매칭 결과 타입 */ interface RevisionMatchResult { id: number issueStageId: number revision: string uploaderType: string uploaderId: number | null uploaderName: string | null usage: string | null usageType: string | null revisionStatus: string comment: string | null externalUploadId: string | null registerId: string | null serialNo: string | null registerSerialNoMax: string | null createdAt: Date updatedAt: Date } /** * 공통 Revision 매칭 함수 * DOLCE DetailDocument와 로컬 DB를 비교하여 기존 revision을 찾음 * * 매칭 우선순위: * 1. registerId (가장 정확) * 2. externalUploadId (업로드된 revision) * 3. DrawingRevNo + serialNo + OFDC_NO (복합 매칭) * 4. DrawingRevNo + serialNo (OFDC_NO 없는 경우) */ export async function findMatchingRevision( projectId: number, docNumber: string, detailDoc: DOLCEDetailDocument, issueStageId?: number ): Promise { let existingRevision: RevisionMatchResult | null = null // 1차: registerId로 조회 (가장 정확한 매칭) if (detailDoc.RegisterId) { const query = issueStageId ? db.select().from(revisions).where( and( eq(revisions.issueStageId, issueStageId), eq(revisions.registerId, detailDoc.RegisterId) ) ) : db.select().from(revisions).where(eq(revisions.registerId, detailDoc.RegisterId)) const results = await query.limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by registerId: ${detailDoc.RegisterId} → local ID: ${existingRevision.id}`) return existingRevision } else { console.log(`❌ NOT found by registerId: ${detailDoc.RegisterId}`) } } // 2차: externalUploadId로 조회 (업로드했던 revision 매칭) if (!existingRevision && detailDoc.UploadId) { if (issueStageId) { const results = await db.select().from(revisions).where( and( eq(revisions.issueStageId, issueStageId), eq(revisions.externalUploadId, detailDoc.UploadId) ) ).limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by externalUploadId: ${detailDoc.UploadId} → local ID: ${existingRevision.id}`) return existingRevision } } else { const results = await db.select({ id: revisions.id, issueStageId: revisions.issueStageId, revision: revisions.revision, uploaderType: revisions.uploaderType, uploaderId: revisions.uploaderId, uploaderName: revisions.uploaderName, usage: revisions.usage, usageType: revisions.usageType, revisionStatus: revisions.revisionStatus, comment: revisions.comment, externalUploadId: revisions.externalUploadId, registerId: revisions.registerId, serialNo: revisions.serialNo, registerSerialNoMax: revisions.registerSerialNoMax, createdAt: revisions.createdAt, updatedAt: revisions.updatedAt, }) .from(revisions) .innerJoin(issueStages, eq(issueStages.id, revisions.issueStageId)) .innerJoin(documents, eq(documents.id, issueStages.documentId)) .where( and( eq(documents.projectId, projectId), eq(documents.docNumber, docNumber), eq(revisions.externalUploadId, detailDoc.UploadId) ) ) .limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by externalUploadId: ${detailDoc.UploadId} → local ID: ${existingRevision.id}`) return existingRevision } } console.log(`❌ NOT found by externalUploadId: ${detailDoc.UploadId}`) } // 3차: DrawingRevNo + serialNo + OFDC_NO로 조회 (OFDC_NO가 있는 경우 더 정확한 매칭) if (!existingRevision && detailDoc.DrawingRevNo && detailDoc.RegisterSerialNo && detailDoc.OFDC_NO) { if (issueStageId) { const results = await db.select().from(revisions).where( and( eq(revisions.issueStageId, issueStageId), eq(revisions.revision, detailDoc.DrawingRevNo), eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)), eq(revisions.ofdcNo, detailDoc.OFDC_NO) ) ).limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO} → local ID: ${existingRevision.id}`) return existingRevision } } else { const results = await db.select({ id: revisions.id, issueStageId: revisions.issueStageId, revision: revisions.revision, uploaderType: revisions.uploaderType, uploaderId: revisions.uploaderId, uploaderName: revisions.uploaderName, usage: revisions.usage, usageType: revisions.usageType, revisionStatus: revisions.revisionStatus, comment: revisions.comment, externalUploadId: revisions.externalUploadId, registerId: revisions.registerId, serialNo: revisions.serialNo, registerSerialNoMax: revisions.registerSerialNoMax, createdAt: revisions.createdAt, updatedAt: revisions.updatedAt, }) .from(revisions) .innerJoin(issueStages, eq(issueStages.id, revisions.issueStageId)) .innerJoin(documents, eq(documents.id, issueStages.documentId)) .where( and( eq(documents.projectId, projectId), eq(documents.docNumber, docNumber), eq(revisions.revision, detailDoc.DrawingRevNo), eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)), eq(revisions.ofdcNo, detailDoc.OFDC_NO) ) ) .limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO} → local ID: ${existingRevision.id}`) return existingRevision } } console.log(`❌ NOT found by DrawingRevNo+serialNo+OFDC_NO: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO}`) } // 4차: DrawingRevNo + serialNo로 조회 (OFDC_NO가 없는 경우 fallback) if (!existingRevision && detailDoc.DrawingRevNo && detailDoc.RegisterSerialNo) { if (issueStageId) { const results = await db.select().from(revisions).where( and( eq(revisions.issueStageId, issueStageId), eq(revisions.revision, detailDoc.DrawingRevNo), eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)) ) ).limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo} → local ID: ${existingRevision.id}`) return existingRevision } } else { const results = await db.select({ id: revisions.id, issueStageId: revisions.issueStageId, revision: revisions.revision, uploaderType: revisions.uploaderType, uploaderId: revisions.uploaderId, uploaderName: revisions.uploaderName, usage: revisions.usage, usageType: revisions.usageType, revisionStatus: revisions.revisionStatus, comment: revisions.comment, externalUploadId: revisions.externalUploadId, registerId: revisions.registerId, serialNo: revisions.serialNo, registerSerialNoMax: revisions.registerSerialNoMax, createdAt: revisions.createdAt, updatedAt: revisions.updatedAt, }) .from(revisions) .innerJoin(issueStages, eq(issueStages.id, revisions.issueStageId)) .innerJoin(documents, eq(documents.id, issueStages.documentId)) .where( and( eq(documents.projectId, projectId), eq(documents.docNumber, docNumber), eq(revisions.revision, detailDoc.DrawingRevNo), eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)) ) ) .limit(1) if (results.length > 0) { existingRevision = results[0] as RevisionMatchResult console.log(`✅ Found revision by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo} → local ID: ${existingRevision.id}`) return existingRevision } } console.log(`❌ NOT found by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}`) } // 최종 결과 로그 if (!existingRevision) { console.log(`🆕 No matching revision found for RegisterId: ${detailDoc.RegisterId} (${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}/${detailDoc.OFDC_NO || 'N/A'})`) } return existingRevision } class ImportService { private readonly DES_KEY = Buffer.from("4fkkdijg", "ascii") /** * DOLCE 시스템에서 문서 목록 가져오기 */ async importFromExternalSystem( projectId: number, // ✅ projectId sourceSystem: string = 'DOLCE' ): Promise { try { console.log('\n') console.log('🚀'.repeat(40)) console.log('🚀 importFromExternalSystem 호출됨!') console.log('🚀'.repeat(40)) debugProcess(`DOLCE 가져오기 시작`, { projectId, sourceSystem }) // 🔥 세션을 한 번만 가져와서 재사용 const session = await getServerSession(authOptions) if (!session?.user?.companyId) { debugError(`세션 없음 - 인증 필요`, { projectId }) throw new Error("인증이 필요합니다.") } const vendorId = Number(session.user.companyId) debugProcess(`세션 조회 완료`, { vendorId, userId: session.user.id }) // 1. 계약 정보를 통해 프로젝트 코드와 벤더 코드 조회 const contractInfo = await this.getContractInfoByProjectId(projectId, vendorId) if (!contractInfo?.projectCode || !contractInfo?.vendorCode || !contractInfo?.contractId) { debugError(`계약 정보 없음`, { projectId, vendorId }) throw new Error(`Contract info not found for project ${projectId}`) } const contractId = contractInfo.contractId // contract.id를 가져옴 debugProcess(`계약 정보 조회 완료`, { contractId, projectId, projectCode: contractInfo.projectCode, vendorCode: contractInfo.vendorCode }) // 2. 각 drawingKind별로 데이터 조회 const allDocuments: DOLCEDocument[] = [] const drawingKinds = ['B3', 'B4', 'B5'] for (const drawingKind of drawingKinds) { try { const documents = await this.fetchFromDOLCE( contractInfo.projectCode, contractInfo.vendorCode, drawingKind ) allDocuments.push(...documents) debugSuccess(`${drawingKind} 문서 조회 완료`, { drawingKind, documentCount: documents.length }) } catch (error) { debugWarn(`${drawingKind} 문서 조회 실패`, { drawingKind, error }) // 개별 drawingKind 실패는 전체 실패로 처리하지 않음 } } if (allDocuments.length === 0) { debugProcess(`가져올 문서 없음`, { contractId, projectId }) return { success: true, newCount: 0, updatedCount: 0, skippedCount: 0, newRevisionsCount: 0, updatedRevisionsCount: 0, newAttachmentsCount: 0, updatedAttachmentsCount: 0, downloadedFilesCount: 0, message: '가져올 새로운 데이터가 없습니다.' } } debugProcess(`전체 문서 수`, { contractId, projectId, totalDocuments: allDocuments.length, byDrawingKind: { B3: allDocuments.filter(d => d.DrawingKind === 'B3').length, B4: allDocuments.filter(d => d.DrawingKind === 'B4').length, B5: allDocuments.filter(d => d.DrawingKind === 'B5').length } }) let newCount = 0 let updatedCount = 0 let skippedCount = 0 let newRevisionsCount = 0 let updatedRevisionsCount = 0 let newAttachmentsCount = 0 let updatedAttachmentsCount = 0 let downloadedFilesCount = 0 const errors: string[] = [] // 3. 각 문서 동기화 처리 for (const dolceDoc of allDocuments) { try { debugProcess(`문서 동기화 시작`, { drawingNo: dolceDoc.DrawingNo, drawingKind: dolceDoc.DrawingKind }) const result = await this.syncSingleDocument(contractId, projectId, vendorId, dolceDoc, sourceSystem) if (result === 'NEW') { newCount++ // B4 문서의 경우 이슈 스테이지 자동 생성 if (dolceDoc.DrawingKind === 'B4') { await this.createIssueStagesForB4Document(dolceDoc.DrawingNo, projectId, dolceDoc) } if (dolceDoc.DrawingKind === 'B3') { await this.createIssueStagesForB3Document(dolceDoc.DrawingNo, projectId, dolceDoc) } if (dolceDoc.DrawingKind === 'B5') { await this.createIssueStagesForB5Document(dolceDoc.DrawingNo, projectId, dolceDoc) } } else if (result === 'UPDATED') { updatedCount++ } else { skippedCount++ } // 4. revisions 동기화 처리 try { const revisionResult = await this.syncDocumentRevisions( projectId, dolceDoc ) newRevisionsCount += revisionResult.newCount updatedRevisionsCount += revisionResult.updatedCount // 5. 파일 첨부 동기화 처리 (Category가 FS인 것만) console.log(`📎 첨부파일 동기화 시도: ${dolceDoc.DrawingNo} [${dolceDoc.Discipline}]`) const attachmentResult = await this.syncDocumentAttachments( dolceDoc ) console.log(`📎 첨부파일 동기화 결과:`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, new: attachmentResult.newCount, updated: attachmentResult.updatedCount, downloaded: attachmentResult.downloadedCount }) newAttachmentsCount += attachmentResult.newCount updatedAttachmentsCount += attachmentResult.updatedCount downloadedFilesCount += attachmentResult.downloadedCount } catch (revisionError) { debugWarn(`리비전 동기화 실패`, { drawingNo: dolceDoc.DrawingNo, error: revisionError }) // revisions 동기화 실패는 에러 로그만 남기고 계속 진행 } } catch (error) { const errorMessage = error instanceof Error ? error.message : 'Unknown error' const errorStack = error instanceof Error ? error.stack : undefined debugError(`❌ 문서 동기화 실패`, { drawingNo: dolceDoc.DrawingNo, drawingKind: dolceDoc.DrawingKind, discipline: dolceDoc.Discipline, errorMessage, errorStack }) console.error(`❌ 문서 동기화 실패 상세:`, { 문서번호: dolceDoc.DrawingNo, 문서종류: dolceDoc.DrawingKind, discipline: dolceDoc.Discipline, 에러메시지: errorMessage, 스택: errorStack }) errors.push(`Document ${dolceDoc.DrawingNo}: ${errorMessage}`) skippedCount++ } } debugSuccess(`DOLCE 가져오기 완료`, { contractId, projectId, newCount, updatedCount, skippedCount, newRevisionsCount, updatedRevisionsCount, newAttachmentsCount, updatedAttachmentsCount, downloadedFilesCount, errorCount: errors.length }) return { success: errors.length === 0, newCount, updatedCount, skippedCount, newRevisionsCount, updatedRevisionsCount, newAttachmentsCount, updatedAttachmentsCount, downloadedFilesCount, errors: errors.length > 0 ? errors : undefined, message: `가져오기 완료: 신규 ${newCount}건, 업데이트 ${updatedCount}건, 리비전 신규 ${newRevisionsCount}건, 리비전 업데이트 ${updatedRevisionsCount}건, 파일 다운로드 ${downloadedFilesCount}건` } } catch (error) { debugError(`DOLCE 가져오기 실패`, { projectId, error }) throw error } } /** * 프로젝트 ID로 계약 정보 조회 */ private async getContractInfoByProjectId(projectId: number, vendorId: number): Promise<{ contractId: number; // 🔥 contract.id 반환 projectCode: string; vendorCode: string; } | null> { const [result] = await db .select({ contractId: contracts.id, // 🔥 contract.id 가져오기 projectCode: projects.code, vendorCode: vendors.vendorCode }) .from(contracts) .innerJoin(projects, eq(contracts.projectId, projects.id)) .innerJoin(vendors, eq(contracts.vendorId, vendors.id)) .where(and( eq(contracts.projectId, projectId), // ✅ projects.id로 조회 eq(contracts.vendorId, vendorId) )) .limit(1) return result?.projectCode && result?.vendorCode ? { contractId: result.contractId, // 🔥 contract.id 반환 projectCode: result.projectCode, vendorCode: result.vendorCode } : null } /** * DOLCE API에서 문서 목록 데이터 조회 */ private async fetchFromDOLCE( projectCode: string, vendorCode: string, drawingKind: string ): Promise { const endpoint = process.env.DOLCE_DOC_LIST_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/DwgReceiptMgmt' const requestBody = { project: projectCode, drawingKind: drawingKind, // B3, B4, B5 drawingMoveGbn: "", drawingNo: "", drawingName: "", drawingVendor: vendorCode } console.log(`Fetching from DOLCE: ${projectCode} - ${drawingKind} = ${vendorCode}`) try { const response = await fetch(endpoint, { method: 'POST', headers: { 'Content-Type': 'application/json', }, body: JSON.stringify(requestBody) }) if (!response.ok) { const errorText = await response.text() throw new Error(`DOLCE API failed: HTTP ${response.status} - ${errorText}`) } const data = await response.json() // DOLCE API 응답 구조에 맞게 처리 if (data.DwgReceiptMgmtResult) { const result = data.DwgReceiptMgmtResult // drawingKind에 따라 적절한 배열에서 데이터 추출 let documents: DOLCEDocument[] = [] switch (drawingKind) { case 'B3': documents = result.VendorDwgList || [] break case 'B4': documents = result.GTTDwgList || [] break case 'B5': documents = result.FMEADwgList || [] break default: console.warn(`Unknown drawingKind: ${drawingKind}`) documents = [] } console.log(`Found ${documents.length} documents for ${drawingKind}`) return documents as DOLCEDocument[] } else { console.warn(`Unexpected DOLCE response structure:`, data) return [] } } catch (error) { console.error(`DOLCE API call failed for ${projectCode}/${drawingKind}:`, error) throw error } } /** * DOLCE API에서 문서 상세 정보 조회 (revisions 데이터) */ private async fetchDetailFromDOLCE( projectCode: string, drawingNo: string, discipline: string, drawingKind: string ): Promise { const endpoint = process.env.DOLCE_DOC_DETAIL_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/DetailDwgReceiptMgmt' const requestBody = { project: projectCode, drawingNo: drawingNo, discipline: discipline, drawingKind: drawingKind } console.log(`Fetching detail from DOLCE: ${projectCode} - ${drawingNo}`) try { const response = await fetch(endpoint, { method: 'POST', headers: { 'Content-Type': 'application/json', }, body: JSON.stringify(requestBody) }) if (!response.ok) { const errorText = await response.text() throw new Error(`DOLCE Detail API failed: HTTP ${response.status} - ${errorText}`) } const data = await response.json() // DOLCE Detail API 응답 구조에 맞게 처리 if (data.DetailDwgReceiptMgmtResult) { const documents = data.DetailDwgReceiptMgmtResult as DOLCEDetailDocument[] console.log(`Found ${documents.length} detail records for ${drawingNo}`) return documents } else { console.warn(`Unexpected DOLCE Detail response structure:`, data) return [] } } catch (error) { console.error(`DOLCE Detail API call failed for ${drawingNo}:`, error) throw error } } /** * DOLCE API에서 파일 정보 조회 */ private async fetchFileInfoFromDOLCE(uploadId: string): Promise { const endpoint = process.env.DOLCE_FILE_INFO_API_URL || 'http://60.100.99.217:1111/Services/VDCSWebService.svc/FileInfoList' const requestBody = { uploadId: uploadId } debugProcess(`DOLCE 파일 정보 조회 시작`, { uploadId, endpoint }) try { const response = await fetch(endpoint, { method: 'POST', headers: { 'Content-Type': 'application/json', }, body: JSON.stringify(requestBody) }) if (!response.ok) { const errorText = await response.text() debugError(`DOLCE FileInfo API 실패`, { uploadId, status: response.status, error: errorText }) throw new Error(`DOLCE FileInfo API failed: HTTP ${response.status} - ${errorText}`) } const data = await response.json() // DOLCE FileInfo API 응답 구조에 맞게 처리 if (data.FileInfoListResult) { const files = data.FileInfoListResult as DOLCEFileInfo[] const activeFiles = files.filter(f => f.UseYn === 'True') debugSuccess(`DOLCE 파일 정보 조회 완료`, { uploadId, totalFiles: files.length, activeFiles: activeFiles.length }) return files } else { debugWarn(`예상치 못한 DOLCE FileInfo 응답 구조`, { uploadId, data }) return [] } } catch (error) { debugError(`DOLCE FileInfo API 호출 실패`, { uploadId, error }) throw error } } /** * DES 암호화 (C# DESCryptoServiceProvider 호환) */ private encryptDES(text: string): string { try { const cipher = crypto.createCipheriv('des-ecb', this.DES_KEY, '') cipher.setAutoPadding(true) let encrypted = cipher.update(text, 'utf8', 'base64') encrypted += cipher.final('base64') // + 문자를 |||로 치환 return encrypted.replace(/\+/g, '|||') } catch (error) { console.error('DES encryption failed:', error) throw error } } /** * DOLCE에서 파일 다운로드 */ private async downloadFileFromDOLCE( fileId: string, userId: string, fileName: string ): Promise { try { // 암호화 문자열 생성: FileId↔UserId↔FileName const encryptString = `${fileId}↔${userId}↔${fileName}` const encryptedKey = this.encryptDES(encryptString) const downloadUrl = `${process.env.DOLCE_DOWNLOAD_URL}?key=${encryptedKey}` ||`http://60.100.99.217:1111/Download.aspx?key=${encryptedKey}` debugProcess(`DOLCE 파일 다운로드 시작`, { fileName, fileId, userId, encryptedKey, downloadUrl }) const response = await fetch(downloadUrl, { method: 'GET', headers: { 'User-Agent': 'DOLCE-Integration-Service' } }) if (!response.ok) { debugError(`DOLCE 다운로드 실패`, { fileName, status: response.status, url: downloadUrl }) throw new Error(`File download failed: HTTP ${response.status}`) } const buffer = Buffer.from(await response.arrayBuffer()) debugSuccess(`DOLCE 파일 다운로드 완료`, { fileName, fileSize: buffer.length, fileId }) return buffer } catch (error) { debugError(`DOLCE 파일 다운로드 실패`, { fileName, fileId, error }) throw error } } /** * 로컬 파일 시스템에 파일 저장 */ private async saveFileToLocal( buffer: Buffer, originalFileName: string ): Promise<{ fileName: string; filePath: string; fileSize: number }> { try { const baseDir = join(process.cwd(), "public", "documents") // 디렉토리가 없으면 생성 await mkdir(baseDir, { recursive: true }) const ext = extname(originalFileName) const fileName = uuidv4() + ext const fullPath = join(baseDir, fileName) const relativePath = "/documents/" + fileName await writeFile(fullPath, buffer) debugSuccess(`로컬 파일 저장 완료`, { originalFileName, savedFileName: fileName, filePath: relativePath, fileSize: buffer.length }) return { fileName: originalFileName, filePath: relativePath, fileSize: buffer.length } } catch (error) { debugError(`로컬 파일 저장 실패`, { originalFileName, error }) throw error } } /** * 단일 문서 동기화 */ private async syncSingleDocument( contractId: number, // 🔥 contractId 추가 projectId: number, vendorId: number, dolceDoc: DOLCEDocument, sourceSystem: string ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> { debugProcess(`📄 문서 동기화 처리 중`, { contractId, projectId, vendorId, drawingNo: dolceDoc.DrawingNo, drawingKind: dolceDoc.DrawingKind, discipline: dolceDoc.Discipline }) // 기존 문서 조회 (문서 번호로) // ✅ projectId + externalDocumentId + discipline로 조회 (유니크 인덱스와 일치) const existingDoc = await db .select() .from(documents) .where(and( eq(documents.projectId, projectId), eq(documents.externalDocumentId, dolceDoc.DrawingNo), // externalDocumentId 사용 eq(documents.discipline, dolceDoc.Discipline), eq(documents.externalSystemType, sourceSystem) )) .limit(1) debugProcess(`🔍 기존 문서 조회 결과`, { projectId, contractId, drawingNo: dolceDoc.DrawingNo, externalDocumentId: dolceDoc.DrawingNo, found: existingDoc.length > 0, existingId: existingDoc.length > 0 ? existingDoc[0].id : null }) // DOLCE 문서를 DB 스키마에 맞게 변환 const documentData = { contractId, // 🔥 contractId 추가 - 유니크 인덱스에 필수! projectId, vendorId, docNumber: dolceDoc.DrawingNo, title: dolceDoc.DrawingName, status: 'ACTIVE', // DOLCE 전용 필드들 drawingKind: dolceDoc.DrawingKind, drawingMoveGbn: dolceDoc.DrawingMoveGbn, discipline: dolceDoc.Discipline, // 외부 시스템 정보 externalDocumentId: dolceDoc.DrawingNo, // DOLCE에서는 DrawingNo가 ID 역할 externalSystemType: sourceSystem, externalSyncedAt: new Date(), // B4 전용 필드들 cGbn: dolceDoc.CGbn, dGbn: dolceDoc.DGbn, degreeGbn: dolceDoc.DegreeGbn, deptGbn: dolceDoc.DeptGbn, jGbn: dolceDoc.JGbn, sGbn: dolceDoc.SGbn, // 추가 정보 shiDrawingNo: dolceDoc.SHIDrawingNo, manager: dolceDoc.Manager, managerENM: dolceDoc.ManagerENM, managerNo: dolceDoc.ManagerNo, registerGroup: dolceDoc.RegisterGroup, registerGroupId: dolceDoc.RegisterGroupId, // 생성자 정보 createUserNo: dolceDoc.CreateUserNo, createUserId: dolceDoc.CreateUserId, createUserENM: dolceDoc.CreateUserENM, updatedAt: new Date() } if (existingDoc.length > 0) { // 업데이트 필요 여부 확인 const existing = existingDoc[0] const hasChanges = existing.title !== documentData.title || existing.drawingMoveGbn !== documentData.drawingMoveGbn || existing.manager !== documentData.manager if (hasChanges) { debugProcess(`🔄 문서 업데이트 시작`, { drawingNo: dolceDoc.DrawingNo, existingId: existing.id, changes: { title: existing.title !== documentData.title, drawingMoveGbn: existing.drawingMoveGbn !== documentData.drawingMoveGbn, manager: existing.manager !== documentData.manager } }) await db .update(documents) .set(documentData) .where(eq(documents.id, existing.id)) debugSuccess(`✅ 문서 업데이트 완료`, { drawingNo: dolceDoc.DrawingNo, documentId: existing.id }) return 'UPDATED' } else { debugProcess(`⏭️ 문서 변경사항 없음 - 스킵`, { drawingNo: dolceDoc.DrawingNo, documentId: existing.id }) return 'SKIPPED' } } else { // 새 문서 생성 debugProcess(`➕ 새 문서 생성 시작`, { drawingNo: dolceDoc.DrawingNo, drawingKind: dolceDoc.DrawingKind, title: dolceDoc.DrawingName }) const [newDoc] = await db .insert(documents) .values({ ...documentData, createdAt: new Date() }) .returning({ id: documents.id }) debugSuccess(`✅ 새 문서 생성 완료`, { drawingNo: dolceDoc.DrawingNo, documentId: newDoc.id, drawingKind: dolceDoc.DrawingKind }) return 'NEW' } } /** * 문서의 revisions 동기화 */ private async syncDocumentRevisions( projectId: number, dolceDoc: DOLCEDocument ): Promise<{ newCount: number; updatedCount: number }> { try { // 1. 상세 정보 조회 const detailDocs = await this.fetchDetailFromDOLCE( dolceDoc.ProjectNo, dolceDoc.DrawingNo, dolceDoc.Discipline, dolceDoc.DrawingKind ) if (detailDocs.length === 0) { console.log(`No detail data found for ${dolceDoc.DrawingNo}`) return { newCount: 0, updatedCount: 0 } } // 2. 해당 문서의 issueStages 조회 const documentRecord = await db .select({ id: documents.id }) .from(documents) .where(and( eq(documents.projectId, projectId), eq(documents.docNumber, dolceDoc.DrawingNo), eq(documents.discipline, dolceDoc.Discipline), )) .limit(1) if (documentRecord.length === 0) { throw new Error(`Document not found: ${dolceDoc.DrawingNo}`) } const documentId = documentRecord[0].id const issueStagesList = await db .select() .from(issueStages) .where(eq(issueStages.documentId, documentId)) .orderBy(asc(issueStages.stageOrder)) // 순서대로 정렬 console.log(`📋 Issue Stages 목록:`, { drawingNo: dolceDoc.DrawingNo, documentId, totalStages: issueStagesList.length, stages: issueStagesList.map(s => ({ id: s.id, name: s.stageName, order: s.stageOrder, status: s.stageStatus })) }) let newCount = 0 let updatedCount = 0 // 3. 각 상세 데이터에 대해 revision 동기화 for (const detailDoc of detailDocs) { try { console.log(`🔄 Revision 동기화 시도:`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, registerId: detailDoc.RegisterId, drawingRevNo: detailDoc.DrawingRevNo, registerSerialNo: detailDoc.RegisterSerialNo, registerGroupId: detailDoc.RegisterGroupId, registerGroup: detailDoc.RegisterGroup, category: detailDoc.Category, drawingUsage: detailDoc.DrawingUsage, registerKind: detailDoc.RegisterKind }) // issueStage 매칭 로직 (여러 fallback) let matchingStage = null // 1. RegisterGroupId가 유효한 경우 (> 0) ID로 매칭 if (detailDoc.RegisterGroupId > 0) { matchingStage = issueStagesList.find(stage => stage.id === detailDoc.RegisterGroupId) if (matchingStage) { console.log(`✅ Stage 매칭 (RegisterGroupId):`, { registerId: detailDoc.RegisterId, stageId: matchingStage.id, stageName: matchingStage.stageName, method: 'RegisterGroupId' }) } } // 2. stageName으로 매칭 시도 (DrawingUsage 기반) if (!matchingStage && detailDoc.DrawingUsage) { const usageKeywords: Record = { 'SUB': ['제출', 'submission', 'submit', 'SUB'], 'WOR': ['작업', 'work', 'working', 'WOR'], 'REV': ['검토', 'review', 'REV'], 'APP': ['승인', 'approval', 'approve', 'APP'] } const keywords = usageKeywords[detailDoc.DrawingUsage] || [] matchingStage = issueStagesList.find(stage => keywords.some(keyword => stage.stageName?.toLowerCase().includes(keyword.toLowerCase()) ) ) if (matchingStage) { console.log(`✅ Stage 매칭 (DrawingUsage):`, { registerId: detailDoc.RegisterId, stageId: matchingStage.id, stageName: matchingStage.stageName, drawingUsage: detailDoc.DrawingUsage, method: 'DrawingUsage keyword' }) } } // 3. Category로 매칭 시도 if (!matchingStage && detailDoc.Category) { const categoryKeywords: Record = { 'FS': ['발신', 'from shi', 'outgoing'], 'TS': ['수신', 'to shi', 'incoming'] } const keywords = categoryKeywords[detailDoc.Category] || [] matchingStage = issueStagesList.find(stage => keywords.some(keyword => stage.stageName?.toLowerCase().includes(keyword.toLowerCase()) ) ) if (matchingStage) { console.log(`✅ Stage 매칭 (Category):`, { registerId: detailDoc.RegisterId, stageId: matchingStage.id, stageName: matchingStage.stageName, category: detailDoc.Category, method: 'Category keyword' }) } } // 4. Fallback: stageOrder가 가장 낮은 것 (첫 번째 단계) if (!matchingStage && issueStagesList.length > 0) { matchingStage = issueStagesList[0] console.warn(`⚠️ Stage 매칭 실패 - Fallback 사용:`, { registerId: detailDoc.RegisterId, stageId: matchingStage.id, stageName: matchingStage.stageName, method: 'fallback (first stage)' }) } if (!matchingStage) { console.warn(`⚠️ Issue Stage 없음 - Revision 생성 불가:`, { drawingNo: dolceDoc.DrawingNo, registerId: detailDoc.RegisterId, registerGroupId: detailDoc.RegisterGroupId, availableStages: issueStagesList.length }) continue } const result = await this.syncSingleRevision( matchingStage.id, detailDoc, projectId, dolceDoc.DrawingNo ) console.log(`✅ Revision 동기화 완료:`, { registerId: detailDoc.RegisterId, result }) if (result === 'NEW') { newCount++ } else if (result === 'UPDATED') { updatedCount++ } } catch (error) { console.error(`❌ Revision 동기화 실패:`, { drawingNo: dolceDoc.DrawingNo, registerId: detailDoc.RegisterId, error: error instanceof Error ? error.message : String(error), stack: error instanceof Error ? error.stack : undefined }) } } return { newCount, updatedCount } } catch (error) { console.error(`Failed to sync revisions for ${dolceDoc.DrawingNo}:`, error) throw error } } /** * 문서의 첨부파일 동기화 (Category가 FS인 것만) */ private async syncDocumentAttachments( dolceDoc: DOLCEDocument ): Promise<{ newCount: number; updatedCount: number; downloadedCount: number }> { try { debugProcess(`문서 첨부파일 동기화 시작`, { drawingNo: dolceDoc.DrawingNo, drawingKind: dolceDoc.DrawingKind, discipline: dolceDoc.Discipline }) // 1. 상세 정보 조회 const detailDocs = await this.fetchDetailFromDOLCE( dolceDoc.ProjectNo, dolceDoc.DrawingNo, dolceDoc.Discipline, dolceDoc.DrawingKind ) // 2. Category가 'FS'인 것만 필터링 const fsDetailDocs = detailDocs.filter(doc => doc.Category === 'FS') if (fsDetailDocs.length === 0) { debugProcess(`FS 카테고리 문서 없음`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline }) return { newCount: 0, updatedCount: 0, downloadedCount: 0 } } debugProcess(`FS 문서 발견`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, totalDetails: detailDocs.length, fsDetails: fsDetailDocs.length }) let newCount = 0 let updatedCount = 0 let downloadedCount = 0 // 3. 각 FS 문서에 대해 파일 첨부 동기화 for (const detailDoc of fsDetailDocs) { try { if (!detailDoc.UploadId || detailDoc.UploadId.trim() === '') { debugProcess(`UploadId 없음`, { registerId: detailDoc.RegisterId }) continue } // 4. 해당 revision 조회 const revisionRecord = await db .select({ id: revisions.id }) .from(revisions) .where(eq(revisions.registerId, detailDoc.RegisterId)) .limit(1) if (revisionRecord.length === 0) { debugWarn(`⚠️ Revision 없음 - 첨부파일 처리 건너뜀`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, registerId: detailDoc.RegisterId, uploadId: detailDoc.UploadId, drawingRevNo: detailDoc.DrawingRevNo, registerSerialNo: detailDoc.RegisterSerialNo, message: 'Revision이 DB에 없습니다. syncDocumentRevisions에서 생성 실패했을 가능성이 있습니다.' }) // 🔍 디버깅: 파일 정보가 있는지 확인 try { const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId) if (fileInfos.length > 0) { console.warn(`⚠️ Orphan 파일 발견 (Revision 없음):`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, registerId: detailDoc.RegisterId, uploadId: detailDoc.UploadId, fileCount: fileInfos.length, files: fileInfos.map(f => ({ fileId: f.FileId, fileName: f.FileName, fileSize: f.FileSize })), message: 'API에는 파일이 있지만 Revision이 없어서 처리할 수 없습니다.' }) } } catch (error) { console.error(`파일 정보 조회 실패 (Revision 없음):`, error) } continue } const revisionId = revisionRecord[0].id // 5. 파일 정보 조회 const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId) console.log(`📂 파일 정보 조회 완료:`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, uploadId: detailDoc.UploadId, totalFiles: fileInfos.length, activeFiles: fileInfos.filter(f => f.UseYn === 'True').length, files: fileInfos.map(f => ({ fileName: f.FileName, fileSize: f.FileSize, fileId: f.FileId, useYn: f.UseYn })) }) for (const fileInfo of fileInfos) { console.log(`🔍 파일 처리 시작:`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, fileName: fileInfo.FileName, fileId: fileInfo.FileId, useYn: fileInfo.UseYn, revisionId }) if (fileInfo.UseYn !== 'True') { debugProcess(`비활성 파일 스킵`, { fileName: fileInfo.FileName }) continue } try { const result = await this.syncSingleAttachment( revisionId, fileInfo, detailDoc.CreateUserId ) if (result === 'NEW') { newCount++ downloadedCount++ } else if (result === 'UPDATED') { updatedCount++ } } catch (attachmentError) { debugError(`⚠️ 개별 첨부파일 동기화 실패 (계속 진행)`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, fileName: fileInfo.FileName, fileId: fileInfo.FileId, revisionId, registerId: detailDoc.RegisterId, error: attachmentError, errorMessage: attachmentError instanceof Error ? attachmentError.message : String(attachmentError) }) // 개별 첨부파일 실패는 전체 프로세스를 중단하지 않음 continue } } } catch (error) { debugError(`첨부파일 동기화 실패`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, registerId: detailDoc.RegisterId, error }) } } debugSuccess(`문서 첨부파일 동기화 완료`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, newCount, updatedCount, downloadedCount }) return { newCount, updatedCount, downloadedCount } } catch (error) { debugError(`문서 첨부파일 동기화 실패`, { drawingNo: dolceDoc.DrawingNo, discipline: dolceDoc.Discipline, error }) throw error } } /** * 단일 첨부파일 동기화 */ private async syncSingleAttachment( revisionId: number, fileInfo: DOLCEFileInfo, userId: string ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> { try { debugProcess(`단일 첨부파일 동기화 시작`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, revisionId, userId }) // 기존 첨부파일 조회 (FileId로) const existingAttachment = await db .select() .from(documentAttachments) .where(and( eq(documentAttachments.revisionId, revisionId), eq(documentAttachments.fileId, fileInfo.FileId) )) .limit(1) if (existingAttachment.length > 0) { // ✅ 변경사항 체크 (fileName, fileSize) const existing = existingAttachment[0] // 타입 안전 비교 (fileName은 문자열, fileSize는 숫자로 변환) const fileNameMatch = existing.fileName === fileInfo.FileName const fileSizeMatch = Number(existing.fileSize) === Number(fileInfo.FileSize) debugProcess(`첨부파일 비교`, { fileId: fileInfo.FileId, revisionId, fileNameMatch, fileSizeMatch, existing: { fileName: existing.fileName, fileSize: existing.fileSize, fileNameType: typeof existing.fileName, fileSizeType: typeof existing.fileSize }, dolce: { fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, fileNameType: typeof fileInfo.FileName, fileSizeType: typeof fileInfo.FileSize } }) const hasChanges = !fileNameMatch || !fileSizeMatch if (hasChanges) { // 변경사항이 있으면 업데이트 debugProcess(`파일 정보 변경 감지 - 업데이트`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, changes: { fileName: !fileNameMatch, fileSize: !fileSizeMatch } }) await db .update(documentAttachments) .set({ fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, updatedAt: new Date() }) .where(eq(documentAttachments.id, existing.id)) debugSuccess(`첨부파일 정보 업데이트 완료`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId }) return 'UPDATED' } // 변경사항 없으면 SKIPPED debugProcess(`파일 이미 존재 - 변경사항 없음`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId }) return 'SKIPPED' } // 파일 다운로드 debugProcess(`📥 [1/3] 파일 다운로드 시작`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, revisionId }) let fileBuffer: Buffer try { fileBuffer = await this.downloadFileFromDOLCE( fileInfo.FileId, userId, fileInfo.FileName ) debugSuccess(`✅ [1/3] 파일 다운로드 완료`, { fileName: fileInfo.FileName, bufferSize: fileBuffer.length }) } catch (downloadError) { debugError(`❌ [1/3] 파일 다운로드 실패`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, error: downloadError }) throw downloadError } // 로컬 파일 시스템에 저장 debugProcess(`💾 [2/3] 로컬 저장 시작`, { fileName: fileInfo.FileName }) let savedFile: { filePath: string; fileSize: number } try { savedFile = await this.saveFileToLocal(fileBuffer, fileInfo.FileName) debugSuccess(`✅ [2/3] 로컬 저장 완료`, { fileName: fileInfo.FileName, filePath: savedFile.filePath, fileSize: savedFile.fileSize }) } catch (saveError) { debugError(`❌ [2/3] 로컬 저장 실패`, { fileName: fileInfo.FileName, error: saveError }) throw saveError } // DB에 첨부파일 정보 저장 debugProcess(`💿 [3/3] DB Insert 시작`, { fileName: fileInfo.FileName, revisionId, fileId: fileInfo.FileId }) const attachmentData = { revisionId, fileName: fileInfo.FileName, filePath: savedFile.filePath, fileType: extname(fileInfo.FileName).slice(1).toLowerCase() || undefined, fileSize: fileInfo.FileSize, uploadId: fileInfo.UploadId, fileId: fileInfo.FileId, uploadedBy: userId, dolceFilePath: fileInfo.FileRelativePath, uploadedAt: this.convertDolceDateToDate(fileInfo.FileCreateDT), createdAt: new Date(), updatedAt: new Date() } debugProcess(`💿 [3/3] DB Insert 데이터`, attachmentData) try { const insertResult = await db .insert(documentAttachments) .values(attachmentData) .returning({ id: documentAttachments.id }) debugSuccess(`✅ [3/3] DB Insert 완료`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, insertedId: insertResult[0]?.id, filePath: savedFile.filePath, fileSize: savedFile.fileSize }) } catch (insertError) { debugError(`❌ [3/3] DB Insert 실패`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, revisionId, error: insertError, errorMessage: insertError instanceof Error ? insertError.message : String(insertError), errorStack: insertError instanceof Error ? insertError.stack : undefined }) throw insertError } debugSuccess(`🎉 새 첨부파일 생성 완료 (전체 프로세스)`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, filePath: savedFile.filePath, fileSize: savedFile.fileSize }) return 'NEW' } catch (error) { debugError(`단일 첨부파일 동기화 실패`, { fileName: fileInfo.FileName, fileId: fileInfo.FileId, error }) throw error } } /** * 단일 revision 동기화 */ private async syncSingleRevision( issueStageId: number, detailDoc: DOLCEDetailDocument, projectId: number, docNumber: string ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> { console.log(detailDoc,"detailDoc") // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함) const existingRevision = await findMatchingRevision( projectId, docNumber, detailDoc, issueStageId ) // Category에 따른 uploaderType 매핑 const uploaderType = this.mapCategoryToUploaderType(detailDoc.Category) // RegisterKind에 따른 usage, usageType 매핑 const { usage, usageType } = this.mapRegisterKindToUsage(detailDoc.RegisterKind) // DOLCE 상세 데이터를 revisions 스키마에 맞게 변환 const submittedDate = this.convertDolceDateToDate(detailDoc.CreateDt) const revisionData = { serialNo: String(detailDoc.RegisterSerialNo), issueStageId, revision: detailDoc.DrawingRevNo, uploaderType, registerSerialNoMax: String(detailDoc.RegisterSerialNoMax), // uploaderName: detailDoc.CreateUserNM, usage, usageType, revisionStatus: detailDoc.Status, externalUploadId: detailDoc.UploadId, registerId: detailDoc.RegisterId, // 🆕 항상 최신 registerId로 업데이트 ofdcNo: detailDoc.OFDC_NO, // 🆕 OFDC Number 추가 comment: detailDoc.SHINote, submittedDate: submittedDate ? submittedDate.toISOString().split('T')[0] : null, // Date를 YYYY-MM-DD string으로 변환 updatedAt: new Date() } if (existingRevision) { // 업데이트 필요 여부 확인 - getImportStatus와 동일한 필드 체크 const hasChanges = existingRevision.comment !== revisionData.comment || existingRevision.revisionStatus !== revisionData.revisionStatus if (hasChanges) { await db .update(revisions) .set(revisionData) .where(eq(revisions.id, existingRevision.id)) console.log(`Updated revision: ${detailDoc.RegisterId} (local ID: ${existingRevision.id})`) return 'UPDATED' } else { return 'SKIPPED' } } else { // 새 revision 생성 await db .insert(revisions) .values({ ...revisionData, createdAt: new Date() }) console.log(`Created new revision: ${detailDoc.RegisterId}`) return 'NEW' } } /** * Category를 uploaderType으로 매핑 */ private mapCategoryToUploaderType(category: string): string { switch (category) { case 'TS': return 'vendor' case 'FS': return 'shi' default: return 'vendor' // 기본값 } } /** * RegisterKind를 usage/usageType으로 매핑 */ private mapRegisterKindToUsage(registerKind: string): { usage: string; usageType: string | null } { if (!registerKind) { return { usage: 'DEFAULT', usageType: 'DEFAULT' } } switch (registerKind.toUpperCase()) { case 'APPR': return { usage: 'APPROVAL', usageType: 'Full' } case 'APPR-P': return { usage: 'APPROVAL', usageType: 'Partial' } case 'WORK': return { usage: 'WORKING', usageType: 'Full' } case 'WORK-P': return { usage: 'WORKING', usageType: 'Partial' } case 'FMEA-1': return { usage: 'The 1st', usageType: null } case 'FMEA-2': return { usage: 'The 2nd', usageType: null } case 'RECP': return { usage: 'Pre', usageType: null } case 'RECW': return { usage: 'Working', usageType: null } case 'CMTM': return { usage: 'Mark-Up', usageType: null } // SUB(제출용) - 도면제출 SHI >> GTT case 'GSUB': return { usage: 'SUB', usageType: null } default: console.warn(`Unknown RegisterKind: ${registerKind}`) return { usage: registerKind, usageType: 'DEFAULT' } } } /** * Status를 revisionStatus로 매핑 */ private mapStatusToRevisionStatus(status: string): string { // TODO: DOLCE의 Status 값에 맞게 매핑 로직 구현 // 현재는 기본 매핑만 제공 switch (status?.toUpperCase()) { case 'SUBMITTED': return 'SUBMITTED' case 'APPROVED': return 'APPROVED' case 'REJECTED': return 'REJECTED' default: return 'SUBMITTED' // 기본값 } } private convertDolceDateToDate(dolceDate: string | undefined | null): Date | null { if (!dolceDate || dolceDate.trim() === '') { return null } // "20250204" 형태의 문자열을 "2025-02-04" 형태로 변환 if (dolceDate.length === 8 && /^\d{8}$/.test(dolceDate)) { const year = dolceDate.substring(0, 4) const month = dolceDate.substring(4, 6) const day = dolceDate.substring(6, 8) try { const date = new Date(`${year}-${month}-${day}`) // 유효한 날짜인지 확인 if (isNaN(date.getTime())) { console.warn(`Invalid date format: ${dolceDate}`) return null } return date } catch (error) { console.warn(`Failed to parse date: ${dolceDate}`, error) return null } } console.warn(`Unexpected date format: ${dolceDate}`) return null } /** * B4 문서용 이슈 스테이지 자동 생성 */ private async createIssueStagesForB4Document( drawingNo: string, projectId: number, dolceDoc: DOLCEDocument ): Promise { try { // 문서 ID 조회 const [document] = await db .select({ id: documents.id }) .from(documents) .where(and( eq(documents.projectId, projectId), eq(documents.docNumber, drawingNo), eq(documents.discipline, dolceDoc.Discipline) )) .limit(1) if (!document) { throw new Error(`Document not found: ${drawingNo}`) } const documentId = document.id // 기존 이슈 스테이지 확인 const existingStages = await db .select() .from(issueStages) .where(eq(issueStages.documentId, documentId)) const existingStageNames = existingStages.map(stage => stage.stageName) // For Pre 스테이지 생성 (GTTPreDwg) if (!existingStageNames.includes('For Pre')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'GTT → SHI (For Pre.DWG)', planDate: this.convertDolceDateToDate(dolceDoc.GTTPreDwg_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.GTTPreDwg_ResultDate), stageStatus: 'PLANNED', stageOrder: 1, priority: 'MEDIUM', reminderDays: 3, description: 'GTT 예비 도면 단계' }) } // For Working 스테이지 생성 (GTTWorkingDwg) if (!existingStageNames.includes('For Work')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'GTT → SHI (For Work.DWG)', planDate: this.convertDolceDateToDate(dolceDoc.GTTWorkingDwg_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.GTTWorkingDwg_ResultDate), stageStatus: 'PLANNED', stageOrder: 2, description: 'GTT 작업 도면 단계' }) } console.log(`Created issue stages for B4 document: ${drawingNo}`) } catch (error) { console.error(`Failed to create issue stages for ${drawingNo}:`, error) } } private async createIssueStagesForB3Document( drawingNo: string, projectId: number, dolceDoc: DOLCEDocument ): Promise { try { // 문서 ID 조회 const [document] = await db .select({ id: documents.id }) .from(documents) .where(and( eq(documents.projectId, projectId), eq(documents.docNumber, drawingNo), eq(documents.discipline, dolceDoc.Discipline) )) .limit(1) if (!document) { throw new Error(`Document not found: ${drawingNo}`) } const documentId = document.id // 기존 이슈 스테이지 확인 const existingStages = await db .select() .from(issueStages) .where(eq(issueStages.documentId, documentId)) const existingStageNames = existingStages.map(stage => stage.stageName) // Approval 스테이지 생성 if (!existingStageNames.includes('Approval')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'Vendor → SHI (For Approval)', planDate: this.convertDolceDateToDate(dolceDoc.AppDwg_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.AppDwg_ResultDate), stageStatus: 'PLANNED', stageOrder: 1, description: 'Vendor 승인 도면 단계' }) } // Working 스테이지 생성 if (!existingStageNames.includes('Working')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'Vendor → SHI (For Working)', planDate: this.convertDolceDateToDate(dolceDoc.WorDwg_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.WorDwg_ResultDate), stageStatus: 'PLANNED', stageOrder: 2, description: 'Vendor 작업 도면 단계' }) } console.log(`Created issue stages for B3 document: ${drawingNo}`) } catch (error) { console.error(`Failed to create issue stages for ${drawingNo}:`, error) } } private async createIssueStagesForB5Document( drawingNo: string, projectId: number, dolceDoc: DOLCEDocument ): Promise { try { // 문서 ID 조회 const [document] = await db .select({ id: documents.id }) .from(documents) .where(and( eq(documents.projectId, projectId), eq(documents.docNumber, drawingNo), eq(documents.discipline, dolceDoc.Discipline) )) .limit(1) if (!document) { throw new Error(`Document not found: ${drawingNo}`) } const documentId = document.id // 기존 이슈 스테이지 확인 const existingStages = await db .select() .from(issueStages) .where(eq(issueStages.documentId, documentId)) const existingStageNames = existingStages.map(stage => stage.stageName) // Approval 스테이지 생성 if (!existingStageNames.includes('Approval')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'Vendor → SHI (For Approval)', planDate: this.convertDolceDateToDate(dolceDoc.FMEAFirst_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.FMEAFirst_ResultDate), stageStatus: 'PLANNED', stageOrder: 1, description: 'FMEA 예비 도면 단계' }) } // Working 스테이지 생성 if (!existingStageNames.includes('Working')) { await db.insert(issueStages).values({ documentId: documentId, stageName: 'Vendor → SHI (For Working)', planDate: this.convertDolceDateToDate(dolceDoc.FMEASecond_PlanDate), actualDate: this.convertDolceDateToDate(dolceDoc.FMEASecond_ResultDate), stageStatus: 'PLANNED', stageOrder: 2, description: 'FMEA 작업 도면 단계' }) } console.log(`Created issue stages for B5 document: ${drawingNo}`) } catch (error) { console.error(`Failed to create issue stages for ${drawingNo}:`, error) } } /** * 가져오기 상태 조회 */ /** * 가져오기 상태 조회 - 에러 시 안전한 기본값 반환 */ async getImportStatus( projectId: number, // ✅ projectId sourceSystem: string = 'DOLCE' ): Promise { try { // 세션 조회 const session = await getServerSession(authOptions) if (!session?.user?.companyId) { console.warn(`Session not found for import status check`) return { lastImportAt: undefined, availableDocuments: 0, newDocuments: 0, updatedDocuments: 0, availableRevisions: 0, newRevisions: 0, updatedRevisions: 0, availableAttachments: 0, newAttachments: 0, updatedAttachments: 0, importEnabled: false, error: '세션이 없습니다. 다시 로그인해주세요.' } } const vendorId = Number(session.user.companyId) // 프로젝트 코드와 벤더 코드 조회 const contractInfo = await this.getContractInfoByProjectId(projectId, vendorId) // 🔥 계약 정보가 없으면 기본 상태 반환 (에러 throw 하지 않음) if (!contractInfo?.projectCode || !contractInfo?.vendorCode) { console.warn(`Contract not found for project ${projectId}`) return { lastImportAt: undefined, availableDocuments: 0, newDocuments: 0, updatedDocuments: 0, availableRevisions: 0, newRevisions: 0, updatedRevisions: 0, availableAttachments: 0, newAttachments: 0, updatedAttachments: 0, importEnabled: false, error: `Project ${projectId}에 대한 계약 정보를 찾을 수 없습니다.` } } const contractId = contractInfo.contractId // 🔥 contract.id 추출 // 마지막 가져오기 시간 조회 const [lastImport] = await db .select({ lastSynced: sql`MAX(${documents.externalSyncedAt})` }) .from(documents) .where(and( eq(documents.contractId, contractId), // ✅ contractId로 조회 eq(documents.externalSystemType, sourceSystem) )) let availableDocuments = 0 let newDocuments = 0 let updatedDocuments = 0 let availableRevisions = 0 let newRevisions = 0 let updatedRevisions = 0 let availableAttachments = 0 let newAttachments = 0 let updatedAttachments = 0 // 🔍 디버깅용: 새로운 attachment 상세 정보 수집 const newAttachmentDetails: Array<{ fileId: string fileName: string fileSize: number revisionId: number documentNo: string discipline: string revision: string }> = [] const updatedAttachmentDetails: Array<{ fileId: string fileName: string fileSize: number revisionId: number documentNo: string discipline: string revision: string changes: { fileName: boolean; fileSize: boolean } }> = [] try { // 각 drawingKind별로 확인 const drawingKinds = ['B3', 'B4', 'B5'] for (const drawingKind of drawingKinds) { try { const externalDocs = await this.fetchFromDOLCE( contractInfo.projectCode, contractInfo.vendorCode, drawingKind ) availableDocuments += externalDocs.length // 신규/업데이트 문서 수 계산 for (const externalDoc of externalDocs) { const existing = await db .select({ id: documents.id, updatedAt: documents.updatedAt }) .from(documents) .where(and( eq(documents.projectId, projectId), // ✅ projectId로 조회 eq(documents.externalDocumentId, externalDoc.DrawingNo), // externalDocumentId 사용 eq(documents.discipline, externalDoc.Discipline), eq(documents.externalSystemType, sourceSystem) )) .limit(1) if (existing.length === 0) { newDocuments++ } else { // DOLCE의 CreateDt와 로컬 updatedAt 비교 if (externalDoc.CreateDt && existing[0].updatedAt) { const externalModified = new Date(externalDoc.CreateDt) const localModified = new Date(existing[0].updatedAt) if (externalModified > localModified) { updatedDocuments++ } } } // revisions 및 attachments 상태도 확인 try { const detailDocs = await this.fetchDetailFromDOLCE( externalDoc.ProjectNo, externalDoc.DrawingNo, externalDoc.Discipline, externalDoc.DrawingKind ) availableRevisions += detailDocs.length for (const detailDoc of detailDocs) { // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함) const existingRevision = await findMatchingRevision( projectId, externalDoc.DrawingNo, detailDoc ) if (!existingRevision) { // revision이 존재하지 않음 -> 신규 newRevisions++ } else { // 2. revision이 존재하면 변경사항이 있는지 체크 const hasChanges = existingRevision.comment !== detailDoc.SHINote || existingRevision.revisionStatus !== detailDoc.Status if (hasChanges) { // 변경사항이 있음 -> 업데이트 대상 updatedRevisions++ } // 변경사항이 없으면 카운트하지 않음 } // FS Category 문서의 첨부파일 확인 if (detailDoc.Category === 'FS' && detailDoc.UploadId) { try { console.log(`🔍 [getImportStatus] FileInfoList 조회 시작:`, { drawingNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, registerId: detailDoc.RegisterId, uploadId: detailDoc.UploadId }) const fileInfos = await this.fetchFileInfoFromDOLCE(detailDoc.UploadId) console.log(`🔍 [getImportStatus] FileInfoList 조회 결과:`, { drawingNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, uploadId: detailDoc.UploadId, totalFiles: fileInfos.length, files: fileInfos.map(f => ({ fileId: f.FileId, fileName: f.FileName, fileSize: f.FileSize, useYn: f.UseYn })) }) availableAttachments += fileInfos.filter(f => f.UseYn === 'True').length for (const fileInfo of fileInfos) { if (fileInfo.UseYn !== 'True') continue // 1. 먼저 해당 revision의 attachment가 존재하는지 확인 // ✅ revisionId를 찾기 위해 먼저 revision 조회 if (!existingRevision) { // ⚠️ revision이 없으면 orphan attachment (처리 불가) console.warn(`⚠️ [getImportStatus] Orphan 파일 - Revision 없음:`, { drawingNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, registerId: detailDoc.RegisterId, uploadId: detailDoc.UploadId, fileId: fileInfo.FileId, fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, reason: 'Revision not found in DB - cannot process this file' }) // ❌ 신규로 카운트하지 않음 (처리할 수 없으므로) continue } const existingAttachment = await db .select({ id: documentAttachments.id, fileName: documentAttachments.fileName, fileSize: documentAttachments.fileSize }) .from(documentAttachments) .where(and( eq(documentAttachments.revisionId, existingRevision.id), eq(documentAttachments.fileId, fileInfo.FileId) )) .limit(1) if (existingAttachment.length === 0) { // attachment가 존재하지 않음 -> 신규 newAttachments++ console.log(`✨ [getImportStatus] 신규 Attachment 감지:`, { drawingNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, registerId: detailDoc.RegisterId, uploadId: detailDoc.UploadId, fileId: fileInfo.FileId, fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, revisionId: existingRevision.id }) newAttachmentDetails.push({ fileId: fileInfo.FileId, fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, revisionId: existingRevision.id, documentNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, revision: detailDoc.DrawingRevNo }) } else { // 2. attachment가 존재하면 변경사항이 있는지 체크 const existing = existingAttachment[0] // 타입 안전 비교 (fileName은 문자열, fileSize는 숫자로 변환) const fileNameMatch = existing.fileName === fileInfo.FileName const fileSizeMatch = Number(existing.fileSize) === Number(fileInfo.FileSize) if (!fileNameMatch || !fileSizeMatch) { console.log(`🔍 Attachment difference detected:`, { fileId: fileInfo.FileId, revisionId: existingRevision.id, fileNameMatch, fileSizeMatch, existing: { fileName: existing.fileName, fileSize: existing.fileSize, fileSizeType: typeof existing.fileSize }, dolce: { fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, fileSizeType: typeof fileInfo.FileSize } }) } const hasChanges = !fileNameMatch || !fileSizeMatch if (hasChanges) { // 변경사항이 있음 -> 업데이트 대상 updatedAttachments++ updatedAttachmentDetails.push({ fileId: fileInfo.FileId, fileName: fileInfo.FileName, fileSize: fileInfo.FileSize, revisionId: existingRevision.id, documentNo: externalDoc.DrawingNo, discipline: externalDoc.Discipline, revision: detailDoc.DrawingRevNo, changes: { fileName: !fileNameMatch, fileSize: !fileSizeMatch } }) } // ✅ fileId가 같고 fileName, fileSize도 같으면 변경사항 없음 } } } catch (error) { console.warn(`Failed to check files for ${detailDoc.UploadId}:`, error) } } } } catch (error) { console.warn(`Failed to check revisions for ${externalDoc.DrawingNo}:`, error) } } } catch (error) { console.warn(`Failed to check ${drawingKind} for status:`, error) } } } catch (error) { console.warn(`Failed to fetch external data for status: ${error}`) // 🔥 외부 API 호출 실패 시에도 기본값 반환 } // 🔍 최종 diff 요약 출력 console.log('\n========================================') console.log('📊 DOLCE 동기화 상태 검사 완료') console.log('========================================') console.log(`프로젝트 ID: ${projectId}`) console.log(`마지막 동기화: ${lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : '없음'}`) console.log('\n📄 Documents:') console.log(` - 총 개수: ${availableDocuments}`) console.log(` - 신규: ${newDocuments}`) console.log(` - 업데이트: ${updatedDocuments}`) console.log('\n📝 Revisions:') console.log(` - 총 개수: ${availableRevisions}`) console.log(` - 신규: ${newRevisions}`) console.log(` - 업데이트: ${updatedRevisions}`) console.log('\n📎 Attachments:') console.log(` - 총 개수: ${availableAttachments}`) console.log(` - 신규: ${newAttachments}`) console.log(` - 업데이트: ${updatedAttachments}`) if (newAttachmentDetails.length > 0) { console.log('\n🆕 신규 Attachments 상세:') newAttachmentDetails.forEach((att, idx) => { console.log(` ${idx + 1}. FileID: ${att.fileId}`) console.log(` - Document: ${att.documentNo} [${att.discipline}] (Rev: ${att.revision})`) console.log(` - FileName: ${att.fileName}`) console.log(` - FileSize: ${att.fileSize}`) console.log(` - RevisionID: ${att.revisionId}`) }) } if (updatedAttachmentDetails.length > 0) { console.log('\n🔄 업데이트 Attachments 상세:') updatedAttachmentDetails.forEach((att, idx) => { console.log(` ${idx + 1}. FileID: ${att.fileId}`) console.log(` - Document: ${att.documentNo} [${att.discipline}] (Rev: ${att.revision})`) console.log(` - FileName: ${att.fileName}`) console.log(` - FileSize: ${att.fileSize}`) console.log(` - RevisionID: ${att.revisionId}`) console.log(` - Changes: fileName=${att.changes.fileName}, fileSize=${att.changes.fileSize}`) }) } console.log('========================================\n') return { lastImportAt: lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : undefined, availableDocuments, newDocuments, updatedDocuments, availableRevisions, newRevisions, updatedRevisions, availableAttachments, newAttachments, updatedAttachments, importEnabled: this.isImportEnabled(sourceSystem) } } catch (error) { // 🔥 최종적으로 모든 에러를 catch하여 안전한 기본값 반환 console.error('Failed to get import status:', error) return { lastImportAt: undefined, availableDocuments: 0, newDocuments: 0, updatedDocuments: 0, availableRevisions: 0, newRevisions: 0, updatedRevisions: 0, availableAttachments: 0, newAttachments: 0, updatedAttachments: 0, importEnabled: false, error: error instanceof Error ? error.message : 'Unknown error occurred' } } } /** * 가져오기 활성화 여부 확인 */ private isImportEnabled(sourceSystem: string): boolean { const upperSystem = sourceSystem.toUpperCase() const enabled = process.env[`IMPORT_${upperSystem}_ENABLED`] return enabled === 'true' || enabled === '1' } /** * DOLCE 업로드 확인 테스트 (업로드 후 파일이 DOLCE에 존재하는지 확인) */ async testDOLCEFileDownload( fileId: string, userId: string, fileName: string ): Promise<{ success: boolean; downloadUrl?: string; error?: string }> { try { // 암호화 문자열 생성: FileId↔UserId↔FileName const encryptString = `${fileId}↔${userId}↔${fileName}` const encryptedKey = this.encryptDES(encryptString) const downloadUrl = `${process.env.DOLCE_DOWNLOAD_URL}?key=${encryptedKey}` || `http://60.100.99.217:1111/Download.aspx?key=${encryptedKey}` console.log(`🧪 DOLCE 파일 다운로드 테스트:`) console.log(` 파일명: ${fileName}`) console.log(` FileId: ${fileId}`) console.log(` UserId: ${userId}`) console.log(` 암호화 키: ${encryptedKey}`) console.log(` 다운로드 URL: ${downloadUrl}`) const response = await fetch(downloadUrl, { method: 'GET', headers: { 'User-Agent': 'DOLCE-Integration-Service' } }) if (!response.ok) { console.error(`❌ DOLCE 파일 다운로드 테스트 실패: HTTP ${response.status}`) return { success: false, downloadUrl, error: `HTTP ${response.status}` } } const buffer = Buffer.from(await response.arrayBuffer()) console.log(`✅ DOLCE 파일 다운로드 테스트 성공: ${fileName} (${buffer.length} bytes)`) return { success: true, downloadUrl } } catch (error) { console.error(`❌ DOLCE 파일 다운로드 테스트 실패: ${fileName}`, error) return { success: false, error: error instanceof Error ? error.message : 'Unknown error' } } } } export const importService = new ImportService()