poll-maker / includes / lists / class-poll-maker-results-list-table.php

class-poll-maker-results-list-table.php in Poll Maker by AYS – Versus Polls, Anonymous Polls, Image Polls 6.3.5, at includes/lists/class-poll-maker-results-list-table.php

606 lines 17.8 KB

Raw Download Zip

1	<?php
2	ob_start();
3
4	class Pma_Results_List_Table extends WP_List_Table {
5	private $plugin_name;
6	private $results_obj;
7	private $title_length;
8
9	/** Class constructor */
10	public function __construct( $plugin_name ) {
11	$this->plugin_name = $plugin_name;
12	$this->title_length = Poll_Maker_Ays_Admin::get_listtables_title_length('results');
13	parent::__construct(array(
14	'singular' =>esc_html__('Result', "poll-maker"), //singular name of the listed records
15	'plural' =>esc_html__('Results', "poll-maker"), //plural name of the listed records
16	'ajax' => false, //does this table support ajax?
17	));
18	add_action('admin_notices', array($this, 'results_notices'));
19
20	}
21
22	/**
23	* Retrieve customers data from the database
24	*
25	* @param int $per_page
26	* @param int $page_number
27	*
28	* @return mixed
29	*/
30	public static function get_reports( $per_page = 50, $page_number = 1 ) {
31
32	global $wpdb;
33
34	if (isset($_SERVER['HTTP_HOST']) && sanitize_text_field($_SERVER['HTTP_HOST']) == "playground.wordpress.net") {
35	$sql = "SELECT
36	{$wpdb->prefix}ayspoll_polls.id,
37	SUM(CAST({$wpdb->prefix}ayspoll_answers.votes AS INTEGER)) AS voted,
38	{$wpdb->prefix}ayspoll_answers.poll_id,
39	{$wpdb->prefix}ayspoll_polls.categories
40	FROM {$wpdb->prefix}ayspoll_answers
41	INNER JOIN {$wpdb->prefix}ayspoll_polls
42	ON {$wpdb->prefix}ayspoll_answers.poll_id = {$wpdb->prefix}ayspoll_polls.id ";
43	} else {
44	$sql = "SELECT
45	{$wpdb->prefix}ayspoll_polls.id,
46	SUM({$wpdb->prefix}ayspoll_answers.votes) AS voted,
47	{$wpdb->prefix}ayspoll_answers.poll_id,
48	{$wpdb->prefix}ayspoll_polls.categories
49	FROM {$wpdb->prefix}ayspoll_answers
50	INNER JOIN {$wpdb->prefix}ayspoll_polls
51	ON {$wpdb->prefix}ayspoll_answers.poll_id = {$wpdb->prefix}ayspoll_polls.id ";
52	}
53
54	if (isset($_REQUEST['orderbypoll']) && $_REQUEST['orderbypoll'] > 0) {
55	$poll_id = absint(sanitize_text_field( $_REQUEST['orderbypoll'] ));
56
57	$sql .= " AND {$wpdb->prefix}ayspoll_reports.answer_id IN (SELECT {$wpdb->prefix}ayspoll_answers.id FROM {$wpdb->prefix}ayspoll_answers WHERE {$wpdb->prefix}ayspoll_answers.poll_id='$poll_id')";
58	}
59
60	if (isset($_REQUEST['orderbycat']) && $_REQUEST['orderbycat'] > 0) {
61	$cat_id = absint(sanitize_text_field( $_REQUEST['orderbycat'] ));
62	$sql .= " AND {$wpdb->prefix}ayspoll_polls.categories LIKE('%,{$cat_id},%')";
63	}
64
65	$sql .= "GROUP BY {$wpdb->prefix}ayspoll_answers.poll_id";
66	if (!empty($_REQUEST['orderby'])) {
67	$order_by = ( isset( $_REQUEST['orderby'] ) && sanitize_text_field( $_REQUEST['orderby'] ) != '' ) ? sanitize_text_field( $_REQUEST['orderby'] ) : 'id';
68	$order_by = str_replace('id', $wpdb->prefix . 'ayspoll_polls.id', $order_by);
69	$order_by .= ( ! empty( $_REQUEST['order'] ) && strtolower( $_REQUEST['order'] ) == 'asc' ) ? ' ASC' : ' DESC';
70
71	$sql_orderby = sanitize_sql_orderby($order_by);
72
73	if ( $sql_orderby ) {
74	$sql .= ' ORDER BY ' . $sql_orderby;
75	} else {
76	$sql .= ' ORDER BY ' . $wpdb->prefix . 'ayspoll_polls.id DESC';
77	}
78	} else {
79	$sql .= ' ORDER BY ' . $wpdb->prefix . 'ayspoll_polls.id DESC';
80	}
81
82	$sql .= " LIMIT %d";
83	$args[] = $per_page;
84	$offset = ($page_number - 1) * $per_page;
85	$sql .= " OFFSET %d";
86	$args[] = $offset;
87
88	$result = $wpdb->get_results(
89	$wpdb->prepare( $sql, $args),
90	'ARRAY_A'
91	);
92
93	return $result;
94	}
95
96	public function display_tablenav( $which ) {
97	?>
98	<div class="tablenav <?php echo esc_attr( $which ); ?>">
99
100	<div class="alignleft actions">
101	<?php $this->bulk_actions( $which ); ?>
102	</div>
103	<?php
104	$this->extra_tablenav( $which );
105	$this->pagination( $which );
106	?>
107	<br class="clear" />
108	</div>
109	<?php
110	}
111
112	public function extra_tablenav($which) {
113	$this->ays_category_filter_content();
114	}
115
116	public function get_report_by_id( $id ) {
117	global $wpdb;
118	$report_id = absint(sanitize_text_field($id));
119	$report_table = esc_sql($wpdb->prefix."ayspoll_reports");
120	$sql = "SELECT * FROM ".$report_table." WHERE id=%d";
121	$result = $wpdb->get_row(
122	$wpdb->prepare( $sql, $report_id),
123	'ARRAY_A'
124	);
125
126	return $result;
127	}
128
129	public static function get_answer_by_id( $id ) {
130	global $wpdb;
131
132	$answ_id = absint(sanitize_text_field($id));
133	$answ_table = esc_sql($wpdb->prefix."ayspoll_answers");
134
135	$sql = "SELECT * FROM ".$answ_table." WHERE id=%d";
136
137	$result = $wpdb->get_row(
138	$wpdb->prepare( $sql, $answ_id),
139	'ARRAY_A'
140	);
141
142	return $result;
143	}
144
145	public function get_polls() {
146	global $wpdb;
147
148	$poll_table = esc_sql($wpdb->prefix."ayspoll_polls");
149	$sql = "SELECT * FROM ".$poll_table;
150
151	$result = $wpdb->get_results($sql, 'ARRAY_A');
152
153	return $result;
154	}
155
156	/**
157	* Delete a customer record.
158	*
159	* @param int $id customer ID
160	*/
161	public static function delete_reports( $id ) {
162
163	global $wpdb;
164
165	$wpdb->update(
166	"{$wpdb->prefix}ayspoll_answers",
167	array(
168	"votes" => 0
169	),
170	array(
171	'poll_id' => $id
172	)
173	);
174
175	if (isset($_SERVER['HTTP_HOST']) && sanitize_text_field($_SERVER['HTTP_HOST']) == "playground.wordpress.net") {
176	$sql = "DELETE FROM {$wpdb->prefix}ayspoll_reports
177	WHERE answer_id IN (
178	SELECT id FROM {$wpdb->prefix}ayspoll_answers
179	WHERE poll_id = $id
180	)";
181	} else {
182	$sql = "DELETE r FROM {$wpdb->prefix}ayspoll_reports as r
183	JOIN {$wpdb->prefix}ayspoll_answers ON {$wpdb->prefix}ayspoll_answers.id = r.answer_id
184	WHERE {$wpdb->prefix}ayspoll_answers.poll_id = $id";
185	}
186
187	$res = $wpdb->query($sql);
188
189	return $res > 0;
190	}
191
192	/**
193	* Returns the count of records in the database.
194	*
195	* @return null\|string
196	*/
197	public static function record_count() {
198	global $wpdb;
199
200	$sql = "SELECT COUNT(*) FROM {$wpdb->prefix}ayspoll_polls";
201	return $wpdb->get_var($sql);
202	}
203
204	/** Text displayed when no customer data is available */
205	public function no_items() {
206	_e('There are no results yet.', "poll-maker");
207	}
208
209	/**
210	* Render a column when no column specific method exist.
211	*
212	* @param array $item
213	* @param string $column_name
214	*
215	* @return mixed
216	*/
217	public function column_default( $item, $column_name ) {
218	switch ( $column_name ) {
219	case 'poll_title':
220	case 'voted':
221	case 'unread':
222	case 'id':
223	return $item[$column_name];
224	break;
225	default:
226	return print_r($item, true); //Show the whole array for troubleshooting purposes
227	}
228	}
229
230	/**
231	* Render the bulk edit checkbox
232	*
233	* @param array $item
234	*
235	* @return string
236	*/
237	function column_cb( $item ) {
238	return sprintf(
239	'<input type="checkbox" name="bulk-action[]" value="%s">', $item['id']
240	);
241	}
242
243	function column_poll_title( $item ) {
244	global $wpdb;
245	$delete_nonce = wp_create_nonce($this->plugin_name . '-delete-result');
246
247	$res = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_polls WHERE id={$item['id']}", "ARRAY_A");
248
249	$restitle = Poll_Maker_Ays_Admin::ays_restriction_string("word",stripcslashes($res['title']), $this->title_length);
250	$title = sprintf('<a href="?page=%s-each&poll=%d&title=%s">' . $restitle . '</a>', esc_attr($_REQUEST['page']), absint($item['id']), stripslashes($res['title']));
251
252	$actions = [
253	'delete' => sprintf(
254	'<a href="?page=%s&action=%s&result=%s&_wpnonce=%s">%s</a>',
255	esc_attr( $_REQUEST['page'] ),
256	'delete',
257	absint( $item['id'] ),
258	$delete_nonce,
259	__( 'Delete', 'poll-maker' )
260	),
261	];
262
263	return $title . $this->row_actions($actions);
264	}
265
266	function get_poll_title( $item ) {
267	global $wpdb;
268	$delete_nonce = wp_create_nonce($this->plugin_name . '-delete-result');
269
270	$result = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_answers WHERE id={$item['answer_id']}", "ARRAY_A");
271
272	$res = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_polls WHERE id={$result['poll_id']}", "ARRAY_A");
273
274	return stripslashes($res['title']);
275	}
276
277	function column_answer( $item ) {
278	global $wpdb;
279	$delete_nonce = wp_create_nonce($this->plugin_name . '-delete-result');
280
281	$result = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_answers WHERE id={$item['answer_id']}", "ARRAY_A");
282
283	$poll_type = $this->get_poll_type($result['poll_id']);
284
285	$result['answer'] = $poll_type['type'] == 'voting' && $result['answer'] == '1' ? 'Like' : $result['answer'];
286	$result['answer'] = $poll_type['type'] == 'voting' && $result['answer'] == '-1' ? 'Dislike' : $result['answer'];
287
288	return stripslashes($result['answer']);
289	}
290
291	function column_voted( $item ) {
292	global $wpdb;
293	$sql = "SELECT
294	COUNT({$wpdb->prefix}ayspoll_reports.id)
295	FROM
296	{$wpdb->prefix}ayspoll_reports
297	JOIN {$wpdb->prefix}ayspoll_answers ON {$wpdb->prefix}ayspoll_answers.id = {$wpdb->prefix}ayspoll_reports.answer_id
298	WHERE {$wpdb->prefix}ayspoll_answers.poll_id = {$item['id']}
299	GROUP BY {$wpdb->prefix}ayspoll_answers.poll_id";
300	$res = $wpdb->get_var($sql);
301
302	return $res ? $res : 0;
303	}
304
305	function column_unread( $item ) {
306	global $wpdb;
307	$sql = "SELECT
308	COUNT({$wpdb->prefix}ayspoll_reports.unread)
309	FROM
310	{$wpdb->prefix}ayspoll_reports
311	JOIN {$wpdb->prefix}ayspoll_answers ON {$wpdb->prefix}ayspoll_answers.id = {$wpdb->prefix}ayspoll_reports.answer_id
312	WHERE {$wpdb->prefix}ayspoll_answers.poll_id = {$item['id']} AND {$wpdb->prefix}ayspoll_reports.unread = 1
313	GROUP BY {$wpdb->prefix}ayspoll_answers.poll_id";
314	$res = $wpdb->get_var($sql);
315	$result = $res ? $res : 0;
316	$unread = $res ? 'ays_poll_unread' : '';
317	$count = sprintf('<span class="%s">' . $result . '</span>', $unread);
318	return $count;
319	}
320
321	/**
322	* Associative array of columns
323	*
324	* @return array
325	*/
326	function get_columns() {
327	$columns = array(
328	'cb' => '<input type="checkbox" />',
329	'poll_title' =>esc_html__('Poll',"poll-maker"),
330	'voted' =>esc_html__('Voters count',"poll-maker"),
331	'unread' =>esc_html__('New results count',"poll-maker"),
332	'id' =>esc_html__('ID',"poll-maker")
333	);
334
335	return $columns;
336	}
337
338	/**
339	* Columns to make sortable.
340	*
341	* @return array
342	*/
343	public function get_sortable_columns() {
344	$sortable_columns = array(
345	'vote_date' => array('vote_date', true),
346	'id' => array('id', true),
347	);
348
349	return $sortable_columns;
350	}
351
352	/**
353	* Returns an associative array containing the bulk action
354	*
355	* @return array
356	*/
357	public function get_bulk_actions() {
358	$actions = array(
359	'bulk-read' =>esc_html__('Mark as read', "poll-maker"),
360	'bulk-delete' =>esc_html__('Delete', "poll-maker"),
361	);
362
363	return $actions;
364	}
365
366	/**
367	* Handles data query and filter, sorting, and pagination.
368	*/
369	public function prepare_items() {
370
371	$this->_column_headers = $this->get_column_info();
372
373	/** Process bulk action */
374	$this->process_bulk_action();
375
376	$per_page = $this->get_items_per_page('poll_results_per_page', 50);
377
378	$current_page = $this->get_pagenum();
379	$total_items = self::record_count();
380
381	$this->set_pagination_args(array(
382	'total_items' => $total_items, //WE have to calculate the total number of items
383	'per_page' => $per_page, //WE have to determine how many items to show on a page
384	));
385
386	$this->items = self::get_reports($per_page, $current_page);
387	}
388
389	public function process_bulk_action() {
390	//Detect when a bulk action is being triggered...
391
392	if ('delete' === $this->current_action()) {
393
394	// In our file that handles the request, verify the nonce.
395	$nonce = esc_attr($_REQUEST['_wpnonce']);
396
397	if (!wp_verify_nonce($nonce, $this->plugin_name . '-delete-result')) {
398	die('Go get a life script kiddies');
399	} else {
400	self::delete_reports(absint($_GET['result']));
401
402	// esc_url_raw() is used to prevent converting ampersand in url to "#038;"
403	// add_query_arg() return the current url
404	$message = 'deleted';
405	$url = esc_url_raw(remove_query_arg([
406	'action',
407	'result',
408	'_wpnonce'
409	])) . '&status=' . $message;
410	wp_redirect($url);
411	}
412
413	}
414
415	// Process bulk-delete action
416	if ((isset($_POST['action']) && $_POST['action'] == 'bulk-delete')
417	\|\| (isset($_POST['action2']) && $_POST['action2'] == 'bulk-delete')
418	) {
419
420	if (!empty($_POST['bulk-action']) && is_array($_POST['bulk-action'])) {
421	$delete_ids = array_map('intval', $_POST['bulk-action']);
422
423	foreach ($delete_ids as $id) {
424	self::delete_reports($id);
425	}
426	}
427
428	// esc_url_raw() is used to prevent converting ampersand in url to "#038;"
429	// add_query_arg() return the current url
430
431	$message = 'deleted';
432	$url = esc_url_raw(remove_query_arg(['action', 'result', '_wpnonce'])) . '&status=' . $message;
433	wp_redirect($url);
434	} elseif ((isset($_POST['action']) && 'bulk-read' == $_POST['action'])
435	\|\| (isset($_POST['action2']) && 'bulk-read' == $_POST['action2'])
436	) {
437
438	if (empty($_POST['bulk-action']) \|\| !is_array($_POST['bulk-action'])) {
439	return;
440	}
441
442	$read_ids = array_map('intval', $_POST['bulk-action']);
443
444	// loop over the array of record IDs and mark as readed them
445	foreach ( $read_ids as $id ) {
446	echo $id . "<br>";
447	self::mark_as_read_reports($id);
448	}
449
450	// esc_url_raw() is used to prevent converting ampersand in url to "#038;"
451	// add_query_arg() return the current url
452
453	$message = 'read';
454	$url = esc_url_raw(remove_query_arg(['action', 'result', '_wpnonce'])) . '&status=' . $message;
455	wp_redirect($url);
456	}
457	}
458
459	/**
460	* Mark as read a result record.
461	*
462	* @param int $id result ID
463	*/
464	public static function mark_as_read_reports( $id ) {
465	global $wpdb;
466	$sql = "UPDATE {$wpdb->prefix}ayspoll_reports
467	JOIN {$wpdb->prefix}ayspoll_answers ON {$wpdb->prefix}ayspoll_answers.id = {$wpdb->prefix}ayspoll_reports.answer_id
468	SET {$wpdb->prefix}ayspoll_reports.unread = 0
469	WHERE {$wpdb->prefix}ayspoll_answers.poll_id = $id";
470	$res = $wpdb->query($sql);
471
472	return $res > 0 ? true : false;
473	}
474
475	public function results_notices() {
476	$status = (isset($_REQUEST['status'])) ? sanitize_text_field($_REQUEST['status']) : '';
477
478	if (empty($status)) {
479	return;
480	}
481
482	if ('deleted' == $status) {
483	$updated_message = esc_html( esc_html__('Result deleted.', "poll-maker"));
484	}
485
486	if (empty($updated_message)) {
487	return;
488	}
489
490	?>
491	<div class="ays-poll-admin-notice notice notice-success is-dismissible">
492	<p> <?php echo $updated_message; ?> </p>
493	</div>
494	<?php
495	}
496
497	public function get_poll_type($item) {
498	global $wpdb;
499
500	$id = absint(sanitize_text_field($item));
501
502	$poll_table = esc_sql($wpdb->prefix."ayspoll_polls");
503	$sql = "SELECT type FROM ".$poll_table." WHERE id=%d";
504
505	$result = $wpdb->get_row(
506	$wpdb->prepare( $sql, $id),
507	'ARRAY_A'
508	);
509
510	return $result;
511	}
512
513	public function ays_category_filter_content(){
514	$poll_cats = $this->get_categories();
515	$content = "";
516	if(isset($poll_cats)){
517	$content = '<label for="bulk-action-selector-top-cat" class="screen-reader-text">Select Filter Type</label>
518	<select name="orderbycat" id="bulk-action-selector-top-cat">
519	<option value="0" selected>' . esc_html__("Select Category", "poll-maker"). '</option>';
520	$selected = "";
521	$this_cat_id = 0;
522	foreach ($poll_cats as $cat_key => $cat_value) {
523	$selected = (isset($_REQUEST['orderbycat']) && $_REQUEST['orderbycat'] == $cat_value['id'] ) ? 'selected' : '';
524	$this_cat_id = isset($_REQUEST['orderbycat']) ? $_REQUEST['orderbycat'] : $this_cat_id;
525	$cat_id = isset($cat_value['id']) && $cat_value['id'] != "" ? esc_attr($cat_value['id']) : "";
526	$cat_value = isset($cat_value['title']) && $cat_value['title'] != "" ? esc_attr($cat_value['title']) : "";
527	$content .= '<option value="'.$cat_id.'" '.$selected.'>'.$cat_value.'</option>';
528	}
529	$content .= '</select>';
530	$content .= '<input type="submit" id="doactioncat" name="filter_by_cat" class="button action" value="'. esc_html__("Filter", "poll-maker").'" style="width: 3.7rem;margin-left: 5px;">';
531	if(isset($_REQUEST['filter_by_cat'])){
532	$new_url = remove_query_arg("orderbycat")."&orderbycat=".$this_cat_id;
533	wp_redirect($new_url);
534	}
535	}
536	echo $content;
537	}
538
539	public function get_categories() {
540	global $wpdb;
541	$category_table = $wpdb->prefix . 'ayspoll_categories';
542
543	$sql = "SELECT id,title FROM ".$category_table." ORDER BY title";
544	$results = $wpdb->get_results($sql , "ARRAY_A");
545
546	if(isset($results) && !empty($results)){
547	return $results;
548	}
549	else{
550	return array();
551	}
552	}
553
554	// Get answers modified
555	public function get_poll_answers($poll_id){
556	global $wpdb;
557
558	$sql = "SELECT * FROM `{$wpdb->prefix}ayspoll_reports` WHERE poll_id=$poll_id";
559	$result = $wpdb->get_results($sql, "ARRAY_A");
560
561	$multivote_answers_ids = array();
562	$multivote_answers = array();
563
564	foreach ($result as $r_key => $r_value) {
565
566	$r_value['multi_answer_id'] = json_decode($r_value['multi_answer_ids'], true);
567
568	$multivote_res = false;
569	if (isset($r_value['multi_answer_id']) && count($r_value['multi_answer_id']) > 0) {
570	$multivote_res = true;
571	}
572
573	if ($multivote_res) {
574	foreach ($r_value['multi_answer_id'] as $m_key => $m_val) {
575	$multivote_answers_ids[] = $m_val;
576	$multi_answer = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_answers WHERE id=".$m_val, "ARRAY_A");
577	$multivote_answers[ $m_val ] = $multi_answer['answer'];
578	}
579	$answ_poll_id = $multi_answer['poll_id'];
580	} else {
581	$answer = $wpdb->get_row("SELECT * FROM {$wpdb->prefix}ayspoll_answers WHERE id={$r_value['answer_id']}", "ARRAY_A");
582	$multivote_answers_ids[] = $r_value['answer_id'];
583	$multivote_answers[ $r_value['answer_id'] ] = $answer['answer'];
584	$answ_poll_id = $answer['poll_id'];
585	}
586	}
587
588	$multivote_answers_count_arr = array_count_values( $multivote_answers_ids );
589
590	$res = array();
591	foreach ($multivote_answers_count_arr as $key => $value) {
592	$res[] = array(
593	"votes" => $value,
594	"answer" => $multivote_answers[ $key ],
595	);
596	}
597
598	if ( is_null( $res ) \|\| empty( $res ) ) {
599	$res = array();
600	}
601
602	return $res;
603	}
604
605	}
606