From ef4c533ebacc2cdc97e518f30e9a9350004fcdfb Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 28 Apr 2025 02:13:30 +0000 Subject: ~20250428 작업사항 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/rfq.ts | 253 ++++++++++++++++++++++++++++++++++++++++--------------- 1 file changed, 186 insertions(+), 67 deletions(-) (limited to 'db/schema/rfq.ts') 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(), cbeId: integer("cbe_id") - .references(() => cbeEvaluations.id) + .references(() => vendorResponses.id) .$type(), 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(), 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`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), rfqVendorUpdated: sql`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), + // TBE 응답 상태 추가 + technicalResponseId: sql`${vendorTechnicalResponses.id}`.as("technical_response_id"), + technicalResponseStatus: sql`${vendorTechnicalResponses.responseStatus}`.as("technical_response_status"), + technicalSummary: sql`${vendorTechnicalResponses.summary}`.as("technical_summary"), + technicalNotes: sql`${vendorTechnicalResponses.notes}`.as("technical_notes"), + technicalUpdated: sql`${vendorTechnicalResponses.updatedAt}`.as("technical_updated"), + // rfqs rfqCode: sql`${rfqs.rfqCode}`.as("rfq_code"), rfqType: sql`${rfqs.rfqType}`.as("rfq_type"), + rfqStatus: sql`${rfqs.status}`.as("rfq_status"), description: sql`${rfqs.description}`.as("description"), dueDate: sql`${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`${vendors.id}`.as("vendor_id"), - vendorName: sql`${vendors.vendorName}`.as("vendor_name"), - vendorCode: sql`${vendors.vendorCode}`.as("vendor_code"), - address: sql`${vendors.address}`.as("address"), - country: sql`${vendors.country}`.as("country"), - email: sql`${vendors.email}`.as("email"), - website: sql`${vendors.website}`.as("website"), - vendorStatus: sql`${vendors.status}`.as("vendor_status"), - - // [2] rfq_vendors (vendorResponses) - vendorResponseId: sql`${vendorResponses.id}`.as("vendor_response_id"), - rfqId: sql`${vendorResponses.rfqId}`.as("rfq_id"), - rfqVendorStatus: sql`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), - rfqVendorUpdated: sql`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), - - // [3] rfqs - rfqCode: sql`${rfqs.rfqCode}`.as("rfq_code"), - rfqType: sql`${rfqs.rfqType}`.as("rfq_type"), - description: sql`${rfqs.description}`.as("description"), - dueDate: sql`${rfqs.dueDate}`.as("due_date"), - - // [4] 프로젝트 정보 - projectId: sql`${projects.id}`.as("project_id"), - projectCode: sql`${projects.code}`.as("project_code"), - projectName: sql`${projects.name}`.as("project_name"), - - // [5] CBE 평가(cbeEvaluations) - cbeId: sql`${cbeEvaluations.id}`.as("cbe_id"), - cbeResult: sql`${cbeEvaluations.result}`.as("cbe_result"), - cbeNote: sql`${cbeEvaluations.notes}`.as("cbe_note"), - cbeUpdated: sql`${cbeEvaluations.updatedAt}`.as("cbe_updated"), - - // 상업평가용 추가 필드들 - totalCost: sql`${cbeEvaluations.totalCost}`.as("total_cost"), - currency: sql`${cbeEvaluations.currency}`.as("currency"), - paymentTerms: sql`${cbeEvaluations.paymentTerms}`.as("payment_terms"), - incoterms: sql`${cbeEvaluations.incoterms}`.as("incoterms"), - deliverySchedule: sql`${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`${vendors.id}`.as("vendor_id"), + vendorName: sql`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql`${vendors.vendorCode}`.as("vendor_code"), + address: sql`${vendors.address}`.as("address"), + country: sql`${vendors.country}`.as("country"), + email: sql`${vendors.email}`.as("email"), + website: sql`${vendors.website}`.as("website"), + vendorStatus: sql`${vendors.status}`.as("vendor_status"), + + // [2] rfq_vendors (vendorResponses) + vendorResponseId: sql`${vendorResponses.id}`.as("vendor_response_id"), + rfqId: sql`${vendorResponses.rfqId}`.as("rfq_id"), + rfqVendorStatus: sql`${vendorResponses.responseStatus}`.as("rfq_vendor_status"), + rfqVendorUpdated: sql`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"), + + // [3] rfqs + rfqCode: sql`${rfqs.rfqCode}`.as("rfq_code"), + rfqType: sql`${rfqs.rfqType}`.as("rfq_type"), + description: sql`${rfqs.description}`.as("description"), + dueDate: sql`${rfqs.dueDate}`.as("due_date"), + + // [4] 프로젝트 정보 + projectId: sql`${projects.id}`.as("project_id"), + projectCode: sql`${projects.code}`.as("project_code"), + projectName: sql`${projects.name}`.as("project_name"), + + // [5] CBE 평가(cbeEvaluations) + cbeId: sql`${cbeEvaluations.id}`.as("cbe_id"), + cbeResult: sql`${cbeEvaluations.result}`.as("cbe_result"), + cbeNote: sql`${cbeEvaluations.notes}`.as("cbe_note"), + cbeUpdated: sql`${cbeEvaluations.updatedAt}`.as("cbe_updated"), + + // 상업평가용 추가 필드들 + totalCost: sql`${cbeEvaluations.totalCost}`.as("total_cost"), + currency: sql`${cbeEvaluations.currency}`.as("currency"), + paymentTerms: sql`${cbeEvaluations.paymentTerms}`.as("payment_terms"), + incoterms: sql`${cbeEvaluations.incoterms}`.as("incoterms"), + deliverySchedule: sql`${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`${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`${vendorResponses.id}`.as("response_id"), + rfqId: sql`${vendorResponses.rfqId}`.as("rfq_id"), + vendorId: sql`${vendorResponses.vendorId}`.as("vendor_id"), + + // vendorResponses 상세 정보 + responseStatus: sql`${vendorResponses.responseStatus}`.as("response_status"), + notes: sql`${vendorResponses.notes}`.as("response_notes"), + respondedBy: sql`${vendorResponses.respondedBy}`.as("responded_by"), + respondedAt: sql`${vendorResponses.respondedAt}`.as("responded_at"), + responseUpdatedAt: sql`${vendorResponses.updatedAt}`.as("response_updated_at"), + + // RFQ 정보 + rfqCode: sql`${rfqs.rfqCode}`.as("rfq_code"), + rfqDescription: sql`${rfqs.description}`.as("rfq_description"), + rfqDueDate: sql`${rfqs.dueDate}`.as("rfq_due_date"), + rfqStatus: sql`${rfqs.status}`.as("rfq_status"), + rfqType: sql`${rfqs.rfqType}`.as("rfq_type"), + + // 협력업체 정보 + vendorName: sql`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql`${vendors.vendorCode}`.as("vendor_code"), + vendorStatus: sql`${vendors.status}`.as("vendor_status"), + + // 프로젝트 정보 + projectId: sql`${projects.id}`.as("project_id"), + projectCode: sql`${projects.code}`.as("project_code"), + projectName: sql`${projects.name}`.as("project_name"), + + // 상업 응답 상세 정보 + commercialResponseId: sql`${vendorCommercialResponses.id}`.as("commercial_response_id"), + commercialResponseStatus: sql`${vendorCommercialResponses.responseStatus}`.as("commercial_response_status"), + totalPrice: sql`${vendorCommercialResponses.totalPrice}`.as("total_price"), + currency: sql`${vendorCommercialResponses.currency}`.as("currency"), + paymentTerms: sql`${vendorCommercialResponses.paymentTerms}`.as("payment_terms"), + incoterms: sql`${vendorCommercialResponses.incoterms}`.as("incoterms"), + deliveryPeriod: sql`${vendorCommercialResponses.deliveryPeriod}`.as("delivery_period"), + warrantyPeriod: sql`${vendorCommercialResponses.warrantyPeriod}`.as("warranty_period"), + validityPeriod: sql`${vendorCommercialResponses.validityPeriod}`.as("validity_period"), + priceBreakdown: sql`${vendorCommercialResponses.priceBreakdown}`.as("price_breakdown"), + commercialNotes: sql`${vendorCommercialResponses.commercialNotes}`.as("commercial_notes"), + commercialCreatedAt: sql`${vendorCommercialResponses.createdAt}`.as("commercial_created_at"), + commercialUpdatedAt: sql`${vendorCommercialResponses.updatedAt}`.as("commercial_updated_at"), + + // 첨부파일 개수 및 상세 정보 + attachmentCount: sql`( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id} + )`.as("attachment_count"), + + commercialAttachmentCount: sql`( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."commercial_response_id" = ${vendorCommercialResponses.id} + )`.as("commercial_attachment_count"), + + // 첨부파일 유형별 개수 (선택적) + technicalAttachmentCount: sql`( + 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`( + 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 -- cgit v1.2.3