--- android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/09/01 19:17:18	292
+++ android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/09/14 07:29:59	322
@@ -26,16 +26,29 @@
         super();
         // TODO Auto-generated constructor stub
     }
+    
 
-	protected String getStations(Connection conn, double latitude, double longitude) throws SQLException {
+
+	protected String getStations(Connection conn, double latitude, double longitude, String name) throws SQLException {
+		String SQL = "";
+		
+		if (latitude >= 0.0 && longitude >= 0.0)
+		{
 		//inner select is workaround from not being able to use a calculated column directly in where clause
-		final String SQL = "SELECT * FROM ( "+
-						   "               SELECT name,latitude,longitude,address,stationcode, " + 
+			SQL = "SELECT * FROM ( "+
+						   "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " + 
 						   "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
 						   "               FROM trainstations " +
+						   "               WHERE latitude IS NOT NULL AND longitude IS NOT NULL " +
 						   "       ) AS trainstations2 " +
 					       "ORDER BY calcdist ASC " + 
 					       "LIMIT 4 ";
+		} else if (name != null) {
+			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " + 
+				  "FROM trainstations " + 
+				  "WHERE name ILIKE ? AND latitude IS NOT NULL AND longitude IS NOT NULL " +
+				  "ORDER BY name ";
+		} else throw new SQLException("not enough parameters");
 		
 		System.out.println(SQL);
 		
@@ -49,20 +62,28 @@
 		try
 		{
 			stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
+			if (latitude >= 0 && longitude >= 0) {
+				stmt.setDouble(1, latitude);
+				stmt.setDouble(2, longitude);
+			}
+			if (name != null) {
+				stmt.setString(1, "%" + name + "%");
+			}
 			
 			res = stmt.executeQuery();
 			
 			while (res.next()) {
 				buff.append("<station>\n");
 
-				buff.append("<name>").append( res.getString(1) ) .append("</name>\n");
-				buff.append("<latitude>").append( res.getDouble(2) ) .append("</latitude>\n");
-				buff.append("<longitude>").append( res.getDouble(3) ) .append("</longitude>\n");				
-				buff.append("<address>").append( res.getString(4) ) .append("</address>\n");	
-				buff.append("<stationcode>").append( res.getString(5) ) .append("</stationcode>\n");
-				buff.append("<calcdist>").append( res.getDouble(6) ) .append("</calcdist>\n");
+				buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
+				buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
+				buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
+				buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
+				res.getString(5);
+				buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
+				res.getString(6);
+				buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
+				buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
 				
 				buff.append("</station>\n");				
 			}
@@ -78,14 +99,21 @@
 
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 		
-		double latitude = Double.parseDouble( request.getParameter("latitude") );
-		double longitude = Double.parseDouble( request.getParameter("longitude") );
+		double latitude = -1.0;
+		if (request.getParameter("latitude") != null)
+			latitude = Double.parseDouble( request.getParameter("latitude") );
+		
+		double longitude = -1.0;
+		if (request.getParameter("latitude") != null)
+			longitude = Double.parseDouble( request.getParameter("longitude") );
+		
+		String name = request.getParameter("name");
 				
 		Connection conn = null;
 		try {
 			conn = DBConnection.getConnection();
 			
-			String xml = getStations(conn, latitude, longitude);
+			String xml = getStations(conn, latitude, longitude, name);
 			
 			response.setContentType("text/xml");
 			response.getWriter().print(xml);