using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;

namespace iMES.Bi.Data
{
    /// <summary>
    /// 公共接口
    /// </summary>
    public class BaseEFDao<T> : IBaseDao<T> where T : class, new()   //限制T为class
    {
        public SqlSugarClient Db;//用来处理事务多表查询和复杂的操作
        public SimpleClient<T> CurrentDb { get { return new SimpleClient<T>(Db); } }//用来处理T表的常用操作

        public static string ConnectionString = Appsettings.app(new string[] { "Connection", "DbConnectionString" });//获取连接字符串
        public static string DBType = Appsettings.app(new string[] { "Connection", "DBType" });//获取连接字符串


        public BaseEFDao()
        {


            Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConnectionString,
                DbType = DBType == "MySql" ? SqlSugar.DbType.MySql : SqlSugar.DbType.SqlServer,
                InitKeyType = InitKeyType.SystemTable,//从特性读取主键和自增列信息
                IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了

            });
           // 调式代码 用来打印SQL
            Db.Aop.OnLogExecuting = (sql, pars) =>
            {
            };

        }

        public void CModel()
        {
            Db.DbFirst.CreateClassFile("E:\\Demo", "iMES.Bi.Data");
        }
        public virtual IEnumerable<T> GetALLEntities()
        {

            //AsNoTracking不记录数据变化状况
            return CurrentDb.GetList();

        }
        public virtual IEnumerable<T> GetEntities(Expression<Func<T, bool>> exp)
        {

            //AsNoTracking不记录数据变化状况
            return CurrentDb.GetList(exp).ToList();
        }
        /// <summary>
        /// 根据条件查找
        /// </summary>
        /// <param name="exp">lambda查询条件where</param>
        /// <returns></returns>
        public virtual T GetEntity(Expression<Func<T, bool>> exp)
        {
            return CurrentDb.GetList(exp).SingleOrDefault();

        }

        /// <summary>
        /// 获取所有Entity(立即执行请使用ToList()
        /// </summary>
        /// <param name="CommandText">Sql语句</param>
        /// <param name="objParams">可变参数</param>
        /// <returns></returns>
        public virtual IEnumerable<T> GetEntities(string CommandText)
        {
            return Db.SqlQueryable<T>("select * from " + typeof(T).Name + " where 1=1 and  " + CommandText).ToList();
        }


        /// <summary>
        /// 插入Entity
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual bool Insert(T entity)
        {
            entity= Db.Insertable<T>(entity).ExecuteReturnEntity();
            //int dataID = CurrentDb.InsertReturnIdentity(entity);
            //List<string> List = Db.DbMaintenance.GetIsIdentities(entity.GetType().Name);
            //if (List.Count > 0)
            //{
            //    //如果有自增,赋值
            //    entity.GetType().GetProperty(List[0].ToString()).SetValue(entity, dataID, null);
            //}
            return true;
        }

        /// <summary>
        /// 同时插入多个实体。
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public virtual bool Insert(IEnumerable<T> entities)
        {
            int Return = 0;
            if (entities.Count() > 0)
            {
                Return = Db.Insertable(entities.ToArray()).ExecuteCommand();
            }
            return Return > 0;
        }

        /// <summary>
        /// 更新Entity(注意这里使用的傻瓜式更新,可能性能略低)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual bool Update(T entity)
        {
            return CurrentDb.Update(entity);

        }

        /// <summary>
        /// 删除Entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Delete(T entity)
        {
            return CurrentDb.Delete(entity);

        }
        /// <summary>
        /// 批量删除Entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Delete(Expression<Func<T, bool>> exp)
        {
            return CurrentDb.Delete(exp);
        }



        /// <summary>
        /// 根据条件查找
        /// </summary>
        /// <param name="CommandText">Sql语句</param>
        /// <param name="objParams">可变参数</param>
        /// <returns></returns>
        public virtual DataTable GetDTByCommand(string CommandText)
        {
            return Db.Ado.GetDataTable(CovSQL(CommandText));
        }



        /// <summary>
        /// 获取链接字符串
        /// </summary>
        /// <param name="CommandText">Sql语句</param>
        /// <param name="objParams">可变参数</param>
        /// <returns></returns>
        public string GetDBString()
        {
            return Db.CurrentConnectionConfig.ConnectionString;

        }


        //执行SQL语句
        public void ExsSql(string sql)
        {
            List<SugarParameter> parameters = null;
            Db.Ado.ExecuteCommand(CovSQL(sql), parameters);
        }

        public object ExsSclarSql(string sql)
        {
            List<SugarParameter> parameters = null;
            return Db.Ado.GetString(CovSQL(sql), parameters);
        }

        /// <summary>
        /// 替换
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        private string CovSQL(string strSQL)
        {
            if (Db.CurrentConnectionConfig.DbType == SqlSugar.DbType.MySql)//MYSQL数据库
            {
                strSQL = strSQL.Replace("isnull", "ifnull").Replace("ISNULL", "IFNULL");
            }
            return strSQL;
        }



        /// <summary>
        /// 数据分页
        /// </summary>
        /// <param name="viewName">表名</param>
        /// <param name="fieldName">字段</param>
        /// <param name="pageSize">默认20</param>
        /// <param name="pageNo">页数</param>
        /// <param name="orderString">排序</param>
        /// <param name="whereString">可选</param>
        /// <param name="recordTotal">总数</param>
        /// <returns></returns>
        public DataTable GetDataPager(string viewName, string fieldName, int pageSize, int pageNo, string orderString, string whereString, ref int recordTotal)
        {
            string strSQL = "select  " + fieldName + "  from " + viewName + " where " + whereString;
            DataTable dt = Db.SqlQueryable<Object>(CovSQL(strSQL)).OrderBy(orderString).ToDataTablePage(pageNo, pageSize, ref recordTotal);
            return dt;
        }



        /// <summary>
        /// 数据分页
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="strOrder"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNo"></param>
        /// <param name="recordTotal"></param>
        /// <returns></returns>
        public DataTable GetDataPager(string strSQL,string strOrder, int pageSize, int pageNo, ref int recordTotal)
        {
            DataTable dt = Db.SqlQueryable<Object>(CovSQL(strSQL)).OrderBy(strOrder).ToDataTablePage(pageNo, pageSize, ref recordTotal);
            return dt;
        }



        /// <summary>
        /// 行专列
        /// </summary>
        /// <param name="CommandText">Sql语句</param>
        /// <param name="objParams">可变参数</param>
        /// <returns></returns>
        public string GetDTHZL(string ExtendModes, string pdid)
        {
            string strSQL = "";
            if (Db.CurrentConnectionConfig.DbType == 0)//MYSQL数据库
            {
                string strTemp = "";
                foreach (string filename in ExtendModes.Split(','))
                {
                    strTemp = strTemp + " MAX(CASE ExFiledColumn WHEN '" + filename + "' THEN ExtendDataValue ELSE 0 END ) " + filename + ",";
                }
                strTemp = strTemp.TrimEnd(',');
                strSQL = " SELECT * FROM ( SELECT  uuid() AS ID, DataID,Yan_WF_PI.ISGD,Yan_WF_PI.BranchNo,Yan_WF_PI.BranchName,Yan_WF_PI.CRUser,Yan_WF_PI.CRUserName,  Yan_WF_PI.CRDate, " + strTemp + " FROM JH_Auth_ExtendData    INNER JOIN Yan_WF_PI  ON JH_Auth_ExtendData.DataID=Yan_WF_PI.ID  AND  JH_Auth_ExtendData.PDID='" + pdid + "' GROUP BY DataID,Yan_WF_PI.ISGD,Yan_WF_PI.BranchNo,Yan_WF_PI.BranchName,Yan_WF_PI.CRUser,Yan_WF_PI.CRUserName,  Yan_WF_PI.CRDate ) T  ";

            }
            else //sqlServer数据库
            {
                strSQL = " SELECT NEWID() AS ID, * FROM (  SELECT   Yan_WF_PI.ISGD,Yan_WF_PI.BranchNo,Yan_WF_PI.BranchName,JH_Auth_ExtendData.DataID, Yan_WF_PI.CRUser,Yan_WF_PI.CRUserName,  Yan_WF_PI.CRDate, JH_Auth_ExtendMode.TableFiledColumn,  ExtendDataValue  from JH_Auth_ExtendMode INNER JOIN JH_Auth_ExtendData ON JH_Auth_ExtendMode.PDID=JH_Auth_ExtendData.PDID and JH_Auth_ExtendMode.TableFiledColumn=JH_Auth_ExtendData.ExFiledColumn and JH_Auth_ExtendMode.PDID='" + pdid + "' INNER JOIN Yan_WF_PI  ON JH_Auth_ExtendData.DataID=Yan_WF_PI.ID  ) AS P PIVOT ( MAX(ExtendDataValue) FOR  p.TableFiledColumn  IN (" + ExtendModes + ") ) AS T ";

            }
            return strSQL;
        }


    }
}