/**
* Mark events as read for a given user
*
* @param int $user_id
* @return void
*/
private function readEvents($user_id)
{
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("UPDATE events SET unread = 0 WHERE send_to = :to");
$statement->bindValue(':to', $user_id);
$statement->execute();
}
/**
* For events, attacks, kills, invites, etc, and no user-created messages
*
* @param int $from_id
* @param int $to_id
* @param String $message
* @return void
* @throws Exception
*/
public static function create($from_id, $to_id, $message)
{
if (!is_numeric($from_id) || !is_numeric($to_id)) {
throw new \Exception('A player id wasn\'t sent in to the Event::create function.');
}
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("INSERT INTO events (event_id, send_from, send_to, message, date)\n VALUES (default, :from, :to, :message, now())");
$statement->bindValue(':from', $from_id);
$statement->bindValue(':to', $to_id);
$statement->bindValue(':message', $message);
$statement->execute();
}
public static function sendLogOfDuel($attacker, $defender, $won, $killpoints)
{
$killpoints = (int) $killpoints;
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("INSERT INTO dueling_log values (default, :attacker, :defender, :won, :killpoints, now())");
//Log of Dueling information.
$statement->bindValue(':attacker', $attacker);
$statement->bindValue(':defender', $defender);
$statement->bindValue(':won', $won);
$statement->bindValue(':killpoints', $killpoints);
$statement->execute();
}
/**
* Stats on recent activity and other aggregate counts/information.
*
* @return array
*/
private function membershipAndCombatStats()
{
DatabaseConnection::getInstance();
$viciousResult = DatabaseConnection::$pdo->query('SELECT stat_result from past_stats where id = 4');
$todaysViciousKiller = $viciousResult->fetchColumn();
$stats = [];
$stats['vicious_killer'] = $todaysViciousKiller;
$playerCount = DatabaseConnection::$pdo->query("SELECT count(player_id) FROM players WHERE active = 1");
$stats['player_count'] = $playerCount->fetchColumn();
$stats['active_chars'] = query_item("SELECT count(*) FROM ppl_online WHERE member = true AND activity > (now() - CAST('15 minutes' AS interval))");
return $stats;
}
public function __construct()
{
$this->m_dbconn = DatabaseConnection::getInstance();
$this->_vo_obj_name = 'SkillVO';
$this->_vo_fields = array();
$vo = new \ReflectionClass(new SkillVO());
$vo = $vo->getProperties();
foreach ($vo as $reflectionProperty) {
$this->_vo_fields[] = $reflectionProperty->name;
}
$this->_id_field = 'skill_id';
$this->_table = 'skill';
}
/**
* Assigns and holds the connection to the db.
*/
public function __construct()
{
$this->m_dbconn = DatabaseConnection::getInstance();
$this->_vo_obj_name = 'PlayerVO';
$this->_vo_fields = array();
$vo = new \ReflectionClass(new PlayerVO());
foreach ($vo->getProperties() as $reflectionProperty) {
$this->_vo_fields[] = $reflectionProperty->name;
}
$this->_id_field = 'player_id';
$this->_table = 'players JOIN class ON class_id = _class_id';
$this->_table_for_saving = 'players';
$this->setReadOnlyFields(['identity', 'class_name', 'theme']);
}
/**
* For events, attacks, kills, invites, etc, and no user-created messages
*
* @param int $fromId
* @param int $toId
* @param String $msg
* @return void
* @throws Exception
*/
function send_event($fromId, $toId, $msg)
{
if (!$toId) {
$toId = self_char_id();
}
if (!is_numeric($fromId) || !is_numeric($toId)) {
throw new \Exception('A player id wasn\'t sent in to the send_event function.');
}
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("INSERT INTO events (event_id, send_from, send_to, message, date)\n VALUES (default, :from, :to, :message, now())");
$statement->bindValue(':from', $fromId);
$statement->bindValue(':to', $toId);
$statement->bindValue(':message', $msg);
$statement->execute();
}
function send_chat($user_id, $msg)
{
DatabaseConnection::getInstance();
$msg = trim($msg);
if ($msg) {
$statement = DatabaseConnection::$pdo->prepare("SELECT message FROM chat WHERE sender_id = :sender ORDER BY date DESC LIMIT 1");
$statement->bindValue(':sender', $user_id);
$statement->execute();
$prevMsg = trim($statement->fetchColumn());
if ($prevMsg != $msg) {
$statement = DatabaseConnection::$pdo->prepare("INSERT INTO chat (chat_id, sender_id, message, date) VALUES (default, :sender, :message, now())");
$statement->bindValue(':sender', $user_id);
$statement->bindValue(':message', $msg);
$statement->execute();
return true;
}
}
return false;
// could add channels later.
}
/**
* Update the information of a viewing observer, or player.
*/
function update_activity_info()
{
// ******************** Usage Information of the browser *********************
Request::setTrustedProxies(Constants::$trusted_proxies);
$request = Request::createFromGlobals();
$remoteAddress = '' . $request->getClientIp();
$userAgent = isset($_SERVER['HTTP_USER_AGENT']) ? substr($_SERVER['HTTP_USER_AGENT'], 0, 250) : NULL;
// Truncated at 250 char.
$referer = isset($_SERVER['HTTP_REFERER']) ? substr($_SERVER['HTTP_REFERER'], 0, 250) : '';
// Truncated at 250 char.
// ************** Setting anonymous and player usage information
DatabaseConnection::getInstance();
$session = SessionFactory::getSession();
if (!$session->has('online')) {
// *** Completely new session, update latest activity log. ***
if ($remoteAddress) {
// *** Delete prior to trying to re-insert into the people online. ***
$statement = DatabaseConnection::$pdo->prepare('DELETE FROM ppl_online WHERE ip_address = :ip OR session_id = :sessionID');
$statement->bindValue(':ip', $remoteAddress);
$statement->bindValue(':sessionID', $session->getId());
$statement->execute();
}
// *** Update viewer data. ***
$statement = DatabaseConnection::$pdo->prepare('INSERT INTO ppl_online (session_id, activity, ip_address, refurl, user_agent) VALUES (:sessionID, now(), :ip, :referer, :userAgent)');
$statement->bindValue(':sessionID', $session->getId());
$statement->bindValue(':ip', $remoteAddress);
$statement->bindValue(':referer', $referer);
$statement->bindValue(':userAgent', $userAgent);
$statement->execute();
$session->set('online', true);
} else {
// *** An already existing session. ***
$statement = DatabaseConnection::$pdo->prepare('UPDATE ppl_online SET activity = now(), member = :member WHERE session_id = :sessionID');
$statement->bindValue(':sessionID', $session->getId());
$statement->bindValue(':member', is_logged_in(), PDO::PARAM_BOOL);
$statement->execute();
}
}
/**
* Run bound queries on the database.
*
* Use: query('select all from players limit :count', array('count'=>10));
* Or: query('select all from players limit :count', array('count'=>array(10, PDO::PARAM_INT)));
*
* Note that it returns foreachable resultset object unless an array is specifically requested.
*/
function query($sql, $bindings = array(), $return_resultset = true)
{
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare($sql);
foreach ($bindings as $binding => $value) {
if (is_array($value)) {
$first = reset($value);
$last = end($value);
// Cast the bindings when something to cast to was sent in.
$statement->bindValue($binding, $first, $last);
} else {
$statement->bindValue($binding, $value);
}
}
$statement->execute();
if ($return_resultset) {
return $statement;
// Returns a foreachable resultset
} else {
// Otherwise returns an associative array.
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
}
function unconfirm_older_players_over_minimums($keep_players = 2300, $unconfirm_days_over = 90, $max_to_unconfirm = 30, $just_testing = true)
{
$change_confirm_to = $just_testing ? '1' : '0';
// Only unconfirm players when not testing.
$minimum_days = 30;
$max_to_unconfirm = is_numeric($max_to_unconfirm) ? $max_to_unconfirm : 30;
DatabaseConnection::getInstance();
$sel_cur = DatabaseConnection::$pdo->query("SELECT count(*) FROM players WHERE active = 1");
$current_players = $sel_cur->fetchColumn();
if ($current_players < $keep_players) {
// *** If we're under the minimum, don't inactivate anyone.
return false;
}
// *** Don't unconfirm anyone below the minimum floor.
$unconfirm_days_over = max($unconfirm_days_over, $minimum_days);
// Unconfirm at a maximum of 20 players at a time.
$unconfirm_within_limits = "UPDATE players\n\t\tSET active = :active\n\t\tWHERE players.player_id\n\t\tIN (\n\t\t\tSELECT player_id FROM players\n\t\t\tWHERE active = 1\n\t\t\tAND days > :age\n\t\t\tORDER BY player_id DESC\tLIMIT :max)";
$update = DatabaseConnection::$pdo->prepare($unconfirm_within_limits);
$update->bindValue(':active', $change_confirm_to);
$update->bindValue(':age', intval($unconfirm_days_over));
$update->bindValue(':max', $max_to_unconfirm);
$update->execute();
return $update->rowCount();
}
public function __construct()
{
$this->m_dbconn = DatabaseConnection::getInstance();
}
/**
* Create a new account
* @return int|false
*/
public static function create($ninja_id, $email, $password_to_hash, $confirm, $type = 0, $active = 1, $ip = null)
{
DatabaseConnection::getInstance();
$newID = query_item("SELECT nextval('accounts_account_id_seq')");
$ins = "INSERT INTO accounts (account_id, account_identity, active_email, phash, type, operational, verification_number, last_ip)\n VALUES (:acc_id, :email, :email2, crypt(:password, gen_salt('bf', 10)), :type, :operational, :verification_number, :ip)";
$email = strtolower($email);
$statement = DatabaseConnection::$pdo->prepare($ins);
$statement->bindParam(':acc_id', $newID);
$statement->bindParam(':email', $email);
$statement->bindParam(':email2', $email);
$statement->bindParam(':password', $password_to_hash);
$statement->bindParam(':type', $type, PDO::PARAM_INT);
$statement->bindParam(':operational', $active, PDO::PARAM_INT);
$statement->bindParam(':verification_number', $confirm);
$statement->bindParam(':ip', $ip);
$statement->execute();
// Create the link between account and player.
$link_ninja = 'INSERT INTO account_players (_account_id, _player_id, last_login) VALUES (:acc_id, :ninja_id, default)';
$statement = DatabaseConnection::$pdo->prepare($link_ninja);
$statement->bindParam(':acc_id', $newID, PDO::PARAM_INT);
$statement->bindParam(':ninja_id', $ninja_id, PDO::PARAM_INT);
$statement->execute();
$sel_ninja_id = 'SELECT player_id FROM players
JOIN account_players ON player_id = _player_id
JOIN accounts ON _account_id = account_id
WHERE account_id = :acc_id ORDER BY level DESC LIMIT 1';
$verify_ninja_id = query_item($sel_ninja_id, array(':acc_id' => array($newID, PDO::PARAM_INT)));
return $verify_ninja_id != $ninja_id ? false : $newID;
}
/**
* Update activity for a logged in player.
*
* @return void
*/
function update_activity_log($p_playerID)
{
// (See update_activity_info in lib_header for the function that updates all the detailed info.)
DatabaseConnection::getInstance();
Request::setTrustedProxies(Constants::$trusted_proxies);
$request = Request::createFromGlobals();
$user_ip = $request->getClientIp();
query("UPDATE players SET days = 0 WHERE player_id = :player", [':player' => $p_playerID]);
query("Update accounts set last_ip = :ip, last_login = now() where account_id = (select _account_id from account_players join players on _player_id = player_id where player_id = :pid)", array(':ip' => $user_ip, ':pid' => $p_playerID));
}
private function jsonIndex()
{
DatabaseConnection::getInstance();
$user_id = self_char_id();
$player = new Player($user_id);
$events = [];
$messages = [];
$unread_messages = null;
$unread_events = null;
$items = null;
if ($user_id) {
$events = DatabaseConnection::$pdo->prepare("SELECT event_id, message AS event, date, send_to, send_from, unread, uname AS sender FROM events JOIN players ON player_id = send_from WHERE send_to = :userID and unread = 1 ORDER BY date DESC");
$events->bindValue(':userID', $user_id);
$events->execute();
$unread_events = $events->rowCount();
$messages = DatabaseConnection::$pdo->prepare("SELECT message_id, message, date, send_to, send_from, unread, uname AS sender FROM messages JOIN players ON player_id = send_from WHERE send_to = :userID1 AND send_from != :userID2 and unread = 1 ORDER BY date DESC");
$messages->bindValue(':userID1', $user_id);
$messages->bindValue(':userID2', $user_id);
$messages->execute();
$unread_messages = $messages->rowCount();
$items = query_array('SELECT item.item_display_name as item, amount FROM inventory join item on inventory.item_type = item.item_id WHERE owner = :user_id ORDER BY item_display_name', [':user_id' => $user_id]);
}
return ['player' => $player ? $player->publicData() : [], 'member_counts' => $this->memberCounts(), 'unread_messages_count' => $unread_messages, 'message' => !empty($messages) ? $messages->fetch() : null, 'inventory' => ['inv' => 1, 'items' => $items, 'hash' => md5(strtotime("now"))], 'unread_events_count' => $unread_events, 'event' => !empty($events) ? $events->fetch() : null];
}
/**
* Deletes old chat messages.
*/
public static function shortenChat($message_limit = 800)
{
DatabaseConnection::getInstance();
// Find the latest 800 messages and delete all the rest;
$deleted = DatabaseConnection::$pdo->prepare("DELETE FROM chat WHERE chat_id NOT IN (SELECT chat_id FROM chat ORDER BY date DESC LIMIT :msg_limit)");
$deleted->bindValue(':msg_limit', $message_limit);
$deleted->execute();
return (int) $deleted->rowCount();
}
function confirm_player($char_name, $confirmation = 0, $autoconfirm = false)
{
DatabaseConnection::getInstance();
// Preconfirmed or the email didn't send, so automatically confirm the player.
$require_confirm = $autoconfirm ? '' : ' AND
(account.verification_number = :confirmation OR players.verification_number = :confirmation2) ';
// Get the account_id for a player
$params = array(':char_name' => $char_name);
if ($require_confirm) {
$params[':confirmation'] = $confirmation;
$params[':confirmation2'] = $confirmation;
}
$info = query_row('select account_id, player_id from players
join account_players on _player_id = player_id
join accounts on account_id = _account_id
where uname = :char_name ' . $require_confirm, $params);
if (empty($info)) {
return false;
} else {
$account_id = $info['account_id'];
$player_id = $info['player_id'];
if (!$account_id || !$player_id) {
return false;
}
}
query('update players set active = 1 where player_id = :player_id', array(':player_id' => $player_id));
$up = "UPDATE accounts set operational = true, confirmed = 1 where account_id = :account_id";
$params = array(':account_id' => $account_id);
return (bool) rco(query($up, $params));
}
/**
* Get the rank integer for a certain character.
*/
private function getRank($p_char_id)
{
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("SELECT rank_id FROM rankings WHERE player_id = :player");
$statement->bindValue(':player', $p_char_id);
$statement->execute();
$rank = $statement->fetchColumn();
return $rank > 0 ? $rank : 1;
// Make rank default to 1 if no valid ones are found.
}
/**
* Drop a certain enemy from the list.
*
* @param Player $p_player
* @param Player $p_enemy
* @return void
*/
private function removeEnemyFromPlayer(Player $p_player, Player $p_enemy)
{
DatabaseConnection::getInstance();
$query = 'DELETE FROM enemies WHERE _player_id = :pid AND _enemy_id = :eid';
$statement = DatabaseConnection::$pdo->prepare($query);
$statement->bindValue(':pid', $p_player->id());
$statement->bindValue(':eid', $p_enemy->id());
$statement->execute();
}
<?php
require_once substr(__FILE__, 0, strpos(__FILE__, 'cron/')) . 'resources.php';
require_once dirname(__DIR__ . '..') . '/core/base.inc.php';
require_once LIB_ROOT . 'data/DatabaseConnection.php';
require_once LIB_ROOT . 'environment/lib_assert.php';
require_once LIB_ROOT . 'environment/status_defines.php';
// Status constant definitions.
require_once LIB_ROOT . 'environment/lib_error_reporting.php';
require_once LIB_ROOT . 'data/lib_db.php';
require_once LIB_ROOT . "control/lib_deity.php";
// Deity-specific functions
use NinjaWars\core\data\DatabaseConnection;
DatabaseConnection::getInstance();
DatabaseConnection::$pdo->query('BEGIN TRANSACTION');
DatabaseConnection::$pdo->query('TRUNCATE player_rank RESTART IDENTITY');
DatabaseConnection::$pdo->query("SELECT setval('player_rank_rank_id_seq', 1, false)");
$ranked_players = DatabaseConnection::$pdo->prepare('INSERT INTO player_rank (_player_id, score) SELECT player_id, ((level*:level_weight) + floor(gold/:gold_weight) + (CASE WHEN kills > (5*level) THEN 3000 + least(floor((kills - (5*level)) * .3), 2000) ELSE ((kills/(5*level))*3000) END) - (days*:inactivity_weight)) AS score FROM players WHERE active = 1 ORDER BY score DESC');
$ranked_players->bindValue(':level_weight', RANK_WEIGHT_LEVEL);
$ranked_players->bindValue(':gold_weight', RANK_WEIGHT_GOLD);
$ranked_players->bindValue(':inactivity_weight', RANK_WEIGHT_INACTIVITY);
$ranked_players->execute();
// *** Running from a cron script, we don't want any output unless we have an error ***
// Add 1 to player's ki when they've been active in the last 5 minutes.
$s = DatabaseConnection::$pdo->prepare("update players set ki = ki + :regen_rate where last_started_attack > (now() - :interval::interval)");
$s->bindValue(':interval', KI_REGEN_TIMEOUT);
$s->bindValue(':regen_rate', KI_REGEN_PER_TICK);
$s->execute();
DatabaseConnection::$pdo->query('COMMIT');
// Err on the side of low revives for this five minute tick.
$params = ['minor_revive_to' => MINOR_REVIVE_THRESHOLD, 'major_revive_percent' => MAJOR_REVIVE_PERCENT];
/**
* ????
*
* @todo Simplify this invite system.
* @param int $user_id
* @param int $clan_id
* @return void
*/
private function sendClanJoinRequest($user_id, $clan_id)
{
DatabaseConnection::getInstance();
$clan_obj = new Clan($clan_id);
$leader = $clan_obj->getLeaderInfo();
$leader_id = $leader['player_id'];
$username = get_char_name($user_id);
$confirmStatement = DatabaseConnection::$pdo->prepare('SELECT verification_number FROM players WHERE player_id = :user');
$confirmStatement->bindValue(':user', $user_id);
$confirmStatement->execute();
$confirm = $confirmStatement->fetchColumn();
// These ampersands get encoded later.
$url = "[href:clan/review/?joiner={$user_id}&confirmation={$confirm}|Confirm Request]";
$join_request_message = 'CLAN JOIN REQUEST: ' . htmlentities($username) . " has sent a request to join your clan.\n If you wish to allow this ninja into your clan click the following link:\n {$url}";
Message::create(['send_from' => $user_id, 'send_to' => $leader_id, 'message' => $join_request_message, 'type' => 0]);
}
/**
* Revive x characters
*
* Use the order by clause to determine who revives, by time, days and
* then by level, using the limit set previously.
*
* Here is a runnable version:
* select uname, player_id, level,floor(($major_revive_percent/100)*$total_active) days, resurrection_time from players where active = 1 AND health < 1 ORDER BY abs(8 - resurrection_time) asc, level desc, days asc
* @return int
*/
private static function performNecromancy($revive_amount, $maximum_heal, $game_hour)
{
$subselect = 'SELECT player_id FROM players WHERE active = 1 AND health < 1 ORDER BY abs(:time - resurrection_time) ASC, level DESC, days ASC LIMIT :amount';
if (self::LEVEL_REVIVE_INCREASE) {
$level_add = '+(level*3)';
} else {
$level_add = '';
}
$up_revive_players = 'UPDATE players SET status = 0, health =
CASE WHEN level >= coalesce(class_skill_level, skill_level)
THEN (:max_heal ' . $level_add . ')
ELSE ((:max_heal_2 + :max_heal_3 * 0.5) ' . $level_add . ') END
FROM (SELECT * FROM skill LEFT JOIN class_skill ON skill_id = _skill_id WHERE skill_id = :midnightHeal)
AS class_skill ';
$up_revive_players .= ' WHERE player_id IN (' . $subselect . ') ';
$up_revive_players .= ' AND coalesce(class_skill._class_id, players._class_id) = players._class_id';
DatabaseConnection::getInstance();
$update = DatabaseConnection::$pdo->prepare($up_revive_players);
$update->bindValue(':amount', $revive_amount, PDO::PARAM_INT);
$update->bindValue(':time', $game_hour, PDO::PARAM_INT);
$update->bindValue(':midnightHeal', self::MIDNIGHT_HEAL_SKILL, PDO::PARAM_INT);
$update->bindValue(':max_heal', $maximum_heal);
$update->bindValue(':max_heal_2', $maximum_heal);
$update->bindValue(':max_heal_3', $maximum_heal);
$update->execute();
$truly_revived = $update->rowCount();
return $truly_revived;
// Return the amount revived
}
function removeItem($who, $item, $quantity = 1)
{
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare("UPDATE inventory SET amount = greatest(0, amount - :quantity) WHERE owner = :user AND item_type = (SELECT item_id FROM item WHERE lower(item_display_name) = lower(:item)) AND amount > 0");
$statement->bindValue(':user', $who);
$statement->bindValue(':item', $item);
$statement->bindValue(':quantity', $quantity);
$statement->execute();
}
/**
* Rename a clan
*
* @param int $p_clanID
* @param String $p_newName
* @return String
* @note
* Does not check for validity, simply renames the clan to the new name.
*/
public static function renameClan($p_clanID, $p_newName)
{
DatabaseConnection::getInstance();
$statement = DatabaseConnection::$pdo->prepare('UPDATE clan SET clan_name = :name WHERE clan_id = :clan');
$statement->bindValue(':name', $p_newName);
$statement->bindValue(':clan', $p_clanID);
$statement->execute();
return $p_newName;
}
/**
* Drop a certain enemy from the list.
*
* @param int $p_playerId
* @param int $p_enemyId
* @return void
*/
private function removeEnemyFromPlayer($p_playerId, $p_enemyId)
{
if (!is_numeric($p_enemyId)) {
throw new \Exception('Enemy id to remove must be present to succeed.');
}
DatabaseConnection::getInstance();
$query = 'DELETE FROM enemies WHERE _player_id = :pid AND _enemy_id = :eid';
$statement = DatabaseConnection::$pdo->prepare($query);
$statement->bindValue(':pid', $p_playerId);
$statement->bindValue(':eid', $p_enemyId);
$statement->execute();
}
