CREATE DEFINER=CURRENT_USER PROCEDURE `yaxye`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `expense_id_filter` INT, IN `staff_id_filter` INT, IN `payment_type_filter` INT)
BEGIN
	select  ROW_NUMBER() OVER (ORDER BY date) AS Sqn,date as Date,expense_categories.name as Category,
    expense_subcategories.name as Subcategory,
    expenses.expense_type as `From`,
    
    expenses.type as Type,ifnull(hrm_staffs.name,'') as Staff, acc_payment_types.name as `Payment type`,concat(acc_currencies.symbol, ' ' ,amount) as Amount from expenses 
	
    
    join expense_categories on expense_categories.id = expenses.category
    join expense_subcategories on expense_subcategories.id = expenses.subcategory
	join acc_currencies on acc_currencies.id = expenses.currency
    join acc_payment_types on acc_payment_types.id = expenses.payment_type
	left join hrm_staffs on hrm_staffs.id = expenses.staff_id
    
    where date between start_date_filter and end_date_filter and (expense_id_filter = 0 or expenses.category = expense_id_filter)
    and (staff_id_filter = 0 or expenses.staff_id = staff_id_filter)  and (payment_type_filter = 0 or expenses.payment_type = payment_type_filter)
    
    union all 
    
    select  '' AS Sqn,'' as Date,'' as Category,
    '' as Subcategory,
    '' as `From`,
    '' as Type,'' as Staff, 
    '' as `Payment type`,
    concat(sum(amount)) as Amount from expenses 
	
    
    join expense_categories on expense_categories.id = expenses.category
    join expense_subcategories on expense_subcategories.id = expenses.subcategory
	join acc_currencies on acc_currencies.id = expenses.currency
    join acc_payment_types on acc_payment_types.id = expenses.payment_type
	left join hrm_staffs on hrm_staffs.id = expenses.staff_id
    
    where date between start_date_filter and end_date_filter and (expense_id_filter = 0 or expenses.category = expense_id_filter)
    and (staff_id_filter = 0 or expenses.staff_id = staff_id_filter)  and (payment_type_filter = 0 or expenses.payment_type = payment_type_filter);

END
