(대표님) 정기평가 피드백 반영, 설계 피드백 반영, (최겸) 기술영업 피드백 반영

1 files changed, 179 insertions, 35 deletions

diff --git a/lib/evaluation/service.ts b/lib/evaluation/service.ts
index c49521da..9889a110 100644
--- a/lib/evaluation/service.ts
+++ b/lib/evaluation/service.ts
@@ -6,6 +6,7 @@ import {
   evaluationTargetReviewers,
   evaluationTargets,
   periodicEvaluations,
+  periodicEvaluationsAggregatedView,
   periodicEvaluationsView,
   regEvalCriteria,
   regEvalCriteriaDetails,
@@ -55,15 +56,6 @@ export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema)
       });
     }
 
-    // 2) 기본 필터 조건
-    let basicWhere: SQL<unknown> | undefined = undefined;
-    if (input.basicFilters && input.basicFilters.length > 0) {
-      basicWhere = filterColumns({
-        table: periodicEvaluationsView,
-        filters: input.basicFilters,
-        joinOperator: input.basicJoinOperator || 'and',
-      });
-    }
 
     // 3) 글로벌 검색 조건
     let globalWhere: SQL<unknown> | undefined = undefined;
@@ -102,7 +94,6 @@ export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema)
     const whereConditions: SQL<unknown>[] = [];
 
     if (advancedWhere) whereConditions.push(advancedWhere);
-    if (basicWhere) whereConditions.push(basicWhere);
     if (globalWhere) whereConditions.push(globalWhere);
 
     const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
@@ -157,7 +148,7 @@ export interface PeriodicEvaluationsStats {
   inReview: number
   reviewCompleted: number
   finalized: number
-  averageScore: number | null
+  // averageScore: number | null
   completionRate: number
   averageFinalScore: number | null
   documentsSubmittedCount: number
@@ -179,7 +170,7 @@ export async function getPeriodicEvaluationsStats(evaluationYear: number): Promi
     const totalStatsResult = await db
       .select({
         total: count(),
-        averageScore: avg(periodicEvaluationsView.totalScore),
+        // averageScore: avg(periodicEvaluationsView.totalScore),
         averageFinalScore: avg(periodicEvaluationsView.finalScore),
       })
       .from(periodicEvaluationsView)
@@ -187,7 +178,7 @@ export async function getPeriodicEvaluationsStats(evaluationYear: number): Promi
 
     const totalStats = totalStatsResult[0] || {
       total: 0,
-      averageScore: null,
+      // averageScore: null,
       averageFinalScore: null
     }
 
@@ -265,7 +256,7 @@ export async function getPeriodicEvaluationsStats(evaluationYear: number): Promi
       inReview: statusCounts['IN_REVIEW'] || 0,
       reviewCompleted: statusCounts['REVIEW_COMPLETED'] || 0,
       finalized: finalizedCount,
-      averageScore: formatScore(totalStats.averageScore),
+      // averageScore: formatScore(totalStats.averageScore),
       averageFinalScore: formatScore(totalStats.averageFinalScore),
       completionRate,
       documentsSubmittedCount: documentCounts.submitted,
@@ -288,7 +279,7 @@ export async function getPeriodicEvaluationsStats(evaluationYear: number): Promi
       inReview: 0,
       reviewCompleted: 0,
       finalized: 0,
-      averageScore: null,
+      // averageScore: null,
       averageFinalScore: null,
       completionRate: 0,
       documentsSubmittedCount: 0,
@@ -569,6 +560,7 @@ export async function getReviewersForEvaluations(
       )
       .orderBy(evaluationTargetReviewers.evaluationTargetId, users.name)
 
+
     // 2. 추가: role name에 "정기평가"가 포함된 사용자들
     const roleBasedReviewers = await db
       .select({
@@ -605,28 +597,11 @@ export async function getReviewersForEvaluations(
       }
     }
 
-    // 4. 중복 제거 (같은 사용자가 designated reviewer와 role-based reviewer 모두에 있을 수 있음)
+    // 4. 모든 리뷰어 합치기 (중복 제거 없이)
     const allReviewers = [...designatedReviewers, ...expandedRoleBasedReviewers]
 
-    // evaluationTargetId + userId 조합으로 중복 제거
-    const uniqueReviewers = allReviewers.reduce((acc, reviewer) => {
-      const key = `${reviewer.evaluationTargetId}-${reviewer.id}`
-
-      // 이미 있는 경우 designated reviewer를 우선 (evaluationTargetReviewerId가 양수인 것)
-      if (acc[key]) {
-        if (reviewer.evaluationTargetReviewerId > 0) {
-          acc[key] = reviewer // designated reviewer로 교체
-        }
-        // 이미 designated reviewer가 있으면 role-based는 무시
-      } else {
-        acc[key] = reviewer
-      }
-
-      return acc
-    }, {} as Record<string, ReviewerInfo>)
-
-    return Object.values(uniqueReviewers).sort((a, b) => {
-      // evaluationTargetId로 먼저 정렬, 그 다음 이름으로 정렬
+    // 정렬만 수행 (evaluationTargetId로 먼저 정렬, 그 다음 이름으로 정렬)
+    return allReviewers.sort((a, b) => {
       if (a.evaluationTargetId !== b.evaluationTargetId) {
         return a.evaluationTargetId - b.evaluationTargetId
       }
@@ -685,6 +660,8 @@ export async function createReviewerEvaluationsRequest(
         )
       )
 
+      console.log(newRequestData,"newRequestData")
+
       if (newRequestData.length === 0) {
         throw new Error("모든 평가 요청이 이미 생성되어 있습니다.")
       }
@@ -1320,4 +1297,171 @@ export async function getEvaluationDetails(periodicEvaluationId: number): Promis
         : "평가 상세 정보 조회 중 오류가 발생했습니다"
     )
   }
+}
+
+
+export async function getPeriodicEvaluationsAggregated(input: GetEvaluationTargetsSchema) {
+  try {
+    const offset = (input.page - 1) * input.perPage;
+    
+    // 1) 고급 필터 조건 (기존과 동일)
+    let advancedWhere: SQL<unknown> | undefined = undefined;
+    if (input.filters && input.filters.length > 0) {
+      advancedWhere = filterColumns({
+        table: periodicEvaluationsAggregatedView,
+        filters: input.filters,
+        joinOperator: input.joinOperator || 'and',
+      });
+    }
+    
+    // 2) 글로벌 검색 조건 (집계 뷰에 맞게 조정)
+    let globalWhere: SQL<unknown> | undefined = undefined;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      
+      const validSearchConditions: SQL<unknown>[] = [];
+      
+      // 벤더 정보로 검색
+      const vendorCodeCondition = ilike(periodicEvaluationsAggregatedView.vendorCode, s);
+      if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);
+      
+      const vendorNameCondition = ilike(periodicEvaluationsAggregatedView.vendorName, s);
+      if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);
+      
+      // 평가 관련 코멘트로 검색
+      const evaluationNoteCondition = ilike(periodicEvaluationsAggregatedView.evaluationNote, s);
+      if (evaluationNoteCondition) validSearchConditions.push(evaluationNoteCondition);
+      
+      const adminCommentCondition = ilike(periodicEvaluationsAggregatedView.evaluationTargetAdminComment, s);
+      if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);
+      
+      const consolidatedCommentCondition = ilike(periodicEvaluationsAggregatedView.evaluationTargetConsolidatedComment, s);
+      if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);
+      
+      // 최종 확정자 이름으로 검색
+      const finalizedByUserNameCondition = ilike(periodicEvaluationsAggregatedView.finalizedByUserName, s);
+      if (finalizedByUserNameCondition) validSearchConditions.push(finalizedByUserNameCondition);
+      
+      if (validSearchConditions.length > 0) {
+        globalWhere = or(...validSearchConditions);
+      }
+    }
+    
+    // 3) WHERE 조건 생성
+    const whereConditions: SQL<unknown>[] = [];
+    
+    if (advancedWhere) whereConditions.push(advancedWhere);
+    if (globalWhere) whereConditions.push(globalWhere);
+    
+    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+    
+    // 4) 전체 데이터 수 조회
+    const totalResult = await db
+      .select({ count: count() })
+      .from(periodicEvaluationsAggregatedView)
+      .where(finalWhere);
+    
+    const total = totalResult[0]?.count || 0;
+    
+    if (total === 0) {
+      return { data: [], pageCount: 0, total: 0 };
+    }
+    
+    console.log("Total aggregated periodic evaluations:", total);
+    
+    // 5) 정렬 및 페이징 처리된 데이터 조회
+    const orderByColumns = input.sort.map((sort) => {
+      const column = sort.id as keyof typeof periodicEvaluationsAggregatedView.$inferSelect;
+      return sort.desc ? desc(periodicEvaluationsAggregatedView[column]) : asc(periodicEvaluationsAggregatedView[column]);
+    });
+    
+    if (orderByColumns.length === 0) {
+      orderByColumns.push(desc(periodicEvaluationsAggregatedView.createdAt));
+    }
+    
+    const periodicEvaluationsData = await db
+      .select()
+      .from(periodicEvaluationsAggregatedView)
+      .where(finalWhere)
+      .orderBy(...orderByColumns)
+      .limit(input.perPage)
+      .offset(offset);
+    
+    const pageCount = Math.ceil(total / input.perPage);
+    
+    return { data: periodicEvaluationsData, pageCount, total };
+    
+  } catch (err) {
+    console.error("Error in getPeriodicEvaluationsAggregated:", err);
+    return { data: [], pageCount: 0, total: 0 };
+  }
+}
+
+// 기존 함수에 집계 옵션을 추가한 통합 함수
+export async function getPeriodicEvaluationsWithAggregation(input: GetEvaluationTargetsSchema) {
+  if (input.aggregated) {
+    return getPeriodicEvaluationsAggregated(input);
+  } else {
+    return getPeriodicEvaluations(input);
+  }
+}
+
+// 집계된 주기평가 통계 함수
+export async function getPeriodicEvaluationsAggregatedStats(evaluationYear: number) {
+  try {
+    const statsQuery = await db
+      .select({
+        total: count(),
+        pendingSubmission: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'PENDING_SUBMISSION' THEN 1 END)::int`,
+        submitted: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'SUBMITTED' THEN 1 END)::int`,
+        inReview: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'IN_REVIEW' THEN 1 END)::int`,
+        reviewCompleted: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'REVIEW_COMPLETED' THEN 1 END)::int`,
+        finalized: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'FINALIZED' THEN 1 END)::int`,
+        averageScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsAggregatedView.finalScore}, 0)), 1)`,
+        totalEvaluationCount: sql<number>`SUM(${periodicEvaluationsAggregatedView.evaluationCount})::int`,
+      })
+      .from(periodicEvaluationsAggregatedView)
+      .where(eq(periodicEvaluationsAggregatedView.evaluationYear, evaluationYear));
+
+    const stats = statsQuery[0];
+
+    if (!stats) {
+      return {
+        total: 0,
+        pendingSubmission: 0,
+        submitted: 0,
+        inReview: 0,
+        reviewCompleted: 0,
+        finalized: 0,
+        completionRate: 0,
+        averageScore: 0,
+        totalEvaluationCount: 0,
+      };
+    }
+
+    const completionRate = stats.total > 0 
+      ? Math.round((stats.finalized / stats.total) * 100) 
+      : 0;
+
+    return {
+      ...stats,
+      completionRate,
+    };
+
+  } catch (error) {
+    console.error('Error fetching aggregated periodic evaluations stats:', error);
+    throw error;
+  }
+}
+
+// 집계 모드에 따른 통계 조회 함수
+export async function getPeriodicEvaluationsStatsWithMode(
+  evaluationYear: number, 
+  aggregated: boolean = false
+) {
+  if (aggregated) {
+    return getPeriodicEvaluationsAggregatedStats(evaluationYear);
+  } else {
+    return getPeriodicEvaluationsStats(evaluationYear);
+  }
 }
\ No newline at end of file