diff options
Diffstat (limited to 'db/schema/evaluationTarget.ts')
| -rw-r--r-- | db/schema/evaluationTarget.ts | 319 |
1 files changed, 319 insertions, 0 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts index 343efc4e..c8011e96 100644 --- a/db/schema/evaluationTarget.ts +++ b/db/schema/evaluationTarget.ts @@ -844,6 +844,25 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q LIMIT 1 )`.as('cs_eval_status'), + // admin 평가 담당 상태 + adminEvalStatus: sql<string>`( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM ${reviewerEvaluationDetails} red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM ${reviewerEvaluations} re + JOIN ${evaluationTargetReviewers} etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} + AND etr.department_code = 'admin' + LIMIT 1 + )`.as('admin_eval_status'), + // ═══════════════════════════════════════════════════════════════ // 리뷰어 통계 (서브쿼리로 계산) // ═══════════════════════════════════════════════════════════════ @@ -1072,3 +1091,303 @@ export interface DepartmentEvaluationStatus { export type EvaluationProgress = 'NOT_STARTED' | 'IN_PROGRESS' | 'COMPLETED'; export type PeriodicEvaluationStatus = 'PENDING_SUBMISSION' | 'SUBMITTED' | 'IN_REVIEW' | 'REVIEW_COMPLETED' | 'FINALIZED'; + + +/** + * 배열에서 첫 번째 값을 뽑아내는 헬퍼. 정렬 기준을 주면 해당 기준으로 정렬 후 첫 번째 값을 가져온다. + */ +const first = <T>(expr: SQL<T>, order?: SQL) => + order + ? sql<T>`(ARRAY_AGG(${expr} ORDER BY ${order} NULLS LAST))[1]` + : sql<T>`(ARRAY_AGG(${expr}))[1]`; + +/** + * BOOL_AND / BOOL_OR 사용이 애매한 경우를 대비한 캐스팅 헬퍼 (선택적으로 사용) + * ex) boolMax(expr) → (MAX((expr)::int))::bool + */ +const boolMax = (expr: SQL<boolean>) => sql<boolean>`(MAX((${expr})::int))::bool`; + +export const periodicEvaluationsAggregatedView = pgView( + "periodic_evaluations_aggregated_view" +).as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // 집계된 식별자 및 기본 정보 + // ═══════════════════════════════════════════════════════════════ + id: sql<string>`CONCAT(${periodicEvaluationsView.evaluationYear}, '_', ${periodicEvaluationsView.vendorId})`.as( + "id" + ), + + // 그룹핑 기준 필드들 + evaluationYear: periodicEvaluationsView.evaluationYear, + vendorId: periodicEvaluationsView.vendorId, + vendorCode: periodicEvaluationsView.vendorCode, + vendorName: periodicEvaluationsView.vendorName, + domesticForeign: periodicEvaluationsView.domesticForeign, + materialType: periodicEvaluationsView.materialType, + + // ═══════════════════════════════════════════════════════════════ + // 평가 점수들의 평균 계산 + // ═══════════════════════════════════════════════════════════════ + processScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)), 1)`.as( + "process_score" + ), + priceScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)), 1)`.as( + "price_score" + ), + deliveryScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)), 1)`.as( + "delivery_score" + ), + selfEvaluationScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)), 1)`.as( + "self_evaluation_score" + ), + participationBonus: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)), 1)`.as( + "participation_bonus" + ), + qualityDeduction: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)), 1)`.as( + "quality_deduction" + ), + + // 최종 점수의 평균 + finalScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)), 1)`.as( + "final_score" + ), + + // ═══════════════════════════════════════════════════════════════ + // 평가 등급 (평균 점수를 기반으로 재계산) + // ═══════════════════════════════════════════════════════════════ + evaluationGrade: sql<string>` + CASE + WHEN ( + AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - + AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) + ) >= 90 THEN 'S' + WHEN ( + AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - + AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) + ) >= 80 THEN 'A' + WHEN ( + AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - + AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) + ) >= 70 THEN 'B' + WHEN ( + AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - + AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) + ) >= 60 THEN 'C' + ELSE 'D' + END + `.as("evaluation_grade"), + + finalGrade: sql<string>` + CASE + WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 90 THEN 'S' + WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 80 THEN 'A' + WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 70 THEN 'B' + WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 60 THEN 'C' + ELSE 'D' + END + `.as("final_grade"), + + // ═══════════════════════════════════════════════════════════════ + // 상태 정보 (우선순위 기반으로 결정) + // ═══════════════════════════════════════════════════════════════ + status: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} = 'FINALIZED' THEN 1 END) = COUNT(*) THEN 'FINALIZED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) = COUNT(*) THEN 'REVIEW_COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'IN_REVIEW' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('SUBMITTED', 'IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'SUBMITTED' + ELSE 'PENDING_SUBMISSION' + END + `.as("status"), + + // ═══════════════════════════════════════════════════════════════ + // 부서별 평가 상태 (최고 진행 상태로 설정) + // ═══════════════════════════════════════════════════════════════ + orderEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("order_eval_status"), + + procurementEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("procurement_eval_status"), + + qualityEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("quality_eval_status"), + + designEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("design_eval_status"), + + csEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("cs_eval_status"), + + adminEvalStatus: sql<string>` + CASE + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' + WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' + ELSE 'NOT_ASSIGNED' + END + `.as("admin_eval_status"), + + // ═══════════════════════════════════════════════════════════════ + // 문서 제출 및 날짜 정보 + // ═══════════════════════════════════════════════════════════════ + documentsSubmitted: sql<boolean>` + BOOL_AND(${periodicEvaluationsView.documentsSubmitted}) + `.as("documents_submitted"), + + submissionDate: sql<Date>`MAX(${periodicEvaluationsView.submissionDate})`.as( + "submission_date" + ), + submissionDeadline: sql<Date>`MAX(${periodicEvaluationsView.submissionDeadline})`.as( + "submission_deadline" + ), + reviewCompletedAt: sql<Date>`MAX(${periodicEvaluationsView.reviewCompletedAt})`.as( + "review_completed_at" + ), + finalizedAt: sql<Date>`MAX(${periodicEvaluationsView.finalizedAt})`.as( + "finalized_at" + ), + + // ═══════════════════════════════════════════════════════════════ + // Division 정보 및 집계 메타데이터 + // ═══════════════════════════════════════════════════════════════ + division: sql<string>` + CASE + WHEN COUNT(DISTINCT ${periodicEvaluationsView.division}) > 1 THEN 'BOTH' + ELSE MAX(${periodicEvaluationsView.division}) + END + `.as("division"), + + evaluationCount: sql<number>`COUNT(*)::int`.as("evaluation_count"), + divisions: sql<string>`STRING_AGG(DISTINCT ${periodicEvaluationsView.division}, ',')`.as( + "divisions" + ), + + // ═══════════════════════════════════════════════════════════════ + // 리뷰어 통계 (합계) + // ═══════════════════════════════════════════════════════════════ + totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})::int`.as( + "total_reviewers" + ), + completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})::int`.as( + "completed_reviewers" + ), + pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})::int`.as( + "pending_reviewers" + ), + + // ═══════════════════════════════════════════════════════════════ + // 기타 정보 + // ═══════════════════════════════════════════════════════════════ + evaluationPeriod: sql<string>`MAX(${periodicEvaluationsView.evaluationPeriod})`.as( + "evaluation_period" + ), + evaluationNote: sql<string>`STRING_AGG(${periodicEvaluationsView.evaluationNote}, ' | ')`.as( + "evaluation_note" + ), + + // 최종 확정자 정보 (가장 최근 확정자) + finalizedBy: first<string>( + sql`${periodicEvaluationsView.finalizedBy}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by"), + finalizedByUserName: first<string>( + sql`${periodicEvaluationsView.finalizedByUserName}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by_user_name"), + finalizedByUserEmail: first<string>( + sql`${periodicEvaluationsView.finalizedByUserEmail}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by_user_email"), + + // 생성/수정 일시 + createdAt: sql<Date>`MIN(${periodicEvaluationsView.createdAt})`.as( + "created_at" + ), + updatedAt: sql<Date>`MAX(${periodicEvaluationsView.updatedAt})`.as( + "updated_at" + ), + + // 추가 평가 대상 정보들 (첫 번째 레코드 사용) + evaluationTargetId: first<string>( + sql`${periodicEvaluationsView.evaluationTargetId}` + ).as("evaluation_target_id"), + + evaluationTargetAdminComment: sql<string>` + STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetAdminComment}, ' | ') + `.as("evaluation_target_admin_comment"), + + evaluationTargetConsolidatedComment: sql<string>` + STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetConsolidatedComment}, ' | ') + `.as("evaluation_target_consolidated_comment"), + + // Boolean / Enum 필드라면 first()로 첫 번째 값만 가져오도록 변경 + evaluationTargetConsensusStatus: first<string>( + sql`${periodicEvaluationsView.evaluationTargetConsensusStatus}`, + sql`${periodicEvaluationsView.updatedAt} DESC` + ).as("evaluation_target_consensus_status"), + + evaluationTargetConfirmedAt: sql<Date>` + MAX(${periodicEvaluationsView.evaluationTargetConfirmedAt}) + `.as("evaluation_target_confirmed_at"), + }) + .from(periodicEvaluationsView) + .groupBy( + periodicEvaluationsView.evaluationYear, + periodicEvaluationsView.vendorId, + periodicEvaluationsView.vendorCode, + periodicEvaluationsView.vendorName, + periodicEvaluationsView.domesticForeign, + periodicEvaluationsView.materialType + ) +); + +// 타입 정의 +export type PeriodicEvaluationAggregatedView = typeof periodicEvaluationsAggregatedView.$inferSelect;
\ No newline at end of file |