diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/evaluationTarget.ts | 319 | ||||
| -rw-r--r-- | db/schema/gtc.ts | 110 | ||||
| -rw-r--r-- | db/schema/vendorData.ts | 3 |
3 files changed, 431 insertions, 1 deletions
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts index 343efc4e..c8011e96 100644 --- a/db/schema/evaluationTarget.ts +++ b/db/schema/evaluationTarget.ts @@ -844,6 +844,25 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q LIMIT 1 )`.as('cs_eval_status'), + // admin 평가 담당 상태 + adminEvalStatus: 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 = 'admin' + LIMIT 1 + )`.as('admin_eval_status'), + // ═══════════════════════════════════════════════════════════════ // 리뷰어 통계 (서브쿼리로 계산) // ═══════════════════════════════════════════════════════════════ @@ -1072,3 +1091,303 @@ export interface DepartmentEvaluationStatus { export type EvaluationProgress = 'NOT_STARTED' | 'IN_PROGRESS' | 'COMPLETED'; export type PeriodicEvaluationStatus = 'PENDING_SUBMISSION' | 'SUBMITTED' | 'IN_REVIEW' | 'REVIEW_COMPLETED' | 'FINALIZED'; + + +/** + * 배열에서 첫 번째 값을 뽑아내는 헬퍼. 정렬 기준을 주면 해당 기준으로 정렬 후 첫 번째 값을 가져온다. + */ +const first = <T>(expr: SQL<T>, order?: SQL) => + order + ? sql<T>`(ARRAY_AGG(${expr} ORDER BY ${order} NULLS LAST))[1]` + : sql<T>`(ARRAY_AGG(${expr}))[1]`; + +/** + * BOOL_AND / BOOL_OR 사용이 애매한 경우를 대비한 캐스팅 헬퍼 (선택적으로 사용) + * ex) boolMax(expr) → (MAX((expr)::int))::bool + */ +const boolMax = (expr: SQL<boolean>) => sql<boolean>`(MAX((${expr})::int))::bool`; + +export const periodicEvaluationsAggregatedView = pgView( + "periodic_evaluations_aggregated_view" +).as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // 집계된 식별자 및 기본 정보 + // ═══════════════════════════════════════════════════════════════ + id: sql<string>`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<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)), 1)`.as( + "process_score" + ), + priceScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)), 1)`.as( + "price_score" + ), + deliveryScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)), 1)`.as( + "delivery_score" + ), + selfEvaluationScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)), 1)`.as( + "self_evaluation_score" + ), + participationBonus: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)), 1)`.as( + "participation_bonus" + ), + qualityDeduction: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)), 1)`.as( + "quality_deduction" + ), + + // 최종 점수의 평균 + finalScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)), 1)`.as( + "final_score" + ), + + // ═══════════════════════════════════════════════════════════════ + // 평가 등급 (평균 점수를 기반으로 재계산) + // ═══════════════════════════════════════════════════════════════ + evaluationGrade: sql<string>` + 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<string>` + 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<string>` + 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<string>` + 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<string>` + 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<string>` + 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<string>` + 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<string>` + 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<string>` + 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<boolean>` + BOOL_AND(${periodicEvaluationsView.documentsSubmitted}) + `.as("documents_submitted"), + + submissionDate: sql<Date>`MAX(${periodicEvaluationsView.submissionDate})`.as( + "submission_date" + ), + submissionDeadline: sql<Date>`MAX(${periodicEvaluationsView.submissionDeadline})`.as( + "submission_deadline" + ), + reviewCompletedAt: sql<Date>`MAX(${periodicEvaluationsView.reviewCompletedAt})`.as( + "review_completed_at" + ), + finalizedAt: sql<Date>`MAX(${periodicEvaluationsView.finalizedAt})`.as( + "finalized_at" + ), + + // ═══════════════════════════════════════════════════════════════ + // Division 정보 및 집계 메타데이터 + // ═══════════════════════════════════════════════════════════════ + division: sql<string>` + CASE + WHEN COUNT(DISTINCT ${periodicEvaluationsView.division}) > 1 THEN 'BOTH' + ELSE MAX(${periodicEvaluationsView.division}) + END + `.as("division"), + + evaluationCount: sql<number>`COUNT(*)::int`.as("evaluation_count"), + divisions: sql<string>`STRING_AGG(DISTINCT ${periodicEvaluationsView.division}, ',')`.as( + "divisions" + ), + + // ═══════════════════════════════════════════════════════════════ + // 리뷰어 통계 (합계) + // ═══════════════════════════════════════════════════════════════ + totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})::int`.as( + "total_reviewers" + ), + completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})::int`.as( + "completed_reviewers" + ), + pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})::int`.as( + "pending_reviewers" + ), + + // ═══════════════════════════════════════════════════════════════ + // 기타 정보 + // ═══════════════════════════════════════════════════════════════ + evaluationPeriod: sql<string>`MAX(${periodicEvaluationsView.evaluationPeriod})`.as( + "evaluation_period" + ), + evaluationNote: sql<string>`STRING_AGG(${periodicEvaluationsView.evaluationNote}, ' | ')`.as( + "evaluation_note" + ), + + // 최종 확정자 정보 (가장 최근 확정자) + finalizedBy: first<string>( + sql`${periodicEvaluationsView.finalizedBy}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by"), + finalizedByUserName: first<string>( + sql`${periodicEvaluationsView.finalizedByUserName}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by_user_name"), + finalizedByUserEmail: first<string>( + sql`${periodicEvaluationsView.finalizedByUserEmail}`, + sql`${periodicEvaluationsView.finalizedAt} DESC` + ).as("finalized_by_user_email"), + + // 생성/수정 일시 + createdAt: sql<Date>`MIN(${periodicEvaluationsView.createdAt})`.as( + "created_at" + ), + updatedAt: sql<Date>`MAX(${periodicEvaluationsView.updatedAt})`.as( + "updated_at" + ), + + // 추가 평가 대상 정보들 (첫 번째 레코드 사용) + evaluationTargetId: first<string>( + sql`${periodicEvaluationsView.evaluationTargetId}` + ).as("evaluation_target_id"), + + evaluationTargetAdminComment: sql<string>` + STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetAdminComment}, ' | ') + `.as("evaluation_target_admin_comment"), + + evaluationTargetConsolidatedComment: sql<string>` + STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetConsolidatedComment}, ' | ') + `.as("evaluation_target_consolidated_comment"), + + // Boolean / Enum 필드라면 first()로 첫 번째 값만 가져오도록 변경 + evaluationTargetConsensusStatus: first<string>( + sql`${periodicEvaluationsView.evaluationTargetConsensusStatus}`, + sql`${periodicEvaluationsView.updatedAt} DESC` + ).as("evaluation_target_consensus_status"), + + evaluationTargetConfirmedAt: sql<Date>` + 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;
\ No newline at end of file diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts new file mode 100644 index 00000000..a4052d61 --- /dev/null +++ b/db/schema/gtc.ts @@ -0,0 +1,110 @@ +import { + pgTable, + serial, + varchar, + text, + integer, + timestamp, + pgEnum, + boolean, + index, + uniqueIndex + } from "drizzle-orm/pg-core" + import { relations } from "drizzle-orm" +import { projects } from "./projects" +import { users } from "./users" + + // GTC 구분 enum + export const gtcTypeEnum = pgEnum("gtc_type", ["standard", "project"]) + + // GTC 문서 테이블 + export const gtcDocuments = pgTable("gtc_documents", { + id: serial("id").primaryKey(), + + // 구분 (표준/프로젝트) + type: gtcTypeEnum("type").notNull(), + + // 프로젝트 참조 (프로젝트 타입인 경우만) + projectId: integer("project_id").references(() => projects.id, { + onDelete: "cascade" + }), + + // 리비전 번호 + revision: integer("revision").notNull().default(0), + + // 파일 정보 + fileName: varchar("file_name", { length: 255 }), + filePath: varchar("file_path", { length: 500 }), + fileSize: integer("file_size"), // bytes + + // 최초 등록 정보 + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + createdById: integer("created_by_id") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + + // 최종 수정 정보 + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedById: integer("updated_by_id") + .references(() => users.id, { onDelete: "set null" }), + + // 편집 사유 + editReason: text("edit_reason"), + + // 활성 상태 + isActive: boolean("is_active").default(true).notNull(), + + }, (table) => { + return { + // 프로젝트별 리비전 유니크 (표준의 경우 projectId가 null) + projectRevisionIdx: uniqueIndex("gtc_project_revision_idx") + .on(table.projectId, table.revision, table.type), + + // 조회 성능을 위한 인덱스들 + typeIdx: index("gtc_type_idx").on(table.type), + projectIdx: index("gtc_project_idx").on(table.projectId), + createdAtIdx: index("gtc_created_at_idx").on(table.createdAt), + updatedAtIdx: index("gtc_updated_at_idx").on(table.updatedAt), + } + }) + + // 관계 정의 (필요한 경우) + export const gtcDocumentsRelations = relations(gtcDocuments, ({ one }) => ({ + project: one(projects, { + fields: [gtcDocuments.projectId], + references: [projects.id], + }), + createdBy: one(users, { + fields: [gtcDocuments.createdById], + references: [users.id], + }), + updatedBy: one(users, { + fields: [gtcDocuments.updatedById], + references: [users.id], + }), + })) + + // 타입 정의 + export type GtcDocument = typeof gtcDocuments.$inferSelect + export type NewGtcDocument = typeof gtcDocuments.$inferInsert + + // 조인된 결과를 위한 타입 + export type GtcDocumentWithRelations = GtcDocument & { + project?: { + id: number + code: string + name: string + } + createdBy?: { + id: number + name: string + } + updatedBy?: { + id: number + name: string + } + }
\ No newline at end of file diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts index 5e1e65cf..a512a8d7 100644 --- a/db/schema/vendorData.ts +++ b/db/schema/vendorData.ts @@ -248,7 +248,8 @@ export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", table.projectId, table.tagTypeLabel, table.classLabel, - table.formCode + table.formCode, + table.remark ) }; }); |