/**
*
* @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;
}
