/**
* Render sql select conditions
*
* @return Varien_Data_Collection_Db
*/
protected function _renderFilters()
{
if ($this->_isFiltersRendered) {
return $this;
}
$this->_renderFiltersBefore();
foreach ($this->_filters as $filter) {
switch ($filter['type']) {
case 'or':
$condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']);
$this->_select->orWhere($condition);
break;
case 'string':
$this->_select->where($filter['value']);
break;
case 'public':
$field = $this->_getMappedField($filter['field']);
$condition = $filter['value'];
$this->_select->where($this->_getConditionSql($field, $condition), null, Varien_Db_Select::TYPE_CONDITION);
break;
default:
$condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']);
$this->_select->where($condition);
}
}
$this->_isFiltersRendered = true;
return $this;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
// quote field identifier, set action and replace wildcards
$field = $this->_getQuotedFieldName($_backend);
$action = $this->_opSqlMap[$this->_operator];
$value = $this->_replaceWildcards($this->_value);
if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
$value = explode(' ', $this->_value);
}
if (in_array($this->_operator, array('equals', 'greater', 'less', 'in', 'notin'))) {
$value = str_replace(array('%', '\\_'), '', $value);
if (is_array($value) && empty($value)) {
$_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
} elseif ($this->_operator == 'equals' && ($value === '' || $value === NULL || $value === false)) {
$_select->where($field . 'IS NULL');
} else {
// finally append query to select object
$_select->where($field . $action['sqlop'], $value, $this->valueType);
}
} else {
// finally append query to select object
$_select->where($field . $action['sqlop'], $value);
}
if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
$_select->orWhere($field . ' IS NULL');
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql($_select, $_backend)
{
$this->_options['ignoreAcl'] = TRUE;
$this->_resolve();
$quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id');
$_select->where($this->_getQuotedFieldName($_backend) . ' IN (?)', empty($this->_containerIds) ? " " : $this->_containerIds);
$_select->orWhere($quotedDisplayContainerIdentifier . ' IN (?)', empty($this->_containerIds) ? " " : $this->_containerIds);
}
/**
* see where
* @return WeFlex_Db_Model
*/
public function orWhere($conditions)
{
if (is_array($conditions)) {
$conditions = $this->_translateToZendWhere($conditions);
}
$this->_selector->orWhere($conditions);
return $this;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql($_select, $_backend)
{
$this->_options['ignoreAcl'] = TRUE;
$this->_resolve();
$quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id');
$where = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($this->_getQuotedFieldName($_backend) . ' IN (?)', $this->_containerIds);
$orWhere = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($quotedDisplayContainerIdentifier . ' IN (?)', $this->_containerIds);
$_select->where($where);
$_select->orWhere($orWhere);
}
/**
* Retorna uma query com informações do empreendimentos cadastrados
*
* @param number $filter
* @throws Zend_Db_Exception
* @return mixed|boolean
*/
public function selectQueryList($filter = 1, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from($this->name);
$select->where('state=?', $filter);
if (!is_null($like)) {
$columns = array('nome', 'categoria', 'logradouro', 'incorporadora');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[3] . ' LIKE ?', '%' . $like . '%');
}
$select->order('id DESC');
return $select;
} catch (Zend_Db_Exception $e) {
throw new Zend_Db_Exception($e->getMessage());
return false;
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*
* @todo to be removed once we split filter model / backend
*/
public function appendFilterSql($_select, $_backend)
{
$now = new Tinebase_DateTime();
$now->setHour(0)->setMinute(0)->setSecond(0);
$db = Tinebase_Core::getDb();
if ($this->_value == 1) {
$_select->where($db->quoteInto('(' . $db->quoteIdentifier('employment_end') . ' >= ? ', $now, 'datetime') . ' OR ' . $db->quoteIdentifier('employment_end') . ' IS NULL )');
$_select->where($db->quoteInto('( ' . $db->quoteIdentifier('employment_begin') . ' <= ? ', $now, 'datetime') . ' OR ( ' . $db->quoteIdentifier('employment_begin') . ' IS NULL ))');
} else {
$_select->where($db->quoteInto($db->quoteIdentifier('employment_end') . ' < ? ', $now, 'datetime'));
$_select->orWhere($db->quoteInto($db->quoteIdentifier('employment_begin') . ' > ? ', $now, 'datetime'));
}
}
/**
* @param number $filterState
* @param string $userIds
* @throws Zend_Exception
*/
public function selectByUsersIds($filterState = 1, $ids, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from('' . $this->name . '');
$select->where('state=?', $filterState);
$select->where('created_user_id IN ( ' . $ids . ' )');
if (!is_null($like)) {
$columns = array('cpf', 'dados_cliente');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
}
$select->order('id DESC');
return $select;
} catch (Zend_Exception $e) {
throw new Zend_Exception($e->getMessage());
return false;
}
}
/**
* Render sql select conditions
*
* @return Varien_Data_Collection_Db
*/
protected function _renderFilters()
{
if ($this->_isFiltersRendered) {
return $this;
}
foreach ($this->_filters as $filter) {
switch ($filter['type']) {
case 'or':
$condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']);
$this->_select->orWhere($condition);
break;
case 'string':
$this->_select->where($filter['value']);
break;
default:
$condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']);
$this->_select->where($condition);
}
}
$this->_isFiltersRendered = true;
return $this;
}
/**
* Recupera dados da tabela #__clientes
*
* @param Zend_DB::FETCH $mode
* @return array
*/
public function selectAll($filterState = 1, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from(array('u' => $this->name), array('id', 'nome', 'email', 'id_perfil', 'acesso', 'state'));
$select->joinLeft(array('p' => $this->namePerfis), 'u.id_perfil = p.id', 'role');
$select->where('u.state = ?', $filterState);
if (!is_null($like)) {
$columns = array('u.nome', 'u.email', 'p.role');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%');
}
$select->order('u.id DESC');
return $select;
//return $result;
} catch (Zend_Db_Adapter_Exception $e) {
throw new Zend_Exception($e->getMessage());
}
}
function combineSubSelectIfWhereIsNotEmpty(Zend_Db_Select $select, Zend_Db_Select $subSelect)
{
$whereArray = $subSelect->getPart(Zend_Db_Select::WHERE);
if (count($whereArray)) {
return $select->orWhere(implode(' ', $whereArray));
}
return $select;
}
protected function applyWheres(Zend_Db_Select $select, $wheres)
{
foreach ($wheres as $where) {
@(list($condition, $value, $type) = $where);
if ($type == 'or') {
$select->orWhere($condition, $value);
} else {
$select->where($condition, $value);
}
}
return $select;
}
/**
* List Volumes likely to need replacement from age or errors
* bconsole -> query -> 16
* @return rows
*/
function getVolumesNeedReplacement()
{
$db = Zend_Registry::get('db_bacula');
// make select from multiple tables
$select = new Zend_Db_Select($db);
$select->from('Media', array("MediaId", 'PoolId', 'StorageId', 'VolumeName', 'VolStatus', 'VolJobs', 'MediaType', 'VolMounts', 'VolErrors', 'VolWrites', 'VolBytes'));
$select->orWhere("VolErrors > 0");
$select->orWhere("VolStatus = 'Error'");
$select->orWhere("VolMounts > 50");
// Number of time media mounted
$select->orWhere("VolStatus = 'Disabled'");
$select->orWhere("VolWrites > 3999999");
// Number of writes to media
$select->order(array('VolStatus ASC', 'VolErrors', 'VolMounts', 'VolumeName DESC'));
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
return $stmt->fetchAll();
}
/**
* Add's a new condition to the curretn query
*
* @param string $filter Filter to apply
* @param string $op Condition option
* @param array $completeField All fields options
*
* @return void
*/
public function addCondition($filter, $op, $completeField)
{
$op = strtolower($op);
$explode = explode('.', $completeField['field']);
$field = end($explode);
$simpleField = false;
$columns = $this->getColumns();
foreach ($columns as $value) {
if ($field == $value[2]) {
if (is_object($value[1])) {
$field = $value[1]->__toString();
$simpleField = true;
} else {
$field = $value[0] . '.' . $value[1];
}
break;
} elseif ($field == $value[0]) {
$field = $value[0] . '.' . $value[1];
break;
}
}
if (strpos($field, '.') === false && $simpleField === false) {
$field = $completeField['field'];
}
/**
* Reserved words from myslq dont contain any special charaters.
* But select expressions may.
*
* SELECT IF(City.Population>500000,1,0)....
*
* We can not quoteIdentifier this fields...
*
*/
if (strpos($field, '(') !== false) {
$field = $this->_getDb()->quoteIdentifier($field);
}
$func = 'where';
if (strpos($field, '(') !== false) {
$func = 'having';
}
switch ($op) {
case 'sqlexp':
$this->_select->{$func}(new Zend_Db_Expr($filter));
break;
case 'empty':
$this->_select->{$func}($field . " = '' ");
break;
case 'isnull':
$this->_select->{$func}(new Zend_Db_Expr($field . ' IS NULL '));
break;
case 'isnnotull':
$this->_select->{$func}(new Zend_Db_Expr($field . ' IS NOT NULL '));
break;
case 'equal':
case '=':
$this->_select->{$func}($field . ' = ?', $filter);
break;
case 'rege':
$this->_select->{$func}($field . " REGEXP " . $this->_getDb()->quote($filter));
break;
case 'rlike':
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote($filter . "%"));
break;
case 'llike':
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter));
break;
case '>=':
$this->_select->{$func}($field . " >= ?", $filter);
break;
case '>':
$this->_select->{$func}($field . " > ?", $filter);
break;
case '<>':
case '!=':
$this->_select->{$func}($field . " <> ?", $filter);
break;
case '<=':
$this->_select->{$func}($field . " <= ?", $filter);
break;
case '<':
$this->_select->{$func}($field . " < ?", $filter);
break;
case 'in':
$filter = explode(',', $filter);
$this->_select->{$func}($field . " IN (?)", $filter);
break;
case 'flag':
$this->_select->{$func}($field . " & ? <> 0", $filter);
break;
case '||':
$this->_select->orWhere($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%"));
break;
case 'range':
case '&':
case 'and':
$start = substr($filter, 0, strpos($filter, '<>'));
$end = substr($filter, strpos($filter, '<>') + 2);
$this->_select->{$func}($field . " between " . $this->_getDb()->quote($start) . " and " . $this->_getDb()->quote($end));
break;
case 'like':
default:
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%"));
break;
}
}
/**
* 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);
}
/**
* Add's a new condition to the curretn query
*
* @param string $filter Filter to apply
* @param string $op Condition option
* @param array $completeField All fields options
*
* @return void
*/
public function addCondition($filter, $op, $completeField)
{
$op = strtolower($op);
// TODO $completeField should contain key ($grid->_data['fields'][$key]),
// cleaner would be to pass key as parameter and provide access to field definition from grid class
$explode = explode('.', $completeField['field']);
$field = end($explode);
$simpleField = false;
// mark the value as Zend_Db_Expr
$sqlExpr = false;
$columns = $this->getColumns();
// TODO it would be worth to rework this inefficient algorithm
foreach ($columns as $value) {
if ($field == $value[2]) {
if ($value[1] instanceof Zend_Db_Expr) {
$sqlExpr = true;
$field = $value[1]->__toString();
} elseif (is_object($value[1])) {
$field = $value[1]->__toString();
$simpleField = true;
} else {
$field = $value[0] . '.' . $value[1];
}
break;
} elseif ($field == $value[0]) {
$field = $value[0] . '.' . $value[1];
break;
} elseif ($value[1] instanceof Zend_Db_Expr && $completeField['field'] == $value[1]->__toString()) {
$sqlExpr = true;
$field = $completeField['field'];
break;
}
}
if (!$sqlExpr && strpos($field, '.') === false && $simpleField === false) {
$field = $completeField['field'];
}
/**
* Reserved words from myslq dont contain any special charaters.
* But select expressions may.
*
* SELECT IF(City.Population>500000,1,0)....
*
* We can not quoteIdentifier this fields...
*
*/
// TODO expressions should always be passed as Zend_Db_Expr, then this should be not needed
if (!$sqlExpr && strpos($field, '(') !== false) {
$field = $this->_getDb()->quoteIdentifier($field);
}
$func = 'where';
// TODO having should be used only on fields declared in GROUP BY part
/*if ($sqlExpr || strpos($field, '(') !== false) {
$func = 'having';
}*/
switch ($op) {
case 'sqlexp':
$this->_select->{$func}(new Zend_Db_Expr($filter));
break;
case 'empty':
$this->_select->{$func}($field . " = '' ");
break;
case 'isnull':
$this->_select->{$func}(new Zend_Db_Expr($field . ' IS NULL '));
break;
case 'isnotnull':
$this->_select->{$func}(new Zend_Db_Expr($field . ' IS NOT NULL '));
break;
case 'equal':
case '=':
$this->_select->{$func}($field . ' = ?', $filter);
break;
case 'rege':
$this->_select->{$func}($field . " REGEXP " . $this->_getDb()->quote($filter));
break;
case 'rlike':
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote($filter . "%"));
break;
case 'llike':
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter));
break;
case '>=':
$this->_select->{$func}($field . " >= ?", $filter);
break;
case '>':
$this->_select->{$func}($field . " > ?", $filter);
break;
case '<>':
case '!=':
$this->_select->{$func}($field . " <> ?", $filter);
break;
case '<=':
$this->_select->{$func}($field . " <= ?", $filter);
break;
case '<':
$this->_select->{$func}($field . " < ?", $filter);
break;
case 'in':
$filter = explode(',', $filter);
$this->_select->{$func}($field . " IN (?)", $filter);
break;
case 'flag':
$this->_select->{$func}($field . " & ? <> 0", $filter);
break;
case '||':
$this->_select->orWhere($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%"));
break;
case 'range':
case '&':
case 'and':
$start = substr($filter, 0, strpos($filter, '<>'));
$end = substr($filter, strpos($filter, '<>') + 2);
$this->_select->{$func}($field . " between " . $this->_getDb()->quote($start) . " and " . $this->_getDb()->quote($end));
break;
case 'like':
default:
$this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%"));
break;
}
}
/**
* Applies the given array of where statements to the given select
*/
public function applyWhereToSelect($where, Zend_Db_Select $select)
{
foreach ($where as $field => $value) {
if ($value instanceof Zend_Db_Expr && is_int($field)) {
$select->where($value);
} else {
if (is_string($field) && is_array($value)) {
// we have an in clause
$in = '';
$sep = '';
foreach ($value as $val) {
$in .= $sep . $this->proxied->quote($val);
$sep = ',';
}
$fieldVal = new Zend_Db_Expr($field . ' in (' . $in . ')');
$select->where($fieldVal);
} else {
if (strpos(mb_strtolower($field), 'or ') === 0) {
$field = substr($field, 3);
$select->orWhere($field . ' ?', $value);
} else {
$select->where($field . ' ?', $value);
}
}
}
}
return $select;
}
