Page MenuHomePhorge
Files F117879938

D3728.1775344326.diff
No OneTemporary
Actions

Authored By
Unknown
Size
16 KB
Referenced Files
None
Subscribers
None

D3728.1775344326.diff
View Options

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,90 @@
+<?php
+
+namespace App\Console\Commands\Data\Stats;
+
+use App\Http\Controllers\API\V4\Admin\StatsController;
+use App\Providers\PaymentProvider;
+use App\User;
+use Illuminate\Console\Command;
+use Illuminate\Support\Facades\DB;
+
+class CollectorCommand extends Command
+{
+ /**
+ * The name and signature of the console command.
+ *
+ * @var string
+ */
+ protected $signature = 'data:stats:collector';
+
+ /**
+ * The console command description.
+ *
+ * @var string
+ */
+ protected $description = 'Collects statictical data about the system (for charts)';
+
+ /**
+ * Execute the console command.
+ *
+ * @return mixed
+ */
+ public function handle()
+ {
+ $this->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);
+
+ if ($tenant_id) {
+ $count->where('users.tenant_id', $tenant_id);
+ } else {
+ $count->whereNull('users.tenant_id');
+ }
+
+ $count = $count->count();
+
+ // FIXME: Should we exclude suspended? or non-active?
+ // FIXME: Should we include active users with 100% discount (the may have no payments)?
+ // But they are not "payers", right?
+
+ if ($count) {
+ DB::table('stats')->insert([
+ 'tenant_id' => $tenant_id,
+ 'type' => StatsController::TYPE_PAYERS,
+ 'value' => $count,
+ ]);
+ }
+
+ // FIXME/TODO: We need to make sure this command is executed for every tenant,
+ // or we collect the overall count for the whole installation
+ // (all tenants separately?) here.
+ }
+}
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',
@@ -179,6 +182,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
*/
@@ -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 @@
+<?php
+
+use Illuminate\Support\Facades\Schema;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Database\Migrations\Migration;
+
+return new class extends Migration
+{
+ /**
+ * Run the migrations.
+ *
+ * @return void
+ */
+ public function up()
+ {
+ Schema::create('stats', function (Blueprint $table) {
+ $table->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 @@
+<?php
+
+namespace Tests\Feature\Console\Data\Stats;
+
+use App\Http\Controllers\API\V4\Admin\StatsController;
+use App\Providers\PaymentProvider;
+use Illuminate\Support\Facades\DB;
+use Tests\TestCase;
+
+class CollectorTest extends TestCase
+{
+ /**
+ * {@inheritDoc}
+ */
+ public function setUp(): void
+ {
+ parent::setUp();
+
+ DB::table('stats')->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]);
+ }
}

File Metadata

Mime Type
text/plain
Expires
Sat, Apr 4, 11:12 PM (22 h, 28 m ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
18831523
Default Alt Text
D3728.1775344326.diff (16 KB)

Event Timeline