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