(대표님) 20250710 작업사항 - 평가 첨부, 로그인, SEDP 변경 요구사항 반영

2 files changed, 41 insertions, 23 deletions

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
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index 0e6c4d66..e5773fc1 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -95,6 +95,8 @@ export const vendorContacts = pgTable("vendor_contacts", {
   vendorId: integer("vendor_id").notNull().references(() => vendors.id),
   contactName: varchar("contact_name", { length: 255 }).notNull(),
   contactPosition: varchar("contact_position", { length: 100 }),
+  contactDepartment: varchar("contact_department", { length: 100 }),
+  contactTask: varchar("contact_task", { length: 100 }),
   contactEmail: varchar("contact_email", { length: 255 }).notNull(),
   contactPhone: varchar("contact_phone", { length: 50 }),
   isPrimary: boolean("is_primary").default(false).notNull(),