diff options
Diffstat (limited to 'lib/evaluation/service.ts')
| -rw-r--r-- | lib/evaluation/service.ts | 214 |
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 |