(김준회) 기준정보-자재그룹 메뉴 구현

- 기준정보 메뉴를 자재그룹코드(/material-groups)로 변경
- 기존 자재코드조회는 /materials 로 남겨둠
- 메뉴를 자재그룹코드로만 남기고, i18n 처리

1 files changed, 165 insertions, 0 deletions

diff --git a/lib/material-groups/services.ts b/lib/material-groups/services.ts
new file mode 100644
index 00000000..4b7e9dea
--- /dev/null
+++ b/lib/material-groups/services.ts
@@ -0,0 +1,165 @@
+'use server'
+
+import { and, asc, desc, ilike, or, sql, eq } from 'drizzle-orm';
+import db from '@/db/db';
+import { filterColumns } from "@/lib/filter-columns";
+import { materialSearchView } from "@/db/schema/items";
+
+// 자재그룹 뷰의 컬럼 타입 정의
+type MaterialGroupColumn = keyof typeof materialSearchView.$inferSelect;
+
+export interface GetMaterialGroupsInput {
+  page: number;
+  perPage: number;
+  search?: string;
+  sort: Array<{
+    id: MaterialGroupColumn;
+    desc: boolean;
+  }>;
+  filters?: any[];
+  joinOperator: 'and' | 'or';
+}
+
+/**
+ * 자재그룹 목록을 조회합니다.
+ * materialSearchView를 사용하여 MATKL(자재그룹코드)와 ZZNAME(자재그룹명)의 고유한 조합을 조회
+ */
+export async function getMaterialGroups(input: GetMaterialGroupsInput) {
+  const safePerPage = Math.min(input.perPage, 100);
+  
+  try {
+    const offset = (input.page - 1) * safePerPage;
+    
+    // 고급 필터링
+    const advancedWhere = filterColumns({
+      table: materialSearchView,
+      filters: (input.filters || []) as any,
+      joinOperator: input.joinOperator,
+    });
+
+    // 전역 검색 - 주요 컬럼들에 대해 검색
+    let globalWhere;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(materialSearchView.materialGroupCode, s),      // 자재그룹코드
+        ilike(materialSearchView.materialName, s),           // 자재그룹명
+        ilike(materialSearchView.displayText, s),            // 표시 텍스트
+      );
+    }
+
+    const finalWhere = and(advancedWhere, globalWhere);
+
+    // 정렬 처리 - 타입 안전하게 처리
+    const orderBy = input.sort.length > 0
+      ? input.sort.map((item) => {
+          const column = materialSearchView[item.id];
+          return item.desc ? desc(column) : asc(column);
+        })
+      : [asc(materialSearchView.materialGroupCode)];
+
+    // 데이터 조회
+    const { data, total } = await db.transaction(async (tx) => {
+      const data = await tx
+        .select({
+          materialGroupCode: materialSearchView.materialGroupCode,
+          materialName: materialSearchView.materialName,
+          displayText: materialSearchView.displayText,
+        })
+        .from(materialSearchView)
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .offset(offset)
+        .limit(safePerPage);
+
+      const totalResult = await tx
+        .select({
+          count: sql<number>`count(*)`
+        })
+        .from(materialSearchView)
+        .where(finalWhere);
+      
+      const total = Number(totalResult[0]?.count) || 0;
+      return { data, total };
+    });
+
+    const pageCount = Math.ceil(total / safePerPage);
+    return { data, pageCount };
+  } catch (err) {
+    console.error('Error in getMaterialGroups:', err);
+    return { data: [], pageCount: 0 };
+  }
+}
+
+/**
+ * 무한 스크롤을 위한 자재그룹 조회 (페이지네이션 없음)
+ */
+export interface GetMaterialGroupsInfiniteInput extends Omit<GetMaterialGroupsInput, 'page' | 'perPage'> {
+  limit?: number; // 무한 스크롤용 추가 옵션
+}
+
+export async function getMaterialGroupsInfinite(input: GetMaterialGroupsInfiniteInput) {
+  try {
+    // 고급 필터링
+    const advancedWhere = filterColumns({
+      table: materialSearchView,
+      filters: (input.filters || []) as any,
+      joinOperator: input.joinOperator || "and",
+    });
+
+    // 전역 검색
+    let globalWhere;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(materialSearchView.materialGroupCode, s),
+        ilike(materialSearchView.materialName, s),
+        ilike(materialSearchView.displayText, s),
+      );
+    }
+
+    const finalWhere = and(advancedWhere, globalWhere);
+
+    // 정렬 처리 - 타입 안전하게 처리
+    const orderBy = input.sort.length > 0
+      ? input.sort.map((item) => {
+          const column = materialSearchView[item.id];
+          return item.desc ? desc(column) : asc(column);
+        })
+      : [asc(materialSearchView.materialGroupCode)];
+
+    // 전체 데이터 조회 (클라이언트에서 가상화 처리)
+    const data = await db
+      .select({
+        materialGroupCode: materialSearchView.materialGroupCode,
+        materialName: materialSearchView.materialName,
+        displayText: materialSearchView.displayText,
+      })
+      .from(materialSearchView)
+      .where(finalWhere)
+      .orderBy(...orderBy);
+
+    return { data };
+  } catch (err) {
+    console.error('Error in getMaterialGroupsInfinite:', err);
+    return { data: [] };
+  }
+}
+
+/**
+ * 특정 자재그룹 상세 정보 조회
+ */
+export async function getMaterialGroupDetail(materialGroupCode: string) {
+  try {
+    const materialGroup = await db
+      .select()
+      .from(materialSearchView)
+      .where(eq(materialSearchView.materialGroupCode, materialGroupCode))
+      .limit(1);
+
+    return materialGroup.length > 0 ? materialGroup[0] : null;
+  } catch (err) {
+    console.error('Error in getMaterialGroupDetail:', err);
+    return null;
+  }
+}