using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Yl.CM.Server.Common.Enum;

namespace Yl.CM.Server.Common
{
    public class UserId
    {
        public string Id { get; set; }

        public string Pws { get; set; }

        public int? LoginCount { get; set; }
    }

    public static class LambdaToSqlHelper
    {
        #region 实现方法

        public static int GetPageByDataTable(this DataTable dt, int size)
        {
            int totalCount = dt.Rows[0][0].ToString().ToIntByStr();
            return totalCount; //% size > 0 ? totalCount / size + 1 : totalCount / size;
        }

        #region DataTable转化成List

        public static List<T> ConvertToModelList<T>(DataTable dt) where T : new()
        {
            List<T> ts = new List<T>();// 定义集合
            Type type = typeof(T); // 获得此模型的类型
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;

                    if (dt.Columns.Contains(tempName))
                    {
                        if (!pi.CanWrite)
                        {
                            continue;
                        }
                        if (pi.PropertyType.Name == "Boolean")
                        {
                            bool value = false;
                            if (dr[tempName].ToString() == "1")
                            {
                                value = true;
                            }

                            pi.SetValue(t, value, null);
                        }
                        else
                        {
                            object value = dr[tempName];
                            if (value != DBNull.Value)
                            {
                                pi.SetValue(t, value, null);
                            }
                        }
                    }
                }
                ts.Add(t);
            }
            return ts;
        }

        #endregion DataTable转化成List

        #region List转换成DataTable

        public static DataTable ConvertToDataTable<T>(IEnumerable<T> collection)
        {
            DataTable dtReturn = new DataTable();

            PropertyInfo[] oProps = null;

            foreach (T rec in collection)
            {
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();

                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                }

                dtReturn.Rows.Add(dr);
            }

            return (dtReturn);

            //var props = typeof(T).GetProperties();
            //var dt = new DataTable();
            //dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name,p.PropertyType)).ToArray());
            //if (collection.Count() > 0)
            //{
            //    for (int i = 0; i < collection.Count(); i++)
            //    {
            //        ArrayList tempList = new ArrayList();
            //        foreach (PropertyInfo pi in props)
            //        {
            //            object obj = pi.GetValue(collection.ElementAt(i), null);
            //            tempList.Add(obj);
            //        }
            //        object[] array = tempList.ToArray();
            //        dt.LoadDataRow(array, true);
            //    }
            //}
            //return dt;
        }

        #endregion List转换成DataTable

        #region 获取 插入语句

        public static string GetInsertSqlByT<T>(T t) where T : class
        {
            var type = typeof(T);

            var typeAttrList = type.GetProperties();
            string sb = "INSERT INTO `{0}` ({1}) values ({2})";
            StringBuilder sbName = new StringBuilder("");
            StringBuilder sbValue = new StringBuilder("");

            foreach (var ta in typeAttrList)
            {
                sbName.Append("`");
                sbName.Append(ta.Name);
                sbName.Append("`");
                sbName.Append(",");
                sbValue.Append("`");
                sbValue.Append(GetValueStringByType(ta.GetValue(t, null)));
                sbValue.Append("`");
                sbValue.Append(",");
            }
            string name = sbName.ToString().Substring(0, sbName.Length - 1);
            string value = sbValue.ToString().Substring(0, sbValue.Length - 1);
            return string.Format(sb, type.Name.ToLower(), name, value);
        }

        public static string GetInsertSqlByT<T>(T t, out MySqlParameter[] pars) where T : class
        {
            var type = typeof(T);
            var typeAttrList = type.GetProperties();
            string sb = "INSERT INTO `{0}` ({1}) values ({2})";
            StringBuilder sbName = new StringBuilder("");
            StringBuilder sbValue = new StringBuilder("");
            int markPar = 0;
            pars = new MySqlParameter[typeAttrList.Length - 1];
            foreach (var ta in typeAttrList)
            {
                if (ta.Name.ToLower() == "id")
                {
                    continue;
                }
                sbName.Append("`");
                sbName.Append(ta.Name);
                sbName.Append("`");
                sbName.Append(",");
                sbValue.Append("");
                sbValue.Append("@par" + markPar);
                sbValue.Append("");
                sbValue.Append(",");
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = "@par" + markPar;
                p.Value = ta.GetValue(t, null);

                // p.SqlDbType =SqlTypeString2SqlType(ta.PropertyType.Name.ToLower());
                pars[markPar] = p;
                markPar++;
            }
            string name = sbName.ToString().Substring(0, sbName.Length - 1);
            string value = sbValue.ToString().Substring(0, sbValue.Length - 1);
            return string.Format(sb, type.Name.ToLower(), name, value);
        }

        public static string GetInsertSqlToIDByT<T>(T t, out MySqlParameter[] pars) where T : class
        {
            var type = typeof(T);
            var typeAttrList = type.GetProperties();
            string sb = "INSERT INTO `{0}` ({1}) values ({2})";
            StringBuilder sbName = new StringBuilder("");
            StringBuilder sbValue = new StringBuilder("");
            int markPar = 0;
            pars = new MySqlParameter[typeAttrList.Length];
            foreach (var ta in typeAttrList)
            {
                sbName.Append("`");
                sbName.Append(ta.Name);
                sbName.Append("`");
                sbName.Append(",");
                sbValue.Append("");
                sbValue.Append("@par" + markPar);
                sbValue.Append("");
                sbValue.Append(",");
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = "@par" + markPar;
                p.Value = ta.GetValue(t, null);

                // p.SqlDbType =SqlTypeString2SqlType(ta.PropertyType.Name.ToLower());
                pars[markPar] = p;
                markPar++;
            }
            string name = sbName.ToString().Substring(0, sbName.Length - 1);
            string value = sbValue.ToString().Substring(0, sbValue.Length - 1);
            return string.Format(sb, type.Name.ToLower(), name, value);
        }

        #endregion 获取 插入语句

        #region 获取修改语句

        public static string GetUpdateSqlByT<T>(T t, Expression<Func<T, bool>> func, out MySqlParameter[] pars) where T : class
        {
            var type = typeof(T);
            var typeAttrList = type.GetProperties();
            string sb = "UPDATE " + type.Name.ToLower() + " SET {0} where 1=1 and {1}";

            StringBuilder sbValue = new StringBuilder("");
            int markPar = 0;

            List<ParMODEL> listPar = new List<ParMODEL>();
            string where = GetWhereSql(func, listPar);

            pars = new MySqlParameter[typeAttrList.Length + listPar.Count - 1];
            foreach (var ta in typeAttrList)
            {
                if (ta.Name == "ID")
                {
                    continue;
                }
                sbValue.Append("`");
                sbValue.Append(ta.Name);
                sbValue.Append("`");
                sbValue.Append("=");
                sbValue.Append("");
                sbValue.Append("@parValue" + markPar);
                sbValue.Append("");
                sbValue.Append(",");
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = "@parValue" + markPar;
                p.Value = ta.GetValue(t, null);
                pars[markPar] = p;
                markPar++;
            }
            string value = sbValue.ToString().Substring(0, sbValue.Length - 1);

            foreach (var l in listPar)
            {
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = l.name;
                p.Value = l.value;
                pars[markPar] = p;
                markPar++;
            }

            return string.Format(sb, value, where);
        }

        #endregion 获取修改语句

        #region 获取分页语句

        public static string GetSelectSqlByPage<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> where, out MySqlParameter[] parameter) where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();

            string sqlWhere = LambdaToSqlHelper.GetWhereSql<T>(where, parModelList);
            parameter = LambdaToSqlHelper.GetParmetersByList(parModelList);

            string sqlTable = LambdaToSqlHelper.GetSelectTableSqlPage<T>();
            string sqlPageTotal = LambdaToSqlHelper.GetTotalPage(pageIndex, pageSize);

            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append(" and ");
            sb.Append(sqlWhere);
            sb.Append("  ");

            sb.Append(sqlPageTotal);
            return sb.ToString();
        }

        public static string GetSelectSqlByPage<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> where, Expression<Func<T, object>> order, out MySqlParameter[] parameter) where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();

            string sqlWhere = LambdaToSqlHelper.GetWhereSql<T>(where, parModelList);
            parameter = LambdaToSqlHelper.GetParmetersByList(parModelList);
            string sqlOrder = LambdaToSqlHelper.GetOrderSql<T>(order);

            string sqlTable = LambdaToSqlHelper.GetSelectTableSqlPage<T>();
            string sqlPageTotal = LambdaToSqlHelper.GetTotalPage(pageIndex, pageSize);

            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append(" and ");
            sb.Append(sqlWhere);
            sb.Append("  ");
            sb.Append(sqlOrder);
            sb.Append(sqlPageTotal);
            return sb.ToString();
        }

        public static string GetSelectSql<T>(Expression<Func<T, bool>> where, Expression<Func<T, object>> order, out MySqlParameter[] parameter) where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();

            string sqlWhere = LambdaToSqlHelper.GetWhereSql<T>(where, parModelList);
            parameter = LambdaToSqlHelper.GetParmetersByList(parModelList);
            string sqlOrder = LambdaToSqlHelper.GetOrderSql<T>(order);

            string sqlTable = LambdaToSqlHelper.GetSelectTableSql<T>();

            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append("  and ");
            sb.Append(sqlWhere);
            sb.Append("  ");
            sb.Append(sqlOrder);
            return sb.ToString();
        }

        public static string GetSelectSql<T>(Expression<Func<T, bool>> where, out MySqlParameter[] pars) where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();

            string sqlWhere = LambdaToSqlHelper.GetWhereSql<T>(where, parModelList);
            pars = LambdaToSqlHelper.GetParmetersByList(parModelList);

            string sqlTable = LambdaToSqlHelper.GetSelectTableSql<T>();

            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append(" and  ");
            sb.Append(sqlWhere);
            sb.Append("  ");

            return sb.ToString();
        }

        public static string GetSelectSql<T>() where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();
            string sqlTable = LambdaToSqlHelper.GetSelectTableSql<T>();
            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append("  ");

            return sb.ToString();
        }

        #endregion 获取分页语句

        #region 获取删除语句

        public static string GetDeleteSqlByID<T>(List<int> list, out MySqlParameter[] pars) where T : class
        {
            var typeT = typeof(T);
            StringBuilder sb = new StringBuilder();
            pars = new MySqlParameter[list.Count];
            for (int i = 0; i < list.Count; i++)
            {
                sb.Append("@par" + i + ",");

                MySqlParameter p = new MySqlParameter();
                p.ParameterName = "@par" + i;
                p.Value = list[i];

                // p.SqlDbType =SqlTypeString2SqlType(ta.PropertyType.Name.ToLower());
                pars[i] = p;
            }
            string value = sb.ToString().Substring(0, sb.Length - 1);
            return "update  `" + typeT.Name.ToLower() + "` set isdelete=" + (int)CommonEnum.IsDelete.已删除 + "  where  id in(" + value + ") ";
        }

        public static string GetDeleteSqlByID<T>(int id, out MySqlParameter[] pars) where T : class
        {
            var typeT = typeof(T);
            StringBuilder sb = new StringBuilder();
            pars = new MySqlParameter[1];

            sb.Append("@par" + id + ",");

            MySqlParameter p = new MySqlParameter();
            p.ParameterName = "@par" + id;
            p.Value = id;

            // p.SqlDbType =SqlTypeString2SqlType(ta.PropertyType.Name.ToLower());
            pars[0] = p;

            string value = sb.ToString().Substring(0, sb.Length - 1);
            return "update  `" + typeT.Name.ToLower() + "` set isdelete=" + (int)CommonEnum.IsDelete.已删除 + "  where  id in(" + value + ") ";
        }

        public static string GetDeleteSqlByIDsTrue<T>(List<int> list, out MySqlParameter[] pars) where T : class
        {
            var typeT = typeof(T);
            StringBuilder sb = new StringBuilder();
            pars = new MySqlParameter[list.Count];
            for (int i = 0; i < list.Count; i++)
            {
                sb.Append("@par" + i + ",");

                MySqlParameter p = new MySqlParameter();
                p.ParameterName = "@par" + i;
                p.Value = list[i];

                // p.SqlDbType =SqlTypeString2SqlType(ta.PropertyType.Name.ToLower());
                pars[i] = p;
            }
            string value = sb.ToString().Substring(0, sb.Length - 1);
            return "delete from  `" + typeT.Name.ToLower() + "`  where  id in(" + value + ") ";
        }

        public static string DeleteEntityByWhereTrue<T>(Expression<Func<T, bool>> func, out MySqlParameter[] pars) where T : class
        {
            var typeT = typeof(T);

            List<ParMODEL> list = new List<ParMODEL>();

            string sql = "delete from  `" + typeT.Name.ToLower() + "`  where  1=1 and  " + GetWhereSql<T>(func, list);
            pars = new MySqlParameter[list.Count];
            int markPar = 0;
            foreach (var l in list)
            {
                MySqlParameter p = new MySqlParameter();
                p.ParameterName = l.name;
                p.Value = l.value;
                pars[markPar] = p;
                markPar++;
            }
            return sql;
        }

        #endregion 获取删除语句

        #region 获取数量语句

        public static string GetSelectSqlCount<T>(Expression<Func<T, bool>> where, out MySqlParameter[] parameter) where T : class
        {
            List<ParMODEL> parModelList = new List<ParMODEL>();

            string sqlWhere = LambdaToSqlHelper.GetWhereSql<T>(where, parModelList);
            parameter = LambdaToSqlHelper.GetParmetersByList(parModelList);

            string sqlTable = LambdaToSqlHelper.GetSelectTableSqlCount<T>();

            StringBuilder sb = new StringBuilder("");
            sb.Append(sqlTable);
            sb.Append("  and ");
            sb.Append(sqlWhere);
            sb.Append("  ");

            return sb.ToString();
        }

        #endregion 获取数量语句

        #endregion 实现方法

        #region 基础方法

        #region 获取条件语句方法

        private static string GetWhereSql<T>(Expression<Func<T, bool>> func, List<ParMODEL> parModelList) where T : class
        {
            string res = "";
            if (func.Body is BinaryExpression)
            {
                //起始参数

                BinaryExpression be = ((BinaryExpression)func.Body);
                res = BinarExpressionProvider(be.Left, be.Right, be.NodeType, parModelList);
            }
            else if (func.Body is MethodCallExpression)
            {
                MethodCallExpression be = ((MethodCallExpression)func.Body);
                res = ExpressionRouter(func.Body, parModelList);
            }
            else
            {
                res = "  ";
            }

            return res;
        }

        #endregion 获取条件语句方法

        #region 将引用类型传出注入参数转化为所需参数

        private static MySqlParameter[] GetParmetersByList(List<ParMODEL> parModelList)
        {
            MySqlParameter[] parameter = new MySqlParameter[parModelList.Count];
            for (int i = 0; i < parModelList.Count; i++)
            {
                MySqlParameter par = new MySqlParameter();
                par.ParameterName = "@" + parModelList[i].name;
                par.Value = parModelList[i].value;
                parameter[i] = par;
            }
            return parameter;
        }

        #endregion 将引用类型传出注入参数转化为所需参数

        #region 获取排序语句 order by

        private static string GetOrderSql<T>(Expression<Func<T, object>> exp) where T : class
        {
            var res = "";
            if (exp.Body is UnaryExpression)
            {
                UnaryExpression ue = ((UnaryExpression)exp.Body);
                List<ParMODEL> parModelList = new List<ParMODEL>();
                res = "order by `" + ExpressionRouter(ue.Operand, parModelList).ToLower() + "`";
            }
            else
            {
                MemberExpression order = ((MemberExpression)exp.Body);
                res = "order by `" + order.Member.Name.ToLower() + "`";
            }
            return res;
        }

        #endregion 获取排序语句 order by

        #region 获取select表名的 sql 语句

        private static string GetSelectTableSqlCount<T>() where T : class
        {
            var typeT = typeof(T);
            return string.Format("select count(*)  from `{0}` where 1=1 ", typeT.Name.ToLower());
        }

        private static string GetSelectTableSql<T>() where T : class
        {
            var typeT = typeof(T);

            var attrNameList = typeT.GetProperties().Select(a => a.Name).ToList();

            StringBuilder sb = new StringBuilder();
            foreach (var name in attrNameList)
            {
                sb.Append("`");
                sb.Append(name);
                sb.Append("`");
                sb.Append(",");
            }
            string cos = sb.ToString().Substring(0, sb.Length - 1);
            return string.Format("select {0} from `{1}` where 1=1 ", cos, typeT.Name.ToLower());
        }

        private static string GetSelectTableSqlPage<T>() where T : class
        {
            var typeT = typeof(T);

            var attrNameList = typeT.GetProperties().Select(a => a.Name).ToList();

            StringBuilder sb = new StringBuilder();
            foreach (var name in attrNameList)
            {
                sb.Append("`");
                sb.Append(name);
                sb.Append("`");
                sb.Append(",");
            }
            string cos = sb.ToString().Substring(0, sb.Length - 1);
            return string.Format("select SQL_CALC_FOUND_ROWS {0} from `{1}` where 1=1 ", cos, typeT.Name.ToLower());
        }

        #endregion 获取select表名的 sql 语句

        #region 获取分页的后尾句

        private static string GetTotalPage(int pageIndex, int pageSize)
        {
            return " limit " + pageSize * (pageIndex - 1) + "," + pageSize + "   ;SELECT FOUND_ROWS() as TotalCount";
        }

        #endregion 获取分页的后尾句

        #region 获取添加返回标识列语句

        public static string GetSqlIDEntity()
        {
            return "SELECT @@IDENTITY AS ID";
        }

        #endregion 获取添加返回标识列语句

        #endregion 基础方法

        #region 底层

        public static bool In<T>(this T obj, T[] array)
        {
            return true;
        }

        public static bool NotIn<T>(this T obj, T[] array)
        {
            return true;
        }

        public static bool Like(this string str, string likeStr)
        {
            return true;
        }

        public static bool NotLike(this string str, string likeStr)
        {
            return true;
        }

        private static string GetValueStringByType(object oj)
        {
            if (oj == null)
            {
                return "null";
            }
            else if (oj is ValueType)
            {
                return oj.ToString();
            }
            else if (oj is string || oj is DateTime || oj is char)
            {
                return string.Format("‘{0}‘", oj.ToString());
            }
            else
            {
                return string.Format("‘{0}‘", oj.ToString());
            }
        }

        private static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type, List<ParMODEL> parModelList)
        {
            string sb = "(";
            //先处理左边
            string reLeftStr = ExpressionRouter(left, parModelList);
            sb += reLeftStr;

            sb += ExpressionTypeCast(type);

            //再处理右边
            string tmpStr = ExpressionRouter(right, parModelList);
            if (tmpStr == "null")
            {
                if (sb.EndsWith(" ="))
                {
                    sb = sb.Substring(0, sb.Length - 2) + " is null";
                }
                else if (sb.EndsWith("<>"))
                {
                    sb = sb.Substring(0, sb.Length - 2) + " is not null";
                }
            }
            else
            {
                //添加参数
                sb += tmpStr;
            }

            return sb += ")";
        }

        private static string ExpressionRouter(Expression exp, List<ParMODEL> parModelList)
        {
            string sb = string.Empty;

            if (exp is BinaryExpression)
            {
                BinaryExpression be = ((BinaryExpression)exp);
                return BinarExpressionProvider(be.Left, be.Right, be.NodeType, parModelList);
            }
            else if (exp is MemberExpression)
            {
                MemberExpression me = ((MemberExpression)exp);
                if (!exp.ToString().StartsWith("value"))
                {
                    return me.Member.Name;
                }
                else
                {
                    var result = Expression.Lambda(exp).Compile().DynamicInvoke();
                    if (result == null)
                    {
                        return "null";
                    }
                    else if (result is ValueType)
                    {
                        ParMODEL p = new ParMODEL();
                        p.name = "par" + (parModelList.Count + 1);
                        p.value = result.ToString().ToIntByStr();
                        parModelList.Add(p);
                        //return ce.Value.ToString();
                        return "@par" + parModelList.Count;
                    }
                    else if (result is string || result is DateTime || result is char)
                    {
                        ParMODEL p = new ParMODEL();
                        p.name = "par" + (parModelList.Count + 1);
                        p.value = result.ToString();
                        parModelList.Add(p);
                        //return string.Format("‘{0}‘", ce.Value.ToString());
                        return "@par" + parModelList.Count;
                    }
                    else if (result is int[])
                    {
                        var rl = result as int[];
                        StringBuilder sbIntStr = new StringBuilder();
                        foreach (var r in rl)
                        {
                            ParMODEL p = new ParMODEL();
                            p.name = "par" + (parModelList.Count + 1);
                            p.value = r.ToString().ToIntByStr();
                            parModelList.Add(p);
                            //return string.Format("‘{0}‘", ce.Value.ToString());
                            sbIntStr.Append("@par" + parModelList.Count + ",");
                        }
                        return sbIntStr.ToString().Substring(0, sbIntStr.ToString().Length - 1);
                    }
                    else if (result is string[])
                    {
                        var rl = result as string[];
                        StringBuilder sbIntStr = new StringBuilder();
                        foreach (var r in rl)
                        {
                            ParMODEL p = new ParMODEL();
                            p.name = "par" + (parModelList.Count + 1);
                            p.value = r.ToString();
                            parModelList.Add(p);
                            //return string.Format("‘{0}‘", ce.Value.ToString());
                            sbIntStr.Append("@par" + parModelList.Count + ",");
                        }
                        return sbIntStr.ToString().Substring(0, sbIntStr.ToString().Length - 1);
                    }
                }
            }
            else if (exp is NewArrayExpression)
            {
                NewArrayExpression ae = ((NewArrayExpression)exp);
                StringBuilder tmpstr = new StringBuilder();
                foreach (Expression ex in ae.Expressions)
                {
                    tmpstr.Append(ExpressionRouter(ex, parModelList));
                    tmpstr.Append(",");
                }
                //添加参数

                return tmpstr.ToString(0, tmpstr.Length - 1);
            }
            else if (exp is MethodCallExpression)
            {
                MethodCallExpression mce = (MethodCallExpression)exp;
                string par = ExpressionRouter(mce.Arguments[0], parModelList);
                if (mce.Method.Name == "Like")
                {
                    //添加参数用
                    return string.Format("({0} like {1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
                }
                else if (mce.Method.Name == "NotLike")
                {
                    //添加参数用
                    return string.Format("({0} Not like {1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
                }
                else if (mce.Method.Name == "In")
                {
                    //添加参数用
                    return string.Format("{0} In ({1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
                }
                else if (mce.Method.Name == "NotIn")
                {
                    //添加参数用
                    return string.Format("{0} Not In ({1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
                }
            }
            else if (exp is ConstantExpression)
            {
                ConstantExpression ce = ((ConstantExpression)exp);
                if (ce.Value == null)
                {
                    return "null";
                }
                else if (ce.Value is ValueType)
                {
                    ParMODEL p = new ParMODEL();
                    p.name = "par" + (parModelList.Count + 1);
                    p.value = ce.Value.ToString().ToIntByStr();
                    parModelList.Add(p);
                    //return ce.Value.ToString();
                    return "@par" + parModelList.Count;
                }
                else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
                {
                    ParMODEL p = new ParMODEL();
                    p.name = "par" + (parModelList.Count + 1);
                    p.value = ce.Value.ToString();
                    parModelList.Add(p);
                    //return string.Format("‘{0}‘", ce.Value.ToString());
                    return "@par" + parModelList.Count;
                }

                //对数值进行参数附加
            }
            else if (exp is UnaryExpression)
            {
                UnaryExpression ue = ((UnaryExpression)exp);

                return ExpressionRouter(ue.Operand, parModelList);
            }
            return null;
        }

        private static string ExpressionTypeCast(ExpressionType type)
        {
            switch (type)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    return " AND ";

                case ExpressionType.Equal:
                    return " =";

                case ExpressionType.GreaterThan:
                    return " >";

                case ExpressionType.GreaterThanOrEqual:
                    return ">=";

                case ExpressionType.LessThan:
                    return "<";

                case ExpressionType.LessThanOrEqual:
                    return "<=";

                case ExpressionType.NotEqual:
                    return "<>";

                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    return " Or ";

                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                    return "+";

                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                    return "-";

                case ExpressionType.Divide:
                    return "/";

                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                    return "*";

                default:
                    return null;
            }
        }

        #endregion 底层
    }

    #region 表达式累加 不支持ef 慎用，把问题复杂化了

    public static class PredicateBuilder
    {
        public static Expression<Func<T, bool>> True<T>()
        {
            return f => true;
        }

        public static Expression<Func<T, bool>> False<T>()
        {
            return f => false;
        }

        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // build parameter map (from parameters of second to parameters of first)
            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.And);
        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.Or);
        }
    }

    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;
            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }

    #endregion 表达式累加 不支持ef 慎用，把问题复杂化了

    public class SqlParMODEL
    {
        public string sql { set; get; }

        private List<ParMODEL> parList { set; get; }
    }

    public class ParMODEL
    {
        public string name { set; get; }

        public object value { set; get; }
    }
}