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);
}
