CREATE DEFINER=CURRENT_USER PROCEDURE `attendance_rpt`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `staff_filter` INT)
BEGIN
SELECT
    ROW_NUMBER() OVER (ORDER BY a.date ASC, s.name ASC)                  AS Sqn,
    DATE_FORMAT(a.date, '%e %b, %Y')                                      AS Date,
    COALESCE(s.name, '')                                                   AS Staff,
    COALESCE(DATE_FORMAT(a.time_in,  '%l:%i %p'), '--')                   AS 'Time In',
    COALESCE(DATE_FORMAT(a.time_out, '%l:%i %p'), '--')                   AS 'Time Out',
    COALESCE(a.worked_hours, '0')                                          AS 'Worked Hours',
    COALESCE(a.status, '')                                                 AS Status
FROM payroll_attendances a
LEFT JOIN hrm_staffs s ON s.id = a.staff_id
WHERE 1 = 1
    AND a.date BETWEEN start_date_filter AND end_date_filter
    AND (staff_filter = 0 OR staff_filter = s.id)
ORDER BY a.date ASC, s.name ASC;
END
