1 files changed, 369 insertions, 0 deletions

diff --git a/lib/evaluation/vendor-submission-service.ts b/lib/evaluation/vendor-submission-service.ts
new file mode 100644
index 00000000..388f382a
--- /dev/null
+++ b/lib/evaluation/vendor-submission-service.ts
@@ -0,0 +1,369 @@
+'use server'
+
+import db from "@/db/db"
+import {
+  evaluationSubmissions,
+  generalEvaluations,
+  generalEvaluationResponses,
+  esgEvaluations,
+  esgEvaluationItems,
+  esgAnswerOptions,
+  esgEvaluationResponses,
+  vendorEvaluationAttachments,
+  periodicEvaluations,
+  evaluationTargets,
+  vendors,
+} from "@/db/schema"
+import { eq, and, asc, desc, sql } from "drizzle-orm"
+
+// 협력업체 제출 상세 정보 타입 정의
+export interface VendorSubmissionDetail {
+  // 제출 기본 정보
+  submissionId: string
+  evaluationYear: number
+  evaluationRound: string | null
+  submissionStatus: string
+  submittedAt: Date | null
+  reviewedAt: Date | null
+  reviewedBy: string | null
+  reviewComments: string | null
+  averageEsgScore: number | null
+  
+  // 진행률 통계
+  totalGeneralItems: number
+  completedGeneralItems: number
+  totalEsgItems: number
+  completedEsgItems: number
+  
+  // 협력업체 정보
+  vendor: {
+    id: number
+    vendorCode: string
+    vendorName: string
+    email: string | null
+    country: string | null
+  }
+  
+  // 일반평가 응답
+  generalEvaluations: {
+    id: number
+    serialNumber: string
+    category: string
+    inspectionItem: string
+    remarks: string | null
+    response: {
+      responseText: string
+      hasAttachments: boolean
+      reviewComments: string | null
+      attachments: {
+        id: number
+        fileId: string
+        originalFileName: string
+        storedFileName: string
+        filePath: string
+        fileSize: number
+        mimeType: string | null
+        uploadedBy: string
+        createdAt: Date
+      }[]
+    } | null
+  }[]
+  
+  // ESG 평가 응답
+  esgEvaluations: {
+    id: number
+    serialNumber: string
+    category: string
+    inspectionItem: string
+    evaluationItems: {
+      id: number
+      evaluationItem: string
+      evaluationItemDescription: string | null
+      orderIndex: number
+      response: {
+        selectedScore: number
+        additionalComments: string | null
+        answerOption: {
+          id: number
+          answerText: string
+          score: number
+        }
+      } | null
+    }[]
+  }[]
+  
+  // 첨부파일 통계
+  attachmentStats: {
+    totalFiles: number
+    totalSize: number
+    generalEvaluationFiles: number
+    esgEvaluationFiles: number
+  }
+}
+
+/**
+ * 특정 정기평가에 대한 협력업체 제출 상세 정보를 조회합니다
+ */
+export async function getVendorSubmissionDetails(periodicEvaluationId: number): Promise<VendorSubmissionDetail | null> {
+  try {
+    // 1. 제출 정보 조회
+    const submissionResult = await db
+      .select({
+        // 제출 기본 정보
+        id: evaluationSubmissions.id,
+        submissionId: evaluationSubmissions.submissionId,
+        evaluationYear: evaluationSubmissions.evaluationYear,
+        evaluationRound: evaluationSubmissions.evaluationRound,
+        submissionStatus: evaluationSubmissions.submissionStatus,
+        submittedAt: evaluationSubmissions.submittedAt,
+        reviewedAt: evaluationSubmissions.reviewedAt,
+        reviewedBy: evaluationSubmissions.reviewedBy,
+        reviewComments: evaluationSubmissions.reviewComments,
+        averageEsgScore: evaluationSubmissions.averageEsgScore,
+        
+        // 진행률 통계
+        totalGeneralItems: evaluationSubmissions.totalGeneralItems,
+        completedGeneralItems: evaluationSubmissions.completedGeneralItems,
+        totalEsgItems: evaluationSubmissions.totalEsgItems,
+        completedEsgItems: evaluationSubmissions.completedEsgItems,
+        
+        // 협력업체 정보
+        vendorId: vendors.id,
+        vendorCode: vendors.vendorCode,
+        vendorName: vendors.vendorName,
+        vendorEmail: vendors.email,
+        vendorCountry: vendors.country,
+      })
+      .from(evaluationSubmissions)
+      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
+      .where(
+        and(
+          eq(evaluationSubmissions.periodicEvaluationId, periodicEvaluationId),
+          eq(evaluationSubmissions.isActive, true)
+        )
+      )
+      .limit(1)
+
+    if (submissionResult.length === 0) {
+      return null // 제출 내용이 없음
+    }
+
+    const submission = submissionResult[0]
+    const submissionId = submission.id // evaluationSubmissions.id (integer)
+    const submissionUuid = submission.submissionId // evaluationSubmissions.submissionId (UUID)
+
+    console.log("=== 협력업체 제출 상세 조회 시작 ===")
+    console.log("submissionId (integer):", submissionId)
+    console.log("submissionUuid:", submissionUuid)
+    console.log("submission:", submission)
+
+    // 2. 일반평가 항목과 응답 조회
+    const generalEvaluationsResult = await db
+      .select({
+        // 일반평가 항목 정보
+        generalEvaluationId: generalEvaluations.id,
+        serialNumber: generalEvaluations.serialNumber,
+        category: generalEvaluations.category,
+        inspectionItem: generalEvaluations.inspectionItem,
+        remarks: generalEvaluations.remarks,
+        
+        // 응답 정보
+        responseId: generalEvaluationResponses.id,
+        responseText: generalEvaluationResponses.responseText,
+        hasAttachments: generalEvaluationResponses.hasAttachments,
+        reviewComments: generalEvaluationResponses.reviewComments,
+        
+        // 첨부파일 정보
+        attachmentId: vendorEvaluationAttachments.id,
+        fileId: vendorEvaluationAttachments.fileId,
+        originalFileName: vendorEvaluationAttachments.originalFileName,
+        storedFileName: vendorEvaluationAttachments.storedFileName,
+        filePath: vendorEvaluationAttachments.filePath,
+        fileSize: vendorEvaluationAttachments.fileSize,
+        mimeType: vendorEvaluationAttachments.mimeType,
+        uploadedBy: vendorEvaluationAttachments.uploadedBy,
+        attachmentCreatedAt: vendorEvaluationAttachments.createdAt,
+      })
+      .from(generalEvaluations)
+      .leftJoin(
+        generalEvaluationResponses,
+        and(
+          eq(generalEvaluationResponses.generalEvaluationId, generalEvaluations.id),
+          eq(generalEvaluationResponses.submissionId, submissionId),
+          eq(generalEvaluationResponses.isActive, true)
+        )
+      )
+      .leftJoin(
+        vendorEvaluationAttachments,
+        eq(vendorEvaluationAttachments.generalEvaluationResponseId, generalEvaluationResponses.id)
+      )
+      .where(eq(generalEvaluations.isActive, true))
+      .orderBy(asc(generalEvaluations.serialNumber))
+
+    // 3. ESG 평가 항목과 응답 조회
+    const esgEvaluationsResult = await db
+      .select({
+        // ESG 평가표 정보
+        esgEvaluationId: esgEvaluations.id,
+        esgSerialNumber: esgEvaluations.serialNumber,
+        esgCategory: esgEvaluations.category,
+        esgInspectionItem: esgEvaluations.inspectionItem,
+        
+        // ESG 평가항목 정보
+        esgEvaluationItemId: esgEvaluationItems.id,
+        evaluationItem: esgEvaluationItems.evaluationItem,
+        evaluationItemDescription: esgEvaluationItems.evaluationItemDescription,
+        orderIndex: esgEvaluationItems.orderIndex,
+        
+        // ESG 응답 정보
+        esgResponseId: esgEvaluationResponses.id,
+        selectedScore: esgEvaluationResponses.selectedScore,
+        additionalComments: esgEvaluationResponses.additionalComments,
+        
+        // ESG 답변 옵션 정보
+        answerOptionId: esgAnswerOptions.id,
+        answerText: esgAnswerOptions.answerText,
+        answerScore: esgAnswerOptions.score,
+      })
+      .from(esgEvaluations)
+      .innerJoin(esgEvaluationItems, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id))
+      .leftJoin(
+        esgEvaluationResponses,
+        and(
+          eq(esgEvaluationResponses.esgEvaluationItemId, esgEvaluationItems.id),
+          eq(esgEvaluationResponses.submissionId, submissionId),
+          eq(esgEvaluationResponses.isActive, true)
+        )
+      )
+      .leftJoin(
+        esgAnswerOptions,
+        eq(esgAnswerOptions.id, esgEvaluationResponses.esgAnswerOptionId)
+      )
+      .where(
+        and(
+          eq(esgEvaluations.isActive, true),
+          eq(esgEvaluationItems.isActive, true)
+        )
+      )
+      .orderBy(
+        asc(esgEvaluations.serialNumber),
+        asc(esgEvaluationItems.orderIndex)
+      )
+
+    // 4. 데이터 가공
+    // 일반평가 데이터 그룹화
+    const generalEvaluationsMap = new Map<number, any>()
+    generalEvaluationsResult.forEach(row => {
+      if (!generalEvaluationsMap.has(row.generalEvaluationId)) {
+        generalEvaluationsMap.set(row.generalEvaluationId, {
+          id: row.generalEvaluationId,
+          serialNumber: row.serialNumber,
+          category: row.category,
+          inspectionItem: row.inspectionItem,
+          remarks: row.remarks,
+          response: row.responseId ? {
+            responseText: row.responseText,
+            hasAttachments: row.hasAttachments,
+            reviewComments: row.reviewComments,
+            attachments: []
+          } : null
+        })
+      }
+
+      // 첨부파일 추가
+      if (row.attachmentId && generalEvaluationsMap.get(row.generalEvaluationId)?.response) {
+        generalEvaluationsMap.get(row.generalEvaluationId).response.attachments.push({
+          id: row.attachmentId,
+          fileId: row.fileId,
+          originalFileName: row.originalFileName,
+          storedFileName: row.storedFileName,
+          filePath: row.filePath,
+          fileSize: row.fileSize,
+          mimeType: row.mimeType,
+          uploadedBy: row.uploadedBy,
+          createdAt: new Date(row.attachmentCreatedAt)
+        })
+      }
+    })
+
+    // ESG 평가 데이터 그룹화
+    const esgEvaluationsMap = new Map<number, any>()
+    esgEvaluationsResult.forEach(row => {
+      if (!esgEvaluationsMap.has(row.esgEvaluationId)) {
+        esgEvaluationsMap.set(row.esgEvaluationId, {
+          id: row.esgEvaluationId,
+          serialNumber: row.esgSerialNumber,
+          category: row.esgCategory,
+          inspectionItem: row.esgInspectionItem,
+          evaluationItems: []
+        })
+      }
+
+      const esgEvaluation = esgEvaluationsMap.get(row.esgEvaluationId)
+      
+      // 평가항목 추가 (중복 방지)
+      const existingItem = esgEvaluation.evaluationItems.find((item: any) => item.id === row.esgEvaluationItemId)
+      if (!existingItem) {
+        esgEvaluation.evaluationItems.push({
+          id: row.esgEvaluationItemId,
+          evaluationItem: row.evaluationItem,
+          evaluationItemDescription: row.evaluationItemDescription,
+          orderIndex: row.orderIndex,
+          response: row.esgResponseId ? {
+            selectedScore: Number(row.selectedScore),
+            additionalComments: row.additionalComments,
+            answerOption: {
+              id: row.answerOptionId,
+              answerText: row.answerText,
+              score: Number(row.answerScore)
+            }
+          } : null
+        })
+      }
+    })
+
+    // 5. 첨부파일 통계 계산
+    const allAttachments = generalEvaluationsResult
+      .filter(row => row.attachmentId)
+      .map(row => ({
+        id: row.attachmentId,
+        fileSize: row.fileSize
+      }))
+
+    const attachmentStats = {
+      totalFiles: allAttachments.length,
+      totalSize: allAttachments.reduce((sum, att) => sum + att.fileSize, 0),
+      generalEvaluationFiles: allAttachments.length,
+      esgEvaluationFiles: 0 // ESG는 첨부파일 없음
+    }
+
+    return {
+      submissionId: submission.submissionId,
+      evaluationYear: submission.evaluationYear,
+      evaluationRound: submission.evaluationRound,
+      submissionStatus: submission.submissionStatus,
+      submittedAt: submission.submittedAt,
+      reviewedAt: submission.reviewedAt,
+      reviewedBy: submission.reviewedBy,
+      reviewComments: submission.reviewComments,
+      averageEsgScore: submission.averageEsgScore ? Number(submission.averageEsgScore) : null,
+      
+
+      vendor: {
+        id: submission.vendorId,
+        vendorCode: submission.vendorCode || "",
+        vendorName: submission.vendorName,
+        email: submission.vendorEmail,
+        country: submission.vendorCountry,
+      },
+      
+      generalEvaluations: Array.from(generalEvaluationsMap.values()),
+      esgEvaluations: Array.from(esgEvaluationsMap.values()),
+      attachmentStats
+    }
+
+  } catch (error) {
+    console.error("Error fetching vendor submission details:", error)
+    throw new Error("협력업체 제출 상세 정보 조회 중 오류가 발생했습니다")
+  }
+}
\ No newline at end of file