summaryrefslogtreecommitdiff
path: root/db/schema/evaluationTarget.ts
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema/evaluationTarget.ts')
-rw-r--r--db/schema/evaluationTarget.ts120
1 files changed, 6 insertions, 114 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index 1bb9ae02..343efc4e 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -652,7 +652,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 공정 점수 (processScore)
processScore: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -664,7 +664,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 가격 점수 (priceScore)
priceScore: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -676,7 +676,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 납기 점수 (deliveryScore)
deliveryScore: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -688,7 +688,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 자율평가 점수 (selfEvaluationScore)
selfEvaluationScore: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -700,7 +700,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 참여도 가점 (bonus)
participationBonus: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -712,7 +712,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// 품질 감점 (penalty)
qualityDeduction: sql<number>`(
- SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+ SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
FROM ${reviewerEvaluationDetails} red
JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
@@ -722,113 +722,6 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
AND re.is_completed = true
)`.as('quality_deduction'),
- // 합계 점수 (4개 기본 점수의 합)
- totalScore: sql<number>`(
- SELECT COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
- )
- )`.as('total_score'),
-
- // 최종 평가 점수 (합계 + 가점 - 감점)
- evaluationScore: sql<number>`(
- SELECT (
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
- ) -
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
- )
- )
- )`.as('evaluation_score'),
-
- // 평가 등급 (evaluationScore 기반으로 계산)
- evaluationGrade: sql<string>`(
- CASE
- WHEN (
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
- ) -
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
- )
- ) >= 95 THEN 'A'
- WHEN (
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
- ) -
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
- )
- ) >= 90 THEN 'B'
- WHEN (
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
- ) +
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
- ) -
- COALESCE(
- (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
- )
- ) >= 60 THEN 'C'
- ELSE 'D'
- END
- )`.as('evaluation_grade'),
-
// 평가 상태
status: periodicEvaluations.status,
@@ -852,7 +745,6 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
evaluationTargetConsensusStatus: evaluationTargets.consensusStatus,
evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
-
// ═══════════════════════════════════════════════════════════════
// 부서별 리뷰어 상태 (서브쿼리로 계산)
// ═══════════════════════════════════════════════════════════════
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 11:41:03 +0000