diff options
Diffstat (limited to 'lib/contact-possible-items')
| -rw-r--r-- | lib/contact-possible-items/service.ts | 205 | ||||
| -rw-r--r-- | lib/contact-possible-items/table/contact-possible-items-table.tsx | 203 | ||||
| -rw-r--r-- | lib/contact-possible-items/validations.ts | 101 |
3 files changed, 280 insertions, 229 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 }
}
}
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 |