public FormMain(Company company, User user)
{
InitializeComponent();
this.DoubleBuffered = true;
SetStyle(ControlStyles.OptimizedDoubleBuffer, true);
this.user = user;
this.company = company;
database = new Database(Settings.Default.DatabaseHost, Settings.Default.DatabaseUsername, Settings.Default.DatabasePassword, 30);
}
static void Main()
{
//if (Debugger.IsAttached) {
// Settings.Default.Reset();
//}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
//0 = ResetMode
//1 = DevelopersMode
//2 = Static Ip
int appMode = 2;
//int appServer = 2;
if (appMode == 0) {
Settings.Default.Reset();
}
User user = new User();
Company company = new Company();
while (true) {
DialogLogin dialogLogin = new DialogLogin(company,user);
DialogResult dialogResult = dialogLogin.ShowDialog();
if (dialogResult == DialogResult.OK) {
FormMain formMain = new FormMain(company, user);
dialogResult = formMain.ShowDialog();
if (dialogResult == DialogResult.Cancel) {
Environment.Exit(0);
} else {
continue;
}
} else {
Environment.Exit(0);
}
}
}
public FormAdvanceSettings(User acount, TabControl tabControl)
{
this.tabControl = tabControl;
this.account = acount;
InitializeComponent();
}
public DataTable getTrackerAccData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
{
DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
DataTable dataTableIAccData = this.getReportTable(ReportType.ACC);
dataTableIAccData.TableName = tracker.DatabaseName;
try {
bool accStatusNow = false;
bool accStatusBefore = false;
int index = 0;
double speed = 0;
double speedDivisor = 0;
double speedAccumulator = 0;
double speedMax = 0;
double speedAverage = 0;
double distanceCovered = 0;
double distance = 0;
String geofenceFrom = "";
String geofenceTo = "";
DateTime dateTimeRunningFrom = new DateTime();
DateTime dateTimeRunningTo = new DateTime();
double latitudeTo = 0;
double latitudeFrom = 0;
double longitudeTo = 0;
double longitudeFrom = 0;
TimeSpan timeSpan;
for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {
//Initialized
accStatusNow = (bool)dataTableHistoricalData.Rows[no]["ACC"];
speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
distance = (double)dataTableHistoricalData.Rows[no]["Distance"];
//OneTime
if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
dateTimeRunningFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceFrom = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
accStatusBefore = accStatusNow;
}
if (accStatusBefore != accStatusNow) {
index++;
dateTimeRunningTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
timeSpan = dateTimeRunningTo - dateTimeRunningFrom;
speedAverage = speedAccumulator / 1;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowAccData = dataTableIAccData.NewRow();
dataRowAccData["No"] = index;
dataRowAccData["Status"] = accStatusBefore;
dataRowAccData["DateTimeFrom"] = dateTimeRunningFrom;
dataRowAccData["DateTimeTo"] = dateTimeRunningTo;
dataRowAccData["LatitudeFrom"] = latitudeFrom;
dataRowAccData["LatitudeTo"] = latitudeTo;
dataRowAccData["LongitudeFrom"] = longitudeFrom;
dataRowAccData["LongitudeTo"] = longitudeTo;
dataRowAccData["Time"] = timeSpan;
dataRowAccData["Distance"] = distanceCovered;
dataRowAccData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowAccData["Cost"] = (double)dataRowAccData["Fuel"] / Settings.Default.fuelLiterToCost;
if (geofenceTo.Equals(geofenceFrom)) {
dataRowAccData["Geofences"] = geofenceFrom;
} else {
dataRowAccData["Geofences"] = geofenceFrom + " to " + geofenceTo;
}
dataRowAccData["SpeedMax"] = speedMax;
dataRowAccData["SpeedAve"] = speedAverage;
speedMax = 0;
distanceCovered = 0;
dateTimeRunningFrom = dateTimeRunningTo;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
geofenceFrom = geofenceTo;
dataTableIAccData.Rows.Add(dataRowAccData);
accStatusBefore = accStatusNow;
}
if (no == dataTableHistoricalData.Rows.Count - 1) {
index++;
dateTimeRunningTo = dateTimeDateTo;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
timeSpan = dateTimeRunningTo - dateTimeRunningFrom;
speedAverage = speedAccumulator / 1;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowAccData = dataTableIAccData.NewRow();
dataRowAccData["No"] = index;
dataRowAccData["Status"] = accStatusNow;
dataRowAccData["DateTimeFrom"] = dateTimeRunningFrom;
dataRowAccData["DateTimeTo"] = dateTimeRunningTo;
dataRowAccData["LatitudeFrom"] = latitudeFrom;
dataRowAccData["LatitudeTo"] = latitudeTo;
dataRowAccData["LongitudeFrom"] = longitudeFrom;
dataRowAccData["LongitudeTo"] = longitudeTo;
dataRowAccData["Time"] = timeSpan;
dataRowAccData["Distance"] = distanceCovered;
dataRowAccData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowAccData["Cost"] = (double)dataRowAccData["Fuel"] / Settings.Default.fuelLiterToCost;
if (geofenceTo.Equals(geofenceFrom)) {
dataRowAccData["Geofences"] = geofenceFrom;
} else {
dataRowAccData["Geofences"] = geofenceFrom + " to " + geofenceTo;
}
dataRowAccData["SpeedMax"] = speedMax;
dataRowAccData["SpeedAve"] = speedAverage;
speedMax = 0;
distanceCovered = 0;
dateTimeRunningFrom = dateTimeRunningTo;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
geofenceFrom = geofenceTo;
dataTableIAccData.Rows.Add(dataRowAccData);
accStatusBefore = accStatusNow;
}
//Accumulators : SpeedAve, SpeedMax, Distance
speedDivisor++;
speedAccumulator += speed;
if (speed > speedMax) {
speedMax = speed;
}
distanceCovered += distance;
}
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
}
return dataTableIAccData;
}
public void fillUsers(Company company, User user)
{
List<User> users = new List<User>();
try {
mysqlConnection = new MySqlConnection(database.getConnectionString());
mysqlConnection.Open();
string sql = "";
if (user.AccessLevel == 1 || user.AccessLevel == 2) {
sql =
"SELECT * " +
"FROM cmp_" + company.DatabaseName + ".usrs " +
"WHERE cmp_" + company.DatabaseName + ".usrs.upriv >= " + user.AccessLevel.ToString() + ";";
} else {
sql =
"SELECT * " +
"FROM cmp_" + company.DatabaseName + ".usrs " +
"WHERE cmp_" + company.DatabaseName + ".usrs.upriv = " + user.AccessLevel.ToString() +
" and cmp_" + company.DatabaseName + ".usrs.uname = " + user.Username + ";";
}
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (!mySqlDataReader.HasRows) {
mySqlDataReader.Dispose();
throw new QueryException(1, "Users table is empty.");
} else {
while (mySqlDataReader.Read()) {
User userSubordinate = new User();
userSubordinate.Id = mySqlDataReader.GetInt32("uid");
userSubordinate.Username = mySqlDataReader.GetString("uname");
userSubordinate.Password = mySqlDataReader.GetString("upass");
userSubordinate.Email = mySqlDataReader.GetString("uemail");
userSubordinate.Main = mySqlDataReader.GetString("umain");
userSubordinate.AccessLevel = int.Parse(mySqlDataReader.GetString("upriv"));
userSubordinate.Timezone = mySqlDataReader.GetString("utimezone");
userSubordinate.IsActive = mySqlDataReader.GetString("uactive").Equals("1");
userSubordinate.DatabaseName = mySqlDataReader.GetString("udbs");
if (!String.IsNullOrEmpty(mySqlDataReader.GetString("uexpiredate"))) {
string dateTime = (mySqlDataReader.GetString("uexpiredate"));
if (!String.IsNullOrEmpty(dateTime)) {
DateTime parsedDate = SubStandard.dateTime(dateTime);
userSubordinate.DateTimeExpired = parsedDate;
}
} else {
userSubordinate.DateTimeExpired = new DateTime(2050, 01, 01);
}
if (!String.IsNullOrEmpty(mySqlDataReader.GetString("ucreatedate"))) {
string dateTime = mySqlDataReader.GetString("ucreatedate");
if (!String.IsNullOrEmpty(dateTime)) {
DateTime parsedDate = SubStandard.dateTime(dateTime);
userSubordinate.DateTimeCreated = parsedDate;
}
} else {
userSubordinate.DateTimeCreated = new DateTime(2010, 01, 01);
}
users.Add(userSubordinate);
}
mySqlDataReader.Dispose();
}
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
mysqlConnection.Close();
}
company.Users = users;
}
public void getUser(Company company, User user)
{
try {
mysqlConnection = new MySqlConnection(database.getConnectionString());
mysqlConnection.Open();
string sql =
"SELECT * " +
"FROM cmp_" + company.DatabaseName + ".usrs " +
"WHERE " +
"cmp_" + company.DatabaseName + ".usrs.uname = @sUsername AND " +
"cmp_" + company.DatabaseName + ".usrs.upass = @sPassword;";
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
mySqlCommand.Parameters.AddWithValue("@sUsername", user.Username);
String coded = Cryptography.md5(user.Password);
mySqlCommand.Parameters.AddWithValue("@sPassword", coded);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (!mySqlDataReader.HasRows) {
mySqlDataReader.Dispose();
throw new QueryException(1, "Username or Password does not exist.");
} else {
mySqlDataReader.Read();
user.Id = mySqlDataReader.GetInt32("uid");
user.Username = mySqlDataReader.GetString("uname");
//user.Password = mySqlDataReader.GetString("upass");
user.Email = mySqlDataReader.GetString("uemail");
user.Main = mySqlDataReader.GetString("umain");
user.AccessLevel = int.Parse(mySqlDataReader.GetString("upriv"));
user.Timezone = mySqlDataReader.GetString("utimezone");
user.IsActive = mySqlDataReader.GetString("uactive").Equals("1");
user.DatabaseName = mySqlDataReader.GetString("udbs");
if (!String.IsNullOrEmpty(mySqlDataReader.GetString("uexpiredate"))) {
string dateTime = (mySqlDataReader.GetString("uexpiredate"));
if (!String.IsNullOrEmpty(dateTime)) {
DateTime parsedDate = SubStandard.dateTime(dateTime);
user.DateTimeExpired = parsedDate;
}
} else {
user.DateTimeExpired = new DateTime(2050, 01, 01);
}
if (!String.IsNullOrEmpty(mySqlDataReader.GetString("ucreatedate"))) {
string dateTime = mySqlDataReader.GetString("ucreatedate");
if (!String.IsNullOrEmpty(dateTime)) {
DateTime parsedDate = SubStandard.dateTime(dateTime);
user.DateTimeCreated = parsedDate;
}
} else {
user.DateTimeCreated = new DateTime(2010, 01, 01);
}
mySqlDataReader.Dispose();
}
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
mysqlConnection.Close();
}
}
public DataTable getTrackersHistorical(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, List<Tracker> trackerList)
{
DataTable dataTable = this.getReportTable(ReportType.TRACKERS_HISTORICAL);
try {
int index = 0;
foreach (Tracker tracker in trackerList) {
index++;
DataTable dataTableHistorical = (this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker));
if (dataTableHistorical.Rows.Count <= 0) {
DataRow dataRow = dataTable.NewRow();
dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
dataRow["DriverName"] = tracker.DriverName;
dataRow["VehicleModel"] = tracker.VehicleModel;
dataRow["OwnerName"] = tracker.OwnerName;
dataRow["SimNumber"] = tracker.SimNumber;
dataRow["SimImei"] = tracker.SimImei;
dataRow["TrackerImei"] = tracker.TrackerImei;
dataTable.Rows.Add(dataRow);
DataRow dataRowEmpty = dataTable.NewRow();
dataTable.Rows.Add(dataRowEmpty);
} else {
foreach (DataRow dataRowItem in dataTableHistorical.Rows) {
DataRow dataRow = dataTable.NewRow();
dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
dataRow["DriverName"] = tracker.DriverName;
dataRow["VehicleModel"] = tracker.VehicleModel;
dataRow["OwnerName"] = tracker.OwnerName;
dataRow["SimNumber"] = tracker.SimNumber;
dataRow["SimImei"] = tracker.SimImei;
dataRow["TrackerImei"] = tracker.TrackerImei;
dataRow["No"] = dataRowItem["No"];
dataRow["DateTime"] = dataRowItem["DateTime"];
dataRow["Latitude"] = dataRowItem["Latitude"];
dataRow["Longitude"] = dataRowItem["Longitude"];
dataRow["Speed"] = dataRowItem["Speed"];
dataRow["Mileage"] = dataRowItem["Mileage"];
dataRow["Distance"] = dataRowItem["Distance"];
dataRow["Fuel"] = dataRowItem["Fuel"];
dataRow["Cost"] = dataRowItem["Cost"];
dataRow["Altitude"] = dataRowItem["Altitude"];
dataRow["Degrees"] = dataRowItem["Degrees"];
dataRow["Direction"] = dataRowItem["Direction"];
dataRow["No"] = dataRowItem["No"];
dataRow["GpsSatellites"] = dataRowItem["GpsSatellites"];
dataRow["Latitude"] = dataRowItem["Latitude"];
dataRow["GsmSignal"] = dataRowItem["GsmSignal"];
dataRow["EventCode"] = dataRowItem["EventCode"];
dataRow["Geofence"] = dataRowItem["Geofence"];
dataRow["ACC"] = dataRowItem["ACC"];
dataRow["SOS"] = dataRowItem["SOS"];
dataRow["OverSpeed"] = dataRowItem["OverSpeed"];
dataRow["Battery"] = dataRowItem["Battery"];
dataRow["BatteryVoltage"] = dataRowItem["BatteryVoltage"];
dataRow["ExternalVoltage"] = dataRowItem["ExternalVoltage"];
dataTable.Rows.Add(dataRow);
}
DataRow dataRowEmpty = dataTable.NewRow();
dataTable.Rows.Add(dataRowEmpty);
}
}
return dataTable;
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
}
}
public DataTable getTrackersGeofence(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, List<Tracker> trackerList)
{
DataTable dataTable = this.getReportTable(ReportType.TRACKERS_GEOFENCE);
//dataTable.TableName = "";
try {
int index = 0;
foreach (Tracker tracker in trackerList) {
index++;
DataTable dataTableGeofence = (this.getTrackerGeofence(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker));
if (dataTableGeofence.Rows.Count <= 0) {
DataRow dataRow = dataTable.NewRow();
dataRow["No"] = index;
dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
dataRow["DriverName"] = tracker.DriverName;
dataRow["DeviceImei"] = tracker.TrackerImei;
dataRow["DateTimeFrom"] = dateTimeDateFrom;
dataRow["DateTimeTo"] = dateTimeDateTo;
dataRow["SpeedAve"] = 0;
dataRow["SpeedMax"] = 0;
dataRow["Distance"] = 0;
dataRow["Fuel"] = 0;
dataRow["Cost"] = 0;
dataRow["Geofence"] = "";
dataTable.Rows.Add(dataRow);
} else {
DataRow dataRow = dataTable.NewRow();
dataRow["No"] = index;
dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
dataRow["DriverName"] = tracker.DriverName;
dataRow["DeviceImei"] = tracker.TrackerImei;
dataRow["DateTimeFrom"] = dateTimeDateFrom;
dataRow["DateTimeTo"] = dateTimeDateTo;
double speedAve = 0;
if (dataTableGeofence.Rows.Count > 0)
speedAve = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["SpeedAve"]) / dataTableGeofence.Rows.Count;
dataRow["SpeedAve"] = speedAve;
double speedMax = Converter.dataTableColumnMaxValue(dataTableGeofence, dataTableGeofence.Columns["SpeedMax"]);
dataRow["SpeedMax"] = speedMax;
dataRow["Distance"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Distance"]);
dataRow["Fuel"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Fuel"]);
dataRow["Cost"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Cost"]);
dataRow["Geofence"] = Converter.dataTableColumnAppend(dataTableGeofence, dataTableGeofence.Columns["Geofence"]);
dataTable.Rows.Add(dataRow);
}
}
return dataTable;
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
}
}
public DataTable getTrackerOverSpeedData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
{
DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
DataTable dataTableOverSpeedData = this.getReportTable(ReportType.OVERSPEED);
dataTableOverSpeedData.TableName = tracker.DatabaseName;
try {
bool overSpeedStatusNow = false;
int index = 0;
String geofence = "";
foreach (DataRow dataRowNow in dataTableHistoricalData.Rows) {
overSpeedStatusNow = (bool)dataRowNow["OverSpeed"];
if (overSpeedStatusNow) {
index++;
geofence = ((dataRowNow["Geofence"] == System.DBNull.Value) ? "" : (string)dataRowNow["Geofence"]);
DataRow dataRowOverspeedData = dataTableOverSpeedData.NewRow();
dataRowOverspeedData["No"] = index;
dataRowOverspeedData["Status"] = overSpeedStatusNow;
dataRowOverspeedData["DateTime"] = (DateTime)dataRowNow["DateTime"];
dataRowOverspeedData["Latitude"] = (double)dataRowNow["Latitude"];
dataRowOverspeedData["Longitude"] = (double)dataRowNow["Longitude"];
dataRowOverspeedData["Speed"] = (int)dataRowNow["Speed"];
dataRowOverspeedData["Mileage"] = (double)dataRowNow["Mileage"];
dataRowOverspeedData["Geofence"] = geofence;
dataTableOverSpeedData.Rows.Add(dataRowOverspeedData);
}
}
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
}
return dataTableOverSpeedData;
}
//Based Reports
public int getTrackerHistoricalDataCount(User account, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, Tracker tracker)
{
DataTable dataTable = new DataTable();
try {
mysqlConnection.Open();
double timestampFrom = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateFrom);
double timestampTo = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateTo);
string sql = "";
if (timestampFrom > timestampTo) {
sql =
"SELECT COUNT(*) " +
"FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
"WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampTo.ToString() + " " +
"AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampFrom.ToString() + " " +
"ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id ASC;";
} else {
sql =
"SELECT COUNT(*) " +
"FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
"WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampTo.ToString() + " " +
"AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampFrom.ToString() + " " +
"ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id ASC;";
}
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
int count = Convert.ToInt32(mySqlCommand.ExecuteScalar());
return count;
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw mySqlException;
} catch (Exception exception) {
throw exception;
} finally {
mysqlConnection.Close();
}
}
public DataTable getTrackerHistoricalData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
{
DataTable dataTable = this.getReportTable(ReportType.HISTORICAL);
dataTable.TableName = tracker.DatabaseName;
try {
mysqlConnection.Open();
double timestampFrom = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateFrom);
double timestampTo = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateTo);
string sql = "";
if (timestampFrom > timestampTo) {
sql =
"SELECT " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lat, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lng, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_ori, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_speed, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_mileage, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_data " +
"FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
"WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampTo.ToString() + " " +
"AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampFrom.ToString() + " " +
"ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time ASC LIMIT " + limit.ToString() + " OFFSET " + offset.ToString() + ";";
} else {
sql =
"SELECT " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lat, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lng, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_ori, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_speed, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_mileage, " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_data " +
"FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
"WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampTo.ToString() + " " +
"AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampFrom.ToString() + " " +
"ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time ASC LIMIT " + limit.ToString() + " OFFSET " + offset.ToString() + ";";
}
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (!mySqlDataReader.HasRows) {
//throw new QueryException(1, "Tracker data is empty.");
return dataTable;
} else {
int index = offset;
DataRow dataRow;
DataRow dataRowBefore = dataTable.NewRow();
while (mySqlDataReader.Read()) {
index++;
dataRow = dataTable.NewRow();
dataRow["No"] = index;
dataRow["DateTime"] = Converter.unixTimeStampToDateTime(double.Parse((string)mySqlDataReader["gm_time"]));
double latitude = double.Parse((string)mySqlDataReader["gm_lat"]);
double longitude = double.Parse((string)mySqlDataReader["gm_lng"]);
dataRow["Latitude"] = latitude;
dataRow["Longitude"] = longitude;
dataRow["Speed"] = int.Parse((string)mySqlDataReader["gm_speed"]);
dataRow["Mileage"] = double.Parse((string)mySqlDataReader["gm_mileage"]);
if ((double)dataRow["Mileage"] < 0) {
dataRow["Mileage"] = ((double)dataRow["Mileage"]) * -1;
}
if (dataRowBefore["Mileage"] != System.DBNull.Value) {
double mileAgeObtained = (double)dataRow["Mileage"] - (double)dataRowBefore["Mileage"];
dataRow["Distance"] = mileAgeObtained;
dataRow["Fuel"] = mileAgeObtained / Settings.Default.fuelLiterToKilometer;
dataRow["Cost"] = ((double)dataRow["Fuel"]) * Settings.Default.fuelLiterToCost;
} else {
dataRow["Distance"] = 0.00f;
dataRow["Fuel"] = 0.00f;
dataRow["Cost"] = 0.00f;
}
dataRow["Degrees"] = int.Parse((string)mySqlDataReader["gm_ori"]);
dataRow["Direction"] = Converter.degreesToCardinalDetailed(double.Parse((string)mySqlDataReader["gm_ori"]));
//1, // (0)
//35, //Event code(Decimal)
//11, //Number of satellites(Decimal)
//26, //GSM signal status(Decimal)
//17160691, //Mileage(Decimal)unit: meter
//0.7, //hpos accuracy(Decimal)
//18, //Altitude(Decimal)unit: meter
//18661240, //Run time(Decimal)unit: second
//427|2|0078|283F, //Base station information(binary|binary|hex|hex) (8)
//==============================================0200
//0,0,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (9)
//0,1,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (17)
//==============================================
//000B,0000,0000,0A6E,0434, //Analog input value (25)
//00000001 //System mark
string gmData = (string)mySqlDataReader["gm_data"];
string[] data = gmData.Split(',');
dataRow["EventCode"] = Enum.GetName(typeof(EventCode), (EventCode)int.Parse(data[1]));
dataRow["GpsSatellites"] = int.Parse(data[2]);
dataRow["GsmSignal"] = int.Parse(data[3]);
dataRow["Altitude"] = int.Parse(data[6]);
dataRow["ACC"] = (int.Parse(data[18]) == 1) ? true : false;
dataRow["SOS"] = (int.Parse(data[17]) == 1) ? true : false;
dataRow["OverSpeed"] = ((int)dataRow["Speed"] > tracker.SpeedLimit) ? true : false;
//Geofence
Coordinate Coordinate = new Coordinate(latitude, longitude);
//MapTools mapTools = new MapTools();
if (company.Geofences != null) {
foreach (Geofence geofence in company.Geofences) {
if (Geofence.isPointInPolygon(geofence, Coordinate)) {
if (String.IsNullOrEmpty(geofence.Name)) {
dataRow["Geofence"] = "";
} else {
dataRow["Geofence"] = geofence.Name;
}
}
}
}
double batteryStrength = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier);
batteryStrength = ((batteryStrength - 2114f) * (100f / 492f));//*100.0;
if (batteryStrength > 100) {
batteryStrength = 100f;
} else if (batteryStrength < 0) {
batteryStrength = 0;
}
double batteryVoltage = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier);
batteryVoltage = (batteryVoltage * 3 * 2) / 1024;
double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier);
externalVoltage = (externalVoltage * 3 * 16) / 1024;
dataRow["Battery"] = batteryStrength;
dataRow["BatteryVoltage"] = batteryVoltage;
dataRow["ExternalVoltage"] = externalVoltage;
dataTable.Rows.Add(dataRow);
dataRowBefore = dataRow;
}
mySqlDataReader.Close();
return dataTable;
}
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw mySqlException;
} catch (Exception exception) {
throw exception;
} finally {
mysqlConnection.Close();
}
}
public DataTable getTrackerGeofence(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
{
DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
DataTable dataTableGeofenceData = this.getReportTable(ReportType.GEOFENCE);
dataTableGeofenceData.TableName = tracker.DatabaseName;
try {
string geofenceStatusNow = "";
string geofenceStatusBefore = "";
bool acc = false;
int index = 0;
double speed = 0;
double speedDivisor = 0;
double speedAccumulator = 0;
double speedMax = 0;
double speedAverage = 0;
double distance = 0;
double distanceCovered = 0;
DateTime dateTimeGeofenceFrom = new DateTime();
DateTime dateTimeGeofenceTo = new DateTime();
double latitudeTo = 0;
double latitudeFrom = 0;
double longitudeTo = 0;
double longitudeFrom = 0;
TimeSpan timeSpan;
for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {
acc = (bool)dataTableHistoricalData.Rows[no]["ACC"];
speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
distance = (double)dataTableHistoricalData.Rows[no]["Distance"];
if (!(dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value)) {
geofenceStatusNow = (string)dataTableHistoricalData.Rows[no]["Geofence"];
} else {
geofenceStatusNow = "-";
}
//OneTime
if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
dateTimeGeofenceFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceStatusBefore = geofenceStatusNow;
}
if (geofenceStatusBefore != geofenceStatusNow) {
index++;
dateTimeGeofenceTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
timeSpan = dateTimeGeofenceTo - dateTimeGeofenceFrom;
speedAverage = speedAccumulator / speedDivisor;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowGeofenceData = dataTableGeofenceData.NewRow();
dataRowGeofenceData["No"] = index;
dataRowGeofenceData["DateTimeFrom"] = dateTimeGeofenceFrom;
dataRowGeofenceData["DateTimeTo"] = dateTimeGeofenceTo;
dataRowGeofenceData["LatitudeFrom"] = latitudeFrom;
dataRowGeofenceData["LatitudeTo"] = latitudeTo;
dataRowGeofenceData["LongitudeFrom"] = longitudeFrom;
dataRowGeofenceData["LongitudeTo"] = longitudeTo;
dataRowGeofenceData["Time"] = timeSpan;
dataRowGeofenceData["Distance"] = distanceCovered;
dataRowGeofenceData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowGeofenceData["Cost"] = ((double)dataRowGeofenceData["Fuel"]) / Settings.Default.fuelLiterToCost;
dataRowGeofenceData["Geofence"] = geofenceStatusBefore;
dataRowGeofenceData["SpeedMax"] = speedMax;
dataRowGeofenceData["SpeedAve"] = speedAverage;
dataRowGeofenceData["Status"] = (geofenceStatusBefore == "-" ? false : true);
speedMax = 0;
distanceCovered = 0;
dateTimeGeofenceFrom = dateTimeGeofenceTo;
geofenceStatusBefore = geofenceStatusNow;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
dataTableGeofenceData.Rows.Add(dataRowGeofenceData);
}
if (no == dataTableHistoricalData.Rows.Count - 1) {
index++;
dateTimeGeofenceTo = dateTimeDateTo;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
timeSpan = dateTimeGeofenceTo - dateTimeGeofenceFrom;
speedAverage = speedAccumulator / 1;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowGeofenceData = dataTableGeofenceData.NewRow();
dataRowGeofenceData["No"] = index;
dataRowGeofenceData["DateTimeFrom"] = dateTimeGeofenceFrom;
dataRowGeofenceData["DateTimeTo"] = dateTimeGeofenceTo;
dataRowGeofenceData["LatitudeFrom"] = latitudeFrom;
dataRowGeofenceData["LatitudeTo"] = latitudeTo;
dataRowGeofenceData["LongitudeFrom"] = longitudeFrom;
dataRowGeofenceData["LongitudeTo"] = longitudeTo;
dataRowGeofenceData["Time"] = timeSpan;
dataRowGeofenceData["Distance"] = distanceCovered;
dataRowGeofenceData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowGeofenceData["Cost"] = (double)dataRowGeofenceData["Fuel"] / Settings.Default.fuelLiterToCost;
dataRowGeofenceData["Geofence"] = geofenceStatusNow;
dataRowGeofenceData["SpeedMax"] = speedMax;
dataRowGeofenceData["SpeedAve"] = speedAverage;
dataRowGeofenceData["Status"] = (geofenceStatusBefore == "-" ? false : true);
speedMax = 0;
distanceCovered = 0;
dateTimeGeofenceFrom = dateTimeGeofenceTo;
geofenceStatusBefore = geofenceStatusNow;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
dataTableGeofenceData.Rows.Add(dataRowGeofenceData);
}
//Accumulators : SpeedAve, SpeedMax, Distance
speedDivisor++;
speedAccumulator += speed;
if (speed > speedMax) {
speedMax = speed;
}
distanceCovered += distance;
}
return dataTableGeofenceData;
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
}
}
public DataTable getTrackerExternalPowerCutData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
{
DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
DataTable dataTableExternalPowerCutData = this.getReportTable(ReportType.EXTERNAL_POWER_CUT);
dataTableExternalPowerCutData.TableName = tracker.DatabaseName;
try {
bool externalPowerStatusNow = false;
bool externalPowerStatusBefore = false;
EventCode eventCode = EventCode.TRACK_BY_TIME_INTERVAL;
int index = 0;
double speed = 0;
double speedDivisor = 0;
double speedAccumulator = 0;
double speedMax = 0;
double speedAverage = 0;
double distanceCovered = 0;
double distance = 0;
String geofenceFrom = "";
String geofenceTo = "";
DateTime dateTimeRunningFrom = new DateTime();
DateTime dateTimeRunningTo = new DateTime();
double latitudeTo = 0;
double latitudeFrom = 0;
double longitudeTo = 0;
double longitudeFrom = 0;
TimeSpan timeSpan;
for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {
//Initialized
eventCode = (dataTableHistoricalData.Rows[no]["EventCode"] == System.DBNull.Value) ?
EventCode.TRACK_BY_TIME_INTERVAL : (EventCode)Enum.Parse(typeof(EventCode), (string)dataTableHistoricalData.Rows[no]["EventCode"], true);
speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
distance = (double)dataTableHistoricalData.Rows[no]["Distance"];
double externalVolt = (double)dataTableHistoricalData.Rows[no]["ExternalVoltage"];
//Conditions
if (eventCode == EventCode.EXTERNAL_BATTERY_CUT || externalVolt < 12) {
externalPowerStatusNow = true;
}
if (eventCode == EventCode.EXTERNAL_BATTERY_ON || externalVolt >= 12) {
externalPowerStatusNow = false;
}
//OneTime
if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
dateTimeRunningFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceFrom = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
externalPowerStatusBefore = externalPowerStatusNow;
}
if (externalPowerStatusBefore != externalPowerStatusNow) {
index++;
dateTimeRunningTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
timeSpan = dateTimeRunningTo - dateTimeRunningFrom;
speedAverage = speedAccumulator / speedDivisor;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowExternalPowerCutData = dataTableExternalPowerCutData.NewRow();
dataRowExternalPowerCutData["No"] = index;
dataRowExternalPowerCutData["Status"] = externalPowerStatusBefore;
dataRowExternalPowerCutData["DateTimeFrom"] = dateTimeRunningFrom;
dataRowExternalPowerCutData["DateTimeTo"] = dateTimeRunningTo;
dataRowExternalPowerCutData["LatitudeFrom"] = latitudeFrom;
dataRowExternalPowerCutData["LatitudeTo"] = latitudeTo;
dataRowExternalPowerCutData["LongitudeFrom"] = longitudeFrom;
dataRowExternalPowerCutData["LongitudeTo"] = longitudeTo;
dataRowExternalPowerCutData["Time"] = timeSpan;
dataRowExternalPowerCutData["Distance"] = distanceCovered;
dataRowExternalPowerCutData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowExternalPowerCutData["Cost"] = ((double)dataRowExternalPowerCutData["Fuel"]) / Settings.Default.fuelLiterToCost;
if (geofenceTo.Equals(geofenceFrom)) {
dataRowExternalPowerCutData["Geofences"] = geofenceFrom;
} else {
dataRowExternalPowerCutData["Geofences"] = geofenceFrom + " to " + geofenceTo;
}
dataRowExternalPowerCutData["SpeedMax"] = speedMax;
dataRowExternalPowerCutData["SpeedAve"] = speedAverage;
speedMax = 0;
distanceCovered = 0;
dateTimeRunningFrom = dateTimeRunningTo;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
dataTableExternalPowerCutData.Rows.Add(dataRowExternalPowerCutData);
externalPowerStatusBefore = externalPowerStatusNow;
}
if (no == dataTableHistoricalData.Rows.Count - 1) {
index++;
dateTimeRunningTo = dateTimeDateTo;// (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
timeSpan = dateTimeRunningTo - dateTimeRunningFrom;
speedAverage = speedAccumulator / 1;
speedAccumulator = 0;
speedDivisor = 0;
DataRow dataRowExternalPowerCutData = dataTableExternalPowerCutData.NewRow();
dataRowExternalPowerCutData["No"] = index;
dataRowExternalPowerCutData["Status"] = externalPowerStatusNow;
dataRowExternalPowerCutData["DateTimeFrom"] = dateTimeRunningFrom;
dataRowExternalPowerCutData["DateTimeTo"] = dateTimeRunningTo;
dataRowExternalPowerCutData["LatitudeFrom"] = latitudeFrom;
dataRowExternalPowerCutData["LatitudeTo"] = latitudeTo;
dataRowExternalPowerCutData["LongitudeFrom"] = longitudeFrom;
dataRowExternalPowerCutData["LongitudeTo"] = longitudeTo;
dataRowExternalPowerCutData["Time"] = timeSpan;
dataRowExternalPowerCutData["Distance"] = distanceCovered;
dataRowExternalPowerCutData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
dataRowExternalPowerCutData["Cost"] = (double)dataRowExternalPowerCutData["Fuel"] / Settings.Default.fuelLiterToCost;
if (geofenceTo.Equals(geofenceFrom)) {
dataRowExternalPowerCutData["Geofences"] = geofenceFrom;
} else {
dataRowExternalPowerCutData["Geofences"] = geofenceFrom + " to " + geofenceTo;
}
dataRowExternalPowerCutData["SpeedMax"] = speedMax;
dataRowExternalPowerCutData["SpeedAve"] = speedAverage;
speedMax = 0;
distanceCovered = 0;
dateTimeRunningFrom = dateTimeRunningTo;
longitudeFrom = longitudeTo;
latitudeFrom = latitudeTo;
dataTableExternalPowerCutData.Rows.Add(dataRowExternalPowerCutData);
externalPowerStatusBefore = externalPowerStatusNow;
}
//Accumulators : SpeedAve, SpeedMax, Distance
speedDivisor++;
speedAccumulator += speed;
if (speed > speedMax) {
speedMax = speed;
}
distanceCovered += distance;
}
} catch (QueryException queryException) {
throw queryException;
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
}
return dataTableExternalPowerCutData;
}
public DialogLogin(Company company, User user)
{
InitializeComponent();
this.company = company;
this.user = user;
}