/**
* Fetch submission details from the database
*
* @param int $id ID to search for
*
* @return array Result of the query
* @throws \Exception Required parameter missing
*/
public static function getByID($id = null)
{
if ($id === null) {
throw new InvalidArgumentException("Required parameter missing");
}
return \jf::SQL("SELECT * FROM " . self::TABLE_NAME . " WHERE ID = ?", $id);
}
function Insert()
{
if (jf::$RunMode->IsCLI()) {
return false;
}
$res = jf::SQL("INSERT INTO {$this->TablePrefix()}stats (UserID,SessionID,Timestamp,Page,Query,IP,Host,Protocol,UserAgent) VALUES\n\t\t\t(?,?,?,?,?,?,?,?,?)", jf::CurrentUser() ?: 0, jf::$Session->SessionID(), jf::time(), HttpRequest::URI(), HttpRequest::QueryString(), HttpRequest::IP(), HttpRequest::Host(), HttpRequest::Protocol(), HttpRequest::UserAgent());
return $res;
}
/**
* Returns all roles assigned to a permission
*
* @param integer $Permission
* ID
* @param boolean $OnlyIDs
* if true, result would be a 1D array of IDs
* @return Array 2D or 1D or null
*/
function Roles($Permission, $OnlyIDs = true)
{
if (!is_numeric($Permission)) {
$Permission = $this->Permission_ID($Permission);
}
if ($OnlyIDs) {
$Res = jf::SQL("SELECT RoleID AS `ID` FROM\n\t\t\t{$this->TablePrefix()}rbac_rolepermissions WHERE PermissionID=? ORDER BY RoleID", $Permission);
if (is_array($Res)) {
$out = array();
foreach ($Res as $R) {
$out[] = $R['ID'];
}
return $out;
} else {
return null;
}
} else {
return jf::SQL("SELECT `TP`.* FROM {$this->TablePrefix()}rbac_rolepermissions AS `TR`\n\t\t\tRIGHT JOIN {$this->TablePrefix()}rbac_roles AS `TP` ON (`TR`.RoleID=`TP`.ID)\n\t\t\tWHERE PermissionID=? ORDER BY TP.RoleID", $Permission);
}
}
/**
* delete all user settings
* @throws \Exception
* @return int, number of rows
*/
function DeleteAllUser($UserID = null)
{
if ($UserID === null) {
if (jf::CurrentUser() == null) {
throw new \Exception("Can not load user options without a logged in user.");
} else {
$UserID = jf::CurrentUser();
}
}
$r = jf::SQL("DELETE FROM {$this->TablePrefix()}options WHERE UserID=?", $UserID);
return $r;
}
/**
* Reset the lesson
*/
public function reset()
{
\jf::SQL("DROP TABLE IF EXISTS " . self::TABLE_NAME);
\jf::SQL("CREATE TABLE " . self::TABLE_NAME . "(station int auto_increment primary key,\n name varchar(30) not null, state varchar(5) not null, min_temp int not null, max_temp int not null)");
\jf::SQL("INSERT INTO " . self::TABLE_NAME . "(name, state, min_temp, max_temp) values ('Columbia', 'MD', -10, 20),\n ('Seattle', 'WA', -15, 18), ('New York', 'NY', -10, 20), ('Houston', 'TX', 20, 30),\n ('Camp David', 'MD', -5, 20), ('Ice Station', 'NA', -30, 0)");
$this->setCompleted(false);
return true;
}
/**
* Checks whether or not a session is logged in
* @param string $SessionID
* @return boolean
*/
function IsLoggedIn($SessionID = null)
{
if ($SessionID === null) {
$SessionID = $this->SessionID();
}
$Result = jf::SQL("SELECT COUNT(*) AS Result FROM {$this->TablePrefix()}session WHERE SessionID=? AND UserID!=0", $SessionID);
return $Result[0]['Result'] >= 1;
}
/**
* Reset the lesson
*/
public function reset()
{
\jf::SQL("DROP TABLE IF EXISTS " . self::TABLE_NAME);
\jf::SQL("CREATE TABLE " . self::TABLE_NAME . "(id int not null auto_increment primary key,\n role varchar(30) not null, first_name varchar(50), last_name varchar(50), password varchar(50),\n street varchar(100), city varchar(50), phone varchar(20), salary int, cc_no varchar(20),\n cc_limit varchar(10))");
\jf::SQL("INSERT INTO " . self::TABLE_NAME . "(role, first_name, last_name, password, street, city, phone, salary, cc_no, cc_limit) values\n ('employee', 'Larry', 'Stooge', 'larry', '9175 Guilford Rd',\n 'New York, NY', '443-689-0192', '55000', '2578546969853547', '5000'),\n ('employee', 'Curly', 'Stooge', 'curly', '1112 Crusoe Lane',\n 'New York, NY', '410-667-6654', '50000', 'NA', '0'),\n ('employee', 'Eric', 'Walker', 'eric', '1160 Prescott Rd',\n 'New York, NY', '410-667-6654', '13000', 'NA', '0'),\n ('employee', 'Tom', 'Cat', 'tom', '2211 HyperThread Rd',\n 'New York, NY', '443-599-0762', '80000', '5481360857968521', '30000'),\n ('manager', 'Moe', 'Stooge', 'moe', '3013 AMD Ave',\n 'New York, NY', '443-938-5301', '140000', 'NA', '0'),\n ('manager', 'David', 'Giambi', 'david', '5132 DIMM Avenue',\n 'New York, NY', '610-521-8413', '100000', '6981754825018101', '10000'),\n ('admin', 'John', 'Wayne', 'john', '129 Third St',\n 'New York, NY', '610-213-1134', '200000', '4437334565679921', '30000')");
$this->setCompleted(false);
$this->deleteSessionData(self::SESSION_NAME);
return true;
}
/**
* Adds a child to the beginning of a node's children
*
* @param Array $FieldValueArray key-paired field-values to insert
* @param string $ConditionString of the parent node
* @param string $Rest optional, rest of variables to fill in placeholders of condition string, one variable for each ? in condition
* @return Integer ChildID
*/
function InsertChildData($FieldValueArray = array(), $ConditionString = null, $Rest = null)
{
$this->Lock();
//Find the Sibling
$Arguments = func_get_args();
array_shift($Arguments);
//first argument, the array
array_shift($Arguments);
if ($ConditionString) {
$ConditionString = "WHERE {$ConditionString}";
}
$Query = "SELECT {$this->Right()} AS `Right`, {$this->Left()} AS `Left`" . " FROM {$this->Table()} {$ConditionString}";
array_unshift($Arguments, $Query);
$Parent = call_user_func_array("jf::SQL", $Arguments);
$Parent = $Parent[0];
if ($Parent == null) {
$Parent["Left"] = $Parent["Right"] = 0;
}
jf::SQL("UPDATE {$this->Table()} SET {$this->Right()} = {$this->Right()} + 2 WHERE {$this->Right()} >= ?", $Parent["Right"]);
jf::SQL("UPDATE {$this->Table()} SET {$this->Left()} = {$this->Left()} + 2 WHERE {$this->Left()} > ?", $Parent["Right"]);
$FieldsString = $ValuesString = "";
$Values = array();
if ($FieldValueArray) {
foreach ($FieldValueArray as $k => $v) {
$FieldsString .= ",";
$FieldsString .= "`" . $k . "`";
$ValuesString .= ",?";
$Values[] = $v;
}
}
$Query = "INSERT INTO {$this->Table()} ({$this->Left()},{$this->Right()} {$FieldsString}) " . "VALUES(?,? {$ValuesString})";
array_unshift($Values, $Parent["Right"] + 1);
array_unshift($Values, $Parent["Right"]);
array_unshift($Values, $Query);
$Res = call_user_func_array("jf::SQL", $Values);
$this->Unlock();
return $Res;
}
/**
* Returns total number of users
* @return integer
*/
function UserCount()
{
$res = jf::SQL("SELECT COUNT(*) FROM {$this->TablePrefix()}users");
return $res[0]['COUNT(*)'];
}
/**
* Returns all permissions assigned to a role
*
* @param integer $Role
* ID
* @param boolean $OnlyIDs
* if true, result would be a 1D array of IDs
* @return Array 2D or 1D or null
* the two dimensional array would have ID,Title and Description of
* permissions
*/
function Permissions($Role, $OnlyIDs = true)
{
if ($OnlyIDs) {
$Res = jf::SQL("SELECT PermissionID AS `ID` FROM {$this->TablePrefix()}rbac_rolepermissions WHERE RoleID=? ORDER BY PermissionID", $Role);
if (is_array($Res)) {
$out = array();
foreach ($Res as $R) {
$out[] = $R['ID'];
}
return $out;
} else {
return null;
}
} else {
return jf::SQL("SELECT `TP`.* FROM {$this->TablePrefix()}rbac_rolepermissions AS `TR`\n\t\tRIGHT JOIN {$this->TablePrefix()}rbac_permissions AS `TP` ON (`TR`.PermissionID=`TP`.ID)\n\t\tWHERE RoleID=? ORDER BY TP.PermissionID", $Role);
}
}
/**
* Returns user ID by email
* @param string $Email
* @return integer|null UserID
*/
function FindByEmail($Email)
{
$res = jf::SQL("SELECT * FROM {$this->TablePrefix()}xuser WHERE LOWER(Email)=LOWER(?)", $Email);
if ($res) {
return $res[0]['ID'];
} else {
return null;
}
}
/**
* Get the ID of the active contest
*
* @return int ID of the active contest, null if no
* contest is active
*/
public static function getActiveID()
{
$currentTime = time();
$query = "SELECT * FROM " . self::TABLE_NAME . " WHERE EndTimestamp > {$currentTime}";
$result = \jf::SQL($query);
return $result[0]['ID'];
}
function testFetch()
{
$insDb = jf::db();
$r = jf::SQL("SELECT * FROM {$this->TablePrefix()}users;");
$myStatement = $insDb->prepare("SELECT * FROM {$this->TablePrefix()}users;");
$myStatement->execute();
$this->assertEquals($r[0], $myStatement->fetch());
}
private function getMessages()
{
return \jf::SQL("SELECT * FROM " . self::TABLE_NAME);
}
/**
* Remove all role-permission relations
* mostly used for testing
*
* @param boolean $Ensure
* must set or throws error
* @return number of deleted relations
*/
function ResetAssignments($Ensure = false)
{
if ($Ensure !== true) {
throw new \Exception("You must pass true to this function, otherwise it won't work.");
return;
}
$res = jf::SQL("DELETE FROM {$this->TablePrefix()}rbac_rolepermissions");
$Adapter = DatabaseManager::Configuration()->Adapter;
if ($Adapter == "mysqli" or $Adapter == "pdo_mysql") {
jf::SQL("ALTER TABLE {$this->TablePrefix()}rbac_rolepermissions AUTO_INCREMENT =1 ");
} elseif ($Adapter == "pdo_sqlite") {
jf::SQL("delete from sqlite_sequence where name=? ", $this->TablePrefix() . "_rbac_rolepermissions");
} else {
throw new \Exception("RBAC can not reset table on this type of database: {$Adapter}");
}
$this->Assign("root", "root");
return $res;
}
private function getUserId($name = null)
{
$user = \jf::SQL("SELECT * FROM " . self::TABLE_NAME . " where name = ?", $name);
if (array_key_exists(0, $user)) {
return $user[0]['user_id'];
} else {
return -1;
}
}
/**
* Retrives the full tree including Depth field.
*
* @return 2DArray Rowset
*/
function FullTree()
{
$Res = jf::SQL("SELECT node.*, (COUNT(parent.{$this->ID()}) - 1) AS Depth\n FROM {$this->Table()} AS node,\n {$this->Table()} AS parent\n WHERE node.{$this->Left()} BETWEEN parent.{$this->Left()} AND parent.{$this->Right()}\n GROUP BY node.{$this->ID()}\n ORDER BY node.{$this->Left()}");
return $Res;
}
static function Log($Subject, $Content, $Severity = 0)
{
if (jf::$App) {
return jf::SQL("INSERT INTO " . jf::TablePrefix() . "logs (Subject,Data,Severity,UserID,SessionID,Timestamp) \n\t\t" . "VALUES (?,?,?,?,?,?)", $Subject, $Content, $Severity, jf::CurrentUser(), jf::$Session->SessionID(), jf::time());
}
}
/**
* Increments the CompletedCount by 1
*
* @throws InvalidArgumentException If required parameters are missing
*/
public static function incrementCompletedCount($challengeName = null)
{
if ($challengeName === null) {
throw new InvalidArgumentException("Required parameter missing");
}
\jf::SQL("UPDATE " . self::TABLE_NAME . " SET CompletedCount = CompletedCount + 1 WHERE ChallengeName = ?", $challengeName);
}
