diff options
Diffstat (limited to 'components/form-data-plant/import-excel-form.tsx')
| -rw-r--r-- | components/form-data-plant/import-excel-form.tsx | 669 |
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 |