CREATE DEFINER=CURRENT_USER PROCEDURE `expenses_rpt`(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
