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 function execute()
{
$this->area->setName($this->name);
$this->area->setTerritory($this->territory);
$this->area->setPercentCompleteness($this->percentCompleteness);
$this->area->setCustomData($this->customData);
$this->repository->update($this->area);
}
public function render(CantigaController $controller, Request $request, Area $area)
{
$data = $area->getCustomData();
if (!empty($data['positionLat']) && !empty($data['positionLng'])) {
return $this->templating->render('WioEdkBundle:Extension:area-information-map.html.twig', ['positionLat' => $data['positionLat'], 'positionLng' => $data['positionLng']]);
}
return '';
}
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;
}
private function canApproveProfile(Area $area)
{
$data = $area->getCustomData();
if (!empty($data['positionLat']) && !empty($data['positionLng']) && !empty($data['ewcDate']) && !empty($data['ewcDate']['year'])) {
if ($area->getPercentCompleteness() > self::REQUIRED_PROFILE_COMPLETENESS) {
return true;
}
}
return false;
}
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 translateColor(Area $area)
{
switch ($area->getStatus()->getLabel()) {
case 'success':
return 'green';
case 'warning':
return 'orange';
case 'danger':
return 'red';
case 'primary':
return 'blue';
default:
return 'grey';
}
}
public static function fetchByRoot(Connection $conn, $id, MembershipEntityInterface $root)
{
if ($root instanceof Area) {
$data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `id` = :id AND `areaId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Group) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`groupId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Project) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`projectId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
}
if (false === $data) {
return false;
}
$item = self::fromArray($data);
if ($root instanceof Area) {
$item->area = $root;
} elseif ($root instanceof Group) {
$item->area = Area::fetchByGroup($conn, $data['areaId'], $root);
} elseif ($root instanceof Project) {
$item->area = Area::fetchByProject($conn, $data['areaId'], $root);
}
if (!empty($data['responderId'])) {
$item->responder = User::fetchByCriteria($conn, QueryClause::clause('u.id = :id', ':id', $data['responderId']));
}
return $item;
}
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));
}
/**
* Fetches an area by its ID and the project.
*
* @param int $id
* @param Project $project
*/
public function getItem($id, Project $project)
{
$item = Area::fetchByProject($this->conn, $id, $project);
if (false === $item) {
throw new ItemNotFoundException('AreaNotFound', $id);
}
return $item;
}
public function findMembership($slug, User $user)
{
$membership = Area::fetchMembership($this->conn, $this->resolver, $slug, $user->getId());
if (false === $membership) {
throw new ItemNotFoundException('The specified project is not available.', $slug);
}
$user->addRole('ROLE_AREA_AWARE');
return $membership;
}
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 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 getItem($id, Group $group)
{
$this->transaction->requestTransaction();
try {
$item = Area::fetchByGroup($this->conn, $id, $group);
if (false === $item) {
$this->transaction->requestRollback();
throw new ItemNotFoundException('The specified item has not been found.', $id);
}
return $item;
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
/**
* @return Area
*/
public function getArea($id)
{
$this->transaction->requestTransaction();
try {
$item = Area::fetchByProject($this->conn, $id, $this->project);
if (false === $item || $item->getStatus()->getId() != $this->publishedStatusId || $item->getProject()->getArchived()) {
$this->transaction->requestRollback();
throw new ItemNotFoundException('The specified area has not been found.', $id);
}
return $item;
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
public static function fetchApproved(Connection $conn, $id)
{
$data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::ROUTE_TBL . '` WHERE `id` = :id AND `approved` = 1', [':id' => $id]);
if (false === $data) {
return false;
}
$item = self::fromArray($data);
$item->area = Area::fetchActive($conn, $data['areaId']);
return $item;
}
public function remove(Area $item)
{
$this->transaction->requestTransaction();
try {
$item->remove($this->conn);
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
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;
}
/**
* @Route("/insert", name="project_area_insert")
*/
public function insertAction(Request $request)
{
$territoryRepo = $this->get('cantiga.core.repo.project_territory');
$statusRepo = $this->get('cantiga.core.repo.project_area_status');
$groupRepo = $this->get('cantiga.core.repo.project_group');
$territoryRepo->setProject($this->getActiveProject());
$statusRepo->setProject($this->getActiveProject());
$groupRepo->setProject($this->getActiveProject());
$formModel = $this->extensionPointFromSettings(CoreExtensions::AREA_FORM, CoreSettings::AREA_FORM);
$item = new Area();
$item->setReporter($this->getUser());
$item->setProject($this->getActiveProject());
$action = new InsertAction($this->crudInfo, $item, ProjectAreaForm::class, ['customFormModel' => $formModel, 'territoryRepository' => $territoryRepo, 'groupRepository' => $groupRepo, 'statusRepository' => $statusRepo]);
$action->slug($this->getSlug());
$action->customForm($formModel);
return $action->run($this, $request);
}
public static function fetchByGroup(Connection $conn, $id, Group $group)
{
$data = $conn->fetchAssoc('SELECT a.*, ' . 't.`id` AS `territory_id`, t.`name` AS `territory_name`, t.`areaNum` AS `territory_areaNum`, t.`requestNum` as `territory_requestNum`, ' . self::createEntityFieldList() . 'FROM `' . CoreTables::AREA_TBL . '` a ' . self::createEntityJoin('a') . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`id` = :id AND a.`groupId` = :groupId', [':id' => $id, ':groupId' => $group->getId()]);
if (false === $data) {
return false;
}
$item = Area::fromArray($data);
$item->group = $group;
$item->project = Project::fetchActive($conn, $data['projectId']);
if (false == $item->project) {
return false;
}
$item->status = $item->oldStatus = AreaStatus::fetchByProject($conn, $data['statusId'], $item->project);
$item->setTerritory($item->oldTerritory = Territory::fromArray($data, 'territory'));
$item->entity = Entity::fromArray($data, 'entity');
return $item;
}
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()]);
}
private function spawnActivationEvent(Area $area, $output)
{
if ($output instanceof CourseProgress) {
$this->eventDispatcher->dispatch(MilestoneEvents::ACTIVATION_EVENT, new ActivationEvent($area->getProject(), $area->getEntity(), 'course.completed', function () use($output) {
if ($output->getMandatoryCourseNum() == 0) {
return NewMilestoneStatus::create(100);
} else {
return NewMilestoneStatus::create((int) ($output->getPassedCourseNum() / $output->getMandatoryCourseNum() * 100));
}
}));
}
}
public function update(Area $item)
{
$this->transaction->requestTransaction();
try {
$item->update($this->conn);
$this->eventDispatcher->dispatch(CantigaEvents::AREA_UPDATED, new AreaEvent($item));
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
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 approve(Connection $conn, MembershipRoleResolver $resolver)
{
$this->status = $conn->fetchColumn('SELECT `status` FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `id` = :id', [':id' => $this->id]);
if ($this->status == self::STATUS_VERIFICATION) {
$this->lastUpdatedAt = time();
$this->status = self::STATUS_APPROVED;
$conn->update(CoreTables::AREA_REQUEST_TBL, ['lastUpdatedAt' => $this->lastUpdatedAt, 'status' => $this->status], ['id' => $this->getId()]);
$area = new Area();
$area->setName($this->name);
$area->setProject($this->project);
$area->setTerritory($this->territory);
$area->setReporter($this->requestor);
// oops, naming inconsistency
$id = $area->insert($conn);
$conn->update(CoreTables::AREA_REQUEST_TBL, ['areaId' => $id], ['id' => $this->getId()]);
$area->joinMember($conn, $this->requestor, $resolver->getHighestRole('Area'), '');
return $area;
}
return false;
}
public function acceptInvitation(Invitation $invitation)
{
$role = $this->getRole($invitation->getRole());
$note = $invitation->getNote();
$item = Area::fetchActive($this->conn, $invitation->getResourceId());
$item->joinMember($this->conn, $invitation->getUser(), $role, $note);
}
/**
* 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;
}
