(대표님) 20250613 16시 08분 b-rfq, document 등

1 files changed, 398 insertions, 2 deletions

diff --git a/lib/vendor-document-list/enhanced-document-service.ts b/lib/vendor-document-list/enhanced-document-service.ts
index 66758b89..e01283dc 100644
--- a/lib/vendor-document-list/enhanced-document-service.ts
+++ b/lib/vendor-document-list/enhanced-document-service.ts
@@ -2,9 +2,9 @@
 "use server"
 
 import { revalidatePath, unstable_cache } from "next/cache"
-import { and, asc, desc, eq, ilike, or, count, avg } from "drizzle-orm"
+import { and, asc, desc, eq, ilike, or, count, avg, inArray } from "drizzle-orm"
 import db from "@/db/db"
-import { documentAttachments, documents, enhancedDocumentsView, issueStages, revisions, type EnhancedDocumentsView } from "@/db/schema/vendorDocu"
+import { documentAttachments, documents, enhancedDocumentsView, issueStages, revisions, simplifiedDocumentsView, type EnhancedDocumentsView } from "@/db/schema/vendorDocu"
 import { filterColumns } from "@/lib/filter-columns"
 import type {
     CreateDocumentInput,
@@ -19,6 +19,8 @@ import type {
     DocumentAttachment,
     Revision
   } from "@/types/enhanced-documents"
+import { GetVendorShipDcoumentsSchema } from "./validations"
+import { contracts, users } from "@/db/schema"
 
 // 스키마 타입 정의
 export interface GetEnhancedDocumentsSchema {
@@ -156,6 +158,225 @@ export async function getEnhancedDocuments(
   // )()
 }
 
+export async function getEnhancedDocumentsShip(
+  input: GetVendorShipDcoumentsSchema,
+) {
+  try {
+    const offset = (input.page - 1) * input.perPage
+
+    // 고급 필터 처리
+    const advancedWhere = filterColumns({
+      table: simplifiedDocumentsView,
+      filters: input.filters || [],
+      joinOperator: input.joinOperator || "and",
+    })
+
+    // 전역 검색 처리
+    let globalWhere
+    if (input.search) {
+      const searchTerm = `%${input.search}%`
+      globalWhere = or(
+        ilike(simplifiedDocumentsView.title, searchTerm),
+        ilike(simplifiedDocumentsView.docNumber, searchTerm),
+        ilike(simplifiedDocumentsView.projectCode, searchTerm),
+        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
+      )
+    }
+
+    // 최종 WHERE 조건
+    const finalWhere = and(
+      advancedWhere,
+      globalWhere,
+    )
+
+    // 정렬 처리
+    const orderBy = input.sort && input.sort.length > 0
+      ? input.sort.map((item) =>
+          item.desc
+            ? desc(simplifiedDocumentsView[item.id])
+            : asc(simplifiedDocumentsView[item.id])
+        )
+      : [desc(simplifiedDocumentsView.createdAt)]
+
+    // 트랜잭션 실행
+    const { data, total } = await db.transaction(async (tx) => {
+      // 데이터 조회
+      const data = await tx
+        .select()
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .limit(input.perPage)
+        .offset(offset)
+
+      // 총 개수 조회
+      const [{ total }] = await tx
+        .select({ 
+          total: count() 
+        })
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+
+      return { data, total }
+    })
+
+    const pageCount = Math.ceil(total / input.perPage)
+
+    return { data, pageCount, total }
+  } catch (err) {
+    console.error("Error fetching enhanced documents:", err)
+    return { data: [], pageCount: 0, total: 0 }
+  }
+}
+
+// contractId로 필터링이 필요한 경우를 위한 추가 함수
+export async function getEnhancedDocumentsShipByContract(
+  input: GetVendorShipDcoumentsSchema,
+  contractId: number
+) {
+  try {
+    const offset = (input.page - 1) * input.perPage
+
+    // 고급 필터 처리
+    const advancedWhere = filterColumns({
+      table: simplifiedDocumentsView,
+      filters: input.filters || [],
+      joinOperator: input.joinOperator || "and",
+    })
+
+    // 전역 검색 처리
+    let globalWhere
+    if (input.search) {
+      const searchTerm = `%${input.search}%`
+      globalWhere = or(
+        ilike(simplifiedDocumentsView.title, searchTerm),
+        ilike(simplifiedDocumentsView.docNumber, searchTerm),
+        ilike(simplifiedDocumentsView.projectCode, searchTerm),
+        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
+      )
+    }
+
+    // 최종 WHERE 조건 (contractId 포함)
+    const finalWhere = and(
+      eq(simplifiedDocumentsView.contractId, contractId),
+      advancedWhere,
+      globalWhere,
+    )
+
+    // 정렬 처리
+    const orderBy = input.sort && input.sort.length > 0
+      ? input.sort.map((item) =>
+          item.desc
+            ? desc(simplifiedDocumentsView[item.id])
+            : asc(simplifiedDocumentsView[item.id])
+        )
+      : [desc(simplifiedDocumentsView.createdAt)]
+
+    // 트랜잭션 실행
+    const { data, total } = await db.transaction(async (tx) => {
+      // 데이터 조회
+      const data = await tx
+        .select()
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .limit(input.perPage)
+        .offset(offset)
+
+      // 총 개수 조회
+      const [{ total }] = await tx
+        .select({ 
+          total: count() 
+        })
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+
+      return { data, total }
+    })
+
+    const pageCount = Math.ceil(total / input.perPage)
+
+    return { data, pageCount, total }
+  } catch (err) {
+    console.error("Error fetching enhanced documents by contract:", err)
+    return { data: [], pageCount: 0, total: 0 }
+  }
+}
+
+// drawingKind별로 문서를 조회하는 함수
+export async function getEnhancedDocumentsShipByDrawingKind(
+  input: GetVendorShipDcoumentsSchema,
+  drawingKind: 'B3' | 'B4' | 'B5'
+) {
+  try {
+    const offset = (input.page - 1) * input.perPage
+
+    // 고급 필터 처리
+    const advancedWhere = filterColumns({
+      table: simplifiedDocumentsView,
+      filters: input.filters || [],
+      joinOperator: input.joinOperator || "and",
+    })
+
+    // 전역 검색 처리
+    let globalWhere
+    if (input.search) {
+      const searchTerm = `%${input.search}%`
+      globalWhere = or(
+        ilike(simplifiedDocumentsView.title, searchTerm),
+        ilike(simplifiedDocumentsView.docNumber, searchTerm),
+        ilike(simplifiedDocumentsView.projectCode, searchTerm),
+        ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
+      )
+    }
+
+    // 최종 WHERE 조건 (drawingKind 포함)
+    const finalWhere = and(
+      eq(simplifiedDocumentsView.drawingKind, drawingKind),
+      advancedWhere,
+      globalWhere,
+    )
+
+    // 정렬 처리
+    const orderBy = input.sort && input.sort.length > 0
+      ? input.sort.map((item) =>
+          item.desc
+            ? desc(simplifiedDocumentsView[item.id])
+            : asc(simplifiedDocumentsView[item.id])
+        )
+      : [desc(simplifiedDocumentsView.createdAt)]
+
+    // 트랜잭션 실행
+    const { data, total } = await db.transaction(async (tx) => {
+      // 데이터 조회
+      const data = await tx
+        .select()
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .limit(input.perPage)
+        .offset(offset)
+
+      // 총 개수 조회
+      const [{ total }] = await tx
+        .select({ 
+          total: count() 
+        })
+        .from(simplifiedDocumentsView)
+        .where(finalWhere)
+
+      return { data, total }
+    })
+
+    const pageCount = Math.ceil(total / input.perPage)
+
+    return { data, pageCount, total }
+  } catch (err) {
+    console.error(`Error fetching enhanced documents for drawing kind ${drawingKind}:`, err)
+    return { data: [], pageCount: 0, total: 0 }
+  }
+}
+
 // 통계 데이터 가져오기
 export async function getDocumentStatistics(contractId: number) {
   // return unstable_cache(
@@ -779,4 +1000,179 @@ export async function getDocumentDetails(documentId: number) {
         error: "일괄 상태 업데이트 중 오류가 발생했습니다."
       }
     }
+  }
+
+
+  export async function getUserVendorDocuments(
+    userId: number,
+    input: GetVendorShipDcoumentsSchema
+  ) {
+    try {
+      const offset = (input.page - 1) * input.perPage
+  
+      // 1. 사용자의 벤더(회사) ID 조회
+      const [user] = await db
+        .select({ companyId: users.companyId })
+        .from(users)
+        .where(eq(users.id, userId))
+        .limit(1)
+  
+      if (!user?.companyId) {
+        return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
+      }
+  
+      // 2. 해당 벤더의 모든 계약 ID들 조회
+      const vendorContracts = await db
+        .select({ id: contracts.id })
+        .from(contracts)
+        .where(eq(contracts.vendorId, user.companyId))
+  
+      const contractIds = vendorContracts.map(c => c.id)
+  
+      if (contractIds.length === 0) {
+        return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
+      }
+  
+      // 3. 고급 필터 처리
+      const advancedWhere = filterColumns({
+        table: simplifiedDocumentsView,
+        filters: input.filters || [],
+        joinOperator: input.joinOperator || "and",
+      })
+  
+      // 4. 전역 검색 처리
+      let globalWhere
+      if (input.search) {
+        const searchTerm = `%${input.search}%`
+        globalWhere = or(
+          ilike(simplifiedDocumentsView.title, searchTerm),
+          ilike(simplifiedDocumentsView.docNumber, searchTerm),
+          ilike(simplifiedDocumentsView.vendorDocNumber, searchTerm),
+        )
+      }
+  
+      // 5. 최종 WHERE 조건 (계약 ID들로 필터링)
+      const finalWhere = and(
+        inArray(simplifiedDocumentsView.contractId, contractIds),
+        advancedWhere,
+        globalWhere,
+      )
+  
+      // 6. 정렬 처리
+      const orderBy = input.sort && input.sort.length > 0
+        ? input.sort.map((item) =>
+            item.desc
+              ? desc(simplifiedDocumentsView[item.id])
+              : asc(simplifiedDocumentsView[item.id])
+          )
+        : [desc(simplifiedDocumentsView.createdAt)]
+  
+      // 7. 트랜잭션 실행
+      const { data, total, drawingKind, vendorInfo } = await db.transaction(async (tx) => {
+        // 데이터 조회
+        const data = await tx
+          .select()
+          .from(simplifiedDocumentsView)
+          .where(finalWhere)
+          .orderBy(...orderBy)
+          .limit(input.perPage)
+          .offset(offset)
+  
+        // 총 개수 조회
+        const [{ total }] = await tx
+          .select({ 
+            total: count() 
+          })
+          .from(simplifiedDocumentsView)
+          .where(finalWhere)
+  
+        // DrawingKind 분석 (첫 번째 문서의 drawingKind 사용)
+        const drawingKind = data.length > 0 ? data[0].drawingKind : null
+  
+        // 벤더 정보 조회
+        const [vendorInfo] = await tx
+          .select({
+            vendorName: simplifiedDocumentsView.vendorName,
+            vendorCode: simplifiedDocumentsView.vendorCode,
+          })
+          .from(simplifiedDocumentsView)
+          .where(eq(simplifiedDocumentsView.contractId, contractIds[0]))
+          .limit(1)
+  
+        return { data, total, drawingKind, vendorInfo }
+      })
+  
+      const pageCount = Math.ceil(total / input.perPage)
+  
+      return { 
+        data, 
+        pageCount, 
+        total, 
+        drawingKind: drawingKind as 'B3' | 'B4' | 'B5' | null,
+        vendorInfo: vendorInfo || null
+      }
+    } catch (err) {
+      console.error("Error fetching user vendor documents:", err)
+      return { data: [], pageCount: 0, total: 0, drawingKind: null, vendorInfo: null }
+    }
+  }
+  
+  /**
+   * DrawingKind별 문서 통계 조회
+   */
+  export async function getUserVendorDocumentStats(userId: number) {
+    try {
+      // 사용자의 벤더 ID 조회
+      const [user] = await db
+        .select({ companyId: users.companyId })
+        .from(users)
+        .where(eq(users.id, userId))
+        .limit(1)
+  
+      if (!user?.companyId) {
+        return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
+      }
+  
+      // 해당 벤더의 계약 ID들 조회
+      const vendorContracts = await db
+        .select({ id: contracts.id })
+        .from(contracts)
+        .where(eq(contracts.vendorId, user.companyId))
+  
+      const contractIds = vendorContracts.map(c => c.id)
+  
+      if (contractIds.length === 0) {
+        return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
+      }
+  
+      // DrawingKind별 통계 조회
+      const documents = await db
+        .select({
+          drawingKind: simplifiedDocumentsView.drawingKind,
+          contractId: simplifiedDocumentsView.contractId,
+        })
+        .from(simplifiedDocumentsView)
+        .where(inArray(simplifiedDocumentsView.contractId, contractIds))
+  
+      // 통계 계산
+      const stats = documents.reduce((acc, doc) => {
+        if (doc.drawingKind) {
+          acc[doc.drawingKind] = (acc[doc.drawingKind] || 0) + 1
+        }
+        return acc
+      }, {} as Record<string, number>)
+  
+      // 가장 많은 DrawingKind 찾기
+      const primaryDrawingKind = Object.entries(stats)
+        .sort(([,a], [,b]) => b - a)[0]?.[0] as 'B3' | 'B4' | 'B5' | undefined
+  
+      return {
+        stats,
+        totalDocuments: documents.length,
+        primaryDrawingKind: primaryDrawingKind || null
+      }
+    } catch (err) {
+      console.error("Error fetching user vendor document stats:", err)
+      return { stats: {}, totalDocuments: 0, primaryDrawingKind: null }
+    }
   }
\ No newline at end of file