class-emails-query.php — Post Views Counter trunk

post-views-counter / includes / class-emails-query.php

post-views-counter / includes Last commit date

class-emails-query.php

603 lines

1	<?php
2	// exit if accessed directly
3	if ( ! defined( 'ABSPATH' ) )
4	exit;
5
6	/**
7	* Post_Views_Counter_Emails_Query class.
8	*
9	* @class Post_Views_Counter_Emails_Query
10	*/
11	class Post_Views_Counter_Emails_Query {
12
13	/**
14	* @var Post_Views_Counter
15	*/
16	private $pvc;
17
18	/**
19	* Class constructor.
20	*
21	* @return void
22	*/
23	public function __construct() {
24	$this->pvc = Post_Views_Counter();
25	}
26
27	/**
28	* Get summary data for a supported cadence.
29	*
30	* @param string $summary_type
31	* @param array\|null $period
32	* @param array $args
33	* @return array
34	*/
35	public function get_summary_data( $summary_type = 'weekly', $period = null, $args = [] ) {
36	$summary_type = Post_Views_Counter_Emails_Period::normalize_summary_type_key( $summary_type );
37	$period = $this->normalize_period( $summary_type, $period );
38	$settings = $this->get_settings();
39	$query_args = $this->build_query_args( $args, $period, $settings );
40	$cache_key = $this->get_cache_key( $period, $query_args );
41	$data = wp_cache_get( $cache_key, 'post_views_counter' );
42
43	if ( $data === false ) {
44	$data = $this->build_summary_data( $period, $query_args );
45	$ttl = absint( apply_filters( 'pvc_email_summary_cache_ttl', 300, $query_args, $period, $this ) );
46
47	wp_cache_set( $cache_key, $data, 'post_views_counter', $ttl );
48	}
49
50	return apply_filters( 'pvc_email_summary_data', $data, $period, $query_args, $this );
51	}
52
53	/**
54	* Get weekly summary data.
55	*
56	* @param array\|null $period
57	* @param array $args
58	* @return array
59	*/
60	public function get_weekly_summary_data( $period = null, $args = [] ) {
61	return $this->get_summary_data( 'weekly', $period, $args );
62	}
63
64	/**
65	* Normalize the requested period.
66	*
67	* @param array\|null $period
68	* @return array
69	*/
70	private function normalize_period( $summary_type = 'weekly', $period = null ) {
71	$default_period = ( new Post_Views_Counter_Emails_Period() )->get_period( $summary_type );
72
73	if ( ! is_array( $period ) )
74	return $default_period;
75
76	return array_merge( $default_period, $period );
77	}
78
79	/**
80	* Get normalized email settings.
81	*
82	* @return array
83	*/
84	private function get_settings() {
85	$stored = isset( $this->pvc->options['emails'] ) && is_array( $this->pvc->options['emails'] ) ? $this->pvc->options['emails'] : [];
86
87	return array_merge( $this->pvc->defaults['emails'], $stored );
88	}
89
90	/**
91	* Build query arguments.
92	*
93	* @param array $args
94	* @param array $period
95	* @param array $settings
96	* @return array
97	*/
98	private function build_query_args( $args, $period, $settings ) {
99	$defaults = [
100	'post_types' => $this->get_post_types( $settings ),
101	'limit' => min( 10, max( 3, (int) $settings['max_top_items'] ) ),
102	'threshold' => max( 0, (int) $settings['min_views_threshold'] ),
103	'send_empty_reports' => ! empty( $settings['send_empty_reports'] )
104	];
105
106	if ( ! is_array( $args ) )
107	$args = [];
108
109	$query_args = wp_parse_args( $args, $defaults );
110	$query_args = apply_filters( 'pvc_email_summary_query_args', $query_args, $period, $settings, $this );
111	$query_args['post_types'] = $this->sanitize_post_types( isset( $query_args['post_types'] ) ? $query_args['post_types'] : [] );
112	$query_args['limit'] = min( 10, max( 3, (int) ( isset( $query_args['limit'] ) ? $query_args['limit'] : 5 ) ) );
113	$query_args['threshold'] = max( 0, (int) ( isset( $query_args['threshold'] ) ? $query_args['threshold'] : 25 ) );
114	$query_args['send_empty_reports'] = ! empty( $query_args['send_empty_reports'] );
115
116	return $query_args;
117	}
118
119	/**
120	* Resolve post types from settings.
121	*
122	* @param array $settings
123	* @return array
124	*/
125	private function get_post_types( $settings ) {
126	$post_types = [];
127
128	if ( ! empty( $settings['include_post_types'] ) && is_array( $settings['include_post_types'] ) )
129	$post_types = $settings['include_post_types'];
130	elseif ( isset( $this->pvc->options['general']['post_types_count'] ) && is_array( $this->pvc->options['general']['post_types_count'] ) )
131	$post_types = $this->pvc->options['general']['post_types_count'];
132
133	return $this->sanitize_post_types( $post_types );
134	}
135
136	/**
137	* Sanitize post type slugs.
138	*
139	* @param array\|string $post_types
140	* @return array
141	*/
142	private function sanitize_post_types( $post_types ) {
143	if ( is_string( $post_types ) )
144	$post_types = [ $post_types ];
145
146	if ( ! is_array( $post_types ) )
147	return [];
148
149	$sanitized = [];
150
151	foreach ( $post_types as $post_type ) {
152	$post_type = sanitize_key( $post_type );
153
154	if ( $post_type !== '' && post_type_exists( $post_type ) )
155	$sanitized[] = $post_type;
156	}
157
158	return array_values( array_unique( $sanitized ) );
159	}
160
161	/**
162	* Build the cache key for summary data.
163	*
164	* @param array $period
165	* @param array $query_args
166	* @return string
167	*/
168	private function get_cache_key( $period, $query_args ) {
169	$cadence = Post_Views_Counter_Emails_Period::normalize_summary_type_key( isset( $period['cadence'] ) ? $period['cadence'] : 'weekly' );
170
171	$cache_context = [
172	'blog_id' => get_current_blog_id(),
173	'cadence' => $cadence,
174	'start_period' => $period['start_period'],
175	'end_period' => $period['end_period'],
176	'comparison_start_period' => $period['comparison_start_period'],
177	'comparison_end_period' => $period['comparison_end_period'],
178	'post_types' => $query_args['post_types'],
179	'limit' => $query_args['limit'],
180	'threshold' => $query_args['threshold'],
181	'send_empty_reports' => $query_args['send_empty_reports']
182	];
183
184	return 'pvc_email_summary_query_' . md5( serialize( $cache_context ) );
185	}
186
187	/**
188	* Build normalized summary data.
189	*
190	* @param array $period
191	* @param array $query_args
192	* @return array
193	*/
194	private function build_summary_data( $period, $query_args ) {
195	$cadence = Post_Views_Counter_Emails_Period::normalize_summary_type_key( isset( $period['cadence'] ) ? $period['cadence'] : 'weekly' );
196
197	if ( empty( $query_args['post_types'] ) )
198	return $this->build_empty_summary_data( $period, $query_args, 'no_post_types' );
199
200	$current = $this->query_period_overview( $period['start_period'], $period['end_period'], $query_args['post_types'] );
201	$previous = $this->query_period_overview( $period['comparison_start_period'], $period['comparison_end_period'], $query_args['post_types'] );
202	$top_items = $this->query_top_content( $period['start_period'], $period['end_period'], $query_args['post_types'], $query_args['limit'] );
203	$previous_views = [];
204
205	if ( ! empty( $top_items ) )
206	$previous_views = $this->query_previous_views_map( $period['comparison_start_period'], $period['comparison_end_period'], $query_args['post_types'], wp_list_pluck( $top_items, 'post_id' ) );
207
208	$overview_trend = $this->build_trend_data( $current['total_views'], $previous['total_views'] );
209	$threshold_met = (int) $current['total_views'] >= (int) $query_args['threshold'];
210	$should_send = $threshold_met \|\| $query_args['send_empty_reports'];
211	$top_content = $this->build_top_content_data( $top_items, $previous_views );
212
213	$overview = [
214	'total_views' => (int) $current['total_views'],
215	'previous_total_views' => (int) $previous['total_views'],
216	'views_change' => $overview_trend['views_change'],
217	'views_change_percent' => $overview_trend['views_change_percent'],
218	'trend_reliable' => $overview_trend['trend_reliable'],
219	'trend_status' => $overview_trend['trend_status'],
220	'threshold_met' => $threshold_met,
221	'should_send' => $should_send,
222	'viewed_content_count' => (int) $current['viewed_content_count']
223	];
224
225	return [
226	'type' => $cadence,
227	'cadence' => $cadence,
228	'period' => $period,
229	'query' => [
230	'post_types' => $query_args['post_types'],
231	'limit' => $query_args['limit'],
232	'threshold' => $query_args['threshold'],
233	'send_empty_reports' => $query_args['send_empty_reports']
234	],
235	'overview' => $overview,
236	'top_content' => $top_content,
237	'traffic_signals' => $this->build_traffic_signals_data( $overview, $top_content ),
238	'status' => $this->build_status_data( $overview, ! empty( $top_content ) )
239	];
240	}
241
242	/**
243	* Build an empty summary response.
244	*
245	* @param array $period
246	* @param array $query_args
247	* @param string $reason
248	* @return array
249	*/
250	private function build_empty_summary_data( $period, $query_args, $reason = 'no_data' ) {
251	$cadence = Post_Views_Counter_Emails_Period::normalize_summary_type_key( isset( $period['cadence'] ) ? $period['cadence'] : 'weekly' );
252	$should_send = $reason === 'no_post_types' ? false : $query_args['send_empty_reports'];
253
254	return [
255	'type' => $cadence,
256	'cadence' => $cadence,
257	'period' => $period,
258	'query' => [
259	'post_types' => $query_args['post_types'],
260	'limit' => $query_args['limit'],
261	'threshold' => $query_args['threshold'],
262	'send_empty_reports' => $query_args['send_empty_reports']
263	],
264	'overview' => [
265	'total_views' => 0,
266	'previous_total_views' => 0,
267	'views_change' => 0,
268	'views_change_percent' => null,
269	'trend_reliable' => false,
270	'trend_status' => 'neutral',
271	'threshold_met' => false,
272	'should_send' => $should_send,
273	'viewed_content_count' => 0
274	],
275	'top_content' => [],
276	'traffic_signals' => [
277	'state' => 'silence',
278	'post_id' => 0,
279	'message_key' => $reason === 'no_post_types' ? 'no_visible_post_types' : 'no_data'
280	],
281	'status' => [
282	'empty' => true,
283	'reason' => $reason
284	]
285	];
286	}
287
288	/**
289	* Query total views and viewed content count for a period.
290	*
291	* @global object $wpdb
292	* @param string\|int $start_period
293	* @param string\|int $end_period
294	* @param array $post_types
295	* @return array
296	*/
297	private function query_period_overview( $start_period, $end_period, $post_types ) {
298	global $wpdb;
299
300	$post_type_placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) );
301	$period_query = $this->get_period_query_parts( $start_period, $end_period );
302	$params = array_merge( [ 0 ], $period_query['params'], [ 'publish', '' ], $post_types );
303	$query = $wpdb->prepare(
304	' SELECT COALESCE( SUM( summary.period_views ), 0 ) AS total_views, COUNT( summary.post_id ) AS viewed_content_count
305	FROM (
306	SELECT pv.id AS post_id, SUM( pv.count ) AS period_views
307	FROM ' . $wpdb->prefix . 'post_views AS pv
308	INNER JOIN ' . $wpdb->posts . ' AS p ON pv.id = p.ID
309	WHERE pv.type = %d
310	AND ' . $period_query['clause'] . '
311	AND p.post_status = %s
312	AND p.post_password = %s
313	AND p.post_type IN (' . $post_type_placeholders . ')
314	GROUP BY pv.id
315	) AS summary',
316	$params
317	);
318
319	$row = $wpdb->get_row( $query, ARRAY_A );
320
321	return [
322	'total_views' => isset( $row['total_views'] ) ? (int) $row['total_views'] : 0,
323	'viewed_content_count' => isset( $row['viewed_content_count'] ) ? (int) $row['viewed_content_count'] : 0
324	];
325	}
326
327	/**
328	* Query top content for the current period.
329	*
330	* @global object $wpdb
331	* @param string\|int $start_period
332	* @param string\|int $end_period
333	* @param array $post_types
334	* @param int $limit
335	* @return array
336	*/
337	private function query_top_content( $start_period, $end_period, $post_types, $limit ) {
338	global $wpdb;
339
340	$post_type_placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) );
341	$period_query = $this->get_period_query_parts( $start_period, $end_period );
342	$params = array_merge( [ 0 ], $period_query['params'], [ 'publish', '' ], $post_types, [ (int) $limit ] );
343	$query = $wpdb->prepare(
344	' SELECT pv.id AS post_id, p.post_title AS post_title, SUM( pv.count ) AS current_views
345	FROM ' . $wpdb->prefix . 'post_views AS pv
346	INNER JOIN ' . $wpdb->posts . ' AS p ON pv.id = p.ID
347	WHERE pv.type = %d
348	AND ' . $period_query['clause'] . '
349	AND p.post_status = %s
350	AND p.post_password = %s
351	AND p.post_type IN (' . $post_type_placeholders . ')
352	GROUP BY pv.id, p.post_title
353	ORDER BY current_views DESC, pv.id ASC
354	LIMIT %d',
355	$params
356	);
357
358	$items = $wpdb->get_results( $query, ARRAY_A );
359
360	return is_array( $items ) ? $items : [];
361	}
362
363	/**
364	* Query previous-period views for a list of posts.
365	*
366	* @global object $wpdb
367	* @param string\|int $start_period
368	* @param string\|int $end_period
369	* @param array $post_types
370	* @param array $post_ids
371	* @return array
372	*/
373	private function query_previous_views_map( $start_period, $end_period, $post_types, $post_ids ) {
374	global $wpdb;
375
376	$post_ids = array_filter( array_map( 'intval', (array) $post_ids ) );
377
378	if ( empty( $post_ids ) )
379	return [];
380
381	$post_type_placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) );
382	$post_id_placeholders = implode( ', ', array_fill( 0, count( $post_ids ), '%d' ) );
383	$period_query = $this->get_period_query_parts( $start_period, $end_period );
384	$params = array_merge( [ 0 ], $period_query['params'], [ 'publish', '' ], $post_types, $post_ids );
385	$query = $wpdb->prepare(
386	' SELECT pv.id AS post_id, SUM( pv.count ) AS previous_views
387	FROM ' . $wpdb->prefix . 'post_views AS pv
388	INNER JOIN ' . $wpdb->posts . ' AS p ON pv.id = p.ID
389	WHERE pv.type = %d
390	AND ' . $period_query['clause'] . '
391	AND p.post_status = %s
392	AND p.post_password = %s
393	AND p.post_type IN (' . $post_type_placeholders . ')
394	AND pv.id IN (' . $post_id_placeholders . ')
395	GROUP BY pv.id',
396	$params
397	);
398
399	$results = $wpdb->get_results( $query, ARRAY_A );
400	$views = [];
401
402	if ( is_array( $results ) ) {
403	foreach ( $results as $result ) {
404	$views[(int) $result['post_id']] = (int) $result['previous_views'];
405	}
406	}
407
408	return $views;
409	}
410
411	/**
412	* Build the period comparison clause and parameters.
413	*
414	* Date-based period rows use zero-padded Ymd strings, so string range
415	* comparisons remain semantically correct and avoid casting the indexed
416	* period column when the bounds are valid eight-digit dates.
417	*
418	* @param string\|int $start_period
419	* @param string\|int $end_period
420	* @return array
421	*/
422	private function get_period_query_parts( $start_period, $end_period ) {
423	$raw_start_period = $start_period;
424	$raw_end_period = $end_period;
425	$start_period = $this->normalize_date_period_value( $start_period );
426	$end_period = $this->normalize_date_period_value( $end_period );
427
428	if ( $start_period !== '' && $end_period !== '' ) {
429	if ( $start_period > $end_period ) {
430	$temp = $start_period;
431	$start_period = $end_period;
432	$end_period = $temp;
433	}
434
435	return [
436	'clause' => 'pv.period >= %s AND pv.period <= %s',
437	'params' => [ $start_period, $end_period ]
438	];
439	}
440
441	return [
442	'clause' => 'CAST( pv.period AS SIGNED ) BETWEEN %d AND %d',
443	'params' => [ (int) $raw_start_period, (int) $raw_end_period ]
444	];
445	}
446
447	/**
448	* Normalize a date period value to an eight-digit Ymd string.
449	*
450	* @param string\|int $period
451	* @return string
452	*/
453	private function normalize_date_period_value( $period ) {
454	$period = preg_replace( '/\D+/', '', (string) $period );
455
456	return preg_match( '/^\d{8}$/', $period ) ? $period : '';
457	}
458
459	/**
460	* Build top-content data.
461	*
462	* @param array $top_items
463	* @param array $previous_views
464	* @return array
465	*/
466	private function build_top_content_data( $top_items, $previous_views ) {
467	$content = [];
468
469	foreach ( $top_items as $item ) {
470	$post_id = (int) $item['post_id'];
471	$current_views = (int) $item['current_views'];
472	$item_previous_views = isset( $previous_views[$post_id] ) ? (int) $previous_views[$post_id] : 0;
473	$trend = $this->build_trend_data( $current_views, $item_previous_views );
474	$url = get_permalink( $post_id );
475
476	$content[] = [
477	'post_id' => $post_id,
478	'title' => sanitize_text_field( $item['post_title'] ),
479	'url' => is_string( $url ) ? esc_url_raw( $url ) : '',
480	'current_views' => $current_views,
481	'previous_views' => $item_previous_views,
482	'views_change' => $trend['views_change'],
483	'views_change_percent' => $trend['views_change_percent'],
484	'trend_reliable' => $trend['trend_reliable'],
485	'trend_status' => $trend['trend_status']
486	];
487	}
488
489	return $content;
490	}
491
492	/**
493	* Build trend data.
494	*
495	* @param int $current_views
496	* @param int $previous_views
497	* @return array
498	*/
499	private function build_trend_data( $current_views, $previous_views ) {
500	$current_views = (int) $current_views;
501	$previous_views = (int) $previous_views;
502	$views_change = $current_views - $previous_views;
503	$trend = [
504	'views_change' => $views_change,
505	'views_change_percent' => null,
506	'trend_reliable' => false,
507	'trend_status' => 'neutral'
508	];
509
510	if ( $previous_views < 25 )
511	return $trend;
512
513	$trend['trend_reliable'] = true;
514	$trend['views_change_percent'] = round( ( $views_change / $previous_views ) * 100, 2 );
515
516	if ( $views_change > 0 )
517	$trend['trend_status'] = 'up';
518	elseif ( $views_change < 0 )
519	$trend['trend_status'] = 'down';
520	else
521	$trend['trend_status'] = 'flat';
522
523	return $trend;
524	}
525
526	/**
527	* Build traffic signal data.
528	*
529	* @param array $overview
530	* @param array $top_content
531	* @return array
532	*/
533	private function build_traffic_signals_data( $overview, $top_content ) {
534	if ( (int) $overview['total_views'] === 0 ) {
535	return [
536	'state' => 'silence',
537	'post_id' => 0,
538	'message_key' => 'no_data'
539	];
540	}
541
542	if ( empty( $top_content ) ) {
543	return [
544	'state' => 'silence',
545	'post_id' => 0,
546	'message_key' => 'not_enough_data'
547	];
548	}
549
550	foreach ( $top_content as $item ) {
551	$current_views = isset( $item['current_views'] ) ? (int) $item['current_views'] : 0;
552	$previous_views = isset( $item['previous_views'] ) ? (int) $item['previous_views'] : 0;
553
554	if ( $current_views < 10 \|\| $previous_views < 10 )
555	continue;
556	$change_percent = ( ( $current_views - $previous_views ) / $previous_views ) * 100;
557
558	if ( abs( $change_percent ) > 25 ) {
559	return [
560	'state' => 'anomaly',
561	'post_id' => ! empty( $item['post_id'] ) ? (int) $item['post_id'] : 0,
562	'message_key' => 'anomaly'
563	];
564	}
565	}
566
567	return [
568	'state' => 'silence',
569	'post_id' => ! empty( $top_content[0]['post_id'] ) ? (int) $top_content[0]['post_id'] : 0,
570	'message_key' => 'silence'
571	];
572	}
573
574	/**
575	* Build status data.
576	*
577	* @param array $overview
578	* @param bool $has_top_content
579	* @return array
580	*/
581	private function build_status_data( $overview, $has_top_content ) {
582	if ( (int) $overview['total_views'] === 0 ) {
583	return [
584	'empty' => true,
585	'reason' => 'no_data'
586	];
587	}
588
589	if ( ! $overview['threshold_met'] ) {
590	return [
591	'empty' => ! $has_top_content,
592	'reason' => 'below_threshold'
593	];
594	}
595
596	return [
597	'empty' => ! $has_top_content,
598	'reason' => 'ready'
599	];
600	}
601
602	}
603