public function getPublicResultSet($userId, array $fields, $startIndex = 0, $count = 16, $sortBy = null, $sortOrder = null, Condition $con = null, $mode = 0, $class = null, array $args = array()) { $startIndex = $startIndex !== null ? (int) $startIndex : 0; $count = !empty($count) ? (int) $count : 16; $sortBy = $sortBy !== null ? $sortBy : 'date'; $sortOrder = $sortOrder !== null ? (int) $sortOrder : Sql::SORT_DESC; $select = $this->hm->getTable('AmunService\\User\\Activity\\Receiver')->select(array('id', 'status', 'activityId', 'userId', 'date'), 'receiver')->join(Join::INNER, $this->hm->getTable('AmunService\\User\\Activity')->select(array('id', 'globalId', 'parentId', 'userId', 'refId', 'table', 'status', 'scope', 'verb', 'summary', 'date'))->join(Join::INNER, $this->hm->getTable('AmunService\\User\\Account')->select(array('globalId', 'name', 'profileUrl', 'thumbnailUrl'), 'author')))->where('receiverUserId', '=', $userId)->where('receiverStatus', '=', Receiver\Record::VISIBLE)->where('parentId', '=', 0)->where('scope', '=', 0)->where('userId', '=', $userId)->orderBy($sortBy, $sortOrder)->limit($startIndex, $count); if (!empty($fields)) { $select->setSelectedColumns($fields); } if ($con !== null && $con->hasCondition()) { $values = $con->toArray(); foreach ($values as $row) { $select->where($row[0], $row[1], $row[2]); } } if ($mode == Sql::FETCH_OBJECT && $class === null) { $class = $this->getClassName(); } if ($mode == Sql::FETCH_OBJECT && empty($args)) { $args = $this->getClassArgs(); } $totalResults = $select->getTotalResults(); $entries = $select->getAll($mode, $class, $args); $resultSet = new ResultSet($totalResults, $startIndex, $count, $entries); return $resultSet; }
public function onLoad() { parent::onLoad(); // friend request count $con = new Condition(); $con->add('friendId', '=', $this->user->getId()); $con->add('status', '=', Friend\Record::REQUEST); $requestCount = $this->getSql()->count($this->registry['table.user_friend'], $con); $this->template->assign('requestCount', $requestCount); // pending count $con = new Condition(); $con->add('userId', '=', $this->user->getId()); $con->add('status', '=', Friend\Record::REQUEST); $pendingCount = $this->getSql()->count($this->registry['table.user_friend'], $con); $this->template->assign('pendingCount', $pendingCount); // load groups $groupList = $this->getGroups(); $this->template->assign('groupList', $groupList); // options $friends = new Option('friends', $this->registry, $this->user, $this->page); $friends->add('my_view', 'Friends', $this->page->getUrl() . '/friends'); if ($requestCount > 0) { $friends->add('my_view', 'Request (' . $requestCount . ')', $this->page->getUrl() . '/friends/request'); } if ($pendingCount > 0) { $friends->add('my_view', 'Pending (' . $pendingCount . ')', $this->page->getUrl() . '/friends/pending'); } $friends->add('my_view', 'Groups', $this->page->getUrl() . '/friends/group'); $friends->load(array($this->page)); $this->template->assign('optionsFriends', $friends); }
public function getTokensByApp($appId) { $now = new DateTime(); $con = new Condition(); $con->add('appId', '=', $appId); $con->add('status', '=', self::STATUS_ACTIVE); $con->add('expire', '>', $now->format('Y-m-d H:i:s')); return $this->getBy($con); }
public function getValue($name) { $condition = new Condition(); $condition->like('name', $name); $config = $this->configTable->getOneBy($condition); if (!empty($config)) { return $this->convertValueToType($config['value'], $config['type']); } else { return null; } }
public function create($name, $class, $config) { // check whether action exists $condition = new Condition(); $condition->equals('name', $name); $action = $this->actionTable->getOneBy($condition); if (!empty($action)) { throw new StatusCode\BadRequestException('Action already exists'); } // create action $this->actionTable->create(array('status' => Table\Action::STATUS_ACTIVE, 'name' => $name, 'class' => $class, 'config' => $config, 'date' => new \DateTime())); }
/** * Returns the GET response * * @param \PSX\Api\Version $version * @return array|\PSX\Data\RecordInterface */ protected function doGet(Version $version) { $startIndex = $this->getParameter('startIndex', Validate::TYPE_INTEGER) ?: 0; $search = $this->getParameter('search', Validate::TYPE_STRING) ?: null; $condition = new Condition(['status', '=', 1]); $condition->add('path', 'NOT LIKE', '/backend%'); $condition->add('path', 'NOT LIKE', '/doc%'); $condition->add('path', 'NOT LIKE', '/authorization%'); $condition->add('path', 'NOT LIKE', '/export%'); if (!empty($search)) { $condition->add('path', 'LIKE', '%' . $search . '%'); } $table = $this->tableManager->getTable('Fusio\\Impl\\Backend\\Table\\Routes'); $table->setRestrictedFields(['config']); return array('totalItems' => $table->getCount($condition), 'startIndex' => $startIndex, 'entry' => $table->getAll($startIndex, null, 'id', Sql::SORT_DESC, $condition)); }
public function delete(RecordInterface $record) { if ($record->hasFields('id')) { // move all friends to uncategorized $con = new Condition(); $con->add('userId', '=', $this->user->getId()); $con->add('groupId', '=', $record->id); $this->sql->update($this->registry['table.user_friend'], array('groupId' => 0), $con); $con = new Condition(array('id', '=', $record->id)); $this->table->delete($con); $this->notify(RecordAbstract::DELETE, $record); return $record; } else { throw new Exception('Missing field in record'); } }
/** * onLoad * * @param count integer */ public function onLoad() { parent::onLoad(); $count = $this->args->get('count', 8); $now = new DateTime('NOW', $this->registry['core.default_timezone']); $past = new DateTime('NOW', $this->registry['core.default_timezone']); $past->sub(new DateInterval('P' . $count . 'D')); $act = array(); // condition $con = new Condition(); $con->add('scope', '=', 0); $con->add('date', '>=', $past->format(DateTime::SQL)); // get activities $handler = $this->hm->getHandler('AmunService\\User\\Activity'); $result = $handler->getAll(array('id', 'scope', 'summary', 'date', 'authorId', 'authorName', 'authorThumbnailUrl'), 0, 64, 'date', Sql::SORT_ASC, $con); foreach ($result as $row) { $date = new DateTime($row['date'], $this->registry['core.default_timezone']); $interval = $date->diff($now); $key = $interval->format('%d'); if (!isset($act[$key])) { $act[$key] = 1; } else { $act[$key]++; } } // build params $chd = array(); $labels = array(); $max = 0; $days = 0; for ($i = $count - 1; $i >= 0; $i--) { if (isset($act[$i])) { if ($act[$i] > $max) { $max = $act[$i]; } $chd[$i] = $act[$i]; } else { $chd[$i] = 0; } $labels[] = date('d M', time() - $i * 3600 * 24); $days++; } $params = array('cht' => 'ls', 'chd' => 't:' . implode(',', $chd), 'chs' => '320x100', 'chco' => '0077CC', 'chds' => '0,' . $max, 'chxt' => 'x', 'chxl' => '0:|' . implode('|', $labels), 'chxr' => '0,1,' . $days . ',1'); $this->display($params); }
public function getRoutes($startIndex = 0, $search = null) { $condition = new Condition(); $condition->equals('status', self::STATUS_ACTIVE); $condition->notLike('path', '/backend%'); $condition->notLike('path', '/consumer%'); $condition->notLike('path', '/doc%'); $condition->notLike('path', '/authorization%'); $condition->notLike('path', '/export%'); if (!empty($search)) { $condition->like('path', '%' . $search . '%'); } $definition = ['totalResults' => $this->getCount($condition), 'startIndex' => $startIndex, 'itemsPerPage' => 16, 'entry' => $this->doCollection([$this, 'getAll'], [$startIndex, 16, null, Sql::SORT_DESC, $condition], ['id' => 'id', 'status' => 'status', 'path' => 'path', 'controller' => 'controller'])]; return $this->build($definition); }
/** * Returns the GET response * * @param \PSX\Api\Version $version * @return array|\PSX\Data\RecordInterface */ protected function doGet(Version $version) { $startIndex = $this->getParameter('startIndex', Validate::TYPE_INTEGER) ?: 0; $search = $this->getParameter('search', Validate::TYPE_STRING) ?: null; $routeId = $this->getParameter('routeId', Validate::TYPE_INTEGER) ?: null; $condition = new Condition(); if (!empty($search)) { $condition->like('name', '%' . $search . '%'); } if (!empty($routeId)) { $sql = 'SELECT actionId FROM fusio_routes_action WHERE routeId = ?'; $condition->raw('id IN (' . $sql . ')', [$routeId]); } $table = $this->tableManager->getTable('Fusio\\Impl\\Backend\\Table\\Action'); $table->setRestrictedFields(['class', 'config']); return array('totalItems' => $table->getCount($condition), 'startIndex' => $startIndex, 'entry' => $table->getAll($startIndex, null, 'id', Sql::SORT_DESC, $condition)); }
public function create($name, $description, array $routes = null) { // check whether scope exists $condition = new Condition(); $condition->equals('name', $name); $scope = $this->scopeTable->getOneBy($condition); if (!empty($scope)) { throw new StatusCode\BadRequestException('Scope already exists'); } try { $this->scopeTable->beginTransaction(); // create scope $this->scopeTable->create(array('name' => $name, 'description' => $description)); // insert routes $scopeId = $this->scopeTable->getLastInsertId(); $this->insertRoutes($scopeId, $routes); $this->scopeTable->commit(); } catch (\Exception $e) { $this->scopeTable->rollBack(); throw $e; } }
public function onGet() { try { $token = $this->get->token('string', array(new Filter\Length(40, 40), new Filter\Xdigit())); if ($token !== false) { $handler = $this->getHandler('AmunService\\User\\Account'); $account = $handler->getNotActivatedByToken($token); if ($account instanceof Account\Record) { try { $expire = 'PT24H'; // expire after 24 hours $now = new DateTime('NOW', $this->registry['core.default_timezone']); if ($now > $account->getDate()->add(new DateInterval($expire))) { throw new Exception('Activation is expired'); } if ($_SERVER['REMOTE_ADDR'] == $account->ip) { $account->setStatus(Account\Record::NORMAL); $handler->update($account); $this->template->assign('success', true); } else { throw new Exception('Registration was requested from another IP'); } } catch (\Exception $e) { $con = new Condition(); $con->add('id', '=', $account->id); $con->add('status', '=', Account\Record::NOT_ACTIVATED); $this->sql->delete($this->registry['table.user_account'], $con); throw $e; } } else { throw new Exception('Invalid token'); } } else { throw new Exception('Token not set'); } } catch (\Exception $e) { $this->template->assign('error', $e->getMessage()); } }
public function create($userId, $name, $url, array $scopes = null) { // validate data $this->assertName($name); $this->assertUrl($url); // check limit of apps which an user can create $condition = new Condition(); $condition->equals('userId', $userId); $condition->in('status', [Table\App::STATUS_ACTIVE, Table\App::STATUS_PENDING, Table\App::STATUS_DEACTIVATED]); if ($this->appTable->getCount($condition) > $this->appCount) { throw new StatusCode\BadRequestException('Maximal amount of apps reached. Please delete another app in order to register a new one'); } $scopes = $this->getValidUserScopes($userId, $scopes); if (empty($scopes)) { throw new StatusCode\BadRequestException('Provide at least one valid scope for the app'); } $this->appService->create($userId, $this->appApproval === false ? Table\App::STATUS_ACTIVE : Table\App::STATUS_PENDING, $name, $url, null, $scopes); }
/** * Merges an existing condition * * @param \PSX\Sql\Condition $condition * @return \PSX\Sql\Condition */ public function merge(Condition $condition) { $this->values = array_merge($this->values, $condition->toArray()); return $this; }
public function clear() { $con = new Condition(); $con->add('ip', '=', $_SERVER['REMOTE_ADDR']); $this->sql->delete($this->registry['table.login_attempt'], $con); }
private function getComments() { $con = new Condition(); $con->add('pageId', '=', $this->page->getId()); $con->add('refId', '=', $this->id); $url = new Url($this->base->getSelf()); $count = $url->getParam('count') > 0 ? $url->getParam('count') : 8; $count = $count > 16 ? 16 : $count; $result = $this->getHandler('AmunService\\Comment')->getResultSet(array(), $url->getParam('startIndex'), $count, $url->getParam('sortBy'), $url->getParam('sortOrder'), $con, Sql::FETCH_OBJECT); $paging = new Paging($url, $result); $this->template->assign('pagingComments', $paging, 0); return $result; }
public function testGetAllConditionOrConjunction() { $table = $this->getTable(); if (!$table instanceof TableQueryInterface) { $this->markTestSkipped('Table not an query interface'); } $con = new Condition(); $con->add('userId', '=', 1, 'OR'); $con->add('userId', '=', 3); $result = $table->getAll(0, 16, 'id', Sql::SORT_DESC, $con); $this->assertEquals(true, is_array($result)); $this->assertEquals(3, count($result)); $expect = array(new Record('comment', array('id' => 4, 'userId' => 3, 'title' => 'blub', 'date' => new \DateTime('2013-04-29 16:56:32'))), new Record('comment', array('id' => 2, 'userId' => 1, 'title' => 'bar', 'date' => new \DateTime('2013-04-29 16:56:32'))), new Record('comment', array('id' => 1, 'userId' => 1, 'title' => 'foo', 'date' => new \DateTime('2013-04-29 16:56:32')))); $this->assertEquals($expect, $result); }
private function insertEntry(Entry $entry) { // get global id $urn = new Urn($entry->id); $globalId = $urn->getNss(); // get author of the entry $author = current($entry->author); if (!empty($author)) { $urn = new Urn($author['uri']); $con = new Condition(); $con->add('globalId', '=', $urn->getNss()); $con->add('name', '=', $author['name']); $userId = $this->sql->select($this->registry['table.user_account'], array('id'), $con, Sql::SELECT_FIELD); $user = new User($userId, $this->registry); $handler = new Handler($user); } else { throw new Exception('No author set'); } // get threading extension $thread = $entry->getElement()->getElementsByTagNameNS('http://purl.org/syndication/thread/1.0', 'in-reply-to'); $refId = 0; if ($thread->length > 0) { // search for referenced activity globalId $ref = $thread->item(0)->getAttribute('ref'); $urn = new Urn($ref); $con = new Condition(array('globalId', '=', $urn->getNss())); $refId = $this->hm->getTable('AmunService\\User\\Activity')->getField('id', $con); if (empty($refId)) { throw new Exception('Invalid referenced id'); } } $activity = $this->hm->getTable('AmunService\\User\\Activity')->getRecord(); $activity->globalId = $globalId; $activity->parentId = $refId; $activity->table = 'amun_user_activity'; $activity->verb = 'add'; $activity->summary = $entry->content; $activity->date = $entry->updated->format(DateTime::SQL); $handler->create($activity); }
public function getCount(Condition $condition = null) { $builder = $this->connection->createQueryBuilder()->select($this->connection->getDatabasePlatform()->getCountExpression($this->getPrimaryKey()))->from($this->getName(), null); if ($condition !== null && $condition->hasCondition()) { $builder->where(substr($condition->getStatment(), 5)); $values = $condition->getValues(); foreach ($values as $key => $value) { $builder->setParameter($key, $value); } } return (int) $this->connection->fetchColumn($builder->getSQL(), $builder->getParameters()); }
public function create($name, $class, $config) { // check whether connection exists $condition = new Condition(); $condition->equals('name', $name); $connection = $this->connectionTable->getOneBy($condition); if (!empty($connection)) { throw new StatusCode\BadRequestException('Connection already exists'); } $this->testConnection($class, $config); // create connection $this->connectionTable->create(array('name' => $name, 'class' => $class, 'config' => self::encryptConfig($config, $this->secretKey))); }
public function remove($opEndpoint, $assocHandle) { $con = new Condition(); $con->add('opEndpoint', '=', $opEndpoint); $con->add('assocHandle', '=', $assocHandle); $this->sql->delete($con); }
public function createRemote($provider, $id, $name, $email, array $scopes = null) { // check whether user exists $condition = new Condition(); $condition->equals('provider', $provider); $condition->equals('remoteId', $id); $user = $this->userTable->getOneBy($condition); if (!empty($user)) { return $user->id; } // replace spaces with a dot $name = str_replace(' ', '.', $name); // check values $this->assertName($name); if (!empty($email)) { $this->assertEmail($email); } else { $email = null; } try { $this->userTable->beginTransaction(); // create user $this->userTable->create(array('provider' => $provider, 'status' => Table\User::STATUS_CONSUMER, 'remoteId' => $id, 'name' => $name, 'email' => $email, 'password' => null, 'date' => new DateTime())); $userId = $this->userTable->getLastInsertId(); // add scopes $this->insertScopes($userId, $scopes); $this->userTable->commit(); } catch (\Exception $e) { $this->userTable->rollBack(); throw $e; } return $userId; }
public function create($path, $config) { // check whether route exists $condition = new Condition(); $condition->equals('status', Table\Routes::STATUS_ACTIVE); $condition->equals('path', $path); $route = $this->routesTable->getOneBy($condition); if (!empty($route)) { throw new StatusCode\BadRequestException('Route already exists'); } try { $this->routesTable->beginTransaction(); // create route $this->routesTable->create(['status' => Table\Routes::STATUS_ACTIVE, 'methods' => 'GET|POST|PUT|DELETE', 'path' => $path, 'controller' => 'Fusio\\Impl\\Controller\\SchemaApiController']); // get last insert id $routeId = $this->routesTable->getLastInsertId(); $this->handleConfig($routeId, $path, $config); $this->routesTable->commit(); } catch (\Exception $e) { $this->routesTable->rollBack(); throw $e; } }
/** * If a user on an remote website accepts our friendship request the website * makes a call to the api/user/friend/relation inorder to inform us that * the relation was accepted. If the user exists we add a relation and set * the status * * @param RecordInterface $record * @return boolean */ protected function handleAccept(RecordInterface $record) { $sql = <<<SQL SELECT \t`account`.`id` AS `accountId`, \t`host`.`id` AS `hostId`, \t`host`.`name` AS `hostName`, \t`host`.`template` AS `hostTemplate` FROM \t{$this->registry['table.user_account']} `account` INNER JOIN \t{$this->registry['table.core_host']} `host` \tON `account`.`hostId` = `host`.`id` WHERE \t`account`.`name` = ? AND \t`host`.`name` = ? AND \t`account`.`status` = ? SQL; $row = $this->sql->getRow($sql, array($record->name, $record->host, Account\Record::REMOTE)); if (!empty($row)) { // create relation $date = new DateTime('NOW', $this->registry['core.default_timezone']); $this->table->insert(array('status' => Record::NORMAL, 'userId' => $row['accountId'], 'friendId' => $this->user->getId(), 'date' => $date->format(DateTime::SQL))); // update status $con = new Condition(); $con->add('userId', '=', $this->user->getId()); $con->add('friendId', '=', $row['accountId']); $this->table->update(array('status' => Record::NORMAL, 'date' => $date->format(DateTime::SQL)), $con); return true; } else { throw new Exception('Account does not exist'); } }
public function getCondition($alias = null) { $alias = $alias !== null ? $alias . '.' : ''; $condition = new Condition(); $condition->greaterThen($alias . 'date', $this->from->format('Y-m-d 00:00:00')); $condition->lowerThen($alias . 'date', $this->to->format('Y-m-d 23:59:59')); if (!empty($this->appId)) { $condition->equals($alias . 'appId', $this->appId); } if (!empty($this->routeId)) { $condition->equals($alias . 'routeId', $this->routeId); } if (!empty($this->ip)) { $condition->like($alias . 'ip', $this->ip); } if (!empty($this->userAgent)) { $condition->like($alias . 'userAgent', '%' . $this->userAgent . '%'); } if (!empty($this->method)) { $condition->equals($alias . 'method', $this->method); } if (!empty($this->path)) { $condition->like($alias . 'path', $this->path . '%'); } if (!empty($this->header)) { $condition->like($alias . 'header', '%' . $this->header . '%'); } if (!empty($this->body)) { $condition->like($alias . 'body', '%' . $this->body . '%'); } return $condition; }
public function create($name, $source) { if (!preg_match('/^[A-z0-9\\-\\_]{3,64}$/', $name)) { throw new StatusCode\BadRequestException('Invalid schema name'); } // check whether schema exists $condition = new Condition(); $condition->equals('name', $name); $connection = $this->schemaTable->getOneBy($condition); if (!empty($connection)) { throw new StatusCode\BadRequestException('Connection already exists'); } // create schema $this->schemaTable->create(array('status' => Table\Schema::STATUS_ACTIVE, 'name' => $name, 'source' => $source, 'cache' => $this->schemaParser->parse(json_encode($source)))); }
public static function getCondition(FilterParameter $parameter, $dateColumn = 'date') { $condition = new Condition(); if ($parameter->getFilterBy() && $parameter->getFilterValue()) { switch ($parameter->getFilterOp()) { case 'contains': $condition->add($parameter->getFilterBy(), 'LIKE', '%' . $parameter->getFilterValue() . '%'); break; case 'equals': $condition->add($parameter->getFilterBy(), '=', $parameter->getFilterValue()); break; case 'startsWith': $condition->add($parameter->getFilterBy(), 'LIKE', $parameter->getFilterValue() . '%'); break; case 'present': $condition->add($parameter->getFilterBy(), 'IS NOT', 'NULL', 'AND'); $condition->add($parameter->getFilterBy(), 'NOT LIKE', ''); break; } } if ($parameter->getUpdatedSince() instanceof \DateTime) { $condition->add($dateColumn, '>', $parameter->getUpdatedSince()->format(DateTime::SQL)); } return $condition; }
public function getMethod($routeId, $version, $method) { if ($version == '*' || empty($version)) { $version = $this->methodTable->getLatestVersion($routeId); } $condition = new Condition(); $condition->equals('routeId', $routeId); $condition->equals('method', $method); $condition->equals('version', $version); $condition->equals('active', Resource::STATUS_ACTIVE); return $this->methodTable->getOneBy($condition); }
public function hasFriend(Account\Record $account) { $con = new Condition(); $con->add('userId', '=', $this->id); $con->add('friendId', '=', $account->id); $con->add('status', '=', Friend\Record::NORMAL); $count = $this->sql->count($this->registry['table.user_friend'], $con); return $count > 0; }
/** * @param string $ip * @param string $timespan * @param \Fusio\Engine\Model\App $app * @return integer */ protected function getRequestCount($ip, $timespan, Model\App $app) { if (empty($timespan)) { return 0; } $now = new \DateTime(); $past = new \DateTime(); $past->sub(new \DateInterval($timespan)); $condition = new Condition(); if ($app->isAnonymous()) { $condition->equals('ip', $ip); } else { $condition->equals('appId', $app->getId()); } $condition->between('date', $past->format('Y-m-d H:i:s'), $now->format('Y-m-d H:i:s')); return $this->logTable->getCount($condition); }