using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Web;
using CY.Infrastructure.Configuration;
using CY.Infrastructure.Logging;
using CY.Infrastructure.Domain;
using CY.IBaseDAL;
namespace CY.SQLDAL
{
    /// <summary>
    /// 数据库交换类
    /// </summary>
    public class Database : IDisposable
    {
        #region 变量
        private SqlConnection _Connection;
        private SqlTransaction _Tran;
        private ILogger Log;
        #endregion

        #region 属性
        /// <summary>
        /// 数据库连接驱动器
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                try
                {
                    if (_Connection.State.Equals(ConnectionState.Closed))
                    {
                        _Connection.Open();
                    }
                    //if (ConnectionState.Open != _Connection.State)
                    //    _Connection.Open();
                    return _Connection;
                }
                catch (Exception)
                {
                    return _Connection;
                }
            }
            set
            {
                _Connection = value;
            }
        }
        /// <summary>
        /// 数据库事物
        /// </summary>
        public SqlTransaction SqlTransaction
        {
            get
            {
                return this._Tran;
            }
            set
            {
                this._Tran = value;
            }
        }
        /// <summary>
        /// 查询超时时间（毫秒，0为无限）
        /// </summary>
        public static int QueryTimeOut
        {
            get
            {
                return int.Parse(ConfigurationSetting.QueryTimeOut);
            }
        }
        #endregion

        #region 方法

        /// <summary>
        /// 创建一个新的默认数据库连接（不包含事务）
        /// </summary>
        public Database()
        {
            LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
            Log = LoggingFactory.GetLogger();
            this.Connection = new SqlConnection(ConfigurationSetting.SQLDB_ConnectionString);
            this.Open(false);
        }

        /// <summary>
        /// 创建一个新的默认数据库连接
        /// </summary>
        /// <param name="isBeginTran">是否包含事务</param>
        public Database(bool isBeginTran)
        {
            LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
            Log = LoggingFactory.GetLogger();
            this.Connection = new SqlConnection(ConfigurationSetting.SQLDB_ConnectionString);
            this.Open(isBeginTran);
        }

        /// <summary>
        /// 使用特定连接字符串
        /// </summary>
        /// <param name="ConnectionString">连接字符串</param>
        public Database(string ConnectionString)
        {
            LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
            Log = LoggingFactory.GetLogger();
            this.Connection = new SqlConnection(ConnectionString);
            this.Open(false);
        }

        /// <summary>
        /// 数据库开连接
        /// </summary>
        /// <param name="isBeginTran">是否开启事务</param>
        private void Open(bool isBeginTran)
        {
            if (this.Connection.State != System.Data.ConnectionState.Open)
            {
                this.Connection.Open();
            }
            if (isBeginTran)
            {
                this._Tran = this.Connection.BeginTransaction();
            }
        }

        /// <summary>
        /// 数据库关连接
        /// </summary>
        private void Close()
        {
            if (this._Tran != null)
            {
                this._Tran.Commit();
            }
            if (_Connection.State.Equals(ConnectionState.Open))
            {
                this.Connection.Close();
            }
        }

        /// <summary>
        /// 无返回查询分析
        /// </summary>
        /// <param name="cmdText">Transaction-SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">SQL参数</param>
        public void Query(string cmdText, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand command = new SqlCommand(cmdText, this.Connection);
            command.CommandType = cmdType;
            if (this._Tran != null)
            {
                command.Transaction = this._Tran;
            }
            command.CommandTimeout = Database.QueryTimeOut;
            command.Parameters.Clear();
            if (param != null)
            {
                //command.Parameters.AddRange(param);

                foreach (SqlParameter p in param)
                {
                    command.Parameters.Add(p);
                }
            }
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SqlException sqlEx = ex as SqlException;
                if (null != sqlEx)
                {
                    Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
                }
                throw ex;
            }
            finally
            {
                Close();
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="cmdText">Transaction-SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">SQL参数</param>
        /// <returns>DataTable数据集</returns>
        public DataTable QueryDataTable(string cmdText, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand command = new SqlCommand(cmdText, this.Connection);
            command.CommandType = cmdType;
            if (this._Tran != null)
            {
                command.Transaction = this._Tran;
            }
            command.CommandTimeout = Database.QueryTimeOut;
            command.Parameters.Clear();
            if (param != null)
            {
                foreach (SqlParameter sp in param)
                {
                    command.Parameters.Add(sp);
                }
            }
            SqlDataAdapter sda = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            try
            {
                sda.Fill(dt);
            }
            catch (Exception ex)
            {
                SqlException sqlEx = ex as SqlException;
                if (null != sqlEx)
                {
                    Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
                }
            }
            finally
            {
                Close();
            }
            return dt;
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="cmdText">Transaction-SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">SQL参数</param>
        /// <returns>DataSet数据集</returns>
        public DataSet QueryDataSet(string cmdText, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand command = new SqlCommand(cmdText, this.Connection);
            command.CommandType = cmdType;
            if (this._Tran != null)
            {
                command.Transaction = this._Tran;
            }
            command.CommandTimeout = Database.QueryTimeOut;
            if (command.Parameters != null && command.Parameters.Count > 0)
                command.Parameters.Clear();
            if (param != null)
            {
                foreach (SqlParameter sp in param)
                {
                    command.Parameters.Add(sp);
                }
            }
            SqlDataAdapter sda = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                SqlException sqlEx = ex as SqlException;
                if (null != sqlEx)
                {
                    Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
                }
            }
            finally
            {
                Close();
            }
            return ds;
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="cmdText">Transaction-SQL语句</param>
        /// <param name="cmdType">语句类型</param>
        /// <param name="param">SQL参数</param>
        /// <returns>DataTable数据集</returns>
        public IDataReader QueryDataReader(string cmdText, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand command = new SqlCommand(cmdText, this.Connection);
            command.CommandType = cmdType;
            if (this._Tran != null)
            {
                command.Transaction = this._Tran;
            }
            command.CommandTimeout = Database.QueryTimeOut;
            command.Parameters.Clear();
            if (param != null)
            {
                command.Parameters.AddRange(param);

            }

            return command.ExecuteReader(CommandBehavior.CloseConnection);//释放Reader时关闭连接
        }


        /// <summary>
        /// 返回intid
        /// </summary>
        /// <param name="SQLString">Transaction-SQL语句</param>
        /// <param name="param">SQL参数</param>
        /// <returns>返回intid</returns>
        public int InsertDataAndGetId(string SQLString, params SqlParameter[] param)
        {
            int newRecordId = 0;
            
                SqlCommand cmd = new SqlCommand(SQLString + "; SELECT SCOPE_IDENTITY();", this.Connection);
            if (this._Tran != null)
            {
                cmd.Transaction = this._Tran;
            }
            cmd.CommandTimeout = Database.QueryTimeOut;
            cmd.Parameters.Clear();
            if (param != null)
            {
                cmd.Parameters.AddRange(param);

            }
            newRecordId = Convert.ToInt32(cmd.ExecuteScalar());
            
            return newRecordId;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, params SqlParameter[] param)
        {
            using (SqlCommand cmd = new SqlCommand(SQLString, this.Connection))
            {
                try
                {
                    if (this._Tran != null)
                    {
                        cmd.Transaction = this._Tran;
                    }

                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    cmd.CommandTimeout = Database.QueryTimeOut;
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    Close();
                }
            }

        }

        #region 泛型扩展方法

        /// <summary>
        /// 根据编号集合批量删除数据
        /// </summary>
        /// <typeparam name="T">实现IAggregateRoot接口的可实例化类</typeparam>
        /// <param name="opear">操作对象</param>
        /// <param name="ids">编号集合</param>
        /// <returns>true:成功,false:失败</returns>
        public static bool DeleteDataByIds<T>(ICommonDAL operatorDal, string currentOperator, params int[] ids) where T : IAggregateRoot, new()
        {
            if (null == ids || 0 == ids.Length)
                return false;
            else
            {
            }
            //加载事务并提交
            IUnitOfWork unit = new UnitOfWorkForSql();
            int i = -1;
            T model;

            try
            {
                while (++i < ids.Length)
                {
                    model = new T();
                    model.Visiter("Keyid", -1, true, ids[i]);
                    model.Visiter("Operator", -1, true, currentOperator);
                    unit.RegisterRemoved(model, operatorDal);
                }
                unit.Commit();
            }
            catch (Exception ex)
            {

                return false;
            }


            return true;
        }

        /// <summary>
        /// 读取数据为指定实体列表并释放掉reader
        /// </summary>
        /// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
        /// <param name="reader">查询结果集</param>
        /// <returns></returns>
        public List<T> ReadDataToModelAndDispose<T>(IDataReader reader) where T : IAggregateRoot, new()
        {
            using (reader)
            {
                return ReadDataToModel<T>(reader);
            }
        }

        /// <summary>
        /// 读取数据为指定实体列表
        /// </summary>
        /// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
        /// <param name="reader">查询结果集</param>
        /// <returns></returns>
        public List<T> ReadDataToModel<T>(IDataReader reader) where T : IAggregateRoot, new()
        {
            List<T> models = new List<T>();
            if (null == reader || !reader.Read())
                return models;

            T model = new T();
            string tempName = string.Empty;
            int maxCount = reader.FieldCount;//5
            int i = 0;

            while (true)
            {
                tempName = reader.GetName(i);
                model.Visiter(tempName, -1, true, reader[tempName]);

                if (++i >= maxCount)// 0,1 
                {
                    i = 0;
                    models.Add(model);
                    if (!reader.Read())
                        break;
                    model = new T();
                }
            }
            return models;
        }

        /// <summary>
        /// 通用分页查询
        /// </summary>
        /// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
        /// <param name="pagination">分页参数</param>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
        /// <param name="condition">筛选条件</param>
        /// <returns>实体集合(传入参数有误时返回空。另：Visiter内的设置值名称必须与查询字段都相符才能将全部值设置到实体)</returns>
        public IList<T> SelectModelPage<T>(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby = "", string condition = "") where T : IAggregateRoot, new()
        {
            IList<T> result = null;
            SqlParameter[] parameters = GetPagingParams(pagination, selectTarget, fromSouce, orderBy, resultOrderby, condition);
            if (null == parameters)
                return null;

            using (IDataReader reader = QueryDataReader("sp_CurrencyPage", CommandType.StoredProcedure, parameters))
            {
                result = ReadDataToModel<T>(reader);
                reader.Close();
                Close();
            }
            int? resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[8].Value);
            pagination.RecordCount = resultValue.HasValue ? resultValue.Value : 0;
            resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[7].Value);
            pagination.PageCount = resultValue.HasValue ? resultValue.Value : 0;
            return result;
        }

        /// <summary>
        /// 通用分页查询(返回DataTable)
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
        /// <param name="condition">筛选条件</param>
        /// <returns>DataTable(传入参数有误时返回空)</returns>
        public DataTable SelectModelPage(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby = "", string condition = "")
        {


            SqlParameter[] parameters = GetPagingParams(pagination, selectTarget, fromSouce, orderBy, resultOrderby, condition);
            if (null == parameters)
                return null;

            DataTable dt = QueryDataTable("sp_CurrencyPage", CommandType.StoredProcedure, parameters);

            int? resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[8].SqlValue);
            pagination.RecordCount = resultValue.HasValue ? resultValue.Value : 0;
            resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[7].SqlValue);
            pagination.PageCount = resultValue.HasValue ? resultValue.Value : 0;

            return dt;



        }

        /// <summary>
        /// 获取分页参数
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
        /// <param name="condition">筛选条件</param>
        /// <returns>参数列表(传入参数有误时返回空)</returns>
        private static SqlParameter[] GetPagingParams(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby, string condition)
        {
            if (null == pagination || string.IsNullOrEmpty(selectTarget) || string.IsNullOrEmpty(fromSouce) || string.IsNullOrEmpty(orderBy))
            {
                return null;
            }

            if (string.IsNullOrEmpty(resultOrderby))
            {
                int splitindex = orderBy.IndexOf('.');
                resultOrderby = string.Format("TempT.{0}", splitindex > 0 ? orderBy.Substring(splitindex + 1) : orderBy);
            }

            SqlParameter[] parameters = {
					new SqlParameter(){ParameterName="@Columns", SqlDbType=SqlDbType.VarChar,Value=selectTarget},
					new SqlParameter(){ParameterName="@TableName", SqlDbType=SqlDbType.VarChar,Value=fromSouce},
                    new SqlParameter(){ParameterName="@Condition", SqlDbType=SqlDbType.VarChar,Value=condition},
                    new SqlParameter(){ParameterName="@OrderByRow", SqlDbType=SqlDbType.VarChar,Value=orderBy},
                    new SqlParameter(){ParameterName="@OrderByTable", SqlDbType=SqlDbType.VarChar,Value=resultOrderby},
                    new SqlParameter(){ParameterName="@PageNum ", SqlDbType=SqlDbType.Int,Value= pagination.PageIndex},
                    new SqlParameter(){ParameterName="@PageSize", SqlDbType=SqlDbType.Int,Value=pagination.PageSize},
                    new SqlParameter(){ParameterName="@PageCount", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output},
                    new SqlParameter(){ParameterName="@RecordCount", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
            };
            return parameters;
        }

        /// <summary>
        /// 暂用查询
        /// </summary>
        /// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="condition">筛选条件</param>
        /// <returns>实体集合(传入参数有误时返回空。另：Visiter内的设置值名称必须与查询字段都相符才能将全部值设置到实体)</returns>
        public IList<T> SelectModel<T>(string selectTarget, string fromSouce, string condition = "") where T : IAggregateRoot, new()
        {
            IList<T> result = null;
            SqlParameter[] parameters = GetModelGeterParams(selectTarget, fromSouce, condition);
            if (null == parameters)
                return null;
            using (IDataReader reader = QueryDataReader("sp_SelectModel", CommandType.StoredProcedure, parameters))
            {
                result = ReadDataToModel<T>(reader);
                reader.Close();
                Close();
            }
            return result;

        }

        /// <summary>
        /// 暂用查询
        /// </summary>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="condition">筛选条件</param>
        /// <returns>DataTable(传入参数有误时返回空)</returns>
        public DataTable SelectModel(string selectTarget, string fromSouce, string condition = "")
        {

            SqlParameter[] parameters = GetModelGeterParams(selectTarget, fromSouce, condition);
            if (null == parameters)
                return null;
            return QueryDataTable("sp_SelectModel", CommandType.StoredProcedure, parameters);

        }

        /// <summary>
        /// 获取实体查询参数
        /// </summary>
        /// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
        /// <param name="fromSouce">数据源(Form后的部分)</param>
        /// <param name="orderBy">排序字段</param>
        /// <returns></returns>
        private SqlParameter[] GetModelGeterParams(string selectTarget, string fromSouce, string condition)
        {


            return string.IsNullOrEmpty(selectTarget) || string.IsNullOrEmpty(fromSouce) ? null :
                    new SqlParameter[] {
					new SqlParameter(){ParameterName="@Columns", SqlDbType=SqlDbType.VarChar,Value=selectTarget},
					new SqlParameter(){ParameterName="@TableName", SqlDbType=SqlDbType.VarChar,Value=fromSouce},
                    new SqlParameter(){ParameterName="@Condition", SqlDbType=SqlDbType.VarChar,Value=condition}
            };
        }

        #endregion

        #endregion

        #region IDisposable接口方法
        public void Dispose()
        {
            try
            {
                this.Close();
            }
            catch (Exception ex)
            {
                if (this._Tran != null)
                {
                    this._Tran.Rollback();
                }
                throw ex;
            }
        }
        #endregion
    }
}