93 lines
2.7 KiB
JavaScript
93 lines
2.7 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const db = require('../database');
|
|
|
|
// Get cashback summary by month/year with optional filters
|
|
router.get('/:year/:month', (req, res) => {
|
|
const { year, month } = req.params;
|
|
const { card_id, category_id } = req.query;
|
|
|
|
let query = `
|
|
SELECT
|
|
c.id as card_id,
|
|
c.name as card_name,
|
|
c.bank,
|
|
cat.id as category_id,
|
|
cat.name as category_name,
|
|
mc.cashback_percent,
|
|
COALESCE(SUM(t.amount), 0) as total_spent,
|
|
COALESCE(SUM(t.cashback_amount), 0) as total_cashback,
|
|
COUNT(t.id) as transaction_count
|
|
FROM monthly_categories mc
|
|
JOIN cards c ON mc.card_id = c.id
|
|
JOIN categories cat ON mc.category_id = cat.id
|
|
LEFT JOIN transactions t ON t.card_id = mc.card_id
|
|
AND t.category_id = mc.category_id
|
|
AND strftime('%Y', t.date) = ?
|
|
AND strftime('%m', t.date) = ?
|
|
WHERE mc.year = ? AND mc.month = ?
|
|
`;
|
|
|
|
const queryParams = [year, month.toString().padStart(2, '0'), year, month];
|
|
|
|
// Add filters
|
|
if (card_id) {
|
|
query += ' AND c.id = ?';
|
|
queryParams.push(card_id);
|
|
}
|
|
|
|
if (category_id) {
|
|
query += ' AND cat.id = ?';
|
|
queryParams.push(category_id);
|
|
}
|
|
|
|
query += ' GROUP BY c.id, cat.id ORDER BY c.name, cat.name';
|
|
|
|
db.all(query, queryParams, (err, rows) => {
|
|
if (err) {
|
|
console.error(err);
|
|
res.status(500).json({ error: 'Database error' });
|
|
} else {
|
|
// Calculate totals
|
|
const totalSpent = rows.reduce((sum, row) => sum + row.total_spent, 0);
|
|
const totalCashback = rows.reduce((sum, row) => sum + row.total_cashback, 0);
|
|
const totalTransactions = rows.reduce((sum, row) => sum + row.transaction_count, 0);
|
|
|
|
res.json({
|
|
summary: {
|
|
total_spent: totalSpent,
|
|
total_cashback: totalCashback,
|
|
total_transactions: totalTransactions,
|
|
average_cashback_rate: totalSpent > 0 ? (totalCashback / totalSpent) * 100 : 0
|
|
},
|
|
details: rows
|
|
});
|
|
}
|
|
});
|
|
});
|
|
|
|
// Get overall cashback statistics
|
|
router.get('/stats/overview', (req, res) => {
|
|
const query = `
|
|
SELECT
|
|
COUNT(DISTINCT c.id) as total_cards,
|
|
COUNT(DISTINCT cat.id) as total_categories,
|
|
COALESCE(SUM(t.amount), 0) as lifetime_spent,
|
|
COALESCE(SUM(t.cashback_amount), 0) as lifetime_cashback,
|
|
COUNT(t.id) as lifetime_transactions
|
|
FROM cards c
|
|
CROSS JOIN categories cat
|
|
LEFT JOIN transactions t ON 1=1
|
|
`;
|
|
|
|
db.get(query, (err, row) => {
|
|
if (err) {
|
|
console.error(err);
|
|
res.status(500).json({ error: 'Database error' });
|
|
} else {
|
|
res.json(row);
|
|
}
|
|
});
|
|
});
|
|
|
|
module.exports = router; |