/// <summary> /// 查询拼接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="ivm"></param> /// <param name="ovm"></param> public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ref QueryDataOutputVM ovm) { //总条数 ovm.total = query.Count(); if (ovm.total <= 0) { return; } //排序 if (!string.IsNullOrWhiteSpace(ivm.sort)) { query = QueryableTo.OrderBy(query, ivm.sort, ivm.order); } //分页 if (ivm.pagination == 1) { query = query.Skip((ivm.page - 1) * ivm.rows).Take(ivm.rows); } //数据 var data = query.ToList(); ovm.data = data; }
public QueryDataOutputVM QueryNoteList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var uinfo = Apps.LoginService.Get(HttpContext); var query = from a in db.Notepad join b in db.UserInfo on a.Uid equals b.UserId orderby a.NoteCreateTime descending where a.Uid == uinfo.UserId select new Domain.Notepad { NoteId = a.NoteId, NoteTitle = a.NoteTitle, NoteCreateTime = a.NoteCreateTime, NoteUpdateTime = a.NoteUpdateTime, Uid = a.Uid, Spare3 = b.Nickname }; if (!string.IsNullOrWhiteSpace(ivm.Pe1)) { query = query.Where(x => x.NoteTitle.Contains(ivm.Pe1)); } Application.CommonService.QueryJoin(query, ivm, ref ovm); return(ovm); }
public QueryDataOutputVM QueryNoteList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var uinfo = new Func.UserAuthAid(HttpContext).Get(); using (var db = new ContextBase()) { var query = from a in db.Notepad join b in db.UserInfo on a.Uid equals b.UserId orderby a.NoteCreateTime descending where a.Uid == uinfo.UserId select new Notepad { NoteId = a.NoteId, NoteTitle = a.NoteTitle, NoteCreateTime = a.NoteCreateTime, NoteUpdateTime = a.NoteUpdateTime, Uid = a.Uid, Spare3 = b.Nickname }; if (!string.IsNullOrWhiteSpace(ivm.pe1)) { query = query.Where(x => x.NoteTitle.Contains(ivm.pe1)); } Func.Common.QueryJoin(query, ivm, ref ovm); } return(ovm); }
public QueryDataOutputVM QuerySysUser(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = from a in db.SysUser join b in db.SysRole on a.SrId equals b.SrId select new { a.SuId, a.SuNickname, a.SrId, a.SuSign, a.SuStatus, a.SuGroup, a.SuName, a.SuPwd, a.SuCreateTime, OldUserPwd = a.SuPwd, b.SrName }; Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询拼接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="ivm"></param> /// <param name="ovm"></param> public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ref QueryDataOutputVM ovm) { //总条数 ovm.Total = query.Count(); //排序 if (!string.IsNullOrWhiteSpace(ivm.Sort)) { query = QueryableTo.OrderBy(query, ivm.Sort, ivm.Order); } //分页 if (ivm.Pagination == 1) { query = query.Skip((ivm.Page - 1) * ivm.Rows).Take(ivm.Rows); } var sql = query.ToQueryString(); Console.WriteLine(sql); //数据 var data = query.ToList(); ovm.Data = data; }
/// <summary> /// 查询文章列表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryWriteList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = from a in db.UserWriting join b in db.UserInfo on a.Uid equals b.UserId select new { a.UwId, a.UwTitle, a.UwCreateTime, a.UwUpdateTime, a.UwReadNum, a.UwReplyNum, a.UwOpen, a.UwStatus, a.UwLaud, a.UwMark, a.UwCategory, b.UserId, b.Nickname, b.UserName, b.UserMail }; if (!string.IsNullOrWhiteSpace(ivm.Pe1)) { query = query.Where(x => x.UwTitle.Contains(ivm.Pe1)); } Application.CommonService.QueryJoin(query, ivm, ref ovm); return(ovm); }
public QueryDataOutputVM QuerySysUser(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = from a in db.SysUser join b in db.SysRole on a.SrId equals b.SrId select new { a.SuId, a.SuNickname, a.SrId, a.SuSign, a.SuStatus, a.SuGroup, a.SuName, a.SuPwd, a.SuCreateTime, OldUserPwd = a.SuPwd, b.SrName }; Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
/// <summary> /// 查询条件(IQueryable) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="ivm"></param> /// <returns></returns> public static IQueryable <T> QueryWhere <T>(IQueryable <T> query, QueryDataInputVM ivm) { //条件 if (!string.IsNullOrWhiteSpace(ivm.wheres)) { var whereItems = JArray.Parse(ivm.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": { query = DynamicQueryableExtensions.Where(query, field + "." + relation + "(@0)", value.ToStringOrEmpty()); } 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; } } } return(query); }
/// <summary> /// Grid多表格-子表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryGridChange2(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.SysUser; Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
public QueryDataOutputVM QueryConfigTable(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.SysTableConfig.Where(x => x.ColHide != 2); Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
public QueryDataOutputVM QuerySysDictionary(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.SysDictionary; Func.Common.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询表配置示例 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryTempExample(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.TempExample; Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询单据主表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryInvoiceMain(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.TempInvoiceMain; Func.Common.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// Grid多表格-主表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryGridChange1(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.SysRole; Func.Common.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询表配置 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QuerySysTableConfig(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = db.SysTableConfig; Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询表配置示例 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryTempExample(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = db.TempExample; Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
/// <summary> /// Grid多表格-子表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryGridChange2(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = db.SysUser; Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
public QueryDataOutputVM QueryConfigTable(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = db.SysTableConfig.Where(x => x.ColHide != 2); Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
/// <summary> /// 查询表配置 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QuerySysTableConfig(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = db.SysTableConfig; Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
public QueryDataOutputVM QueryTableConfig(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var queryHas = from a in db.SysTableConfig group a by a.TableName into g select g.Key; var listHas = queryHas.ToList(); var dbname = db.Database.GetDbConnection().Database; var sql = QueryScripts(ContextBase.TDB.ToString(), "name").Replace("@DataBaseName", dbname); var listRow = new List <TreeNodeVM>(); if (!string.IsNullOrWhiteSpace(sql)) { using var dt = new DataTable(); using (var conn = db.Database.GetDbConnection()) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = sql; dt.Load(cmd.ExecuteReader()); } foreach (DataRow dr in dt.Rows) { var key = dr[0].ToString(); var val = (listHas.Exists(x => x.ToLower() == key.ToLower()) ? "1" : ""); var row = new TreeNodeVM { id = key, pid = val }; listRow.Add(row); } } var listFilter = Func.Common.QueryWhere(listRow, ivm); ovm.data = listFilter; ovm.total = listFilter.Count(); } return(ovm); }
/// <summary> /// 查询回复列表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryReplyList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = from a in db.UserReply join b1 in db.UserInfo on a.Uid equals b1.UserId into bg from b in bg.DefaultIfEmpty() select new { a.UrId, a.Uid, a.UrAnonymousName, a.UrAnonymousLink, a.UrAnonymousMail, a.UrTargetType, a.UrTargetId, a.UrContent, a.UrContentMd, a.UrCreateTime, a.UrStatus, a.UrTargetPid, a.Spare1, a.Spare2, a.Spare3, UserId = b == null ? 0 : b.UserId, Nickname = b == null ? null : b.Nickname, UserName = b == null ? null : b.UserName, UserMail = b == null ? null : b.UserMail }; if (!string.IsNullOrWhiteSpace(ivm.Pe1)) { query = GlobalTo.TDB switch { SharedEnum.TypeDB.SQLite => query.Where(x => EF.Functions.Like(x.UrContent, $"%{ivm.Pe1}%")), SharedEnum.TypeDB.PostgreSQL => query.Where(x => EF.Functions.ILike(x.UrContent, $"%{ivm.Pe1}%")), _ => query.Where(x => x.UrContent.Contains(ivm.Pe1)), }; } Application.CommonService.QueryJoin(query, ivm, ref ovm); return(ovm); }
public QueryDataOutputVM QuerySysButton(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var list = db.SysButton.OrderBy(x => x.SbBtnOrder).ToList(); var tree = Core.TreeTo.ListToTree(list, "SbPid", "SbId", new List <string> { Guid.Empty.ToString() }); ovm.Data = tree.ToJArray(); //列 if (ivm.ColumnsExists != 1) { ovm.Columns = db.SysTableConfig.Where(x => x.TableName == ivm.TableName).OrderBy(x => x.ColOrder).ToList(); } return(ovm); }
/// <summary> /// 查询单据明细表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryInvoiceDetail(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = from a in db.TempInvoiceDetail select a; if (string.IsNullOrWhiteSpace(ivm.Pe1)) { query = query.Where(x => false); } else { query = query.Where(x => x.TimId == ivm.Pe1); } Application.CommonService.QueryJoin(query, ivm, db, ref ovm); return(ovm); }
/// <summary> /// 查询回复列表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryReplyList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = from a in db.UserReply join b1 in db.UserInfo on a.Uid equals b1.UserId into bg from b in bg.DefaultIfEmpty() select new { a.UrId, a.Uid, a.UrAnonymousName, a.UrAnonymousLink, a.UrAnonymousMail, a.UrTargetType, a.UrTargetId, a.UrContent, a.UrContentMd, a.UrCreateTime, a.UrStatus, a.UrTargetPid, a.Spare1, a.Spare2, a.Spare3, b.UserId, b.Nickname, b.UserName, b.UserMail }; if (!string.IsNullOrWhiteSpace(ivm.pe1)) { query = query.Where(x => x.UrContent.Contains(ivm.pe1)); } Func.Common.QueryJoin(query, ivm, ref ovm); } return(ovm); }
/// <summary> /// 查询条件(IEnumerable,仅支持部分) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="ivm"></param> /// <returns></returns> public static IEnumerable <T> QueryWhere <T>(IEnumerable <T> query, QueryDataInputVM ivm) { //条件 if (!string.IsNullOrWhiteSpace(ivm.wheres)) { var whereItems = JArray.Parse(ivm.wheres); foreach (var item in whereItems) { //关系符 var relation = item["relation"].ToStringOrEmpty(); string rel = DicSqlRelation[relation]; //字段 var field = item["field"].ToStringOrEmpty(); //值 var value = item["value"].ToString().ToLower(); switch (relation) { case "Equal": query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower() == value); break; case "NotEqual": query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower() != value); break; case "Contains": query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().Contains(value)); break; case "StartsWith": query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().StartsWith(value)); break; case "EndsWith": query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().EndsWith(value)); break; } } } return(query); }
public QueryDataOutputVM QuerySysMenu(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var list = db.SysMenu.OrderBy(x => x.SmOrder).ToList(); var tree = Core.TreeTo.ListToTree(list, "SmPid", "SmId", new List <string> { Guid.Empty.ToString() }); ovm.data = tree.ToJArray(); //列 if (ivm.columnsExists != 1) { ovm.columns = db.SysTableConfig.Where(x => x.TableName == ivm.tableName).OrderBy(x => x.ColOrder).ToList(); } } return(ovm); }
/// <summary> /// 查询单据明细表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryInvoiceDetail(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); using (var db = new ContextBase()) { var query = from a in db.TempInvoiceDetail select a; if (string.IsNullOrWhiteSpace(ivm.pe1)) { query = query.Where(x => false); } else { query = query.Where(x => x.TimId == ivm.pe1); } Func.Common.QueryJoin(query, ivm, db, ref ovm); } return(ovm); }
/// <summary> /// 查询文章列表 /// </summary> /// <param name="ivm"></param> /// <returns></returns> public QueryDataOutputVM QueryWriteList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var query = from a in db.UserWriting join b in db.UserInfo on a.Uid equals b.UserId select new { a.UwId, a.UwTitle, a.UwCreateTime, a.UwUpdateTime, a.UwReadNum, a.UwReplyNum, a.UwOpen, a.UwStatus, a.UwLaud, a.UwMark, a.UwCategory, b.UserId, b.Nickname, b.UserName, b.UserMail }; if (!string.IsNullOrWhiteSpace(ivm.Pe1)) { query = GlobalTo.TDB switch { SharedEnum.TypeDB.SQLite => query.Where(x => EF.Functions.Like(x.UwTitle, $"%{ivm.Pe1}%")), SharedEnum.TypeDB.PostgreSQL => query.Where(x => EF.Functions.ILike(x.UwTitle, $"%{ivm.Pe1}%")), _ => query.Where(x => x.UwTitle.Contains(ivm.Pe1)), }; } Application.CommonService.QueryJoin(query, ivm, ref ovm); return(ovm); }
/// <summary> /// 查询拼接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="ivm"></param> /// <param name="db"></param> /// <param name="ovm"></param> public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ContextBase db, ref QueryDataOutputVM ovm) { //条件 query = QueryWhere(query, ivm); //总条数 ovm.Total = query.Count(); //排序 if (!string.IsNullOrWhiteSpace(ivm.Sort)) { query = QueryableTo.OrderBy(query, ivm.Sort, ivm.Order); } //分页 if (ivm.Pagination == 1) { query = query.Skip((Math.Max(ivm.Page, 1) - 1) * ivm.Rows).Take(ivm.Rows); } //查询SQL ovm.QuerySql = query.ToQueryString(); //数据 var data = query.ToList(); ovm.Data = data; //导出时,存储数据表格 if (ivm.HandleType == "export") { ovm.Table = ToDataTableForString(data); } //列 if (ivm.ColumnsExists != 1) { ovm.Columns = db.SysTableConfig.Where(x => x.TableName == ivm.TableName).OrderBy(x => x.ColOrder).ToList(); } }
public QueryDataOutputVM QueryTagList(QueryDataInputVM ivm) { var ovm = new QueryDataOutputVM(); var uinfo = new Func.UserAuthAid(HttpContext).Get(); var query = from a in _context.Tags orderby a.TagId descending where string.IsNullOrEmpty(ivm.pe1) ? true : a.TagName.Contains(ivm.pe1) select new Tags { TagId = a.TagId, TagName = a.TagName, TagOwner = a.TagOwner, TagStatus = a.TagStatus, createTime = a.createTime, updateTime = a.updateTime, }; Func.Common.QueryJoin(query, ivm, ref ovm); return(ovm); }