/**
* 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));
}
