SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBemailServiceManager.cs

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

namespace SafeMobileLib
{
    public class DBemailServiceManager : DBmanager
    {
        public DBemailServiceManager(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 EmailService GetEmailServiceSettings()
        {

            EmailService service= null;

            try
            {

                using (NpgsqlConnection connection = new NpgsqlConnection())
                {

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

                    string command = "SELECT id, \"user\", pass, \"enable\", pop_server, pop_port, smtp_server, " +
                                    "smtp_port, is_pop_ssl, is_smtp_ssl, last_email_time FROM emailservice where id=1";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                service = new EmailService();
                                service.EmailAddress = (Reader.IsDBNull(1) ? "" : Reader.GetString(1));
                                service.Pass = (Reader.IsDBNull(2) ? "" : Reader.GetString(2));
                                service.Enable = (Reader.IsDBNull(3) ? false : ((Reader.GetInt32(3) == 0) ? false : true));
                                service.PopServer = (Reader.IsDBNull(4) ? "" : Reader.GetString(4));
                                service.PopPort = (Reader.IsDBNull(5) ? 995 : Reader.GetInt32(5));
                                service.SmtpServer = (Reader.IsDBNull(6) ? "" : Reader.GetString(6));
                                service.SmtpPort = (Reader.IsDBNull(7) ? 587 : Reader.GetInt32(7));
                                service.IsPopSSL = (Reader.IsDBNull(8) ? false : Reader.GetBoolean(8));
                                service.IsSmtpSSL = (Reader.IsDBNull(9) ? false : Reader.GetBoolean(9));
                                service.LastEmailTime = (Reader.IsDBNull(10) ? 0 : Reader.GetInt32(10));
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message, ConsoleColor.Red);
            }
            
            return service;
        }

        public Boolean GetPollSMSServiceSettings()
        {

            Boolean toreturn = false;
            

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

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

                    string command = "SELECT es.\"enable\" FROM emailservice as es where id=2";

                    using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
                    {
                        using (NpgsqlDataReader Reader = cmd.ExecuteReader())
                        {
                            while (Reader.Read())
                            {
                                toreturn = (Reader.GetInt32(0) == 0) ? false : true;
                            }
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                Console.WriteLine(ee.Message, ConsoleColor.Red);
                
            }
          
            return toreturn;
        }

        public sqlResponse SetEmailServiceSettings(EmailService eService)
        {


            string command = String.Format("UPDATE emailservice SET \"user\" = '{0}', \"pass\" = '{1}', \"pop_server\" = '{2}', \"pop_port\" = {3}, " +
                                            " \"smtp_server\" = '{4}', \"smtp_port\" = {5}, \"is_pop_ssl\" = {6}, \"is_smtp_ssl\" = {6}, " +
                                            " \"last_email_time\" = {8}, \"enable\" = {9} where id = 1",
                                            eService.EmailAddress, eService.Pass, eService.PopServer, eService.PopPort, eService.SmtpServer, eService.SmtpPort,
                                            eService.IsPopSSL, eService.IsSmtpSSL, eService.LastEmailTime, eService.Enable ? 1 : 0);

            return RunCommand(command);
        }
               


        public sqlResponse SetLastEmailTime(Int64 time)
        {
           string command = String.Format("UPDATE emailservice SET \"last_email_time\" = {0} where id = 1", time);
           return RunCommand(command);
        }

        public sqlResponse SetEmailServiceEnable(Boolean isEnabled)
        {

            string command = String.Format("UPDATE emailservice SET \"enable\" = {0} where id = 1", isEnabled ? 1 : 0);
            return RunCommand(command);
        }

        public sqlResponse SetPollAfterSMSSettings(Int32 enabled)
        {
            string command = $"UPDATE emailservice SET \"enable\"= {enabled} where id=2";
            return RunCommand(command);
        }
            
    }

    public class EmailService
    {
        private string emailAddress;
        public string EmailAddress
        {
            get { return emailAddress; }
            set { emailAddress = value; }
        }

        private string pass;
        public string Pass
        {
            get { return pass; }
            set { pass = value; }
        }

        private string popServer;
        public string PopServer
        {
            get { return popServer; }
            set { popServer = value; }
        }

        private string smtpServer;
        public string SmtpServer
        {
            get { return smtpServer; }
            set { smtpServer = value; }
        }

        private Int32 popPort;
        public Int32 PopPort
        {
            get { return popPort; }
            set { popPort = value; }
        }

        private Int32 smtpPort;
        public Int32 SmtpPort
        {
            get { return smtpPort; }
            set { smtpPort = value; }
        }

        private Int64 lastEmailTime;
        public Int64 LastEmailTime
        {
            get { return lastEmailTime; }
            set { lastEmailTime = value; }
        }

        private bool enable;
        public bool Enable
        {
            get { return enable; }
            set { enable = value; }
        }

        private bool isPopSSL;
        public bool IsPopSSL
        {
            get { return isPopSSL; }
            set { isPopSSL = value; }
        }

        private bool isSmtpSSL;
        public bool IsSmtpSSL
        {
            get { return isSmtpSSL; }
            set { isSmtpSSL = value; }
        }



        public EmailService(string user, string pass, bool enable)
        {
            this.emailAddress = user;
            this.pass = pass;
            this.enable = enable;
        }

        public EmailService()
        {

        }
    }
}