diff options
Diffstat (limited to 'lib/contact-possible-items/service-add-mapping.ts')
| -rw-r--r-- | lib/contact-possible-items/service-add-mapping.ts | 312 |
1 files changed, 312 insertions, 0 deletions
diff --git a/lib/contact-possible-items/service-add-mapping.ts b/lib/contact-possible-items/service-add-mapping.ts new file mode 100644 index 00000000..39215549 --- /dev/null +++ b/lib/contact-possible-items/service-add-mapping.ts @@ -0,0 +1,312 @@ +"use server" + +import { revalidatePath } from 'next/cache' +import { eq, and, or, ilike, inArray } 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" + +/** + * 조선 아이템 검색 + */ +export async function searchShipbuildingItems(search: string = "") { + try { + const where = search + ? or( + ilike(itemShipbuilding.itemCode, `%${search}%`), + ilike(itemShipbuilding.itemList, `%${search}%`), + ilike(itemShipbuilding.workType, `%${search}%`), + ilike(itemShipbuilding.shipTypes, `%${search}%`) + ) + : undefined + + const items = await db + .select({ + id: itemShipbuilding.id, + itemCode: itemShipbuilding.itemCode, + itemList: itemShipbuilding.itemList, + workType: itemShipbuilding.workType, + shipTypes: itemShipbuilding.shipTypes, + }) + .from(itemShipbuilding) + .where(where) + .limit(100) + + return { data: items, error: null } + } catch (err) { + console.error("조선 아이템 검색 오류:", err) + return { data: [], error: "조선 아이템 검색에 실패했습니다." } + } +} + +/** + * 해양 TOP 아이템 검색 + */ +export async function searchOffshoreTopItems(search: string = "") { + try { + const where = search + ? or( + ilike(itemOffshoreTop.itemCode, `%${search}%`), + ilike(itemOffshoreTop.itemList, `%${search}%`), + ilike(itemOffshoreTop.workType, `%${search}%`) + ) + : undefined + + const items = await db + .select({ + id: itemOffshoreTop.id, + itemCode: itemOffshoreTop.itemCode, + itemList: itemOffshoreTop.itemList, + workType: itemOffshoreTop.workType, + subItemList: itemOffshoreTop.subItemList, + }) + .from(itemOffshoreTop) + .where(where) + .limit(100) + + return { data: items, error: null } + } catch (err) { + console.error("해양 TOP 아이템 검색 오류:", err) + return { data: [], error: "해양 TOP 아이템 검색에 실패했습니다." } + } +} + +/** + * 해양 HULL 아이템 검색 + */ +export async function searchOffshoreHullItems(search: string = "") { + try { + const where = search + ? or( + ilike(itemOffshoreHull.itemCode, `%${search}%`), + ilike(itemOffshoreHull.itemList, `%${search}%`), + ilike(itemOffshoreHull.workType, `%${search}%`) + ) + : undefined + + const items = await db + .select({ + id: itemOffshoreHull.id, + itemCode: itemOffshoreHull.itemCode, + itemList: itemOffshoreHull.itemList, + workType: itemOffshoreHull.workType, + subItemList: itemOffshoreHull.subItemList, + }) + .from(itemOffshoreHull) + .where(where) + .limit(100) + + return { data: items, error: null } + } catch (err) { + console.error("해양 HULL 아이템 검색 오류:", err) + return { data: [], error: "해양 HULL 아이템 검색에 실패했습니다." } + } +} + +/** + * 기술영업 벤더 검색 + */ +export async function searchTechVendors(search: string = "") { + try { + const where = search + ? or( + ilike(techVendors.vendorCode, `%${search}%`), + ilike(techVendors.vendorName, `%${search}%`), + ilike(techVendors.email, `%${search}%`) + ) + : undefined + + const vendors = await db + .select({ + id: techVendors.id, + vendorCode: techVendors.vendorCode, + vendorName: techVendors.vendorName, + email: techVendors.email, + techVendorType: techVendors.techVendorType, + status: techVendors.status, + }) + .from(techVendors) + .where(where) + .limit(100) + + return { data: vendors, error: null } + } catch (err) { + console.error("벤더 검색 오류:", err) + return { data: [], error: "벤더 검색에 실패했습니다." } + } +} + +/** + * 여러 벤더의 담당자 조회 + */ +export async function getTechVendorsContactsForMapping(vendorIds: number[]) { + try { + if (vendorIds.length === 0) { + return { data: {}, error: null } + } + + const contactsWithVendor = await db + .select({ + contactId: techVendorContacts.id, + contactName: techVendorContacts.contactName, + contactPosition: techVendorContacts.contactPosition, + contactTitle: techVendorContacts.contactTitle, + contactEmail: techVendorContacts.contactEmail, + contactPhone: techVendorContacts.contactPhone, + isPrimary: techVendorContacts.isPrimary, + vendorId: techVendorContacts.vendorId, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode + }) + .from(techVendorContacts) + .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id)) + .where(inArray(techVendorContacts.vendorId, vendorIds)) + + // 벤더별로 그룹화 + const contactsByVendor = contactsWithVendor.reduce((acc, row) => { + const vendorId = row.vendorId + if (!acc[vendorId]) { + acc[vendorId] = { + vendor: { + id: vendorId, + vendorName: row.vendorName || '', + vendorCode: row.vendorCode || '' + }, + contacts: [] + } + } + acc[vendorId].contacts.push({ + id: row.contactId, + contactName: row.contactName, + contactPosition: row.contactPosition, + contactTitle: row.contactTitle, + contactEmail: row.contactEmail, + contactPhone: row.contactPhone, + isPrimary: row.isPrimary + }) + return acc + }, {} as Record<number, { + vendor: { + id: number + vendorName: string + vendorCode: string | null + } + contacts: Array<{ + id: number + contactName: string + contactPosition: string | null + contactTitle: string | null + contactEmail: string + contactPhone: string | null + isPrimary: boolean + }> + }>) + + return { data: contactsByVendor, error: null } + } catch (err) { + console.error("벤더 담당자 조회 오류:", err) + return { data: {}, error: "벤더 담당자 조회에 실패했습니다." } + } +} + +/** + * 담당자-아이템 매핑 추가 + * 선택된 아이템과 벤더를 기준으로 possible-items 테이블에 정보가 있으면 해당 id 사용, + * 없으면 possible-items 테이블에 insert 후, insert한 id를 가지고 담당자와 연결 + */ +interface AddContactItemMappingInput { + items: { + id: number + type: 'SHIP' | 'TOP' | 'HULL' + }[] + vendors: number[] + contactsByVendor: Record<number, number[]> // vendorId -> contactIds[] +} + +export async function addContactItemMapping(input: AddContactItemMappingInput) { + try { + return await db.transaction(async (tx) => { + const insertedMappings: Array<typeof techSalesContactPossibleItems.$inferSelect> = [] + + // 각 아이템 x 벤더 조합에 대해 처리 + for (const item of input.items) { + for (const vendorId of input.vendors) { + const contactIds = input.contactsByVendor[vendorId] || [] + if (contactIds.length === 0) continue + + // techVendorPossibleItems에서 기존 항목 찾기 + let vendorPossibleItemId: number | null = null + + const whereCondition = and( + eq(techVendorPossibleItems.vendorId, vendorId), + item.type === 'SHIP' + ? eq(techVendorPossibleItems.shipbuildingItemId, item.id) + : item.type === 'TOP' + ? eq(techVendorPossibleItems.offshoreTopItemId, item.id) + : eq(techVendorPossibleItems.offshoreHullItemId, item.id) + ) + + const existingPossibleItem = await tx + .select({ id: techVendorPossibleItems.id }) + .from(techVendorPossibleItems) + .where(whereCondition) + .limit(1) + + if (existingPossibleItem.length > 0) { + vendorPossibleItemId = existingPossibleItem[0].id + } else { + // 없으면 새로 생성 + const newPossibleItem = await tx + .insert(techVendorPossibleItems) + .values({ + vendorId, + shipbuildingItemId: item.type === 'SHIP' ? item.id : null, + offshoreTopItemId: item.type === 'TOP' ? item.id : null, + offshoreHullItemId: item.type === 'HULL' ? item.id : null, + }) + .returning({ id: techVendorPossibleItems.id }) + + vendorPossibleItemId = newPossibleItem[0].id + } + + // 각 담당자에 대해 techSalesContactPossibleItems에 추가 + for (const contactId of contactIds) { + // 중복 체크 + const existing = await tx + .select({ id: techSalesContactPossibleItems.id }) + .from(techSalesContactPossibleItems) + .where( + and( + eq(techSalesContactPossibleItems.contactId, contactId), + eq(techSalesContactPossibleItems.vendorPossibleItemId, vendorPossibleItemId) + ) + ) + .limit(1) + + if (existing.length === 0) { + const inserted = await tx + .insert(techSalesContactPossibleItems) + .values({ + contactId, + vendorPossibleItemId, + }) + .returning() + + if (inserted.length > 0) { + insertedMappings.push(inserted[0]) + } + } + } + } + } + + revalidatePath("/evcp/contact-possible-items") + return { data: insertedMappings, error: null } + }) + } catch (err) { + console.error("담당자-아이템 매핑 추가 오류:", err) + return { data: null, error: "담당자-아이템 매핑 추가에 실패했습니다." } + } +} + |