public PagedRecords GetRecords( Entity entity, int page, int take, IList<IEntityFilter> filters, string searchQuery, string order, string orderDirection) { 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 = string.Join(",", entity.GetColumns()); List<object> args; var where = ConvertFiltersToSql(filters, search, out args); var table = new DynamicModel( AdminInitialise.ConnectionStringName, entity.TableName, entity.Key.ColumnName); 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, entity)); } 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) { 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 }; } }
private static string ConvertFiltersToSql( IList<BaseFilter> filters, EntitySearch search, out List<object> args, string alias = "") { args = new List<object>(); filters = filters ?? new List<BaseFilter>(); var activeFilters = filters .Where(x => !x.Value.IsNullOrEmpty()) .ToList(); if (!activeFilters.Any() && !search.IsActive) { return null; } if (!alias.IsNullOrEmpty()) { alias += "."; } var sbConditions = new StringBuilder(); foreach (var filter in activeFilters) { var condition = filter.GetSqlCondition(alias, ref args); if (!condition.IsNullOrEmpty()) { sbConditions.AppendFormat(" {0} AND", condition); } } if (search.IsActive) { var searchCondition = String.Empty; foreach (var property in search.Properties) { var query = search.Query.TrimStart('>', '<'); decimal temp; if (property.TypeInfo.IsString) { searchCondition += " {0}{1} LIKE @{2} OR" .Fill(alias, property.ColumnName, args.Count); args.Add("%" + search.Query + "%"); } // TODO: Temporary solution else if (decimal.TryParse(query.Replace(".", ","), out temp)) { var sign = "="; if (search.Query.StartsWith(">")) { sign = ">="; } else if (search.Query.StartsWith("<")) { sign = "<="; } searchCondition += " {0}{1} {2} @{3} OR" .Fill(alias, property.ColumnName, sign, args.Count); args.Add(temp); } } if (!searchCondition.IsNullOrEmpty()) { searchCondition = searchCondition .TrimStart(' ') .TrimEnd("OR".ToCharArray()); sbConditions.AppendFormat(" ({0})", searchCondition); } } var conditions = sbConditions.ToString(); if (conditions.IsNullOrEmpty()) { return null; } return " WHERE" + conditions.TrimEnd("AND".ToCharArray()); }
public IList<DataRow> GetRecords( Entity entity, IList<IEntityFilter> filters = null, string searchQuery = null, string order = null, string orderDirection = null, bool determineDisplayValue = false) { 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 = 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( AdminInitialise.ConnectionStringName, entity.TableName, entity.Key.ColumnName); 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 data; }