From ba8cd44a0ed2c613a5f2cee06bfc9bd0f61f21c7 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 7 Nov 2025 08:39:04 +0000 Subject: (최겸) 입찰/견적 수정사항 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/general-contracts_old/service.ts | 1933 ++++++++++++++++++++++++++++++++++ 1 file changed, 1933 insertions(+) create mode 100644 lib/general-contracts_old/service.ts (limited to 'lib/general-contracts_old/service.ts') diff --git a/lib/general-contracts_old/service.ts b/lib/general-contracts_old/service.ts new file mode 100644 index 00000000..2422706a --- /dev/null +++ b/lib/general-contracts_old/service.ts @@ -0,0 +1,1933 @@ +'use server' + +import { revalidatePath } from 'next/cache' +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 { projects } from '@/db/schema/projects' +import { items } from '@/db/schema/items' +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 { + const offset = (input.page - 1) * input.perPage + + console.log(input.filters) + console.log(input.sort) + + // ✅ 1) 고급 필터 조건 + let advancedWhere: SQL | undefined = undefined + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: generalContracts, + filters: input.filters as any, + joinOperator: input.joinOperator || 'and', + }) + } + + // ✅ 2) 기본 필터 조건들 + const basicConditions: SQL[] = [] + + if (input.contractNumber) { + basicConditions.push(ilike(generalContracts.contractNumber, `%${input.contractNumber}%`)) + } + + if (input.name) { + basicConditions.push(ilike(generalContracts.name, `%${input.name}%`)) + } + + if (input.status && input.status.length > 0) { + basicConditions.push( + or(...input.status.map(status => eq(generalContracts.status, status)))! + ) + } + + if (input.category && input.category.length > 0) { + basicConditions.push( + or(...input.category.map(category => eq(generalContracts.category, category)))! + ) + } + + if (input.type && input.type.length > 0) { + basicConditions.push( + or(...input.type.map(type => eq(generalContracts.type, type)))! + ) + } + + if (input.executionMethod && input.executionMethod.length > 0) { + basicConditions.push( + or(...input.executionMethod.map(method => eq(generalContracts.executionMethod, method)))! + ) + } + + if (input.contractSourceType && input.contractSourceType.length > 0) { + basicConditions.push( + or(...input.contractSourceType.map(method => eq(generalContracts.contractSourceType, method)))! + ) + } + + if (input.vendorId && input.vendorId > 0) { + basicConditions.push(eq(generalContracts.vendorId, input.vendorId)) + } + + if (input.managerName) { + basicConditions.push(ilike(users.name, `%${input.managerName}%`)) + } + + // 날짜 필터들 + if (input.registeredAtFrom) { + basicConditions.push(gte(generalContracts.registeredAt, new Date(input.registeredAtFrom))) + } + if (input.registeredAtTo) { + basicConditions.push(lte(generalContracts.registeredAt, new Date(input.registeredAtTo))) + } + + if (input.signedAtFrom) { + basicConditions.push(gte(generalContracts.signedAt, new Date(input.signedAtFrom))) + } + if (input.signedAtTo) { + basicConditions.push(lte(generalContracts.signedAt, new Date(input.signedAtTo))) + } + + if (input.startDateFrom) { + basicConditions.push(gte(generalContracts.startDate, new Date(input.startDateFrom))) + } + if (input.startDateTo) { + basicConditions.push(lte(generalContracts.startDate, new Date(input.startDateTo))) + } + + if (input.endDateFrom) { + basicConditions.push(gte(generalContracts.endDate, new Date(input.endDateFrom))) + } + if (input.endDateTo) { + basicConditions.push(lte(generalContracts.endDate, new Date(input.endDateTo))) + } + + // 금액 필터들 + if (input.contractAmountMin) { + basicConditions.push(gte(generalContracts.contractAmount, parseFloat(input.contractAmountMin))) + } + if (input.contractAmountMax) { + basicConditions.push(lte(generalContracts.contractAmount, parseFloat(input.contractAmountMax))) + } + + const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined + + // ✅ 3) 글로벌 검색 조건 + let globalWhere: SQL | undefined = undefined + if (input.search) { + const s = `%${input.search}%` + const searchConditions = [ + ilike(generalContracts.contractNumber, s), + ilike(generalContracts.name, s), + ilike(generalContracts.notes, s), + ilike(vendors.vendorName, s), + ilike(users.name, s), + ilike(generalContracts.linkedPoNumber, s), + ilike(generalContracts.linkedRfqOrItb, s), + ilike(generalContracts.linkedBidNumber, s), + ] + globalWhere = or(...searchConditions) + } + + // ✅ 4) 최종 WHERE 조건 + const whereConditions: SQL[] = [] + if (advancedWhere) whereConditions.push(advancedWhere) + if (basicWhere) whereConditions.push(basicWhere) + if (globalWhere) whereConditions.push(globalWhere) + + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined + + // ✅ 5) 전체 개수 조회 + const totalResult = await db + .select({ count: count() }) + .from(generalContracts) + .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id)) + .leftJoin(users, eq(generalContracts.registeredById, users.id)) + .where(finalWhere) + + const total = totalResult[0]?.count || 0 + + if (total === 0) { + return { data: [], pageCount: 0, total: 0 } + } + + console.log("Total contracts:", total) + + // ✅ 6) 정렬 및 페이징 + const orderByColumns: any[] = [] + + for (const sort of input.sort) { + const column = sort.id + + // generalContracts 테이블의 컬럼들 + if (column in generalContracts) { + const contractColumn = generalContracts[column as keyof typeof generalContracts] + orderByColumns.push(sort.desc ? desc(contractColumn) : asc(contractColumn)) + } + // vendors 테이블의 컬럼들 + else if (column === 'vendorName' || column === 'vendorCode') { + const vendorColumn = vendors[column as keyof typeof vendors] + orderByColumns.push(sort.desc ? desc(vendorColumn) : asc(vendorColumn)) + } + // users 테이블의 컬럼들 + else if (column === 'managerName' || column === 'lastUpdatedByName') { + const userColumn = users.name + orderByColumns.push(sort.desc ? desc(userColumn) : asc(userColumn)) + } + } + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(generalContracts.registeredAt)) + } + + // ✅ 7) 메인 쿼리 + const data = await db + .select({ + id: generalContracts.id, + contractNumber: generalContracts.contractNumber, + revision: generalContracts.revision, + status: generalContracts.status, + category: generalContracts.category, + type: generalContracts.type, + executionMethod: generalContracts.executionMethod, + name: generalContracts.name, + contractSourceType: generalContracts.contractSourceType, + startDate: generalContracts.startDate, + endDate: generalContracts.endDate, + validityEndDate: generalContracts.validityEndDate, + contractScope: generalContracts.contractScope, + specificationType: generalContracts.specificationType, + specificationManualText: generalContracts.specificationManualText, + contractAmount: generalContracts.contractAmount, + totalAmount: generalContracts.totalAmount, + currency: generalContracts.currency, + registeredAt: generalContracts.registeredAt, + signedAt: generalContracts.signedAt, + linkedRfqOrItb: generalContracts.linkedRfqOrItb, + linkedPoNumber: generalContracts.linkedPoNumber, + linkedBidNumber: generalContracts.linkedBidNumber, + lastUpdatedAt: generalContracts.lastUpdatedAt, + notes: generalContracts.notes, + // Vendor info + vendorId: generalContracts.vendorId, + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + // Project info + projectId: generalContracts.projectId, + projectName: projects.name, + projectCode: projects.code, + // User info + managerName: users.name, + lastUpdatedByName: users.name, + }) + .from(generalContracts) + .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id)) + .leftJoin(users, eq(generalContracts.registeredById, users.id)) + .leftJoin(projects, eq(generalContracts.projectId, projects.id)) + .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 getGeneralContracts:", err) + return { data: [], pageCount: 0, total: 0 } + } +} + +export async function getContractById(id: number) { + try { + // ID 유효성 검사 + if (!id || isNaN(id) || id <= 0) { + throw new Error('Invalid contract ID') + } + + const contract = await db + .select() + .from(generalContracts) + .where(eq(generalContracts.id, id)) + .limit(1) + + if (!contract.length) { + throw new Error('Contract not found') + } + + // Get contract items + const items = await db + .select() + .from(generalContractItems) + .where(eq(generalContractItems.contractId, id)) + + // Get contract attachments + const attachments = await db + .select() + .from(generalContractAttachments) + .where(eq(generalContractAttachments.contractId, id)) + + // Get vendor info + const vendor = await db + .select() + .from(vendors) + .where(eq(vendors.id, contract[0].vendorId)) + .limit(1) + + // Get project info + const project = contract[0].projectId ? await db + .select() + .from(projects) + .where(eq(projects.id, contract[0].projectId)) + .limit(1) : null + + // Get manager info + const manager = await db + .select() + .from(users) + .where(eq(users.id, contract[0].registeredById)) + .limit(1) + + return { + ...contract[0], + contractItems: items, + attachments, + vendor: vendor[0] || null, + vendorCode: vendor[0]?.vendorCode || null, + vendorName: vendor[0]?.vendorName || null, + project: project ? project[0] : null, + projectName: project ? project[0].name : null, + projectCode: project ? project[0].code : null, + manager: manager[0] || null + } + } catch (error) { + console.error('Error fetching contract by ID:', error) + throw new Error('Failed to fetch contract') + } +} + +export async function getContractBasicInfo(id: number) { + try { + const [contract] = await db + .select() + .from(generalContracts) + .where(eq(generalContracts.id, id)) + .limit(1) + + if (!contract) { + return null + } + + // JSON 필드를 문자열에서 객체로 변환하여 클라이언트에서 사용하기 쉽게 만듭니다. + // Drizzle ORM이 JSONB 타입을 처리하지만, 명확성을 위해 명시적으로 파싱하는 것이 좋습니다. + const parsedContract = { + ...contract, + warrantyPeriod: contract.warrantyPeriod as any, + paymentBeforeDelivery: contract.paymentBeforeDelivery as any, + paymentAfterDelivery: contract.paymentAfterDelivery as any, + contractEstablishmentConditions: contract.contractEstablishmentConditions as any, + mandatoryDocuments: contract.mandatoryDocuments as any, + contractTerminationConditions: contract.contractTerminationConditions as any, + } + + // 품목정보 총합 계산 로직 (기존 코드와 동일) + const contractItems = await db + .select() + .from(generalContractItems) + .where(eq(generalContractItems.contractId, id)) + + let calculatedContractAmount = null + if (contractItems && contractItems.length > 0) { + calculatedContractAmount = contractItems.reduce((sum, item) => { + const amount = parseFloat(item.contractAmount || '0') + return sum + amount + }, 0) + } + + return { + ...parsedContract, + contractAmount: calculatedContractAmount, + } + + } catch (error) { + console.error('Error getting contract basic info:', error) + throw new Error('Failed to fetch contract basic info') + } +} + +export async function createContract(data: Record) { + try { + // 계약번호 자동 생성 + // TODO: 구매 발주담당자 코드 필요 - 파라미터 추가 + const rawUserId = data.registeredById + const userId = (rawUserId && !isNaN(Number(rawUserId))) ? String(rawUserId) : undefined + const contractNumber = await generateContractNumber( + userId, + data.type as string + ) + + const [newContract] = await db + .insert(generalContracts) + .values({ + contractNumber: contractNumber, + revision: 0, + // contractSourceType: data.contractSourceType || 'manual', + status: data.status || 'Draft', + category: data.category as string, + type: data.type as string, + executionMethod: data.executionMethod as string, + name: data.name as string, + vendorId: data.vendorId as number, + projectId: data.projectId as number, + startDate: data.startDate as string, + endDate: data.endDate as string, + validityEndDate: data.validityEndDate as string, + linkedRfqOrItb: data.linkedRfqOrItb as string, + linkedPoNumber: data.linkedPoNumber as string, + linkedBidNumber: data.linkedBidNumber as string, + contractScope: data.contractScope as string, + warrantyPeriod: data.warrantyPeriod || {}, + specificationType: data.specificationType as string, + specificationManualText: data.specificationManualText as string, + unitPriceType: data.unitPriceType as string, + contractAmount: data.contractAmount as number, + currency: data.currency as string, + paymentBeforeDelivery: data.paymentBeforeDelivery || {}, + paymentDelivery: data.paymentDelivery as string, + paymentAfterDelivery: data.paymentAfterDelivery || {}, + paymentTerm: data.paymentTerm as string, + taxType: data.taxType as string, + liquidatedDamages: data.liquidatedDamages as number, + liquidatedDamagesPercent: data.liquidatedDamagesPercent as number, + deliveryType: data.deliveryType as string, + deliveryTerm: data.deliveryTerm as string, + shippingLocation: data.shippingLocation as string, + dischargeLocation: data.dischargeLocation as string, + contractDeliveryDate: data.contractDeliveryDate as string, + contractEstablishmentConditions: data.contractEstablishmentConditions || {}, + interlockingSystem: data.interlockingSystem as string, + mandatoryDocuments: data.mandatoryDocuments || {}, + contractTerminationConditions: data.contractTerminationConditions || {}, + terms: data.terms || {}, + complianceChecklist: data.complianceChecklist || {}, + communicationChannels: data.communicationChannels || {}, + locations: data.locations || {}, + fieldServiceRates: data.fieldServiceRates || {}, + offsetDetails: data.offsetDetails || {}, + totalAmount: data.totalAmount as number, + availableBudget: data.availableBudget as number, + registeredById: data.registeredById as number, + lastUpdatedById: data.lastUpdatedById as number, + notes: data.notes as string, + }) + .returning() + console.log(newContract,"newContract") + + + revalidatePath('/general-contracts') + return newContract + } catch (error) { + console.error('Error creating contract:', error) + throw new Error('Failed to create contract') + } +} + +export async function updateContractBasicInfo(id: number, data: Record, userId: number) { + try { + // 업데이트할 데이터 정리 + // 클라이언트에서 전송된 formData를 그대로 사용합니다. + const { + specificationType, + specificationManualText, + unitPriceType, + warrantyPeriod, + currency, + linkedPoNumber, + linkedBidNumber, + notes, + paymentBeforeDelivery, + paymentDelivery, + paymentAfterDelivery, + paymentTerm, + taxType, + liquidatedDamages, + liquidatedDamagesPercent, + deliveryType, + deliveryTerm, + shippingLocation, + dischargeLocation, + contractDeliveryDate, + contractEstablishmentConditions, + interlockingSystem, + mandatoryDocuments, + contractTerminationConditions, + } = data + + // 계약금액 자동 집계 로직 + const contractItems = await db + .select() + .from(generalContractItems) + .where(eq(generalContractItems.contractId, id)) + + let calculatedContractAmount: number | null = null + if (contractItems && contractItems.length > 0) { + calculatedContractAmount = contractItems.reduce((sum, item) => { + const amount = parseFloat(item.contractAmount || '0') + return sum + amount + }, 0) + } + + // 데이터 타입 변환 및 검증 + const convertToNumberOrNull = (value: unknown): number | null => { + 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, + specificationManualText, + unitPriceType, + warrantyPeriod, // JSON 필드 + currency, + linkedPoNumber, + linkedBidNumber, + notes, + paymentBeforeDelivery, // JSON 필드 + paymentDelivery: convertToNumberOrNull(paymentDelivery), + paymentAfterDelivery, // JSON 필드 + paymentTerm, + taxType, + liquidatedDamages: convertToNumberOrNull(liquidatedDamages), + liquidatedDamagesPercent: convertToNumberOrNull(liquidatedDamagesPercent), + deliveryType, + deliveryTerm, + shippingLocation, + dischargeLocation, + contractDeliveryDate: convertEmptyStringToNull(contractDeliveryDate), + contractEstablishmentConditions, // JSON 필드 + interlockingSystem, + mandatoryDocuments, // JSON 필드 + contractTerminationConditions, // JSON 필드 + contractAmount: calculatedContractAmount || 0, + lastUpdatedAt: new Date(), + lastUpdatedById: userId, + } + + // DB에 업데이트 실행 + const [updatedContract] = await db + .update(generalContracts) + .set(updateData) + .where(eq(generalContracts.id, id)) + .returning() + + revalidatePath('/general-contracts') + revalidatePath(`/general-contracts/detail/${id}`) + return updatedContract + } catch (error) { + console.error('Error updating contract basic info:', error) + throw new Error('Failed to update contract basic info') + } +} + +// 품목정보 조회 +export async function getContractItems(contractId: number) { + try { + const items = await db + .select() + .from(generalContractItems) + .where(eq(generalContractItems.contractId, contractId)) + .orderBy(asc(generalContractItems.id)) + + return items + } catch (error) { + console.error('Error getting contract items:', error) + throw new Error('Failed to get contract items') + } +} + +// 품목정보 생성 +export async function createContractItem(contractId: number, itemData: Record) { + try { + const [newItem] = await db + .insert(generalContractItems) + .values({ + contractId, + itemCode: itemData.itemCode as string, + itemInfo: itemData.itemInfo as string, + specification: itemData.specification as string, + quantity: itemData.quantity as number, + quantityUnit: itemData.quantityUnit as string, + contractDeliveryDate: itemData.contractDeliveryDate as string, + contractUnitPrice: itemData.contractUnitPrice as number, + contractAmount: itemData.contractAmount as number, + contractCurrency: itemData.contractCurrency as string, + }) + .returning() + + // 계약금액 자동 업데이트 + await updateContractAmount(contractId) + + revalidatePath('/general-contracts') + return newItem + } catch (error) { + console.error('Error creating contract item:', error) + throw new Error('Failed to create contract item') + } +} + +// 품목정보 업데이트 +export async function updateContractItem(itemId: number, itemData: Record) { + try { + const [updatedItem] = await db + .update(generalContractItems) + .set({ + itemCode: itemData.itemCode as string, + itemInfo: itemData.itemInfo as string, + specification: itemData.specification as string, + quantity: itemData.quantity as number, + quantityUnit: itemData.quantityUnit as string, + contractDeliveryDate: itemData.contractDeliveryDate as string, + contractUnitPrice: itemData.contractUnitPrice as number, + contractAmount: itemData.contractAmount as number, + contractCurrency: itemData.contractCurrency as string, + updatedAt: new Date() + }) + .where(eq(generalContractItems.id, itemId)) + .returning() + + // 계약금액 자동 업데이트 + await updateContractAmount(updatedItem.contractId) + + revalidatePath('/general-contracts') + return updatedItem + } catch (error) { + console.error('Error updating contract item:', error) + throw new Error('Failed to update contract item') + } +} + +// 품목정보 삭제 +export async function deleteContractItem(itemId: number) { + try { + // 삭제 전 계약 ID 조회 + const [item] = await db + .select({ contractId: generalContractItems.contractId }) + .from(generalContractItems) + .where(eq(generalContractItems.id, itemId)) + .limit(1) + + if (!item) { + throw new Error('Contract item not found') + } + + await db + .delete(generalContractItems) + .where(eq(generalContractItems.id, itemId)) + + // 계약금액 자동 업데이트 + await updateContractAmount(item.contractId) + + revalidatePath('/general-contracts') + return { success: true } + } catch (error) { + console.error('Error deleting contract item:', error) + throw new Error('Failed to delete contract item') + } +} + +// 품목정보 일괄 업데이트 (기존 함수 개선) +export async function updateContractItems(contractId: number, items: Record[]) { + try { + // 기존 품목 삭제 + await db + .delete(generalContractItems) + .where(eq(generalContractItems.contractId, contractId)) + + // 새 품목 추가 + if (items && items.length > 0) { + await db + .insert(generalContractItems) + .values( + items.map((item: Record) => ({ + contractId, + itemCode: item.itemCode as string, + itemInfo: item.itemInfo as string, + specification: item.specification as string, + quantity: item.quantity as number, + quantityUnit: item.quantityUnit as string, + totalWeight: item.totalWeight as number, + weightUnit: item.weightUnit as string, + contractDeliveryDate: item.contractDeliveryDate as string, + contractUnitPrice: item.contractUnitPrice as number, + contractAmount: item.contractAmount as number, + contractCurrency: item.contractCurrency as string, + })) + ) + } + + // 계약금액 자동 업데이트 + await updateContractAmount(contractId) + + revalidatePath('/general-contracts') + return { success: true } + } catch (error) { + console.error('Error updating contract items:', error) + throw new Error('Failed to update contract items') + } +} + +// 계약금액 자동 업데이트 헬퍼 함수 +async function updateContractAmount(contractId: number) { + try { + const items = await db + .select({ contractAmount: generalContractItems.contractAmount }) + .from(generalContractItems) + .where(eq(generalContractItems.contractId, contractId)) + + let calculatedContractAmount: number | null = null + if (items && items.length > 0) { + calculatedContractAmount = items.reduce((sum, item) => { + const amount = parseFloat(String(item.contractAmount || '0')) + return sum + amount + }, 0) + } + + // 계약 테이블의 contractAmount 업데이트 + await db + .update(generalContracts) + .set({ + contractAmount: calculatedContractAmount || 0, + lastUpdatedAt: new Date() + }) + .where(eq(generalContracts.id, contractId)) + } catch (error) { + console.error('Error updating contract amount:', error) + throw new Error('Failed to update contract amount') + } +} + +export async function updateSubcontractChecklist(contractId: number, checklistData: Record) { + try { + await db + .update(generalContracts) + .set({ + complianceChecklist: checklistData, + lastUpdatedAt: new Date() + }) + .where(eq(generalContracts.id, contractId)) + + revalidatePath('/general-contracts') + return { success: true } + } catch (error) { + console.error('Error updating subcontract checklist:', error) + throw new Error('Failed to update subcontract checklist') + } +} + +export async function getSubcontractChecklist(contractId: number) { + try { + const result = await db + .select() + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (result.length === 0) { + return { success: false, error: '계약을 찾을 수 없습니다.' } + } + + const contract = result[0] + const checklistData = contract.complianceChecklist as any + + return { + success: true, + enabled: !!checklistData, + data: checklistData || {} + } + } catch (error) { + console.error('Error getting subcontract checklist:', error) + return { success: false, error: '하도급 체크리스트 조회에 실패했습니다.' } + } +} + +export async function getBasicInfo(contractId: number) { + try { + const result = await db + .select() + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (result.length === 0) { + return { success: false, error: '계약을 찾을 수 없습니다.' } + } + + const contract = result[0] + return { + success: true, + enabled: true, // basic-info는 항상 활성화 + data: { + // 기본 정보 + contractNumber: contract.contractNumber, + contractName: contract.name, + vendorId: contract.vendorId, + vendorName: contract.vendorName, + projectName: contract.projectName, + contractType: contract.type, + contractStatus: contract.status, + startDate: contract.startDate, + endDate: contract.endDate, + contractAmount: contract.contractAmount, + currency: contract.currency, + description: contract.description, + specificationType: contract.specificationType, + specificationManualText: contract.specificationManualText, + unitPriceType: contract.unitPriceType, + warrantyPeriod: contract.warrantyPeriod, + linkedPoNumber: contract.linkedPoNumber, + linkedBidNumber: contract.linkedBidNumber, + notes: contract.notes, + + // 지급/인도 조건 + paymentBeforeDelivery: contract.paymentBeforeDelivery, + paymentDelivery: contract.paymentDelivery, + paymentAfterDelivery: contract.paymentAfterDelivery, + paymentTerm: contract.paymentTerm, + taxType: contract.taxType, + liquidatedDamages: contract.liquidatedDamages, + liquidatedDamagesPercent: contract.liquidatedDamagesPercent, + deliveryType: contract.deliveryType, + deliveryTerm: contract.deliveryTerm, + shippingLocation: contract.shippingLocation, + dischargeLocation: contract.dischargeLocation, + contractDeliveryDate: contract.contractDeliveryDate, + + // 추가 조건 + contractEstablishmentConditions: contract.contractEstablishmentConditions, + interlockingSystem: contract.interlockingSystem, + mandatoryDocuments: contract.mandatoryDocuments, + contractTerminationConditions: contract.contractTerminationConditions + } + } + } catch (error) { + console.error('Error getting basic info:', error) + return { success: false, error: '기본 정보 조회에 실패했습니다.' } + } +} + + +export async function getCommunicationChannel(contractId: number) { + try { + const [contract] = await db + .select({ + communicationChannels: generalContracts.communicationChannels + }) + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (!contract) { + return null + } + + return contract.communicationChannels as any + } catch (error) { + console.error('Error getting communication channel:', error) + throw new Error('Failed to get communication channel') + } +} + +export async function updateCommunicationChannel(contractId: number, communicationData: Record, userId: number) { + try { + await db + .update(generalContracts) + .set({ + communicationChannels: communicationData, + lastUpdatedAt: new Date(), + lastUpdatedById: userId + }) + .where(eq(generalContracts.id, contractId)) + + revalidatePath('/general-contracts') + return { success: true } + } catch (error) { + console.error('Error updating communication channel:', error) + throw new Error('Failed to update communication channel') + } +} + +export async function updateLocation(contractId: number, locationData: Record, userId: number) { + try { + await db + .update(generalContracts) + .set({ + locations: locationData, + lastUpdatedAt: new Date(), + lastUpdatedById: userId + }) + .where(eq(generalContracts.id, contractId)) + + revalidatePath('/general-contracts') + return { success: true } + } catch (error) { + console.error('Error updating location:', error) + throw new Error('Failed to update location') + } +} + +export async function getLocation(contractId: number) { + try { + const [contract] = await db + .select({ + locations: generalContracts.locations + }) + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (!contract) { + return null + } + + return contract.locations as any + } catch (error) { + console.error('Error getting location:', error) + throw new Error('Failed to get location') + } +} + +export async function updateContract(id: number, data: Record) { + try { + // 숫자 필드에서 빈 문자열을 null로 변환 + const cleanedData = { ...data } + const numericFields = [ + 'vendorId', + 'projectId', + 'warrantyPeriodValue', + 'warrantyPeriodMax', + 'contractAmount', + 'totalAmount', + 'availableBudget', + 'liquidatedDamages', + 'liquidatedDamagesPercent', + 'lastUpdatedById' + ] + + // 모든 필드에서 빈 문자열, undefined, 빈 객체 등을 정리 + Object.keys(cleanedData).forEach(key => { + const value = cleanedData[key] + + // 빈 문자열을 null로 변환 + if (value === '') { + cleanedData[key] = null + } + + // 빈 객체를 null로 변환 + if (value && typeof value === 'object' && Object.keys(value).length === 0) { + cleanedData[key] = null + } + }) + + // 숫자 필드들 추가 정리 (vendorId는 NOT NULL이므로 null로 설정하지 않음) + numericFields.forEach(field => { + 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 + } + } + }) + + const [updatedContract] = await db + .update(generalContracts) + .set({ + ...cleanedData, + lastUpdatedAt: new Date(), + revision: (cleanedData.revision as number) ? (cleanedData.revision as number) + 1 : 0, + }) + .where(eq(generalContracts.id, id)) + .returning() + + // Update contract items if provided + if (data.contractItems && Array.isArray(data.contractItems)) { + // Delete existing items + await db + .delete(generalContractItems) + .where(eq(generalContractItems.contractId, id)) + + // Insert new items + if (data.contractItems.length > 0) { + await db + .insert(generalContractItems) + .values( + data.contractItems.map((item: any) => ({ + project: item.project, + itemCode: item.itemCode, + itemInfo: item.itemInfo, + specification: item.specification, + quantity: item.quantity, + quantityUnit: item.quantityUnit, + contractDeliveryDate: item.contractDeliveryDate, + contractUnitPrice: item.contractUnitPrice, + contractAmount: item.contractAmount, + contractCurrency: item.contractCurrency, + contractId: id, + })) + ) + } + } + + // Update attachments if provided + if (data.attachments && Array.isArray(data.attachments)) { + // Delete existing attachments + await db + .delete(generalContractAttachments) + .where(eq(generalContractAttachments.contractId, id)) + + // Insert new attachments + if (data.attachments.length > 0) { + await db + .insert(generalContractAttachments) + .values( + data.attachments.map((attachment: any) => ({ + ...attachment, + contractId: id, + })) + ) + } + } + + revalidatePath('/general-contracts') + revalidatePath(`/general-contracts/detail/${id}`) + return updatedContract + } catch (error) { + console.error('Error updating contract:', error) + throw new Error('Failed to update contract') + } +} + +export async function deleteContract(id: number) { + try { + // 현재 계약 정보 조회 + await db + .select({ revision: generalContracts.revision }) + .from(generalContracts) + .where(eq(generalContracts.id, id)) + .limit(1) + + // 계약폐기: status를 'Contract Delete'로 변경 + const [updatedContract] = await db + .update(generalContracts) + .set({ + status: 'Contract Delete', + lastUpdatedAt: new Date(), + // revision: (currentContract[0]?.revision || 0) + 1 // 계약 파기 시 리비전 증가? 확인 필요 + }) + .where(eq(generalContracts.id, id)) + .returning() + + revalidatePath('/general-contracts') + return { success: true, contract: updatedContract } + } catch (error) { + console.error('Error deleting contract:', error) + throw new Error('Failed to delete contract') + } +} + +// 상태별 개수 집계 +export async function getGeneralContractStatusCounts() { + try { + const counts = await db + .select({ + status: generalContracts.status, + count: count(), + }) + .from(generalContracts) + .groupBy(generalContracts.status) + + return counts.reduce((acc, { status, count }) => { + acc[status] = count + return acc + }, {} as Record) + } catch (error) { + console.error('Failed to get contract status counts:', error) + return {} + } +} + +// 계약구분별 개수 집계 +export async function getGeneralContractCategoryCounts() { + try { + const counts = await db + .select({ + category: generalContracts.category, + count: count(), + }) + .from(generalContracts) + .groupBy(generalContracts.category) + + return counts.reduce((acc, { category, count }) => { + acc[category] = count + return acc + }, {} as Record) + } catch (error) { + console.error('Failed to get contract category counts:', error) + return {} + } +} + +export async function getVendors() { + try { + const vendorList = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + }) + .from(vendors) + .orderBy(asc(vendors.vendorName)) + + return vendorList + } catch (error) { + console.error('Error fetching vendors:', error) + throw new Error('Failed to fetch vendors') + } +} + +// 첨부파일 업로드 +export async function uploadContractAttachment(contractId: number, file: File, userId: string, documentName: string = '사양 및 공급범위') { + try { + // userId를 숫자로 변환 + const userIdNumber = parseInt(userId) + if (isNaN(userIdNumber)) { + throw new Error('Invalid user ID') + } + + const saveResult = await saveDRMFile( + file, + decryptWithServerAction, + `general-contracts/${contractId}/attachments`, + userId, + ) + + if (saveResult.success && saveResult.publicPath) { + // generalContractAttachments 테이블에 저장 + const [attachment] = await db.insert(generalContractAttachments).values({ + contractId, + documentName, + fileName: saveResult.fileName || file.name, + filePath: saveResult.publicPath, + uploadedById: userIdNumber, + uploadedAt: new Date(), + }).returning() + + return { + success: true, + message: '파일이 성공적으로 업로드되었습니다.', + attachment + } + } else { + return { + success: false, + error: saveResult.error || '파일 저장에 실패했습니다.' + } + } + } catch (error) { + console.error('Failed to upload contract attachment:', error) + return { + success: false, + error: '파일 업로드에 실패했습니다.' + } + } +} + +// 첨부파일 목록 조회 +export async function getContractAttachments(contractId: number) { + try { + const attachments = await db + .select() + .from(generalContractAttachments) + .where(eq(generalContractAttachments.contractId, contractId)) + .orderBy(desc(generalContractAttachments.uploadedAt)) + + return attachments + } catch (error) { + console.error('Failed to get contract attachments:', error) + return [] + } +} + +// 첨부파일 다운로드 +export async function getContractAttachmentForDownload(attachmentId: number, contractId: number) { + try { + const attachments = await db + .select() + .from(generalContractAttachments) + .where(and( + eq(generalContractAttachments.id, attachmentId), + eq(generalContractAttachments.contractId, contractId) + )) + .limit(1) + + if (attachments.length === 0) { + return { + success: false, + error: '첨부파일을 찾을 수 없습니다.' + } + } + + return { + success: true, + attachment: attachments[0] + } + } catch (error) { + console.error('Failed to get contract attachment for download:', error) + return { + success: false, + error: '첨부파일 다운로드 준비에 실패했습니다.' + } + } +} + +// 첨부파일 삭제 +export async function deleteContractAttachment(attachmentId: number, contractId: number) { + try { + const attachments = await db + .select() + .from(generalContractAttachments) + .where(and( + eq(generalContractAttachments.id, attachmentId), + eq(generalContractAttachments.contractId, contractId) + )) + .limit(1) + + if (attachments.length === 0) { + return { + success: false, + error: '첨부파일을 찾을 수 없습니다.' + } + } + + // 데이터베이스에서 삭제 + await db + .delete(generalContractAttachments) + .where(eq(generalContractAttachments.id, attachmentId)) + + return { + success: true, + message: '첨부파일이 삭제되었습니다.' + } + } catch (error) { + console.error('Failed to delete contract attachment:', error) + return { + success: false, + error: '첨부파일 삭제에 실패했습니다.' + } + } +} + +// 계약승인요청용 파일 업로드 (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 + + // const items: { + // id: number; + // createdAt: Date; + // updatedAt: Date; + // contractId: number; + // itemCode: string | null; + // quantity: string | null; + // contractAmount: string | null; + // contractCurrency: string | null; + // contractDeliveryDate: string | null; + // specification: string | null; + // itemInfo: string | null; + // quantityUnit: string | null; + // totalWeight: string | null; + // weightUnit: string | null; + // contractUnitPrice: string | null; + // }[] + + // contractItems 테이블에 품목 정보 저장 (general-contract-items가 있을 때만) + if (contractSummary.items && contractSummary.items.length > 0) { + const projectNo = contractSummary.basicInfo?.projectCode || contractSummary.basicInfo?.projectId?.toString() || 'NULL' + + for (const item of contractSummary.items) { + let itemId: number + + // 1. items 테이블에서 itemCode로 기존 아이템 검색 + if (item.itemCode) { + // const existingItem = await db + // .select({ id: items.id }) + // .from(items) + // .where(and( + // eq(items.itemCode, item.itemCode), + // eq(items.ProjectNo, projectNo) + // )) + // .limit(1) + const existingItem = await db + .select({ id: items.id }) + .from(items) + .where( + eq(items.itemCode, item.itemCode) + ) + .limit(1) + + if (existingItem.length > 0) { + // 기존 아이템이 있으면 해당 ID 사용 + itemId = existingItem[0].id + } else { + // 기존 아이템이 없으면 새로 생성 + const newItem = await db.insert(items).values({ + ProjectNo: projectNo, + itemCode: item.itemCode, + itemName: item.itemInfo || item.description || item.itemCode, + packageCode: item.itemCode, + description: item.specification || item.description || '', + unitOfMeasure: item.quantityUnit || 'EA', + createdAt: new Date(), + updatedAt: new Date(), + }).returning({ id: items.id }) + + itemId = newItem[0].id + } + + + // 2. contractItems에 저장 + await db.insert(contractItems).values({ + contractId, + itemId: itemId, + 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 || '', + }) + }else{ + //아이템코드가 없으니 pass + continue + } + } + } + + // 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", + }, + }) + // 계약 상태 업데이트 + await db.update(generalContracts) + .set({ + status: 'Contract Accept Request', + lastUpdatedAt: new Date() + }) + .where(eq(generalContracts.id, generalContractId)) + + } catch (error) { + console.error('계약승인요청 전송 오류:', error) + + } + + + revalidatePath('/evcp/general-contracts') + revalidatePath('/evcp/general-contracts/detail') + 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.userId, + basicInfo.contractType || basicInfo.type || 'UP' + ) + + 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 { + const result = await db + .select({ fieldServiceRates: generalContracts.fieldServiceRates }) + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (result.length === 0) { + return null + } + + return result[0].fieldServiceRates as Record || null + } catch (error) { + console.error('Failed to get field service rate:', error) + throw new Error('Field Service Rate 데이터를 불러오는데 실패했습니다.') + } +} + +export async function updateFieldServiceRate( + contractId: number, + fieldServiceRateData: Record, + userId: number +) { + try { + await db + .update(generalContracts) + .set({ + fieldServiceRates: fieldServiceRateData, + lastUpdatedAt: new Date(), + lastUpdatedById: userId + }) + .where(eq(generalContracts.id, contractId)) + + revalidatePath('/evcp/general-contracts') + return { success: true } + } catch (error) { + console.error('Failed to update field service rate:', error) + throw new Error('Field Service Rate 업데이트에 실패했습니다.') + } +} + +// Offset Details 관련 서버 액션들 +export async function getOffsetDetails(contractId: number) { + try { + const result = await db + .select({ offsetDetails: generalContracts.offsetDetails }) + .from(generalContracts) + .where(eq(generalContracts.id, contractId)) + .limit(1) + + if (result.length === 0) { + return null + } + + return result[0].offsetDetails as Record || null + } catch (error) { + console.error('Failed to get offset details:', error) + throw new Error('회입/상계내역 데이터를 불러오는데 실패했습니다.') + } +} + +export async function updateOffsetDetails( + contractId: number, + offsetDetailsData: Record, + userId: number +) { + try { + await db + .update(generalContracts) + .set({ + offsetDetails: offsetDetailsData, + lastUpdatedAt: new Date(), + lastUpdatedById: userId + }) + .where(eq(generalContracts.id, contractId)) + + revalidatePath('/evcp/general-contracts') + return { success: true } + } catch (error) { + console.error('Failed to update offset details:', error) + throw new Error('회입/상계내역 업데이트에 실패했습니다.') + } +} + +// 계약번호 생성 함수 +export async function generateContractNumber( + userId?: string, + contractType: 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' // 기본값 + // user 테이블의 user.userCode가 있으면 발주담당자 코드로 사용 + // userId가 주어졌을 때 user.userCode를 조회, 없으면 '000' 사용 + let purchaseManagerCode = '000'; + if (userId) { + const user = await db + .select({ userCode: users.userCode }) + .from(users) + .where(eq(users.id, parseInt(userId || '0'))) + .limit(1); + if (user[0]?.userCode && user[0].userCode.length >= 3) { + purchaseManagerCode = user[0].userCode.substring(0, 3).toUpperCase(); + } + } + 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 lastSequenceStr = lastContractNumber.slice(-3) + + // contractNumber에서 숫자만 추출하여 sequence 찾기 + const numericParts = lastContractNumber.match(/\d+/g) + if (numericParts && numericParts.length > 0) { + // 마지막 숫자 부분을 시퀀스로 사용 (일반적으로 마지막 3자리) + const potentialSequence = numericParts[numericParts.length - 1] + const lastSequence = parseInt(potentialSequence) + + if (!isNaN(lastSequence)) { + sequenceNumber = lastSequence + 1 + } + } + // 숫자를 찾지 못했거나 파싱 실패 시 sequenceNumber = 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 + + // contractNumber에서 숫자만 추출하여 sequence 찾기 + const numericParts = lastContractNumber.match(/\d+/g) + if (numericParts && numericParts.length > 0) { + // 마지막 숫자 부분을 시퀀스로 사용 + const potentialSequence = numericParts[numericParts.length - 1] + const lastSequence = parseInt(potentialSequence) + + if (!isNaN(lastSequence)) { + sequenceNumber = lastSequence + 1 + } + } + // 숫자를 찾지 못했거나 파싱 실패 시 sequenceNumber = 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('계약번호 생성에 실패했습니다.') + } +} + +// 프로젝트 목록 조회 +export async function getProjects() { + try { + const projectList = await db + .select({ + id: projects.id, + code: projects.code, + name: projects.name, + type: projects.type, + }) + .from(projects) + .orderBy(asc(projects.name)) + + return projectList + } catch (error) { + console.error('Error fetching projects:', error) + throw new Error('Failed to fetch projects') + } +} -- cgit v1.2.3