public List <UserListModel> GetUserListModel(string search_key = null)
{
List <UserListModel> list = new List <UserListModel>();
var u_repo = new BaseDapperRepository <BTCConnection, UserListModel>();
if (!string.IsNullOrWhiteSpace(search_key))
{
search_key = "%" + search_key + "%";
}
else
{
search_key = null;
}
list = u_repo.GetByCustomQuery(@"select
u.Email,
u.FirstName + ' ' + u.LastName as [FullName],
u.Phone,
u.ID,
u.IsActive,
u.IsApproved,
u.IsVip,
u.CreateDate,
(select COUNT(*) from UserRoleRels ur where ur.UserID = u.ID and ur.RoleID = 1) as [IsAdmin],
(select COUNT(*) from UserRoleRels ur where ur.UserID = u.ID and ur.RoleID = 2) as [IsWriter],
(select COUNT(*) from UserRoleRels ur where ur.UserID = u.ID and ur.RoleID = 3) as [IsMember],
(select COUNT(*) from UserRoleRels ur where ur.UserID = u.ID and ur.RoleID = 4) as [IsSupplier]
from Users u where (@Key is null or (u.Email like @Key or u.FirstName like @Key or u.LastName like @Key or u.Email like @Key))", new { Key = search_key }).ToList();
return(list);
}
public List <ContentViewListModel> GetListViewPublished()
{
var cRepo = new BaseDapperRepository <BTCConnection, ContentViewListModel>();
var list = cRepo.GetByCustomQuery("select Title, Uri, RowNumber, CanSeeUser, CanSeeMember,CanSeeTrader,CanSeeWriter,CanSeeVip from ContentViews where IsPublish = 1 ", null);
return(list);
}
public List <CommentListModel> GetWaitingComments()
{
var cRepo = new BaseDapperRepository <BTCConnection, CommentListModel>();
var list = cRepo.GetByCustomQuery(@"select up.Title as [PostTitle], c.* from Comments c
inner join UserPosts up on up.ID = c.PostID
where c.IsPublish = 0", null).ToList();
return(list);
}
public List <UserSelectListModel> GetSelectedUsers(int role_id)
{
var viewRepo = new BaseDapperRepository <BTCConnection, UserSelectListModel>();
var list = viewRepo.GetByCustomQuery(@"
select u.FirstName + ' ' + u.LastName as [FullName] , u.ID as [ID] from Users u
inner join UserRoleRels ur on ur.UserID = u.ID
where ur.RoleID = @RoleID", new { RoleID = role_id }).ToList();
return(list);
}
public DasboardMainModel GetDashboard()
{
var viewRepo = new BaseDapperRepository <BTCConnection, DasboardMainModel>();
var result = viewRepo.GetByCustomQuery(@"
select
COUNT(*) as [CategoryCount],
(select COUNT(*) from UserPosts where CategoryID != 8) as PostCount,
(select COUNT(*) from UserPosts where CategoryID = 8) as NewsCount,
(select COUNT(*)from UserProducts) as ProductCount,
(select COUNT(*) from Users) as UserCount,
(select COUNT(*) from ContentViews) as ContentCount,
(select COUNT(*) from Users where IsVip = 1 ) as VipCount,
(select COUNT(*) from Users where IsVip = 1 ) as CommentCount
from Categories c
", null).FirstOrDefault();
result.BestPosts = new UserPostRepository().GetByCustomQuery("select top 10 * from UserPosts where IsPublish = 1 order by ViewCount desc", null);
result.Posts = new UserPostRepository().GetByCustomQuery("select top 10 * from UserPosts where IsPublish = 1 and CategoryId != 8 order by CreateDate desc", null);
result.Comments = new CommentRepository().GetByCustomQuery("select top 10 * from Comments order by CreateDate desc", null);
result.News = new UserPostRepository().GetByCustomQuery("select top 10 * from UserPosts where IsPublish = 1 and CategoryId = 8 order by CreateDate desc", null);
result.Users = new UserRepository().GetByCustomQuery("select top 10 * from Users order by CreateDate desc", null);
return(result);
}
