using Dapper;
using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VueWebCoreApi.DLL.BLL;
using VueWebCoreApi.Models;

namespace VueWebCoreApi.Tools
{
    public class DapperHelper
    {

        /// <summary>
        /// 创建数据库连接对象Sqlserver
        /// </summary>
        /// <returns></returns>
        public static IDbConnection sqlConnection()
        {
            //string sqlServerConnectString = "server=***;database=***;User=***;password=***;Connect Timeout=10000";
            string sqlServerConnectString = AppSetting.GetAppSetting("DBServer");
            //获取数据库连接
            IDbConnection connection = new SqlConnection(sqlServerConnectString);
            connection.Open();
            return connection;
        }


        /// <summary>
        /// Dapper查询返回List
        /// </summary>
        /// <typeparam name="T">需要返回的对象类型</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static List<T> select<T>(string sql, Object parm) where T : new()
        {

            List<T> list = null;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    //用户操作记录写入数据表
                    //Log4net.LogUtil.SaveMessage("PC", "操作了xxxxx", "api/Login/LoginSave", ConfigurationManager.AppSettings["FileIP"], "9999", 1);
                    list = conn.Query<T>(sql, parm).ToList<T>();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }

            }
            return list;
        }


        /// <summary>
        /// Dapper查询返回List字典对象  无需手动Wapper对象了 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static List<Dictionary<string, Object>> selectToDict(string sql, Object parm)
        {

            List<Dictionary<string, Object>> result = null;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    var menus = conn.Query(sql, parm).ToList();
                    result = JsonConvert.DeserializeObject<List<Dictionary<string, Object>>>(JsonConvert.SerializeObject(menus));
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Dapper查询返回对象非List集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static T selectToObject<T>(string sql, Object parm) where T : new()
        {
            T t = new T();
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    t = conn.Query<T>(sql, parm).Single<T>();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return t;
        }

        /// <summary>
        /// dapper通用分页函数
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="orderBy">排序（字段 DESC/ASC）</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList<T>(string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
        {
            //int skip = 1;
            //if (pageIndex > 0)
            //{
            //    skip = (pageIndex - 1) * pageSize + 1;
            //}
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
            sb.AppendFormat(@"SELECT *
                        FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNum
                             FROM  ({0}) AS Temp) AS Result
                        WHERE  RowNum >= {3} AND RowNum <= {4}
                        ORDER BY {1} {2}", sql, orderBy, sort, pageIndex, pageSize);  //skip, pageIndex * pageSize

            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    using (var reader = conn.QueryMultiple(sb.ToString(), parm))
                    {
                        total = reader.ReadFirst<int>();
                        return reader.Read<T>();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }

            }
        }

        /// <summary>
        /// dapper通用分页函数
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        ///<param name="withsql">with函数语句</param>
        /// <param name="sql">查询语句</param>
        /// <param name="orderBy">排序（字段 DESC/ASC）</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPagedDataWith<T>(string withsql, string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
        {
            //int skip = 1;
            //if (pageIndex > 0)
            //{
            //    skip = (pageIndex - 1) * pageSize + 1;
            //}
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("{0} SELECT COUNT(1) FROM ({1}) AS Result;",withsql, sql);
            sb.AppendFormat(@"{0} SELECT *
                        FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {2} {3}) AS RowNum
                             FROM  ({1}) AS Temp) AS Result
                        WHERE  RowNum >= {4} AND RowNum <= {5}
                        ORDER BY {2} {3}",withsql, sql, orderBy, sort, pageIndex, pageSize);  //skip, pageIndex * pageSize

            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    using (var reader = conn.QueryMultiple(sb.ToString(), parm))
                    {
                        total = reader.ReadFirst<int>();
                        return reader.Read<T>();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }

            }
        }


        /// <summary>
        /// WITH分页
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="torg_code">子级字段</param>
        /// <param name="parent_id">父级字段</param>
        /// <param name="condition">查询条件</param>
        /// <param name="parm">查询参数</param>
        /// <param name="prop">排序字段</param>
        /// <param name="order">排序规则</param>
        /// <param name="pageSize">页码</param>
        /// <param name="pageNumber">每页显示条数</param>
        /// <returns></returns>

        public static IEnumerable<T> GetPagedData<T>(string tableName, string torg_code, string parent_id, string condition, Object parm, string prop, string order, int pageSize, int pageNumber, out int total)
        {
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    // 查询总记录数
                    string countQuery = $@"WITH CTE AS (
                                SELECT  *
                                FROM {tableName}
                                WHERE {condition}
                                UNION ALL 
                                select o.*
                                from {tableName} o
                                JOIN CTE c ON o.{torg_code} = c.{parent_id}
                               )
                              SELECT DISTINCT * FROM CTE";
                    int totalRecords = conn.ExecuteScalar<int>(countQuery, parm);

                    // 计算总页数
                    int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

                    // 计算分页的起始行号
                    int startRow = (pageNumber - 1) * pageSize + 1;
                    // 计算分页的结束行号
                    int endRow = pageNumber * pageSize;

                    // 分页查询
                    string query = $@"WITH CTE AS (
                                SELECT  *
                                FROM {tableName}
                                WHERE {condition}
                                UNION ALL 
                                select o.*
                                from {tableName} o
                                JOIN CTE c ON o.{torg_code} = c.{parent_id}
                               )
                              SELECT *  FROM(
                              SELECT ROW_NUMBER() OVER (ORDER BY {prop} {order}) AS RowNum,*
                                  FROM (
                                      SELECT DISTINCT *
                                      from CTE
                                ) AS subquery) AS AA  WHERE RowNum >= {startRow} AND RowNum <={endRow}";
                    total = totalRecords;
                    return conn.Query<T>(query, parm);
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Dapper查询返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static DataTable selectdata(string sql, Object parm)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql, parm);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Dapper查询返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static DataTable selectlist(string sql, Object parm)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql, parm);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Dapper查询返回datatable数据(不带参数)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable selecttable(string sql)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    //LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Dapper执行存储过程返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="parm">参数</param>
        /// <returns></returns>
        public static DataTable selectProcedure(string sql, Object parm)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程 
                    table.Load(res);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// Dapper执行存储过程返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="parm">参数</param>
        /// <returns></returns>
        public static bool IsProcedure(string sql, Object parm)
        {
            bool result;
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程 
                    result = true;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    result = false;
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// Dapper执行存储过程返回datatable数据(不带参数)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <returns></returns>
        public static bool IsProcedureNo(string sql)
        {
            bool result;
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql);//sql 存储过程 
                    result = true;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    result = false;
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// Dapper查询返回数据条数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static int selectcount(string sql, Object parm)
        {
            //sql语句
            int result = 0;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    result = conn.Query(sql, parm).Count();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// Dapper增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parametere"></param>
        /// <returns></returns>
        public static int SQL(string sql, Object parametere)
        {
            //sql语句
            int result = 0;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    result = conn.Execute(sql, parametere);
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 增加，删除，修改使用的 事务方法  Sqllist为依次执行
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static bool DoTransaction(List<object> sqlList)
        {
            bool result;
            IDbTransaction tranction = null;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    tranction = conn.BeginTransaction();
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
                    }
                    tranction.Commit();

                    result = true;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    result = false;
                    tranction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// 增加，删除，修改使用的 事务方法  Sqllist为依次执行
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static int DoTransactionCont(List<object> sqlList)
        {
            int result = 0;
            IDbTransaction tranction = null;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    tranction = conn.BeginTransaction();
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        result += conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
                    }
                    tranction.Commit();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    tranction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// Dapper插入 返回自增主键Id（备份）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <param name="tableName">待插入数据的表名</param>
        /// <returns></returns>
        public static int insertReturnId0(string sql, Object parameter, String tableName)
        {

            int result = 0;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    result = conn.Execute(sql, parameter);
                    if (result != 0)
                    {
                        result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }


        /// <summary>
        /// Dapper 返回自增主键当前数据表最大Id
        /// </summary>
        /// <param name="tableName">待插入数据的表名</param>
        /// <returns></returns>
        public static int insertReturnId(String tableName)
        {

            int result = 0;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    if (result == 0)
                    {
                        result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// 执行sql执行语句,返回字符串
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string sqlstr(String sql)
        {

            string result = "";
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    if (result == "")
                    {
                        result = conn.Query<string>(sql).First();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }

        public static DataTable lissql(string sql)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql);//sql 存储过程 
                    table.Load(res);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
        }
    }
}