99 lines
5.8 KiB
JavaScript
99 lines
5.8 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const { getDb } = require('../db');
|
|
const { generateAdvice } = require('../advice');
|
|
|
|
router.get('/', (req, res) => {
|
|
const db = getDb();
|
|
const tab = req.query.tab || 'overview';
|
|
|
|
const clients = db.prepare(`
|
|
SELECT c.*,
|
|
(SELECT COUNT(*) FROM engagements e WHERE e.client_id = c.id) as engagement_count,
|
|
(SELECT COUNT(*) FROM engagements e WHERE e.client_id = c.id AND e.status = 'in_progress') as active_engagements,
|
|
(SELECT MAX(a.created_at) FROM assessments a WHERE a.client_id = c.id) as last_assessment
|
|
FROM clients c ORDER BY c.updated_at DESC
|
|
`).all();
|
|
|
|
const stats = {
|
|
total: clients.length,
|
|
active: clients.filter(c => c.status === 'active').length,
|
|
leads: clients.filter(c => c.status === 'lead').length,
|
|
paused: clients.filter(c => c.status === 'paused').length,
|
|
archived: clients.filter(c => c.status === 'archived').length,
|
|
activeEngagements: clients.reduce((s, c) => s + c.active_engagements, 0),
|
|
totalEngagements: clients.reduce((s, c) => s + c.engagement_count, 0)
|
|
};
|
|
|
|
const totalDiagrams = db.prepare('SELECT COUNT(*) as cnt FROM data_diagrams').get().cnt;
|
|
const totalRacks = db.prepare('SELECT COUNT(*) as cnt FROM racks').get().cnt;
|
|
const totalRackDevices = db.prepare('SELECT COUNT(*) as cnt FROM rack_devices').get().cnt;
|
|
const totalNetworkDevices = db.prepare('SELECT COUNT(*) as cnt FROM network_devices').get().cnt;
|
|
|
|
const overdueTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks WHERE status IN (\'todo\',\'in_progress\') AND due_date < date(\'now\')').get().cnt;
|
|
const totalTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks').get().cnt;
|
|
const completedTasks = db.prepare('SELECT COUNT(*) as cnt FROM tasks WHERE status = \'done\'').get().cnt;
|
|
const assessmentsCount = db.prepare('SELECT COUNT(*) as cnt FROM assessments').get().cnt;
|
|
|
|
const recentEngagements = db.prepare(`
|
|
SELECT e.*, c.name as client_name FROM engagements e JOIN clients c ON c.id = e.client_id
|
|
WHERE e.status = 'in_progress' ORDER BY e.updated_at DESC LIMIT 8
|
|
`).all();
|
|
|
|
const allEngagements = db.prepare(`
|
|
SELECT e.*, c.name as client_name FROM engagements e JOIN clients c ON c.id = e.client_id ORDER BY e.updated_at DESC
|
|
`).all();
|
|
|
|
const recentAssessments = db.prepare(`
|
|
SELECT a.id, a.created_at, c.name as client_name FROM assessments a JOIN clients c ON c.id = a.client_id ORDER BY a.created_at DESC LIMIT 5
|
|
`).all();
|
|
|
|
const engagementByType = db.prepare('SELECT type, COUNT(*) as cnt FROM engagements GROUP BY type ORDER BY cnt DESC').all();
|
|
const engagementByStatus = db.prepare('SELECT status, COUNT(*) as cnt FROM engagements GROUP BY status ORDER BY cnt DESC').all();
|
|
const clientsByIndustry = db.prepare("SELECT industry, COUNT(*) as cnt FROM clients WHERE industry IS NOT NULL AND industry != '' GROUP BY industry ORDER BY cnt DESC LIMIT 6").all();
|
|
|
|
const allAdvice = [];
|
|
for (const client of clients) allAdvice.push(...generateAdvice(client.id));
|
|
const activeAdvice = allAdvice.filter(a => !a.dismissed).sort((a, b) => {
|
|
const prio = { critical: 0, high: 1, medium: 2, low: 3 };
|
|
return (prio[a.priority] || 99) - (prio[b.priority] || 99);
|
|
}).slice(0, 8);
|
|
|
|
// Finance
|
|
const timeEntries = db.prepare('SELECT t.*, c.name as client_name, e.title as engagement_title, u.username FROM time_entries t LEFT JOIN clients c ON c.id = t.client_id LEFT JOIN engagements e ON e.id = t.engagement_id LEFT JOIN users u ON u.id = t.user_id ORDER BY t.date DESC LIMIT 20').all();
|
|
const totalHours = db.prepare('SELECT COALESCE(SUM(hours),0) as h FROM time_entries').get().h;
|
|
const billableHours = db.prepare('SELECT COALESCE(SUM(hours),0) as h FROM time_entries WHERE billable=1').get().h;
|
|
const invoices = db.prepare('SELECT i.*, c.name as client_name FROM invoices i JOIN clients c ON c.id = i.client_id ORDER BY i.created_at DESC LIMIT 10').all();
|
|
const totalInvoiced = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status != 'draft'").get().t;
|
|
const totalPaid = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status='paid'").get().t;
|
|
const totalOutstanding = db.prepare("SELECT COALESCE(SUM(total),0) as t FROM invoices WHERE status IN ('sent','overdue')").get().t;
|
|
|
|
// Infrastructure
|
|
const racks = db.prepare('SELECT r.*, c.name as client_name, (SELECT COUNT(*) FROM rack_devices d WHERE d.rack_id = r.id) as device_count FROM racks r JOIN clients c ON c.id = r.client_id ORDER BY r.updated_at DESC LIMIT 6').all();
|
|
const netDevices = db.prepare('SELECT nd.*, c.name as client_name FROM network_devices nd JOIN clients c ON c.id = nd.client_id ORDER BY nd.updated_at DESC LIMIT 10').all();
|
|
const diagrams = db.prepare('SELECT d.*, c.name as client_name FROM data_diagrams d JOIN clients c ON c.id = d.client_id ORDER BY d.updated_at DESC LIMIT 6').all();
|
|
|
|
// Tasks
|
|
const allTasks = db.prepare(`
|
|
SELECT t.*, e.title as engagement_title, e.id as eid, c.name as client_name
|
|
FROM tasks t JOIN engagements e ON e.id = t.engagement_id JOIN clients c ON c.id = e.client_id
|
|
ORDER BY CASE t.status WHEN 'todo' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'done' THEN 2 WHEN 'cancelled' THEN 3 END,
|
|
CASE t.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END,
|
|
t.due_date ASC
|
|
`).all();
|
|
|
|
res.render('dashboard', {
|
|
tab, clients, stats,
|
|
totalDiagrams, totalRacks, totalRackDevices, totalNetworkDevices,
|
|
overdueTasks, totalTasks, completedTasks, assessmentsCount,
|
|
recentEngagements, allEngagements, recentAssessments,
|
|
engagementByType, engagementByStatus, clientsByIndustry,
|
|
activeAdvice,
|
|
timeEntries, totalHours, billableHours, invoices, totalInvoiced, totalPaid, totalOutstanding,
|
|
racks, netDevices, diagrams,
|
|
allTasks
|
|
});
|
|
});
|
|
|
|
module.exports = router;
|