From 2650b7c0bb0ea12b68a58c0439f72d61df04b2f1 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 25 Jul 2025 07:51:15 +0000 Subject: (대표님) 정기평가 대상, 미들웨어 수정, nextauth 토큰 처리 개선, GTC 등 (최겸) 기술영업 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/basicContractDocumnet.ts | 4 +- db/schema/evaluationTarget.ts | 22 +++-- db/schema/gtc.ts | 174 ++++++++++++++++++++++++++++++++++++- db/schema/index.ts | 1 + db/schema/techVendors.ts | 105 +++------------------- 5 files changed, 200 insertions(+), 106 deletions(-) (limited to 'db/schema') diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts index 33530ca9..25069942 100644 --- a/db/schema/basicContractDocumnet.ts +++ b/db/schema/basicContractDocumnet.ts @@ -9,8 +9,8 @@ export const basicContractTemplates = pgTable('basic_contract_templates', { templateName: text('template_name').notNull(), revision: integer('revision').notNull().default(1), // 리비전 status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED - fileName: varchar("file_name", { length: 255 }).notNull(), - filePath: varchar("file_path", { length: 1024 }).notNull(), + fileName: varchar("file_name", { length: 255 }), + filePath: varchar("file_path", { length: 1024 }), validityPeriod: integer('validity_period'), // 계약 유효기간(개월) // 법무검토 관련 diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts index c8011e96..f55cacc4 100644 --- a/db/schema/evaluationTarget.ts +++ b/db/schema/evaluationTarget.ts @@ -124,7 +124,7 @@ export const evaluationTargetReviews = pgTable("evaluation_target_reviews", { }, (table) => ({ // 같은 평가대상에 같은 담당자는 한 번만 검토 uniqueTargetReviewer: unique("unique_target_reviewer") - .on(table.evaluationTargetId, table.reviewerUserId), + .on(table.evaluationTargetId, table.reviewerUserId, table.departmentCode), })); @@ -321,7 +321,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ ) .leftJoin( sql`evaluation_target_reviews order_review`, - sql`${evaluationTargets.id} = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id` + sql`${evaluationTargets.id} = order_review.evaluation_target_id + AND order_review.reviewer_user_id = order_reviewer.id + AND order_review.department_code = 'ORDER_EVAL'` ) // 조달 평가 담당자 JOIN @@ -335,7 +337,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ ) .leftJoin( sql`evaluation_target_reviews procurement_review`, - sql`${evaluationTargets.id} = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id` + sql`${evaluationTargets.id} = procurement_review.evaluation_target_id + AND procurement_review.reviewer_user_id = procurement_reviewer.id + AND procurement_review.department_code = 'PROCUREMENT_EVAL'` ) // 품질 평가 담당자 JOIN @@ -349,7 +353,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ ) .leftJoin( sql`evaluation_target_reviews quality_review`, - sql`${evaluationTargets.id} = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id` + sql`${evaluationTargets.id} = quality_review.evaluation_target_id + AND quality_review.reviewer_user_id = quality_reviewer.id + AND quality_review.department_code = 'QUALITY_EVAL'` ) // 설계 평가 담당자 JOIN @@ -363,7 +369,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ ) .leftJoin( sql`evaluation_target_reviews design_review`, - sql`${evaluationTargets.id} = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id` + sql`${evaluationTargets.id} = design_review.evaluation_target_id + AND design_review.reviewer_user_id = design_reviewer.id + AND design_review.department_code = 'DESIGN_EVAL'` ) // CS 평가 담당자 JOIN @@ -377,7 +385,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_ ) .leftJoin( sql`evaluation_target_reviews cs_review`, - sql`${evaluationTargets.id} = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id` + sql`${evaluationTargets.id} = cs_review.evaluation_target_id + AND cs_review.reviewer_user_id = cs_reviewer.id + AND cs_review.department_code = 'CS_EVAL'` ); }); diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts index a4052d61..281b6af9 100644 --- a/db/schema/gtc.ts +++ b/db/schema/gtc.ts @@ -8,9 +8,9 @@ import { pgEnum, boolean, index, - uniqueIndex + uniqueIndex, pgView } from "drizzle-orm/pg-core" - import { relations } from "drizzle-orm" + import { relations ,sql, eq} from "drizzle-orm" import { projects } from "./projects" import { users } from "./users" @@ -107,4 +107,172 @@ import { users } from "./users" id: number name: string } - } \ No newline at end of file + } + + + export const gtcDocumentsView = pgView('gtc_documents_view').as((qb) => + qb + .select({ + // GTC 문서 기본 필드 + id: gtcDocuments.id, + type: gtcDocuments.type, + projectId: gtcDocuments.projectId, + revision: gtcDocuments.revision, + fileName: gtcDocuments.fileName, + filePath: gtcDocuments.filePath, + fileSize: gtcDocuments.fileSize, + createdAt: gtcDocuments.createdAt, + createdById: gtcDocuments.createdById, + updatedAt: gtcDocuments.updatedAt, + updatedById: gtcDocuments.updatedById, + editReason: gtcDocuments.editReason, + isActive: gtcDocuments.isActive, + + // 프로젝트 정보 + projectCode: projects.code, + projectName: projects.name, + + // 생성자 정보 + createdByName: sql`created_by_user.name`.as('created_by_name'), + createdByEmail: sql`created_by_user.email`.as('created_by_email'), + + // 수정자 정보 + updatedByName: sql`updated_by_user.name`.as('updated_by_name'), + updatedByEmail: sql`updated_by_user.email`.as('updated_by_email'), + + // 집계 필드들 + // 같은 타입/프로젝트의 총 문서 수 + totalDocumentsInGroup: sql` + ( + SELECT count(*) + FROM gtc_documents gd2 + WHERE gd2.type = ${gtcDocuments.type} + AND gd2.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd2.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd2.project_id IS NULL) + ) + ) + `.as('total_documents_in_group'), + + // 최신 리비전 번호 + latestRevision: sql` + ( + SELECT max(revision) + FROM gtc_documents gd3 + WHERE gd3.type = ${gtcDocuments.type} + AND gd3.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd3.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd3.project_id IS NULL) + ) + ) + `.as('latest_revision'), + + // 현재 문서가 최신인지 여부 + isLatestRevision: sql` + ${gtcDocuments.revision} = ( + SELECT max(revision) + FROM gtc_documents gd4 + WHERE gd4.type = ${gtcDocuments.type} + AND gd4.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd4.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd4.project_id IS NULL) + ) + ) + `.as('is_latest_revision'), + + // 이전 리비전 ID (있다면) + previousRevisionId: sql` + ( + SELECT id + FROM gtc_documents gd5 + WHERE gd5.type = ${gtcDocuments.type} + AND gd5.is_active = true + AND gd5.revision < ${gtcDocuments.revision} + AND ( + (${gtcDocuments.type} = 'project' AND gd5.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd5.project_id IS NULL) + ) + ORDER BY gd5.revision DESC + LIMIT 1 + ) + `.as('previous_revision_id'), + + // 다음 리비전 ID (있다면) + nextRevisionId: sql` + ( + SELECT id + FROM gtc_documents gd6 + WHERE gd6.type = ${gtcDocuments.type} + AND gd6.is_active = true + AND gd6.revision > ${gtcDocuments.revision} + AND ( + (${gtcDocuments.type} = 'project' AND gd6.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd6.project_id IS NULL) + ) + ORDER BY gd6.revision ASC + LIMIT 1 + ) + `.as('next_revision_id'), + + // 파일 크기 (사람이 읽기 쉬운 형태) + fileSizeFormatted: sql` + CASE + WHEN ${gtcDocuments.fileSize} IS NULL THEN NULL + WHEN ${gtcDocuments.fileSize} < 1024 THEN ${gtcDocuments.fileSize} || ' B' + WHEN ${gtcDocuments.fileSize} < 1024 * 1024 THEN round(${gtcDocuments.fileSize} / 1024.0, 1) || ' KB' + WHEN ${gtcDocuments.fileSize} < 1024 * 1024 * 1024 THEN round(${gtcDocuments.fileSize} / (1024.0 * 1024), 1) || ' MB' + ELSE round(${gtcDocuments.fileSize} / (1024.0 * 1024 * 1024), 1) || ' GB' + END + `.as('file_size_formatted'), + + // 프로젝트가 있는 경우, 해당 프로젝트의 총 GTC 문서 수 + projectTotalDocuments: sql` + CASE + WHEN ${gtcDocuments.projectId} IS NOT NULL THEN ( + SELECT count(*) + FROM gtc_documents gd7 + WHERE gd7.project_id = ${gtcDocuments.projectId} + AND gd7.is_active = true + ) + ELSE NULL + END + `.as('project_total_documents'), + + // 리비전 히스토리 (배열로 반환) + revisionHistory: sql` + ( + SELECT array_agg(revision ORDER BY revision) + FROM gtc_documents gd8 + WHERE gd8.type = ${gtcDocuments.type} + AND gd8.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd8.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd8.project_id IS NULL) + ) + ) + `.as('revision_history'), + + // 수정 이력이 있는지 여부 + hasEditHistory: sql` + ${gtcDocuments.createdById} != ${gtcDocuments.updatedById} OR + ${gtcDocuments.createdAt} != ${gtcDocuments.updatedAt} + `.as('has_edit_history'), + }) + .from(gtcDocuments) + .leftJoin(projects, sql`${gtcDocuments.projectId} = ${projects.id}`) + .leftJoin( + sql`users created_by_user`, + sql`${gtcDocuments.createdById} = created_by_user.id` + ) + .leftJoin( + sql`users updated_by_user`, + sql`${gtcDocuments.updatedById} = updated_by_user.id` + ) + ); + + export type GtcDocumentView = typeof gtcDocumentsView.$inferSelect; + + \ No newline at end of file diff --git a/db/schema/index.ts b/db/schema/index.ts index 19470fca..5c6aad41 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -29,6 +29,7 @@ export * from './notice'; export * from './history'; export * from './notification'; export * from './templates'; +export * from './gtc'; // 부서별 도메인 할당 관리 export * from './departmentDomainAssignments'; diff --git a/db/schema/techVendors.ts b/db/schema/techVendors.ts index e34d3e60..582ff724 100644 --- a/db/schema/techVendors.ts +++ b/db/schema/techVendors.ts @@ -1,6 +1,8 @@ import { pgTable, serial, varchar, text, timestamp, integer, boolean, pgView } from "drizzle-orm/pg-core"; -import { sql, eq } from "drizzle-orm"; -import { items } from "./items"; +import { sql } from "drizzle-orm"; +import { itemShipbuilding } from "./items"; +import { itemOffshoreTop } from "./items"; +import { itemOffshoreHull } from "./items"; // 벤더 타입 enum 정의 export const VENDOR_TYPES = ["조선", "해양TOP", "해양HULL"] as const; @@ -53,6 +55,7 @@ export const techVendorContacts = pgTable("tech_vendor_contacts", { vendorId: integer("vendor_id").notNull().references(() => techVendors.id), contactName: varchar("contact_name", { length: 255 }).notNull(), contactPosition: varchar("contact_position", { length: 100 }), + contactTitle: varchar("contact_title", { length: 100 }), contactEmail: varchar("contact_email", { length: 255 }).notNull(), contactPhone: varchar("contact_phone", { length: 50 }), contactCountry: varchar("contact_country", { length: 100 }), @@ -65,16 +68,9 @@ export const techVendorPossibleItems = pgTable("tech_vendor_possible_items", { // 벤더 정보 vendorId: integer("vendor_id").notNull().references(() => techVendors.id), - vendorCode: varchar("vendor_code", { length: 100 }), // 검색/필터용 중복 저장 - vendorEmail: varchar("vendor_email", { length: 255 }), // 검색/필터용 중복 저장 - - // 아이템 정보 (itemCode도 nullable로 변경 - 해양 HULL 등에서 없을 수 있음) - itemCode: varchar("item_code", { length: 100 }).notNull(), - workType: varchar("work_type", { length: 100 }), // 공종 (nullable) - shipTypes: varchar("ship_types", { length: 255 }), // 선종 (nullable) - itemList: varchar("item_list", { length: 500 }), // 아이템 리스트 (nullable) - subItemList: varchar("sub_item_list", { length: 500 }), // 서브 아이템 리스트 (nullable) - + shipbuildingItemId: integer("shipbuilding_item_id").references(() => itemShipbuilding.id), + offshoreTopItemId: integer("offshore_top_item_id").references(() => itemOffshoreTop.id), + offshoreHullItemId: integer("offshore_hull_item_id").references(() => itemOffshoreHull.id), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); @@ -90,19 +86,6 @@ export const techVendorAttachments = pgTable("tech_vendor_attachments", { createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); -// 뷰: 벤더 + 아이템 정보 조인 -export const techVendorItemsView = pgView("tech_vendor_items_view").as((qb) => { - return qb - .select({ - vendorItemId: techVendorPossibleItems.id, - vendorId: techVendorPossibleItems.vendorId, - itemCode: items.itemCode, - createdAt: techVendorPossibleItems.createdAt, - updatedAt: techVendorPossibleItems.updatedAt, - }) - .from(techVendorPossibleItems) - .leftJoin(items, eq(techVendorPossibleItems.itemCode, items.itemCode)); -}); // 벤더 상세 정보 뷰 (연락처 정보 포함) export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => { @@ -140,6 +123,7 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => 'id', c.id, 'contactName', c.contact_name, 'contactPosition', c.contact_position, + 'contactTitle', c.contact_title, 'contactEmail', c.contact_email, 'contactPhone', c.contact_phone, 'isPrimary', c.is_primary @@ -147,7 +131,7 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => ), '[]'::json ) - FROM vendor_contacts c + FROM tech_vendor_contacts c WHERE c.vendor_id = tech_vendors.id) `.as("contacts"), // 첨부파일 정보 (수정된 버전) @@ -181,21 +165,6 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => WHERE c.vendor_id = tech_vendors.id) `.as("contact_count"), - // 가능 아이템 목록 JSON - possibleItems: sql` - (SELECT COALESCE( - json_agg( - json_build_object( - 'itemCode', i.item_code - ) - ), - '[]'::json - ) - FROM tech_vendor_possible_items i - LEFT JOIN items it ON i.item_code = it.item_code - WHERE i.vendor_id = tech_vendors.id) - `.as("possible_items"), - // 아이템 수 itemCount: sql` (SELECT COUNT(*) @@ -207,65 +176,11 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => }); -export const techVendorCandidates = pgTable("tech_vendor_candidates", { - id: serial("id").primaryKey(), - companyName: varchar("company_name", { length: 255 }).notNull(), - contactEmail: varchar("contact_email", { length: 255 }), - contactPhone: varchar("contact_phone", { length: 50 }), - taxId: varchar("tax_id", { length: 100 }), - country: varchar("country", { length: 100 }), - status: varchar("status", { - length: 30, - enum: [ - "COLLECTED", // 단순 데이터 수집 완료 - "INVITED", // 초청 메일 발송 - "DISCARDED", // 불필요, 검토 후 배제됨 - ], - }) - .notNull() - .default("COLLECTED"), - remark: text("remark"), - vendorId: integer("vendor_id") - .references(() => techVendors.id, { onDelete: "cascade" }), - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), -}); -export const techVendorCandidatesWithVendorInfo = pgView("tech_vendor_candidates_with_vendor_info").as((qb) => { - return qb - .select({ - // ---------------------------------------- - // 1) techVendorCandidates 기본 필드 - id: techVendorCandidates.id, - companyName: techVendorCandidates.companyName, - contactEmail: techVendorCandidates.contactEmail, - contactPhone: techVendorCandidates.contactPhone, - taxId: techVendorCandidates.taxId, - country: techVendorCandidates.country, - status: techVendorCandidates.status, - remark: techVendorCandidates.remark, - createdAt: techVendorCandidates.createdAt, - updatedAt: techVendorCandidates.updatedAt, - - // ---------------------------------------- - // 2) techVendors 조인해서 가져올 필드 - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - vendorCreatedAt: sql`${techVendors.createdAt}`.as("vendor_created_at"), - - }) - .from(techVendorCandidates) - .leftJoin(techVendors, eq(techVendorCandidates.vendorId, techVendors.id)); -}); - -export type TechVendorCandidatesWithVendorInfo = typeof techVendorCandidatesWithVendorInfo.$inferSelect; - export type TechVendor = typeof techVendors.$inferSelect export type TechVendorContact = typeof techVendorContacts.$inferSelect export type TechVendorItem = typeof techVendorPossibleItems.$inferSelect export type TechVendorAttach = typeof techVendorAttachments.$inferSelect -export type TechVendorItemsView = typeof techVendorItemsView.$inferSelect export type TechVendorDetailView = typeof techVendorDetailView.$inferSelect -export type TechVendorCandidate = typeof techVendorCandidates.$inferSelect export type TechVendorWithAttachments = TechVendor & { hasAttachments?: boolean; attachmentsList?: TechVendorAttach[]; -- cgit v1.2.3