public static function search_query($keyword = '', $category = '')
 {
     if (isset($_GET)) {
         $keyword_clean = mysqli_real_escape_string(db_connect(), $keyword);
         $category_clean = mysqli_real_escape_string(db_connect(), $category);
         if ($category_clean === 'post') {
             $search_results = db_select("SELECT * FROM post WHERE title LIKE '%" . $keyword_clean . "%' OR body LIKE '%" . $keyword_clean . "%'");
         } elseif ($category_clean === 'category') {
             $search_results = db_select("SELECT * FROM category WHERE title LIKE '%" . $keyword_clean . "%' OR description LIKE '%" . $keyword_clean . "%'");
         } elseif ($category_clean === 'page') {
             $search_results = db_select("SELECT * FROM page WHERE title LIKE '%" . $keyword_clean . "%' OR body LIKE '%" . $keyword_clean . "%'");
         } elseif ($category_clean === 'upload') {
             $search_results = db_select("SELECT * FROM upload WHERE filename LIKE '%" . $keyword_clean . "%' OR filetype LIKE '%" . $keyword_clean . "%' OR filepath LIKE '%" . $keyword_clean . "%'");
         } elseif ($category_clean === 'user') {
             $search_results = db_select("SELECT * FROM user WHERE username LIKE '%" . $keyword_clean . "%'");
         } else {
             // ALL
             $search = new Search();
             $search_results = $search->searchAllDB($keyword_clean);
             //print_r($search_results);
         }
     } else {
         $search_results = '';
         $flash = new Flash();
         $flash->flash('flash_message', 'No keyword entered!', 'danger');
     }
     return $search_results;
 }

 protected function eliminar_promocion()
 {
     $this->sql_con = new mysqli($this->link['host'], $this->link['user'], $this->link['pass'], $this->link['bd']);
     $this->datos['promocion_id'] = mysqli_real_escape_string($this->sql_con, $_POST['pId']);
     $this->sql_con->set_charset("utf8");
     $filas_afectadas = 0;
     $eliminar_promocion = $this->sql_con->prepare("DELETE FROM promocion WHERE promocion_id = ?");
     $eliminar_promocion->bind_param('i', $this->datos['promocion_id']);
     $eliminar_promocion->execute();
     $eliminacion_exitosa = $this->sql_con->affected_rows;
     if ($eliminacion_exitosa > 0) {
         $filas_afectadas++;
     }
     $eliminar_promocion->close();
     if ($filas_afectadas > 0) {
         $eliminar_promocion_sucursal = $this->sql_con->prepare("DELETE FROM promocion_sucursal WHERE promocion_id = ?");
         $eliminar_promocion_sucursal->bind_param('i', $this->datos['promocion_id']);
         $eliminar_promocion_sucursal->execute();
         $eliminacion_exitosa = $this->sql_con->affected_rows;
         if ($eliminacion_exitosa > 0) {
             $filas_afectadas++;
         }
         $eliminar_promocion_sucursal->close();
     }
     $this->resultado = $filas_afectadas;
 }

function allUsersMadeChoice($eventId)
{
    global $connection;
    //Get the event_date_ids that are associated with the user_choices.
    $qry = "SELECT \n\t\t\t\t\tevent_date.id as event_date_id,\n\t\t\t\t\tevent_date.event_id\n\t\t\t\tFROM \n\t\t\t\t\tevent_date\n\t\t\t\tWHERE \n\t\t\t\t\tevent_date.event_id = '" . mysqli_real_escape_string($connection, $eventId) . "'";
    if ($result = mysqli_query($connection, $qry)) {
        $eventDateIds = array();
        while ($row = mysqli_fetch_array($result)) {
            //Save the event date ids
            $eventDateIds[] = $row['event_date_id'];
        }
        if (count($eventDateIds) > 0) {
            //Create a string from the array of IDS so we can use SQL's IN statement. WHERE id IN (1, 2, 3) etc.
            $eventDateString = '';
            foreach ($eventDateIds as $id) {
                $eventDateString .= $id . ', ';
            }
            $eventDateString = substr($eventDateString, 0, -2);
            $getChoices = "SELECT * FROM date_userchoice WHERE event_date_id IN(" . $eventDateString . ") AND choice = 0";
            if ($result = mysqli_query($connection, $getChoices)) {
                $count = mysqli_num_rows($result);
                //If the count is = 0 it means that all choices are not 0(0 means no choice made)
                if ($count == 0) {
                    return true;
                }
            }
        }
    }
    return false;
}

/**
* смена пароля
**/
function change_forgot_password()
{
    global $connection;
    $hash = trim(mysqli_real_escape_string($connection, $_POST['hash']));
    $password = trim($_POST['new_password']);
    if (empty($password)) {
        $_SESSION['forgot']['change_error'] = "Не введен пароль";
        return;
    }
    $query = "SELECT * FROM forgot WHERE hash = '{$hash}' LIMIT 1";
    $res = mysqli_query($connection, $query);
    // если не найден хэш
    if (!mysqli_num_rows($res)) {
        return;
    }
    $now = time();
    $row = mysqli_fetch_assoc($res);
    // если ссылка устарела
    if ($row['expire'] - $now < 0) {
        mysqli_query($connection, "DELETE FROM forgot WHERE expire < {$now}");
        return;
    }
    $password = md5($password);
    mysqli_query($connection, "UPDATE users SET password = '{$password}' WHERE email = '{$row['email']}'");
    mysqli_query($connection, "DELETE FROM forgot WHERE email = '{$row['email']}'");
    $_SESSION['forgot']['ok'] = "Вы успешно сменили пароль. Теперь можно авторизоваться";
}

 function sanitize($var, $quotes = true)
 {
     global $connection;
     if (is_array($var)) {
         //run each array item through this function (by reference)
         foreach ($var as &$val) {
             $val = $this->sanitize($val);
         }
     } else {
         if (is_string($var)) {
             //clean strings
             $var = mysqli_real_escape_string($connection, $var);
             if ($quotes) {
                 $var = "'" . $var . "'";
             }
         } else {
             if (is_null($var)) {
                 //convert null variables to SQL NULL
                 $var = "NULL";
             } else {
                 if (is_bool($var)) {
                     //convert boolean variables to binary boolean
                     $var = $var ? 1 : 0;
                 }
             }
         }
     }
     return $var;
 }

function au_login()
{
    global $aulis;
    // Error messages!
    $errormsg = array();
    // Are we currently attempting to login?
    if (isset($_POST['au_login'])) {
        // Did we provide our username?
        if (empty($_POST['au_username'])) {
            $errormsg[] = LOGIN_NO_USERNAME;
        }
        // What about our password?
        if (empty($_POST['au_password'])) {
            $errormsg[] = LOGIN_NO_PASSWORD;
        }
        // Create variables that are easier to type
        $login['username'] = $_POST['au_username'];
        $login['password'] = $_POST['au_password'];
        // Usernames don't contain HTML
        if ($login['username'] != htmlspecialchars($login['username'], ENT_NOQUOTES, 'UTF-8', false)) {
            $errormsg[] = LOGIN_USERNAME_NO_HTML;
        }
        // We don't want to mess up the database
        $login['username'] = mysqli_real_escape_string($aulis['connection'], $login['username']);
        // Hash the password
        $login['password'] = au_hash($login['password']);
        // Okay. Now check if the database has any record of the user
        $result = au_query("\n\t\t\tSELECT user_id, user_username, user_password\n\t\t\t\tFROM users\n\t\t\t\tWHERE user_username = '" . $login['username'] . "'\n\t\t");
        // This is only run if the user exists
        foreach ($result as $userlogin) {
            // Get the user id
            $userid = $userlogin['user_id'];
            // Does the password match?
            if ($userlogin['user_password'] == $login['password']) {
                $correctpass = true;
            } else {
                $errormsg[] = LOGIN_PASSWORD_FAIL;
            }
        }
        // Can we login?
        if (!empty($correctpass)) {
            // The user agent
            $login['user_agent'] = mysqli_real_escape_string($aulis['connection'], $_SERVER['HTTP_USER_AGENT']);
            // The IP address
            $login['user_ip'] = addslashes($_SERVER['REMOTE_ADDR']);
            // How long should we keep the session active?
            $sessionlength = !empty($_POST['au_forever']) ? '0' : '60';
            // Set the session
            $_SESSION[$setting['session_name']] = array('user' => $userid, 'agent' => $login['user_agent'], 'ip' => $login['user_ip'], 'sessionlength' => $sessionlength);
            // Show a nice information page
            template_info('login_success', 'login_success_title', 'user_green.png', $basefilenq, 'login_link');
        }
    }
    // This array is used in the login template
    $logindata = array('errors' => empty($_POST['au_login']) ? 0 : 1, 'error_message' => $errormsg, 'username' => !empty($login['username']) ? $login['username'] : '');
    // Okay, load this app's template
    au_load_template('login', false);
    // Show the registration template
    au_template_login(!empty($login_complete) ? true : false);
}

 public function updateProfileInfo()
 {
     $userid = mysqli_real_escape_string($this->mysqli, $_REQUEST['userid']);
     if (!empty($userid)) {
         $arr = array();
         $fname = mysqli_real_escape_string($this->mysqli, $_REQUEST['fname']);
         $lname = mysqli_real_escape_string($this->mysqli, $_REQUEST['lname']);
         $email = mysqli_real_escape_string($this->mysqli, $_REQUEST['email']);
         $mobile = mysqli_real_escape_string($this->mysqli, $_REQUEST['mobile_no']);
         $telno = mysqli_real_escape_string($this->mysqli, $_REQUEST['telephone_no']);
         $postcode = mysqli_real_escape_string($this->mysqli, $_REQUEST['postcode']);
         $address1 = mysqli_real_escape_string($this->mysqli, $_REQUEST['address1']);
         $address2 = mysqli_real_escape_string($this->mysqli, $_REQUEST['address2']);
         $city = mysqli_real_escape_string($this->mysqli, $_REQUEST['city']);
         $country = mysqli_real_escape_string($this->mysqli, $_REQUEST['country']);
         $dob = mysqli_real_escape_string($this->mysqli, $_REQUEST['dob_date']);
         $dateOfAniversary = mysqli_real_escape_string($this->mysqli, $_REQUEST['doa']);
         $query = "UPDATE user_profile a INNER JOIN user b ON a.user_id = b.id SET a.first_name = '{$fname} ', a.last_name = '{$lname}', b.email = '{$email}', a.address1 = '{$address1}', a.address2 = '{$address2}', a.mobile_no = '{$mobile}', a.telephone_no = '{$telno}', a.postcode = '{$postcode}', a.town = '{$city}', a.region = '{$country}', a.date_of_birth = '{$dob}', a.date_of_anniversery = '{$dateOfAniversary}' WHERE a.user_id = '" . $userid . "'";
         $result = $this->mysqli->query($query) or die($this->mysqli->error . __LINE__);
         if ($result) {
             $success = self::ViewProfile($userid);
             echo json_encode($success);
         } else {
             $callback = array('status' => "Failed", 'msg' => "Profile informaiton not updated.");
             echo json_encode($callback);
         }
     } else {
         $callback = array('status' => "Failed", "msg" => "Profile information not found!");
         echo json_encode($callback);
     }
 }

 public static function prepareData($item, $mysqli)
 {
     include_once getcwd() . '/scripts/data-helpers/elrh_db_extractor.php';
     // determine data according the item request
     if (empty($item)) {
         // if no item selected = show list of all articles
         $data["entries"] = ELRHDataExtractor::retrieveArray($mysqli, "SELECT a.id AS aid, a.cat, a.posted, a.name AS article_name, a.dscr, g.id AS gid, g.name AS gallery_name, u.u_displayed_name AS author_name FROM elrh_articles a LEFT JOIN elrh_gallery_galleries g ON a.gallery=g.id JOIN elrh_users u ON a.author=u.u_name ORDER BY a.posted DESC");
         // notify content renderer, there will be only list of articles
         $data["single"] = false;
     } else {
         // still have to determine between article-id and admin operations
         if (is_numeric($item)) {
             // notify content renderer, there will be only one article
             $data["single"] = true;
             // try to find particular article
             $data["entry"] = ELRHDataExtractor::retrieveRow($mysqli, "SELECT a.id AS aid, a.author, a.cat, a.posted, a.name AS article_name, a.dscr, a.content, g.id AS gid, g.name AS gallery_name, (SELECT count(*) FROM elrh_gallery_images i WHERE i.gallery=g.id) AS images, u.u_displayed_name AS author_name FROM elrh_articles a LEFT JOIN elrh_gallery_galleries g ON a.gallery=g.id JOIN elrh_users u ON a.author=u.u_name WHERE a.id='" . mysqli_real_escape_string($mysqli, $item) . "'");
             if (!empty($data["entry"])) {
                 // page title adjustment
                 $data["item_title"] = ": " . $data["entry"]["article_name"];
                 // notify content renderer, that article exists
                 $data["exists"] = true;
             } else {
                 // notify content renderer, that article not found
                 $data["exists"] = false;
             }
         } else {
             // TODO admin operations
         }
     }
     // save prepared data for renderer
     return $data;
 }

 public function addUpdate($title, $body, $target, $source)
 {
     $con = $this->connect();
     $title = mysqli_real_escape_string($con, $title);
     $body = mysqli_real_escape_string($con, $body);
     $target = mysqli_real_escape_string($con, $target);
     $source = mysqli_real_escape_string($con, $source);
     $query = "INSERT INTO news VALUES(null, '{$title}', '{$body}','{$target}', NOW(),'{$source}')";
     $res = mysqli_query($con, $query) or die("Couldn't execute query: " . mysqli_error($con));
     if ($res) {
         $id = mysqli_insert_id($con);
         $query = "SELECT * FROM news WHERE id = {$id}";
         $update = mysqli_query($con, $query);
         if (mysqli_num_rows($update) > 0) {
             $rows = array();
             while ($row = mysqli_fetch_array($update, MYSQLI_ASSOC)) {
                 $rows[] = $row;
             }
             return $rows;
         } else {
             return false;
         }
     } else {
         return false;
     }
     $this->close();
 }

function escapeArray($db, $array)
{
    foreach ($array as $key => $value) {
        $array['key'] = mysqli_real_escape_string($db->link, $value);
    }
    return $array;
}

function clearString($str)
{
    $str = strip_tags($str);
    $str = mysqli_real_escape_string($mysqli, $str);
    $str = trim($str);
    return $str;
}

 function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
 {
     if (PHP_VERSION < 6) {
         $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
     }
     //$theValue = function_exists("mysqli_real_escape_string") ? mysqli_real_escape_string($conexion , $theValue) : mysqli_escape_string( $conexion , $theValue);
     if (function_exist) {
         $obj = new database();
         $link = $obj->connect();
         $theValue = mysqli_real_escape_string($link, $theValue);
     }
     switch ($theType) {
         case "text":
             $theValue = $theValue != "" ? "'" . $theValue . "'" : "NULL";
             break;
         case "long":
         case "int":
             $theValue = $theValue != "" ? intval($theValue) : "NULL";
             break;
         case "double":
             $theValue = $theValue != "" ? doubleval($theValue) : "NULL";
             break;
         case "date":
             $theValue = $theValue != "" ? "'" . $theValue . "'" : "NULL";
             break;
         case "defined":
             $theValue = $theValue != "" ? $theDefinedValue : $theNotDefinedValue;
             break;
     }
     return $theValue;
 }

function api_remove_table($activeUser, $con, $character_get)
{
    if (isset($_GET['rm'])) {
        $remove = mysqli_real_escape_string($con, $_GET['rm']);
        $remove_name = utils::mysqli_result(mysqli_query($con, "SELECT name FROM characters WHERE eve_idcharacter = '{$remove}'"), 0, 0);
        //character is only dissossiated with the account, not removed from the database
        $remove_character_account = mysqli_query($con, "DELETE FROM aggr WHERE user_iduser = (SELECT iduser FROM user WHERE username = '{$activeUser}') AND character_eve_idcharacter = '{$remove}'") or die(mysqli_error($con));
        //$remove_character = mysqli_query($con, "DELETE FROM characters WHERE eve_idcharacter = '$remove'") or die(mysqli_error($con));
        echo "Character " . $remove_name . " removed successfully.";
        return;
    } else {
        $charsKeys = mysqli_query($con, "SELECT character_eve_idcharacter, name, username, apikey FROM v_user_characters WHERE username = '{$activeUser}'") or die(mysqli_error($con));
        ?>
   <table class='table table-striped table-bordered table-hover' id='dataTables-api'>
       <tr><th align="center">Character</th>
           <th align="center">API Key</th>
           <th></th>
<?php 
        while ($chars = mysqli_fetch_array($charsKeys)) {
            $name = $chars['name'];
            $api = $chars['apikey'];
            $charid = $chars['character_eve_idcharacter'];
            $imgpath = "https://image.eveonline.com/Character/" . $charid . "_32.jpg";
            echo "<tr><td>" . "<img src=" . $imgpath . ">" . "  " . $name . "</td><td >" . $api . "</td><td align='center'>" . "<a href= 'api_remove.php?character={$character_get}&rm={$charid}'<button type='button' class='btn btn-danger'>Remove</button>" . "</td></tr>";
        }
        ?>
   </table>
<?php 
    }
}

 protected function traer_fichas()
 {
     extract($_POST);
     $this->datos_usuario["tipo"] = mysqli_real_escape_string($this->sql_con, $tipo);
     $this->datos_usuario["nmr_folio"] = mysqli_real_escape_string($this->sql_con, $nmr_folio);
     switch ($this->datos_usuario["tipo"]) {
         case 1:
             $this->traer_todo();
             break;
     }
     if (isset($user)) {
         $this->arreglo['cliente'] = array();
         //$this->arreglo['contacto']=array();
         //$this->arreglo['producto']=array();
         //$this->arreglo['folio']=array();
         if ($rol != 1) {
             $consulta = "select * from cliente c left join ingreso i on i.ing_rut_usu = c.cli_rut";
             $consulta .= " left join contacto co on co.con_cli_rut=c.cli_rut";
             $consulta .= " left join servicio s on s.serv_cli_rut=c.cli_rut";
             $consulta .= " left join ingreso_estado ie on i.ing_id = ie.ingreso_id";
             if ($rol == 2) {
                 $consulta .= " where i.ing_id_usuario='" . $user . "'";
             }
         }
         $con = $this->sql_con->query($consulta);
         while ($arr = $con->fetch_assoc()) {
             $clientes = array();
             $clientes = array("cli_rut" => $arr["cli_rut"], "cli_nombre" => $arr["cli_nombre"], "cli_app" => $arr["cli_app"], "cli_apm" => $arr["cli_apm"], "cli_id" => $arr["cli_id"], "cli_fantasia" => $arr["cli_fantasia"], "cli_rut_emp" => $this->rut($arr["cli_rut_emp"]), "nmr_folio" => $arr["ing_nmr_folio"], "fecha_ing" => $arr["ing_fecha_contrato"], "con_nombre" => $arr["con_nombre"], "con_mail" => $arr["con_mail"], "con_tmovil" => $arr["con_tmovil"], "con_tfijo" => $arr["con_tfijo"], "serv_tipoplan" => $this->nombre_plan($arr["serv_tipoplan"]), "serv_cli_rut" => $arr["serv_cli_rut"], "serv_nombre_proveedor" => $arr["serv_nombre_proveedor"], "vendedor" => $arr["serv_vendedor"], "estado" => $arr["ingreso_estado"]);
             array_push($this->arreglo["cliente"], $clientes);
         }
     }
 }

function traitement_magic_quotes($_value) {
   if (get_magic_quotes_gpc())    $_value = stripslashes($_value);
   if (!is_numeric($_value)) {
        $_value = mysqli_real_escape_string($GLOBALS["mysqli"], $_value);
   }
   return $_value;
}

function validate($dbc, $email = '', $pwd = '')
{
    $errors = array();
    #Array to store errors.
    if (empty($email)) {
        $errors[] = 'Enter your email address.';
    } else {
        $e = mysqli_real_escape_string($dbc, trim($email));
        #Escapes any special characters
        #to avoid codes being run on the database.
        $email = strip_tags($email);
    }
    if (empty($pwd)) {
        $errors[] = 'Enter your password.';
    } else {
        $p = mysqli_real_escape_string($dbc, trim($pwd));
        $pwd = strip_tags($pwd);
    }
    if (empty($errors)) {
        $q = "SELECT customer_id,first_name,last_name\r\n\tFROM customers \r\n\tWHERE email='{$e}'\r\n\tAND password= SHA1('{$p}')";
        #Retrieves customer related data
        $r = mysqli_query($dbc, $q);
        if (mysqli_num_rows($r) == 1) {
            $row = mysqli_fetch_array($r, MYSQLI_ASSOC);
            return array(true, $row);
        } else {
            $errors[] = 'Email address and password not found';
        }
        return array(false, $errors);
    }
}

function isInQueue()
{
    // Reference Global Variables
    global $globalHostName;
    global $globalUserName;
    global $globalPassword;
    global $globalDatabase;
    // MySQL Connection
    $connection = mysqli_connect($globalHostName, $globalUserName, $globalPassword, $globalDatabase);
    // Connection Error Handling
    if ($connection->connect_error) {
        // Kill the Connection
        die("Could Not Connect to the Database");
    }
    // MySQL Injection Neutralized Email Variable
    $safeEmail = mysqli_real_escape_string($connection, $_REQUEST['inputEmail']);
    // Query Preparation
    $query = mysqli_prepare($connection, 'SELECT COUNT(*) as total FROM users WHERE email = ?');
    $query->bind_param('s', $safeEmail);
    // Query Execution
    mysqli_stmt_execute($query);
    // Query Result Analysis
    mysqli_stmt_bind_result($query, $total);
    $data = mysqli_stmt_fetch($query);
    //-----
    $connection->close();
    // If That Email is Already Registered...
    if ($total > 0) {
        echo "true";
        return true;
    } else {
        echo "false";
        return false;
    }
}

function sanitizeString($_db, $str)
{
    $str = strip_tags($str);
    $str = htmlentities($str);
    $str = stripslashes($str);
    return mysqli_real_escape_string($_db, $str);
}

function hesk_dbEscape($in)
{
    global $hesk_db_link;
    $in = mysqli_real_escape_string($hesk_db_link, stripslashes($in));
    $in = str_replace('`', '`', $in);
    return $in;
}

function update_item($dbc, $values)
{
    if (isset($values['owner'])) {
        $person = 'owner';
    } else {
        $person = 'finder';
    }
    # Get the ID of the item being updated
    $id = $values['id'];
    foreach ($values as $key => $value) {
        # Sanitize the input
        $values[$key] = mysqli_real_escape_string($dbc, $value);
    }
    # Get the location id by name
    $building = mysqli_real_escape_string($dbc, $id);
    $location_id = get_location_id($dbc, $values['building']);
    $person_value = $values[$person];
    # Build the UPDATE query
    $query = "UPDATE stuff SET item = '{$values['item']}', {$person} = '{$person_value}', phone = '{$values['phone']}', email = '{$values['email']}', location_id = {$location_id}, room = '{$values['room']}', description = '{$values['description']}', update_date = NOW() WHERE id = {$values['id']}";
    $results = mysqli_query($dbc, $query);
    check_results($dbc, $results);
    if (!$results) {
        return false;
    } else {
        # If succesfful, return the id of the newly inserted item
        return $id;
    }
}