Merge remote-tracking branch 'origin/dujinkim' into master_homemaster

1 files changed, 1933 insertions, 0 deletions

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<unknown> | 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<unknown>[] = []

+

+    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<unknown> | 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<unknown>[] = []

+    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<string, unknown>) {

+  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<string, unknown>, 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<string, unknown> = {

+      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<string, unknown>) {

+  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<string, unknown>) {

+  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<string, unknown>[]) {

+  try {

+    // 기존 품목 삭제

+    await db

+      .delete(generalContractItems)

+      .where(eq(generalContractItems.contractId, contractId))

+

+    // 새 품목 추가

+    if (items && items.length > 0) {

+      await db

+        .insert(generalContractItems)

+        .values(

+          items.map((item: Record<string, unknown>) => ({

+            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<string, unknown>) {

+  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<string, unknown>, 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<string, unknown>, 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<string, unknown>) {

+  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<string, number>)

+  } 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<string, number>)

+  } 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<void> {

+  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<string, unknown> || 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<string, unknown>, 

+  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<string, unknown> || null

+  } catch (error) {

+    console.error('Failed to get offset details:', error)

+    throw new Error('회입/상계내역 데이터를 불러오는데 실패했습니다.')

+  }

+}

+

+export async function updateOffsetDetails(

+  contractId: number, 

+  offsetDetailsData: Record<string, unknown>, 

+  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<string> {

+  try {

+    // 계약종류 매핑 (2자리) - GENERAL_CONTRACT_TYPES 상수 사용

+    const contractTypeMap: Record<string, string> = {

+      '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')

+  }

+}