Click_Statistics.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

independent-analytics / IAWP / Statistics / Click_Statistics.php

independent-analytics / IAWP / Statistics Last commit date

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 5 months ago

Click_Statistics.php

95 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_chart_with_aggregates(Date_Range $current_range, ?Rows $rows = null) : array
26	{
27	$previous_range = $current_range->previous_period();
28	$combined_range = [\min($previous_range->iso_start(), $current_range->iso_start()), \max($previous_range->iso_end(), $current_range->iso_end())];
29	$cur_start = $current_range->iso_start();
30	$cur_end = $current_range->iso_end();
31	$prev_start = $previous_range->iso_start();
32	$prev_end = $previous_range->iso_end();
33	$is_current = "clicks.created_at BETWEEN '{$cur_start}' AND '{$cur_end}'";
34	$is_previous = "clicks.created_at BETWEEN '{$prev_start}' AND '{$prev_end}'";
35	$query = Illuminate_Builder::new()->selectRaw("COUNT(DISTINCT CASE WHEN {$is_current} THEN clicks.click_id END) AS cur_clicks")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_current} THEN views.id END) AS cur_views")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_current} THEN sessions.session_id END) AS cur_sessions")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_current} THEN sessions.visitor_id END) AS cur_visitors")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_previous} THEN clicks.click_id END) AS prev_clicks")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_previous} THEN views.id END) AS prev_views")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_previous} THEN sessions.session_id END) AS prev_sessions")->selectRaw("COUNT(DISTINCT CASE WHEN {$is_previous} THEN sessions.visitor_id END) AS prev_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) {
36	$rows->attach_filters($query);
37	})->tap(Query_Taps::tap_authored_content_for_clicks())->whereBetween('clicks.created_at', $combined_range);
38	// Add date interval grouping using clicks.created_at (not sessions.created_at)
39	$this->add_click_date_interval_grouping($query);
40	$query->groupByRaw("date WITH ROLLUP");
41	$all_rows = $query->get()->all();
42	// Separate the rollup summary row (date IS NULL) from the per-date chart rows
43	$rollup_row = null;
44	$chart_rows = [];
45	foreach ($all_rows as $row) {
46	if (\is_null($row->date)) {
47	$rollup_row = $row;
48	} else {
49	$chart_rows[] = $row;
50	}
51	}
52	// Extract current/previous aggregates from the rollup row
53	$current = $this->extract_period_from_combined_row($rollup_row, 'cur_');
54	$previous = $this->extract_period_from_combined_row($rollup_row, 'prev_');
55	// Extract chart data from the per-date rows (current period only, strip cur_ prefix)
56	$chart = \array_map(function (object $row) : object {
57	$chart_row = $this->extract_period_from_combined_row($row, 'cur_');
58	$chart_row->date = $row->date;
59	return $this->clean_up_raw_statistic_row($chart_row);
60	}, $chart_rows);
61	return ['current' => $this->clean_up_raw_statistic_row($current), 'previous' => $this->clean_up_raw_statistic_row($previous), 'chart' => $chart];
62	}
63	protected function query(Date_Range $range, ?Rows $rows = null, bool $is_grouped_by_date_interval = \false)
64	{
65	$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) {
66	$rows->attach_filters($query);
67	})->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) {
68	$this->add_click_date_interval_grouping($query);
69	$query->groupByRaw("date");
70	});
71	$results = \array_map(function (object $statistic) : object {
72	return $this->clean_up_raw_statistic_row($statistic);
73	}, $query->get()->all());
74	if (!$is_grouped_by_date_interval) {
75	return $results[0];
76	}
77	return $results;
78	}
79	private function add_click_date_interval_grouping(Builder $query) : void
80	{
81	$utc_offset = Timezone::utc_offset();
82	$site_offset = Timezone::site_offset();
83	if ($this->chart_interval->id() === 'daily') {
84	$query->selectRaw("DATE(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}')) AS date");
85	} elseif ($this->chart_interval->id() === 'monthly') {
86	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-01 00:00:00') AS date");
87	} elseif ($this->chart_interval->id() === 'weekly') {
88	$day_of_week = \IAWPSCOPED\iawp()->get_option('iawp_dow', 0) + 1;
89	$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 ");
90	} else {
91	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(clicks.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-%d %H:00:00') AS date");
92	}
93	}
94	}
95