SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBalarmManager.cs

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Collections;
using System.Data;

namespace SafeMobileLib
{
    public class DBalarmManager:DBmanager
    {
        public DBalarmManager(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 Alarm getAllAlarms(string radioID)
        {
            Alarm alarmRet= null;
            NpgsqlCommand cmd;
            int sc_id = 0;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    //get sc_id from imei/radioID
                    string command = "SELECT sc_id from subscriber where imei='" + radioID.ToString() + "'";
                    using (cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    command = " SELECT  id, emergency, landmark, \"zone\" ,loneworker,speed,email,empopup,emsound,geopopup,geosound,speedpopup,speedsound,telempopup,telemsound FROM subscriber_alarm " +
                              " WHERE sc_id = " + sc_id.ToString();
                    using (cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                alarmRet = new Alarm(Reader.GetInt32(0), sc_id, Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetString(4), Reader.GetString(5), Reader.GetString(6), Reader.GetBoolean(7), Reader.GetBoolean(8), Reader.GetBoolean(9), Reader.GetBoolean(10), Reader.GetBoolean(11), Reader.GetBoolean(12), Reader.GetBoolean(13), Reader.GetBoolean(14));
                            }
                        }
                    }
                }

            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.ToString());

            }
           
            return alarmRet;
        }

        public Hashtable getImeiLastStatus()
        {
           
            Hashtable ret = new Hashtable();
            
            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT imei,status FROM lastpos", connection))
                    {

                        using (NpgsqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                
                               string imei = (reader["imei"] != DBNull.Value) ? reader["imei"].ToString() : "";
                               if (imei != "" && ret.ContainsKey(imei))
                                   ret.Add(imei, reader.GetInt32(1));
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                
                Console.WriteLine(ee.ToString());
            }
            

            return ret;
        }

        public Alarm getAllAlarms_Sc_id(Int32 sc_ID)
        {

            int sc_id = sc_ID;
            Alarm alarmRet = null;
            
            string command = $"SELECT  id, emergency, landmark, \"zone\" ,loneworker,speed,email,empopup,emsound,geopopup,geosound,speedpopup,speedsound,telempopup,telemsound FROM subscriber_alarm WHERE sc_id = {sc_id} ";

            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())
                            {
                                alarmRet = new Alarm(Reader.GetInt32(0), sc_id, Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetString(4), Reader.GetString(5), Reader.GetString(6), Reader.GetBoolean(7), Reader.GetBoolean(8), Reader.GetBoolean(9), Reader.GetBoolean(10), Reader.GetBoolean(11), Reader.GetBoolean(12), Reader.GetBoolean(13), Reader.GetBoolean(14));
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                SM.Debug(command);
                Console.WriteLine(ee.ToString());
            }
            
            return alarmRet;
        } 

        public Dictionary<String, Alarm> getAllAlarms()
        {
            Dictionary<String, Alarm> allAlarms = new Dictionary<string, Alarm>();

            string command = "SELECT  id, emergency, landmark, \"zone\" ,loneworker,speed,email,empopup,emsound,geopopup,geosound,speedpopup,speedsound,telempopup,telemsound, sc_id FROM subscriber_alarm ";

            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())
                            {
                                Alarm alarmRet = new Alarm(Reader.GetInt32(0), Reader.GetInt32(15) + "", Reader.GetString(1), Reader.GetString(2), Reader.GetString(3),
                                    Reader.GetString(4), Reader.GetString(5), Reader.GetString(6), Reader.GetBoolean(7), Reader.GetBoolean(8), Reader.GetBoolean(9),
                                    Reader.GetBoolean(10), Reader.GetBoolean(11), Reader.GetBoolean(12), Reader.GetBoolean(13), Reader.GetBoolean(14));

                                if (!allAlarms.ContainsKey(Reader.GetInt32(15) + ""))
                                    allAlarms.Add(Reader.GetInt32(15) + "", alarmRet);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Utils.WriteLine("getAllAlarms " + ex.ToString(), ConsoleColor.Red);
            }
            

            return allAlarms;
        }



        public sqlResponse addAlarm(Alarm alm, string radioID)
        {
            
            sqlResponse resp;
            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}' LIMIT 1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }


                    command = "insert into \"subscriber_alarm\" " +
                              "(sc_id , emergency ,landmark ,\"zone\",loneworker ,speed ,email,emsound,empopup,geosound,geopopup,speedsound,speedpopup,telemsound,telempopup)" +
                              $" VALUES( {sc_id},'{alm.Emergency}','{alm.Landmark}','{alm.Zone}','{alm.Loneworker}','{alm.Speed}','{alm.Email}',{alm.Emsound},{alm.Empopup},{alm.Geosound},{alm.Geopopup},{alm.Speedsound},{alm.Speedpopup},{alm.Telemsound},{alm.Telempopup})";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
                resp = sqlResponse.SQLerror;
            }
            

            return resp;
        }

        public sqlResponse Insert_speed_alarm_sim(UInt32 time_start, string radioID, ArrayList speed_step)
        {
            
            sqlResponse resp;
             
            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}' LIMIT 1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    int i = 0;
                    while (i < speed_step.Count)
                    {
                        command = "INSERT INTO speedalarm (sc_id, timegmt ,speed ,preview ,address) " +
                                  $" VALUES({sc_id},{(time_start + ((Int32)speed_step[i]) * 60)}, {(Int32)speed_step[i + 1]}, 0, 'no address')";
                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                        i += 2;
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
                resp = sqlResponse.SQLerror;
            }
            
            return resp;
        }

        public sqlResponse Insert_landmark_alarm_sim(UInt32 time_start, string radioID, ArrayList land_step)
        {
           
            sqlResponse resp;
             
            int sc_id = 0;
            Int32 idxL1=0, idxL2=0, idxGeo = 0;
            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    string command = $"SELECT sc_id FROM subscriber WHERE imei='{radioID}' LIMIT 1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                        
                    }

                    command = "SELECT idx FROM place WHERE name='Landmark1'";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        idxL1 = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    command = "SELECT idx FROM place WHERE name='Landmark2'";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        idxL2 = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    command = "SELECT idx FROM zonename WHERE name='testZone1'";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {

                        idxGeo = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    // get ID for landmark1,2 and zone1

                    int i = 0;
                    while (i < land_step.Count)
                    {
                        Int32 zoneId = idxL2;
                        if (((Int32)land_step[i + 1]) == 2) zoneId = idxL1;
                        else if (((Int32)land_step[i + 1]) == 3) zoneId = idxGeo;
                        command = "INSERT INTO geozoneinout (sc_id, timegmt, zone_id, action, preview, type) " +
                                  $" VALUES({sc_id},{(time_start + ((Int32)land_step[i]) * 60)}, {zoneId}, {(Int32)land_step[i + 2]}, 0, { (Int32)land_step[i + 3]} )";

                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }

                        i += 4;
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
                resp = sqlResponse.SQLerror;
            }
            

            return resp;
        }


        public sqlResponse insertAlarm4ScIds(Alarm alarm, List<int> scIds, List<AdminAlerts> toUpdateAlerts)
        {
            sqlResponse resp;
            NpgsqlTransaction tr = null;
            StringBuilder sb = new StringBuilder(1024);
            bool has_emergency = alarm.Emergency == "" ? false : true;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    string scIdIN = "(";

                    for (int i = 0; i < scIds.Count; i++)
                        scIdIN += "'" + scIds[i] + "'" + (i == scIds.Count - 1 ? ");" : ",");

                    if (toUpdateAlerts.Contains(AdminAlerts.EMERGENCY))
                    {

                        string cmdLINX = $"update subscriber_mobile set has_emergency = {has_emergency} where sc_id in ";

                        cmdLINX += scIdIN;

                        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdLINX, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }

                    }

                    sb.Clear();

                    tr = connection.BeginTransaction();

                    foreach (int scId in scIds)
                    {
                        sb.Append("UPDATE \"subscriber_alarm\" SET "
                                + (toUpdateAlerts.Contains(AdminAlerts.EMERGENCY) ? " emergency = '" + alarm.Emergency + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.LANDMARK) ? " landmark = '" + alarm.Landmark + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.GEOFENCE) ? " \"zone\" = '" + alarm.Zone + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.LONE_WORKER) ? " loneworker = '" + alarm.Loneworker + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.SPEED) ? " speed = '" + alarm.Speed + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.ALARM_ON_EMAIL) ? " email = '" + alarm.Email + "', " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.EMERGENCY_SOUND) ? " emsound = " + alarm.Emsound + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.EMERGENCY_POPUP) ? " empopup = " + alarm.Empopup + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.GEOFENCE_SOUND) ? " geosound = " + alarm.Geosound + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.GEOFENCE_POPUP) ? " geopopup = " + alarm.Geopopup + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.SPEED_SOUND) ? " speedsound = " + alarm.Speedsound + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.SPEED_POPUP) ? " speedpopup = " + alarm.Speedpopup + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.TELEMETRY_SOUND) ? " telemsound = " + alarm.Telemsound + ", " : "")
                                + (toUpdateAlerts.Contains(AdminAlerts.TELEMETRY_POPUP) ? " telempopup = " + alarm.Telempopup + ", " : "")
                                + " sc_id = " + scId
                                + " WHERE sc_id=" + scId + ";"

                                + "INSERT INTO \"subscriber_alarm\" "
                                + "(sc_id" + ", emergency"
                                + ", landmark"
                                + ", \"zone\""
                                + ", loneworker"
                                + ", speed"
                                + ", email"
                                + ", emsound"
                                + ", empopup"
                                + ", geosound"
                                + ", geopopup"
                                + ", speedsound"
                                + ", speedpopup"
                                + ", telemsound"
                                + ", telempopup"
                                + ") "
                                + " SELECT " + scId
                                + ",'" + (alarm.Emergency.Length > 0 ? alarm.Emergency : String.Empty) + "'"
                                + ",'" + alarm.Landmark + "'"
                                + ",'" + alarm.Zone + "'"
                                + ",'" + alarm.Loneworker + "'"
                                + ",'" + (alarm.Speed.Length > 0 ? alarm.Speed : String.Empty) + "'"
                                + ",'" + alarm.Email + "'"
                                + "," + alarm.Emsound
                                + "," + alarm.Empopup
                                + "," + alarm.Geosound
                                + "," + alarm.Geopopup
                                + "," + alarm.Speedsound
                                + "," + alarm.Speedpopup
                                + "," + alarm.Telemsound
                                + "," + alarm.Telempopup
                                + " WHERE NOT EXISTS (SELECT 1 FROM \"subscriber_alarm\" WHERE sc_id = " + scId + ");"
                            );

                        if (sb.Length > 5000)
                        {
                            using (NpgsqlCommand cmd = new NpgsqlCommand(sb.ToString(), connection, tr))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            sb.Clear();
                        }
                    }


                    if (sb.Length > 0)
                    {
                        using (NpgsqlCommand cmd = new NpgsqlCommand(sb.ToString(), connection, tr))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tr.Commit();

                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                tr?.Rollback();
                Utils.WriteLine("insertAlarm4ScIds: " + ee.ToString(), ConsoleColor.Red);
                resp = sqlResponse.SQLerror;
            }
            
            return resp;
        }


        public sqlResponse deleteAlarm(string radioID)
        {
          
            sqlResponse resp;
             
            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}' LIMIT 1";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    command = $"DELETE FROM \"subscriber_alarm\" WHERE sc_id = {sc_id} ";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
                resp = sqlResponse.SQLerror;
            }
            
            return resp;
        }


        public sqlResponse Insert_Emerg_Alarm(string scID, int emergencyType, int speed, double lat, double lng)
        {
            sqlResponse resp;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    //get sc_id from imei/radioID
                    string emergTime = DateTime.Now.ToUniversalTime().DateTo70Format() + "";
                    string emtypedesc = (emergencyType > -1) ? emergencyType.ToString() : "null";
                    string query = $"INSERT INTO emergalarm (sc_id,timeGMT,preview, emergency_type, lat, lng) VALUES({scID},{emergTime},0,{emergencyType},{lat},{lng})";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    query = String.Format("UPDATE alarm set sc_id={0},timeGMT={1} where type=5", scID, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Utils.WriteLine("Insert_Emerg_Alarm5 " + o.ToString(), ConsoleColor.Red);
                resp = sqlResponse.SQLerror;
                throw new ArgumentException(o.Message.ToString());
            }
            
            return resp;
        }

        public sqlResponse Insert_Emerg_Alarm(string radioID, int? emergencyType)
        {
           
            sqlResponse resp;
            int p_sc_id =0;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    //get sc_id from imei/radioID
                    string command = $"SELECT sc_id FROM subscriber WHERE imei='{radioID}' LIMIT 1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        p_sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    string emergTime = DateTime.Now.ToUniversalTime().DateTo70Format() + "";
                    string emtypedesc = (emergencyType.HasValue) ? emergencyType.ToString() : "null";
                    command = "INSERT INTO emergalarm (sc_id,timeGMT,preview, emergency_type) VALUES(" + p_sc_id + "," + emergTime + ",0," + emtypedesc + ")";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    command = String.Format("UPDATE alarm set sc_id={0},timeGMT={1} where type=5", p_sc_id, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
                resp = sqlResponse.SQLerror;
                throw new ArgumentException(o.Message.ToString());

            }
           
            return resp;
        }




        public sqlResponse Insert_Speed_Alarm(string radioID, UInt32 p_timeGMT, Int32 p_speed, String lat, String lng)
        {

            sqlResponse resp = sqlResponse.SQLerror;
            try
            {
                using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
                {
                    conn.Open();
                     
                    int p_sc_id = 0;
                    try
                    {
                        //get sc_id from imei/radioID
                        string command = $"SELECT sc_id FROM subscriber WHERE imei='{radioID}'";
                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                        {
                            p_sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        command = "INSERT INTO speedalarm (sc_id,timeGMT,speed,preview,address,lat,lng) " + 
                                  $" VALUES({p_sc_id}, {p_timeGMT}, {p_speed}, 0, 'no address', {lat}, {lng})";

                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }

                        command = $"UPDATE alarm SET sc_id={p_sc_id}, timeGMT={p_speed} WHERE type=4";
                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }

                        resp = sqlResponse.done;
                    }
                    catch (Exception o)
                    {
                        Utils.WriteLine(o.ToString(), ConsoleColor.Red);
                    }
                    
                }
            }
            catch (Exception ex)
            {
                Utils.WriteLine("Insert_Speed_Alarm " + ex.ToString(), ConsoleColor.Red);
            }
            return resp;
        }

        public sqlResponse Insert_Zone_Alarm(int radioID, UInt32 p_timeGMT, Int32 p_zone_id, Int32 p_action, Boolean Zone)
        {
            sqlResponse resp = sqlResponse.SQLerror;
            try
            {
                using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
                {
                    conn.Open();
                   
                    int p_sc_id = radioID;

                    string query = "";
                    

                    if (Zone) query = "INSERT INTO geozoneinout (sc_id,timeGMT,zone_id,action,type) VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",1)";
                    else query = "INSERT INTO geozoneinout (sc_id,timeGMT,zone_id,action,type) VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",2)";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    if (Zone) query = String.Format("UPDATE alarm set sc_id={0},timeGMT={1},mess='{2}' where type=2", p_sc_id, p_zone_id, p_action);
                    else query = String.Format("UPDATE alarm set sc_id={0},timeGMT={1},mess='{2}' where type=3", p_sc_id, p_zone_id, p_action);

                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    resp = sqlResponse.done;
                    
                }
            }
            catch (Exception ex)
            {
                Utils.WriteLine("Insert_Zone_Alarm " + ex.ToString(), ConsoleColor.Red);
            }
            return resp;
        }

        public sqlResponse Insert_Zone_Simulator(String name, Int32 color, ArrayList List_of_points)
        {
            
            sqlResponse resp = sqlResponse.SQLerror;

            string query = "";
            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    query = $"INSERT INTO zonename (name,color) VALUES('{name}',{color})";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    String idx = string.Empty;
                    query = "SELECT MAX(idx) FROM zonename";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        idx = ((Int32)cmd.ExecuteScalar()).ToString();
                    }


                    query = $"INSERT INTO zonepoints (lat,lng,zone_id) VALUES(@lat1,@lng1,{idx})";
                    foreach (PointonZone obj in List_of_points)
                    {
                        using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                        {
                            cmd.Parameters.Add("@lat1", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.lat;
                            cmd.Parameters.Add("@lng1", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.lng;

                            cmd.Prepare();
                            cmd.ExecuteNonQuery();
                        }
                    }

                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("Insert_Zone_Alarm : " + o.Message.ToString(),ConsoleColor.Red);
                throw new ArgumentException(o.Message.ToString());
            }
            
            return resp;
        }

        public sqlResponse Insert_History_Simulator(String imei, ArrayList List_of_points)
        {

            Double sc_ev = 0;
            Int32  sc_id = 0;
            sqlResponse resp = sqlResponse.SQLerror;

            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();


                    string command = $"SELECT sc_id FROM subscriber where imei='{imei}' LIMIT 1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        sc_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }


                    command = $"INSERT INTO messages (sc_id,lat,lng,speed,di,dox,timeGMT,scevtime) " +
                                       $" VALUES ({sc_id}, @lat1, @lng1, @speed,0,0, @timegmt @timegmt,@sc_ev)";
                    foreach (imei_and_message obj in List_of_points)
                    {
                        sc_ev = sc_id * 10000000000 + Convert.ToInt64(obj.TimeGMT);

                        using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                        {
                            cmd.Parameters.Add("@sc_ev", NpgsqlTypes.NpgsqlDbType.Double).Value = sc_ev;
                            cmd.Parameters.Add("@speed", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.Speed;
                            cmd.Parameters.Add("@timegmt", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.TimeGMT;

                            cmd.Parameters.Add("@lng1", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.lng;
                            cmd.Parameters.Add("@lat1", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.lat;

                            cmd.Prepare();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
                throw new ArgumentException(o.Message.ToString());
            }
            

            return resp;
        }

        public sqlResponse Insert_Landmark_Simulator(String name, String descris, Double lat, Double lng, Int32 typeID)
        {
         
            sqlResponse resp = sqlResponse.SQLerror;
            NpgsqlCommand cmd;
            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    cmd = new NpgsqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = $"INSERT INTO place (name,note,lat,lng,type_id,address) VALUES('{name}','{descris}',@lat,@lng, {typeID},'{""}')";
                    cmd.Parameters.Add("@lat", NpgsqlTypes.NpgsqlDbType.Double).Value = lat;
                    cmd.Parameters.Add("@lng", NpgsqlTypes.NpgsqlDbType.Double).Value = lng;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine($"Insert_Landmark_Simulator : {o.Message}", ConsoleColor.Red);
                throw new ArgumentException(o.Message.ToString());
            }
            
            return resp;
        }

        //get all zones
        public ArrayList get_all_zone()
        {
            ArrayList GroupList = new ArrayList();
            ArrayList arr = new ArrayList();
            int OldGrpId = 0;
            Boolean first = true;
            ZoneClass oldZone = null;
            

            string command = "SELECT zn.idx,zn.name,znpnt.lat,znpnt.lng,znpnt.idx,zn.color,zn.useridx,zn.alarmtype,zn.sentmsg,zn.msgbody,zn.unitids,zn.sentemail,zn.email,zn.subj,zn.body,zn.imeilist,zn.sentmsg2,zn.msgbody2,zn.speed, zn.callout, zn.callout_severity"
                                + " FROM zonename as zn"
                                + " LEFT JOIN zonepoints as znpnt ON (zn.idx = znpnt.zone_id) "            
                                + " ORDER by zn.name, znpnt.idx";
            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {

                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                if (OldGrpId != dr.GetInt32(0))
                                {
                                    if (first) first = false;
                                    else
                                    {
                                        ZoneClass g = new ZoneClass(oldZone, OldGrpId, arr);
                                        GroupList.Add(g);
                                    }

                                    OldGrpId = dr.GetInt32(0);
                                    oldZone = new ZoneClass(dr.GetString(1),
                                                        dr.GetInt32(5),     /* name */
                                                        dr.GetInt32(6),     /* color */
                                                        dr.GetInt32(7),     /* useridx*/
                                                        dr.GetBoolean(8),   /* alarmType */
                                                        dr.GetString(9),
                                                        dr.GetString(10),
                                                        dr.GetBoolean(11),
                                                        dr.GetString(12),
                                                        dr.GetString(13),
                                                        dr.GetString(14),
                                                        dr.GetString(15),
                                                        dr.GetBoolean(16),
                                                        dr.GetString(17),
                                                        dr.GetInt32(18),
                                                        dr.GetBoolean(19),
                                                        dr.GetInt16(20));
                                    arr = new ArrayList();
                                }
                                if (!dr.IsDBNull(2))
                                    arr.Add(new PointonZone(dr.GetDouble(2), dr.GetDouble(3), dr.GetInt32(4)));
                            }
                            dr.Close();
                        }
                    }
                    if (OldGrpId != 0)
                    {
                        ZoneClass g1 = new ZoneClass(oldZone, OldGrpId, arr);
                        GroupList.Add(g1);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"get_all_zone : {ex.Message}", ConsoleColor.Red);
            }
            

            return GroupList;
        }

          //get all landmarks
        public ArrayList get_all_landmarks()
        {
            
            ArrayList ret = new ArrayList();

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    string command = "SELECT name,lat,lng,idx, useridx, callout, callout_severity FROM place ORDER BY name";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                LandMark tmp = new LandMark(dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2),
                                                            dr.GetInt32(3), dr.GetInt32(4), dr.GetBoolean(5), dr.GetInt16(6));
                                ret.Add(tmp);
                            }
                            dr.Close();
                        }
                    }
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("get_all_landmarks: " + o.Message, ConsoleColor.Red);
                throw new ArgumentException(o.Message);
            }
            

            return ret;
        }

       
        //check if a unit is in alarm state
        public bool hasEmergencyActivated(int sc_id)
        {
            string result = "";

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    string command = $"SELECT COUNT(sc_id) FROM emergalarm WHERE preview = 0 and sc_id = {sc_id} ";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        result = (cmd.ExecuteScalar()).ToString();
                    }
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("File: SMdb_access.cs Meth: hasEmergencyActivated " + o.Message.ToString());
                throw new ArgumentException(o.Message.ToString());
            }
           

            return (result != "0");
        }


        //for zones
        public Hashtable get_Vehicle_and_zone()
        {
            
            Hashtable ret = new Hashtable();
       
           
            string command = "SELECT l.imei, l.lat, l.lng, s.sc_id,v.name,a.id, a.emergency, a.landmark, " +
                                    "a.\"zone\" ,a.loneworker,a.speed,a.email, si.sip_id, v.type as type, active" +   
                            " FROM lastpos l " +
                                "INNER JOIN subscriber as s ON (l.imei = s.imei) " +
                                "INNER JOIN subscriber_history as h ON (h.sc_id = s.sc_id) " +
                                "INNER JOIN vehicle as v on (v.id = h.veh_id) " +
                                "LEFT JOIN subscriber_alarm as a on (s.sc_id = a.sc_id) " +
                                "INNER JOIN sip_manager as si on (si.id = s.sc_id AND si.type = 2) ORDER BY l.imei";

            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {

                                Alarm alm = new Alarm()
                                {
                                    

                                    Id          = dr["id"] != DBNull.Value ? int.Parse(dr["id"].ToString()) : 0,
                                    Sc_id       = dr["sc_id"] != DBNull.Value ? int.Parse(dr["sc_id"].ToString()) : 0,
                                    Emergency   = dr["emergency"] != DBNull.Value ? dr["emergency"].ToString() : "",
                                    Landmark    = dr["landmark"] != DBNull.Value ? dr["landmark"].ToString() : "",
                                    Zone        = dr["zone"] != DBNull.Value ? dr["zone"].ToString() : "",
                                    Loneworker  = dr["loneworker"] != DBNull.Value ? dr["loneworker"].ToString() : "",
                                    Speed       = dr["speed"] != DBNull.Value ? dr["speed"].ToString() : "",
                                    Email       = dr["email"] != DBNull.Value ? dr["email"].ToString() : "",
                                    Empopup     = true,
                                    Emsound     = true,
                                    Geopopup    = true,
                                    Geosound    = true,
                                    Speedpopup  = true,
                                    Speedsound  = true,
                                    Telempopup  = true,
                                    Telemsound  = true
                                };

                                int sip_id          = dr["sip_id"] != DBNull.Value ? int.Parse(dr["sip_id"].ToString()) : 0;
                                string imei         = dr["imei"] != DBNull.Value ? dr["imei"].ToString() : "";
                                double latitude     = dr["lat"] != DBNull.Value ? double.Parse(dr["lat"].ToString()) : 0.0;
                                double longitude    = dr["lng"] != DBNull.Value ? double.Parse(dr["lng"].ToString()) : 0.0;
                                string name         = dr["name"] != DBNull.Value ? dr["name"].ToString() : "";


                                Vehicle_Data obj = new Vehicle_Data( alm.Sc_id, sip_id, imei, latitude, longitude, name, alm)
                                {
                                    active = (!dr.IsDBNull(dr.GetOrdinal("active")) ? dr.GetBoolean(dr.GetOrdinal("active")) : false)
                                };

                                obj.type = dr.GetInt16(dr.GetOrdinal("type"));


                                if (!ret.ContainsKey(obj.imei))
                                    ret.Add(obj.imei, obj);
                            }
                            dr.Close();
                        }
                    }
                }
            }
            catch (Exception o)
            {
                Console.WriteLine(o.Message, ConsoleColor.Red);
                throw new ArgumentException(o.Message.ToString());
            }
            
            return ret;
        }
        
        //alarms ACK
        public sqlResponse ACKalarms(int id, AlarmTypes type, int? user_id, uint? timegmt_ack)
        {
            string query = string.Empty;
            sqlResponse resp = sqlResponse.SQLerror;

            try
            { 
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();
                    
                    switch (type)
                    {
                        case AlarmTypes.emergency:

                            query = $"UPDATE emergalarm SET preview=1, user_id={user_id}, timegmt_ack={timegmt_ack} WHERE idx={id}";
                            using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            resp = sqlResponse.done;
                            break;
                        case AlarmTypes.landmark:
                        case AlarmTypes.zone:

                            query = $"UPDATE geozoneinout set preview=1, user_id={user_id}, timegmt_ack={timegmt_ack} where idx={id}";
                            using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            resp = sqlResponse.done;

                            break;
                        case AlarmTypes.speed:

                            query = $"UPDATE speedalarm set preview=1, user_id={user_id}, timegmt_ack={timegmt_ack} where idx={id}";
                            using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            resp = sqlResponse.done;

                            break;
                        case AlarmTypes.telemetry:

                            query = $"UPDATE telemetry_history set ack=1, user_id={user_id}, timegmt_ack={timegmt_ack} where idx={id}";
                            using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            resp = sqlResponse.done;

                            break;
                    }
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
                resp = sqlResponse.SQLerror;
                throw new ArgumentException(o.Message.ToString());

            }
            
            return resp;
        }

        public Dictionary<string, string> InitAlertCommandDictionary()
        {
            Dictionary<string, string> alertCommand = new Dictionary<string, string>();
            alertCommand.Add(AlarmTypes.emergency.ToString(), "UPDATE emergalarm set preview=1, user_id={1} , timegmt_ack={2} where idx={0};");
            alertCommand.Add(AlarmTypes.zone.ToString(), "UPDATE geozoneinout set preview=1, user_id={1} , timegmt_ack={2} where idx={0};");
            alertCommand.Add(AlarmTypes.landmark.ToString(), "UPDATE geozoneinout set preview=1, user_id={1} , timegmt_ack={2} where idx={0};");
            alertCommand.Add(AlarmTypes.speed.ToString(), "UPDATE speedalarm set preview=1, user_id={1} , timegmt_ack={2} where idx={0};");
            alertCommand.Add(AlarmTypes.telemetry.ToString(), "UPDATE telemetry_history set ack=1, user_id={1} , timegmt_ack={2} where id={0};");
            return alertCommand;
        }
        public sqlResponse ACKalarms(string command)
        {
            
            sqlResponse resp = sqlResponse.SQLerror;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine("File: SMdb_access.cs Meth: ACKalarms bulk ack by dispatcher " + o.Message.ToString());
                resp = sqlResponse.SQLerror;
                throw new ArgumentException(o.Message.ToString());

            }
            

            return resp;
        }

        //alarms ACK
        public sqlResponse ACKALLalarms()
        {
           
            sqlResponse resp = sqlResponse.SQLerror;

            try
            {
                //get sc_id from imei/radioID

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    String query = String.Format("UPDATE emergalarm set preview=1 WHERE preview = 0;"
                                            + " UPDATE telemetry_history set ack=1 WHERE ack = 0; "
                                            + " UPDATE geozoneinout set preview=1 WHERE preview = 0; "
                                            + " UPDATE speedalarm set preview=1 WHERE preview = 0; "
                                            + " UPDATE telemetry_history set ack=1 WHERE ack = 0; ");
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.CommandTimeout = Int16.MaxValue;
                        cmd.ExecuteNonQuery();
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine($"ACKALLalarms : {o.Message} ",ConsoleColor.Red);
                resp = sqlResponse.SQLerror;
                throw new ArgumentException(o.Message.ToString());
            }
            

            return resp;
        }

        //alarms ACK
        public sqlResponse ACKALLalarms(int sc_id, int user_id)
        {
            
            sqlResponse resp = sqlResponse.SQLerror;

            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    String query = String.Format("UPDATE emergalarm set preview=1, user_id={1}, timegmt_ack={2} WHERE sc_id ={0}", sc_id, user_id, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    System.Threading.Thread.Sleep(50);

                    query = String.Format("UPDATE geozoneinout set preview=1, user_id={1}, timegmt_ack={2} WHERE sc_id ={0}", sc_id, user_id, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    System.Threading.Thread.Sleep(50);

                    query = String.Format("UPDATE speedalarm set preview=1, user_id={1}, timegmt_ack={2} WHERE sc_id ={0}", sc_id, user_id, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    System.Threading.Thread.Sleep(50);

                    query = String.Format("UPDATE telemetry_history set ack=1, user_id={1}, timegmt_ack={2} WHERE sc_id ={0}", sc_id, user_id, DateTime.Now.ToUniversalTime().DateTo70Format());
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    resp = sqlResponse.done;
                }
            }
            catch (Exception o)
            {
                Console.WriteLine($"ACKALLalarms : {o.Message}", ConsoleColor.Red);
                resp = sqlResponse.SQLerror;
            }
            

            return resp;
        }


        public static readonly object locker = new object();

        public Int32 countAlarm = 0;
        //get all non ack alarms
        public List<AlarmHisItem> Get_nonACK_alarms1()
        {
            return Get_nonACK_alarms1(99999999);
        }

        public List<AlarmHisItem> Get_nonACK_alarms1(int limit)
        {
            Utils.WriteLine("START Get_nonACK_alarms", ConsoleColor.Yellow);
            List<AlarmHisItem> list = new List<AlarmHisItem>();

            using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
            {

                //get all energ alarm
                try
                {
                    conn.Open();
                    string command = "SELECT  a.idx, a.sc_id,a.timegmt,v.\"name\", a.emergency_type, lat, lng " +
                                     " FROM emergalarm as a" +
                                     " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                     " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                     " WHERE preview = 0 " +
                                     " ORDER BY a.timegmt DESC" +
                                    $" limit {limit}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                string description = (Reader.IsDBNull(4) ? "" : ((EmergencyTypes)Reader.GetInt32(4)).ToString());

                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(3),
                                    AlarmTypes.emergency, description, GetDTLocalFromSeconds(Reader.GetInt32(2)), Reader.GetInt32(2), 0);

                                item.Latitude = Reader.GetDouble(5);
                                item.Longitude = Reader.GetDouble(6);

                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine("DB Get Emerg Alerts: " + ee.ToString(), ConsoleColor.Red);
                }

                //get all zone alarm
                try
                {
                    string command = "SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                    " FROM geozoneinout as g" +
                                    " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                    " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                    " INNER JOIN zonename as zn on(g.zone_id = zn.idx)" +
                                    " WHERE preview =0 and g.\"type\" = 1" +
                                    " ORDER BY g.timegmt desc " +
                                    $" limit {limit}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                string text = ((uint)Reader.GetInt32(4) == 1) ? "OUT " + Reader.GetString(3) : "IN " + Reader.GetString(3);

                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2),
                                                                    AlarmTypes.zone, text, GetDTLocalFromSeconds(Reader.GetInt32(5)),
                                                                    Reader.GetInt32(5), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine("DB Get Zone Alerts: " + ee.ToString(), ConsoleColor.Red);
                }

                //get all land alarm
                try
                {
                    string command = " SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                     " FROM geozoneinout as g" +
                                            " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN place as zn on(g.zone_id = zn.idx)" +
                                            " WHERE preview = 0 and g.\"type\" = 2" +
                                            " ORDER BY g.timegmt DESC" +
                                            $" limit {limit}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {

                                string text = ((uint)Reader.GetInt32(4) == 1) ? "OUT " + Reader.GetString(3) : "IN " + Reader.GetString(3);

                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2), AlarmTypes.landmark,
                                                        text, GetDTLocalFromSeconds(Reader.GetInt32(5)), Reader.GetInt32(5), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine("DB Get Landmark Alerts: " + ee.ToString(), ConsoleColor.Red);
                }

                //get all speed alarm
                try
                {
                    string command = " SELECT  a.idx, a.sc_id, v.\"name\", a.timegmt, a.speed " +
                                     " FROM speedalarm as a" +
                                     " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                     " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                     " WHERE preview = 0 " +
                                     " ORDER BY a.timegmt desc" +
                                     $" limit {limit}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {

                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2),
                                                                    AlarmTypes.speed, Reader.GetInt32(4).ToString(), GetDTLocalFromSeconds(Reader.GetInt32(3)),
                                                                    Reader.GetInt32(3), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine("DB Get Speed Alerts: " + ee.ToString(), ConsoleColor.Red);
                }

                //get all telem alarm
                try
                {
                    string command = " SELECT  a.id, a.sc_id, v.\"name\", a.timegmt, tel.\"name\"" +
                                     " FROM telemetry_history as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN telemetry as tel on (a.telemetry_id = tel.id)" +
                                            " WHERE ack = 0 and a.alarm = 1 " +
                                            " ORDER BY a.timegmt desc" +
                                            $" limit {limit}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2), AlarmTypes.telemetry, Reader.GetString(4), GetDTLocalFromSeconds(Reader.GetInt32(3)), Reader.GetInt32(3), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine("DB Get Telem Alerts: " + ee.ToString(), ConsoleColor.Red);
                }


                countAlarm = list.Count;
                Utils.WriteLine("FINISH Get_nonACK_alarms", ConsoleColor.Yellow);

                conn.Close();
            }

            return list;
        }

        public int Get_nonACK_alarms_count(int userID)
        {
            Utils.WriteLine($"START Get_nonACK_alarms_count for user {userID}", ConsoleColor.Yellow);


            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    string query =
                    $" select sum(x) from " +
                         $" (" +
                         $" select count(*) as x from emergalarm e" +
                         $" where e.preview = 0 and e.sc_id IN ( SELECT VU.veh_id FROM vehicle_user as VU WHERE VU.user_id = {userID})" +
                        
                        $" UNION " +
                        $" select count(*) as x from geozoneinout g" +
                        $" where preview = 0 and g.sc_id IN ( SELECT VU.veh_id FROM vehicle_user as VU WHERE VU.user_id = {userID})" +



                        $" UNION " +
                        $" select count(*) as x from speedalarm s" +
                        $" where preview = 0 and s.sc_id IN ( SELECT VU.veh_id FROM vehicle_user as VU WHERE VU.user_id = {userID})" +
                        
                        $" UNION " +
                        $" select count(*) as x from telemetry_history t" +
                        $" where ack = 0 and t.sc_id IN(SELECT VU.veh_id FROM vehicle_user as VU WHERE VU.user_id = {userID})" +
                         $" ) as foo";



                    string result = string.Empty;
                    using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                    {
                        result = cmd.ExecuteScalar().ToString();
                    }


                    Utils.WriteLine("FINISH Get_nonACK_alarms for user", ConsoleColor.Yellow);
                    int x = 0;
                    int.TryParse(result, out x);
                    return x;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.ToString());
            }
           

            return 0;

        }
        //get all non ack alarms for unit
        public ArrayList Get_nonACK_alarms(int sc_id)
        {
            ArrayList list = new ArrayList();


            using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
            {
                conn.Open();

                //get all energ alarm
                try
                {
                    string command = " SELECT  a.idx, a.sc_id,a.timegmt,v.\"name\", a.emergency_type " +
                                     " FROM emergalarm as a" +
                                       " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                       " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                     $" WHERE preview = 0 AND a.sc_id = {sc_id}";


                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Alarms item = new Alarms(rep_type.EMERG, DateTime.Now, Reader.GetString(3), "");
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
                }

                //get all zone alarm
                try
                {
                    string command = " SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                     " FROM geozoneinout as g" +
                                        " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                        " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                        " INNER JOIN zonename as zn on(g.zone_id = zn.idx)" +
                                        $" WHERE preview = 0 AND g.sc_id = {sc_id}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Alarms item = new Alarms(rep_type.GEOFENC, DateTime.Now, Reader.GetString(2), "");
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
                }

                //get all land alarm
                try
                {
                    string command = "SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                     "FROM geozoneinout as g" +
                                            " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN place as zn on(g.zone_id = zn.idx)" +
                                            $" WHERE preview = 0 AND g.sc_id = {sc_id}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Alarms item = new Alarms(rep_type.GEOFENC, DateTime.Now, Reader.GetString(2), "");
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
                }

                //get all speed alarm
                try
                {
                    string command = "SELECT  a.idx, a.sc_id, v.\"name\", a.timegmt, a.speed " +
                                     "FROM speedalarm as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                           $" WHERE preview = 0 AND a.sc_id = {sc_id}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Alarms item = new Alarms(rep_type.SPEED, DateTime.Now, Reader.GetString(2), "");
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
                }

                //get all telem alarm
                try
                {
                    string command = "SELECT  a.id, a.sc_id, v.\"name\", a.timegmt, tel.\"name\" " +
                                     "FROM telemetry_history as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN telemetry as tel on (a.telemetry_id = tel.id)" +
                                           $" WHERE ack = 0 and a.alarm = 1 AND a.sc_id = {sc_id}";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Alarms item = new Alarms(rep_type.TELEMETRY_ALARM, DateTime.Now, Reader.GetString(2), "");
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
                }
                conn.Close();
            }
            countAlarm = list.Count;
            return list;
        }

        public Int64 GetNumberOfNonAckAllarmsForRadioId(Int64 scId)
        {
            Int64 numberOfNonAck = 0;
             
            try
            {
               

                string cmdEmergAlarm = "SELECT  count(*) FROM emergalarm as a" +
                                       $" WHERE preview =0 AND a.sc_id = {scId}";
                string cmdZoneLandAlarm = "SELECT  count(*) FROM geozoneinout as g" +
                                        $" WHERE preview =0 AND g.sc_id = {scId}";
                string cmdSpeedAlarm = "SELECT count(*) FROM speedalarm as a" +
                                        $" WHERE preview =0 AND a.sc_id = {scId} ";
                string cmdTelemAlarm = "SELECT  count(*) FROM telemetry_history as a" +
                                        $" WHERE ack =0 and a.alarm=1 AND a.sc_id = {scId} ";

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

                    connection.ConnectionString = getConnectionString();
                    connection.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand($"{cmdEmergAlarm} UNION {cmdZoneLandAlarm} UNION {cmdSpeedAlarm} UNION {cmdTelemAlarm}", connection))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                Int64 count = Reader.GetInt64(0);
                                numberOfNonAck += count;
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine($"GetNumberOfNonAckAllarmsForRadioId: {ee.Message} ", ConsoleColor.Red);
            }
           

            Utils.WriteLine($"Received {numberOfNonAck} non ack alarms for sc_id {scId}", ConsoleColor.Cyan);

            
            
            return numberOfNonAck;
        }

        public List<AlarmHisItem> Get_nonACK_alarms_user_id(Int32 userID)
        {
            
            List<AlarmHisItem> list = new List<AlarmHisItem>();

            using (NpgsqlConnection conn = new NpgsqlConnection(getConnectionString()))
            {
                conn.Open();

                //get all energ alarm
                try
                {
                    string command = "SELECT  a.idx, a.sc_id,a.timegmt,v.\"name\" " +
                                     " FROM emergalarm as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id) " +
                                            " INNER JOIN vehicle_user as u on (u.veh_id = v.id) " +
                                           $" WHERE u.user_id={userID} AND preview =0 ";


                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(3),
                                                                     AlarmTypes.emergency, "", GetDTLocalFromSeconds(Reader.GetInt32(2)),
                                                                     Reader.GetInt32(2), 0);
                                list.Add(item);
                            }
                        }
                    }

                }
                catch (Exception ee)
                {
                    Console.WriteLine(ee.Message, ConsoleColor.Red);
                }

                //get all zone alarm
                try
                {
                    string command = "SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                     "FROM geozoneinout as g" +
                                            " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN zonename as zn on(g.zone_id = zn.idx)" +
                                            " INNER JOIN vehicle_user as u on (u.veh_id = v.id) " +
                                           $" WHERE u.user_id= {userID} and preview =0 ";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                string text = ((uint)Reader.GetInt32(4) == 1) ? "OUT " : "IN " + Reader.GetString(3);


                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2),
                                                                     AlarmTypes.zone, text, GetDTLocalFromSeconds(Reader.GetInt32(5)),
                                                                     Reader.GetInt32(5), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Console.WriteLine(ee.Message, ConsoleColor.Red);
                }

                //get all land alarm
                try
                {

                    string command = "SELECT  g.idx, g.sc_id, v.\"name\", zn.\"name\", g.\"action\",g.timeGMT " +
                                     " FROM geozoneinout as g" +
                                            " INNER JOIN subscriber_history as sh on (g.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN place as zn on(g.zone_id = zn.idx)" +
                                            " INNER JOIN vehicle_user as u on (u.veh_id = v.id) " +
                                           $" WHERE u.user_id= {userID} and preview =0 ";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                string text = ((uint)Reader.GetInt32(4) == 1) ? "OUT " : "IN " + Reader.GetString(3);

                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2),
                                                                     AlarmTypes.landmark, text, GetDTLocalFromSeconds(Reader.GetInt32(5)),
                                                                     Reader.GetInt32(5), 0);
                                list.Add(item);
                            }
                        }
                    }

                }
                catch (Exception ee)
                {
                    Console.WriteLine(ee.Message, ConsoleColor.Red);
                }

                //get all speed alarm
                try
                {

                    string command = "SELECT  a.idx, a.sc_id, v.\"name\", a.timegmt, a.speed " +
                                     " FROM speedalarm as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN vehicle_user as u on (u.veh_id = v.id) " +
                                            $" WHERE u.user_id={userID}  and preview =0 ";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2), AlarmTypes.speed, Reader.GetInt32(4).ToString(), GetDTLocalFromSeconds(Reader.GetInt32(3)), Reader.GetInt32(3), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Console.WriteLine(ee.Message, ConsoleColor.Red );
                }

                //get all telem alarm
                try
                {
                    string command = " SELECT  a.id, a.sc_id, v.\"name\", a.timegmt, tel.\"name\" " +
                                     " FROM telemetry_history as a" +
                                            " INNER JOIN subscriber_history as sh on (a.sc_id = sh.sc_id) " +
                                            " INNER JOIN vehicle as v on (v.id = sh.veh_id)" +
                                            " INNER JOIN telemetry as tel on (a.telemetry_id = tel.id)" +
                                            " INNER JOIN vehicle_user as u on (u.veh_id = v.id) " +
                                            $" WHERE u.user_id= {userID} and ack =0 and a.alarm=1 ";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, conn))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                AlarmHisItem item = new AlarmHisItem(Reader.GetInt32(0), Reader.GetInt32(1), Reader.GetString(2),
                                                    AlarmTypes.telemetry, Reader.GetString(4), GetDTLocalFromSeconds(Reader.GetInt32(3)),
                                                    Reader.GetInt32(3), 0);
                                list.Add(item);
                            }
                        }
                    }
                }
                catch (Exception ee)
                {
                    Console.WriteLine(ee.Message, ConsoleColor.Red);
                }


                countAlarm = list.Count;
                conn.Close();
            }// conn

            return list;
        }

        public System.DateTime GetDTLocalFromSeconds(Int32 param)
        {
            DateTime dateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            dateTime = dateTime.AddSeconds((double)param);
            dateTime = dateTime.ToLocalTime();
            return dateTime;
        }
        
    }

    public class Alarm
    {
        private int id;
        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private int sc_id;
        public int Sc_id
        {
            get { return sc_id; }
            set { sc_id = value; }
        }

        private string emergency;

        public string Emergency
        {
            get { return emergency; }
            set { emergency = value; }
        }
        private string landmark;

        public string Landmark
        {
            get { return landmark; }
            set { landmark = value; }
        }
        private string zone;

        public string Zone
        {
            get { return zone; }
            set { zone = value; }
        }
        private string loneworker;

        public string Loneworker
        {
            get { return loneworker; }
            set { loneworker = value; }
        }
        private string speed;

        public string Speed
        {
            get { return speed; }
            set { speed = value; }
        }
        private string email;

        public string Email
        {
            get { return email; }
            set { email = value; }
        }
        private string imei;

        public string Imei
        {
            get { return imei; }
            set { imei = value; }
        }

        private Boolean emsound;
        public Boolean Emsound
        {
            get { return emsound; }
            set { emsound = value; }
        }

        private Boolean empopup;
        public Boolean Empopup
        {
            get { return empopup; }
            set { empopup = value; }
        }

        private Boolean geosound;
        public Boolean Geosound
        {
            get { return geosound; }
            set { geosound = value; }
        }

        private Boolean geopopup;
        public Boolean Geopopup
        {
            get { return geopopup; }
            set { geopopup = value; }
        }

        private Boolean speedsound;
        public Boolean Speedsound
        {
            get { return speedsound; }
            set { speedsound = value; }
        }

        private Boolean speedpopup;
        public Boolean Speedpopup
        {
            get { return speedpopup; }
            set { speedpopup = value; }
        }

        private Boolean telemsound;
        public Boolean Telemsound
        {
            get { return telemsound; }
            set { telemsound = value; }
        }

        private Boolean telempopup;
        public Boolean Telempopup
        {
            get { return telempopup; }
            set { telempopup = value; }
        }

        public Alarm()
        {

        }

        public Alarm(int id, int sc_id, string emergency, string landmark, string zone, string loneworker, string speed, string email, Boolean empopup, Boolean emsound, Boolean geopopup, Boolean geosound, Boolean speedpopup, Boolean speedsound, Boolean telempopup, Boolean telemsound)
        {
            this.id = id;
            this.sc_id = sc_id;
            this.emergency = emergency;
            this.landmark = landmark;
            this.zone = zone;
            this.loneworker = loneworker;
            this.speed = speed;
            this.email = email;
            this.imei = "";
            this.empopup = empopup;
            this.emsound = emsound;
            this.geopopup = geopopup;
            this.geosound = geosound;
            this.speedpopup = speedpopup;
            this.speedsound = speedsound;
            this.telempopup = telempopup;
            this.telemsound = telemsound;
        }

        public Alarm(int id, string imei, string emergency,string landmark,string zone, string loneworker, string speed, string email,Boolean empopup,Boolean emsound,Boolean geopopup,Boolean geosound,Boolean speedpopup,Boolean speedsound,Boolean telempopup,Boolean telemsound)
        {
            this.id = id;
            this.imei = imei;
            this.emergency = emergency;
            this.landmark = landmark;
            this.zone = zone;
            this.loneworker = loneworker;
            this.speed = speed;
            this.email = email;
            this.sc_id = 0;
            this.empopup = empopup;
            this.emsound = emsound;
            this.geopopup = geopopup;
            this.geosound = geosound;
            this.speedpopup = speedpopup;
            this.speedsound = speedsound;
            this.telempopup = telempopup;
            this.telemsound = telemsound;
        }
    }

    public class AlarmHisItem
    {
        private int id;
        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private int sc_id;
        public int Sc_id
        {
            get { return sc_id; }
            set { sc_id = value; }
        }

        private string unitName;
        public string UnitName
        {
            get { return unitName; }
            set { unitName = value; }
        }

        private AlarmTypes type;
        public AlarmTypes Type
        {
            get { return type; }
            set { type = value; }
        }

        private string text;
        public string Text
        {
            get { return text; }
            set { text = value; }
        }

        private DateTime timegmt;
        public DateTime Timegmt
        {
            get { return timegmt; }
            set { timegmt = value; }
        }

        private Int32 timegmtUnix;
        public Int32 TimegmtUnix
        {
            get { return timegmtUnix; }
            set { timegmtUnix = value; }
        }

        private int ack;
        public int ACK
        {
            get { return ack; }
            set { ack = value; }
        }
        
        public double Latitude { get; set; }
        
        public double Longitude { get; set; }


        public AlarmHisItem(int id, int sc_id, string unitName, AlarmTypes type, string text, DateTime timegmt, Int32 timegmtUnix, int ack)
        {
            this.id = id;
            this.sc_id = sc_id;
            this.unitName = unitName;
            this.type = type;
            this.text = text;
            this.timegmt = timegmt;
            this.timegmtUnix = timegmtUnix;
            this.ack = ack;
            Latitude = 0;
            Longitude = 0;
        }
    }


    public class AlarmHisItemComparer : IComparer
    {
        public AlarmHisItemComparer() : base() { }

        int IComparer.Compare(object x, object y)
        {

            AlarmHisItem X = x as AlarmHisItem;
            AlarmHisItem Y = y as AlarmHisItem;
            if (X.TimegmtUnix < Y.TimegmtUnix) return 1;
            else if (X.TimegmtUnix == Y.TimegmtUnix) return 0;
            else return -1;
        }
    }

    public enum AlarmTypes{all = -1, emergency = 0,landmark = 1, zone = 2, speed = 3,telemetry = 4, unknown = 11}

    public enum EmergencyTypes { NORMAL = 0, MANDOWN = 1, LONEWORKER = 2, MOTIONLESS = 3, PHONE_DROPPED = 5 };

    
}