From 37611339fea096e47aaa42311a13a6313b4200db Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Mon, 2 Jun 2025 02:27:28 +0000
Subject: (대표님) 20250602 오전 작업사항 (코드프리징)
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 components/form-data/import-excel-form.tsx | 284 +++++++++++++++++++++++------
 1 file changed, 233 insertions(+), 51 deletions(-)

(limited to 'components/form-data/import-excel-form.tsx')

diff --git a/components/form-data/import-excel-form.tsx b/components/form-data/import-excel-form.tsx
index 6f0828b0..82c7afc8 100644
--- a/components/form-data/import-excel-form.tsx
+++ b/components/form-data/import-excel-form.tsx
@@ -5,6 +5,18 @@ import { DataTableColumnJSON } from "./form-data-table-columns";
 import { updateFormDataInDB } from "@/lib/forms/services";
 import { decryptWithServerAction } from "../drm/drmUtils";
 
+// Define error structure for import
+export interface ImportError {
+  tagNo: string;
+  rowIndex: number;
+  columnKey: string;
+  columnLabel: string;
+  errorType: string;
+  errorMessage: string;
+  currentValue?: any;
+  expectedFormat?: string;
+}
+
 // Simplified options interface without editableFieldsMap
 export interface ImportExcelOptions {
   file: File;
@@ -22,6 +34,8 @@ export interface ImportExcelResult {
   error?: any;
   message?: string;
   skippedFields?: { tagNo: string, fields: string[] }[]; // 건너뛴 필드 정보
+  errorCount?: number;
+  hasErrors?: boolean;
 }
 
 export interface ExportExcelOptions {
@@ -35,6 +49,133 @@ interface GenericData {
   [key: string]: any;
 }
 
+/**
+ * Create error sheet with import validation results
+ */
+function createImportErrorSheet(workbook: ExcelJS.Workbook, errors: ImportError[], headerErrors?: string[]) {
+  const errorSheet = workbook.addWorksheet("Import_Errors");
+
+  // Add header error section if exists
+  if (headerErrors && headerErrors.length > 0) {
+    errorSheet.addRow(["HEADER VALIDATION ERRORS"]);
+    const headerErrorTitleRow = errorSheet.getRow(1);
+    headerErrorTitleRow.font = { bold: true, size: 14, color: { argb: "FFFFFFFF" } };
+    headerErrorTitleRow.getCell(1).fill = {
+      type: "pattern",
+      pattern: "solid",
+      fgColor: { argb: "FFDC143C" },
+    };
+
+    headerErrors.forEach((error, index) => {
+      const errorRow = errorSheet.addRow([`${index + 1}. ${error}`]);
+      errorRow.getCell(1).fill = {
+        type: "pattern",
+        pattern: "solid",
+        fgColor: { argb: "FFFFCCCC" },
+      };
+    });
+
+    errorSheet.addRow([]); // Empty row for separation
+  }
+
+  // Data validation errors section
+  const startRow = errorSheet.rowCount + 1;
+  
+  // Summary row
+  errorSheet.addRow([`DATA VALIDATION ERRORS: ${errors.length} errors found`]);
+  const summaryRow = errorSheet.getRow(startRow);
+  summaryRow.font = { bold: true, size: 12 };
+  if (errors.length > 0) {
+    summaryRow.getCell(1).fill = {
+      type: "pattern",
+      pattern: "solid",
+      fgColor: { argb: "FFFFC0C0" },
+    };
+  }
+
+  if (errors.length > 0) {
+    // Error data headers
+    const errorHeaders = [
+      "TAG NO",
+      "Row Number",
+      "Column",
+      "Error Type",
+      "Error Message",
+      "Current Value",
+      "Expected Format",
+    ];
+
+    errorSheet.addRow(errorHeaders);
+    const headerRow = errorSheet.getRow(errorSheet.rowCount);
+    headerRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
+    headerRow.alignment = { horizontal: "center" };
+    
+    headerRow.eachCell((cell) => {
+      cell.fill = {
+        type: "pattern",
+        pattern: "solid",
+        fgColor: { argb: "FFDC143C" },
+      };
+    });
+
+    // Add error data
+    errors.forEach((error) => {
+      const errorRow = errorSheet.addRow([
+        error.tagNo,
+        error.rowIndex,
+        error.columnLabel,
+        error.errorType,
+        error.errorMessage,
+        error.currentValue || "",
+        error.expectedFormat || "",
+      ]);
+
+      // Color code by error type
+      errorRow.eachCell((cell) => {
+        let bgColor = "FFFFFFFF"; // Default white
+        
+        switch (error.errorType) {
+          case "MISSING_TAG_NO":
+            bgColor = "FFFFCCCC"; // Light red
+            break;
+          case "TAG_NOT_FOUND":
+            bgColor = "FFFFDDDD"; // Very light red
+            break;
+          case "TYPE_MISMATCH":
+            bgColor = "FFFFEECC"; // Light orange
+            break;
+          case "INVALID_OPTION":
+            bgColor = "FFFFFFE0"; // Light yellow
+            break;
+          case "HEADER_MISMATCH":
+            bgColor = "FFFFE0E0"; // Very light red
+            break;
+        }
+
+        cell.fill = {
+          type: "pattern",
+          pattern: "solid",
+          fgColor: { argb: bgColor },
+        };
+      });
+    });
+  }
+
+  // Auto-fit columns
+  errorSheet.columns.forEach((column) => {
+    let maxLength = 0;
+    column.eachCell({ includeEmpty: false }, (cell) => {
+      const columnLength = String(cell.value).length;
+      if (columnLength > maxLength) {
+        maxLength = columnLength;
+      }
+    });
+    column.width = Math.min(Math.max(maxLength + 2, 10), 50);
+  });
+
+  return errorSheet;
+}
+
 export async function importExcelData({
   file,
   tableData,
@@ -80,13 +221,13 @@ export async function importExcelData({
     }
 
     // Validate headers
-    let headerErrorMessage = "";
+    const headerErrors: string[] = [];
 
     // Check for missing required columns
     columnsJSON.forEach((col) => {
       const label = col.label;
       if (!headerToIndexMap.has(label)) {
-        headerErrorMessage += `Column "${label}" is missing. `;
+        headerErrors.push(`Column "${label}" is missing from Excel file`);
       }
     });
 
@@ -94,23 +235,24 @@ export async function importExcelData({
     headerToIndexMap.forEach((index, headerLabel) => {
       const found = columnsJSON.some((col) => col.label === headerLabel);
       if (!found) {
-        headerErrorMessage += `Unexpected column "${headerLabel}" found in Excel. `;
+        headerErrors.push(`Unexpected column "${headerLabel}" found in Excel file`);
       }
     });
 
-    // Add error column
-    const lastColIndex = worksheet.columnCount + 1;
-    worksheet.getRow(1).getCell(lastColIndex).value = "Error";
-
-    // If header validation fails, download error report and exit
-    if (headerErrorMessage) {
-      headerRow.getCell(lastColIndex).value = headerErrorMessage.trim();
+    // If header validation fails, create error report and exit
+    if (headerErrors.length > 0) {
+      createImportErrorSheet(workbook, [], headerErrors);
 
       const outBuffer = await workbook.xlsx.writeBuffer();
-      saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`);
+      saveAs(new Blob([outBuffer]), `import-error-report_${Date.now()}.xlsx`);
 
-      toast.error(`Header mismatch found. Please check downloaded file.`);
-      return { success: false, error: "Header mismatch" };
+      toast.error(`Header validation failed. ${headerErrors.length} errors found. Check downloaded error report.`);
+      return { 
+        success: false, 
+        error: "Header validation errors",
+        errorCount: headerErrors.length,
+        hasErrors: true
+      };
     }
 
     // Create column key to Excel index mapping
@@ -124,8 +266,8 @@ export async function importExcelData({
 
     // Parse and validate data rows
     const importedData: GenericData[] = [];
+    const validationErrors: ImportError[] = [];
     const lastRowNumber = worksheet.lastRow?.number || 1;
-    let errorCount = 0;
     const skippedFieldsLog: { tagNo: string, fields: string[] }[] = []; // 건너뛴 필드 로그
 
     // Process each data row
@@ -134,16 +276,40 @@ export async function importExcelData({
       const rowValues = row.values as ExcelJS.CellValue[];
       if (!rowValues || rowValues.length <= 1) continue; // Skip empty rows
 
-      let errorMessage = "";
-      let warningMessage = "";
       const rowObj: Record<string, any> = {};
       const skippedFields: string[] = []; // 현재 행에서 건너뛴 필드들
+      let hasErrors = false;
       
       // Get the TAG_NO first to identify existing data
       const tagNoColIndex = keyToIndexMap.get("TAG_NO");
       const tagNo = tagNoColIndex ? String(rowValues[tagNoColIndex] ?? "").trim() : "";
       const existingRowData = existingDataMap.get(tagNo);
 
+      // Validate TAG_NO first
+      if (!tagNo) {
+        validationErrors.push({
+          tagNo: `Row-${rowNum}`,
+          rowIndex: rowNum,
+          columnKey: "TAG_NO",
+          columnLabel: "TAG NO",
+          errorType: "MISSING_TAG_NO",
+          errorMessage: "TAG_NO is empty or missing",
+          currentValue: tagNo,
+        });
+        hasErrors = true;
+      } else if (!existingTagNumbers.has(tagNo)) {
+        validationErrors.push({
+          tagNo: tagNo,
+          rowIndex: rowNum,
+          columnKey: "TAG_NO",
+          columnLabel: "TAG NO",
+          errorType: "TAG_NOT_FOUND",
+          errorMessage: "TAG_NO not found in current data",
+          currentValue: tagNo,
+        });
+        hasErrors = true;
+      }
+
       // Process each column
       columnsJSON.forEach((col) => {
         const colIndex = keyToIndexMap.get(col.key);
@@ -179,9 +345,6 @@ export async function importExcelData({
         // Type-specific validation
         switch (col.type) {
           case "STRING":
-            if (!stringVal && col.key === "TAG_NO") {
-              errorMessage += `[${col.label}] is empty. `;
-            }
             rowObj[col.key] = stringVal;
             break;
 
@@ -189,7 +352,17 @@ export async function importExcelData({
             if (stringVal) {
               const num = parseFloat(stringVal);
               if (isNaN(num)) {
-                errorMessage += `[${col.label}] '${stringVal}' is not a valid number. `;
+                validationErrors.push({
+                  tagNo: tagNo || `Row-${rowNum}`,
+                  rowIndex: rowNum,
+                  columnKey: col.key,
+                  columnLabel: col.label,
+                  errorType: "TYPE_MISMATCH",
+                  errorMessage: "Value is not a valid number",
+                  currentValue: stringVal,
+                  expectedFormat: "Number",
+                });
+                hasErrors = true;
               } else {
                 rowObj[col.key] = num;
               }
@@ -199,14 +372,18 @@ export async function importExcelData({
             break;
 
           case "LIST":
-            if (
-              stringVal &&
-              col.options &&
-              !col.options.includes(stringVal)
-            ) {
-              errorMessage += `[${
-                col.label
-              }] '${stringVal}' not in ${col.options.join(", ")}. `;
+            if (stringVal && col.options && !col.options.includes(stringVal)) {
+              validationErrors.push({
+                tagNo: tagNo || `Row-${rowNum}`,
+                rowIndex: rowNum,
+                columnKey: col.key,
+                columnLabel: col.label,
+                errorType: "INVALID_OPTION",
+                errorMessage: "Value is not in the allowed options list",
+                currentValue: stringVal,
+                expectedFormat: col.options.join(", "),
+              });
+              hasErrors = true;
             }
             rowObj[col.key] = stringVal;
             break;
@@ -223,26 +400,10 @@ export async function importExcelData({
           tagNo: tagNo,
           fields: skippedFields
         });
-        warningMessage += `Skipped ${skippedFields.length} SHI-only fields. `;
       }
 
-      // Validate TAG_NO
-      const tagNum = rowObj["TAG_NO"];
-      if (!tagNum) {
-        errorMessage += `No TAG_NO found. `;
-      } else if (!existingTagNumbers.has(tagNum)) {
-        errorMessage += `TagNumber '${tagNum}' is not in current data. `;
-      }
-
-      // Record errors or add to valid data
-      if (errorMessage) {
-        row.getCell(lastColIndex).value = errorMessage.trim();
-        errorCount++;
-      } else {
-        // Add warning message to Excel if there are skipped fields
-        if (warningMessage) {
-          row.getCell(lastColIndex).value = `WARNING: ${warningMessage.trim()}`;
-        }
+      // Add to valid data only if no errors
+      if (!hasErrors) {
         importedData.push(rowObj);
       }
     }
@@ -256,16 +417,22 @@ export async function importExcelData({
       );
     }
 
-    // If there are validation errors, download error report and exit
-    if (errorCount > 0) {
+    // If there are validation errors, create error report and exit
+    if (validationErrors.length > 0) {
+      createImportErrorSheet(workbook, validationErrors);
+
       const outBuffer = await workbook.xlsx.writeBuffer();
-      saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`);
+      saveAs(new Blob([outBuffer]), `import-error-report_${Date.now()}.xlsx`);
+      
       toast.error(
-        `There are ${errorCount} error row(s). Please check downloaded file.`
+        `Data validation failed. ${validationErrors.length} errors found across ${new Set(validationErrors.map(e => e.tagNo)).size} TAG(s). Check downloaded error report.`
       );
+      
       return { 
         success: false, 
         error: "Data validation errors",
+        errorCount: validationErrors.length,
+        hasErrors: true,
         skippedFields: skippedFieldsLog
       };
     }
@@ -342,6 +509,8 @@ export async function importExcelData({
               success: true, 
               importedCount: successCount,
               message: `Partially successful: ${successCount} rows updated, ${errorCount} errors`,
+              errorCount: errorCount,
+              hasErrors: errorCount > 0,
               skippedFields: skippedFieldsLog
             };
           } else {
@@ -349,6 +518,8 @@ export async function importExcelData({
               success: false, 
               error: "All updates failed", 
               message: errors.join("\n"),
+              errorCount: errorCount,
+              hasErrors: true,
               skippedFields: skippedFieldsLog
             };
           }
@@ -368,6 +539,8 @@ export async function importExcelData({
           success: true, 
           importedCount: successCount, 
           message: "All data imported and saved to database",
+          errorCount: 0,
+          hasErrors: false,
           skippedFields: skippedFieldsLog
         };
       } catch (saveError) {
@@ -376,6 +549,8 @@ export async function importExcelData({
         return { 
           success: false, 
           error: saveError,
+          errorCount: 1,
+          hasErrors: true,
           skippedFields: skippedFieldsLog
         };
       }
@@ -393,6 +568,8 @@ export async function importExcelData({
       return { 
         success: true, 
         importedCount: importedData.length,
+        errorCount: 0,
+        hasErrors: false,
         skippedFields: skippedFieldsLog
       };
     }
@@ -400,7 +577,12 @@ export async function importExcelData({
   } catch (err) {
     console.error("Excel import error:", err);
     toast.error("Excel import failed.");
-    return { success: false, error: err };
+    return { 
+      success: false, 
+      error: err,
+      errorCount: 1,
+      hasErrors: true
+    };
   } finally {
     if (onPendingChange) onPendingChange(false);
   }
-- 
cgit v1.2.3

