diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/evaluationTarget.ts | 120 | ||||
| -rw-r--r-- | db/schema/vendorData.ts | 13 |
2 files changed, 17 insertions, 116 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, - // ═══════════════════════════════════════════════════════════════ // 부서별 리뷰어 상태 (서브쿼리로 계산) // ═══════════════════════════════════════════════════════════════ diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts index b7c70e72..5e1e65cf 100644 --- a/db/schema/vendorData.ts +++ b/db/schema/vendorData.ts @@ -82,7 +82,9 @@ export const tags = pgTable("tags", { .references(() => forms.id, { onDelete: "set null" }), tagNo: varchar("tag_no", { length: 100 }).notNull(), tagType: varchar("tag_type", { length: 50 }).notNull(), - class: varchar("class", { length: 100 }).notNull(), + class: varchar("class", { length: 100 }).notNull(), // 기존 필드 유지 (호환성) + tagClassId: integer("tag_class_id") + .references(() => tagClasses.id, { onDelete: "set null" }), // 새로운 관계 필드 description: text("description"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), @@ -92,6 +94,7 @@ export const tags = pgTable("tags", { }; }); + // tagTypes에 projectId 추가 및 복합 기본키 생성 export const tagTypes = pgTable("tag_types", { code: varchar("code", { length: 50 }).notNull(), @@ -182,6 +185,10 @@ export const tagClasses = pgTable("tag_classes", { code: varchar("code", { length: 100 }).notNull(), label: text("label").notNull(), tagTypeCode: varchar("tag_type_code", { length: 50 }).notNull(), + // 서브클래스 정보 (ID와 DESC를 포함한 객체 배열) + subclasses: json("subclasses").$type<{id: string, desc: string}[]>().default([]), + // 서브클래스별 리마크 (JSON 객체) + subclassRemark: json("subclass_remark").$type<Record<string, string>>().default({}), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { @@ -197,7 +204,7 @@ export const tagClasses = pgTable("tag_classes", { foreignColumns: [tagTypes.code, tagTypes.projectId] }).onDelete("cascade") }; -}) +}); export const tagClassAttributes = pgTable("tag_class_attributes", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), @@ -221,6 +228,8 @@ export const tagClassAttributes = pgTable("tag_class_attributes", { seqIdx: index("tag_class_attributes_seq_idx").on(table.seq) }; }); + + // tagTypeClassFormMappings에 projectId 추가 export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", { id: serial("id").primaryKey(), |