669 lines
24 KiB
C#
669 lines
24 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using Npgsql;
|
|
using System.Xml;
|
|
using System.Collections;
|
|
using System.Data;
|
|
|
|
namespace SafeMobileLib
|
|
{
|
|
public class DBsubsOperationManager : DBmanager
|
|
{
|
|
public DBsubsOperationManager(string p_server, string p_dbname, string p_user, string p_password, string p_port)
|
|
: base(p_server, p_dbname, p_user, p_password, p_port)
|
|
{
|
|
|
|
}
|
|
|
|
public static readonly object lockerStatus = new object();
|
|
public sqlResponse updateSUStatus(string p_IMEI, int status)
|
|
{
|
|
string command = string.Empty;
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
try
|
|
{
|
|
|
|
int stat = get_SUstatus(p_IMEI);
|
|
|
|
|
|
if (stat == -1)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
command = String.Format("INSERT INTO lastpos (imei,lat,lng,speed,heading,di,dox,timeGMT,status) VALUES('{0}',0,0,0,0,{1},0,0,{2})", p_IMEI, 0, status);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
else if (stat != status)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
command = String.Format("UPDATE lastpos SET status = {0} where imei = '{1}'", status, p_IMEI);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
|
|
}
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
command = String.Format("INSERT INTO logmototurbo (imei,timeGMT,status) VALUES('{0}',{1},{2})", p_IMEI, DateTo70Format(DateTime.Now.ToUniversalTime()), status);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"Error write on trbolog : {o.Message}", ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public void updateOnlyStatus(string p_IMEI, int status)
|
|
{
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = String.Format("UPDATE lastpos SET status = {0} where imei='{1}'", status, p_IMEI);
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
if ((status == 9) || (status == 10))
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = String.Format("INSERT INTO logmototurbo (imei,timeGMT,status) VALUES('{0}',{1},{2})", p_IMEI, DateTo70Format(DateTime.Now.ToUniversalTime()), status);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"updateOnlyStatus : {o.Message} ");
|
|
}
|
|
|
|
}
|
|
|
|
|
|
public void updateOnlyStatus(Dictionary<string, Status_for_tab> subscribersStatuses)
|
|
{
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
String command = "UPDATE lastpos SET status = CASE imei ";
|
|
String imeisList = "";
|
|
|
|
foreach (KeyValuePair<string, Status_for_tab> de in subscribersStatuses)
|
|
{
|
|
command += "WHEN '" + de.Key + "'" + " THEN '" + (int)de.Value + "'";
|
|
imeisList += "'" + de.Key + "'" + ",";
|
|
}
|
|
|
|
// remove last , from imeis list
|
|
if (subscribersStatuses.Count > 0)
|
|
imeisList = imeisList.Substring(0, imeisList.Length - 1);
|
|
command += " ELSE status"
|
|
+ " END"
|
|
+ " WHERE imei IN (" + imeisList + ");";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine(o.Message, ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
|
|
public sqlResponse updateARSStatus(string p_IMEI, int status)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
try
|
|
{
|
|
int stat = get_ARSstatus(p_IMEI);
|
|
if (stat == -1)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = String.Format("INSERT INTO lastpos_ars (imei,timeGMT,status) VALUES('{0}',{1},{2})", p_IMEI, DateTo70Format(DateTime.Now.ToUniversalTime()), status);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = String.Format("UPDATE lastpos_ars SET status ={0},timegmt={1} where imei='{2}'", status, DateTo70Format(DateTime.Now.ToUniversalTime()), p_IMEI);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"updateARSStatus : {o.Message}");
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public int getSUenableDisableStatus(string radioID)
|
|
{
|
|
|
|
int respStatus = -1;
|
|
int sc_id = 0;
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT sc_id from subscriber where imei='" + radioID + "'";
|
|
//get sc_id from imei/radioID
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
if (sc_id != 0)
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT status from subscriber_stun where sc_id={sc_id}";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
|
|
respStatus = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
//if this status is not yet in DB added it
|
|
if (respStatus == -1)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = String.Format("INSERT INTO subscriber_stun (sc_id,status) VALUES({0},{1})", sc_id, 1);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
respStatus = 1;
|
|
}
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
Utils.WriteLine($"Unit {radioID} is not in the Database", ConsoleColor.Red);
|
|
return respStatus;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
|
|
Utils.WriteLine(o.Message, ConsoleColor.Red);
|
|
throw new ArgumentException(o.ToString());
|
|
}
|
|
|
|
return respStatus;
|
|
}
|
|
|
|
public sqlResponse updateSUenableDisableStatus(string radioID, int status)
|
|
{
|
|
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
int sc_id = 0;
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT sc_id from subscriber where imei = '{radioID}'";
|
|
//get sc_id from imei/radioID
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
|
|
|
|
command = String.Format("UPDATE subscriber_stun SET status ={0} where sc_id='{1}'", status, sc_id);
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
Console.WriteLine(cmd.CommandText);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
resp = sqlResponse.SQLerror;
|
|
throw new ArgumentException(o.ToString());
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public int get_SUstatus(string IMEI)
|
|
{
|
|
string command = $"SELECT status FROM lastpos WHERE imei='{IMEI}'";
|
|
return GetInt32Result(command);
|
|
}
|
|
|
|
|
|
public int get_ARSstatus(string IMEI)
|
|
{
|
|
|
|
string command = $"SELECT status FROM lastpos_ars WHERE imei='{IMEI}'";
|
|
return GetInt32Result(command);
|
|
}
|
|
|
|
|
|
private uint DateTo70Format(DateTime param)
|
|
{
|
|
long nOfSeconds;
|
|
System.DateTime dt70 = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
|
|
TimeSpan span = param - dt70;
|
|
nOfSeconds = (long)span.TotalSeconds;
|
|
return ((uint)nOfSeconds);
|
|
}
|
|
|
|
public void insertDIRECTWithAltitude(String radioID, String lat, String lng, Int32 timeGMT, String speed, String di, String address, String alt, string logId)
|
|
{
|
|
String LAT, LNG, ALT ;
|
|
Double dlat = 0, dlng = 0, dalt = 0;
|
|
string field = "";
|
|
string value = "";
|
|
|
|
|
|
try
|
|
{
|
|
int sc_id = GetInt32Result($"SELECT sc_id FROM subscriber WHERE imei='{radioID}'");
|
|
if (sc_id < 0)
|
|
throw new ApplicationException($"No subscriber found for imei='{radioID}'");
|
|
|
|
using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
|
|
{
|
|
conn.Open();
|
|
|
|
LAT = lat;
|
|
LNG = lng;
|
|
ALT = alt;
|
|
|
|
try
|
|
{
|
|
dlat = Convert.ToDouble(LAT);
|
|
dlng = Convert.ToDouble(LNG);
|
|
dalt = Convert.ToDouble(ALT);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
Console.WriteLine("Erorr on convert float1");
|
|
}
|
|
|
|
|
|
if ((dlat > 90) || (dlng > 180) || (dlat < -90) || (dlng < -180))
|
|
{
|
|
LAT = LAT.Replace('.', ',');
|
|
LNG = LNG.Replace('.', ',');
|
|
ALT = ALT.Replace('.', ',');
|
|
try
|
|
{
|
|
dlat = Convert.ToDouble(LAT);
|
|
dlng = Convert.ToDouble(LNG);
|
|
dalt = Convert.ToDouble(ALT);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
Console.WriteLine("Erorr on convert float2");
|
|
}
|
|
}
|
|
|
|
Boolean firsttime = true;
|
|
Boolean retry = true;
|
|
|
|
while (retry)
|
|
{
|
|
retry = false;
|
|
try
|
|
{
|
|
|
|
double sc_ev = sc_id * 10000000000 + timeGMT;
|
|
|
|
|
|
int n;
|
|
if (int.TryParse(logId, out n))
|
|
{
|
|
field = logId == "" ? "" : ",tallysman_log_id";
|
|
value = logId == "" ? "" : $",{logId}";
|
|
}
|
|
|
|
if (address == null || address.Trim().Length < 1)
|
|
address = "";
|
|
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand())
|
|
{
|
|
cmd.Connection = conn;
|
|
|
|
cmd.CommandText = $"INSERT INTO messages (sc_id, lat, lng, speed, di, dox, timeGMT, scevtime, address, altitude {field}) " +
|
|
$" VALUES ({sc_id},{dlat},{dlng},{speed},{di},0,{timeGMT.ToString()},{sc_ev.ToString()},'{address}',{alt}{value})";
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
|
|
string command = string.Empty;
|
|
if (!((Math.Round(dlat) == 0) && (Math.Round(dlng) == 0)))
|
|
{
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand())
|
|
{
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = "update lastpos set lat=@lat1, lng=@lng1, altitude=@alt1, speed = " + speed + ", di=" + di + ", dox=0, timeGMT=" + timeGMT.ToString() + ", address='' where imei='" + radioID + "' and timeGMT<" + timeGMT.ToString();
|
|
cmd.Parameters.Add("@lat1", NpgsqlTypes.NpgsqlDbType.Double).Value = dlat;
|
|
cmd.Parameters.Add("@lng1", NpgsqlTypes.NpgsqlDbType.Double).Value = dlng;
|
|
cmd.Parameters.Add("@alt1", NpgsqlTypes.NpgsqlDbType.Double).Value = dalt;
|
|
cmd.Prepare();
|
|
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand())
|
|
{
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = $"UPDATE lastpos SET timeGMT={timeGMT.ToString()} WHERE imei='{radioID}' AND timeGMT< {timeGMT.ToString()}";
|
|
|
|
cmd.Prepare();
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
if (o.ToString().Contains("duplicate"))
|
|
{
|
|
Utils.WriteLine("Duplicate GPS position", ConsoleColor.Yellow);
|
|
}
|
|
|
|
else
|
|
{
|
|
Utils.WriteLine("Exception : " + o.ToString(), ConsoleColor.Red);
|
|
if (firsttime)
|
|
{
|
|
retry = true;
|
|
firsttime = false;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
conn.Close();
|
|
}
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
if (!ex.Message.Contains("duplicate"))
|
|
Utils.WriteLine("Exception on inserting gps position with altitude " + ex.ToString(), ConsoleColor.Red);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Detect if the application is Sierra Wireless by counting the number of columns
|
|
/// with name of phone and email
|
|
/// </summary>
|
|
/// <returns>Boolean representing true if Sierra Wireless and false otherwise</returns>
|
|
public bool IsSierraWireless()
|
|
{
|
|
|
|
try
|
|
{
|
|
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT COUNT(column_name) FROM information_schema.columns " +
|
|
" WHERE table_name='subscriber' and (column_name='phone' or column_name = 'email')";
|
|
|
|
object temp = null;
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
temp = cmd.ExecuteScalar();
|
|
}
|
|
|
|
return (temp != null && temp.ToString() == "2");
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
return false;
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
public Hashtable getAll_imei_sc_id(string GroupBy)
|
|
{
|
|
Hashtable List = new Hashtable();
|
|
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
|
|
string command = "SELECT COUNT(column_name) FROM information_schema.columns " +
|
|
" WHERE table_name='subscriber' and (column_name='phone' or column_name = 'email')";
|
|
|
|
object temp = null;
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
temp = cmd.ExecuteScalar();
|
|
}
|
|
|
|
if (temp != null & temp.ToString() == "2")
|
|
{
|
|
|
|
SW_Subscriber result;
|
|
|
|
|
|
command = "SELECT sc_id, imei, phone, email FROM subscriber WHERE ( phone IS NOT NULL OR email IS NOT NULL)";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
|
|
result = new SW_Subscriber
|
|
{
|
|
sc_id = Reader.GetInt32(0),
|
|
imei = Reader.GetString(1),
|
|
phone = (Reader.GetValue(2) == DBNull.Value) ? string.Empty : Reader.GetString(2),
|
|
email = (Reader.GetValue(3) == DBNull.Value) ? string.Empty : Reader.GetString(3)
|
|
};
|
|
|
|
switch (GroupBy)
|
|
{
|
|
case "ByPhone":
|
|
if (!List.ContainsKey(Reader.GetString(2)))
|
|
List.Add(Reader.GetString(2), result);
|
|
break;
|
|
case "ByScId":
|
|
if (!List.ContainsKey(Reader.GetString(1)))
|
|
List.Add(Reader.GetString(1), result);
|
|
break;
|
|
case "ByMail":
|
|
if (!List.ContainsKey(Reader.GetString(3)))
|
|
List.Add(Reader.GetString(3), result);
|
|
break;
|
|
default:
|
|
if (!List.ContainsKey(Reader.GetString(1)))
|
|
List.Add(Reader.GetString(1), result);
|
|
break;
|
|
}
|
|
}// while
|
|
}//using
|
|
}//using
|
|
|
|
}//if
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
}
|
|
|
|
return List;
|
|
}
|
|
|
|
|
|
|
|
public static readonly object locker = new object();
|
|
public Hashtable getAll_imei_sc_id_status()
|
|
{
|
|
Hashtable List = new Hashtable();
|
|
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT s.sc_id,s.imei,l.status FROM subscriber s,lastpos l WHERE s.imei=l.imei";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
List.Add(Reader.GetString(1), new Scid_and_Status(Reader.GetInt32(0), Reader.GetInt32(2)));
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
}
|
|
|
|
return List;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
}
|