/**
* Construct
*
* @param registry $registry
* @param int $currency_id Current currency_id id
*/
public function __construct($registry, $currency_id)
{
$this->_db = $registry->get('db');
try {
$statement = $this->_db->prepare('SELECT * FROM `currency`');
$statement->execute();
} catch (PDOException $e) {
if ($this->db->inTransaction()) {
$this->db->rollBack();
}
trigger_error($e->getMessage());
}
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $currency) {
$this->_currencies[$currency->currency_id] = array('currency_id' => $currency->currency_id, 'code' => $currency->code, 'rate' => $currency->rate, 'symbol' => $currency->symbol, 'name' => $currency->name);
if ($currency->currency_id == $currency_id) {
$this->_currency_id = $currency->currency_id;
$this->_currency_code = $currency->code;
$this->_currency_rate = $currency->rate;
$this->_currency_name = $currency->name;
$this->_currency_symbol = $currency->symbol;
}
}
}
}
public function getUser($login)
{
$this->connect();
$q = $this->bdCon->prepare("select * from " . $this->config['user_tab'] . " where " . $this->config['user_login'] . "='{$login}'");
$q->execute();
$res = $q->fetch();
return $res;
}
/**
* Get a list of recycle bin contents for the current user
* @param object $userObj current user object
* @param resource $db database connection
* @return array of recycle bin contents
*/
static function get_recyclebin_contents($userObj, $db)
{
$recycle_bin = array();
// Query the Papers tables.
$i = 0;
$stmt = $db->prepare("SELECT property_id AS id, paper_type, paper_title, DATE_FORMAT(deleted,'%Y%m%d%H%i') AS deleted FROM properties WHERE paper_ownerID = ? AND deleted IS NOT NULL");
$stmt->bind_param('i', $userObj->get_user_ID());
$stmt->execute();
$stmt->bind_result($id, $paper_type, $paper_title, $deleted);
while ($stmt->fetch()) {
$recycle_bin[$i]['id'] = $id;
$recycle_bin[$i]['type'] = 'paper';
$recycle_bin[$i]['name'] = $paper_title;
$recycle_bin[$i]['deleted'] = $deleted;
$recycle_bin[$i]['subtype'] = $paper_type;
$i++;
}
$stmt->close();
// Query the Questions tables.
$stmt = $db->prepare("SELECT q_id AS id, q_type, leadin_plain, DATE_FORMAT(deleted,'%Y%m%d%H%i') AS deleted FROM questions WHERE ownerID = ? AND deleted IS NOT NULL");
$stmt->bind_param('i', $userObj->get_user_ID());
$stmt->execute();
$stmt->bind_result($id, $q_type, $leadin_plain, $deleted);
while ($stmt->fetch()) {
$recycle_bin[$i]['id'] = $id;
$recycle_bin[$i]['type'] = 'question';
if ($q_type == 'sct') {
$parts = explode('~', $leadin_plain);
$recycle_bin[$i]['name'] = $parts[0];
} else {
$recycle_bin[$i]['name'] = $leadin_plain;
}
$recycle_bin[$i]['deleted'] = $deleted;
$recycle_bin[$i]['subtype'] = $q_type;
$i++;
}
$stmt->close();
// Query the Folder tables.
$stmt = $db->prepare("SELECT id, name, DATE_FORMAT(deleted,'%Y%m%d%H%i') AS deleted FROM folders WHERE ownerID = ? AND deleted IS NOT NULL");
$stmt->bind_param('i', $userObj->get_user_ID());
$stmt->execute();
$stmt->bind_result($id, $name, $deleted);
while ($stmt->fetch()) {
$recycle_bin[$i]['id'] = $id;
$recycle_bin[$i]['type'] = 'folder';
$recycle_bin[$i]['name'] = str_replace(';', '\\', $name);
$recycle_bin[$i]['deleted'] = $deleted;
$recycle_bin[$i]['subtype'] = '';
$i++;
}
$stmt->close();
return $recycle_bin;
}
/**
* Store cell data in cache for the current cell object if it's "dirty",
* and the 'nullify' the current cell object
*
* @return void
* @throws Exception
*/
private function _storeData()
{
if ($this->_currentCellIsDirty) {
$this->_currentObject->detach();
$query = $this->_DBHandle->prepare("INSERT OR REPLACE INTO kvp_" . $this->_TableName . " VALUES(:id,:data)");
$query->bindValue('id', $this->_currentObjectID, SQLITE3_TEXT);
$query->bindValue('data', serialize($this->_currentObject), SQLITE3_BLOB);
$result = $query->execute();
if ($result === false) {
throw new Exception($this->_DBHandle->lastErrorMsg());
}
$this->_currentCellIsDirty = false;
}
$this->_currentObjectID = $this->_currentObject = null;
}
/**
* {@inheritDoc}
*
* @param string $sql SQL statement to execute
* @param array $params bind_name => value values to interpolate into
* the $sql to be executes
* @return mixed false if query fails, resource or true otherwise
*/
function exec($sql, $params = array())
{
static $last_sql = NULL;
static $statement = NULL;
$is_select = strtoupper(substr(ltrim($sql), 0, 6)) == "SELECT";
if ($last_sql != $sql) {
$statement = NULL;
//garbage collect so don't sqlite lock
}
if ($params) {
if (!$statement) {
$statement = $this->pdo->prepare($sql);
}
$result = $statement->execute($params);
$this->num_affected = $statement->rowCount();
if ($result) {
if ($is_select) {
$result = $statement;
} else {
$result = $this->num_affected;
}
}
} else {
if ($is_select) {
$result = $this->pdo->query($sql);
$this->num_affected = 0;
} else {
$this->num_affected = $this->pdo->exec($sql);
$result = $this->num_affected + 1;
}
}
$last_sql = $sql;
return $result;
}
/**
* Add items on the database
*
* @param Integer $feed_id ToDo desc
* @param Array $items ToDo desc
*
* @return void
*/
public function addItems($feed_id, $items)
{
if (empty($items)) {
return;
}
$items = array_slice($items, 0, intval($this->getConfig('FeedTicker.itemsLimit', 5)));
$dli = intval($this->getConfig('FeedTicker.dateLimit', 60 * 60 * 24 * 7));
$dateLimit = time() - $dli;
$q = $this->db->prepare('INSERT INTO ft_items (
feed_id, updated, title, link, author, read
) VALUES (
:feed_id, :updated, :title, :link, :author, :read
)');
foreach ($items as $i) {
if (!empty($i['updated']) and $i['updated'] < $dateLimit) {
continue;
}
// Check if this item already exists
$sql = 'SELECT COUNT(*) FROM ft_items WHERE feed_id = ' . $this->db->quote($feed_id) . ' AND link = ' . $this->db->quote(trim($i['link']));
$opa = $this->db->query($sql)->fetchColumn();
if ((bool) $this->db->query($sql)->fetchColumn()) {
continue;
}
$q->execute(array(':feed_id' => $feed_id, ':updated' => trim($i['updated']), ':title' => trim($i['title']), ':link' => trim($i['link']), ':author' => trim($i['author']), ':read' => 0));
}
}
/**
* Prepare statement
*
* @param string $stmt The sql statement.
*/
function prepare( $stmt ) {
$this->sql = $stmt;
$this->stmt = $this->conn->prepare( $this->sql );
return $this->stmt;
}
/**
* Fetch value by sql
*
* @param string $sql
* @return mixed
*/
public function fetchColumn($sql)
{
$sth = $this->_conn->prepare($sql);
if ($sth instanceof PDOStatement) {
$sth->execute();
return $sth->fetchColumn();
}
return null;
}
/**
* Creates a new OCI8Statement that uses the given connection handle and SQL statement.
*
* @param resource $dbh The connection handle.
* @param string $statement The SQL statement.
*/
public function __construct($dbh, $statement, SQLite3Connection $conn)
{
list($statement, $paramMap) = self::convertPositionalToNamedPlaceholders($statement);
$this->_sth = $dbh->prepare($statement);
$this->_dbh = $dbh;
$this->_ret = null;
$this->_paramMap = $paramMap;
$this->_conn = $conn;
}
/**
* Deletes a delivered message
*
* @param int $rowid ID of the message to delete
* @param string $channel message's channel
* @param string $nick message's recipient
*
* @return void
*/
protected function deleteMessage($rowid, $channel, $nick)
{
$nick = strtolower($nick);
$q = $this->db->prepare('DELETE FROM remind WHERE rowid = :rowid');
$q->execute(array('rowid' => $rowid));
if ($this->keepListInMemory) {
if (isset($this->msgStorage[$channel][$nick]) && $this->msgStorage[$channel][$nick] == $rowid) {
unset($this->msgStorage[$channel][$nick]);
}
}
}
/**
* Creates a new OCI8Statement that uses the given connection handle and SQL statement.
*
* @param resource $dbh The connection handle.
* @param string $statement The SQL statement.
*/
public function __construct($dbh, $statement, SQLite3Connection $conn)
{
$this->_dbh = $dbh;
list($statement, $paramMap) = self::convertPositionalToNamedPlaceholders($statement);
$this->_sth = $dbh->prepare($statement);
if (!$this->_sth) {
throw SQLite3Exception::fromErrorInfo($this->errorInfo());
}
$this->_ret = null;
$this->_paramMap = $paramMap;
$this->_conn = $conn;
}
/**
* Construct
*
* @param registry $registry
* @param int $language_id Current language id
*/
public function __construct(Registry $registry, $language_id)
{
$this->_db = $registry->get('db');
try {
$statement = $this->_db->prepare('SELECT * FROM `language`');
$statement->execute();
} catch (PDOException $e) {
if ($this->db->inTransaction()) {
$this->db->rollBack();
}
trigger_error($e->getMessage());
}
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $language) {
$this->_languages[$language->language_id] = array('language_id' => $language->language_id, 'language_code' => $language->code, 'language_locale' => $language->locale, 'language_name' => $language->name);
if ($language->language_id == $language_id) {
$this->_language_id = $language->language_id;
$this->_language_code = $language->code;
$this->_language_locale = $language->locale;
$this->_language_name = $language->name;
}
}
}
}
/**
* @method _parseResults()
* @access private
* @throws Exception
* @desc Parsea los resultados obtenidos en cualquier ejecución de consultas SQL.
* @see self::_throwModelException()
*/
private function _parseResults()
{
$this->_resultSet = array();
$statementWords = explode(' ', $this->_sqlQuery);
if (preg_match('/SELECT/', strtoupper($statementWords[0]))) {
$statement = $this->_PDOmySQLConn->prepare($this->_sqlQuery);
$statement->execute();
$this->_numRows = $statement->rowCount();
if ((int) $this->_numRows > 0) {
while ($row = $this->_resource->fetch(PDO::FETCH_ASSOC)) {
array_push($this->_resultSet, $row);
}
}
} else {
$this->_numRows = $this->_resource->rowCount();
}
}
/**
* Destroys the given Token object, by invalidating and removing it from the backend.
* @access public
* @param mixed $token Token object.
* @return boolean True if succesful, false if the Token could not be destroyed.
*/
public function destroyToken($token)
{
if (!$token instanceof Token) {
return false;
}
if (empty($token->username) || empty($token->valid_until) || empty($token->hash)) {
return false;
}
$this->connection->beginTransaction();
$stat = $this->connection->prepare(sprintf("DELETE FROM `%s` WHERE token_hash = :hash;", PowerDnsConfig::DB_TOKEN_TABLE));
if ($stat->execute(array(":hash" => $token->hash)) === false) {
$this->connection->rollback();
return false;
} else {
$this->connection->commit();
return true;
}
}
/**
* Get unread items from the database and delivery then
*
* @param String $channel ToDo desc
*
* @return void
*/
public function checkQueue($channel)
{
$items = $this->getUnreadItems($channel);
if (empty($items)) {
return;
}
foreach ($items as $i) {
$outputFormat = "[%source%] %title% [ %link% ] by %author% at %updated%";
$outputFormat = $this->getConfig('FeedTicker.format', $outputFormat);
$outputTimeFormat = $this->getConfig('FeedTicker.timeFormat', "Y-m-d H:i");
$updated = date($outputTimeFormat, $i['updated']);
$txt = str_replace(array('%source%', '%title%', '%link%', '%author%', '%updated%'), array($i['source'], $i['title'], $i['link'], $i['author'], $updated), $outputFormat);
$this->doPrivmsg($channel, $txt);
// Mark item as read
$q = $this->db->prepare('UPDATE ft_items SET read = 1 WHERE rowid = :rowid');
$q->execute(array('rowid' => $i['rowid']));
}
}
/**
* Initialise this new cell collection
*
* @param PHPExcel_Worksheet $parent The worksheet for this cell collection
*/
public function __construct(PHPExcel_Worksheet $parent)
{
parent::__construct($parent);
if (is_null($this->_DBHandle)) {
$this->_TableName = str_replace('.', '_', $this->_getUniqueID());
$_DBName = ':memory:';
$this->_DBHandle = new SQLite3($_DBName);
if ($this->_DBHandle === false) {
throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
}
if (!$this->_DBHandle->exec('CREATE TABLE kvp_' . $this->_TableName . ' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
}
}
$this->_selectQuery = $this->_DBHandle->prepare("SELECT value FROM kvp_" . $this->_TableName . " WHERE id = :id");
$this->_insertQuery = $this->_DBHandle->prepare("INSERT OR REPLACE INTO kvp_" . $this->_TableName . " VALUES(:id,:data)");
$this->_updateQuery = $this->_DBHandle->prepare("UPDATE kvp_" . $this->_TableName . " SET id=:toId WHERE id=:fromId");
$this->_deleteQuery = $this->_DBHandle->prepare("DELETE FROM kvp_" . $this->_TableName . " WHERE id = :id");
}
/**
* Initializes prepared statements used by the plugin.
*
* @return void
*/
protected function initializePreparedStatements()
{
$this->fetchKarma = $this->db->prepare('SELECT karma
FROM karmas
WHERE term = :term
LIMIT 1');
$this->insertKarma = $this->db->prepare('INSERT INTO karmas (term, karma)
VALUES (:term, :karma)');
$this->updateKarma = $this->db->prepare('UPDATE karmas
SET karma = :karma
WHERE term = :term');
$this->fetchFixedKarma = $this->db->prepare('SELECT karma
FROM fixed_karmas
WHERE term = :term
LIMIT 1');
$this->fetchPositiveAnswer = $this->db->prepare('SELECT answer
FROM positive_answers
ORDER BY RANDOM()
LIMIT 1');
$this->fetchNegativeAnswer = $this->db->prepare('SELECT answer
FROM negative_answers
ORDER BY RANDOM()
LIMIT 1');
}
/**
* Saves the CAPTCHA data to the configured database.
*/
protected function saveCodeToDatabase()
{
$success = false;
$this->openDatabase();
if ($this->use_database && $this->pdo_conn) {
$id = $this->getCaptchaId(false);
$ip = $_SERVER['REMOTE_ADDR'];
if (empty($id)) {
$id = $ip;
}
$time = time();
$code = $this->code;
$code_disp = $this->code_display;
// This is somewhat expensive in PDO Sqlite3 (when there is something to delete)
$this->clearCodeFromDatabase();
$query = "INSERT INTO {$this->database_table} (" . "id, code, code_display, namespace, created) " . "VALUES(?, ?, ?, ?, ?)";
$stmt = $this->pdo_conn->prepare($query);
$success = $stmt->execute(array($id, $code, $code_disp, $this->namespace, $time));
if (!$success) {
$err = $stmt->errorInfo();
$error = "Failed to insert code into database. {$err[1]}: {$err[2]}.";
if ($this->database_driver == self::SI_DRIVER_SQLITE3) {
$err14 = $err[1] == 14;
if ($err14) {
$error .= sprintf(" Ensure database directory and file are writeable by user '%s' (%d).", get_current_user(), getmyuid());
}
}
trigger_error($error, E_USER_WARNING);
}
}
return $success !== false;
}
/**
* Get a code from the sqlite database for ip address/captchaId.
*
* @return string|array Empty string if no code was found or has expired,
* otherwise returns the stored captcha code. If a captchaId is set, this
* returns an array with indices "code" and "code_disp"
*/
protected function getCodeFromDatabase()
{
$code = '';
if ($this->use_database == true && $this->pdo_conn) {
if (Securimage::$_captchaId !== null) {
$query = "SELECT * FROM {$this->database_table} WHERE id = ?";
$stmt = $this->pdo_conn->prepare($query);
$result = $stmt->execute(array(Securimage::$_captchaId));
} else {
$ip = $_SERVER['REMOTE_ADDR'];
$ns = $this->namespace;
// ip is stored in id column when no captchaId
$query = "SELECT * FROM {$this->database_table} WHERE id = ? AND namespace = ?";
$stmt = $this->pdo_conn->prepare($query);
$result = $stmt->execute(array($ip, $ns));
}
if (!$result) {
$err = $this->pdo_conn->errorInfo();
trigger_error("Failed to select code from database. {$err[0]}: {$err[1]}", E_USER_WARNING);
} else {
if (($row = $stmt->fetch()) !== false) {
if (false == $this->isCodeExpired($row['created'])) {
if (Securimage::$_captchaId !== null) {
// return an array when using captchaId
$code = array('code' => $row['code'], 'code_disp' => $row['code_display']);
} else {
$code = $row['code'];
}
}
}
}
}
return $code;
}
/**
* Write the data stored in the object to the database. Not abstracted as this
* is the only other database operation in the system.
*/
public function save()
{
$sql = 'INSERT INTO users SET email = ?, password = ?, name = ?, dob = ?';
$sth = $this->db->prepare($sql);
$sth->execute([$this->email, password_hash($this->password, PASSWORD_BCRYPT), $this->name, $this->dob]);
}
/**
* Execute an SQL query on the database
*
* @param resource $db Database handler
* @param array $bindings Array of PDO binding values from bind() to be
* used for safely escaping strings. Note that this can be given as the
* SQL query string if no bindings are required.
* @param string $sql SQL query to execute.
* @return array Result from the query (all rows)
*/
static function sql_exec($db, $bindings, $sql = null)
{
// Argument shifting
if ($sql === null) {
$sql = $bindings;
}
$stmt = $db->prepare($sql);
//echo $sql;
// Bind parameters
if (is_array($bindings)) {
for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
$binding = $bindings[$i];
$stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
}
}
// Execute
try {
$stmt->execute();
} catch (PDOException $e) {
self::fatal("An SQL error occurred: " . $e->getMessage());
}
// Return all
return $stmt->fetchAll();
}
/**
* Create a non-executed prepared statement.
*
* @param string $prepareString
* @return OCI8Statement
*/
public function prepare($prepareString)
{
return $this->dbh->prepare($prepareString);
}
/**
* Deletes a record
* TAG_TODO add call userland API
*
* @param array $input parameters
* @param array $return metadata parameters
* @param resource $DB database connection to use
* @return string 'OK' or some error text
*/
public static function recordDelete($input, &$return_metadata, $DB)
{
$table_name = $DB->lb . CMS::$R['db_api_fields'][CMS::$R['db_api_reports'][$input['report_id']]['id_field']]['table'] . $DB->rb;
$id_field_name = $DB->lb . CMS::$R['db_api_fields'][CMS::$R['db_api_reports'][$input['report_id']]['id_field']]['table_field'] . $DB->rb;
$sql = "delete from {$table_name} where {$id_field_name} = :id";
$prepared = $DB->prepare($sql);
$prepared->execute(array(':id' => $input['row_id']));
$return_metadata = array('type' => 'command', 'command' => 'reload');
return 'OK';
}
/**
*
* @param string $tabla
* @param array $data
* @param resource $link_identifier
* @return array
*/
function insertPDO($tabla, $data, $link_identifier = null)
{
$names = $values = array();
$tabla = (string) $tabla;
$data = (array) $data;
$return = array('success' => false, 'lastInsertId' => 0);
if (!empty($tabla) && !empty($data)) {
foreach ($data as $key => $value) {
$names[] = (string) $key;
$values[] = is_int($value) ? $value : "'{$value}'";
}
$namesString = implode(', ', $names);
$valuesString = implode(', ', $values);
$sql = "INSERT INTO {$tabla} ( {$namesString} ) VALUES( {$valuesString} )";
$insert = $link_identifier->prepare($sql);
$insert->execute();
$return['success'] = $insert;
$return['lastInsertId'] = $link_identifier->lastInsertId();
}
return $return;
}
/**
* Get a list of all the fields for the given table.
* The results are not totally equal for all databases (for example collations are handled very differently, PostgreSQL disregards zerofill,...)
* but as close as possible and necessary.
* @param string $able Table of which to show the fields.
* Returns a map of field name => field spec.
*/
public function fieldList($table)
{
switch (self::getDatabaseServer()) {
case "mysql":
foreach ($this->dbConn->query("SHOW FULL FIELDS IN {$table}") as $field) {
$fieldSpec = $field['Type'];
if (!$field['Null'] || $field['Null'] == 'NO') {
$fieldSpec .= ' not null';
}
if ($field['Collation'] && $field['Collation'] != 'NULL') {
$values = $this->dbConn->prepare("SHOW COLLATION LIKE '{$field['Collation']}'");
$values->execute();
$collInfo = $values->fetchColumn();
$fieldSpec .= " character set {$collInfo['Charset']} collate {$field['Collation']}";
}
if ($field['Default'] || $field['Default'] === "0") {
$fieldSpec .= " default '" . addslashes($field['Default']) . "'";
}
if ($field['Extra']) {
$fieldSpec .= " {$field['Extra']}";
}
$fieldList[$field['Field']] = $fieldSpec;
}
break;
case "pgsql":
foreach ($this->dbConn->query("\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tcolumn_name AS cname,\n\t\t\t\t\t\tcolumn_default AS cdefault,\n\t\t\t\t\t\tis_nullable AS nullable,\n\t\t\t\t\t\tdata_type AS dtype,\n\t\t\t\t\t\tcharacter_maximum_length AS maxlength\n\t\t\t\t\tFROM\n\t\t\t\t\t\tinformation_schema.columns\n\t\t\t\t\tWHERE\n\t\t\t\t\t\ttable_name = {$table}\n\t\t\t\t") as $field) {
if ($field['maxlength']) {
$fieldSpec = $field['dtype'] . "(" . $field['maxlength'] . ")";
} else {
$fieldSpec = $field['dtype'];
}
if ($field['nullable'] == 'NO') {
$fieldSpec .= ' not null';
}
if ($field['cdefault'] || $field['cdefault'] === "0") {
$fieldSpec .= " default '" . addslashes($field['cdefault']) . "'";
}
$fieldList[$field['cname']] = $fieldSpec;
}
break;
case "mssql":
foreach ($this->dbConn->query("\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tCOLUMN_NAME AS 'cname',\n\t\t\t\t\t\tCOLUMN_DEFAULT AS 'cdefault',\n\t\t\t\t\t\tIS_NULLABLE AS 'nullable',\n\t\t\t\t\t\tDATA_TYPE AS 'dtype',\n\t\t\t\t\t\tCOLLATION_NAME AS 'collname',\n\t\t\t\t\t\tCHARACTER_SET_NAME AS 'cset',\n\t\t\t\t\t\tCHARACTER_MAXIMUM_LENGTH AS 'maxlength'\n\t\t\t\t\tFROM\n\t\t\t\t\t\tinformation_schema.columns\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tTABLE_NAME = '{$table}'\n\t\t\t\t") as $field) {
if ($field['maxlength']) {
$fieldSpec = $field['dtype'] . "(" . $field['maxlength'] . ")";
} else {
$fieldSpec = $field['dtype'];
}
if ($field['nullable'] == 'NO') {
$fieldSpec .= ' not null';
}
if ($field['collname'] && $field['collname'] != 'NULL') {
$fieldSpec .= " character set {$field['cset']} collate {$field['collname']}";
}
if ($field['cdefault'] || $field['cdefault'] === "0") {
$fieldSpec .= " default '" . addslashes($field['cdefault']) . "'";
}
$fieldList[$field['cname']] = $fieldSpec;
}
break;
default:
$this->databaseError("This database server is not available");
}
return $fieldList;
}
/**
* Tell the database to execute the query
*/
function Execute()
{
global $c;
if (!isset($this->connection)) {
_awl_connect_configured_database();
$this->connection = $GLOBALS['_awl_dbconn'];
}
if (isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters) {
$this->bound_querystring = $this->querystring;
if (isset($this->bound_parameters)) {
$this->bound_querystring = $this->connection->ReplaceParameters($this->querystring, $this->bound_parameters);
// printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
// printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
// print_r( $this->bound_parameters );
}
$t1 = microtime(true);
// get start time
$this->sth = $this->connection->query($this->bound_querystring);
} else {
$t1 = microtime(true);
// get start time
$this->sth = $this->connection->prepare($this->querystring);
if ($this->sth) {
$this->sth->execute($this->bound_parameters);
}
// printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
// print_r( $this->bound_parameters );
}
$this->bound_querystring = null;
if (!$this->sth) {
$this->error_info = $this->connection->errorInfo();
return false;
}
$this->rows = $this->sth->rowCount();
$i_took = microtime(true) - $t1;
$c->total_query_time += $i_took;
$this->execution_time = sprintf("%2.06lf", $i_took);
$this->error_info = null;
return true;
}
/**
* Construct
*
* @param $db
* @param $request
* @param $response
* @param string $http
* @param string $https
*/
public function __construct($db, $request, $response, $http, $https)
{
$this->_http = $http;
$this->_https = $https;
$this->_db = $db;
$this->_request = $request;
$this->_response = $response;
// Create account rewrite rules
$this->_addRewrite('account/account', 'profile');
$this->_addRewrite('account/account/approve', 'approve');
$this->_addRewrite('account/account/create', 'signup');
$this->_addRewrite('account/account/update', 'account-settings');
$this->_addRewrite('account/account/login', 'signin');
$this->_addRewrite('account/account/logout', 'logout');
$this->_addRewrite('account/account/forgot', 'forgot-password');
$this->_addRewrite('account/product', 'product-list');
$this->_addRewrite('account/product/create', 'add-product');
$this->_addRewrite('account/product/update', 'update-product');
$this->_addRewrite('account/product/delete', 'delete-product');
// Create catalog rewrite rules
$this->_addRewrite('catalog/category', '');
$this->_addRewrite('catalog/product', '');
$this->_addRewrite('catalog/search', 'search');
$this->_addRewrite('catalog/product/download', 'download');
$this->_addRewrite('catalog/product/demo', 'demo');
// Create common rewrite rules
$this->_addRewrite('common/home', '');
$this->_addRewrite('common/contact', 'contact');
$this->_addRewrite('common/information/about', 'about');
$this->_addRewrite('common/information/terms', 'terms');
$this->_addRewrite('common/information/licenses', 'licenses');
$this->_addRewrite('common/information/faq', 'faq');
// Create error rewrite rules
$this->_addRewrite('error/not_found', '404');
// Create categories rewrite rules
$statement = $this->_db->query('SELECT
`c`.`category_id`,
CONCAT_WS("/", (SELECT `pc`.`alias` FROM `category` AS `pc` WHERE `pc`.`category_id` = `c`.`parent_category_id`), `c`.`alias`) AS `sef`
FROM `category` AS `c`');
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $category) {
// Add rewrite rule
$this->_addRewrite('category_id=' . $category->category_id, $category->sef);
}
}
// Create products rewrite rules
$statement = $this->_db->query('SELECT
`p`.`product_id`,
CONCAT_WS("/",
(SELECT `ppc`.`alias` FROM `category` AS `ppc` WHERE `ppc`.`category_id` = `c`.`parent_category_id`),
(SELECT `pc`.`alias` FROM `category` AS `pc` WHERE `pc`.`category_id` = `p`.`category_id`),
`p`.`alias`) AS `sef`
FROM `product` AS `p` JOIN `category` AS `c` ON (`c`.`category_id` = `p`.`category_id`)');
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $product) {
// Add rewrite rule
$this->_addRewrite('product_id=' . $product->product_id, $product->sef);
}
}
// Rewrite begin
if (isset($this->_request->get['_route_'])) {
$rewrite = array_flip($this->_rewrite);
// If has rewrite rule
if (isset($rewrite[$this->_request->get['_route_']])) {
// Category
if (false !== strpos($rewrite[$this->_request->get['_route_']], 'category_id')) {
$argument = explode('=', $rewrite[$this->_request->get['_route_']]);
$this->_request->get['route'] = 'catalog/category';
$this->_request->get[$argument[0]] = $argument[1];
// Product
} else {
if (false !== strpos($rewrite[$this->_request->get['_route_']], 'product_id')) {
$argument = explode('=', $rewrite[$this->_request->get['_route_']]);
$this->_request->get['route'] = 'catalog/product';
$this->_request->get[$argument[0]] = $argument[1];
// Other
} else {
$this->_request->get['route'] = $rewrite[$this->_request->get['_route_']];
}
}
// If rewrite rule not found
} else {
// Try to 301 redirect if request URI exists in database history
$statement = $this->_db->prepare('SELECT `redirect_id`, `uri_to` FROM `redirect` WHERE `code` = 301 AND `uri_from` LIKE ?');
$statement->execute(array($this->_request->get['_route_']));
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $redirect) {
// Find available URI
if (isset($rewrite[$redirect->uri_to])) {
// Register hit
$statement = $this->_db->prepare('UPDATE `redirect` SET `requested` = `requested` + 1 WHERE `redirect_id` = ? LIMIT 1');
$statement->execute(array($redirect->redirect_id));
// Redirect
$this->_response->redirect(($this->_request->getHttps() ? $this->_https : $this->_http) . $redirect->uri_to, 301);
}
}
}
$this->_request->get['route'] = 'error/not_found';
}
// If raw request
} else {
if (isset($this->_request->get['route'])) {
// Allow AJAX raw requests
if (!$this->_request->isAjax()) {
// Check if rewrite rule is exists
$raw = ($this->_request->getHttps() ? $this->_https : $this->_http) . 'index.php?' . urldecode(http_build_query($this->_request->get));
$sef = $this->link($this->_request->get['route'], urldecode(http_build_query(array_diff_key($this->_request->get, array_flip(array('route'))))), $this->_request->getHttps() ? 'SSL' : false);
if (rawurldecode($raw) != rawurldecode($sef)) {
$this->_response->redirect($sef, 303);
}
}
}
}
}
/**
* Construct
*
* @param $db
* @param $request
* @param $response
* @param string $base
*/
public function __construct($db, $request, $response, $base)
{
$this->_base = $base;
$this->_db = $db;
$this->_request = $request;
$this->_response = $response;
// Create account rewrite rules
$this->_addRewrite('account/account', 'profile');
$this->_addRewrite('account/account/approve', 'approve');
$this->_addRewrite('account/account/create', 'signup');
$this->_addRewrite('account/account/update', 'settings');
$this->_addRewrite('account/account/login', 'signin');
$this->_addRewrite('account/account/logout', 'logout');
$this->_addRewrite('account/account/forgot', 'forgot');
$this->_addRewrite('account/account/reset', 'reset');
$this->_addRewrite('account/account/affiliate', 'affiliate');
$this->_addRewrite('account/account/verification', 'verification');
$this->_addRewrite('account/account/subscription', 'subscriptions');
$this->_addRewrite('account/product', 'product/list');
$this->_addRewrite('account/product/create', 'product/create');
$this->_addRewrite('account/product/update', 'product/update');
$this->_addRewrite('account/product/delete', 'product/delete');
$this->_addRewrite('account/notification', 'notifications');
$this->_addRewrite('account/notification/read', 'notification/read');
// Create catalog rewrite rules
$this->_addRewrite('catalog/category', '');
$this->_addRewrite('catalog/product', '');
$this->_addRewrite('catalog/search', 'search');
$this->_addRewrite('catalog/product/download', 'product/download');
$this->_addRewrite('catalog/product/demo', 'product/demo');
// Create common rewrite rules
$this->_addRewrite('common/home', '');
$this->_addRewrite('common/contact', 'contact');
$this->_addRewrite('common/information/about', 'about');
$this->_addRewrite('common/information/terms', 'terms');
$this->_addRewrite('common/information/licenses', 'licenses');
$this->_addRewrite('common/information/faq', 'faq');
$this->_addRewrite('common/information/team', 'team');
$this->_addRewrite('common/information/bitcoin', 'bitcoin');
$this->_addRewrite('common/information/promo', 'promo');
$this->_addRewrite('common/image/qr', 'qr');
// Create error rewrite rules
$this->_addRewrite('error/not_found', '404');
// Ajax rewrite rules begin
// Account
$this->_addRewrite('account/account/uploadAvatar', 'ajax/upload/avatar');
$this->_addRewrite('account/product/uploadPackage', 'ajax/upload/package');
$this->_addRewrite('account/product/uploadImage', 'ajax/upload/image');
$this->_addRewrite('account/product/uploadAudio', 'ajax/upload/audio');
$this->_addRewrite('account/product/uploadVideo', 'ajax/upload/video');
$this->_addRewrite('account/product/quota', 'ajax/quota');
// Product
$this->_addRewrite('catalog/product/report', 'ajax/report');
$this->_addRewrite('catalog/product/favorite', 'ajax/favorite');
$this->_addRewrite('catalog/product/review', 'ajax/review');
$this->_addRewrite('catalog/product/reviews', 'ajax/reviews');
// Order
$this->_addRewrite('order/bitcoin/create', 'ajax/order/bitcoin/create');
// Other
$this->_addRewrite('account/account/captcha', 'account/captcha');
$this->_addRewrite('common/contact/captcha', 'contact/captcha');
// Create categories rewrite rules
$statement = $this->_db->query('SELECT
`c`.`category_id`,
CONCAT_WS("/", (SELECT `pc`.`alias` FROM `category` AS `pc` WHERE `pc`.`category_id` = `c`.`parent_category_id`), `c`.`alias`) AS `sef`
FROM `category` AS `c`');
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $category) {
// Add rewrite rule
$this->_addRewrite('category_id=' . $category->category_id, $category->sef);
}
}
// Create products rewrite rules
$statement = $this->_db->query('SELECT
`p`.`product_id`,
CONCAT_WS("/",
(SELECT `ppc`.`alias` FROM `category` AS `ppc` WHERE `ppc`.`category_id` = `c`.`parent_category_id`),
(SELECT `pc`.`alias` FROM `category` AS `pc` WHERE `pc`.`category_id` = `p`.`category_id`),
`p`.`alias`) AS `sef`
FROM `product` AS `p` JOIN `category` AS `c` ON (`c`.`category_id` = `p`.`category_id`)');
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $product) {
// Add rewrite rule
$this->_addRewrite('product_id=' . $product->product_id, $product->sef);
}
}
// Rewrite begin
if (isset($this->_request->get['_route_'])) {
$rewrite = array_flip($this->_rewrite);
// Prepare multi-language requests
$statement = $this->_db->query('SELECT `language_id`, `code` FROM `language`');
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $language) {
if (isset($this->_request->get['_route_']) && preg_match(sprintf('/^%s\\//ui', $language->code), $this->_request->get['_route_'])) {
// Set current language
$this->_language = $language->code;
// Set global language request
$this->_request->get['language_id'] = $language->language_id;
// Clear request
$this->_request->get['_route_'] = str_replace($language->code . '/', false, $this->_request->get['_route_']);
break;
}
}
}
// If request exists in rewrite rule
if (isset($rewrite[$this->_request->get['_route_']])) {
// Category
if (false !== strpos($rewrite[$this->_request->get['_route_']], 'category_id')) {
$argument = explode('=', $rewrite[$this->_request->get['_route_']]);
$this->_request->get['route'] = 'catalog/category';
$this->_request->get[$argument[0]] = $argument[1];
// Product
} else {
if (false !== strpos($rewrite[$this->_request->get['_route_']], 'product_id')) {
$argument = explode('=', $rewrite[$this->_request->get['_route_']]);
$this->_request->get['route'] = 'catalog/product';
$this->_request->get[$argument[0]] = $argument[1];
// Other
} else {
$this->_request->get['route'] = $rewrite[$this->_request->get['_route_']];
}
}
// If rewrite rule not found
} else {
// Try to 301 redirect if request URI exists in database history
$statement = $this->_db->prepare('SELECT `redirect_id`, `uri_to` FROM `redirect` WHERE `code` = 301 AND `uri_from` LIKE ?');
$statement->execute(array($this->_request->get['_route_']));
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $redirect) {
// Find available URI
if (isset($rewrite[$redirect->uri_to])) {
// Register hit
$statement = $this->_db->prepare('UPDATE `redirect` SET `requested` = `requested` + 1 WHERE `redirect_id` = ? LIMIT 1');
$statement->execute(array($redirect->redirect_id));
// Redirect
$this->_response->redirect($this->_base . $redirect->uri_to, 301);
}
}
}
$this->_request->get['route'] = 'error/not_found';
}
// If raw request
} else {
if (isset($this->_request->get['route'])) {
// Allow AJAX raw requests
if (!$this->_request->isAjax()) {
// Check if rewrite rule is exists
$raw = $this->_base . 'index.php?' . http_build_query($this->_request->get);
$sef = $this->link($this->_request->get['route'], http_build_query(array_diff_key($this->_request->get, array_flip(array('route')))));
if (rawurldecode($raw) != rawurldecode($sef)) {
$this->_response->redirect($sef, 303);
}
}
}
}
}
/**
* remove a question from rogo
* Normal Questions - sets the deleted field we don't actuality delete the row form the questions table
* Random Questions - deletes the rows in optionsto ensure random questions cannot use the deleted question
* @param $q_id the id of the question or property_id
* @param resource $db the database connection.
* @return void
*/
static function delete_question($q_id, $db)
{
$delete = $db->prepare("UPDATE questions SET deleted = NOW() WHERE q_id = ?");
$delete->bind_param('i', $q_id);
$delete->execute();
$delete->close();
$select_random = $db->prepare("SELECT o.o_id, o.option_text FROM questions q, options o WHERE q.q_id = o.o_id AND q_type = 'random' AND o.option_text = ?");
$select_random->bind_param('s', $q_id);
$select_random->execute();
$select_random->store_result();
$select_random->bind_result($o_id, $option_text);
while ($select_random->fetch()) {
$delete_random = $db->prepare("DELETE FROM options where o_id = ? AND option_text = ?");
$delete_random->bind_param('is', $o_id, $option_text);
$delete_random->execute();
$delete_random->close();
}
$select_random->close();
}
/**
* Garbage Collector
* @param int life time (sec.)
* @return bool
* @see session.gc_divisor 100
* @see session.gc_maxlifetime 1440
* @see session.gc_probability 1
* @usage execution rate 1/100
* (session.gc_probability/session.gc_divisor)
*/
public function gc($max)
{
$stmt = $this->dbh->prepare("DELETE FROM {$this->dbTable} WHERE timestamp < :limit");
$ret = $stmt->execute(array(':limit' => time() - intval($max)));
return $ret;
}
