Past attendance incorrect if people are removed from groups/congregations

[jefft]

There's a logic bug in Jethro's attendance report. When people are removed from a group or congregation, they no longer appear as present in past weeks when they did attend.

To test this out:

• create a new Group, with attendance marked once a week (here, Mondays):

• Add 4 group members.

• Go to Attendance -> Record, pick the most recent Monday and record 4 attendances.

• The attendances, when viewed, will look like:

• Now remove 2 members from the group.

• Re-run the report. The attendances have vanished:

[jefft]

Duplicate of https://github.com/tbar0970/jethro-pmm/issues/490.

I don't see that this is too complex to fix, at least for group attendance. The attendance_record table hasn't changed. Jethro still 'knows' that 4 people attended that group that day. It's just a matter of fixing the SQL. The current SQL query is:

SELECT person.id, 
       person.last_name,
       person.first_name,
       pgms.label AS membership_status,
       person.status,
       ar.date,
       ar.present,
       IF (pa.id IS NOT NULL,
                    1,
                    0) AS planned_absence
FROM _person person
JOIN age_bracket ab ON ab.id = person.age_bracketid
JOIN family f ON person.familyid = f.id
JOIN person_group_membership pgm ON pgm.personid = person.id
AND pgm.groupid = 73
LEFT JOIN attendance_record ar ON (ar.personid = person.id
                                   AND ar.date BETWEEN '2024-05-26' AND '2024-05-30'
                                   AND ar.groupid = 73)
LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status
LEFT JOIN planned_absence pa ON pa.personid = person.id
AND ar.date BETWEEN pa.start_date AND pa.end_date
WHERE ((person.status <> "archived")
       OR (ar.present IS NOT NULL))
ORDER BY last_name ASC,
         person.familyid,
         ab.`rank`,
         IF (ab.is_adult,
             person.gender,
             1) DESC, first_name;

It needs to start with attendance_record and LEFT JOIN on other records that may not exist:

SELECT person.id,
       person.last_name, 
       person.first_name,
       pgms.label AS membership_status,
       ar.date,
       ar.present,
       IF (pa.id IS NOT NULL,
                    1,
                    0) AS planned_absence
FROM attendance_record ar
JOIN _person person ON person.id=ar.personid
LEFT JOIN person_group_membership pgm ON pgm.personid = person.id
AND pgm.groupid=ar.groupid
LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status
LEFT JOIN planned_absence pa ON pa.personid = person.id
AND ar.date BETWEEN pa.start_date AND pa.end_date
WHERE ar.groupid=73
  AND ar.date BETWEEN '2024-05-26' AND '2024-05-30'

Admittedly we do lose historical members' membership status:

We could LEFT JOIN on person too, to handle the case where a person was deleted.

[tbar0970]

You're right that there is a quick/partial fix for group attendance. I will put this in place.

490 describes the proper fix whereby we copy more details into the attendance_record table for posterity.