'use server' import db from '@/db/db' import { biddings, prItemsForBidding, biddingDocuments, biddingCompanies, vendors, companyPrItemBids, companyConditionResponses, vendorSelectionResults, BiddingListItem, biddingConditions, priceAdjustmentForms } from '@/db/schema' import { specificationMeetings } from '@/db/schema/bidding' import { eq, and, sql, desc, ne } from 'drizzle-orm' import { revalidatePath, revalidateTag } from 'next/cache' import { unstable_cache } from "@/lib/unstable-cache"; import { sendEmail } from '@/lib/mail/sendEmail' import { saveFile } from '@/lib/file-stroage' // 데이터 조회 함수들 export interface BiddingDetailData { bidding: Awaited> quotationDetails: QuotationDetails | null quotationVendors: QuotationVendor[] prItems: Awaited> } // getBiddingById 함수 임포트 (기존 함수 재사용) import { getBiddingById, getPRDetailsAction } from '@/lib/bidding/service' // Promise.all을 사용하여 모든 데이터를 병렬로 조회 (캐시 적용) export async function getBiddingDetailData(biddingId: number): Promise { return unstable_cache( async () => { const [ bidding, quotationDetails, quotationVendors, prItems ] = await Promise.all([ getBiddingById(biddingId), getQuotationDetails(biddingId), getQuotationVendors(biddingId), getPRItemsForBidding(biddingId) ]) return { bidding, quotationDetails, quotationVendors, prItems } }, [`bidding-detail-data-${biddingId}`], { tags: [`bidding-${biddingId}`, 'bidding-detail', 'quotation-vendors', 'pr-items'] } )() } export interface QuotationDetails { biddingId: number estimatedPrice: number // 예상액 lowestQuote: number // 최저견적가 averageQuote: number // 평균견적가 targetPrice: number // 내정가 quotationCount: number // 견적 수 lastUpdated: string // 최종 업데이트일 } export interface QuotationVendor { id: number biddingId: number vendorId: number vendorName: string vendorCode: string contactPerson: string contactEmail: string contactPhone: string quotationAmount: number // 견적금액 currency: string submissionDate: string // 제출일 isWinner: boolean | null // 낙찰여부 (null: 미정, true: 낙찰, false: 탈락) awardRatio: number | null // 발주비율 isBiddingParticipated: boolean | null // 본입찰 참여여부 status: 'pending' | 'submitted' | 'selected' | 'rejected' documents: Array<{ id: number fileName: string originalFileName: string filePath: string uploadedAt: string }> } // 견적 시스템에서 내정가 및 관련 정보를 가져오는 함수 (캐시 적용) export async function getQuotationDetails(biddingId: number): Promise { return unstable_cache( async () => { try { // bidding_companies 테이블에서 견적 데이터를 집계 const quotationStats = await db .select({ biddingId: biddingCompanies.biddingId, estimatedPrice: sql`AVG(${biddingCompanies.finalQuoteAmount})`.as('estimated_price'), lowestQuote: sql`MIN(${biddingCompanies.finalQuoteAmount})`.as('lowest_quote'), averageQuote: sql`AVG(${biddingCompanies.finalQuoteAmount})`.as('average_quote'), targetPrice: sql`AVG(${biddings.targetPrice})`.as('target_price'), quotationCount: sql`COUNT(*)`.as('quotation_count'), lastUpdated: sql`MAX(${biddingCompanies.updatedAt})`.as('last_updated') }) .from(biddingCompanies) .leftJoin(biddings, eq(biddingCompanies.biddingId, biddings.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), sql`${biddingCompanies.finalQuoteAmount} IS NOT NULL` )) .groupBy(biddingCompanies.biddingId) .limit(1) if (quotationStats.length === 0) { return { biddingId, estimatedPrice: 0, lowestQuote: 0, averageQuote: 0, targetPrice: 0, quotationCount: 0, lastUpdated: new Date().toISOString() } } const stat = quotationStats[0] return { biddingId, estimatedPrice: Number(stat.estimatedPrice) || 0, lowestQuote: Number(stat.lowestQuote) || 0, averageQuote: Number(stat.averageQuote) || 0, targetPrice: Number(stat.targetPrice) || 0, quotationCount: Number(stat.quotationCount) || 0, lastUpdated: stat.lastUpdated || new Date().toISOString() } } catch (error) { console.error('Failed to get quotation details:', error) return null } }, [`quotation-details-${biddingId}`], { tags: [`bidding-${biddingId}`, 'quotation-details'] } )() } // bidding_companies 테이블을 메인으로 vendors 테이블을 조인하여 협력업체 정보 조회 export async function getBiddingCompaniesData(biddingId: number) { try { const companies = await db .select({ id: biddingCompanies.id, biddingId: biddingCompanies.biddingId, companyId: biddingCompanies.companyId, companyName: vendors.vendorName, companyCode: vendors.vendorCode, invitationStatus: biddingCompanies.invitationStatus, invitedAt: biddingCompanies.invitedAt, respondedAt: biddingCompanies.respondedAt, preQuoteAmount: biddingCompanies.preQuoteAmount, preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt, isPreQuoteSelected: biddingCompanies.isPreQuoteSelected, finalQuoteAmount: biddingCompanies.finalQuoteAmount, finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, notes: biddingCompanies.notes, contactPerson: biddingCompanies.contactPerson, contactEmail: biddingCompanies.contactEmail, contactPhone: biddingCompanies.contactPhone, createdAt: biddingCompanies.createdAt, updatedAt: biddingCompanies.updatedAt }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where( and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isPreQuoteSelected, true) ) ) .orderBy(desc(biddingCompanies.finalQuoteAmount)) console.log(companies) return companies } catch (error) { console.error('Failed to get bidding companies data:', error) return [] } } // prItemsForBidding 테이블에서 품목 정보 조회 (캐시 적용) export async function getPRItemsForBidding(biddingId: number) { return unstable_cache( async () => { try { const items = await db .select() .from(prItemsForBidding) .where(eq(prItemsForBidding.biddingId, biddingId)) .orderBy(prItemsForBidding.id) return items } catch (error) { console.error('Failed to get PR items for bidding:', error) return [] } }, [`pr-items-for-bidding-${biddingId}`], { tags: [`bidding-${biddingId}`, 'pr-items'] } )() } // 견적 시스템에서 협력업체 정보를 가져오는 함수 (캐시 적용) export async function getQuotationVendors(biddingId: number): Promise { return unstable_cache( async () => { try { // bidding_companies 테이블을 메인으로 vendors를 조인하여 협력업체 정보 조회 const vendorsData = await db .select({ id: biddingCompanies.id, biddingId: biddingCompanies.biddingId, vendorId: biddingCompanies.companyId, vendorName: vendors.vendorName, vendorCode: vendors.vendorCode, contactPerson: biddingCompanies.contactPerson, contactEmail: biddingCompanies.contactEmail, contactPhone: biddingCompanies.contactPhone, quotationAmount: biddingCompanies.finalQuoteAmount, currency: sql`'KRW'`, submissionDate: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, // awardRatio: sql`CASE WHEN ${biddingCompanies.isWinner} THEN 100 ELSE 0 END`, awardRatio: biddingCompanies.awardRatio, isBiddingParticipated: biddingCompanies.isBiddingParticipated, status: sql`CASE WHEN ${biddingCompanies.isWinner} THEN 'selected' WHEN ${biddingCompanies.finalQuoteSubmittedAt} IS NOT NULL THEN 'submitted' WHEN ${biddingCompanies.respondedAt} IS NOT NULL THEN 'submitted' ELSE 'pending' END`, }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isPreQuoteSelected, true) // 본입찰 선정된 업체만 조회 )) .orderBy(desc(biddingCompanies.finalQuoteAmount)) return vendorsData.map(vendor => ({ id: vendor.id, biddingId: vendor.biddingId, vendorId: vendor.vendorId, vendorName: vendor.vendorName || `Vendor ${vendor.vendorId}`, vendorCode: vendor.vendorCode || '', contactPerson: vendor.contactPerson || '', contactEmail: vendor.contactEmail || '', contactPhone: vendor.contactPhone || '', quotationAmount: Number(vendor.quotationAmount) || 0, currency: vendor.currency, submissionDate: vendor.submissionDate ? (vendor.submissionDate instanceof Date ? vendor.submissionDate.toISOString().split('T')[0] : String(vendor.submissionDate).split('T')[0]) : '', isWinner: vendor.isWinner, awardRatio: vendor.awardRatio ? Number(vendor.awardRatio) : null, isBiddingParticipated: vendor.isBiddingParticipated, status: vendor.status as 'pending' | 'submitted' | 'selected' | 'rejected', })) } catch (error) { console.error('Failed to get quotation vendors:', error) return [] } }, [`quotation-vendors-${biddingId}`], { tags: [`bidding-${biddingId}`, 'quotation-vendors'] } )() } // 사전견적 데이터 조회 (내정가 산정용) export async function getPreQuoteData(biddingId: number) { try { const preQuotes = await db .select({ id: biddingCompanies.id, companyId: biddingCompanies.companyId, vendorName: vendors.vendorName, preQuoteAmount: biddingCompanies.preQuoteAmount, submittedAt: biddingCompanies.preQuoteSubmittedAt, }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), sql`${biddingCompanies.preQuoteAmount} IS NOT NULL AND ${biddingCompanies.preQuoteAmount} > 0` )) .orderBy(biddingCompanies.preQuoteAmount) if (preQuotes.length === 0) { return { quotes: [], lowestQuote: null, highestQuote: null, averageQuote: null, quotationCount: 0 } } const amounts = preQuotes .map(q => Number(q.preQuoteAmount)) .filter(amount => !isNaN(amount) && amount > 0) console.log('Pre-quote amounts:', amounts) if (amounts.length === 0) { return { quotes: preQuotes, lowestQuote: null, highestQuote: null, averageQuote: null, quotationCount: 0 } } const lowestQuote = Math.min(...amounts) const highestQuote = Math.max(...amounts) const averageQuote = amounts.reduce((sum, amount) => sum + amount, 0) / amounts.length console.log('Calculated quotes:', { lowestQuote, highestQuote, averageQuote }) return { quotes: preQuotes, lowestQuote, highestQuote, averageQuote, quotationCount: amounts.length } } catch (error) { console.error('Failed to get pre-quote data:', error) return { quotes: [], lowestQuote: null, highestQuote: null, averageQuote: null, quotationCount: 0 } } } // 입찰유형별 내정가 자동 산정 로직 export async function calculateTargetPrice( biddingType: string, budget: number | null, lowestQuote: number | null, highestQuote: number | null ): Promise<{ targetPrice: number; criteria: string }> { const results: Array<{ price: number; description: string }> = [] // 입찰유형별 로직 switch (biddingType) { case 'equipment': case 'construction': case 'service': case 'lease': case 'steel_stock': case 'piping': { // 예산가 85%, 최저견적가 85% 중 최저가 (직전실적가 95% 제외) if (budget) { results.push({ price: budget * 0.85, description: '예산가 85%' }) } if (lowestQuote) { results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' }) } break } case 'transport': { // 예산가 85%, 최저견적가 85% 중 최저가 (직전실적가 95% 제외) // 만약 예산이 없을 경우 최저견적가의 70% if (budget) { results.push({ price: budget * 0.85, description: '예산가 85%' }) } if (lowestQuote) { if (budget) { results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' }) } else { results.push({ price: lowestQuote * 0.70, description: '최저견적가 70% (예산 없음)' }) } } break } case 'waste': { // 예산가 85%, 최저견적가 70% 중 최저가 (직전실적가 95% 제외) if (budget) { results.push({ price: budget * 0.85, description: '예산가 85%' }) } if (lowestQuote) { results.push({ price: lowestQuote * 0.70, description: '최저견적가 70%' }) } break } case 'sale': { // 최고견적가 130% (직전실적가 105% 제외) if (highestQuote) { results.push({ price: highestQuote * 1.30, description: '최고견적가 130%' }) } break } default: { // 기본: 최저견적가 85% if (lowestQuote) { results.push({ price: lowestQuote * 0.85, description: '최저견적가 85%' }) } break } } if (results.length === 0) { return { targetPrice: 0, criteria: '산정 가능한 데이터가 없습니다.' } } // 매각의 경우 최고가, 나머지는 최저가 const prices = results.map(r => r.price).filter(p => !isNaN(p) && isFinite(p)) if (prices.length === 0) { return { targetPrice: 0, criteria: '유효한 가격 데이터가 없습니다.' } } const targetPrice = biddingType === 'sale' ? Math.max(...prices) : Math.min(...prices) if (!isFinite(targetPrice) || isNaN(targetPrice)) { return { targetPrice: 0, criteria: '내정가 계산 오류가 발생했습니다.' } } const selectedResult = results.find(r => r.price === targetPrice) const criteria = `입찰유형: ${biddingType} - ${selectedResult?.description || ''}로 산정` return { targetPrice: Math.round(targetPrice), criteria } } // 내정가 자동 산정 및 업데이트 export async function calculateAndUpdateTargetPrice( biddingId: number, userId: string ) { try { // 입찰 정보 조회 const bidding = await getBiddingById(biddingId) if (!bidding) { return { success: false, error: '입찰 정보를 찾을 수 없습니다.' } } // 사전견적 데이터 조회 const preQuoteData = await getPreQuoteData(biddingId) if (preQuoteData.quotationCount === 0) { return { success: false, error: '사전견적 데이터가 없습니다.' } } // 내정가 산정 console.log('Bidding data for calculation:', { biddingType: bidding.biddingType, budget: bidding.budget, preQuoteData }) const { targetPrice, criteria } = await calculateTargetPrice( bidding.biddingType || '', bidding.budget ? Number(bidding.budget) : null, preQuoteData.lowestQuote, preQuoteData.highestQuote ) console.log('Calculated target price:', { targetPrice, criteria }) if (!targetPrice || targetPrice <= 0 || isNaN(targetPrice)) { return { success: false, error: `내정가 산정에 실패했습니다. (계산된 값: ${targetPrice})` } } // 내정가 업데이트 const updateResult = await updateTargetPrice(biddingId, targetPrice, criteria, userId) if (updateResult.success) { // 내정가 산정 후 입찰 상태를 set_target_price로 변경 (received_quotation 상태에서만) await db .update(biddings) .set({ status: 'set_target_price', updatedAt: new Date() }) .where(and( eq(biddings.id, biddingId), eq(biddings.status, 'received_quotation') )) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) return { success: true, message: '내정가가 자동으로 산정되었습니다.', data: { targetPrice, criteria, preQuoteData } } } else { return updateResult } } catch (error) { console.error('Failed to calculate and update target price:', error) return { success: false, error: '내정가 자동 산정에 실패했습니다.' } } } // 내정가 수동 업데이트 (실제 저장) export async function updateTargetPrice( biddingId: number, targetPrice: number, targetPriceCalculationCriteria: string, userId: string ) { try { // 입력값 검증 if (!targetPrice || targetPrice <= 0 || isNaN(targetPrice)) { return { success: false, error: `유효하지 않은 내정가입니다: ${targetPrice}` } } console.log('Updating target price:', { biddingId, targetPrice, targetPriceCalculationCriteria }) await db .update(biddings) .set({ targetPrice: Math.round(targetPrice).toString(), targetPriceCalculationCriteria: targetPriceCalculationCriteria, updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '내정가가 성공적으로 업데이트되었습니다.' } } catch (error) { console.error('Failed to update target price:', error) return { success: false, error: '내정가 업데이트에 실패했습니다.' } } } // 본입찰용 업체 수정 (간소화 버전 - 발주비율만 UI에서 수정 가능, 견적금액/통화는 기존값 유지) export async function updateBiddingDetailVendor( biddingCompanyId: number, quotationAmount: number, // 기존값 유지용 currency: string, // 기존값 유지용 awardRatio: number, // UI에서 수정 가능 userId: string ) { try { const result = await db.update(biddingCompanies) .set({ finalQuoteAmount: quotationAmount.toString(), awardRatio: awardRatio.toString(), isWinner: awardRatio > 0, updatedAt: new Date(), }) .where(eq(biddingCompanies.id, biddingCompanyId)) .returning({ biddingId: biddingCompanies.biddingId }) // 캐시 무효화 if (result.length > 0) { const biddingId = result[0].biddingId revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) } return { success: true, message: '업체 정보가 성공적으로 수정되었습니다.', } } catch (error) { console.error('Failed to update bidding detail vendor:', error) return { success: false, error: error instanceof Error ? error.message : '업체 정보 수정에 실패했습니다.' } } } // 본입찰용 업체 추가 export async function createBiddingDetailVendor( biddingId: number, vendorId: number, userId: string ) { try { const result = await db.transaction(async (tx) => { // 1. biddingCompanies 레코드 생성 (본입찰 선정 기본값 true) const biddingCompanyResult = await tx.insert(biddingCompanies).values({ biddingId: biddingId, companyId: vendorId, invitationStatus: 'pending', isPreQuoteSelected: true, // 본입찰 등록 기본값 isWinner: false, createdAt: new Date(), updatedAt: new Date(), }).returning({ id: biddingCompanies.id }) if (biddingCompanyResult.length === 0) { throw new Error('업체 추가에 실패했습니다.') } const biddingCompanyId = biddingCompanyResult[0].id // 2. company_condition_responses 레코드 생성 (기본값) await tx.insert(companyConditionResponses).values({ biddingCompanyId: biddingCompanyId, submittedAt: new Date(), createdAt: new Date(), updatedAt: new Date(), }) return biddingCompanyId }) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '업체가 성공적으로 추가되었습니다.', data: { id: result } } } catch (error) { console.error('Failed to create bidding detail vendor:', error) return { success: false, error: error instanceof Error ? error.message : '업체 추가에 실패했습니다.' } } } // 협력업체 정보 저장 - biddingCompanies와 companyConditionResponses 테이블에 레코드 생성 export async function createQuotationVendor(input: any, userId: string) { try { const result = await db.transaction(async (tx) => { // 1. biddingCompanies에 레코드 생성 const biddingCompanyResult = await tx.insert(biddingCompanies).values({ biddingId: input.biddingId, companyId: input.vendorId, quotationAmount: input.quotationAmount, currency: input.currency, status: input.status, awardRatio: input.awardRatio, isWinner: false, contactPerson: input.contactPerson, contactEmail: input.contactEmail, contactPhone: input.contactPhone, submissionDate: new Date(), createdBy: userId, updatedBy: userId, }).returning({ id: biddingCompanies.id }) if (biddingCompanyResult.length === 0) { throw new Error('협력업체 정보 저장에 실패했습니다.') } const biddingCompanyId = biddingCompanyResult[0].id // 2. companyConditionResponses에 입찰 조건 생성 await tx.insert(companyConditionResponses).values({ biddingCompanyId: biddingCompanyId, paymentTermsResponse: input.paymentTermsResponse || '', taxConditionsResponse: input.taxConditionsResponse || '', proposedContractDeliveryDate: input.proposedContractDeliveryDate || null, priceAdjustmentResponse: input.priceAdjustmentResponse || false, incotermsResponse: input.incotermsResponse || '', proposedShippingPort: input.proposedShippingPort || '', proposedDestinationPort: input.proposedDestinationPort || '', sparePartResponse: input.sparePartResponse || '', additionalProposals: input.additionalProposals || '', isPreQuote: false, submittedAt: new Date(), createdAt: new Date(), updatedAt: new Date(), }) return biddingCompanyId }) // 캐시 무효화 revalidateTag(`bidding-${input.biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/[id]`) return { success: true, message: '협력업체 정보가 성공적으로 저장되었습니다.', data: { id: result } } } catch (error) { console.error('Failed to create quotation vendor:', error) return { success: false, error: '협력업체 정보 저장에 실패했습니다.' } } } // 협력업체 정보 업데이트 export async function updateQuotationVendor(id: number, input: any, userId: string) { try { const result = await db.transaction(async (tx) => { // 1. biddingCompanies 테이블 업데이트 const updateData: any = {} if (input.quotationAmount !== undefined) updateData.finalQuoteAmount = input.quotationAmount if (input.contactPerson !== undefined) updateData.contactPerson = input.contactPerson if (input.contactEmail !== undefined) updateData.contactEmail = input.contactEmail if (input.contactPhone !== undefined) updateData.contactPhone = input.contactPhone if (input.awardRatio !== undefined) updateData.awardRatio = input.awardRatio if (input.status !== undefined) updateData.status = input.status updateData.updatedBy = userId updateData.updatedAt = new Date() if (Object.keys(updateData).length > 0) { await tx.update(biddingCompanies) .set(updateData) .where(eq(biddingCompanies.id, id)) } // 2. companyConditionResponses 테이블 업데이트 (입찰 조건들) if (input.paymentTermsResponse !== undefined || input.taxConditionsResponse !== undefined || input.incotermsResponse !== undefined || input.proposedContractDeliveryDate !== undefined || input.proposedShippingPort !== undefined || input.proposedDestinationPort !== undefined || input.priceAdjustmentResponse !== undefined || input.sparePartResponse !== undefined || input.additionalProposals !== undefined) { const conditionsUpdateData: any = {} if (input.paymentTermsResponse !== undefined) conditionsUpdateData.paymentTermsResponse = input.paymentTermsResponse if (input.taxConditionsResponse !== undefined) conditionsUpdateData.taxConditionsResponse = input.taxConditionsResponse if (input.incotermsResponse !== undefined) conditionsUpdateData.incotermsResponse = input.incotermsResponse if (input.proposedContractDeliveryDate !== undefined) conditionsUpdateData.proposedContractDeliveryDate = input.proposedContractDeliveryDate || null if (input.proposedShippingPort !== undefined) conditionsUpdateData.proposedShippingPort = input.proposedShippingPort if (input.proposedDestinationPort !== undefined) conditionsUpdateData.proposedDestinationPort = input.proposedDestinationPort if (input.priceAdjustmentResponse !== undefined) conditionsUpdateData.priceAdjustmentResponse = input.priceAdjustmentResponse if (input.sparePartResponse !== undefined) conditionsUpdateData.sparePartResponse = input.sparePartResponse if (input.additionalProposals !== undefined) conditionsUpdateData.additionalProposals = input.additionalProposals conditionsUpdateData.updatedAt = new Date() await tx.update(companyConditionResponses) .set(conditionsUpdateData) .where(eq(companyConditionResponses.biddingCompanyId, id)) } return true }) // 캐시 무효화 (모든 입찰 관련 데이터 무효화) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/[id]`) return { success: true, message: '협력업체 정보가 성공적으로 업데이트되었습니다.', } } catch (error) { console.error('Failed to update quotation vendor:', error) return { success: false, error: '협력업체 정보 업데이트에 실패했습니다.' } } } // 협력업체 정보 삭제 export async function deleteQuotationVendor(id: number) { try { // TODO: 실제로는 견적 시스템의 테이블에서 삭제 console.log(`[TODO] 견적 시스템에서 협력업체 정보 ${id} 삭제 예정`) // 임시로 성공 응답 return { success: true, message: '협력업체 정보가 성공적으로 삭제되었습니다.' } } catch (error) { console.error('Failed to delete quotation vendor:', error) return { success: false, error: '협력업체 정보 삭제에 실패했습니다.' } } } // 낙찰 처리 export async function selectWinner(biddingId: number, vendorId: number, awardRatio: number, userId: string) { try { // 트랜잭션으로 처리 await db.transaction(async (tx) => { // 기존 낙찰자 초기화 await tx .update(biddingCompanies) .set({ isWinner: false, updatedAt: new Date() }) .where(eq(biddingCompanies.biddingId, biddingId)) // 새로운 낙찰자 설정 const biddingCompany = await tx .select() .from(biddingCompanies) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.companyId, vendorId) )) .limit(1) if (biddingCompany.length > 0) { await tx .update(biddingCompanies) .set({ isWinner: true, updatedAt: new Date() }) .where(eq(biddingCompanies.id, biddingCompany[0].id)) } // biddings 테이블의 상태 업데이트 await tx .update(biddings) .set({ status: 'vendor_selected', finalBidPrice: undefined, // TODO: 낙찰가 설정 로직 추가 updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) }) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '낙찰 처리가 완료되었습니다.' } } catch (error) { console.error('Failed to select winner:', error) return { success: false, error: '낙찰 처리에 실패했습니다.' } } } // 유찰 처리 export async function markAsDisposal(biddingId: number, userId: string) { try { // 입찰 정보 조회 const biddingInfo = await db .select() .from(biddings) .where(eq(biddings.id, biddingId)) .limit(1) if (biddingInfo.length === 0) { return { success: false, error: '입찰 정보를 찾을 수 없습니다.' } } const bidding = biddingInfo[0] // 입찰 참여 업체들 조회 const participantCompanies = await db .select({ companyId: biddingCompanies.companyId, companyName: vendors.vendorName, contactEmail: vendors.email }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isBiddingParticipated, true) )) // 입찰 상태를 유찰로 변경 await db .update(biddings) .set({ status: 'bidding_disposal', updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) // 참여 업체들에게 유찰 안내 메일 발송 for (const company of participantCompanies) { if (company.contactEmail) { try { await sendEmail({ to: company.contactEmail, template: 'bidding-disposal', context: { companyName: company.companyName, biddingNumber: bidding.biddingNumber, title: bidding.title, projectName: bidding.projectName, itemName: bidding.itemName, biddingType: bidding.biddingType, processedDate: new Date().toLocaleDateString('ko-KR'), managerName: bidding.managerName, managerEmail: bidding.managerEmail, managerPhone: bidding.managerPhone, language: 'ko' } }) } catch (emailError) { console.error(`Failed to send disposal email to ${company.contactEmail}:`, emailError) } } } // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: `유찰 처리가 완료되었습니다. ${participantCompanies.length}개 업체에 안내 메일을 발송했습니다.` } } catch (error) { console.error('Failed to mark as disposal:', error) return { success: false, error: '유찰 처리에 실패했습니다.' } } } // 입찰 등록 (사전견적에서 선정된 업체들에게 본입찰 초대 발송) export async function registerBidding(biddingId: number, userId: string) { try { // 사전견적에서 선정된 업체들 조회 const selectedCompanies = await db .select({ companyId: biddingCompanies.companyId, companyName: vendors.vendorName, contactEmail: vendors.email }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isPreQuoteSelected, true) )) // 입찰 정보 조회 const biddingInfo = await db .select() .from(biddings) .where(eq(biddings.id, biddingId)) .limit(1) if (biddingInfo.length === 0) { return { success: false, error: '입찰 정보를 찾을 수 없습니다.' } } const bidding = biddingInfo[0] await db.transaction(async (tx) => { // 1. 입찰 상태를 오픈으로 변경 await tx .update(biddings) .set({ status: 'bidding_opened', updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) // 2. 선정된 업체들의 입찰 초대 여부를 true로 변경하고 초대 상태 업데이트 for (const company of selectedCompanies) { await tx .update(biddingCompanies) .set({ isBiddingInvited: true, invitationStatus: 'sent', updatedAt: new Date() }) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.companyId, company.companyId) )) } }) // 3. 선정된 업체들에게 본입찰 초대 메일 발송 for (const company of selectedCompanies) { if (company.contactEmail) { try { await sendEmail({ to: company.contactEmail, template: 'bidding-invitation', // 새로운 본입찰 초대 템플릿 필요 context: { companyName: company.companyName, biddingNumber: bidding.biddingNumber, title: bidding.title, projectName: bidding.projectName, itemName: bidding.itemName, biddingType: bidding.biddingType, submissionStartDate: bidding.submissionStartDate, submissionEndDate: bidding.submissionEndDate, biddingUrl: `${process.env.NEXT_PUBLIC_BASE_URL}/partners/bid/${biddingId}`, managerName: bidding.managerName, managerEmail: bidding.managerEmail, managerPhone: bidding.managerPhone, language: 'ko' } }) } catch (emailError) { console.error(`Failed to send bidding invitation email to ${company.contactEmail}:`, emailError) } } } // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: `입찰이 성공적으로 등록되었습니다. ${selectedCompanies.length}개 업체에 초대 메일을 발송했습니다.` } } catch (error) { console.error('Failed to register bidding:', error) return { success: false, error: '입찰 등록에 실패했습니다.' } } } // 재입찰 생성 (기존 입찰의 revision 업데이트 + 메일 발송) export async function createRebidding(biddingId: number, userId: string) { try { // 기존 입찰 정보 조회 const bidding = await db .select() .from(biddings) .where(eq(biddings.id, biddingId)) .limit(1) if (bidding.length === 0) { return { success: false, error: '입찰을 찾을 수 없습니다.' } } const originalBidding = bidding[0] // 기존 입찰 참여 업체들 조회 const participantCompanies = await db .select({ companyId: biddingCompanies.companyId, companyName: vendors.vendorName, contactEmail: vendors.email }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isBiddingParticipated, true) )) // 기존 입찰의 revision 증가 및 상태 변경 const updatedBidding = await db .update(biddings) .set({ revision: (originalBidding.revision || 0) + 1, status: 'bidding_opened', // 재입찰 시 다시 오픈 상태로 updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) .returning({ id: biddings.id, biddingNumber: biddings.biddingNumber, revision: biddings.revision }) if (updatedBidding.length === 0) { return { success: false, error: '재입찰 업데이트에 실패했습니다.' } } // 참여 업체들의 상태를 대기로 변경 await db .update(biddingCompanies) .set({ isBiddingParticipated: null, // 대기 상태로 변경 invitationStatus: 'sent', updatedAt: new Date() }) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isBiddingParticipated, true) )) // 재입찰 안내 메일 발송 for (const company of participantCompanies) { if (company.contactEmail) { try { await sendEmail({ to: company.contactEmail, template: 'rebidding-invitation', context: { companyName: company.companyName, biddingNumber: updatedBidding[0].biddingNumber, title: originalBidding.title, projectName: originalBidding.projectName, itemName: originalBidding.itemName, biddingType: originalBidding.biddingType, revision: updatedBidding[0].revision || 1, submissionStartDate: originalBidding.submissionStartDate, submissionEndDate: originalBidding.submissionEndDate, biddingUrl: `${process.env.NEXT_PUBLIC_BASE_URL}/partners/bid/${biddingId}`, managerName: originalBidding.managerName, managerEmail: originalBidding.managerEmail, managerPhone: originalBidding.managerPhone, language: 'ko' } }) } catch (emailError) { console.error(`Failed to send rebidding email to ${company.contactEmail}:`, emailError) } } } // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath('/evcp/bid') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: `재입찰이 성공적으로 처리되었습니다. ${participantCompanies.length}개 업체에 안내 메일을 발송했습니다.` } } catch (error) { console.error('Failed to create rebidding:', error) return { success: false, error: '재입찰 처리에 실패했습니다.' } } } // 업체 선정 사유 업데이트 export async function updateVendorSelectionReason(biddingId: number, selectedCompanyId: number, selectionReason: string, userId: string) { try { // vendorSelectionResults 테이블에 삽입 또는 업데이트 await db .insert(vendorSelectionResults) .values({ biddingId, selectedCompanyId, selectionReason, selectedBy: userId, selectedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }) .onConflictDoUpdate({ target: [vendorSelectionResults.biddingId], set: { selectedCompanyId, selectionReason, selectedBy: userId, selectedAt: new Date(), updatedAt: new Date() } }) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '업체 선정 사유가 성공적으로 업데이트되었습니다.' } } catch (error) { console.error('Failed to update vendor selection reason:', error) return { success: false, error: '업체 선정 사유 업데이트에 실패했습니다.' } } } // 낙찰용 문서 업로드 export async function uploadAwardDocument(biddingId: number, file: File, userId: string) { try { const saveResult = await saveFile({ file, directory: `biddings/${biddingId}/award`, userId: userId }) if (saveResult.success && saveResult.filePath) { // biddingDocuments 테이블에 저장 const [document] = await db.insert(biddingDocuments).values({ biddingId, fileName: saveResult.fileName || file.name, originalFileName: file.name, filePath: saveResult.filePath, fileSize: file.size, documentType: 'other', title: '낙찰 관련 문서', description: '낙찰 관련 첨부파일', uploadedBy: userId, uploadedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }).returning() return { success: true, message: '파일이 성공적으로 업로드되었습니다.', document } } else { return { success: false, error: saveResult.error || '파일 저장에 실패했습니다.' } } } catch (error) { console.error('Failed to upload award document:', error) return { success: false, error: '파일 업로드에 실패했습니다.' } } } // 낙찰용 문서 목록 조회 export async function getAwardDocuments(biddingId: number) { try { const documents = await db .select() .from(biddingDocuments) .where(and( eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.documentType, 'other') )) .orderBy(desc(biddingDocuments.uploadedAt)) return documents } catch (error) { console.error('Failed to get award documents:', error) return [] } } // 낙찰용 문서 다운로드 export async function getAwardDocumentForDownload(documentId: number, biddingId: number) { try { const documents = await db .select() .from(biddingDocuments) .where(and( eq(biddingDocuments.id, documentId), eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.documentType, 'other') )) .limit(1) if (documents.length === 0) { return { success: false, error: '문서를 찾을 수 없습니다.' } } return { success: true, document: documents[0] } } catch (error) { console.error('Failed to get award document for download:', error) return { success: false, error: '문서 다운로드 준비에 실패했습니다.' } } } // 낙찰용 문서 삭제 export async function deleteAwardDocument(documentId: number, biddingId: number, userId: string) { try { // 문서 정보 조회 const documents = await db .select() .from(biddingDocuments) .where(and( eq(biddingDocuments.id, documentId), eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.documentType, 'other'), eq(biddingDocuments.uploadedBy, userId) )) .limit(1) if (documents.length === 0) { return { success: false, error: '삭제할 수 있는 문서가 없습니다.' } } // DB에서 삭제 await db .delete(biddingDocuments) .where(eq(biddingDocuments.id, documentId)) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) return { success: true, message: '문서가 성공적으로 삭제되었습니다.' } } catch (error) { console.error('Failed to delete award document:', error) return { success: false, error: '문서 삭제에 실패했습니다.' } } } // 낙찰 처리 (발주비율과 함께) export async function awardBidding(biddingId: number, selectionReason: string, userId: string) { try { // 낙찰된 업체들 조회 (isWinner가 true인 업체들) const awardedCompanies = await db .select({ companyId: biddingCompanies.companyId, finalQuoteAmount: biddingCompanies.finalQuoteAmount, awardRatio: biddingCompanies.awardRatio }) .from(biddingCompanies) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isWinner, true) )) if (awardedCompanies.length === 0) { return { success: false, error: '낙찰된 업체가 없습니다. 먼저 발주비율을 산정해주세요.' } } // 최종입찰가 계산 (낙찰된 업체의 견적금액 * 발주비율의 합) let finalBidPrice = 0 for (const company of awardedCompanies) { const quoteAmount = parseFloat(company.finalQuoteAmount?.toString() || '0') const ratio = parseFloat(company.awardRatio?.toString() || '0') / 100 finalBidPrice += quoteAmount * ratio } await db.transaction(async (tx) => { // 1. 입찰 상태를 낙찰로 변경하고 최종입찰가 업데이트 await tx .update(biddings) .set({ status: 'vendor_selected', finalBidPrice: finalBidPrice.toString(), updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) // 2. 선정 사유 저장 (첫 번째 낙찰 업체 기준으로 저장) const firstAwardedCompany = awardedCompanies[0] // 기존 선정 결과 확인 const existingResult = await tx .select() .from(vendorSelectionResults) .where(eq(vendorSelectionResults.biddingId, biddingId)) .limit(1) if (existingResult.length > 0) { // 업데이트 await tx .update(vendorSelectionResults) .set({ selectedCompanyId: firstAwardedCompany.companyId, selectionReason, selectedBy: userId, selectedAt: new Date(), updatedAt: new Date() }) .where(eq(vendorSelectionResults.biddingId, biddingId)) } else { // 삽입 await tx .insert(vendorSelectionResults) .values({ biddingId, selectedCompanyId: firstAwardedCompany.companyId, selectionReason, selectedBy: userId, selectedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }) } }) // 캐시 무효화 revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: `낙찰 처리가 완료되었습니다. 최종입찰가: ${finalBidPrice.toLocaleString()}원` } } catch (error) { console.error('Failed to award bidding:', error) return { success: false, error: '낙찰 처리에 실패했습니다.' } } } // 낙찰된 업체 정보 조회 export async function getAwardedCompanies(biddingId: number) { try { const awardedCompanies = await db .select({ companyId: biddingCompanies.companyId, companyName: vendors.vendorName, finalQuoteAmount: biddingCompanies.finalQuoteAmount, awardRatio: biddingCompanies.awardRatio }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isWinner, true) )) return awardedCompanies.map(company => ({ companyId: company.companyId, companyName: company.companyName, finalQuoteAmount: parseFloat(company.finalQuoteAmount?.toString() || '0'), awardRatio: parseFloat(company.awardRatio?.toString() || '0') })) } catch (error) { console.error('Failed to get awarded companies:', error) return [] } } // PR 품목 정보 업데이트 export async function updatePrItem(prItemId: number, input: Partial, userId: string) { try { await db .update(prItemsForBidding) .set({ ...input, updatedAt: new Date() }) .where(eq(prItemsForBidding.id, prItemId)) // 캐시 무효화 if (input.biddingId) { revalidateTag(`bidding-${input.biddingId}`) revalidateTag('pr-items') revalidatePath(`/evcp/bid/${input.biddingId}`) } return { success: true, message: '품목 정보가 성공적으로 업데이트되었습니다.' } } catch (error) { console.error('Failed to update PR item:', error) return { success: false, error: '품목 정보 업데이트에 실패했습니다.' } } } // 입찰 참여여부 업데이트 export async function updateBiddingParticipation( biddingCompanyId: number, participated: boolean, userId: string ) { try { const result = await db.update(biddingCompanies) .set({ isBiddingParticipated: participated, updatedAt: new Date(), }) .where(eq(biddingCompanies.id, biddingCompanyId)) .returning({ biddingId: biddingCompanies.biddingId }) // 캐시 무효화 if (result.length > 0) { const biddingId = result[0].biddingId revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidatePath(`/evcp/bid/${biddingId}`) } return { success: true, message: `입찰 참여상태가 ${participated ? '응찰' : '미응찰'}로 업데이트되었습니다.`, } } catch (error) { console.error('Failed to update bidding participation:', error) return { success: false, error: error instanceof Error ? error.message : '입찰 참여상태 업데이트에 실패했습니다.' } } } // ================================================= // 품목별 견적 관련 함수들 (본입찰용) // ================================================= // 품목별 견적 임시 저장 (본입찰용) export async function saveBiddingDraft( biddingCompanyId: number, prItemQuotations: Array<{ prItemId: number bidUnitPrice: number bidAmount: number proposedDeliveryDate?: string technicalSpecification?: string }>, userId: string ) { try { let totalAmount = 0 await db.transaction(async (tx) => { // 품목별 견적 Upsert 방식으로 저장 for (const item of prItemQuotations) { // 기존 데이터 확인 const existingItem = await tx .select() .from(companyPrItemBids) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.prItemId, item.prItemId), ) ) .limit(1) const itemData = { bidUnitPrice: item.bidUnitPrice.toString(), bidAmount: item.bidAmount.toString(), proposedDeliveryDate: item.proposedDeliveryDate, technicalSpecification: item.technicalSpecification, currency: 'KRW', updatedAt: new Date() } if (existingItem.length > 0) { // 업데이트 await tx .update(companyPrItemBids) .set(itemData) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.prItemId, item.prItemId), eq(companyPrItemBids.isPreQuote, false) ) ) } else { // 새로 생성 await tx.insert(companyPrItemBids) .values({ biddingCompanyId, prItemId: item.prItemId, isPreQuote: false, // 본입찰 데이터 createdAt: new Date(), ...itemData }) } totalAmount += item.bidAmount } }) // 캐시 무효화 revalidateTag(`bidding-${biddingCompanyId}`) revalidateTag('quotation-vendors') return { success: true, message: '품목별 견적이 임시 저장되었습니다.', totalAmount } } catch (error) { console.error('Failed to save bidding draft:', error) return { success: false, error: error instanceof Error ? error.message : '임시 저장에 실패했습니다.' } } } // ================================================= // 협력업체 페이지용 함수들 (Partners) // ================================================= // 협력업체용 입찰 참여여부 업데이트 export async function updatePartnerBiddingParticipation( biddingCompanyId: number, participated: boolean, userId: string ) { try { const result = await db.update(biddingCompanies) .set({ isBiddingParticipated: participated, updatedAt: new Date(), }) .where(eq(biddingCompanies.id, biddingCompanyId)) .returning({ biddingId: biddingCompanies.biddingId }) // 캐시 무효화 if (result.length > 0) { const biddingId = result[0].biddingId revalidateTag(`bidding-${biddingId}`) revalidateTag('quotation-vendors') revalidateTag(`partners-bidding-${biddingId}`) revalidatePath(`/partners/bid/${biddingId}`) } return { success: true, message: `입찰 참여상태가 ${participated ? '응찰' : '미응찰'}로 업데이트되었습니다.`, } } catch (error) { console.error('Failed to update partner bidding participation:', error) return { success: false, error: error instanceof Error ? error.message : '입찰 참여상태 업데이트에 실패했습니다.' } } } // 협력업체용 입찰 목록 조회 (bidding_companies 기준) export interface PartnersBiddingListItem { // bidding_companies 정보 id: number biddingCompanyId: number invitationStatus: string respondedAt: string | null finalQuoteAmount: number | null finalQuoteSubmittedAt: string | null isWinner: boolean | null isAttendingMeeting: boolean | null isPreQuoteSelected: boolean | null isPreQuoteParticipated: boolean | null preQuoteDeadline: Date | null isBiddingInvited: boolean | null notes: string | null createdAt: Date updatedAt: Date // updatedBy: string | null hasSpecificationMeeting: boolean | null // biddings 정보 biddingId: number biddingNumber: string revision: number projectName: string itemName: string title: string contractType: string biddingType: string contractPeriod: string | null submissionStartDate: Date | null submissionEndDate: Date | null status: string managerName: string | null managerEmail: string | null managerPhone: string | null currency: string budget: number | null // 계산된 필드 responseDeadline: Date | null // 참여회신 마감일 (submissionStartDate 전 3일) submissionDate: Date | null // 입찰제출일 (submissionEndDate) } export async function getBiddingListForPartners(companyId: number): Promise { try { const result = await db .select({ // bidding_companies 정보 id: biddingCompanies.id, biddingCompanyId: biddingCompanies.id, // 동일 invitationStatus: biddingCompanies.invitationStatus, respondedAt: biddingCompanies.respondedAt, finalQuoteAmount: biddingCompanies.finalQuoteAmount, finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, isAttendingMeeting: biddingCompanies.isAttendingMeeting, isPreQuoteSelected: biddingCompanies.isPreQuoteSelected, isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated, preQuoteDeadline: biddingCompanies.preQuoteDeadline, isBiddingInvited: biddingCompanies.isBiddingInvited, notes: biddingCompanies.notes, createdAt: biddingCompanies.createdAt, updatedAt: biddingCompanies.updatedAt, // updatedBy: biddingCompanies.updatedBy, // 이 필드가 존재하지 않음 // biddings 정보 biddingId: biddings.id, biddingNumber: biddings.biddingNumber, revision: biddings.revision, projectName: biddings.projectName, itemName: biddings.itemName, title: biddings.title, contractType: biddings.contractType, biddingType: biddings.biddingType, contractPeriod: biddings.contractPeriod, submissionStartDate: biddings.submissionStartDate, submissionEndDate: biddings.submissionEndDate, status: biddings.status, managerName: biddings.managerName, managerEmail: biddings.managerEmail, managerPhone: biddings.managerPhone, currency: biddings.currency, budget: biddings.budget, hasSpecificationMeeting: biddings.hasSpecificationMeeting, }) .from(biddingCompanies) .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id)) .where(and( eq(biddingCompanies.companyId, companyId), ne(biddingCompanies.invitationStatus, 'pending') // 초대 대기 상태 제외 )) .orderBy(desc(biddingCompanies.createdAt)) console.log(result, "result") // 계산된 필드 추가 const resultWithCalculatedFields = result.map(item => ({ ...item, respondedAt: item.respondedAt ? (item.respondedAt instanceof Date ? item.respondedAt.toISOString() : item.respondedAt.toString()) : null, finalQuoteAmount: item.finalQuoteAmount ? Number(item.finalQuoteAmount) : null, // string을 number로 변환 finalQuoteSubmittedAt: item.finalQuoteSubmittedAt ? (item.finalQuoteSubmittedAt instanceof Date ? item.finalQuoteSubmittedAt.toISOString() : item.finalQuoteSubmittedAt.toString()) : null, responseDeadline: item.submissionStartDate ? new Date(item.submissionStartDate.getTime() - 3 * 24 * 60 * 60 * 1000) // 3일 전 : null, submissionDate: item.submissionEndDate, })) return resultWithCalculatedFields } catch (error) { console.error('Failed to get bidding list for partners:', error) return [] } } // 협력업체용 입찰 상세 정보 조회 export async function getBiddingDetailsForPartners(biddingId: number, companyId: number) { try { const result = await db .select({ // 입찰 기본 정보 id: biddings.id, biddingId: biddings.id, // partners-bidding-detail.tsx에서 필요한 필드 biddingNumber: biddings.biddingNumber, revision: biddings.revision, projectName: biddings.projectName, itemName: biddings.itemName, title: biddings.title, description: biddings.description, content: biddings.content, // 계약 정보 contractType: biddings.contractType, biddingType: biddings.biddingType, awardCount: biddings.awardCount, contractPeriod: biddings.contractPeriod, // 일정 정보 preQuoteDate: biddings.preQuoteDate, biddingRegistrationDate: biddings.biddingRegistrationDate, submissionStartDate: biddings.submissionStartDate, submissionEndDate: biddings.submissionEndDate, evaluationDate: biddings.evaluationDate, // 가격 정보 currency: biddings.currency, budget: biddings.budget, targetPrice: biddings.targetPrice, // 상태 및 담당자 status: biddings.status, managerName: biddings.managerName, managerEmail: biddings.managerEmail, managerPhone: biddings.managerPhone, // 협력업체 특정 정보 biddingCompanyId: biddingCompanies.id, invitationStatus: biddingCompanies.invitationStatus, finalQuoteAmount: biddingCompanies.finalQuoteAmount, finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, isAttendingMeeting: biddingCompanies.isAttendingMeeting, isPreQuoteSelected: biddingCompanies.isPreQuoteSelected, isBiddingParticipated: biddingCompanies.isBiddingParticipated, isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated, hasSpecificationMeeting: biddings.hasSpecificationMeeting, // 응답한 조건들 (company_condition_responses) - 제시된 조건과 응답 모두 여기서 관리 paymentTermsResponse: companyConditionResponses.paymentTermsResponse, taxConditionsResponse: companyConditionResponses.taxConditionsResponse, incotermsResponse: companyConditionResponses.incotermsResponse, proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate, proposedShippingPort: companyConditionResponses.proposedShippingPort, proposedDestinationPort: companyConditionResponses.proposedDestinationPort, priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse, isInitialResponse: companyConditionResponses.isInitialResponse, sparePartResponse: companyConditionResponses.sparePartResponse, additionalProposals: companyConditionResponses.additionalProposals, responseSubmittedAt: companyConditionResponses.submittedAt, }) .from(biddings) .innerJoin(biddingCompanies, eq(biddings.id, biddingCompanies.biddingId)) .leftJoin(companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId)) .where(and( eq(biddings.id, biddingId), eq(biddingCompanies.companyId, companyId) )) .limit(1) return result[0] || null } catch (error) { console.error('Failed to get bidding details for partners:', error) return null } } // 협력업체 응찰 제출 export async function submitPartnerResponse( biddingCompanyId: number, response: { paymentTermsResponse?: string taxConditionsResponse?: string incotermsResponse?: string proposedContractDeliveryDate?: string proposedShippingPort?: string proposedDestinationPort?: string priceAdjustmentResponse?: boolean isInitialResponse?: boolean sparePartResponse?: string additionalProposals?: string finalQuoteAmount?: number prItemQuotations?: Array<{ prItemId: number bidUnitPrice: number bidAmount: number proposedDeliveryDate?: string technicalSpecification?: string }> priceAdjustmentForm?: { itemName?: string adjustmentReflectionPoint?: string majorApplicableRawMaterial?: string adjustmentFormula?: string rawMaterialPriceIndex?: string referenceDate?: string comparisonDate?: string adjustmentRatio?: number notes?: string adjustmentConditions?: string majorNonApplicableRawMaterial?: string adjustmentPeriod?: string contractorWriter?: string adjustmentDate?: string nonApplicableReason?: string } }, userId: string ) { try { const result = await db.transaction(async (tx) => { // 0. 품목별 견적 정보 최종 저장 (본입찰 제출) - Upsert 방식 if (response.prItemQuotations && response.prItemQuotations.length > 0) { for (const item of response.prItemQuotations) { // 기존 데이터 확인 const existingItem = await tx .select() .from(companyPrItemBids) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.prItemId, item.prItemId), ) ) .limit(1) const itemData = { bidUnitPrice: item.bidUnitPrice.toString(), bidAmount: item.bidAmount.toString(), proposedDeliveryDate: item.proposedDeliveryDate || null, technicalSpecification: item.technicalSpecification || null, currency: 'KRW', submittedAt: new Date(), updatedAt: new Date() } if (existingItem.length > 0) { // 업데이트 await tx .update(companyPrItemBids) .set(itemData) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.prItemId, item.prItemId), eq(companyPrItemBids.isPreQuote, false) ) ) } else { // 새로 생성 await tx.insert(companyPrItemBids) .values({ biddingCompanyId, prItemId: item.prItemId, isPreQuote: false, // 본입찰 데이터 createdAt: new Date(), ...itemData }) } } } // 3. 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우) // if (response.priceAdjustmentResponse && response.priceAdjustmentForm) { // const priceAdjustmentData = { // companyConditionResponsesId: companyConditionResponseId, // itemName: response.priceAdjustmentForm.itemName, // adjustmentReflectionPoint: response.priceAdjustmentForm.adjustmentReflectionPoint, // majorApplicableRawMaterial: response.priceAdjustmentForm.majorApplicableRawMaterial, // adjustmentFormula: response.priceAdjustmentForm.adjustmentFormula, // rawMaterialPriceIndex: response.priceAdjustmentForm.rawMaterialPriceIndex, // referenceDate: response.priceAdjustmentForm.referenceDate || null, // comparisonDate: response.priceAdjustmentForm.comparisonDate || null, // adjustmentRatio: response.priceAdjustmentForm.adjustmentRatio, // notes: response.priceAdjustmentForm.notes, // adjustmentConditions: response.priceAdjustmentForm.adjustmentConditions, // majorNonApplicableRawMaterial: response.priceAdjustmentForm.majorNonApplicableRawMaterial, // adjustmentPeriod: response.priceAdjustmentForm.adjustmentPeriod, // contractorWriter: response.priceAdjustmentForm.contractorWriter, // adjustmentDate: response.priceAdjustmentForm.adjustmentDate || null, // nonApplicableReason: response.priceAdjustmentForm.nonApplicableReason, // } // // 기존 연동제 정보가 있는지 확인 // const existingPriceAdjustment = await tx // .select() // .from(priceAdjustmentForms) // .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId)) // .limit(1) // if (existingPriceAdjustment.length > 0) { // // 업데이트 // await tx // .update(priceAdjustmentForms) // .set(priceAdjustmentData) // .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId)) // } else { // // 새로 생성 // await tx.insert(priceAdjustmentForms).values(priceAdjustmentData) // } // } // 2. biddingCompanies 테이블에 견적 금액과 상태 업데이트 const companyUpdateData: any = { respondedAt: new Date(), updatedAt: new Date(), // updatedBy: userId, // 이 필드가 존재하지 않음 } if (response.finalQuoteAmount !== undefined) { companyUpdateData.finalQuoteAmount = response.finalQuoteAmount companyUpdateData.finalQuoteSubmittedAt = new Date() companyUpdateData.invitationStatus = 'submitted' } await tx .update(biddingCompanies) .set(companyUpdateData) .where(eq(biddingCompanies.id, biddingCompanyId)) // biddingId 조회 const biddingCompanyInfo = await tx .select({ biddingId: biddingCompanies.biddingId }) .from(biddingCompanies) .where(eq(biddingCompanies.id, biddingCompanyId)) .limit(1) const biddingId = biddingCompanyInfo[0]?.biddingId // 응찰 제출 시 입찰 상태를 평가중으로 변경 (bidding_opened 상태에서만) if (biddingId && response.finalQuoteAmount !== undefined) { await tx .update(biddings) .set({ status: 'evaluation_of_bidding', updatedAt: new Date() }) .where(and( eq(biddings.id, biddingId), eq(biddings.status, 'bidding_opened') )) } return biddingId }) // 캐시 무효화 if (result) { revalidateTag(`bidding-${result}`) revalidateTag('quotation-vendors') revalidateTag('quotation-details') } revalidatePath('/partners/bid/[id]') return { success: true, message: '응찰이 성공적으로 제출되었습니다.', } } catch (error) { console.error('Failed to submit partner response:', error) return { success: false, error: '응찰 제출에 실패했습니다.' } } } // 사양설명회 정보 조회 (협력업체용) export async function getSpecificationMeetingForPartners(biddingId: number) { try { // specification_meetings 테이블에서 사양설명회 정보 조회 const specMeeting = await db .select({ id: specificationMeetings.id, meetingDate: specificationMeetings.meetingDate, meetingTime: specificationMeetings.meetingTime, location: specificationMeetings.location, address: specificationMeetings.address, contactPerson: specificationMeetings.contactPerson, contactPhone: specificationMeetings.contactPhone, contactEmail: specificationMeetings.contactEmail, agenda: specificationMeetings.agenda, materials: specificationMeetings.materials, notes: specificationMeetings.notes, isRequired: specificationMeetings.isRequired, }) .from(specificationMeetings) .where(eq(specificationMeetings.biddingId, biddingId)) .limit(1) // bidding_documents에서 사양설명회 관련 문서 조회 const documents = await db .select({ id: biddingDocuments.id, fileName: biddingDocuments.fileName, originalFileName: biddingDocuments.originalFileName, filePath: biddingDocuments.filePath, fileSize: biddingDocuments.fileSize, title: biddingDocuments.title, }) .from(biddingDocuments) .where(and( eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.documentType, 'specification_meeting') )) // 기본 입찰 정보도 가져오기 (제목, 입찰번호 등) const bidding = await db .select({ id: biddings.id, title: biddings.title, biddingNumber: biddings.biddingNumber, }) .from(biddings) .where(eq(biddings.id, biddingId)) .limit(1) if (bidding.length === 0) { return { success: false, error: '입찰 정보를 찾을 수 없습니다.' } } // 사양설명회 정보가 없는 경우 if (specMeeting.length === 0) { return { success: true, data: { ...bidding[0], documents, meetingDate: null, meetingTime: null, location: null, address: null, contactPerson: null, contactPhone: null, contactEmail: null, agenda: null, materials: null, notes: null, isRequired: false, } } } return { success: true, data: { ...bidding[0], documents, meetingDate: specMeeting[0].meetingDate ? (specMeeting[0].meetingDate instanceof Date ? specMeeting[0].meetingDate.toISOString().split('T')[0] : specMeeting[0].meetingDate.toString().split('T')[0]) : null, meetingTime: specMeeting[0].meetingTime, location: specMeeting[0].location, address: specMeeting[0].address, contactPerson: specMeeting[0].contactPerson, contactPhone: specMeeting[0].contactPhone, contactEmail: specMeeting[0].contactEmail, agenda: specMeeting[0].agenda, materials: specMeeting[0].materials, notes: specMeeting[0].notes, isRequired: specMeeting[0].isRequired, } } } catch (error) { console.error('Failed to get specification meeting info:', error) return { success: false, error: '사양설명회 정보 조회에 실패했습니다.' } } } // 사양설명회 참석 여부 업데이트 (상세 정보 포함) export async function updatePartnerAttendance( biddingCompanyId: number, attendanceData: { isAttending: boolean attendeeCount?: number representativeName?: string representativePhone?: string }, userId: string ) { try { const result = await db.transaction(async (tx) => { // biddingCompanies 테이블 업데이트 (참석여부만 저장) await tx .update(biddingCompanies) .set({ isAttendingMeeting: attendanceData.isAttending, updatedAt: new Date(), }) .where(eq(biddingCompanies.id, biddingCompanyId)) // 참석하는 경우, 사양설명회 담당자에게 이메일 발송을 위한 정보 반환 if (attendanceData.isAttending) { const biddingInfo = await tx .select({ biddingId: biddingCompanies.biddingId, companyId: biddingCompanies.companyId, managerEmail: biddings.managerEmail, managerName: biddings.managerName, title: biddings.title, biddingNumber: biddings.biddingNumber, }) .from(biddingCompanies) .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id)) .where(eq(biddingCompanies.id, biddingCompanyId)) .limit(1) if (biddingInfo.length > 0) { // 협력업체 정보 조회 const companyInfo = await tx .select({ vendorName: vendors.vendorName, }) .from(vendors) .where(eq(vendors.id, biddingInfo[0].companyId)) .limit(1) const companyName = companyInfo.length > 0 ? companyInfo[0].vendorName : '알 수 없음' // 메일 발송 (템플릿 사용) try { const { sendEmail } = await import('@/lib/mail/sendEmail') await sendEmail({ to: biddingInfo[0].managerEmail, template: 'specification-meeting-attendance', context: { biddingNumber: biddingInfo[0].biddingNumber, title: biddingInfo[0].title, companyName: companyName, attendeeCount: attendanceData.attendeeCount, representativeName: attendanceData.representativeName, representativePhone: attendanceData.representativePhone, managerName: biddingInfo[0].managerName, managerEmail: biddingInfo[0].managerEmail, currentYear: new Date().getFullYear(), language: 'ko' } }) console.log(`사양설명회 참석 알림 메일 발송 완료: ${biddingInfo[0].managerEmail}`) } catch (emailError) { console.error('메일 발송 실패:', emailError) // 메일 발송 실패해도 참석 여부 업데이트는 성공으로 처리 } // 캐시 무효화 revalidateTag(`bidding-${biddingInfo[0].biddingId}`) revalidateTag('quotation-vendors') return { ...biddingInfo[0], companyName, attendeeCount: attendanceData.attendeeCount, representativeName: attendanceData.representativeName, representativePhone: attendanceData.representativePhone } } } return null }) revalidatePath('/partners/bid/[id]') return { success: true, message: `사양설명회 ${attendanceData.isAttending ? '참석' : '불참'}으로 설정되었습니다.`, data: result } } catch (error) { console.error('Failed to update partner attendance:', error) return { success: false, error: '참석 여부 업데이트에 실패했습니다.' } } } // 연동제 정보 조회 export async function getPriceAdjustmentForm(companyConditionResponseId: number) { try { const priceAdjustment = await db .select() .from(priceAdjustmentForms) .where(eq(priceAdjustmentForms.companyConditionResponsesId, companyConditionResponseId)) .limit(1) return priceAdjustment[0] || null } catch (error) { console.error('Failed to get price adjustment form:', error) return null } } // 입찰업체 ID로 연동제 정보 조회 export async function getPriceAdjustmentFormByBiddingCompanyId(biddingCompanyId: number) { try { const result = await db .select({ priceAdjustmentForm: priceAdjustmentForms, companyConditionResponse: companyConditionResponses, }) .from(companyConditionResponses) .leftJoin(priceAdjustmentForms, eq(companyConditionResponses.id, priceAdjustmentForms.companyConditionResponsesId)) .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) .limit(1) return result[0]?.priceAdjustmentForm || null } catch (error) { console.error('Failed to get price adjustment form by bidding company id:', error) return null } }