summaryrefslogtreecommitdiff
path: root/db/schema/vendorDocu.ts
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema/vendorDocu.ts')
-rw-r--r--db/schema/vendorDocu.ts303
1 files changed, 196 insertions, 107 deletions
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 8ab86160..642657e2 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -1,6 +1,8 @@
// enhanced-schema.ts
-import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,
- index, text } from "drizzle-orm/pg-core"
+import {
+ pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,json,
+ index, text
+} from "drizzle-orm/pg-core"
import { eq, sql } from "drizzle-orm";
import { projects } from "./projects";
import { vendors } from "./vendors";
@@ -12,10 +14,13 @@ export const documents = pgTable(
{
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
pic: varchar("pic", { length: 50 }),
+ projectId: integer("project_id")
+ // .notNull()
+ .references(() => projects.id, { onDelete: "cascade" }),
contractId: integer("contract_id")
- .notNull()
+ // .notNull()
.references(() => contracts.id, { onDelete: "cascade" }),
-
+
// 기본 문서 정보
docNumber: varchar("doc_number", { length: 100 }).notNull(),
vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
@@ -24,17 +29,17 @@ export const documents = pgTable(
.notNull()
.default("ACTIVE"),
issuedDate: date("issued_date"),
-
+
// ✅ DOLCE 연동을 위한 새로운 필드들
drawingKind: varchar("drawing_kind", { length: 10 }), // B3, B4, B5
drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), // 도면입수, 도면제출, GTT Deliverable, SHI Input Information
discipline: varchar("discipline", { length: 10 }), // DE, ME, etc.
-
+
// ✅ 외부 시스템 연동 정보
externalDocumentId: varchar("external_document_id", { length: 100 }), // DOLCE 시스템의 문서 ID
externalSystemType: varchar("external_system_type", { length: 20 }), // DOLCE, SWP
externalSyncedAt: timestamp("external_synced_at"), // 마지막 동기화 시간
-
+
// ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
cGbn: varchar("c_gbn", { length: 50 }), // CGbn
dGbn: varchar("d_gbn", { length: 50 }), // DGbn
@@ -42,7 +47,7 @@ export const documents = pgTable(
deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
jGbn: varchar("j_gbn", { length: 50 }), // JGbn
sGbn: varchar("s_gbn", { length: 50 }), // SGbn
-
+
// ✅ DOLCE 응답의 추가 정보들
shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), // SHI 도면 번호
manager: varchar("manager", { length: 100 }), // 담당자
@@ -50,12 +55,12 @@ export const documents = pgTable(
managerNo: varchar("manager_no", { length: 50 }), // 담당자 번호
registerGroup: integer("register_group"), // 등록 그룹
registerGroupId: integer("register_group_id"), // 등록 그룹 ID
-
+
// ✅ 생성자 정보 (DOLCE에서 가져온 정보)
createUserNo: varchar("create_user_no", { length: 50 }), // 생성자 번호
createUserId: varchar("create_user_id", { length: 100 }), // 생성자 ID (한글명)
createUserENM: varchar("create_user_enm", { length: 100 }), // 생성자 영문명
-
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
@@ -70,14 +75,32 @@ export const documents = pgTable(
table.contractId,
table.vendorDocNumber
).where(sql`${table.vendorDocNumber} IS NOT NULL`),
-
+
// ✅ 외부 시스템 문서 ID 유니크 인덱스
uniqueExternalDoc: uniqueIndex("unique_external_doc").on(
table.contractId,
table.externalDocumentId,
table.externalSystemType
).where(sql`${table.externalDocumentId} IS NOT NULL`),
-
+
+
+ uniqueProjectDocStatus: uniqueIndex("unique_project_doc_status").on(
+ table.projectId,
+ table.docNumber,
+ table.status
+ ),
+ uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on(
+ table.projectId,
+ table.vendorDocNumber
+ ).where(sql`${table.vendorDocNumber} IS NOT NULL`),
+
+ // ✅ 외부 시스템 문서 ID 유니크 인덱스
+ uniqueExternalDocProject: uniqueIndex("unique_external_doc_project").on(
+ table.projectId,
+ table.externalDocumentId,
+ table.externalSystemType
+ ).where(sql`${table.externalDocumentId} IS NOT NULL`),
+
// ✅ drawingKind 인덱스 (자주 검색될 것 같음)
drawingKindIndex: index("drawing_kind_idx").on(table.drawingKind),
}
@@ -93,27 +116,27 @@ export const issueStages = pgTable(
.notNull()
.references(() => documents.id, { onDelete: "cascade" }),
stageName: varchar("stage_name", { length: 100 }).notNull(),
-
+
// 기존 필드
planDate: date("plan_date"),
actualDate: date("actual_date"),
-
+
// 새로운 스케줄 관리 필드들
stageStatus: varchar("stage_status", { length: 50 })
.notNull()
.default("PLANNED"), // PLANNED, IN_PROGRESS, SUBMITTED, APPROVED, REJECTED, COMPLETED
stageOrder: integer("stage_order").default(0),
priority: varchar("priority", { length: 20 }).default("MEDIUM"), // HIGH, MEDIUM, LOW
-
+
// 담당자 정보
assigneeId: integer("assignee_id"),
assigneeName: varchar("assignee_name", { length: 100 }),
-
+
// 알림 및 추가 정보
reminderDays: integer("reminder_days").default(3),
description: varchar("description", { length: 500 }),
notes: varchar("notes", { length: 1000 }),
-
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
@@ -142,27 +165,27 @@ export const revisions = pgTable(
uploaderId: integer("uploader_id"),
uploaderName: varchar("uploader_name", { length: 100 }),
- usage:varchar("usage", { length: 100 }),
- usageType:varchar("usage_type", { length: 255 }),
-
+ usage: varchar("usage", { length: 100 }),
+ usageType: varchar("usage_type", { length: 255 }),
+
// 확장된 상태 관리
revisionStatus: varchar("revision_status", { length: 50 })
.notNull()
.default("SUBMITTED"), // SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, SUPERSEDED
-
+
// 상세 날짜 추적
submittedDate: date("submitted_date"),
uploadedAt: date("uploaded_at"),
reviewStartDate: date("review_start_date"),
approvedDate: date("approved_date"),
rejectedDate: date("rejected_date"),
-
+
// 검토자 정보
reviewerId: integer("reviewer_id"),
reviewerName: varchar("reviewer_name", { length: 100 }),
reviewComments: varchar("review_comments", { length: 1000 }),
externalUploadId: varchar("external_upload_id", { length: 255 }),
-
+
comment: varchar("comment", { length: 500 }),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
@@ -178,7 +201,7 @@ export const revisions = pgTable(
table.usage,
sql`COALESCE(${table.usageType}, '')`
),
-
+
}
}
)
@@ -196,7 +219,7 @@ export const documentAttachments = pgTable(
filePath: varchar("file_path", { length: 1024 }).notNull(),
fileType: varchar("file_type", { length: 50 }),
fileSize: integer("file_size"),
-
+
// DOLCE 연동 관련 필드 추가
uploadId: varchar("upload_id", { length: 36 }), // UUID 형태
fileId: varchar("file_id", { length: 36 }), // UUID 형태
@@ -221,21 +244,21 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
status: varchar("status", { length: 50 }).notNull(),
issuedDate: date("issued_date"),
contractId: integer("contract_id").notNull(),
-
+
// ✅ 프로젝트 및 벤더 정보 추가
projectCode: varchar("project_code", { length: 50 }),
vendorName: varchar("vendor_name", { length: 255 }),
vendorCode: varchar("vendor_code", { length: 50 }),
vendorId: varchar("vendor_id", { length: 50 }),
- // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
- cGbn: varchar("c_gbn", { length: 50 }), // CGbn
- dGbn: varchar("d_gbn", { length: 50 }), // DGbn
- degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
- deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
- jGbn: varchar("j_gbn", { length: 50 }), // JGbn
- sGbn: varchar("s_gbn", { length: 50 }), // SGbn
-
+ // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
+ cGbn: varchar("c_gbn", { length: 50 }), // CGbn
+ dGbn: varchar("d_gbn", { length: 50 }), // DGbn
+ degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
+ deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
+ jGbn: varchar("j_gbn", { length: 50 }), // JGbn
+ sGbn: varchar("s_gbn", { length: 50 }), // SGbn
+
// 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
currentStageId: integer("current_stage_id"),
currentStageName: varchar("current_stage_name", { length: 100 }),
@@ -245,24 +268,24 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
currentStageActualDate: date("current_stage_actual_date"),
currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }),
currentStagePriority: varchar("current_stage_priority", { length: 20 }),
-
+
// 계산 필드
daysUntilDue: integer("days_until_due"),
isOverdue: boolean("is_overdue"),
daysDifference: integer("days_difference"),
-
+
// 전체 진행률
totalStages: integer("total_stages"),
completedStages: integer("completed_stages"),
progressPercentage: integer("progress_percentage"),
-
+
// 최신 리비전 정보
latestRevisionId: integer("latest_revision_id"),
latestRevision: varchar("latest_revision", { length: 50 }),
latestRevisionStatus: varchar("latest_revision_status", { length: 50 }),
latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }),
latestSubmittedDate: date("latest_submitted_date"),
-
+
// 전체 스테이지 목록 (리비전 및 첨부파일 포함)
allStages: jsonb("all_stages").$type<Array<{
id: number;
@@ -305,7 +328,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
}>;
}>;
}>>(),
-
+
// 메타 정보
attachmentCount: integer("attachment_count"),
createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
@@ -623,9 +646,8 @@ export const syncConfigs = pgTable(
"sync_configs",
{
id: serial("id").primaryKey(),
- contractId: integer("contract_id")
- .notNull()
- .references(() => contracts.id, { onDelete: "cascade" }),
+ projectId: integer("project_id").notNull(),
+
targetSystem: varchar("target_system", { length: 50 }).notNull(), // 'SHI', 'SAP' 등
syncEnabled: boolean("sync_enabled").default(true),
syncIntervalMinutes: integer("sync_interval_minutes").default(30), // 30분마다
@@ -642,7 +664,7 @@ export const syncConfigs = pgTable(
(table) => {
return {
contractSystemIdx: index("idx_sync_configs_contract_system").on(
- table.contractId,
+ table.projectId,
table.targetSystem
),
}
@@ -654,7 +676,8 @@ export const changeLogs = pgTable(
"change_logs",
{
id: serial("id").primaryKey(),
- contractId: integer("contract_id").notNull(),
+ projectId: integer("project_id").notNull(),
+
entityType: varchar("entity_type", { length: 50 }).notNull(), // 'document', 'revision', 'attachment'
entityId: integer("entity_id").notNull(),
action: varchar("action", { length: 20 }).notNull(), // 'CREATE', 'UPDATE', 'DELETE'
@@ -672,8 +695,8 @@ export const changeLogs = pgTable(
},
(table) => {
return {
- contractSyncedIdx: index("idx_change_logs_contract_synced").on(
- table.contractId,
+ projectSyncedIdx: index("idx_change_logs_project_synced").on(
+ table.projectId,
table.isSynced
),
createdAtIdx: index("idx_change_logs_created_at").on(table.createdAt),
@@ -688,7 +711,8 @@ export const syncBatches = pgTable(
"sync_batches",
{
id: serial("id").primaryKey(),
- contractId: integer("contract_id").notNull(),
+ projectId: integer("project_id").notNull(),
+
targetSystem: varchar("target_system", { length: 50 }).notNull(),
batchSize: integer("batch_size").notNull(),
status: varchar("status", { length: 20 }).notNull().default("PENDING"), // 'PENDING', 'PROCESSING', 'SUCCESS', 'FAILED', 'PARTIAL'
@@ -705,8 +729,8 @@ export const syncBatches = pgTable(
},
(table) => {
return {
- contractSystemIdx: index("idx_sync_batches_contract_system").on(
- table.contractId,
+ projectSystemIdx: index("idx_sync_batches_project_system").on(
+ table.projectId,
table.targetSystem
),
statusIdx: index("idx_sync_batches_status").on(table.status),
@@ -717,7 +741,7 @@ export const syncBatches = pgTable(
// 동기화 상태 추적을 위한 뷰
export const syncStatusView = pgView("sync_status_view", {
- contractId: integer("contract_id").notNull(),
+ projectId: integer("project_id").notNull(),
targetSystem: varchar("target_system", { length: 50 }).notNull(),
totalChanges: integer("total_changes").notNull(),
pendingChanges: integer("pending_changes").notNull(),
@@ -729,7 +753,7 @@ export const syncStatusView = pgView("sync_status_view", {
}).as(sql`
WITH change_stats AS (
SELECT
- cl.contract_id,
+ cl.project_id,
sc.target_system,
COUNT(*) as total_changes,
COUNT(CASE WHEN cl.is_synced = false AND cl.sync_attempts < sc.retry_max_attempts THEN 1 END) as pending_changes,
@@ -738,12 +762,12 @@ export const syncStatusView = pgView("sync_status_view", {
MAX(cl.synced_at) as last_sync_at
FROM change_logs cl
CROSS JOIN sync_configs sc
- WHERE cl.contract_id = sc.contract_id
- AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(sc.target_system))
- GROUP BY cl.contract_id, sc.target_system
+ WHERE cl.project_id = sc.project_id
+ AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(ARRAY[sc.target_system]))
+ GROUP BY cl.project_id, sc.target_system
)
SELECT
- cs.contract_id,
+ cs.project_id,
cs.target_system,
COALESCE(cs.total_changes, 0) as total_changes,
COALESCE(cs.pending_changes, 0) as pending_changes,
@@ -757,7 +781,7 @@ export const syncStatusView = pgView("sync_status_view", {
END as next_sync_at,
sc.sync_enabled
FROM sync_configs sc
- LEFT JOIN change_stats cs ON sc.contract_id = cs.contract_id AND sc.target_system = cs.target_system
+ LEFT JOIN change_stats cs ON sc.project_id = cs.project_id AND sc.target_system = cs.target_system
`)
// 타입 추출
@@ -773,15 +797,36 @@ export type SyncStatusView = typeof syncStatusView.$inferSelect
export const simplifiedDocumentsView = pgView("simplified_documents_view", {
// 기본 문서 정보
documentId: integer("document_id").notNull(),
+ projectId: integer("project_id"),
docNumber: varchar("doc_number", { length: 100 }).notNull(),
- drawingKind: varchar("drawing_kind", { length: 50 }),
+ drawingKind: varchar("drawing_kind", { length: 10 }),
+ drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }),
+ discipline: varchar("discipline", { length: 10 }),
vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
title: varchar("title", { length: 255 }).notNull(),
pic: varchar("pic", { length: 50 }),
status: varchar("status", { length: 50 }).notNull(),
issuedDate: date("issued_date"),
- contractId: integer("contract_id").notNull(),
-
+ contractId: integer("contract_id"),
+
+ // 외부 시스템 연동 정보
+ externalDocumentId: varchar("external_document_id", { length: 100 }),
+ externalSystemType: varchar("external_system_type", { length: 20 }),
+ externalSyncedAt: timestamp("external_synced_at"),
+
+ // DOLCE 응답의 추가 정보들
+ shiDrawingNo: varchar("shi_drawing_no", { length: 100 }),
+ manager: varchar("manager", { length: 100 }),
+ managerENM: varchar("manager_enm", { length: 100 }),
+ managerNo: varchar("manager_no", { length: 50 }),
+ registerGroup: integer("register_group"),
+ registerGroupId: integer("register_group_id"),
+
+ // 생성자 정보
+ createUserNo: varchar("create_user_no", { length: 50 }),
+ createUserId: varchar("create_user_id", { length: 100 }),
+ createUserENM: varchar("create_user_enm", { length: 100 }),
+
// 프로젝트 및 벤더 정보
projectCode: varchar("project_code", { length: 50 }),
vendorName: varchar("vendor_name", { length: 255 }),
@@ -794,21 +839,21 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
deptGbn: varchar("dept_gbn", { length: 50 }),
jGbn: varchar("j_gbn", { length: 50 }),
sGbn: varchar("s_gbn", { length: 50 }),
-
+
// 첫 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
firstStageId: integer("first_stage_id"),
firstStageName: varchar("first_stage_name", { length: 100 }),
firstStagePlanDate: date("first_stage_plan_date"),
firstStageActualDate: date("first_stage_actual_date"),
-
+
// 두 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
secondStageId: integer("second_stage_id"),
secondStageName: varchar("second_stage_name", { length: 100 }),
secondStagePlanDate: date("second_stage_plan_date"),
secondStageActualDate: date("second_stage_actual_date"),
-
- // 전체 스테이지 목록 (기존과 동일)
- allStages: jsonb("all_stages").$type<Array<{
+
+ // 전체 스테이지 목록
+ allStages: json("all_stages").$type<Array<{
id: number;
stageName: string;
stageStatus: string;
@@ -850,7 +895,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
}>;
}>;
}>>(),
-
+
// 메타 정보
attachmentCount: integer("attachment_count"),
createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
@@ -880,6 +925,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
LEFT JOIN document_attachments da ON r.id = da.revision_id
GROUP BY r.id
),
+
-- 스테이지별 리비전 집계 (첨부파일 포함)
stage_revisions AS (
SELECT
@@ -907,7 +953,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
'reviewComments', r.review_comments,
'createdAt', r.created_at,
'updatedAt', r.updated_at,
- 'attachments', ra.attachments
+ 'attachments', COALESCE(ra.attachments, '[]'::json)
) ORDER BY r.created_at
) FILTER (WHERE r.id IS NOT NULL),
'[]'::json
@@ -917,6 +963,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
LEFT JOIN revision_attachments ra ON r.id = ra.revision_id
GROUP BY ist.id
),
+
-- 문서별 스테이지 집계 (리비전 포함)
stage_aggregation AS (
SELECT
@@ -931,45 +978,66 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
'actualDate', ist.actual_date,
'assigneeName', ist.assignee_name,
'priority', ist.priority,
- 'revisions', sr.revisions
+ 'revisions', COALESCE(sr.revisions, '[]'::json)
) ORDER BY ist.stage_order
) as all_stages
FROM issue_stages ist
LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id
GROUP BY ist.document_id
),
+
-- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
first_stage_info AS (
- SELECT DISTINCT ON (ist.document_id)
- ist.document_id,
- ist.id as first_stage_id,
- ist.stage_name as first_stage_name,
- ist.plan_date as first_stage_plan_date,
- ist.actual_date as first_stage_actual_date
- FROM issue_stages ist
- JOIN documents d ON ist.document_id = d.id
- WHERE
- (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR
- (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR
- (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%')
- ORDER BY ist.document_id, ist.stage_order ASC
+ SELECT
+ document_id,
+ first_stage_id,
+ first_stage_name,
+ first_stage_plan_date,
+ first_stage_actual_date
+ FROM (
+ SELECT
+ ist.document_id,
+ ist.id as first_stage_id,
+ ist.stage_name as first_stage_name,
+ ist.plan_date as first_stage_plan_date,
+ ist.actual_date as first_stage_actual_date,
+ ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
+ FROM issue_stages ist
+ JOIN documents d ON ist.document_id = d.id
+ WHERE
+ (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR
+ (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR
+ (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%')
+ ) ranked
+ WHERE rn = 1
),
+
-- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
second_stage_info AS (
- SELECT DISTINCT ON (ist.document_id)
- ist.document_id,
- ist.id as second_stage_id,
- ist.stage_name as second_stage_name,
- ist.plan_date as second_stage_plan_date,
- ist.actual_date as second_stage_actual_date
- FROM issue_stages ist
- JOIN documents d ON ist.document_id = d.id
- WHERE
- (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR
- (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR
- (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%')
- ORDER BY ist.document_id, ist.stage_order ASC
+ SELECT
+ document_id,
+ second_stage_id,
+ second_stage_name,
+ second_stage_plan_date,
+ second_stage_actual_date
+ FROM (
+ SELECT
+ ist.document_id,
+ ist.id as second_stage_id,
+ ist.stage_name as second_stage_name,
+ ist.plan_date as second_stage_plan_date,
+ ist.actual_date as second_stage_actual_date,
+ ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
+ FROM issue_stages ist
+ JOIN documents d ON ist.document_id = d.id
+ WHERE
+ (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR
+ (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR
+ (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%')
+ ) ranked
+ WHERE rn = 1
),
+
-- 첨부파일 수 집계
attachment_counts AS (
SELECT
@@ -983,8 +1051,11 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
SELECT
d.id as document_id,
+ d.project_id,
d.doc_number,
d.drawing_kind,
+ d.drawing_move_gbn,
+ d.discipline,
d.vendor_doc_number,
d.title,
d.pic,
@@ -992,6 +1063,29 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
d.issued_date,
d.contract_id,
+ -- 외부 시스템 연동 정보
+ d.external_document_id,
+ d.external_system_type,
+ d.external_synced_at,
+
+ -- DOLCE 응답의 추가 정보들
+ d.shi_drawing_no,
+ d.manager,
+ d.manager_enm,
+ d.manager_no,
+ d.register_group,
+ d.register_group_id,
+
+ -- 생성자 정보
+ d.create_user_no,
+ d.create_user_id,
+ d.create_user_enm,
+
+ -- 프로젝트 및 벤더 정보
+ p.code as project_code,
+ v.vendor_name,
+ v.vendor_code,
+
-- B4 전용 필드들
d.c_gbn,
d.d_gbn,
@@ -1000,11 +1094,6 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
d.s_gbn,
d.j_gbn,
- -- 프로젝트 및 벤더 정보
- p.code as project_code,
- v.vendor_name as vendor_name,
- v.vendor_code as vendor_code,
-
-- 첫 번째 스테이지 정보
fsi.first_stage_id,
fsi.first_stage_name,
@@ -1026,9 +1115,9 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
d.updated_at
FROM documents d
- -- contracts, projects, vendors 테이블 JOIN
+ -- projects, vendors 테이블 JOIN (projectId가 이제 documents에 직접 있음)
+ LEFT JOIN projects p ON d.project_id = p.id AND p.type = 'ship'
LEFT JOIN contracts c ON d.contract_id = c.id
- INNER JOIN projects p ON c.project_id = p.id AND p.type = 'ship'
LEFT JOIN vendors v ON c.vendor_id = v.id
-- 스테이지 정보 JOIN
@@ -1055,15 +1144,15 @@ export const documentStagesOnlyView = pgView("document_stages_only_view", {
status: varchar("status", { length: 50 }).notNull(),
issuedDate: date("issued_date"),
contractId: integer("contract_id").notNull(),
-
-
+
+
// 프로젝트 및 벤더 정보
projectCode: varchar("project_code", { length: 50 }),
vendorName: varchar("vendor_name", { length: 255 }),
vendorCode: varchar("vendor_code", { length: 50 }),
vendorId: varchar("vendor_id", { length: 50 }),
-
+
// 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
currentStageId: integer("current_stage_id"),
currentStageName: varchar("current_stage_name", { length: 100 }),
@@ -1073,17 +1162,17 @@ export const documentStagesOnlyView = pgView("document_stages_only_view", {
currentStageActualDate: date("current_stage_actual_date"),
currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }),
currentStagePriority: varchar("current_stage_priority", { length: 20 }),
-
+
// 계산 필드
daysUntilDue: integer("days_until_due"),
isOverdue: boolean("is_overdue"),
daysDifference: integer("days_difference"),
-
+
// 전체 진행률
totalStages: integer("total_stages"),
completedStages: integer("completed_stages"),
progressPercentage: integer("progress_percentage"),
-
+
// 전체 스테이지 목록 (리비전 및 첨부파일 제외)
allStages: jsonb("all_stages").$type<Array<{
id: number;
@@ -1098,7 +1187,7 @@ export const documentStagesOnlyView = pgView("document_stages_only_view", {
notes: string | null;
reminderDays: number | null;
}>>(),
-
+
// 메타 정보
createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 03:43:33 +0000