UC0001 - View Member Profile and Membership History

Initiating actor: Board Member

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

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,
    ms.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
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%')
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,
    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,
    ofn.of_title AS current_organizational_function,
    fm.mandate_year
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 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 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;

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

SELECT
    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;

6. The application shows the member's applications.

SELECT
    a.application_id,
    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 ee.ee_start_time, a.application_id;