1 files changed, 192 insertions, 0 deletions
diff --git a/components/common/project/project-service.ts b/components/common/project/project-service.ts
new file mode 100644
index 00000000..6c103c6f
--- /dev/null
+++ b/components/common/project/project-service.ts
@@ -0,0 +1,192 @@
+"use server"
+
+import db from "@/db/db"
+import { projects, biddingProjects } from "@/db/schema"
+import { eq, ilike, or } from "drizzle-orm"
+
+// 통합 프로젝트 타입 정의
+export interface UnifiedProject {
+  id: string // projects의 경우 id를 string으로, biddingProjects의 경우 pspid
+  code: string
+  name: string
+  type: string
+  source: 'projects' | 'biddingProjects' // 어느 테이블에서 왔는지 구분
+  originalData?: any // 원본 데이터 (필요시 사용)
+}
+
+// 프로젝트 검색 옵션
+export interface ProjectSearchOptions {
+  searchFrom: 'both' | 'projects' | 'biddingProjects'
+  searchTerm?: string
+  limit?: number
+}
+
+// 프로젝트 정보 조회 결과 타입
+export interface ProjectInfo {
+  projectCode: string
+  projectName: string | null
+  shipType?: string | null
+  projectMsrm?: string | null
+  projectHtDivision?: string | null
+  source: 'projects' | 'biddingProjects'
+}
+
+/**
+ * 통합 프로젝트 검색 - projects와 biddingProjects 테이블에서 검색
+ */
+export async function searchUnifiedProjects(options: ProjectSearchOptions): Promise<{
+  success: boolean
+  data: UnifiedProject[]
+  error?: string
+}> {
+  try {
+    const { searchFrom, searchTerm, limit = 100 } = options
+    const results: UnifiedProject[] = []
+
+    // projects 테이블에서 검색
+    if (searchFrom === 'both' || searchFrom === 'projects') {
+      try {
+        let projectsQuery = db.select({
+          id: projects.id,
+          code: projects.code,
+          name: projects.name,
+          type: projects.type
+        }).from(projects)
+
+        if (searchTerm && searchTerm.trim()) {
+          const term = `%${searchTerm.trim()}%`
+          projectsQuery = projectsQuery.where(
+            or(
+              ilike(projects.code, term),
+              ilike(projects.name, term)
+            )
+          )
+        }
+
+        const projectsResults = await projectsQuery.limit(limit)
+
+        for (const project of projectsResults) {
+          results.push({
+            id: project.id.toString(),
+            code: project.code,
+            name: project.name,
+            type: project.type || 'project',
+            source: 'projects',
+            originalData: project
+          })
+        }
+      } catch (error) {
+        console.error('Error searching projects table:', error)
+        // projects 테이블 검색 실패해도 계속 진행
+      }
+    }
+
+    // biddingProjects 테이블에서 검색
+    if (searchFrom === 'both' || searchFrom === 'biddingProjects') {
+      try {
+        let biddingQuery = db.select({
+          pspid: biddingProjects.pspid,
+          projNm: biddingProjects.projNm,
+          pjtType: biddingProjects.pjtType,
+          ptypeNm: biddingProjects.ptypeNm
+        }).from(biddingProjects)
+
+        if (searchTerm && searchTerm.trim()) {
+          const term = `%${searchTerm.trim()}%`
+          biddingQuery = biddingQuery.where(
+            or(
+              ilike(biddingProjects.pspid, term),
+              ilike(biddingProjects.projNm, term)
+            )
+          )
+        }
+
+        const biddingResults = await biddingQuery.limit(limit)
+
+        for (const biddingProject of biddingResults) {
+          results.push({
+            id: biddingProject.pspid,
+            code: biddingProject.pspid,
+            name: biddingProject.projNm || '',
+            type: biddingProject.pjtType || 'bidding',
+            source: 'biddingProjects',
+            originalData: biddingProject
+          })
+        }
+      } catch (error) {
+        console.error('Error searching biddingProjects table:', error)
+        // biddingProjects 테이블 검색 실패해도 계속 진행
+      }
+    }
+
+    return {
+      success: true,
+      data: results
+    }
+  } catch (error) {
+    console.error('Error in searchUnifiedProjects:', error)
+    return {
+      success: false,
+      data: [],
+      error: '프로젝트 검색 중 오류가 발생했습니다.'
+    }
+  }
+}
+
+/**
+ * 프로젝트 코드로 상세 정보 조회
+ */
+export async function getProjectInfoByCode(projectCode: string, searchFrom: 'both' | 'projects' | 'biddingProjects' = 'both'): Promise<ProjectInfo | null> {
+  if (!projectCode.trim()) {
+    return null
+  }
+
+  // projects 테이블에서 먼저 검색
+  if (searchFrom === 'both' || searchFrom === 'projects') {
+    try {
+      const projectInfo = await db.select().from(projects).where(eq(projects.code, projectCode)).limit(1)
+      
+      if (projectInfo && projectInfo.length > 0) {
+        return {
+          projectCode: projectInfo[0].code,
+          projectName: projectInfo[0].name,
+          shipType: projectInfo[0].SKND || undefined,
+          projectHtDivision: projectInfo[0].type || undefined,
+          source: 'projects'
+        }
+      }
+    } catch (error) {
+      console.error('Error searching in projects table:', error)
+    }
+  }
+
+  // biddingProjects 테이블에서 검색
+  if (searchFrom === 'both' || searchFrom === 'biddingProjects') {
+    try {
+      const projectInfo = await db.select().from(biddingProjects).where(eq(biddingProjects.pspid, projectCode)).limit(1)
+      
+      if (projectInfo && projectInfo.length > 0) {
+        let projectHtDivision = null
+        if (projectInfo[0].pjtType === 'SHIP') {
+          projectHtDivision = 'H'
+        } else if (projectInfo[0].pjtType === 'HULL') {
+          projectHtDivision = 'H'
+        } else if (projectInfo[0].pjtType === 'TOP') {
+          projectHtDivision = 'T'
+        }
+
+        return {
+          projectCode: projectInfo[0].pspid,
+          projectName: projectInfo[0].projNm,
+          projectMsrm: projectInfo[0].ptypeNm,
+          projectHtDivision,
+          source: 'biddingProjects'
+        }
+      }
+    } catch (error) {
+      console.error('Error searching in biddingProjects table:', error)
+    }
+  }
+
+  return null
+}