1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
<?php
namespace App\Helpers;
use App\Models\Click;
use App\Models\Link;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
class StatsHelper {
function __construct($link_id, $left_bound, $right_bound) {
$this->link_id = $link_id;
$this->left_bound_parsed = Carbon::parse($left_bound);
$this->right_bound_parsed = Carbon::parse($right_bound);
if (!$this->left_bound_parsed->lte($this->right_bound_parsed)) {
// If left bound is not less than or equal to right bound
throw new \Exception('Invalid bounds.');
}
$days_diff = $this->left_bound_parsed->diffInDays($this->right_bound_parsed);
$max_days_diff = env('_ANALYTICS_MAX_DAYS_DIFF') ?: 365;
if ($days_diff > $max_days_diff) {
throw new \Exception('Bounds too broad.');
}
}
public function getBaseRows() {
/**
* Fetches base rows given left date bound, right date bound, and link id
*
* @param integer $link_id
* @param string $left_bound
* @param string $right_bound
*
* @return DB rows
*/
return DB::table('clicks')
->where('link_id', $this->link_id)
->where('created_at', '>=', $this->left_bound_parsed)
->where('created_at', '<=', $this->right_bound_parsed);
}
public function getDayStats() {
// Return stats by day from the last 30 days
// date => x
// clicks => y
$stats = $this->getBaseRows()
->select(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d') AS x, count(*) AS y"))
->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))
->orderBy('x', 'asc')
->get();
return $stats;
}
public function getCountryStats() {
$stats = $this->getBaseRows()
->select(DB::raw("country AS label, count(*) AS clicks"))
->groupBy('country')
->orderBy('clicks', 'desc')
->get();
return $stats;
}
public function getRefererStats() {
$stats = $this->getBaseRows()
->select(DB::raw("COALESCE(referer_host, 'Direct') as label, count(*) as clicks"))
->groupBy('referer_host')
->orderBy('clicks', 'desc')
->get();
return $stats;
}
}
|
