'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 } from 'next/cache' // 데이터 조회 함수들 export interface BiddingDetailData { bidding: Awaited> quotationDetails: QuotationDetails | null quotationVendors: QuotationVendor[] biddingCompanies: Awaited> prItems: Awaited> } // getBiddingById 함수 임포트 (기존 함수 재사용) import { getBiddingById, getPRDetailsAction } from '@/lib/bidding/service' // Promise.all을 사용하여 모든 데이터를 병렬로 조회 export async function getBiddingDetailData(biddingId: number): Promise { const [ bidding, quotationDetails, quotationVendors, biddingCompanies, prItems ] = await Promise.all([ getBiddingById(biddingId), getQuotationDetails(biddingId), getQuotationVendors(biddingId), getBiddingCompaniesData(biddingId), getPRItemsForBidding(biddingId) ]) return { bidding, quotationDetails, quotationVendors, biddingCompanies, prItems } } 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 // 낙찰여부 awardRatio: number // 발주비율 status: 'pending' | 'submitted' | 'selected' | 'rejected' // companyConditionResponses에서 가져온 입찰 조건들 paymentTermsResponse?: string // 지급조건 응답 taxConditionsResponse?: string // 세금조건 응답 incotermsResponse?: string // 운송조건 응답 proposedContractDeliveryDate?: string // 제안 계약납기일 proposedShippingPort?: string // 제안 선적지 proposedDestinationPort?: string // 제안 도착지 priceAdjustmentResponse?: boolean // 연동제 적용 응답 sparePartResponse?: string // 스페어파트 응답 additionalProposals?: string // 추가 제안사항 documents: Array<{ id: number fileName: string originalFileName: string filePath: string uploadedAt: string }> } // 견적 시스템에서 내정가 및 관련 정보를 가져오는 함수 export async function getQuotationDetails(biddingId: number): Promise { 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 } } // 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(eq(biddingCompanies.biddingId, biddingId)) .orderBy(desc(biddingCompanies.finalQuoteAmount)) return companies } catch (error) { console.error('Failed to get bidding companies data:', error) return [] } } // prItemsForBidding 테이블에서 품목 정보 조회 export async function getPRItemsForBidding(biddingId: number) { 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 [] } } // 견적 시스템에서 협력업체 정보를 가져오는 함수 export async function getQuotationVendors(biddingId: number): Promise { try { // bidding_companies 테이블을 메인으로 vendors, company_condition_responses를 조인하여 협력업체 정보 조회 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'` as currency, submissionDate: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, awardRatio: sql`CASE WHEN ${biddingCompanies.isWinner} THEN 100 ELSE 0 END`, 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`, // companyConditionResponses에서 입찰 조건들 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, }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .leftJoin(companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId)) .where(eq(biddingCompanies.biddingId, biddingId)) .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.toISOString().split('T')[0] : '', isWinner: vendor.isWinner || false, awardRatio: vendor.awardRatio || 0, status: vendor.status as 'pending' | 'submitted' | 'selected' | 'rejected', // companyConditionResponses에서 입찰 조건들 paymentTermsResponse: vendor.paymentTermsResponse || '', taxConditionsResponse: vendor.taxConditionsResponse || '', incotermsResponse: vendor.incotermsResponse || '', proposedContractDeliveryDate: vendor.proposedContractDeliveryDate ? (typeof vendor.proposedContractDeliveryDate === 'string' ? vendor.proposedContractDeliveryDate : vendor.proposedContractDeliveryDate.toISOString().split('T')[0]) : undefined, proposedShippingPort: vendor.proposedShippingPort || '', proposedDestinationPort: vendor.proposedDestinationPort || '', priceAdjustmentResponse: vendor.priceAdjustmentResponse || false, isInitialResponse: vendor.isInitialResponse || false, sparePartResponse: vendor.sparePartResponse || '', additionalProposals: vendor.additionalProposals || '', documents: [] // TODO: 문서 정보 조회 로직 추가 })) } catch (error) { console.error('Failed to get quotation vendors:', error) return [] } } // 내정가 수동 업데이트 (실제 저장) export async function updateTargetPrice( biddingId: number, targetPrice: number, targetPriceCalculationCriteria: string, userId: string ) { try { await db .update(biddings) .set({ targetPrice: targetPrice.toString(), targetPriceCalculationCriteria: targetPriceCalculationCriteria, updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '내정가가 성공적으로 업데이트되었습니다.' } } catch (error) { console.error('Failed to update target price:', error) return { success: false, error: '내정가 업데이트에 실패했습니다.' } } } // 협력업체 정보 저장 - 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 ? new Date(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 }) 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 ? new Date(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 }) 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)) }) 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 { await db .update(biddings) .set({ status: 'bidding_disposal', updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '유찰 처리가 완료되었습니다.' } } catch (error) { console.error('Failed to mark as disposal:', error) return { success: false, error: '유찰 처리에 실패했습니다.' } } } // 입찰 등록 (상태 변경) export async function registerBidding(biddingId: number, userId: string) { try { await db .update(biddings) .set({ status: 'bidding_opened', updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) //todo 입찰 등록하면 bidding_companies invitationStatus를 sent로 변경! await db .update(biddingCompanies) .set({ invitationStatus: 'sent', updatedAt: new Date() }) .where(eq(biddingCompanies.biddingId, biddingId)) revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '입찰이 성공적으로 등록되었습니다.' } } catch (error) { console.error('Failed to register bidding:', error) return { success: false, error: '입찰 등록에 실패했습니다.' } } } // 재입찰 생성 export async function createRebidding(originalBiddingId: number, userId: string) { try { // 원본 입찰 정보 조회 const originalBidding = await db .select() .from(biddings) .where(eq(biddings.id, originalBiddingId)) .limit(1) if (originalBidding.length === 0) { return { success: false, error: '원본 입찰을 찾을 수 없습니다.' } } const original = originalBidding[0] // 재입찰용 데이터 준비 const rebiddingData = { ...original, id: undefined, biddingNumber: `${original.biddingNumber}-R${(original.revision || 0) + 1}`, revision: (original.revision || 0) + 1, status: 'bidding_generated' as const, createdAt: new Date(), updatedAt: new Date() } // 새로운 입찰 생성 const [newBidding] = await db .insert(biddings) .values(rebiddingData) .returning({ id: biddings.id, biddingNumber: biddings.biddingNumber }) revalidatePath('/evcp/bid') revalidatePath(`/evcp/bid/${newBidding.id}`) return { success: true, message: '재입찰이 성공적으로 생성되었습니다.', data: newBidding } } 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() } }) revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '업체 선정 사유가 성공적으로 업데이트되었습니다.' } } catch (error) { console.error('Failed to update vendor selection reason:', error) return { success: false, error: '업체 선정 사유 업데이트에 실패했습니다.' } } } // 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)) 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 addVendorToBidding(biddingId: number, companyId: number, userId: string) { try { // 이미 추가된 업체인지 확인 const existing = await db .select() .from(biddingCompanies) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.companyId, companyId) )) .limit(1) if (existing.length > 0) { return { success: false, error: '이미 추가된 협력업체입니다.' } } // 새로운 협력업체 추가 await db .insert(biddingCompanies) .values({ biddingId, companyId, invitationStatus: 'pending', invitedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }) revalidatePath(`/evcp/bid/${biddingId}`) return { success: true, message: '협력업체가 성공적으로 추가되었습니다.' } } catch (error) { console.error('Failed to add vendor to bidding:', error) return { success: false, error: '협력업체 추가에 실패했습니다.' } } } // ================================================= // 협력업체 페이지용 함수들 (Partners) // ================================================= // 협력업체용 입찰 목록 조회 (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 notes: string | null createdAt: Date updatedAt: Date // updatedBy: string | 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, 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, }) .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.toISOString() : null, finalQuoteAmount: item.finalQuoteAmount ? Number(item.finalQuoteAmount) : null, // string을 number로 변환 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, 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, // 응답한 조건들 (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 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) => { // 1. company_condition_responses 테이블에 응답 저장/업데이트 const responseData = { paymentTermsResponse: response.paymentTermsResponse, taxConditionsResponse: response.taxConditionsResponse, incotermsResponse: response.incotermsResponse, proposedContractDeliveryDate: response.proposedContractDeliveryDate ? response.proposedContractDeliveryDate : null, // Date 대신 string 사용 proposedShippingPort: response.proposedShippingPort, proposedDestinationPort: response.proposedDestinationPort, priceAdjustmentResponse: response.priceAdjustmentResponse, isInitialResponse: response.isInitialResponse, sparePartResponse: response.sparePartResponse, additionalProposals: response.additionalProposals, submittedAt: new Date(), updatedAt: new Date(), } // 기존 응답이 있는지 확인 const existingResponse = await tx .select() .from(companyConditionResponses) .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) .limit(1) let companyConditionResponseId: number if (existingResponse.length > 0) { // 업데이트 await tx .update(companyConditionResponses) .set(responseData) .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) companyConditionResponseId = existingResponse[0].id } else { // 새로 생성 const [newResponse] = await tx .insert(companyConditionResponses) .values({ biddingCompanyId, ...responseData, }) .returning({ id: companyConditionResponses.id }) companyConditionResponseId = newResponse.id } // 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 ? new Date(response.priceAdjustmentForm.referenceDate) : null, comparisonDate: response.priceAdjustmentForm.comparisonDate ? new Date(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 ? new Date(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)) return true }) 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.toISOString().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) // 메일 발송 실패해도 참석 여부 업데이트는 성공으로 처리 } 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 } }