From 89274bffa596ffdfc4275fb8d11cdb02ff9a2d02 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Mon, 13 Oct 2025 00:22:54 +0000
Subject: (최겸) 기술영업 import 수정 2
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 lib/contact-possible-items/service.ts              | 205 ++++++++++++++-------
 .../table/contact-possible-items-table.tsx         | 203 ++++++++++----------
 lib/contact-possible-items/validations.ts          | 101 +++++-----
 3 files changed, 280 insertions(+), 229 deletions(-)

(limited to 'lib/contact-possible-items')

diff --git a/lib/contact-possible-items/service.ts b/lib/contact-possible-items/service.ts
index 960df17e..72b93e16 100644
--- a/lib/contact-possible-items/service.ts
+++ b/lib/contact-possible-items/service.ts
@@ -1,12 +1,12 @@
 "use server"
 
-import db from "@/db/db"
+import { revalidatePath } from 'next/cache'
+import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte } from 'drizzle-orm'
+import db from '@/db/db'
 import { techSalesContactPossibleItems } from "@/db/schema/techSales"
 import { techVendors, techVendorContacts, techVendorPossibleItems } from "@/db/schema/techVendors"
 import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"
-import { eq, desc, ilike, count, or } from "drizzle-orm"
-import { revalidatePath } from "next/cache"
-import { unstable_noStore } from "next/cache"
+import { filterColumns } from '@/lib/filter-columns'
 import { GetContactPossibleItemsSchema } from "./validations"
 
 // 담당자별 아이템 상세 타입 정의 (뷰 기반)
@@ -45,35 +45,130 @@ export interface ContactPossibleItemDetail {
 }
 
 /**
- * 담당자별 아이템 목록 조회 (뷰 사용)
+ * 담당자별 아이템 목록 조회 (간단한 필터, 정렬, 검색 지원)
  */
 export async function getContactPossibleItems(input: GetContactPossibleItemsSchema) {
-  unstable_noStore()
-
   try {
-    const offset = (input.page - 1) * input.per_page
-    
-    console.log("=== getContactPossibleItems DEBUG ===")
-    console.log("Input:", input)
-    console.log("Offset:", offset)
-    
-    // 검색 조건 (벤더명, 연락처명으로만 검색)
-    let whereCondition
+    const offset = (input.page - 1) * input.perPage
+
+    // ✅ 1) 고급 필터 조건
+    let advancedWhere: SQL<unknown> | undefined = undefined
+    if (input.filters && input.filters.length > 0) {
+      advancedWhere = filterColumns({
+        table: techSalesContactPossibleItems,
+        filters: input.filters as any,
+        joinOperator: input.joinOperator || 'and',
+      })
+    }
+
+    // ✅ 2) 기본 필터 조건들
+    const basicConditions: SQL<unknown>[] = []
+
+    if (input.vendorName) {
+      basicConditions.push(ilike(techVendors.vendorName, `%${input.vendorName}%`))
+    }
+
+    if (input.contactName) {
+      basicConditions.push(ilike(techVendorContacts.contactName, `%${input.contactName}%`))
+    }
+
+    if (input.vendorCode) {
+      basicConditions.push(ilike(techVendors.vendorCode, `%${input.vendorCode}%`))
+    }
+
+    // 아이템 코드와 작업 유형은 조인된 테이블에서 검색
+    if (input.itemCode) {
+      const itemCodeConditions = [
+        ilike(itemShipbuilding.itemCode, `%${input.itemCode}%`),
+        ilike(itemOffshoreTop.itemCode, `%${input.itemCode}%`),
+        ilike(itemOffshoreHull.itemCode, `%${input.itemCode}%`)
+      ]
+      basicConditions.push(or(...itemCodeConditions))
+    }
+
+    if (input.workType) {
+      const workTypeConditions = [
+        ilike(itemShipbuilding.workType, `%${input.workType}%`),
+        ilike(itemOffshoreTop.workType, `%${input.workType}%`),
+        ilike(itemOffshoreHull.workType, `%${input.workType}%`)
+      ]
+      basicConditions.push(or(...workTypeConditions))
+    }
+
+    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined
+
+    // ✅ 3) 글로벌 검색 조건
+    let globalWhere: SQL<unknown> | undefined = undefined
     if (input.search) {
-      const searchTerm = `%${input.search}%`
-      whereCondition = or(
-        ilike(techVendors.vendorName, searchTerm),
-        ilike(techVendorContacts.contactName, searchTerm)
-      )
-      console.log("Search term:", searchTerm)
-    } else {
-      console.log("No search condition")
+      const s = `%${input.search}%`
+      const searchConditions = [
+        ilike(techVendors.vendorName, s),
+        ilike(techVendorContacts.contactName, s),
+        ilike(techVendors.vendorCode, s),
+        ilike(techVendors.email, s),
+        ilike(techVendorContacts.contactEmail, 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(techSalesContactPossibleItems)
+      .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
+      .leftJoin(techVendorPossibleItems, eq(techSalesContactPossibleItems.vendorPossibleItemId, techVendorPossibleItems.id))
+      .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
+      .where(finalWhere)
+
+    const total = totalResult[0]?.count || 0
+
+    if (total === 0) {
+      return { data: [], pageCount: 0, total: 0 }
     }
 
-    // 새로운 스키마에 맞게 수정 - 아이템 정보를 별도로 조회해야 함
-    console.log("Executing data query...")
-    
-    // 1단계: 기본 매핑 정보 조회
+    console.log("Total contact possible items:", total)
+
+    // ✅ 6) 정렬 및 페이징
+    const orderByColumns: any[] = []
+
+    for (const sort of input.sort) {
+      const column = sort.id
+
+      // techSalesContactPossibleItems 테이블의 컬럼들
+      if (column in techSalesContactPossibleItems) {
+        const contactItemColumn = techSalesContactPossibleItems[column as keyof typeof techSalesContactPossibleItems]
+        orderByColumns.push(sort.desc ? desc(contactItemColumn) : asc(contactItemColumn))
+      }
+      // techVendors 테이블의 컬럼들
+      else if (column === 'vendorName' || column === 'vendorCode' || column === 'email') {
+        const vendorColumn = techVendors[column as keyof typeof techVendors]
+        orderByColumns.push(sort.desc ? desc(vendorColumn) : asc(vendorColumn))
+      }
+      // techVendorContacts 테이블의 컬럼들
+      else if (column === 'contactName' || column === 'contactEmail') {
+        const contactColumn = techVendorContacts[column as keyof typeof techVendorContacts]
+        orderByColumns.push(sort.desc ? desc(contactColumn) : asc(contactColumn))
+      }
+      // 조인된 테이블의 컬럼들 (실제로는 계산된 값이므로 기본 정렬 사용)
+      else if (column === 'itemCode' || column === 'workType') {
+        // 조인된 테이블의 컬럼은 직접 정렬할 수 없으므로 기본 정렬 사용
+        orderByColumns.push(desc(techSalesContactPossibleItems.createdAt))
+      }
+    }
+
+    if (orderByColumns.length === 0) {
+      orderByColumns.push(desc(techSalesContactPossibleItems.createdAt))
+    }
+
+    // ✅ 7) 메인 쿼리 (단순 조인으로 변경)
     const basicItems = await db
       .select({
         // 기본 매핑 정보
@@ -82,7 +177,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
         vendorPossibleItemId: techSalesContactPossibleItems.vendorPossibleItemId,
         createdAt: techSalesContactPossibleItems.createdAt,
         updatedAt: techSalesContactPossibleItems.updatedAt,
-        
+
         // 벤더 정보
         vendorId: techVendors.id,
         vendorName: techVendors.vendorName,
@@ -92,7 +187,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
         vendorCountry: techVendors.country,
         vendorStatus: techVendors.status,
         techVendorType: techVendors.techVendorType,
-        
+
         // 연락처 정보
         contactName: techVendorContacts.contactName,
         contactPosition: techVendorContacts.contactPosition,
@@ -101,7 +196,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
         contactPhone: techVendorContacts.contactPhone,
         contactCountry: techVendorContacts.contactCountry,
         isPrimary: techVendorContacts.isPrimary,
-        
+
         // 벤더 가능 아이템 ID 정보
         shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
         offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
@@ -111,12 +206,12 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
       .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
       .leftJoin(techVendorPossibleItems, eq(techSalesContactPossibleItems.vendorPossibleItemId, techVendorPossibleItems.id))
       .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
-      .where(whereCondition)
-      .orderBy(desc(techSalesContactPossibleItems.createdAt))
+      .where(finalWhere)
+      .orderBy(...orderByColumns)
       .offset(offset)
-      .limit(input.per_page)
+      .limit(input.perPage)
 
-    // 2단계: 각 아이템의 상세 정보를 별도로 조회하여 합치기
+    // ✅ 8) 각 아이템의 상세 정보를 별도로 조회하여 합치기
     const items = await Promise.all(basicItems.map(async (item) => {
       let itemCode = null;
       let workType = null;
@@ -135,7 +230,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
           .from(itemShipbuilding)
           .where(eq(itemShipbuilding.id, item.shipbuildingItemId))
           .limit(1);
-        
+
         if (shipItem.length > 0) {
           itemCode = shipItem[0].itemCode;
           workType = shipItem[0].workType;
@@ -153,7 +248,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
           .from(itemOffshoreTop)
           .where(eq(itemOffshoreTop.id, item.offshoreTopItemId))
           .limit(1);
-        
+
         if (topItem.length > 0) {
           itemCode = topItem[0].itemCode;
           workType = topItem[0].workType;
@@ -171,7 +266,7 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
           .from(itemOffshoreHull)
           .where(eq(itemOffshoreHull.id, item.offshoreHullItemId))
           .limit(1);
-        
+
         if (hullItem.length > 0) {
           itemCode = hullItem[0].itemCode;
           workType = hullItem[0].workType;
@@ -190,41 +285,13 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
       };
     }))
 
-    console.log("Items found:", items.length)
-    console.log("First 3 items:", items.slice(0, 3))
-
-    // 전체 개수 조회 (동일한 조인과 검색 조건 적용)
-    console.log("Executing count query...")
-    const [{ count: total }] = await db
-      .select({ count: count() })
-      .from(techSalesContactPossibleItems)
-      .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
-      .leftJoin(techVendorPossibleItems, eq(techSalesContactPossibleItems.vendorPossibleItemId, techVendorPossibleItems.id))
-      .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
-      .where(whereCondition)
-
-    console.log("Total count:", total)
-
-    const pageCount = Math.ceil(total / input.per_page)
+    // ✅ 9) 페이지 수 계산
+    const pageCount = Math.ceil(total / input.perPage)
 
-    console.log("Final result:", { dataLength: items.length, pageCount, total })
-    console.log("=== END DEBUG ===")
-
-    return {
-      data: items as ContactPossibleItemDetail[],
-      pageCount,
-      total,
-    }
+    return { data: items as ContactPossibleItemDetail[], pageCount, total }
   } catch (err) {
-    console.error("=== ERROR in getContactPossibleItems ===")
     console.error("Error fetching contact possible items:", err)
-    console.error("Input was:", input)
-    console.error("=== END ERROR ===")
-    return {
-      data: [],
-      pageCount: 0,
-      total: 0,
-    }
+    return { data: [], pageCount: 0, total: 0 }
   }
 }
 
diff --git a/lib/contact-possible-items/table/contact-possible-items-table.tsx b/lib/contact-possible-items/table/contact-possible-items-table.tsx
index 3828e26c..a46f71ea 100644
--- a/lib/contact-possible-items/table/contact-possible-items-table.tsx
+++ b/lib/contact-possible-items/table/contact-possible-items-table.tsx
@@ -1,102 +1,103 @@
-"use client"
-
-import React from "react"
-import { DataTable } from "@/components/data-table/data-table"
-import { ContactPossibleItemsTableToolbarActions } from "./contact-possible-items-table-toolbar-actions"
-import { getColumns } from "./contact-possible-items-table-columns"
-import { ContactPossibleItemDetail } from "../service"
-import { DeleteContactPossibleItemsDialog } from "./delete-contact-possible-items-dialog"
-import { useDataTable } from "@/hooks/use-data-table"
-import { DataTableAdvancedToolbar } from "@/components/data-table/data-table-advanced-toolbar"
-import { type DataTableAdvancedFilterField } from "@/types/table"
-
-// 필터 필드 정의
-const advancedFilterFields: DataTableAdvancedFilterField<ContactPossibleItemDetail>[] = [
-  {
-    id: "contactName",
-    label: "담당자명",
-    type: "text",
-    placeholder: "담당자명으로 검색...",
-  },
-  {
-    id: "vendorName", 
-    label: "벤더명",
-    type: "text",
-    placeholder: "벤더명으로 검색...",
-  },
-  {
-    id: "vendorCode",
-    label: "벤더코드",
-    type: "text", 
-    placeholder: "벤더코드로 검색...",
-  },
-  {
-    id: "itemCode",
-    label: "아이템코드",
-    type: "text",
-    placeholder: "아이템코드로 검색...",
-  },
-  {
-    id: "workType",
-    label: "공종",
-    type: "text",
-    placeholder: "공종으로 검색...",
-  },
-]
-
-interface ContactPossibleItemsTableProps {
-  contactPossibleItemsPromise: Promise<{
-    data: ContactPossibleItemDetail[]
-    pageCount: number
-    total: number
-  }>
-}
-
-export function ContactPossibleItemsTable({
-  contactPossibleItemsPromise,
-}: ContactPossibleItemsTableProps) {
-  const { data, pageCount, total } = React.use(contactPossibleItemsPromise)
-
-  const [rowAction, setRowAction] = React.useState<any | null>(null)
-
-  const columns = React.useMemo(
-    () => getColumns({ setRowAction }),
-    [setRowAction]
-  )
-
-  const { table } = useDataTable({
-    data,
-    columns,
-    pageCount,
-    rowCount: total,
-  })
-
-  return (
-    <div className="w-full space-y-2.5 overflow-auto">
-      {/* 메인 테이블 */}
-      <DataTable table={table}>
-        <DataTableAdvancedToolbar
-          table={table}
-          filterFields={advancedFilterFields}
-          shallow={false}
-        >
-          <ContactPossibleItemsTableToolbarActions 
-            table={table} 
-          />
-        </DataTableAdvancedToolbar>
-      </DataTable>
-
-      <DeleteContactPossibleItemsDialog
-        open={rowAction?.type === "delete"}
-        onOpenChange={() => setRowAction(null)}
-        contactPossibleItems={
-          rowAction?.type === "delete" && rowAction.row
-            ? [rowAction.row.original]
-            : []
-        }
-        showTrigger={false}
-        onSuccess={() => setRowAction(null)}
-      />
-    </div>
-  )
+"use client"
+
+import React from "react"
+import { DataTable } from "@/components/data-table/data-table"
+import { ContactPossibleItemsTableToolbarActions } from "./contact-possible-items-table-toolbar-actions"
+import { getColumns } from "./contact-possible-items-table-columns"
+import { ContactPossibleItemDetail } from "../service"
+import { DeleteContactPossibleItemsDialog } from "./delete-contact-possible-items-dialog"
+import { useDataTable } from "@/hooks/use-data-table"
+import { DataTableAdvancedToolbar } from "@/components/data-table/data-table-advanced-toolbar"
+import { type DataTableAdvancedFilterField } from "@/types/table"
+
+// 필터 필드 정의
+const advancedFilterFields: DataTableAdvancedFilterField<ContactPossibleItemDetail>[] = [
+  {
+    id: "contactName",
+    label: "담당자명",
+    type: "text",
+    placeholder: "담당자명으로 검색...",
+  },
+  {
+    id: "vendorName", 
+    label: "벤더명",
+    type: "text",
+    placeholder: "벤더명으로 검색...",
+  },
+  {
+    id: "vendorCode",
+    label: "벤더코드",
+    type: "text", 
+    placeholder: "벤더코드로 검색...",
+  },
+  {
+    id: "itemCode",
+    label: "아이템코드",
+    type: "text",
+    placeholder: "아이템코드로 검색...",
+  },
+  {
+    id: "workType",
+    label: "공종",
+    type: "text",
+    placeholder: "공종으로 검색...",
+  },
+]
+
+interface ContactPossibleItemsTableProps {
+  promises: Promise<
+    [
+      Awaited<ReturnType<typeof getContactPossibleItems>>
+    ]
+  >
+}
+
+export function ContactPossibleItemsTable({
+  promises,
+}: ContactPossibleItemsTableProps) {
+  const [{ data, pageCount, total }] = React.use(promises)
+
+  const [rowAction, setRowAction] = React.useState<any | null>(null)
+
+  const columns = React.useMemo(
+    () => getColumns({ setRowAction }),
+    [setRowAction]
+  )
+
+  const { table } = useDataTable({
+    data,
+    columns,
+    pageCount,
+    rowCount: total,
+    enableAdvancedFilter: true,
+  })
+
+  return (
+    <div className="w-full space-y-2.5 overflow-auto">
+      {/* 메인 테이블 */}
+      <DataTable table={table}>
+        <DataTableAdvancedToolbar
+          table={table}
+          filterFields={advancedFilterFields}
+          shallow={false}
+        >
+          <ContactPossibleItemsTableToolbarActions 
+            table={table} 
+          />
+        </DataTableAdvancedToolbar>
+      </DataTable>
+
+      <DeleteContactPossibleItemsDialog
+        open={rowAction?.type === "delete"}
+        onOpenChange={() => setRowAction(null)}
+        contactPossibleItems={
+          rowAction?.type === "delete" && rowAction.row
+            ? [rowAction.row.original]
+            : []
+        }
+        showTrigger={false}
+        onSuccess={() => setRowAction(null)}
+      />
+    </div>
+  )
 } 
\ No newline at end of file
diff --git a/lib/contact-possible-items/validations.ts b/lib/contact-possible-items/validations.ts
index 609be0df..e5c301cb 100644
--- a/lib/contact-possible-items/validations.ts
+++ b/lib/contact-possible-items/validations.ts
@@ -1,59 +1,42 @@
-import { createSearchParamsCache, parseAsInteger, parseAsString } from "nuqs/server"
-import { z } from "zod"
-
-// 검색 파라미터 스키마 (뷰 기반으로 수정)
-export const searchParamsSchema = z.object({
-  page: z.coerce.number().default(1),
-  per_page: z.coerce.number().default(10),
-  sort: z.string().optional(),
-  search: z.string().optional(), // 통합 검색
-  contactName: z.string().optional(),
-  vendorName: z.string().optional(),
-  itemCode: z.string().optional(),
-  vendorCode: z.string().optional(),
-  workType: z.string().optional(),
-  from: z.string().optional(),
-  to: z.string().optional(),
-})
-
-// searchParams 캐시 생성
-export const searchParamsCache = createSearchParamsCache({
-  page: parseAsInteger.withDefault(1),
-  per_page: parseAsInteger.withDefault(10),
-  sort: parseAsString.withDefault(""),
-  search: parseAsString.withDefault(""), // 통합 검색 추가
-  contactName: parseAsString.withDefault(""),
-  vendorName: parseAsString.withDefault(""),
-  itemCode: parseAsString.withDefault(""),
-  vendorCode: parseAsString.withDefault(""),
-  workType: parseAsString.withDefault(""),
-  from: parseAsString.withDefault(""),
-  to: parseAsString.withDefault(""),
-})
-
-export type SearchParamsCache = typeof searchParamsCache
-
-// 담당자별 아이템 생성용 스키마 (FK만 사용)
-export const contactPossibleItemSchema = z.object({
-  contactId: z.number().min(1, "담당자를 선택해주세요"),
-  vendorPossibleItemId: z.number().min(1, "벤더 가능 아이템을 선택해주세요"),
-})
-
-export type ContactPossibleItemSchema = z.infer<typeof contactPossibleItemSchema> 
-
-// 조회용 스키마 (searchParamsCache와 일치하도록 수정)
-export const getContactPossibleItemsSchema = z.object({
-  page: z.number().default(1),
-  per_page: z.number().default(10),
-  sort: z.string().optional(),
-  search: z.string().optional(),
-  contactName: z.string().optional(),
-  vendorName: z.string().optional(),
-  itemCode: z.string().optional(),
-  vendorCode: z.string().optional(),
-  workType: z.string().optional(),
-  from: z.string().optional(),
-  to: z.string().optional(),
-})
-
-export type GetContactPossibleItemsSchema = z.infer<typeof getContactPossibleItemsSchema> 
\ No newline at end of file
+import { techSalesContactPossibleItems } from "@/db/schema/techSales"
+import {
+  createSearchParamsCache,
+  parseAsArrayOf,
+  parseAsInteger,
+  parseAsString,
+  parseAsStringEnum,
+} from "nuqs/server"
+import * as z from "zod"
+
+import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers"
+
+export const searchParamsCache = createSearchParamsCache({
+  flags: parseAsArrayOf(z.enum(["advancedTable", "floatingBar"])).withDefault([]),
+  page: parseAsInteger.withDefault(1),
+  perPage: parseAsInteger.withDefault(10),
+  sort: getSortingStateParser<typeof techSalesContactPossibleItems.$inferSelect>().withDefault([
+    { id: "createdAt", desc: true },
+  ]),
+
+  // 기본 필터
+  vendorName: parseAsString.withDefault(""),
+  contactName: parseAsString.withDefault(""),
+  vendorCode: parseAsString.withDefault(""),
+  itemCode: parseAsString.withDefault(""),
+  workType: parseAsString.withDefault(""),
+
+  // 고급 필터
+  filters: getFiltersStateParser().withDefault([]),
+  joinOperator: parseAsStringEnum(["and", "or"]).withDefault("and"),
+  search: parseAsString.withDefault(""),
+})
+
+export type GetContactPossibleItemsSchema = Awaited<ReturnType<typeof searchParamsCache.parse>>
+
+// 담당자별 아이템 생성용 스키마 (FK만 사용)
+export const contactPossibleItemSchema = z.object({
+  contactId: z.number().min(1, "담당자를 선택해주세요"),
+  vendorPossibleItemId: z.number().min(1, "벤더 가능 아이템을 선택해주세요"),
+})
+
+export type ContactPossibleItemSchema = z.infer<typeof contactPossibleItemSchema> 
\ No newline at end of file
-- 
cgit v1.2.3