CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_expense_budget`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
-- Expenses vs budget per category: the monthly budget set on each expense
-- category (Expenses -> Category) times the months in the period, against
-- what was spent — over budget shows red.
SELECT c.name AS `Category`,
       CONCAT('$ ', FORMAT(c.budget, 2)) AS `Budget`,
       CONCAT('$ ', FORMAT(c.spent, 2)) AS `Spent`,
       CONCAT(IF(c.budget - c.spent < 0, '<span style="color:#b42318">', '<span>'), '$ ', FORMAT(c.budget - c.spent, 2), '</span>') AS `Remaining`,
       IF(c.budget > 0, CONCAT(FORMAT(100 * c.spent / c.budget, 1), ' %'), '— no budget') AS `Used`
FROM (
    SELECT ec.name,
           IFNULL(ec.monthly_budget, 0) * (TIMESTAMPDIFF(MONTH, DATE_FORMAT(start_date, '%Y-%m-01'), DATE_FORMAT(end_date, '%Y-%m-01')) + 1) budget,
           IFNULL((SELECT SUM(IF(e.currency = 2 AND e.exchange_rate > 0, e.amount / e.exchange_rate, e.amount)) FROM expenses e
                   WHERE e.category = ec.id AND DATE(e.date) BETWEEN start_date AND end_date), 0) spent
    FROM expense_categories ec
) c
WHERE c.budget > 0 OR c.spent > 0
ORDER BY c.spent DESC;
END
