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;
}
/**
* 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];
}
public function removeMember(MembershipEntityInterface $item, User $user)
{
$this->transaction->requestTransaction();
try {
if ($item->removeMember($this->conn, $user)) {
return ['status' => 1, 'data' => $item->findMembers($this->conn, $this->roleResolver)];
}
return ['status' => 0, 'data' => $item->findMembers($this->conn, $this->roleResolver)];
} catch (Exception $exception) {
$this->transaction->requestRollback();
throw $exception;
}
}
public function findProjectForEntity(MembershipEntityInterface $entity)
{
return $entity->getProject();
}
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 isAllowedForArea(Entity $entity, MembershipEntityInterface $who, $editable = false)
{
if ($entity->getType() == 'Area') {
if (!$editable || $this->settings->get(MilestoneSettings::AREA_CAN_UPDATE_OWN_PROGRESS)->getValue()) {
return $who->getEntity()->getId() == $entity->getId();
}
}
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;
}
