stream / classes / class-query.php

class-query.php in Stream – Activity Log & Audit Trail 3.6.1, at classes/class-query.php

276 lines 7.4 KB

Raw Download Zip

1	<?php
2	/**
3	* Queries the database for stream records.
4	*
5	* @package WP_Stream
6	*/
7
8	namespace WP_Stream;
9
10	/**
11	* Class - Query
12	*/
13	class Query {
14	/**
15	* Hold the number of records found
16	*
17	* @var int
18	*/
19	public $found_records = 0;
20
21	/**
22	* Query records
23	*
24	* @param array $args Arguments to filter the records by.
25	*
26	* @return array Stream Records
27	*/
28	public function query( $args ) {
29	global $wpdb;
30
31	$join = '';
32	$where = '';
33
34	/**
35	* PARSE CORE PARAMS
36	*/
37	if ( is_numeric( $args['site_id'] ) ) {
38	$where .= $wpdb->prepare( " AND $wpdb->stream.site_id = %d", $args['site_id'] );
39	}
40
41	if ( is_numeric( $args['blog_id'] ) ) {
42	$where .= $wpdb->prepare( " AND $wpdb->stream.blog_id = %d", $args['blog_id'] );
43	}
44
45	if ( is_numeric( $args['object_id'] ) ) {
46	$where .= $wpdb->prepare( " AND $wpdb->stream.object_id = %d", $args['object_id'] );
47	}
48
49	if ( is_numeric( $args['user_id'] ) ) {
50	$where .= $wpdb->prepare( " AND $wpdb->stream.user_id = %d", $args['user_id'] );
51	}
52
53	if ( ! empty( $args['user_role'] ) ) {
54	$where .= $wpdb->prepare( " AND $wpdb->stream.user_role = %s", $args['user_role'] );
55	}
56
57	if ( ! empty( $args['search'] ) ) {
58	$field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary';
59
60	// Sanitize field.
61	$allowed_fields = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'created', 'summary', 'connector', 'context', 'action', 'ip' );
62	if ( in_array( $field, $allowed_fields, true ) ) {
63	$where .= $wpdb->prepare( " AND $wpdb->stream.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name
64	}
65	}
66
67	if ( ! empty( $args['connector'] ) ) {
68	$where .= $wpdb->prepare( " AND $wpdb->stream.connector = %s", $args['connector'] );
69	}
70
71	if ( ! empty( $args['context'] ) ) {
72	$where .= $wpdb->prepare( " AND $wpdb->stream.context = %s", $args['context'] );
73	}
74
75	if ( ! empty( $args['action'] ) ) {
76	$where .= $wpdb->prepare( " AND $wpdb->stream.action = %s", $args['action'] );
77	}
78
79	if ( ! empty( $args['ip'] ) ) {
80	$where .= $wpdb->prepare( " AND $wpdb->stream.ip = %s", wp_stream_filter_var( $args['ip'], FILTER_VALIDATE_IP ) );
81	}
82
83	/**
84	* PARSE DATE PARAM FAMILY
85	*/
86	if ( ! empty( $args['date'] ) ) {
87	$args['date_from'] = $args['date'];
88	$args['date_to'] = $args['date'];
89	}
90
91	if ( ! empty( $args['date_from'] ) ) {
92	$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_from'] . ' 00:00:00' ) ) );
93	$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) >= %s", $date );
94	}
95
96	if ( ! empty( $args['date_to'] ) ) {
97	$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_to'] . ' 23:59:59' ) ) );
98	$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) <= %s", $date );
99	}
100
101	if ( ! empty( $args['date_after'] ) ) {
102	$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_after'] ) ) );
103	$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) > %s", $date );
104	}
105
106	if ( ! empty( $args['date_before'] ) ) {
107	$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_before'] ) ) );
108	$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) < %s", $date );
109	}
110
111	/**
112	* PARSE __IN PARAM FAMILY
113	*/
114	$ins = array();
115
116	foreach ( $args as $arg => $value ) {
117	if ( '__in' === substr( $arg, -4 ) ) {
118	$ins[ $arg ] = $value;
119	}
120	}
121
122	if ( ! empty( $ins ) ) {
123	foreach ( $ins as $key => $value ) {
124	if ( empty( $value ) \|\| ! is_array( $value ) ) {
125	continue;
126	}
127
128	$field = str_replace( array( 'record_', '__in' ), '', $key );
129	$field = empty( $field ) ? 'ID' : $field;
130	$type = is_numeric( array_shift( $value ) ) ? '%d' : '%s';
131
132	if ( ! empty( $value ) ) {
133	$format = '(' . join( ',', array_fill( 0, count( $value ), $type ) ) . ')';
134	$where .= $wpdb->prepare( " AND $wpdb->stream.%s IN {$format}", $field, $value ); // @codingStandardsIgnoreLine prepare okay
135	}
136	}
137	}
138
139	/**
140	* PARSE __NOT_IN PARAM FAMILY
141	*/
142	$not_ins = array();
143
144	foreach ( $args as $arg => $value ) {
145	if ( '__not_in' === substr( $arg, -8 ) ) {
146	$not_ins[ $arg ] = $value;
147	}
148	}
149
150	if ( ! empty( $not_ins ) ) {
151	foreach ( $not_ins as $key => $value ) {
152	if ( empty( $value ) \|\| ! is_array( $value ) ) {
153	continue;
154	}
155
156	$field = str_replace( array( 'record_', '__not_in' ), '', $key );
157	$field = empty( $field ) ? 'ID' : $field;
158	$type = is_numeric( array_shift( $value ) ) ? '%d' : '%s';
159
160	if ( ! empty( $value ) ) {
161	$format = '(' . join( ',', array_fill( 0, count( $value ), $type ) ) . ')';
162	$where .= $wpdb->prepare( " AND $wpdb->stream.%s NOT IN {$format}", $field, $value ); // @codingStandardsIgnoreLine prepare okay
163	}
164	}
165	}
166
167	/**
168	* PARSE PAGINATION PARAMS
169	*/
170	$limits = '';
171	$page = absint( $args['paged'] );
172	$per_page = absint( $args['records_per_page'] );
173
174	if ( $per_page >= 0 ) {
175	$offset = absint( ( $page - 1 ) * $per_page );
176	$limits = "LIMIT {$offset}, {$per_page}";
177	}
178
179	/**
180	* PARSE ORDER PARAMS
181	*/
182	$order = esc_sql( $args['order'] );
183	$orderby = esc_sql( $args['orderby'] );
184	$orderable = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'summary', 'created', 'connector', 'context', 'action' );
185
186	if ( in_array( $orderby, $orderable, true ) ) {
187	$orderby = sprintf( '%s.%s', $wpdb->stream, $orderby );
188	} elseif ( 'meta_value_num' === $orderby && ! empty( $args['meta_key'] ) ) {
189	$orderby = "CAST($wpdb->streammeta.meta_value AS SIGNED)";
190	} elseif ( 'meta_value' === $orderby && ! empty( $args['meta_key'] ) ) {
191	$orderby = "$wpdb->streammeta.meta_value";
192	} else {
193	$orderby = "$wpdb->stream.ID";
194	}
195
196	$orderby = "ORDER BY {$orderby} {$order}";
197
198	/**
199	* PARSE FIELDS PARAMETER
200	*/
201	$fields = (array) $args['fields'];
202	$selects = array();
203
204	if ( ! empty( $fields ) ) {
205	foreach ( $fields as $field ) {
206	// We'll query the meta table later.
207	if ( 'meta' === $field ) {
208	continue;
209	}
210
211	$selects[] = sprintf( "$wpdb->stream.%s", $field );
212	}
213	} else {
214	$selects[] = "$wpdb->stream.*";
215	}
216
217	$select = implode( ', ', $selects );
218
219	/**
220	* Filters query WHERE statement as an alternative to filtering
221	* the $query using the hook below.
222	*
223	* @param string $where WHERE statement.
224	*
225	* @return string
226	*/
227	$where = apply_filters( 'wp_stream_db_query_where', $where );
228
229	/**
230	* BUILD THE FINAL QUERY
231	*/
232	$query = "SELECT {$select}
233	FROM $wpdb->stream
234	{$join}
235	WHERE 1=1 {$where}
236	{$orderby}
237	{$limits}";
238
239	/**
240	* Filter allows the final query to be modified before execution
241	*
242	* @param string $query
243	* @param array $args
244	*
245	* @return string
246	*/
247	$query = apply_filters( 'wp_stream_db_query', $query, $args );
248
249	// Build result count query.
250	$count_query = "SELECT COUNT(*) as found
251	FROM $wpdb->stream
252	{$join}
253	WHERE 1=1 {$where}";
254
255	/**
256	* Filter allows the result count query to be modified before execution.
257	*
258	* @param string $query
259	* @param array $args
260	*
261	* @return string
262	*/
263	$count_query = apply_filters( 'wp_stream_db_count_query', $count_query, $args );
264
265	/**
266	* QUERY THE DATABASE FOR RESULTS
267	*/
268	$result = array(
269	'items' => $wpdb->get_results( $query ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
270	'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
271	);
272
273	return $result;
274	}
275	}
276