Changes between Version 1 and Version 2 of UseCase0003

Timestamp:

09/02/26 20:38:05 (4 days ago)

Author:

236024

Comment:

--

UseCase0003

@@ -9 +9 @@
 1. The Member opens the list of upcoming activity sessions.
 
-2. The application lists upcoming sessions with the member's current RSVP and attendance status.
+2. The application lists upcoming sessions with the member's current RSVP, attendance status, and whether attendance is expected.
 
 {{{#!sql
 SELECT
-    a.at_name,
+    a.as_id,
+    at.at_name,
     a.as_start_time,
-    a.as_location,
-    COALESCE(p.rsvp_status, 'no_response') AS rsvp_status,
+    l.l_name,
+    p.rsvp_status,
     p.rsvp_at,
-    COALESCE(p.attendance_status, 'not_checked') AS attendance_status
+    p.attendance_status,
+    CASE
+        WHEN ra.student_index IS NOT NULL THEN 'yes'
+        ELSE 'no'
+    END AS attendance_expected
 FROM activity_sessions a
+JOIN activity_types at
+    ON at.at_id = a.at_id
+JOIN locations l
+    ON l.l_id = a.l_id
 LEFT JOIN participation p
-    ON p.at_name = a.at_name
-   AND p.as_start_time = a.as_start_time
-   AND p.as_location = a.as_location
+    ON p.as_id = a.as_id
    AND p.student_index = 230014
+LEFT JOIN required_attendance ra
+    ON ra.as_id = a.as_id
+   AND ra.student_index = 230014
 WHERE a.as_start_time >= CURRENT_TIMESTAMP
-ORDER BY a.as_start_time, a.at_name;
+ORDER BY a.as_start_time, at.at_name;
 }}}
 
@@ -36 +46 @@
 INSERT INTO participation (
     student_index,
-    at_name,
-    as_start_time,
-    as_location,
+    as_id,
     rsvp_status,
-    rsvp_at,
-    attendance_status
+    rsvp_at
 )
 VALUES (
     230014,
-    'IT Workshop',
-    TIMESTAMP '2026-11-26 17:00:00',
-    'Base42',
+    22,
     'going',
-    CURRENT_TIMESTAMP,
-    'not_checked'
+    CURRENT_TIMESTAMP
 );
 }}}
 
-5. The Member enters an absence reason for the Hackathon core team meeting.
+5. The Member enters an absence reason for the Full Member Meeting on 16 April 2026.
 
-6. The application stores the absence announcement.
+6. The application checks that the member's attendance is expected for the selected activity session.
+
+{{{#!sql
+SELECT
+    student_index,
+    as_id
+FROM required_attendance
+WHERE student_index = 230014
+  AND as_id = 9;
+}}}
+
+7. The application stores the absence announcement.
 
 {{{#!sql
 INSERT INTO announces_absence (
     student_index,
-    at_name,
-    as_start_time,
-    as_location,
+    as_id,
     reason,
     announced_at
@@ -69 +82 @@
 VALUES (
     230014,
-    'Core Team Meeting',
-    TIMESTAMP '2026-12-01 18:30:00',
-    'BEST Office',
+    9,
     'Faculty obligations at the same time.',
     CURRENT_TIMESTAMP
@@ -77 +88 @@
 }}}
 
-7. The application updates the participation status for the same session.
+8. The application stores that the member is not going to the same activity session.
 
 {{{#!sql
-UPDATE participation
-SET
-    rsvp_status = 'not_going',
-    rsvp_at = CURRENT_TIMESTAMP,
-    attendance_status = 'excused'
-WHERE student_index = 230014
-  AND at_name = 'Core Team Meeting'
-  AND as_start_time = TIMESTAMP '2026-12-01 18:30:00'
-  AND as_location = 'BEST Office';
+INSERT INTO participation (
+    student_index,
+    as_id,
+    rsvp_status,
+    rsvp_at
+)
+VALUES (
+    230014,
+    9,
+    'not_going',
+    CURRENT_TIMESTAMP
+);
 }}}
 
-8. The application shows the saved absence and updated participation status.
+9. The application shows the saved absence and participation status.
 
 {{{#!sql
 SELECT
     p.student_index,
-    p.at_name,
-    p.as_start_time,
-    p.as_location,
+    at.at_name,
+    a.as_start_time,
+    l.l_name,
     p.rsvp_status,
     p.rsvp_at,
@@ -105 +119 @@
     aa.announced_at
 FROM participation p
+JOIN activity_sessions a
+    ON a.as_id = p.as_id
+JOIN activity_types at
+    ON at.at_id = a.at_id
+JOIN locations l
+    ON l.l_id = a.l_id
 LEFT JOIN announces_absence aa
     ON aa.student_index = p.student_index
-   AND aa.at_name = p.at_name
-   AND aa.as_start_time = p.as_start_time
-   AND aa.as_location = p.as_location
+   AND aa.as_id = p.as_id
 WHERE p.student_index = 230014
-  AND p.at_name = 'Core Team Meeting'
-  AND p.as_start_time = TIMESTAMP '2026-12-01 18:30:00'
-  AND p.as_location = 'BEST Office';
+  AND p.as_id = 9;
 }}}