Campaign_Landing_Pages.php
1 week ago
Campaign_UTM_Campaigns.php
1 week ago
Campaign_UTM_Mediums.php
1 week ago
Campaign_UTM_Sources.php
1 week ago
Campaigns.php
1 week ago
Cities.php
1 week ago
Countries.php
1 week ago
Device_Browsers.php
1 week ago
Device_OSS.php
1 week ago
Device_Types.php
1 week 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
1 week ago
Referrers.php
1 week ago
Rows.php
2 weeks ago
Journeys.php
55 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Rows; |
| 4 | |
| 5 | use IAWP\Illuminate_Builder; |
| 6 | use IAWP\Models\Journey; |
| 7 | use IAWP\Tables; |
| 8 | use IAWPSCOPED\Illuminate\Database\Query\Builder; |
| 9 | /** @internal */ |
| 10 | class Journeys extends \IAWP\Rows\Rows |
| 11 | { |
| 12 | private ?int $visitor_id = null; |
| 13 | public function attach_filters(Builder $query) : void |
| 14 | { |
| 15 | // TODO |
| 16 | } |
| 17 | public function limit_to_visitor(int $id) : void |
| 18 | { |
| 19 | $this->visitor_id = $id; |
| 20 | } |
| 21 | protected function fetch_rows() : array |
| 22 | { |
| 23 | $rows = $this->query()->get()->all(); |
| 24 | return \array_map(function ($row) { |
| 25 | return new Journey($row); |
| 26 | }, $rows); |
| 27 | } |
| 28 | protected function sort_tie_breaker_column() : string |
| 29 | { |
| 30 | return 'cached_title'; |
| 31 | } |
| 32 | private function query(?bool $skip_pagination = \false) : Builder |
| 33 | { |
| 34 | if ($skip_pagination) { |
| 35 | $this->number_of_rows = null; |
| 36 | } |
| 37 | $sort_column = $this->sort_configuration->column(); |
| 38 | $click_conversion_subquery = Illuminate_Builder::new()->select(['sessions.session_id', 'clicks.click_id', 'link_rules.link_rule_id', 'link_rules.name'])->from(Tables::sessions(), 'sessions')->join(Tables::views() . ' AS views', 'sessions.session_id', '=', 'views.session_id')->join(Tables::clicks() . ' AS clicks', 'views.id', '=', 'clicks.view_id')->join(Tables::clicked_links() . ' AS clicked_links', 'clicks.click_id', '=', 'clicked_links.click_id')->join(Tables::links() . ' AS links', 'clicked_links.link_id', '=', 'links.id')->join(Tables::link_rules() . ' AS link_rules', 'links.link_rule_id', '=', 'link_rules.link_rule_id')->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 39 | $orders_subquery = Illuminate_Builder::new()->select(['sessions.session_id'])->selectRaw('COUNT(DISTINCT orders.order_id) AS 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')->from(Tables::sessions(), 'sessions')->join(Tables::views() . ' AS views', 'sessions.session_id', '=', 'views.session_id')->join(Tables::orders() . ' AS orders', 'views.id', '=', 'orders.view_id')->whereBetween('sessions.created_at', $this->get_current_period_iso_range())->groupBy('sessions.session_id'); |
| 40 | $journeys_query = Illuminate_Builder::new()->select(['sessions.session_id', 'sessions.created_at', 'initial_resources.cached_title', 'initial_resources.cached_url', 'referrers.referrer', 'referrers.domain', 'referrer_types.referrer_type', 'countries.country_id', 'countries.country_code', 'countries.country', 'device_types.device_type_id', 'device_types.device_type', 'device_browsers.device_browser_id', 'device_browsers.device_browser', 'utm_sources.utm_source'])->selectRaw('TIME_TO_SEC(TIMEDIFF(sessions.ended_at, sessions.created_at)) AS duration')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT clicks.click_id) AS clicks')->selectRaw('COUNT(DISTINCT form_submissions.form_submission_id) AS form_submissions')->selectRaw('MIN(orders.orders) AS orders')->selectRaw('MIN(IFNULL(orders.wc_gross_sales, 0)) AS wc_gross_sales')->selectRaw('MIN(orders.wc_refunded_amount) AS wc_refunded_amount')->from(Tables::sessions(), 'sessions')->leftJoin(Tables::views() . ' AS initial_views', 'sessions.initial_view_id', '=', 'initial_views.id')->leftJoin(Tables::resources() . ' AS initial_resources', 'initial_views.resource_id', '=', 'initial_resources.id')->leftJoin(Tables::referrers() . ' AS referrers', 'sessions.referrer_id', '=', 'referrers.id')->leftJoin(Tables::referrer_types() . ' AS referrer_types', 'referrers.referrer_type_id', '=', 'referrer_types.id')->leftJoin(Tables::views() . ' AS views', 'sessions.session_id', '=', 'views.session_id')->leftJoin(Tables::resources() . ' AS resources', 'views.resource_id', '=', 'resources.id')->leftJoin(Tables::countries() . ' AS countries', 'sessions.country_id', '=', 'countries.country_id')->leftJoin(Tables::device_types() . ' AS device_types', 'sessions.device_type_id', '=', 'device_types.device_type_id')->leftJoin(Tables::device_browsers() . ' AS device_browsers', 'sessions.device_browser_id', '=', 'device_browsers.device_browser_id')->leftJoin(Tables::campaigns() . ' AS campaigns', 'sessions.campaign_id', '=', 'campaigns.campaign_id')->leftJoin(Tables::utm_campaigns() . ' AS utm_campaigns', 'campaigns.utm_campaign_id', '=', 'utm_campaigns.id')->leftJoin(Tables::utm_mediums() . ' AS utm_mediums', 'campaigns.utm_medium_id', '=', 'utm_mediums.id')->leftJoin(Tables::utm_sources() . ' AS utm_sources', 'campaigns.utm_source_id', '=', 'utm_sources.id')->leftJoin(Tables::orders() . ' AS orders', 'views.id', '=', 'orders.view_id')->leftJoin(Tables::form_submissions() . ' AS form_submissions', 'sessions.session_id', '=', 'form_submissions.session_id')->leftJoinSub($click_conversion_subquery, 'clicks', 'sessions.session_id', '=', 'clicks.session_id')->leftJoinSub($orders_subquery, 'orders', 'sessions.session_id', '=', 'orders.session_id')->whereBetween('sessions.created_at', $this->get_current_period_iso_range())->when(\is_int($this->visitor_id), function (Builder $query) { |
| 41 | $query->where('sessions.visitor_id', '=', $this->visitor_id); |
| 42 | })->tap(fn(Builder $query) => $this->apply_record_filters($query))->when($this->can_order_and_limit_at_record_level(), function (Builder $query) use($sort_column) { |
| 43 | $query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column)); |
| 44 | })->groupBy('sessions.session_id'); |
| 45 | $outer_query = Illuminate_Builder::new()->fromSub($journeys_query, 'journeys')->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) { |
| 46 | $query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $sort_column)); |
| 47 | }); |
| 48 | if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) { |
| 49 | $og_outer_query = $outer_query; |
| 50 | $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)); |
| 51 | } |
| 52 | return $outer_query; |
| 53 | } |
| 54 | } |
| 55 |