Intervals
5 months ago
Campaign_Landing_Page_Statistics.php
9 months ago
Campaign_Statistics.php
2 years ago
Campaign_UTM_Campaign_Statistics.php
9 months ago
Campaign_UTM_Medium_Statistics.php
9 months ago
Campaign_UTM_Source_Statistics.php
9 months ago
City_Statistics.php
2 years ago
Click_Statistics.php
5 months ago
Country_Statistics.php
2 years ago
Device_Browser_Statistics.php
2 years ago
Device_OS_Statistics.php
2 years ago
Device_Type_Statistics.php
2 years ago
Journey_Statistics.php
6 months ago
Page_Statistics.php
2 years ago
Referrer_Statistics.php
2 years ago
Referrer_Type_Statistics.php
9 months ago
Statistic.php
9 months ago
Statistics.php
3 months ago
Click_Statistics.php
53 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Statistics; |
| 4 | |
| 5 | use IAWP\Date_Range\Date_Range; |
| 6 | use IAWP\Illuminate_Builder; |
| 7 | use IAWP\Query_Taps; |
| 8 | use IAWP\Rows\Rows; |
| 9 | use IAWP\Utils\Timezone; |
| 10 | use IAWPSCOPED\Illuminate\Database\Query\Builder; |
| 11 | /** @internal */ |
| 12 | class Click_Statistics extends \IAWP\Statistics\Statistics |
| 13 | { |
| 14 | public function total_number_of_rows() : ?int |
| 15 | { |
| 16 | $query = Illuminate_Builder::new()->selectRaw('COUNT(DISTINCT links.id) AS total_table_rows')->from($this->tables::links(), 'links')->leftJoin($this->tables::link_rules() . ' AS link_rules', 'link_rules.link_rule_id', '=', 'links.link_rule_id')->leftJoin($this->tables::click_targets() . ' AS click_targets', 'click_targets.click_target_id', '=', 'links.click_target_id')->leftJoin($this->tables::clicked_links() . ' AS clicked_links', 'clicked_links.link_id', '=', 'links.id')->leftJoin($this->tables::clicks() . ' AS clicks', 'clicks.click_id', '=', 'clicked_links.click_id')->leftJoin($this->tables::views() . ' AS views', 'views.id', '=', 'clicks.view_id')->whereBetween('clicks.created_at', [$this->date_range->iso_start(), $this->date_range->iso_end()])->when(!\is_null($this->rows), function (Builder $query) { |
| 17 | $this->rows->attach_filters($query); |
| 18 | })->tap(Query_Taps::tap_authored_content_for_clicks()); |
| 19 | return $query->value('total_table_rows'); |
| 20 | } |
| 21 | protected function make_statistic_instances() : array |
| 22 | { |
| 23 | return [$this->make_statistic(['id' => 'clicks', 'name' => \__('Clicks', 'independent-analytics'), 'plugin_group' => 'general']), $this->make_statistic(['id' => 'visitors', 'name' => \__('Visitors', 'independent-analytics'), 'plugin_group' => 'general', 'is_invisible' => \true]), $this->make_statistic(['id' => 'views', 'name' => \__('Views', 'independent-analytics'), 'plugin_group' => 'general', 'is_invisible' => \true]), $this->make_statistic(['id' => 'sessions', 'name' => \__('Sessions', 'independent-analytics'), 'plugin_group' => 'general', 'is_invisible' => \true])]; |
| 24 | } |
| 25 | protected function query(Date_Range $range, ?Rows $rows = null, bool $is_grouped_by_date_interval = \false) |
| 26 | { |
| 27 | $utc_offset = Timezone::utc_offset(); |
| 28 | $site_offset = Timezone::site_offset(); |
| 29 | $query = Illuminate_Builder::new()->selectRaw('COUNT(DISTINCT clicks.click_id) AS clicks')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->from($this->tables::clicks(), 'clicks')->leftJoin($this->tables::clicked_links() . ' AS clicked_links', 'clicked_links.click_id', '=', 'clicks.click_id')->leftJoin($this->tables::links() . ' AS links', 'links.id', '=', 'clicked_links.link_id')->leftJoin($this->tables::link_rules() . ' AS link_rules', 'link_rules.link_rule_id', '=', 'links.link_rule_id')->leftJoin($this->tables::click_targets() . ' AS click_targets', 'click_targets.click_target_id', '=', 'links.click_target_id')->leftJoin($this->tables::views() . ' AS views', 'views.id', '=', 'clicks.view_id')->leftJoin($this->tables::sessions() . ' AS sessions', 'sessions.session_id', '=', 'views.session_id')->when(!\is_null($rows), function (Builder $query) use($rows) { |
| 30 | $rows->attach_filters($query); |
| 31 | })->tap(Query_Taps::tap_authored_content_for_clicks())->whereBetween('clicks.created_at', [$range->iso_start(), $range->iso_end()])->when($is_grouped_by_date_interval, function (Builder $query) use($utc_offset, $site_offset) { |
| 32 | if ($this->chart_interval->id() === 'daily') { |
| 33 | $query->selectRaw("DATE(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}')) AS date"); |
| 34 | } elseif ($this->chart_interval->id() === 'monthly') { |
| 35 | $query->selectRaw("DATE_FORMAT(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-01 00:00:00') AS date"); |
| 36 | } elseif ($this->chart_interval->id() === 'weekly') { |
| 37 | $day_of_week = \IAWPSCOPED\iawp()->get_option('iawp_dow', 0) + 1; |
| 38 | $query->selectRaw("\n IF (\n DAYOFWEEK(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} < 0,\n DATE_FORMAT(SUBDATE(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} + 7), '%Y-%m-%d 00:00:00'),\n DATE_FORMAT(SUBDATE(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week}), '%Y-%m-%d 00:00:00')\n ) AS date\n "); |
| 39 | } else { |
| 40 | $query->selectRaw("DATE_FORMAT(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-%d %H:00:00') AS date"); |
| 41 | } |
| 42 | $query->groupByRaw("date"); |
| 43 | }); |
| 44 | $results = \array_map(function (object $statistic) : object { |
| 45 | return $this->clean_up_raw_statistic_row($statistic); |
| 46 | }, $query->get()->all()); |
| 47 | if (!$is_grouped_by_date_interval) { |
| 48 | return $results[0]; |
| 49 | } |
| 50 | return $results; |
| 51 | } |
| 52 | } |
| 53 |