Changes between Version 1 and Version 2 of UseCase0007


Ignore:
Timestamp:
09/02/26 17:34:35 (4 days ago)
Author:
236024
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • UseCase0007

    v1 v2  
    33'''Initiating actor:''' Main Organiser
    44
    5 A Main Organiser reviews applications for their event edition and updates application statuses. The scenario reviews Hackathon participation applications.
     5A Main Organiser reviews applications for their event edition and updates application statuses.
    66
    77== Scenario ==
    88
    9 1. The Main Organiser opens the application review page for Impact Hackathon 2026.
     91. The Main Organiser opens the application review page.
    1010
    11 2. The application lists applications for Impact Hackathon 2026.
     112. The application lists event editions where the current member is an accepted Main Organiser.
     12
     13{{{#!sql
     14SELECT
     15    ee.ee_id,
     16    et.et_name,
     17    ee.ee_title,
     18    ee.ee_start_time,
     19    ee.ee_end_time
     20FROM applications a
     21JOIN event_functions ef
     22    ON ef.ee_id = a.ee_id
     23   AND ef.ef_no = a.ef_no
     24JOIN event_editions ee
     25    ON ee.ee_id = a.ee_id
     26JOIN event_types et
     27    ON et.et_id = ee.et_id
     28WHERE a.student_index = 230014
     29  AND a.application_type = 'event_function'
     30  AND a.application_status = 'accepted'
     31  AND ef.ef_name LIKE 'Main Organiser%'
     32ORDER BY ee.ee_start_time DESC;
     33}}}
     34
     353. The Main Organiser chooses Impact Hackathon 2026.
     36
     374. The application lists submitted applications for the event edition.
    1238
    1339{{{#!sql
    … …  
    1743    s.first_name,
    1844    s.last_name,
     45    ef.ef_name,
    1946    a.application_type,
    20     a.function_name,
    2147    a.application_status,
    2248    a.motivational_letter
    … …  
    2450JOIN students s
    2551    ON s.student_index = a.student_index
    26 WHERE a.et_name = 'Hackathon'
    27   AND a.event_title = 'Impact Hackathon 2026'
    28   AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
    29 ORDER BY a.application_type, a.function_name, a.application_status, s.last_name, s.first_name;
     52LEFT JOIN event_functions ef
     53    ON ef.ee_id = a.ee_id
     54   AND ef.ef_no = a.ef_no
     55WHERE a.ee_id = 8
     56ORDER BY a.application_type, ef.ef_name, a.application_status, s.last_name, s.first_name;
    3057}}}
    3158
    32 3. The Main Organiser selects Lina Stamenovska's participation application and accepts it.
     595. The Main Organiser accepts the participation application from student 230031.
    3360
    34 4. The application updates the application status.
     616. The application updates the application status.
    3562
    3663{{{#!sql
    3764UPDATE applications
    3865SET application_status = 'accepted'
    39 WHERE application_id = (
    40     SELECT a.application_id
    41     FROM applications a
    42     WHERE a.student_index = 230031
    43       AND a.et_name = 'Hackathon'
    44       AND a.event_title = 'Impact Hackathon 2026'
    45       AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
    46       AND a.application_type = 'event_participation'
    47     ORDER BY a.application_id
    48     LIMIT 1
    49 );
     66WHERE student_index = 230031
     67  AND ee_id = 8
     68  AND application_type = 'event_participation';
    5069}}}
    5170
    52 5. The application shows the updated application.
     717. The application shows the updated application list.
    5372
    5473{{{#!sql
    5574SELECT
    5675    a.application_id,
     76    s.student_index,
    5777    s.first_name,
    5878    s.last_name,
     79    ef.ef_name,
    5980    a.application_type,
    60     a.function_name,
    6181    a.application_status
    6282FROM applications a
    6383JOIN students s
    6484    ON s.student_index = a.student_index
    65 WHERE a.student_index = 230031
    66   AND a.et_name = 'Hackathon'
    67   AND a.event_title = 'Impact Hackathon 2026'
    68   AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
    69   AND a.application_type = 'event_participation'
    70 ORDER BY a.application_id;
     85LEFT JOIN event_functions ef
     86    ON ef.ee_id = a.ee_id
     87   AND ef.ef_no = a.ef_no
     88WHERE a.ee_id = 8
     89ORDER BY a.application_type, ef.ef_name, a.application_status, s.last_name, s.first_name;
    7190}}}
    7291
    73 6. The application shows accepted function applications for Impact Hackathon 2026.
     928. The application shows accepted event function applications for the same event edition.
    7493
    7594{{{#!sql
    7695SELECT
    77     ctf.function_name,
    78     COALESCE(s.first_name || ' ' || s.last_name, 'not assigned') AS accepted_member,
    79     a.application_status
    80 FROM core_team_functions ctf
     96    ef.ef_no,
     97    ef.ef_name,
     98    s.student_index,
     99    s.first_name,
     100    s.last_name
     101FROM event_functions ef
    81102LEFT JOIN applications a
    82     ON a.et_name = ctf.et_name
    83    AND a.event_title = ctf.event_title
    84    AND a.event_start_time = ctf.event_start_time
    85    AND a.function_name = ctf.function_name
    86    AND a.application_type = 'core_team_function'
     103    ON a.ee_id = ef.ee_id
     104   AND a.ef_no = ef.ef_no
     105   AND a.application_type = 'event_function'
    87106   AND a.application_status = 'accepted'
    88107LEFT JOIN students s
    89108    ON s.student_index = a.student_index
    90 WHERE ctf.et_name = 'Hackathon'
    91   AND ctf.event_title = 'Impact Hackathon 2026'
    92   AND ctf.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
    93 ORDER BY ctf.function_name;
     109WHERE ef.ee_id = 8
     110ORDER BY ef.ef_no;
    94111}}}