diff options
Diffstat (limited to 'lib/material/services.ts')
| -rw-r--r-- | lib/material/services.ts | 224 |
1 files changed, 224 insertions, 0 deletions
diff --git a/lib/material/services.ts b/lib/material/services.ts new file mode 100644 index 00000000..e050d8f8 --- /dev/null +++ b/lib/material/services.ts @@ -0,0 +1,224 @@ +'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 { + MATERIAL_MASTER_PART_MATL, + MATERIAL_MASTER_PART_MATL_CHARASGN, + MATERIAL_MASTER_PART_MATL_CLASSASGN, + MATERIAL_MASTER_PART_MATL_DESC, + MATERIAL_MASTER_PART_MATL_UNIT +} from "@/db/schema/MDG/mdg"; + +// 자재마스터 테이블의 컬럼 타입 정의 +type MaterialColumn = keyof typeof MATERIAL_MASTER_PART_MATL.$inferSelect; + +export interface GetMaterialsInput { + page: number; + perPage: number; + search?: string; + sort: Array<{ + id: MaterialColumn; + desc: boolean; + }>; + filters?: any[]; + joinOperator: 'and' | 'or'; +} + +/** + * 자재마스터 목록을 조회합니다. + * 필수 컬럼: MATKL(자재그룹), MATNR(자재코드), ZZNAME(자재명), ZZPJT(프로젝트), createdAt, updatedAt + */ +export async function getMaterials(input: GetMaterialsInput) { + const safePerPage = Math.min(input.perPage, 100); + + try { + const offset = (input.page - 1) * safePerPage; + + // 고급 필터링 + const advancedWhere = filterColumns({ + table: MATERIAL_MASTER_PART_MATL, + filters: (input.filters || []) as any, + joinOperator: input.joinOperator, + }); + + // 전역 검색 - 주요 컬럼들에 대해 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(MATERIAL_MASTER_PART_MATL.MATKL, s), // 자재그룹코드 + ilike(MATERIAL_MASTER_PART_MATL.MATNR, s), // 자재코드 + ilike(MATERIAL_MASTER_PART_MATL.ZZNAME, s), // 자재명 + ilike(MATERIAL_MASTER_PART_MATL.ZZPJT, s), // 프로젝트 + ); + } + + const finalWhere = and(advancedWhere, globalWhere); + + // 정렬 처리 - 타입 안전하게 처리 + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + const column = MATERIAL_MASTER_PART_MATL[item.id]; + return item.desc ? desc(column) : asc(column); + }) + : [desc(MATERIAL_MASTER_PART_MATL.createdAt)]; + + // 데이터 조회 - 필요한 컬럼만 선택 + const { data, total } = await db.transaction(async (tx) => { + const data = await tx + .select({ + id: MATERIAL_MASTER_PART_MATL.id, + MATKL: MATERIAL_MASTER_PART_MATL.MATKL, // 자재그룹(=자재그룹코드) + MATNR: MATERIAL_MASTER_PART_MATL.MATNR, // 자재코드(=자재번호) + ZZNAME: MATERIAL_MASTER_PART_MATL.ZZNAME, // 자재명(=자재그룹명) + ZZPJT: MATERIAL_MASTER_PART_MATL.ZZPJT, // 프로젝트 + createdAt: MATERIAL_MASTER_PART_MATL.createdAt, + updatedAt: MATERIAL_MASTER_PART_MATL.updatedAt, + }) + .from(MATERIAL_MASTER_PART_MATL) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(safePerPage); + + const totalResult = await tx + .select({ + count: sql<number>`count(*)` + }) + .from(MATERIAL_MASTER_PART_MATL) + .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 getMaterials:', err); + return { data: [], pageCount: 0 }; + } +} + +/** + * 무한 스크롤을 위한 자재마스터 조회 (페이지네이션 없음) + */ +export interface GetMaterialsInfiniteInput extends Omit<GetMaterialsInput, 'page' | 'perPage'> { + limit?: number; // 무한 스크롤용 추가 옵션 +} + +export async function getMaterialsInfinite(input: GetMaterialsInfiniteInput) { + try { + // 고급 필터링 + const advancedWhere = filterColumns({ + table: MATERIAL_MASTER_PART_MATL, + filters: (input.filters || []) as any, + joinOperator: input.joinOperator || "and", + }); + + // 전역 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(MATERIAL_MASTER_PART_MATL.MATKL, s), + ilike(MATERIAL_MASTER_PART_MATL.MATNR, s), + ilike(MATERIAL_MASTER_PART_MATL.ZZNAME, s), + ilike(MATERIAL_MASTER_PART_MATL.ZZPJT, s), + ); + } + + const finalWhere = and(advancedWhere, globalWhere); + + // 정렬 처리 - 타입 안전하게 처리 + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + const column = MATERIAL_MASTER_PART_MATL[item.id]; + return item.desc ? desc(column) : asc(column); + }) + : [desc(MATERIAL_MASTER_PART_MATL.createdAt)]; + + // 전체 데이터 조회 (클라이언트에서 가상화 처리) + const data = await db + .select({ + id: MATERIAL_MASTER_PART_MATL.id, + MATKL: MATERIAL_MASTER_PART_MATL.MATKL, + MATNR: MATERIAL_MASTER_PART_MATL.MATNR, + ZZNAME: MATERIAL_MASTER_PART_MATL.ZZNAME, + ZZPJT: MATERIAL_MASTER_PART_MATL.ZZPJT, + createdAt: MATERIAL_MASTER_PART_MATL.createdAt, + updatedAt: MATERIAL_MASTER_PART_MATL.updatedAt, + }) + .from(MATERIAL_MASTER_PART_MATL) + .where(finalWhere) + .orderBy(...orderBy); + + return { data }; + } catch (err) { + console.error('Error in getMaterialsInfinite:', err); + return { data: [] }; + } +} + +/** + * 자재마스터 상세 정보를 조회합니다 (모든 관련 테이블 포함) + */ +export async function getMaterialDetail(matnr: string) { + try { + // 메인 자재 정보 조회 + const material = await db + .select() + .from(MATERIAL_MASTER_PART_MATL) + .where(eq(MATERIAL_MASTER_PART_MATL.MATNR, matnr)) + .limit(1); + + if (material.length === 0) { + return null; + } + + // 관련 테이블들 조회 + const [ + characteristics, + classifications, + descriptions, + units + ] = await Promise.all([ + // CHARASGN - 특성 할당 + db + .select() + .from(MATERIAL_MASTER_PART_MATL_CHARASGN) + .where(eq(MATERIAL_MASTER_PART_MATL_CHARASGN.MATNR, matnr)), + + // CLASSASGN - 클래스 할당 + db + .select() + .from(MATERIAL_MASTER_PART_MATL_CLASSASGN) + .where(eq(MATERIAL_MASTER_PART_MATL_CLASSASGN.MATNR, matnr)), + + // DESC - 설명 + db + .select() + .from(MATERIAL_MASTER_PART_MATL_DESC) + .where(eq(MATERIAL_MASTER_PART_MATL_DESC.MATNR, matnr)), + + // UNIT - 단위 + db + .select() + .from(MATERIAL_MASTER_PART_MATL_UNIT) + .where(eq(MATERIAL_MASTER_PART_MATL_UNIT.MATNR, matnr)) + ]); + + return { + material: material[0], + characteristics, + classifications, + descriptions, + units + }; + } catch (err) { + console.error('Error in getMaterialDetail:', err); + return null; + } +}
\ No newline at end of file |