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

1 files changed, 158 insertions, 143 deletions

diff --git a/lib/evaluation-target-list/service.ts b/lib/evaluation-target-list/service.ts
index 4559374b..251561f9 100644
--- a/lib/evaluation-target-list/service.ts
+++ b/lib/evaluation-target-list/service.ts
@@ -345,7 +345,7 @@ export async function createEvaluationTarget(
       // 담당자들 지정
       if (input.reviewers && input.reviewers.length > 0) {
         const reviewerIds = input.reviewers.map(r => r.reviewerUserId);
-        
+
         // 🔧 수정: SQL 배열 처리 개선
         const reviewerInfos = await tx
           .select({
@@ -354,26 +354,26 @@ export async function createEvaluationTarget(
           .from(users)
           .where(inArray(users.id, reviewerIds)); // sql 대신 inArray 사용
 
-          const reviewerAssignments: typeof evaluationTargetReviewers.$inferInsert[] = [
-            ...input.reviewers.map(r => {
-              const info = reviewerInfos.find(i => i.id === r.reviewerUserId);
-              return {
-                evaluationTargetId,
-                departmentCode: r.departmentCode,
-                departmentNameFrom: info?.departmentName ?? "TEST 부서",
-                reviewerUserId: r.reviewerUserId,
-                assignedBy: createdBy,
-              };
-            }),
-            // session user 추가
-            {
+        const reviewerAssignments: typeof evaluationTargetReviewers.$inferInsert[] = [
+          ...input.reviewers.map(r => {
+            const info = reviewerInfos.find(i => i.id === r.reviewerUserId);
+            return {
               evaluationTargetId,
-              departmentCode: "admin",
-              departmentNameFrom: "정기평가 관리자",
-              reviewerUserId: Number(session.user.id),
+              departmentCode: r.departmentCode,
+              departmentNameFrom: info?.departmentName ?? "TEST 부서",
+              reviewerUserId: r.reviewerUserId,
               assignedBy: createdBy,
-            }
-          ];
+            };
+          }),
+          // session user 추가
+          {
+            evaluationTargetId,
+            departmentCode: "admin",
+            departmentNameFrom: "정기평가 관리자",
+            reviewerUserId: Number(session.user.id),
+            assignedBy: createdBy,
+          }
+        ];
         await tx.insert(evaluationTargetReviewers).values(reviewerAssignments);
       }
 
@@ -423,14 +423,14 @@ export interface UpdateEvaluationTargetInput {
   ldClaimAmount?: number
   ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY"
   consensusStatus?: boolean | null
-  
+
   // 각 부서별 평가 결과
   orderIsApproved?: boolean | null
   procurementIsApproved?: boolean | null
   qualityIsApproved?: boolean | null
   designIsApproved?: boolean | null
   csIsApproved?: boolean | null
-  
+
   // 담당자 이메일 (사용자 ID로 변환됨)
   orderReviewerEmail?: string
   procurementReviewerEmail?: string
@@ -441,7 +441,7 @@ export interface UpdateEvaluationTargetInput {
 
 export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput) {
   console.log(input, "update input")
-  
+
   try {
     const session = await getServerSession(authOptions)
 
@@ -486,7 +486,7 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
       // 기본 정보가 있으면 업데이트
       if (Object.keys(updateFields).length > 0) {
         updateFields.updatedAt = new Date()
-        
+
         await tx
           .update(evaluationTargets)
           .set(updateFields)
@@ -530,7 +530,7 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
                   evaluationTargetId: input.id,
                   departmentCode: update.departmentCode,
                   reviewerUserId: Number(user[0].id),
-                  assignedBy:Number( session.user.id),
+                  assignedBy: Number(session.user.id),
                 })
             }
           }
@@ -550,8 +550,8 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
         if (review.isApproved !== undefined) {
           // 해당 부서의 담당자 조회
           const reviewer = await tx
-            .select({ 
-              reviewerUserId: evaluationTargetReviewers.reviewerUserId 
+            .select({
+              reviewerUserId: evaluationTargetReviewers.reviewerUserId
             })
             .from(evaluationTargetReviewers)
             .where(
@@ -598,10 +598,25 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
         .from(evaluationTargetReviews)
         .where(eq(evaluationTargetReviews.evaluationTargetId, input.id))
 
-      console.log("Current reviews:", currentReviews)
+
+      const evaluationTargetForConcensus = await tx
+        .select({
+          materialType: evaluationTargets.materialType,
+        })
+        .from(evaluationTargets)
+        .where(eq(evaluationTargets.id, input.id))
+        .limit(1)
+
+      if (evaluationTargetForConcensus.length === 0) {
+        throw new Error("평가 대상을 찾을 수 없습니다.")
+      }
+
+      const { materialType } = evaluationTargetForConcensus[0]
+      const minimumReviewsRequired = materialType === "BULK" ? 3 : 5
+
 
       // 최소 3개 부서에서 평가가 완료된 경우 의견 일치 상태 계산
-      if (currentReviews.length >= 3) {
+      if (currentReviews.length >= minimumReviewsRequired) {
         const approvals = currentReviews.map(r => r.isApproved)
         const allApproved = approvals.every(approval => approval === true)
         const allRejected = approvals.every(approval => approval === false)
@@ -617,7 +632,7 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
 
         await tx
           .update(evaluationTargets)
-          .set({ 
+          .set({
             consensusStatus: hasConsensus,
             confirmedAt: hasConsensus ? new Date() : null,
             confirmedBy: hasConsensus ? Number(session.user.id) : null,
@@ -710,24 +725,24 @@ export async function getDepartmentInfo() {
 
 
 export async function confirmEvaluationTargets(
-  targetIds: number[], 
+  targetIds: number[],
   evaluationPeriod?: string // "상반기", "하반기", "연간" 등
 ) {
   try {
     const session = await getServerSession(authOptions)
-    
+
     if (!session?.user) {
       return { success: false, error: "인증이 필요합니다." }
     }
-    
+
     if (targetIds.length === 0) {
       return { success: false, error: "선택된 평가 대상이 없습니다." }
     }
 
     // 평가 기간이 없으면 현재 날짜 기준으로 자동 결정
     // const currentPeriod = evaluationPeriod || getCurrentEvaluationPeriod()
-    const currentPeriod ="연간"
-    
+    const currentPeriod = "연간"
+
     // 트랜잭션으로 처리
     const result = await db.transaction(async (tx) => {
       // 확정 가능한 대상들 확인 (PENDING 상태이면서 consensusStatus가 true인 것들)
@@ -741,13 +756,13 @@ export async function confirmEvaluationTargets(
             eq(evaluationTargets.consensusStatus, true)
           )
         )
-      
+
       if (eligibleTargets.length === 0) {
         throw new Error("확정 가능한 평가 대상이 없습니다. (의견 일치 상태인 대기중 항목만 확정 가능)")
       }
-      
+
       const confirmedTargetIds = eligibleTargets.map(target => target.id)
-      
+
       // 1. 평가 대상 상태를 CONFIRMED로 변경
       await tx
         .update(evaluationTargets)
@@ -758,10 +773,10 @@ export async function confirmEvaluationTargets(
           updatedAt: new Date()
         })
         .where(inArray(evaluationTargets.id, confirmedTargetIds))
-      
+
       // 2. 각 확정된 평가 대상에 대해 periodicEvaluations 레코드 생성
       const periodicEvaluationsToCreate = []
-      
+
       for (const target of eligibleTargets) {
         // 이미 해당 기간에 평가가 존재하는지 확인
         const existingEvaluation = await tx
@@ -774,7 +789,7 @@ export async function confirmEvaluationTargets(
             )
           )
           .limit(1)
-        
+
         // 없으면 생성 목록에 추가
         if (existingEvaluation.length === 0) {
           periodicEvaluationsToCreate.push({
@@ -782,14 +797,14 @@ export async function confirmEvaluationTargets(
             evaluationPeriod: currentPeriod,
             // 평가년도에 따른 제출 마감일 설정 (예: 상반기는 7월 말, 하반기는 1월 말)
             submissionDeadline: getSubmissionDeadline(target.evaluationYear, currentPeriod),
-            status: "PENDING_SUBMISSION" as const,
+            status: "PENDING" as const,
             createdAt: new Date(),
             updatedAt: new Date()
           })
         }
         console.log("periodicEvaluationsToCreate", periodicEvaluationsToCreate)
       }
-      
+
       // 3. periodicEvaluations 레코드들 일괄 생성
       let createdEvaluationsCount = 0
       if (periodicEvaluationsToCreate.length > 0) {
@@ -797,7 +812,7 @@ export async function confirmEvaluationTargets(
           .insert(periodicEvaluations)
           .values(periodicEvaluationsToCreate)
           .returning({ id: periodicEvaluations.id })
-        
+
         createdEvaluationsCount = createdEvaluations.length
       }
       console.log("createdEvaluationsCount", createdEvaluationsCount)
@@ -807,13 +822,13 @@ export async function confirmEvaluationTargets(
         tx.select({ count: count() })
           .from(generalEvaluations)
           .where(eq(generalEvaluations.isActive, true)),
-        
+
         // 활성화된 ESG 평가항목 수
         tx.select({ count: count() })
           .from(esgEvaluationItems)
           .where(eq(esgEvaluationItems.isActive, true))
       ])
-      
+
       const totalGeneralItems = generalItemsCount[0]?.count || 0
       const totalEsgItems = esgItemsCount[0]?.count || 0
 
@@ -832,7 +847,7 @@ export async function confirmEvaluationTargets(
       //         eq(periodicEvaluations.evaluationPeriod, currentPeriod)
       //       )
       //     )
-        
+
       //   // 각 평가에 대해 담당자별 reviewerEvaluations 생성
       //   for (const periodicEval of newPeriodicEvaluations) {
       //     // 해당 evaluationTarget의 담당자들 조회
@@ -840,7 +855,7 @@ export async function confirmEvaluationTargets(
       //       .select()
       //       .from(evaluationTargetReviewers)
       //       .where(eq(evaluationTargetReviewers.evaluationTargetId, periodicEval.evaluationTargetId))
-          
+
       //     if (reviewers.length > 0) {
       //       const reviewerEvaluationsToCreate = reviewers.map(reviewer => ({
       //         periodicEvaluationId: periodicEval.id,
@@ -849,102 +864,102 @@ export async function confirmEvaluationTargets(
       //         createdAt: new Date(),
       //         updatedAt: new Date()
       //       }))
-            
+
       //       await tx
       //         .insert(reviewerEvaluations)
       //         .values(reviewerEvaluationsToCreate)
       //     }
       //   }
       // }
-      
+
       // 6. 벤더별 evaluationSubmissions 레코드 생성
-      const evaluationSubmissionsToCreate = []
-      
-      // 생성된 periodicEvaluations의 ID를 매핑하기 위한 맵 생성
-      const periodicEvaluationIdMap = new Map()
-      if (createdEvaluationsCount > 0) {
-        const createdEvaluations = await tx
-          .select({
-            id: periodicEvaluations.id,
-            evaluationTargetId: periodicEvaluations.evaluationTargetId
-          })
-          .from(periodicEvaluations)
-          .where(
-            and(
-              inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds),
-              eq(periodicEvaluations.evaluationPeriod, currentPeriod)
-            )
-          )
-        
-                 // evaluationTargetId를 키로 하는 맵 생성
-         createdEvaluations.forEach(periodicEval => {
-           periodicEvaluationIdMap.set(periodicEval.evaluationTargetId, periodicEval.id)
-         })
-      }
-      console.log("periodicEvaluationIdMap", periodicEvaluationIdMap)
-      
-      for (const target of eligibleTargets) {
-        // 이미 해당 년도/기간에 제출 레코드가 있는지 확인
-        const existingSubmission = await tx
-          .select({ id: evaluationSubmissions.id })
-          .from(evaluationSubmissions)
-          .where(
-            and(
-              eq(evaluationSubmissions.companyId, target.vendorId),
-              eq(evaluationSubmissions.evaluationYear, target.evaluationYear),
-              // eq(evaluationSubmissions.evaluationRound, currentPeriod)
-            )
-          )
-          .limit(1)
-        
-        // 없으면 생성 목록에 추가
-        if (existingSubmission.length === 0) {
-          const periodicEvaluationId = periodicEvaluationIdMap.get(target.id)
-          if (periodicEvaluationId) {
-            evaluationSubmissionsToCreate.push({
-              companyId: target.vendorId,
-              periodicEvaluationId: periodicEvaluationId,
-              evaluationYear: target.evaluationYear,
-              evaluationRound: currentPeriod,
-              submissionStatus: "draft" as const,
-              totalGeneralItems: totalGeneralItems,
-              completedGeneralItems: 0,
-              totalEsgItems: totalEsgItems,
-              completedEsgItems: 0,
-              isActive: true,
-              createdAt: new Date(),
-              updatedAt: new Date()
-            })
-          }
-        }
-      }
-      // 7. evaluationSubmissions 레코드들 일괄 생성
-      let createdSubmissionsCount = 0
-      if (evaluationSubmissionsToCreate.length > 0) {
-        const createdSubmissions = await tx
-          .insert(evaluationSubmissions)
-          .values(evaluationSubmissionsToCreate)
-          .returning({ id: evaluationSubmissions.id })
-        
-        createdSubmissionsCount = createdSubmissions.length
-      }
-      
+      // const evaluationSubmissionsToCreate = []
+
+      // // 생성된 periodicEvaluations의 ID를 매핑하기 위한 맵 생성
+      // const periodicEvaluationIdMap = new Map()
+      // if (createdEvaluationsCount > 0) {
+      //   const createdEvaluations = await tx
+      //     .select({
+      //       id: periodicEvaluations.id,
+      //       evaluationTargetId: periodicEvaluations.evaluationTargetId
+      //     })
+      //     .from(periodicEvaluations)
+      //     .where(
+      //       and(
+      //         inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds),
+      //         eq(periodicEvaluations.evaluationPeriod, currentPeriod)
+      //       )
+      //     )
+
+      //   // evaluationTargetId를 키로 하는 맵 생성
+      //   createdEvaluations.forEach(periodicEval => {
+      //     periodicEvaluationIdMap.set(periodicEval.evaluationTargetId, periodicEval.id)
+      //   })
+      // }
+      // console.log("periodicEvaluationIdMap", periodicEvaluationIdMap)
+
+      // for (const target of eligibleTargets) {
+      //   // 이미 해당 년도/기간에 제출 레코드가 있는지 확인
+      //   const existingSubmission = await tx
+      //     .select({ id: evaluationSubmissions.id })
+      //     .from(evaluationSubmissions)
+      //     .where(
+      //       and(
+      //         eq(evaluationSubmissions.companyId, target.vendorId),
+      //         eq(evaluationSubmissions.evaluationYear, target.evaluationYear),
+      //         // eq(evaluationSubmissions.evaluationRound, currentPeriod)
+      //       )
+      //     )
+      //     .limit(1)
+
+      //   // 없으면 생성 목록에 추가
+      //   if (existingSubmission.length === 0) {
+      //     const periodicEvaluationId = periodicEvaluationIdMap.get(target.id)
+      //     if (periodicEvaluationId) {
+      //       evaluationSubmissionsToCreate.push({
+      //         companyId: target.vendorId,
+      //         periodicEvaluationId: periodicEvaluationId,
+      //         evaluationYear: target.evaluationYear,
+      //         evaluationRound: currentPeriod,
+      //         submissionStatus: "draft" as const,
+      //         totalGeneralItems: totalGeneralItems,
+      //         completedGeneralItems: 0,
+      //         totalEsgItems: totalEsgItems,
+      //         completedEsgItems: 0,
+      //         isActive: true,
+      //         createdAt: new Date(),
+      //         updatedAt: new Date()
+      //       })
+      //     }
+      //   }
+      // }
+      // // 7. evaluationSubmissions 레코드들 일괄 생성
+      // let createdSubmissionsCount = 0
+      // if (evaluationSubmissionsToCreate.length > 0) {
+      //   const createdSubmissions = await tx
+      //     .insert(evaluationSubmissions)
+      //     .values(evaluationSubmissionsToCreate)
+      //     .returning({ id: evaluationSubmissions.id })
+
+      //   createdSubmissionsCount = createdSubmissions.length
+      // }
+
       return {
         confirmedTargetIds,
         createdEvaluationsCount,
-        createdSubmissionsCount,
+        // createdSubmissionsCount,
         totalConfirmed: confirmedTargetIds.length
       }
     })
-    
+
     return {
       success: true,
       message: `${result.totalConfirmed}개 평가 대상이 확정되었습니다. ${result.createdEvaluationsCount}개의 정기평가와 ${result.createdSubmissionsCount}개의 제출 요청이 생성되었습니다.`,
       confirmedCount: result.totalConfirmed,
       createdEvaluationsCount: result.createdEvaluationsCount,
-      createdSubmissionsCount: result.createdSubmissionsCount
+      // createdSubmissionsCount: result.createdSubmissionsCount
     }
-    
+
   } catch (error) {
     console.error("Error confirming evaluation targets:", error)
     return {
@@ -959,7 +974,7 @@ export async function confirmEvaluationTargets(
 function getCurrentEvaluationPeriod(): string {
   const now = new Date()
   const month = now.getMonth() + 1 // 0-based이므로 +1
-  
+
   // 1~6월: 상반기, 7~12월: 하반기
   return month <= 6 ? "상반기" : "하반기"
 }
@@ -967,7 +982,7 @@ function getCurrentEvaluationPeriod(): string {
 // 평가년도와 기간에 따른 제출 마감일 설정하는 헬퍼 함수
 function getSubmissionDeadline(evaluationYear: number, period: string): Date {
   const year = evaluationYear
-  
+
   if (period === "상반기") {
     // 상반기 평가는 다음 해 6월 말까지
     return new Date(year, 5, 31) // 7월은 6 (0-based)
@@ -1022,17 +1037,17 @@ export async function excludeEvaluationTargets(targetIds: number[]) {
     })
 
 
-    return { 
-      success: true, 
+    return {
+      success: true,
       message: `${targetIds.length}개 평가 대상이 제외되었습니다.`,
       excludedCount: targetIds.length
     }
 
   } catch (error) {
     console.error("Error excluding evaluation targets:", error)
-    return { 
-      success: false, 
-      error: error instanceof Error ? error.message : "제외 처리 중 오류가 발생했습니다." 
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "제외 처리 중 오류가 발생했습니다."
     }
   }
 }
@@ -1095,7 +1110,7 @@ export async function requestEvaluationReview(targetIds: number[], message?: str
           reviewers: []
         }
       }
-      
+
       if (item.reviewerEmail) {
         acc[item.id].reviewers.push({
           email: item.reviewerEmail,
@@ -1104,7 +1119,7 @@ export async function requestEvaluationReview(targetIds: number[], message?: str
           departmentName: item.departmentName
         })
       }
-      
+
       return acc
     }, {} as Record<number, any>)
 
@@ -1118,14 +1133,14 @@ export async function requestEvaluationReview(targetIds: number[], message?: str
       target.reviewers.forEach((reviewer: any) => {
         if (reviewer.email) {
           reviewerEmails.add(reviewer.email)
-          
+
           if (!reviewerInfo.has(reviewer.email)) {
             reviewerInfo.set(reviewer.email, {
               name: reviewer.name || reviewer.email,
               departments: []
             })
           }
-          
+
           const info = reviewerInfo.get(reviewer.email)!
           if (reviewer.departmentName && !info.departments.includes(reviewer.departmentName)) {
             info.departments.push(reviewer.departmentName)
@@ -1141,7 +1156,7 @@ export async function requestEvaluationReview(targetIds: number[], message?: str
     // 각 담당자에게 이메일 발송
     const emailPromises = Array.from(reviewerEmails).map(email => {
       const reviewer = reviewerInfo.get(email)!
-      
+
       return sendEmail({
         to: email,
         subject: `벤더 평가 의견 요청 - ${targets.length}건`,
@@ -1165,17 +1180,17 @@ export async function requestEvaluationReview(targetIds: number[], message?: str
 
     await Promise.all(emailPromises)
 
-    return { 
-      success: true, 
+    return {
+      success: true,
       message: `${reviewerEmails.size}명의 담당자에게 의견 요청 이메일이 발송되었습니다.`,
       emailCount: reviewerEmails.size
     }
 
   } catch (error) {
     console.error("Error requesting evaluation review:", error)
-    return { 
-      success: false, 
-      error: error instanceof Error ? error.message : "의견 요청 중 오류가 발생했습니다." 
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "의견 요청 중 오류가 발생했습니다."
     }
   }
 }
@@ -1220,7 +1235,7 @@ export async function autoGenerateEvaluationTargets(
         // vendor 정보
         vendorCode: vendors.vendorCode,
         vendorName: vendors.vendorName,
-        vendorType: vendors.country ==="KR"? "DOMESTIC":"FOREIGN", // DOMESTIC | FOREIGN
+        vendorType: vendors.country === "KR" ? "DOMESTIC" : "FOREIGN", // DOMESTIC | FOREIGN
         // project 정보  
         projectType: projects.type, // ship | plant
       })
@@ -1258,7 +1273,7 @@ export async function autoGenerateEvaluationTargets(
     contractsWithDetails.forEach(contract => {
       const division = contract.projectType === "ship" ? "SHIP" : "PLANT"
       const key = `${contract.vendorId}-${division}`
-      
+
       if (!targetGroups.has(key)) {
         targetGroups.set(key, {
           vendorId: contract.vendorId,