CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_payroll_summary`(IN `start_date` DATE, IN `end_date` DATE, IN `staff_filter` INT)
BEGIN
-- Payroll per staff for the payrolls whose period falls in the dates (older runs
-- without a period: by the day they were made). Additions = commission + overtime
-- + bonus; deductions as calculated from attendance (HRM → Attendance rules).
SELECT CONCAT(IF(r.period_from IS NULL, DATE_FORMAT(r.created_at, '%d %b, %Y'),
                 IF(DAY(r.period_from) = 1 AND r.period_to = LAST_DAY(r.period_from), DATE_FORMAT(r.period_from, '%M %Y'),
                    CONCAT(DATE_FORMAT(r.period_from, '%d %b'), ' – ', DATE_FORMAT(r.period_to, '%d %b, %Y')))),
              IF(r.status = 'draft', ' (draft)', '')) AS `Period`,
       COALESCE(s.name, CONCAT('(deleted staff #', d.staff_id, ')')) AS `Staff`,
       CONCAT('$ ', FORMAT(d.basic_salary, 2)) AS `Basic`,
       CONCAT('$ ', FORMAT(d.commission_amount + d.overtime_amount + d.adjustment_bonus, 2)) AS `Additions`,
       CONCAT('$ ', FORMAT(d.absence_deduction + d.unpaid_leave_deduction, 2)) AS `Absence & unpaid leave`,
       CONCAT('$ ', FORMAT(d.late_deduction + d.early_deduction, 2)) AS `Late & early`,
       CONCAT('$ ', FORMAT(d.other_deduction + d.adjustment_deduction, 2)) AS `Other deductions`,
       CONCAT('$ ', FORMAT(d.deduction_amount, 2)) AS `Total deductions`,
       CONCAT('$ ', FORMAT(d.total, 2)) AS `Net pay`,
       d.absent_days AS `Absent days`,
       d.late_minutes AS `Late (min)`
FROM payroll_run_details d
JOIN payroll_runs r ON r.id = d.payroll_run_id
LEFT JOIN hrm_staffs s ON s.id = d.staff_id
WHERE (IF(r.period_from IS NULL, DATE(r.created_at) BETWEEN start_date AND end_date,
          r.period_from <= end_date AND r.period_to >= start_date))
  AND (staff_filter = 0 OR d.staff_id = staff_filter)
ORDER BY COALESCE(r.period_from, DATE(r.created_at)), s.name;
END
