using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Text;

namespace FEG.ESB.Data.EF
{
    using Dapper;
    using MySql.Data.MySqlClient;
    using System.Threading.Tasks;
    using static Dapper.SqlMapper;

    /// <summary>
    /// 
    /// </summary>
    public class FEG_DapperHelper
    {
        private static string str = FEG_ConfigHelper.GetDbConnectionStr();

        /// <summary>
        /// 查询 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(string sql, object parames = null) where T : class, new()
        {
            try
            {
                using MySqlConnection conn = GetMySqlConnnetion();
                return conn.Query<T>(sql, parames);
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 查询 异步操作 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> QueryAsync<T>(string sql, object parames = null) where T : class, new()
        {
            try
            {
                using MySqlConnection conn = GetMySqlConnnetion();
                return await conn.QueryAsync<T>(sql, parames);
            }
            catch (Exception)
            {
                return null;
            }
        }

        #region old
        ///// <summary>
        ///// 查询两个实体的操作 
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="parames"></param>
        ///// <returns></returns>
        //public static Tuple<IEnumerable<T>, IEnumerable<M>> QueryTwoEntity<T, M>(string sql, object parames = null) where T : class where M : class, new()
        //{
        //    try
        //    {
        //        using MySqlConnection conn = GetMySqlConnnetion();
        //        var readData = conn.QueryMultiple(sql, parames);
        //        var obj = readData.Read<T>() as IEnumerable<T>;
        //        var obj2 = readData.Read<M>() as IEnumerable<M>;
        //        return (obj, obj2).ToTuple();
        //    }
        //    catch (Exception)
        //    {
        //        return null;
        //    }
        //} 
        #endregion

        /// <summary>
        /// 同时查询多个实体的操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="funcObj"></param>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public static void QueryMultipeEntity(string sql, Dictionary<string, object> dicParams, Action<GridReader> funcObj)
        {
            using MySqlConnection conn = GetMySqlConnnetion();
            if (dicParams != null)
            {
                DynamicParameters ps = new DynamicParameters();
                foreach (string item in dicParams.Keys)
                {
                    ps.Add(item, dicParams[item]);
                }
                using (var readRsult = conn.QueryMultiple(sql, ps))
                {
                    funcObj.Invoke(readRsult);
                }
            }
        }

        /// <summary>
        /// 获取 MySql连接
        /// </summary>
        /// <returns></returns>
        private static MySqlConnection GetMySqlConnnetion()
        {
            return new MySqlConnection(str);
        }

        /// <summary>
        /// 获取Dapper参数化对象,这里直接New来处理，不到处引入命名空间
        /// </summary>
        /// <returns></returns>
        public static DynamicParameters GetDynamicParameters()
        {
            return new DynamicParameters();
        }
    }

    /// <summary>
    /// 
    /// </summary>
    public class FEG_ConfigHelper
    {
        public static IConfiguration _configuration { get; set; }
        /// <summary>
        /// 获取连接数据库的字符串
        /// </summary>
        /// <returns></returns>
        public static string GetDbConnectionStr()
        {
            return _configuration.GetSection("ConnectionStrings:ReadonlyConnection").Value;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string GetAppSettingValueByKey(string key)
        {
            return _configuration.GetSection(key).Value;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key"></param>
        /// <returns></returns>
        public static T GetAppSettingEntity<T>(string key) where T : class, new()
        {
            return _configuration.GetSection(key).Value as T;
        }
    }
}

 /// <summary>
        /// Test dapper，同时查询多个表的操作
        /// </summary>
        /// <returns></returns>
        [HttpPost,Route("TestGridReader")]
        public BasisApiResult TestGridReader()
        {
            BasisApiResult result = new BasisApiResult();
            result.data = _personnelService.TestGridReader<FEG.ESB.Data.Model.personnel, FEG.ESB.Data.Model.course>();
            return result;
        }

       public Tuple<IEnumerable<T>, IEnumerable<M>> TestGridReader<T, M>()
        {
            IEnumerable<T> plist = null;
            IEnumerable<M> clist = null;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("isdel", "0");
            FEG_DapperHelper.QueryMultipeEntity("select * from personnel where isdel=@isdel limit 0,1;select * from course  where isdel=@isdel limit 0,1;", dic, x =>
              {
                  plist = x.Read<T>().ToList();
                  clist = x.Read<M>().ToList();
              });
            return Tuple.Create(plist, clist);
        }