diff options
Diffstat (limited to 'db/schema/rfq.ts')
| -rw-r--r-- | db/schema/rfq.ts | 253 |
1 files changed, 186 insertions, 67 deletions
diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts index 98c4245c..4e35dd10 100644 --- a/db/schema/rfq.ts +++ b/db/schema/rfq.ts @@ -3,7 +3,7 @@ import { vendors } from "./vendors"; import { users } from "./users"; import { items } from "./items"; import { eq, sql, and } from "drizzle-orm"; -import { projects } from "./projects"; +import { biddingProjects, projects } from "./projects"; export const rfqs = pgTable( "rfqs", @@ -17,6 +17,9 @@ export const rfqs = pgTable( projectId: integer("project_id") .references(() => projects.id, { onDelete: "set null" }), + bidProjectId: integer("bid_project_id") + .references(() => biddingProjects.id, { onDelete: "set null" }), + description: varchar("description", { length: 255 }), dueDate: date("due_date", { mode: "date" }) @@ -71,7 +74,6 @@ export const rfqItems = pgTable("rfq_items", { updatedAt: timestamp("updated_at").defaultNow().notNull(), }); - export const rfqAttachments = pgTable("rfq_attachments", { id: serial("id").primaryKey(), rfqId: integer("rfq_id").references(() => rfqs.id), @@ -103,7 +105,7 @@ export const rfqComments = pgTable("rfq_comments", { .references(() => rfqEvaluations.id) .$type<number | null>(), cbeId: integer("cbe_id") - .references(() => cbeEvaluations.id) + .references(() => vendorResponses.id) .$type<number | null>(), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), @@ -175,6 +177,12 @@ export const vendorTechnicalResponses = pgTable( .notNull() .references(() => vendorResponses.id, { onDelete: "cascade" }), + responseStatus: varchar("response_status", { length: 30 }) + .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">() + .default("PENDING") + .notNull(), + + // 간소화된 기술 응답 필드 // 기술 내용은 주로 첨부 파일로 받으므로 최소한의 필드만 유지 summary: text("summary"), // 간단한 기술 응답 요약 @@ -197,6 +205,11 @@ export const vendorCommercialResponses = pgTable( .notNull() .references(() => vendorResponses.id, { onDelete: "cascade" }), + responseStatus: varchar("response_status", { length: 30 }) + .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">() + .default("PENDING") + .notNull(), + // Commercial response fields totalPrice: numeric("total_price", { precision: 18, scale: 2 }).$type<number>(), currency: varchar("currency", { length: 10 }).default("USD"), @@ -260,7 +273,7 @@ export const cbeEvaluations = pgTable("cbe_evaluations", { .notNull() .references(() => rfqs.id, { onDelete: "cascade" }), - // 어떤 벤더의 상업평가(CBE)인지 + // 어떤 협력업체의 상업평가(CBE)인지 vendorId: integer("vendor_id") .notNull() .references(() => vendors.id, { onDelete: "cascade" }), @@ -380,7 +393,7 @@ export const rfqsView = pgView("rfqs_view").as((qb) => { .leftJoin(users, eq(rfqs.createdBy, users.id)) }) -// vendorRfqView 업데이트 벤더 기준 +// vendorRfqView 업데이트 협력업체 기준 export const vendorRfqView = pgView("vendor_rfq_view").as((qb) => { return qb .select({ @@ -437,9 +450,17 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => { rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), + // TBE 응답 상태 추가 + technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"), + technicalResponseStatus: sql<string | null>`${vendorTechnicalResponses.responseStatus}`.as("technical_response_status"), + technicalSummary: sql<string | null>`${vendorTechnicalResponses.summary}`.as("technical_summary"), + technicalNotes: sql<string | null>`${vendorTechnicalResponses.notes}`.as("technical_notes"), + technicalUpdated: sql<Date | null>`${vendorTechnicalResponses.updatedAt}`.as("technical_updated"), + // rfqs rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"), rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"), + rfqStatus: sql<string | null>`${rfqs.status}`.as("rfq_status"), description: sql<string | null>`${rfqs.description}`.as("description"), dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"), @@ -468,6 +489,11 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => { .leftJoin( projects, eq(rfqs.projectId, projects.id) + + ) + .leftJoin( + vendorTechnicalResponses, + eq(vendorTechnicalResponses.responseId, vendorResponses.id) ) // 3) rfqEvaluations .leftJoin( @@ -482,68 +508,68 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => { export const vendorCbeView = pgView("vendor_cbe_view").as((qb) => { return qb - .select({ - // [1] Vendor 기본정보 - vendorId: sql<number>`${vendors.id}`.as("vendor_id"), - vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"), - vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"), - address: sql<string | null>`${vendors.address}`.as("address"), - country: sql<string | null>`${vendors.country}`.as("country"), - email: sql<string | null>`${vendors.email}`.as("email"), - website: sql<string | null>`${vendors.website}`.as("website"), - vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"), - - // [2] rfq_vendors (vendorResponses) - vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"), - rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"), - rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), - rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), - - // [3] rfqs - rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"), - rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"), - description: sql<string | null>`${rfqs.description}`.as("description"), - dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"), - - // [4] 프로젝트 정보 - projectId: sql<number | null>`${projects.id}`.as("project_id"), - projectCode: sql<string | null>`${projects.code}`.as("project_code"), - projectName: sql<string | null>`${projects.name}`.as("project_name"), - - // [5] CBE 평가(cbeEvaluations) - cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"), - cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"), - cbeNote: sql<string | null>`${cbeEvaluations.notes}`.as("cbe_note"), - cbeUpdated: sql<Date | null>`${cbeEvaluations.updatedAt}`.as("cbe_updated"), - - // 상업평가용 추가 필드들 - totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"), - currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"), - paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"), - incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"), - deliverySchedule: sql<string | null>`${cbeEvaluations.deliverySchedule}`.as("delivery_schedule"), - }) - .from(vendors) - .leftJoin( - vendorResponses, - eq(vendorResponses.vendorId, vendors.id) - ) - .leftJoin( - rfqs, - eq(vendorResponses.rfqId, rfqs.id) - ) - .leftJoin( - projects, - eq(rfqs.projectId, projects.id) - ) - .leftJoin( - cbeEvaluations, - and( - eq(cbeEvaluations.vendorId, vendors.id), - eq(cbeEvaluations.rfqId, vendorResponses.rfqId) + .select({ + // [1] Vendor 기본정보 + vendorId: sql<number>`${vendors.id}`.as("vendor_id"), + vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"), + address: sql<string | null>`${vendors.address}`.as("address"), + country: sql<string | null>`${vendors.country}`.as("country"), + email: sql<string | null>`${vendors.email}`.as("email"), + website: sql<string | null>`${vendors.website}`.as("website"), + vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"), + + // [2] rfq_vendors (vendorResponses) + vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"), + rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"), + rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), + rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), + + // [3] rfqs + rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"), + rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"), + description: sql<string | null>`${rfqs.description}`.as("description"), + dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"), + + // [4] 프로젝트 정보 + projectId: sql<number | null>`${projects.id}`.as("project_id"), + projectCode: sql<string | null>`${projects.code}`.as("project_code"), + projectName: sql<string | null>`${projects.name}`.as("project_name"), + + // [5] CBE 평가(cbeEvaluations) + cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"), + cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"), + cbeNote: sql<string | null>`${cbeEvaluations.notes}`.as("cbe_note"), + cbeUpdated: sql<Date | null>`${cbeEvaluations.updatedAt}`.as("cbe_updated"), + + // 상업평가용 추가 필드들 + totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"), + currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"), + paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"), + incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"), + deliverySchedule: sql<string | null>`${cbeEvaluations.deliverySchedule}`.as("delivery_schedule"), + }) + .from(vendors) + .leftJoin( + vendorResponses, + eq(vendorResponses.vendorId, vendors.id) + ) + .leftJoin( + rfqs, + eq(vendorResponses.rfqId, rfqs.id) ) - ); - }); + .leftJoin( + projects, + eq(rfqs.projectId, projects.id) + ) + .leftJoin( + cbeEvaluations, + and( + eq(cbeEvaluations.vendorId, vendors.id), + eq(cbeEvaluations.rfqId, vendorResponses.rfqId) + ) + ); +}); // Types for TypeScript export type VendorResponse = typeof vendorResponses.$inferSelect; @@ -582,7 +608,7 @@ export const vendorResponsesView = pgView("vendor_responses_view").as((qb) => { // Response status responseStatus: sql<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">`${vendorResponses.responseStatus}` - .as("response_status"), + .as("response_status"), respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"), // Technical response indicators @@ -713,6 +739,10 @@ export interface RfqWithItems extends Rfq { lines: RfqItem[]; } +export interface RfqViewWithItems extends RfqsView { + lines: RfqItem[]; +} + export interface RfqWithAll extends VendorResponsesView { // 아래처럼 배열/객체로 @@ -741,3 +771,92 @@ export interface RfqWithAll extends VendorResponsesView { } export type RfqsView = typeof rfqsView.$inferSelect + + +export const vendorResponseCBEView = pgView("vendor_response_cbe_view").as((qb) => { + return qb + .select({ + // 기본 응답 식별 정보 + responseId: sql<number>`${vendorResponses.id}`.as("response_id"), + rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"), + vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"), + + // vendorResponses 상세 정보 + responseStatus: sql<string>`${vendorResponses.responseStatus}`.as("response_status"), + notes: sql<string | null>`${vendorResponses.notes}`.as("response_notes"), + respondedBy: sql<string | null>`${vendorResponses.respondedBy}`.as("responded_by"), + respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"), + responseUpdatedAt: sql<Date>`${vendorResponses.updatedAt}`.as("response_updated_at"), + + // RFQ 정보 + rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"), + rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"), + rfqDueDate: sql<Date | null>`${rfqs.dueDate}`.as("rfq_due_date"), + rfqStatus: sql<string>`${rfqs.status}`.as("rfq_status"), + rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"), + + // 협력업체 정보 + vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"), + vendorStatus: sql<string | null>`${vendors.status}`.as("vendor_status"), + + // 프로젝트 정보 + projectId: sql<number | null>`${projects.id}`.as("project_id"), + projectCode: sql<string | null>`${projects.code}`.as("project_code"), + projectName: sql<string | null>`${projects.name}`.as("project_name"), + + // 상업 응답 상세 정보 + commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"), + commercialResponseStatus: sql<string | null>`${vendorCommercialResponses.responseStatus}`.as("commercial_response_status"), + totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"), + currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"), + paymentTerms: sql<string | null>`${vendorCommercialResponses.paymentTerms}`.as("payment_terms"), + incoterms: sql<string | null>`${vendorCommercialResponses.incoterms}`.as("incoterms"), + deliveryPeriod: sql<string | null>`${vendorCommercialResponses.deliveryPeriod}`.as("delivery_period"), + warrantyPeriod: sql<string | null>`${vendorCommercialResponses.warrantyPeriod}`.as("warranty_period"), + validityPeriod: sql<string | null>`${vendorCommercialResponses.validityPeriod}`.as("validity_period"), + priceBreakdown: sql<string | null>`${vendorCommercialResponses.priceBreakdown}`.as("price_breakdown"), + commercialNotes: sql<string | null>`${vendorCommercialResponses.commercialNotes}`.as("commercial_notes"), + commercialCreatedAt: sql<Date | null>`${vendorCommercialResponses.createdAt}`.as("commercial_created_at"), + commercialUpdatedAt: sql<Date | null>`${vendorCommercialResponses.updatedAt}`.as("commercial_updated_at"), + + // 첨부파일 개수 및 상세 정보 + attachmentCount: sql<number>`( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id} + )`.as("attachment_count"), + + commercialAttachmentCount: sql<number>`( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."commercial_response_id" = ${vendorCommercialResponses.id} + )`.as("commercial_attachment_count"), + + // 첨부파일 유형별 개수 (선택적) + technicalAttachmentCount: sql<number>`( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id} + AND "vendor_response_attachments"."attachment_type" = 'TECHNICAL_SPEC' + )`.as("technical_attachment_count"), + + // 최신 첨부파일 정보 (선택적) + latestAttachmentDate: sql<Date | null>`( + SELECT MAX("uploaded_at") + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id} + )`.as("latest_attachment_date"), + }) + .from(vendorResponses) + .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id)) + .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id)) + .leftJoin(projects, eq(rfqs.projectId, projects.id)) + .leftJoin( + vendorCommercialResponses, + eq(vendorCommercialResponses.responseId, vendorResponses.id) + ); +}); + +// TypeScript 타입 정의 +export type VendorResponseCBEView = typeof vendorResponseCBEView.$inferSelect;
\ No newline at end of file |