/**
* Returns the select object to be used for validation.
* @return Zend_Db_Select $select
*/
public function getSelect()
{
if (null === $this->_select) {
$db = $this->getAdapter();
// Zend_Debug::dump($this->_excludeId ); // die(0);
$select = new Zend_Db_Select($db);
$select->from($this->_table, array($this->_field), $this->_schema);
if ($db->supportsParameters('named')) {
$select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
// named
} else {
$select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
// positional
}
$select->limit(1);
if (!empty($this->_meetingId)) {
$select->where($db->quoteInto('`meeting_id` = ?', $this->_meetingId));
}
if (!empty($this->_excludeId)) {
$select->where($db->quoteInto('`id` != ?', $this->_excludeId));
}
$this->_select = $select;
}
return $this->_select;
}
/**
* Run query and returns matches, or null if no matches are found.
*
* @param String $value
* @return Array when matches are found.
*/
protected function _query($value)
{
/**
* Check for an adapter being defined. if not, fetch the default adapter.
*/
if ($this->_adapter === null) {
$this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
}
/**
* Build select object
*/
$select = new Zend_Db_Select($this->_adapter);
$select->from($this->_table, array($this->_field), $this->_schema)->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
if ($this->_exclude !== null) {
if (is_array($this->_exclude)) {
$select->where($this->_adapter->quoteIdentifier($this->_exclude['field']) . ' != ?', $this->_exclude['value']);
} else {
$select->where($this->_exclude);
}
}
$select->limit(1);
/**
* Run query
*/
$result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
return $result;
}
/**
* Returns an array of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
// Cast to integers, as $itemCountPerPage can be string sometimes and that would fail later checks
$offset = (int) $offset;
$itemCountPerPage = (int) $itemCountPerPage;
if ($this->_lastOffset === $offset && $this->_lastItemCount === $itemCountPerPage && null !== $this->_lastItems) {
return $this->_lastItems;
}
$this->_lastOffset = $offset;
$this->_lastItemCount = $itemCountPerPage;
// Optimization: by using the MySQL feature SQL_CALC_FOUND_ROWS
// we can get the count and the results in a single query.
$db = $this->_select->getAdapter();
if (null === $this->_count && $db instanceof \Zend_Db_Adapter_Mysqli) {
$this->_select->limit($itemCountPerPage, $offset);
$sql = $this->_select->__toString();
if (\MUtil_String::startsWith($sql, 'select ', true)) {
$sql = 'SELECT SQL_CALC_FOUND_ROWS ' . substr($sql, 7);
}
$this->_lastItems = $db->fetchAll($sql);
$this->_count = $db->fetchOne('SELECT FOUND_ROWS()');
} else {
$this->_lastItems = $this->_selectAdapter->getItems($offset, $itemCountPerPage);
}
if (is_array($this->_lastItems)) {
if (isset($this->_model->prefetchIterator) && $this->_model->prefetchIterator) {
$this->_lastItems = new \ArrayIterator($this->_lastItems);
}
$this->_lastItems = $this->_model->processAfterLoad($this->_lastItems, false, false);
}
return $this->_lastItems;
}
function getVesion()
{
$select = new Zend_Db_Select($this->db);
$select->from('Version', 'VersionId');
$select->limit(1);
$res = $this->db->fetchOne($select);
return $res;
}
public function limit($count = null, $offset = null)
{
if (is_array($count)) {
$offset = $count['start'];
$count = $count['limit'];
}
return parent::limit($count, $offset);
}
/**
* Set's the query limit
*
* @param int $count Offset Start
* @param int $offset Offset End
*
* @return Zend_Db_Select
*/
public function buildQueryLimit($count, $offset)
{
if ($this->_limit > 0 && $this->_limit < $count) {
$count = $this->_limit;
}
$this->_select->limit($count, $offset);
return $this;
}
function getClientId($client_name)
{
$select = new Zend_Db_Select($this->_db);
$select->from('Client');
$select->where("Name = ?", $client_name);
$select->limit(1);
$stmt = $select->query();
$res = $stmt->fetch();
return $res['clientid'];
}
public function search($word = null, $limit = null)
{
$db = Zend_Registry::get('db');
$sql = new Zend_Db_Select($db);
$select = $sql->from('albums');
if (!is_null($word)) {
$select = $sql->where($db->quoteInto('title LIKE ? OR artist LIKE ?', "%" . $word . "%"));
}
if ($limit > 0) {
$select = $sql->limit($limit);
}
$select = $sql->order('artist DESC');
$results = $db->query($select);
$rows = $results->fetchAll();
return $rows;
}
/**
* @return array
*/
public function fetchRow()
{
$this->sql_select->limit(1);
return $this->sql_select->query()->fetch();
}
/**
* Extract limit and offset from the filter and add it to a select
*
* @param array $filter
* @param \Zend_Db_Select $select
*/
protected function filterLimitOffset(&$filter, $select)
{
$limit = null;
$offset = null;
if (array_key_exists('limit', $filter)) {
$limit = (int) $filter['limit'];
unset($filter['limit']);
}
if (array_key_exists('offset', $filter)) {
$offset = (int) $filter['offset'];
unset($filter['offset']);
}
$select->limit($limit, $offset);
}
/**
* fetchRow wrapper
* This function does not invalidate/store any data on the dataset; All read rows are returned as an array directly
*
* @param string|\Zend_Db_Select $sql
* @param array $params
* @return mixed Array, object, or scalar depending on fetch mode.
*/
public function fetchRow($sql, array $params = [])
{
if ($sql instanceof \Zend_Db_Select) {
$sql->limit(1);
}
$sql = $this->assembleSql($sql);
return $this->getReadAdapter()->fetchRow($sql, $params);
}
public function getProduct($newsDate = null)
{
$start_date = new DateTime();
$start_date->setTime(20, 0, 0);
$start_date->setDate($newsDate->format("Y"), $newsDate->format("m"), $newsDate->format("d") - 1);
$end_date = new DateTime();
$end_date->setTime(10, 0, 0);
$end_date->setDate($newsDate->format("Y"), $newsDate->format("m"), $newsDate->format("d"));
$select = new Zend_Db_Select($this->getDefaultAdapter());
$select->from(array('c' => $this->_campanha));
$select->where('start_date = ?', $start_date->format("Y-m-d H:i:s"));
$select->where('start_date = end_date');
$select->where('is_banner = ?', '0');
$select->where('is_magazine = ?', '0');
$select->where('is_product = ?', '1');
$select->where('is_active = ?', '1');
$select->order("priority DESC");
$select->order("end_date ASC");
$select->limit(1);
return $this->_db->fetchAssoc($select);
}
/**
* Overrides _query() from Zend_Validate_Db_Abstract
*
* @param mixed $value
* @access protected
* @return void
*/
protected function _query($value)
{
if ($this->_adapter === NULL) {
$this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
if (NULL === $this->_adapter) {
require_once 'Zend/Validate/Exception.php';
throw new Zend_Validate_Exception('No database adapter present');
}
}
$select = new Zend_Db_Select($this->_adapter);
$select->from($this->_table, array($this->_field), $this->_schema);
if (NULL == $this->_userPkValue) {
require_once 'Zend/Validate/Exception.php';
throw new Zend_Validate_Exception('You must specify the value for the primary / unique key');
}
$select->where($this->_adapter->quoteIdentifier($this->_userPkField) . ' = ?', $this->_userPkValue);
if (strpos($this->_treatment, '?') !== FALSE || $this->_treatment instanceof Zend_Db_Expr) {
$where = $this->_adapter->quoteIdentifier($this->_field) . ' = ' . $this->_treatment;
$select->where($where, $value);
} else {
$value = call_user_func($this->_treatment, $value);
$select->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
}
$select->limit(1);
$result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
return $result;
}
/**
* Refine the active pastes selection based on criteria provided
*
* Allows setting a limit to the number of records returend
*
* @param Zend_Db_Select $select
* @param array $criteria
* @return void
*/
protected function _refineSelection(Zend_Db_Select $select, array $criteria)
{
if (array_key_exists('start', $criteria) && $criteria['start'] == intval($criteria['start'])) {
if (array_key_exists('count', $criteria) && $criteria['count'] == intval($criteria['count'])) {
$select->limit($criteria['count'], $criteria['start']);
}
}
$sorted = false;
if (array_key_exists('sort', $criteria)) {
$sort = $criteria['sort'];
$dir = 'ASC';
if ('-' == substr($sort, 0, 1)) {
$sort = substr($sort, 1);
$dir = 'DESC';
}
$fields = $this->getTable()->info('cols');
if (in_array($sort, $fields)) {
$select->order("{$sort} {$dir}");
$sorted = true;
}
}
if (!$sorted) {
$select->order('created DESC');
}
}
/**
* Gets the select object to be used by the validator.
* If no select object was supplied to the constructor,
* then it will auto-generate one from the given table,
* schema, field, and adapter options.
*
* @return Zend_Db_Select The Select object which will be used
*/
public function getSelect()
{
if (null === $this->_select) {
$db = $this->getAdapter();
/**
* Build select object
*/
$select = new Zend_Db_Select($db);
$select->from($this->_table, array($this->_field), $this->_schema);
if ($db->supportsParameters('named')) {
$select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
// named
} else {
$select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
// positional
}
if ($this->_exclude !== null) {
if (is_array($this->_exclude)) {
$select->where($db->quoteIdentifier($this->_exclude['field'], true) . ' != ?', $this->_exclude['value']);
} else {
$select->where($this->_exclude);
}
}
$select->limit(1);
$this->_select = $select;
}
return $this->_select;
}
/**
* Set's the query limit
*
* @param int $start Offset Start
* @param int $offset Offset End
*
* @return Zend_Db_Select
*/
public function buildQueryLimit($start, $offset)
{
$this->_select->limit($start, $offset);
return $this;
}
/**
* Find File(s) by Path/Name file
*
* @param $path with trailing slash
* @param $namefile
* @param $client
* @param $limit
* @param $type_search [ordinary | like | regexp]
* @return rows
*/
function getByFileName($path, $namefile, $client, $limit, $type_search)
{
if (isset($namefile, $client)) {
$select = new Zend_Db_Select($this->db);
$select->distinct();
$select->limit($limit);
switch ($this->db_adapter) {
case 'PDO_MYSQL':
$select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'StartTime' => "DATE_FORMAT(j.StartTime, '%y-%b-%d %H:%i')", 'EndTime' => "DATE_FORMAT(j.EndTime, '%y-%b-%d %H:%i')", 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => 'TIMEDIFF(EndTime, StartTime)'));
$select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
$select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
$select->joinLeft('Path', 'File.PathId = Path.PathId', array('Path' => 'Path.Path'));
$select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('JobStatusLong' => 'Status.JobStatusLong'));
$select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('ClientName' => 'Client.Name'));
$select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('PoolName' => 'Pool.Name'));
$select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('FileSet' => 'FileSet.FileSet'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
break;
case 'PDO_PGSQL':
// PostgreSQL
// http://www.postgresql.org/docs/8.0/static/functions-datetime.html
$select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => '(EndTime - StartTime)'));
$select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
$select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
$select->joinLeft('Path', 'File.PathId = Path.PathId', array('Path' => 'Path.Path'));
$select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('JobStatusLong' => 'Status.JobStatusLong'));
$select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('ClientName' => 'Client.Name'));
$select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('PoolName' => 'Pool.Name'));
$select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('FileSet' => 'FileSet.FileSet'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// workaround of bug http://framework.zend.com/issues/browse/ZF-884
$select->from(array('j' => 'Job'), array('jobid' => 'JobId', 'type' => 'Type', 'JobName' => 'Name', 'level' => 'Level', 'clientid' => 'ClientId', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'volsessionid' => 'VolSessionId', 'volsessiontime' => 'VolSessionTime', 'jobfiles' => 'JobFiles', 'jobbytes' => 'JobBytes', 'joberrors' => 'JobErrors', 'reviewed' => 'Reviewed', 'poolid' => 'PoolId', 'filesetid' => 'FileSetId', 'purgedfiles' => 'PurgedFiles', 'jobstatus' => 'JobStatus', 'DurationTime' => "(strftime('%H:%M:%S',strftime('%s',EndTime) - strftime('%s',StartTime),'unixepoch'))"));
$select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
$select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
$select->joinLeft('Path', 'File.PathId = Path.PathId', array('path' => 'Path.Path'));
$select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('jobstatuslong' => 'Status.JobStatusLong'));
$select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('clientname' => 'Client.Name'));
$select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('poolname' => 'Pool.Name'));
$select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('fileset' => 'FileSet.FileSet'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
break;
}
// terminated jobs
$select->where("j.JobStatus IN ('T', 'E', 'e', 'f', 'A', 'W')");
if (!empty($path)) {
$select->where($this->myMakeWhere('Path.Path', $path, $type_search));
}
$select->where($this->myMakeWhere('Filename.Name', $namefile, $type_search));
if (!empty($client)) {
$select->where($this->db->quoteInto("Client.Name = ?", $client));
}
$select->order(array("StartTime"));
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
}
$stmt = $select->query();
// do Bacula ACLs
return $this->bacula_acl->doBaculaAcl($stmt->fetchAll(), 'jobname', 'job');
}
/**
* @return WeFlex_Db_Model
*/
public function limit($count = null, $offset = null)
{
$this->_selector->limit($count, $offset);
return $this;
}
/**
* Returns an array of objects queried from the given t41_Object_Collection instance parameters
*
* The given collection is populated if it comes empty of members.
*
* In any other case, this method doesn't directly populate the collection. This action is under the responsability of
* the caller. For example, the t41_Object_Collection::find() method takes care of it.
*
* @param t41\ObjectModel\Collection $collection
* @param boolean|array $returnCount true = counting, array = stats on listed properties
* @param string $subOp complex operation like SUM or AVG
* @return array
*/
public function find(ObjectModel\Collection $collection, $returnCount = false, $subOp = null)
{
$this->_class = $class = $collection->getDataObject()->getClass();
$table = $this->_getTableFromClass($class);
if (!$table) {
throw new Exception('MISSING_DBTABLE_PARAM');
}
// primary key is either part of the mapper configuration or 'id'
$pkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : \t41\Backend::DEFAULT_PKEY;
if (is_array($pkey)) {
$composite = array();
/* @var $obj t41\Backend\Key */
foreach ($pkey as $obj) {
$composite[] = sprintf('TRIM(%s)', $table . '.' . $obj->getName());
$composite[] = Backend\Mapper::VALUES_SEPARATOR;
}
$pkey = sprintf("CONCAT(%s) AS %s", implode(',', $composite), Backend::DEFAULT_PKEY);
} else {
$pkey = $table . '.' . $pkey;
}
$this->_connect();
/* @var $select \Zend_Db_Select */
$this->_select = $this->_ressource->select();
// detect if query is of stat-kind
if ($returnCount) {
switch ($subOp) {
case ObjectModel::CALC_SUM:
$expressions = array();
foreach ($returnCount as $propKey => $property) {
$prop = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $propKey) : $propKey;
$expressions[] = sprintf('SUM(%s.%s)', $table, $prop);
}
$subOpExpr = implode('+', $expressions);
break;
case ObjectModel::CALC_AVG:
$subOpExpr = sprintf('AVG(%s)', $returnCount);
break;
default:
$subOpExpr = 'COUNT(*)';
break;
}
$this->_select->from($table, new \Zend_Db_Expr($subOpExpr . " AS " . \t41\Backend::MAX_ROWS_IDENTIFIER));
} else {
$this->_select->distinct();
$this->_select->from($table, $pkey);
}
$this->_alreadyJoined = array();
/* @var $condition t41\Backend\Condition */
foreach ($collection->getConditions() as $conditionArray) {
// combo conditions
if ($conditionArray[0] instanceof Condition\Combo) {
$statement = array();
foreach ($conditionArray[0]->getConditions() as $condition) {
$statement[] = $this->_parseCondition($condition[0], $this->_select, $table);
}
$statement = implode(' OR ', $statement);
switch ($conditionArray[1]) {
case Condition::MODE_OR:
$this->_select->orWhere($statement);
break;
case Condition::MODE_AND:
default:
$this->_select->where($statement);
break;
}
continue;
}
// optional table where the column may be
$jtable = '';
// condition object is in the first key
$condition = $conditionArray[0];
/* does condition contain another condition object ? */
if ($condition->isRecursive()) {
while ($condition->isRecursive()) {
$property = $condition->getProperty();
$parent = $property->getParent() ? $property->getParent()->getId() : $table;
$condition = $condition->getCondition();
if ($jtable) {
$parentTable = $jtable;
} else {
if ($parent) {
$parentTable = $this->_mapper ? $this->_mapper->getDatastore($parent) : $parent;
} else {
$parentTable = $table;
}
}
$jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
/* column name in left table */
$jlkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId();
$uniqext = $jtable . '__joined_for__' . $jlkey;
if (in_array($uniqext, $this->_alreadyJoined)) {
$class = $property->getParameter('instanceof');
$jtable = $uniqext;
continue;
}
/* pkey name in joined table */
$jpkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$join = sprintf("%s.%s = %s.%s", $parentTable, $jlkey, $uniqext, $jpkey);
$this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array());
$this->_alreadyJoined[$jtable] = $uniqext;
//$jtable;
$jtable = $uniqext;
$class = $property->getParameter('instanceof');
}
}
$property = $condition->getProperty();
if ($property instanceof Property\ObjectProperty) {
// no join if object is stored in a different backend !
// @todo improve this part
if (ObjectModel::getObjectBackend($property->getParameter('instanceof'))->getAlias() != $this->_uri->getAlias()) {
$clauses = $condition->getClauses();
if ($clauses[0]['value'] != Condition::NO_VALUE) {
$clauses[0]['operator'] = Condition::OPERATOR_ENDSWITH | Condition::OPERATOR_EQUAL;
$condition->setClauses($clauses);
}
$field = $this->_mapper ? $this->_mapper->propertyToDatastoreName($this->_class, $property->getId()) : $property->getId();
} else {
// which table to join with ? (in case of condition is last element of a recursion)
$jtable2 = $jtable ? $jtable : $table;
$jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId();
$field = $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$uniqext = $jtable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
$join = sprintf("%s.%s = %s.%s", $jtable2, $leftkey, $uniqext, is_array($rightkey) ? $rightkey[0] : $rightkey);
$this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array());
$this->_alreadyJoined[$jtable] = $uniqext;
}
$jtable = $uniqext;
}
} else {
if ($property instanceof Property\CollectionProperty) {
// handling of conditions based on collection limited to withMembers() and withoutMembers()
$leftkey = $property->getParameter('keyprop');
$field = $property->getId();
$subSelect = $this->_ressource->select();
$subseltbl = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
$subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey)));
$join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey);
$subSelect->where($join);
$statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]);
$this->_select->where($statement);
continue;
} else {
$field = $property->getId();
if ($this->_mapper) {
$field = $this->_mapper->propertyToDatastoreName($class, $field);
}
}
}
/* convert identifier tag to the valid primary key */
if ($field == ObjectUri::IDENTIFIER) {
// @todo handle multiple keys from mapper
$field = $table . '.';
$key = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$field .= is_array($key) ? $key[0] : $key;
} else {
if ($jtable) {
if (array_key_exists($jtable, $this->_alreadyJoined)) {
$field = $this->_alreadyJoined[$jtable] . '.' . $field;
} else {
$tmp = $jtable . '.';
$tmp .= is_array($field) ? $field[0] : $field;
$field = $tmp;
}
} else {
if (array_key_exists($table, $this->_alreadyJoined)) {
$field = $this->_alreadyJoined[$table] . '.' . $field;
} else {
$field = $table . '.' . $field;
}
}
}
if ($field instanceof Key) {
$field = $table . '.' . $field->getName();
}
// protect DateProperty() with setted timepart parameter from misuse
if ($property instanceof DateProperty && $property->getParameter('timepart') == true) {
$field = "DATE({$field})";
}
$statement = $this->_buildConditionStatement($field, $condition->getClauses(), $conditionArray[1]);
switch ($conditionArray[1]) {
case Condition::MODE_OR:
$this->_select->orWhere($statement);
break;
case Condition::MODE_AND:
default:
$this->_select->where($statement);
break;
}
}
// Adjust query based on returnCount
if ($returnCount) {
if (is_array($returnCount)) {
if ($subOp) {
} else {
// return count on grouped columns
foreach ($returnCount as $key => $property) {
$fieldmodifier = null;
if ($this->_mapper) {
$class = $property->getParent() ? $property->getParent()->getId() : $collection->getDataObject()->getClass();
$field = $this->_mapper->propertyToDatastoreName($class, $property->getId());
} else {
$field = $property->getId();
}
if ($property instanceof ObjectProperty) {
// join with $key if necessary
if (strstr($key, '.') !== false) {
$leftPart = substr($key, 0, strpos($key, '.'));
$intermediateProp = $collection->getDataObject()->getProperty($leftPart);
$fieldmodifier = $this->_join($intermediateProp, $table) . '.' . $field;
}
}
// limit date grouping to date part, omitting possible hour part
if ($property instanceof DateProperty) {
$fieldmodifier = "DATE({$field})";
}
$this->_select->group($fieldmodifier ? $fieldmodifier : $field);
$this->_select->columns(array($field => $fieldmodifier ? $fieldmodifier : $field));
}
}
} else {
$this->_select->reset('group');
}
} else {
$this->_select->limit($collection->getBoundaryBatch() != -1 ? $collection->getBoundaryBatch() : null, $collection->getBoundaryOffset());
/**
* Sorting part
*/
foreach ($collection->getSortings() as $sorting) {
$slUniqext = $slTable = null;
// Specific cases first
// @todo find a better way to sort on meta properties
if ($sorting[0]->getId() == ObjectUri::IDENTIFIER || $sorting[0] instanceof MetaProperty) {
$id = Backend::DEFAULT_PKEY;
$this->_select->order(new \Zend_Db_Expr($table . '.' . $id . ' ' . $sorting[1]));
continue;
} else {
if ($sorting[0] instanceof Property\CollectionProperty) {
// handling of conditions based on collection limited to withMembers() and withoutMembers()
$leftkey = $sorting[0]->getParameter('keyprop');
//$field = $property->getId();
$subSelect = $this->_ressource->select();
$subseltbl = $this->_mapper ? $this->_mapper->getDatastore($sorting[0]->getParameter('instanceof')) : $this->_getTableFromClass($sorting[0]->getParameter('instanceof'));
$subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey)));
$join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey);
$subSelect->where($join);
// $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]);
$this->_select->order(new \Zend_Db_Expr('(' . $subSelect->__toString() . ') ' . $sorting[1]));
continue;
} else {
if ($sorting[0] instanceof Property\ObjectProperty) {
// find which property to sort by
if ($sorting[0]->getParameter('sorting')) {
$sprops = array_keys($sorting[0]->getParameter('sorting'));
} else {
// try to sort with properties used to display value
if (substr($sorting[0]->getParameter('display'), 0, 1) == '[') {
// @todo extract elements of pattern to order from them ?
$sprops = array('id');
} else {
$sprops = explode(',', $sorting[0]->getParameter('display'));
}
}
// sorting property belongs to a second-level join
if ($sorting[0]->getParent()->getClass() != $collection->getClass()) {
$leftkey = 'commande';
//$this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getParent()getId()) : $sorting[0]->getId();
$class = $sorting[0]->getParent()->getClass();
$stable = $this->_getTableFromClass($class);
$sbackend = ObjectModel::getObjectBackend($class);
// Property to sort from is in a different backend from current one
if ($sbackend->getAlias() != $this->getAlias()) {
// We presume that the current backend is allowed to connect to the remote one
// Should we raise an exception instead ?
$stable = $sbackend->getUri()->getDatabase() . '.' . $stable;
}
$field = $sorting[0]->getId();
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
if (is_array($rightkey)) {
foreach ($rightkey as $rightkeyObj) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName());
}
} else {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey);
}
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
$slTable = $this->_getTableFromClass($sorting[0]->getParameter('instanceof'));
$slUniqext = $uniqext;
}
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getId()) : $sorting[0]->getId();
$class = $sorting[0]->getParameter('instanceof');
$stable = isset($slTable) ? $slTable : $this->_getTableFromClass($class);
$sbackend = ObjectModel::getObjectBackend($class);
// Property to sort from is in a different backend from current one
if ($sbackend->getAlias() != $this->getAlias()) {
// We presume that the current backend is allowed to connect to the remote one
// Should we raise an exception instead ?
$stable = $sbackend->getUri()->getDatabase() . '.' . $stable;
}
$field = $sorting[0]->getId();
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
if (is_array($rightkey)) {
foreach ($rightkey as $rightkeyObj) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName());
}
} else {
$join = sprintf("%s.%s = %s.%s", isset($slUniqext) ? $slUniqext : $table, $leftkey, $uniqext, $rightkey);
}
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
foreach ($sprops as $sprop) {
if ($this->_mapper) {
$sfield = $this->_mapper->propertyToDatastoreName($class, $sprop);
} else {
$sfield = $sprop;
}
$sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield;
if (isset($sorting[2]) && !empty($sorting[2])) {
$sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr);
}
$this->_select->order(new \Zend_Db_Expr($sortingExpr . ' ' . $sorting[1]));
}
continue;
}
}
}
// default sorting on a different table
$class = $sorting[0]->getParent() ? $sorting[0]->getParent()->getClass() : $collection->getDataObject()->getClass();
$stable = $this->_getTableFromClass($class);
if ($this->_mapper) {
$sfield = $this->_mapper->propertyToDatastoreName($class, $sorting[0]->getId());
} else {
$field = $sorting[0];
$sfield = $field->getId();
}
// add a left join if the sorting field belongs to a table not yet part of the query
if ($stable != $table) {
// get the property id from the class name
$tfield = isset($sorting[3]) ? $sorting[3] : $collection->getDataObject()->getObjectPropertyId($class);
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $tfield) : $tfield;
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($field->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey);
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
$sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield;
} else {
$sortingExpr = $stable . '.' . $sfield;
}
if (isset($sorting[2]) && !empty($sorting[2])) {
$sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr);
}
$this->_select->order(new \Zend_Db_Expr('TRIM(' . $sortingExpr . ') ' . $sorting[1]));
}
}
$result = array();
$context = array('table' => $table);
try {
if (true && $returnCount == false) {
$this->_select->columns($this->_getColumns($collection->getDataObject()));
}
$result = $this->_ressource->fetchAll($this->_select);
} catch (\Zend_Db_Exception $e) {
$context['error'] = $e->getMessage();
$this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context);
return false;
}
$this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context);
if ($returnCount !== false) {
return is_array($returnCount) ? $result : $result[0][Backend::MAX_ROWS_IDENTIFIER];
}
// convert array of primary keys to strings
foreach ($result as $key => $val) {
// $result[$key] = implode(Backend\Mapper::VALUES_SEPARATOR, $val);
}
/* prepare base of object uri */
$uri = new ObjectModel\ObjectUri();
$uri->setBackendUri($this->_uri);
$uri->setClass($collection->getDataObject()->getClass());
$uri->setUrl($this->_database . '/' . $table . '/');
return $collection->populate($result, $uri);
//return $this->_populateCollection($result, $collection, $uri);
}
/**
* Returns an array of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
$this->_select->limit($itemCountPerPage, $offset);
return $this->_select->query()->fetchAll();
}
/**
* Apply pagination to a select object via the LIMIT and OFFSET clauses.
*
* @param Zend_Db_Select $select
* @param integer $limit Number of results per "page".
* @param integer|null $page Page to retrieve, first if omitted.
* @return Zend_Db_Select
*/
public function applyPagination($select, $limit, $page = null)
{
if ($page) {
$select->limitPage($page, $limit);
} else {
$select->limit($limit);
}
return $select;
}
