Changeset View
Changeset View
Standalone View
Standalone View
src/app/Http/Controllers/API/V4/Admin/StatsController.php
<?php | <?php | ||||
namespace App\Http\Controllers\API\V4\Admin; | namespace App\Http\Controllers\API\V4\Admin; | ||||
use App\Providers\PaymentProvider; | use App\Providers\PaymentProvider; | ||||
use App\User; | use App\User; | ||||
use Carbon\Carbon; | use Carbon\Carbon; | ||||
use Illuminate\Http\Request; | use Illuminate\Http\Request; | ||||
use Illuminate\Support\Facades\Auth; | |||||
use Illuminate\Support\Facades\DB; | use Illuminate\Support\Facades\DB; | ||||
class StatsController extends \App\Http\Controllers\Controller | class StatsController extends \App\Http\Controllers\Controller | ||||
{ | { | ||||
public const COLOR_GREEN = '#48d368'; // '#28a745' | public const COLOR_GREEN = '#48d368'; // '#28a745' | ||||
public const COLOR_GREEN_DARK = '#19692c'; | public const COLOR_GREEN_DARK = '#19692c'; | ||||
public const COLOR_RED = '#e77681'; // '#dc3545' | public const COLOR_RED = '#e77681'; // '#dc3545' | ||||
public const COLOR_RED_DARK = '#a71d2a'; | public const COLOR_RED_DARK = '#a71d2a'; | ||||
public const COLOR_BLUE = '#4da3ff'; // '#007bff' | public const COLOR_BLUE = '#4da3ff'; // '#007bff' | ||||
public const COLOR_BLUE_DARK = '#0056b3'; | public const COLOR_BLUE_DARK = '#0056b3'; | ||||
public const COLOR_ORANGE = '#f1a539'; | public const COLOR_ORANGE = '#f1a539'; | ||||
/** @var array List of enabled charts */ | |||||
protected $charts = [ | |||||
'discounts', | |||||
'income', | |||||
'users', | |||||
'users-all', | |||||
]; | |||||
/** | /** | ||||
* Fetch chart data | * Fetch chart data | ||||
* | * | ||||
* @param string $chart Name of the chart | * @param string $chart Name of the chart | ||||
* | * | ||||
* @return \Illuminate\Http\JsonResponse | * @return \Illuminate\Http\JsonResponse | ||||
*/ | */ | ||||
public function chart($chart) | public function chart($chart) | ||||
{ | { | ||||
if (!preg_match('/^[a-z-]+$/', $chart)) { | if (!preg_match('/^[a-z-]+$/', $chart)) { | ||||
return $this->errorResponse(404); | return $this->errorResponse(404); | ||||
} | } | ||||
$method = 'chart' . implode('', array_map('ucfirst', explode('-', $chart))); | $method = 'chart' . implode('', array_map('ucfirst', explode('-', $chart))); | ||||
if (!method_exists($this, $method)) { | if (!in_array($chart, $this->charts) || !method_exists($this, $method)) { | ||||
return $this->errorResponse(404); | return $this->errorResponse(404); | ||||
} | } | ||||
$result = $this->{$method}(); | $result = $this->{$method}(); | ||||
return response()->json($result); | return response()->json($result); | ||||
} | } | ||||
/** | /** | ||||
* Get discounts chart | * Get discounts chart | ||||
*/ | */ | ||||
protected function chartDiscounts(): array | protected function chartDiscounts(): array | ||||
{ | { | ||||
$discounts = DB::table('wallets') | $discounts = DB::table('wallets') | ||||
->selectRaw("discount, count(discount_id) as cnt") | ->selectRaw("discount, count(discount_id) as cnt") | ||||
->join('discounts', 'discounts.id', '=', 'wallets.discount_id') | ->join('discounts', 'discounts.id', '=', 'wallets.discount_id') | ||||
->join('users', 'users.id', '=', 'wallets.user_id') | ->join('users', 'users.id', '=', 'wallets.user_id') | ||||
->where('discount', '>', 0) | ->where('discount', '>', 0) | ||||
->whereNull('users.deleted_at') | ->whereNull('users.deleted_at') | ||||
->groupBy('discounts.discount') | ->groupBy('discounts.discount'); | ||||
->pluck('cnt', 'discount') | |||||
->all(); | $addTenantScope = function ($builder, $tenantId) { | ||||
return $builder->where('users.tenant_id', $tenantId); | |||||
}; | |||||
$discounts = $this->applyTenantScope($discounts, $addTenantScope) | |||||
->pluck('cnt', 'discount')->all(); | |||||
$labels = array_keys($discounts); | $labels = array_keys($discounts); | ||||
$discounts = array_values($discounts); | $discounts = array_values($discounts); | ||||
// $labels = [10, 25, 30, 100]; | // $labels = [10, 25, 30, 100]; | ||||
// $discounts = [100, 120, 30, 50]; | // $discounts = [100, 120, 30, 50]; | ||||
$labels = array_map(function ($item) { | $labels = array_map(function ($item) { | ||||
▲ Show 20 Lines • Show All 47 Lines • ▼ Show 20 Lines | protected function chartIncome(): array | ||||
$labels = array_reverse($labels); | $labels = array_reverse($labels); | ||||
$start->startOfWeek(Carbon::MONDAY); | $start->startOfWeek(Carbon::MONDAY); | ||||
$payments = DB::table('payments') | $payments = DB::table('payments') | ||||
->selectRaw("date_format(updated_at, '%Y-%v') as period, sum(amount) as amount") | ->selectRaw("date_format(updated_at, '%Y-%v') as period, sum(amount) as amount") | ||||
->where('updated_at', '>=', $start->toDateString()) | ->where('updated_at', '>=', $start->toDateString()) | ||||
->where('status', PaymentProvider::STATUS_PAID) | ->where('status', PaymentProvider::STATUS_PAID) | ||||
->whereIn('type', [PaymentProvider::TYPE_ONEOFF, PaymentProvider::TYPE_RECURRING]) | ->whereIn('type', [PaymentProvider::TYPE_ONEOFF, PaymentProvider::TYPE_RECURRING]) | ||||
->groupByRaw('1') | ->groupByRaw('1'); | ||||
$addTenantScope = function ($builder, $tenantId) { | |||||
$where = '`wallet_id` IN (' | |||||
. 'select `id` from `wallets` ' | |||||
. 'join `users` on (`wallets`.`user_id` = `users`.`id`) ' | |||||
. 'where `payments`.`wallet_id` = `wallets`.`id` ' | |||||
. 'and `users`.`tenant_id` = ' . intval($tenantId) | |||||
. ')'; | |||||
return $builder->whereRaw($where); | |||||
}; | |||||
$payments = $this->applyTenantScope($payments, $addTenantScope) | |||||
->pluck('amount', 'period') | ->pluck('amount', 'period') | ||||
->map(function ($amount) { | ->map(function ($amount) { | ||||
return $amount / 100; | return $amount / 100; | ||||
}); | }); | ||||
// TODO: exclude refunds/chargebacks | // TODO: exclude refunds/chargebacks | ||||
$empty = array_fill_keys($labels, 0); | $empty = array_fill_keys($labels, 0); | ||||
▲ Show 20 Lines • Show All 49 Lines • ▼ Show 20 Lines | protected function chartUsers(): array | ||||
} | } | ||||
$labels = array_reverse($labels); | $labels = array_reverse($labels); | ||||
$start->startOfWeek(Carbon::MONDAY); | $start->startOfWeek(Carbon::MONDAY); | ||||
$created = DB::table('users') | $created = DB::table('users') | ||||
->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") | ->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") | ||||
->where('created_at', '>=', $start->toDateString()) | ->where('created_at', '>=', $start->toDateString()) | ||||
->groupByRaw('1') | ->groupByRaw('1'); | ||||
->get(); | |||||
$deleted = DB::table('users') | $deleted = DB::table('users') | ||||
->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") | ->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") | ||||
->where('deleted_at', '>=', $start->toDateString()) | ->where('deleted_at', '>=', $start->toDateString()) | ||||
->groupByRaw('1') | ->groupByRaw('1'); | ||||
->get(); | |||||
$created = $this->applyTenantScope($created)->get(); | |||||
$deleted = $this->applyTenantScope($deleted)->get(); | |||||
$empty = array_fill_keys($labels, 0); | $empty = array_fill_keys($labels, 0); | ||||
$created = array_values(array_merge($empty, $created->pluck('cnt', 'period')->all())); | $created = array_values(array_merge($empty, $created->pluck('cnt', 'period')->all())); | ||||
$deleted = array_values(array_merge($empty, $deleted->pluck('cnt', 'period')->all())); | $deleted = array_values(array_merge($empty, $deleted->pluck('cnt', 'period')->all())); | ||||
// $created = [5, 2, 4, 2, 0, 5, 2, 4]; | // $created = [5, 2, 4, 2, 0, 5, 2, 4]; | ||||
// $deleted = [1, 2, 3, 1, 2, 1, 2, 3]; | // $deleted = [1, 2, 3, 1, 2, 1, 2, 3]; | ||||
▲ Show 20 Lines • Show All 51 Lines • ▼ Show 20 Lines | protected function chartUsersAll(): array | ||||
} | } | ||||
$labels = array_reverse($labels); | $labels = array_reverse($labels); | ||||
$start->startOfWeek(Carbon::MONDAY); | $start->startOfWeek(Carbon::MONDAY); | ||||
$created = DB::table('users') | $created = DB::table('users') | ||||
->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") | ->selectRaw("date_format(created_at, '%Y-%v') as period, count(*) as cnt") | ||||
->where('created_at', '>=', $start->toDateString()) | ->where('created_at', '>=', $start->toDateString()) | ||||
->groupByRaw('1') | ->groupByRaw('1'); | ||||
->get(); | |||||
$deleted = DB::table('users') | $deleted = DB::table('users') | ||||
->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") | ->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") | ||||
->where('deleted_at', '>=', $start->toDateString()) | ->where('deleted_at', '>=', $start->toDateString()) | ||||
->groupByRaw('1') | ->groupByRaw('1'); | ||||
->get(); | |||||
$count = DB::table('users')->whereNull('deleted_at')->count(); | $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); | $empty = array_fill_keys($labels, 0); | ||||
$created = array_merge($empty, $created->pluck('cnt', 'period')->all()); | $created = array_merge($empty, $created->pluck('cnt', 'period')->all()); | ||||
$deleted = array_merge($empty, $deleted->pluck('cnt', 'period')->all()); | $deleted = array_merge($empty, $deleted->pluck('cnt', 'period')->all()); | ||||
$all = []; | $all = []; | ||||
foreach (array_reverse($labels) as $label) { | foreach (array_reverse($labels) as $label) { | ||||
$all[] = $count; | $all[] = $count; | ||||
Show All 27 Lines | protected function chartUsersAll(): array | ||||
[ | [ | ||||
// 'name' => 'Existing', | // 'name' => 'Existing', | ||||
'values' => $all | 'values' => $all | ||||
] | ] | ||||
] | ] | ||||
] | ] | ||||
]; | ]; | ||||
} | } | ||||
/** | |||||
* 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) | |||||
{ | |||||
$user = Auth::guard()->user(); | |||||
if ($user->role == 'reseller') { | |||||
if ($addQuery) { | |||||
$query = $addQuery($query, \config('app.tenant_id')); | |||||
} else { | |||||
$query = $query->withEnvTenant(); | |||||
} | |||||
} | |||||
// TODO: Tenant selector for admins | |||||
return $query; | |||||
} | |||||
} | } |