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

1 files changed, 487 insertions, 0 deletions

diff --git a/lib/vendor-document-list/import-service.ts b/lib/vendor-document-list/import-service.ts
new file mode 100644
index 00000000..4a152299
--- /dev/null
+++ b/lib/vendor-document-list/import-service.ts
@@ -0,0 +1,487 @@
+// lib/vendor-document-list/import-service.ts - DOLCE API 연동 버전
+
+import db from "@/db/db"
+import { documents, issueStages, contracts, projects, vendors } from "@/db/schema"
+import { eq, and, desc, sql } from "drizzle-orm"
+
+export interface ImportResult {
+  success: boolean
+  newCount: number
+  updatedCount: number
+  skippedCount: number
+  errors?: string[]
+  message?: string
+}
+
+export interface ImportStatus {
+  lastImportAt?: string
+  availableDocuments: number
+  newDocuments: number
+  updatedDocuments: number
+  importEnabled: boolean
+}
+
+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
+  GTTPreDwg_PlanDate?: string
+  GTTPreDwg_ResultDate?: string
+  GTTWorkingDwg_PlanDate?: string
+  GTTWorkingDwg_ResultDate?: string
+  JGbn?: string
+  Manager: string
+  ManagerENM: string
+  ManagerNo: string
+  ProjectNo: string
+  RegisterGroup: number
+  RegisterGroupId: number
+  SGbn?: string
+  SHIDrawingNo?: string
+}
+
+class ImportService {
+  /**
+   * DOLCE 시스템에서 문서 목록 가져오기
+   */
+  async importFromExternalSystem(
+    contractId: number,
+    sourceSystem: string = 'DOLCE'
+  ): Promise<ImportResult> {
+    try {
+      console.log(`Starting import from ${sourceSystem} for contract ${contractId}`)
+
+      // 1. 계약 정보를 통해 프로젝트 코드와 벤더 코드 조회
+      const contractInfo = await this.getContractInfoById(contractId)
+      if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
+        throw new Error(`Project code or vendor code not found for contract ${contractId}`)
+      }
+
+      // 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)
+          console.log(`Fetched ${documents.length} documents for ${drawingKind}`)
+        } catch (error) {
+          console.warn(`Failed to fetch ${drawingKind} documents:`, error)
+          // 개별 drawingKind 실패는 전체 실패로 처리하지 않음
+        }
+      }
+      
+      if (allDocuments.length === 0) {
+        return {
+          success: true,
+          newCount: 0,
+          updatedCount: 0,
+          skippedCount: 0,
+          message: '가져올 새로운 데이터가 없습니다.'
+        }
+      }
+
+      let newCount = 0
+      let updatedCount = 0
+      let skippedCount = 0
+      const errors: string[] = []
+
+      // 3. 각 문서 동기화 처리
+      for (const dolceDoc of allDocuments) {
+        try {
+          const result = await this.syncSingleDocument(contractId, dolceDoc, sourceSystem)
+          
+          if (result === 'NEW') {
+            newCount++
+            // B4 문서의 경우 이슈 스테이지 자동 생성
+            if (dolceDoc.DrawingKind === 'B4') {
+              await this.createIssueStagesForB4Document(dolceDoc.DrawingNo, contractId, dolceDoc)
+            }
+          } else if (result === 'UPDATED') {
+            updatedCount++
+          } else {
+            skippedCount++
+          }
+
+        } catch (error) {
+          errors.push(`Document ${dolceDoc.DrawingNo}: ${error instanceof Error ? error.message : 'Unknown error'}`)
+          skippedCount++
+        }
+      }
+
+      console.log(`Import completed: ${newCount} new, ${updatedCount} updated, ${skippedCount} skipped`)
+
+      return {
+        success: errors.length === 0,
+        newCount,
+        updatedCount,
+        skippedCount,
+        errors: errors.length > 0 ? errors : undefined,
+        message: `가져오기 완료: 신규 ${newCount}건, 업데이트 ${updatedCount}건`
+      }
+
+    } catch (error) {
+      console.error('Import failed:', error)
+      throw error
+    }
+  }
+
+  /**
+   * 계약 ID로 프로젝트 코드와 벤더 코드 조회
+   */
+  private async getContractInfoById(contractId: number): Promise<{
+    projectCode: string;
+    vendorCode: string;
+  } | null> {
+    const [result] = await db
+      .select({ 
+        projectCode: projects.code,
+        vendorCode: vendors.vendorCode
+      })
+      .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?.projectCode && result?.vendorCode 
+      ? { projectCode: result.projectCode, vendorCode: result.vendorCode }
+      : null
+  }
+
+  /**
+   * DOLCE API에서 데이터 조회
+   */
+  private async fetchFromDOLCE(
+    projectCode: string,
+    vendorCode: string, 
+    drawingKind: string
+  ): Promise<DOLCEDocument[]> {
+    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',
+          // DOLCE API에 특별한 인증이 필요하다면 여기에 추가
+        },
+        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()
+      
+      // 응답 구조에 따라 조정 필요 (실제 API 응답 구조 확인 후)
+      if (Array.isArray(data)) {
+        return data as DOLCEDocument[]
+      } else if (data.documents && Array.isArray(data.documents)) {
+        return data.documents as DOLCEDocument[]
+      } else if (data.data && Array.isArray(data.data)) {
+        return data.data 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
+    }
+  }
+
+  /**
+   * 단일 문서 동기화
+   */
+  private async syncSingleDocument(
+    contractId: number,
+    dolceDoc: DOLCEDocument,
+    sourceSystem: string
+  ): Promise<'NEW' | 'UPDATED' | 'SKIPPED'> {
+    // 기존 문서 조회 (문서 번호로)
+    const existingDoc = await db
+      .select()
+      .from(documents)
+      .where(and(
+        eq(documents.contractId, contractId),
+        eq(documents.docNumber, dolceDoc.DrawingNo)
+      ))
+      .limit(1)
+
+    // DOLCE 문서를 DB 스키마에 맞게 변환
+    const documentData = {
+      contractId,
+      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) {
+        await db
+          .update(documents)
+          .set(documentData)
+          .where(eq(documents.id, existing.id))
+
+        console.log(`Updated document: ${dolceDoc.DrawingNo}`)
+        return 'UPDATED'
+      } else {
+        return 'SKIPPED'
+      }
+    } else {
+      // 새 문서 생성
+      const [newDoc] = await db
+        .insert(documents)
+        .values({
+          ...documentData,
+          createdAt: new Date()
+        })
+        .returning({ id: documents.id })
+
+      console.log(`Created new document: ${dolceDoc.DrawingNo}`)
+      return 'NEW'
+    }
+  }
+
+  /**
+   * B4 문서용 이슈 스테이지 자동 생성
+   */
+  private async createIssueStagesForB4Document(
+    drawingNo: string,
+    contractId: number,
+    dolceDoc: DOLCEDocument
+  ): Promise<void> {
+    try {
+      // 문서 ID 조회
+      const [document] = await db
+        .select({ id: documents.id })
+        .from(documents)
+        .where(and(
+          eq(documents.contractId, contractId),
+          eq(documents.docNumber, drawingNo)
+        ))
+        .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: 'For Pre',
+          planDate: dolceDoc.GTTPreDwg_PlanDate ? dolceDoc.GTTPreDwg_PlanDate : null,
+          actualDate: dolceDoc.GTTPreDwg_ResultDate ? dolceDoc.GTTPreDwg_ResultDate : null,
+          stageStatus: dolceDoc.GTTPreDwg_ResultDate ? 'COMPLETED' : 'PLANNED',
+          stageOrder: 1,
+          description: 'GTT 예비 도면 단계'
+        })
+      }
+
+      // For Working 스테이지 생성 (GTTWorkingDwg)
+      if (!existingStageNames.includes('For Working')) {
+        await db.insert(issueStages).values({
+          documentId: documentId,
+          stageName: 'For Working',
+          planDate: dolceDoc.GTTWorkingDwg_PlanDate ? dolceDoc.GTTWorkingDwg_PlanDate : null,
+          actualDate: dolceDoc.GTTWorkingDwg_ResultDate ? dolceDoc.GTTWorkingDwg_ResultDate : null,
+          stageStatus: dolceDoc.GTTWorkingDwg_ResultDate ? 'COMPLETED' : '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)
+      // 이슈 스테이지 생성 실패는 전체 문서 생성을 막지 않음
+    }
+  }
+
+  /**
+   * 가져오기 상태 조회
+   */
+  async getImportStatus(
+    contractId: number,
+    sourceSystem: string = 'DOLCE'
+  ): Promise<ImportStatus> {
+    try {
+      // 마지막 가져오기 시간 조회
+      const [lastImport] = await db
+        .select({ 
+          lastSynced: sql<string>`MAX(${documents.externalSyncedAt})` 
+        })
+        .from(documents)
+        .where(and(
+          eq(documents.contractId, contractId),
+          eq(documents.externalSystemType, sourceSystem)
+        ))
+
+      // 프로젝트 코드와 벤더 코드 조회
+      const contractInfo = await this.getContractInfoById(contractId)
+
+      console.log(contractInfo,"contractInfo")
+
+      if (!contractInfo?.projectCode || !contractInfo?.vendorCode) {
+        throw new Error(`Project code or vendor code not found for contract ${contractId}`)
+      }
+
+      let availableDocuments = 0
+      let newDocuments = 0
+      let updatedDocuments = 0
+
+      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.contractId, contractId),
+                  eq(documents.docNumber, externalDoc.DrawingNo)
+                ))
+                .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++
+                  }
+                }
+              }
+            }
+          } catch (error) {
+            console.warn(`Failed to check ${drawingKind} for status:`, error)
+          }
+        }
+      } catch (error) {
+        console.warn(`Failed to fetch external data for status: ${error}`)
+      }
+
+      return {
+        lastImportAt: lastImport?.lastSynced ? new Date(lastImport.lastSynced).toISOString() : undefined,
+        availableDocuments,
+        newDocuments,
+        updatedDocuments,
+        importEnabled: this.isImportEnabled(sourceSystem)
+      }
+
+    } catch (error) {
+      console.error('Failed to get import status:', error)
+      throw error
+    }
+  }
+
+  /**
+   * 가져오기 활성화 여부 확인
+   */
+  private isImportEnabled(sourceSystem: string): boolean {
+    const upperSystem = sourceSystem.toUpperCase()
+    const enabled = process.env[`IMPORT_${upperSystem}_ENABLED`]
+    return enabled === 'true' || enabled === '1'
+  }
+}
+
+export const importService = new ImportService()
\ No newline at end of file