public JsonResult GetSearchResults(string sidx, string sord, int page, int rows, bool _search, string filters) { //Placements tbl = new Placements(); int pageIndex = Convert.ToInt32(page) - 1; int pageSize = rows; int currentPage = pageIndex * pageSize == 0 ? 1 : pageIndex * pageSize; int totalRecords = tbl.Count(); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize); jgridFilter searchRules = jgridFilter.Create(filters ?? ""); bool IsSearch = _search; string where_string = ""; if (IsSearch) { string groupOp = searchRules.groupOp; int i = 0; foreach (Rule myrule in searchRules.rules) { string line = BuildLine(myrule.op, myrule.field, myrule.data); if (i == 0) { where_string = line; } else { where_string += " " + groupOp + " " + line; } i++; } }// IEnumerable <dynamic> results; if (IsSearch) { results = tbl.Paged(where : where_string, orderBy: sidx + " " + sord, currentPage: currentPage, pageSize: pageSize).Items; } else { results = tbl.Paged(orderBy: sidx + " " + sord, currentPage: currentPage, pageSize: pageSize).Items; } JsonResult myJasonResults = GetJson(results, totalPages, page, totalRecords); return(myJasonResults); }
public override void QueryTop10Dynamic(BenchmarksContainer bc) { bc.Add(i => { Enumerable.ToArray(_db.Paged(pageSize: 10).Items); }, "Massive"); }
public PagedRecordsViewModel GetRecords(Entity entity, int page, int take, IList <IEntityFilter> filters, string searchQuery, string order, string orderDirection) { var skip = (page - 1) * take; var search = new EntitySearch { Query = searchQuery, Properties = entity.SearchProperties }; order = order.IsNullOrEmpty() ? entity.Key.ColumnName : order; orderDirection = orderDirection.IsNullOrEmpty() ? "ASC" : orderDirection.ToUpper(); var orderBy = order + " " + orderDirection; var columns = GetColumns(entity); var where = ConvertFiltersToSQL(filters, search); var table = new DynamicModel(AdminInitialise.ConnectionString, tableName: entity.TableName, primaryKeyField: entity.Key.ColumnName); var result = table.Paged(columns: columns, where : where, orderBy: orderBy, currentPage: page, pageSize: take); var data = new List <DataRowViewModel>(); foreach (var item in result.Items) { data.Add(ExpandoToDataRow(item, entity)); } return(new PagedRecordsViewModel { TotalItems = result.TotalRecords, TotalPages = result.TotalPages, Records = data }); }
public PagedRecords GetChangesRecords( Entity entityChangesFor, int page, int take, IList <IEntityFilter> filters, string searchQuery, string order, string orderDirection) { var changeEntity = AdminInitialise.ChangeEntity; var search = new EntitySearch { Query = searchQuery, Properties = changeEntity.SearchProperties }; order = order.IsNullOrEmpty() ? changeEntity.Key.ColumnName : order; orderDirection = orderDirection.IsNullOrEmpty() ? "ASC" : orderDirection.ToUpper(); var orderBy = order + " " + orderDirection; var columns = string.Join(",", changeEntity.GetColumns()); List <object> args; var where = ConvertFiltersToSql(filters, search, out args); where += where.IsNullOrEmpty() ? " WHERE " : " AND "; where += "EntityName = @" + args.Count; args.Add(entityChangesFor.Name); var table = new DynamicModel( AdminInitialise.ConnectionStringName, changeEntity.TableName, changeEntity.Key.Name); var result = table.Paged( columns: columns, where : where, orderBy: orderBy, currentPage: page, pageSize: take, args: args.ToArray()); var data = new List <DataRow>(); foreach (var item in result.Items) { data.Add(new DataRow(item, changeEntity)); } return(new PagedRecords { TotalItems = result.TotalRecords, TotalPages = result.TotalPages, Records = data }); }
public PagedRecords GetRecords( Entity entity, IList <BaseFilter> filters = null, string searchQuery = null, string order = null, string orderDirection = null, bool determineDisplayValue = false, int?page = null, int?take = null, bool loadForeignKeys = false) { var search = new EntitySearch { Query = searchQuery, Properties = entity.SearchProperties }; order = order.IsNullOrEmpty() ? entity.Keys.FirstOrDefault().Column : order; orderDirection = orderDirection.IsNullOrEmpty() ? "ASC" : orderDirection.ToUpper(); var orderBy = order + " " + orderDirection; var columns = string.Join(",", entity.DisplayProperties .Union(entity.Keys) .Where(x => !x.IsForeignKey || (!x.TypeInfo.IsCollection && x.IsForeignKey)) .Select(x => $"{entity.Table}.{x.Column} as {x.Column}") .Distinct()); List <object> args; var where = ConvertFiltersToSql(filters, search, out args); var table = new DynamicModel( _admin.ConnectionStringName, entity.Table, entity.JoinedKeys); if (page.HasValue && take.HasValue) { var result = table.Paged( columns: columns, where : where, orderBy: orderBy, currentPage: page.Value, pageSize: take.Value, args: args.ToArray()); var records = new List <EntityRecord>(); foreach (var item in result.Items) { records.Add(entity.CreateRecord((Dictionary <string, object>)item)); } return(new PagedRecords { TotalItems = result.TotalRecords, TotalPages = result.TotalPages, Records = records }); } else { var joins = ""; if (loadForeignKeys) { foreach (var foreignKey in entity.ForeignKeys.WhereOneToMany()) { var joinTable = foreignKey.ForeignEntity.Table; var joinProperty = foreignKey.ForeignEntity.Keys.FirstOrDefault(x => x.ForeignEntity == entity); var keyProperty = foreignKey.TypeInfo.IsCollection ? entity.Keys.FirstOrDefault() : foreignKey; joins += Environment.NewLine + $"left join {joinTable} on {joinTable}.{joinProperty.Column} = {entity.Table}.{keyProperty.Column}"; var propertyToGet = foreignKey.ForeignEntity.Keys.FirstOrDefault(x => x.ForeignEntity != entity) ?? joinProperty; columns += $",{joinTable}.{propertyToGet.Column} as {foreignKey.Column}"; } } var result = table.All( columns: columns, joins: joins, where : where, orderBy: orderBy, args: args.ToArray()); var records = result .Select(item => entity.CreateRecord(item)) .ToList(); return(new PagedRecords { Records = records }); } }
public PagedRecords GetRecords( Entity entity, IList <BaseFilter> filters = null, string searchQuery = null, string order = null, string orderDirection = null, bool determineDisplayValue = false, int?page = null, int?take = null) { var search = new EntitySearch { Query = searchQuery, Properties = entity.SearchProperties }; order = order.IsNullOrEmpty() ? entity.Key.FirstOrDefault().ColumnName : order; orderDirection = orderDirection.IsNullOrEmpty() ? "ASC" : orderDirection.ToUpper(); var orderBy = order + " " + orderDirection; var columns = string.Join(",", entity.Properties .Where(x => !x.IsForeignKey || (!x.TypeInfo.IsCollection && x.IsForeignKey)) .Select(x => x.ColumnName) .Distinct()); List <object> args; var where = ConvertFiltersToSql(filters, search, out args); var table = new DynamicModel( Admin.ConnectionStringName, entity.TableName, entity.JoinedKey); if (page.HasValue && take.HasValue) { var result = table.Paged( columns: columns, where : where, orderBy: orderBy, currentPage: page.Value, pageSize: take.Value, args: args.ToArray()); var data = new List <DataRow>(); foreach (var item in result.Items) { data.Add(new DataRow(item, entity)); } return(new PagedRecords { TotalItems = result.TotalRecords, TotalPages = result.TotalPages, Records = data }); } else { var result = table.All( columns: columns, where : where, orderBy: orderBy, args: args.ToArray()); var data = result .Select(item => new DataRow(item, entity)) .ToList(); if (determineDisplayValue) { foreach (var row in data) { row.DisplayName = entity.ToString(row); } } return(new PagedRecords { Records = data }); } }