Pages.php — Independent Analytics – WordPress Analytics Plugin 2.7.2

independent-analytics / IAWP / Rows / Pages.php

independent-analytics / IAWP / Rows Last commit date

Campaigns.php 2 years ago Cities.php 2 years ago Countries.php 2 years ago Device_Browsers.php 2 years ago Device_OSS.php 2 years ago Device_Types.php 2 years ago Filter.php 2 years ago Pages.php 2 years ago Referrers.php 2 years ago Rows.php 2 years ago

Pages.php

130 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;
10	use IAWP\Query_Taps;
11	use IAWP\WooCommerce_Order;
12	use IAWPSCOPED\Illuminate\Database\Query\Builder;
13	use IAWPSCOPED\Illuminate\Database\Query\JoinClause;
14	/** @internal */
15	class Pages extends \IAWP\Rows\Rows
16	{
17	private static $has_wp_comments_table = null;
18	public function attach_filters(Builder $query) : void
19	{
20	$query->joinSub($this->query(\true), 'page_rows', function (JoinClause $join) {
21	$join->on('page_rows.id', '=', 'views.resource_id');
22	});
23	}
24	protected function fetch_rows() : array
25	{
26	$rows = $this->query()->get()->all();
27	return \array_map(function (object $row) {
28	return Page::from_row($row);
29	}, $rows);
30	}
31	private function has_wp_comments_table() : bool
32	{
33	if (\is_bool(self::$has_wp_comments_table)) {
34	return self::$has_wp_comments_table;
35	}
36	global $wpdb;
37	self::$has_wp_comments_table = Database::has_table($wpdb->prefix . 'comments');
38	return self::$has_wp_comments_table;
39	}
40	private function query(?bool $skip_pagination = \false) : Builder
41	{
42	if ($skip_pagination) {
43	$this->number_of_rows = null;
44	}
45	$views_table = Query::get_table_name(Query::VIEWS);
46	$sessions_table = Query::get_table_name(Query::SESSIONS);
47	$resources_table = Query::get_table_name(Query::RESOURCES);
48	$wc_orders_table = Query::get_table_name(Query::WC_ORDERS);
49	$database_sort_columns = ['title' => 'cached_title', 'url' => 'cached_url', 'author' => 'cached_author', 'type' => 'cached_type_label', 'date' => 'cached_date', 'category' => 'cached_category'];
50	$sort_column = $this->sort_configuration->column();
51	foreach ($database_sort_columns as $key => $value) {
52	if ($sort_column === $key) {
53	$sort_column = $value;
54	}
55	}
56	$woo_commerce_query = Illuminate_Builder::get_builder();
57	$woo_commerce_query->select(['sessions.initial_view_id AS view_id'])->selectRaw('IFNULL(COUNT(DISTINCT wc_orders.order_id), 0) AS wc_orders')->selectRaw('IFNULL(ROUND(CAST(SUM(wc_orders.total) AS DECIMAL(10, 2))), 0) AS wc_gross_sales')->selectRaw('IFNULL(ROUND(CAST(SUM(wc_orders.total_refunded) AS DECIMAL(10, 2))), 0) AS wc_refunded_amount')->selectRaw('IFNULL(SUM(wc_orders.total_refunds), 0) AS wc_refunds')->from($wc_orders_table, 'wc_orders')->leftJoin($woo_commerce_query->raw($views_table . ' AS views'), function (JoinClause $join) {
58	$join->on('wc_orders.view_id', '=', 'views.id');
59	})->leftJoin($woo_commerce_query->raw($sessions_table . ' AS sessions'), function (JoinClause $join) {
60	$join->on('views.session_id', '=', 'sessions.session_id');
61	})->whereIn('wc_orders.status', WooCommerce_Order::tracked_order_statuses())->whereBetween('wc_orders.created_at', $this->get_current_period_iso_range())->groupBy('wc_orders.view_id');
62	$pages_query = Illuminate_Builder::get_builder();
63	$pages_query->select('resources.*')->selectRaw('COUNT(DISTINCT views.id) AS views')->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('IFNULL(SUM(wc.wc_orders), 0) AS wc_orders')->selectRaw('IFNULL(SUM(wc.wc_gross_sales), 0) AS wc_gross_sales')->selectRaw('IFNULL(SUM(wc.wc_refunded_amount), 0) AS wc_refunded_amount')->selectRaw('IFNULL(SUM(wc_refunds), 0) AS wc_refunds')->selectRaw('IFNULL(SUM(form_submissions.form_submissions), 0) AS form_submissions')->tap(function (Builder $query) {
64	foreach (Form::get_forms() as $form) {
65	$query->selectRaw("SUM(IF(form_submissions.form_id = ?, form_submissions.form_submissions, 0)) AS {$form->submissions_column()}", [$form->id()]);
66	}
67	})->from($views_table, 'views')->leftJoin($pages_query->raw($sessions_table . ' AS sessions'), function (JoinClause $join) {
68	$join->on('views.session_id', '=', 'sessions.session_id');
69	})->leftJoin($pages_query->raw($resources_table . ' AS resources'), function (JoinClause $join) {
70	$join->on('views.resource_id', '=', 'resources.id');
71	})->leftJoin($pages_query->raw($views_table . ' AS initial_view'), function (JoinClause $join) {
72	$join->on('sessions.initial_view_id', '=', 'initial_view.id');
73	})->leftJoin($pages_query->raw($views_table . ' AS final_view'), function (JoinClause $join) {
74	$join->on('sessions.final_view_id', '=', 'final_view.id');
75	})->leftJoinSub($woo_commerce_query, 'wc', function (JoinClause $join) {
76	$join->on('wc.view_id', '=', 'views.id');
77	})->tap(Query_Taps::tap_authored_content_check(\false))->when($this->has_wp_comments_table(), function (Builder $query) {
78	$query->selectRaw('IFNULL(comments.comments, 0) AS comments');
79	$query->leftJoinSub($this->get_comments_query(), 'comments', 'comments.resource_id', '=', 'resources.id');
80	}, function (Builder $query) {
81	$query->selectRaw('0 AS comments');
82	})->leftJoinSub($this->get_form_submissions_query(), 'form_submissions', function (JoinClause $join) {
83	$join->on('form_submissions.view_id', '=', 'views.id');
84	})->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) {
85	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
86	})->when(\count($this->filters) > 0, function (Builder $query) {
87	foreach ($this->filters as $filter) {
88	if (!$this->is_a_calculated_column($filter->column())) {
89	$filter->apply_to_query($query);
90	}
91	}
92	})->groupBy('resources.id')->having('views', '>', 0)->when(!$this->is_using_a_calculated_column(), function (Builder $query) use($sort_column) {
93	$query->when($this->sort_configuration->is_column_nullable(), function (Builder $query) use($sort_column) {
94	$query->orderByRaw("CASE WHEN {$sort_column} IS NULL THEN 1 ELSE 0 END");
95	})->orderBy($sort_column, $this->sort_configuration->direction())->orderBy('cached_title')->when(\is_int($this->number_of_rows), function (Builder $query) {
96	$query->limit($this->number_of_rows);
97	});
98	});
99	$previous_period_query = Illuminate_Builder::get_builder();
100	$previous_period_query->select(['views.resource_id'])->selectRaw('COUNT(*) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from($views_table, 'views')->join($previous_period_query->raw($sessions_table . ' AS sessions'), 'views.session_id', '=', 'sessions.session_id')->whereBetween('views.viewed_at', $this->get_previous_period_iso_range())->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('views.resource_id');
101	$outer_query = Illuminate_Builder::get_builder();
102	$outer_query->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 DECIMAL(10, 2))) 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 DECIMAL(10, 2)))) AS wc_average_order_volume')->selectRaw('IF(visitors = 0, 0, (form_submissions / visitors) * 100) AS form_conversion_rate')->tap(function (Builder $query) {
103	foreach (Form::get_forms() as $form) {
104	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
105	}
106	})->when(\count($this->filters) > 0, function (Builder $query) {
107	foreach ($this->filters as $filter) {
108	if ($this->is_a_calculated_column($filter->column())) {
109	$filter->apply_to_query($query);
110	}
111	}
112	})->fromSub($pages_query, 'pages')->leftJoinSub($previous_period_query, 'previous_period_stats', 'pages.id', '=', 'previous_period_stats.resource_id')->when($this->is_using_a_calculated_column(), function (Builder $query) use($sort_column) {
113	$query->when($this->sort_configuration->is_column_nullable(), function (Builder $query) use($sort_column) {
114	$query->orderByRaw("CASE WHEN {$sort_column} IS NULL THEN 1 ELSE 0 END");
115	})->orderBy($sort_column, $this->sort_configuration->direction())->orderBy('cached_title')->when(\is_int($this->number_of_rows), function (Builder $query) {
116	$query->limit($this->number_of_rows);
117	});
118	});
119	return $outer_query;
120	}
121	private function get_comments_query() : Builder
122	{
123	global $wpdb;
124	$comments_table = $wpdb->prefix . 'comments';
125	$resources_table = Query::get_table_name(Query::RESOURCES);
126	$comments_query = Illuminate_Builder::get_builder()->select(['resources.id AS resource_id'])->selectRaw('COUNT(*) AS comments')->from($comments_table, 'comments')->join($resources_table . " 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');
127	return $comments_query;
128	}
129	}
130