From c657ef972feeafff16ab0e07cb4771f7dd141ba0 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Thu, 10 Jul 2025 09:55:45 +0000 Subject: (대표님) 20250710 작업사항 - 평가 첨부, 로그인, SEDP 변경 요구사항 반영 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/evaluationTarget.ts | 62 +++++++++++++++++++++++++++---------------- 1 file changed, 39 insertions(+), 23 deletions(-) (limited to 'db/schema/evaluationTarget.ts') diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts index 54f2ba16..466143be 100644 --- a/db/schema/evaluationTarget.ts +++ b/db/schema/evaluationTarget.ts @@ -1,4 +1,4 @@ -import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, unique, decimal } from "drizzle-orm/pg-core"; +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"; @@ -517,6 +517,42 @@ export const periodicEvaluationsRelations2 = relations(periodicEvaluations, ({ o })); + +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 }) => ({ // 정기평가 @@ -748,27 +784,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q COALESCE( (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0 ) - ) >= 90 THEN 'S' - WHEN ( - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0 - ) + - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 - ) + - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 - ) + - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 - ) + - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0 - ) - - COALESCE( - (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0 - ) - ) >= 80 THEN 'A' + ) >= 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 @@ -788,7 +804,7 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q COALESCE( (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0 ) - ) >= 70 THEN 'B' + ) >= 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 -- cgit v1.2.3