1332 lines
59 KiB
C#
1332 lines
59 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using System.Linq;
|
|
using System.Threading;
|
|
using MySql.Data.MySqlClient;
|
|
using SafeMobileLib.MessageDecoders;
|
|
|
|
namespace SafeMobileLib
|
|
{
|
|
public class DBhandle
|
|
{
|
|
public static string gatewayID;
|
|
|
|
public bool DBconnected = false;
|
|
private MySqlConnection connection;
|
|
private MySqlCommand command;
|
|
private static string MyConString;
|
|
private bool conStatus;
|
|
private bool running = false;
|
|
|
|
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 Server, string Database, string User, string Password)
|
|
{
|
|
MyConString = "SERVER=" + Server + "; " +
|
|
"DATABASE=" + Database + "; " +
|
|
"UID=" + User + "; " +
|
|
"PASSWORD=" + Password + "; Pooling=true;";
|
|
}
|
|
|
|
public void StartDB()
|
|
{
|
|
running = true;
|
|
int count = 0;
|
|
while (DBconnected == false && running)
|
|
{
|
|
|
|
if (count++ % 20 == 0)
|
|
{
|
|
if (connection != null)
|
|
StopDB();
|
|
try
|
|
{
|
|
Utils.WriteLine("»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
|
|
Utils.WriteLine("»»»»»»»»»»»»»»»» Connecting to MYSQL server ««««««««««««««««");
|
|
Utils.WriteLine("»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
|
|
|
|
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.WriteLine("»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
|
|
Utils.WriteLine("»»»»»»»»»»»»»»» MYSQL Connection established «««««««««««««««");
|
|
Utils.WriteLine("»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»««««««««««««««««««««««««««««««");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Utils.WriteLine("DB Start: " + ex.ToString(), ConsoleColor.Red);
|
|
DBconnected = false;
|
|
|
|
Utils.WriteLine("---- Trying to reconnect to DB in 10 seconds.", ConsoleColor.Red);
|
|
|
|
}
|
|
}
|
|
Thread.Sleep(500);
|
|
}
|
|
}
|
|
|
|
public void StopDB()
|
|
{
|
|
try
|
|
{
|
|
running = false;
|
|
if (connection != null)
|
|
connection.Close();
|
|
DBconnected = false;
|
|
if (tCheckConnection != null)
|
|
{
|
|
tCheckConnection.Dispose();
|
|
tCheckConnection = null;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Utils.WriteLine("StopDB Error " + ex.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
|
|
#region new Safenet
|
|
|
|
/// <summary>
|
|
/// Loads unit info from DB (reporting interval) adds it to (hashtable)ht_SUInfo
|
|
/// </summary>
|
|
/// <param name="gatewayCode">gateway Code form conf file</param>
|
|
public bool LoadUnitInfo(Int64 gatewayCode)
|
|
{
|
|
return LoadUnitInfo(gatewayCode + "");
|
|
}
|
|
|
|
/// <summary>
|
|
/// Loads unit info from DB (reporting interval) adds it to (hashtable)ht_SUInfo
|
|
/// </summary>
|
|
/// <param name="gatewayCode">gateway Code form conf file</param>
|
|
public bool LoadUnitInfo(string gatewayCode)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
bool ret = false;
|
|
try
|
|
{
|
|
using (MySqlConnection connection_units = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection_units.State != System.Data.ConnectionState.Open)
|
|
connection_units.Open();
|
|
|
|
using (MySqlCommand command_units = connection_units.CreateCommand())
|
|
{
|
|
using (MySqlConnection connection_alert = new MySqlConnection(MyConString))
|
|
{
|
|
if (connection_alert.State != System.Data.ConnectionState.Open)
|
|
connection_alert.Open();
|
|
|
|
using (MySqlCommand command_alert = connection_alert.CreateCommand())
|
|
{
|
|
//Utils.WriteLine("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.IsDBNull(4) ? false : (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<Alert>();
|
|
|
|
|
|
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.WriteLine("LoadUnitInfo (ALERTS)error..." + ex.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
}//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.WriteLine("New interval: " + sui.repInterval + " found for suid:" + suid);
|
|
}
|
|
else
|
|
{
|
|
//Utils.WriteLine("Same interval: " + sui.repInterval + " found for suid:" + suid);
|
|
}
|
|
}
|
|
catch (Exception ex1)
|
|
{
|
|
Utils.WriteLine("LoadUnitInfo (parsing unit error)" + ex1.ToString(), ConsoleColor.Red);
|
|
}
|
|
}//while (reader_units.Read())
|
|
|
|
reader_units.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Loaded {0} units [{1} new] in {2} ms", nrOfUnits, nrOfNewUnits, DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection_alert.Close();
|
|
|
|
}
|
|
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection_units.Close();
|
|
}
|
|
|
|
ret = true;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("LoadUnitInfo error..." + e.ToString(), ConsoleColor.Red);
|
|
ret = false;
|
|
}
|
|
TimeSpan delta = DateTime.Now.Subtract(start);
|
|
return ret;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Loads address from DB
|
|
/// </summary>
|
|
/// <param name="gatewayID">gateway ID form conf file</param>
|
|
public bool LoadAddressList(string gatewayID)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
bool ret = false;
|
|
try
|
|
{
|
|
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection2.State !=System.Data.ConnectionState.Open)
|
|
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;";
|
|
}
|
|
|
|
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();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Got Address Hash in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection2.Close();
|
|
}
|
|
ret = true;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("LoadAddressList error: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Insert in ars_message
|
|
/// </summary>
|
|
/// <param name="suid"> subscriber id </param>
|
|
/// <param name="p_message"> message ON/OFF (string) </param>
|
|
/// <returns> </returns>
|
|
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.WriteLine(String.Format("■■■ ARS inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
return true;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
return EvaluateDBQuerryError("Insert ARS", e);
|
|
}
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Insert location in address list wiht string addr = null
|
|
/// </summary>
|
|
/// <param name="cell"> GPS data </param>
|
|
/// <returns> adr.addr_hash </returns>
|
|
public void Insert_AddressList(htCell_t cell)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
Address adr = new Address(cell);
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
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, connectionSet);
|
|
adr = null;
|
|
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Address inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
if (!e.ToString().Contains("Duplicate"))
|
|
{
|
|
Utils.WriteLine("Error inserting Address in database: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Insert into messages
|
|
/// </summary>
|
|
/// <param name="addrID">ht_cell </param>
|
|
public bool Insert_messages(htCell_t cell)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
Address adr = new Address(cell);
|
|
int unitID = Utils.GetDBidFromSUID(cell.suid);
|
|
if (unitID == -1)
|
|
{
|
|
Utils.WriteLine("Unit <" + cell.suid + "> not assign to this GW!!!");
|
|
LOGS.LOG("Unit <" + cell.suid + "> not assign to this GW!!!");
|
|
return false;
|
|
}
|
|
try
|
|
{
|
|
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(System.Globalization.CultureInfo.InvariantCulture) +
|
|
"," + Math.Round(cell.d_lng, 5).ToString(System.Globalization.CultureInfo.InvariantCulture) +
|
|
"," + cell.spd + ")";
|
|
|
|
LOGS.LOG(query);
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connection);
|
|
setSent.CommandTimeout = 4;
|
|
setSent.ExecuteNonQuery();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Position inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
return true;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// if position already exists - do nothing
|
|
return EvaluateDBQuerryError("Insert GPS", e);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// feed SN_Queues.computeAddressQueue with addresses that dont have an compute addr.
|
|
/// </summary>
|
|
public static void GetUnComputeAddr()
|
|
{
|
|
//ex:
|
|
//Address addr = new Address("45.33342","43.5454");
|
|
//SN_Queues.computeAddressQueue.PostItem(addr);
|
|
|
|
try
|
|
{
|
|
Utils.WriteLine("GetUnComputeAddr.");
|
|
using (MySqlConnection connection2 = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection2.State != System.Data.ConnectionState.Open)
|
|
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();
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection2.Close();
|
|
}
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("GetUnComputeAddr error: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Update address value in DB after fetching it from google
|
|
/// </summary>
|
|
/// <param name="addr"></param>
|
|
public static void UpdateAddressValue(Address addr)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
string query = "update address set address='" + addr.Value + "'" +
|
|
" where address_hash='" + addr.addr_hash + "';";
|
|
|
|
Utils.WriteLine("UpdateAddressValue in DB: " + query);
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Address updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("UpdateAddressValue error: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Insert location in address list wiht string addr = null
|
|
/// </summary>
|
|
/// <param name="cell"> GPS data </param>
|
|
/// <returns> adr.addr_hash </returns>
|
|
public void InsertTallysmanEvent(TallysmanMsg e)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
int suDBid = 0;
|
|
lock (SN_Queues.ht_SUInfo.SyncRoot)
|
|
{
|
|
if (SN_Queues.ht_SUInfo != null)
|
|
{
|
|
if (SN_Queues.ht_SUInfo.ContainsKey(e.RadioID + ""))
|
|
suDBid = ((SUinfo)SN_Queues.ht_SUInfo[e.RadioID + ""]).DBid;
|
|
}
|
|
}
|
|
|
|
if (suDBid == 0)
|
|
{
|
|
Utils.WriteLine("Unit " + e.RadioID + " not assigned to the DB", ConsoleColor.Green);
|
|
return;
|
|
}
|
|
|
|
// update the radio ID with DB id
|
|
e.RadioID = suDBid;
|
|
|
|
|
|
|
|
string query = String.Format("INSERT INTO tallysman_data (log_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_delta_sec) " +
|
|
" 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.LogId, e.EventType, e.RadioID, e.Latitude, e.Longitude, (((new DateTime(1970, 1, 1)).AddSeconds(e.GPSFixTime)).ToUniversalTime()).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);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Tallysman Event inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception e2)
|
|
{
|
|
if (!e2.ToString().Contains("Duplicate"))
|
|
{
|
|
Utils.WriteLine("Error inserting Tallysman Event in database: " + e2.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
public bool Insert_GW_status(string gatewayID, int status)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
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);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Gateway Status inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
|
|
return true;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("Error inserting gateway status in database: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
public bool Insert_GW_status(string gatewayID, int status, string message)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
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);
|
|
|
|
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Gateway Status inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
|
|
return true;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("Error inserting gateway status in database: " + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
#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.WriteLine("[SMS] Unit with IMEI:" + p_radioID + " not found in DB!!!!");
|
|
LOGS.LOG("Unit with IMEI:" + p_radioID + " not found in DB!!!!");
|
|
return false;
|
|
}
|
|
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
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);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ SMS inserted in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
|
|
return true;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("Error inserting SMS in DB\n" + e.ToString(), ConsoleColor.Red);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public void UpdateSMSRead(Int32 DBid)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
string query = "update text_message set is_read=1 where id =" + DBid;
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ SMS read updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception excp)
|
|
{
|
|
Utils.WriteLine("Error in UpdateSMSRead: " + excp.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
public void confirmSMS(SMSmsg p_msg)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
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;
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ SMS confirmed in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception excp)
|
|
{
|
|
Utils.WriteLine("Could not update SMS confirmed: " + excp.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
public void checkSMSinDB()
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection.State != System.Data.ConnectionState.Open)
|
|
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<SMSmsg> smsRequestsList = new List<SMSmsg>();
|
|
|
|
while (reader.Read())
|
|
{
|
|
LOGS.LOG("@SMS@ " + "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);
|
|
|
|
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");
|
|
}
|
|
|
|
}
|
|
|
|
Utils.WriteLine(String.Format("■■■ Got {0} SMS requests in {1} ms [DB]", countSMS, DateTime.Now.Subtract(start).Milliseconds));
|
|
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection.Close();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("checkSMSinDB error...");
|
|
Utils.WriteLine(e.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
public void GetPollRequests4Conventional()
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection.State != System.Data.ConnectionState.Open)
|
|
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) + "';";
|
|
|
|
|
|
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<MotoTRBOcmdMsg> toAddPollRequests = new List<MotoTRBOcmdMsg>();
|
|
|
|
while (reader.Read())
|
|
{
|
|
// increase rows count
|
|
numRows++;
|
|
|
|
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();
|
|
|
|
toAddPollRequests.Add(TRBOmsg);
|
|
}
|
|
}
|
|
|
|
UpdatePollReadTime(msg.DBid);
|
|
}
|
|
reader.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Got {0} Poll requests in {1} ms [DB]",
|
|
numRows, DateTime.Now.Subtract(start).Milliseconds));
|
|
|
|
foreach (MotoTRBOcmdMsg msg in toAddPollRequests)
|
|
{
|
|
SN_Queues.locationQueue.PostItem(msg);
|
|
}
|
|
}
|
|
|
|
if(ShouldCloseConnection())
|
|
connection.Close();
|
|
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("GetPollRequests error...");
|
|
Utils.WriteLine(e.ToString());
|
|
}
|
|
}
|
|
|
|
public void UpdatePollReadTime(int DBid)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
string query = "update poll set is_read=1 where id =" + DBid;
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Poll read time updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception excp)
|
|
{
|
|
Utils.WriteLine("Error in UpdatePollReadTime: " + excp.ToString());
|
|
}
|
|
}
|
|
|
|
public void UpdatePollSentTime(int DBid, DateTime sentTime)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
string query = "update poll set is_sent=1 where id=" + DBid;
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
|
|
Utils.WriteLine(String.Format("■■■ Poll sent time updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception excp)
|
|
{
|
|
Utils.WriteLine("Error in UpdatePollSentTime: " + excp.ToString());
|
|
}
|
|
}
|
|
|
|
public void UpdatePollGPSConventional(POLLmsg msg)
|
|
{
|
|
DateTime start = DateTime.Now;
|
|
try
|
|
{
|
|
if (msg.DBid > -1)
|
|
{
|
|
if (msg.lat != "")
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
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;
|
|
|
|
LOGS.LOG("Added poll response to DB for unit:" + msg.suid);
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
using (MySqlConnection connectionSet = new MySqlConnection(MyConString))
|
|
{
|
|
if (connectionSet.State != System.Data.ConnectionState.Open)
|
|
connectionSet.Open();
|
|
|
|
string query = "update poll set responded=1"
|
|
+ " where id=" + msg.DBid.ToString();
|
|
|
|
MySqlCommand setSent = new MySqlCommand(query, connectionSet);
|
|
|
|
|
|
setSent.ExecuteNonQuery();
|
|
|
|
if (ShouldCloseConnection())
|
|
connectionSet.Close();
|
|
}
|
|
}
|
|
|
|
Utils.WriteLine(String.Format("■■■ Poll updated in {0} ms [DB]", DateTime.Now.Subtract(start).Milliseconds));
|
|
}
|
|
}
|
|
catch (Exception excp)
|
|
{
|
|
Utils.WriteLine("Error in UpdatePollGPS: " + excp.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
#region MOTORepeater
|
|
public List<SafenetGatewayIdent> GetRepeaterGatewayIDForCredentials(string username, string password)
|
|
{
|
|
List<SafenetGatewayIdent> returnGWs = new List<SafenetGatewayIdent>();
|
|
try
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection.State != System.Data.ConnectionState.Open)
|
|
connection.Open();
|
|
|
|
using (MySqlCommand command = connection.CreateCommand())
|
|
{
|
|
MySqlDataReader reader;
|
|
command.CommandText = String.Format("SELECT U.login, U.password, G.id, 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 " +
|
|
" RIGHT JOIN gateway_direct_connect_config GC ON GC.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
|
|
{
|
|
returnGWs.Add(new SafenetGatewayIdent(reader.GetInt64(2), reader.GetInt64(3)));
|
|
}
|
|
catch (Exception ee) { Utils.WriteLine(ee.ToString(), ConsoleColor.Red); }
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection.Close();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("Error GetRepeaterGatewayIDForCredentials \n" + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return returnGWs;
|
|
}
|
|
|
|
|
|
public RepeaterConfig GetRepeaterGatewayConfiguration(Int64 gatewayCode)
|
|
{
|
|
RepeaterConfig config = null;
|
|
try
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(MyConString))
|
|
{
|
|
if(connection.State != System.Data.ConnectionState.Open)
|
|
connection.Open();
|
|
|
|
using (MySqlCommand command = connection.CreateCommand())
|
|
{
|
|
MySqlDataReader reader;
|
|
command.CommandText = String.Format("SELECT GC.id, GC.version, GC.auth_key, GC.ddms_ars, GC.ddms_gps, GC.ddmsip, " +
|
|
"GC.ddms_port, GC.ddms_telemetry, GC.ddms_text, GC.gateway_id, GC.peer_radio_id, " +
|
|
"GC.repeater_port, GC.repeater_type, GC.slot1, GC.slot2 , GC.repeaterip, G.id, G.code, " +
|
|
"GC.tallysman_port " +
|
|
" FROM gateway_direct_connect_config GC " +
|
|
"INNER JOIN gateway G ON G.id = GC.gateway_id " +
|
|
"WHERE G.code = {0}", gatewayCode);
|
|
|
|
reader = command.ExecuteReader();
|
|
int column = 0;
|
|
while (reader.Read())
|
|
{
|
|
try
|
|
{
|
|
config = new RepeaterConfig();
|
|
config.ConfigID = reader.GetInt32(column++);
|
|
config.Version = reader.GetInt64(column++);
|
|
config.AuthenticationKey = (reader.IsDBNull(column++) ? null : reader.GetString(column-1));
|
|
config.ARSPort = reader.GetInt32(column++);
|
|
config.LocationPort = reader.GetInt32(column++);
|
|
config.DDMSIP = reader.GetString(column++);
|
|
config.DDMSPort = reader.GetInt32(column++);
|
|
config.TelemetryPort = reader.GetInt32(column++);
|
|
config.TextPort = reader.GetInt32(column++);
|
|
config.GatewayID = reader.GetInt64(column++);
|
|
config.PeerID = reader.GetInt64(column++);
|
|
config.RepeaterPort = reader.GetInt32(column++);
|
|
config.SystemType = reader.GetString(column++);
|
|
config.Slot1 = (reader.IsDBNull(column++) ? null : reader.GetString(column-1));
|
|
config.Slot2 = (reader.IsDBNull(column++) ? null : reader.GetString(column-1));
|
|
config.RepeaterIP = reader.GetString(column++);
|
|
config.GatewayID = reader.GetInt64(column++);
|
|
config.GatewayCode = reader.GetInt64(column++);
|
|
config.TallysmanPort = reader.GetInt32(column++);
|
|
}
|
|
catch (Exception e) { Utils.WriteLine("Error Get Repeater Config \n" + e.ToString(), ConsoleColor.Red); }
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
if (ShouldCloseConnection())
|
|
connection.Close();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Utils.WriteLine("Error GetRepeaterGatewayConfiguration \n" + e.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return config;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// Return if the connection needs to be closed in case of a connection that is not a part of
|
|
/// the connection pool
|
|
/// </summary>
|
|
/// <returns>True if the connection is not from the connection pool, false otherwise</returns>
|
|
private static bool ShouldCloseConnection()
|
|
{
|
|
return MyConString.ToString().Contains("Pooling=false");
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Evaluete the error received as a result of executing a DB Querry. Not every errors
|
|
/// must be treated as errors
|
|
/// </summary>
|
|
/// <param name="function">The function name in which the error occured</param>
|
|
/// <param name="ex">The exception received and that needs to be evaluated</param>
|
|
/// <returns>A boolean value telling if the querry was completed succesfully [true]
|
|
/// or if the error caused the query to be insuccessful [false]</returns>
|
|
private bool EvaluateDBQuerryError(String function, Exception ex)
|
|
{
|
|
if (ex.ToString().Contains("Duplicate"))
|
|
{
|
|
return true;
|
|
}
|
|
else if (ex.ToString().Contains("Connection must be valid and open"))
|
|
{
|
|
//Utils.WriteLine(e.Message);
|
|
DBconnected = false;
|
|
// restart DB because something bad happened
|
|
StartDB();
|
|
}
|
|
|
|
return false;
|
|
}
|
|
}
|
|
}
|