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.AddressUtils;
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.SearchResult;
import dk.daoas.daoadresseservice.util.DeduplicateHelper;
import dk.daoas.daoadresseservice.util.TimingHelper;
public class DatabaseLayerImplementation implements DatabaseLayer {
static boolean DEBUG = false;
@Override
public List
getAllAdresses() throws SQLException {
TimingHelper timing = new TimingHelper();
String debugFilter = DatabaseLayerImplementation.DEBUG ? " AND postnr = 8700 " : "";
String sql =
"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute,korelisteloerdag,ruteloerdag,korelistesoendag,rutesoendag,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.korelisteLordag = korelisteCache.getInstance( res.getString(13) );
a.ruteLordag = ruteCache.getInstance( res.getString(14) );
a.korelisteSondag = korelisteCache.getInstance( res.getString(15) );
a.ruteSondag = ruteCache.getInstance( res.getString(16) );
a.latitude = (float) res.getDouble(17);
a.longitude = (float) res.getDouble(18);
//a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
//Ajourfør adresse objectets dækningstype
AddressUtils.updateDaekningstype(a);
list.add(a);
}
res.close();
System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
return list;
}
}
@Override
public List getAliasList() throws SQLException {
TimingHelper timing = new TimingHelper();
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("DB Loaded " + list.size() + " aliase beans in " + timing.getElapsed() + "ms");
return list;
}
}
@Override
public List getExtendedAdresslist() throws SQLException {
TimingHelper timing = new TimingHelper();
String debugFilter1 = DatabaseLayerImplementation.DEBUG ? " WHERE orgPostnr = 8700 " : "";
String debugFilter2 = DatabaseLayerImplementation.DEBUG ? " AND orgPostnr = 8700 " : "";
String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
"LEFT join odbc.transporttype t " +
"ON (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("DB Loaded " + list.size() + " extendedbeans in " + timing.getElapsed() + "ms");
return list;
}
}
@Override
public Map get100PctList() throws SQLException {
TimingHelper timing = new TimingHelper();
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();
DeduplicateHelper ruteCache = new DeduplicateHelper();
DeduplicateHelper korelisteCache = new DeduplicateHelper();
while (res.next()) {
HundredePctBean bean = new HundredePctBean();
bean.postnr = (short) res.getInt(1);
bean.distributor = distributorCache.getInstance(res.getString(2));
bean.rute = ruteCache.getInstance( res.getString(3) );
bean.koreliste = korelisteCache.getInstance(res.getString(4) );
bean.dbkBane = (short)res.getInt(5);
map.put(bean.postnr, bean);
}
res.close();
System.out.println("DB Loaded " + map.size() + " 100pct beans in " + timing.getElapsed() + "ms");
return map;
}
}
@Override
public void saveRequestLog(String brugerid, String postnr, String adresse, String omdelingsdag, 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, OmdelingDag, indlast) " +
"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
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);
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.setString(12, omdelingsdag);
stmt.executeUpdate();
}
}
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;
}
public 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;
}
}
}