/**
* 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);
}
/**
* Sets the total row count, either directly or through a supplied
* query. Without setting this, {@link getPages()} selects the count
* as a subquery (SELECT COUNT ... FROM (SELECT ...)). While this
* yields an accurate count even with queries containing clauses like
* LIMIT, it can be slow in some circumstances. For example, in MySQL,
* subqueries are generally slow when using the InnoDB storage engine.
* Users are therefore encouraged to profile their queries to find
* the solution that best meets their needs.
*
* @param Zend_Db_Select|integer $totalRowCount Total row count integer
* or query
* @return Zend_Paginator_Adapter_DbSelect $this
* @throws Zend_Paginator_Exception
*/
public function setRowCount($rowCount)
{
if ($rowCount instanceof Zend_Db_Select) {
$columns = $rowCount->getPart(Zend_Db_Select::COLUMNS);
$countColumnPart = empty($columns[0][2]) ? $columns[0][1] : $columns[0][2];
if ($countColumnPart instanceof Zend_Db_Expr) {
$countColumnPart = $countColumnPart->__toString();
}
$rowCountColumn = $this->_select->getAdapter()->foldCase(self::ROW_COUNT_COLUMN);
// The select query can contain only one column, which should be the row count column
if (false === strpos($countColumnPart, $rowCountColumn)) {
/**
* @see Zend_Paginator_Exception
*/
//require_once 'Zend/Paginator/Exception.php';
throw new Zend_Paginator_Exception('Row count column not found');
}
$result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch();
$this->_rowCount = count($result) > 0 ? $result[$rowCountColumn] : 0;
} else {
if (is_integer($rowCount)) {
$this->_rowCount = $rowCount;
} else {
/**
* @see Zend_Paginator_Exception
*/
//require_once 'Zend/Paginator/Exception.php';
throw new Zend_Paginator_Exception('Invalid row count');
}
}
return $this;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return Zend_Db_Select
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!isset($this->_attributesCache[$attributeCode])) {
$this->_loadAttribute($attributeCode);
}
$attribute = $this->_attributesCache[$attributeCode];
if (!$this->_select instanceof Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('main_table.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'main_table.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return Zend_Db_Select
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = Mage::getSingleton('catalog/product')->getResource()->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType());
}
$attribute = $this->_attributesCache[$attributeCode];
if (!$this->_select instanceof Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'e.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* 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;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Zend_Db_Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
$attribute = $this->_getAttribute($attributeCode);
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_joinAttribute($storeId, $attributeCode);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
public function getConnectionId()
{
if ($this->_server == 'mysql') {
return $this->_select->getAdapter()->fetchOne('SELECT CONNECTION_ID();');
}
return 0;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Zend_Db_Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Zend_Db_Select) {
return false;
}
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = $this->_categoryResource->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
}
$attribute = $this->_attributesCache[$attributeCode];
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Executes the current query and returns an associative array of results
*
* @return array
*/
public function execute()
{
$this->_prepareExecute();
if ($this->_cache['use'] == 1) {
$hash = 'Bvb_Grid' . md5($this->_select->__toString());
if (!($result = $this->_cache['instance']->load($hash))) {
$final = $this->_select->query(Zend_Db::FETCH_ASSOC);
$result = $final->fetchAll();
$this->_cache['instance']->save($result, $hash, array($this->_cache['tag']));
}
} else {
$final = $this->_select->query(Zend_Db::FETCH_ASSOC);
$result = $final->fetchAll();
if ($this->_server == 'mysql') {
$this->_totalRecords = $this->_select->getAdapter()->fetchOne('select FOUND_ROWS()');
}
}
return $result;
}
/**
* 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);
}
private function setup2(Zend_Db_Select $select, $columes = null)
{
$conn = $select->getAdapter();
$this->setConnection($conn);
$extFields = array();
if ($columes == null) {
$sql = $select->assemble();
$db = $this->conn;
$columes = $this->_getColumns($sql, $db);
}
foreach ($columes as $field) {
$extFields[]['field'] = $field;
}
$this->_setFields($extFields);
$this->strTable = " ( {$sql} ) AS tableName ";
}
/**
* The database adapter used by the model.
*
* @return \Zend_Db_Adapter_Abstract
*/
public function getAdapter()
{
return $this->_select->getAdapter();
}
/**
* Join SELECT to translation table
*
* @param string $language
* @param Zend_Db_Select $select
* @param string $tableName
* @param string $tableAlias
* @param string $tableKey
* @param array $fields
*/
protected function _makeTranslationJoin($language, Zend_Db_Select $select, $tableName, $tableAlias, $tableKey, $fields)
{
$trTableName = $tableName . '_tr';
$trTableAlias = $tableAlias . 't';
$selectFields = array();
foreach ($fields as $field) {
$fieldTr = $field . "_tr";
$selectFields[$fieldTr] = "{$trTableAlias}.{$field}";
}
$select->joinLeft(array($trTableAlias => $trTableName), $select->getAdapter()->quoteInto("{$trTableAlias}.translation_id = {$tableAlias}.{$tableKey} AND {$trTableAlias}.language = ?", $language), $selectFields);
}
/**
* appends container_acl sql
*
* @param Zend_Db_Select $_select
* @param integer $iteration
* @return string table identifier to work on
*/
public static function addGrantsSqlCallback($_select, $iteration)
{
$db = $_select->getAdapter();
$_select->join(array('container_acl' . $iteration => SQL_TABLE_PREFIX . 'container_acl'), $db->quoteIdentifier('container_acl' . $iteration . '.container_id') . ' = ' . $db->quoteIdentifier('container.id'), array());
return 'container_acl' . $iteration;
}
/**
* appends container_acl sql
*
* @param Zend_Db_Select $_select
* @param String $_accountId
* @param Array|String $_grant
* @param String $_aclTableName
* @return void
*/
public static function addGrantsSql($_select, $_accountId, $_grant, $_aclTableName = 'container_acl')
{
$db = $_select->getAdapter();
$grants = is_array($_grant) ? $_grant : array($_grant);
// admin grant includes all other grants
if (!in_array(Tinebase_Model_Grants::GRANT_ADMIN, $grants)) {
$grants[] = Tinebase_Model_Grants::GRANT_ADMIN;
}
// @todo fetch wildcard from specific db adapter
$grants = str_replace('*', '%', $grants);
if (empty($grants)) {
$_select->where('1=0');
return;
}
// @todo add groupmembers via join
$groupMemberships = Tinebase_Group::getInstance()->getGroupMemberships($_accountId);
$quotedActId = $db->quoteIdentifier("{$_aclTableName}.account_id");
$quotedActType = $db->quoteIdentifier("{$_aclTableName}.account_type");
$quotedGrant = $db->quoteIdentifier("{$_aclTableName}.account_grant");
$accountSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$accountSelect->orWhere("{$quotedActId} = ? AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_USER), $_accountId)->orWhere("{$quotedActId} IN (?) AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP), empty($groupMemberships) ? ' ' : $groupMemberships)->orWhere("{$quotedActType} = ?", Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE);
$grantsSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
foreach ($grants as $grant) {
$grantsSelect->orWhere("{$quotedGrant} LIKE ?", $grant);
}
$grantsSelect->appendWhere(Zend_Db_Select::SQL_AND);
$accountSelect->appendWhere(Zend_Db_Select::SQL_AND);
}
private function setup2(Zend_Db_Select $select, $columes = null)
{
$conn = $select->getAdapter();
$this->setConnection($conn);
if ($conn instanceof Zend_Db_Adapter_Pdo_Mysql) {
$this->_drivertype = 'mysql';
}
$extFields = array();
if ($columes == null) {
$sql = $select->assemble();
$db = $this->conn;
if ($db instanceof Zend_Db_Adapter_Sqlsrv) {
$columes = $this->_getSqlsrvColumns($sql, $db);
} elseif ($db instanceof Zend_Db_Adapter_Pdo_Mysql) {
$columes = $this->_getMysqlColumns($sql, $db);
}
}
foreach ($columes as $field) {
$extFields[]['field'] = $field;
}
$this->_setFields($extFields);
$this->strTable = " ( {$sql} ) AS tableName ";
}
