. // // Authors: Niccolo Rigacci // Thomas Raffin //------------------------------------------------------------------------- // prevent XSS if (isset($_REQUEST['_SESSION'])) exit(); session_start(); // If plugin is not activated, do not execute. if (!isset($_SESSION['digitizepoints_activated']) or !$_SESSION['digitizepoints_activated']) { exit(); } require_once($_SESSION['PM_INCPHP'] . '/common.phtml'); require_once($_SESSION['PM_INCPHP'] . '/globals.phtml'); require_once('DB.phtml'); require_once('include.phtml'); require_once('include_conf.phtml'); // TODO: // * Now the pkey must be numeric, add proper escaping if needed. // * Trap errors on fetchRow, when getting lon/lat of existing point. $distance = 'st_distance'; // Name for 'SELECT ... AS', must not conflict with other table fields. $prefix = '__db'; // Prefix for database fields used into the html form. print "\n"; print "\n"; print "\n"; print "\n"; $lon = (float)$_REQUEST['lon']; $lat = (float)$_REQUEST['lat']; //------------------------------------------------------------------------ // Connect to the database. //------------------------------------------------------------------------ $db = DB::connect($dsn, true); if (DB::isError($db)) die ($db->getMessage()); //------------------------------------------------------------------------ // What to do? //------------------------------------------------------------------------ $action = isset($_REQUEST['__action']) ? $_REQUEST['__action'] : false; switch($action) { //-------------------------------------------------------------------- // Do insert. //-------------------------------------------------------------------- case 'insert': // Get all the fields from the web form. list($columns, $values) = get_columns_and_values($_REQUEST, $prefix, $db); // Add the geometry. $val = sprintf('ST_SetSRID(ST_MakePoint(%f, %f), %d)', $lon, $lat, $srid_map); if ($srid_geom != $srid_map) $val = "ST_Transform($val, $srid_geom)"; array_push($columns, $the_geom); array_push($values, $val); // Make the SQL statement. $sql = 'INSERT INTO ' . $db_table . ' (' . implode(', ', $columns) . ')'; $sql .= ' VALUES (' . implode(', ', $values) . ')'; $result = $db->query($sql); if (DB::isError($result)) { print "Error executing statement: " . my_html($sql) ."

\n"; die ($result->getMessage()); } else { msg_and_close(_p('Insert successful.')); } break; //-------------------------------------------------------------------- // Do update. //-------------------------------------------------------------------- case 'update': // Get the record ID. $id = (int)$_REQUEST['__id']; // Get all the fields from the web form. list($columns, $values) = get_columns_and_values($_REQUEST, $prefix, $db); // Add the geometry. $val = sprintf('ST_SetSRID(ST_MakePoint(%f, %f), %d)', $lon, $lat, $srid_map); if ($srid_geom != $srid_map) $val = "ST_Transform($val, $srid_geom)"; array_push($columns, $the_geom); array_push($values, $val); // Make the SQL statement. $sql = 'UPDATE ' . $db_table . ' SET (' . implode(', ', $columns) . ')'; $sql .= ' = (' . implode(', ', $values) . ')'; $sql .= ' WHERE ' . $pkey . ' = ' . $id; $result = $db->query($sql); if (DB::isError($result)) { print "Error executing statement: " . my_html($sql) ."

\n"; die ($result->getMessage()); } else { msg_and_close(_p('Update successful.')); } break; //-------------------------------------------------------------------- // Do delete. //-------------------------------------------------------------------- case 'delete': // Get the record ID. $id = (int)$_REQUEST['__id']; // Make the SQL statement. $sql = 'DELETE FROM ' . $db_table . ' WHERE ' . $pkey . ' = ' . $id; $result = $db->query($sql); if (DB::isError($result)) { print "Error executing statement: " . my_html($sql) ."

\n"; die ($result->getMessage()); } else { msg_and_close(_p('Delete successful.')); } break; //-------------------------------------------------------------------- // Get points near the clik and get table info. //-------------------------------------------------------------------- default: $point = sprintf("ST_PointFromText('POINT(%f %f)', %d)", $lon, $lat, $srid_map); $geom_ll = $the_geom; // Function ST_Distance_Sphere() requires EPSG:4326 lon/lat points. if ($srid_map != 4326) $point = "ST_Transform($point, 4326)"; if ($srid_geom != 4326) $geom_ll = "ST_Transform($the_geom, 4326)"; $sql = 'SELECT *, ST_Distance_Sphere(%s, %s) AS %s'; $sql .= ' FROM %s WHERE ST_Distance_Sphere(%s, %s) < %f'; $sql .= ' ORDER BY %s'; $sql = sprintf($sql, $geom_ll, $point, $distance, $db_table, $geom_ll, $point, $tolerance, $distance); print '\n"; $result = $db->query($sql); if (DB::isError($result)) die ($result->getMessage()); $tableinfo = $result->tableInfo(); // If there is a near point, we will do an update. if (!isset($_REQUEST['addnew']) and $result->numRows() > 0) { $record = $result->fetchRow(DB_FETCHMODE_ASSOC); $point = $the_geom; if ($srid_geom != $srid_map) $point = "ST_Transform($point, $srid_map)"; $sql = 'SELECT ST_X(%s), ST_Y(%s) FROM %s WHERE %s = %s'; $sql = sprintf($sql, $point, $point, $db_table, $pkey, $record[$pkey]); list($point_lon, $point_lat) = $db->query($sql)->fetchRow(DB_FETCHMODE_ORDERED); $new_record = false; $action = 'update'; $id = $record[$pkey]; } else { list($point_lon, $point_lat) = array($lon, $lat); $new_record = true; $action = 'insert'; $id = ''; } //------------------------------------------------------------------------ // Display the insert/update form. //------------------------------------------------------------------------ $html = ''; $heading = ($new_record) ? _p('Insert new point') : _p('Update point'); $html .= '

' . $heading . "

\n"; $html .= '

' . "\n"; $html .= '\n"; $html .= '\n"; $html .= '\n"; $html .= '\n"; // Display the form for record insert/update. $html .= "\n"; foreach ($tableinfo as $f) { if (in_array($f['name'], $hide_fields)) continue; if ($f['name'] == $the_geom) continue; if ($f['name'] == $distance) continue; $align = numeric_type($f['type']) ? 'right' : 'left'; $type = numeric_type($f['type']) ? 'n': 'c'; $html .= '\n"; } // Input fields for longitude and latitude. $html .= '\n"; $html .= '\n"; $addnew_url = sprintf('?addnew=yes&lon=%f&lat=%f', $lon, $lat); $delete_url = sprintf('?__action=delete&__id=%d', my_html($id)); $disabled = $new_record ? 'disabled' : ''; $html .= "\n"; $html .= "

' . my_html($f['name']) . "	"; if ($new_record) { $value = ''; } else { $value = $record[$f['name']]; } $html_name = $prefix . '_' . $type . '_' . $f['name']; $html .= sprintf('', my_html($html_name), my_html($value)); $html .= "
' . _p('Longitude') . '	'; $html .= sprintf('', my_html($point_lon)); $html .= "
' . _p('Latitude') . '	'; $html .= sprintf('', my_html($point_lat)); $html .= "
	\n"; $html .= '\n"; $html .= '\n"; $html .= " \n"; $html .= '\n"; $html .= '\n"; $html .= "

\n"; $html .= "

\n"; $html .= "\n"; $html .= "\n"; echo $html; break; } $db->disconnect();