function show_css_page($dbconn, $diary_login)
{
$sql = "SELECT u.uid, s.page_css FROM " . TABLE_SETTINGS . " AS s, " . TABLE_USERS . " AS u WHERE u.login='******' AND s.uid=u.uid LIMIT 1";
$result = pg_query($dbconn, $sql) or die(pg_last_error($dbconn));
$data = pg_fetch_object($result, NULL);
echo $data->page_css;
}
/** Constructor. Creates a DB connection. */
public function __construct()
{
$this->conn = pg_connect("host=" . APPDB_HOST . " dbname=" . APPDB_DATABASE . " port=" . APPDB_PORT . " user="******" password="******"Could not connect to database: " . pg_last_error());
}
}
public static function getPrivacyData()
{
global $dbconn, $ixmaps_debug_mode;
$sql1 = "SELECT privacy_stars.* FROM privacy_stars";
$sql2 = "SELECT privacy_scores.* FROM privacy_scores order by asn, star_id";
//echo $sql;
$result1 = pg_query($dbconn, $sql1) or die('Query privacy_stars failed: ' . pg_last_error());
$result2 = pg_query($dbconn, $sql2) or die('Query privacy_scores failed: ' . pg_last_error());
// loop and format the data
$stars = array();
$scores = array();
// stars
while ($line1 = pg_fetch_array($result1, null, PGSQL_ASSOC)) {
$stars[$line1['star_id']] = $line1;
}
while ($line2 = pg_fetch_array($result2, null, PGSQL_ASSOC)) {
$scores[$line2['asn']][] = $line2;
}
//$stars = pg_fetch_all($result1);
//$scores = pg_fetch_all($result2);
pg_free_result($result1);
pg_free_result($result2);
$privacy = array('stars' => $stars, 'scores' => $scores);
pg_close($dbconn);
//print_r($privacy);
return $privacy;
}
function query($query)
{
if (defined('WE_ARE_TESTING')) {
file_put_contents('/tmp/pg-query', $query . ";\n\n", FILE_APPEND);
}
$this->result = pg_query($this->link, $query) or $this->nicedie($query . "\n\n" . pg_last_error($this->link));
}
function get_points($table_name, $show_json = FALSE)
{
// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=steki_db user=postgres password=password") or die('Could not connect: ' . pg_last_error());
// Performing SQL query
$query = 'SELECT array_to_json(array_agg(row_to_json(t))) as ' . $table_name . '_json
FROM (
SELECT id, latlong[0] as latitude, latlong[1] as longitude, brand, rating, votes, nam as name, descr as description FROM "' . $table_name . '"
) t';
//$query = 'SELECT row_to_json("'.$table_name.'") as '.$table_name.'_json FROM "'.$table_name.'"';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
// Printing results in HTML
$json = pg_fetch_array($result, null, PGSQL_ASSOC);
if ($show_json) {
echo json_encode(array_values($json));
} else {
$str = implode("*", $json);
$out = htmlspecialchars($str);
echo $out;
}
// Free resultset
pg_free_result($result);
// Closing connection
pg_close($dbconn);
//return $json;
}
function FetchLogs($channel)
{
$html = "";
$c = 0;
$logs = array();
$display_joins = isset($_GET['data']);
if ($display_joins) {
$sql = "SELECT * FROM logs WHERE channel = '" . pg_escape_string($channel) . "' and time > to_timestamp( '" . pg_escape_string($_GET["start"] . " 00:00:00") . "', 'MM/DD/YYYY HH24:MI:SS' ) and time < to_timestamp( '" . pg_escape_string($_GET["end"] . " 23:59:59") . "', 'MM/DD/YYYY HH24:MI:SS' ) order by time asc;";
} else {
$sql = "SELECT * FROM logs WHERE channel = '" . pg_escape_string($channel) . "' and time > to_timestamp( '" . pg_escape_string($_GET["start"] . " 00:00:00") . "', 'MM/DD/YYYY HH24:MI:SS' ) and time < to_timestamp( '" . pg_escape_string($_GET["end"] . " 23:59:59") . "', 'MM/DD/YYYY HH24:MI:SS' ) and type = 0 order by time asc;";
}
$query = pg_query($sql);
if (!$query) {
die('SQL failure: ' . pg_last_error());
}
while ($item = pg_fetch_assoc($query)) {
$logs[] = $item;
$c++;
}
if ($c == 0) {
return "No logs found, try a different filter";
}
$html .= "<p>Displaying {$c} items:</p>\n";
if (isset($_GET["wiki"])) {
$html .= LogsWiki::Render2($logs);
} else {
$html .= LogsHtml::RenderLogs($logs);
}
return $html;
}
function get_attributes()
{
$sql_str = "SELECT " . "name, " . "style_desc, " . "symbol_name, " . "symbol_size, " . "angle, " . "width, " . "color_r, " . "color_g, " . "color_b, " . "outlinecolor_r, " . "outlinecolor_b, " . "outlinecolor_g, " . "bgcolor_r, " . "bgcolor_g, " . "bgcolor_b " . "FROM " . "tng_mapserver_style " . "WHERE " . "id = " . $this->id;
$this->dbconn->connect();
$result = pg_query($this->dbconn->conn, $sql_str);
if (!$result) {
echo "An error occurred while executing the query - " . $sql_str . " - " . pg_last_error($this->dbconn->conn);
//$this->dbconn->disconnect();
return false;
}
// successfuly ran the query
// store attributes
$this->name = pg_fetch_result($result, 0, 'name');
$this->style_desc = pg_fetch_result($result, 0, 'style_desc');
$this->symbol_name = pg_fetch_result($result, 0, 'symbol_name');
$this->symbol_size = pg_fetch_result($result, 0, 'symbol_size');
$this->angle = pg_fetch_result($result, 0, 'angle');
$this->width = pg_fetch_result($result, 0, 'width');
$this->color_r = pg_fetch_result($result, 0, 'color_r');
$this->color_g = pg_fetch_result($result, 0, 'color_g');
$this->color_b = pg_fetch_result($result, 0, 'color_b');
$this->outlinecolor_r = pg_fetch_result($result, 0, 'outlinecolor_r');
$this->outlinecolor_g = pg_fetch_result($result, 0, 'outlinecolor_g');
$this->outlinecolor_b = pg_fetch_result($result, 0, 'outlinecolor_b');
$this->bgcolor_r = pg_fetch_result($result, 0, 'bgcolor_r');
$this->bgcolor_g = pg_fetch_result($result, 0, 'bgcolor_g');
$this->bgcolor_b = pg_fetch_result($result, 0, 'bgcolor_b');
$this->dbconn->disconnect();
return true;
}
/**
* @throws SQLException
* @return void
*/
protected function initTables()
{
include_once 'creole/drivers/pgsql/metadata/PgSQLTableInfo.php';
// Get Database Version
$result = pg_exec($this->dblink, "SELECT version() as ver");
if (!$result) {
throw new SQLException("Failed to select database version");
}
// if (!$result)
$row = pg_fetch_assoc($result, 0);
$arrVersion = sscanf($row['ver'], '%*s %d.%d');
$version = sprintf("%d.%d", $arrVersion[0], $arrVersion[1]);
// Clean up
$arrVersion = null;
$row = null;
pg_free_result($result);
$result = null;
$result = pg_exec($this->dblink, "SELECT oid, relname FROM pg_class\n\t\t\t\t\t\t\t\t\t\tWHERE relkind = 'r' AND relnamespace = (SELECT oid\n\t\t\t\t\t\t\t\t\t\tFROM pg_namespace\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t nspname NOT IN ('information_schema','pg_catalog')\n\t\t\t\t\t\t\t\t\t\t AND nspname NOT LIKE 'pg_temp%'\n\t\t\t\t\t\t\t\t\t\t AND nspname NOT LIKE 'pg_toast%'\n\t\t\t\t\t\t\t\t\t\tLIMIT 1)\n\t\t\t\t\t\t\t\t\t\tORDER BY relname");
if (!$result) {
throw new SQLException("Could not list tables", pg_last_error($this->dblink));
}
while ($row = pg_fetch_assoc($result)) {
$this->tables[strtoupper($row['relname'])] = new PgSQLTableInfo($this, $row['relname'], $version, $row['oid']);
}
}
function daemon_scanTime()
{
require "daemon_db_init.php";
// to initialize database connection
// 1, get timestamp
// $time = date("j F Y h:i:s A");
// $timestamp = "'" . date('YmdGis') . "'";
global $timestamp;
// 2, check if the table "dameontimestamp" in the database "vanguardhe"
$query_exist = "SELECT relname FROM pg_class \n\tWHERE relname = 'dameontimestamp';";
$result_exist = pg_query($query_exist) or die('Query failed: ' . pg_last_error());
$exist = '';
while ($row_exist = pg_fetch_object($result_exist)) {
$exist = $row_exist->relname;
}
// // 3, if not existed, create it
if ($exist != "dameontimestamp") {
# code...
$query_construct = "CREATE TABLE PUBLIC.dameontimestamp(\n\t\t\tscanid SERIAL PRIMARY KEY,\n\t\t\ttime TEXT,\n\t\t\talarmcount TEXT );";
$result_construct = pg_query($query_construct) or die('Query failed: ' . pg_last_error());
pg_free_result($result_construct);
}
// 4, insert data into the table
$query_insert = "INSERT INTO PUBLIC.dameontimestamp (time) VALUES ({$timestamp});";
$result_insert = pg_query($query_insert) or die('Query failed: ' . pg_last_error());
pg_free_result($result_exist);
pg_free_result($result_insert);
}
function upload($database, $userlogin, $file, $maxsize, $extensions)
{
if (isset($_FILES[$file]) && $_FILES[$file]['error'] == 0) {
$upload = false;
$upload_dest = '../Users/Images/' . $_SESSION['login'] . '.jpg';
if ($_FILES[$file]['size'] <= $maxsize) {
$infosfichier = pathinfo($_FILES[$file]['name']);
$extension_upload = $infosfichier['extension'];
if (in_array($extension_upload, $extensions)) {
$upload = move_uploaded_file($_FILES[$file]['tmp_name'], $upload_dest);
}
} else {
$msg = "<span class=\"red\">Photo volumineuse</span>";
}
if ($upload == true) {
$photo = pg_escape_string($upload_dest);
$query_photo = pg_query($database, "UPDATE users SET photo='{$photo}' WHERE login='******'") or die('Échec requête : ' . pg_last_error());
if ($query_photo != false) {
$msg = "Envoi du fichier \"" . $_FILES[$file]['name'] . "\" réussi";
} else {
$msg = "Photo envoyée mais non ajouté à la base";
}
pg_free_result($query_photo);
} else {
$msg = "<span class=\"red\">Envoi du fichier \"" . $_FILES[$file]['name'] . "\" échoué</span>";
}
} else {
$msg = "Photo de profil supprimée";
}
return $msg;
}
public function testUniqueCheck($username, $email)
{
//Get info from the array
$final_username = $username;
$final_email = $email;
//Connect to the db
$db = $this->connectProd();
//Check for username and email address
$query_user_check = "SELECT username FROM tb_users WHERE username = '******'";
$query_email_check = "SELECT email FROM tb_users WHERE email = '" . $final_email . "'";
pg_send_query($db, $query_user_check) or die('Query failed: ' . pg_last_error());
$username_check_result = pg_get_result($db);
$username_check_result_rows = pg_num_rows($username_check_result);
pg_close($db);
if ($username_check_result_rows == 0) {
//Set flag if no user found
$user_check = 'pass';
} else {
$user_check = 'fail';
}
if ($email_check_result_rows == 0) {
//Set flag if no email is found
$email_check = 'pass';
} else {
$email_check = 'fail';
}
if ($email_check == 'pass' && $user_check == 'pass') {
$check_result = 'pass';
return $check_result;
} else {
$check_result = 'fail';
return $check_result;
}
}
function pg_email_erro($serro)
{
global $secu, $base, $base_name;
$email = '*****@*****.**';
$tee = '<table width="400" bordercolor="#ff0000" border="3" align="center">';
$tee .= '<TR><TD bgcolor="#ff0000" align="center"><FONT class="lt2"><FONT COLOR=white><B>Erro -' . $base . '-[' . $base_name . ']-</TD></TR>';
$tee .= '<TR><TD><B><TT>';
$tee .= $serro;
$tee .= '<TR><TD><B><TT>';
$tee .= pg_last_error();
$tee .= '<TR><TD><B><TT>';
$tee .= '<BR>Remote Address: ' . $_SERVER['REMOTE_ADDR'];
$tee .= '<BR>Metodo: ' . $_SERVER['REQUEST_METHOD'];
$tee .= '<BR>Nome da pagina: ' . $_SERVER['SCRIPT_NAME'];
$tee .= '<BR>Dominio: ' . $_SERVER['SERVER_NAME'];
$tee .= '<BR>Data: ' . date("d/m/Y H:i:s");
$tee .= '</table>';
$headers .= 'To: Rene (Monitoramento) <*****@*****.**>' . "\r\n";
$headers .= 'From: BancoSQL (PG) <*****@*****.**>' . "\r\n";
$headers .= 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
mail($to, $subject, $message, $headers);
mail($email, 'Erros de SQL ' . $secu, $tee, $headers);
// echo '<BR>e-mail enviado para '.$email ;
}
function dbconnect()
{
//connects to the db
$dbConnString = "host=173.254.28.90 options='--client_encoding=UTF8' user=feedmati_user dbname=feedmati_system password=PZi0wuz9n+XX";
$dbConn = pg_connect($dbConnString) or die("Problem with connection to PostgreSQL:" . pg_last_error());
return $dbConn;
}
public function consulta($aux)
{
$auxcon = $this->conectar();
$resultado = pg_query($aux) or die('La consulta fallo: ' . pg_last_error());
pg_close($auxcon);
return $resultado;
}
public function loadAvatarsWithMaxLevel($accounts)
{
$avatars = array();
// we will map account to avatar
$accountParts = array_chunk($accounts, 10);
foreach ($accountParts as $accountPart) {
$logins = $this->makeLoginLikeQuery($accountPart);
$sql = "select \"avatarId\", \"title\", \"race\", \"class\", \"shardName\", level, login from \"statistics\".v_avatar where {$logins} and \"isDeleted\" = false";
$result = pg_query($this->conn, $sql);
if (!$result) {
throw new DBException("Failed to query avatars: " + pg_last_error($this->conn));
}
while ($row = pg_fetch_assoc($result)) {
$avatar = array();
$avatar['id'] = $row['avatarId'];
$avatar['title'] = $row['title'];
$avatar['race'] = $row['race'];
$avatar['class'] = $row['class'];
$avatar['shard'] = $row['shardName'];
$avatar['level'] = intval($row['level']);
$account = $row['login'];
$old = @$avatars[$account];
if (isset($old)) {
if ($old['level'] < $avatar['level']) {
$avatars[$account] = $avatar;
}
} else {
$avatars[$account] = $avatar;
}
}
}
return $avatars;
}
function get_db_variables()
{
$sql_str = "SELECT " . "var_name, " . "var_value " . "FROM " . "portal_config_variables ";
$this->dbconn->connect();
$result = pg_query($this->dbconn->conn, $sql_str);
if (!$result) {
echo "An error occurred while executing the query - " . $sql_str . " - " . pg_last_error($this->dbconn->conn);
$this->dbconn->disconnect();
return false;
}
$n_rows = pg_num_rows($result);
$vars = array();
for ($i = 0; $i < $n_rows; $i++) {
$vars[pg_fetch_result($result, $i, 'var_name')] = pg_fetch_result($result, $i, 'var_value');
}
$this->dbconn->disconnect();
// set member variables from name/value pairs
$this->upload_path = $vars["upload_path"];
$this->mapfile_path = $vars["mapfile_path"];
$this->layer_config_path = $vars["layer_config_path"];
$this->mapservice_config_path = $vars["mapservice_config_path"];
$this->mapservice_name = $vars["mapservice_name"];
$this->map_agent = $vars["map_agent"];
$this->map_agent_launch_url = $vars["map_agent_launch_url"];
$this->output_dir = $vars["output_dir"];
$this->ogr2ogr_path = $vars["ogr2ogr_path"];
$this->confirmation_email_template = $vars["confirmation_email_template"];
return true;
}
function Error()
{
if (version_compare(phpversion(), "4.2.0", "ge") > 0) {
$this->error = pg_last_error($this->dbconnect);
}
return $this->error;
}
public function conexion()
{
$this->conexion = pg_connect("host=localhost port=5432 dbname=pao user=postgres password=123") or die('No pudo conectarse: ' . pg_last_error());
if (!$this->conexion) {
die('No pudo conectarse: ' . pg_last_error());
}
}
public function exec($query, $params = array(), $html_safe = true, $debug = false)
{
// Process the request.
if ($debug) {
echo "<pre>" . $query . "</pre><br />";
}
if ($html_safe) {
foreach ($params as &$p) {
$p = htmlspecialchars($p);
}
}
$result = pg_query_params($this->db, $query, $params);
if (!$result) {
echo "<b>Query Failed:</b><pre>" . $query . "</pre><br />";
}
// Check for errors.
$pg_error = pg_last_error();
// If there are errors, put them in the error list
if (strlen($pg_error) > 0) {
$this->errors .= $pg_error;
echo $pg_error;
return false;
} else {
return new PgSqlResult($result);
}
}
function show_main_page($dbconn, $diary_login)
{
$sql = "SELECT u.uid, s.page_main, s.format_note FROM " . TABLE_SETTINGS . " AS s, " . TABLE_USERS . " AS u WHERE u.login='******' AND s.uid=u.uid LIMIT 1";
$result = pg_query($dbconn, $sql) or die(pg_last_error($dbconn));
$data = pg_fetch_object($result, NULL);
echo assign_vars($data->page_main, array('{login}' => $diary_login, '{diary}' => get_last_notes($dbconn, $diary_login, $data->format_note), '{hrefguestbook}' => 'http://' . $_SERVER['SERVER_NAME'] . '/' . PAGE_GUESTBOOK, '{archive}' => get_archive($dbconn, $diary_login), '{links}' => get_links($dbconn, $diary_login)));
}
private static function __query($sql)
{
if (!($res = pg_query(self::$__connection, $sql))) {
throw new Exception(pg_last_error(self::$__connection));
}
return $res;
}
private function __construct()
{
$this->handle = pg_connect("host=labdb dbname=bd user=fc359081 password=lel");
if (!$this->handle) {
App::app()->add_info('Nie udało się połączyć z bazą danych ' . pg_last_error(), 'danger');
}
}
function trae_fila($tipo_indice = "A")
{
try {
switch ($tipo_indice) {
case "A":
// Arreglo de datos con indice asociativo
$fila = @pg_fetch_array($this->id_resultado, null, PGSQL_ASSOC);
break;
case "N":
// Arreglo de datos con indice numerico
$fila = @pg_fetch_array($this->id_resultado, null, PGSQL_NUM);
break;
default:
// Otros casos
$fila = @pg_fetch_array($this->id_resultado, null, PGSQL_BOTH);
break;
}
if ($fila) {
return $fila;
} else {
throw new Exception(pg_last_error($this->id_conexion) == "" ? "" : "Error 03: " . pg_last_error($this->id_conexion));
}
} catch (Exception $e) {
$this->error1 = $e->getMessage();
return null;
}
}
function printUserLog()
{
//db connection
$conn = pg_connect(HOST . " " . DBNAME . " " . USERNAME . " " . PASSWORD) or die('Could not connect: ' . pg_last_error());
//query the database
$result = pg_prepare($conn, "getLog", "SELECT * FROM lab8.log\n\t\t\tWHERE username LIKE \$1") or die("getLog prepare fail: " . pg_last_error());
$result = pg_execute($conn, "getLog", array($_SESSION['user'])) or die("getLog execute fail: " . pg_last_error());
//Printing results in HTML
echo "<br>There where <em>" . pg_num_rows($result) . "</em> rows returned<br><br>\n";
echo "<table class='tablestuff' border='1'>";
//account for added form row
echo "<tr>";
//checking the number of fields return to populate header
$numFields = pg_num_fields($result);
//populating the header
for ($i = 0; $i < $numFields; $i++) {
$fieldName = pg_field_name($result, $i);
echo "<th width=\"135\">" . $fieldName . "</th>\n";
}
echo "</tr>";
//populating table with the results
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>{$col_value}</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
pg_free_result($result);
//close connection
pg_close($conn);
}
public static function castLink($link, array $a, Stub $stub, $isNested)
{
$a['status'] = pg_connection_status($link);
$a['status'] = new ConstStub(PGSQL_CONNECTION_OK === $a['status'] ? 'PGSQL_CONNECTION_OK' : 'PGSQL_CONNECTION_BAD', $a['status']);
$a['busy'] = pg_connection_busy($link);
$a['transaction'] = pg_transaction_status($link);
if (isset(self::$transactionStatus[$a['transaction']])) {
$a['transaction'] = new ConstStub(self::$transactionStatus[$a['transaction']], $a['transaction']);
}
$a['pid'] = pg_get_pid($link);
$a['last error'] = pg_last_error($link);
$a['last notice'] = pg_last_notice($link);
$a['host'] = pg_host($link);
$a['port'] = pg_port($link);
$a['dbname'] = pg_dbname($link);
$a['options'] = pg_options($link);
$a['version'] = pg_version($link);
foreach (self::$paramCodes as $v) {
if (false !== ($s = pg_parameter_status($link, $v))) {
$a['param'][$v] = $s;
}
}
$a['param']['client_encoding'] = pg_client_encoding($link);
$a['param'] = new EnumStub($a['param']);
return $a;
}
function connect($db_conn_string, $db_search_path)
{
global $conn;
$conn = pg_connect($db_conn_string) or die('Could not connect: ' . pg_last_error());
pg_query("SET search_path TO {$db_search_path}");
return $conn;
}
public static function last_error()
{
if (self::$connection) {
return @pg_last_error();
}
return "Database server unreachable.";
}
function conectar($usuario, $contrasena)
{
$servidor = "localhost";
$puerto = "5432";
$dbname = "congresos";
$conexion = pg_connect("host=" . $servidor . " port=" . $puerto . " dbname=" . $dbname . " user="******" password="******"No se ha podido conectar con la base de datos" . pg_last_error());
}
function PQuery($query)
{
require "config.php";
global $connection;
//error_log( $query );
//print $query."<br>";
$res = pg_query($connection, $query);
if (!$res) {
//print_r (debug_backtrace());
list(, $caller) = debug_backtrace(false);
error_log($caller['function'] . ', ' . $caller['line'] . ": {$query}");
$result['count'] = 0;
$result['rows'] = NULL;
$result['error'] = pg_last_error($connection);
return $result;
}
$result['count'] = pg_num_rows($res);
$i = 0;
$rowarr = array();
while ($row = pg_fetch_array($res, NULL, PGSQL_ASSOC)) {
$rowarr[$i++] = $row;
}
pg_free_result($res);
$result['rows'] = $rowarr;
return $result;
}
function DbError()
{
if (pg_connection_status($this->ConnectionID) != 0) {
return translate("ERROR_DBCONNECT_3");
}
return pg_last_error($this->ConnectionID);
}
