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 listData(DataTable $dataTable, TranslatorInterface $translator)
{
$qb = QueryBuilder::select()->field('i.id', 'id');
if (!$this->root instanceof Area) {
$qb->field('a.id', 'areaId');
$qb->field('a.name', 'areaName');
$qb->join(CoreTables::AREA_TBL, 'a', QueryClause::clause('a.id = i.areaId'));
}
$qb->field('i.subject', 'subject')->field('i.createdAt', 'createdAt')->field('i.status', 'status')->field('u.id', 'responderId')->field('u.name', 'responder')->field('i.duplicate', 'duplicate')->from(EdkTables::MESSAGE_TBL, 'i')->leftJoin(CoreTables::USER_TBL, 'u', QueryClause::clause('u.id = i.responderId'))->orderBy('i.status', 'ASC')->orderBy('i.createdAt', 'ASC');
if ($this->root instanceof Area) {
$qb->where(QueryClause::clause('i.`areaId` = :areaId', ':areaId', $this->root->getId()));
} elseif ($this->root instanceof Group) {
$qb->where(QueryClause::clause('a.`groupId` = :groupId', ':groupId', $this->root->getId()));
} elseif ($this->root instanceof Project) {
$qb->where(QueryClause::clause('a.`projectId` = :projectId', ':projectId', $this->root->getId()));
}
$qb->postprocess(function ($row) use($translator) {
$row['statusText'] = $translator->trans(EdkMessage::statusText($row['status']), [], 'edk');
$row['statusLabel'] = EdkMessage::statusLabel($row['status']);
$row['createdAt'] = $this->timeFormatter->ago($row['createdAt']);
return $row;
});
$recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn);
$recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(i.id)', 'cnt')->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchCell($this->conn);
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
protected function performResults($courseInfoPage, $profilePage, Area $area, Request $request)
{
$repository = $this->get(self::REPOSITORY_NAME);
$text = $this->getTextRepository()->getText(CourseTexts::AREA_COURSE_LIST_TEXT, $request);
$this->breadcrumbs()->link($area->getName(), $this->crudInfo->getInfoPage(), ['id' => $area->getId(), 'slug' => $this->getSlug()]);
return $this->render($this->crudInfo->getTemplateLocation() . 'other-individual-results.html.twig', array('pageSubtitle' => $this->crudInfo->getPageSubtitle(), 'courseInfoPage' => $courseInfoPage, 'userProfilePage' => $profilePage, 'area' => $area, 'items' => $repository->findTotalIndividualResultsForArea($area), 'indexPage' => $this->crudInfo->getIndexPage()));
}
public static function fetchNotes(Connection $conn, Area $area)
{
$item = new EdkAreaNotes($area);
$notes = $conn->fetchAll('SELECT * FROM `' . EdkTables::AREA_NOTE_TBL . '` WHERE `areaId` = :areaId', [':areaId' => $area->getId()]);
foreach ($notes as $note) {
$item->notes[$note['noteType']] = $note['content'];
}
return $item;
}
public function countParticipants()
{
$this->transaction->requestTransaction();
try {
return $this->conn->fetchColumn('SELECT SUM(s.`participantNum`) + SUM(s.`externalParticipantNum`) ' . 'FROM `' . EdkTables::REGISTRATION_SETTINGS_TBL . '` s ' . 'INNER JOIN `' . EdkTables::ROUTE_TBL . '` r ON r.`id` = s.`routeId` ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . $this->createWhereClause(), [':itemId' => $this->root->getId()]);
} catch (Exception $ex) {
$this->transaction->requestRollback();
throw $ex;
}
}
public function countRoutes()
{
$this->transaction->requestTransaction();
try {
return $this->conn->fetchColumn('SELECT COUNT(r.`id`) ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . $this->createWhereClause(), [':itemId' => $this->root->getId()]);
} catch (Exception $ex) {
$this->transaction->requestRollback();
throw $ex;
}
}
public static function fetchByArea(Connection $conn, Area $area)
{
$data = $conn->fetchAssoc('SELECT * FROM `' . CourseTables::COURSE_PROGRESS_TBL . '` WHERE `areaId` = :areaId', [':areaId' => $area->getId()]);
if (false === $data) {
return false;
}
$item = new CourseProgress($area);
$item->mandatoryCourseNum = $data['mandatoryCourseNum'];
$item->passedCourseNum = $data['passedCourseNum'];
$item->failedCourseNum = $data['failedCourseNum'];
return $item;
}
public static function fetchResult(Connection $conn, Area $area, Course $course)
{
$data = $conn->fetchAssoc('SELECT r.* ' . 'FROM `' . CourseTables::COURSE_RESULT_TBL . '` r ' . 'INNER JOIN `' . CourseTables::COURSE_AREA_RESULT_TBL . '` a ON a.`courseId` = r.`courseId` AND a.`userId` = r.`userId` ' . 'WHERE a.`areaId` = :areaId AND a.`courseId` = :courseId', array(':areaId' => $area->getId(), ':courseId' => $course->getId()));
$result = new AreaCourseResult();
$result->area = $area;
$result->course = $course;
$result->result = Question::RESULT_UNKNOWN;
if (false === $data) {
return $result;
}
DataMappers::fromArray($result, $data);
return $result;
}
private function createResultForUserAndArea(User $user, Area $area, $result, $questionNum, $passedQuestions)
{
self::$conn->insert(CourseTables::COURSE_RESULT_TBL, ['userId' => $user->getId(), 'courseId' => $this->course->getId(), 'trialNumber' => '1', 'result' => $result, 'startedAt' => time() - 100, 'completedAt' => time() - 50, 'totalQuestions' => $questionNum, 'passedQuestions' => $passedQuestions]);
self::$conn->insert(CourseTables::COURSE_AREA_RESULT_TBL, ['areaId' => $area->getId(), 'userId' => $user->getId(), 'courseId' => $this->course->getId()]);
}
public function findMembers(Area $area)
{
$items = $this->conn->fetchAll('SELECT u.name, u.avatar, p.location, p.telephone, p.publicMail, p.privShowTelephone, p.privShowPublicMail, m.note ' . 'FROM `' . CoreTables::USER_TBL . '` u ' . 'INNER JOIN `' . CoreTables::USER_PROFILE_TBL . '` p ON p.`userId` = u.`id` ' . 'INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` m ON m.`userId` = u.`id` ' . 'WHERE m.`areaId` = :areaId ORDER BY m.`role` DESC, u.`name`', [':areaId' => $area->getId()]);
foreach ($items as &$item) {
$item['publicMail'] = User::evaluateUserPrivacy($item['privShowPublicMail'], $this->project) ? $item['publicMail'] : '';
$item['telephone'] = User::evaluateUserPrivacy($item['privShowTelephone'], $this->project) ? $item['telephone'] : '';
}
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;
}
private function expectCourseProgress(Area $area, $mandatory, $passed, $failed)
{
$this->assertFieldEqualsEx(CourseTables::COURSE_PROGRESS_TBL, 'areaId', $area->getId(), 'mandatoryCourseNum', $mandatory);
$this->assertFieldEqualsEx(CourseTables::COURSE_PROGRESS_TBL, 'areaId', $area->getId(), 'passedCourseNum', $passed);
$this->assertFieldEqualsEx(CourseTables::COURSE_PROGRESS_TBL, 'areaId', $area->getId(), 'failedCourseNum', $failed);
}
public function countParticipantsOnRoutes(Area $area)
{
return $this->conn->fetchAll('SELECT s.`participantNum` + s.`externalParticipantNum` AS `participantNum`, r.`name` ' . 'FROM `' . EdkTables::REGISTRATION_SETTINGS_TBL . '` s ' . 'INNER JOIN `' . EdkTables::ROUTE_TBL . '` r ON r.id = s.routeId ' . 'WHERE r.areaId = :rootId ' . 'ORDER BY (s.`participantNum` + s.`externalParticipantNum`)', [':rootId' => $area->getId()]);
}
/**
* Fetches the participant by his/her ID and area.
*
* @param Connection $conn
* @param int $id
* @param Area $area
* @param boolean $forUpdate Whether to lock the registration settings for writing
* @return EdkParticipant
*/
public static function fetchByArea(Connection $conn, $id, Area $area)
{
$data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::PARTICIPANT_TBL . '` WHERE `id` = :id AND `areaId` = :areaId', [':id' => $id, ':areaId' => $area->getId()]);
if (false === $data) {
return false;
}
$route = EdkRoute::fetchByRoot($conn, $data['routeId'], $area);
if (empty($route)) {
return false;
}
$registrationSettings = EdkRegistrationSettings::fetchByRoute($conn, $route);
if (empty($registrationSettings)) {
return false;
}
$item = EdkParticipant::fromArray($data);
$item->setRegistrationSettings($registrationSettings);
return $item;
}
