(김준회) PO/계약 히스토리

1 files changed, 182 insertions, 0 deletions

diff --git a/lib/vendors/contract-history-service.ts b/lib/vendors/contract-history-service.ts
new file mode 100644
index 00000000..2aebcd8b
--- /dev/null
+++ b/lib/vendors/contract-history-service.ts
@@ -0,0 +1,182 @@
+'use server'
+
+import db from "@/db/db"
+import { eq, desc, and, sql } from "drizzle-orm"
+import { contractsDetailView, ContractDetailParsed } from "@/db/schema/contract"
+
+export interface ContractHistoryFilters {
+  vendorId?: number
+  projectId?: number
+  status?: string
+  startDate?: Date
+  endDate?: Date
+  contractNo?: string
+  search?: string
+}
+
+export interface ContractHistoryResult {
+  data: ContractDetailParsed[]
+  totalCount: number
+  pageCount: number
+}
+
+export interface ContractHistoryQueryOptions {
+  page?: number
+  pageSize?: number
+  sortBy?: string
+  sortOrder?: 'asc' | 'desc'
+  filters?: ContractHistoryFilters
+}
+
+/**
+ * 벤더별 계약 히스토리 조회 (contractsDetailView 사용)
+ */
+export async function getVendorContractHistory(
+  vendorId: number,
+  options: ContractHistoryQueryOptions = {}
+): Promise<ContractHistoryResult> {
+  const {
+    page = 1,
+    pageSize = 10,
+    sortBy = 'createdAt',
+    sortOrder = 'desc',
+    filters = {}
+  } = options
+
+  const offset = (page - 1) * pageSize
+
+  // where 조건들을 한 번에 구성
+  const conditions = [eq(contractsDetailView.vendorId, vendorId)]
+
+  if (filters.projectId) {
+    conditions.push(eq(contractsDetailView.projectId, filters.projectId))
+  }
+
+  if (filters.status) {
+    conditions.push(eq(contractsDetailView.status, filters.status))
+  }
+
+  if (filters.startDate) {
+    conditions.push(sql`${contractsDetailView.startDate} >= ${filters.startDate}`)
+  }
+
+  if (filters.endDate) {
+    conditions.push(sql`${contractsDetailView.endDate} <= ${filters.endDate}`)
+  }
+
+  if (filters.contractNo) {
+    conditions.push(sql`${contractsDetailView.contractNo} ILIKE ${`%${filters.contractNo}%`}`)
+  }
+
+  if (filters.search) {
+    conditions.push(sql`(
+      ${contractsDetailView.contractNo} ILIKE ${`%${filters.search}%`} OR
+      ${contractsDetailView.contractName} ILIKE ${`%${filters.search}%`} OR
+      ${contractsDetailView.projectName} ILIKE ${`%${filters.search}%`}
+    )`)
+  }
+
+  const whereCondition = conditions.length === 1 ? conditions[0] : and(...conditions)
+
+  // 정렬 설정
+  const orderByField = sortBy === 'createdAt' ? contractsDetailView.createdAt : contractsDetailView.contractNo
+  const orderBy = sortOrder === 'desc' ? desc(orderByField) : orderByField
+
+  // 데이터 조회
+  const data = await db
+    .select()
+    .from(contractsDetailView)
+    .where(whereCondition)
+    .orderBy(orderBy)
+    .limit(pageSize)
+    .offset(offset)
+
+  // 전체 개수 조회
+  const [{ count }] = await db
+    .select({ count: sql<number>`count(*)` })
+    .from(contractsDetailView)
+    .where(whereCondition)
+
+  const totalCount = count
+  const pageCount = Math.ceil(totalCount / pageSize)
+
+  // ContractDetailParsed 타입으로 변환
+  const parsedData: ContractDetailParsed[] = data.map(item => ({
+    ...item,
+    envelopes: JSON.parse(item.envelopes || '[]'),
+    items: JSON.parse(item.items || '[]')
+  }))
+
+  return {
+    data: parsedData,
+    totalCount,
+    pageCount
+  }
+}
+
+/**
+ * 벤더별 계약 히스토리 조회 (contractsDetailView 사용)
+ * contract.ts 스키마를 기준으로 데이터를 가져옴
+ */
+export async function getVendorContractHistoryExtended(
+  vendorId: number,
+  options: ContractHistoryQueryOptions = {}
+): Promise<ContractHistoryResult> {
+  // 기본 함수와 동일한 로직 사용
+  return getVendorContractHistory(vendorId, options)
+}
+
+/**
+ * 무한 스크롤용 계약 히스토리 조회
+ */
+export async function getVendorContractHistoryInfinite(
+  vendorId: number,
+  cursor?: number,
+  pageSize: number = 50,
+  filters?: ContractHistoryFilters
+): Promise<{
+  data: ContractDetailParsed[]
+  hasNextPage: boolean
+  nextCursor?: number
+}> {
+  // where 조건들을 한 번에 구성
+  const conditions = [eq(contractsDetailView.vendorId, vendorId)]
+
+  if (cursor) {
+    conditions.push(sql`${contractsDetailView.id} < ${cursor}`)
+  }
+
+  if (filters?.status) {
+    conditions.push(eq(contractsDetailView.status, filters.status))
+  }
+
+  if (filters?.contractNo) {
+    conditions.push(sql`${contractsDetailView.contractNo} ILIKE ${`%${filters.contractNo}%`}`)
+  }
+
+  const whereCondition = conditions.length === 1 ? conditions[0] : and(...conditions)
+
+  const data = await db
+    .select()
+    .from(contractsDetailView)
+    .where(whereCondition)
+    .orderBy(desc(contractsDetailView.createdAt))
+    .limit(pageSize + 1) // 다음 페이지 확인용 +1
+
+  const hasNextPage = data.length > pageSize
+  const items = hasNextPage ? data.slice(0, -1) : data
+  const nextCursor = hasNextPage ? items[items.length - 1]?.id : undefined
+
+  // ContractDetailParsed 타입으로 변환
+  const parsedData: ContractDetailParsed[] = items.map(item => ({
+    ...item,
+    envelopes: JSON.parse(item.envelopes || '[]'),
+    items: JSON.parse(item.items || '[]')
+  }))
+
+  return {
+    data: parsedData,
+    hasNextPage,
+    nextCursor
+  }
+}
\ No newline at end of file