Searching bookings with certain users

[atlet]

In previous version it was possible to filter booking options by studenst that are in it. Now this function is remove. How can I implement this search?

[georgmaisser]

Hi, we did discontinue this feature out of data protection concerns. But It's possible to implement the feature again via the new functionalities of local_wunderbyte_table.

One possibility would be to concatenated the names of the booked users in the sql and add them to full text search. It would just be important to link this to the view participants capability to integrate it in the Moodle Access APIs.

It's not a big deal to integrate this correctly, but it's not top of our priority, we hope we get around to do this until the end of summer.

[atlet]

Ok, tnx. Any example or hint in which file I need to do the changes?

[atlet]

@georgmaisser in file classes/booking_option_settings.php I added this code:

    public static function return_sql_for_users($searchparams = []): array {

        global $DB;

        $select = $DB->sql_group_concat('ba1.userobject') . ' as userobjects';

        // We have to create the users object beforehand, in order to be able to use group_concat afterwards.
        $innerselect = $DB->sql_concat_join("''", [
            "'{\"id\":'",
            "ua.id",
            "', \"firstname\":\"'",
            "ua.firstname",
            "'\", \"lastname\":\"'",
            "ua.lastname",
            "'\", \"name\":\"'",
            "ua.firstname",
            "' '",
            'ua.lastname',
            "'\"}'"]);
        $where = '';
        $params = [];

        $from = 'LEFT JOIN
        (
            SELECT ba.optionid, ' . $innerselect . ' as userobject
            FROM {booking_answers} ba
            JOIN {user} ua
            ON ba.userid = ua.id
        ) ba1
        ON ba1.optionid = bo.id';

        // As this is a complete subrequest, we have to add the "where" to the outer table, where it is already rendered.
        $counter = 0;
        foreach ($searchparams as $searchparam) {

            if (!$key = key($searchparam)) {
                throw new moodle_exception('wrongstructureofsearchparams', 'mod_booking');
            }
            $value = $searchparam[$key];

            // Only add Or if we are not in the first line.
            $where .= $counter > 0 ? ' OR ' : ' AND (';

            $value = "%\"$key\"\:%$value%";

            // Make sure we never use the param more than once.
            if (isset($params[$key])) {
                $key = $key . $counter;
            }

            $where .= $DB->sql_like('s1.userobjects', ":$key", false);

            // Now we have to add the values to our params array.
            $params[$key] = $value;
            $counter++;
        }
        // If we ran through the loop at least once, we close it again here.
        $where .= $counter > 0 ? ') ' : '';

        return [$select, $from, $where, $params];
    }

and than in file classes/booking.php I updated this code:

public static function get_options_filter_sql($limitfrom = 0,
                                                $limitnum = 0,
                                                $searchtext = '',
                                                $fields = null,
                                                $context = null,
                                                $filterarray = [],
                                                $wherearray = [],
                                                $userid = null,
                                                $bookingparam = STATUSPARAM_BOOKED,
                                                $additionalwhere = '') {

        global $DB;

        $groupby = " bo.id ";

        if (empty($fields)) {
            $fields = "DISTINCT s1.*";
        }

        $where = '';

        $filter = '';

        $params = [];

        $outerfrom = "(
                        SELECT DISTINCT bo.* ";

        $innerfrom = "FROM {booking_options} bo";

        // If the user does not have the capability to see invisible options...
        if (!$context || !has_capability('mod/booking:canseeinvisibleoptions', $context)) {
            // ... then only show visible options.
            $where = "invisible = 0 ";
        } else {
            // The "Where"-clause is always added so we have to have something here for the sql to work.
            $where = "1=1 ";
        }

        if ($userid !== null) {
            $innerfrom .= " JOIN {booking_answers} ba
                          ON ba.optionid=bo.id ";

            $outerfrom .= ", ba.waitinglist, ba.userid as bookeduserid ";
            $where .= " AND waitinglist=:bookingparam
                        AND bookeduserid=:bookeduserid ";
            $groupby .= " , ba.waitinglist, ba.userid ";

            $params['bookeduserid'] = $userid;
            $params['bookingparam'] = $bookingparam;
        }

        // Instead of "where" we return "filter". This is to support the filter functionality of wunderbyte table.
        list($select1, $from1, $filter1, $params1) = booking_option_settings::return_sql_for_customfield();
        list($select2, $from2, $filter2, $params2) = booking_option_settings::return_sql_for_teachers();
        list($select3, $from3, $filter3, $params3) = booking_option_settings::return_sql_for_imagefiles();
        list($select4, $from4, $filter4, $params4) = booking_option_settings::return_sql_for_users();

        // The $outerfrom takes all the select from the supplementary selects.
        $outerfrom .= !empty($select1) ? ", $select1 " : '';
        $outerfrom .= !empty($select2) ? ", $select2 " : '';
        $outerfrom .= !empty($select3) ? ", $select3 " : '';
        $outerfrom .= !empty($select4) ? ", $select4 " : '';

        // The innerfrom takes all the froms from the supplementary froms.
        $innerfrom .= " $from1 ";
        $innerfrom .= " $from2 ";
        $innerfrom .= " $from3 ";
        $innerfrom .= " $from4 ";

        $pattern = '/as.*?,/';
        $addgroupby = preg_replace($pattern, ',', $select1 . ",");

        $groupby .= !empty($addgroupby) ? ' , ' . $addgroupby : '';

        $addgroupby = preg_replace($pattern, ',', $select3 . ",");
        $groupby .= !empty($addgroupby) ? ' , ' . $addgroupby : '';

        $groupbyarray = (array)explode(',', $groupby);

        foreach ($groupbyarray as $key => $value) {
            if (empty(trim($value))) {
                unset($groupbyarray[$key]);
            }
        }

        $groupby = implode(" , ", $groupbyarray);

        // Now we merge all the params arrays.
        $params = array_merge($params, $params1, $params2, $params3, $params4);

        // We build everything together.
        $from = $outerfrom;
        $from .= $innerfrom;

        // Finally, we add the outer group by.
        $groupby = "GROUP BY " . $groupby . "
                    ) s1";

        $from .= $groupby;

        // Add the where at the right place.
        $filter .= " $filter1 ";
        $filter .= " $filter2 ";
        $filter .= " $filter3 ";
        $filter .= " $filter4 ";

        $counter = 1;
        foreach ($filterarray as $key => $value) {

            // Be sure to have a lower key string.
            $paramsvaluekey = "param";
            while (isset($params[$paramsvaluekey])) {
                $paramsvaluekey .= $counter;
                $counter++;
            }

            if (gettype($value) == 'integer') {
                $filter .= " AND   $key = $value";
            } else {
                $filter .= " AND " . $DB->sql_like("$key", ":$paramsvaluekey", false);
                $params[$paramsvaluekey] = $value;
            }
        }

        foreach ($wherearray as $key => $value) {

            // Be sure to have a lower key string.
            $paramsvaluekey = "param";
            while (isset($params[$paramsvaluekey])) {
                $paramsvaluekey .= $counter;
                $counter++;
            }

            if (gettype($value) == 'integer') {
                $where .= " AND   $key = $value";
            } else {
                $where .= " AND " . $DB->sql_like("$key", ":$paramsvaluekey", false);
                $params[$paramsvaluekey] = $value;
            }
        }

        // We add additional conditions to $where, if there are any.
        if (!empty($additionalwhere)) {
            $where .= " AND " . $additionalwhere;
        }

        return [$fields, $from, $where, $params, $filter];

    }

But it's not working. I missed something?

Tnx.

[georgmaisser]

Did it work out in the end? Actually, it seems pretty ok, you just need to make sure to add the new column to the fulltextsearch column, like teachers are right now.

Also, every call is cached, this is sth to keep in mind during development!

[atlet]

Yes, now it's working fine.