/**
*
*
*/
private function __construct()
{
$conn = MySQLUtil::getConnection();
$rs = mysql_query("SELECT * FROM ApplicationState") or die("Error while initializing Application: " . mysql_error());
if (mysql_affected_rows() > 0) {
$row = mysql_fetch_assoc($rs);
while ($row != false) {
//echo "add property {" . $row["AttributeKey"] . "," . $row["AttributeValue"] . "}";
$this->properties[$row["AttributeKey"]] = $row["AttributeValue"];
$row = mysql_fetch_assoc($rs);
}
}
/*
* Set any required properties that were not retrieved from the database
*/
$temp = $this->properties["TopTimeWaster"];
if (strlen($temp) <= 0) {
$rs = mysql_query("SELECT UserID FROM Users WHERE TotalTime = (SELECT MAX(TotalTime) FROM Users) LIMIT 1") or die("SQL ERROR: " . mysql_error());
$vals = mysql_fetch_array($rs);
$this->setTopTimeWaster($vals["UserID"]);
}
$temp = $this->properties["TopCoolPoints"];
if (strlen($temp) <= 0) {
$rs = mysql_query("SELECT UserID FROM Users WHERE CoolPoints = (SELECT MAX(CoolPoints) FROM Users) LIMIT 1") or die("SQL ERROR: " . mysql_error());
$vals = mysql_fetch_array($rs);
$this->setCoolestUser($vals["UserID"]);
}
}
public static function getConnection()
{
if (!MySQLUtil::$connected) {
/*
* get db props and set up doctrine manager
*/
$props = Properties::getInstance();
$props->loadFromFile();
$db_server = $props->getProperty("db.server");
$db_user = $props->getProperty("db.username");
$db_password = $props->getProperty("db.password");
$db_name = $props->getProperty("db.name");
$dsn = 'mysql://' . $db_user . ':' . $db_password . '@' . $db_server . '/' . $db_name;
$db = mysql_connect($db_server, $db_user, $db_password);
if (mysql_select_db($db_name)) {
MySQLUtil::$connected = true;
}
}
}
/**
* transactions available
* @return boolean
*/
public static function transactionsAvailable()
{
$pdo = MySQLUtil::getConnection();
$tsql = "SELECT ENGINE";
$tsql .= " FROM information_schema.TABLES";
$tsql .= " WHERE TABLE_SCHEMA = :schema";
$stmt = $pdo->prepare($tsql);
$db = MYSQL_DB;
$stmt->bindParam(':schema', $db, PDO::PARAM_STR);
$stmt->execute();
$answer = true;
while ($result = $stmt->fetch(PDO::FETCH_OBJ)) {
if (strtolower($result->ENGINE) != "innodb") {
$answer = false;
break;
}
}
return $answer;
}
/**
* sign in
* @param string $username
* @param string $password
* @return boolean
*/
public function signIn($username, $password)
{
$pdo = MySQLUtil::getConnection();
// hash the password
$password = DBUtils::hashPassword($password);
$tsql = "SELECT ur.name AS user_role, u.* FROM users AS u";
$tsql .= " INNER JOIN user_roles AS ur ON (ur.id = u.user_role_id)";
$tsql .= " WHERE u.username = :username AND u.password = :pass";
$stmt = $pdo->prepare($tsql);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':pass', $password, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_OBJ);
if ($result) {
AmfphpAuthentication::addRole($result->user_role);
unset($result->password);
return $result;
} else {
return false;
}
}
/**
* get users
* @return array
*/
public function getUsers()
{
$pdo = MySQLUtil::getConnection();
$tsql = "SELECT id, user_role_id, username, first_name, last_name FROM users";
$stmt = $pdo->prepare($tsql);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $results;
}
if (strlen($given_by) < 1) {
$given_by = $_GET["given_by"];
}
$missingParams = "";
if ($giveOrTake != "add" && $giveOrTake != "subtract") {
$missingParams = $missingParams . "plus_minus,";
}
if ($numberOfPoints <= 0) {
$missingParams = $missingParams . "number_points,";
}
if (strlen($userID) < 1) {
$missingParams = $missingParams . "userID,";
}
if (strlen($reasonFor) < 1) {
$missingParams = $missingParams . "reasonFor,";
}
if (strlen($given_by) < 1) {
$missingParams = $missingParams . "given_by,";
}
if (strlen($missingParams) > 0) {
echo "The following parameters are missing or invalid in this request: " . $missingParams;
} else {
$sql_string = "INSERT INTO UserCoolPoints (UserID, CoolPoints, AddPoints, Reason, ActionDate, GivenBy) VALUES ('" . $userID . "', " . $numberOfPoints . ", " . ($giveOrTake == "add" ? 1 : 0) . ", '" . $reasonFor . "', '" . date_format(date_create(), MySQLUtil::$MYSQL_DATE_FORMAT) . "', '" . $given_by . "')";
//echo $sql_string;
MySQLUtil::getConnection();
mysql_query($sql_string) or die("Unable to execute query '" . $sql_string . "'");
$sql_string = "UPDATE Users SET CoolPoints=((SELECT case when sum(CoolPoints) is null then 0 else sum(CoolPoints) end FROM UserCoolPoints WHERE AddPoints=1 AND UserID='" . $userID . "') - (SELECT case when sum(CoolPoints) is null then 0 else sum(CoolPoints) end FROM UserCoolPoints WHERE AddPoints=0 AND UserID='" . $userID . "')) WHERE UserID='" . $userID . "'";
mysql_query($sql_string) or die("Unable to execute query '" . $sql_string . "'");
$sql_string = "INSERT INTO NerderyEvents (EventTitle, EventDescription, UserID, EventTypeID, EventURL) VALUES (CONCAT((SELECT DisplayName FROM Users WHERE UserID='" . $userID . "'), ' became cooler'), CONCAT((SELECT DisplayName FROM Users WHERE UserID='" . $given_by . "'), ' gave ', (SELECT DisplayName FROM Users WHERE UserID='" . $userID . "'), ' some cool points.'), '" . $_SESSION["UserID"] . "', (SELECT EventTypeID FROM NerderyEventType WHERE EventTypeName='CoolPoints'), '/cool_points.php?userID=" . $userID . "')";
mysql_query($sql_string) or die("ERROR: " . mysql_error() . "<br>SQL: " . $sql_string);
}
