/**
* Connect to DB with jointable, return connection handler
*/
function dbConnect($dsn)
{
$dbh = DB::connect($dsn);
if (DB::isError($dbh)) {
#die ($dbh->getMessage());
PMCommon::db_logErrors($dbh);
return NULL;
} else {
return $dbh;
}
}
/**
* DRAW XY LAYER: ADD POINTS FROM DB TO MAP IMAGE
*/
protected function pmap_drawXYLayer($img)
{
$grouplist = $_SESSION["grouplist"];
// Loop through groups and layers to find XYLayers
foreach ($grouplist as $grp) {
$glayerList = $grp->getLayers();
foreach ($glayerList as $glayer) {
$XYLayerProperties = $glayer->getXYLayerProperties();
if ($XYLayerProperties) {
// XYLayer found, now process
$mLayer = $this->map->getLayer($glayer->getLayerIdx());
$mLayerStatus = $mLayer->status;
if ($mLayerStatus == MS_ON) {
// Check if XYLayer is switched ON
$pearDbClass = $_SESSION['pearDbClass'];
require_once "{$pearDbClass}.php";
require_once "../query/query.php";
// Check if Layer projection differs from Map projection
$changeLayProj = PMCommon::checkProjection($this->map, $mLayer);
// Map extent for limiting query
if ($changeLayProj) {
$me = $this->map->extent;
$mapExt = ms_newRectObj();
$mapExt->setExtent($me->minx, $me->miny, $me->maxx, $me->maxy);
$mapExt->project($changeLayProj['mapProj'], $changeLayProj['layProj']);
} else {
$mapExt = $this->map->extent;
}
$map_xmin = $mapExt->minx;
$map_ymin = $mapExt->miny;
$map_xmax = $mapExt->maxx;
$map_ymax = $mapExt->maxy;
if ($changeLayProj) {
PMCommon::freeMsObj($mapExt);
}
// XY Layer Properties
$dsn = $XYLayerProperties["dsn"];
$xyTable = $XYLayerProperties["xyTable"];
$x_fld = $XYLayerProperties["x_fld"];
$y_fld = $XYLayerProperties["y_fld"];
$classidx_fld = $XYLayerProperties["classidx_fld"];
$resFldStr = $mLayer->getMetaData("RESULT_FIELDS");
$resFieldList = preg_split('/[\\s,]+/', $resFldStr);
// Prepare query
if (preg_match("/@/", $xyTable)) {
// Check for WHERE filter in table definition
$xyList = preg_split('/@/', $xyTable);
$whereFilter = $xyList[1];
$xyTable = $xyList[0];
}
$sql_SELECT = "SELECT {$x_fld}, {$y_fld}, {$classidx_fld} FROM {$xyTable} ";
$sql_WHERE = "WHERE " . ($whereFilter ? $whereFilter . " AND " : "") . " {$x_fld} >= {$map_xmin} AND {$x_fld} <= {$map_xmax} AND {$y_fld} >= {$map_ymin} AND {$y_fld} <= {$map_ymax}";
$sql = "{$sql_SELECT} {$sql_WHERE}";
pm_logDebug(3, $sql, "P.MAPPER-DEBUG: map.php/pmap_drawXYLayer() - SQL Cmd:");
// init DB class
$db = new $pearDbClass();
// Connect to DB
$dbh = $db->connect($dsn);
if ($db->isError($dbh)) {
PMCommon::db_logErrors($dbh);
die;
}
// Execute query
$res = $dbh->query($sql);
if ($db->isError($res)) {
PMCommon::db_logErrors($res);
die;
}
// Draw points on map image
while ($xyRow = $res->fetchRow()) {
$ptX = $xyRow[0];
$ptY = $xyRow[1];
$clIdx = $xyRow[2];
$xyPoint = ms_newPointObj();
$xyPoint->setXY($ptX, $ptY);
$xyPoint->draw($this->map, $mLayer, $img, $clIdx, "");
PMCommon::freeMsObj($xyPoint);
}
//$res->free();
$dbh->disconnect();
}
}
}
}
}
function dumpXYQueryResults()
{
$pearDbClass = $_SESSION['pearDbClass'];
require_once "{$pearDbClass}.php";
$eqr = $_SESSION["equeryRect"];
// XY Layer Properties
$XYLayerProperties = $this->glayer->getXYLayerProperties();
if ($XYLayerProperties['noQuery']) {
return false;
}
$dsn = $XYLayerProperties["dsn"];
$xyTable = $XYLayerProperties["xyTable"];
$x_fld = $XYLayerProperties["x_fld"];
$y_fld = $XYLayerProperties["y_fld"];
$classidx_fld = $XYLayerProperties["classidx_fld"];
$oid_fld = $XYLayerProperties["oid_fld"];
$resFieldList = $this->glayer->getResFields();
$resFldStr = join(',', $resFieldList);
// Prepare SQL query
if (preg_match("/@/", $xyTable)) {
// Check for WHERE filter in table definition
$xyList = preg_split('/@/', $xyTable);
$whereFilter = $xyList[1];
$xyTable = $xyList[0];
}
$sql_select = "SELECT {$x_fld}, {$y_fld}, {$resFldStr} FROM {$xyTable}";
$qr = $this->xyLayQueryList;
$changeLayProj = PMCommon::checkProjection($this->map, $this->qLayer);
// Map extent for limiting query
if ($changeLayProj) {
$mapExt = ms_newRectObj();
$mapExt->setExtent($qr["xmin"], $qr["ymin"], $qr["xmax"], $qr["ymax"]);
$mapExt->project($changeLayProj['mapProj'], $changeLayProj['layProj']);
$qxmin = $mapExt->minx;
$qymin = $mapExt->miny;
$qxmax = $mapExt->maxx;
$qymax = $mapExt->maxy;
} else {
$qxmin = $qr["xmin"];
$qymin = $qr["ymin"];
$qxmax = $qr["xmax"];
$qymax = $qr["ymax"];
}
if ($this->search) {
$sql_where = "WHERE " . ($whereFilter ? $whereFilter . " AND " : "") . $qr;
} else {
$sql_where = "WHERE " . ($whereFilter ? $whereFilter . " AND " : "") . " {$x_fld} >= {$qxmin} AND {$x_fld} <= {$qxmax} AND {$y_fld} >= {$qymin} AND {$y_fld} <= {$qymax} ";
}
$sql = "{$sql_select} {$sql_where}";
pm_logDebug(3, $query, "P.MAPPER-DEBUG: squery.php/dumpXYQueryResults() - SQL Cmd:");
// init DB class
$db = new $pearDbClass();
// Connect to DB
$dbh = $db->connect($dsn);
if ($db->isError($dbh)) {
PMCommon::db_logErrors($dbh);
die;
}
// Execute query
$res = $dbh->query($sql);
if ($db->isError($res)) {
PMCommon::db_logErrors($res);
die;
}
$this->mExtMinx = 999999999;
$this->mExtMiny = 999999999;
$this->mExtMaxx = -999999999;
$this->mExtMaxy = -999999999;
// Now print results as JSON
$nres = 0;
$numrows = $res->numRows();
while ($row = $res->fetchRow(2)) {
if ($changeLayProj) {
$nP = ms_newpointobj();
$nP->setXY($row["{$x_fld}"], $row["{$y_fld}"]);
$nP->project($changeLayProj['layProj'], $changeLayProj['mapProj']);
$rx = $nP->x;
$ry = $nP->y;
} else {
$rx = $row["{$x_fld}"];
$ry = $row["{$y_fld}"];
}
$buf = $this->pointBuffer;
//error_log($buf);
$shpMinx = $rx - $buf;
$shpMiny = $ry - $buf;
$shpMaxx = $rx + $buf;
$shpMaxy = $ry + $buf;
if ($this->zoomFull) {
$this->mExtMinx = min($this->mExtMinx, $shpMinx);
$this->mExtMiny = min($this->mExtMiny, $shpMiny);
$this->mExtMaxx = max($this->mExtMaxx, $shpMaxx);
$this->mExtMaxy = max($this->mExtMaxy, $shpMaxy);
}
// Link for zoom to feature
$qShpIdx = $row["{$oid_fld}"];
$this->resultindexes[] = $row["{$x_fld}"] . "@" . $row["{$y_fld}"];
// Output JSON
//$qShpLink = "{\"shplink\": [\"0\",\"0\",\"" . $shpMinx ."+". $shpMiny ."+". $shpMaxx ."+". $shpMaxy ."\,0"]}";
$qShpLink = "{\"shplink\": [\"" . $this->qLayerName . "\",\"" . $qShpIdx . "\",\"" . $shpMinx . "+" . $shpMiny . "+" . $shpMaxx . "+" . $shpMaxy . "\"" . ($this->zoomFull ? ",1" : ",0") . "]}";
if ($nres > 0) {
$this->qStr .= ", ";
}
// Add shape link
$this->qStr .= "[" . $qShpLink;
// Add 'normal' field values
foreach ($resFieldList as $fn) {
$this->qStr .= $this->printFieldValues($fn, $row["{$fn}"]);
}
$this->qStr .= "]";
$nres++;
// Stop query if result records exceed limit set in config.ini
if ($nres > $this->limitResult) {
break;
}
}
$this->numResults = $nres;
$res->free();
$dbh->disconnect();
}
/**
* Get the option values from a DBMS via PEAR
*/
protected function getOptionsFromDb($dsn, $sql, $encoding)
{
pm_logDebug(3, $sql, "search.php->getOptionsFromDb()");
// Load PEAR class
$pearDbClass = $_SESSION['pearDbClass'];
require_once "{$pearDbClass}.php";
// Query DB
$options = array('persistent' => 0);
if ($pearDbClass == "DB") {
$options['portability'] = DB_PORTABILITY_ALL;
}
// init DB class
$db = new $pearDbClass();
// Connect to DB
$dbh = $db->connect($dsn);
if ($db->isError($dbh)) {
PMCommon::db_logErrors($dbh);
die;
}
// Execute query
$res = $dbh->query($sql);
if ($db->isError($res)) {
PMCommon::db_logErrors($res);
die;
}
// Create output JSON
$json = "{";
$rc = 0;
while ($row = $res->fetchRow()) {
$sep = $rc > 0 ? "," : "";
$k = $encoding != "UTF-8" ? iconv($encoding, "UTF-8", $row[0]) : $row[0];
$v = $encoding != "UTF-8" ? iconv($encoding, "UTF-8", $row[1]) : $row[1];
// avoid <, >, ...
$v = htmlentities($v, ENT_COMPAT, 'UTF-8');
$json .= "{$sep} \"{$k}\":" . "\"" . addslashes(trim($v)) . "\"";
$rc++;
}
$json .= "}";
//error_log($json);
pm_logDebug(3, $json, "search.php->getOptionsFromDb()->json");
return $json;
}
/**
* Get Suggest records from a database
*
* @return string
*/
private function dbSuggestMatch($searchParams)
{
$dsn = $searchParams['dsn'];
$encoding = $searchParams['encoding'];
// many dependfields
//$search = ($encoding != "UTF-8" ? iconv("UTF-8", $encoding, $this->search) : $this->search);
$search = $this->search;
$dependFields = isset($searchParams['dependFields']) && $searchParams['dependFields'] ? $searchParams['dependFields'] : array();
$sql = str_replace('[search]', $search, $searchParams['sql']);
foreach ($dependFields as $fieldName => $fieldValue) {
$sql = str_replace(array('[dependfldval]', "[dependfldval_{$fieldName}]"), trim($fieldValue), $sql);
}
// Perform requests in case of dependency defined
if ($dependFields) {
$sql = str_replace(array("{", "}"), "", $sql);
} else {
// remove {...} part if exists in SQL string
$sql = preg_replace("/(\\{)([\\w\\d\\s\\='%_\\(\\)\\*~])+(\\})/", "", $sql);
}
pm_logDebug(3, $sql);
// Load PEAR class
$pearDbClass = $_SESSION['pearDbClass'];
require_once "{$pearDbClass}.php";
// init DB class
$db = new $pearDbClass();
// Connect to DB
$dbh = $db->connect($dsn);
if ($db->isError($dbh)) {
PMCommon::db_logErrors($dbh);
die;
}
// Execute query
$res = $dbh->query($sql);
if ($db->isError($res)) {
PMCommon::db_logErrors($res);
die;
}
$ret = '';
if ($res->numRows() > 0) {
$rc = 0;
while ($row = $res->fetchRow()) {
//$ret .= $row[0] . "\n";
$sep = $rc > 0 ? $this->resdelimiter : "";
$suggest = $encoding != "UTF-8" ? iconv($encoding, "UTF-8", $row[0]) : $row[0];
//$ret .= "$sep" . addslashes(str_replace("\n", "", $suggest)) ;
$ret .= "{$sep}" . str_replace("\n", "", $suggest);
$rc++;
}
} else {
$ret = 0;
}
return $ret;
}
