(대표님) 벤더데이터 S-EDP 변경사항 대응(seperator), 정기평가 점수오류, dim 준비

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,
 
-
       // ═══════════════════════════════════════════════════════════════
       // 부서별 리뷰어 상태 (서브쿼리로 계산)
       // ═══════════════════════════════════════════════════════════════