'use server' import db from '@/db/db' import { biddingCompanies, companyConditionResponses, biddings, prItemsForBidding, biddingDocuments, companyPrItemBids, priceAdjustmentForms } from '@/db/schema/bidding' import { basicContractTemplates } from '@/db/schema' import { vendors } from '@/db/schema/vendors' import { users } from '@/db/schema' import { sendEmail } from '@/lib/mail/sendEmail' import { eq, inArray, and, ilike } from 'drizzle-orm' import { mkdir, writeFile } from 'fs/promises' import path from 'path' import { revalidateTag, revalidatePath } from 'next/cache' import { basicContract } from '@/db/schema/basicContractDocumnet' import { saveFile } from '@/lib/file-stroage' // userId를 user.name으로 변환하는 유틸리티 함수 async function getUserNameById(userId: string): Promise { try { const user = await db .select({ name: users.name }) .from(users) .where(eq(users.id, parseInt(userId))) .limit(1) return user[0]?.name || userId // user.name이 없으면 userId를 그대로 반환 } catch (error) { console.error('Failed to get user name:', error) return userId // 에러 시 userId를 그대로 반환 } } interface CreateBiddingCompanyInput { biddingId: number companyId: number contactPerson?: string contactEmail?: string contactPhone?: string notes?: string } interface UpdateBiddingCompanyInput { contactPerson?: string contactEmail?: string contactPhone?: string preQuoteAmount?: number notes?: string invitationStatus?: 'pending' | 'accepted' | 'declined' isPreQuoteSelected?: boolean isAttendingMeeting?: boolean } interface PrItemQuotation { prItemId: number bidUnitPrice: number bidAmount: number proposedDeliveryDate?: string technicalSpecification?: string } interface PreQuoteDocumentUpload { fileName: string originalFileName: string fileSize: number mimeType: string filePath: string } // 사전견적용 업체 추가 - biddingCompanies와 company_condition_responses 레코드 생성 export async function createBiddingCompany(input: CreateBiddingCompanyInput) { try { const result = await db.transaction(async (tx) => { // 1. biddingCompanies 레코드 생성 const biddingCompanyResult = await tx.insert(biddingCompanies).values({ biddingId: input.biddingId, companyId: input.companyId, invitationStatus: 'pending', // 초기 상태: 입찰생성 invitedAt: new Date(), contactPerson: input.contactPerson, contactEmail: input.contactEmail, contactPhone: input.contactPhone, notes: input.notes, }).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, // 나머지 필드들은 null로 시작 (벤더가 나중에 응답) }) return biddingCompanyId }) return { success: true, message: '업체가 성공적으로 추가되었습니다.', data: { id: result } } } catch (error) { console.error('Failed to create bidding company:', error) return { success: false, error: error instanceof Error ? error.message : '업체 추가에 실패했습니다.' } } } // 사전견적용 업체 정보 업데이트 export async function updateBiddingCompany(id: number, input: UpdateBiddingCompanyInput) { try { const updateData: any = { updatedAt: new Date() } 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.preQuoteAmount !== undefined) updateData.preQuoteAmount = input.preQuoteAmount if (input.notes !== undefined) updateData.notes = input.notes if (input.invitationStatus !== undefined) { updateData.invitationStatus = input.invitationStatus if (input.invitationStatus !== 'pending') { updateData.respondedAt = new Date() } } if (input.isPreQuoteSelected !== undefined) updateData.isPreQuoteSelected = input.isPreQuoteSelected if (input.isAttendingMeeting !== undefined) updateData.isAttendingMeeting = input.isAttendingMeeting await db.update(biddingCompanies) .set(updateData) .where(eq(biddingCompanies.id, id)) return { success: true, message: '업체 정보가 성공적으로 업데이트되었습니다.', } } catch (error) { console.error('Failed to update bidding company:', error) return { success: false, error: error instanceof Error ? error.message : '업체 정보 업데이트에 실패했습니다.' } } } // 본입찰 등록 상태 업데이트 (복수 업체 선택 가능) export async function updatePreQuoteSelection(companyIds: number[], isSelected: boolean) { try { // 업체들의 입찰 ID 조회 (캐시 무효화를 위해) const companies = await db .select({ biddingId: biddingCompanies.biddingId }) .from(biddingCompanies) .where(inArray(biddingCompanies.id, companyIds)) .limit(1) await db.update(biddingCompanies) .set({ isPreQuoteSelected: isSelected, invitationStatus: 'pending', // 초기 상태: 입찰생성 updatedAt: new Date() }) .where(inArray(biddingCompanies.id, companyIds)) // 캐시 무효화 if (companies.length > 0) { const biddingId = companies[0].biddingId revalidateTag(`bidding-${biddingId}`) revalidateTag('bidding-detail') revalidateTag('quotation-vendors') revalidateTag('quotation-details') revalidatePath(`/evcp/bid/${biddingId}`) } const message = isSelected ? `${companyIds.length}개 업체가 본입찰 대상으로 선정되었습니다.` : `${companyIds.length}개 업체의 본입찰 선정이 취소되었습니다.` return { success: true, message } } catch (error) { console.error('Failed to update pre-quote selection:', error) return { success: false, error: error instanceof Error ? error.message : '본입찰 선정 상태 업데이트에 실패했습니다.' } } } // 사전견적용 업체 삭제 export async function deleteBiddingCompany(id: number) { try { // 1. 해당 업체의 초대 상태 확인 const company = await db .select({ invitationStatus: biddingCompanies.invitationStatus }) .from(biddingCompanies) .where(eq(biddingCompanies.id, id)) .then(rows => rows[0]) if (!company) { return { success: false, error: '해당 업체를 찾을 수 없습니다.' } } // 이미 초대가 발송된 경우(수락, 거절, 요청됨 등) 삭제 불가 if (company.invitationStatus !== 'pending') { return { success: false, error: '이미 초대를 보낸 업체는 삭제할 수 없습니다.' } } await db.transaction(async (tx) => { // 2. 먼저 관련된 조건 응답들 삭제 await tx.delete(companyConditionResponses) .where(eq(companyConditionResponses.biddingCompanyId, id)) // 3. biddingCompanies 레코드 삭제 await tx.delete(biddingCompanies) .where(eq(biddingCompanies.id, id)) }) return { success: true, message: '업체가 성공적으로 삭제되었습니다.' } } catch (error) { console.error('Failed to delete bidding company:', error) return { success: false, error: error instanceof Error ? error.message : '업체 삭제에 실패했습니다.' } } } // 특정 입찰의 참여 업체 목록 조회 (company_condition_responses와 vendors 조인) export async function getBiddingCompanies(biddingId: number) { try { const companies = await db .select({ // bidding_companies 필드들 id: biddingCompanies.id, biddingId: biddingCompanies.biddingId, companyId: biddingCompanies.companyId, invitationStatus: biddingCompanies.invitationStatus, invitedAt: biddingCompanies.invitedAt, respondedAt: biddingCompanies.respondedAt, preQuoteAmount: biddingCompanies.preQuoteAmount, preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt, preQuoteDeadline: biddingCompanies.preQuoteDeadline, isPreQuoteSelected: biddingCompanies.isPreQuoteSelected, isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated, isAttendingMeeting: biddingCompanies.isAttendingMeeting, notes: biddingCompanies.notes, contactPerson: biddingCompanies.contactPerson, contactEmail: biddingCompanies.contactEmail, contactPhone: biddingCompanies.contactPhone, createdAt: biddingCompanies.createdAt, updatedAt: biddingCompanies.updatedAt, // vendors 테이블에서 업체 정보 companyName: vendors.vendorName, companyCode: vendors.vendorCode, // company_condition_responses 필드들 paymentTermsResponse: companyConditionResponses.paymentTermsResponse, taxConditionsResponse: companyConditionResponses.taxConditionsResponse, proposedContractDeliveryDate: companyConditionResponses.proposedContractDeliveryDate, priceAdjustmentResponse: companyConditionResponses.priceAdjustmentResponse, isInitialResponse: companyConditionResponses.isInitialResponse, incotermsResponse: companyConditionResponses.incotermsResponse, proposedShippingPort: companyConditionResponses.proposedShippingPort, proposedDestinationPort: companyConditionResponses.proposedDestinationPort, 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)) return { success: true, data: companies } } catch (error) { console.error('Failed to get bidding companies:', error) return { success: false, error: error instanceof Error ? error.message : '업체 목록 조회에 실패했습니다.' } } } // 선택된 업체들에게 사전견적 초대 발송 export async function sendPreQuoteInvitations(companyIds: number[], preQuoteDeadline?: Date | string) { try { if (companyIds.length === 0) { return { success: false, error: '선택된 업체가 없습니다.' } } // 선택된 업체들의 정보와 입찰 정보 조회 const companiesInfo = await db .select({ biddingCompanyId: biddingCompanies.id, companyId: biddingCompanies.companyId, biddingId: biddingCompanies.biddingId, companyName: vendors.vendorName, companyEmail: vendors.email, // 입찰 정보 biddingNumber: biddings.biddingNumber, revision: biddings.revision, projectName: biddings.projectName, biddingTitle: biddings.title, itemName: biddings.itemName, preQuoteDate: biddings.preQuoteDate, budget: biddings.budget, currency: biddings.currency, managerName: biddings.managerName, managerEmail: biddings.managerEmail, managerPhone: biddings.managerPhone, }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .leftJoin(biddings, eq(biddingCompanies.biddingId, biddings.id)) .where(inArray(biddingCompanies.id, companyIds)) if (companiesInfo.length === 0) { return { success: false, error: '업체 정보를 찾을 수 없습니다.' } } await db.transaction(async (tx) => { // 선택된 업체들의 상태를 '사전견적요청(초대발송)'으로 변경 for (const id of companyIds) { await tx.update(biddingCompanies) .set({ invitationStatus: 'sent', // 사전견적 초대 발송 상태 invitedAt: new Date(), preQuoteDeadline: preQuoteDeadline ? new Date(preQuoteDeadline) : null, updatedAt: new Date() }) .where(eq(biddingCompanies.id, id)) } }) // 각 업체별로 이메일 발송 for (const company of companiesInfo) { if (company.companyEmail) { try { await sendEmail({ to: company.companyEmail, template: 'pre-quote-invitation', context: { companyName: company.companyName, biddingNumber: company.biddingNumber, revision: company.revision, projectName: company.projectName, biddingTitle: company.biddingTitle, itemName: company.itemName, preQuoteDate: company.preQuoteDate ? new Date(company.preQuoteDate).toLocaleDateString() : null, budget: company.budget ? company.budget.toLocaleString() : null, currency: company.currency, managerName: company.managerName, managerEmail: company.managerEmail, managerPhone: company.managerPhone, loginUrl: `${process.env.NEXT_PUBLIC_APP_URL}/partners/bid/${company.biddingId}/pre-quote`, currentYear: new Date().getFullYear(), language: 'ko' } }) } catch (emailError) { console.error(`Failed to send email to ${company.companyEmail}:`, emailError) // 이메일 발송 실패해도 전체 프로세스는 계속 진행 } } } // 3. 입찰 상태를 사전견적 요청으로 변경 (bidding_generated 상태에서만) for (const company of companiesInfo) { await db.transaction(async (tx) => { await tx .update(biddings) .set({ status: 'request_for_quotation', updatedAt: new Date() }) .where(and( eq(biddings.id, company.biddingId), eq(biddings.status, 'bidding_generated') )) }) } return { success: true, message: `${companyIds.length}개 업체에 사전견적 초대를 발송했습니다.` } } catch (error) { console.error('Failed to send pre-quote invitations:', error) return { success: false, error: error instanceof Error ? error.message : '초대 발송에 실패했습니다.' } } } // Partners에서 특정 업체의 입찰 정보 조회 (사전견적 단계) export async function getBiddingCompaniesForPartners(biddingId: number, companyId: number) { try { // 1. 먼저 입찰 기본 정보를 가져옴 const biddingResult = 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, }) .from(biddings) .where(eq(biddings.id, biddingId)) .limit(1) if (biddingResult.length === 0) { return null } const biddingData = biddingResult[0] // 2. 해당 업체의 biddingCompanies 정보 조회 const companyResult = await db .select({ biddingCompanyId: biddingCompanies.id, biddingId: biddingCompanies.biddingId, invitationStatus: biddingCompanies.invitationStatus, preQuoteAmount: biddingCompanies.preQuoteAmount, preQuoteSubmittedAt: biddingCompanies.preQuoteSubmittedAt, preQuoteDeadline: biddingCompanies.preQuoteDeadline, isPreQuoteSelected: biddingCompanies.isPreQuoteSelected, isPreQuoteParticipated: biddingCompanies.isPreQuoteParticipated, 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, sparePartResponse: companyConditionResponses.sparePartResponse, isInitialResponse: companyConditionResponses.isInitialResponse, additionalProposals: companyConditionResponses.additionalProposals, }) .from(biddingCompanies) .leftJoin( companyConditionResponses, eq(biddingCompanies.id, companyConditionResponses.biddingCompanyId) ) .where( and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.companyId, companyId) ) ) .limit(1) // 3. 결과 조합 if (companyResult.length === 0) { // 아직 초대되지 않은 상태 return { ...biddingData, biddingCompanyId: null, biddingId: biddingData.id, invitationStatus: null, preQuoteAmount: null, preQuoteSubmittedAt: null, preQuoteDeadline: null, isPreQuoteSelected: false, isPreQuoteParticipated: null, isAttendingMeeting: null, paymentTermsResponse: null, taxConditionsResponse: null, incotermsResponse: null, proposedContractDeliveryDate: null, proposedShippingPort: null, proposedDestinationPort: null, priceAdjustmentResponse: null, sparePartResponse: null, isInitialResponse: null, additionalProposals: null, } } const companyData = companyResult[0] return { ...biddingData, ...companyData, biddingId: biddingData.id, // bidding ID 보장 } } catch (error) { console.error('Failed to get bidding companies for partners:', error) throw error } } // Partners에서 사전견적 응답 제출 export async function submitPreQuoteResponse( biddingCompanyId: number, responseData: { preQuoteAmount?: number // 품목별 계산에서 자동으로 계산되므로 optional prItemQuotations?: PrItemQuotation[] // 품목별 견적 정보 추가 paymentTermsResponse?: string taxConditionsResponse?: string incotermsResponse?: string proposedContractDeliveryDate?: string proposedShippingPort?: string proposedDestinationPort?: string priceAdjustmentResponse?: boolean isInitialResponse?: boolean sparePartResponse?: string additionalProposals?: string priceAdjustmentForm?: any }, userId: string ) { try { let finalAmount = responseData.preQuoteAmount || 0 await db.transaction(async (tx) => { // 1. 품목별 견적 정보 최종 저장 (사전견적 제출) if (responseData.prItemQuotations && responseData.prItemQuotations.length > 0) { // 기존 사전견적 품목 삭제 후 새로 생성 await tx.delete(companyPrItemBids) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.isPreQuote, true) ) ) // 품목별 견적 최종 저장 for (const item of responseData.prItemQuotations) { await tx.insert(companyPrItemBids) .values({ biddingCompanyId, prItemId: item.prItemId, bidUnitPrice: item.bidUnitPrice.toString(), bidAmount: item.bidAmount.toString(), proposedDeliveryDate: item.proposedDeliveryDate || null, technicalSpecification: item.technicalSpecification || null, currency: 'KRW', isPreQuote: true, submittedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }) } // 총 금액 다시 계산 finalAmount = responseData.prItemQuotations.reduce((sum, item) => sum + item.bidAmount, 0) } // 2. biddingCompanies 업데이트 (사전견적 금액, 제출 시간, 상태 변경) await tx.update(biddingCompanies) .set({ preQuoteAmount: finalAmount.toString(), preQuoteSubmittedAt: new Date(), invitationStatus: 'submitted', // 사전견적 제출 완료 상태로 변경 updatedAt: new Date() }) .where(eq(biddingCompanies.id, biddingCompanyId)) // 3. company_condition_responses 업데이트 const finalConditionResult = await tx.update(companyConditionResponses) .set({ paymentTermsResponse: responseData.paymentTermsResponse, taxConditionsResponse: responseData.taxConditionsResponse, incotermsResponse: responseData.incotermsResponse, proposedContractDeliveryDate: responseData.proposedContractDeliveryDate, proposedShippingPort: responseData.proposedShippingPort, proposedDestinationPort: responseData.proposedDestinationPort, priceAdjustmentResponse: responseData.priceAdjustmentResponse, isInitialResponse: responseData.isInitialResponse, sparePartResponse: responseData.sparePartResponse, additionalProposals: responseData.additionalProposals, updatedAt: new Date() }) .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) .returning() // 4. 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우) if (responseData.priceAdjustmentResponse && responseData.priceAdjustmentForm && finalConditionResult.length > 0) { const companyConditionResponseId = finalConditionResult[0].id const priceAdjustmentData = { companyConditionResponsesId: companyConditionResponseId, itemName: responseData.priceAdjustmentForm.itemName, adjustmentReflectionPoint: responseData.priceAdjustmentForm.adjustmentReflectionPoint, majorApplicableRawMaterial: responseData.priceAdjustmentForm.majorApplicableRawMaterial, adjustmentFormula: responseData.priceAdjustmentForm.adjustmentFormula, rawMaterialPriceIndex: responseData.priceAdjustmentForm.rawMaterialPriceIndex, referenceDate: responseData.priceAdjustmentForm.referenceDate as string || null, comparisonDate: responseData.priceAdjustmentForm.comparisonDate as string || null, adjustmentRatio: responseData.priceAdjustmentForm.adjustmentRatio || null, notes: responseData.priceAdjustmentForm.notes, adjustmentConditions: responseData.priceAdjustmentForm.adjustmentConditions, majorNonApplicableRawMaterial: responseData.priceAdjustmentForm.majorNonApplicableRawMaterial, adjustmentPeriod: responseData.priceAdjustmentForm.adjustmentPeriod, contractorWriter: responseData.priceAdjustmentForm.contractorWriter, adjustmentDate: responseData.priceAdjustmentForm.adjustmentDate as string || null, nonApplicableReason: responseData.priceAdjustmentForm.nonApplicableReason, } as any // 기존 연동제 정보가 있는지 확인 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) } } // 5. 입찰 상태를 사전견적 접수로 변경 (request_for_quotation 상태에서만) // 또한 사전견적 접수일 업데이트 const biddingCompany = await tx .select({ biddingId: biddingCompanies.biddingId }) .from(biddingCompanies) .where(eq(biddingCompanies.id, biddingCompanyId)) .limit(1) if (biddingCompany.length > 0) { await tx .update(biddings) .set({ status: 'received_quotation', preQuoteDate: new Date().toISOString().split('T')[0], // 사전견적 접수일 업데이트 updatedAt: new Date() }) .where(and( eq(biddings.id, biddingCompany[0].biddingId), eq(biddings.status, 'request_for_quotation') )) } }) return { success: true, message: '사전견적이 성공적으로 제출되었습니다.' } } catch (error) { console.error('Failed to submit pre-quote response:', error) return { success: false, error: error instanceof Error ? error.message : '사전견적 제출에 실패했습니다.' } } } // Partners에서 사전견적 참여 의사 결정 (수락/거절) export async function respondToPreQuoteInvitation( biddingCompanyId: number, response: 'accepted' | 'declined' ) { try { await db.update(biddingCompanies) .set({ invitationStatus: response, // accepted 또는 declined respondedAt: new Date(), updatedAt: new Date() }) .where(eq(biddingCompanies.id, biddingCompanyId)) const message = response === 'accepted' ? '사전견적 참여를 수락했습니다.' : '사전견적 참여를 거절했습니다.' return { success: true, message } } catch (error) { console.error('Failed to respond to pre-quote invitation:', error) return { success: false, error: error instanceof Error ? error.message : '응답 처리에 실패했습니다.' } } } // 벤더에서 사전견적 참여 여부 결정 (isPreQuoteSelected, isPreQuoteParticipated 사용) export async function setPreQuoteParticipation( biddingCompanyId: number, isParticipating: boolean ) { try { await db.update(biddingCompanies) .set({ isPreQuoteParticipated: isParticipating, isPreQuoteSelected: isParticipating, respondedAt: new Date(), updatedAt: new Date() }) .where(eq(biddingCompanies.id, biddingCompanyId)) const message = isParticipating ? '사전견적 참여를 확정했습니다. 이제 견적서를 작성하실 수 있습니다.' : '사전견적 참여를 거절했습니다.' return { success: true, message } } catch (error) { console.error('Failed to set pre-quote participation:', error) return { success: false, error: error instanceof Error ? error.message : '참여 의사 처리에 실패했습니다.' } } } // PR 아이템 조회 (입찰에 포함된 품목들) export async function getPrItemsForBidding(biddingId: number) { try { const prItems = await db .select({ id: prItemsForBidding.id, itemNumber: prItemsForBidding.itemNumber, prNumber: prItemsForBidding.prNumber, itemInfo: prItemsForBidding.itemInfo, materialDescription: prItemsForBidding.materialDescription, quantity: prItemsForBidding.quantity, quantityUnit: prItemsForBidding.quantityUnit, totalWeight: prItemsForBidding.totalWeight, weightUnit: prItemsForBidding.weightUnit, currency: prItemsForBidding.currency, requestedDeliveryDate: prItemsForBidding.requestedDeliveryDate, hasSpecDocument: prItemsForBidding.hasSpecDocument }) .from(prItemsForBidding) .where(eq(prItemsForBidding.biddingId, biddingId)) return prItems } catch (error) { console.error('Failed to get PR items for bidding:', error) return [] } } // SPEC 문서 조회 (PR 아이템에 연결된 문서들) export async function getSpecDocumentsForPrItem(prItemId: number) { try { const specDocs = await db .select({ id: biddingDocuments.id, fileName: biddingDocuments.fileName, originalFileName: biddingDocuments.originalFileName, fileSize: biddingDocuments.fileSize, filePath: biddingDocuments.filePath, title: biddingDocuments.title, description: biddingDocuments.description, uploadedAt: biddingDocuments.uploadedAt }) .from(biddingDocuments) .where( and( eq(biddingDocuments.prItemId, prItemId), eq(biddingDocuments.documentType, 'spec_document') ) ) return specDocs } catch (error) { console.error('Failed to get spec documents for PR item:', error) return [] } } // 사전견적 임시저장 export async function savePreQuoteDraft( biddingCompanyId: number, responseData: { prItemQuotations?: PrItemQuotation[] paymentTermsResponse?: string taxConditionsResponse?: string incotermsResponse?: string proposedContractDeliveryDate?: string proposedShippingPort?: string proposedDestinationPort?: string priceAdjustmentResponse?: boolean isInitialResponse?: boolean sparePartResponse?: string additionalProposals?: string priceAdjustmentForm?: any }, userId: string ) { try { let totalAmount = 0 await db.transaction(async (tx) => { // 품목별 견적 정보 저장 if (responseData.prItemQuotations && responseData.prItemQuotations.length > 0) { // 기존 사전견적 품목 삭제 (임시저장 시 덮어쓰기) await tx.delete(companyPrItemBids) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.isPreQuote, true) ) ) // 새로운 품목별 견적 저장 for (const item of responseData.prItemQuotations) { await tx.insert(companyPrItemBids) .values({ biddingCompanyId, prItemId: item.prItemId, bidUnitPrice: item.bidUnitPrice.toString(), bidAmount: item.bidAmount.toString(), proposedDeliveryDate: item.proposedDeliveryDate || null, technicalSpecification: item.technicalSpecification || null, currency: 'KRW', isPreQuote: true, // 사전견적 표시 submittedAt: new Date(), createdAt: new Date(), updatedAt: new Date() }) } // 총 금액 계산 totalAmount = responseData.prItemQuotations.reduce((sum, item) => sum + item.bidAmount, 0) // biddingCompanies에 총 금액 임시 저장 (status는 변경하지 않음) await tx.update(biddingCompanies) .set({ preQuoteAmount: totalAmount.toString(), updatedAt: new Date() }) .where(eq(biddingCompanies.id, biddingCompanyId)) } // company_condition_responses 업데이트 (임시저장) const conditionResult = await tx.update(companyConditionResponses) .set({ paymentTermsResponse: responseData.paymentTermsResponse || null, taxConditionsResponse: responseData.taxConditionsResponse || null, incotermsResponse: responseData.incotermsResponse || null, proposedContractDeliveryDate: responseData.proposedContractDeliveryDate || null, proposedShippingPort: responseData.proposedShippingPort || null, proposedDestinationPort: responseData.proposedDestinationPort || null, priceAdjustmentResponse: responseData.priceAdjustmentResponse || null, isInitialResponse: responseData.isInitialResponse || null, sparePartResponse: responseData.sparePartResponse || null, additionalProposals: responseData.additionalProposals || null, updatedAt: new Date() }) .where(eq(companyConditionResponses.biddingCompanyId, biddingCompanyId)) .returning() // 연동제 정보 저장 (연동제 적용이 true이고 연동제 정보가 있는 경우) if (responseData.priceAdjustmentResponse && responseData.priceAdjustmentForm && conditionResult.length > 0) { const companyConditionResponseId = conditionResult[0].id const priceAdjustmentData = { companyConditionResponsesId: companyConditionResponseId, itemName: responseData.priceAdjustmentForm.itemName, adjustmentReflectionPoint: responseData.priceAdjustmentForm.adjustmentReflectionPoint, majorApplicableRawMaterial: responseData.priceAdjustmentForm.majorApplicableRawMaterial, adjustmentFormula: responseData.priceAdjustmentForm.adjustmentFormula, rawMaterialPriceIndex: responseData.priceAdjustmentForm.rawMaterialPriceIndex, referenceDate: responseData.priceAdjustmentForm.referenceDate as string || null, comparisonDate: responseData.priceAdjustmentForm.comparisonDate as string || null, adjustmentRatio: responseData.priceAdjustmentForm.adjustmentRatio || null, notes: responseData.priceAdjustmentForm.notes, adjustmentConditions: responseData.priceAdjustmentForm.adjustmentConditions, majorNonApplicableRawMaterial: responseData.priceAdjustmentForm.majorNonApplicableRawMaterial, adjustmentPeriod: responseData.priceAdjustmentForm.adjustmentPeriod, contractorWriter: responseData.priceAdjustmentForm.contractorWriter, adjustmentDate: responseData.priceAdjustmentForm.adjustmentDate as string || null, nonApplicableReason: responseData.priceAdjustmentForm.nonApplicableReason, } as any // 기존 연동제 정보가 있는지 확인 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) } } }) return { success: true, message: '임시저장이 완료되었습니다.', totalAmount } } catch (error) { console.error('Failed to save pre-quote draft:', error) return { success: false, error: error instanceof Error ? error.message : '임시저장에 실패했습니다.' } } } // 견적 문서 업로드 export async function uploadPreQuoteDocument( biddingId: number, companyId: number, file: File, userId: string ) { try { const userName = await getUserNameById(userId) // 파일 저장 const saveResult = await saveFile({ file, directory: `bidding/${biddingId}/quotations`, originalName: file.name, userId }) if (!saveResult.success) { return { success: false, error: saveResult.error || '파일 저장에 실패했습니다.' } } // 데이터베이스에 문서 정보 저장 const result = await db.insert(biddingDocuments) .values({ biddingId, companyId, documentType: 'other', // 견적서 타입 fileName: saveResult.fileName!, originalFileName: file.name, fileSize: file.size, mimeType: file.type, filePath: saveResult.publicPath!, // publicPath 사용 (웹 접근 가능한 경로) title: `견적서 - ${file.name}`, description: '협력업체 제출 견적서', isPublic: false, isRequired: false, uploadedBy: userName, uploadedAt: new Date() }) .returning() return { success: true, message: '견적서가 성공적으로 업로드되었습니다.', documentId: result[0].id } } catch (error) { console.error('Failed to upload pre-quote document:', error) return { success: false, error: error instanceof Error ? error.message : '견적서 업로드에 실패했습니다.' } } } // 업로드된 견적 문서 목록 조회 export async function getPreQuoteDocuments(biddingId: number, companyId: number) { try { const documents = await db .select({ id: biddingDocuments.id, fileName: biddingDocuments.fileName, originalFileName: biddingDocuments.originalFileName, fileSize: biddingDocuments.fileSize, filePath: biddingDocuments.filePath, title: biddingDocuments.title, description: biddingDocuments.description, uploadedAt: biddingDocuments.uploadedAt, uploadedBy: biddingDocuments.uploadedBy }) .from(biddingDocuments) .where( and( eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.companyId, companyId), ) ) return documents } catch (error) { console.error('Failed to get pre-quote documents:', error) return [] } } // 저장된 품목별 견적 조회 (임시저장/기존 데이터 불러오기용) export async function getSavedPrItemQuotations(biddingCompanyId: number) { try { const savedQuotations = await db .select({ prItemId: companyPrItemBids.prItemId, bidUnitPrice: companyPrItemBids.bidUnitPrice, bidAmount: companyPrItemBids.bidAmount, proposedDeliveryDate: companyPrItemBids.proposedDeliveryDate, technicalSpecification: companyPrItemBids.technicalSpecification, currency: companyPrItemBids.currency }) .from(companyPrItemBids) .where( and( eq(companyPrItemBids.biddingCompanyId, biddingCompanyId), eq(companyPrItemBids.isPreQuote, true) ) ) // Decimal 타입을 number로 변환 return savedQuotations.map(item => ({ prItemId: item.prItemId, bidUnitPrice: parseFloat(item.bidUnitPrice || '0'), bidAmount: parseFloat(item.bidAmount || '0'), proposedDeliveryDate: item.proposedDeliveryDate, technicalSpecification: item.technicalSpecification, currency: item.currency })) } catch (error) { console.error('Failed to get saved PR item quotations:', error) return [] } } // 견적 문서 정보 조회 (다운로드용) export async function getPreQuoteDocumentForDownload( documentId: number, biddingId: number, companyId: number ) { try { const document = await db .select({ fileName: biddingDocuments.fileName, originalFileName: biddingDocuments.originalFileName, filePath: biddingDocuments.filePath }) .from(biddingDocuments) .where( and( eq(biddingDocuments.id, documentId), eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.companyId, companyId), eq(biddingDocuments.documentType, 'other') ) ) .limit(1) if (document.length === 0) { return { success: false, error: '문서를 찾을 수 없습니다.' } } return { success: true, document: document[0] } } catch (error) { console.error('Failed to get pre-quote document:', error) return { success: false, error: '문서 정보 조회에 실패했습니다.' } } } // 견적 문서 삭제 export async function deletePreQuoteDocument( documentId: number, biddingId: number, companyId: number, userId: string ) { try { // 문서 존재 여부 및 권한 확인 const document = await db .select({ id: biddingDocuments.id, fileName: biddingDocuments.fileName, filePath: biddingDocuments.filePath, uploadedBy: biddingDocuments.uploadedBy }) .from(biddingDocuments) .where( and( eq(biddingDocuments.id, documentId), eq(biddingDocuments.biddingId, biddingId), eq(biddingDocuments.companyId, companyId), eq(biddingDocuments.documentType, 'other') ) ) .limit(1) if (document.length === 0) { return { success: false, error: '문서를 찾을 수 없습니다.' } } const doc = document[0] // 데이터베이스에서 문서 정보 삭제 await db .delete(biddingDocuments) .where(eq(biddingDocuments.id, documentId)) return { success: true, message: '문서가 성공적으로 삭제되었습니다.' } } catch (error) { console.error('Failed to delete pre-quote document:', error) return { success: false, error: '문서 삭제에 실패했습니다.' } } } // 기본계약 발송 (서버 액션) export async function sendBiddingBasicContracts( biddingId: number, vendorData: Array<{ vendorId: number vendorName: string vendorCode?: string vendorCountry?: string selectedMainEmail: string additionalEmails: string[] customEmails?: Array<{ email: string; name?: string }> contractRequirements: { ndaYn: boolean generalGtcYn: boolean projectGtcYn: boolean agreementYn: boolean } biddingCompanyId: number biddingId: number hasExistingContracts?: boolean }>, generatedPdfs: Array<{ key: string buffer: number[] fileName: string }>, message?: string ) { try { console.log("sendBiddingBasicContracts called with:", { biddingId, vendorData: vendorData.map(v => ({ vendorId: v.vendorId, biddingCompanyId: v.biddingCompanyId, biddingId: v.biddingId })) }); // 현재 사용자 정보 조회 (임시로 첫 번째 사용자 사용) const [currentUser] = await db.select().from(users).limit(1) if (!currentUser) { throw new Error("사용자 정보를 찾을 수 없습니다.") } const results = [] const savedContracts = [] // 트랜잭션 시작 const contractsDir = path.join(process.cwd(), `${process.env.NAS_PATH}`, "contracts", "generated"); await mkdir(contractsDir, { recursive: true }); const result = await db.transaction(async (tx) => { // 각 벤더별로 기본계약 생성 및 이메일 발송 for (const vendor of vendorData) { // 기존 계약 확인 (biddingCompanyId 기준) if (vendor.hasExistingContracts) { console.log(`벤더 ${vendor.vendorName}는 이미 계약이 체결되어 있어 건너뜁니다.`) continue } // 벤더 정보 조회 const [vendorInfo] = await tx .select() .from(vendors) .where(eq(vendors.id, vendor.vendorId)) .limit(1) if (!vendorInfo) { console.error(`벤더 정보를 찾을 수 없습니다: ${vendor.vendorId}`) continue } // biddingCompany 정보 조회 (biddingCompanyId를 직접 사용) console.log(`Looking for biddingCompany with id=${vendor.biddingCompanyId}`) let [biddingCompanyInfo] = await tx .select() .from(biddingCompanies) .where(eq(biddingCompanies.id, vendor.biddingCompanyId)) .limit(1) console.log(`Found biddingCompanyInfo:`, biddingCompanyInfo) if (!biddingCompanyInfo) { console.error(`입찰 회사 정보를 찾을 수 없습니다: biddingCompanyId=${vendor.biddingCompanyId}`) // fallback: biddingId와 vendorId로 찾기 시도 console.log(`Fallback: Looking for biddingCompany with biddingId=${biddingId}, companyId=${vendor.vendorId}`) const [fallbackCompanyInfo] = await tx .select() .from(biddingCompanies) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.companyId, vendor.vendorId) )) .limit(1) console.log(`Fallback found biddingCompanyInfo:`, fallbackCompanyInfo) if (fallbackCompanyInfo) { console.log(`Using fallback biddingCompanyInfo`) biddingCompanyInfo = fallbackCompanyInfo } else { console.log(`Available biddingCompanies for biddingId=${biddingId}:`, await tx.select().from(biddingCompanies).where(eq(biddingCompanies.biddingId, biddingId)).limit(10)) continue } } // 계약 요구사항에 따라 계약서 생성 const contractTypes: Array<{ type: string; templateName: string }> = [] if (vendor.contractRequirements.ndaYn) contractTypes.push({ type: 'NDA', templateName: '비밀' }) if (vendor.contractRequirements.generalGtcYn) contractTypes.push({ type: 'General_GTC', templateName: 'General GTC' }) if (vendor.contractRequirements.projectGtcYn) contractTypes.push({ type: 'Project_GTC', templateName: '기술' }) if (vendor.contractRequirements.agreementYn) contractTypes.push({ type: '기술자료', templateName: '기술자료' }) console.log("contractTypes", contractTypes) for (const contractType of contractTypes) { // PDF 데이터 찾기 (include를 사용하여 유연하게 찾기) console.log("generatedPdfs", generatedPdfs.map(pdf => pdf.key)) const pdfData = generatedPdfs.find((pdf: any) => pdf.key.includes(`${vendor.vendorId}_`) && pdf.key.includes(`_${contractType.templateName}`) ) console.log("pdfData", pdfData, "for contractType", contractType) if (!pdfData) { console.error(`PDF 데이터를 찾을 수 없습니다: vendorId=${vendor.vendorId}, templateName=${contractType.templateName}`) continue } // 파일 저장 (rfq-last 방식) const fileName = `${contractType.type}_${vendor.vendorCode || vendor.vendorId}_${vendor.biddingCompanyId}_${Date.now()}.pdf` const filePath = path.join(contractsDir, fileName); await writeFile(filePath, Buffer.from(pdfData.buffer)); // 템플릿 정보 조회 (rfq-last 방식) const [template] = await db .select() .from(basicContractTemplates) .where( and( ilike(basicContractTemplates.templateName, `%${contractType.templateName}%`), eq(basicContractTemplates.status, "ACTIVE") ) ) .limit(1); console.log("템플릿", contractType.templateName, template); // 기존 계약이 있는지 확인 (rfq-last 방식) const [existingContract] = await tx .select() .from(basicContract) .where( and( eq(basicContract.templateId, template?.id), eq(basicContract.vendorId, vendor.vendorId), eq(basicContract.biddingCompanyId, biddingCompanyInfo.id) ) ) .limit(1); let contractRecord; if (existingContract) { // 기존 계약이 있으면 업데이트 [contractRecord] = await tx .update(basicContract) .set({ requestedBy: currentUser.id, status: "PENDING", // 재발송 상태 fileName: fileName, filePath: `/contracts/generated/${fileName}`, deadline: new Date(Date.now() + 10 * 24 * 60 * 60 * 1000), updatedAt: new Date(), }) .where(eq(basicContract.id, existingContract.id)) .returning(); console.log("기존 계약 업데이트:", contractRecord.id); } else { // 새 계약 생성 [contractRecord] = await tx .insert(basicContract) .values({ templateId: template?.id || null, vendorId: vendor.vendorId, biddingCompanyId: biddingCompanyInfo.id, rfqCompanyId: null, generalContractId: null, requestedBy: currentUser.id, status: 'PENDING', fileName: fileName, filePath: `/contracts/generated/${fileName}`, deadline: new Date(Date.now() + 10 * 24 * 60 * 60 * 1000), // 10일 후 createdAt: new Date(), updatedAt: new Date(), }) .returning(); console.log("새 계약 생성:", contractRecord.id); } results.push({ vendorId: vendor.vendorId, vendorName: vendor.vendorName, contractId: contractRecord.id, contractType: contractType.type, fileName: fileName, filePath: `/contracts/generated/${fileName}`, }) // savedContracts에 추가 (rfq-last 방식) // savedContracts.push({ // vendorId: vendor.vendorId, // vendorName: vendor.vendorName, // templateName: contractType.templateName, // contractId: contractRecord.id, // fileName: fileName, // isUpdated: !!existingContract, // 업데이트 여부 표시 // }) } // 이메일 발송 (선택사항) if (vendor.selectedMainEmail) { try { await sendEmail({ to: vendor.selectedMainEmail, template: 'basic-contract-notification', context: { vendorName: vendor.vendorName, biddingId: biddingId, contractCount: contractTypes.length, deadline: new Date(Date.now() + 10 * 24 * 60 * 60 * 1000).toLocaleDateString('ko-KR'), loginUrl: `${process.env.NEXT_PUBLIC_APP_URL}/partners/bid/${biddingId}`, message: message || '', currentYear: new Date().getFullYear(), language: 'ko' } }) } catch (emailError) { console.error(`이메일 발송 실패 (${vendor.selectedMainEmail}):`, emailError) // 이메일 발송 실패해도 계약 생성은 유지 } } } return { success: true, message: `${results.length}개의 기본계약이 생성되었습니다.`, results, savedContracts, totalContracts: savedContracts.length } }) return result } catch (error) { console.error('기본계약 발송 실패:', error) throw new Error( error instanceof Error ? error.message : '기본계약 발송 중 오류가 발생했습니다.' ) } } // 기존 기본계약 조회 (서버 액션) export async function getExistingBasicContractsForBidding(biddingId: number) { try { // 해당 biddingId에 속한 biddingCompany들의 기존 기본계약 조회 const existingContracts = await db .select({ id: basicContract.id, vendorId: basicContract.vendorId, biddingCompanyId: basicContract.biddingCompanyId, biddingId: biddingCompanies.biddingId, templateId: basicContract.templateId, status: basicContract.status, createdAt: basicContract.createdAt, }) .from(basicContract) .leftJoin(biddingCompanies, eq(basicContract.biddingCompanyId, biddingCompanies.id)) .where( and( eq(biddingCompanies.biddingId, biddingId), ) ) return { success: true, contracts: existingContracts } } catch (error) { console.error('기존 계약 조회 실패:', error) return { success: false, error: '기존 계약 조회에 실패했습니다.' } } } // 선정된 업체들 조회 (서버 액션) export async function getSelectedVendorsForBidding(biddingId: number) { try { const selectedCompanies = await db .select({ id: biddingCompanies.id, companyId: biddingCompanies.companyId, companyName: vendors.vendorName, companyCode: vendors.vendorCode, companyCountry: vendors.country, contactPerson: biddingCompanies.contactPerson, contactEmail: biddingCompanies.contactEmail, biddingId: biddingCompanies.biddingId, }) .from(biddingCompanies) .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) .where(and( eq(biddingCompanies.biddingId, biddingId), eq(biddingCompanies.isPreQuoteSelected, true) )) return { success: true, vendors: selectedCompanies.map(company => ({ vendorId: company.companyId, // 실제 vendor ID vendorName: company.companyName || '', vendorCode: company.companyCode, vendorCountry: company.companyCountry || '대한민국', contactPerson: company.contactPerson, contactEmail: company.contactEmail, biddingCompanyId: company.id, // biddingCompany ID biddingId: company.biddingId, ndaYn: true, // 모든 계약 타입을 활성화 (필요에 따라 조정) generalGtcYn: true, projectGtcYn: true, agreementYn: true })) } } catch (error) { console.error('선정된 업체 조회 실패:', error) return { success: false, error: '선정된 업체 조회에 실패했습니다.', vendors: [] } } }