CREATE DEFINER=CURRENT_USER PROCEDURE `pharmacy_summary_sales_rpt`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN

SELECT 
    phar_pos_payments.date,
    FORMAT(SUM( case when phar_pos_payments.currency = 1 then phar_pos_payments.amount else phar_pos_payments.amount/phar_pos_payments.exchange_rate end ), 2) AS Paid,
    FORMAT(SUM( case when phar_pos_payments.currency = 1 then phar_pos_payments.discount else phar_pos_payments.discount/phar_pos_payments.exchange_rate end),
        2) AS discount
FROM
    phar_pos
        JOIN
    phar_pos_payments ON phar_pos_payments.pos_id = phar_pos.id
     where date between start_date and end_date and IFNULL(phar_pos.is_deleted, 0) = 0
GROUP BY date;
        
END
