diff options
Diffstat (limited to 'lib/material-groups')
| -rw-r--r-- | lib/material-groups/services.ts | 4 | ||||
| -rw-r--r-- | lib/material-groups/sync-service.ts | 200 | ||||
| -rw-r--r-- | lib/material-groups/table/material-group-table-columns.tsx | 5 | ||||
| -rw-r--r-- | lib/material-groups/table/material-group-table.tsx | 6 |
4 files changed, 202 insertions, 13 deletions
diff --git a/lib/material-groups/services.ts b/lib/material-groups/services.ts index ff2b7f70..ecbbc9e8 100644 --- a/lib/material-groups/services.ts +++ b/lib/material-groups/services.ts @@ -44,7 +44,6 @@ export async function getMaterialGroups(input: GetMaterialGroupsInput) { globalWhere = or( ilike(materialSearchView.materialGroupCode, s), // 자재그룹코드 ilike(materialSearchView.materialGroupDesc, s), // 자재그룹 설명 - ilike(materialSearchView.displayText, s), // 표시 텍스트 ); } @@ -64,7 +63,6 @@ export async function getMaterialGroups(input: GetMaterialGroupsInput) { .select({ materialGroupCode: materialSearchView.materialGroupCode, materialGroupDesc: materialSearchView.materialGroupDesc, - displayText: materialSearchView.displayText, }) .from(materialSearchView) .where(finalWhere) @@ -114,7 +112,6 @@ export async function getMaterialGroupsInfinite(input: GetMaterialGroupsInfinite globalWhere = or( ilike(materialSearchView.materialGroupCode, s), ilike(materialSearchView.materialGroupDesc, s), - ilike(materialSearchView.displayText, s), ); } @@ -133,7 +130,6 @@ export async function getMaterialGroupsInfinite(input: GetMaterialGroupsInfinite .select({ materialGroupCode: materialSearchView.materialGroupCode, materialGroupDesc: materialSearchView.materialGroupDesc, - displayText: materialSearchView.displayText, }) .from(materialSearchView) .where(finalWhere) diff --git a/lib/material-groups/sync-service.ts b/lib/material-groups/sync-service.ts new file mode 100644 index 00000000..eda5dba5 --- /dev/null +++ b/lib/material-groups/sync-service.ts @@ -0,0 +1,200 @@ +"use server"; + +import { sql } from "drizzle-orm"; +import db from "@/db/db"; +import { + MATERIAL_MASTER_PART_MATL, + MATERIAL_MASTER_PART_MATL_DESC, + MATERIAL_GROUP_MASTER +} from "@/db/schema/MDG/mdg"; + +export interface SyncResult { + success: boolean; + message: string; + data?: { + totalProcessed: number; + newRecords: number; + updatedRecords: number; + skippedRecords: number; + }; +} + +/** + * 기존 MDG 데이터로부터 MATERIAL_GROUP_MASTER 테이블을 동기화하는 함수 + * + * 로직: + * 1. MATERIAL_MASTER_PART_MATL에서 MATKL(자재그룹코드)가 있는 레코드 조회 + * 2. 각 MATKL에 대해 MATERIAL_MASTER_PART_MATL_DESC에서 SPRAS='E'인 MAKTX 찾기 + * 3. MATERIAL_GROUP_MASTER에 UPSERT (MATKL 기준으로 중복 제거) + */ +export async function syncMaterialGroupMaster(): Promise<SyncResult> { + try { + console.log("📊 자재그룹 마스터 동기화 시작..."); + + const result = await db.transaction(async (tx) => { + // 1단계: 기존 MDG 테이블에서 MATKL별 영어 설명 조회 + const materialGroupData = await tx + .select({ + MATKL: MATERIAL_MASTER_PART_MATL.MATKL, + MAKTX: sql<string>` + ( + SELECT ${MATERIAL_MASTER_PART_MATL_DESC.MAKTX} + FROM ${MATERIAL_MASTER_PART_MATL_DESC} + WHERE ${MATERIAL_MASTER_PART_MATL_DESC.MATNR} = ${MATERIAL_MASTER_PART_MATL.MATNR} + AND ${MATERIAL_MASTER_PART_MATL_DESC.SPRAS} = 'E' + AND ${MATERIAL_MASTER_PART_MATL_DESC.MAKTX} IS NOT NULL + AND ${MATERIAL_MASTER_PART_MATL_DESC.MAKTX} != '' + LIMIT 1 + ) + `.as("MAKTX") + }) + .from(MATERIAL_MASTER_PART_MATL) + .where(sql` + ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL + AND ${MATERIAL_MASTER_PART_MATL.MATKL} != '' + `) + .groupBy(MATERIAL_MASTER_PART_MATL.MATKL); // MATKL별 중복 제거 + + console.log(`🔍 발견된 고유 자재그룹코드: ${materialGroupData.length}개`); + + // 2단계: MAKTX가 있는 레코드만 필터링 + const validData = materialGroupData.filter(item => + item.MAKTX && item.MAKTX.trim() !== '' + ); + + console.log(`✅ 유효한 자재그룹 데이터: ${validData.length}개`); + + if (validData.length === 0) { + return { + totalProcessed: 0, + newRecords: 0, + updatedRecords: 0, + skippedRecords: materialGroupData.length + }; + } + + // 3단계: 기존 MATERIAL_GROUP_MASTER의 모든 MATKL 조회 + const existingRecords = await tx + .select({ MATKL: MATERIAL_GROUP_MASTER.MATKL }) + .from(MATERIAL_GROUP_MASTER); + + const existingMatkls = new Set(existingRecords.map(r => r.MATKL)); + + // 4단계: 신규/업데이트 데이터 분류 + const newRecords = validData.filter(item => !existingMatkls.has(item.MATKL)); + const updateRecords = validData.filter(item => existingMatkls.has(item.MATKL)); + + let insertedCount = 0; + let updatedCount = 0; + + // 5단계: 신규 레코드 삽입 (배치 처리) + if (newRecords.length > 0) { + await tx + .insert(MATERIAL_GROUP_MASTER) + .values( + newRecords.map(item => ({ + MATKL: item.MATKL, + MAKTX: item.MAKTX + })) + ); + insertedCount = newRecords.length; + console.log(`➕ 신규 삽입: ${insertedCount}개`); + } + + // 6단계: 기존 레코드 업데이트 (배치 처리) + if (updateRecords.length > 0) { + // PostgreSQL의 UPDATE ... FROM 구문 사용 + await tx.execute(sql` + UPDATE ${MATERIAL_GROUP_MASTER} + SET + ${MATERIAL_GROUP_MASTER.MAKTX} = temp_data.maktx, + ${MATERIAL_GROUP_MASTER.updatedAt} = NOW() + FROM ( + VALUES ${sql.join( + updateRecords.map(item => + sql`(${item.MATKL}, ${item.MAKTX})` + ), + sql`, ` + )} + ) AS temp_data(matkl, maktx) + WHERE ${MATERIAL_GROUP_MASTER.MATKL} = temp_data.matkl + `); + updatedCount = updateRecords.length; + console.log(`🔄 업데이트: ${updatedCount}개`); + } + + return { + totalProcessed: validData.length, + newRecords: insertedCount, + updatedRecords: updatedCount, + skippedRecords: materialGroupData.length - validData.length + }; + }); + + console.log("✅ 자재그룹 마스터 동기화 완료"); + + return { + success: true, + message: `동기화 완료: 신규 ${result.newRecords}개, 업데이트 ${result.updatedRecords}개, 건너뜀 ${result.skippedRecords}개`, + data: result + }; + + } catch (error) { + console.error("❌ 자재그룹 마스터 동기화 오류:", error); + + return { + success: false, + message: `동기화 실패: ${error instanceof Error ? error.message : '알 수 없는 오류'}` + }; + } +} + +/** + * 동기화 상태 확인 함수 + */ +export async function getMaterialGroupSyncStatus(): Promise<{ + success: boolean; + data?: { + totalMDGRecords: number; + totalMasterRecords: number; + lastSyncDate?: string; + }; +}> { + try { + const [mdgCount, masterCount] = await Promise.all([ + // MDG에서 유효한 MATKL 개수 + db + .select({ count: sql<number>`count(distinct ${MATERIAL_MASTER_PART_MATL.MATKL})` }) + .from(MATERIAL_MASTER_PART_MATL) + .where(sql` + ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL + AND ${MATERIAL_MASTER_PART_MATL.MATKL} != '' + `), + + // MATERIAL_GROUP_MASTER 개수 + db + .select({ count: sql<number>`count(*)` }) + .from(MATERIAL_GROUP_MASTER) + ]); + + // 최근 업데이트 시간 조회 + const lastUpdated = await db + .select({ + lastUpdate: sql<string>`max(${MATERIAL_GROUP_MASTER.updatedAt})` + }) + .from(MATERIAL_GROUP_MASTER); + + return { + success: true, + data: { + totalMDGRecords: mdgCount[0]?.count || 0, + totalMasterRecords: masterCount[0]?.count || 0, + lastSyncDate: lastUpdated[0]?.lastUpdate || undefined + } + }; + + } catch (error) { + console.error("동기화 상태 확인 오류:", error); + return { success: false }; + } +} diff --git a/lib/material-groups/table/material-group-table-columns.tsx b/lib/material-groups/table/material-group-table-columns.tsx index f958104c..3d304325 100644 --- a/lib/material-groups/table/material-group-table-columns.tsx +++ b/lib/material-groups/table/material-group-table-columns.tsx @@ -9,7 +9,6 @@ import { DataTableColumnHeaderSimple } from "@/components/data-table/data-table- type MaterialGroup = { materialGroupCode: string | null; materialGroupDesc: string | null; - displayText: string | null; } /** @@ -28,7 +27,7 @@ export function getColumns(): ColumnDef<MaterialGroup>[] { cell: ({ row }) => { const value = row.getValue("materialGroupCode") as string | null return ( - <div className="font-medium"> + <div className="font-medium w-[100px]"> {value || "-"} </div> ) @@ -44,7 +43,7 @@ export function getColumns(): ColumnDef<MaterialGroup>[] { cell: ({ row }) => { const value = row.getValue("materialGroupDesc") as string | null return ( - <div className="max-w-[300px] truncate"> + <div className="max-w-[400px] truncate"> {value || "-"} </div> ) diff --git a/lib/material-groups/table/material-group-table.tsx b/lib/material-groups/table/material-group-table.tsx index e38ccf70..766b5054 100644 --- a/lib/material-groups/table/material-group-table.tsx +++ b/lib/material-groups/table/material-group-table.tsx @@ -21,7 +21,6 @@ import { ViewModeToggle } from "@/components/data-table/view-mode-toggle" type MaterialGroup = { materialGroupCode: string | null; materialGroupDesc: string | null; - displayText: string | null; } interface MaterialGroupTableProps { @@ -67,11 +66,6 @@ export function MaterialGroupTable({ promises }: MaterialGroupTableProps) { label: "자재그룹명", type: "text", }, - { - id: "displayText", - label: "표시텍스트", - type: "text", - }, ] // 확장된 useDataTable 훅 사용 (pageSize 기반 자동 전환) |