public string QuerySysUser(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var db = new ContextBase()) { var query = from a in db.SysUser join b in db.SysRole on a.RoleId equals b.Id select new { a.Id, a.Nickname, a.RoleId, a.Sign, a.Status, a.UserGroup, a.UserName, a.UserPwd, a.CreateTime, OldUserPwd = a.UserPwd, RoleName = b.Name }; Func.Common.QueryJoin(query, param, db, ref or); } return(or.ToJson()); }
/// <summary> /// 查询拼接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="param"></param> /// <param name="ru"></param> /// <param name="or"></param> public static void QueryJoin <T>(IQueryable <T> query, QueryDataVM.GetParams param, ContextBase db, ref QueryDataVM.OutputResult or) { //条件 query = QueryWhere(query, param); //总条数 or.total = query.Count(); //排序 if (!string.IsNullOrWhiteSpace(param.sort)) { query = Fast.QueryableTo.OrderBy(query, param.sort, param.order); } //分页 if (param.pagination == 1) { query = query.Skip((param.page - 1) * param.rows).Take(param.rows); } //数据 or.data = query.ToList(); //列 if (param.columnsExists != 1) { or.columns = db.SysTableConfig.Where(x => x.TableName == param.tableName).OrderBy(x => x.ColOrder).ToList(); } }
public string QuerySysRole(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var db = new ContextBase()) { var query = db.SysRole; Func.Common.QueryJoin(query, param, db, ref or); } return(or.ToJson()); }
public string QueryConfigTable(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var db = new ContextBase()) { var query = db.SysTableConfig.Where(x => x.ColHide != 2); Func.Common.QueryJoin(query, param, db, ref or); } return(or.ToJson()); }
public string QuerySysTableConfig(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var db = new ContextBase()) { var query = db.SysTableConfig; //query = query.Where(x => EF.Functions.Like(x.TableName, "%log%")); Func.Common.QueryJoin(query, param, db, ref or); } return(or.ToJson()); }
/// <summary> /// 查询 /// </summary> /// <param name="param"></param> /// <returns></returns> public string QueryConfigTable(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var ru = new RepositoryUse()) { var query = from a in ru.Context.Set <SysTableConfig>() select a; Func.Common.QueryJoin(query, param, ru, ref or); } return(or.ToJson()); }
public string QueryData(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); //根据标识检索是否存在对应的数据表,(表字典DicTable 可过滤掉不让查询的表,也可以自定义DicTable表字典) //前端直传表名查询会有安全问题,所以通过uri标识在后台做对应且可过滤 if (Func.Common.DicTable.ContainsKey(param.uri)) { //表名 param.tableName = Func.Common.DicTable[param.uri]; //条件 if (!string.IsNullOrWhiteSpace(param.wheres)) { var jwhere = JObject.Parse(param.wheres) as JToken; param.wheres = QueryDataVM.SqlQueryWhere(jwhere); } if (string.IsNullOrWhiteSpace(param.wheres)) { param.wheres = "1=1"; } //排序 param.sortOrderJoin = DataBase.OrderByJoin(param.sort, param.order); //查询:列、数据表、总条数 if (param.columnsExists != 1) { or.columns = Func.Common.QuerySysTableConfigList(x => x.TableName == param.tableName); } var ds = Func.Common.QueryDS(param); or.data = ds.Tables[0]; if (param.pagination == 1) { or.total = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString()); } else { or.total = ds.Tables[0].Rows.Count; } } return(or.ToJson()); }
public string QuerySysButton(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); using (var db = new ContextBase()) { var list = db.SysButton.OrderBy(x => x.BtnOrder).ToList(); var tree = Func.Common.ListToTree(list, "Pid", "Id", new List <string> { Guid.Empty.ToString() }); or.data = tree.ToJArray(); //列 if (param.columnsExists != 1) { or.columns = db.SysTableConfig.Where(x => x.TableName == param.tableName).OrderBy(x => x.ColOrder).ToList(); } } return(or.ToJson()); }
/// <summary> /// 查询 /// </summary> /// <param name="param"></param> /// <returns></returns> public string QuerySysUser(QueryDataVM.GetParams param) { var or = new QueryDataVM.OutputResult(); #region 自定义SQL查询主体 //查询主体,select 开头 ,QueryJoin里面会处理分页排序及查询条件 string sql = "select a.*,UserPwd as OldUserPwd,b.Name as RoleName from SysUser a left join SysRole b on a.RoleID=b.ID"; //查询总条数,可选,不传,则以查询主体sql查询总条数,此处如果用主体SQL多了不必要的关联查询 string sqlcount = "select count(1) from SysUser"; Func.Common.QueryJoin(sql, param, ref or, sqlcount); #endregion #region linq查询,Like等SQL查询条件不支持 //using (var ru = new RepositoryUse()) //{ // var query = from a in ru.Context.Set<SysUser>() // join b in ru.Context.Set<SysRole>() on a.RoleID equals b.ID // select new // { // a.ID, // a.RoleID, // a.UserName, // a.UserPwd, // OldUserPwd = a.UserPwd, // a.Nickname, // a.CreateTime, // a.Status, // a.UserGroup, // RoleName = b.Name // }; // Func.Common.QueryJoin(query, param, ru, ref or); //} #endregion return(or.ToJson()); }
/// <summary> /// 查询条件 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="param"></param> /// <returns></returns> public static IQueryable <T> QueryWhere <T>(IQueryable <T> query, QueryDataVM.GetParams param) { //条件 if (!string.IsNullOrWhiteSpace(param.wheres)) { var whereItems = JArray.Parse(param.wheres); foreach (var item in whereItems) { //关系符 var relation = item["relation"].ToStringOrEmpty(); string rel = DicSqlRelation[relation]; //字段 var field = item["field"].ToStringOrEmpty(); //值 var value = item["value"]; //值引号 var vqm = "\""; switch (relation) { case "Equal": case "NotEqual": case "LessThan": case "GreaterThan": case "LessThanOrEqual": case "GreaterThanOrEqual": { string val = vqm + value.ToStringOrEmpty() + vqm; string iwhere = string.Format(rel, field, val); query = DynamicQueryableExtensions.Where(query, iwhere); } break; case "Contains": case "StartsWith": case "EndsWith": { string iwhere = string.Format(rel, value.ToStringOrEmpty()); query = query.Where(x => EF.Functions.Like(x.GetType().GetProperty(field).GetValue(x, null).ToString(), iwhere)); } break; case "BetweenAnd": if (value.Count() == 2) { var v1 = vqm + value[0].ToString() + vqm; var v2 = vqm + value[1].ToString() + vqm; var iwhere = string.Format(rel, field, v1, v2); query = DynamicQueryableExtensions.Where(query, iwhere); } break; case "In": { var list = new List <string>(); int len = value.Count(); for (int i = 0; i < len; i++) { list.Add(value[i].ToString()); } query = query.Where(x => list.Contains(x.GetType().GetProperty(field).GetValue(x, null).ToString())); } break; case "NotIn": { var list = new List <string>(); int len = value.Count(); for (int i = 0; i < len; i++) { list.Add(value[i].ToString()); } query = query.Where(x => !list.Contains(x.GetType().GetProperty(field).GetValue(x, null).ToString())); } break; } } } return(query); }
public void Export(QueryDataVM.GetParams param) { if (!string.IsNullOrWhiteSpace(param.uri)) { //根据uri转换表名,不建议直接传表名(有些表涉密不允许导出) string tablename = ""; var listtb = new List <string>() { "syslog", "systableconfig", "sysuser" }; tablename = listtb.Where(x => x.ToLower() == param.uri).FirstOrDefault(); if (string.IsNullOrWhiteSpace(tablename)) { return; } //导出文件名 string filename = "导出表格"; //生成excel的临时文件目录 string path = GlobalTo.WebRootPath + "/temp/"; //存放导出的数据 var dt = new DataTable(); //查询导出的数据 string sql = ExportSql(tablename); #region 表配置处理(列排序、列改名、剔除不要的列) using (var db = new ContextBase()) { var configquery = from a in db.SysTableConfig where a.TableName == param.tableName && a.ColHide == 0 && a.ColExport == 1 orderby(a.ColFrozen ?? 0) descending, a.ColOrder ascending select new { a.ColField, a.ColTitle }; //配置信息 var configlist = configquery.ToList(); var listcol = configlist.Select(x => x.ColField.ToLower()).ToList(); using (var conn = db.Database.GetDbConnection()) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = sql; dt.Load(cmd.ExecuteReader()); } //清空表格主键(不允许移除主键列) dt.PrimaryKey = null; //需要删除的列 var listcolRemove = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (!listcol.Contains(dc.ColumnName.ToLower())) { listcolRemove.Add(dc.ColumnName); } } //删除列 foreach (var item in listcolRemove) { dt.Columns.Remove(item); } //列排序、重命名 for (int i = 0; i < configlist.Count; i++) { var item = configlist[i]; if (dt.Columns.Contains(item.ColField)) { var dc = dt.Columns[item.ColField]; //列排序 dc.SetOrdinal(i); //重命名列 int index = 1; string newcol = item.ColTitle; while (dt.Columns.Contains(newcol)) { newcol = item.ColTitle + index.ToString(); index++; } dc.ColumnName = newcol; } } } #endregion #region 生成Excel并输出流 if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } filename += DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; if (Fast.NpoiTo.DataTableToExcel(dt, path + filename)) { new Core.DownTo(Response).Stream(path, filename); } #endregion } }
/// <summary> /// 公共导出 /// 默认查询所有数据 再根据表配置保留要导出的列 /// /// </summary> public void Export(QueryDataVM.GetParams param) { if (Func.Common.DicTable.ContainsKey(param.uri)) { //表名 param.tableName = Func.Common.DicTable[param.uri]; //导出文件名 string filename = "导出表格"; //生成excel的临时文件目录 string path = Startup.HostingEnvironment.WebRootPath + "\\temp\\"; //存放导出的数据 var dt = new DataTable(); //查询导出的数据 string sql = ExportSql(param.tableName.ToLower()); dt = SQLServerDB.GetDataTable(sql); #region 表配置处理(列排序、列改名、剔除不要的列) using (var ru = new RepositoryUse()) { var configquery = from a in ru.Context.Set <Domain.SysTableConfig>() where a.TableName == param.tableName && a.ColHide == 0 && a.ColExport == 1 orderby(a.ColFrozen == null ? 0 : a.ColFrozen) descending, a.ColOrder ascending select new { a.ColField, a.ColTitle }; //配置信息 var configlist = configquery.ToList(); var listcol = configlist.Select(x => x.ColField.ToLower()).ToList(); //需要删除的列 var listcolRemove = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (!listcol.Contains(dc.ColumnName.ToLower())) { listcolRemove.Add(dc.ColumnName); } } //删除列 foreach (var item in listcolRemove) { dt.Columns.Remove(item); } //列排序、重命名 for (int i = 0; i < configlist.Count; i++) { var item = configlist[i]; if (dt.Columns.Contains(item.ColField)) { var dc = dt.Columns[item.ColField]; //列排序 dc.SetOrdinal(i); //重命名列 int index = 1; string newcol = item.ColTitle; while (dt.Columns.Contains(newcol)) { newcol = item.ColTitle + index.ToString(); index++; } dc.ColumnName = newcol; } } } #endregion #region 生成Excel并输出流 if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } filename += DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; if (Core.NPOITo.DataTableToExcel(dt, path + filename)) { new Core.DownTo(Response).Stream(path, filename); } #endregion } }