function gGetDb($db = "acc")
{
global $accdbobjects;
if (!is_array($accdbobjects)) {
$accdbobjects = array();
}
if (!isset($accdbobjects[$db])) {
global $cDatabaseConfig;
if (!array_key_exists($db, $cDatabaseConfig)) {
trigger_error("Database configuration not found for alias {$db}");
die;
}
try {
$accdbobject = new PdoDatabase($cDatabaseConfig[$db]["dsrcname"], $cDatabaseConfig[$db]["username"], $cDatabaseConfig[$db]["password"]);
} catch (PDOException $ex) {
// wrap around any potential stack traces which may include passwords
throw new Exception("Error connectiong to database '{$db}': " . $ex->getMessage());
}
$accdbobject->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// emulating prepared statements gives a performance boost on MySQL.
//
// however, our version of PDO doesn't seem to understand parameter types when emulating
// the prepared statements, so we're forced to turn this off for now.
// -- stw 2014-02-11
$accdbobject->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$accdbobjects[$db] = $accdbobject;
}
return $accdbobjects[$db];
}
/**
* @return string|null
*/
private function getNewest()
{
global $cDataClearIp, $cDataClearEmail;
$statement = $this->database->prepare("select max(date) from request where email != :email and ip != :ip;");
$statement->execute(array(':email' => $cDataClearEmail, ':ip' => $cDataClearIp));
$result = $statement->fetchColumn(0);
return $result;
}
/**
* Deletes the object from the database
*/
public function delete()
{
$statement = $this->dbObject->prepare("DELETE FROM `" . strtolower(get_called_class()) . "` WHERE id = :id LIMIT 1;");
$statement->bindValue(":id", $this->id);
$statement->execute();
$this->id = 0;
$this->isNew = true;
}
/**
* @param string $address
*/
public static function getByAddress($address, PdoDatabase $database)
{
$statement = $database->prepare("SELECT * FROM `" . strtolower(get_called_class()) . "` WHERE address = :id LIMIT 1;");
$statement->bindValue(":id", $address);
$statement->execute();
$resultObject = $statement->fetchObject(get_called_class());
if ($resultObject != false) {
$resultObject->isNew = false;
$resultObject->setDatabase($database);
}
return $resultObject;
}
public static function getByName($name, PdoDatabase $database)
{
$statement = $database->prepare("SELECT * FROM `emailtemplate` WHERE name = :name LIMIT 1;");
$statement->bindValue(":name", $name);
$statement->execute();
$resultObject = $statement->fetchObject(get_called_class());
if ($resultObject != false) {
$resultObject->isNew = false;
$resultObject->setDatabase($database);
}
return $resultObject;
}
public static function getByUsername($username, PdoDatabase $database)
{
$statement = $database->prepare("SELECT * FROM `" . strtolower(get_called_class()) . "` WHERE username = :id AND timestamp > date_sub(now(), interval 3 hour) LIMIT 1;");
$statement->bindValue(":id", $username);
$statement->execute();
$resultObject = $statement->fetchObject(get_called_class());
if ($resultObject != false) {
$resultObject->isNew = false;
$resultObject->setDatabase($database);
}
return $resultObject;
}
public function execute(\DOMElement $apiDocument)
{
$this->database = gGetDb();
$statusElement = $this->document->createElement("status");
$apiDocument->appendChild($statusElement);
$query = $this->database->prepare(<<<SQL
SELECT COUNT(*) AS count
FROM request
WHERE
status = :pstatus
AND emailconfirm = "Confirmed";
SQL
);
global $availableRequestStates;
foreach ($availableRequestStates as $key => $value) {
$query->bindValue(":pstatus", $key);
$query->execute();
$sus = $query->fetchColumn();
$statusElement->setAttribute($value['api'], $sus);
$query->closeCursor();
}
$query = $this->database->prepare(<<<SQL
SELECT COUNT(*) AS count
FROM ban
WHERE
(duration > UNIX_TIMESTAMP() OR duration = -1)
AND active = 1;
SQL
);
$query->execute();
$sus = $query->fetchColumn();
$statusElement->setAttribute("bans", $sus);
$query->closeCursor();
$query = $this->database->prepare("SELECT COUNT(*) AS count FROM user WHERE status = :ulevel;");
$query->bindValue(":ulevel", "Admin");
$query->execute();
$sus = $query->fetchColumn();
$statusElement->setAttribute("useradmin", $sus);
$query->closeCursor();
$query->bindValue(":ulevel", "User");
$query->execute();
$sus = $query->fetchColumn();
$statusElement->setAttribute("user", $sus);
$query->closeCursor();
$query->bindValue(":ulevel", "New");
$query->execute();
$sus = $query->fetchColumn();
$statusElement->setAttribute("usernew", $sus);
$query->closeCursor();
return $apiDocument;
}
/**
* @param string $connectionName
* @return PdoDatabase
* @throws Exception
*/
public static function getDatabaseConnection($connectionName)
{
if (!isset(self::$connections[$connectionName])) {
global $cDatabaseConfig;
if (!array_key_exists($connectionName, $cDatabaseConfig)) {
throw new Exception("Database configuration not found for alias {$connectionName}");
}
try {
$databaseObject = new PdoDatabase($cDatabaseConfig[$connectionName]["dsrcname"], $cDatabaseConfig[$connectionName]["username"], $cDatabaseConfig[$connectionName]["password"]);
} catch (PDOException $ex) {
// wrap around any potential stack traces which may include passwords
throw new Exception("Error connecting to database '{$connectionName}': " . $ex->getMessage());
}
$databaseObject->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// emulating prepared statements gives a performance boost on MySQL.
//
// however, our version of PDO doesn't seem to understand parameter types when emulating
// the prepared statements, so we're forced to turn this off for now.
// -- stw 2014-02-11
$databaseObject->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
self::$connections[$connectionName] = $databaseObject;
}
return self::$connections[$connectionName];
}
/**
* Gets all the usernames in the system
* @param PdoDatabase $database
* @param null|bool|string $filter If null, no filter. If true, active users only, otherwise provided status.
* @return string[]
*/
public static function getAllUsernames(PdoDatabase $database, $filter = null)
{
if ($filter === null) {
$userListQuery = "SELECT username FROM user;";
$userListResult = $database->query($userListQuery);
} elseif ($filter === true) {
$userListQuery = "SELECT username FROM user WHERE status IN ('User', 'Admin');";
$userListResult = $database->query($userListQuery);
} else {
$userListQuery = "SELECT username FROM user WHERE status = :status;";
$userListResult = $database->prepare($userListQuery);
$userListResult->execute(array(":status" => $filter));
}
return $userListResult->fetchAll(PDO::FETCH_COLUMN);
}
private function fetchAdminData(\DOMElement $userElement)
{
$query = "SELECT COUNT(*) AS count FROM acc_log WHERE log_user = :username AND log_action = :action";
$statement = $this->database->prepare($query);
$statement->bindValue(":username", $this->user->getUsername());
$statement->bindValue(":action", "Suspended");
$statement->execute();
$sus = $statement->fetchColumn();
$userElement->setAttribute("suspended", $sus);
$statement->closeCursor();
$statement->bindValue(":action", "Promoted");
$statement->execute();
$pro = $statement->fetchColumn();
$userElement->setAttribute("promoted", $pro);
$statement->closeCursor();
$statement->bindValue(":action", "Approved");
$statement->execute();
$app = $statement->fetchColumn();
$userElement->setAttribute("approved", $app);
$statement->closeCursor();
$statement->bindValue(":action", "Demoted");
$statement->execute();
$dem = $statement->fetchColumn();
$userElement->setAttribute("demoted", $dem);
$statement->closeCursor();
$statement->bindValue(":action", "Declined");
$statement->execute();
$dec = $statement->fetchColumn();
$userElement->setAttribute("declined", $dec);
$statement->closeCursor();
$statement->bindValue(":action", "Renamed");
$statement->execute();
$rnc = $statement->fetchColumn();
$userElement->setAttribute("renamed", $rnc);
$statement->closeCursor();
$statement->bindValue(":action", "Edited");
$statement->execute();
$mec = $statement->fetchColumn();
$userElement->setAttribute("edited", $mec);
$statement->closeCursor();
$statement->bindValue(":action", "Prefchange");
$statement->execute();
$pcc = $statement->fetchColumn();
$userElement->setAttribute("prefchange", $pcc);
$statement->closeCursor();
// Combine all three actions affecting Welcome templates into one count.
$combinedquery = $this->database->prepare(<<<SQL
SELECT
COUNT(*) AS count
FROM acc_log
WHERE log_user = :username
AND log_action IN ('CreatedTemplate', 'EditedTemplate', 'DeletedTemplate');
SQL
);
$combinedquery->bindValue(":username", $this->user->getUsername());
$combinedquery->execute();
$dtc = $combinedquery->fetchColumn();
$userElement->setAttribute("welctempchange", $dtc);
$combinedquery->closeCursor();
// Combine both actions affecting Email templates into one count.
$combinedquery = $this->database->prepare(<<<SQL
SELECT COUNT(*) AS count
FROM acc_log
WHERE log_user = :username
AND log_action IN ('CreatedEmail', 'EditedEmail');
SQL
);
$combinedquery->bindValue(":username", $this->user->getUsername());
$combinedquery->execute();
$cec = $combinedquery->fetchColumn();
$userElement->setAttribute("emailtempchange", $cec);
$combinedquery->closeCursor();
}
/**
* Summary of getRequestLogs
* @param int $requestId ID of the request to get logs for
* @param PdoDatabase $db Database to use
* @return array|bool
*/
public static function getRequestLogs($requestId, PdoDatabase $db)
{
$logStatement = $db->prepare("SELECT * FROM log WHERE objecttype = 'Request' AND objectid = :requestid ORDER BY timestamp DESC");
$result = $logStatement->execute(array(":requestid" => $requestId));
if ($result) {
$data = $logStatement->fetchAll(PDO::FETCH_CLASS, "Log");
foreach ($data as $entry) {
$entry->isNew = false;
$entry->setDatabase($db);
}
return $data;
}
return false;
}