diff options
Diffstat (limited to 'lib/bidding/detail/service.ts')
| -rw-r--r-- | lib/bidding/detail/service.ts | 326 |
1 files changed, 226 insertions, 100 deletions
diff --git a/lib/bidding/detail/service.ts b/lib/bidding/detail/service.ts index d0dc6a08..2ce17713 100644 --- a/lib/bidding/detail/service.ts +++ b/lib/bidding/detail/service.ts @@ -62,21 +62,20 @@ export interface QuotationVendor { 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 // 연동제 적용 여부 + // 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 @@ -195,7 +194,7 @@ export async function getPRItemsForBidding(biddingId: number) { // 견적 시스템에서 협력업체 정보를 가져오는 함수 export async function getQuotationVendors(biddingId: number): Promise<QuotationVendor[]> { try { - // bidding_companies 테이블을 메인으로 vendors, bidding_conditions, company_condition_responses를 조인하여 협력업체 정보 조회 + // bidding_companies 테이블을 메인으로 vendors, company_condition_responses를 조인하여 협력업체 정보 조회 const vendorsData = await db .select({ id: biddingCompanies.id, @@ -208,9 +207,6 @@ export async function getQuotationVendors(biddingId: number): Promise<QuotationV 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`, @@ -220,19 +216,20 @@ export async function getQuotationVendors(biddingId: number): Promise<QuotationV 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, + // companyConditionResponses에서 입찰 조건들 + paymentTermsResponse: companyConditionResponses.paymentTermsResponse, + taxConditionsResponse: companyConditionResponses.taxConditionsResponse, + incotermsResponse: companyConditionResponses.incotermsResponse, + proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate, + proposedShippingPort: companyConditionResponses.proposedShippingPort, + proposedDestinationPort: companyConditionResponses.proposedDestinationPort, + priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse, + sparePartResponse: companyConditionResponses.sparePartResponse, + additionalProposals: companyConditionResponses.additionalProposals, }) .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)) @@ -247,21 +244,20 @@ export async function getQuotationVendors(biddingId: number): Promise<QuotationV 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, + // 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, + sparePartResponse: vendor.sparePartResponse || '', + additionalProposals: vendor.additionalProposals || '', documents: [] // TODO: 문서 정보 조회 로직 추가 })) } catch (error) { @@ -295,15 +291,14 @@ export async function updateTargetPrice( } } -// 협력업체 정보 저장 - biddingCompanies와 biddingConditions 테이블에 레코드 생성 -export async function createQuotationVendor(input: Omit<QuotationVendor, 'id'>, userId: string) { +// 협력업체 정보 저장 - 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, - vendorId: input.vendorId, quotationAmount: input.quotationAmount, currency: input.currency, status: input.status, @@ -312,9 +307,6 @@ export async function createQuotationVendor(input: Omit<QuotationVendor, 'id'>, 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, @@ -326,17 +318,20 @@ export async function createQuotationVendor(input: Omit<QuotationVendor, 'id'>, const biddingCompanyId = biddingCompanyResult[0].id - // 2. biddingConditions에 기본 조건 생성 - await tx.insert(biddingConditions).values({ + // 2. companyConditionResponses에 입찰 조건 생성 + await tx.insert(companyConditionResponses).values({ biddingCompanyId: biddingCompanyId, - paymentTerms: '["선금 30%, 잔금 70%"]', // 기본 지급조건 - taxConditions: '["부가세 별도"]', // 기본 세금조건 - contractDeliveryDate: null, - isPriceAdjustmentApplicable: false, - incoterms: '["FOB"]', // 기본 운송조건 - shippingPort: null, - destinationPort: null, - sparePartOptions: '[]', // 기본 예비품 옵션 + 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(), }) @@ -357,7 +352,7 @@ export async function createQuotationVendor(input: Omit<QuotationVendor, 'id'>, } // 협력업체 정보 업데이트 -export async function updateQuotationVendor(id: number, input: Partial<QuotationVendor>, userId: string) { +export async function updateQuotationVendor(id: number, input: any, userId: string) { try { const result = await db.transaction(async (tx) => { // 1. biddingCompanies 테이블 업데이트 @@ -377,28 +372,32 @@ export async function updateQuotationVendor(id: number, input: Partial<Quotation .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) { + // 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.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 + 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(biddingConditions) + await tx.update(companyConditionResponses) .set(conditionsUpdateData) - .where(eq(biddingConditions.biddingCompanyId, id)) + .where(eq(companyConditionResponses.biddingCompanyId, id)) } return true @@ -683,7 +682,7 @@ export interface PartnersBiddingListItem { notes: string | null createdAt: Date updatedAt: Date - updatedBy: string | null + // updatedBy: string | null // biddings 정보 biddingId: number @@ -725,7 +724,7 @@ export async function getBiddingListForPartners(companyId: number): Promise<Part notes: biddingCompanies.notes, createdAt: biddingCompanies.createdAt, updatedAt: biddingCompanies.updatedAt, - updatedBy: biddingCompanies.updatedBy, + // updatedBy: biddingCompanies.updatedBy, // 이 필드가 존재하지 않음 // biddings 정보 biddingId: biddings.id, @@ -754,11 +753,13 @@ export async function getBiddingListForPartners(companyId: number): Promise<Part )) .orderBy(desc(biddingCompanies.createdAt)) - // console.log(result) + 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, @@ -817,30 +818,22 @@ export async function getBiddingDetailsForPartners(biddingId: number, companyId: finalQuoteAmount: biddingCompanies.finalQuoteAmount, finalQuoteSubmittedAt: biddingCompanies.finalQuoteSubmittedAt, isWinner: biddingCompanies.isWinner, + isAttendingMeeting: biddingCompanies.isAttendingMeeting, - // 제시된 조건들 (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, + // 응답한 조건들 (company_condition_responses) - 제시된 조건과 응답 모두 여기서 관리 + paymentTermsResponse: companyConditionResponses.paymentTermsResponse, + taxConditionsResponse: companyConditionResponses.taxConditionsResponse, + incotermsResponse: companyConditionResponses.incotermsResponse, proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate, proposedShippingPort: companyConditionResponses.proposedShippingPort, proposedDestinationPort: companyConditionResponses.proposedDestinationPort, priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse, + sparePartResponse: companyConditionResponses.sparePartResponse, 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), @@ -866,6 +859,7 @@ export async function submitPartnerResponse( proposedShippingPort?: string proposedDestinationPort?: string priceAdjustmentResponse?: boolean + sparePartResponse?: string additionalProposals?: string finalQuoteAmount?: number }, @@ -878,10 +872,11 @@ export async function submitPartnerResponse( paymentTermsResponse: response.paymentTermsResponse, taxConditionsResponse: response.taxConditionsResponse, incotermsResponse: response.incotermsResponse, - proposedContractDeliveryDate: response.proposedContractDeliveryDate ? new Date(response.proposedContractDeliveryDate) : null, + proposedContractDeliveryDate: response.proposedContractDeliveryDate ? response.proposedContractDeliveryDate : null, // Date 대신 string 사용 proposedShippingPort: response.proposedShippingPort, proposedDestinationPort: response.proposedDestinationPort, priceAdjustmentResponse: response.priceAdjustmentResponse, + sparePartResponse: response.sparePartResponse, additionalProposals: response.additionalProposals, submittedAt: new Date(), updatedAt: new Date(), @@ -914,7 +909,7 @@ export async function submitPartnerResponse( const companyUpdateData: any = { respondedAt: new Date(), updatedAt: new Date(), - updatedBy: userId, + // updatedBy: userId, // 이 필드가 존재하지 않음 } if (response.finalQuoteAmount !== undefined) { @@ -931,7 +926,7 @@ export async function submitPartnerResponse( return true }) - revalidatePath(`/partners/bid/${biddingId}`) + revalidatePath('/partners/bid/[id]') return { success: true, message: '응찰이 성공적으로 제출되었습니다.', @@ -942,26 +937,157 @@ export async function submitPartnerResponse( } } -// 사양설명회 참석 여부 업데이트 +// 사양설명회 정보 조회 (협력업체용) +export async function getSpecificationMeetingForPartners(biddingId: number) { + try { + // 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') + )) + + // biddings 테이블에서 사양설명회 기본 정보 조회 + const bidding = await db + .select({ + id: biddings.id, + title: biddings.title, + biddingNumber: biddings.biddingNumber, + preQuoteDate: biddings.preQuoteDate, + biddingRegistrationDate: biddings.biddingRegistrationDate, + managerName: biddings.managerName, + managerEmail: biddings.managerEmail, + managerPhone: biddings.managerPhone, + }) + .from(biddings) + .where(eq(biddings.id, biddingId)) + .limit(1) + + if (bidding.length === 0) { + return { success: false, error: '입찰 정보를 찾을 수 없습니다.' } + } + + return { + success: true, + data: { + ...bidding[0], + documents, + meetingDate: bidding[0].preQuoteDate ? bidding[0].preQuoteDate.toISOString().split('T')[0] : null, + contactPerson: bidding[0].managerName, + contactEmail: bidding[0].managerEmail, + contactPhone: bidding[0].managerPhone, + } + } + } catch (error) { + console.error('Failed to get specification meeting info:', error) + return { success: false, error: '사양설명회 정보 조회에 실패했습니다.' } + } +} + +// 사양설명회 참석 여부 업데이트 (상세 정보 포함) export async function updatePartnerAttendance( biddingCompanyId: number, - isAttending: boolean, + attendanceData: { + isAttending: boolean + attendeeCount?: number + representativeName?: string + representativePhone?: string + }, userId: string ) { try { - await db - .update(biddingCompanies) - .set({ - isAttendingMeeting: isAttending, - updatedAt: new Date(), - updatedBy: userId, - }) - .where(eq(biddingCompanies.id, biddingCompanyId)) + 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: `사양설명회 ${isAttending ? '참석' : '불참'}으로 설정되었습니다.`, + message: `사양설명회 ${attendanceData.isAttending ? '참석' : '불참'}으로 설정되었습니다.`, + data: result } } catch (error) { console.error('Failed to update partner attendance:', error) |