(김준회) EDP-Progress 프로젝트 목록 찾기 쿼리 오류 수정, 자재그룹 싱크 및 테이블 관련 오류 수정

1 files changed, 51 insertions, 38 deletions

diff --git a/lib/material-groups/sync-service.ts b/lib/material-groups/sync-service.ts
index eda5dba5..9bcdd8bf 100644
--- a/lib/material-groups/sync-service.ts
+++ b/lib/material-groups/sync-service.ts
@@ -32,44 +32,58 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
     console.log("📊 자재그룹 마스터 동기화 시작...");
 
     const result = await db.transaction(async (tx) => {
-      // 1단계: 기존 MDG 테이블에서 MATKL별 영어 설명 조회
+      // 1단계: MATKL별로 첫 번째 MATNR 선택 (DISTINCT ON 사용)
+      const matklWithMatnr = await tx.execute(sql`
+        SELECT DISTINCT ON (${MATERIAL_MASTER_PART_MATL.MATKL}) 
+          ${MATERIAL_MASTER_PART_MATL.MATKL} as "MATKL",
+          ${MATERIAL_MASTER_PART_MATL.MATNR} as "MATNR"
+        FROM ${MATERIAL_MASTER_PART_MATL}
+        WHERE ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL 
+          AND ${MATERIAL_MASTER_PART_MATL.MATKL} != ''
+        ORDER BY ${MATERIAL_MASTER_PART_MATL.MATKL}, ${MATERIAL_MASTER_PART_MATL.MATNR}
+      `);
+
+      console.log(`🔍 발견된 고유 자재그룹코드: ${matklWithMatnr.rowCount}개`);
+
+      if (matklWithMatnr.rowCount === 0) {
+        return {
+          totalProcessed: 0,
+          newRecords: 0,
+          updatedRecords: 0,
+          skippedRecords: 0
+        };
+      }
+
+      // 2단계: 선택된 MATNR들로 DESC 테이블에서 영어 설명 조회
+      const matnrList = matklWithMatnr.rows.map(item => item.MATNR as string);
+      
       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")
+          MAKTX: MATERIAL_MASTER_PART_MATL_DESC.MAKTX
         })
         .from(MATERIAL_MASTER_PART_MATL)
+        .innerJoin(
+          MATERIAL_MASTER_PART_MATL_DESC,
+          sql`${MATERIAL_MASTER_PART_MATL.MATNR} = ${MATERIAL_MASTER_PART_MATL_DESC.MATNR}`
+        )
         .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}개`);
+          ${MATERIAL_MASTER_PART_MATL.MATNR} IN (${sql.join(matnrList.map(matnr => sql`${matnr}`), sql`, `)})
+          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} != ''
+        `);
 
-      // 2단계: MAKTX가 있는 레코드만 필터링
-      const validData = materialGroupData.filter(item => 
-        item.MAKTX && item.MAKTX.trim() !== ''
-      );
+      console.log(`✅ 유효한 자재그룹 데이터: ${materialGroupData.length}개`);
 
-      console.log(`✅ 유효한 자재그룹 데이터: ${validData.length}개`);
+      const validData = materialGroupData;
 
       if (validData.length === 0) {
         return {
           totalProcessed: 0,
           newRecords: 0,
           updatedRecords: 0,
-          skippedRecords: materialGroupData.length
+          skippedRecords: matklWithMatnr.rowCount || 0
         };
       }
 
@@ -80,9 +94,13 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
 
       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));
+      // 4단계: 신규/업데이트 데이터 분류 (null 값 제외하고 타입 안전하게)
+      const filteredData = validData.filter((item): item is { MATKL: string; MAKTX: string } => 
+        !!item.MATKL && !!item.MAKTX
+      );
+
+      const newRecords = filteredData.filter(item => !existingMatkls.has(item.MATKL));
+      const updateRecords = filteredData.filter(item => existingMatkls.has(item.MATKL));
 
       let insertedCount = 0;
       let updatedCount = 0;
@@ -91,12 +109,7 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
       if (newRecords.length > 0) {
         await tx
           .insert(MATERIAL_GROUP_MASTER)
-          .values(
-            newRecords.map(item => ({
-              MATKL: item.MATKL,
-              MAKTX: item.MAKTX
-            }))
-          );
+          .values(newRecords);
         insertedCount = newRecords.length;
         console.log(`➕ 신규 삽입: ${insertedCount}개`);
       }
@@ -105,10 +118,10 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
       if (updateRecords.length > 0) {
         // PostgreSQL의 UPDATE ... FROM 구문 사용
         await tx.execute(sql`
-          UPDATE ${MATERIAL_GROUP_MASTER} 
+          UPDATE mdg."MATERIAL_GROUP_MASTER" 
           SET 
-            ${MATERIAL_GROUP_MASTER.MAKTX} = temp_data.maktx,
-            ${MATERIAL_GROUP_MASTER.updatedAt} = NOW()
+            "MAKTX" = temp_data.maktx,
+            "updated_at" = NOW()
           FROM (
             VALUES ${sql.join(
               updateRecords.map(item => 
@@ -117,7 +130,7 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
               sql`, `
             )}
           ) AS temp_data(matkl, maktx)
-          WHERE ${MATERIAL_GROUP_MASTER.MATKL} = temp_data.matkl
+          WHERE "MATKL" = temp_data.matkl
         `);
         updatedCount = updateRecords.length;
         console.log(`🔄 업데이트: ${updatedCount}개`);
@@ -127,7 +140,7 @@ export async function syncMaterialGroupMaster(): Promise<SyncResult> {
         totalProcessed: validData.length,
         newRecords: insertedCount,
         updatedRecords: updatedCount,
-        skippedRecords: materialGroupData.length - validData.length
+        skippedRecords: (matklWithMatnr.rowCount || 0) - validData.length
       };
     });