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
133 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP_SCOPED\IAWP\Rows; |
| 4 | |
| 5 | use IAWP_SCOPED\IAWP\Illuminate_Builder; |
| 6 | use IAWP_SCOPED\IAWP\Models\Page; |
| 7 | use IAWP_SCOPED\IAWP\Query; |
| 8 | use IAWP_SCOPED\Illuminate\Database\Query\Builder; |
| 9 | use IAWP_SCOPED\Illuminate\Database\Query\JoinClause; |
| 10 | /** @internal */ |
| 11 | class Pages extends Rows |
| 12 | { |
| 13 | private static $has_wp_comments_table = null; |
| 14 | public function attach_filters(Builder $query) : void |
| 15 | { |
| 16 | $query->joinSub($this->query(\true), 'page_rows', function (JoinClause $join) { |
| 17 | $join->on('page_rows.id', '=', 'views.resource_id'); |
| 18 | }); |
| 19 | } |
| 20 | protected function fetch_rows() : array |
| 21 | { |
| 22 | $rows = $this->query()->get()->all(); |
| 23 | return \array_map(function (object $row) { |
| 24 | return Page::from_row($row); |
| 25 | }, $rows); |
| 26 | } |
| 27 | private function has_wp_comments_table() : bool |
| 28 | { |
| 29 | if (\is_bool(self::$has_wp_comments_table)) { |
| 30 | return self::$has_wp_comments_table; |
| 31 | } |
| 32 | global $wpdb; |
| 33 | $table_name = $wpdb->prefix . 'comments'; |
| 34 | $tables = $wpdb->get_row($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)); |
| 35 | self::$has_wp_comments_table = !\is_null($tables); |
| 36 | return self::$has_wp_comments_table; |
| 37 | } |
| 38 | private function query(?bool $skip_pagination = \false) : Builder |
| 39 | { |
| 40 | global $wpdb; |
| 41 | if ($skip_pagination) { |
| 42 | $this->number_of_rows = null; |
| 43 | } |
| 44 | $views_table = Query::get_table_name(Query::VIEWS); |
| 45 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 46 | $resources_table = Query::get_table_name(Query::RESOURCES); |
| 47 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 48 | $comments_table = $wpdb->prefix . 'comments'; |
| 49 | $current_period_array = [$this->date_range->iso_start(), $this->date_range->iso_end()]; |
| 50 | $previous_period_array = [$this->date_range->previous_period()->iso_start(), $this->date_range->previous_period()->iso_end()]; |
| 51 | $total_period_array = [$this->date_range->previous_period()->iso_start(), $this->date_range->iso_end()]; |
| 52 | $calculated_columns = ['comments', 'views_growth', 'visitors_growth', 'bounce_rate', 'exit_percent', 'wc_net_sales', 'wc_conversion_rate', 'wc_earnings_per_visitor', 'wc_average_order_volume']; |
| 53 | $has_calculate_column_filter = !empty(\array_filter($this->filters, function ($filter) use($calculated_columns) { |
| 54 | return \in_array($filter['column'], $calculated_columns); |
| 55 | })); |
| 56 | if (\in_array($this->sort_configuration->column(), $calculated_columns)) { |
| 57 | $has_calculate_column_filter = \true; |
| 58 | } |
| 59 | $database_sort_columns = ['title' => 'cached_title', 'url' => 'cached_url', 'author' => 'cached_author', 'type' => 'cached_type_label', 'date' => 'cached_date', 'category' => 'cached_category']; |
| 60 | $sort_column = $this->sort_configuration->column(); |
| 61 | foreach ($database_sort_columns as $key => $value) { |
| 62 | if ($sort_column === $key) { |
| 63 | $sort_column = $value; |
| 64 | } |
| 65 | } |
| 66 | $woo_commerce_query = Illuminate_Builder::get_builder(); |
| 67 | $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) { |
| 68 | $join->on('wc_orders.view_id', '=', 'views.id'); |
| 69 | })->leftJoin($woo_commerce_query->raw($sessions_table . ' AS sessions'), function (JoinClause $join) { |
| 70 | $join->on('views.session_id', '=', 'sessions.session_id'); |
| 71 | })->whereIn('wc_orders.status', ['wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded'])->whereBetween('wc_orders.created_at', $current_period_array)->groupBy('wc_orders.view_id'); |
| 72 | $pages_query = Illuminate_Builder::get_builder(); |
| 73 | $pages_query->select('resources.*')->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ?, views.id, NULL)) AS views', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ?, views.id, NULL)) AS previous_period_views', $previous_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ?, sessions.visitor_id, NULL)) AS visitors', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ? AND initial_view.resource_id = resources.id, sessions.visitor_id, NULL)) AS landing_page_visitors', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ?, sessions.visitor_id, NULL)) AS previous_period_visitors', $previous_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ?, sessions.session_id, NULL)) AS sessions', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ? AND sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces', $current_period_array)->selectRaw('IF(resources.singular_id IS NOT NULL, COUNT(DISTINCT IF(comments.comment_date_gmt BETWEEN ? AND ? AND comments.comment_approved = "1", comments.comment_ID, null)), NULL) as comments', $current_period_array)->selectRaw('AVG(IF(views.viewed_at BETWEEN ? AND ?, TIMESTAMPDIFF(SECOND, views.viewed_at, views.next_viewed_at), NULL)) AS average_view_duration', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ? AND resources.id = initial_view.resource_id, sessions.session_id, NULL)) AS entrances', $current_period_array)->selectRaw('COUNT(DISTINCT IF(views.viewed_at BETWEEN ? AND ? AND (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', $current_period_array)->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')->from($views_table, 'views')->leftJoin($pages_query->raw($sessions_table . ' AS sessions'), function (JoinClause $join) { |
| 74 | $join->on('views.session_id', '=', 'sessions.session_id'); |
| 75 | })->leftJoin($pages_query->raw($resources_table . ' AS resources'), function (JoinClause $join) { |
| 76 | $join->on('views.resource_id', '=', 'resources.id'); |
| 77 | })->leftJoin($pages_query->raw($views_table . ' AS initial_view'), function (JoinClause $join) { |
| 78 | $join->on('sessions.initial_view_id', '=', 'initial_view.id'); |
| 79 | })->leftJoin($pages_query->raw($views_table . ' AS final_view'), function (JoinClause $join) { |
| 80 | $join->on('sessions.final_view_id', '=', 'final_view.id'); |
| 81 | })->leftJoinSub($woo_commerce_query, 'wc', function (JoinClause $join) { |
| 82 | $join->on('wc.view_id', '=', 'views.id'); |
| 83 | })->when($this->has_wp_comments_table(), function (Builder $query) use($comments_table) { |
| 84 | $query->leftJoin($query->raw($comments_table . ' AS comments'), function (JoinClause $join) { |
| 85 | $join->on('resources.singular_id', '=', 'comments.comment_post_ID'); |
| 86 | }); |
| 87 | }, function (Builder $query) { |
| 88 | $query->leftJoinSub('SELECT now() AS comment_date_gmt, 0 AS comment_ID, 0 AS comment_post_ID, "1" AS comment_approved LIMIT 0', 'comments', function (JoinClause $join) { |
| 89 | $join->on('resources.singular_id', '=', 'comments.comment_post_ID'); |
| 90 | }); |
| 91 | })->whereBetween('views.viewed_at', $total_period_array)->whereBetween('sessions.created_at', $total_period_array)->where(function (Builder $query) use($total_period_array) { |
| 92 | $query->whereNull('sessions.ended_at')->orWhereBetween('sessions.ended_at', $total_period_array); |
| 93 | })->where(function (Builder $query) use($total_period_array) { |
| 94 | $query->whereNull('initial_view.viewed_at')->orWhereBetween('initial_view.viewed_at', $total_period_array); |
| 95 | })->where(function (Builder $query) use($total_period_array) { |
| 96 | $query->whereNull('final_view.viewed_at')->orWhereBetween('final_view.viewed_at', $total_period_array); |
| 97 | })->when(\count($this->filters) > 0, function (Builder $query) use($calculated_columns) { |
| 98 | foreach ($this->filters as $filter) { |
| 99 | if (\in_array($filter['column'], $calculated_columns)) { |
| 100 | continue; |
| 101 | } |
| 102 | $filter = new Filter($filter); |
| 103 | $method = $filter->method(); |
| 104 | $query->{$method}($filter->column(), $filter->operator(), $filter->value()); |
| 105 | } |
| 106 | })->groupBy('resources.id')->having('views', '>', 0)->when(!$has_calculate_column_filter, function (Builder $query) use($sort_column) { |
| 107 | $query->when($this->sort_configuration->is_nullable(), function (Builder $query) use($sort_column) { |
| 108 | $query->orderByRaw("CASE WHEN {$sort_column} IS NULL THEN 1 ELSE 0 END"); |
| 109 | })->orderBy($sort_column, $this->sort_configuration->direction())->orderBy('cached_title')->when(\is_int($this->number_of_rows), function (Builder $query) { |
| 110 | $query->limit($this->number_of_rows); |
| 111 | }); |
| 112 | }); |
| 113 | $outer_query = Illuminate_Builder::get_builder(); |
| 114 | $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')->when(\count($this->filters) > 0, function (Builder $query) use($calculated_columns) { |
| 115 | foreach ($this->filters as $filter) { |
| 116 | if (!\in_array($filter['column'], $calculated_columns)) { |
| 117 | continue; |
| 118 | } |
| 119 | $filter = new Filter($filter); |
| 120 | $method = $filter->method(); |
| 121 | $query->{$method}($filter->column(), $filter->operator(), $filter->value()); |
| 122 | } |
| 123 | })->fromSub($pages_query, 'pages')->when($has_calculate_column_filter, function (Builder $query) use($sort_column) { |
| 124 | $query->when($this->sort_configuration->is_nullable(), function (Builder $query) use($sort_column) { |
| 125 | $query->orderByRaw("CASE WHEN {$sort_column} IS NULL THEN 1 ELSE 0 END"); |
| 126 | })->orderBy($sort_column, $this->sort_configuration->direction())->orderBy('cached_title')->when(\is_int($this->number_of_rows), function (Builder $query) { |
| 127 | $query->limit($this->number_of_rows); |
| 128 | }); |
| 129 | }); |
| 130 | return $outer_query; |
| 131 | } |
| 132 | } |
| 133 |