using Dapper;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using SuperCms.Oracle;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using SuperCms.Controllers;
using SuperCms.Models;

namespace SuperCms.Repositories
{
    public class SuperCmsRepo : ISuperCmsRepo
    {
        IConfiguration configuration;
        public SuperCmsRepo(IConfiguration _configuration)
        {
            configuration = _configuration;
        }
        public IDbConnection GetConnection()
        {
            var connectionString = configuration.GetSection("Connection").Value;
            var conn = new OracleConnection(connectionString);
            return conn;
        }

        public object GetConnData(log4net.ILog log, String fromDate, String toDate)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_fromdate", OracleDbType.NVarchar2, ParameterDirection.Input, fromDate);
                dyParam.Add("sc_todate", OracleDbType.NVarchar2, ParameterDirection.Input, toDate);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETCONNDATA";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetConnDataByService(log4net.ILog log, string serviceID, string fromDate, string toDate)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_fromdate", OracleDbType.NVarchar2, ParameterDirection.Input, fromDate);
                dyParam.Add("sc_todate", OracleDbType.NVarchar2, ParameterDirection.Input, toDate);
                dyParam.Add("sc_serviceid", OracleDbType.Int32, ParameterDirection.Input, serviceID);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETCONNDATABYSERVICE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);

                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetConnConfig(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETCONNCONFIG";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetConnTelco(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETCONNTELCO";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetConnCompany(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETCONNCOMPANY";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetAllBots(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETALLBOTS";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetAllGroups(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETALLGROUPS";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetScheduleByRow(log4net.ILog log, string seqPage, string rowsOnPage)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_seqpage", OracleDbType.Int32, ParameterDirection.Input, seqPage);
                dyParam.Add("sc_rowsonpage", OracleDbType.Int32, ParameterDirection.Input, rowsOnPage);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETSCHEDULEBYROW";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetTotalRow(log4net.ILog log, string type)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = type == UtilsController.Constant.BOTS ? "GETBOTTOTALROW" :
                       type == UtilsController.Constant.GROUPS ? "GETGROUPTOTALROW" :
                       "GETSCHEDULETOTALROW";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddingBotStructure(log4net.ILog log, BotStructure bot)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, bot.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, bot.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, bot.status);
                dyParam.Add("sc_startdate", OracleDbType.NVarchar2, ParameterDirection.Input, bot.startDate);
                dyParam.Add("sc_enddate", OracleDbType.NVarchar2, ParameterDirection.Input, bot.endDate);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGBOTSTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddingGroupStructure(log4net.ILog log, GroupStructure group)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, group.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, group.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, group.status);
                dyParam.Add("sc_startDate", OracleDbType.NVarchar2, ParameterDirection.Input, group.startDate);
                dyParam.Add("sc_enddate", OracleDbType.NVarchar2, ParameterDirection.Input, group.endDate);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGGROUPSTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddingScheduleStructure(log4net.ILog log, ScheduleStructure schedule)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_botid", OracleDbType.Int32, ParameterDirection.Input, schedule.botID);
                dyParam.Add("sc_groupid", OracleDbType.Int32, ParameterDirection.Input, schedule.groupID);
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, schedule.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, schedule.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, schedule.status);
                dyParam.Add("sc_timerun", OracleDbType.NVarchar2, ParameterDirection.Input, schedule.timeRun);
                dyParam.Add("sc_content", OracleDbType.NClob, ParameterDirection.Input, schedule.content);
                dyParam.Add("sc_note", OracleDbType.NClob, ParameterDirection.Input, schedule.note);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGSCHEDULESTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditingBotStructure(log4net.ILog log, BotStructure bot)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, bot.id);
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, bot.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, bot.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, bot.status);
                dyParam.Add("sc_startdate", OracleDbType.NVarchar2, ParameterDirection.Input, bot.startDate);
                dyParam.Add("sc_enddate", OracleDbType.NVarchar2, ParameterDirection.Input, bot.endDate);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGBOTSTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditingGroupStructure(log4net.ILog log, GroupStructure group)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, group.id);
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, group.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, group.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, group.status);
                dyParam.Add("sc_startdate", OracleDbType.NVarchar2, ParameterDirection.Input, group.startDate);
                dyParam.Add("sc_enddate", OracleDbType.NVarchar2, ParameterDirection.Input, group.endDate);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGGROUPSTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditingScheduleStructure(log4net.ILog log, ScheduleStructure schedule)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, schedule.id);
                dyParam.Add("sc_botid", OracleDbType.Int32, ParameterDirection.Input, schedule.botID);
                dyParam.Add("sc_groupid", OracleDbType.Int32, ParameterDirection.Input, schedule.groupID);
                dyParam.Add("sc_name", OracleDbType.NVarchar2, ParameterDirection.Input, schedule.name);
                dyParam.Add("sc_description", OracleDbType.NClob, ParameterDirection.Input, schedule.description);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, schedule.status);
                dyParam.Add("sc_timerun", OracleDbType.NVarchar2, ParameterDirection.Input, schedule.timeRun);
                dyParam.Add("sc_content", OracleDbType.NClob, ParameterDirection.Input, schedule.content);
                dyParam.Add("sc_note", OracleDbType.NClob, ParameterDirection.Input, schedule.note);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGSCHEDULESTRUCTURE";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object DeleteStructure(log4net.ILog log, string tableType, string id)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, id);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = tableType == UtilsController.Constant.BOTS ? "DELETEBOTSTRUCTURE" :
                       tableType == UtilsController.Constant.GROUPS ? "DELETEGROUPSTRUCTURE" :
                       tableType == UtilsController.Constant.SCHEDULES ? "DELETESCHEDULESTRUCTURE" :
                       tableType == UtilsController.Constant.CONFIGS ? "DELETECONFIG" :
                       tableType == UtilsController.Constant.SERVICE_CONFIG ? "DELETECONNCONFIG" :
                       "DELETESENDMAIL";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object Login(log4net.ILog log, string account, string password)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_account", OracleDbType.NVarchar2, ParameterDirection.Input, account);
                dyParam.Add("sc_password", OracleDbType.NVarchar2, ParameterDirection.Input, password);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADMINLOGIN";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetAllConfigs(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETALLCONFIGS";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddingConfig(log4net.ILog log, Config config)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_key", OracleDbType.NVarchar2, ParameterDirection.Input, config.key);
                dyParam.Add("sc_value", OracleDbType.NVarchar2, ParameterDirection.Input, config.value);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGCONFIG";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditingConfig(log4net.ILog log, Config config)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, config.id);
                dyParam.Add("sc_key", OracleDbType.NVarchar2, ParameterDirection.Input, config.key);
                dyParam.Add("sc_value", OracleDbType.NVarchar2, ParameterDirection.Input, config.value);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGCONFIG";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object GetAllSendMails(log4net.ILog log)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "GETSENDMAIL";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddingSendMail(log4net.ILog log, SendMail mail)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_telcoid", OracleDbType.Int32, ParameterDirection.Input, mail.telcoId);
                dyParam.Add("sc_companyid", OracleDbType.Int32, ParameterDirection.Input, mail.companyId);
                dyParam.Add("sc_serviceid", OracleDbType.Int32, ParameterDirection.Input, mail.serviceId);
                dyParam.Add("sc_listmail", OracleDbType.NClob, ParameterDirection.Input, mail.listMail);
                dyParam.Add("sc_message", OracleDbType.NClob, ParameterDirection.Input, mail.message);
                dyParam.Add("sc_timesend", OracleDbType.NVarchar2, ParameterDirection.Input, mail.timeSend);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, mail.status);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGSENDMAIL";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditingSendMail(log4net.ILog log, SendMail mail)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.Int32, ParameterDirection.Input, mail.id);
                dyParam.Add("sc_telcoid", OracleDbType.Int32, ParameterDirection.Input, mail.telcoId);
                dyParam.Add("sc_companyid", OracleDbType.Int32, ParameterDirection.Input, mail.companyId);
                dyParam.Add("sc_serviceid", OracleDbType.Int32, ParameterDirection.Input, mail.serviceId);
                dyParam.Add("sc_listmail", OracleDbType.NClob, ParameterDirection.Input, mail.listMail);
                dyParam.Add("sc_message", OracleDbType.NClob, ParameterDirection.Input, mail.message);
                dyParam.Add("sc_timesend", OracleDbType.NVarchar2, ParameterDirection.Input, mail.timeSend);
                dyParam.Add("sc_status", OracleDbType.Int32, ParameterDirection.Input, mail.status);
                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGSENDMAIL";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object AddConnConfig(log4net.ILog log, ConnConfig connConfig)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_telcoid", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.telcoID);
                dyParam.Add("sc_companyid", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.companyID);

                dyParam.Add("sc_servicename", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.serviceName);
                dyParam.Add("sc_url", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.url);
                dyParam.Add("sc_vsql", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.vSql);
                dyParam.Add("sc_connection", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.connection);
                dyParam.Add("sc_daterun", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.dateRun);
                dyParam.Add("sc_dateget", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.dateGet);
                dyParam.Add("sc_hourrun", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.hourRun);
                dyParam.Add("sc_telco", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.telco);
                dyParam.Add("sc_status", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.status);
                dyParam.Add("sc_state", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.state);
                dyParam.Add("sc_money", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.money);

                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "ADDINGCONNCONFIG";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public object EditConnConfig(log4net.ILog log, ConnConfig connConfig)
        {
            object result = null;
            var conn = this.GetConnection();
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("sc_id", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.id);
                dyParam.Add("sc_servicename", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.serviceName);
                dyParam.Add("sc_url", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.url);
                dyParam.Add("sc_vsql", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.vSql);
                dyParam.Add("sc_connection", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.connection);
                dyParam.Add("sc_telcoid", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.telcoID);
                dyParam.Add("sc_companyid", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.companyID);
                dyParam.Add("sc_hourrun", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.hourRun);
                dyParam.Add("sc_telco", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.telco);
                dyParam.Add("sc_status", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.status);
                dyParam.Add("sc_state", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.state);
                dyParam.Add("sc_money", OracleDbType.NVarchar2, ParameterDirection.Input, connConfig.money);

                dyParam.Add("sc_cursor", OracleDbType.RefCursor, ParameterDirection.Output);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "EDITINGCONNCONFIG";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }
    }
}