diff --git a/src/app/Console/Commands/Data/Stats/CollectorCommand.php b/src/app/Console/Commands/Data/Stats/CollectorCommand.php new file mode 100644 --- /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 --- a/src/app/Console/Kernel.php +++ b/src/app/Console/Kernel.php @@ -30,6 +30,9 @@ // 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'); } /** diff --git a/src/app/Http/Controllers/API/V4/Admin/StatsController.php b/src/app/Http/Controllers/API/V4/Admin/StatsController.php --- a/src/app/Http/Controllers/API/V4/Admin/StatsController.php +++ b/src/app/Http/Controllers/API/V4/Admin/StatsController.php @@ -18,10 +18,13 @@ public const COLOR_BLUE_DARK = '#0056b3'; public const COLOR_ORANGE = '#f1a539'; + public const TYPE_PAYERS = 1; + /** @var array List of enabled charts */ protected $charts = [ 'discounts', 'income', + 'payers', 'users', 'users-all', 'vouchers', @@ -180,6 +183,39 @@ } /** + * 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 @@ -425,4 +461,54 @@ // 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 --- a/src/app/Http/Controllers/API/V4/Reseller/StatsController.php +++ b/src/app/Http/Controllers/API/V4/Reseller/StatsController.php @@ -10,6 +10,7 @@ protected $charts = [ 'discounts', // 'income', + 'payers', 'users', 'users-all', 'vouchers', 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 --- /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 --- a/src/resources/lang/en/app.php +++ b/src/resources/lang/en/app.php @@ -17,6 +17,7 @@ '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.', diff --git a/src/resources/vue/Admin/Stats.vue b/src/resources/vue/Admin/Stats.vue --- a/src/resources/vue/Admin/Stats.vue +++ b/src/resources/vue/Admin/Stats.vue @@ -9,7 +9,7 @@ data() { return { charts: {}, - chartTypes: ['users', 'users-all', 'income', 'discounts', 'vouchers'] + chartTypes: ['users', 'users-all', 'income', 'payers', 'discounts', 'vouchers'] } }, mounted() { diff --git a/src/resources/vue/Reseller/Stats.vue b/src/resources/vue/Reseller/Stats.vue --- a/src/resources/vue/Reseller/Stats.vue +++ b/src/resources/vue/Reseller/Stats.vue @@ -9,7 +9,7 @@ mixins: [Stats], data() { return { - chartTypes: ['users', 'users-all', 'discounts', 'vouchers'] + chartTypes: ['users', 'users-all', 'payers', 'discounts', 'vouchers'] } } } diff --git a/src/tests/Browser/Admin/StatsTest.php b/src/tests/Browser/Admin/StatsTest.php --- a/src/tests/Browser/Admin/StatsTest.php +++ b/src/tests/Browser/Admin/StatsTest.php @@ -31,9 +31,10 @@ ->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 --- a/src/tests/Browser/Reseller/StatsTest.php +++ b/src/tests/Browser/Reseller/StatsTest.php @@ -42,9 +42,10 @@ ->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 --- /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 --- a/src/tests/Feature/Controller/Admin/StatsTest.php +++ b/src/tests/Feature/Controller/Admin/StatsTest.php @@ -2,6 +2,7 @@ namespace Tests\Feature\Controller\Admin; +use App\Http\Controllers\API\V4\Admin\StatsController; use App\Payment; use App\Providers\PaymentProvider; use Illuminate\Support\Facades\DB; @@ -208,4 +209,44 @@ $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]); + } }