PluginProbe
OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin / 0.9.0
OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin v0.9.0
1.1.10 1.1.9 1.1.8 1.1.7 1.1.6 1.1.5 1.1.4 1.1.3 1.1.2 1.1.1 1.1.0 1.0.1 1.0.0 0.9.8 0.9.7 0.9.6 0.9.4 0.9.5 0.9.3 0.9.2 0.9.1 0.9.0 0.8.9 0.8.8 0.8.7 All 34 releases
desktop-mode / includes / my-wordpress / user-footprint.php

user-footprint.php in OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin 0.9.0, at includes/my-wordpress/user-footprint.php

512 lines 15.9 KB
No matching file
Up and down to move Enter to open Esc to close
Raw Download Zip
1 <?php
2 /**
3 * Desktop Mode — My WordPress: per-user activity footprint endpoint.
4 *
5 * `GET /desktop-mode/v1/user-footprint/<id>` returns a deep activity
6 * footprint for one user: a year of day-by-day publishing counts
7 * (GitHub-style calendar heatmap), weekday and hour-of-day
8 * distribution (publishing rhythm), longest publishing streak, and
9 * a recent-events timeline (posts published + comments left, last
10 * 30). The right-click "View activity footprint" action in the My
11 * WordPress users folder paints from this single payload.
12 *
13 * Permission: any logged-in user (the dossier route already has
14 * the same gate). Sensitive fields (email, IP) are NOT returned
15 * from this endpoint — `user-stats.php` carries those for the
16 * preview pane, and the footprint focuses on activity patterns.
17 *
18 * Payload shape:
19 *
20 * {
21 * profile: { id, name, avatarUrl, link, roleLabels?, registered? },
22 * range: { from, to, days }, // YYYY-MM-DD bookends + day count
23 * daily: [ { date, posts, comments, updates } ], // length = range.days; missing days = 0
24 * weekday: [ 0..6 ], // post counts, Sunday-indexed
25 * hour: [ 0..23 ], // post counts, server-local hour
26 * streak: { longest, current, longestRange:{ from, to } },
27 * timeline:[ // 30 most recent activity rows
28 * { kind:'post'|'comment'|'post-update', date, title, link, status, postId?, type? }
29 * ],
30 * totals: { posts, pages, comments, updates, mostProlificMonth?:{ ym, n } }
31 * }
32 *
33 * Timeline row fields:
34 * - `kind` — discriminator: `'post'` (publish), `'comment'`, or
35 * `'post-update'` (revision rollup).
36 * - `type` — only set when `kind` is `'post'` or `'post-update'`.
37 * Carries the post's CPT slug (`'post'`, `'page'`, custom
38 * types) so the renderer can pick a Post-vs-Page icon
39 * without a second REST lookup.
40 *
41 * "Updates" are revisions saved by the user AFTER a post's original
42 * creation — i.e. the user opened an existing post and saved it
43 * again. The initial save (which WordPress also writes as a revision)
44 * is excluded so the per-day "updates" count doesn't double up with
45 * the per-day "posts" count.
46 *
47 * @package WPDesktopMode
48 * @since 0.20.0
49 */
50
51 defined( 'ABSPATH' ) || exit;
52
53 /**
54 * Register the route.
55 *
56 * @since 0.20.0
57 */
58 function desktop_mode_my_wordpress_register_user_footprint_route() {
59 register_rest_route(
60 'desktop-mode/v1',
61 '/user-footprint/(?P<id>\d+)',
62 array(
63 'methods' => WP_REST_Server::READABLE,
64 'callback' => 'desktop_mode_my_wordpress_user_footprint_callback',
65 'permission_callback' => static function () {
66 return is_user_logged_in();
67 },
68 'args' => array(
69 'id' => array(
70 'required' => true,
71 'type' => 'integer',
72 'sanitize_callback' => 'absint',
73 ),
74 ),
75 )
76 );
77 }
78 add_action( 'rest_api_init', 'desktop_mode_my_wordpress_register_user_footprint_route' );
79
80 /**
81 * Aggregator callback. See the file docblock for the payload shape.
82 *
83 * @since 0.20.0
84 *
85 * @param WP_REST_Request $request REST request.
86 * @return array|WP_Error
87 */
88 function desktop_mode_my_wordpress_user_footprint_callback( $request ) {
89 global $wpdb;
90
91 $user_id = (int) $request->get_param( 'id' );
92 $user = get_userdata( $user_id );
93 if ( ! $user ) {
94 return new WP_Error(
95 'desktop_mode_user_not_found',
96 __( 'User not found.', 'desktop-mode' ),
97 array( 'status' => 404 )
98 );
99 }
100
101 $can_see_private = current_user_can( 'list_users' )
102 || ( get_current_user_id() === $user_id );
103
104 // ---- Profile (minimal — the dossier already returned the full one) ----
105 $profile = array(
106 'id' => (int) $user->ID,
107 'name' => (string) $user->display_name,
108 'avatarUrl' => get_avatar_url( $user->ID, array( 'size' => 128 ) ),
109 'link' => get_author_posts_url( $user->ID ),
110 );
111 if ( $can_see_private ) {
112 $role_labels = array();
113 if ( function_exists( 'wp_roles' ) ) {
114 $wp_roles = wp_roles();
115 foreach ( (array) $user->roles as $slug ) {
116 $role_labels[] = isset( $wp_roles->role_names[ $slug ] )
117 ? translate_user_role( $wp_roles->role_names[ $slug ] )
118 : $slug;
119 }
120 }
121 $profile['roleLabels'] = $role_labels;
122 if ( '' !== $user->user_registered ) {
123 $profile['registered'] = mysql2date( 'c', $user->user_registered, false );
124 }
125 }
126
127 // ---- Range: rolling 365-day window ending today (UTC bookends) -------
128 $days = 365;
129 $now = current_time( 'timestamp', true ); // UTC
130 $from_ts = strtotime( '-' . ( $days - 1 ) . ' days', $now );
131 $to_ts = $now;
132 $range = array(
133 'from' => gmdate( 'Y-m-d', $from_ts ),
134 'to' => gmdate( 'Y-m-d', $to_ts ),
135 'days' => $days,
136 );
137
138 // ---- Daily counts (posts published per day + comments LEFT per day) --
139 // Two queries (one for posts, one for comments), each grouped by
140 // `DATE(post_date_gmt)` / `DATE(comment_date_gmt)`. Then we
141 // densify to a full day-by-day array so the heatmap renders
142 // every cell, even empty ones.
143 $post_rows = $wpdb->get_results(
144 $wpdb->prepare(
145 "SELECT DATE(post_date_gmt) AS d, COUNT(*) AS n
146 FROM {$wpdb->posts}
147 WHERE post_author = %d
148 AND post_status = 'publish'
149 AND post_type IN ( 'post', 'page' )
150 AND post_date_gmt >= %s
151 GROUP BY d
152 ORDER BY d ASC",
153 $user_id,
154 gmdate( 'Y-m-d 00:00:00', $from_ts )
155 ),
156 ARRAY_A
157 );
158 $post_by_day = array();
159 foreach ( (array) $post_rows as $row ) {
160 $post_by_day[ (string) $row['d'] ] = (int) $row['n'];
161 }
162
163 $comment_rows = $wpdb->get_results(
164 $wpdb->prepare(
165 "SELECT DATE(comment_date_gmt) AS d, COUNT(*) AS n
166 FROM {$wpdb->comments}
167 WHERE user_id = %d
168 AND comment_approved = '1'
169 AND comment_date_gmt >= %s
170 GROUP BY d
171 ORDER BY d ASC",
172 $user_id,
173 gmdate( 'Y-m-d 00:00:00', $from_ts )
174 ),
175 ARRAY_A
176 );
177 $comment_by_day = array();
178 foreach ( (array) $comment_rows as $row ) {
179 $comment_by_day[ (string) $row['d'] ] = (int) $row['n'];
180 }
181
182 // Updates = revisions saved by this user, joined back to the
183 // parent post so we can skip the initial-save revision (where the
184 // revision's `post_date_gmt` equals the parent's `post_date_gmt`).
185 // `r.post_author` (not the parent's) tracks who hit Save, so
186 // updates an editor makes to someone else's post show up on the
187 // editor's footprint — same shape GitHub's contribution graph
188 // uses for commits across repos you don't own.
189 $update_rows = $wpdb->get_results(
190 $wpdb->prepare(
191 "SELECT DATE(r.post_date_gmt) AS d, COUNT(*) AS n
192 FROM {$wpdb->posts} r
193 INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
194 WHERE r.post_author = %d
195 AND r.post_type = 'revision'
196 AND r.post_status = 'inherit'
197 AND r.post_date_gmt > p.post_date_gmt
198 AND r.post_date_gmt >= %s
199 GROUP BY d
200 ORDER BY d ASC",
201 $user_id,
202 gmdate( 'Y-m-d 00:00:00', $from_ts )
203 ),
204 ARRAY_A
205 );
206 $update_by_day = array();
207 foreach ( (array) $update_rows as $row ) {
208 $update_by_day[ (string) $row['d'] ] = (int) $row['n'];
209 }
210
211 $daily = array();
212 for ( $i = 0; $i < $days; $i += 1 ) {
213 $ts = strtotime( '+' . $i . ' days', $from_ts );
214 $date = gmdate( 'Y-m-d', $ts );
215 $daily[] = array(
216 'date' => $date,
217 'posts' => isset( $post_by_day[ $date ] ) ? $post_by_day[ $date ] : 0,
218 'comments' => isset( $comment_by_day[ $date ] ) ? $comment_by_day[ $date ] : 0,
219 'updates' => isset( $update_by_day[ $date ] ) ? $update_by_day[ $date ] : 0,
220 );
221 }
222
223 // ---- Weekday distribution (Sunday-indexed) ---------------------------
224 // `DAYOFWEEK` returns 1=Sunday through 7=Saturday in MySQL.
225 $weekday_rows = $wpdb->get_results(
226 $wpdb->prepare(
227 "SELECT DAYOFWEEK(post_date_gmt) AS dow, COUNT(*) AS n
228 FROM {$wpdb->posts}
229 WHERE post_author = %d
230 AND post_status = 'publish'
231 AND post_type IN ( 'post', 'page' )
232 GROUP BY dow",
233 $user_id
234 ),
235 ARRAY_A
236 );
237 $weekday = array( 0, 0, 0, 0, 0, 0, 0 );
238 foreach ( (array) $weekday_rows as $row ) {
239 $dow = (int) $row['dow'];
240 if ( $dow >= 1 && $dow <= 7 ) {
241 $weekday[ $dow - 1 ] = (int) $row['n'];
242 }
243 }
244
245 // ---- Hour-of-day distribution (0..23, site timezone) -----------------
246 // `post_date` is already in site timezone — that's the timestamp
247 // the author saw when they hit Publish. Using GMT here would shift
248 // the bars by the offset and feel wrong to anyone in a non-UTC tz.
249 $hour_rows = $wpdb->get_results(
250 $wpdb->prepare(
251 "SELECT HOUR(post_date) AS h, COUNT(*) AS n
252 FROM {$wpdb->posts}
253 WHERE post_author = %d
254 AND post_status = 'publish'
255 AND post_type IN ( 'post', 'page' )
256 GROUP BY h",
257 $user_id
258 ),
259 ARRAY_A
260 );
261 $hour = array_fill( 0, 24, 0 );
262 foreach ( (array) $hour_rows as $row ) {
263 $h = (int) $row['h'];
264 if ( $h >= 0 && $h <= 23 ) {
265 $hour[ $h ] = (int) $row['n'];
266 }
267 }
268
269 // ---- Streak (longest consecutive run of days with ≥1 post over the
270 // 365-day window; current run ending today). ------------------------
271 $longest = 0;
272 $current = 0;
273 $longest_run = 0;
274 $longest_from = '';
275 $longest_to = '';
276 $run_start = '';
277 $today_str = $range['to'];
278 $prev_day_active = false;
279
280 // "Active" = published a post, left a comment, or saved a revision.
281 // Pre-0.8.7 this only counted publish days, so an editor doing
282 // daily updates without new posts had a "0 day" streak — wrong
283 // flavour of GitHub-style for a CMS where most work is editing.
284 $is_active = static function ( $entry ) {
285 return $entry['posts'] > 0
286 || ( isset( $entry['updates'] ) && $entry['updates'] > 0 )
287 || ( isset( $entry['comments'] ) && $entry['comments'] > 0 );
288 };
289 foreach ( $daily as $entry ) {
290 if ( $is_active( $entry ) ) {
291 if ( ! $prev_day_active ) {
292 $run_start = $entry['date'];
293 }
294 $longest_run += 1;
295 if ( $longest_run > $longest ) {
296 $longest = $longest_run;
297 $longest_from = $run_start;
298 $longest_to = $entry['date'];
299 }
300 $prev_day_active = true;
301 } else {
302 $longest_run = 0;
303 $prev_day_active = false;
304 }
305 }
306 // Current streak — walk backward from today.
307 for ( $i = count( $daily ) - 1; $i >= 0; $i -= 1 ) {
308 if ( $is_active( $daily[ $i ] ) ) {
309 $current += 1;
310 } else {
311 break;
312 }
313 }
314 $streak = array(
315 'longest' => $longest,
316 'current' => $current,
317 'longestRange' => array(
318 'from' => $longest_from,
319 'to' => $longest_to,
320 ),
321 );
322
323 // ---- Timeline: 30 most recent posts + comments, interleaved by date -
324 // One query per kind, then merge + sort + slice in PHP. Smaller and
325 // simpler than a SQL `UNION ALL`, and each branch already has the
326 // right index.
327 $timeline_posts = $wpdb->get_results(
328 $wpdb->prepare(
329 "SELECT ID, post_title, post_status, post_date_gmt, post_type
330 FROM {$wpdb->posts}
331 WHERE post_author = %d
332 AND post_type IN ( 'post', 'page' )
333 AND post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )
334 ORDER BY post_date_gmt DESC
335 LIMIT 30",
336 $user_id
337 ),
338 ARRAY_A
339 );
340 $timeline_comments = $wpdb->get_results(
341 $wpdb->prepare(
342 "SELECT c.comment_ID, c.comment_post_ID, c.comment_date_gmt, c.comment_approved,
343 p.post_title
344 FROM {$wpdb->comments} c
345 LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
346 WHERE c.user_id = %d
347 AND c.comment_approved = '1'
348 ORDER BY c.comment_date_gmt DESC
349 LIMIT 30",
350 $user_id
351 ),
352 ARRAY_A
353 );
354 // Recent updates — newest revision per parent post saved by this
355 // user. We collapse per-parent (`GROUP BY r.post_parent`) so a
356 // burst of saves on one post reads as one row in the activity
357 // list (otherwise an editor polishing a single article would push
358 // every other event off the screen). The MAX(r.post_date_gmt)
359 // surfaces the most recent save as the row's timestamp.
360 $timeline_updates = $wpdb->get_results(
361 $wpdb->prepare(
362 "SELECT r.post_parent AS parent_id, MAX(r.post_date_gmt) AS last_save, p.post_title, p.post_status, p.post_type
363 FROM {$wpdb->posts} r
364 INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
365 WHERE r.post_author = %d
366 AND r.post_type = 'revision'
367 AND r.post_status = 'inherit'
368 AND r.post_date_gmt > p.post_date_gmt
369 AND p.post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )
370 GROUP BY r.post_parent
371 ORDER BY last_save DESC
372 LIMIT 30",
373 $user_id
374 ),
375 ARRAY_A
376 );
377 $timeline = array();
378 foreach ( (array) $timeline_posts as $p ) {
379 $pid = (int) $p['ID'];
380 $timeline[] = array(
381 'kind' => 'post',
382 'date' => mysql2date( 'c', $p['post_date_gmt'], false ),
383 'title' => (string) $p['post_title'],
384 'status' => (string) $p['post_status'],
385 'postId' => $pid,
386 'link' => (string) get_permalink( $pid ),
387 'type' => (string) $p['post_type'],
388 );
389 }
390 foreach ( (array) $timeline_comments as $c ) {
391 $pid = (int) $c['comment_post_ID'];
392 $timeline[] = array(
393 'kind' => 'comment',
394 'date' => mysql2date( 'c', $c['comment_date_gmt'], false ),
395 'title' => (string) ( $c['post_title'] ?? '' ),
396 'status' => 'approved',
397 'postId' => $pid,
398 'link' => $pid ? (string) get_permalink( $pid ) : '',
399 );
400 }
401 foreach ( (array) $timeline_updates as $u ) {
402 $pid = (int) $u['parent_id'];
403 $timeline[] = array(
404 'kind' => 'post-update',
405 'date' => mysql2date( 'c', $u['last_save'], false ),
406 'title' => (string) $u['post_title'],
407 'status' => (string) $u['post_status'],
408 'postId' => $pid,
409 'link' => $pid ? (string) get_permalink( $pid ) : '',
410 'type' => (string) $u['post_type'],
411 );
412 }
413 usort(
414 $timeline,
415 static function ( $a, $b ) {
416 return strcmp( (string) $b['date'], (string) $a['date'] );
417 }
418 );
419 $timeline = array_slice( $timeline, 0, 30 );
420
421 // ---- Totals + most-prolific month -----------------------------------
422 $totals_posts = (int) $wpdb->get_var(
423 $wpdb->prepare(
424 "SELECT COUNT(*) FROM {$wpdb->posts}
425 WHERE post_author = %d
426 AND post_type = 'post'
427 AND post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )",
428 $user_id
429 )
430 );
431 $totals_pages = (int) $wpdb->get_var(
432 $wpdb->prepare(
433 "SELECT COUNT(*) FROM {$wpdb->posts}
434 WHERE post_author = %d
435 AND post_type = 'page'
436 AND post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )",
437 $user_id
438 )
439 );
440 $totals_comments = (int) $wpdb->get_var(
441 $wpdb->prepare(
442 "SELECT COUNT(*) FROM {$wpdb->comments}
443 WHERE user_id = %d AND comment_approved = '1'",
444 $user_id
445 )
446 );
447 // Lifetime updates = revisions this user saved after the initial
448 // creation of the parent post. Matches the per-day `updates`
449 // definition so the hero stat and heatmap rollups agree.
450 $totals_updates = (int) $wpdb->get_var(
451 $wpdb->prepare(
452 "SELECT COUNT(*) FROM {$wpdb->posts} r
453 INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
454 WHERE r.post_author = %d
455 AND r.post_type = 'revision'
456 AND r.post_status = 'inherit'
457 AND r.post_date_gmt > p.post_date_gmt",
458 $user_id
459 )
460 );
461 $month_row = $wpdb->get_row(
462 $wpdb->prepare(
463 "SELECT DATE_FORMAT(post_date_gmt, '%%Y-%%m') AS ym, COUNT(*) AS n
464 FROM {$wpdb->posts}
465 WHERE post_author = %d
466 AND post_status = 'publish'
467 AND post_type IN ( 'post', 'page' )
468 GROUP BY ym
469 ORDER BY n DESC
470 LIMIT 1",
471 $user_id
472 ),
473 ARRAY_A
474 );
475 $totals = array(
476 'posts' => $totals_posts,
477 'pages' => $totals_pages,
478 'comments' => $totals_comments,
479 'updates' => $totals_updates,
480 );
481 if ( $month_row && isset( $month_row['ym'] ) ) {
482 $totals['mostProlificMonth'] = array(
483 'ym' => (string) $month_row['ym'],
484 'n' => (int) $month_row['n'],
485 );
486 }
487
488 $payload = array(
489 'profile' => $profile,
490 'range' => $range,
491 'daily' => $daily,
492 'weekday' => $weekday,
493 'hour' => $hour,
494 'streak' => $streak,
495 'timeline' => $timeline,
496 'totals' => $totals,
497 );
498
499 /**
500 * Filter the per-user footprint payload before it's returned to
501 * the My WordPress folder window. Plugins can extend the timeline
502 * with their own activity rows, or replace the streak math with
503 * something domain-specific.
504 *
505 * @since 0.20.0
506 *
507 * @param array $payload Footprint payload.
508 * @param int $user_id Subject user id.
509 */
510 return apply_filters( 'desktop_mode_my_wordpress_user_footprint', $payload, $user_id );
511 }
512