db.php — Independent Analytics – WordPress Analytics Plugin 1.10

independent-analytics / inc / db.php

independent-analytics / inc Last commit date

ajax 3 years ago databases 3 years ago migrations 3 years ago models 3 years ago queries 3 years ago utils 3 years ago chart.php 3 years ago chart_geo.php 3 years ago dashboard_widget.php 3 years ago db.php 3 years ago filters.php 3 years ago freemius.php 3 years ago independent_analytics.php 3 years ago known_referrers.php 3 years ago quick_stats.php 3 years ago rest_api.php 3 years ago settings.php 3 years ago table.php 3 years ago table_geo.php 3 years ago table_referrers.php 3 years ago table_views.php 3 years ago track_resource_changes.php 3 years ago view_counter.php 3 years ago

db.php

236 lines

1	<?php
2
3	namespace IAWP;
4
5	class DB
6	{
7	public function __construct()
8	{
9	}
10
11	private static function table_prefix()
12	{
13	global $wpdb;
14
15	return $wpdb->prefix . 'independent_analytics_';
16	}
17
18	public static function referrer_groups_table()
19	{
20	return self::table_prefix() . 'referrer_groups';
21	}
22
23	public static function referrers_table()
24	{
25	return self::table_prefix() . 'referrers';
26	}
27
28	public static function views_table()
29	{
30	return self::table_prefix() . 'views';
31	}
32
33	public static function visitors_table()
34	{
35	return self::table_prefix() . 'visitors';
36	}
37
38	public static function resources_table()
39	{
40	return self::table_prefix() . 'resources';
41	}
42
43	public function wipe_db()
44	{
45	delete_option('iawp_db_version');
46	delete_option('iawp_is_migrating');
47	delete_option('iawp_dark_mode');
48	delete_option('iawp_dow');
49	delete_option('iawp_track_authenticated_users');
50	delete_option('iawp_daily_salt');
51	delete_option('iawp_daily_salt_set');
52	Migration::create_or_migrate();
53	}
54
55	public function record_view($payload, $referrer_url, $visitor, $viewed_at = null)
56	{
57	global $wpdb;
58	$views_table = $this->views_table();
59	$view = [];
60
61	$view['page'] = $payload['page'];
62
63	if (isset($referrer_url)) {
64	$url = new URL($referrer_url);
65
66	if ($url->is_valid_url()) {
67	$referrer = $this->get_or_create_referrer($url->get_domain());
68	$view['referrer_id'] = $referrer->id;
69	}
70	}
71
72	$visitor = $this->get_or_create_visitor($visitor);
73	$view['visitor_id'] = $visitor->id;
74
75	$resource_payload = $payload;
76	unset($resource_payload['page']);
77	$resource = $this->get_or_create_resource($resource_payload);
78	$view['resource_id'] = $resource->id;
79
80	if (isset($viewed_at)) {
81	$view['viewed_at'] = $viewed_at;
82	} else {
83	$date = new \DateTime();
84	$view['viewed_at'] = $date->format('Y-m-d H:i:s');
85	}
86
87	try {
88	$wpdb->insert($views_table, $view);
89	} catch (Exception $e) {
90	}
91	}
92
93	private function get_or_create_resource($payload)
94	{
95	global $wpdb;
96	$resources_table = $this->resources_table();
97	$query = '';
98
99	switch ($payload['resource']) {
100	case 'singular':
101	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND singular_id = %d", $payload['resource'], $payload['singular_id']);
102	break;
103	case 'author_archive':
104	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND author_id = %d", $payload['resource'], $payload['author_id']);
105	break;
106	case 'date_archive':
107	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND date_archive = %s", $payload['resource'], $payload['date_archive']);
108	break;
109	case 'post_type_archive':
110	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND post_type = %s", $payload['resource'], $payload['post_type']);
111	break;
112	case 'term_archive':
113	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND term_id = %s", $payload['resource'], $payload['term_id']);
114	break;
115	case 'search':
116	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND search_query = %s", $payload['resource'], $payload['search_query']);
117	break;
118	case 'home':
119	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s ", $payload['resource']);
120	break;
121	case '404':
122	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND not_found_url = %s", $payload['resource'], $payload['not_found_url']);
123	break;
124	}
125
126	$resource = $wpdb->get_row($query);
127
128	if (is_null($resource)) {
129	$wpdb->insert($resources_table, $payload);
130	$resource = $wpdb->get_row($query);
131	}
132
133	// Todo - This probably shouldn't happen here... A call should be make to the page and then the page should
134	// know if it's a page type that needs to be cached or not.
135	switch ($resource->resource) {
136	case 'singular':
137	(new Page_Singular($resource))->update_cache();
138	break;
139	case 'author_archive':
140	(new Page_Author_Archive($resource))->update_cache();
141	break;
142	case 'post_type_archive':
143	(new Page_Post_Type_Archive($resource))->update_cache();
144	break;
145	case 'term_archive':
146	(new Page_Term_Archive($resource))->update_cache();
147	break;
148	}
149
150	return $resource;
151	}
152
153	// Why not use an index? Indexing a varchar(2048) isn't possible.
154	private function get_or_create_referrer($referrer_domain)
155	{
156	global $wpdb;
157	$referrers_table = $this->referrers_table();
158	$referrer_domain = trim($referrer_domain);
159
160	if (strlen($referrer_domain) == 0) {
161	return null;
162	}
163
164	$referrer = $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE domain = %s", $referrer_domain));
165
166	if (!is_null($referrer)) {
167	return $referrer;
168	}
169
170	$wpdb->insert($referrers_table, ['domain' => $referrer_domain]);
171
172	return $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE domain = %s", $referrer_domain));
173	}
174
175	private function get_or_create_visitor($visitor)
176	{
177	global $wpdb;
178	$visitors_table = self::visitors_table();
179
180	$existing_visitor = $wpdb->get_row($wpdb->prepare("
181	SELECT *
182	FROM $visitors_table
183	WHERE visitor_token = %s
184	", $visitor->token()));
185
186	// If there is an existing visitor
187	if (!is_null($existing_visitor)) {
188	// If the existing visitor doesn't have geo data
189	if (is_null($existing_visitor->country_code)) {
190	$wpdb->update(
191	$visitors_table,
192	[
193	'country_code' => $visitor->country_code(),
194	'city' => $visitor->city(),
195	'subdivision' => $visitor->subdivision(),
196	'country' => $visitor->country(),
197	'continent' => $visitor->continent(),
198	],
199	['visitor_token' => $visitor->token()],
200	[
201	'%s',
202	'%s',
203	'%s',
204	'%s',
205	'%s',
206	],
207	['%s']
208	);
209
210	return $wpdb->get_row($wpdb->prepare("
211	SELECT *
212	FROM $visitors_table
213	WHERE visitor_token = %s
214	", $visitor->token()));
215	}
216
217	return $existing_visitor;
218	}
219
220	$wpdb->insert($visitors_table, [
221	'visitor_token' => $visitor->token(),
222	'country_code' => $visitor->country_code(),
223	'city' => $visitor->city(),
224	'subdivision' => $visitor->subdivision(),
225	'country' => $visitor->country(),
226	'continent' => $visitor->continent(),
227	]);
228
229	return $wpdb->get_row($wpdb->prepare("
230	SELECT *
231	FROM $visitors_table
232	WHERE visitor_token = %s
233	", $visitor->token()));
234	}
235	}
236