summaryrefslogtreecommitdiff
path: root/components/form-data/import-excel-form.tsx
diff options
context:
space:
mode:
Diffstat (limited to 'components/form-data/import-excel-form.tsx')
-rw-r--r--components/form-data/import-excel-form.tsx323
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
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 05:08:21 +0000