Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
59.79% |
290 / 485 |
|
30.77% |
4 / 13 |
CRAP | |
0.00% |
0 / 1 |
| ReportController | |
59.79% |
290 / 485 |
|
30.77% |
4 / 13 |
462.35 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| availableCharts | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
6 | |||
| chart | |
0.00% |
0 / 25 |
|
0.00% |
0 / 1 |
30 | |||
| data | |
96.83% |
122 / 126 |
|
0.00% |
0 / 1 |
9 | |||
| company_users_overview | |
0.00% |
0 / 68 |
|
0.00% |
0 / 1 |
12 | |||
| company_licenses_overview | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
6 | |||
| company_usage_overview | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
2 | |||
| export | |
100.00% |
95 / 95 |
|
100.00% |
1 / 1 |
26 | |||
| buildDateFilteredAggregates | |
100.00% |
23 / 23 |
|
100.00% |
1 / 1 |
4 | |||
| resolveExportColumns | |
90.00% |
9 / 10 |
|
0.00% |
0 / 1 |
2.00 | |||
| buildBaseMatchQuery | |
85.00% |
34 / 40 |
|
0.00% |
0 / 1 |
14.66 | |||
| toDailyUsageMatch | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| getDateRange | |
15.38% |
2 / 13 |
|
0.00% |
0 / 1 |
27.81 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Http\Controllers\v2\Company; |
| 4 | |
| 5 | use App\Http\Controllers\Controller; |
| 6 | use App\Http\Models\Admin\AdminUserInvitation; |
| 7 | use App\Http\Models\Admin\CompanyGroup; |
| 8 | use App\Http\Models\Admin\CompanyLicenses; |
| 9 | use App\Http\Models\Auth\Role; |
| 10 | use App\Http\Models\Auth\User; |
| 11 | use App\Http\Models\FlyMsgUserDailyUsage; |
| 12 | use App\Http\Models\Setting; |
| 13 | use App\Http\Models\UserInfo; |
| 14 | use App\Http\Requests\v2\Reports\GetReportRequest; |
| 15 | use App\Http\Services\Admin\Companies\CompanyUsersService; |
| 16 | use App\Http\Services\Admin\Users\IndividualUsersService; |
| 17 | use App\Http\Services\CsvExportService; |
| 18 | use Carbon\Carbon; |
| 19 | use Illuminate\Http\JsonResponse; |
| 20 | use Illuminate\Http\Request; |
| 21 | use Illuminate\Support\Facades\Log; |
| 22 | use MongoDB\BSON\UTCDateTime; |
| 23 | use Symfony\Component\HttpFoundation\StreamedResponse; |
| 24 | |
| 25 | class ReportController extends Controller |
| 26 | { |
| 27 | private array $propertyMap = [ |
| 28 | 'time' => 'time_saved', |
| 29 | 'cost' => 'cost_savings', |
| 30 | 'shortcuts_created' => 'flycuts_created', |
| 31 | 'templates' => 'flyplates_added', |
| 32 | 'chars' => 'characters_typed', |
| 33 | 'shortcuts_used' => 'flycut_count', |
| 34 | 'comments_generated' => 'flyengage_count', |
| 35 | 'posts_generated' => 'flypost_count', |
| 36 | 'paragraph_rewrite' => 'paragraph_rewrite_count', |
| 37 | 'grammar' => 'fly_grammar_actions', |
| 38 | 'issues_accepted' => 'fly_grammar_accepted', |
| 39 | 'issues_autocorrect' => 'fly_grammar_autocorrect', |
| 40 | 'issues_autocomplete' => 'fly_grammar_autocomplete', |
| 41 | ]; |
| 42 | |
| 43 | /** |
| 44 | * Maps chart IDs (from available-charts endpoint) to export column definitions. |
| 45 | * 'field' → UserInfo collection field (lifetime totals, used when no date filter) |
| 46 | * 'daily_field' → FlyMsgUserDailyUsage field (used when from/to date filter is present) |
| 47 | * 'round' → decimal places for rounding |
| 48 | */ |
| 49 | private array $effectivenessColumns = [ |
| 50 | 'cost' => ['header' => 'Cost Savings ($)', 'field' => 'total_cost_savings_by_flymsg_by_user', 'daily_field' => 'cost_savings', 'round' => 5], |
| 51 | 'time' => ['header' => 'Time Saved (hrs)', 'field' => 'total_time_saved_by_flymsg_by_user', 'daily_field' => 'time_saved', 'round' => 5], |
| 52 | 'shortcuts_created' => ['header' => 'Shortcuts Created', 'field' => 'number_of_flycuts_created_count', 'daily_field' => 'flycuts_created', 'round' => 5], |
| 53 | 'templates' => ['header' => 'Templates Added', 'field' => 'number_of_flyplates_in_flycuts_count', 'daily_field' => 'flyplates_added', 'round' => 5], |
| 54 | ]; |
| 55 | |
| 56 | private array $usageColumns = [ |
| 57 | 'chars' => ['header' => 'Characters Typed', 'field' => 'total___of_characters_typed_by_flymsg_by_user', 'daily_field' => 'characters_typed', 'round' => 0], |
| 58 | 'grammar' => ['header' => 'AI Grammar Checker Used', 'field' => 'total___of_times_flygrammar_is_used_count', 'daily_field' => 'fly_grammar_actions', 'round' => 0], |
| 59 | 'shortcuts_used' => ['header' => 'Shortcuts Deployed', 'field' => 'total___of_times_flycut_used__count_', 'daily_field' => 'flycut_count', 'round' => 0], |
| 60 | 'comments_generated' => ['header' => 'AI Comment Generator Used', 'field' => 'total___of_times_flyengage_used__count_', 'daily_field' => 'flyengage_count', 'round' => 0], |
| 61 | 'posts_generated' => ['header' => 'AI Post Generator Used', 'field' => 'total___of_times_flyposts_used__count_', 'daily_field' => 'flypost_count', 'round' => 0], |
| 62 | 'paragraph_rewrite' => ['header' => 'AI Paragraph Rewrite Used', 'field' => 'total___of_times_paragraph_rewrite_used__count_', 'daily_field' => 'paragraph_rewrite_count','round' => 0], |
| 63 | ]; |
| 64 | |
| 65 | public function __construct( |
| 66 | private CompanyUsersService $companyUsersService, |
| 67 | private IndividualUsersService $individualUsersService, |
| 68 | private CsvExportService $csvExportService |
| 69 | ) {} |
| 70 | |
| 71 | public function availableCharts(Request $request, string $type): JsonResponse |
| 72 | { |
| 73 | if ($type === 'effectiveness') { |
| 74 | $charts = [ |
| 75 | ['value' => 'cost', 'label' => 'Cost Saved ($)'], |
| 76 | ['value' => 'time', 'label' => 'Time Saved (hrs)'], |
| 77 | ['value' => 'shortcuts_created', 'label' => 'Shortcuts Created'], |
| 78 | ['value' => 'templates', 'label' => 'Templates Added'], |
| 79 | ]; |
| 80 | } else { |
| 81 | $charts = [ |
| 82 | ['value' => 'chars', 'label' => 'Characters Typed'], |
| 83 | ['value' => 'grammar', 'label' => 'AI Grammar Checker Used'], |
| 84 | ['value' => 'shortcuts_used', 'label' => 'Shortcuts Deployed'], |
| 85 | ['value' => 'comments_generated', 'label' => 'AI Comment Generator Used'], |
| 86 | ['value' => 'posts_generated', 'label' => 'AI Post Generator Used'], |
| 87 | ['value' => 'paragraph_rewrite', 'label' => 'AI Paragraph Rewrite Used'], |
| 88 | ]; |
| 89 | } |
| 90 | |
| 91 | return response()->json(['data' => (array) $charts, 'success' => true], 200); |
| 92 | } |
| 93 | |
| 94 | public function chart(GetReportRequest $request, string $type): JsonResponse |
| 95 | { |
| 96 | $property = $this->propertyMap[$type] ?? $type; |
| 97 | $round = in_array($type, ['time', 'cost']) ? 2 : 0; |
| 98 | |
| 99 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 100 | $dailyMatch = $this->toDailyUsageMatch($baseMatch); |
| 101 | [$startDate, $endDate] = $this->getDateRange($request, $dailyMatch, FlyMsgUserDailyUsage::class); |
| 102 | |
| 103 | $finalMatch = $dailyMatch; |
| 104 | $finalMatch['created_at'] = ['$gte' => new UTCDateTime($startDate), '$lte' => new UTCDateTime($endDate)]; |
| 105 | |
| 106 | $pipeline = [ |
| 107 | ['$match' => $finalMatch], |
| 108 | ['$group' => ['_id' => ['$dateToString' => ['format' => '%Y-%m', 'date' => '$created_at']], 'totalValue' => ['$sum' => '$'.$property]]], |
| 109 | ['$sort' => ['_id' => 1]], |
| 110 | ['$project' => ['_id' => 0, 'month_year' => '$_id', 'total' => ['$round' => ['$totalValue', $round]]]], |
| 111 | ]; |
| 112 | |
| 113 | try { |
| 114 | $results = FlyMsgUserDailyUsage::raw(fn ($c) => $c->aggregate($pipeline)); |
| 115 | $dbData = []; |
| 116 | foreach ($results as $result) { |
| 117 | $dbData[$result['month_year']] = $result['total']; |
| 118 | } |
| 119 | $chart = []; |
| 120 | $period = new \DatePeriod($startDate->copy()->startOfMonth(), new \DateInterval('P1M'), $endDate->copy()->endOfMonth()); |
| 121 | foreach ($period as $date) { |
| 122 | $monthKey = $date->format('Y-m'); |
| 123 | $chart[] = ['period' => $date->format('M Y'), 'value' => $dbData[$monthKey] ?? 0]; |
| 124 | } |
| 125 | |
| 126 | return response()->json(['data' => ['chart' => $chart], 'success' => true], 200); |
| 127 | } catch (\Exception $e) { |
| 128 | return response()->json(['success' => false, 'error' => $e->getMessage()], 400); |
| 129 | } |
| 130 | } |
| 131 | |
| 132 | public function data(GetReportRequest $request, string $type): JsonResponse |
| 133 | { |
| 134 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 135 | $dailyMatch = $this->toDailyUsageMatch($baseMatch); |
| 136 | |
| 137 | // Extract company for wage lookup from the built match query |
| 138 | $companyId = $baseMatch['company_id']['$in'][0] |
| 139 | ?? ($request->input('company_ids') ? explode(',', $request->input('company_ids'))[0] : $request->user()->company_id); |
| 140 | |
| 141 | $companySetting = Setting::where('company_id', $companyId)->first(); |
| 142 | $wageValue = round($companySetting?->wage_per_hour ?? 31.75, 2); // phpcs:ignore |
| 143 | |
| 144 | $textMap = [ |
| 145 | 'time' => [ |
| 146 | 'label' => 'Time Saved (hrs)', |
| 147 | 'title' => 'Time Saved (hrs)', |
| 148 | 'tooltip' => 'This show the total amount of time users saved over the selected period of time.', |
| 149 | ], |
| 150 | 'cost' => [ |
| 151 | 'label' => 'Cost Saved ($)', |
| 152 | 'title' => 'Cost Saved ($)', |
| 153 | 'tooltip' => 'This show the total cost savings in USD using the average US knowledge worker wage of $'.$wageValue.'/over the selected period of time.', |
| 154 | ], |
| 155 | 'shortcuts_created' => [ |
| 156 | 'label' => 'Shortcuts Created', |
| 157 | 'title' => 'Shortcuts Created', |
| 158 | 'tooltip' => 'Total number of Shortcuts (FlyCuts) created from users over the selected period of time.', |
| 159 | ], |
| 160 | 'templates' => [ |
| 161 | 'label' => 'Templates Added', |
| 162 | 'title' => 'Templates Added', |
| 163 | 'tooltip' => 'Total number of Templates (FlyPlates) added to FlyCuts from users over the selected period of time.', |
| 164 | ], |
| 165 | 'chars' => [ |
| 166 | 'label' => 'Characters Typed', |
| 167 | 'title' => 'Characters Typed', |
| 168 | 'tooltip' => 'This shows the total amount of characters users typed over the selected period of time.', |
| 169 | ], |
| 170 | 'shortcuts_used' => [ |
| 171 | 'label' => 'Shortcuts Deployed', |
| 172 | 'title' => 'Shortcuts Deployed', |
| 173 | 'tooltip' => 'Total number of Shortcuts (FlyCuts) used by users over the selected period of time.', |
| 174 | ], |
| 175 | 'comments_generated' => [ |
| 176 | 'label' => 'AI Comment Generator Used', |
| 177 | 'title' => 'AI Comment Generator Used', |
| 178 | 'tooltip' => 'Total number of times that AI Comment Generator Used (FlyEngage) was used over the selected period of time.', |
| 179 | ], |
| 180 | 'posts_generated' => [ |
| 181 | 'label' => 'AI Post Generator Used', |
| 182 | 'title' => 'AI Post Generator Used', |
| 183 | 'tooltip' => 'Total number of times that AI Post Generator Used (FlyPosts) was used over the selected period of time.', |
| 184 | ], |
| 185 | 'paragraph_rewrite' => [ |
| 186 | 'label' => 'AI Paragraph Rewrite Used', |
| 187 | 'title' => 'AI Paragraph Rewrite Used', |
| 188 | 'tooltip' => 'This shows the total number of times AI Sentence and Paragraph Rewrite was used over the selected period of time.', |
| 189 | ], |
| 190 | 'grammar' => [ |
| 191 | 'label' => 'AI Grammar Checker Used', |
| 192 | 'title' => 'AI Grammar Checker Used', |
| 193 | 'tooltip' => 'Total number of times AI Grammar Checker was used by users over the selected period of time.', |
| 194 | ], |
| 195 | 'issues_accepted' => [ |
| 196 | 'label' => 'AI Grammar Accepted', |
| 197 | 'title' => 'AI Grammar Accepted', |
| 198 | 'tooltip' => 'This total represents all grammar suggestions accepted by users over the selected period of time.', |
| 199 | ], |
| 200 | 'issues_autocorrect' => [ |
| 201 | 'label' => 'AI Spelling Autocorrected', |
| 202 | 'title' => 'AI Spelling Autocorrected', |
| 203 | 'tooltip' => 'This total represents all spelling auto correction over the selected period of time.', |
| 204 | ], |
| 205 | 'issues_autocomplete' => [ |
| 206 | 'label' => 'AI Sentence Autocompleted', |
| 207 | 'title' => 'AI Sentence Autocompleted', |
| 208 | 'tooltip' => 'This total represents all sentences auto completed accepted by users over the selected period of time.', |
| 209 | ], |
| 210 | ]; |
| 211 | |
| 212 | $property = $this->propertyMap[$type] ?? $type; |
| 213 | $round = in_array($type, ['time', 'cost']) ? 2 : 0; |
| 214 | $textData = $textMap[$type] ?? ['label' => ucfirst($type), 'title' => 'Total '.ucfirst($type), 'tooltip' => '']; |
| 215 | $topUserLimit = 5; |
| 216 | |
| 217 | [$startDate, $endDate] = $this->getDateRange($request, $dailyMatch, FlyMsgUserDailyUsage::class); |
| 218 | |
| 219 | $finalMatch = $dailyMatch; |
| 220 | $finalMatch['created_at'] = ['$gte' => new UTCDateTime($startDate), '$lte' => new UTCDateTime($endDate)]; |
| 221 | |
| 222 | try { |
| 223 | // --- QUERY 1: Get Summary Statistics (Total and Unique User Count) --- |
| 224 | $summaryPipeline = [ |
| 225 | ['$match' => $finalMatch], |
| 226 | ['$group' => ['_id' => '$user_id', 'userTotal' => ['$sum' => '$'.$property]]], |
| 227 | ['$group' => ['_id' => null, 'total' => ['$sum' => '$userTotal'], 'unique_users' => ['$sum' => 1]]], |
| 228 | ]; |
| 229 | $summaryResult = FlyMsgUserDailyUsage::raw(fn ($c) => $c->aggregate($summaryPipeline))->first(); |
| 230 | |
| 231 | // --- QUERY 2: Get Top Users --- |
| 232 | // First, get per-user aggregates from daily usage within the date range |
| 233 | $dailyAggPipeline = [ |
| 234 | ['$match' => $finalMatch], |
| 235 | ['$group' => ['_id' => '$user_id', 'value' => ['$sum' => '$'.$property]]], |
| 236 | ]; |
| 237 | $dailyAggResults = FlyMsgUserDailyUsage::raw(fn ($c) => $c->aggregate($dailyAggPipeline)); |
| 238 | $userDailyValues = []; |
| 239 | foreach ($dailyAggResults as $row) { |
| 240 | $userDailyValues[$row['_id']] = $row['value']; |
| 241 | } |
| 242 | |
| 243 | // Get all users in scope from UserInfo (respects company/group filters), including those with zero activity |
| 244 | $userInfoPipeline = [ |
| 245 | ['$match' => $baseMatch], |
| 246 | ['$project' => ['_id' => 0, 'user_id' => 1, 'full_name' => 1, 'avatar' => 1]], |
| 247 | ]; |
| 248 | $usersInScope = UserInfo::raw(fn ($c) => $c->aggregate($userInfoPipeline)); |
| 249 | |
| 250 | // Merge: assign daily value or 0 for each user |
| 251 | $allUsers = []; |
| 252 | foreach ($usersInScope as $user) { |
| 253 | $userId = $user['user_id'] ?? ''; |
| 254 | $allUsers[] = [ |
| 255 | 'name' => $user['full_name'] ?? 'Deleted User', |
| 256 | 'avatar' => $user['avatar'] ?? null, |
| 257 | 'value' => round($userDailyValues[$userId] ?? 0, $round), |
| 258 | ]; |
| 259 | } |
| 260 | |
| 261 | // Sort descending by value, then take top 5 |
| 262 | usort($allUsers, fn ($a, $b) => $b['value'] <=> $a['value']); |
| 263 | $topUsers = array_slice($allUsers, 0, $topUserLimit); |
| 264 | |
| 265 | $total = $summaryResult->total ?? 0; |
| 266 | $uniqueUsers = $summaryResult->unique_users ?? 0; |
| 267 | $average = $uniqueUsers > 0 ? ($total / $uniqueUsers) : 0; |
| 268 | |
| 269 | $summaryData = [ |
| 270 | 'total' => number_format(round($total, $round), $round), |
| 271 | 'average' => round($average, 2) ? number_format($average, 2) : $average, |
| 272 | 'top' => $topUsers, |
| 273 | 'label' => $textData['label'], |
| 274 | 'title' => $textData['title'], |
| 275 | 'tooltip' => $textData['tooltip'], |
| 276 | ]; |
| 277 | |
| 278 | if ($type === 'cost') { |
| 279 | $summaryData['valuePrefix'] = '$'; |
| 280 | } |
| 281 | |
| 282 | return response()->json(['data' => $summaryData, 'success' => true], 200); |
| 283 | } catch (\Exception $e) { |
| 284 | Log::error('Summary data aggregation error: '.$e->getMessage()); |
| 285 | |
| 286 | return response()->json(['success' => false, 'error' => $e->getMessage()], 400); |
| 287 | } |
| 288 | } |
| 289 | |
| 290 | public function company_users_overview(GetReportRequest $request): JsonResponse |
| 291 | { |
| 292 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 293 | // Get Carbon dates first. |
| 294 | [$carbonStartDate, $carbonEndDate] = $this->getDateRange($request, $baseMatch, UserInfo::class, 'status_date'); |
| 295 | |
| 296 | $pipeline = []; |
| 297 | if (! empty($baseMatch)) { |
| 298 | $pipeline[] = ['$match' => $baseMatch]; |
| 299 | } |
| 300 | |
| 301 | // Prepare date clauses with UTCDateTime objects to prevent driver errors. |
| 302 | $statusDateClause = []; |
| 303 | $extensionInstallDateClause = []; |
| 304 | $extensionUninstallDateClause = []; |
| 305 | |
| 306 | if ($carbonStartDate) { |
| 307 | $utcStartDate = new UTCDateTime($carbonStartDate); |
| 308 | $utcEndDate = new UTCDateTime($carbonEndDate); |
| 309 | |
| 310 | $statusDateClause = ['status_date' => ['$gte' => $utcStartDate, '$lte' => $utcEndDate]]; |
| 311 | |
| 312 | $extensionInstallDateClause = ['$or' => [ |
| 313 | ['flymsg_chrome_extension_installed__date_' => ['$gte' => $utcStartDate, '$lte' => $utcEndDate]], |
| 314 | ['flymsg_edge_extension_installed__date_' => ['$gte' => $utcStartDate, '$lte' => $utcEndDate]], |
| 315 | ]]; |
| 316 | |
| 317 | $extensionUninstallDateClause = ['$or' => [ |
| 318 | ['flymsg_chrome_extension_uninstalled__date_' => ['$gte' => $utcStartDate, '$lte' => $utcEndDate]], |
| 319 | ['flymsg_edge_extension_uninstalled__date_' => ['$gte' => $utcStartDate, '$lte' => $utcEndDate]], |
| 320 | ]]; |
| 321 | } |
| 322 | |
| 323 | $pipeline[] = [ |
| 324 | '$facet' => [ |
| 325 | 'activated_users' => [ |
| 326 | ['$match' => array_merge(['status' => 'Active'], $statusDateClause)], |
| 327 | ['$count' => 'count'], |
| 328 | ], |
| 329 | 'inactivated_users' => [ |
| 330 | ['$match' => array_merge(['status' => ['$nin' => ['Active', 'Invited']]], $statusDateClause)], |
| 331 | ['$count' => 'count'], |
| 332 | ], |
| 333 | 'extensions_installed' => [ |
| 334 | ['$match' => array_merge( |
| 335 | ['is_any_extension_installed' => true, 'status' => 'Active'], |
| 336 | $extensionInstallDateClause |
| 337 | )], |
| 338 | ['$count' => 'count'], |
| 339 | ], |
| 340 | 'extensions_uninstalled' => [ |
| 341 | ['$match' => array_merge( |
| 342 | [ |
| 343 | 'is_any_extension_installed' => false, |
| 344 | 'is_any_extension_uninstalled' => true, |
| 345 | 'status' => ['$nin' => ['Deleted', 'Deactivated']], |
| 346 | ], |
| 347 | $extensionUninstallDateClause |
| 348 | )], |
| 349 | ['$count' => 'count'], |
| 350 | ], |
| 351 | ], |
| 352 | ]; |
| 353 | |
| 354 | $pipeline[] = [ |
| 355 | '$project' => [ |
| 356 | 'activated_users' => ['$ifNull' => [['$arrayElemAt' => ['$activated_users.count', 0]], 0]], |
| 357 | 'inactivated_users' => ['$ifNull' => [['$arrayElemAt' => ['$inactivated_users.count', 0]], 0]], |
| 358 | 'extensions_installed' => ['$ifNull' => [['$arrayElemAt' => ['$extensions_installed.count', 0]], 0]], |
| 359 | 'extensions_uninstalled' => ['$ifNull' => [['$arrayElemAt' => ['$extensions_uninstalled.count', 0]], 0]], |
| 360 | ], |
| 361 | ]; |
| 362 | |
| 363 | $result = UserInfo::raw(fn ($c) => $c->aggregate($pipeline))->first(); |
| 364 | |
| 365 | return response()->json([ |
| 366 | 'data' => [ |
| 367 | ['amount' => round($result->activated_users ?? 0), 'title' => 'Activated Users', 'tooltip' => 'Total number of users activated.'], |
| 368 | ['amount' => round($result->extensions_installed ?? 0), 'title' => 'Extension Installed', 'tooltip' => 'Total number of activated users with the extension installed.'], |
| 369 | ['amount' => round($result->inactivated_users ?? 0), 'title' => 'Deactivated Users', 'tooltip' => 'Total number of users deactivated.'], |
| 370 | ['amount' => round($result->extensions_uninstalled ?? 0), 'title' => 'Extension Uninstalled', 'tooltip' => 'Total number of extensions uninstalled.'], |
| 371 | ], |
| 372 | 'success' => true, |
| 373 | ]); |
| 374 | } |
| 375 | |
| 376 | public function company_licenses_overview(GetReportRequest $request): JsonResponse |
| 377 | { |
| 378 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 379 | $companyId = $baseMatch['company_id']['$in'][0] ?? $request->user()->company_id; |
| 380 | |
| 381 | $company_license = CompanyLicenses::where('company_id', $companyId)->active()->first(); |
| 382 | $totalLicenses = $company_license->total_number_of_licenses_available ?? 0; |
| 383 | $totalAssignedUserLicenses = User::where('company_id', $companyId)->where('status', 'Active')->count(); |
| 384 | $totalAssignedInvitationLicenses = AdminUserInvitation::where('company_id', $companyId)->count(); |
| 385 | |
| 386 | $pipeline = []; |
| 387 | |
| 388 | if (! empty($baseMatch)) { |
| 389 | $pipeline[] = ['$match' => $baseMatch]; |
| 390 | } |
| 391 | |
| 392 | $pipeline = array_merge($pipeline, [ |
| 393 | ['$facet' => [ |
| 394 | 'activated_licenses' => [['$match' => ['status' => 'Active']], ['$count' => 'count']], |
| 395 | 'invited_licenses' => [['$match' => ['status' => 'Invited']], ['$count' => 'count']], |
| 396 | 'extensions_installed' => [['$match' => ['status' => 'Active', 'is_any_extension_installed' => true]], ['$count' => 'count']], |
| 397 | ]], |
| 398 | ['$project' => [ |
| 399 | 'activated_licenses' => ['$ifNull' => [['$arrayElemAt' => ['$activated_licenses.count', 0]], 0]], |
| 400 | 'invited_licenses' => ['$ifNull' => [['$arrayElemAt' => ['$invited_licenses.count', 0]], 0]], |
| 401 | 'extensions_installed' => ['$ifNull' => [['$arrayElemAt' => ['$extensions_installed.count', 0]], 0]], |
| 402 | ]], |
| 403 | ]); |
| 404 | $result = UserInfo::raw(fn ($c) => $c->aggregate($pipeline))->first(); |
| 405 | $totalActivatedLicenses = $result->activated_licenses ?? 0; |
| 406 | $totalInvitedLicenses = $result->invited_licenses ?? 0; |
| 407 | |
| 408 | return response()->json(['data' => [ |
| 409 | 'licenses' => $totalLicenses, |
| 410 | 'remaining_licenses' => $totalLicenses - $totalAssignedUserLicenses - $totalAssignedInvitationLicenses, |
| 411 | 'activated_licenses' => $totalActivatedLicenses, |
| 412 | 'assigned_licenses' => $totalActivatedLicenses + $totalInvitedLicenses, |
| 413 | 'invitations' => $totalInvitedLicenses, |
| 414 | 'extensions_installed' => $result->extensions_installed ?? 0, |
| 415 | ]]); |
| 416 | } |
| 417 | |
| 418 | public function company_usage_overview(GetReportRequest $request): JsonResponse |
| 419 | { |
| 420 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 421 | $dailyMatch = $this->toDailyUsageMatch($baseMatch); |
| 422 | $companyId = $baseMatch['company_id']['$in'][0] ?? $request->user()->company_id; |
| 423 | [$startDate, $endDate] = $this->getDateRange($request, $dailyMatch, FlyMsgUserDailyUsage::class); |
| 424 | |
| 425 | $finalMatch = $dailyMatch; |
| 426 | $finalMatch['created_at'] = ['$gte' => new UTCDateTime($startDate), '$lte' => new UTCDateTime($endDate)]; |
| 427 | |
| 428 | $pipeline = [ |
| 429 | ['$match' => $finalMatch], |
| 430 | ['$group' => [ |
| 431 | '_id' => null, |
| 432 | 'cost' => ['$sum' => '$cost_savings'], |
| 433 | 'time' => ['$sum' => '$time_saved'], |
| 434 | 'chars' => ['$sum' => '$characters_typed'], |
| 435 | 'uniqueUserIds' => ['$addToSet' => '$user_id'], |
| 436 | ]], |
| 437 | ['$project' => [ |
| 438 | '_id' => 0, |
| 439 | 'cost' => 1, |
| 440 | 'time' => 1, |
| 441 | 'chars' => 1, |
| 442 | 'total_users' => ['$size' => '$uniqueUserIds'], |
| 443 | ]], |
| 444 | ]; |
| 445 | |
| 446 | $result = FlyMsgUserDailyUsage::raw(fn ($c) => $c->aggregate($pipeline))->first(); |
| 447 | $companySetting = Setting::where('company_id', $companyId)->first(); |
| 448 | $wageValue = round($companySetting?->wage_per_hour ?? 31.75, 2); |
| 449 | $totalUsers = $result->total_users ?? 1; |
| 450 | |
| 451 | return response()->json(['data' => [ |
| 452 | ['title' => 'ROI Spotlight', 'subtitle' => 'Total Cost Savings ($)', 'tooltip' => 'This shows the total cost savings in USD using the average US knowledge worker wage of $'.$wageValue.'/hour.', 'amount' => number_format(round($result->cost ?? 0, 2), 2), 'average' => ['value' => number_format(round(($result->cost ?? 0) / $totalUsers, 2), 2), 'label' => 'Average per User']], |
| 453 | ['title' => 'Productivity Spotlight', 'subtitle' => 'Total Time Saved (hrs)', 'tooltip' => 'This shows the total amount of time activated users saved over the selected period of time.', 'amount' => number_format(round($result->time ?? 0, 2), 2), 'average' => ['value' => number_format(round(($result->time ?? 0) / $totalUsers, 2), 2), 'label' => 'Average per User']], |
| 454 | ['title' => 'Total Characters Typed', 'subtitle' => 'Characters Typed', 'tooltip' => 'This shows the total amount of characters users typed over the selected period of time.', 'amount' => number_format(round($result->chars ?? 0, 0), 0), 'average' => ['value' => number_format(round(($result->chars ?? 0) / $totalUsers, 0), 2), 'label' => 'Average per User']], |
| 455 | ], 'success' => true]); |
| 456 | } |
| 457 | |
| 458 | public function export(GetReportRequest $request, string $type): StreamedResponse|JsonResponse |
| 459 | { |
| 460 | $baseMatch = $this->buildBaseMatchQuery($request); |
| 461 | $fileName = "report_{$type}_".now()->format('Y-m-d').'.csv'; |
| 462 | |
| 463 | $delimiter = $this->csvExportService->resolveDelimiter($request->user()); |
| 464 | |
| 465 | // Resolve columns BEFORE the streaming callback (where $request is not available) |
| 466 | $chartIdsParam = $request->input('chart_ids'); |
| 467 | $columns = $this->resolveExportColumns($type, $chartIdsParam); |
| 468 | |
| 469 | if (! empty($chartIdsParam) && empty($columns)) { |
| 470 | return response()->json([ |
| 471 | 'success' => false, |
| 472 | 'error' => 'None of the provided chart_ids match the report type.', |
| 473 | ], 422); |
| 474 | } |
| 475 | |
| 476 | // Determine if we need date-filtered data from FlyMsgUserDailyUsage |
| 477 | $hasDateFilter = $request->filled('from') && $request->filled('to'); |
| 478 | $dateFilteredAggregates = null; |
| 479 | |
| 480 | if ($hasDateFilter) { |
| 481 | $dateFilteredAggregates = $this->buildDateFilteredAggregates($baseMatch, $columns, $request); |
| 482 | } |
| 483 | |
| 484 | $callback = function () use ($type, $baseMatch, $delimiter, $columns, $hasDateFilter, $dateFilteredAggregates) { |
| 485 | $file = fopen('php://output', 'w'); |
| 486 | fprintf($file, chr(0xEF).chr(0xBB).chr(0xBF)); // Add BOM for Excel compatibility |
| 487 | |
| 488 | switch ($type) { |
| 489 | case 'effectiveness': |
| 490 | case 'usage': |
| 491 | // Headers: identity + selected data columns |
| 492 | $headers = ['User Name', 'Email', 'License', 'Extension Installed']; |
| 493 | foreach ($columns as $col) { |
| 494 | $headers[] = $col['header']; |
| 495 | } |
| 496 | fputcsv($file, $headers, $delimiter); |
| 497 | |
| 498 | // Get user identity data from UserInfo |
| 499 | $identityProjection = [ |
| 500 | '_id' => 0, 'user_id' => 1, 'full_name' => 1, 'email' => 1, |
| 501 | 'plan_name' => 1, 'is_any_extension_installed' => 1, |
| 502 | ]; |
| 503 | // Include UserInfo data columns when not using date filter |
| 504 | if (! $hasDateFilter) { |
| 505 | foreach ($columns as $col) { |
| 506 | $identityProjection[$col['field']] = 1; |
| 507 | } |
| 508 | } |
| 509 | |
| 510 | $pipeline = []; |
| 511 | if (! empty($baseMatch)) { |
| 512 | $pipeline[] = ['$match' => $baseMatch]; |
| 513 | } |
| 514 | $pipeline[] = ['$project' => $identityProjection]; |
| 515 | |
| 516 | $data = UserInfo::raw(fn ($c) => $c->aggregate($pipeline)); |
| 517 | foreach ($data as $row) { |
| 518 | $csvRow = [ |
| 519 | $row['full_name'], |
| 520 | $row['email'], |
| 521 | $row['plan_name'], |
| 522 | $row['is_any_extension_installed'] ? 'Yes' : 'No', |
| 523 | ]; |
| 524 | foreach ($columns as $col) { |
| 525 | if ($hasDateFilter && $dateFilteredAggregates !== null) { |
| 526 | $userId = $row['user_id'] ?? ''; |
| 527 | $csvRow[] = round($dateFilteredAggregates[$userId][$col['daily_field']] ?? 0, $col['round']); |
| 528 | } else { |
| 529 | $csvRow[] = round($row[$col['field']] ?? 0, $col['round']); |
| 530 | } |
| 531 | } |
| 532 | fputcsv($file, $csvRow, $delimiter); |
| 533 | } |
| 534 | break; |
| 535 | |
| 536 | case 'overview': |
| 537 | // Headers: identity columns (always included) + selected data columns |
| 538 | $headers = [ |
| 539 | 'User Name', 'Email', 'Account Creation', 'Extension Installed', |
| 540 | 'Last Login', 'License', 'Group', 'Subgroup', |
| 541 | ]; |
| 542 | foreach ($columns as $col) { |
| 543 | $headers[] = $col['header']; |
| 544 | } |
| 545 | fputcsv($file, $headers, $delimiter); |
| 546 | |
| 547 | // MongoDB projection: identity + selected fields |
| 548 | $projection = [ |
| 549 | '_id' => 0, 'user_id' => 1, |
| 550 | 'full_name' => 1, 'email' => 1, 'user_created_at' => 1, |
| 551 | 'is_any_extension_installed' => 1, 'last_login' => 1, |
| 552 | 'plan_name' => 1, 'group_name' => 1, 'subgroup_name' => 1, |
| 553 | ]; |
| 554 | if (! $hasDateFilter) { |
| 555 | foreach ($columns as $col) { |
| 556 | $projection[$col['field']] = 1; |
| 557 | } |
| 558 | } |
| 559 | |
| 560 | $pipeline = []; |
| 561 | if (! empty($baseMatch)) { |
| 562 | $pipeline[] = ['$match' => $baseMatch]; |
| 563 | } |
| 564 | $pipeline[] = ['$project' => $projection]; |
| 565 | |
| 566 | $data = UserInfo::raw(fn ($c) => $c->aggregate($pipeline)); |
| 567 | foreach ($data as $row) { |
| 568 | $csvRow = [ |
| 569 | $row['full_name'], |
| 570 | $row['email'], |
| 571 | $row['user_created_at'], |
| 572 | $row['is_any_extension_installed'] ? 'Yes' : 'No', |
| 573 | $row['last_login'], |
| 574 | $row['plan_name'], |
| 575 | $row['group_name'] ?? 'Not Assigned', |
| 576 | $row['subgroup_name'] ?? 'Not Assigned', |
| 577 | ]; |
| 578 | foreach ($columns as $col) { |
| 579 | if ($hasDateFilter && $dateFilteredAggregates !== null) { |
| 580 | $userId = $row['user_id'] ?? ''; |
| 581 | $csvRow[] = round($dateFilteredAggregates[$userId][$col['daily_field']] ?? 0, $col['round']); |
| 582 | } else { |
| 583 | $csvRow[] = round($row[$col['field']] ?? 0, $col['round']); |
| 584 | } |
| 585 | } |
| 586 | fputcsv($file, $csvRow, $delimiter); |
| 587 | } |
| 588 | break; |
| 589 | } |
| 590 | fclose($file); |
| 591 | }; |
| 592 | |
| 593 | return new StreamedResponse($callback, 200, [ |
| 594 | 'Content-Type' => 'text/csv', |
| 595 | 'Content-Disposition' => 'attachment; filename="'.$fileName.'"', |
| 596 | ]); |
| 597 | } |
| 598 | |
| 599 | /** |
| 600 | * Aggregate data from FlyMsgUserDailyUsage for the given date range, |
| 601 | * grouped by user_id. Returns an associative array keyed by user_id. |
| 602 | */ |
| 603 | private function buildDateFilteredAggregates(array $baseMatch, array $columns, GetReportRequest $request): array |
| 604 | { |
| 605 | $startDate = Carbon::parse($request->input('from'))->startOfDay(); |
| 606 | $endDate = Carbon::parse($request->input('to'))->endOfDay(); |
| 607 | |
| 608 | $dailyMatch = $this->toDailyUsageMatch($baseMatch); |
| 609 | $dailyMatch['created_at'] = [ |
| 610 | '$gte' => new UTCDateTime($startDate), |
| 611 | '$lte' => new UTCDateTime($endDate), |
| 612 | ]; |
| 613 | |
| 614 | // Build $sum aggregations for each selected column's daily field |
| 615 | $groupSums = ['_id' => '$user_id']; |
| 616 | foreach ($columns as $col) { |
| 617 | $dailyField = $col['daily_field']; |
| 618 | $groupSums[$dailyField] = ['$sum' => '$'.$dailyField]; |
| 619 | } |
| 620 | |
| 621 | $pipeline = [ |
| 622 | ['$match' => $dailyMatch], |
| 623 | ['$group' => $groupSums], |
| 624 | ]; |
| 625 | |
| 626 | $results = FlyMsgUserDailyUsage::raw(fn ($c) => $c->aggregate($pipeline)); |
| 627 | |
| 628 | $aggregates = []; |
| 629 | foreach ($results as $row) { |
| 630 | $userId = $row['_id']; |
| 631 | $aggregates[$userId] = []; |
| 632 | foreach ($columns as $col) { |
| 633 | $aggregates[$userId][$col['daily_field']] = $row[$col['daily_field']] ?? 0; |
| 634 | } |
| 635 | } |
| 636 | |
| 637 | return $aggregates; |
| 638 | } |
| 639 | |
| 640 | private function resolveExportColumns(string $type, ?string $chartIdsParam): array |
| 641 | { |
| 642 | $allColumns = match ($type) { |
| 643 | 'effectiveness' => $this->effectivenessColumns, |
| 644 | 'usage' => $this->usageColumns, |
| 645 | 'overview' => array_merge($this->effectivenessColumns, $this->usageColumns), |
| 646 | default => [], |
| 647 | }; |
| 648 | |
| 649 | if (empty($chartIdsParam)) { |
| 650 | return $allColumns; // No filter → export all (backward-compatible) |
| 651 | } |
| 652 | |
| 653 | $requestedIds = array_filter(explode(',', $chartIdsParam)); |
| 654 | return array_intersect_key($allColumns, array_flip($requestedIds)); |
| 655 | } |
| 656 | |
| 657 | private function buildBaseMatchQuery(Request $request): array |
| 658 | { |
| 659 | $user = $request->user(); |
| 660 | $roles = $user->roles(); |
| 661 | $baseMatch = ['status' => ['$ne' => 'Invited']]; |
| 662 | $processIds = fn ($ids) => array_values(array_filter(explode(',', $ids ?? ''))); |
| 663 | $isCmc = $request->input('cmc', false); |
| 664 | |
| 665 | $companyIds = $processIds($request->input('company_ids', $request->input('companyIds'))); |
| 666 | $userIds = $processIds($request->input('user_ids', $request->input('userIds'))); |
| 667 | $rawGroupIds = $processIds($request->input('group_ids', $request->input('groupIds'))); |
| 668 | $rawSubgroupIds = $processIds($request->input('subgroup_ids', $request->input('subgroupIds'))); |
| 669 | $hasNotAssignedGroup = in_array('-1', $rawGroupIds); |
| 670 | $hasNotAssignedSubgroup = in_array('-1', $rawSubgroupIds); |
| 671 | $groupIds = array_filter($rawGroupIds, fn ($id) => $id !== '-1'); |
| 672 | $subgroupIds = array_filter($rawSubgroupIds, fn ($id) => $id !== '-1'); |
| 673 | |
| 674 | if (! $isCmc && empty($companyIds)) { |
| 675 | $companyIds = [$user->company_id]; |
| 676 | // This is safe because GetReportRequest::authorize() already verified |
| 677 | // that non-VENGRESO_ADMIN users can only access their own company. |
| 678 | // For masquerade, the frontend must send company_ids explicitly. |
| 679 | } |
| 680 | |
| 681 | if (! in_array(Role::VENGRESO_ADMIN, $roles)) { |
| 682 | $companyIds = [$user->company_id]; |
| 683 | if (! in_array(Role::GLOBAL_ADMIN, $roles)) { |
| 684 | $managedGroupIds = CompanyGroup::where('company_id', $user->company_id) |
| 685 | ->where('admins', $user->id) |
| 686 | ->pluck('id')->all(); |
| 687 | $groupIds = ! empty($groupIds) ? array_intersect($groupIds, $managedGroupIds) : $managedGroupIds; |
| 688 | } |
| 689 | } |
| 690 | |
| 691 | $orConditions = []; |
| 692 | |
| 693 | if (! $isCmc) { |
| 694 | $baseMatch['company_id'] = ['$in' => $companyIds]; |
| 695 | } elseif (! empty($companyIds)) { |
| 696 | $orConditions[] = ['company_id' => ['$in' => $companyIds]]; |
| 697 | } |
| 698 | |
| 699 | if (! empty($userIds)) { |
| 700 | $orConditions[] = ['user_id' => ['$in' => $userIds]]; |
| 701 | } |
| 702 | if (! empty($groupIds)) { |
| 703 | $orConditions[] = ['group_id' => ['$in' => $groupIds]]; |
| 704 | } |
| 705 | if ($hasNotAssignedGroup) { |
| 706 | $orConditions[] = ['group_id' => null]; |
| 707 | } |
| 708 | if (! empty($subgroupIds)) { |
| 709 | $orConditions[] = ['subgroup_id' => ['$in' => $subgroupIds]]; |
| 710 | } |
| 711 | if ($hasNotAssignedSubgroup) { |
| 712 | $orConditions[] = ['subgroup_id' => null]; |
| 713 | } |
| 714 | |
| 715 | if (! empty($orConditions)) { |
| 716 | $baseMatch['$or'] = $orConditions; |
| 717 | } |
| 718 | |
| 719 | return $baseMatch; |
| 720 | } |
| 721 | |
| 722 | /** |
| 723 | * Remap $baseMatch keys for FlyMsgUserDailyUsage queries. |
| 724 | * UserInfo uses 'status', but DailyUsage uses 'user_status'. |
| 725 | */ |
| 726 | private function toDailyUsageMatch(array $baseMatch): array |
| 727 | { |
| 728 | if (isset($baseMatch['status'])) { |
| 729 | $baseMatch['user_status'] = $baseMatch['status']; |
| 730 | unset($baseMatch['status']); |
| 731 | } |
| 732 | |
| 733 | return $baseMatch; |
| 734 | } |
| 735 | |
| 736 | private function getDateRange(Request $request, array $baseMatch, string $model, string $dateField = 'created_at'): array |
| 737 | { |
| 738 | if ($request->filled('from') && $request->filled('to')) { |
| 739 | return [Carbon::parse($request->from)->startOfDay(), Carbon::parse($request->to)->endOfDay()]; |
| 740 | } |
| 741 | $datePipeline = []; |
| 742 | if (! empty($baseMatch)) { |
| 743 | $datePipeline[] = ['$match' => $baseMatch]; |
| 744 | } |
| 745 | $datePipeline[] = ['$group' => ['_id' => null, 'minDate' => ['$min' => '$'.$dateField], 'maxDate' => ['$max' => '$'.$dateField]]]; |
| 746 | |
| 747 | $dateResult = $model::raw(fn ($c) => $c->aggregate($datePipeline))->first(); |
| 748 | |
| 749 | if ($dateResult && $dateResult->minDate) { |
| 750 | $startDate = Carbon::createFromTimestampMs($dateResult->minDate->toDateTime()->getTimestamp() * 1000); |
| 751 | $endDate = Carbon::createFromTimestampMs($dateResult->maxDate->toDateTime()->getTimestamp() * 1000); |
| 752 | } else { |
| 753 | $startDate = Carbon::now()->subYear()->startOfDay(); |
| 754 | $endDate = Carbon::now()->endOfDay(); |
| 755 | } |
| 756 | |
| 757 | return [$startDate, $endDate]; |
| 758 | } |
| 759 | } |