public function findMilestoneProgressForAreasInGroup(Group $group)
{
$totalMilestones = $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = \'Area\'', [':projectId' => $group->getProject()->getId()]);
$results = $this->conn->fetchAll('SELECT a.`id`, a.`name`, a.`entityId`, p.`completedNum` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_PROGRESS_TBL . '` p ON p.`entityId` = a.`entityId` ' . 'WHERE a.`groupId` = :groupId ' . 'ORDER BY p.`completedNum` DESC, a.`name`', [':groupId' => $group->getId()]);
foreach ($results as &$result) {
$this->processResult($totalMilestones, $result);
}
return $results;
}
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 a group by its ID and the project.
*
* @param int $id
* @param Project $project
*/
public function getItem($id, Project $project)
{
$item = Group::fetchByProject($this->conn, $id, $project);
if (false === $item) {
throw new ItemNotFoundException('GroupNotFound', $id);
}
return $item;
}
public function findMembership($slug, User $user)
{
$membership = Group::fetchMembership($this->conn, $this->resolver, $slug, $user->getId());
if (false === $membership) {
throw new ItemNotFoundException('The specified project is not available.', $slug);
}
$user->addRole('ROLE_GROUP_AWARE');
return $membership;
}
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 function listData(DataTable $dataTable)
{
$qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('r.mandatoryCourseNum', 'mandatoryCourseNum')->field('r.passedCourseNum', 'passedCourseNum')->field('r.failedCourseNum', 'failedCourseNum')->from(CoreTables::AREA_TBL, 'i')->join(CourseTables::COURSE_PROGRESS_TBL, 'r', QueryClause::clause('r.areaId = i.id'))->leftJoin(CoreTables::GROUP_TBL, 'g', QueryClause::clause('g.id = i.groupId'));
if (null !== $this->group) {
$qb->where(QueryClause::clause('i.`groupId` = :groupId', ':groupId', $this->group->getId()));
} else {
$qb->where(QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId()));
}
$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);
$qb->postprocess(function ($row) {
if ($row['mandatoryCourseNum'] == 0) {
$row['progress'] = 0;
} else {
$row['progress'] = $row['passedCourseNum'] / $row['mandatoryCourseNum'] * 100;
}
return $row;
});
$dataTable->processQuery($qb);
return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($qb->getWhere()))->fetchAll($this->conn));
}
public function getFormChoices()
{
$this->transaction->requestTransaction();
$stmt = $this->conn->prepare('SELECT `id`, `name` FROM `' . CoreTables::AREA_TBL . '` WHERE `groupId` = :groupId ORDER BY `name`');
$stmt->bindValue(':groupId', $this->group->getId());
$stmt->execute();
$result = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row['name']] = $row['id'];
}
$stmt->closeCursor();
return $result;
}
public static function fetch(Connection $conn, $id)
{
$data = $conn->fetchAssoc('SELECT g.*, ' . self::createEntityFieldList() . 'FROM `' . CoreTables::GROUP_TBL . '` g ' . self::createEntityJoin('g') . 'WHERE g.`id` = :id', [':id' => $id]);
if (null === $data) {
return false;
}
$item = Group::fromArray($data);
$item->project = Project::fetchActive($conn, $data['projectId']);
if (false === $item->project) {
return false;
}
if (!empty($data['categoryId'])) {
$item->category = GroupCategory::fetchByProject($conn, $data['categoryId'], $item->project);
}
$item->entity = Entity::fromArray($data, 'entity');
return $item;
}
public function acceptInvitation(Invitation $invitation)
{
$role = $this->getRole($invitation->getRole());
$note = $invitation->getNote();
$item = Group::fetch($this->conn, $invitation->getResourceId());
$item->joinMember($this->conn, $invitation->getUser(), $role, $note);
}
/**
* @param Connection $conn
* @param int $projectId
* @param int $userId
* @return Membership
*/
public static function fetchMembership(Connection $conn, MembershipRoleResolver $resolver, $slug, $userId)
{
$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`, ' . 'm.`role` AS `membership_role`, m.`note` AS `membership_note`, ' . self::createEntityFieldList() . 'FROM `' . CoreTables::AREA_TBL . '` a ' . self::createEntityJoin('a') . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` m ON m.`areaId` = a.`id` WHERE m.`userId` = :userId AND a.`slug` = :slug', [':userId' => $userId, ':slug' => $slug]);
if (false === $data) {
return false;
}
$item = self::fromArray($data);
$item->project = Project::fetchActive($conn, $data['projectId']);
if (false == $item->project) {
return false;
}
$item->status = $item->oldStatus = AreaStatus::fetchByProject($conn, $data['statusId'], $item->project);
if (!empty($data['groupId'])) {
$item->group = $item->oldGroup = Group::fetchByProject($conn, $data['groupId'], $item->project);
}
$item->setTerritory($item->oldTerritory = Territory::fromArray($data, 'territory'));
$role = $resolver->getRole('Area', $data['membership_role']);
$item->entity = Entity::fromArray($data, 'entity');
return new Membership($item, $role, $data['membership_note']);
}
/**
* @Route("/insert", name="project_area_group_insert")
*/
public function insertAction(Request $request)
{
$item = new Group();
$item->setProject($this->getActiveProject());
$action = new InsertAction($this->crudInfo, $item, ProjectGroupForm::class, ['categoryRepository' => $this->getCategoryRepo()]);
$action->slug($this->getSlug());
return $action->run($this, $request);
}
public function remove(Group $item)
{
$this->transaction->requestTransaction();
try {
$item->remove($this->conn);
$this->eventDispatcher->dispatch(CantigaEvents::GROUP_REMOVED, new GroupEvent($item));
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}