(최겸) 구매 계약 메인 및 상세 기능 개발(템플릿 연동 및 계약 전달 개발 필요)

1 files changed, 1226 insertions, 0 deletions

diff --git a/lib/general-contracts/service.ts b/lib/general-contracts/service.ts
new file mode 100644
index 00000000..6d9e5c39
--- /dev/null
+++ b/lib/general-contracts/service.ts
@@ -0,0 +1,1226 @@
+'use server'

+

+import { revalidatePath } from 'next/cache'

+import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte, lt } from 'drizzle-orm'

+import db from '@/db/db'

+import { generalContracts, generalContractItems, generalContractAttachments } from '@/db/schema/generalContract'

+import { vendors } from '@/db/schema/vendors'

+import { users } from '@/db/schema/users'

+import { filterColumns } from '@/lib/filter-columns'

+import { saveDRMFile } from '@/lib/file-stroage'

+import { decryptWithServerAction } from '@/components/drm/drmUtils'

+import { GetGeneralContractsSchema } from './validation'

+

+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.selectionMethod && input.selectionMethod.length > 0) {

+      basicConditions.push(

+        or(...input.selectionMethod.map(method => eq(generalContracts.selectionMethod, 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,

+        selectionMethod: generalContracts.selectionMethod,

+        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

+        vendorName: vendors.vendorName,

+        vendorCode: vendors.vendorCode,

+        // User info

+        managerName: users.name,

+        lastUpdatedByName: users.name,

+      })

+      .from(generalContracts)

+      .leftJoin(vendors, eq(generalContracts.vendorId, vendors.id))

+      .leftJoin(users, eq(generalContracts.registeredById, users.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 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,

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

+  }

+}

+

+// 계약번호 생성 함수

+async function generateContractNumber(registeredById: number, contractType: string): Promise<string> {

+  // 발주담당자코드 3자리 (사용자 ID를 3자리로 패딩)

+  const managerCode = String(registeredById).padStart(3, '0')

+  

+  // 계약종류 2자리 (영문)

+  const typeCode = contractType.substring(0, 2).toUpperCase()

+  

+  // 일련번호 3자리 (현재 날짜 기준으로 생성)

+  const today = new Date()

+  

+  // 같은 날짜의 계약 개수 조회

+  const startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate())

+  const endOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1)

+  

+  const countResult = await db

+    .select({ count: count() })

+    .from(generalContracts)

+    .where(

+      and(

+        gte(generalContracts.registeredAt, startOfDay),

+        lt(generalContracts.registeredAt, endOfDay)

+      )

+    )

+  

+  const sequenceNumber = String((countResult[0]?.count || 0) + 1).padStart(3, '0')

+  

+  return `C${managerCode}${typeCode}${sequenceNumber}`

+}

+

+export async function createContract(data: Record<string, unknown>) {

+  try {

+    // 계약번호 자동 생성

+    const contractNumber = data.contractNumber || await generateContractNumber(

+      data.registeredById as number, 

+      data.type as string

+    )

+    

+    const [newContract] = await db

+      .insert(generalContracts)

+      .values({

+        contractNumber: contractNumber,

+        revision: 0,

+        status: data.status || 'Draft',

+        category: data.category as string,

+        type: data.type as string,

+        executionMethod: data.executionMethod as string,

+        name: data.name as string,

+        selectionMethod: data.selectionMethod as string,

+        vendorId: data.vendorId 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()

+

+

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

+      specificationType,

+      specificationManualText,

+      unitPriceType,

+      warrantyPeriod, // JSON 필드

+      currency,

+      linkedPoNumber,

+      linkedBidNumber,

+      notes,

+      paymentBeforeDelivery, // JSON 필드

+      paymentDelivery,

+      paymentAfterDelivery, // JSON 필드

+      paymentTerm,

+      taxType,

+      liquidatedDamages,

+      liquidatedDamagesPercent,

+      deliveryType,

+      deliveryTerm,

+      shippingLocation,

+      dischargeLocation,

+      contractDeliveryDate,

+      contractEstablishmentConditions, // JSON 필드

+      interlockingSystem,

+      mandatoryDocuments, // JSON 필드

+      contractTerminationConditions, // JSON 필드

+      contractAmount: calculatedContractAmount,

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

+        project: itemData.project as string,

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

+        project: itemData.project as string,

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

+            project: item.project as string,

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

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

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

+        subcontractChecklist: 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 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', 

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

+      }

+    })

+    

+    // 숫자 필드들 추가 정리

+    numericFields.forEach(field => {

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

+      .where(eq(vendors.status, 'ACTIVE'))

+      .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.filePath) {

+      // generalContractAttachments 테이블에 저장

+      const [attachment] = await db.insert(generalContractAttachments).values({

+        contractId,

+        documentName,

+        fileName: saveResult.fileName || file.name,

+        filePath: saveResult.filePath,

+        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: '첨부파일 삭제에 실패했습니다.'

+    }

+  }

+}

+

+// 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,

+        updatedAt: new Date(),

+        updatedBy: 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,

+        updatedAt: new Date(),

+        updatedBy: 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('회입/상계내역 업데이트에 실패했습니다.')

+  }

+}