Booking option API call (or a way to export attendees using SQL or REST)

[xnstad]

Get booking option object as JSON

I can't seem to find a good way to export attendees per booking option using the API. I can only use mod_booking_bookings, which does not return any users for me.

[
    {
        "id": 5,
        "cm": 172,
        "timemodified": 1720442365,
        "name": "Test course",
        "intro": "<p>Intro text here</p>",
        "duration": "2 hours",
        "points": "1.00",
        "organizatorname": "",
        "eventtype": "Course",
        "bookingmanagerid": 710,
        "bookingmanagername": "Trainer",
        "bookingmanagersurname": "Lastname",
        "bookingmanageremail": "emailhere",
        "myfilemanager": [],
        "categories": [],
        "options": [
            {
                "id": 32,
                "text": "Booking option name",
                "timemodified": 0,
                "maxanswers": 0,
                "coursestarttime": 1722495600,
                "courseendtime": 1725174000,
                "description": "<p description here </p>",
                "location": "Location here",
                "institution": "Institution here",
                "address": "",
                "users": [], <--------------------------------------
                "teachers": []
            },
            [...]

It would be ideal if something like mod_booking_option could be implemented, which takes a booking option ID as input and returns a booking option object, with dates, confirmed attendees, and waiting list.

Alternatives I've considered

I've tried to write SQL queries as a way around this, but I haven't found a way to exclude deleted answers from my queries. I've tried to look at the query logs as well. I can see the new records being created when I add a user to a booking, how are these marked as deleted when I unbook the user?

Here is an example query, which lists all booking answers, if I can filter the results to only include active bookings - I'll have what I need.

SELECT mdl_booking_answers.id as 'Answer ID', mdl_booking.name AS 'Course', mdl_user.username, mdl_booking_options.text AS 'Option', FROM_UNIXTIME(mdl_booking_answers.timemodified) AS 'Timestamp' FROM mdl_booking_answers
INNER JOIN mdl_booking ON mdl_booking_answers.bookingid=mdl_booking.id
INNER JOIN mdl_user ON mdl_user.id=mdl_booking_answers.userid
INNER JOIN mdl_booking_options ON mdl_booking_options.id=mdl_booking_answers.optionid
WHERE mdl_booking_answers.id > 25
ORDER BY mdl_booking_answers.timemodified DESC

Here is another query, which lists all booking options ordered by start date, and counts the number of confirmed attendees per booking option. The issue here is also that it counts deleted entries.

SELECT
  o.id AS 'Option ID',
  o.text AS 'Description',
  o.location,
  DATE_FORMAT(FROM_UNIXTIME(o.coursestarttime), '%d. %M %Y') AS 'Start Date',
  o.maxanswers AS 'Max Participants',
  COALESCE(a.attendees, 0) AS attendees
FROM moodle.mdl_booking_options o
LEFT JOIN (
  SELECT optionid, COUNT(*) AS attendees
  FROM moodle.mdl_booking_answers
  GROUP BY optionid
) a ON o.id = a.optionid
ORDER BY o.coursestarttime;

Additional context

Moodle version: 4.3.5+ (Build: 20240627) Booking version: Testing - 8.4.6 (Build: 2024070500) Wunderbyte Table version: Dev - 2.0.6 (Build 2024061200)

[georgmaisser]

Hi,

We provide currently no webservice for that.

SELECT mdl_booking_answers.id as 'Answer ID', mdl_booking.name AS 'Course', mdl_user.username, mdl_booking_options.text AS 'Option', FROM_UNIXTIME(mdl_booking_answers.timemodified) AS 'Timestamp' FROM mdl_booking_answers
INNER JOIN mdl_booking ON mdl_booking_answers.bookingid=mdl_booking.id
INNER JOIN mdl_user ON mdl_user.id=mdl_booking_answers.userid
INNER JOIN mdl_booking_options ON mdl_booking_options.id=mdl_booking_answers.optionid
WHERE mdl_booking_answers.id > 25 AND mdl_booking_answers.waitinglist < 2
ORDER BY mdl_booking_answers.timemodified DESC

You notice the AND mdl_booking_answers.waitinglist < 2 I added above?

The old waitinglist column has merged in a kind of booking status column. We use constants defined in lib for the values:

// Define booking status parameters. define('MOD_BOOKING_STATUSPARAM_BOOKED', 0); define('MOD_BOOKING_STATUSPARAM_WAITINGLIST', 1); define('MOD_BOOKING_STATUSPARAM_RESERVED', 2); define('MOD_BOOKING_STATUSPARAM_NOTIFYMELIST', 3); // Get message when place is open. define('MOD_BOOKING_STATUSPARAM_NOTBOOKED', 4); define('MOD_BOOKING_STATUSPARAM_DELETED', 5);

[georgmaisser]

BTW, you can get booked users on the report.php as csv etc. When you have the pro license, you can download all the answers of one instance, not only from one specific booking option.