Person Report 'attendance percentage' filter omits 0% attendance people altogether

[jefft]

Jethro's Person Reports has a the ability to find people whose attendance is less than X percent over the last Y weeks:

Say we filter for under 50% attendance over the last 2 weeks, as in the screenshot.

Jethro has a bug where if someone hasn't attended at all in the last 2 weeks, they are filtered out out of the report. Their attendance is 0%, which is certainly less than 50%, so they should be displayed, but they're not.

To replicate, find someone who hasn't attended in the last 2 weeks, note their congregation, and create a person report as above. You'll see that person isn't shown in the results.

[jefft]

The bug is in this bit of code, which constructs the attendance filter:

https://github.com/tbar0970/jethro-pmm/blob/f45e9ae4fe9d66314ec0b872fee6eac684698dd0/db_objects/person_query.class.php#L1148-L1152

which generates SQL WHERE clause:

  AND (
         (SELECT sum(present)/count(*)*100
          FROM attendance_record
          WHERE date >= '2024-06-03'
            AND groupid = 0
            AND personid = p.id) < 50)

When there's no records in the date range, sum(present) is null, so the whole subquery is null, and null < 50 is false. The fix is to emit 0 instead of null for zero records, using coalesce():

         coalesce(SELECT sum(present)/count(*)*100,0)

[jefft]

This is not a bug, as Tom explained offline. If there are no attendance records (Present or Absent) in the last 2 weeks, then Jethro can't assume that means 0% attendance: it could just mean attendance wasn't marked in the last 2 weeks. We could only positively assert 0% attendance if there are two 'A' absences. If there is no data, best leave out that person entirely, which is what Jethro does.