diff options
Diffstat (limited to 'lib/rfq-last/compare-action.ts')
| -rw-r--r-- | lib/rfq-last/compare-action.ts | 477 |
1 files changed, 324 insertions, 153 deletions
diff --git a/lib/rfq-last/compare-action.ts b/lib/rfq-last/compare-action.ts index 2be594e9..1a50a373 100644 --- a/lib/rfq-last/compare-action.ts +++ b/lib/rfq-last/compare-action.ts @@ -1,7 +1,7 @@ "use server"; import db from "@/db/db"; -import { eq, and, inArray,ne } from "drizzle-orm"; +import { eq, and, inArray, ne, asc, desc } from "drizzle-orm"; import { rfqsLast, rfqLastDetails, @@ -10,7 +10,9 @@ import { rfqLastVendorQuotationItems, vendors, paymentTerms, - incoterms,vendorSelections + incoterms, + vendorSelections, + users } from "@/db/schema"; import { revalidatePath } from "next/cache"; import { getServerSession } from "next-auth/next" @@ -44,14 +46,10 @@ export interface ComparisonData { }; } -export interface VendorComparison { - vendorId: number; - vendorName: string; - vendorCode: string; - vendorCountry?: string; - - // 응답 정보 +// 벤더의 각 차수별 응답 정보 +export interface VendorResponseVersion { responseId: number; + responseVersion: number; participationStatus: string; responseStatus: string; submittedAt: Date | null; @@ -62,7 +60,58 @@ export interface VendorComparison { rank?: number; priceVariance?: number; - // 구매자 제시 조건 + // 벤더 제안 조건 + 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; + + // 품목별 견적 아이템 정보 추가 + quotationItems?: { + prItemId: number; + unitPrice: number; + totalPrice: number; + currency: string; + quantity: number; + deliveryDate?: Date | null; + leadTime?: number; + manufacturer?: string; + modelNo?: string; + }[]; +} + +export interface VendorComparison { + vendorId: number; + vendorName: string; + vendorCode: string; + vendorCountry?: string; + + // 구매자 제시 조건 (모든 차수에 공통) buyerConditions: { currency: string; paymentTermsCode: string; @@ -81,7 +130,25 @@ export interface VendorComparison { 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; paymentTermsCode?: string; @@ -101,14 +168,14 @@ export interface VendorComparison { materialPriceRelatedReason?: string; }; - // 조건 차이 분석 + // 레거시 호환: 최신 응답의 조건 차이 분석 conditionDifferences: { hasDifferences: boolean; differences: string[]; criticalDifferences: string[]; }; - // 비고 + // 레거시 호환: 최신 응답의 비고 generalRemark?: string; technicalProposal?: string; @@ -188,7 +255,7 @@ export async function getComparisonData( if (!rfqData[0]) return null; - // 2. 벤더별 정보, 응답, 선정 정보 조회 + // 2a. 벤더 기본 정보 + 구매자 조건 + 선정 정보 조회 const vendorData = await db .select({ // 벤더 정보 @@ -228,14 +295,30 @@ export async function getComparisonData( 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. 모든 차수의 벤더 응답 조회 (isLatest 조건 제거) + 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, - responseCurrency: rfqLastVendorResponses.currency, + currency: rfqLastVendorResponses.currency, // 벤더 제안 조건 vendorCurrency: rfqLastVendorResponses.vendorCurrency, @@ -260,24 +343,14 @@ export async function getComparisonData( 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, + .from(rfqLastVendorResponses) + .where( and( - eq(rfqLastVendorResponses.vendorId, vendors.id), eq(rfqLastVendorResponses.rfqsLastId, rfqId), - eq(rfqLastVendorResponses.isLatest, true) + inArray(rfqLastVendorResponses.vendorId, vendorIds) ) ) - .where(inArray(vendors.id, vendorIds)); + .orderBy(asc(rfqLastVendorResponses.vendorId), asc(rfqLastVendorResponses.responseVersion)); // 3. 선정자 이름 조회 (선정된 업체가 있는 경우) const selectedVendor = vendorData.find(v => v.isSelected); @@ -328,90 +401,165 @@ export async function getComparisonData( .from(rfqPrItems) .where(eq(rfqPrItems.rfqsLastId, rfqId)); - // 6. 벤더별 견적 아이템 조회 - 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) - ) - ); - - // 7. 데이터 가공 및 분석 - 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; - - // 8. 벤더별 비교 데이터 구성 - 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}`); + // 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)) + : []; + + // 7. 데이터 가공 및 분석 - 각 벤더별 최신 차수 기준으로 평균 계산 + // 각 벤더별로 가장 높은 responseVersion을 가진 응답 찾기 + const latestResponsesByVendor = new Map<number, typeof allVendorResponses[0]>(); + 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: VendorComparison[] = vendorData.map((v) => { + // 이 벤더의 모든 응답 가져오기 + const vendorResponses = allVendorResponses.filter(r => r.vendorId === v.vendorId); - 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))}일 지연`); + // 차수별 응답 정보 구성 + 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 (v.vendorFirstAcceptance === "거부" && v.buyerFirstYn) { - criticalDifferences.push("초도품 거부"); - } - - if (v.vendorSparepartAcceptance === "거부" && v.buyerSparepartYn) { - criticalDifferences.push("스페어파트 거부"); - } + 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, + leadTime: q.leadTime, + manufacturer: q.manufacturer, + modelNo: q.modelNo, + })); + + 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, + technicalProposal: resp.technicalProposal, + quotationItems: responseQuotationItems, + }; + }); + + // 최신 응답 찾기 + 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, - - 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, + vendorCode: v.vendorCode || "", + vendorCountry: v.vendorCountry || undefined, + // 구매자 제시 조건 buyerConditions: { currency: v.buyerCurrency || "USD", paymentTermsCode: v.buyerPaymentTermsCode || "", @@ -430,33 +578,48 @@ export async function getComparisonData( 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, + // 차수별 응답 배열 + 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: { - hasDifferences: differences.length > 0 || criticalDifferences.length > 0, - differences, - criticalDifferences, + conditionDifferences: latestResp?.conditionDifferences || { + hasDifferences: false, + differences: [], + criticalDifferences: [], }, - generalRemark: v.generalRemark, - technicalProposal: v.technicalProposal, + generalRemark: latestResp?.generalRemark, + technicalProposal: latestResp?.technicalProposal, // 선정 관련 정보 isSelected: v.isSelected || false, @@ -487,7 +650,10 @@ export async function getComparisonData( const itemQuotes = quotationItems .filter(q => q.prItemId === item.id) .map(q => { - const vendor = vendorData.find(v => v.responseId === q.vendorResponseId); + // 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 || "", @@ -495,13 +661,13 @@ export async function getComparisonData( totalPrice: q.totalPrice || 0, currency: q.currency || "USD", quotedQuantity: q.quantity || 0, - deliveryDate: q.deliveryDate, - leadTime: q.leadTime, - manufacturer: q.manufacturer, - modelNo: q.modelNo, + deliveryDate: q.deliveryDate || undefined, + leadTime: q.leadTime || undefined, + manufacturer: q.manufacturer || undefined, + modelNo: q.modelNo || undefined, technicalCompliance: q.technicalCompliance || true, - alternativeProposal: q.alternativeProposal, - itemRemark: q.itemRemark, + alternativeProposal: q.alternativeProposal || undefined, + itemRemark: q.itemRemark || undefined, priceRank: 0, }; }); @@ -555,7 +721,14 @@ export async function getComparisonData( // 11. 최종 데이터 반환 return { - rfqInfo: rfqData[0], + 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: { @@ -586,11 +759,17 @@ interface SelectVendorParams { priceRank: number; hasConditionDifferences: boolean; criticalDifferences: string[]; - userId: number; // 현재 사용자 ID } 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 상태 확인 @@ -613,7 +792,7 @@ export async function selectVendor(params: SelectVendorParams) { .set({ isSelected: false, updatedAt: new Date(), - updatedBy: params.userId + updatedBy: userId }) .where( and( @@ -629,11 +808,11 @@ export async function selectVendor(params: SelectVendorParams) { isSelected: true, selectionDate: new Date(), selectionReason: params.selectionReason, - selectedBy: params.userId, + selectedBy: userId, totalAmount: params.totalAmount.toString(), priceRank: params.priceRank, updatedAt: new Date(), - updatedBy: params.userId, + updatedBy: userId, }) .where( and( @@ -868,16 +1047,8 @@ export async function processSelectionApproval( throw new Error("선정된 업체를 찾을 수 없습니다."); } - // 승인된 경우 RFQ 상태 업데이트 - if (action === "승인") { - await tx - .update(rfqsLast) - .set({ - status: "계약 진행중", - updatedAt: new Date(), - }) - .where(eq(rfqsLast.id, rfqId)); - } + // 승인된 경우 RFQ 상태는 이미 "최종업체선정"이므로 별도 업데이트 불필요 + // (선정 시 이미 "최종업체선정"으로 설정됨) }); revalidatePath(`/evcp/rfq-last/${rfqId}`); @@ -950,8 +1121,8 @@ export async function getRfqQuotationStatus(rfqId: number) { .select({ id: rfqLastDetails.id, vendorId: rfqLastDetails.vendorsId, - vendorName: vendors.name, - vendorCode: vendors.code, + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, totalAmount: rfqLastDetails.totalAmount, currency: rfqLastDetails.currency, priceRank: rfqLastDetails.priceRank, |