/**
* Get deferred iterator for a given criteria or
* its absence. In other words, select certificates
* from a database
*
* @param array $criteria
* @return ResultSetPromise
*/
public function fetchAll(array $criteria = array())
{
$adapter = $this->certificates->getAdapter();
$conditions = '';
if (!empty($criteria)) {
foreach ($criteria as $column => $value) {
$conditions .= " AND {$column} = {$adapter->platform->quoteValue($value)}";
}
}
/**
* Using raw SQL query as query builder objects does not have that kind of advanced
* joining capabilities. While brainstorming this, an idea arisen, that we can just
* transform raw query into query builder object and assign in additional custom
* WHERE conditions as long as pagination-related limit/offset restrictions. Please
* @see https://github.com/nfx/sql-querybuilder for details. Only thing why I didn't
* include this package here is that ZF's API is not so stable at beta4 release.
*/
$query = "SELECT\n c.id,\n c.isin,\n c.currency,\n c.issuer,\n c.issuer_price,\n h.current_price,\n c.trading_market,\n bc.barrier_level,\n gc.participation_rate,\n c.type\n FROM\n history h -- it could be right join, but SQLite does not support it\n LEFT JOIN certificate c ON (h.certificate_id = c.id)\n INNER JOIN history h2 ON (h.certificate_id = h2.certificate_id)\n LEFT JOIN bonus_certificate bc ON (c.id = bc.certificate_id)\n LEFT JOIN guarantee_certificate gc ON (c.id = gc.certificate_id)\n WHERE\n h.created = h2.created\n {$conditions}\n GROUP BY c.id";
$statement = $adapter->query($query);
$prototypes = array(Entity\Certificate::STANDARD => new Entity\Certificate(), Entity\Certificate::BONUS => new Entity\BonusCertificate(), Entity\Certificate::GUARANTEE => new Entity\GuaranteeCertificate());
return new ResultSetPromise(function ($row) use($prototypes) {
$certificate = clone $prototypes[$row['type']];
$certificate->populate($row);
return $certificate;
}, $statement);
}
public function getUsersAndAbove(bool $paginated, $name = '', $roles = [])
{
$select = new Select('account');
$where = new Where();
if ($roles) {
$sub = $where->nest();
for ($i = 0; $i < count($roles); $i++) {
$sub->equalTo('role', $roles[$i]);
if ($i < count($roles) - 1) {
$sub->or;
}
}
$sub->unnest();
} else {
$where->greaterThan('role', '0');
$where->lessThan('role', '32');
}
if ($name) {
$where->like('name', '%' . $name . '%');
}
$select->where($where)->order('name ASC');
if ($paginated) {
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Account());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select($select);
}
/**
* Gets the set of ratings for the given type id.
*
* @param string $typeId The type identifier to get the set of ratings for.
* @return RatingSet
*/
public function getRatingSet($typeId)
{
$adapter = $this->gateway->getAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from($this->gateway->getTable());
$select->columns(array('amount' => new Expression('COUNT(rating)'), 'avarage' => new Expression('AVG(rating)'), 'highest' => new Expression('MAX(rating)'), 'lowest' => new Expression('MIN(rating)')), false);
$rowset = $this->gateway->selectWith($select);
$row = $rowset->current();
return new RatingSet($typeId, $row['amount'], $row['avarage'], $row['highest'], $row['lowest']);
}
/**
* @param $select
* @param array $options Paginator options
* @param ResultSetInterface $resultSet
* @return Paginator
*/
public function initPaginator($select, array $options, ResultSetInterface $resultSet = null)
{
$paginator = new Paginator(new DbSelect($select, $this->tableGateway->getAdapter(), $resultSet));
if (isset($options['perpage'])) {
$paginator->setItemCountPerPage($options['perpage']);
}
if (isset($options['page'])) {
$paginator->setCurrentPageNumber($options['page']);
}
return $paginator;
}
/**
* Returns all Media in db
*
* @return \Zend\Db\ResultSet\ResultSet
*/
public function fetchAll($paginated = false)
{
if ($paginated) {
$select = new Select('media');
$select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Media());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
});
}
/**
* Returns all accepted and declined applications
*/
public function getProcessedApplications($paginated = false)
{
if ($paginated) {
$select = new Select('application');
$select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Application());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
});
}
/**
* Returns all News in db
*
* @return \Zend\Db\ResultSet\ResultSet
*/
public function fetchAll($paginated = false)
{
if ($paginated) {
$select = new Select('news');
$select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['cname' => 'name'])->join(['m' => 'comment'], 'news.id = m.news_id', ['cid' => 'id'], $select::JOIN_LEFT)->order('news.date_posted DESC')->columns(['id', 'title', 'content', 'date_posted', 'comment_count' => new Expression('COUNT(m.id)')])->group('news.id');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new News());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['category'])->order('date_posted DESC');
});
}
public function test__construct()
{
// constructor with only required args
$table = new TableGateway(
'foo',
$this->mockAdapter
);
$this->assertEquals('foo', $table->getTable());
$this->assertSame($this->mockAdapter, $table->getAdapter());
$this->assertInstanceOf('Zend\Db\TableGateway\Feature\FeatureSet', $table->getFeatureSet());
$this->assertInstanceOf('Zend\Db\ResultSet\ResultSet', $table->getResultSetPrototype());
$this->assertInstanceOf('Zend\Db\Sql\Sql', $table->getSql());
// injecting all args
$table = new TableGateway(
'foo',
$this->mockAdapter,
$featureSet = new Feature\FeatureSet,
$resultSet = new ResultSet,
$sql = new Sql($this->mockAdapter, 'foo')
);
$this->assertEquals('foo', $table->getTable());
$this->assertSame($this->mockAdapter, $table->getAdapter());
$this->assertSame($featureSet, $table->getFeatureSet());
$this->assertSame($resultSet, $table->getResultSetPrototype());
$this->assertSame($sql, $table->getSql());
}
/**
* {@inheritdoc}
*
* {@inheritdoc}
*/
public function count()
{
$adapter = $this->dbTable->getAdapter();
/* @var $rowset Zend\Db\ResultSet\ResultSet */
$rowset = $adapter->query('SELECT COUNT(*) AS count FROM ' . $adapter->platform->quoteIdentifier($this->dbTable->getTable()), $adapter::QUERY_MODE_EXECUTE);
return $rowset->current()['count'];
}
/**
* Adds a data
*/
public function insert()
{
$id = $this->table->insert($this->data);
if (is_null($id)) {
if (array_key_exists('firephp', $GLOBALS)) {
$GLOBALS['firephp']->error('Error on query ' . $this->table->getSql()->getSqlPlatform()->getSqlString($this->table->getAdapter()->getPlatform()));
}
throw new Exception('Error on query ' . $this->table->getSql()->getSqlPlatform()->getSqlString($this->table->getAdapter()->getPlatform()));
}
$this->data[$this->primary] = $this->table->getLastInsertValue();
}
/**
* Constructs instance.
*
* @param TableGateway $tableGateway
* @param Where|\Closure|string|array $where
* @param null $order
*/
public function __construct(TableGateway $tableGateway, $where = null, $order = null)
{
$select = $tableGateway->getSql()->select();
if ($where) {
$select->where($where);
}
if ($order) {
$select->order($order);
}
$dbAdapter = $tableGateway->getAdapter();
$resultSetPrototype = $tableGateway->getResultSetPrototype();
parent::__construct($select, $dbAdapter, $resultSetPrototype);
}
public function getSqlQuery(Query $query)
{
$conditionBuilder = new SqlConditionBuilder($this->dbTable->getAdapter(), $this->dbTable->getTable());
$selectSQL = $this->dbTable->getSql()->select();
$selectSQL->where($conditionBuilder($query->getQuery()));
$selectSQL = $this->setSelectOrder($selectSQL, $query);
$selectSQL = $this->setSelectLimitOffset($selectSQL, $query);
$selectSQL = $this->setSelectColumns($selectSQL, $query);
$selectSQL = $this->setSelectJoin($selectSQL, $query);
$selectSQL = $this->makeExternalSql($selectSQL);
//build sql string
$sql = $this->dbTable->getSql()->buildSqlString($selectSQL);
//replace double ` char to single.
$sql = str_replace(["`(", ")`", "``"], ['(', ')', "`"], $sql);
return $sql;
}
public function test__construct()
{
// constructor with only required args
$table = new TableGateway('foo', $this->mockAdapter);
$this->assertEquals('foo', $table->getTable());
$this->assertSame($this->mockAdapter, $table->getAdapter());
$this->assertInstanceOf('Zend\\Db\\TableGateway\\Feature\\FeatureSet', $table->getFeatureSet());
$this->assertInstanceOf('Zend\\Db\\ResultSet\\ResultSet', $table->getResultSetPrototype());
$this->assertInstanceOf('Zend\\Db\\Sql\\Sql', $table->getSql());
// injecting all args
$table = new TableGateway('foo', $this->mockAdapter, $featureSet = new Feature\FeatureSet(), $resultSet = new ResultSet(), $sql = new Sql($this->mockAdapter, 'foo'));
$this->assertEquals('foo', $table->getTable());
$this->assertSame($this->mockAdapter, $table->getAdapter());
$this->assertSame($featureSet, $table->getFeatureSet());
$this->assertSame($resultSet, $table->getResultSetPrototype());
$this->assertSame($sql, $table->getSql());
// constructor expects exception
$this->setExpectedException('Zend\\Db\\TableGateway\\Exception\\InvalidArgumentException', 'Table name must be a string or an instance of Zend\\Db\\Sql\\TableIdentifier');
new TableGateway(null, $this->mockAdapter);
}
/**
* Update siblings nodes
*
* @param array $data
* @param integer $leftKey
* @param integer $rightKey
* @param integer $level
* @param array $filter
* @param boolean $useTransaction
* @return boolean|string
*/
public function updateSiblingsNodes(array $data, $leftKey, $rightKey, $level = null, array $filter = [], $useTransaction = true)
{
try {
if ($useTransaction) {
$this->tableGateway->getAdapter()->getDriver()->getConnection()->beginTransaction();
}
$baseFilter = array_merge([(new Predicate())->greaterThan($this->left, $leftKey), (new Predicate())->lessThan($this->right, $rightKey)], $filter);
if ($level) {
$baseFilter[$this->level] = $level;
}
$this->tableGateway->update($data, $baseFilter);
if ($useTransaction) {
$this->tableGateway->getAdapter()->getDriver()->getConnection()->commit();
}
} catch (Exception $e) {
if ($useTransaction) {
$this->tableGateway->getAdapter()->getDriver()->getConnection()->rollback();
}
ApplicationErrorLogger::log($e);
return $e->getMessage();
}
return true;
}
/**
* Garbage Collection
*
* @param int $maxlifetime
* @return true
*/
public function gc($maxlifetime)
{
$platform = $this->tableGateway->getAdapter()->getPlatform();
return (bool) $this->tableGateway->delete(sprintf('%s + %s < %d', $platform->quoteIdentifier($this->options->getModifiedColumn()), $platform->quoteIdentifier($this->options->getLifetimeColumn()), time()));
}
/**
* @covers Zend\Db\TableGateway\TableGateway::getAdapter
*/
public function testGetAdapter()
{
$this->assertSame($this->mockAdapter, $this->table->getAdapter());
}
protected function selectFoundRows()
{
return (int) $this->tableGateway->getAdapter()->createStatement('SELECT FOUND_ROWS()')->execute()->current()['FOUND_ROWS()'];
}
protected function getConnection()
{
return $this->gateway->getAdapter()->getDriver()->getConnection();
}
public function getAdapter()
{
return $this->tableGateway->getAdapter();
}
/**
* @return $this
*/
public function commit()
{
$this->gateway->getAdapter()->getDriver()->getConnection()->commit();
return $this;
}
