using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using HH.WMS.Entitys;
using System.Data;

namespace HH.WMS.DAL
{

    public class DapperBaseDAL : BaseDAL
    {

        #region 执行查询-返回动态列List
        /// <summary>
        /// 查询-返回动态列List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<dynamic> ExecuteDynamicList(string sql, object param = null)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                return conn.Query(sql, param).ToList();
            }
        }
        #endregion

        #region 执行查询-返回datatable
        /// <summary>
        /// 执行查询-返回datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, object param = null)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                DataTable dt = new DataTable();
                dt.Load(conn.ExecuteReader(sql, param));
                return dt;
            }
        }
        #endregion

        #region 执行查询-返回单个实体
        /// <summary>
        /// 执行查询-返回单个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T ExecuteSingleEntity<T>(string sql, object param = null)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                return conn.QueryFirstOrDefault<T>(sql, param);
            }
        }
        #endregion



        #region 执行查询-返回动态列单个实体
        /// <summary>
        /// 执行查询-返回动态列单个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public dynamic ExecuteSingleDynamic(string sql, object param = null)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                return conn.QueryFirstOrDefault(sql, param);
            }
        }
        #endregion

        #region 执行查询-返回List
        /// <summary>
        /// 执行查询-返回List
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(string sql, object param = null)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                return conn.Query<T>(sql, param).ToList();
            }
        }
        #endregion

        #region 分页-datable
        /// <summary>
        /// 分页-datable
        /// </summary>
        /// <param name="tableName">多表格式(select * from tn_wm_out_mst) t where 1=1 </param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="orderBy"></param>
        /// <param name="param">注入参数</param>
        /// <returns></returns>
        public OperateResult ExecutePagingResult(string tableName, int pageIndex, int pageSize, string orderBy = "", object param = null)
        {
            try
            {
                long total;
                DataTable dt = ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, param);
                return OperateResult.Succeed(null, new
                {
                    rows = dt,
                    total = total
                });
            }
            catch (Exception ex)
            {
                return OperateResult.Error(ex.Message);
            }
        }
        #endregion

        #region 分页返回数据
        /// <summary>
        /// 分页返回数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="orderBy"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable ExecutePagingData(string tableName, int pageIndex, int pageSize, out long total, string orderBy = "", object param = null)
        {
            string countSql = "SELECT COUNT(1) FROM " + tableName;
            string pagingSql = string.Empty;
            switch (NowDbType)
            {
                case DBTYPE.MSSQL:
                    if (string.IsNullOrEmpty(orderBy))
                        orderBy = "ORDER BY CN_T_MODIFY DESC";
                    pagingSql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER({0}) AS ROWID,* FROM {1} ) AS SP 
	WHERE ROWID BETWEEN STR(({2}-1)*{3}+1) AND STR({4}*{5})";
                    pagingSql = string.Format(pagingSql, orderBy, tableName, pageIndex, pageSize, pageIndex, pageSize);
                    break;
                case DBTYPE.MYSQL:
                    pagingSql = @"SELECT T.* FROM (SELECT * FROM {0} {3}) T LIMIT {1},{2} ";
                    pagingSql = string.Format(pagingSql, tableName, pageIndex, pageSize, orderBy);
                    break;
            }
            total = ExecuteCountSql(countSql, param);
            HH.WMS.Common.Log.Info("DapperBaseDAL", countSql);
            return ExecuteDataTable(pagingSql, param);
        }
        #endregion

        public DataTable ExecuteSqlDataTable(string tableName, object param = null)
        {
            string sql = @"SELECT * FROM " + tableName;
            return ExecuteDataTable(sql, param);
        }

        #region 执行sql，返回数量
        /// <summary>
        /// 执行sql，返回数量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long ExecuteCountSql(string sql, object param = null)
        {
            try
            {
                using (DbConnection conn = this.DataAccess.CreateConnection())
                {
                    object obj = conn.ExecuteScalar(sql, param);
                    return Convert.ToUInt32(obj);
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region 执行sql 事务，单条
        /// <summary>
        /// 执行sql 事务，单条
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ExecuteTranSql(string sql, object param, IDbTransaction trans)
        {
            if (trans == null)
            {
                return UseTransaction(tran =>
                {
                    return tran.Connection.Execute(sql, param, tran);
                });
            }
            else
            {
                try
                {
                    int affectedRows = trans.Connection.Execute(sql, param, trans);
                    return OperateResult.Succeed(affectedRows);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
        }
        #endregion

        #region 执行sql 事务，多条
        /// <summary>
        /// 执行sql 事务，多条
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult ExecuteTranSql<T>(string sql, IList<T> list, IDbTransaction trans = null)
        {
            if (trans == null)
            {
                return UseTransaction(tran =>
                {
                    foreach (var entity in list)
                    {
                        tran.Connection.Execute(sql, entity, tran);
                    }
                });
            }
            else
            {
                try
                {
                    foreach (var entity in list)
                    {
                        trans.Connection.Execute(sql, entity, trans);
                    }
                    return OperateResult.Succeed();
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
        }
        #endregion

        #region 使用事务
        /// <summary>
        /// 使用事务
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        public OperateResult UseTransaction(Action<IDbTransaction> action)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                conn.Open();
                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        action(tran);
                        tran.Commit();
                        return OperateResult.Succeed();
                    }
                    catch (Exception exception)
                    {
                        tran.Rollback();
                        return OperateResult.Error(exception.Message);
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 带返回值事务（用于单条事务返回影响行数）
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        public OperateResult UseTransaction(Func<IDbTransaction, int> action)
        {
            using (DbConnection conn = this.DataAccess.CreateConnection())
            {
                conn.Open();
                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        int affectedRows = action(tran);
                        tran.Commit();
                        return OperateResult.Succeed(affectedRows);
                    }
                    catch (Exception exception)
                    {
                        tran.Rollback();
                        return OperateResult.Error(exception.Message);
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
        }
        #endregion

        #region 过滤查询条件
        /// <summary>
        /// 过滤查询条件
        /// </summary>
        /// <param name="field"></param>
        /// <param name="value"></param>
        /// <param name="opt"></param>
        /// <returns></returns>
        public string GetFilterField(string field, object value, string opt = "=")
        {
            string _value = WMS.Common.Util.ToStringInput(value);
            if (string.IsNullOrEmpty(_value))
                return string.Empty;
            string result = string.Empty;
            field = " AND " + field + " ";
            switch (opt)
            {
                case "=":
                    result = field + opt + "'" + _value + "' ";
                    break;
                case "like":
                    result = field + opt + " '%" + _value + "%' ";
                    break;
                case "in":
                    result = field + opt + " ('" + _value.Replace(",", "','") + "') ";
                    break;
                case "dateArray":
                    string[] date = new string[2];
                    date = value as string[];
                    if (date.Length == 2)
                    {
                        string startDate = date[0];
                        if (!string.IsNullOrEmpty(startDate))
                        {
                            result += (field + ">= '" + startDate + "' ");
                        }
                        string endDate = date[1];
                        if (!string.IsNullOrEmpty(endDate))
                        {
                            result += (field + "< '" + Convert.ToDateTime(endDate).AddDays(1).ToString() + "' ");
                        }
                    }
                    break;
                default: return string.Empty;
            }
            return result;
        }
        #endregion
    }
}