--- android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/09/24 21:04:34	344
+++ android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2010/01/26 20:18:57	549
@@ -6,71 +6,94 @@
 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;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
-import dk.thoerup.traininfoservice.DBConnection;
-
 /**
  * Servlet implementation class LocateStations
  */
 public class LocateStations extends HttpServlet {
 	private static final long serialVersionUID = 1L;
-       
-    /**
-     * @see HttpServlet#HttpServlet()
-     */
-    public LocateStations() {
-        super();
-        // TODO Auto-generated constructor stub
-    }
-    
 
+	Logger logger = Logger.getLogger( LocateStations.class.toString() );
+
+
+	
+	protected String transformToIntList(String input) {		
+		String strings[] = input.split(",");
+		
+		StringBuffer sb = new StringBuffer();
+		sb.append("(");
+		for (int i = 0; i<strings.length; i++) {
+			if (i>0) {
+				sb.append(",");
+			}
+			sb.append( Integer.parseInt(strings[i])); //by doing the integer conversion we ensure that it really is a integer
+		}
+		sb.append(")");
+		return sb.toString();		
+	}
+
+
+	protected PreparedStatement createStatement(Connection conn, HttpServletRequest req) 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
-			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");
-		
-		
 		PreparedStatement stmt = null;
+		String SQL;
+
+		if (req.getParameter("latitude") != null && req.getParameter("latitude") != null) {
+			//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, stationcode_metro, address," + 
+			"                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
+			"               FROM trainstations " +
+			"               WHERE enabled = true " +
+			"       ) AS trainstations2 " +
+			"ORDER BY calcdist ASC " + 
+			"LIMIT 4 ";
+			double latitude = Double.parseDouble( req.getParameter("latitude") );
+			double longitude = Double.parseDouble( req.getParameter("longitude") );
+			stmt = conn.prepareStatement(SQL);
+			stmt.setDouble(1, latitude);
+			stmt.setDouble(2, longitude);
+		} else if (req.getParameter("name") != null) {
+			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + 
+			"FROM trainstations " + 
+			"WHERE name ILIKE ? AND enabled = true " +
+			"ORDER BY name ";
+
+			String name = req.getParameter("name").trim();
+			stmt = conn.prepareStatement(SQL);
+			stmt.setString(1, name + "%");
+		} else if (req.getParameter("list") != null) {
+			String list = transformToIntList( req.getParameter("list"));
+			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + 
+			"FROM trainstations " + 
+			"WHERE id IN " + list + " AND enabled = true " +
+			"ORDER BY name ";
+			stmt = conn.prepareStatement(SQL);
+		} 
+
+		return stmt;
+	}
+
+
+	protected String formatResultset(PreparedStatement stmt) throws SQLException {
 		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);
-			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");
 
@@ -82,8 +105,13 @@
 				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");
+				res.getString(7);
+				buff.append("<metro>").append( !res.wasNull() ).append("</metro>\n");
 				
+				String address = res.getString(8);
+				buff.append("<address>").append( res.wasNull() ? "" : address ).append("</address>");				
+				buff.append("<calcdist>").append( res.getInt(9) ) .append("</calcdist>\n");
+
 				buff.append("</station>\n");				
 			}
 		} finally {
@@ -96,37 +124,33 @@
 		return buff.toString();
 	}
 
+	@Override
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
-		
-		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, name);
-			
-			response.setContentType("text/xml");
-			response.getWriter().print(xml);
-			
-			conn.close();
-			conn = null;
-			
-		} catch (Exception e) {
-			throw new ServletException(e);
+
+			PreparedStatement stmt = createStatement(conn, request);
+			if (stmt != null){
+				String xml = formatResultset(stmt);
+
+				response.setContentType("text/xml");
+				response.getWriter().print(xml);
+			} else {
+				response.sendError(400, "not enough parameters");		
+			}
+
+
+		} catch (Exception e) {				
+			logger.log(Level.SEVERE, "Exception while finding stations", e);
+			response.sendError(500);						
 		} finally {
 			try {
-				if (conn != null)
+				if (conn != null) {
 					conn.close();
-			} catch (Throwable t) {}
+				}
+			} catch (Exception t) {}
 		}
 	}