diff options
Diffstat (limited to 'lib/bidding/service.ts')
| -rw-r--r-- | lib/bidding/service.ts | 646 |
1 files changed, 599 insertions, 47 deletions
diff --git a/lib/bidding/service.ts b/lib/bidding/service.ts index a658ee6a..ed20ad0c 100644 --- a/lib/bidding/service.ts +++ b/lib/bidding/service.ts @@ -18,6 +18,7 @@ import { vendorContacts, vendors } from '@/db/schema' +import { companyConditionResponses } from '@/db/schema/bidding' import { eq, desc, @@ -39,8 +40,11 @@ import { import { revalidatePath } from 'next/cache' import { filterColumns } from '@/lib/filter-columns' import { GetBiddingsSchema, CreateBiddingSchema } from './validation' -import { saveFile } from '../file-stroage' - +import { saveFile, saveBuffer } from '../file-stroage' +import { decryptBufferWithServerAction } from '@/components/drm/drmUtils' +import { getVendorPricesForBidding } from './detail/service' +import { getPrItemsForBidding } from './pre-quote/service' +import { checkChemicalSubstance, checkMultipleChemicalSubstances, type ChemicalSubstanceResult } from '@/lib/soap/ecc/send/chemical-substance-check' // 사용자 이메일로 사용자 코드 조회 @@ -59,6 +63,27 @@ export async function getUserCodeByEmail(email: string): Promise<string | null> } } +// 사용자 ID로 상세 정보 조회 (이름, 코드 등) +export async function getUserDetails(userId: number) { + try { + const user = await db + .select({ + id: users.id, + name: users.name, + userCode: users.userCode, + employeeNumber: users.employeeNumber + }) + .from(users) + .where(eq(users.id, userId)) + .limit(1) + + return user[0] || null + } catch (error) { + console.error('Failed to get user details:', error) + return null + } +} + // userId를 user.name으로 변환하는 유틸리티 함수 async function getUserNameById(userId: string): Promise<string> { try { @@ -419,9 +444,10 @@ export async function getBiddings(input: GetBiddingsSchema) { // 메타 정보 remarks: biddings.remarks, updatedAt: biddings.updatedAt, - updatedBy: biddings.updatedBy, + updatedBy: users.name, }) .from(biddings) + .leftJoin(users, sql`${biddings.updatedBy} = ${users.id}::varchar`) .where(finalWhere) .orderBy(...orderByColumns) .limit(input.perPage) @@ -846,7 +872,7 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { .insert(biddings) .values({ biddingNumber, - originalBiddingNumber: null, // 원입찰번호는 초기 생성이므로 아직 없음 + originalBiddingNumber: biddingNumber.split('-')[0], revision: input.revision || 0, // 프로젝트 정보 (PR 아이템에서 설정됨) @@ -872,7 +898,6 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { biddingRegistrationDate: new Date(), submissionStartDate: parseDate(input.submissionStartDate), submissionEndDate: parseDate(input.submissionEndDate), - evaluationDate: parseDate(input.evaluationDate), hasSpecificationMeeting: input.hasSpecificationMeeting || false, hasPrDocument: input.hasPrDocument || false, @@ -911,6 +936,7 @@ export async function createBidding(input: CreateBiddingInput, userId: string) { await tx.insert(biddingNoticeTemplate).values({ biddingId, title: input.title + ' 입찰공고', + type: input.noticeType || 'standard', content: input.content || standardContent, isTemplate: false, }) @@ -1721,7 +1747,6 @@ export async function updateBiddingBasicInfo( contractEndDate?: string submissionStartDate?: string submissionEndDate?: string - evaluationDate?: string hasSpecificationMeeting?: boolean hasPrDocument?: boolean currency?: string @@ -1779,9 +1804,23 @@ export async function updateBiddingBasicInfo( // 정의된 필드들만 업데이트 if (updates.title !== undefined) updateData.title = updates.title if (updates.description !== undefined) updateData.description = updates.description - if (updates.content !== undefined) updateData.content = updates.content + // content는 bidding 테이블에 컬럼이 없음, notice content는 별도로 저장해야 함 + // 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.content !== undefined) { + try { + await saveBiddingNotice(biddingId, { + title: (updates.title || '') + ' 입찰공고', // 제목이 없으면 기존 제목을 가져오거나 해야하는데, 여기서는 업데이트된 제목 사용 + content: updates.content + }) + } catch (e) { + console.error('Failed to save bidding notice content:', e) + // 공고 저장 실패는 전체 업데이트 실패로 처리하지 않음 (로그만 남김) + } + } if (updates.biddingType !== undefined) updateData.biddingType = updates.biddingType if (updates.biddingTypeCustom !== undefined) updateData.biddingTypeCustom = updates.biddingTypeCustom if (updates.awardCount !== undefined) updateData.awardCount = updates.awardCount @@ -1793,7 +1832,6 @@ export async function updateBiddingBasicInfo( 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 @@ -1877,12 +1915,14 @@ export async function updateBiddingBasicInfo( } } -// 입찰 일정 업데이트 +// 입찰 일정 업데이트 (오프셋 기반) export async function updateBiddingSchedule( biddingId: number, schedule: { - submissionStartDate?: string - submissionEndDate?: string + submissionStartOffset?: number // 시작일 오프셋 (결재 후 n일) + submissionStartTime?: string // 시작 시간 (HH:MM) + submissionDurationDays?: number // 기간 (시작일 + n일) + submissionEndTime?: string // 마감 시간 (HH:MM) remarks?: string isUrgent?: boolean hasSpecificationMeeting?: boolean @@ -1913,14 +1953,28 @@ export async function updateBiddingSchedule( return new Date(`${dateStr}:00+09:00`) } + // 시간 문자열(HH:MM)을 임시 timestamp로 변환 (1970-01-01 HH:MM:00 UTC) + // 결재 완료 시 실제 날짜로 계산됨 + const timeToTimestamp = (timeStr?: string): Date | null => { + if (!timeStr) return null + const [hours, minutes] = timeStr.split(':').map(Number) + const date = new Date(0) // 1970-01-01 00:00:00 UTC + date.setUTCHours(hours, minutes, 0, 0) + return date + } + return await db.transaction(async (tx) => { const updateData: any = { updatedAt: new Date(), updatedBy: userName, } - if (schedule.submissionStartDate !== undefined) updateData.submissionStartDate = parseDate(schedule.submissionStartDate) || null - if (schedule.submissionEndDate !== undefined) updateData.submissionEndDate = parseDate(schedule.submissionEndDate) || null + // 오프셋 기반 필드 저장 + if (schedule.submissionStartOffset !== undefined) updateData.submissionStartOffset = schedule.submissionStartOffset + if (schedule.submissionDurationDays !== undefined) updateData.submissionDurationDays = schedule.submissionDurationDays + // 시간은 timestamp 필드에 임시 저장 (1970-01-01 HH:MM:00) + if (schedule.submissionStartTime !== undefined) updateData.submissionStartDate = timeToTimestamp(schedule.submissionStartTime) + if (schedule.submissionEndTime !== undefined) updateData.submissionEndDate = timeToTimestamp(schedule.submissionEndTime) if (schedule.remarks !== undefined) updateData.remarks = schedule.remarks if (schedule.isUrgent !== undefined) updateData.isUrgent = schedule.isUrgent if (schedule.hasSpecificationMeeting !== undefined) updateData.hasSpecificationMeeting = schedule.hasSpecificationMeeting @@ -2196,7 +2250,7 @@ export async function updateBiddingProjectInfo(biddingId: number) { } // 입찰의 PR 아이템 금액 합산하여 bidding 업데이트 -async function updateBiddingAmounts(biddingId: number) { +export async function updateBiddingAmounts(biddingId: number) { try { // 해당 bidding의 모든 PR 아이템들의 금액 합계 계산 const amounts = await db @@ -2214,9 +2268,9 @@ async function updateBiddingAmounts(biddingId: number) { await db .update(biddings) .set({ - targetPrice: totalTargetAmount, - budget: totalBudgetAmount, - finalBidPrice: totalActualAmount, + targetPrice: String(totalTargetAmount), + budget: String(totalBudgetAmount), + finalBidPrice: String(totalActualAmount), updatedAt: new Date() }) .where(eq(biddings.id, biddingId)) @@ -2511,6 +2565,119 @@ export async function deleteBiddingCompanyContact(contactId: number) { } } +// 입찰담당자별 입찰 업체 조회 +export async function getBiddingCompaniesByBidPicId(bidPicId: number) { + try { + const companies = await db + .select({ + biddingId: biddings.id, + biddingNumber: biddings.biddingNumber, + biddingTitle: biddings.title, + companyId: biddingCompanies.companyId, + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + updatedAt: biddings.updatedAt, + }) + .from(biddings) + .innerJoin(biddingCompanies, eq(biddings.id, biddingCompanies.biddingId)) + .innerJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) + .where(eq(biddings.bidPicId, bidPicId)) + .orderBy(desc(biddings.updatedAt)) + + return { + success: true, + data: companies + } + } catch (error) { + console.error('Failed to get bidding companies by bidPicId:', error) + return { + success: false, + error: '입찰 업체 조회에 실패했습니다.', + data: [] + } + } +} + +// 입찰 업체를 현재 입찰에 추가 (담당자 정보 포함) +export async function addBiddingCompanyFromOtherBidding( + targetBiddingId: number, + sourceBiddingId: number, + companyId: number, + contacts?: Array<{ + contactName: string + contactEmail: string + contactNumber?: string + }> +) { + try { + return await db.transaction(async (tx) => { + // 중복 체크 + const existingCompany = await tx + .select() + .from(biddingCompanies) + .where( + and( + eq(biddingCompanies.biddingId, targetBiddingId), + eq(biddingCompanies.companyId, companyId) + ) + ) + .limit(1) + + if (existingCompany.length > 0) { + return { + success: false, + error: '이미 등록된 업체입니다.' + } + } + + // 1. biddingCompanies 레코드 생성 + const [biddingCompanyResult] = await tx + .insert(biddingCompanies) + .values({ + biddingId: targetBiddingId, + companyId: companyId, + invitationStatus: 'pending', + invitedAt: new Date(), + }) + .returning({ id: biddingCompanies.id }) + + if (!biddingCompanyResult) { + throw new Error('업체 추가에 실패했습니다.') + } + + // 2. 담당자 정보 추가 + if (contacts && contacts.length > 0) { + await tx.insert(biddingCompaniesContacts).values( + contacts.map(contact => ({ + biddingId: targetBiddingId, + vendorId: companyId, + contactName: contact.contactName, + contactEmail: contact.contactEmail, + contactNumber: contact.contactNumber || null, + })) + ) + } + + // 3. company_condition_responses 레코드 생성 + await tx.insert(companyConditionResponses).values({ + biddingCompanyId: biddingCompanyResult.id, + }) + + return { + success: true, + message: '업체가 성공적으로 추가되었습니다.', + data: { id: biddingCompanyResult.id } + } + }) + } catch (error) { + console.error('Failed to add bidding company from other bidding:', error) + return { + success: false, + error: error instanceof Error ? error.message : '업체 추가에 실패했습니다.' + } + } +} + export async function updateBiddingConditions( biddingId: number, updates: { @@ -2758,10 +2925,13 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u // 2. 입찰번호 생성 (타입에 따라 다르게 처리) let newBiddingNumber: string + let originalBiddingNumber: string if (type === 'rebidding') { // 재입찰: 완전히 새로운 입찰번호 생성 newBiddingNumber = await generateBiddingNumber(existingBidding.contractType, userId, tx) + // 재입찰시에도 원입찰번호는 새로 생성된 입찰번호로 셋팅 + originalBiddingNumber = newBiddingNumber.split('-')[0] } else { // 차수증가: 기존 입찰번호에서 차수 증가 const currentBiddingNumber = existingBidding.biddingNumber @@ -2771,16 +2941,18 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u let currentRound = match ? parseInt(match[1]) : 1 if (currentRound >= 3) { - // -03 이상이면 새로운 번호 생성 + // -03 이상이면 재입찰이며, 새로운 번호 생성 newBiddingNumber = await generateBiddingNumber(existingBidding.contractType, userId, tx) + // 새로 생성한 입찰번호를 원입찰번호로 셋팅 + originalBiddingNumber = newBiddingNumber.split('-')[0] } else { // -02까지는 차수만 증가 const baseNumber = currentBiddingNumber.split('-')[0] newBiddingNumber = `${baseNumber}-${String(currentRound + 1).padStart(2, '0')}` + // 차수증가의 경우에도 원입찰번호는 새로 생성한 입찰번호로 셋팅 + originalBiddingNumber = newBiddingNumber.split('-')[0] } } - //원입찰번호는 -0n 제외하고 저장 - const originalBiddingNumber = existingBidding.biddingNumber.split('-')[0] // 3. 새로운 입찰 생성 (기존 정보 복제) const [newBidding] = await tx @@ -2793,13 +2965,15 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u // 기본 정보 복제 projectName: existingBidding.projectName, + projectCode: existingBidding.projectCode, // 프로젝트 코드 복제 itemName: existingBidding.itemName, title: existingBidding.title, description: existingBidding.description, // 계약 정보 복제 contractType: existingBidding.contractType, - biddingType: existingBidding.biddingType, + noticeType: existingBidding.noticeType, // 공고타입 복제 + biddingType: existingBidding.biddingType, // 구매유형 복제 awardCount: existingBidding.awardCount, contractStartDate: existingBidding.contractStartDate, contractEndDate: existingBidding.contractEndDate, @@ -2809,7 +2983,6 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u biddingRegistrationDate: new Date(), submissionStartDate: null, submissionEndDate: null, - evaluationDate: null, // 사양설명회 hasSpecificationMeeting: existingBidding.hasSpecificationMeeting, @@ -2819,6 +2992,7 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u budget: existingBidding.budget, targetPrice: existingBidding.targetPrice, targetPriceCalculationCriteria: existingBidding.targetPriceCalculationCriteria, + actualPrice: existingBidding.actualPrice, finalBidPrice: null, // 최종입찰가는 초기화 // PR 정보 복제 @@ -2832,6 +3006,7 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u // 구매조직 purchasingOrganization: existingBidding.purchasingOrganization, + plant: existingBidding.plant, // 담당자 정보 복제 bidPicId: existingBidding.bidPicId, @@ -3074,8 +3249,6 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u .from(biddingDocuments) .where(and( eq(biddingDocuments.biddingId, biddingId), - // PR 아이템에 연결된 첨부파일은 제외 (SHI용과 협력업체용만 복제) - isNull(biddingDocuments.prItemId), // SHI용(evaluation_doc) 또는 협력업체용(company_proposal) 문서만 복제 or( eq(biddingDocuments.documentType, 'evaluation_doc'), @@ -3086,32 +3259,34 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u if (existingDocuments.length > 0) { for (const doc of existingDocuments) { try { - // 기존 파일을 Buffer로 읽어서 File 객체 생성 - const { readFileSync, existsSync } = await import('fs') + // 기존 파일 경로 확인 및 Buffer로 읽기 + const { readFile, access, constants } = await import('fs/promises') const { join } = await import('path') + // 파일 경로 정규화 const oldFilePath = doc.filePath.startsWith('/uploads/') ? join(process.cwd(), 'public', doc.filePath) + : doc.filePath.startsWith('/') + ? join(process.cwd(), 'public', doc.filePath) : doc.filePath - if (!existsSync(oldFilePath)) { - console.warn(`원본 파일이 존재하지 않음: ${oldFilePath}`) + // 파일 존재 여부 확인 + try { + await access(oldFilePath, constants.R_OK) + } catch { + console.warn(`원본 파일이 존재하지 않거나 읽을 수 없음: ${oldFilePath}`) continue } - // 파일 내용을 읽어서 Buffer 생성 - const fileBuffer = readFileSync(oldFilePath) - - // Buffer를 File 객체로 변환 (브라우저 File API 시뮬레이션) - const file = new File([fileBuffer], doc.fileName, { - type: doc.mimeType || 'application/octet-stream' - }) + // 파일 내용을 Buffer로 읽기 + const fileBuffer = await readFile(oldFilePath) - // saveFile을 사용하여 새 파일 저장 - const saveResult = await saveFile({ - file, + // saveBuffer를 사용하여 새 파일 저장 (File 객체 변환 없이 직접 저장) + const saveResult = await saveBuffer({ + buffer: fileBuffer, + fileName: doc.fileName, directory: `biddings/${newBidding.id}/attachments/${doc.documentType === 'evaluation_doc' ? 'shi' : 'vendor'}`, - originalName: `copied_${Date.now()}_${doc.fileName}`, + originalName: doc.originalFileName || doc.fileName, userId: userName }) @@ -3145,9 +3320,10 @@ export async function increaseRoundOrRebid(biddingId: number, userId: string | u } } - revalidatePath('/bidding') - revalidatePath(`/bidding/${biddingId}`) // 기존 입찰 페이지도 갱신 - revalidatePath(`/bidding/${newBidding.id}`) + revalidatePath('/bid-receive') + revalidatePath('/evcp/bid-receive') + revalidatePath('/evcp/bid') + revalidatePath(`/bid-receive/${biddingId}`) // 기존 입찰 페이지도 갱신 return { success: true, @@ -3436,9 +3612,10 @@ export async function getBiddingsForSelection(input: GetBiddingsSchema) { // 'bidding_opened', 'bidding_closed', 'evaluation_of_bidding', 'vendor_selected' 상태만 조회 basicConditions.push( or( - eq(biddings.status, 'bidding_closed'), eq(biddings.status, 'evaluation_of_bidding'), - eq(biddings.status, 'vendor_selected') + eq(biddings.status, 'vendor_selected'), + eq(biddings.status, 'round_increase'), + eq(biddings.status, 'rebidding'), )! ) @@ -3704,7 +3881,7 @@ export async function getBiddingsForFailure(input: GetBiddingsSchema) { // 유찰 정보 (업데이트 일시를 유찰일로 사용) disposalDate: biddings.updatedAt, // 유찰일 disposalUpdatedAt: biddings.updatedAt, // 폐찰수정일 - disposalUpdatedBy: biddings.updatedBy, // 폐찰수정자 + disposalUpdatedBy: users.name, // 폐찰수정자 // 폐찰 정보 closureReason: biddings.description, // 폐찰사유 @@ -3719,9 +3896,10 @@ export async function getBiddingsForFailure(input: GetBiddingsSchema) { createdBy: biddings.createdBy, createdAt: biddings.createdAt, updatedAt: biddings.updatedAt, - updatedBy: biddings.updatedBy, + updatedBy: users.name, }) .from(biddings) + .leftJoin(users, sql`${biddings.updatedBy} = ${users.id}::varchar`) .leftJoin(biddingDocuments, and( eq(biddingDocuments.biddingId, biddings.id), eq(biddingDocuments.documentType, 'evaluation_doc'), // 폐찰 문서 @@ -3791,4 +3969,378 @@ export async function getBiddingsForFailure(input: GetBiddingsSchema) { console.error("Error in getBiddingsForFailure:", err) return { data: [], pageCount: 0, total: 0 } } -}
\ No newline at end of file +} + + +export async function getBiddingSelectionItemsAndPrices(biddingId: number) { + try { + const [prItems, vendorPrices] = await Promise.all([ + getPrItemsForBidding(biddingId), + getVendorPricesForBidding(biddingId) + ]) + + return { + prItems, + vendorPrices + } + } catch (error) { + console.error('Failed to get bidding selection items and prices:', error) + throw error + } +} + +// ======================================== +// 화학물질 조회 및 저장 관련 함수들 +// ======================================== + +/** + * 입찰 참여업체의 화학물질 정보를 조회하고 DB에 저장 + */ +// export async function checkAndSaveChemicalSubstanceForBiddingCompany(biddingCompanyId: number) { +// try { +// // 입찰 참여업체 정보 조회 (벤더 정보 포함) +// const biddingCompanyInfo = await db +// .select({ +// id: biddingCompanies.id, +// biddingId: biddingCompanies.biddingId, +// companyId: biddingCompanies.companyId, +// hasChemicalSubstance: biddingCompanies.hasChemicalSubstance, +// vendors: { +// vendorCode: vendors.vendorCode +// } +// }) +// .from(biddingCompanies) +// .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) +// .where(eq(biddingCompanies.id, biddingCompanyId)) +// .limit(1) + +// if (!biddingCompanyInfo[0]) { +// throw new Error(`입찰 참여업체를 찾을 수 없습니다: ${biddingCompanyId}`) +// } + +// const companyInfo = biddingCompanyInfo[0] + +// // 이미 화학물질 검사가 완료된 경우 스킵 +// if (companyInfo.hasChemicalSubstance !== null && companyInfo.hasChemicalSubstance !== undefined) { +// console.log(`이미 화학물질 검사가 완료된 입찰 참여업체: ${biddingCompanyId}`) +// return { +// success: true, +// message: '이미 화학물질 검사가 완료되었습니다.', +// hasChemicalSubstance: companyInfo.hasChemicalSubstance +// } +// } + +// // 벤더 코드가 없는 경우 스킵 +// if (!companyInfo.vendors?.vendorCode) { +// console.log(`벤더 코드가 없는 입찰 참여업체: ${biddingCompanyId}`) +// return { +// success: false, +// message: '벤더 코드가 없습니다.' +// } +// } + +// // 입찰의 PR 아이템들 조회 (자재번호 있는 것만) +// const prItems = await db +// .select({ +// id: prItemsForBidding.id, +// materialNumber: prItemsForBidding.materialNumber +// }) +// .from(prItemsForBidding) +// .where(and( +// eq(prItemsForBidding.biddingId, companyInfo.biddingId), +// isNotNull(prItemsForBidding.materialNumber), +// sql`${prItemsForBidding.materialNumber} != ''` +// )) + +// if (prItems.length === 0) { +// console.log(`자재번호가 있는 PR 아이템이 없는 입찰: ${companyInfo.biddingId}`) +// return { +// success: false, +// message: '조회할 자재가 없습니다.' +// } +// } + +// // 각 자재에 대해 화학물질 조회 +// let hasAnyChemicalSubstance = false +// const results: Array<{ materialNumber: string; hasChemicalSubstance: boolean; message: string }> = [] + +// for (const prItem of prItems) { +// try { +// const checkResult = await checkChemicalSubstance({ +// bukrs: 'H100', // 회사코드는 H100 고정 +// werks: 'PM11', // WERKS는 PM11 고정 +// lifnr: companyInfo.vendors.vendorCode, +// matnr: prItem.materialNumber! +// }) + +// if (checkResult.success) { +// const itemHasChemical = checkResult.hasChemicalSubstance || false +// hasAnyChemicalSubstance = hasAnyChemicalSubstance || itemHasChemical + +// results.push({ +// materialNumber: prItem.materialNumber!, +// hasChemicalSubstance: itemHasChemical, +// message: checkResult.message || '조회 성공' +// }) +// } else { +// results.push({ +// materialNumber: prItem.materialNumber!, +// hasChemicalSubstance: false, +// message: checkResult.message || '조회 실패' +// }) +// } + +// // API 호출 간 지연 +// await new Promise(resolve => setTimeout(resolve, 500)) + +// } catch (error) { +// results.push({ +// materialNumber: prItem.materialNumber!, +// hasChemicalSubstance: false, +// message: error instanceof Error ? error.message : 'Unknown error' +// }) +// } +// } + +// // 하나라도 Y(Y=true)이면 true, 모두 N(false)이면 false +// const finalHasChemicalSubstance = hasAnyChemicalSubstance + +// // DB에 결과 저장 +// await db +// .update(biddingCompanies) +// .set({ +// hasChemicalSubstance: finalHasChemicalSubstance, +// updatedAt: new Date() +// }) +// .where(eq(biddingCompanies.id, biddingCompanyId)) + +// console.log(`화학물질 정보 저장 완료: 입찰 참여업체 ${biddingCompanyId}, 화학물질 ${finalHasChemicalSubstance ? '있음' : '없음'} (${results.filter(r => r.hasChemicalSubstance).length}/${results.length})`) + +// return { +// success: true, +// message: `화학물질 조회 및 저장이 완료되었습니다. (${results.filter(r => r.hasChemicalSubstance).length}/${results.length}개 자재에 화학물질 있음)`, +// hasChemicalSubstance: finalHasChemicalSubstance, +// results +// } + +// } catch (error) { +// console.error(`화학물질 조회 실패 (입찰 참여업체 ${biddingCompanyId}):`, error) +// return { +// success: false, +// message: error instanceof Error ? error.message : 'Unknown error', +// hasChemicalSubstance: null, +// results: [] +// } +// } +// } + +/** + * 입찰의 모든 참여업체에 대한 화학물질 정보를 일괄 조회하고 저장 + */ +export async function checkAndSaveChemicalSubstancesForBidding(biddingId: number) { + try { + const [biddingInfo] = await db + .select({ + id: biddings.id, + ANFNR: biddings.ANFNR, + plant: biddings.plant, + }) + .from(biddings) + .where(eq(biddings.id, biddingId)) + .limit(1) + + if (!biddingInfo) { + return { + success: false, + message: '입찰 정보를 찾을 수 없습니다.', + results: [] + } + } + + if (!biddingInfo.ANFNR) { + return { + success: true, + message: 'SAP PR 연동 입찰이 아니므로 화학물질 검사를 건너뜁니다.', + results: [] + } + } + + const biddingWerks = biddingInfo.plant?.trim() + if (!biddingWerks) { + return { + success: false, + message: '입찰의 플랜트(WERKS) 정보가 없어 화학물질 검사를 진행할 수 없습니다.', + results: [] + } + } + + // 입찰의 모든 참여업체 조회 (벤더 코드 있는 것만) + const biddingCompaniesList = await db + .select({ + id: biddingCompanies.id, + companyId: biddingCompanies.companyId, + hasChemicalSubstance: biddingCompanies.hasChemicalSubstance, + vendors: { + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName + } + }) + .from(biddingCompanies) + .leftJoin(vendors, eq(biddingCompanies.companyId, vendors.id)) + .where(and( + eq(biddingCompanies.biddingId, biddingId), + isNotNull(vendors.vendorCode), + sql`${vendors.vendorCode} != ''` + )) + + if (biddingCompaniesList.length === 0) { + return { + success: true, + message: '벤더 코드가 있는 참여업체가 없습니다.', + results: [] + } + } + + // 입찰의 PR 아이템들 조회 (자재번호 있는 것만) + const prItems = await db + .select({ + materialNumber: prItemsForBidding.materialNumber + }) + .from(prItemsForBidding) + .where(and( + eq(prItemsForBidding.biddingId, biddingId), + isNotNull(prItemsForBidding.materialNumber), + sql`${prItemsForBidding.materialNumber} != ''` + )) + + if (prItems.length === 0) { + return { + success: false, + message: '조회할 자재가 없습니다.', + results: [] + } + } + + const materialNumbers = prItems.map(item => item.materialNumber!).filter(Boolean) + + // 각 참여업체에 대해 화학물질 조회 + const results: Array<{ + biddingCompanyId: number; + vendorCode: string; + vendorName: string; + success: boolean; + hasChemicalSubstance?: boolean; + message: string; + materialResults?: Array<{ materialNumber: string; hasChemicalSubstance: boolean; message: string }>; + }> = [] + + for (const biddingCompany of biddingCompaniesList) { + try { + // 이미 검사가 완료된 경우 스킵 + if (biddingCompany.hasChemicalSubstance !== null && biddingCompany.hasChemicalSubstance !== undefined) { + results.push({ + biddingCompanyId: biddingCompany.id, + vendorCode: biddingCompany.vendors!.vendorCode!, + vendorName: biddingCompany.vendors!.vendorName || '', + success: true, + hasChemicalSubstance: biddingCompany.hasChemicalSubstance, + message: '이미 검사가 완료되었습니다.' + }) + continue + } + + // 각 자재에 대해 화학물질 조회 + let hasAnyChemicalSubstance = false + const materialResults: Array<{ materialNumber: string; hasChemicalSubstance: boolean; message: string }> = [] + + for (const materialNumber of materialNumbers) { + try { + const checkResult = await checkChemicalSubstance({ + bukrs: 'H100', // 회사코드는 H100 고정 + werks: biddingWerks, + lifnr: biddingCompany.vendors!.vendorCode!, + matnr: materialNumber + }) + + if (checkResult.success) { + const itemHasChemical = checkResult.hasChemicalSubstance || false + hasAnyChemicalSubstance = hasAnyChemicalSubstance || itemHasChemical + + materialResults.push({ + materialNumber, + hasChemicalSubstance: itemHasChemical, + message: checkResult.message || '조회 성공' + }) + } else { + materialResults.push({ + materialNumber, + hasChemicalSubstance: false, + message: checkResult.message || '조회 실패' + }) + } + + // API 호출 간 지연 + await new Promise(resolve => setTimeout(resolve, 500)) + + } catch (error) { + materialResults.push({ + materialNumber, + hasChemicalSubstance: false, + message: error instanceof Error ? error.message : 'Unknown error' + }) + } + } + + // 하나라도 Y이면 true, 모두 N이면 false + const finalHasChemicalSubstance = hasAnyChemicalSubstance + + // DB에 결과 저장 + await db + .update(biddingCompanies) + .set({ + hasChemicalSubstance: finalHasChemicalSubstance, + updatedAt: new Date() + }) + .where(eq(biddingCompanies.id, biddingCompany.id)) + + results.push({ + biddingCompanyId: biddingCompany.id, + vendorCode: biddingCompany.vendors!.vendorCode!, + vendorName: biddingCompany.vendors!.vendorName || '', + success: true, + hasChemicalSubstance: finalHasChemicalSubstance, + message: `조회 완료 (${materialResults.filter(r => r.hasChemicalSubstance).length}/${materialResults.length}개 자재에 화학물질 있음)`, + materialResults + }) + + } catch (error) { + results.push({ + biddingCompanyId: biddingCompany.id, + vendorCode: biddingCompany.vendors!.vendorCode!, + vendorName: biddingCompany.vendors!.vendorName || '', + success: false, + message: error instanceof Error ? error.message : 'Unknown error' + }) + } + } + + const successCount = results.filter(r => r.success).length + const totalCount = results.length + + console.log(`입찰 ${biddingId} 화학물질 일괄 조회 완료: ${successCount}/${totalCount} 성공`) + + return { + success: true, + message: `화학물질 일괄 조회 완료: ${successCount}/${totalCount} 성공`, + results + } + + } catch (error) { + console.error(`입찰 화학물질 일괄 조회 실패 (${biddingId}):`, error) + return { + success: false, + message: error instanceof Error ? error.message : 'Unknown error', + results: [] + } + } +} |