1790 lines
81 KiB
C#
1790 lines
81 KiB
C#
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;
|
|
}
|
|
|
|
|
|
/// <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 + "");
|
|
}
|
|
|
|
|
|
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());
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <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))
|
|
{
|
|
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<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.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;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Loads address from DB
|
|
/// </summary>
|
|
/// <param name="gatewayID">gateway ID form conf file</param>
|
|
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;
|
|
}
|
|
|
|
/// <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.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);
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Update ARS last pos
|
|
/// </summary>
|
|
/// <param name="arsID"> arsID that was returned by Insert_ARSmsg </param>
|
|
private void Update_ARSlp(int arsID)
|
|
{
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Insert location in address list wiht string addr = null
|
|
/// </summary>
|
|
/// <param name="cell"> GPS data </param>
|
|
/// <returns> adr.addr_hash </returns>
|
|
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());
|
|
}*/
|
|
//}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
/// <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
|
|
{
|
|
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);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// Insert into messages
|
|
/// </summary>
|
|
/// <param name="addrID">ht_cell </param>
|
|
///
|
|
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);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Update last pos
|
|
/// </summary>
|
|
/// <param name="addreID">string addr_hash (return by Insert_AddressList or get from ht_addressList </param>
|
|
private void Update_lastpos(string addr_hash)
|
|
{
|
|
}
|
|
|
|
/// <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.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());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// feed SN_Queues.computeAddressQueue with addresses that dont have an compute addr.
|
|
/// </summary>
|
|
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;
|
|
}
|
|
|
|
/// <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
|
|
{
|
|
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<SMSmsg> smsRequestsList = new List<SMSmsg>();
|
|
|
|
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<SMSmsg> CheckSMSinDB_for_unit(string p_radioID)
|
|
{
|
|
List<SMSmsg> returnList = new List<SMSmsg>();
|
|
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<MotoTRBOcmdMsg> toAddPollRequests = new List<MotoTRBOcmdMsg>();
|
|
|
|
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
|
|
|
|
|
|
/// <summary>
|
|
/// Check if a gateway or it's account are enabled or not
|
|
/// </summary>
|
|
/// <param name="gatewayCode">gateway Code form conf file</param>
|
|
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<Int64> GetGatewayIDForCredentials(string username, string password)
|
|
{
|
|
List<Int64> returnGwIDs = new List<Int64>();
|
|
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
|
|
|
|
|
|
/// <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("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;
|
|
}
|
|
}
|
|
}
|