| @@ -5,23 +5,21 @@ | ||
| 5 | 5 | use FluentSupport\App\Models\Agent; |
| 6 | 6 | use FluentSupport\App\Models\Conversation; |
| 7 | 7 | use FluentSupport\App\Models\Ticket; |
| 8 | 8 | use FluentSupport\App\Models\Product; |
| 9 | +use FluentSupport\App\Services\Tickets\AgentTicketAccess; | |
| 9 | 10 | |
| 10 | 11 | /** |
| 11 | - * StatModule class is responsible for getting data related to report | |
| 12 | - * @package FluentSupport\App\Modules | |
| 13 | - * | |
| 14 | - * @version 1.0.0 | |
| 12 | + * Dashboard ticket stats, scoped to the current agent's mailbox restrictions. | |
| 15 | 13 | */ |
| 16 | 14 | class StatModule |
| 17 | 15 | { |
| 18 | 16 | /** |
| 19 | - * getAgentStat method will return ticket statistics by an agent id | |
| 20 | - * This method will get agent id, start date and end date as parameter and fetch the ticket information and return | |
| 17 | + * Date range defaults to today. | |
| 18 | + * | |
| 21 | 19 | * @param $agentId |
| 22 | - * @param false $startDate | |
| 23 | - * @param false $endDate | |
| 20 | + * @param string|false $startDate | |
| 21 | + * @param string|false $endDate | |
| 24 | 22 | * @return array[] |
| 25 | 23 | */ |
| 26 | 24 | public static function getAgentStat($agentId, $startDate = false, $endDate = false) |
| 27 | 25 | { |
| @@ -30,41 +28,41 @@ | ||
| 30 | 28 | $startDate = $currentDate; |
| 31 | 29 | $endDate = $currentDate; |
| 32 | 30 | } |
| 33 | 31 | |
| 34 | - $startDate = date('Y-m-d 00:00.01', strtotime($startDate)); | |
| 35 | - $endDate = date('Y-m-d 23:59.59', strtotime($endDate)); | |
| 32 | + $startDate = gmdate('Y-m-d 00:00.01', strtotime($startDate)); | |
| 33 | + $endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate)); | |
| 36 | 34 | |
| 37 | - //Get list of new ticket by agent | |
| 38 | - $newTickets = Ticket::where('agent_id', $agentId) | |
| 39 | - ->where('status', 'new') | |
| 40 | - ->count(); | |
| 35 | + $access = new AgentTicketAccess(); | |
| 41 | 36 | |
| 42 | - //Get list of active ticket by agent | |
| 43 | - $activeTickets = Ticket::where('agent_id', $agentId) | |
| 44 | - ->where('status', 'active') | |
| 45 | - ->count(); | |
| 37 | + $newTickets = $access->applyMailboxRestrictionScope( | |
| 38 | + Ticket::where('agent_id', $agentId)->where('status', 'new') | |
| 39 | + )->count(); | |
| 46 | 40 | |
| 47 | - //Get list of closed ticket by agent within a date range(default today) | |
| 48 | - $closedTickets = Ticket::where('agent_id', $agentId) | |
| 49 | - ->where('status', 'closed') | |
| 50 | - ->whereBetween('resolved_at', [$startDate, $endDate]) | |
| 51 | - ->count(); | |
| 41 | + $activeTickets = $access->applyMailboxRestrictionScope( | |
| 42 | + Ticket::where('agent_id', $agentId)->where('status', 'active') | |
| 43 | + )->count(); | |
| 52 | 44 | |
| 53 | - //Get list of response by agent id within a date range(default today) | |
| 54 | - $responses = Conversation::where('conversation_type', 'response') | |
| 55 | - ->where('person_id', $agentId) | |
| 56 | - ->whereBetween('created_at', [$startDate, $endDate]) | |
| 57 | - ->count(); | |
| 45 | + $closedTickets = $access->applyMailboxRestrictionScope( | |
| 46 | + Ticket::where('agent_id', $agentId) | |
| 47 | + ->where('status', 'closed') | |
| 48 | + ->whereBetween('resolved_at', [$startDate, $endDate]) | |
| 49 | + )->count(); | |
| 58 | 50 | |
| 59 | - //Count the response in tickets within a date range(default today) for agent | |
| 60 | - $interactions = Conversation::where('person_id', $agentId) | |
| 61 | - ->where('conversation_type', 'response') | |
| 62 | - ->whereBetween('created_at', [$startDate, $endDate]) | |
| 63 | - ->groupBy('ticket_id') | |
| 64 | - ->get() | |
| 65 | - ->count(); | |
| 51 | + $responses = $access->applyMailboxRestrictionScopeViaTicket( | |
| 52 | + Conversation::where('conversation_type', 'response') | |
| 53 | + ->where('person_id', $agentId) | |
| 54 | + ->whereBetween('created_at', [$startDate, $endDate]) | |
| 55 | + )->count(); | |
| 66 | 56 | |
| 57 | + // Distinct tickets the agent replied to | |
| 58 | + $interactions = $access->applyMailboxRestrictionScopeViaTicket( | |
| 59 | + Conversation::where('person_id', $agentId) | |
| 60 | + ->where('conversation_type', 'response') | |
| 61 | + ->whereBetween('created_at', [$startDate, $endDate]) | |
| 62 | + ->groupBy('ticket_id') | |
| 63 | + )->get()->count(); | |
| 64 | + | |
| 67 | 65 | return [ |
| 68 | 66 | 'new_tickets' => [ |
| 69 | 67 | 'title' => __('New Tickets', 'fluent-support'), |
| 70 | 68 | 'count' => $newTickets |
| @@ -77,9 +75,9 @@ | ||
| 77 | 75 | 'title' => __('Closed Tickets', 'fluent-support'), |
| 78 | 76 | 'count' => $closedTickets |
| 79 | 77 | ], |
| 80 | 78 | 'responses' => [ |
| 81 | - 'title' => __('Responses', 'fluent-support'), | |
| 79 | + 'title' => __('Total Responses', 'fluent-support'), | |
| 82 | 80 | 'count' => $responses |
| 83 | 81 | ], |
| 84 | 82 | 'interactions' => [ |
| 85 | 83 | 'title' => __('Interactions', 'fluent-support'), |
| @@ -88,25 +86,27 @@ | ||
| 88 | 86 | ]; |
| 89 | 87 | } |
| 90 | 88 | |
| 91 | 89 | /** |
| 92 | - * getOverAllStats method will return the overall statistics for all tickets | |
| 93 | - * This method will count the ticket number by ticket status and return the array | |
| 94 | 90 | * @return array[] |
| 95 | 91 | */ |
| 96 | 92 | public static function getOverAllStats() |
| 97 | 93 | { |
| 98 | - $newTickets = Ticket::where('status', 'new') | |
| 94 | + $access = new AgentTicketAccess(); | |
| 95 | + | |
| 96 | + // Same mailbox boundary as the ticket list; these totals are agent-facing. | |
| 97 | + $newTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'new')) | |
| 99 | 98 | ->count(); |
| 100 | 99 | |
| 101 | - $activeTickets = Ticket::where('status', 'active') | |
| 100 | + $activeTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'active')) | |
| 102 | 101 | ->count(); |
| 103 | 102 | |
| 104 | - $closedTickets = Ticket::where('status', 'closed') | |
| 103 | + $closedTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'closed')) | |
| 105 | 104 | ->count(); |
| 106 | 105 | |
| 107 | - $responses = Conversation::where('conversation_type', 'response') | |
| 108 | - ->count(); | |
| 106 | + $responses = $access->applyMailboxRestrictionScopeViaTicket( | |
| 107 | + Conversation::where('conversation_type', 'response') | |
| 108 | + )->count(); | |
| 109 | 109 | |
| 110 | 110 | return [ |
| 111 | 111 | 'new_tickets' => [ |
| 112 | 112 | 'title' => __('New Tickets', 'fluent-support'), |
| @@ -127,29 +127,33 @@ | ||
| 127 | 127 | ]; |
| 128 | 128 | } |
| 129 | 129 | |
| 130 | 130 | /** |
| 131 | - * getTodayStats method will return a stats of today's tickets | |
| 132 | - * This method will count the ticket number by ticket status and return the array | |
| 133 | - * @param bool|int $agentId By default value set to false however when it gets an agent id it will fetch | |
| 134 | - * the result by this id | |
| 135 | - * @return array result in array format | |
| 131 | + * @param bool|int $agentId Limit to this agent when given. | |
| 132 | + * @return array | |
| 136 | 133 | */ |
| 137 | - public static function getTodayStats($agentId = false) | |
| 134 | + public static function getTodayStats($agentId = false, ?AgentTicketAccess $access = null) | |
| 138 | 135 | { |
| 139 | - $start = date('Y-m-d 00:00.01'); | |
| 140 | - $end = date('Y-m-d 23:59.59'); | |
| 136 | + list($start, $end) = static::todayRange(); | |
| 141 | 137 | |
| 142 | - $newTickets = Ticket::whereBetween('created_at', [$start, $end]); | |
| 138 | + $access = $access ?: new AgentTicketAccess(); | |
| 143 | 139 | |
| 144 | - $closedTickets = Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end]); | |
| 140 | + $newTickets = $access->applyMailboxRestrictionScope( | |
| 141 | + Ticket::whereBetween('created_at', [$start, $end]) | |
| 142 | + ); | |
| 145 | 143 | |
| 146 | - $responses = Conversation::where('conversation_type', 'response') | |
| 147 | - ->whereHas('person', function ($q) { | |
| 148 | - $q->where('person_type', 'agent'); | |
| 149 | - }) | |
| 150 | - ->whereBetween('created_at', [$start, $end]); | |
| 144 | + $closedTickets = $access->applyMailboxRestrictionScope( | |
| 145 | + Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end]) | |
| 146 | + ); | |
| 151 | 147 | |
| 148 | + $responses = $access->applyMailboxRestrictionScopeViaTicket( | |
| 149 | + Conversation::where('conversation_type', 'response') | |
| 150 | + ->whereHas('person', function ($q) { | |
| 151 | + $q->where('person_type', 'agent'); | |
| 152 | + }) | |
| 153 | + ->whereBetween('created_at', [$start, $end]) | |
| 154 | + ); | |
| 155 | + | |
| 152 | 156 | if ($agentId) { |
| 153 | 157 | $newTickets->where('agent_id', $agentId); |
| 154 | 158 | $closedTickets->where('agent_id', $agentId); |
| 155 | 159 | $responses->where('person_id', $agentId); |
| @@ -171,36 +175,27 @@ | ||
| 171 | 175 | ]; |
| 172 | 176 | } |
| 173 | 177 | |
| 174 | 178 | /** |
| 175 | - * getAgentOverallStats method will produce overall statistics by agent | |
| 176 | 179 | * @param $agentId |
| 177 | 180 | * @return array[] |
| 178 | 181 | */ |
| 179 | 182 | public static function getAgentOverallStats($agentId) |
| 180 | 183 | { |
| 181 | - //Get count of response by the agent | |
| 182 | - $replies_count = Conversation::where('person_id', $agentId)->count(); | |
| 184 | + $replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket( | |
| 185 | + Conversation::where('person_id', $agentId) | |
| 186 | + )->count(); | |
| 183 | 187 | |
| 184 | - //Get the number of interactions/responses by agent with tickets | |
| 185 | - $interactions_count = Conversation::where('person_id', $agentId) | |
| 186 | - ->where('conversation_type', 'response') | |
| 187 | - ->groupBy('ticket_id') | |
| 188 | - ->get() | |
| 189 | - ->count(); | |
| 188 | + // Closed tickets assigned to the agent, whoever closed them | |
| 189 | + $total_closed = (new AgentTicketAccess())->applyMailboxRestrictionScope( | |
| 190 | + Ticket::where('agent_id', $agentId)->where('status', 'closed') | |
| 191 | + )->count(); | |
| 190 | 192 | |
| 191 | - //Get the number of tickets that are closed by this agent | |
| 192 | - $total_closed = Ticket::where('agent_id', $agentId)->where('status', 'closed')->count(); | |
| 193 | - | |
| 194 | 193 | return [ |
| 195 | 194 | 'replies_count' => [ |
| 196 | 195 | 'title' => __('Total Replies', 'fluent-support'), |
| 197 | 196 | 'count' => $replies_count |
| 198 | 197 | ], |
| 199 | - 'interactions_count' => [ | |
| 200 | - 'title' => __('Total Interactions', 'fluent-support'), | |
| 201 | - 'count' => $interactions_count | |
| 202 | - ], | |
| 203 | 198 | 'total_closed' => [ |
| 204 | 199 | 'title' => __('Total Closed', 'fluent-support'), |
| 205 | 200 | 'count' => $total_closed |
| 206 | 201 | ] |
| @@ -207,9 +202,9 @@ | ||
| 207 | 202 | ]; |
| 208 | 203 | } |
| 209 | 204 | |
| 210 | 205 | /** |
| 211 | - * This `getActiveTicketsByProductStats` method will count today's tickets by product that are waiting for reply | |
| 206 | + * Open tickets awaiting an agent reply, per product. | |
| 212 | 207 | * @return array $result |
| 213 | 208 | */ |
| 214 | 209 | public static function getActiveTicketsByProductStats() |
| 215 | 210 | { |
| @@ -215,12 +210,19 @@ | ||
| 215 | 210 | { |
| 216 | 211 | $products = Product::all(); |
| 217 | 212 | $result = []; |
| 218 | 213 | |
| 214 | + $counts = static::awaitingTicketsQuery(new AgentTicketAccess()) | |
| 215 | + ->whereIn('product_id', $products->pluck('id')->all()) | |
| 216 | + ->groupBy('product_id') | |
| 217 | + ->selectRaw('product_id, COUNT(*) AS total') | |
| 218 | + ->pluck('total', 'product_id') | |
| 219 | + ->all(); | |
| 220 | + | |
| 219 | 221 | foreach ($products as $product) { |
| 220 | 222 | $result[$product->id] = [ |
| 221 | 223 | 'title' => $product->title, |
| 222 | - 'count' => static::countAwaitingTickets('product_id', $product->id) | |
| 224 | + 'count' => (int) ($counts[$product->id] ?? 0) | |
| 223 | 225 | ]; |
| 224 | 226 | } |
| 225 | 227 | |
| 226 | 228 | return $result; |
| @@ -227,56 +229,132 @@ | ||
| 227 | 229 | } |
| 228 | 230 | |
| 229 | 231 | |
| 230 | 232 | /** |
| 231 | - * This will count the number of tickets that are waiting for response also it can receive parameters | |
| 232 | - * @param string $whereClause // This is the where clause that will be used in the query | |
| 233 | - * @param string $whereClauseValue // This is the value of the where clause | |
| 233 | + * Open tickets awaiting an agent reply, optionally filtered by one column. | |
| 234 | + * | |
| 235 | + * @param string $whereClause Column name. | |
| 236 | + * @param string $whereClauseValue | |
| 234 | 237 | * @return int $awatingTicketCount |
| 235 | 238 | */ |
| 236 | - public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null) | |
| 239 | + public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null, ?AgentTicketAccess $access = null) | |
| 237 | 240 | { |
| 238 | - $ticket = new Ticket; | |
| 241 | + $ticket = static::awaitingTicketsQuery($access ?: new AgentTicketAccess()); | |
| 239 | 242 | |
| 240 | 243 | if ($whereClause && $whereClauseValue) { |
| 241 | - $ticket = $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue)); | |
| 244 | + $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue)); | |
| 242 | 245 | } |
| 243 | 246 | |
| 244 | - $awatingTicketCount = $ticket->where('status', '!=', 'closed') | |
| 247 | + return $ticket->count(); | |
| 248 | + } | |
| 249 | + | |
| 250 | + /** | |
| 251 | + * Open tickets awaiting an agent reply, within the viewer's mailboxes. | |
| 252 | + * | |
| 253 | + * @param AgentTicketAccess $access | |
| 254 | + * @return \FluentSupport\Framework\Database\Orm\Builder | |
| 255 | + */ | |
| 256 | + protected static function awaitingTicketsQuery(AgentTicketAccess $access) | |
| 257 | + { | |
| 258 | + // Must be a builder: ->where() on a bare model returns a new one, losing the scope. | |
| 259 | + return $access->applyMailboxRestrictionScope(Ticket::query()) | |
| 260 | + ->where('status', '!=', 'closed') | |
| 245 | 261 | ->where(function ($query) { |
| 246 | 262 | $query->whereColumn('last_agent_response', '<', 'last_customer_response') |
| 247 | 263 | ->orWhereNull('last_agent_response') |
| 248 | 264 | ->orWhere('status', 'new'); |
| 249 | - })->count(); | |
| 265 | + }); | |
| 266 | + } | |
| 250 | 267 | |
| 251 | - return $awatingTicketCount; | |
| 268 | + /** | |
| 269 | + * Today in site time, like the stored dates. | |
| 270 | + * | |
| 271 | + * @return string[] [start, end] as "Y-m-d H:i:s". | |
| 272 | + */ | |
| 273 | + protected static function todayRange() | |
| 274 | + { | |
| 275 | + $today = current_time('Y-m-d'); | |
| 276 | + | |
| 277 | + return [$today . ' 00:00:00', $today . ' 23:59:59']; | |
| 252 | 278 | } |
| 253 | 279 | |
| 254 | 280 | /** |
| 255 | - * This method will return a summary of today's stats of all agent | |
| 281 | + * Today's stats for every agent with replies today or tickets waiting. | |
| 256 | 282 | * @return array |
| 257 | 283 | */ |
| 258 | 284 | public static function getAgentTodayStats() |
| 259 | 285 | { |
| 286 | + // Share one instance so restrictions are resolved once. | |
| 287 | + $access = new AgentTicketAccess(); | |
| 288 | + list($start, $end) = static::todayRange(); | |
| 289 | + | |
| 290 | + $agents = Agent::get(); | |
| 291 | + $agentIds = $agents->pluck('id')->all(); | |
| 292 | + | |
| 293 | + if (!$agentIds) { | |
| 294 | + return []; | |
| 295 | + } | |
| 296 | + | |
| 297 | + // One grouped query per figure for all agents. | |
| 298 | + $countBy = function ($query, $column) { | |
| 299 | + return $query->groupBy($column) | |
| 300 | + ->selectRaw($column . ' AS grouped_id, COUNT(*) AS total') | |
| 301 | + ->pluck('total', 'grouped_id') | |
| 302 | + ->all(); | |
| 303 | + }; | |
| 304 | + | |
| 305 | + $waiting = $countBy(static::awaitingTicketsQuery($access)->whereIn('agent_id', $agentIds), 'agent_id'); | |
| 306 | + | |
| 307 | + $newTickets = $countBy($access->applyMailboxRestrictionScope( | |
| 308 | + Ticket::whereBetween('created_at', [$start, $end])->whereIn('agent_id', $agentIds) | |
| 309 | + ), 'agent_id'); | |
| 310 | + | |
| 311 | + $closedTickets = $countBy($access->applyMailboxRestrictionScope( | |
| 312 | + Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])->whereIn('agent_id', $agentIds) | |
| 313 | + ), 'agent_id'); | |
| 314 | + | |
| 315 | + // person_id among the agent ids is the "written by an agent" check. | |
| 316 | + $responses = $countBy($access->applyMailboxRestrictionScopeViaTicket( | |
| 317 | + Conversation::where('conversation_type', 'response') | |
| 318 | + ->whereIn('person_id', $agentIds) | |
| 319 | + ->whereBetween('created_at', [$start, $end]) | |
| 320 | + ), 'person_id'); | |
| 321 | + | |
| 260 | 322 | $stats = []; |
| 261 | - Agent::select(['id', 'first_name'])->get()->each(function ($agent) use (&$stats) { | |
| 262 | - $agentStat = static::getTodayStats($agent->id); | |
| 263 | - $waiting = static::countAwaitingTickets('agent_id', $agent->id); | |
| 264 | - if(!empty($agentStat['responses']['count']) || $waiting) { | |
| 265 | - $stats[] = [ | |
| 266 | - 'agent_name' => $agent->first_name, | |
| 267 | - 'stats' => array_merge( | |
| 268 | - [ | |
| 269 | - 'waiting_today' => [ | |
| 270 | - 'title' => __('Waiting Today', 'fluent-support'), | |
| 271 | - 'count' => $waiting | |
| 272 | - ] | |
| 273 | - ], | |
| 274 | - $agentStat | |
| 275 | - ) | |
| 276 | - ]; | |
| 323 | + | |
| 324 | + foreach ($agents as $agent) { | |
| 325 | + $waitingCount = (int) ($waiting[$agent->id] ?? 0); | |
| 326 | + $responseCount = (int) ($responses[$agent->id] ?? 0); | |
| 327 | + | |
| 328 | + if (!$responseCount && !$waitingCount) { | |
| 329 | + continue; | |
| 277 | 330 | } |
| 278 | - }); | |
| 331 | + | |
| 332 | + $stats[] = [ | |
| 333 | + 'agent_id' => $agent->id, | |
| 334 | + 'agent_name' => $agent->full_name, | |
| 335 | + 'agent_email' => $agent->email, | |
| 336 | + 'avatar' => $agent->photo, | |
| 337 | + 'stats' => [ | |
| 338 | + 'waiting_today' => [ | |
| 339 | + 'title' => __('Waiting Today', 'fluent-support'), | |
| 340 | + 'count' => $waitingCount | |
| 341 | + ], | |
| 342 | + 'new_tickets' => [ | |
| 343 | + 'title' => __('New Tickets', 'fluent-support'), | |
| 344 | + 'count' => (int) ($newTickets[$agent->id] ?? 0) | |
| 345 | + ], | |
| 346 | + 'closed_tickets' => [ | |
| 347 | + 'title' => __('Closed Tickets', 'fluent-support'), | |
| 348 | + 'count' => (int) ($closedTickets[$agent->id] ?? 0) | |
| 349 | + ], | |
| 350 | + 'responses' => [ | |
| 351 | + 'title' => __('Responses', 'fluent-support'), | |
| 352 | + 'count' => $responseCount | |
| 353 | + ] | |
| 354 | + ] | |
| 355 | + ]; | |
| 356 | + } | |
| 279 | 357 | |
| 280 | 358 | return $stats; |
| 281 | 359 | } |
| 282 | 360 | |
