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
6 months ago
Forms.php
6 months ago
Journeys.php
5 months ago
Link_Patterns.php
5 months ago
Links.php
6 months ago
Pages.php
5 months ago
Referrer_Types.php
5 months ago
Referrers.php
5 months ago
Rows.php
6 months ago
Pages.php
119 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->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 56 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 57 | })->groupBy('views.resource_id'); |
| 58 | $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->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 59 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 60 | })->groupBy('views.resource_id'); |
| 61 | $form_submissions_subquery = Illuminate_Builder::new()->select('views.resource_id')->selectRaw('COUNT(*) AS form_submissions')->tap(function (Builder $query) { |
| 62 | foreach (Form::get_forms() as $form) { |
| 63 | $query->selectRaw('CAST(SUM(IF(form_id = ?, 1, 0)) AS SIGNED) AS ' . $form->submissions_column(), [$form->id()]); |
| 64 | } |
| 65 | })->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->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 66 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 67 | })->groupBy('views.resource_id'); |
| 68 | $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) { |
| 69 | $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->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 70 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 71 | }); |
| 72 | })->groupBy('views.resource_id'); |
| 73 | $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->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 74 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 75 | })->when(\is_int($this->solo_record_id), function (Builder $query) { |
| 76 | $query->where('views.resource_id', '=', $this->solo_record_id); |
| 77 | })->when($this->can_order_and_limit_at_record_level(), function (Builder $query) use($sort_column) { |
| 78 | $query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column)); |
| 79 | }); |
| 80 | $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'); |
| 81 | $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) { |
| 82 | foreach (Form::get_forms() as $form) { |
| 83 | $column = $form->submissions_column(); |
| 84 | $query->selectRaw("IFNULL(form_submission_stats.{$column}, 0) AS {$column}"); |
| 85 | $query->selectRaw("IF(visitors = 0, 0, (IFNULL(form_submission_stats.{$column}, 0) / visitors) * 100) AS {$form->conversion_rate_column()}"); |
| 86 | } |
| 87 | })->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) { |
| 88 | $query->selectRaw('IFNULL(comments.comments, 0) AS comments'); |
| 89 | $query->leftJoinSub($this->get_comments_query(), 'comments', 'comments.resource_id', '=', 'resources.id'); |
| 90 | }, function (Builder $query) { |
| 91 | $query->selectRaw('0 AS comments'); |
| 92 | })->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) { |
| 93 | $query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column)); |
| 94 | }); |
| 95 | if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) { |
| 96 | $og_outer_query = $outer_query; |
| 97 | $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)); |
| 98 | } |
| 99 | return $outer_query; |
| 100 | } |
| 101 | private function get_comments_query() : Builder |
| 102 | { |
| 103 | global $wpdb; |
| 104 | $comments_table = $wpdb->prefix . 'comments'; |
| 105 | $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'); |
| 106 | return $comments_query; |
| 107 | } |
| 108 | private function remove_non_singulars_when_filtering_by_comments(Builder $query) : void |
| 109 | { |
| 110 | foreach ($this->filters as $filter) { |
| 111 | if ($filter->column() !== 'comments') { |
| 112 | continue; |
| 113 | } |
| 114 | $query->whereNotNull('singular_id'); |
| 115 | break; |
| 116 | } |
| 117 | } |
| 118 | } |
| 119 |