public IEnumerable <UserFromTable> GetUserFrom()
{
using (var db = new AALifeDbContext())
{
return(db.Set <UserFromTable>().OrderBy(a => a.Rank).ToList());
}
}
public IEnumerable <WorkDayTable> GetWorkDay()
{
using (var db = new AALifeDbContext())
{
return(db.Set <WorkDayTable>().OrderBy(a => a.Rank).ToList());
}
}
/// <summary>
/// 取用户根据日期
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public IEnumerable <UserTableViewModel> GetUserTable(QueryPageModel query, out int count)
{
using (var db = new AALifeDbContext())
{
//默认
var lists = db.Set <UserTable>()
.Where(a => a.CreateDate >= query.startDate && a.CreateDate <= query.endDate);
//关键字
if (query.keySearch != null && query.keySearch.Any())
{
int userId = 0;
Int32.TryParse(query.keySearch, out userId);
lists = db.Set <UserTable>().Where(a => a.UserID == userId || a.UserName.Contains(query.keySearch) || a.UserPassword.Contains(query.keySearch) || a.UserNickName.Contains(query.keySearch) || a.UserEmail.Contains(query.keySearch));
}
//结果
var result = lists
.Include(a => a.UserFromTable)
.Include(a => a.WorkDayTable)
.ProjectToType <UserTableViewModel>();
//where查询
if (query.filter != null)
{
result = result.Where(query.filterString);
}
//排序
if (query.sort != null && query.sort.Count > 0)
{
result = result.OrderBy(query.sortString);
}
else
{
result = result.OrderByDescending(a => a.UserID);
}
//总数
count = result.Count();
//返回
return(result.Skip(query.skip).Take(query.take).ToList());
}
}
/// <summary>
/// 获取全部
/// </summary>
/// <returns></returns>
public IQueryable <ItemTableView2019> GetAll()
{
var db = new AALifeDbContext();
//默认
var lists = db.Set <ItemTableView2019>()
.AsNoTracking();
return(lists);
}
/// <summary>
/// 更新用户关像
/// </summary>
/// <param name="userId"></param>
/// <param name="userImage"></param>
public void UpdateUserImage(int userId, string userImage)
{
using (var db = new AALifeDbContext())
{
var user = db.Set <UserTable>().Find(userId);
user.UserImage = userImage;
db.SaveChanges();
}
}
/// <summary>
/// 固定类别
/// </summary>
/// <returns></returns>
public IEnumerable <RegionTypeTable> GetRegionType()
{
using (var db = new AALifeDbContext())
{
//默认
var lists = db.Set <RegionTypeTable>()
.AsNoTracking();
return(lists.ToList());
}
}
/// <summary>
/// 取1个用户根据id
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public UserTableViewModel GetUser(int userId)
{
using (var db = new AALifeDbContext())
{
var item = db.Set <UserTable>().Where(a => a.UserID == userId);
var result = item
.Include(a => a.UserFromTable)
.Include(a => a.WorkDayTable)
.ProjectToType <UserTableViewModel>();
return(result.FirstOrDefault());
}
}
/// <summary>
/// 页面表
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
public IEnumerable <ViewPageTable> GetViewPageTable(out int count)
{
using (var db = new AALifeDbContext())
{
//默认
var lists = db.Set <ViewPageTable>()
.AsNoTracking()
.AsQueryable();
//总数
count = lists.Count();
return(lists.ToList());
}
}
/// <summary>
/// 检查用户是否存在
/// </summary>
/// <param name="models"></param>
/// <param name="userName"></param>
public bool CheckUserExists(UserTableViewModel models, string type)
{
using (var db = new AALifeDbContext())
{
var exists = false;
switch (type)
{
case "UserName":
exists = db.Set <UserTable>().Any(a => a.UserName == models.UserName);
break;
}
return(exists);
}
}
/// <summary>
/// 取用户根据关键字
/// </summary>
/// <param name="keySearch"></param>
/// <returns></returns>
public IEnumerable <UserTableViewModel> GetUserTable(string keySearch)
{
using (var db = new AALifeDbContext())
{
int userId = 0;
Int32.TryParse(keySearch, out userId);
var lists = db.Set <UserTable>().Where(a => a.UserID == userId || a.UserName.Contains(keySearch) || a.UserPassword.Contains(keySearch) || a.UserNickName.Contains(keySearch) || a.UserEmail.Contains(keySearch)).OrderByDescending(a => a.UserID);
var result = lists
.Include(a => a.UserFromTable)
.Include(a => a.WorkDayTable)
.ProjectToType <UserTableViewModel>();
return(result.ToList());
}
}
/// <summary>
/// 获取消费列表
/// </summary>
/// <param name="query"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataSourceResult GetViewTable(int take, int skip, IEnumerable <Sort> sort, Filter filter, IEnumerable <Aggregator> aggregates)
{
using (var db = new AALifeDbContext())
{
//默认
var lists = db.Set <ViewTable>()
.AsNoTracking()
.AsQueryable();
//结果
var result = lists
.Include(a => a.ViewPageTable)
.ProjectToType <ViewTableViewModel>();
//排序
result = result.OrderByDescending(a => a.ViewID);
//返回
return(result.ToDataSourceResult(take, skip, sort, filter, aggregates));
}
}
/// <summary>
/// 获取消费列表
/// </summary>
/// <param name="pageModels"></param>
/// <param name="count"></param>
/// <returns></returns>
public IEnumerable <ItemTableViewModel> GetItemTable(QueryPageModel pageModels, out int count)
{
using (var db = new AALifeDbContext())
{
//默认
var lists = db.Set <ItemTable>()
.AsNoTracking()
.Where(a => a.ItemBuyDate >= pageModels.startDate && a.ItemBuyDate <= pageModels.endDate);
//关键字
if (pageModels.keySearch != null && pageModels.keySearch.Any())
{
lists = db.Set <ItemTable>().AsNoTracking().Where(a => a.ItemName.Contains(pageModels.keySearch));
}
//用户id
if (pageModels.userId.GetValueOrDefault() > 0)
{
lists = lists.Where(a => a.UserID == pageModels.userId);
}
#region groupjoin old
////结果
//var viewModel = lists
// .Include(a => a.ItemTypeTable)
// .Include(a => a.CategoryTypeTable)
// .Include(a => a.RegionTypeTable)
// .GroupJoin(db.CardTable, a => new { a.UserID, a.CardID.Value }, b => new { b.UserID, b.CDID.Value }, (a, b) => new
// {
// ItemTable = a,
// CardTable = b
// })
// .GroupJoin(db.ZhuanTiTable, a => new { a.ItemTable.UserID, a.ItemTable.ZhuanTiID.Value }, b => new { b.UserID, b.ZTID.Value }, (a, b) => new
// {
// ItemTable = a.ItemTable,
// CardTable = a.CardTable,
// ZhuanTiTable = b
// })
// .GroupJoin(db.UserCategoryTable, a => new { a.ItemTable.UserID, a.ItemTable.CategoryTypeID }, b => new { b.UserID, b.CategoryTypeID }, (a, b) => new
// {
// ItemTable = a.ItemTable,
// CardTable = a.CardTable,
// ZhuanTiTable = a.ZhuanTiTable,
// UserCategoryTable = b
// })
// .Select(a => new ItemTableViewModel
// {
// ItemID = a.ItemTable.ItemID,
// ItemName = a.ItemTable.ItemName,
// CategoryTypeID = a.ItemTable.CategoryTypeID,
// CategoryTypeName = a.UserCategoryTable.FirstOrDefault().CategoryTypeName ?? a.ItemTable.CategoryTypeTable.CategoryTypeName,
// ItemPrice = a.ItemTable.ItemPrice,
// ItemBuyDate = a.ItemTable.ItemBuyDate,
// UserID = a.ItemTable.UserID,
// Recommend = a.ItemTable.Recommend,
// ModifyDate = a.ItemTable.ModifyDate,
// Synchronize = a.ItemTable.Synchronize,
// ItemAppID = a.ItemTable.ItemAppID,
// RegionID = a.ItemTable.RegionID,
// RegionType = a.ItemTable.RegionType,
// RegionTypeName = a.ItemTable.RegionTypeTable.RegionTypeName,
// ItemType = a.ItemTable.ItemType,
// ItemTypeName = a.ItemTable.ItemTypeTable.ItemTypeName,
// ZhuanTiID = a.ItemTable.ZhuanTiID,
// ZhuanTiName = a.ZhuanTiTable.FirstOrDefault().ZhuanTiName,
// CardID = a.ItemTable.CardID,
// CardName = a.CardTable.FirstOrDefault().CardName ?? Constant.CardName
// });
#endregion
//结果
var viewModel = lists
.Include(a => a.ItemTypeTable)
.ProjectToType <ItemTableViewModel>();
//排序
if (pageModels.sort != null && pageModels.sort.Count > 0)
{
viewModel = viewModel.OrderBy(pageModels.sortString);
}
else
{
viewModel = viewModel.OrderByDescending(a => a.ItemID);
}
//总数
count = viewModel.Count();
//返回
return(viewModel.Skip(pageModels.page).Take(pageModels.rows).ToList());
}
}