Statistics.php — Independent Analytics – WordPress Analytics Plugin 2.1.4

independent-analytics / IAWP / Statistics / Statistics.php

independent-analytics / IAWP / Statistics Last commit date

Intervals 3 years ago Campaign_Statistics.php 2 years ago City_Statistics.php 2 years ago Country_Statistics.php 2 years ago Device_Browser_Statistics.php 2 years ago Device_OS_Statistics.php 2 years ago Device_Type_Statistics.php 2 years ago Page_Statistics.php 2 years ago Referrer_Statistics.php 2 years ago Statistic.php 3 years ago Statistics.php 2 years ago

Statistics.php

245 lines

1	<?php
2
3	namespace IAWP_SCOPED\IAWP\Statistics;
4
5	use DatePeriod;
6	use DateTime;
7	use IAWP_SCOPED\IAWP\Date_Range\Date_Range;
8	use IAWP_SCOPED\IAWP\Illuminate_Builder;
9	use IAWP_SCOPED\IAWP\Query;
10	use IAWP_SCOPED\IAWP\Rows\Rows;
11	use IAWP_SCOPED\IAWP\Statistics\Intervals\Daily;
12	use IAWP_SCOPED\IAWP\Statistics\Intervals\Interval;
13	use IAWP_SCOPED\Illuminate\Database\Query\Builder;
14	use IAWP_SCOPED\Illuminate\Database\Query\JoinClause;
15	use IAWP_SCOPED\Proper\Timezone;
16	abstract class Statistics
17	{
18	protected $date_range;
19	protected $rows;
20	protected $chart_interval;
21	private $views;
22	private $visitors;
23	private $sessions;
24	private $average_session_duration;
25	private $views_per_session;
26	private $bounce_rate;
27	private $woocommerce_orders;
28	private $woocommerce_net_sales;
29	private $statistics_by_day;
30	private $statistics;
31	private $previous_period_statistics;
32	// The biggest flaw here is that it requires two queries for the stats (current and previous) when
33	// that could be one. I think it would also be possible to reuse the rows query and not limit
34	// by 50 and just SUM() up all the stats columns for the quick stats. Maybe that would be faster
35	// even if two queries were still used. Needs testing.
36	public function __construct(Date_Range $date_range, ?Rows $rows = null, ?Interval $chart_interval = null)
37	{
38	$this->date_range = $date_range;
39	$this->rows = $rows;
40	$this->chart_interval = $chart_interval ?? new Daily();
41	$this->statistics_by_day = $this->query($this->date_range, \true);
42	$this->statistics = $this->query($this->date_range, \false);
43	$this->previous_period_statistics = $this->query($this->date_range->previous_period(), \false);
44	$this->views = $this->get_statistic('views');
45	$this->visitors = $this->get_statistic('visitors');
46	$this->sessions = $this->get_statistic('sessions');
47	$this->woocommerce_orders = $this->get_statistic('wc_orders');
48	$this->woocommerce_net_sales = $this->get_statistic('wc_net_sales');
49	$this->average_session_duration = $this->get_statistic('average_session_duration');
50	$this->bounce_rate = new Statistic($this->calculate_percent($this->statistics->bounces, $this->statistics->sessions), $this->calculate_percent($this->previous_period_statistics->bounces, $this->previous_period_statistics->sessions));
51	$this->views_per_session = new Statistic($this->divide($this->statistics->total_views, $this->statistics->sessions, 2), $this->divide($this->previous_period_statistics->total_views, $this->previous_period_statistics->sessions, 2));
52	}
53	public function views() : Statistic
54	{
55	return $this->views;
56	}
57	public function visitors() : Statistic
58	{
59	return $this->visitors;
60	}
61	public function sessions() : Statistic
62	{
63	return $this->sessions;
64	}
65	public function average_session_duration() : Statistic
66	{
67	return $this->average_session_duration;
68	}
69	public function woocommerce_orders() : Statistic
70	{
71	return $this->woocommerce_orders;
72	}
73	public function woocommerce_net_sales() : Statistic
74	{
75	return $this->woocommerce_net_sales;
76	}
77	public function bounce_rate() : Statistic
78	{
79	return $this->bounce_rate;
80	}
81	public function view_per_session() : Statistic
82	{
83	return $this->views_per_session;
84	}
85	public function chart_interval() : Interval
86	{
87	return $this->chart_interval;
88	}
89	/**
90	* I'm sure there's more we could do here. If you get a result back where there isn't a full
91	* page of results or where you're not paginating, then you can just count up the rows...
92	*
93	* @return int\|null
94	*/
95	public function total_table_rows() : ?int
96	{
97	$sessions_table = Query::get_table_name(Query::SESSIONS);
98	$views_table = Query::get_table_name(Query::VIEWS);
99	$column = $this->total_table_rows_column() ?? $this->required_column();
100	$query = Illuminate_Builder::get_builder()->selectRaw("COUNT(DISTINCT {$column}) AS total_table_rows")->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) {
101	$join->on('sessions.session_id', '=', 'views.session_id');
102	})->when(!\is_null($this->rows), function (Builder $query) {
103	$this->rows->attach_filters($query);
104	})->whereBetween('sessions.created_at', [$this->date_range->iso_start(), $this->date_range->iso_end()])->whereBetween('views.viewed_at', [$this->date_range->iso_start(), $this->date_range->iso_end()]);
105	return $query->value('total_table_rows');
106	}
107	/**
108	* Define which id column to use to count up the total table rows. This is only required
109	* for classes that don't have a required column and don't override required_column
110	*
111	* @return string\|null
112	*/
113	protected function total_table_rows_column() : ?string
114	{
115	return null;
116	}
117	/**
118	* Statistics can require that a column exists in order to be included. As an example, geos
119	* requires visitors.country_code and campaigns requires sessions.campaign_id
120	*
121	* @return string\|null
122	*/
123	protected function required_column() : ?string
124	{
125	return null;
126	}
127	private function get_statistic(string $name) : Statistic
128	{
129	return new Statistic($this->statistics->{$name}, $this->previous_period_statistics->{$name}, $this->fill_in_partial_day_range($this->statistics_by_day, $name));
130	}
131	private function query(Date_Range $range, bool $as_daily_statistics)
132	{
133	$utc_offset = Timezone::utc_offset();
134	$site_offset = Timezone::site_offset();
135	$sessions_table = Query::get_table_name(Query::SESSIONS);
136	$views_table = Query::get_table_name(Query::VIEWS);
137	$wc_orders_table = Query::get_table_name(Query::WC_ORDERS);
138	$session_statistics = Illuminate_Builder::get_builder();
139	$session_statistics->select('sessions.session_id')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT wc_orders.order_id) AS orders')->selectRaw('IFNULL(CAST(SUM(wc_orders.total) AS DECIMAL(10, 2)), 0) AS gross_sales')->selectRaw('IFNULL(CAST(SUM(wc_orders.total_refunded) AS DECIMAL(10, 2)), 0) AS total_refunded')->selectRaw('IFNULL(CAST(SUM(wc_orders.total_refunds) AS UNSIGNED), 0) AS total_refunds')->selectRaw('IFNULL(CAST(SUM(wc_orders.total - wc_orders.total_refunded) AS DECIMAL(10, 2)), 0) AS net_sales')->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) {
140	$join->on('sessions.session_id', '=', 'views.session_id');
141	})->leftJoin("{$wc_orders_table} AS wc_orders", function (JoinClause $join) {
142	$join->on('views.id', '=', 'wc_orders.view_id')->whereIn('wc_orders.status', ['wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded']);
143	})->when(!\is_null($this->rows), function (Builder $query) {
144	$this->rows->attach_filters($query);
145	})->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->whereBetween('views.viewed_at', [$range->iso_start(), $range->iso_end()])->groupBy('sessions.session_id')->when(!\is_null($this->required_column()), function (Builder $query) {
146	$query->whereNotNull($this->required_column());
147	});
148	$statistics = Illuminate_Builder::get_builder();
149	$statistics->selectRaw('IFNULL(CAST(SUM(sessions.total_views) AS UNSIGNED), 0) AS total_views')->selectRaw('IFNULL(CAST(SUM(session_statistics.views) AS UNSIGNED), 0) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('IFNULL(CAST(AVG(TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at)) AS UNSIGNED), 0) AS average_session_duration')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('IFNULL(CAST(SUM(session_statistics.orders) AS UNSIGNED), 0) AS wc_orders')->selectRaw('IFNULL(CAST(SUM(session_statistics.gross_sales) AS DECIMAL(10, 2)), 0) AS wc_gross_sales')->selectRaw('IFNULL(CAST(SUM(session_statistics.total_refunds) AS UNSIGNED), 0) AS wc_refunds')->selectRaw('IFNULL(CAST(SUM(session_statistics.total_refunded) AS DECIMAL(10, 2)), 0) AS wc_refunded_amount')->selectRaw('IFNULL(CAST(SUM(session_statistics.net_sales) AS DECIMAL(10, 2)), 0) AS wc_net_sales')->from("{$sessions_table} AS sessions")->joinSub($session_statistics, 'session_statistics', function (JoinClause $join) {
150	$join->on('sessions.session_id', '=', 'session_statistics.session_id');
151	})->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->when($as_daily_statistics, function (Builder $query) use($utc_offset, $site_offset) {
152	if ($this->chart_interval->id() === 'daily') {
153	$query->selectRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) AS date");
154	} elseif ($this->chart_interval->id() === 'monthly') {
155	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-01 00:00:00') AS date");
156	} elseif ($this->chart_interval->id() === 'weekly') {
157	$day_of_week = \IAWP_SCOPED\iawp()->get_option('iawp_dow', 0) + 1;
158	$query->selectRaw("\n IF (\n DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} < 0,\n DATE_FORMAT(SUBDATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} + 7), '%Y-%m-%d 00:00:00'),\n DATE_FORMAT(SUBDATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week}), '%Y-%m-%d 00:00:00')\n ) AS date\n ");
159	} else {
160	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-%d %H:00:00') AS date");
161	}
162	$query->groupByRaw("date");
163	});
164	$results = \array_map(function (object $statistic) : object {
165	return $this->parse_statistic($statistic);
166	}, $statistics->get()->all());
167	if (!$as_daily_statistics) {
168	return $results[0];
169	}
170	return $results;
171	}
172	private function parse_statistic(object $statistic) : object
173	{
174	$statistic->wc_gross_sales = \floatval($statistic->wc_gross_sales);
175	$statistic->wc_refunded_amount = \floatval($statistic->wc_refunded_amount);
176	$statistic->wc_net_sales = \floatval($statistic->wc_net_sales);
177	return $statistic;
178	}
179	private function calculate_percent(float $top, float $bottom) : float
180	{
181	if ($bottom === 0.0 && $top > 0) {
182	return 100;
183	} elseif ($bottom === 0.0) {
184	return 0;
185	}
186	return \round($top / $bottom * 100, 0);
187	}
188	private function divide(float $top, float $bottom, int $precision = 0) : float
189	{
190	if ($bottom === 0.0 && $top > 0) {
191	return 100;
192	} elseif ($bottom === 0.0) {
193	return 0;
194	}
195	return \round($top / $bottom, $precision);
196	}
197	/**
198	* @param array $partial_day_range
199	* @param string $field
200	*
201	* @return array
202	*/
203	private function fill_in_partial_day_range(array $partial_day_range, string $field) : array
204	{
205	$original_start = (clone $this->date_range->start())->setTimezone(Timezone::site_timezone());
206	$start = $this->chart_interval->calculate_start_of_interval_for($original_start);
207	$original_end = (clone $this->date_range->end())->setTimezone(Timezone::site_timezone());
208	$end = $this->chart_interval->calculate_start_of_interval_for($original_end);
209	$end->add(new \DateInterval('PT1S'));
210	$date_range = new DatePeriod($start, $this->chart_interval->date_interval(), $end);
211	$filled_in_data = [];
212	foreach ($date_range as $date) {
213	$stat = $this->get_statistic_for_date($partial_day_range, $date, $field);
214	$filled_in_data[] = [$date, $stat];
215	}
216	return $filled_in_data;
217	}
218	/**
219	* @param array $partial_day_range
220	* @param DateTime $datetime_to_match
221	* @param string $field
222	*
223	* @return int Defaults to 0
224	*/
225	private function get_statistic_for_date(array $partial_day_range, DateTime $datetime_to_match, string $field) : int
226	{
227	$user_timezone = Timezone::site_timezone();
228	$default_value = 0;
229	foreach ($partial_day_range as $day) {
230	$date = $day->date;
231	$stat = $day->{$field};
232	try {
233	$datetime = new DateTime($date, $user_timezone);
234	} catch (\Throwable $e) {
235	return $default_value;
236	}
237	// Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time
238	if ($datetime == $datetime_to_match) {
239	return \intval($stat);
240	}
241	}
242	return $default_value;
243	}
244	}
245