99 lines
5.4 KiB
JavaScript
99 lines
5.4 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const { getDb, logAudit } = require('../db');
|
|
const emailService = require('../email');
|
|
|
|
router.get('/', (req, res) => {
|
|
const db = getDb();
|
|
const entries = 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, t.created_at DESC
|
|
`).all();
|
|
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();
|
|
const users = db.prepare('SELECT id, username FROM users ORDER BY username').all();
|
|
const totalHours = entries.reduce((s, e) => s + e.hours, 0);
|
|
const billableHours = entries.filter(e => e.billable).reduce((s, e) => s + e.hours, 0);
|
|
res.render('time-list', { entries, clients, engagements, users, totalHours, billableHours, sent: req.query.sent === '1', error: req.query.error || null });
|
|
});
|
|
|
|
router.post('/new', (req, res) => {
|
|
const db = getDb();
|
|
const { client_id, engagement_id, date, hours, description, billable, hourly_rate } = req.body;
|
|
if (!date || !hours) return res.redirect('/time');
|
|
db.prepare('INSERT INTO time_entries (user_id, client_id, engagement_id, date, hours, description, billable, hourly_rate) VALUES (?,?,?,?,?,?,?,?)')
|
|
.run(req.session.userId, client_id || null, engagement_id || null, date, parseFloat(hours), description || '', billable === '1' ? 1 : 0, parseFloat(hourly_rate) || 150.0);
|
|
logAudit(req.session.userId, req.session.username, 'time_entry_created', 'time_entry', null, `${hours}u geregistreerd op ${date}`);
|
|
res.redirect('/time');
|
|
});
|
|
|
|
router.post('/:id/delete', (req, res) => {
|
|
const db = getDb();
|
|
db.prepare('DELETE FROM time_entries WHERE id = ?').run(req.params.id);
|
|
res.redirect('/time');
|
|
});
|
|
|
|
router.post('/create-invoice', (req, res) => {
|
|
const db = getDb();
|
|
const { client_id, date_from, date_to } = req.body;
|
|
if (!client_id) return res.redirect('/time');
|
|
const entries = db.prepare(`
|
|
SELECT t.*, e.title as engagement_title, c.name as client_name
|
|
FROM time_entries t
|
|
LEFT JOIN engagements e ON e.id = t.engagement_id
|
|
JOIN clients c ON c.id = t.client_id
|
|
WHERE t.client_id = ? AND t.billable = 1
|
|
AND (? IS NULL OR t.date >= ?) AND (? IS NULL OR t.date <= ?)
|
|
`).all(client_id, date_from || null, date_from || null, date_to || null, date_to || null);
|
|
if (entries.length === 0) return res.redirect('/time?error=nobillable');
|
|
const totalHours = entries.reduce((s, e) => s + e.hours, 0);
|
|
const rate = entries[0].hourly_rate || 150;
|
|
const subtotal = totalHours * rate;
|
|
const tax = subtotal * 0.21;
|
|
const count = db.prepare('SELECT COUNT(*) as cnt FROM invoices').get().cnt + 1;
|
|
const number = `FACT-${new Date().getFullYear()}-${String(count).padStart(4, '0')}`;
|
|
const date = new Date().toISOString().split('T')[0];
|
|
const result = db.prepare('INSERT INTO invoices (client_id, number, status, date, due_date, subtotal, tax, total, notes) VALUES (?,?,?,?,?,?,?,?,?)')
|
|
.run(client_id, number, 'draft', date, '', subtotal, tax, subtotal + tax, `Factuur gegenereerd uit ${entries.length} tijdregistraties`);
|
|
const invoiceId = result.lastInsertRowid;
|
|
const insert = db.prepare('INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total) VALUES (?,?,?,?,?)');
|
|
entries.forEach(e => {
|
|
const lineTotal = e.hours * (e.hourly_rate || rate);
|
|
insert.run(invoiceId, `${e.date} - ${e.engagement_title || 'Werkzaamheden'}: ${e.description || ''}`, e.hours, e.hourly_rate || rate, lineTotal);
|
|
});
|
|
logAudit(req.session.userId, req.session.username, 'invoice_from_time', 'invoice', invoiceId, `Factuur ${number} gemaakt uit ${entries.length} uren`);
|
|
res.redirect('/invoices/' + invoiceId);
|
|
});
|
|
|
|
router.post('/send-email', async (req, res) => {
|
|
const db = getDb();
|
|
const { client_id, date_from, date_to, to_email } = req.body;
|
|
if (!client_id) return res.redirect('/time');
|
|
const account = db.prepare('SELECT * FROM email_accounts WHERE user_id = ? AND active = 1 LIMIT 1').get(req.session.userId);
|
|
if (!account) return res.redirect('/time?error=noemail');
|
|
const entries = db.prepare(`
|
|
SELECT t.*, e.title as engagement_title, c.name as client_name
|
|
FROM time_entries t
|
|
LEFT JOIN engagements e ON e.id = t.engagement_id
|
|
JOIN clients c ON c.id = t.client_id
|
|
WHERE t.client_id = ? AND (? IS NULL OR t.date >= ?) AND (? IS NULL OR t.date <= ?)
|
|
ORDER BY t.date
|
|
`).all(client_id, date_from || null, date_from || null, date_to || null, date_to || null);
|
|
if (entries.length === 0) return res.redirect('/time?error=noentries');
|
|
const totalHours = entries.reduce((s, e) => s + e.hours, 0);
|
|
let text = `Urenoverzicht - ${entries[0].client_name}\n\nPeriode: ${date_from || 'begin'} t/m ${date_to || 'heden'}\nTotaal: ${totalHours} uur\n\n`;
|
|
entries.forEach(e => { text += `${e.date} ${e.hours}u ${e.engagement_title || ''} ${e.description || ''}\n`; });
|
|
text += `\nMek-Tech Consulting`;
|
|
const to = to_email;
|
|
try {
|
|
await emailService.sendEmail(account.id, to, `Urenoverzicht - ${entries[0].client_name}`, text, text.replace(/\n/g, '<br>'));
|
|
res.redirect('/time?sent=1');
|
|
} catch (e) { res.redirect('/time?error=' + encodeURIComponent(e.message)); }
|
|
});
|
|
|
|
module.exports = router;
|