From 2650b7c0bb0ea12b68a58c0439f72d61df04b2f1 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 25 Jul 2025 07:51:15 +0000 Subject: (대표님) 정기평가 대상, 미들웨어 수정, nextauth 토큰 처리 개선, GTC 등 (최겸) 기술영업 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/contact-possible-items/service.ts | 99 ++++++++++++++++++++++++++++++----- 1 file changed, 87 insertions(+), 12 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 f4b89368..960df17e 100644 --- a/lib/contact-possible-items/service.ts +++ b/lib/contact-possible-items/service.ts @@ -3,6 +3,7 @@ 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" @@ -29,15 +30,16 @@ export interface ContactPossibleItemDetail { // 연락처 정보 contactName: string | null contactPosition: string | null + contactTitle: string | null contactEmail: string | null contactPhone: string | null contactCountry: string | null isPrimary: boolean | null // 아이템 정보 - itemCode: string | null workType: string | null shipTypes: string | null + itemCode: string | null itemList: string | null subItemList: string | null } @@ -55,13 +57,11 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche console.log("Input:", input) console.log("Offset:", offset) - // 검색 조건 + // 검색 조건 (벤더명, 연락처명으로만 검색) let whereCondition if (input.search) { const searchTerm = `%${input.search}%` whereCondition = or( - ilike(techVendorPossibleItems.itemCode, searchTerm), - ilike(techVendorPossibleItems.itemList, searchTerm), ilike(techVendors.vendorName, searchTerm), ilike(techVendorContacts.contactName, searchTerm) ) @@ -70,9 +70,11 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche console.log("No search condition") } - // 원본 테이블들을 직접 조인해서 데이터 조회 + // 새로운 스키마에 맞게 수정 - 아이템 정보를 별도로 조회해야 함 console.log("Executing data query...") - const items = await db + + // 1단계: 기본 매핑 정보 조회 + const basicItems = await db .select({ // 기본 매핑 정보 id: techSalesContactPossibleItems.id, @@ -94,17 +96,16 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche // 연락처 정보 contactName: techVendorContacts.contactName, contactPosition: techVendorContacts.contactPosition, + contactTitle: techVendorContacts.contactTitle, contactEmail: techVendorContacts.contactEmail, contactPhone: techVendorContacts.contactPhone, contactCountry: techVendorContacts.contactCountry, isPrimary: techVendorContacts.isPrimary, - // 벤더 가능 아이템 정보 - itemCode: techVendorPossibleItems.itemCode, - workType: techVendorPossibleItems.workType, - shipTypes: techVendorPossibleItems.shipTypes, - itemList: techVendorPossibleItems.itemList, - subItemList: techVendorPossibleItems.subItemList, + // 벤더 가능 아이템 ID 정보 + shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId, + offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId, + offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId, }) .from(techSalesContactPossibleItems) .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id)) @@ -115,6 +116,80 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche .offset(offset) .limit(input.per_page) + // 2단계: 각 아이템의 상세 정보를 별도로 조회하여 합치기 + const items = await Promise.all(basicItems.map(async (item) => { + let itemCode = null; + let workType = null; + let shipTypes = null; + let itemList = null; + let subItemList = null; + + if (item.shipbuildingItemId) { + const shipItem = await db + .select({ + itemCode: itemShipbuilding.itemCode, + workType: itemShipbuilding.workType, + shipTypes: itemShipbuilding.shipTypes, + itemList: itemShipbuilding.itemList, + }) + .from(itemShipbuilding) + .where(eq(itemShipbuilding.id, item.shipbuildingItemId)) + .limit(1); + + if (shipItem.length > 0) { + itemCode = shipItem[0].itemCode; + workType = shipItem[0].workType; + shipTypes = shipItem[0].shipTypes; + itemList = shipItem[0].itemList; + } + } else if (item.offshoreTopItemId) { + const topItem = await db + .select({ + itemCode: itemOffshoreTop.itemCode, + workType: itemOffshoreTop.workType, + itemList: itemOffshoreTop.itemList, + subItemList: itemOffshoreTop.subItemList, + }) + .from(itemOffshoreTop) + .where(eq(itemOffshoreTop.id, item.offshoreTopItemId)) + .limit(1); + + if (topItem.length > 0) { + itemCode = topItem[0].itemCode; + workType = topItem[0].workType; + itemList = topItem[0].itemList; + subItemList = topItem[0].subItemList; + } + } else if (item.offshoreHullItemId) { + const hullItem = await db + .select({ + itemCode: itemOffshoreHull.itemCode, + workType: itemOffshoreHull.workType, + itemList: itemOffshoreHull.itemList, + subItemList: itemOffshoreHull.subItemList, + }) + .from(itemOffshoreHull) + .where(eq(itemOffshoreHull.id, item.offshoreHullItemId)) + .limit(1); + + if (hullItem.length > 0) { + itemCode = hullItem[0].itemCode; + workType = hullItem[0].workType; + itemList = hullItem[0].itemList; + subItemList = hullItem[0].subItemList; + } + } + + return { + ...item, + itemCode, + workType, + shipTypes, + itemList, + subItemList, + }; + })) + console.log("Items found:", items.length) console.log("First 3 items:", items.slice(0, 3)) -- cgit v1.2.3