--- android/TrainInfoService/src/dk/thoerup/traininfoservice/StationDAO.java	2010/06/11 17:12:29	836
+++ android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java	2010/09/22 21:09:39	1105
@@ -1,231 +1,213 @@
 package dk.thoerup.traininfoservice;
 
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.io.IOException;
 import java.util.ArrayList;
+import java.util.Collections;
+import java.util.Comparator;
 import java.util.List;
+import java.util.logging.Logger;
 
-public class StationDAO {
-	private StationBean convertSingleRow(ResultSet res) throws SQLException {
-		StationBean station = new StationBean();
-
-		station.setId( res.getInt(1) );
-		station.setName( res.getString(2) );
-		station.setLatitude( res.getDouble(3) );
-		station.setLongitude( res.getDouble(4) );
-		station.setRegional( res.getString(5) );
-		station.setStrain( res.getString(6) );
-		station.setMetro( res.getString(7) );
-		station.setAddress( res.getString(8) );
-		station.setCalcdist( (int)res.getDouble(9) );
-
-		return station;
-	}
-
-	private List<StationBean> convertResultset(ResultSet res) throws SQLException {
-		List<StationBean> stations = new ArrayList<StationBean>();
-		while (res.next()) {
-			stations.add( convertSingleRow(res) );
-		}
-		return stations;
+import javax.jdo.PersistenceManager;
+import javax.jdo.Query;
 
-	}
+import dk.thoerup.android.traininfo.common.StationBean;
+import dk.thoerup.android.traininfo.common.StationBean.StationEntry;
+import dk.thoerup.traininfoservice.geo.Geo;
+import dk.thoerup.traininfoservice.jdo.JdoStationBean;
+import dk.thoerup.traininfoservice.jdo.PMF;
 
+public class StationDAO {
+	final static int LOCATION_LIMIT = 8;
+	static final Logger logger = Logger.getLogger(StationDAO.class.getName());
 
-	public StationBean getById(int id) throws SQLException {
-		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
-		"FROM trainstations WHERE id=" + id + " AND enabled=true";
 
-		Connection conn = null;
-		Statement stmt = null;
-		ResultSet res = null;
-		StationBean result;
+	public StationEntry getById(int id)  {
 
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-
-			stmt = conn.createStatement();
-			res = stmt.executeQuery(SQL);		
-			res.next();
-			result = convertSingleRow(res);
-		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn != null)
-				conn.close();
+			pm = PMF.get().getPersistenceManager();
+			JdoStationBean bean =pm.getObjectById(JdoStationBean.class, new Integer(id) );
+			return bean.toStationEntry();
+		} finally {
+			pm.close();
 		}
-
-		return result;
 	}
 
-	/*
-	 * this code requires theses statements are run on database in order to do ILIKE searches against aliases (which is defines as array of varchar(64) )
-	 *     create function rlike(text,text) returns bool as 
-	 *     'select $2 ilike $1' language sql strict immutable;
-	 *     create operator ~~~ (procedure = rlike, leftarg = text, rightarg = text, commutator = ~~);
-	 */
-	public List<StationBean> getByName(String name) throws SQLException {
-		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + 
-		"FROM trainstations " + 
-		"WHERE (name ILIKE ? OR ? ~~~ ANY(aliases)) AND enabled = true " +
-		"ORDER BY name ";
-
-
-		List<StationBean> result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
-		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name + "%");
-			stmt.setString(2, name + "%");
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
+	/* damn that JDO sucks so we to the filtering in java-code */
+	public StationBean getByName(String searchname) {
+		PersistenceManager pm = null;
+		
+		try {
+			pm = PMF.get().getPersistenceManager();
+			
+			
+			
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setOrdering("name");			
+			List<JdoStationBean> beanList = (List<JdoStationBean>) q.execute();
+			
+			StationBean stationBean = new StationBean();
+			
+			searchname = searchname.toLowerCase();
+			for(JdoStationBean bean : beanList) { 
+				if (bean.getName().toLowerCase().startsWith(searchname)) {
+					stationBean.entries.add( bean.toStationEntry() );
+				} else {
+					for (String alias : bean.aliases ) {
+						if (alias.toLowerCase().startsWith(searchname)) {
+							stationBean.entries.add( bean.toStationEntry() );
+						}
+					}
+				}
+			}
 
+			return stationBean;			
 		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
+			pm.close();
 		}
-		return result;
 	}
 
-	//the "hack" with max 1.5 degrees latitude and 2.5 degrees longitude is only valid since we only service danish trains
-	// in denmark 1.5dg latitude ~ 165km, 2.5dg longitude ~ 155km
 
-	// the ultra fast method  (and only slightly inaccurate as long as we only cover a limited geographically area)
-	// is using an aproximation of the length of 1 latitude degree and 1 longitude degree and just use pythagoras to 
-	// calculate the distance:
-	//     sqrt( power(abs(latitude-?)*111320, 2) + power(abs(longitude-?)*63000,2) )::int as calcdist 
+	public StationBean getByLocation(double latitude, double longitude)  {
+		
 
-	public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
-		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + 
-		"       earth_distance( earth_coord, ll_to_earth(?,?))::int AS calcdist " + 
-		"FROM trainstations " +
-		"WHERE enabled = true AND abs(latitude-?)<1.5 AND abs(longitude-?)<2.5 " +
-		"ORDER BY calcdist ASC " + 
-		"LIMIT 4 ";
-		List<StationBean> result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
+		
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
-			stmt.setDouble(3, latitude);
-			stmt.setDouble(4, longitude);			
-			res = stmt.executeQuery();
-			result = convertResultset(res);
+			pm = PMF.get().getPersistenceManager();
+			List<JdoStationBean> beanList = (List<JdoStationBean>) pm.newQuery(JdoStationBean.class).execute();
+			
+			StationBean stationBean = new StationBean();			
+			
 
-		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
-		}
-		return result;
-	}
-
-	public List<StationBean> getByList(String list) throws SQLException {
-		String 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 ";
+			for(JdoStationBean bean : beanList) {
+				double meter = Geo.distanceKM(latitude, longitude, bean.getLatitude(), bean.getLongitude()) * 1000.0;
+				
+				bean.distance = (int) meter;
+			}
+			
 
-		Connection conn = null;
-		Statement stmt = null;
-		ResultSet res = null;
-		List<StationBean> result;
+			Collections.sort(beanList, new Comparator<JdoStationBean>() {
+				@Override
+				public int compare(JdoStationBean o1, JdoStationBean o2) {
+					if (o1.distance < o2.distance) {
+						return -1;
+					} else if (o1.distance > o2.distance) {
+						return 1;
+					} else {
+						return 0;
+					}
+				}
+			});
+			
+			for (int i=0; i<LOCATION_LIMIT && i<beanList.size(); i++) {
+				stationBean.entries.add( beanList.get(i).toStationEntry() );
+			}
 
-		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.createStatement();
-			res = stmt.executeQuery(SQL);
-			result = convertResultset(res);
+			return stationBean;			
 		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
+			pm.close();
 		}
-
-		return result;
-
 	}
-	public static String getStationName(int stationID) {
-		String station = "";
+	
 
-		Connection conn = null;
-		try {
-			conn = DBConnection.getConnection();
-			Statement stmt = conn.createStatement();
-			ResultSet rs = stmt.executeQuery("SELECT name FROM trainstations WHERE id=" + stationID);
-			if (rs.next()) {
-				station = rs.getString(1);
+	public StationBean getByList(String list)  {
+		PersistenceManager pm = null;
+		
+		try {
+			String parts[] = list.split(",");
+			
+			StringBuilder filter = new StringBuilder();
+			
+			for(String part : parts)  {
+				if (filter.length() > 0) {
+					filter.append( " || " );
+				}
+				filter.append("id == ").append(part);
+			}			
+			
+			//String filter = "id == 10 || id == 82"; //TODO: build filter
+			
+			pm = PMF.get().getPersistenceManager();
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setFilter( filter.toString() );
+			q.setOrdering("name");
+			
+			List<JdoStationBean> beanList = (List<JdoStationBean>) q.execute();
+			
+			StationBean stationBean = new StationBean();
+			
+			for(JdoStationBean bean : beanList) {
+				stationBean.entries.add( bean.toStationEntry() );
 			}
 
-		} catch (Exception e) {	
+			return stationBean;			
 		} finally {
-			try {
-				if (conn != null && !conn.isClosed())
-					conn.close();
-			} catch (Exception e) {}
+			pm.close();
 		}
 
-		return station;
 	}
 
-	public int getBySpecificName(String name) throws SQLException {
-		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + 
-		"FROM trainstations " + 
-		"WHERE name = ?  AND enabled = true " +
-		"LIMIT 1 ";
-
-		System.out.println(" getBySpecificName() ");
-
-		List<StationBean> result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
-		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name );
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
 
+	public int getIdByName(String name)  {
+		
+		List<JdoStationBean> beanList = null;
+		
+		PersistenceManager pm = null;
+		
+		try {
+			
+			String filter = " name == '" + name + "'";
+			
+			pm = PMF.get().getPersistenceManager();
+			Query q = pm.newQuery(JdoStationBean.class);
+			q.setFilter(filter);
+			
+			beanList = (List<JdoStationBean>) q.execute();
+			
+			StationBean stationBean = new StationBean();
+			
+			for(JdoStationBean bean : beanList) {
+				stationBean.entries.add( bean.toStationEntry() );
+			}
+			
 		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
+			pm.close();
 		}
 
-		if (result.size() == 1) {
-			return result.get(0).getId();
+		if ( beanList != null && beanList.size()  == 1) {
+			return (int) beanList.get(0).getId();
 		} else {
 			return -1;
 		}
 	}
+	
+	@SuppressWarnings("unchecked")
+	public int saveStations(StationBean stationBean) throws IOException {
+		PersistenceManager pm = null;
+		
+		try {
+			pm = PMF.get().getPersistenceManager();
+			
+			List oldEntries = (List) pm.newQuery(JdoStationBean.class).execute();
+			pm.deletePersistentAll(oldEntries);
+			
+			List<JdoStationBean> jdoList = new ArrayList<JdoStationBean>();
+			for (StationEntry station : stationBean.entries) {
+				JdoStationBean jdoBean = JdoStationBean.fromStationEntry(station);
+				
+				jdoList.add(jdoBean);
+				
+			}
+			pm.makePersistentAll(jdoList);
+			
+			return jdoList.size();
+			
+		} finally {
+			pm.close();
+		}
+	}
+	
 }