summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/bRfq.ts680
-rw-r--r--db/schema/index.ts3
-rw-r--r--db/schema/ocr.ts10
-rw-r--r--db/schema/vendorDocu.ts31
4 files changed, 721 insertions, 3 deletions
diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts
new file mode 100644
index 00000000..5e211448
--- /dev/null
+++ b/db/schema/bRfq.ts
@@ -0,0 +1,680 @@
+import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core";
+import { eq, sql, and } 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),
+
+ 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),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 512 }).notNull(),
+ fileSize: integer("file_size"),
+ fileType: varchar("file_type", { length: 100 }),
+ description: varchar("description", { length: 500 }),
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" })
+ .notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ },
+ );
+// 1. 첨부파일 리비전 관리 테이블
+export const bRfqAttachmentRevisions = pgTable(
+ "b_rfq_attachment_revisions",
+ {
+ id: serial("id").primaryKey(),
+ attachmentId: integer("attachment_id")
+ .notNull()
+ .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
+
+ revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 512 }).notNull(),
+ fileSize: integer("file_size"),
+ fileType: varchar("file_type", { length: 100 }),
+
+ isLatest: boolean("is_latest").notNull().default(true),
+ revisionComment: text("revision_comment"),
+
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" })
+ .notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ }, (t) => ({
+ latestRevisionIdx: uniqueIndex('latest_revision_idx').on(
+ t.attachmentId.asc(),
+ t.isLatest.asc(),
+ ),
+ }));
+
+// 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"),
+
+ // 응답 관련 날짜
+ requestedAt: timestamp("requested_at").notNull(),
+ respondedAt: timestamp("responded_at"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },(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 }),
+ 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"),
+ 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,
+ 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.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 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 vendorResponseSummaryView = 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;
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 38a27393..9ed0c59e 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -16,6 +16,7 @@ export * from './procurementRFQ';
export * from './setting';
export * from './techSales';
export * from './ocr';
+export * from './bRfq';
// MDG SOAP 수신용
-export * from './MDG/modelMaster'
+// export * from './MDG/modelMaster'
diff --git a/db/schema/ocr.ts b/db/schema/ocr.ts
index 289eb297..12382aa4 100644
--- a/db/schema/ocr.ts
+++ b/db/schema/ocr.ts
@@ -9,9 +9,10 @@ import {
boolean,
jsonb,
text,
- serial
+ serial , uniqueIndex
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
+import { users } from './users';
// OCR 세션 테이블 (전체 처리 정보)
export const ocrSessions = pgTable('ocr_sessions', {
@@ -57,8 +58,13 @@ export const ocrRows = pgTable('ocr_rows', {
confidence: decimal('confidence', { precision: 5, scale: 4 }), // 0.0000 ~ 1.0000
sourceTable: integer('source_table'),
sourceRow: integer('source_row'),
+ userId: integer("user_id")
+ .references(() => users.id, { onDelete: "set null" }),
createdAt: timestamp('created_at').notNull().defaultNow(),
-});
+}, (t) => ({
+ // tableId + reportNo + no 조합이 유일
+ uniqReportAndNo: uniqueIndex('idx_ocr_report_no_unique').on(t.reportNo, t.no, t.tagNo, t.jointNo, t.jointType),
+}));
// 회전 시도 결과
export const ocrRotationAttempts = pgTable('ocr_rotation_attempts', {
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index ebae7630..a3138fd5 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -141,6 +141,8 @@ export const revisions = pgTable(
uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"),
uploaderId: integer("uploader_id"),
uploaderName: varchar("uploader_name", { length: 100 }),
+
+ usage:varchar("usage", { length: 100 }),
// 확장된 상태 관리
revisionStatus: varchar("revision_status", { length: 50 })
@@ -175,6 +177,7 @@ export const revisions = pgTable(
)
// 기존 documentAttachments (변경 없음)
+
export const documentAttachments = pgTable(
"document_attachments",
{
@@ -186,6 +189,14 @@ export const documentAttachments = pgTable(
filePath: varchar("file_path", { length: 1024 }).notNull(),
fileType: varchar("file_type", { length: 50 }),
fileSize: integer("file_size"),
+
+ // DOLCE 연동 관련 필드 추가
+ uploadId: varchar("upload_id", { length: 36 }), // UUID 형태
+ fileId: varchar("file_id", { length: 36 }), // UUID 형태
+ uploadedBy: varchar("uploaded_by", { length: 255 }), // userId 저장
+ dolceFilePath: varchar("dolce_file_path", { length: 1024 }), // DOLCE에서 반환하는 파일 경로
+ uploadedAt: timestamp("uploaded_at"), // DOLCE 업로드 성공 시간
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
}
@@ -196,6 +207,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
// 기본 문서 정보
documentId: integer("document_id").notNull(),
docNumber: varchar("doc_number", { length: 100 }).notNull(),
+ drawingKind: varchar("drawing_kind", { length: 50 }),
vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), // ✅ 벤더 문서 번호 추가
title: varchar("title", { length: 255 }).notNull(),
pic: varchar("pic", { length: 50 }),
@@ -207,6 +219,14 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
projectCode: varchar("project_code", { length: 50 }),
vendorName: varchar("vendor_name", { length: 255 }),
vendorCode: varchar("vendor_code", { length: 50 }),
+
+ // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
+ cGbn: varchar("c_gbn", { length: 50 }), // CGbn
+ dGbn: varchar("d_gbn", { length: 50 }), // DGbn
+ degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
+ deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
+ jGbn: varchar("j_gbn", { length: 50 }), // JGbn
+ sGbn: varchar("s_gbn", { length: 50 }), // SGbn
// 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
currentStageId: integer("current_stage_id"),
@@ -253,6 +273,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
uploaderId: number | null;
uploaderName: string | null;
comment: string | null;
+ usage: string | null;
revisionStatus: string;
submittedDate: string | null;
approvedDate: string | null;
@@ -377,6 +398,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
'uploaderId', r.uploader_id,
'uploaderName', r.uploader_name,
'comment', r.comment,
+ 'usage', r.usage,
'revisionStatus', r.revision_status,
'submittedDate', r.submitted_date,
'uploadedAt', r.uploaded_at,
@@ -432,12 +454,21 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
SELECT
d.id as document_id,
d.doc_number,
+ d.drawing_kind,
d.vendor_doc_number, -- ✅ 벤더 문서 번호 추가
d.title,
d.pic,
d.status,
d.issued_date,
d.contract_id,
+
+ d.c_gbn,
+ d.d_gbn,
+ d.degree_gbn,
+ d.dept_gbn,
+ d.s_gbn,
+
+
-- ✅ 프로젝트 및 벤더 정보 추가
p.code as project_code,
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 06:28:29 +0000