wp-data-access / WPDataAccess / Dashboard / WPDA_Widget_Google_Chart.php

WPDA_Widget_Google_Chart.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.5.85, at WPDataAccess/Dashboard/WPDA_Widget_Google_Chart.php

428 lines 13.5 KB

Raw Download Zip

1	<?php
2
3	// phpcs:disable WordPress.Security.NonceVerification.Recommended, WordPress.Security.NonceVerification.Missing -- verified on page
4	namespace WPDataAccess\Dashboard;
5
6	use WPDataAccess\Connection\WPDADB;
7	use WPDataAccess\WPDA;
8	/**
9	* Chart widget
10	*/
11	class WPDA_Widget_Google_Chart extends WPDA_Widget {
12	const OPTION_CHART_CACHE = 'wpda-chart-cache';
13
14	/**
15	* Output type
16	*
17	* @var mixed\|string[]
18	*/
19	protected $outputType = array('Table');
20
21	// phpcs:ignore
22	/**
23	* Selected chart types
24	*
25	* @var array\|mixed
26	*/
27	protected $userChartTypeList = array();
28
29	// phpcs:ignore
30	/**
31	* Database name
32	*
33	* @var mixed\|null
34	*/
35	protected $dbs = null;
36
37	/**
38	* SQL query
39	*
40	* @var mixed\|null
41	*/
42	protected $query = null;
43
44	/**
45	* Refresh indicator
46	*
47	* @var mixed\|null
48	*/
49	protected $refresh = null;
50
51	/**
52	* Cache indicator
53	*
54	* @var mixed\|null
55	*/
56	protected $cache = null;
57
58	/**
59	* Unit of measurement (for cache)
60	*
61	* @var mixed\|null
62	*/
63	protected $unit = null;
64
65	/**
66	* Queried columns
67	*
68	* @var array
69	*/
70	protected $columns = array();
71
72	/**
73	* Selected rows
74	*
75	* @var array
76	*/
77	protected $rows = array();
78
79	/**
80	* Chart options
81	*
82	* @var mixed\|null
83	*/
84	protected $options = null;
85
86	private $sonse = null;
87
88	/**
89	* Constructor
90	*
91	* @param array $args Constructor arguments.
92	*/
93	public function __construct( $args = array() ) {
94	parent::__construct( $args );
95	$this->can_share = true;
96	$this->has_layout = true;
97	$this->has_setting = true;
98	$this->can_refresh = true;
99	// Create container.
100	$this->content = "\n\t\t\t\t<div class='wpda-chart-container'>\n\t\t\t\t\t<div class='wpda_widget_chart_selection'>\n\t\t\t\t\t\t<button href='javascript:void(0)' class='dt-button wpda-chart-button-export'>\n\t\t\t\t\t\t\t<i class='fas fa-cloud-download wpda_icon_on_button'></i>\n\t\t\t\t\t\t\tExport data\n\t\t\t\t\t\t</button>\n\t\t\t\t\t\t<a href='' target='_blank' style='display:none' class='wpda-chart-button-export-link'>Export data hyperlink</a>\n\t\t\t\t\t\t<button href='javascript:void(0)' class='dt-button wpda-chart-button-print' style='display:none'>\n\t\t\t\t\t\t\t<i class='fas fa-print wpda_icon_on_button'></i>\n\t\t\t\t\t\t\tPrintable version\n\t\t\t\t\t\t</button>\n\t\t\t\t\t\t<select id='wpda_widget_chart_selection_{$this->widget_id}' style='display:none'></select>\n\t\t\t\t\t</div>\n\t\t\t\t\t<div class='wpda_widget_chart_container' id='wpda_widget_container_{$this->widget_id}'></div>\n\t\t\t\t</div>\n\t\t\t";
101	}
102
103	/**
104	* Chart shortcode implementation
105	*
106	* @param WPDA_Widget_Google_Chart $widget Chart widget.
107	* @return void
108	*/
109	public function do_shortcode( $widget ) {
110	}
111
112	/**
113	* Embed chart widget on external website
114	*
115	* @param WPDA_Widget_Google_Chart $widget Chart widget.
116	* @param string $target_element HTML element id.
117	* @return void
118	*/
119	public function do_embed( $widget, $target_element ) {
120	}
121
122	/**
123	* Edit chart form
124	*
125	* @return void
126	*/
127	public function edit_chart() {
128	// phpcs:disable WordPress.Security.EscapeOutput
129	echo $this->html();
130	echo $this->js( 300 );
131	// phpcs:enable WordPress.Security.EscapeOutput
132	}
133
134	/**
135	* Chart javascript code
136	*
137	* @param integer $interval Interval.
138	* @return void
139	*/
140	protected function js( $interval = 1000 ) {
141	?>
142	<script type='application/javascript' class="wpda-widget-<?php
143	echo esc_attr( $this->widget_id );
144	?>">
145	jQuery(function() {
146	var widget = jQuery("#wpda-widget-<?php
147	echo esc_attr( $this->widget_id );
148	?>");
149
150	<?php
151	?>
152
153	widget.find(".wpda-widget-layout").on("click", function() {
154	chartLayout("<?php
155	echo esc_attr( $this->widget_id );
156	?>");
157	});
158
159	widget.find(".wpda-widget-setting").on("click", function() {
160	chartSettings("<?php
161	echo esc_attr( $this->widget_id );
162	?>");
163	});
164
165	widget.find(".wpda-widget-refresh").on("click", function(e, action = null) {
166	if (action==="refresh") {
167	refreshChart("<?php
168	echo esc_attr( $this->widget_id );
169	?>");
170	} else {
171	getChartData("<?php
172	echo esc_attr( $this->widget_id );
173	?>");
174	}
175	});
176
177	jQuery("#wpda_widget_chart_selection_<?php
178	echo esc_attr( $this->widget_id );
179	?>").on("change", function() {
180	refreshChart("<?php
181	echo esc_attr( $this->widget_id );
182	?>");
183	});
184
185	<?php
186	if ( 'new' === $this->state ) {
187	?>
188	chartSettings("<?php
189	echo esc_attr( $this->widget_id );
190	?>");
191	<?php
192	}
193	?>
194	});
195	</script>
196	<?php
197	}
198
199	/**
200	* Get chart data
201	*
202	* @param string $dbs Database name.
203	* @param string $query SQL query.
204	* @return array
205	*/
206	protected static function get_data( $dbs, $query ) {
207	$return_value = array(
208	'cols' => array(),
209	'rows' => array(),
210	'error' => '',
211	);
212	$wpdadb = WPDADB::get_db_connection( $dbs );
213	if ( null === $wpdadb ) {
214	$return_value['error'] = 'Database connection failed';
215	return $return_value;
216	}
217	$suppress = $wpdadb->suppress_errors( true );
218	// Get column info.
219	$wpdadb->query( "\n\t\t\t\tcreate temporary table widget as select * from (\n\t\t\t\t\t{$query}\n\t\t\t\t) resultset limit 0\n\t\t\t" );
220	if ( '' !== $wpdadb->last_error ) {
221	$return_value['error'] = $wpdadb->last_error;
222	return $return_value;
223	}
224	$cols = $wpdadb->get_results( 'desc widget', 'ARRAY_A' );
225	$cols_return = array();
226	foreach ( $cols as $col ) {
227	$cols_return[] = array(
228	'id' => $col['Field'],
229	'label' => $col['Field'],
230	'type' => self::google_charts_type( $col['Type'] ),
231	);
232	}
233	// Perform query.
234	$rows = $wpdadb->get_results( $query, 'ARRAY_A' );
235	$rows_return = array();
236	foreach ( $rows as $row ) {
237	$val = array();
238	$index = 0;
239	foreach ( $row as $col ) {
240	if ( 'number' === $cols_return[$index]['type'] ) {
241	if ( is_int( $col ) ) {
242	$col = intval( $col );
243	} else {
244	$col = floatval( $col );
245	}
246	} elseif ( 'date' === $cols_return[$index]['type'] \|\| 'datetime' === $cols_return[$index]['type'] ) {
247	$year = substr( $col, 0, 4 );
248	$month = strval( intval( substr( $col, 5, 2 ) ) - 1 );
249	// Month is zero based in JS
250	$day = substr( $col, 8, 2 );
251	if ( 'datetime' === $cols_return[$index]['type'] ) {
252	$hrs = substr( $col, 11, 2 );
253	$min = substr( $col, 14, 2 );
254	$sec = substr( $col, 17, 2 );
255	$col = "Date({$year},{$month},{$day},{$hrs},{$min},{$sec})";
256	} else {
257	$col = "Date({$year},{$month},{$day})";
258	}
259	} elseif ( 'timeofday' === $cols_return[$index]['type'] ) {
260	$hrs = substr( $col, 0, 2 );
261	$min = substr( $col, 3, 2 );
262	$sec = substr( $col, 6, 2 );
263	$col = "[{$hrs},{$min},{$sec},0]";
264	}
265	$val[] = array(
266	'v' => $col,
267	);
268	$index++;
269	}
270	$rows_return[] = array(
271	'c' => $val,
272	);
273	}
274	$wpdadb->suppress_errors( $suppress );
275	$return_value['cols'] = $cols_return;
276	$return_value['rows'] = $rows_return;
277	return $return_value;
278	}
279
280	/**
281	* Cache query result
282	*
283	* @param array $cached_data Cached meta data.
284	* @param string $widget_name Widget name.
285	* @param array $data Cached data.
286	* @return void
287	*/
288	protected static function write_cache( $cached_data, $widget_name, $data ) {
289	}
290
291	/**
292	* Read cached data
293	*
294	* @param string $filename File to read data from.
295	* @return false\|string\|void\|null
296	*/
297	protected static function read_cache( $filename ) {
298	}
299
300	/**
301	* Remove cached data for a specific chart widget
302	*
303	* @param array $cached_data Cached meta data.
304	* @param string $widget_name Widget name.
305	* @return void
306	*/
307	protected static function remove_cache( $cached_data, $widget_name ) {
308	}
309
310	/**
311	* Chart widget implementation
312	*
313	* @return void
314	*/
315	public static function widget() {
316	$panel_name = ( isset( $_REQUEST['wpda_panel_name'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_panel_name'] ) ) : '' );
317	$panel_dbs = ( isset( $_REQUEST['wpda_panel_dbs'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_panel_dbs'] ) ) : '' );
318	$panel_query = ( isset( $_REQUEST['wpda_panel_query'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_panel_query'] ) ) : '' );
319	$panel_column = ( isset( $_REQUEST['wpda_panel_column'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_panel_column'] ) ) : '1' );
320	$column_position = ( isset( $_REQUEST['wpda_column_position'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_column_position'] ) ) : 'prepend' );
321	$widget_sequence_nr = ( isset( $_REQUEST['wpda_widget_sequence_nr'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpda_widget_sequence_nr'] ) ) : '1' );
322	$wdg = new WPDA_Widget_Google_Chart(array(
323	'outputtype' => array('Table'),
324	'name' => $panel_name,
325	'dbs' => $panel_dbs,
326	'query' => $panel_query,
327	'column' => $panel_column,
328	'position' => $column_position,
329	'widget_id' => $widget_sequence_nr,
330	));
331	WPDA::sent_header( 'text/html; charset=UTF-8' );
332	// phpcs:disable WordPress.Security.EscapeOutput
333	echo $wdg->container();
334	// phpcs:enable WordPress.Security.EscapeOutput
335	wp_die();
336	}
337
338	/**
339	* Widget refresh implementation
340	*
341	* @return void
342	*/
343	public static function refresh() {
344	$is_header_send = false;
345	$dbs = null;
346	$query = null;
347	$widget_name = ( isset( $_POST['wpda_name'] ) ? sanitize_text_field( wp_unslash( $_POST['wpda_name'] ) ) : null );
348	if ( !$is_header_send ) {
349	WPDA::sent_header( 'application/json' );
350	}
351	if ( !isset( $_REQUEST['wpda_action'] ) ) {
352	// phpcs:disable WordPress.Security.EscapeOutput
353	echo static::msg( 'ERROR', 'Invalid arguments' );
354	// phpcs:enable WordPress.Security.EscapeOutput
355	wp_die();
356	}
357	if ( null === $dbs \|\| null === $query ) {
358	$widgets = get_user_meta( WPDA::get_current_user_id(), WPDA_Dashboard::USER_DASHBOARD );
359	$widget_found = false;
360	foreach ( $widgets as $widget ) {
361	if ( isset( $widget[$widget_name] ) ) {
362	$dbs = $widget[$widget_name]['chartDbs'];
363	$query = wp_unslash( $widget[$widget_name]['chartSql'] );
364	$widget_found = true;
365	}
366	}
367	if ( !$widget_found ) {
368	// phpcs:disable WordPress.Security.EscapeOutput
369	echo static::msg( 'ERROR', 'Invalid arguments' );
370	// phpcs:enable WordPress.Security.EscapeOutput
371	wp_die();
372	}
373	}
374	switch ( $_REQUEST['wpda_action'] ) {
375	case 'get_data':
376	echo wp_json_encode( static::get_data( $dbs, $query ) );
377	wp_die();
378	break;
379	case 'refresh':
380	// TODO ???
381	wp_die();
382	break;
383	default:
384	// phpcs:disable WordPress.Security.EscapeOutput
385	echo static::msg( 'ERROR', 'Invalid arguments' );
386	// phpcs:enable WordPress.Security.EscapeOutput
387	wp_die();
388	}
389	}
390
391	/**
392	* Supported data types for charts
393	*
394	* @param string $data_type Original data type.
395	* @return string
396	*/
397	public static function google_charts_type( $data_type ) {
398	$type = explode( '(', (string) $data_type );
399	// phpcs:ignore -- 8.1 proof
400	switch ( $type[0] ) {
401	case 'tinyint':
402	case 'smallint':
403	case 'mediumint':
404	case 'int':
405	case 'bigint':
406	case 'float':
407	case 'double':
408	case 'decimal':
409	case 'year':
410	return 'number';
411	case 'date':
412	return 'date';
413	case 'datetime':
414	case 'timestamp':
415	return 'datetime';
416	case 'time':
417	// TODO Timeofday returns an error in Google Charts
418	// Workaround = return time as string
419	// return 'timeofday';
420	// .
421	default:
422	return 'string';
423	}
424	}
425
426	}
427
428	// phpcs:enable WordPress.Security.NonceVerification.Recommended, WordPress.Security.NonceVerification.Missing