diff options
Diffstat (limited to 'lib/bidding/service.ts')
| -rw-r--r-- | lib/bidding/service.ts | 2841 |
1 files changed, 2648 insertions, 193 deletions
diff --git a/lib/bidding/service.ts b/lib/bidding/service.ts index 5ab18ef1..80e4850f 100644 --- a/lib/bidding/service.ts +++ b/lib/bidding/service.ts @@ -3,7 +3,6 @@ import db from '@/db/db' import { biddings, - biddingListView, biddingNoticeTemplate, projects, biddingDocuments, @@ -14,7 +13,10 @@ import { users, basicContractTemplates, vendorsWithTypesView, - biddingCompanies + biddingCompanies, + biddingCompaniesContacts, + vendorContacts, + vendors } from '@/db/schema' import { eq, @@ -69,13 +71,40 @@ async function getUserNameById(userId: string): Promise<string> { } -export async function getBiddingNoticeTemplate() { +export async function getBiddingNoticeTemplates() { try { const result = await db .select() .from(biddingNoticeTemplate) - .where(eq(biddingNoticeTemplate.type, 'standard')) - .limit(1) + .where(eq(biddingNoticeTemplate.isTemplate, true)) + .orderBy(desc(biddingNoticeTemplate.updatedAt)) + + // 타입별로 그룹화하여 반환 + const templates = result.reduce((acc, template) => { + acc[template.type] = template + return acc + }, {} as Record<string, typeof result[0]>) + + return templates + } catch (error) { + console.error('Failed to get bidding notice templates:', error) + throw new Error('입찰공고문 템플릿을 불러오는데 실패했습니다.') + } +} + +export async function getBiddingNoticeTemplate(type?: string) { + try { + let query = db + .select() + .from(biddingNoticeTemplate) + .where(eq(biddingNoticeTemplate.isTemplate, true)) + .orderBy(desc(biddingNoticeTemplate.updatedAt)) + + if (type) { + query = query.where(eq(biddingNoticeTemplate.type, type)) + } + + const result = await query.limit(1) return result[0] || null } catch (error) { @@ -84,18 +113,41 @@ export async function getBiddingNoticeTemplate() { } } -export async function saveBiddingNoticeTemplate(formData: { +export async function getBiddingNotice(biddingId: number) { + try { + const result = await db + .select({ + id: biddingNoticeTemplate.id, + biddingId: biddingNoticeTemplate.biddingId, + title: biddingNoticeTemplate.title, + content: biddingNoticeTemplate.content, + isTemplate: biddingNoticeTemplate.isTemplate, + createdAt: biddingNoticeTemplate.createdAt, + updatedAt: biddingNoticeTemplate.updatedAt, + }) + .from(biddingNoticeTemplate) + .where(eq(biddingNoticeTemplate.biddingId, biddingId)) + .limit(1) + + return result[0] || null + } catch (error) { + console.error('Failed to get bidding notice:', error) + throw new Error('입찰공고문을 불러오는데 실패했습니다.') + } +} + +export async function saveBiddingNotice(biddingId: number, formData: { title: string content: string }) { try { const { title, content } = formData - // 기존 템플릿 확인 + // 기존 입찰공고 확인 const existing = await db .select() .from(biddingNoticeTemplate) - .where(eq(biddingNoticeTemplate.type, 'standard')) + .where(eq(biddingNoticeTemplate.biddingId, biddingId)) .limit(1) if (existing.length > 0) { @@ -107,13 +159,62 @@ export async function saveBiddingNoticeTemplate(formData: { content, updatedAt: new Date(), }) - .where(eq(biddingNoticeTemplate.type, 'standard')) + .where(eq(biddingNoticeTemplate.biddingId, biddingId)) } else { // 새로 생성 await db.insert(biddingNoticeTemplate).values({ - type: 'standard', + biddingId, title, content, + isTemplate: false, + }) + } + + return { success: true, message: '입찰공고문이 저장되었습니다.' } + } catch (error) { + console.error('Failed to save bidding notice:', error) + throw new Error('입찰공고문 저장에 실패했습니다.') + } +} + +export async function saveBiddingNoticeTemplate(formData: { + title: string + content: string + type: string +}) { + try { + const { title, content, type } = formData + + // 기존 동일 타입의 템플릿 확인 + const existing = await db + .select() + .from(biddingNoticeTemplate) + .where(and( + eq(biddingNoticeTemplate.isTemplate, true), + eq(biddingNoticeTemplate.type, type) + )) + .limit(1) + + if (existing.length > 0) { + // 업데이트 + await db + .update(biddingNoticeTemplate) + .set({ + title, + content, + updatedAt: new Date(), + }) + .where(and( + eq(biddingNoticeTemplate.isTemplate, true), + eq(biddingNoticeTemplate.type, type) + )) + } else { + // 새로 생성 + await db.insert(biddingNoticeTemplate).values({ + title, + content, + type, + isTemplate: true, }) } @@ -137,7 +238,7 @@ export async function getBiddings(input: GetBiddingsSchema) { let advancedWhere: SQL<unknown> | undefined = undefined if (input.filters && input.filters.length > 0) { advancedWhere = filterColumns({ - table: biddingListView, + table: biddings, filters: input.filters, joinOperator: input.joinOperator || 'and', }) @@ -147,72 +248,83 @@ export async function getBiddings(input: GetBiddingsSchema) { const basicConditions: SQL<unknown>[] = [] if (input.biddingNumber) { - basicConditions.push(ilike(biddingListView.biddingNumber, `%${input.biddingNumber}%`)) + basicConditions.push(ilike(biddings.biddingNumber, `%${input.biddingNumber}%`)) } if (input.status && input.status.length > 0) { basicConditions.push( - or(...input.status.map(status => eq(biddingListView.status, status)))! + or(...input.status.map(status => eq(biddings.status, status)))! ) } if (input.biddingType && input.biddingType.length > 0) { basicConditions.push( - or(...input.biddingType.map(type => eq(biddingListView.biddingType, type)))! + or(...input.biddingType.map(type => eq(biddings.biddingType, type)))! ) } if (input.contractType && input.contractType.length > 0) { basicConditions.push( - or(...input.contractType.map(type => eq(biddingListView.contractType, type)))! + or(...input.contractType.map(type => eq(biddings.contractType, type)))! ) } + if (input.purchasingOrganization) { + basicConditions.push(ilike(biddings.purchasingOrganization, `%${input.purchasingOrganization}%`)) + } + + // 담당자 필터 (bidPicId 또는 supplyPicId로 검색) if (input.managerName) { - basicConditions.push(ilike(biddingListView.managerName, `%${input.managerName}%`)) + // managerName으로 검색 시 bidPic 또는 supplyPic의 이름으로 검색 + basicConditions.push( + or( + ilike(biddings.bidPicName, `%${input.managerName}%`), + ilike(biddings.supplyPicName, `%${input.managerName}%`) + )! + ) } // 날짜 필터들 if (input.preQuoteDateFrom) { - basicConditions.push(gte(biddingListView.preQuoteDate, input.preQuoteDateFrom)) + basicConditions.push(gte(biddings.preQuoteDate, input.preQuoteDateFrom)) } if (input.preQuoteDateTo) { - basicConditions.push(lte(biddingListView.preQuoteDate, input.preQuoteDateTo)) + basicConditions.push(lte(biddings.preQuoteDate, input.preQuoteDateTo)) } if (input.submissionDateFrom) { - basicConditions.push(gte(biddingListView.submissionStartDate, new Date(input.submissionDateFrom))) + basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom))) } if (input.submissionDateTo) { - basicConditions.push(lte(biddingListView.submissionEndDate, new Date(input.submissionDateTo))) + basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo))) } if (input.createdAtFrom) { - basicConditions.push(gte(biddingListView.createdAt, new Date(input.createdAtFrom))) + basicConditions.push(gte(biddings.createdAt, new Date(input.createdAtFrom))) } if (input.createdAtTo) { - basicConditions.push(lte(biddingListView.createdAt, new Date(input.createdAtTo))) + basicConditions.push(lte(biddings.createdAt, new Date(input.createdAtTo))) } // 가격 범위 필터 if (input.budgetMin) { - basicConditions.push(gte(biddingListView.budget, input.budgetMin)) + basicConditions.push(gte(biddings.budget, input.budgetMin)) } if (input.budgetMax) { - basicConditions.push(lte(biddingListView.budget, input.budgetMax)) + basicConditions.push(lte(biddings.budget, input.budgetMax)) } // Boolean 필터 if (input.hasSpecificationMeeting === "true") { - basicConditions.push(eq(biddingListView.hasSpecificationMeeting, true)) + basicConditions.push(eq(biddings.hasSpecificationMeeting, true)) } else if (input.hasSpecificationMeeting === "false") { - basicConditions.push(eq(biddingListView.hasSpecificationMeeting, false)) + basicConditions.push(eq(biddings.hasSpecificationMeeting, false)) } if (input.hasPrDocument === "true") { - basicConditions.push(eq(biddingListView.hasPrDocument, true)) + basicConditions.push(eq(biddings.hasPrDocument, true)) } else if (input.hasPrDocument === "false") { - basicConditions.push(eq(biddingListView.hasPrDocument, false)) + basicConditions.push(eq(biddings.hasPrDocument, false)) } const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined @@ -222,13 +334,15 @@ export async function getBiddings(input: GetBiddingsSchema) { if (input.search) { const s = `%${input.search}%` const searchConditions = [ - ilike(biddingListView.biddingNumber, s), - ilike(biddingListView.title, s), - ilike(biddingListView.projectName, s), - ilike(biddingListView.itemName, s), - ilike(biddingListView.managerName, s), - ilike(biddingListView.prNumber, s), - ilike(biddingListView.remarks, s), + ilike(biddings.biddingNumber, s), + ilike(biddings.title, s), + ilike(biddings.projectName, s), + ilike(biddings.itemName, s), + ilike(biddings.purchasingOrganization, s), + ilike(biddings.bidPicName, s), + ilike(biddings.supplyPicName, s), + ilike(biddings.prNumber, s), + ilike(biddings.remarks, s), ] globalWhere = or(...searchConditions) } @@ -244,7 +358,7 @@ export async function getBiddings(input: GetBiddingsSchema) { // ✅ 5) 전체 개수 조회 const totalResult = await db .select({ count: count() }) - .from(biddingListView) + .from(biddings) .where(finalWhere) const total = totalResult[0]?.count || 0 @@ -257,18 +371,444 @@ export async function getBiddings(input: GetBiddingsSchema) { // ✅ 6) 정렬 및 페이징 const orderByColumns = input.sort.map((sort) => { - const column = sort.id as keyof typeof biddingListView.$inferSelect - return sort.desc ? desc(biddingListView[column]) : asc(biddingListView[column]) + const column = sort.id as keyof typeof biddings.$inferSelect + return sort.desc ? desc(biddings[column]) : asc(biddings[column]) }) if (orderByColumns.length === 0) { - orderByColumns.push(desc(biddingListView.createdAt)) + orderByColumns.push(desc(biddings.createdAt)) } - // ✅ 7) 메인 쿼리 - 매우 간단해짐! + // ✅ 7) 메인 쿼리 - 이제 조인이 필요함! const data = await db - .select() - .from(biddingListView) + .select({ + // 기본 입찰 정보 + 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) @@ -305,80 +845,6 @@ export async function getBiddingStatusCounts() { } } -// 입찰유형별 개수 집계 -export async function getBiddingTypeCounts() { - try { - const counts = await db - .select({ - biddingType: biddings.biddingType, - count: count(), - }) - .from(biddings) - .groupBy(biddings.biddingType) - - return counts.reduce((acc, { biddingType, count }) => { - acc[biddingType] = count - return acc - }, {} as Record<string, number>) - } catch (error) { - console.error('Failed to get bidding type counts:', error) - return {} - } -} - -// 담당자별 개수 집계 -export async function getBiddingManagerCounts() { - try { - const counts = await db - .select({ - managerName: biddings.managerName, - count: count(), - }) - .from(biddings) - .where(sql`${biddings.managerName} IS NOT NULL AND ${biddings.managerName} != ''`) - .groupBy(biddings.managerName) - - return counts.reduce((acc, { managerName, count }) => { - if (managerName) { - acc[managerName] = count - } - return acc - }, {} as Record<string, number>) - } catch (error) { - console.error('Failed to get bidding manager counts:', error) - return {} - } -} - -// 월별 입찰 생성 통계 -export async function getBiddingMonthlyStats(year: number = new Date().getFullYear()) { - try { - const stats = await db - .select({ - month: sql<number>`EXTRACT(MONTH FROM ${biddings.createdAt})`.as('month'), - count: count(), - }) - .from(biddings) - .where(sql`EXTRACT(YEAR FROM ${biddings.createdAt}) = ${year}`) - .groupBy(sql`EXTRACT(MONTH FROM ${biddings.createdAt})`) - .orderBy(sql`EXTRACT(MONTH FROM ${biddings.createdAt})`) - - // 1-12월 전체 배열 생성 (없는 월은 0으로) - const monthlyData = Array.from({ length: 12 }, (_, i) => { - const month = i + 1 - const found = stats.find(stat => stat.month === month) - return { - month, - count: found?.count || 0, - } - }) - - return monthlyData - } catch (error) { - console.error('Failed to get bidding monthly stats:', error) - return [] - } -} export interface CreateBiddingInput extends CreateBiddingSchema { // 사양설명회 정보 (선택사항) @@ -401,17 +867,56 @@ export interface CreateBiddingInput extends CreateBiddingSchema { prItems?: Array<{ id: string prNumber: string - itemCode: string - itemInfo: string + projectId?: number + projectInfo?: string + shi?: string quantity: string quantityUnit: string totalWeight: string weightUnit: string + materialGroupNumber: string + materialGroupInfo: string + materialNumber?: string + materialInfo?: string materialDescription: string hasSpecDocument: boolean requestedDeliveryDate: string specFiles: File[] isRepresentative: boolean + + // 가격 정보 + annualUnitPrice?: string + currency?: string + + // 단위 정보 + priceUnit?: string + purchaseUnit?: string + materialWeight?: string + + // WBS 정보 + wbsCode?: string + wbsName?: string + + // Cost Center 정보 + costCenterCode?: string + costCenterName?: string + + // GL Account 정보 + glAccountCode?: string + glAccountName?: string + + // 내정 정보 + targetUnitPrice?: string + targetAmount?: string + targetCurrency?: string + + // 예산 정보 + budgetAmount?: string + budgetCurrency?: string + + // 실적 정보 + actualAmount?: string + actualCurrency?: string }> // 입찰 조건 (선택사항) @@ -419,9 +924,10 @@ export interface CreateBiddingInput extends CreateBiddingSchema { paymentTerms: string taxConditions: string incoterms: string - contractDeliveryDate: string - shippingPort: string - destinationPort: string + incotermsOption?: string + contractDeliveryDate?: string + shippingPort?: string + destinationPort?: string isPriceAdjustmentApplicable: boolean sparePartOptions: string } @@ -435,12 +941,54 @@ export interface UpdateBiddingInput extends UpdateBiddingSchema { id: number } +// 4자리 시퀀스 생성 (0001 -> 0009 -> 000A -> 000Z -> 0011 -> ...) +function generateNextSequence(currentMax: string | null): string { + if (!currentMax) { + return '0001'; // 첫 번째 시퀀스 + } + + // 36진수로 변환 (0-9, A-Z) + const chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; + + // 4자리 시퀀스를 36진수로 해석 + let value = 0; + for (let i = 0; i < 4; i++) { + const charIndex = chars.indexOf(currentMax[i]); + if (charIndex === -1) return '0001'; // 잘못된 문자면 초기화 + value = value * 36 + charIndex; + } + + // 1 증가 + value += 1; + + // 다시 4자리 36진수로 변환 + let result = ''; + for (let i = 0; i < 4; i++) { + const remainder = value % 36; + result = chars[remainder] + result; + value = Math.floor(value / 36); + } + + // 4자리가 되도록 앞에 0 채우기 + return result.padStart(4, '0'); +} + // 자동 입찰번호 생성 export async function generateBiddingNumber( + contractType: string, userId?: string, tx?: any, maxRetries: number = 5 ): Promise<string> { + // 계약 타입별 접두사 설정 + const typePrefix = { + 'general': 'E', + 'unit_price': 'F', + 'sale': 'G' + }; + + const prefix = typePrefix[contractType as keyof typeof typePrefix] || 'E'; + // user 테이블의 user.userCode가 있으면 발주담당자 코드로 사용 // userId가 주어졌을 때 user.userCode를 조회, 없으면 '000' 사용 let purchaseManagerCode = '000'; @@ -458,27 +1006,25 @@ export async function generateBiddingNumber( ? purchaseManagerCode.substring(0, 3).toUpperCase() : '000'; + // 현재 년도 2자리 + const currentYear = new Date().getFullYear().toString().slice(-2); + const dbInstance = tx || db; - const prefix = `B${managerCode}`; + const yearPrefix = `${prefix}${managerCode}${currentYear}`; for (let attempt = 0; attempt < maxRetries; attempt++) { - // 현재 최대 일련번호 조회 + // 현재 최대 시퀀스 조회 (년도별로, -01 제외하고 앞부분만) + const prefixLength = yearPrefix.length + 4; const result = await dbInstance - .select({ - maxNumber: sql<string>`MAX(${biddings.biddingNumber})` + .select({ + maxNumber: sql<string>`MAX(LEFT(${biddings.biddingNumber}, ${prefixLength}))` }) .from(biddings) - .where(like(biddings.biddingNumber, `${prefix}%`)); + .where(like(biddings.biddingNumber, `${yearPrefix}%`)); - let sequence = 1; - if (result[0]?.maxNumber) { - const lastSequence = parseInt(result[0].maxNumber.slice(-5)); - if (!isNaN(lastSequence)) { - sequence = lastSequence + 1; - } - } + const nextSequence = generateNextSequence(result[0]?.maxNumber?.slice(-4) || null); - const biddingNumber = `${prefix}${sequence.toString().padStart(5, '0')}`; + const biddingNumber = `${yearPrefix}${nextSequence}-01`; // 중복 확인 const existing = await dbInstance @@ -503,32 +1049,27 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { const userName = await getUserNameById(userId) return await db.transaction(async (tx) => { // 자동 입찰번호 생성 - const biddingNumber = await generateBiddingNumber(userId) + const biddingNumber = await generateBiddingNumber(input.contractType, userId, tx) - // 프로젝트 정보 조회 + // 프로젝트 정보 조회 (PR 아이템에서 설정됨) let projectName = input.projectName - if (input.projectId) { - const project = await tx - .select({ code: projects.code, name: projects.name }) - .from(projects) - .where(eq(projects.id, input.projectId)) - .limit(1) - - if (project.length > 0) { - projectName = `${project[0].code} (${project[0].name})` - } - } - // 표준 공고문 템플릿 가져오기 + // 표준 공고문 템플릿 가져오기 (noticeType별) let standardContent = '' if (!input.content) { try { const template = await tx .select({ content: biddingNoticeTemplate.content }) .from(biddingNoticeTemplate) - .where(eq(biddingNoticeTemplate.type, 'standard')) + .where( + and( + eq(biddingNoticeTemplate.isTemplate, true), + eq(biddingNoticeTemplate.type, input.noticeType || 'standard') + ) + ) + .orderBy(desc(biddingNoticeTemplate.updatedAt)) .limit(1) - + if (template.length > 0) { standardContent = template[0].content } @@ -552,22 +1093,26 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { .insert(biddings) .values({ biddingNumber, + originalBiddingNumber: null, // 원입찰번호는 단순 정보이므로 null revision: input.revision || 0, - // 프로젝트 정보 - projectId: input.projectId, + // 프로젝트 정보 (PR 아이템에서 설정됨) projectName, itemName: input.itemName, title: input.title, description: input.description, - content: input.content || standardContent, contractType: input.contractType, biddingType: input.biddingType, awardCount: input.awardCount, - contractStartDate: input.contractStartDate ? parseDate(input.contractStartDate) : null, - contractEndDate: input.contractEndDate ? parseDate(input.contractEndDate) : null, + contractStartDate: input.contractStartDate ? parseDate(input.contractStartDate) : new Date(), + contractEndDate: input.contractEndDate ? parseDate(input.contractEndDate) : (() => { + const startDate = input.contractStartDate ? new Date(input.contractStartDate) : new Date() + const endDate = new Date(startDate) + endDate.setFullYear(endDate.getFullYear() + 1) // 1년 후 + return endDate + })(), // 자동 등록일 설정 biddingRegistrationDate: new Date(), @@ -588,9 +1133,17 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { // biddingSourceType: input.biddingSourceType || 'manual', isPublic: input.isPublic || false, isUrgent: input.isUrgent || false, - managerName: input.managerName, - managerEmail: input.managerEmail, - managerPhone: input.managerPhone, + + // 구매조직 + purchasingOrganization: input.purchasingOrganization, + + // 담당자 정보 (user FK) + bidPicId: input.bidPicId ? parseInt(input.bidPicId.toString()) : null, + bidPicName: input.bidPicName || null, + bidPicCode: input.bidPicCode || null, + supplyPicId: input.supplyPicId ? parseInt(input.supplyPicId.toString()) : null, + supplyPicName: input.supplyPicName || null, + supplyPicCode: input.supplyPicCode || null, remarks: input.remarks, createdBy: userName, @@ -600,7 +1153,15 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { const biddingId = newBidding.id - // 2. 사양설명회 정보 저장 (있는 경우) + // 2. 입찰공고 생성 (템플릿에서 복제) + await tx.insert(biddingNoticeTemplate).values({ + biddingId, + title: input.title + ' 입찰공고', + content: input.content || standardContent, + isTemplate: false, + }) + + // 3. 사양설명회 정보 저장 (있는 경우) if (input.specificationMeeting) { const [newSpecMeeting] = await tx .insert(specificationMeetings) @@ -666,9 +1227,10 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { paymentTerms: input.biddingConditions.paymentTerms, taxConditions: input.biddingConditions.taxConditions, incoterms: input.biddingConditions.incoterms, + incotermsOption: input.biddingConditions.incotermsOption, contractDeliveryDate: input.biddingConditions.contractDeliveryDate || null, - shippingPort: input.biddingConditions.shippingPort, - destinationPort: input.biddingConditions.destinationPort, + shippingPort: input.biddingConditions.shippingPort || null, + destinationPort: input.biddingConditions.destinationPort || null, isPriceAdjustmentApplicable: input.biddingConditions.isPriceAdjustmentApplicable, sparePartOptions: input.biddingConditions.sparePartOptions, }) @@ -684,18 +1246,61 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { // PR 아이템 저장 const [newPrItem] = await tx.insert(prItemsForBidding).values({ biddingId, - itemNumber: prItem.itemCode, // itemCode를 itemNumber로 매핑 - projectInfo: '', // 필요시 추가 - itemInfo: prItem.itemInfo, - shi: '', // 필요시 추가 + projectId: prItem.projectId, // 프로젝트 ID 추가 + projectInfo: prItem.projectInfo || '', // 프로젝트 정보 (기존 호환성 유지) + shi: prItem.shi || '', // SHI 정보 requestedDeliveryDate: prItem.requestedDeliveryDate ? new Date(prItem.requestedDeliveryDate) : null, - annualUnitPrice: null, // 필요시 추가 - currency: 'KRW', // 기본값 또는 입력받은 값 + + // 자재 그룹 정보 (필수) + materialGroupNumber: prItem.materialGroupNumber, + materialGroupInfo: prItem.materialGroupInfo, + + // 자재 정보 + materialNumber: prItem.materialNumber || null, + materialInfo: prItem.materialInfo || null, + + // 가격 정보 + annualUnitPrice: prItem.annualUnitPrice ? parseFloat(prItem.annualUnitPrice) : null, + currency: prItem.currency || 'KRW', + + // 수량 및 중량 quantity: prItem.quantity ? parseFloat(prItem.quantity) : null, - quantityUnit: prItem.quantityUnit as any, // enum 타입에 맞게 + quantityUnit: prItem.quantityUnit as any, totalWeight: prItem.totalWeight ? parseFloat(prItem.totalWeight) : null, - weightUnit: prItem.weightUnit as any, // enum 타입에 맞게 - materialDescription: '', // 필요시 추가 + weightUnit: prItem.weightUnit as any, + + // 단위 정보 + priceUnit: prItem.priceUnit || null, + purchaseUnit: prItem.purchaseUnit || null, + materialWeight: prItem.materialWeight ? parseFloat(prItem.materialWeight) : null, + + // WBS 정보 + wbsCode: prItem.wbsCode || null, + wbsName: prItem.wbsName || null, + + // Cost Center 정보 + costCenterCode: prItem.costCenterCode || null, + costCenterName: prItem.costCenterName || null, + + // GL Account 정보 + glAccountCode: prItem.glAccountCode || null, + glAccountName: prItem.glAccountName || null, + + // 내정 정보 + targetUnitPrice: prItem.targetUnitPrice ? parseFloat(prItem.targetUnitPrice) : null, + targetAmount: prItem.targetAmount ? parseFloat(prItem.targetAmount) : null, + targetCurrency: prItem.targetCurrency || 'KRW', + + // 예산 정보 + budgetAmount: prItem.budgetAmount ? parseFloat(prItem.budgetAmount) : null, + budgetCurrency: prItem.budgetCurrency || 'KRW', + + // 실적 정보 + actualAmount: prItem.actualAmount ? parseFloat(prItem.actualAmount) : null, + actualCurrency: prItem.actualCurrency || 'KRW', + + // 상세 정보 + materialDescription: prItem.materialDescription || '', prNumber: prItem.prNumber, hasSpecDocument: prItem.specFiles.length > 0, isRepresentative: prItem.isRepresentative, @@ -724,7 +1329,7 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { mimeType: file.type, filePath: saveResult.publicPath!, // publicPath: saveResult.publicPath, - title: `${prItem.itemInfo || prItem.itemCode} 스펙 - ${file.name}`, + title: `${prItem.materialGroupInfo || prItem.materialGroupNumber} 스펙 - ${file.name}`, description: `PR ${prItem.prNumber}의 스펙 문서`, isPublic: false, isRequired: false, @@ -840,9 +1445,15 @@ export async function updateBidding(input: UpdateBiddingInput, userId: string) { if (input.status !== undefined) updateData.status = input.status if (input.isPublic !== undefined) updateData.isPublic = input.isPublic if (input.isUrgent !== undefined) updateData.isUrgent = input.isUrgent - if (input.managerName !== undefined) updateData.managerName = input.managerName - if (input.managerEmail !== undefined) updateData.managerEmail = input.managerEmail - if (input.managerPhone !== undefined) updateData.managerPhone = input.managerPhone + + // 구매조직 + if (input.purchasingOrganization !== undefined) updateData.purchasingOrganization = input.purchasingOrganization + + // 담당자 정보 (user FK) + if (input.bidPicId !== undefined) updateData.bidPicId = input.bidPicId + if (input.bidPicName !== undefined) updateData.bidPicName = input.bidPicName + if (input.supplyPicId !== undefined) updateData.supplyPicId = input.supplyPicId + if (input.supplyPicName !== undefined) updateData.supplyPicName = input.supplyPicName if (input.remarks !== undefined) updateData.remarks = input.remarks @@ -908,6 +1519,12 @@ export async function deleteBidding(id: number) { // 단일 입찰 조회 export async function getBiddingById(id: number) { try { + // ID 유효성 검증 + if (!id || isNaN(id) || id <= 0) { + console.warn('Invalid bidding ID provided to getBiddingById:', id) + return null + } + const bidding = await db .select() .from(biddings) @@ -979,17 +1596,52 @@ export interface PRDetails { }> items: Array<{ id: number - itemNumber?: string | null - itemInfo: string - quantity?: number | null - quantityUnit?: string | null - requestedDeliveryDate?: string | null - prNumber?: string | null - annualUnitPrice?: number | null - currency: string - totalWeight?: number | null - weightUnit?: string | null - materialDescription?: string | null + itemNumber: string | null + prNumber: string | null + projectInfo: string | null + shi: string | null + // 자재 그룹 정보 + materialGroupNumber: string | null + materialGroupInfo: string | null + // 자재 정보 + materialNumber: string | null + materialInfo: string | null + // 품목 정보 + itemInfo: string | null + // 수량 및 중량 + quantity: number | null + quantityUnit: string | null + totalWeight: number | null + weightUnit: string | null + // 가격 정보 + annualUnitPrice: number | null + currency: string | null + // 단위 정보 + priceUnit: string | null + purchaseUnit: string | null + materialWeight: number | null + // WBS 정보 + wbsCode: string | null + wbsName: string | null + // Cost Center 정보 + costCenterCode: string | null + costCenterName: string | null + // GL Account 정보 + glAccountCode: string | null + glAccountName: string | null + // 내정 정보 + targetUnitPrice: number | null + targetAmount: number | null + targetCurrency: string | null + // 예산 정보 + budgetAmount: number | null + budgetCurrency: string | null + // 실적 정보 + actualAmount: number | null + actualCurrency: string | null + // 납품 일정 + requestedDeliveryDate: string | null + // SPEC 문서 hasSpecDocument: boolean createdAt: string updatedAt: string @@ -1000,7 +1652,7 @@ export interface PRDetails { fileSize: number filePath: string uploadedAt: string - title?: string | null + title: string | null }> }> } @@ -1162,21 +1814,56 @@ export async function getPRDetailsAction( ) ) - // 5. 데이터 직렬화 + // 5. 데이터 직렬화 (모든 필드 포함) return { id: item.id, itemNumber: item.itemNumber, + prNumber: item.prNumber, + projectInfo: item.projectInfo, + shi: item.shi, + // 자재 그룹 정보 + materialGroupNumber: item.materialGroupNumber, + materialGroupInfo: item.materialGroupInfo, + // 자재 정보 + materialNumber: item.materialNumber, + materialInfo: item.materialInfo, + // 품목 정보 itemInfo: item.itemInfo, + // 수량 및 중량 quantity: item.quantity ? Number(item.quantity) : null, quantityUnit: item.quantityUnit, - requestedDeliveryDate: item.requestedDeliveryDate || null, - prNumber: item.prNumber, - annualUnitPrice: item.annualUnitPrice ? Number(item.annualUnitPrice) : null, - currency: item.currency, totalWeight: item.totalWeight ? Number(item.totalWeight) : null, weightUnit: item.weightUnit, - materialDescription: item.materialDescription, - hasSpecDocument: item.hasSpecDocument, + // 가격 정보 + annualUnitPrice: item.annualUnitPrice ? Number(item.annualUnitPrice) : null, + currency: item.currency, + // 단위 정보 + priceUnit: item.priceUnit, + purchaseUnit: item.purchaseUnit, + materialWeight: item.materialWeight ? Number(item.materialWeight) : null, + // WBS 정보 + wbsCode: item.wbsCode, + wbsName: item.wbsName, + // Cost Center 정보 + costCenterCode: item.costCenterCode, + costCenterName: item.costCenterName, + // GL Account 정보 + glAccountCode: item.glAccountCode, + glAccountName: item.glAccountName, + // 내정 정보 + targetUnitPrice: item.targetUnitPrice ? Number(item.targetUnitPrice) : null, + targetAmount: item.targetAmount ? Number(item.targetAmount) : null, + targetCurrency: item.targetCurrency, + // 예산 정보 + budgetAmount: item.budgetAmount ? Number(item.budgetAmount) : null, + budgetCurrency: item.budgetCurrency, + // 실적 정보 + actualAmount: item.actualAmount ? Number(item.actualAmount) : null, + actualCurrency: item.actualCurrency, + // 납품 일정 + requestedDeliveryDate: item.requestedDeliveryDate || null, + // 기타 + hasSpecDocument: item.hasSpecDocument || false, createdAt: item.createdAt?.toISOString() || '', updatedAt: item.updatedAt?.toISOString() || '', specDocuments: specDocuments.map(doc => ({ @@ -1302,12 +1989,688 @@ export async function getBiddingConditions(biddingId: number) { } // 입찰 조건 업데이트 +// === 입찰 관리 서버 액션들 === + +// 입찰 기본 정보 업데이트 (관리 페이지용) +export async function updateBiddingBasicInfo( + biddingId: number, + updates: { + title?: string + description?: string + content?: string + noticeType?: string + contractType?: string + biddingType?: string + biddingTypeCustom?: string + awardCount?: string + budget?: string + finalBidPrice?: string + targetPrice?: string + prNumber?: string + contractStartDate?: string + contractEndDate?: string + submissionStartDate?: string + submissionEndDate?: string + evaluationDate?: string + hasSpecificationMeeting?: boolean + hasPrDocument?: boolean + currency?: string + purchasingOrganization?: string + bidPicName?: string + bidPicCode?: string + supplyPicName?: string + supplyPicCode?: string + requesterName?: string + remarks?: string + }, + userId: string +) { + try { + const userName = await getUserNameById(userId) + + // 존재 여부 확인 + const existing = await db + .select({ id: biddings.id }) + .from(biddings) + .where(eq(biddings.id, biddingId)) + .limit(1) + + if (existing.length === 0) { + return { + success: false, + error: '존재하지 않는 입찰입니다.' + } + } + + // 날짜 문자열을 Date 객체로 변환 + const parseDate = (dateStr?: string) => { + if (!dateStr) return undefined + try { + return new Date(dateStr) + } catch { + return undefined + } + } + + // 숫자 문자열을 숫자로 변환 (빈 문자열은 null) + const parseNumeric = (value?: string): number | null | undefined => { + if (value === undefined) return undefined + if (value === '' || value === null) return null + const parsed = parseFloat(value) + return isNaN(parsed) ? null : parsed + } + + // 업데이트할 데이터 준비 + const updateData: any = { + updatedAt: new Date(), + updatedBy: userName, + } + + // 정의된 필드들만 업데이트 + if (updates.title !== undefined) updateData.title = updates.title + if (updates.description !== undefined) updateData.description = updates.description + if (updates.content !== undefined) updateData.content = updates.content + if (updates.noticeType !== undefined) updateData.noticeType = updates.noticeType + if (updates.contractType !== undefined) updateData.contractType = updates.contractType + if (updates.biddingType !== undefined) updateData.biddingType = updates.biddingType + if (updates.biddingTypeCustom !== undefined) updateData.biddingTypeCustom = updates.biddingTypeCustom + if (updates.awardCount !== undefined) updateData.awardCount = updates.awardCount + if (updates.budget !== undefined) updateData.budget = parseNumeric(updates.budget) + if (updates.finalBidPrice !== undefined) updateData.finalBidPrice = parseNumeric(updates.finalBidPrice) + if (updates.targetPrice !== undefined) updateData.targetPrice = parseNumeric(updates.targetPrice) + if (updates.prNumber !== undefined) updateData.prNumber = updates.prNumber + if (updates.contractStartDate !== undefined) updateData.contractStartDate = parseDate(updates.contractStartDate) + if (updates.contractEndDate !== undefined) updateData.contractEndDate = parseDate(updates.contractEndDate) + if (updates.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(updates.submissionStartDate) + if (updates.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(updates.submissionEndDate) + if (updates.evaluationDate !== undefined) updateData.evaluationDate = parseDate(updates.evaluationDate) + if (updates.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = updates.hasSpecificationMeeting + if (updates.hasPrDocument !== undefined) updateData.hasPrDocument = updates.hasPrDocument + if (updates.currency !== undefined) updateData.currency = updates.currency + if (updates.purchasingOrganization !== undefined) updateData.purchasingOrganization = updates.purchasingOrganization + if (updates.bidPicName !== undefined) updateData.bidPicName = updates.bidPicName + if (updates.bidPicCode !== undefined) updateData.bidPicCode = updates.bidPicCode + if (updates.supplyPicName !== undefined) updateData.supplyPicName = updates.supplyPicName + if (updates.supplyPicCode !== undefined) updateData.supplyPicCode = updates.supplyPicCode + if (updates.requesterName !== undefined) updateData.requesterName = updates.requesterName + if (updates.remarks !== undefined) updateData.remarks = updates.remarks + + // 데이터베이스 업데이트 + await db + .update(biddings) + .set(updateData) + .where(eq(biddings.id, biddingId)) + + revalidatePath(`/evcp/bid/${biddingId}`) + revalidatePath(`/evcp/bid/${biddingId}/info`) + + return { + success: true, + message: '입찰 기본 정보가 성공적으로 업데이트되었습니다.' + } + } catch (error) { + console.error('Failed to update bidding basic info:', error) + return { + success: false, + error: '입찰 기본 정보 업데이트에 실패했습니다.' + } + } +} + +// 입찰 일정 업데이트 +export async function updateBiddingSchedule( + biddingId: number, + schedule: { + submissionStartDate?: string + submissionEndDate?: string + remarks?: string + isUrgent?: boolean + hasSpecificationMeeting?: boolean + }, + userId: string, + specificationMeeting?: { + meetingDate: string + meetingTime: string + location: string + address: string + contactPerson: string + contactPhone: string + contactEmail: string + agenda: string + materials: string + notes: string + isRequired: boolean + } +) { + try { + const userName = await getUserNameById(userId) + + // 날짜 문자열을 Date 객체로 변환 + const parseDate = (dateStr?: string) => { + if (!dateStr) return undefined + try { + return new Date(dateStr) + } catch { + return undefined + } + } + + return await db.transaction(async (tx) => { + const updateData: any = { + updatedAt: new Date(), + updatedBy: userName, + } + + if (schedule.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(schedule.submissionStartDate) + if (schedule.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(schedule.submissionEndDate) + if (schedule.remarks !== undefined) updateData.remarks = schedule.remarks + if (schedule.isUrgent !== undefined) updateData.isUrgent = schedule.isUrgent + if (schedule.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = schedule.hasSpecificationMeeting + + await tx + .update(biddings) + .set(updateData) + .where(eq(biddings.id, biddingId)) + + // 사양설명회 정보 저장/업데이트 + if (schedule.hasSpecificationMeeting && specificationMeeting) { + // 기존 사양설명회 정보 확인 + const existingMeeting = await tx + .select() + .from(specificationMeetings) + .where(eq(specificationMeetings.biddingId, biddingId)) + .limit(1) + + if (existingMeeting.length > 0) { + // 기존 정보 업데이트 + await tx + .update(specificationMeetings) + .set({ + meetingDate: new Date(specificationMeeting.meetingDate), + meetingTime: specificationMeeting.meetingTime || null, + location: specificationMeeting.location, + address: specificationMeeting.address || null, + contactPerson: specificationMeeting.contactPerson, + contactPhone: specificationMeeting.contactPhone || null, + contactEmail: specificationMeeting.contactEmail || null, + agenda: specificationMeeting.agenda || null, + materials: specificationMeeting.materials || null, + notes: specificationMeeting.notes || null, + isRequired: specificationMeeting.isRequired || false, + updatedAt: new Date(), + }) + .where(eq(specificationMeetings.id, existingMeeting[0].id)) + } else { + // 새로 생성 + await tx + .insert(specificationMeetings) + .values({ + biddingId, + meetingDate: new Date(specificationMeeting.meetingDate), + meetingTime: specificationMeeting.meetingTime || null, + location: specificationMeeting.location, + address: specificationMeeting.address || null, + contactPerson: specificationMeeting.contactPerson, + contactPhone: specificationMeeting.contactPhone || null, + contactEmail: specificationMeeting.contactEmail || null, + agenda: specificationMeeting.agenda || null, + materials: specificationMeeting.materials || null, + notes: specificationMeeting.notes || null, + isRequired: specificationMeeting.isRequired || false, + }) + } + } else if (!schedule.hasSpecificationMeeting) { + // 사양설명회 실시 여부가 false로 변경된 경우, 관련 정보 삭제 + await tx + .delete(specificationMeetings) + .where(eq(specificationMeetings.biddingId, biddingId)) + } + + revalidatePath(`/evcp/bid/${biddingId}`) + revalidatePath(`/evcp/bid/${biddingId}/schedule`) + + return { + success: true, + message: '입찰 일정이 성공적으로 업데이트되었습니다.' + } + }) + } catch (error) { + console.error('Failed to update bidding schedule:', error) + return { + success: false, + error: '입찰 일정 업데이트에 실패했습니다.' + } + } +} + +// 입찰 품목 관리 액션들 +export async function getBiddingItems(biddingId: number) { + try { + // PR 아이템 조회 (실제로는 prItemsForBidding 테이블에서 조회) + const items = await db + .select({ + id: prItemsForBidding.id, + itemName: prItemsForBidding.itemName, + description: prItemsForBidding.description, + quantity: prItemsForBidding.quantity, + unit: prItemsForBidding.unit, + unitPrice: prItemsForBidding.unitPrice, + totalPrice: prItemsForBidding.totalPrice, + currency: prItemsForBidding.currency, + }) + .from(prItemsForBidding) + .where(eq(prItemsForBidding.biddingId, biddingId)) + .orderBy(prItemsForBidding.id) + + return { + success: true, + data: items + } + } catch (error) { + console.error('Failed to get bidding items:', error) + return { + success: false, + error: '품목 정보를 불러오는데 실패했습니다.' + } + } +} + +export async function updateBiddingItem( + itemId: number, + updates: { + itemName?: string + description?: string + quantity?: number + unit?: string + unitPrice?: number + currency?: string + } +) { + try { + const updateData: any = { + updatedAt: new Date(), + } + + if (updates.itemName !== undefined) updateData.itemName = updates.itemName + if (updates.description !== undefined) updateData.description = updates.description + if (updates.quantity !== undefined) updateData.quantity = updates.quantity + if (updates.unit !== undefined) updateData.unit = updates.unit + if (updates.unitPrice !== undefined) updateData.unitPrice = updates.unitPrice + if (updates.currency !== undefined) updateData.currency = updates.currency + + // 총액 자동 계산 + if (updates.quantity !== undefined || updates.unitPrice !== undefined) { + const item = await db + .select({ quantity: prItemsForBidding.quantity, unitPrice: prItemsForBidding.unitPrice }) + .from(prItemsForBidding) + .where(eq(prItemsForBidding.id, itemId)) + .limit(1) + + if (item.length > 0) { + const quantity = updates.quantity ?? item[0].quantity ?? 0 + const unitPrice = updates.unitPrice ?? item[0].unitPrice ?? 0 + updateData.totalPrice = quantity * unitPrice + } + } + + await db + .update(prItemsForBidding) + .set(updateData) + .where(eq(prItemsForBidding.id, itemId)) + + return { + success: true, + message: '품목 정보가 성공적으로 업데이트되었습니다.' + } + } catch (error) { + console.error('Failed to update bidding item:', error) + return { + success: false, + error: '품목 정보 업데이트에 실패했습니다.' + } + } +} + +export async function addBiddingItem( + biddingId: number, + item: { + itemName: string + description?: string + quantity?: number + unit?: string + unitPrice?: number + currency?: string + } +) { + try { + const totalPrice = (item.quantity || 0) * (item.unitPrice || 0) + + await db.insert(prItemsForBidding).values({ + biddingId, + itemName: item.itemName, + description: item.description, + quantity: item.quantity || 0, + unit: item.unit, + unitPrice: item.unitPrice || 0, + totalPrice, + currency: item.currency || 'KRW', + }) + + return { + success: true, + message: '품목이 성공적으로 추가되었습니다.' + } + } catch (error) { + console.error('Failed to add bidding item:', error) + return { + success: false, + error: '품목 추가에 실패했습니다.' + } + } +} + +export async function removeBiddingItem(itemId: number) { + try { + await db + .delete(prItemsForBidding) + .where(eq(prItemsForBidding.id, itemId)) + + return { + success: true, + message: '품목이 성공적으로 삭제되었습니다.' + } + } catch (error) { + console.error('Failed to remove bidding item:', error) + return { + success: false, + error: '품목 삭제에 실패했습니다.' + } + } +} + +// PR 아이템 추가 (전체 필드 지원) +export async function addPRItemForBidding( + biddingId: number, + item: { + projectId?: number | null + projectInfo?: string | null + shi?: string | null + materialGroupNumber?: string | null + materialGroupInfo?: string | null + materialNumber?: string | null + materialInfo?: string | null + quantity?: string | null + quantityUnit?: string | null + totalWeight?: string | null + weightUnit?: string | null + priceUnit?: string | null + purchaseUnit?: string | null + materialWeight?: string | null + wbsCode?: string | null + wbsName?: string | null + costCenterCode?: string | null + costCenterName?: string | null + glAccountCode?: string | null + glAccountName?: string | null + targetUnitPrice?: string | null + targetAmount?: string | null + targetCurrency?: string | null + budgetAmount?: string | null + budgetCurrency?: string | null + actualAmount?: string | null + actualCurrency?: string | null + requestedDeliveryDate?: string | null + prNumber?: string | null + currency?: string | null + annualUnitPrice?: string | null + hasSpecDocument?: boolean + } +) { + try { + const result = await db.insert(prItemsForBidding).values({ + biddingId, + projectId: item.projectId || null, + projectInfo: item.projectInfo || null, + shi: item.shi || null, + materialGroupNumber: item.materialGroupNumber || null, + materialGroupInfo: item.materialGroupInfo || null, + materialNumber: item.materialNumber || null, + materialInfo: item.materialInfo || null, + quantity: item.quantity ? parseFloat(item.quantity) : null, + quantityUnit: item.quantityUnit || null, + totalWeight: item.totalWeight ? parseFloat(item.totalWeight) : null, + weightUnit: item.weightUnit || null, + priceUnit: item.priceUnit || null, + purchaseUnit: item.purchaseUnit || null, + materialWeight: item.materialWeight ? parseFloat(item.materialWeight) : null, + wbsCode: item.wbsCode || null, + wbsName: item.wbsName || null, + costCenterCode: item.costCenterCode || null, + costCenterName: item.costCenterName || null, + glAccountCode: item.glAccountCode || null, + glAccountName: item.glAccountName || null, + targetUnitPrice: item.targetUnitPrice ? parseFloat(item.targetUnitPrice) : null, + targetAmount: item.targetAmount ? parseFloat(item.targetAmount) : null, + targetCurrency: item.targetCurrency || 'KRW', + budgetAmount: item.budgetAmount ? parseFloat(item.budgetAmount) : null, + budgetCurrency: item.budgetCurrency || 'KRW', + actualAmount: item.actualAmount ? parseFloat(item.actualAmount) : null, + actualCurrency: item.actualCurrency || 'KRW', + requestedDeliveryDate: item.requestedDeliveryDate ? new Date(item.requestedDeliveryDate) : null, + prNumber: item.prNumber || null, + currency: item.currency || 'KRW', + annualUnitPrice: item.annualUnitPrice ? parseFloat(item.annualUnitPrice) : null, + hasSpecDocument: item.hasSpecDocument || false, + }).returning() + + revalidatePath(`/evcp/bid/${biddingId}/info`) + revalidatePath(`/evcp/bid/${biddingId}`) + + return { + success: true, + data: result[0], + message: '품목이 성공적으로 추가되었습니다.' + } + } catch (error) { + console.error('Failed to add PR item:', error) + return { + success: false, + error: '품목 추가에 실패했습니다.' + } + } +} + +// 입찰 업체 관리 액션들 +export async function getBiddingVendors(biddingId: number) { + try { + const vendorsData = await db + .select({ + id: biddingCompanies.id, + companyId: biddingCompanies.companyId, // 벤더 ID 추가 + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + contactPerson: biddingCompanies.contactPerson, + contactEmail: biddingCompanies.contactEmail, + contactPhone: biddingCompanies.contactPhone, + quotationAmount: biddingCompanies.finalQuoteAmount, + currency: sql<string>`'KRW'`, + invitationStatus: biddingCompanies.invitationStatus, + isPriceAdjustmentApplicableQuestion: biddingCompanies.isPriceAdjustmentApplicableQuestion, + }) + .from(biddingCompanies) + .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) + .where(eq(biddingCompanies.biddingId, biddingId)) + .orderBy(biddingCompanies.id) + + return { + success: true, + data: vendorsData + } + } catch (error) { + console.error('Failed to get bidding vendors:', error) + return { + success: false, + error: '업체 정보를 불러오는데 실패했습니다.' + } + } +} + +export async function updateBiddingCompanyPriceAdjustmentQuestion( + biddingCompanyId: number, + isPriceAdjustmentApplicableQuestion: boolean +) { + try { + await db + .update(biddingCompanies) + .set({ + isPriceAdjustmentApplicableQuestion, + updatedAt: new Date(), + }) + .where(eq(biddingCompanies.id, biddingCompanyId)) + + return { + success: true, + message: '연동제 적용요건 문의 여부가 성공적으로 업데이트되었습니다.' + } + } catch (error) { + console.error('Failed to update price adjustment question:', error) + return { + success: false, + error: '연동제 적용요건 문의 여부 업데이트에 실패했습니다.' + } + } +} + +export async function updateVendorContact( + biddingCompanyId: number, + contact: { + contactPerson?: string + contactEmail?: string + contactPhone?: string + } +) { + try { + // biddingCompanies 테이블에 연락처 정보가 직접 저장되어 있으므로 직접 업데이트 + await db + .update(biddingCompanies) + .set({ + contactPerson: contact.contactPerson, + contactEmail: contact.contactEmail, + contactPhone: contact.contactPhone, + updatedAt: new Date(), + }) + .where(eq(biddingCompanies.id, biddingCompanyId)) + + return { + success: true, + message: '담당자 정보가 성공적으로 업데이트되었습니다.' + } + } catch (error) { + console.error('Failed to update vendor contact:', error) + return { + success: false, + error: '담당자 정보 업데이트에 실패했습니다.' + } + } +} + +// 입찰 참여 업체 담당자 관리 함수들 +export async function getBiddingCompanyContacts(biddingId: number, vendorId: number) { + try { + const contacts = await db + .select({ + id: biddingCompaniesContacts.id, + biddingId: biddingCompaniesContacts.biddingId, + vendorId: biddingCompaniesContacts.vendorId, + contactName: biddingCompaniesContacts.contactName, + contactEmail: biddingCompaniesContacts.contactEmail, + contactNumber: biddingCompaniesContacts.contactNumber, + createdAt: biddingCompaniesContacts.createdAt, + updatedAt: biddingCompaniesContacts.updatedAt, + }) + .from(biddingCompaniesContacts) + .where( + and( + eq(biddingCompaniesContacts.biddingId, biddingId), + eq(biddingCompaniesContacts.vendorId, vendorId) + ) + ) + .orderBy(asc(biddingCompaniesContacts.contactName)) + + return { + success: true, + data: contacts + } + } catch (error) { + console.error('Failed to get bidding company contacts:', error) + return { + success: false, + error: '담당자 목록을 불러오는데 실패했습니다.' + } + } +} + +export async function createBiddingCompanyContact( + biddingId: number, + vendorId: number, + contact: { + contactName: string + contactEmail: string + contactNumber?: string + } +) { + try { + const [newContact] = await db + .insert(biddingCompaniesContacts) + .values({ + biddingId, + vendorId, + contactName: contact.contactName, + contactEmail: contact.contactEmail, + contactNumber: contact.contactNumber || null, + }) + .returning() + + return { + success: true, + data: newContact, + message: '담당자가 성공적으로 추가되었습니다.' + } + } catch (error) { + console.error('Failed to create bidding company contact:', error) + return { + success: false, + error: '담당자 추가에 실패했습니다.' + } + } +} + +export async function deleteBiddingCompanyContact(contactId: number) { + try { + await db + .delete(biddingCompaniesContacts) + .where(eq(biddingCompaniesContacts.id, contactId)) + + return { + success: true, + message: '담당자가 성공적으로 삭제되었습니다.' + } + } catch (error) { + console.error('Failed to delete bidding company contact:', error) + return { + success: false, + error: '담당자 삭제에 실패했습니다.' + } + } +} + export async function updateBiddingConditions( biddingId: number, updates: { paymentTerms?: string taxConditions?: string incoterms?: string + incotermsOption?: string contractDeliveryDate?: string shippingPort?: string destinationPort?: string @@ -1328,6 +2691,7 @@ export async function updateBiddingConditions( paymentTerms: updates.paymentTerms, taxConditions: updates.taxConditions, incoterms: updates.incoterms, + incotermsOption: updates.incotermsOption, contractDeliveryDate: updates.contractDeliveryDate || null, shippingPort: updates.shippingPort, destinationPort: updates.destinationPort, @@ -1367,6 +2731,272 @@ export async function updateBiddingConditions( } } +// 사전견적용 일반견적 생성 액션 +export async function createPreQuoteRfqAction(input: { + biddingId: number + rfqType: string + rfqTitle: string + dueDate: Date + picUserId: number + projectId?: number + remark?: string + items: Array<{ + itemCode: string + itemName: string + materialCode?: string + materialName?: string + quantity: number + uom: string + remark?: string + }> + biddingConditions?: { + paymentTerms?: string | null + taxConditions?: string | null + incoterms?: string | null + incotermsOption?: string | null + contractDeliveryDate?: string | null + shippingPort?: string | null + destinationPort?: string | null + isPriceAdjustmentApplicable?: boolean | null + sparePartOptions?: string | null + } + createdBy: number + updatedBy: number +}) { + try { + // 일반견적 생성 서버 액션 및 필요한 스키마 import + const { createGeneralRfqAction } = await import('@/lib/rfq-last/service') + const { rfqLastDetails, rfqLastVendorResponses, rfqLastVendorResponseHistory } = await import('@/db/schema') + + // 일반견적 생성 + const result = await createGeneralRfqAction({ + rfqType: input.rfqType, + rfqTitle: input.rfqTitle, + dueDate: input.dueDate, + picUserId: input.picUserId, + projectId: input.projectId, + remark: input.remark || '', + items: input.items.map(item => ({ + itemCode: item.itemCode, + itemName: item.itemName, + quantity: item.quantity, + uom: item.uom, + remark: item.remark, + materialCode: item.materialCode, + materialName: item.materialName, + })), + createdBy: input.createdBy, + updatedBy: input.updatedBy, + }) + + if (!result.success || !result.data) { + return { + success: false, + error: result.error || '사전견적용 일반견적 생성에 실패했습니다', + } + } + + const rfqId = result.data.id + const conditions = input.biddingConditions + + // 입찰 조건을 RFQ 조건으로 매핑 + const mapBiddingConditionsToRfqConditions = () => { + if (!conditions) { + return { + currency: 'KRW', + paymentTermsCode: undefined, + incotermsCode: undefined, + incotermsDetail: undefined, + deliveryDate: undefined, + taxCode: undefined, + placeOfShipping: undefined, + placeOfDestination: undefined, + materialPriceRelatedYn: false, + sparepartYn: false, + sparepartDescription: undefined, + } + } + + // contractDeliveryDate 문자열을 Date로 변환 (timestamp 타입용) + let deliveryDate: Date | undefined = undefined + if (conditions.contractDeliveryDate) { + try { + const date = new Date(conditions.contractDeliveryDate) + if (!isNaN(date.getTime())) { + deliveryDate = date + } + } catch (error) { + console.warn('Failed to parse contractDeliveryDate:', error) + } + } + + return { + currency: 'KRW', // 기본값 + paymentTermsCode: conditions.paymentTerms || undefined, + incotermsCode: conditions.incoterms || undefined, + incotermsDetail: conditions.incotermsOption || undefined, + deliveryDate: deliveryDate, // timestamp 타입 (rfqLastDetails용) + vendorDeliveryDate: deliveryDate, // date 타입 (rfqLastVendorResponses용) + taxCode: conditions.taxConditions || undefined, + placeOfShipping: conditions.shippingPort || undefined, + placeOfDestination: conditions.destinationPort || undefined, + materialPriceRelatedYn: conditions.isPriceAdjustmentApplicable ?? false, + sparepartYn: !!conditions.sparePartOptions, // sparePartOptions가 있으면 true + sparepartDescription: conditions.sparePartOptions || undefined, + } + } + + const rfqConditions = mapBiddingConditionsToRfqConditions() + + // 입찰에 참여한 업체 목록 조회 + const vendorsResult = await getBiddingVendors(input.biddingId) + if (!vendorsResult.success || !vendorsResult.data || vendorsResult.data.length === 0) { + return { + success: true, + message: '사전견적용 일반견적이 생성되었습니다. (참여 업체 없음)', + data: { + rfqCode: result.data.rfqCode, + rfqId: result.data.id, + }, + } + } + + // 각 업체에 대해 rfqLastDetails와 rfqLastVendorResponses 생성 + await db.transaction(async (tx) => { + for (const vendor of vendorsResult.data) { + if (!vendor.companyId) continue + + // 1. rfqLastDetails 생성 (구매자 제시 조건) + const [rfqDetail] = await tx + .insert(rfqLastDetails) + .values({ + rfqsLastId: rfqId, + vendorsId: vendor.companyId, + currency: rfqConditions.currency, + paymentTermsCode: rfqConditions.paymentTermsCode || null, + incotermsCode: rfqConditions.incotermsCode || null, + incotermsDetail: rfqConditions.incotermsDetail || null, + deliveryDate: rfqConditions.deliveryDate || null, + taxCode: rfqConditions.taxCode || null, + placeOfShipping: rfqConditions.placeOfShipping || null, + placeOfDestination: rfqConditions.placeOfDestination || null, + materialPriceRelatedYn: rfqConditions.materialPriceRelatedYn, + sparepartYn: rfqConditions.sparepartYn, + sparepartDescription: rfqConditions.sparepartDescription || null, + updatedBy: input.updatedBy, + createdBy: input.createdBy, + isLatest: true, + }) + .returning() + + // 2. rfqLastVendorResponses 생성 (초기 응답 레코드) + const [vendorResponse] = await tx + .insert(rfqLastVendorResponses) + .values({ + rfqsLastId: rfqId, + rfqLastDetailsId: rfqDetail.id, + vendorId: vendor.companyId, + status: '대기중', + responseVersion: 1, + isLatest: true, + participationStatus: '미응답', + currency: rfqConditions.currency, + // 구매자 제시 조건을 벤더 제안 조건의 초기값으로 복사 + vendorCurrency: rfqConditions.currency, + vendorPaymentTermsCode: rfqConditions.paymentTermsCode || null, + vendorIncotermsCode: rfqConditions.incotermsCode || null, + vendorIncotermsDetail: rfqConditions.incotermsDetail || null, + vendorDeliveryDate: rfqConditions.vendorDeliveryDate || null, + vendorTaxCode: rfqConditions.taxCode || null, + vendorPlaceOfShipping: rfqConditions.placeOfShipping || null, + vendorPlaceOfDestination: rfqConditions.placeOfDestination || null, + vendorMaterialPriceRelatedYn: rfqConditions.materialPriceRelatedYn, + vendorSparepartYn: rfqConditions.sparepartYn, + vendorSparepartDescription: rfqConditions.sparepartDescription || null, + createdBy: input.createdBy, + updatedBy: input.updatedBy, + }) + .returning() + + // 3. 이력 기록 + await tx + .insert(rfqLastVendorResponseHistory) + .values({ + vendorResponseId: vendorResponse.id, + action: '생성', + newStatus: '대기중', + changeDetails: { + action: '사전견적용 일반견적 생성', + biddingId: input.biddingId, + conditions: rfqConditions, + }, + performedBy: input.createdBy, + }) + } + }) + + return { + success: true, + message: `사전견적용 일반견적이 성공적으로 생성되었습니다. (${vendorsResult.data.length}개 업체 추가)`, + data: { + rfqCode: result.data.rfqCode, + rfqId: result.data.id, + }, + } + } catch (error) { + console.error('Failed to create pre-quote RFQ:', error) + return { + success: false, + error: error instanceof Error ? error.message : '사전견적용 일반견적 생성에 실패했습니다', + } + } +} + +// 일반견적 RFQ 코드 미리보기 (rfq-last/service에서 재사용) +export async function previewGeneralRfqCode(picUserId: number): Promise<string> { + try { + const { previewGeneralRfqCode: previewCode } = await import('@/lib/rfq-last/service') + return await previewCode(picUserId) + } catch (error) { + console.error('Failed to preview general RFQ code:', error) + return 'F???00001' + } +} + +// 내정가 산정 기준 업데이트 +export async function updateTargetPriceCalculationCriteria( + biddingId: number, + criteria: string, + userId: string +) { + try { + const userName = await getUserNameById(userId) + + await db + .update(biddings) + .set({ + targetPriceCalculationCriteria: criteria.trim() || null, + updatedAt: new Date(), + updatedBy: userName, + }) + .where(eq(biddings.id, biddingId)) + + revalidatePath(`/evcp/bid/${biddingId}`) + revalidatePath(`/evcp/bid/${biddingId}/items`) + + return { + success: true, + message: '내정가 산정 기준이 성공적으로 저장되었습니다.', + } + } catch (error) { + console.error('Failed to update target price calculation criteria:', error) + return { + success: false, + error: '내정가 산정 기준 저장에 실패했습니다.', + } + } +} + // 활성 템플릿 조회 서버 액션 export async function getActiveContractTemplates() { try { @@ -1443,4 +3073,829 @@ export async function searchVendorsForBidding(searchTerm: string = "", biddingId console.error('Error searching vendors for bidding:', error) return [] } +} + +// 차수증가 또는 재입찰 함수 +export async function increaseRoundOrRebid(biddingId: number, userId: string, type: 'round_increase' | 'rebidding') { + try { + const userName = await getUserNameById(userId) + + return await db.transaction(async (tx) => { + // 1. 기존 입찰 정보 조회 + const [existingBidding] = await tx + .select() + .from(biddings) + .where(eq(biddings.id, biddingId)) + .limit(1) + + if (!existingBidding) { + return { + success: false, + error: '입찰 정보를 찾을 수 없습니다.' + } + } + + // 2. 입찰번호 파싱 및 차수 증가 + const currentBiddingNumber = existingBidding.biddingNumber + + // 현재 입찰번호에서 차수 추출 (예: E00025-02 -> 02) + const match = currentBiddingNumber.match(/-(\d+)$/) + let currentRound = match ? parseInt(match[1]) : 1 + + let newBiddingNumber: string + + if (currentRound >= 3) { + // -03 이상이면 새로운 번호 생성 + newBiddingNumber = await generateBiddingNumber(existingBidding.contractType, userId, tx) + } else { + // -02까지는 차수만 증가 + const baseNumber = currentBiddingNumber.split('-')[0] + newBiddingNumber = `${baseNumber}-${String(currentRound + 1).padStart(2, '0')}` + } + + // 3. 새로운 입찰 생성 (기존 정보 복제) + const [newBidding] = await tx + .insert(biddings) + .values({ + biddingNumber: newBiddingNumber, + originalBiddingNumber: null, // 원입찰번호는 단순 정보이므로 null + revision: 0, + biddingSourceType: existingBidding.biddingSourceType, + + // 기본 정보 복제 + projectName: existingBidding.projectName, + itemName: existingBidding.itemName, + title: existingBidding.title, + description: existingBidding.description, + + // 계약 정보 복제 + contractType: existingBidding.contractType, + biddingType: existingBidding.biddingType, + awardCount: existingBidding.awardCount, + contractStartDate: existingBidding.contractStartDate, + contractEndDate: existingBidding.contractEndDate, + + // 일정은 초기화 (새로 설정해야 함) + preQuoteDate: null, + biddingRegistrationDate: new Date(), + submissionStartDate: null, + submissionEndDate: null, + evaluationDate: null, + + // 사양설명회 + hasSpecificationMeeting: existingBidding.hasSpecificationMeeting, + + // 예산 및 가격 정보 복제 + currency: existingBidding.currency, + budget: existingBidding.budget, + targetPrice: existingBidding.targetPrice, + targetPriceCalculationCriteria: existingBidding.targetPriceCalculationCriteria, + finalBidPrice: null, // 최종입찰가는 초기화 + + // PR 정보 복제 + prNumber: existingBidding.prNumber, + hasPrDocument: existingBidding.hasPrDocument, + + // 상태는 내정가 산정으로 초기화 + status: 'set_target_price', + isPublic: existingBidding.isPublic, + isUrgent: existingBidding.isUrgent, + + // 구매조직 + purchasingOrganization: existingBidding.purchasingOrganization, + + // 담당자 정보 복제 + bidPicId: existingBidding.bidPicId, + bidPicName: existingBidding.bidPicName, + bidPicCode: existingBidding.bidPicCode, + supplyPicId: existingBidding.supplyPicId, + supplyPicName: existingBidding.supplyPicName, + supplyPicCode: existingBidding.supplyPicCode, + + remarks: `${type === 'round_increase' ? '차수증가' : '재입찰'}`, + createdBy: userName, + updatedBy: userName, + ANFNR: existingBidding.ANFNR, + }) + .returning() + + // 4. 입찰 조건 복제 + const [existingConditions] = await tx + .select() + .from(biddingConditions) + .where(eq(biddingConditions.biddingId, biddingId)) + .limit(1) + + if (existingConditions) { + await tx + .insert(biddingConditions) + .values({ + biddingId: newBidding.id, + paymentTerms: existingConditions.paymentTerms, + taxConditions: existingConditions.taxConditions, + incoterms: existingConditions.incoterms, + incotermsOption: existingConditions.incotermsOption, + contractDeliveryDate: existingConditions.contractDeliveryDate, + shippingPort: existingConditions.shippingPort, + destinationPort: existingConditions.destinationPort, + isPriceAdjustmentApplicable: existingConditions.isPriceAdjustmentApplicable, + sparePartOptions: existingConditions.sparePartOptions, + }) + } + + // 5. PR 아이템 복제 + const existingPrItems = await tx + .select() + .from(prItemsForBidding) + .where(eq(prItemsForBidding.biddingId, biddingId)) + + if (existingPrItems.length > 0) { + await tx + .insert(prItemsForBidding) + .values( + existingPrItems.map((item) => ({ + biddingId: newBidding.id, + + // 기본 정보 + itemNumber: item.itemNumber, + projectId: item.projectId, + projectInfo: item.projectInfo, + itemInfo: item.itemInfo, + shi: item.shi, + prNumber: item.prNumber, + + // 자재 그룹 정보 + materialGroupNumber: item.materialGroupNumber, + materialGroupInfo: item.materialGroupInfo, + + // 자재 정보 + materialNumber: item.materialNumber, + materialInfo: item.materialInfo, + + // 납품 일정 + requestedDeliveryDate: item.requestedDeliveryDate, + + // 가격 정보 + annualUnitPrice: item.annualUnitPrice, + currency: item.currency, + + // 수량 및 중량 + quantity: item.quantity, + quantityUnit: item.quantityUnit, + totalWeight: item.totalWeight, + weightUnit: item.weightUnit, + + // 단위 정보 + priceUnit: item.priceUnit, + purchaseUnit: item.purchaseUnit, + materialWeight: item.materialWeight, + + // WBS 정보 + wbsCode: item.wbsCode, + wbsName: item.wbsName, + + // Cost Center 정보 + costCenterCode: item.costCenterCode, + costCenterName: item.costCenterName, + + // GL Account 정보 + glAccountCode: item.glAccountCode, + glAccountName: item.glAccountName, + + // 내정가 정보 + targetUnitPrice: item.targetUnitPrice, + targetAmount: item.targetAmount, + targetCurrency: item.targetCurrency, + + // 예산 정보 + budgetAmount: item.budgetAmount, + budgetCurrency: item.budgetCurrency, + + // 실적 정보 + actualAmount: item.actualAmount, + actualCurrency: item.actualCurrency, + + // SPEC 문서 여부 + hasSpecDocument: item.hasSpecDocument, + + createdAt: new Date(), + updatedAt: new Date(), + })) + ) + } + + // 6. 벤더 복제 (제출 정보는 초기화) + const existingCompanies = await tx + .select() + .from(biddingCompanies) + .where(eq(biddingCompanies.biddingId, biddingId)) + + if (existingCompanies.length > 0) { + await tx + .insert(biddingCompanies) + .values( + existingCompanies.map((company) => ({ + biddingId: newBidding.id, + companyId: company.companyId, + invitedAt: new Date(), + invitationStatus: 'pending' as const, // 초대 대기 상태로 초기화 + // 제출 정보는 초기화 + submittedAt: null, + quotationPrice: null, + quotationCurrency: null, + quotationValidityDays: null, + deliveryDate: null, + remarks: null, + })) + ) + } + + // 7. 사양설명회 정보 복제 (있는 경우) + if (existingBidding.hasSpecificationMeeting) { + const [existingMeeting] = await tx + .select() + .from(specificationMeetings) + .where(eq(specificationMeetings.biddingId, biddingId)) + .limit(1) + + if (existingMeeting) { + await tx + .insert(specificationMeetings) + .values({ + biddingId: newBidding.id, + meetingDate: existingMeeting.meetingDate, + meetingTime: existingMeeting.meetingTime, + location: existingMeeting.location, + address: existingMeeting.address, + contactPerson: existingMeeting.contactPerson, + contactPhone: existingMeeting.contactPhone, + contactEmail: existingMeeting.contactEmail, + agenda: existingMeeting.agenda, + materials: existingMeeting.materials, + notes: existingMeeting.notes, + isRequired: existingMeeting.isRequired, + }) + } + } + // 8. 입찰공고문 정보 복제 (있는 경우) + if (existingBidding.hasBiddingNotice) { + const [existingNotice] = await tx + .select() + .from(biddingNoticeTemplate) + .where(eq(biddingNoticeTemplate.biddingId, biddingId)) + .limit(1) + + if (existingNotice) { + await tx + .insert(biddingNoticeTemplate) + .values({ + biddingId: newBidding.id, + title: existingNotice.title, + content: existingNotice.content, + }) + } + } + + revalidatePath('/bidding') + revalidatePath(`/bidding/${newBidding.id}`) + + return { + success: true, + message: `${type === 'round_increase' ? '차수증가' : '재입찰'}가 완료되었습니다.`, + biddingId: newBidding.id, + biddingNumber: newBiddingNumber + } + }) + } catch (error) { + console.error('차수증가/재입찰 실패:', error) + return { + success: false, + error: error instanceof Error ? error.message : '차수증가/재입찰 중 오류가 발생했습니다.' + } + } +} + +/** + * 벤더의 담당자 목록 조회 + */ +export async function getVendorContactsByVendorId(vendorId: number) { + try { + const contacts = await db + .select({ + id: vendorContacts.id, + vendorId: vendorContacts.vendorId, + contactName: vendorContacts.contactName, + contactPosition: vendorContacts.contactPosition, + contactDepartment: vendorContacts.contactDepartment, + contactTask: vendorContacts.contactTask, + contactEmail: vendorContacts.contactEmail, + contactPhone: vendorContacts.contactPhone, + isPrimary: vendorContacts.isPrimary, + createdAt: vendorContacts.createdAt, + updatedAt: vendorContacts.updatedAt, + }) + .from(vendorContacts) + .where(eq(vendorContacts.vendorId, vendorId)) + .orderBy(vendorContacts.isPrimary, vendorContacts.contactName) + + return { + success: true, + data: contacts + } + } catch (error) { + console.error('Failed to get vendor contacts:', error) + return { + success: false, + error: error instanceof Error ? error.message : '담당자 목록 조회에 실패했습니다.' + } + } +} + +// ═══════════════════════════════════════════════════════════════ +// bid-receive 페이지용 함수들 +// ═══════════════════════════════════════════════════════════════ + +// bid-receive: 입찰서접수및마감 페이지용 입찰 목록 조회 +export async function getBiddingsForReceive(input: GetBiddingsSchema) { + try { + const offset = (input.page - 1) * input.perPage + + // 기본 필터 조건들 (입찰서접수및마감에 적합한 상태만) + const basicConditions: SQL<unknown>[] = [] + + // 입찰서 접수 및 마감과 관련된 상태만 필터링 + // 'received_quotation', 'bidding_opened', 'bidding_closed' 상태만 조회 + basicConditions.push( + or( + eq(biddings.status, 'received_quotation'), + eq(biddings.status, 'bidding_opened'), + eq(biddings.status, 'bidding_closed') + )! + ) + + if (input.biddingNumber) { + basicConditions.push(ilike(biddings.biddingNumber, `%${input.biddingNumber}%`)) + } + + if (input.status && input.status.length > 0) { + basicConditions.push( + or(...input.status.map(status => eq(biddings.status, status)))! + ) + } + + if (input.contractType && input.contractType.length > 0) { + basicConditions.push( + or(...input.contractType.map(type => eq(biddings.contractType, type)))! + ) + } + + if (input.prNumber) { + basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`)) + } + + if (input.managerName) { + basicConditions.push( + or( + ilike(biddings.bidPicName, `%${input.managerName}%`), + ilike(biddings.supplyPicName, `%${input.managerName}%`) + )! + ) + } + + // 날짜 필터들 + if (input.submissionDateFrom) { + basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom))) + } + if (input.submissionDateTo) { + basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo))) + } + + if (input.createdAtFrom) { + basicConditions.push(gte(biddings.createdAt, new Date(input.createdAtFrom))) + } + if (input.createdAtTo) { + basicConditions.push(lte(biddings.createdAt, new Date(input.createdAtTo))) + } + + const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined + + // 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined + if (input.search) { + const s = `%${input.search}%` + const searchConditions = [ + ilike(biddings.biddingNumber, s), + ilike(biddings.title, s), + ilike(biddings.projectName, s), + ilike(biddings.prNumber, s), + ilike(biddings.bidPicName, s), + ilike(biddings.supplyPicName, s), + ] + globalWhere = or(...searchConditions) + } + + const whereConditions: SQL<unknown>[] = [] + if (basicWhere) whereConditions.push(basicWhere) + if (globalWhere) whereConditions.push(globalWhere) + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined + + // 전체 개수 조회 + const totalResult = await db + .select({ count: count() }) + .from(biddings) + .where(finalWhere) + + const total = totalResult[0]?.count || 0 + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 } + } + + // 정렬 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof biddings.$inferSelect + return sort.desc ? desc(biddings[column]) : asc(biddings[column]) + }) + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(biddings.createdAt)) + } + + // bid-receive 페이지용 데이터 조회 (필요한 컬럼만 선택) + const data = await db + .select({ + // 기본 입찰 정보 + id: biddings.id, + biddingNumber: biddings.biddingNumber, + originalBiddingNumber: biddings.originalBiddingNumber, + title: biddings.title, + status: biddings.status, + contractType: biddings.contractType, + prNumber: biddings.prNumber, + submissionStartDate: biddings.submissionStartDate, + submissionEndDate: biddings.submissionEndDate, + bidPicName: biddings.bidPicName, + supplyPicName: biddings.supplyPicName, + createdBy: biddings.createdBy, + createdAt: biddings.createdAt, + updatedAt: biddings.updatedAt, + + // 참여 현황 집계 + participantExpected: sql<number>` + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = ${biddings.id} + ), 0) + `.as('participant_expected'), + + participantParticipated: sql<number>` + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = ${biddings.id} + AND invitation_status = 'bidding_submitted' + ), 0) + `.as('participant_participated'), + + participantDeclined: sql<number>` + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = ${biddings.id} + AND invitation_status IN ('bidding_declined', 'bidding_cancelled') + ), 0) + `.as('participant_declined'), + + participantPending: sql<number>` + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = ${biddings.id} + AND invitation_status IN ('pending', 'bidding_sent', 'bidding_accepted') + ), 0) + `.as('participant_pending'), + + // 개찰 정보 (bidding_opened 상태에서만 의미 있음) + openedAt: biddings.updatedAt, // 개찰일은 업데이트 일시로 대체 + openedBy: biddings.updatedBy, // 개찰자는 업데이트자로 대체 + }) + .from(biddings) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset) + + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount, total } + + } catch (err) { + console.error("Error in getBiddingsForReceive:", err) + return { data: [], pageCount: 0, total: 0 } + } +} + +// ═══════════════════════════════════════════════════════════════ +// bid-selection 페이지용 함수들 +// ═══════════════════════════════════════════════════════════════ + +// bid-selection: 개찰 이후 입찰가 및 정보 확인 페이지용 입찰 목록 조회 +export async function getBiddingsForSelection(input: GetBiddingsSchema) { + try { + const offset = (input.page - 1) * input.perPage + + // 기본 필터 조건들 (개찰 이후 상태만) + const basicConditions: SQL<unknown>[] = [] + + // 개찰 이후 상태만 필터링 + // 'bidding_opened', 'bidding_closed', 'evaluation_of_bidding', 'vendor_selected' 상태만 조회 + basicConditions.push( + or( + eq(biddings.status, 'bidding_opened'), + eq(biddings.status, 'bidding_closed'), + eq(biddings.status, 'evaluation_of_bidding'), + eq(biddings.status, 'vendor_selected') + )! + ) + + if (input.biddingNumber) { + basicConditions.push(ilike(biddings.biddingNumber, `%${input.biddingNumber}%`)) + } + + if (input.status && input.status.length > 0) { + basicConditions.push( + or(...input.status.map(status => eq(biddings.status, status)))! + ) + } + + if (input.contractType && input.contractType.length > 0) { + basicConditions.push( + or(...input.contractType.map(type => eq(biddings.contractType, type)))! + ) + } + + if (input.prNumber) { + basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`)) + } + + if (input.managerName) { + basicConditions.push( + or( + ilike(biddings.bidPicName, `%${input.managerName}%`), + ilike(biddings.supplyPicName, `%${input.managerName}%`) + )! + ) + } + + // 날짜 필터들 + if (input.submissionDateFrom) { + basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom))) + } + if (input.submissionDateTo) { + basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo))) + } + + if (input.createdAtFrom) { + basicConditions.push(gte(biddings.createdAt, new Date(input.createdAtFrom))) + } + if (input.createdAtTo) { + basicConditions.push(lte(biddings.createdAt, new Date(input.createdAtTo))) + } + + const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined + + // 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined + if (input.search) { + const s = `%${input.search}%` + const searchConditions = [ + ilike(biddings.biddingNumber, s), + ilike(biddings.title, s), + ilike(biddings.projectName, s), + ilike(biddings.prNumber, s), + ilike(biddings.bidPicName, s), + ilike(biddings.supplyPicName, s), + ] + globalWhere = or(...searchConditions) + } + + const whereConditions: SQL<unknown>[] = [] + if (basicWhere) whereConditions.push(basicWhere) + if (globalWhere) whereConditions.push(globalWhere) + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined + + // 전체 개수 조회 + const totalResult = await db + .select({ count: count() }) + .from(biddings) + .where(finalWhere) + + const total = totalResult[0]?.count || 0 + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 } + } + + // 정렬 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof biddings.$inferSelect + return sort.desc ? desc(biddings[column]) : asc(biddings[column]) + }) + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(biddings.createdAt)) + } + + // bid-selection 페이지용 데이터 조회 + const data = await db + .select({ + // 기본 입찰 정보 + id: biddings.id, + biddingNumber: biddings.biddingNumber, + originalBiddingNumber: biddings.originalBiddingNumber, + title: biddings.title, + status: biddings.status, + contractType: biddings.contractType, + prNumber: biddings.prNumber, + submissionStartDate: biddings.submissionStartDate, + submissionEndDate: biddings.submissionEndDate, + bidPicName: biddings.bidPicName, + supplyPicName: biddings.supplyPicName, + createdBy: biddings.createdBy, + createdAt: biddings.createdAt, + updatedAt: biddings.updatedAt, + }) + .from(biddings) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset) + + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount, total } + + } catch (err) { + console.error("Error in getBiddingsForSelection:", err) + return { data: [], pageCount: 0, total: 0 } + } +} + +// ═══════════════════════════════════════════════════════════════ +// bid-failure 페이지용 함수들 +// ═══════════════════════════════════════════════════════════════ + +// bid-failure: 유찰된 입찰 확인 페이지용 입찰 목록 조회 +export async function getBiddingsForFailure(input: GetBiddingsSchema) { + try { + const offset = (input.page - 1) * input.perPage + + // 기본 필터 조건들 (유찰된 입찰만) + const basicConditions: SQL<unknown>[] = [] + + // 유찰된 상태만 필터링 + basicConditions.push(eq(biddings.status, 'bidding_disposal')) + + if (input.biddingNumber) { + basicConditions.push(ilike(biddings.biddingNumber, `%${input.biddingNumber}%`)) + } + + if (input.status && input.status.length > 0) { + basicConditions.push( + or(...input.status.map(status => eq(biddings.status, status)))! + ) + } + + if (input.contractType && input.contractType.length > 0) { + basicConditions.push( + or(...input.contractType.map(type => eq(biddings.contractType, type)))! + ) + } + + if (input.prNumber) { + basicConditions.push(ilike(biddings.prNumber, `%${input.prNumber}%`)) + } + + if (input.managerName) { + basicConditions.push( + or( + ilike(biddings.bidPicName, `%${input.managerName}%`), + ilike(biddings.supplyPicName, `%${input.managerName}%`) + )! + ) + } + + // 날짜 필터들 + if (input.createdAtFrom) { + basicConditions.push(gte(biddings.createdAt, new Date(input.createdAtFrom))) + } + if (input.createdAtTo) { + basicConditions.push(lte(biddings.createdAt, new Date(input.createdAtTo))) + } + + if (input.submissionDateFrom) { + basicConditions.push(gte(biddings.submissionStartDate, new Date(input.submissionDateFrom))) + } + if (input.submissionDateTo) { + basicConditions.push(lte(biddings.submissionEndDate, new Date(input.submissionDateTo))) + } + + const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined + + // 글로벌 검색 조건 + let globalWhere: SQL<unknown> | undefined = undefined + if (input.search) { + const s = `%${input.search}%` + const searchConditions = [ + ilike(biddings.biddingNumber, s), + ilike(biddings.title, s), + ilike(biddings.projectName, s), + ilike(biddings.prNumber, s), + ilike(biddings.bidPicName, s), + ilike(biddings.supplyPicName, s), + ] + globalWhere = or(...searchConditions) + } + + const whereConditions: SQL<unknown>[] = [] + if (basicWhere) whereConditions.push(basicWhere) + if (globalWhere) whereConditions.push(globalWhere) + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined + + // 전체 개수 조회 + const totalResult = await db + .select({ count: count() }) + .from(biddings) + .where(finalWhere) + + const total = totalResult[0]?.count || 0 + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 } + } + + // 정렬 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id as keyof typeof biddings.$inferSelect + return sort.desc ? desc(biddings[column]) : asc(biddings[column]) + }) + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(biddings.updatedAt)) // 유찰된 최신순 + } + + // bid-failure 페이지용 데이터 조회 + const data = await db + .select({ + // 기본 입찰 정보 + id: biddings.id, + biddingNumber: biddings.biddingNumber, + originalBiddingNumber: biddings.originalBiddingNumber, + title: biddings.title, + status: biddings.status, + contractType: biddings.contractType, + prNumber: biddings.prNumber, + + // 가격 정보 + targetPrice: biddings.targetPrice, + currency: biddings.currency, + + // 일정 정보 + biddingRegistrationDate: biddings.biddingRegistrationDate, + submissionStartDate: biddings.submissionStartDate, + submissionEndDate: biddings.submissionEndDate, + + // 담당자 정보 + bidPicName: biddings.bidPicName, + supplyPicName: biddings.supplyPicName, + + // 유찰 정보 (업데이트 일시를 유찰일로 사용) + disposalDate: biddings.updatedAt, // 유찰일 + disposalUpdatedAt: biddings.updatedAt, // 폐찰수정일 + disposalUpdatedBy: biddings.updatedBy, // 폐찰수정자 + + // 기타 정보 + createdBy: biddings.createdBy, + createdAt: biddings.createdAt, + updatedAt: biddings.updatedAt, + updatedBy: biddings.updatedBy, + }) + .from(biddings) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset) + + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount, total } + + } catch (err) { + console.error("Error in getBiddingsForFailure:", err) + return { data: [], pageCount: 0, total: 0 } + } }
\ No newline at end of file |