UC0007 - Review Applications

Initiating actor: Main Organiser

A Main Organiser reviews applications for their event edition and updates application statuses.

Scenario

1. The Main Organiser opens the application review page.

2. The application lists event editions where the current member is an accepted Main Organiser.

SELECT
    ee.ee_id,
    et.et_name,
    ee.ee_title,
    ee.ee_start_time,
    ee.ee_end_time
FROM holds_event_function hef
JOIN event_functions ef
    ON ef.ee_id = hef.ee_id
   AND ef.ef_no = hef.ef_no
JOIN event_function_types eft
    ON eft.eft_id = ef.eft_id
JOIN event_editions ee
    ON ee.ee_id = hef.ee_id
JOIN event_types et
    ON et.et_id = ee.et_id
WHERE hef.student_index = 230014
  AND eft.eft_name = 'Main Organiser'
ORDER BY ee.ee_start_time DESC;

3. The Main Organiser chooses Impact Hackathon 2026.

4. The application lists submitted applications for the event edition.

SELECT
    a.application_id,
    s.student_index,
    s.first_name,
    s.last_name,
    eft.eft_name,
    a.application_type,
    a.application_status,
    a.motivational_letter
FROM applications a
JOIN students s
    ON s.student_index = a.student_index
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.ee_id = 8
ORDER BY a.application_type, eft.eft_name, a.application_status, s.last_name, s.first_name;

5. The Main Organiser accepts the participation application from student 230031.

6. The application updates the application status.

UPDATE applications
SET application_status = 'accepted'
WHERE student_index = 230031
  AND ee_id = 8
  AND application_type = 'event_participation';

7. The application shows the updated application list.

SELECT
    a.application_id,
    s.student_index,
    s.first_name,
    s.last_name,
    eft.eft_name,
    a.application_type,
    a.application_status
FROM applications a
JOIN students s
    ON s.student_index = a.student_index
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.ee_id = 8
ORDER BY a.application_type, eft.eft_name, a.application_status, s.last_name, s.first_name;

8. The application shows accepted event function applications for the same event edition.

SELECT
    ef.ef_no,
    eft.eft_name,
    s.student_index,
    s.first_name,
    s.last_name
FROM event_functions ef
JOIN event_function_types eft
    ON eft.eft_id = ef.eft_id
LEFT JOIN holds_event_function hef
    ON hef.ee_id = ef.ee_id
   AND hef.ef_no = ef.ef_no
LEFT JOIN students s
    ON s.student_index = hef.student_index
WHERE ef.ee_id = 8
ORDER BY ef.ef_no;