1 files changed, 266 insertions, 32 deletions
diff --git a/lib/tech-vendor-possible-items/service.ts b/lib/tech-vendor-possible-items/service.ts
index efe9be51..c630e33a 100644
--- a/lib/tech-vendor-possible-items/service.ts
+++ b/lib/tech-vendor-possible-items/service.ts
@@ -1,5 +1,5 @@
 "use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
-import { eq, and, inArray, desc, asc, or, ilike } from "drizzle-orm";
+import { eq, and, inArray, desc, asc, or, ilike, isNull } from "drizzle-orm";
 import db from "@/db/db";
 import { 
   techVendors, 
@@ -9,9 +9,9 @@ import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema
 import { unstable_cache } from "@/lib/unstable-cache";
 import { filterColumns } from "@/lib/filter-columns";
 import type { GetTechVendorPossibleItemsSchema } from "./validations";
-import { 
-  selectTechVendorPossibleItemsWithJoin, 
-  countTechVendorPossibleItemsWithJoin 
+import {
+  selectTechVendorPossibleItemsWithJoin,
+  countTechVendorPossibleItemsWithJoin,
 } from "./repository";
 
 export interface TechVendorPossibleItemsData {
@@ -19,21 +19,34 @@ export interface TechVendorPossibleItemsData {
   vendorId: number;
   vendorCode: string | null;
   vendorName: string;
+  vendorEmail: string | null;
   techVendorType: string;
   itemCode: string;
+  workType: string | null;
+  shipTypes: string | null;
+  itemList: string | null;
+  subItemList: string | null;
   createdAt: Date;
   updatedAt: Date;
 }
 
 export interface CreateTechVendorPossibleItemData {
-  vendorId: number;
-  itemCode: string;
+  vendorId: number; // 필수: 벤더 ID (Add Dialog에서 벤더 선택 시 사용)
+  itemCode: string; // 필수: 아이템 코드
+  workType?: string | null; // 공종 (아이템에서 가져온 정보)
+  shipTypes?: string | null; // 선종 (아이템에서 가져온 정보)
+  itemList?: string | null; // 아이템리스트 (아이템에서 가져온 정보)
+  subItemList?: string | null; // 서브아이템리스트 (아이템에서 가져온 정보)
 }
 
 export interface ImportTechVendorPossibleItemData {
-  vendorCode: string;
-  vendorEmail?: string;
-  itemCode: string;
+  vendorCode?: string;
+  vendorEmail: string; // 필수: 벤더 이메일
+  itemCode: string; // 필수: 아이템 코드
+  workType?: string;
+  shipTypes?: string;
+  itemList?: string;
+  subItemList?: string;
 }
 
 export interface ImportResult {
@@ -45,7 +58,11 @@ export interface ImportResult {
     error: string; 
     vendorCode?: string; 
     vendorEmail?: string;
-    itemCode?: string; 
+    itemCode?: string;
+    workType?: string;
+    shipTypes?: string;
+    itemList?: string;
+    subItemList?: string;
   }[];
 }
 
@@ -74,14 +91,19 @@ export async function getTechVendorPossibleItems(input: GetTechVendorPossibleIte
           globalWhere = or(
             ilike(techVendors.vendorCode, s),
             ilike(techVendors.vendorName, s),
+            ilike(techVendorPossibleItems.vendorEmail, s),
             ilike(techVendorPossibleItems.itemCode, s),
+            ilike(techVendorPossibleItems.workType, s),
+            ilike(techVendorPossibleItems.shipTypes, s),
+            ilike(techVendorPossibleItems.itemList, s),
+            ilike(techVendorPossibleItems.subItemList, s),
           );
         }
 
         // 기존 호환성을 위한 개별 필터들
         const legacyFilters = [];
         if (input.vendorCode) {
-          legacyFilters.push(ilike(techVendors.vendorCode, `%${input.vendorCode}%`));
+          legacyFilters.push(ilike(techVendorPossibleItems.vendorCode, `%${input.vendorCode}%`));
         }
         if (input.vendorName) {
           legacyFilters.push(ilike(techVendors.vendorName, `%${input.vendorName}%`));
@@ -225,13 +247,13 @@ export async function getTechVendorPossibleItems(input: GetTechVendorPossibleIte
 // }
 
 /**
- * tech vendor possible item 생성 (간단 버전)
+ * tech vendor possible item 생성 (Add Dialog용 - vendorId 기반)
  */
 export async function createTechVendorPossibleItem(
   data: CreateTechVendorPossibleItemData
 ): Promise<{ success: boolean; error?: string }> {
   try {
-    // 벤더 존재 여부만 확인
+    // 벤더 ID로 벤더 조회
     const vendor = await db
       .select()
       .from(techVendors)
@@ -242,14 +264,20 @@ export async function createTechVendorPossibleItem(
       return { success: false, error: "벤더를 찾을 수 없습니다." };
     }
 
-    // 중복 체크
+    // 중복 체크 (벤더 + 아이템코드 + 공종 + 선종 조합)
     const existing = await db
       .select()
       .from(techVendorPossibleItems)
       .where(
         and(
           eq(techVendorPossibleItems.vendorId, data.vendorId),
-          eq(techVendorPossibleItems.itemCode, data.itemCode)
+          eq(techVendorPossibleItems.itemCode, data.itemCode),
+          data.workType 
+            ? eq(techVendorPossibleItems.workType, data.workType)
+            : isNull(techVendorPossibleItems.workType),
+          data.shipTypes 
+            ? eq(techVendorPossibleItems.shipTypes, data.shipTypes)
+            : isNull(techVendorPossibleItems.shipTypes)
         )
       )
       .limit(1);
@@ -258,10 +286,16 @@ export async function createTechVendorPossibleItem(
       return { success: false, error: "이미 존재하는 벤더-아이템 조합입니다." };
     }
 
-    // 아이템 코드 검증 없이 바로 삽입
+    // 새로운 아이템 생성 (선택한 아이템의 정보를 그대로 저장)
     await db.insert(techVendorPossibleItems).values({
-      vendorId: data.vendorId,
+      vendorId: vendor[0].id,
+      vendorCode: vendor[0].vendorCode,
+      vendorEmail: vendor[0].email,
       itemCode: data.itemCode,
+      workType: data.workType,
+      shipTypes: data.shipTypes,
+      itemList: data.itemList,
+      subItemList: data.subItemList,
     });
 
     return { success: true };
@@ -419,7 +453,7 @@ export async function getItemByCode(itemCode: string) {
 }
 
 /**
- * Import 기능: 벤더코드와 아이템코드를 통한 batch insert (간단 버전)
+ * Import 기능: 벤더이메일과 아이템정보를 통한 batch insert (새로운 스키마 버전)
  */
 export async function importTechVendorPossibleItems(
   data: ImportTechVendorPossibleItemData[]
@@ -436,39 +470,55 @@ export async function importTechVendorPossibleItems(
     const rowNumber = i + 1;
 
     try {
-      // 벤더 코드 또는 이메일로 벤더 찾기
+      // 벤더 이메일로 벤더 찾기 (필수)
       let vendor = null;
       
-      if (row.vendorCode && row.vendorCode.trim()) {
-        // 벤더 코드가 있으면 먼저 벤더 코드로 검색
-        vendor = await getTechVendorByCode(row.vendorCode);
-      } else if (row.vendorEmail && row.vendorEmail.trim()) {
-        // 벤더 코드가 없으면 이메일로 검색
+      if (row.vendorEmail && row.vendorEmail.trim()) {
         vendor = await getTechVendorByEmail(row.vendorEmail);
+      } else {
+        result.failedRows.push({
+          row: rowNumber,
+          error: "벤더 이메일은 필수입니다.",
+          vendorCode: row.vendorCode,
+          vendorEmail: row.vendorEmail,
+          itemCode: row.itemCode,
+          workType: row.workType,
+          shipTypes: row.shipTypes,
+          itemList: row.itemList,
+          subItemList: row.subItemList,
+        });
+        continue;
       }
       
       if (!vendor) {
-        const identifier = row.vendorCode ? `벤더 코드 '${row.vendorCode}'` : 
-                          row.vendorEmail ? `벤더 이메일 '${row.vendorEmail}'` : 
-                          '벤더 코드 또는 이메일';
         result.failedRows.push({
           row: rowNumber,
-          error: `${identifier}을(를) 찾을 수 없습니다.`,
+          error: `벤더 이메일 '${row.vendorEmail}'을(를) 찾을 수 없습니다.`,
           vendorCode: row.vendorCode,
           vendorEmail: row.vendorEmail,
           itemCode: row.itemCode,
+          workType: row.workType,
+          shipTypes: row.shipTypes,
+          itemList: row.itemList,
+          subItemList: row.subItemList,
         });
         continue;
       }
 
-      // 중복 체크
+      // 중복 체크 (벤더 + 아이템코드 + 공종 + 선종 조합)
       const existing = await db
         .select()
         .from(techVendorPossibleItems)
         .where(
           and(
             eq(techVendorPossibleItems.vendorId, vendor.id),
-            eq(techVendorPossibleItems.itemCode, row.itemCode)
+            eq(techVendorPossibleItems.itemCode, row.itemCode),
+            row.workType 
+              ? eq(techVendorPossibleItems.workType, row.workType)
+              : isNull(techVendorPossibleItems.workType),
+            row.shipTypes 
+              ? eq(techVendorPossibleItems.shipTypes, row.shipTypes)
+              : isNull(techVendorPossibleItems.shipTypes)
           )
         )
         .limit(1);
@@ -480,14 +530,24 @@ export async function importTechVendorPossibleItems(
           vendorCode: row.vendorCode,
           vendorEmail: row.vendorEmail,
           itemCode: row.itemCode,
+          workType: row.workType,
+          shipTypes: row.shipTypes,
+          itemList: row.itemList,
+          subItemList: row.subItemList,
         });
         continue;
       }
 
-      // 아이템 코드 검증 없이 바로 삽입
+      // 새로운 아이템 생성
       await db.insert(techVendorPossibleItems).values({
         vendorId: vendor.id,
+        vendorCode: vendor.vendorCode,
+        vendorEmail: vendor.email,
         itemCode: row.itemCode,
+        workType: row.workType || null,
+        shipTypes: row.shipTypes || null,
+        itemList: row.itemList || null,
+        subItemList: row.subItemList || null,
       });
 
       result.successCount++;
@@ -498,6 +558,10 @@ export async function importTechVendorPossibleItems(
         vendorCode: row.vendorCode,
         vendorEmail: row.vendorEmail,
         itemCode: row.itemCode,
+        workType: row.workType,
+        shipTypes: row.shipTypes,
+        itemList: row.itemList,
+        subItemList: row.subItemList,
       });
     }
   }
@@ -580,4 +644,174 @@ export async function getUniqueTechVendorTypes(): Promise<string[]> {
     // 오류 발생시 기본 벤더 타입 반환
     return ["조선", "해양TOP", "해양HULL"];
   }
-} 
-\ No newline at end of file
+}
+
+/**
+ * 벤더 타입에 따른 아이템 목록 조회
+ */
+export async function getItemsByVendorType(vendorTypes: string): Promise<{
+  itemCode: string;
+  itemList: string | null;
+  workType: string | null;
+  shipTypes?: string | null;
+  subItemList?: string | null;
+}[]> {
+  try {
+    // 벤더 타입 파싱 개선
+    let types: string[] = [];
+    if (!vendorTypes) {
+      return [];
+    }
+    
+    if (vendorTypes.startsWith('[') && vendorTypes.endsWith(']')) {
+      // JSON 배열 형태
+      try {
+        const parsed = JSON.parse(vendorTypes);
+        types = Array.isArray(parsed) ? parsed.filter(Boolean) : [vendorTypes];
+      } catch {
+        types = [vendorTypes];
+      }
+    } else if (vendorTypes.includes(',')) {
+      // 콤마로 구분된 문자열
+      types = vendorTypes.split(',').map(t => t.trim()).filter(Boolean);
+    } else {
+      // 단일 문자열
+      types = [vendorTypes.trim()].filter(Boolean);
+    }
+    // 벤더 타입 정렬 - 조선 > 해양TOP > 해양HULL 순
+    const typeOrder = ["조선", "해양TOP", "해양HULL"];
+    types.sort((a, b) => {
+      const indexA = typeOrder.indexOf(a);
+      const indexB = typeOrder.indexOf(b);
+      
+      // 정의된 순서에 있는 경우 우선순위 적용
+      if (indexA !== -1 && indexB !== -1) {
+        return indexA - indexB;
+      }
+      // 정의된 순서에 없는 경우 마지막에 배치하고 알파벳 순으로 정렬
+      if (indexA !== -1) return -1;
+      if (indexB !== -1) return 1;
+      return a.localeCompare(b);
+    });
+    
+    const allItems: any[] = [];
+
+    // 각 벤더 타입에 따라 해당 아이템 테이블에서 조회
+    for (const type of types) {
+      switch (type) {
+        case "조선":
+          const shipItems = await db
+            .select({
+              itemCode: itemShipbuilding.itemCode,
+              itemList: itemShipbuilding.itemList,
+              workType: itemShipbuilding.workType,
+              shipTypes: itemShipbuilding.shipTypes,
+            })
+            .from(itemShipbuilding);
+          allItems.push(...shipItems);
+          break;
+
+        case "해양TOP":
+          const topItems = await db
+            .select({
+              itemCode: itemOffshoreTop.itemCode,
+              itemList: itemOffshoreTop.itemList,
+              workType: itemOffshoreTop.workType,
+              subItemList: itemOffshoreTop.subItemList,
+            })
+            .from(itemOffshoreTop);
+          allItems.push(...topItems);
+          break;
+
+        case "해양HULL":
+          const hullItems = await db
+            .select({
+              itemCode: itemOffshoreHull.itemCode,
+              itemList: itemOffshoreHull.itemList,
+              workType: itemOffshoreHull.workType,
+              subItemList: itemOffshoreHull.subItemList,
+            })
+            .from(itemOffshoreHull);
+          allItems.push(...hullItems);
+          break;
+      }
+    }
+    // // 중복 제거 (itemCode 기준)
+    // const uniqueItems = allItems.filter((item, index, self) => 
+    //   index === self.findIndex(i => i.itemCode === item.itemCode)
+    // );
+
+    // const finalItems = uniqueItems.filter(item => item.itemCode); // itemCode가 있는 것만 반환
+    // console.log("Final items after deduplication and filtering:", finalItems.length);
+    
+    return allItems;
+  } catch (error) {
+    console.error("Error fetching items by vendor type:", error);
+    return [];
+  }
+} 
+
+/**
+ * Excel Export 기능: 기술영업 벤더 가능 아이템 목록 내보내기
+ */
+export async function exportTechVendorPossibleItemsToExcel(): Promise<{
+  success: boolean;
+  data?: Array<{
+    벤더코드: string | null;
+    벤더명: string;
+    벤더이메일: string | null;
+    벤더타입: string;
+    아이템코드: string;
+    공종: string | null;
+    선종: string | null;
+    아이템리스트: string | null;
+    서브아이템리스트: string | null;
+    생성일: string;
+  }>;
+  error?: string;
+}> {
+  try {
+    // 모든 데이터 조회 (페이지네이션 없이)
+    const allData = await db
+      .select({
+        vendorCode: techVendorPossibleItems.vendorCode,
+        vendorName: techVendors.vendorName,
+        vendorEmail: techVendorPossibleItems.vendorEmail,
+        techVendorType: techVendors.techVendorType,
+        itemCode: techVendorPossibleItems.itemCode,
+        workType: techVendorPossibleItems.workType,
+        shipTypes: techVendorPossibleItems.shipTypes,
+        itemList: techVendorPossibleItems.itemList,
+        subItemList: techVendorPossibleItems.subItemList,
+        createdAt: techVendorPossibleItems.createdAt,
+      })
+      .from(techVendorPossibleItems)
+      .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+      .orderBy(desc(techVendorPossibleItems.createdAt));
+
+    // Excel 형태로 변환
+    const excelData = allData.map(item => ({
+      벤더코드: item.vendorCode,
+      벤더명: item.vendorName,
+      벤더이메일: item.vendorEmail,
+      벤더타입: item.techVendorType,
+      아이템코드: item.itemCode,
+      공종: item.workType,
+      선종: item.shipTypes,
+      아이템리스트: item.itemList,
+      서브아이템리스트: item.subItemList,
+      생성일: item.createdAt.toISOString().split('T')[0], // YYYY-MM-DD 형식
+    }));
+
+    return {
+      success: true,
+      data: excelData,
+    };
+  } catch (error) {
+    console.error("Error exporting tech vendor possible items:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "내보내기 중 오류가 발생했습니다.",
+    };
+  }
+}