CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_collections`(IN `start_date` DATE, IN `end_date` DATE, IN `cashier_filter` INT, IN `method_filter` INT)
BEGIN
-- Daily cash / collection: what each cashier took, per payment method and day
-- (bill payments + pharmacy till payments; refunded and sponsored rows left out,
-- other currency converted to $).
SELECT DATE_FORMAT(c.d, '%d %b, %Y') AS `Date`, c.cashier AS `Cashier`, c.method AS `Method`,
       c.n AS `Payments`,
       CONCAT('$ ', FORMAT(c.collected, 2)) AS `Collected`,
       CONCAT('$ ', FORMAT(c.disc, 2)) AS `Discount`
FROM (
    SELECT x.d, x.cashier, x.method, SUM(x.n) n, SUM(x.collected) collected, SUM(x.disc) disc
    FROM (
        SELECT DATE(p.created_at) d, COALESCE(u.name, '-') cashier, COALESCE(pt.name, 'Other') method, COUNT(*) n,
               SUM(IF(p.currency = 2 AND p.exchange_rate > 0, p.payment / p.exchange_rate, p.payment)) collected,
               SUM(IF(p.currency = 2 AND p.exchange_rate > 0, (IFNULL(p.discount, 0) + IFNULL(p.promotion, 0)) / p.exchange_rate, IFNULL(p.discount, 0) + IFNULL(p.promotion, 0))) disc
        FROM payments p LEFT JOIN users u ON u.id = p.created_by LEFT JOIN acc_payment_types pt ON pt.id = p.payment_method
        WHERE p.status NOT IN ('Refund', 'Sponsored') AND DATE(p.created_at) BETWEEN start_date AND end_date
          AND (cashier_filter = 0 OR p.created_by = cashier_filter) AND (method_filter = 0 OR p.payment_method = method_filter)
        GROUP BY DATE(p.created_at), u.name, pt.name
        UNION ALL
        SELECT DATE(pp.date), COALESCE(u.name, '-'), COALESCE(pt.name, 'Other'), COUNT(*),
               SUM(IF(pp.currency = 2 AND pp.exchange_rate > 0, pp.amount / pp.exchange_rate, pp.amount)),
               SUM(IF(pp.currency = 2 AND pp.exchange_rate > 0, IFNULL(pp.discount, 0) / pp.exchange_rate, IFNULL(pp.discount, 0)))
        FROM phar_pos_payments pp LEFT JOIN users u ON u.id = pp.created_by LEFT JOIN acc_payment_types pt ON pt.id = pp.payment_method
        WHERE IFNULL(pp.amount, 0) > 0 AND pp.refund_of IS NULL AND DATE(pp.date) BETWEEN start_date AND end_date
          AND (cashier_filter = 0 OR pp.created_by = cashier_filter) AND (method_filter = 0 OR pp.payment_method = method_filter)
        GROUP BY DATE(pp.date), u.name, pt.name
    ) x
    GROUP BY x.d, x.cashier, x.method
) c
ORDER BY c.d, c.cashier, c.method;
END
