copy-the-code / includes / analytics / class-database.php

class-database.php in Copy Anything to Clipboard for WordPress – Copy Button, Copy Text & Copy Code 5.5.3, at includes/analytics/class-database.php

460 lines 12.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Analytics Database
4	*
5	* Single table (ctc_analytics) for copy analytics. Used by free plugin to store
6	* events; Pro reads from the same table for detailed/export. Created in free on upgrade.
7	*
8	* @package CTC
9	* @since 5.3.0
10	*/
11
12	namespace CTC\Analytics;
13
14	use CTC\Helper;
15
16	/**
17	* Database class.
18	*
19	* @since 5.3.0
20	*/
21	class Database {
22
23	/**
24	* Table name (without prefix).
25	*
26	* @var string
27	*/
28	const TABLE_NAME = 'ctc_analytics';
29
30	/**
31	* Instance
32	*
33	* @var Database\|null
34	*/
35	private static $instance = null;
36
37	/**
38	* Get instance.
39	*
40	* @return Database
41	*/
42	public static function get() {
43	if ( null === self::$instance ) {
44	self::$instance = new self();
45	}
46	return self::$instance;
47	}
48
49	/**
50	* Constructor.
51	*/
52	private function __construct() {
53	add_action( 'ctc_analytics_cleanup', [ $this, 'cleanup_scheduled' ] );
54	}
55
56	/**
57	* Get table name with WordPress prefix.
58	*
59	* @return string
60	*/
61	public function get_table_name() {
62	global $wpdb;
63	return $wpdb->prefix . self::TABLE_NAME;
64	}
65
66	/**
67	* Create database table.
68	* Called from Updater when upgrading to 5.3.0+ or on first install.
69	*
70	* @since 5.3.0
71	* @return void
72	*/
73	public static function create_table() {
74	global $wpdb;
75
76	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
77
78	$table_name = self::get()->get_table_name();
79	$charset_collate = $wpdb->get_charset_collate();
80
81	$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
82	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
83	rule_id bigint(20) unsigned DEFAULT NULL,
84	source varchar(50) NOT NULL DEFAULT 'global-injector',
85	success tinyint(1) NOT NULL DEFAULT 1,
86	failure_reason text DEFAULT NULL,
87	post_id bigint(20) unsigned DEFAULT NULL,
88	post_type varchar(20) DEFAULT NULL,
89	page_url text DEFAULT NULL,
90	device varchar(20) DEFAULT NULL,
91	browser varchar(50) DEFAULT NULL,
92	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
93	PRIMARY KEY (id),
94	KEY rule_id (rule_id),
95	KEY created_at (created_at),
96	KEY source (source),
97	KEY success (success)
98	) {$charset_collate};";
99
100	dbDelta( $sql );
101	}
102
103	/**
104	* Scheduled cleanup callback for analytics events.
105	*
106	* @since 5.4.0
107	* @return void
108	*/
109	public function cleanup_scheduled() {
110	$this->cleanup_old_events();
111	}
112
113	/**
114	* Get analytics retention period in days.
115	*
116	* @since 5.4.0
117	* @return int
118	*/
119	public function get_retention_days() {
120	$default_days = 395; // ~13 months.
121
122	/**
123	* Filter analytics retention period in days.
124	*
125	* @since 5.4.0
126	*
127	* @param int $days Number of days to retain analytics events.
128	*/
129	$days = (int) apply_filters( 'ctc/analytics/retention_days', $default_days );
130
131	if ( $days <= 0 ) {
132	return $default_days;
133	}
134
135	return $days;
136	}
137
138	/**
139	* Delete old analytics events beyond the retention period.
140	*
141	* Uses small batches to avoid long-running deletes on large tables.
142	*
143	* @since 5.4.0
144	* @return void
145	*/
146	public function cleanup_old_events() {
147	global $wpdb;
148
149	$table_name = $this->get_table_name();
150	$days = $this->get_retention_days();
151
152	// Compute cutoff date in UTC for the retention window.
153	$cutoff = Helper::mysql_date_ago( "-{$days} days" );
154
155	/**
156	* Filter the batch size used when cleaning up analytics events.
157	*
158	* @since 5.4.0
159	*
160	* @param int $batch_size Number of rows to delete per batch.
161	*/
162	$batch_size = (int) apply_filters( 'ctc/analytics/cleanup_batch_size', 500 );
163	if ( $batch_size <= 0 ) {
164	$batch_size = 500;
165	}
166
167	do {
168	$deleted = $wpdb->query(
169	$wpdb->prepare(
170	"DELETE FROM {$table_name} WHERE created_at < %s LIMIT %d",
171	$cutoff,
172	$batch_size
173	)
174	);
175	} while ( $deleted && $deleted === $batch_size );
176	}
177
178	/**
179	* Insert copy event.
180	*
181	* @since 5.3.0
182	* @param array $data Keys: rule_id, source, success, failure_reason, post_id, post_type, page_url, device, browser.
183	* @return int\|false Event ID on success, false on failure.
184	*/
185	public function insert_event( array $data ) {
186	global $wpdb;
187
188	$table_name = $this->get_table_name();
189
190	$rule_id = isset( $data['rule_id'] ) ? absint( $data['rule_id'] ) : null;
191	$source = isset( $data['source'] ) ? sanitize_text_field( $data['source'] ) : 'global-injector';
192	$source = $source ?: 'global-injector';
193	$success = isset( $data['success'] ) ? (bool) $data['success'] : true;
194	$failure_reason = isset( $data['failure_reason'] ) ? sanitize_text_field( $data['failure_reason'] ) : null;
195	$post_id = isset( $data['post_id'] ) ? absint( $data['post_id'] ) : null;
196	$post_type = isset( $data['post_type'] ) ? sanitize_text_field( $data['post_type'] ) : null;
197	$page_url = isset( $data['page_url'] ) ? esc_url_raw( $data['page_url'] ) : null;
198	$device = isset( $data['device'] ) ? sanitize_text_field( $data['device'] ) : null;
199	$browser = isset( $data['browser'] ) ? sanitize_text_field( $data['browser'] ) : null;
200
201	$row = [
202	'rule_id' => $rule_id,
203	'source' => $source,
204	'success' => $success ? 1 : 0,
205	'failure_reason' => $failure_reason,
206	'post_id' => $post_id,
207	'post_type' => $post_type,
208	'page_url' => $page_url,
209	'device' => $device,
210	'browser' => $browser,
211	'created_at' => Helper::mysql_now(),
212	];
213
214	$result = $wpdb->insert(
215	$table_name,
216	$row,
217	[ '%d', '%s', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s' ]
218	);
219
220	if ( false === $result ) {
221	return false;
222	}
223
224	return (int) $wpdb->insert_id;
225	}
226
227	/**
228	* Get total successful copy count in date range.
229	*
230	* @since 5.3.0
231	* @param string $date_from Start date (Y-m-d H:i:s).
232	* @param string $date_to End date (Y-m-d H:i:s).
233	* @param int\|null $rule_id Optional rule ID filter.
234	* @return int
235	*/
236	public function get_total_copies( $date_from, $date_to, $rule_id = null ) {
237	global $wpdb;
238
239	$table_name = $this->get_table_name();
240
241	if ( null !== $rule_id && $rule_id > 0 ) {
242	$sql = $wpdb->prepare(
243	"SELECT COUNT(*) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s AND source = 'global-injector' AND success = 1 AND rule_id = %d",
244	$date_from,
245	$date_to,
246	$rule_id
247	);
248	} else {
249	$sql = $wpdb->prepare(
250	"SELECT COUNT(*) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s AND success = 1",
251	$date_from,
252	$date_to
253	);
254	}
255
256	$count = $wpdb->get_var( $sql );
257
258	return (int) $count;
259	}
260
261	/**
262	* Get total successful copy count by source (e.g. global-injector, shortcode) in date range.
263	*
264	* @since 5.4.0
265	* @param string $date_from Start date (Y-m-d H:i:s).
266	* @param string $date_to End date (Y-m-d H:i:s).
267	* @return array<string, int> Map of source => count (e.g. [ 'global-injector' => 100, 'shortcode' => 50 ]).
268	*/
269	public function get_total_copies_by_source( $date_from, $date_to ) {
270	global $wpdb;
271
272	$table_name = $this->get_table_name();
273
274	$results = $wpdb->get_results(
275	$wpdb->prepare(
276	"SELECT source, COUNT(*) as cnt FROM {$table_name}
277	WHERE created_at >= %s AND created_at <= %s AND success = 1 AND source != ''
278	GROUP BY source",
279	$date_from,
280	$date_to
281	),
282	ARRAY_A
283	);
284
285	$by_source = [];
286	if ( is_array( $results ) ) {
287	foreach ( $results as $row ) {
288	$src = isset( $row['source'] ) ? sanitize_key( $row['source'] ) : 'global-injector';
289	$by_source[ $src ] = (int) ( $row['cnt'] ?? 0 );
290	}
291	}
292	return $by_source;
293	}
294
295	/**
296	* Get rule-specific stats (for main-rule-list activity column).
297	*
298	* @since 5.3.0
299	* @param int $rule_id Rule ID.
300	* @param string $date_from Start date (Y-m-d H:i:s).
301	* @param string $date_to End date (Y-m-d H:i:s).
302	* @return array Stats with total_copies, copies_24h, copies_7d, copies_30d, change_percent_24h.
303	*/
304	public function get_rule_stats( $rule_id, $date_from, $date_to ) {
305	$total_copies = $this->get_total_copies( $date_from, $date_to, $rule_id );
306
307	$now = Helper::mysql_now();
308	$date_24h_ago = Helper::mysql_date_ago( '-24 hours' );
309	$date_7d_ago = Helper::mysql_date_ago( '-7 days' );
310	$date_30d_ago = Helper::mysql_date_ago( '-30 days' );
311
312	$copies_24h = $this->get_total_copies( $date_24h_ago, $now, $rule_id );
313	$copies_7d = $this->get_total_copies( $date_7d_ago, $now, $rule_id );
314	$copies_30d = $this->get_total_copies( $date_30d_ago, $now, $rule_id );
315
316	$date_48h_ago = Helper::mysql_date_ago( '-48 hours' );
317	$previous_24h_count = $this->get_total_copies( $date_48h_ago, $date_24h_ago, $rule_id );
318	$change_percent_24h = $this->calculate_change_percent( $copies_24h, $previous_24h_count );
319
320	return [
321	'rule_id' => $rule_id,
322	'total_copies' => $total_copies,
323	'copies_24h' => $copies_24h,
324	'copies_7d' => $copies_7d,
325	'copies_30d' => $copies_30d,
326	'change_percent_24h' => $change_percent_24h,
327	];
328	}
329
330	/**
331	* Calculate percentage change between two counts.
332	*
333	* @since 5.3.0
334	* @param int $current Current period count.
335	* @param int $previous Previous period count.
336	* @return float
337	*/
338	public function calculate_change_percent( $current, $previous ) {
339	if ( 0 === (int) $previous ) {
340	return $current > 0 ? 100.0 : 0.0;
341	}
342	return round( ( ( (int) $current - (int) $previous ) / (int) $previous ) * 100, 1 );
343	}
344
345	/**
346	* Get activity trends (timeline data).
347	*
348	* @since 5.3.0
349	* @param string $date_from Start date (Y-m-d H:i:s).
350	* @param string $date_to End date (Y-m-d H:i:s).
351	* @param string $group_by Group by 'hour' or 'day'.
352	* @return array Array of { date, count } objects.
353	*/
354	public function get_trends( $date_from, $date_to, $group_by = 'hour' ) {
355	global $wpdb;
356
357	$table_name = $this->get_table_name();
358
359	$date_format = 'hour' === $group_by ? '%Y-%m-%d %H:00:00' : '%Y-%m-%d 00:00:00';
360
361	$results = $wpdb->get_results(
362	$wpdb->prepare(
363	"SELECT
364	DATE_FORMAT(created_at, %s) as date,
365	COUNT(*) as count
366	FROM {$table_name}
367	WHERE created_at >= %s
368	AND created_at <= %s
369	AND source = 'global-injector'
370	AND success = 1
371	GROUP BY DATE_FORMAT(created_at, %s)
372	ORDER BY date ASC",
373	$date_format,
374	$date_from,
375	$date_to,
376	$date_format
377	),
378	ARRAY_A
379	);
380
381	return $results ?? [];
382	}
383
384	/**
385	* Get top rules by copy count.
386	*
387	* @since 5.3.0
388	* @param int $limit Number of rules to return.
389	* @param string $date_from Start date (Y-m-d H:i:s).
390	* @param string $date_to End date (Y-m-d H:i:s).
391	* @return array Array of { rule_id, count } objects.
392	*/
393	public function get_top_rules( $limit = 10, $date_from = null, $date_to = null ) {
394	global $wpdb;
395
396	$table_name = $this->get_table_name();
397	$limit = absint( $limit );
398	$base_where = "source = 'global-injector' AND success = 1 AND rule_id IS NOT NULL";
399
400	if ( null !== $date_from && null !== $date_to ) {
401	$sql = $wpdb->prepare(
402	"SELECT rule_id, COUNT(*) as count FROM {$table_name} WHERE {$base_where} AND created_at >= %s AND created_at <= %s GROUP BY rule_id ORDER BY count DESC LIMIT %d",
403	$date_from,
404	$date_to,
405	$limit
406	);
407	} elseif ( null !== $date_from ) {
408	$sql = $wpdb->prepare(
409	"SELECT rule_id, COUNT(*) as count FROM {$table_name} WHERE {$base_where} AND created_at >= %s GROUP BY rule_id ORDER BY count DESC LIMIT %d",
410	$date_from,
411	$limit
412	);
413	} elseif ( null !== $date_to ) {
414	$sql = $wpdb->prepare(
415	"SELECT rule_id, COUNT(*) as count FROM {$table_name} WHERE {$base_where} AND created_at <= %s GROUP BY rule_id ORDER BY count DESC LIMIT %d",
416	$date_to,
417	$limit
418	);
419	} else {
420	$sql = $wpdb->prepare(
421	"SELECT rule_id, COUNT(*) as count FROM {$table_name} WHERE {$base_where} GROUP BY rule_id ORDER BY count DESC LIMIT %d",
422	$limit
423	);
424	}
425
426	$results = $wpdb->get_results( $sql, ARRAY_A );
427
428	return $results ?? [];
429	}
430
431	/**
432	* Get active rules count (rules with at least one copy event).
433	*
434	* @since 5.3.0
435	* @param string $date_from Start date (Y-m-d H:i:s).
436	* @param string $date_to End date (Y-m-d H:i:s).
437	* @return int Count of unique rules.
438	*/
439	public function get_active_rules_count( $date_from, $date_to ) {
440	global $wpdb;
441
442	$table_name = $this->get_table_name();
443
444	$count = $wpdb->get_var(
445	$wpdb->prepare(
446	"SELECT COUNT(DISTINCT rule_id)
447	FROM {$table_name}
448	WHERE created_at >= %s
449	AND created_at <= %s
450	AND source = 'global-injector'
451	AND success = 1",
452	$date_from,
453	$date_to
454	)
455	);
456
457	return (int) $count;
458	}
459	}
460