| @@ -5,9 +5,8 @@ | ||
| 5 | 5 | use FluentSupport\App\Models\Agent; |
| 6 | 6 | use FluentSupport\App\Models\AgentGroup; |
| 7 | 7 | use FluentSupport\App\Models\Conversation; |
| 8 | 8 | use FluentSupport\App\Models\MailBox; |
| 9 | -use FluentSupport\App\Models\Meta; | |
| 10 | 9 | use FluentSupport\App\Models\Person; |
| 11 | 10 | use FluentSupport\App\Models\Product; |
| 12 | 11 | use FluentSupport\App\Models\TagPivot; |
| 13 | 12 | use FluentSupport\App\Models\Ticket; |
| @@ -18,12 +17,9 @@ | ||
| 18 | 17 | use FluentSupport\Framework\Support\Arr; |
| 19 | 18 | use FluentSupport\Framework\Support\DateTime; |
| 20 | 19 | |
| 21 | 20 | /** |
| 22 | - * Reporting class is responsible for getting data related to report | |
| 23 | - * @package FluentSupport\App\Modules\Reporting | |
| 24 | - * | |
| 25 | - * @version 1.0.0 | |
| 21 | + * Ticket, response and agent reports, scoped to the current agent's mailboxes. | |
| 26 | 22 | */ |
| 27 | 23 | class Reporting |
| 28 | 24 | { |
| 29 | 25 | use ReportingHelperTrait; |
| @@ -28,27 +24,25 @@ | ||
| 28 | 24 | { |
| 29 | 25 | use ReportingHelperTrait; |
| 30 | 26 | |
| 31 | 27 | /** |
| 32 | - * getTicketsGrowth will generate tickets statistics and return | |
| 33 | - * @param false $from | |
| 34 | - * @param false $to | |
| 28 | + * New tickets per period. | |
| 29 | + * | |
| 30 | + * @param string|false $from | |
| 31 | + * @param string|false $to | |
| 35 | 32 | * @param array $filters |
| 36 | 33 | * @return array |
| 37 | 34 | */ |
| 38 | 35 | public function getTicketsGrowth($from = false, $to = false, $filters = []) |
| 39 | 36 | { |
| 40 | - //Generate report period | |
| 41 | 37 | $period = $this->makeDatePeriod( |
| 42 | - $from = $this->makeFromDate($from),//Date from | |
| 43 | - $to = $this->makeToDate($to),//Date to | |
| 44 | - $frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M | |
| 38 | + $from = $this->makeFromDate($from), | |
| 39 | + $to = $this->makeToDate($to), | |
| 40 | + $frequency = $this->getFrequency($from, $to) | |
| 45 | 41 | ); |
| 46 | 42 | |
| 47 | - //Get group by and order by i.e date,week, month | |
| 48 | 43 | list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency); |
| 49 | 44 | |
| 50 | - //get all tickets statistics within the date range | |
| 51 | 45 | $query = $this->db()->table('fs_tickets') |
| 52 | 46 | ->select($this->prepareSelect($frequency)) |
| 53 | 47 | ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) |
| 54 | 48 | ->groupBy($groupBy) |
| @@ -53,12 +47,11 @@ | ||
| 53 | 47 | ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) |
| 54 | 48 | ->groupBy($groupBy) |
| 55 | 49 | ->oldest($orderBy); |
| 56 | 50 | |
| 57 | - // Bound first: a caller-supplied mailbox_id below is not proof of access. | |
| 51 | + // Scope first. A mailbox_id filter below is not proof of access. | |
| 58 | 52 | (new AgentTicketAccess())->applyMailboxRestrictionScope($query); |
| 59 | 53 | |
| 60 | - //If filter by product or agent or status selected | |
| 61 | 54 | if ($filters) { |
| 62 | 55 | if (!empty($filters['statuses'])) { |
| 63 | 56 | $query->whereIn('status', $filters['statuses']); |
| 64 | 57 | } |
| @@ -85,11 +78,12 @@ | ||
| 85 | 78 | return $this->getResult($period, $items); |
| 86 | 79 | } |
| 87 | 80 | |
| 88 | 81 | /** |
| 89 | - * getTicketResolveGrowth method will get the statistics for resolved/closed tickets | |
| 90 | - * @param false $from | |
| 91 | - * @param false $to | |
| 82 | + * Closed tickets per period, by resolved_at. | |
| 83 | + * | |
| 84 | + * @param string|false $from | |
| 85 | + * @param string|false $to | |
| 92 | 86 | * @param array $filters |
| 93 | 87 | * @return array |
| 94 | 88 | */ |
| 95 | 89 | public function getTicketResolveGrowth($from = false, $to = false, $filters = [], $type = '') |
| @@ -94,18 +88,17 @@ | ||
| 94 | 88 | */ |
| 95 | 89 | public function getTicketResolveGrowth($from = false, $to = false, $filters = [], $type = '') |
| 96 | 90 | { |
| 97 | 91 | $period = $this->makeDatePeriod( |
| 98 | - $from = $this->makeFromDate($from),//Date from | |
| 99 | - $to = $this->makeToDate($to),//date to | |
| 100 | - $frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M | |
| 92 | + $from = $this->makeFromDate($from), | |
| 93 | + $to = $this->makeToDate($to), | |
| 94 | + $frequency = $this->getFrequency($from, $to) | |
| 101 | 95 | ); |
| 102 | 96 | |
| 103 | - list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);//Get group by and order by i.e date,week, month | |
| 97 | + list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency); | |
| 104 | 98 | |
| 105 | 99 | $filterColumn = (!empty($type)) ? $type.'_id' : 'id'; |
| 106 | 100 | |
| 107 | - //get the closed ticket statistics within the date range | |
| 108 | 101 | $query = $this->db()->table('fs_tickets') |
| 109 | 102 | ->select($this->prepareSelect($frequency, 'resolved_at')) |
| 110 | 103 | ->whereBetween('resolved_at', $this->prepareBetween($frequency, $from, $to)) |
| 111 | 104 | ->where('status', 'closed') |
| @@ -114,9 +107,8 @@ | ||
| 114 | 107 | ->oldest($orderBy); |
| 115 | 108 | |
| 116 | 109 | (new AgentTicketAccess())->applyMailboxRestrictionScope($query); |
| 117 | 110 | |
| 118 | - //If filter by product or agent is selected | |
| 119 | 111 | if ($filters) { |
| 120 | 112 | if (!empty($filters['product_id'])) { |
| 121 | 113 | $query->where('product_id', $filters['product_id']); |
| 122 | 114 | } |
| @@ -139,11 +131,12 @@ | ||
| 139 | 131 | return $this->getResult($period, $items); |
| 140 | 132 | } |
| 141 | 133 | |
| 142 | 134 | /** |
| 143 | - * getResponseGrowth method will generate the statistics for response | |
| 144 | - * @param false $from | |
| 145 | - * @param false $to | |
| 135 | + * Responses per period. | |
| 136 | + * | |
| 137 | + * @param string|false $from | |
| 138 | + * @param string|false $to | |
| 146 | 139 | * @param array $filters |
| 147 | 140 | * @return array |
| 148 | 141 | */ |
| 149 | 142 | public function getResponseGrowth($from = false, $to = false, $filters = []) |
| @@ -237,12 +230,13 @@ | ||
| 237 | 230 | )->get(); |
| 238 | 231 | } |
| 239 | 232 | |
| 240 | 233 | /** |
| 241 | - * agentSummary method will prepare ticket summary with responses by agent | |
| 242 | - * @param false $from | |
| 243 | - * @param false $to | |
| 244 | - * @param false $agent | |
| 234 | + * Per-agent ticket and response counts for a date range (default today). | |
| 235 | + * | |
| 236 | + * @param string|false $from | |
| 237 | + * @param string|false $to | |
| 238 | + * @param string|false $agent Comma-separated agent ids. | |
| 245 | 239 | * @return mixed |
| 246 | 240 | */ |
| 247 | 241 | public function agentSummary($from = false, $to = false, $agent = false) |
| 248 | 242 | { |
| @@ -259,9 +253,8 @@ | ||
| 259 | 253 | $reports = []; |
| 260 | 254 | |
| 261 | 255 | $access = new AgentTicketAccess(); |
| 262 | 256 | |
| 263 | - //Get tickets statistics that are closed | |
| 264 | 257 | $resolves = $this->db()->table('fs_tickets') |
| 265 | 258 | ->select([ |
| 266 | 259 | $this->db()->raw('COUNT(id) AS count'), |
| 267 | 260 | 'agent_id', |
| @@ -273,9 +266,9 @@ | ||
| 273 | 266 | $resolves = $access->applyMailboxRestrictionScope($resolves)->get(); |
| 274 | 267 | |
| 275 | 268 | $reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id'); |
| 276 | 269 | |
| 277 | - //get statistics for all except closed ticket | |
| 270 | + // Open tickets aren't limited to the date range | |
| 278 | 271 | $openTickets = $this->db()->table('fs_tickets') |
| 279 | 272 | ->select([ |
| 280 | 273 | $this->db()->raw('COUNT(id) AS count'), |
| 281 | 274 | 'agent_id' |
| @@ -285,9 +278,8 @@ | ||
| 285 | 278 | |
| 286 | 279 | $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get(); |
| 287 | 280 | |
| 288 | 281 | $reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id'); |
| 289 | - //Get response by agent | |
| 290 | 282 | $responses = $access->applyMailboxRestrictionScopeViaTicket( |
| 291 | 283 | Conversation::select([ |
| 292 | 284 | $this->db()->raw('COUNT(id) AS count'), |
| 293 | 285 | $this->db()->raw('person_id as agent_id'), |
| @@ -299,9 +291,8 @@ | ||
| 299 | 291 | ->groupBy('agent_id') |
| 300 | 292 | )->get(); |
| 301 | 293 | |
| 302 | 294 | $reports = $this->pushReportData('responses', $responses, $reports, 'agent_id'); |
| 303 | - //Get interactions/responses by individual agents | |
| 304 | 295 | foreach ($this->interactionCountsByAgent($from, $to, $access) as $row) { |
| 305 | 296 | if (isset($reports[$row->agent_id])) { |
| 306 | 297 | $reports[$row->agent_id]['interactions'] = (int) $row->count; |
| 307 | 298 | } |
| @@ -312,52 +303,39 @@ | ||
| 312 | 303 | if ($agent) { |
| 313 | 304 | $agentIds = array_map('intval', explode(',', $agent)); |
| 314 | 305 | } |
| 315 | 306 | |
| 316 | - // get agent feedback statistics | |
| 317 | 307 | $agentFeedbackRatingEnabled = Helper::getBusinessSettings('agent_feedback_rating') === 'yes'; |
| 318 | 308 | if (defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled) { |
| 319 | - $agentConversations = $access->applyMailboxRestrictionScopeViaTicket( | |
| 320 | - Conversation::select([ | |
| 321 | - $this->db()->raw('person_id as agent_id'), | |
| 322 | - $this->db()->raw('GROUP_CONCAT(id) as conversation_ids') | |
| 309 | + global $wpdb; | |
| 310 | + $prefix = $wpdb->prefix; | |
| 311 | + | |
| 312 | + // One grouped join, not GROUP_CONCAT of response ids, which | |
| 313 | + // truncates at group_concat_max_len (1024 bytes) for busy agents. | |
| 314 | + $feedbackRows = $this->db()->table('fs_conversations') | |
| 315 | + ->join('fs_meta', 'fs_meta.object_id', '=', 'fs_conversations.id') | |
| 316 | + ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id') | |
| 317 | + ->select([ | |
| 318 | + $this->db()->raw($prefix . 'fs_conversations.person_id AS agent_id'), | |
| 319 | + $this->db()->raw("SUM(CASE WHEN {$prefix}fs_meta.value = 'like' THEN 1 ELSE 0 END) AS likes"), | |
| 320 | + $this->db()->raw("SUM(CASE WHEN {$prefix}fs_meta.value = 'dislike' THEN 1 ELSE 0 END) AS dislikes"), | |
| 323 | 321 | ]) |
| 324 | - ->whereIn('person_id', $agentIds) | |
| 325 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 326 | - ->where('conversation_type', 'response') | |
| 327 | - ->groupBy('agent_id') | |
| 328 | - )->get(); | |
| 322 | + ->where('fs_meta.object_type', 'conversation_meta') | |
| 323 | + ->where('fs_meta.key', 'agent_feedback_ratings') | |
| 324 | + ->whereBetween('fs_meta.created_at', [$from, $to]) | |
| 325 | + ->where('fs_conversations.conversation_type', 'response') | |
| 326 | + ->whereIn('fs_conversations.person_id', $agentIds) | |
| 327 | + ->whereIn('fs_conversations.person_id', Agent::getAgentIds()) | |
| 328 | + ->groupBy('fs_conversations.person_id'); | |
| 329 | 329 | |
| 330 | - foreach ($agentConversations as $conversation) { | |
| 331 | - $conversationIds = array_map('intval', explode(',', $conversation->conversation_ids)); | |
| 332 | - | |
| 333 | - $feedbackMeta = Meta::whereIn('object_id', $conversationIds) | |
| 334 | - ->where('key', 'agent_feedback_ratings') | |
| 335 | - ->whereBetween('created_at', [$from, $to]) | |
| 336 | - ->get(); | |
| 337 | - | |
| 338 | - $likeCount = 0; | |
| 339 | - $dislikeCount = 0; | |
| 340 | - | |
| 341 | - foreach ($feedbackMeta as $feedback) { | |
| 342 | - $feedbackStatus = $feedback->value; | |
| 343 | - | |
| 344 | - if ($feedbackStatus === 'like') { | |
| 345 | - $likeCount++; | |
| 346 | - } elseif ($feedbackStatus === 'dislike') { | |
| 347 | - $dislikeCount++; | |
| 348 | - } | |
| 349 | - } | |
| 350 | - | |
| 351 | - $agentId = $conversation->agent_id; | |
| 352 | - $reports[$agentId]['likes'] = $likeCount; | |
| 353 | - $reports[$agentId]['dislikes'] = $dislikeCount; | |
| 330 | + foreach ($access->applyMailboxRestrictionScope($feedbackRows)->get() as $row) { | |
| 331 | + $reports[$row->agent_id]['likes'] = (int) $row->likes; | |
| 332 | + $reports[$row->agent_id]['dislikes'] = (int) $row->dislikes; | |
| 354 | 333 | } |
| 355 | 334 | } |
| 356 | 335 | |
| 357 | - // Without an explicit agent filter, report on every agent — $reports | |
| 358 | - // only holds the ones with activity in the range, so restricting to its | |
| 359 | - // keys would silently drop agents who simply had a quiet period. | |
| 336 | + // Without an agent filter, list every agent, including those with no | |
| 337 | + // activity in the range. | |
| 360 | 338 | $agentsQuery = Agent::select(['id', 'first_name', 'last_name', 'email']); |
| 361 | 339 | |
| 362 | 340 | if ($agent) { |
| 363 | 341 | $agentsQuery->whereIn('id', $agentIds); |
| @@ -375,10 +353,9 @@ | ||
| 375 | 353 | |
| 376 | 354 | $additionalFields = defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled ? ['likes' => 0, 'dislikes' => 0] : []; |
| 377 | 355 | $reportFields = $reportFields + $additionalFields; |
| 378 | 356 | |
| 379 | - // One grouped query instead of one per agent — this summary lists every | |
| 380 | - // agent, so a per-agent call scales with the whole agent table. | |
| 357 | + // One grouped query for all agents instead of one per agent. | |
| 381 | 358 | $waitStats = $this->getWaitStatsByAgents($agents->pluck('id')->toArray()); |
| 382 | 359 | $emptyActiveStat = ['average_waiting' => 0, 'max_waiting' => 0, 'waiting_tickets' => 0]; |
| 383 | 360 | |
| 384 | 361 | foreach ($agents as $agent) { |
| @@ -419,9 +396,8 @@ | ||
| 419 | 396 | ->groupBy($groupByField) |
| 420 | 397 | ->where('status', 'closed') |
| 421 | 398 | ->whereBetween('resolved_at', [$from, $to]); |
| 422 | 399 | |
| 423 | - // Aggregates count tickets too, so the same mailbox boundary applies. | |
| 424 | 400 | $resolves = $access->applyMailboxRestrictionScope($resolves)->get(); |
| 425 | 401 | |
| 426 | 402 | $reports = $this->pushReportData('closed', $resolves, $reports, $groupByField); |
| 427 | 403 | |
| @@ -450,14 +426,10 @@ | ||
| 450 | 426 | $responses = $access->applyMailboxRestrictionScope($responses)->get(); |
| 451 | 427 | |
| 452 | 428 | $reports = $this->pushReportData('responses', $responses, $reports, $groupByField); |
| 453 | 429 | |
| 454 | - // Interactions = how many distinct tickets were replied to in the range. | |
| 455 | - // This used to GROUP_CONCAT every ticket id per group and feed them back | |
| 456 | - // in a whereIn, one query per group. group_concat_max_len is 1024 bytes | |
| 457 | - // by default, so any group past ~170 tickets had its id list silently | |
| 458 | - // truncated and the count came out far too low. One grouped | |
| 459 | - // COUNT(DISTINCT) has no such limit and drops the per-group queries. | |
| 430 | + // Interactions = distinct tickets replied to in the range. Use COUNT(DISTINCT), | |
| 431 | + // not GROUP_CONCAT, which truncates at group_concat_max_len (1024 bytes). | |
| 460 | 432 | $interactions = $this->db()->table('fs_conversations') |
| 461 | 433 | ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id') |
| 462 | 434 | ->select([ |
| 463 | 435 | 'fs_tickets.' . $groupByField, |
| @@ -507,11 +479,10 @@ | ||
| 507 | 479 | return $items; |
| 508 | 480 | } |
| 509 | 481 | |
| 510 | 482 | /** |
| 511 | - * agentGroupSummary method will prepare ticket summary aggregated by agent group | |
| 512 | - * @param false $from | |
| 513 | - * @param false $to | |
| 483 | + * @param string|false $from | |
| 484 | + * @param string|false $to | |
| 514 | 485 | * @return mixed |
| 515 | 486 | */ |
| 516 | 487 | public function agentGroupSummary($from = false, $to = false) |
| 517 | 488 | { |
| @@ -525,9 +496,9 @@ | ||
| 525 | 496 | |
| 526 | 497 | $from .= ' 00:00:00'; |
| 527 | 498 | $to .= ' 23:59:59'; |
| 528 | 499 | |
| 529 | - // Get per-agent stats using the same queries as agentSummary | |
| 500 | + // Same per-agent queries as agentSummary() | |
| 530 | 501 | $reports = []; |
| 531 | 502 | |
| 532 | 503 | $access = new AgentTicketAccess(); |
| 533 | 504 | |
| @@ -574,15 +545,13 @@ | ||
| 574 | 545 | $reports[$row->agent_id]['interactions'] = (int) $row->count; |
| 575 | 546 | } |
| 576 | 547 | } |
| 577 | 548 | |
| 578 | - // Get group -> agent_id mappings | |
| 579 | 549 | $groupAgentMap = TagPivot::where('source_type', 'agent_group') |
| 580 | 550 | ->select(['tag_id', 'source_id']) |
| 581 | 551 | ->get() |
| 582 | 552 | ->groupBy('tag_id'); |
| 583 | 553 | |
| 584 | - // Get all agent groups | |
| 585 | 554 | $groups = AgentGroup::select(['id', 'title'])->get(); |
| 586 | 555 | |
| 587 | 556 | $defaultStats = [ |
| 588 | 557 | 'responses' => 0, |
| @@ -616,9 +585,10 @@ | ||
| 616 | 585 | return $groups; |
| 617 | 586 | } |
| 618 | 587 | |
| 619 | 588 | /** |
| 620 | - * pushReportData method will format the ticket summary report | |
| 589 | + * Merge grouped counts into $reports[groupKey][$type]. | |
| 590 | + * | |
| 621 | 591 | * @param $type |
| 622 | 592 | * @param $tickets |
| 623 | 593 | * @param $reports |
| 624 | 594 | * @param $groupByField |
| @@ -643,16 +613,15 @@ | ||
| 643 | 613 | return $reports; |
| 644 | 614 | } |
| 645 | 615 | |
| 646 | 616 | /** |
| 647 | - * getActiveStats method will return the statistics for active tickets | |
| 648 | - * This method will get the list of open tickets calculate the wait times and return results | |
| 617 | + * Wait-time stats for open tickets awaiting an agent reply. | |
| 618 | + * | |
| 649 | 619 | * @param int|null $productId Narrow to one product's tickets |
| 650 | 620 | * @return array|false |
| 651 | 621 | */ |
| 652 | 622 | public function getActiveStats($productId = null) |
| 653 | 623 | { |
| 654 | - // We will calculate the wait times for open waiting tickets | |
| 655 | 624 | $query = (new AgentTicketAccess())->applyMailboxRestrictionScope( |
| 656 | 625 | Ticket::waitingOnly() |
| 657 | 626 | ) |
| 658 | 627 | ->where('status', '!=', 'closed') |
| @@ -693,10 +662,10 @@ | ||
| 693 | 662 | ]; |
| 694 | 663 | } |
| 695 | 664 | |
| 696 | 665 | /** |
| 697 | - * getActiveStatByAgent method will return the statistics of active tickets for an agent | |
| 698 | - * This method will get agent id as parameter, fetch the list of open tickets by agent id, calculate the wait times and return results | |
| 666 | + * Same as getActiveStats() for one agent's tickets. | |
| 667 | + * | |
| 699 | 668 | * @param $agentId |
| 700 | 669 | * @return array|false |
| 701 | 670 | */ |
| 702 | 671 | public function getActiveStatByAgent($agentId) |
| @@ -719,10 +688,10 @@ | ||
| 719 | 688 | return $this->formatActiveStat($waitStat); |
| 720 | 689 | } |
| 721 | 690 | |
| 722 | 691 | /** |
| 723 | - * getWaitStatsByAgents is the batched form of getActiveStatByAgent's aggregate, keyed by agent id | |
| 724 | - * Agents with no waiting tickets get no row, so callers supply the zeroed default | |
| 692 | + * Batched getActiveStatByAgent() aggregate, keyed by agent id. Agents with | |
| 693 | + * no waiting tickets have no row. | |
| 725 | 694 | * @param array $agentIds |
| 726 | 695 | * @return \FluentSupport\Framework\Database\Orm\Collection|array |
| 727 | 696 | */ |
| 728 | 697 | public function getWaitStatsByAgents($agentIds) |
| @@ -746,9 +715,9 @@ | ||
| 746 | 715 | ->keyBy('agent_id'); |
| 747 | 716 | } |
| 748 | 717 | |
| 749 | 718 | /** |
| 750 | - * formatActiveStat converts a waiting-ticket aggregate row into the display shape | |
| 719 | + * Format a wait-time aggregate row for display. | |
| 751 | 720 | * @param $waitStat |
| 752 | 721 | * @return array |
| 753 | 722 | */ |
| 754 | 723 | private function formatActiveStat($waitStat) |
| @@ -814,11 +783,9 @@ | ||
| 814 | 783 | $whereClause .= ($whereClause ? ' AND ' : 'WHERE ') |
| 815 | 784 | . "(mailbox_id NOT IN ({$restrictedList}) OR mailbox_id IS NULL)"; |
| 816 | 785 | } |
| 817 | 786 | |
| 818 | - // SQL query to count tickets by day of week and hour within the specified date range. | |
| 819 | - // $wpdb->prefix is WordPress-internal; $whereClause is built from hardcoded literals | |
| 820 | - // only — user-supplied date values are bound via %s placeholders in prepare() below. | |
| 787 | + // $whereClause holds only literals and int ids. Dates are bound in prepare() below. | |
| 821 | 788 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 822 | 789 | $query = "SELECT DAYNAME(created_at) AS weekday, HOUR(created_at) AS hour, COUNT(*) AS count |
| 823 | 790 | FROM {$wpdb->prefix}fs_tickets |
| 824 | 791 | {$whereClause} |
| @@ -824,9 +791,8 @@ | ||
| 824 | 791 | {$whereClause} |
| 825 | 792 | GROUP BY DAYNAME(created_at), HOUR(created_at) |
| 826 | 793 | ORDER BY FIELD(DAYNAME(created_at), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), HOUR(created_at)"; |
| 827 | 794 | |
| 828 | - // Execute the query — prepared when date params are present, plain otherwise. | |
| 829 | 795 | // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query structure is hardcoded; values are bound via $wpdb->prepare() when $queryParams is non-empty. |
| 830 | 796 | $results = $queryParams |
| 831 | 797 | ? $wpdb->get_results($wpdb->prepare($query, $queryParams)) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 832 | 798 | : $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| @@ -831,15 +797,12 @@ | ||
| 831 | 797 | ? $wpdb->get_results($wpdb->prepare($query, $queryParams)) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 832 | 798 | : $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 833 | 799 | |
| 834 | 800 | $fillData = array_fill(0, 24, 0); |
| 835 | - // add :00 on the suffix | |
| 836 | 801 | $fillData = array_combine(array_map(function ($hour) { |
| 837 | 802 | return sprintf('%1d:00', $hour); |
| 838 | 803 | }, array_keys($fillData)), $fillData); |
| 839 | 804 | |
| 840 | - // Prepare the report | |
| 841 | - // Prepare the report | |
| 842 | 805 | $report = [ |
| 843 | 806 | 'Mon' => $fillData, |
| 844 | 807 | 'Tue' => $fillData, |
| 845 | 808 | 'Wed' => $fillData, |
| @@ -879,9 +842,9 @@ | ||
| 879 | 842 | $whereClause .= ' AND c.person_id = %d'; |
| 880 | 843 | $queryParams[] = intval($agentId); |
| 881 | 844 | } |
| 882 | 845 | |
| 883 | - // As getTicketStats(), but reaching the mailbox and product through the parent ticket. | |
| 846 | + // Mailbox and product scoping go through the parent ticket. | |
| 884 | 847 | $ticketConditions = []; |
| 885 | 848 | $restrictedMailboxIds = PermissionManager::getRestrictedMailboxIds(); |
| 886 | 849 | |
| 887 | 850 | if ($restrictedMailboxIds) { |
| @@ -898,9 +861,9 @@ | ||
| 898 | 861 | $whereClause .= " AND EXISTS (SELECT 1 FROM {$wpdb->prefix}fs_tickets t" |
| 899 | 862 | . " WHERE t.id = c.ticket_id AND " . implode(' AND ', $ticketConditions) . ')'; |
| 900 | 863 | } |
| 901 | 864 | |
| 902 | - // SQL query to count customer responses by day of week and hour within the specified date range | |
| 865 | + // Responses by weekday and hour, for agents or customers per $reportType | |
| 903 | 866 | $query = $wpdb->prepare( |
| 904 | 867 | "SELECT DAYNAME(c.created_at) AS weekday, HOUR(c.created_at) AS hour, COUNT(*) AS count |
| 905 | 868 | FROM {$wpdb->prefix}fs_conversations AS c |
| 906 | 869 | JOIN {$wpdb->prefix}fs_persons AS p ON c.person_id = p.id |
| @@ -910,19 +873,16 @@ | ||
| 910 | 873 | ORDER BY FIELD(DAYNAME(c.created_at), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), HOUR(c.created_at)", |
| 911 | 874 | $queryParams |
| 912 | 875 | ); |
| 913 | 876 | |
| 914 | - // Execute the query | |
| 915 | 877 | // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $query is the output of $wpdb->prepare() above; checker incorrectly traces back through the prepared parameters. |
| 916 | 878 | $results = $wpdb->get_results($query); |
| 917 | 879 | |
| 918 | 880 | $fillData = array_fill(0, 24, 0); |
| 919 | - // add :00 on the suffix | |
| 920 | 881 | $fillData = array_combine(array_map(function ($hour) { |
| 921 | 882 | return sprintf('%1d:00', $hour); |
| 922 | 883 | }, array_keys($fillData)), $fillData); |
| 923 | 884 | |
| 924 | - // Prepare the report | |
| 925 | 885 | $report = [ |
| 926 | 886 | 'Mon' => $fillData, |
| 927 | 887 | 'Tue' => $fillData, |
| 928 | 888 | 'Wed' => $fillData, |
