mlsimport / includes / mlsimport-activity-log.php

mlsimport-activity-log.php in MLSImport: IDX Plugin & MLS Plugin for Real Estate Listings 6.3.8, at includes/mlsimport-activity-log.php

399 lines 14.4 KB

Raw Download Zip

1	<?php
2	if ( ! defined( 'ABSPATH' ) ) {
3	exit;
4	}
5
6	// B1 — Schema version. Bump to trigger dbDelta re-run via mlsimport_maybe_upgrade_activity_table().
7	define( 'MLSIMPORT_ACTIVITY_DB_VERSION', '1.0' );
8
9	/**
10	* Returns the prefixed activity table name.
11	* The ONLY source of the table name — never accept a table name from request input,
12	* never use as a $wpdb->prepare placeholder.
13	*
14	* @return string
15	*/
16	function mlsimport_activity_table_name(): string {
17	global $wpdb;
18	return $wpdb->prefix . 'mlsimport_activity';
19	}
20
21	/**
22	* Creates the activity table via dbDelta().
23	* Safe to call multiple times — dbDelta() is idempotent.
24	*
25	* @return void
26	*/
27	function mlsimport_create_activity_table(): void {
28	global $wpdb;
29
30	$table_name = mlsimport_activity_table_name();
31	$charset_collate = $wpdb->get_charset_collate();
32
33	$sql = "CREATE TABLE {$table_name} (
34	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
35	action VARCHAR(10) NOT NULL,
36	listing_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
37	listing_key VARCHAR(191) NOT NULL DEFAULT '',
38	listing_title VARCHAR(255) NOT NULL DEFAULT '',
39	listing_url VARCHAR(255) NOT NULL DEFAULT '',
40	import_item_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
41	import_item_title VARCHAR(255) NOT NULL DEFAULT '',
42	source VARCHAR(20) NOT NULL DEFAULT '',
43	created_at DATETIME NOT NULL,
44	PRIMARY KEY (id),
45	KEY created_at (created_at),
46	KEY import_item_id (import_item_id),
47	KEY action (action)
48	) {$charset_collate};";
49
50	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
51	dbDelta( $sql );
52	}
53
54	/**
55	* Runs the table migration if the installed schema version is outdated.
56	* Hooked to 'init' priority 1 so it runs on wp-cron requests too.
57	*
58	* @return void
59	*/
60	function mlsimport_maybe_upgrade_activity_table(): void {
61	if ( get_option( 'mlsimport_activity_db_version' ) !== MLSIMPORT_ACTIVITY_DB_VERSION ) {
62	mlsimport_create_activity_table();
63	update_option( 'mlsimport_activity_db_version', MLSIMPORT_ACTIVITY_DB_VERSION );
64	}
65	}
66	add_action( 'init', 'mlsimport_maybe_upgrade_activity_table', 1 );
67
68	/**
69	* Normalizes a raw source label to one of the canonical values.
70	* PURE PHP — no WordPress functions (unit-testable).
71	*
72	* Maps: normal->manual, cron->cron, manual->manual, import->import,
73	* reconciliation->reconciliation, anything else->other.
74	*
75	* @param string $raw
76	* @return string
77	*/
78	function mlsimport_normalize_activity_source( string $raw ): string {
79	$normalized = strtolower( trim( $raw ) );
80
81	$map = [
82	'normal' => 'manual',
83	'cron' => 'cron',
84	'manual' => 'manual',
85	'import' => 'import',
86	'reconciliation' => 'reconciliation',
87	];
88
89	return isset( $map[ $normalized ] ) ? $map[ $normalized ] : 'other';
90	}
91
92	/**
93	* Records ONE activity row for a listing add / edit / delete.
94	* Guards $action first — returns before any WP/DB call on invalid input.
95	* Snapshots title/URL/import-task title and caps every string to its column width.
96	*
97	* @param string $action 'added' \| 'edited' \| 'deleted'
98	* @param int $listing_id WP post ID of the property
99	* @param string $listing_key MLS ListingKey
100	* @param int $import_item_id mlsimport_item post ID (0 if unknown)
101	* @param string $source raw label, e.g. 'normal' \| 'cron' \| 'import' \| 'reconciliation'
102	* @return void
103	*/
104	function mlsimport_record_activity( string $action, int $listing_id, string $listing_key, int $import_item_id, string $source = '' ): void {
105	$valid_actions = [ 'added', 'edited', 'deleted' ];
106
107	if ( ! in_array( $action, $valid_actions, true ) ) {
108	return;
109	}
110
111	global $wpdb;
112
113	// Snapshot listing title — fall back to listing_key if empty.
114	$raw_title = get_the_title( $listing_id );
115	if ( '' === $raw_title ) {
116	$raw_title = $listing_key;
117	}
118	$listing_title = mb_substr( wp_strip_all_tags( $raw_title ), 0, 255 );
119
120	// Snapshot listing URL.
121	$permalink = get_permalink( $listing_id );
122	$listing_url = ( false !== $permalink ) ? mb_substr( $permalink, 0, 255 ) : '';
123
124	// Snapshot import task title.
125	$import_item_title = mb_substr( wp_strip_all_tags( get_the_title( $import_item_id ) ), 0, 255 );
126
127	// Normalize source.
128	$normalized_source = mlsimport_normalize_activity_source( $source );
129
130	// Cap listing_key to its column width.
131	$listing_key_capped = mb_substr( $listing_key, 0, 191 );
132
133	$data = [
134	'action' => $action,
135	'listing_id' => $listing_id,
136	'listing_key' => $listing_key_capped,
137	'listing_title' => $listing_title,
138	'listing_url' => $listing_url,
139	'import_item_id' => $import_item_id,
140	'import_item_title' => $import_item_title,
141	'source' => $normalized_source,
142	'created_at' => current_time( 'mysql' ),
143	];
144
145	$format = [
146	'%s', // action
147	'%d', // listing_id
148	'%s', // listing_key
149	'%s', // listing_title
150	'%s', // listing_url
151	'%d', // import_item_id
152	'%s', // import_item_title
153	'%s', // source
154	'%s', // created_at
155	];
156
157	$wpdb->insert( mlsimport_activity_table_name(), $data, $format );
158
159	// A new activity row makes the cached 24h banner summary stale. Drop the
160	// transient so the next banner view recomputes — keeps the banner current
161	// after every import without waiting out the 15-minute TTL.
162	delete_transient( 'mlsimport_activity_banner_summary' );
163	}
164
165	/**
166	* Aggregates activity rows from the last $hours.
167	* Query uses GROUP BY import_item_id, action — safe under ONLY_FULL_GROUP_BY.
168	* Table name is interpolated directly (never as a prepare placeholder).
169	*
170	* @param int $hours Window in hours. Default 24.
171	* @return array {
172	* 'totals' => ['added'=>int, 'edited'=>int, 'deleted'=>int],
173	* 'by_item' => [ import_item_id => ['title'=>string, 'added'=>int, 'edited'=>int, 'deleted'=>int] ]
174	* }
175	*/
176	function mlsimport_get_activity_summary( int $hours = 24 ): array {
177	global $wpdb;
178
179	$cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - $hours * HOUR_IN_SECONDS );
180
181	$table = mlsimport_activity_table_name();
182
183	// COUNT(DISTINCT listing_key), not COUNT(*): the same MLS listing re-imported
184	// (added→deleted→re-added over test runs) writes one activity row per event but
185	// keeps a stable listing_key, so the banner must count unique properties — not
186	// events — or it reports "50 added" for the same 10 listings.
187	$sql = $wpdb->prepare(
188	"SELECT import_item_id, action, COUNT(DISTINCT listing_key) AS cnt, MAX(import_item_title) AS import_item_title FROM {$table} WHERE created_at >= %s GROUP BY import_item_id, action",
189	$cutoff
190	);
191	$rows = $wpdb->get_results( $sql );
192
193	$totals = [ 'added' => 0, 'edited' => 0, 'deleted' => 0 ];
194	$by_item = [];
195
196	if ( ! empty( $rows ) ) {
197	foreach ( $rows as $row ) {
198	$item_id = (int) $row->import_item_id;
199	$act = $row->action;
200	$cnt = (int) $row->cnt;
201	$title = (string) $row->import_item_title;
202
203	if ( isset( $totals[ $act ] ) ) {
204	$totals[ $act ] += $cnt;
205	}
206
207	if ( ! isset( $by_item[ $item_id ] ) ) {
208	$by_item[ $item_id ] = [
209	'title' => $title,
210	'added' => 0,
211	'edited' => 0,
212	'deleted' => 0,
213	];
214	}
215
216	if ( isset( $by_item[ $item_id ][ $act ] ) ) {
217	$by_item[ $item_id ][ $act ] += $cnt;
218	}
219
220	// Keep most recent title if available.
221	if ( '' !== $title ) {
222	$by_item[ $item_id ]['title'] = $title;
223	}
224	}
225	}
226
227	return [
228	'totals' => $totals,
229	'by_item' => $by_item,
230	];
231	}
232
233	/**
234	* Returns a cached (15-minute transient) version of the 24-hour activity summary.
235	* The recorder does NOT bust this transient.
236	*
237	* @return array Same structure as mlsimport_get_activity_summary().
238	*/
239	function mlsimport_get_activity_banner_data(): array {
240	$cached = get_transient( 'mlsimport_activity_banner_summary' );
241	if ( false !== $cached ) {
242	return $cached;
243	}
244
245	$data = mlsimport_get_activity_summary( 24 );
246	set_transient( 'mlsimport_activity_banner_summary', $data, 15 * MINUTE_IN_SECONDS );
247
248	return $data;
249	}
250
251	/**
252	* Deletes activity rows older than 30 days.
253	* Hooked to 'mlsimport_reconciliation_event' (existing daily cron).
254	*
255	* @return void
256	*/
257	function mlsimport_prune_activity_log(): void {
258	global $wpdb;
259
260	$cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 30 * DAY_IN_SECONDS );
261
262	$table = mlsimport_activity_table_name();
263
264	$wpdb->query(
265	$wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s", $cutoff )
266	);
267	}
268	add_action( 'mlsimport_reconciliation_event', 'mlsimport_prune_activity_log' );
269
270	// ---------------------------------------------------------------------------
271	// B2 — Banner render + AJAX dismissal handler
272	// ---------------------------------------------------------------------------
273
274	/**
275	* Renders the dismissible sync-status banner on wp-admin pages.
276	* Shows only when:
277	* 1. current user is an administrator,
278	* 2. the last-24h activity totals are non-zero,
279	* 3. the per-user 'mlsimport_activity_banner_dismissed' meta is NOT today's date.
280	*
281	* Mirrors the inline-script pattern from mlsimport_handle_dismiss_protected_notice()
282	* in mlsimport.php.
283	*
284	* @return void
285	*/
286	function mlsimport_render_activity_banner(): void {
287
288	if ( ! current_user_can( 'administrator' ) ) {
289	return;
290	}
291
292	$data = mlsimport_get_activity_banner_data();
293	$totals = $data['totals'];
294	$by_item = $data['by_item'];
295
296	$today = current_time( 'Y-m-d' );
297	$dismissed = get_user_meta( get_current_user_id(), 'mlsimport_activity_banner_dismissed', true );
298	if ( $dismissed === $today ) {
299	return;
300	}
301
302	$nonce = wp_create_nonce( 'mlsimport_activity_banner' );
303	$history_url = esc_url( admin_url( 'admin.php?page=mlsimport_history' ) );
304
305	?>
306	<div class="notice notice-info is-dismissible mlsimport-activity-banner">
307	<div class="mlsimport-activity-banner__counts">
308	<span class="mlsimport-activity-banner__title">
309	<?php echo esc_html__( 'MLSImport activity', 'mlsimport' ); ?>
310	<span class="mlsimport-activity-banner__period"><?php echo esc_html__( 'last 24 hours', 'mlsimport' ); ?></span>
311	</span>
312	<span class="mlsimport-activity-banner__totals">
313	<?php
314	$total_added = (int) $totals['added'];
315	$total_edited = (int) $totals['edited'];
316	$total_deleted = (int) $totals['deleted'];
317	?>
318	<span class="mlsimport-activity-stat mlsimport-activity-stat--added"><strong><?php echo esc_html( number_format_i18n( $total_added ) ); ?></strong> <?php echo esc_html( _n( 'property', 'properties', $total_added, 'mlsimport' ) ); ?> <?php echo esc_html__( 'added', 'mlsimport' ); ?></span>
319	<span class="mlsimport-activity-stat mlsimport-activity-stat--edited"><strong><?php echo esc_html( number_format_i18n( $total_edited ) ); ?></strong> <?php echo esc_html( _n( 'property', 'properties', $total_edited, 'mlsimport' ) ); ?> <?php echo esc_html__( 'edited', 'mlsimport' ); ?></span>
320	<span class="mlsimport-activity-stat mlsimport-activity-stat--deleted"><strong><?php echo esc_html( number_format_i18n( $total_deleted ) ); ?></strong> <?php echo esc_html( _n( 'property', 'properties', $total_deleted, 'mlsimport' ) ); ?> <?php echo esc_html__( 'deleted', 'mlsimport' ); ?></span>
321	</span>
322	</div>
323	<?php
324	if ( ! empty( $by_item ) ) :
325	// Show only the 5 most active tasks (by total added + edited + deleted).
326	usort(
327	$by_item,
328	function ( $a, $b ) {
329	$a_total = (int) $a['added'] + (int) $a['edited'] + (int) $a['deleted'];
330	$b_total = (int) $b['added'] + (int) $b['edited'] + (int) $b['deleted'];
331	return $b_total <=> $a_total;
332	}
333	);
334	$by_item = array_slice( $by_item, 0, 5 );
335	?>
336	<ul class="mlsimport-activity-banner__breakdown">
337	<?php
338	foreach ( $by_item as $item ) :
339	$task_title = trim( (string) $item['title'] );
340	if ( '' === $task_title ) {
341	$task_title = __( 'Unknown import task', 'mlsimport' );
342	}
343	$added = (int) $item['added'];
344	$edited = (int) $item['edited'];
345	$deleted = (int) $item['deleted'];
346	?>
347	<li>
348	<span class="mlsimport-activity-banner__task"><?php echo esc_html__( 'Task Name:', 'mlsimport' ); ?> <?php echo esc_html( $task_title ); ?></span>
349	<span class="mlsimport-activity-banner__chips">
350	<span class="mlsimport-activity-chip mlsimport-activity-chip--added<?php echo 0 === $added ? ' is-zero' : ''; ?>"><?php echo esc_html( number_format_i18n( $added ) ); ?> <?php echo esc_html( _n( 'property', 'properties', $added, 'mlsimport' ) ); ?> <?php echo esc_html__( 'added', 'mlsimport' ); ?></span>
351	<span class="mlsimport-activity-chip mlsimport-activity-chip--edited<?php echo 0 === $edited ? ' is-zero' : ''; ?>"><?php echo esc_html( number_format_i18n( $edited ) ); ?> <?php echo esc_html( _n( 'property', 'properties', $edited, 'mlsimport' ) ); ?> <?php echo esc_html__( 'edited', 'mlsimport' ); ?></span>
352	<span class="mlsimport-activity-chip mlsimport-activity-chip--deleted<?php echo 0 === $deleted ? ' is-zero' : ''; ?>"><?php echo esc_html( number_format_i18n( $deleted ) ); ?> <?php echo esc_html( _n( 'property', 'properties', $deleted, 'mlsimport' ) ); ?> <?php echo esc_html__( 'deleted', 'mlsimport' ); ?></span>
353	</span>
354	</li>
355	<?php endforeach; ?>
356	</ul>
357	<?php endif; ?>
358	<p class="mlsimport-activity-banner__actions">
359	<a class="mlsimport-activity-banner__link" href="<?php echo $history_url; ?>"><?php echo esc_html__( 'View full 30-day history', 'mlsimport' ); ?> <span aria-hidden="true">→</span></a>
360	</p>
361	<span class="mlsimport-activity-banner-nonce" style="display:none;" data-nonce="<?php echo esc_attr( $nonce ); ?>"></span>
362	</div>
363	<script>
364	(function() {
365	var banner = document.querySelector('.mlsimport-activity-banner');
366	if ( ! banner ) { return; }
367	var dismissBtn = banner.querySelector('.notice-dismiss');
368	if ( ! dismissBtn ) { return; }
369	var nonce = banner.querySelector('.mlsimport-activity-banner-nonce').getAttribute('data-nonce');
370	dismissBtn.addEventListener('click', function() {
371	var xhr = new XMLHttpRequest();
372	xhr.open('POST', ajaxurl);
373	xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
374	xhr.send('action=mlsimport_dismiss_activity_banner&_ajax_nonce=' + encodeURIComponent(nonce));
375	});
376	})();
377	</script>
378	<?php
379	}
380	add_action( 'admin_notices', 'mlsimport_render_activity_banner' );
381
382	/**
383	* AJAX handler: persists banner dismissal for the current user for today.
384	*
385	* @return void
386	*/
387	function mlsimport_ajax_dismiss_activity_banner(): void {
388	check_ajax_referer( 'mlsimport_activity_banner' );
389
390	if ( ! current_user_can( 'administrator' ) ) {
391	wp_send_json_error();
392	}
393
394	update_user_meta( get_current_user_id(), 'mlsimport_activity_banner_dismissed', current_time( 'Y-m-d' ) );
395
396	wp_send_json_success();
397	}
398	add_action( 'wp_ajax_mlsimport_dismiss_activity_banner', 'mlsimport_ajax_dismiss_activity_banner' );
399