function user_having_email($email)
{
$data = query_row('SELECT uname, accounts.confirmed, accounts.verification_number, account_id, CASE WHEN active::bool THEN 1 ELSE 0 END AS active
from accounts LEFT JOIN account_players ON account_id = _account_id LEFT JOIN players on _player_id = player_id
WHERE active_email = :email limit 1;', array(':email' => $email));
return $data;
}
private function queryEntryID($entry_ID)
{
if ($this->queryEntryIDQuery === null) {
$this->queryEntryIDQuery = $this->parentQuery;
$this->queryEntryIDQuery->appendCondition("`{$this->column_ID}` = '%d'");
$this->queryEntryIDQuery = $this->queryEntryIDQuery->getQuery();
}
return query_row(sprintf($this->queryEntryIDQuery, (int) $entry_ID), $this->className);
}
function item_info($item_id, $specific = null)
{
$info = query_row('SELECT item_id, item_internal_name, item_display_name, item_cost, image, for_sale, usage,
ignore_stealth, covert, turn_cost, target_damage, turn_change, self_use, other_usable, plural
FROM item WHERE item_id = :item_id', array(':item_id' => array($item_id, PDO::PARAM_INT)));
if (!$info || $specific && !isset($info[$specific])) {
return null;
} elseif ($specific) {
return $info[$specific];
} else {
return $info;
}
}
/**
* Get the clan that a member has, if any
*
* @param Player|int $pc_or_id
* @return Clan|null
*/
public static function clanOfMember($pc_or_id)
{
if ($pc_or_id instanceof Player) {
$playerID = $pc_or_id->id();
} else {
$playerID = $pc_or_id;
}
$clan_info = query_row('select clan_id, clan_name, clan_created_date, clan_founder, clan_avatar_url, description from clan JOIN clan_player ON clan_id = _clan_id where _player_id = :pid', [':pid' => $playerID]);
if (empty($clan_info)) {
return null;
} else {
$clan = new Clan($clan_info['clan_id']);
$clan->setFounder($clan_info['clan_founder']);
$clan->setDescription($clan_info['description']);
$clan->setName($clan_info['clan_name']);
$clan->setAvatarUrl($clan_info['clan_avatar_url']);
return $clan;
}
}
/**
* Get only the first result item.
*/
function query_item($sql, $bindings = array())
{
$row = query_row($sql, $bindings);
return is_array($row) ? reset($row) : null;
}
/**
* Get sums about all the active pcs, counts of dead/alive
* @return array
*/
private static function pcsActiveDeadAlive()
{
$pc_data = query_row('select count(*) as active,
sum(case when health < 1 then 1 else 0 end) as dead
from players where active = 1');
$pc_data['alive'] = $pc_data['active'] - $pc_data['dead'];
return $pc_data;
}
function querySessionuserFromLogin($user_uid, $user_password)
{
return query_row(sprintf("SELECT * FROM {$GLOBALS[T]('user')} WHERE user_uid = '%s' AND user_password = '******'", esc_sql($user_uid), esc_sql(self::encryptSessionuserPassword($user_password))), SESSIONUSER_CLASS);
}
$query =& $update['inline_query']['query'];
if (is_command($query, 'chat id')) {
apiRequest('answerInlineQuery', ['inline_query_id' => $id, 'results' => [['type' => 'article', 'id' => "2", 'title' => "Premi qua e scopri il tuo chat id", 'message_text' => "Il tuo chat id è: " . $from["id"], 'parse_mode' => 'HTML']], 'cache_time' => 0]);
} elseif (is_command($query, 'presenta')) {
apiRequest('answerInlineQuery', ['inline_query_id' => $id, 'results' => [['type' => 'article', 'id' => "1", 'title' => "Premi qua per presentare questo bot a qualcuno", 'message_text' => "Spotted, un fenomeno molto conosciuto ai giorni nostri, " . "dove le persone possono inviare degli appelli anonimi, " . "ora è arrivato anche su Telegram, per gli <b>studenti di UniTO</b>! " . "\n <a href=\"telegram.me/spottedunitobot?start=byinline\">PREMI QUI</a>, e vieni a leggere le confessioni degli studenti!", 'parse_mode' => 'HTML']], 'cache_time' => 0]);
}
} elseif (isset($update['message']['text'], $update['message']['chat']['id'])) {
// It's a message!
$text = trim($update['message']['text']);
$chat_id =& $update['message']['from']['id'];
$first_name =& $update['message']['from']['first_name'];
$last_name =& $update['message']['from']['last__name'];
$username =& $update['message']['from']['username'];
if (is_command($text, '/start')) {
// Exists?
$exists = query_row(sprintf("SELECT 1 FROM {$T('spotter')} " . "WHERE spotter_chat_ID = %d", $update['message']['chat']['id']));
// Insert if not exists
$exists || insert_row('spotter', [new DBCol('spotter_chat_id', $update['message']['chat']['id'], 'd'), new DBCol('spotter_datetime', 'NOW()', '-')]);
apiRequest('sendMessage', ['chat_id' => $update['message']['chat']['id'], 'text' => "Benvenuto sul bot di <b>Spotted Unito</b>\n\n" . "Con questo bot, potrai inviare i tuoi appelli o confessioni anonimamente, a tutti coloro che seguono questo bot.\n" . "Per inviare uno spot, non ti resta altro che scrivere (indifferente se maiuscolo o minuscolo) <code>spotted messaggio</code>, dove al posto di <code>messaggio</code> dovrete scrivere" . " il testo desiderato. (Es. <code>spotted Un saluto a tutti!</code>)\n\nNB: attualmente non sono supportate le emoticon", 'parse_mode' => "HTML", 'disable_web_page_preview' => true]);
apiRequest('sendMessage', ['chat_id' => $update['message']['chat']['id'], 'text' => "Un messaggio di conferma apparirà successivamente. Da quel momento, il messaggio, appena " . "i moderatori avranno verificato che non contenga parole inappropriate (bestemmie, minacce, offese, ecc...), verrà pubblicato." . "\n\nIn caso di necessità, premere su /help , oppure inviare un messaggio con <code>/help messaggio</code>.", 'parse_mode' => "HTML", 'disable_web_page_preview' => true]);
} elseif (stripos($text, 'spotted') === 0) {
$spotted = ltrim(str_ireplace('spotted', '', $text));
if (strlen($spotted) === 0) {
apiRequest('sendMessage', ['chat_id' => $update['message']['chat']['id'], 'text' => _("Il comando <code>spotted</code> è esatto. Tuttavia, per inviare uno spot, deve essere seguito da un messaggio.\n" . "Es. Spotted Chi da l'esame al posto mio domani?"), 'parse_mode' => 'HTML']);
} else {
$spotted = str_truncate($spotted, 1000, '...');
insert_row('spotted', [new DBCol('spotted_datetime', 'NOW()', '-'), new DBCol('spotted_message', $spotted, 's'), new DBCol('spotted_chat_id', $update['message']['chat']['id'], 'd'), new DBCol('spotted_approved', 0, '-')]);
refresh_admin_keyboard($update['message']['chat']['id'], $spotted, $first_name, $last_name, $username);
$spotters = query_value("SELECT COUNT(*) as count FROM {$T('spotter')}", 'count');
apiRequest('sendMessage', ['chat_id' => $update['message']['chat']['id'], 'text' => sprintf(_("Il messaggio\n<code>" . $spotted . "</code>\ne' stato acquisito ed ora è in coda di moderazione per esser mandato a <b>%d</b> persone.\n"), $spotters), 'parse_mode' => 'HTML']);
}
// And now a lot of example doing the same thing in multiple way.
/**********************************************************************************
* First require your configuration file to start the framework. That's it!
* Note: the page obviusly dies here if you have not configured you database access.
**********************************************************************************/
require 'load.php';
// You can use `query` for a pure standard `mysqli` result.
$results = query('SELECT NOW() AS miao');
while ($row = $results->fetch_object()) {
printf("The database says that now it's '%s'", $row->miao);
}
// You can use `query_results` for an array of objects
$results = query_results('SELECT NOW() AS miao');
foreach ($results as $result) {
printf("The database says that now it's '%s'", $row->miao);
}
// You can use `query_result` for an object with only the first row
$row = query_row('SELECT NOW() AS miao');
printf("The database says that now it's '%s'", $row->miao);
// You can use `query_value` for only a single field from a single row:
$miao = query_value('SELECT NOW() AS miao', 'miao');
printf("The database says that now it's '%s'", $miao);
/*
* Note that the last parameter of `query_results`, `query_row`, and `query_value`
* can be a custom class name. To to something as this:
*
* @see MyClass.php
*/
require 'MyClass.php';
$row = query_row('SELECT NOW() AS miao', 'MyClass');
printf("The database says that now it's the year <b>%d</b> and the day is <b>%d</b>! " . "Time: %s. That is a clean PHP DateTime object retrieved with one line query!", $row->miao->format('Y'), $row->miao->format('d'), $row->miao->format('H:i'));
/**
* Pull account record from database
*
* @param int $account_id
* @return Array
*/
public static function accountInfo($account_id)
{
return query_row("SELECT *, date_part('epoch', now() - coalesce(last_login_failure, '1999-01-01')) AS login_failure_interval FROM accounts WHERE account_id = :account_id", [':account_id' => [$account_id, PDO::PARAM_INT]]);
}
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;
}
}
$up = 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);
$result = (bool) rco(query($up, $params));
return $result;
}
function get_provincia_ID($provincia_uid, $provincia_name)
{
global $provincie;
if (isset($provincie[$provincia_uid])) {
return $provincie[$provincia_uid];
}
insert_row('provincia', [new DBCol('provincia_uid', $provincia_uid, 's'), new DBCol('provincia_name', $provincia_name, 's')]);
$provincia = query_row(sprintf("SELECT provincia_ID, provincia_uid " . "FROM {$GLOBALS[T]('provincia')} " . "WHERE provincia_ID = %d", last_inserted_ID()), 'Provincia');
return $provincie[$provincia->provincia_uid] = $provincia->provincia_ID;
}
/**
* Handle account email confirmation
*
* @return Response
*/
public function confirm(Container $p_dependencies)
{
$request = RequestWrapper::$request;
$admin_override_pass = '******';
// Just a weak passphrase for simply confirming players.
$admin_override_request = $request->get('admin_override');
$acceptable_admin_override = $admin_override_pass === $admin_override_request;
$confirm = $request->get('confirm');
$aid = Filter::toNonNegativeInt($request->get('aid'));
$data = query_row('
SELECT player_id, uname,
accounts.verification_number as verification_number,
CASE WHEN active = 1 THEN 1 ELSE 0 END AS active,
accounts.active_email,
CASE WHEN accounts.confirmed = 1 THEN 1 ELSE 0 END as confirmed,
status, member, days, players.created_date
FROM accounts JOIN account_players ON _account_id = account_id
JOIN players ON _player_id = player_id
WHERE account_id = :acctId', array(':acctId' => $aid));
if (count($data)) {
$check = $data['verification_number'];
$confirmed = $data['confirmed'];
$username = $data['uname'];
} else {
$check = $confirmed = $username = null;
}
$confirmation_confirmed = false;
if ($confirmed != 1 && ($check && $confirm && $confirm == $check || $acceptable_admin_override)) {
// Confirmation number not null and matches
// or the admin override was met.
query('UPDATE accounts SET operational = true, confirmed=1
WHERE account_id = :accountID', array(':accountID' => $aid));
$statement = DatabaseConnection::$pdo->prepare('UPDATE players SET active = 1 WHERE player_id in
(SELECT _player_id FROM account_players
WHERE _account_id = :accountID)');
$statement->bindValue(':accountID', $aid);
$statement->execute();
// todo - test for success
$confirmation_confirmed = true;
}
$parts = ['confirmed' => $confirmed, 'username' => $username, 'confirmation_confirmed' => $confirmation_confirmed];
$options = ['quickstat' => false];
return new StreamedViewResponse('Account Confirmation', 'assistance.confirm.tpl', $parts, $options);
}
<?php
$alive = false;
$private = false;
if ($error = init($private, $alive)) {
display_error($error);
} else {
$admin_override_pass = '******';
// Just a weak passphrase for simply confirming players.
$admin_override_request = in('admin_override');
$acceptable_admin_override = $admin_override_pass === $admin_override_request;
$confirm = in('confirm');
$aid = positive_int(in('aid'));
$data = query_row('
SELECT player_id, uname, accounts.verification_number as verification_number, CASE WHEN active = 1 THEN 1 ELSE 0 END AS active, accounts.active_email,
CASE WHEN accounts.confirmed = 1 THEN 1 ELSE 0 END as confirmed, status, member, days, ip, players.created_date
FROM accounts JOIN account_players ON _account_id = account_id JOIN players ON _player_id = player_id
WHERE account_id = :acctId', array(':acctId' => $aid));
if (rco($data)) {
$check = $data['verification_number'];
$confirmed = $data['confirmed'];
$active = $data['active'];
$username = $data['uname'];
} else {
$active = $check = $confirmed = $username = null;
}
//debug($data, $confirm, $check, $confirmed);
//debug($check, $confirm);
$confirmation_confirmed = false;
if ($confirmed == 1) {
// Confirmation state from the database is already confirmed.
function find_account_info_by_oauth($id, $provider = 'facebook')
{
$id = positive_int($id);
$account_info = query_row('select * from accounts where ( oauth_id = :id and oauth_provider = :provider )
order by operational, type, created_date asc limit 1', array(':id' => $id, ':provider' => $provider));
if (empty($account_info) || !$account_info['account_id']) {
return false;
} else {
return $account_info;
}
}
/**
* Unique clan name check, ignores whitespace
*
* @param String $p_potential
* @return boolean
*/
public static function isUniqueClanName($p_potential)
{
return !(bool) query_row("SELECT clan_name FROM clan WHERE regexp_replace(clan_name, '[[:space:]]', '', 'g') ~~* regexp_replace(:testName, '[[:space:]]', '', 'g')", [':testName' => $p_potential]);
}
function clan_data($clan_id)
{
$sel = 'SELECT * FROM clan WHERE clan_id = :clan_id';
return query_row($sel, array(':clan_id' => $clan_id));
}
/**
* Find the clan belonging to a player, if any
*
* @param Player $player
* @return Clan|null
*/
public static function findByMember(Player $player)
{
$clan_info = query_row('select clan_id, clan_name, clan_created_date, clan_founder, clan_avatar_url, description from clan JOIN clan_player ON clan_id = _clan_id where _player_id = :pid', [':pid' => $player->id()]);
if (empty($clan_info)) {
return null;
} else {
return new Clan($clan_info['clan_id'], $clan_info['clan_name'], $clan_info);
}
}
