diff options
Diffstat (limited to 'lib/bidding/detail/service.ts')
| -rw-r--r-- | lib/bidding/detail/service.ts | 970 |
1 files changed, 970 insertions, 0 deletions
diff --git a/lib/bidding/detail/service.ts b/lib/bidding/detail/service.ts new file mode 100644 index 00000000..d0dc6a08 --- /dev/null +++ b/lib/bidding/detail/service.ts @@ -0,0 +1,970 @@ +'use server' + +import db from '@/db/db' +import { biddings, prItemsForBidding, biddingDocuments, biddingCompanies, vendors, companyPrItemBids, companyConditionResponses, vendorSelectionResults, BiddingListItem, biddingConditions } from '@/db/schema' +import { eq, and, sql, desc, ne } from 'drizzle-orm' +import { revalidatePath } from 'next/cache' + +// 데이터 조회 함수들 +export interface BiddingDetailData { + bidding: Awaited<ReturnType<typeof getBiddingById>> + quotationDetails: QuotationDetails | null + quotationVendors: QuotationVendor[] + biddingCompanies: Awaited<ReturnType<typeof getBiddingCompaniesData>> + prItems: Awaited<ReturnType<typeof getPRItemsForBidding>> +} + +// getBiddingById 함수 임포트 (기존 함수 재사용) +import { getBiddingById, getPRDetailsAction } from '@/lib/bidding/service' + +// Promise.all을 사용하여 모든 데이터를 병렬로 조회 +export async function getBiddingDetailData(biddingId: number): Promise<BiddingDetailData> { + 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 + paymentTerms: string // 지급조건 (응답) + taxConditions: string // 세금조건 (응답) + deliveryDate: string // 납품일 (응답) + submissionDate: string // 제출일 + isWinner: boolean // 낙찰여부 + awardRatio: number // 발주비율 + status: 'pending' | 'submitted' | 'selected' | 'rejected' + // bidding_conditions에서 제시된 조건들 + offeredPaymentTerms?: string // 제시된 지급조건 + offeredTaxConditions?: string // 제시된 세금조건 + offeredIncoterms?: string // 제시된 운송조건 + offeredContractDeliveryDate?: string // 제시된 계약납기일 + offeredShippingPort?: string // 제시된 선적지 + offeredDestinationPort?: string // 제시된 도착지 + isPriceAdjustmentApplicable?: boolean // 연동제 적용 여부 + documents: Array<{ + id: number + fileName: string + originalFileName: string + filePath: string + uploadedAt: string + }> +} + +// 견적 시스템에서 내정가 및 관련 정보를 가져오는 함수 +export async function getQuotationDetails(biddingId: number): Promise<QuotationDetails | null> { + try { + // bidding_companies 테이블에서 견적 데이터를 집계 + const quotationStats = await db + .select({ + biddingId: biddingCompanies.biddingId, + estimatedPrice: sql<number>`AVG(${biddingCompanies.finalQuoteAmount})`.as('estimated_price'), + lowestQuote: sql<number>`MIN(${biddingCompanies.finalQuoteAmount})`.as('lowest_quote'), + averageQuote: sql<number>`AVG(${biddingCompanies.finalQuoteAmount})`.as('average_quote'), + targetPrice: sql<number>`AVG(${biddings.targetPrice})`.as('target_price'), + quotationCount: sql<number>`COUNT(*)`.as('quotation_count'), + lastUpdated: sql<string>`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<QuotationVendor[]> { + try { + // bidding_companies 테이블을 메인으로 vendors, bidding_conditions, 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<string>`'KRW'` as currency, + paymentTerms: sql<string>`COALESCE(${companyConditionResponses.paymentTermsResponse}, '')`, + taxConditions: sql<string>`COALESCE(${companyConditionResponses.taxConditionsResponse}, '')`, + deliveryDate: companyConditionResponses.proposedContractDeliveryDate, + submissionDate: biddingCompanies.finalQuoteSubmittedAt, + isWinner: biddingCompanies.isWinner, + awardRatio: sql<number>`CASE WHEN ${biddingCompanies.isWinner} THEN 100 ELSE 0 END`, + status: sql<string>`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`, + // bidding_conditions에서 제시된 조건들 + offeredPaymentTerms: biddingConditions.paymentTerms, + offeredTaxConditions: biddingConditions.taxConditions, + offeredIncoterms: biddingConditions.incoterms, + offeredContractDeliveryDate: biddingConditions.contractDeliveryDate, + offeredShippingPort: biddingConditions.shippingPort, + offeredDestinationPort: biddingConditions.destinationPort, + isPriceAdjustmentApplicable: biddingConditions.isPriceAdjustmentApplicable, + }) + .from(biddingCompanies) + .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) + .leftJoin(companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId)) + .leftJoin(biddingConditions, eq(biddingCompanies.id, biddingConditions.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, + paymentTerms: vendor.paymentTerms, + taxConditions: vendor.taxConditions, + deliveryDate: vendor.deliveryDate ? vendor.deliveryDate.toISOString().split('T')[0] : '', + 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', + // bidding_conditions에서 제시된 조건들 + offeredPaymentTerms: vendor.offeredPaymentTerms, + offeredTaxConditions: vendor.offeredTaxConditions, + offeredIncoterms: vendor.offeredIncoterms, + offeredContractDeliveryDate: vendor.offeredContractDeliveryDate ? vendor.offeredContractDeliveryDate.toISOString().split('T')[0] : undefined, + offeredShippingPort: vendor.offeredShippingPort, + offeredDestinationPort: vendor.offeredDestinationPort, + isPriceAdjustmentApplicable: vendor.isPriceAdjustmentApplicable, + 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와 biddingConditions 테이블에 레코드 생성 +export async function createQuotationVendor(input: Omit<QuotationVendor, 'id'>, 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, + vendorId: 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, + paymentTerms: input.paymentTerms, + taxConditions: input.taxConditions, + deliveryDate: input.deliveryDate ? new Date(input.deliveryDate) : null, + submissionDate: new Date(), + createdBy: userId, + updatedBy: userId, + }).returning({ id: biddingCompanies.id }) + + if (biddingCompanyResult.length === 0) { + throw new Error('협력업체 정보 저장에 실패했습니다.') + } + + const biddingCompanyId = biddingCompanyResult[0].id + + // 2. biddingConditions에 기본 조건 생성 + await tx.insert(biddingConditions).values({ + biddingCompanyId: biddingCompanyId, + paymentTerms: '["선금 30%, 잔금 70%"]', // 기본 지급조건 + taxConditions: '["부가세 별도"]', // 기본 세금조건 + contractDeliveryDate: null, + isPriceAdjustmentApplicable: false, + incoterms: '["FOB"]', // 기본 운송조건 + shippingPort: null, + destinationPort: null, + sparePartOptions: '[]', // 기본 예비품 옵션 + 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: Partial<QuotationVendor>, 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. biddingConditions 테이블 업데이트 (제시된 조건들) + if (input.offeredPaymentTerms !== undefined || + input.offeredTaxConditions !== undefined || + input.offeredIncoterms !== undefined || + input.offeredContractDeliveryDate !== undefined || + input.offeredShippingPort !== undefined || + input.offeredDestinationPort !== undefined || + input.isPriceAdjustmentApplicable !== undefined) { + + const conditionsUpdateData: any = {} + if (input.offeredPaymentTerms !== undefined) conditionsUpdateData.paymentTerms = input.offeredPaymentTerms + if (input.offeredTaxConditions !== undefined) conditionsUpdateData.taxConditions = input.offeredTaxConditions + if (input.offeredIncoterms !== undefined) conditionsUpdateData.incoterms = input.offeredIncoterms + if (input.offeredContractDeliveryDate !== undefined) conditionsUpdateData.contractDeliveryDate = input.offeredContractDeliveryDate ? new Date(input.offeredContractDeliveryDate) : null + if (input.offeredShippingPort !== undefined) conditionsUpdateData.shippingPort = input.offeredShippingPort + if (input.offeredDestinationPort !== undefined) conditionsUpdateData.destinationPort = input.offeredDestinationPort + if (input.isPriceAdjustmentApplicable !== undefined) conditionsUpdateData.isPriceAdjustmentApplicable = input.isPriceAdjustmentApplicable + conditionsUpdateData.updatedAt = new Date() + + await tx.update(biddingConditions) + .set(conditionsUpdateData) + .where(eq(biddingConditions.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<typeof prItemsForBidding.$inferSelect>, 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<PartnersBiddingListItem[]> { + 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) + + // 계산된 필드 추가 + const resultWithCalculatedFields = result.map(item => ({ + ...item, + 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, + + // 제시된 조건들 (bidding_conditions) + offeredPaymentTerms: biddingConditions.paymentTerms, + offeredTaxConditions: biddingConditions.taxConditions, + offeredIncoterms: biddingConditions.incoterms, + offeredContractDeliveryDate: biddingConditions.contractDeliveryDate, + offeredShippingPort: biddingConditions.shippingPort, + offeredDestinationPort: biddingConditions.destinationPort, + isPriceAdjustmentApplicable: biddingConditions.isPriceAdjustmentApplicable, + + // 응답한 조건들 (company_condition_responses) + responsePaymentTerms: companyConditionResponses.paymentTermsResponse, + responseTaxConditions: companyConditionResponses.taxConditionsResponse, + responseIncoterms: companyConditionResponses.incotermsResponse, + proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate, + proposedShippingPort: companyConditionResponses.proposedShippingPort, + proposedDestinationPort: companyConditionResponses.proposedDestinationPort, + priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse, + additionalProposals: companyConditionResponses.additionalProposals, + responseSubmittedAt: companyConditionResponses.submittedAt, + }) + .from(biddings) + .innerJoin(biddingCompanies, eq(biddings.id, biddingCompanies.biddingId)) + .leftJoin(biddingConditions, eq(biddingCompanies.id, biddingConditions.biddingCompanyId)) + .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 + additionalProposals?: string + finalQuoteAmount?: number + }, + 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 ? new Date(response.proposedContractDeliveryDate) : null, + proposedShippingPort: response.proposedShippingPort, + proposedDestinationPort: response.proposedDestinationPort, + priceAdjustmentResponse: response.priceAdjustmentResponse, + additionalProposals: response.additionalProposals, + submittedAt: new Date(), + updatedAt: new Date(), + } + + // 기존 응답이 있는지 확인 + const existingResponse = await tx + .select() + .from(companyConditionResponses) + .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) + .limit(1) + + if (existingResponse.length > 0) { + // 업데이트 + await tx + .update(companyConditionResponses) + .set(responseData) + .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) + } else { + // 새로 생성 + await tx + .insert(companyConditionResponses) + .values({ + biddingCompanyId, + ...responseData, + }) + } + + // 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/${biddingId}`) + return { + success: true, + message: '응찰이 성공적으로 제출되었습니다.', + } + } catch (error) { + console.error('Failed to submit partner response:', error) + return { success: false, error: '응찰 제출에 실패했습니다.' } + } +} + +// 사양설명회 참석 여부 업데이트 +export async function updatePartnerAttendance( + biddingCompanyId: number, + isAttending: boolean, + userId: string +) { + try { + await db + .update(biddingCompanies) + .set({ + isAttendingMeeting: isAttending, + updatedAt: new Date(), + updatedBy: userId, + }) + .where(eq(biddingCompanies.id, biddingCompanyId)) + + revalidatePath('/partners/bid/[id]') + return { + success: true, + message: `사양설명회 ${isAttending ? '참석' : '불참'}으로 설정되었습니다.`, + } + } catch (error) { + console.error('Failed to update partner attendance:', error) + return { success: false, error: '참석 여부 업데이트에 실패했습니다.' } + } +} |