588 lines
21 KiB
JavaScript
588 lines
21 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const pool = require('../db');
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const crypto = require('crypto');
|
|
const { db } = require('../activityDb');
|
|
const XLSX = require('xlsx');
|
|
|
|
const ALGORITHM = 'aes-256-cbc';
|
|
const SECRET_KEY = crypto.createHash('sha256').update(process.env.CRYPTO_KEY || 'default_secret_key_12345').digest();
|
|
const IV_LENGTH = 16;
|
|
|
|
function encrypt(text) {
|
|
const iv = crypto.randomBytes(IV_LENGTH);
|
|
const cipher = crypto.createCipheriv(ALGORITHM, SECRET_KEY, iv);
|
|
let encrypted = cipher.update(text, 'utf8', 'hex');
|
|
encrypted += cipher.final('hex');
|
|
return iv.toString('hex') + ':' + encrypted;
|
|
}
|
|
|
|
function decrypt(text) {
|
|
const textParts = text.split(':');
|
|
const iv = Buffer.from(textParts.shift(), 'hex');
|
|
const encryptedText = Buffer.from(textParts.join(':'), 'hex');
|
|
const decipher = crypto.createDecipheriv(ALGORITHM, SECRET_KEY, iv);
|
|
let decrypted = decipher.update(encryptedText, 'hex', 'utf8');
|
|
decrypted += decipher.final('utf8');
|
|
return decrypted;
|
|
}
|
|
|
|
// Ensure SQLite table exists for phrases
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS frasi_cache (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
tipo TEXT,
|
|
testo TEXT
|
|
)
|
|
`);
|
|
|
|
// Seed function to import plain-text files into SQLite cache
|
|
function seedPhrases() {
|
|
try {
|
|
const countRow = db.prepare("SELECT COUNT(*) AS count FROM frasi_cache").get();
|
|
if (countRow.count === 0) {
|
|
console.log('[Phrases Seed] SQLite frasi_cache is empty. Seeding...');
|
|
|
|
const seedFile = (fileName, type) => {
|
|
const txtPath = path.join(__dirname, `../public/${fileName}`);
|
|
if (fs.existsSync(txtPath)) {
|
|
const text = fs.readFileSync(txtPath, 'utf8');
|
|
const lines = text.split('\n').map(line => line.trim()).filter(line => line.length > 0);
|
|
|
|
const insertStmt = db.prepare("INSERT INTO frasi_cache (tipo, testo) VALUES (?, ?)");
|
|
db.transaction(() => {
|
|
for (const line of lines) {
|
|
insertStmt.run(type, encrypt(line));
|
|
}
|
|
})();
|
|
console.log(`[Phrases Seed] Successfully seeded ${lines.length} encrypted ${type} phrases.`);
|
|
}
|
|
};
|
|
|
|
seedFile('demotivational.txt', 'demotivational');
|
|
seedFile('motivational.txt', 'motivational');
|
|
}
|
|
} catch (err) {
|
|
console.error('[Phrases Seed] Seeding failed:', err.message);
|
|
}
|
|
}
|
|
|
|
seedPhrases();
|
|
|
|
|
|
// GET /api/dashboard/stats — Dashboard statistics
|
|
router.get('/stats', async (req, res) => {
|
|
const activeAgentId = parseInt(req.headers['x-agent-id'], 10) || 1;
|
|
|
|
let previewLimit = 10;
|
|
try {
|
|
const row = db.prepare("SELECT preview_limit FROM agent_settings WHERE agent_id = ?").get(activeAgentId);
|
|
if (row) {
|
|
previewLimit = row.preview_limit;
|
|
}
|
|
} catch (err) {
|
|
console.error('Error reading agent_settings:', err.message);
|
|
}
|
|
|
|
try {
|
|
// All queries in parallel for speed
|
|
const [
|
|
byState,
|
|
byPriority,
|
|
byQueue,
|
|
todayCount,
|
|
weekCount,
|
|
totalOpen,
|
|
recentTickets,
|
|
escalated,
|
|
myOpenCount,
|
|
] = await Promise.all([
|
|
// Tickets by state (only open-ish states)
|
|
pool.query(
|
|
`SELECT ts.name AS state, tst.name AS state_type, COUNT(*) AS count
|
|
FROM ticket t
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
WHERE tst.name IN ('new', 'open', 'pending reminder', 'pending auto')
|
|
GROUP BY ts.name, tst.name
|
|
ORDER BY count DESC`
|
|
),
|
|
// Tickets by priority (open only)
|
|
pool.query(
|
|
`SELECT tp.name AS priority, tp.color, COUNT(*) AS count
|
|
FROM ticket t
|
|
JOIN ticket_priority tp ON t.ticket_priority_id = tp.id
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
WHERE tst.name IN ('new', 'open', 'pending reminder', 'pending auto')
|
|
GROUP BY tp.name, tp.color, tp.id
|
|
ORDER BY tp.id`
|
|
),
|
|
// Tickets by queue (open only, top 10)
|
|
pool.query(
|
|
`SELECT q.name AS queue, COUNT(*) AS count
|
|
FROM ticket t
|
|
JOIN queue q ON t.queue_id = q.id
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
WHERE tst.name IN ('new', 'open', 'pending reminder', 'pending auto')
|
|
GROUP BY q.name
|
|
ORDER BY count DESC
|
|
LIMIT 10`
|
|
),
|
|
// Created today
|
|
pool.query(
|
|
`SELECT COUNT(*) AS count FROM ticket
|
|
WHERE create_time >= CURRENT_DATE`
|
|
),
|
|
// Created this week
|
|
pool.query(
|
|
`SELECT COUNT(*) AS count FROM ticket
|
|
WHERE create_time >= date_trunc('week', CURRENT_DATE)`
|
|
),
|
|
// Total open
|
|
pool.query(
|
|
`SELECT COUNT(*) AS count
|
|
FROM ticket t
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
WHERE tst.name IN ('new', 'open', 'pending reminder', 'pending auto')`
|
|
),
|
|
// Custom most recent tickets based on agent settings
|
|
pool.query(
|
|
`SELECT t.id, t.tn, t.title, ts.name AS state_name,
|
|
tp.name AS priority_name, tp.color AS priority_color,
|
|
q.name AS queue_name, t.create_time
|
|
FROM ticket t
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_priority tp ON t.ticket_priority_id = tp.id
|
|
JOIN queue q ON t.queue_id = q.id
|
|
ORDER BY t.create_time DESC
|
|
LIMIT $1`,
|
|
[previewLimit]
|
|
),
|
|
// Escalated tickets
|
|
pool.query(
|
|
`SELECT COUNT(*) AS count FROM ticket
|
|
WHERE escalation_time > 0
|
|
AND escalation_time < EXTRACT(EPOCH FROM NOW())`
|
|
),
|
|
// My open tickets count
|
|
pool.query(
|
|
`SELECT COUNT(*) AS count
|
|
FROM ticket t
|
|
JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
WHERE tst.name IN ('new', 'open', 'pending reminder', 'pending auto')
|
|
AND t.user_id = $1`,
|
|
[activeAgentId]
|
|
),
|
|
]);
|
|
|
|
res.json({
|
|
by_state: byState.rows,
|
|
by_priority: byPriority.rows,
|
|
by_queue: byQueue.rows,
|
|
created_today: parseInt(todayCount.rows[0].count),
|
|
created_this_week: parseInt(weekCount.rows[0].count),
|
|
total_open: parseInt(totalOpen.rows[0].count),
|
|
recent_tickets: recentTickets.rows,
|
|
escalated: parseInt(escalated.rows[0].count),
|
|
total_my_open: parseInt(myOpenCount.rows[0].count),
|
|
preview_limit: previewLimit,
|
|
});
|
|
} catch (err) {
|
|
console.error('Error fetching dashboard stats:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// GET /api/dashboard/phrases — Get decrypted phrases from SQLite cache
|
|
router.get('/phrases', (req, res) => {
|
|
const { tipo = 'demotivational' } = req.query;
|
|
try {
|
|
const rows = db.prepare("SELECT testo FROM frasi_cache WHERE tipo = ?").all(tipo);
|
|
const decryptedPhrases = rows.map(row => {
|
|
try {
|
|
return decrypt(row.testo);
|
|
} catch (decErr) {
|
|
console.warn('[Decrypt Phrase] Failed to decrypt phrase:', decErr.message);
|
|
return null;
|
|
}
|
|
}).filter(Boolean);
|
|
|
|
res.json(decryptedPhrases);
|
|
} catch (err) {
|
|
console.error('Error fetching decrypted phrases:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// GET /api/dashboard/settings — Retrieve settings for the agent
|
|
router.get('/settings', (req, res) => {
|
|
const activeAgentId = parseInt(req.headers['x-agent-id'], 10) || 1;
|
|
try {
|
|
const row = db.prepare("SELECT preview_limit, tickets_per_page FROM agent_settings WHERE agent_id = ?").get(activeAgentId);
|
|
if (row) {
|
|
res.json(row);
|
|
} else {
|
|
res.json({ preview_limit: 10, tickets_per_page: 50 });
|
|
}
|
|
} catch (err) {
|
|
console.error('Error reading agent settings:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// GET /api/dashboard/chart-lines
|
|
router.get('/chart-lines', (req, res) => {
|
|
try {
|
|
const lines = db.prepare("SELECT * FROM dashboard_chart_lines ORDER BY is_default DESC, id ASC").all();
|
|
const formatted = lines.map(line => ({
|
|
...line,
|
|
statuses: JSON.parse(line.statuses || '[]'),
|
|
types: JSON.parse(line.types || '[]'),
|
|
queues: JSON.parse(line.queues || '[]'),
|
|
owners: JSON.parse(line.owners || '[]'),
|
|
responsibles: JSON.parse(line.responsibles || '[]')
|
|
}));
|
|
res.json(formatted);
|
|
} catch (err) {
|
|
console.error('Error fetching chart lines:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// POST /api/dashboard/chart-lines
|
|
router.post('/chart-lines', (req, res) => {
|
|
const { name, statuses, types, queues, owners, responsibles, color, is_visible, bypass_state_filter } = req.body;
|
|
if (!name) return res.status(400).json({ error: 'Name is required' });
|
|
|
|
try {
|
|
const info = db.prepare(`
|
|
INSERT INTO dashboard_chart_lines (name, statuses, types, queues, owners, responsibles, color, is_visible, is_default, bypass_state_filter)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?)
|
|
`).run(
|
|
name,
|
|
JSON.stringify(statuses || []),
|
|
JSON.stringify(types || []),
|
|
JSON.stringify(queues || []),
|
|
JSON.stringify(owners || []),
|
|
JSON.stringify(responsibles || []),
|
|
color || '#4f46e5',
|
|
is_visible !== undefined ? parseInt(is_visible, 10) : 1,
|
|
bypass_state_filter !== undefined ? parseInt(bypass_state_filter, 10) : 0
|
|
);
|
|
res.json({ success: true, id: info.lastInsertRowid });
|
|
} catch (err) {
|
|
console.error('Error saving chart line:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// PUT /api/dashboard/chart-lines/:id
|
|
router.put('/chart-lines/:id', (req, res) => {
|
|
const { id } = req.params;
|
|
const { name, statuses, types, queues, owners, responsibles, color, is_visible, bypass_state_filter } = req.body;
|
|
if (!name) return res.status(400).json({ error: 'Name is required' });
|
|
|
|
try {
|
|
const info = db.prepare(`
|
|
UPDATE dashboard_chart_lines
|
|
SET name = ?, statuses = ?, types = ?, queues = ?, owners = ?, responsibles = ?, color = ?, is_visible = ?, bypass_state_filter = ?
|
|
WHERE id = ?
|
|
`).run(
|
|
name,
|
|
JSON.stringify(statuses || []),
|
|
JSON.stringify(types || []),
|
|
JSON.stringify(queues || []),
|
|
JSON.stringify(owners || []),
|
|
JSON.stringify(responsibles || []),
|
|
color || '#4f46e5',
|
|
is_visible !== undefined ? parseInt(is_visible, 10) : 1,
|
|
bypass_state_filter !== undefined ? parseInt(bypass_state_filter, 10) : 0,
|
|
id
|
|
);
|
|
if (info.changes === 0) return res.status(404).json({ error: 'Line not found' });
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
console.error('Error updating chart line:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// DELETE /api/dashboard/chart-lines/:id
|
|
router.delete('/chart-lines/:id', (req, res) => {
|
|
const { id } = req.params;
|
|
try {
|
|
const line = db.prepare("SELECT is_default FROM dashboard_chart_lines WHERE id = ?").get(id);
|
|
if (!line) return res.status(404).json({ error: 'Line not found' });
|
|
if (line.is_default === 1) {
|
|
return res.status(400).json({ error: 'Cannot delete default line' });
|
|
}
|
|
|
|
db.prepare("DELETE FROM dashboard_chart_lines WHERE id = ?").run(id);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
console.error('Error deleting chart line:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// GET /api/dashboard/chart-data
|
|
router.get('/chart-data', async (req, res) => {
|
|
const { start_date, end_date } = req.query;
|
|
if (!start_date || !end_date) {
|
|
return res.status(400).json({ error: 'start_date and end_date are required' });
|
|
}
|
|
|
|
try {
|
|
// Only query data series that are configured as visible
|
|
const lines = db.prepare("SELECT * FROM dashboard_chart_lines WHERE is_visible = 1 ORDER BY is_default DESC, id ASC").all();
|
|
|
|
const dates = [];
|
|
let curr = new Date(start_date);
|
|
const endLimit = new Date(end_date);
|
|
while (curr <= endLimit) {
|
|
const y = curr.getFullYear();
|
|
const m = String(curr.getMonth() + 1).padStart(2, '0');
|
|
const d = String(curr.getDate()).padStart(2, '0');
|
|
dates.push(`${y}-${m}-${d}`);
|
|
curr.setDate(curr.getDate() + 1);
|
|
}
|
|
|
|
const linesData = await Promise.all(lines.map(async (line) => {
|
|
const conditions = [];
|
|
const params = [];
|
|
let paramIdx = 1;
|
|
|
|
conditions.push(`t.create_time >= $${paramIdx++}`);
|
|
params.push(start_date + ' 00:00:00');
|
|
conditions.push(`t.create_time <= $${paramIdx++}`);
|
|
params.push(end_date + ' 23:59:59');
|
|
|
|
const statuses = JSON.parse(line.statuses || '[]');
|
|
if (statuses.length > 0 && !line.bypass_state_filter) {
|
|
const placeholders = statuses.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.ticket_state_id IN (${placeholders})`);
|
|
params.push(...statuses.map(id => parseInt(id)));
|
|
}
|
|
|
|
const types = JSON.parse(line.types || '[]');
|
|
if (types.length > 0) {
|
|
const placeholders = types.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.type_id IN (${placeholders})`);
|
|
params.push(...types.map(id => parseInt(id)));
|
|
}
|
|
|
|
const queues = JSON.parse(line.queues || '[]');
|
|
if (queues.length > 0) {
|
|
const placeholders = queues.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.queue_id IN (${placeholders})`);
|
|
params.push(...queues.map(id => parseInt(id)));
|
|
}
|
|
|
|
const owners = JSON.parse(line.owners || '[]');
|
|
if (owners.length > 0) {
|
|
const placeholders = owners.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.user_id IN (${placeholders})`);
|
|
params.push(...owners.map(id => parseInt(id)));
|
|
}
|
|
|
|
const responsibles = JSON.parse(line.responsibles || '[]');
|
|
if (responsibles.length > 0) {
|
|
const placeholders = responsibles.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.responsible_user_id IN (${placeholders})`);
|
|
params.push(...responsibles.map(id => parseInt(id)));
|
|
}
|
|
|
|
const sql = `
|
|
SELECT CAST(t.create_time AS DATE) AS date_val, COUNT(*) AS count
|
|
FROM ticket t
|
|
WHERE ${conditions.join(' AND ')}
|
|
GROUP BY CAST(t.create_time AS DATE)
|
|
`;
|
|
|
|
const result = await pool.query(sql, params);
|
|
|
|
const countsByDate = {};
|
|
for (const row of result.rows) {
|
|
let dateStr = row.date_val;
|
|
if (dateStr instanceof Date) {
|
|
const y = dateStr.getFullYear();
|
|
const m = String(dateStr.getMonth() + 1).padStart(2, '0');
|
|
const d = String(dateStr.getDate()).padStart(2, '0');
|
|
dateStr = `${y}-${m}-${d}`;
|
|
} else if (typeof dateStr === 'string') {
|
|
dateStr = dateStr.split('T')[0];
|
|
}
|
|
countsByDate[dateStr] = parseInt(row.count) || 0;
|
|
}
|
|
|
|
const data = dates.map(d => countsByDate[d] || 0);
|
|
|
|
return {
|
|
id: line.id,
|
|
name: line.name,
|
|
color: line.color,
|
|
is_default: line.is_default,
|
|
data
|
|
};
|
|
}));
|
|
|
|
res.json({
|
|
labels: dates,
|
|
lines: linesData
|
|
});
|
|
} catch (err) {
|
|
console.error('Error generating chart data:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
// GET /api/dashboard/export-excel
|
|
router.get('/export-excel', async (req, res) => {
|
|
const { start_date, end_date, series_ids } = req.query;
|
|
if (!start_date || !end_date) {
|
|
return res.status(400).json({ error: 'start_date and end_date are required' });
|
|
}
|
|
|
|
try {
|
|
let lines = [];
|
|
if (series_ids) {
|
|
const ids = series_ids.split(',').map(id => parseInt(id, 10)).filter(id => !isNaN(id));
|
|
if (ids.length > 0) {
|
|
const placeholders = ids.map(() => '?').join(', ');
|
|
lines = db.prepare(`SELECT * FROM dashboard_chart_lines WHERE is_visible = 1 AND id IN (${placeholders}) ORDER BY is_default DESC, id ASC`).all(...ids);
|
|
}
|
|
} else {
|
|
lines = db.prepare("SELECT * FROM dashboard_chart_lines WHERE is_visible = 1 ORDER BY is_default DESC, id ASC").all();
|
|
}
|
|
|
|
const wb = XLSX.utils.book_new();
|
|
const allRows = [];
|
|
|
|
for (const line of lines) {
|
|
const conditions = [];
|
|
const params = [];
|
|
let paramIdx = 1;
|
|
|
|
conditions.push(`t.create_time >= $${paramIdx++}`);
|
|
params.push(start_date + ' 00:00:00');
|
|
conditions.push(`t.create_time <= $${paramIdx++}`);
|
|
params.push(end_date + ' 23:59:59');
|
|
|
|
const statuses = JSON.parse(line.statuses || '[]');
|
|
if (statuses.length > 0 && !line.bypass_state_filter) {
|
|
const placeholders = statuses.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.ticket_state_id IN (${placeholders})`);
|
|
params.push(...statuses.map(id => parseInt(id)));
|
|
}
|
|
|
|
const types = JSON.parse(line.types || '[]');
|
|
if (types.length > 0) {
|
|
const placeholders = types.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.type_id IN (${placeholders})`);
|
|
params.push(...types.map(id => parseInt(id)));
|
|
}
|
|
|
|
const queues = JSON.parse(line.queues || '[]');
|
|
if (queues.length > 0) {
|
|
const placeholders = queues.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.queue_id IN (${placeholders})`);
|
|
params.push(...queues.map(id => parseInt(id)));
|
|
}
|
|
|
|
const owners = JSON.parse(line.owners || '[]');
|
|
if (owners.length > 0) {
|
|
const placeholders = owners.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.user_id IN (${placeholders})`);
|
|
params.push(...owners.map(id => parseInt(id)));
|
|
}
|
|
|
|
const responsibles = JSON.parse(line.responsibles || '[]');
|
|
if (responsibles.length > 0) {
|
|
const placeholders = responsibles.map(() => `$${paramIdx++}`).join(', ');
|
|
conditions.push(`t.responsible_user_id IN (${placeholders})`);
|
|
params.push(...responsibles.map(id => parseInt(id)));
|
|
}
|
|
|
|
const sql = `
|
|
SELECT
|
|
t.id AS ticket_id,
|
|
t.tn,
|
|
t.title,
|
|
tt.name AS type_name,
|
|
ts.name AS state_name,
|
|
tst.name AS state_type_name,
|
|
q.name AS queue_name,
|
|
COALESCE(u.first_name || ' ' || u.last_name, u.login) AS owner_name,
|
|
COALESCE(ru.first_name || ' ' || ru.last_name, ru.login) AS responsible_name,
|
|
t.create_time,
|
|
t.change_time
|
|
FROM ticket t
|
|
LEFT JOIN ticket_type tt ON t.type_id = tt.id
|
|
LEFT JOIN ticket_state ts ON t.ticket_state_id = ts.id
|
|
LEFT JOIN ticket_state_type tst ON ts.type_id = tst.id
|
|
LEFT JOIN queue q ON t.queue_id = q.id
|
|
LEFT JOIN users u ON t.user_id = u.id
|
|
LEFT JOIN users ru ON t.responsible_user_id = ru.id
|
|
WHERE ${conditions.join(' AND ')}
|
|
ORDER BY t.create_time DESC
|
|
`;
|
|
|
|
const result = await pool.query(sql, params);
|
|
|
|
const rows = result.rows.map(t => {
|
|
const isClosed = t.state_type_name && (
|
|
t.state_type_name.toLowerCase().includes('closed') ||
|
|
t.state_name.toLowerCase().includes('closed') ||
|
|
t.state_name.toLowerCase().includes('chiuso') ||
|
|
t.state_name.toLowerCase().includes('chiusa')
|
|
);
|
|
const closureDate = isClosed ? t.change_time : '';
|
|
return {
|
|
'Origine della serie': line.name,
|
|
'ID Ticket': t.ticket_id,
|
|
'Numero (TN)': t.tn,
|
|
'Oggetto': t.title || '',
|
|
'Tipo': t.type_name || '',
|
|
'Stato': t.state_name || '',
|
|
'Coda': t.queue_name || '',
|
|
'Owner': t.owner_name || '',
|
|
'Responsabile': t.responsible_name || '',
|
|
'Data di Creazione': t.create_time,
|
|
'Data di Chiusura': closureDate
|
|
};
|
|
});
|
|
|
|
allRows.push(...rows);
|
|
|
|
const ws = XLSX.utils.json_to_sheet(rows);
|
|
const cleanName = line.name.replace(/[\\\/\?\*\:\[\]]/g, '').slice(0, 30);
|
|
XLSX.utils.book_append_sheet(wb, ws, cleanName || `Serie ${line.id}`);
|
|
}
|
|
|
|
if (allRows.length > 0) {
|
|
// Sort combined rows by Data di Creazione descending
|
|
allRows.sort((a, b) => new Date(b['Data di Creazione']) - new Date(a['Data di Creazione']));
|
|
const wsAll = XLSX.utils.json_to_sheet(allRows);
|
|
wb.SheetNames.unshift('Tutte le serie');
|
|
wb.Sheets['Tutte le serie'] = wsAll;
|
|
}
|
|
|
|
const buf = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
|
|
|
|
res.setHeader('Content-Disposition', `attachment; filename="Andamento_Ticket_${start_date}_${end_date}.xlsx"`);
|
|
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
res.send(buf);
|
|
} catch (err) {
|
|
console.error('Error exporting excel:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|