public function findAvailableCourses(User $user)
{
$items = $this->conn->fetchAll('SELECT c.`id`, c.`name`, c.`deadline`, r.`result` AS `user_result`, r.`passedQuestions` AS `user_passedQuestions`, ' . 'r.`totalQuestions` AS `user_totalQuestions`, r.`completedAt` AS `user_completedAt`, arr.`result` AS `area_result`, arr.`passedQuestions` AS `area_passedQuestions`, ' . 'arr.`totalQuestions` AS `area_totalQuestions`, arr.`completedAt` AS `area_completedAt` ' . 'FROM `' . CourseTables::COURSE_TBL . '` c ' . 'LEFT JOIN `' . CourseTables::COURSE_AREA_RESULT_TBL . '` ar ON (ar.`courseId` = c.`id` AND ar.`areaId` = :areaId) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` arr ON (arr.`courseId` = ar.`courseId` AND arr.`userId` = ar.`userId`) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` r ON (r.`courseId` = c.`id` AND r.`userId` = :userId) ' . 'WHERE c.`isPublished` = 1 AND c.`projectId` = :projectId ORDER BY c.`displayOrder`', [':areaId' => $this->area->getId(), ':userId' => $user->getId(), ':projectId' => $this->area->getProject()->getId()]);
foreach ($items as &$item) {
TestResult::processResults($item, 'user_');
TestResult::processResults($item, 'area_');
}
return $items;
}
public function findTotalIndividualResultsForArea(Area $area)
{
$items = $this->conn->fetchAll('SELECT c.`id` AS `courseId`, c.`name` AS `courseName`, u.`id` AS `userId`, u.`name` AS `userName`, u.`avatar`, ' . 'ur.`result`, ur.`totalQuestions`, ur.`passedQuestions`, ur.`completedAt`, ur.`trialNumber` ' . 'FROM `' . CourseTables::COURSE_TBL . '` c ' . 'INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` m ON m.`areaId` = :areaId ' . 'INNER JOIN `' . CoreTables::USER_TBL . '` u ON u.`id` = m.`userId` ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` ur ON ur.`userId` = u.`id` AND c.`id` = ur.`courseId` ' . 'WHERE c.`isPublished` = 1 AND u.`active` = 1 AND c.`projectId` = :projectId ' . 'ORDER BY c.`displayOrder`, u.`name`', [':areaId' => $area->getId(), ':projectId' => $area->getProject()->getId()]);
foreach ($items as &$item) {
TestResult::processResults($item);
}
return $items;
}
