"use server"; import db from "@/db/db"; import { eq, and, inArray, ne, asc, isNotNull } from "drizzle-orm"; import { rfqsLast, rfqLastDetails, rfqPrItems, rfqLastVendorResponses, rfqLastVendorQuotationItems, rfqLastVendorAttachments, vendors, paymentTerms, incoterms, users } from "@/db/schema"; import { revalidatePath } from "next/cache"; import { getServerSession } from "next-auth/next" import { authOptions } from "@/app/api/auth/[...nextauth]/route" // ===== 타입 정의 ===== 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 VendorResponseVersion { responseId: number; responseVersion: number; participationStatus: string; responseStatus: string; submittedAt: Date | null; // 가격 정보 totalAmount: number; currency: string; rank?: number; priceVariance?: number; // 벤더 제안 조건 vendorConditions: { currency?: string | null; paymentTermsCode?: string | null; paymentTermsDesc?: string | null; incotermsCode?: string | null; incotermsDesc?: string | null; deliveryDate?: Date | null; contractDuration?: string | null; taxCode?: string | null; placeOfShipping?: string | null; placeOfDestination?: string | null; firstAcceptance?: "수용" | "부분수용" | "거부" | null; firstDescription?: string | null; sparepartAcceptance?: "수용" | "부분수용" | "거부" | null; sparepartDescription?: string | null; materialPriceRelatedYn?: boolean | null; materialPriceRelatedReason?: string | null; }; // 조건 차이 분석 conditionDifferences: { hasDifferences: boolean; differences: string[]; criticalDifferences: string[]; }; // 비고 generalRemark?: string | null; technicalProposal?: string | null; // 품목별 견적 아이템 정보 추가 quotationItems?: { prItemId: number; unitPrice: number; totalPrice: number; currency: string; quantity: number; deliveryDate: Date | null | undefined; leadTime: number | null | undefined; manufacturer: string | null | undefined; modelNo: string | null | undefined; }[]; // 첨부파일 정보 attachments?: VendorAttachment[]; } export interface VendorComparison { vendorId: number; vendorName: string; vendorCode: string; vendorCountry?: string; // 구매자 제시 조건 (모든 차수에 공통) buyerConditions: { currency: string; paymentTermsCode: string; paymentTermsDesc?: string; incotermsCode: string; incotermsDesc?: string; deliveryDate: Date | null; contractDuration?: string | null; taxCode?: string | null; placeOfShipping?: string | null; placeOfDestination?: string | null; firstYn: boolean; firstDescription?: string | null; sparepartYn: boolean; sparepartDescription?: string | null; materialPriceRelatedYn: boolean; }; // 차수별 응답 배열 (최신 순) responses: VendorResponseVersion[]; // 최신 응답 정보 (편의성) latestResponse?: VendorResponseVersion; // 응답 정보 (레거시, 하위 호환) responseId: number; participationStatus: string; responseStatus: string; submittedAt: Date | null; // 가격 정보 (최신 응답 기준, 레거시 호환) totalAmount: number; currency: string; rank?: number; priceVariance?: number; // 레거시 호환: 최신 응답의 조건 정보 vendorConditions: { currency?: string | null; paymentTermsCode?: string | null; paymentTermsDesc?: string | null; incotermsCode?: string | null; incotermsDesc?: string | null; deliveryDate?: Date | null; contractDuration?: string | null; taxCode?: string | null; placeOfShipping?: string | null; placeOfDestination?: string | null; firstAcceptance?: "수용" | "부분수용" | "거부" | null; firstDescription?: string | null; sparepartAcceptance?: "수용" | "부분수용" | "거부" | null; sparepartDescription?: string | null; materialPriceRelatedYn?: boolean | null; materialPriceRelatedReason?: string | null; }; // 레거시 호환: 최신 응답의 조건 차이 분석 conditionDifferences: { hasDifferences: boolean; differences: string[]; criticalDifferences: string[]; }; // 레거시 호환: 최신 응답의 비고 generalRemark?: string | null; technicalProposal?: string | null; // 선정 관련 정보 isSelected?: boolean; selectionDate?: Date | null; selectionReason?: string | null; selectedBy?: number | null; selectedByName?: string; selectionApprovalStatus?: "대기" | "승인" | "반려" | null; selectionApprovedBy?: number | null; selectionApprovedAt?: Date | null; selectionApprovalComment?: string | null; // 계약 관련 정보 추가 contractStatus?: string | null; contractNo?: string | null; contractCreatedAt?: Date | null; } export interface PrItemComparison { prItemId: number; prNo: string; prItem: string; materialCode: string; materialDescription: string; materialCategory?: string; requestedQuantity: number; uom: string; size?: string; grossWeight?: number; gwUom?: string; requestedDeliveryDate: Date | null; remark?: string; 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 interface VendorAttachment { id: number; attachmentType: string; documentNo?: string; fileName: string; originalFileName: string; filePath: string; fileSize?: number; fileType?: string; description?: string; validFrom?: Date | null; validTo?: Date | null; uploadedBy: number; uploadedAt: Date; uploaderName?: string; } // ===== 메인 조회 함수 ===== 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, dueDate: rfqsLast.dueDate, packageNo: rfqsLast.packageNo, packageName: rfqsLast.packageName, }) .from(rfqsLast) .where(eq(rfqsLast.id, rfqId)) .limit(1); if (!rfqData[0]) return null; // 2a. 벤더 기본 정보 + 구매자 조건 + 선정 정보 조회 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, // 선정 관련 정보 isSelected: rfqLastDetails.isSelected, selectionDate: rfqLastDetails.selectionDate, selectionReason: rfqLastDetails.selectionReason, selectedBy: rfqLastDetails.selectedBy, selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus, selectionApprovedBy: rfqLastDetails.selectionApprovedBy, selectionApprovedAt: rfqLastDetails.selectionApprovedAt, selectionApprovalComment: rfqLastDetails.selectionApprovalComment, // 계약 관련 정보 contractStatus: rfqLastDetails.contractStatus, contractNo: rfqLastDetails.contractNo, contractCreatedAt: rfqLastDetails.contractCreatedAt, }) .from(vendors) .innerJoin( rfqLastDetails, and( eq(rfqLastDetails.vendorsId, vendors.id), eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isLatest, true) ) ) .where(inArray(vendors.id, vendorIds)); // 2b. 벤더가 실제 제출한 응답만 조회 (submittedAt이 null이 아닌 것만) const allVendorResponses = await db .select({ responseId: rfqLastVendorResponses.id, vendorId: rfqLastVendorResponses.vendorId, responseVersion: rfqLastVendorResponses.responseVersion, isLatest: rfqLastVendorResponses.isLatest, participationStatus: rfqLastVendorResponses.participationStatus, responseStatus: rfqLastVendorResponses.status, submittedAt: rfqLastVendorResponses.submittedAt, totalAmount: rfqLastVendorResponses.totalAmount, currency: 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(rfqLastVendorResponses) .where( and( eq(rfqLastVendorResponses.rfqsLastId, rfqId), inArray(rfqLastVendorResponses.vendorId, vendorIds), isNotNull(rfqLastVendorResponses.submittedAt) // 벤더가 실제 제출한 것만 ) ) .orderBy(asc(rfqLastVendorResponses.vendorId), asc(rfqLastVendorResponses.responseVersion)); // 3. 선정자 이름 조회 (선정된 업체가 있는 경우) const selectedVendor = vendorData.find(v => v.isSelected); let selectedByName = ""; if (selectedVendor?.selectedBy) { const [user] = await db .select({ name: users.name }) .from(users) .where(eq(users.id, selectedVendor.selectedBy)) .limit(1); selectedByName = user?.name || ""; } // 4. 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]) ); // 5. PR Items 조회 (추가 필드 포함) const prItems = await db .select({ id: rfqPrItems.id, prNo: rfqPrItems.prNo, prItem: rfqPrItems.prItem, materialCode: rfqPrItems.materialCode, materialDescription: rfqPrItems.materialDescription, materialCategory: rfqPrItems.materialCategory, quantity: rfqPrItems.quantity, uom: rfqPrItems.uom, size: rfqPrItems.size, grossWeight: rfqPrItems.grossWeight, gwUom: rfqPrItems.gwUom, deliveryDate: rfqPrItems.deliveryDate, remark: rfqPrItems.remark, }) .from(rfqPrItems) .where(eq(rfqPrItems.rfqsLastId, rfqId)); // 6. 벤더별 견적 아이템 조회 (모든 응답 버전 포함) const allResponseIds = allVendorResponses.map(r => r.responseId); const quotationItems = allResponseIds.length > 0 ? 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, allResponseIds)) : []; // 6b. 벤더 첨부파일 조회 (모든 응답 버전 포함) const vendorAttachments = allResponseIds.length > 0 ? await db .select({ id: rfqLastVendorAttachments.id, vendorResponseId: rfqLastVendorAttachments.vendorResponseId, attachmentType: rfqLastVendorAttachments.attachmentType, documentNo: rfqLastVendorAttachments.documentNo, fileName: rfqLastVendorAttachments.fileName, originalFileName: rfqLastVendorAttachments.originalFileName, filePath: rfqLastVendorAttachments.filePath, fileSize: rfqLastVendorAttachments.fileSize, fileType: rfqLastVendorAttachments.fileType, description: rfqLastVendorAttachments.description, validFrom: rfqLastVendorAttachments.validFrom, validTo: rfqLastVendorAttachments.validTo, uploadedBy: rfqLastVendorAttachments.uploadedBy, uploadedAt: rfqLastVendorAttachments.uploadedAt, uploaderName: users.name, }) .from(rfqLastVendorAttachments) .leftJoin(users, eq(rfqLastVendorAttachments.uploadedBy, users.id)) .where(inArray(rfqLastVendorAttachments.vendorResponseId, allResponseIds)) : []; // 7. 데이터 가공 및 분석 - 각 벤더별 최신 차수 기준으로 평균 계산 // 각 벤더별로 가장 높은 responseVersion을 가진 응답 찾기 const latestResponsesByVendor = new Map(); allVendorResponses.forEach(response => { const existing = latestResponsesByVendor.get(response.vendorId); if (!existing || response.responseVersion > existing.responseVersion) { latestResponsesByVendor.set(response.vendorId, response); } }); const latestResponses = Array.from(latestResponsesByVendor.values()); const validAmounts = latestResponses .map(r => r.totalAmount) .filter((a): a is number => a != null && a > 0); console.log("Latest responses:", latestResponses.map(r => ({ vendorId: r.vendorId, version: r.responseVersion, amount: r.totalAmount, isLatest: r.isLatest }))); console.log("Valid amounts for average:", validAmounts); const minAmount = validAmounts.length > 0 ? Math.min(...validAmounts) : 0; const maxAmount = validAmounts.length > 0 ? Math.max(...validAmounts) : 0; const avgAmount = validAmounts.length > 0 ? validAmounts.reduce((a, b) => a + b, 0) / validAmounts.length : 0; // 8. 벤더별 비교 데이터 구성 (차수별 응답 포함) const vendorComparisons = vendorData.map((v) => { // 이 벤더의 모든 응답 가져오기 const vendorResponses = allVendorResponses.filter(r => r.vendorId === v.vendorId); // 차수별 응답 정보 구성 const responses: VendorResponseVersion[] = vendorResponses.map(resp => { const differences: string[] = []; const criticalDifferences: string[] = []; // 조건 차이 분석 if (resp.vendorCurrency && resp.vendorCurrency !== v.buyerCurrency) { criticalDifferences.push(`통화: ${v.buyerCurrency} → ${resp.vendorCurrency}`); } if (resp.vendorPaymentTermsCode && resp.vendorPaymentTermsCode !== v.buyerPaymentTermsCode) { differences.push(`지급조건: ${v.buyerPaymentTermsCode} → ${resp.vendorPaymentTermsCode}`); } if (resp.vendorIncotermsCode && resp.vendorIncotermsCode !== v.buyerIncotermsCode) { differences.push(`인코텀즈: ${v.buyerIncotermsCode} → ${resp.vendorIncotermsCode}`); } if (resp.vendorDeliveryDate && v.buyerDeliveryDate) { const buyerDate = new Date(v.buyerDeliveryDate); const vendorDate = new Date(resp.vendorDeliveryDate); if (vendorDate > buyerDate) { criticalDifferences.push(`납기: ${Math.ceil((vendorDate.getTime() - buyerDate.getTime()) / (1000 * 60 * 60 * 24))}일 지연`); } } if (resp.vendorFirstAcceptance === "거부" && v.buyerFirstYn) { criticalDifferences.push("초도품 거부"); } if (resp.vendorSparepartAcceptance === "거부" && v.buyerSparepartYn) { criticalDifferences.push("스페어파트 거부"); } // 이 응답의 품목별 견적 아이템 가져오기 const responseQuotationItems = quotationItems .filter(q => q.vendorResponseId === resp.responseId) .map(q => ({ prItemId: q.prItemId, unitPrice: q.unitPrice || 0, totalPrice: q.totalPrice || 0, currency: q.currency || "USD", quantity: q.quantity || 0, deliveryDate: q.deliveryDate || undefined, leadTime: q.leadTime || undefined, manufacturer: q.manufacturer || undefined, modelNo: q.modelNo || undefined, })); // 이 응답의 첨부파일 가져오기 const responseAttachments = vendorAttachments .filter(a => a.vendorResponseId === resp.responseId) .map(a => ({ id: a.id, attachmentType: a.attachmentType, documentNo: a.documentNo || undefined, fileName: a.fileName, originalFileName: a.originalFileName, filePath: a.filePath, fileSize: a.fileSize || undefined, fileType: a.fileType || undefined, description: a.description || undefined, validFrom: a.validFrom || undefined, validTo: a.validTo || undefined, uploadedBy: a.uploadedBy, uploadedAt: a.uploadedAt, uploaderName: a.uploaderName || undefined, })); return { responseId: resp.responseId, responseVersion: resp.responseVersion, participationStatus: resp.participationStatus || "미응답", responseStatus: resp.responseStatus || "대기중", submittedAt: resp.submittedAt, totalAmount: resp.totalAmount || 0, currency: resp.currency || v.buyerCurrency || "USD", rank: 0, // 나중에 계산 priceVariance: resp.totalAmount ? ((resp.totalAmount - avgAmount) / avgAmount) * 100 : 0, vendorConditions: { currency: resp.vendorCurrency || undefined, paymentTermsCode: resp.vendorPaymentTermsCode || undefined, paymentTermsDesc: paymentTermsMap.get(resp.vendorPaymentTermsCode || ""), incotermsCode: resp.vendorIncotermsCode || undefined, incotermsDesc: incotermsMap.get(resp.vendorIncotermsCode || ""), deliveryDate: resp.vendorDeliveryDate, contractDuration: resp.vendorContractDuration || undefined, taxCode: resp.vendorTaxCode || undefined, placeOfShipping: resp.vendorPlaceOfShipping || undefined, placeOfDestination: resp.vendorPlaceOfDestination || undefined, firstAcceptance: resp.vendorFirstAcceptance || undefined, firstDescription: resp.vendorFirstDescription || undefined, sparepartAcceptance: resp.vendorSparepartAcceptance || undefined, sparepartDescription: resp.vendorSparepartDescription || undefined, materialPriceRelatedYn: resp.vendorMaterialPriceRelatedYn || undefined, materialPriceRelatedReason: resp.vendorMaterialPriceRelatedReason || undefined, }, conditionDifferences: { hasDifferences: differences.length > 0 || criticalDifferences.length > 0, differences, criticalDifferences, }, generalRemark: resp.generalRemark || undefined, technicalProposal: resp.technicalProposal || undefined, quotationItems: responseQuotationItems, attachments: responseAttachments, }; }); // 최신 응답 찾기 const latestResp = responses.find(r => r.responseVersion === Math.max(...responses.map(r => r.responseVersion))); return { vendorId: v.vendorId, vendorName: v.vendorName, vendorCode: v.vendorCode || "", vendorCountry: v.vendorCountry || undefined, // 구매자 제시 조건 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 || undefined, taxCode: v.buyerTaxCode || undefined, placeOfShipping: v.buyerPlaceOfShipping || undefined, placeOfDestination: v.buyerPlaceOfDestination || undefined, firstYn: v.buyerFirstYn || false, firstDescription: v.buyerFirstDescription || undefined, sparepartYn: v.buyerSparepartYn || false, sparepartDescription: v.buyerSparepartDescription || undefined, materialPriceRelatedYn: v.buyerMaterialPriceRelatedYn || false, }, // 차수별 응답 배열 responses, latestResponse: latestResp, // 레거시 호환 필드 (최신 응답 기준) responseId: latestResp?.responseId || 0, participationStatus: latestResp?.participationStatus || "미응답", responseStatus: latestResp?.responseStatus || "대기중", submittedAt: latestResp?.submittedAt || null, totalAmount: latestResp?.totalAmount || 0, currency: latestResp?.currency || v.buyerCurrency || "USD", rank: 0, // 나중에 계산 priceVariance: latestResp?.priceVariance || 0, vendorConditions: latestResp?.vendorConditions || { currency: undefined, paymentTermsCode: undefined, paymentTermsDesc: undefined, incotermsCode: undefined, incotermsDesc: undefined, deliveryDate: null, contractDuration: undefined, taxCode: undefined, placeOfShipping: undefined, placeOfDestination: undefined, firstAcceptance: undefined, firstDescription: undefined, sparepartAcceptance: undefined, sparepartDescription: undefined, materialPriceRelatedYn: undefined, materialPriceRelatedReason: undefined, }, conditionDifferences: latestResp?.conditionDifferences || { hasDifferences: false, differences: [], criticalDifferences: [], }, generalRemark: latestResp?.generalRemark || undefined, technicalProposal: latestResp?.technicalProposal || undefined, // 선정 관련 정보 isSelected: v.isSelected || false, selectionDate: v.selectionDate, selectionReason: v.selectionReason || undefined, selectedBy: v.selectedBy || undefined, selectedByName: v.isSelected ? selectedByName : undefined, selectionApprovalStatus: v.selectionApprovalStatus || undefined, selectionApprovedBy: v.selectionApprovedBy || undefined, selectionApprovedAt: v.selectionApprovedAt, selectionApprovalComment: v.selectionApprovalComment || undefined, // 계약 관련 정보 contractStatus: v.contractStatus || undefined, contractNo: v.contractNo || undefined, contractCreatedAt: v.contractCreatedAt, }; }); // 9. 가격 순위 계산 vendorComparisons.sort((a, b) => a.totalAmount - b.totalAmount); vendorComparisons.forEach((v, index) => { v.rank = index + 1; }); // 10. PR 아이템별 비교 데이터 구성 const prItemComparisons: PrItemComparison[] = prItems.map(item => { const itemQuotes = quotationItems .filter(q => q.prItemId === item.id) .map(q => { // vendorResponseId로 응답 찾기 const response = allVendorResponses.find(r => r.responseId === q.vendorResponseId); // 그 응답의 vendorId로 벤더 정보 찾기 const vendor = vendorData.find(v => v.vendorId === response?.vendorId); 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 || undefined, leadTime: q.leadTime || undefined, manufacturer: q.manufacturer || undefined, modelNo: q.modelNo || undefined, technicalCompliance: q.technicalCompliance || true, alternativeProposal: q.alternativeProposal || undefined, itemRemark: q.itemRemark || undefined, 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 || "", materialCategory: item.materialCategory || undefined, requestedQuantity: item.quantity || 0, uom: item.uom || "", size: item.size || undefined, grossWeight: item.grossWeight || undefined, gwUom: item.gwUom || undefined, requestedDeliveryDate: item.deliveryDate, remark: item.remark || undefined, vendorQuotes: itemQuotes, priceAnalysis: { lowestPrice: Math.min(...unitPrices) || 0, highestPrice: Math.max(...unitPrices) || 0, averagePrice: avgPrice, priceVariance: variance, }, }; }); console.log({ 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", }, }); // 11. 최종 데이터 반환 return { rfqInfo: { ...rfqData[0], rfqCode: rfqData[0].rfqCode || "", rfqTitle: rfqData[0].rfqTitle || "", rfqType: rfqData[0].rfqType || "", packageNo: rfqData[0].packageNo || undefined, packageName: rfqData[0].packageName || undefined, }, 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; } } interface SelectVendorParams { rfqId: number; vendorId: number; vendorName: string; vendorCode: string; totalAmount: number; currency: string; selectionReason: string; priceRank: number; hasConditionDifferences: boolean; criticalDifferences: string[]; } export async function selectVendor(params: SelectVendorParams) { try { // 세션에서 사용자 ID 가져오기 const session = await getServerSession(authOptions); if (!session?.user) { throw new Error("인증이 필요합니다."); } const userId = Number(session.user.id); // 트랜잭션 시작 const result = await db.transaction(async (tx) => { // 1. RFQ 상태 확인 const [rfq] = await tx .select() .from(rfqsLast) .where(eq(rfqsLast.id, params.rfqId)); if (!rfq) { throw new Error("RFQ를 찾을 수 없습니다."); } if (rfq.status === "최종업체선정") { throw new Error("이미 업체가 선정된 RFQ입니다."); } // 2. 기존에 선정된 업체가 있다면 선정 해제 await tx .update(rfqLastDetails) .set({ isSelected: false, updatedAt: new Date(), updatedBy: userId }) .where( and( eq(rfqLastDetails.rfqsLastId, params.rfqId), eq(rfqLastDetails.isSelected, true) ) ); // 3. 새로운 업체 선정 const [selection] = await tx .update(rfqLastDetails) .set({ isSelected: true, selectionDate: new Date(), selectionReason: params.selectionReason, selectedBy: userId, totalAmount: params.totalAmount.toString(), priceRank: params.priceRank, updatedAt: new Date(), updatedBy: userId, }) .where( and( eq(rfqLastDetails.rfqsLastId, params.rfqId), eq(rfqLastDetails.vendorsId, params.vendorId), eq(rfqLastDetails.isLatest, true) ) ) .returning(); if (!selection) { throw new Error("업체 견적 정보를 찾을 수 없습니다."); } // 4. RFQ 상태 업데이트 await tx .update(rfqsLast) .set({ status: "최종업체선정", updatedAt: new Date(), }) .where(eq(rfqsLast.id, params.rfqId)); // 5. 다른 업체들의 견적은 미선정 상태로 명시적 업데이트 await tx .update(rfqLastDetails) .set({ isSelected: false, updatedAt: new Date(), }) .where( and( eq(rfqLastDetails.rfqsLastId, params.rfqId), eq(rfqLastDetails.isLatest, true), // NOT equal to selected vendor // Drizzle에서는 ne (not equal) 연산자를 사용 ne(rfqLastDetails.vendorsId, params.vendorId) ) ); return selection; }); // 캐시 무효화 revalidatePath(`/evcp/rfq-last/${params.rfqId}`); revalidatePath("/evcp/rfq"); return { success: true, data: result, redirectUrl: `/evcp/rfq-last/${params.rfqId}/selection-complete` }; } catch (error) { console.error("업체 선정 오류:", error); return { success: false, error: error instanceof Error ? error.message : "업체 선정 중 오류가 발생했습니다." }; } } // 업체 선정 취소 export async function cancelVendorSelection(rfqId: number, cancelReason: string) { try { const session = await getServerSession(authOptions) if (!session?.user) { throw new Error("인증이 필요합니다.") } const userId = Number(session.user.id) await db.transaction(async (tx) => { // 선정 정보 업데이트 (취소 사유 기록) const [cancelled] = await tx .update(rfqLastDetails) .set({ isSelected: false, selectionDate: null, selectionReason: null, selectedBy: null, cancelReason: cancelReason, selectionApprovalStatus: null, selectionApprovedBy: null, selectionApprovedAt: null, selectionApprovalComment: null, updatedAt: new Date(), updatedBy: userId, }) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isSelected, true) ) ) .returning(); if (!cancelled) { throw new Error("선정된 업체를 찾을 수 없습니다."); } // RFQ 상태 되돌리기 await tx .update(rfqsLast) .set({ status: "견적접수", updatedAt: new Date(), }) .where(eq(rfqsLast.id, rfqId)); }); revalidatePath(`/evcp/rfq-last/${rfqId}`); revalidatePath("/evcp/rfq-last"); return { success: true }; } catch (error) { console.error("업체 선정 취소 오류:", error); return { success: false, error: error instanceof Error ? error.message : "업체 선정 취소 중 오류가 발생했습니다." }; } } // 선정된 업체 정보 조회 export async function getSelectedVendor(rfqId: number) { try { const [selected] = await db .select({ detail: rfqLastDetails, vendor: vendors, }) .from(rfqLastDetails) .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id)) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isSelected, true), eq(rfqLastDetails.isLatest, true) ) ) .limit(1); return { success: true, data: selected }; } catch (error) { console.error("선정 업체 조회 오류:", error); return { success: false, error: error instanceof Error ? error.message : "선정 업체 조회 중 오류가 발생했습니다." }; } } // 선정 승인 요청 export async function requestSelectionApproval( rfqId: number, vendorId: number, userId: number ) { try { const [updated] = await db .update(rfqLastDetails) .set({ selectionApprovalStatus: "대기", updatedAt: new Date(), updatedBy: userId, }) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.vendorsId, vendorId), eq(rfqLastDetails.isSelected, true), eq(rfqLastDetails.isLatest, true) ) ) .returning(); if (!updated) { throw new Error("선정된 업체를 찾을 수 없습니다."); } revalidatePath(`/evcp/rfq-last/${rfqId}`); return { success: true, data: updated }; } catch (error) { console.error("선정 승인 요청 오류:", error); return { success: false, error: error instanceof Error ? error.message : "선정 승인 요청 중 오류가 발생했습니다." }; } } // 선정 승인/반려 처리 export async function processSelectionApproval( rfqId: number, vendorId: number, action: "승인" | "반려", comment: string, approverId: number ) { try { await db.transaction(async (tx) => { // 선정 승인 정보 업데이트 const [updated] = await tx .update(rfqLastDetails) .set({ selectionApprovalStatus: action, selectionApprovedBy: approverId, selectionApprovedAt: new Date(), selectionApprovalComment: comment, updatedAt: new Date(), updatedBy: approverId, }) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.vendorsId, vendorId), eq(rfqLastDetails.isSelected, true), eq(rfqLastDetails.isLatest, true) ) ) .returning(); if (!updated) { throw new Error("선정된 업체를 찾을 수 없습니다."); } // 승인된 경우 RFQ 상태는 이미 "최종업체선정"이므로 별도 업데이트 불필요 // (선정 시 이미 "최종업체선정"으로 설정됨) }); revalidatePath(`/evcp/rfq-last/${rfqId}`); revalidatePath("/evcp/rfq"); return { success: true, message: action === "승인" ? "업체 선정이 승인되었습니다." : "업체 선정이 반려되었습니다." }; } catch (error) { console.error("선정 승인 처리 오류:", error); return { success: false, error: error instanceof Error ? error.message : "선정 승인 처리 중 오류가 발생했습니다." }; } } // 가격 순위 업데이트 (견적 제출 후 자동 실행) export async function updatePriceRanks(rfqId: number) { try { // 해당 RFQ의 모든 최신 견적 조회 const quotes = await db .select({ id: rfqLastDetails.id, vendorsId: rfqLastDetails.vendorsId, totalAmount: rfqLastDetails.totalAmount, }) .from(rfqLastDetails) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isLatest, true) ) ) .orderBy(asc(rfqLastDetails.totalAmount)); // 순위 업데이트 await db.transaction(async (tx) => { for (let i = 0; i < quotes.length; i++) { await tx .update(rfqLastDetails) .set({ priceRank: i + 1, updatedAt: new Date() }) .where(eq(rfqLastDetails.id, quotes[i].id)); } }); return { success: true, message: "가격 순위가 업데이트되었습니다." }; } catch (error) { console.error("가격 순위 업데이트 오류:", error); return { success: false, error: error instanceof Error ? error.message : "가격 순위 업데이트 중 오류가 발생했습니다." }; } } // RFQ의 모든 견적 상태 조회 export async function getRfqQuotationStatus(rfqId: number) { try { const quotations = await db .select({ id: rfqLastDetails.id, vendorId: rfqLastDetails.vendorsId, vendorName: vendors.vendorName, vendorCode: vendors.vendorCode, totalAmount: rfqLastDetails.totalAmount, currency: rfqLastDetails.currency, priceRank: rfqLastDetails.priceRank, isSelected: rfqLastDetails.isSelected, selectionDate: rfqLastDetails.selectionDate, selectionReason: rfqLastDetails.selectionReason, selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus, emailStatus: rfqLastDetails.emailStatus, lastEmailSentAt: rfqLastDetails.lastEmailSentAt, }) .from(rfqLastDetails) .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id)) .where( and( eq(rfqLastDetails.rfqsLastId, rfqId), eq(rfqLastDetails.isLatest, true) ) ) .orderBy(asc(rfqLastDetails.priceRank)); const selectedVendor = quotations.find(q => q.isSelected); const totalQuotations = quotations.length; const respondedQuotations = quotations.filter(q => q.totalAmount).length; return { success: true, data: { quotations, summary: { total: totalQuotations, responded: respondedQuotations, pending: totalQuotations - respondedQuotations, selected: selectedVendor ? 1 : 0, selectedVendor: selectedVendor || null, } } }; } catch (error) { console.error("RFQ 견적 상태 조회 오류:", error); return { success: false, error: error instanceof Error ? error.message : "견적 상태 조회 중 오류가 발생했습니다." }; } }