stream / classes / class-query.php

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

240 lines 6.6 KB

Raw Download Zip

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