Device_Browsers.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

independent-analytics / IAWP / Rows / Device_Browsers.php

independent-analytics / IAWP / Rows Last commit date

Campaign_Landing_Pages.php 5 months ago Campaign_UTM_Campaigns.php 5 months ago Campaign_UTM_Mediums.php 5 months ago Campaign_UTM_Sources.php 5 months ago Campaigns.php 5 months ago Cities.php 5 months ago Countries.php 5 months ago Device_Browsers.php 5 months ago Device_OSS.php 5 months ago Device_Types.php 5 months ago Filter.php 5 months ago Forms.php 5 months ago Journeys.php 5 months ago Link_Patterns.php 5 months ago Links.php 5 months ago Pages.php 5 months ago Referrer_Types.php 5 months ago Referrers.php 5 months ago Rows.php 5 months ago

Device_Browsers.php

71 lines

1	<?php
2
3	namespace IAWP\Rows;
4
5	use IAWP\Form_Submissions\Form;
6	use IAWP\Illuminate_Builder;
7	use IAWP\Models\Device;
8	use IAWP\Query;
9	use IAWP\Query_Taps;
10	use IAWPSCOPED\Illuminate\Database\Query\Builder;
11	use IAWPSCOPED\Illuminate\Database\Query\JoinClause;
12	/** @internal */
13	class Device_Browsers extends \IAWP\Rows\Rows
14	{
15	public function attach_filters(Builder $query) : void
16	{
17	$query->joinSub($this->get_filter_query(), 'device_browser_rows', function (JoinClause $join) {
18	$join->on('device_browser_rows.device_browser_id', '=', 'sessions.device_browser_id');
19	});
20	}
21	protected function fetch_rows() : array
22	{
23	$rows = $this->query()->get()->all();
24	return \array_map(function ($row) {
25	return new Device($row);
26	}, $rows);
27	}
28	protected function sort_tie_breaker_column() : string
29	{
30	return 'browser';
31	}
32	protected function query(?bool $skip_pagination = \false) : Builder
33	{
34	if ($skip_pagination) {
35	$this->number_of_rows = null;
36	}
37	$sessions_table = Query::get_table_name(Query::SESSIONS);
38	$device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS);
39	$session_statistics = $this->session_statistics_query()->whereNotNull('sessions.device_browser_id');
40	$device_browsers_query = Illuminate_Builder::new();
41	$device_browsers_query->select('device_browsers.device_browser_id', 'device_browsers.device_browser AS browser')->selectRaw('IFNULL(CAST(SUM(sessions.views) AS SIGNED), 0) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('ROUND(AVG( TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at))) AS average_session_duration')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('SUM(sessions.clicks) AS clicks')->selectRaw('SUM(sessions.wc_orders) AS wc_orders')->selectRaw('SUM(sessions.wc_gross_sales) AS wc_gross_sales')->selectRaw('SUM(sessions.wc_refunded_amount) AS wc_refunded_amount')->selectRaw('SUM(sessions.wc_refunds) AS wc_refunds')->selectRaw('SUM(sessions.form_submissions) AS form_submissions')->tap(function (Builder $query) {
42	foreach (Form::get_forms() as $form) {
43	$query->selectRaw("SUM(sessions.{$form->submissions_column()}) AS {$form->submissions_column()}");
44	}
45	})->fromSub($session_statistics, 'sessions')->join($device_browsers_query->raw($device_browsers_table . ' AS device_browsers'), function (JoinClause $join) {
46	$join->on('sessions.device_browser_id', '=', 'device_browsers.device_browser_id');
47	})->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) {
48	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
49	})->when(\is_int($this->solo_record_id), function (Builder $query) {
50	$query->where('device_browsers.device_browser_id', '=', $this->solo_record_id);
51	})->groupBy('device_browsers.device_browser_id')->having('views', '>', 0)->tap(fn(Builder $query) => $this->apply_record_filters($query))->when($this->can_order_and_limit_at_record_level(), function (Builder $query) {
52	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
53	});
54	$previous_period_query = Illuminate_Builder::new();
55	$previous_period_query->select(['sessions.device_browser_id'])->selectRaw('SUM(sessions.total_views) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from($sessions_table, 'sessions')->tap(Query_Taps::tap_related_to_examined_record_for_previous_period($this->examiner_config, ['sessions']))->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('sessions.device_browser_id');
56	$outer_query = Illuminate_Builder::new();
57	$outer_query->selectRaw('device_browsers.')->selectRaw('IF(sessions = 0, 0, views / sessions) AS views_per_session')->selectRaw('IFNULL((views - previous_period_views) / previous_period_views 100, 0) AS views_growth')->selectRaw('IFNULL((visitors - previous_period_visitors) / previous_period_visitors * 100, 0) AS visitors_growth')->selectRaw('IFNULL(bounces / sessions * 100, 0) AS bounce_rate')->selectRaw('ROUND(CAST(wc_gross_sales - wc_refunded_amount AS SIGNED)) AS wc_net_sales')->selectRaw('IF(visitors = 0, 0, (wc_orders / visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(visitors = 0, 0, (wc_gross_sales - wc_refunded_amount) / visitors) AS wc_earnings_per_visitor')->selectRaw('IF(wc_orders = 0, 0, ROUND(CAST(wc_gross_sales / wc_orders AS SIGNED))) AS wc_average_order_volume')->selectRaw('IF(visitors = 0, 0, (form_submissions / visitors) * 100) AS form_conversion_rate')->tap(function (Builder $query) {
58	foreach (Form::get_forms() as $form) {
59	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
60	}
61	})->fromSub($device_browsers_query, 'device_browsers')->leftJoinSub($previous_period_query, 'previous_period_stats', 'device_browsers.device_browser_id', '=', 'previous_period_stats.device_browser_id')->tap(fn(Builder $query) => $this->apply_aggregate_filters($query))->when(!$this->can_order_and_limit_at_record_level() && !($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()), function (Builder $query) {
62	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
63	});
64	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
65	$og_outer_query = $outer_query;
66	$outer_query = Illuminate_Builder::new()->select('*')->fromSub($og_outer_query, 'records')->tap(fn(Builder $query) => $this->apply_or_filters($query))->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
67	}
68	return $outer_query;
69	}
70	}
71