(대표님) 변경사항 0604 - OCR 관련 및 drizzle generated sqls

1 files changed, 655 insertions, 0 deletions

diff --git a/lib/vendor-document-list/dolce-upload-service.ts b/lib/vendor-document-list/dolce-upload-service.ts
new file mode 100644
index 00000000..0396e819
--- /dev/null
+++ b/lib/vendor-document-list/dolce-upload-service.ts
@@ -0,0 +1,655 @@
+// lib/vendor-document-list/dolce-upload-service.ts
+import db from "@/db/db"
+import { documents, revisions, documentAttachments, contracts, projects, vendors, issueStages } from "@/db/schema"
+import { eq, and, desc, sql, inArray, min } from "drizzle-orm"
+import { v4 as uuidv4 } from "uuid"
+
+export interface DOLCEUploadResult {
+  success: boolean
+  uploadedDocuments: number
+  uploadedFiles: number
+  errors?: string[]
+  results?: {
+    documentResults?: any[]
+    fileResults?: any[]
+    mappingResults?: any[]
+  }
+}
+
+interface DOLCEDocument {
+  Mode: "ADD" | "MOD"
+  Status: string
+  RegisterId: number
+  ProjectNo: string
+  Discipline: string
+  DrawingKind: string
+  DrawingNo: string
+  DrawingName: string
+  RegisterGroupId: number
+  RegisterSerialNo: number
+  RegisterKind: string
+  DrawingRevNo: string
+  Category: string
+  Receiver: string | null
+  Manager: string
+  RegisterDesc: string
+  UploadId?: string
+  RegCompanyCode: string
+}
+
+interface DOLCEFileMapping {
+  CGbn?: string
+  Category?: string
+  CheckBox: string
+  DGbn?: string
+  DegreeGbn?: string
+  DeptGbn?: string
+  Discipline: string
+  DrawingKind: string
+  DrawingMoveGbn: string
+  DrawingName: string
+  DrawingNo: string
+  DrawingUsage: string
+  FileNm: string
+  JGbn?: string
+  Manager: string
+  MappingYN: string
+  NewOrNot: string
+  ProjectNo: string
+  RegisterGroup: number
+  RegisterGroupId: number
+  RegisterKindCode: string
+  RegisterSerialNo: number
+  RevNo?: string
+  SGbn?: string
+  UploadId: string
+}
+
+class DOLCEUploadService {
+  private readonly BASE_URL = process.env.DOLCE_API_URL || 'http://60.100.99.217:1111'
+  private readonly UPLOAD_SERVICE_URL = process.env.DOLCE_UPLOAD_URL || 'http://60.100.99.217:1111/PWPUploadService.ashx'
+
+  /**
+   * 메인 업로드 함수: 변경된 문서와 파일을 DOLCE로 업로드
+   */
+  async uploadToDoLCE(
+    contractId: number,
+    revisionIds: number[],
+    userId: string,
+    userName?: string
+  ): Promise<DOLCEUploadResult> {
+    try {
+      console.log(`Starting DOLCE upload for contract ${contractId}, revisions: ${revisionIds.join(', ')}`)
+
+      // 1. 계약 정보 조회 (프로젝트 코드, 벤더 코드 등)
+      const contractInfo = await this.getContractInfo(contractId)
+      if (!contractInfo) {
+        throw new Error(`Contract info not found for ID: ${contractId}`)
+      }
+
+      // 2. 업로드할 리비전 정보 조회
+      const revisionsToUpload = await this.getRevisionsForUpload(revisionIds)
+      if (revisionsToUpload.length === 0) {
+        return {
+          success: true,
+          uploadedDocuments: 0,
+          uploadedFiles: 0
+        }
+      }
+
+      // 3. 각 issueStageId별로 첫 번째 revision 정보를 미리 조회 (Mode 결정용)
+      const firstRevisionMap = await this.getFirstRevisionMap(revisionsToUpload.map(r => r.issueStageId))
+
+      let uploadedDocuments = 0
+      let uploadedFiles = 0
+      const errors: string[] = []
+      const results: any = {
+        documentResults: [],
+        fileResults: [],
+        mappingResults: []
+      }
+
+      // 4. 각 리비전별로 처리
+      for (const revision of revisionsToUpload) {
+        try {
+          console.log(`Processing revision ${revision.revision} for document ${revision.documentNo}`)
+
+          // 4-1. 파일이 있는 경우 먼저 업로드
+          let uploadId: string | undefined
+          if (revision.attachments && revision.attachments.length > 0) {
+            const fileUploadResults = await this.uploadFiles(revision.attachments)
+            
+            if (fileUploadResults.length > 0) {
+              uploadId = fileUploadResults[0].uploadId // 첫 번째 파일의 UploadId 사용
+              uploadedFiles += fileUploadResults.length
+              results.fileResults.push(...fileUploadResults)
+            }
+          }
+
+          // 4-2. 문서 정보 업로드
+          const dolceDoc = this.transformToDoLCEDocument(
+            revision, 
+            contractInfo, 
+            uploadId,
+            contractInfo.vendorCode,
+            firstRevisionMap
+          )
+
+          const docResult = await this.uploadDocument([dolceDoc], userId)
+          if (docResult.success) {
+            uploadedDocuments++
+            results.documentResults.push(docResult)
+
+            // 4-3. 파일이 있는 경우 매핑 정보 전송
+            if (uploadId && revision.attachments && revision.attachments.length > 0) {
+              const mappingData = this.transformToFileMapping(
+                revision,
+                contractInfo,
+                uploadId,
+                revision.attachments[0].fileName
+              )
+
+              const mappingResult = await this.uploadFileMapping([mappingData], userId)
+              if (mappingResult.success) {
+                results.mappingResults.push(mappingResult)
+              } else {
+                errors.push(`File mapping failed for ${revision.documentNo}: ${mappingResult.error}`)
+              }
+            }
+
+            // 4-4. 성공한 리비전의 상태 업데이트
+            await this.updateRevisionStatus(revision.id, 'SUBMITTED', uploadId)
+
+          } else {
+            errors.push(`Document upload failed for ${revision.documentNo}: ${docResult.error}`)
+          }
+
+        } catch (error) {
+          const errorMessage = `Failed to process revision ${revision.revision}: ${error instanceof Error ? error.message : 'Unknown error'}`
+          errors.push(errorMessage)
+          console.error(errorMessage, error)
+        }
+      }
+
+      return {
+        success: errors.length === 0,
+        uploadedDocuments,
+        uploadedFiles,
+        errors: errors.length > 0 ? errors : undefined,
+        results
+      }
+
+    } catch (error) {
+      console.error('DOLCE upload failed:', error)
+      throw error
+    }
+  }
+
+  /**
+   * 계약 정보 조회
+   */
+  private async getContractInfo(contractId: number) {
+    const [result] = await db
+      .select({
+        projectCode: projects.code,
+        vendorCode: vendors.vendorCode,
+        contractNo: contracts.contractNo
+      })
+      .from(contracts)
+      .innerJoin(projects, eq(contracts.projectId, projects.id))
+      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
+      .where(eq(contracts.id, contractId))
+      .limit(1)
+
+    return result
+  }
+
+  /**
+   * 각 issueStageId별로 첫 번째 revision 정보를 조회
+   */
+  private async getFirstRevisionMap(issueStageIds: number[]): Promise<Map<number, string>> {
+    const firstRevisions = await db
+      .select({
+        issueStageId: revisions.issueStageId,
+        firstRevision: min(revisions.revision)
+      })
+      .from(revisions)
+      .where(inArray(revisions.issueStageId, issueStageIds))
+      .groupBy(revisions.issueStageId)
+
+    const map = new Map<number, string>()
+    firstRevisions.forEach(item => {
+      if (item.firstRevision) {
+        map.set(item.issueStageId, item.firstRevision)
+      }
+    })
+    
+    return map
+  }
+
+  /**
+   * 업로드할 리비전 정보 조회 (문서 정보 및 첨부파일 포함)
+   */
+  private async getRevisionsForUpload(revisionIds: number[]) {
+    // revisions → issueStages → documents 순서로 join하여 정보 조회
+    const revisionResults = await db
+      .select({
+        // revision 테이블 정보
+        id: revisions.id,
+        revision: revisions.revision, // revisionNo가 아니라 revision
+        revisionStatus: revisions.revisionStatus,
+        uploaderId: revisions.uploaderId,
+        uploaderName: revisions.uploaderName,
+        submittedDate: revisions.submittedDate,
+        comment: revisions.comment,
+        
+        // ✅ DOLCE 연동 필드들 (새로 추가)
+        externalUploadId: revisions.externalUploadId,
+        externalRegisterId: revisions.id,
+        externalSentAt: revisions.submittedDate,
+        
+        // issueStages 테이블 정보
+        issueStageId: issueStages.id,
+        stageName: issueStages.stageName,
+        documentId: issueStages.documentId,
+        
+        // documents 테이블 정보 (DOLCE 업로드에 필요한 모든 필드)
+        documentNo: documents.docNumber,
+        documentName: documents.title,
+        drawingKind: documents.drawingKind,
+        drawingMoveGbn: documents.drawingMoveGbn,
+        discipline: documents.discipline,
+        registerGroupId: documents.registerGroupId,
+        
+        // DOLCE B4 전용 필드들
+        cGbn: documents.cGbn,
+        dGbn: documents.dGbn,
+        degreeGbn: documents.degreeGbn,
+        deptGbn: documents.deptGbn,
+        jGbn: documents.jGbn,
+        sGbn: documents.sGbn,
+        
+        // DOLCE 추가 정보
+        manager: documents.manager,
+        managerENM: documents.managerENM,
+        managerNo: documents.managerNo,
+        shiDrawingNo: documents.shiDrawingNo,
+        
+        // 외부 시스템 연동 정보
+        externalDocumentId: documents.externalDocumentId,
+        externalSystemType: documents.externalSystemType,
+        externalSyncedAt: documents.externalSyncedAt
+      })
+      .from(revisions)
+      .innerJoin(issueStages, eq(revisions.issueStageId, issueStages.id))
+      .innerJoin(documents, eq(issueStages.documentId, documents.id))
+      .where(inArray(revisions.id, revisionIds))
+
+    // 각 리비전의 첨부파일 정보도 조회
+    const revisionsWithAttachments = []
+    for (const revision of revisionResults) {
+      const attachments = await db
+        .select({
+          id: documentAttachments.id,
+          fileName: documentAttachments.fileName,
+          filePath: documentAttachments.filePath,
+          fileType: documentAttachments.fileType,
+          fileSize: documentAttachments.fileSize,
+          createdAt: documentAttachments.createdAt
+        })
+        .from(documentAttachments)
+        .where(eq(documentAttachments.revisionId, revision.id))
+
+      revisionsWithAttachments.push({
+        ...revision,
+        attachments
+      })
+    }
+
+    return revisionsWithAttachments
+  }
+
+  /**
+   * 파일 업로드 (PWPUploadService.ashx)
+   */
+  private async uploadFiles(attachments: any[]): Promise<Array<{uploadId: string, fileId: string, filePath: string}>> {
+    const uploadResults = []
+
+    for (const attachment of attachments) {
+      try {
+        // UploadId와 FileId 생성 (UUID 형태)
+        const uploadId = uuidv4()
+        const fileId = uuidv4()
+
+        // 파일 데이터 읽기 (실제 구현에서는 파일 시스템이나 S3에서 읽어옴)
+        const fileBuffer = await this.getFileBuffer(attachment.filePath)
+
+        const uploadUrl = `${this.UPLOAD_SERVICE_URL}?UploadId=${uploadId}&FileId=${fileId}`
+
+        const response = await fetch(uploadUrl, {
+          method: 'POST',
+          headers: {
+            'Content-Type': 'application/octet-stream',
+          },
+          body: fileBuffer
+        })
+
+        if (!response.ok) {
+          const errorText = await response.text()
+          throw new Error(`File upload failed: HTTP ${response.status} - ${errorText}`)
+        }
+
+        const filePath = await response.text() // DOLCE에서 반환하는 파일 경로
+
+        uploadResults.push({
+          uploadId,
+          fileId,
+          filePath
+        })
+
+        console.log(`✅ File uploaded successfully: ${attachment.fileName} -> ${filePath}`)
+
+      } catch (error) {
+        console.error(`❌ File upload failed for ${attachment.fileName}:`, error)
+        throw error
+      }
+    }
+
+    return uploadResults
+  }
+
+  /**
+   * 문서 정보 업로드 (DetailDwgReceiptMgmtEdit)
+   */
+  private async uploadDocument(dwgList: DOLCEDocument[], userId: string): Promise<{success: boolean, error?: string, data?: any}> {
+    try {
+      const endpoint = `${this.BASE_URL}/Services/VDCSWebService.svc/DetailDwgReceiptMgmtEdit`
+
+      const requestBody = {
+        DwgList: dwgList,
+        UserID: userId
+      }
+
+      console.log('Uploading documents to DOLCE:', JSON.stringify(requestBody, null, 2))
+
+      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(`HTTP ${response.status} - ${errorText}`)
+      }
+
+      const result = await response.json()
+
+      return {
+        success: true,
+        data: result
+      }
+
+    } catch (error) {
+      return {
+        success: false,
+        error: error instanceof Error ? error.message : 'Unknown error'
+      }
+    }
+  }
+
+  /**
+   * 파일 매핑 정보 업로드 (MatchBatchFileDwgEdit)
+   */
+  private async uploadFileMapping(mappingList: DOLCEFileMapping[], userId: string): Promise<{success: boolean, error?: string, data?: any}> {
+    try {
+      const endpoint = `${this.BASE_URL}/Services/VDCSWebService.svc/MatchBatchFileDwgEdit`
+
+      const requestBody = {
+        mappingSaveLists: mappingList,
+        UserID: userId
+      }
+
+      console.log('Uploading file mapping to DOLCE:', JSON.stringify(requestBody, null, 2))
+
+      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(`HTTP ${response.status} - ${errorText}`)
+      }
+
+      const result = await response.json()
+
+      return {
+        success: true,
+        data: result
+      }
+
+    } catch (error) {
+      return {
+        success: false,
+        error: error instanceof Error ? error.message : 'Unknown error'
+      }
+    }
+  }
+
+  /**
+   * 리비전 데이터를 DOLCE 문서 형태로 변환 (업데이트된 스키마 사용)
+   */
+ private transformToDoLCEDocument(
+    revision: any,
+    contractInfo: any,
+    uploadId?: string,
+    vendorCode?: string,
+    firstRevisionMap?: Map<number, string>
+  ): DOLCEDocument {
+    // Mode 결정: 해당 issueStageId의 첫 번째 revision인지 확인
+    let mode: "ADD" | "MOD" = "MOD" // 기본값은 MOD
+    
+    if (firstRevisionMap && firstRevisionMap.has(revision.issueStageId)) {
+      const firstRevision = firstRevisionMap.get(revision.issueStageId)
+      if (revision.revision === firstRevision) {
+        mode = "ADD"
+      }
+    }
+
+    // RegisterKind 결정: stageName에 따라 설정
+    let registerKind = "APPC" // 기본값
+    if (revision.stageName) {
+      const stageNameLower = revision.stageName.toLowerCase()
+      if (stageNameLower.includes("pre")) {
+        registerKind = "RECP"
+      } else if (stageNameLower.includes("working")) {
+        registerKind = "RECW"
+      }
+    }
+
+    const getSerialNumber = (revisionValue: string): number => {
+      // 먼저 숫자인지 확인
+      const numericValue = parseInt(revisionValue)
+      if (!isNaN(numericValue)) {
+        return numericValue
+      }
+      
+      // 문자인 경우 (a=1, b=2, c=3, ...)
+      if (typeof revisionValue === 'string' && revisionValue.length === 1) {
+        const charCode = revisionValue.toLowerCase().charCodeAt(0)
+        if (charCode >= 97 && charCode <= 122) { // a-z
+          return charCode - 96 // a=1, b=2, c=3, ...
+        }
+      }
+      
+      // 기본값
+      return 1
+    }
+
+
+    return {
+      Mode: mode,
+      Status: revision.revisionStatus || "Standby",
+      RegisterId: revision.externalRegisterId, // 업데이트된 필드 사용
+      ProjectNo: contractInfo.projectCode,
+      Discipline: revision.discipline,
+      DrawingKind: revision.drawingKind,
+      DrawingNo: revision.documentNo,
+      DrawingName: revision.documentName,
+      RegisterGroupId: revision.registerGroupId || 0,
+      RegisterSerialNo: getSerialNumber(revision.revision || "1"),
+      RegisterKind: registerKind, // stageName에 따라 동적 설정
+      DrawingRevNo: revision.revision || "-",
+      Category: revision.category || "TS",
+      Receiver: null,
+      Manager: revision.managerNo || "202206", // 담당자 번호 사용
+      RegisterDesc: revision.comment || "System upload",
+      UploadId: uploadId,
+      RegCompanyCode: vendorCode || "A0005531" // 벤더 코드
+    }
+  }
+  /**
+   * 파일 매핑 데이터 변환
+   */
+  private transformToFileMapping(
+    revision: any,
+    contractInfo: any,
+    uploadId: string,
+    fileName: string
+  ): DOLCEFileMapping {
+    return {
+      CGbn: revision.cGbn,
+      Category: revision.category,
+      CheckBox: "0",
+      DGbn: revision.dGbn,
+      DegreeGbn: revision.degreeGbn,
+      DeptGbn: revision.deptGbn,
+      Discipline: revision.discipline || "DL",
+      DrawingKind: revision.drawingKind || "B4",
+      DrawingMoveGbn: revision.drawingMoveGbn || "도면입수",
+      DrawingName: revision.documentName,
+      DrawingNo: revision.documentNo,
+      DrawingUsage: "입수용",
+      FileNm: fileName,
+      JGbn: revision.jGbn,
+      Manager: revision.managerNo || "970043",
+      MappingYN: "Y",
+      NewOrNot: "N",
+      ProjectNo: contractInfo.projectCode,
+      RegisterGroup: 0,
+      RegisterGroupId: revision.registerGroupId || 0,
+      RegisterKindCode: "RECW",
+      RegisterSerialNo: parseInt(revision.revision) || 1,
+      RevNo: revision.revision,
+      SGbn: revision.sGbn,
+      UploadId: uploadId
+    }
+  }
+
+  /**
+   * 파일 버퍼 읽기 (실제 파일 시스템 기반) - 타입 에러 수정
+   */
+  private async getFileBuffer(filePath: string): Promise<ArrayBuffer> {
+    try {
+      console.log(`Reading file from path: ${filePath}`)
+      
+      if (filePath.startsWith('http')) {
+        // URL인 경우 직접 다운로드
+        const response = await fetch(filePath)
+        if (!response.ok) {
+          throw new Error(`Failed to download file: ${response.status}`)
+        }
+        return await response.arrayBuffer()
+      } else {
+        // 로컬 파일 경로인 경우
+        const fs = await import('fs')
+        const path = await import('path')
+        
+        let actualFilePath: string
+        
+        if (filePath.startsWith('/documents/')) {
+          // DB에 저장된 경로 형태: "/documents/[uuid].ext"
+          // 실제 파일 시스템 경로로 변환: "public/documents/[uuid].ext"
+          actualFilePath = path.join(process.cwd(), 'public', filePath)
+        } else if (filePath.startsWith('/')) {
+          // 절대 경로인 경우 public 디렉토리 기준으로 변환
+          actualFilePath = path.join(process.cwd(), 'public', filePath)
+        } else {
+          // 상대 경로인 경우 그대로 사용
+          actualFilePath = filePath
+        }
+        
+        // 파일 존재 여부 확인
+        if (!fs.existsSync(actualFilePath)) {
+          throw new Error(`File not found: ${actualFilePath}`)
+        }
+        
+        // 파일 읽기
+        const fileBuffer = fs.readFileSync(actualFilePath)
+        console.log(`✅ File read successfully: ${actualFilePath} (${fileBuffer.length} bytes)`)
+        
+        // Buffer를 ArrayBuffer로 변환 (타입 안전성 보장)
+        return new ArrayBuffer(fileBuffer.length).slice(0).constructor(fileBuffer)
+      }
+    } catch (error) {
+      console.error(`❌ Failed to read file: ${filePath}`, error)
+      throw error
+    }
+  }
+
+  /**
+   * 리비전 상태 업데이트 (업데이트된 스키마 사용)
+   */
+  private async updateRevisionStatus(revisionId: number, status: string, uploadId?: string) {
+    const updateData: any = {
+      revisionStatus: status,
+      updatedAt: new Date()
+    }
+
+    // 업로드 성공 시 관련 날짜 설정
+    if (status === 'SUBMITTED') {
+      updateData.submittedDate = new Date().toISOString().slice(0, 10)
+    //   updateData.externalSentAt = new Date().toISOString().slice(0, 10)
+    } else if (status === 'APPROVED') {
+      updateData.approvedDate = new Date().toISOString().slice(0, 10)
+    }
+
+    // DOLCE 업로드 ID 저장
+    if (uploadId) {
+      updateData.externalUploadId = uploadId
+    }
+
+    await db
+      .update(revisions)
+      .set(updateData)
+      .where(eq(revisions.id, revisionId))
+
+    console.log(`✅ Updated revision ${revisionId} status to ${status}${uploadId ? ` with upload ID: ${uploadId}` : ''}`)
+  }
+
+  /**
+   * 업로드 가능 여부 확인
+   */
+  isUploadEnabled(): boolean {
+    const enabled = process.env.DOLCE_UPLOAD_ENABLED
+    return enabled === 'true' || enabled === '1'
+  }
+}
+
+export const dolceUploadService = new DOLCEUploadService()
+
+// 편의 함수
+export async function uploadRevisionsToDOLCE(
+  contractId: number,
+  revisionIds: number[],
+  userId: string,
+  userName?: string
+): Promise<DOLCEUploadResult> {
+  return dolceUploadService.uploadToDoLCE(contractId, revisionIds, userId, userName)
+}
\ No newline at end of file