709 lines
23 KiB
C#
709 lines
23 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using Npgsql;
|
|
using System.Collections;
|
|
using System.Text;
|
|
|
|
namespace SafeMobileLib
|
|
{
|
|
public class DBtelemetryManager : DBmanager
|
|
{
|
|
public DBtelemetryManager(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 List<TelemetryObj> getAllTelemetryObjs(int sc_id)
|
|
{
|
|
|
|
List<TelemetryObj> resp = new List<TelemetryObj>();
|
|
|
|
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT id, digital_nr, digital_type,transition,\"name\",alarm FROM \"telemetry\" WHERE sc_id=" + sc_id.ToString() + " order by \"name\"";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
string key = Reader.GetValue(0).ToString();
|
|
string value = Reader.GetValue(1).ToString();
|
|
bool alarm = (Reader.GetInt32(5) == 1);
|
|
|
|
TelemetryObj t = new TelemetryObj(Reader.GetInt32(0), Reader.GetInt32(1),
|
|
Reader.GetString(2), Reader.GetString(3), Reader.GetString(4), alarm);
|
|
|
|
resp.Add(t);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if (resp.Count < 5)
|
|
{
|
|
for (int d = 1; d <= 5; d++)
|
|
{
|
|
bool found = false;
|
|
foreach (TelemetryObj tel in resp)
|
|
{
|
|
if (tel.DigitalNr == d)
|
|
{
|
|
found = true;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (!found)
|
|
{
|
|
TelemetryObj telObj = new TelemetryObj()
|
|
{
|
|
Alarm = false,
|
|
DigitalNr = d,
|
|
DigitalType = "IN",
|
|
Name = "Digital " + d,
|
|
Sc_Id = sc_id,
|
|
Transition = "Low"
|
|
};
|
|
|
|
addTelemetryObj(telObj, sc_id);
|
|
resp.Add(telObj);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.ToString());
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public List<TelemetryObj> getAllTelemetryObjsAll()
|
|
{
|
|
|
|
List<TelemetryObj> resp = new List<TelemetryObj>();
|
|
|
|
string command = "SELECT id, digital_nr, digital_type,transition,\"name\",alarm,sc_id FROM \"telemetry\" order by \"name\"";
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
string key = Reader.GetValue(0).ToString();
|
|
string value = Reader.GetValue(1).ToString();
|
|
bool alarm = (Reader.GetInt32(5) == 1);
|
|
|
|
TelemetryObj t = new TelemetryObj(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2), Reader.GetString(3), Reader.GetString(4), alarm, Reader.GetInt32(6));
|
|
resp.Add(t);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message, ConsoleColor.Red);
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public List<TelemetryObj> getAllTelObjs()
|
|
{
|
|
List<TelemetryObj> resp = new List<TelemetryObj>();
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT id, digital_nr, digital_type,transition,\"name\",alarm,sc_id FROM \"telemetry\" order by \"name\"";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
string key = Reader.GetValue(0).ToString();
|
|
string value = Reader.GetValue(1).ToString();
|
|
bool alarm = (Reader.GetInt32(5) == 1);
|
|
TelemetryObj t = new TelemetryObj(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2), Reader.GetString(3), Reader.GetString(4), alarm, Reader.GetInt32(6));
|
|
resp.Add(t);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message, ConsoleColor.Red);
|
|
}
|
|
|
|
|
|
return resp;
|
|
}
|
|
|
|
public List<TelemetryObj> getAllTelemetryObjsByRadioID(string RadioID)
|
|
{
|
|
int sc_id = getSCID(RadioID);
|
|
|
|
return getAllTelemetryObjs(sc_id);
|
|
|
|
}
|
|
|
|
public sqlResponse addTelemetryObj(TelemetryObj telObj, int sc_id)
|
|
{
|
|
|
|
sqlResponse resp;
|
|
|
|
try
|
|
{
|
|
|
|
|
|
int telAlarm = ((telObj.Alarm) ? 1 : 0);
|
|
string command = "insert into \"telemetry\" (sc_id, digital_nr, digital_type,transition,\"name\",alarm)" +
|
|
$" VALUES({sc_id},{telObj.DigitalNr},'{telObj.DigitalType}','{telObj.Transition}','{telObj.Name}',{telAlarm})";
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public sqlResponse addRangeTelemetryObj(TelemetryObj telObj, ArrayList List_sc_id)
|
|
{
|
|
|
|
sqlResponse resp;
|
|
try
|
|
{
|
|
|
|
|
|
int telAlarm = ((telObj.Alarm) ? 1 : 0);
|
|
StringBuilder sb = new StringBuilder("");
|
|
sb.Append("insert into \"telemetry\" (sc_id, digital_nr, digital_type,transition,\"name\",alarm) VALUES ");
|
|
|
|
for (int i = 0; i < List_sc_id.Count; i++)
|
|
{
|
|
Int32 sc_id = (Int32)List_sc_id[i];
|
|
sb.Append("(" + sc_id + "," + telObj.DigitalNr + ",'" + telObj.DigitalType + "','" +
|
|
telObj.Transition + "','" + telObj.Name + "'," + telAlarm + ")" + (i == List_sc_id.Count - 1 ? "" : ","));
|
|
}
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sb.ToString(), connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Utils.WriteLine("addRangeTelemetryObj: " + ee.ToString(), ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
|
|
public sqlResponse updateTelemtry(List<TelemetryObj> allTelemetries, List<int> toUpdatePins)
|
|
{
|
|
if (allTelemetries.Count == 0)
|
|
return sqlResponse.done;
|
|
|
|
|
|
sqlResponse resp = sqlResponse.done;
|
|
|
|
StringBuilder sb = new StringBuilder(1024);
|
|
|
|
try
|
|
{
|
|
// iterate through all pins
|
|
for (int d = 1; d <= 5; d++)
|
|
{
|
|
// skip pins that are not desired to be updated
|
|
if (!toUpdatePins.Contains(d))
|
|
continue;
|
|
|
|
sb.Clear();
|
|
// store the state for current pin
|
|
TelemetryObj telObj = null;
|
|
// store all sc ids for current pin state
|
|
List<int> scIds = new List<int>();
|
|
|
|
for (int i = 0; i < allTelemetries.Count; i++)
|
|
{
|
|
TelemetryObj telem = allTelemetries[i];
|
|
|
|
// verify if the current telemetry object is for current pin
|
|
if (!scIds.Contains(telem.Sc_Id) && telem.DigitalNr == d)
|
|
{
|
|
// save an instance for current telemetry state
|
|
telObj = telem;
|
|
// add current sc id in the list
|
|
scIds.Add(telem.Sc_Id);
|
|
}
|
|
}
|
|
|
|
string scIdIN = "(";
|
|
for (int i = 0; i < scIds.Count; i++)
|
|
{
|
|
scIdIN += "'" + scIds[i] + "'" + (i == scIds.Count - 1 ? ")" : ",");
|
|
}
|
|
|
|
sb.Append("UPDATE \"telemetry\" SET " +
|
|
"digital_nr=" + telObj.DigitalNr.ToString() +
|
|
",digital_type='" + telObj.DigitalType +
|
|
"',transition='" + telObj.Transition +
|
|
"',\"name\"='" + telObj.Name + "', " +
|
|
"alarm=" + (telObj.Alarm ? 1 : 0) +
|
|
" WHERE sc_id IN " + scIdIN + " AND digital_nr = '" + telObj.DigitalNr + "';");
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sb.ToString(), connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
//telemetry byte position methods
|
|
public sqlResponse AddTelemetryPOS(TelemetryPOS telPOS)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
try
|
|
{
|
|
int sc_id = getSCID(telPOS.RadioID);
|
|
if (sc_id == -1)
|
|
return resp;
|
|
|
|
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "insert into \"telemetry_pos\" (sc_id, timegmt, \"value\")" +
|
|
$" VALUES({sc_id},{DateTime.Now.ToUniversalTime().DateTo70Format()},{telPOS.ByteValue})";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
resp = sqlResponse.done;
|
|
}
|
|
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source,ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public TelemetryPOS GetTelemetryPOS(string radioID)
|
|
{
|
|
TelemetryPOS resp = null;
|
|
|
|
try
|
|
{
|
|
|
|
int sc_id = getSCID(radioID);
|
|
if (sc_id != -1)
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT id, sc_id, timegmt,\"value\" FROM \"telemetry_pos\" WHERE sc_id={sc_id}";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
resp = new TelemetryPOS(Reader.GetInt32(0), radioID, Reader.GetInt32(2), Reader.GetInt32(3));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message, ConsoleColor.Red);
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public List<TelemetryPOS> GetTelemetryPOSAll()
|
|
{
|
|
List<TelemetryPOS> resp = new List<TelemetryPOS>();
|
|
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "SELECT id, sc_id, timegmt,\"value\" FROM \"telemetry_pos\"";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
resp.Add(new TelemetryPOS(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetInt32(2), Reader.GetInt32(3)));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message, ConsoleColor.Red);
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public sqlResponse UpdateTelemetryPOS(TelemetryPOS telPOS)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
try
|
|
{
|
|
|
|
int sc_id = getSCID(telPOS.RadioID);
|
|
if (sc_id == -1)
|
|
return resp;
|
|
|
|
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT COUNT(id) FROM \"telemetry_pos\" WHERE sc_id={sc_id}";
|
|
|
|
object result = null;
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
result = cmd.ExecuteScalar();
|
|
}
|
|
|
|
|
|
if (result != null && result.ToString() == "1")
|
|
{
|
|
command = "UPDATE \"telemetry_pos\" SET " +
|
|
"timegmt=" + DateTime.Now.ToUniversalTime().DateTo70Format() +
|
|
",\"value\"=" + telPOS.ByteValue +
|
|
" WHERE sc_id=" + sc_id.ToString();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
result = cmd.ExecuteScalar();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
else
|
|
{
|
|
Console.WriteLine("TelemetryPOS for " + telPOS.RadioID + "- not found");
|
|
AddTelemetryPOS(telPOS);
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
//telemetry history methods
|
|
public sqlResponse AddTelemetryHist(string radioID,int tel_id, int alarm)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
try
|
|
{
|
|
|
|
int sc_id = getSCID(radioID);
|
|
if (sc_id == -1)
|
|
return resp;
|
|
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "insert into \"telemetry_history\" (sc_id, timegmt, telemetry_id, alarm)" +
|
|
$" VALUES({sc_id},{DateTime.Now.ToUniversalTime().DateTo70Format()},{tel_id},{alarm})";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
resp = sqlResponse.done;
|
|
}
|
|
catch (Exception ee)
|
|
{
|
|
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|
resp = sqlResponse.SQLerror;
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
private int getSCID(string radioID)
|
|
{
|
|
int sc_id = -1;
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT sc_id FROM subscriber where imei='" + radioID + "'", connection))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
sc_id = Reader.GetInt32(0);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|
}
|
|
|
|
return sc_id;
|
|
}
|
|
}
|
|
|
|
public class TelemetryObj
|
|
{
|
|
private int id;
|
|
public int Id
|
|
{
|
|
get { return id; }
|
|
set { id = value; }
|
|
}
|
|
|
|
private int digitalNr;
|
|
public int DigitalNr
|
|
{
|
|
get { return digitalNr; }
|
|
set { digitalNr = value; }
|
|
}
|
|
|
|
private string digitalType;
|
|
public string DigitalType
|
|
{
|
|
get { return digitalType; }
|
|
set { digitalType = value; }
|
|
}
|
|
|
|
private string transition;
|
|
public string Transition
|
|
{
|
|
get { return transition; }
|
|
set { transition = value; }
|
|
}
|
|
|
|
private string name;
|
|
public string Name
|
|
{
|
|
get { return name; }
|
|
set { name = value; }
|
|
}
|
|
|
|
private bool alarm;
|
|
public bool Alarm
|
|
{
|
|
get { return alarm; }
|
|
set { alarm = value; }
|
|
}
|
|
|
|
private int sc_id;
|
|
public int Sc_Id
|
|
{
|
|
get { return sc_id; }
|
|
set { sc_id = value; }
|
|
}
|
|
|
|
|
|
public TelemetryObj()
|
|
{
|
|
|
|
}
|
|
|
|
public TelemetryObj(int id, int digitalNr, string digitalType, string transition, string name, bool alarm)
|
|
{
|
|
this.id = id;
|
|
this.digitalNr = digitalNr;
|
|
this.digitalType = digitalType;
|
|
this.transition = transition;
|
|
this.name = name;
|
|
this.alarm = alarm;
|
|
this.sc_id = 0;
|
|
}
|
|
|
|
public TelemetryObj(int id, int digitalNr, string digitalType, string transition, string name, bool alarm,int sc_id)
|
|
{
|
|
this.id = id;
|
|
this.digitalNr = digitalNr;
|
|
this.digitalType = digitalType;
|
|
this.transition = transition;
|
|
this.name = name;
|
|
this.alarm = alarm;
|
|
this.sc_id = sc_id;
|
|
}
|
|
}
|
|
|
|
public class TelemetryPOS
|
|
{
|
|
private int id;
|
|
|
|
public int Id
|
|
{
|
|
get { return id; }
|
|
set { id = value; }
|
|
}
|
|
private string radioID;
|
|
|
|
public string RadioID
|
|
{
|
|
get { return radioID; }
|
|
set { radioID = value; }
|
|
}
|
|
private int sc_id;
|
|
|
|
public int Sc_id
|
|
{
|
|
get { return sc_id; }
|
|
set { sc_id = value; }
|
|
}
|
|
private int timeGMT;
|
|
|
|
public int TimeGMT
|
|
{
|
|
get { return timeGMT; }
|
|
set { timeGMT = value; }
|
|
}
|
|
private int byteValue;
|
|
|
|
public int ByteValue
|
|
{
|
|
get { return byteValue; }
|
|
set { byteValue = value; }
|
|
}
|
|
|
|
public TelemetryPOS(int id, string radioID, int timeGMT, int byteValue)
|
|
{
|
|
this.id = id;
|
|
this.radioID = radioID;
|
|
this.sc_id = 0;
|
|
this.timeGMT = timeGMT;
|
|
this.byteValue = byteValue;
|
|
}
|
|
|
|
public TelemetryPOS(int id, int sc_id, int timeGMT, int byteValue)
|
|
{
|
|
this.id = id;
|
|
this.radioID = "";
|
|
this.sc_id = sc_id;
|
|
this.timeGMT = timeGMT;
|
|
this.byteValue = byteValue;
|
|
}
|
|
}
|
|
}
|