/**
* Hook into the task scheduler. When run, the system checks the cache_occurrences table for records where the data cleaner has marked
* the record as data_cleaner_info "pass", record_status="C", the system then sets the record to verified automatically.
* @param string $last_run_date Date last run, or null if never run
* @param object $db Database object.
*/
function auto_verify_scheduled_task($last_run_date, $db)
{
$autoVerifyNullIdDiff = kohana::config('auto_verify.auto_accept_occurrences_with_null_id_difficulty');
global $processOldData;
$processOldData = kohana::config('auto_verify.process_old_data');
if (empty($autoVerifyNullIdDiff)) {
print_r("Unable to automatically verify occurrences when the auto_accept_occurrences_with_null_id_difficulty entry is empty.<br>");
kohana::log('error', 'Unable to automatically verify occurrences when the auto_accept_occurrences_with_null_id_difficulty configuration entry is empty.');
return false;
}
//Do we need to consider old data (probably as a one-off run) or just newly changed data.
$subQuery = "\n SELECT co.id";
if (!empty($processOldData) && $processOldData === 'true') {
$subQuery .= " \n FROM cache_occurrences co";
} else {
$subQuery .= " \n FROM occdelta od\n JOIN cache_occurrences co on co.id=od.id";
}
$subQuery .= "\n JOIN surveys s on s.id = co.survey_id AND s.auto_accept=true AND s.deleted=false\n LEFT JOIN cache_taxon_searchterms cts on cts.taxon_meaning_id = co.taxon_meaning_id \n WHERE co.data_cleaner_info='pass' AND co.record_status='C' AND co.record_substatus IS NULL\n AND ((" . $autoVerifyNullIdDiff . "=false AND cts.identification_difficulty IS NOT NULL AND cts.identification_difficulty<=s.auto_accept_max_difficulty) \n OR (" . $autoVerifyNullIdDiff . "=true AND (cts.identification_difficulty IS NULL OR cts.identification_difficulty<=s.auto_accept_max_difficulty)))";
$verificationTime = gmdate("Y\\/m\\/d H:i:s");
//Need to update cache_occurrences, as this table has already been built at this point.
$query = "\n INSERT INTO occurrence_comments (comment, generated_by, occurrence_id,record_status,record_substatus,created_by_id,updated_by_id,created_on,updated_on,auto_generated)\n SELECT 'Accepted based on automatic checks', 'system', id,'V','2',1,1,'" . $verificationTime . "','" . $verificationTime . "',true\n FROM occurrences\n WHERE id in\n (" . $subQuery . ");\n \n UPDATE occurrences\n SET \n record_status='V',\n record_substatus='2',\n release_status='R',\n verified_by_id=1,\n verified_on='" . $verificationTime . "',\n record_decision_source='M'\n WHERE id in\n (" . $subQuery . ");\n \n UPDATE cache_occurrences\n SET \n record_status='V',\n record_substatus='2',\n release_status='R',\n verified_on='" . $verificationTime . "',\n verifier='admin, core'\n WHERE id in\n (" . $subQuery . ");";
$results = $db->query($query)->result_array(false);
//Query to return count of records, as I was unable to pursuade the above query to output the number of updated
//records correctly.
$query = "\n SELECT count(id)\n FROM cache_occurrences co\n WHERE co.verified_on='" . $verificationTime . "';";
$results = $db->query($query)->result_array(false);
if (!empty($results[0]['count']) && $results[0]['count'] > 1) {
echo $results[0]['count'] . ' occurrence records have been automatically verified.</br>';
} elseif (!empty($results[0]['count']) && $results[0]['count'] === "1") {
echo '1 occurrence record has been automatically verified.</br>';
} else {
echo 'No occurrence records have been auto-verified.</br>';
}
}
/**
* handles the entire registration process. checks all error possibilities
* and creates a new user in the database if everything is fine
*/
private function registerNewUser()
{
if (empty($_POST['user_name'])) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Empty Username\n </div></div></div>";
} elseif (empty($_POST['user_password_new']) || empty($_POST['user_password_repeat'])) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Empty Password.\n </div></div></div>";
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Passwords don't match - please try again.\n </div></div></div>";
} elseif (strlen($_POST['user_password_new']) < 6) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Your password has to ne at least 6 characters long.\n </div></div></div>";
} elseif (strlen($_POST['user_name']) > 64 || strlen($_POST['user_name']) < 2) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Username cannot be shorter than 2 or longer than 64 characters.\n </div></div></div>";
} elseif (!preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name'])) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Username does not fit the name scheme: only a-Z and numbers are allowed, 2 to 64 characters.\n </div></div></div>";
} elseif (empty($_POST['user_email'])) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Email cannot be empty.\n </div></div></div>";
} elseif (strlen($_POST['user_email']) > 64) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Email cannot be longer than 64 characters.\n </div></div></div>";
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$this->errors[] = "<div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Your email address is not in a valid email format.\n </div></div></div>";
} elseif (!empty($_POST['user_name']) && strlen($_POST['user_name']) <= 64 && strlen($_POST['user_name']) >= 2 && preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name']) && !empty($_POST['user_email']) && strlen($_POST['user_email']) <= 64 && filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL) && !empty($_POST['user_password_new']) && !empty($_POST['user_password_repeat']) && $_POST['user_password_new'] === $_POST['user_password_repeat']) {
// create a database connection
$this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// change character set to utf8 and check it
if (!$this->db_connection->set_charset("utf8")) {
$this->errors[] = $this->db_connection->error;
}
// if no connection errors (= working database connection)
if (!$this->db_connection->connect_errno) {
// escaping, additionally removing everything that could be (html/javascript-) code
$user_name = $this->db_connection->real_escape_string(strip_tags($_POST['user_name'], ENT_QUOTES));
$user_email = $this->db_connection->real_escape_string(strip_tags($_POST['user_email'], ENT_QUOTES));
$user_password = $_POST['user_password_new'];
// crypt the user's password with PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using
// PHP 5.3/5.4, by the password hashing compatibility library
$user_password_hash = password_hash($user_password, PASSWORD_DEFAULT);
// check if user or email address already exists
$sql = "SELECT * FROM users WHERE user_name = '" . $user_name . "' OR user_email = '" . $user_email . "';";
$query_check_user_name = $this->db_connection->query($sql);
if ($query_check_user_name->num_rows == 1) {
$this->errors[] = "<div class='row'>\n\t\t\t\t <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n\t\t\t\t <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n\t\t\t\t\t\t\tSorry, that username / email address is already taken.\n\t\t\t\t\t\t\t</div></div></div>";
} else {
// write new user's data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email)\n VALUES('" . $user_name . "', '" . $user_password_hash . "', '" . $user_email . "');";
$query_new_user_insert = $this->db_connection->query($sql);
// if user has been added successfully
if ($query_new_user_insert) {
$this->messages[] = " <div class='row'>\n <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n Your account has been created successfully. You can now <a href='index.php'>log in</a>.\n</div></div></div>";
} else {
$this->errors[] = "<div class='row'>\n\t\t\t\t <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n\t\t\t\t <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n\t\t\t\t\t\t\tSorry, your registration failed. Please go back and try again.\n\t\t\t\t\t\t\t</div></div></div>";
}
}
} else {
$this->errors[] = "<div class='row'>\n\t\t\t\t <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n\t\t\t\t <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n\t\t\t\t Sorry, no database connection.\n\t\t\t\t</div></div></div>";
}
} else {
$this->errors[] = "<div class='row'>\n\t\t\t\t <div class='col-md-4 col-md-offset-4'><div class='alert alert-warning alert-dismissible' role='alert'>\n\t\t\t\t <button type='button' class='close' data-dismiss='alert'><span aria-hidden='true'>×</span><span class='sr-only'>Close</span></button>\n\t\t\t\t An unknown error occurred.\n\t\t\t\t</div></div></div>\n\t\t\t\t";
}
}
/**
* handles the entire registration process. checks all error possibilities
* and creates a new user in the database if everything is fine
*/
private function registerNewUser()
{
if (empty($_POST['user_name'])) {
$this->errors[] = "Nome de Usuário Vazio";
} elseif (empty($_POST['user_password_new']) || empty($_POST['user_password_repeat'])) {
$this->errors[] = "Senha Vazia";
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$this->errors[] = "Senha e repetição de senha não são as mesmas";
} elseif (strlen($_POST['user_password_new']) < 6) {
$this->errors[] = "A senha deve possuir no mínimo 6 caracteres";
} elseif (strlen($_POST['user_name']) > 64 || strlen($_POST['user_name']) < 2) {
$this->errors[] = "Nome de Usuário não pode ser menor que 2 ou maior que 64 caracteres";
} elseif (!preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name'])) {
$this->errors[] = "Nome de usuário não segue a regra: somente a-Z and numeros são permitidos, 2 a 64 caracteres";
} elseif (empty($_POST['user_email'])) {
$this->errors[] = "Email não poder estar vazio";
} elseif (strlen($_POST['user_email']) > 64) {
$this->errors[] = "Email não pode ser maior que 64 caracteres";
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$this->errors[] = "Formato de Email inválido";
} elseif (!empty($_POST['user_name']) && strlen($_POST['user_name']) <= 64 && strlen($_POST['user_name']) >= 2 && preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name']) && !empty($_POST['user_email']) && strlen($_POST['user_email']) <= 64 && filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL) && !empty($_POST['user_password_new']) && !empty($_POST['user_password_repeat']) && $_POST['user_password_new'] === $_POST['user_password_repeat']) {
// create a database connection
$this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// change character set to utf8 and check it
if (!$this->db_connection->set_charset("utf8")) {
$this->errors[] = $this->db_connection->error;
}
// if no connection errors (= working database connection)
if (!$this->db_connection->connect_errno) {
// escaping, additionally removing everything that could be (html/javascript-) code
$user_name = $this->db_connection->real_escape_string(strip_tags($_POST['user_name'], ENT_QUOTES));
$user_email = $this->db_connection->real_escape_string(strip_tags($_POST['user_email'], ENT_QUOTES));
$user_password = $_POST['user_password_new'];
// crypt the user's password with PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using
// PHP 5.3/5.4, by the password hashing compatibility library
$user_password_hash = password_hash($user_password, PASSWORD_DEFAULT);
// check if user or email address already exists
$sql = "SELECT * FROM users WHERE user_name = '" . $user_name . "' OR user_email = '" . $user_email . "';";
$query_check_user_name = $this->db_connection->query($sql);
if ($query_check_user_name->num_rows == 1) {
$this->errors[] = "Desculpe, esse nome de usuário / e-mail já existe.";
} else {
// write new user's data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email)\n VALUES('" . $user_name . "', '" . $user_password_hash . "', '" . $user_email . "');";
$query_new_user_insert = $this->db_connection->query($sql);
// if user has been added successfully
if ($query_new_user_insert) {
$this->messages[] = "Sua conta foi criada com sucesso!.Você pode fazer login agora.";
} else {
$this->errors[] = "Desculpe, Seu registro falhou. Por favor, retorne e tente novamente.";
}
}
} else {
$this->errors[] = "Desculpe, sem conexão com a base de dados.";
}
} else {
$this->errors[] = "Um erro desconhecido aconteceu.";
}
}
/**
* handles the entire registration process. checks all error possibilities
* and creates a new user in the database if everything is fine
*/
private function registerNewUser()
{
if (empty($_POST['user_name'])) {
$this->errors[] = "Empty Username";
} elseif (empty($_POST['user_password_new']) || empty($_POST['user_password_repeat'])) {
$this->errors[] = "Empty Password";
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$this->errors[] = "Password and password repeat are not the same";
} elseif (strlen($_POST['user_password_new']) < 6) {
$this->errors[] = "Password has a minimum length of 6 characters";
} elseif (strlen($_POST['user_name']) > 64 || strlen($_POST['user_name']) < 2) {
$this->errors[] = "Username cannot be shorter than 2 or longer than 64 characters";
} elseif (!preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name'])) {
$this->errors[] = "Username does not fit the name scheme: only a-Z and numbers are allowed, 2 to 64 characters";
} elseif (empty($_POST['user_email'])) {
$this->errors[] = "Email cannot be empty";
} elseif (strlen($_POST['user_email']) > 64) {
$this->errors[] = "Email cannot be longer than 64 characters";
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$this->errors[] = "Your email address is not in a valid email format";
} elseif (!empty($_POST['user_name']) && strlen($_POST['user_name']) <= 64 && strlen($_POST['user_name']) >= 2 && preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name']) && !empty($_POST['user_email']) && strlen($_POST['user_email']) <= 64 && filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL) && !empty($_POST['user_password_new']) && !empty($_POST['user_password_repeat']) && $_POST['user_password_new'] === $_POST['user_password_repeat']) {
// create a database connection
$this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// change character set to utf8 and check it
if (!$this->db_connection->set_charset("utf8")) {
$this->errors[] = $this->db_connection->error;
}
// if no connection errors (= working database connection)
if (!$this->db_connection->connect_errno) {
// escaping, additionally removing everything that could be (html/javascript-) code
$user_name = $this->db_connection->real_escape_string(strip_tags($_POST['user_name'], ENT_QUOTES));
$user_email = $this->db_connection->real_escape_string(strip_tags($_POST['user_email'], ENT_QUOTES));
$user_password = $_POST['user_password_new'];
// crypt the user's password with PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using
// PHP 5.3/5.4, by the password hashing compatibility library
$user_password_hash = password_hash($user_password, PASSWORD_DEFAULT);
// check if user or email address already exists
$sql = "SELECT * FROM users WHERE user_name = '" . $user_name . "' OR user_email = '" . $user_email . "';";
$query_check_user_name = $this->db_connection->query($sql);
if ($query_check_user_name->num_rows == 1) {
$this->errors[] = "Sorry, that username / email address is already taken.";
} else {
// write new user's data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email)\n VALUES('" . $user_name . "', '" . $user_password_hash . "', '" . $user_email . "');";
$query_new_user_insert = $this->db_connection->query($sql);
// if user has been added successfully
if ($query_new_user_insert) {
$this->messages[] = "Your account has been created successfully. You can now log in.";
} else {
$this->errors[] = "Sorry, your registration failed. Please go back and try again.";
}
}
} else {
$this->errors[] = "Sorry, no database connection.";
}
} else {
$this->errors[] = "An unknown error occurred.";
}
}
protected function renderIndex()
{
$data['schema'] = $this->model->schema;
$data['primary'] = $this->model->primary;
//$this->
$where = [];
$grid['filter'] = isset($this->grid['filter']) ? $this->grid['filter'] : [];
foreach ($grid['filter'] as $key => $value) {
if ($this->input->get($key)) {
$where[$key] = $this->input->get($key);
}
}
$grid['page'] = isset($this->grid['page']) ? $this->grid['page'] : (intval($this->input->get('page')) ? intval($this->input->get('page')) : 1);
$grid['pageSize'] = isset($this->grid['pageSize']) ? $this->grid['pageSize'] : 12;
$grid['total'] = isset($this->grid['total']) ? $this->grid['total'] : $this->model->count($where);
$grid['fields'] = isset($this->grid['fields']) ? $this->grid['fields'] : array_keys($this->model->schema);
$grid['fieldshow'] = isset($this->grid['fieldshow']) ? $this->grid['fieldshow'] : null;
$grid['datasource'] = isset($this->grid['page']) ? $this->grid['datasource'] : $this->model->query($where, $grid['fields'], [$this->model->primary => 'desc'], [$grid['pageSize'] * ($grid['page'] - 1), $grid['pageSize']]);
$grid['pager'] = isset($this->grid['pager']) ? $this->grid['pager'] : $this->helper->backstage->paging($grid['pageSize'], $grid['total'], $grid['page'], $this->helper->backstage->uri(['page' => '']));
$grid['tools'] = isset($this->grid['tools']) ? $this->grid['tools'] : ['add' => $this->helper->form->button(['properties' => ['id' => 'add', 'onclick' => 'edit()'], 'label' => '增加'])];
$grid['operations'] = isset($this->grid['operations']) ? $this->grid['operations'] : [['name' => '操作', 'action' => function ($record) {
return '<a href="javascript:del(' . $record->id . ')">删除</a> | <a href="javascript:update(' . $record->id . ')">修改</a>';
}]];
$data['grid'] = $grid;
$this->output->view('common/index', $data);
}
/**
* Returns all active stays
*
* @return array
*/
function getActive()
{
$res = $this->db->query('SELECT id, indkvartering_key, price FROM kortkursus_x_indkvartering WHERE kursus_id = ' . $this->course->getId() . ' ORDER BY indkvartering_key ASC');
if (PEAR::isError($res)) {
throw new Exception($res->getUserInfo());
}
return $res->fetchAll(MDB2_FETCHMODE_ASSOC);
}
/**
* Query the database.
*
* @param string The query SQL.
* @return resource The query data.
*/
function query($string)
{
++$this->queries;
$query = $this->db->query($string, PDO::FETCH_BOTH);
$this->last_query = $query;
$query->guid = $this->queries;
return $query;
}
/**
* 连接数据库
*
* @access public
* @param string $host 主机地址
* @param string $dbname 数据库名
* @param string $user 连接帐户
* @param string $password 连接密码
* @param string $charset 字符集
* @return object $mysql 连接对象
*/
public function connect($host = 'localhost', $dbname = 'default', $user = '******', $password = '', $charset = 'utf8')
{
$this->mysql_connect = new Driver_Db_Pdo('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password);
$this->mysql_connect->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
$this->mysql_connect->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$this->mysql_connect->query('set names ' . $charset);
return $this;
}
/**
* handles the entire registration process. checks all error possibilities
* and creates a new user in the database if everything is fine
*/
private function registerNewUser()
{
if (empty($_POST['user_name'])) {
$this->errors[] = "Empty Username";
} elseif (empty($_POST['user_password_new']) || empty($_POST['user_password_repeat'])) {
$this->errors[] = "Empty Password";
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$this->errors[] = "Password and password repeat are not the same";
} elseif (strlen($_POST['user_password_new']) < 6) {
$this->errors[] = "Password has a minimum length of 6 characters";
} elseif (strlen($_POST['user_name']) > 64 || strlen($_POST['user_name']) < 2) {
$this->errors[] = "Username cannot be shorter than 2 or longer than 64 characters";
} elseif (!preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name'])) {
$this->errors[] = "Username does not fit the name scheme: only a-Z and numbers are allowed, 2 to 64 characters";
} elseif (empty($_POST['user_email'])) {
$this->errors[] = "Email cannot be empty";
} elseif (strlen($_POST['user_email']) > 64) {
$this->errors[] = "Email cannot be longer than 64 characters";
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$this->errors[] = "Your email address is not in a valid email format";
} elseif (!empty($_POST['user_name']) && strlen($_POST['user_name']) <= 64 && strlen($_POST['user_name']) >= 2 && preg_match('/^[a-z\\d]{2,64}$/i', $_POST['user_name']) && !empty($_POST['user_email']) && strlen($_POST['user_email']) <= 64 && filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL) && !empty($_POST['user_password_new']) && !empty($_POST['user_password_repeat']) && $_POST['user_password_new'] === $_POST['user_password_repeat']) {
// create a database connection
$this->db_connection = new mysqli(localhost, ggl_adam, lugubrious7947, ggl_misc);
// change character set to utf8 and check it
if (!$this->db_connection->set_charset("utf8_general_ci")) {
$this->errors[] = $this->db_connection->error;
}
// if no connection errors (= working database connection)
if (!$this->db_connection->connect_errno) {
// escaping, additionally removing everything that could be (html/javascript-) code
$user_name = $this->db_connection->real_escape_string(strip_tags($_POST['user_name'], ENT_QUOTES));
$user_email = $this->db_connection->real_escape_string(strip_tags($_POST['user_email'], ENT_QUOTES));
$user_password = $_POST['user_password_new'];
$hash = password_hash($user_email, PASSWORD_DEFAULT);
// crypt the user's password with PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using
// PHP 5.3/5.4, by the password hashing compatibility library
$user_password_hash = password_hash($user_password, PASSWORD_DEFAULT);
// check if user or email address already exists
$sql = "SELECT * FROM player WHERE player_username = '******' OR player_email = '" . $user_email . "';";
$query_check_user_name = $this->db_connection->query($sql);
if ($query_check_user_name->num_rows == 1) {
$this->errors[] = "Sorry, that username / email address is already taken.";
} else {
// write new user's data into database
$sql = "INSERT INTO player (player_username, player_pass, player_email, verification_hash)\n VALUES('" . $user_name . "', '" . $user_password_hash . "', '" . $user_email . "', '" . $hash . "');";
$query_new_user_insert = $this->db_connection->query($sql);
include "phpmailer_registration.php";
}
} else {
$this->errors[] = "Sorry, no database connection.";
}
} else {
$this->errors[] = "An unknown error occurred.";
}
//uncommenting breaks it for some reason
//mysqli_close($this->$db_connection);
}
function fnDBUpdateCon($strSql, $hdlConfDB)
{
// fire a SQL on default connection
$arrResult =& $this->hdlDb->query($strSql);
if (DB::isError($arrResult)) {
print $arrResult->getDebugInfo() . "<br>";
die("Failed: " . $arrResult->getMessage() . "\n");
}
}
/**
* 执行SQL语句
*
* SQL语句执行函数
*
* @access public
* @param string $sql SQL语句内容
* @return mixed
*/
public function query($sql)
{
//参数分析
if (!$sql) {
return false;
}
$result = $this->db_link->query($sql);
return $result;
}
/**
* Update an existing handler
*
* @param int $id the handler ID
* @param string $email the email address
* @param string $handler the handler for messages to the email address
*/
function update_handler($id, $email, $handler, $active)
{
$id = (int) $id;
$email = $this->db->db_addslashes($email);
$handler = $this->db->db_addslashes($handler);
$active = (int) $active;
$lastmod = time();
$sql = 'UPDATE phpgw_mail_handler' . " SET target_email = '{$email}', handler = '{$handler}', is_active = {$active}, lastmod = {$lastmod}, lastmod_user = {$GLOBALS['phpgw_info']['user']['account_id']}" . " WHERE handler_id = {$id}";
$this->db->query($sql, __LINE__, __FILE__);
}
private function simpleQuery($sql, $get_insert_id = false)
{
if (!($result = $this->mysqli->query($sql))) {
ErrorHandler::insertErrorInLogs("DB_ERROR[]", 'Не удалось отправить запрос', 'liw\\core\\model\\connect\\ConnectMysqli', '92');
return false;
}
if ($get_insert_id) {
return $this->mysqli->insert_id;
}
return $result;
}
/**
* Creates a connection to the database.
*/
protected function _connect()
{
if ($this->connection) {
return;
}
$this->connection = new PDO($this->_dsn(), $this->cfg->user, $this->cfg->pass, $this->cfg->driverOptions);
foreach ($this->cfg->conQuery as $q) {
$this->connection->query($q);
}
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
/**
* 执行SQL语句并返回结果集
* @param string $sql
* @return mixed
* @throws Exception
*/
public function query($sql)
{
util_log::appLog()->info(__CLASS__ . '::' . __FUNCTION__ . "(): SQL: {$sql}");
$statement = $this->db->query($sql);
if (!$statement) {
$errorInfo = $this->errorInfo();
$errorCode = $this->errorCode();
util_log::monitor()->error(array('MONITOR_KEY' => "database_failed", 'errorCode' => $errorCode, 'errorInfo' => $errorInfo, 'sql' => $sql));
throw new Exception($errorInfo, $errorCode);
}
return $statement;
}
/**
* Loop through option array and delete the option and clear and drop cache tables.
* @param array $opts Array of yarpp's options
* @param object $wpdb Wordpress db global
*/
function clean(array $opts, $wpdb)
{
foreach ($opts as $opt) {
delete_option($opt);
}
/* Truncate, clear and drop yarpp cache */
$wpdb->query('DELETE FROM `' . $wpdb->prefix . 'postmeta` WHERE meta_key LIKE "%yarpp%"');
$wpdb->query('TRUNCATE TABLE `' . $wpdb->prefix . 'yarpp_related_cache`');
wp_cache_flush();
$wpdb->query('DROP TABLE `' . $wpdb->prefix . 'yarpp_related_cache`');
/* Delete users yarpp related data */
$wpdb->query('DELETE FROM `' . $wpdb->prefix . 'usermeta` WHERE meta_key LIKE "%yarpp%"');
}
/**
* sql查询
*
* @param string $sql
*
* @return \System\Database\Result
*/
public function query($sql)
{
$result = $this->db->query($sql);
// var_dump($sql);
if ($this->db->errno) {
exit('Database Error : [' . $sql . '] ' . $this->db->error . ' [Code:' . $this->db->errno . ']');
}
if (is_object($result)) {
return new \System\Database\Result($result);
} else {
return $result;
}
}
/**
* 查询操作的底层接口
*
* @param string $sql
* 要执行查询的SQL语句
* @return Object
*/
public function execute($sql)
{
if (time() - $this->iPingTime > 300) {
$this->close();
$this->connect();
$this->iPingTime = time();
}
$res = $this->oDbh->query($sql);
if ($res === false) {
throw new Exception($sql);
// echo $sql;exit;
}
return $res;
}
/**
* figure out which minion server we are going to use for this mix
* @return string
* @todo actually have some kind of algorithm that selects the best server
*/
private function getNewServer()
{
$minions = $this->db->query("SELECT * FROM `minions` WHERE `load`=0");
if (empty($minions[0]["minionId"])) {
$this->db->simpleQuery("UPDATE `minions` SET `load`=0");
$minion = $this->db->query("SELECT * FROM `minions` ORDER BY RAND() LIMIT 1");
$minion = $minion[0];
} else {
$minion = $minions[array_rand($minions)];
}
$minionId = $minion["minionId"];
$this->db->simpleQuery("UPDATE `minions` SET `load`=1 WHERE `minionId`={$minionId}");
$this->db->update($this->table, array("minionId" => $minionId), array("%d"), array("mixId" => $this->mixId), array("%d"));
return $minion["minionRoot"];
}
function commit()
{
if (self::$_count > 0) {
self::$_count--;
if (self::$_count == 0) {
if (self::$_doCommit) {
self::$_dao->query('COMMIT');
} else {
self::$_dao->query('ROLLBACK');
}
// this transaction is complete, so reset doCommit flag
self::$_doCommit = true;
}
}
}
/**
* Inserts $message to the currently open database. Calls open(),
* if necessary. Also passes the message along to any Log_observer
* instances that are observing this Log.
*
* @param mixed $message String or object containing the message to log.
* @param string $priority The priority of the message. Valid
* values are: PEAR_LOG_EMERG, PEAR_LOG_ALERT,
* PEAR_LOG_CRIT, PEAR_LOG_ERR, PEAR_LOG_WARNING,
* PEAR_LOG_NOTICE, PEAR_LOG_INFO, and PEAR_LOG_DEBUG.
* @return boolean True on success or false on failure.
* @access public
*/
function log($message, $priority = null)
{
/* If a priority hasn't been specified, use the default value. */
if ($priority === null) {
$priority = $this->_priority;
}
/* Abort early if the priority is above the maximum logging level. */
if (!$this->_isMasked($priority)) {
return false;
}
/* If the connection isn't open and can't be opened, return failure. */
if (!$this->_opened && !$this->open()) {
return false;
}
/* Extract the string representation of the message. */
$message = $this->_extractMessage($message);
/* Build the SQL query for this log entry insertion. */
$id = $this->_db->nextId('log_id');
$q = sprintf('insert into %s (id, logtime, ident, priority, message)' . 'values(%d, CURRENT_TIMESTAMP, %s, %d, %s)', $this->_table, $id, $this->_db->quote($this->_ident), $priority, $this->_db->quote($message));
$result = $this->_db->query($q);
if (DB::isError($result)) {
return false;
}
$this->_announce(array('priority' => $priority, 'message' => $message));
return true;
}
/**
* Returns an array of the strings in the selected page
*
* @param string $pageID
* @param string $langID
* @return array
*/
function &getPage($pageID = null, $langID = null)
{
$langID = $this->_getLangID($langID);
if (PEAR::isError($langID)) {
return $langID;
}
$lang_col = $this->_getLangCol($langID);
$table = $this->_getLangTable($langID);
$query = sprintf('SELECT %s, %s FROM %s WHERE %s ', $this->db->quoteIdentifier($this->options['string_id_col'], true), $this->db->quoteIdentifier($lang_col, true), $this->db->quoteIdentifier($table, true), $this->db->quoteIdentifier($this->options['string_page_id_col'], true));
if (is_null($pageID)) {
$query .= 'IS NULL';
} else {
$query .= ' = ' . $this->db->quote($pageID, 'text');
}
++$this->_queries;
$res = $this->db->query($query);
if (PEAR::isError($res)) {
return $res;
}
$strings = array();
while (list($key, $value) = $res->fetchRow(MDB2_FETCHMODE_ORDERED)) {
$strings[$key] = $value;
}
$res->free();
return $strings;
}
/**
* Replacing MODX's getCount(), because it has bug on counting SQL with function.<br>
* Retrieves a count of xPDOObjects by the specified xPDOCriteria.
*
* @param string $className Class of xPDOObject to count instances of.
* @param mixed $criteria Any valid xPDOCriteria object or expression.
* @return integer The number of instances found by the criteria.
* @see xPDO::getCount()
* @link http://forums.modx.com/thread/88619/getcount-fails-if-the-query-has-aggregate-leaving-having-039-s-field-undefined The discussion for this
*/
public function getQueryCount($className, $criteria = null)
{
$count = 0;
if ($query = $this->modx->newQuery($className, $criteria)) {
$expr = '*';
if ($pk = $this->modx->getPK($className)) {
if (!is_array($pk)) {
$pk = array($pk);
}
$expr = $this->modx->getSelectColumns($className, 'alias', '', $pk);
}
$query->prepare();
$sql = $query->toSQL();
$stmt = $this->modx->query("SELECT COUNT({$expr}) FROM ({$sql}) alias");
if ($stmt) {
$tstart = microtime(true);
if ($stmt->execute()) {
$this->modx->queryTime += microtime(true) - $tstart;
$this->modx->executedQueries++;
if ($results = $stmt->fetchAll(PDO::FETCH_COLUMN)) {
$count = reset($results);
$count = intval($count);
}
} else {
$this->modx->queryTime += microtime(true) - $tstart;
$this->modx->executedQueries++;
$this->modx->log(modX::LOG_LEVEL_ERROR, "[" . __CLASS__ . "] Error " . $stmt->errorCode() . " executing statement: \n" . print_r($stmt->errorInfo(), true), '', __METHOD__, __FILE__, __LINE__);
}
}
}
return $count;
}
/**
* Garbage collection class function - handles removal of old sessions (Called by
* PHP internally, based on session.gc_probability and session.gc_divisor values)
*
* @param int $lifetime
* @return bool
*/
function session_gc($lifetime)
{
global $db_prefix;
$query = "DELETE FROM {$db_prefix}sessions WHERE session_modified < " . $this->_db_object->quote(time() - $this->_session_options['session_lifetime'], 'integer');
$this->_db_object->query($query);
return true;
}
/**
* Column Modify
*
* @param string $table Table name
* @param string $field Column definition
* @return bool
*/
public function modify_column($table = '', $field = array())
{
if ($table === '') {
show_error('A table name is required for that operation.');
}
// Work-around for literal column definitions
if (!is_array($field)) {
$field = array($field);
}
foreach (array_keys($field) as $k) {
$this->add_field(array($k => $field[$k]));
}
if (count($this->fields) === 0) {
show_error('Field information is required.');
}
$sqls = $this->_alter_table('CHANGE', $this->db->dbprefix . $table, $this->_process_fields());
$this->_reset();
if ($sqls === FALSE) {
return $this->db->db_debug ? $this->db->display_error('db_unsupported_feature') : FALSE;
}
for ($i = 0, $c = count($sqls); $i < $c; $i++) {
if ($this->db->query($sqls[$i]) === FALSE) {
return FALSE;
}
}
return TRUE;
}
/**
* Get data SQL.
*
* @param string $table
* @access public
* @return string
*/
public function getDataSQL($table, $where = '', $fields = '*', $replace = false)
{
if (empty($fields)) {
$fields = '*';
}
$rows = $this->dbh->query("select {$fields} from `{$table}` {$where}")->fetchAll(PDO::FETCH_ASSOC);
$sql = '';
if (!empty($rows)) {
/* Create key sql for insert. */
$keys = array_keys(current($rows));
$keys = array_map('addslashes', $keys);
$keys = join('`,`', $keys);
$keys = "`" . $keys . "`";
/* Create all value sql. */
$values = array();
foreach ($rows as $row) {
$value = array_values($row);
$value = array_map('addslashes', $value);
$value = join("','", $value);
$value = "'" . $value . "'";
$values[] = "({$value})";
}
if (!$replace) {
$sql .= "INSERT INTO `{$table}`({$keys}) VALUES" . join(',', $values) . ";\n";
}
if ($replace) {
$sql .= "REPLACE INTO `{$table}`({$keys}) VALUES" . join(',', $values) . ";\n";
}
}
return $sql;
}
/**
* 用于更新的SQL语句是否后台执行
* @param $sql
* @return boolean
*/
public function wtriteThroughBehind($sql)
{
//error_log(date('H:i:s').'_'.substr($sql,0,40)."\n",3,TMP_PATH.'/'.date('Y-m-d').'sql_update.log');
//if( USE_WTRITE_THROUGH_BEHIND == FALSE )
//{
//var_dump($this->masterDB);
return $this->masterDB->query($sql);
//}
if (WTRITE_THROUGH_BEHIND_HANDLER == 'File') {
$session_id = session_id();
return file_put_contents(TMP_PATH . '/session_sqls/' . $session_id . '.sql', $sql, FILE_APPEND);
}
if (WTRITE_THROUGH_BEHIND_HANDLER == 'zeromq' && !empty($this->zeroMQ)) {
/*
$this->zeroMQ->doBackground("gearman_sql", serialize(array($this->pid, $this->dbConfig,$sql)) );
if ($this->zeroMQ->returnCode() != GEARMAN_SUCCESS)
{
error_log(date('Y-m-d H:i:s').':'.substr($sql,0,40)."\n",3,TMP_PATH.'/'.date('Y-m-d').'gearman_doBackground_fail.log');
return $this->masterDB->query($sql);
}
*/
return true;
}
return false;
}
/**
* Class Constructor
*
* @param object $obj_db
* @param integer $int_detail_id
* @param str $str_output_format
* @return void
*/
public function __construct($obj_db, $int_detail_id, $str_output_format = 'html')
{
if (!in_array($str_output_format, array('html', 'xml', 'mods', 'dc'))) {
$this->output_format = trim($str_output_format);
} else {
$this->output_format = $str_output_format;
}
$this->obj_db = $obj_db;
$this->detail_id = $int_detail_id;
$_sql = sprintf('SELECT b.*, l.language_name, p.publisher_name, pl.place_name AS \'publish_place\', gmd.gmd_name, fr.frequency FROM biblio AS b
LEFT JOIN mst_gmd AS gmd ON b.gmd_id=gmd.gmd_id
LEFT JOIN mst_language AS l ON b.language_id=l.language_id
LEFT JOIN mst_publisher AS p ON b.publisher_id=p.publisher_id
LEFT JOIN mst_place AS pl ON b.publish_place_id=pl.place_id
LEFT JOIN mst_frequency AS fr ON b.frequency_id=fr.frequency_id
WHERE biblio_id=%d', $int_detail_id);
// for debugging purpose only
// die($_sql);
// query the data to database
$_det_q = $obj_db->query($_sql);
if ($obj_db->error) {
$this->error = $obj_db->error;
} else {
$this->error = false;
$this->record_detail = $_det_q->fetch_assoc();
// free the memory
$_det_q->free_result();
}
}
/**
* Tests the ability to create and drop a table
*
* @since 6.4.0.13
*
* @return void
*/
function test_createdroptable()
{
// Make sure we've already connected. This should never run unless someone calls it manually again.
if (empty($this->tests['connect'])) {
$this->test_connect();
}
// Escape prefix manually since we can't use $wpdb->prefix and $wpdb->prepare adds single quotes
if (empty($this->wpdb->use_mysqli)) {
$prefix = mysql_real_escape_string($this->creds['prefix']);
} else {
$prefix = mysqli_real_escape_string($this->wpdb->dbh, $this->creds['prefix']);
}
// Try to drop test table in event previous attempt failed. Not a part of the test. NOTE: This throws an error to the PHP error log if wpdb logging enabled unless errors are suppressed.
$this->wpdb->suppress_errors(true);
// Hide errors if this test fails since we have logging on by default.
$drop_test_table = 'DROP TABLE ' . $prefix . 'buddy_test';
$this->wpdb->query($drop_test_table);
$this->wpdb->suppress_errors(false);
// Attempt to create the test table
$create_test_table = 'CREATE TABLE ' . $prefix . 'buddy_test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);';
if (FALSE !== $this->wpdb->query($create_test_table)) {
// Drop temp test table we created before we declare success.
if (FALSE !== $this->wpdb->query($drop_test_table)) {
$this->tests['createdroptable'] = true;
} else {
// drop failed.
if (empty($this->wpdb->use_mysqli)) {
$mysql_errno = mysql_errno($this->wpdb->dbh);
} else {
$mysql_errno = mysqli_errno($this->wpdb->dbh);
}
$this->tests['createdroptable_error'] = 'Unable to delete temporary table. ' . $this->wpdb->last_error . ' - ErrorNo: `' . $mysql_errno . '`.';
}
}
}
/**
* Save user membership
*
* @param integer $membership_id membership identifier
* @param array $membership membership data
* @return integer
*/
public function saveUserMembership($membership_id = null, $membership = array())
{
if (empty($membership_id)) {
$update_arr = array();
foreach (array_diff(array_keys($membership), array('id', 'id_user')) as $field) {
$update_arr[] = $field . "`='" . $membership[$field];
}
$sql = $this->DB->insert_string(MEMBERSHIPS_USERS_TABLE, $membership) . ' ON DUPLICATE KEY UPDATE `' . implode("',`", $update_arr) . "'";
$this->DB->query($sql);
$membership_id = $this->DB->insert_id();
} else {
$this->DB->where('id', $membership_id);
$this->DB->update(MEMBERSHIPS_USERS_TABLE, $membership);
}
return $membership_id;
}
