diff options
Diffstat (limited to 'components/form-data/import-excel-form.tsx')
| -rw-r--r-- | components/form-data/import-excel-form.tsx | 323 |
1 files changed, 323 insertions, 0 deletions
diff --git a/components/form-data/import-excel-form.tsx b/components/form-data/import-excel-form.tsx new file mode 100644 index 00000000..45e48312 --- /dev/null +++ b/components/form-data/import-excel-form.tsx @@ -0,0 +1,323 @@ +// lib/excelUtils.ts (continued) +import ExcelJS from "exceljs"; +import { saveAs } from "file-saver"; +import { toast } from "sonner"; +import { DataTableColumnJSON } from "./form-data-table-columns"; +import { updateFormDataInDB } from "@/lib/forms/services"; +// Assuming the previous types are defined above +export interface ImportExcelOptions { + file: File; + tableData: GenericData[]; + columnsJSON: DataTableColumnJSON[]; + formCode?: string; // Optional - provide to enable direct DB save + contractItemId?: number; // Optional - provide to enable direct DB save + onPendingChange?: (isPending: boolean) => void; + onDataUpdate?: (updater: ((prev: GenericData[]) => GenericData[]) | GenericData[]) => void; +} + +export interface ImportExcelResult { + success: boolean; + importedCount?: number; + error?: any; + message?: string; +} + +export interface ExportExcelOptions { + tableData: GenericData[]; + columnsJSON: DataTableColumnJSON[]; + formCode: string; + onPendingChange?: (isPending: boolean) => void; +} + +// For typing consistency +interface GenericData { + [key: string]: any; +} + +export async function importExcelData({ + file, + tableData, + columnsJSON, + formCode, + contractItemId, + onPendingChange, + onDataUpdate +}: ImportExcelOptions): Promise<ImportExcelResult> { + if (!file) return { success: false, error: "No file provided" }; + + try { + if (onPendingChange) onPendingChange(true); + + // Get existing tag numbers + const existingTagNumbers = new Set(tableData.map((d) => d.TAG_NO)); + + const workbook = new ExcelJS.Workbook(); + const arrayBuffer = await file.arrayBuffer(); + 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 + let headerErrorMessage = ""; + + // Check for missing required columns + columnsJSON.forEach((col) => { + const label = col.label; + if (!headerToIndexMap.has(label)) { + headerErrorMessage += `Column "${label}" is missing. `; + } + }); + + // Check for unexpected columns + headerToIndexMap.forEach((index, headerLabel) => { + const found = columnsJSON.some((col) => col.label === headerLabel); + if (!found) { + headerErrorMessage += `Unexpected column "${headerLabel}" found in Excel. `; + } + }); + + // 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(); + + const outBuffer = await workbook.xlsx.writeBuffer(); + saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`); + + toast.error(`Header mismatch found. Please check downloaded file.`); + return { success: false, error: "Header mismatch" }; + } + + // 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 lastRowNumber = worksheet.lastRow?.number || 1; + let errorCount = 0; + + // Process each data row + for (let rowNum = 2; rowNum <= lastRowNumber; rowNum++) { + const row = worksheet.getRow(rowNum); + const rowValues = row.values as ExcelJS.CellValue[]; + if (!rowValues || rowValues.length <= 1) continue; // Skip empty rows + + let errorMessage = ""; + const rowObj: Record<string, any> = {}; + + // Process each column + columnsJSON.forEach((col) => { + const colIndex = keyToIndexMap.get(col.key); + if (colIndex === undefined) return; + + const cellValue = rowValues[colIndex] ?? ""; + let stringVal = String(cellValue).trim(); + + // Type-specific validation + switch (col.type) { + case "STRING": + if (!stringVal && col.key === "TAG_NO") { + errorMessage += `[${col.label}] is empty. `; + } + rowObj[col.key] = stringVal; + break; + + case "NUMBER": + if (stringVal) { + const num = parseFloat(stringVal); + if (isNaN(num)) { + errorMessage += `[${col.label}] '${stringVal}' is not a valid number. `; + } else { + rowObj[col.key] = num; + } + } else { + rowObj[col.key] = null; + } + break; + + case "LIST": + if ( + stringVal && + col.options && + !col.options.includes(stringVal) + ) { + errorMessage += `[${ + col.label + }] '${stringVal}' not in ${col.options.join(", ")}. `; + } + rowObj[col.key] = stringVal; + break; + + default: + rowObj[col.key] = stringVal; + break; + } + }); + + // 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 { + importedData.push(rowObj); + } + } + + // If there are validation errors, download error report and exit + if (errorCount > 0) { + const outBuffer = await workbook.xlsx.writeBuffer(); + saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`); + toast.error( + `There are ${errorCount} error row(s). Please check downloaded file.` + ); + return { success: false, error: "Data validation errors" }; + } + + // 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 + if (formCode && contractItemId) { + try { + // Process each imported row individually + let successCount = 0; + let errorCount = 0; + const errors = []; + + // Since updateFormDataInDB expects a single row at a time, + // we need to process each imported row individually + for (const importedRow of importedData) { + try { + const result = await updateFormDataInDB( + formCode, + contractItemId, + importedRow + ); + + if (result.success) { + successCount++; + } else { + errorCount++; + errors.push(`Error updating tag ${importedRow.TAG_NO}: ${result.message}`); + } + } catch (rowError) { + errorCount++; + errors.push(`Exception updating tag ${importedRow.TAG_NO}: ${rowError instanceof Error ? rowError.message : 'Unknown error'}`); + } + } + + // If any errors occurred + if (errorCount > 0) { + console.error("Errors during import:", errors); + + if (successCount > 0) { + toast.warning(`Partially successful: ${successCount} rows updated, ${errorCount} errors`); + } else { + toast.error(`Failed to update all ${errorCount} rows`); + } + + // If some rows were updated successfully, update the local state + if (successCount > 0) { + if (onDataUpdate) { + onDataUpdate(() => mergedData); + } + return { + success: true, + importedCount: successCount, + message: `Partially successful: ${successCount} rows updated, ${errorCount} errors` + }; + } else { + return { + success: false, + error: "All updates failed", + message: errors.join("\n") + }; + } + } + + // All rows were updated successfully + if (onDataUpdate) { + onDataUpdate(() => mergedData); + } + + toast.success(`Successfully updated ${successCount} rows`); + return { + success: true, + importedCount: successCount, + message: "All data imported and saved to database" + }; + } catch (saveError) { + console.error("Failed to save imported data:", saveError); + toast.error("Failed to save imported data to database"); + return { success: false, error: saveError }; + } + } else { + // Fall back to just updating local state if DB parameters aren't provided + if (onDataUpdate) { + onDataUpdate(() => mergedData); + } + + toast.success(`Imported ${importedData.length} rows successfully (local only)`); + return { success: true, importedCount: importedData.length }; + } + + } catch (err) { + console.error("Excel import error:", err); + toast.error("Excel import failed."); + return { success: false, error: err }; + } finally { + if (onPendingChange) onPendingChange(false); + } +}
\ No newline at end of file |