23 lines
1.5 KiB
JavaScript
23 lines
1.5 KiB
JavaScript
|
const express = require('express');
|
||
|
|
const router = express.Router();
|
||
|
|
const { getDb } = require('../db');
|
||
|
|
|
||
|
|
router.get('/', (req, res) => {
|
||
|
|
const db = getDb();
|
||
|
|
const tab = req.query.tab || 'overview';
|
||
|
|
|
||
|
|
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 entries = db.prepare('SELECT t.*, c.name as client_name FROM time_entries t LEFT JOIN clients c ON c.id = t.client_id ORDER BY t.date DESC LIMIT 10').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 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;
|
||
|
|
const clients = db.prepare('SELECT id, name FROM clients ORDER BY name').all();
|
||
|
|
const engagements = db.prepare('SELECT e.id, e.title, c.name as client_name FROM engagements e JOIN clients c ON c.id = e.client_id ORDER BY e.title').all();
|
||
|
|
|
||
|
|
res.render('finance', { tab, invoices, entries, clients, engagements, totalHours, billableHours, totalInvoiced, totalPaid, totalOutstanding });
|
||
|
|
});
|
||
|
|
|
||
|
|
module.exports = router;
|