diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/bRfq.ts | 132 | ||||
| -rw-r--r-- | db/schema/vendorDocu.ts | 273 |
2 files changed, 370 insertions, 35 deletions
diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts index 5e211448..115d2c6e 100644 --- a/db/schema/bRfq.ts +++ b/db/schema/bRfq.ts @@ -168,52 +168,114 @@ export const bRfqsAttachments = pgTable( "b_rfq_attachments", { id: serial("id").primaryKey(), - attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // '구매', '설계' + attachmentType: varchar("attachment_type", { length: 50 }).notNull(), serialNo: varchar("serial_no", { length: 50 }).notNull(), rfqId: integer("rfq_id") - .notNull() - .references(() => bRfqs.id), + .notNull() + .references(() => bRfqs.id), + + // 현재 리비전 정보 (빠른 접근용) + currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"), + latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions + + // 메타 정보 + description: varchar("description", { length: 500 }), + createdBy: integer("created_by") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + } + ) + + // 리비전 테이블 (모든 파일 버전 관리) + export const bRfqAttachmentRevisions = pgTable( + "b_rfq_attachment_revisions", + { + id: serial("id").primaryKey(), + attachmentId: integer("attachment_id") + .notNull() + .references(() => bRfqsAttachments.id, { onDelete: "cascade" }), + + // 리비전 정보 + revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2" + revisionComment: text("revision_comment"), + isLatest: boolean("is_latest").notNull().default(true), + + // 파일 정보 fileName: varchar("file_name", { length: 255 }).notNull(), originalFileName: varchar("original_file_name", { length: 255 }).notNull(), filePath: varchar("file_path", { length: 512 }).notNull(), fileSize: integer("file_size"), fileType: varchar("file_type", { length: 100 }), - description: varchar("description", { length: 500 }), + + // 리비전 생성 정보 createdBy: integer("created_by") .references(() => users.id, { onDelete: "set null" }) .notNull(), createdAt: timestamp("created_at").defaultNow().notNull(), - }, - ); -// 1. 첨부파일 리비전 관리 테이블 -export const bRfqAttachmentRevisions = pgTable( - "b_rfq_attachment_revisions", - { - id: serial("id").primaryKey(), - attachmentId: integer("attachment_id") - .notNull() - .references(() => bRfqsAttachments.id, { onDelete: "cascade" }), - - revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2" - fileName: varchar("file_name", { length: 255 }).notNull(), - originalFileName: varchar("original_file_name", { length: 255 }).notNull(), - filePath: varchar("file_path", { length: 512 }).notNull(), - fileSize: integer("file_size"), - fileType: varchar("file_type", { length: 100 }), - - isLatest: boolean("is_latest").notNull().default(true), - revisionComment: text("revision_comment"), - - createdBy: integer("created_by") - .references(() => users.id, { onDelete: "set null" }) - .notNull(), - createdAt: timestamp("created_at").defaultNow().notNull(), - }, (t) => ({ - latestRevisionIdx: uniqueIndex('latest_revision_idx').on( - t.attachmentId.asc(), - t.isLatest.asc(), - ), - })); + }, + (t) => ({ + // 첨부파일당 하나의 최신 리비전만 허용 + latestRevisionIdx: uniqueIndex('latest_revision_idx') + .on(t.attachmentId, t.isLatest) + .where(eq(t.isLatest, true)), + + // 첨부파일 + 리비전 번호 유니크 + attachmentRevisionIdx: uniqueIndex('attachment_revision_idx') + .on(t.attachmentId, t.revisionNo), + }) + ) + + // 첨부파일 + 최신 리비전 뷰 + export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", { + // 메인 첨부파일 정보 + attachmentId: integer("attachment_id"), + attachmentType: varchar("attachment_type", { length: 50 }), + serialNo: varchar("serial_no", { length: 50 }), + rfqId: integer("rfq_id"), + description: varchar("description", { length: 500 }), + currentRevision: varchar("current_revision", { length: 10 }), + + // 최신 리비전 파일 정보 + revisionId: integer("revision_id"), + fileName: varchar("file_name", { length: 255 }), + originalFileName: varchar("original_file_name", { length: 255 }), + filePath: varchar("file_path", { length: 512 }), + fileSize: integer("file_size"), + fileType: varchar("file_type", { length: 100 }), + revisionComment: text("revision_comment"), + + // 생성/수정 정보 + createdBy: integer("created_by"), + createdByName: varchar("created_by_name", { length: 255 }), + createdAt: timestamp("created_at"), + updatedAt: timestamp("updated_at"), + }).as(sql` + SELECT + a.id as attachment_id, + a.attachment_type, + a.serial_no, + a.rfq_id, + a.description, + a.current_revision, + + r.id as revision_id, + r.file_name, + r.original_file_name, + r.file_path, + r.file_size, + r.file_type, + r.revision_comment, + + a.created_by, + u.name as created_by_name, + a.created_at, + a.updated_at + FROM b_rfq_attachments a + LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id + LEFT JOIN users u ON a.created_by = u.id + `) // 2. 벤더별 첨부파일 응답 현황 관리 export const vendorAttachmentResponses = pgTable( diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index a3138fd5..af48d74b 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -467,6 +467,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", { d.degree_gbn, d.dept_gbn, d.s_gbn, + d.j_gbn, @@ -758,3 +759,275 @@ export type ChangeLogInsert = typeof changeLogs.$inferInsert export type SyncBatch = typeof syncBatches.$inferSelect export type SyncBatchInsert = typeof syncBatches.$inferInsert export type SyncStatusView = typeof syncStatusView.$inferSelect + + +export const simplifiedDocumentsView = pgView("simplified_documents_view", { + // 기본 문서 정보 + documentId: integer("document_id").notNull(), + docNumber: varchar("doc_number", { length: 100 }).notNull(), + drawingKind: varchar("drawing_kind", { length: 50 }), + 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(), + + // 프로젝트 및 벤더 정보 + projectCode: varchar("project_code", { length: 50 }), + vendorName: varchar("vendor_name", { length: 255 }), + vendorCode: varchar("vendor_code", { length: 50 }), + + // B4 전용 필드들 + cGbn: varchar("c_gbn", { length: 50 }), + dGbn: varchar("d_gbn", { length: 50 }), + degreeGbn: varchar("degree_gbn", { length: 50 }), + 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<{ + id: number; + stageName: string; + stageStatus: string; + stageOrder: number; + planDate: string | null; + actualDate: string | null; + assigneeName: string | null; + priority: string; + revisions: Array<{ + id: number; + issueStageId: number; + revision: string; + uploaderType: string; + uploaderId: number | null; + uploaderName: string | null; + comment: string | null; + usage: string | null; + revisionStatus: string; + submittedDate: string | null; + approvedDate: string | null; + uploadedAt: string | null; + reviewStartDate: string | null; + rejectedDate: string | null; + reviewerId: number | null; + reviewerName: string | null; + reviewComments: string | null; + createdAt: Date; + updatedAt: Date; + attachments: Array<{ + id: number; + revisionId: number; + fileName: string; + filePath: string; + fileSize: number | null; + fileType: string | null; + createdAt: Date; + updatedAt: Date; + }>; + }>; + }>>(), + + // 메타 정보 + attachmentCount: integer("attachment_count"), + createdAt: timestamp("created_at", { withTimezone: true }).notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), +}).as(sql` + WITH + -- 리비전별 첨부파일 집계 + revision_attachments AS ( + SELECT + r.id as revision_id, + COALESCE( + json_agg( + json_build_object( + 'id', da.id, + 'revisionId', da.revision_id, + 'fileName', da.file_name, + 'filePath', da.file_path, + 'fileSize', da.file_size, + 'fileType', da.file_type, + 'createdAt', da.created_at, + 'updatedAt', da.updated_at + ) ORDER BY da.created_at + ) FILTER (WHERE da.id IS NOT NULL), + '[]'::json + ) as attachments + FROM revisions r + LEFT JOIN document_attachments da ON r.id = da.revision_id + GROUP BY r.id + ), + -- 스테이지별 리비전 집계 (첨부파일 포함) + stage_revisions AS ( + SELECT + ist.id as stage_id, + COALESCE( + json_agg( + json_build_object( + 'id', r.id, + 'issueStageId', r.issue_stage_id, + 'revision', r.revision, + 'uploaderType', r.uploader_type, + 'uploaderId', r.uploader_id, + 'uploaderName', r.uploader_name, + 'comment', r.comment, + 'usage', r.usage, + 'revisionStatus', r.revision_status, + 'submittedDate', r.submitted_date, + 'uploadedAt', r.uploaded_at, + 'approvedDate', r.approved_date, + 'reviewStartDate', r.review_start_date, + 'rejectedDate', r.rejected_date, + 'reviewerId', r.reviewer_id, + 'reviewerName', r.reviewer_name, + 'reviewComments', r.review_comments, + 'createdAt', r.created_at, + 'updatedAt', r.updated_at, + 'attachments', ra.attachments + ) ORDER BY r.created_at + ) FILTER (WHERE r.id IS NOT NULL), + '[]'::json + ) as revisions + FROM issue_stages ist + LEFT JOIN revisions r ON ist.id = r.issue_stage_id + LEFT JOIN revision_attachments ra ON r.id = ra.revision_id + GROUP BY ist.id + ), + -- 문서별 스테이지 집계 (리비전 포함) + stage_aggregation AS ( + SELECT + ist.document_id, + json_agg( + json_build_object( + 'id', ist.id, + 'stageName', ist.stage_name, + 'stageStatus', ist.stage_status, + 'stageOrder', ist.stage_order, + 'planDate', ist.plan_date, + 'actualDate', ist.actual_date, + 'assigneeName', ist.assignee_name, + 'priority', ist.priority, + 'revisions', sr.revisions + ) 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 + ), + -- 두 번째 스테이지 정보 (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 + ), + -- 첨부파일 수 집계 + attachment_counts AS ( + SELECT + ist.document_id, + COUNT(da.id) as attachment_count + FROM issue_stages ist + LEFT JOIN revisions r ON ist.id = r.issue_stage_id + LEFT JOIN document_attachments da ON r.id = da.revision_id + GROUP BY ist.document_id + ) + + SELECT + d.id as document_id, + d.doc_number, + d.drawing_kind, + d.vendor_doc_number, + d.title, + d.pic, + d.status, + d.issued_date, + d.contract_id, + + -- B4 전용 필드들 + d.c_gbn, + d.d_gbn, + d.degree_gbn, + d.dept_gbn, + 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, + fsi.first_stage_plan_date, + fsi.first_stage_actual_date, + + -- 두 번째 스테이지 정보 + ssi.second_stage_id, + ssi.second_stage_name, + ssi.second_stage_plan_date, + ssi.second_stage_actual_date, + + -- 전체 스테이지 (리비전 및 첨부파일 포함) + COALESCE(sa.all_stages, '[]'::json) as all_stages, + + -- 기타 + COALESCE(ac.attachment_count, 0) as attachment_count, + d.created_at, + d.updated_at + + FROM documents d + -- contracts, projects, vendors 테이블 JOIN + LEFT JOIN contracts c ON d.contract_id = c.id + LEFT JOIN projects p ON c.project_id = p.id + LEFT JOIN vendors v ON c.vendor_id = v.id + + -- 스테이지 정보 JOIN + LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id + LEFT JOIN second_stage_info ssi ON d.id = ssi.document_id + LEFT JOIN stage_aggregation sa ON d.id = sa.document_id + LEFT JOIN attachment_counts ac ON d.id = ac.document_id + + ORDER BY d.created_at DESC +`); + +// 타입 추출 +export type SimplifiedDocumentsView = typeof simplifiedDocumentsView.$inferSelect |