/** * * @param Zend_Db_Select $select * @param int $store_id * @return Zend_Db_Adapter_Mysqli */ public function addVisibileFilterToCR(&$select, $store_id = 0) { if ($this->getAttribute()->isScopeGlobal()) { $tableName = $this->getAttribute()->getAttributeCode() . '_t'; $select->joinLeft(array($tableName => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$tableName}`.`entity_id`" . " AND `{$tableName}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$tableName}`.`store_id`='0'", array()); $valueExpr = $tableName . '.value'; } else { $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1'; $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2'; $select->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$valueTable1}`.`entity_id`" . " AND `{$valueTable1}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable1}`.`store_id`='0'", array())->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$valueTable2}`.`entity_id`" . " AND `{$valueTable2}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable2}`.`store_id`='{$store_id}'", array()); $valueExpr = new Zend_Db_Expr("IF(`{$valueTable2}`.`value_id`>0, `{$valueTable2}`.`value`, `{$valueTable1}`.`value`)"); } $select->where($valueExpr . " IN (?)", $this->getVisibleInSiteIds()); 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])) { $attribute = Mage::getSingleton('catalog/category')->getResource()->getAttribute($attributeCode); $this->_attributesCache[$attributeCode] = array('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]; 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 { $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('IFNULL(t2_' . $attributeCode . '.value, t1_' . $attributeCode . '.value)' . $conditionRule, $value); } } return $this->_select; }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { $correlationName = Tinebase_Record_Abstract::generateUID(30); $db = $_backend->getAdapter(); $_select->joinLeft(array($correlationName => $db->table_prefix . 'adb_list_m_role'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array()); $_select->where($db->quoteIdentifier($correlationName . '.list_role_id') . ' IN (?)', (array) $this->_value); }
/** * 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; }
/** * 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; }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_UnexpectedValue */ public function appendFilterSql($_select, $_backend) { // don't take empty filter into account if (empty($this->_value) || !is_array($this->_value) || !isset($this->_value['cfId']) || empty($this->_value['cfId']) || !isset($this->_value['value'])) { return; } else { if ($this->_operator == 'in') { throw new Tinebase_Exception_UnexpectedValue('Operator "in" not supported.'); } } // make sure $correlationName is a string $correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value['cfId'] . 'cf'; if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Adding custom field filter: ' . print_r($this->_value, true)); } $db = Tinebase_Core::getDb(); $idProperty = $db->quoteIdentifier($this->_options['idProperty']); // per left join we add a customfield column named as the customfield and filter this joined column // NOTE: we name the column we join like the customfield, to be able to join multiple customfield criteria (multiple invocations of this function) $what = array($correlationName => SQL_TABLE_PREFIX . 'customfield'); $on = $db->quoteIdentifier("{$correlationName}.record_id") . " = {$idProperty} AND " . $db->quoteIdentifier("{$correlationName}.customfield_id") . " = " . $db->quote($this->_value['cfId']); $_select->joinLeft($what, $on, array()); $valueIdentifier = $db->quoteIdentifier("{$correlationName}.value"); if ($this->_value['value'] === '') { $where = $db->quoteInto($valueIdentifier . ' IS NULL OR ' . $valueIdentifier . ' = ?', $this->_value['value']); } else { $value = $this->_replaceWildcards($this->_value['value']); $where = $db->quoteInto($valueIdentifier . $this->_opSqlMap[$this->_operator]['sqlop'], $value); } $_select->where($where . ' /* add cf filter */'); }
public function getSelectFilesByJobId($jobid) { // do Bacula ACLs Zend_Loader::loadClass('Job'); $table = new Job(); if (!$table->isJobIdExists($jobid)) { return FALSE; } // !!! IMPORTANT !!! с Zend Paginator нельзя использовать DISTINCT иначе не работает в PDO_PGSQL $select = new Zend_Db_Select($this->db); $select->from(array('f' => 'File'), array('FileId', 'FileIndex', 'LStat')); $select->joinLeft(array('p' => 'Path'), 'f.PathId = p.PathId', array('Path')); $select->joinLeft(array('n' => 'Filename'), 'f.FileNameId = n.FileNameId', array('Name')); $select->where("f.JobId = ?", $jobid); $select->order(array('f.FileIndex', 'f.FileId')); return $select; }
/** * Join attribute by code * * @param int $storeId * @param string $attributeCode */ protected function _joinAttribute($storeId, $attributeCode) { $adapter = $this->getReadConnection(); $attribute = $this->_getAttribute($attributeCode); $this->_select->joinLeft(array('t1_' . $attributeCode => $attribute['table']), 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $adapter->quoteInto(' t1_' . $attributeCode . '.store_id = ?', Mage_Core_Model_App::ADMIN_STORE_ID) . $adapter->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), array()); if (!$attribute['is_global']) { $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()); } }
/** * Join attribute by code * * @param int $storeId * @param string $attributeCode * @return void */ protected function _joinAttribute($storeId, $attributeCode) { $adapter = $this->getReadConnection(); $attribute = $this->_getAttribute($attributeCode); $this->_select->joinLeft(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $adapter->quoteInto(' t1_' . $attributeCode . '.store_id = ?', \Magento\Store\Model\Store::DEFAULT_STORE_ID) . $adapter->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), []); if (!$attribute['is_global']) { $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), []); } }
/** * 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) { $db = $_backend->getAdapter(); $correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value . 'owner'; $_select->joinLeft(array($correlationName => SQL_TABLE_PREFIX . 'container_acl'), $db->quoteIdentifier("{$correlationName}.container_id") . " = container.id", array()); // only personal containers have an owner! $_select->where("{$db->quoteIdentifier('container.type')} = ?", Tinebase_Model_Container::TYPE_PERSONAL); // assure admin grant $_select->where($db->quoteIdentifier("{$correlationName}.account_id") . " = " . $db->quote($this->_value) . ' AND ' . $db->quoteIdentifier("{$correlationName}.account_grant") . " = ?", Tinebase_Model_Grants::GRANT_ADMIN); }
/** * Get Client name * * @return Client name, or "" if not exist * @param integer $jobid */ function getClientName($jobid) { $select = new Zend_Db_Select($this->db); $select->from(array('j' => 'Job'), array('JobId', 'ClientId')); $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('c.Name')); $select->where("j.JobId = ?", $jobid); //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!! $stmt = $select->query(); $res = $stmt->fetchAll(); return $res[0]['name']; }
/** * Join Left * * Joins a table, without selecting any columns by default and allowing MySQL table alias syntax * * @access public * @param array|string|Zend_Db_Expr $name * @param string $cond * @param array|string $cols * @param string $schema * @return $this - Chainable. */ public function joinLeft($name, $cond, $cols = array(), $schema = null) { $this->_joinHistory[] = array('function' => 'joinLeft', 'args' => func_get_args()); if (is_string($name) && strpos($name, ' ')) { list($table, $alias) = explode(' ', $name); $name = array($alias => $table); } $this->_isJoined = true; $this->_select->joinLeft($name, $cond, $cols, $schema); $this->_count = false; return $this; }
public function fetchAllUsers($order = 'id') { $select = new Zend_Db_Select($this->db); $select->from(array('user1' => 'webacula_users'), array('id', 'login', 'name', 'email', 'create_login', 'last_login', 'last_ip', 'active', 'role_id')); $select->joinLeft(array('role1' => 'webacula_roles'), 'user1.role_id = role1.id', array('role_name' => 'name', 'role_id' => 'id')); if ($order) { $select->order(array($order . ' ASC')); } //$sql = $select->__toString(); var_dump($sql); exit; // for !!!debug!!! $stmt = $select->query(); $result = $stmt->fetchAll(); return $result; }
/** * Get info Volumes with Status of media: Disabled, Error * */ function getProblemVolumes($order = null) { $db = Zend_Registry::get('db_bacula'); // make select from multiple tables $select = new Zend_Db_Select($db); $select->distinct(); $select->from(array('m' => 'Media'), array("MediaId", 'PoolId', 'StorageId', 'VolumeName', 'VolStatus', 'VolBytes', 'MaxVolBytes', 'VolJobs', 'VolRetention', 'Recycle', 'Slot', 'InChanger', 'MediaType', 'FirstWritten', 'LastWritten')); $select->joinLeft(array('p' => 'Pool'), 'm.PoolId = p.PoolId', array('PoolName' => 'p.Name')); $select->where("VolStatus IN ('Error', 'Disabled')"); //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!! $result = $select->query()->fetchAll(null, $order); // do Bacula ACLs return $this->bacula_acl->doBaculaAcl($result, 'poolname', 'pool'); }
public function fetchAllRoles() { /* SELECT roles.id, roles.name, inherits.name AS inherit_name FROM webacula_roles AS roles LEFT JOIN webacula_roles AS inherits ON inherits.id = roles.inherit_id ORDER BY roles.inherit_id, roles.order_role ASC */ $select = new Zend_Db_Select($this->db); $select->from(array('roles' => 'webacula_roles'), array('id', 'name', 'description', 'order_role', 'inherit_id')); $select->joinLeft(array('inherits' => 'webacula_roles'), 'inherits.id = roles.inherit_id', array('inherit_name' => 'name')); $select->order(array('roles.order_role, roles.id ASC')); //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!! $stmt = $select->query(); return $stmt->fetchAll(); }
/** * 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) { // don't take empty tag filter into account if (empty($this->_value)) { if ($this->_operator === 'in') { $_select->where('1=0'); } return; } // check the view right of the tag (throws Exception if not accessable) Tinebase_Tags::getInstance()->getTagsById($this->_value); $db = Tinebase_Core::getDb(); $idProperty = $db->quoteIdentifier($this->_options['idProperty']); $app = Tinebase_Application::getInstance()->getApplicationByName($this->_options['applicationName']); $correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value . 'tag'; // per left join we add a tag column named as the tag and filter this joined column // NOTE: we name the column we join like the tag, to be able to join multiple tag criteria (multiple invocations of this function) $_select->joinLeft(array($correlationName => SQL_TABLE_PREFIX . 'tagging'), $db->quoteIdentifier("{$correlationName}.record_id") . " = {$idProperty} " . " AND " . $db->quoteIdentifier("{$correlationName}.application_id") . " = " . $db->quote($app->getId()) . " AND " . $db->quoteInto($db->quoteIdentifier("{$correlationName}.tag_id") . " IN (?)", (array) $this->_value), array()); $_select->where($db->quoteIdentifier("{$correlationName}.tag_id") . $this->_opSqlMap[$this->_operator]['sqlop']); }
/** * 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()); } }
/** * _addAclJoins * * @param Zend_Db_Select $sql * @param string $alias the name/alias of the main table in the query * @return Zend_Db_Select modified query */ protected function _addAclJoins(Zend_Db_Select $sql, $alias = null, $primaryKey = null) { $roles = Zend_Registry::get('Default_DiContainer')->getUserService()->getIdentity()->getRoles(); if ($alias === null) { $alias = $this->getTableName(); } $table = $this->getTableName(); if ($primaryKey === null) { $primaryKey = $table . '_id'; } $sql->joinLeft('acl_resource_record', "`acl_resource_record`.`resource_type` = '{$table}' AND `acl_resource_record`.`resource_id` = {$alias}.{$primaryKey}", array())->where("(({$alias}.private = ?", 1)->where("acl_resource_record.role_id IN (?))", $roles)->orWhere("{$alias}.private = ?)", 0); return $sql; }
/** * Join virtual grid columns to select * * @param string $mainTableAlias * @param Zend_Db_Select $select * @param array $columnsToSelect * @return Mage_Sales_Model_Resource_Order_Abstract */ public function joinVirtualGridColumnsToSelect($mainTableAlias, Zend_Db_Select $select, &$columnsToSelect) { $adapter = $this->_getWriteAdapter(); foreach ($this->getVirtualGridColumns() as $alias => $expression) { list($table, $joinCondition, $column) = $expression; $tableAlias = 'table_' . $alias; $joinConditionExpr = array(); foreach ($joinCondition as $fkField => $pkField) { $pkField = $adapter->quoteIdentifier($tableAlias . '.' . $pkField); $fkField = $adapter->quoteIdentifier($mainTableAlias . '.' . $fkField); $joinConditionExpr[] = $fkField . '=' . $pkField; } $select->joinLeft(array($tableAlias => $table), implode(' AND ', $joinConditionExpr), array($alias => str_replace('{{table}}', $tableAlias, $column))); $columnsToSelect[] = $alias; } return $this; }
/** * Join an attribute value to a collection * * joinAttribute($select, 'weddingdate', 'customer/weddingdate', 'customer_id') * * @param Zend_Db_Select $select * @param string $alias Alias of the appended column * @param string $attributeCode The name of the attribute * @param string $bind The collection column used to bind the attribute * @param boolean $joinStatic Join attribute even if static * @return Zend_Db_Expr */ public function joinAttribute(Zend_Db_Select $select, $attributeCode, $bind, $alias = null, $joinStatic = false) { $attribute = $this->getAttribute($attributeCode); if (!$attribute) { throw new Exception("Attribute does not exist '{$attributeCode}'"); } if (!$alias) { $alias = $attribute->getAttributeCode(); } $tableAlias = '_table_' . $alias; if (!$attribute->isStatic()) { $select->joinLeft(array($tableAlias => $attribute->getBackendTable()), "(`{$tableAlias}`.`entity_id`={$bind}) AND (`{$tableAlias}`.`attribute_id`={$attribute->getId()})", null); return new Zend_Db_Expr("`{$tableAlias}`.`value`"); } else { if ($joinStatic) { $select->joinLeft(array($tableAlias => $attribute->getBackendTable()), "(`{$tableAlias}`.`entity_id`={$bind})", null); return new Zend_Db_Expr("`{$tableAlias}`.`{$attribute->getName()}`"); } } return new Zend_Db_Expr("`{$attribute->getName()}`"); }
/** * Add variable store and default value to select * * @param Zend_Db_Select $select * @param integer $storeId * @return Mage_Core_Model_Mysql4_Variable */ protected function _addValueToSelect(Zend_Db_Select $select, $storeId = 0) { $select->joinLeft(array('default' => $this->getTable('core/variable_value')), 'default.variable_id = ' . $this->getMainTable() . '.variable_id AND default.store_id = 0', array())->joinLeft(array('store' => $this->getTable('core/variable_value')), 'store.variable_id = default.variable_id AND store.store_id = ' . $storeId, array())->columns(array('plain_value' => new Zend_Db_Expr('IFNULL(store.plain_value, default.plain_value)'), 'html_value' => new Zend_Db_Expr('IFNULL(store.html_value, default.html_value)'), 'store_plain_value' => 'store.plain_value', 'store_html_value' => 'store.html_value')); return $this; }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_UnexpectedValue */ public function appendFilterSql($_select, $_backend) { // don't take empty filter into account if (empty($this->_value) || !is_array($this->_value) || !isset($this->_value['cfId']) || empty($this->_value['cfId']) || !isset($this->_value['value'])) { return; } else { if ($this->_operator == 'in') { throw new Tinebase_Exception_UnexpectedValue('Operator "in" not supported.'); } } // make sure $correlationName is a string $correlationName = Tinebase_Record_Abstract::generateUID(30); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Adding custom field filter: ' . print_r($this->_value, true)); } $db = Tinebase_Core::getDb(); $idProperty = $db->quoteIdentifier($this->_options['idProperty']); // per left join we add a customfield column named as the customfield and filter this joined column // NOTE: we name the column we join like the customfield, to be able to join multiple customfield criteria (multiple invocations of this function) $what = array($correlationName => SQL_TABLE_PREFIX . 'customfield'); $on = $db->quoteIdentifier("{$correlationName}.record_id") . " = {$idProperty} AND " . $db->quoteIdentifier("{$correlationName}.customfield_id") . " = " . $db->quote($this->_value['cfId']); $_select->joinLeft($what, $on, array()); $valueIdentifier = $db->quoteIdentifier("{$correlationName}.value"); switch ($this->_cfRecord->definition['type']) { case 'date': case 'datetime': $customfields = Tinebase_CustomField::getInstance()->search($this->_subFilter); if ($customfields->count()) { $where = $db->quoteInto($idProperty . ' IN (?) ', $customfields->record_id); } else { $where = '1=2'; } break; default: if (!$this->_value['value']) { $where = $db->quoteInto($valueIdentifier . ' IS NULL OR ' . $valueIdentifier . ' = ?', $this->_value['value']); } else { $value = $this->_replaceWildcards($this->_value['value']); if (($this->_cfRecord->definition['type'] == 'keyField' || $this->_cfRecord->definition['type'] == 'record') && $this->_operator == 'not') { $where = $db->quoteInto($valueIdentifier . ' IS NULL OR ' . $valueIdentifier . $this->_opSqlMap[$this->_operator]['sqlop'], $value); } else { $where = $db->quoteInto($valueIdentifier . $this->_opSqlMap[$this->_operator]['sqlop'], $value); } } } $_select->where($where); }
/** * appends effective grant calculation to select object * * @param Zend_Db_Select $_select */ protected function _appendEffectiveGrantCalculationSql($_select, $_attendeeFilters = NULL) { // groupmemberships of current user, needed to compute phys and inherited grants $_select->joinLeft(array('groupmemberships' => $this->_tablePrefix . 'group_members'), $this->_db->quoteInto($this->_db->quoteIdentifier('groupmemberships.account_id') . ' = ?', Tinebase_Core::getUser()->getId()), array()); // attendee joins the attendee we need to compute the curr users effective grants // NOTE: 2010-04 the behaviour changed. Now, only the attendee the client filters for are // taken into account for grants calculation $attendeeWhere = FALSE; if (is_array($_attendeeFilters) && !empty($_attendeeFilters)) { $attendeeSelect = $this->_db->select(); foreach ((array) $_attendeeFilters as $attendeeFilter) { if ($attendeeFilter instanceof Calendar_Model_AttenderFilter) { $attendeeFilter->appendFilterSql($attendeeSelect, $this); } } $whereArray = $attendeeSelect->getPart(Zend_Db_Select::SQL_WHERE); if (!empty($whereArray)) { $attendeeWhere = ' AND ' . Tinebase_Helper::array_value(0, $whereArray); } } $_select->joinLeft(array('attendee' => $this->_tablePrefix . 'cal_attendee'), $this->_db->quoteIdentifier('attendee.cal_event_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.id') . $attendeeWhere, array()); $_select->joinLeft(array('attendeeaccounts' => $this->_tablePrefix . 'accounts'), $this->_db->quoteIdentifier('attendeeaccounts.contact_id') . ' = ' . $this->_db->quoteIdentifier('attendee.user_id') . ' AND (' . $this->_db->quoteInto($this->_db->quoteIdentifier('attendee.user_type') . '= ?', Calendar_Model_Attender::USERTYPE_USER) . ' OR ' . $this->_db->quoteInto($this->_db->quoteIdentifier('attendee.user_type') . '= ?', Calendar_Model_Attender::USERTYPE_GROUPMEMBER) . ')', array()); $_select->joinLeft(array('attendeegroupmemberships' => $this->_tablePrefix . 'group_members'), $this->_db->quoteIdentifier('attendeegroupmemberships.account_id') . ' = ' . $this->_db->quoteIdentifier('attendeeaccounts.contact_id'), array()); $_select->joinLeft(array('dispgrants' => $this->_tablePrefix . 'container_acl'), $this->_db->quoteIdentifier('dispgrants.container_id') . ' = ' . $this->_db->quoteIdentifier('attendee.displaycontainer_id') . ' AND ' . $this->_getContainGrantCondition('dispgrants', 'groupmemberships'), array()); $_select->joinLeft(array('physgrants' => $this->_tablePrefix . 'container_acl'), $this->_db->quoteIdentifier('physgrants.container_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.container_id'), array()); $allGrants = Tinebase_Model_Grants::getAllGrants(); foreach ($allGrants as $grant) { if (in_array($grant, $this->_recordBasedGrants)) { $_select->columns(array($grant => "\n MAX( CASE WHEN ( \n" . ' /* physgrant */' . $this->_getContainGrantCondition('physgrants', 'groupmemberships', $grant) . " OR \n" . ' /* implicit */' . $this->_getImplicitGrantCondition($grant) . " OR \n" . ' /* inherited */' . $this->_getInheritedGrantCondition($grant) . " \n" . ") THEN 1 ELSE 0 END ) ")); } else { $_select->columns(array($grant => "\n MAX( CASE WHEN ( \n" . ' /* physgrant */' . $this->_getContainGrantCondition('physgrants', 'groupmemberships', $grant) . "\n" . ") THEN 1 ELSE 0 END ) ")); } } }
/** * Get category collection array * * @return array */ public function getCollection($storeId, $onlyCount = false, $limit = 4000000000.0, $from = 0) { $products = array(); $store = Mage::app()->getStore($storeId); /* @var $store Mage_Core_Model_Store */ if (!$store) { return false; } $read = $this->_getReadAdapter(); // $useCategories = Mage::getStoreConfigFlag('catalog/seo/product_use_categories'); // $urCondions = array( // 'e.entity_id=ur.product_id', // //'ur.category_id IS ' . ($useCategories ? 'NOT' : '') . ' NULL', // $read->quoteInto('ur.store_id=?', $store->getId()), // $read->quoteInto('ur.is_system=?', 1), // ); $this->_select = $read->select()->distinct()->from(array('e' => $this->getMainTable()), array($onlyCount ? 'COUNT(*)' : $this->getIdFieldName()))->join(array('w' => $this->getTable('catalog/product_website')), 'e.entity_id=w.product_id', array())->where('w.website_id=?', $store->getWebsiteId())->limit($limit, $from); $excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_sitemap'); if ($excludeAttr) { $this->_select->joinLeft(array('exclude_tbl' => $excludeAttr->getBackend()->getTable()), 'exclude_tbl.entity_id = e.entity_id AND exclude_tbl.attribute_id = ' . $excludeAttr->getAttributeId() . ' AND exclude_tbl.store_id = 0', array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL'); } $this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in'); $this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in'); if ($onlyCount) { return $read->fetchOne($this->_select); } $productCanonicalUrl = Mage::getStoreConfig('mageworx_seo/seosuite/product_canonical_url'); // $useLongest = ($productCanonicalUrl==1) ? true : false; if ($productCanonicalUrl == 1) { $sort = 'DESC'; } else { if ($productCanonicalUrl == 2) { $sort = 'ASC'; } else { $sort = ''; } } $canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url'); if ($canonicalAttr) { $this->_select->columns(array('url' => new Zend_Db_Expr("IFNULL((SELECT canonical_url_rewrite.`request_path`\n FROM `" . $canonicalAttr->getBackend()->getTable() . "` AS canonical_path\n LEFT JOIN `" . $this->getTable('core/url_rewrite') . "` AS canonical_url_rewrite ON canonical_url_rewrite.`id_path` = canonical_path.`value`\n WHERE canonical_path.`entity_id` = e.`entity_id` AND canonical_path.`attribute_id` = " . $canonicalAttr->getAttributeId() . ($sort ? " ORDER BY LENGTH(canonical_url_rewrite.`request_path`) " . $sort : "") . " LIMIT 1),\n (SELECT `request_path` \n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1) \n )"))); // $this->_select->joinLeft( // array('ur' => $this->getTable('core/url_rewrite')), // join(' AND ', $urCondions), // array('url' => 'IFNULL(`canonical_url_rewrite`.`request_path`, `ur`.`request_path`)') // ) // ->joinLeft( // array('canonical_path' => $canonicalAttr->getBackend()->getTable()), // 'canonical_path.entity_id = e.entity_id AND canonical_path.attribute_id = ' . $canonicalAttr->getAttributeId() , // array() // ) // ->joinLeft( // array('canonical_url_rewrite' => $this->getTable('core/url_rewrite')), // '`canonical_url_rewrite`.`id_path` = `canonical_path`.value', // array() // ) // ->where('ur.request_path IS NOT NULL OR `canonical_url_rewrite`.`request_path` IS NOT NULL'); } else { $this->_select->columns(array('url' => new Zend_Db_Expr("(SELECT `request_path` \n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)"))); // $this->_select->joinLeft( // array('ur' => $this->getTable('core/url_rewrite')), // join(' AND ', $urCondions), // array('url' => 'ur.request_path') // ) // ->where('ur.request_path IS NOT NULL'); } $crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain'); if ($crossDomainAttr) { $this->_select->joinLeft(array('cross_domain_tbl' => $crossDomainAttr->getBackend()->getTable()), 'cross_domain_tbl.entity_id = e.entity_id AND cross_domain_tbl.attribute_id = ' . $crossDomainAttr->getAttributeId(), array('canonical_cross_domain' => 'cross_domain_tbl.value')); } //echo $this->_select->assemble(); exit; $query = $read->query($this->_select); while ($row = $query->fetch()) { $product = $this->_prepareProduct($row); // if (isset($products[$product->getId()])) { // if (($useLongest && strlen($product->getUrl()) < strlen($products[$product->getId()]->getUrl())) // || (!$useLongest && strlen($product->getUrl()) > strlen($products[$product->getId()]->getUrl()))) { // $product->setUrl($products[$product->getId()]->getUrl()); // } // } if ($productCanonicalUrl == 3) { // use root $urlArr = explode('/', $product->getUrl()); $product->setUrl(end($urlArr)); } $products[$product->getId()] = $product; } return $products; }
/** * Add variable store and default value to select * * @param Zend_Db_Select $select * @param integer $storeId * @return Mage_Core_Model_Resource_Variable */ protected function _addValueToSelect(Zend_Db_Select $select, $storeId = Mage_Core_Model_App::ADMIN_STORE_ID) { $adapter = $this->_getReadAdapter(); $ifNullPlainValue = $adapter->getCheckSql('store.plain_value IS NULL', 'def.plain_value', 'store.plain_value'); $ifNullHtmlValue = $adapter->getCheckSql('store.html_value IS NULL', 'def.html_value', 'store.html_value'); $select->joinLeft(array('def' => $this->getTable('core/variable_value')), 'def.variable_id = ' . $this->getMainTable() . '.variable_id AND def.store_id = 0', array())->joinLeft(array('store' => $this->getTable('core/variable_value')), 'store.variable_id = def.variable_id AND store.store_id = ' . $adapter->quote($storeId), array())->columns(array('plain_value' => $ifNullPlainValue, 'html_value' => $ifNullHtmlValue, 'store_plain_value' => 'store.plain_value', 'store_html_value' => 'store.html_value')); return $this; }
/** * LogBook full text search * */ function findLogBookByText($id_text, $sort_order) { if (!isset($id_text)) { return; } $id_text = trim($id_text); $db = Zend_Db_Table::getAdapter('db_bacula'); $select = new Zend_Db_Select($db); switch ($this->db_adapter) { case 'PDO_MYSQL': $select->distinct(); $select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel')); $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc')); $select->where(' MATCH(logTxt) AGAINST ("' . $id_text . '" WITH QUERY EXPANSION)'); break; case 'PDO_PGSQL': $select->distinct(); $select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel')); $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc')); $str = preg_replace('/\\s+/', ' & ', $id_text); $select->where(" to_tsvector(logtxt) @@ to_tsquery(" . $db->quote($str) . ")"); break; case 'PDO_SQLITE': // see also http://www.sqlite.org/cvstrac/wiki?p=FtsOne "FTS1 module is available in SQLite version 3.3.8 and later $select->distinct(); $select->from(array('l' => 'webacula_logbook'), array('logid' => 'logId', 'logdatecreate' => 'logDateCreate', 'logdatelast' => 'logDateLast', 'logtxt' => 'logTxt', 'logtypeid' => 'logTypeId', 'logisdel' => 'logIsDel')); $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeid' => 'typeId', 'typedesc' => 'typeDesc')); $select->where(' logTxt LIKE "%' . $id_text . '%"'); break; } //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!! $result = $select->query(); return $result; }
* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0) */ $installer = $this; /* @var $installer Mage_Sales_Model_Entity_Setup */ $installer->startSetup(); $installer->run("\r\n\r\n/*Table structure for table `sales_order` */\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')};\r\nCREATE TABLE {$this->getTable('sales_order')} (\r\n `entity_id` int(10) unsigned NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_set_id` smallint(5) unsigned NOT NULL default '0',\r\n `increment_id` varchar(50) NOT NULL default '',\r\n `parent_id` int(10) unsigned NOT NULL default '0',\r\n `store_id` smallint(5) unsigned default NULL,\r\n `created_at` datetime NOT NULL default '0000-00-00 00:00:00',\r\n `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',\r\n `is_active` tinyint(1) unsigned NOT NULL default '1',\r\n `customer_id` int(11),\r\n `tax_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `shipping_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `discount_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `subtotal` decimal(12,4) NOT NULL default '0.0000',\r\n `grand_total` decimal(12,4) NOT NULL default '0.0000',\r\n `total_paid` decimal(12,4) NOT NULL default '0.0000',\r\n `total_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n `total_qty_ordered` decimal(12,4) NOT NULL default '0.0000',\r\n `total_canceled` decimal(12,4) NOT NULL default '0.0000',\r\n `total_invoiced` decimal(12,4) NOT NULL default '0.0000',\r\n `total_online_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n `total_offline_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n `base_tax_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `base_shipping_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `base_discount_amount` decimal(12,4) NOT NULL default '0.0000',\r\n `base_subtotal` decimal(12,4) NOT NULL default '0.0000',\r\n `base_grand_total` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_paid` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_qty_ordered` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_canceled` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_invoiced` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_online_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n `base_total_offline_refunded` decimal(12,4) NOT NULL default '0.0000',\r\n PRIMARY KEY (`entity_id`),\r\n KEY `FK_sales_order_type` (`entity_type_id`),\r\n KEY `FK_sales_order_store` (`store_id`),\r\n CONSTRAINT `FK_SALE_ORDER_STORE` FOREIGN KEY (`store_id`) REFERENCES `{$this->getTable('core_store')}` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE,\r\n CONSTRAINT `FK_SALE_ORDER_TYPE` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;\r\n\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')}_datetime;\r\nCREATE TABLE `{$this->getTable('sales_order')}_datetime` (\r\n `value_id` int(11) NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_id` smallint(5) unsigned NOT NULL default '0',\r\n `entity_id` int(10) unsigned NOT NULL default '0',\r\n `value` datetime NOT NULL default '0000-00-00 00:00:00',\r\n PRIMARY KEY (`value_id`),\r\n KEY `FK_sales_order_datetime_entity_type` (`entity_type_id`),\r\n KEY `FK_sales_order_datetime_attribute` (`attribute_id`),\r\n KEY `FK_sales_order_datetime` (`entity_id`),\r\n CONSTRAINT `FK_sales_order_datetime` FOREIGN KEY (`entity_id`) REFERENCES `{$this->getTable('sales_order')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_datetime_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `{$this->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_datetime_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')}_decimal;\r\nCREATE TABLE `{$this->getTable('sales_order')}_decimal` (\r\n `value_id` int(11) NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_id` smallint(5) unsigned NOT NULL default '0',\r\n `entity_id` int(10) unsigned NOT NULL default '0',\r\n `value` decimal(12,4) NOT NULL default '0.0000',\r\n PRIMARY KEY (`value_id`),\r\n KEY `FK_sales_order_decimal_entity_type` (`entity_type_id`),\r\n KEY `FK_sales_order_decimal_attribute` (`attribute_id`),\r\n KEY `FK_sales_order_decimal` (`entity_id`),\r\n CONSTRAINT `FK_sales_order_decimal` FOREIGN KEY (`entity_id`) REFERENCES `{$this->getTable('sales_order')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_decimal_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `{$this->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_decimal_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')}_int;\r\nCREATE TABLE `{$this->getTable('sales_order')}_int` (\r\n `value_id` int(11) NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_id` smallint(5) unsigned NOT NULL default '0',\r\n `entity_id` int(10) unsigned NOT NULL default '0',\r\n `value` int(11) NOT NULL default '0',\r\n PRIMARY KEY (`value_id`),\r\n KEY `FK_sales_order_int_entity_type` (`entity_type_id`),\r\n KEY `FK_sales_order_int_attribute` (`attribute_id`),\r\n KEY `FK_sales_order_int` (`entity_id`),\r\n CONSTRAINT `FK_sales_order_int` FOREIGN KEY (`entity_id`) REFERENCES `{$this->getTable('sales_order')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_int_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `{$this->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_int_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')}_text;\r\nCREATE TABLE `{$this->getTable('sales_order')}_text` (\r\n `value_id` int(11) NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_id` smallint(5) unsigned NOT NULL default '0',\r\n `entity_id` int(10) unsigned NOT NULL default '0',\r\n `value` text NOT NULL,\r\n PRIMARY KEY (`value_id`),\r\n KEY `FK_sales_order_text_entity_type` (`entity_type_id`),\r\n KEY `FK_sales_order_text_attribute` (`attribute_id`),\r\n KEY `FK_sales_order_text` (`entity_id`),\r\n CONSTRAINT `FK_sales_order_text` FOREIGN KEY (`entity_id`) REFERENCES `{$this->getTable('sales_order')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_text_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `{$this->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_text_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n\r\nDROP TABLE IF EXISTS {$this->getTable('sales_order')}_varchar;\r\nCREATE TABLE `{$this->getTable('sales_order')}_varchar` (\r\n `value_id` int(11) NOT NULL auto_increment,\r\n `entity_type_id` smallint(5) unsigned NOT NULL default '0',\r\n `attribute_id` smallint(5) unsigned NOT NULL default '0',\r\n `entity_id` int(10) unsigned NOT NULL default '0',\r\n `value` varchar(255) NOT NULL default '',\r\n PRIMARY KEY (`value_id`),\r\n KEY `FK_sales_order_varchar_entity_type` (`entity_type_id`),\r\n KEY `FK_sales_order_varchar_attribute` (`attribute_id`),\r\n KEY `FK_sales_order_varchar` (`entity_id`),\r\n CONSTRAINT `FK_sales_order_varchar` FOREIGN KEY (`entity_id`) REFERENCES `{$this->getTable('sales_order')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_varchar_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `{$this->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,\r\n CONSTRAINT `FK_sales_order_varchar_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `{$this->getTable('eav_entity_type')}` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n\r\n"); $installer->endSetup(); $orderEntityTypeId = $this->getEntityTypeId('order'); $attributes = array('customer_id' => array(), 'tax_amount' => array(), 'shipping_amount' => array(), 'discount_amount' => array(), 'subtotal' => array(), 'grand_total' => array(), 'total_paid' => array(), 'total_refunded' => array(), 'total_qty_ordered' => array(), 'total_canceled' => array(), 'total_invoiced' => array(), 'total_online_refunded' => array(), 'total_offline_refunded' => array(), 'base_tax_amount' => array(), 'base_shipping_amount' => array(), 'base_discount_amount' => array(), 'base_subtotal' => array(), 'base_grand_total' => array(), 'base_total_paid' => array(), 'base_total_refunded' => array(), 'base_total_qty_ordered' => array(), 'base_total_canceled' => array(), 'base_total_invoiced' => array(), 'base_total_online_refunded' => array(), 'base_total_offline_refunded' => array()); $select = new Zend_Db_Select($installer->getConnection()); $select->from(array('e' => $this->getTable('sales_order_entity'))); $attributeIds = array(); foreach ($attributes as $code => $params) { $attributes[$code] = $installer->getAttribute($orderEntityTypeId, $code); if ($attributes[$code]['backend_type'] != 'static') { $select->joinLeft(array("_table_{$code}" => "{$this->getTable('sales_order_entity')}_{$attributes[$code]['backend_type']}"), "_table_{$code}.attribute_id = {$attributes[$code]['attribute_id']} AND _table_{$code}.entity_id = e.entity_id", array($code => 'value')); $select->join(array("_eav_atr_{$code}" => $this->getTable('eav/attribute')), "_eav_atr_{$code}.attribute_id = {$attributes[$code]['attribute_id']}", array()); $attributeIds[] = $attributes[$code]['attribute_id']; } } $select->where("e.entity_type_id = {$orderEntityTypeId}"); $orders = $installer->getConnection()->fetchAll($select); foreach ($orders as $order) { $old_entity_id = $order['entity_id']; unset($order['entity_id']); unset($order['parent_id']); foreach ($order as $key => $field) { if ($field == '') { unset($order[$key]); } }
/** * Perform the correct join from given object property and return the table alias * @todo test and implement in various places in find() method * @param Property\ObjectProperty $property * @param string $table * @return string */ protected function _join(Property\ObjectProperty $property, $table) { $join = array(); $class = $property->getParameter('instanceof'); $stable = $this->_getTableFromClass($class); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($property->getParent()->getDataObject()->getClass(), $property->getId()) : $property->getId(); $uniqext = $stable . '__joined_for__' . $leftkey; if (!isset($this->_alreadyJoined[$stable])) { $sbackend = ObjectModel::getObjectBackend($class); if ($sbackend->getAlias() != $this->getAlias()) { // @todo raise and exception if backends are not of same type // 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 = $property->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { // @todo fix left key that should be provided by mapper $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}", implode(' AND ', $join), array()); $this->_alreadyJoined[$stable] = $uniqext; } return $uniqext; }
function addJoins(Zend_Db_Select $select, $noRoot = false) { $joins = ''; $levels = $this->schema->getLevels(); foreach ($levels as $level) { $condition = sprintf('`elite_level_%1$s`.`id` = `elite_definition`.`%1$s_id`', $level); $select->joinLeft('elite_level_' . $level, $condition); } }
/** * 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; } $this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'r.entity_pk_value=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->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; }