Campaigns.php
1 year ago
Cities.php
1 year ago
Countries.php
1 year ago
Device_Browsers.php
1 year ago
Device_OSS.php
1 year ago
Device_Types.php
1 year ago
Filter.php
1 year ago
Forms.php
1 year ago
Link_Patterns.php
1 year ago
Links.php
1 year ago
Pages.php
1 year ago
Referrers.php
1 year ago
Rows.php
1 year ago
Countries.php
98 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Rows; |
| 4 | |
| 5 | use IAWP\Form_Submissions\Form; |
| 6 | use IAWP\Illuminate_Builder; |
| 7 | use IAWP\Models\Geo; |
| 8 | use IAWP\Query; |
| 9 | use IAWP\Query_Taps; |
| 10 | use IAWPSCOPED\Illuminate\Database\Query\Builder; |
| 11 | use IAWPSCOPED\Illuminate\Database\Query\JoinClause; |
| 12 | /** @internal */ |
| 13 | class Countries extends \IAWP\Rows\Rows |
| 14 | { |
| 15 | public function attach_filters(Builder $query) : void |
| 16 | { |
| 17 | $query->joinSub($this->query(\true), 'country_rows', function (JoinClause $join) { |
| 18 | $join->on('country_rows.country_id', '=', 'sessions.country_id'); |
| 19 | }); |
| 20 | } |
| 21 | protected function fetch_rows() : array |
| 22 | { |
| 23 | $rows = $this->query()->get()->all(); |
| 24 | return \array_map(function ($row) { |
| 25 | return new Geo($row); |
| 26 | }, $rows); |
| 27 | } |
| 28 | private function query(?bool $skip_pagination = \false) : Builder |
| 29 | { |
| 30 | if ($skip_pagination) { |
| 31 | $this->number_of_rows = null; |
| 32 | } |
| 33 | $views_table = Query::get_table_name(Query::VIEWS); |
| 34 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 35 | $orders_table = Query::get_table_name(Query::ORDERS); |
| 36 | $countries_table = Query::get_table_name(Query::COUNTRIES); |
| 37 | $session_statistics = Illuminate_Builder::new(); |
| 38 | $session_statistics->select('sessions.*')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT clicks.click_id) AS clicks')->selectRaw('COUNT(DISTINCT orders.order_id) AS wc_orders')->selectRaw('IFNULL(CAST(SUM(orders.total) AS SIGNED), 0) AS wc_gross_sales')->selectRaw('IFNULL(CAST(SUM(orders.total_refunded) AS SIGNED), 0) AS wc_refunded_amount')->selectRaw('IFNULL(CAST(SUM(orders.total_refunds) AS SIGNED), 0) AS wc_refunds')->selectRaw('IFNULL(SUM(form_submissions.form_submissions), 0) AS form_submissions')->tap(function (Builder $query) { |
| 39 | foreach (Form::get_forms() as $form) { |
| 40 | $query->selectRaw("SUM(IF(form_submissions.form_id = ?, form_submissions.form_submissions, 0)) AS {$form->submissions_column()}", [$form->id()]); |
| 41 | } |
| 42 | })->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) { |
| 43 | $join->on('sessions.session_id', '=', 'views.session_id'); |
| 44 | })->leftJoin("{$orders_table} AS orders", function (JoinClause $join) { |
| 45 | $join->on('views.id', '=', 'orders.initial_view_id')->where('orders.is_included_in_analytics', '=', \true); |
| 46 | })->leftJoin("{$this->tables::clicks()} AS clicks", function (JoinClause $join) { |
| 47 | $join->on('views.id', '=', 'clicks.view_id'); |
| 48 | })->tap(Query_Taps::tap_authored_content_check())->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 49 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 50 | })->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->leftJoinSub($this->get_form_submissions_query(), 'form_submissions', function (JoinClause $join) { |
| 51 | $join->on('form_submissions.view_id', '=', 'views.id'); |
| 52 | })->whereNotNull('sessions.country_id')->groupBy('sessions.session_id'); |
| 53 | $countries_query = Illuminate_Builder::new(); |
| 54 | $countries_query->select('countries.country_id', 'countries.country_code', 'countries.country', 'countries.continent')->selectRaw('IFNULL(CAST(SUM(sessions.views) AS SIGNED), 0) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('AVG( TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at)) AS average_session_duration')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('SUM(sessions.clicks) AS clicks')->selectRaw('SUM(sessions.wc_orders) AS wc_orders')->selectRaw('SUM(sessions.wc_gross_sales) AS wc_gross_sales')->selectRaw('SUM(sessions.wc_refunded_amount) AS wc_refunded_amount')->selectRaw('SUM(sessions.wc_refunds) AS wc_refunds')->selectRaw('SUM(sessions.form_submissions) AS form_submissions')->tap(function (Builder $query) { |
| 55 | foreach (Form::get_forms() as $form) { |
| 56 | $query->selectRaw("SUM(sessions.{$form->submissions_column()}) AS {$form->submissions_column()}"); |
| 57 | } |
| 58 | })->fromSub($session_statistics, 'sessions')->join($countries_query->raw($countries_table . ' AS countries'), function (JoinClause $join) { |
| 59 | $join->on('sessions.country_id', '=', 'countries.country_id'); |
| 60 | })->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 61 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 62 | })->when(\count($this->filters) > 0, function (Builder $query) { |
| 63 | foreach ($this->filters as $filter) { |
| 64 | if (!$this->is_a_calculated_column($filter->column())) { |
| 65 | $filter->apply_to_query($query); |
| 66 | } |
| 67 | } |
| 68 | })->groupBy('countries.country_id')->having('views', '>', 0)->when(!$this->is_using_a_calculated_column(), function (Builder $query) { |
| 69 | $query->when($this->sort_configuration->is_column_nullable(), function (Builder $query) { |
| 70 | $query->orderByRaw("CASE WHEN {$this->sort_configuration->column()} IS NULL THEN 1 ELSE 0 END"); |
| 71 | })->orderBy($this->sort_configuration->column(), $this->sort_configuration->direction())->orderBy('country')->when(\is_int($this->number_of_rows), function (Builder $query) { |
| 72 | $query->limit($this->number_of_rows); |
| 73 | }); |
| 74 | }); |
| 75 | $previous_period_query = Illuminate_Builder::new(); |
| 76 | $previous_period_query->select(['sessions.country_id'])->selectRaw('SUM(sessions.total_views) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from($sessions_table, 'sessions')->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('sessions.country_id'); |
| 77 | $outer_query = Illuminate_Builder::new(); |
| 78 | $outer_query->selectRaw('countries.*')->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(bounces / sessions * 100, 0) AS bounce_rate')->selectRaw('ROUND(CAST(wc_gross_sales - wc_refunded_amount AS SIGNED)) AS wc_net_sales')->selectRaw('IF(visitors = 0, 0, (wc_orders / visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(visitors = 0, 0, (wc_gross_sales - wc_refunded_amount) / 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) { |
| 79 | foreach (Form::get_forms() as $form) { |
| 80 | $query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}"); |
| 81 | } |
| 82 | })->when(\count($this->filters) > 0, function (Builder $query) { |
| 83 | foreach ($this->filters as $filter) { |
| 84 | if ($this->is_a_calculated_column($filter->column())) { |
| 85 | $filter->apply_to_query($query); |
| 86 | } |
| 87 | } |
| 88 | })->fromSub($countries_query, 'countries')->leftJoinSub($previous_period_query, 'previous_period_stats', 'countries.country_id', '=', 'previous_period_stats.country_id')->when($this->is_using_a_calculated_column(), function (Builder $query) { |
| 89 | $query->when($this->sort_configuration->is_column_nullable(), function (Builder $query) { |
| 90 | $query->orderByRaw("CASE WHEN {$this->sort_configuration->column()} IS NULL THEN 1 ELSE 0 END"); |
| 91 | })->orderBy($this->sort_configuration->column(), $this->sort_configuration->direction())->orderBy('country')->when(\is_int($this->number_of_rows), function (Builder $query) { |
| 92 | $query->limit($this->number_of_rows); |
| 93 | }); |
| 94 | }); |
| 95 | return $outer_query; |
| 96 | } |
| 97 | } |
| 98 |