public static DataTablesResponseModel <BankAccountModel> GetBankAccountDataTable(int pageSize, int position, string search, int sortColumnId, string sortDirection)
{
List <BankAccountModel> items = new List <BankAccountModel>();
var bankAccountList = GetAccounts();
int totalCount = bankAccountList.Count();
var query = bankAccountList.Select(x => x.Account).AsQueryable();
if (search != String.Empty)
{
query = query.Where(x => x.Name.Contains(search) || x.IBAN.Contains(search) || x.Code.Contains(search));
}
string sortColumn = "Code";
switch (sortColumnId)
{
case 1:
sortColumn = "Name";
break;
case 2:
sortColumn = "Description";
break;
case 3:
sortColumn = "IBAN";
break;
default:
sortColumn = "Code";
break;
}
string direction = (sortDirection == "DESC") ? "DESC" : "ASC";
// Apply sorting:
//dynamic from System.Linq.Dynamic in Common Exstensions
query = query.OrderBy(sortColumn + " " + sortDirection);
//paging
query = query.Skip(position).Take(pageSize);
DataTablesResponseModel <BankAccountModel> model = new DataTablesResponseModel <BankAccountModel>(query.ToList(), totalCount, totalCount, 0);
return(model);
}
public static DataTablesResponseModel <DataTableCvCompetenceModel> GetCvCompetenceDataTable(int pageSize, int position, string search, int sortColumnId, string sortDirection)
{
List <DataTableCvCompetenceModel> items = new List <DataTableCvCompetenceModel>();
// Set the total count
// so GridView knows how many pages to create
int totalCount = 0;
using (var db = new Entities())
{
totalCount = db.CvCompetences.Count();
var query = db.CvCompetences.Where(x => x.Id != null);
if (search != String.Empty)
{
query = query.Where(x => x.Name.Contains(search) || x.Description.Contains(search));
}
string sortColumn = "Code";
switch (sortColumnId)
{
case 1:
sortColumn = "Name";
break;
case 2:
sortColumn = "Description";
break;
default:
break;
}
string direction = (sortDirection == "DESC") ? "DESC" : "ASC";
// Apply sorting:
//dynamic from System.Linq.Dynamic in Common Exstensions
query = query.OrderBy(sortColumn + " " + sortDirection);
//paging
query = query.Skip(position).Take(pageSize);
int actionCounter = 1;
foreach (var item in query.ToList())
{
items.Add(new DataTableCvCompetenceModel
{
Code = item.Code,
Name = item.Name,
Description = item.Description,
//Action = "/Admin/CvCompetenceEdit/" + item.Id.ToString()
Action = item.Id.ToString()
});
actionCounter++;
}
}
DataTablesResponseModel <DataTableCvCompetenceModel> model = new DataTablesResponseModel <DataTableCvCompetenceModel>(items, totalCount, totalCount, 0);
return(model);
}
/// <summary>
/// The call for this function is generated in order to simplify the filtering below. This is not usually necessary for IQueryable<object>
/// However, LLBLGen has problems with IQuerable when the type is casted to object, so the proper typed call is generated above and this generic is called.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dataTableInit"></param>
/// <param name="model"></param>
/// <returns></returns>
private static JsonResult Query <TEntity>(DataTablesInitializationModel <TEntity> dataTableInit, DataTablesRequestModel model)
{
// Get source model used to create the view.
var source = dataTableInit.Source;
// get IQueryable type of object passed in, this makes the work below generalized for any type
/*var source2 = source as IQueryable<PatientEntity>;
* if(source2 != null)
* {
* source2 = source2.Where(x => Convert.ToString(x.BirthDate, new DateTimeFormatInfo(){ShortDatePattern = }).Contains("fe"));
*
* var result = source2.ToList();
* }
*/
// start with the source, then add on each filter
var count = source.Count();
var displayCount = count;
if (!String.IsNullOrEmpty(model.sSearch))
{
var llblGenProProvider = source.Provider as LLBLGenProProvider;
if (llblGenProProvider != null)
{
var dateConverter = new FunctionMapping(typeof(DateTime), "ToString", 0,
@"CONVERT(nvarchar(2), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 6, 2))) + '/' + CONVERT(nvarchar(2), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 9, 2))) + '/' + CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 1, 4))) + ' ' + CASE WHEN CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) > 12 THEN CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) - 12) ELSE CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2))) END +':' +SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 15, 2) + ':' + SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 18, 2) + CASE WHEN CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) > 11 THEN ' PM' ELSE ' AM' END");
//var indexOf = new FunctionMapping(typeof(string), "IndexOf", 1, "PATINDEX('%{1}%', {0}) - 1");
//var indexOf2 = new FunctionMapping(typeof(string), "IndexOf", 2, "PATINDEX('%{1}%', {0}) - 1");
if ((llblGenProProvider).CustomFunctionMappings == null)
{
(llblGenProProvider).CustomFunctionMappings = new FunctionMappingStore();
}
(llblGenProProvider).CustomFunctionMappings.Add(dateConverter);
//(llblGenProProvider).CustomFunctionMappings.Add(indexOf);
//(llblGenProProvider).CustomFunctionMappings.Add(indexOf2);
// ^--- Doesn't work for no apparent reason but would make the Search() function a lot simpler.
}
// search all specified columns for the specified string
source = source.Search(dataTableInit.Columns, model.sSearch, !(source.Provider is LLBLGenProProvider));
displayCount = source.Count();
}
// sort the result based on the model, this calls OrderBy on multiple columns sequentially
if (model.Sorts.Any())
{
source = source.Sort(dataTableInit.Columns, model.Sorts);
}
// filter page based on start and length, this is like the SQL LIMIT directive
if (model.iDisplayLength > 0)
{
source = source.Skip(model.iDisplayStart).Take(model.iDisplayLength);
}
// now build the response
var response = new DataTablesResponseModel
{
sEcho = model.sEcho.Value,
iTotalRecords = count,
iTotalDisplayRecords = displayCount
};
// loop through each item and output JSON
foreach (TEntity entity in source.ToList())
{
var row = response.NewRow();
row.SetRowId(string.Empty + entity.GetHashCode());
foreach (var column in dataTableInit.Columns)
{
// if the column specifies a formatting function
if (column.Format != null)
{
var method = column.Format.Compile();
row.PushColumn(method.Invoke(entity).ToString());
}
else
{
var value = DataBinder.Eval(entity, column.ColumnName);
row.PushColumn(value != null ? value.ToString() : "");
}
}
}
return(new JsonResult
{
Data = response,
ContentType = null,
ContentEncoding = null,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}