MESProductionWeb/AntORM/Ant.ORM/DataBaise/SQLite/SQLiteQueryCommandBuilder.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using Ant.Frame;
using Ant.Data;
using System.Reflection;
using Ant.Descriptors;
using Ant.Common;
using System.Data;

namespace Ant.ORM
{
    class SQLiteQueryCommandBuilder : QueryCommandBuilder
    {
        public override QueryCommand GetQueryCommand()
        {
            QueryCommand cmd = null;
            EntityPersistType str = (!object.Equals(PersistType, null)) ? PersistType : Md.PersistType;
            switch (str)
            {
                case EntityPersistType.Save:
                    cmd = ParserInsertCommand();
                    break;
                case EntityPersistType.Update:
                    cmd = ParserUpdateCommand();
                    break;
                case EntityPersistType.Delete:
                    cmd = ParserDeleteCommand();
                    break;
                case EntityPersistType.Query:
                    cmd = ParserSelectCommand();
                    break;
                case EntityPersistType.Sum:
                    cmd = ParserSumCommand();
                    break;
            }
            return cmd;

        }
        /// <summary>
        /// 解析Insert SQL
        /// </summary>
        /// <returns></returns>
        private QueryCommand ParserInsertCommand()
        {
            QueryCommand qc = new QueryCommand(); bool IsAutoincrement = false;
            List<string> columnlist = new List<string>();
            List<string> paramlist = new List<string>();
            List<string> valuelist = new List<string>();

            var typedesc = Md.FiledMeta.MemberColumnMap;
            if (Md.FiledMeta.AutoIncrement.IfNotNull())
            {
                IsAutoincrement = true;
                typedesc.Remove(Md.FiledMeta.AutoIncrement.MemberInfo);
            }
            foreach (var kv in typedesc)
            {
                MemberInfo member = kv.Key;
                Md.FiledMeta.Fields.Add(member);
                MappingMemberDescriptor memberDescriptor = MetaDataManager.TryGetMappingMemberDescriptor(kv.Key, Md.FiledMeta);
                //var val = memberDescriptor.Column.Value;
                var val = Md.FieldsColumns[member.Name].Value;
                if (!MetaDataManager.IsAutoIncrement(memberDescriptor, Md))
                {
                    columnlist.Add(string.Format("[{0}]", memberDescriptor.Column.Name));
                    paramlist.Add("@" + memberDescriptor.Column.Name);
                    QueryParameter parameter = new QueryParameter();
                    parameter.ParameterName = "@" + memberDescriptor.Column.Name;
                    parameter.DbType = (DbType)AntUtils.TryGetDbType(memberDescriptor.Column.Type);
                    parameter.Direction = ParameterDirection.Input;
                    if (val.IsNull())
                        val = SetDefaultValue(parameter.DbType);
                    parameter.Value = val;
                    valuelist.Add("'" + val + "'");
                    qc.Parameters.Add(parameter);
                }
            }

            string sqlstr = string.Format(ConstSql.SQL_INSERT_TABLE, Md.FiledMeta.Table.Name, columnlist.AppendStrings(), paramlist.AppendStrings(),
               IsAutoincrement ? GetLastInsertID() : string.Empty);
            qc.CommandText = sqlstr;
            qc.SqlString = string.Format(ConstSql.SQL_INSERT_TABLE, Md.FiledMeta.Table.Name, columnlist.AppendStrings(),
               valuelist.AppendStrings(), IsAutoincrement ? GetLastInsertID() : string.Empty);
            return qc;
        }
        private QueryCommand ParserUpdateCommand()
        {
            QueryCommand qc = new QueryCommand(); string strwhere = string.Empty;
            StringBuilder sql = new StringBuilder();//SQL语句
            StringBuilder sqlstr = new StringBuilder();
            List<string> updateColumns = new List<string>();//更新列
            List<string> StrupdateColumns = new List<string>();
            List<string> WhereColumn = new List<string>();//条件列
            List<string> StrWhereColumn = new List<string>();//条件列
            sql.AppendFormat("UPDATE [{0}] SET ", Md.FiledMeta.Table.Name);

            List<string> condition = new List<string>();


            #region 更新字段

            if (Md.UpdateColumns.IsNull())
            {
                var typedesc = Md.FiledMeta.MemberColumnMap;
                foreach (var kv in typedesc)
                {
                    MemberInfo member = kv.Key;
                    MappingMemberDescriptor memberDescriptor = MetaDataManager.TryGetMappingMemberDescriptor(member, Md.FiledMeta);
                    if (!MetaDataManager.IsAutoIncrement(memberDescriptor, Md))
                    {
                        var column = memberDescriptor.Column;
                        updateColumns.Add(string.Format("[{0}]=@{0}", column.Name));
                        qc.Parameters.Add("@" + column.Name, (DbType)AntUtils.TryGetDbType(column.Type), column.Value);
                        StrupdateColumns.Add(string.Format("[{0}]='{1}'", column.Name, column.Value));
                    }
                }
            }
            else
            {
                foreach (var column in Md.UpdateColumns)
                {
                    if (column.Value.Value.IfNotNull())
                    {
                        updateColumns.Add(string.Format("[{0}]=@{0}", column.Key));
                        qc.Parameters.Add("@" + column.Key, (DbType)AntUtils.TryGetDbType(column.Value.Type), column.Value.Value);
                        StrupdateColumns.Add(string.Format("[{0}]='{1}'", column.Key, column.Value.Value));
                    }
                }
            }


            #endregion

            if (Md.FiledMeta.PrimaryKey.Column.Value.IfNotNull())//通过主键作用查询条件
            {
                WhereColumn.Add(string.Format("[{0}]=@{0}", Md.FiledMeta.PrimaryKey.MemberInfo.Name));
                qc.Parameters.Add("@" + Md.FiledMeta.PrimaryKey.Column.Name, (DbType)AntUtils.TryGetDbType(Md.FiledMeta.PrimaryKey.Column.Type), Md.FiledMeta.PrimaryKey.Column.Value);
                StrWhereColumn.Add(string.Format("[{0}]='{1}'", Md.FiledMeta.PrimaryKey.Column.Name, Md.FiledMeta.PrimaryKey.Column.Value));
            }
            else
            {//通过查询实体进行查询

                #region 给实体赋值的查询条件更新
                foreach (var key in Md.WhereColumns.Values)
                {
                    WhereColumn.Add(string.Format("[{0}]=@{0}", key.Name));
                    qc.Parameters.Add("@" + key.Name, (DbType)AntUtils.TryGetDbType(key.Type), key.Value);
                    StrWhereColumn.Add(string.Format("[{0}]={1}", key.Name, key.Value));
                }
                #endregion

            }

            if (updateColumns.Count > 0)//更新字段
            {

                #region 带时间戳
                if (Md.FieldsColumns.ContainsKey("Version"))
                {
                    //WhereColumn.Add(string.Format(" {0}+0=@{0}", Md.FieldMeteDatas["Version"].ColumnName));
                    WhereColumn.Add(string.Format(" CONVERT(bigint,{0})=:{0}", Md.FieldsColumns["Version"].Name));
                    qc.Parameters.Add("@" + Md.FieldsColumns["Version"].Name, (DbType)AntUtils.TryGetDbType(Md.FieldsColumns["Version"].Type), Md.FieldsColumns["Version"].Value);
                    StrWhereColumn.Add(string.Format(" CONVERT(bigint,{0})='{1}'", Md.FieldsColumns["Version"].Name, Md.FieldsColumns["Version"].Value));
                }
                #endregion

                sql.Append(string.Join(",", updateColumns.ToArray())).Append(" WHERE ").Append(string.Join(" AND ", WhereColumn.ToArray()));
                if (!string.IsNullOrEmpty(Md.WhereSql))
                {//有自定义查询条件的时候
                    if (Md.WhereSql.Trim().Length > 0) WhereColumn.Add(Md.WhereSql);
                }
                if (Predicate.IfNotNull())
                {
                    var FlieldWhere = new FieldValueCollection();
                    strwhere = Predicate.GetSql(FlieldWhere, qc);//拼接SQL语句
                    if (!strwhere.IsEmpty())
                    {
                        sql.AppendFormat("  and  {0} ", strwhere);
                        foreach (string key in FlieldWhere.Keys)
                        {
                            strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
                        }
                        sqlstr.AppendFormat("  and  {0} ", strwhere);
                    }

                }


                qc.CommandText = sql.ToString();
                qc.SqlString = string.Format(ConstSql.SQL_UPDATE_TABLE, Md.FiledMeta.Table.Name, string.Join(",", StrupdateColumns.ToArray()), string.Join(" AND ", StrWhereColumn.ToArray()) + (strwhere.IsEmpty() ? strwhere : " and " + strwhere));
            }
            return qc;
        }
        private QueryCommand ParserDeleteCommand()
        {
            QueryCommand qc = new QueryCommand();
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlstr = new StringBuilder();
            List<string> WhereColumn = new List<string>();//条件列集合
            List<string> WhereColumnStr = new List<string>();
            if (!Md.FieldsColumns.IsNull())
            {
                foreach (string key in Md.FieldsColumns.Keys)//查询条件
                {
                    //if (!Object.Equals(Md.Fields[key], null) || !Object.Equals(Md.Fields[key], DBNull.Value))
                    if (!(Md.FieldsColumns[key].IsNull()))
                    {
                        if (!Md.FieldsColumns[key].Value.ToString().IsEmpty())
                        {
                            WhereColumn.Add(string.Format(" [{0}].{1}=@{2} ", Md.FiledMeta.Table.Name, Md.FieldsColumns[key].Name, Md.FieldsColumns[key].Name));
                            qc.Parameters.Add("@" + Md.FieldsColumns[key].Name, (DbType)AntUtils.TryGetDbType(Md.FieldsColumns[key].Type), Md.FieldsColumns[key].Value);
                            WhereColumnStr.Add(string.Format(" [{0}].{1}={2} ", Md.FiledMeta.Table.Name, Md.FieldsColumns[key].Name, Md.FieldsColumns[key].Value));
                        }
                    }
                }
            }

            if (!Md.WhereSql.IsNull())
            {
                if (Md.WhereSql.Trim().Length > 0)
                {
                    string sqlwherestr = (Md.WhereSql.StartsWith("(") && Md.WhereSql.EndsWith(")")) ? Md.WhereSql : " (" + Md.WhereSql + ") ";
                    WhereColumn.Add(sqlwherestr);
                    WhereColumnStr.Add(sqlwherestr);
                }
            }

            sql.AppendFormat(ConstSql.SQL_DELETE_DATA, Md.FiledMeta.Table.Name, (WhereColumn.Count > 0) ? string.Format(" and {0}", string.Join(" AND ", WhereColumn.ToArray())) : string.Empty);
            sqlstr.AppendFormat(ConstSql.SQL_DELETE_DATA, Md.FiledMeta.Table.Name, (WhereColumnStr.Count > 0) ? string.Format(" and {0}", string.Join(" AND ", WhereColumnStr.ToArray())) : string.Empty);
            if (Predicate.IfNotNull())
            {
                var FlieldWhere = new FieldValueCollection();
                string strwhere = Predicate.GetSql(FlieldWhere, qc);//拼接SQL语句
                if (!strwhere.IsEmpty())
                {
                    sql.AppendFormat("  and  {0} ", strwhere);
                    foreach (string key in FlieldWhere.Keys)
                    {
                        strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
                    }
                    sqlstr.AppendFormat("  and  {0} ", strwhere);
                }
            }
            qc.CommandText = sql.ToString();
            qc.SqlString = sqlstr.ToString();
            return qc;
        }
        private QueryCommand ParserSelectCommand()
        {
            QueryCommand qc = new QueryCommand() { DbParmChar = DbParmChar };
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlstr = new StringBuilder();
            switch (SqlType)
            {
                case SqlEnum.MainSql:
                    {
                        string topnum = Top.IsEmpty() ? string.Empty : Top;
                        sql.AppendFormat(ConstSql.MYSQL_GET_VALUE, FieldColumn(), Md.FiledMeta.Table.Name);//显示自定义的字段
                        List<string> WhereColumn = new List<string>();//条件列集合
                        List<string> WhereColumnStr = new List<string>();//条件列集合
                        if (Md.FieldsColumns.IfNotNull())//查询条件
                        {
                            foreach (string key in Md.FieldsColumns.Keys)
                            {
                                var field = Md.FieldsColumns[key];
                                if (!(field.Value.IsNull()))
                                {
                                    if (!field.Value.ToString().IsEmpty())
                                    {
                                        WhereColumn.Add(string.Format(" [{0}].{1}=@{2} ", Md.FiledMeta.Table.Name, field.Name,
                                            field.Name));
                                        qc.Parameters.Add("@" + field.Name, (DbType)AntUtils.TryGetDbType(field.Type), field.Value);
                                        WhereColumnStr.Add(string.Format(" [{0}].{1}='{2}' ", Md.FiledMeta.Table.Name, field.Name, field.Value));
                                    }
                                }
                            }
                        }
                        else
                        {
                            if (!Md.WhereSql.IsEmpty()) sql.AppendFormat(" and {0} ", Md.WhereSql);
                        }
                        sqlstr.Append(sql.ToString());
                        if (WhereColumn.Count > 0) sql.AppendFormat(" and {0} ", string.Join(" AND ", WhereColumn.ToArray()));
                        if (WhereColumnStr.Count > 0) sqlstr.AppendFormat(" and {0} ", string.Join(" AND ", WhereColumnStr.ToArray()));
                        if (Predicate.IfNotNull())
                        {
                            var FlieldWhere = new FieldValueCollection();
                            string strwhere = Predicate.GetSql(FlieldWhere, qc);//拼接SQL语句
                            if (!strwhere.IsEmpty())
                            {
                                sql.AppendFormat("  and  {0} ", strwhere);
                                foreach (string key in FlieldWhere.Keys)
                                {
                                    strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
                                }
                                sqlstr.AppendFormat("  and  {0} ", strwhere);
                            }

                        }
                        string orderstr = GetOrderBy();
                        sql.Append(orderstr);
                        sql.AppendFormat(" limit {0}", topnum);
                        sqlstr.Append(orderstr);
                        sqlstr.AppendFormat(" limit {0}", topnum);
                        WhereColumn = null; WhereColumnStr = null;
                    }
                    break;
            }
            qc.CommandText = sql.ToString();
            qc.SqlString = sqlstr.ToString();
            sqlstr = null; sql = null;
            return qc;
        }


        #region 解析 查询分页 SQL语句
        /// <summary>
        /// 解析 Select SQL语句
        /// </summary>
        /// <returns></returns>
        private QueryCommand ParserQueryPageCommand()
        {
            List<string> WhereColumn = new List<string>();
            List<string> WhereColumnStr = new List<string>();//条件列集合
            QueryCommand qc = new QueryCommand() { DbParmChar = DbParmChar };
            StringBuilder sql = new StringBuilder();



            if (Md.FieldsColumns.IfNotNull())//查询条件
            {
                foreach (string key in Md.FieldsColumns.Keys)
                {
                    var field = Md.FieldsColumns[key];
                    if (!(field.Value.IsNull()))
                    {
                        if (!field.Value.ToString().IsEmpty())
                        {
                            string parmname = string.Format("@{0}_{1}", field.Name, qc.Parameters.Count);
                            WhereColumn.Add(string.Format(" [{0}].{1}=:{2} ", Md.FiledMeta.Table.Name, field.Name,
                                parmname));
                            qc.Parameters.Add(":" + parmname, (DbType)AntUtils.TryGetDbType(field.Type), field.Value);
                            WhereColumnStr.Add(string.Format(" [{0}].{1}='{2}' ", Md.FiledMeta.Table.Name, field.Name, field.Value));
                        }
                    }
                }
            }



            string strsqlwhere = string.Join(" AND ", WhereColumn.ToArray());

            if (Md.WhereSql.Length > 0) strsqlwhere = strsqlwhere + " and " + Md.WhereSql;
            if (Predicate.IfNotNull())
            {
                var FlieldWhere = new FieldValueCollection();
                string strwhere = Predicate.GetSql(FlieldWhere, qc);//拼接SQL语句
                if (!strwhere.IsEmpty())
                {
                    strsqlwhere = strsqlwhere + " and " + strwhere;
                }

            }

            sql.AppendFormat(ConstSql.MYSQL_PAGE_VALUE, FieldColumn(), Md.FiledMeta.Table.Name, strsqlwhere, GetOrderBy(), (((PageNo - 1) * PageSize) + 1), (PageNo * PageSize));

            if (!qc.SqlNotes.IsNull())
                qc.CommandText = sql.ToString();
            else
                qc.CommandText = sql.ToString();
            string sqlstr = sql.ToString();
            qc.SqlString = sqlstr;
            return qc;
        }
        #endregion


        private QueryCommand ParserSumCommand()
        {
            QueryCommand qc = new QueryCommand();
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT COUNT(*) FROM {0}", Md.FiledMeta.Table.Name);
            if (!string.IsNullOrEmpty(Md.WhereSql))
            {
                sql.AppendFormat(" WHERE {0}", Md.WhereSql);
            }
            qc.CommandText = sql.ToString();
            return qc;
        }


        /// <summary>
        /// 获取显示字段列
        /// </summary>
        /// <returns></returns>
        private string FieldColumn()
        {
            List<string> columns = new List<string>();
            string fieldcolumn = string.Empty;
            if (!ShowField.IsEmpty())
            {
                fieldcolumn = ShowField;
            }
            else
            {
                foreach (MemberInfo df in Md.FiledMeta.MemberColumnMap.Keys)//显示字段
                {
                    columns.Add(string.Format("{0}", "[" + Md.FiledMeta.Table.Name + "]." + df.Name));
                }
                fieldcolumn = string.Join(",", columns.ToArray());
            }
            fieldcolumn = fieldcolumn.IsEmpty() ? " * " : fieldcolumn;
            return fieldcolumn;
        }


        /// <summary>
        /// 获取排序
        /// </summary>
        /// <returns></returns>
        public string GetOrderBy()
        {
            string orderbystr = string.Empty;
            if (Sort.IsNull())
            {

                orderbystr = (orderbystr.Trim().IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) != -1) ? string.Format(" {0} ", Md.FiledMeta.PrimaryKey.MemberInfo.Name) : string.Format(" order by  {0}", Md.FiledMeta.PrimaryKey.MemberInfo.Name);
            }
            else
            {
                string orderby = string.Empty;
                foreach (ISort sort in Sort)
                {
                    if (orderby.IsEmpty())
                    {
                        orderby = sort.PropertyName + (sort.Ascending ? " ASC" : " DESC");
                    }
                    else
                    {
                        orderby += " ," + sort.PropertyName + (sort.Ascending ? " ASC" : " DESC");
                    }
                }
                orderbystr = string.Format(" ORDER BY {0} ", orderby);
            }
            return orderbystr;
        }


        /// <summary>
        /// 获取默认值
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        private object SetDefaultValue(DbType ColumnType)
        {
            object value = null;
            switch (ColumnType)
            {
                case DbType.String:
                    {
                        value = "";
                    }
                    break;
                case DbType.Boolean:
                    {
                        value = false;
                    }
                    break;
                case DbType.Decimal:
                case DbType.Double:
                case DbType.Int32:
                case DbType.Int16:
                case DbType.Int64:
                    {
                        value = 0;
                    }
                    break;
                case DbType.DateTime:
                    {
                        value = DateTime.Now;
                    }
                    break;
            }
            return value;

        }

        /// <summary>
        /// 自增长
        /// </summary>
        /// <returns></returns>
        public string GetLastInsertID()
        {
            return "select last_insert_rowid()";
        }
    }
}