public async Task <List <ProjectUserFavData> > GetProjectsFavUser(int idUser)
{
IEnumerable <ProjectUserFavData> usersFav = await _controller.QueryAsync <ProjectUserFavData>
(" select g.GroupName, ps.Logo,ps.ProjectStudentId as ProjectId from IPR.tUserFavProject ufp " +
" join IPR.tProjectStudent ps on ufp.ProjectId = ps.ProjectStudentId" +
" join CK.tGroup g on g.GroupId = ps.ProjectStudentId" +
" where ufp.UserId = @UserId", new { UserId = idUser });
return(usersFav.AsList());
}
public async Task <List <EventData> > GetAllEventsByIdSchool(int idSchool)
{
IEnumerable <EventData> result = await _controller.QueryAsync <EventData>(
"SELECT es.EventId,g.GroupName as Name,es.BegDate,es.EndDate" +
" FROM IPR.tEventSchool es" +
" JOIN CK.tGroup g on g.GroupId = es.EventId" +
" JOIN CK.vZoneDirectChildren zdc on zdc.ChildId = g.ZoneId and zdc.ZoneId = @ZoneID" +
" ORDER BY es.EventId ASC", new { ZoneID = idSchool });
return(result.AsList());
}
public async Task <IEnumerable <ProjectData> > GetAllProjectBySchool(int schoolId)
{
IEnumerable <ProjectData> result = await _controller.QueryAsync <ProjectData>(
"SELECT *" +
"FROM IPR.vProjectsDetails" +
"WHERE ZoneId = @SchoolId",
new { SchoolId = schoolId });
return(result);
}
public async Task <List <SchoolData> > GetAllSchool( )
{
IEnumerable <SchoolData> result = await _controller.QueryAsync <SchoolData>("SELECT SchoolId, [Name] FROM IPR.tSchool ORDER BY SchoolId;");
return(result.AsList());
}
public async Task <IEnumerable <TimedStudentData> > GetAllStudentInfosByGroup(int groupId, string userTimedTable, string specificTimedUserId)
{
IEnumerable <TimedStudentData> result = await _controller.QueryAsync <TimedStudentData>("SELECT * FROM CK.tGroup g JOIN CK.tActor a ON a.ActorId = g.GroupId AND g.GroupId = @GroupId JOIN CK.tActorProfile ap ON ap.GroupId = g.GroupId AND ap.ActorId <> ap.GroupId JOIN CK.tUser u ON u.UserId = ap.ActorId JOIN IPR.tTimedUser tu ON tu.UserId = u.UserId AND tu.TimePeriodId = g.ZoneId JOIN IPR.t" + userTimedTable + " ts ON ts.Timed" + specificTimedUserId + " = tu.TimedUserId AND ts.Timed" + specificTimedUserId + " is not null;", new { GroupId = groupId });
return(result);
}
public async Task <IEnumerable <ForumData> > ForumInfoByJury(string juryName)
{
IEnumerable <ForumData> result = await _controller.QueryAsync <ForumData>("SELECT Jury = g.GroupName, e.Date, Project = g1.GroupName FROM CK.tGroup g JOIN IPR.tEvaluates e ON e.JuryId = g.GroupId LEFT OUTER JOIN IPR.tProjectStudent ps ON ps.ProjectStudentId = e.ProjectId LEFT OUTER JOIN CK.tGroup g1 on g1.GroupId = e.ProjectId WHERE g.GroupName = @JuryName", new { JuryName = juryName });
return(result);
}
public async Task <List <PeriodData> > GetNumberWishPeriodBySchool(int schoolId, int numberWish)
{
IEnumerable <PeriodData> result = await _controller.QueryAsync <PeriodData>("SELECT TOP(@NumberWish) OrderedChild = ChildOrderByKey.ToString(), ChildId, ZoneId, BegDate,EndDate FROM CK.vZoneDirectChildren JOIN IPR.tTimePeriod on ChildId = TimePeriodId WHERE ZoneId = @ZoneId ORDER BY ChildOrderByKey DESC; ", new { ZoneId = schoolId, NumberWish = numberWish });
return(result.AsList());
}
public async Task <List <string> > GetAllGroupByZoneId(int zoneID)
{
IEnumerable <string> list = await _controller.QueryAsync <string>("select GroupName from CK.vGroup where ZoneId = 0 AND IsZone = 0 OR ZoneId = @ZoneId AND IsZone = 0 group by GroupName;", new { ZoneId = zoneID });
return(list.AsList());
}