(최겸) 기술영업 벤더별 아이템 조회 기능 추가

1 files changed, 583 insertions, 0 deletions

diff --git a/lib/tech-vendor-possible-items/service.ts b/lib/tech-vendor-possible-items/service.ts
new file mode 100644
index 00000000..efe9be51
--- /dev/null
+++ b/lib/tech-vendor-possible-items/service.ts
@@ -0,0 +1,583 @@
+"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

+import { eq, and, inArray, desc, asc, or, ilike } from "drizzle-orm";

+import db from "@/db/db";

+import { 

+  techVendors, 

+  techVendorPossibleItems

+} from "@/db/schema/techVendors";

+import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";

+import { unstable_cache } from "@/lib/unstable-cache";

+import { filterColumns } from "@/lib/filter-columns";

+import type { GetTechVendorPossibleItemsSchema } from "./validations";

+import { 

+  selectTechVendorPossibleItemsWithJoin, 

+  countTechVendorPossibleItemsWithJoin 

+} from "./repository";

+

+export interface TechVendorPossibleItemsData {

+  id: number;

+  vendorId: number;

+  vendorCode: string | null;

+  vendorName: string;

+  techVendorType: string;

+  itemCode: string;

+  createdAt: Date;

+  updatedAt: Date;

+}

+

+export interface CreateTechVendorPossibleItemData {

+  vendorId: number;

+  itemCode: string;

+}

+

+export interface ImportTechVendorPossibleItemData {

+  vendorCode: string;

+  vendorEmail?: string;

+  itemCode: string;

+}

+

+export interface ImportResult {

+  success: boolean;

+  totalRows: number;

+  successCount: number;

+  failedRows: { 

+    row: number; 

+    error: string; 

+    vendorCode?: string; 

+    vendorEmail?: string;

+    itemCode?: string; 

+  }[];

+}

+

+

+

+/**

+ * 견적프로젝트 패턴에 맞는 메인 조회 함수

+ */

+export async function getTechVendorPossibleItems(input: GetTechVendorPossibleItemsSchema) {

+  return unstable_cache(

+    async () => {

+      try {

+        const offset = (input.page - 1) * input.perPage;

+

+        // 고급 필터링 (DataTableAdvancedToolbar용)

+        const advancedWhere = filterColumns({

+          table: techVendorPossibleItems,

+          filters: input.filters,

+          joinOperator: input.joinOperator,

+        });

+

+        // 전역 검색 (search box용)

+        let globalWhere;

+        if (input.search) {

+          const s = `%${input.search}%`;

+          globalWhere = or(

+            ilike(techVendors.vendorCode, s),

+            ilike(techVendors.vendorName, s),

+            ilike(techVendorPossibleItems.itemCode, s),

+          );

+        }

+

+        // 기존 호환성을 위한 개별 필터들

+        const legacyFilters = [];

+        if (input.vendorCode) {

+          legacyFilters.push(ilike(techVendors.vendorCode, `%${input.vendorCode}%`));

+        }

+        if (input.vendorName) {

+          legacyFilters.push(ilike(techVendors.vendorName, `%${input.vendorName}%`));

+        }

+        if (input.itemCode) {

+          legacyFilters.push(ilike(techVendorPossibleItems.itemCode, `%${input.itemCode}%`));

+        }

+

+        // 벤더 타입 필터링

+        if (input.vendorType && input.vendorType !== "all") {

+          const vendorTypeMap = {

+            "ship": "조선",

+            "top": "해양TOP", 

+            "hull": "해양HULL"

+          };

+          

+          const actualVendorType = vendorTypeMap[input.vendorType as keyof typeof vendorTypeMap] || input.vendorType;

+          

+          if (actualVendorType) {

+            legacyFilters.push(ilike(techVendors.techVendorType, `%${actualVendorType}%`));

+          }

+        }

+

+        // 모든 조건 결합

+        const finalWhere = and(

+          advancedWhere,

+          globalWhere,

+          ...(legacyFilters.length > 0 ? [and(...legacyFilters)] : [])

+        );

+

+        // 정렬 조건

+        const orderBy = [desc(techVendorPossibleItems.createdAt)];

+

+        // 트랜잭션 내에서 Repository 호출

+        const { data, total } = await db.transaction(async (tx) => {

+          const data = await selectTechVendorPossibleItemsWithJoin(tx, finalWhere, orderBy, offset, input.perPage);

+

+          const total = await countTechVendorPossibleItemsWithJoin(tx, finalWhere);

+          return { data, total };

+        });

+

+        const pageCount = Math.ceil(total / input.perPage);

+

+        return { data, pageCount, totalCount: total };

+      } catch (error) {

+        console.error("Error fetching tech vendor possible items:", error);

+        return { data: [], pageCount: 0, totalCount: 0 };

+      }

+    },

+    [JSON.stringify(input)],

+    {

+      revalidate: 3600,

+      tags: ["tech-vendor-possible-items"],

+    }

+  )();

+}

+

+

+

+/**

+ * 페이지네이션을 포함한 tech vendor possible items 조회

+ */

+// export async function getTechVendorPossibleItemsWithPagination(

+//   page: number = 1,

+//   pageSize: number = 50,

+//   searchTerm?: string,

+//   vendorType?: string

+// ): Promise<{

+//   data: TechVendorPossibleItemsData[];

+//   totalCount: number;

+//   totalPages: number;

+// }> {

+//   const whereConditions = [];

+  

+//   if (searchTerm) {

+//     whereConditions.push(

+//       sql`(

+//         ${techVendors.vendorName} ILIKE ${`%${searchTerm}%`} OR

+//         ${techVendors.vendorCode} ILIKE ${`%${searchTerm}%`} OR

+//         ${techVendorPossibleItems.itemCode} ILIKE ${`%${searchTerm}%`}

+//       )`

+//     );

+//   }

+

+//   // 벤더 타입 필터링 로직 추가

+//   if (vendorType && vendorType !== "all") {

+//     // URL의 vendorType 파라미터를 실제 벤더 타입으로 매핑

+//     const vendorTypeMap = {

+//       "ship": "조선",

+//       "top": "해양TOP", 

+//       "hull": "해양HULL"

+//     };

+    

+//     const actualVendorType = vendorType in vendorTypeMap 

+//       ? vendorTypeMap[vendorType as keyof typeof vendorTypeMap]

+//       : vendorType; // 매핑되지 않는 경우 원본 값 사용

+    

+//     if (actualVendorType) {

+//       // techVendorType 필드는 콤마로 구분된 문자열이므로 LIKE 사용

+//       whereConditions.push(sql`${techVendors.techVendorType} ILIKE ${`%${actualVendorType}%`}`);

+//     }

+//   }

+

+//   const whereClause = whereConditions.length > 0 ? and(...whereConditions) : undefined;

+

+//   // 총 개수 조회

+//   const [totalCountResult] = await db

+//     .select({ count: count() })

+//     .from(techVendorPossibleItems)

+//     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+//     .where(whereClause);

+

+//   const totalCount = totalCountResult.count;

+//   const totalPages = Math.ceil(totalCount / pageSize);

+//   const offset = (page - 1) * pageSize;

+

+//   // 데이터 조회

+//   const data = await db

+//     .select({

+//       id: techVendorPossibleItems.id,

+//       vendorId: techVendorPossibleItems.vendorId,

+//       vendorCode: techVendors.vendorCode,

+//       vendorName: techVendors.vendorName,

+//       techVendorType: techVendors.techVendorType,

+//       itemCode: techVendorPossibleItems.itemCode,

+//       createdAt: techVendorPossibleItems.createdAt,

+//       updatedAt: techVendorPossibleItems.updatedAt,

+//     })

+//     .from(techVendorPossibleItems)

+//     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+//     .where(whereClause)

+//     .orderBy(desc(techVendorPossibleItems.createdAt))

+//     .limit(pageSize)

+//     .offset(offset);

+

+//   return {

+//     data,

+//     totalCount,

+//     totalPages,

+//   };

+// }

+

+/**

+ * tech vendor possible item 생성 (간단 버전)

+ */

+export async function createTechVendorPossibleItem(

+  data: CreateTechVendorPossibleItemData

+): Promise<{ success: boolean; error?: string }> {

+  try {

+    // 벤더 존재 여부만 확인

+    const vendor = await db

+      .select()

+      .from(techVendors)

+      .where(eq(techVendors.id, data.vendorId))

+      .limit(1);

+

+    if (!vendor[0]) {

+      return { success: false, error: "벤더를 찾을 수 없습니다." };

+    }

+

+    // 중복 체크

+    const existing = await db

+      .select()

+      .from(techVendorPossibleItems)

+      .where(

+        and(

+          eq(techVendorPossibleItems.vendorId, data.vendorId),

+          eq(techVendorPossibleItems.itemCode, data.itemCode)

+        )

+      )

+      .limit(1);

+

+    if (existing.length > 0) {

+      return { success: false, error: "이미 존재하는 벤더-아이템 조합입니다." };

+    }

+

+    // 아이템 코드 검증 없이 바로 삽입

+    await db.insert(techVendorPossibleItems).values({

+      vendorId: data.vendorId,

+      itemCode: data.itemCode,

+    });

+

+    return { success: true };

+  } catch (error) {

+    console.error("Failed to create tech vendor possible item:", error);

+    return { 

+      success: false, 

+      error: error instanceof Error ? error.message : "생성 중 오류가 발생했습니다." 

+    };

+  }

+}

+

+/**

+ * tech vendor possible items 삭제

+ */

+export async function deleteTechVendorPossibleItems(

+  ids: number[]

+): Promise<{ success: boolean; error?: string }> {

+  try {

+    await db

+      .delete(techVendorPossibleItems)

+      .where(inArray(techVendorPossibleItems.id, ids));

+

+    return { success: true };

+  } catch (error) {

+    console.error("Failed to delete tech vendor possible items:", error);

+    return { 

+      success: false, 

+      error: error instanceof Error ? error.message : "삭제 중 오류가 발생했습니다." 

+    };

+  }

+}

+

+/**

+ * 벤더 코드로 벤더 정보 조회

+ */

+export async function getTechVendorByCode(vendorCode: string) {

+  const result = await db

+    .select()

+    .from(techVendors)

+    .where(eq(techVendors.vendorCode, vendorCode))

+    .limit(1);

+

+  return result[0] || null;

+}

+

+/**

+ * 벤더 이메일로 벤더 정보 조회

+ */

+export async function getTechVendorByEmail(vendorEmail: string) {

+  const result = await db

+    .select()

+    .from(techVendors)

+    .where(eq(techVendors.email, vendorEmail))

+    .limit(1);

+

+  return result[0] || null;

+}

+

+/**

+ * 벤더 타입에 따라 적절한 아이템 테이블에서 아이템 조회

+ */

+export async function getItemByCodeAndVendorType(itemCode: string, vendorType: string) {

+  try {

+    switch (vendorType) {

+      case "조선":

+        const shipItem = await db

+          .select()

+          .from(itemShipbuilding)

+          .where(eq(itemShipbuilding.itemCode, itemCode))

+          .limit(1);

+        return shipItem[0] ? {

+          itemCode: shipItem[0].itemCode,

+          workType: shipItem[0].workType

+        } : null;

+

+      case "해양TOP":

+        const topItem = await db

+          .select()

+          .from(itemOffshoreTop)

+          .where(eq(itemOffshoreTop.itemCode, itemCode))

+          .limit(1);

+        return topItem[0] ? {

+          itemCode: topItem[0].itemCode,

+          workType: topItem[0].workType

+        } : null;

+

+      case "해양HULL":

+        const hullItem = await db

+          .select()

+          .from(itemOffshoreHull)

+          .where(eq(itemOffshoreHull.itemCode, itemCode))

+          .limit(1);

+        return hullItem[0] ? {

+          itemCode: hullItem[0].itemCode,

+          workType: hullItem[0].workType

+        } : null;

+

+      default:

+        return null;

+    }

+  } catch (error) {

+    console.error("Error fetching item by code and vendor type:", error);

+    return null;

+  }

+}

+

+/**

+ * 아이템 코드로 아이템 정보 조회 (기존 함수 - 호환성 유지)

+ */

+export async function getItemByCode(itemCode: string) {

+  // 기존 items 테이블 대신 조선 테이블에서 먼저 조회 시도

+  try {

+    const shipItem = await db

+      .select()

+      .from(itemShipbuilding)

+      .where(eq(itemShipbuilding.itemCode, itemCode))

+      .limit(1);

+    

+    if (shipItem[0]) {

+      return {

+        itemCode: shipItem[0].itemCode,

+      };

+    }

+

+    const topItem = await db

+      .select()

+      .from(itemOffshoreTop)

+      .where(eq(itemOffshoreTop.itemCode, itemCode))

+      .limit(1);

+    

+    if (topItem[0]) {

+      return {

+        itemCode: topItem[0].itemCode,

+      };

+    }

+

+    const hullItem = await db

+      .select()

+      .from(itemOffshoreHull)

+      .where(eq(itemOffshoreHull.itemCode, itemCode))

+      .limit(1);

+    

+    if (hullItem[0]) {

+      return {

+        itemCode: hullItem[0].itemCode,

+      };

+    }

+

+    return null;

+  } catch (error) {

+    console.error("Error fetching item by code:", error);

+    return null;

+  }

+}

+

+/**

+ * Import 기능: 벤더코드와 아이템코드를 통한 batch insert (간단 버전)

+ */

+export async function importTechVendorPossibleItems(

+  data: ImportTechVendorPossibleItemData[]

+): Promise<ImportResult> {

+  const result: ImportResult = {

+    success: true,

+    totalRows: data.length,

+    successCount: 0,

+    failedRows: [],

+  };

+

+  for (let i = 0; i < data.length; i++) {

+    const row = data[i];

+    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()) {

+        // 벤더 코드가 없으면 이메일로 검색

+        vendor = await getTechVendorByEmail(row.vendorEmail);

+      }

+      

+      if (!vendor) {

+        const identifier = row.vendorCode ? `벤더 코드 '${row.vendorCode}'` : 

+                          row.vendorEmail ? `벤더 이메일 '${row.vendorEmail}'` : 

+                          '벤더 코드 또는 이메일';

+        result.failedRows.push({

+          row: rowNumber,

+          error: `${identifier}을(를) 찾을 수 없습니다.`,

+          vendorCode: row.vendorCode,

+          vendorEmail: row.vendorEmail,

+          itemCode: row.itemCode,

+        });

+        continue;

+      }

+

+      // 중복 체크

+      const existing = await db

+        .select()

+        .from(techVendorPossibleItems)

+        .where(

+          and(

+            eq(techVendorPossibleItems.vendorId, vendor.id),

+            eq(techVendorPossibleItems.itemCode, row.itemCode)

+          )

+        )

+        .limit(1);

+

+      if (existing.length > 0) {

+        result.failedRows.push({

+          row: rowNumber,

+          error: `이미 존재하는 벤더-아이템 조합입니다.`,

+          vendorCode: row.vendorCode,

+          vendorEmail: row.vendorEmail,

+          itemCode: row.itemCode,

+        });

+        continue;

+      }

+

+      // 아이템 코드 검증 없이 바로 삽입

+      await db.insert(techVendorPossibleItems).values({

+        vendorId: vendor.id,

+        itemCode: row.itemCode,

+      });

+

+      result.successCount++;

+    } catch (error) {

+      result.failedRows.push({

+        row: rowNumber,

+        error: error instanceof Error ? error.message : "알 수 없는 오류",

+        vendorCode: row.vendorCode,

+        vendorEmail: row.vendorEmail,

+        itemCode: row.itemCode,

+      });

+    }

+  }

+

+  if (result.failedRows.length > 0) {

+    result.success = false;

+  }

+

+  return result;

+}

+

+/**

+ * 모든 기술영업 벤더 조회 (드롭다운용)

+ */

+export async function getAllTechVendors() {

+  return await db

+    .select({

+      id: techVendors.id,

+      vendorCode: techVendors.vendorCode,

+      vendorName: techVendors.vendorName,

+      techVendorType: techVendors.techVendorType,

+    })

+    .from(techVendors)

+    .where(eq(techVendors.status, "ACTIVE"))

+    .orderBy(asc(techVendors.vendorName));

+}

+

+/**

+ * 고유한 벤더 타입 목록 조회 (필터용)

+ */

+export async function getUniqueTechVendorTypes(): Promise<string[]> {

+  try {

+    const result = await db

+      .select({

+        techVendorType: techVendors.techVendorType,

+      })

+      .from(techVendors)

+      .where(eq(techVendors.status, "ACTIVE"));

+

+    // techVendorType이 JSON 배열 형태로 저장된 경우를 고려

+    const allTypes = new Set<string>();

+    

+    result.forEach(row => {

+      try {

+        // techVendorType이 JSON 문자열인지 확인

+        if (row.techVendorType && row.techVendorType.startsWith('[')) {

+          const types = JSON.parse(row.techVendorType);

+          if (Array.isArray(types)) {

+            types.forEach(type => {

+              if (type && typeof type === 'string') {

+                allTypes.add(type.trim());

+              }

+            });

+          }

+        } else if (row.techVendorType) {

+          // 단순 문자열인 경우

+          row.techVendorType.split(',').forEach(type => {

+            const trimmedType = type.trim();

+            if (trimmedType) {

+              allTypes.add(trimmedType);

+            }

+          });

+        }

+      } catch {

+        // JSON 파싱 실패시 문자열로 처리

+        if (row.techVendorType) {

+          row.techVendorType.split(',').forEach(type => {

+            const trimmedType = type.trim();

+            if (trimmedType) {

+              allTypes.add(trimmedType);

+            }

+          });

+        }

+      }

+    });

+

+    return Array.from(allTypes).sort();

+  } catch (error) {

+    console.error("Error fetching unique tech vendor types:", error);

+    // 오류 발생시 기본 벤더 타입 반환

+    return ["조선", "해양TOP", "해양HULL"];

+  }

+} 
\ No newline at end of file