using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace HH.WCS.Hexafluo.util
{
    //https://www.donet5.com/Home/Doc
    public class SqlHelper<T> where T : class, new()
    {
        public bool ExecuteSql(string sql)
        {
            try
            {
                var code = GetInstance().Ado.ExecuteCommand(sql);
                return code > 1;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool Update(T model, string[] cols)
        {
            var db = GetInstance();
            return db.Updateable<T>(model).UpdateColumns(cols).ExecuteCommand() > 0;
        }
        public List<T> GetList(Expression<Func<T, bool>> where = null)
        {
            var db = GetInstance();
            if (where == null)
            {
                return db.Queryable<T>().ToList();
            }
            else
            {
                return db.Queryable<T>().Where(where).ToList();
            }
        }
        public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> order, bool asc = false)
        {
            SqlSugarClient db = GetInstance();
            if (order == null)
            {
                return db.Queryable<T>().Where(where).Single();
            }
            else
            {
                return db.Queryable<T>().Where(where).OrderBy(order, asc ? OrderByType.Asc : OrderByType.Desc).First();
            }
        }
        public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> orderBy = null)
        {
            try
            {
                var db = GetInstance();
                T model = null;
                if (orderBy != null)
                {
                    model = db.Queryable<T>().Where(where).OrderBy(orderBy, OrderByType.Desc).First();//查询表的所有
                }
                else
                {
                    model = db.Queryable<T>().Where(where).First();
                }
                return model;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return default(T);
            }
        }
        public bool Update(T model)
        {
            var db = GetInstance();
            return db.Updateable<T>(model).ExecuteCommand() > 0;
        }
        public bool Insert(T model)
        {
            try
            {
                var db = GetInstance();
                return db.Insertable<T>(model).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //删除指定条件数据
        public bool Deleteable(Expression<Func<T, bool>> where)
        {
            try
            {
                var db = GetInstance();
                return db.Deleteable<T>().Where(where).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static List<ConnectionConfig> connD = new List<ConnectionConfig>();
        //创建SqlSugarClient 
        public SqlSugarClient GetInstance(string url = null)
        {
            //创建数据库对象
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = url ?? Settings.SqlServer,
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true,
                MoreSettings = new ConnMoreSettings()
                {
                    IsWithNoLockQuery = true,//主查询
                    IsWithNoLockSubquery = true //子查询
                },
                InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
            });
            //
            //db.MappingTables.Add("","");

            //监控所有超过1秒的Sql
            db.Aop.OnLogExecuted = (sql, p) =>
            {
                // Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
                LogHelper.Info("执行的SQL", "ID:" + JsonConvert.SerializeObject(db.ContextID) + " sql: " + UtilMethods.GetSqlString(DbType.SqlServer, sql, p));
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                {
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
                }
                //相当于EF的 PrintToMiniProfiler
            };
            db.Aop.DataExecuted = (value, entity) =>
            {
                //只有行级事件
                entity.EntityColumnInfos.ToList().ForEach(a =>
                {
                    var pvalue = entity.GetValue(a.PropertyName);
                    if (pvalue != null && pvalue.GetType() == typeof(String))
                    {
                        entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                    }
                });
            };
            //据转换 (ExecuteCommand才会拦截，查询不行)
            //db.Aop.DataExecuting = (value, entity) => {
            //    //var val=entity.EntityColumnInfo
            //    Console.WriteLine(entity.EntityName);
            //};

            return db;
        }
    }



    public class SqlSugarHelper //不能是泛型类
    {
        //多库情况下使用说明：
        //如果是固定多库可以传 new SqlSugarScope(List<ConnectionConfig>,db=>{}) 文档：多租户
        //如果是不固定多库 可以看文档Saas分库
        public static List<ConnectionConfig> conn = new List<ConnectionConfig>() {
          new ConnectionConfig(){ConfigId="1",
              DbType=DbType.SqlServer,
              ConnectionString=Settings.SqlServer,
              IsAutoCloseConnection=true,
              MoreSettings=new ConnMoreSettings() {
                    IsWithNoLockQuery= true,//主查询
                    //DisableWithNoLockWithTran = false,//事务禁用NoLock 
                    IsWithNoLockSubquery=true //子查询
              },

              InitKeyType = InitKeyType.Attribute},
        };

        //用单例模式
        /// <summary>
        /// 连接池
        /// </summary>

        public static SqlSugarScope Db = new SqlSugarScope(conn,
      db =>
      {

          //(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
          //调试SQL事件，可以删掉
          db.Aop.OnLogExecuting = (sql, pars) =>
          {
              //  Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响

              //获取原生SQL推荐 5.1.4.63  性能OK
              // UtilMethods.GetNativeSql(sql, pars);
              //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
              // LogHelper.Info("执行的SQL", UtilMethods.GetNativeSql(sql, pars));
              LogHelper.Info("执行的SQL", "ID:" + JsonConvert.SerializeObject(db.ContextID) + " sql: " + UtilMethods.GetSqlString(DbType.SqlServer, sql, pars));

              //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
              //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)

          };
          db.Aop.DataExecuted = (value, entity) =>
          {
              //只有行级事件
              entity.EntityColumnInfos.ToList().ForEach(a =>
              {
                  var pvalue = entity.GetValue(a.PropertyName);
                  if (pvalue != null && pvalue.GetType() == typeof(String))
                  {
                      entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                  }
              });
          };
          db.Aop.OnLogExecuted = (sql, p) =>
          {
              // Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
              // LogHelper.Info("执行的SQL", sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));

              //执行时间超过1秒
              if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
              {
                  //代码CS文件名
                  var fileName = db.Ado.SqlStackTrace.FirstFileName;
                  //代码行数
                  var fileLine = db.Ado.SqlStackTrace.FirstLine;
                  //方法名
                  var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                  //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
              }
              //相当于EF的 PrintToMiniProfiler
          };

          //多个配置就写下面
          //db.Ado.IsDisableMasterSlaveSeparation=true;

          //注意多租户 有几个设置几个
          //db.GetConnection(i).Aop
      });
    }
}