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
});
}
}