using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using NetLibrary.RegularExpressions;

namespace NetLibrary.Data
{
    public class RefParameter
    {
        public RefParameter()
        {
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
        }

        public RefParameter(string name, ConditionType Condition, Object Value, DbType Type)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = ConvertConditionType(Condition);
            this.Type = Type;
            this.Value = Value;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, ConditionTypeNull Condition)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = ConvertConditionTypeNull(Condition);
            this.Type = DbType.String;
            this.Value = "";

        }

        public RefParameter(string name, string Condition, Object Value, DbType Type)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            if (Condition == null || Condition == "") throw new Exception("条件不能为空");
            CheckCondition(Condition);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = Condition;
            this.Type = Type;
            this.Value = Value;
            CheckValue(Value, Type);
        }

        /// <summary>
        /// 创建一个参数
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="Condition">关系符</param>
        /// <param name="Value">值</param>
        /// <param name="Type">数据类型</param>
        /// <param name="IsDeclare">是否生成组合条件语句(true默认生成,false不生成但传入参数变量,变量名为@加name属性)</param>
        public RefParameter(string name, string Condition, Object Value, DbType Type, bool IsDeclare)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            if (Condition == null || Condition == "") throw new Exception("条件不能为空");
            CheckCondition(Condition);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Type = Type;
            this.Condition = Condition;
            this.Value = Value;
            this.IsDeclare = IsDeclare;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, ConditionType Condition, Object Value, DbType Type, bool IsDeclare)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = ConvertConditionType(Condition);
            this.Type = Type;
            this.Value = Value;
            this.IsDeclare = IsDeclare;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, string Condition, Object Value, string RelateTo, DbType Type)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            if (Condition == null || Condition == "") throw new Exception("条件不能为空");
            CheckCondition(Condition);
            CheckRelateTo(RelateTo);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = Condition;
            this.Type = Type;
            this.Value = Value;
            this.RelateTo = RelateTo;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, string Condition, Object Value, string RelateTo, string EndString, DbType Type)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            if (Condition == null || Condition == "") throw new Exception("条件不能为空");
            CheckCondition(Condition);
            CheckRelateTo(RelateTo);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = Condition;
            this.EndString = EndString;
            this.Type = Type;
            this.Value = Value;
            this.RelateTo = RelateTo;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, ConditionType Condition, Object Value, string RelateTo, DbType Type)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            CheckRelateTo(RelateTo);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Type = Type;
            this.Name = name;
            this.Condition = ConvertConditionType(Condition);
            this.Value = Value;
            this.RelateTo = RelateTo;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, string Condition, Object Value, string RelateTo, DbType Type, ParameterDirection Direction)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            if (Condition == null || Condition == "") throw new Exception("条件不能为空");
            CheckCondition(Condition);
            CheckRelateTo(RelateTo);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = Condition;
            this.Type = Type;
            this.Value = Value;
            this.RelateTo = RelateTo;
            this.Direction = Direction;
            CheckValue(Value, Type);
        }

        public RefParameter(string name, ConditionType Condition, Object Value, string RelateTo, DbType Type, ParameterDirection Direction)
        {
            if (name == null || name == "") throw new Exception("字段名称不能为空");
            CheckRelateTo(RelateTo);
            this.Caption = Guid.NewGuid().ToString().Replace("-", "");
            this.Name = name;
            this.Condition = ConvertConditionType(Condition);
            this.Type = Type;
            this.Value = Value;
            this.RelateTo = RelateTo;
            this.Direction = Direction;
            CheckValue(Value, Type);
        }

        private void CheckRelateTo(string RelateTo)
        {
            if (RelateTo != "and" && RelateTo != "or")
            {
                throw new Exception("多个条件之间关系只能是and或者or");
            }
        }

        private void CheckCondition(string Condition)
        {//检测条件是否有错
            if (Condition != ">=" && Condition != "<=" && Condition != ">" && Condition != "<" && Condition != "=" && Condition != "<>" && Condition != "in" && Condition != "not in" && Condition != "like left" && Condition != "like right" && Condition != "like" && Condition != "is" && Condition != "is not") throw new Exception("查询条件只能为>,<,=,<>,in, not in,like left,like right,like,is,is not");
        }

        #region 转换ConditionType条件
        private string ConvertConditionType(ConditionType ctype)
        {
            switch (ctype)
            {
                case ConditionType.Equals:
                    return "=";
                case ConditionType.Ceiling:
                    return ">";
                case ConditionType.CeilingEqual:
                    return ">=";
                case ConditionType.Floor:
                    return "<";
                case ConditionType.FloorEqual:
                    return "<=";
                case ConditionType.NotEquals:
                    return "<>";
                case ConditionType.In:
                    return "in";
                case ConditionType.NotIn:
                    return "not in";
                case ConditionType.LikeLeft:
                    return "like left";
                case ConditionType.LikeRight:
                    return "like right";
                case ConditionType.Like:
                    return "like";
                default:
                    return "";
            }
        }
        #endregion

        #region 转换ConditionTypeNull条件
        private string ConvertConditionTypeNull(ConditionTypeNull ctype)
        {
            switch (ctype)
            {
                case ConditionTypeNull.Is:
                    return "is";
                case ConditionTypeNull.IsNot:
                    return "is not";
                default:
                    return "";
            }
        }
        #endregion

        private void CheckValue(Object Value, DbType Type)
        {//检测数据类型是否错误
            if (Value == null || Value.ToString() == "" || Type == DbType.String) return;
            if (this.Condition == "in" || this.Condition == "not in")
            {
                string[] chars1 = Value.ToString().Split(new char[] { ',' });
                foreach (string s in chars1)
                {
                    if (Type == DbType.Byte || Type == DbType.Int16 || Type == DbType.Int32 || Type == DbType.Int64)
                    {
                        if (DataRegex.IsNumberInteger(s) == false) throw new Exception(this.Name + ":" + Value.ToString() + ":只能是整数");
                    }
                    else
                    {
                        if (DataRegex.IsNumberDouble(s) == false) throw new Exception(this.Name + ":" + Value.ToString() + ":只能是浮点数");
                    }
                }
                return;
            }
            if (Type == DbType.DateTime)
            {
                try { Convert.ToDateTime(Value); }
                catch { throw new Exception(Value.ToString() + ":只能是日期格式"); }
            }
            if (Type == DbType.Byte || Type == DbType.Int16 || Type == DbType.Int32 || Type == DbType.Int64)
            {
                if (DataRegex.IsNumberInteger(Value.ToString()) == false) throw new Exception(this.Name + ":" + Value.ToString() + ":只能是整数");
            }
            if (Type == DbType.Currency || Type == DbType.Single || Type == DbType.Decimal || Type == DbType.Double)
            {
                if (DataRegex.IsNumberDouble(Value.ToString()) == false) throw new Exception(this.Name + ":" + Value.ToString() + ":只能是浮点数");
            }
            if (Type == DbType.Boolean)
            {
                if (Value.ToString() != "True" && Value.ToString() != "False" && Value.ToString() != "0" && Value.ToString() != "1")
                {
                    throw new Exception(this.Name + ":" + Value.ToString() + ":只能是True、False、0、1");
                }
            }
        }

        private string m_Caption;

        /// <summary>
        /// 列标题
        /// </summary>
        public string Caption
        {
            get { return m_Caption; }
            set { m_Caption = value; }
        }

        private string m_Name;

        /// <summary>
        /// 参数名称
        /// </summary>
        public string Name
        {
            get { return m_Name; }
            set { m_Name = value; }
        }

        private string m_Condition;

        /// <summary>
        /// 条件(等于,大于,小于,不等于,in, not in,like,like left,like right)
        /// </summary>
        public string Condition
        {
            get { return m_Condition; }
            set
            {
                CheckCondition(value);
                m_Condition = value;
            }
        }

        private Object m_Value;

        /// <summary>
        /// 值
        /// </summary>
        public Object Value
        {
            get { return m_Value; }
            set
            {
                CheckValue(value, this.Type);
                if (value != null && value != DBNull.Value && this.Type == DbType.String) { m_Value = value.ToString().Replace("'", "''"); }
                else { m_Value = value; }
            }
        }

        private string m_RelateTo = "and";

        /// <summary>
        /// 关系符(and,or)默认为and
        /// </summary>
        public string RelateTo
        {
            get { return m_RelateTo; }
            set { m_RelateTo = value; }
        }
        private string m_EndString = "";
        /// <summary>
        /// 条件结束符号(常用于括号运算)
        /// </summary>
        public string EndString
        {
            get { return m_EndString; }
            set { m_EndString = value; }
        }
        private System.Data.DbType m_Type = DbType.Object;
        /// <summary>
        /// 数据类型
        /// </summary>
        public System.Data.DbType Type
        {
            get { return m_Type; }
            set { m_Type = value; }
        }
        private ParameterDirection m_Direction = ParameterDirection.Input;
        /// <summary>
        /// 参数类型
        /// </summary>
        public ParameterDirection Direction
        {
            get { return m_Direction; }
            set { m_Direction = value; }
        }
        private bool m_IsDeclare = true;
        /// <summary>
        /// 是否生成组合条件语句(true默认生成,false不生成但传入参数变量,变量名为@加name属性)
        /// </summary>
        public bool IsDeclare
        {
            get { return m_IsDeclare; }
            set { m_IsDeclare = value; }
        }
    }

    [Serializable]
    public class RefParameterCollection : List<RefParameter>
    {
        private int m_PageIndex = 1;
        /// <summary>
        /// 分页码
        /// </summary>
        public int PageIndex
        {
            get { return m_PageIndex; }
            set { m_PageIndex = value; }
        }
        private int m_PageSize = 10;
        /// <summary>
        /// 每页显示行数
        /// </summary>
        public int PageSize
        {
            get { return m_PageSize; }
            set { m_PageSize = value; }
        }

        public RefParameterCollection()
        { }

        public RefParameterCollection(int PageIndex, int PageSize)
        {
            this.PageIndex = PageIndex;
            this.PageSize = PageSize;
        }

        public void Add(string name, ConditionType Condition, Object Value, DbType Type)
        {
            this.Add(new RefParameter(name, Condition, Value, Type));
        }
        public void Add(string name, ConditionTypeNull Condition)
        {
            this.Add(new RefParameter(name, Condition));
        }

        public void Add(string name, string Condition, Object Value, DbType Type)
        {
            this.Add(new RefParameter(name, Condition, Value, Type));
        }

        /// <summary>
        /// 创建一个参数
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="Condition">关系符</param>
        /// <param name="Value">值</param>
        /// <param name="Type">数据类型</param>
        /// <param name="IsDeclare">是否生成组合条件语句(true默认生成,false不生成但传入参数变量,变量名为@加name属性)</param>
        public void Add(string name, string Condition, Object Value, DbType Type, bool IsDeclare)
        {
            this.Add(new RefParameter(name, Condition, Value, Type, IsDeclare));
        }

        public void Add(string name, ConditionType Condition, Object Value, DbType Type, bool IsDeclare)
        {
            this.Add(new RefParameter(name, Condition, Value, Type, IsDeclare));
        }

        public void Add(string name, string Condition, Object Value, string RelateTo, DbType Type)
        {
            this.Add(new RefParameter(name, Condition, Value, RelateTo, Type));
        }

        public void Add(string name, string Condition, Object Value, string RelateTo, string EndString, DbType Type)
        {
            this.Add(new RefParameter(name, Condition, Value, RelateTo, EndString, Type));
        }

        public void Add(string name, ConditionType Condition, Object Value, string RelateTo, DbType Type)
        {
            this.Add(new RefParameter(name, Condition, Value, RelateTo, Type));
        }

        public void Add(string name, string Condition, Object Value, string RelateTo, DbType Type, ParameterDirection Direction)
        {
            this.Add(new RefParameter(name, Condition, Value, RelateTo, Type, Direction));
        }

        public void Add(string name, ConditionType Condition, Object Value, string RelateTo, DbType Type, ParameterDirection Direction)
        {
            this.Add(new RefParameter(name, Condition, Value, RelateTo, Type, Direction));
        }

        public void AddOutParameter(string name, DbType Type)
        {
            this.Add(new RefParameter(name, "=", null, "and", Type, ParameterDirection.Output));
        }

        public bool Contains(string name)
        {
            foreach (RefParameter pam in this)
            {
                if (pam.Name == name) return true;

            }
            return false;
        }

        public RefParameter this[string name]
        {
            get
            {
                foreach (RefParameter cf in this)
                {
                    if (cf.Name == name) return cf;
                }
                return null;
            }
        }

        public void Remove(string name)
        {
            foreach (RefParameter cf in this)
            {
                if (cf.Name == name) { this.Remove(cf); return; }
            }
        }

        #region 转义特殊字符
        private string ConvertUnionChar(string value)
        {
            string a = value;
            a = a.ToString().Replace("*", "[*]");
            //a = a.ToString().Replace("~", "[~]");
            //a = a.ToString().Replace("(", "[(]");
            //a = a.ToString().Replace(")", "[)]");
            //a = a.ToString().Replace("#", "[#]");
            //a = a.ToString().Replace("/", "[/]");
            //a = a.ToString().Replace("=", "[=]");
            //a = a.ToString().Replace(">", "[>]");
            //a = a.ToString().Replace("<", "[<]");
            //a = a.ToString().Replace("+", "[+]");
            //a = a.ToString().Replace("-", "[-]");
            //a = a.ToString().Replace("|", "[|]");
            //a = a.ToString().Replace("^", "[^]");
            //a = a.ToString().Replace("%", "[%]");
            //a = a.ToString().Replace("&", "[&]");
            return a;
        }
        #endregion

        #region 返回向DataTable查询条件
        public string GetDataTableWhere()
        {
            string tsql = "";
            foreach (RefParameter pam in this)
            {
                string value = pam.Value.ToString();
                if (pam.Type == DbType.String) value = ConvertUnionChar(value);
                if (this.IndexOf(pam) == 0)
                {
                    switch (pam.Condition)
                    {
                        case "in":
                            if (pam.Type != DbType.String)
                            {
                                tsql = pam.Name + " in (" + value + ")" + pam.EndString;
                            }
                            else
                            {
                                string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
                                string ss = "";
                                foreach (string s in chars1)
                                {
                                    if (ss == "") { ss = "'" + s + "'"; }
                                    else { ss = ss + ",'" + s + "'"; }
                                }
                                tsql = pam.Name + " in (" + ss + ")" + pam.EndString;
                            }
                            break;
                        case "not in":
                            if (pam.Type != DbType.String)
                            {
                                tsql = pam.Name + " not in (" + value + ")" + pam.EndString;
                            }
                            else
                            {
                                string[] chars2 = value.Split(new char[] { ',' });
                                string ss = "";
                                foreach (string s in chars2)
                                {
                                    if (ss == "") { ss = "'" + s + "'"; }
                                    else { ss = ss + ",'" + s + "'"; }
                                }

                                tsql = pam.Name + " not in (" + ss + ")" + pam.EndString;
                            }
                            break;
                        case "like":
                            tsql = pam.Name + " like '%" + value + "%'" + pam.EndString;
                            break;
                        case "like left":
                            tsql = pam.Name + " like '%" + value + "'" + pam.EndString;
                            break;
                        case "like right":
                            tsql = pam.Name + " like '" + value + "%'" + pam.EndString;
                            break;
                        case "is":
                            tsql = pam.Name + " is null" + pam.EndString;
                            break;
                        case "is not":
                            tsql = pam.Name + " is not null" + pam.EndString;
                            break;
                        default:
                            if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                            {
                                tsql = pam.Name + "=''" + pam.EndString;
                            }
                            else if (pam.Value == null || pam.Value == DBNull.Value)
                            {
                                tsql = pam.Name + " is null" + pam.EndString;
                            }
                            else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
                            {
                                tsql = pam.Name + pam.Condition + "'" + value + "'" + pam.EndString;
                            }
                            else
                            {
                                tsql = pam.Name + pam.Condition + value + pam.EndString;
                            }
                            break;
                    }
                }
                else
                {
                    switch (pam.Condition)
                    {
                        case "in":
                            if (pam.Type != DbType.String)
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + value + ")" + pam.EndString;
                            }
                            else
                            {
                                string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
                                string ss = "";
                                foreach (string s in chars3)
                                {
                                    if (ss == "") { ss = "'" + s + "'"; }
                                    else { ss = ss + ",'" + s + "'"; }
                                }

                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + ss + ")" + pam.EndString;
                            }
                            break;
                        case "not in":
                            if (pam.Type != DbType.String)
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + value + ")" + pam.EndString;
                            }
                            else
                            {
                                string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
                                string ss = "";
                                foreach (string s in chars4)
                                {
                                    if (ss == "") { ss = "'" + s + "'"; }
                                    else { ss = ss + ",'" + s + "'"; }
                                }

                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + ss + ")" + pam.EndString;
                            }
                            break;
                        case "like":
                            tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + value + "%'" + pam.EndString;
                            break;
                        case "like left":
                            tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + value + "'" + pam.EndString;
                            break;
                        case "like right":
                            tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '" + value + "%'" + pam.EndString;
                            break;
                        case "is":
                            tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                            break;
                        case "is not":
                            tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null" + pam.EndString;
                            break;
                        case "<>":
                           if (Convert.ToString(pam.Value) == "")
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <> ''" + pam.EndString;
                                    else tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <>" + pam.Value + pam.EndString;
                                    break;
                        default:
                            if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + "=''" + pam.EndString;
                            }
                            else if (pam.Value == null || pam.Value == DBNull.Value)
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                            }
                            else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "'" + value + "'" + pam.EndString;
                            }
                            else
                            {
                                tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + value + pam.EndString;
                            }

                            break;
                    }
                }
            }
            return tsql;
        }
        #endregion

        #region 返回条件参数化连接语句
        public string GetWhere(CommandType CommandType)
        {
            string tsql = "";
            if (this.Count != 0)
            {
                if (CommandType == CommandType.Text)
                {
                    foreach (RefParameter pam in this)
                    {
                        if (pam.Direction != ParameterDirection.Input) continue;
                        if (pam.IsDeclare == false) continue; //不生成条件
                        if (tsql.IndexOf("where") == -1)
                        {
                            switch (pam.Condition)
                            {
                                case "in":
                                    tsql = tsql + " where " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
                                    string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
                                    for (int i = 1; i < chars1.Length; i++)
                                    {
                                        tsql = tsql + " ,@" + pam.Caption + i.ToString();
                                    }
                                    tsql = tsql + ")" + pam.EndString;
                                    break;
                                case "not in":
                                    tsql = tsql + " where " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
                                    string[] chars2 = pam.Value.ToString().Split(new char[] { ',' });
                                    for (int i = 1; i < chars2.Length; i++)
                                    {
                                        tsql = tsql + " ,@" + pam.Caption + i.ToString();
                                    }
                                    tsql = tsql + ")" + pam.EndString;
                                    break;
                                case "like":
                                    tsql = tsql + " where " + pam.Name + " like '%'+" + "@" + pam.Caption + "+'%'" + pam.EndString;
                                    break;
                                case "like left":
                                    tsql = tsql + " where " + pam.Name + " like '%'+" + "@" + pam.Caption + pam.EndString;
                                    break;
                                case "like right":
                                    tsql = tsql + " where " + pam.Name + " like " + "@" + pam.Caption + "+'%'" + pam.EndString;
                                    break;
                                case "is":
                                    tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
                                    break;
                                case "is not":
                                    tsql = tsql + " where " + pam.Name + " is not null" + pam.EndString;
                                    break;
                                default:
                                    if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                                    {
                                        tsql = tsql + " where " + pam.Name + "=''" + pam.EndString;
                                    }
                                    else if (pam.Value == null || pam.Value == DBNull.Value)
                                    {
                                        tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
                                    }
                                    else
                                    {
                                        tsql = tsql + " where " + pam.Name + pam.Condition + "@" + pam.Caption + pam.EndString;
                                    }
                                    break;
                            }
                        }
                        else
                        {
                            switch (pam.Condition)
                            {
                                case "in":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
                                    string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
                                    for (int i = 1; i < chars3.Length; i++)
                                    {
                                        tsql = tsql + " ,@" + pam.Caption + i.ToString();
                                    }
                                    tsql = tsql + ")" + pam.EndString;
                                    break;
                                case "not in":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
                                    string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
                                    for (int i = 1; i < chars4.Length; i++)
                                    {
                                        tsql = tsql + " ,@" + pam.Caption + i.ToString();
                                    }
                                    tsql = tsql + ")" + pam.EndString;
                                    break;
                                case "like":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%'+" + "@" + pam.Caption + "+'%'" + pam.EndString;
                                    break;
                                case "like left":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%'+" + "@" + pam.Caption + pam.EndString;
                                    break;
                                case "like right":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like " + "@" + pam.Caption + "+'%'" + pam.EndString;
                                    break;
                                case "is":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                                    break;
                                case "is not":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null" + pam.EndString;
                                    break;
                                case "<>":
                                    if (Convert.ToString(pam.Value) == "")
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <> ''" + pam.EndString;
                                    else tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <>" + pam.Value + pam.EndString;
                                    break;
                                default:
                                    if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + "=''" + pam.EndString;
                                    }
                                    else if (pam.Value == null || pam.Value == DBNull.Value)
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                                    }
                                    else
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "@" + pam.Caption + pam.EndString;
                                    }
                                    break;
                            }
                        }
                    }
                }
                else
                {
                    foreach (RefParameter pam in this)
                    {
                        if (pam.Direction != ParameterDirection.Input) continue;
                        if (pam.IsDeclare == false) continue; //不生成条件
                        if (tsql.IndexOf("where") == -1)
                        {
                            switch (pam.Condition)
                            {
                                case "in":
                                    if (pam.Type != DbType.String)
                                    {
                                        tsql = tsql + " where " + pam.Name + " in (" + pam.Value.ToString() + ")" + pam.EndString;
                                    }
                                    else
                                    {
                                        string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
                                        string ss = "";
                                        foreach (string s in chars1)
                                        {
                                            if (ss == "") { ss = "'" + s + "'"; }
                                            else { ss = ss + ",'" + s + "'"; }
                                        }

                                        tsql = tsql + " where " + pam.Name + " in (" + ss + ")" + pam.EndString;
                                    }
                                    break;
                                case "not in":
                                    if (pam.Type != DbType.String)
                                    {
                                        tsql = tsql + " where " + pam.Name + " not in (" + pam.Value.ToString() + ")" + pam.EndString;
                                    }
                                    else
                                    {
                                        string[] chars2 = pam.Value.ToString().Split(new char[] { ',' });
                                        string ss = "";
                                        foreach (string s in chars2)
                                        {
                                            if (ss == "") { ss = "'" + s + "'"; }
                                            else { ss = ss + ",'" + s + "'"; }
                                        }

                                        tsql = tsql + " where " + pam.Name + " not in (" + ss + ")" + pam.EndString;
                                    }
                                    break;
                                case "like":
                                    tsql = tsql + " where " + pam.Name + " like '%" + pam.Value.ToString() + "%'" + pam.EndString;
                                    break;
                                case "like left":
                                    tsql = tsql + " where " + pam.Name + " like '%" + pam.Value.ToString() + "'" + pam.EndString;
                                    break;
                                case "like right":
                                    tsql = tsql + " where " + pam.Name + " like '" + pam.Value.ToString() + "%'" + pam.EndString;
                                    break;
                                case "is":
                                    tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
                                    break;
                                case "is not":
                                    tsql = tsql + " where " + pam.Name + " is not null" + pam.EndString;
                                    break;
                                default:

                                    if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                                    {
                                        tsql = " where " + pam.Name + "=''" + pam.EndString;
                                    }
                                    else if (pam.Value == null || pam.Value == DBNull.Value)
                                    {
                                        tsql = " where " + pam.Name + " is null" + pam.EndString;
                                    }
                                    else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
                                    {
                                        tsql = " where " + pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'" + pam.EndString;
                                    }
                                    else
                                    {
                                        tsql = " where " + pam.Name + pam.Condition + pam.Value.ToString() + pam.EndString;
                                    }
                                    break;
                            }

                        }
                        else
                        {
                            switch (pam.Condition)
                            {
                                case "in":
                                    if (pam.Type != DbType.String)
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + pam.Value.ToString() + ")" + pam.EndString;
                                    }
                                    else
                                    {
                                        string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
                                        string ss = "";
                                        foreach (string s in chars3)
                                        {
                                            if (ss == "") { ss = "'" + s + "'"; }
                                            else { ss = ss + ",'" + s + "'"; }
                                        }

                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + ss + ")" + pam.EndString;
                                    }
                                    break;
                                case "not in":
                                    if (pam.Type != DbType.String)
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + pam.Value.ToString() + ")" + pam.EndString;
                                    }
                                    else
                                    {
                                        string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
                                        string ss = "";
                                        foreach (string s in chars4)
                                        {
                                            if (ss == "") { ss = "'" + s + "'"; }
                                            else { ss = ss + ",'" + s + "'"; }
                                        }

                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + ss + ")" + pam.EndString;
                                    }
                                    break;
                                case "like":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "%'" + pam.EndString;
                                    break;
                                case "like left":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "'" + pam.EndString;
                                    break;
                                case "like right":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '" + pam.Value.ToString() + "%'" + pam.EndString;
                                    break;
                                case "is":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                                    break;
                                case "is not":
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null" + pam.EndString;
                                    break;
                                case "<>":
                                   if (Convert.ToString(pam.Value) == "")
                                    tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <> ''" + pam.EndString;
                                    else tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " <>" + pam.Value + pam.EndString;
                                    break;
                                default:
                                    if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + "=''" + pam.EndString;
                                    }
                                    else if (pam.Value == null || pam.Value == DBNull.Value)
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
                                    }
                                    else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'" + pam.EndString;
                                    }
                                    else
                                    {
                                        tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + pam.Value.ToString() + pam.EndString;
                                    }

                                    break;
                            }
                        }
                    }
                }
            }
            return tsql;
        }
        #endregion

        #region 返回参数化数组
        public System.Data.SqlClient.SqlParameter[] GetParameter()
        {
            List<System.Data.SqlClient.SqlParameter> list = new List<System.Data.SqlClient.SqlParameter>();
            if (this.Count == 0) return list.ToArray();
            int i = 0;
            foreach (RefParameter pam in this)
            {
                int index = this.IndexOf(pam);
                if (pam.Direction == ParameterDirection.Input)
                {
                    if (pam.IsDeclare == true)
                    {
                        if (pam.Condition == "in" || pam.Condition == "not in")
                        {
                            string[] chars = pam.Value.ToString().Split(new char[] { ',' });
                            i = 0;
                            foreach (string s in chars)
                            {
                                list.Add(new System.Data.SqlClient.SqlParameter() { ParameterName = pam.Caption + i.ToString(), DbType = pam.Type, Value = s });
                                i++;
                            }
                        }
                        else
                        {
                            if (pam.Value != null)
                            {
                                list.Add(new System.Data.SqlClient.SqlParameter() { ParameterName = pam.Caption, DbType = pam.Type, Value = pam.Value });
                            }
                        }
                    }
                    else
                    {
                        list.Add(new System.Data.SqlClient.SqlParameter() { ParameterName = pam.Name, DbType = pam.Type, Value = pam.Value });
                    }
                }
                else
                {
                    list.Add(new System.Data.SqlClient.SqlParameter() { ParameterName = pam.Name, DbType = pam.Type, Direction = ParameterDirection.Output });
                }
            }
            return list.ToArray();
        }
        #endregion

        #region 返回参数化数组
        public System.Data.OleDb.OleDbParameter[] GetOledbParameter()
        {
            List<System.Data.OleDb.OleDbParameter> list = new List<System.Data.OleDb.OleDbParameter>();
            if (this.Count == 0) return list.ToArray();
            int i = 0;
            foreach (RefParameter pam in this)
            {
                int index = this.IndexOf(pam);
                if (pam.Direction == ParameterDirection.Input)
                {
                    if (pam.IsDeclare == true)
                    {
                        if (pam.Condition == "in" || pam.Condition == "not in")
                        {
                            string[] chars = pam.Value.ToString().Split(new char[] { ',' });
                            i = 0;
                            foreach (string s in chars)
                            {
                                list.Add(new System.Data.OleDb.OleDbParameter() { ParameterName = pam.Caption + i.ToString(), DbType = pam.Type, Value = s });
                                i++;
                            }
                        }
                        else
                        {
                            if (pam.Value != null)
                            {
                                list.Add(new System.Data.OleDb.OleDbParameter() { ParameterName = pam.Caption, DbType = pam.Type, Value = pam.Value });
                            }
                        }
                    }
                    else
                    {
                        list.Add(new System.Data.OleDb.OleDbParameter() { ParameterName = pam.Name, DbType = pam.Type, Value = pam.Value });
                    }
                }
                else
                {
                    list.Add(new System.Data.OleDb.OleDbParameter() { ParameterName = pam.Name, DbType = pam.Type, Direction = ParameterDirection.Output });
                }
            }
            return list.ToArray();
        }
        #endregion
    }

    public enum ConditionType
    {
        /// <summary>
        /// 等于=
        /// </summary>
        Equals = 0,
        /// <summary>
        /// 大于>
        /// </summary>
        Ceiling = 1,
        /// <summary>
        /// 大于等于(>=)
        /// </summary>
        CeilingEqual = 2,
        /// <summary>
        /// 小于(<)
        /// </summary>
        Floor = 3,
        /// <summary>
        /// 小于等于(<=)
        /// </summary>
        FloorEqual = 4,
        /// <summary>
        /// 不等于(<>)
        /// </summary>
        NotEquals = 5,
        /// <summary>
        /// in
        /// </summary>
        In = 6,
        /// <summary>
        /// not in
        /// </summary>
        NotIn = 7,
        /// <summary>
        /// like left
        /// </summary>
        LikeLeft = 8,
        /// <summary>
        /// like right
        /// </summary>
        LikeRight = 9,
        /// <summary>
        /// like
        /// </summary>
        Like = 10
    }

    public enum ConditionTypeNull
    {
        /// <summary>
        /// is
        /// </summary>
        Is = 0,
        /// <summary>
        /// is not
        /// </summary>
        IsNot = 1
    }

    public enum EditDataType
    {
        /// <summary>
        /// 当前使用DataSet或是DataTable数据源
        /// </summary>
        DataSetIndex,
        /// <summary>
        /// 当前使用DataView数据源
        /// </summary>
        DataViewIndex
    }
}