UC0001 - View Member Profile and Membership History

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.

Scenario

1. The Board Member opens the administration page and searches for members named Dario.

2. The application shows a list of members that include the name Dario in their first or last name.

SELECT
    s.student_index,
    s.first_name,
    s.last_name,
    s.faculty,
    hs.ms_name AS current_membership_stage
FROM students s
JOIN members m
    ON m.student_index = s.student_index
LEFT JOIN has_stage hs
    ON hs.student_index = s.student_index
   AND hs.valid_to IS NULL
WHERE lower(s.first_name) LIKE lower('%Dario%')
   OR lower(s.last_name) LIKE lower('%Dario%')
ORDER BY s.last_name, s.first_name;

3. The Board Member selects Dario Petreski.

4. The application shows the selected member profile.

SELECT
    s.student_index,
    s.first_name,
    s.last_name,
    s.faculty,
    s.field_of_studies,
    s.year_of_studies,
    s.email,
    s.phone_number,
    hs.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
FROM students s
JOIN members m
    ON m.student_index = s.student_index
LEFT JOIN has_stage hs
    ON hs.student_index = s.student_index
   AND hs.valid_to IS NULL
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'
WHERE s.student_index = 230024;

5. The application shows the member's membership stage history.

SELECT
    hs.ms_name,
    hs.valid_from,
    hs.valid_to
FROM has_stage hs
WHERE hs.student_index = 230024
ORDER BY hs.valid_from;

6. The application shows the member's applications.

SELECT
    a.application_id,
    a.et_name,
    a.event_title,
    a.event_start_time,
    a.function_name,
    a.application_type,
    a.application_status
FROM applications a
WHERE a.student_index = 230024
ORDER BY a.event_start_time, a.application_id;