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
5 months ago
Forms.php
5 months ago
Journeys.php
5 months ago
Link_Patterns.php
5 months ago
Links.php
5 months ago
Pages.php
5 months ago
Referrer_Types.php
5 months ago
Referrers.php
5 months ago
Rows.php
5 months ago
Rows.php
213 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Rows; |
| 4 | |
| 5 | use IAWP\Date_Range\Date_Range; |
| 6 | use IAWP\Examiner_Config; |
| 7 | use IAWP\Form_Submissions\Form; |
| 8 | use IAWP\Illuminate_Builder; |
| 9 | use IAWP\Query_Taps; |
| 10 | use IAWP\Sort_Configuration; |
| 11 | use IAWP\Tables; |
| 12 | use IAWPSCOPED\Illuminate\Database\Query\Builder; |
| 13 | /** @internal */ |
| 14 | abstract class Rows |
| 15 | { |
| 16 | protected $tables = Tables::class; |
| 17 | protected $date_range; |
| 18 | protected $number_of_rows; |
| 19 | /** @var Filter[] */ |
| 20 | protected $filters; |
| 21 | protected $sort_configuration; |
| 22 | protected $filter_logic; |
| 23 | protected $solo_record_id = null; |
| 24 | protected $examiner_config = null; |
| 25 | private $rows = null; |
| 26 | private $cached_filter_query = null; |
| 27 | public function __construct(Date_Range $date_range, Sort_Configuration $sort_configuration, ?int $number_of_rows = null, ?array $filters = null, ?string $filter_logic = null) |
| 28 | { |
| 29 | $this->date_range = $date_range; |
| 30 | $this->sort_configuration = $sort_configuration; |
| 31 | $this->number_of_rows = $number_of_rows; |
| 32 | $this->filters = $filters ?? []; |
| 33 | $this->filter_logic = \in_array($filter_logic, ['and', 'or']) ? $filter_logic : 'and'; |
| 34 | } |
| 35 | protected abstract function fetch_rows() : array; |
| 36 | public abstract function attach_filters(Builder $query) : void; |
| 37 | protected abstract function query(?bool $skip_pagination = \false) : Builder; |
| 38 | protected abstract function sort_tie_breaker_column() : string; |
| 39 | /** |
| 40 | * Used to limit the rows to just a single record. This is useful if you want a single page, |
| 41 | * referrer, etc. row, and you know the records database id. |
| 42 | * |
| 43 | * @param int $id |
| 44 | * |
| 45 | * @return void |
| 46 | */ |
| 47 | public function limit_to(int $id) : void |
| 48 | { |
| 49 | $this->solo_record_id = $id; |
| 50 | } |
| 51 | public function for_examiner(Examiner_Config $config) |
| 52 | { |
| 53 | $this->examiner_config = $config; |
| 54 | } |
| 55 | public function rows() |
| 56 | { |
| 57 | if (\is_array($this->rows)) { |
| 58 | return $this->rows; |
| 59 | } |
| 60 | $this->rows = $this->fetch_rows(); |
| 61 | return $this->rows; |
| 62 | } |
| 63 | protected function get_filter_query() : Builder |
| 64 | { |
| 65 | if ($this->cached_filter_query === null) { |
| 66 | $this->cached_filter_query = $this->query(\true); |
| 67 | } |
| 68 | return clone $this->cached_filter_query; |
| 69 | } |
| 70 | protected function only_filtering_by_record_columns() : bool |
| 71 | { |
| 72 | if (\count($this->filters) === 0) { |
| 73 | return \true; |
| 74 | } |
| 75 | foreach ($this->filters as $filter) { |
| 76 | if ($filter->is_calculated_column()) { |
| 77 | return \false; |
| 78 | } |
| 79 | } |
| 80 | return \true; |
| 81 | } |
| 82 | protected function only_filtering_by_aggregate_columns() : bool |
| 83 | { |
| 84 | if (\count($this->filters) === 0) { |
| 85 | return \false; |
| 86 | } |
| 87 | foreach ($this->filters as $filter) { |
| 88 | if ($filter->is_concrete_column()) { |
| 89 | return \false; |
| 90 | } |
| 91 | } |
| 92 | return \true; |
| 93 | } |
| 94 | protected function filtering_by_mixed_columns() : bool |
| 95 | { |
| 96 | if (\count($this->filters) === 0) { |
| 97 | return \false; |
| 98 | } |
| 99 | $has_record_column = \false; |
| 100 | $has_aggregate_column = \false; |
| 101 | foreach ($this->filters as $filter) { |
| 102 | if ($filter->is_calculated_column()) { |
| 103 | $has_aggregate_column = \true; |
| 104 | } else { |
| 105 | $has_record_column = \true; |
| 106 | } |
| 107 | if ($has_record_column && $has_aggregate_column) { |
| 108 | return \true; |
| 109 | } |
| 110 | } |
| 111 | return \false; |
| 112 | } |
| 113 | protected function get_current_period_iso_range() : array |
| 114 | { |
| 115 | return [$this->date_range->iso_start(), $this->date_range->iso_end()]; |
| 116 | } |
| 117 | protected function appears_to_be_for_real_time_analytics() : bool |
| 118 | { |
| 119 | $difference_in_seconds = $this->date_range->end()->getTimestamp() - $this->date_range->start()->getTimestamp(); |
| 120 | $one_hour_in_seconds = 3600; |
| 121 | return $difference_in_seconds < $one_hour_in_seconds; |
| 122 | } |
| 123 | protected function get_previous_period_iso_range() : array |
| 124 | { |
| 125 | return [$this->date_range->previous_period()->iso_start(), $this->date_range->previous_period()->iso_end()]; |
| 126 | } |
| 127 | protected function apply_record_filters(Builder $query) : void |
| 128 | { |
| 129 | $should_apply_record_filters = $this->using_logical_and_operator() || $this->only_filtering_by_record_columns(); |
| 130 | if (!$should_apply_record_filters) { |
| 131 | return; |
| 132 | } |
| 133 | if ($this->using_logical_or_operator()) { |
| 134 | $query->where(function (Builder $query) { |
| 135 | foreach ($this->filters as $index => $filter) { |
| 136 | $filter->apply_to_query($query, \IAWP\Rows\Filter::$RECORD_FILTER, $index > 0); |
| 137 | } |
| 138 | }); |
| 139 | return; |
| 140 | } |
| 141 | foreach ($this->filters as $filter) { |
| 142 | if ($filter->is_concrete_column()) { |
| 143 | $filter->apply_to_query($query, \IAWP\Rows\Filter::$RECORD_FILTER); |
| 144 | } |
| 145 | } |
| 146 | } |
| 147 | protected function can_order_and_limit_at_record_level() : bool |
| 148 | { |
| 149 | return $this->sort_configuration->the_column()->is_concrete_column() && $this->only_filtering_by_record_columns(); |
| 150 | } |
| 151 | protected function apply_aggregate_filters(Builder $query) : void |
| 152 | { |
| 153 | $should_apply_aggregate_filters = $this->using_logical_and_operator() || $this->using_logical_or_operator() && $this->only_filtering_by_aggregate_columns(); |
| 154 | if (!$should_apply_aggregate_filters) { |
| 155 | return; |
| 156 | } |
| 157 | if ($this->using_logical_or_operator()) { |
| 158 | $query->where(function (Builder $query) { |
| 159 | foreach ($this->filters as $index => $filter) { |
| 160 | $filter->apply_to_query($query, \IAWP\Rows\Filter::$AGGREGATE_FILTER, $index > 0); |
| 161 | } |
| 162 | }); |
| 163 | return; |
| 164 | } |
| 165 | foreach ($this->filters as $filter) { |
| 166 | if ($filter->is_calculated_column()) { |
| 167 | $filter->apply_to_query($query, \IAWP\Rows\Filter::$AGGREGATE_FILTER); |
| 168 | } |
| 169 | } |
| 170 | } |
| 171 | protected function apply_or_filters(Builder $query) : void |
| 172 | { |
| 173 | $query->where(function (Builder $query) { |
| 174 | foreach ($this->filters as $index => $filter) { |
| 175 | $filter->apply_to_query($query, \IAWP\Rows\Filter::$OUTER_FILTER, $index > 0); |
| 176 | } |
| 177 | }); |
| 178 | } |
| 179 | protected function using_logical_or_operator() : bool |
| 180 | { |
| 181 | return $this->filter_logic === 'or' && \count($this->filters) > 1; |
| 182 | } |
| 183 | protected function using_logical_and_operator() : bool |
| 184 | { |
| 185 | return !$this->using_logical_or_operator(); |
| 186 | } |
| 187 | protected function apply_order_and_limit(Builder $query, string $sort_column) : void |
| 188 | { |
| 189 | $query->when($this->sort_configuration->the_column()->is_nullable(), function (Builder $query) use($sort_column) { |
| 190 | $query->orderByRaw("CASE WHEN {$sort_column} IS NULL THEN 1 ELSE 0 END"); |
| 191 | })->orderBy($sort_column, $this->sort_configuration->direction())->orderBy($this->sort_tie_breaker_column())->when(\is_int($this->number_of_rows), function (Builder $query) { |
| 192 | $query->limit($this->number_of_rows); |
| 193 | }); |
| 194 | } |
| 195 | protected function session_statistics_query() : Builder |
| 196 | { |
| 197 | $orders_subquery = Illuminate_Builder::new()->selectRaw('views.session_id')->selectRaw('COUNT(*) as order_count')->selectRaw('IFNULL(SUM(orders.total), 0) as total')->selectRaw('IFNULL(SUM(orders.total_refunded), 0) as total_refunded')->selectRaw('IFNULL(SUM(orders.total_refunds), 0) as total_refunds')->from(Tables::orders() . " AS orders")->join(Tables::views() . " AS views", 'orders.initial_view_id', '=', 'views.id')->where('orders.is_included_in_analytics', '=', \true)->whereBetween('orders.created_at', $this->get_current_period_iso_range())->groupBy('views.session_id'); |
| 198 | $form_submissions_subquery = Illuminate_Builder::new()->select(['views.session_id'])->selectRaw('COUNT(*) as form_submissions')->tap(function (Builder $query) { |
| 199 | foreach (Form::get_forms() as $form) { |
| 200 | $query->selectRaw('SUM(submissions.form_id = ?) as ' . $form->submissions_column(), [$form->id()]); |
| 201 | } |
| 202 | })->from(Tables::form_submissions(), 'submissions')->leftJoin(Tables::views() . ' AS views', 'views.id', '=', 'submissions.view_id')->whereBetween('submissions.created_at', $this->get_current_period_iso_range())->groupBy('views.session_id'); |
| 203 | $views_subquery = Illuminate_Builder::new()->select('views.session_id')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT clicks.click_id) AS clicks')->from(Tables::views(), 'views')->leftJoin(Tables::clicks() . ' AS clicks', 'clicks.view_id', '=', 'views.id')->whereBetween('views.viewed_at', $this->get_current_period_iso_range())->groupBy('views.session_id'); |
| 204 | return Illuminate_Builder::new()->select('sessions.*')->selectRaw('views.views AS views')->selectRaw('views.clicks AS clicks')->selectRaw('IFNULL(order_stats.order_count, 0) AS wc_orders')->selectRaw('IFNULL(order_stats.total, 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('IFNULL(form_submissions.form_submissions, 0) AS form_submissions')->tap(function (Builder $query) { |
| 205 | foreach (Form::get_forms() as $form) { |
| 206 | $query->selectRaw("IFNULL(form_submissions.{$form->submissions_column()}, 0) AS {$form->submissions_column()}"); |
| 207 | } |
| 208 | })->from(Tables::sessions(), 'sessions')->joinSub($views_subquery, 'views', 'sessions.session_id', '=', 'views.session_id')->leftJoinSub($orders_subquery, 'order_stats', 'sessions.session_id', '=', 'order_stats.session_id')->leftJoinSub($form_submissions_subquery, 'form_submissions', 'sessions.session_id', '=', 'form_submissions.session_id')->tap(Query_Taps::tap_authored_content_check())->tap(Query_Taps::tap_related_to_examined_record($this->examiner_config))->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) { |
| 209 | $query->whereBetween('sessions.created_at', $this->get_current_period_iso_range()); |
| 210 | }); |
| 211 | } |
| 212 | } |
| 213 |