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.ts550
1 files changed, 497 insertions, 53 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index 66f3dc04..54f2ba16 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -3,7 +3,7 @@ import { eq , sql, relations} from "drizzle-orm";
import { vendors } from "./vendors";
import { users } from "./users";
import { contracts } from "./contract";
-import { regEvalCriteriaDetails } from "./evaluationCriteria";
+import { regEvalCriteria, regEvalCriteriaDetails } from "./evaluationCriteria";
// 평가 대상 메인 테이블
export const evaluationTargets = pgTable("evaluation_targets", {
@@ -416,19 +416,7 @@ export const periodicEvaluations = pgTable("periodic_evaluations", {
enum: ["S", "A", "B", "C", "D"]
}),
- // 평가항목별 점수
- processScore: decimal("process_score", { precision: 5, scale: 2 }).default("0"), // 공정
- priceScore: decimal("price_score", { precision: 5, scale: 2 }).default("0"), // 가격
- deliveryScore: decimal("delivery_score", { precision: 5, scale: 2 }).default("0"), // 납기
- selfEvaluationScore: decimal("self_evaluation_score", { precision: 5, scale: 2 }).default("0"), // 자율평가
-
- // 합계 점수
- totalScore: decimal("total_score", { precision: 5, scale: 2 }).default("0"),
-
- // 가점/감점
- participationBonus: decimal("participation_bonus", { precision: 5, scale: 2 }).default("0"), // 참여도 가점
- qualityDeduction: decimal("quality_deduction", { precision: 5, scale: 2 }).default("0"), // 품질 감점
-
+
// 평가 상태
status: varchar("status", {
length: 30,
@@ -465,27 +453,11 @@ export const reviewerEvaluations = pgTable("reviewer_evaluations", {
evaluationTargetReviewerId: integer("evaluation_target_reviewer_id")
.references(() => evaluationTargetReviewers.id, { onDelete: "cascade" })
.notNull(),
-
- // 평가항목별 점수 (카테고리별 합산 점수)
- processScore: decimal("process_score", { precision: 5, scale: 2 }),
- priceScore: decimal("price_score", { precision: 5, scale: 2 }),
- deliveryScore: decimal("delivery_score", { precision: 5, scale: 2 }),
- selfEvaluationScore: decimal("self_evaluation_score", { precision: 5, scale: 2 }),
-
- // 가점/감점
- participationBonus: decimal("participation_bonus", { precision: 5, scale: 2 }).default("0"),
- qualityDeduction: decimal("quality_deduction", { precision: 5, scale: 2 }).default("0"),
-
- // 리뷰어 총점
- totalScore: decimal("total_score", { precision: 5, scale: 2 }),
- grade: varchar("grade", {
- length: 5,
- enum: ["S", "A", "B", "C", "D"]
- }),
-
+
// 평가 완료 여부
isCompleted: boolean("is_completed").default(false),
completedAt: timestamp("completed_at"),
+ submittedAt: timestamp("submitted_at"),
// 리뷰어 의견
reviewerComment: text("reviewer_comment"),
@@ -510,10 +482,10 @@ export const reviewerEvaluationDetails = pgTable("reviewer_evaluation_details",
// 평가 기준 참조
regEvalCriteriaDetailsId: integer("reg_eval_criteria_details_id")
.references(() => regEvalCriteriaDetails.id)
- .notNull(),
+ ,
// 리뷰어가 매긴 점수
- score: decimal("score", { precision: 5, scale: 2 }).notNull(),
+ score: decimal("score", { precision: 5, scale: 2 }),
// 세부 의견
comment: text("comment"),
@@ -584,11 +556,11 @@ export const reviewerEvaluationDetailsRelations = relations(reviewerEvaluationDe
type PeriodicEvaluation = typeof periodicEvaluations.$inferSelect;
type NewPeriodicEvaluation = typeof periodicEvaluations.$inferInsert;
-type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect;
+export type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect;
type NewReviewerEvaluation = typeof reviewerEvaluations.$inferInsert;
-type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect;
-type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert;
+export type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect;
+export type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert;
// 관계 포함 타입들
type PeriodicEvaluationWithRelations = PeriodicEvaluation & {
@@ -634,26 +606,212 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
submissionDate: periodicEvaluations.submissionDate,
submissionDeadline: periodicEvaluations.submissionDeadline,
- // 평가 점수 (최종 확정)
+ // 평가 점수 (최종 확정 - 이건 수동으로 설정하는 값이므로 유지)
finalScore: periodicEvaluations.finalScore,
finalGrade: periodicEvaluations.finalGrade,
- // 평가 점수
- evaluationScore: periodicEvaluations.evaluationScore,
- evaluationGrade: periodicEvaluations.evaluationGrade,
+ // ═══════════════════════════════════════════════════════════════
+ // 동적으로 계산되는 평가 점수들
+ // ═══════════════════════════════════════════════════════════════
+
+ // 공정 점수 (processScore)
+ processScore: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('process_score'),
+
+ // 가격 점수 (priceScore)
+ priceScore: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('price_score'),
+
+ // 납기 점수 (deliveryScore)
+ deliveryScore: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('delivery_score'),
+
+ // 자율평가 점수 (selfEvaluationScore)
+ selfEvaluationScore: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('self_evaluation_score'),
+
+ // 참여도 가점 (bonus)
+ participationBonus: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('participation_bonus'),
+
+ // 품질 감점 (penalty)
+ qualityDeduction: sql<number>`(
+ SELECT COALESCE(AVG(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
+ 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
+ )`.as('quality_deduction'),
- // 평가항목별 점수
- processScore: periodicEvaluations.processScore,
- priceScore: periodicEvaluations.priceScore,
- deliveryScore: periodicEvaluations.deliveryScore,
- selfEvaluationScore: periodicEvaluations.selfEvaluationScore,
+ // 합계 점수 (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'),
- // 합계 점수
- totalScore: periodicEvaluations.totalScore,
+ // 최종 평가 점수 (합계 + 가점 - 감점)
+ 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'),
- // 가점/감점
- participationBonus: periodicEvaluations.participationBonus,
- qualityDeduction: periodicEvaluations.qualityDeduction,
+ // 평가 등급 (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
+ )
+ ) >= 90 THEN 'S'
+ 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
+ )
+ ) >= 80 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
+ )
+ ) >= 70 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,
@@ -673,11 +831,110 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
// ═══════════════════════════════════════════════════════════════
// 평가 대상 추가 정보 (evaluationTargets 조인)
// ═══════════════════════════════════════════════════════════════
- evaluationTargetStatus: evaluationTargets.status,
evaluationTargetAdminComment: evaluationTargets.adminComment,
evaluationTargetConsolidatedComment: evaluationTargets.consolidatedComment,
evaluationTargetConsensusStatus: evaluationTargets.consensusStatus,
evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
+
+
+ // ═══════════════════════════════════════════════════════════════
+ // 부서별 리뷰어 상태 (서브쿼리로 계산)
+ // ═══════════════════════════════════════════════════════════════
+
+ // 발주 평가 담당 상태
+ orderEvalStatus: 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 = 'ORDER_EVAL'
+ LIMIT 1
+ )`.as('order_eval_status'),
+
+ // 조달 평가 담당 상태
+ procurementEvalStatus: 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 = 'PROCUREMENT_EVAL'
+ LIMIT 1
+ )`.as('procurement_eval_status'),
+
+ // 품질 평가 담당 상태
+ qualityEvalStatus: 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 = 'QUALITY_EVAL'
+ LIMIT 1
+ )`.as('quality_eval_status'),
+
+ // 설계 평가 담당 상태
+ designEvalStatus: 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 = 'DESIGN_EVAL'
+ LIMIT 1
+ )`.as('design_eval_status'),
+
+ // CS 평가 담당 상태
+ csEvalStatus: 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 = 'CS_EVAL'
+ LIMIT 1
+ )`.as('cs_eval_status'),
// ═══════════════════════════════════════════════════════════════
// 리뷰어 통계 (서브쿼리로 계산)
@@ -714,9 +971,196 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
.orderBy(periodicEvaluations.createdAt)
);
-
// ================================================================
// TYPES
// ================================================================
export type PeriodicEvaluationView = typeof periodicEvaluationsView.$inferSelect;
+
+// reviewerEvaluations를 기준으로 하는 종합 뷰 테이블
+export const reviewerEvaluationsView = pgView('reviewer_evaluations_view').as((qb) =>
+ qb
+ .select({
+ // ═══════════════════════════════════════════════════════════════
+ // 리뷰어 평가 기본 정보 (reviewerEvaluations)
+ // ═══════════════════════════════════════════════════════════════
+ reviewerEvaluationId: reviewerEvaluations.id,
+ periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
+ evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId,
+
+ // 평가 완료 여부
+ isCompleted: reviewerEvaluations.isCompleted,
+ completedAt: reviewerEvaluations.completedAt,
+
+ // 리뷰어 의견
+ reviewerComment: reviewerEvaluations.reviewerComment,
+
+ // 생성/수정일
+ reviewerEvaluationCreatedAt: reviewerEvaluations.createdAt,
+ reviewerEvaluationUpdatedAt: reviewerEvaluations.updatedAt,
+
+ // ═══════════════════════════════════════════════════════════════
+ // 정기평가 정보 (periodicEvaluations)
+ // ═══════════════════════════════════════════════════════════════
+ evaluationPeriod: periodicEvaluations.evaluationPeriod,
+
+ submittedAt:reviewerEvaluations.submittedAt,
+
+ // 업체 제출 관련
+ documentsSubmitted: periodicEvaluations.documentsSubmitted,
+ submissionDate: periodicEvaluations.submissionDate,
+ submissionDeadline: periodicEvaluations.submissionDeadline,
+
+ // 정기평가 최종 점수
+ periodicFinalScore: periodicEvaluations.finalScore,
+ periodicFinalGrade: periodicEvaluations.finalGrade,
+ periodicEvaluationScore: periodicEvaluations.evaluationScore,
+ periodicEvaluationGrade: periodicEvaluations.evaluationGrade,
+
+ // 정기평가 상태
+ periodicStatus: periodicEvaluations.status,
+ reviewCompletedAt: periodicEvaluations.reviewCompletedAt,
+ finalizedAt: periodicEvaluations.finalizedAt,
+ finalizedBy: periodicEvaluations.finalizedBy,
+
+ // 정기평가 비고
+ evaluationNote: periodicEvaluations.evaluationNote,
+
+ // ═══════════════════════════════════════════════════════════════
+ // 평가 대상 기본 정보 (evaluationTargets)
+ // ═══════════════════════════════════════════════════════════════
+ evaluationYear: evaluationTargets.evaluationYear,
+ division: evaluationTargets.division,
+
+ // 벤더 정보
+ vendorId: evaluationTargets.vendorId,
+ vendorCode: evaluationTargets.vendorCode,
+ vendorName: evaluationTargets.vendorName,
+
+ // 분류 정보
+ domesticForeign: evaluationTargets.domesticForeign,
+ materialType: evaluationTargets.materialType,
+ // 평가 대상 의견
+ adminComment: evaluationTargets.adminComment,
+ consolidatedComment: evaluationTargets.consolidatedComment,
+
+ // 평가 대상 확정 정보
+ evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
+ evaluationTargetConfirmedBy: evaluationTargets.confirmedBy,
+
+ // LD 클레임 정보
+ ldClaimCount: evaluationTargets.ldClaimCount,
+ ldClaimAmount: evaluationTargets.ldClaimAmount,
+ ldClaimCurrency: evaluationTargets.ldClaimCurrency,
+
+ // ═══════════════════════════════════════════════════════════════
+ // 리뷰어 정보 (evaluationTargetReviewers + users)
+ // ═══════════════════════════════════════════════════════════════
+ departmentCode: evaluationTargetReviewers.departmentCode,
+ departmentNameFrom: evaluationTargetReviewers.departmentNameFrom,
+
+ // 리뷰어 담당자 정보
+ reviewerUserId: evaluationTargetReviewers.reviewerUserId,
+ reviewerName: sql<string | null>`reviewer_user.name`.as('reviewer_name'),
+ reviewerEmail: sql<string | null>`reviewer_user.email`.as('reviewer_email'),
+
+ // 리뷰어 지정 정보
+ assignedAt: evaluationTargetReviewers.assignedAt,
+ assignedBy: evaluationTargetReviewers.assignedBy,
+ assignedByUserName: sql<string | null>`assigned_by_user.name`.as('assigned_by_user_name'),
+
+ // ═══════════════════════════════════════════════════════════════
+ // 최종 확정자 정보
+ // ═══════════════════════════════════════════════════════════════
+ finalizedByUserName: sql<string | null>`finalized_by_user.name`.as('finalized_by_user_name'),
+ finalizedByUserEmail: sql<string | null>`finalized_by_user.email`.as('finalized_by_user_email'),
+
+ // ═══════════════════════════════════════════════════════════════
+ // 추가 계산 필드
+ // ═══════════════════════════════════════════════════════════════
+
+ // 평가 진행률 (개인별)
+ evaluationProgress: sql<string>`
+ CASE
+ WHEN ${reviewerEvaluations.isCompleted} = true THEN 'COMPLETED'
+ ELSE 'NOT_STARTED'
+ END
+ `.as('evaluation_progress'),
+
+
+ })
+ .from(reviewerEvaluations)
+
+ // 정기평가 정보 조인
+ .leftJoin(
+ periodicEvaluations,
+ eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluations.id)
+ )
+
+ // 평가 대상 정보 조인
+ .leftJoin(
+ evaluationTargets,
+ eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id)
+ )
+
+ // 리뷰어 지정 정보 조인
+ .leftJoin(
+ evaluationTargetReviewers,
+ eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)
+ )
+
+ // 리뷰어 사용자 정보 조인
+ .leftJoin(
+ sql`users reviewer_user`,
+ sql`${evaluationTargetReviewers.reviewerUserId} = reviewer_user.id`
+ )
+
+ // 지정자 사용자 정보 조인
+ .leftJoin(
+ sql`users assigned_by_user`,
+ sql`${evaluationTargetReviewers.assignedBy} = assigned_by_user.id`
+ )
+
+ // 최종 확정자 사용자 정보 조인
+ .leftJoin(
+ sql`users finalized_by_user`,
+ sql`${periodicEvaluations.finalizedBy} = finalized_by_user.id`
+ )
+
+ // 정렬: 최신순, 미완료 우선
+ .orderBy(
+ sql`${reviewerEvaluations.isCompleted} ASC`, // false(미완료)가 먼저
+ sql`${reviewerEvaluations.updatedAt} DESC`
+ )
+);
+
+// ================================================================
+// TYPES
+// ================================================================
+
+export type ReviewerEvaluationView = typeof reviewerEvaluationsView.$inferSelect;
+
+// 리뷰어 평가 뷰 관련 추가 타입들
+export interface ReviewerEvaluationSummary {
+ reviewerId: number;
+ reviewerName: string;
+ departmentCode: string;
+ totalEvaluations: number;
+ completedEvaluations: number;
+ pendingEvaluations: number;
+ overdueEvaluations: number;
+ averageScore: number | null;
+ averageGrade: string | null;
+}
+
+export interface DepartmentEvaluationStatus {
+ departmentCode: string;
+ departmentName: string;
+ totalReviewers: number;
+ completedReviewers: number;
+ completionRate: number;
+ averageScore: number | null;
+}
+
+export type EvaluationProgress = 'NOT_STARTED' | 'IN_PROGRESS' | 'COMPLETED';
+export type PeriodicEvaluationStatus = 'PENDING_SUBMISSION' | 'SUBMITTED' | 'IN_REVIEW' | 'REVIEW_COMPLETED' | 'FINALIZED';
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 10:17:34 +0000