package Parse; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import DataStructure.GPScoordinate; import DataStructure.MobilePhone; import DataStructure.SingleBTS; public class SqlPollerDate { public Connection cn; public Connection cnForName; /** * @param args * @throws SQLException * @throws ClassNotFoundException */ public static void main(String[] args) throws ClassNotFoundException, SQLException { SqlPollerDate sql = new SqlPollerDate(); MobilePhone phone = sql.getActionRefID(146024l); phone.toString(); } public SqlPollerDate() throws ClassNotFoundException, SQLException { cn = getConnection(); cnForName = getNameConnection(); } private Connection getNameConnection() { // catch Exceptions as Name lookup is not needed for operation try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { Connection cn = DriverManager.getConnection( "jdbc:mysql://132.230.4.13:3306/hlr", "richard", "uh237Aug.ad7"); cn.setReadOnly(true); return cn; } catch (SQLException e) { return null; } } public SqlPollerDate(SqlPollerUnThreaded sql) { cn = sql.cn; } /** * Returns last occured MRs of phone with given IMSI. * * @param IMSI * @return * @throws SQLException */ public MobilePhone getLastMRs(long IMSI) throws SQLException { // TODO MobilePhone result = new MobilePhone(); result.IMSI = IMSI; result.MR = getIMSIMRs(IMSI); result.name = getName(IMSI); return result; } private ArrayList getIMSIMRs(long IMSI) throws SQLException { String query = imsiQuery(IMSI); ResultSet rs = cn.createStatement().executeQuery(query); // get current assigned BTS-arfcn int usedBTS = getUsedBTSIMSI(IMSI); ArrayList result = new ArrayList(); while (rs.next()) { // parse first BTS SingleBTS assigned = new SingleBTS(usedBTS, "lastUsed"); assigned.addDl(rs.getInt("RXL_FULL_dl")); Date timestamp = rs.getTimestamp("cur_timestamp"); assigned.time = timestamp; result.add(assigned); // now, add neighbor BTSs int neigh = rs.getInt("NUM_NEIGH"); if (neigh != 7) { for (int i = 0; i < neigh; i++) { int ARFCN = rs.getInt("ARFCN_" + i); int rxdl = rs.getInt("rxlev_" + i); // int reason = rs.getInt("actionID"); SingleBTS neighbor = new SingleBTS(ARFCN, 0, rxdl, false, timestamp, "Lookup Record"); // neighbor.reason = reason; result.add(neighbor); } } } return result; } private int getUsedBTSIMSI(long iMSI) throws SQLException { String query = "SELECT arfcn FROM arfcn " + "JOIN usedBTS ON usedBTS.bts = " + "arfcn.bts JOIN action ON imsi = " + iMSI + " AND usedBTS.actionRefID = action.ID " + "ORDER BY usedBTS.cur_timestamp DESC LIMIT 0,1"; ResultSet rs = cn.createStatement().executeQuery(query); rs.first(); int arfcn = rs.getInt("arfcn"); // return new SingleBTS(arfcn, "last Used BTS"); return arfcn; } private String imsiQuery(long iMSI) { // TODO Auto-generated method stub return "SELECT MR.cur_timestamp, RXL_FULL_dl, RXL_SUB_dl, " + "RXQ_FULL_dl, RXQ_SUB_dl, NUM_NEIGH, " + "IDX_0, ARFCN_0, BSIC_0, rxlev_0, IDX_1, " + "ARFCN_1, BSIC_1, rxlev_1, IDX_2, ARFCN_2, " + "BSIC_2, rxlev_2, IDX_3, ARFCN_3, BSIC_3, " + "rxlev_3, IDX_4, ARFCN_4, BSIC_4, rxlev_4, " + "IDX_5, ARFCN_5, BSIC_5, rxlev_5 " + "FROM measurementResults MR JOIN action ON " + "IMSI = " + iMSI + " AND MR.actionRefID = action.ID " + "ORDER BY MR.id DESC LIMIT 0,6"; } public MobilePhone getActionRefID(long actionRefID) throws SQLException { // get MRs ArrayList MRs = getActionRefMRs(actionRefID); // get IMSI of actionRefID long IMSI = getIMSI(actionRefID); MobilePhone phone = new MobilePhone(); phone.IMSI = IMSI; // phone.MR = filterMRTime(MRs, 9000l); // phone.MR = getMiddle(MRs); phone.MR = MRs; // phone.MR = ListBTS.generateAveragedList(phone.MR); System.out.println("ActionRefID " + actionRefID + " fertig"); // get Coordinates of that phone if possible phone.correctCoordinate = getCoord(actionRefID); phone.name = getName(IMSI); return phone; } private String getName(long iMSI) { try { Statement st = cnForName.createStatement(); ResultSet rs = st .executeQuery("SELECT surname, User.name FROM Subscriber" + " JOIN UserWatch ON imsi = '" + iMSI + "' AND Subscriber.id = subscriber_id " + "JOIN User ON user_id = User.id"); rs.first(); return rs.getString("surname") + " " + rs.getString("name"); } catch (SQLException e) { // e.printStackTrace(); } catch (NullPointerException e) { } return null; } /** * Searches the logging table coordinates for the correct coordinate for * this measure * * @param actionRefID * @return * @throws SQLException */ public GPScoordinate getCoord(long actionRefID) { String query = "SELECT * FROM coordinates WHERE ActionID = " + actionRefID; try { ResultSet rs = cn.createStatement().executeQuery(query); while (rs.next()) { GPScoordinate coordinate = new GPScoordinate(new Date(), rs.getDouble("CoordNS"), 'N', rs.getDouble("CoordEW"), 'E', true); return coordinate; } } catch (SQLException e) { } return null; } /** * Returns all BTS that are l seconds before last Measurement * * @param mRs * @param l * @return */ @SuppressWarnings("unused") private ArrayList filterMRTime(ArrayList mRs, long l) { // get max time long time = getMaxTime(mRs); // make new list. copy every entry that is l milliseconds before time ArrayList newMR = new ArrayList(); for (int i = 0; i < mRs.size(); i++) { if ((time - mRs.get(i).time.getTime()) <= l) { newMR.add(mRs.get(i)); } } return newMR; } /** * Returns Middle +/- 1/2 second! * * @param MR * @return */ @SuppressWarnings("unused") private ArrayList getMiddle(ArrayList MR) { ArrayList result = new ArrayList(); long time = (getMinTime(MR) + getMaxTime(MR)) / 2; for (SingleBTS current : MR) { if (Math.abs(current.time.getTime() - time) <= 1000) { result.add(current); } } return result; } private long getMinTime(ArrayList MR) { long time = MR.get(0).time.getTime(); for (int i = 1; i < MR.size(); i++) { if (MR.get(i).time.getTime() < time) { time = MR.get(i).time.getTime(); } } return time; } private long getMaxTime(ArrayList MR) { long time = MR.get(0).time.getTime(); for (int i = 1; i < MR.size(); i++) { if (MR.get(i).time.getTime() > time) { time = MR.get(i).time.getTime(); } } return time; } /** * Returns IMSI from given ActionRefID * * @param actionRefID * @return * @throws SQLException */ private long getIMSI(long actionRefID) throws SQLException { String query = "Select IMSI from action WHERE ID = " + actionRefID; ResultSet rs = cn.createStatement().executeQuery(query); rs.first(); return rs.getLong("IMSI"); } /** * Gets all MR with given actionRefID * * @param actionRefID * @param usedBTS * @return * @throws SQLException */ private ArrayList getActionRefMRs(long actionRefID) throws SQLException { SingleBTS[] usedBTS = getUsedBTS(actionRefID); String query = "SELECT * FROM measurementResults MR " + "WHERE actionRefID = " + actionRefID; Statement st = cn.createStatement(); ResultSet rs = st.executeQuery(query); ArrayList result = new ArrayList(); while (rs.next()) { // add current assigned BTS Date timestamp = rs.getTimestamp("cur_timestamp"); // get name of the assigned BTS SingleBTS search = searchForUsedBTS(usedBTS, timestamp); // transfer name to current SingleBTS current = new SingleBTS(search.ARFCN, search.name); current.clear(); // fill current with its receive level int RXdl = rs.getInt("RXL_FULL_dl"); current.addDl(RXdl); current.time = timestamp; result.add(current); // now: neighbors! result.addAll(readNeighborList(rs, timestamp)); } return result; } /** * Parses all BTSs from neighbor list fo given ResultSet. Assigned date * provided in timestamp * * @param rs * @param timestamp * @return * @throws SQLException */ private ArrayList readNeighborList(ResultSet rs, Date timestamp) throws SQLException { int numOfNeigh = rs.getInt("NUM_NEIGH"); if (numOfNeigh == 7 || numOfNeigh == 0) { return new ArrayList(); } ArrayList result = new ArrayList(); for (int i = 0; i < numOfNeigh; i++) { int ARFCN = rs.getInt("ARFCN_" + i); int RXdl = rs.getInt("rxlev_" + i); SingleBTS neighbor = new SingleBTS(ARFCN, "neighbor"); neighbor.clear(); neighbor.addDl(RXdl); neighbor.time = timestamp; result.add(neighbor); } return result; } /** * Returns index of currently used BTS * * @param btsnames * @param timestamp * @return */ private SingleBTS searchForUsedBTS(SingleBTS[] btsnames, Date timestamp) { if (btsnames == null || btsnames.length == 0 || timestamp == null) { return null; } // System.out.print("searchInBetween called..."); SingleBTS searchelement = new SingleBTS(0, 0, 0, false, timestamp, "name"); int between = Arrays.binarySearch(btsnames, searchelement); if (between < 0) { between = Math.abs(between); between = between - 2; } // if between < 0 it means that the call would be invalid because there // is no usedBTS assignable. A call was before a BTS got assigned // through OpenBSC. Check if there is a x second margin! // System.out.println("ended!"); return btsnames[between]; } private SingleBTS[] getUsedBTS(long actionRefID) throws SQLException { String query = "select bts.cur_timestamp, " + "bts.bts, " + "arfcn.arfcn, " + "arfcn.location " + "from usedBTS bts " + "join action on bts.actionRefID = " + actionRefID + " " + "AND bts.actionRefID = action.ID " + "JOIN arfcn ON bts.bts = arfcn.bts " + "ORDER BY bts.ID"; Statement st = cn.createStatement(); ResultSet rs = st.executeQuery(query); ArrayList usedBTS = new ArrayList(); while (rs.next()) { int ARFCN = rs.getInt("arfcn.arfcn"); String name = rs.getString("arfcn.location"); Date time = rs.getTimestamp("bts.cur_timestamp"); SingleBTS current = new SingleBTS(ARFCN, name); current.time = time; usedBTS.add(current); } return usedBTS.toArray(new SingleBTS[1]); } private Connection getConnection() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); cn = DriverManager.getConnection( "jdbc:mysql://132.230.4.13:3306/logging", "richard", "uh237Aug.ad7"); cn.setReadOnly(true); return cn; } }