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

	select ROW_NUMBER() OVER (ORDER BY type) AS Sqn,  Type, Total, ifnull(Commission,0) as Commission from (
	select 
    reference as Type, concat('$ ',
    format(sum(case when currency = 2 then payment/payments.exchange_rate else payment end),2)) as Total,
	concat('$ ',
    format(sum(case when currency = 2 then commissions/payments.exchange_rate else commissions end),2)) as Commission
    from payments 
    where status <> 'Refund' and date(payments.created_at) between start_date and end_date
    group by reference
    
    union all
    
    
    select "Pharmacy" as Type, concat('$ ',
    format(ifnull(sum(case when currency = 2 then phar_pos_payments.amount/phar_pos_payments.exchange_rate else phar_pos_payments.amount end),0),2)) as Total, 
    0 as Commission  from phar_pos_payments
    where date(phar_pos_payments.date) between start_date and end_date
    
    union all
    
    select "Expense" as Type, concat('$ -',
    format(ifnull(sum(case when currency = 2 then expenses.amount/expenses.exchange_rate else expenses.amount end),0),2)) as Total, 
    0 as Commission  from expenses
    where date(expenses.date) between start_date and end_date and is_cash = 'Yes'
    
    
    )z
    
    union all
    
    select '' Sqn,  '' Type, concat("<span style='font-weight:bold;'> $ ",format(sum(Total) - sum(Expense),2),"</span>") as Total,
    concat("<span style='font-weight:bold;'> $ ",ifnull(format(sum(Commission),2),"</span>"),0) as Commission from (
	select  sum(case when currency = 2 then payment/payments.exchange_rate else payment end) as Total,
    sum(case when currency = 2 then commissions/payments.exchange_rate else commissions end) as Commission,
    0 as Expense
    from payments 
    where status <> 'Refund' and date(payments.created_at) between start_date and end_date
    
    union 

    select ifnull(sum(case when currency = 2 then phar_pos_payments.amount/phar_pos_payments.exchange_rate else phar_pos_payments.amount end),0) as Total, 0 as Commission, 0 as Expense  from phar_pos_payments
    where date(phar_pos_payments.date) between start_date and end_date
    
	union all
    
    select 0 as Total,0 as Commission, ifnull(sum(case when currency = 2 then expenses.amount/expenses.exchange_rate else expenses.amount end),0) as Expense from expenses
    where date(expenses.date) between start_date and end_date and is_cash = 'Yes'
    
    )f;
    
    
END
