/** * Generates and executes the query. * * @return array array of items */ public function get() { // Start building the query $sql = 'SELECT id, name, closed, date, votes_up, votes_down, '; $sql .= 'votes_up - votes_down AS votes_balance, '; $sql .= 'votes_up + votes_down AS votes_total, '; $sql .= 'votes_up / (votes_up + votes_down) * 100 AS votes_pct_up, '; $sql .= 'votes_down / (votes_up + votes_down) * 100 AS votes_pct_down '; $sql .= 'FROM ' . ThumbsUp::config('database_table_prefix') . 'items '; // Select only either open or closed items if ($this->closed !== NULL) { $where[] = 'closed = ' . (int) $this->closed; } // Select only either open or closed items if ($this->name !== NULL) { // Note: substr() is used to chop off the wrapping quotes $where[] = 'name LIKE "%' . substr(ThumbsUp::db()->quote($this->name), 1, -1) . '%"'; } // Append all query conditions if any if (!empty($where)) { $sql .= ' WHERE ' . implode(' AND ', $where); } // We need to order the results if ($this->orderby) { $sql .= ' ORDER BY ' . $this->orderby; } else { // Default order $sql .= ' ORDER BY name '; } // A limit has been set if ($this->limit) { $sql .= ' LIMIT ' . (int) $this->limit; } // Wrap this in an try/catch block just in case something goes wrong try { // Execute the query $sth = ThumbsUp::db()->prepare($sql); $sth->execute(array($this->name)); } catch (PDOException $e) { // Rethrow the exception in debug mode if (ThumbsUp::config('debug')) { throw $e; } // Otherwise, fail silently and just return an empty item array return array(); } // Initialize the items array that will be returned $items = array(); // Fetch all results while ($row = $sth->fetch(PDO::FETCH_OBJ)) { // Return an item_id => item_name array $items[] = array('id' => (int) $row->id, 'name' => $row->name, 'closed' => (bool) $row->closed, 'date' => (int) $row->date, 'votes_up' => (int) $row->votes_up, 'votes_down' => (int) $row->votes_down, 'votes_pct_up' => (double) $row->votes_pct_up, 'votes_pct_down' => (double) $row->votes_pct_down, 'votes_balance' => (int) $row->votes_balance, 'votes_total' => (int) $row->votes_total); } return $items; }
/** * Shows the admin dashboard: an overview of all ThumbsUp items. * * @return void */ public function action_dashboard() { $template = new ThumbsUp_Template(THUMBSUP_DOCROOT . 'admin/html/dashboard.php'); // Filter on closed $filter_closed = (isset($_GET['filter_closed']) and preg_match('/^[01]$/D', (string) $_GET['filter_closed'])) ? (int) $_GET['filter_closed'] : ''; if ($filter_closed !== '') { $sql_filter[] = 'closed = ' . $filter_closed; } // Filter on name $filter_name = isset($_GET['filter_name']) ? (string) $_GET['filter_name'] : ''; if ($filter_name !== '') { $sql_filter[] = 'name LIKE "%' . substr(ThumbsUp::db()->quote($filter_name), 1, -1) . '%"'; } // Build a WHERE clause if needed $sql_where = empty($sql_filter) ? '' : ' WHERE ' . implode(' AND ', $sql_filter); // Count the total items $sth = ThumbsUp::db()->prepare('SELECT COUNT(1) FROM ' . ThumbsUp::config('database_table_prefix') . 'items ' . $sql_where); $sth->execute(); $total_items = (int) $sth->fetchColumn(); // Build the dropdown options for items_per_page foreach (array(10, 20, 50, 100, 200, 500, 1000, 2000, 5000) as $i) { $items_per_page_select[] = $i; // Don't bother showing values higher than needed if ($total_items < $i) { break; } } // Grab the current items_per_page setting if (isset($_GET['items_per_page'])) { // A value of "0" equals "view all" $items_per_page = $_GET['items_per_page'] === '0' ? 0 : max(1, (int) $_GET['items_per_page']); } else { // Default value $items_per_page = min(50, end($items_per_page_select)); } // Support custom entered items_per_page GET values if ($items_per_page !== 0 and !in_array($items_per_page, $items_per_page_select)) { $items_per_page_select[] = $items_per_page; sort($items_per_page_select); } // Add an option for "view all" at the end $items_per_page_select[] = 0; // More pagination variables $total_pages = $items_per_page === 0 ? 1 : max(1, (int) ceil($total_items / $items_per_page)); $page = isset($_GET['page']) ? min($total_pages, max(1, (int) $_GET['page'])) : 1; // Limit the results if "view all" has not been selected $sql_limit = $items_per_page === 0 ? '' : ' LIMIT ' . $items_per_page . ' OFFSET ' . ($page - 1) * $items_per_page; // Load the items $sth = ThumbsUp::db()->prepare('SELECT id, name, date, closed, votes_up, votes_down, votes_up - votes_down AS votes_balance, votes_up + votes_down AS votes_total, votes_up / (votes_up + votes_down) * 100 AS votes_pct_up, votes_down / (votes_up + votes_down) * 100 AS votes_pct_down FROM ' . ThumbsUp::config('database_table_prefix') . 'items ' . $sql_where . ' ORDER BY name ' . $sql_limit); $sth->execute(); $items = array(); while ($row = $sth->fetch(PDO::FETCH_OBJ)) { $items[(int) $row->id] = $row; } // Pass on all data we need to the template $template->filter_closed = $filter_closed; $template->filter_name = $filter_name; $template->page = $page; $template->total_items = $total_items; $template->total_pages = $total_pages; $template->items_per_page = $items_per_page; $template->items_per_page_select = $items_per_page_select; $template->items = $items; echo $template->render(); }
/** * Deletes the item and all votes for it. * * @return void */ public function delete() { // Delete all registered votes for this item $sth = ThumbsUp::db()->prepare('DELETE FROM ' . ThumbsUp::config('database_table_prefix') . 'votes WHERE item_id = ?'); $sth->execute(array($this->id)); // Delete the item itself $sth = ThumbsUp::db()->prepare('DELETE FROM ' . ThumbsUp::config('database_table_prefix') . 'items WHERE id = ?'); $sth->execute(array($this->id)); }