--- android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/09/07 12:23:35	301
+++ android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/10/02 15:11:25	388
@@ -5,6 +5,8 @@
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
 
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServlet;
@@ -18,52 +20,85 @@
  */
 public class LocateStations extends HttpServlet {
 	private static final long serialVersionUID = 1L;
-       
-    /**
-     * @see HttpServlet#HttpServlet()
-     */
-    public LocateStations() {
-        super();
-        // TODO Auto-generated constructor stub
-    }
-
-	protected String getStations(Connection conn, double latitude, double longitude) throws SQLException {
-		//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,stationcode, " + 
-						   "                     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 ";
-		
-		System.out.println(SQL);
-		
+
+	Logger logger = Logger.getLogger( LocateStations.class.toString() );
+	
+	/**
+	 * @see HttpServlet#HttpServlet()
+	 */
+	public LocateStations() {
+		super();
+		// TODO Auto-generated constructor stub
+	}
+
+	public enum Requested {
+		BY_NAME,
+		BY_LOCATION,
+		NONE
+	}
+
+
+	protected String getStations(Connection conn, double latitude, double longitude, String name, Requested method) throws SQLException {
+		String SQL = "";
 		PreparedStatement stmt = null;
 		ResultSet res = null;
-		
+
 		StringBuffer buff = new StringBuffer();
-		
+
 		buff.append("<?xml version=\"1.0\" ?>\n");
-		buff.append("<stations>\n");
+		buff.append("<stations>\n");	
+		
 		try
 		{
-			stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
-			
+
+			switch (method)
+			{
+			case BY_LOCATION:
+				//inner select is workaround from not being able to use a calculated column directly in where clause
+				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 enabled = true AND latitude IS NOT NULL AND longitude IS NOT NULL " +
+						"       ) AS trainstations2 " +
+						"ORDER BY calcdist ASC " + 
+						"LIMIT 4 ";
+				stmt = conn.prepareStatement(SQL);
+				stmt.setDouble(1, latitude);
+				stmt.setDouble(2, longitude);
+
+				break;
+			case BY_NAME:
+				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 ";
+				stmt = conn.prepareStatement(SQL);
+				stmt.setString(1, name + "%");
+				break;
+			default:
+				// This should not be possible
+				logger.severe("getStations(): default switch case");
+			}
+
+
+
+
 			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("<stationcode>").append( res.getString(4) ) .append("</stationcode>\n");
-				buff.append("<calcdist>").append( res.getInt(5) ) .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");				
 			}
 		} finally {
@@ -77,29 +112,50 @@
 	}
 
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
-		
-		double latitude = Double.parseDouble( request.getParameter("latitude") );
-		double longitude = Double.parseDouble( request.getParameter("longitude") );
-				
-		Connection conn = null;
-		try {
-			conn = DBConnection.getConnection();
-			
-			String xml = getStations(conn, latitude, longitude);
-			
-			response.setContentType("text/xml");
-			response.getWriter().print(xml);
-			
-			conn.close();
-			conn = null;
-			
-		} catch (Exception e) {
-			throw new ServletException(e);
-		} finally {
+
+		double longitude = 0.0;
+		double latitude = 0.0;
+		String name = "";
+
+		Requested method = Requested.NONE;
+
+		if (request.getParameter("latitude") != null && request.getParameter("latitude") != null) { 
+			latitude = Double.parseDouble( request.getParameter("latitude") );
+			longitude = Double.parseDouble( request.getParameter("longitude") );
+			method = Requested.BY_LOCATION;
+		}
+
+		if (request.getParameter("name") != null) {
+			name = request.getParameter("name").trim();
+			method = Requested.BY_NAME;
+		}
+
+
+		if (method != Requested.NONE) {
+
+			Connection conn = null;
 			try {
-				if (conn != null)
-					conn.close();
-			} catch (Throwable t) {}
+				conn = DBConnection.getConnection();
+
+				String xml = getStations(conn, latitude, longitude, name, method);
+
+				response.setContentType("text/xml");
+				response.getWriter().print(xml);
+
+				conn.close();
+				conn = null;
+ 
+			} catch (Exception e) {				
+				logger.log(Level.SEVERE, "Exception while finding stations", e);
+				response.sendError(500);						
+			} finally {
+				try {
+					if (conn != null)
+						conn.close();
+				} catch (Throwable t) {}
+			}
+		} else {
+			response.sendError(400, "not enough parameters");
 		}
 	}