import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, unique, decimal, index } from "drizzle-orm/pg-core"; import { eq , sql, relations} from "drizzle-orm"; import { vendors } from "./vendors"; import { users } from "./users"; import { contracts } from "./contract"; import { regEvalCriteria, regEvalCriteriaDetails } from "./evaluationCriteria"; // 평가 대상 메인 테이블 export const evaluationTargets = pgTable("evaluation_targets", { id: serial("id").primaryKey(), // 평가년도 (예: 2024) evaluationYear: integer("evaluation_year").notNull(), // 구분 (해양/조선) division: varchar("division", { length: 20, enum: ["SHIP", "PLANT"] }).notNull(), // 벤더 정보 vendorId: integer("vendor_id").references(() => vendors.id).notNull(), vendorCode: varchar("vendor_code", { length: 100 }).notNull(), vendorName: varchar("vendor_name", { length: 255 }).notNull(), // 내외자 구분 domesticForeign: varchar("domestic_foreign", { length: 20, enum: ["DOMESTIC", "FOREIGN"] }).notNull(), // 자재구분 materialType: varchar("material_type", { length: 30, enum: ["EQUIPMENT", "BULK", "EQUIPMENT_BULK"] }).notNull(), // 상태 status: varchar("status", { length: 30, enum: ["PENDING", "CONFIRMED", "EXCLUDED"] }).notNull().default("PENDING"), // 관리자 의견 adminComment: text("admin_comment"), adminUserId: integer("admin_user_id").references(() => users.id), // 종합 담당자 의견 (각 담당자들의 의견을 모아서 표시) consolidatedComment: text("consolidated_comment"), // 의견 일치 여부 (null: 아직 검토중, true: 일치, false: 불일치) consensusStatus: boolean("consensus_status"), // 최종 확정일 confirmedAt: timestamp("confirmed_at"), confirmedBy: integer("confirmed_by").references(() => users.id), ldClaimCount: integer("ld_claim_count").default(0), ldClaimAmount: decimal("ld_claim_amount", { precision: 15, scale: 2 }).default("0"), ldClaimCurrency: varchar("ld_claim_currency", { length: 10, enum: ["KRW", "USD", "EUR", "JPY"] }).default("KRW"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); // 평가 대상별 담당자 지정 테이블 export const evaluationTargetReviewers = pgTable("evaluation_target_reviewers", { id: serial("id").primaryKey(), evaluationTargetId: integer("evaluation_target_id") .references(() => evaluationTargets.id, { onDelete: "cascade" }) .notNull(), departmentCode: varchar("department_code", { length: 50 }).notNull(), departmentNameFrom: varchar("department_name_from", { length: 50 }), reviewerUserId: integer("reviewer_user_id") .references(() => users.id) .notNull(), // 지정일 assignedAt: timestamp("assigned_at").defaultNow().notNull(), assignedBy: integer("assigned_by").references(() => users.id).notNull(), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => ({ // 같은 평가대상에 같은 부서는 한 명의 담당자만 uniqueTargetDepartment: unique("unique_target_department") .on(table.evaluationTargetId, table.departmentCode), })); // 각 담당자의 평가 대상 검토 결과 테이블 export const evaluationTargetReviews = pgTable("evaluation_target_reviews", { id: serial("id").primaryKey(), evaluationTargetId: integer("evaluation_target_id") .references(() => evaluationTargets.id, { onDelete: "cascade" }) .notNull(), reviewerUserId: integer("reviewer_user_id") .references(() => users.id) .notNull(), // 부서 코드 (API에서 부서 정보 조회용) departmentCode: varchar("department_code", { length: 50 }).notNull(), // 평가 대상 동의 여부 isApproved: boolean("is_approved"), // 검토 의견 reviewComment: text("review_comment"), // 검토일 reviewedAt: timestamp("reviewed_at"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => ({ // 같은 평가대상에 같은 담당자는 한 번만 검토 uniqueTargetReviewer: unique("unique_target_reviewer") .on(table.evaluationTargetId, table.reviewerUserId), })); // 관계 정의 export const evaluationTargetsRelations = relations(evaluationTargets, ({ one, many }) => ({ vendor: one(vendors, { fields: [evaluationTargets.vendorId], references: [vendors.id], }), adminUser: one(users, { fields: [evaluationTargets.adminUserId], references: [users.id], }), confirmedByUser: one(users, { fields: [evaluationTargets.confirmedBy], references: [users.id], }), reviewers: many(evaluationTargetReviewers), reviews: many(evaluationTargetReviews), })); export const evaluationTargetReviewersRelations = relations(evaluationTargetReviewers, ({ one }) => ({ evaluationTarget: one(evaluationTargets, { fields: [evaluationTargetReviewers.evaluationTargetId], references: [evaluationTargets.id], }), reviewer: one(users, { fields: [evaluationTargetReviewers.reviewerUserId], references: [users.id], }), assignedByUser: one(users, { fields: [evaluationTargetReviewers.assignedBy], references: [users.id], }), })); export const evaluationTargetReviewsRelations = relations(evaluationTargetReviews, ({ one }) => ({ evaluationTarget: one(evaluationTargets, { fields: [evaluationTargetReviews.evaluationTargetId], references: [evaluationTargets.id], }), reviewer: one(users, { fields: [evaluationTargetReviews.reviewerUserId], references: [users.id], }), })); export const orderRecordsRelations = relations(contracts, ({ one }) => ({ vendor: one(vendors, { fields: [contracts.vendorId], references: [vendors.id], }), })); // 평가 담당 부서 코드 상수 export const EVALUATION_DEPARTMENT_CODES = { ORDER_EVAL: "ORDER_EVAL", // 발주 평가 담당 PROCUREMENT_EVAL: "PROCUREMENT_EVAL", // 조달 평가 담당 QUALITY_EVAL: "QUALITY_EVAL", // 품질 평가 담당 DESIGN_EVAL: "DESIGN_EVAL", // 설계 평가 담당 CS_EVAL: "CS_EVAL", // CS 평가 담당 } as const; export type EvaluationDepartmentCode = keyof typeof EVALUATION_DEPARTMENT_CODES; // ============= TypeScript 타입 정의 ============= export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED"; export type Division = "PLANT" | "SHIP"; export type MaterialType = "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK"; export type DomesticForeign = "DOMESTIC" | "FOREIGN"; export interface EvaluationTargetCreate { evaluationYear: number; division: Division; vendorId: number; vendorCode: string; vendorName: string; domesticForeign: DomesticForeign; materialType: MaterialType; adminComment?: string; managerComment?: string; } export interface ReviewerAssignment { departmentId: number; reviewerUserId: number; } export interface EvaluationReview { evaluationTargetId: number; reviewerUserId: number; departmentId: number; isApproved: boolean; reviewComment?: string; } export interface EvaluationTargetWithReviews { id: number; evaluationYear: number; division: Division; vendorCode: string; vendorName: string; domesticForeign: DomesticForeign; materialType: MaterialType; status: EvaluationTargetStatus; consensusStatus: boolean | null; adminComment?: string; managerComment?: string; reviews: { departmentName: string; reviewerName: string; isApproved: boolean | null; reviewComment?: string; reviewedAt?: Date; }[]; } export type EvaluationTargets = typeof evaluationTargets.$inferSelect; export type NewEvaluationTargets= typeof evaluationTargets.$inferInsert; export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_departments").as((qb) => { return qb .select({ // 기본 정보 id: evaluationTargets.id, evaluationYear: evaluationTargets.evaluationYear, division: evaluationTargets.division, vendorCode: evaluationTargets.vendorCode, vendorName: evaluationTargets.vendorName, domesticForeign: evaluationTargets.domesticForeign, materialType: evaluationTargets.materialType, status: evaluationTargets.status, consensusStatus: evaluationTargets.consensusStatus, adminComment: evaluationTargets.adminComment, consolidatedComment: evaluationTargets.consolidatedComment, confirmedAt: evaluationTargets.confirmedAt, confirmedBy: evaluationTargets.confirmedBy, ldClaimCount: evaluationTargets.ldClaimCount, ldClaimAmount: evaluationTargets.ldClaimAmount, ldClaimCurrency: evaluationTargets.ldClaimCurrency, createdAt: evaluationTargets.createdAt, updatedAt: evaluationTargets.updatedAt, // 발주 평가 담당 (ORDER_EVAL) orderReviewerName: sql`order_reviewer.name`.as('order_reviewer_name'), orderReviewerEmail: sql`order_reviewer.email`.as('order_reviewer_email'), orderDepartmentName: sql`order_etr.department_name_from`.as('order_department_name'), orderIsApproved: sql`order_review.is_approved`.as('order_is_approved'), orderReviewedAt: sql`order_review.reviewed_at`.as('order_reviewed_at'), // 조달 평가 담당 (PROCUREMENT_EVAL) procurementReviewerName: sql`procurement_reviewer.name`.as('procurement_reviewer_name'), procurementReviewerEmail: sql`procurement_reviewer.email`.as('procurement_reviewer_email'), procurementDepartmentName: sql`procurement_etr.department_name_from`.as('procurement_department_name'), procurementIsApproved: sql`procurement_review.is_approved`.as('procurement_is_approved'), procurementReviewedAt: sql`procurement_review.reviewed_at`.as('procurement_reviewed_at'), // 품질 평가 담당 (QUALITY_EVAL) qualityReviewerName: sql`quality_reviewer.name`.as('quality_reviewer_name'), qualityReviewerEmail: sql`quality_reviewer.email`.as('quality_reviewer_email'), qualityDepartmentName: sql`quality_etr.department_name_from`.as('quality_department_name'), qualityIsApproved: sql`quality_review.is_approved`.as('quality_is_approved'), qualityReviewedAt: sql`quality_review.reviewed_at`.as('quality_reviewed_at'), // 설계 평가 담당 (DESIGN_EVAL) designReviewerName: sql`design_reviewer.name`.as('design_reviewer_name'), designReviewerEmail: sql`design_reviewer.email`.as('design_reviewer_email'), designDepartmentName: sql`design_etr.department_name_from`.as('design_department_name'), designIsApproved: sql`design_review.is_approved`.as('design_is_approved'), designReviewedAt: sql`design_review.reviewed_at`.as('design_reviewed_at'), // CS 평가 담당 (CS_EVAL) csReviewerName: sql`cs_reviewer.name`.as('cs_reviewer_name'), csReviewerEmail: sql`cs_reviewer.email`.as('cs_reviewer_email'), csDepartmentName: sql`cs_etr.department_name_from`.as('cs_department_name'), csIsApproved: sql`cs_review.is_approved`.as('cs_is_approved'), csReviewedAt: sql`cs_review.reviewed_at`.as('cs_reviewed_at'), }) .from(evaluationTargets) // 발주 평가 담당자 JOIN .leftJoin( sql`evaluation_target_reviewers order_etr`, sql`${evaluationTargets.id} = order_etr.evaluation_target_id AND order_etr.department_code = 'ORDER_EVAL'` ) .leftJoin( sql`users order_reviewer`, sql`order_etr.reviewer_user_id = order_reviewer.id` ) .leftJoin( sql`evaluation_target_reviews order_review`, sql`${evaluationTargets.id} = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id` ) // 조달 평가 담당자 JOIN .leftJoin( sql`evaluation_target_reviewers procurement_etr`, sql`${evaluationTargets.id} = procurement_etr.evaluation_target_id AND procurement_etr.department_code = 'PROCUREMENT_EVAL'` ) .leftJoin( sql`users procurement_reviewer`, sql`procurement_etr.reviewer_user_id = procurement_reviewer.id` ) .leftJoin( sql`evaluation_target_reviews procurement_review`, sql`${evaluationTargets.id} = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id` ) // 품질 평가 담당자 JOIN .leftJoin( sql`evaluation_target_reviewers quality_etr`, sql`${evaluationTargets.id} = quality_etr.evaluation_target_id AND quality_etr.department_code = 'QUALITY_EVAL'` ) .leftJoin( sql`users quality_reviewer`, sql`quality_etr.reviewer_user_id = quality_reviewer.id` ) .leftJoin( sql`evaluation_target_reviews quality_review`, sql`${evaluationTargets.id} = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id` ) // 설계 평가 담당자 JOIN .leftJoin( sql`evaluation_target_reviewers design_etr`, sql`${evaluationTargets.id} = design_etr.evaluation_target_id AND design_etr.department_code = 'DESIGN_EVAL'` ) .leftJoin( sql`users design_reviewer`, sql`design_etr.reviewer_user_id = design_reviewer.id` ) .leftJoin( sql`evaluation_target_reviews design_review`, sql`${evaluationTargets.id} = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id` ) // CS 평가 담당자 JOIN .leftJoin( sql`evaluation_target_reviewers cs_etr`, sql`${evaluationTargets.id} = cs_etr.evaluation_target_id AND cs_etr.department_code = 'CS_EVAL'` ) .leftJoin( sql`users cs_reviewer`, sql`cs_etr.reviewer_user_id = cs_reviewer.id` ) .leftJoin( sql`evaluation_target_reviews cs_review`, sql`${evaluationTargets.id} = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id` ); }); // 타입 정의 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: ["A", "B", "C", "D"] }), // 평가 점수 (평가자 평균) evaluationScore: decimal("evaluation_score", { precision: 5, scale: 2 }), evaluationGrade: varchar("evaluation_grade", { length: 5, enum: ["A", "B", "C", "D"] }), // 평가 상태 status: varchar("status", { length: 30, enum: ["PENDING","PENDING_SUBMISSION", "SUBMITTED", "IN_REVIEW", "REVIEW_COMPLETED", "FINALIZED"] }).notNull().default("PENDING"), // 평가 완료 정보 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(), // 평가 완료 여부 isCompleted: boolean("is_completed").default(false), completedAt: timestamp("completed_at"), submittedAt: timestamp("submitted_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) , // 리뷰어가 매긴 점수 score: decimal("score", { precision: 5, scale: 2 }), // 세부 의견 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 periodicEvaluationsRelations2 = 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), })); export const reviewerEvaluationAttachments = pgTable("reviewer_evaluation_attachments", { id: serial("id").primaryKey(), // 리뷰어 평가 세부사항 참조 (어떤 평가 항목에 대한 첨부파일인지) reviewerEvaluationDetailId: integer("reviewer_evaluation_detail_id") .references(() => reviewerEvaluationDetails.id, { onDelete: "cascade" }) .notNull(), // 파일 정보 originalFileName: varchar("original_file_name", { length: 255 }).notNull(), storedFileName: varchar("stored_file_name", { length: 255 }).notNull(), filePath: varchar("file_path", { length: 500 }).notNull(), publicPath: varchar("public_path", { length: 500 }).notNull(), // 파일 메타데이터 fileSize: integer("file_size").notNull(), // bytes mimeType: varchar("mime_type", { length: 100 }), fileExtension: varchar("file_extension", { length: 10 }), // 파일 설명 (선택사항) description: text("description"), // 업로드 정보 uploadedBy: integer("uploaded_by") .references(() => users.id) .notNull(), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => ({ // 인덱스 reviewerEvaluationDetailIdIndex: index("reviewer_evaluation_detail_id_idx") .on(table.reviewerEvaluationDetailId), })); // 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; export type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect; type NewReviewerEvaluation = typeof reviewerEvaluations.$inferInsert; export type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect; export 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, // ═══════════════════════════════════════════════════════════════ // 동적으로 계산되는 평가 점수들 // ═══════════════════════════════════════════════════════════════ // 공정 점수 (processScore) processScore: sql`( 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 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`( 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 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`( 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 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`( 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 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`( 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 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`( 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 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'), // 평가 상태 status: periodicEvaluations.status, // 평가 완료 정보 reviewCompletedAt: periodicEvaluations.reviewCompletedAt, finalizedAt: periodicEvaluations.finalizedAt, finalizedBy: periodicEvaluations.finalizedBy, // 비고 evaluationNote: periodicEvaluations.evaluationNote, // 생성/수정일 createdAt: periodicEvaluations.createdAt, updatedAt: periodicEvaluations.updatedAt, // ═══════════════════════════════════════════════════════════════ // 평가 대상 추가 정보 (evaluationTargets 조인) // ═══════════════════════════════════════════════════════════════ evaluationTargetAdminComment: evaluationTargets.adminComment, evaluationTargetConsolidatedComment: evaluationTargets.consolidatedComment, evaluationTargetConsensusStatus: evaluationTargets.consensusStatus, evaluationTargetConfirmedAt: evaluationTargets.confirmedAt, // ═══════════════════════════════════════════════════════════════ // 부서별 리뷰어 상태 (서브쿼리로 계산) // ═══════════════════════════════════════════════════════════════ // 발주 평가 담당 상태 orderEvalStatus: sql`( 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`( 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`( 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`( 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`( 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'), // admin 평가 담당 상태 adminEvalStatus: sql`( 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 = 'admin' LIMIT 1 )`.as('admin_eval_status'), // ═══════════════════════════════════════════════════════════════ // 리뷰어 통계 (서브쿼리로 계산) // ═══════════════════════════════════════════════════════════════ totalReviewers: sql`( SELECT COUNT(*)::int FROM ${reviewerEvaluations} re WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} )`.as('total_reviewers'), completedReviewers: sql`( SELECT COUNT(*)::int FROM ${reviewerEvaluations} re WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND re.is_completed = true )`.as('completed_reviewers'), pendingReviewers: sql`( 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; // 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`reviewer_user.name`.as('reviewer_name'), reviewerEmail: sql`reviewer_user.email`.as('reviewer_email'), // 리뷰어 지정 정보 assignedAt: evaluationTargetReviewers.assignedAt, assignedBy: evaluationTargetReviewers.assignedBy, assignedByUserName: sql`assigned_by_user.name`.as('assigned_by_user_name'), // ═══════════════════════════════════════════════════════════════ // 최종 확정자 정보 // ═══════════════════════════════════════════════════════════════ finalizedByUserName: sql`finalized_by_user.name`.as('finalized_by_user_name'), finalizedByUserEmail: sql`finalized_by_user.email`.as('finalized_by_user_email'), // ═══════════════════════════════════════════════════════════════ // 추가 계산 필드 // ═══════════════════════════════════════════════════════════════ // 평가 진행률 (개인별) evaluationProgress: sql` 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'; /** * 배열에서 첫 번째 값을 뽑아내는 헬퍼. 정렬 기준을 주면 해당 기준으로 정렬 후 첫 번째 값을 가져온다. */ const first = (expr: SQL, order?: SQL) => order ? sql`(ARRAY_AGG(${expr} ORDER BY ${order} NULLS LAST))[1]` : sql`(ARRAY_AGG(${expr}))[1]`; /** * BOOL_AND / BOOL_OR 사용이 애매한 경우를 대비한 캐스팅 헬퍼 (선택적으로 사용) * ex) boolMax(expr) → (MAX((expr)::int))::bool */ const boolMax = (expr: SQL) => sql`(MAX((${expr})::int))::bool`; export const periodicEvaluationsAggregatedView = pgView( "periodic_evaluations_aggregated_view" ).as((qb) => qb .select({ // ═══════════════════════════════════════════════════════════════ // 집계된 식별자 및 기본 정보 // ═══════════════════════════════════════════════════════════════ id: sql`CONCAT(${periodicEvaluationsView.evaluationYear}, '_', ${periodicEvaluationsView.vendorId})`.as( "id" ), // 그룹핑 기준 필드들 evaluationYear: periodicEvaluationsView.evaluationYear, vendorId: periodicEvaluationsView.vendorId, vendorCode: periodicEvaluationsView.vendorCode, vendorName: periodicEvaluationsView.vendorName, domesticForeign: periodicEvaluationsView.domesticForeign, materialType: periodicEvaluationsView.materialType, // ═══════════════════════════════════════════════════════════════ // 평가 점수들의 평균 계산 // ═══════════════════════════════════════════════════════════════ processScore: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)), 1)`.as( "process_score" ), priceScore: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)), 1)`.as( "price_score" ), deliveryScore: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)), 1)`.as( "delivery_score" ), selfEvaluationScore: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)), 1)`.as( "self_evaluation_score" ), participationBonus: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)), 1)`.as( "participation_bonus" ), qualityDeduction: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)), 1)`.as( "quality_deduction" ), // 최종 점수의 평균 finalScore: sql`ROUND(AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)), 1)`.as( "final_score" ), // ═══════════════════════════════════════════════════════════════ // 평가 등급 (평균 점수를 기반으로 재계산) // ═══════════════════════════════════════════════════════════════ evaluationGrade: sql` CASE WHEN ( AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) ) >= 90 THEN 'S' WHEN ( AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) ) >= 80 THEN 'A' WHEN ( AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) ) >= 70 THEN 'B' WHEN ( AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) + AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)) ) >= 60 THEN 'C' ELSE 'D' END `.as("evaluation_grade"), finalGrade: sql` CASE WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 90 THEN 'S' WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 80 THEN 'A' WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 70 THEN 'B' WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 60 THEN 'C' ELSE 'D' END `.as("final_grade"), // ═══════════════════════════════════════════════════════════════ // 상태 정보 (우선순위 기반으로 결정) // ═══════════════════════════════════════════════════════════════ status: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} = 'FINALIZED' THEN 1 END) = COUNT(*) THEN 'FINALIZED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) = COUNT(*) THEN 'REVIEW_COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'IN_REVIEW' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('SUBMITTED', 'IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'SUBMITTED' ELSE 'PENDING_SUBMISSION' END `.as("status"), // ═══════════════════════════════════════════════════════════════ // 부서별 평가 상태 (최고 진행 상태로 설정) // ═══════════════════════════════════════════════════════════════ orderEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("order_eval_status"), procurementEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("procurement_eval_status"), qualityEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("quality_eval_status"), designEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("design_eval_status"), csEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("cs_eval_status"), adminEvalStatus: sql` CASE WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS' WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED' ELSE 'NOT_ASSIGNED' END `.as("admin_eval_status"), // ═══════════════════════════════════════════════════════════════ // 문서 제출 및 날짜 정보 // ═══════════════════════════════════════════════════════════════ documentsSubmitted: sql` BOOL_AND(${periodicEvaluationsView.documentsSubmitted}) `.as("documents_submitted"), submissionDate: sql`MAX(${periodicEvaluationsView.submissionDate})`.as( "submission_date" ), submissionDeadline: sql`MAX(${periodicEvaluationsView.submissionDeadline})`.as( "submission_deadline" ), reviewCompletedAt: sql`MAX(${periodicEvaluationsView.reviewCompletedAt})`.as( "review_completed_at" ), finalizedAt: sql`MAX(${periodicEvaluationsView.finalizedAt})`.as( "finalized_at" ), // ═══════════════════════════════════════════════════════════════ // Division 정보 및 집계 메타데이터 // ═══════════════════════════════════════════════════════════════ division: sql` CASE WHEN COUNT(DISTINCT ${periodicEvaluationsView.division}) > 1 THEN 'BOTH' ELSE MAX(${periodicEvaluationsView.division}) END `.as("division"), evaluationCount: sql`COUNT(*)::int`.as("evaluation_count"), divisions: sql`STRING_AGG(DISTINCT ${periodicEvaluationsView.division}, ',')`.as( "divisions" ), // ═══════════════════════════════════════════════════════════════ // 리뷰어 통계 (합계) // ═══════════════════════════════════════════════════════════════ totalReviewers: sql`SUM(${periodicEvaluationsView.totalReviewers})::int`.as( "total_reviewers" ), completedReviewers: sql`SUM(${periodicEvaluationsView.completedReviewers})::int`.as( "completed_reviewers" ), pendingReviewers: sql`SUM(${periodicEvaluationsView.pendingReviewers})::int`.as( "pending_reviewers" ), // ═══════════════════════════════════════════════════════════════ // 기타 정보 // ═══════════════════════════════════════════════════════════════ evaluationPeriod: sql`MAX(${periodicEvaluationsView.evaluationPeriod})`.as( "evaluation_period" ), evaluationNote: sql`STRING_AGG(${periodicEvaluationsView.evaluationNote}, ' | ')`.as( "evaluation_note" ), // 최종 확정자 정보 (가장 최근 확정자) finalizedBy: first( sql`${periodicEvaluationsView.finalizedBy}`, sql`${periodicEvaluationsView.finalizedAt} DESC` ).as("finalized_by"), finalizedByUserName: first( sql`${periodicEvaluationsView.finalizedByUserName}`, sql`${periodicEvaluationsView.finalizedAt} DESC` ).as("finalized_by_user_name"), finalizedByUserEmail: first( sql`${periodicEvaluationsView.finalizedByUserEmail}`, sql`${periodicEvaluationsView.finalizedAt} DESC` ).as("finalized_by_user_email"), // 생성/수정 일시 createdAt: sql`MIN(${periodicEvaluationsView.createdAt})`.as( "created_at" ), updatedAt: sql`MAX(${periodicEvaluationsView.updatedAt})`.as( "updated_at" ), // 추가 평가 대상 정보들 (첫 번째 레코드 사용) evaluationTargetId: first( sql`${periodicEvaluationsView.evaluationTargetId}` ).as("evaluation_target_id"), evaluationTargetAdminComment: sql` STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetAdminComment}, ' | ') `.as("evaluation_target_admin_comment"), evaluationTargetConsolidatedComment: sql` STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetConsolidatedComment}, ' | ') `.as("evaluation_target_consolidated_comment"), // Boolean / Enum 필드라면 first()로 첫 번째 값만 가져오도록 변경 evaluationTargetConsensusStatus: first( sql`${periodicEvaluationsView.evaluationTargetConsensusStatus}`, sql`${periodicEvaluationsView.updatedAt} DESC` ).as("evaluation_target_consensus_status"), evaluationTargetConfirmedAt: sql` MAX(${periodicEvaluationsView.evaluationTargetConfirmedAt}) `.as("evaluation_target_confirmed_at"), }) .from(periodicEvaluationsView) .groupBy( periodicEvaluationsView.evaluationYear, periodicEvaluationsView.vendorId, periodicEvaluationsView.vendorCode, periodicEvaluationsView.vendorName, periodicEvaluationsView.domesticForeign, periodicEvaluationsView.materialType ) ); // 타입 정의 export type PeriodicEvaluationAggregatedView = typeof periodicEvaluationsAggregatedView.$inferSelect;