diff options
Diffstat (limited to 'lib/bidding/service.ts')
| -rw-r--r-- | lib/bidding/service.ts | 483 |
1 files changed, 74 insertions, 409 deletions
diff --git a/lib/bidding/service.ts b/lib/bidding/service.ts index 521f4c33..489268c6 100644 --- a/lib/bidding/service.ts +++ b/lib/bidding/service.ts @@ -33,7 +33,8 @@ import { like, notInArray, inArray, - isNull + isNull, + isNotNull } from 'drizzle-orm' import { revalidatePath } from 'next/cache' import { filterColumns } from '@/lib/filter-columns' @@ -390,429 +391,36 @@ export async function getBiddings(input: GetBiddingsSchema) { id: biddings.id, biddingNumber: biddings.biddingNumber, originalBiddingNumber: biddings.originalBiddingNumber, - revision: biddings.revision, projectName: biddings.projectName, - itemName: biddings.itemName, title: biddings.title, - description: biddings.description, - biddingSourceType: biddings.biddingSourceType, - isUrgent: biddings.isUrgent, // 계약 정보 contractType: biddings.contractType, biddingType: biddings.biddingType, - awardCount: biddings.awardCount, - contractStartDate: biddings.contractStartDate, - contractEndDate: biddings.contractEndDate, // 일정 관리 - preQuoteDate: biddings.preQuoteDate, biddingRegistrationDate: biddings.biddingRegistrationDate, submissionStartDate: biddings.submissionStartDate, submissionEndDate: biddings.submissionEndDate, - evaluationDate: biddings.evaluationDate, // 회의 및 문서 hasSpecificationMeeting: biddings.hasSpecificationMeeting, - hasPrDocument: biddings.hasPrDocument, - prNumber: biddings.prNumber, // 가격 정보 currency: biddings.currency, budget: biddings.budget, targetPrice: biddings.targetPrice, - finalBidPrice: biddings.finalBidPrice, // 상태 및 담당자 status: biddings.status, - isPublic: biddings.isPublic, - purchasingOrganization: biddings.purchasingOrganization, - bidPicId: biddings.bidPicId, bidPicName: biddings.bidPicName, - supplyPicId: biddings.supplyPicId, - supplyPicName: biddings.supplyPicName, // 메타 정보 remarks: biddings.remarks, - createdBy: biddings.createdBy, - createdAt: biddings.createdAt, updatedAt: biddings.updatedAt, updatedBy: biddings.updatedBy, - - // 사양설명회 상세 정보 - hasSpecificationMeetingDetails: sql<boolean>`${specificationMeetings.id} IS NOT NULL`.as('has_specification_meeting_details'), - meetingDate: specificationMeetings.meetingDate, - meetingLocation: specificationMeetings.location, - meetingContactPerson: specificationMeetings.contactPerson, - meetingIsRequired: specificationMeetings.isRequired, - - // PR 문서 집계 - prDocumentCount: sql<number>` - COALESCE(( - SELECT count(*) - FROM pr_documents - WHERE bidding_id = ${biddings.id} - ), 0) - `.as('pr_document_count'), - - prDocumentNames: sql<string[]>` - ( - SELECT array_agg(document_name ORDER BY registered_at DESC) - FROM pr_documents - WHERE bidding_id = ${biddings.id} - LIMIT 5 - ) - `.as('pr_document_names'), - - // 참여 현황 집계 (전체) - participantExpected: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - ), 0) - `.as('participant_expected'), - - // === 사전견적 참여 현황 === - preQuotePending: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status IN ('pending', 'pre_quote_sent') - ), 0) - `.as('pre_quote_pending'), - - preQuoteAccepted: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'pre_quote_accepted' - ), 0) - `.as('pre_quote_accepted'), - - preQuoteDeclined: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'pre_quote_declined' - ), 0) - `.as('pre_quote_declined'), - - preQuoteSubmitted: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'pre_quote_submitted' - ), 0) - `.as('pre_quote_submitted'), - - // === 입찰 참여 현황 === - biddingPending: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_sent' - ), 0) - `.as('bidding_pending'), - - biddingAccepted: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_accepted' - ), 0) - `.as('bidding_accepted'), - - biddingDeclined: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_declined' - ), 0) - `.as('bidding_declined'), - - biddingCancelled: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_cancelled' - ), 0) - `.as('bidding_cancelled'), - - biddingSubmitted: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_submitted' - ), 0) - `.as('bidding_submitted'), - - // === 호환성을 위한 기존 컬럼 (사전견적 기준) === - participantParticipated: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'pre_quote_submitted' - ), 0) - `.as('participant_participated'), - - participantDeclined: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status IN ('pre_quote_declined', 'bidding_declined') - ), 0) - `.as('participant_declined'), - - participantPending: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status IN ('pending', 'pre_quote_sent', 'bidding_sent') - ), 0) - `.as('participant_pending'), - - participantAccepted: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status IN ('pre_quote_accepted', 'bidding_accepted') - ), 0) - `.as('participant_accepted'), - - // 참여율 계산 (입찰 기준 - 응찰 완료 / 전체) - participationRate: sql<number>` - CASE - WHEN ( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - ) > 0 - THEN ROUND( - ( - SELECT count(*)::decimal - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND invitation_status = 'bidding_submitted' - ) / ( - SELECT count(*)::decimal - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - ) * 100, 1 - ) - ELSE 0 - END - `.as('participation_rate'), - - // 견적 금액 통계 - avgPreQuoteAmount: sql<number>` - ( - SELECT AVG(pre_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND pre_quote_amount IS NOT NULL - ) - `.as('avg_pre_quote_amount'), - - minPreQuoteAmount: sql<number>` - ( - SELECT MIN(pre_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND pre_quote_amount IS NOT NULL - ) - `.as('min_pre_quote_amount'), - - maxPreQuoteAmount: sql<number>` - ( - SELECT MAX(pre_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND pre_quote_amount IS NOT NULL - ) - `.as('max_pre_quote_amount'), - - avgFinalQuoteAmount: sql<number>` - ( - SELECT AVG(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) - `.as('avg_final_quote_amount'), - - minFinalQuoteAmount: sql<number>` - ( - SELECT MIN(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) - `.as('min_final_quote_amount'), - - maxFinalQuoteAmount: sql<number>` - ( - SELECT MAX(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) - `.as('max_final_quote_amount'), - - // 선정 및 낙찰 정보 - selectedForFinalBidCount: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND is_pre_quote_selected = true - ), 0) - `.as('selected_for_final_bid_count'), - - winnerCount: sql<number>` - COALESCE(( - SELECT count(*) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND is_winner = true - ), 0) - `.as('winner_count'), - - winnerCompanyNames: sql<string[]>` - ( - SELECT array_agg(v.vendor_name ORDER BY v.vendor_name) - FROM bidding_companies bc - JOIN vendors v ON bc.company_id = v.id - WHERE bc.bidding_id = ${biddings.id} - AND bc.is_winner = true - ) - `.as('winner_company_names'), - - // 일정 상태 계산 - submissionStatus: sql<string>` - CASE - WHEN ${biddings.submissionStartDate} IS NULL OR ${biddings.submissionEndDate} IS NULL - THEN 'not_scheduled' - WHEN NOW() < ${biddings.submissionStartDate} - THEN 'scheduled' - WHEN NOW() BETWEEN ${biddings.submissionStartDate} AND ${biddings.submissionEndDate} - THEN 'active' - WHEN NOW() > ${biddings.submissionEndDate} - THEN 'closed' - ELSE 'unknown' - END - `.as('submission_status'), - - // 마감까지 남은 일수 - daysUntilDeadline: sql<number>` - CASE - WHEN ${biddings.submissionEndDate} IS NOT NULL - AND NOW() < ${biddings.submissionEndDate} - THEN EXTRACT(DAYS FROM (${biddings.submissionEndDate} - NOW()))::integer - ELSE NULL - END - `.as('days_until_deadline'), - - // 시작까지 남은 일수 - daysUntilStart: sql<number>` - CASE - WHEN ${biddings.submissionStartDate} IS NOT NULL - AND NOW() < ${biddings.submissionStartDate} - THEN EXTRACT(DAYS FROM (${biddings.submissionStartDate} - NOW()))::integer - ELSE NULL - END - `.as('days_until_start'), - - // 예산 대비 최저 견적 비율 - budgetEfficiencyRate: sql<number>` - CASE - WHEN ${biddings.budget} IS NOT NULL AND ${biddings.budget} > 0 - AND ( - SELECT MIN(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) IS NOT NULL - THEN ROUND( - ( - SELECT MIN(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) / ${biddings.budget} * 100, 1 - ) - ELSE NULL - END - `.as('budget_efficiency_rate'), - - // 내정가 대비 최저 견적 비율 - targetPriceEfficiencyRate: sql<number>` - CASE - WHEN ${biddings.targetPrice} IS NOT NULL AND ${biddings.targetPrice} > 0 - AND ( - SELECT MIN(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) IS NOT NULL - THEN ROUND( - ( - SELECT MIN(final_quote_amount) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - AND final_quote_amount IS NOT NULL - ) / ${biddings.targetPrice} * 100, 1 - ) - ELSE NULL - END - `.as('target_price_efficiency_rate'), - - // 입찰 진행 단계 점수 (0-100) - progressScore: sql<number>` - CASE ${biddings.status} - WHEN 'bidding_generated' THEN 10 - WHEN 'request_for_quotation' THEN 20 - WHEN 'received_quotation' THEN 40 - WHEN 'set_target_price' THEN 60 - WHEN 'bidding_opened' THEN 70 - WHEN 'bidding_closed' THEN 80 - WHEN 'evaluation_of_bidding' THEN 90 - WHEN 'vendor_selected' THEN 100 - WHEN 'bidding_disposal' THEN 0 - ELSE 0 - END - `.as('progress_score'), - - // 마지막 활동일 (가장 최근 업체 응답일) - lastActivityDate: sql<Date>` - GREATEST( - ${biddings.updatedAt}, - COALESCE(( - SELECT MAX(updated_at) - FROM bidding_companies - WHERE bidding_id = ${biddings.id} - ), ${biddings.updatedAt}) - ) - `.as('last_activity_date'), }) .from(biddings) - .leftJoin( - specificationMeetings, - sql`${biddings.id} = ${specificationMeetings.biddingId}` - ) .where(finalWhere) .orderBy(...orderByColumns) .limit(input.perPage) @@ -995,8 +603,8 @@ export async function getParticipantCountsForBidding(biddingId: number) { const expected = expectedResult[0]?.count || 0 - // 참여 완료 수 - const participatedResult = await db + // 최종 제출 완료 수 (Strict) + const finalSubmittedResult = await db .select({ count: count() }) .from(biddingCompanies) .where(and( @@ -1004,6 +612,23 @@ export async function getParticipantCountsForBidding(biddingId: number) { eq(biddingCompanies.invitationStatus, 'bidding_submitted') )) + const finalSubmitted = finalSubmittedResult[0]?.count || 0 + + // 참여 완료 수 (Broad: 최종제출 OR (참여수락 AND 견적제출)) + const participatedResult = await db + .select({ count: count() }) + .from(biddingCompanies) + .where(and( + eq(biddingCompanies.biddingId, biddingId), + or( + eq(biddingCompanies.invitationStatus, 'bidding_submitted'), + and( + eq(biddingCompanies.invitationStatus, 'bidding_accepted'), + isNotNull(biddingCompanies.finalQuoteAmount) + ) + ) + )) + const participated = participatedResult[0]?.count || 0 // 거부/취소 수 @@ -1020,7 +645,8 @@ export async function getParticipantCountsForBidding(biddingId: number) { const declined = declinedResult[0]?.count || 0 - // 대기중 수 + // 대기중 수 (Expected - Participated - Declined) + // 또는: Pending OR Sent OR (Accepted AND Quote IS NULL) const pendingResult = await db .select({ count: count() }) .from(biddingCompanies) @@ -1029,7 +655,10 @@ export async function getParticipantCountsForBidding(biddingId: number) { or( eq(biddingCompanies.invitationStatus, 'pending'), eq(biddingCompanies.invitationStatus, 'bidding_sent'), - eq(biddingCompanies.invitationStatus, 'bidding_accepted') + and( + eq(biddingCompanies.invitationStatus, 'bidding_accepted'), + isNull(biddingCompanies.finalQuoteAmount) + ) ) )) @@ -1039,7 +668,8 @@ export async function getParticipantCountsForBidding(biddingId: number) { participantExpected: expected, participantParticipated: participated, participantDeclined: declined, - participantPending: pending + participantPending: pending, + participantFinalSubmitted: finalSubmitted } } catch (error) { console.error('Error in getParticipantCountsForBidding:', error) @@ -1047,7 +677,8 @@ export async function getParticipantCountsForBidding(biddingId: number) { participantExpected: 0, participantParticipated: 0, participantDeclined: 0, - participantPending: 0 + participantPending: 0, + participantFinalSubmitted: 0 } } } @@ -1614,8 +1245,8 @@ export async function updateBidding(input: UpdateBiddingInput, userId: string) { .set(updateData) .where(eq(biddings.id, input.id)) - revalidatePath('/admin/biddings') - revalidatePath(`/admin/biddings/${input.id}`) + revalidatePath('/evcp/bid') + revalidatePath(`/evcp/bid/${input.id}/info`) return { success: true, @@ -1651,7 +1282,7 @@ export async function deleteBidding(id: number) { .delete(biddings) .where(eq(biddings.id, id)) - revalidatePath('/admin/biddings') + revalidatePath('/evcp/bid') return { success: true, @@ -3539,14 +3170,48 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u invitedAt: new Date(), invitationStatus: 'pending' as const, // 초대 대기 상태로 초기화 // 제출 정보는 초기화 - submittedAt: null, - quotationPrice: null, - quotationCurrency: null, - quotationValidityDays: null, - deliveryDate: null, - remarks: null, + finalQuoteAmount: null, + finalQuoteSubmittedAt: null, + isFinalSubmission: false, + isWinner: null, + awardRatio: null, + respondedAt: null, + + // 연락처 정보 복제 + contactPerson: company.contactPerson, + contactEmail: company.contactEmail, + contactPhone: company.contactPhone, + + // 본입찰 대상 선정 여부 복제 (중요: 차수증가 시에도 대상 업체 유지) + isPreQuoteSelected: company.isPreQuoteSelected, + // 본입찰 참여 여부 초기화 (다시 참여해야 함) + isBiddingParticipated: null, + // 본입찰 초대 여부 초기화 (다시 초대해야 함) + isBiddingInvited: false, + + notes: company.notes, })) ) + + // 6-1. 벤더 담당자 복제 (추가) + const existingContacts = await tx + .select() + .from(biddingCompaniesContacts) + .where(eq(biddingCompaniesContacts.biddingId, biddingId)) + + if (existingContacts.length > 0) { + await tx + .insert(biddingCompaniesContacts) + .values( + existingContacts.map((contact) => ({ + biddingId: newBidding.id, + vendorId: contact.vendorId, + contactName: contact.contactName, + contactEmail: contact.contactEmail, + contactNumber: contact.contactNumber, + })) + ) + } } // 7. 사양설명회 정보 복제 (있는 경우) |