From d7585b3f2ea941ee807c1e87bbc833265a193c78 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 15 Sep 2025 10:14:09 +0000 Subject: (최겸) 구매 일반계약 및 상세, PO 전달 구현 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/general-contracts/service.ts | 657 ++++++++++++++++++++++++++++++++++++--- 1 file changed, 607 insertions(+), 50 deletions(-) (limited to 'lib/general-contracts/service.ts') diff --git a/lib/general-contracts/service.ts b/lib/general-contracts/service.ts index 6d9e5c39..7f95ae3d 100644 --- a/lib/general-contracts/service.ts +++ b/lib/general-contracts/service.ts @@ -1,15 +1,22 @@ 'use server' import { revalidatePath } from 'next/cache' -import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte, lt } from 'drizzle-orm' +import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte, lt, like, sql } from 'drizzle-orm' import db from '@/db/db' +import path from 'path' +import { promises as fs } from 'fs' import { generalContracts, generalContractItems, generalContractAttachments } from '@/db/schema/generalContract' +import { contracts, contractItems, contractEnvelopes, contractSigners } from '@/db/schema/contract' +import { basicContract, basicContractTemplates } from '@/db/schema/basicContractDocumnet' import { vendors } from '@/db/schema/vendors' import { users } from '@/db/schema/users' import { filterColumns } from '@/lib/filter-columns' import { saveDRMFile } from '@/lib/file-stroage' import { decryptWithServerAction } from '@/components/drm/drmUtils' +import { saveBuffer } from '@/lib/file-stroage' +import { v4 as uuidv4 } from 'uuid' import { GetGeneralContractsSchema } from './validation' +import { sendEmail } from '../mail/sendEmail' export async function getGeneralContracts(input: GetGeneralContractsSchema) { try { @@ -213,6 +220,7 @@ export async function getGeneralContracts(input: GetGeneralContractsSchema) { lastUpdatedAt: generalContracts.lastUpdatedAt, notes: generalContracts.notes, // Vendor info + vendorId: generalContracts.vendorId, vendorName: vendors.vendorName, vendorCode: vendors.vendorCode, // User info @@ -342,41 +350,11 @@ export async function getContractBasicInfo(id: number) { } } -// 계약번호 생성 함수 -async function generateContractNumber(registeredById: number, contractType: string): Promise { - // 발주담당자코드 3자리 (사용자 ID를 3자리로 패딩) - const managerCode = String(registeredById).padStart(3, '0') - - // 계약종류 2자리 (영문) - const typeCode = contractType.substring(0, 2).toUpperCase() - - // 일련번호 3자리 (현재 날짜 기준으로 생성) - const today = new Date() - - // 같은 날짜의 계약 개수 조회 - const startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate()) - const endOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1) - - const countResult = await db - .select({ count: count() }) - .from(generalContracts) - .where( - and( - gte(generalContracts.registeredAt, startOfDay), - lt(generalContracts.registeredAt, endOfDay) - ) - ) - - const sequenceNumber = String((countResult[0]?.count || 0) + 1).padStart(3, '0') - - return `C${managerCode}${typeCode}${sequenceNumber}` -} - export async function createContract(data: Record) { try { // 계약번호 자동 생성 - const contractNumber = data.contractNumber || await generateContractNumber( - data.registeredById as number, + // TODO: 구매 발주담당자 코드 필요 - 파라미터 추가 + const contractNumber = await generateContractNumber( data.type as string ) @@ -434,6 +412,7 @@ export async function createContract(data: Record) { notes: data.notes as string, }) .returning() + console.log(newContract,"newContract") revalidatePath('/general-contracts') @@ -489,6 +468,20 @@ export async function updateContractBasicInfo(id: number, data: Record { + if (value === null || value === undefined || value === '' || value === 'false') { + return null + } + const num = typeof value === 'string' ? parseFloat(value) : Number(value) + return isNaN(num) ? null : num + } + + // 날짜 필드에서 빈 문자열을 null로 변환 + const convertEmptyStringToNull = (value: unknown): string | null => { + return (value === '' || value === undefined) ? null : value as string + } + // 업데이트할 데이터 객체 생성 const updateData: Record = { specificationType, @@ -500,22 +493,22 @@ export async function updateContractBasicInfo(id: number, data: Record) } }) - // 숫자 필드들 추가 정리 + // 숫자 필드들 추가 정리 (vendorId는 NOT NULL이므로 null로 설정하지 않음) numericFields.forEach(field => { - if (cleanedData[field] === '' || cleanedData[field] === undefined || cleanedData[field] === 0) { - cleanedData[field] = null + if (field === 'vendorId') { + // vendorId는 필수 필드이므로 null로 설정하지 않음 + if (cleanedData[field] === '' || cleanedData[field] === undefined || cleanedData[field] === 0) { + // 유효하지 않은 값이면 에러 발생 + throw new Error('Vendor ID is required and cannot be null') + } + } else { + // 다른 숫자 필드들은 빈 값이면 null로 설정 + if (cleanedData[field] === '' || cleanedData[field] === undefined || cleanedData[field] === 0) { + cleanedData[field] = null + } } }) @@ -997,7 +1091,6 @@ export async function getVendors() { vendorCode: vendors.vendorCode, }) .from(vendors) - .where(eq(vendors.status, 'ACTIVE')) .orderBy(asc(vendors.vendorName)) return vendorList @@ -1023,13 +1116,13 @@ export async function uploadContractAttachment(contractId: number, file: File, u userId, ) - if (saveResult.success && saveResult.filePath) { + if (saveResult.success && saveResult.publicPath) { // generalContractAttachments 테이블에 저장 const [attachment] = await db.insert(generalContractAttachments).values({ contractId, documentName, fileName: saveResult.fileName || file.name, - filePath: saveResult.filePath, + filePath: saveResult.publicPath, uploadedById: userIdNumber, uploadedAt: new Date(), }).returning() @@ -1139,6 +1232,384 @@ export async function deleteContractAttachment(attachmentId: number, contractId: } } +// 계약승인요청용 파일 업로드 (DRM 사용) +export async function uploadContractApprovalFile(contractId: number, file: File, userId: string) { + try { + const userIdNumber = parseInt(userId) + if (isNaN(userIdNumber)) { + throw new Error('Invalid user ID') + } + + const saveResult = await saveDRMFile( + file, + decryptWithServerAction, + `general-contracts/${contractId}/approval-documents`, + userId, + ) + + if (saveResult.success && saveResult.publicPath) { + return { + success: true, + message: '파일이 성공적으로 업로드되었습니다.', + filePath: saveResult.publicPath, + fileName: saveResult.fileName || file.name + } + } else { + return { + success: false, + error: saveResult.error || '파일 저장에 실패했습니다.' + } + } + } catch (error) { + console.error('Failed to upload contract approval file:', error) + return { + success: false, + error: '파일 업로드에 실패했습니다.' + } + } +} + + + +// 계약승인요청 전송 +export async function sendContractApprovalRequest( + contractSummary: any, + pdfBuffer: Uint8Array, + documentType: string, + userId: string, + generatedBasicContracts?: Array<{ key: string; buffer: number[]; fileName: string }> +) { + try { + // contracts 테이블에 새 계약 생성 (generalContracts에서 contracts로 복사) + const contractData = await mapContractSummaryToDb(contractSummary) + + const [newContract] = await db.insert(contracts).values({ + ...contractData, + contractNo: contractData.contractNo || `GC-${Date.now()}`, // contractNumber 대신 contractNo 사용 + }).returning() + + const contractId = newContract.id + + // contractItems 테이블에 품목 정보 저장 (general-contract-items가 있을 때만) + if (contractSummary.items && contractSummary.items.length > 0) { + // 새 품목 추가 + for (const item of contractSummary.items) { + await db.insert(contractItems).values({ + contractId, + itemId: item.itemId || 2602, // 기본값 설정 + description: item.itemInfo || item.description || '', + quantity: Math.floor(Number(item.quantity) || 1), // 정수로 변환 + unitPrice: item.contractUnitPrice || item.unitPrice || 0, + taxRate: item.taxRate || 0, + taxAmount: item.taxAmount || 0, + totalLineAmount: item.contractAmount || item.totalLineAmount || 0, + remark: item.remark || '', + }) + } + } + + // PDF 버퍼를 saveBuffer 함수로 저장 + const fileId = uuidv4() + const fileName = `${fileId}.pdf` + + // PDF 버퍼를 Buffer로 변환 + let bufferData: Buffer + if (Buffer.isBuffer(pdfBuffer)) { + bufferData = pdfBuffer + } else if (pdfBuffer instanceof ArrayBuffer) { + bufferData = Buffer.from(pdfBuffer) + } else if (pdfBuffer instanceof Uint8Array) { + bufferData = Buffer.from(pdfBuffer) + } else { + bufferData = Buffer.from(pdfBuffer as any) + } + + // saveBuffer 함수를 사용해서 파일 저장 + const saveResult = await saveBuffer({ + buffer: bufferData, + fileName: fileName, + directory: "generalContracts", + originalName: `contract_${contractId}_${documentType}_${fileId}.pdf`, + userId: userId + }) + + if (!saveResult.success) { + throw new Error(saveResult.error || 'PDF 파일 저장에 실패했습니다.') + } + + const finalFileName = saveResult.fileName || fileName + const finalFilePath = saveResult.publicPath + ? saveResult.publicPath.replace('/api/files/', '') + : `/generalContracts/${fileName}` + + // contractEnvelopes 테이블에 서명할 PDF 파일 정보 저장 + const [newEnvelope] = await db.insert(contractEnvelopes).values({ + contractId: contractId, + envelopeId: `envelope_${contractId}_${Date.now()}`, + documentId: `document_${contractId}_${Date.now()}`, + envelopeStatus: 'PENDING', + fileName: finalFileName, + filePath: finalFilePath, + }).returning() + + // contractSigners 테이블에 벤더 서명자 정보 저장 + const vendorEmail = contractSummary.basicInfo?.vendorEmail || 'vendor@example.com' + const vendorName = contractSummary.basicInfo?.vendorName || '벤더' + + await db.insert(contractSigners).values({ + envelopeId: newEnvelope.id, + signerType: 'VENDOR', + signerEmail: vendorEmail, + signerName: vendorName, + signerPosition: '대표자', + signerStatus: 'PENDING', + }) + + // generalContractAttachments에 contractId 업데이트 (일반계약의 첨부파일들을 PO 계약과 연결) + const generalContractId = contractSummary.basicInfo?.id || contractSummary.id + if (generalContractId) { + await db.update(generalContractAttachments) + .set({ poContractId: contractId }) + .where(eq(generalContractAttachments.contractId, generalContractId)) + } + + // 기본계약 처리 (클라이언트에서 생성된 PDF 사용 또는 자동 생성) + await processGeneratedBasicContracts(contractSummary, contractId, userId, generatedBasicContracts) + + try { + sendEmail({ + to: contractSummary.basicInfo.vendorEmail, + subject: `계약승인요청`, + template: "contract-approval-request", + context: { + contractId: contractId, + loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/po`, + language: "ko", + }, + }) + } catch (error) { + console.error('계약승인요청 전송 오류:', error) + + } + //계약상태변경 + revalidatePath('/evcp/general-contracts') + revalidatePath('/evcp/general-contracts/detail') + revalidatePath('/evcp/general-contracts/detail/contract-approval-request-dialog') + revalidatePath('/evcp/general-contracts/detail/contract-approval-request-dialog') + + return { + success: true, + message: '계약승인요청이 성공적으로 전송되었습니다.', + pdfPath: saveResult.publicPath + } + + } catch (error: any) { + console.error('계약승인요청 전송 오류:', error) + + // 중복 계약 번호 오류 처리 + if (error.message && error.message.includes('duplicate key value violates unique constraint')) { + return { + success: false, + error: '이미 존재하는 계약번호입니다. 다른 계약번호를 사용해주세요.' + } + } + + // 다른 데이터베이스 오류 처리 + if (error.code === '23505') { // PostgreSQL unique constraint violation + return { + success: false, + error: '중복된 데이터가 존재합니다. 입력값을 확인해주세요.' + } + } + + return { + success: false, + error: `계약승인요청 전송 중 오류가 발생했습니다: ${error.message}` + } + } +} + +// 클라이언트에서 생성된 기본계약 처리 (RFQ-Last 방식) +async function processGeneratedBasicContracts( + contractSummary: any, + contractId: number, + userId: string, + generatedBasicContracts: Array<{ key: string; buffer: number[]; fileName: string }> +): Promise { + try { + const userIdNumber = parseInt(userId) + if (isNaN(userIdNumber)) { + throw new Error('Invalid user ID') + } + + console.log(`${generatedBasicContracts.length}개의 클라이언트 생성 기본계약을 처리합니다.`) + + // 기본계약 디렉토리 생성 (RFQ-Last 방식) + const nasPath = process.env.NAS_PATH || "/evcp_nas" + const isProduction = process.env.NODE_ENV === "production" + const baseDir = isProduction ? nasPath : path.join(process.cwd(), "public") + const contractsDir = path.join(baseDir, "basicContracts") + await fs.mkdir(contractsDir, { recursive: true }) + + for (const contractData of generatedBasicContracts) { + try { + console.log(contractSummary.basicInfo?.vendorId || 'unknown', contractData.buffer.length) + + // PDF 버퍼를 Buffer로 변환 및 파일 저장 + const pdfBuffer = Buffer.from(contractData.buffer) + const fileName = contractData.fileName + const filePath = path.join(contractsDir, fileName) + + await fs.writeFile(filePath, pdfBuffer) + + // key에서 템플릿 정보 추출 (vendorId_type_templateName 형식) + const keyParts = contractData.key.split('_') + const vendorId = parseInt(keyParts[0]) + const contractType = keyParts[1] + const templateName = keyParts.slice(2).join('_') + + // 템플릿 조회 + const template = await getTemplateByName(templateName) + + console.log("템플릿", templateName, template) + + if (template) { + // 웹 접근 경로 설정 (RFQ-Last 방식) + let filePublicPath: string + if (isProduction) { + filePublicPath = `/api/files/basicContracts/${fileName}` + } else { + filePublicPath = `/basicContracts/${fileName}` + } + + // basicContract 테이블에 저장 + const deadline = new Date() + deadline.setDate(deadline.getDate() + 10) // 10일 후 마감 + + await db.insert(basicContract).values({ + templateId: template.id, + vendorId: vendorId, + requestedBy: userIdNumber, + generalContractId: contractSummary.basicInfo?.id || contractSummary.id, + fileName: fileName, + filePath: filePublicPath, + deadline: deadline.toISOString().split('T')[0], // YYYY-MM-DD 형식으로 + status: 'PENDING' + }) + + console.log(`클라이언트 생성 기본계약 저장 완료:${contractData.fileName}`) + } else { + console.error(`템플릿을 찾을 수 없음: ${templateName}`) + } + + } catch (error) { + console.error(`기본계약 처리 실패 (${contractData.fileName}):`, error) + // 개별 계약서 처리 실패는 전체 프로세스를 중단하지 않음 + } + } + + } catch (error) { + console.error('클라이언트 생성 기본계약 처리 중 오류:', error) + // 기본계약 생성 실패는 계약 승인 요청 전체를 실패시키지 않음 + } +} + +// 템플릿명으로 템플릿 조회 (RFQ-Last 방식) +async function getTemplateByName(templateName: string) { + const [template] = await db + .select() + .from(basicContractTemplates) + .where( + and( + ilike(basicContractTemplates.templateName, `%${templateName}%`), + eq(basicContractTemplates.status, "ACTIVE") + ) + ) + .limit(1) + + return template +} + +async function mapContractSummaryToDb(contractSummary: any) { + const basicInfo = contractSummary.basicInfo || {} + + // 계약번호 생성 + const contractNumber = await generateContractNumber( + basicInfo.contractType || basicInfo.type || 'UP', + basicInfo.purchaseManagerCode + ) + + return { + // 기본 정보 + projectId: basicInfo.projectId || null, // 기본값 설정 + vendorId: basicInfo.vendorId, + contractNo: contractNumber, + contractName: basicInfo.contractName || '계약승인요청', + status: 'PENDING_APPROVAL', + + // 계약 기간 + startDate: basicInfo.startDate || new Date().toISOString().split('T')[0], + endDate: basicInfo.endDate || new Date().toISOString().split('T')[0], + + // 지급/인도 조건 + paymentTerms: basicInfo.paymentTerm || '', + deliveryTerms: basicInfo.deliveryTerm || '', + deliveryDate: basicInfo.contractDeliveryDate || basicInfo.deliveryDate || new Date().toISOString().split('T')[0], + shippmentPlace: basicInfo.shippingLocation || basicInfo.shippmentPlace || '', + deliveryLocation: basicInfo.dischargeLocation || basicInfo.deliveryLocation || '', + + // 금액 정보 + budgetAmount: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0), + budgetCurrency: basicInfo.currency || basicInfo.contractCurrency || 'USD', + totalAmountKrw: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0), + currency: basicInfo.currency || basicInfo.contractCurrency || 'USD', + totalAmount: Number(basicInfo.totalAmount || basicInfo.contractAmount || 0), + + // SAP ECC 관련 필드들 + poVersion: basicInfo.revision || 1, + purchaseDocType: basicInfo.type || 'UP', + purchaseOrg: basicInfo.purchaseOrg || '', + purchaseGroup: basicInfo.purchaseGroup || '', + exchangeRate: Number(basicInfo.exchangeRate || 1), + + // 계약/보증 관련 + contractGuaranteeCode: basicInfo.contractGuaranteeCode || '', + defectGuaranteeCode: basicInfo.defectGuaranteeCode || '', + guaranteePeriodCode: basicInfo.guaranteePeriodCode || '', + advancePaymentYn: basicInfo.advancePaymentYn || 'N', + + // 전자계약/승인 관련 + electronicContractYn: basicInfo.electronicContractYn || 'Y', + electronicApprovalDate: basicInfo.electronicApprovalDate || null, + electronicApprovalTime: basicInfo.electronicApprovalTime || '', + ownerApprovalYn: basicInfo.ownerApprovalYn || 'N', + + // 기타 + plannedInOutFlag: basicInfo.plannedInOutFlag || 'I', + settlementStandard: basicInfo.settlementStandard || 'A', + weightSettlementFlag: basicInfo.weightSettlementFlag || 'N', + + // 연동제 관련 + priceIndexYn: basicInfo.priceIndexYn || 'N', + writtenContractNo: basicInfo.contractNumber || '', + contractVersion: basicInfo.revision || 1, + + // 부분 납품/결제 + partialShippingAllowed: basicInfo.partialShippingAllowed || false, + partialPaymentAllowed: basicInfo.partialPaymentAllowed || false, + + // 메모 + remarks: basicInfo.notes || basicInfo.remarks || '', + + // 버전 관리 + version: basicInfo.revision || 1, + + // 타임스탬프 (contracts 테이블 스키마에 맞게) + createdAt: new Date(), + updatedAt: new Date() + } +} + // Field Service Rate 관련 서버 액션들 export async function getFieldServiceRate(contractId: number) { try { @@ -1169,8 +1640,8 @@ export async function updateFieldServiceRate( .update(generalContracts) .set({ fieldServiceRates: fieldServiceRateData, - updatedAt: new Date(), - updatedBy: userId + lastUpdatedAt: new Date(), + lastUpdatedById: userId }) .where(eq(generalContracts.id, contractId)) @@ -1212,8 +1683,8 @@ export async function updateOffsetDetails( .update(generalContracts) .set({ offsetDetails: offsetDetailsData, - updatedAt: new Date(), - updatedBy: userId + lastUpdatedAt: new Date(), + lastUpdatedById: userId }) .where(eq(generalContracts.id, contractId)) @@ -1224,3 +1695,89 @@ export async function updateOffsetDetails( throw new Error('회입/상계내역 업데이트에 실패했습니다.') } } + +// 계약번호 생성 함수 +export async function generateContractNumber( + contractType: string, + purchaseManagerCode?: string +): Promise { + try { + // 계약종류 매핑 (2자리) - GENERAL_CONTRACT_TYPES 상수 사용 + const contractTypeMap: Record = { + 'UP': 'UP', // 자재단가계약 + 'LE': 'LE', // 임대차계약 + 'IL': 'IL', // 개별운송계약 + 'AL': 'AL', // 연간운송계약 + 'OS': 'OS', // 외주용역계약 + 'OW': 'OW', // 도급계약 + 'IS': 'IS', // 검사계약 + 'LO': 'LO', // LOI (의향서) + 'FA': 'FA', // FA (Frame Agreement) + 'SC': 'SC', // 납품합의계약 (Supply Contract) + 'OF': 'OF', // 클레임상계계약 (Offset Agreement) + 'AW': 'AW', // 사전작업합의 (Advanced Work) + 'AD': 'AD', // 사전납품합의 (Advanced Delivery) + 'AM': 'AM', // 설계계약 + 'SC_SELL': 'SC' // 폐기물매각계약 (Scrap) - 납품합의계약과 동일한 코드 사용 + } + + const typeCode = contractTypeMap[contractType] || 'XX' // 기본값 + // 발주담당자 코드 처리 + let managerCode: string + if (purchaseManagerCode && purchaseManagerCode.length >= 3) { + // 발주담당자 코드가 있으면 3자리 사용 + managerCode = purchaseManagerCode.substring(0, 3).toUpperCase() + } else { + // 발주담당자 코드가 없으면 일련번호로 대체 (001부터 시작) + const currentYear = new Date().getFullYear() + const prefix = `C${typeCode}${currentYear.toString().slice(-2)}` + + // 해당 패턴으로 시작하는 계약번호 중 가장 큰 일련번호 찾기 + const existingContracts = await db + .select({ contractNumber: generalContracts.contractNumber }) + .from(generalContracts) + .where(like(generalContracts.contractNumber, `${prefix}%`)) + .orderBy(desc(generalContracts.contractNumber)) + .limit(1) + + let sequenceNumber = 1 + if (existingContracts.length > 0) { + const lastContractNumber = existingContracts[0].contractNumber + const lastSequence = parseInt(lastContractNumber.slice(-3)) + sequenceNumber = lastSequence + 1 + } + + // 일련번호를 3자리로 포맷팅 + managerCode = sequenceNumber.toString().padStart(3, '0') + } + + // 일련번호 생성 (3자리) + const currentYear = new Date().getFullYear() + const prefix = `C${managerCode}${typeCode}${currentYear.toString().slice(-2)}` + + // 해당 패턴으로 시작하는 계약번호 중 가장 큰 일련번호 찾기 + const existingContracts = await db + .select({ contractNumber: generalContracts.contractNumber }) + .from(generalContracts) + .where(like(generalContracts.contractNumber, `${prefix}%`)) + .orderBy(desc(generalContracts.contractNumber)) + .limit(1) + + let sequenceNumber = 1 + if (existingContracts.length > 0) { + const lastContractNumber = existingContracts[0].contractNumber + const lastSequence = parseInt(lastContractNumber.slice(-3)) + sequenceNumber = lastSequence + 1 + } + + // 최종 계약번호 생성: C + 발주담당자코드(3자리) + 계약종류(2자리) + 연도(2자리) + 일련번호(3자리) + const finalSequence = sequenceNumber.toString().padStart(3, '0') + const contractNumber = `C${managerCode}${typeCode}${currentYear.toString().slice(-2)}${finalSequence}` + + return contractNumber + + } catch (error) { + console.error('계약번호 생성 오류:', error) + throw new Error('계약번호 생성에 실패했습니다.') + } +} -- cgit v1.2.3