private static function _getVotesOverTime($vars, $users = false)
{
$time = time();
$startDate = Lib\Url::GetInt('startDate', $time - 3600 * 24, $vars);
// Default to the last 24 hours
$endDate = Lib\Url::GetInt('endDate', $time, $vars);
$bracketId = Lib\Url::GetInt('bracketId', null, $vars);
$granularity = Lib\Url::GetInt('granularity', 2, $vars);
$cacheKey = '_getVotesOverTime_' . implode('_', [$startDate, $endDate, $bracketId, $granularity, $users]);
$retVal = Lib\Cache::Get($cacheKey);
if (false === $retVal && $bracketId) {
$selectCount = $users ? 'DISTINCT user_id' : '1';
$result = Lib\Db::Query('SELECT COUNT(' . $selectCount . ') AS total, DATE(FROM_UNIXTIME(vote_date)) AS date, HOUR(FROM_UNIXTIME(vote_date)) AS hour, (MINUTE(FROM_UNIXTIME(vote_date)) % :granularity) AS hour_fraction FROM votes WHERE bracket_id = :bracketId AND vote_date BETWEEN :start AND :end GROUP BY date, hour, hour_fraction ORDER BY date, hour, hour_fraction', [':granularity' => $granularity, ':bracketId' => $bracketId, ':start' => $startDate, ':end' => $endDate]);
if ($result && $result->count) {
$retVal = [];
while ($row = Lib\Db::Fetch($result)) {
$obj = new stdClass();
$obj->date = (int) $row->date;
$obj->hour = (int) $row->hour;
$obj->minutes = $row->hour_fraction == 0 ? 0 : 60 * ((int) $row->hour_fraction / $granularity);
$obj->count = (int) $row->total;
$retVal[] = $obj;
}
Lib\Cache::Set($cacheKey, $retVal, STATS_CACHE_DURATION);
}
}
return $retVal;
}
public static function generate(array $params)
{
$bracket = self::_getBracket(array_shift($params));
if ($bracket) {
$handle = fopen('php://output', 'wb');
if ($handle) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename=' . $bracket->perma . '.csv');
// I generally don't like doing queries in a controller, but it's going
// to be much lighter weight to dump the data from the query directly
// out to the stream.
$query = 'SELECT v.vote_date, c.character_name, r.round_tier, r.round_group ';
$query .= 'FROM `votes` v INNER JOIN `round` r ON r.round_id = v.round_id ';
$query .= 'INNER JOIN `character` c ON c.character_id = v.character_id ';
$query .= 'WHERE v.bracket_id = :bracketId';
$result = Lib\Db::Query($query, [':bracketId' => $bracket->id]);
if ($result && $result->count) {
fputcsv($handle, ['Date', 'Entrant', 'Round', 'Group']);
while ($row = Lib\Db::Fetch($result)) {
fputcsv($handle, [date('c', $row->vote_date), $row->character_name, $row->round_tier, $row->round_group]);
}
}
fclose($handle);
}
}
exit;
}
public static function getByName($userName)
{
$retVal = null;
$result = Lib\Db::Query('SELECT * FROM users WHERE user_name = :userName', [':userName' => $userName]);
if ($result && $result->count) {
$retVal = new User(Lib\Db::Fetch($result));
}
return $retVal;
}
protected static function _main($message = null, $force = false)
{
$out = new stdClass();
$out->brackets = Api\Bracket::getUserOwnedBrackets(self::$_user, $force);
// If there's no message passed directly, check for one from cache
$message = !$message ? self::_getStashedMessage() : $message;
if ($out->brackets) {
// Check for card images
foreach ($out->brackets as $bracket) {
if (is_readable('./images/bracket_' . $bracket->id . '_card.jpg')) {
$bracket->cardImage = '/images/bracket_' . $bracket->id . '_card.jpg';
} else {
$bracket->entrants = Api\Character::getRandomCharacters($bracket, 9);
}
}
// Sort the brackets by reverse date
usort($out->brackets, function ($a, $b) {
return $a->state == BS_FINAL || $a->state > $b->state ? 1 : -1;
});
// Decorate each bracket with some information about what phase it can
// safely move to. Mostly this is for eliminations
foreach ($out->brackets as $bracket) {
$bracket->title = Api\Round::getBracketTitleForActiveRound($bracket);
$bracket->nextIsFinal = $bracket->title === 'Title Match';
// Get the title of the next round
$nextRounds = Api\Round::getNextRounds($bracket);
$bracket->nextTitle = null;
if ($nextRounds) {
$bracket->nextTitle = str_replace(['Voting - ', 'Eliminations - '], '', Api\Round::getBracketTitleForRound($bracket, $nextRounds[0]));
}
// This is a dumb catch all while I work out issues in the stored procedure
$bracket->nextTitle = $bracket->nextTitle ?: 'Next Round';
if ($bracket->state == BS_ELIMINATIONS) {
// Should query all the brackets at once, but I'm feeling lazy tonight...
$result = Lib\Db::Query('SELECT MIN(round_group) AS current_group, MAX(round_group) AS last_group FROM `round` WHERE bracket_id = :bracketId AND round_final = 0', [':bracketId' => $bracket->id]);
if ($result && $result->count) {
$row = Lib\Db::Fetch($result);
// If the eliminations are on the last group, don't show the
// advance button
if ($row->current_group == $row->last_group) {
$bracket->showStart = true;
} else {
$bracket->showAdvance = true;
}
}
}
}
}
if ($message) {
$out->message = $message;
}
Lib\Display::renderAndAddKey('content', 'admin/brackets', $out);
}
/**
* Returns an array of parent items for this object
*/
public function getParents()
{
$cacheKey = 'MalItem::getItemParents_' . $this->id;
$retVal = Lib\Cache::Get($cacheKey);
if (false === $retVal && $this->id) {
$retVal = null;
$result = Lib\Db::Query('SELECT i.* FROM mal_xref x INNER JOIN mal_items i ON i.item_id = x.mal_parent WHERE x.mal_child = :id ORDER BY x.mal_parent ASC', [':id' => $this->id]);
if ($result && $result->count) {
$retVal = [];
while ($row = Lib\Db::Fetch($result)) {
$retVal[] = new MalItem($row);
}
}
Lib\Cache::Set($retVal, 3600);
}
return $retVal;
}
/**
* Takes a list of rounds and returns rounds with open voting
* that the user has not yet voted on
*/
public static function getOpenRounds($user, $votes)
{
$params = [':userId' => $user->id];
for ($i = 0, $count = count($votes); $i < $count; $i++) {
$params[':round' . $i] = $votes[$i]->roundId;
}
$roundKeys = implode(',', array_keys($params));
$query = 'SELECT round_id FROM votes WHERE user_id = :userId AND round_id IN (' . $roundKeys . ') UNION ';
$query .= 'SELECT round_id FROM round WHERE round_id IN (' . $roundKeys . ') AND round_final = 1';
$result = Lib\Db::Query($query, $params);
$retVal = [];
if ($result && $result->count > 0) {
while ($row = Lib\Db::Fetch($result)) {
$retVal[$row->round_id] = true;
}
}
return $retVal;
}
public static function _beginEliminations(Api\Bracket $bracket)
{
$days = Lib\Url::Post('days', true);
if ($bracket && $bracket->state == BS_NOMINATIONS) {
if (!$days) {
$result = Lib\Db::Query('SELECT COUNT(1) AS total FROM `character` WHERE bracket_id = :id', [':id' => $bracket->id]);
if ($result) {
$count = Lib\Db::Fetch($result);
$bracket->count = (int) $count->total;
}
Lib\Display::renderAndAddKey('content', 'admin/eliminations', $bracket);
} else {
$days = (int) $days;
$result = Lib\Db::Query('SELECT character_id FROM `character` WHERE bracket_id = :id ORDER BY RAND()', [':id' => $bracket->id]);
if ($result && $result->count) {
$group = 0;
$order = 0;
while ($row = Lib\Db::Fetch($result)) {
$round = new Api\Round();
$round->bracketId = $bracket->id;
$round->tier = 0;
$round->group = $group;
$round->order = $order;
$round->character1Id = $row->character_id;
$round->character2Id = 1;
$round->sync();
$order++;
$group = $order % $days;
}
$bracket->state = BS_ELIMINATIONS;
if ($bracket->sync()) {
$message = self::_createMessage('success', 'Eliminations for "' . $bracket->name . '" have started.');
}
self::_refreshCaches($bracket);
self::_main($message);
}
}
}
}
/**
* Gets a full dataset including characters for multiple rounds
*/
private static function _getRoundsAndCharacters($query, $params = null)
{
$retVal = null;
$result = Lib\Db::Query($query, $params);
if ($result && $result->count) {
// This array will hold all unique character IDs (and later character objects)
// to retrieve so we reduce the number of trips to the database.
$characters = [];
$retVal = [];
while ($row = Lib\Db::Fetch($result)) {
$round = new Round($row);
$characters[$round->character1Id] = true;
$characters[$round->character2Id] = true;
$retVal[] = new Round($row);
}
// Now fetch the character objects
$result = Character::query(['id' => ['in' => array_keys($characters)]]);
if ($result && $result->count) {
while ($row = Lib\Db::Fetch($result)) {
$character = new Character($row);
$characters[$character->id] = $character;
}
// Now, assign the character objects to their rounds
for ($i = 0, $count = count($retVal); $i < $count; $i++) {
$retVal[$i]->character1 = $characters[$retVal[$i]->character1Id];
$retVal[$i]->character2 = $characters[$retVal[$i]->character2Id];
}
}
}
return $retVal;
}
/**
* Gets the number of unique users who has voted for this character
*/
public function getVoterCount()
{
$retVal = null;
if ($this->id > 0 and $this->bracketId > 0) {
$retVal = 0;
$result = Lib\Db::Query('SELECT COUNT(DISTINCT user_id) AS total FROM votes WHERE character_id = :characterId', [':characterId' => $this->id]);
if ($result && $result->count === 1) {
$row = Lib\Db::Fetch($result);
$retVal = (int) $row->total;
}
}
return $retVal;
}
private static function _vote($user)
{
$out = new stdClass();
$out->success = false;
$bracketId = Lib\Url::Post('bracketId', true);
$bracket = Api\Bracket::getById($bracketId);
if ($bracket) {
$state = $bracket ? (int) $bracket->state : null;
if ($bracket->isLocked()) {
$out->message = 'Voting is closed for this round. Please refresh to see the latest round.';
} else {
if ($state === BS_ELIMINATIONS || $state === BS_VOTING) {
if (self::_verifyAccountAge($user, $bracket)) {
// Break the votes down into an array of round/character objects
$votes = [];
foreach ($_POST as $key => $val) {
if (strpos($key, 'round:') === 0) {
$key = str_replace('round:', '', $key);
$obj = new stdClass();
$obj->roundId = (int) $key;
$obj->characterId = (int) $val;
$votes[] = $obj;
}
}
$count = count($votes);
if ($count > 0) {
$query = 'INSERT INTO `votes` (`user_id`, `vote_date`, `round_id`, `character_id`, `bracket_id`) VALUES ';
$params = [':userId' => $user->id, ':date' => time(), ':bracketId' => $bracketId];
$insertCount = 0;
// Only run an insert for rounds that haven't been voted on
$rounds = Api\Votes::getOpenRounds($user, $votes);
for ($i = 0; $i < $count; $i++) {
if (!isset($rounds[$votes[$i]->roundId])) {
$query .= '(:userId, :date, :round' . $i . ', :character' . $i . ', :bracketId),';
$params[':round' . $i] = $votes[$i]->roundId;
$params[':character' . $i] = $votes[$i]->characterId;
$insertCount++;
$rounds[$votes[$i]->roundId] = true;
}
}
if ($insertCount > 0) {
$query = substr($query, 0, strlen($query) - 1);
if (Lib\Db::Query($query, $params)) {
$out->success = true;
// I am vehemently against putting markup in the controller, but there's much refactor needed to make this right
// So, that's a note that it will be changed in the future
$out->message = 'Your votes were successfully submitted! <a href="/results/' . $bracket->perma . '">View Results</a>';
// Oops, I did it again...
if ($bracket->externalId) {
$out->message .= ' or <a href="http://redd.it/' . $bracket->externalId . '" target="_blank">discuss on reddit</a>.';
}
// Clear any user related caches
$round = Api\Round::getById($votes[0]->roundId);
Lib\Cache::Set('GetBracketRounds_' . $bracketId . '_' . $round->tier . '_' . $round->group . '_' . $user->id, false);
Lib\Cache::Set('GetBracketRounds_' . $bracketId . '_' . $round->tier . '_all_' . $user->id, false);
Lib\Cache::Set('CurrentRound_' . $bracketId . '_' . $user->id, false);
$bracket->getVotesForUser($user, true);
} else {
$out->message = 'There was an unexpected error. Please try again in a few moments.';
}
} else {
$out->message = 'Voting for this round has closed';
$out->code = 'closed';
}
} else {
$out->message = 'No votes were submitted';
}
} else {
$out->message = 'Your reddit account is not old enough to vote in this bracket';
}
} else {
$out->message = 'Voting is closed on this bracket';
$out->code = 'closed';
}
}
} else {
$out->message = 'Invalid parameters';
}
return $out;
}
/**
* Saves the bot data back to the database. If the bot has not been created, it will create it.
*/
private function _persist()
{
$retVal = false;
$result = Lib\Db::Query('SELECT bot_id FROM bot_users WHERE bot_name=:user', array(':user' => $this->_userName));
if ($result->count > 0) {
$params = array(':hash' => $this->_hash, ':cookie' => $this->_cookie, ':name' => $this->_userName, ':data' => $this->data);
$retVal = Lib\Db::Query('UPDATE bot_users SET bot_updated=UNIX_TIMESTAMP(NOW()), bot_hash=:hash, bot_cookie=:cookie, bot_data=:data WHERE bot_name=:name', $params) > 0;
} else {
$retVal = Lib\Db::Query('INSERT INTO bot_users (bot_created, bot_name, bot_password, bot_cookie, bot_hash, bot_enabled) VALUES (UNIX_TIMESTAMP(NOW()), :user, :password, :cookie, :hash, 0)', array(':password' => $this->_password, ':hash' => $this->_hash, ':cookie' => $this->_cookie, ':user' => $this->_userName)) > 0;
}
}
/**
* Returns the number of unprocessed nominees and the number of unique names within that for a bracket
*/
public static function getUnprocessedCount(Bracket $bracket)
{
$retVal = (object) ['total' => 0, 'uniques' => 0];
$result = Lib\Db::Query('SELECT COUNT(1) AS total, COUNT(DISTINCT nominee_name) AS uniques FROM `nominee` WHERE bracket_id = :bracketId AND nominee_processed IS NULL', [':bracketId' => $bracket->id]);
if ($result && $result->count) {
$retVal = Lib\Db::Fetch($result);
}
return $retVal;
}
/**
* Assigns this bracket to a user
*/
public function addUser(User $user)
{
return Lib\Db::Query('INSERT INTO bracket_owners VALUES (:bracketId, :userId)', [':bracketId' => $this->id, ':userId' => $user->id]);
}
/**
* Verifies an incoming signed request
*/
public static function checkSignature($vars, $raiseError = true)
{
$retVal = false;
// If this is an internal call, no verification is required
if (defined('API_LOCATION') && API_LOCATION == '_internal') {
return true;
}
// Check to make sure the key is valid
$key = isset($vars['key']) ? $vars['key'] : false;
$key = strlen($key) == 32 && preg_match('/[a-f0-9]{32}/', $key) ? $key : false;
if ($key) {
$signature = isset($vars['signature']) ? $vars['signature'] : false;
if ($signature) {
// Get the user's secret from the database
$params = array(':key' => $key);
$row = Lib\Db::Fetch(Lib\Db::Query('SELECT api_secret FROM api_keys WHERE api_id=:key', $params));
if ($row) {
// Sort all the variables by key and create the signature key
ksort($vars);
$sig = '';
foreach ($vars as $key => $val) {
if ($key != 'signature') {
$sig .= $key . '=' . $val . '&';
}
}
$sig = substr($sig, 0, strlen($sig) - 1);
$sig = base64_encode(hash_hmac('sha256', $sig, $row->api_secret));
// Drumroll
if ($sig == $signature) {
$retVal = true;
} else {
if ($raiseError) {
throw new Exception('Signature is invalid', INVALID_SIGNATURE);
}
}
} else {
if ($raiseError) {
throw new Exception('Provided key is not registered', INVALID_KEY);
}
}
} else {
if ($raiseError) {
throw new Exception('This request requires a signature', NO_SIGNATURE);
}
}
} else {
if ($raiseError) {
throw new Exception('Provided key is invalid', INVALID_KEY);
}
}
return $retVal;
}
/**
* Gets a record from the database by the primary key
*/
private function _getById($id)
{
$retVal = null;
if (self::_verifyProperties($this)) {
if (is_numeric($id)) {
$cacheKey = 'Lib:Dal:' . $this->_dbTable . '_getById_' . $id;
$retVal = Cache::Get($cacheKey);
if (!$retVal) {
$query = 'SELECT `' . implode('`, `', $this->_dbMap) . '` FROM `' . $this->_dbTable . '` ';
$query .= 'WHERE `' . $this->_dbMap[$this->_dbPrimaryKey] . '` = :id LIMIT 1';
$result = Db::Query($query, [':id' => $id]);
if (null !== $result && $result->count === 1) {
$retVal = Db::Fetch($result);
Cache::Set($cacheKey, $retVal);
}
}
if ($retVal) {
$this->copyFromDbRow($retVal);
}
} else {
throw new Exception('ID must be a number');
}
} else {
throw new Exception('Class must have "_dbTable", "_dbMap", and "_dbPrimaryKey" properties to use method "getById"');
}
}
