From 89274bffa596ffdfc4275fb8d11cdb02ff9a2d02 Mon Sep 17 00:00:00 2001 From: dujinkim 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 ++++++++++++++++++++++------------ 1 file changed, 136 insertions(+), 69 deletions(-) (limited to 'lib/contact-possible-items/service.ts') 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 | 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[] = [] + + 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 | 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[] = [] + 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 } } } -- cgit v1.2.3