package dk.daoas.daoadresseservice.db;
import java.sql.Connection;
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;
import java.util.Map;
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;
public class DatabaseLayer {
static boolean DEBUG = false;
public static List
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,latitude,longitude "
+ "FROM fulddaekning.adressetabel "
+ "WHERE gadeid IS NOT NULL "
+ debugFilter
;
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 list = new ArrayList(2600000);//initial capacity 2.6 mio
DeduplicateHelper vejnavnCache = new DeduplicateHelper();
DeduplicateHelper husnrbogstavCache = new DeduplicateHelper();
DeduplicateHelper distributorCache = new DeduplicateHelper();
DeduplicateHelper korelisteCache = new DeduplicateHelper();
DeduplicateHelper ruteCache = new DeduplicateHelper();
while (res.next()) {
Address a = new Address();
a.id = res.getInt(1);
a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
a.husnr = (short) res.getInt(3);
a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
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 = (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);
if (a.rute != null && a.rute.length()> 0) {
a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
} else {
a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
}
list.add(a);
}
res.close();
stmt.close();
conn.close();
System.out.println("Loaded " + list.size() + " adresses");
return list;
}
}
public static List 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 vejCache = new DeduplicateHelper();
List list = new ArrayList( 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 getExtendedAdresslist() throws SQLException {
String debugFilter1 = DatabaseLayer.DEBUG ? " WHERE orgPostnr = 8700 " : "";
String debugFilter2 = DatabaseLayer.DEBUG ? " AND orgPostnr = 8700 " : "";
String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
"join odbc.transporttype t " +
"on t.Art = 'Transpost' " +
"and ( (t.Type = 'Cykel' and a.Afstand < 1.001) or (t.Type = 'Scooter' and a.Afstand < 1.201) or (t.Type = 'Bil' and a.Afstand < 2.601) ) " +
"and t.Rute = a.Rute " +
debugFilter1 +
"UNION ALL " +
"SELECT orgid, a.id as targetid, afstand,'' as type FROM fulddaekning.afstand_anden_rute_bk a " +
"left join bogleveringer.postnummerdistributor d on d.PostNr = a.orgPostnr " +
"WHERE d.Distributor <> 10057 " +
debugFilter2
;
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 transportCache = new DeduplicateHelper();
List list = new ArrayList( 350000); //Initial capacity 350K
while (res.next()) {
ExtendedBean eb = new ExtendedBean();
eb.orgId = res.getInt(1);
eb.targetId = res.getInt(2);
eb.afstand = (float) res.getDouble(3);
eb.transport = transportCache.getInstance(res.getString(4));
list.add(eb);
}
res.close();
System.out.println("Loaded " + list.size() + " extendedbeans");
return list;
}
}
public static Map get100PctList() throws SQLException {
String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
"FROM bogleveringer.adresser_udenfor_daekning";
try ( Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
) {
ResultSet res = stmt.executeQuery(sql);
Map map = new HashMap();
DeduplicateHelper distributorCache = new DeduplicateHelper();
while (res.next()) {
HundredePctBean bean = new HundredePctBean();
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 = (short)res.getInt(5);
map.put(bean.postnr, bean);
}
res.close();
System.out.println("Loaded " + map.size() + " 100pct beans");
return map;
}
}
public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
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();
PreparedStatement stmt = conn.prepareStatement(sql);
) {
stmt.setInt( 1, safeInt(postnr) );
stmt.setString( 2, adresse);
stmt.setString( 3, result.splitResult.vej);
stmt.setString( 4, coalesce(result.googleVej,result.osmVej) );
stmt.setString( 5, nullify(result.splitResult.husnr) );
stmt.setString( 6, result.splitResult.litra);
stmt.setString( 7, result.splitResult.etage);
stmt.setString( 8, result.splitResult.lejlighed);
stmt.setString( 9, result.splitResult.resten);
stmt.setString(10, brugerid);
stmt.setInt(11, getStatusInt(result.status) );
stmt.executeUpdate();
}
}
/*
* Bruges til at sammenligne gammel og ny adresse service - kan fjernes engang efter at vi er skiftet til ny service
*/
public static List 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 result = new ArrayList();
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) {
case ERROR_UNKNOWN_POSTAL:
return 20;
case ERROR_MISSING_HOUSENUMBER:
return 21;
case ERROR_POSTBOX:
return 22;
case ERROR_UNKNOWN_STREETNAME:
return 23;
case ERROR_UNKNOWN_ADDRESSPOINT:
return 24;
case STATUS_NOT_COVERED:
return 25;
case ERROR_INTERNAL: //
return 26;
case STATUS_OK:
return 30;
default:
return 31;
}
}
private static int safeInt(String str) {
try {
return Integer.parseInt( str );
} catch (NumberFormatException e) {
return 0;
}
}
private static String nullify(String str) {
if (str == null)
return null;
if (str.equals("")) {
return null;
} else {
return str;
}
}
private static String coalesce(String s1, String s2) {
if (s1 != null)
return s1;
return s2;
}
}