/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package com.teltonikaServer.persistence; import com.teltonikaServer.protocol.immobilizerEvent; import java.sql.*; import java.time.Instant; import java.util.logging.Level; import java.util.logging.Logger; import com.teltonikaServer.network.TCPServer; import com.teltonikaServer.protocol.IoElementsSection; import com.teltonikaServer.protocol.GpsElementSection; import com.teltonikaServer.protocol.IgnitionCount; import java.util.ArrayList; import com.teltonikaServer.bl.*; /** * * @author Americo */ public final class Persistor { public static String MYSQL_HOST = "gps.protracking.com.uy "; public static String MYSQL_SCHEMA = "f22"; public static String MYSQL_USER = "f22"; public static String MYSQL_PASS = "Forestracking1024!"; // Conexión private Connection connection; // Statements public PreparedStatement dbmsPing; private static final int VALIDATION_TIMEOUT = 5; public Persistor(String host, String schema, String user, String pass) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, SQLException, SQLException, SQLException { // String url = "jdbc:mysql://" + MYSQL_HOST + ";databaseName=" + MYSQL_SCHEMA + // ";user=" + MYSQL_USER + ";password=******"; // connection = DriverManager.getConnection(url); Persistor.MYSQL_HOST = host; Persistor.MYSQL_SCHEMA = schema; Persistor.MYSQL_USER = user; Persistor.MYSQL_PASS = pass; System.out.println("Persisting: " + Persistor.MYSQL_HOST); connection = getConnection(MYSQL_USER, MYSQL_PASS); if (!connection.isClosed()) { TCPServer.serverLog.logEvent(Level.SEVERE, "Conexión DBMS establecida al esquema " + MYSQL_SCHEMA + " mediante TCP/IP... (" + MYSQL_USER + ",*******)"); } else { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 220 (Constructor connection request fail)"); } } public PreparedStatement getStatement(String sqlSentence) throws SQLException { return this.connection.prepareStatement(sqlSentence); } public Connection reconnect() throws SQLException, InstantiationException, IllegalAccessException { try { if (this.connection == null || !this.connection.isValid(VALIDATION_TIMEOUT)) { // connection.close(); this.connection = getConnection(MYSQL_USER, MYSQL_PASS); } } catch (SQLException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 211 (Reconnect fail): " + exception.getMessage()); } catch (InstantiationException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 212 (Reconnect fail): " + exception.getMessage()); } catch (IllegalAccessException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 213 (Reconnect fail): " + exception.getMessage()); } return this.connection; } public void checkConnection() throws SQLException, InstantiationException, IllegalAccessException { try { if (!this.connection.isValid(VALIDATION_TIMEOUT)) { reconnect(); } } catch (SQLException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "Check connection fail: " + exception.getMessage()); } catch (InstantiationException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "Check connection fail: " + exception.getMessage()); } catch (IllegalAccessException exception) { TCPServer.serverLog.logEvent(Level.SEVERE, "Check connection fail: " + exception.getMessage()); } } public Connection getConnection(String user, String password) throws InstantiationException, IllegalAccessException { try { TCPServer.serverLog.logEvent(Level.SEVERE, "Persisting (MySQL): " + MYSQL_SCHEMA); Connection aConnection = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_SCHEMA + "?" + "user=" + MYSQL_USER + "&password=" + MYSQL_PASS + "&serverTimezone=UTC"); try { dbmsPing = aConnection.prepareStatement("SELECT 1"); ResultSet rs = dbmsPing.executeQuery(); if (rs.next()) { if (rs.getInt(1) == 1) { return (Connection) aConnection; } } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 230 (PING fail): " + ex.getMessage()); } } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 231 (Get connection fail): " + e.getMessage()); } return null; } // Persistencia public long persistReceivedMessage(byte[] rawBuffer, String sourceIp, String receiver, String signature, String imei) { long logEntryId = -1; PreparedStatement rawIns = null; try { rawIns = this.connection.prepareStatement( "INSERT INTO iot_log_raw (rcv_timestamp,raw_msg,source_ip,bytes_red,origin,server_signature,imei) VALUES (?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS); rawIns.setLong(1, Instant.now().getEpochSecond()); rawIns.setBytes(2, rawBuffer); rawIns.setString(3, sourceIp); rawIns.setInt(4, rawBuffer.length); rawIns.setString(5, receiver); rawIns.setString(6, signature); rawIns.setString(7, imei); rawIns.executeUpdate(); ResultSet rs; rs = rawIns.getGeneratedKeys(); if (rs.next()) { logEntryId = rs.getLong(1); } try { rs.close(); } catch (SQLException rsException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 240 (RS CLOSE fail): " + rsException.getMessage()); } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 241 (Raw message insert fail): " + ex.getMessage()); } finally { try { if (rawIns != null) { rawIns.close(); } try { reconnect(); } catch (InstantiationException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 242 (Reconnect fail): " + ex.getMessage()); } catch (IllegalAccessException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 243 (Reconnect fail): " + ex.getMessage()); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 244 (Reconnect fail): " + s.getMessage()); } } return logEntryId; } public void persistIgnitionCounter(long assetId, long utcTimestamp, long hourCount, long hourCountOffset, int countStatus) { PreparedStatement statement = null; try { statement = this.connection.prepareStatement( "INSERT INTO iot_log_hours (asset_id, `utc_timestamp`, hour_count, hour_offset,checked) VALUES (?,?,?,?,?);"); statement.setLong(1, assetId); statement.setLong(2, utcTimestamp); statement.setLong(3, hourCount); statement.setLong(4, hourCountOffset); statement.setInt(5, countStatus); statement.executeUpdate(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 251 (Persist ignition fail): " + ex.getMessage()); ex.printStackTrace(); } if (statement != null) { try { statement.close(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 251 (Persist ignition fail): " + ex.getMessage()); } } } public long persistGPS_Data(GpsElementSection gpsElement, Asset asset, long logId, IoElementsSection ioElement, long ignitionCounterOffset, int messageTypeCode) { PreparedStatement statement = null; long gpsLogId = -1; try { statement = this.connection.prepareStatement( "INSERT INTO iot_log_gps_data (log_id, asset_id, `utc_timestamp`, lat, lng, alt, azimuth, speed, satellites, total_odometer, partial_odometer, total_hours, is_moving, ignition, inputs, outputs, ad_1, driver_id, message_type_code, hour_offset) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS); statement.setLong(1, logId); statement.setLong(2, asset.getAssetId()); statement.setLong(3, gpsElement.getUtcTimestamp()); statement.setDouble(4, gpsElement.getLat()); statement.setDouble(5, gpsElement.getLng()); statement.setInt(6, (int) gpsElement.getAltitude()); statement.setInt(7, gpsElement.getAngle()); statement.setInt(8, (int) gpsElement.getSpeed()); statement.setInt(9, gpsElement.getSatellites()); double totalOdometer = ioElement.getOdometer(); if (totalOdometer < 0) { statement.setNull(10, java.sql.Types.DOUBLE); } else { statement.setDouble(10, totalOdometer); } double tripOdometer = ioElement.getTripOdometer(); if (tripOdometer < 0) { statement.setNull(11, java.sql.Types.DOUBLE); } else { statement.setDouble(11, tripOdometer); } long ignCount = ioElement.getIgnitionCounter(); if (ignCount < 0) { statement.setNull(12, java.sql.Types.DOUBLE); } else { statement.setLong(12, ignCount); } int isMoving = ioElement.getMovementStatus(); if (isMoving < 0) { statement.setNull(13, java.sql.Types.DOUBLE); } else { statement.setInt(13, isMoving); } int ignition = ioElement.getIgnitionStatus(); if (ignition < 0) { statement.setNull(14, java.sql.Types.DOUBLE); } else { statement.setInt(14, ignition); } statement.setInt(15, ioElement.getInputs()); statement.setInt(16, ioElement.getOutputs()); statement.setInt(17, ioElement.getAd1()); // statement.setLong(18, ioElement.getDriverId()); statement.setLong(18, asset.getDriverId()); statement.setInt(19, messageTypeCode); statement.setLong(20, ignitionCounterOffset); statement.executeUpdate(); ResultSet rs; rs = statement.getGeneratedKeys(); if (rs.next()) { gpsLogId = rs.getLong(1); } try { rs.close(); } catch (SQLException rsException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 261 (LogId Close fail): " + rsException.getMessage()); } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 260 (log GPS data fail): " + ex.getMessage()); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 262 (Statement close fail): " + s.getMessage()); } } return gpsLogId; } public int updateAssetPosition(GpsElementSection gpsElement, long assetId, int ign, int moving, long ignCount, double mileage, int externalPower, long driverId, int inputs, int outputs, int ad1, boolean updateDriver, long ignitionCountOffset) { PreparedStatement statement = null; int updated = 0; String sql; try { if (updateDriver) { sql = "INSERT INTO iot_current_locations (asset_id, lat, lng, spd, azimuth, alt, ts, ign, moving, ignition_count, mileage, external_power, inputs, outputs, ad_1, driver_id, ignition_count_offset) "; sql += " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "; sql += " ON DUPLICATE KEY UPDATE lat = ?, lng = ?, spd = ?, azimuth = ?, alt = ?, ts = ?, ign = ?, moving = ?, ignition_count = ?, mileage = ?, external_power = ?, inputs = ?, outputs=?, ad_1=?, driver_id = ?, ignition_count_offset = ?;"; statement = this.connection.prepareStatement(sql); statement.setLong(1, assetId); statement.setDouble(2, gpsElement.getLat()); statement.setDouble(3, gpsElement.getLng()); statement.setInt(4, (int) gpsElement.getSpeed()); statement.setInt(5, gpsElement.getAngle()); statement.setInt(6, gpsElement.getAltitude()); statement.setLong(7, gpsElement.getUtcTimestamp()); statement.setInt(8, ign); statement.setInt(9, moving); statement.setLong(10, ignCount); statement.setDouble(11, mileage); statement.setInt(12, externalPower); statement.setInt(13, inputs); statement.setInt(14, outputs); statement.setInt(15, ad1); statement.setLong(16, driverId); statement.setLong(17, ignitionCountOffset); statement.setDouble(18, gpsElement.getLat()); statement.setDouble(19, gpsElement.getLng()); statement.setInt(20, (int) gpsElement.getSpeed()); statement.setInt(21, gpsElement.getAngle()); statement.setInt(22, gpsElement.getAltitude()); statement.setLong(23, gpsElement.getUtcTimestamp()); statement.setInt(24, ign); statement.setInt(25, moving); statement.setLong(26, ignCount); statement.setDouble(27, mileage); statement.setInt(28, externalPower); statement.setInt(29, inputs); statement.setInt(30, outputs); statement.setInt(31, ad1); statement.setLong(32, driverId); statement.setLong(33, ignitionCountOffset); } else { sql = "INSERT INTO iot_current_locations (asset_id, lat, lng, spd, azimuth, alt, ts, ign, moving, ignition_count, mileage, external_power, inputs, outputs, ad_1, ignition_count_offset)"; sql += " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "; sql += " ON DUPLICATE KEY UPDATE lat = ?, lng = ?, spd = ?, azimuth = ?, alt = ?, ts = ?, ign = ?, moving = ?, ignition_count = ?, mileage = ?, external_power = ?, inputs = ?, outputs=?, ad_1=?, ignition_count_offset = ?;"; statement = this.connection.prepareStatement(sql); statement.setLong(1, assetId); statement.setDouble(2, gpsElement.getLat()); statement.setDouble(3, gpsElement.getLng()); statement.setInt(4, (int) gpsElement.getSpeed()); statement.setInt(5, gpsElement.getAngle()); statement.setInt(6, gpsElement.getAltitude()); statement.setLong(7, gpsElement.getUtcTimestamp()); statement.setInt(8, ign); statement.setInt(9, moving); statement.setLong(10, ignCount); statement.setDouble(11, mileage); statement.setInt(12, externalPower); statement.setInt(13, inputs); statement.setInt(14, outputs); statement.setInt(15, ad1); statement.setLong(16, ignitionCountOffset); statement.setDouble(17, gpsElement.getLat()); statement.setDouble(18, gpsElement.getLng()); statement.setInt(19, (int) gpsElement.getSpeed()); statement.setInt(20, gpsElement.getAngle()); statement.setInt(21, gpsElement.getAltitude()); statement.setLong(22, gpsElement.getUtcTimestamp()); statement.setInt(23, ign); statement.setInt(24, moving); statement.setLong(25, ignCount); statement.setDouble(26, mileage); statement.setInt(27, externalPower); statement.setInt(28, inputs); statement.setInt(29, outputs); statement.setInt(30, ad1); statement.setLong(31, ignitionCountOffset); } updated = statement.executeUpdate(); } catch (SQLException ex) { if (ex.getSQLState().equals("45000")) { TCPServer.serverLog.logEvent(Level.FINE, "270 (Current position is older than previous): " + ex.getMessage()); } else { // Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, ex); TCPServer.serverLog.logEvent(Level.WARNING, "271 (Persist ignition fail): " + ex.getMessage()); } } finally { try { if (statement != null) { statement.close(); } } catch (SQLException s) { // Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, s); TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 272 (Persist ignition statement close fail): " + s.getMessage()); } } return updated; } public long persistEvent(int eventId, long logGpsDataId) { PreparedStatement statement = null; long ioLogId = -1; try { statement = this.connection.prepareStatement( "INSERT INTO iot_log_io_teltonika (log_gps_data_id,element_id,is_event) VALUES (?,?,1);", Statement.RETURN_GENERATED_KEYS); statement.setLong(1, logGpsDataId); statement.setInt(2, eventId); statement.executeUpdate(); ResultSet rs; rs = statement.getGeneratedKeys(); if (rs.next()) { ioLogId = rs.getLong(1); try { rs.close(); } catch (SQLException closeException) { Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, closeException); TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 282 (Persist event insert rs close fail): " + closeException.getMessage()); } } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 280 (Persist event insert fail): " + ex.getMessage()); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 281 (Persist event statement close fail): " + s.getMessage()); } } return ioLogId; } private long getDeviceIdFromImei(String imei) { PreparedStatement getDeviceStatement = null; long deviceId = -1; try { getDeviceStatement = this.connection .prepareStatement("SELECT iot_device_id FROM iot_devices WHERE imei = ?"); getDeviceStatement.setString(1, imei); ResultSet rs = getDeviceStatement.executeQuery(); if (rs.next()) { deviceId = rs.getLong("iot_device_id"); } try { rs.close(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.031 (Resultset Close fail) " + ex.getMessage()); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.031 (Get device id fail) " + s.getMessage()); } TCPServer.serverLog.logEvent(Level.INFO, "Previously assigned id for imei " + imei + " is " + deviceId); return deviceId; } private long persistNewIotDevice(String imei) { long deviceId = -1; PreparedStatement newDeviceStatement = null; try { newDeviceStatement = this.connection.prepareStatement("INSERT INTO iot_devices (imei) VALUES (?);", Statement.RETURN_GENERATED_KEYS); newDeviceStatement.setString(1, imei); newDeviceStatement.executeUpdate(); ResultSet rs; rs = newDeviceStatement.getGeneratedKeys(); if (rs.next()) { deviceId = rs.getLong(1); } try { rs.close(); } catch (SQLException closeFail) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.021 (Close resultset fail) " + closeFail.getMessage()); } } catch (SQLException insertException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.020 (Persist new device insert fail) " + insertException.getMessage()); deviceId = getDeviceIdFromImei(imei); } finally { try { if (newDeviceStatement != null) { newDeviceStatement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.022 (Persist close statement fail) " + s.getMessage()); } } return deviceId; } private long persistNewAsset(String imei) { long assetId = 0; PreparedStatement newAssetStatement = null; long deviceId = persistNewIotDevice(imei); try { newAssetStatement = this.connection.prepareStatement( "INSERT INTO assets (iot_device_id, asset_name, asset_type_id, org_id, asset_container_id,activo) VALUES (?,?,?,?,?,1);", Statement.RETURN_GENERATED_KEYS); newAssetStatement.setLong(1, deviceId); newAssetStatement.setString(2, imei); newAssetStatement.setInt(3, 2); newAssetStatement.setInt(4, 4); newAssetStatement.setInt(5, 200); newAssetStatement.executeUpdate(); ResultSet rs; rs = newAssetStatement.getGeneratedKeys(); if (rs.next()) { assetId = rs.getLong(1); try { rs.close(); } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 300 (Persist new asset insert fail): " + s.getMessage()); } } else { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 301 (Persist new asset autoinc fail): "); assetId = 0; } } catch (SQLException ex) { assetId = -1; TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 302 (Persist new asset insert fail): " + ex.getMessage()); } finally { try { if (newAssetStatement != null) { newAssetStatement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 303 (Persist new device insert fail): " + s.getMessage()); } } return assetId; } public Asset getAssetFromImei(String imei) { PreparedStatement statement = null; long assetId; Asset asset = new Asset(); try { statement = this.connection.prepareStatement( "SELECT a.asset_id, a.asset_name, a.matricula, a.org_id, a.iot_device_id, a.gateway_api_mask, a.hour_count_offset, mileage_offset, o.org_name, " + " l.ignition_count, l.driver_id, l.ts, a.max_allowed_speed FROM assets a " + "JOIN iot_devices i ON a.iot_device_id = i.iot_device_id " + "LEFT JOIN organizations o ON o.org_id = a.org_id " + "LEFT JOIN iot_current_locations l ON l.asset_id = a.asset_id " + "WHERE imei = ?"); statement.setString(1, imei); ResultSet rs; rs = statement.executeQuery(); if (rs.next()) { asset.setAssetId(rs.getLong("asset_id")); asset.setAssetName(rs.getString("asset_name")); asset.setOrgId(rs.getInt("org_id")); asset.setIotDeviceId(rs.getLong("iot_device_id")); asset.setGatewayApiMask(rs.getInt("gateway_api_mask")); asset.setMatricula(rs.getString("matricula")); asset.setOrgName(rs.getString("org_name")); asset.setIgnitionCounterOffset(rs.getLong("hour_count_offset")); asset.setMileageOffset(rs.getDouble("mileage_offset")); asset.setLastIgnitionCounter(rs.getLong("ignition_count")); asset.setDriverId(rs.getLong("driver_id")); asset.setLastTimestamp(rs.getLong("ts")); asset.setMaxAllowedSpeed(rs.getInt("max_allowed_speed")); } else { assetId = persistNewAsset(imei); asset.setAssetId(assetId); asset.setAssetName(imei); asset.setOrgId(0); asset.setIotDeviceId(0); asset.setGatewayApiMask(0); asset.setMatricula("-"); asset.setOrgName("-"); asset.setIgnitionCounterOffset(0); asset.setMileageOffset(0); asset.setLastIgnitionCounter(0); asset.setDriverId(0L); asset.setLastTimestamp(0); } try { rs.close(); } catch (SQLException rsCloseException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.010 (getAssetFromImei): " + rsCloseException.getMessage()); } } catch (SQLException sqlException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.011 (getAssetFromImei): " + sqlException.getMessage()); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException statementCloseException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR DB.012 (getAssetFromImei): " + statementCloseException.getMessage()); } } return asset; } public ArrayList getPendingCommands(String imei) { PreparedStatement statement = null; ArrayList pendingCommands = new ArrayList(); try { statement = this.connection.prepareStatement( "SELECT * FROM iot_commands WHERE imei = ? AND retries_left > 0 AND (last_sending_ts + retry_gap < unix_timestamp(now()));"); statement.setString(1, imei); ResultSet rs; rs = statement.executeQuery(); while (rs.next()) { try { long commandId = rs.getLong("command_id"); String commandBody = rs.getString("command_body"); Command command = new Command(); command.setCommandBody(commandBody); command.setCommandId(commandId); pendingCommands.add(command); } catch (SQLException sqlException) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 320 (getPendingCommands fail): " + sqlException.getMessage()); } } rs.close(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 321 (getPendingCommands fail): " + ex.getMessage()); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "ERROR 322 (getPendingCommands fail): " + e.getMessage()); } } return pendingCommands; } public long persistTrip(GpsElementSection gps, long gpsLogId, long assetId, double tripOdometer, double odometer, long ignCounter, int entryType, Asset asset) { PreparedStatement newTripStatement = null; long tripId = -1; try { newTripStatement = this.connection.prepareStatement( "INSERT INTO iot_log_trips (log_gps_data_id, asset_id, event_timestamp, lat, lng, trip_odometer, ign_counter, entry_type, odometer, driver_id ) VALUES (?,?,?,?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS); newTripStatement.setLong(1, gpsLogId); newTripStatement.setLong(2, assetId); newTripStatement.setLong(3, gps.getUtcTimestamp()); newTripStatement.setDouble(4, gps.getLat()); newTripStatement.setDouble(5, gps.getLng()); newTripStatement.setDouble(6, (double) tripOdometer); newTripStatement.setLong(7, ignCounter); newTripStatement.setInt(8, entryType); newTripStatement.setDouble(9, odometer); newTripStatement.setDouble(10, asset.getDriverId()); newTripStatement.executeUpdate(); ResultSet rs; rs = newTripStatement.getGeneratedKeys(); if (rs.next()) { tripId = rs.getLong(1); } else { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 330 (Persist trip fail)"); } rs.close(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 331 (Persist trip fail)"); } finally { try { if (newTripStatement != null) { newTripStatement.close(); } } catch (SQLException statementCloseError) { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 333 (Persist trip fail)"); } } return tripId; } public void markCommandSending(Command command) { PreparedStatement statement = null; try { statement = connection.prepareStatement( "UPDATE iot_commands SET last_sending_ts = UNIX_TIMESTAMP(NOW()), retries_left = retries_left-1 WHERE command_id = ?;"); statement.setLong(1, command.getCommandId()); statement.executeUpdate(); } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 340 (Persist IOT COMMAND SENDING)"); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "WARNING 341 (Persist IOT COMMAND Statement close problem)"); } } } public long persistIdling(GpsElementSection gps, long gpsLogId, long assetId, int idlingType, long ignCounter) { PreparedStatement idlingStatement = null; long idlingId = -1; try { idlingStatement = this.connection.prepareStatement( "INSERT INTO iot_log_idling (log_gps_data_id, asset_id, event_timestamp, lat, lng, idling, ign_counter) VALUES (?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS); idlingStatement.setLong(1, gpsLogId); idlingStatement.setLong(2, assetId); idlingStatement.setLong(3, gps.getUtcTimestamp()); idlingStatement.setDouble(4, gps.getLat()); idlingStatement.setDouble(5, gps.getLng()); idlingStatement.setInt(6, idlingType); idlingStatement.setLong(7, ignCounter); idlingStatement.executeLargeUpdate(); ResultSet rs; rs = idlingStatement.getGeneratedKeys(); if (rs.next()) { idlingId = rs.getLong(1); } else { idlingId = 0; } try { rs.close(); } catch (SQLException resultSetCloseError) { TCPServer.serverLog.logEvent(Level.WARNING, "DB.ERROR: No se puede cerrar Resultset (Persist Iddling)"); } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.WARNING, "DB.ERROR: No se puede persistir (Persist Iddling)"); } finally { try { if (idlingStatement != null) { idlingStatement.close(); } } catch (SQLException statementCloseError) { TCPServer.serverLog.logEvent(Level.WARNING, "DB.ERROR: No se puede cerrar Statement (Persist Iddling)"); } } return idlingId; } public int appendIMMPositions(GpsElementSection gpsElement, long assetId, String matricula, String orgName) { PreparedStatement statement = null; int updated = 0; try { String sql = "INSERT INTO iot_imm_locations (asset_id, lat, lng, spd, azimuth, ts, matricula, cli) VALUES (?,?,?,?,?,?,?,?) "; statement = this.connection.prepareStatement(sql); statement.setLong(1, assetId); statement.setDouble(2, gpsElement.getLat()); statement.setDouble(3, gpsElement.getLng()); statement.setInt(4, (int) gpsElement.getSpeed()); statement.setInt(5, gpsElement.getAngle()); statement.setLong(6, gpsElement.getUtcTimestamp()); statement.setString(7, matricula); statement.setString(8, orgName); updated = statement.executeUpdate(); } catch (SQLException ex) { // Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, ex); TCPServer.serverLog.logEvent(Level.SEVERE, "Error 370 al persistir posición (IMM), Matrícula: " + matricula + ", Coords: (" + gpsElement.getLat() + "," + gpsElement.getLng() + "," + gpsElement.getUtcTimestamp() + ")"); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 371 (Persist IMM statement close)"); } } return updated; } public boolean persistMessageTs(String imei, long currentTs) { PreparedStatement statement = null; int updated = 0; try { String sql = "UPDATE iot_devices SET last_message_ts = ? WHERE imei = ? LIMIT 1"; statement = this.connection.prepareStatement(sql); statement.setLong(1, currentTs); statement.setString(2, imei); updated = statement.executeUpdate(); } catch (SQLException ex) { // Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, ex); TCPServer.serverLog.logEvent(Level.SEVERE, "Error 360 (Persist LastMessage)"); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 361 (Persist LastMessage)"); } } return updated > 0; } private SpeedStatistic getSpeedingStatistic(long assetId, int baseSpeed, SpeedingEvent openingEvent, long currentTs) { SpeedStatistic sStat = new SpeedStatistic(baseSpeed, openingEvent.getSpeedingEventTimestamp()); if (openingEvent != null) { String statQuery = "SELECT speed, g.utc_timestamp as ts FROM iot_log_gps_data g WHERE asset_id = ? AND g.utc_timestamp BETWEEN ? AND ?;"; PreparedStatement statement; try { statement = connection.prepareStatement(statQuery); statement.setLong(1, assetId); statement.setLong(2, openingEvent.getSpeedingEventTimestamp()); statement.setLong(3, currentTs); try { ResultSet rs = statement.executeQuery(); while (rs.next()) { sStat.addSpeed(rs.getInt("speed"), rs.getLong("ts")); } rs.close(); } catch (SQLException ex) { //sStat = null; } finally { try { statement.close(); } catch (SQLException e) { } } } catch (SQLException e) { //sStat = null; } } return sStat; } private SpeedingEvent getLastOpenSpeeding(long assetId) { return getLastSpeedingEvent(assetId, 1); } private SpeedingEvent getLastSpeedingEvent(long assetId, int eventId) { SpeedingEvent sEvt; PreparedStatement statement = null; ResultSet resultSet = null; try { String statQuery = "SELECT * FROM iot_log_overspeeding WHERE asset_id = ? AND event_id = ? ORDER by event_timestamp DESC LIMIT 0,1;"; statement = this.connection.prepareStatement(statQuery); statement.setLong(1, assetId); statement.setLong(2, eventId); resultSet = statement.executeQuery(); if (resultSet.next()) { sEvt = new SpeedingEvent(); sEvt.setSpeedingEventId(resultSet.getLong("overspeed_id")); sEvt.setSpeedingEventTimestamp(resultSet.getLong("event_timestamp")); } else { sEvt = null; } } catch (SQLException ex) { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } } sEvt = null; } finally { try { if (statement != null) { statement.close(); } } catch (SQLException ex) { } } return sEvt; } public boolean persistOverspeeding(GpsElementSection gps, long logGpsDataId, long assetId, double mileage, int eventId, Asset asset) { TCPServer.serverLog.logEvent(Level.INFO, "Persisting O/S (ASSET: " + assetId + ", speed: " + gps.getSpeed()); PreparedStatement speedingStatement = null; int updated = 0; try { String overspeedQuery = "INSERT INTO iot_log_overspeeding (log_gps_data_id, asset_id, event_timestamp, lat, lng, speed, odometer,event_id,max_speed,avg_speed,related_id, ponderated_avg, driver_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"; speedingStatement = this.connection.prepareStatement(overspeedQuery); speedingStatement.setLong(1, logGpsDataId); speedingStatement.setLong(2, assetId); speedingStatement.setLong(3, gps.getUtcTimestamp()); speedingStatement.setDouble(4, gps.getLat()); speedingStatement.setDouble(5, gps.getLng()); speedingStatement.setInt(6, gps.getSpeed()); speedingStatement.setDouble(7, mileage); speedingStatement.setInt(8, eventId); if (eventId == 0) { SpeedingEvent sEvt = getLastOpenSpeeding(assetId); if (sEvt != null) { SpeedStatistic sStat = getSpeedingStatistic(assetId, gps.getSpeed(), sEvt, gps.getUtcTimestamp()); speedingStatement.setInt(9, sStat.getMaxSpeed()); speedingStatement.setInt(10, sStat.getAvgSpeed()); speedingStatement.setLong(11, sEvt.getSpeedingEventId()); speedingStatement.setInt(12, sStat.getPonderatedAvgSpeed()); } else { speedingStatement.setInt(9, 0); speedingStatement.setInt(10, 0); speedingStatement.setInt(11, 0); speedingStatement.setInt(12, 0); } speedingStatement.setLong(13, asset.getDriverId()); } else { speedingStatement.setNull(9, java.sql.Types.INTEGER); speedingStatement.setNull(10, java.sql.Types.INTEGER); speedingStatement.setNull(11, java.sql.Types.INTEGER); speedingStatement.setNull(12, java.sql.Types.INTEGER); speedingStatement.setNull(13, java.sql.Types.INTEGER); } updated = speedingStatement.executeUpdate(); } catch (SQLException ex) { // Logger.getLogger(Persistor.class.getName()).log(Level.SEVERE, null, ex); TCPServer.serverLog.logEvent(Level.SEVERE, "Error 380 (Overspeeding) Asset: "+assetId+" LogId: "+logGpsDataId+"\n"+ex.getMessage()+" SQL: "+ex.getSQLState()); //ex.printStackTrace(); } finally { try { if (speedingStatement != null) { speedingStatement.close(); } } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 381 (Overspeedind statement close)"); } } return updated > 0; } public long getDriverIdFromTagId(String tagId) { long driverId = -1; PreparedStatement statement = null; try { statement = TCPServer.persistor.getStatement("SELECT driver_id FROM drivers WHERE tag_id = ? LIMIT 0,1;"); statement.setString(1, tagId); ResultSet rs = statement.executeQuery(); if (rs.next()) { driverId = rs.getLong("driver_id"); } rs.close(); } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 390 (Driver ID)"); } finally { try { statement.close(); } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 391 (Driver ID)"); } } return driverId; } public void persistImmobilizerEvent(GpsElementSection gps, long logGpsDataId, long assetId, int authorized, long driverId, String tagId) { PreparedStatement statement = null; try { statement = TCPServer.persistor.getStatement( "INSERT INTO iot_log_immobilizer (log_gps_data_id, asset_id, driver_id, tag_id, event_timestamp,authorized) VALUES (?,?,?,?,?,?);"); statement.setLong(1, logGpsDataId); statement.setLong(2, assetId); statement.setLong(3, driverId); statement.setString(4, tagId); statement.setLong(5, gps.getUtcTimestamp()); statement.setInt(6, authorized); statement.executeUpdate(); } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 400 (ImmobilizerEvent)"); } finally { try { statement.close(); } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 401 (Driver ID)"); } } } public immobilizerEvent getCurrentDriver(long assetId) { PreparedStatement stmt = null; immobilizerEvent event = null; ResultSet rs = null; try { stmt = TCPServer.persistor.getStatement( "SELECT * FROM iot_log_immobilizer WHERE asset_id = ? ORDER BY event_timestamp DESC LIMIT 0,1;"); stmt.setLong(1, assetId); rs = stmt.executeQuery(); if (rs.next()) { event = new immobilizerEvent(); event.assetId = rs.getLong("asset_id"); event.logGpsDataId = rs.getLong("log_gps_data_id"); event.immobilizerLogId = rs.getLong("immobililizer_log_id"); event.driverId = rs.getLong("driver_id"); event.tagId = rs.getString("tag_id"); event.eventTimestamp = rs.getLong("event_timestamp"); event.authorized = rs.getInt("authorized"); } try { if (rs != null) { rs.close(); } } catch (SQLException sqlE) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 412 (GET Current Driver)"); } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 410 (GET Current Driver)"); } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException sqlE) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 411 (GET Current Driver)"); } } return event; } public boolean persistIgnitionCounterOffset(long assetId, long ignCounterOffset) throws SQLException { PreparedStatement statement = null; boolean updated = false; try { statement = this.connection .prepareStatement("UPDATE assets SET hour_count_offset = ? WHERE asset_id = ? LIMIT 1;"); statement.setLong(1, ignCounterOffset); statement.setLong(2, assetId); updated = (statement.executeUpdate() == 1); } catch (SQLException sqlException) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 420 (Update Ign Count error)" + sqlException.getMessage()); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException ex) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 421 (Update Ign Count error) " + ex.getMessage()); } } return updated; } public IgnitionCount getCurrentHourCount(long assetId) { PreparedStatement statement = null; IgnitionCount ignCnt = null; try { String query = "SELECT `utc_timestamp`,hour_count,hour_offset FROM iot_log_hours WHERE asset_id = ? ORDER BY `utc_timestamp` DESC, (hour_count+hour_offset) DESC LIMIT 0,1;"; statement = this.connection.prepareStatement(query); statement.setLong(1, assetId); ResultSet rs = statement.executeQuery(); if (rs.next()) { ignCnt = new IgnitionCount(); ignCnt.timestamp = rs.getLong("utc_timestamp"); ignCnt.ignitionCount = rs.getLong("hour_count"); ignCnt.ignitionCountOffset = rs.getLong("hour_offset"); } rs.close(); } catch (SQLException e) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 600 (GET current Hour Count)" + e.getMessage()); } finally { if (statement != null) { try { statement.close(); } catch (SQLException s) { TCPServer.serverLog.logEvent(Level.SEVERE, "Error 601 (GET current Hour Count)" + s.getLocalizedMessage()); } } } return ignCnt; } }