SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBtelemetryManager.cs

703 lines
23 KiB
C#
Raw Permalink Normal View History

2024-02-22 16:43:59 +00:00
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))
{
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
2024-02-22 16:43:59 +00:00
}
}
}
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;
}
}
}