(대표님) EDP 해양 관련 개발 사항들

1 files changed, 669 insertions, 0 deletions

diff --git a/components/form-data-plant/import-excel-form.tsx b/components/form-data-plant/import-excel-form.tsx
new file mode 100644
index 00000000..ffc6f2f9
--- /dev/null
+++ b/components/form-data-plant/import-excel-form.tsx
@@ -0,0 +1,669 @@
+import ExcelJS from "exceljs";
+import { saveAs } from "file-saver";
+import { toast } from "sonner";
+import { DataTableColumnJSON } from "./form-data-table-columns";
+import { updateFormDataBatchInDB } from "@/lib/forms-plant/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;
+}
+
+// Updated options interface with editableFieldsMap
+export interface ImportExcelOptions {
+  file: File;
+  tableData: GenericData[];
+  columnsJSON: DataTableColumnJSON[];
+  formCode?: string;
+  contractItemId?: number;
+  editableFieldsMap?: Map<string, string[]>; // 새로 추가
+  onPendingChange?: (isPending: boolean) => void;
+  onDataUpdate?: (updater: ((prev: GenericData[]) => GenericData[]) | GenericData[]) => void;
+}
+
+export interface ImportExcelResult {
+  success: boolean;
+  importedCount?: number;
+  error?: any;
+  message?: string;
+  skippedFields?: { tagNo: string, fields: string[] }[]; // 건너뛴 필드 정보
+  errorCount?: number;
+  hasErrors?: boolean;
+  notFoundTags?: string[];
+}
+
+export interface ExportExcelOptions {
+  tableData: GenericData[];
+  columnsJSON: DataTableColumnJSON[];
+  formCode: string;
+  editableFieldsMap?: Map<string, string[]>; // 새로 추가
+  onPendingChange?: (isPending: boolean) => void;
+}
+
+interface GenericData {
+  [key: string]: any;
+}
+
+/**
+ * Check if a field is editable for a specific TAG_NO
+ */
+function isFieldEditable(
+  column: DataTableColumnJSON,
+  tagNo: string,
+  editableFieldsMap: Map<string, string[]>
+): boolean {
+  // SHI-only fields (shi === "OUT" or shi === null) are never editable
+  if (column.shi === "OUT" || column.shi === null) return false;
+
+  // System fields are never editable
+  if (column.key === "TAG_NO" || column.key === "TAG_DESC" || column.key === "status") return false;
+
+  // If no editableFieldsMap provided, assume all non-SHI fields are editable
+  if (!editableFieldsMap || editableFieldsMap.size === 0) return true;
+
+  // If TAG_NO not in map, no fields are editable
+  if (!editableFieldsMap.has(tagNo)) return false;
+
+  // Check if this field is in the editable fields list for this TAG_NO
+  const editableFields = editableFieldsMap.get(tagNo) || [];
+  return editableFields.includes(column.key);
+}
+
+/**
+ * Create error sheet with import validation results
+ */
+function createImportErrorSheet(workbook: ExcelJS.Workbook, errors: ImportError[], headerErrors?: string[]) {
+
+  const existingErrorSheet = workbook.getWorksheet("Import_Errors");
+  if (existingErrorSheet) {
+    workbook.removeWorksheet("Import_Errors");
+  }
+
+  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;
+          case "READ_ONLY_FIELD":
+            bgColor = "FFF0F0F0"; // Light gray
+            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,
+  columnsJSON,
+  formCode,
+  contractItemId,
+  editableFieldsMap = new Map(), // 새로 추가
+  onPendingChange,
+  onDataUpdate
+}: ImportExcelOptions): Promise<ImportExcelResult> {
+  if (!file) return { success: false, error: "No file provided" };
+
+  try {
+    if (onPendingChange) onPendingChange(true);
+
+    // Get existing tag numbers and create a map for quick lookup
+    const existingTagNumbers = new Set(tableData.map((d) => d.TAG_NO));
+    const existingDataMap = new Map<string, GenericData>();
+    tableData.forEach(item => {
+      if (item.TAG_NO) {
+        existingDataMap.set(item.TAG_NO, item);
+      }
+    });
+
+    const workbook = new ExcelJS.Workbook();
+    const arrayBuffer = await decryptWithServerAction(file);
+    await workbook.xlsx.load(arrayBuffer);
+
+    const worksheet = workbook.worksheets[0];
+
+    // Parse headers
+    const headerRow = worksheet.getRow(1);
+    const headerRowValues = headerRow.values as ExcelJS.CellValue[];
+
+    console.log("Original headers:", headerRowValues);
+
+    // Create mappings between Excel headers and column definitions
+    const headerToIndexMap = new Map<string, number>();
+    for (let i = 1; i < headerRowValues.length; i++) {
+      const headerValue = String(headerRowValues[i] || "").trim();
+      if (headerValue) {
+        headerToIndexMap.set(headerValue, i);
+      }
+    }
+
+    // Validate headers
+    const headerErrors: string[] = [];
+
+    // Check for missing required columns
+    columnsJSON.forEach((col) => {
+      const label = col.label;
+      if (!headerToIndexMap.has(label)) {
+        headerErrors.push(`Column "${label}" is missing from Excel file`);
+      }
+    });
+
+    // Check for unexpected columns
+    headerToIndexMap.forEach((index, headerLabel) => {
+      const found = columnsJSON.some((col) => col.label === headerLabel);
+      if (!found) {
+        headerErrors.push(`Unexpected column "${headerLabel}" found in Excel file`);
+      }
+    });
+
+    // 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-error-report_${Date.now()}.xlsx`);
+
+      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
+    const keyToIndexMap = new Map<string, number>();
+    columnsJSON.forEach((col) => {
+      const index = headerToIndexMap.get(col.label);
+      if (index !== undefined) {
+        keyToIndexMap.set(col.key, index);
+      }
+    });
+
+    // Parse and validate data rows
+    const importedData: GenericData[] = [];
+    const validationErrors: ImportError[] = [];
+    const lastRowNumber = worksheet.lastRow?.number || 1;
+    const skippedFieldsLog: { tagNo: string, fields: string[] }[] = []; // 건너뛴 필드 로그
+
+    // Process each data row
+    for (let rowNum = 2; rowNum <= lastRowNumber; rowNum++) {
+      const row = worksheet.getRow(rowNum);
+      const rowValues = row.values as ExcelJS.CellValue[];
+      // 실제 값이 있는지 확인 (빈 문자열이 아닌 실제 내용)
+      const hasAnyValue = rowValues && rowValues.slice(1).some(val =>
+        val !== undefined &&
+        val !== null &&
+        String(val).trim() !== ""
+      );
+
+      if (!hasAnyValue) {
+        console.log(`Row ${rowNum} is empty, skipping...`);
+        continue; // 완전히 빈 행은 건너뛰기
+      }
+
+      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);
+
+      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);
+        if (colIndex === undefined) return;
+
+        // Check if this field is editable for this TAG_NO
+        const fieldEditable = isFieldEditable(col, tagNo, editableFieldsMap);
+
+        if (!fieldEditable) {
+          // If field is not editable, preserve existing value
+          if (existingRowData && existingRowData[col.key] !== undefined) {
+            rowObj[col.key] = existingRowData[col.key];
+          } else {
+            // If no existing data, use appropriate default
+            switch (col.type) {
+              case "NUMBER":
+                rowObj[col.key] = null;
+                break;
+              case "STRING":
+              case "LIST":
+              default:
+                rowObj[col.key] = "";
+                break;
+            }
+          }
+
+          // Determine skip reason
+          let skipReason = "";
+          if (col.shi === "OUT" || col.shi === null) {
+            skipReason = "SHI-only field";
+          } else if (col.key === "TAG_NO" || col.key === "TAG_DESC" || col.key === "status") {
+            skipReason = "System field";
+          } else {
+            skipReason = "Not editable for this TAG";
+          }
+
+          // Log skipped field
+          skippedFields.push(`${col.label} (${skipReason})`);
+
+          // Check if Excel contains a value for a read-only field and warn
+          const cellValue = rowValues[colIndex] ?? "";
+          const stringVal = String(cellValue).trim();
+          if (stringVal && existingRowData && String(existingRowData[col.key] || "").trim() !== stringVal) {
+            validationErrors.push({
+              tagNo: tagNo || `Row-${rowNum}`,
+              rowIndex: rowNum,
+              columnKey: col.key,
+              columnLabel: col.label,
+              errorType: "READ_ONLY_FIELD",
+              errorMessage: `Attempting to modify read-only field. ${skipReason}.`,
+              currentValue: stringVal,
+              expectedFormat: `Field is read-only. Current value: ${existingRowData[col.key] || "empty"}`,
+            });
+            hasErrors = true;
+          }
+
+          return; // Skip processing Excel value for this column
+        }
+
+        // Process Excel value for editable fields
+        const cellValue = rowValues[colIndex] ?? "";
+        let stringVal = String(cellValue).trim();
+
+        // Type-specific validation
+        switch (col.type) {
+          case "STRING":
+            rowObj[col.key] = stringVal;
+            break;
+
+          case "NUMBER":
+            if (stringVal) {
+              const num = parseFloat(stringVal);
+              if (isNaN(num)) {
+                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;
+              }
+            } else {
+              rowObj[col.key] = null;
+            }
+            break;
+
+          case "LIST":
+            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;
+
+          default:
+            rowObj[col.key] = stringVal;
+            break;
+        }
+      });
+
+      // Log skipped fields for this TAG
+      if (skippedFields.length > 0) {
+        skippedFieldsLog.push({
+          tagNo: tagNo,
+          fields: skippedFields
+        });
+      }
+
+      // Add to valid data only if no errors
+      if (!hasErrors) {
+        importedData.push(rowObj);
+      }
+    }
+
+    // Show summary of skipped fields
+    if (skippedFieldsLog.length > 0) {
+      const totalSkippedFields = skippedFieldsLog.reduce((sum, log) => sum + log.fields.length, 0);
+      console.log("Skipped fields summary:", skippedFieldsLog);
+      toast.info(
+        `${totalSkippedFields} read-only fields were skipped across ${skippedFieldsLog.length} rows. Check console for details.`
+      );
+    }
+
+    // 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-error-report_${Date.now()}.xlsx`);
+
+      toast.error(
+        `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
+      };
+    }
+
+    // If we reached here, all data is valid
+    // Create locally merged data for UI update
+    const mergedData = [...tableData];
+    const dataMap = new Map<string, GenericData>();
+
+    // Map existing data by TAG_NO
+    mergedData.forEach(item => {
+      if (item.TAG_NO) {
+        dataMap.set(item.TAG_NO, item);
+      }
+    });
+
+    // Update with imported data
+    importedData.forEach(item => {
+      if (item.TAG_NO) {
+        const existingItem = dataMap.get(item.TAG_NO);
+        if (existingItem) {
+          // Update existing item with imported values
+          Object.assign(existingItem, item);
+        }
+      }
+    });
+
+    // If formCode and contractItemId are provided, save directly to DB
+    // importExcelData 함수에서 DB 저장 부분
+    if (formCode && contractItemId) {
+      try {
+        // 배치 업데이트 함수 호출
+        const result = await updateFormDataBatchInDB(
+          formCode,
+          contractItemId,
+          importedData // 모든 imported rows를 한번에 전달
+        );
+
+        if (result.success) {
+          // 로컬 상태 업데이트
+          if (onDataUpdate) {
+            onDataUpdate(() => mergedData);
+          }
+
+          // 성공 메시지 구성
+          const { updatedCount, notFoundTags } = result.data || {};
+
+          let message = `Successfully updated ${updatedCount || importedData.length} rows`;
+
+          // 건너뛴 필드가 있는 경우
+          if (skippedFieldsLog.length > 0) {
+            const totalSkippedFields = skippedFieldsLog.reduce((sum, log) => sum + log.fields.length, 0);
+            message += ` (${totalSkippedFields} read-only fields preserved)`;
+          }
+
+          // 찾을 수 없는 TAG가 있는 경우
+          if (notFoundTags && notFoundTags.length > 0) {
+            console.warn("Tags not found in database:", notFoundTags);
+            message += `. Warning: ${notFoundTags.length} tags not found in database`;
+          }
+
+          toast.success(message);
+
+          return {
+            success: true,
+            importedCount: updatedCount || importedData.length,
+            message: message,
+            errorCount: 0,
+            hasErrors: false,
+            skippedFields: skippedFieldsLog,
+            notFoundTags: notFoundTags
+          };
+
+        } else {
+          // 배치 업데이트 실패
+          console.error("Batch update failed:", result.message);
+
+          // 부분 성공인 경우
+          if (result.data?.updatedCount > 0) {
+            // 부분적으로라도 업데이트된 경우 로컬 상태 업데이트
+            if (onDataUpdate) {
+              onDataUpdate(() => mergedData);
+            }
+
+            toast.warning(
+              `Partially updated: ${result.data.updatedCount} of ${importedData.length} rows updated. ` +
+              `${result.data.failedCount || 0} failed.`
+            );
+
+            return {
+              success: true, // 부분 성공도 success로 처리
+              importedCount: result.data.updatedCount,
+              message: result.message,
+              errorCount: result.data.failedCount || 0,
+              hasErrors: true,
+              skippedFields: skippedFieldsLog
+            };
+
+          } else {
+            // 완전 실패
+            toast.error(result.message || "Failed to update data to database");
+
+            return {
+              success: false,
+              error: result.message,
+              errorCount: importedData.length,
+              hasErrors: true,
+              skippedFields: skippedFieldsLog
+            };
+          }
+        }
+
+      } catch (saveError) {
+        // 예외 발생 처리
+        console.error("Failed to save imported data:", saveError);
+
+        const errorMessage = saveError instanceof Error
+          ? saveError.message
+          : "Unknown error occurred";
+
+        toast.error(`Database update failed: ${errorMessage}`);
+
+        return {
+          success: false,
+          error: saveError,
+          message: errorMessage,
+          errorCount: importedData.length,
+          hasErrors: true,
+          skippedFields: skippedFieldsLog
+        };
+      }
+
+    } else {
+      // formCode나 contractItemId가 없는 경우 - 로컬 업데이트만
+      if (onDataUpdate) {
+        onDataUpdate(() => mergedData);
+      }
+
+      const successMessage = skippedFieldsLog.length > 0
+        ? `Imported ${importedData.length} rows successfully (read-only fields preserved)`
+        : `Imported ${importedData.length} rows successfully`;
+
+      toast.success(`${successMessage} (local only - no database connection)`);
+
+      return {
+        success: true,
+        importedCount: importedData.length,
+        message: "Data imported locally only",
+        errorCount: 0,
+        hasErrors: false,
+        skippedFields: skippedFieldsLog
+      };
+    }
+
+  } catch (err) {
+    console.error("Excel import error:", err);
+    toast.error("Excel import failed.");
+    return {
+      success: false,
+      error: err,
+      errorCount: 1,
+      hasErrors: true
+    };
+  } finally {
+    if (onPendingChange) onPendingChange(false);
+  }
+}
\ No newline at end of file