CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_pnl_period`(IN `start_date` DATE, IN `end_date` DATE, IN `period` VARCHAR(10))
BEGIN
-- Profit & loss summary per day, month or year: money collected (bill payments
-- + pharmacy till) minus expenses — the same figures as Daily sales summary.
SELECT x.label AS `Period`,
       CONCAT('$ ', FORMAT(SUM(x.income), 2)) AS `Income`,
       CONCAT('$ ', FORMAT(SUM(x.expense), 2)) AS `Expenses`,
       CONCAT(IF(SUM(x.income) - SUM(x.expense) < 0, '<span style="color:#b42318">', '<span>'), '$ ', FORMAT(SUM(x.income) - SUM(x.expense), 2), '</span>') AS `Profit`,
       CONCAT(FORMAT(100 * (SUM(x.income) - SUM(x.expense)) / NULLIF(SUM(x.income), 0), 1), ' %') AS `Margin`
FROM (
    SELECT CASE WHEN period = 'Year' THEN DATE_FORMAT(y.d, '%Y') WHEN period = 'Month' THEN DATE_FORMAT(y.d, '%Y-%m (%b)') ELSE DATE_FORMAT(y.d, '%Y-%m-%d (%a)') END AS label,
           y.income, y.expense
    FROM (
        SELECT DATE(p.created_at) d, IF(p.currency = 2 AND p.exchange_rate > 0, p.payment / p.exchange_rate, p.payment) income, 0 expense
        FROM payments p WHERE p.status NOT IN ('Refund', 'Sponsored') AND DATE(p.created_at) BETWEEN start_date AND end_date
        UNION ALL
        SELECT DATE(pp.date), IF(pp.currency = 2 AND pp.exchange_rate > 0, pp.amount / pp.exchange_rate, pp.amount), 0
        FROM phar_pos_payments pp WHERE IFNULL(pp.amount, 0) > 0 AND pp.refund_of IS NULL AND DATE(pp.date) BETWEEN start_date AND end_date
        UNION ALL
        SELECT DATE(e.date), 0, IF(e.currency = 2 AND e.exchange_rate > 0, e.amount / e.exchange_rate, e.amount)
        FROM expenses e WHERE DATE(e.date) BETWEEN start_date AND end_date
    ) y
) x
GROUP BY x.label
ORDER BY x.label;
END
