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 @@ -22,6 +22,7 @@ protected $charts = [ 'discounts', 'income', + 'payers', 'users', 'users-all', 'vouchers', @@ -332,6 +333,125 @@ ]; } + /** + * Get payers chart + */ + protected function chartPayers(): 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); + + // A subquery to get the first payment date - one record per wallet + $payments = DB::table('payments') + ->selectRaw("wallet_id, min(created_at) as first_payment") + ->where('status', PaymentProvider::STATUS_PAID) + ->groupBy('wallet_id'); + + // 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'); + + // Users before the start of the chart period (initial state) + // Includes users deleted later + $initial = 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'); + }) + ->where('first_payment', '<', $start->toDateString()) + ->where('created_at', '<', $start->toDateString()) + ->where(function($query) use ($start) { + $query->where('deleted_at', '>=', $start->toDateString()) + ->orWhereNull('deleted_at'); + }); + + // Users with a new payment (per week) since the initial state + // Includes users created since the initial state even if the payment was earlier + $created = DB::table('users') + ->selectRaw("date_format(first_payment, '%Y-%v') as period, count(*) as cnt") + ->joinSub($wallets, 'wallets', function ($join) { + $join->on('users.id', '=', 'wallets.user_id'); + }) + ->joinSub($payments, 'payments', function ($join) { + $join->on('wallets.id', '=', 'payments.wallet_id'); + }) + ->where('first_payment', '>=', $start->toDateString()) + ->orWhere('created_at', '>=', $start->toDateString()) + ->groupByRaw('1'); + + // Users deleted (per week) since the initial state (only these having payments) + $deleted = DB::table('users') + ->selectRaw("date_format(deleted_at, '%Y-%v') as period, count(*) as cnt") + ->joinSub($wallets, 'wallets', function ($join) { + $join->on('users.id', '=', 'wallets.user_id'); + }) + ->joinSub($payments, 'payments', function ($join) { + $join->on('wallets.id', '=', 'payments.wallet_id'); + }) + ->where('deleted_at', '>=', $start->toDateString()) + ->groupByRaw('1'); + + $count = $this->applyTenantScope($initial)->count(); + $created = $this->applyTenantScope($created)->get(); + $deleted = $this->applyTenantScope($deleted)->get(); + + $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 ($labels as $label) { + $count += $created[$label] - $deleted[$label]; + $all[] = $count; + } + + // $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-payers'), + '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 */ 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', 'discounts', 'vouchers', 'payers'] } }, 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', 'discounts', 'vouchers', 'payers'] } } }