Pages.php — Independent Analytics – WordPress Analytics Plugin 2.15.1

independent-analytics / IAWP / Rows / Pages.php

independent-analytics / IAWP / Rows Last commit date

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

Pages.php

129 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->query(\true), '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	private function has_wp_comments_table() : bool
35	{
36	if (\is_bool(self::$has_wp_comments_table)) {
37	return self::$has_wp_comments_table;
38	}
39	global $wpdb;
40	self::$has_wp_comments_table = Database::has_table($wpdb->prefix . 'comments');
41	return self::$has_wp_comments_table;
42	}
43	private function query(?bool $skip_pagination = \false) : Builder
44	{
45	if ($skip_pagination) {
46	$this->number_of_rows = null;
47	}
48	$database_sort_columns = ['title' => 'cached_title', 'url' => 'cached_url', 'author' => 'cached_author', 'type' => 'cached_type_label', 'date' => 'cached_date', 'category' => 'cached_category'];
49	$sort_column = $this->sort_configuration->column();
50	foreach ($database_sort_columns as $key => $value) {
51	if ($sort_column === $key) {
52	$sort_column = $value;
53	}
54	}
55	$clicks_subquery = Illuminate_Builder::new()->select('views.resource_id')->selectRaw('COUNT(*) AS clicks')->from(Tables::clicks(), 'clicks')->join(Tables::views() . ' AS views', 'clicks.view_id', '=', 'views.id')->join(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->tap(Query_Taps::tap_authored_content_check())->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config, ['clicks']))->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when($this->is_real_time(), function (Builder $query) {
56	$query->whereNull('views.next_view_id');
57	})->when(!$this->is_real_time(), function (Builder $query) {
58	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
59	})->groupBy('views.resource_id');
60	$orders_subquery = Illuminate_Builder::new()->select('views.resource_id')->selectRaw('COUNT(*) AS orders')->selectRaw('IFNULL(CAST(SUM(total) AS SIGNED), 0) AS gross_sales')->selectRaw('IFNULL(CAST(SUM(total_refunded) AS SIGNED), 0) AS total_refunded')->selectRaw('IFNULL(CAST(SUM(total_refunds) AS SIGNED), 0) AS total_refunds')->selectRaw('IFNULL(CAST(SUM(total - total_refunded) AS SIGNED), 0) AS net_sales')->from(Tables::orders(), 'orders')->join(Tables::views() . ' AS views', 'orders.initial_view_id', '=', 'views.id')->join(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->where('is_included_in_analytics', '=', \true)->tap(Query_Taps::tap_authored_content_check())->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when($this->is_real_time(), function (Builder $query) {
61	$query->whereNull('views.next_view_id');
62	})->when(!$this->is_real_time(), function (Builder $query) {
63	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
64	})->groupBy('views.resource_id');
65	$form_submissions_subquery = Illuminate_Builder::new()->select('views.resource_id')->selectRaw('COUNT(*) AS form_submissions')->tap(function (Builder $query) {
66	foreach (Form::get_forms() as $form) {
67	$query->selectRaw('CAST(SUM(IF(form_id = ?, 1, 0)) AS SIGNED) AS ' . $form->submissions_column(), [$form->id()]);
68	}
69	})->from(Tables::form_submissions(), 'form_submissions')->join(Tables::views() . ' AS views', 'form_submissions.view_id', '=', 'views.id')->join(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->tap(Query_Taps::tap_authored_content_check())->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when($this->is_real_time(), function (Builder $query) {
70	$query->whereNull('views.next_view_id');
71	})->when(!$this->is_real_time(), function (Builder $query) {
72	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
73	})->groupBy('views.resource_id');
74	$average_view_duration_subquery = Illuminate_Builder::new()->select(['views.resource_id'])->selectRaw('AVG(TIMESTAMPDIFF(SECOND, views.viewed_at, views.next_viewed_at)) AS average_view_duration')->from(Tables::views(), 'views')->whereIn('views.id', function ($subquery) {
75	$subquery->select('views.id')->from(Tables::views(), 'views')->join(Tables::sessions() . ' AS sessions', 'views.session_id', '=', 'sessions.session_id')->tap(Query_Taps::tap_authored_content_check())->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when($this->is_real_time(), function (Builder $query) {
76	$query->whereNull('views.next_view_id');
77	})->when(!$this->is_real_time(), function (Builder $query) {
78	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
79	});
80	})->groupBy('views.resource_id');
81	$resources_query = Illuminate_Builder::new()->select('resources.*')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('MAX(average_view_duration.average_view_duration) 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(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->from(Tables::sessions() . ' AS sessions')->leftJoin(Tables::views() . ' AS views', 'sessions.session_id', '=', 'views.session_id')->leftJoin(Tables::resources() . ' AS resources', 'resources.id', '=', 'views.resource_id')->leftJoin(Tables::views() . ' AS initial_view', 'initial_view.id', '=', 'sessions.initial_view_id')->leftJoin(Tables::views() . ' AS final_view', 'final_view.id', '=', 'sessions.final_view_id')->leftJoinSub($average_view_duration_subquery, 'average_view_duration', 'average_view_duration.resource_id', '=', 'views.resource_id')->tap(Query_Taps::tap_authored_content_check(\false))->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->groupBy('views.resource_id')->having('views', '>', 0)->tap(fn(Builder $query) => $this->apply_record_filters($query))->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when($this->is_real_time(), function (Builder $query) {
82	$query->whereNull('views.next_view_id');
83	})->when(!$this->is_real_time(), function (Builder $query) {
84	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
85	})->when(\is_int($this->solo_record_id), function (Builder $query) {
86	$query->where('views.resource_id', '=', $this->solo_record_id);
87	})->when($this->can_order_and_limit_at_record_level(), function (Builder $query) use($sort_column) {
88	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column));
89	});
90	$previous_period_query = Illuminate_Builder::new()->select('views.resource_id')->selectRaw('COUNT(DISTINCT views.id) 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']))->whereNotNull('views.resource_id')->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('views.resource_id');
91	$outer_query = Illuminate_Builder::new()->selectRaw('resources.')->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((exits / views) * 100, 0) AS exit_percent')->selectRaw('IFNULL(bounces / sessions * 100, 0) AS bounce_rate')->selectRaw('IFNULL(click_stats.clicks, 0) AS clicks')->selectRaw('IFNULL(order_stats.orders, 0) AS wc_orders')->selectRaw('IFNULL(order_stats.gross_sales, 0) AS wc_gross_sales')->selectRaw('IFNULL(order_stats.total_refunded, 0) AS wc_refunded_amount')->selectRaw('IFNULL(order_stats.total_refunds, 0) AS wc_refunds')->selectRaw('ROUND(CAST(IFNULL(order_stats.gross_sales, 0) - IFNULL(order_stats.total_refunded, 0) AS SIGNED)) AS wc_net_sales')->selectRaw('IF(visitors = 0, 0, (IFNULL(order_stats.orders, 0) / visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(visitors = 0, 0, (IFNULL(order_stats.gross_sales, 0) - IFNULL(order_stats.total_refunded, 0)) / visitors) AS wc_earnings_per_visitor')->selectRaw('IF(IFNULL(order_stats.orders, 0) = 0, 0, ROUND(CAST(IFNULL(order_stats.gross_sales, 0) / order_stats.orders AS SIGNED))) AS wc_average_order_volume')->selectRaw('IFNULL(form_submission_stats.form_submissions, 0) AS form_submissions')->selectRaw('IF(visitors = 0, 0, (IFNULL(form_submission_stats.form_submissions, 0) / visitors) * 100) AS form_conversion_rate')->tap(function (Builder $query) {
92	foreach (Form::get_forms() as $form) {
93	$column = $form->submissions_column();
94	$query->selectRaw("IFNULL(form_submission_stats.{$column}, 0) AS {$column}");
95	$query->selectRaw("IF(visitors = 0, 0, (IFNULL(form_submission_stats.{$column}, 0) / visitors) * 100) AS {$form->conversion_rate_column()}");
96	}
97	})->fromSub($resources_query, 'resources')->leftJoinSub($clicks_subquery, 'click_stats', 'click_stats.resource_id', '=', 'resources.id')->leftJoinSub($orders_subquery, 'order_stats', 'order_stats.resource_id', '=', 'resources.id')->leftJoinSub($form_submissions_subquery, 'form_submission_stats', 'form_submission_stats.resource_id', '=', 'resources.id')->leftJoinSub($previous_period_query, 'previous_period_stats', 'previous_period_stats.resource_id', '=', 'resources.id')->when($this->has_wp_comments_table(), function (Builder $query) {
98	$query->selectRaw('IFNULL(comments.comments, 0) AS comments');
99	$query->leftJoinSub($this->get_comments_query(), 'comments', 'comments.resource_id', '=', 'resources.id');
100	}, function (Builder $query) {
101	$query->selectRaw('0 AS comments');
102	})->tap(fn(Builder $query) => $this->remove_non_singulars_when_filtering_by_comments($query))->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) {
103	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column));
104	});
105	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
106	$og_outer_query = $outer_query;
107	$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));
108	}
109	return $outer_query;
110	}
111	private function get_comments_query() : Builder
112	{
113	global $wpdb;
114	$comments_table = $wpdb->prefix . 'comments';
115	$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');
116	return $comments_query;
117	}
118	private function remove_non_singulars_when_filtering_by_comments(Builder $query) : void
119	{
120	foreach ($this->filters as $filter) {
121	if ($filter->column() !== 'comments') {
122	continue;
123	}
124	$query->whereNotNull('singular_id');
125	break;
126	}
127	}
128	}
129