Changes between Version 1 and Version 2 of UseCase0001

Timestamp:

09/02/26 20:28:39 (4 days ago)

Author:

236024

Comment:

--

UseCase0001

@@ -3 +3 @@
 '''Initiating actor:''' Board Member
 
-A Board Member views the stored data for a selected BEST Skopje member. The goal is to see the member's profile, current membership stage, mentor, current organisational function, previous membership stages, and applications.
+A Board Member views the stored data for a selected BEST Skopje member. In this scenario, the Board Member is acting as the HR Coordinator. The goal is to see the member's profile, current membership stage, mentor, current organisational function, previous membership stages, and applications.
 
 == Scenario ==
@@ -17 +17 @@
     s.last_name,
     s.faculty,
-    hs.ms_name AS current_membership_stage
+    ms.ms_name AS current_membership_stage
 FROM students s
 JOIN members m
@@ -24 +24 @@
     ON hs.student_index = s.student_index
    AND hs.valid_to IS NULL
+LEFT JOIN membership_stages ms
+    ON ms.ms_id = hs.ms_id
 WHERE lower(s.first_name) LIKE lower('%Dario%')
    OR lower(s.last_name) LIKE lower('%Dario%')
@@ -43 +45 @@
     s.email,
     s.phone_number,
-    hs.ms_name AS current_membership_stage,
+    ms.ms_name AS current_membership_stage,
     hs.valid_from AS current_stage_valid_from,
     ym.mentor_index,
     mentor.first_name AS mentor_first_name,
     mentor.last_name AS mentor_last_name,
-    iof.of_title AS current_organizational_function,
-    iof.mandate
+    ofn.of_title AS current_organizational_function,
+    fm.mandate_year
 FROM students s
 JOIN members m
@@ -56 +58 @@
     ON hs.student_index = s.student_index
    AND hs.valid_to IS NULL
+LEFT JOIN membership_stages ms
+    ON ms.ms_id = hs.ms_id
 LEFT JOIN young_members ym
     ON ym.student_index = s.student_index
 LEFT JOIN students mentor
     ON mentor.student_index = ym.mentor_index
-LEFT JOIN is_organizational_function iof
-    ON iof.student_index = s.student_index
-   AND iof.mandate = '2025/2026'
+LEFT JOIN function_mandates fm
+    ON fm.student_index = s.student_index
+   AND fm.mandate_year = 2025
+LEFT JOIN organizational_functions ofn
+    ON ofn.of_id = fm.of_id
 WHERE s.student_index = 230024;
 }}}
@@ -70 +76 @@
 {{{#!sql
 SELECT
-    hs.ms_name,
+    ms.ms_name,
     hs.valid_from,
     hs.valid_to
 FROM has_stage hs
+JOIN membership_stages ms
+    ON ms.ms_id = hs.ms_id
 WHERE hs.student_index = 230024
 ORDER BY hs.valid_from;
@@ -83 +91 @@
 SELECT
     a.application_id,
-    a.et_name,
-    a.event_title,
-    a.event_start_time,
-    a.function_name,
+    et.et_name,
+    ee.ee_title,
+    ee.ee_start_time,
+    eft.eft_name,
     a.application_type,
     a.application_status
 FROM applications a
+JOIN event_editions ee
+    ON ee.ee_id = a.ee_id
+JOIN event_types et
+    ON et.et_id = ee.et_id
+LEFT JOIN event_functions ef
+    ON ef.ee_id = a.ee_id
+   AND ef.ef_no = a.ef_no
+LEFT JOIN event_function_types eft
+    ON eft.eft_id = ef.eft_id
 WHERE a.student_index = 230024
-ORDER BY a.event_start_time, a.application_id;
+ORDER BY ee.ee_start_time, a.application_id;
 }}}