using System;
using System.Data.OleDb;
using System.Data;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using System.Collections.Generic;

namespace DataLibrary
{
    public class Sta
    {
        public static bool IS_MYSQL_MODE = false;
        public static String SQL_PARAM_PREFIX =( IS_MYSQL_MODE?"@":":");
        private String url;
        // ORACLE
        private IDbConnection con;
        private IDbCommand cmd;
        private IDbTransaction tran;
        private OracleParameterCollection param2;
        private List<OracleParameter> param;
        private MySql.Data.MySqlClient.MySqlParameterCollection param2_;
        private List<MySql.Data.MySqlClient.MySqlParameter> param_;

        private DbType dbType;

        public DbType DbType
        {
            get { return this.dbType; }
        }

        private bool isTran;

        public Sta(int conIndex, String gcoopPath)
        {
            XmlConfigService xmlc = new XmlConfigService(gcoopPath);
            XmlService x = new XmlService(gcoopPath);
            string conStr = x.GetConnectionString(conIndex);
            this.SecoundConstructor(conStr);
        }

        public Sta(String connectionString)
        {
            this.SecoundConstructor(connectionString);
        }

        public void ResetParameter2()
        {
            if (Sta.IS_MYSQL_MODE)
                param2_ = new MySqlCommand().Parameters;
            else
                param2 = new OracleParameterCollection();
        }

        public void AddParameter(string name, object value)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                if (param2_ == null)
                {
                    ResetParameter2();
                }
                MySqlParameter p = new MySqlParameter(name==""?"":Sta.SQL_PARAM_PREFIX + name, value);
                param2_.Add(p);
            }
            else
            {
                if (param2 == null)
                {
                    ResetParameter2();
                }
                OracleParameter p = new OracleParameter(name, value);
                param2.Add(p);
            }
        }

        //---------------------------------------------------------------

        private void SecoundConstructor(String connectionString)
        {
            Sta.IS_MYSQL_MODE = false;
            Sta.SQL_PARAM_PREFIX = (IS_MYSQL_MODE ? "@" : ":");
            this.url = connectionString;
            if (this.url.ToLower().IndexOf("Provider=MSDAORA;".ToLower()) >= 0)
            {
                this.dbType = DbType.OleDb;
            }
            else if (this.url.ToLower().IndexOf("Server=".ToLower()) >= 0)
            {
                this.dbType = DbType.MySQL;
                Sta.IS_MYSQL_MODE = true;
                Sta.SQL_PARAM_PREFIX = (IS_MYSQL_MODE ? "@" : ":");
            }
            else
            {
                this.dbType = DbType.Oracle;
            }
            //this.SecoundConstructor();

            try
            {
                if (dbType == DbType.Oracle)
                {
                    con = new OracleConnection(url);
                }
                else if (dbType == DbType.OleDb)
                {
                    con = new OleDbConnection(url);
                }
                else if (dbType == DbType.MySQL)
                {
                    con = new MySqlConnection(url);
                }
                cmd = con.CreateCommand();
                con.Open();
            }
            catch
            {
                try
                {
                    con.Close();
                }
                catch { }
                throw new StaConnectException();
            }
        }

        //---------------------------------------------------------------

        public Sdt Query(String sql)
        {
            sql = replaceOracle2Mysql(sql);
            Sdt dt = new Sdt();
            cmd.CommandText = sql;
            if (dbType == DbType.MySQL)
            {
                MySqlDataAdapter da = new MySqlDataAdapter((MySqlCommand)cmd);
                da.Fill(dt);
                dt.SecoundConstructor();
            }
            else if (dbType == DbType.Oracle)
            {
                OracleDataAdapter da = new OracleDataAdapter((OracleCommand)cmd);
                da.Fill(dt);
                dt.SecoundConstructor();
            }
            else if (dbType == DbType.OleDb)
            {
                OleDbDataAdapter da = new OleDbDataAdapter((OleDbCommand)cmd);
                da.Fill(dt);
                dt.SecoundConstructor();
            }
            else
            {
                return null;
            }
            return dt;
        }

        //---------------------------------------------------------------

        public DataTable QueryDataTable(String sql)
        {
            sql = replaceOracle2Mysql(sql);
            DataTable dt = new DataTable();
            cmd.CommandText = sql;
            if (dbType == DbType.MySQL)
            {
                MySqlDataAdapter da = new MySqlDataAdapter((MySqlCommand)cmd);
                da.Fill(dt);
            }
            else if (dbType == DbType.Oracle)
            {
                OracleDataAdapter da = new OracleDataAdapter((OracleCommand)cmd);
                da.Fill(dt);
            }
            else if (dbType == DbType.OleDb)
            {
                OleDbDataAdapter da = new OleDbDataAdapter((OleDbCommand)cmd);
                da.Fill(dt);
            }
            else
            {
                return null;
            }
            return dt;
        }

        //---------------------------------------------------------------

        public string replaceOracle2Mysql(String sql)
        {

            if (dbType == DbType.MySQL)
            {
                sql = sql.Replace("sysdate", "NOW()");
                sql = sql.Replace("SYSDATE", "NOW()");
                sql = sql.Replace("\\", "\\\\");
                sql = sql.Replace("to_date(", "str_to_date(");
                sql = sql.Replace("TO_DATE(", "STR_TO_DATE(");
                sql = sql.Replace("yyyy-mm-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.Replace("yyyy-MM-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.Replace("yyyy/mm/dd hh24:mi:ss", "%Y/%m/%d %T");
                sql = sql.Replace("yyyy/MM/dd hh24:mi:ss", "%Y/%m/%d %T");
                sql = sql.Replace("yyyy-mm-dd", "%Y-%m-%d");
                sql = sql.Replace("yyyy-MM-dd", "%Y-%m-%d");
                sql = sql.Replace("yyyy/mm/dd", "%Y/%m/%d");
                sql = sql.Replace("yyyy/MM/dd", "%Y/%m/%d");
                sql = sql.Replace("yyyyMMdd", "%Y%m%d");
                sql = sql.Replace("ddmmyyyy", "%d%m%Y");
                sql = sql.Replace("ddMMyyyy", "%d%m%Y");
            }

            return sql;
        }

        public int Exe(String sql, bool isParam = false)
        {
            sql = replaceOracle2Mysql(sql);
            cmd.CommandText = sql;
            if (isParam)
            {
                cmd.Parameters.Clear();
                if (Sta.IS_MYSQL_MODE)
                {
                    if (param2_ != null)
                    {
                        MySql.Data.MySqlClient.MySqlCommand cmdo = (MySql.Data.MySqlClient.MySqlCommand)cmd;
                        //cmd.Parameters.Add(param);
                        for (int i = 0; i < param2_.Count; i++)
                        {
                            cmdo.Parameters.Add(param2_[i].ParameterName==""?"":Sta.SQL_PARAM_PREFIX + param2_[i].ParameterName, param2_[i].Value);
                        }
                    }
                }
                else
                {
                    if (param2 != null)
                    {
                        OracleCommand cmdo = (OracleCommand)cmd;
                        //cmd.Parameters.Add(param);
                        for (int i = 0; i < param2.Count; i++)
                        {
                            cmdo.Parameters.Add(param2[i].ParameterName, param2[i].Value);
                        }
                    }
                }
            }
            return cmd.ExecuteNonQuery();
        }

        //หนุ่มน้อย เพิ่มฟังก์ชัน Execute Stored Procedure
        public void Exec(String exec_command)
        {
            exec_command = replaceOracle2Mysql(exec_command);
            cmd.Parameters.Clear();
            if (Sta.IS_MYSQL_MODE)
            {
                if (param2_ != null)
                {
                    MySql.Data.MySqlClient.MySqlCommand cmdo = (MySql.Data.MySqlClient.MySqlCommand)cmd;
                    //cmd.Parameters.Add(param);
                    for (int i = 0; i < param2_.Count; i++)
                    {
                        cmdo.Parameters.Add(param2_[i].ParameterName, param2_[i].Value);
                    }
                }
            }
            else
            {
                if (param2 != null)
                {
                    OracleCommand cmdo = (OracleCommand)cmd;
                    //cmd.Parameters.Add(param);
                    for (int i = 0; i < param2.Count; i++)
                    {
                        cmdo.Parameters.Add(param2[i].ParameterName, param2[i].Value);
                    }
                }
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = exec_command;
            cmd.ExecuteNonQuery();
        }

        //---------------------------------------------------------------

        public void ExePlSql(String exec_command)
        {
            cmd.Parameters.Clear();
            if (Sta.IS_MYSQL_MODE)
            {
                if (param_ != null)
                {
                    MySql.Data.MySqlClient.MySqlCommand cmdo = (MySql.Data.MySqlClient.MySqlCommand)cmd;
                    for (int i = 0; i < param_.Count; i++)
                    {
                        cmdo.Parameters.Add(param_[i]);
                    }
                }
            }
            else
            {
                if (param != null)
                {
                    OracleCommand cmdo = (OracleCommand)cmd;
                    for (int i = 0; i < param.Count; i++)
                    {
                        cmdo.Parameters.Add(param[i]);
                    }
                }
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = exec_command;
            cmd.ExecuteNonQuery();
        }

        public void ResetParameter()
        {
            if (Sta.IS_MYSQL_MODE)
                param_ = new List<MySql.Data.MySqlClient.MySqlParameter>();
            else
                param = new List<OracleParameter>();
        }

        public void AddInParameter(string name, string value)
        {

            if (Sta.IS_MYSQL_MODE){
            
                if (param_ == null)
                {
                    ResetParameter();
                }
                MySql.Data.MySqlClient.MySqlParameter p = new MySql.Data.MySqlClient.MySqlParameter(name, value);
                param_.Add(p);
            }
            else
            {
                if (param == null)
                {
                    ResetParameter();
                }
                OracleParameter p = new OracleParameter(name, value);
                param.Add(p);
            }
        }

        public void AddInParameter(string name, object value, OracleType oraType)
        {
            if (param == null)
            {
                ResetParameter();
            }
            OracleParameter p = new OracleParameter(name, oraType);
            p.Value = value;
            param.Add(p);
        }

        public void AddInOutParameter(string name, object value, OracleType oraType)
        {
            if (param == null)
            {
                ResetParameter();
            }
            OracleParameter p = new OracleParameter(name, oraType);
            p.Value = value;
            p.Direction = ParameterDirection.InputOutput;
            if (oraType == OracleType.VarChar)
            {
                p.Size = 999;
            }
            else if (oraType == OracleType.Char)
            {
                p.Size = 30;
            }
            param.Add(p);
        }

        public void AddOutParameter(string name, OracleType oraType)
        {
            if (param == null)
            {
                ResetParameter();
            }
            OracleParameter p = new OracleParameter(name, oraType);
            p.Direction = ParameterDirection.Output;
            if (oraType == OracleType.VarChar)
            {
                p.Size = 999;
            }
            else if (oraType == OracleType.Char)
            {
                p.Size = 30;
            }
            param.Add(p);
        }

        public void AddReturnParameter(string name, OracleType oraType)
        {
            if (param == null)
            {
                ResetParameter();
            }
            OracleParameter p = new OracleParameter(name, oraType);
            p.Direction = ParameterDirection.ReturnValue;
            if (oraType == OracleType.VarChar)
            {
                p.Size = 999;
            }
            else if (oraType == OracleType.Char)
            {
                p.Size = 30;
            }
            param.Add(p);
        }

        public object OutParameter(int index)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                MySql.Data.MySqlClient.MySqlParameter p = param_[index];
                return p.Value;
            }
            else
            {
                OracleParameter p = param[index];
                return p.Value;
            }
        }

        public object OutParameter(string paramName)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                for (int i = 0; i < param_.Count; i++)
                {
                    if (paramName.ToLower() == param_[i].ParameterName.ToLower())
                    {
                        return OutParameter(i);
                    }
                }
            }
            else
            {
                for (int i = 0; i < param.Count; i++)
                {
                    if (paramName.ToLower() == param[i].ParameterName.ToLower())
                    {
                        return OutParameter(i);
                    }
                }
            }
            return null;
        }

        //---------------------------------------------------------------

        public bool Transection()
        {
            try
            {
                tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = tran;
                isTran = true;
            }
            catch
            {
                isTran = false;
            }
            return isTran;
        }

        //---------------------------------------------------------------

        public void RollBack()
        {
            tran.Rollback();
        }

        //---------------------------------------------------------------

        public void RollBack(bool close)
        {
            RollBack();
            if (close) Close();
        }

        //---------------------------------------------------------------

        public void Commit()
        {
            tran.Commit();
        }

        //---------------------------------------------------------------

        public void Commit(bool close)
        {
            Commit();
            if (close) Close();
        }

        //---------------------------------------------------------------

        public void Close()
        {
            try
            {
                con.Close();
            }
            catch { }
        }
    }
}