diff options
Diffstat (limited to 'db/schema/rfqVendor.ts')
| -rw-r--r-- | db/schema/rfqVendor.ts | 211 |
1 files changed, 145 insertions, 66 deletions
diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts index 9d7b5fa8..5752b1c2 100644 --- a/db/schema/rfqVendor.ts +++ b/db/schema/rfqVendor.ts @@ -12,7 +12,7 @@ export const rfqLastVendorResponses = pgTable( "rfq_last_vendor_responses", { id: serial("id").primaryKey(), - + // RFQ 및 벤더 참조 rfqsLastId: integer("rfqs_last_id") .notNull() @@ -23,26 +23,37 @@ export const rfqLastVendorResponses = pgTable( vendorId: integer("vendor_id") .notNull() .references(() => vendors.id, { onDelete: "cascade" }), - + // 응답 버전 관리 responseVersion: integer("response_version").notNull().default(1), isLatest: boolean("is_latest").notNull().default(true), + + + // 참여 여부 관련 필드 (새로 추가) + participationStatus: varchar("participation_status", { length: 20 }) + .$type<"미응답" | "참여" | "불참">() + .notNull() + .default("미응답"), + participationRepliedAt: timestamp("participation_replied_at"), + participationRepliedBy: integer("participation_replied_by") + .references(() => users.id, { onDelete: "set null" }), + nonParticipationReason: text("non_participation_reason"), // 불참 사유 - // 응답 상태 + // 응답 상태 (수정: 참여 결정 후에만 의미 있음) status: varchar("status", { length: 30 }) - .$type<"초대됨" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">() + .$type<"대기중" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">() .notNull() - .default("초대됨"), - + .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 }) @@ -55,23 +66,23 @@ export const rfqLastVendorResponses = pgTable( 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"), @@ -79,11 +90,11 @@ export const rfqLastVendorResponses = pgTable( 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() @@ -93,6 +104,8 @@ export const rfqLastVendorResponses = pgTable( .references(() => users.id, { onDelete: "set null" }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), + + } ); @@ -109,18 +122,18 @@ export const rfqLastVendorQuotationItems = pgTable( 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>() @@ -129,28 +142,28 @@ export const rfqLastVendorQuotationItems = pgTable( .$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(), } @@ -166,27 +179,27 @@ export const rfqLastVendorAttachments = pgTable( 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() @@ -205,16 +218,16 @@ export const rfqLastVendorResponseHistory = pgTable( 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" }), @@ -240,25 +253,25 @@ export const vendorResponseSummaryView = pgView("vendor_response_summary_view"). 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 @@ -266,28 +279,28 @@ export const vendorResponseSummaryView = pgView("vendor_response_summary_view"). 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"), @@ -311,10 +324,10 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det // 견적 아이템 정보 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"), @@ -322,7 +335,7 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det 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"), @@ -330,19 +343,19 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det 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"), @@ -354,27 +367,93 @@ export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_det }); // Relations -export const vendorResponseRelations = relations( +export const vendorQuotationItemsRelations = relations( + rfqLastVendorQuotationItems, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorQuotationItems.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + prItem: one(rfqPrItems, { + fields: [rfqLastVendorQuotationItems.rfqPrItemId], + references: [rfqPrItems.id], + }), + }) + ); + + // 첨부파일 테이블의 relation + export const vendorAttachmentsRelations = relations( + rfqLastVendorAttachments, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorAttachments.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + uploadedByUser: one(users, { + fields: [rfqLastVendorAttachments.uploadedBy], + references: [users.id], + }), + }) + ); + + // 이력 테이블의 relation + export const vendorResponseHistoryRelations = relations( + rfqLastVendorResponseHistory, + ({ one }) => ({ + vendorResponse: one(rfqLastVendorResponses, { + fields: [rfqLastVendorResponseHistory.vendorResponseId], + references: [rfqLastVendorResponses.id], + }), + performedByUser: one(users, { + fields: [rfqLastVendorResponseHistory.performedBy], + references: [users.id], + }), + }) + ); + + // 메인 응답 테이블의 relation (이미 있지만 완전한 버전) + 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), + 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], + }), + submittedByUser: one(users, { + fields: [rfqLastVendorResponses.submittedBy], + references: [users.id], + relationName: "vendorResponseSubmittedBy", + }), + participationRepliedByUser: one(users, { + fields: [rfqLastVendorResponses.participationRepliedBy], + references: [users.id], + relationName: "vendorResponseParticipationRepliedBy", + }), + createdByUser: one(users, { + fields: [rfqLastVendorResponses.createdBy], + references: [users.id], + relationName: "vendorResponseCreatedBy", + }), + updatedByUser: one(users, { + fields: [rfqLastVendorResponses.updatedBy], + references: [users.id], + relationName: "vendorResponseUpdatedBy", + }), + quotationItems: many(rfqLastVendorQuotationItems), + attachments: many(rfqLastVendorAttachments), + history: many(rfqLastVendorResponseHistory), }) -); + ); + // Type exports export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect; export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect; |