From 14f61e24947fb92dd71ec0a7196a6e815f8e66da Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 21 Jul 2025 07:54:26 +0000 Subject: (최겸)기술영업 RFQ 담당자 초대, 요구사항 반영 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/tech-vendors/repository.ts | 851 ++++++++++++++++++++++------------------- 1 file changed, 462 insertions(+), 389 deletions(-) (limited to 'lib/tech-vendors/repository.ts') diff --git a/lib/tech-vendors/repository.ts b/lib/tech-vendors/repository.ts index d3c6671c..a273bf50 100644 --- a/lib/tech-vendors/repository.ts +++ b/lib/tech-vendors/repository.ts @@ -1,389 +1,462 @@ -// src/lib/vendors/repository.ts - -import { eq, inArray, count, desc } from "drizzle-orm"; -import db from '@/db/db'; -import { SQL } from "drizzle-orm"; -import { techVendors, techVendorContacts, techVendorPossibleItems, techVendorItemsView, type TechVendor, type TechVendorContact, type TechVendorItem, type TechVendorWithAttachments, techVendorAttachments } from "@/db/schema/techVendors"; -import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"; - -export type NewTechVendorContact = typeof techVendorContacts.$inferInsert -export type NewTechVendorItem = typeof techVendorPossibleItems.$inferInsert - -type PaginationParams = { - offset: number; - limit: number; -}; - -// 메인 벤더 목록 조회 (첨부파일 정보 포함) -export async function selectTechVendorsWithAttachments( - tx: any, - params: { - where?: SQL; - orderBy?: SQL[]; - } & PaginationParams -) { - const query = tx - .select({ - id: techVendors.id, - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - taxId: techVendors.taxId, - address: techVendors.address, - country: techVendors.country, - phone: techVendors.phone, - email: techVendors.email, - website: techVendors.website, - status: techVendors.status, - techVendorType: techVendors.techVendorType, - representativeName: techVendors.representativeName, - representativeEmail: techVendors.representativeEmail, - representativePhone: techVendors.representativePhone, - representativeBirth: techVendors.representativeBirth, - countryEng: techVendors.countryEng, - countryFab: techVendors.countryFab, - agentName: techVendors.agentName, - agentPhone: techVendors.agentPhone, - agentEmail: techVendors.agentEmail, - items: techVendors.items, - createdAt: techVendors.createdAt, - updatedAt: techVendors.updatedAt, - }) - .from(techVendors); - - // where 조건이 있는 경우 - if (params.where) { - query.where(params.where); - } - - // 정렬 조건이 있는 경우 - if (params.orderBy && params.orderBy.length > 0) { - query.orderBy(...params.orderBy); - } else { - // 기본 정렬: 생성일 기준 내림차순 - query.orderBy(desc(techVendors.createdAt)); - } - - // 페이지네이션 적용 - query.offset(params.offset).limit(params.limit); - - const vendors = await query; - - // 첨부파일 정보 가져오기 - const vendorsWithAttachments = await Promise.all( - vendors.map(async (vendor: TechVendor) => { - const attachments = await tx - .select({ - id: techVendorAttachments.id, - fileName: techVendorAttachments.fileName, - filePath: techVendorAttachments.filePath, - }) - .from(techVendorAttachments) - .where(eq(techVendorAttachments.vendorId, vendor.id)); - - // 벤더의 worktype 조회 - const workTypes = await getVendorWorkTypes(tx, vendor.id, vendor.techVendorType); - - return { - ...vendor, - hasAttachments: attachments.length > 0, - attachmentsList: attachments, - workTypes: workTypes.join(', '), // 콤마로 구분해서 저장 - } as TechVendorWithAttachments; - }) - ); - - return vendorsWithAttachments; -} - -// 메인 벤더 목록 수 조회 (첨부파일 정보 포함) -export async function countTechVendorsWithAttachments( - tx: any, - where?: SQL -) { - const query = tx.select({ count: count() }).from(techVendors); - - if (where) { - query.where(where); - } - - const result = await query; - return result[0].count; -} - -// 기술영업 벤더 조회 -export async function selectTechVendors( - tx: any, - params: { - where?: SQL; - orderBy?: SQL[]; - } & PaginationParams -) { - const query = tx.select().from(techVendors); - - if (params.where) { - query.where(params.where); - } - - if (params.orderBy && params.orderBy.length > 0) { - query.orderBy(...params.orderBy); - } else { - query.orderBy(desc(techVendors.createdAt)); - } - - query.offset(params.offset).limit(params.limit); - - return query; -} - -// 기술영업 벤더 수 카운트 -export async function countTechVendors(tx: any, where?: SQL) { - const query = tx.select({ count: count() }).from(techVendors); - - if (where) { - query.where(where); - } - - const result = await query; - return result[0].count; -} - -// 벤더 상태별 카운트 -export async function groupByTechVendorStatus(tx: any) { - const result = await tx - .select({ - status: techVendors.status, - count: count(), - }) - .from(techVendors) - .groupBy(techVendors.status); - - return result; -} - -// 벤더 상세 정보 조회 -export async function getTechVendorById(id: number) { - const result = await db - .select() - .from(techVendors) - .where(eq(techVendors.id, id)); - - return result.length > 0 ? result[0] : null; -} - -// 벤더 연락처 정보 조회 -export async function getTechVendorContactsById(id: number) { - const result = await db - .select() - .from(techVendorContacts) - .where(eq(techVendorContacts.id, id)); - - return result.length > 0 ? result[0] : null; -} - -// 신규 벤더 생성 -export async function insertTechVendor( - tx: any, - data: Omit -) { - return tx - .insert(techVendors) - .values({ - ...data, - createdAt: new Date(), - updatedAt: new Date(), - }) - .returning(); -} - -// 벤더 정보 업데이트 (단일) -export async function updateTechVendor( - tx: any, - id: string | number, - data: Partial -) { - return tx - .update(techVendors) - .set({ - ...data, - updatedAt: new Date(), - }) - .where(eq(techVendors.id, Number(id))) - .returning(); -} - -// 벤더 정보 업데이트 (다수) -export async function updateTechVendors( - tx: any, - ids: (string | number)[], - data: Partial -) { - return tx - .update(techVendors) - .set({ - ...data, - updatedAt: new Date(), - }) - .where(inArray(techVendors.id, ids.map(id => Number(id)))) - .returning(); -} - -// 벤더 연락처 조회 -export async function selectTechVendorContacts( - tx: any, - params: { - where?: SQL; - orderBy?: SQL[]; - } & PaginationParams -) { - const query = tx.select().from(techVendorContacts); - - if (params.where) { - query.where(params.where); - } - - if (params.orderBy && params.orderBy.length > 0) { - query.orderBy(...params.orderBy); - } else { - query.orderBy(desc(techVendorContacts.createdAt)); - } - - query.offset(params.offset).limit(params.limit); - - return query; -} - -// 벤더 연락처 수 카운트 -export async function countTechVendorContacts(tx: any, where?: SQL) { - const query = tx.select({ count: count() }).from(techVendorContacts); - - if (where) { - query.where(where); - } - - const result = await query; - return result[0].count; -} - -// 연락처 생성 -export async function insertTechVendorContact( - tx: any, - data: Omit -) { - return tx - .insert(techVendorContacts) - .values({ - ...data, - createdAt: new Date(), - updatedAt: new Date(), - }) - .returning(); -} - -// 아이템 목록 조회 -export async function selectTechVendorItems( - tx: any, - params: { - where?: SQL; - orderBy?: SQL[]; - } & PaginationParams -) { - const query = tx.select().from(techVendorItemsView); - - if (params.where) { - query.where(params.where); - } - - if (params.orderBy && params.orderBy.length > 0) { - query.orderBy(...params.orderBy); - } else { - query.orderBy(desc(techVendorItemsView.createdAt)); - } - - query.offset(params.offset).limit(params.limit); - - return query; -} - -// 아이템 수 카운트 -export async function countTechVendorItems(tx: any, where?: SQL) { - const query = tx.select({ count: count() }).from(techVendorItemsView); - - if (where) { - query.where(where); - } - - const result = await query; - return result[0].count; -} - -// 아이템 생성 -export async function insertTechVendorItem( - tx: any, - data: Omit -) { - return tx - .insert(techVendorPossibleItems) - .values({ - ...data, - createdAt: new Date(), - updatedAt: new Date(), - }) - .returning(); -} - -// 벤더의 worktype 조회 -export async function getVendorWorkTypes( - tx: any, - vendorId: number, - vendorType: string -): Promise { - try { - // 벤더의 possible items 조회 - const possibleItems = await tx - .select({ itemCode: techVendorPossibleItems.itemCode }) - .from(techVendorPossibleItems) - .where(eq(techVendorPossibleItems.vendorId, vendorId)); - - if (!possibleItems.length) { - return []; - } - - const itemCodes = possibleItems.map((item: { itemCode: string }) => item.itemCode); - const workTypes: string[] = []; - - // 벤더 타입에 따라 해당하는 아이템 테이블에서 worktype 조회 - if (vendorType.includes('조선')) { - const shipWorkTypes = await tx - .select({ workType: itemShipbuilding.workType }) - .from(itemShipbuilding) - .where(inArray(itemShipbuilding.itemCode, itemCodes)); - - workTypes.push(...shipWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean)); - } - - if (vendorType.includes('해양TOP')) { - const topWorkTypes = await tx - .select({ workType: itemOffshoreTop.workType }) - .from(itemOffshoreTop) - .where(inArray(itemOffshoreTop.itemCode, itemCodes)); - - workTypes.push(...topWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean)); - } - - if (vendorType.includes('해양HULL')) { - const hullWorkTypes = await tx - .select({ workType: itemOffshoreHull.workType }) - .from(itemOffshoreHull) - .where(inArray(itemOffshoreHull.itemCode, itemCodes)); - - workTypes.push(...hullWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean)); - } - - // 중복 제거 후 반환 - const uniqueWorkTypes = [...new Set(workTypes)]; - - return uniqueWorkTypes; - } catch (error) { - return []; - } -} +// src/lib/vendors/repository.ts + +import { eq, inArray, count, desc } from "drizzle-orm"; +import db from '@/db/db'; +import { SQL } from "drizzle-orm"; +import { techVendors, techVendorContacts, techVendorPossibleItems, techVendorItemsView, type TechVendor, type TechVendorContact, type TechVendorItem, type TechVendorWithAttachments, techVendorAttachments } from "@/db/schema/techVendors"; +import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"; + +export type NewTechVendorContact = typeof techVendorContacts.$inferInsert +export type NewTechVendorItem = typeof techVendorPossibleItems.$inferInsert + +type PaginationParams = { + offset: number; + limit: number; +}; + +// 메인 벤더 목록 조회 (첨부파일 정보 포함) +export async function selectTechVendorsWithAttachments( + tx: any, + params: { + where?: SQL; + orderBy?: SQL[]; + } & PaginationParams +) { + const query = tx + .select({ + id: techVendors.id, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + taxId: techVendors.taxId, + address: techVendors.address, + country: techVendors.country, + phone: techVendors.phone, + email: techVendors.email, + website: techVendors.website, + status: techVendors.status, + techVendorType: techVendors.techVendorType, + representativeName: techVendors.representativeName, + representativeEmail: techVendors.representativeEmail, + representativePhone: techVendors.representativePhone, + representativeBirth: techVendors.representativeBirth, + countryEng: techVendors.countryEng, + countryFab: techVendors.countryFab, + agentName: techVendors.agentName, + agentPhone: techVendors.agentPhone, + agentEmail: techVendors.agentEmail, + items: techVendors.items, + createdAt: techVendors.createdAt, + updatedAt: techVendors.updatedAt, + }) + .from(techVendors); + + // where 조건이 있는 경우 + if (params.where) { + query.where(params.where); + } + + // 정렬 조건이 있는 경우 + if (params.orderBy && params.orderBy.length > 0) { + query.orderBy(...params.orderBy); + } else { + // 기본 정렬: 생성일 기준 내림차순 + query.orderBy(desc(techVendors.createdAt)); + } + + // 페이지네이션 적용 + query.offset(params.offset).limit(params.limit); + + const vendors = await query; + + // 첨부파일 정보 가져오기 + const vendorsWithAttachments = await Promise.all( + vendors.map(async (vendor: TechVendor) => { + const attachments = await tx + .select({ + id: techVendorAttachments.id, + fileName: techVendorAttachments.fileName, + filePath: techVendorAttachments.filePath, + }) + .from(techVendorAttachments) + .where(eq(techVendorAttachments.vendorId, vendor.id)); + + // 벤더의 worktype 조회 + const workTypes = await getVendorWorkTypes(tx, vendor.id, vendor.techVendorType); + + return { + ...vendor, + hasAttachments: attachments.length > 0, + attachmentsList: attachments, + workTypes: workTypes.join(', '), // 콤마로 구분해서 저장 + } as TechVendorWithAttachments; + }) + ); + + return vendorsWithAttachments; +} + +// 메인 벤더 목록 수 조회 (첨부파일 정보 포함) +export async function countTechVendorsWithAttachments( + tx: any, + where?: SQL +) { + const query = tx.select({ count: count() }).from(techVendors); + + if (where) { + query.where(where); + } + + const result = await query; + return result[0].count; +} + +// 기술영업 벤더 조회 +export async function selectTechVendors( + tx: any, + params: { + where?: SQL; + orderBy?: SQL[]; + } & PaginationParams +) { + const query = tx.select().from(techVendors); + + if (params.where) { + query.where(params.where); + } + + if (params.orderBy && params.orderBy.length > 0) { + query.orderBy(...params.orderBy); + } else { + query.orderBy(desc(techVendors.createdAt)); + } + + query.offset(params.offset).limit(params.limit); + + return query; +} + +// 기술영업 벤더 수 카운트 +export async function countTechVendors(tx: any, where?: SQL) { + const query = tx.select({ count: count() }).from(techVendors); + + if (where) { + query.where(where); + } + + const result = await query; + return result[0].count; +} + +// 벤더 상태별 카운트 +export async function groupByTechVendorStatus(tx: any) { + const result = await tx + .select({ + status: techVendors.status, + count: count(), + }) + .from(techVendors) + .groupBy(techVendors.status); + + return result; +} + +// 벤더 상세 정보 조회 +export async function getTechVendorById(id: number) { + const result = await db + .select() + .from(techVendors) + .where(eq(techVendors.id, id)); + + return result.length > 0 ? result[0] : null; +} + +// 벤더 연락처 정보 조회 +export async function getTechVendorContactsById(id: number) { + const result = await db + .select() + .from(techVendorContacts) + .where(eq(techVendorContacts.id, id)); + + return result.length > 0 ? result[0] : null; +} + +// 신규 벤더 생성 +export async function insertTechVendor( + tx: any, + data: Omit +) { + return tx + .insert(techVendors) + .values({ + ...data, + createdAt: new Date(), + updatedAt: new Date(), + }) + .returning(); +} + +// 벤더 정보 업데이트 (단일) +export async function updateTechVendor( + tx: any, + id: string | number, + data: Partial +) { + return tx + .update(techVendors) + .set({ + ...data, + updatedAt: new Date(), + }) + .where(eq(techVendors.id, Number(id))) + .returning(); +} + +// 벤더 정보 업데이트 (다수) +export async function updateTechVendors( + tx: any, + ids: (string | number)[], + data: Partial +) { + return tx + .update(techVendors) + .set({ + ...data, + updatedAt: new Date(), + }) + .where(inArray(techVendors.id, ids.map(id => Number(id)))) + .returning(); +} + +// 벤더 연락처 조회 +export async function selectTechVendorContacts( + tx: any, + params: { + where?: SQL; + orderBy?: SQL[]; + } & PaginationParams +) { + const query = tx.select().from(techVendorContacts); + + if (params.where) { + query.where(params.where); + } + + if (params.orderBy && params.orderBy.length > 0) { + query.orderBy(...params.orderBy); + } else { + query.orderBy(desc(techVendorContacts.createdAt)); + } + + query.offset(params.offset).limit(params.limit); + + return query; +} + +// 벤더 연락처 수 카운트 +export async function countTechVendorContacts(tx: any, where?: SQL) { + const query = tx.select({ count: count() }).from(techVendorContacts); + + if (where) { + query.where(where); + } + + const result = await query; + return result[0].count; +} + +// 연락처 생성 +export async function insertTechVendorContact( + tx: any, + data: Omit +) { + return tx + .insert(techVendorContacts) + .values({ + ...data, + createdAt: new Date(), + updatedAt: new Date(), + }) + .returning(); +} + +// 아이템 목록 조회 +export async function selectTechVendorItems( + tx: any, + params: { + where?: SQL; + orderBy?: SQL[]; + } & PaginationParams +) { + const query = tx.select().from(techVendorItemsView); + + if (params.where) { + query.where(params.where); + } + + if (params.orderBy && params.orderBy.length > 0) { + query.orderBy(...params.orderBy); + } else { + query.orderBy(desc(techVendorItemsView.createdAt)); + } + + query.offset(params.offset).limit(params.limit); + + return query; +} + +// 아이템 수 카운트 +export async function countTechVendorItems(tx: any, where?: SQL) { + const query = tx.select({ count: count() }).from(techVendorItemsView); + + if (where) { + query.where(where); + } + + const result = await query; + return result[0].count; +} + +// 아이템 생성 +export async function insertTechVendorItem( + tx: any, + data: Omit +) { + return tx + .insert(techVendorPossibleItems) + .values({ + ...data, + createdAt: new Date(), + updatedAt: new Date(), + }) + .returning(); +} + +// 벤더의 worktype 조회 +export async function getVendorWorkTypes( + tx: any, + vendorId: number, + vendorType: string +): Promise { + try { + // 벤더의 possible items 조회 - 모든 필드 가져오기 + const possibleItems = await tx + .select({ + itemCode: techVendorPossibleItems.itemCode, + shipTypes: techVendorPossibleItems.shipTypes, + itemList: techVendorPossibleItems.itemList, + subItemList: techVendorPossibleItems.subItemList, + workType: techVendorPossibleItems.workType + }) + .from(techVendorPossibleItems) + .where(eq(techVendorPossibleItems.vendorId, vendorId)); + console.log("possibleItems", possibleItems); + if (!possibleItems.length) { + return []; + } + + const workTypes: string[] = []; + + // 벤더 타입에 따라 해당하는 아이템 테이블에서 worktype 조회 + if (vendorType.includes('조선')) { + const itemCodes = possibleItems + .map((item: { itemCode?: string | null }) => item.itemCode) + .filter(Boolean); + + if (itemCodes.length > 0) { + const shipWorkTypes = await tx + .select({ workType: itemShipbuilding.workType }) + .from(itemShipbuilding) + .where(inArray(itemShipbuilding.itemCode, itemCodes)); + + workTypes.push(...shipWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean)); + } + } + + if (vendorType.includes('해양TOP')) { + // 1. 아이템코드가 있는 경우 + const itemCodesTop = possibleItems + .map((item: { itemCode?: string | null }) => item.itemCode) + .filter(Boolean) as string[]; + + if (itemCodesTop.length > 0) { + const topWorkTypes = await tx + .select({ workType: itemOffshoreTop.workType }) + .from(itemOffshoreTop) + .where(inArray(itemOffshoreTop.itemCode, itemCodesTop)); + + workTypes.push( + ...topWorkTypes + .map((item: { workType: string | null }) => item.workType) + .filter(Boolean) as string[] + ); + } + + // 2. 아이템코드가 없는 경우 서브아이템리스트로 매칭 + const itemsWithoutCodeTop = possibleItems.filter( + (item: { itemCode?: string | null; subItemList?: string | null }) => + !item.itemCode && item.subItemList + ); + if (itemsWithoutCodeTop.length > 0) { + const subItemListsTop = itemsWithoutCodeTop + .map((item: { subItemList?: string | null }) => item.subItemList) + .filter(Boolean) as string[]; + + if (subItemListsTop.length > 0) { + const topWorkTypesBySubItem = await tx + .select({ workType: itemOffshoreTop.workType }) + .from(itemOffshoreTop) + .where(inArray(itemOffshoreTop.subItemList, subItemListsTop)); + + workTypes.push( + ...topWorkTypesBySubItem + .map((item: { workType: string | null }) => item.workType) + .filter(Boolean) as string[] + ); + } + } + } + if (vendorType.includes('해양HULL')) { + // 1. 아이템코드가 있는 경우 + const itemCodes = possibleItems + .map((item: { itemCode?: string | null }) => item.itemCode) + .filter(Boolean); + + if (itemCodes.length > 0) { + const hullWorkTypes = await tx + .select({ workType: itemOffshoreHull.workType }) + .from(itemOffshoreHull) + .where(inArray(itemOffshoreHull.itemCode, itemCodes)); + + workTypes.push(...hullWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean)); + } + + // 2. 아이템코드가 없는 경우 서브아이템리스트로 매칭 + const itemsWithoutCodeHull = possibleItems.filter( + (item: { itemCode?: string | null; subItemList?: string | null }) => + !item.itemCode && item.subItemList + ); + + if (itemsWithoutCodeHull.length > 0) { + const subItemListsHull = itemsWithoutCodeHull + .map((item: { subItemList?: string | null }) => item.subItemList) + .filter(Boolean) as string[]; + + if (subItemListsHull.length > 0) { + const hullWorkTypesBySubItem = await tx + .select({ workType: itemOffshoreHull.workType }) + .from(itemOffshoreHull) + .where(inArray(itemOffshoreHull.subItemList, subItemListsHull)); + + workTypes.push(...hullWorkTypesBySubItem.map((item: { workType: string | null }) => item.workType).filter(Boolean)); + } + } + } + // 중복 제거 후 반환 + const uniqueWorkTypes = [...new Set(workTypes)]; + + return uniqueWorkTypes; + } catch (error) { + console.error('getVendorWorkTypes 오류:', error); + return []; + } +} -- cgit v1.2.3