/**
* Opens a SQLite database; automatically creates the database if it does not exist yet.
* Also automatically installs the tables needed by ThumbsUp: items and votes.
* Returns an SQLiteDatabase object, which represents an open SQLite database.
* Note: using a method like this allows you to lazy load the database, the database will
* only be touched if/when this method is called for the first time.
*
* @return object SQLiteDatabase
*/
public static function db()
{
// Database not opened yet
if (self::$db === NULL) {
// Open or create the database if it does not exist yet
self::$db = new SQLiteDatabase(THUMBSUP_DATABASE, 0666, $error) or exit($error);
// Retrieve the names of all tables in the database
$tables = (array) self::$db->singleQuery('SELECT name FROM sqlite_master WHERE type = \'table\'');
// Create the items table if it does not exist
if (!in_array('items', $tables)) {
self::$db->queryExec("\n\t\t\t\t\tCREATE TABLE items (\n\t\t\t\t\t\tid INTEGER AUTOINCREMENT,\n\t\t\t\t\t\tname TEXT,\n\t\t\t\t\t\tclosed INTEGER,\n\t\t\t\t\t\tdate INTEGER,\n\t\t\t\t\t\tPRIMARY KEY (id),\n\t\t\t\t\t\tUNIQUE (name) )");
}
// Create the votes table if it does not exist
if (!in_array('votes', $tables)) {
self::$db->queryExec("\n\t\t\t\t\tCREATE TABLE votes (\n\t\t\t\t\t\tid INTEGER AUTOINCREMENT,\n\t\t\t\t\t\titem_id INTEGER,\n\t\t\t\t\t\trating INTEGER,\n\t\t\t\t\t\tip TEXT,\n\t\t\t\t\t\tdate INTEGER,\n\t\t\t\t\t\tPRIMARY KEY (id) )");
}
}
// Return SQLiteDatabase object
return self::$db;
}
/**
* Completely deletes an item, with all votes.
* TODO wordpress handles.
* @return void
*/
public function action_delete()
{
// Here to track down any references.
wp_die("You don't need me anymore. Deleting items is Wordpress's job");
// We need an item id
if (empty($_POST['post_id'])) {
$error = 'No item ID posted.';
} else {
// Clean item id
$post_id = (int) $_POST['post_id'];
// Delete both the item and the votes for it
ThumbsUp_Database::db()->queryExec("\n\t\t\t\tDELETE FROM items\n\t\t\t\tWHERE id = {$post_id};\n\t\t\t\tDELETE FROM votes\n\t\t\t\tWHERE post_id = {$post_id}");
}
if ($this->is_ajax) {
// Return an error, or null if everything went fine
exit(json_encode(isset($error) ? $error : NULL));
}
// If this is not an AJAX request, print the error
if (!empty($error)) {
$this->template->error = $error;
}
// Show the item overview again
$this->action_dashboard();
}
/**
* Loads a previous vote from the user for the current item; stores it in item[vote].
* item[vote] = array with vote data (rating, date) if a previous vote was found
* = TRUE if the user previously voted, but we couldn't retrieve the rating
* = FALSE if the user has note voted on the item yet, for as far as we know
*
* @return boolean TRUE if a new/previous vote is found; FALSE otherwise
*/
protected function load_vote()
{
// No item loaded yet
if (empty($this->item['item_id'])) {
return FALSE;
}
global $current_user;
// A new vote was cast for the current item
if (isset($this->new_vote['item_id']) and $this->new_vote['item_id'] == $this->item['item_id']) {
// Transfer new vote data to the item property
$this->item['vote'] = $this->new_vote;
// This vote is only new, if it was really registered
$this->item['vote']['new'] = empty($this->new_vote['error']);
// The item id is stored in item[id] already
unset($this->item['vote']['item_id']);
// Vote found
return TRUE;
}
// TODO Implement Session Manager or regular ip search.
// Currently the config is false so this part shouldn't run.
// Config can hold if session manager is enabled, as well as
// allow people to vote anonomously.
// We can only retrieve a previous vote via IP
if ($this->config['ip_check'] and !empty($this->ip)) {
// TODO
$vote = ThumbsUp_Database::db()->arrayQuery("\n\t\t\t\tSELECT rating, ip, date\n\t\t\t\tFROM votes\n\t\t\t\tWHERE item_id = {$this->item['ID']} AND ip = '{$this->ip}'\n\t\t\t\tLIMIT 1", SQLITE_ASSOC);
} else {
global $blog_id, $wpdb;
//get_currentuserinfo();
$select = $wpdb->prepare("SELECT rating, user_id, date\n\t\t\t\tFROM {$this->tblname}\n\t\t\t\tWHERE item_id=%d AND user_id=%d AND blog_id=%d", $this->item['item_id'], $current_user->ID, $blog_id);
$vote = $wpdb->get_row($select);
}
// Previous vote found
// TODO I believe this should work the same for the wpdb results.
if (!empty($vote)) {
$this->item['vote'] = current($vote);
$this->item['vote']['new'] = FALSE;
// Vote found
return TRUE;
}
// If no previous vote was found via IP or user, we can still look at the cookie just to
// determine whether the user already voted on this item. We won't we able to look up
// the rating, though, so we set item[vote] to a boolean.
return $this->item['vote'] = isset($this->cookie[$this->item['item_id']]);
}
/**
* Loads and calculates the vote results for the current item. Stores them in item[results].
*
* @return boolean TRUE if results were loaded; FALSE otherwise
*/
protected function load_results()
{
// No item loaded yet
if (empty($this->item['id'])) {
return FALSE;
}
// Join-query to retrieve vote results
$results = ThumbsUp_Database::db()->arrayQuery("\n\t\t\tSELECT\n\t\t\t\tCOUNT(1) AS total_votes,\n\t\t\t\tSUM(v.rating) AS positive_votes,\n\t\t\t\tMAX(v.date) AS last_vote_date\n\t\t\tFROM items i\n\t\t\tJOIN votes v ON i.id = v.item_id\n\t\t\tWHERE i.id = {$this->item['id']}\n\t\t\tGROUP BY i.id", SQLITE_ASSOC);
// Nobody voted on this item yet
if (empty($results)) {
// Initialize result array manually
$this->item['results'] = array('total_votes' => 0, 'positive_votes' => 0, 'votes_balance' => 0, 'negative_votes' => 0, 'positive_percentage' => 0, 'negative_percentage' => 0, 'last_vote_date' => NULL);
// We're done
return TRUE;
}
// Load the results into the class
$this->item['results'] = current($results);
// Add our own extra result data, calculated by PHP instead of SQL
$this->item['results']['negative_votes'] = $this->item['results']['total_votes'] - $this->item['results']['positive_votes'];
$this->item['results']['votes_balance'] = $this->item['results']['positive_votes'] - $this->item['results']['negative_votes'];
$this->item['results']['positive_percentage'] = $this->item['results']['positive_votes'] / $this->item['results']['total_votes'] * 100;
$this->item['results']['negative_percentage'] = 100 - $this->item['results']['positive_percentage'];
return TRUE;
}
