using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Collections.Concurrent;
using System.Configuration;

namespace DataAccessLayer
{
    public class DataAccessDE : IDisposable
    {        
        private static ConcurrentDictionary<string, bool> preCalculatedProcedureTables = new ConcurrentDictionary<string, bool>();
        //c6martperiodico.gestione_s_intermediari - select only
        //c6martperiodico.pl_d2_s150anagrafica - select only
        //c6martperiodico.pl_d2_s166contocorrenteai - select only
        //c6martperiodico.pl_mp_s132profilorischioadeguatezza - select only
        //c6martperiodico.pl_mp_s137bis_foi80 - select only
        //c6martperiodico.pl_mp_s149dettaglioprodottiaffluent - select only
        //c6martperiodico.pl_s25anagrafica - select only
        //c6martperiodico.pl_s80bisevoluzionerischio - select only

        //c6martperiodico.pl_d_s331areebisogno - proc missing on C6StampeCentralizzate
        //c6martperiodico.pl_d_s332fvi_cs80_ct80 - proc missing on C6StampeCentralizzate
        //c6martperiodico.pl_d_s333fonditalia80 - proc missing on C6StampeCentralizzate
        private static string[] mergedProcedures = new string[] {
                //"c6martperiodico.pl_s43lineaselfbf",
                //"c6martperiodico.pl_s10patrimoniofinanziario",
                //"c6martperiodico.pl_prodotti_non_associati_area_inv",
                //"c6martperiodico.pl_d2_s151profilodirischio",
                //"c6martperiodico.pl_d2_s152patrimonio",
                //"c6martperiodico.pl_d2_s153patrimoniofinanziario",
                //"c6martperiodico.pl_d2_s154patrimoniofinanziario",
                //"c6martperiodico.pl_d2_s155patrimonioimmobiliare",
                //"c6martperiodico.pl_d2_s156altropatrimonio",
                //"c6martperiodico.pl_d2_s157confrontopiramidi",
                //"c6martperiodico.pl_d2_s158piramidemodello",
                //"c6martperiodico.pl_d2_s159patrimoniofinanziario",
                //"c6martperiodico.pl_d2_s160dettaglioprodottibf",
                //"c6martperiodico.pl_d2_s162dettaglioprodotti",
                //"c6martperiodico.pl_d2_s163patrimoniofinanziariomassetclass",
                //"c6martperiodico.pl_d2_s164polizzealtriistituti",
                //"c6martperiodico.pl_d2_s165fondialtriistituti",
                //"c6martperiodico.pl_d2_s167distribuzionerischiocredito",
                //"c6martperiodico.pl_d2_s168tabellaemittenti",
                //"c6martperiodico.pl_s43dettaglioprodottibf",
                //"c6martperiodico.pl_d2_s169rischiomercatorischiocredito",
                //"c6martperiodico.pl_d2_s170rischiodiversificazione",
                //"c6martperiodico.pl_d2_s171patrimoniononrappresentabileassettclass",
                //"c6martperiodico.pl_areemonitorate",
                //"c6martperiodico.pl_mp_s137bonus",
                //"c6martperiodico.pl_mp_s135monitoraggioinvestimento",
                //"c6martperiodico.pl_mp_s134monitoraggioliquidita",
                //"c6martperiodico.pl_mp_s134bismonitoraggioliqinv",
                //"c6martperiodico.pl_mp_s133bisprincipaliprodotti",
                //"c6martperiodico.pl_mp_s133areebisognoprincprod",
                //"c6martperiodico.pl_mp_s146rischiodiversificazione",
                //"c6martperiodico.pl_mp_s141analisirisparmiotable",
                //"c6martperiodico.pl_mp_s140analisirisparmiocharts",
                //"c6martperiodico.pl_mp_s137biscapitaleprotetto",
                //"c6martperiodico.pl_s43contocorrentebf",
                //"c6martperiodico.pl_s43bisdettaglioprodottibf",
                //"c6martperiodico.pl_s88patrimoniononconsideratonelcalcolodelrendimento",
                //"c6martperiodico.pl_s85rendimentoportafogliocono",
                //"c6martperiodico.pl_s82datisintetici",
                //"c6martperiodico.pl_s82bisdatisintetici",
                //"c6martperiodico.pl_s76alternativapatrimoniononrappresentabileassettclass",
                //"c6martperiodico.pl_s80alternativa",
                //"c6martperiodico.pl_s96rischiomercatorischiocredito",
                //"c6martperiodico.pl_d_s170rischiodiversificazione"

                "c6martperiodico.pl_d2_s169rischiomercatorischiocredito",
                "c6martperiodico.pl_d2_s170rischiodiversificazione"


            };
        private static int commandTimeout
        {
            get
            {
                try
                {
                    return Int32.Parse(ConfigurationManager.AppSettings["sqlCommandTimeout"]);
                }
                catch
                {
                    return 200;
                }
            }            
        }
        private bool CanTakeDataFromPreCalculatedTables(string storedProcedure, List<Parametro> paramList)
        {
            //int usePrecalculatedTablesParam = int.Parse(ConfigurationManager.AppSettings["usePrefilledTables"]);
            bool usePrecalculatedTablesParam = bool.Parse(ConfigurationManager.AppSettings["usePrefilledTables"]);

            string procedureName = storedProcedure.ToLower().Replace("[", "").Replace("]", "");
            //if (paramList == null || usePrecalculatedTablesParam == 0 ||
            if (paramList == null || !usePrecalculatedTablesParam ||
                !(paramList.Where(x => x.ParameterName.ToLower().Equals("rete") || x.ParameterName.ToLower().Equals("codicefiscale")).Count() == 2 ||
                  paramList.Where(x => x.ParameterName.ToLower().Equals("rete") || x.ParameterName.ToLower().Equals("codicecliente")).Count() == 2) ||
                  !mergedProcedures.Contains(procedureName)
                )
            {
                return false;
            }

            string tableName = "wh." + storedProcedure.Substring(storedProcedure.IndexOf('.') + 1, storedProcedure.Length - storedProcedure.IndexOf('.') - 1).ToLower().Replace("[", "").Replace("]", "");

            if (!preCalculatedProcedureTables.ContainsKey(tableName))
            {
                string checkTableExistanceStatement = "IF object_id('" + tableName + "') is not null select 2 ELSE select 1";
                int tableExistanceResult = (int)ExecuteScalar(DBProvider.SqlServerStampeC6, CommandType.Text, checkTableExistanceStatement);

                if (tableExistanceResult == 2)
                {
                    preCalculatedProcedureTables.TryAdd(tableName, true);
                }
                else
                {
                    preCalculatedProcedureTables.TryAdd(tableName, false);
                }
            }
            bool value = false;
            preCalculatedProcedureTables.TryGetValue(tableName, out value);
            bool result = true;
            if (!value)
            {
                result = false;
            }
            return result;
        }

        private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
        public DBProvider Provider { get; private set; }
        private SqlConnection _connection { get; set; }
        private SqlConnection ConnectionBackup
        {
            get
            {
                SqlConnection con = new SqlConnection(GetConnectionString(Provider));
                con.Open();
                return con;

            }
        }

        public SqlConnection Connection
        {
            get
            {

                if (_connection == null)
                {
                    _connection = new SqlConnection(GetConnectionString(Provider));
                }
                int i = 0;
                string err = String.Empty;
                while (_connection.State != ConnectionState.Open)
                {
                    try
                    {
                        _connection.Open();
                    }
                    catch (Exception ex)
                    {
                        i++;
                        err = (ex.Message);
                    }
                    if (i > 6)
                    {
                        logger.Error(err);
                        break;
                    }
                }
  
                return _connection;
            }
        }


        public DataAccessDE(DBProvider provider)
        {
            Provider = provider;
        }
        public void Dispose()
        {
            if (_connection != null) { if ( _connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }
                _connection.Dispose();
            }
        }
        public IDataReader ExecuteDataReader(CommandType _commandType, string _commandText, out SqlCommand _command)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = commandTimeout;
            IDataReader reader;
            _command = cmd;
            try
            {
                cmd.Connection = Connection;
                cmd.CommandText = _commandText;
                cmd.CommandType = _commandType;
                reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message));
                }
                catch { }
                if (cmd != null)
                    cmd.Dispose();
            }
            return null;
        }
        public DataTable ExecuteDataTable(CommandType _commandType, string _commandText)
        {
            DataTable result = null;
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.CommandType = _commandType;
                    cmd.CommandText = _commandText;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(result);
                    }
                    cmd.Parameters.Clear();
                }
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message));
                }
                catch { }
                result = null;
            }
            return null;
        }
        public object ExecuteScalar(DBProvider _dbprovider, CommandType _commandType, string _commandText)
        {
            object result = null;
            SqlTransaction tran = Connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    
                    cmd.CommandTimeout = commandTimeout;
                    cmd.Transaction = tran;
                    cmd.CommandText = _commandText;
                    cmd.Connection = Connection;
                    cmd.CommandType = _commandType;
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message));
                }
                catch { }
                tran.Rollback();
            }
            return null;
        }
        public object ExecuteNonQuery(CommandType _commandType, string _commandText)
        {
            object result = null;
            SqlTransaction tran = Connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.Transaction = tran;
                    cmd.CommandText = _commandText;
                    cmd.Connection = Connection;
                    cmd.CommandType = _commandType;
                    result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message));
                }
                catch { }
                tran.Rollback();
            }
            return null;
        }
        public IDataReader ExecuteDataReader(CommandType _commandType, string _commandText, DbParameter[] _commandParameters, out SqlCommand _command)
        {
            SqlCommand cmd = new SqlCommand();
            IDataReader reader;
            _command = cmd;
            try
            {
                cmd.CommandTimeout = commandTimeout;
                cmd.Connection = Connection;
                cmd.CommandText = _commandText;
                cmd.CommandType = _commandType;
                cmd.Parameters.AddRange(_commandParameters);
                reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message, paramsToString(_commandParameters)));
                }
                catch { }
                if (cmd != null)
                    cmd.Dispose();
            }
            return null;
        }
        public DataTable ExecuteDataTable(CommandType _commandType, string _commandText, DbParameter[] _commandParameters)
        {
            DataTable result = new DataTable();
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.CommandType = _commandType;
                    cmd.CommandText = _commandText;
                    cmd.Parameters.AddRange(_commandParameters);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(result);
                    }
                    cmd.Parameters.Clear();
                }
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message, paramsToString(_commandParameters)));
                }
                catch { }
                result = null;
            }
            return null;
        }
        public object ExecuteScalar(CommandType _commandType, string _commandText, DbParameter[] _commandParameters)
        {
            object result = null;
            SqlTransaction tran = Connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.Transaction = tran;
                    cmd.CommandText = _commandText;
                    cmd.Connection = Connection;
                    cmd.CommandType = _commandType;
                    cmd.Parameters.AddRange(_commandParameters);
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message, paramsToString(_commandParameters)));
                }
                catch { }
                tran.Rollback();
            }
            return null;
        }
        public object ExecuteNonQuery(CommandType _commandType, string _commandText, DbParameter[] _commandParameters)
        {
            object result = null;
            SqlTransaction tran = Connection.BeginTransaction();
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.CommandTimeout = 9000000;
                    cmd.Transaction = tran;
                    cmd.CommandText = _commandText;
                    cmd.Connection = Connection;
                    cmd.CommandType = _commandType;
                    cmd.Parameters.AddRange(_commandParameters);
                    result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(_commandText, ":", ex.Message, paramsToString(_commandParameters)));
                }
                catch { }
                tran.Rollback();
            }
            return null;
        }
        public DataTable ExecuteDataTableSqlStatement(DBProvider _dbprovider, string sqlText, List<Parametro> paramList)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            if (paramList != null)
            {
                foreach (Parametro p in paramList)
                {
                    parameters.Add(new SqlParameter(p.ParameterName, p.Value));
                }
            }
            DataTable result = new DataTable();
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters.ToArray());
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(result);
                    }
                    cmd.Parameters.Clear();
                }
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(sqlText, ":", ex.Message, paramsToString(parameters.ToArray())));
                }
                catch { }
                result = null;
            }
            return null;
        }
        public DataTable ExecuteDataTableStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            if (paramList != null)
            {
                foreach (Parametro p in paramList)
                {
                    parameters.Add(new SqlParameter(p.ParameterName, p.Value));
                }
            }
            DataTable result = new DataTable();
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (!CanTakeDataFromPreCalculatedTables(nomeStoredProcedure, paramList))
                    {
                        cmd.CommandText = nomeStoredProcedure;
                    }
                    else
                    {
                        cmd.CommandText = ConvertProcedureToTableStatement(paramList, nomeStoredProcedure);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(result);
                    }
                    cmd.Parameters.Clear();
                }
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(nomeStoredProcedure, ":", ex.Message, paramsToString(parameters.ToArray())));
                }
                catch { }
                result = null;
            }
            return null;
        }
        public object ExecuteScalarStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            object result = null;
            List<SqlParameter> parameters = new List<SqlParameter>();
            if (paramList != null)
            {
                foreach (Parametro p in paramList)
                {
                    parameters.Add(new SqlParameter(p.ParameterName, p.Value));
                }
            }
            try
            {
                using (SqlCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandTimeout = commandTimeout;
                    cmd.Connection = Connection;
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (!CanTakeDataFromPreCalculatedTables(nomeStoredProcedure, paramList))
                    {
                        cmd.CommandText = nomeStoredProcedure;
                    }
                    else
                    {
                        cmd.CommandText = ConvertProcedureToTableStatement(paramList, nomeStoredProcedure);
                    }
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                return result;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(nomeStoredProcedure, ":", ex.Message, paramsToString(parameters.ToArray())));
                }
                catch { }
            }
            return null;
        }
        public IDataReader ExecuteDataReaderStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            SqlCommand cmd = new SqlCommand();
            IDataReader reader;
            List<SqlParameter> parameters = new List<SqlParameter>();
            if (paramList != null)
            {
                foreach (Parametro p in paramList)
                {
                    parameters.Add(new SqlParameter(p.ParameterName, p.Value));
                }
            }
            try
            {
                cmd.CommandTimeout = commandTimeout;
                cmd.Connection = Connection;
                cmd.Parameters.AddRange(parameters.ToArray());
                cmd.CommandType = CommandType.StoredProcedure;
                if (!CanTakeDataFromPreCalculatedTables(nomeStoredProcedure, paramList))
                {
                    cmd.CommandText = nomeStoredProcedure;
                }
                else
                {
                    cmd.CommandText = ConvertProcedureToTableStatement(paramList, nomeStoredProcedure);
                }
                reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                try { 
                logger.Error(String.Concat(nomeStoredProcedure, ":", ex.Message, paramsToString(parameters.ToArray())));
                }
                catch { }
                if (cmd != null)
                    cmd.Dispose();
            }
            return null;
        }
        public int ExecuteNonQueryStoredProcedureRoot(SqlConnection _myConnection, DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            SqlCommand cmd = new SqlCommand();
            List<SqlParameter> parameters = new List<SqlParameter>();
            int result = 0;
            if (paramList != null)
            {
                foreach (Parametro p in paramList)
                {
                    parameters.Add(new SqlParameter(p.ParameterName, p.Value));
                }
            }

            try
            {
                cmd.CommandTimeout = commandTimeout;
                cmd.Connection = _myConnection;
                cmd.CommandType = CommandType.StoredProcedure;
                if (!CanTakeDataFromPreCalculatedTables(nomeStoredProcedure, paramList))
                {
                    cmd.CommandText = nomeStoredProcedure;
                }
                else
                {
                    cmd.CommandText = ConvertProcedureToTableStatement(paramList, nomeStoredProcedure);
                }
                if(parameters.Count()>0)
                    cmd.Parameters.AddRange(parameters.ToArray());
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                try
                {
                    logger.Error(String.Concat(nomeStoredProcedure, ":", ex.Message, paramsToString(parameters.ToArray())));
                }
                catch { }
                if (cmd != null)
                    cmd.Dispose();
            }
            return result;
        }
        public int ExecuteNonQueryStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            return ExecuteNonQueryStoredProcedureRoot(Connection, _dbprovider, nomeStoredProcedure, paramList);
        }
        public int ExecuteNonQueryStoredProcedureBackupConnection(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            return ExecuteNonQueryStoredProcedureRoot(ConnectionBackup, _dbprovider, nomeStoredProcedure, paramList);
        }
        private string paramsToString(DbParameter[] par)
        {
            string res = String.Empty;
            foreach (var p in par)
            {
                res += String.Concat(p.ParameterName, ":", p.Value, ";");
            }
            return res;
        }
        private static string GetConnectionString(DBProvider _dbprovider)
        {
            switch (_dbprovider)
            {
                case DBProvider.SqlServer:
                    return WebConfigParameter.getConnectionString("SqlServerConnection");
                case DBProvider.SqlServerStampeC6:
                    return WebConfigParameter.getConnectionString("SqlServerStampeC6Connection");
                case DBProvider.SqlServerConsulenzaBase:
                    return WebConfigParameter.getConnectionString("SqlServerConnectionConsulenzaBase");
                case DBProvider.SqlServerConsulenzaUnica:
                    return WebConfigParameter.getConnectionString("SqlServerConsulenzaUnicaConnection");
                case DBProvider.SqlServerConsulenzaEvoluta:
                    return WebConfigParameter.getConnectionString("SqlServerConsulenzaEvolutaConnection");
                case DBProvider.SqlServerCatalogoProdotti:
                    return WebConfigParameter.getConnectionString("SqlServerCatalogoProdottiConnection");
                case DBProvider.SqlServerReportModeler:
                    return WebConfigParameter.getConnectionString("SqlServerConnectionReportModeler");
                case DBProvider.SqlServerWebTemplateReportModeler:
                    return WebConfigParameter.getConnectionString("SqlServerConnectionWebTemplateReportModeler");
                case DBProvider.SqlServerReportArchive:
                    return WebConfigParameter.getConnectionString("SqlServerConnectionReportArchive");
                case DBProvider.Oracle:
                    return WebConfigParameter.getConnectionString("OracleConnection");
                default:
                    return "";
            }
        }
        private string ConvertProcedureToTableStatement(List<Parametro> listaParametri, string storedProcedure)
        {
            return "wh.p_" + storedProcedure.Substring(storedProcedure.IndexOf('.') + 1, storedProcedure.Length - storedProcedure.IndexOf('.') - 1).ToLower().Replace("[", "").Replace("]", "");
        }
    }
}