diff --git a/src/app/Console/Commands/Data/Stats/CollectorCommand.php b/src/app/Console/Commands/Data/Stats/CollectorCommand.php new file mode 100644 index 00000000..a7bbf7d1 --- /dev/null +++ b/src/app/Console/Commands/Data/Stats/CollectorCommand.php @@ -0,0 +1,83 @@ +collectPayersCount(); + } + + /** + * Collect current payers count + */ + protected function collectPayersCount(): void + { + $tenant_id = \config('app.tenant_id'); + + // A subquery to get the all wallets with a successful payment + $payments = DB::table('payments') + ->distinct('wallet_id') + ->where('status', PaymentProvider::STATUS_PAID); + + // A subquery to get users' wallets (by entitlement) - one record per user + $wallets = DB::table('entitlements') + ->selectRaw("min(wallet_id) as id, entitleable_id as user_id") + ->where('entitleable_type', User::class) + ->groupBy('entitleable_id'); + + // Count all non-degraded and non-deleted users with any successful payment + $count = DB::table('users') + ->joinSub($wallets, 'wallets', function ($join) { + $join->on('users.id', '=', 'wallets.user_id'); + }) + ->joinSub($payments, 'payments', function ($join) { + $join->on('wallets.id', '=', 'payments.wallet_id'); + }) + ->whereNull('users.deleted_at') + ->whereNot('users.status', '&', User::STATUS_DEGRADED) + ->whereNot('users.status', '&', User::STATUS_SUSPENDED); + + if ($tenant_id) { + $count->where('users.tenant_id', $tenant_id); + } else { + $count->whereNull('users.tenant_id'); + } + + $count = $count->count(); + + if ($count) { + DB::table('stats')->insert([ + 'tenant_id' => $tenant_id, + 'type' => StatsController::TYPE_PAYERS, + 'value' => $count, + ]); + } + } +} diff --git a/src/app/Console/Kernel.php b/src/app/Console/Kernel.php index 3d239fbf..92d038bf 100644 --- a/src/app/Console/Kernel.php +++ b/src/app/Console/Kernel.php @@ -1,50 +1,53 @@ command('data:import')->dailyAt('05:00'); // This notifies users about coming password expiration $schedule->command('password:retention')->dailyAt('06:00'); // This applies wallet charges $schedule->command('wallet:charge')->everyFourHours(); // This removes deleted storage files/file chunks from the filesystem $schedule->command('fs:expunge')->hourly(); // This notifies users about an end of the trial period $schedule->command('wallet:trial-end')->dailyAt('07:00'); + + // This collects some statistics into the database + $schedule->command('data:stats:collector')->dailyAt('23:00'); } /** * Register the commands for the application. * * @return void */ protected function commands() { $this->load(__DIR__ . '/Commands'); if (\app('env') == 'local') { $this->load(__DIR__ . '/Development'); } include base_path('routes/console.php'); } } diff --git a/src/app/Http/Controllers/API/V4/Admin/StatsController.php b/src/app/Http/Controllers/API/V4/Admin/StatsController.php index ecc4a7c2..d018bd86 100644 --- a/src/app/Http/Controllers/API/V4/Admin/StatsController.php +++ b/src/app/Http/Controllers/API/V4/Admin/StatsController.php @@ -1,428 +1,514 @@ errorResponse(404); } $method = 'chart' . implode('', array_map('ucfirst', explode('-', $chart))); if (!in_array($chart, $this->charts) || !method_exists($this, $method)) { return $this->errorResponse(404); } $result = $this->{$method}(); return response()->json($result); } /** * Get discounts chart */ protected function chartDiscounts(): array { $discounts = DB::table('wallets') ->selectRaw("discount, count(discount_id) as cnt") ->join('discounts', 'discounts.id', '=', 'wallets.discount_id') ->join('users', 'users.id', '=', 'wallets.user_id') ->where('discount', '>', 0) ->whereNull('users.deleted_at') ->groupBy('discounts.discount'); $addTenantScope = function ($builder, $tenantId) { return $builder->where('users.tenant_id', $tenantId); }; $discounts = $this->applyTenantScope($discounts, $addTenantScope) ->pluck('cnt', 'discount')->all(); $labels = array_keys($discounts); $discounts = array_values($discounts); // $labels = [10, 25, 30, 100]; // $discounts = [100, 120, 30, 50]; $labels = array_map(function ($item) { return $item . '%'; }, $labels); return $this->donutChart(\trans('app.chart-discounts'), $labels, $discounts); } /** * Get income chart */ protected function chartIncome(): array { $weeks = 8; $start = Carbon::now(); $labels = []; while ($weeks > 0) { $labels[] = $start->format('Y-W'); $weeks--; if ($weeks) { $start->subWeeks(1); } } $labels = array_reverse($labels); $start->startOfWeek(Carbon::MONDAY); // FIXME: We're using wallets.currency instead of payments.currency and payments.currency_amount // as I believe this way we have more precise amounts for this use-case (and default currency) $query = DB::table('payments') ->selectRaw("date_format(updated_at, '%Y-%v') as period, sum(amount) as amount, wallets.currency") ->join('wallets', 'wallets.id', '=', 'wallet_id') ->where('updated_at', '>=', $start->toDateString()) ->where('status', PaymentProvider::STATUS_PAID) ->whereIn('type', [PaymentProvider::TYPE_ONEOFF, PaymentProvider::TYPE_RECURRING]) ->groupByRaw('period, wallets.currency'); $addTenantScope = function ($builder, $tenantId) { $where = sprintf( '`wallets`.`user_id` IN (select `id` from `users` where `tenant_id` = %d)', $tenantId ); return $builder->whereRaw($where); }; $currency = $this->currency(); $payments = []; $this->applyTenantScope($query, $addTenantScope) ->get() ->each(function ($record) use (&$payments, $currency) { $amount = $record->amount; if ($record->currency != $currency) { $amount = intval(round($amount * \App\Utils::exchangeRate($record->currency, $currency))); } if (isset($payments[$record->period])) { $payments[$record->period] += $amount / 100; } else { $payments[$record->period] = $amount / 100; } }); // TODO: exclude refunds/chargebacks $empty = array_fill_keys($labels, 0); $payments = array_values(array_merge($empty, $payments)); // $payments = [1000, 1200.25, 3000, 1897.50, 2000, 1900, 2134, 3330]; $avg = collect($payments)->slice(0, count($labels) - 1)->avg(); // See https://frappe.io/charts/docs for format/options description return [ 'title' => \trans('app.chart-income', ['currency' => $currency]), 'type' => 'bar', 'colors' => [self::COLOR_BLUE], 'axisOptions' => [ 'xIsSeries' => true, ], 'data' => [ 'labels' => $labels, 'datasets' => [ [ // 'name' => 'Payments', 'values' => $payments ] ], 'yMarkers' => [ [ 'label' => sprintf('average = %.2f', $avg), 'value' => $avg, 'options' => [ 'labelPos' => 'left' ] // default: 'right' ] ] ] ]; } + /** + * Get payers chart + */ + protected function chartPayers(): array + { + list($labels, $stats) = $this->getCollectedStats(self::TYPE_PAYERS, 54, fn($v) => intval($v)); + + // See https://frappe.io/charts/docs for format/options description + + return [ + 'title' => \trans('app.chart-payers'), + 'type' => 'line', + 'colors' => [self::COLOR_GREEN], + 'axisOptions' => [ + 'xIsSeries' => true, + 'xAxisMode' => 'tick', + ], + 'lineOptions' => [ + 'hideDots' => true, + 'regionFill' => true, + ], + 'data' => [ + 'labels' => $labels, + 'datasets' => [ + [ + // 'name' => 'Existing', + 'values' => $stats + ] + ] + ] + ]; + } + /** * Get created/deleted users chart */ protected function chartUsers(): array { $weeks = 8; $start = Carbon::now(); $labels = []; while ($weeks > 0) { $labels[] = $start->format('Y-W'); $weeks--; if ($weeks) { $start->subWeeks(1); } } $labels = array_reverse($labels); $start->startOfWeek(Carbon::MONDAY); $created = DB::table('users') ->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") ->where('created_at', '>=', $start->toDateString()) ->groupByRaw('1'); $deleted = DB::table('users') ->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") ->where('deleted_at', '>=', $start->toDateString()) ->groupByRaw('1'); $created = $this->applyTenantScope($created)->get(); $deleted = $this->applyTenantScope($deleted)->get(); $empty = array_fill_keys($labels, 0); $created = array_values(array_merge($empty, $created->pluck('cnt', 'period')->all())); $deleted = array_values(array_merge($empty, $deleted->pluck('cnt', 'period')->all())); // $created = [5, 2, 4, 2, 0, 5, 2, 4]; // $deleted = [1, 2, 3, 1, 2, 1, 2, 3]; $avg = collect($created)->slice(0, count($labels) - 1)->avg(); // See https://frappe.io/charts/docs for format/options description return [ 'title' => \trans('app.chart-users'), 'type' => 'bar', // Required to fix https://github.com/frappe/charts/issues/294 'colors' => [self::COLOR_GREEN, self::COLOR_RED], 'axisOptions' => [ 'xIsSeries' => true, ], 'data' => [ 'labels' => $labels, 'datasets' => [ [ 'name' => \trans('app.chart-created'), 'chartType' => 'bar', 'values' => $created ], [ 'name' => \trans('app.chart-deleted'), 'chartType' => 'line', 'values' => $deleted ] ], 'yMarkers' => [ [ 'label' => sprintf('%s = %.1f', \trans('app.chart-average'), $avg), 'value' => collect($created)->avg(), 'options' => [ 'labelPos' => 'left' ] // default: 'right' ] ] ] ]; } /** * Get all users chart */ protected function chartUsersAll(): array { $weeks = 54; $start = Carbon::now(); $labels = []; while ($weeks > 0) { $labels[] = $start->format('Y-W'); $weeks--; if ($weeks) { $start->subWeeks(1); } } $labels = array_reverse($labels); $start->startOfWeek(Carbon::MONDAY); $created = DB::table('users') ->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") ->where('created_at', '>=', $start->toDateString()) ->groupByRaw('1'); $deleted = DB::table('users') ->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") ->where('deleted_at', '>=', $start->toDateString()) ->groupByRaw('1'); $created = $this->applyTenantScope($created)->get(); $deleted = $this->applyTenantScope($deleted)->get(); $count = $this->applyTenantScope(DB::table('users')->whereNull('deleted_at'))->count(); $empty = array_fill_keys($labels, 0); $created = array_merge($empty, $created->pluck('cnt', 'period')->all()); $deleted = array_merge($empty, $deleted->pluck('cnt', 'period')->all()); $all = []; foreach (array_reverse($labels) as $label) { $all[] = $count; $count -= $created[$label] - $deleted[$label]; } $all = array_reverse($all); // $start = 3000; // for ($i = 0; $i < count($labels); $i++) { // $all[$i] = $start + $i * 15; // } // See https://frappe.io/charts/docs for format/options description return [ 'title' => \trans('app.chart-allusers'), 'type' => 'line', 'colors' => [self::COLOR_GREEN], 'axisOptions' => [ 'xIsSeries' => true, 'xAxisMode' => 'tick', ], 'lineOptions' => [ 'hideDots' => true, 'regionFill' => true, ], 'data' => [ 'labels' => $labels, 'datasets' => [ [ // 'name' => 'Existing', 'values' => $all ] ] ] ]; } /** * Get vouchers chart */ protected function chartVouchers(): array { $vouchers = DB::table('wallets') ->selectRaw("count(discount_id) as cnt, code") ->join('discounts', 'discounts.id', '=', 'wallets.discount_id') ->join('users', 'users.id', '=', 'wallets.user_id') ->where('discount', '>', 0) ->whereNotNull('code') ->whereNull('users.deleted_at') ->groupBy('discounts.code') ->havingRaw("count(discount_id) > 0") ->orderByRaw('1'); $addTenantScope = function ($builder, $tenantId) { return $builder->where('users.tenant_id', $tenantId); }; $vouchers = $this->applyTenantScope($vouchers, $addTenantScope) ->pluck('cnt', 'code')->all(); $labels = array_keys($vouchers); $vouchers = array_values($vouchers); // $labels = ["TEST", "NEW", "OTHER", "US"]; // $vouchers = [100, 120, 30, 50]; return $this->donutChart(\trans('app.chart-vouchers'), $labels, $vouchers); } protected static function donutChart($title, $labels, $data): array { // See https://frappe.io/charts/docs for format/options description return [ 'title' => $title, 'type' => 'donut', 'colors' => [ self::COLOR_BLUE, self::COLOR_BLUE_DARK, self::COLOR_GREEN, self::COLOR_GREEN_DARK, self::COLOR_ORANGE, self::COLOR_RED, self::COLOR_RED_DARK ], 'maxSlices' => 8, 'tooltipOptions' => [], // does not work without it (https://github.com/frappe/charts/issues/314) 'data' => [ 'labels' => $labels, 'datasets' => [ [ 'values' => $data ] ] ] ]; } /** * Add tenant scope to the queries when needed * * @param \Illuminate\Database\Query\Builder $query The query * @param callable $addQuery Additional tenant-scope query-modifier * * @return \Illuminate\Database\Query\Builder */ protected function applyTenantScope($query, $addQuery = null) { // TODO: Per-tenant stats for admins return $query; } /** * Get the currency for stats * * @return string Currency code */ protected function currency() { $user = $this->guard()->user(); // For resellers return their wallet currency if ($user->role == 'reseller') { $currency = $user->wallet()->currency; } // System currency for others return \config('app.currency'); } + + /** + * Get collected stats for a specific type/period + * + * @param int $type Chart + * @param int $weeks Number of weeks back from now + * @param ?callable $itemCallback A callback to execute on every stat item + * + * @return array [ labels, stats ] + */ + protected function getCollectedStats(int $type, int $weeks, $itemCallback = null): array + { + $start = Carbon::now(); + $labels = []; + + while ($weeks > 0) { + $labels[] = $start->format('Y-W'); + $weeks--; + if ($weeks) { + $start->subWeeks(1); + } + } + + $labels = array_reverse($labels); + $start->startOfWeek(Carbon::MONDAY); + + // Get the stats grouped by tenant and week + $stats = DB::table('stats') + ->selectRaw("tenant_id, date_format(created_at, '%Y-%v') as period, avg(value) as cnt") + ->where('type', $type) + ->where('created_at', '>=', $start->toDateString()) + ->groupByRaw('1,2'); + + // Get the query result and sum up per-tenant stats + $result = []; + $this->applyTenantScope($stats)->get() + ->each(function ($item) use (&$result) { + $result[$item->period] = ($result[$item->period] ?? 0) + $item->cnt; + }); + + // Process the result, e.g. convert values to int + if ($itemCallback) { + $result = array_map($itemCallback, $result); + } + + // Fill the missing weeks with zeros + $result = array_values(array_merge(array_fill_keys($labels, 0), $result)); + + return [$labels, $result]; + } } diff --git a/src/app/Http/Controllers/API/V4/Reseller/StatsController.php b/src/app/Http/Controllers/API/V4/Reseller/StatsController.php index 9bff1132..a82ddaf7 100644 --- a/src/app/Http/Controllers/API/V4/Reseller/StatsController.php +++ b/src/app/Http/Controllers/API/V4/Reseller/StatsController.php @@ -1,37 +1,38 @@ user(); $query = $addQuery($query, $user->tenant_id); } else { $query = $query->withSubjectTenantContext(); } return $query; } } diff --git a/src/database/migrations/2022_07_19_100000_create_stats_table.php b/src/database/migrations/2022_07_19_100000_create_stats_table.php new file mode 100644 index 00000000..e1ef84d1 --- /dev/null +++ b/src/database/migrations/2022_07_19_100000_create_stats_table.php @@ -0,0 +1,40 @@ +bigIncrements('id'); + $table->unsignedBigInteger('tenant_id')->nullable(); + $table->integer('type')->unsigned(); + $table->bigInteger('value'); + $table->timestamp('created_at')->useCurrent(); + + $table->index(['type', 'created_at', 'tenant_id']); + $table->index('tenant_id'); + + $table->foreign('tenant_id')->references('id')->on('tenants') + ->onDelete('cascade')->onUpdate('cascade'); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('stats'); + } +}; diff --git a/src/resources/lang/en/app.php b/src/resources/lang/en/app.php index 715290e5..e955bec3 100644 --- a/src/resources/lang/en/app.php +++ b/src/resources/lang/en/app.php @@ -1,142 +1,143 @@ 'Created', 'chart-deleted' => 'Deleted', 'chart-average' => 'average', 'chart-allusers' => 'All Users - last year', 'chart-discounts' => 'Discounts', 'chart-vouchers' => 'Vouchers', 'chart-income' => 'Income in :currency - last 8 weeks', + 'chart-payers' => 'Payers - last year', 'chart-users' => 'Users - last 8 weeks', 'companion-deleteall-success' => 'All companion apps have been removed.', 'mandate-delete-success' => 'The auto-payment has been removed.', 'mandate-update-success' => 'The auto-payment has been updated.', 'planbutton' => 'Choose :plan', 'process-async' => 'Setup process has been pushed. Please wait.', 'process-user-new' => 'Registering a user...', 'process-user-ldap-ready' => 'Creating a user...', 'process-user-imap-ready' => 'Creating a mailbox...', 'process-domain-new' => 'Registering a custom domain...', 'process-domain-ldap-ready' => 'Creating a custom domain...', 'process-domain-verified' => 'Verifying a custom domain...', 'process-domain-confirmed' => 'Verifying an ownership of a custom domain...', 'process-success' => 'Setup process finished successfully.', 'process-error-distlist-ldap-ready' => 'Failed to create a distribution list.', 'process-error-domain-ldap-ready' => 'Failed to create a domain.', 'process-error-domain-verified' => 'Failed to verify a domain.', 'process-error-domain-confirmed' => 'Failed to verify an ownership of a domain.', 'process-error-resource-imap-ready' => 'Failed to verify that a shared folder exists.', 'process-error-resource-ldap-ready' => 'Failed to create a resource.', 'process-error-shared-folder-imap-ready' => 'Failed to verify that a shared folder exists.', 'process-error-shared-folder-ldap-ready' => 'Failed to create a shared folder.', 'process-error-user-ldap-ready' => 'Failed to create a user.', 'process-error-user-imap-ready' => 'Failed to verify that a mailbox exists.', 'process-distlist-new' => 'Registering a distribution list...', 'process-distlist-ldap-ready' => 'Creating a distribution list...', 'process-resource-new' => 'Registering a resource...', 'process-resource-imap-ready' => 'Creating a shared folder...', 'process-resource-ldap-ready' => 'Creating a resource...', 'process-shared-folder-new' => 'Registering a shared folder...', 'process-shared-folder-imap-ready' => 'Creating a shared folder...', 'process-shared-folder-ldap-ready' => 'Creating a shared folder...', 'distlist-update-success' => 'Distribution list updated successfully.', 'distlist-create-success' => 'Distribution list created successfully.', 'distlist-delete-success' => 'Distribution list deleted successfully.', 'distlist-suspend-success' => 'Distribution list suspended successfully.', 'distlist-unsuspend-success' => 'Distribution list unsuspended successfully.', 'distlist-setconfig-success' => 'Distribution list settings updated successfully.', 'domain-create-success' => 'Domain created successfully.', 'domain-delete-success' => 'Domain deleted successfully.', 'domain-notempty-error' => 'Unable to delete a domain with assigned users or other objects.', 'domain-verify-success' => 'Domain verified successfully.', 'domain-verify-error' => 'Domain ownership verification failed.', 'domain-suspend-success' => 'Domain suspended successfully.', 'domain-unsuspend-success' => 'Domain unsuspended successfully.', 'domain-setconfig-success' => 'Domain settings updated successfully.', 'file-create-success' => 'File created successfully.', 'file-delete-success' => 'File deleted successfully.', 'file-update-success' => 'File updated successfully.', 'file-permissions-create-success' => 'File permissions created successfully.', 'file-permissions-update-success' => 'File permissions updated successfully.', 'file-permissions-delete-success' => 'File permissions deleted successfully.', 'resource-update-success' => 'Resource updated successfully.', 'resource-create-success' => 'Resource created successfully.', 'resource-delete-success' => 'Resource deleted successfully.', 'resource-setconfig-success' => 'Resource settings updated successfully.', 'room-update-success' => 'Room updated successfully.', 'room-create-success' => 'Room created successfully.', 'room-delete-success' => 'Room deleted successfully.', 'room-setconfig-success' => 'Room configuration updated successfully.', 'room-unsupported-option-error' => 'Invalid room configuration option.', 'shared-folder-update-success' => 'Shared folder updated successfully.', 'shared-folder-create-success' => 'Shared folder created successfully.', 'shared-folder-delete-success' => 'Shared folder deleted successfully.', 'shared-folder-setconfig-success' => 'Shared folder settings updated successfully.', 'user-update-success' => 'User data updated successfully.', 'user-create-success' => 'User created successfully.', 'user-delete-success' => 'User deleted successfully.', 'user-suspend-success' => 'User suspended successfully.', 'user-unsuspend-success' => 'User unsuspended successfully.', 'user-reset-2fa-success' => '2-Factor authentication reset successfully.', 'user-reset-geo-lock-success' => 'Geo-lockin setup reset successfully.', 'user-setconfig-success' => 'User settings updated successfully.', 'user-set-sku-success' => 'The subscription added successfully.', 'user-set-sku-already-exists' => 'The subscription already exists.', 'search-foundxdomains' => ':x domains have been found.', 'search-foundxdistlists' => ':x distribution lists have been found.', 'search-foundxresources' => ':x resources have been found.', 'search-foundxshared-folders' => ':x shared folders have been found.', 'search-foundxusers' => ':x user accounts have been found.', 'signup-invitations-created' => 'The invitation has been created.|:count invitations has been created.', 'signup-invitations-csv-empty' => 'Failed to find any valid email addresses in the uploaded file.', 'signup-invitations-csv-invalid-email' => 'Found an invalid email address (:email) on line :line.', 'signup-invitation-delete-success' => 'Invitation deleted successfully.', 'signup-invitation-resend-success' => 'Invitation added to the sending queue successfully.', 'support-request-success' => 'Support request submitted successfully.', 'support-request-error' => 'Failed to submit the support request.', 'siteuser' => ':site User', 'wallet-award-success' => 'The bonus has been added to the wallet successfully.', 'wallet-penalty-success' => 'The penalty has been added to the wallet successfully.', 'wallet-update-success' => 'User wallet updated successfully.', 'password-reset-code-delete-success' => 'Password reset code deleted successfully.', 'password-rule-min' => 'Minimum password length: :param characters', 'password-rule-max' => 'Maximum password length: :param characters', 'password-rule-lower' => 'Password contains a lower-case character', 'password-rule-upper' => 'Password contains an upper-case character', 'password-rule-digit' => 'Password contains a digit', 'password-rule-special' => 'Password contains a special character', 'password-rule-last' => 'Password cannot be the same as the last :param passwords', 'wallet-notice-date' => 'With your current subscriptions your account balance will last until about :date (:days).', 'wallet-notice-nocredit' => 'You are out of credit, top up your balance now.', 'wallet-notice-today' => 'You will run out of credit today, top up your balance now.', 'wallet-notice-trial' => 'You are in your free trial period.', 'wallet-notice-trial-end' => 'Your free trial is about to end, top up to continue.', ]; diff --git a/src/resources/vue/Admin/Stats.vue b/src/resources/vue/Admin/Stats.vue index c45db4c2..6aad0624 100644 --- a/src/resources/vue/Admin/Stats.vue +++ b/src/resources/vue/Admin/Stats.vue @@ -1,42 +1,42 @@ diff --git a/src/resources/vue/Reseller/Stats.vue b/src/resources/vue/Reseller/Stats.vue index 88be2302..a56c8a2c 100644 --- a/src/resources/vue/Reseller/Stats.vue +++ b/src/resources/vue/Reseller/Stats.vue @@ -1,16 +1,16 @@ diff --git a/src/tests/Browser/Admin/StatsTest.php b/src/tests/Browser/Admin/StatsTest.php index 0cb3aec4..3f2a2bff 100644 --- a/src/tests/Browser/Admin/StatsTest.php +++ b/src/tests/Browser/Admin/StatsTest.php @@ -1,42 +1,43 @@ browse(function (Browser $browser) { $browser->visit(new Home()) ->submitLogon('jeroen@jeroen.jeroen', \App\Utils::generatePassphrase(), true) ->on(new Dashboard()) ->assertSeeIn('@links .link-stats', 'Stats') ->click('@links .link-stats') ->on(new Stats()) - ->assertElementsCount('@container > div', 5) + ->assertElementsCount('@container > div', 6) ->waitForTextIn('@container #chart-users svg .title', 'Users - last 8 weeks') ->waitForTextIn('@container #chart-users-all svg .title', 'All Users - last year') + ->waitForTextIn('@container #chart-payers svg .title', 'Payers - last year') ->waitForTextIn('@container #chart-income svg .title', 'Income in CHF - last 8 weeks') ->waitForTextIn('@container #chart-discounts svg .title', 'Discounts') ->waitForTextIn('@container #chart-vouchers svg .title', 'Vouchers'); }); } } diff --git a/src/tests/Browser/Reseller/StatsTest.php b/src/tests/Browser/Reseller/StatsTest.php index 29c3d961..5819e8dd 100644 --- a/src/tests/Browser/Reseller/StatsTest.php +++ b/src/tests/Browser/Reseller/StatsTest.php @@ -1,52 +1,53 @@ browse(function (Browser $browser) { $browser->visit('/stats')->on(new Home()); }); } /** * Test Stats page */ public function testStats(): void { $this->browse(function (Browser $browser) { $browser->visit(new Home()) ->submitLogon('reseller@' . \config('app.domain'), \App\Utils::generatePassphrase(), true) ->on(new Dashboard()) ->assertSeeIn('@links .link-stats', 'Stats') ->click('@links .link-stats') ->on(new Stats()) - ->assertElementsCount('@container > div', 4) + ->assertElementsCount('@container > div', 5) ->waitForTextIn('@container #chart-users svg .title', 'Users - last 8 weeks') ->waitForTextIn('@container #chart-users-all svg .title', 'All Users - last year') + ->waitForTextIn('@container #chart-payers svg .title', 'Payers - last year') ->waitForTextIn('@container #chart-discounts svg .title', 'Discounts') ->waitForTextIn('@container #chart-vouchers svg .title', 'Vouchers'); }); } } diff --git a/src/tests/Feature/Console/Data/Stats/CollectorTest.php b/src/tests/Feature/Console/Data/Stats/CollectorTest.php new file mode 100644 index 00000000..3e692a59 --- /dev/null +++ b/src/tests/Feature/Console/Data/Stats/CollectorTest.php @@ -0,0 +1,77 @@ +truncate(); + DB::table('payments')->truncate(); + } + + /** + * {@inheritDoc} + */ + public function tearDown(): void + { + DB::table('stats')->truncate(); + DB::table('payments')->truncate(); + + parent::tearDown(); + } + + /** + * Test the command + */ + public function testHandle(): void + { + $code = \Artisan::call("data:stats:collector"); + $output = trim(\Artisan::output()); + + $this->assertSame(0, $code); + + $stats = DB::table('stats')->get(); + + $this->assertSame(0, $stats->count()); + + $john = $this->getTestUser('john@kolab.org'); + $wallet = $john->wallet(); + + \App\Payment::create([ + 'id' => 'test1', + 'description' => '', + 'status' => PaymentProvider::STATUS_PAID, + 'amount' => 1000, + 'type' => PaymentProvider::TYPE_ONEOFF, + 'wallet_id' => $wallet->id, + 'provider' => 'mollie', + 'currency' => $wallet->currency, + 'currency_amount' => 1000, + ]); + + $code = \Artisan::call("data:stats:collector"); + $output = trim(\Artisan::output()); + + $this->assertSame(0, $code); + + $stats = DB::table('stats')->get(); + + $this->assertSame(1, $stats->count()); + $this->assertSame(StatsController::TYPE_PAYERS, $stats[0]->type); + $this->assertEquals(\config('app.tenant_id'), $stats[0]->tenant_id); + $this->assertEquals(4, $stats[0]->value); // there's 4 users in john's wallet + + // TODO: More precise tests (degraded users) + } +} diff --git a/src/tests/Feature/Controller/Admin/StatsTest.php b/src/tests/Feature/Controller/Admin/StatsTest.php index 77d575c4..f7caf8c5 100644 --- a/src/tests/Feature/Controller/Admin/StatsTest.php +++ b/src/tests/Feature/Controller/Admin/StatsTest.php @@ -1,211 +1,252 @@ update(['discount_id' => null]); } /** * {@inheritDoc} */ public function tearDown(): void { Payment::truncate(); DB::table('wallets')->update(['discount_id' => null]); parent::tearDown(); } /** * Test charts (GET /api/v4/stats/chart/) */ public function testChart(): void { $user = $this->getTestUser('john@kolab.org'); $admin = $this->getTestUser('jeroen@jeroen.jeroen'); // Non-admin user $response = $this->actingAs($user)->get("api/v4/stats/chart/discounts"); $response->assertStatus(403); // Unknown chart name $response = $this->actingAs($admin)->get("api/v4/stats/chart/unknown"); $response->assertStatus(404); // 'discounts' chart $response = $this->actingAs($admin)->get("api/v4/stats/chart/discounts"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('Discounts', $json['title']); $this->assertSame('donut', $json['type']); $this->assertSame([], $json['data']['labels']); $this->assertSame([['values' => []]], $json['data']['datasets']); // 'income' chart $response = $this->actingAs($admin)->get("api/v4/stats/chart/income"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('Income in CHF - last 8 weeks', $json['title']); $this->assertSame('bar', $json['type']); $this->assertCount(8, $json['data']['labels']); $this->assertSame(date('Y-W'), $json['data']['labels'][7]); $this->assertSame([['values' => [0,0,0,0,0,0,0,0]]], $json['data']['datasets']); // 'users' chart $response = $this->actingAs($admin)->get("api/v4/stats/chart/users"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('Users - last 8 weeks', $json['title']); $this->assertCount(8, $json['data']['labels']); $this->assertSame(date('Y-W'), $json['data']['labels'][7]); $this->assertCount(2, $json['data']['datasets']); $this->assertSame('Created', $json['data']['datasets'][0]['name']); $this->assertSame('Deleted', $json['data']['datasets'][1]['name']); // 'users-all' chart $response = $this->actingAs($admin)->get("api/v4/stats/chart/users-all"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('All Users - last year', $json['title']); $this->assertCount(54, $json['data']['labels']); $this->assertCount(1, $json['data']['datasets']); // 'vouchers' chart $discount = \App\Discount::withObjectTenantContext($user)->where('code', 'TEST')->first(); $wallet = $user->wallets->first(); $wallet->discount()->associate($discount); $wallet->save(); $response = $this->actingAs($admin)->get("api/v4/stats/chart/vouchers"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('Vouchers', $json['title']); $this->assertSame(['TEST'], $json['data']['labels']); $this->assertSame([['values' => [1]]], $json['data']['datasets']); } /** * Test income chart currency handling */ public function testChartIncomeCurrency(): void { $admin = $this->getTestUser('jeroen@jeroen.jeroen'); $john = $this->getTestUser('john@kolab.org'); $user = $this->getTestUser('test-stats@' . \config('app.domain')); $wallet = $user->wallets()->first(); $wallet->currency = 'EUR'; $wallet->save(); $johns_wallet = $john->wallets()->first(); // Create some test payments Payment::create([ 'id' => 'test1', 'description' => '', 'status' => PaymentProvider::STATUS_PAID, 'amount' => 1000, // EUR 'type' => PaymentProvider::TYPE_ONEOFF, 'wallet_id' => $wallet->id, 'provider' => 'mollie', 'currency' => 'EUR', 'currency_amount' => 1000, ]); Payment::create([ 'id' => 'test2', 'description' => '', 'status' => PaymentProvider::STATUS_PAID, 'amount' => 2000, // EUR 'type' => PaymentProvider::TYPE_RECURRING, 'wallet_id' => $wallet->id, 'provider' => 'mollie', 'currency' => 'EUR', 'currency_amount' => 2000, ]); Payment::create([ 'id' => 'test3', 'description' => '', 'status' => PaymentProvider::STATUS_PAID, 'amount' => 3000, // CHF 'type' => PaymentProvider::TYPE_ONEOFF, 'wallet_id' => $johns_wallet->id, 'provider' => 'mollie', 'currency' => 'EUR', 'currency_amount' => 2800, ]); Payment::create([ 'id' => 'test4', 'description' => '', 'status' => PaymentProvider::STATUS_PAID, 'amount' => 4000, // CHF 'type' => PaymentProvider::TYPE_RECURRING, 'wallet_id' => $johns_wallet->id, 'provider' => 'mollie', 'currency' => 'CHF', 'currency_amount' => 4000, ]); Payment::create([ 'id' => 'test5', 'description' => '', 'status' => PaymentProvider::STATUS_OPEN, 'amount' => 5000, // CHF 'type' => PaymentProvider::TYPE_ONEOFF, 'wallet_id' => $johns_wallet->id, 'provider' => 'mollie', 'currency' => 'CHF', 'currency_amount' => 5000, ]); Payment::create([ 'id' => 'test6', 'description' => '', 'status' => PaymentProvider::STATUS_FAILED, 'amount' => 6000, // CHF 'type' => PaymentProvider::TYPE_ONEOFF, 'wallet_id' => $johns_wallet->id, 'provider' => 'mollie', 'currency' => 'CHF', 'currency_amount' => 6000, ]); // 'income' chart $response = $this->actingAs($admin)->get("api/v4/stats/chart/income"); $response->assertStatus(200); $json = $response->json(); $this->assertSame('Income in CHF - last 8 weeks', $json['title']); $this->assertSame('bar', $json['type']); $this->assertCount(8, $json['data']['labels']); $this->assertSame(date('Y-W'), $json['data']['labels'][7]); // 7000 CHF + 3000 EUR = $expected = 7000 + intval(round(3000 * \App\Utils::exchangeRate('EUR', 'CHF'))); $this->assertCount(1, $json['data']['datasets']); $this->assertSame($expected / 100, $json['data']['datasets'][0]['values'][7]); } + + /** + * Test payers chart + */ + public function testChartPayers(): void + { + $admin = $this->getTestUser('jeroen@jeroen.jeroen'); + + DB::table('stats')->truncate(); + + $response = $this->actingAs($admin)->get("api/v4/stats/chart/payers"); + $response->assertStatus(200); + + $json = $response->json(); + + $this->assertSame('Payers - last year', $json['title']); + $this->assertSame('line', $json['type']); + $this->assertCount(54, $json['data']['labels']); + $this->assertSame(date('Y-W'), $json['data']['labels'][53]); + $this->assertCount(1, $json['data']['datasets']); + $this->assertCount(54, $json['data']['datasets'][0]['values']); + + DB::table('stats')->insert([ + 'type' => StatsController::TYPE_PAYERS, + 'value' => 5, + 'created_at' => \now(), + ]); + DB::table('stats')->insert([ + 'type' => StatsController::TYPE_PAYERS, + 'value' => 7, + 'created_at' => \now(), + ]); + + $response = $this->actingAs($admin)->get("api/v4/stats/chart/payers"); + $response->assertStatus(200); + + $json = $response->json(); + + $this->assertSame(6, $json['data']['datasets'][0]['values'][53]); + } }