public async Task<JsonResult> DriveDataTable(jQueryDataTableParamModel param)
{
return Json(await repo.GetPagedAspNetUserItems(param));
}
public async Task<JsonResult> DriveDataTable(jQueryDataTableParamModel param)
{
return Json(await repo.GetPagedTopEntitiesItems(param));
}
public async Task<object> GetPagedAssignmentItems(jQueryDataTableParamModel param)
{
var assignmentsCount = await db.Assignments.CountAsync();
var assignments = from r in db.Assignments
select r;
if (param.searchColumn != null)
{
if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
{
var temp = param.searchColumn[0].ToUpper();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.AssignmentId.ToString()) > 0);
else if (temp.StartsWith("<"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
assignments = assignments.Where(r => r.AssignmentId < tempToNumber);
}
else if (temp.StartsWith(">"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
assignments = assignments.Where(r => r.AssignmentId > tempToNumber);
}
else if (temp.StartsWith("~"))
assignments = assignments.Where(r => r.AssignmentId.ToString().ToLower() != temp.Substring(1));
else
assignments = assignments.Where(r => r.AssignmentId.ToString().ToUpper() == temp);
}
if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
{
var temp = param.searchColumn[1].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.UserId.ToString().ToLower()) > 0);
else if (temp.StartsWith("~"))
assignments = assignments.Where(r => r.UserId.ToString().ToLower() != temp.Substring(1));
else
assignments = assignments.Where(r => r.UserId.ToString().ToLower() == temp);
}
if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
{
var temp = param.searchColumn[2].ToUpper();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
assignments = assignments.Where(r => SqlFunctions.PatIndex(temp, r.Key) > 0);
else if (temp.StartsWith("<"))
assignments = assignments.Where(r => String.Compare(r.Key.ToUpper(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
assignments = assignments.Where(r => String.Compare(r.Key.ToUpper(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
assignments = assignments.Where(r => r.Key.ToUpper() != temp.Substring(1));
else
assignments = assignments.Where(r => r.Key.ToUpper() == temp);
}
}
switch (param.orderByCol)
{
case 0:
assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.AssignmentId) : assignments.OrderByDescending(r => r.AssignmentId);
break;
case 1:
assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.UserId) : assignments.OrderByDescending(r => r.UserId);
break;
case 2:
assignments = param.orderDirection == "asc" ? assignments.OrderBy(r => r.Key) : assignments.OrderByDescending(r => r.Key);
break;
}
var filter = assignments;
var filterCount = await filter.CountAsync();
assignments = assignments.Skip(param.start).Take(param.length);
var data = await assignments.ToListAsync();
var dataFormatted = data.Select(x => new string[] {
x.AssignmentId.ToString(),
x.UserId.ToString(),
x.Key,
""
});
return new
{
recordsTotal = assignmentsCount,
recordsFiltered = filterCount,
data = dataFormatted
};
}
public async Task<object> GetPagedAspNetUserItems(jQueryDataTableParamModel param)
{
var usersCount = await db.AspNetUsers.CountAsync();
var users = from r in db.AspNetUsers
select r;
if (param.searchColumn != null)
{
if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
{
var temp = param.searchColumn[0].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
users = users.Where(r => SqlFunctions.PatIndex(temp, r.Id.ToLower()) > 0);
else if (temp.StartsWith("<"))
users = users.Where(r => String.Compare(r.Id.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
users = users.Where(r => String.Compare(r.Id.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
users = users.Where(r => r.Id.ToLower() != temp.Substring(1));
else
users = users.Where(r => r.Id.ToLower() == temp);
}
if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
{
var temp = param.searchColumn[1].ToLowerInvariant();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
users = users.Where(r => SqlFunctions.PatIndex(temp, r.UserName) > 0);
else if (temp.StartsWith("<"))
users = users.Where(r => String.Compare(r.UserName.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
users = users.Where(r => String.Compare(r.UserName.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
users = users.Where(r => r.UserName.ToLower() != temp.Substring(1));
else
users = users.Where(r => r.UserName.ToLower() == temp);
}
if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
{
var temp = param.searchColumn[2].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
users = users.Where(r => SqlFunctions.PatIndex(temp, r.Email) > 0);
else if (temp.StartsWith("<"))
users = users.Where(r => String.Compare(r.Email.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
users = users.Where(r => String.Compare(r.Email.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
users = users.Where(r => r.Email.ToLower() != temp.Substring(1));
else
users = users.Where(r => r.Email.ToLower() == temp);
}
if (param.searchColumn.Length > 3 && !String.IsNullOrWhiteSpace(param.searchColumn[3]))
{
var temp = param.searchColumn[3].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
users = users.Where(r => SqlFunctions.PatIndex(temp, r.PhoneNumber) > 0);
else if (temp.StartsWith("<"))
users = users.Where(r => String.Compare(r.PhoneNumber.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
users = users.Where(r => String.Compare(r.PhoneNumber.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
users = users.Where(r => r.PhoneNumber.ToLower() != temp.Substring(1));
else
users = users.Where(r => r.PhoneNumber.ToLower() == temp);
}
if (param.searchColumn.Length > 4 && !String.IsNullOrWhiteSpace(param.searchColumn[4]))
{
var temp = param.searchColumn[4].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
users = users.Where(r => SqlFunctions.PatIndex(temp, r.AccessFailedCount.ToString()) > 0);
else if (temp.StartsWith("<"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
users = users.Where(r => r.AccessFailedCount < tempToNumber);
}
else if (temp.StartsWith(">"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
users = users.Where(r => r.AccessFailedCount > tempToNumber);
}
else if (temp.StartsWith("~"))
{
users = users.Where(r => r.AccessFailedCount.ToString() != temp.Substring(1));
}
else
users = users.Where(r => r.AccessFailedCount.ToString().ToLower().Contains(temp));
}
}
switch (param.orderByCol)
{
case 0:
users = param.orderDirection == "asc" ? users.OrderBy(r => r.Id) : users.OrderByDescending(r => r.Id);
break;
case 1:
users = param.orderDirection == "asc" ? users.OrderBy(r => r.UserName) : users.OrderByDescending(r => r.UserName);
break;
case 2:
users = param.orderDirection == "asc" ? users.OrderBy(r => r.Email) : users.OrderByDescending(r => r.Email);
break;
case 3:
users = param.orderDirection == "asc" ? users.OrderBy(r => r.PhoneNumber) : users.OrderByDescending(r => r.PhoneNumber);
break;
case 4:
users = param.orderDirection == "asc" ? users.OrderBy(r => r.AccessFailedCount) : users.OrderByDescending(r => r.AccessFailedCount);
break;
}
var filter = users;
var filterCount = await filter.CountAsync();
users = users.Skip(param.start).Take(param.length);
var data = await users.ToListAsync();
var dataFormatted = data.Select(x => new string[] {
x.Id,
x.UserName,
x.Email,
x.PhoneNumber,
x.AccessFailedCount.ToString(),
""
});
return new
{
recordsTotal = usersCount,
recordsFiltered = filterCount,
data = dataFormatted
};
}
public async Task<object> GetPagedTopEntitiesItems(jQueryDataTableParamModel param)
{
var rAdminCount = await db.Entities.CountAsync();
var rAdmin = from r in db.Entities
select r;
if (param.searchColumn != null)
{
if (param.searchColumn.Length > 0 && !String.IsNullOrWhiteSpace(param.searchColumn[0]))
{
var temp = param.searchColumn[0].ToLower();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Key.ToLower()) > 0);
else if (temp.StartsWith("<"))
rAdmin = rAdmin.Where(r => String.Compare(r.Key.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
rAdmin = rAdmin.Where(r => String.Compare(r.Key.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
rAdmin = rAdmin.Where(r => r.Key.ToLower() != temp.Substring(1));
else
rAdmin = rAdmin.Where(r => r.Key.ToLower() == temp);
}
if (param.searchColumn.Length > 1 && !String.IsNullOrWhiteSpace(param.searchColumn[1]))
{
var temp = param.searchColumn[1].ToLowerInvariant();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Attributes) > 0);
else if (temp.StartsWith("<"))
rAdmin = rAdmin.Where(r => String.Compare(r.Attributes.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
rAdmin = rAdmin.Where(r => String.Compare(r.Attributes.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
rAdmin = rAdmin.Where(r => r.Attributes.ToLower() != temp.Substring(1));
else
rAdmin = rAdmin.Where(r => r.Attributes.ToLower() == temp);
}
if (param.searchColumn.Length > 2 && !String.IsNullOrWhiteSpace(param.searchColumn[2]))
{
var temp = param.searchColumn[2].ToLowerInvariant();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.Type) > 0);
else if (temp.StartsWith("<"))
rAdmin = rAdmin.Where(r => String.Compare(r.Type.ToLower(), temp.Substring(1)) < 0);
else if (temp.StartsWith(">"))
rAdmin = rAdmin.Where(r => String.Compare(r.Type.ToLower(), temp.Substring(1)) > 0);
else if (temp.StartsWith("~"))
rAdmin = rAdmin.Where(r => r.Type.ToLower() != temp.Substring(1));
else
rAdmin = rAdmin.Where(r => r.Type.ToLower() == temp);
}
if (param.searchColumn.Length > 3 && !String.IsNullOrWhiteSpace(param.searchColumn[3]))
{
var temp = param.searchColumn[3].ToUpper();
if (temp.Contains('%') || temp.Contains('_') || temp.Contains('[') || temp.Contains(']') || temp.Contains('^') || temp.Contains('!'))
rAdmin = rAdmin.Where(r => SqlFunctions.PatIndex(temp, r.SortKey.ToString()) > 0);
else if (temp.StartsWith("<"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
rAdmin = rAdmin.Where(r => r.SortKey < tempToNumber);
}
else if (temp.StartsWith(">"))
{
int tempToNumber = 0;
Int32.TryParse(temp.Substring(1), out tempToNumber);
rAdmin = rAdmin.Where(r => r.SortKey > tempToNumber);
}
else if (temp.StartsWith("~"))
rAdmin = rAdmin.Where(r => r.SortKey.ToString().ToLower() != temp.Substring(1));
else
rAdmin = rAdmin.Where(r => r.SortKey.ToString().ToUpper() == temp);
}
}
switch (param.orderByCol)
{
case 0:
rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Key) : rAdmin.OrderByDescending(r => r.Key);
break;
case 1:
rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Attributes) : rAdmin.OrderByDescending(r => r.Attributes);
break;
case 2:
rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(r => r.Type) : rAdmin.OrderByDescending(r => r.Type);
break;
case 3:
rAdmin = param.orderDirection == "asc" ? rAdmin.OrderBy(x => x.SortKey) : rAdmin.OrderByDescending(x => x.SortKey);
break;
}
var filter = rAdmin;
var filterCount = await filter.CountAsync();
rAdmin = rAdmin.Skip(param.start).Take(param.length);
var data = await rAdmin.ToListAsync();
var dataFormatted = data.Select(x => new string[] {
x.Key,
x.Attributes != null? ( x.Attributes.Length > 100 ? x.Attributes.Substring(0,100) + " ...": x.Attributes):"" ,
x.Type,
x.SortKey.ToString(),
""
});
return new
{
recordsTotal = rAdminCount,
recordsFiltered = filterCount,
data = dataFormatted
};
}