← All changes | includes/Admin/ReportEmail.php +51 -37 4.4.1 → 4.9.2 View file →

		@@ -1,8 +1,13 @@
1	1	<?php
	2	+// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- weekly/monthly analytics aggregation; runs once per email send.
	3	+namespace WPDeveloper\BetterDocs\Admin;
2	4
3		-namespace WPDeveloper\BetterDocs\Admin;
	5	+if ( ! defined( 'ABSPATH' ) ) {
	6	+ exit;
	7	+}
4	8
	9	+
5	10	use WP_Error;
6	11	use WP_Query;
7	12	use stdClass;
8	13	use WPDeveloper\BetterDocs\Utils\Base;
		@@ -91,20 +96,21 @@
91	96	return self::$current_timestamp;
92	97	}
93	98
94	99	public function create_date( $count = '-7days' ) {
95		- return date( 'Y-m-d', strtotime( $count, self::timestamps() ) );
	100	+ return gmdate( 'Y-m-d', strtotime( $count, self::timestamps() ) );
96	101	}
97	102
98	103	public function get_views( $start_date, $end_date = null ) {
99	104	global $wpdb;
100		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
101	105	$query = $wpdb->get_results(
102		- "
103		- SELECT sum(impressions) as views, sum(unique_visit) as unique_visit, sum(happy + sad + normal) as reactions
104		- FROM {$wpdb->prefix}betterdocs_analytics
105		- WHERE (created_at BETWEEN '" . $start_date . "' AND '" . $end_date . "')
106		- "
	106	+ $wpdb->prepare(
	107	+ "SELECT sum(impressions) as views, sum(unique_visit) as unique_visit, sum(happy + sad + normal) as reactions
	108	+ FROM {$wpdb->prefix}betterdocs_analytics
	109	+ WHERE created_at BETWEEN %s AND %s",
	110	+ (string) $start_date,
	111	+ (string) $end_date
	112	+ )
107	113	);
108	114	return $query;
109	115	}
110	116
		@@ -109,15 +115,16 @@
109	115	}
110	116
111	117	public function get_search( $start_date, $end_date = null ) {
112	118	global $wpdb;
113		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
114	119	$query = $wpdb->get_results(
115		- "
116		- SELECT SUM(count + not_found_count) as search_count, SUM(count) as search_found, SUM(not_found_count) as search_not_found_count
117		- FROM {$wpdb->prefix}betterdocs_search_log as search_log
118		- WHERE (search_log.created_at BETWEEN '" . $start_date . "' AND '" . $end_date . "')
119		- "
	120	+ $wpdb->prepare(
	121	+ "SELECT SUM(count + not_found_count) as search_count, SUM(count) as search_found, SUM(not_found_count) as search_not_found_count
	122	+ FROM {$wpdb->prefix}betterdocs_search_log as search_log
	123	+ WHERE search_log.created_at BETWEEN %s AND %s",
	124	+ (string) $start_date,
	125	+ (string) $end_date
	126	+ )
120	127	);
121	128
122	129	return $query;
123	130	}
		@@ -141,20 +148,22 @@
141	148	}
142	149
143	150	public function get_search_keywords( $start_date, $end_date = null ) {
144	151	global $wpdb;
145		- $select = 'SELECT search_keyword.keyword, search_log.keyword_id, SUM(search_log.count + search_log.not_found_count) as total_search, SUM(search_log.count) as count, SUM(search_log.not_found_count) as not_found';
146		- $join = "FROM {$wpdb->prefix}betterdocs_search_keyword as search_keyword
147		- JOIN {$wpdb->prefix}betterdocs_search_log as search_log on search_keyword.id = search_log.keyword_id";
148		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
149	152	$query = $wpdb->get_results(
150		- "
151		- {$select}
152		- {$join}
153		- WHERE (search_log.created_at BETWEEN '" . $start_date . "' AND '" . $end_date . "')
154		- GROUP BY search_log.keyword_id
155		- ORDER BY count DESC LIMIT 5
156		- "
	153	+ $wpdb->prepare(
	154	+ "SELECT search_keyword.keyword, search_log.keyword_id,
	155	+ SUM(search_log.count + search_log.not_found_count) as total_search,
	156	+ SUM(search_log.count) as count,
	157	+ SUM(search_log.not_found_count) as not_found
	158	+ FROM {$wpdb->prefix}betterdocs_search_keyword as search_keyword
	159	+ JOIN {$wpdb->prefix}betterdocs_search_log as search_log ON search_keyword.id = search_log.keyword_id
	160	+ WHERE search_log.created_at BETWEEN %s AND %s
	161	+ GROUP BY search_log.keyword_id
	162	+ ORDER BY count DESC LIMIT 5",
	163	+ (string) $start_date,
	164	+ (string) $end_date
	165	+ )
157	166	);
158	167
159	168	return $query;
160	169	}
		@@ -191,20 +200,25 @@
191	200
192	201	public function get_leading_docs( $start_date, $end_date = null ) {
193	202	global $wpdb;
194	203
195		- $select = 'SELECT docs.ID, docs.post_author, docs.post_title, SUM(analytics.impressions) as total_views, SUM(analytics.unique_visit) as total_unique_visit, SUM(analytics.happy + analytics.sad + analytics.normal) as total_reactions';
196		- $join = "FROM {$wpdb->prefix}posts as docs
197		- JOIN {$wpdb->prefix}betterdocs_analytics as analytics on docs.ID = analytics.post_id";
198		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
199	204	$query = $wpdb->get_results(
200		- "
201		- {$select}
202		- {$join}
203		- WHERE post_type = 'docs' AND post_status = 'publish' AND (analytics.created_at BETWEEN '" . $start_date . "' AND '" . $end_date . "')
204		- GROUP BY analytics.post_id
205		- ORDER BY total_views DESC LIMIT 5
206		- "
	205	+ $wpdb->prepare(
	206	+ "SELECT docs.ID, docs.post_author, docs.post_title,
	207	+ SUM(analytics.impressions) as total_views,
	208	+ SUM(analytics.unique_visit) as total_unique_visit,
	209	+ SUM(analytics.happy + analytics.sad + analytics.normal) as total_reactions
	210	+ FROM {$wpdb->posts} as docs
	211	+ JOIN {$wpdb->prefix}betterdocs_analytics as analytics ON docs.ID = analytics.post_id
	212	+ WHERE post_type = %s AND post_status = %s
	213	+ AND analytics.created_at BETWEEN %s AND %s
	214	+ GROUP BY analytics.post_id
	215	+ ORDER BY total_views DESC LIMIT 5",
	216	+ 'docs',
	217	+ 'publish',
	218	+ (string) $start_date,
	219	+ (string) $end_date
	220	+ )
207	221	);
208	222
209	223	$docs = [];
210	224	foreach ( $query as $key => $value ) {
		@@ -457,9 +471,9 @@
457	471	$days_ago = '1 day';
458	472	break;
459	473	case 'betterdocs_monthly':
460	474	$initial_timestamp = strtotime( 'first day of last month', current_time( 'timestamp' ) );
461		- $days_in_last_month = cal_days_in_month( CAL_GREGORIAN, date( 'm', $initial_timestamp ), date( 'Y', $initial_timestamp ) );
	475	+ $days_in_last_month = cal_days_in_month( CAL_GREGORIAN, (int) gmdate( 'm', $initial_timestamp ), (int) gmdate( 'Y', $initial_timestamp ) );
462	476	$days_ago = $days_in_last_month . ' days';
463	477	break;
464	478	}
465	479