package art.servers.gost.access.controller;
|
|
import art.library.interop.serialization.Serialization;
|
import art.library.model.devices.Device;
|
import art.library.model.devices.gost.access.AccessEnforcement;
|
import art.library.model.devices.gost.access.AccessEnforcementInformation;
|
import art.library.model.devices.gost.access.types.AccessEnforcement_Detection;
|
import art.library.model.devices.gost.access.types.AccessEnforcement_Detection_State;
|
import art.library.utils.licence.Licence;
|
import art.servers.controller.ControllerDevice;
|
import art.servers.gost.access.Shared;
|
import art.servers.gost.access.types.DatabasePoolConnection;
|
import art.servers.gost.access.utils.ZIP;
|
import java.io.File;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.Timestamp;
|
import java.sql.Types;
|
import java.text.SimpleDateFormat;
|
import java.util.Calendar;
|
import org.postgresql.util.PGobject;
|
|
|
public class Controller_ACCESS extends ControllerDevice
|
{
|
protected DatabasePoolConnection database = null;
|
|
|
public Controller_ACCESS(Device device, DatabasePoolConnection database)
|
{
|
super(device);
|
this.database = database;
|
}
|
|
|
|
protected void initialise()
|
{
|
// Database
|
|
Connection connection = null;
|
|
try
|
{
|
connection = database.getConnection(true);
|
|
database.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS detections(number BIGSERIAL, datetime TIMESTAMP, "
|
+ "device TEXT, type TEXT, plate TEXT NOT NULL, state SMALLINT, record TEXT, value JSONB, "
|
+ "PRIMARY KEY (datetime, device, plate)) "
|
+ "PARTITION BY RANGE (datetime)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_1 ON detections (number)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_2 ON detections (datetime)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_3 ON detections (datetime, device)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_4 ON detections (datetime, type)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_6 ON detections (datetime, state)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_7 ON detections (datetime, plate)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_8 ON detections (datetime, record");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_9 ON detections (record)");
|
database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_10 ON detections (state)");
|
|
|
// Partitioned table
|
|
SimpleDateFormat formato1 = new SimpleDateFormat("yyyy_MM");
|
SimpleDateFormat formato2 = new SimpleDateFormat("yyyy-MM-dd");
|
|
Calendar calendar1 = Calendar.getInstance();
|
calendar1.setTimeInMillis(System.currentTimeMillis());
|
calendar1.set(Calendar.DATE, 1);
|
calendar1.set(Calendar.HOUR_OF_DAY, 0);
|
calendar1.set(Calendar.MINUTE, 0);
|
calendar1.set(Calendar.SECOND, 0);
|
calendar1.set(Calendar.MILLISECOND, 0);
|
calendar1.add(Calendar.MONTH, -24);
|
|
Calendar calendar2 = Calendar.getInstance();
|
calendar2.setTimeInMillis(calendar1.getTimeInMillis());
|
calendar2.add(Calendar.MONTH, 1);
|
|
for (int i=0; i<48; i++)
|
{
|
String tablename = "detections_" + formato1.format(calendar1.getTimeInMillis());
|
String month1 = formato2.format(calendar1.getTimeInMillis());
|
String month2 = formato2.format(calendar2.getTimeInMillis());
|
|
database.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS " + tablename + " PARTITION OF detections FOR VALUES FROM ('" + month1 + "') TO ('" + month2 + "')");
|
|
calendar1.add(Calendar.MONTH, 1);
|
calendar2.add(Calendar.MONTH, 1);
|
}
|
|
|
database.vacuum("detections", connection);
|
|
}
|
catch (Exception exception)
|
{
|
// Shared.printstack(getName(), exception);
|
Shared.traceError(getName(),Shared.getMessage("Initialise"), exception);
|
}
|
finally
|
{
|
database.releaseConnection(connection);
|
}
|
}
|
|
|
|
|
protected int updateDatabaseDetection(AccessEnforcementInformation deviceInformation, AccessEnforcement_Detection detection) throws Exception
|
{
|
Connection connection = null;
|
PreparedStatement statement = null;
|
ResultSet resultset = null;
|
|
try
|
{
|
connection = database.getConnection(true);
|
|
// Exists
|
{
|
String command = "SELECT EXISTS(SELECT 1 FROM detections WHERE datetime = ? AND device = ?)";
|
statement = connection.prepareStatement(command);
|
statement.setTimestamp(1, new Timestamp(detection.timestamp));
|
statement.setString(2, deviceInformation.getIdentifier());
|
resultset = statement.executeQuery();
|
resultset.next();
|
if (resultset.getBoolean(1) == true) return 0;
|
}
|
|
|
// Save images
|
|
ZIP.zip(detection, Licence.decrypt(deviceInformation.storage.storagePassword), deviceInformation.storage.storageFolder);
|
|
// Remove images, we don't want store in database
|
|
detection.images = null;
|
|
AccessEnforcement_Detection_State detectionState = detection.getLastState();
|
if (detectionState.vehicle.plate != null) detectionState.vehicle.plate = detectionState.vehicle.plate.replaceAll("\\s+","");
|
|
// Insert
|
{
|
String command = "INSERT INTO detections (datetime, device, type, plate, state, record , value) VALUES (?,?,?,?,?,?,?) ON CONFLICT DO NOTHING";
|
statement = connection.prepareStatement(command);
|
PGobject jsonObject = new PGobject();
|
jsonObject.setType("json");
|
jsonObject.setValue(Serialization.toString(detection));
|
|
statement.setTimestamp(1, new Timestamp(detection.timestamp));
|
statement.setString(2, deviceInformation.getIdentifier());
|
statement.setString(3, AccessEnforcement.class.getName());
|
statement.setString(4, detectionState.vehicle.getPlate());
|
statement.setShort(5, detectionState.state);
|
|
if ((detectionState.violation != null) && (detectionState.violation.record != null))
|
{
|
statement.setString(6, detectionState.violation.record);
|
}
|
else
|
{
|
statement.setNull(6, Types.VARCHAR);
|
}
|
|
statement.setObject(7, jsonObject);
|
int result = statement.executeUpdate();
|
database.close(statement);
|
return result;
|
}
|
}
|
finally
|
{
|
database.releaseConnection(connection, statement);
|
}
|
|
}
|
|
|
|
|
|
|
protected void delete(File folder)
|
{
|
for (File file : folder.listFiles())
|
{
|
if (file.isDirectory() == true)
|
{
|
delete(file);
|
}
|
else
|
{
|
file.delete();
|
}
|
}
|
|
folder.delete();
|
}
|
|
}
|
