(대표님) 20250627 오전 10시 작업사항

1 files changed, 265 insertions, 1 deletions

diff --git a/lib/evaluation/service.ts b/lib/evaluation/service.ts
index 3cc4ca7d..19e41dff 100644
--- a/lib/evaluation/service.ts
+++ b/lib/evaluation/service.ts
@@ -1,5 +1,8 @@
+'use server'
+
 import db from "@/db/db"
 import { 
+  evaluationSubmissions,
   periodicEvaluationsView, 
   type PeriodicEvaluationView 
 } from "@/db/schema"
@@ -9,7 +12,7 @@ import {
   count, 
   desc, 
   ilike, 
-  or, 
+  or, sql , eq, avg, 
   type SQL 
 } from "drizzle-orm"
 import { filterColumns } from "@/lib/filter-columns"
@@ -17,6 +20,7 @@ import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation
 
 export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema) {
     try {
+
       const offset = (input.page - 1) * input.perPage;
   
       // ✅ getEvaluationTargets 방식과 동일한 필터링 처리
@@ -115,6 +119,8 @@ export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema)
         .offset(offset);
   
       const pageCount = Math.ceil(total / input.perPage);
+
+      console.log(periodicEvaluationsData,"periodicEvaluationsData")
   
       return { data: periodicEvaluationsData, pageCount, total };
     } catch (err) {
@@ -122,4 +128,262 @@ export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema)
       // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함)
       return { data: [], pageCount: 0, total: 0 };
     }
+  }
+
+  export interface PeriodicEvaluationsStats {
+    total: number
+    pendingSubmission: number
+    submitted: number
+    inReview: number
+    reviewCompleted: number
+    finalized: number
+    averageScore: number | null
+    completionRate: number
+    averageFinalScore: number | null
+    documentsSubmittedCount: number
+    documentsNotSubmittedCount: number
+    reviewProgress: {
+      totalReviewers: number
+      completedReviewers: number
+      pendingReviewers: number
+      reviewCompletionRate: number
+    }
+  }
+  
+  export async function getPeriodicEvaluationsStats(evaluationYear: number): Promise<PeriodicEvaluationsStats> {
+    try {
+      // 기본 WHERE 조건: 해당 연도의 평가만
+      const baseWhere = eq(periodicEvaluationsView.evaluationYear, evaluationYear)
+  
+      // 1. 전체 통계 조회
+      const totalStatsResult = await db
+        .select({
+          total: count(),
+          averageScore: avg(periodicEvaluationsView.totalScore),
+          averageFinalScore: avg(periodicEvaluationsView.finalScore),
+        })
+        .from(periodicEvaluationsView)
+        .where(baseWhere)
+  
+      const totalStats = totalStatsResult[0] || { 
+        total: 0, 
+        averageScore: null, 
+        averageFinalScore: null 
+      }
+  
+      // 2. 상태별 카운트 조회
+      const statusStatsResult = await db
+        .select({
+          status: periodicEvaluationsView.status,
+          count: count(),
+        })
+        .from(periodicEvaluationsView)
+        .where(baseWhere)
+        .groupBy(periodicEvaluationsView.status)
+  
+      // 상태별 카운트를 객체로 변환
+      const statusCounts = statusStatsResult.reduce((acc, item) => {
+        acc[item.status] = item.count
+        return acc
+      }, {} as Record<string, number>)
+  
+      // 3. 문서 제출 상태 통계
+      const documentStatsResult = await db
+        .select({
+          documentsSubmitted: periodicEvaluationsView.documentsSubmitted,
+          count: count(),
+        })
+        .from(periodicEvaluationsView)
+        .where(baseWhere)
+        .groupBy(periodicEvaluationsView.documentsSubmitted)
+  
+      const documentCounts = documentStatsResult.reduce((acc, item) => {
+        if (item.documentsSubmitted) {
+          acc.submitted = item.count
+        } else {
+          acc.notSubmitted = item.count
+        }
+        return acc
+      }, { submitted: 0, notSubmitted: 0 })
+  
+      // 4. 리뷰어 진행 상황 통계
+      const reviewProgressResult = await db
+        .select({
+          totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})`.as('total_reviewers'),
+          completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})`.as('completed_reviewers'),
+          pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})`.as('pending_reviewers'),
+        })
+        .from(periodicEvaluationsView)
+        .where(baseWhere)
+  
+      const reviewProgress = reviewProgressResult[0] || {
+        totalReviewers: 0,
+        completedReviewers: 0,
+        pendingReviewers: 0,
+      }
+  
+      // 5. 완료율 계산
+      const finalizedCount = statusCounts['FINALIZED'] || 0
+      const totalCount = totalStats.total
+      const completionRate = totalCount > 0 ? Math.round((finalizedCount / totalCount) * 100) : 0
+  
+      // 6. 리뷰 완료율 계산
+      const reviewCompletionRate = reviewProgress.totalReviewers > 0 
+        ? Math.round((reviewProgress.completedReviewers / reviewProgress.totalReviewers) * 100) 
+        : 0
+  
+      // 7. 평균 점수 포맷팅 (소수점 1자리)
+      const formatScore = (score: string | number | null): number | null => {
+        if (score === null || score === undefined) return null
+        return Math.round(Number(score) * 10) / 10
+      }
+  
+      return {
+        total: totalCount,
+        pendingSubmission: statusCounts['PENDING_SUBMISSION'] || 0,
+        submitted: statusCounts['SUBMITTED'] || 0,
+        inReview: statusCounts['IN_REVIEW'] || 0,
+        reviewCompleted: statusCounts['REVIEW_COMPLETED'] || 0,
+        finalized: finalizedCount,
+        averageScore: formatScore(totalStats.averageScore),
+        averageFinalScore: formatScore(totalStats.averageFinalScore),
+        completionRate,
+        documentsSubmittedCount: documentCounts.submitted,
+        documentsNotSubmittedCount: documentCounts.notSubmitted,
+        reviewProgress: {
+          totalReviewers: reviewProgress.totalReviewers,
+          completedReviewers: reviewProgress.completedReviewers,
+          pendingReviewers: reviewProgress.pendingReviewers,
+          reviewCompletionRate,
+        },
+      }
+  
+    } catch (error) {
+      console.error('Error in getPeriodicEvaluationsStats:', error)
+      // 에러 발생 시 기본값 반환
+      return {
+        total: 0,
+        pendingSubmission: 0,
+        submitted: 0,
+        inReview: 0,
+        reviewCompleted: 0,
+        finalized: 0,
+        averageScore: null,
+        averageFinalScore: null,
+        completionRate: 0,
+        documentsSubmittedCount: 0,
+        documentsNotSubmittedCount: 0,
+        reviewProgress: {
+          totalReviewers: 0,
+          completedReviewers: 0,
+          pendingReviewers: 0,
+          reviewCompletionRate: 0,
+        },
+      }
+    }
+  }
+
+
+
+  interface RequestDocumentsData {
+    periodicEvaluationId: number
+    companyId: number
+    evaluationYear: number
+    evaluationRound: string
+    message: string
+  }
+  
+  export async function requestDocumentsFromVendors(data: RequestDocumentsData[]) {
+    try {
+      // 각 평가에 대해 evaluationSubmissions 레코드 생성
+      const submissions = await Promise.all(
+        data.map(async (item) => {
+          // 이미 해당 periodicEvaluationId와 companyId로 생성된 submission이 있는지 확인
+          const existingSubmission = await db.query.evaluationSubmissions.findFirst({
+            where: and(
+              eq(evaluationSubmissions.periodicEvaluationId, item.periodicEvaluationId),
+              eq(evaluationSubmissions.companyId, item.companyId)
+            )
+          })
+  
+          if (existingSubmission) {
+            // 이미 존재하면 reviewComments만 업데이트
+            const [updated] = await db
+              .update(evaluationSubmissions)
+              .set({
+                reviewComments: item.message,
+                updatedAt: new Date()
+              })
+              .where(eq(evaluationSubmissions.id, existingSubmission.id))
+              .returning()
+            
+            return updated
+          } else {
+            // 새로 생성
+            const [created] = await db
+              .insert(evaluationSubmissions)
+              .values({
+                periodicEvaluationId: item.periodicEvaluationId,
+                companyId: item.companyId,
+                evaluationYear: item.evaluationYear,
+                evaluationRound: item.evaluationRound,
+                submissionStatus: 'draft', // 기본값
+                reviewComments: item.message,
+                // 진행률 관련 필드들은 기본값 0으로 설정됨
+                totalGeneralItems: 0,
+                completedGeneralItems: 0,
+                totalEsgItems: 0,
+                completedEsgItems: 0,
+                isActive: true
+              })
+              .returning()
+            
+            return created
+          }
+        })
+      )
+  
+      
+      return {
+        success: true,
+        message: `${submissions.length}개 업체에 자료 요청이 완료되었습니다.`,
+        submissions
+      }
+  
+    } catch (error) {
+      console.error("Error requesting documents from vendors:", error)
+      return {
+        success: false,
+        message: "자료 요청 중 오류가 발생했습니다.",
+        error: error instanceof Error ? error.message : "Unknown error"
+      }
+    }
+  }
+  
+  // 기존 요청 상태 확인 함수 추가
+  export async function checkExistingSubmissions(periodicEvaluationIds: number[]) {
+    try {
+      const existingSubmissions = await db.query.evaluationSubmissions.findMany({
+        where: (submissions) => {
+          // periodicEvaluationIds 배열에 포함된 ID들을 확인
+          return periodicEvaluationIds.length === 1 
+            ? eq(submissions.periodicEvaluationId, periodicEvaluationIds[0])
+            : periodicEvaluationIds.length > 1
+            ? or(...periodicEvaluationIds.map(id => eq(submissions.periodicEvaluationId, id)))
+            : eq(submissions.id, -1) // 빈 배열인 경우 결과 없음
+        },
+        columns: {
+          id: true,
+          periodicEvaluationId: true,
+          companyId: true,
+          createdAt: true,
+          reviewComments: true
+        }
+      })
+  
+      return existingSubmissions
+    } catch (error) {
+      console.error("Error checking existing submissions:", error)
+      return []
+    }
   }
\ No newline at end of file