diff options
Diffstat (limited to 'db/schema/evaluationTarget.ts')
| -rw-r--r-- | db/schema/evaluationTarget.ts | 341 |
1 files changed, 339 insertions, 2 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts index 4eff1c19..915641c8 100644 --- a/db/schema/evaluationTarget.ts +++ b/db/schema/evaluationTarget.ts @@ -3,6 +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"; // 평가 대상 메인 테이블 export const evaluationTargets = pgTable("evaluation_targets", { @@ -178,7 +179,7 @@ export const orderRecordsRelations = relations(contracts, ({ one }) => ({ }), })); -// 평가 담당 부서 코드 상수 (조직 API와 매핑) +// 평가 담당 부서 코드 상수 export const EVALUATION_DEPARTMENT_CODES = { ORDER_EVAL: "ORDER_EVAL", // 발주 평가 담당 PROCUREMENT_EVAL: "PROCUREMENT_EVAL", // 조달 평가 담당 @@ -192,7 +193,7 @@ export type EvaluationDepartmentCode = keyof typeof EVALUATION_DEPARTMENT_CODES; // ============= TypeScript 타입 정의 ============= export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED"; - export type Division = "OCEAN" | "SHIPYARD"; + export type Division = "PLANT" | "SHIP"; export type MaterialType = "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK"; export type DomesticForeign = "DOMESTIC" | "FOREIGN"; @@ -383,3 +384,339 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ // 타입 정의 export type EvaluationTargetWithDepartments = typeof evaluationTargetsWithDepartments.$inferSelect; + + +export const periodicEvaluations = pgTable("periodic_evaluations", { + id: serial("id").primaryKey(), + + // 평가 대상 참조 + evaluationTargetId: integer("evaluation_target_id") + .references(() => evaluationTargets.id, { onDelete: "cascade" }) + .notNull(), + + // 평가 기본 정보 + evaluationPeriod: varchar("evaluation_period", { length: 20 }).notNull(), // "상반기", "하반기", "연간" 등 + + // 업체 제출 관련 + documentsSubmitted: boolean("documents_submitted").default(false), + submissionDate: timestamp("submission_date"), + submissionDeadline: timestamp("submission_deadline"), + + // 평가 점수 (최종 확정) + finalScore: decimal("final_score", { precision: 5, scale: 2 }), + finalGrade: varchar("final_grade", { + length: 5, + enum: ["S", "A", "B", "C", "D"] + }), + + // 평가 점수 (평가자 평균) + evaluationScore: decimal("evaluation_score", { precision: 5, scale: 2 }), + evaluationGrade: varchar("evaluation_grade", { + length: 5, + 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, + enum: ["PENDING_SUBMISSION", "SUBMITTED", "IN_REVIEW", "REVIEW_COMPLETED", "FINALIZED"] + }).notNull().default("PENDING_SUBMISSION"), + + // 평가 완료 정보 + reviewCompletedAt: timestamp("review_completed_at"), + finalizedAt: timestamp("finalized_at"), + finalizedBy: integer("finalized_by").references(() => users.id), + + // 비고 + evaluationNote: text("evaluation_note"), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}, (table) => ({ + // 같은 평가대상에 같은 기간에는 하나의 평가만 + uniqueEvaluationTarget: unique("unique_evaluation_target") + .on(table.evaluationTargetId, table.evaluationPeriod), +})); + + +// 2. 리뷰어별 개별 평가 테이블 +export const reviewerEvaluations = pgTable("reviewer_evaluations", { + id: serial("id").primaryKey(), + + // 정기평가 참조 + periodicEvaluationId: integer("periodic_evaluation_id") + .references(() => periodicEvaluations.id, { onDelete: "cascade" }) + .notNull(), + + // 리뷰어 정보 (evaluationTargetReviewers 참조) + 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"), + + // 리뷰어 의견 + reviewerComment: text("reviewer_comment"), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}, (table) => ({ + // 같은 평가에 같은 리뷰어는 하나의 평가만 + uniqueReviewerEvaluation: unique("unique_reviewer_evaluation") + .on(table.periodicEvaluationId, table.evaluationTargetReviewerId), +})); + +// 2-1. 리뷰어별 세부 평가 점수 테이블 (평가표의 각 항목별 점수) +export const reviewerEvaluationDetails = pgTable("reviewer_evaluation_details", { + id: serial("id").primaryKey(), + + // 리뷰어 평가 참조 + reviewerEvaluationId: integer("reviewer_evaluation_id") + .references(() => reviewerEvaluations.id, { onDelete: "cascade" }) + .notNull(), + + // 평가 기준 참조 + regEvalCriteriaDetailsId: integer("reg_eval_criteria_details_id") + .references(() => regEvalCriteriaDetails.id) + .notNull(), + + // 리뷰어가 매긴 점수 + score: decimal("score", { precision: 5, scale: 2 }).notNull(), + + // 세부 의견 + comment: text("comment"), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}, (table) => ({ + // 같은 리뷰어 평가에서 같은 평가 기준은 하나의 점수만 + uniqueReviewerCriteria: unique("unique_reviewer_criteria") + .on(table.reviewerEvaluationId, table.regEvalCriteriaDetailsId), +})); + +// 1. periodicEvaluations relations +export const periodicEvaluationsRelations = relations(periodicEvaluations, ({ one, many }) => ({ + // 평가 대상 + evaluationTarget: one(evaluationTargets, { + fields: [periodicEvaluations.evaluationTargetId], + references: [evaluationTargets.id], + }), + + // 최종 확정자 + finalizedByUser: one(users, { + fields: [periodicEvaluations.finalizedBy], + references: [users.id], + }), + + // 리뷰어별 평가들 + reviewerEvaluations: many(reviewerEvaluations), + +})); + +// 2. reviewerEvaluations relations +export const reviewerEvaluationsRelations = relations(reviewerEvaluations, ({ one, many }) => ({ + // 정기평가 + periodicEvaluation: one(periodicEvaluations, { + fields: [reviewerEvaluations.periodicEvaluationId], + references: [periodicEvaluations.id], + }), + + // 평가 대상 리뷰어 + evaluationTargetReviewer: one(evaluationTargetReviewers, { + fields: [reviewerEvaluations.evaluationTargetReviewerId], + references: [evaluationTargetReviewers.id], + }), + + // 세부 평가 점수들 + evaluationDetails: many(reviewerEvaluationDetails), +})); + +// 3. reviewerEvaluationDetails relations +export const reviewerEvaluationDetailsRelations = relations(reviewerEvaluationDetails, ({ one }) => ({ + // 리뷰어 평가 + reviewerEvaluation: one(reviewerEvaluations, { + fields: [reviewerEvaluationDetails.reviewerEvaluationId], + references: [reviewerEvaluations.id], + }), + + // 평가 기준 세부사항 + regEvalCriteriaDetail: one(regEvalCriteriaDetails, { + fields: [reviewerEvaluationDetails.regEvalCriteriaDetailsId], + references: [regEvalCriteriaDetails.id], + }), +})); + +// ---------------------------------------------------------------------------------------------------- + +/* TYPES */ +type PeriodicEvaluation = typeof periodicEvaluations.$inferSelect; +type NewPeriodicEvaluation = typeof periodicEvaluations.$inferInsert; + +type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect; +type NewReviewerEvaluation = typeof reviewerEvaluations.$inferInsert; + +type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect; +type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert; + +// 관계 포함 타입들 +type PeriodicEvaluationWithRelations = PeriodicEvaluation & { + evaluationTarget?: typeof evaluationTargets.$inferSelect; + finalizedByUser?: typeof users.$inferSelect; + reviewerEvaluations?: ReviewerEvaluationWithRelations[]; +}; + +type ReviewerEvaluationWithRelations = ReviewerEvaluation & { + periodicEvaluation?: PeriodicEvaluation; + evaluationTargetReviewer?: typeof evaluationTargetReviewers.$inferSelect; + evaluationDetails?: ReviewerEvaluationDetailWithRelations[]; +}; + +type ReviewerEvaluationDetailWithRelations = ReviewerEvaluationDetail & { + reviewerEvaluation?: ReviewerEvaluation; + regEvalCriteriaDetail?: typeof regEvalCriteriaDetails.$inferSelect; +}; + +export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // 정기평가 기본 정보 (평가 대상 핵심 정보 포함) + // ═══════════════════════════════════════════════════════════════ + id: periodicEvaluations.id, + evaluationTargetId: periodicEvaluations.evaluationTargetId, + + // 평가 대상 핵심 정보 (조인으로 가져와서 기본 정보로 포함) + evaluationYear: evaluationTargets.evaluationYear, + division: evaluationTargets.division, + vendorId: evaluationTargets.vendorId, + vendorCode: evaluationTargets.vendorCode, + vendorName: evaluationTargets.vendorName, + domesticForeign: evaluationTargets.domesticForeign, + materialType: evaluationTargets.materialType, + + // 평가 기간 + evaluationPeriod: periodicEvaluations.evaluationPeriod, + + // 업체 제출 관련 + documentsSubmitted: periodicEvaluations.documentsSubmitted, + submissionDate: periodicEvaluations.submissionDate, + submissionDeadline: periodicEvaluations.submissionDeadline, + + // 평가 점수 (최종 확정) + finalScore: periodicEvaluations.finalScore, + finalGrade: periodicEvaluations.finalGrade, + + // 평가 점수 (평가자 평균) + evaluationScore: periodicEvaluations.evaluationScore, + evaluationGrade: periodicEvaluations.evaluationGrade, + + // 평가항목별 점수 + processScore: periodicEvaluations.processScore, + priceScore: periodicEvaluations.priceScore, + deliveryScore: periodicEvaluations.deliveryScore, + selfEvaluationScore: periodicEvaluations.selfEvaluationScore, + + // 합계 점수 + totalScore: periodicEvaluations.totalScore, + + // 가점/감점 + participationBonus: periodicEvaluations.participationBonus, + qualityDeduction: periodicEvaluations.qualityDeduction, + + // 평가 상태 + status: periodicEvaluations.status, + + // 평가 완료 정보 + reviewCompletedAt: periodicEvaluations.reviewCompletedAt, + finalizedAt: periodicEvaluations.finalizedAt, + finalizedBy: periodicEvaluations.finalizedBy, + + // 비고 + evaluationNote: periodicEvaluations.evaluationNote, + + // 생성/수정일 + createdAt: periodicEvaluations.createdAt, + updatedAt: periodicEvaluations.updatedAt, + + // ═══════════════════════════════════════════════════════════════ + // 평가 대상 추가 정보 (evaluationTargets 조인) + // ═══════════════════════════════════════════════════════════════ + evaluationTargetStatus: evaluationTargets.status, + evaluationTargetAdminComment: evaluationTargets.adminComment, + evaluationTargetConsolidatedComment: evaluationTargets.consolidatedComment, + evaluationTargetConsensusStatus: evaluationTargets.consensusStatus, + evaluationTargetConfirmedAt: evaluationTargets.confirmedAt, + + // ═══════════════════════════════════════════════════════════════ + // 리뷰어 통계 (서브쿼리로 계산) + // ═══════════════════════════════════════════════════════════════ + totalReviewers: sql<number>`( + SELECT COUNT(*)::int + FROM ${reviewerEvaluations} re + WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} + )`.as('total_reviewers'), + + completedReviewers: sql<number>`( + SELECT COUNT(*)::int + FROM ${reviewerEvaluations} re + WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} + AND re.is_completed = true + )`.as('completed_reviewers'), + + pendingReviewers: sql<number>`( + SELECT COUNT(*)::int + FROM ${reviewerEvaluations} re + WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} + AND re.is_completed = false + )`.as('pending_reviewers'), + + // ═══════════════════════════════════════════════════════════════ + // 최종 확정자 정보 + // ═══════════════════════════════════════════════════════════════ + finalizedByUserName: users.name, + finalizedByUserEmail: users.email, + }) + .from(periodicEvaluations) + .leftJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id)) + .leftJoin(users, eq(periodicEvaluations.finalizedBy, users.id)) + .orderBy(periodicEvaluations.createdAt) +); + + +// ================================================================ +// TYPES +// ================================================================ + +export type PeriodicEvaluationView = typeof periodicEvaluationsView.$inferSelect; |