"use server"; import db from "@/db/db"; import { eq, and, inArray } from "drizzle-orm"; import { rfqsLast, rfqLastDetails, rfqPrItems, rfqLastVendorResponses, rfqLastVendorQuotationItems, vendors, paymentTerms, incoterms, } from "@/db/schema"; export interface ComparisonData { rfqInfo: { id: number; rfqCode: string; rfqTitle: string; rfqType: string; projectCode?: string; projectName?: string; dueDate: Date | null; packageNo?: string; packageName?: string; }; vendors: VendorComparison[]; prItems: PrItemComparison[]; summary: { lowestBidder: string; highestBidder: string; priceRange: { min: number; max: number; average: number; }; currency: string; }; } export interface VendorComparison { vendorId: number; vendorName: string; vendorCode: string; vendorCountry?: string; // 응답 정보 responseId: number; participationStatus: string; responseStatus: string; submittedAt: Date | null; // 가격 정보 totalAmount: number; currency: string; rank?: number; priceVariance?: number; // 평균 대비 차이 % // 구매자 제시 조건 buyerConditions: { currency: string; paymentTermsCode: string; paymentTermsDesc?: string; incotermsCode: string; incotermsDesc?: string; deliveryDate: Date | null; contractDuration?: string; taxCode?: string; placeOfShipping?: string; placeOfDestination?: string; // 추가 조건 firstYn: boolean; firstDescription?: string; sparepartYn: boolean; sparepartDescription?: string; materialPriceRelatedYn: boolean; }; // 벤더 제안 조건 vendorConditions: { currency?: string; paymentTermsCode?: string; paymentTermsDesc?: string; incotermsCode?: string; incotermsDesc?: string; deliveryDate?: Date | null; contractDuration?: string; taxCode?: string; placeOfShipping?: string; placeOfDestination?: string; // 추가 조건 응답 firstAcceptance?: "수용" | "부분수용" | "거부"; firstDescription?: string; sparepartAcceptance?: "수용" | "부분수용" | "거부"; sparepartDescription?: string; materialPriceRelatedYn?: boolean; materialPriceRelatedReason?: string; }; // 조건 차이 분석 conditionDifferences: { hasDifferences: boolean; differences: string[]; criticalDifferences: string[]; // 중요한 차이점 }; // 비고 generalRemark?: string; technicalProposal?: string; } export interface PrItemComparison { prItemId: number; prNo: string; prItem: string; materialCode: string; materialDescription: string; requestedQuantity: number; uom: string; requestedDeliveryDate: Date | null; vendorQuotes: { vendorId: number; vendorName: string; unitPrice: number; totalPrice: number; currency: string; quotedQuantity: number; deliveryDate?: Date | null; leadTime?: number; manufacturer?: string; modelNo?: string; technicalCompliance: boolean; alternativeProposal?: string; itemRemark?: string; priceRank?: number; }[]; priceAnalysis: { lowestPrice: number; highestPrice: number; averagePrice: number; priceVariance: number; // 표준편차 }; } export async function getComparisonData( rfqId: number, vendorIds: number[] ): Promise { try { // 1. RFQ 기본 정보 조회 const rfqData = await db .select({ id: rfqsLast.id, rfqCode: rfqsLast.rfqCode, rfqTitle: rfqsLast.rfqTitle, rfqType: rfqsLast.rfqType, // projectCode: rfqsLast.projectCode, // projectName: rfqsLast.projectName, dueDate: rfqsLast.dueDate, packageNo: rfqsLast.packageNo, packageName: rfqsLast.packageName, }) .from(rfqsLast) .where(eq(rfqsLast.id, rfqId)) .limit(1); if (!rfqData[0]) return null; // 2. 벤더별 정보 및 응답 조회 const vendorData = await db .select({ // 벤더 정보 vendorId: vendors.id, vendorName: vendors.vendorName, vendorCode: vendors.vendorCode, vendorCountry: vendors.country, // RFQ Details (구매자 조건) detailId: rfqLastDetails.id, buyerCurrency: rfqLastDetails.currency, buyerPaymentTermsCode: rfqLastDetails.paymentTermsCode, buyerIncotermsCode: rfqLastDetails.incotermsCode, buyerIncotermsDetail: rfqLastDetails.incotermsDetail, buyerDeliveryDate: rfqLastDetails.deliveryDate, buyerContractDuration: rfqLastDetails.contractDuration, buyerTaxCode: rfqLastDetails.taxCode, buyerPlaceOfShipping: rfqLastDetails.placeOfShipping, buyerPlaceOfDestination: rfqLastDetails.placeOfDestination, buyerFirstYn: rfqLastDetails.firstYn, buyerFirstDescription: rfqLastDetails.firstDescription, buyerSparepartYn: rfqLastDetails.sparepartYn, buyerSparepartDescription: rfqLastDetails.sparepartDescription, buyerMaterialPriceRelatedYn: rfqLastDetails.materialPriceRelatedYn, // 벤더 응답 responseId: rfqLastVendorResponses.id, participationStatus: rfqLastVendorResponses.participationStatus, responseStatus: rfqLastVendorResponses.status, submittedAt: rfqLastVendorResponses.submittedAt, totalAmount: rfqLastVendorResponses.totalAmount, responseCurrency: rfqLastVendorResponses.currency, // 벤더 제안 조건 vendorCurrency: rfqLastVendorResponses.vendorCurrency, vendorPaymentTermsCode: rfqLastVendorResponses.vendorPaymentTermsCode, vendorIncotermsCode: rfqLastVendorResponses.vendorIncotermsCode, vendorIncotermsDetail: rfqLastVendorResponses.vendorIncotermsDetail, vendorDeliveryDate: rfqLastVendorResponses.vendorDeliveryDate, vendorContractDuration: rfqLastVendorResponses.vendorContractDuration, vendorTaxCode: rfqLastVendorResponses.vendorTaxCode, vendorPlaceOfShipping: rfqLastVendorResponses.vendorPlaceOfShipping, vendorPlaceOfDestination: rfqLastVendorResponses.vendorPlaceOfDestination, // 추가 조건 응답 vendorFirstAcceptance: rfqLastVendorResponses.vendorFirstAcceptance, vendorFirstDescription: rfqLastVendorResponses.vendorFirstDescription, vendorSparepartAcceptance: rfqLastVendorResponses.vendorSparepartAcceptance, vendorSparepartDescription: rfqLastVendorResponses.vendorSparepartDescription, vendorMaterialPriceRelatedYn: rfqLastVendorResponses.vendorMaterialPriceRelatedYn, vendorMaterialPriceRelatedReason: rfqLastVendorResponses.vendorMaterialPriceRelatedReason, // 비고 generalRemark: rfqLastVendorResponses.generalRemark, technicalProposal: rfqLastVendorResponses.technicalProposal, }) .from(vendors) .innerJoin( rfqLastDetails, and( eq(rfqLastDetails.vendorsId, vendors.id), eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isLatest, true) ) ) .leftJoin( rfqLastVendorResponses, and( eq(rfqLastVendorResponses.vendorId, vendors.id), eq(rfqLastVendorResponses.rfqsLastId, rfqId), eq(rfqLastVendorResponses.isLatest, true) ) ) .where(inArray(vendors.id, vendorIds)); // 3. Payment Terms와 Incoterms 설명 조회 const paymentTermsData = await db .select({ code: paymentTerms.code, description: paymentTerms.description, }) .from(paymentTerms); const incotermsData = await db .select({ code: incoterms.code, description: incoterms.description, }) .from(incoterms); const paymentTermsMap = new Map( paymentTermsData.map(pt => [pt.code, pt.description]) ); const incotermsMap = new Map( incotermsData.map(ic => [ic.code, ic.description]) ); // 4. PR Items 조회 const prItems = await db .select({ id: rfqPrItems.id, prNo: rfqPrItems.prNo, prItem: rfqPrItems.prItem, materialCode: rfqPrItems.materialCode, materialDescription: rfqPrItems.materialDescription, quantity: rfqPrItems.quantity, uom: rfqPrItems.uom, deliveryDate: rfqPrItems.deliveryDate, }) .from(rfqPrItems) .where(eq(rfqPrItems.rfqsLastId, rfqId)); // 5. 벤더별 견적 아이템 조회 const quotationItems = await db .select({ vendorResponseId: rfqLastVendorQuotationItems.vendorResponseId, prItemId: rfqLastVendorQuotationItems.rfqPrItemId, unitPrice: rfqLastVendorQuotationItems.unitPrice, totalPrice: rfqLastVendorQuotationItems.totalPrice, currency: rfqLastVendorQuotationItems.currency, quantity: rfqLastVendorQuotationItems.quantity, deliveryDate: rfqLastVendorQuotationItems.vendorDeliveryDate, leadTime: rfqLastVendorQuotationItems.leadTime, manufacturer: rfqLastVendorQuotationItems.manufacturer, modelNo: rfqLastVendorQuotationItems.modelNo, technicalCompliance: rfqLastVendorQuotationItems.technicalCompliance, alternativeProposal: rfqLastVendorQuotationItems.alternativeProposal, itemRemark: rfqLastVendorQuotationItems.itemRemark, }) .from(rfqLastVendorQuotationItems) .where( inArray( rfqLastVendorQuotationItems.vendorResponseId, vendorData.map(v => v.responseId).filter(id => id != null) ) ); // 6. 데이터 가공 및 분석 const validAmounts = vendorData .map(v => v.totalAmount) .filter(a => a != null && a > 0); const minAmount = Math.min(...validAmounts); const maxAmount = Math.max(...validAmounts); const avgAmount = validAmounts.reduce((a, b) => a + b, 0) / validAmounts.length; // 벤더별 비교 데이터 구성 const vendorComparisons: VendorComparison[] = vendorData.map((v, index) => { const differences: string[] = []; const criticalDifferences: string[] = []; // 조건 차이 분석 if (v.vendorCurrency && v.vendorCurrency !== v.buyerCurrency) { criticalDifferences.push(`통화: ${v.buyerCurrency} → ${v.vendorCurrency}`); } if (v.vendorPaymentTermsCode && v.vendorPaymentTermsCode !== v.buyerPaymentTermsCode) { differences.push(`지급조건: ${v.buyerPaymentTermsCode} → ${v.vendorPaymentTermsCode}`); } if (v.vendorIncotermsCode && v.vendorIncotermsCode !== v.buyerIncotermsCode) { differences.push(`인코텀즈: ${v.buyerIncotermsCode} → ${v.vendorIncotermsCode}`); } if (v.vendorDeliveryDate && v.buyerDeliveryDate) { const buyerDate = new Date(v.buyerDeliveryDate); const vendorDate = new Date(v.vendorDeliveryDate); if (vendorDate > buyerDate) { criticalDifferences.push(`납기: ${Math.ceil((vendorDate.getTime() - buyerDate.getTime()) / (1000 * 60 * 60 * 24))}일 지연`); } } if (v.vendorFirstAcceptance === "거부" && v.buyerFirstYn) { criticalDifferences.push("초도품 거부"); } if (v.vendorSparepartAcceptance === "거부" && v.buyerSparepartYn) { criticalDifferences.push("스페어파트 거부"); } return { vendorId: v.vendorId, vendorName: v.vendorName, vendorCode: v.vendorCode, vendorCountry: v.vendorCountry, responseId: v.responseId || 0, participationStatus: v.participationStatus || "미응답", responseStatus: v.responseStatus || "대기중", submittedAt: v.submittedAt, totalAmount: v.totalAmount || 0, currency: v.responseCurrency || v.buyerCurrency || "USD", rank: 0, // 나중에 계산 priceVariance: v.totalAmount ? ((v.totalAmount - avgAmount) / avgAmount) * 100 : 0, buyerConditions: { currency: v.buyerCurrency || "USD", paymentTermsCode: v.buyerPaymentTermsCode || "", paymentTermsDesc: paymentTermsMap.get(v.buyerPaymentTermsCode || ""), incotermsCode: v.buyerIncotermsCode || "", incotermsDesc: incotermsMap.get(v.buyerIncotermsCode || ""), deliveryDate: v.buyerDeliveryDate, contractDuration: v.buyerContractDuration, taxCode: v.buyerTaxCode, placeOfShipping: v.buyerPlaceOfShipping, placeOfDestination: v.buyerPlaceOfDestination, firstYn: v.buyerFirstYn || false, firstDescription: v.buyerFirstDescription, sparepartYn: v.buyerSparepartYn || false, sparepartDescription: v.buyerSparepartDescription, materialPriceRelatedYn: v.buyerMaterialPriceRelatedYn || false, }, vendorConditions: { currency: v.vendorCurrency, paymentTermsCode: v.vendorPaymentTermsCode, paymentTermsDesc: paymentTermsMap.get(v.vendorPaymentTermsCode || ""), incotermsCode: v.vendorIncotermsCode, incotermsDesc: incotermsMap.get(v.vendorIncotermsCode || ""), deliveryDate: v.vendorDeliveryDate, contractDuration: v.vendorContractDuration, taxCode: v.vendorTaxCode, placeOfShipping: v.vendorPlaceOfShipping, placeOfDestination: v.vendorPlaceOfDestination, firstAcceptance: v.vendorFirstAcceptance, firstDescription: v.vendorFirstDescription, sparepartAcceptance: v.vendorSparepartAcceptance, sparepartDescription: v.vendorSparepartDescription, materialPriceRelatedYn: v.vendorMaterialPriceRelatedYn, materialPriceRelatedReason: v.vendorMaterialPriceRelatedReason, }, conditionDifferences: { hasDifferences: differences.length > 0 || criticalDifferences.length > 0, differences, criticalDifferences, }, generalRemark: v.generalRemark, technicalProposal: v.technicalProposal, }; }); // 가격 순위 계산 vendorComparisons.sort((a, b) => a.totalAmount - b.totalAmount); vendorComparisons.forEach((v, index) => { v.rank = index + 1; }); // PR 아이템별 비교 데이터 구성 const prItemComparisons: PrItemComparison[] = prItems.map(item => { const itemQuotes = quotationItems .filter(q => q.prItemId === item.id) .map(q => { const vendor = vendorData.find(v => v.responseId === q.vendorResponseId); return { vendorId: vendor?.vendorId || 0, vendorName: vendor?.vendorName || "", unitPrice: q.unitPrice || 0, totalPrice: q.totalPrice || 0, currency: q.currency || "USD", quotedQuantity: q.quantity || 0, deliveryDate: q.deliveryDate, leadTime: q.leadTime, manufacturer: q.manufacturer, modelNo: q.modelNo, technicalCompliance: q.technicalCompliance || true, alternativeProposal: q.alternativeProposal, itemRemark: q.itemRemark, priceRank: 0, }; }); // 아이템별 가격 순위 itemQuotes.sort((a, b) => a.unitPrice - b.unitPrice); itemQuotes.forEach((q, index) => { q.priceRank = index + 1; }); const unitPrices = itemQuotes.map(q => q.unitPrice); const avgPrice = unitPrices.reduce((a, b) => a + b, 0) / unitPrices.length || 0; const variance = Math.sqrt( unitPrices.reduce((sum, price) => sum + Math.pow(price - avgPrice, 2), 0) / unitPrices.length ); return { prItemId: item.id, prNo: item.prNo || "", prItem: item.prItem || "", materialCode: item.materialCode || "", materialDescription: item.materialDescription || "", requestedQuantity: item.quantity || 0, uom: item.uom || "", requestedDeliveryDate: item.deliveryDate, vendorQuotes: itemQuotes, priceAnalysis: { lowestPrice: Math.min(...unitPrices) || 0, highestPrice: Math.max(...unitPrices) || 0, averagePrice: avgPrice, priceVariance: variance, }, }; }); // 최종 데이터 구성 return { rfqInfo: rfqData[0], vendors: vendorComparisons, prItems: prItemComparisons, summary: { lowestBidder: vendorComparisons[0]?.vendorName || "", highestBidder: vendorComparisons[vendorComparisons.length - 1]?.vendorName || "", priceRange: { min: minAmount, max: maxAmount, average: avgAmount, }, currency: vendorComparisons[0]?.currency || "USD", }, }; } catch (error) { console.error("견적 비교 데이터 조회 실패:", error); return null; } }