795 lines
23 KiB
JavaScript
795 lines
23 KiB
JavaScript
const XLSX = require('xlsx');
|
|
const database = require('../models/database');
|
|
const path = require('path');
|
|
const fs = require('fs').promises;
|
|
|
|
/**
|
|
* Service for exporting inventory data to Excel files
|
|
*/
|
|
class ExcelExportService {
|
|
constructor() {
|
|
this.exportDirectory = path.join(__dirname, '..', 'data', 'exports');
|
|
this.ensureExportDirectory();
|
|
}
|
|
|
|
/**
|
|
* Ensure export directory exists
|
|
*/
|
|
async ensureExportDirectory() {
|
|
try {
|
|
await fs.mkdir(this.exportDirectory, { recursive: true });
|
|
} catch (error) {
|
|
console.error('Failed to create export directory:', error);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Export inventory data to Excel format
|
|
* @param {Object} options - Export options
|
|
* @returns {Object} Export results with file path and metadata
|
|
*/
|
|
async exportInventoryToExcel(options = {}) {
|
|
try {
|
|
const {
|
|
format = 'xlsx',
|
|
includeHistory = false,
|
|
filters = {},
|
|
filename,
|
|
originalFileBuffer = null,
|
|
preserveFormatting = true
|
|
} = options;
|
|
|
|
// Get inventory data
|
|
const inventoryData = await this.getInventoryData(filters);
|
|
|
|
let workbook;
|
|
let exportResult;
|
|
|
|
if (originalFileBuffer && preserveFormatting) {
|
|
// Preserve original Excel structure and update data
|
|
exportResult = await this.updateOriginalExcelFile(originalFileBuffer, inventoryData, options);
|
|
} else {
|
|
// Create new Excel file with standard format
|
|
exportResult = await this.createNewExcelFile(inventoryData, options);
|
|
}
|
|
|
|
// Generate filename if not provided
|
|
const exportFilename = filename || this.generateExportFilename(format);
|
|
const exportPath = path.join(this.exportDirectory, exportFilename);
|
|
|
|
// Write file to disk
|
|
await this.writeExcelFile(exportResult.workbook, exportPath, format);
|
|
|
|
// Create export session record
|
|
const sessionId = await this.createExportSession({
|
|
filename: exportFilename,
|
|
totalRecords: inventoryData.length,
|
|
filters: filters,
|
|
includeHistory: includeHistory
|
|
});
|
|
|
|
return {
|
|
success: true,
|
|
filePath: exportPath,
|
|
filename: exportFilename,
|
|
sessionId: sessionId,
|
|
recordCount: inventoryData.length,
|
|
exportDate: new Date().toISOString(),
|
|
metadata: exportResult.metadata
|
|
};
|
|
|
|
} catch (error) {
|
|
return {
|
|
success: false,
|
|
error: error.message,
|
|
filePath: null
|
|
};
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get inventory data with optional filtering
|
|
* @param {Object} filters - Filter options
|
|
* @returns {Array} Array of inventory records with product information
|
|
*/
|
|
async getInventoryData(filters = {}) {
|
|
const db = database.getDatabase();
|
|
|
|
let query = `
|
|
SELECT
|
|
p.product_code,
|
|
p.description,
|
|
p.category,
|
|
p.unit_of_measure,
|
|
i.current_level,
|
|
i.minimum_level,
|
|
i.maximum_level,
|
|
i.last_updated,
|
|
i.updated_by,
|
|
p.created_at as product_created_at,
|
|
p.updated_at as product_updated_at,
|
|
CASE
|
|
WHEN i.current_level <= i.minimum_level THEN 'Low Stock'
|
|
WHEN i.current_level <= i.minimum_level * 1.5 THEN 'Warning'
|
|
ELSE 'Normal'
|
|
END as stock_status
|
|
FROM products p
|
|
LEFT JOIN inventory i ON p.id = i.product_id
|
|
`;
|
|
|
|
const params = [];
|
|
const conditions = [];
|
|
|
|
// Apply filters
|
|
if (filters.category) {
|
|
conditions.push('p.category = ?');
|
|
params.push(filters.category);
|
|
}
|
|
|
|
if (filters.stockStatus) {
|
|
switch (filters.stockStatus) {
|
|
case 'low':
|
|
conditions.push('i.current_level <= i.minimum_level');
|
|
break;
|
|
case 'warning':
|
|
conditions.push('i.current_level <= i.minimum_level * 1.5 AND i.current_level > i.minimum_level');
|
|
break;
|
|
case 'normal':
|
|
conditions.push('i.current_level > i.minimum_level * 1.5');
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (filters.updatedSince) {
|
|
conditions.push('i.last_updated >= ?');
|
|
params.push(filters.updatedSince);
|
|
}
|
|
|
|
if (filters.productCodes && filters.productCodes.length > 0) {
|
|
const placeholders = filters.productCodes.map(() => '?').join(',');
|
|
conditions.push(`p.product_code IN (${placeholders})`);
|
|
params.push(...filters.productCodes);
|
|
}
|
|
|
|
if (conditions.length > 0) {
|
|
query += ' WHERE ' + conditions.join(' AND ');
|
|
}
|
|
|
|
query += ' ORDER BY p.product_code';
|
|
|
|
const stmt = db.prepare(query);
|
|
return stmt.all(...params);
|
|
}
|
|
|
|
/**
|
|
* Update original Excel file with current inventory data
|
|
* @param {Buffer} originalFileBuffer - Original Excel file buffer
|
|
* @param {Array} inventoryData - Current inventory data
|
|
* @param {Object} options - Update options
|
|
* @returns {Object} Updated workbook and metadata
|
|
*/
|
|
async updateOriginalExcelFile(originalFileBuffer, inventoryData, options = {}) {
|
|
try {
|
|
// Read original workbook
|
|
const workbook = XLSX.read(originalFileBuffer, { type: 'buffer', cellStyles: true });
|
|
const sheetName = options.sheetName || workbook.SheetNames[0];
|
|
const worksheet = workbook.Sheets[sheetName];
|
|
|
|
if (!worksheet) {
|
|
throw new Error(`Sheet "${sheetName}" not found in original file`);
|
|
}
|
|
|
|
// Get original data structure
|
|
const originalData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: '' });
|
|
|
|
if (originalData.length === 0) {
|
|
throw new Error('Original Excel file appears to be empty');
|
|
}
|
|
|
|
// Detect column mappings from original file
|
|
const headerRow = originalData[0];
|
|
const columnMapping = this.detectColumnMappings(headerRow);
|
|
|
|
// Create lookup map for current inventory data
|
|
const inventoryMap = new Map();
|
|
inventoryData.forEach(item => {
|
|
inventoryMap.set(item.product_code, item);
|
|
});
|
|
|
|
// Update data rows while preserving structure
|
|
const updatedData = [...originalData];
|
|
let updatedCount = 0;
|
|
let addedCount = 0;
|
|
|
|
// Update existing rows
|
|
for (let i = 1; i < originalData.length; i++) {
|
|
const row = [...originalData[i]];
|
|
const productCode = this.getCellValue(row, columnMapping.productCode);
|
|
|
|
if (productCode && inventoryMap.has(productCode)) {
|
|
const inventoryItem = inventoryMap.get(productCode);
|
|
|
|
// Update quantity/current level
|
|
if (columnMapping.quantity !== null) {
|
|
row[columnMapping.quantity] = inventoryItem.current_level;
|
|
}
|
|
|
|
// Update description if it exists and is different
|
|
if (columnMapping.description !== null && inventoryItem.description) {
|
|
row[columnMapping.description] = inventoryItem.description;
|
|
}
|
|
|
|
// Update category if it exists
|
|
if (columnMapping.category !== null && inventoryItem.category) {
|
|
row[columnMapping.category] = inventoryItem.category;
|
|
}
|
|
|
|
// Add timestamp column if requested
|
|
if (options.includeTimestamp) {
|
|
if (columnMapping.lastUpdated === null) {
|
|
// Add new column for timestamp
|
|
if (i === 1) {
|
|
// Add header
|
|
updatedData[0].push('Last Updated');
|
|
columnMapping.lastUpdated = updatedData[0].length - 1;
|
|
}
|
|
row.push(inventoryItem.last_updated || '');
|
|
} else {
|
|
row[columnMapping.lastUpdated] = inventoryItem.last_updated || '';
|
|
}
|
|
}
|
|
|
|
updatedData[i] = row;
|
|
updatedCount++;
|
|
inventoryMap.delete(productCode); // Remove from map to track what's been processed
|
|
}
|
|
}
|
|
|
|
// Add new products that weren't in original file
|
|
if (options.includeNewProducts && inventoryMap.size > 0) {
|
|
inventoryMap.forEach(inventoryItem => {
|
|
const newRow = new Array(headerRow.length).fill('');
|
|
|
|
if (columnMapping.productCode !== null) {
|
|
newRow[columnMapping.productCode] = inventoryItem.product_code;
|
|
}
|
|
if (columnMapping.description !== null) {
|
|
newRow[columnMapping.description] = inventoryItem.description || '';
|
|
}
|
|
if (columnMapping.category !== null) {
|
|
newRow[columnMapping.category] = inventoryItem.category || '';
|
|
}
|
|
if (columnMapping.quantity !== null) {
|
|
newRow[columnMapping.quantity] = inventoryItem.current_level;
|
|
}
|
|
if (columnMapping.lastUpdated !== null) {
|
|
newRow[columnMapping.lastUpdated] = inventoryItem.last_updated || '';
|
|
}
|
|
|
|
updatedData.push(newRow);
|
|
addedCount++;
|
|
});
|
|
}
|
|
|
|
// Convert back to worksheet
|
|
const newWorksheet = XLSX.utils.aoa_to_sheet(updatedData);
|
|
|
|
// Preserve column widths and formatting where possible
|
|
if (worksheet['!cols']) {
|
|
newWorksheet['!cols'] = worksheet['!cols'];
|
|
}
|
|
if (worksheet['!rows']) {
|
|
newWorksheet['!rows'] = worksheet['!rows'];
|
|
}
|
|
|
|
// Replace the worksheet in workbook
|
|
workbook.Sheets[sheetName] = newWorksheet;
|
|
|
|
return {
|
|
workbook: workbook,
|
|
metadata: {
|
|
originalRows: originalData.length - 1,
|
|
updatedRows: updatedCount,
|
|
addedRows: addedCount,
|
|
preservedFormatting: true,
|
|
sheetName: sheetName
|
|
}
|
|
};
|
|
|
|
} catch (error) {
|
|
throw new Error(`Failed to update original Excel file: ${error.message}`);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Create new Excel file with inventory data
|
|
* @param {Array} inventoryData - Inventory data to export
|
|
* @param {Object} options - Export options
|
|
* @returns {Object} New workbook and metadata
|
|
*/
|
|
async createNewExcelFile(inventoryData, options = {}) {
|
|
const { includeHistory = false, includeAuditInfo = true } = options;
|
|
|
|
// Create main inventory sheet
|
|
const inventorySheet = this.createInventorySheet(inventoryData, includeAuditInfo);
|
|
|
|
// Create workbook
|
|
const workbook = XLSX.utils.book_new();
|
|
XLSX.utils.book_append_sheet(workbook, inventorySheet, 'Inventory');
|
|
|
|
// Add history sheet if requested
|
|
if (includeHistory) {
|
|
const historySheet = await this.createHistorySheet(inventoryData);
|
|
XLSX.utils.book_append_sheet(workbook, historySheet, 'History');
|
|
}
|
|
|
|
// Add summary sheet
|
|
const summarySheet = this.createSummarySheet(inventoryData);
|
|
XLSX.utils.book_append_sheet(workbook, summarySheet, 'Summary');
|
|
|
|
return {
|
|
workbook: workbook,
|
|
metadata: {
|
|
sheets: ['Inventory', ...(includeHistory ? ['History'] : []), 'Summary'],
|
|
recordCount: inventoryData.length,
|
|
includeHistory: includeHistory,
|
|
includeAuditInfo: includeAuditInfo
|
|
}
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Create inventory worksheet
|
|
* @param {Array} inventoryData - Inventory data
|
|
* @param {boolean} includeAuditInfo - Whether to include audit information
|
|
* @returns {Object} Worksheet object
|
|
*/
|
|
createInventorySheet(inventoryData, includeAuditInfo = true) {
|
|
const headers = [
|
|
'Product Code',
|
|
'Description',
|
|
'Category',
|
|
'Unit of Measure',
|
|
'Current Level',
|
|
'Minimum Level',
|
|
'Maximum Level',
|
|
'Stock Status'
|
|
];
|
|
|
|
if (includeAuditInfo) {
|
|
headers.push('Last Updated', 'Updated By');
|
|
}
|
|
|
|
// Create data rows
|
|
const rows = [headers];
|
|
|
|
inventoryData.forEach(item => {
|
|
const row = [
|
|
item.product_code || '',
|
|
item.description || '',
|
|
item.category || '',
|
|
item.unit_of_measure || '',
|
|
item.current_level || 0,
|
|
item.minimum_level || 0,
|
|
item.maximum_level || '',
|
|
item.stock_status || 'Normal'
|
|
];
|
|
|
|
if (includeAuditInfo) {
|
|
row.push(
|
|
item.last_updated ? new Date(item.last_updated).toLocaleString() : '',
|
|
item.updated_by || ''
|
|
);
|
|
}
|
|
|
|
rows.push(row);
|
|
});
|
|
|
|
// Create worksheet
|
|
const worksheet = XLSX.utils.aoa_to_sheet(rows);
|
|
|
|
// Set column widths
|
|
const columnWidths = [
|
|
{ wch: 15 }, // Product Code
|
|
{ wch: 30 }, // Description
|
|
{ wch: 15 }, // Category
|
|
{ wch: 12 }, // Unit of Measure
|
|
{ wch: 12 }, // Current Level
|
|
{ wch: 12 }, // Minimum Level
|
|
{ wch: 12 }, // Maximum Level
|
|
{ wch: 12 } // Stock Status
|
|
];
|
|
|
|
if (includeAuditInfo) {
|
|
columnWidths.push(
|
|
{ wch: 18 }, // Last Updated
|
|
{ wch: 15 } // Updated By
|
|
);
|
|
}
|
|
|
|
worksheet['!cols'] = columnWidths;
|
|
|
|
return worksheet;
|
|
}
|
|
|
|
/**
|
|
* Create history worksheet
|
|
* @param {Array} inventoryData - Inventory data for getting history
|
|
* @returns {Object} History worksheet
|
|
*/
|
|
async createHistorySheet(inventoryData) {
|
|
const db = database.getDatabase();
|
|
|
|
// Get product IDs for history lookup
|
|
const productCodes = inventoryData.map(item => item.product_code);
|
|
const placeholders = productCodes.map(() => '?').join(',');
|
|
|
|
const historyQuery = `
|
|
SELECT
|
|
p.product_code,
|
|
p.description,
|
|
h.old_level,
|
|
h.new_level,
|
|
h.change_reason,
|
|
h.updated_by,
|
|
h.updated_at
|
|
FROM inventory_history h
|
|
JOIN products p ON h.product_id = p.id
|
|
WHERE p.product_code IN (${placeholders})
|
|
ORDER BY h.updated_at DESC, p.product_code
|
|
LIMIT 1000
|
|
`;
|
|
|
|
const historyData = db.prepare(historyQuery).all(...productCodes);
|
|
|
|
const headers = [
|
|
'Product Code',
|
|
'Description',
|
|
'Old Level',
|
|
'New Level',
|
|
'Change Reason',
|
|
'Updated By',
|
|
'Updated At'
|
|
];
|
|
|
|
const rows = [headers];
|
|
|
|
historyData.forEach(record => {
|
|
rows.push([
|
|
record.product_code,
|
|
record.description || '',
|
|
record.old_level || 0,
|
|
record.new_level || 0,
|
|
record.change_reason || '',
|
|
record.updated_by || '',
|
|
new Date(record.updated_at).toLocaleString()
|
|
]);
|
|
});
|
|
|
|
const worksheet = XLSX.utils.aoa_to_sheet(rows);
|
|
|
|
// Set column widths
|
|
worksheet['!cols'] = [
|
|
{ wch: 15 }, // Product Code
|
|
{ wch: 30 }, // Description
|
|
{ wch: 10 }, // Old Level
|
|
{ wch: 10 }, // New Level
|
|
{ wch: 20 }, // Change Reason
|
|
{ wch: 15 }, // Updated By
|
|
{ wch: 18 } // Updated At
|
|
];
|
|
|
|
return worksheet;
|
|
}
|
|
|
|
/**
|
|
* Create summary worksheet
|
|
* @param {Array} inventoryData - Inventory data for summary
|
|
* @returns {Object} Summary worksheet
|
|
*/
|
|
createSummarySheet(inventoryData) {
|
|
const summary = {
|
|
totalProducts: inventoryData.length,
|
|
lowStockItems: inventoryData.filter(item => item.stock_status === 'Low Stock').length,
|
|
warningItems: inventoryData.filter(item => item.stock_status === 'Warning').length,
|
|
normalItems: inventoryData.filter(item => item.stock_status === 'Normal').length,
|
|
totalInventoryValue: inventoryData.reduce((sum, item) => sum + (item.current_level || 0), 0),
|
|
categories: {}
|
|
};
|
|
|
|
// Calculate category breakdown
|
|
inventoryData.forEach(item => {
|
|
const category = item.category || 'Uncategorized';
|
|
if (!summary.categories[category]) {
|
|
summary.categories[category] = {
|
|
count: 0,
|
|
totalStock: 0,
|
|
lowStock: 0
|
|
};
|
|
}
|
|
summary.categories[category].count++;
|
|
summary.categories[category].totalStock += item.current_level || 0;
|
|
if (item.stock_status === 'Low Stock') {
|
|
summary.categories[category].lowStock++;
|
|
}
|
|
});
|
|
|
|
// Create summary data
|
|
const rows = [
|
|
['Inventory Export Summary'],
|
|
[''],
|
|
['Export Date', new Date().toLocaleString()],
|
|
['Total Products', summary.totalProducts],
|
|
['Total Inventory Items', summary.totalInventoryValue],
|
|
[''],
|
|
['Stock Status Breakdown'],
|
|
['Low Stock Items', summary.lowStockItems],
|
|
['Warning Items', summary.warningItems],
|
|
['Normal Stock Items', summary.normalItems],
|
|
[''],
|
|
['Category Breakdown'],
|
|
['Category', 'Product Count', 'Total Stock', 'Low Stock Items']
|
|
];
|
|
|
|
// Add category data
|
|
Object.entries(summary.categories).forEach(([category, data]) => {
|
|
rows.push([category, data.count, data.totalStock, data.lowStock]);
|
|
});
|
|
|
|
const worksheet = XLSX.utils.aoa_to_sheet(rows);
|
|
|
|
// Set column widths
|
|
worksheet['!cols'] = [
|
|
{ wch: 25 },
|
|
{ wch: 15 },
|
|
{ wch: 15 },
|
|
{ wch: 15 }
|
|
];
|
|
|
|
return worksheet;
|
|
}
|
|
|
|
/**
|
|
* Detect column mappings from header row
|
|
* @param {Array} headerRow - Header row from Excel
|
|
* @returns {Object} Column mapping object
|
|
*/
|
|
detectColumnMappings(headerRow) {
|
|
const mapping = {
|
|
productCode: null,
|
|
description: null,
|
|
quantity: null,
|
|
category: null,
|
|
lastUpdated: null
|
|
};
|
|
|
|
const patterns = {
|
|
productCode: ['product_code', 'productcode', 'product code', 'code', 'item_code', 'sku'],
|
|
description: ['description', 'desc', 'product_description', 'name', 'product_name'],
|
|
quantity: ['quantity', 'qty', 'current_quantity', 'inventory', 'stock', 'current_level'],
|
|
category: ['category', 'cat', 'product_category', 'type', 'group'],
|
|
lastUpdated: ['last_updated', 'lastupdated', 'updated', 'timestamp', 'date_updated']
|
|
};
|
|
|
|
const normalizedHeaders = headerRow.map(header =>
|
|
typeof header === 'string' ? header.toLowerCase().trim() : ''
|
|
);
|
|
|
|
Object.keys(patterns).forEach(columnType => {
|
|
const columnPatterns = patterns[columnType];
|
|
|
|
for (let i = 0; i < normalizedHeaders.length; i++) {
|
|
const header = normalizedHeaders[i];
|
|
|
|
if (columnPatterns.includes(header)) {
|
|
mapping[columnType] = i;
|
|
break;
|
|
}
|
|
|
|
const partialMatch = columnPatterns.find(pattern =>
|
|
header.includes(pattern) || pattern.includes(header)
|
|
);
|
|
|
|
if (partialMatch && mapping[columnType] === null) {
|
|
mapping[columnType] = i;
|
|
}
|
|
}
|
|
});
|
|
|
|
return mapping;
|
|
}
|
|
|
|
/**
|
|
* Get cell value safely
|
|
* @param {Array} row - Data row
|
|
* @param {number} columnIndex - Column index
|
|
* @returns {string} Cell value or empty string
|
|
*/
|
|
getCellValue(row, columnIndex) {
|
|
if (columnIndex === null || columnIndex >= row.length) {
|
|
return '';
|
|
}
|
|
|
|
const value = row[columnIndex];
|
|
if (value === null || value === undefined) {
|
|
return '';
|
|
}
|
|
return value.toString().trim();
|
|
}
|
|
|
|
/**
|
|
* Write Excel file to disk
|
|
* @param {Object} workbook - Excel workbook object
|
|
* @param {string} filePath - Output file path
|
|
* @param {string} format - File format (xlsx, xls, csv)
|
|
*/
|
|
async writeExcelFile(workbook, filePath, format = 'xlsx') {
|
|
try {
|
|
const writeOptions = {
|
|
bookType: format,
|
|
type: 'buffer'
|
|
};
|
|
|
|
const buffer = XLSX.write(workbook, writeOptions);
|
|
await fs.writeFile(filePath, buffer);
|
|
} catch (error) {
|
|
throw new Error(`Failed to write Excel file: ${error.message}`);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Generate export filename with timestamp
|
|
* @param {string} format - File format
|
|
* @returns {string} Generated filename
|
|
*/
|
|
generateExportFilename(format = 'xlsx') {
|
|
const timestamp = new Date().toISOString()
|
|
.replace(/[:.]/g, '-')
|
|
.replace('T', '_')
|
|
.substring(0, 19);
|
|
|
|
return `inventory_export_${timestamp}.${format}`;
|
|
}
|
|
|
|
/**
|
|
* Create export session record for tracking
|
|
* @param {Object} sessionData - Export session data
|
|
* @returns {number} Session ID
|
|
*/
|
|
async createExportSession(sessionData) {
|
|
try {
|
|
const db = database.getDatabase();
|
|
|
|
// Create export_sessions table if it doesn't exist
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS export_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
filename VARCHAR(255),
|
|
total_records INTEGER,
|
|
export_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
filters TEXT,
|
|
include_history BOOLEAN DEFAULT 0,
|
|
status VARCHAR(20) DEFAULT 'completed'
|
|
)
|
|
`);
|
|
|
|
const stmt = db.prepare(`
|
|
INSERT INTO export_sessions (filename, total_records, filters, include_history)
|
|
VALUES (?, ?, ?, ?)
|
|
`);
|
|
|
|
const result = stmt.run(
|
|
sessionData.filename,
|
|
sessionData.totalRecords,
|
|
JSON.stringify(sessionData.filters || {}),
|
|
sessionData.includeHistory ? 1 : 0
|
|
);
|
|
|
|
return result.lastInsertRowid;
|
|
} catch (error) {
|
|
console.error('Failed to create export session:', error);
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get export history
|
|
* @param {Object} options - Query options
|
|
* @returns {Array} Export history records
|
|
*/
|
|
async getExportHistory(options = {}) {
|
|
try {
|
|
const db = database.getDatabase();
|
|
const { limit = 50, offset = 0 } = options;
|
|
|
|
const stmt = db.prepare(`
|
|
SELECT * FROM export_sessions
|
|
ORDER BY export_date DESC
|
|
LIMIT ? OFFSET ?
|
|
`);
|
|
|
|
return stmt.all(limit, offset);
|
|
} catch (error) {
|
|
console.error('Failed to get export history:', error);
|
|
return [];
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Clean up old export files
|
|
* @param {number} maxAgeHours - Maximum age in hours
|
|
* @returns {Object} Cleanup results
|
|
*/
|
|
async cleanupOldExports(maxAgeHours = 24) {
|
|
try {
|
|
const files = await fs.readdir(this.exportDirectory);
|
|
const cutoffTime = Date.now() - (maxAgeHours * 60 * 60 * 1000);
|
|
let deletedCount = 0;
|
|
|
|
for (const file of files) {
|
|
const filePath = path.join(this.exportDirectory, file);
|
|
const stats = await fs.stat(filePath);
|
|
|
|
if (stats.mtime.getTime() < cutoffTime) {
|
|
await fs.unlink(filePath);
|
|
deletedCount++;
|
|
}
|
|
}
|
|
|
|
return {
|
|
success: true,
|
|
deletedCount: deletedCount,
|
|
message: `Cleaned up ${deletedCount} old export files`
|
|
};
|
|
} catch (error) {
|
|
return {
|
|
success: false,
|
|
error: error.message
|
|
};
|
|
}
|
|
}
|
|
/**
|
|
* Clear export history
|
|
* @returns {Object} Result with success status and deleted count
|
|
*/
|
|
async clearExportHistory() {
|
|
const db = database.getDatabase();
|
|
|
|
try {
|
|
// Get count before deletion
|
|
const countStmt = db.prepare('SELECT COUNT(*) as count FROM export_sessions');
|
|
const countResult = countStmt.get();
|
|
const deletedCount = countResult.count;
|
|
|
|
// Clear export sessions table
|
|
const deleteStmt = db.prepare('DELETE FROM export_sessions');
|
|
deleteStmt.run();
|
|
|
|
// Clean up export files directory
|
|
try {
|
|
const files = await fs.readdir(this.exportDirectory);
|
|
for (const file of files) {
|
|
if (file.endsWith('.xlsx') || file.endsWith('.xls')) {
|
|
await fs.unlink(path.join(this.exportDirectory, file));
|
|
}
|
|
}
|
|
} catch (error) {
|
|
console.warn('Warning: Could not clean up export files:', error.message);
|
|
}
|
|
|
|
return {
|
|
success: true,
|
|
deletedCount
|
|
};
|
|
|
|
} catch (error) {
|
|
console.error('Error clearing export history:', error);
|
|
return {
|
|
success: false,
|
|
error: error.message
|
|
};
|
|
}
|
|
}
|
|
}
|
|
|
|
module.exports = ExcelExportService; |