(대표님/최겸) 20250624 작업사항 10시43분

1 files changed, 303 insertions, 69 deletions

diff --git a/lib/evaluation-target-list/service.ts b/lib/evaluation-target-list/service.ts
index 572b468d..0da50fa2 100644
--- a/lib/evaluation-target-list/service.ts
+++ b/lib/evaluation-target-list/service.ts
@@ -17,13 +17,16 @@ import {
   type DomesticForeign,
   EVALUATION_DEPARTMENT_CODES,
   EvaluationTargetWithDepartments,
-  evaluationTargetsWithDepartments
+  evaluationTargetsWithDepartments,
+  periodicEvaluations,
+  reviewerEvaluations
 } from "@/db/schema";
 import { GetEvaluationTargetsSchema } from "./validation";
 import { PgTransaction } from "drizzle-orm/pg-core";
 import { getServerSession } from "next-auth/next"
 import { authOptions } from "@/app/api/auth/[...nextauth]/route"
 import { sendEmail } from "../mail/sendEmail";
+import type { SQL } from "drizzle-orm"
 
 export async function selectEvaluationTargetsFromView(
   tx: PgTransaction<any, any, any>,
@@ -60,76 +63,122 @@ export async function countEvaluationTargetsFromView(
 
 // ============= 메인 서버 액션도 함께 수정 =============
 
+
 export async function getEvaluationTargets(input: GetEvaluationTargetsSchema) {
   try {
     const offset = (input.page - 1) * input.perPage;
 
-    // 고급 필터링 (View 테이블 기준)
-    const advancedWhere = filterColumns({
-      table: evaluationTargetsWithDepartments,
-      filters: input.filters,
-      joinOperator: input.joinOperator,
-    });
+    // ✅ getRFQ 방식과 동일한 필터링 처리
+    // 1) 고급 필터 조건
+    let advancedWhere: SQL<unknown> | undefined = undefined;
+    if (input.filters && input.filters.length > 0) {
+      advancedWhere = filterColumns({
+        table: evaluationTargetsWithDepartments,
+        filters: input.filters,
+        joinOperator: input.joinOperator || 'and',
+      });
+    }
 
-    // 베이직 필터링 (커스텀 필터)
-    let basicWhere;
+    // 2) 기본 필터 조건
+    let basicWhere: SQL<unknown> | undefined = undefined;
     if (input.basicFilters && input.basicFilters.length > 0) {
       basicWhere = filterColumns({
         table: evaluationTargetsWithDepartments,
         filters: input.basicFilters,
-        joinOperator: input.basicJoinOperator || "and",
+        joinOperator: input.basicJoinOperator || 'and',
       });
     }
 
-    // 전역 검색 (View 테이블 기준)
-    let globalWhere;
+    // 3) 글로벌 검색 조건
+    let globalWhere: SQL<unknown> | undefined = undefined;
     if (input.search) {
       const s = `%${input.search}%`;
-      globalWhere = or(
-        ilike(evaluationTargetsWithDepartments.vendorCode, s),
-        ilike(evaluationTargetsWithDepartments.vendorName, s),
-        ilike(evaluationTargetsWithDepartments.adminComment, s),
-        ilike(evaluationTargetsWithDepartments.consolidatedComment, s),
-        // 담당자 이름으로도 검색 가능
-        ilike(evaluationTargetsWithDepartments.orderReviewerName, s),
-        ilike(evaluationTargetsWithDepartments.procurementReviewerName, s),
-        ilike(evaluationTargetsWithDepartments.qualityReviewerName, s),
-        ilike(evaluationTargetsWithDepartments.designReviewerName, s),
-        ilike(evaluationTargetsWithDepartments.csReviewerName, s)
-      );
+
+      const validSearchConditions: SQL<unknown>[] = [];
+
+      const vendorCodeCondition = ilike(evaluationTargetsWithDepartments.vendorCode, s);
+      if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);
+
+      const vendorNameCondition = ilike(evaluationTargetsWithDepartments.vendorName, s);
+      if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);
+
+      const adminCommentCondition = ilike(evaluationTargetsWithDepartments.adminComment, s);
+      if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);
+
+      const consolidatedCommentCondition = ilike(evaluationTargetsWithDepartments.consolidatedComment, s);
+      if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);
+
+      // 담당자 이름으로도 검색
+      const orderReviewerCondition = ilike(evaluationTargetsWithDepartments.orderReviewerName, s);
+      if (orderReviewerCondition) validSearchConditions.push(orderReviewerCondition);
+
+      const procurementReviewerCondition = ilike(evaluationTargetsWithDepartments.procurementReviewerName, s);
+      if (procurementReviewerCondition) validSearchConditions.push(procurementReviewerCondition);
+
+      const qualityReviewerCondition = ilike(evaluationTargetsWithDepartments.qualityReviewerName, s);
+      if (qualityReviewerCondition) validSearchConditions.push(qualityReviewerCondition);
+
+      const designReviewerCondition = ilike(evaluationTargetsWithDepartments.designReviewerName, s);
+      if (designReviewerCondition) validSearchConditions.push(designReviewerCondition);
+
+      const csReviewerCondition = ilike(evaluationTargetsWithDepartments.csReviewerName, s);
+      if (csReviewerCondition) validSearchConditions.push(csReviewerCondition);
+
+      if (validSearchConditions.length > 0) {
+        globalWhere = or(...validSearchConditions);
+      }
     }
 
-    const finalWhere = and(advancedWhere, basicWhere, globalWhere);
+    // ✅ getRFQ 방식과 동일한 WHERE 조건 생성
+    const whereConditions: SQL<unknown>[] = [];
 
-    // 정렬 (View 테이블 기준)
-    const orderBy = input.sort.length > 0
-      ? input.sort.map((item) => {
-        const column = evaluationTargetsWithDepartments[item.id as keyof typeof evaluationTargetsWithDepartments];
-        return item.desc ? desc(column) : asc(column);
-      })
-      : [desc(evaluationTargetsWithDepartments.createdAt)];
-
-    // 데이터 조회 - View 테이블 사용
-    const { data, total } = await db.transaction(async (tx) => {
-      const data = await selectEvaluationTargetsFromView(tx, {
-        where: finalWhere,
-        orderBy,
-        offset,
-        limit: input.perPage,
-      });
+    if (advancedWhere) whereConditions.push(advancedWhere);
+    if (basicWhere) whereConditions.push(basicWhere);
+    if (globalWhere) whereConditions.push(globalWhere);
+
+    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
 
-      const total = await countEvaluationTargetsFromView(tx, finalWhere);
-      return { data, total };
+    // ✅ getRFQ 방식과 동일한 전체 데이터 수 조회 (Transaction 제거)
+    const totalResult = await db
+      .select({ count: count() })
+      .from(evaluationTargetsWithDepartments)
+      .where(finalWhere);
+
+    const total = totalResult[0]?.count || 0;
+
+    if (total === 0) {
+      return { data: [], pageCount: 0, total: 0 };
+    }
+
+    console.log("Total evaluation targets:", total);
+
+    // ✅ getRFQ 방식과 동일한 정렬 및 페이징 처리된 데이터 조회
+    const orderByColumns = input.sort.map((sort) => {
+      const column = sort.id as keyof typeof evaluationTargetsWithDepartments.$inferSelect;
+      return sort.desc ? desc(evaluationTargetsWithDepartments[column]) : asc(evaluationTargetsWithDepartments[column]);
     });
 
+    if (orderByColumns.length === 0) {
+      orderByColumns.push(desc(evaluationTargetsWithDepartments.createdAt));
+    }
+
+    const evaluationData = await db
+      .select()
+      .from(evaluationTargetsWithDepartments)
+      .where(finalWhere)
+      .orderBy(...orderByColumns)
+      .limit(input.perPage)
+      .offset(offset);
+
     const pageCount = Math.ceil(total / input.perPage);
-    return { data, pageCount, total };
+
+    return { data: evaluationData, pageCount, total };
   } catch (err) {
     console.error("Error in getEvaluationTargets:", err);
-    return { data: [], pageCount: 0 };
+    // ✅ getRFQ 방식과 동일한 에러 반환 (total 포함)
+    return { data: [], pageCount: 0, total: 0 };
   }
 }
-
 // ============= 개별 조회 함수도 업데이트 =============
 
 export async function getEvaluationTargetById(id: number): Promise<EvaluationTargetWithDepartments | null> {
@@ -377,7 +426,8 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
   console.log(input, "update input")
   
   try {
-    const session = await auth()
+    const session = await getServerSession(authOptions)
+
     if (!session?.user) {
       throw new Error("인증이 필요합니다.")
     }
@@ -462,8 +512,8 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
                 .values({
                   evaluationTargetId: input.id,
                   departmentCode: update.departmentCode,
-                  reviewerUserId: user[0].id,
-                  assignedBy: session.user.id,
+                  reviewerUserId: Number(user[0].id),
+                  assignedBy:Number( session.user.id),
                 })
             }
           }
@@ -553,7 +603,7 @@ export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput)
             consensusStatus: hasConsensus,
             status: newStatus,
             confirmedAt: hasConsensus ? new Date() : null,
-            confirmedBy: hasConsensus ? session.user.id : null,
+            confirmedBy: hasConsensus ? Number(session.user.id) : null,
             updatedAt: new Date()
           })
           .where(eq(evaluationTargets.id, input.id))
@@ -649,20 +699,27 @@ export async function getDepartmentInfo() {
 }
 
 
-export async function confirmEvaluationTargets(targetIds: number[]) {
+export async function confirmEvaluationTargets(
+  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 ="연간"
+    
     // 트랜잭션으로 처리
-    await db.transaction(async (tx) => {
+    const result = await db.transaction(async (tx) => {
       // 확정 가능한 대상들 확인 (PENDING 상태이면서 consensusStatus가 true인 것들)
       const eligibleTargets = await tx
         .select()
@@ -674,13 +731,14 @@ export async function confirmEvaluationTargets(targetIds: number[]) {
             eq(evaluationTargets.consensusStatus, true)
           )
         )
-
+      
       if (eligibleTargets.length === 0) {
         throw new Error("확정 가능한 평가 대상이 없습니다. (의견 일치 상태인 대기중 항목만 확정 가능)")
       }
-
-      // 상태를 CONFIRMED로 변경
+      
       const confirmedTargetIds = eligibleTargets.map(target => target.id)
+      
+      // 1. 평가 대상 상태를 CONFIRMED로 변경
       await tx
         .update(evaluationTargets)
         .set({
@@ -690,26 +748,201 @@ export async function confirmEvaluationTargets(targetIds: number[]) {
           updatedAt: new Date()
         })
         .where(inArray(evaluationTargets.id, confirmedTargetIds))
+      
+      // 2. 각 확정된 평가 대상에 대해 periodicEvaluations 레코드 생성
+      const periodicEvaluationsToCreate = []
+      
+      for (const target of eligibleTargets) {
+        // 이미 해당 기간에 평가가 존재하는지 확인
+        const existingEvaluation = await tx
+          .select({ id: periodicEvaluations.id })
+          .from(periodicEvaluations)
+          .where(
+            and(
+              eq(periodicEvaluations.evaluationTargetId, target.id),
+              // eq(periodicEvaluations.evaluationPeriod, currentPeriod)
+            )
+          )
+          .limit(1)
+        
+        // 없으면 생성 목록에 추가
+        if (existingEvaluation.length === 0) {
+          periodicEvaluationsToCreate.push({
+            evaluationTargetId: target.id,
+            evaluationPeriod: currentPeriod,
+            // 평가년도에 따른 제출 마감일 설정 (예: 상반기는 7월 말, 하반기는 1월 말)
+            submissionDeadline: getSubmissionDeadline(target.evaluationYear, currentPeriod),
+            status: "PENDING_SUBMISSION" as const,
+            createdAt: new Date(),
+            updatedAt: new Date()
+          })
+        }
+      }
+      
+      // 3. periodicEvaluations 레코드들 일괄 생성
+      let createdEvaluationsCount = 0
+      if (periodicEvaluationsToCreate.length > 0) {
+        const createdEvaluations = await tx
+          .insert(periodicEvaluations)
+          .values(periodicEvaluationsToCreate)
+          .returning({ id: periodicEvaluations.id })
+        
+        createdEvaluationsCount = createdEvaluations.length
+      }
+      
+      // 4. 평가 항목 수 조회 (evaluationSubmissions 생성을 위해)
+      const [generalItemsCount, esgItemsCount] = await Promise.all([
+        // 활성화된 일반평가 항목 수
+        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
 
-      return confirmedTargetIds
+      // 5. 각 periodicEvaluation에 대해 담당자별 reviewerEvaluations도 생성
+      if (periodicEvaluationsToCreate.length > 0) {
+        // 새로 생성된 periodicEvaluations 조회
+        const newPeriodicEvaluations = await tx
+          .select({
+            id: periodicEvaluations.id,
+            evaluationTargetId: periodicEvaluations.evaluationTargetId
+          })
+          .from(periodicEvaluations)
+          .where(
+            and(
+              inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds),
+              eq(periodicEvaluations.evaluationPeriod, currentPeriod)
+            )
+          )
+        
+        // 각 평가에 대해 담당자별 reviewerEvaluations 생성
+        for (const periodicEval of newPeriodicEvaluations) {
+          // 해당 evaluationTarget의 담당자들 조회
+          const reviewers = await tx
+            .select()
+            .from(evaluationTargetReviewers)
+            .where(eq(evaluationTargetReviewers.evaluationTargetId, periodicEval.evaluationTargetId))
+          
+          if (reviewers.length > 0) {
+            const reviewerEvaluationsToCreate = reviewers.map(reviewer => ({
+              periodicEvaluationId: periodicEval.id,
+              evaluationTargetReviewerId: reviewer.id,
+              isCompleted: false,
+              createdAt: new Date(),
+              updatedAt: new Date()
+            }))
+            
+            await tx
+              .insert(reviewerEvaluations)
+              .values(reviewerEvaluationsToCreate)
+          }
+        }
+      }
+      
+      // 6. 벤더별 evaluationSubmissions 레코드 생성
+      const evaluationSubmissionsToCreate = []
+      
+      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) {
+          evaluationSubmissionsToCreate.push({
+            companyId: target.vendorId,
+            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,
+        totalConfirmed: confirmedTargetIds.length
+      }
     })
-
-
-    return { 
-      success: true, 
-      message: `${targetIds.length}개 평가 대상이 확정되었습니다.`,
-      confirmedCount: targetIds.length
+    
+    return {
+      success: true,
+      message: `${result.totalConfirmed}개 평가 대상이 확정되었습니다. ${result.createdEvaluationsCount}개의 정기평가와 ${result.createdSubmissionsCount}개의 제출 요청이 생성되었습니다.`,
+      confirmedCount: result.totalConfirmed,
+      createdEvaluationsCount: result.createdEvaluationsCount,
+      createdSubmissionsCount: result.createdSubmissionsCount
     }
-
+    
   } catch (error) {
     console.error("Error confirming evaluation targets:", error)
-    return { 
-      success: false, 
-      error: error instanceof Error ? error.message : "확정 처리 중 오류가 발생했습니다." 
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "확정 처리 중 오류가 발생했습니다."
     }
   }
 }
 
+
+// 현재 날짜 기준으로 평가 기간 결정하는 헬퍼 함수
+function getCurrentEvaluationPeriod(): string {
+  const now = new Date()
+  const month = now.getMonth() + 1 // 0-based이므로 +1
+  
+  // 1~6월: 상반기, 7~12월: 하반기
+  return month <= 6 ? "상반기" : "하반기"
+}
+
+// 평가년도와 기간에 따른 제출 마감일 설정하는 헬퍼 함수
+function getSubmissionDeadline(evaluationYear: number, period: string): Date {
+  const year = evaluationYear
+  
+  if (period === "상반기") {
+    // 상반기 평가는 다음 해 6월 말까지
+    return new Date(year, 5, 31) // 7월은 6 (0-based)
+  } else if (period === "하반기") {
+    // 하반기 평가는 다음 올해 12월 말까지
+    return new Date(year, 11, 31) // 1월은 0 (0-based)
+  } else {
+    // 연간 평가는 올해 6월 말까지
+    return new Date(year, 5, 31) // 3월은 2 (0-based)
+  }
+}
+
 export async function excludeEvaluationTargets(targetIds: number[]) {
   try {
     const session = await getServerSession(authOptions)
@@ -769,7 +1002,8 @@ export async function excludeEvaluationTargets(targetIds: number[]) {
 
 export async function requestEvaluationReview(targetIds: number[], message?: string) {
   try {
-    const session = await auth()
+    const session = await getServerSession(authOptions)
+
     if (!session?.user) {
       return { success: false, error: "인증이 필요합니다." }
     }