diff options
| -rw-r--r-- | db/schema/vendorDocu.ts | 5 | ||||
| -rw-r--r-- | lib/vendor-document-list/import-service.ts | 420 |
2 files changed, 269 insertions, 156 deletions
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index de9dc7fb..304fe5ae 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -203,7 +203,10 @@ export const revisions = pgTable( registerId: varchar("register_id", { length: 50 }), serialNo: varchar("serial_no", { length: 50 }), - registerSerialNoMax: varchar("register_serial_no_max", { length: 50 }), + registerSerialNoMax: varchar("register_serial_no_max", { length: 50 }), + + // OFDC Number for document identification + ofdcNo: varchar("ofdc_no", { length: 100 }), }, (table) => { diff --git a/lib/vendor-document-list/import-service.ts b/lib/vendor-document-list/import-service.ts index 5af49215..4e0f7610 100644 --- a/lib/vendor-document-list/import-service.ts +++ b/lib/vendor-document-list/import-service.ts @@ -120,6 +120,7 @@ interface DOLCEDetailDocument { SHIDrawingNo: string Receiver: string SHINote: string + OFDC_NO: string | null // OFDC Number for document identification } interface DOLCEFileInfo { @@ -138,6 +139,247 @@ interface DOLCEFileInfo { 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<RevisionMatchResult | null> { + 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") @@ -987,7 +1229,12 @@ class ImportService { continue } - const result = await this.syncSingleRevision(matchingStage.id, detailDoc) + const result = await this.syncSingleRevision( + matchingStage.id, + detailDoc, + projectId, + dolceDoc.DrawingNo + ) console.log(`✅ Revision 동기화 완료:`, { registerId: detailDoc.RegisterId, @@ -1412,94 +1659,20 @@ class ImportService { */ private async syncSingleRevision( issueStageId: number, - detailDoc: DOLCEDetailDocument + detailDoc: DOLCEDetailDocument, + projectId: number, + docNumber: string ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> { console.log(detailDoc,"detailDoc") - // 🆕 여러 조건으로 기존 revision 조회 - type RevisionRecord = { - 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 - } - let existingRevision: RevisionRecord | null = null - - // 1차: registerId로 조회 (가장 정확한 매칭) - if (detailDoc.RegisterId) { - const results = await db - .select() - .from(revisions) - .where(and( - eq(revisions.issueStageId, issueStageId), - eq(revisions.registerId, detailDoc.RegisterId) - )) - .limit(1) - - if (results.length > 0) { - existingRevision = results[0] - console.log(`✅ Found revision by registerId: ${detailDoc.RegisterId} → local ID: ${existingRevision.id}`) - } else { - console.log(`❌ NOT found by registerId: ${detailDoc.RegisterId}`) - } - } - - // 2차: externalUploadId로 조회 (업로드했던 revision 매칭) - if (!existingRevision && detailDoc.UploadId) { - 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] - console.log(`✅ Found revision by externalUploadId: ${detailDoc.UploadId} → local ID: ${existingRevision.id}`) - } else { - console.log(`❌ NOT found by externalUploadId: ${detailDoc.UploadId}`) - } - } - - // 3차: DrawingRevNo + serialNo로 조회 (같은 issueStage 내에서 정확한 매칭) - if (!existingRevision && detailDoc.DrawingRevNo && detailDoc.RegisterSerialNo) { - 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] - console.log(`✅ Found revision by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo} → local ID: ${existingRevision.id}`) - } else { - console.log(`❌ NOT found by DrawingRevNo+serialNo: ${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo}`) - } - } - - // 최종 결과 로그 - if (!existingRevision) { - console.log(`🆕 Will CREATE NEW revision for RegisterId: ${detailDoc.RegisterId} (${detailDoc.DrawingRevNo}/${detailDoc.RegisterSerialNo})`) - } + // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함) + const existingRevision = await findMatchingRevision( + projectId, + docNumber, + detailDoc, + issueStageId + ) // Category에 따른 uploaderType 매핑 const uploaderType = this.mapCategoryToUploaderType(detailDoc.Category) @@ -1522,6 +1695,7 @@ class ImportService { 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() @@ -2046,76 +2220,12 @@ async getImportStatus( availableRevisions += detailDocs.length for (const detailDoc of detailDocs) { - // 1. syncSingleRevision과 동일한 방식으로 revision 찾기 - let existingRevision: { id: number; comment: string | null; revisionStatus: string } | null = null - - // 1차: registerId로 조회 (가장 정확한 매칭) - if (detailDoc.RegisterId) { - const results = await db - .select({ - id: revisions.id, - comment: revisions.comment, - revisionStatus: revisions.revisionStatus - }) - .from(revisions) - .where(eq(revisions.registerId, detailDoc.RegisterId)) - .limit(1) - - if (results.length > 0) { - existingRevision = results[0] - } - } - - // 2차: externalUploadId로 조회 (registerId가 없거나 못 찾은 경우) - if (!existingRevision && detailDoc.UploadId) { - const results = await db - .select({ - id: revisions.id, - comment: revisions.comment, - revisionStatus: revisions.revisionStatus - }) - .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, detailDoc.DrawingNo), - eq(revisions.externalUploadId, detailDoc.UploadId) - ) - ) - .limit(1) - - if (results.length > 0) { - existingRevision = results[0] - } - } - - // 3차: DrawingRevNo + serialNo로 조회 (최후 수단) - if (!existingRevision && detailDoc.DrawingRevNo) { - const results = await db - .select({ - id: revisions.id, - comment: revisions.comment, - revisionStatus: revisions.revisionStatus - }) - .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, detailDoc.DrawingNo), - eq(revisions.revision, detailDoc.DrawingRevNo), - eq(revisions.serialNo, String(detailDoc.RegisterSerialNo)) - ) - ) - .limit(1) - - if (results.length > 0) { - existingRevision = results[0] - } - } + // 🔄 공통 revision 매칭 함수 사용 (OFDC_NO 포함) + const existingRevision = await findMatchingRevision( + projectId, + externalDoc.DrawingNo, + detailDoc + ) if (!existingRevision) { // revision이 존재하지 않음 -> 신규 |