using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;
using System.Collections;
using System.IO;
namespace SafeNetLib
{
public class DBhandle
{
public static string gatewayID;
public static int port;
public bool DBconnected = false;
private MySqlConnection connection;
private MySqlCommand command;
private static string MyConString;
private bool conStatus;
public bool running = false;
public string land_dupl = "";
public bool ConStatusGet
{
get
{
conStatus = false;
if (connection != null)
if (connection.State == System.Data.ConnectionState.Open)
{
conStatus = true;
}
else
{
conStatus = false;
}
return conStatus;
}
}
private static System.Threading.Timer tCheckConnection;
public DBhandle(string _MyConString, string _gatewayID)
{
MyConString = _MyConString;
gatewayID = _gatewayID;
}
public DBhandle(string _MyConString, string _gatewayID, int _port)
{
MyConString = _MyConString;
gatewayID = _gatewayID;
port = _port;
}
public DBhandle(string Server, string Database, string User, string Password)
{
MyConString = "SERVER=" + Server + "; " +
"DATABASE=" + Database + "; " +
"UID=" + User + "; " +
"PASSWORD=" + Password + "; Pooling=false;";
}
public void StartDB()
{
running = true;
int count = 0;
while (DBconnected == false && running)
{
if (count++ % 20 == 0)
{
if (connection != null)
StopDB();
try
{
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»»» Connecting to MYSQL server ««««««««««««««««");
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
connection = new MySqlConnection(MyConString);
command = connection.CreateCommand();
connection.Open();
//Utils.WriteLine("DB PING is " + connection.Ping(), ConsoleColor.Red);
DBconnected = true;
/*
//start backup thread
if (tCheckConnection != null)
tCheckConnection.Dispose();
tCheckConnection = null;
//tCheckConnection = new System.Threading.Timer(CheckConnection, null, new TimeSpan(0, 0, 10), new TimeSpan(0, 0, 30));
*/
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»» MYSQL Connection established «««««««««««««««");
Utils.ConsWrite(DebugMSG_Type.always, "»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
}
catch (Exception ex)
{
Utils.WriteLine("Database connection could not be made", ConsoleColor.Red);
//Utils.ConsWrite(DebugMSG_Type.always, ex.ToString());
DBconnected = false;
//Utils.ConsWrite(DebugMSG_Type.always, "---- Trying to reconnect to DB in 10 seconds.");
}
}
Thread.Sleep(6000);
}
}
public void StopDB()
{
try
{
running = false;
if (connection != null)
{
connection.CancelQuery(50);
connection.Close();
connection.Dispose();
}
DBconnected = false;
if (tCheckConnection != null)
{
tCheckConnection.Dispose();
tCheckConnection = null;
}
}
catch (Exception ex)
{
Utils.ConsWrite(DebugMSG_Type.always, "StopDB Error " + ex.ToString());
}
}
//Connection timer check
private void CheckConnection(Object state)
{
try
{
Utils.ConsWrite(DebugMSG_Type.DB, "Checking DB connection :" + ConStatusGet);
if (!ConStatusGet)
{
DBconnected = false;
}
}
catch(Exception ex)
{
Utils.ConsWrite(DebugMSG_Type.always, ex.ToString());
DBconnected = false;
}
}
#region new Safenet
public ConnPlussCFG LoadGWConfig()
{
ConnPlussCFG cfg = null;
try
{
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
using (MySqlCommand command = connection.CreateCommand())
{
MySqlDataReader reader;
command.CommandText = "select cfg.ip,cfg.ars_port,cfg.lrrp_port,cfg.sms_port,cfg.receive_lrrp_port,cfg.receive_sms_port from connect_plus_contr as cfg" +
" inner join gateway as g on(cfg.id = g.contr_id)" +
" where g.code = " + gatewayID + ";";
//Console.WriteLine("Checking SMS ####" + command.CommandText);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
cfg = new ConnPlussCFG();
cfg.IP = reader.GetString(0);
cfg.ars_port = reader.GetInt32(1);
cfg.lrrp_port = reader.GetInt32(2);
cfg.sms_port = reader.GetInt32(3);
cfg.r_lrrp_port = reader.GetInt32(4);
cfg.r_sms_port = reader.GetInt32(5);
}
reader.Close();
}
connection.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadGWConfig error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
return cfg;
}
///
/// Loads unit info from DB (reporting interval) adds it to (hashtable)ht_SUInfo
///
/// gateway Code form conf file
public bool LoadUnitInfo(Int64 gatewayCode)
{
return LoadUnitInfo(gatewayCode + "");
}
public void LoadImeiHashtable(string gatewayCode, ref Hashtable HT)
{
lock (locker)
{
try
{
using (MySqlCommand command_units = connection.CreateCommand())
{
Utils.ConsWrite(DebugMSG_Type.DEV, "Getting subscribers imei info..");
MySqlDataReader reader_units;
if (gatewayCode != "0")
{
command_units.CommandText = "SELECT U.imei " +
" FROM unit as U " +
"INNER JOIN gateway as g ON (U.gateway_id = g.id) " +
"WHERE g.code = " + gatewayCode + " AND (is_deactivated != 1) AND (is_deleted != 1);";
}
else
{
command_units.CommandText = "select imei from unit as u " +
"where u.gateway_id is NULL AND (is_deactivated != 1) AND (is_deleted != 1);";
}
//Utils.ConsWrite(DebugMSG_Type.ALL, "Getting subscribers query \n" + command_units.CommandText);
reader_units = command_units.ExecuteReader();
// foreach unit
while (reader_units.Read())
{
try
{
string imei = reader_units.GetString(0);
if (!HT.Contains(imei))
HT.Add(imei, 0);
}
catch (Exception ex)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadImeiHashtable (ALERTS)error...");
Utils.ConsWrite(DebugMSG_Type.always, ex.ToString());
}
}
reader_units.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadImeiHashtable error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
}
///
/// Loads unit info from DB (reporting interval) adds it to (hashtable)ht_SUInfo
///
/// gateway Code form conf file
public bool LoadUnitInfo(string gatewayCode)
{
DateTime start = DateTime.Now;
bool ret = false;
try
{
using (MySqlConnection connection_units = new MySqlConnection(MyConString))
{
connection_units.Open();
using (MySqlCommand command_units = connection_units.CreateCommand())
{
using (MySqlConnection connection_alert = new MySqlConnection(MyConString))
{
connection_alert.Open();
using (MySqlCommand command_alert = connection_alert.CreateCommand())
{
Utils.ConsWrite(DebugMSG_Type.DEV, "Getting subscriber info..");
MySqlDataReader reader_units;
if (gatewayCode != "0")
{
command_units.CommandText = "SELECT U.id, U.imei, U.reporting_seconds, U.name, " +
"IFNULL(lp.is_on, 0) || '' as ars " +
" FROM unit as U " +
"INNER JOIN gateway as g ON (U.gateway_id = g.id) " +
"LEFT JOIN last_position as lp ON (U.id = lp.unit_id) " +
"WHERE g.code = " + gatewayCode + " AND (is_deactivated != 1) AND (is_deleted != 1);";
}
else
{
command_units.CommandText = "select u.id, imei, reporting_seconds, name from unit as u " +
"where u.gateway_id is NULL AND (is_deactivated != 1) AND (is_deleted != 1);";
}
//Utils.ConsWrite(DebugMSG_Type.ALL, "Getting subscribers query \n" + command_units.CommandText);
reader_units = command_units.ExecuteReader();
int nrOfUnits = 0;
int nrOfNewUnits = 0;
// foreach unit
while (reader_units.Read())
{
try
{
int DBid = reader_units.GetInt32(0);
string suid = reader_units.GetInt64(1).ToString();
SUinfo sui = new SUinfo();
sui.DBid = DBid;
sui.suid = suid;
if (gatewayCode != "0")
sui.ARSon = (reader_units.GetInt32(4) == 1 ? true : false);
//Utils.ConsWrite(DebugMSG_Type.ALL, "SU " + sui.suid + " is " + sui.ARSon);
//fetch reporting intervall
int dbvalue_repInterval = reader_units.GetInt32(2);
sui.repInterval = dbvalue_repInterval;
string unitName = reader_units.GetString(3);
//get newVal from DB( this is set to true if rep inteval has changed from last read)
//TODO: get the actaucl (newval) from DB --- if this is still present in the NEW DB
//TODO: edit1 I think this will be replace with a hashtable that will know if we have a new value
bool newVal = false;
//search for alerts definitions for each unit
sui.alertList = new List();
MySqlDataReader reader_alert;
command_alert.CommandText = "select ad.id, ad.class, ad.digitals_value, ad.name, " +
"ad.digital_alert_description, ad.max_speed_in_miles from alert_definition as ad " +
" inner join unit_alert_definition as uad" +
" on (ad.id = uad.alert_definition_id) " +
" where uad.unit_alert_definitions_id = " + DBid + " and ad.is_deactivated = 0;";
reader_alert = command_alert.ExecuteReader();
while (reader_alert.Read())
{
try
{
Alert alert = new Alert();
alert.UnitDbID = DBid;
alert.UnitIMEI = suid;
alert.UnitName = unitName;
alert.AlertDbID = reader_alert.GetInt32(0);
alert.Alert_class = reader_alert.GetString(1);
if (!reader_alert.IsDBNull(2))
alert.DImask1 = reader_alert.GetInt32(2);
else
alert.DImask1 = 0;
if (!reader_alert.IsDBNull(3))
alert.Alert_name = reader_alert.GetString(3);
else
alert.Alert_name = "";
if (!reader_alert.IsDBNull(4))
alert.Alert_description = reader_alert.GetString(4);
else
alert.Alert_description = "";
if (!reader_alert.IsDBNull(5))
alert.Speed = reader_alert.GetInt32(5);
else
alert.Speed = 0;
switch (alert.Alert_class)
{
case "safenet.DigitalAlertDefinition":
alert.Type = Alert_TYPE.DI;
break;
case "safenet.SpeedAlertDefinition":
alert.Type = Alert_TYPE.SPEED;
break;
}
sui.alertList.Add(alert);
}
catch (Exception ex)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadUnitInfo (ALERTS)error...");
Utils.ConsWrite(DebugMSG_Type.always, ex.ToString());
}
}//while (reader_alert.Read())
reader_alert.Close();
nrOfUnits++;
//add data to ht_SUInfo
lock (SN_Queues.ht_SUInfo.SyncRoot)
{
if (SN_Queues.ht_SUInfo != null)
{
if (SN_Queues.ht_SUInfo.ContainsKey(suid))
{
// check previous report interval value had changed
if (((SUinfo)SN_Queues.ht_SUInfo[suid]).repInterval != sui.repInterval)
newVal = true;
// update the unit with the new values
SN_Queues.ht_SUInfo[suid] = sui;
}
else
{
newVal = true;
nrOfNewUnits++;
SN_Queues.ht_SUInfo.Add(suid, sui);
}
}
}
//raise a flag that we have a new interval value
if (newVal)
{
SN_Queues.NewIntervalQueue.PostItem(sui);
//Utils.ConsWrite(DebugMSG_Type.always, "New interval: " + sui.repInterval + " found for suid:" + suid);
}
else
{
//Utils.ConsWrite(DebugMSG_Type.always, "Same interval: " + sui.repInterval + " found for suid:" + suid);
}
}
catch (Exception ex1)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadUnitInfo (parsing unit error)");
Utils.ConsWrite(DebugMSG_Type.always, ex1.ToString());
}
}//while (reader_units.Read())
reader_units.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Loaded {0} units [{1} new] in {2} ms", nrOfUnits, nrOfNewUnits, DateTime.Now.Subtract(start).Milliseconds) );
}
connection_alert.Close();
}
}
connection_units.Close();
}
ret = true;
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "_____________________");
Utils.ConsWrite(DebugMSG_Type.always, "LoadUnitInfo error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
ret = false;
}
TimeSpan delta = DateTime.Now.Subtract(start);
Utils.ConsWrite(DebugMSG_Type.ALERTS, "Done loading "+SN_Queues.ht_SUInfo.Count + " units from DB!!!!!! delta: " + delta.Milliseconds + " msecs");
return ret;
}
///
/// Loads address from DB
///
/// gateway ID form conf file
public bool LoadAddressList(string gatewayID)
{
bool ret = false;
try
{
Utils.ConsWrite(DebugMSG_Type.DB, "Getting Addresses .");
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
{
connection2.Open();
using (MySqlCommand command2 = connection2.CreateCommand())
{
MySqlDataReader reader;
if (gatewayID != "0")
{
command2.CommandText = "select adr.address_hash, adr.address, adr.latitude, adr.longitude from address as adr " +
"where adr.gateway_id = (select id from gateway where code=" + gatewayID + ");";
}
else
{
command2.CommandText = "select adr.address_hash, adr.address, adr.latitude, adr.longitude from address as adr " +
"where adr.gateway_id is NULL;";
}
//Utils.ConsWrite(DebugMSG_Type.DB, "LoadAddressList command2.CommandText:" + command2.CommandText);
reader = command2.ExecuteReader();
while (reader.Read())
{
Address addr = new Address();
addr.addr_hash = reader.GetString(0);
addr.Value = (reader.IsDBNull(1)) ? "" : reader.GetString(1);
addr.lat = reader.GetFloat(2).ToString();
addr.lng = reader.GetFloat(3).ToString();
//add data to ht_addressList
lock (SN_Queues.ht_addressList.SyncRoot)
{
if (SN_Queues.ht_addressList != null)
{
if (SN_Queues.ht_addressList.ContainsKey(addr.addr_hash))
SN_Queues.ht_addressList[addr.addr_hash] = addr;
else
SN_Queues.ht_addressList.Add(addr.addr_hash, addr);
}
}
}
reader.Close();
}
connection2.Close();
}
ret = true;
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "LoadAddressList error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
return ret;
}
///
/// Insert in ars_message
///
/// subscriber id
/// message ON/OFF (string)
///
public bool Insert_ARSmsg(int DBsuid, string p_message)
{
DateTime start = DateTime.Now;
try
{
//int suDBid = Utils.GetDBidFromSUID(suid);
if (DBsuid == 0)
return false;
string query = "INSERT into ars_message (message_time,unit_id,is_on) values (" +
"'" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"'," + DBsuid +
"," + (("ON" == p_message) ? 1 : 0).ToString() + ")";
MySqlCommand setSent = new MySqlCommand(query, connection);
setSent.ExecuteNonQuery();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("ARS inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
//Utils.ConsWrite(DebugMSG_Type.always, "Error inserting ARS in database");
return EvaluateDBQuerryError("Insert ARS", e);
}
}
///
/// Update ARS last pos
///
/// arsID that was returned by Insert_ARSmsg
private void Update_ARSlp(int arsID)
{
}
///
/// Insert location in address list wiht string addr = null
///
/// GPS data
/// adr.addr_hash
public bool Insert_AddressList(htCell_t cell)
{
lock (locker)
{
//MySqlConnection connectionSet = new MySqlConnection(MyConString); ;
DateTime start = DateTime.Now;
Address adr = new Address(cell);
try
{
string query = "INSERT into address (address_hash,address,gateway_id,latitude,longitude) values (" +
"'" + adr.addr_hash +
"'," + "NULL" +
",(select id from gateway where code=" + gatewayID +
")," + Math.Round(cell.d_lat, 4).ToString() +
"," + Math.Round(cell.d_lng, 4).ToString() + ")";
MySqlCommand setSent = new MySqlCommand(query, connection);
adr = null;
//connectionSet.Open();
setSent.ExecuteNonQuery();
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Address [{0:0.0000},{1:0.0000}] inserted in {2} ms [DB]",
Math.Round(cell.d_lat, 4), Math.Round(cell.d_lng, 4), DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
//connectionSet.Close();
return EvaluateDBQuerryError("Insert_AddressList", e);
/*
if (!e.ToString().Contains("Duplicate"))
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting Address in database");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}*/
}
}
}
public bool Insert_Landmark(landmark lnd)
{
lock (locker)
{
//MySqlConnection connectionSet = new MySqlConnection(MyConString); ;
DateTime start = DateTime.Now;
Object pointID_Landmark = null;
try
{
string query = string.Format("select id from lat_lng_point where latitude ={1} and longitude = {2}"
, lnd.address.Replace("'", "`"), lnd.lat, lnd.lng);
MySqlCommand setSent = new MySqlCommand(query, connection);
//connectionSet.Open();
Object pointID = setSent.ExecuteScalar();
if(pointID!= null)
{
query = string.Format("select point_id from landmark where point_id ='{0}' and user_id ={1} /*and name = '{2}'*/"
, pointID, lnd.userID, lnd.name.Replace("'", "`"));
setSent = new MySqlCommand(query, connection);
//connectionSet.Open();
pointID_Landmark = setSent.ExecuteScalar();
}
if (pointID == null)
{
query = string.Format("insert into lat_lng_point(version,address,latitude,longitude) values (0,'{0}',{1},{2}); SELECT LAST_INSERT_ID();"
, lnd.address.Replace("'", "`"), lnd.lat, lnd.lng);
setSent = new MySqlCommand(query, connection);
//connectionSet.Open();
pointID = setSent.ExecuteScalar();
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Point [{0:0.0000},{1:0.0000}] with address {2} inserted in {3} ms [DB]",
Math.Round(lnd.lat, 4), Math.Round(lnd.lng, 4),lnd.address, DateTime.Now.Subtract(start).Milliseconds));
}
else
{
using (System.IO.StreamWriter file = File.AppendText(land_dupl))
{
file.WriteLine(DateTime.Now.ToString() + "," + lnd.name +"," + lnd.address + "," + lnd.lat + "," + lnd.lng + "," + pointID);
}
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Point {0} with lat {1}, long {1} and address {2} already in table lat_lng_point"
, pointID, lnd.lat, lnd.lng, lnd.address));
}
if (pointID_Landmark == null)
{
query = string.Format("insert into landmark(version,color,date_created,icon,last_updated,name,point_id,radius,user_id)"
+ " values (0,0,'{0}','{1}','{2}','{3}',{4},{5},{6})",
DateTime.UtcNow.ToString("yyyy:MM:dd HH:mm:ss"),
lnd.icon,
DateTime.UtcNow.ToString("yyyy:MM:dd HH:mm:ss"),
lnd.name.Replace("'", "`"),
pointID,
lnd.radius,
lnd.userID
);
setSent = new MySqlCommand(query, connection);
setSent.ExecuteNonQuery();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Landmark with point_id {0} inserted into [DB]", pointID));
}
else
{
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Point {0} with name {1} already in table landmark", pointID, lnd.name.Replace("'", "`")));
}
return true;
}
catch (Exception e)
{
//connectionSet.Close();
return EvaluateDBQuerryError("Insert_Landmark", e);
/*
if (!e.ToString().Contains("Duplicate"))
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting Address in database");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}*/
//}
}
}
}
///
/// Insert location in address list wiht string addr = null
///
/// GPS data
/// adr.addr_hash
public void InsertTallysmanEvent(TallysmanMsg e)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = String.Format("INSERT INTO tallysman_data (id, event_type, unit_id, latitude, longitude, gps_fix_time, " +
" speed, bearing, altitude, confidence_level, accuracy_horizontal, accuracy_vertical, odometer, " +
" run_time, idle_time, vio_status, vio_changed, average_speed, waypoint_id, " +
" firmware_version, rssi, vital_id, event_time) " +
" VALUES ({0}, '{1}', {2}, {3}, {4}, '{5}'," +
" {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
" {13}, {14}, {15}, {16}, {17}, {18}, " +
" '{19}', {20}, {21}, '{22}')",
e.Id, e.EventType, e.RadioID, e.Latitude, e.Longitude, ((new DateTime(1970, 1, 1)).AddSeconds(e.GPSFixTime)).ToString("yyyy:MM:dd HH:mm:ss"),
e.Speed, e.Bearing, e.Altitude, e.LevelOfConfidence, e.HorizontalAccuracy, e.VerticalAccuracy, e.Odometer,
e.RunTime, e.IdleTime, e.VioStatus, e.VioChanged, e.AverageSpeed, e.WaypointId,
e.FirmwareVersion, e.RSSI, e.VitalId, e.EventTime);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.WriteLine(String.Format("■■■ Tallysman Event inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds), ConsoleColor.Gray);
}
catch (Exception e2)
{
if (!e2.ToString().Contains("Duplicate"))
{
Utils.WriteLine("Error inserting Tallysman Event in database: " + e2.ToString(), ConsoleColor.Red);
}
}
}
///
/// Insert into messages
///
/// ht_cell
///
public static readonly object locker = new object();
public bool Insert_messages(htCell_t cell)
{
lock (locker)
{
DateTime start = DateTime.Now;
Address adr = new Address(cell);
int unitID = Utils.GetDBidFromSUID(cell.suid);
if (unitID == -1)
{
Utils.ConsWrite(DebugMSG_Type.GPS, "Unit <" + cell.suid + "> not assign to this GW!!!");
LOGS.LOG("Unit <" + cell.suid + "> not assign to this GW!!!");
return false;
}
try
{
//Console.WriteLine("Timee AFTER UTC conversion:" + cell.location_time.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss"));
LOGS.LOG("Unit <" + cell.suid + ">" + "Time AFTER UTC conversion:" + cell.location_time.ToString("yyyy:MM:dd HH:mm:ss"));
string query = "INSERT into position_message PARTITION (PM_FUTURE) VALUES (" +
"'" + cell.location_time.ToString("yyyy:MM:dd HH:mm:ss") +
"'," + unitID +
",'" + adr.addr_hash +
"'," + "0" + //digital in
"," + "0" + //digital out
"," + Math.Round(cell.d_lat, 5).ToString() +
"," + Math.Round(cell.d_lng, 5).ToString() +
"," + cell.spd + ")";
//Console.WriteLine("\n\n" + query + "\n\n");
LOGS.LOG(query);
MySqlCommand setSent = new MySqlCommand(query, connection);
setSent.CommandTimeout = 500;
setSent.ExecuteNonQuery();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Position [{0:0.0000},{1:0.0000}] inserted in {2} ms [DB]",
Math.Round(cell.d_lat, 4), Math.Round(cell.d_lng, 4), DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
if (e.ToString().Contains("Found a row not matching the given partition set"))
Utils.WriteLine("ERROR INSERT POSITIONS [PARTITION] : " + cell.location_time.ToShortDateString()
+ Environment.NewLine + e.ToString(), ConsoleColor.Red);
return EvaluateDBQuerryError("Insert GPS", e);
}
}
}
///
/// Update last pos
///
/// string addr_hash (return by Insert_AddressList or get from ht_addressList
private void Update_lastpos(string addr_hash)
{
}
///
/// feed SN_Queues.computeAddressQueue with addresses that dont have an compute addr.
///
public static void GetUnComputeAddr()
{
//ex:
//Address addr = new Address("45.33342","43.5454");
//SN_Queues.computeAddressQueue.PostItem(addr);
try
{
Utils.ConsWrite(DebugMSG_Type.DEV, "GetUnComputeAddr.");
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
{
connection2.Open();
using (MySqlCommand command2 = connection2.CreateCommand())
{
MySqlDataReader reader;
command2.CommandText = "SELECT latitude, longitude FROM address " +
"where gateway_id=" + gatewayID + " and address is NULL LIMIT 100;";
reader = command2.ExecuteReader();
while (reader.Read())
{
Address addr = new Address(reader.GetDouble(0), reader.GetDouble(1));
SN_Queues.computeAddressQueue.PostItem(addr);
}
reader.Close();
}
connection2.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "GetUnComputeAddr error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
///
/// feed SN_Queues.computeAddressQueue with addresses that dont have an compute addr.
///
public static int GetGatewayPort()
{
//ex:
//Address addr = new Address("45.33342","43.5454");
//SN_Queues.computeAddressQueue.PostItem(addr);
object temp = null;
try
{
Utils.ConsWrite(DebugMSG_Type.DEV, "GetGatewayPort.");
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
{
connection2.Open();
using (MySqlCommand command2 = connection2.CreateCommand())
{
command2.CommandText = "SELECT " +
" listen_port " +
"FROM " +
" gateway_config gc " +
" JOIN " +
" gateway g ON g.config_id = gc.id " +
"WHERE " +
" g.code = " + gatewayID;
temp = command2.ExecuteScalar();
}
connection2.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "GetUnComputeAddr error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
int ret = -1;
Int32.TryParse((temp ?? port).ToString(), out ret);
return ret;
}
///
/// Update address value in DB after fetching it from google
///
///
public static void UpdateAddressValue(Address addr)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update address set address='" + addr.Value + "'" +
" where address_hash='" + addr.addr_hash + "';";
Utils.ConsWrite(DebugMSG_Type.DB, "UpdateAddressValue in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Update Address inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "UpdateAddressValue error...");
Utils.ConsWrite(DebugMSG_Type.always, e.Message);
}
}
public bool Insert_GW_status(string gatewayID, int status)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "INSERT into gateway_status (date_created,gateway_id,is_connected) values (" +
"'" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"',(select id from gateway where code=" + gatewayID + ")" +
"," + status + ")";
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("GW Status inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting gateway status in database");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
return false;
}
public bool Insert_GW_status(string gatewayID, int status, string message)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "INSERT into gateway_status (date_created,gateway_id, is_connected, message) values (" +
"'" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"',(select id from gateway where code=" + gatewayID + ")" +
"," + status + ",'" + message + "')";
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("GW Status inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting gateway status in database");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
return false;
}
public void Insert_Alert_Event(Alert alert)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "INSERT into alert_event (date_created,alert_type_id,unit_id,latitude,longitude,speed,details,position_time,digital_in) values (" +
"'" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"'," + alert.AlertDbID +
"," + alert.UnitDbID +
"," + alert.Alert_latitude +
"," + alert.Alert_longitude +
"," + alert.Speed +
",'digital'" +
",'" + alert.Position_time + "'" +
"," + alert.Digital_in +
")";
Console.WriteLine("query:" + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Alert inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting gateway status in database");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
public void Get_Alert_Notification(Alert alert)
{
try
{
Utils.ConsWrite(DebugMSG_Type.DEV, "GetUnComputeAddr.");
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
{
connection2.Open();
using (MySqlCommand command2 = connection2.CreateCommand())
{
MySqlDataReader reader;
command2.CommandText = "SELECT ant.call_notification, ant.desktop, ant.email, ant.sms FROM alert_notification_type as ant" +
" inner join alert_definition as ad on (ant.id = ad.notification_type_id)" +
" where ad.id=" + alert.AlertDbID;
reader = command2.ExecuteReader();
Boolean email_notif = false;
while (reader.Read())
{
email_notif = reader.GetBoolean(2);
}
reader.Close();
if (email_notif)
{
command2.CommandText = "SELECT c.email FROM contact as c" +
" inner join alert_definition_contact as adc on (c.id = adc.contact_id )" +
" where adc.alert_definition_recipients_id=" + alert.AlertDbID;
reader = command2.ExecuteReader();
while (reader.Read())
{
alert.Notification_email.Add(reader.GetString(0));
}
reader.Close();
}
}
connection2.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Get_Alert_Notification error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
#endregion
#region OlD safenet
public bool insertSMSinDB(string p_radioID, string p_message)
{
DateTime start = DateTime.Now;
try
{
int suDBid = 0;
lock (SN_Queues.ht_SUInfo.SyncRoot)
{
if (SN_Queues.ht_SUInfo != null)
{
if (SN_Queues.ht_SUInfo.ContainsKey(p_radioID))
suDBid = ((SUinfo)SN_Queues.ht_SUInfo[p_radioID]).DBid;
}
}
if (suDBid == 0)
{
Utils.ConsWrite(DebugMSG_Type.Debug, "[SMS] Unit with IMEI:" + p_radioID + " not found in DB!!!!");
LOGS.LOG("Unit with IMEI:" + p_radioID + " not found in DB!!!!");
return false;
}
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "insert into text_message (message_content,unit_id,date_created,time_sent_or_received,message_type) values ('" + p_message +
"'," + suDBid +
",'" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"','" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"','IN')";
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("SMS inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
return true;
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error inserting SMS in DB\n" + e.ToString() );
return false;
}
}
public void UpdateSMSRead(Int32 DBid)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update text_message set is_read=1 where id =" + DBid;
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("SMS Read Updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception excp)
{
//Exception myExcp = new Exception("Could not set SEND param " + excp.Message, excp);
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, "Error in UpdateSMSRead: " + excp.ToString());
}
}
public void confirmSMS(SMSmsg p_msg)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update text_message set is_sent=1,time_sent_or_received='" + DateTime.Now.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") +
"' where id=" + p_msg.DBmsg_id;
//Utils.ConsWrite(DebugMSG_Type.DB, "Confirming message in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("SMS Confirmed in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception excp)
{
Exception myExcp = new Exception("Could not update SMS Confirm: " + excp.Message, excp);
//connectionSet.Close();
//throw (myExcp);
}
}
public void checkSMSinDB()
{
DateTime start = DateTime.Now;
try
{
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
MySqlDataReader reader;
command.CommandText = "select tm.id,u.imei,tm.message_content from text_message as tm" +
" inner join unit as u on(tm.unit_id = u.id)" +
" inner join gateway as g on(u.gateway_id = g.id)" +
" where g.code = " + gatewayID + " and tm.message_type = 'OUT' and tm.is_sent = 0 " +
" AND tm.date_created > '" + String.Format("{0:yyyy-MM-dd H:mm:ss}", DateTime.UtcNow.AddHours(-24)) + "';";
//Console.WriteLine("Checking SMS ####" + command.CommandText);
reader = command.ExecuteReader();
List smsRequestsList = new List();
while (reader.Read())
{
LOGS.LOG("@SMS@ " + "Analyzing message: " + reader.GetInt32(0) + ", " + reader.GetInt64(1) + ", " + reader.GetString(2));
//Utils.ConsWrite(DebugMSG_Type.DB, "Analyzing message: " + reader.GetInt32(0) + ", " + reader.GetInt64(1) + ", " + reader.GetString(2));
string suid = reader.GetInt64(1).ToString();
SMSmsg msg = new SMSmsg();
msg.conf = true;
msg.suid = suid;
msg.msg = reader.GetString(2);
msg.DBmsg_id = reader.GetUInt32(0);
// add sms message to the list
smsRequestsList.Add(msg);
}
reader.Close();
int countSMS = 0;
foreach (SMSmsg msg in smsRequestsList)
{
int index = -1;
int count = 0;
//check to see if we already sent the message
lock (SN_Queues.waitConfSMSList.SyncRoot)
{
SMSmsg expiredAckMessage = null;
foreach (SMSmsg msgTmp in SN_Queues.waitConfSMSList)
{
if (msg.DBmsg_id == msgTmp.DBmsg_id)
{
index = count;
if (DateTime.Now.Subtract(msgTmp.waitConfSMSList_time).TotalMinutes > 5)
{
expiredAckMessage = msgTmp;
}
break;
}
count++;
}
if(expiredAckMessage != null)
{
// flag that the item wasn't found and remove it from the lists ['read from db' AND 'wait for conf']
index = -1;
SN_Queues.ht_ReadSMSFromDB_List.Remove(msg.DBmsg_id);
SN_Queues.waitConfSMSList.Remove(expiredAckMessage);
}
}
// we found a new message which is not in the way queue
if (index == -1 && !SN_Queues.ht_ReadSMSFromDB_List.Contains(msg.DBmsg_id))
{
SN_Queues.ht_ReadSMSFromDB_List.Add(msg.DBmsg_id, msg);
//Utils.ConsWrite(DebugMSG_Type.DB, "Message added to sendSMSQueue");
LOGS.LOG("@SMS@ " + "Message added to sendSMSQueue");
UpdateSMSRead(Int32.Parse(msg.DBmsg_id + ""));
SN_Queues.sendSMSQueue.PostItem(msg);
countSMS++;
}
else
{
LOGS.LOG("@SMS@ " + "Message already in SN_Queues.waitConfSMSList... no need to send it again");
}
}
if (countSMS > 0)
Utils.ConsWrite(DebugMSG_Type.DB, "■■■ Got " + countSMS + " SMS requests");
Utils.ConsWrite(DebugMSG_Type.always, String.Format("SMS Received in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
connection.Close();
}
}
catch (Exception e)
{
EvaluateDBQuerryError("checkSMSinDB", e);
Utils.ConsWrite(DebugMSG_Type.always, "checkSMSinDB error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
public List CheckSMSinDB_for_unit(string p_radioID)
{
List returnList = new List();
try
{
int suDBid = 0;
lock (SN_Queues.ht_SUInfo.SyncRoot)
{
if (SN_Queues.ht_SUInfo != null)
{
if (SN_Queues.ht_SUInfo.ContainsKey(p_radioID))
suDBid = ((SUinfo)SN_Queues.ht_SUInfo[p_radioID]).DBid;
}
}
if (suDBid == 0)
{
Utils.ConsWrite(DebugMSG_Type.Debug, "Unit with IMEI:" + p_radioID + " not found in DB!!!!");
LOGS.LOG("Unit with IMEI:" + p_radioID + " not found in DB!!!!");
return returnList;
}
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
MySqlDataReader reader;
command.CommandText = "select tm.id,u.imei,tm.message_content from text_message as tm" +
" inner join unit as u on(tm.unit_id = u.id)" +
" inner join gateway as g on(u.gateway_id = g.id)" +
" where g.code = " + gatewayID + " and tm.message_type = 'OUT' and tm.is_sent = 0" +
" and tm.unit_id=" + suDBid + ";";
//Console.WriteLine("Checking SMS ####" + command.CommandText);
reader = command.ExecuteReader();
while (reader.Read())
{
Utils.ConsWrite(DebugMSG_Type.DB, "Analyzing message: " + reader.GetInt32(0) + ", " + reader.GetInt64(1) + ", " + reader.GetString(2));
string suid = reader.GetInt64(1).ToString();
SMSmsg msg = new SMSmsg();
msg.conf = true;
msg.suid = suid;
msg.msg = reader.GetString(2);
msg.DBmsg_id = reader.GetUInt32(0);
//check to see if we already sent the message
returnList.Add(msg);
}
reader.Close();
}
connection.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error CheckSMSinDB_for_unit \n" + e.ToString());
}
return returnList;
}
public void GetPollRequests4Conventional()
{
DateTime start = DateTime.Now;
try
{
//Console.WriteLine("Get Poll Requests");
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
DateTime dateB = DateTime.Now.ToUniversalTime().AddMinutes(-10);
MySqlDataReader reader;
command.CommandText = "select p.id,u.imei,p.date_created from poll as p" +
" inner join unit as u on(p.unit_id = u.id)" +
" inner join gateway as g on(u.gateway_id = g.id)" +
" where g.code = " + gatewayID + " and is_read=0 and p.date_created > '" + String.Format("{0:yyyy-MM-dd H:mm:ss}", dateB) + "';";
//Console.WriteLine("Checking Poll ####" + command.CommandText);
reader = command.ExecuteReader();
int numRows = 0;
// create a list that will store all poll requests to be added in the hash
// this allows the count of them, and only after insert to hash
List toAddPollRequests = new List();
while (reader.Read())
{
// increase rows count
numRows++;
string suid = reader.GetInt64(1).ToString();
//Utils.ConsWrite(DebugMSG_Type.DB, "Got POLL REQ for " + suid);
POLLmsg msg = new POLLmsg();
msg.DBid = reader.GetInt32(0);
msg.suid = suid;
msg.created = reader.GetDateTime(2);
msg.read = DateTime.Now.ToUniversalTime();
//check to see if we already have this poll
lock (SN_Queues.ht_POLL_List.SyncRoot)
{
if (!SN_Queues.ht_POLL_List.ContainsKey(msg.DBid))
{
//add to poll hashtable
SN_Queues.ht_POLL_List.Add(msg.DBid, msg);
//Utils.ConsWrite(DebugMSG_Type.DB, "Poll for:" + msg.suid + " added to loc queue ");
//add to location que
MotoTRBOcmdMsg TRBOmsg = new MotoTRBOcmdMsg();
TRBOmsg.m_cmd = (byte)MotoTRBOcmd.SEND_POLL;
TRBOmsg.m_suid = msg.suid;
TRBOmsg.m_payload = msg.DBid.ToString();
toAddPollRequests.Add(TRBOmsg);
}
}
UpdatePollReadTime(msg.DBid);
}
reader.Close();
if (numRows > 0)
Utils.ConsWrite(DebugMSG_Type.DB, "■■■ Got " + numRows + " poll requests");
//else
//Console.WriteLine("Empty handed");
foreach (MotoTRBOcmdMsg msg in toAddPollRequests)
{
SN_Queues.locationQueue.PostItem(msg);
}
}
connection.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll requests received in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "GetPollRequests error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
public void GetPollRequests()
{
DateTime start = DateTime.Now;
try
{
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
MySqlDataReader reader;
command.CommandText = "select p.id,u.imei,p.date_created from poll as p" +
" inner join unit as u on(p.unit_id = u.id)" +
" inner join gateway as g on(u.gateway_id = g.id)" +
" where g.code = " + gatewayID + " and is_read=0;";
//Console.WriteLine("Checking SMS ####" + command.CommandText);
reader = command.ExecuteReader();
while (reader.Read())
{
string suid = reader.GetInt64(1).ToString();
POLLmsg msg = new POLLmsg();
msg.DBid = reader.GetInt32(0);
msg.suid = suid;
msg.created = reader.GetDateTime(2);
msg.read = DateTime.Now.ToUniversalTime();
//check to see if we already have this poll
lock (SN_Queues.ht_POLL_List.SyncRoot)
{
if (!SN_Queues.ht_POLL_List.ContainsKey(msg.DBid))
{
//add to poll hashtable
SN_Queues.ht_POLL_List.Add(msg.DBid, msg);
//add to location que
MotoTRBOcmdMsg TRBOmsg = new MotoTRBOcmdMsg();
TRBOmsg.m_cmd = (byte)MotoTRBOcmd.SEND_POLL;
TRBOmsg.m_suid = msg.suid;
TRBOmsg.m_payload = msg.DBid.ToString();
SN_Queues.locationQueue.PostItem(TRBOmsg);
Utils.ConsWrite(DebugMSG_Type.DEV, "Poll for:" + msg.suid + " added to loc queue ");
}
}
UpdatePollReadTime(msg.DBid);
}
reader.Close();
}
connection.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll requests received in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
}
catch (Exception e)
{
EvaluateDBQuerryError("GetPollRequests", e);
Utils.ConsWrite(DebugMSG_Type.always, "GetPollRequests error...");
Utils.ConsWrite(DebugMSG_Type.always, e.ToString());
}
}
public void UpdatePollReadTime(int DBid)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set is_read=1 where id =" + DBid;
//Utils.ConsWrite(DebugMSG_Type.DB, "UpdatePollReadTime in DB: " + query);
//Utils.ConsWrite(DebugMSG_Type.DEV, "UpdatePollReadTime in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll read updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception excp)
{
//Exception myExcp = new Exception("Could not set SEND param " + excp.Message, excp);
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, "Error in UpdatePollReadTime: " + excp.ToString());
}
}
public void UpdatePollSentTime(int DBid, DateTime sentTime)
{
DateTime start = DateTime.Now;
try
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set is_sent=1 where id=" + DBid;
//Utils.ConsWrite(DebugMSG_Type.DEV, "UpdatePollSentTime in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll sent in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception excp)
{
//Exception myExcp = new Exception("Could not set SEND param " + excp.Message, excp);
Utils.ConsWrite(DebugMSG_Type.always, "Error in UpdatePollSentTime: " + excp.ToString());
}
}
public void UpdatePollGPSConventional(POLLmsg msg)
{
DateTime start = DateTime.Now;
try
{
if (msg.DBid > -1)
{
if (msg.lat != "")
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set responded=1, position_message_message_time='" + msg.response.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") + "', position_message_unit_id=" + Utils.GetDBidFromSUID(msg.suid) +
" where id=" + msg.DBid;
//Utils.ConsWrite(DebugMSG_Type.POLL, "Added GPS poll in DB: " + query);
LOGS.LOG("Added poll response to DB for unit:" + msg.suid);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
}
else
{
//Utils.ConsWrite(DebugMSG_Type.POLL, "Invalid position updating just response time.");
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set responded=1"
+ " where id=" + msg.DBid.ToString();
//Utils.ConsWrite(DebugMSG_Type.POLL, "Added poll response time in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
}
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
else
Utils.ConsWrite(DebugMSG_Type.always, "Poll has no dbID !!! ");
}
catch (Exception excp)
{
//Exception myExcp = new Exception("Error in UpdatePollGPS: " + excp.Message, excp);
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, "Error in UpdatePollGPS: " + excp.ToString());
}
}
public void UpdatePollGPS(POLLmsg msg)
{
DateTime start = DateTime.Now;
try
{
int maxID = -1;
using (MySqlConnection connection1 = new MySqlConnection(MyConString))
{
connection1.Open();
using (MySqlCommand command1 = connection1.CreateCommand())
{
MySqlDataReader reader1;
command1.CommandText = "select max(p.id) from poll as p" +
" inner join unit as u on(p.unit_id = u.id)" +
" inner join gateway as g on(u.gateway_id = g.id)" +
" where g.code = " + gatewayID + " and u.imei=" + msg.suid;
Console.WriteLine("Update Poll ####" + command1.CommandText);
reader1 = command1.ExecuteReader();
while (reader1.Read())
{
maxID = reader1.GetInt32(0);
}
reader1.Close();
}
connection1.Close();
}
if (maxID != -1)
{
if (msg.lat != "")
{
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set responded=1, position_message_message_time='" + msg.response.ToUniversalTime().ToString("yyyy:MM:dd HH:mm:ss") + "', position_message_unit_id=" + Utils.GetDBidFromSUID(msg.suid) +
" where id=" + maxID.ToString();
//Utils.ConsWrite(DebugMSG_Type.POLL, "Added GPS poll in DB: " + query);
LOGS.LOG("Added poll response to DB for unit:" + msg.suid);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
}
else
{
//Utils.ConsWrite(DebugMSG_Type.POLL, "Invalid position updating just response time.");
MySqlConnection connectionSet = new MySqlConnection(MyConString);
string query = "update poll set responded=1"
+ " where id=" + maxID.ToString();
//Utils.ConsWrite(DebugMSG_Type.POLL, "Added poll response time in DB: " + query);
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
connectionSet.Open();
setSent.ExecuteNonQuery();
connectionSet.Close();
}
}
else
Utils.ConsWrite(DebugMSG_Type.always, "Max ID for suid" + msg.suid + " not found");
Utils.ConsWrite(DebugMSG_Type.always, String.Format("Poll updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
}
catch (Exception excp)
{
//Exception myExcp = new Exception("Error in UpdatePollGPS: " + excp.Message, excp);
//connectionSet.Close();
Utils.ConsWrite(DebugMSG_Type.always, "Error in UpdatePollGPS: " + excp.ToString());
}
}
#endregion
///
/// Check if a gateway or it's account are enabled or not
///
/// gateway Code form conf file
public bool CheckGatewayIsEnabled(string gatewayCode)
{
DateTime start = DateTime.Now;
bool ret = false;
try
{
using (MySqlConnection connection_units = new MySqlConnection(MyConString))
{
connection_units.Open();
using (MySqlCommand command_units = connection_units.CreateCommand())
{
Utils.ConsWrite(DebugMSG_Type.DEV, "Getting subscriber info..");
MySqlDataReader reader_units;
command_units.CommandText = "SELECT A.id accountID, A.is_deactivated, G.code " +
" FROM account A " +
" INNER JOIN account_gateway AG ON AG.account_gateways_id = A.id " +
" INNER JOIN gateway G ON G.id = AG.gateway_id " +
" WHERE G.code = " + gatewayCode + " AND A.is_deactivated = 0";
reader_units = command_units.ExecuteReader();
// foreach unit
while (reader_units.Read())
{
ret = true;
}
}
connection_units.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Check Gateway Enable/Disable error..." + e.ToString());
}
TimeSpan delta = DateTime.Now.Subtract(start);
Utils.ConsWrite(DebugMSG_Type.ALERTS, "Done checking gateway enable/disabled from DB!!!!!! [" + delta.Milliseconds + "ms]");
return ret;
}
#region MOTORepeater
public List GetGatewayIDForCredentials(string username, string password)
{
List returnGwIDs = new List();
try
{
using (MySqlConnection connection = new MySqlConnection(MyConString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
MySqlDataReader reader;
command.CommandText = String.Format("SELECT U.login, U.password, G.code FROM safenet301.user U " +
" INNER JOIN account_gateway AG ON AG.account_gateways_id = U.account_id " +
" INNER JOIN gateway G ON AG.gateway_id = G.id " +
" WHERE U.login LIKE '{0}' AND U.password LIKE '{1}' ORDER BY G.code ASC", username, password );
reader = command.ExecuteReader();
while (reader.Read())
{
try
{
returnGwIDs.Add(reader.GetInt64(2));
}
catch (Exception) { }
}
reader.Close();
}
connection.Close();
}
}
catch (Exception e)
{
Utils.ConsWrite(DebugMSG_Type.always, "Error CheckSMSinDB_for_unit \n" + e.ToString());
}
return returnGwIDs;
}
public bool TestSafeMobileChanges()
{
return true;
}
#endregion
///
/// Evaluete the error received as a result of executing a DB Querry. Not every errors
/// must be treated as errors
///
/// The function name in which the error occured
/// The exception received and that needs to be evaluated
/// A boolean value telling if the querry was completed succesfully [true]
/// or if the error caused the query to be insuccessful [false]
private bool EvaluateDBQuerryError(String function, Exception ex)
{
if (ex.ToString().Contains("Duplicate"))
{
return true;
}
else if (ex.ToString().Contains("Found a row not matching the given partition set"))
{
return true;
}
else if (ex.ToString().Contains("Connection must be valid and open") ||
ex.ToString().Contains("Unable to connect to any")
|| ex.ToString().Contains("Object reference not set"))
{
//Utils.ConsWrite(DebugMSG_Type.always, e.Message);
DBconnected = false;
// restart DB because something bad happened
StartDB();
}
else
{
//Utils.WriteLine("EXCEPTION : " + ex.ToString(), ConsoleColor.Red);
}
return false;
}
}
}