using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace DbAccess
{
    public class MySqlHelper : ISysSqlHelper
    {
        private string connectionString = "";
        public MySqlHelper()
        {
            connectionString = Encrypt.DecryptDES_DB(DbInfo.GetDataBaseInfo("Main"));
        }


        public MySqlHelper(string rdsType)
        {
            try
            {
                connectionString = DbInfo.GetDataBaseInfo(rdsType);
                connectionString = Encrypt.DecryptDES_DB(connectionString);
            }
            catch (Exception ex)
            {

            }

        }


        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.CommandText = SQLString;
                    cmd.CommandTimeout = 10000;
                    DataSet ds = new DataSet();
                    try
                    {
                        DateTime time = DateTime.Now;
                        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                        da.Fill(ds);
                    }
                    catch (MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Clone();
                        connection.Close();
                    }
                    return ds;
                }
            }
        }


        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string SQLString)
        {
            DateTime time = DateTime.Now;
            DataSet ds = ExecuteDataSet(SQLString);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 返回数据表(分页)
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strSql)
        {
            DateTime time = DateTime.Now;
            DataTable dt = new DataTable();
            //获取总记录数
            recordCount = Convert.ToInt32(this.ExecuteScalar("select count(1) from (" + strSql + ") as t"));
            StringBuilder sb = new StringBuilder();
            int beg = (page - 1) * pageSize;
            sb.Append(strSql + " limit " + beg.ToString() + "," + pageSize);
            dt = this.ExecuteDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 返回数据表(分页) add zhangjun  2014-12-19 优化大数据分页，计算总记录数的sql
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <param name="strSql"></param>
        /// <param name="tableName"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strSql, string tableName, string strWhere)
        {
            DateTime time = DateTime.Now;
            DataTable dt = new DataTable();
            //获取总记录数
            recordCount = Convert.ToInt32(this.ExecuteScalar("select count(*) from " + tableName + " " + strWhere));
            StringBuilder sb = new StringBuilder();
            int beg = (page - 1) * pageSize;
            sb.Append(strSql + " limit " + beg.ToString() + "," + pageSize);
            dt = this.ExecuteDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 返回查询的所有结果，突破rds的1万条的限制
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTableAll(string strSql)
        {
            DateTime time = DateTime.Now;
            int recordCount = Convert.ToInt32(this.ExecuteScalar("select count(1) from (" + strSql + ") as t"));
            //如果记录总数
            if (recordCount < 10001)
            {
                return this.ExecuteDataTable(strSql);
            }
            //如果超过1万条记录，分次从rds提取
            DataTable dt = new DataTable();
            int mi = recordCount % 10000;
            int pi = recordCount / 10000;
            if (mi > 0)
            {
                pi = pi + 1;
            }
            for (int i = 1; i < pi + 1; i++)
            {
                DataTable cdt = this.ExecuteDataTable(i, 10000, ref recordCount, strSql);
                if (dt.Rows.Count == 0)
                {
                    dt = cdt.Clone();
                }
                dt.Merge(cdt);
                dt.AcceptChanges();
            }
            return dt;
        }


        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        DateTime time = DateTime.Now;
                        connection.Open();
                        cmd.CommandTimeout = 10000;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySqlException E)
                    {
                        if (E.ToString().Contains("Deadlock"))
                        {
                            //WriteLine("物流签收锁定日志跟踪", E.ToString() + "_____" + SQLString);
                        }
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public object ExecuteScalar(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        DateTime time = DateTime.Now;

                        connection.Open();
                        cmd.CommandTimeout = 10000;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        cmd.Clone();
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.CommandTimeout = 10000;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (MySqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                    cmd.Dispose();
                }
            }
        }

        public string GetConnectString()
        {
            return connectionString;
        }
    }
}