Page MenuHomePhorge
Files F256645

No OneTemporary
Actions

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
index 00000000..a7bbf7d1
--- /dev/null
+++ b/src/app/Console/Commands/Data/Stats/CollectorCommand.php
@@ -0,0 +1,83 @@
+<?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)
+ ->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 @@
<?php
namespace App\Console;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
class Kernel extends ConsoleKernel
{
/**
* Define the application's command schedule.
*
* @param Schedule $schedule The application's command schedule
*
* @return void
*/
protected function schedule(Schedule $schedule)
{
// This imports countries and the current set of IPv4 and IPv6 networks allocated to countries.
$schedule->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 @@
<?php
namespace App\Http\Controllers\API\V4\Admin;
use App\Providers\PaymentProvider;
use App\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
class StatsController extends \App\Http\Controllers\Controller
{
public const COLOR_GREEN = '#48d368'; // '#28a745'
public const COLOR_GREEN_DARK = '#19692c';
public const COLOR_RED = '#e77681'; // '#dc3545'
public const COLOR_RED_DARK = '#a71d2a';
public const COLOR_BLUE = '#4da3ff'; // '#007bff'
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',
];
/**
* Fetch chart data
*
* @param string $chart Name of the chart
*
* @return \Illuminate\Http\JsonResponse
*/
public function chart($chart)
{
if (!preg_match('/^[a-z-]+$/', $chart)) {
return $this->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 @@
<?php
namespace App\Http\Controllers\API\V4\Reseller;
use Illuminate\Support\Facades\Auth;
class StatsController extends \App\Http\Controllers\API\V4\Admin\StatsController
{
/** @var array List of enabled charts */
protected $charts = [
'discounts',
// 'income',
+ 'payers',
'users',
'users-all',
'vouchers',
];
/**
* 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)
{
if ($addQuery) {
$user = Auth::guard()->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 @@
+<?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
index 715290e5..e955bec3 100644
--- a/src/resources/lang/en/app.php
+++ b/src/resources/lang/en/app.php
@@ -1,142 +1,143 @@
<?php
return [
/*
|--------------------------------------------------------------------------
| Application Language Lines
|--------------------------------------------------------------------------
|
| The following language lines are used in the application.
*/
'chart-created' => '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 @@
<template>
<div id="stats-container" class="container"></div>
</template>
<script>
import { Chart } from 'frappe-charts/dist/frappe-charts.esm.js'
export default {
data() {
return {
charts: {},
- chartTypes: ['users', 'users-all', 'income', 'discounts', 'vouchers']
+ chartTypes: ['users', 'users-all', 'income', 'payers', 'discounts', 'vouchers']
}
},
mounted() {
this.chartTypes.forEach(chart => this.loadChart(chart))
},
methods: {
drawChart(name, data) {
if (!data.title) {
return
}
const ch = new Chart('#chart-' + name, data)
this.charts[name] = ch
},
loadChart(name) {
const chart = $('<div>').attr({ id: 'chart-' + name }).appendTo(this.$el)
axios.get('/api/v4/stats/chart/' + name, { loader: chart })
.then(response => {
this.drawChart(name, response.data)
})
.catch(error => {
console.error(error)
chart.append($('<span>').text(this.$t('msg.loading-failed')))
})
}
}
}
</script>
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 @@
<template>
<div id="stats-container" class="container"></div>
</template>
<script>
import Stats from '../Admin/Stats'
export default {
mixins: [Stats],
data() {
return {
- chartTypes: ['users', 'users-all', 'discounts', 'vouchers']
+ chartTypes: ['users', 'users-all', 'payers', 'discounts', 'vouchers']
}
}
}
</script>
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 @@
<?php
namespace Tests\Browser\Admin;
use Tests\Browser;
use Tests\Browser\Pages\Admin\Stats;
use Tests\Browser\Pages\Dashboard;
use Tests\Browser\Pages\Home;
use Tests\TestCaseDusk;
class StatsTest extends TestCaseDusk
{
/**
* {@inheritDoc}
*/
public function setUp(): void
{
parent::setUp();
self::useAdminUrl();
}
/**
* Test Stats page
*/
public function testStats(): void
{
$this->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 @@
<?php
namespace Tests\Browser\Reseller;
use Tests\Browser;
use Tests\Browser\Pages\Admin\Stats;
use Tests\Browser\Pages\Dashboard;
use Tests\Browser\Pages\Home;
use Tests\TestCaseDusk;
class StatsTest extends TestCaseDusk
{
/**
* {@inheritDoc}
*/
public function setUp(): void
{
parent::setUp();
self::useResellerUrl();
}
/**
* Test Stats page (unauthenticated)
*/
public function testStatsUnauth(): void
{
// Test that the page requires authentication
$this->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 @@
+<?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
index 77d575c4..f7caf8c5 100644
--- a/src/tests/Feature/Controller/Admin/StatsTest.php
+++ b/src/tests/Feature/Controller/Admin/StatsTest.php
@@ -1,211 +1,252 @@
<?php
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;
use Tests\TestCase;
class StatsTest extends TestCase
{
/**
* {@inheritDoc}
*/
public function setUp(): void
{
parent::setUp();
self::useAdminUrl();
Payment::truncate();
DB::table('wallets')->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/<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]);
+ }
}

File Metadata

Mime Type
text/x-diff
Expires
Sun, Jun 8, 10:59 PM (23 h, 7 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
196719
Default Alt Text
(49 KB)

Event Timeline