using System;
using System.Web;
using System.Data;
using System.Data.Odbc;

namespace MySQL
{
    /// <summary>
    /// Acesso à DB
    /// </summary>
    public class sql
    {
        private OdbcConnection sqlConnection;
        private string sqlQuery;
        private string stringConnection;

        /// <summary>
        /// Construtor de acesso à DB
        /// </summary>
        /// <param name="sqlConn">Connection String</param>
        public sql(string sqlConn)
        {
            stringConnection = sqlConn;
        }

        /// <summary>
        /// Fecho de ligação DB
        /// </summary>
        public void closeConnection()
        {
            // FECHAR LIGAÇÃO À DB
            try
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
            catch (Exception) { }
        }

        /// <summary>
        /// Query SQL
        /// </summary>
        public string Query
        {
            get { return sqlQuery; }
            set { sqlQuery = value; }
        }

        /// <summary>
        /// Escrita da query na DB
        /// </summary>
        /// <returns>True ou false</returns>
        public bool Write()
        {
            bool escrita = false;
            using (OdbcConnection sqlConnectionWrite = new OdbcConnection(stringConnection))
            {
                try { sqlConnectionWrite.Open(); }
                catch { return false; }

                using (OdbcCommand sqlCommand = new OdbcCommand(sqlQuery, sqlConnectionWrite))
                {
                    try
                    {
                        sqlCommand.ExecuteNonQuery();
                        escrita = true;
                    }
                    catch (Exception ex)
                    {
                        RegistoErros(ex.Message);
                        escrita = false;
                    }
                }
            }
            return escrita;
        }

        /// <summary>
        /// Leitura da query na DB
        /// </summary>
        /// <returns>Toda a informação numa DataTable</returns>
        public DataTable Read()
        {
            DataTable dtResult = new DataTable();

            using (OdbcConnection sqlConnectionRead = new OdbcConnection(stringConnection))
            {
                try { sqlConnectionRead.Open(); }
                catch { return null; }

                using (OdbcCommand sqlCommand = new OdbcCommand(sqlQuery, sqlConnectionRead))
                {
                    using (OdbcDataAdapter sqlAdapter = new OdbcDataAdapter(sqlCommand))
                    {
                        try
                        {
                            sqlAdapter.Fill(dtResult);
                        }
                        catch (Exception ex)
                        {
                            RegistoErros(ex.Message);
                        }
                    }
                }
            }
            return dtResult;
        }

        /// <summary>
        /// Registo de erros
        /// </summary>
        private void RegistoErros(string Erro)
        {
            string query = "";

            try
            {
                // UTILIZADOR AUTENTICADO
                Intranet.Users intranetUser = (Intranet.Users)HttpContext.Current.Session["intranetUser"];

                query = "INSERT INTO sys_errosql(createuser, createdep, createdata, query, descricao) "
                      + "VALUES ( " + intranetUser.ID + ", "
                      + "         " + intranetUser.Departamento.ID + ", "
                      + "             now(), "
                      + "        '" + sqlQuery.Replace("'", "\"") + "', "
                      + "        '" + Erro.Replace("'", "`") + "')";
            }
            catch (Exception)
            {
                // UTILIZADOR ANÓNIMO
                query = "INSERT INTO sys_errosql(createuser, createdep, createdata, query, descricao) "
                      + "VALUES ( 0, "
                      + "         0, "
                      + "         now(), "
                      + "        '" + sqlQuery.Replace("'", "\"") + "', "
                      + "        '" + Erro.Replace("'", "`") + "')";
            }

            OdbcCommand sqlCommand = new OdbcCommand(query);

            sqlCommand.Connection = sqlConnection;

            try
            {
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            { }
        }
    }
}