/** * 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; }