/** * @param Connection $conn * @param string $place * @param string $locale * @param Project $project * @return AppText */ public static function fetchByLocation(Connection $conn, $place, $locale, Project $project = null) { if (null === $project) { $data = $conn->fetchAssoc('SELECT * FROM `' . CoreTables::APP_TEXT_TBL . '` WHERE `place` = :place AND `locale` = :locale AND `projectId` IS NULL', [':place' => $place, ':locale' => $locale]); } else { $items = $conn->fetchAll('SELECT * FROM `' . CoreTables::APP_TEXT_TBL . '` ' . 'WHERE `place` = :place AND `locale` = :locale AND (`projectId` = :projectId OR `projectId` IS NULL)', [':place' => $place, ':locale' => $locale, ':projectId' => $project->getId()]); $data = false; foreach ($items as $item) { if ($item['projectId'] == $project->getId()) { $data = $item; break; } } if (false === $data) { foreach ($items as $item) { if (empty($item['projectId'])) { $data = $item; break; } } } } if (false === $data) { return false; } $item = self::fromArray($data); if (null !== $project) { $item->setProject($project); } return $item; }
public static function fromProject(Project $project) { $root = new ForumRoot(); $root->id = $project->getId(); $root->name = $project->getName(); return $root; }
public function listData(DataTable $dataTable) { $qb = QueryBuilder::select()->field('i.id', 'id')->field('i.name', 'name')->field('i.label', 'label')->field('i.isDefault', 'isDefault')->field('i.areaNum', 'areaNum')->from(CoreTables::AREA_STATUS_TBL, 'i'); $where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId()); $recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($where))->fetchCell($this->conn); $recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildFetchingCondition($where))->fetchCell($this->conn); $dataTable->processQuery($qb); return $dataTable->createAnswer($recordsTotal, $recordsFiltered, $qb->where($dataTable->buildFetchingCondition($where))->fetchAll($this->conn)); }
public function findMilestoneProgressForGroupsInProject(Project $project) { $totalMilestones = $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = \'Group\'', [':projectId' => $project->getId()]); $results = $this->conn->fetchAll('SELECT g.`id`, g.`name`, g.`entityId`, p.`completedNum` ' . 'FROM `' . CoreTables::GROUP_TBL . '` g ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_PROGRESS_TBL . '` p ON p.`entityId` = g.`entityId` ' . 'WHERE g.`projectId` = :projectId ' . 'ORDER BY p.`completedNum` DESC, g.`name`', [':projectId' => $project->getId()]); foreach ($results as &$result) { $this->processResult($totalMilestones, $result); } return $results; }
public static function fetchDefault(Connection $conn, Project $project) { $data = $conn->fetchAssoc('SELECT * FROM `' . CoreTables::AREA_STATUS_TBL . '` WHERE `isDefault` = 1 AND `projectId` = :projectId', [':projectId' => $project->getId()]); if (empty($data)) { return false; } $item = self::fromArray($data); $item->project = $project; return $item; }
public static function fetchByProject(Connection $conn, $id, Project $project) { $data = $conn->fetchAssoc('SELECT * FROM `' . CoreTables::GROUP_CATEGORY_TBL . '` WHERE `id` = :id AND `projectId` = :projectId', [':id' => $id, ':projectId' => $project->getId()]); if (empty($data)) { return false; } $item = self::fromArray($data); $item->project = $project; return $item; }
public static function fetchByActivator(Connection $conn, Project $project, $activator, Entity $entity) { $data = $conn->fetchAssoc('SELECT r.*, m.`id` AS `milestone_id`, m.`name` AS `milestone_name`, m.`description` AS `milestone_description`, m.`displayOrder` AS `milestone_displayOrder`, ' . 'm.`type` AS `milestone_type`, m.`entityType` AS `milestone_entityType`, m.`deadline` AS `milestone_deadline` ' . 'FROM `' . MilestoneTables::MILESTONE_RULE_TBL . '` r ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_TBL . '` m ON m.`id` = r.`milestoneId` ' . 'WHERE r.`activator` = :activator AND r.`projectId` = :projectId AND m.`entityType` = :entityType', [':activator' => $activator, ':projectId' => $project->getId(), ':entityType' => $entity->getType()]); if (empty($data)) { return false; } $item = self::fromArray($data); $item->project = $project; $item->milestone = Milestone::fromArray($data, 'milestone'); $item->milestone->setProject($project); return $item; }
public static function populateEntities(Connection $conn, Entity $newEntity, Project $project) { $conn->insert(MilestoneTables::MILESTONE_PROGRESS_TBL, ['entityId' => $newEntity->getId(), 'completedNum' => 0]); $milestones = $conn->fetchAll('SELECT `id` FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = :entityType', [':projectId' => $project->getId(), ':entityType' => $newEntity->getType()]); if (sizeof($milestones) > 0) { $stmt = $conn->prepare('INSERT INTO `' . MilestoneTables::MILESTONE_STATUS_TBL . '` (`entityId`, `milestoneId`, `progress`) VALUES(:entityId, :milestoneId, 0)'); foreach ($milestones as $milestone) { $stmt->bindValue(':entityId', $newEntity->getId()); $stmt->bindValue(':milestoneId', $milestone['id']); $stmt->execute(); } } }
public static function fetchPublished(Connection $conn, $id, Project $project) { $data = $conn->fetchAssoc('SELECT * FROM `' . CourseTables::COURSE_TBL . '` WHERE `id` = :id AND `projectId` = :projectId AND `isPublished` = 1', [':id' => $id, ':projectId' => $project->getId()]); if (empty($data)) { return false; } $item = self::fromArray($data); $item->project = $project; $test = $conn->fetchAssoc('SELECT * FROM `' . CourseTables::COURSE_TEST_TBL . '` WHERE `courseId` = :id', [':id' => $id]); if (false !== $test) { $item->test = new CourseTest($item, $test['testStructure']); } return $item; }
public function listData(DataTable $dataTable) { $qb = QueryBuilder::select()->field('i.id', 'id')->field('i.place', 'place')->field('i.title', 'title')->field('i.locale', 'locale')->from(CoreTables::APP_TEXT_TBL, 'i'); if (null === $this->project) { $where = QueryClause::clause('i.`projectId` IS NULL'); } else { $where = QueryClause::clause('i.`projectId` = :projectId', ':projectId', $this->project->getId()); } $qb->where($where); $recordsTotal = QueryBuilder::copyWithoutFields($qb)->field('COUNT(id)', 'cnt')->where($dataTable->buildCountingCondition($qb->getWhere()))->fetchCell($this->conn); $recordsFiltered = QueryBuilder::copyWithoutFields($qb)->field('COUNT(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)); }
public static function fetchByProject(Connection $conn, $id, Project $project) { $data = $conn->fetchAssoc('SELECT r.*, ' . ' s1.`id` AS `s1_id`, s1.`name` AS `s1_name`, s1.`label` AS `s1_label`, s1.`isDefault` AS `s1_isDefault`, s1.`areaNum` AS `s1_areaNum`, ' . ' s2.`id` AS `s2_id`, s2.`name` AS `s2_name`, s2.`label` AS `s2_label`, s2.`isDefault` AS `s2_isDefault`, s2.`areaNum` AS `s2_areaNum` ' . 'FROM `' . MilestoneTables::MILESTONE_STATUS_RULE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_STATUS_TBL . '` s1 ON r.`newStatusId` = s1.`id` ' . 'INNER JOIN `' . CoreTables::AREA_STATUS_TBL . '` s2 ON r.`prevStatusId` = s2.`id` ' . 'WHERE r.`id` = :id AND r.`projectId` = :projectId', [':id' => $id, ':projectId' => $project->getId()]); if (empty($data)) { return false; } $item = self::fromArray($data); $item->project = $project; $item->newStatus = AreaStatus::fromArray($data, 's1'); $item->prevStatus = AreaStatus::fromArray($data, 's2'); $item->newStatus->setProject($project); $item->prevStatus->setProject($project); return $item; }
public static function fetchByProject(Connection $conn, $id, Project $project) { $data = $conn->fetchAssoc('SELECT r.*, v.id AS `verifier_id`, v.`name` AS `verifier_name`, ' . 't.`id` AS `territory_id`, t.`name` AS `territory_name`, t.`areaNum` AS `territory_areaNum`, t.`requestNum` as `territory_requestNum` ' . 'FROM `' . CoreTables::AREA_REQUEST_TBL . '` r ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = r.`territoryId` ' . 'LEFT JOIN `' . CoreTables::USER_TBL . '` v ON v.`id` = r.`verifierId` ' . 'WHERE r.`id` = :id AND r.`projectId` = :projectId', [':id' => $id, ':projectId' => $project->getId()]); if (null === $data) { return false; } $user = User::fetchByCriteria($conn, QueryClause::clause('u.`id` = :id', ':id', $data['requestorId'])); $item = self::fromArray($data); $item->setProject($project); $item->setRequestor($user); if (!empty($data['verifier_id'])) { $item->verifier = new Verifier($data['verifier_id'], $data['verifier_name']); } $item->setTerritory($item->oldTerritory = Territory::fromArray($data, 'territory')); 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 the list of links to present on the dashboard. * * @param int $presentationPlace */ public function findLinks($presentationPlace) { if (null !== $this->project) { return $this->conn->fetchAll('SELECT `name`, `url` FROM `' . LinksTables::LINK_TBL . '` WHERE `presentedTo` = :place AND `projectId` = :projectId ORDER BY `listOrder`', [':place' => $presentationPlace, ':projectId' => $this->project->getId()]); } else { return $this->conn->fetchAll('SELECT `name`, `url` FROM `' . LinksTables::LINK_TBL . '` WHERE `presentedTo` = :place AND `projectId` IS NULL ORDER BY `listOrder`', [':place' => $presentationPlace]); } }
public function findMembership($slug, User $user) { $membership = Project::fetchMembership($this->conn, $this->resolver, $slug, $user->getId()); if (false === $membership) { throw new ItemNotFoundException('The specified project is not available.', $slug); } $user->addRole('ROLE_PROJECT_AWARE'); return $membership; }
public function setUp() { $this->project = Project::fetch(self::$conn, 1); $this->status = AreaStatus::fetchByProject(self::$conn, 1, $this->project); $this->territory = Territory::fetchByProject(self::$conn, 1, $this->project); $this->area = Area::newArea($this->project, $this->territory, $this->status, 'Area1'); $this->area->insert(self::$conn); $pp = new CourseProgress($this->area); $pp->insert(self::$conn); }
/** * @return Project */ public function getItem($id) { $this->transaction->requestTransaction(); $data = $this->conn->fetchAssoc('SELECT * FROM `' . CoreTables::PROJECT_TBL . '` WHERE `id` = :id', [':id' => $id]); if (null === $data) { $this->transaction->requestRollback(); throw new ItemNotFoundException('The specified item has not been found.', $id); } return Project::fromArray($data); }
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 static function fetchById(Connection $conn, $id) { $data = $conn->fetchAssoc('SELECT * FROM `' . ExportTables::DATA_EXPORT_TBL . '` WHERE `id` = :id', [':id' => $id]); if (false === $data) { return false; } $item = self::fromArray($data); $item->project = Project::fetch($conn, $data['projectId']); $item->areaStatus = AreaStatus::fetchByProject($conn, $data['areaStatusId'], $item->project); return $item; }
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 initialize(Request $request, AuthorizationCheckerInterface $authChecker) { $this->slug = $request->get('slug'); if (empty($this->slug)) { return $this->redirect($this->generateUrl('public_edk_error')); } $this->project = Project::fetchBySlug($this->get('database_connection'), $this->slug); if (false === $this->project) { return $this->redirect($this->generateUrl('public_edk_error')); } $projectSettings = $this->getProjectSettings(); $projectSettings->setProject($this->project); }
public function getFormChoices() { $this->transaction->requestTransaction(); $stmt = $this->conn->prepare('SELECT `id`, `name` FROM `' . CoreTables::GROUP_CATEGORY_TBL . '` WHERE `projectId` = :projectId ORDER BY `name`'); $stmt->bindValue(':projectId', $this->project->getId()); $stmt->execute(); $result = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $result[$row['name']] = $row['id']; } $stmt->closeCursor(); return $result; }
public function getFormChoices($entityType) { $this->transaction->requestTransaction(); $stmt = $this->conn->prepare('SELECT `id`, `name` FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = :entityType ORDER BY `displayOrder`'); $stmt->bindValue(':projectId', $this->project->getId()); $stmt->bindValue(':entityType', $entityType); $stmt->execute(); $result = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $result[$row['name']] = $row['id']; } $stmt->closeCursor(); return $result; }
public function getRecentRequests($count) { $this->transaction->requestTransaction(); try { $items = $this->conn->fetchAll('SELECT r.`id`, r.`name`, u.`name` AS `username`, u.`avatar`, r.`status`, r.`createdAt` ' . 'FROM `' . CoreTables::AREA_REQUEST_TBL . '` r ' . 'INNER JOIN `' . CoreTables::USER_TBL . '` u ON u.`id` = r.`requestorId` ' . 'WHERE r.`projectId` = :projectId ' . 'ORDER BY r.`createdAt` DESC LIMIT ' . $count, [':projectId' => $this->project->getId()]); foreach ($items as &$item) { $item['statusText'] = AreaRequest::statusText($item['status']); $item['statusLabel'] = AreaRequest::statusLabel($item['status']); } return $items; } catch (Exception $ex) { $this->transaction->requestRollback(); throw $ex; } }
public function setUp() { $this->project = Project::fetch(self::$conn, 1); $this->status = AreaStatus::fetchByProject(self::$conn, 1, $this->project); $this->territory = Territory::fetchByProject(self::$conn, 1, $this->project); $this->area = Area::newArea($this->project, $this->territory, $this->status, 'Area1'); $this->area->insert(self::$conn); $this->area2 = Area::newArea($this->project, $this->territory, $this->status, 'Area2'); $lang = new Language(); $lang->setId(1); $this->user = User::newUser('login', 'Some user', $lang); $this->user->insert(self::$conn); $this->user2 = User::newUser('login2', 'Another user', $lang); $this->user2->insert(self::$conn); $tpa1 = new CourseProgress($this->area); $tpa1->insert(self::$conn); }
public function setUp() { $this->project = Project::fetch(self::$conn, 1); $this->status = AreaStatus::fetchByProject(self::$conn, 1, $this->project); $this->territory = Territory::fetchByProject(self::$conn, 1, $this->project); $this->area = Area::newArea($this->project, $this->territory, $this->status, 'Area1'); $this->area->insert(self::$conn); $this->course = new Course(); $this->course->setProject($this->project); $this->course->setName('Foo'); $this->course->setAuthorName('Foo'); $this->course->setAuthorEmail('*****@*****.**'); $this->course->setPresentationLink('http://www.example.com/'); $this->course->setIsPublished(true); $this->course->insert(self::$conn); $lang = new Language(); $lang->setId(1); $this->user = User::newUser('login', 'Some user', $lang); $this->user->insert(self::$conn); $this->anotherUser = User::newUser('login2', 'Another user', $lang); $this->anotherUser->insert(self::$conn); $pp = new CourseProgress($this->area); $pp->insert(self::$conn); }
public function updateStatisticsForAllDays() { $projects = Project::fetchActiveIds($this->conn); foreach ($projects as $projectId) { $participants = $this->conn->fetchAll('SELECT p.`peopleNum`, p.`createdAt`, a.`id` AS `areaId` ' . 'FROM `' . EdkTables::PARTICIPANT_TBL . '` p ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.id = p.areaId ' . 'WHERE a.projectId = :projectId ORDER BY p.`createdAt`', [':projectId' => $projectId]); $total = 0; $lastDay = null; $first = true; $areas = []; foreach ($participants as $participant) { $when = getdate($participant['createdAt']); $currentDay = $when['year'] . '-' . $when['mon'] . '-' . $when['mday']; if (!$first && $lastDay != $currentDay) { $this->updateStatsFor($projectId, $lastDay, $total); $this->updateAreaStatsFor($projectId, $lastDay, $areas); $lastDay = $currentDay; } if (!isset($areas[$participant['areaId']])) { $areas[$participant['areaId']] = 0; } $total += $participant['peopleNum']; $areas[$participant['areaId']] += $participant['peopleNum']; if ($first) { $first = false; $lastDay = $currentDay; } } } }
public function getHelpPages(RouterInterface $router) { return [['route' => 'user_help_introduction', 'url' => $router->generate('user_help_introduction'), 'title' => 'Introduction to the system'], ['route' => 'project_help_introduction', 'url' => $router->generate('project_help_introduction', ['slug' => $this->project->getSlug()]), 'title' => 'Introduction to projects'], ['route' => 'project_help_members', 'url' => $router->generate('project_help_members', ['slug' => $this->project->getSlug()]), 'title' => 'Member management']]; }
public function findMembers(Project $project) { $this->transaction->requestTransaction(); return $project->findMembers($this->conn, $this->roleResolver); }
public function fetchProjectSummary(Project $project) { return ['areaRequestNum' => $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `projectId` = :projectId', [':projectId' => $project->getId()]), 'areaNum' => $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . CoreTables::AREA_TBL . '` WHERE `projectId` = :projectId', [':projectId' => $project->getId()]), 'groupNum' => $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . CoreTables::GROUP_TBL . '` WHERE `projectId` = :projectId', [':projectId' => $project->getId()])]; }