diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/bRfq.ts | 1409 | ||||
| -rw-r--r-- | db/schema/index.ts | 11 | ||||
| -rw-r--r-- | db/schema/procurementRFQ.ts | 771 | ||||
| -rw-r--r-- | db/schema/rfqLastTBE.ts | 63 | ||||
| -rw-r--r-- | db/schema/vendors.ts | 3 |
5 files changed, 41 insertions, 2216 deletions
diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts deleted file mode 100644 index 6eef6ee3..00000000 --- a/db/schema/bRfq.ts +++ /dev/null @@ -1,1409 +0,0 @@ -import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core"; -import { eq, sql, and, relations } from "drizzle-orm"; -import { projects } from "./projects"; -import { users } from "./users"; -import { vendors } from "./vendors"; -import { incoterms, paymentTerms } from "./procurementRFQ"; - -export const bRfqs = pgTable( - "b_rfqs", - { - id: serial("id").primaryKey(), - - // RFQ 고유 코드 - rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001" - - // 프로젝트 참조 - projectId: integer("project_id") - .references(() => projects.id, { onDelete: "set null" }), - - description: varchar("description", { length: 255 }), - - remark: text("remark"), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>() - .notNull(), - - status: varchar("status", { length: 30 }) - .$type<"DRAFT" | "Doc. Received" | "PIC Assigned" | "Doc. Confirmed" | "Init. RFQ Sent" | "Init. RFQ Answered" | "TBE started" | "TBE finished" | "Final RFQ Sent" | "Quotation Received" | "Vendor Selected">() - .default("DRAFT") - .notNull(), - - picCode: varchar("pic_code", { length: 50 }), - picName: varchar("pic_name", { length: 50 }), - EngPicName: varchar("eng_pic_name", { length: 50 }), - - projectCompany: varchar("project_company", { length: 255 }), - projectFlag: varchar("project_flag", { length: 255 }), - projectSite: varchar("project_site", { length: 255 }), - - packageNo: varchar("package_no", { length: 50 }), - packageName: varchar("package_name", { length: 255 }), - - // 생성자 - createdBy: integer("created_by") - .notNull() - .references(() => users.id, { onDelete: "set null" }), - - updatedBy: integer("updated_by") - .notNull() - .references(() => users.id, { onDelete: "set null" }), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - } -); - -export const initialRfq = pgTable("initial_rfq", { - id: serial("id").primaryKey(), - rfqId: integer("rfq_id") - .notNull() - .references(() => bRfqs.id), - - initialRfqStatus: varchar("initial_rfq_status", { length: 30 }) - .$type<"DRAFT" | "Init. RFQ Sent" | "S/L Decline" | "Init. RFQ Answered">() - .default("DRAFT") - .notNull(), - - vendorId: integer("vendor_id") - .notNull() - .references(() => vendors.id), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>() - .notNull(), - - validDate: date("valid_date", { mode: "date" }) - .$type<Date>(), - - incotermsCode: varchar("incoterms_code", { length: 20 }) - .references(() => incoterms.code, { onDelete: "set null" }), - - gtc: varchar("gtc", { length: 255 }), - gtcValidDate: varchar("gtc_valid_date", { length: 255 }), - - classification: varchar("classification", { length: 255 }), - sparepart: varchar("sparepart", { length: 255 }), - - shortList: boolean('short_list').notNull().default(false), - returnYn: boolean('return_yn').notNull().default(false), - - cpRequestYn: boolean('cp_request_yn').notNull().default(false), - - prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false), - - returnRevision: integer("return_revision") - .notNull().default(0), - - rfqRevision: integer("rfq_revision") - .notNull().default(0), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), -}); - -export const finalRfq = pgTable("final_rfq", { - id: serial("id").primaryKey(), - rfqId: integer("rfq_id") - .notNull() - .references(() => bRfqs.id), - - finalRfqStatus: varchar("final_rfq_status", { length: 30 }) - .$type<"DRAFT" | "Final RFQ Sent" | "Final RFQ Answered">() - .default("DRAFT") - .notNull(), - - vendorId: integer("vendor_id") - .notNull() - .references(() => vendors.id), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>() - .notNull(), - - validDate: date("valid_date", { mode: "date" }) - .$type<Date>(), - - incotermsCode: varchar("incoterms_code", { length: 20 }) - .references(() => incoterms.code, { onDelete: "set null" }), - - gtc: varchar("gtc", { length: 255 }), - gtcValidDate: varchar("gtc_valid_date", { length: 255 }), - - classification: varchar("classification", { length: 255 }), - sparepart: varchar("sparepart", { length: 255 }), - - shortList: boolean('short_list').notNull().default(false), - returnYn: boolean('return_yn').notNull().default(false), - - cpRequestYn: boolean('cp_request_yn').notNull().default(false), - - prjectGtcYn: boolean('prject_gtc_yn').notNull().default(true), - - returnRevision: integer("return_revision") - .notNull().default(0), - - currency: varchar("currency", { length: 10 }).default("KRW"), - - paymentTermsCode: varchar("payment_terms_code", { length: 50 }) - .references(() => paymentTerms.code, { onDelete: "set null" }), - - taxCode: varchar("tax_code", { length: 255 }).default("VV"), - deliveryDate: date("delivery_date", { mode: "date" }) - .$type<Date>() - .notNull(), - - placeOfShipping: varchar("place_of_shipping", { length: 255 }), - placeOfDestination: varchar("place_of_destination", { length: 255 }), - - firsttimeYn: boolean('firsttime_yn').notNull().default(true), - materialPriceRelatedYn: boolean("material_price_related_yn").default(false), - remark: text("remark"), - - vendorRemark: text("vendor_remark"), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), -}); - -export const bRfqsAttachments = pgTable( - "b_rfq_attachments", - { - id: serial("id").primaryKey(), - attachmentType: varchar("attachment_type", { length: 50 }).notNull(), - serialNo: varchar("serial_no", { length: 50 }).notNull(), - rfqId: integer("rfq_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 }), - - // 리비전 생성 정보 - 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, 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( - "vendor_attachment_responses", - { - id: serial("id").primaryKey(), - attachmentId: integer("attachment_id") - .notNull() - .references(() => bRfqsAttachments.id, { onDelete: "cascade" }), - - vendorId: integer("vendor_id") - .notNull() - .references(() => vendors.id, { onDelete: "cascade" }), - - rfqType: varchar("rfq_type", { length: 20 }) - .$type<"INITIAL" | "FINAL">() - .notNull(), // initial_rfq 또는 final_rfq 구분 - - rfqRecordId: integer("rfq_record_id").notNull(), // initialRfq.id 또는 finalRfq.id - - responseStatus: varchar("response_status", { length: 30 }) - .$type<"NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED">() - .default("NOT_RESPONDED") - .notNull(), - - currentRevision: varchar("current_revision", { length: 10 }).default("Rev.0"), - respondedRevision: varchar("responded_revision", { length: 10 }), - - responseComment: text("response_comment"), - vendorComment: text("vendor_comment"), - - revisionRequestComment: text("revision_request_comment"), - - - // 응답 관련 날짜 - requestedAt: timestamp("requested_at").notNull(), - respondedAt: timestamp("responded_at"), - revisionRequestedAt: timestamp("revision_requested_at"), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - - createdBy: integer("created_by") - .references(() => users.id, { onDelete: "set null" }) - , - - updatedBy: integer("updated_by") - .references(() => users.id, { onDelete: "set null" }) - , - }, (t) => ({ - // attachmentId + vendorId + rfqType 유니크 - vendorResponseIdx: uniqueIndex('vendor_response_idx').on( - t.attachmentId.asc(), - t.vendorId.asc(), - t.rfqType.asc(), - ), - })); - -// 3. 벤더 응답 첨부파일 -export const vendorResponseAttachmentsB = pgTable( - "vendor_response_attachments_b", - { - id: serial("id").primaryKey(), - vendorResponseId: integer("vendor_response_id") - .notNull() - .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }), - - 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 }), - - uploadedBy: integer("uploaded_by") - .references(() => users.id, { onDelete: "set null" }), - uploadedAt: timestamp("uploaded_at").defaultNow().notNull(), - }, -); - -// 4. 응답 히스토리 추적 (선택사항) -export const vendorResponseHistory = pgTable( - "vendor_response_history", - { - id: serial("id").primaryKey(), - vendorResponseId: integer("vendor_response_id") - .notNull() - .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }), - - action: varchar("action", { length: 50 }) - .$type<"REQUESTED" | "RESPONDED" | "REVISION_REQUESTED" | "REVISED" | "WAIVED">() - .notNull(), - - previousStatus: varchar("previous_status", { length: 30 }), - newStatus: varchar("new_status", { length: 30 }), - - comment: text("comment"), - - actionBy: integer("action_by") - .references(() => users.id, { onDelete: "set null" }), - actionAt: timestamp("action_at").defaultNow().notNull(), - }, -); - -// === 유용한 뷰들 === - -// 1. bRfqs 기본 마스터 뷰 (프로젝트 정보 포함) -export const bRfqsMasterView = pgView("b_rfqs_master", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - description: varchar("description", { length: 255 }), - status: varchar("status", { length: 30 }), - dueDate: date("due_date"), - picCode: varchar("pic_code", { length: 50 }), - picName: varchar("pic_name", { length: 50 }), - EngPicName: varchar("eng_pic_name", { length: 50 }), - packageNo: varchar("package_no", { length: 50 }), - packageName: varchar("package_name", { length: 255 }), - projectId: integer("project_id"), - projectCode: varchar("project_code", { length: 50 }), - projectName: text("project_name"), - projectType: varchar("project_type", { length: 20 }), - projectCompany: varchar("project_company", { length: 255 }), - projectFlag: varchar("project_flag", { length: 255 }), - projectSite: varchar("project_site", { length: 255 }), - totalAttachments: integer("total_attachments"), - createdAt: timestamp("created_at"), - updatedAt: timestamp("updated_at"), -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - br.description, - br.status, - br.due_date, - br.pic_code, - br.pic_name, - br.eng_pic_name, - br.package_no, - br.package_name, - br.project_id, - p.code as project_code, - p.name as project_name, - p.type as project_type, - br.project_company, - br.project_flag, - br.project_site, - COALESCE(att_count.total_attachments, 0) as total_attachments, - br.created_at, - br.updated_at - FROM b_rfqs br - LEFT JOIN projects p ON br.project_id = p.id - LEFT JOIN ( - SELECT rfq_id, COUNT(*) as total_attachments - FROM b_rfq_attachments - GROUP BY rfq_id - ) att_count ON br.id = att_count.rfq_id -`); - -// 2. Initial RFQ 상세 뷰 (벤더, 인코텀즈 정보 포함) -export const initialRfqDetailView = pgView("initial_rfq_detail", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - rfqStatus: varchar("rfq_status", { length: 30 }), - initialRfqId: integer("initial_rfq_id"), - initialRfqStatus: varchar("initial_rfq_status", { length: 30 }), - vendorId: integer("vendor_id"), - vendorCode: varchar("vendor_code", { length: 50 }), - vendorName: varchar("vendor_name", { length: 255 }), - vendorCategory: varchar("vendor_category", { length: 255 }), - vendorCountry: varchar("vendor_country", { length: 100 }), - vendorBusinessSize: varchar("vendor_business_size", { length: 50 }), - dueDate: date("due_date"), - validDate: date("valid_date"), - incotermsCode: varchar("incoterms_code", { length: 20 }), - incotermsDescription: varchar("incoterms_description", { length: 255 }), - shortList: boolean("short_list"), - returnYn: boolean("return_yn"), - cpRequestYn: boolean("cp_request_yn"), - prjectGtcYn: boolean("prject_gtc_yn"), - returnRevision: integer("return_revision"), - rfqRevision: integer("rfq_revision"), - gtc: varchar("gtc", { length: 255 }), - gtcValidDate: varchar("gtc_valid_date", { length: 255 }), - classification: varchar("classification", { length: 255 }), - sparepart: varchar("sparepart", { length: 255 }), - createdAt: timestamp("created_at"), - updatedAt: timestamp("updated_at"), -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - br.status as rfq_status, - ir.id as initial_rfq_id, - ir.initial_rfq_status, - ir.vendor_id, - v.vendor_code, - v.vendor_name, - v.country as vendor_country, - v.business_size as vendor_business_size, - v.vendor_category as vendor_category, - ir.due_date, - ir.valid_date, - ir.incoterms_code, - inc.description as incoterms_description, - ir.short_list, - ir.return_yn, - ir.cp_request_yn, - ir.prject_gtc_yn, - ir.return_revision, - ir.rfq_revision, - ir.gtc, - ir.gtc_valid_date, - ir.classification, - ir.sparepart, - ir.created_at, - ir.updated_at - FROM b_rfqs br - JOIN initial_rfq ir ON br.id = ir.rfq_id - LEFT JOIN vendors_with_types v ON ir.vendor_id = v.id - LEFT JOIN incoterms inc ON ir.incoterms_code = inc.code -`); - -// 3. Final RFQ 상세 뷰 (벤더, 인코텀즈, 결제조건 정보 포함) -export const finalRfqDetailView = pgView("final_rfq_detail", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - rfqStatus: varchar("rfq_status", { length: 30 }), - finalRfqId: integer("final_rfq_id"), - finalRfqStatus: varchar("final_rfq_status", { length: 30 }), - vendorId: integer("vendor_id"), - vendorCode: varchar("vendor_code", { length: 50 }), - vendorName: varchar("vendor_name", { length: 255 }), - vendorCountry: varchar("vendor_country", { length: 100 }), - vendorBusinessSize: varchar("vendor_business_size", { length: 50 }), - dueDate: date("due_date"), - validDate: date("valid_date"), - deliveryDate: date("delivery_date"), - incotermsCode: varchar("incoterms_code", { length: 20 }), - incotermsDescription: varchar("incoterms_description", { length: 255 }), - paymentTermsCode: varchar("payment_terms_code", { length: 50 }), - paymentTermsDescription: varchar("payment_terms_description", { length: 255 }), - currency: varchar("currency", { length: 10 }), - taxCode: varchar("tax_code", { length: 255 }), - placeOfShipping: varchar("place_of_shipping", { length: 255 }), - placeOfDestination: varchar("place_of_destination", { length: 255 }), - shortList: boolean("short_list"), - returnYn: boolean("return_yn"), - cpRequestYn: boolean("cp_request_yn"), - prjectGtcYn: boolean("prject_gtc_yn"), - firsttimeYn: boolean("firsttime_yn"), - materialPriceRelatedYn: boolean("material_price_related_yn"), - returnRevision: integer("return_revision"), - gtc: varchar("gtc", { length: 255 }), - gtcValidDate: varchar("gtc_valid_date", { length: 255 }), - classification: varchar("classification", { length: 255 }), - sparepart: varchar("sparepart", { length: 255 }), - remark: text("remark"), - vendorRemark: text("vendor_remark"), - createdAt: timestamp("created_at"), - updatedAt: timestamp("updated_at"), -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - br.status as rfq_status, - fr.id as final_rfq_id, - fr.final_rfq_status, - fr.vendor_id, - v.vendor_code, - v.vendor_name, - v.country as vendor_country, - v.business_size as vendor_business_size, - fr.due_date, - fr.valid_date, - fr.delivery_date, - fr.incoterms_code, - inc.description as incoterms_description, - fr.payment_terms_code, - pt.description as payment_terms_description, - fr.currency, - fr.tax_code, - fr.place_of_shipping, - fr.place_of_destination, - fr.short_list, - fr.return_yn, - fr.cp_request_yn, - fr.prject_gtc_yn, - fr.firsttime_yn, - fr.material_price_related_yn, - fr.return_revision, - fr.gtc, - fr.gtc_valid_date, - fr.classification, - fr.sparepart, - fr.remark, - fr.vendor_remark, - fr.created_at, - fr.updated_at - FROM b_rfqs br - JOIN final_rfq fr ON br.id = fr.rfq_id - LEFT JOIN vendors v ON fr.vendor_id = v.id - LEFT JOIN incoterms inc ON fr.incoterms_code = inc.code - LEFT JOIN payment_terms pt ON fr.payment_terms_code = pt.code -`); - -// 4. 벤더 응답 현황 요약 뷰 -export const vendorResponseSummaryView2 = pgView("vendor_response_summary", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - rfqStatus: varchar("rfq_status", { length: 30 }), - vendorId: integer("vendor_id"), - vendorCode: varchar("vendor_code", { length: 50 }), - vendorName: varchar("vendor_name", { length: 255 }), - vendorCountry: varchar("vendor_country", { length: 100 }), - vendorBusinessSize: varchar("vendor_business_size", { length: 50 }), - rfqType: varchar("rfq_type", { length: 20 }), - totalAttachments: integer("total_attachments"), - respondedCount: integer("responded_count"), - pendingCount: integer("pending_count"), - waivedCount: integer("waived_count"), - revisionRequestedCount: integer("revision_requested_count"), - responseRate: numeric("response_rate", { precision: 5, scale: 2 }), - completionRate: numeric("completion_rate", { precision: 5, scale: 2 }), -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - br.status as rfq_status, - v.id as vendor_id, - v.vendor_code, - v.vendor_name, - v.country as vendor_country, - v.business_size as vendor_business_size, - var.rfq_type, - COUNT(var.id) as total_attachments, - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, - COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count, - COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END) as waived_count, - COUNT(CASE WHEN var.response_status = 'REVISION_REQUESTED' THEN 1 END) as revision_requested_count, - ROUND( - (COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / - NULLIF(COUNT(CASE WHEN var.response_status != 'WAIVED' THEN 1 END), 0)), - 2 - ) as response_rate, - ROUND( - ((COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) + - COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END)) * 100.0 / COUNT(var.id)), - 2 - ) as completion_rate - FROM b_rfqs br - JOIN b_rfq_attachments bra ON br.id = bra.rfq_id - JOIN vendor_attachment_responses var ON bra.id = var.attachment_id - JOIN vendors v ON var.vendor_id = v.id - GROUP BY br.id, br.rfq_code, br.status, v.id, v.vendor_code, v.vendor_name, v.country, v.business_size, var.rfq_type -`); - -// 5. RFQ 전체 진행 현황 대시보드 뷰 -export const rfqDashboardView = pgView("rfq_dashboard", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - description: varchar("description", { length: 255 }), - status: varchar("status", { length: 30 }), - dueDate: date("due_date"), - projectCode: varchar("project_code", { length: 50 }), - projectName: text("project_name"), - packageNo: varchar("package_no", { length: 50 }), - packageName: varchar("package_name", { length: 255 }), - picCode: varchar("pic_code", { length: 50 }), - picName: varchar("pic_name", { length: 50 }), - engPicName: varchar("eng_pic_name", { length: 50 }), - projectCompany: varchar("project_company", { length: 255 }), - projectFlag: varchar("project_flag", { length: 255 }), - projectSite: varchar("project_site", { length: 255 }), - totalAttachments: integer("total_attachments"), - initialVendorCount: integer("initial_vendor_count"), - finalVendorCount: integer("final_vendor_count"), - initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }), - finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }), - overallProgress: numeric("overall_progress", { precision: 5, scale: 2 }), - daysToDeadline: integer("days_to_deadline"), - createdAt: timestamp("created_at"), - updatedAt: timestamp("updated_at"), - remark: text("remark"), - updatedByName: varchar("updated_by_name", { length: 255 }), - updatedByEmail: varchar("updated_by_email", { length: 255 }), -}).as(sql` - -- ② SELECT 절 확장 ------------------------------------------- - SELECT - br.id AS rfq_id, - br.rfq_code, - br.description, - br.status, - br.due_date, - p.code AS project_code, - p.name AS project_name, - br.package_no, - br.package_name, - br.pic_code, - br.pic_name, - br.eng_pic_name, - br.project_company, - br.project_flag, - br.project_site, - br.remark, - - -- 첨부/벤더 요약 ----------------------- - COALESCE(att_count.total_attachments, 0) AS total_attachments, - COALESCE(init_summary.vendor_count, 0) AS initial_vendor_count, - COALESCE(final_summary.vendor_count, 0) AS final_vendor_count, - COALESCE(init_summary.avg_response_rate, 0) AS initial_response_rate, - COALESCE(final_summary.avg_response_rate, 0) AS final_response_rate, - - -- 진행률·마감까지 일수 -------------- - CASE - WHEN br.status = 'DRAFT' THEN 0 - WHEN br.status = 'Doc. Received' THEN 10 - WHEN br.status = 'PIC Assigned' THEN 20 - WHEN br.status = 'Doc. Confirmed' THEN 30 - WHEN br.status = 'Init. RFQ Sent' THEN 40 - WHEN br.status = 'Init. RFQ Answered' THEN 50 - WHEN br.status = 'TBE started' THEN 60 - WHEN br.status = 'TBE finished' THEN 70 - WHEN br.status = 'Final RFQ Sent' THEN 80 - WHEN br.status = 'Quotation Received' THEN 90 - WHEN br.status = 'Vendor Selected' THEN 100 - ELSE 0 - END AS overall_progress, - (br.due_date - CURRENT_DATE) AS days_to_deadline, - - br.created_at, - br.updated_at, - - -- 💡 추가되는 컬럼 ------------------- - upd.name AS updated_by_name, - upd.email AS updated_by_email - FROM b_rfqs br - LEFT JOIN projects p ON br.project_id = p.id - - -- ③ 사용자 정보 조인 -------------------- - LEFT JOIN users upd ON br.updated_by = upd.id - - -- (나머지 이미 있던 JOIN 들은 그대로) ----- - LEFT JOIN ( - SELECT rfq_id, COUNT(*) AS total_attachments - FROM b_rfq_attachments - GROUP BY rfq_id - ) att_count ON br.id = att_count.rfq_id - - LEFT JOIN ( - SELECT - rfq_id, - COUNT(DISTINCT vendor_id) AS vendor_count, - AVG(response_rate) AS avg_response_rate - FROM vendor_response_summary - WHERE rfq_type = 'INITIAL' - GROUP BY rfq_id - ) init_summary ON br.id = init_summary.rfq_id - - LEFT JOIN ( - SELECT - rfq_id, - COUNT(DISTINCT vendor_id) AS vendor_count, - AVG(response_rate) AS avg_response_rate - FROM vendor_response_summary - WHERE rfq_type = 'FINAL' - GROUP BY rfq_id - ) final_summary ON br.id = final_summary.rfq_id - `); - -// 사용 예시 타입 정의 -export type VendorAttachmentResponse = typeof vendorAttachmentResponses.$inferSelect; -export type NewVendorAttachmentResponse = typeof vendorAttachmentResponses.$inferInsert; -export type AttachmentRevision = typeof bRfqAttachmentRevisions.$inferSelect; -export type ResponseAttachment = typeof vendorResponseAttachmentsB.$inferSelect; - -export type InitialRfqDetailView = typeof initialRfqDetailView.$inferSelect; -export type FinalRfqDetailView = typeof finalRfqDetailView.$inferSelect; -export type RfqDashboardView = typeof rfqDashboardView.$inferSelect; - - -export const bRfqsRelations = relations(bRfqs, ({ one, many }) => ({ - // 단일 관계 - project: one(projects, { - fields: [bRfqs.projectId], - references: [projects.id], - }), - createdByUser: one(users, { - fields: [bRfqs.createdBy], - references: [users.id], - relationName: "bRfqCreatedBy" - }), - updatedByUser: one(users, { - fields: [bRfqs.updatedBy], - references: [users.id], - relationName: "bRfqUpdatedBy" - }), - - // 다중 관계 - attachments: many(bRfqsAttachments), - initialRfqs: many(initialRfq), - finalRfqs: many(finalRfq), - })); - - // bRfqsAttachments 관계 정의 - export const bRfqsAttachmentsRelations = relations(bRfqsAttachments, ({ one, many }) => ({ - // 단일 관계 - rfq: one(bRfqs, { - fields: [bRfqsAttachments.rfqId], - references: [bRfqs.id], - }), - createdByUser: one(users, { - fields: [bRfqsAttachments.createdBy], - references: [users.id], - }), - latestRevision: one(bRfqAttachmentRevisions, { - fields: [bRfqsAttachments.latestRevisionId], - references: [bRfqAttachmentRevisions.id], - relationName: "attachmentLatestRevision" - }), - - // 다중 관계 - revisions: many(bRfqAttachmentRevisions), - vendorResponses: many(vendorAttachmentResponses), - })); - - // bRfqAttachmentRevisions 관계 정의 - export const bRfqAttachmentRevisionsRelations = relations(bRfqAttachmentRevisions, ({ one }) => ({ - attachment: one(bRfqsAttachments, { - fields: [bRfqAttachmentRevisions.attachmentId], - references: [bRfqsAttachments.id], - }), - createdByUser: one(users, { - fields: [bRfqAttachmentRevisions.createdBy], - references: [users.id], - }), - })); - - // vendorAttachmentResponses 관계 정의 - export const vendorAttachmentResponsesRelations = relations(vendorAttachmentResponses, ({ one, many }) => ({ - // 단일 관계 - attachment: one(bRfqsAttachments, { - fields: [vendorAttachmentResponses.attachmentId], - references: [bRfqsAttachments.id], - }), - vendor: one(vendors, { - fields: [vendorAttachmentResponses.vendorId], - references: [vendors.id], - }), - - // 다중 관계 - responseAttachments: many(vendorResponseAttachmentsB), - history: many(vendorResponseHistory), - })); - - // vendorResponseAttachmentsB 관계 정의 - export const vendorResponseAttachmentsBRelations = relations(vendorResponseAttachmentsB, ({ one }) => ({ - vendorResponse: one(vendorAttachmentResponses, { - fields: [vendorResponseAttachmentsB.vendorResponseId], - references: [vendorAttachmentResponses.id], - }), - uploadedByUser: one(users, { - fields: [vendorResponseAttachmentsB.uploadedBy], - references: [users.id], - }), - })); - - // vendorResponseHistory 관계 정의 - export const vendorResponseHistoryRelations_old = relations(vendorResponseHistory, ({ one }) => ({ - vendorResponse: one(vendorAttachmentResponses, { - fields: [vendorResponseHistory.vendorResponseId], - references: [vendorAttachmentResponses.id], - }), - actionByUser: one(users, { - fields: [vendorResponseHistory.actionBy], - references: [users.id], - }), - })); - - // initialRfq 관계 정의 - export const initialRfqRelations = relations(initialRfq, ({ one }) => ({ - rfq: one(bRfqs, { - fields: [initialRfq.rfqId], - references: [bRfqs.id], - }), - vendor: one(vendors, { - fields: [initialRfq.vendorId], - references: [vendors.id], - }), - })); - - // finalRfq 관계 정의 - export const finalRfqRelations = relations(finalRfq, ({ one }) => ({ - rfq: one(bRfqs, { - fields: [finalRfq.rfqId], - references: [bRfqs.id], - }), - vendor: one(vendors, { - fields: [finalRfq.vendorId], - references: [vendors.id], - }), - })); - - - -// 업데이트된 vendorResponseAttachmentsEnhanced 뷰 -export const vendorResponseAttachmentsEnhanced = pgView("vendor_response_attachments_enhanced", { - // 벤더 응답 파일 기본 정보 - responseAttachmentId: integer("response_attachment_id"), - vendorResponseId: integer("vendor_response_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 }), - description: varchar("description", { length: 500 }), - uploadedAt: timestamp("uploaded_at"), - - // 응답 기본 정보 - attachmentId: integer("attachment_id"), - vendorId: integer("vendor_id"), - rfqType: varchar("rfq_type", { length: 20 }), - rfqRecordId: integer("rfq_record_id"), - responseStatus: varchar("response_status", { length: 30 }), - currentRevision: varchar("current_revision", { length: 10 }), - respondedRevision: varchar("responded_revision", { length: 10 }), - - // 코멘트 관련 필드들 (새로 추가된 필드 포함) - responseComment: text("response_comment"), - vendorComment: text("vendor_comment"), - revisionRequestComment: text("revision_request_comment"), // 새로 추가 - - // 날짜 관련 필드들 (새로 추가된 필드 포함) - requestedAt: timestamp("requested_at"), - respondedAt: timestamp("responded_at"), - revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가 - - // 첨부파일 정보 - attachmentType: varchar("attachment_type", { length: 50 }), - serialNo: varchar("serial_no", { length: 50 }), - rfqId: integer("rfq_id"), - - // 벤더 정보 - vendorCode: varchar("vendor_code", { length: 50 }), - vendorName: varchar("vendor_name", { length: 255 }), - vendorCountry: varchar("vendor_country", { length: 100 }), - - // 발주처 현재 리비전 정보 - latestClientRevisionId: integer("latest_client_revision_id"), - latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }), - latestClientFileName: varchar("latest_client_file_name", { length: 255 }), - - // 리비전 비교 정보 - isVersionMatched: boolean("is_version_matched"), - versionLag: integer("version_lag"), // 몇 버전 뒤처져 있는지 - needsUpdate: boolean("needs_update"), - - // 응답 파일 순서 (같은 응답에 대한 여러 파일이 있을 경우) - fileSequence: integer("file_sequence"), - isLatestResponseFile: boolean("is_latest_response_file"), - -}).as(sql` - SELECT - vra.id as response_attachment_id, - vra.vendor_response_id, - vra.file_name, - vra.original_file_name, - vra.file_path, - vra.file_size, - vra.file_type, - vra.description, - vra.uploaded_at, - - -- 응답 기본 정보 - var.attachment_id, - var.vendor_id, - var.rfq_type, - var.rfq_record_id, - var.response_status, - var.current_revision, - var.responded_revision, - - -- 코멘트 (새로 추가된 필드 포함) - var.response_comment, - var.vendor_comment, - var.revision_request_comment, - - -- 날짜 (새로 추가된 필드 포함) - var.requested_at, - var.responded_at, - var.revision_requested_at, - - -- 첨부파일 정보 - ba.attachment_type, - ba.serial_no, - ba.rfq_id, - - -- 벤더 정보 - v.vendor_code, - v.vendor_name, - v.country as vendor_country, - - -- 발주처 현재 리비전 정보 - latest_rev.id as latest_client_revision_id, - latest_rev.revision_no as latest_client_revision_no, - latest_rev.original_file_name as latest_client_file_name, - - -- 리비전 비교 - CASE - WHEN var.responded_revision = ba.current_revision THEN true - ELSE false - END as is_version_matched, - - -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정) - CASE - WHEN var.responded_revision IS NULL THEN NULL - WHEN ba.current_revision IS NULL THEN NULL - ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - - CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER) - END as version_lag, - - CASE - WHEN var.response_status = 'RESPONDED' - AND var.responded_revision != ba.current_revision THEN true - ELSE false - END as needs_update, - - -- 파일 순서 - ROW_NUMBER() OVER ( - PARTITION BY var.id - ORDER BY vra.uploaded_at DESC - ) as file_sequence, - - -- 최신 응답 파일 여부 - CASE - WHEN ROW_NUMBER() OVER ( - PARTITION BY var.id - ORDER BY vra.uploaded_at DESC - ) = 1 THEN true - ELSE false - END as is_latest_response_file - - FROM vendor_response_attachments_b vra - JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id - JOIN b_rfq_attachments ba ON var.attachment_id = ba.id - LEFT JOIN vendors v ON var.vendor_id = v.id - LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id -`); - -// 2. 첨부파일별 리비전 히스토리 전체 뷰 -export const attachmentRevisionHistoryView = pgView("attachment_revision_history", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - attachmentId: integer("attachment_id"), - attachmentType: varchar("attachment_type", { length: 50 }), - serialNo: varchar("serial_no", { length: 50 }), - - // 발주처 리비전 정보 - clientRevisionId: integer("client_revision_id"), - clientRevisionNo: varchar("client_revision_no", { length: 10 }), - clientFileName: varchar("client_file_name", { length: 255 }), - clientFilePath: varchar("client_file_path", { length: 512 }), - clientFileSize: integer("client_file_size"), - clientRevisionComment: text("client_revision_comment"), - clientRevisionCreatedAt: timestamp("client_revision_created_at"), - isLatestClientRevision: boolean("is_latest_client_revision"), - - // 이 리비전에 대한 벤더 응답 통계 - totalVendorResponses: integer("total_vendor_responses"), - respondedVendors: integer("responded_vendors"), - pendingVendors: integer("pending_vendors"), - totalResponseFiles: integer("total_response_files"), - -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - ba.id as attachment_id, - ba.attachment_type, - ba.serial_no, - - -- 발주처 리비전 정보 - rev.id as client_revision_id, - rev.revision_no as client_revision_no, - rev.original_file_name as client_file_name, - rev.file_size as client_file_size, - rev.file_path as client_file_path, - rev.revision_comment as client_revision_comment, - rev.created_at as client_revision_created_at, - rev.is_latest as is_latest_client_revision, - - -- 벤더 응답 통계 - COALESCE(response_stats.total_responses, 0) as total_vendor_responses, - COALESCE(response_stats.responded_count, 0) as responded_vendors, - COALESCE(response_stats.pending_count, 0) as pending_vendors, - COALESCE(response_stats.total_files, 0) as total_response_files - - FROM b_rfqs br - JOIN b_rfq_attachments ba ON br.id = ba.rfq_id - JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id - LEFT JOIN ( - SELECT - var.attachment_id, - COUNT(*) as total_responses, - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, - COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count, - COUNT(vra.id) as total_files - FROM vendor_attachment_responses var - LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id - GROUP BY var.attachment_id - ) response_stats ON ba.id = response_stats.attachment_id - - ORDER BY ba.id, rev.created_at DESC -`); - -// 3. 벤더별 응답 현황 상세 뷰 (리비전 정보 포함) -// 업데이트된 vendorResponseDetailView -export const vendorResponseDetailView = pgView("vendor_response_detail", { - // 기본 식별 정보 - responseId: integer("response_id"), - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - rfqType: varchar("rfq_type", { length: 20 }), - rfqRecordId: integer("rfq_record_id"), - - // 첨부파일 정보 - attachmentId: integer("attachment_id"), - attachmentType: varchar("attachment_type", { length: 50 }), - serialNo: varchar("serial_no", { length: 50 }), - attachmentDescription: varchar("attachment_description", { length: 500 }), - - // 벤더 정보 - vendorId: integer("vendor_id"), - vendorCode: varchar("vendor_code", { length: 50 }), - vendorName: varchar("vendor_name", { length: 255 }), - vendorCountry: varchar("vendor_country", { length: 100 }), - - // 응답 상태 정보 - responseStatus: varchar("response_status", { length: 30 }), - currentRevision: varchar("current_revision", { length: 10 }), - respondedRevision: varchar("responded_revision", { length: 10 }), - - // 코멘트 관련 필드들 (새로 추가된 필드 포함) - responseComment: text("response_comment"), - vendorComment: text("vendor_comment"), - revisionRequestComment: text("revision_request_comment"), // 새로 추가 - - // 날짜 관련 필드들 (새로 추가된 필드 포함) - requestedAt: timestamp("requested_at"), - respondedAt: timestamp("responded_at"), - revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가 - - // 발주처 최신 리비전 정보 - latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }), - latestClientFileName: varchar("latest_client_file_name", { length: 255 }), - latestClientFileSize: integer("latest_client_file_size"), - latestClientRevisionComment: text("latest_client_revision_comment"), - - // 리비전 분석 - isVersionMatched: boolean("is_version_matched"), - versionLag: integer("version_lag"), - needsUpdate: boolean("needs_update"), - hasMultipleRevisions: boolean("has_multiple_revisions"), - - // 응답 파일 통계 - totalResponseFiles: integer("total_response_files"), - latestResponseFileName: varchar("latest_response_file_name", { length: 255 }), - latestResponseFileSize: integer("latest_response_file_size"), - latestResponseUploadedAt: timestamp("latest_response_uploaded_at"), - - // 효과적인 상태 (UI 표시용) - effectiveStatus: varchar("effective_status", { length: 50 }), - -}).as(sql` - SELECT - var.id as response_id, - ba.rfq_id, - br.rfq_code, - var.rfq_type, - var.rfq_record_id, - - -- 첨부파일 정보 - ba.id as attachment_id, - ba.attachment_type, - ba.serial_no, - ba.description as attachment_description, - - -- 벤더 정보 - v.id as vendor_id, - v.vendor_code, - v.vendor_name, - v.country as vendor_country, - - -- 응답 상태 - var.response_status, - var.current_revision, - var.responded_revision, - - -- 코멘트 (새로 추가된 필드 포함) - var.response_comment, - var.vendor_comment, - var.revision_request_comment, - - -- 날짜 (새로 추가된 필드 포함) - var.requested_at, - var.responded_at, - var.revision_requested_at, - - -- 발주처 최신 리비전 - latest_rev.revision_no as latest_client_revision_no, - latest_rev.original_file_name as latest_client_file_name, - latest_rev.file_size as latest_client_file_size, - latest_rev.revision_comment as latest_client_revision_comment, - - -- 리비전 분석 - CASE - WHEN var.responded_revision = ba.current_revision THEN true - ELSE false - END as is_version_matched, - - CASE - WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL - ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - - CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER) - END as version_lag, - - CASE - WHEN var.response_status = 'RESPONDED' - AND var.responded_revision != ba.current_revision THEN true - ELSE false - END as needs_update, - - CASE - WHEN revision_count.total_revisions > 1 THEN true - ELSE false - END as has_multiple_revisions, - - -- 응답 파일 정보 - COALESCE(file_stats.total_files, 0) as total_response_files, - file_stats.latest_file_name as latest_response_file_name, - file_stats.latest_file_size as latest_response_file_size, - file_stats.latest_uploaded_at as latest_response_uploaded_at, - - -- 효과적인 상태 - CASE - WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED' - WHEN var.response_status = 'WAIVED' THEN 'WAIVED' - WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED' - WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE' - WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH' - ELSE var.response_status - END as effective_status - - FROM vendor_attachment_responses var - JOIN b_rfq_attachments ba ON var.attachment_id = ba.id - JOIN b_rfqs br ON ba.rfq_id = br.id - LEFT JOIN vendors v ON var.vendor_id = v.id - LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id - LEFT JOIN ( - SELECT - attachment_id, - COUNT(*) as total_revisions - FROM b_rfq_attachment_revisions - GROUP BY attachment_id - ) revision_count ON ba.id = revision_count.attachment_id - LEFT JOIN ( - SELECT - vendor_response_id, - COUNT(*) as total_files, - MAX(original_file_name) as latest_file_name, - MAX(file_size) as latest_file_size, - MAX(uploaded_at) as latest_uploaded_at - FROM vendor_response_attachments_b - GROUP BY vendor_response_id - ) file_stats ON var.id = file_stats.vendor_response_id -`); - -// 4. RFQ 진행 현황 요약 뷰 (리비전 정보 포함) -export const rfqProgressSummaryView = pgView("rfq_progress_summary", { - rfqId: integer("rfq_id"), - rfqCode: varchar("rfq_code", { length: 50 }), - rfqStatus: varchar("rfq_status", { length: 30 }), - dueDate: date("due_date"), - daysToDeadline: integer("days_to_deadline"), - - // 첨부파일 통계 - totalAttachments: integer("total_attachments"), - attachmentsWithMultipleRevisions: integer("attachments_with_multiple_revisions"), - totalClientRevisions: integer("total_client_revisions"), - - // 응답 통계 (INITIAL) - initialVendorCount: integer("initial_vendor_count"), - initialTotalResponses: integer("initial_total_responses"), - initialRespondedCount: integer("initial_responded_count"), - initialUpToDateCount: integer("initial_up_to_date_count"), - initialVersionMismatchCount: integer("initial_version_mismatch_count"), - initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }), - initialVersionMatchRate: numeric("initial_version_match_rate", { precision: 5, scale: 2 }), - - // 응답 통계 (FINAL) - finalVendorCount: integer("final_vendor_count"), - finalTotalResponses: integer("final_total_responses"), - finalRespondedCount: integer("final_responded_count"), - finalUpToDateCount: integer("final_up_to_date_count"), - finalVersionMismatchCount: integer("final_version_mismatch_count"), - finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }), - finalVersionMatchRate: numeric("final_version_match_rate", { precision: 5, scale: 2 }), - - // 전체 파일 통계 - totalResponseFiles: integer("total_response_files"), - -}).as(sql` - SELECT - br.id as rfq_id, - br.rfq_code, - br.status as rfq_status, - br.due_date, - (br.due_date - CURRENT_DATE) as days_to_deadline, - - -- 첨부파일 통계 - attachment_stats.total_attachments, - attachment_stats.attachments_with_multiple_revisions, - attachment_stats.total_client_revisions, - - -- Initial RFQ 통계 - COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count, - COALESCE(initial_stats.total_responses, 0) as initial_total_responses, - COALESCE(initial_stats.responded_count, 0) as initial_responded_count, - COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count, - COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count, - COALESCE(initial_stats.response_rate, 0) as initial_response_rate, - COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate, - - -- Final RFQ 통계 - COALESCE(final_stats.vendor_count, 0) as final_vendor_count, - COALESCE(final_stats.total_responses, 0) as final_total_responses, - COALESCE(final_stats.responded_count, 0) as final_responded_count, - COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count, - COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count, - COALESCE(final_stats.response_rate, 0) as final_response_rate, - COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate, - - COALESCE(file_stats.total_files, 0) as total_response_files - - FROM b_rfqs br - LEFT JOIN ( - SELECT - ba.rfq_id, - COUNT(*) as total_attachments, - COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions, - SUM(rev_count.total_revisions) as total_client_revisions - FROM b_rfq_attachments ba - LEFT JOIN ( - SELECT - attachment_id, - COUNT(*) as total_revisions - FROM b_rfq_attachment_revisions - GROUP BY attachment_id - ) rev_count ON ba.id = rev_count.attachment_id - GROUP BY ba.rfq_id - ) attachment_stats ON br.id = attachment_stats.rfq_id - - LEFT JOIN ( - SELECT - br.id as rfq_id, - COUNT(DISTINCT var.vendor_id) as vendor_count, - COUNT(*) as total_responses, - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, - COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, - COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, - ROUND( - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / - NULLIF(COUNT(*), 0), 2 - ) as response_rate, - ROUND( - COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / - NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 - ) as version_match_rate - FROM b_rfqs br - JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id - JOIN vendor_attachment_responses var ON vrd.response_id = var.id - WHERE var.rfq_type = 'INITIAL' - GROUP BY br.id - ) initial_stats ON br.id = initial_stats.rfq_id - - LEFT JOIN ( - SELECT - br.id as rfq_id, - COUNT(DISTINCT var.vendor_id) as vendor_count, - COUNT(*) as total_responses, - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, - COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, - COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, - ROUND( - COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / - NULLIF(COUNT(*), 0), 2 - ) as response_rate, - ROUND( - COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / - NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 - ) as version_match_rate - FROM b_rfqs br - JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id - JOIN vendor_attachment_responses var ON vrd.response_id = var.id - WHERE var.rfq_type = 'FINAL' - GROUP BY br.id - ) final_stats ON br.id = final_stats.rfq_id - - LEFT JOIN ( - SELECT - br.id as rfq_id, - COUNT(vra.id) as total_files - FROM b_rfqs br - JOIN b_rfq_attachments ba ON br.id = ba.rfq_id - JOIN vendor_attachment_responses var ON ba.id = var.attachment_id - LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id - GROUP BY br.id - ) file_stats ON br.id = file_stats.rfq_id -`); - -// 타입 정의 -export type VendorResponseAttachmentEnhanced = typeof vendorResponseAttachmentsEnhanced.$inferSelect; -export type AttachmentRevisionHistory = typeof attachmentRevisionHistoryView.$inferSelect; -export type VendorResponseDetail = typeof vendorResponseDetailView.$inferSelect; -export type RfqProgressSummary = typeof rfqProgressSummaryView.$inferSelect;
\ No newline at end of file diff --git a/db/schema/index.ts b/db/schema/index.ts index cd54e032..7d433f7c 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -1,24 +1,29 @@ +export * from './vendors'; export * from './companies'; export * from './contract'; export * from './items'; export * from './pq'; export * from './projects'; -export * from './rfq'; +// 미사용 테이블 주석 처리 +// export * from './rfq'; + export * from './users'; export * from './vendorData'; export * from './vendorDocu'; -export * from './vendors'; export * from './tasks'; export * from './logs'; export * from './basicContractDocumnet'; + +// 미사용 테이블 주석 처리 export * from './procurementRFQ'; export * from './agreementComments'; export * from './setting'; export * from './techSales'; export * from './ocr'; // 명시적 import/export로 vendorResponseSummaryView 이름 충돌 방지 -export * from './bRfq'; +// 미사용 스키마 제거 +// export * from './bRfq'; export * from './techVendors'; export * from './evaluation'; export * from './evaluationTarget'; diff --git a/db/schema/procurementRFQ.ts b/db/schema/procurementRFQ.ts index fe60bb0e..2756f934 100644 --- a/db/schema/procurementRFQ.ts +++ b/db/schema/procurementRFQ.ts @@ -1,65 +1,4 @@ -import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check } from "drizzle-orm/pg-core"; -import { eq, sql, relations } from "drizzle-orm"; -import { projects } from "./projects"; -import { users } from "./users"; -import { vendors } from "./vendors"; - -export const procurementRfqs = pgTable( - "procurement_rfqs", - { - id: serial("id").primaryKey(), - - // RFQ 고유 코드 - rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001" - - // 프로젝트: ECC RFQ는 프로젝트 테이블과 1:N 관계를 가져야 함 - // WHY?: 여러 프로젝트 혹은 여러 시리즈의 동일 품목을 PR로 묶어 올리기 때문 - projectId: varchar("project_id", { length: 1000 }), - - // SS, II, null 값을 가질 수 있음. - // SS = 시리즈 통합, II = 품목 통합, 공란 = 통합 없음 - series: varchar("series", { length: 50 }), - - // 자재코드, 자재명: ECC RFQ는 자재코드, 자재명을 가지지 않음 - // WHY?: 여러 프로젝트 혹은 여러 시리즈의 동일 품목을 PR로 묶어 올리기 때문 - // 아래 컬럼은 대표 자재코드, 대표 자재명으로 사용 - itemCode: varchar("item_code", { length: 100 }), - itemName: varchar("item_name", { length: 255 }), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>(), // 인터페이스한 값은 dueDate가 없으므로 notNull 제약조건 제거 - - rfqSendDate: date("rfq_send_date", { mode: "date" }) - .$type<Date | null>(), // notNull() 제약조건 제거, null 허용 (ECC에서 수신 후 보내지 않은 RFQ) - - status: varchar("status", { length: 30 }) - .$type<"RFQ Created" | "RFQ Vendor Assignned" | "RFQ Sent" | "Quotation Analysis" | "PO Transfer" | "PO Create">() - .default("RFQ Created") - .notNull(), - - rfqSealedYn: boolean("rfq_sealed_yn").default(false), - picCode: varchar("pic_code", { length: 50 }), // 구매그룹에 대응시킴 (담당자 코드로 3자리) - - remark: text("remark"), - // 생성자 - - sentBy: integer("sent_by") - .references(() => users.id, { onDelete: "set null" }), - - - createdBy: integer("created_by") - .notNull() - .references(() => users.id, { onDelete: "set null" }), - - updatedBy: integer("updated_by") - .notNull() - .references(() => users.id, { onDelete: "set null" }), - - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - }, - -); +import { pgTable, varchar, timestamp, boolean } from "drizzle-orm/pg-core"; /** * 지불조건, 인코텀즈, 선적/하역(출발지, 도착지) 테이블은 Non-SAP에서 동기화 (Oracle DB to PostgreSQL) @@ -91,712 +30,4 @@ export const placeOfShipping = pgTable("place_of_shipping", { createdAt: timestamp("created_at").defaultNow().notNull(), }); -export const procurementRfqDetails = pgTable( - "procurement_rfq_details", - { - id: serial("id").primaryKey(), - procurementRfqsId: integer("procurement_rfqs_id") - .references(() => procurementRfqs.id, { onDelete: "set null" }), - vendorsId: integer("vendors_id") - .references(() => vendors.id, { onDelete: "set null" }), - currency: varchar("currency", { length: 10 }).default("USD"), - - // 정규화된 paymentTerms 참조 - paymentTermsCode: varchar("payment_terms_code", { length: 50 }) - .references(() => paymentTerms.code, { onDelete: "set null" }), - // paymentTerms 필드는 제거 (코드로 조회) - - // 정규화된 incoterms 참조 - incotermsCode: varchar("incoterms_code", { length: 20 }) - .references(() => incoterms.code, { onDelete: "set null" }), - incotermsDetail: varchar("incoterms_detail", { length: 255 }), - - deliveryDate: date("delivery_date", { mode: "date" }) - .$type<Date>() - .notNull(), - taxCode: varchar("tax_code", { length: 255 }).default("VV"), - placeOfShipping: varchar("place_of_shipping", { length: 255 }), - placeOfDestination: varchar("place_of_destination", { length: 255 }), - remark: text("remark"), - cancelReason: text("cancel_reason"), - updatedBy: integer("updated_by") - .notNull() - .references(() => users.id, { onDelete: "set null" }), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - materialPriceRelatedYn: boolean("material_price_related_yn").default(false), - } -); - -export const prItems = pgTable( - "pr_items", - { - id: serial("id").primaryKey(), - procurementRfqsId: integer("procurement_rfqs_id") - .references(() => procurementRfqs.id, { onDelete: "set null" }), - - rfqItem: varchar("rfq_item", { length: 50 }), // 단위 - prItem: varchar("pr_item", { length: 50 }), // 단위 - prNo: varchar("pr_no", { length: 50 }), // 단위 - - // itemId: integer("item_id") - // .references(() => items.id, { onDelete: "set null" }), - - materialCode: varchar("material_code", { length: 255 }), - materialCategory: varchar("material_category", { length: 255 }), - acc: varchar("acc", { length: 255 }), - materialDescription: varchar("material_description", { length: 255 }), - size: varchar("size", { length: 255 }), - deliveryDate: date("delivery_date", { mode: "date" }) - .$type<Date>(), - quantity: numeric("quantity", { precision: 12, scale: 2 }) - .$type<number>() - .default(1), - uom: varchar("uom", { length: 50 }), // 단위 - grossWeight: numeric("gross_weight", { precision: 12, scale: 2 }) - .$type<number>() - .default(1), - gwUom: varchar("gw_uom", { length: 50 }), // 단위 - - specNo: varchar("spec_no", { length: 255 }), - specUrl: varchar("spec_url", { length: 255 }), - trackingNo: varchar("tracking_no", { length: 255 }), - - majorYn: boolean("major_yn").default(false), - - projectDef: varchar("project_def", { length: 255 }), - projectSc: varchar("project_sc", { length: 255 }), - projectKl: varchar("project_kl", { length: 255 }), - projectLc: varchar("project_lc", { length: 255 }), - projectDl: varchar("project_dl", { length: 255 }), - - remark: text("remark"), - - }, -); - - -//view -export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => { - const createdByUser = alias(users, "created_by_user"); - const updatedByUser = alias(users, "updated_by_user"); - const sentByUser = alias(users, "sent_by_user"); - - return qb - .select({ - // Basic RFQ identification - id: sql<number>`${procurementRfqs.id}`.as("id"), - rfqCode: sql<string>`${procurementRfqs.rfqCode}`.as("rfq_code"), - series: sql<string | null>`${procurementRfqs.series}`.as("series"), - rfqSealedYn: sql<string | null>`${procurementRfqs.rfqSealedYn}`.as("rfq_sealed_yn"), - - // Project information - projectCode: sql<string | null>`${projects.code}`.as("project_code"), - projectName: sql<string | null>`${projects.name}`.as("project_name"), - - // Item information - itemCode: sql<string | null>`${procurementRfqs.itemCode}`.as("item_code"), - itemName: sql<string | null>`${procurementRfqs.itemName}`.as("item_name"), - - // Status and dates - status: sql<string>`${procurementRfqs.status}`.as("status"), - picCode: sql<string | null>`${procurementRfqs.picCode}`.as("pic_code"), - rfqSendDate: sql<Date | null>`${procurementRfqs.rfqSendDate}`.as("rfq_send_date"), - dueDate: sql<Date | null>`${procurementRfqs.dueDate}`.as("due_date"), - - // 가장 빠른 견적서 제출 날짜 추가 - earliestQuotationSubmittedAt: sql<Date | null>`( - SELECT MIN(submitted_at) - FROM procurement_vendor_quotations - WHERE rfq_id = ${procurementRfqs.id} - AND submitted_at IS NOT NULL - )`.as("earliest_quotation_submitted_at"), - - // Audit information - createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"), - sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"), - updatedAt: sql<Date>`${procurementRfqs.updatedAt}`.as("updated_at"), - updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"), - remark: sql<string | null>`${procurementRfqs.remark}`.as("remark"), - - // Related item information - majorItemMaterialCode: sql<string | null>`( - SELECT material_code - FROM pr_items - WHERE procurement_rfqs_id = ${procurementRfqs.id} - AND major_yn = true - LIMIT 1 - )`.as("major_item_material_code"), - - poNo: sql<string | null>`( - SELECT pr_no - FROM pr_items - WHERE procurement_rfqs_id = ${procurementRfqs.id} - AND major_yn = true - LIMIT 1 - )`.as("po_no"), - - prItemsCount: sql<number>`( - SELECT COUNT(*) - FROM pr_items - WHERE procurement_rfqs_id = ${procurementRfqs.id} - )`.as("pr_items_count") - }) - .from(procurementRfqs) - .leftJoin(projects, eq(procurementRfqs.projectId, projects.id)) - // .leftJoin(items, eq(procurementRfqs.itemId, items.id)) - .leftJoin(createdByUser, eq(procurementRfqs.createdBy, createdByUser.id)) - .leftJoin(updatedByUser, eq(procurementRfqs.updatedBy, updatedByUser.id)) - .leftJoin(sentByUser, eq(procurementRfqs.sentBy, sentByUser.id)); -}); - -// 수정된 pr_items_view -export const prItemsView = pgView("pr_items_view").as((qb) => { - return qb - .select({ - id: prItems.id, - procurementRfqsId: prItems.procurementRfqsId, - rfqItem: prItems.rfqItem, - prItem: prItems.prItem, - prNo: prItems.prNo, - // itemId: prItems.itemId, - materialCode: prItems.materialCode, - materialCategory: prItems.materialCategory, - acc: prItems.acc, - materialDescription: prItems.materialDescription, - size: prItems.size, - deliveryDate: prItems.deliveryDate, - quantity: prItems.quantity, - uom: prItems.uom, - grossWeight: prItems.grossWeight, // 필드명 수정 - gwUom: prItems.gwUom, - specNo: prItems.specNo, - specUrl: prItems.specUrl, - trackingNo: prItems.trackingNo, - majorYn: prItems.majorYn, - projectDef: prItems.projectDef, - projectSc: prItems.projectSc, - projectKl: prItems.projectKl, - projectLc: prItems.projectLc, - projectDl: prItems.projectDl, - remark: prItems.remark, - rfqCode: procurementRfqs.rfqCode, - itemCode: procurementRfqs.itemCode, - itemName: procurementRfqs.itemName - }) - .from(prItems) - .leftJoin(procurementRfqs, eq(prItems.procurementRfqsId, procurementRfqs.id)) -}); - -export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").as((qb) => { - // 기존 별칭 정의 유지 - const rfqDetailsTable = alias(procurementRfqDetails, "rfq_details"); - const rfqsTable = alias(procurementRfqs, "rfqs"); - const projectsTable = alias(projects, "projects"); - const vendorsTable = alias(vendors, "vendors"); - const paymentTermsTable = alias(paymentTerms, "payment_terms"); - const incotermsTable = alias(incoterms, "incoterms"); - const updatedByUser = alias(users, "updated_by_user"); - - return qb - .select({ - // procurementRfqDetails 필드 - detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"), - rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"), - rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"), - - // 프로젝트 관련 필드 - projectCode: sql<string | null>`${projectsTable.code}`.as("project_code"), - projectName: sql<string | null>`${projectsTable.name}`.as("project_name"), - - // 아이템 관련 필드 - itemCode: sql<string | null>`${rfqsTable.itemCode}`.as("item_code"), - itemName: sql<string | null>`${rfqsTable.itemName}`.as("item_name"), - - // 벤더 관련 필드 - vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"), - vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"), - vendorId: sql<string | null>`${vendorsTable.id}`.as("vendor_id"), - vendorCountry: sql<string | null>`${vendorsTable.country}`.as("vendor_country"), - - // RFQ 상세 정보 필드 - currency: sql<string | null>`${rfqDetailsTable.currency}`.as("currency"), - paymentTermsCode: sql<string | null>`${paymentTermsTable.code}`.as("payment_terms_code"), - paymentTermsDescription: sql<string | null>`${paymentTermsTable.description}`.as("payment_terms_description"), - incotermsCode: sql<string | null>`${incotermsTable.code}`.as("incoterms_code"), - incotermsDescription: sql<string | null>`${incotermsTable.description}`.as("incoterms_description"), - incotermsDetail: sql<string | null>`${rfqDetailsTable.incotermsDetail}`.as("incoterms_detail"), - deliveryDate: sql<Date | null>`${rfqDetailsTable.deliveryDate}`.as("delivery_date"), - taxCode: sql<string | null>`${rfqDetailsTable.taxCode}`.as("tax_code"), - placeOfShipping: sql<string | null>`${rfqDetailsTable.placeOfShipping}`.as("place_of_shipping"), - placeOfDestination: sql<string | null>`${rfqDetailsTable.placeOfDestination}`.as("place_of_destination"), - materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"), - - // 업데이트 관련 필드 - updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"), - updatedAt: sql<Date | null>`${rfqDetailsTable.updatedAt}`.as("updated_at"), - - // 추가적으로 pr_items 개수도 카운트하는 필드 추가 - prItemsCount: sql<number>`( - SELECT COUNT(*) - FROM pr_items - WHERE procurement_rfqs_id = ${rfqsTable.id} - )`.as("pr_items_count"), - - // 메이저 아이템 개수도 카운트 - majorItemsCount: sql<number>`( - SELECT COUNT(*) - FROM pr_items - WHERE procurement_rfqs_id = ${rfqsTable.id} - AND major_yn = true - )`.as("major_items_count"), - - // 새로운 필드 추가: 코멘트 수 카운트 - commentCount: sql<number>`( - SELECT COUNT(*) - FROM procurement_rfq_comments - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - )`.as("comment_count"), - - // 새로운 필드 추가: 최근 코멘트 날짜 - lastCommentDate: sql<Date | null>`( - SELECT created_at - FROM procurement_rfq_comments - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - ORDER BY created_at DESC LIMIT 1 - )`.as("last_comment_date"), - - // 새로운 필드 추가: 벤더가 마지막으로 코멘트한 날짜 - lastVendorCommentDate: sql<Date | null>`( - SELECT created_at - FROM procurement_rfq_comments - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} AND is_vendor_comment = true - ORDER BY created_at DESC LIMIT 1 - )`.as("last_vendor_comment_date"), - - // 새로운 필드 추가: 첨부파일 수 카운트 - attachmentCount: sql<number>`( - SELECT COUNT(*) - FROM procurement_rfq_attachments - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - )`.as("attachment_count"), - - // 새로운 필드 추가: 견적서 제출 여부 - hasQuotation: sql<boolean>`( - SELECT COUNT(*) > 0 - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - )`.as("has_quotation"), - - // 새로운 필드 추가: 견적서 상태 - quotationStatus: sql<string | null>`( - SELECT status - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - ORDER BY created_at DESC LIMIT 1 - )`.as("quotation_status"), - - // 새로운 필드 추가: 견적서 총액 - quotationTotalPrice: sql<number | null>`( - SELECT total_price - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - ORDER BY created_at DESC LIMIT 1 - )`.as("quotation_total_price"), - - // 최신 견적서 버전 - quotationVersion: sql<number | null>`( - SELECT quotation_version - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - ORDER BY quotation_version DESC LIMIT 1 - )`.as("quotation_version"), - - // 총 견적서 버전 수 - quotationVersionCount: sql<number>`( - SELECT COUNT(DISTINCT quotation_version) - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - )`.as("quotation_version_count"), - - // 마지막 견적서 생성 날짜 - lastQuotationDate: sql<Date | null>`( - SELECT created_at - FROM procurement_vendor_quotations - WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} - ORDER BY quotation_version DESC LIMIT 1 - )`.as("last_quotation_date"), - }) - .from(rfqDetailsTable) - // 기존 조인 유지 - .leftJoin(rfqsTable, eq(rfqDetailsTable.procurementRfqsId, rfqsTable.id)) - .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id)) - // .leftJoin(itemsTable, eq(rfqsTable.itemId, itemsTable.id)) - .leftJoin(vendorsTable, eq(rfqDetailsTable.vendorsId, vendorsTable.id)) - .leftJoin(paymentTermsTable, eq(rfqDetailsTable.paymentTermsCode, paymentTermsTable.code)) - .leftJoin(incotermsTable, eq(rfqDetailsTable.incotermsCode, incotermsTable.code)) - .leftJoin(updatedByUser, eq(rfqDetailsTable.updatedBy, updatedByUser.id)); -}); - -export const procurementAttachments = pgTable( - "procurement_attachments", - { - id: serial("id").primaryKey(), - attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // 'RFQ_COMMON', 'VENDOR_SPECIFIC' - procurementRfqsId: integer("procurement_rfqs_id") - .references(() => procurementRfqs.id, { onDelete: "cascade" }), - procurementRfqDetailsId: integer("procurement_rfq_details_id") - .references(() => procurementRfqDetails.id, { onDelete: "cascade" }), - 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(), - }, - (table) => ({ - attachmentTypeCheck: check( - "attachment_type_check", - sql`${table.procurementRfqsId} IS NOT NULL OR ${table.procurementRfqDetailsId} IS NOT NULL` - ) - }) -); - -export type ProcurementRfqsView = typeof procurementRfqsView.$inferSelect; -export type PrItemsView = typeof prItemsView.$inferSelect; -export type ProcurementRfqDetailsView = typeof procurementRfqDetailsView.$inferSelect; - - -//vendor response -export const procurementVendorQuotations = pgTable( - "procurement_vendor_quotations", - { - id: serial("id").primaryKey(), - rfqId: integer("rfq_id") - .notNull() - .references(() => procurementRfqs.id, { onDelete: "cascade" }), - vendorId: integer("vendor_id") - .notNull() - .references(() => vendors.id, { onDelete: "set null" }), - - // 견적 요약 정보 - quotationCode: varchar("quotation_code", { length: 50 }), - quotationVersion: integer("quotation_version").default(1), - totalItemsCount: integer("total_items_count").default(0), - subTotal: numeric("sub_total").default("0"), - taxTotal: numeric("tax_total").default("0"), - discountTotal: numeric("discount_total").default("0"), - totalPrice: numeric("total_price").default("0"), - currency: varchar("currency", { length: 10 }).default("USD"), - - // 견적 유효성 및 배송 정보 - validUntil: date("valid_until", { mode: "date" }).$type<Date>(), - estimatedDeliveryDate: date("estimated_delivery_date", { mode: "date" }).$type<Date>(), - - // 지불 조건 등 상세 정보 - paymentTermsCode: varchar("payment_terms_code", { length: 50 }) - .references(() => paymentTerms.code, { onDelete: "set null" }), - incotermsCode: varchar("incoterms_code", { length: 20 }) - .references(() => incoterms.code, { onDelete: "set null" }), - incotermsDetail: varchar("incoterms_detail", { length: 255 }), - - // 상태 관리 - status: varchar("status", { length: 30 }) - .$type<"Draft" | "Submitted" | "Revised" | "Rejected" | "Accepted">() - .default("Draft") - .notNull(), - - // 기타 정보 - remark: text("remark"), - rejectionReason: text("rejection_reason"), - submittedAt: timestamp("submitted_at"), - acceptedAt: timestamp("accepted_at"), - - // 감사 필드 - createdBy: integer("created_by"), - updatedBy: integer("updated_by"), - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - } -); - -export const procurementRfqComments = pgTable( - "procurement_rfq_comments", - { - id: serial("id").primaryKey(), - rfqId: integer("rfq_id") - .notNull() - .references(() => procurementRfqs.id, { onDelete: "cascade" }), - vendorId: integer("vendor_id") - .references(() => vendors.id, { onDelete: "set null" }), - userId: integer("user_id") - .references(() => users.id, { onDelete: "set null" }), - content: text("content").notNull(), - isVendorComment: boolean("is_vendor_comment").default(false), - isRead: boolean("is_read").default(false), // 읽음 상태 추가 - parentCommentId: integer("parent_comment_id"), - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - }, - // 자기참조 FK 정의 - (table) => { - return { - parentFk: foreignKey({ - columns: [table.parentCommentId], - foreignColumns: [table.id], - }).onDelete("set null"), - }; - } -); - -export const procurementRfqAttachments = pgTable( - "procurement_rfq_attachments", - { - id: serial("id").primaryKey(), - rfqId: integer("rfq_id") - .notNull() - .references(() => procurementRfqs.id, { onDelete: "cascade" }), - commentId: integer("comment_id") - .references(() => procurementRfqComments.id, { onDelete: "cascade" }), - quotationId: integer("quotation_id") - .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }), - fileName: varchar("file_name", { length: 255 }).notNull(), - fileSize: integer("file_size").notNull(), - fileType: varchar("file_type", { length: 100 }), - filePath: varchar("file_path", { length: 500 }).notNull(), - isVendorUpload: boolean("is_vendor_upload").default(false), - uploadedBy: integer("uploaded_by") - .references(() => users.id, { onDelete: "set null" }), - vendorId: integer("vendor_id") - .references(() => vendors.id, { onDelete: "set null" }), - uploadedAt: timestamp("uploaded_at").defaultNow().notNull(), - } -); - -export const procurementQuotationItems = pgTable( - "procurement_quotation_items", - { - id: serial("id").primaryKey(), - quotationId: integer("quotation_id") - .notNull() - .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }), - - // PR 아이템과의 연결 추가 - prItemId: integer("pr_item_id") - .notNull() - .references(() => prItems.id, { onDelete: "cascade" }), - - // 원본 PR 아이템 정보 참조 (읽기 전용) - materialCode: varchar("material_code", { length: 50 }), - materialDescription: varchar("material_description", { length: 255 }), - quantity: numeric("quantity").notNull(), - uom: varchar("uom", { length: 20 }), - - // 벤더가 입력하는 정보 - unitPrice: numeric("unit_price").notNull(), - totalPrice: numeric("total_price").notNull(), - currency: varchar("currency", { length: 10 }).default("USD"), - vendorMaterialCode: varchar("vendor_material_code", { length: 50 }), - vendorMaterialDescription: varchar("vendor_material_description", { length: 255 }), - - // 배송 관련 정보 - deliveryDate: date("delivery_date", { mode: "date" }).$type<Date>(), - leadTimeInDays: integer("lead_time_in_days"), - - // 세금 및 기타 정보 - taxRate: numeric("tax_rate"), - taxAmount: numeric("tax_amount"), - discountRate: numeric("discount_rate"), - discountAmount: numeric("discount_amount"), - - // 기타 정보 - remark: text("remark"), - isAlternative: boolean("is_alternative").default(false), - isRecommended: boolean("is_recommended").default(false), - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - } -); - - -// procurementRfqComments 테이블의 관계 정의 - 타입 명시 -export const procurementRfqCommentsRelations = relations( - procurementRfqComments, - ({ one, many }) => ({ - user: one(users, { - fields: [procurementRfqComments.userId], - references: [users.id], - }), - vendor: one(vendors, { - fields: [procurementRfqComments.vendorId], - references: [vendors.id], - }), - rfq: one(procurementRfqs, { - fields: [procurementRfqComments.rfqId], - references: [procurementRfqs.id], - }), - parentComment: one(procurementRfqComments, { - fields: [procurementRfqComments.parentCommentId], - references: [procurementRfqComments.id], - relationName: "commentHierarchy", - }), - childComments: many(procurementRfqComments, { relationName: "commentHierarchy" }), - attachments: many(procurementRfqAttachments, { relationName: "commentAttachments" }), - }) -); - -export const procurementRfqAttachmentsRelations = relations( - procurementRfqAttachments, - ({ one }) => ({ - comment: one(procurementRfqComments, { - fields: [procurementRfqAttachments.commentId], - references: [procurementRfqComments.id], - relationName: "commentAttachments", - }), - rfq: one(procurementRfqs, { - fields: [procurementRfqAttachments.rfqId], - references: [procurementRfqs.id], - }), - uploader: one(users, { - fields: [procurementRfqAttachments.uploadedBy], - references: [users.id], - }), - vendor: one(vendors, { - fields: [procurementRfqAttachments.vendorId], - references: [vendors.id], - }), - }) -); - - -export const procurementRfqsRelations = relations( - procurementRfqs, - ({ one, many }) => ({ - project: one(projects, { - fields: [procurementRfqs.projectId], - references: [projects.id], - }), - // item: one(items, { - // fields: [procurementRfqs.itemId], - // references: [items.id], - // }), - createdByUser: one(users, { - fields: [procurementRfqs.createdBy], - references: [users.id], - relationName: "rfqCreator", - }), - updatedByUser: one(users, { - fields: [procurementRfqs.updatedBy], - references: [users.id], - relationName: "rfqUpdater", - }), - rfqDetails: many(procurementRfqDetails), - prItems: many(prItems, { relationName: "rfqPrItems" }), - quotations: many(procurementVendorQuotations), - - }) -); - -export const prItemsRelations = relations( - prItems, - ({ one }) => ({ - rfq: one(procurementRfqs, { - fields: [prItems.procurementRfqsId], - references: [procurementRfqs.id], - relationName: "rfqPrItems" - }), - }) -); - -// procurementRfqDetails 테이블의 관계 정의 -export const procurementRfqDetailsRelations = relations( - procurementRfqDetails, - ({ one }) => ({ - rfq: one(procurementRfqs, { - fields: [procurementRfqDetails.procurementRfqsId], - references: [procurementRfqs.id], - }), - vendor: one(vendors, { - fields: [procurementRfqDetails.vendorsId], - references: [vendors.id], - }), - paymentTerms: one(paymentTerms, { - fields: [procurementRfqDetails.paymentTermsCode], - references: [paymentTerms.code], - }), - incoterms: one(incoterms, { - fields: [procurementRfqDetails.incotermsCode], - references: [incoterms.code], - }), - updatedByUser: one(users, { - fields: [procurementRfqDetails.updatedBy], - references: [users.id], - }), - }) -); - -export const vendorsRelations = relations( - vendors, - ({ many }) => ({ - users: many(users, { - relationName: "vendorUsers" - }), - quotations: many(procurementVendorQuotations), - }) -); - -export const usersRelations = relations( - users, - ({ one }) => ({ - vendor: one(vendors, { - fields: [users.companyId], - references: [vendors.id], - relationName: "vendorUsers" - }), - }) -); - -export const procurementVendorQuotationsRelations = relations( - procurementVendorQuotations, - ({ one, many }) => ({ - rfq: one(procurementRfqs, { - fields: [procurementVendorQuotations.rfqId], - references: [procurementRfqs.id], - // relationName 제거 - }), - vendor: one(vendors, { - fields: [procurementVendorQuotations.vendorId], - references: [vendors.id], - // relationName 제거 - }), - items: many(procurementQuotationItems), - paymentTerms: one(paymentTerms, { - fields: [procurementVendorQuotations.paymentTermsCode], - references: [paymentTerms.code], - }), - incoterms: one(incoterms, { - fields: [procurementVendorQuotations.incotermsCode], - references: [incoterms.code], - }), - }) -); - -export const procurementQuotationItemsRelations = relations( - procurementQuotationItems, - ({ one }) => ({ - quotation: one(procurementVendorQuotations, { - fields: [procurementQuotationItems.quotationId], - references: [procurementVendorQuotations.id], - }), - prItem: one(prItems, { - fields: [procurementQuotationItems.prItemId], - references: [prItems.id], - }), - }) -); - -export type ProcurementVendorQuotations = typeof procurementVendorQuotations.$inferSelect; export type Incoterm = typeof incoterms.$inferSelect; diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts index e690ce4b..20cefba3 100644 --- a/db/schema/rfqLastTBE.ts +++ b/db/schema/rfqLastTBE.ts @@ -1,6 +1,6 @@ import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb, uniqueIndex, index } from "drizzle-orm/pg-core"; import { eq, sql, relations } from "drizzle-orm"; -import { rfqsLast, rfqLastDetails, rfqLastAttachments, rfqLastAttachmentRevisions } from "./rfqLast"; +import { rfqsLast, rfqLastDetails, rfqLastAttachments, rfqLastAttachmentRevisions, rfqPrItems } from "./rfqLast"; import { users } from "./users"; import { vendors } from "./vendors"; import { rfqLastVendorAttachments } from "./rfqVendor"; @@ -393,23 +393,23 @@ export const tbeSessionSummaryView = pgView("tbe_session_summary_view").as((qb) // 문서 검토 통계 totalDocuments: sql<number>`( SELECT COUNT(*) - FROM rfq_last_tbe_document_reviews - WHERE tbe_session_id = ${tbeSession.id} + FROM ${rfqLastTbeDocumentReviews} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${tbeSession.id} )`.as("total_documents"), reviewedDocuments: sql<number>`( SELECT COUNT(*) - FROM rfq_last_tbe_document_reviews - WHERE tbe_session_id = ${tbeSession.id} - AND review_status IN ('검토완료', '승인') + FROM ${rfqLastTbeDocumentReviews} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${tbeSession.id} + AND ${rfqLastTbeDocumentReviews.reviewStatus} IN ('검토완료', '승인') )`.as("reviewed_documents"), // PDFTron 코멘트 통계 totalComments: sql<number>`( - SELECT COUNT(*) - FROM rfq_last_tbe_pdftron_comments pc - JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id - WHERE dr.tbe_session_id = ${tbeSession.id} + SELECT COALESCE(SUM((${rfqLastTbePdftronComments.commentSummary}->>'total')::int), 0) + FROM ${rfqLastTbePdftronComments} + JOIN ${rfqLastTbeDocumentReviews} ON ${rfqLastTbePdftronComments.documentReviewId} = ${rfqLastTbeDocumentReviews.id} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${tbeSession.id} )`.as("total_comments"), @@ -537,54 +537,53 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => { // PR 아이템 수 prItemsCount: sql<number>`( SELECT COUNT(*) - FROM rfq_pr_items - WHERE rfqs_last_id = ${rfqsLast.id} + FROM ${rfqPrItems} + WHERE ${rfqPrItems.rfqsLastId} = ${rfqsLast.id} )`.as("pr_items_count"), majorItemsCount: sql<number>`( SELECT COUNT(*) - FROM rfq_pr_items - WHERE rfqs_last_id = ${rfqsLast.id} - AND major_yn = true + FROM ${rfqPrItems} + WHERE ${rfqPrItems.rfqsLastId} = ${rfqsLast.id} + AND ${rfqPrItems.majorYn} = true )`.as("major_items_count"), // 구매자 문서 수 (설계 문서) buyerDocumentsCount: sql<number>`( SELECT COUNT(*) - FROM rfq_last_tbe_document_reviews - WHERE tbe_session_id = ${rfqLastTbeSessions.id} - AND document_source = 'buyer' + FROM ${rfqLastTbeDocumentReviews} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${rfqLastTbeSessions.id} + AND ${rfqLastTbeDocumentReviews.documentSource} = 'buyer' )`.as("buyer_documents_count"), // 벤더 문서 수 vendorDocumentsCount: sql<number>`( SELECT COUNT(*) - FROM rfq_last_tbe_vendor_documents - WHERE tbe_session_id = ${rfqLastTbeSessions.id} + FROM ${rfqLastTbeVendorDocuments} + WHERE ${rfqLastTbeVendorDocuments.tbeSessionId} = ${rfqLastTbeSessions.id} )`.as("vendor_documents_count"), // 검토 완료 문서 수 reviewedDocumentsCount: sql<number>`( SELECT COUNT(*) - FROM rfq_last_tbe_document_reviews - WHERE tbe_session_id = ${rfqLastTbeSessions.id} - AND review_status IN ('검토완료', '승인') + FROM ${rfqLastTbeDocumentReviews} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${rfqLastTbeSessions.id} + AND ${rfqLastTbeDocumentReviews.reviewStatus} IN ('검토완료', '승인') )`.as("reviewed_documents_count"), // PDFTron 코멘트 수 totalCommentsCount: sql<number>`( - SELECT COUNT(*) - FROM rfq_last_tbe_pdftron_comments pc - JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id - WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id} + SELECT COALESCE(SUM((${rfqLastTbePdftronComments.commentSummary}->>'total')::int), 0) + FROM ${rfqLastTbePdftronComments} + JOIN ${rfqLastTbeDocumentReviews} ON ${rfqLastTbePdftronComments.documentReviewId} = ${rfqLastTbeDocumentReviews.id} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${rfqLastTbeSessions.id} )`.as("total_comments_count"), unresolvedCommentsCount: sql<number>`( - SELECT COUNT(*) - FROM rfq_last_tbe_pdftron_comments pc - JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id - WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id} - AND pc.status = 'open' + SELECT COALESCE(SUM((${rfqLastTbePdftronComments.commentSummary}->>'open')::int), 0) + FROM ${rfqLastTbePdftronComments} + JOIN ${rfqLastTbeDocumentReviews} ON ${rfqLastTbePdftronComments.documentReviewId} = ${rfqLastTbeDocumentReviews.id} + WHERE ${rfqLastTbeDocumentReviews.tbeSessionId} = ${rfqLastTbeSessions.id} )`.as("unresolved_comments_count"), // 타임스탬프 diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts index d587d441..c194cf61 100644 --- a/db/schema/vendors.ts +++ b/db/schema/vendors.ts @@ -114,8 +114,7 @@ export const vendorPossibleItems = pgTable("vendor_possible_items", { vendorId: integer("vendor_id").notNull().references(() => vendors.id), // itemId: integer("item_id"), // 별도 item 테이블 연동시 itemCode: varchar("item_code", { length: 100 }) - .notNull() - .references(() => items.itemCode, { onDelete: "cascade" }), + .notNull(), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); |