SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBiconsManager.cs

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

namespace SafeMobileLib.DBmanagers
{
    public class DBiconsManager : DBmanager
    {
        public DBiconsManager(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)
        {

        }
        
        /// <summary>
        /// Type 1 = place and 2 = units
        /// </summary>
        /// <param name="IconName"></param>
        /// <param name="Imagedata"></param>
        /// <returns></returns>
        public sqlResponse Insert_Place_Icons(String IconName, byte[] Imagedata)
        {
           
            sqlResponse resp;
            string placetype_id = "";
            try
            {

               
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = "insert into placetype (type_name) VALUES('" + IconName + "')";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    command = "SELECT currval(pg_get_serial_sequence('placetype', 'type_id'))";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        placetype_id = cmd.ExecuteScalar().ToString();
                    }

                    command = "INSERT INTO newicons(icontype,id,imagedata) SELECT @Type, @Id, @ImageData";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.Parameters.Add("@Type", NpgsqlTypes.NpgsqlDbType.Integer).Value = 1;
                        cmd.Parameters.Add("@Id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = placetype_id;
                        cmd.Parameters.Add("@ImageData", NpgsqlTypes.NpgsqlDbType.Bytea).Value = Imagedata;
                        cmd.ExecuteNonQuery();
                    }
                    resp = sqlResponse.done;
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
                resp = sqlResponse.SQLerror;
            }
          
            return resp;
        }

        /// <summary>
        /// Type 1 = place and 2 = units
        /// </summary>
        /// <param name="IconName"></param>
        /// <param name="Imagedata"></param>
        /// <returns></returns>
        public sqlResponse Insert_Units_Icons(String IconName, byte[] Imagedata)
        {
            
            sqlResponse resp;
            
            string car_id = "";
            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = "SELECT MAX(id) FROM car";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        car_id = cmd.ExecuteScalar().ToString();
                    }
                    Int64 carId = 1000;
                    Int64.TryParse(car_id, out carId);

                    // increment the new carId 
                    carId = carId + 1;

                    command = "INSERT INTO car (id, name, pattern, displayed_name) "
                                + " VALUES(" + (carId) + ",'" + IconName + "','custom_" + IconName.ToLower().Trim().Replace(' ', '_')
                                + "','" + IconName + "')";

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

                    command = "INSERT INTO newicons(icontype,id,imagedata) SELECT @Type, @Id, @ImageData";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.Parameters.Add("@Type", NpgsqlTypes.NpgsqlDbType.Integer).Value = 2;
                        cmd.Parameters.Add("@Id", NpgsqlTypes.NpgsqlDbType.Double).Value = carId;
                        cmd.Parameters.Add("@ImageData", NpgsqlTypes.NpgsqlDbType.Bytea).Value = Imagedata;
                        cmd.ExecuteNonQuery();
                    }

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

        public Byte[] Get_Place_Image(Int32 ID) 
        {
            Byte[] productImageByte = null;
            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = $"SELECT ImageData FROM newicons where id={ID} and icontype=1";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        productImageByte = (Byte[])cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
            }
          
            return productImageByte;            
        }

        public Byte[] Get_Unit_Image(Int32 ID)
        {
          
            Byte[] productImageByte = null;
            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = $"SELECT ImageData FROM newicons where id={ID} and icontype=2";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        productImageByte = (Byte[])cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
            }
           
            return productImageByte;
        }


        //need to update all old stuff one existing ID
        public sqlResponse Delete_Units_Icons(Int32 ID, Int32 firstID)
        {
            sqlResponse resp;

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

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

                    string command = $"UPDATE vehicle SET driver_id= {firstID} WHERE driver_id={ID}";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    command = $"DELETE FROM car WHERE id={ID}";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    command = $"DELETE FROM newicons WHERE id={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 List<IconTheme> GetIconThemes()
        {
           
            List<IconTheme> ret = new List<IconTheme>();

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

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

                    using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, name, prefix FROM icon_theme", connection))
                    {
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                IconTheme obj = new IconTheme();

                                obj.Id = (int)dr.GetInt32(0);
                                obj.Name = dr.GetString(1);
                                obj.Prefix = dr.GetString(2);

                                ret.Add(obj);
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
            }
          
            return ret;
        }

        public List<Place> getAllPlaceIcons()
        {
            List<Place> ret = new List<Place>();
            try
            {
                

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = "SELECT type_name, type_id from placetype ORDER by type_name";
                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Place obj = new Place();
                                obj.type = dr.GetString(0);
                                obj.idx = dr.GetInt32(1);
                                obj.pic = "";

                                ret.Add(obj);

                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
            }

            return ret;
        }

        public List<Car> getAllUnitIcons()
        {
            
            List<Car> suidList = new List<Car>();

            try
            {


                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = "SELECT name, id, pattern, displayed_name from car ORDER by name";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Car obj = new Car();
                                obj.Name = dr.GetString(0);
                                obj.idx = dr.GetInt32(1);
                                obj.iconPattern = dr.IsDBNull(2) ? obj.Name.ToLower().Trim().Replace(' ', '_') : dr.GetString(2);
                                obj.DisplayedName = dr.IsDBNull(3) ? obj.Name : dr.GetString(3);

                                suidList.Add(obj);
                            }

                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            
           
            return suidList;
        }
    }

    public class Place
    {
        public string name;
        public string desc { get; set; }
        public double lat { get; set; }
        public double lng { get; set; }
        public string type { get; set; }
        public string pic { get; set; }
        public string address { get; set; }
        public Int32 idx { get; set; }
        public Int32 useridx { get; set; }

        public String Name
        {
            get
            {
                return name;
            }
        }

        public String Type
        {
            get
            {
                return type;
            }
        }
    }

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

        private string name;
        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        private string prefix;
        public string Prefix
        {
            get { return prefix; }
            set { prefix = value; }
        }

        public IconTheme()
        {
            this.id = 0;
            this.name = "";
            this.prefix = "";  
        }
    }


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

        private string placename;
        public string Placename
        {
            get { return placename; }
            set { placename = value; }
        }

        private string pic;
        public string Pic
        {
            get { return pic; }
            set { pic = value; }
        }

        public PlaceIcons(int id, string placename, string pic)
        {
            this.id = id;
            this.placename = placename;
            this.pic = pic;  
        }
    }

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

        private string unitname;
        public string Unitname
        {
            get { return unitname; }
            set { unitname = value; }
        }

        private string pic;
        public string Pic
        {
            get { return pic; }
            set { pic = value; }
        }

        public UnitIcons(int id, string unitname, string pic)
        {
            this.id = id;
            this.unitname = unitname;
            this.pic = pic;
        }
    }

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

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

            UnitIcons X = x as UnitIcons;
            UnitIcons Y = y as UnitIcons;
            return String.Compare(X.Unitname, Y.Unitname);
        }
    }

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

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

            PlaceIcons X = x as PlaceIcons;
            PlaceIcons Y = y as PlaceIcons;
            return String.Compare(X.Placename, Y.Placename);
        }
    }
}