public async Task <int> GetJuryId(int userId, int timePeriodId)
{
return(await _controller.QueryFirstOrDefaultAsync <int>(
" SELECT e.JuryId" +
" FROM IPR.tEvaluates e " +
" JOIN CK.tActorProfile ap on ap.GroupId = e.JuryId" +
" JOIN CK.tGroup g on g.GroupId = e.ProjectId" +
" JOIN IPR.tProjectStudent ps on ps.ProjectStudentId = e.ProjectId" +
" where ap.ActorId = @UserId and g.ZoneId = @TimePeriodId", new { UserId = userId, TimePeriodId = timePeriodId }));
}
public async Task <EventData> GetEventSchoolBylNameAndPeriodId(string name, int periodId)
{
EventData result = await _controller.QueryFirstOrDefaultAsync <EventData>(
"SELECT *" +
" FROM IPR.tEventSchool es" +
" JOIN CK.tGroup g on g.GroupId = es.EventId" +
" where g.ZoneId = @TimePeriodID and g.GroupName like CONCAT(@Name,'%')", new { TimePeriodID = periodId, Name = name });
return(result);
}
public async Task <ProjectData> GetDetailProject(int idProject)
{
ProjectData project = await _controller.QueryFirstOrDefaultAsync <ProjectData>(
" SELECT ps.Logo, ps.Slogan, ps.Pitch, g.GroupName as [Name], pu.[Url], ps.LeaderId, ps.[Type],ps.Background,fi.ClassRoom,fi.ForumNumber" +
" from IPR.tProjectStudent ps" +
" join CK.tGroup g on g.GroupId = ps.ProjectStudentId" +
" left outer join IPR.tProjectUrl pu on pu.ProjectId = ps.ProjectStudentId" +
" LEFT OUTER JOIN IPR.tForumInfos fi on fi.ProjectId = ps.ProjectStudentId" +
" where ProjectStudentId = @ProjectId",
new { ProjectId = idProject });
//" SELECT ps.Logo, ps.Slogan, ps.Pitch, g.GroupName as [Name], pu.[Url], ps.LeaderId, ps.[Type], ps.Background, g.GroupName as Semester" +
//" FROM IPR.tProjectStudent ps" +
//" LEFT OUTER JOIN CK.tActorProfile ap on ap.ActorId = ps.ProjectStudentId" +
//" LEFT OUTER JOIN CK.tGroup g on g.GroupId = ap.GroupId" +
//" LEFT OUTER JOIN CK.tGroup g1 on g1.GroupId = ps.ProjectStudentId" +
//" LEFT OUTER JOIN IPR.tForumInfos fi on fi.ProjectId = ps.ProjectStudentId" +
//" LEFT OUTER JOIN IPR.tProjectUrl pu on pu.ProjectId = ps.ProjectStudentId " +
//" WHERE ps.ProjectStudentId = @ProjectId and g.GroupName <> g1.GroupName and g.GroupName like 'S0%'",
// new { ProjectId = idProject } );
string technos = await _controller.QueryFirstOrDefaultAsync <string>(
" SELECT ckt.TraitName as Technologies" +
" from IPR.tProjectStudent ps" +
" join CK.tGroup g on g.GroupId = ps.ProjectStudentId" +
" join CK.tCKTrait ckt on ps.TraitId = ckt.CKTraitId" +
" where ProjectStudentId = @ProjectId",
new { ProjectId = idProject });
project.Technologies = technos.Split(';').AsList();
return(project);
}