--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java	2015/02/24 20:38:56	2372
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java	2015/05/12 13:30:55	2544
@@ -13,8 +13,10 @@
 
 import dk.daoas.daoadresseservice.DaekningsType;
 import dk.daoas.daoadresseservice.beans.Address;
+import dk.daoas.daoadresseservice.beans.AliasBean;
 import dk.daoas.daoadresseservice.beans.ExtendedBean;
 import dk.daoas.daoadresseservice.beans.HundredePctBean;
+import dk.daoas.daoadresseservice.beans.LoggedAddress;
 import dk.daoas.daoadresseservice.beans.SearchResult;
 import dk.daoas.daoadresseservice.util.DeduplicateHelper;
 
@@ -25,7 +27,8 @@
 	public static List<Address> getAllAdresses() throws SQLException {
 		String debugFilter = DatabaseLayer.DEBUG ? " AND postnr = 8700 " : "";
 		
-		String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute "
+		String sql = 
+				"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute,latitude,longitude "
 				+ "FROM fulddaekning.adressetabel "
 				+ "WHERE gadeid IS NOT NULL "
 				+ debugFilter
@@ -51,16 +54,18 @@
 				Address a = new Address();
 				a.id = res.getInt(1);
 				a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
-				a.husnr = res.getInt(3);
+				a.husnr = (short) res.getInt(3);
 				a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
-				a.kommunekode = res.getInt(5);
-				a.vejkode = res.getInt(6);
-				a.postnr = res.getInt(7);
-				a.gadeid = res.getLong(8);
+				a.kommunekode = (short) res.getInt(5);
+				a.vejkode = (short)res.getInt(6);
+				a.postnr = (short)res.getInt(7);
+				a.gadeid = res.getInt(8);
 				a.distributor = distributorCache.getInstance(res.getString(9));
-				a.dbkBane = res.getInt(10);
+				a.dbkBane = (short) res.getInt(10);
 				a.koreliste = korelisteCache.getInstance( res.getString(11) );
 				a.rute = ruteCache.getInstance( res.getString(12) );
+				a.latitude = (float) res.getDouble(13);
+				a.longitude = (float) res.getDouble(14);
 				
 				//a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
 				
@@ -82,6 +87,43 @@
 		}
 	}
 	
+	public static List<AliasBean> getAliasList() throws SQLException {
+		
+
+		String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
+					"FROM bogleveringer.vejtabelprod " 
+					;
+		
+		try ( Connection conn = DBConnection.getConnection();		
+				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+				) {
+					
+			stmt.setFetchSize(Integer.MIN_VALUE);
+			
+			ResultSet res = stmt.executeQuery(sql);
+			
+			DeduplicateHelper<String> vejCache = new DeduplicateHelper<String>();
+			
+			List<AliasBean> list = new ArrayList<AliasBean>( 5000); 
+			while (res.next()) {		
+				
+				AliasBean ab = new AliasBean();
+				ab.postnr = res.getShort(1);
+				ab.vejnavn = vejCache.getInstance( res.getString(2) );
+				ab.aliasVejnavn = vejCache.getInstance( res.getString(3) );
+							
+				list.add(ab);
+			}
+		
+			res.close();
+			
+			System.out.println("Loaded " + list.size() + " aliase beans");
+			
+			return list;
+		}
+
+	}
+	
 	public static List<ExtendedBean> getExtendedAdresslist() throws SQLException {
 		String debugFilter1 = DatabaseLayer.DEBUG ? " WHERE orgPostnr = 8700 " : "";
 		String debugFilter2 = DatabaseLayer.DEBUG ? " AND orgPostnr = 8700 " : "";
@@ -119,7 +161,7 @@
 				ExtendedBean eb = new ExtendedBean();
 				eb.orgId = res.getInt(1);
 				eb.targetId = res.getInt(2);
-				eb.afstand = res.getDouble(3);
+				eb.afstand = (float) res.getDouble(3);
 				eb.transport = transportCache.getInstance(res.getString(4));
 							
 				list.add(eb);
@@ -133,7 +175,7 @@
 		}
 	}
 	
-	public static Map<Integer,HundredePctBean> get100PctList() throws SQLException {
+	public static Map<Short,HundredePctBean> get100PctList() throws SQLException {
 		String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " + 
 					 "FROM bogleveringer.adresser_udenfor_daekning";
 		
@@ -142,7 +184,7 @@
 				) {
 			ResultSet res = stmt.executeQuery(sql);
 	
-			Map<Integer,HundredePctBean> map = new HashMap<Integer,HundredePctBean>();
+			Map<Short, HundredePctBean> map = new HashMap<Short,HundredePctBean>();
 			
 			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
 			
@@ -150,11 +192,11 @@
 				
 				
 				HundredePctBean bean = new HundredePctBean();
-				bean.postnr = res.getInt(1);
+				bean.postnr = (short) res.getInt(1);
 				bean.distributor = distributorCache.getInstance(res.getString(2));
 				bean.rute = res.getString(3);
 				bean.koreliste = res.getString(4);
-				bean.dbkBane = res.getInt(5);			
+				bean.dbkBane = (short)res.getInt(5);			
 							
 				map.put(bean.postnr, bean);
 			}
@@ -169,15 +211,21 @@
 	}
 	
 	public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
+		String setVar = "set sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ";
+		
 		String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, indlast) " +
 					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,  NOW() )";
 		
 
 		
-		try ( Connection conn = DBConnection.getConnection();		
+		try ( Connection conn = DBConnection.getConnection();
+				Statement setStmt = conn.createStatement();				
 				PreparedStatement stmt = conn.prepareStatement(sql);
 			) {
 			
+			setStmt.execute(setVar);
+			
+			
 			stmt.setInt( 1, safeInt(postnr) );
 			stmt.setString( 2, adresse);
 			stmt.setString( 3, result.splitResult.vej);
@@ -195,6 +243,41 @@
 		}		
 	}
 	
+	/*
+	 * Bruges til at sammenligne gammel og ny adresse service - kan fjernes engang efter at vi er skiftet til ny service 
+	 */
+	public static List<LoggedAddress> getLoggedAdresses(int antaldage) throws SQLException {
+		String sql = "select postnr,adresse,status from logs.hentruteinformation where indlast>=date_sub(curdate(), interval " + antaldage + " day) " +
+				"and status IN (10,11,12) " +
+				"group by postnr,adresse "
+				;
+
+		try ( Connection conn = DBConnection.getConnection();		
+				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+				) {
+
+
+			stmt.setFetchSize(Integer.MIN_VALUE);
+
+			ResultSet res = stmt.executeQuery(sql);
+
+			List<LoggedAddress> result = new ArrayList<LoggedAddress>();
+
+			while (res.next()) {
+				LoggedAddress a = new LoggedAddress();				
+				a.postnr = res.getInt(1);				
+				a.adresse = res.getString(2);
+				a.status = res.getInt(3);
+
+				result.add(a);
+			}
+
+			res.close();
+
+			return result;
+		}
+	}
+	
 	private static int getStatusInt(SearchResult.Status status) {
 
 		switch (status) {
@@ -202,17 +285,22 @@
 			return 20;
 		case ERROR_MISSING_HOUSENUMBER:
 			return 21;
+		case ERROR_POSTBOX:
+			return 22;			
 		case ERROR_UNKNOWN_STREETNAME:
-			return 22;
-		case ERROR_UNKNOWN_ADDRESSPOINT:
 			return 23;
-		case STATUS_NOT_COVERED:
+		case ERROR_UNKNOWN_ADDRESSPOINT:
 			return 24;
-		case STATUS_OK:
+		case STATUS_NOT_COVERED:			
 			return 25;
+		case ERROR_INTERNAL: //			
+			return 26;			
+			
+		case STATUS_OK:
+			return 30;
 			
 		default:
-			return 29;
+			return 31;
 		}
 	}