yki.public

Parameters

Name	Type	Mode
eid	bigint	IN
pet	exam_session_ticket_type	IN

Definition

DECLARE
    es record;
    rl_sub_type exam_session_ticket_type;
    sw_sub_type exam_session_ticket_type;
    queue_count bigint;
    participants_count bigint;
BEGIN
    SELECT INTO es type, max_participants, max_participants_read_listen, max_participants_speak_write
    FROM exam_session WHERE id = eid;

-- FULL session: count all registrations against max_participants
    IF es.type = 'FULL' THEN
        SELECT COUNT(*) INTO queue_count
        FROM registration r
        WHERE r.exam_session_id = eid AND r.kind = 'QUEUE' AND r.state IN ('STARTED', 'SUBMITTED');

SELECT COUNT(*) INTO participants_count
        FROM registration r
        WHERE r.exam_session_id = eid AND r.kind = 'ADMISSION' AND r.state IN ('STARTED', 'SUBMITTED', 'COMPLETED');

IF queue_count > 0 OR participants_count >= es.max_participants THEN RETURN 'QUEUE'; END IF;
        RETURN 'ADMISSION';
    END IF;

-- Partial session: determine which sub-types compete in each pool
    IF es.type = 'READ_SPEAK' THEN
        rl_sub_type := 'READ';
        sw_sub_type := 'SPEAK';
    ELSE -- LISTEN_WRITE
        rl_sub_type := 'LISTEN';
        sw_sub_type := 'WRITE';
    END IF;

-- Check read/listen pool when relevant
    IF pet IN (rl_sub_type, 'ALL_PARTS') THEN
        SELECT COUNT(*) INTO queue_count
        FROM registration r
        WHERE r.exam_session_id = eid AND r.kind = 'QUEUE' AND r.state IN ('STARTED', 'SUBMITTED')
          AND r.partial_exam_type IN ('ALL_PARTS', rl_sub_type);

SELECT COUNT(*) INTO participants_count
        FROM registration r
        WHERE r.exam_session_id = eid AND r.kind = 'ADMISSION' AND r.state IN ('STARTED', 'SUBMITTED', 'COMPLETED')
          AND r.partial_exam_type IN ('ALL_PARTS', rl_sub_type);

IF queue_count > 0 OR participants_count >= es.max_participants_read_listen THEN RETURN 'QUEUE'; END IF;
        IF pet = rl_sub_type THEN RETURN 'ADMISSION'; END IF;
    END IF;

-- Check speak/write pool when relevant
    SELECT COUNT(*) INTO queue_count
    FROM registration r
    WHERE r.exam_session_id = eid AND r.kind = 'QUEUE' AND r.state IN ('STARTED', 'SUBMITTED')
      AND r.partial_exam_type IN ('ALL_PARTS', sw_sub_type);

SELECT COUNT(*) INTO participants_count
    FROM registration r
    WHERE r.exam_session_id = eid AND r.kind = 'ADMISSION' AND r.state IN ('STARTED', 'SUBMITTED', 'COMPLETED')
      AND r.partial_exam_type IN ('ALL_PARTS', sw_sub_type);

IF queue_count > 0 OR participants_count >= es.max_participants_speak_write THEN RETURN 'QUEUE'; END IF;
    RETURN 'ADMISSION';
END;

select_registration_kind(eid bigint, pet exam_session_ticket_type)

Parameters

Definition