summaryrefslogtreecommitdiffstats
path: root/Parse/SqlPollerDate.java
diff options
context:
space:
mode:
Diffstat (limited to 'Parse/SqlPollerDate.java')
-rw-r--r--Parse/SqlPollerDate.java406
1 files changed, 406 insertions, 0 deletions
diff --git a/Parse/SqlPollerDate.java b/Parse/SqlPollerDate.java
new file mode 100644
index 0000000..e71e66b
--- /dev/null
+++ b/Parse/SqlPollerDate.java
@@ -0,0 +1,406 @@
+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<SingleBTS> 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<SingleBTS> result = new ArrayList<SingleBTS>();
+ 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<SingleBTS> 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<SingleBTS> filterMRTime(ArrayList<SingleBTS> mRs, long l) {
+ // get max time
+ long time = getMaxTime(mRs);
+
+ // make new list. copy every entry that is l milliseconds before time
+ ArrayList<SingleBTS> newMR = new ArrayList<SingleBTS>();
+ 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<SingleBTS> getMiddle(ArrayList<SingleBTS> MR) {
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+ 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<SingleBTS> 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<SingleBTS> 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<SingleBTS> 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<SingleBTS> result = new ArrayList<SingleBTS>();
+
+ 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<SingleBTS> readNeighborList(ResultSet rs, Date timestamp)
+ throws SQLException {
+ int numOfNeigh = rs.getInt("NUM_NEIGH");
+ if (numOfNeigh == 7 || numOfNeigh == 0) {
+ return new ArrayList<SingleBTS>();
+ }
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+ 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<SingleBTS> usedBTS = new ArrayList<SingleBTS>();
+ 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;
+ }
+
+}