PluginProbe
MLSImport: IDX Plugin & MLS Plugin for Real Estate Listings / 7.1.1
MLSImport: IDX Plugin & MLS Plugin for Real Estate Listings v7.1.1
7.2.1 7.2 7.1.2 7.1.1 7.1 7.0.4 7.0.6 7.0.7 6.3.8 6.3.7 6.3.6 6.3.5 6.3.4 6.3.3 6.3.1 trunk 5.7.3 5.7.5 5.8.1 5.8.2 5.8.3 5.8.4 5.8.6 6.0.4 6.0.5 All 36 releases
mlsimport / includes / mlsimport-activity-log.php

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

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