summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/basicContractDocumnet.ts4
-rw-r--r--db/schema/evaluationTarget.ts22
-rw-r--r--db/schema/gtc.ts174
-rw-r--r--db/schema/index.ts1
-rw-r--r--db/schema/techVendors.ts105
5 files changed, 200 insertions, 106 deletions
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<string>`created_by_user.name`.as('created_by_name'),
+ createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'),
+
+ // 수정자 정보
+ updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'),
+ updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'),
+
+ // 집계 필드들
+ // 같은 타입/프로젝트의 총 문서 수
+ totalDocumentsInGroup: sql<number>`
+ (
+ 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<number>`
+ (
+ 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<boolean>`
+ ${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<number>`
+ (
+ 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<number>`
+ (
+ 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<string>`
+ 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<number>`
+ 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<number[]>`
+ (
+ 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<boolean>`
+ ${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<string>`
- (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<number>`
(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<Date>`${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[];
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 08:37:13 +0000