diff options
Diffstat (limited to 'lib/contact-possible-items/service.ts')
| -rw-r--r-- | lib/contact-possible-items/service.ts | 205 |
1 files changed, 136 insertions, 69 deletions
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 }
}
}
|