MESProductionWeb/AntORM/Ant.ORM/DataBaise/MakeSql/SqlConditionList.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace Ant.ORM.MakeSql
{

    #region 定义牧举
    public enum SqlLogicOper
    {
        /// <summary>
        /// 并且
        /// </summary>
        AND,     // AND
        /// <summary>
        /// 或者
        /// </summary>
        OR,      // OR
    }
    public enum SqlCompareOper
    {
        /// <summary>
        /// 大于
        /// </summary>
        MORE_THAN,     // >
        /// <summary>
        /// 小于
        /// </summary>
        LESS_THAN,     // <
        /// <summary>
        /// 小于等于
        /// </summary>
        NOT_MORE_THAN, // <=
        /// <summary>
        /// 大于等于
        /// </summary>
        NOT_LESS_THAN, // >=
        /// <summary>
        /// 等于
        /// </summary>
        EQUAL,         // =
        /// <summary>
        /// 不等于
        /// </summary>
        NOT_EQUAL,     // <>
        /// <summary>
        /// 模糊匹配
        /// </summary>
        LIKE,          // LIKE
        /// <summary>
        /// 不匹配
        /// </summary>
        NOT_LIKE,      // NOT LIKE
        /// <summary>
        /// 包含
        /// </summary>
        IN,            // IN
        /// <summary>
        /// 区间
        /// </summary>
        BETWEEN,       // BETWEEN
    }
    #endregion

    /// <summary>
    /// SQL条件集合，
    /// 负责处理各个条件的合并和最终SQL的生成。
    /// </summary>
    public class SqlConditionList
    {
        // 缓存所有条件
        private ArrayList _condList = new ArrayList();

        public SqlConditionList()
        {
        }
        /// <summary>
        /// 添加一个条件
        /// </summary>
        /// <param name="cond">条件</param>
        public SqlConditionList Add(SqlCondition cond)
        {
            _condList.Add(cond);
            return this;
        }

        /// <summary>
        /// 添加一个条件
        /// </summary>
        /// <param name="condition">条件</param>
        /// <param name="logicOper">与前一个条件的关系</param>
        public SqlConditionList AddOrLogic(SqlCondition condition)
        {
            SqlCondition cond = null;
            if (_condList.Count > 0)
            {
                SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
                cond = Merge(lastCond, condition, SqlLogicOper.OR);
            }
            else
            {
                cond = condition;
            }
            //remove all exit condition
            this.Clear(cond);
            _condList.Add(cond);
            return this;
        }

        /// <summary>
        /// 添加一个条件
        /// </summary>
        /// <param name="condition">条件</param>
        /// <param name="logicOper">与前一个条件的关系</param>
        public SqlConditionList AddAndLogic(SqlCondition condition)
        {
            SqlCondition cond = null;
            if (_condList.Count > 0)
            {
                SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
                cond = Merge(lastCond, condition, SqlLogicOper.AND);
            }
            else
            {
                cond = condition;
            }
            //remove all exit condition
            this.Clear(cond);
            _condList.Add(cond);
            return this;
        }

        /// <summary>
        /// 合并两个条件，合二为一。
        /// </summary>
        /// <param name="cond1"></param>
        /// <param name="cond2"></param>
        /// <param name="logicOper"></param>
        /// <returns></returns>
        public static SqlCondition Merge(SqlCondition cond1, SqlCondition cond2, SqlLogicOper logicOper)
        {
            return new SqlConditionRelation(cond1, cond2, logicOper);
        }

        /// <summary>
        /// 生成SQL语句。
        /// </summary>
        /// <returns></returns>
        public string ToSql()
        {
            StringBuilder buff = new StringBuilder();
            foreach (SqlCondition cond in _condList)
            {
                buff.Append(cond.ToSql());
            }
            return buff.ToString();
        }

        /// <summary>
        /// 去除条件
        /// </summary>
        /// <param name="condition"></param>
        private void Clear(SqlCondition condition)
        {
            if (_condList.Contains(condition))
            {
                _condList.Remove(condition);
            }

            if (condition.GetType() == typeof(SqlConditionRelation))
            {
                SqlConditionRelation richCond =

                             (SqlConditionRelation)condition;
                this.Clear(richCond._cond1);
                this.Clear(richCond._cond2);
            }
        }
    }


    /// <summary>
    /// SQL条件，也就是WHERE部分。
    /// </summary>
    public class SqlCondition
    {
        private static string[] _compareOpers = new string[] {
                " > ", " < ", " <= ", " >= ", " = ", " <> ",
                " LIKE ", " NOT LIKE ", " IN " , " BETWEEN "};

        private string _filedName;
        private object _value;
        private SqlCompareOper _compareOper;
        private string _templateName;

        protected SqlCondition()
        {
        }

        public SqlCondition(SqlCompareOper compareOper,
            string fieldName, object value)
        {
            _compareOper = compareOper;
            _filedName = fieldName;
            _value = value;
        }

        // 生成条件的SQL
        public virtual string ToSql()
        {
            if (_value == null)
            {
                throw new Exception("Can not parse SQL because value is null.");
            }
            StringBuilder buff = new StringBuilder();
            if (_compareOper == SqlCompareOper.IN)
            {
                if (!_value.GetType().IsSubclassOf(typeof(Array)))
                {
                    throw new Exception("Can not parse [IN].");
                }
                buff.Append(_filedName);
                buff.Append(" IN (");
                Array arrVal = (Array)_value;
                foreach (object val in arrVal)
                {
                    buff.Append(this.ToValueSqlString(val)).Append(",");
                }
                buff.Remove(buff.Length - 1, 1);
                buff.Append(") ");
            }
            else if (_compareOper == SqlCompareOper.BETWEEN)
            {
                if (!_value.GetType().IsSubclassOf(typeof(Array)))
                {
                    throw new Exception("Can not parse [BETWEEN].");
                }
                Array arrVal = (Array)_value;
                if (arrVal.Length != 2)
                {
                    throw new Exception("Can not parse [BETWEEN].");
                }
                buff.Append(" (");
                buff.Append(_filedName);
                buff.Append(" BETWEEN ");
                buff.Append(arrVal.GetValue(0));
                buff.Append(" AND ");
                buff.Append(arrVal.GetValue(1));
                buff.Append(") ");
            }
            else
            {
                buff.Append(_filedName);
                buff.Append(_compareOpers[(int)_compareOper]);
                buff.Append(this.ToValueSqlString(_value));
            }
            return buff.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private string ToValueSqlString(object value)
        {
            StringBuilder buff = new StringBuilder();
            if (value.GetType() == typeof(Int16)
                || value.GetType() == typeof(Int32)
                || value.GetType() == typeof(Int64)
                || value.GetType() == typeof(Decimal)
                || value.GetType() == typeof(Single)
                || value.GetType() == typeof(Double))
            {
                buff.Append(value);
            }
            else if (value.GetType() == typeof(Boolean))
            {
                if ((bool)value)
                {
                    buff.Append(1);
                }
                else
                {
                    buff.Append(0);
                }
            }
            else if (value.GetType() == typeof(DateTime))
            {
                string dValue = ((DateTime)value).
                    ToString("yyyy-MM-dd hh:mm:ss.fff");
                buff.Append("'").Append(dValue).Append("'");
            }
            else
            {
                string sValue = value.ToString().Replace("'", "''");
                buff.Append("'").Append(sValue).Append("'");
            }
            return buff.ToString();
        }
    }



    /// <summary>
    /// 两个条件合并后的条件。
    /// </summary>
    public class SqlConditionRelation : SqlCondition
    {
        private static string[] _logicOpers = new string[] { " AND ", " OR " };
        internal SqlLogicOper _logicOper;
        internal SqlCondition _cond1;
        internal SqlCondition _cond2;

        internal SqlConditionRelation(SqlCondition cond1,
            SqlCondition cond2, SqlLogicOper logicOper)
        {
            _cond1 = cond1;
            _cond2 = cond2;
            _logicOper = logicOper;
        }

        public override string ToSql()
        {
            StringBuilder buff = new StringBuilder();
            buff.Append(_cond1.ToSql())
                .Append(_logicOpers[(int)_logicOper])
                .Append(_cond2.ToSql());
            if (_logicOper == SqlLogicOper.OR)
            {
                buff.Insert(0, " (");
                buff.Append(") ");
            }
            return buff.ToString();
        }

    }
}