Access to attendance sheets for parents

[ywarnier]

The DRH/parent role should have access, through his personal drop-down menu, to a page where he can see the list of students he follows, with a possibility to quickly see attendances (or failed attendances) in date order between all the courses/sessions followed by his "children".

Context:
The Human Resources Manager is a role that can be assigned to a user in Chamilo (the best way to progress here is to create a new user and immediately set him as DRH).
In other contexts (primary/secondary schools), we generally just change the name of this role to "Parent", because essentially what the role does is enable the user to follow the progress of another user on the platform, with practically no other useful permission.
To assign a "student" to a "parent", you create a student user, then go to the users list in the administration page, search for the DRH and click the "Assign users" icon (next to the admin star icon).

Once the student is assigned to the parent, the parent can enter the interface and see student progress in the "Reporting" tab. However, there is currently no clear/prominent way to access the attendance sheets of students.

The difficulty here is that, normally, a student can access his own attendance sheet per course. This means that there is no existing screen showing, for one given student, his/her attendances in all of his/her courses.

The goal of this issue is to create a new page, accessible as an action next to the student's reporting data in the reporting tab of the DRH, where we have a filter by date (by default the current week) and we see all the attendances by dates in a vertical table. Something like this:

Date & time	Course	Present?
30/03/2020 8:00	Trigonometry	Yes
30/03/2020 10:00	Geography	Yes
30/03/2020 13:00	History	No
30/03/2020 15:00	Chemistry	Yes
31/03/2020 8:00	English	Yes
31/03/2020 10:00	Religion	Yes
31/03/2020 13:00	Environment	Yes
31/03/2020 15:00	Vectorial mathematics	No

Where the name of the course would be a link to the course (and possibly session) from where this attendance register comes from.

To collect this information, it will be necessary to scan the c_attendance_calendar (and c_attendance_calendar_rel_group) table to get the dates, and the c_attendance_sheet table to get the attendances (or absence thereof) of students for these dates.

The attendance tables schema is roughly like this:

The most efficient way is probably to first scan the c_attendance_sheet table for the user_id we're looking for, then get the attendance_calendar_id which is a reference to the c_attendance_calendar, but c_attendance is the only table that has information on the course and the session.

However, doing it this way would prevent any practical filter on dates.

So it would have to be something like this in terms of query:

SELECT a.iid, a.attendance_id, a.date_time, s.presence FROM c_attendance_calendar a INNER JOIN c_attendance_sheet s ON a.iid = s.attendance_calendar_id AND s.user_id = XYZ AND a.date_time BETWEEN 'date1' AND 'date2';

This could be filtered by dates (date1 and date2 above) and by user_id, and you would get the attendance_id, which is a link to the c_attendance.iid which gives you course and session to create the link:

SELECT c.title, c.code, a.session_id FROM course c, c_attendance a WHERE a.c_id = c.id AND a.iid = XYZ;

Something like that...

In a first implementation, this could ignore the "group" notion in the c_attendance_calendar_rel_group table, but this will later have to be implemented too.

The important aspect to keep in mind here is that you should only show the results for a limited period of time by default (current week from Monday to Sunday, for example), and only of one single user at a time, otherwise the pages will start becoming slow to load.

I am not entirely sure (not considered yet) if it's a good idea to start with c_attendance_sheet, as maybe the user doesn't appear there if he/she is marked absent.
Would that be the case, then it might be better to get a list of all courses/sessions of the user and start from there, looking for c_attendance data only for the courses where the user is subscribed...

[ywarnier]

I think we can close this already.