Pages.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

independent-analytics / IAWP / Rows / Pages.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

Pages.php

110 lines

1	<?php
2
3	namespace IAWP\Rows;
4
5	use IAWP\Database;
6	use IAWP\Form_Submissions\Form;
7	use IAWP\Illuminate_Builder;
8	use IAWP\Models\Page;
9	use IAWP\Query_Taps;
10	use IAWP\Tables;
11	use IAWPSCOPED\Illuminate\Database\Query\Builder;
12	use IAWPSCOPED\Illuminate\Database\Query\JoinClause;
13	/** @internal */
14	class Pages extends \IAWP\Rows\Rows
15	{
16	private static $has_wp_comments_table = null;
17	public function attach_filters(Builder $query) : void
18	{
19	$query->joinSub($this->get_filter_query(), 'page_rows', function (JoinClause $join) {
20	$join->on('page_rows.id', '=', 'views.resource_id');
21	});
22	}
23	protected function fetch_rows() : array
24	{
25	$rows = $this->query()->get()->all();
26	return \array_map(function (object $row) {
27	return Page::from_row($row);
28	}, $rows);
29	}
30	protected function sort_tie_breaker_column() : string
31	{
32	return 'cached_title';
33	}
34	protected function query(?bool $skip_pagination = \false) : Builder
35	{
36	if ($skip_pagination) {
37	$this->number_of_rows = null;
38	}
39	$database_sort_columns = ['title' => 'cached_title', 'url' => 'cached_url', 'author' => 'cached_author', 'type' => 'cached_type_label', 'date' => 'cached_date', 'category' => 'cached_category'];
40	$sort_column = $this->sort_configuration->column();
41	foreach ($database_sort_columns as $key => $value) {
42	if ($sort_column === $key) {
43	$sort_column = $value;
44	}
45	}
46	$orders_subquery = Illuminate_Builder::new()->selectRaw('orders.view_id')->selectRaw('COUNT(*) as order_count')->selectRaw('IFNULL(SUM(orders.total), 0) as total')->selectRaw('IFNULL(SUM(orders.total_refunded), 0) as total_refunded')->selectRaw('IFNULL(SUM(orders.total_refunds), 0) as total_refunds')->from(Tables::orders() . " AS orders")->where('orders.is_included_in_analytics', '=', \true)->whereBetween('orders.created_at', $this->get_current_period_iso_range())->groupBy('orders.view_id');
47	$form_submissions_subquery = Illuminate_Builder::new()->select(['submissions.view_id'])->selectRaw('COUNT(*) as form_submissions')->tap(function (Builder $query) {
48	foreach (Form::get_forms() as $form) {
49	$query->selectRaw('SUM(submissions.form_id = ?) as ' . $form->submissions_column(), [$form->id()]);
50	}
51	})->from(Tables::form_submissions(), 'submissions')->whereBetween('submissions.created_at', $this->get_current_period_iso_range())->groupBy('submissions.view_id');
52	$clicks_subquery = Illuminate_Builder::new()->select('clicks.view_id')->selectRaw('COUNT(*) AS clicks')->from(Tables::clicks(), 'clicks')->whereBetween('clicks.created_at', $this->get_current_period_iso_range())->groupBy('clicks.view_id');
53	$pages_query = Illuminate_Builder::new()->select('resources.')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT IF(initial_view.resource_id = resources.id, sessions.visitor_id, NULL)) AS landing_page_visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('AVG(TIMESTAMPDIFF(SECOND, views.viewed_at, views.next_viewed_at)) AS average_view_duration')->selectRaw('COUNT(DISTINCT IF(resources.id = initial_view.resource_id, sessions.session_id, NULL)) AS entrances')->selectRaw('COUNT(DISTINCT IF((resources.id = final_view.resource_id OR (resources.id = initial_view.resource_id AND sessions.final_view_id IS NULL)), sessions.session_id, NULL)) AS exits')->selectRaw('COUNT() AS views')->selectRaw('SUM(IFNULL(clicks.clicks, 0)) AS clicks')->selectRaw('SUM(IFNULL(order_stats.order_count, 0)) AS wc_orders')->selectRaw('SUM(IFNULL(order_stats.total, 0)) AS wc_gross_sales')->selectRaw('SUM(IFNULL(order_stats.total_refunded, 0)) AS wc_refunded_amount')->selectRaw('SUM(IFNULL(order_stats.total_refunds, 0)) AS wc_refunds')->selectRaw('SUM(IFNULL(form_submissions.form_submissions, 0)) AS form_submissions')->tap(function (Builder $query) {
54	foreach (Form::get_forms() as $form) {
55	$query->selectRaw("SUM(IFNULL(form_submissions.{$form->submissions_column()}, 0)) AS {$form->submissions_column()}");
56	}
57	})->from(Tables::views(), 'views')->leftJoin(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->leftJoin(Tables::resources() . ' AS resources', 'views.resource_id', '=', 'resources.id')->leftJoin(Tables::views() . ' AS initial_view', 'sessions.initial_view_id', '=', 'initial_view.id')->leftJoin(Tables::views() . ' AS final_view', 'sessions.final_view_id', '=', 'final_view.id')->leftJoinSub($orders_subquery, 'order_stats', 'views.id', '=', 'order_stats.view_id')->leftJoinSub($form_submissions_subquery, 'form_submissions', 'views.id', '=', 'form_submissions.view_id')->leftJoinSub($clicks_subquery, 'clicks', 'views.id', '=', 'clicks.view_id')->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->tap(Query_Taps::tap_authored_content_check(\false))->when($this->has_wp_comments_table(), function (Builder $query) {
58	$query->selectRaw('IFNULL(comments.comments, 0) AS comments');
59	$query->leftJoinSub($this->get_comments_query(), 'comments', 'comments.resource_id', '=', 'resources.id');
60	}, function (Builder $query) {
61	$query->selectRaw('0 AS comments');
62	})->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) {
63	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
64	})->tap(fn(Builder $query) => $this->remove_non_singulars_when_filtering_by_comments($query))->when(\is_int($this->solo_record_id), function (Builder $query) {
65	$query->where('resources.id', '=', $this->solo_record_id);
66	})->groupBy('resources.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) use($sort_column) {
67	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column));
68	});
69	$previous_period_query = Illuminate_Builder::new()->select(['views.resource_id'])->selectRaw('COUNT(*) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from(Tables::views(), 'views')->leftJoin(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->tap(Query_Taps::tap_related_to_examined_record_for_previous_period($this->examiner_config, ['views', 'sessions']))->whereBetween('views.viewed_at', $this->get_previous_period_iso_range())->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('views.resource_id');
70	$outer_query = Illuminate_Builder::new()->selectRaw('pages.')->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('IFNULL((exits / views) * 100, 0) AS exit_percent')->selectRaw('ROUND(CAST(wc_gross_sales - wc_refunded_amount AS SIGNED)) AS wc_net_sales')->selectRaw('IF(visitors = 0, 0, (wc_orders / landing_page_visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(visitors = 0, 0, (wc_gross_sales - wc_refunded_amount) / landing_page_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) {
71	foreach (Form::get_forms() as $form) {
72	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
73	}
74	})->fromSub($pages_query, 'pages')->leftJoinSub($previous_period_query, 'previous_period_stats', 'pages.id', '=', 'previous_period_stats.resource_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) use($sort_column) {
75	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column));
76	});
77	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
78	$og_outer_query = $outer_query;
79	$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, $sort_column));
80	}
81	return $outer_query;
82	}
83	private function has_wp_comments_table() : bool
84	{
85	if (\is_bool(self::$has_wp_comments_table)) {
86	return self::$has_wp_comments_table;
87	}
88	global $wpdb;
89	self::$has_wp_comments_table = Database::has_table($wpdb->prefix . 'comments');
90	return self::$has_wp_comments_table;
91	}
92	private function get_comments_query() : Builder
93	{
94	global $wpdb;
95	$comments_table = $wpdb->prefix . 'comments';
96	$comments_query = Illuminate_Builder::new()->select(['resources.id AS resource_id'])->selectRaw('COUNT(*) AS comments')->from($comments_table, 'comments')->join(Tables::resources() . ' AS resources', 'comments.comment_post_ID', '=', 'resources.singular_id')->where('comments.comment_type', '=', 'comment')->where('comments.comment_approved', '=', '1')->whereBetween('comments.comment_date_gmt', $this->get_current_period_iso_range())->groupBy('resources.id');
97	return $comments_query;
98	}
99	private function remove_non_singulars_when_filtering_by_comments(Builder $query) : void
100	{
101	foreach ($this->filters as $filter) {
102	if ($filter->column() !== 'comments') {
103	continue;
104	}
105	$query->whereNotNull('singular_id');
106	break;
107	}
108	}
109	}
110