Personnel Files #141

New Issue

Ajdj100 · 2026-01-01T19:32:41-06:00

Ajdj100 commented

2026-01-01 19:32:41 -06:00

This query can be used to discover what courses a member has attended and passed.

SELECT
    c.id AS course_id,
    c.name AS course_name,

    (
        (ce.hasBookwork = 0 OR MAX(ca.passed_bookwork) = 1)
        AND
        (ce.hasQual = 0 OR MAX(ca.passed_qual) = 1)
    ) AS completed

FROM courses c

LEFT JOIN course_events ce
    ON ce.course_id = c.id
    AND ce.deleted = 0

LEFT JOIN course_attendees ca
    ON ca.course_event_id = ce.id
    AND ca.attendee_id = 38
    AND ca.expired = 0

WHERE c.deleted = 0

GROUP BY c.id, c.name, c.hasBookwork, c.hasQual

ORDER BY c.name;```

This query can be used to discover what courses a member has attended and passed. ```sql SELECT c.id AS course_id, c.name AS course_name, ( (ce.hasBookwork = 0 OR MAX(ca.passed_bookwork) = 1) AND (ce.hasQual = 0 OR MAX(ca.passed_qual) = 1) ) AS completed FROM courses c LEFT JOIN course_events ce ON ce.course_id = c.id AND ce.deleted = 0 LEFT JOIN course_attendees ca ON ca.course_event_id = ce.id AND ca.attendee_id = 38 AND ca.expired = 0 WHERE c.deleted = 0 GROUP BY c.id, c.name, c.hasBookwork, c.hasQual ORDER BY c.name;```

EagleTrooper commented

2026-01-21 00:35:28 -06:00

Need to revisit query. This does not take into account the role of the member and a bad data entry of potentially a "Observer" getting checks on Bookwork & Qual by mistake or trainer not caring / paying attention.

Need to filter as Trainee

Need to revisit query. This does not take into account the role of the member and a bad data entry of potentially a "Observer" getting checks on Bookwork & Qual by mistake or trainer not caring / paying attention. Need to filter as Trainee

Ajdj100 commented

2026-01-21 09:56:07 -06:00

SELECT
    c.id AS course_id,
    c.name AS course_name,
    (
        (c.hasBookwork = 0 OR MAX(ca.passed_bookwork) = 1)
        AND
        (c.hasQual = 0 OR MAX(ca.passed_qual) = 1)
    ) AS completed
FROM courses c
LEFT JOIN course_events ce
    ON ce.course_id = c.id  
    AND ce.deleted = 0
LEFT JOIN course_attendees ca
    ON ca.course_event_id = ce.id
    AND ca.attendee_id = 42 -- hardcoded to user 42
    AND ca.attendee_role_id = 2 -- trainee role ID
    AND ca.expired = 0
WHERE c.deleted = 0
GROUP BY c.id, c.name, c.hasBookwork, c.hasQual
ORDER BY c.name;

Updated query now excludes all non trainee attendances

```sql SELECT c.id AS course_id, c.name AS course_name, ( (c.hasBookwork = 0 OR MAX(ca.passed_bookwork) = 1) AND (c.hasQual = 0 OR MAX(ca.passed_qual) = 1) ) AS completed FROM courses c LEFT JOIN course_events ce ON ce.course_id = c.id AND ce.deleted = 0 LEFT JOIN course_attendees ca ON ca.course_event_id = ce.id AND ca.attendee_id = 42 -- hardcoded to user 42 AND ca.attendee_role_id = 2 -- trainee role ID AND ca.expired = 0 WHERE c.deleted = 0 GROUP BY c.id, c.name, c.hasBookwork, c.hasQual ORDER BY c.name; ``` Updated query now excludes all non trainee attendances

EagleTrooper added the Top Hitlist label 2026-01-26 17:24:18 -06:00

EagleTrooper commented

2026-01-26 17:30:31 -06:00

Adding this to the top hitlist but this should probably be chained to other items such as Fixing Award Forms and if Awards / Training Awards / Qualifications / and how they are added to the database structure.

Sign in to join this conversation.

2 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: 17th-Ranger-Battalion-ORG/milsim-site-v4#141