(대표님) OCR 박진석프로 요청 대응, rfq 변경된 요구사항 구현

1 files changed, 382 insertions, 0 deletions

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