public function connect()
{
$dbh = DatabaseHandler::getInstance();
$dbh->filePath = __DIR__ . '/squid_database.sqlite';
$dbh->openDBMS('sqlite');
return $dbh;
}
public function openDatabase()
{
try {
$dbh = DatabaseHandler::getInstance();
$databaseLink = new PDO('pgsql:' . $dbh->name, $dbh->host, $dbh->user, $dbh->password);
$databaseLink->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $exception) {
$databaseException = $exception->getMessage();
trigger_error($databaseException, E_USER_ERROR);
}
return $databaseLink;
}
public function openDatabase()
{
try {
$dbh = DatabaseHandler::getInstance();
$databaseLink = new PDO('sqlite:' . $dbh->filePath);
$databaseLink->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $exception) {
$databaseException = $exception->getMessage();
trigger_error($databaseException, E_USER_ERROR);
}
return $databaseLink;
}
public static function GetTransactionHistory($userId)
{
$dbHandler = DatabaseHandler::getInstance();
$history = $dbHandler->execQuery("SELECT * FROM transactions WHERE (sender_id='" . $userId . "' OR receiver_id='" . $userId . "') AND approved='1';");
$dataArray = array();
while ($row = $history->fetch_assoc()) {
$amount = $row['amount'];
$sourceIBAN = $row['sender_id'];
$receiverIBAN = $row['receiver_id'];
//if send by the user the amount will be negative
if ($sourceIBAN == $userId) {
$amount *= -1.0;
}
$dataArray[] = new Transaction($row['transaction_date'], self::GetAccountName($sourceIBAN), $sourceIBAN, self::GetAccountName($receiverIBAN), $receiverIBAN, $amount, $row['description']);
}
return $dataArray;
}
/**
* @param $email
*/
public static function clearLock($email)
{
$dbHandler = DatabaseHandler::getInstance();
$query = "SELECT id FROM users WHERE mail_address='" . $email . "';";
$res = $dbHandler->execQuery($query);
$row = $res->fetch_assoc();
if ($row != NULL) {
$id = $row['id'];
$dbHandler->execQuery("UPDATE " . self::$table . " SET failed_login_attempt=0 WHERE id='" . $id . "';");
}
}
public function denyRequest($id, $transaction)
{
$table = "users";
if ($transaction) {
$table = "transactions";
}
$dbHandler = DatabaseHandler::getInstance();
$dbHandler->execQuery("DELETE FROM " . $table . " WHERE id='" . $id . "';");
if (!$transaction) {
$dbHandler->execQuery("DELETE FROM accounts WHERE user_id='" . $id . "';");
$dbHandler->execQuery("DELETE FROM scs WHERE user_id='" . $id . "';");
}
}
<?php
session_start();
include_once "../Model/DatabaseHandler.php";
if (isset($_SESSION['isEmployee'])) {
if ($_SESSION['isEmployee'] == false) {
exit;
}
} else {
exit;
}
//get the customers
$dbHandler = DatabaseHandler::getInstance();
$data = $dbHandler->execQuery("SELECT * FROM users INNER JOIN accounts ON users.id = accounts.user_id;");
echo "<table>\n<tr>\n<th>IBAN</th>\n<th>Balance</th>\n<th>First Name</th>\n<th>Last Name</th>\n<th>Email</th>\n<th>Registration date</th>\n<th>Transaction History</th>\n</tr>";
while ($row = $data->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['balance'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['mail_address'] . "</td>";
echo "<td>" . $row['registration_date'] . "</td>";
echo "<td>\n <form action='../View/TransactionHistoryEmpl.php' method='post'>\n <input type='hidden' name='iban' value=" . $row['id'] . " />\n <input type='hidden' name='firstName' value=" . $row['first_name'] . " />\n <input type='hidden' name='lastName' value=" . $row['last_name'] . " />\n <input type='hidden' name='balance' value=" . $row['balance'] . " />\n <input type='submit' value='View'/>\n </form>\n </td>";
echo "<td>\n <form action='DownloadHistory.php' method='post'>\n <input type='hidden' name='iban' value=" . $row['id'] . " />\n <input type='submit' value='Export'/>\n </form>\n </td>";
}
echo "</table>";
echo "<form action='../View/administration.php' method='post'>\n <input type ='submit' value='Back'/>\n</form>";
public static function register($email, $firstName, $lastName, $password, $isEmployee, $usesSCS)
{
$email = mysql_real_escape_string($email);
$firstName = mysql_real_escape_string($firstName);
$lastName = mysql_real_escape_string($lastName);
$dbHandler = DatabaseHandler::getInstance();
$res = $dbHandler->execQuery("SELECT * FROM users WHERE mail_address='" . $email . "';");
if ($res->fetch_assoc() != NULL) {
return "ERROR: An account with that email has already been created!\n";
}
$query = "INSERT INTO users (first_name, last_name, isEmployee, approved, mail_address, password, uses_scs)";
$query .= " VALUES ('" . $firstName . "', '" . $lastName . "', ";
if ($isEmployee) {
$query .= "TRUE, FALSE, ";
} else {
$query .= "FALSE, FALSE, ";
}
$query .= "'" . $email . "', ";
$query .= "'" . self::calculateHash($password) . "', ";
if ($usesSCS) {
$query .= "TRUE" . ");";
} else {
$query .= "FALSE" . ");";
}
$rc = $dbHandler->execQuery($query);
if ($rc != TRUE) {
return "ERROR: New User couldn't be stored in Database!\n";
}
// Get id of newly created user
$query = "SELECT id FROM users WHERE mail_address='" . $email . "';";
$res = $dbHandler->execQuery($query);
$row = $res->fetch_assoc();
$userID = $row['id'];
//Add an entry for the user in passwdsec table
$query = "INSERT INTO passwdsec VALUES (" . $userID . ", DEFAULT , DEFAULT ,0, DEFAULT)";
if ($dbHandler->execQuery($query) != TRUE) {
return "ERROR: New entry in passwdsec for new user couldn't be created!\n";
}
// If the new Account is for an employee, we are already done here.
if ($isEmployee) {
return TRUE;
}
// Add new account for the customer
$query = "INSERT INTO accounts VALUES (" . $userID . ", 0);";
if ($dbHandler->execQuery($query) != TRUE) {
return "ERROR: Account entry for new user couldn't be created!\n";
}
//Add SCS row if user chose SCS
if ($usesSCS) {
$pin = mt_rand(100000, 999999);
$pin_string = (string) $pin;
$query = "INSERT INTO scs VALUES (" . $userID . ", '" . $pin_string . "', 0);";
if ($dbHandler->execQuery($query) != TRUE) {
return "ERROR: Account entry for new user couldn't be created!\n";
}
return "You have registered successfully!<br><br>Your SCS PIN is <b>" . $pin_string . "</b>!<br>Please remember or save it somewhere <b>NOW</b>. It will not be shown again!";
} else {
return "You have registered successfully!<br>Your PDF password is <b>" . self::CalcPDFPassword(self::calculateHash($password)) . "</b>! Please remember it or save it somewhere <b>NOW</b>. It will not be shown again!";
}
return TRUE;
}
public static function performTransaction($id)
{
$dbHandler = DatabaseHandler::getInstance();
$res = $dbHandler->execQuery("SELECT * FROM transactions WHERE id='" . $id . "';");
$row = $res->fetch_assoc();
$amount = $row['amount'];
$sender = $row['sender_id'];
$receiver = $row['receiver_id'];
self::changeBalance(-$amount, $sender);
self::changeBalance($amount, $receiver);
}