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; }
/** * @param $membershipEntity Entity whose members we want to view * @return array */ public function findMembers(MembershipEntityInterface $membershipEntity) { $stmt = $this->conn->prepare('SELECT i.`id`, i.`name`, i.`avatar`, i.`lastVisit`, p.`location`, m.`role` AS `membershipRole`, m.`note` AS `membershipNote` ' . 'FROM `' . CoreTables::USER_TBL . '` i ' . 'INNER JOIN `' . CoreTables::USER_PROFILE_TBL . '` p ON p.`userId` = i.`id` ' . 'INNER JOIN `' . $this->membershipTable() . '` m ON m.`userId` = i.`id` ' . 'WHERE m.`' . $this->entityColumn() . '` = :entityId AND i.`active` = 1 AND i.`removed` = 0 ' . 'ORDER BY i.`name`'); $stmt->bindValue(':entityId', $membershipEntity->getId()); $stmt->execute(); $results = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $results[] = ['id' => $row['id'], 'name' => $row['name'], 'avatar' => $row['avatar'], 'lastVisit' => $row['lastVisit'], 'location' => $row['location'], 'membership' => new Membership($membershipEntity, $this->roleResolver->getRole($this->entityName(), $row['membershipRole']), $row['membershipNote'])]; } $stmt->closeCursor(); return $results; }
public function countWhereLearntGrouped(MembershipEntityInterface $root) { $rootQuery = ''; if ($root instanceof Project) { $rootQuery = 'a.projectId'; } elseif ($root instanceof Group) { $rootQuery = 'a.groupId'; } else { $rootQuery = 'a.id'; } $result = []; $vals = $this->conn->fetchAll('SELECT SUM(`peopleNum`) AS `participants`, `whereLearnt` AS `id` ' . 'FROM `' . EdkTables::PARTICIPANT_TBL . '` p ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.id = p.areaId ' . 'WHERE ' . $rootQuery . ' = :rootId ' . 'GROUP BY `whereLearnt`', [':rootId' => $root->getId()]); foreach ($vals as $val) { $result[$val['id']] = $val['participants']; } return $result; }
public function getOpenRegistrations(MembershipEntityInterface $root, $acceptedStatus) { if ($root instanceof Project) { $rootPart = 'a.`projectId` = :rootId'; } elseif ($root instanceof Group) { $rootPart = 'a.`groupId` = :rootId'; } elseif ($root instanceof Area) { $rootPart = 'a.`id` = :rootId'; } $stmt = $this->conn->prepare('SELECT r.`id` AS `routeId`, a.`id` AS `areaId`, t.`id` AS `territoryId`, r.`name` AS `routeName`, a.`name` AS `areaName`, t.`name` AS `territoryName`, s.`startTime`, s.`endTime`, s.`participantLimit`, s.`participantNum`, s.`allowLimitExceed`, s.`maxPeoplePerRecord`, s.`customQuestion`, r.`routeFrom`, r.`routeTo`, r.`routeLength`, r.`routeAscent`, r.`routeType` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = r.`areaId` ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'INNER JOIN `' . EdkTables::REGISTRATION_SETTINGS_TBL . '` s ON s.`routeId` = r.`id` ' . 'WHERE a.`statusId` = :statusId AND ' . $rootPart . ' AND r.`approved` = 1 AND s.`registrationType` = ' . EdkRegistrationSettings::TYPE_EDK_WEBSITE . ' ' . 'ORDER BY a.`name`, r.`name`'); $stmt->bindValue(':statusId', $acceptedStatus); $stmt->bindValue(':rootId', $root->getId()); $stmt->execute(); $results = array(); $now = time(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if ($now < $row['startTime'] || $row['endTime'] < $now) { continue; } if ($row['participantNum'] >= $row['participantLimit'] && !$row['allowLimitExceed']) { continue; } if (!isset($results[$row['territoryId']])) { $results[$row['territoryId']] = ['id' => $row['territoryId'], 'name' => $row['territoryName'], 'areas' => []]; } if (!isset($results[$row['territoryId']]['areas'][$row['areaId']])) { $results[$row['territoryId']]['areas'][$row['areaId']] = ['id' => $row['areaId'], 'name' => $row['areaName'], 'routes' => []]; } if (!isset($results[$row['territoryId']]['areas'][$row['areaId']]['routes'][$row['routeId']])) { $results[$row['territoryId']]['areas'][$row['areaId']]['routes'][$row['routeId']] = ['id' => $row['routeId'], 'name' => $row['routeName'], 'from' => $row['routeFrom'], 'to' => $row['routeTo'], 'length' => $row['routeLength'], 'ascent' => $row['routeAscent'], 'q' => $row['customQuestion'], 'pn' => $row['participantNum'], 'pl' => $row['participantLimit'], 'ppr' => $row['maxPeoplePerRecord'], 't' => $row['routeType']]; } } $stmt->closeCursor(); usort($results, function ($a, $b) { return strcmp($a['name'], $b['name']); }); return $results; }
private function isAllowedForGroup(Entity $entity, MembershipEntityInterface $who, $editable = false) { switch ($entity->getType()) { case 'Project': return false; case 'Group': if (!$editable || $this->settings->get(MilestoneSettings::GROUP_CAN_UPDATE_OWN_PROGRESS)->getValue()) { return $who->getEntity()->getId() == $entity->getId(); } return false; case 'Area': if (!$editable || $this->settings->get(MilestoneSettings::GROUP_CAN_UPDATE_AREA_PROGRESS)->getValue()) { $pid = $this->conn->fetchColumn('SELECT `groupId` FROM `' . CoreTables::AREA_TBL . '` WHERE `entityId` = :id', [':id' => $entity->getId()]); return $pid == $who->getId(); } return false; } return false; }
public static function fetchByRoot(Connection $conn, $id, MembershipEntityInterface $root) { if ($root instanceof Area) { $data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::ROUTE_TBL . '` WHERE `areaId` = :areaId AND `id` = :id', [':id' => $id, ':areaId' => $root->getId()]); } elseif ($root instanceof Group) { $data = $conn->fetchAssoc('SELECT r.* ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = r.`areaId` ' . 'WHERE a.`groupId` = :groupId AND r.`id` = :id', [':id' => $id, ':groupId' => $root->getId()]); } elseif ($root instanceof Project) { $data = $conn->fetchAssoc('SELECT r.* ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = r.`areaId` ' . 'WHERE a.`projectId` = :projectId AND r.`id` = :id', [':id' => $id, ':projectId' => $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); } $notes = $conn->fetchAll('SELECT * FROM `' . EdkTables::ROUTE_NOTE_TBL . '` WHERE `routeId` = :routeId', [':routeId' => $item->getId()]); foreach ($notes as $note) { $item->notes[$note['noteType']] = $note['content']; } return $item; }
/** * Shows all the areas and the completeness of all the milestones in a simple grid. * * @param MembershipEntityInterface $parent */ public function findTotalAreaCompleteness(MembershipEntityInterface $parent) { $whereExtras = ''; if ($parent instanceof Project) { $whereExtras = ' AND a.`projectId` = :itemId '; } else { $whereExtras = ' AND a.`groupId` = :itemId '; } $stmt = $this->conn->prepare('SELECT a.`id`, a.`entityId`, a.`name`, t.`name` AS `statusName`, t.`label` AS `statusLabel`, m.`id` AS `milestoneId`, m.`name` AS `milestoneName`, s.`progress` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::AREA_STATUS_TBL . '` t ON t.`id` = a.`statusId` ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_STATUS_TBL . '` s ON s.`entityId` = a.`entityId` ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_TBL . '` m ON m.`id` = s.`milestoneId` ' . 'WHERE m.`entityType` = \'Area\' ' . $whereExtras . 'ORDER BY a.`name`, m.`id` '); $stmt->bindValue(':itemId', $parent->getId()); $stmt->execute(); $milestones = array(); $results = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if (!isset($results[$row['id']])) { $results[$row['id']] = ['id' => $row['id'], 'entityId' => $row['entityId'], 'name' => $row['name'], 'status' => $row['statusName'], 'label' => $row['statusLabel'], 'milestones' => [0 => $row['progress']]]; } else { $results[$row['id']]['milestones'][] = $row['progress']; } $milestones[$row['milestoneId']] = $row['milestoneName']; } $stmt->closeCursor(); return [$milestones, $results]; }