public async Task <UserPagination> GetAll(string query, int page, int itemCount)
{
var users = this.eventAppDbContext.Users.ToList();
if (query == null)
{
if (users.Count < itemCount)
{
UserPagination result = new UserPagination()
{
Users = users,
maxPage = (int)Math.Ceiling((double)users.Count / itemCount),
totalUsers = users.Count
};
return(result);
}
else
{
UserPagination result = new UserPagination()
{
Users = users.Skip((page - 1) * itemCount).Take(itemCount).ToList(),
maxPage = (int)Math.Ceiling((double)users.Count / itemCount),
totalUsers = users.Count
};
return(result);
}
}
else
{
List <User> foundUsers = new List <User>();
foreach (var user in users)
{
if (user.FirstName.ToLower().IndexOf(query.ToLower()) != -1)
{
foundUsers.Add(user);
}
}
if (foundUsers.Count < itemCount)
{
UserPagination result = new UserPagination()
{
Users = foundUsers,
maxPage = 1,
totalUsers = foundUsers.Count
};
return(result);
}
else
{
UserPagination result = new UserPagination()
{
Users = foundUsers.Skip((page - 1) * itemCount).Take(itemCount).ToList(),
maxPage = (int)Math.Ceiling((double)foundUsers.Count / itemCount),
totalUsers = foundUsers.Count
};
return(result);
}
}
}
public async Task<IHttpActionResult> Get(int page = 1, int pagesize = 20, string sortdirection = "ascending",
string sortby = "Active", bool onlyShowActive = false, string query = null)
{
int skip = (pagesize * (page - 1));
var totalUsers = this.userRepository.GetCount();
var activeCount = this.cacheService.TryGet("ActiveUserCount", GetActiveCount, null);
string cacheKey = User.GetType().FullName + ":skip:" +
skip + ":take:" + pagesize + ":active:" + onlyShowActive;
ICollection<User> userList;
if (this.cacheService.Contains(cacheKey))
{
userList = (ICollection<User>)this.cacheService[cacheKey];
}
else
{
userList = await GetUserList(pagesize, sortby, skip, onlyShowActive);
this.cacheService.Add(cacheKey, userList);
}
var users = new UserPagination(userList, totalUsers, page, pagesize,
activeCount, ParseSort(sortdirection))
{
BaseUrl = "users",
SortBy = sortby
};
return await Task.FromResult((IHttpActionResult)this.Ok(users));
}
public ActionResult GetUsers([FromQuery] UserPagination userPagination)
{
var pageUsers = _userRepository.GetPagingUser(userPagination);
if (pageUsers == null || pageUsers.Count == 0)
{
return(NotFound());
}
else
{
var previousLink = pageUsers.HasPrevious ? CreateUserUri(userPagination, PaginationResourceUriType.PreviousPage) : null;
var nextLink = pageUsers.HasNext ? CreateUserUri(userPagination, PaginationResourceUriType.NextPage) : null;
var meta = new
{
pageUsers.TotalItemCount,
pageUsers.PaginationBase.PageIndex,
pageUsers.PaginationBase.PageSize,
pageUsers.PageCount,
PreviousPageLink = previousLink,
NextPageLink = nextLink
};
Response.Headers.Add("X-Pagination", JsonConvert.SerializeObject(meta));
return(new JsonResult(pageUsers));
}
}
public async Task <IActionResult> Count([FromQuery] UserPagination pagination)
{
var count = await repository.Count(pagination);
return(Json(new StandardResponse
{
Status = 200,
Data = count
}));
}
public async Task <IActionResult> Get([FromQuery] UserPagination pagination)
{
var users = await repository.All(pagination);
return(Json(new StandardResponse
{
Status = 200,
Data = users
}));
}
public Task <List <User> > All(UserPagination pagination)
{
var query =
context.Users
.Include(u => u.Role)
.Include(u => u.EmissionPoint)
.Include(u => u.Turn)
.Skip(pagination.Page * pagination.Quantity - pagination.Quantity)
.Take(pagination.Quantity);
if (pagination.IsActive != "-")
{
query = query.Where(u => u.IsActive == Boolean.Parse(pagination.IsActive));
}
if (pagination.Role != 0)
{
query = query.Where(u => u.Role.Id == pagination.Role);
}
if (pagination.EmissionPoint != 0)
{
query = query.Where(u => u.EmissionPoint.Id == pagination.EmissionPoint);
}
query = query.OrderBy(u => u.Name);
return(query.ToListAsync());
//return await (
// query
// .Include(u => u.Role)
// .Include(u => u.EmissionPoint)
// .Skip(pagination.Page * pagination.Quantity - pagination.Quantity)
// .Take(pagination.Quantity)
// //from u in context.Users
// //select new User
// //{
// // Id = u.Id,
// // Name = u.Name,
// // Username = u.Username,
// // Email = u.Email,
// // // Password = u.Password,
// // Role = u.Role,
// // IsActive = u.IsActive
// //}
//).ToListAsync();
}
public void Pagination()
{
string get = System.Web.Configuration.WebConfigurationManager
.ConnectionStrings["ProjectConnection"].ConnectionString;
SqlConnection getConnection = new SqlConnection(get);
SqlCommand command =
new SqlCommand("SELECT count(*) FROM YachtsNews Where 1=1 ", getConnection);
SqlDataAdapter loginAdapter = new SqlDataAdapter(command);
DataTable user = new DataTable();
loginAdapter.Fill(user);
UserPagination.limit = limit; //每頁數量
UserPagination.totalitems = Convert.ToInt32(user.Rows[0][0]); //資料總量
UserPagination.targetpage = "NewsIndex.aspx"; //連結文字，例:pagination.aspx?page=100
UserPagination.showPageControls();
}
public UserPagination ListStudents(int page)
{
var pagination = new UserPagination();
#region Users
string rowsQuery = $"EXECUTE SP_ListStudents {page}";
pagination.Users = _db.Database.SqlQuery <UserBinder>(rowsQuery).ToList();
#endregion
#region TotalRows
string totalQuery = $"EXECUTE SP_StudentTotalRows";
pagination.TotalRows = _db.Database.SqlQuery <int>(totalQuery).FirstOrDefault();
#endregion
return(pagination);
}
public Task <int> Count(UserPagination pagination)
{
var query = from u in context.Users select u;
if (pagination.IsActive != "-")
{
query = query.Where(u => u.IsActive == Boolean.Parse(pagination.IsActive));
}
if (pagination.Role != 0)
{
query = query.Where(u => u.Role.Id == pagination.Role);
}
if (pagination.EmissionPoint != 0)
{
query = query.Where(u => u.EmissionPoint.Id == pagination.EmissionPoint);
}
return(query.CountAsync());
}
