using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using System.Globalization;


namespace CheckSheet
{
    public class FcSql
    {

        private SqlConnection oleconn;
        private string v;

        public FcSql(string v, SqlConnection oleconn)
        {
            this.v = v;
            this.oleconn = oleconn;
        }

        public class Function : IDisposable
        {
            void IDisposable.Dispose() { }
            public void Dispose() { }


            //private OracleConnection orclconn;
            //private OracleCommand orclcomm = null;
            //private OracleDataAdapter orclda = null;
            //private OracleDataReader orcldr = null;

            private SqlConnection oleconn;
            private SqlCommand olecomm = null;
            private SqlDataAdapter oleda = null;
            private SqlDataReader oledr = null;

            private DataSet ds = null;
            private DataTable dt = null;

            public void closeconnection()
            {

                oleconn.Close();
                oleconn.Dispose();
                olecomm.Dispose();

            }
            public string EvolutionConnect()
            {

                string ConString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

                //string ConString = string.Format(GetXML("ConnectionString"));
                //string deConString = string.Empty;
                //string strEncriptPassword = string.Empty;

                //try
                //{
                //    classEncriptDecript EncryptDecrypt = new classEncriptDecript("Piriy@P@ssw0rd1234567890");
                //    deConString = EncryptDecrypt.Decrypt(ConString);
                //}
                //catch { };

                //string deConString = string.Format(@"Data Source=PAIROJ\SQLEXPRESS;Initial Catalog=scrap-web;User ID=sa;Password=piriy@123;Integrated Security=True;");
                oleconn = new SqlConnection(ConString);

                try
                {
                    oleconn.Open();

                }
                catch (SqlException oleEx)
                {
                    ConString = oleEx.Message;
                };




                return ConString;


            }
            public void Connect()
            {
                string ConString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                //string ConString = string.Format(GetXML("ConnectionString"));
                //string deConString = string.Empty;
                //string strEncriptPassword = string.Empty;

                //try
                //{
                //    classEncriptDecript EncryptDecrypt = new classEncriptDecript("Piriy@P@ssw0rd1234567890");
                //    deConString = EncryptDecrypt.Decrypt(ConString);
                //}
                //catch { };

                //string deConString = string.Format(@"Data Source=PAIROJ\SQLEXPRESS;Initial Catalog=scrap-web;User ID=sa;Password=piriy@123;Integrated Security=True;");

                oleconn = new SqlConnection(ConString);

                try
                {
                    oleconn.Open();

                }
                catch (SqlException oleEx)
                {

                };



            }
            public void Log_Error(string cmd)
            {
                using(FcSql.Function fc=new Function())
                {
                    try
                    {
                        string sqlerror = string.Format("insert into mst_ErrorLog (error_msg_sql, error_log_datetime) values ('{0}','{1}')", cmd, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US")));
                        string cmdcommand = fc.ExecSQLStatement(sqlerror);
                    }
                    catch { };

                    fc.closeconnection();



                }

            }
            public string ExecSQLStatement(string sqlString)
            {

                string status = string.Empty;
                Connect();


                try
                {
                    olecomm = new SqlCommand(sqlString.ToString(), oleconn);
                    olecomm.ExecuteNonQuery();

                }
                catch (SqlException oleEx)
                {
                    status = oleEx.Message;
                    Log_Error(status.Replace("'",""));

                };
                oleconn.Close();
                olecomm.Dispose();



                return status;
                //orclconn.Dispose();


            }
            public DataSet SQL_DataSet(string sqlString)
            {

                Connect();
                try
                {
                    olecomm = new SqlCommand(sqlString.ToString(), oleconn);
                    oleda = new SqlDataAdapter(olecomm);


                    ds = new DataSet();
                    try
                    {
                        oleda.Fill(ds);
                    }
                    catch { }




                }
                catch (SqlException oleEx)
                {

                    Log_Error(oleEx.Message.ToString().Replace("'", ""));

                };
                oleconn.Close();
                olecomm.Dispose();
                oleda.Dispose();

                return ds;
                //orclconn.Dispose();
                ds.Dispose();


            }
            public DataTable SQL_DataTable(string sqlString)
            {
                Connect();

                if (sqlString.Length > 0)
                {
                    try
                    {
                        olecomm = new SqlCommand(sqlString.ToString(), oleconn);

                        oleda = new SqlDataAdapter(olecomm);

                        dt = new DataTable();
                        oleda.Fill(dt);



                    }
                    catch (SqlException oleEx)
                    {
                        Log_Error(oleEx.Message.ToString().Replace("'", ""));

                    };
                    oleconn.Close();
                    olecomm.Dispose();
                    oleda.Dispose();

                }

                return dt;
                //orclconn.Dispose();
                dt.Dispose();



            }
            public DataTable SQL_DataReader(string sqlString)
            {
                if (sqlString == "")
                {
                    //MessageBox.Show("ERROR" + ":" + sqlString);
                }
                Connect();
                try
                {
                    dt = new DataTable();
                    if (oleconn.State.ToString().ToLower() == "close")
                    {
                        oleconn.Open();
                    }

                    olecomm = new SqlCommand(sqlString.ToString(), oleconn);

                    oledr = olecomm.ExecuteReader();

                    if (oledr.HasRows)
                    {
                        dt.Load(oledr);
                    }



                }
                catch (SqlException oleEx)
                {
                    Log_Error(oleEx.Message.ToString().Replace("'", ""));
                };

                //olecomm.Dispose();
                oleconn.Close();
                //oledr.Dispose();



                return dt;
                //orclconn.Dispose();
                dt.Dispose();


            }
            public Byte[] SQL_ExecuteScalar(string sqlString)
            {
                byte[] b = null;
                Connect();
                try
                {
                    dt = new DataTable();

                    olecomm = new SqlCommand(sqlString.ToString(), oleconn);

                    b = (byte[])olecomm.ExecuteScalar();


                }

                catch (SqlException oleEx)
                {
                    Log_Error(oleEx.Message.ToString().Replace("'", ""));

                };

                oleconn.Close();
                olecomm.Dispose();



                return b;
                //orclconn.Dispose();
                dt.Dispose();

            }
            public string GetColumn(string databasename, string tableName)
            {
                string query = string.Empty;
                switch (GetXML("DatabaseType"))
                {
                    case "MySql":
                        {
                            query = string.Format("use {0}; select top 1 * from {1} ", databasename, tableName);
                            break;
                        }
                    case "SqlServer":
                        {

                            query = string.Format("use {0}; select top 1 * from {0}.dbo.{1} ", databasename, tableName);
                            break;
                        }
                }



                return query;


            }
            public string GetColumnStr(string tableName)
            {

                string query = string.Empty;
                query = string.Format("select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from INFORMATION_SCHEMA.COLUMNS where [Table_Name] ='{0}' ", tableName);
                return query;



            }
            public string GetTableName(string _database)
            {
                string query = string.Empty;
                switch (GetXML("DatabaseType"))
                {
                    case "MySql":
                        {
                            query = string.Format("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema =  '{0}'", _database);
                            break;
                        }
                    case "SqlServer":
                        {

                            query = string.Format("use {0}; select [Table_Name] from information_schema.tables where [Table_Name] not like 'sys%' and  [Table_Name] not like 'dt%' and  [Table_Name] not like 'cdo%'  and  [Table_Name] not like '%view%' order by [Table_Name]", _database);
                            break;
                        }
                }

                return query;

            }
            public string GetColumnFilter(string strCommand)
            {

                string query = string.Empty;
                string qReplace = strCommand.ToLower().Replace("select", "");
                query = "select top 1 " + qReplace;
                return query;


            }


            //private string url = ConfigurationSettings.AppSettings["strUrlMain"];
            public static string url = @"C://CCRMs/Web.config";
            //private string url = System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\App.config";
            public void WriteSetting(string key, string value)
            {
                // load config document for current assembly
                XmlDocument doc = loadConfigDocument();

                // retrieve appSettings node
                XmlNode node = doc.SelectSingleNode("//connectionStrings");

                if (node == null)
                    throw new InvalidOperationException("appSettings section not found in config file.");

                try
                {
                    // select the 'add' element that contains the kzzzxcey
                    XmlElement elem = (XmlElement)node.SelectSingleNode(string.Format("//add[@key='{0}']", key));

                    if (elem != null)
                    {
                        // add value for key
                        elem.SetAttribute("value", value);
                    }
                    else
                    {
                        // key was not found so create the 'add' element 
                        // and set it's key/value attributes 
                        elem = doc.CreateElement("add");
                        elem.SetAttribute("key", key);
                        elem.SetAttribute("value", value);
                        node.AppendChild(elem);
                    }
                    doc.Save(url);
                }
                catch
                {
                    throw;
                }
            }
            public XmlDocument loadConfigDocument()
            {
                XmlDocument doc = null;
                try
                {
                    doc = new XmlDocument();
                    doc.Load(url);
                    return doc;
                }
                catch (System.IO.FileNotFoundException e)
                {
                    throw new Exception("No configuration file found.", e);
                }
            }
            public string GetXML(string key)
            {
                //fDecript();
                XmlDocument doc = new XmlDocument();
                doc.Load(url);
                string returnValue = string.Empty;
                //get all the elements for the specified tag
                XmlNodeList nodeList = doc.GetElementsByTagName("connectionStrings");
                //loop through the document
                //for (int i = 0; i < nodeList.Count; i++)
                //{
                for (int i = 0; i < nodeList[0].ChildNodes.Count; i++)
                {
                    //check to see if we have a match
                    try
                    {
                        if (nodeList[0].ChildNodes[i].Attributes[0].Value == key)
                        {
                            returnValue = nodeList[0].ChildNodes[i].Attributes[1].Value;
                            //break;
                        }
                    }
                    catch { };

                }

                //}
                return returnValue;
            }
        }

        private void ExecuteNonQuery()
        {
            throw new NotImplementedException();
        }

        private void Dispose()
        {
            throw new NotImplementedException();
        }
    }

}