--- dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/afstandandenrute/Database.java	2016/01/30 14:05:53	2878
+++ dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/afstandandenrute/DatabaseRouteDistance.java	2016/02/13 13:16:34	2946
@@ -5,6 +5,7 @@
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
@@ -13,15 +14,16 @@
 import java.util.Set;
 import java.util.TreeSet;
 import java.util.concurrent.ConcurrentLinkedQueue;
-import java.util.logging.Logger;
 
 import dk.daoas.adressevedligehold.beans.Address;
+import dk.daoas.adressevedligehold.tasks.TaskLogger;
 import dk.daoas.adressevedligehold.util.DeduplicateHelper;
+import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
 
 
 
-public class Database {	
-	Logger logger = Logger.getLogger(Database.class.getName());
+public class DatabaseRouteDistance {	
+	private TaskLogger logger = TaskLogger.getInstance();
 
 	int batchCount = 0;
 
@@ -45,51 +47,76 @@
 	
 	//Map<Short, List<Address>> ikkeDaekkedePrPost = new HashMap<Short, List<Address>>(); 
 	
+	boolean isIncremental;
 	
 	private HashMap<Short,BoundingBox> bbCache = new HashMap<Short,BoundingBox>();
 
-	public Database(Connection conn)  throws SQLException,IOException {
+	public DatabaseRouteDistance(Connection conn, boolean isIncremental)  throws SQLException,IOException {
 		this.conn = conn;
+		
+		String newExt = "";
+		if (isIncremental == false) {
+			newExt = "_ny";
+		}
 
-		String sql = "INSERT INTO fulddaekning.afstand_anden_rute_ny (orgId,orgPostnr, orgAddress,orgGadeid,orgHusnr,orgHusnrBogstav,orgRute,id,postnr,Address,gadeid,husnr,husnrbogstav,rute,afstand,`timestamp`) "+
+		String sql = "INSERT INTO fulddaekning.afstand_anden_rute" + newExt + " (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,rute,afstand,`timestamp`) "+
 				"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )";
 
 		saveStmt = conn.prepareStatement(sql);		
+		this.isIncremental = isIncremental;
 
 	}
 
 	public void resetResultTable() throws SQLException {
-		logger.info("Dropping old result table (if exists)");
-		String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_ny";
-		conn.createStatement().executeUpdate(sql);
-		
-		logger.info("Create new result table");
-		sql = "CREATE TABLE fulddaekning.afstand_anden_rute_ny LIKE fulddaekning.afstand_anden_rute";
-		conn.createStatement().executeUpdate(sql);		
+		if (isIncremental == true) {
+			return;
+		}
+		
+		try (Statement stmt = conn.createStatement()) {
+			logger.info("Dropping old result table (if exists)");
+			String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_ny";
+			stmt.executeUpdate(sql);
+			
+			logger.info("Create new result table");
+			sql = "CREATE TABLE fulddaekning.afstand_anden_rute_ny LIKE fulddaekning.afstand_anden_rute";
+			stmt.executeUpdate(sql);		
+			
+		}
 	}	
 	
+	@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
 	public void renameResultTables() throws SQLException {
 		if (AfstandAndenRuteTask.test_mode == true) {
 			throw new RuntimeException("Can not rename tables in test mode");
 		}
-
-		Constants consts = Constants.getInstance();
-		String ext = consts.getTableExtension();
 		
-		logger.info("Dropping old backup table (if exists)");
-		String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_old" + ext;
-		conn.createStatement().executeUpdate(sql);
+		if (isIncremental) {
+			return;
+		}
+		
+		Constants consts = Constants.getInstance();
 		
-		logger.info("Rename tables");
-		String sql2 = "RENAME TABLE fulddaekning.afstand_anden_rute" + ext + " TO fulddaekning.afstand_anden_rute_old" + ext + ", fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute" + ext;
+		try (Statement stmt = conn.createStatement()) {
+			
+			String ext = consts.getTableExtension();
+			
+			logger.info("Dropping old backup table (if exists)");
+			String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_old" + ext;
+			stmt.executeUpdate(sql);
+			
+			logger.info("Rename tables");
+			String sql2 = "RENAME TABLE fulddaekning.afstand_anden_rute" + ext + " TO fulddaekning.afstand_anden_rute_old" + ext + ", fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute" + ext;
 
-		logger.info("Executing: " + sql2);
-		conn.createStatement().executeUpdate(sql2);		
+			logger.info("Executing: " + sql2);
+			stmt.executeUpdate(sql2);			
+		}
+
+		
 	}
 	
 	public BoundingBox getBoundingbox(short postnr)  {
 		BoundingBox bb = bbCache.get(postnr);
-		return bb.clone();//never return the original / cached object
+		return new BoundingBox(bb); //never return the original / cached object
 	}
 	
 	
@@ -99,57 +126,76 @@
 
 
 	public Queue<Address> hentAlleIkkedaekkedeAdresser(int minPostnr, int maxPostnr)  throws SQLException {
+		String placeHolder1 = "%INCREMENTAL1%";
+		String placeHolder2 = "%INCREMENTAL2%";
+		
+		Constants consts = Constants.getInstance();
 		
 		logger.info("Henter alle IKKE-daekkede Addressr");
 
-		String sql = "SELECT id,a.postnr,vejnavn,gadeid,husnr,husnrbogstav,latitude,longitude,ruteMa,p.distributor as ho " + 
-				"FROM fulddaekning.Addresstabel a " +
+		String sql = "SELECT a.id,a.postnr,a.vejnavn,a.gadeid,a.husnr,a.husnrbogstav,latitude,longitude,ruteMa,p.distributor as ho " + 
+				"FROM fulddaekning.adressetabel a " +
 				"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " +
+				placeHolder1 +
 				"WHERE ruteMa IS NULL " +  //Ingen dækning
 				"AND a.postnr BETWEEN ? AND ? " +
 				"AND latitude IS NOT NULL " +
 				"AND longitude IS NOT NULL " +
-				"AND gadeid IS NOT NULL " +
-				"AND (a.distributor IS NULL OR a.distributor<>'LUKKET') " 
+				"AND a.gadeid IS NOT NULL " +
+				"AND (a.distributor IS NULL OR a.distributor<>'LUKKET') " +
+				placeHolder2 +
+				"ORDER BY gadeid "
 				;		
 		
+		
+		if (isIncremental) {			
+			sql = sql.replace(placeHolder1, "LEFT JOIN fulddaekning.afstand_anden_rute" + consts.getTableExtension() +" afstand ON (a.id = afstand.orgId) " );
+			sql = sql.replace(placeHolder2, "AND afstand.id IS NULL " );
+		} else {
+			sql = sql.replace(placeHolder1, "");
+			sql = sql.replace(placeHolder2, "");
+		}
+		
 		if (AfstandAndenRuteTask.test_mode == true) {
 			sql = sql + " LIMIT 100 ";
 		}
 		
-		PreparedStatement stmt = conn.prepareStatement(sql);
-		stmt.setInt(1, minPostnr);
-		stmt.setInt(2, maxPostnr);
-
-		List<Address> list = hentAdresseListe( stmt );
-		alleIkkeDaekkede = list.toArray( new Address[ list.size() ] );
-		
-		logger.info("Analyserer ikke-daekkede Addressr");
-		
-		for (Address a : alleIkkeDaekkede) {
+		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
 			
+		
+			stmt.setInt(1, minPostnr);
+			stmt.setInt(2, maxPostnr);
+	
+			List<Address> list = hentAdresseListe( stmt );
+			alleIkkeDaekkede = list.toArray( new Address[ list.size() ] );
 			
-			BoundingBox bbox;
+			logger.info("Analyserer ikke-daekkede Addressr");
 			
-			if (! postnumre.contains(a.postnr )) {
-				postnumre.add( a.postnr );
+			for (Address a : alleIkkeDaekkede) {
+				
 				
-				bbox = new BoundingBox(); 
+				BoundingBox bbox;
+				
+				if (! postnumre.contains(a.postnr )) {
+					postnumre.add( a.postnr );
+					
+					bbox = new BoundingBox(); 
+					
+					bbCache.put( a.postnr, bbox);											
+					
+				} else {
+					 bbox = bbCache.get( a.postnr);
+				}
+				
+				bbox.latitudeMax = Math.max(bbox.latitudeMax, a.latitude);
+				bbox.latitudeMin = Math.min(bbox.latitudeMin, a.latitude);
+				bbox.longitudeMax = Math.max(bbox.longitudeMax, a.longitude);
+				bbox.longitudeMin = Math.min(bbox.longitudeMin, a.longitude);
 				
-				bbCache.put( a.postnr, bbox);											
 				
-			} else {
-				 bbox = bbCache.get( a.postnr);
 			}
-			
-			bbox.latitudeMax = Math.max(bbox.latitudeMax, a.latitude);
-			bbox.latitudeMin = Math.min(bbox.latitudeMin, a.latitude);
-			bbox.longitudeMax = Math.max(bbox.longitudeMax, a.longitude);
-			bbox.longitudeMin = Math.min(bbox.longitudeMin, a.longitude);
-			
-			
+			return new ConcurrentLinkedQueue<Address>( list );
 		}
-		return new ConcurrentLinkedQueue<Address>( list );
 	}
 	
 
@@ -163,7 +209,7 @@
 		
 		if ( alleAddressr == null ) {
 			String sql = "SELECT id,a.postnr,vejnavn,gadeid,husnr,husnrbogstav,latitude,longitude,ruteMa,p.distributor as ho " + 
-					"FROM fulddaekning.Addresstabel a " +
+					"FROM fulddaekning.adressetabel a " +
 					"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " +
 					"WHERE ruteMa IS NOT NULL " + 
 					"AND latitude IS NOT NULL " +
@@ -175,31 +221,34 @@
 			}
 	
 			// Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets)
-			// Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html 
-			PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
-			stmt.setFetchSize(Integer.MIN_VALUE);
-	
-			stmt.setString(1, distributor);
-	
-			List<Address> list = hentAdresseListe( stmt );
-			
-			alleAddressr = new ArrayList<Address>();
-			alleAddressr.addAll(list);
+			// Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html
 			
-			for(Address addr : list) {
-				short ho = addr.ho;
-				List<Address> hoListe = daekkedeAddressrHO.get(ho);
-				if ( hoListe == null) {
-					hoListe = new ArrayList<Address>();
-					daekkedeAddressrHO.put(ho, hoListe);					
-				}
+			try (PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
+				stmt.setFetchSize(Integer.MIN_VALUE);
+		
+				stmt.setString(1, distributor);
+		
+				List<Address> list = hentAdresseListe( stmt );
 				
-				hoListe.add(addr);								
+				alleAddressr = new ArrayList<Address>();
+				alleAddressr.addAll(list);
 				
+				for(Address addr : list) {
+					short ho = addr.ho;
+					List<Address> hoListe = daekkedeAddressrHO.get(ho);
+					if ( hoListe == null) {
+						hoListe = new ArrayList<Address>();
+						daekkedeAddressrHO.put(ho, hoListe);					
+					}
+					
+					hoListe.add(addr);								
+					
+				}
 			}
 		}
 		
 		return alleAddressr.size();
+		
 	}
 	
 	
@@ -242,6 +291,39 @@
 		//saveStmt.close();	
 	}
 	
+	
+	@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")	
+	public void prepareIncrementalSearch() throws SQLException {
+		if (isIncremental == false) {
+			return;
+		}
+		
+		logger.info("prepareIncrementalSearch() ");
+		
+		Constants consts = Constants.getInstance();
+		
+		String sql1 = "UPDATE fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " afstand " +
+					 "JOIN fulddaekning.adressetabel addr ON (afstand.id = addr.id) " + 
+					 "SET afstand.id = 0 " +
+					 "WHERE addr.rutema is null "
+					 ;
+		
+		String sql2 = "DELETE FROM fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " " +
+					  "WHERE id=0 "
+					  ;
+		
+		
+		try (Statement stmt = conn.createStatement()) {
+			
+			int rows = stmt.executeUpdate(sql1);
+			logger.info(sql1 + "\n updated rows: " + rows); 
+			
+			rows = stmt.executeUpdate(sql2);
+			logger.info(sql2 + "\n deleted rows: " + rows); 
+			
+		}
+	}
+	
 	public synchronized void saveBatch() throws SQLException{
 		saveStmt.executeBatch();
 		batchCount = 0;
@@ -255,43 +337,49 @@
 		Constants consts = Constants.getInstance();
 
 		//logger.info("Starting query");
-		ResultSet res = stmt.executeQuery();
-		//logger.info("Starting exec query done");
-
-		while (res.next()) {
-			
-			double latitude = res.getDouble(7);
-			double longitude = res.getDouble(8);
+		try ( ResultSet res = stmt.executeQuery() ) {
+		
 
-			
-			Address adr = new Address(latitude,longitude);
-
-			adr.id = res.getInt(1);
-			adr.postnr = res.getShort(2);
-			adr.vejnavn = vejnavnCache.getInstance( res.getString(3) );
-			adr.gadeid = res.getInt(4);
-			adr.husnr = res.getShort(5);
-			adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) );
-			//adr.latitude = res.getDouble(7);
-			//adr.longitude = res.getDouble(8);
-			adr.ruteMandag =  ruteCache.getInstance( res.getString(9) );
-			adr.ho = res.getShort(10);
-
-			list.add(adr);
-
-			if (consts.doCheckHO() == true && adr.ho == 0) {
-				System.out.println( "Mangler HO" );
-				System.out.println( adr );
-				System.exit(0);
+			while (res.next()) {
+				
+				
+				double latitude = res.getDouble(7);
+				double longitude = res.getDouble(8);
+	
+				
+				Address adr = new Address(latitude,longitude);
+	
+				adr.id = res.getInt(1);
+				adr.postnr = res.getShort(2);
+				adr.vejnavn = vejnavnCache.getInstance( res.getString(3) );
+				adr.gadeid = res.getInt(4);
+				adr.husnr = res.getShort(5);
+				adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) );
+				//adr.latitude = res.getDouble(7);
+				//adr.longitude = res.getDouble(8);
+				adr.ruteMandag =  ruteCache.getInstance( res.getString(9) );
+				adr.ho = res.getShort(10);
+	
+				
+				if (consts.validatePostnr(adr.postnr) == false) {//delegate to Constants implementations to validate whether we should look at this address 
+					continue;
+				}
+				
+				list.add(adr);
+	
+				if (consts.doCheckHO() == true && adr.ho == 0) {
+					logger.info( "Mangler HO: " + adr );
+					throw new RuntimeException("Mangler HO: "+ adr);
+				}
+	
+				//logger.info( "Adress:" + adr);
 			}
-
-			//logger.info( "Adress:" + adr);
+	
+			res.close();
+			stmt.close();
+	
+			return list;
 		}
-
-		res.close();
-		stmt.close();
-
-		return list;
 	}
 		
 }