/**
* Render sql select orders
*
* @return Varien_Data_Collection_Db
*/
protected function _renderOrders()
{
$ordersInSelect = $this->_select->getPart(Zend_Db_Select::ORDER);
foreach ($this->_orders as $orderExpr) {
if (!in_array($orderExpr, $ordersInSelect)) {
$this->_select->order($orderExpr);
}
}
return $this;
}
/**
* Setzt die Headerdaten einsprachig entsprechend des
* internen Select-Objekts
*
*/
public function setDefaultHeaderdata()
{
$selectpart = $this->select->getPart(Zend_DB_Select::COLUMNS);
for ($i = 0; $i < count($selectpart); $i++) {
if (($columnname = $selectpart[$i][1]) != '*') {
$defaultHeaderData[] = array('raw' => $columnname, 'lang_xx' => $columnname, 'lang_de' => $columnname, 'lang_en' => $columnname);
} else {
throw new Exception('Das Select-Objekt enthält keine Spaltennamen. Es kann kein Default-Header gesetzt werden.');
}
}
$this->header = $defaultHeaderData;
}
/**
*
* @param \Zend_Db_Select $select
* @param string $name Optiona name
*/
public function __construct(\Zend_Db_Select $select, $name = null)
{
$this->_select = $select;
// Make sure the columns are known to the model
foreach ($select->getPart(\Zend_Db_Select::COLUMNS) as $column) {
if (isset($column[2])) {
$this->set($column[2]);
} elseif (is_string($column[1])) {
$this->set($column[1]);
}
}
if (null === $name) {
$name = 'rnd' . rand(10000, 999999);
}
parent::__construct($name);
}
/**
* Returns a specific record given it's offset/index
* @param integer $offset Offset of record to fetch
*/
public function offsetGet($offset)
{
// PDO does not support scrollable cursor for MySQL, so we can't use the cursor
// and offset parameters of _statement->fetch().
// Therefore, our only real option is to put a limit on the list temporarily and
// then reset it.
$previousLimitCount = $this->_select->getPart(Zend_Db_Select::LIMIT_COUNT);
$previousLimitOffset = $this->_select->getPart(Zend_Db_Select::LIMIT_OFFSET);
$this->limit(1, $offset);
$this->_execute();
$item = $this->_statement->fetch();
$this->reset(Zend_Db_Select::LIMIT_COUNT);
$this->reset(Zend_Db_Select::LIMIT_OFFSET);
$this->limit($previousLimitCount, $previousLimitOffset);
if ($item) {
$class = $this->_class;
return new $class($item, $this->tableName(), $this->_db);
}
return false;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_AccessDenied
*/
public function appendFilterSql($_select, $_backend)
{
$db = $_backend->getAdapter();
if ($this->_requiredGrants === NULL) {
throw new Tinebase_Exception_AccessDenied('No grants have been defined.');
}
// it searches select columns
// return an array where each element has 3 items: 0 - table, 1 - field/expression, 2 - alias
$columns = $_select->getPart(Zend_Db_Select::COLUMNS);
// it gets an array with expressions used in having clause
$grants = array();
foreach ($columns as $column) {
if (!empty($column[2])) {
$grants[$column[2]] = $column[1];
}
}
// it uses into having clause expressions instead of aliases
foreach ($this->_requiredGrants as $grant) {
$_select->orHaving($db->quoteInto($db->quoteIdentifier($grants[$grant]) . ' = ?', 1, Zend_Db::INT_TYPE));
}
}
/**
* Constructor.
*
* @param Zend_Db_Select $select The select query
*/
public function __construct(Zend_Db_Select $select, $sphinxQuery, $sphinxTableName = null)
{
$this->_select = $select;
$this->_sphinxQuery = $sphinxQuery;
$from = $select->getPart(Zend_Db_Select::FROM);
if (null !== $sphinxTableName) {
$this->_sphinxTableName = $sphinxTableName;
} else {
foreach ($from as $table => $opts) {
if (strpos($table, 'sphinx') !== false) {
$this->_sphinxTableName = $table;
break;
}
}
if (null === $this->_sphinxTableName) {
throw new Zend_Paginator_Exception('Could not detect sphinx table name in select');
}
}
if (!array_key_exists($this->_sphinxTableName, $from)) {
throw new Zend_Paginator_Exception('Select must contain a table/alias "' . $this->_sphinxTableName . '"');
}
}
/**
* Join the specified groupscatalog index table to the passed select instance
*
* @param Zend_Db_Select $select
* @param string $table The groupscatalog index table
* @param int $groupId
* @param int $storeId
* @param string $entityField The entity table column where the product or category id is stored
* @return void
*/
protected function _addGroupsCatalogFilterToSelect(Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = 'e.entity_id')
{
// NOTE to self:
// Using joinTable() seems to trigger an exception for some users that I can't reproduce (so far).
// It is related to the flat catalog (Mage_Catalog_Model_Resource_Category_Flat_Collection missing
// joinTable()). Using getSelect()->joinInner() to work around this issue.
/*
$collection->joinTable(
$helper->getIndexTableByEntityType($entityType), // table
"catalog_entity_id=entity_id", // primary bind
array('group_id' => 'group_id', 'store_id' => 'store_id'), // alias to field mappings for the bind cond.
array( // additional bind conditions (see mappings above)
'group_id' => $groupId,
'store_id' => $collection->getStoreId(),
),
'inner' // join type
);
*/
// Avoid double joins for the wishlist collection.
// They clone and clear() the collection each time, but the joins on the
// collections select objects persist. This is more reliable then setting
// a flag on the collection object.
foreach ($select->getPart(Zend_Db_Select::FROM) as $joinedTable) {
if ($joinedTable['tableName'] == $table) {
// filter join already applied
return;
}
}
$select->joinInner($table, "{$table}.catalog_entity_id={$entityField} AND " . $this->_getReadAdapter()->quoteInto("{$table}.group_id=? AND ", $groupId) . $this->_getReadAdapter()->quoteInto("{$table}.store_id=?", $storeId), array());
}
protected static final function _buildPerPageCacheKey(Zend_Db_Select $select)
{
$cacheKey = sha1(serialize(array($select->getPart(Zend_Db_Select::COLUMNS), $select->getPart(Zend_Db_Select::FROM), $select->getPart(Zend_Db_Select::WHERE), $select->getPart(Zend_Db_Select::GROUP), $select->getPart(Zend_Db_Select::HAVING), $select->getPart(Zend_Db_Select::ORDER))));
return $cacheKey;
}
protected function _prepareSelect(Zend_Db_Select $select)
{
$fromPart = $select->getPart(Zend_Db_Select::FROM);
// the recipient table is required!
if (!isset($fromPart['recipient'])) {
return false;
}
foreach ($this->_where as $where) {
$select->where($where);
}
$select->columns($this->_columns, 'recipient');
return true;
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param string $order
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
$order = 'l.' . $field . ' ' . $arrOrder[1];
}
} else {
$order = 'l.' . $order;
}
}
$select->order($order);
return $select;
}
/**
* WARNING: Only columns copying supported.
* @param Lib_Model_Db_Mysql $model
* @param string|null $thisKeyCol may contain table prefix or not
* @param string|null $thatKeyCol may contain table prefix or not
* @param string $conditions
* @return $this
*/
public function joinFrom($model, $thisKeyCol, $thatKeyCol = null, $conditions = '')
{
if ($thatKeyCol === null) {
debug_assert($thisKeyCol !== null);
$thatKeyCol = $model->getPrimaryKey();
} elseif ($thisKeyCol === null) {
debug_assert($thatKeyCol !== null);
$thisKeyCol = $this->getPrimaryKey();
}
$this->prefixColumn($model, $thatKeyCol);
$this->prefixColumn($this, $thisKeyCol);
$conditions = str_replace('{that}', $model->getAlias(), $conditions);
$conditions = str_replace('{this}', $this->getAlias(), $conditions);
$this->mapPartWhere($this->addAliasToConditionDg());
$model->mapPartWhere($model->addAliasToConditionDg());
$thisFrom = $this->_select->getPart(Zend_Db_Select::FROM);
$modelColumns = array_chain($model->_select->getPart(Zend_Db_Select::COLUMNS), array_group_dg(array_get_dg(return_dg(0))), array_map_val_dg(array_chain_dg(array_map_val_dg(function ($descriptor) {
return null === $descriptor[2] ? $descriptor[1] : [$descriptor[2] => $descriptor[1]];
}), function ($columns) {
$outArray = [];
array_map_val($columns, function ($column) use(&$outArray) {
if (is_array($column)) {
array_map_val($column, function ($column, $alias) use(&$outArray) {
$outArray[$alias] = $column;
});
} else {
$outArray[] = $column;
}
});
return $outArray;
})));
array_each($model->_select->getPart(Zend_Db_Select::FROM), function ($descriptor, $alias) use($modelColumns, $thisFrom, $model, $thisKeyCol, $thatKeyCol, $conditions) {
debug_enforce(!array_key_exists($alias, $thisFrom), "Alias `{$alias}` already used for table `{$descriptor['tableName']}`");
switch ($descriptor['joinType']) {
case Zend_Db_Select::FROM:
$this->_select->joinLeft([$model->getAlias() => $model->getTable()], "{$thisKeyCol}={$thatKeyCol} " . $conditions, array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::INNER_JOIN:
$this->_select->joinInner([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::LEFT_JOIN:
$this->_select->joinLeft([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::RIGHT_JOIN:
$this->_select->joinRight([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::FULL_JOIN:
$this->_select->joinFull([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::CROSS_JOIN:
$this->_select->joinCross([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::NATURAL_JOIN:
$this->_select->joinNatural([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
default:
debug_assert(false, "Unknown join type " . var_dump_human_compact($descriptor['joinType']));
break;
}
});
$this->settingsJoin($model);
return $this;
}
protected function _modifySearchQuery(Zend_Db_Select &$select, $model)
{
$where = $select->getPart(Zend_Db_Select::WHERE);
if (!$where) {
return;
}
$select->reset(Zend_Db_Select::WHERE);
foreach ($where as $clause) {
// Check if it's a search query
if (stripos($clause, 'like') !== false) {
preg_match('/%.*?%/', $clause, $matches);
if (!empty($matches[0])) {
$clause = $this->_cleanClause($clause);
$clause .= ' OR ' . $this->_joinCmsSearchQuery($model, $select, $matches[0]);
}
}
// re-attach clause
$whereBoolType = $this->_determineAndOrOr($clause);
$clause = preg_replace('/(^OR|^AND)/', '', $clause);
$clause = $this->_cleanClause($clause);
if ($whereBoolType === 'OR') {
$select->orWhere($clause);
continue;
}
$select->where($clause);
}
}
/**
* Extracts the current table's alias from a composed
* query.
*
* @param Zend_Db_Select $select
* @access protected
* @return string
*/
protected function _extractTableAlias($select)
{
$parts = $select->getPart('from');
foreach ($parts as $alias => $part) {
if ($part['tableName'] == $this->_name) {
return $alias;
}
}
return $this->_name;
}
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;
}
/**
* @param Zend_Db_Select $select
* @return string
*/
protected function _getMainTableAliasFromSelect(Zend_Db_Select $select)
{
$tables = $select->getPart(Zend_Db_Select::FROM);
return array_key_exists('main_table', $tables) ? 'main_table' : 'e';
}
/**
* 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);
}
/**
* Modify the order clause to reflect the record's weight
* @param Zend_Db_Select $select
*/
public function addOrderClause(Zend_Db_Select &$select)
{
// Distill the WHERE class from the Select object
$where = $select->getPart(Zend_Db_Select::WHERE);
// Save the existing ORDER clause.
$originalOrder = $select->getPart(Zend_Db_Select::ORDER);
$select->reset(Zend_Db_Select::ORDER);
$alias = '';
if ($this->_modelAlias) {
$alias = $this->_modelAlias . '.';
}
/**
* If a registered foreign key (see self::_relationConfig) is found, this query is
* considered to be a related fetch() command, and an ORDER BY clause is added with
* the registered weight column.
*/
foreach ($where as $w) {
foreach ($this->_relationConfig as $model => $modelRelationConfig) {
if (strpos($w, $modelRelationConfig[self::FOREIGN_KEY_COLUMN_KEY]) !== false) {
$select->order($alias . $modelRelationConfig[self::WEIGHT_COLUMN_KEY] . ' DESC');
}
}
}
// Return the existing ORDER clause, only this time '<weight-column> DESC' will be in front of it
foreach ($originalOrder as $order) {
// [0] = column, [1] = direction
if (is_array($order)) {
$order = $order[0] . ' ' . $order[1];
}
$select->order($order);
}
}
/**
* Get an alias for a table in a database query
*
* @param Zend_Db_Select $select
* @param string $table
*
* @return string
*/
static function getAliasForTable($select, $table)
{
$aliasTable = '';
//---------------
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $table) {
$aliasTable = $alias;
}
}
return $aliasTable;
}
/**
* add to/cc/bcc and flags custom filters
*
* @param Zend_Db_Select $_select
* @param Felamimail_Backend_Cache_Sql_Message $_backend
* @param array $_filterData
* @return void
*/
protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData)
{
$db = $_backend->getAdapter();
$foreignTables = $_backend->getForeignTables();
// add conditions
$tablename = $foreignTables[$_filterData['field']]['table'];
if ($_filterData['field'] !== 'flags') {
$fieldName = $tablename . '.name';
$fieldEmail = $tablename . '.email';
}
// add filter value
if (!is_array($_filterData['value'])) {
$value = '%' . $_filterData['value'] . '%';
} else {
$value = array();
foreach ((array) $_filterData['value'] as $customValue) {
$value[] = '%' . $customValue . '%';
}
}
if ($_filterData['field'] == 'flags') {
$columns = $_select->getPart(Zend_Db_Select::COLUMNS);
$flags = '';
foreach ($columns as $column) {
if ($column[2] == 'flags') {
$flags = '(' . $column[1] . ')';
break;
}
}
if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') {
$_select->having($db->quoteInto($flags . ' LIKE ?', $value));
} else {
if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') {
if (empty($value)) {
$whereString = $flags . ' IS NULL';
} else {
$value = (array) $value;
$where = array();
$op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE';
$opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND ';
foreach ($value as $flag) {
$where[] = $db->quoteInto($flags . ' ' . $op . ' ?', $flag);
}
$whereString = implode($opImplode, $where);
if ($_filterData['operator'] == 'notin') {
$whereString = '(' . $whereString . ') OR ' . $flags . ' IS NULL';
}
}
$_select->having($whereString);
} else {
$_select->having($db->quoteInto($flags . ' NOT LIKE ? OR ' . $flags . ' IS NULL', $value));
}
}
} else {
$_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value));
}
}
/**
*
* Public service for grouping treatment
* @param Zend_Db_Adapter $adapter
* @param string $tablePrefix
* @param Zend_Db_Select $select
*/
public static function traitGroup($adapter, $tablePrefix, Zend_Db_Select $select)
{
$group = $select->getPart(Zend_Db_Select::GROUP);
//if (empty($group)) return;
$order = $select->getPart(Zend_Db_Select::ORDER);
$columns = $select->getPart(Zend_Db_Select::COLUMNS);
try {
//$column is an array where 0 is table, 1 is field and 2 is alias
foreach ($columns as $column) {
$field = implode('.', $column);
if (!in_array($field, $group)) {
// replaces * by each name of column
if ($column[1] == '*') {
$tableFields = $adapter->describeTable($tablePrefix . $column[0]);
foreach ($tableFields as $columnName => $schema) {
// adds columns into group by clause (table.field)
// checks if field has a function (that must be an aggregation)
$element = "{$column[0]}.{$columnName}";
if (!in_array($element, $group) && !preg_match('/\\(.*\\)/', $element)) {
$group[] = $element;
}
}
} else {
// adds column into group by clause (table.field)
$element = "{$column[0]}.{$column[1]}";
if (!preg_match('/\\(.*\\)/', $element)) {
$group[] = $element;
} else {
if (substr($column[1], 0, 10) == "(CASE WHEN") {
$group[] = $column[1];
}
}
}
}
}
foreach ($order as $column) {
$field = $column[0];
if (preg_match('/.*\\..*/', $field) && !in_array($field, $group)) {
// adds column into group by clause (table.field)
$group[] = $field;
}
}
$select->reset(Zend_Db_Select::GROUP);
$select->group($group);
} catch (Exception $e) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Exception: ' . $e->getMessage() . ' Trace: ' . $e->getTraceAsString());
}
return $select;
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param array $options
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
// Присоединим таблицу
$joinTable = $joinTableForSort;
switch ($joinTable) {
case 'blog_info_profile':
$select->joinInner(array('i_profile' => $joinTable), 'i_profile.info_id = i.id', array())->where('i_profile.profile_key = ?', $field);
$order = 'i_profile.profile_value ' . $arrOrder[1];
break;
default:
$order = 'i.' . $field . ' ' . $arrOrder[1];
break;
}
}
} else {
$order = 'i.' . $order;
}
}
$select->order($order);
return $select;
}
/**
* Public service for grouping treatment
*
* @param Zend_Db_Select $select
*/
public static function traitGroup(Zend_Db_Select $select)
{
// not needed for MySQL backends
if ($select->getAdapter() instanceof Zend_Db_Adapter_Pdo_Mysql) {
return;
}
$group = $select->getPart(Zend_Db_Select::GROUP);
if (empty($group)) {
return;
}
$columns = $select->getPart(Zend_Db_Select::COLUMNS);
$updatedColumns = array();
//$column is an array where 0 is table, 1 is field and 2 is alias
foreach ($columns as $key => $column) {
if ($column[1] instanceof Zend_Db_Expr) {
if (preg_match('/^\\(.*\\)/', $column[1])) {
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
} else {
$updatedColumns[] = $column;
}
continue;
}
if (preg_match('/^\\(.*\\)/', $column[1])) {
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
continue;
}
// resolve * to single columns
if ($column[1] == '*') {
$tableFields = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $column[0], $select->getAdapter());
foreach ($tableFields as $columnName => $schema) {
// adds columns into group by clause (table.field)
// checks if field has a function (that must be an aggregation)
$fieldName = "{$column[0]}.{$columnName}";
if (in_array($fieldName, $group)) {
$updatedColumns[] = array($column[0], $fieldName, $columnName);
} else {
// any selected field which is not in the group by clause must have an aggregate function
// we choose MIN() as default. In practice the affected columns will have only one value anyways.
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $columnName);
}
}
continue;
}
$fieldName = $column[0] . '.' . $column[1];
if (in_array($fieldName, $group)) {
$updatedColumns[] = $column;
} else {
// any selected field which is not in the group by clause must have an aggregate function
// we choose MIN() as default. In practice the affected columns will have only one value anyways.
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $column[2] ? $column[2] : $column[1]);
}
}
$select->reset(Zend_Db_Select::COLUMNS);
foreach ($updatedColumns as $column) {
$select->columns(!empty($column[2]) ? array($column[2] => $column[1]) : $column[1], $column[0]);
}
// add order by columns to group by
$order = $select->getPart(Zend_Db_Select::ORDER);
foreach ($order as $column) {
$field = $column[0];
if (preg_match('/.*\\..*/', $field) && !in_array($field, $group)) {
// adds column into group by clause (table.field)
$group[] = $field;
}
}
$select->reset(Zend_Db_Select::GROUP);
$select->group($group);
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param string $order
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
// Присоединим таблицу
$joinTable = $joinTableForSort;
switch ($joinTable) {
case 'blog_posts_profile':
$select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field);
$order = 'p_profile.profile_value ' . $arrOrder[1];
break;
case 'blog_posts_images':
$select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array());
$order = 'img.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_audio':
$select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array());
$order = 'au.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_video':
$select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array());
$order = 'v.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_lacations':
$select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array());
$order = 'l.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_tags':
$select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array());
$order = 't.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_comments':
$select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array());
$order = 'c.' . $field . ' ' . $arrOrder[1];
break;
case 'users':
$select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array());
$order = 'u.' . $field . ' ' . $arrOrder[1];
break;
case 'users_profile':
$select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field);
$order = 'u_profile.profile_value ' . $arrOrder[1];
break;
default:
$order = 'p.' . $field . ' ' . $arrOrder[1];
break;
}
}
} else {
$order = 'p.' . $order;
}
}
$select->order($order);
return $select;
}
public static function fetchAll(Zend_Db_Select $select, &$count = null)
{
self::init();
$result = false;
try {
$limit = $select->getPart(Zend_Db_Select::LIMIT_COUNT);
$offset = $select->getPart(Zend_Db_Select::LIMIT_OFFSET);
$result = $select->query()->fetchAll();
if ($count !== null) {
$count = self::getCount($select);
}
} catch (Exception $e) {
$errors = PR_Api_Error::getInstance();
$errors->addError(7, 'SQL Error: ' . $select . " " . $e);
error_log($e);
$result = false;
}
return $result;
}
/**
* Returns current columns in use
*
* @return Zend_Db_Select
*/
public function getColumns()
{
return $this->_select->getPart('columns');
}
/**
* Retrieve ID field from entity collection
*
* @param Varien_Data_Collection_Db $collection
* @return string
*/
public function getTableAlias(Zend_Db_Select $select)
{
foreach ($select->getPart(Zend_Db_Select::FROM) as $alias => $table) {
if ($table['joinType'] === Zend_Db_Select::FROM) {
return $alias;
}
}
return null;
}
protected function _prepareSelect(Zend_Db_Select $select)
{
$fromPart = $select->getPart(Zend_Db_Select::FROM);
// the recipient table is required!
if (!isset($fromPart['recipient'])) {
return false;
}
foreach ($this->_where as $where) {
$select->where($where);
}
/*
$select->where('`recipient`.`sent_at` IS NOT NULL');
if($this->_campaignFilter !== null) {
$select->where('`recipient`.`campaign_id` = ?', $this->_campaignFilter);
}
if($this->_variationFilter !== null) {
$select->where('`recipient`.`variation_id` = ?', $this->_variationFilter);
}
*/
$select->columns($this->_columns, 'recipient');
return true;
}
