MESProductionWeb/AntORM/Ant.ORM/MoreSql/MakeMsJoinSql.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using Ant.Data;
using System.Diagnostics;

namespace Ant.ORM
{
    public class MakeMsJoinSql : SqlQuery
    {
        public string SelectColumn = "";
        public string JoinTable = "";
        private static volatile MakeMsJoinSql instance = null;
        private static object lockHelper = new object();
        public StringPlus StrShowSql = new StringPlus("Select "); //组装外显的SQL语句
        public StringPlus StrSql = new StringPlus(); //组装SQL语句

        public SqlEntity sqlmod = new SqlEntity();
        public MakeMsJoinSql()
        {

            StrShowSql = new StringPlus("Select "); //组装外显的SQL语句
            StrSql = new StringPlus(); //组装SQL语句
        }

        #region 赋值为了组装SQL语句

        /// <summary>
        /// 查询外显字段
        /// </summary>
        /// <param name="colume"></param>
        /// <returns></returns>
        public  SqlQuery Select(MakeShowFields colume)
        {
            sqlmod.FieldColumns = colume;
            return this;
        }
        /// <summary>
        /// 查询外显字段
        /// </summary>
        /// <param name="colume"></param>
        /// <returns></returns>
        public  SqlQuery Select()
        {
            sqlmod.FieldColumns = null;
            return this;
        }

        /// <summary>
        /// Top条数
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        public  SqlQuery Top(int num)
        {
            sqlmod.StrTop = num;
            return this;
        }
        /// <summary>
        /// 分页方法
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="pageno"></param>
        /// <returns></returns>
        public SqlQuery PageSize(int pagesize, int pageno)
        {
            sqlmod.PageSize = pagesize;
            sqlmod.PageNo = pageno;
            return this;
        }

        /// <summary>
        /// 查询主表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strasname"></param>
        /// <returns></returns>
        public  SqlQuery From<T>(string strasname)
        {
            sqlmod.StrFrom = typeof(T).Name;
            sqlmod.StrAsName = strasname;
            return this;
        }

        /// <summary>
        /// 关联表
        /// </summary>
        /// <param name="jointable"></param>
        /// <returns></returns>
        public  SqlQuery Join(MakeJoinTable jointable)
        {
            sqlmod.JoinTable = jointable;
            return this;
        }


        /// <summary>
        /// 查询条件
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public  SqlQuery Where(MakeQueryWhere where)
        {
            sqlmod.QueryWhere = where;
            return this;
        }

        /// <summary>
        /// 排序条件
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public  SqlQuery OrderBy(MakeOrderBy orderBy)
        {
            sqlmod.OrderBy = orderBy;
            foreach (var orderByModel in orderBy.OrderByList)
            {
                var tableAsName = GetTableAsName(orderByModel.TableName);
                foreach (var col in orderByModel.StrOrderBy)
                {
                    sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + "." + col + " " + orderByModel.SortType + " ,";
                }
            }
            return this;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="strlist"></param>
        /// <returns></returns>
        public  SqlQuery Group(params string[] strlist)
        {

            return this;
        }

        /// <summary>
        /// 降序排序
        /// </summary>
        /// <param name="columns">The columns.</param>
        /// <returns></returns>
        public  SqlQuery OrderDesc<T>(params string[] columns)
        {
            string tableAsName = GetTableAsName(typeof(T).Name);
            foreach (string column in columns)
            {
                sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + "." + column + " Desc ,";
            }

            return this;
        }

        /// <summary>
        /// 降序排序
        /// </summary>
        /// <param name="columns">The columns.</param>
        /// <returns></returns>
        public  SqlQuery OrderAsc<T>(params string[] columns)
        {
            string tableAsName = GetTableAsName(typeof(T).Name);
            foreach (string column in columns)
            {
                sqlmod.StrOrderBy = sqlmod.StrOrderBy + " " + tableAsName + " . " + column + " asc ,";
            }
            return this;
        }

 

        #endregion




        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <returns></returns>
        public ResponseModel GetEnSingle<T>(SqlNote sqlNote, DataAccess db = null)
        {
            var enty = new ResponseModel();
            try
            {
                sqlmod.StrTop = 1;
                var dt = GetDtList(null, db);
                var mod = DataToModel.Dt2EnList<T>(dt.DataTable);
                enty.IsSuccess = true;
                enty.ResultModel = mod.FirstOrDefault();
                enty.StrSql = StrShowSql.ToString();
                return enty;
            }
            catch (Exception ex)
            {
               
            }
            return enty;
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public ResponseModel GetDtList(SqlNote sqlNote, DataAccess db = null)
        {
            var response = new ResponseModel();
            try
            {
                if (sqlNote.IfNotNull())
                {
                    if(sqlNote.NewSt.IsNull())
                    {
                        response.Message = "没有获取到方法的具体行号";
                        return response;
                    }
                    if (sqlNote.Author.IsEmpty())
                    {
                        response.Message = "方法作者没有赋值";
                        return response;
                    }
                    if(sqlNote.SqlDesc.IsEmpty())
                    {
                        response.Message = "方法的备注没有赋值";
                        return response;
                    }
                    var dtlist = DataTableList(sqlNote, db);
                    if (dtlist.IsHaveRows())
                    {
                        response.IsSuccess = true;
                        response.ResultModel = dtlist;
                        response.StrSql = StrShowSql.ToString();
                        response.Message = "获取数据成功";
                    }
                }
                
            }
            catch (Exception ex)
            {
               
            }

            return response;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public ResponseModel GetEnList<T>(SqlNote newSt, DataAccess db = null)
        {
            var listmod = new ResponseModel();
            try
            {
                var dtlist = DataTableList(null, db);
                var modlist = DataToModel.Dt2EnList<T>(dtlist);
                listmod.ResultModel = modlist;
                listmod.IsSuccess = modlist.Count > 0;
            }
            catch (Exception ex)
            {
            }

            return listmod;
        }

       /// <summary>
        /// 分页方法
       /// </summary>
       /// <param name="pageno"></param>
       /// <param name="pagesize"></param>
       /// <param name="db"></param>
       /// <returns></returns>
        public ResponseModel GetPageSite(int pageno, int pagesize, SqlNote newSt, DataAccess db = null)
        {

            var response = new ResponseModel();
            try
            {
                sqlmod.PageSize = pagesize;
                sqlmod.PageNo = pageno;
                var dtt = PageSiteDatable(newSt,db);
                if (dtt.Rows.Count > 0)
                {
                    response.IsSuccess = true;
                    response.DataTable = dtt;
                    response.Message = "查询DataTable返回成功";
                }
            }
            catch (Exception ex)
            {
               
            }

            return response;
        }





        #region 组装SQL语句
        /// <summary>
        /// 获取分页DataTable
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        private DataTable PageSiteDatable(SqlNote newSt, DataAccess db)
        {
            string columnName; object columnValue; string tableName; string tableAsName = string.Empty; DbType dbtype; string overcolumnName;
 

            StrShowSql.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER (");
            if (string.IsNullOrEmpty(sqlmod.StrOrderBy))
            {
                StrShowSql.Append(" order by ");
                StrShowSql.Append(sqlmod.StrAsName + "." + sqlmod.StrOrderBy);
            }
            else
            {
                //主键
                StrShowSql.Append(" order by ");
            }
            StrShowSql.Append(") AS rowNumber,");
            var showfiled = sqlmod.FieldColumns;

            #region 拼接显示字段
            if (showfiled.IsNull())
            {
                StrShowSql.Append(" * ");
            }
            else
            {
                foreach (FieldColumnsList fieldList in showfiled.Values)
                { 
                    tableName = fieldList.TableName;
                    tableAsName = GetTableAsName(tableName);
                    foreach (FieldColumns feild in fieldList.FieldList)
                    {
                        if (!feild.FieldAsName.IsEmpty())
                            StrShowSql.Append(tableAsName + "." + feild.FieldColumn + " as " + feild.FieldAsName + ",");
                        else
                            StrShowSql.Append(tableAsName + "." + feild.FieldColumn + ",");
                        StrShowSql.AppendLine();
                    }
                }
            }
            #endregion

            StrShowSql.DelLastComma();
            StrShowSql.Append(" from ");
            StrShowSql.Append(sqlmod.StrFrom + " as " + sqlmod.StrAsName + " with (nolock) ");

            #region 拼接关联表
            foreach (JoinOnTableNode jion in sqlmod.JoinTable.RootList)
            {
                string tableAsName1 = GetTableAsName(jion.TableName); string tableAsName2 = GetTableAsName(jion.NTableName);
                StrShowSql.Append(JoinSql.GetJoinTypeValue(jion.JoinType) + " " + jion.NTableName + " as " + jion.NTableAsName);
                StrShowSql.Append(" " + tableAsName1 + "." + jion.JoinOn1 + "=" + tableAsName2 + "." + jion.JoinOn2);
                StrShowSql.AppendLine();
            }
            #endregion

            StrShowSql.Append(" where 1=1 ");
            StrSql.Append(StrShowSql);//把外显示SQL组装带到参数的SQL中去
            MakeQueryWhere where = sqlmod.QueryWhere;

            #region 拼接查询条件
            if (where.IfNotNull())
            {
                foreach (QueryWhereList queryList in where.QueryList.Values)
                {
                    foreach (TableColumn col in queryList.QueryList)
                    {
                        columnName = col.ColumnName;
                        overcolumnName = (col.OverColumnName.IsEmpty()) ? columnName : col.OverColumnName;
                        columnValue = col.ColumnValue;
                        dbtype = col.ColumnType;
                        tableName = col.TableName;
                        tableAsName = GetTableAsName(tableName) + "." + columnName;
                        if (col.Operatstr==Operator.Like)
                        {
                            StrShowSql.Append(queryList.WhereStr + tableAsName +
                                              QueryOperator.GetOperator(col.Operatstr) + " %" +
                                              GetNativeType(dbtype, columnValue) + "% ");
                            StrSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) +
                                          "@" + overcolumnName + " ESCAPE '!' ");
                            var parameter = new QueryParameter();
                            parameter.ParameterName = "@" + overcolumnName;
                            parameter.Value = string.Format("%{0}%", GetEscape(columnValue.ToString()));
                            parameter.DbType = dbtype;
                            sqlmod.Parameters.Add(parameter);
                        }
                        else
                        {
                            StrShowSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) + GetNativeType(dbtype, columnValue));
                            StrSql.Append(queryList.WhereStr + tableAsName + QueryOperator.GetOperator(col.Operatstr) + "@" +
                                          overcolumnName);
                            var parameter = new QueryParameter();
                            parameter.ParameterName = "@" + overcolumnName;
                            parameter.Value = columnValue;
                            parameter.DbType = dbtype;
                            sqlmod.Parameters.Add(parameter);
                        }
                        
                    }
                }
            }
            #endregion

            StrSql.AppendFormat(") AS sp WHERE rowNumber BETWEEN {0}  and {1} ", (sqlmod.PageNo + 1), (sqlmod.PageNo + sqlmod.PageSize));

            if (db.IsNull())
                db = DataAccessFactory.GetWriteDataDefault;
            StrSql.Append(GetSqlComments(newSt));
            IDataReader rdr = db.ExecuteDataReader(StrSql.ToString(), sqlmod.Parameters);
            DataTable dtt = DataToModel.Dr2Dt(rdr);
            return dtt;
        }
        /// <summary>
        /// 获取DataTableList
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        private DataTable DataTableList(SqlNote newSt, DataAccess db)
        {
            string columnName; object columnValue; string tableName; string tableAsName = string.Empty; DbType dbtype;
            string overcolumnName;
            if (sqlmod.StrTop > 0)
                StrShowSql.AppendFormat(" top {0} ", sqlmod.StrTop);

            #region 拼接显示字段
            var showfiled = sqlmod.FieldColumns;
            if (showfiled.IsNull())
            {
                StrShowSql.Append(" * ");
            }
            else
            {
                foreach (FieldColumnsList fieldList in showfiled.Values)
                {
                    tableAsName = GetTableAsName(fieldList.TableName);
                    foreach (FieldColumns feild in fieldList.FieldList)
                    {
                        if (!feild.FieldAsName.IsEmpty())
                            StrShowSql.Append(tableAsName + "." + feild.FieldColumn + " as " + feild.FieldAsName + ",");
                        else
                            StrShowSql.Append(tableAsName + "." + feild.FieldColumn + ",");
                    }
                    StrShowSql.AppendLine();
                }
            }
            #endregion
            StrShowSql.DelLastComma();
           
            StrShowSql.Append(" from " + sqlmod.StrFrom + " as " + sqlmod.StrAsName + " with (nolock) ");
            StrShowSql.AppendLine();

            #region 拼接关联表
            foreach (JoinOnTableNode jion in sqlmod.JoinTable.RootList)
            {
                string tableAsName1 = GetTableAsName(jion.TableName); string tableAsName2 = GetTableAsName(jion.NTableName);
                StrShowSql.Append(JoinSql.GetJoinTypeValue(jion.JoinType) + " " + jion.NTableName + " as " + jion.NTableAsName + " with (nolock) on ");
                StrShowSql.Append(tableAsName1 + "." + jion.JoinOn1 + "=" + tableAsName2 + "." + jion.JoinOn2);
                StrShowSql.AppendLine();
            }
            #endregion

            StrShowSql.Append(" where 1=1 ");
            StrSql.Append(StrShowSql);//把外显示SQL组装带到参数的SQL中去

            #region 拼接查询条件
            MakeQueryWhere where = sqlmod.QueryWhere;
            if (where.IfNotNull())
            {
                foreach (QueryWhereList queryList in where.QueryList.Values)
                {
                    if(queryList.QueryList.Count>0)
                    {
                        StrSql.Append("(");
                        StrShowSql.Append("(");   
                    }
                    foreach (TableColumn col in queryList.QueryList)
                    {
                        columnName = col.ColumnName;
                        overcolumnName = (col.OverColumnName.IsEmpty()) ? columnName : col.OverColumnName;
                        columnValue = col.ColumnValue;
                        dbtype = col.ColumnType;
                        tableName = col.TableName;
                        tableAsName = GetTableAsName(tableName) + "." + columnName;
                        StrShowSql.Append(tableAsName + QueryOperator.GetOperator(col.Operatstr) + GetNativeType(dbtype, columnValue) + queryList.WhereStr);
                        StrSql.Append(tableAsName + QueryOperator.GetOperator(col.Operatstr) + "@" + overcolumnName + queryList.WhereStr);
                        var parameter = new QueryParameter();
                        parameter.ParameterName = "@" + overcolumnName;
                        parameter.Value = columnValue;
                        parameter.DbType = dbtype;
                        sqlmod.Parameters.Add(parameter);
                    }
                    if(queryList.QueryList.Count>0)
                    {
                        StrShowSql.DelLastChar(" and ");
                        StrShowSql.DelLastChar(" or ");
                        StrSql.DelLastChar(" and ");
                        StrSql.DelLastChar(" or ");
                        StrSql.Append(")");
                        StrShowSql.Append(")");    
                    }
                }
            }
            #endregion

            if (sqlmod.StrOrderBy.IfNotNull())
            {
                if (sqlmod.StrOrderBy.EndsWith(","))
                    sqlmod.StrOrderBy = sqlmod.StrOrderBy.TrimEnd(',');
                StrShowSql.Append(" order by " + sqlmod.StrOrderBy);
                StrSql.Append(" order by " + sqlmod.StrOrderBy);
            }
            if (db.IsNull())
                db = DataAccessFactory.GetWriteDataDefault;
            StrSql.Append(GetSqlComments(newSt));
            IDataReader rdr = db.ExecuteDataReader(StrSql.ToString(), sqlmod.Parameters);
            DataTable dtt = DataToModel.Dr2Dt(rdr);
            return dtt;
        }

        #endregion


        #region 返回sql注释
        /// <summary>
        /// 返回sql注释
        /// </summary>
        /// <param name="newSt">调用方法new StackTrace(true)</param>
        /// <param name="author">sql作者</param>
        /// <param name="sqlDesc">sql说明</param>
        /// <returns></returns>
        /// <remarks>
        ///  added by 季健国 at 2015-3-7
        /// </remarks>
        public static string GetSqlComments(SqlNote sqlNote)
        {
            try
            {
                StackFrame stackFrame = sqlNote.NewSt.GetFrame(0);
                StringBuilder commetBuilder = new StringBuilder();
                commetBuilder.AppendFormat("/*项目名称:{0}/作者:{1}/备注:{2}/文件:///{3}/方法:{4}/行号：{5}*/","某某项目", sqlNote.Author, sqlNote.SqlDesc, stackFrame.GetFileName(), stackFrame.GetMethod().Name, stackFrame.GetFileLineNumber());
                return commetBuilder.ToString();
            }
            catch
            {
                //默认许可按此处理
                return "";
            }
        }
        #endregion
        /// <summary>
        /// 获取别名
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        private string GetTableAsName(string tablename)
        {
            string tableasname;
            if (tablename == sqlmod.StrFrom)
            {
                tableasname = sqlmod.StrAsName;
                if (tableasname.IsEmpty())
                    tableasname = sqlmod.StrFrom;
            }
            else
            {
                var mod = sqlmod.JoinTable.RootList.Find(p => p.NTableName.Equals(tablename));
                tableasname = mod.NTableAsName;
                if (tableasname.IsEmpty())
                    tableasname = mod.TableName;
            }
            return tableasname;
        }
        /// <summary>
        /// 通配符转换
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        protected static string GetEscape(string str)
        {
            return str.Replace("!", "!!").Replace("%", "!%").Replace("_", "!_");
        }

        /// <summary>
        /// Gets the type of the native.
        /// </summary>
        /// <param name="dbType">Type of the db.</param>
        /// <returns></returns>
        private object GetNativeType(DbType dbType, object obj)
        {
            switch (dbType)
            {
                case DbType.AnsiString:
                case DbType.Object:
                case DbType.String:
                case DbType.AnsiStringFixedLength:
                case DbType.StringFixedLength:
                    return "'" + obj + "'";

                case DbType.Binary:
                case DbType.Byte:
                case DbType.SByte:
                    return "'" + obj + "'";

                case DbType.Boolean:
                    return obj;

                case DbType.Currency:
                    return "money";

                case DbType.Date:
                case DbType.DateTime:
                case DbType.Time:
                    return "'" + obj + "'";

                case DbType.Decimal:
                    return obj;

                case DbType.Double:
                    return obj;

                case DbType.Guid:
                    return "'" + obj + "'";

                case DbType.Int16:
                case DbType.Int32:
                case DbType.UInt16:
                case DbType.UInt32:
                    return obj;

                case DbType.Int64:
                case DbType.UInt64:
                    return obj;

                case DbType.Single:
                    return "'" + obj + "'";

                case DbType.VarNumeric:
                    return obj;

                case DbType.Xml:
                    return "'" + obj + "'";
            }
            return obj;
        }
    }
}