chart-builder / includes / class-chart-builder-db-query.php

class-chart-builder-db-query.php in Chartify – WordPress Chart Plugin 3.5.2, at includes/class-chart-builder-db-query.php

707 lines 16.4 KB

Raw Download Zip

1	<?php
2	if( !class_exists( 'Chart_Builder_DB_Query' ) ){
3	ob_start();
4
5	/**
6	* Class Chart_Builder_DB_Query
7	* Class contains functions to interact with chart database
8	*
9	* Main functionality belong to inserting, updating and deleting of
10	* Also chart settings and options
11	*
12	* Hooks used in the class
13	* @hooks @filters ays_chart_item_save_options
14	* ays_chart_item_save_settings
15	*
16	* Database tables without prefixes
17	* @tables charts
18	* charts_meta
19	*
20	* @param $plugin_name
21	*
22	* @since 1.0.0
23	* @package Chart_Builder
24	* @subpackage Chart_Builder/includes
25	* @author Chart Builder Team <info@ays-pro.com>
26	*/
27	class Chart_Builder_DB_Query {
28
29	/**
30	* The array of allowed types.
31	*
32	* @since 1.0.0
33	*
34	* @access protected
35	* @var array
36	*/
37	protected static $allowed_types = array( 'string', 'number', 'boolean', 'date', 'datetime', 'timeofday' );
38
39	/**
40	* The query.
41	*
42	* @access protected
43	* @var string
44	*/
45	protected $_query;
46
47	/**
48	* The chart id.
49	*
50	* @access protected
51	* @var int
52	*/
53	protected $_chart_id;
54
55	/**
56	* Any additional parameters (e.g. for connecting to a remote db).
57	*
58	* @access protected
59	* @var array
60	*/
61	protected $_params;
62
63	/**
64	* The error message.
65	*
66	* @access protected
67	* @var string
68	*/
69	protected $_error;
70
71	/**
72	* The array of data.
73	*
74	* @since 1.0.0
75	*
76	* @access protected
77	* @var array
78	*/
79	protected $_data = array();
80
81	/**
82	* The array of series.
83	*
84	* @since 1.0.0
85	*
86	* @access protected
87	* @var array
88	*/
89	protected $_series = array();
90
91	/**
92	*
93	* @since 1.0.0
94	*
95	* @access private
96	* @var
97	*/
98	private $_args;
99
100	/**
101	* Constructor.
102	*
103	* @access public
104	* @param string $query The query.
105	* @param int $chart_id The chart id.
106	* @param array $params Any additional parameters (e.g. for connecting to a remote db).
107	*/
108	public function __construct( $query = null, $chart_id = null, $params = null ) {
109	$this->_query = $query;
110	$this->_chart_id = $chart_id;
111	$this->_params = $params;
112	}
113
114	/**
115	* Return allowed types
116	*
117	* @since 1.0.1
118	*
119	* @static
120	* @access public
121	* @return array the allowed types
122	*/
123	public static function getAllowedTypes() {
124	return self::$allowed_types;
125	}
126
127	/**
128	* Validates series tyeps.
129	*
130	* @since 1.0.1
131	*
132	* @static
133	* @access protected
134	*
135	* @param array $types The icoming series types.
136	*
137	* @return boolean TRUE if sereis types are valid, otherwise FALSE.
138	*/
139	protected static function _validateTypes( $types ) {
140	foreach ( $types as $type ) {
141	if ( ! in_array( $type, self::$allowed_types, true ) ) {
142	return false;
143	}
144	}
145
146	return true;
147	}
148
149	/**
150	* Fetches information from source, parses it and builds series and data arrays.
151	*
152	* @param bool $as_html Should the result be fetched as an HTML table or as an object.
153	* @param bool $results_as_numeric_array Should the result be fetched as ARRAY_N instead of ARRAY_A.
154	* @param bool $raw_results Should the result be returned without processing.
155	* @access public
156	* @return boolean TRUE on success, otherwise FALSE.
157	*/
158	public function fetch( $as_html = false, $results_as_numeric_array = false, $raw_results = false ) {
159	if ( empty( $this->_query ) ) {
160	return false;
161	}
162
163	// only select queries allowed.
164	if ( preg_match( '/^\s*(insert\|delete\|update\|replace\|create\|alter\|drop\|truncate)\s/i', $this->_query ) ) {
165	$this->_error = __( 'Only SELECT queries are allowed', 'chart-builder' );
166	return false;
167	}
168
169	$validate_query = str_replace( "\n", ' ', $this->_query );
170	// impose a limit if no limit clause is provided.
171	if ( strpos( strtolower( $validate_query ), 'select' ) !== false ) {
172	if ( strpos( strtolower( $validate_query ), ' limit ' ) === false ) {
173	$this->_query .= ' LIMIT ' . apply_filters( 'ays_cb_sql_query_limit', 1000, $this->_chart_id );
174	}
175	}
176
177	$this->_query = apply_filters( 'ays_cb_db_query', $this->_query, $this->_chart_id, $this->_params );
178
179	$results = array();
180	$headers = array();
181
182	// short circuit results for remote dbs.
183	if ( false !== ( $remote_results = apply_filters( 'ays_cb_db_query_execute', false, $this->_query, $as_html, $results_as_numeric_array, $raw_results, $this->_chart_id, $this->_params ) ) ) {
184	$error = $remote_results['error'];
185	if ( empty( $error ) ) {
186	$results = $remote_results['results'];
187	$headers = $remote_results['headers'];
188	}
189
190	$this->_error = $error;
191
192	if ( $raw_results ) {
193	return $results;
194	}
195	}
196
197	if ( ! ( $results && $headers ) ) {
198	global $wpdb;
199	$wpdb->hide_errors();
200	// @codingStandardsIgnoreStart
201	$rows = $wpdb->get_results( $this->_query, $results_as_numeric_array ? ARRAY_N : ARRAY_A );
202	// @codingStandardsIgnoreEnd
203	$wpdb->show_errors();
204
205	if ( $raw_results ) {
206	return $rows;
207	}
208
209	if ( $rows ) {
210	$results = array();
211	$headers = array();
212	if ( $rows ) {
213	$row_num = 0;
214	foreach ( $rows as $row ) {
215	$result = array();
216	$col_num = 0;
217	foreach ( $row as $k => $v ) {
218	$result[] = $v;
219	if ( 0 === $row_num ) {
220	$headers[] = array( 'type' => $this->get_col_type( $col_num++ ), 'label' => $k );
221	}
222	}
223	$results[] = $result;
224	$row_num++;
225	}
226	}
227	}
228
229	$this->_error = $wpdb->last_error;
230	}
231
232	if ( $as_html ) {
233	$results = $this->html( $headers, $results );
234	} else {
235	$results = $this->object( $headers, $results );
236	}
237
238	return apply_filters( 'ays_cb_db_query_results', $results, $headers, $as_html, $results_as_numeric_array, $raw_results, $this->_query, $this->_chart_id, $this->_params );
239	}
240
241	/**
242	* Get the data type of the column.
243	*
244	* @param int $col_num The column index in the fetched result set.
245	* @access private
246	* @return int
247	*/
248	private function get_col_type( $col_num ) {
249	global $wpdb;
250	switch ( $wpdb->get_col_info( 'type', $col_num ) ) {
251	case 0:
252	case 5:
253	case 4:
254	case 9:
255	case 3:
256	case 2:
257	case 246:
258	case 8:
259	// numeric.
260	return 'number';
261	case 10:
262	case 12:
263	case 14:
264	// date.
265	return 'date';
266	}
267	return 'string';
268	}
269
270	/**
271	* Returns the HTML output.
272	*
273	* @param array $headers The headers of the result set.
274	* @param array $results The data of the result set.
275	* @access private
276	* @return string
277	*/
278	private function html( $headers, $results ) {
279	ob_start();
280	?>
281	<table cellspacing="0" width="100%" id="results">
282	<thead>
283	<tr>
284	<?php
285	foreach ( $headers as $header ) {
286	echo '<th>' . esc_html($header['label']) . '</th>';
287	}
288	?>
289	</tr>
290	</thead>
291	<tfoot>
292	</tfoot>
293	<tbody>
294	<?php
295	foreach ( $results as $result ) {
296	echo '<tr>';
297	foreach ( $result as $r ) {
298	echo '<td>' . esc_html($r) . '</td>';
299	}
300	echo '</tr>';
301	}
302	?>
303	</tbody>
304	</table>
305	<?php
306	return ob_get_clean();
307	}
308
309	/**
310	* Sets the series and data.
311	*
312	* @param array $headers The headers of the result set.
313	* @param array $results The data of the result set.
314	*
315	* @access private
316	* @return array
317	* @throws Exception
318	*/
319	private function object( $headers, $results ) {
320	$series = array();
321	foreach ( $headers as $header ) {
322	$series[] = $header;
323	}
324	$this->_series = $series;
325
326	$data = array();
327	foreach ( $results as $row ) {
328	$data[] = $this->_normalizeData( $row );
329	}
330
331	$this->_data = $data;
332
333	return $this->_data;
334	}
335
336	/**
337	* Returns the final query.
338	*
339	* @access public
340	* @return string
341	*/
342	public function get_query() {
343	return $this->_query;
344	}
345
346	/**
347	* Returns source name.
348	*
349	* @since 1.0.0
350	*
351	* @access public
352	* @return string The name of source.
353	*/
354	public function getSourceName() {
355	return __CLASS__;
356	}
357
358	/**
359	* Returns series parsed from source.
360	*
361	* @since 1.0.0
362	*
363	* @access public
364	* @return array The array of series.
365	*/
366	public function getSeries() {
367	return $this->_series;
368	}
369
370	/**
371	* Returns data parsed from source.
372	*
373	* @since 1.0.0
374	*
375	* @access public
376	* @return string The serialized array of data.
377	*/
378	public function getData( $fetch_from_editable_table = false ) {
379	if ( $fetch_from_editable_table ) {
380	$this->_fetchDataFromEditableTable();
381	}
382	return serialize( $this->_data );
383	}
384
385	/**
386	* Returns raw data array.
387	*
388	* @since 1.1.0
389	*
390	* @access public
391	* @return array
392	*/
393	public function getRawData( $fetch_from_editable_table = false ) {
394	if ( $fetch_from_editable_table ) {
395	$this->_fetchDataFromEditableTable();
396	}
397	return $this->_data;
398	}
399
400	/**
401	* Re populates series if the source is dynamic.
402	*
403	* @since 1.1.0
404	*
405	* @access public
406	*
407	* @param array $series The actual array of series.
408	* @param int $chart_id The chart id.
409	*
410	* @return array The re populated array of series or old one.
411	*/
412	public function repopulateSeries( $series, $chart_id ) {
413	return $series;
414	}
415
416	/**
417	* Re populates data if the source is dynamic.
418	*
419	* @since 1.1.0
420	*
421	* @access public
422	*
423	* @param array $data The actual array of data.
424	* @param int $chart_id The chart id.
425	*
426	* @return array The re populated array of data or old one.
427	*/
428	public function repopulateData( $data, $chart_id ) {
429	return $data;
430	}
431
432	/**
433	* Normalizes values according to series' type.
434	*
435	* @param array $data The row of data.
436	*
437	* @return array Normalized row of data.
438	* @throws Exception
439	* @since 1.0.0
440	*
441	* @access protected
442	*
443	*/
444	protected function _normalizeData( $data ) {
445	// normalize values
446	foreach ( $this->_series as $i => $series ) {
447	// if no value exists for the seires, then add null
448	if ( ! isset( $data[ $i ] ) ) {
449	$data[ $i ] = null;
450	}
451	if ( is_null( $data[ $i ] ) ) {
452	continue;
453	}
454	switch ( $series['type'] ) {
455	case 'number':
456	$data[ $i ] = ( is_numeric( $data[ $i ] ) ) ? floatval( $data[ $i ] ) : ( is_numeric( str_replace( ',', '', $data[ $i ] ) ) ? floatval( str_replace( ',', '', $data[ $i ] ) ) : null );
457	break;
458	case 'boolean':
459	$datum = trim( strval( $data[ $i ] ) );
460	$data[ $i ] = in_array( $datum, array( 'true', 'yes', '1' ), true ) ? 'true' : 'false';
461	break;
462	case 'timeofday':
463	$date = new DateTime( '1984-03-16T' . $data[ $i ] );
464	if ( $date ) {
465	$data[ $i ] = array(
466	intval( $date->format( 'H' ) ),
467	intval( $date->format( 'i' ) ),
468	intval( $date->format( 's' ) ),
469	0,
470	);
471	}
472	break;
473	case 'datetime':
474	// let's check if the date is a Unix epoch
475	$value = DateTime::createFromFormat( 'U', $data[ $i ] );
476	if ( $value !== false && ! is_wp_error( $value ) ) {
477	$data[ $i ] = $value->format( 'Y-m-d H:i:s' );
478	}
479	break;
480	case 'string':
481	// if a ' is provided, strip the backslash
482	$data[ $i ] = stripslashes( $this->toUTF8( $data[ $i ] ) );
483	break;
484	}
485	}
486
487	return apply_filters( 'ays_cb_format_data', $data, $this->_series );
488	}
489
490	/**
491	* Converts values to UTF8, if required.
492	*
493	* @access protected
494	*
495	* @param string $datum The data to convert.
496	*
497	* @return string The converted data.
498	*/
499	protected final function toUTF8( $datum ) {
500	if ( ! function_exists( 'mb_detect_encoding' ) \|\| mb_detect_encoding( $datum ) !== 'ASCII' ) {
501	$datum = \ForceUTF8\Encoding::toUTF8( $datum );
502	}
503	return $datum;
504	}
505
506	/**
507	* Determines the formats of date/time columns.
508	*
509	* @access public
510	*
511	* @param array $series The actual array of series.
512	* @param array $data The actual array of data.
513	*
514	* @return array
515	*/
516	public static final function get_date_formats_if_exists( $series, $data ) {
517	$date_formats = array();
518	$types = array();
519	$index = 0;
520	foreach ( $series as $column ) {
521	if ( in_array( $column['type'], array( 'date', 'datetime', 'timeofday' ), true ) ) {
522	$types[] = array( 'index' => $index, 'type' => $column['type'] );
523	}
524	$index++;
525	}
526
527	if ( ! $types ) {
528	return $date_formats;
529	}
530
531	$random = $data;
532	// let's randomly pick 5 data points instead of cycling through the entire data set.
533	if ( count( $data ) > 5 ) {
534	$random = array();
535	for ( $x = 0; $x < 5; $x++ ) {
536	$random[] = $data[ wp_rand( 0, count( $data ) - 1 ) ];
537	}
538	}
539
540	foreach ( $types as $type ) {
541	$formats = array();
542	foreach ( $random as $datum ) {
543	$f = self::determine_date_format( $datum[ $type['index'] ], $type['type'] );
544	if ( $f ) {
545	$formats[] = $f;
546	}
547	}
548	// if there are multiple formats, use the most frequent format.
549	$formats = array_filter( $formats );
550	if ( $formats ) {
551	$formats = array_count_values( $formats );
552	arsort( $formats );
553	$formats = array_keys( $formats );
554	$final_format = reset( $formats );
555	// we have determined the PHP format; now we have to change this into the JS format where m = MM, d = DD etc.
556	$date_formats[] = array( 'index' => $type['index'], 'format' => str_replace( array( 'Y', 'm', 'd', 'H', 'i', 's' ), array( 'YYYY', 'MM', 'DD', 'HH', 'mm', 'ss' ), $final_format ) );
557	}
558	}
559	return $date_formats;
560	}
561
562	/**
563	* Determines the date/time format of the given string.
564	*
565	* @access private
566	*
567	* @param string $value The string.
568	* @param string $type 'date', 'timeofday' or 'datetime'.
569	*
570	* @return string\|null
571	*/
572	private static function determine_date_format( $value, $type ) {
573	if ( version_compare( phpversion(), '5.3.0', '<' ) ) {
574	return null;
575	}
576
577	$formats = array(
578	'Y/m/d',
579	'Y-m-d',
580	'm/d/Y',
581	'm-d-Y',
582	'd-m-Y',
583	'd/m/Y',
584	);
585
586	switch ( $type ) {
587	case 'datetime':
588	$formats = array_merge(
589	$formats, array(
590	'U',
591	'Y/m/d H:i:s',
592	'Y-m-d H:i:s',
593	'm/d/Y H:i:s',
594	'm-d-Y H:i:s',
595	)
596	);
597	break;
598	case 'timeofday':
599	$formats = array_merge(
600	$formats, array(
601	'H:i:s',
602	'H:i',
603	)
604	);
605	break;
606	}
607
608	$formats = apply_filters( 'ays_cb_date_formats', $formats, $type );
609
610	foreach ( $formats as $format ) {
611	$return = DateTime::createFromFormat( $format, $value );
612	if ( $return !== false && ! is_wp_error( $return ) ) {
613	return $format;
614	}
615	}
616	// invalid format
617	return null;
618	}
619
620	/**
621	* Returns the error, if any.
622	*
623	* @access public
624	* @return string
625	*/
626	public function get_error() {
627	return $this->_error;
628	}
629
630	/**
631	* Fetches information from the editable table and parses it to build series and data arrays.
632	*
633	* @since ?
634	*
635	* @access public
636	* @return boolean TRUE on success, otherwise FALSE.
637	*/
638	public function fetchFromEditableTable() {
639	if ( empty( $this->_args ) ) {
640	return false;
641	}
642
643	$this->_fetchSeriesFromEditableTable();
644	$this->_fetchDataFromEditableTable();
645	return true;
646	}
647
648	/**
649	* Fetches series information from the editable table. This is fetched only through the UI and not while refreshing the chart data.
650	*
651	* @since 1.0.0
652	*
653	* @access private
654	*/
655	private function _fetchSeriesFromEditableTable() {
656	$params = $this->_args;
657	$headers = array_filter( $params['header'] );
658	$types = array_filter( $params['type'] );
659	$header_row = $type_row = array();
660
661	if ( $headers ) {
662	foreach ( $headers as $header ) {
663	if ( ! empty( $types[ $header ] ) ) {
664	$this->_series[] = array(
665	'label' => $header,
666	'type' => $types[ $header ],
667	);
668	}
669	}
670	}
671
672	return true;
673	}
674
675	/**
676	* Fetches data information from the editable table.
677	*
678	* @throws Exception
679	* @since 1.0.0
680	*
681	* @access private
682	*/
683	private function _fetchDataFromEditableTable() {
684	$headers = wp_list_pluck( $this->_series, 'label' );
685	$this->fetch();
686
687	$data = $this->_data;
688	$this->_data = array();
689
690	foreach ( $data as $line ) {
691	$data_row = array();
692	// we have to make sure we are fetching the data in the right order
693	// in case the columns have been reordered
694	foreach ( $headers as $header ) {
695	$value = $line[ $header ];
696	// phpcs:ignore WordPress.PHP.StrictInArray.MissingTrueStrict
697	if ( in_array( $header, $headers ) ) {
698	$data_row[] = $value;
699	}
700	}
701	$this->_data[] = $this->_normalizeData( $data_row );
702	}
703
704	return true;
705	}
706	}
707	}