public APIResult <Libs.DataTableResponse <sec_roles> > All(Requests.DataTableRequest request)
{
Libs.DataTableResponse <sec_roles> response = Libs.DataTableResponse <sec_roles> .getDataTable(Libs.General.getDataTabe(request, "sec_roles"));
if (response.data == null)
{
return(APIResult <Libs.DataTableResponse <sec_roles> > .Error(ResponseCode.BackendInternalServer, "API_ERROR_BAD"));
}
return(APIResult <Libs.DataTableResponse <sec_roles> > .Success(response, "API_SUCCESS"));
}
//public static IEnumerable<T> Create<T>(IQueryable<T> results, Requests.DataTableRequest request)
//{
// var skip = request.start;
// var pageSize = request.length;
// var orderedResults = OrderResults(results, request);
// return pageSize > 0 ? orderedResults.Skip(skip).Take(pageSize).ToList() :
// orderedResults.ToList();
//}
public static IEnumerable <T> OrderResults <T>(IQueryable <T> results, Requests.DataTableRequest request)
{
if (request.order == null)
{
return(results);
}
foreach (var ord in request.order)
{
var columnName = request.columns[ord.column].data;
if (ord.dir == "asc")
{
results = results.OrderBy(typeof(T).GetProperty(columnName).Name);
}
if (ord.dir == "desc")
{
results = results.OrderByDescending(typeof(T).GetProperty(columnName).Name);
}
}
return(results);
}
//private static DataTableResponse<DAL.Item.vwItem> WrapSearch
// (ICollection<DAL.Item.vwItem> details, DataTableRequest request)
//{
// var results = DataTableFilters.FilterItems(details, request.search.value).ToList();
// var response = new DataTableResponse<DAL.Item.vwItem>()
// {
// data = Create(results, request),
// draw = request.draw,
// recordsFiltered = results.Count,
// recordsTotal = details.Count
// };
// return response;
//}
public static IQueryable <T> Filter
(IQueryable <T> details, string searchText, Requests.DataTableRequest request)
{
if (string.IsNullOrEmpty(searchText))
{
return(details);
}
var ds = searchText.Split(' ');
var results = details;
foreach (var i in ds)
{
results = results.Where(x =>
x.ConcatColumns(request.columns.Where(a => a.searchable).Select(a => a.data).ToList()).ContainsIgnoringCase(i.Trim())
);
}
return(results);
}
public static DataTableResponse <T> getDataTable(IEnumerable <T> list, IEnumerable <KeyValuePair <string, string> > querystring, Requests.DataTableRequest request)
{
DataTableResponse <Dictionary <string, object> > dataTableResponse = new DataTableResponse <Dictionary <string, object> >();
DataTable dt = new DataTable();
dt = list.ToDataTable();
dataTableResponse.recordsTotal = list.Count();
string whr = "";
//foreach (var i in querystring)
//{
// whr += (whr == "" ? "" : " and ") + i.Key + " = '" + i.Value + "'";
//}
if (request != null && request.search != null)
{
string subWhr = "";
foreach (var item in request.columns.Where(a => a.searchable).ToList())
{
subWhr += (subWhr == "" ? "" : " or ") + item.data + " like '%" + request.search.value + "%' ";
}
//subWhr += ")";
whr += (whr == "" ? (subWhr == "" ? "" : subWhr) : (subWhr == "" ? "" : " and " + subWhr));
}
string orders = "";
if (request != null && request.order != null && request.order.Count > 0)
{
foreach (var ord in request.order)
{
if (request.columns[ord.column].orderable)
{
orders += (orders == "" ? "" : " , ") + request.columns[ord.column].data + " " + ord.dir;
}
}
//orders = (orders == "" ? "" : " order by ") + orders;
}
int length = 1000;
int start = 0;
if (request != null)
{
length = (request.length == -1? length: request.length);
start = request.start;
}
List <DataRow> rows = dt.Select(whr, orders).ToList();
dataTableResponse.recordsFiltered = rows.Count();
rows = rows.Skip(start).Take(length).ToList();
DataTable newdt = new DataTable();
foreach (DataColumn c in dt.Columns)
{
newdt.Columns.Add(c.ColumnName, c.DataType);
}
foreach (var row in rows)
{
newdt.LoadDataRow(row.ItemArray, true);
}
dataTableResponse.data = General.DataTableToDictionary(newdt);
Libs.DataTableResponse <T> d = getDataTable(dataTableResponse);
return(d);
}
public static DataTableResponse <Dictionary <string, object> > getDataTabe(Requests.DataTableRequest request, string tableName, string sqlWhere = "")
{
DataTableResponse <Dictionary <string, object> > dt = new DataTableResponse <Dictionary <string, object> >();
if (request == null)
{
request = new Requests.DataTableRequest();
}
if (request.length == null || request.length <= 0)
{
request.length = 1000;
}
//IEnumerable<DAL.Security.sec_sessions> result = ses.GetAll();
var sqlData = General.GetData("select count(*) from " + tableName + (sqlWhere == ""?"":" where " + sqlWhere));
dt.recordsTotal = (int)sqlData.Rows[0][0];
string whr = sqlWhere;
if (request.search != null)
{
string subWhr = "(";
foreach (var item in request.columns.Where(a => a.searchable).ToList())
{
subWhr += (subWhr == "(" ? "" : " or ") + item.data + " like '%" + request.search.value + "%' ";
}
subWhr += ")";
whr += (whr == "" ? "" : " and ") + subWhr;
}
string orders = "";
if (request.order != null && request.order.Count > 0)
{
foreach (var ord in request.order)
{
if (request.columns[ord.column].orderable)
{
orders += (orders == "" ? "" : " , ") + request.columns[ord.column].data + " " + ord.dir;
}
}
orders = (orders == "" ? "" : " order by ") + orders;
}
sqlData = General.GetData("select count(*) from " + tableName + " " + (whr == "" ? "" : " where " + whr));
dt.recordsFiltered = (int)sqlData.Rows[0][0];
sqlData = General.GetData("select top " + request.length + " * from "
+ " ("
+ " select *,"
+ " ROW_NUMBER() " + (orders == "" ? " OVER(Order By id asc)" : " OVER(" + orders + ")") + " AS ROW_NUM"
+ " from " + tableName + " " + " " + (whr == "" ? "" : " where " + whr)
+ " ) x"
+ " where ROW_NUM > " + request.start);
//Sessions ses = new Sessions();
dt.data = General.DataTableToDictionary(sqlData).AsEnumerable();
return(dt);
}