| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentBooking\App\Modules\MCP\Tools; |
| 4 |
|
| 5 |
use FluentBooking\App\Models\CalendarSlot; |
| 6 |
use FluentBooking\App\Modules\MCP\Support\MCPHelper; |
| 7 |
use FluentBooking\App\Modules\MCP\Support\PermissionGate; |
| 8 |
use FluentBooking\App\Services\BookingReportService; |
| 9 |
use FluentBooking\Framework\Support\Arr; |
| 10 |
|
| 11 |
defined('ABSPATH') || exit; |
| 12 |
|
| 13 |
/** |
| 14 |
* One aggregation tool: "group by X, measure Y" covers stats, trends, top |
| 15 |
* events and host utilization with a single schema. |
| 16 |
* |
| 17 |
* Returns aggregates only, never booking rows. Rows come from list-bookings, |
| 18 |
* which paginates and masks PII. |
| 19 |
*/ |
| 20 |
class ReportTools |
| 21 |
{ |
| 22 |
public static function definitions() |
| 23 |
{ |
| 24 |
return [ |
| 25 |
'fluent-booking/query-bookings' => [ |
| 26 |
'label' => __('Query bookings', 'fluent-booking'), |
| 27 |
'description' => __('Aggregate bookings by one or two dimensions. Answers "how many bookings per host last month", "which event types get cancelled most", "what hours do people book". Returns totals only, never booking rows.', 'fluent-booking'), |
| 28 |
'input_schema' => [ |
| 29 |
'type' => 'object', |
| 30 |
'properties' => [ |
| 31 |
'group_by' => [ |
| 32 |
'type' => 'array', |
| 33 |
'description' => __('One or two dimensions. Omit for a single total over the whole range.', 'fluent-booking'), |
| 34 |
'items' => [ |
| 35 |
'type' => 'string', |
| 36 |
'enum' => array_keys(BookingReportService::dimensions()), |
| 37 |
], |
| 38 |
], |
| 39 |
'metrics' => [ |
| 40 |
'type' => 'array', |
| 41 |
'description' => __('Defaults to count. Rates are fractions of that group\'s bookings, 0 to 1.', 'fluent-booking'), |
| 42 |
'items' => [ |
| 43 |
'type' => 'string', |
| 44 |
'enum' => BookingReportService::metrics(), |
| 45 |
], |
| 46 |
], |
| 47 |
'date_field' => [ |
| 48 |
'type' => 'string', |
| 49 |
'description' => __('Which timestamp the range and the day/month/weekday/hour dimensions read. start_time is when the meeting is; created_at is when it was booked. Defaults to start_time.', 'fluent-booking'), |
| 50 |
'enum' => BookingReportService::dateFields(), |
| 51 |
], |
| 52 |
'from' => [ |
| 53 |
'type' => 'string', |
| 54 |
'description' => __('Y-m-d, inclusive. Defaults to 29 days before to.', 'fluent-booking'), |
| 55 |
], |
| 56 |
'to' => [ |
| 57 |
'type' => 'string', |
| 58 |
'description' => __('Y-m-d, inclusive. Defaults to today. Maximum span 366 days.', 'fluent-booking'), |
| 59 |
], |
| 60 |
'timezone' => [ |
| 61 |
'type' => 'string', |
| 62 |
'description' => __('IANA zone the day, weekday and hour buckets are expressed in. Defaults to the site timezone.', 'fluent-booking'), |
| 63 |
], |
| 64 |
'filters' => [ |
| 65 |
'type' => 'object', |
| 66 |
'description' => __('Narrow the set before grouping. Any of: status (array), event_id, calendar_id, host_id, event_type, source.', 'fluent-booking'), |
| 67 |
], |
| 68 |
'having' => [ |
| 69 |
'type' => 'object', |
| 70 |
'description' => __('Drop small groups, e.g. {"metric":"count","op":">=","value":5}.', 'fluent-booking'), |
| 71 |
], |
| 72 |
'order_by' => [ |
| 73 |
'type' => 'string', |
| 74 |
'description' => __('A metric or a grouped dimension. Time series default to chronological, everything else to largest first.', 'fluent-booking'), |
| 75 |
], |
| 76 |
'order' => [ |
| 77 |
'type' => 'string', |
| 78 |
'enum' => ['asc', 'desc'], |
| 79 |
], |
| 80 |
'limit' => [ |
| 81 |
'type' => 'integer', |
| 82 |
'description' => __('Groups to return. Default 50, maximum 200.', 'fluent-booking'), |
| 83 |
], |
| 84 |
], |
| 85 |
], |
| 86 |
'annotations' => [ |
| 87 |
'title' => __('Query bookings', 'fluent-booking'), |
| 88 |
'readonly' => true, |
| 89 |
], |
| 90 |
'permission_callback' => [PermissionGate::class, 'readGate'], |
| 91 |
'execute_callback' => [self::class, 'queryBookings'], |
| 92 |
], |
| 93 |
]; |
| 94 |
} |
| 95 |
|
| 96 |
/** |
| 97 |
* @param array $params |
| 98 |
* @return array|\WP_Error |
| 99 |
*/ |
| 100 |
public static function queryBookings($params = []) |
| 101 |
{ |
| 102 |
$timezone = MCPHelper::resolveTimezone(Arr::get($params, 'timezone', '')); |
| 103 |
|
| 104 |
$result = BookingReportService::aggregate([ |
| 105 |
'group_by' => Arr::get($params, 'group_by', []), |
| 106 |
'metrics' => Arr::get($params, 'metrics', []), |
| 107 |
'date_field' => Arr::get($params, 'date_field', 'start_time'), |
| 108 |
'from' => Arr::get($params, 'from'), |
| 109 |
'to' => Arr::get($params, 'to'), |
| 110 |
'timezone' => $timezone, |
| 111 |
'filters' => Arr::get($params, 'filters', []), |
| 112 |
'having' => Arr::get($params, 'having'), |
| 113 |
'order_by' => Arr::get($params, 'order_by', ''), |
| 114 |
'order' => Arr::get($params, 'order', ''), |
| 115 |
'limit' => Arr::get($params, 'limit', 50), |
| 116 |
]); |
| 117 |
|
| 118 |
if (is_wp_error($result)) { |
| 119 |
return MCPHelper::error($result->get_error_code(), $result->get_error_message()); |
| 120 |
} |
| 121 |
|
| 122 |
$rows = self::labelRows($result['rows'], $result['group_by']); |
| 123 |
|
| 124 |
$meta = [ |
| 125 |
'date_field' => $result['date_field'], |
| 126 |
'from' => $result['range']['from'], |
| 127 |
'to' => $result['range']['to'], |
| 128 |
'days' => $result['range']['days'], |
| 129 |
'group_count' => count($rows), |
| 130 |
'group_by' => $result['group_by'], |
| 131 |
'group_by_labels' => self::dimensionLabels($result['group_by']), |
| 132 |
'timezone' => $timezone, |
| 133 |
'scope' => PermissionGate::currentScope(), |
| 134 |
]; |
| 135 |
|
| 136 |
if ($result['truncated']) { |
| 137 |
$meta['truncated'] = true; |
| 138 |
$meta['truncation_note'] = sprintf( |
| 139 |
/* translators: %d: the number of groups returned */ |
| 140 |
__('More groups matched than the limit of %d. Raise limit, add a having filter, or narrow the range.', 'fluent-booking'), |
| 141 |
$result['limit'] |
| 142 |
); |
| 143 |
} |
| 144 |
|
| 145 |
// Time buckets are shifted by a fixed offset, so report which one. |
| 146 |
if (self::hasTimeDimension($result['group_by'])) { |
| 147 |
$offset = BookingReportService::offsetSeconds($timezone, $result['range']['from']); |
| 148 |
$meta['bucket_offset'] = sprintf('%s%02d:%02d', $offset < 0 ? '-' : '+', abs($offset) / 3600, (abs($offset) % 3600) / 60); |
| 149 |
$meta['bucket_note'] = __('Day, weekday and hour buckets use one fixed offset for the whole range. A range crossing a daylight-saving change can place bookings on the far side an hour out.', 'fluent-booking'); |
| 150 |
} |
| 151 |
|
| 152 |
return MCPHelper::success( |
| 153 |
[ |
| 154 |
'rows' => $rows, |
| 155 |
'totals' => self::totals($result['rows'], $result['metrics']), |
| 156 |
], |
| 157 |
$meta, |
| 158 |
$rows ? '' : 'No bookings matched. Widen the range, or drop a filter.' |
| 159 |
); |
| 160 |
} |
| 161 |
|
| 162 |
/** |
| 163 |
* Human names for the grouped dimensions, for table headers. |
| 164 |
* |
| 165 |
* @return array |
| 166 |
*/ |
| 167 |
private static function dimensionLabels($groupBy) |
| 168 |
{ |
| 169 |
$dimensions = BookingReportService::dimensions(); |
| 170 |
$labels = []; |
| 171 |
|
| 172 |
foreach ((array) $groupBy as $dimension) { |
| 173 |
if (isset($dimensions[$dimension]['label'])) { |
| 174 |
$labels[$dimension] = $dimensions[$dimension]['label']; |
| 175 |
} |
| 176 |
} |
| 177 |
|
| 178 |
return $labels; |
| 179 |
} |
| 180 |
|
| 181 |
/** |
| 182 |
* Resolve host and event ids to names, one query per dimension. |
| 183 |
* |
| 184 |
* @return array |
| 185 |
*/ |
| 186 |
private static function labelRows($rows, $groupBy) |
| 187 |
{ |
| 188 |
if (!$rows) { |
| 189 |
return $rows; |
| 190 |
} |
| 191 |
|
| 192 |
$labels = []; |
| 193 |
|
| 194 |
foreach ($groupBy as $dimension) { |
| 195 |
if ($dimension === 'event') { |
| 196 |
$ids = array_unique(array_filter(array_column($rows, 'event'))); |
| 197 |
$labels['event'] = $ids |
| 198 |
? CalendarSlot::whereIn('id', $ids)->pluck('title', 'id')->toArray() |
| 199 |
: []; |
| 200 |
} |
| 201 |
|
| 202 |
if ($dimension === 'host') { |
| 203 |
$ids = array_unique(array_filter(array_column($rows, 'host'))); |
| 204 |
|
| 205 |
// One query for the lot rather than one per host: a report can |
| 206 |
// return up to 200 groups. |
| 207 |
if ($ids) { |
| 208 |
cache_users(array_map('intval', $ids)); |
| 209 |
} |
| 210 |
|
| 211 |
foreach ($ids as $id) { |
| 212 |
$user = get_userdata($id); |
| 213 |
$labels['host'][$id] = $user ? MCPHelper::untrusted($user->display_name, 200) : sprintf('#%d', $id); |
| 214 |
} |
| 215 |
} |
| 216 |
|
| 217 |
if ($dimension === 'weekday') { |
| 218 |
// MySQL DAYOFWEEK is 1 = Sunday. |
| 219 |
$labels['weekday'] = [ |
| 220 |
1 => __('Sunday', 'fluent-booking'), |
| 221 |
2 => __('Monday', 'fluent-booking'), |
| 222 |
3 => __('Tuesday', 'fluent-booking'), |
| 223 |
4 => __('Wednesday', 'fluent-booking'), |
| 224 |
5 => __('Thursday', 'fluent-booking'), |
| 225 |
6 => __('Friday', 'fluent-booking'), |
| 226 |
7 => __('Saturday', 'fluent-booking'), |
| 227 |
]; |
| 228 |
} |
| 229 |
} |
| 230 |
|
| 231 |
if (!$labels) { |
| 232 |
return $rows; |
| 233 |
} |
| 234 |
|
| 235 |
foreach ($rows as &$row) { |
| 236 |
foreach ($labels as $dimension => $map) { |
| 237 |
if (isset($row[$dimension]) && isset($map[$row[$dimension]])) { |
| 238 |
$row[$dimension . '_label'] = $map[$row[$dimension]]; |
| 239 |
} |
| 240 |
} |
| 241 |
} |
| 242 |
|
| 243 |
return $rows; |
| 244 |
} |
| 245 |
|
| 246 |
/** |
| 247 |
* Column totals. Rates are omitted: averaging per-group rates isn't the |
| 248 |
* overall rate, and the numerators aren't in the response. |
| 249 |
* |
| 250 |
* @return array |
| 251 |
*/ |
| 252 |
private static function totals($rows, $metrics) |
| 253 |
{ |
| 254 |
$totals = []; |
| 255 |
|
| 256 |
foreach (['count', 'distinct_attendees', 'total_minutes'] as $metric) { |
| 257 |
if (in_array($metric, $metrics, true)) { |
| 258 |
$totals[$metric] = array_sum(array_column($rows, $metric)); |
| 259 |
} |
| 260 |
} |
| 261 |
|
| 262 |
// distinct_attendees can't be summed without double-counting. |
| 263 |
if (isset($totals['distinct_attendees'])) { |
| 264 |
$totals['distinct_attendees_note'] = __('Summed across groups, so an attendee in two groups is counted twice.', 'fluent-booking'); |
| 265 |
} |
| 266 |
|
| 267 |
return $totals; |
| 268 |
} |
| 269 |
|
| 270 |
/** |
| 271 |
* @return bool |
| 272 |
*/ |
| 273 |
private static function hasTimeDimension($groupBy) |
| 274 |
{ |
| 275 |
return (bool) array_intersect((array) $groupBy, ['day', 'month', 'weekday', 'hour']); |
| 276 |
} |
| 277 |
} |
| 278 |
|
