diff options
Diffstat (limited to 'lib/tech-vendors/repository.ts')
| -rw-r--r-- | lib/tech-vendors/repository.ts | 164 |
1 files changed, 53 insertions, 111 deletions
diff --git a/lib/tech-vendors/repository.ts b/lib/tech-vendors/repository.ts index 6f9aafbf..35301eb7 100644 --- a/lib/tech-vendors/repository.ts +++ b/lib/tech-vendors/repository.ts @@ -1,9 +1,9 @@ // src/lib/vendors/repository.ts
-import { eq, inArray, count, desc } from "drizzle-orm";
+import { eq, inArray, count, desc, not, isNull, and } 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 { techVendors, techVendorContacts, techVendorPossibleItems, type TechVendor, type TechVendorContact, type TechVendorWithAttachments, techVendorAttachments } from "@/db/schema/techVendors";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
export type NewTechVendorContact = typeof techVendorContacts.$inferInsert
@@ -279,15 +279,23 @@ export async function insertTechVendorContact( .returning();
}
-// 아이템 목록 조회
-export async function selectTechVendorItems(
+// 아이템 목록 조회 (새 스키마용)
+export async function selectTechVendorPossibleItems(
tx: any,
params: {
where?: SQL<unknown>;
orderBy?: SQL<unknown>[];
} & PaginationParams
) {
- const query = tx.select().from(techVendorItemsView);
+ const query = tx.select({
+ id: techVendorPossibleItems.id,
+ vendorId: techVendorPossibleItems.vendorId,
+ shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
+ offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
+ offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
+ createdAt: techVendorPossibleItems.createdAt,
+ updatedAt: techVendorPossibleItems.updatedAt,
+ }).from(techVendorPossibleItems);
if (params.where) {
query.where(params.where);
@@ -296,7 +304,7 @@ export async function selectTechVendorItems( if (params.orderBy && params.orderBy.length > 0) {
query.orderBy(...params.orderBy);
} else {
- query.orderBy(desc(techVendorItemsView.createdAt));
+ query.orderBy(desc(techVendorPossibleItems.createdAt));
}
query.offset(params.offset).limit(params.limit);
@@ -304,9 +312,9 @@ export async function selectTechVendorItems( return query;
}
-// 아이템 수 카운트
-export async function countTechVendorItems(tx: any, where?: SQL<unknown>) {
- const query = tx.select({ count: count() }).from(techVendorItemsView);
+// 아이템 수 카운트 (새 스키마용)
+export async function countTechVendorPossibleItems(tx: any, where?: SQL<unknown>) {
+ const query = tx.select({ count: count() }).from(techVendorPossibleItems);
if (where) {
query.where(where);
@@ -319,7 +327,7 @@ export async function countTechVendorItems(tx: any, where?: SQL<unknown>) { // 아이템 생성
export async function insertTechVendorItem(
tx: any,
- data: Omit<TechVendorItem, "id" | "createdAt" | "updatedAt">
+ data: Omit<NewTechVendorItem, "id" | "createdAt" | "updatedAt">
) {
return tx
.insert(techVendorPossibleItems)
@@ -338,118 +346,52 @@ export async function getVendorWorkTypes( vendorType: string
): Promise<string[]> {
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));
- 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));
- }
+ // 조선 아이템들의 workType 조회
+ const shipWorkTypes = await tx
+ .select({ workType: itemShipbuilding.workType })
+ .from(techVendorPossibleItems)
+ .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
+ .where(and(
+ eq(techVendorPossibleItems.vendorId, vendorId),
+ not(isNull(techVendorPossibleItems.shipbuildingItemId))
+ ));
+ 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
+ // 해양 TOP 아이템들의 workType 조회
+ const topWorkTypes = await tx
+ .select({ workType: itemOffshoreTop.workType })
+ .from(techVendorPossibleItems)
+ .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+ .where(and(
+ eq(techVendorPossibleItems.vendorId, vendorId),
+ not(isNull(techVendorPossibleItems.offshoreTopItemId))
+ ));
+ workTypes.push(
+ ...topWorkTypes
+ .map((item: { workType: string | null }) => item.workType)
+ .filter(Boolean) as string[]
);
- 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));
- }
- }
+ if (vendorType.includes('해양HULL')) {
+ // 해양 HULL 아이템들의 workType 조회
+ const hullWorkTypes = await tx
+ .select({ workType: itemOffshoreHull.workType })
+ .from(techVendorPossibleItems)
+ .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
+ .where(and(
+ eq(techVendorPossibleItems.vendorId, vendorId),
+ not(isNull(techVendorPossibleItems.offshoreHullItemId))
+ ));
+ workTypes.push(...hullWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean));
}
+
// 중복 제거 후 반환
const uniqueWorkTypes = [...new Set(workTypes)];
|