(대표님) 20250715 협력사 정기평가, spreadJS, roles 서비스에 함수 추가

3 files changed, 92 insertions, 29 deletions

diff --git a/lib/vendor-evaluation-submit/service.ts b/lib/vendor-evaluation-submit/service.ts
index 7be18fb8..3a31b380 100644
--- a/lib/vendor-evaluation-submit/service.ts
+++ b/lib/vendor-evaluation-submit/service.ts
@@ -17,7 +17,8 @@ import {
   EsgEvaluationResponse,
   esgEvaluations,
   esgAnswerOptions,
-  esgEvaluationItems
+  esgEvaluationItems,
+  periodicEvaluations
 } from "@/db/schema";
 import { and, asc, desc, eq, ilike, or, SQL, count , sql, avg} from "drizzle-orm";
 import { filterColumns } from "@/lib/filter-columns";
@@ -84,6 +85,27 @@ export async function getEvaluationSubmissions(input: GetEvaluationsSubmitSchema
 
     // 데이터 조회
     const { data, total } = await db.transaction(async (tx) => {
+
+      const totalGeneralItemsResult = await tx
+      .select({ count: count() })
+      .from(generalEvaluations)
+      .where(eq(generalEvaluations.isActive, true));
+    
+    const totalGeneralItemsCount = totalGeneralItemsResult[0]?.count || 0;
+
+    const totalEsgItemsResult = await tx
+    .select({ count: count() })
+    .from(esgEvaluationItems)
+    .innerJoin(esgEvaluations, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id))
+    .where(
+      and(
+        eq(esgEvaluations.isActive, true),
+        eq(esgEvaluationItems.isActive, true)
+      )
+    );
+  
+  const totalEGSItemsCount = totalEsgItemsResult[0]?.count || 0;
+
       // 메인 데이터 조회
       const data = await tx
         .select({
@@ -98,9 +120,9 @@ export async function getEvaluationSubmissions(input: GetEvaluationsSubmitSchema
           reviewedBy: evaluationSubmissions.reviewedBy,
           reviewComments: evaluationSubmissions.reviewComments,
           averageEsgScore: evaluationSubmissions.averageEsgScore,
-          totalGeneralItems: evaluationSubmissions.totalGeneralItems,
+        
           completedGeneralItems: evaluationSubmissions.completedGeneralItems,
-          totalEsgItems: evaluationSubmissions.totalEsgItems,
+        
           completedEsgItems: evaluationSubmissions.completedEsgItems,
           isActive: evaluationSubmissions.isActive,
           createdAt: evaluationSubmissions.createdAt,
@@ -161,6 +183,8 @@ export async function getEvaluationSubmissions(input: GetEvaluationsSubmitSchema
 
           return {
             ...submission,
+            totalGeneralItems: totalGeneralItemsCount ,
+            totalEsgItems: totalEGSItemsCount,
             _count: {
               generalResponses: generalCount,
               esgResponses: esgCount,
@@ -420,6 +444,18 @@ export async function updateEvaluationSubmissionStatus(
       .where(eq(evaluationSubmissions.id, submissionId))
       .returning();
 
+    // newStatus === 'submitted'일 때 periodicEvaluations 테이블도 업데이트
+    if (newStatus === 'submitted' && updatedSubmission) {
+      await tx
+        .update(periodicEvaluations)
+        .set({
+          documentsSubmitted: true,
+          submissionDate: new Date(),
+          updatedAt: new Date(),
+        })
+        .where(eq(periodicEvaluations.id, updatedSubmission.periodicEvaluationId));
+    }
+
     return updatedSubmission;
   });
 }
diff --git a/lib/vendor-evaluation-submit/table/esg-evaluation-form-sheet.tsx b/lib/vendor-evaluation-submit/table/esg-evaluation-form-sheet.tsx
index 53d25382..d90f60b8 100644
--- a/lib/vendor-evaluation-submit/table/esg-evaluation-form-sheet.tsx
+++ b/lib/vendor-evaluation-submit/table/esg-evaluation-form-sheet.tsx
@@ -1,7 +1,7 @@
 "use client"
 
 import * as React from "react"
-import { useForm } from "react-hook-form"
+import { useForm, useWatch } from "react-hook-form"
 import { zodResolver } from "@hookform/resolvers/zod"
 import * as z from "zod"
 import { SaveIcon, CheckIcon, XIcon, BarChart3Icon, TrendingUpIcon } from "lucide-react"
@@ -25,7 +25,6 @@ import { Button } from "@/components/ui/button"
 import { Textarea } from "@/components/ui/textarea"
 import { Badge } from "@/components/ui/badge"
 import { Card, CardContent, CardHeader, CardTitle } from "@/components/ui/card"
-import { ScrollArea } from "@/components/ui/scroll-area"
 import { Separator } from "@/components/ui/separator"
 import { RadioGroup, RadioGroupItem } from "@/components/ui/radio-group"
 import { toast } from "sonner"
@@ -81,6 +80,12 @@ export function EsgEvaluationFormSheet({
     }
   })
 
+  // 현재 폼 값을 실시간으로 감시
+  const watchedResponses = useWatch({
+    control: form.control,
+    name: 'responses'
+  })
+
   // 데이터 로딩
   React.useEffect(() => {
     if (open && submission?.id) {
@@ -207,33 +212,52 @@ export function EsgEvaluationFormSheet({
       total: 0, 
       percentage: 0, 
       averageScore: 0, 
-      maxAverageScore: 0 
+      maxAverageScore: 0,
+      totalPossibleScore: 0,
+      actualTotalScore: 0
     }
-
+  
     let total = 0
     let completed = 0
-    let totalScore = 0
+    let totalScore = 0  // 숫자로 초기화
     let maxTotalScore = 0
-
+  
     formData.evaluations.forEach(evaluation => {
       evaluation.items.forEach(item => {
         total++
-        if (currentScores[item.item.id] > 0) {
-          completed++
-          totalScore += currentScores[item.item.id]
-        }
         
-        // 최대 점수 계산
+        // 최대 점수 계산 (모든 항목에 대해)
         const maxOptionScore = Math.max(...item.answerOptions.map(opt => parseFloat(opt.score.toString())))
         maxTotalScore += maxOptionScore
+        
+        // 응답이 있는 경우에만 완료된 것으로 계산
+        const currentScore = currentScores[item.item.id]
+        if (currentScore !== undefined && currentScore >= 0) {
+          completed++
+          // 숫자로 명시적 변환하여 더하기
+          totalScore += Number(currentScore) || 0
+          console.log(`Adding score: ${Number(currentScore)}, Total so far: ${totalScore}`)
+        }
       })
     })
-
+  
     const percentage = total > 0 ? Math.round((completed / total) * 100) : 0
+    
+    // 응답한 항목들에 대해서만 평균 계산 (0으로 나누기 방지)
     const averageScore = completed > 0 ? totalScore / completed : 0
+    
+    // 전체 항목 기준 최대 평균 점수
     const maxAverageScore = total > 0 ? maxTotalScore / total : 0
-
-    return { completed, total, percentage, averageScore, maxAverageScore }
+  
+    return { 
+      completed, 
+      total, 
+      percentage, 
+      averageScore, 
+      maxAverageScore,
+      totalPossibleScore: maxTotalScore,
+      actualTotalScore: totalScore
+    }
   }
 
   const progress = getProgress()
@@ -241,7 +265,7 @@ export function EsgEvaluationFormSheet({
   if (isLoading) {
     return (
       <Sheet open={open} onOpenChange={onOpenChange}>
-        <SheetContent className="w-[900px] sm:max-w-[900px]">
+        <SheetContent className="w-[900px] sm:max-w-[900px]" style={{width:900, maxWidth:900}}>
           <div className="flex items-center justify-center h-full">
             <div className="text-center space-y-4">
               <div className="animate-spin rounded-full h-8 w-8 border-b-2 border-gray-900 mx-auto"></div>
@@ -256,7 +280,7 @@ export function EsgEvaluationFormSheet({
   return (
     <Sheet open={open} onOpenChange={onOpenChange}>
       <SheetContent className="w-[900px] sm:max-w-[900px] flex flex-col" style={{width:900, maxWidth:900}}>
-      <SheetHeader>
+        <SheetHeader>
           <SheetTitle>ESG 평가 작성</SheetTitle>
           <SheetDescription>
             {formData?.submission.vendorName}의 ESG 평가를 작성해주세요.
@@ -318,11 +342,10 @@ export function EsgEvaluationFormSheet({
             </div>
 
             <Form {...form}>
-              <form onSubmit={form.handleSubmit(onSubmit)} className="space-y-6">
-              <div className="flex-1 overflow-y-auto min-h-0">
-
-              <ScrollArea className="h-full pr-4">
-              <div className="space-y-4 pr-4">
+              <form onSubmit={form.handleSubmit(onSubmit)} className="flex-1 flex flex-col min-h-0">
+                {/* 스크롤 가능한 폼 영역 */}
+                <div className="flex-1 overflow-y-auto min-h-0 mt-6">
+                  <div className="space-y-4 pr-4">
                     <Accordion type="multiple" defaultValue={formData.evaluations.map((_, i) => `evaluation-${i}`)}>
                       {formData.evaluations.map((evaluation, evalIndex) => (
                         <AccordionItem 
@@ -348,7 +371,7 @@ export function EsgEvaluationFormSheet({
                                 <BarChart3Icon className="h-4 w-4" />
                                 <span className="text-sm">
                                   {evaluation.items.filter(item => 
-                                    currentScores[item.item.id] > 0
+                                    currentScores[item.item.id] >= 0
                                   ).length}/{evaluation.items.length}
                                 </span>
                               </div>
@@ -361,6 +384,10 @@ export function EsgEvaluationFormSheet({
                                   r => r.itemId === item.item.id
                                 )
                                 
+                                // watchedResponses에서 현재 응답 찾기
+                                const currentResponse = watchedResponses?.find(r => r.itemId === item.item.id)
+                                const selectedOptionId = currentResponse?.selectedOptionId?.toString() || ''
+                                
                                 return (
                                   <Card key={item.item.id} className="bg-gray-50">
                                     <CardHeader className="pb-3">
@@ -381,7 +408,7 @@ export function EsgEvaluationFormSheet({
                                     <CardContent className="space-y-4">
                                       {/* 답변 옵션들 */}
                                       <RadioGroup
-                                        value={item.response?.esgAnswerOptionId?.toString() || ''}
+                                        value={selectedOptionId}
                                         onValueChange={(value) => {
                                           const option = item.answerOptions.find(
                                             opt => opt.id === parseInt(value)
@@ -457,13 +484,12 @@ export function EsgEvaluationFormSheet({
                       ))}
                     </Accordion>
                   </div>
-                </ScrollArea>
                 </div>
 
-                <Separator />
+                <Separator className="my-4" />
 
                 {/* 하단 버튼 영역 */}
-                <div className="flex-shrink-0 flex items-center justify-between pt-4">
+                <div className="flex-shrink-0 flex items-center justify-between">
                   <div className="text-sm text-muted-foreground">
                     {progress.percentage === 100 ? (
                       <div className="flex items-center gap-2 text-green-600">
diff --git a/lib/vendor-evaluation-submit/table/general-evaluation-form-sheet.tsx b/lib/vendor-evaluation-submit/table/general-evaluation-form-sheet.tsx
index cc80e29c..bda087bb 100644
--- a/lib/vendor-evaluation-submit/table/general-evaluation-form-sheet.tsx
+++ b/lib/vendor-evaluation-submit/table/general-evaluation-form-sheet.tsx
@@ -35,6 +35,7 @@ import {
   saveGeneralEvaluationResponse,
   recalculateEvaluationProgress, // 진행률만 계산
   GeneralEvaluationFormData,
+  updateAttachmentStatus,
 } from "../service"
 import { EvaluationSubmissionWithVendor } from "../service"