public TrackerItem(Tracker tracker) {
this.tracker = tracker;
}
//public DataTable getAllCompanies() {
// DataTable dataTable = new DataTable();
// try {
// mysqlConnection.Open();
// string sql =
// "SELECT * " +
// "FROM dbt_tracking_master.cmps;";
// MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
// MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
// if (!mySqlDataReader.HasRows) {
// throw new QueryException(1, "Companies's Collection is empty.");
// } else {
// dataTable.Load(mySqlDataReader);
// }
// } catch (QueryException queryException) {
// throw queryException;
// } catch (MySqlException mySqlException) {
// throw new QueryException(1, mySqlException.Message);
// } catch (Exception exception) {
// throw new QueryException(1, exception.Message);
// } finally {
// mysqlConnection.Close();
// }
// return dataTable;
//}
//public TrackerData getTrackerLatestData (Company company, Tracker tracker) {
// TrackerData trackerData = new TrackerData();
// trackerData.Tracker = tracker;
// try {
// mysqlConnection.Open();
// string sql =
// "SELECT * " +
// "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
// "ORDER BY " +
// "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time " +
// "DESC limit 1;";
// //"SELECT * " +
// // "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
// // "ORDER BY " +
// // "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id " +
// // "DESC limit 1;";
// MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
// MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
// if (!mySqlDataReader.HasRows) {
// trackerData.IsDataEmpty = true;
// return trackerData;
// } else {
// mySqlDataReader.Read();
// trackerData.IsDataEmpty = false;
// trackerData.Id = mySqlDataReader.GetInt32("gm_id");
// double dateTime = double.Parse(mySqlDataReader.GetString("gm_time"));
// trackerData.DateTime = Parser.UnixTime.toDateTime(dateTime);
// double latitude = double.Parse(mySqlDataReader.GetString("gm_lat"));
// double longitude = double.Parse(mySqlDataReader.GetString("gm_lng"));
// trackerData.Coordinate = new Coordinate(latitude, longitude);
// trackerData.Speed = int.Parse(mySqlDataReader.GetString("gm_speed"));
// trackerData.Degrees = int.Parse(mySqlDataReader.GetString("gm_ori"));
// trackerData.Direction = Direction.degreesToCardinalDetailed(double.Parse(mySqlDataReader.GetString("gm_ori")));
// //trackerData.Mileage = double.Parse(mySqlDataReader.GetString("gm_mileage"));
// double deviceMileage = double.Parse(mySqlDataReader.GetString("gm_mileage"));
// double carMileage = (tracker.MileageInitial);
// trackerData.Mileage = deviceMileage + carMileage;
// //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(',');
// trackerData.EventCode = (EventCode)int.Parse(data[1]);
// trackerData.GpsSatellites = int.Parse(data[2]);
// trackerData.GsmSignal = int.Parse(data[3]);
// trackerData.Altitude = int.Parse(data[6]);
// trackerData.ACC = (int.Parse(data[18]) == 1) ? true : false;
// trackerData.SOS = (int.Parse(data[17]) == 1) ? true : false;
// trackerData.OverSpeed = ((int)trackerData.Speed > tracker.SpeedLimit) ? true : false;
// //Geofence
// Coordinate coordinate = new Coordinate(latitude, longitude);
// foreach (Geofence geofence in company.Geofences) {
// if (Geofence.isPointInPolygon(geofence, coordinate)) {
// trackerData.Geofence = geofence;
// }
// };
// double batteryStrength = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier);
// batteryStrength = ((batteryStrength - 2114f) * (100f / 492f));//*100.0;
// batteryStrength = Math.Round(batteryStrength, 2);
// 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;
// batteryVoltage = Math.Round(batteryVoltage, 2);
// double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier);
// externalVoltage = (externalVoltage * 3 * 16) / 1024;
// externalVoltage = Math.Round(externalVoltage, 2);
// trackerData.Battery = batteryStrength;
// trackerData.BatteryVoltage = batteryVoltage;
// trackerData.ExternalVoltage = externalVoltage;
// return trackerData;
// }
// //} catch (QueryException queryException) {
// //throw queryException;
// //} catch (MySqlException mySqlException) {
// //throw new QueryException(1, mySqlException.Message);
// } catch (Exception exception) {
// Debug.Write(exception);
// //throw new QueryException(1, exception.Message);
// return trackerData;
// } finally {
// mysqlConnection.Close();
// }
//}
public TrackerData getTrackerLatestData (Company company, Tracker tracker, Server server) {
TrackerData trackerData = new TrackerData();
trackerData.Tracker = tracker;
try {
WebRequest request = WebRequest.Create("http://" + server.Ip + "/connect/get_realtime.php?url=http://" + server.Ip + ":" + server.PortHttp + "/?id=" + tracker.TrackerImei);
// If required by the server, set the credentials.
request.Credentials = CredentialCache.DefaultCredentials;
// Get the response.
WebResponse response = request.GetResponse();
// Display the status.
//Console.WriteLine(((HttpWebResponse)response).StatusDescription);
// Get the stream containing content returned by the server.
Stream dataStream = response.GetResponseStream();
// Open the stream using a StreamReader for easy access.
StreamReader reader = new StreamReader(dataStream);
// Read the content.
string responseFromServer = reader.ReadToEnd();
// Display the content.
//Console.WriteLine(responseFromServer);
// Clean up the streams and the response.
reader.Close();
response.Close();
//===========================================
//===========================================
responseFromServer = responseFromServer.Trim();
if (responseFromServer == "1") {
trackerData.IsDataEmpty = true;
return trackerData;
} else {
dynamic jsonData = (dynamic)JsonConvert.DeserializeObject<dynamic>(responseFromServer);
trackerData.IsDataEmpty = false;
trackerData.Tracker.TrackerImei = (string)jsonData.gm_unit;
double dateTime = double.Parse((string)jsonData.gm_time);
trackerData.DateTime = Parser.UnixTime.toDateTime(dateTime);
//DateTime dateTime1 = Parser.UnixTime.toDateTime(dateTime);
//TimeSpan timeSpan = dateTime1.Subtract(DateTime.Now);
//if (timeSpan.Hours > 6) {
// trackerData.DateTime = dateTime1;
//} else {
// trackerData.DateTime = DateTime.Now;
//}
double latitude = 0;
double.TryParse((string)jsonData.gm_lat, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out latitude);
double longitude = 0;
double.TryParse((string)jsonData.gm_lng, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out longitude);
trackerData.Coordinate = new Coordinate(latitude, longitude);
trackerData.Speed = int.Parse((string)jsonData.gm_speed);
trackerData.Degrees = int.Parse((string)jsonData.gm_ori);
trackerData.Direction = Direction.degreesToCardinalDetailed(double.Parse((string)jsonData.gm_ori));
//trackerData.Mileage = double.Parse((string)jsonData.gm_mileage);
double deviceMileage = 0;
double.TryParse((string)jsonData.gm_mileage, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out deviceMileage);
double carMileage = (tracker.MileageInitial);
trackerData.Mileage = deviceMileage + carMileage;
//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)jsonData.gm_data;
string[] data = gmData.Split(',');
trackerData.EventCode = (TqatProModel.Devices.Meitrack.EventCode)int.Parse(data[1]);
trackerData.GpsSatellites = int.Parse(data[2]);
trackerData.GsmSignal = int.Parse(data[3]);
trackerData.Altitude = int.Parse(data[6]);
trackerData.ACC = (int.Parse(data[18]) == 1) ? true : false;
trackerData.SOS = (int.Parse(data[17]) == 1) ? true : false;
trackerData.OverSpeed = ((int)trackerData.Speed > tracker.SpeedLimit) ? true : false;
//Geofence
Coordinate coordinate = new Coordinate(latitude, longitude);
if (company.Geofences != null) {
foreach (Geofence geofence in company.Geofences) {
if (Geofence.isPointInPolygon(geofence, coordinate)) {
trackerData.Geofence = geofence;
}
};
}
double batteryVoltage = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier);
batteryVoltage = (batteryVoltage * 3.3 * 2) / 4096;
double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier);
externalVoltage = (externalVoltage * 3.3 * 16) / 4096;
double batteryStrength = batteryVoltage - 3.5;
batteryStrength = (batteryStrength / 0.8) * 100;
if (batteryStrength < 0) {
batteryStrength = 0;
} else if (batteryStrength > 100) {
batteryStrength = 100;
}
trackerData.EPC = (externalVoltage > 3) ? false : true;
trackerData.Battery = Math.Round(batteryStrength, 2);
trackerData.BatteryVoltage = Math.Round(batteryVoltage, 2);
trackerData.ExternalVoltage = Math.Round(externalVoltage, 2);
return trackerData;
}
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (Exception exception) {
Debug.Write(exception);
mysqlConnection.Close();
throw new QueryException(1, exception.Message);
} finally {
mysqlConnection.Close();
}
}
public Tracker getTracker (String imei) {
try {
mysqlConnection.Open();
string sql =
"SELECT * " +
"FROM dbt_tracking_master.trks " +
"WHERE dbt_tracking_master.trks.tunit = @imei;";
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
mySqlCommand.Parameters.AddWithValue("@imei", imei);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (!mySqlDataReader.HasRows) {
mySqlDataReader.Dispose();
throw new QueryException(1, "Tracker " + imei + " is not registered.");
} else {
mySqlDataReader.Read();
Tracker tracker = new Tracker();
tracker.CompanyDatabaseName = (string)mySqlDataReader["tcmp"];
tracker.DatabaseHost = int.Parse((string)mySqlDataReader["tdbhost"]);
tracker.DatabaseName = (string)mySqlDataReader["tdbs"];
String dateTime = (string)mySqlDataReader["tcreatedate"];
tracker.DateTimeCreated = SubStandard.dateTime(dateTime);
dateTime = String.Empty;
dateTime = (string)mySqlDataReader["ttrackerexpiry"];
tracker.DateTimeExpired = SubStandard.dateTime(dateTime);
dateTime = String.Empty;
tracker.TrackerImei = (string)mySqlDataReader["tunit"];
tracker.DevicePassword = (string)mySqlDataReader["tunitpassword"];
tracker.DeviceType = int.Parse((string)mySqlDataReader["ttype"]);
tracker.DriverName = (string)mySqlDataReader["tdrivername"];
tracker.Emails = (string)mySqlDataReader["temails"];
tracker.HttpHost = int.Parse((string)mySqlDataReader["thttphost"]);
tracker.Id = (int)mySqlDataReader["tid"];
tracker.IdlingTime = int.Parse((string)mySqlDataReader["tidlingtime"]);
tracker.ImageNumber = int.Parse((string)mySqlDataReader["timg"]);
tracker.Inputs = (string)mySqlDataReader["tinputs"];
tracker.MileageInitial = int.Parse((string)mySqlDataReader["tmileageInit"]);
tracker.MileageLimit = int.Parse((string)mySqlDataReader["tmileagelimit"]);
tracker.MobileDataProvider = int.Parse((string)mySqlDataReader["tprovider"]);
tracker.Note = (string)mySqlDataReader["tnote"];
tracker.OwnerName = (string)mySqlDataReader["townername"];
tracker.SimImei = (string)mySqlDataReader["tsimsr"];
tracker.SimNumber = (string)mySqlDataReader["tsimno"];
tracker.SpeedLimit = int.Parse((string)mySqlDataReader["tSpeedLimit"]);
tracker.VehicleModel = (string)mySqlDataReader["tvehiclemodel"];
tracker.VehicleRegistration = (string)mySqlDataReader["tvehiclereg"];
mySqlDataReader.Dispose();
return tracker;
}
} catch (Exception exception) {
throw exception;
} finally {
mysqlConnection.Close();
}
}
public void fillTrackers (Company company) {
ConcurrentQueue<Tracker> trackers = new ConcurrentQueue<Tracker>();
try {
mysqlConnection.Open();
string sql =
"SELECT * " +
"FROM dbt_tracking_master.trks " +
"WHERE dbt_tracking_master.trks.tcmp = @CompanyDatabaseName";
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
mySqlCommand.Parameters.AddWithValue("@CompanyDatabaseName", company.DatabaseName);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (!mySqlDataReader.HasRows) {
//throw new QueryException(1, "Tracker's Collection is empty.");
mySqlDataReader.Dispose();
mysqlConnection.Close();
} else {
string dateTime;
while (mySqlDataReader.Read()) {
string[] trackerUser = mySqlDataReader.GetString("tusers").Split(',');
ConcurrentQueue<User> trackerUsers = new ConcurrentQueue<User>();
foreach (User user in company.Users) {
for (int index = 0; index < trackerUser.ToList().Count; index++) {
if (Int32.Parse(trackerUser[index]) == user.Id) {
trackerUsers.Enqueue(user);
}
}
}
Tracker tracker = new Tracker();
dynamic dynamicCollection = JsonConvert.DeserializeObject<dynamic>(mySqlDataReader.GetString("tcollections"));
JArray arrayCollection = (JArray)dynamicCollection;
ConcurrentQueue<Collection> collections = new ConcurrentQueue<Collection>();
Collection collectionItem = new Collection();
collectionItem.Id = 0;
collectionItem.Name = "All";
collectionItem.Description = "All";
collections.Enqueue(collectionItem);
foreach (User user in company.Users) {
if (user.Collections == null) {
continue;
}
foreach (Collection collection in user.Collections) {
for (int index = 0; index < arrayCollection.Count; index++) {
if (dynamicCollection[index].value == collection.Id) {
collections.Enqueue(collection);
}
}
}
}
tracker.Collections = collections;
//tracker.Collections = mySqlDataReader.GetString("tcollections");
tracker.CompanyDatabaseName = (string)mySqlDataReader["tcmp"];
tracker.DatabaseHost = int.Parse((string)mySqlDataReader["tdbhost"]);
tracker.DatabaseName = (string)mySqlDataReader["tdbs"];
dateTime = (string)mySqlDataReader["tcreatedate"];
tracker.DateTimeCreated = SubStandard.dateTime(dateTime);
dateTime = String.Empty;
dateTime = (string)mySqlDataReader["ttrackerexpiry"];
tracker.DateTimeExpired = SubStandard.dateTime(dateTime);
dateTime = String.Empty;
tracker.TrackerImei = (string)mySqlDataReader["tunit"];
tracker.DevicePassword = (string)mySqlDataReader["tunitpassword"];
tracker.DeviceType = int.Parse((string)mySqlDataReader["ttype"]);
tracker.DriverName = (string)mySqlDataReader["tdrivername"];
tracker.Emails = (string)mySqlDataReader["temails"];
tracker.HttpHost = int.Parse((string)mySqlDataReader["thttphost"]);
tracker.Id = (int)mySqlDataReader["tid"];
tracker.IdlingTime = int.Parse((string)mySqlDataReader["tidlingtime"]);
tracker.ImageNumber = int.Parse((string)mySqlDataReader["timg"]);
tracker.Inputs = (string)mySqlDataReader["tinputs"];
tracker.MileageInitial = int.Parse((string)mySqlDataReader["tmileageInit"]);
tracker.MileageLimit = int.Parse((string)mySqlDataReader["tmileagelimit"]);
tracker.MobileDataProvider = int.Parse((string)mySqlDataReader["tprovider"]);
tracker.Note = (string)mySqlDataReader["tnote"];
tracker.OwnerName = (string)mySqlDataReader["townername"];
tracker.SimImei = (string)mySqlDataReader["tsimsr"];
tracker.SimNumber = (string)mySqlDataReader["tsimno"];
tracker.Users = trackerUsers;
tracker.SpeedLimit = int.Parse((string)mySqlDataReader["tSpeedLimit"]);
tracker.VehicleModel = (string)mySqlDataReader["tvehiclemodel"];
tracker.VehicleRegistration = (string)mySqlDataReader["tvehiclereg"];
trackers.Enqueue(tracker);
}
company.Trackers = trackers;
mySqlDataReader.Dispose();
}
} catch (QueryException queryException) {
throw queryException;
} catch (MySqlException mySqlException) {
throw new QueryException(1, mySqlException.Message);
} catch (Exception exception) {
throw new QueryException(1, exception.Message);
} finally {
mysqlConnection.Close();
}
}
public void insertTrackerData (Tracker tracker, Gm gm) {
try {
mysqlConnection.Open();
string sql =
"INSERT INTO " +
"trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName +
"(gm_time,gm_lat,gm_lng,gm_speed,gm_ori,gm_mileage,gm_data,gm_lasttime) " +
"VALUES(@TimeStamp,@Latitude,@Longitude,@Speed,@Orientation,@Mileage,@Data,@LastTime)";
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
mySqlCommand.Parameters.AddWithValue("@TimeStamp", gm.TimeStamp);
mySqlCommand.Parameters.AddWithValue("@Latitude", gm.Latitude);
mySqlCommand.Parameters.AddWithValue("@Longitude", gm.Longitude);
mySqlCommand.Parameters.AddWithValue("@Speed", gm.Speed);
mySqlCommand.Parameters.AddWithValue("@Orientation", gm.Orientation);
mySqlCommand.Parameters.AddWithValue("@Mileage", gm.Mileage);
mySqlCommand.Parameters.AddWithValue("@Data", gm.Data);
mySqlCommand.Parameters.AddWithValue("@LastTime", gm.LastTime);
if (mySqlCommand.ExecuteNonQuery() != 1) {
throw new Exception("Error on Insert..");
}
} 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();
}
}
