SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBtelemetryManager.cs

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());
                    }
                }
            }
            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;
        }
    }
}