--- android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java	2010/09/22 19:13:41	1104
+++ android/TrainInfoServiceGoogle/src/dk/thoerup/traininfoservice/StationDAO.java	2010/09/22 21:09:39	1105
@@ -1,249 +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;
 
 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());
-	
-	
-	private StationEntry convertSingleRow(ResultSet res) throws SQLException {
-		StationEntry station = new StationEntry();
-
-		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) );
-		
-		station.setIsRegional( station.getRegional() != null );
-		station.setIsStrain( station.getStrain() != null );
-		station.setIsMetro( station.getMetro() != null );
-
-		return station;
-	}
 
-	private StationBean convertResultset(ResultSet res) throws SQLException {
-		StationBean stations = new StationBean();
-		while (res.next()) {
-			stations.entries.add( convertSingleRow(res) );
-		}
-		return stations;
-
-	}
 
-
-	public JdoStationBean getById(int id) throws SQLException {
+	public StationEntry getById(int id)  {
 
 		PersistenceManager pm = null;
 		
 		try {
 			pm = PMF.get().getPersistenceManager();
-			return pm.getObjectById(JdoStationBean.class, new Integer(id) );
+			JdoStationBean bean =pm.getObjectById(JdoStationBean.class, new Integer(id) );
+			return bean.toStationEntry();
 		} finally {
 			pm.close();
 		}
 	}
 
-	/*
-	 * 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 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 ";
-
-
-		StationBean result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
+	/* damn that JDO sucks so we to the filtering in java-code */
+	public StationBean getByName(String searchname) {
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name + "%");
-			stmt.setString(2, name + "%");
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
+			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 0.4 degrees latitude and 0.75 degrees longitude is only valid since we only service danish trains,
-	// in denmark 0.4dg latitude ~ 44km, 0.75dg longitude ~ 47km
 
-	// 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 getByLocationWorker(double latitude, double longitude, boolean geolimit) throws SQLException {
-		
-		String limitExpression = (geolimit == true) ? "AND abs(latitude-?)<0.4 AND abs(longitude-?)<0.75 " : "";
+	public StationBean getByLocation(double latitude, double longitude)  {
 		
-		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 " + limitExpression +
-			"ORDER BY calcdist ASC " + 
-			"LIMIT " + LOCATION_LIMIT;
-
 
 		
-		StationBean result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
-		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
-			if (geolimit == true) {
-				stmt.setDouble(3, latitude);
-				stmt.setDouble(4, longitude);
-			}
-			res = stmt.executeQuery();
-			result = convertResultset(res);
-		
-		} finally {
-			if (res != null)
-				res.close();
-			if (stmt != null)
-				stmt.close();
-			if (conn!= null)
-				conn.close();
-		}
-		return result;
-	}
-	
-	public StationBean getByLocation(double latitude, double longitude) throws SQLException {
-		StationBean result = getByLocationWorker(latitude, longitude, true);
+		PersistenceManager pm = null;
 		
-		if (result.entries.size() < LOCATION_LIMIT) { //failover
-			logger.info("getByLocation failover: " +latitude + "," + longitude);
+		try {
+			pm = PMF.get().getPersistenceManager();
+			List<JdoStationBean> beanList = (List<JdoStationBean>) pm.newQuery(JdoStationBean.class).execute();
+			
+			StationBean stationBean = new StationBean();			
 			
-			result = getByLocationWorker(latitude, longitude, false);
-		}
-		
-		return result;
-	}
-	
-	
 
-	public 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;
-		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;
+	public StationBean getByList(String list)  {
+		PersistenceManager pm = 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);
+			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 getIdByName(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 ";
 
-		StationBean result;
-		Connection conn = null;
-		PreparedStatement stmt = null;
-		ResultSet res = null;
+	public int getIdByName(String name)  {
+		
+		List<JdoStationBean> beanList = null;
+		
+		PersistenceManager pm = null;
+		
 		try {
-			conn = DBConnection.getConnection();
-			stmt = conn.prepareStatement(SQL);
-
-			stmt.setString(1, name );
-
-			res = stmt.executeQuery();
-			result = convertResultset(res);
-
+			
+			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.entries.size() == 1) {
-			return result.entries.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();
+		}
+	}
+	
 }