// OK
public IList <ProjectModel> GetUserFundedProjects(int userId)
{
var _user = uow.UserRepository.FindById(userId);
//user not found
if (_user == null)
{
return(new List <ProjectModel>()
{
});
}
else
{
using (var ctx = new VivaWalletEntities())
{
//Get user funded projects
return(ctx.Database.SqlQuery <ProjectModel>(
@"
SELECT
p.Id Id,
pc.Id ProjectCategoryId,
pc.Name ProjectCategoryDesc,
p.AttachmentSetId AttachmentSetId,
p.Title Title,
p.Description Description,
p.CreatedDate CreatedDate,
p.UpdateDate UpdateDate,
p.FundingEndDate FundingEndDate,
p.FundingGoal FundingGoal,
p.Status Status,
p.UserId OwnerId,
u.Name OwnerName
FROM
UserFundings uf
LEFT JOIN
FundingPackages fp
ON
uf.FundingPackageId = fp.Id
LEFT JOIN
Projects p
ON
fp.ProjectId = p.Id
LEFT JOIN
ProjectCategories pc
ON
p.ProjectCategoryId = pc.Id
LEFT JOIN
Users u
ON
p.UserId = u.Id
WHERE
uf.UserId = {0}
ORDER BY
uf.WhenDateTime DESC
", userId
).ToList <ProjectModel>());
}
}
}
// OK
public IList <ProjectModel> GetTrendingProjects()
{
using (var ctx = new VivaWalletEntities())
{
//Get trending projects
return(ctx.Database.SqlQuery <ProjectModel>(
@"
SELECT
TOP 10
pro.Id Id, pro.Title, Title, pro.CreatedDate CreatedDate,
pro.Description Description, pro.FundingEndDate FundingEndDate,
pro.FundingGoal FundingGoal, us.Id OwnerId, us.Name OwnerName,
pc.Name ProjectCategoryDesc, pc.Id ProjectCategoryId,
pro.Status Status, pro.UpdateDate UpdateDate
FROM
Projects pro
LEFT JOIN
ProjectStats ps
ON
ps.ProjectId = pro.Id
LEFT JOIN
Users us
ON
us.Id = pro.UserId
LEFT JOIN
ProjectCategories pc
ON
pro.ProjectCategoryId = pc.Id
ORDER BY
ps.MoneyPledged DESC, ps.BackersNo DESC, ps.SharesNo DESC, ps.CommentsNo DESC;
"
).ToList());
}
}
public IList <ProjectModel> GetUserFundedCompletedProjects(ClaimsIdentity identity, bool showAll)
{
long currentUserId;
try
{
currentUserId = uow.UserRepository
.SearchFor(e => e.Username == identity.Name)
.Select(e => e.Id)
.SingleOrDefault();
}
catch (InvalidOperationException ex)
{
throw new InvalidOperationException("User lookup for current logged in User Id failed in GetUserFundedProjectsCompletedNotifications", ex);
}
//get user funded projects that have status = "COM"
using (var ctx = new VivaWalletEntities())
{
//first return rows as IEnumerable - Reason? A user may have backed this project
//that completed multiple times
//as a result we may end have many same rows
//create a function distinctBy to remove same entries from the IEnumerable
IOrderedQueryable <ProjectModel> userFundingsOrderedQueryable = ctx.UserFundings
.Join(ctx.FundingPackages, uf => uf.FundingPackageId, fp => fp.Id, (uf, fp) => new { uf, fp })
.Join(ctx.Projects, uffp => uffp.fp.ProjectId, pr => pr.Id, (uffp, pr) => new { uffp.fp, uffp.uf, pr })
.Where(uffppr => (uffppr.uf.UserId == currentUserId && uffppr.pr.Status == "COM"))
.Select(uffppr => new ProjectModel()
{
Id = uffppr.pr.Id,
OwnerId = uffppr.pr.UserId,
OwnerName = uffppr.pr.User.Name,
ProjectCategoryId = uffppr.pr.ProjectCategoryId,
ProjectCategoryDesc = uffppr.pr.ProjectCategory.Name,
Title = uffppr.pr.Title,
Description = uffppr.pr.Description,
CreatedDate = uffppr.pr.CreatedDate,
UpdatedDate = uffppr.pr.UpdateDate,
FundingEndDate = uffppr.pr.FundingEndDate,
FundingGoal = uffppr.pr.FundingGoal,
Status = uffppr.pr.Status
}).OrderByDescending(e => e.UpdatedDate);
IEnumerable <ProjectModel> userFundings;
//if showAll true show all completed projects
if (showAll)
{
userFundings = userFundingsOrderedQueryable.AsEnumerable();
}
//else show top most recent completed
else
{
userFundings = userFundingsOrderedQueryable.Take(5).AsEnumerable();
}
//return the filtered set of rows as a IList for the view to render
return(UserRepository.DistinctBy(userFundings, p => p.Id).ToList());
}
}
public UnitOfWork()
{
_dbContext = new VivaWalletEntities();
}
public Repository(VivaWalletEntities _context = null)
{
context = _context ?? new VivaWalletEntities();
dbSet = context.Set <TEntity>();
}