| @@ -10,11 +10,9 @@ | ||
| 10 | 10 | use FluentSupport\App\Models\Person; |
| 11 | 11 | use FluentSupport\App\Models\Product; |
| 12 | 12 | use FluentSupport\App\Models\TagPivot; |
| 13 | 13 | use FluentSupport\App\Models\Ticket; |
| 14 | -use FluentSupport\App\Modules\PermissionManager; | |
| 15 | 14 | use FluentSupport\App\Services\Helper; |
| 16 | -use FluentSupport\App\Services\Tickets\AgentTicketAccess; | |
| 17 | 15 | use FluentSupport\Framework\Database\Orm\Builder; |
| 18 | 16 | use FluentSupport\Framework\Support\Arr; |
| 19 | 17 | use FluentSupport\Framework\Support\DateTime; |
| 20 | 18 | |
| @@ -53,11 +51,8 @@ | ||
| 53 | 51 | ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) |
| 54 | 52 | ->groupBy($groupBy) |
| 55 | 53 | ->oldest($orderBy); |
| 56 | 54 | |
| 57 | - // Bound first: a caller-supplied mailbox_id below is not proof of access. | |
| 58 | - (new AgentTicketAccess())->applyMailboxRestrictionScope($query); | |
| 59 | - | |
| 60 | 55 | //If filter by product or agent or status selected |
| 61 | 56 | if ($filters) { |
| 62 | 57 | if (!empty($filters['statuses'])) { |
| 63 | 58 | $query->whereIn('status', $filters['statuses']); |
| @@ -112,10 +107,8 @@ | ||
| 112 | 107 | ->where($filterColumn, '>', 0) |
| 113 | 108 | ->groupBy($groupBy) |
| 114 | 109 | ->oldest($orderBy); |
| 115 | 110 | |
| 116 | - (new AgentTicketAccess())->applyMailboxRestrictionScope($query); | |
| 117 | - | |
| 118 | 111 | //If filter by product or agent is selected |
| 119 | 112 | if ($filters) { |
| 120 | 113 | if (!empty($filters['product_id'])) { |
| 121 | 114 | $query->where('product_id', $filters['product_id']); |
| @@ -155,17 +148,17 @@ | ||
| 155 | 148 | ); |
| 156 | 149 | |
| 157 | 150 | list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency); |
| 158 | 151 | |
| 159 | - $query = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket( | |
| 160 | - Conversation::query() | |
| 161 | - ->select($this->prepareSelect($frequency)) | |
| 162 | - ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) | |
| 163 | - ->where('conversation_type', 'response') | |
| 164 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 165 | - ->groupBy($groupBy) | |
| 166 | - ->oldest($orderBy) | |
| 167 | - ); | |
| 152 | + $query = Conversation::query() | |
| 153 | + ->select($this->prepareSelect($frequency)) | |
| 154 | + ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) | |
| 155 | + ->where('conversation_type', 'response') | |
| 156 | + ->whereHas('person', function ($q) { | |
| 157 | + $q->where('person_type', 'agent'); | |
| 158 | + }) | |
| 159 | + ->groupBy($groupBy) | |
| 160 | + ->oldest($orderBy); | |
| 168 | 161 | |
| 169 | 162 | if ($filters) { |
| 170 | 163 | if (!empty($filters['person_id'])) { |
| 171 | 164 | $query->where('person_id', $filters['person_id']); |
| @@ -194,9 +187,9 @@ | ||
| 194 | 187 | ); |
| 195 | 188 | |
| 196 | 189 | list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency); |
| 197 | 190 | |
| 198 | - $filterColumn = (!empty($type)) ? $type.'_id' : 'id'; | |
| 191 | + $filterColumn = $type."_id"; | |
| 199 | 192 | |
| 200 | 193 | $query = $this->db()->table('fs_tickets') |
| 201 | 194 | ->select($this->prepareSelect($frequency,'created_at','response_count')) |
| 202 | 195 | ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to)) |
| @@ -204,10 +197,8 @@ | ||
| 204 | 197 | ->where($filterColumn, '>', 0) |
| 205 | 198 | ->groupBy($groupBy) |
| 206 | 199 | ->oldest($orderBy); |
| 207 | 200 | |
| 208 | - (new AgentTicketAccess())->applyMailboxRestrictionScope($query); | |
| 209 | - | |
| 210 | 201 | if ($filters) { |
| 211 | 202 | if (!empty($filters['product_id'])) { |
| 212 | 203 | $query->where('product_id', $filters['product_id']); |
| 213 | 204 | } |
| @@ -221,23 +212,8 @@ | ||
| 221 | 212 | |
| 222 | 213 | return $this->getResult($period, $items); |
| 223 | 214 | } |
| 224 | 215 | |
| 225 | - // One grouped query instead of one per agent; shared by the two summaries. | |
| 226 | - private function interactionCountsByAgent($from, $to, $access) | |
| 227 | - { | |
| 228 | - return $access->applyMailboxRestrictionScopeViaTicket( | |
| 229 | - Conversation::select([ | |
| 230 | - $this->db()->raw('person_id as agent_id'), | |
| 231 | - $this->db()->raw('COUNT(DISTINCT ticket_id) as count') | |
| 232 | - ]) | |
| 233 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 234 | - ->whereBetween('created_at', [$from, $to]) | |
| 235 | - ->where('conversation_type', 'response') | |
| 236 | - ->groupBy('person_id') | |
| 237 | - )->get(); | |
| 238 | - } | |
| 239 | - | |
| 240 | 216 | /** |
| 241 | 217 | * agentSummary method will prepare ticket summary with responses by agent |
| 242 | 218 | * @param false $from |
| 243 | 219 | * @param false $to |
| @@ -257,10 +233,8 @@ | ||
| 257 | 233 | $from .= ' 00:00:00'; |
| 258 | 234 | $to .= ' 23:59:59'; |
| 259 | 235 | $reports = []; |
| 260 | 236 | |
| 261 | - $access = new AgentTicketAccess(); | |
| 262 | - | |
| 263 | 237 | //Get tickets statistics that are closed |
| 264 | 238 | $resolves = $this->db()->table('fs_tickets') |
| 265 | 239 | ->select([ |
| 266 | 240 | $this->db()->raw('COUNT(id) AS count'), |
| @@ -267,12 +241,11 @@ | ||
| 267 | 241 | 'agent_id', |
| 268 | 242 | ]) |
| 269 | 243 | ->groupBy('agent_id') |
| 270 | 244 | ->where('status', 'closed') |
| 271 | - ->whereBetween('resolved_at', [$from, $to]); | |
| 245 | + ->whereBetween('resolved_at', [$from, $to]) | |
| 246 | + ->get(); | |
| 272 | 247 | |
| 273 | - $resolves = $access->applyMailboxRestrictionScope($resolves)->get(); | |
| 274 | - | |
| 275 | 248 | $reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id'); |
| 276 | 249 | |
| 277 | 250 | //get statistics for all except closed ticket |
| 278 | 251 | $openTickets = $this->db()->table('fs_tickets') |
| @@ -280,32 +253,35 @@ | ||
| 280 | 253 | $this->db()->raw('COUNT(id) AS count'), |
| 281 | 254 | 'agent_id' |
| 282 | 255 | ]) |
| 283 | 256 | ->groupBy('agent_id') |
| 284 | - ->where('status', '!=', 'closed'); | |
| 257 | + ->where('status', '!=', 'closed') | |
| 258 | + ->get(); | |
| 285 | 259 | |
| 286 | - $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get(); | |
| 287 | - | |
| 288 | 260 | $reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id'); |
| 289 | 261 | //Get response by agent |
| 290 | - $responses = $access->applyMailboxRestrictionScopeViaTicket( | |
| 291 | - Conversation::select([ | |
| 292 | - $this->db()->raw('COUNT(id) AS count'), | |
| 293 | - $this->db()->raw('person_id as agent_id'), | |
| 294 | - $this->db()->raw('created_at') | |
| 295 | - ]) | |
| 296 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 297 | - ->whereBetween('created_at', [$from, $to]) | |
| 298 | - ->where('conversation_type', 'response') | |
| 299 | - ->groupBy('agent_id') | |
| 300 | - )->get(); | |
| 262 | + $responses = Conversation::select([ | |
| 263 | + $this->db()->raw('COUNT(id) AS count'), | |
| 264 | + $this->db()->raw('person_id as agent_id'), | |
| 265 | + $this->db()->raw('created_at') | |
| 266 | + ]) | |
| 267 | + ->whereHas('person', function ($q) { | |
| 268 | + $q->where('person_type', '=', 'agent'); | |
| 269 | + }) | |
| 270 | + ->whereBetween('created_at', [$from, $to]) | |
| 271 | + ->where('conversation_type', 'response') | |
| 272 | + ->groupBy('agent_id') | |
| 273 | + ->get(); | |
| 301 | 274 | |
| 302 | 275 | $reports = $this->pushReportData('responses', $responses, $reports, 'agent_id'); |
| 303 | 276 | //Get interactions/responses by individual agents |
| 304 | - foreach ($this->interactionCountsByAgent($from, $to, $access) as $row) { | |
| 305 | - if (isset($reports[$row->agent_id])) { | |
| 306 | - $reports[$row->agent_id]['interactions'] = (int) $row->count; | |
| 307 | - } | |
| 277 | + foreach ($responses as $response) { | |
| 278 | + $reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id) | |
| 279 | + ->where('conversation_type', 'response') | |
| 280 | + ->whereBetween('created_at', [$from, $to]) | |
| 281 | + ->groupBy('ticket_id') | |
| 282 | + ->get() | |
| 283 | + ->count(); | |
| 308 | 284 | } |
| 309 | 285 | |
| 310 | 286 | $agentIds = array_keys($reports); |
| 311 | 287 | |
| @@ -315,18 +291,19 @@ | ||
| 315 | 291 | |
| 316 | 292 | // get agent feedback statistics |
| 317 | 293 | $agentFeedbackRatingEnabled = Helper::getBusinessSettings('agent_feedback_rating') === 'yes'; |
| 318 | 294 | 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') | |
| 323 | - ]) | |
| 324 | - ->whereIn('person_id', $agentIds) | |
| 325 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 326 | - ->where('conversation_type', 'response') | |
| 327 | - ->groupBy('agent_id') | |
| 328 | - )->get(); | |
| 295 | + $agentConversations = Conversation::select([ | |
| 296 | + $this->db()->raw('person_id as agent_id'), | |
| 297 | + $this->db()->raw('GROUP_CONCAT(id) as conversation_ids') | |
| 298 | + ]) | |
| 299 | + ->whereIn('person_id', $agentIds) | |
| 300 | + ->whereHas('person', function ($q) { | |
| 301 | + $q->where('person_type', '=', 'agent'); | |
| 302 | + }) | |
| 303 | + ->where('conversation_type', 'response') | |
| 304 | + ->groupBy('agent_id') | |
| 305 | + ->get(); | |
| 329 | 306 | |
| 330 | 307 | foreach ($agentConversations as $conversation) { |
| 331 | 308 | $conversationIds = array_map('intval', explode(',', $conversation->conversation_ids)); |
| 332 | 309 | |
| @@ -353,40 +330,30 @@ | ||
| 353 | 330 | $reports[$agentId]['dislikes'] = $dislikeCount; |
| 354 | 331 | } |
| 355 | 332 | } |
| 356 | 333 | |
| 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. | |
| 360 | - $agentsQuery = Agent::select(['id', 'first_name', 'last_name', 'email']); | |
| 334 | + $agents = Agent::select(['id', 'first_name', 'last_name', 'email']) | |
| 335 | + ->whereIn('id', $agentIds) | |
| 336 | + ->get(); | |
| 361 | 337 | |
| 362 | - if ($agent) { | |
| 363 | - $agentsQuery->whereIn('id', $agentIds); | |
| 364 | - } | |
| 338 | + foreach ($agents as $agent) { | |
| 339 | + $report = NULL; | |
| 340 | + if(isset($reports[$agent->id])) { | |
| 341 | + $reportFields = [ | |
| 342 | + 'interactions' => 0, | |
| 343 | + 'responses' => 0, | |
| 344 | + 'opens' => 0, | |
| 345 | + 'closed' => 0, | |
| 346 | + 'waiting_tickets' => 0, | |
| 347 | + ]; | |
| 365 | 348 | |
| 366 | - $agents = $agentsQuery->get(); | |
| 349 | + $additionalFields = defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled ? ['likes' => 0, 'dislikes' => 0] : []; | |
| 350 | + $reportFields = $reportFields + $additionalFields; | |
| 367 | 351 | |
| 368 | - $reportFields = [ | |
| 369 | - 'interactions' => 0, | |
| 370 | - 'responses' => 0, | |
| 371 | - 'opens' => 0, | |
| 372 | - 'closed' => 0, | |
| 373 | - 'waiting_tickets' => 0, | |
| 374 | - ]; | |
| 375 | - | |
| 376 | - $additionalFields = defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled ? ['likes' => 0, 'dislikes' => 0] : []; | |
| 377 | - $reportFields = $reportFields + $additionalFields; | |
| 378 | - | |
| 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. | |
| 381 | - $waitStats = $this->getWaitStatsByAgents($agents->pluck('id')->toArray()); | |
| 382 | - $emptyActiveStat = ['average_waiting' => 0, 'max_waiting' => 0, 'waiting_tickets' => 0]; | |
| 383 | - | |
| 384 | - foreach ($agents as $agent) { | |
| 385 | - $agent->stats = wp_parse_args(isset($reports[$agent->id]) ? $reports[$agent->id] : [], $reportFields); | |
| 386 | - $agent->active_stat = isset($waitStats[$agent->id]) | |
| 387 | - ? $this->formatActiveStat($waitStats[$agent->id]) | |
| 388 | - : $emptyActiveStat; | |
| 352 | + $report = wp_parse_args($reports[$agent->id], $reportFields); | |
| 353 | + } | |
| 354 | + $agent->stats = $report; | |
| 355 | + $agent->active_stat = $this->getActiveStatByAgent($agent->id); | |
| 389 | 356 | } |
| 390 | 357 | return $agents; |
| 391 | 358 | } |
| 392 | 359 | |
| @@ -408,10 +375,8 @@ | ||
| 408 | 375 | $reports = []; |
| 409 | 376 | |
| 410 | 377 | $groupByField = $type == 'product' ? 'product_id' : 'mailbox_id'; |
| 411 | 378 | |
| 412 | - $access = new AgentTicketAccess(); | |
| 413 | - | |
| 414 | 379 | $resolves = $this->db()->table('fs_tickets') |
| 415 | 380 | ->select([ |
| 416 | 381 | $this->db()->raw('COUNT(id) AS count'), |
| 417 | 382 | $groupByField, |
| @@ -417,13 +382,11 @@ | ||
| 417 | 382 | $groupByField, |
| 418 | 383 | ]) |
| 419 | 384 | ->groupBy($groupByField) |
| 420 | 385 | ->where('status', 'closed') |
| 421 | - ->whereBetween('resolved_at', [$from, $to]); | |
| 386 | + ->whereBetween('resolved_at', [$from, $to]) | |
| 387 | + ->get(); | |
| 422 | 388 | |
| 423 | - // Aggregates count tickets too, so the same mailbox boundary applies. | |
| 424 | - $resolves = $access->applyMailboxRestrictionScope($resolves)->get(); | |
| 425 | - | |
| 426 | 389 | $reports = $this->pushReportData('closed', $resolves, $reports, $groupByField); |
| 427 | 390 | |
| 428 | 391 | $openTickets = $this->db()->table('fs_tickets') |
| 429 | 392 | ->select([ |
| @@ -431,12 +394,11 @@ | ||
| 431 | 394 | $groupByField |
| 432 | 395 | ]) |
| 433 | 396 | ->groupBy($groupByField) |
| 434 | 397 | ->where('status', '!=', 'closed') |
| 435 | - ->whereBetween('created_at', [$from, $to]); | |
| 398 | + ->whereBetween('created_at', [$from, $to]) | |
| 399 | + ->get(); | |
| 436 | 400 | |
| 437 | - $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get(); | |
| 438 | - | |
| 439 | 401 | $reports = $this->pushReportData('opens', $openTickets, $reports, $groupByField); |
| 440 | 402 | |
| 441 | 403 | $responses = $this->db()->table('fs_conversations') |
| 442 | 404 | ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id') |
| @@ -444,33 +406,37 @@ | ||
| 444 | 406 | $this->db()->raw('COUNT(' . $tablePrefix . 'fs_conversations.id) AS count'), |
| 445 | 407 | 'fs_tickets.' . $groupByField, |
| 446 | 408 | ]) |
| 447 | 409 | ->groupBy('fs_tickets.' . $groupByField) |
| 448 | - ->whereBetween('fs_conversations.created_at', [$from, $to]); | |
| 410 | + ->whereBetween('fs_conversations.created_at', [$from, $to]) | |
| 411 | + ->get(); | |
| 449 | 412 | |
| 450 | - $responses = $access->applyMailboxRestrictionScope($responses)->get(); | |
| 413 | + $reports = $this->pushReportData('responses', $responses, $reports, $groupByField); | |
| 451 | 414 | |
| 452 | - $reports = $this->pushReportData('responses', $responses, $reports, $groupByField); | |
| 415 | + $ticketIds = $this->db()->table('fs_tickets') | |
| 416 | + ->select([ | |
| 417 | + $groupByField, | |
| 418 | + $this->db()->raw('GROUP_CONCAT(id) AS ticket_ids'), | |
| 419 | + ]) | |
| 420 | + ->where($groupByField, '!=', 0) | |
| 421 | + ->groupBy($groupByField) | |
| 422 | + ->get(); | |
| 453 | 423 | |
| 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. | |
| 460 | - $interactions = $this->db()->table('fs_conversations') | |
| 461 | - ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id') | |
| 462 | - ->select([ | |
| 463 | - 'fs_tickets.' . $groupByField, | |
| 464 | - $this->db()->raw('COUNT(DISTINCT ' . $tablePrefix . 'fs_conversations.ticket_id) AS count'), | |
| 465 | - ]) | |
| 466 | - ->where('fs_conversations.conversation_type', 'response') | |
| 467 | - ->whereBetween('fs_conversations.created_at', [$from, $to]) | |
| 468 | - ->groupBy('fs_tickets.' . $groupByField); | |
| 424 | + $result = []; | |
| 425 | + foreach ($ticketIds as $item) { | |
| 426 | + $result[$item->{$groupByField}] = explode(',', $item->ticket_ids); | |
| 427 | + } | |
| 469 | 428 | |
| 470 | - $interactions = $access->applyMailboxRestrictionScope($interactions)->get(); | |
| 429 | + foreach ($result as $id => $ticketIds) { | |
| 430 | + $interactions = Conversation::whereIn('ticket_id', $ticketIds) | |
| 431 | + ->where('conversation_type', 'response') | |
| 432 | + ->whereBetween('created_at', [$from, $to]) | |
| 433 | + ->groupBy('ticket_id') | |
| 434 | + ->get() | |
| 435 | + ->count(); | |
| 471 | 436 | |
| 472 | - $reports = $this->pushReportData('interactions', $interactions, $reports, $groupByField); | |
| 437 | + $reports[$id]['interactions'] = $interactions; | |
| 438 | + } | |
| 473 | 439 | |
| 474 | 440 | $ids = array_keys($reports); |
| 475 | 441 | |
| 476 | 442 | $types = [ |
| @@ -528,10 +494,8 @@ | ||
| 528 | 494 | |
| 529 | 495 | // Get per-agent stats using the same queries as agentSummary |
| 530 | 496 | $reports = []; |
| 531 | 497 | |
| 532 | - $access = new AgentTicketAccess(); | |
| 533 | - | |
| 534 | 498 | $resolves = $this->db()->table('fs_tickets') |
| 535 | 499 | ->select([ |
| 536 | 500 | $this->db()->raw('COUNT(id) AS count'), |
| 537 | 501 | 'agent_id', |
| @@ -537,12 +501,11 @@ | ||
| 537 | 501 | 'agent_id', |
| 538 | 502 | ]) |
| 539 | 503 | ->groupBy('agent_id') |
| 540 | 504 | ->where('status', 'closed') |
| 541 | - ->whereBetween('resolved_at', [$from, $to]); | |
| 505 | + ->whereBetween('resolved_at', [$from, $to]) | |
| 506 | + ->get(); | |
| 542 | 507 | |
| 543 | - $resolves = $access->applyMailboxRestrictionScope($resolves)->get(); | |
| 544 | - | |
| 545 | 508 | $reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id'); |
| 546 | 509 | |
| 547 | 510 | $openTickets = $this->db()->table('fs_tickets') |
| 548 | 511 | ->select([ |
| @@ -549,31 +512,34 @@ | ||
| 549 | 512 | $this->db()->raw('COUNT(id) AS count'), |
| 550 | 513 | 'agent_id' |
| 551 | 514 | ]) |
| 552 | 515 | ->groupBy('agent_id') |
| 553 | - ->where('status', '!=', 'closed'); | |
| 516 | + ->where('status', '!=', 'closed') | |
| 517 | + ->get(); | |
| 554 | 518 | |
| 555 | - $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get(); | |
| 556 | - | |
| 557 | 519 | $reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id'); |
| 558 | 520 | |
| 559 | - $responses = $access->applyMailboxRestrictionScopeViaTicket( | |
| 560 | - Conversation::select([ | |
| 561 | - $this->db()->raw('COUNT(id) AS count'), | |
| 562 | - $this->db()->raw('person_id as agent_id'), | |
| 563 | - ]) | |
| 564 | - ->whereIn('person_id', Agent::getAgentIds()) | |
| 565 | - ->whereBetween('created_at', [$from, $to]) | |
| 566 | - ->where('conversation_type', 'response') | |
| 567 | - ->groupBy('agent_id') | |
| 568 | - )->get(); | |
| 521 | + $responses = Conversation::select([ | |
| 522 | + $this->db()->raw('COUNT(id) AS count'), | |
| 523 | + $this->db()->raw('person_id as agent_id'), | |
| 524 | + ]) | |
| 525 | + ->whereHas('person', function ($q) { | |
| 526 | + $q->where('person_type', '=', 'agent'); | |
| 527 | + }) | |
| 528 | + ->whereBetween('created_at', [$from, $to]) | |
| 529 | + ->where('conversation_type', 'response') | |
| 530 | + ->groupBy('agent_id') | |
| 531 | + ->get(); | |
| 569 | 532 | |
| 570 | 533 | $reports = $this->pushReportData('responses', $responses, $reports, 'agent_id'); |
| 571 | 534 | |
| 572 | - foreach ($this->interactionCountsByAgent($from, $to, $access) as $row) { | |
| 573 | - if (isset($reports[$row->agent_id])) { | |
| 574 | - $reports[$row->agent_id]['interactions'] = (int) $row->count; | |
| 575 | - } | |
| 535 | + foreach ($responses as $response) { | |
| 536 | + $reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id) | |
| 537 | + ->where('conversation_type', 'response') | |
| 538 | + ->whereBetween('created_at', [$from, $to]) | |
| 539 | + ->groupBy('ticket_id') | |
| 540 | + ->get() | |
| 541 | + ->count(); | |
| 576 | 542 | } |
| 577 | 543 | |
| 578 | 544 | // Get group -> agent_id mappings |
| 579 | 545 | $groupAgentMap = TagPivot::where('source_type', 'agent_group') |
| @@ -645,25 +611,16 @@ | ||
| 645 | 611 | |
| 646 | 612 | /** |
| 647 | 613 | * getActiveStats method will return the statistics for active tickets |
| 648 | 614 | * This method will get the list of open tickets calculate the wait times and return results |
| 649 | - * @param int|null $productId Narrow to one product's tickets | |
| 650 | 615 | * @return array|false |
| 651 | 616 | */ |
| 652 | - public function getActiveStats($productId = null) | |
| 617 | + public function getActiveStats() | |
| 653 | 618 | { |
| 654 | 619 | // We will calculate the wait times for open waiting tickets |
| 655 | - $query = (new AgentTicketAccess())->applyMailboxRestrictionScope( | |
| 656 | - Ticket::waitingOnly() | |
| 657 | - ) | |
| 620 | + $waitStat = Ticket::waitingOnly() | |
| 658 | 621 | ->where('status', '!=', 'closed') |
| 659 | - ->whereNotNull('waiting_since'); | |
| 660 | - | |
| 661 | - if ($productId) { | |
| 662 | - $query->where('product_id', $productId); | |
| 663 | - } | |
| 664 | - | |
| 665 | - $waitStat = $query | |
| 622 | + ->whereNotNull('waiting_since') | |
| 666 | 623 | ->select([ |
| 667 | 624 | $this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'), |
| 668 | 625 | $this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'), |
| 669 | 626 | $this->db()->raw('COUNT(*) as total_tickets') |
| @@ -700,9 +657,9 @@ | ||
| 700 | 657 | * @return array|false |
| 701 | 658 | */ |
| 702 | 659 | public function getActiveStatByAgent($agentId) |
| 703 | 660 | { |
| 704 | - $waitStat = (new AgentTicketAccess())->applyMailboxRestrictionScope(Ticket::waitingOnly()) | |
| 661 | + $waitStat = Ticket::waitingOnly() | |
| 705 | 662 | ->where('status', '!=', 'closed') |
| 706 | 663 | ->whereNotNull('waiting_since') |
| 707 | 664 | ->where('agent_id', $agentId) |
| 708 | 665 | ->select([ |
| @@ -715,45 +672,8 @@ | ||
| 715 | 672 | if(!$waitStat) { |
| 716 | 673 | return false; |
| 717 | 674 | } |
| 718 | 675 | |
| 719 | - return $this->formatActiveStat($waitStat); | |
| 720 | - } | |
| 721 | - | |
| 722 | - /** | |
| 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 | |
| 725 | - * @param array $agentIds | |
| 726 | - * @return \FluentSupport\Framework\Database\Orm\Collection|array | |
| 727 | - */ | |
| 728 | - public function getWaitStatsByAgents($agentIds) | |
| 729 | - { | |
| 730 | - if (empty($agentIds)) { | |
| 731 | - return []; | |
| 732 | - } | |
| 733 | - | |
| 734 | - return (new AgentTicketAccess())->applyMailboxRestrictionScope(Ticket::waitingOnly()) | |
| 735 | - ->where('status', '!=', 'closed') | |
| 736 | - ->whereNotNull('waiting_since') | |
| 737 | - ->whereIn('agent_id', $agentIds) | |
| 738 | - ->select([ | |
| 739 | - 'agent_id', | |
| 740 | - $this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'), | |
| 741 | - $this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'), | |
| 742 | - $this->db()->raw('COUNT(*) as total_tickets') | |
| 743 | - ]) | |
| 744 | - ->groupBy('agent_id') | |
| 745 | - ->get() | |
| 746 | - ->keyBy('agent_id'); | |
| 747 | - } | |
| 748 | - | |
| 749 | - /** | |
| 750 | - * formatActiveStat converts a waiting-ticket aggregate row into the display shape | |
| 751 | - * @param $waitStat | |
| 752 | - * @return array | |
| 753 | - */ | |
| 754 | - private function formatActiveStat($waitStat) | |
| 755 | - { | |
| 756 | 676 | $waitStat->avg_waiting = intval($waitStat->avg_waiting); |
| 757 | 677 | if($waitStat->avg_waiting > 0) { |
| 758 | 678 | $waitSeconds = time() - $waitStat->avg_waiting; |
| 759 | 679 | if( $waitSeconds < 172800 && $waitSeconds > 7200) { |
| @@ -785,9 +705,9 @@ | ||
| 785 | 705 | return []; |
| 786 | 706 | } |
| 787 | 707 | } |
| 788 | 708 | |
| 789 | - public function getTicketStats($from, $to, $productId = null) | |
| 709 | + public function getTicketStats($from, $to) | |
| 790 | 710 | { |
| 791 | 711 | global $wpdb; |
| 792 | 712 | |
| 793 | 713 | $whereClause = ''; |
| @@ -800,22 +720,8 @@ | ||
| 800 | 720 | $queryParams[] = $start_date; |
| 801 | 721 | $queryParams[] = $end_date; |
| 802 | 722 | } |
| 803 | 723 | |
| 804 | - if ($productId) { | |
| 805 | - $whereClause .= ($whereClause ? ' AND ' : 'WHERE ') . 'product_id = %d'; | |
| 806 | - $queryParams[] = intval($productId); | |
| 807 | - } | |
| 808 | - | |
| 809 | - // Raw SQL, so the query-builder helper cannot be used. | |
| 810 | - $restrictedMailboxIds = PermissionManager::getRestrictedMailboxIds(); | |
| 811 | - | |
| 812 | - if ($restrictedMailboxIds) { | |
| 813 | - $restrictedList = implode(',', array_map('intval', $restrictedMailboxIds)); | |
| 814 | - $whereClause .= ($whereClause ? ' AND ' : 'WHERE ') | |
| 815 | - . "(mailbox_id NOT IN ({$restrictedList}) OR mailbox_id IS NULL)"; | |
| 816 | - } | |
| 817 | - | |
| 818 | 724 | // SQL query to count tickets by day of week and hour within the specified date range. |
| 819 | 725 | // $wpdb->prefix is WordPress-internal; $whereClause is built from hardcoded literals |
| 820 | 726 | // only — user-supplied date values are bound via %s placeholders in prepare() below. |
| 821 | 727 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| @@ -859,9 +765,9 @@ | ||
| 859 | 765 | |
| 860 | 766 | return $report; |
| 861 | 767 | } |
| 862 | 768 | |
| 863 | - public function getResponseStats($from, $to, $reportType, $agentId = null, $productId = null) | |
| 769 | + public function getResponseStats($from, $to, $reportType, $agentId = null) | |
| 864 | 770 | { |
| 865 | 771 | global $wpdb; |
| 866 | 772 | |
| 867 | 773 | $whereClause = ' AND p.person_type = %s'; |
| @@ -879,27 +785,8 @@ | ||
| 879 | 785 | $whereClause .= ' AND c.person_id = %d'; |
| 880 | 786 | $queryParams[] = intval($agentId); |
| 881 | 787 | } |
| 882 | 788 | |
| 883 | - // As getTicketStats(), but reaching the mailbox and product through the parent ticket. | |
| 884 | - $ticketConditions = []; | |
| 885 | - $restrictedMailboxIds = PermissionManager::getRestrictedMailboxIds(); | |
| 886 | - | |
| 887 | - if ($restrictedMailboxIds) { | |
| 888 | - $restrictedList = implode(',', array_map('intval', $restrictedMailboxIds)); | |
| 889 | - $ticketConditions[] = "(t.mailbox_id NOT IN ({$restrictedList}) OR t.mailbox_id IS NULL)"; | |
| 890 | - } | |
| 891 | - | |
| 892 | - if ($productId) { | |
| 893 | - $ticketConditions[] = 't.product_id = %d'; | |
| 894 | - $queryParams[] = intval($productId); | |
| 895 | - } | |
| 896 | - | |
| 897 | - if ($ticketConditions) { | |
| 898 | - $whereClause .= " AND EXISTS (SELECT 1 FROM {$wpdb->prefix}fs_tickets t" | |
| 899 | - . " WHERE t.id = c.ticket_id AND " . implode(' AND ', $ticketConditions) . ')'; | |
| 900 | - } | |
| 901 | - | |
| 902 | 789 | // SQL query to count customer responses by day of week and hour within the specified date range |
| 903 | 790 | $query = $wpdb->prepare( |
| 904 | 791 | "SELECT DAYNAME(c.created_at) AS weekday, HOUR(c.created_at) AS hour, COUNT(*) AS count |
| 905 | 792 | FROM {$wpdb->prefix}fs_conversations AS c |
| @@ -944,11 +831,9 @@ | ||
| 944 | 831 | } |
| 945 | 832 | |
| 946 | 833 | public function getTicketResponseStats($from, $to, $filter) |
| 947 | 834 | { |
| 948 | - $query = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket( | |
| 949 | - Conversation::query() | |
| 950 | - ) | |
| 835 | + $query = Conversation::query() | |
| 951 | 836 | ->select('id', 'ticket_id', 'person_id', 'created_at', 'content') |
| 952 | 837 | ->addSelect([ |
| 953 | 838 | 'person_type' => Person::select('person_type') |
| 954 | 839 | ->whereColumn('id', 'fs_conversations.person_id'), |
