From 4ee8b24cfadf47452807fa2af801385ed60ab47c Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 15 Sep 2025 14:41:01 +0000 Subject: (대표님) 작업사항 - rfqLast, tbeLast, pdfTron, userAuth MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/rfqLastTBE.ts | 211 +++++++++++++++++++++++++-------------------- db/schema/rfqVendor.ts | 224 +++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 340 insertions(+), 95 deletions(-) (limited to 'db') diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts index ba7e30b5..1efb43bb 100644 --- a/db/schema/rfqLastTBE.ts +++ b/db/schema/rfqLastTBE.ts @@ -40,7 +40,7 @@ export const rfqLastTbeSessions = pgTable( // 평가 결과 (단순화) evaluationResult: varchar("evaluation_result", { length: 30 }) - .$type<"pass" | "conditional_pass" | "non_pass" | null>(), + .$type<"Acceptable" | "Acceptable with Comment" | "Not Acceptable" | null>(), // 조건부 승인 시 조건 conditionalRequirements: text("conditional_requirements"), @@ -123,7 +123,7 @@ export const rfqLastTbeDocumentReviews = pgTable( // 벤더 문서인 경우 vendorAttachmentId: integer("vendor_attachment_id") - .references(() => rfqLastVendorAttachments.id, { onDelete: "cascade" }), + .references(() => rfqLastTbeVendorDocuments.id, { onDelete: "cascade" }), // 검토 정보 documentType: varchar("document_type", { length: 50 }), @@ -169,6 +169,7 @@ export const rfqLastTbeDocumentReviews = pgTable( // ========================================== // 3. PDFTron 코멘트 관리 // ========================================== +// 수정된 스키마 (버전 관리 제거) export const rfqLastTbePdftronComments = pgTable( "rfq_last_tbe_pdftron_comments", { @@ -177,65 +178,80 @@ export const rfqLastTbePdftronComments = pgTable( .notNull() .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "cascade" }), - // PDFTron 관련 정보 + // PDFTron 문서 식별자 pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }).notNull(), - pdftronAnnotationId: varchar("pdftron_annotation_id", { length: 255 }).notNull(), - annotationType: varchar("annotation_type", { length: 50 }), // highlight, note, drawing, etc. - // 위치 정보 - pageNumber: integer("page_number"), - xPosition: numeric("x_position", { precision: 10, scale: 4 }).$type(), - yPosition: numeric("y_position", { precision: 10, scale: 4 }).$type(), - coordinates: jsonb("coordinates"), // 복잡한 도형의 경우 - - // 코멘트 내용 - commentText: text("comment_text"), - commentCategory: varchar("comment_category", { length: 50 }) - .$type<"technical" | "commercial" | "quality" | "compliance" | "general">(), - - severity: varchar("severity", { length: 20 }) - .$type<"minor" | "major" | "critical">() - .default("minor"), - - // 상태 관리 - status: varchar("status", { length: 30 }) - .$type<"open" | "resolved" | "rejected" | "deferred">() - .default("open"), - - // 해결 정보 - resolvedBy: integer("resolved_by") - .references(() => users.id, { onDelete: "set null" }), - resolvedAt: timestamp("resolved_at", { withTimezone: true }).$type(), - resolutionNote: text("resolution_note"), - - // 답변 스레드 - replies: jsonb("replies").$type<{ - userId: number; - userName: string; - message: string; - createdAt: string; - }[]>(), + // XFDF XML 전체 저장 (모든 annotation 포함) + xfdfString: text("xfdf_string").notNull(), + + // 파싱된 annotation 데이터 (검색/필터링용) + annotationData: jsonb("annotation_data").$type<{ + annotations: { + id: string; + type: string; + page: number; + author: string; + subject: string; + contents?: string; + color?: string; + opacity?: number; + createdDate: string; + modifiedDate?: string; + customData?: { + category?: "technical" | "commercial" | "quality" | "compliance" | "general"; + severity?: "minor" | "major" | "critical"; + status?: "open" | "resolved" | "rejected" | "deferred"; + createdBy?: number; + createdByType?: "buyer" | "vendor"; + resolvedBy?: number; + resolvedAt?: string; + resolutionNote?: string; + }; + replies?: { + author: string; + contents: string; + createdDate: string; + }[]; + coords?: number[]; // 좌표 데이터 + }[]; + }>(), + + // 요약 정보 (빠른 조회용) + commentSummary: jsonb("comment_summary").$type<{ + total: number; + open: number; + resolved: number; + rejected: number; + deferred: number; + byCategory: Record; + bySeverity: Record; + byAuthor: Record; + }>(), // 작성자 정보 createdBy: integer("created_by") .notNull() - .references(() => users.id, { onDelete: "set null" }), + .references(() => users.id, { onDelete: "restrict" }), createdByType: varchar("created_by_type", { length: 20 }) .$type<"buyer" | "vendor">() .notNull(), + // 마지막 수정자 + lastModifiedBy: integer("last_modified_by") + .references(() => users.id, { onDelete: "set null" }), + createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => ({ documentReviewIdx: index("idx_pdftron_doc_review").on(table.documentReviewId), - statusIdx: index("idx_pdftron_status").on(table.status), - // PDFTron ID들에 대한 유니크 제약 - uniquePdftronAnnotation: uniqueIndex("unique_pdftron_annotation") - .on(table.pdftronDocumentId, table.pdftronAnnotationId), - }) -); + documentIdIdx: index("idx_pdftron_doc_id").on(table.pdftronDocumentId), + // documentReviewId와 pdftronDocumentId 조합 유니크 + uniqueDocument: uniqueIndex("unique_document") + .on(table.documentReviewId, table.pdftronDocumentId), + }) +) // ========================================== // 4. TBE 새로운 벤더 첨부파일 (TBE 중 추가 제출) // ========================================== @@ -478,6 +494,9 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => { series: sql`${rfqsLast.series}`.as("series"), rfqStatus: sql`${rfqsLast.status}`.as("rfq_status"), rfqDueDate: sql`${rfqsLast.dueDate}`.as("rfq_due_date"), + picName: sql`${rfqsLast.picName}`.as("pic_name"), + EngPicName: sql`${rfqsLast.EngPicName}`.as("eng_pic_name"), + // 패키지 정보 packageNo: sql`${rfqsLast.packageNo}`.as("package_no"), @@ -591,68 +610,74 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => { // TBE 문서 상세 뷰 (구매자 + 벤더 문서 통합) // ========================================== export const tbeDocumentsView = pgView("tbe_documents_view").as((qb) => { - const ba = alias(rfqLastAttachments, "ba"); - const baRev = alias(rfqLastAttachmentRevisions, "ba_rev"); + const dr = alias(rfqLastTbeDocumentReviews, "dr") + const ba = alias(rfqLastAttachments, "ba") + const baRev = alias(rfqLastAttachmentRevisions, "ba_rev") + const vd = alias(rfqLastTbeVendorDocuments, "vd") + return qb .select({ - // 문서 검토 ID - documentReviewId: sql`dr.id`.as("document_review_id"), - tbeSessionId: sql`COALESCE(dr.tbe_session_id, vd.tbe_session_id)`.as("tbe_session_id"), - - // 문서 구분 - documentSource: sql` - CASE - WHEN dr.id IS NOT NULL THEN dr.document_source - WHEN vd.id IS NOT NULL THEN 'vendor' - ELSE NULL - END - `.as("document_source"), - - // 문서 정보 - documentId: sql`COALESCE(dr.buyer_attachment_id, vd.id)`.as("document_id"), - documentType: sql`COALESCE(dr.document_type, vd.document_type)`.as("document_type"), - documentName: sql`COALESCE(dr.document_name, vd.file_name)`.as("document_name"), + // 기본키/세션 + documentReviewId: sql`dr.id`.as("document_review_id"), + tbeSessionId: sql`dr.tbe_session_id`.as("tbe_session_id"), + + // 소스 + documentSource: sql<"buyer" | "vendor">`dr.document_source`.as("document_source"), + + // 문서 식별자: buyer면 buyerAttachmentId, vendor면 vendorAttachmentId + documentId: sql` + CASE + WHEN dr.document_source = 'buyer' THEN dr.buyer_attachment_id + WHEN dr.document_source = 'vendor' THEN dr.vendor_attachment_id + ELSE NULL + END + `.as("document_id"), + + // 표시 정보 + documentType: sql`dr.document_type`.as("document_type"), + documentName: sql`dr.document_name`.as("document_name"), + + // 파일 메타: buyer면 ba_rev.*, vendor면 vd.* originalFileName: sql`COALESCE(ba_rev.original_file_name, vd.original_file_name)`.as("original_file_name"), - filePath: sql`COALESCE(ba_rev.file_path, vd.file_path)`.as("file_path"), - fileSize: sql`COALESCE(ba_rev.file_size, vd.file_size)`.as("file_size"), - fileType: sql`COALESCE(ba_rev.file_type, vd.file_type)`.as("file_type"), + filePath: sql`COALESCE(ba_rev.file_path, vd.file_path)`.as("file_path"), + fileSize: sql`COALESCE(ba_rev.file_size, vd.file_size)`.as("file_size"), + fileType: sql`COALESCE(ba_rev.file_type, vd.file_type)`.as("file_type"), - // 검토 상태 - reviewStatus: sql`COALESCE(dr.review_status, vd.review_status, '미검토')`.as("review_status"), + // 리뷰 상태/정보 (dr 기준) + reviewStatus: sql`dr.review_status`.as("review_status"), technicalCompliance: sql`dr.technical_compliance`.as("technical_compliance"), qualityAcceptable: sql`dr.quality_acceptable`.as("quality_acceptable"), requiresRevision: sql`COALESCE(dr.requires_revision, false)`.as("requires_revision"), - // PDFTron 관련 + // PDFTron hasPdftronComments: sql`COALESCE(dr.has_pdftron_comments, false)`.as("has_pdftron_comments"), pdftronDocumentId: sql`dr.pdftron_document_id`.as("pdftron_document_id"), pdftronAnnotationCount: sql`COALESCE(dr.pdftron_annotation_count, 0)`.as("pdftron_annotation_count"), - // 검토 정보 - reviewedBy: sql`COALESCE(dr.reviewed_by, vd.reviewed_by)`.as("reviewed_by"), - reviewedAt: sql`COALESCE(dr.reviewed_at, vd.reviewed_at)`.as("reviewed_at"), - reviewComments: sql`COALESCE(dr.review_comments, vd.review_comments)`.as("review_comments"), - - // 제출 정보 (벤더 문서인 경우) - submittedBy: sql`vd.submitted_by`.as("submitted_by"), - submittedAt: sql`vd.submitted_at`.as("submitted_at"), - - // 타임스탬프 - createdAt: sql`COALESCE(dr.created_at, vd.submitted_at)`.as("created_at"), - updatedAt: sql`COALESCE(dr.updated_at, vd.submitted_at)`.as("updated_at"), + // 검토자/타임스탬프 + reviewedBy: sql`dr.reviewed_by`.as("reviewed_by"), + reviewedAt: sql`dr.reviewed_at`.as("reviewed_at"), + reviewComments: sql`dr.review_comments`.as("review_comments"), + + // 제출 정보(벤더 문서일 때 vd의 제출 정보 노출, 아니면 null) + submittedBy: sql` + CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_by ELSE NULL END + `.as("submitted_by"), + submittedAt: sql` + CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_at ELSE NULL END + `.as("submitted_at"), + + // 생성/업데이트 시각: 리뷰기준 + createdAt: sql`dr.created_at`.as("created_at"), + updatedAt: sql`dr.updated_at`.as("updated_at"), }) - .from( - sql`( - SELECT * FROM rfq_last_tbe_document_reviews - ) dr - FULL OUTER JOIN ( - SELECT * FROM rfq_last_tbe_vendor_documents - ) vd ON false - ` - ) + .from(dr) + // buyer: 리뷰가 가리키는 첨부 개정에 조인 .leftJoin(ba, sql`dr.buyer_attachment_id = ${ba.id}`) - .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`); -}); + .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`) + // vendor: 리뷰가 가리키는 벤더 문서에 조인 + .leftJoin(vd, sql`dr.vendor_attachment_id = ${vd.id}`) +}) // Type exports export type TbeLastView = typeof tbeLastView.$inferSelect; diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts index 5752b1c2..0ddf109b 100644 --- a/db/schema/rfqVendor.ts +++ b/db/schema/rfqVendor.ts @@ -1,9 +1,10 @@ import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb } from "drizzle-orm/pg-core"; -import { eq, sql, relations } from "drizzle-orm"; +import { eq, sql, relations,and } from "drizzle-orm"; import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast"; import { users } from "./users"; import { vendors } from "./vendors"; import { incoterms, paymentTerms } from "./procurementRFQ"; +import { projects } from "./projects"; // ========================================== // 1. 벤더 응답 메인 테이블 (견적서 헤더) @@ -458,4 +459,223 @@ export const vendorQuotationItemsRelations = relations( export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect; export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect; export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect; -export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect; \ No newline at end of file +export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect; + + +// vendorQuotationView - 벤더별 견적 현황을 보여주는 통합 뷰 +export const vendorQuotationView = pgView("vendor_quotation_view").as((qb) => { + const createdByUser = alias(users, "created_by_user"); + const updatedByUser = alias(users, "updated_by_user"); + const sentByUser = alias(users, "sent_by_user"); + const picUser = alias(users, "pic_user"); + + return qb + .select({ + // ===== RFQ 기본 정보 (rfqsLastView에서 가져온 필드들) ===== + id: sql`${rfqsLast.id}`.as("id"), + rfqCode: sql`${rfqsLast.rfqCode}`.as("rfq_code"), + series: sql`${rfqsLast.series}`.as("series"), + rfqSealedYn: sql`${rfqsLast.rfqSealedYn}`.as("rfq_sealed_yn"), + + // RFQ 타입 정보 + rfqType: sql`${rfqsLast.rfqType}`.as("rfq_type"), + rfqTitle: sql`${rfqsLast.rfqTitle}`.as("rfq_title"), + + // ITB 관련 필드 + projectCompany: sql`${rfqsLast.projectCompany}`.as("project_company"), + projectFlag: sql`${rfqsLast.projectFlag}`.as("project_flag"), + projectSite: sql`${rfqsLast.projectSite}`.as("project_site"), + smCode: sql`${rfqsLast.smCode}`.as("sm_code"), + + // RFQ 추가 필드 + prNumber: sql`${rfqsLast.prNumber}`.as("pr_number"), + prIssueDate: sql`${rfqsLast.prIssueDate}`.as("pr_issue_date"), + + // 프로젝트 정보 + projectId: sql`${rfqsLast.projectId}`.as("project_id"), + projectCode: sql`${projects.code}`.as("project_code"), + projectName: sql`${projects.name}`.as("project_name"), + + // 아이템 정보 + itemCode: sql`${rfqsLast.itemCode}`.as("item_code"), + itemName: sql`${rfqsLast.itemName}`.as("item_name"), + + // 패키지 정보 + packageNo: sql`${rfqsLast.packageNo}`.as("package_no"), + packageName: sql`${rfqsLast.packageName}`.as("package_name"), + + engPicName: sql`${rfqsLast.EngPicName}`.as("eng_pic_name"), + + // 상태와 날짜 + status: sql`${rfqsLast.status}`.as("status"), + rfqSendDate: sql`${rfqsLast.rfqSendDate}`.as("rfq_send_date"), + dueDate: sql`${rfqsLast.dueDate}`.as("due_date"), + + // PIC 정보 + picId: sql`${rfqsLast.pic}`.as("pic_id"), + picCode: sql`${rfqsLast.picCode}`.as("pic_code"), + picName: sql`${rfqsLast.picName}`.as("pic_name"), + picUserName: sql`${picUser.name}`.as("pic_user_name"), + + // 감사 정보 + createdBy: sql`${rfqsLast.createdBy}`.as("created_by"), + createdByUserName: sql`${createdByUser.name}`.as("created_by_user_name"), + createdAt: sql`${rfqsLast.createdAt}`.as("created_at"), + sentBy: sql`${rfqsLast.sentBy}`.as("sent_by"), + sentByUserName: sql`${sentByUser.name}`.as("sent_by_user_name"), + updatedBy: sql`${rfqsLast.updatedBy}`.as("updated_by"), + updatedByUserName: sql`${updatedByUser.name}`.as("updated_by_user_name"), + updatedAt: sql`${rfqsLast.updatedAt}`.as("updated_at"), + remark: sql`${rfqsLast.remark}`.as("remark"), + + // ===== 벤더별 정보 ===== + vendorId: sql`${vendors.id}`.as("vendor_id"), + vendorName: sql`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql`${vendors.vendorCode}`.as("vendor_code"), + + // rfqLastDetails 정보 + rfqLastDetailsId: sql`${rfqLastDetails.id}`.as("rfq_last_details_id"), + emailSentAt: sql`${rfqLastDetails.emailSentAt}`.as("email_sent_at"), + emailStatus: sql`${rfqLastDetails.emailStatus}`.as("email_status"), + shortList: sql`${rfqLastDetails.shortList}`.as("short_list"), + + // ===== 벤더 응답 정보 (rfqLastVendorResponses) ===== + vendorResponseId: sql`${rfqLastVendorResponses.id}`.as("vendor_response_id"), + + // 참여 상태 + participationStatus: sql`${rfqLastVendorResponses.participationStatus}`.as("participation_status"), + participationRepliedAt: sql`${rfqLastVendorResponses.participationRepliedAt}`.as("participation_replied_at"), + nonParticipationReason: sql`${rfqLastVendorResponses.nonParticipationReason}`.as("non_participation_reason"), + + // 응답 상태 + responseStatus: sql`${rfqLastVendorResponses.status}`.as("response_status"), + responseVersion: sql`${rfqLastVendorResponses.responseVersion}`.as("response_version"), + submittedAt: sql`${rfqLastVendorResponses.submittedAt}`.as("submitted_at"), + + // 금액 정보 + totalAmount: sql`${rfqLastVendorResponses.totalAmount}`.as("total_amount"), + vendorCurrency: sql`${rfqLastVendorResponses.vendorCurrency}`.as("vendor_currency"), + + // 벤더 제안 조건 + vendorPaymentTermsCode: sql`${rfqLastVendorResponses.vendorPaymentTermsCode}`.as("vendor_payment_terms_code"), + vendorIncotermsCode: sql`${rfqLastVendorResponses.vendorIncotermsCode}`.as("vendor_incoterms_code"), + vendorDeliveryDate: sql`${rfqLastVendorResponses.vendorDeliveryDate}`.as("vendor_delivery_date"), + + // ===== 계산된 필드 - displayStatus ===== + displayStatus: sql` + CASE + WHEN ${rfqLastVendorResponses.participationStatus} = '불참' THEN '불참' + WHEN ${rfqLastVendorResponses.participationStatus} = '참여' THEN + COALESCE(${rfqLastVendorResponses.status}, '작성중') + WHEN ${rfqLastVendorResponses.participationStatus} = '미응답' OR ${rfqLastVendorResponses.participationStatus} IS NULL THEN + CASE + WHEN ${rfqLastDetails.emailSentAt} IS NOT NULL THEN '미응답' + ELSE NULL + END + ELSE '미응답' + END + `.as("display_status"), + + // ===== 집계 정보 (RFQ 레벨) ===== + vendorCount: sql`( + SELECT COUNT(*) + FROM rfq_last_details d + WHERE d.rfqs_last_id = ${rfqsLast.id} + AND d.is_latest = true + )`.as("vendor_count"), + + shortListedVendorCount: sql`( + SELECT COUNT(*) + FROM rfq_last_details d + WHERE d.rfqs_last_id = ${rfqsLast.id} + AND d.short_list = true + AND d.is_latest = true + )`.as("short_listed_vendor_count"), + + quotationReceivedCount: sql`( + SELECT COUNT(DISTINCT r.vendor_id) + FROM rfq_last_vendor_responses r + WHERE r.rfqs_last_id = ${rfqsLast.id} + AND r.submitted_at IS NOT NULL + AND r.is_latest = true + )`.as("quotation_received_count"), + + earliestQuotationSubmittedAt: sql`( + SELECT MIN(r.submitted_at) + FROM rfq_last_vendor_responses r + WHERE r.rfqs_last_id = ${rfqsLast.id} + AND r.submitted_at IS NOT NULL + AND r.is_latest = true + )`.as("earliest_quotation_submitted_at"), + + // PR Items 관련 정보 + majorItemMaterialCode: sql`( + SELECT material_code + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_code"), + + majorItemMaterialDescription: sql`( + SELECT material_description + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_description"), + + majorItemMaterialCategory: sql`( + SELECT material_category + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_category"), + + majorItemPrNo: sql`( + SELECT pr_no + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_pr_no"), + + prItemsCount: sql`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + )`.as("pr_items_count"), + + majorItemsCount: sql`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + )`.as("major_items_count") + }) + .from(rfqsLast) + .innerJoin(rfqLastDetails, + and( + eq(rfqLastDetails.rfqsLastId, rfqsLast.id), + eq(rfqLastDetails.isLatest, true) + ) + ) + .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id)) + .leftJoin(rfqLastVendorResponses, + and( + eq(rfqLastVendorResponses.rfqsLastId, rfqsLast.id), + eq(rfqLastVendorResponses.vendorId, vendors.id), + eq(rfqLastVendorResponses.isLatest, true) + ) + ) + .leftJoin(projects, eq(rfqsLast.projectId, projects.id)) + .leftJoin(createdByUser, eq(rfqsLast.createdBy, createdByUser.id)) + .leftJoin(updatedByUser, eq(rfqsLast.updatedBy, updatedByUser.id)) + .leftJoin(sentByUser, eq(rfqsLast.sentBy, sentByUser.id)) + .leftJoin(picUser, eq(rfqsLast.pic, picUser.id)); +}); + +// Type export +export type VendorQuotationView = typeof vendorQuotationView.$inferSelect; + -- cgit v1.2.3