diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/index.ts | 2 | ||||
| -rw-r--r-- | db/schema/rfqLast.ts | 764 | ||||
| -rw-r--r-- | db/schema/rfqVendor.ts | 382 |
3 files changed, 1148 insertions, 0 deletions
diff --git a/db/schema/index.ts b/db/schema/index.ts index 7637d247..bcabac1b 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -36,6 +36,8 @@ export * from './consent'; export * from './bidding'; export * from './vendorRegistrations'; export * from './compliance'; +export * from './rfqLast'; +export * from './rfqVendor'; // 부서별 도메인 할당 관리 export * from './departmentDomainAssignments'; diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts new file mode 100644 index 00000000..34cb7bf3 --- /dev/null +++ b/db/schema/rfqLast.ts @@ -0,0 +1,764 @@ +import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; +import { eq, sql, relations } from "drizzle-orm"; +import { projects } from "./projects"; +import { users } from "./users"; +import { vendors } from "./vendors"; +import { incoterms, paymentTerms } from "./procurementRFQ"; + + +export type RfqStatus = + | "RFQ 생성" + | "구매담당지정" + | "견적요청문서 확정" + | "Short List 확정" + | "TBE 완료" + | "RFQ 발송" + | "견적접수" + | "최종업체선정"; + + +export const rfqsLast = pgTable( + "rfqs_last", + { + id: serial("id").primaryKey(), + + rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001" + + projectId: integer("project_id") + .references(() => projects.id, { onDelete: "set null" }), + + // 자재코드, 자재명: 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<RfqStatus>() + .default("RFQ 생성") + .notNull(), + + rfqSealedYn: boolean("rfq_sealed_yn").default(false), + + EngPicName: varchar("eng_pic_name", { length: 50 }), + + packageNo: varchar("package_no", { length: 50 }), + packageName: varchar("package_name", { length: 255 }), + + remark: text("remark"), + + pic: integer("pic") + .references(() => users.id, { onDelete: "set null" }), + picCode: varchar("pic_code", { length: 50 }), // 구매그룹에 대응시킴 (담당자 코드로 3자리) + picName: varchar("pic_name", { length: 50 }), + + 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(), + + //일반견적 추가 필드 + rfqType: varchar("rfq_type", { length: 255 }), + rfqTitle: varchar("rfq_title", { length: 255 }), + + //ITB 추가 필드 + projectCompany: varchar("project_company", { length: 255 }), + projectFlag: varchar("project_flag", { length: 255 }), + projectSite: varchar("project_site", { length: 255 }), + smCode: varchar("sm_code", { length: 255 }), + + //RFQ 추가 필드 + prNumber: varchar("pr_number", { length: 255 }), + prIssueDate: date("pr_issue_date", { mode: "date" }) + .$type<Date | null>(), + // SS, II, null 값을 가질 수 있음. + // SS = 시리즈 통합, II = 품목 통합, 공란 = 통합 없음 + series: varchar("series", { length: 50 }), + + }, + +); + + + +export const rfqLastDetails = pgTable( + "rfq_last_details", + { + id: serial("id").primaryKey(), + rfqsLastId: integer("rfqs_last_id") + .references(() => rfqsLast.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(), + + contractDuration: varchar("contract_duration", { length: 255 }), + + + 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(), + + shortList: boolean('short_list').notNull().default(false), + returnYn: boolean('return_yn').notNull().default(false), + returnedAt: timestamp("returned_at").defaultNow(), + + + prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false), + generalGtcYn: boolean('general_gtc_yn').notNull().default(false), + ndaYn: boolean('nda_yn').notNull().default(false), + agreementYn: boolean('agreement_yn').notNull().default(false), + + materialPriceRelatedYn: boolean("material_price_related_yn").default(false), + sparepartYn: boolean("sparepart_yn").default(false), + firstYn: boolean("first_yn").default(false), + + firstDescription: text("first_description"), + sparepartDescription: text("sparepart_escription"), + + + } +); + +export const rfqPrItems = pgTable( + "rfq_pr_items", + { + id: serial("id").primaryKey(), + rfqsLastId: integer("rfqs_last_id") + .references(() => rfqsLast.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 rfqsLastView = pgView("rfqs_last_view").as((qb) => { + const createdByUser = alias(users, "created_by_user"); + const updatedByUser = alias(users, "updated_by_user"); + const sentByUser = alias(users, "sent_by_user"); + const picUser = alias(users, "pic_user"); + + return qb + .select({ + // Basic RFQ identification + id: sql<number>`${rfqsLast.id}`.as("id"), + rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"), + series: sql<string | null>`${rfqsLast.series}`.as("series"), + rfqSealedYn: sql<boolean | null>`${rfqsLast.rfqSealedYn}`.as("rfq_sealed_yn"), + + // RFQ Type and Title (일반견적 필드) + rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"), + rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"), + + // ITB 관련 필드 + projectCompany: sql<string | null>`${rfqsLast.projectCompany}`.as("project_company"), + projectFlag: sql<string | null>`${rfqsLast.projectFlag}`.as("project_flag"), + projectSite: sql<string | null>`${rfqsLast.projectSite}`.as("project_site"), + smCode: sql<string | null>`${rfqsLast.smCode}`.as("sm_code"), + + // RFQ 추가 필드 + prNumber: sql<string | null>`${rfqsLast.prNumber}`.as("pr_number"), + prIssueDate: sql<Date | null>`${rfqsLast.prIssueDate}`.as("pr_issue_date"), + + // Project information + projectId: sql<number | null>`${rfqsLast.projectId}`.as("project_id"), + projectCode: sql<string | null>`${projects.code}`.as("project_code"), + projectName: sql<string | null>`${projects.name}`.as("project_name"), + + // Item information + itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"), + itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"), + + // Package information + packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"), + packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"), + + // Engineering PIC + engPicName: sql<string | null>`${rfqsLast.EngPicName}`.as("eng_pic_name"), + + // Status and dates + status: sql<string>`${rfqsLast.status}`.as("status"), + rfqSendDate: sql<Date | null>`${rfqsLast.rfqSendDate}`.as("rfq_send_date"), + dueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("due_date"), + + // PIC information + picId: sql<number | null>`${rfqsLast.pic}`.as("pic_id"), + picCode: sql<string | null>`${rfqsLast.picCode}`.as("pic_code"), + picName: sql<string | null>`${rfqsLast.picName}`.as("pic_name"), + picUserName: sql<string | null>`${picUser.name}`.as("pic_user_name"), + + // 가장 빠른 견적서 제출 날짜 + earliestQuotationSubmittedAt: sql<Date | null>`( + SELECT MIN(submitted_at) + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsLast.id} + AND submitted_at IS NOT NULL + )`.as("earliest_quotation_submitted_at"), + + // Vendor details count + vendorCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_details + WHERE rfqs_last_id = ${rfqsLast.id} + )`.as("vendor_count"), + + // Short listed vendor count + shortListedVendorCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_details + WHERE rfqs_last_id = ${rfqsLast.id} + AND short_list = true + )`.as("short_listed_vendor_count"), + + // Quotation received count + quotationReceivedCount: sql<number>`( + SELECT COUNT(DISTINCT vendor_id) + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsLast.id} + AND submitted_at IS NOT NULL + )`.as("quotation_received_count"), + + // Audit information + createdBy: sql<number>`${rfqsLast.createdBy}`.as("created_by"), + createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"), + createdAt: sql<Date>`${rfqsLast.createdAt}`.as("created_at"), + + sentBy: sql<number | null>`${rfqsLast.sentBy}`.as("sent_by"), + sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"), + + updatedBy: sql<number>`${rfqsLast.updatedBy}`.as("updated_by"), + updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"), + updatedAt: sql<Date>`${rfqsLast.updatedAt}`.as("updated_at"), + + remark: sql<string | null>`${rfqsLast.remark}`.as("remark"), + + // PR Items related information + majorItemMaterialCode: sql<string | null>`( + SELECT material_code + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_code"), + + majorItemPrNo: sql<string | null>`( + SELECT pr_no + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_pr_no"), + + prItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${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 + )`.as("major_items_count") + }) + .from(rfqsLast) + .leftJoin(projects, eq(rfqsLast.projectId, projects.id)) + .leftJoin(createdByUser, eq(rfqsLast.createdBy, createdByUser.id)) + .leftJoin(updatedByUser, eq(rfqsLast.updatedBy, updatedByUser.id)) + .leftJoin(sentByUser, eq(rfqsLast.sentBy, sentByUser.id)) + .leftJoin(picUser, eq(rfqsLast.pic, picUser.id)); +}); + +// Updated rfqLastDetailsView with new fields +export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => { + const rfqDetailsTable = alias(rfqLastDetails, "rfq_details"); + const rfqsTable = alias(rfqsLast, "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({ + // rfqLastDetails 기본 필드 + detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"), + rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"), + rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"), + + // RFQ 타입 정보 + rfqType: sql<string | null>`${rfqsTable.rfqType}`.as("rfq_type"), + rfqTitle: sql<string | null>`${rfqsTable.rfqTitle}`.as("rfq_title"), + + // ITB 관련 정보 + projectCompany: sql<string | null>`${rfqsTable.projectCompany}`.as("project_company"), + projectFlag: sql<string | null>`${rfqsTable.projectFlag}`.as("project_flag"), + projectSite: sql<string | null>`${rfqsTable.projectSite}`.as("project_site"), + smCode: sql<string | null>`${rfqsTable.smCode}`.as("sm_code"), + + // PR 정보 + prNumber: sql<string | null>`${rfqsTable.prNumber}`.as("pr_number"), + prIssueDate: sql<Date | null>`${rfqsTable.prIssueDate}`.as("pr_issue_date"), + series: sql<string | null>`${rfqsTable.series}`.as("series"), + + // 프로젝트 관련 필드 + 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"), + + // 벤더 관련 필드 + vendorId: sql<number | null>`${vendorsTable.id}`.as("vendor_id"), + vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"), + vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"), + 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"), + contractDuration: sql<string | null>`${rfqDetailsTable.contractDuration}`.as("contract_duration"), + 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"), + + // Boolean 필드들 + shortList: sql<boolean>`${rfqDetailsTable.shortList}`.as("short_list"), + returnYn: sql<boolean>`${rfqDetailsTable.returnYn}`.as("return_yn"), + returnedAt: sql<Date | null>`${rfqDetailsTable.returnedAt}`.as("returned_at"), + + // GTC/NDA/Agreement 필드 + prjectGtcYn: sql<boolean>`${rfqDetailsTable.prjectGtcYn}`.as("prject_gtc_yn"), + generalGtcYn: sql<boolean>`${rfqDetailsTable.generalGtcYn}`.as("general_gtc_yn"), + ndaYn: sql<boolean>`${rfqDetailsTable.ndaYn}`.as("nda_yn"), + agreementYn: sql<boolean>`${rfqDetailsTable.agreementYn}`.as("agreement_yn"), + + // 추가 YN 필드들 + materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"), + sparepartYn: sql<boolean | null>`${rfqDetailsTable.sparepartYn}`.as("sparepart_yn"), + firstYn: sql<boolean | null>`${rfqDetailsTable.firstYn}`.as("first_yn"), + + // Remarks + remark: sql<string | null>`${rfqDetailsTable.remark}`.as("remark"), + cancelReason: sql<string | null>`${rfqDetailsTable.cancelReason}`.as("cancel_reason"), + + firstDescription: sql<string | null>`${rfqDetailsTable.firstDescription}`.as("first_description"), + sparepartDescription: sql<string | null>`${rfqDetailsTable.sparepartDescription}`.as("sparepart_description"), + + + // 업데이트 관련 필드 + updatedBy: sql<number>`${rfqDetailsTable.updatedBy}`.as("updated_by"), + 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 rfq_pr_items + WHERE rfqs_last_id = ${rfqsTable.id} + )`.as("pr_items_count"), + + majorItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsTable.id} + AND major_yn = true + )`.as("major_items_count"), + + + // 견적서 관련 필드 + hasQuotation: sql<boolean>`( + SELECT COUNT(*) > 0 + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + )`.as("has_quotation"), + + quotationStatus: sql<string | null>`( + SELECT status + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + ORDER BY created_at DESC LIMIT 1 + )`.as("quotation_status"), + + quotationTotalPrice: sql<number | null>`( + SELECT total_amount + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + ORDER BY created_at DESC LIMIT 1 + )`.as("quotation_total_price"), + + quotationVersion: sql<number | null>`( + SELECT response_version + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + ORDER BY response_version DESC LIMIT 1 + )`.as("quotation_version"), + + quotationVersionCount: sql<number>`( + SELECT COUNT(DISTINCT response_version) + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + )`.as("quotation_version_count"), + + lastQuotationDate: sql<Date | null>`( + SELECT created_at + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + ORDER BY response_version DESC LIMIT 1 + )`.as("last_quotation_date"), + + quotationSubmittedAt: sql<Date | null>`( + SELECT submitted_at + FROM rfq_last_vendor_responses + WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} + ORDER BY response_version DESC LIMIT 1 + )`.as("quotation_submitted_at") + }) + .from(rfqDetailsTable) + .leftJoin(rfqsTable, eq(rfqDetailsTable.rfqsLastId, rfqsTable.id)) + .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.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)); +}); + +// Updated prItemsLastView to match new table structure +export const prItemsLastView = pgView("pr_items_last_view").as((qb) => { + return qb + .select({ + // PR Item 기본 필드 + id: rfqPrItems.id, + rfqsLastId: rfqPrItems.rfqsLastId, + rfqItem: rfqPrItems.rfqItem, + prItem: rfqPrItems.prItem, + prNo: rfqPrItems.prNo, + + // Material 정보 + materialCode: rfqPrItems.materialCode, + materialCategory: rfqPrItems.materialCategory, + acc: rfqPrItems.acc, + materialDescription: rfqPrItems.materialDescription, + size: rfqPrItems.size, + + // Delivery & Quantity + deliveryDate: rfqPrItems.deliveryDate, + quantity: rfqPrItems.quantity, + uom: rfqPrItems.uom, + grossWeight: rfqPrItems.grossWeight, + gwUom: rfqPrItems.gwUom, + + // Specification + specNo: rfqPrItems.specNo, + specUrl: rfqPrItems.specUrl, + trackingNo: rfqPrItems.trackingNo, + + // Major flag + majorYn: rfqPrItems.majorYn, + + // Project 정보 + projectDef: rfqPrItems.projectDef, + projectSc: rfqPrItems.projectSc, + projectKl: rfqPrItems.projectKl, + projectLc: rfqPrItems.projectLc, + projectDl: rfqPrItems.projectDl, + + // Remark + remark: rfqPrItems.remark, + + // RFQ 관련 정보 (from rfqsLast) + rfqCode: rfqsLast.rfqCode, + rfqType: rfqsLast.rfqType, + rfqTitle: rfqsLast.rfqTitle, + itemCode: rfqsLast.itemCode, + itemName: rfqsLast.itemName, + prNumber: rfqsLast.prNumber, + prIssueDate: rfqsLast.prIssueDate, + series: rfqsLast.series, + status: rfqsLast.status, + + // ITB 관련 정보 + projectCompany: rfqsLast.projectCompany, + projectFlag: rfqsLast.projectFlag, + projectSite: rfqsLast.projectSite, + smCode: rfqsLast.smCode, + + // Package 정보 + packageNo: rfqsLast.packageNo, + packageName: rfqsLast.packageName, + + // Project 정보 + projectCode: projects.code, + projectName: projects.name + }) + .from(rfqPrItems) + .leftJoin(rfqsLast, eq(rfqPrItems.rfqsLastId, rfqsLast.id)) + .leftJoin(projects, eq(rfqsLast.projectId, projects.id)); +}); + + + +export const rfqLastAttachments = pgTable( + "rfq_last_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(() => rfqsLast.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 rfqLastAttachmentRevisions = pgTable( + "rfq_last_attachment_revisions", + { + id: serial("id").primaryKey(), + attachmentId: integer("attachment_id") + .notNull() + .references(() => rfqLastAttachments.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) => ({ + // 첨부파일당 하나의 최신 리비전만 허용 + rfqLastLatestRevisionIdx: uniqueIndex('rfq_last_latest_revision_idx') + .on(t.attachmentId, t.isLatest) + .where(eq(t.isLatest, true)), + + // 첨부파일 + 리비전 번호 유니크 + rfqLastAttachmentRevisionIdx: uniqueIndex('rfq_last_attachment_revision_idx') + .on(t.attachmentId, t.revisionNo), + }) +) + +// 첨부파일 + 최신 리비전 뷰 +export const rfpAttachmentsWithLatestRevisionView = pgView("rfq_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 rfq_last_attachments a + LEFT JOIN rfq_last_attachment_revisions r ON a.latest_revision_id = r.id + LEFT JOIN users u ON a.created_by = u.id + `) + + +export type RfqsLastView = typeof rfqsLastView.$inferSelect; +export type PrItemsLastView = typeof prItemsLastView.$inferSelect; +export type RfqLastDetailsView = typeof rfqLastDetailsView.$inferSelect; + + +export const rfqLastRelations = relations( + rfqsLast, + ({ one, many }) => ({ + project: one(projects, { + fields: [rfqsLast.projectId], + references: [projects.id], + }), + // item: one(items, { + // fields: [rfqsLast.itemId], + // references: [items.id], + // }), + createdByUser: one(users, { + fields: [rfqsLast.createdBy], + references: [users.id], + relationName: "rfqCreator", + }), + updatedByUser: one(users, { + fields: [rfqsLast.updatedBy], + references: [users.id], + relationName: "rfqUpdater", + }), + rfqDetails: many(rfqLastDetails), + rfqPrItems: many(rfqPrItems, { relationName: "rfqPrItems" }), + + }) +); + +export const prItemsLastRelations = relations( + rfqPrItems, + ({ one }) => ({ + rfq: one(rfqsLast, { + fields: [rfqPrItems.rfqsLastId], + references: [rfqsLast.id], + relationName: "rfqPrItems" + }), + }) +); + +// rfqLastDetails 테이블의 관계 정의 +export const rfqDetailsLastRelations = relations( + rfqLastDetails, + ({ one }) => ({ + rfq: one(rfqsLast, { + fields: [rfqLastDetails.rfqsLastId], + references: [rfqsLast.id], + }), + vendor: one(vendors, { + fields: [rfqLastDetails.vendorsId], + references: [vendors.id], + }), + paymentTerms: one(paymentTerms, { + fields: [rfqLastDetails.paymentTermsCode], + references: [paymentTerms.code], + }), + incoterms: one(incoterms, { + fields: [rfqLastDetails.incotermsCode], + references: [incoterms.code], + }), + updatedByUser: one(users, { + fields: [rfqLastDetails.updatedBy], + references: [users.id], + }), + }) +); + diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts new file mode 100644 index 00000000..e92b1e10 --- /dev/null +++ b/db/schema/rfqVendor.ts @@ -0,0 +1,382 @@ +import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb } from "drizzle-orm/pg-core"; +import { eq, sql, relations } from "drizzle-orm"; +import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast"; +import { users } from "./users"; +import { vendors } from "./vendors"; +import { incoterms, paymentTerms } from "./procurementRFQ"; + +// ========================================== +// 1. 벤더 응답 메인 테이블 (견적서 헤더) +// ========================================== +export const rfqLastVendorResponses = pgTable( + "rfq_last_vendor_responses", + { + id: serial("id").primaryKey(), + + // RFQ 및 벤더 참조 + rfqsLastId: integer("rfqs_last_id") + .notNull() + .references(() => rfqsLast.id, { onDelete: "cascade" }), + rfqLastDetailsId: integer("rfq_last_details_id") + .notNull() + .references(() => rfqLastDetails.id, { onDelete: "cascade" }), + vendorId: integer("vendor_id") + .notNull() + .references(() => vendors.id, { onDelete: "cascade" }), + + // 응답 버전 관리 + responseVersion: integer("response_version").notNull().default(1), + isLatest: boolean("is_latest").notNull().default(true), + + // 응답 상태 + status: varchar("status", { length: 30 }) + .$type<"작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">() + .notNull() + .default("작성중"), + + // 제출 정보 + submittedAt: timestamp("submitted_at"), + submittedBy: integer("submitted_by") + .references(() => users.id, { onDelete: "set null" }), + + // 총액 정보 + totalAmount: numeric("total_amount", { precision: 15, scale: 2 }).$type<number>(), + currency: varchar("currency", { length: 10 }).default("USD"), + + // 벤더 제안 조건 (구매자 제시와 다른 경우) + vendorCurrency: varchar("vendor_currency", { length: 10 }), + vendorPaymentTermsCode: varchar("vendor_payment_terms_code", { length: 50 }) + .references(() => paymentTerms.code, { onDelete: "set null" }), + vendorIncotermsCode: varchar("vendor_incoterms_code", { length: 20 }) + .references(() => incoterms.code, { onDelete: "set null" }), + vendorIncotermsDetail: varchar("vendor_incoterms_detail", { length: 255 }), + vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(), + vendorContractDuration: varchar("vendor_contract_duration", { length: 255 }), + vendorTaxCode: varchar("vendor_tax_code", { length: 255 }), + vendorPlaceOfShipping: varchar("vendor_place_of_shipping", { length: 255 }), + vendorPlaceOfDestination: varchar("vendor_place_of_destination", { length: 255 }), + + // 초도품관리 응답 + vendorFirstYn: boolean("vendor_first_yn"), + vendorFirstDescription: text("vendor_first_description"), + vendorFirstAcceptance: varchar("vendor_first_acceptance", { length: 50 }) + .$type<"수용" | "부분수용" | "거부" | null>(), + + // Spare part 응답 + vendorSparepartYn: boolean("vendor_sparepart_yn"), + vendorSparepartDescription: text("vendor_sparepart_description"), + vendorSparepartAcceptance: varchar("vendor_sparepart_acceptance", { length: 50 }) + .$type<"수용" | "부분수용" | "거부" | null>(), + + // 연동제 적용 응답 + vendorMaterialPriceRelatedYn: boolean("vendor_material_price_related_yn"), + vendorMaterialPriceRelatedReason: text("vendor_material_price_related_reason"), + + // 각 조건별 변경 사유 (구매자 제시와 다른 경우) + currencyReason: text("currency_reason"), + paymentTermsReason: text("payment_terms_reason"), + deliveryDateReason: text("delivery_date_reason"), + incotermsReason: text("incoterms_reason"), + taxReason: text("tax_reason"), + shippingReason: text("shipping_reason"), + + // 전체 비고 및 특이사항 + generalRemark: text("general_remark"), + technicalProposal: text("technical_proposal"), + + // 감사 필드 + 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(), + } +); + +// ========================================== +// 2. 벤더 견적 아이템 테이블 (PR 아이템별 가격) +// ========================================== +export const rfqLastVendorQuotationItems = pgTable( + "rfq_last_vendor_quotation_items", + { + id: serial("id").primaryKey(), + vendorResponseId: integer("vendor_response_id") + .notNull() + .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }), + rfqPrItemId: integer("rfq_pr_item_id") + .notNull() + .references(() => rfqPrItems.id, { onDelete: "cascade" }), + + // PR 아이템 정보 (캐시) + prNo: varchar("pr_no", { length: 50 }), + materialCode: varchar("material_code", { length: 255 }), + materialDescription: varchar("material_description", { length: 255 }), + + // 견적 정보 + quantity: numeric("quantity", { precision: 12, scale: 2 }) + .$type<number>() + .notNull(), + uom: varchar("uom", { length: 50 }), + + // 가격 정보 + unitPrice: numeric("unit_price", { precision: 15, scale: 4 }) + .$type<number>() + .notNull(), + totalPrice: numeric("total_price", { precision: 15, scale: 2 }) + .$type<number>() + .notNull(), + currency: varchar("currency", { length: 10 }).default("USD"), + + // 납기 정보 + vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(), + leadTime: integer("lead_time"), // days + + // 제조사 정보 + manufacturer: varchar("manufacturer", { length: 255 }), + manufacturerCountry: varchar("manufacturer_country", { length: 100 }), + modelNo: varchar("model_no", { length: 255 }), + + // 기술 사양 + technicalCompliance: boolean("technical_compliance").default(true), + alternativeProposal: text("alternative_proposal"), + + // 할인 정보 + discountRate: numeric("discount_rate", { precision: 5, scale: 2 }).$type<number>(), + discountAmount: numeric("discount_amount", { precision: 15, scale: 2 }).$type<number>(), + + // 비고 + itemRemark: text("item_remark"), + deviationReason: text("deviation_reason"), // 요구사항과 다른 경우 사유 + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + } +); + +// ========================================== +// 4. 벤더 첨부파일 테이블 +// ========================================== +export const rfqLastVendorAttachments = pgTable( + "rfq_last_vendor_attachments", + { + id: serial("id").primaryKey(), + vendorResponseId: integer("vendor_response_id") + .notNull() + .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }), + + // 첨부파일 구분 + attachmentType: varchar("attachment_type", { length: 50 }).notNull(), + // "견적서", "기술제안서", "인증서", "카탈로그", "도면", "테스트성적서", "기타" + + documentNo: varchar("document_no", { length: 100 }), // 문서번호 + + // 파일 정보 + 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: text("description"), + + // 유효기간 (인증서 등) + validFrom: date("valid_from", { mode: "date" }).$type<Date | null>(), + validTo: date("valid_to", { mode: "date" }).$type<Date | null>(), + + // 감사 필드 + uploadedBy: integer("uploaded_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + uploadedAt: timestamp("uploaded_at").defaultNow().notNull(), + } +); + +// ========================================== +// 4. 벤더 응답 이력 테이블 (변경 이력 관리) +// ========================================== +export const rfqLastVendorResponseHistory = pgTable( + "rfq_last_vendor_response_history", + { + id: serial("id").primaryKey(), + vendorResponseId: integer("vendor_response_id") + .notNull() + .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }), + + action: varchar("action", { length: 50 }).notNull(), + // "생성", "수정", "제출", "철회", "재제출", "승인", "반려" + + previousStatus: varchar("previous_status", { length: 30 }), + newStatus: varchar("new_status", { length: 30 }), + + changeDetails: jsonb("change_details"), // 변경된 필드들 + changeReason: text("change_reason"), + + performedBy: integer("performed_by") + .notNull() + .references(() => users.id, { onDelete: "set null" }), + performedAt: timestamp("performed_at").defaultNow().notNull(), + } +); + +// ========================================== +// Views +// ========================================== + +// 벤더 응답 종합 뷰 +export const vendorResponseSummaryView = pgView("vendor_response_summary_view").as((qb) => { + const responseTable = alias(rfqLastVendorResponses, "response"); + const rfqTable = alias(rfqsLast, "rfq"); + const detailTable = alias(rfqLastDetails, "detail"); + const vendorTable = alias(vendors, "vendor"); + const submittedByUser = alias(users, "submitted_by_user"); + + return qb + .select({ + // 응답 기본 정보 + responseId: sql<number>`${responseTable.id}`.as("response_id"), + rfqId: sql<number>`${rfqTable.id}`.as("rfq_id"), + rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"), + + // 벤더 정보 + vendorId: sql<number>`${vendorTable.id}`.as("vendor_id"), + vendorName: sql<string>`${vendorTable.vendorName}`.as("vendor_name"), + vendorCode: sql<string>`${vendorTable.vendorCode}`.as("vendor_code"), + + // 응답 상태 + responseVersion: sql<number>`${responseTable.responseVersion}`.as("response_version"), + status: sql<string>`${responseTable.status}`.as("status"), + isLatest: sql<boolean>`${responseTable.isLatest}`.as("is_latest"), + + // 제출 정보 + submittedAt: sql<Date | null>`${responseTable.submittedAt}`.as("submitted_at"), + submittedByName: sql<string | null>`${submittedByUser.name}`.as("submitted_by_name"), + + // 금액 정보 + totalAmount: sql<number | null>`${responseTable.totalAmount}`.as("total_amount"), + currency: sql<string>`${responseTable.currency}`.as("currency"), + + // 주요 조건 차이 + deliveryDateMatch: sql<boolean>` + CASE + WHEN ${detailTable.deliveryDate} = ${responseTable.vendorDeliveryDate} THEN true + ELSE false + END + `.as("delivery_date_match"), + + currencyMatch: sql<boolean>` + CASE + WHEN ${detailTable.currency} = ${responseTable.vendorCurrency} THEN true + ELSE false + END + `.as("currency_match"), + + // 아이템 수 + quotedItemCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_vendor_quotation_items + WHERE vendor_response_id = ${responseTable.id} + )`.as("quoted_item_count"), + + // 첨부파일 수 + attachmentCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_vendor_attachments + WHERE vendor_response_id = ${responseTable.id} + )`.as("attachment_count"), + + // 타임스탬프 + createdAt: sql<Date>`${responseTable.createdAt}`.as("created_at"), + updatedAt: sql<Date>`${responseTable.updatedAt}`.as("updated_at"), + }) + .from(responseTable) + .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id)) + .leftJoin(detailTable, eq(responseTable.rfqLastDetailsId, detailTable.id)) + .leftJoin(vendorTable, eq(responseTable.vendorId, vendorTable.id)) + .leftJoin(submittedByUser, eq(responseTable.submittedBy, submittedByUser.id)); +}); + +// 벤더 견적 아이템 상세 뷰 +export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_detail_view").as((qb) => { + const quotationTable = alias(rfqLastVendorQuotationItems, "quotation"); + const prItemTable = alias(rfqPrItems, "pr_item"); + const responseTable = alias(rfqLastVendorResponses, "response"); + const rfqTable = alias(rfqsLast, "rfq"); + + return qb + .select({ + // 견적 아이템 정보 + quotationItemId: sql<number>`${quotationTable.id}`.as("quotation_item_id"), + vendorResponseId: sql<number>`${quotationTable.vendorResponseId}`.as("vendor_response_id"), + + // RFQ 정보 + rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"), + + // PR 아이템 정보 + prNo: sql<string | null>`${prItemTable.prNo}`.as("pr_no"), + prItem: sql<string | null>`${prItemTable.prItem}`.as("pr_item"), + materialCode: sql<string | null>`${prItemTable.materialCode}`.as("material_code"), + materialDescription: sql<string | null>`${prItemTable.materialDescription}`.as("material_description"), + originalQuantity: sql<number | null>`${prItemTable.quantity}`.as("original_quantity"), + originalDeliveryDate: sql<Date | null>`${prItemTable.deliveryDate}`.as("original_delivery_date"), + + // 벤더 견적 정보 + quotedQuantity: sql<number>`${quotationTable.quantity}`.as("quoted_quantity"), + unitPrice: sql<number>`${quotationTable.unitPrice}`.as("unit_price"), + totalPrice: sql<number>`${quotationTable.totalPrice}`.as("total_price"), + currency: sql<string>`${quotationTable.currency}`.as("currency"), + vendorDeliveryDate: sql<Date | null>`${quotationTable.vendorDeliveryDate}`.as("vendor_delivery_date"), + leadTime: sql<number | null>`${quotationTable.leadTime}`.as("lead_time"), + + // 제조사 정보 + manufacturer: sql<string | null>`${quotationTable.manufacturer}`.as("manufacturer"), + modelNo: sql<string | null>`${quotationTable.modelNo}`.as("model_no"), + + // 할인 정보 + discountRate: sql<number | null>`${quotationTable.discountRate}`.as("discount_rate"), + discountAmount: sql<number | null>`${quotationTable.discountAmount}`.as("discount_amount"), + + // 기술 준수 + technicalCompliance: sql<boolean>`${quotationTable.technicalCompliance}`.as("technical_compliance"), + alternativeProposal: sql<string | null>`${quotationTable.alternativeProposal}`.as("alternative_proposal"), + + // 비고 + itemRemark: sql<string | null>`${quotationTable.itemRemark}`.as("item_remark"), + deviationReason: sql<string | null>`${quotationTable.deviationReason}`.as("deviation_reason"), + }) + .from(quotationTable) + .leftJoin(prItemTable, eq(quotationTable.rfqPrItemId, prItemTable.id)) + .leftJoin(responseTable, eq(quotationTable.vendorResponseId, responseTable.id)) + .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id)); +}); + +// Relations +export const vendorResponseRelations = relations( + rfqLastVendorResponses, + ({ one, many }) => ({ + rfq: one(rfqsLast, { + fields: [rfqLastVendorResponses.rfqsLastId], + references: [rfqsLast.id], + }), + rfqDetail: one(rfqLastDetails, { + fields: [rfqLastVendorResponses.rfqLastDetailsId], + references: [rfqLastDetails.id], + }), + vendor: one(vendors, { + fields: [rfqLastVendorResponses.vendorId], + references: [vendors.id], + }), + quotationItems: many(rfqLastVendorQuotationItems), + attachments: many(rfqLastVendorAttachments), + history: many(rfqLastVendorResponseHistory), + }) +); + +// Type exports +export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect; +export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect; +export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect; +export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;
\ No newline at end of file |