/**
* Constructor : open a folder or create it
*
* @param string|array $dossier folder id or args to create it
* @param boolean $creation true if it's a creation
*
* @return void
*/
public function __construct($dossier, $creation = false)
{
// On commence par paramétrer les données PDO
$this->_link = Configuration::read('db.link');
if ($creation) {
// On prépare la requête
$query = 'INSERT INTO `dossiers` (`dossier_nom`,
`dossier_description`,
`dossier_date_ouverture`)
VALUES (:nom,
:desc,
NOW())';
$query = $this->_link->prepare($query);
$query->bindParam(':nom', $dossier['nom']);
$query->bindParam(':desc', $dossier['desc']);
// On exécute la création du dossier
$query->execute();
// On récupère l'identifiant du dossier
$dossier = md5($this->_link->lastInsertId());
// On vide les informations de BDD
unset($query);
}
// On récupère les informations sur le dossier
$query = 'SELECT *
FROM `dossiers`
WHERE MD5(`dossier_id`) = :id';
$query = $this->_link->prepare($query);
$query->bindParam(':id', $dossier);
$query->execute();
$dossier = $query->fetch(PDO::FETCH_ASSOC);
// On fabrique de MD5 du dossier
$dossier['dossier_md5'] = md5($dossier['dossier_id']);
// On déplace ces informations dans la propriété $dossier
$this->dossier = $dossier;
}
private function registerNewUser($first_name, $middle_name, $last_name, $email_id, $phone_number, $designation)
{
$user_name = trim($first_name);
$user_email = trim($email_id);
if ($this->databaseConnection()) {
// check if username or email already exists
$query_check_user_name = $this->db_connection->prepare('SELECT first_name, email_id FROM police_directory WHERE email_id=:user_email');
$query_check_user_name->bindValue(':user_email', $email_id, PDO::PARAM_STR);
$query_check_user_name->execute();
$result = $query_check_user_name->fetchAll();
// if username or/and email find in the database
// TODO: this is really awful!
if (count($result) > 0) {
for ($i = 0; $i < count($result); $i++) {
$this->errors[] = "This person is already added or you are adding same email id . Please check your directory . ";
}
} else {
$query_new_user_insert = $this->db_connection->prepare('INSERT INTO police_directory (first_name, middle_name, last_name, email_id, phone_number, designation , entry_time) VALUES(:first_name, :middle_name, :last_name, :email_id ,:phone_number, :designation, now() )');
$query_new_user_insert->bindValue(':first_name', $first_name, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':middle_name', $middle_name, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':last_name', $last_name, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':email_id', $email_id, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':phone_number', $phone_number, PDO::PARAM_INT);
$query_new_user_insert->bindValue(':designation', $designation, PDO::PARAM_STR);
$query_new_user_insert->execute();
$user_id = $this->db_connection->lastInsertId();
if ($query_new_user_insert) {
$this->errors[] = "Data added successfully";
} else {
$this->errors[] = "Error in adding data . Please try again . ";
}
}
}
}
/**
* Checks and inserts a new account email into the database
*
* @return string a message indicating the action status
*/
public function createAccount()
{
$u = trim($_POST['username']);
$v = sha1(time());
$sql = "SELECT COUNT(Username) AS theCount\n FROM users\n WHERE Username=:email";
if ($stmt = $this->_db->prepare($sql)) {
$stmt->bindParam(":email", $u, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch();
if ($row['theCount'] != 0) {
return "<h2> Error </h2>" . "<p> Sorry, that email is already in use. " . "Please try again. </p>";
}
if (!$this->sendVerificationEmail($u, $v)) {
return "<h2> Error </h2>" . "<p> There was an error sending your" . " verification email. Please " . '<a href="mailto:sahbingo@gmail.com">contact ' . "us</a> for support. We apologize for the " . "inconvenience. </p>";
}
$stmt->closeCursor();
}
$sql = "INSERT INTO users(Username, ver_code)\n VALUES(:email, :ver)";
if ($stmt = $this->_db->prepare($sql)) {
$stmt->bindParam(":email", $u, PDO::PARAM_STR);
$stmt->bindParam(":ver", $v, PDO::PARAM_STR);
$stmt->execute();
$stmt->closeCursor();
$userID = $this->_db->lastInsertId();
return "<h2> Success! </h2>" . "<p> Your account was successfully " . "created with the username <strong>{$u}</strong>." . " Check your email!";
} else {
return "<h2> Error </h2><p> Couldn't insert the " . "user information into the database. </p>";
}
}
public function save(array $dados)
{
$this->beforeSave();
$indices = implode(", ", array_keys($dados));
$valores = "'" . implode("', '", array_values($dados)) . "'";
$q = $this->db->query("INSERT INTO `{$this->tableName}` ({$indices}) VALUES ({$valores})");
$this->lastInsertId = $this->db->lastInsertId();
}
public function create($insert_array)
{
try {
$this->db->insert("`" . $this->table . "`", $insert_array);
return $this->db->lastInsertId();
} catch (Exception $e) {
return false;
}
}
/**
* Insert data in database with currentdate and time
* @author Jim Ahlstrand
* @return int Id of inserted row
*/
private function insertComment()
{
// Prepare variables
$date = $this->date->format("Y-m-d H:i:s");
$subcomments = serialize($this->subcomments);
$sth = $this->dbh->prepare(SQL_INSERT_COMMENT);
$sth->bindParam(":user", $this->user, PDO::PARAM_INT);
$sth->bindParam(':date', $date, PDO::PARAM_STR);
$sth->bindParam(":data", $this->data, PDO::PARAM_STR);
$sth->bindParam(":subcomments", $subcomments, PDO::PARAM_STR);
$sth->execute();
return $this->dbh->lastInsertId();
}
/**
* Adds an item to the database
*
* @return mixed ID of the new item on success, error message on failure
*/
public function addItem()
{
$userid = $_POST['userid'];
$name = $_POST['name'];
$type = $_POST['type'];
$color = $_POST['color'];
$thickness = $_POST['thickness'];
$formality = $_POST['formality'];
$length = $_POST['length'];
$attractiveness = $_POST['attractiveness'];
$fitness = $_POST['fitness'];
$sql = "INSERT INTO items\n (UserID, ItemName, ItemType, ItemColor, ItemThickness, ItemFormality, ItemLength, ItemAttractiveness, ItemFitness)\n VALUES (:userid, :name, :type, :color, :thickness, :formality, :length, :attractiveness, :fitness)";
try {
$stmt = $this->_db->prepare($sql);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':type', $type, PDO::PARAM_STR);
$stmt->bindParam(':color', $color, PDO::PARAM_STR);
$stmt->bindParam(':thickness', $thickness, PDO::PARAM_INT);
$stmt->bindParam(':formality', $formality, PDO::PARAM_INT);
$stmt->bindParam(':length', $length, PDO::PARAM_INT);
$stmt->bindParam(':attractiveness', $attractiveness, PDO::PARAM_INT);
$stmt->bindParam(':fitness', $fitness, PDO::PARAM_INT);
$stmt->execute();
$stmt->closeCursor();
return $this->_db->lastInsertId();
} catch (PDOException $e) {
return $e->getMessage();
}
}
/**
* Returns compiled email to be sent to the user with deny message
*
* @param string $email The user's email address
* @param string $ver The random verification code for the user
* @param string $sendgm SendGrid instance
* @return $sendgm Return compiled mail
*/
public function sendDenyEmail($email)
{
$sendgrid = new SendGrid($_ENV['SG_KEY']);
$mail = new SendGrid\Email();
$mail->addTo($email)->setFrom('*****@*****.**')->setFromName('WolvesOfOld')->setSubject('[WolvesOfOld] Account Denied')->setText("We're sorry. . .\n \nYour request has been denied. \n\nPlease contact us via the 'Contact Us' tab or ask one of our Clan admins.\n \nThank you for your interest in the WolvesOfOld!\n \n--\nThanks!\n \nRairaku\nwoobs.herokuapp.com")->setSubstitutions(array('%email%' => array($email)));
try {
$sendgrid->send($mail);
} catch (\SendGrid\Exception $e) {
echo $e->getCode();
foreach ($e->getErrors() as $er) {
echo $er;
}
return "<h2> Error </h2>" . "<p> There was an error sending" . " deny email. </p>";
}
$sql = "DELETE FROM users\n WHERE Email=:email";
if ($stmt = $this->_db->prepare($sql)) {
$stmt->bindParam(":email", $email, PDO::PARAM_STR);
$stmt->execute();
$stmt->closeCursor();
$userID = $this->_db->lastInsertId();
$url = dechex($userID);
return "<h2> Email has been removed. </h2>";
} else {
return "<h2> Error </h2><p> Couldn't delete the " . "user information from the database. </p>";
}
}
/**
* Stores a URL in the database.
*
* @param string $url URL to store
* @return int Insert id
*/
public function store($url)
{
$datetime = date('Y-m-d H:i:s');
$statement = $this->connection->prepare('INSERT INTO urls (url, created) VALUES (?,?)');
$statement->execute(array($url, $datetime));
return $this->connection->lastInsertId();
}
/**
* @author Oliver Rosander, Jim Ahlstrand
* @return void
*/
public function createSubmission()
{
$user = 0;
$date = date("Y-m-d H:i:s");
$files = serialize(array());
$reviews = serialize(array());
$comments = serialize(array());
$grade = 0;
// Insert the new submission
// TODO This should be using the submissions class
$sth = $this->dbh->prepare(SQL_INSERT_SUBMISSION);
$sth->bindParam(":user", $user, PDO::PARAM_INT);
$sth->bindParam(':date', $date, PDO::PARAM_STR);
$sth->bindParam(":files", $files, PDO::PARAM_STR);
$sth->bindParam(":reviews", $reviews, PDO::PARAM_STR);
$sth->bindParam(":comments", $comments, PDO::PARAM_STR);
$sth->bindParam(":grade", $grade, PDO::PARAM_INT);
$sth->bindParam(":stage", $this->stage, PDO::PARAM_INT);
$sth->execute();
// Add the submission
$this->submissions[] = intval($this->dbh->lastInsertId());
// Update the Database
$submissions = serialize($this->submissions);
$sth = $this->dbh->prepare(SQL_UPDATE_PROJECT_SUBMISSION_WHERE_ID);
$sth->bindParam(":submissions", $submissions, PDO::PARAM_STR);
$sth->bindParam(":id", $this->id, PDO::PARAM_INT);
$sth->execute();
}
public function lastInsertId($name = NULL)
{
if (!$this->db) {
throw new \Exception('There is no database connection.');
}
return $this->db->lastInsertId($name);
}
/**
* Add a new element in the tree near element with number id.
*
* @param integer $ID Number of a parental element
* @param array $condition Array structure: array('and' => array('id = 0', 'id2 >= 3'), 'or' => array('sec = \'www\'', 'sec2 <> \'erere\'')), etc where array key - condition (AND, OR, etc), value - condition string
* @param array $data Contains parameters for additional fields of a tree (if is): array('filed name' => 'importance', etc)
* @return integer Inserted element id
*/
function InsertNear($ID, $condition = '', $data = array())
{
$node_info = $this->GetNodeInfo($ID);
if (FALSE === $node_info) {
return FALSE;
}
list($leftId, $rightId, $level, $parent) = $node_info;
$data[$this->table_left] = $rightId + 1;
$data[$this->table_right] = $rightId + 2;
$data[$this->table_level] = $level;
$data[$this->table_parent] = $section_id;
if (!empty($condition)) {
$condition = $this->_PrepareCondition($condition);
}
$sql = 'UPDATE ' . $this->table . ' SET ' . $this->table_left . ' = CASE WHEN ' . $this->table_left . ' > ' . $rightId . ' THEN ' . $this->table_left . ' + 2 ELSE ' . $this->table_left . ' END, ' . $this->table_right . ' = CASE WHEN ' . $this->table_right . '> ' . $rightId . ' THEN ' . $this->table_right . ' + 2 ELSE ' . $this->table_right . ' END, ' . $this->table_parent . ' = ' . $parent . 'WHERE ' . $this->table_right . ' > ' . $rightId;
$sql .= $condition;
//$this->db->beginTransaction();
//try{
$res = $db->query($sql);
$this->db->insert($this->table, $data);
$id = $this->db->lastInsertId();
//$this->db->commit();
return $id;
//}
//catch (Exception $e){
//$this->db->rollBack();
//}
}
/**
* Inserts data to the database
*
* @param string $table
* @param array $data
* @return int
*/
function save($table, $data)
{
//remove csrf
unset($data['csrf']);
$this->db->insert($table, $data);
return $this->db->lastInsertId();
}
/**
* Execute the query and return the result.
*
* First of all prepare the given query. Query must have placeholders. Next we execute the query
* using the $value argument which contain elements equal to placeholder in same order. Result is
* returned based on query type if query is 'select' type then return array fetchAll otherwise
* return number of row effected.
*
* @param string $query the query to be executed
* @param array $values array of values, must be associative if named placeholder
* @return mixed fetchAll for select
* lastInsertId for insert
* @throws PDOException $e propagate the exception for next handler
*/
public function prepareAndExecute($query, $values = array())
{
$fetchMode = PDO::FETCH_ASSOC;
$this->query = $query;
try {
$this->stHandler = $this->dbHandler->prepare($this->query);
$this->stHandler->execute($values);
if (preg_match("/^select/i", $this->query)) {
$this->result = $this->stHandler->fetchAll($fetchMode);
} elseif (preg_match("/^insert/i", $this->query)) {
$this->result = $this->dbHandler->lastInsertId();
}
} catch (PDOException $e) {
throw $e;
}
return $this->result;
}
/**
This method gets the last inserted id
@access public
@throws PDOException object
@return int
*/
public function last_inserted_id()
{
try {
return $this->DBHandler->lastInsertId();
} catch (PDOException $e) {
throw $e;
}
}
/**
* 插入数据库数据
*
* @param string $sSQL
* @param array $aParam
* @return int/false
*/
protected function _insert($sSQL, $aParam)
{
$iRowCount = $this->_exectue($sSQL, $aParam, true, true);
if ($iRowCount != 1) {
return 0;
}
return $this->_oMasterDB->lastInsertId();
}
/**
* retrieves the last inserted ID from the connector
*
* @return int
*/
protected function getInsertedID()
{
$lastInsertId = $this->connector->lastInsertId();
if (is_numeric($lastInsertId)) {
return $lastInsertId;
} else {
return false;
}
}
public function uploadBulkFiles($data)
{
//echo '<pre>';
//print_r($data);die();
// we just remove extra space on username and email
$user_id = $data['user_id'];
$user_name = $data['user_name'];
$filename = $data['filename'];
$filepath = $data['filepath'];
$users = new Users();
$groupid = $users->getGroupid($user_id);
if ($this->databaseConnection()) {
// write new users data into database
$query_insert = $this->db_connection->prepare('INSERT INTO igi_files (filename, filepath, user_id, groupid, active, createdate) VALUES (:filename, :filepath, :userid, :groupid, :active, now())');
$query_insert->bindValue(':filename', $filename);
$query_insert->bindValue(':filepath', 'upload/' . $_SESSION['user_name'] . '_' . $_SESSION['user_id']);
$query_insert->bindValue(':userid', $user_id);
$query_insert->bindValue(':groupid', $groupid);
$query_insert->bindValue(':active', '1');
try {
$query_insert->execute();
} catch (Exception $e) {
echo $e->getMessage();
}
// id of new user
$fileid = $this->db_connection->lastInsertId();
if ($fileid) {
//$path = getcwd()."/upload/".$_SESSION['user_name']."_".$user_id."/".date("d-m-Y")."/".date("h", time());
$path = $filepath . "/" . date("d-m-Y");
try {
if (!file_exists($path)) {
if (mkdir($path, 0777, true)) {
if (copy($filepath . "/" . $filename, $path . "/" . $fileid . "_" . $filename)) {
unlink($filepath . "/" . $filename);
$this->messages[] = "File successfully uploaded";
} else {
$this->errors[] = 'Failed to upload file...';
}
} else {
$this->errors[] = 'Failed to create folders...';
}
} else {
if (copy($filepath . "/" . $filename, $path . "/" . $fileid . "_" . $filename)) {
unlink($filepath . "/" . $filename);
$this->messages[] = "File successfully uploaded";
} else {
$this->errors[] = 'Failed to upload file...';
}
}
} catch (Exception $e) {
echo $e->getMessage();
}
} else {
$this->errors[] = 'Something went wrong, Please try again';
}
}
}
/**
* Insert
*
* @param string $into table name
* @param array $data data
*
* @return int insert id
*/
public function insert($into, $data)
{
$into = str_replace('.', '`.`', $into);
$fields = implode('`, `', array_keys($data));
$values = implode(', ', array_fill(0, count($data), '?'));
$sql = "INSERT INTO `{$into}` (`{$fields}`) VALUES ({$values});";
$this->query($sql, array_values($data));
return $this->dbh->lastInsertId();
}
/**
* Checks and inserts a new account email into the database
*
* @return string a message indicating the action status
*/
public function createAccount()
{
$u = trim($_POST['username']);
$v = sha1(time());
//check if email already in use<div class='alert alert-danger'>
$sql = "SELECT COUNT(Username) AS theCount\n FROM users\n WHERE Username=:email";
if ($stmt = $this->_db->prepare($sql)) {
$stmt->bindParam(":email", $u, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch();
if ($row['theCount'] != 0) {
return "<div class='alert alert-danger'><h2> Error: </h2><p> Sorry, that email is already in use.Please try again. </p></div>";
}
//Try this outside of this if block
if (!$this->sendVerificationEmail($u, $v)) {
return "<div class='alert alert-danger'><h2> Error </h2><p> There was an error sending your verification email." . "Please <a href=“mailto:ammly@gmail.com,example2@gmail.com”> contact us</a> for support." . "We apologize for the inconvenience. </p></div>";
}
$stmt->closeCursor();
}
$sql = "INSERT INTO users(Username, ver_code)\n VALUES(:email, :ver)";
if ($stmt = $this->_db->prepare($sql)) {
$stmt->bindParam(":email", $u, PDO::PARAM_STR);
$stmt->bindParam(":ver", $v, PDO::PARAM_STR);
$stmt->execute();
$stmt->closeCursor();
$userID = $this->_db->lastInsertId();
$url = dechex($userID);
//convert userID to hexadecimal
/*
* If the UserID was successfully
* retrieved, create a default entry.
*/
$sql = "INSERT INTO products (UserID, ProductUrl)\n VALUES ({$userID}, {$url})";
if (!$this->_db->query($sql)) {
return "<div class='alert alert-warning'><h2> Error </h2><p> Your account was created, but creating your first entry failed. </p></div>";
} else {
return "<div class='alert alert-success'><h2> Success! </h2><p> Your account was successfully created with the username <strong>{$u}</strong>." . " Check your email!</div>";
}
} else {
return "<div class='alert alert-danger'><h2> Error </h2><p> Couldn't insert the user information into the database. </p></div>";
}
}
/**
* Shorten an encrypted long URL
*
* @param string $longUrlEncrypted_bin Encrypted URL (non-Base64)
* @param string $firstKey_bin First half of key (non-Base64)
* @param string $secondKey_bin Optional second half of key (non-Base64)
* @return array Data array
*/
public function insert_encrypted_url($longUrlEncrypted_bin, $firstKey_bin, $secondKey_bin = '')
{
if (!(strlen($firstKey_bin) == 8)) {
throw new M29Exception(array('reason' => 'invalid', 'message' => 'firstKey must be 64 bits (8 bytes)', 'locationType' => 'parameter', 'location' => 'firstKey'));
}
if (strlen($longUrlEncrypted_bin) > $this->max_url_length) {
throw new M29Exception(array('reason' => 'invalid', 'message' => 'URLs must be ' . $this->max_url_length . ' characters or less', 'locationType' => 'parameter', 'location' => 'longUrl'));
}
if ($secondKey_bin) {
if (!(strlen($secondKey_bin) == 8)) {
throw new M29Exception(array('reason' => 'invalid', 'message' => 'secondKey must be 64 bits (8 bytes)', 'locationType' => 'parameter', 'location' => 'secondKey'));
}
$key = $firstKey_bin . $secondKey_bin;
$url = $this->decrypt($longUrlEncrypted_bin, $key);
$url = str_replace(chr(0), '', $url);
$valid_protocol = false;
foreach ($this->allowed_protocols as $proto) {
if (strtolower(substr($url, 0, strlen($proto) + 1)) == strtolower("{$proto}:")) {
$valid_protocol = true;
break;
}
}
if (!$valid_protocol) {
throw new M29Exception(array('reason' => 'invalid', 'message' => 'Invalid decryption keys or URL protocol', 'locationType' => 'parameter', 'location' => 'longUrl'));
}
}
$now = time();
try {
$this->db_connect();
$sth = $this->dbh->prepare("insert into urls (created_at, hits, encrypted_url, first_key) values (?, 0, ?, ?)");
$sth->bindValue(1, $now, PDO::PARAM_INT);
$sth->bindValue(2, $longUrlEncrypted_bin, PDO::PARAM_STR);
$sth->bindValue(3, $firstKey_bin, PDO::PARAM_STR);
$sth->execute();
} catch (PDOException $e) {
throw new M29Exception(array('reason' => 'serviceError', 'message' => 'Database error: ' . $e->getMessage()));
}
$id = $this->dbh->lastInsertId();
$idb64 = $this->base64_encode_url($this->int2chars($id));
$base_url = $this->base_url;
if ($secondKey_bin) {
$key2b64 = $this->base64_encode_url($secondKey_bin);
$outurl = "{$base_url}/{$idb64}/{$key2b64}";
$short_url_incomplete = false;
} else {
$outurl = "{$base_url}/{$idb64}";
$short_url_incomplete = true;
}
$out = array('short_url' => $outurl, 'short_url_incomplete' => $short_url_incomplete);
if ($secondKey_bin) {
$out['long_url'] = $url;
}
return $out;
}
public static function lastInsertId($name)
{
try {
if (!self::$instance instanceof \PDO) {
throw new \PDOException(self::$exception['no-instance']);
}
if (!self::$instance->lastInsertId($name)) {
throw new \PDOException(current(self::$instance->errorInfo()) . ' ' . end(self::$instance->errorInfo()));
}
} catch (\PDOException $e) {
self::stackTrace($e);
}
}
/**
* Insert the DomainObject in persistent storage
*
* @param DomainObjectInterface $user
*/
protected function oInsert(DomainObjectInterface $user) : int
{
if (!$user instanceof User) {
throw new \Exception('$user must be instance of User class');
}
try {
$pdos = $this->dBase->prepare('INSERT INTO user (name, description, password, created) VALUES (:name, :description, :password, NOW())');
$pdos->bindParam(':name', $user->name, \PDO::PARAM_STR);
$pdos->bindParam(':description', $user->description, \PDO::PARAM_STR);
$pdos->bindParam(':password', $user->password, \PDO::PARAM_STR);
$pdos->execute();
return (int) $this->dBase->lastInsertId();
} catch (\Exception $e) {
echo 'Mapper exception: ', $e->getMessage(), "\n";
}
}
/**
* Insert/Update User Account
*
* Run queries to insert and update user accounts in the database.
*
* @uses UaserAccount::$this->delete_user_groups
* @param array $data The array
* @param int $user_account_id The data value
* @param bool $update_groups True/False
* @param int $proxy_role_id The data value
* @param bool $role_perm_manage_all_accounts_access The data value
* @return void
*/
public function insert_update_user_account($data, $user_account_id, $update_groups = true, $proxy_role_id = false, $role_perm_manage_all_accounts_access = false)
{
// Update
$statement = $this->db->prepare("\n UPDATE user_account\n SET user_account_email = :user_account_email\n ,first_name = :first_name\n ,last_name = :last_name\n ,modified_date = NOW()\n WHERE user_account_id = :user_account_id");
$statement->bindValue(":user_account_email", $data["user_account_email"], PDO::PARAM_STR);
$statement->bindValue(":first_name", $data["first_name"], PDO::PARAM_STR);
$statement->bindValue(":last_name", $data["last_name"], PDO::PARAM_STR);
$statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);
$statement->execute();
// Update the password if user has entered one.
if (!empty($data["user_account_password"])) {
$statement = $this->db->prepare("\n UPDATE user_account\n SET user_account_password = :user_account_password\n ,modified_date = NOW()\n WHERE user_account_id = :user_account_id");
$statement->bindValue(":user_account_password", $data["user_account_password"], PDO::PARAM_STR);
$statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);
$statement->execute();
}
if ($update_groups && $role_perm_manage_all_accounts_access) {
// Remove all groups/roles because we are going to add them all back in.
$this->delete_user_groups($user_account_id);
if (isset($data["group_data"]) && $data["group_data"]) {
$group_array = array_filter(json_decode($data["group_data"], true));
foreach ($group_array as $single_group_data) {
if (!empty($single_group_data) && !empty($single_group_data["roles"])) {
foreach ($single_group_data["roles"] as $single_role) {
$statement = $this->db->prepare("\n INSERT INTO user_account_groups\n (role_id\n ,user_account_id\n ,group_id)\n VALUES\n (:role_id\n ,:user_account_id\n ,:group_id)");
$statement->bindValue(":role_id", $single_role, PDO::PARAM_INT);
$statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);
$statement->bindValue(":group_id", $single_group_data["group_id"], PDO::PARAM_INT);
$statement->execute();
if ($single_role == $proxy_role_id) {
if (!empty($single_group_data["proxy_users"])) {
$user_account_groups_id = $this->db->lastInsertId();
foreach ($single_group_data["proxy_users"] as $single_proxy_user) {
$statement = $this->db->prepare("\n INSERT INTO user_account_proxy\n (user_account_groups_id\n ,proxy_user_account_id)\n VALUES\n (:user_account_groups_id\n ,:proxy_user_account_id)");
$statement->bindValue(":user_account_groups_id", $user_account_groups_id, PDO::PARAM_INT);
$statement->bindValue(":proxy_user_account_id", $single_proxy_user["user_account_id"], PDO::PARAM_INT);
$statement->execute();
}
}
}
}
}
}
}
}
}
/**
* Put new railcam footage in our storage destination
* @since Version 3.10.0
* @param string $tmpFile
* @param string $origFile
* @return int
*/
public function putFootage($tmpFile = null, $origFile = null)
{
if (count($this->config) === 0) {
throw new Exception("Cannot put railcam footage - storage configuration has not been set");
}
if ($this->type == null) {
throw new Exception("Cannot put railcam footage - storage type has not been set");
}
$storageType = sprintf("\\Railpage\\Railcams\\Storage\\%s", $this->type);
$storageType = new $storageType();
$storageType->setConfig($this->getConfig())->putFile($tmpFile, $origFile);
$fileInfo = $storageType->getFileInfo();
// insert
$data = ["datestored" => $this->timeStamp->format("Y-m-d H:i:s"), "railcam_id" => $this->cameraObject->id, "type" => $fileInfo['type'], "duration" => $fileInfo['duration'], "remote_id" => $fileInfo['remote_id'], "storage_id" => $this->id, "fileinfo" => json_encode($fileInfo)];
$this->db->insert("railcam_footage", $data);
$this->footageId = $this->db->lastInsertId();
}
/**
* Insert/Update Group
*
* Run a queries to insert and update groups in the database.
*
* @param array $data The data array
* @param array $group_id The data value
* @return array|bool The group id
*/
public function insert_update_group($data, $group_id = false)
{
$pdo_params = array($data["name"], $data["abbreviation"], $data["description"], $data["address_1"], $data["address_2"], $data["city"], $data["state"], $data["zip"], $_SESSION[$this->session_key]["user_account_id"], 1);
if ($group_id) {
$pdo_params[] = $group_id;
$statement = $this->db->prepare("\n UPDATE `group`\n SET name = ?\n ,abbreviation = ?\n ,description = ?\n ,address_1 = ?\n ,address_2 = ?\n ,city = ?\n ,state = ?\n ,zip = ?\n ,last_modified_user_account_id = ?\n ,active = ?\n ,last_modified = NOW()\n WHERE group_id = ?");
$statement->execute($pdo_params);
} else {
$pdo_params[] = $_SESSION[$this->session_key]["user_account_id"];
$statement = $this->db->prepare("\n INSERT INTO `group`\n (name\n ,abbreviation\n ,description\n ,address_1\n ,address_2\n ,city\n ,state\n ,zip\n ,last_modified_user_account_id\n ,active\n ,created_by_user_account_id\n ,last_modified\n ,date_created)\n VALUES\n (?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())");
$statement->execute($pdo_params);
$group_id = $this->db->lastInsertId();
}
// Update the groups closure table per Bill Karwin's SQL Antipatterns, Chapter 3.
// The pathlengh column refers to the jumps in between the ancestor and descendant -
// self-reference = 0, first child = 1, and so forth...
// First, check to see if we need to update or insert records.
$group_parent = isset($data["group_parent"]) && $data["group_parent"] ? $data["group_parent"] : false;
$statement = $this->db->prepare("\n SELECT *\n FROM group_closure_table\n WHERE descendant = :group_id");
$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
$statement->execute();
$closure_check = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($closure_check) {
// We need to move everything under it as well.
// First, detatch the node subtree...
$statement = $this->db->prepare("\n DELETE FROM group_closure_table\n WHERE descendant IN (\n SELECT tmpdescendant.d FROM (\n SELECT descendant AS d FROM group_closure_table WHERE ancestor = :group_id\n ) AS tmpdescendant\n )\n AND ancestor IN (\n SELECT tmpancestor.a FROM (\n SELECT ancestor AS a FROM group_closure_table WHERE descendant = :group_id2 AND ancestor != descendant\n ) AS tmpancestor\n )");
$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
$statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
$statement->execute();
// Now, attach the subtree under the updated group.
$statement = $this->db->prepare("\n INSERT INTO group_closure_table\n (ancestor, descendant, pathlength)\n SELECT supertree.ancestor, subtree.descendant, subtree.pathlength+1\n FROM group_closure_table AS supertree\n CROSS JOIN group_closure_table AS subtree\n WHERE supertree.descendant = :new_parent\n AND subtree.ancestor = :group_id");
$statement->bindValue(":new_parent", $group_parent, PDO::PARAM_INT);
$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
$statement->execute();
} else {
// Just insert the leaf node.
$statement = $this->db->prepare("\n INSERT INTO group_closure_table\n (ancestor, descendant, pathlength)\n SELECT gct.ancestor, :group_id, pathlength+1\n FROM group_closure_table AS gct\n WHERE gct.descendant = :parent_group\n UNION ALL\n SELECT :group_id2, :group_id3,0");
$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
$statement->bindValue(":parent_group", $group_parent, PDO::PARAM_INT);
$statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
$statement->bindValue(":group_id3", $group_id, PDO::PARAM_INT);
$statement->execute();
}
return $group_id;
}
/**
* handles the entire add group process. checks all error possibilities, and creates a new user in the database if
* everything is fine
*/
private function addGroup($groupname, $description)
{
// we just remove extra space on username and email
$groupname = trim($groupname);
$description = trim($description);
// check provided data validity
// TODO: check for "return true" case early, so put this first
if (empty($groupname)) {
$this->errors[] = 'Group Namw Empty.';
} elseif (empty($description)) {
$this->errors[] = 'Description is empty.';
// finally if all the above checks are ok
} else {
if ($this->databaseConnection()) {
// check if group already exists
$query_check_groupname = $this->db_connection->prepare('SELECT groupname FROM igi_groups WHERE groupname=:groupname');
$query_check_groupname->bindValue(':groupname', $groupname, PDO::PARAM_STR);
$query_check_groupname->execute();
$result = $query_check_groupname->fetchAll();
// if username or/and email find in the database
// TODO: this is really awful!
if (count($result) > 0) {
for ($i = 0; $i < count($result); $i++) {
$this->errors[] = $result[$i]['groupname'] == $groupname ? 'Group already exists' : '';
}
} else {
// write new users data into database
$query_new_group_insert = $this->db_connection->prepare('INSERT INTO igi_groups (groupname, description, createdate) VALUES(:groupname, :description,now())');
$query_new_group_insert->bindValue(':groupname', $groupname, PDO::PARAM_STR);
$query_new_group_insert->bindValue(':description', $description, PDO::PARAM_STR);
$query_new_group_insert->execute();
// id of group user
$group_id = $this->db_connection->lastInsertId();
if ($group_id) {
$this->messages[] = 'Group Added successfully';
$this->addgroup_successful = true;
} else {
$this->errors[] = 'Failed to add group';
}
}
}
}
}
/**
* 简单回调pdo对象方法
* @param string 函数名
* @param array 参数数组
* @return mixed
*/
public function __call($method, $args)
{
switch ($method) {
case "lastInsertId":
$result = $this->pdo->lastInsertId();
break;
case "rowCount":
case "fetchAll":
case "fetch":
case "fetchRow":
$result = $this->stmt->{$method}();
break;
default:
trigger_error("NOT FOUND Mysql::{$method}");
}
// 删除结果集
unset($this->stmt);
// 返回结果
return $result;
}
/**
* Executes a r/w insert into query.
*
* @param string $table_name Table to insert into.
* @param array $row Assoc array of (column => value) pairs containing the data to insert.
* @return mixed The auto-int key of the inserted record or true if no auto-int exists.
*/
public function insert($table_name, $row)
{
if (!is_array($row) || empty($row)) {
throw new Exception('Invalid row parameter.');
}
// single record insert
if (is_assoc($row)) {
$this->queryRW("insert into {$table_name} (" . implode(',', array_keys($row)) . ")" . " values (:" . implode(', :', array_keys($row)) . ")", $row);
if ($this->getFieldInfo($table_name, 'auto')) {
return $this->dbh_rw->lastInsertId();
}
} else {
// validate batch insert
foreach ($row as $r) {
if (empty($r) || !is_assoc($r)) {
throw new Exception('Invalid row parameter.');
}
if (!isset($fields)) {
$fields = array_keys($r);
} else {
$d = array_diff($fields, array_keys($r));
if (!empty($d)) {
throw new Exception('Batch insert fields mismatch.');
}
}
}
$keys = array_keys($row[0]);
$inserts = '';
$values = array();
foreach ($row as $r) {
$inserts .= ($inserts ? ' UNION ALL ' : '') . 'SELECT ?' . str_repeat(',?', count($keys) - 1);
// being pedantic about ensuring sort order
foreach ($keys as $k) {
$values[] = $r[$k];
}
}
$this->queryRW("insert into {$table_name} (" . implode(',', $keys) . ") {$inserts}", $values);
}
return true;
}
public function importKeyword($keyword)
{
// we just remove extra space on username and email
$keyword = trim($keyword);
// check provided data validity
// TODO: check for "return true" case early, so put this first
if (empty($keyword)) {
return 'Keyword Empty.';
// finally if all the above checks are ok
} else {
if ($this->databaseConnection()) {
// check if group already exists
$query_check_keyword = $this->db_connection->prepare('SELECT keywords FROM igi_keywords WHERE keywords=:keyword');
$query_check_keyword->bindValue(':keyword', $keyword, PDO::PARAM_STR);
$query_check_keyword->execute();
$result = $query_check_keyword->fetchAll();
// if username or/and email find in the database
// TODO: this is really awful!
if (count($result) > 0) {
for ($i = 0; $i < count($result); $i++) {
return 'Keyword already exists';
}
} else {
// write new users data into database
$query_new_group_insert = $this->db_connection->prepare('INSERT INTO igi_keywords (keywords, createdate) VALUES(:keyword,now())');
$query_new_group_insert->bindValue(':keyword', $keyword, PDO::PARAM_STR);
$query_new_group_insert->execute();
// id of group user
$keyid = $this->db_connection->lastInsertId();
if ($keyid) {
return 'Keyword Added successfully';
} else {
return 'Failed to add Keyword';
}
}
}
}
}
