/**
* Internal method to fetch the next row from the result set
*
* @return array|null
*/
protected function getNextRow()
{
# If the result resource is invalid then don't bother trying to fetch
if (!$this->result) {
return;
}
switch ($this->mode) {
case "mysql":
$row = $this->result->fetch_assoc();
break;
case "postgres":
case "redshift":
$row = pg_fetch_assoc($this->result);
break;
case "odbc":
$row = odbc_fetch_array($this->result, $this->position + 1);
break;
case "sqlite":
$row = $this->result->fetchArray(SQLITE3_ASSOC);
break;
case "mssql":
$row = mssql_fetch_assoc($this->result);
break;
}
# If the fetch fails then there are no rows left to retrieve
if (!$row) {
return;
}
$this->position++;
return $row;
}
function queryArray($query)
{
$this->connection = pg_connect("{$this->connstring}") or die("Connection failed: " . pg_last_error());
$result = pg_query($query) or die("Query failed: " . pg_last_error());
$fetch = pg_fetch_assoc($result);
return $fetch;
}
public function select($data)
{
$sql = $this->user_model->select($data);
$query = $this->conexion->query($sql, $this->conexion);
$row = pg_fetch_assoc($query);
return json_encode($row);
}
/**
* Current
*
* @return array|bool|mixed
*/
public function current()
{
if ($this->count === 0) {
return false;
}
return pg_fetch_assoc($this->resource, $this->position);
}
public function obtenerArregloAsociativo($result)
{
if (!is_resource($result)) {
return false;
}
return pg_fetch_assoc($result);
}
public function getline()
{
if ($this->resource === false) {
return;
}
return pg_fetch_assoc($this->resource);
}
function printTable($result)
{
//get the first row in the table:
if (!($row = pg_fetch_assoc($result))) {
return false;
}
//set up the table:
print "<table><tr>";
//print the column names as table headers:
foreach ($row as $key => $value) {
print "<th>{$key}</th>";
}
print "</tr>";
//loop through the table, printing
//the field values in table cells:
do {
print "<tr>";
foreach ($row as $key => $value) {
print "<td>{$value}</td>";
}
print "</tr>";
} while ($row = pg_fetch_assoc($result));
//close out the table:
print "</tr></table>";
return true;
}
function get_servicecategoryname($service_category_id)
{
$result = pg_query_params("select servicecategoryname from techmatcher.servicecategories where servicecategory_id=\$1", array($service_category_id));
//$service_iddd=array();
$category_name = pg_fetch_assoc($result);
return $category_name;
}
function wikipedia_streetnames_info($info_ret, $object) {
global $data_lang;
$text="";
if(!$object->tags->get("highway"))
return;
$res=sql_query("select * from osm_polygon where osm_way && geomfromtext('{$object->data['way']}', 900913) and CollectionIntersects(osm_way, geomfromtext('{$object->data['way']}', 900913)) and osm_tags @> 'boundary=>administrative' order by parse_number(osm_tags->'admin_level') desc");
while($elem=pg_fetch_assoc($res)) {
$boundary=load_object($elem['osm_id']);
$data=cache_search($boundary->id, "wikipedia:street_names:$data_lang");
if($data) {
$data=unserialize($data);
}
else {
$data=wikipedia_get_lang_page($boundary, "wikipedia:street_names");
$article=wikipedia_get_article($boundary, $data['page'], $data['lang']);
$data['article']=$article;
cache_insert($boundary->id, "wikipedia:street_names:$data_lang",
serialize($data), "1 hour");
}
if($data['article']) {
$text.=wikipedia_streetnames_parse($data['article'], $object);
if($text) {
$text.="<br>".lang("source").": <a class='external' href='".wikipedia_url($boundary, $data['page'], $data['lang'])."'>Wikipedia</a>\n";
$info_ret[]=array("head"=>"wikipedia_streetnames", "content"=>$text, "doc"=>"plugin:wikipedia_streetnames/feature");
return;
}
}
}
}
/**
* @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']);
}
}
/**
* \brief
* delete from copyright where pfile_fk not in (select pfile_pk from pfile)
* add foreign constraint on copyright pfile_fk if not exist
*
* \return 0 on success, 1 on failure
**/
function Migrate_20_25($Verbose)
{
global $PG_CONN;
$sql = "select count(*) from pg_class where relname='copyright';";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
$row = pg_fetch_assoc($result);
pg_free_result($result);
if (1 > $row['count']) {
return 0;
// fresh install, even no copyright table
}
$sql = "delete from copyright where pfile_fk not in (select pfile_pk from pfile);";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
pg_free_result($result);
/** add foreign key CONSTRAINT on pfile_fk of copyrigyt table when not exist */
$sql = "SELECT conname from pg_constraint where conname= 'copyright_pfile_fk_fkey';";
$conresult = pg_query($PG_CONN, $sql);
DBCheckResult($conresult, $sql, __FILE__, __LINE__);
if (pg_num_rows($conresult) == 0) {
$sql = "ALTER TABLE copyright ADD CONSTRAINT copyright_pfile_fk_fkey FOREIGN KEY (pfile_fk) REFERENCES pfile (pfile_pk) ON DELETE CASCADE; ";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
pg_free_result($result);
print "add contr\n";
}
pg_free_result($conresult);
return 0;
}
function hashDecode($hashedUrl)
{
$urlEncode = new UrlEncode();
$result = array();
//No hash was given as input
if (!$hashedUrl) {
$result["error"] = "No input given";
} else {
$hashedUrl = $urlEncode->parseHash($hashedUrl);
if ($hashedUrl != -1) {
$id = $urlEncode->decodeToOriginalUrl($hashedUrl);
//Database stuff
global $dbConnStr;
//This comes from dbConfig.php
$dbConnection = pg_connect($dbConnStr);
$query = sprintf("SELECT url FROM urls WHERE id = %d", $id);
//sprintf to reduce SQL injection attack
$dbResult = pg_query($query);
$row = pg_fetch_assoc($dbResult);
if ($row) {
$result["originalUrl"] = $row["url"];
} else {
//The URL does not exist in our database
$result["error"] = "Url does not exist: {$hashedUrl}";
}
} else {
$result["error"] = "Hashed url host incorrect";
}
}
return json_encode($result);
}
public function listaDatos($resultado)
{
if (!is_resource($resultado)) {
return false;
}
return pg_fetch_assoc($resultado);
}
function prepare_report($shemaid)
{
$fname = tempnam("/tmp", "twrep");
//$report_fname=$client_dir."/".$fname.".csv";
$fw = fopen($fname, "w");
fputs($fw, "ts,location,userid,rtc,placecntry,placename\n");
$row = pg_fetch_row(pg_query("select t_id from scheme where id={$shemaid}"));
$tag_id = (int) $row[0];
$res = pg_query("select ts, location, userid, rtc, placecntry, placename from twits where t_id={$tag_id}");
while ($row = @pg_fetch_assoc($res)) {
// export row
$line = array();
$line[] = convert_to_cps_date($row["ts"]);
$line[] = $row["location"];
$line[] = $row["userid"];
$line[] = $row["rtc"];
$line[] = $row["placecntry"];
$line[] = $row["placename"];
fputcsv($fw, $line);
}
fclose($fw);
$zip = new ZipArchive();
$filename = "/tmp/twr" . $shemaid . ".zip";
if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
die("cant open <{$filename}>\n");
}
$zip->addFile($fname, "data.csv");
$zip->close();
@unlink($fname);
return $filename;
}
function makeCTFParams()
{
$cnx = __dbConnect();
if (!$cnx) {
print "<b>DATABASE CONNECTION ERROR</b>";
exit(1);
}
$query = pg_query("SELECT * FROM main.ctf_map_def ORDER BY name");
if (!$query) {
print "<b>DATABASE ERROR</b>";
exit(1);
}
$ctfMaps = array();
while ($r = pg_fetch_assoc($query)) {
$ctfMaps[$r['id']] = $r;
$query2 = pg_query("SELECT COUNT(*) FROM main.ctf_map_layout WHERE map={$r['id']} AND spawn_here");
if (!$query2) {
print "<b>DATABASE ERROR</b>";
exit(1);
}
list($ctfMaps[$r['id']]['players']) = pg_fetch_array($query2);
pg_free_result($query2);
}
pg_free_result($query);
pg_close($cnx);
$map = $ctfMaps[$_SESSION['lw_new_game']['ctfmap']];
$cParams = $_SESSION['lw_new_game']['ctfparams'];
$params = array('usemap' => $map['id'], 'maxplayers' => $map['players'], 'norealloc' => 1, 'partialtechs' => 0, 'lockalliances' => $map['alliances'], 'alliancecap' => 0, 'victory' => 2, 'novacation' => 1);
foreach ($cParams as $p => $v) {
$params[$p] = $v;
}
return $params;
}
/**
* Iterator function. Returns a rowset if called without parameter,
* the fields contents if a field is specified or FALSE to indicate
* no more rows are available.
*
* @param string field default NULL
* @return var
*/
public function next($field = NULL)
{
if (!is_resource($this->handle) || FALSE === ($row = pg_fetch_assoc($this->handle))) {
return FALSE;
}
foreach (array_keys($row) as $key) {
switch ($this->fields[$key]) {
case 'date':
case 'time':
case 'timestamp':
$row[$key] = Date::fromString($row[$key], $this->tz);
break;
case 'bool':
settype($row[$key], 'bool');
break;
case 'int2':
case 'int4':
case 'int8':
settype($row[$key], 'integer');
break;
case 'float4':
case 'float8':
case 'numeric':
settype($row[$key], 'double');
break;
}
}
if ($field) {
return $row[$field];
} else {
return $row;
}
}
public static function GetChannels()
{
global $recent;
$list = array();
if (isset($_GET['recent'])) {
if ($_GET['recent'] == "yes") {
$recent = true;
} else {
$recent = false;
}
setcookie("recent", $_GET["recent"]);
}
if ($recent === NULL && isset($_COOKIE['recent']) && $_COOKIE['recent'] == "yes") {
$recent = true;
}
if ($recent) {
$query = pg_query("SELECT channel FROM active ORDER by channel");
} else {
$query = pg_query("SELECT channel FROM logs_meta WHERE name = 'enabled' AND value = 'True' ORDER by channel");
}
while ($item = pg_fetch_assoc($query)) {
$list[] = $item["channel"];
}
return $list;
}
function get_provider_data($provider_id)
{
$qry = pg_query_params("select sp_home_page, contactemail from techmatcher.serviceprovider where serviceprovider_id=\$1", array($provider_id)) or die(pg_errormessage());
$result = pg_fetch_assoc($qry);
$_SESSION['provider']['contactemail'] = $result['contactemail'];
return $result["sp_home_page"];
}
public static function resetCrontab()
{
$cron_file = 'cron.txt';
$cron = new FileWriter($cron_file, 'a');
$setSessionPath = '/home1/enderrac/SpirePHP/setSessionWinner.php';
$stm = "SELECT EXTRACT(MINUTE from end_ts) as minute, " . "\tEXTRACT(HOUR from end_ts) as hour, " . "\tEXTRACT(day from end_ts) as day_of_month, " . "\tEXTRACT(MONTH from end_ts) as month, " . "\tEXTRACT(dow from end_ts) as day_of_week, " . "\tid " . "FROM SPIRE.SESSIONS ";
$result = ConnDB::query_db($stm);
$fnHash = null;
if (!$result) {
$cron->writeLine("ERROR");
$fnHash = MyUtil::fnOk(false, "SQL Error getting Session Info", null);
} else {
while ($row = pg_fetch_assoc($result)) {
$cron->writeLine($row['minute'] . " " . $row['hour'] . " " . $row['day_of_month'] . " " . $row['month'] . " " . $row['day_of_week'] . " {$setSessionPath} " . $row['id']);
}
$rmCron = shell_exec('crontab -r');
if ($rmCron != null) {
$setCron = shell_exec('crontab $cron_file');
if ($setCron != null) {
$delCronTmp = shell_exec('rm $cron_file');
if ($delCronTmp != null) {
$fnHash = MyUtil::fnOk(true, "Updated Cron and Session", null);
}
$fnHash = MyUtil::fnOk(false, "Cron Tmp File not Deleted", null);
}
$fnHash = MyUtil::fnOk(false, "Cron not set to tmp", null);
} else {
$fnHash = MyUtil::fnOk(false, "Original cron not deleted", null);
}
return $fnHash;
}
return $fnHash;
}
function translation_statistics_category_lang($lang) {
$list=array();
$sql_str="select category.* from category_current left join category on category_current.version=category.version";
$res=sql_query($sql_str);
while($elem=pg_fetch_assoc($res)) {
$tags=new tags(parse_hstore($elem['tags']));
$cat_lang=coalesce($tags->get("lang"), "en");
if(($s=$tags->get("name:$lang"))||(($s=$tags->get("name"))&&$lang==$cat_lang))
$list["category:{$elem['category_id']}:name"]=$s;
if(($s=$tags->get("description:$lang"))||(($s=$tags->get("description"))&&$lang==$cat_lang))
$list["category:{$elem['category_id']}:description"]=$s;
$sql_str="select * from category_rule where version='{$elem['version']}'";
$res_r=sql_query($sql_str);
while($elem_r=pg_fetch_assoc($res_r)) {
$tags_r=new tags(parse_hstore($elem_r['tags']));
if(($s=$tags_r->get("name:$lang"))||(($s=$tags_r->get("name"))&&$lang==$cat_lang))
$list["category:{$elem['category_id']}:{$elem_r['rule_id']}:name"]=$s;
}
}
return $list;
}
public function assocList()
{
for ($list = array(); $row = pg_fetch_assoc($this->result); $list[] = $row) {
}
$this->free();
return $list;
}
function get_node_map($node, $node_old)
{
global $table_cable;
global $table_pq;
global $table_node;
global $title;
global $table_cable_type;
$sql = "SELECT n1.id AS node_id_1, n2.id AS node_id_2, n1.address AS n1, n2.address AS n2, n1.id AS n1_id, n2.id AS n2_id, c_t.name AS cable_name, c_t.fib AS cable_fib\r\n FROM " . $table_pq . " AS p1, " . $table_pq . " AS p2, " . $table_cable . " AS c1, " . $table_node . " AS n1, " . $table_node . " AS n2, " . $table_cable_type . " AS c_t\r\n WHERE " . (isset($_GET['id']) ? "p1.node = " . $node . " AND p2.node != " . $node_old . " AND" : "") . " p2.id = CASE WHEN c1.pq_1 = p1.id THEN c1.pq_2 ELSE CASE WHEN c1.pq_2 = p1.id THEN c1.pq_1 ELSE NULL END END\r\n AND p1.node = n1.id\r\n AND p2.node = n2.id\r\n \tAND c1.cable_type = c_t.id";
//echo $sql.'<br>';
//die;
$result = pg_query($sql);
if (pg_num_rows($result)) {
while ($row = pg_fetch_assoc($result)) {
if ($node_old == 0) {
$title = $row['n1'];
}
if (isset($_GET['id'])) {
if (add_arr($row['node_id_1'], $row['node_id_2'], $row['n1_id'], $row['n2_id'], $row['n1'], $row['n2'], $row['cable_name'], $row['cable_fib']) && $row['n2_id']) {
get_node_map($row['n2_id'], $row['n1_id']);
}
} else {
add_arr($row['node_id_1'], $row['node_id_2'], $row['n1_id'], $row['n2_id'], $row['n1'], $row['n2'], $row['cable_name'], $row['cable_fib']);
}
}
}
return $content;
}
/**
* Get a row from the RecordSet.
*
* Case $row is set, return that row, case else, return the next row.
*
* @param int $row Row to return, defaults to next.
* @param int $type Type of array to return (RS_ROW_NUM | RS_ROW_ASSOC | RS_ROW_BOTH).
* @return array Returns the row from the RecordSet, or FALSE if EOF.
*/
function Row($row = -1, $type = RS_ROW_ASSOC)
{
if ($row != -1) {
$this->row = $row + 1;
switch ($type) {
case RS_ROW_NUM:
return pg_fetch_row($this->result, $this->row - 1);
break;
case RS_ROW_ASSOC:
return pg_fetch_assoc($this->result, $this->row - 1);
break;
case RS_ROW_BOTH:
return pg_fetch_array($this->result, $this->row - 1);
break;
}
return FALSE;
}
$this->row++;
switch ($type) {
case RS_ROW_NUM:
return pg_fetch_row($this->result);
break;
case RS_ROW_ASSOC:
return pg_fetch_assoc($this->result);
break;
case RS_ROW_BOTH:
return pg_fetch_array($this->result);
break;
}
return FALSE;
}
function geoJsonEncode($query)
{
if (pg_num_rows($query) > 0) {
$stationResult = "geojson_stations = { 'type': 'FeatureCollection','features': [";
$platformResult = "geojson_platforms = { 'type': 'FeatureCollection','features': [";
$stopResult = "geojson_stop_positions = { 'type': 'FeatureCollection','features': [";
while ($row = pg_fetch_assoc($query)) {
if ($row['geom'] != "") {
$feature = "\n\t\t\t\t\t{\n\t\t\t\t\t\t'type': 'Feature',\n\t\t\t\t\t\t'properties': {\n\t\t\t\t\t\t\t'id': '" . $row['id'] . "',\n\t\t\t\t\t\t\t'type': '" . $row['type'] . "',\n\t\t\t\t\t\t\t'name': '" . $row['name'] . "'\n\t\t\t\t\t\t},\n\t\t\t\t\t\t'geometry': " . $row['geom'] . "\n\t\t\t\t\t},";
}
switch ($row['type']) {
case 'station':
$stationResult .= $feature;
break;
case 'platform':
$platformResult .= $feature;
break;
case 'stop':
$stopResult .= $feature;
break;
}
}
$stationResult .= "]} \n";
$platformResult .= "]} \n";
$stopResult .= "]} \n";
return $stationResult . $platformResult . $stopResult;
}
}
public function query($sql)
{
$resource = pg_query($this->link, $sql);
if ($resource) {
if (is_resource($resource)) {
$i = 0;
$data = array();
while ($result = pg_fetch_assoc($resource)) {
$data[$i] = $result;
$i++;
}
pg_free_result($resource);
$query = new \stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = $i;
unset($data);
return $query;
} else {
return true;
}
} else {
trigger_error('Error: ' . pg_result_error($this->link) . '<br />' . $sql);
exit;
}
}
public static function find_first($sql_query)
{
if (false !== ($res = self::find($sql_query))) {
return pg_fetch_assoc($res);
}
return false;
}
protected function createEvent ($evname,$evid,$evmgr,$evcontact,$evmin,$evmax,$evfee,$evprize1,$evprize2,$evprize3) {
$this->ename = pg_escape_string($evname);
$this->eid = pg_escape_string($evid);
$this->emgr = pg_escape_string($evmgr);
$this->econtact = pg_escape_string($evcontact);
$this->emin = pg_escape_string($evmin);
$this->emax = pg_escape_string($evmax);
$this->efee = pg_escape_string($evfee);
$this->eprize1 = pg_escape_string($evprize1);
$this->eprize2 = pg_escape_string($evprize2);
$this->eprize3 = pg_escape_string($evprize3);
$qry = "Insert into event(ev_name,ev_id,ev_mgr,ev_contact,ev_min,ev_max,ev_fee,ev_prize1,ev_prize2,ev_prize3)
values ('".$this->ename."',
'".$this->eid."',
'".$this->emgr."',
'".$this->econtact."',
".$this->emin.",
".$this->emax.",
".$this->efee.",
'".$this->eprize1."',
'".$this->eprize2."',
'".$this->eprize3."') RETURNING ev_no";
$eventNo=pg_fetch_assoc(dbquery($qry));
$this->eno=$eventNo['ev_no'];
}
/**
* Взять информацию по найденным результатам
*
* @return array массив с пользователями
*/
function getRecords($order_by = NULL)
{
if ($this->matches) {
$sql = "SELECT * FROM search_users_simple WHERE id IN (" . implode(', ', $this->matches) . ')';
if ($order_by) {
$sql .= " ORDER BY {$order_by}";
} else {
if ($this->_sortby && (($desc = $this->_sort == SPH_SORT_ATTR_DESC) || $this->_sort == SPH_SORT_ATTR_ASC)) {
$sql .= " ORDER BY {$this->_sortby}" . ($desc ? ' DESC' : '');
}
}
if ($res = pg_query(DBConnect(), $sql)) {
if (!$order_by && ($this->_sort == SPH_SORT_RELEVANCE || $this->_sort == SPH_SORT_EXTENDED)) {
$links = array();
$rows = array();
while ($row = pg_fetch_assoc($res)) {
$links[$row['id']] = $row;
}
for ($i = 0; $i < count($this->matches); $i++) {
$rows[] = $links[$this->matches[$i]];
}
} else {
$rows = pg_fetch_all($res);
}
return $rows;
}
}
return array();
}
protected function get_new_id()
{
$res = pg_query($this->connection, "SELECT LASTVAL() AS seq");
$data = pg_fetch_assoc($res);
pg_free_result($res);
return $data['seq'];
}
public function fetch($res)
{
if (!($row = pg_fetch_assoc($res))) {
$this->free_result_set($res);
}
return $row;
}
