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;
}
/**
* Loads a reddit bot's data by username
*/
private function _loadSessionByUserName($userName)
{
$retVal = false;
$result = Lib\Db::Query('SELECT bot_name, bot_password, bot_hash, bot_cookie, bot_data, bot_updated, bot_callback FROM bot_users WHERE bot_name=:name LIMIT 1', array(':name' => $userName));
while ($row = Lib\Db::Fetch($result)) {
$this->_userName = $row->bot_name;
$this->_password = $row->bot_password;
$this->_userObj = new stdClass();
$this->_hash = $row->bot_hash;
$this->_cookie = $row->bot_cookie;
$this->data = $row->bot_data;
$this->lastUpdated = $row->bot_updated;
$this->_runCallback = $row->bot_callback;
$retVal = true;
}
return $retVal;
}
/**
* 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;
}
/**
* Takes the results from the elimination rounds and creates a seeded bracket
*/
public function createBracketFromEliminations($entrants, $groups)
{
$retVal = false;
if (is_numeric($entrants)) {
// Generate the bracket template
$seeding = self::generateSeededBracket($entrants);
// Get the max vote counts for each day
$result = Lib\Db::Query('SELECT COUNT(1) AS total, r.round_group FROM votes v INNER JOIN round r ON r.round_id = v.round_id WHERE v.bracket_id = :bracketId GROUP BY r.round_group', [':bracketId' => $this->id]);
$groupCounts = [];
$max = 0;
while ($row = Lib\Db::Fetch($result)) {
$votes = (int) $row->total;
$groupCounts[(int) $row->round_group] = $votes;
$max = $votes > $max ? $votes : $max;
}
$characters = [];
$result = Lib\Db::Query('SELECT COUNT(1) AS total, c.*, r.round_group FROM `round` r INNER JOIN `character` c ON c.character_id = r.round_character1_id LEFT OUTER JOIN votes v ON v.character_id = c.character_id WHERE r.round_tier = 0 AND r.bracket_id = :bracketId GROUP BY c.character_id', [':bracketId' => $this->id]);
// Ensure that we have characters and there are at least enough to meet the bracket constraints
if ($result && $result->count >= $entrants) {
while ($row = Lib\Db::Fetch($result)) {
$obj = new Character($row);
// Normalize the votes against the highest day of voting to ensure that seeding order is reflective of flucuations in daily voting
// $obj->adjustedVotes = round(($obj->votes / $groups[$obj->group]) * $max);
$obj->adjustedVotes = round((int) $row->total / $groupCounts[(int) $row->round_group] * $max);
$characters[] = $obj;
}
// Reorder by adjusted votes
usort($characters, function ($a, $b) {
return $a->adjustedVotes < $b->adjustedVotes ? 1 : -1;
});
// Set up the rounds
$groupSplit = $entrants / $groups;
for ($i = 0; $i < $entrants; $i += 2) {
$round = new Round();
$round->bracketId = $this->id;
$round->tier = 1;
$round->order = ($i + 1) % $groupSplit;
$round->group = floor($i / $groupSplit);
// Get the correct character and save their seed
$character1 = $characters[$seeding[$i] - 1];
$character1->seed = $seeding[$i];
$character1->sync();
$character2 = $characters[$seeding[$i + 1] - 1];
$character2->seed = $seeding[$i + 1];
$character2->sync();
$round->character1Id = $character1->id;
$round->character2Id = $character2->id;
$round->sync();
}
// Change the state to standard bracket voting
$this->state = BS_VOTING;
$retVal = $this->sync();
// Force update the results cache
$this->getResults(true);
}
}
return $retVal;
}
/**
* 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"');
}
}
