protected function _addHierarchy() { $requestPath = $this->_getWriteAdapter()->getIfNullSql('h.request_url', 'main_table.identifier'); // Add Hierarchy $this->_select->join(array('h' => $this->getTable('enterprise_cms/hierarchy_node')), 'main_table.page_id=h.page_id', array($requestPath . ' as url')); return $this; }
/** * Get Canonical Collection ( all different link for that product ) * * @param string $storeId * @param string $prodId * * @return Zend_Db_Statement_Interface */ public function _getCollectionCE($storeId, $prodId, $catId = null) { $adapter = $this->_getReadAdapter(); $urlConditions = array('e.product_id=ur.product_id', 'e.category_id=ur.category_id', $adapter->quoteInto('ur.store_id=?', $storeId), 'ur.is_system="1"'); $this->_select = $adapter->select()->from(array('e' => $this->getMainTable()), array('category_id'))->where('e.product_id =?', $prodId)->where('e.store_id =?', $storeId)->where('e.is_parent= "1"'); if ($catId) { $this->_select->where('e.category_id =?', $catId); } $this->_select = $this->_select->join(array('ur' => $this->getTable('core/url_rewrite')), join(' AND ', $urlConditions), array('url' => 'request_path')); // die((string)($this->_select)); $query = $adapter->query($this->_select); return $query; }
/** * 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; }
/** * 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; }
/** * Select the token before the current token * * @param string|array $fields * @return \Gems_Tracker_Token_TokenSelect */ public function forPreviousTokenId($tokenId) { $this->sql_select->join('gems__tokens as ct', 'gems__tokens.gto_id_respondent_track = ct.gto_id_respondent_track AND gems__tokens.gto_id_token != ct.gto_id_token AND ((gems__tokens.gto_round_order > ct.gto_round_order) OR (gems__tokens.gto_round_order = ct.gto_round_order AND gems__tokens.gto_created > ct.gto_created))', array()); $this->sql_select->where('ct.gto_id_token = ?', $tokenId)->order('gems__tokens.gto_round_order')->order('gems__tokens.gto_created'); return $this; }
/** * @access protected * @param Zend_Db_Select $select * @return void */ protected function _joinDefault(Zend_Db_Select $select) { $dbFEFOPPrograms = App_Model_DbTable_Factory::get('FEFOPPrograms'); $dbFEFOPModules = App_Model_DbTable_Factory::get('FEFOPModules'); $dbAddDistrict = App_Model_DbTable_Factory::get('AddDistrict'); $dbSysUser = App_Model_DbTable_Factory::get('SysUser'); $mapper = new Fefop_Model_Mapper_Contract(); $select->join($dbFEFOPPrograms->__toString(), 'FEFOP_Programs.id_fefop_programs = FEFOP_Contract.fk_id_fefop_programs', array())->join($dbFEFOPModules->__toString(), 'FEFOP_Modules.id_fefop_modules = FEFOP_Contract.fk_id_fefop_modules', array())->join($dbAddDistrict->__toString(), 'AddDistrict.acronym = FEFOP_Contract.num_district', array())->join($dbSysUser->__toString(), 'SysUser.id_sysuser = FEFOP_Contract.fk_id_sysuser', array())->join(array('s' => new Zend_Db_Expr('(' . $this->_selectFEFOPStatus() . ')')), 's.fk_id_fefop_contract = FEFOP_Contract.id_fefop_contract', array())->join(array('b' => new Zend_Db_Expr('(' . $mapper->getSelectBeneficiary() . ')')), 'b.fk_id_fefop_contract = FEFOP_Contract.id_fefop_contract AND b.target = 1', array()); }
/** * append grants acl filter * * @param Zend_Db_Select $select * @param Tinebase_Backend_Sql_Abstract $backend * @param Tinebase_Model_User $user */ protected function _appendGrantsFilter($select, $backend, $user) { $db = $backend->getAdapter(); $select->join(array($this->_aclTableName => SQL_TABLE_PREFIX . $this->_aclTableName), "{$db->quoteIdentifier($this->_aclTableName . '.record_id')} = {$db->quoteIdentifier($backend->getTableName() . '.id')}", array()); Tinebase_Container::addGrantsSql($select, $user, $this->_requiredGrants, $this->_aclTableName); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' $select after appending grants sql: ' . $select); } }
/** * Join * * 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 join($name, $cond, $cols = array(), $schema = null) { $this->_joinHistory[] = array('function' => 'join', 'args' => func_get_args()); if (is_string($name) && strpos($name, ' ')) { list($table, $alias) = explode(' ', $name); $name = array($alias => $table); } $this->_isJoined = true; $this->_select->join($name, $cond, $cols, $schema); $this->_count = false; return $this; }
/** * Applies the requierd params for tags acl to the given select object * * @param Zend_Db_Select $_select * @param string $_right required right * @param string $_idProperty property of tag id in select statement * @return void */ public static function applyAclSql($_select, $_right = self::VIEW_RIGHT, $_idProperty = 'id') { $db = Tinebase_Core::getDb(); // TODO: how quota default value if ($_idProperty == 'id') { $_idProperty = $db->quoteIdentifier('id'); } $currentAccountId = Tinebase_Core::getUser()->getId(); $currentGroupIds = Tinebase_Group::getInstance()->getGroupMemberships($currentAccountId); $groupCondition = !empty($currentGroupIds) ? ' OR (' . $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP) . ' AND ' . $db->quoteInto($db->quoteIdentifier('acl.account_id') . ' IN (?)', $currentGroupIds) . ' )' : ''; $where = $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE) . ' OR (' . $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_USER) . ' AND ' . $db->quoteInto($db->quoteIdentifier('acl.account_id') . ' = ?', $currentAccountId) . ' ) ' . $groupCondition; $_select->join(array('acl' => SQL_TABLE_PREFIX . 'tags_acl'), $_idProperty . ' = ' . $db->quoteIdentifier('acl.tag_id'), array())->where($where)->where($db->quoteIdentifier('acl.account_right') . ' = ?', $_right); }
public function getAllImagesCollection($storeId, $includeOutOfStock = true, $catId = null) { $store = Mage::app()->getStore($storeId); /* @var $store Mage_Core_Model_Store */ if (!$store) { return false; } // filter for category if ($catId) { $catConditions = array('e.entity_id=cat_index.product_id', $this->_getWriteAdapter()->quoteInto('cat_index.store_id=?', $store->getId()), $this->_getWriteAdapter()->quoteInto('cat_index.category_id=?', $catId), $this->_getWriteAdapter()->quoteInto('cat_index.is_parent=?', 1)); } else { $rootId = $store->getRootCategoryId(); $_category = Mage::getModel('catalog/category')->load($rootId); //get category model $child_categories = $_category->getResource()->getChildren($_category, false); //array consisting of all child categories id $child_categories = array_merge(array($rootId), $child_categories); // filter product that doesn't belongs to the store root category childs $catConditions = array('e.entity_id=cat_index.product_id', $this->_getWriteAdapter()->quoteInto('cat_index.store_id=?', $store->getId()), $this->_getWriteAdapter()->quoteInto('cat_index.position!=?', 0)); } $this->_select = $this->_getWriteAdapter()->select()->from(array('e' => $this->getMainTable()), array($this->getIdFieldName(), 'e.entity_id', 'path' => 'e.value'))->join(array('cat_index' => $this->getTable('catalog/category_product_index')), join(' AND ', $catConditions), array()); // filter Out of Stock if (!$includeOutOfStock) { $stkConditions = array('e.entity_id=stk.product_id', $this->_getWriteAdapter()->quoteInto('stk.is_in_stock=?', 1)); $this->_select = $this->_select->join(array('stk' => $this->getTable('cataloginventory/stock_item')), join(' AND ', $stkConditions), array('is_in_stock' => 'is_in_stock')); } // $valueConditions = array( // 'e.value_id=w.value_id', // // $this->_getWriteAdapter()->quoteInto('w.disabled=?', 0) // ); // // $this->_select = $this->_select->join( // array('w' => $this->getTable('catalog/product_attribute_media_gallery_value')), join(' AND ', $valueConditions), array('w.disabled') // ); $query = $this->_getWriteAdapter()->query($this->_select); // die((string) ($this->_select)); return $query; }
/** * Stud function to allow extension of standard one table select. * * @param \Zend_Db_Select $select */ protected function processSelect(\Zend_Db_Select $select) { $select->join('gems__surveys', 'gto_id_survey = gsu_id_survey'); $select->join('gems__groups', 'gsu_id_primary_group = ggp_id_group'); }
/** * Limit select by website with joining to store table * * @param Zend_Db_Select $select * @param int | Zend_Db_Expr $website * @param string $storeIdField * @return Enterprise_CustomerSegment_Model_Condition_Abstract */ protected function _limitByStoreWebsite(Zend_Db_Select $select, $website, $storeIdField) { $storeTable = $this->getResource()->getTable('core/store'); $select->join(array('store' => $storeTable), $storeIdField . '=store.store_id', array())->where('store.website_id=?', $website); return $this; }
/** * Get category collection array * * @return array */ public function getCollection($storeId, $onlyCount = false, $limit = 4000000000, $from = 0) { $products = array(); $store = Mage::app()->getStore($storeId); /* @var $store Mage_Core_Model_Store */ if (!$store) { return false; } if (self::FILTER_PRODUCT == 1) { $fpstring = " AND product_id IN (" . implode(',', $this->_getStoreProductIds($storeId)) . ")"; } else { $fpstring = ''; } $read = $this->_getReadAdapter(); $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 {$fpstring}", 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() . new Zend_Db_Expr(" AND store_id =\n IF(\n\t\t\t\t\t\t(SELECT `exclude`.`value` FROM `{$excludeAttr->getBackend()->getTable()}` AS `exclude` WHERE `exclude`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$excludeAttr->getAttributeId()} AND `store_id` = {$storeId}) ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL'); } if (Mage::helper('xsitemap')->isExcludeFromXMLOutOfStockProduct($storeId)) { $cond = 'e.entity_id = csi.product_id'; if (Mage::getStoreConfig('cataloginventory/item_options/manage_stock', $storeId)) { $cond .= ' AND IF (csi.use_config_manage_stock = 1, csi.is_in_stock = 1, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } else { $cond .= ' AND IF (csi.use_config_manage_stock = 1, TRUE, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } $this->_select->join(array('csi' => $this->getTable('cataloginventory/stock_item')), $cond, array('is_in_stock', 'manage_stock', 'use_config_manage_stock')); } $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); } $sort = ''; if (Mage::helper('xsitemap')->isSeosuiteUltimateAvailable() && Mage::helper('xsitemap')->isSeosuiteCanonicalUrlEnabled($storeId) && Mage::helper('xsitemap')->getSeosuiteProductCanonicalType($storeId)) { $productCanonicalType = Mage::helper('xsitemap')->getSeosuiteProductCanonicalType($storeId); if ($productCanonicalType == 3) { //$suffix = "AND canonical_url_rewrite.category_id IS NULL"; $suffix = ''; $suffix2 = "AND category_id IS NULL"; } else { //$suffix = "AND canonical_url_rewrite.category_id IS NOT NULL"; $suffix = ''; $suffix2 = "AND category_id IS NOT NULL"; } if ($productCanonicalType == 1 || $productCanonicalType == 4) { $sort = 'DESC'; } else { if ($productCanonicalType == 2 || $productCanonicalType == 5) { $sort = 'ASC'; } else { } } } else { $length = Mage::helper('xsitemap')->getXmlSitemapUrlLength(); if ($length == 'short') { $sort = 'ASC'; } elseif ($length == 'long') { $sort = 'DESC'; } if (Mage::getStoreConfigFlag('catalog/seo/product_use_categories', $storeId)) { $suffix3 = ''; } else { $suffix3 = 'AND `category_id` IS NULL'; } } $canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url'); $urlPathAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('url_path'); /* if (Mage::helper('xsitemap')->isEnterpriseSince113()) { $this->_select->columns(array('url' => new Zend_Db_Expr("( SELECT `eur`.`request_path` FROM `" . Mage::getSingleton('core/resource')->getTableName('enterprise_url_rewrite') . "` AS `eur` INNER JOIN `" . Mage::getSingleton('core/resource')->getTableName('enterprise_catalog_product_rewrite') . "` AS `ecpr` ON `eur`.`url_rewrite_id` = `ecpr`.`url_rewrite_id` WHERE `product_id`=`e`.`entity_id` AND `ecpr`.`store_id` IN(" . intval(Mage::app()->isSingleStoreMode() ? 0 : 0, $storeId) . ") AND `is_system`=1 AND `request_path` IS NOT NULL " . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)"))); } * */ if (Mage::helper('xsitemap')->isEnterpriseSince113()) { $urlSuffix = Mage::helper('catalog/product')->getProductUrlSuffix($storeId); if ($urlSuffix) { $urlSuffix = '.' . $urlSuffix; } else { $urlSuffix = ''; } $this->_select->joinLeft(array('ecp' => $this->getTable('enterprise_catalog/product')), 'ecp.product_id = e.entity_id ' . 'AND ecp.store_id = ' . $storeId, array())->joinLeft(array('euur' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp.url_rewrite_id = euur.url_rewrite_id AND euur.is_system = 1', array())->joinLeft(array('ecp2' => $this->getTable('enterprise_catalog/product')), 'ecp2.product_id = e.entity_id AND ecp2.store_id = 0', array())->joinLeft(array('euur2' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp2.url_rewrite_id = euur2.url_rewrite_id', array('url' => 'concat( ' . $this->_getWriteAdapter()->getIfNullSql('euur.request_path', 'euur2.request_path') . ',"' . $urlSuffix . '")')); } elseif (!empty($productCanonicalType) && $canonicalAttr) { $this->_select->columns(array('url' => new Zend_Db_Expr("\n IFNULL(\n (IFNULL(\n (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() . " AND canonical_url_rewrite.`store_id` IN (0," . $storeId . ") {$suffix}" . ($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` IN (0," . $storeId . ") AND `is_system`=1 AND `request_path` IS NOT NULL {$suffix2}" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)\n )),\n (SELECT p.`value` FROM `" . $urlPathAttr->getBackend()->getTable() . "` AS p\n WHERE p.`entity_id` = e.`entity_id` AND p.`attribute_id` = " . $urlPathAttr->getAttributeId() . " AND p.`store_id` IN (0," . $storeId . ") ORDER BY p.`store_id` DESC LIMIT 1\n )\n )"))); } else { $this->_select->columns(array('url' => new Zend_Db_Expr("(\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 {$suffix3}" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)"))); } $crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain'); if ($crossDomainAttr && !empty($productCanonicalType)) { $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')); } $updatedAt = Mage::getModel('catalog/product')->getResource()->getAttribute('updated_at'); if ($updatedAt) { $this->_select->joinLeft(array('updatedat_tbl' => $updatedAt->getBackend()->getTable()), 'updatedat_tbl.entity_id = e.entity_id', array('updated_at' => 'updatedat_tbl.updated_at')); } //echo $this->_select->assemble(); exit; $query = $read->query($this->_select); while ($row = $query->fetch()) { $product = $this->_prepareProduct($row); /** if (isset($productCanonicalType) && $productCanonicalType == 3) { // use root $urlArr = explode('/', $product->getUrl()); $product->setUrl(end($urlArr)); } **/ $products[$product->getId()] = $product; } return $products; }
/** * Stub function to allow extension of standard one table select. * * @param \Zend_Db_Select $select */ protected function processSelect(\Zend_Db_Select $select) { // $select->joinLeft('gems__rounds', 'gto_id_round = gro_id_round', array()); // $select->join('gems__tracks', 'gto_id_track = gtr_id_track', array()); $select->join('gems__surveys', 'gto_id_survey = gsu_id_survey', array()); $select->join('gems__groups', 'gsu_id_primary_group = ggp_id_group', array()); $select->join('gems__respondents', 'gto_id_respondent = grs_id_user', array()); $select->join('gems__respondent2track', 'gto_id_respondent_track = gr2t_id_respondent_track', array()); $select->join('gems__reception_codes', 'gto_reception_code = grc_id_reception_code', array()); }
/** * Recuperar da table proposta_valores comissões baseadas em um array de ids do usuarios * @param array $uids */ public function getPagamentosByUser($uids = array()) { $select = new Zend_Db_Select($this->db); $select->from(array('v' => $this->name_valores), array('id', 'id_proposta', 'comissao', 'parcelas_pagas', 'last_modified', 'last_user_id')); $select->join(array('c' => $this->name), 'v.id_proposta = c.id', 'dados_extras'); $select->where('c.created_user_id IN (' . implode(',', $uids) . ')'); $select->order('v.last_modified DESC'); return $select; }
public static function _setSelectRules(Zend_Db_Select $select) { $select->join(RM_User_Profile::TABLE_NAME, join(' = ', array(RM_User_Profile::TABLE_NAME . '.idUser', self::TABLE_NAME . '.idUser'))); parent::_setSelectRules($select); }
/** * Filter by an item used on the exhibit page * @param Zend_Db_Select $select Select object to filter * @param integer $item_id Item id to filter by */ protected function filterByItem($select, $item_id) { $db = $this->getDb(); $select->join(array('exhibit_page_blocks' => $db->ExhibitPageBlocks), 'exhibit_pages.id = exhibit_page_blocks.page_id', array()); $select->join(array('exhibit_block_attachments' => $db->ExhibitBlockAttachments), 'exhibit_page_blocks.id = exhibit_block_attachments.block_id', array()); $select->where('exhibit_block_attachments.item_id = ?', $item_id); }
/** * * @param Zend_Db_Select $select * @param array $filters * @return Zend_Db_Select */ public function applyFiltersSelect($select, $filters) { if (!empty($filters['fk_id_fefop_type_transaction'])) { $select->where('bs.fk_id_fefop_type_transaction = ?', $filters['fk_id_fefop_type_transaction']); } if (!empty($filters['fk_id_budget_category'])) { $select->where('bsc.fk_id_budget_category IN (?)', (array) $filters['fk_id_budget_category']); } if (!empty($filters['fk_id_budget_category_type'])) { $select->where('bc.fk_id_budget_category_type IN (?)', (array) $filters['fk_id_budget_category_type']); } if (!empty($filters['fk_id_fefopfund'])) { $select->where('bs.fk_id_fefopfund = ?', $filters['fk_id_fefopfund']); } if (!empty($filters['status'])) { $select->where('bs.status = ?', $filters['status']); } if (!empty($filters['minimum_amount'])) { $select->where('bs.amount >= ?', App_General_String::toFloat($filters['minimum_amount'])); } if (!empty($filters['maximum_amount'])) { $select->where('bs.amount <= ?', App_General_String::toFloat($filters['maximum_amount'])); } if (!empty($filters['num_contract'])) { $dbFefopContract = App_Model_DbTable_Factory::get('FEFOPContract'); $select->join(array('fct' => $dbFefopContract), 'fct.id_fefop_contract = bsc.fk_id_fefop_contract', array('num_contract' => new Zend_Db_Expr('CONCAT( fct.num_program, "-",' . ' fct.num_module, "-", ' . 'fct.num_district, "-", ' . 'fct.num_year, "-", fct.num_sequence )')))->having('num_contract LIKE ?', '%' . $filters['num_contract'] . '%'); } $date = new Zend_Date(); if (!empty($filters['date_start'])) { $select->where('bs.date_statement >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd')); } if (!empty($filters['date_finish'])) { $select->where('bs.date_statement <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd')); } return $select; }
/** * Applies the requierd params for tags acl to the given select object * * @param Zend_Db_Select $_select * @param string $_right required right * @param string $_idProperty property of tag id in select statement * @return void */ public static function applyAclSql($_select, $_right = self::VIEW_RIGHT, $_idProperty = 'id') { if (empty($_right)) { throw new Tinebase_Exception_InvalidArgument('right is empty'); } $db = Tinebase_Core::getDb(); if ($_idProperty == 'id') { $_idProperty = $db->quoteIdentifier('id'); } if (!is_object(Tinebase_Core::getUser())) { if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) { Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Cannot apply ACL, no user object found. This might happen during setup/update.'); } return; } $currentAccountId = Tinebase_Core::getUser()->getId(); $currentGroupIds = Tinebase_Group::getInstance()->getGroupMemberships($currentAccountId); $groupCondition = !empty($currentGroupIds) ? ' OR (' . $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP) . ' AND ' . $db->quoteInto($db->quoteIdentifier('acl.account_id') . ' IN (?)', $currentGroupIds) . ' )' : ''; $where = $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE) . ' OR (' . $db->quoteInto($db->quoteIdentifier('acl.account_type') . ' = ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_USER) . ' AND ' . $db->quoteInto($db->quoteIdentifier('acl.account_id') . ' = ?', $currentAccountId) . ' ) ' . $groupCondition; $_select->join(array('acl' => SQL_TABLE_PREFIX . 'tags_acl'), $_idProperty . ' = ' . $db->quoteIdentifier('acl.tag_id'), array())->where($where)->where($db->quoteIdentifier('acl.account_right') . ' = ?', $_right); }
/** * @param int * @return Varien_Data_Collerction */ public function getCollection($catId) { $currentCatProducts = $this->_getProductDataByCategory($catId); if (!$currentCatProducts) { return new Varien_Data_Collection(); } $productUrlToCategory = Mage::helper('xsitemap')->getHtmlSitemapProductUrlType(); $onlyCount = false; $store = Mage::app()->getStore(); $storeId = $store->getStoreId(); $read = $this->_getReadAdapter(); $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 AND product_id IN (" . implode(',', array_keys($currentCatProducts)) . ")", array())->where('w.website_id=?', $store->getWebsiteId()); $name = Mage::getModel('catalog/product')->getResource()->getAttribute('name'); if ($name) { $this->_select->join(array('cpev' => $name->getBackend()->getTable(), array($this->getIdFieldName())), "cpev.entity_id = e.entity_id", array('name' => 'cpev.value')); $this->_select->joinRight(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), "cpev.attribute_id = ea.attribute_id" . new Zend_Db_Expr(" AND cpev.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `cpev_store`.`value` FROM `{$name->getBackend()->getTable()}` AS `cpev_store` WHERE `cpev_store`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$name->getAttributeId()} AND `store_id` = {$storeId}) IS NOT NULL ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('ea.attribute_code=?', 'name'); } $excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_html_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() . new Zend_Db_Expr(" AND exclude_tbl.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `exclude`.`value` FROM `{$excludeAttr->getBackend()->getTable()}` AS `exclude` WHERE `exclude`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$excludeAttr->getAttributeId()} AND `store_id` = {$storeId}) ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL'); } if (Mage::helper('xsitemap')->isExcludeFromXMLOutOfStockProduct($storeId)) { $cond = 'e.entity_id = csi.product_id'; if (Mage::getStoreConfig('cataloginventory/item_options/manage_stock', $storeId)) { $cond .= ' AND IF (csi.use_config_manage_stock = 1, csi.is_in_stock = 1, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } else { $cond .= ' AND IF (csi.use_config_manage_stock = 1, TRUE, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } $this->_select->join(array('csi' => $this->getTable('cataloginventory/stock_item')), $cond, array('is_in_stock', 'manage_stock', 'use_config_manage_stock')); } $this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in'); $this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in'); $suffix = ''; $suffix2 = ''; $sort = ''; if ($productUrlToCategory == 'canonical') { $suffix = "AND canonical_url_rewrite.category_id IS NOT NULL"; $suffix2 = "AND category_id IS NOT NULL"; $productCanonicalType = Mage::helper('xsitemap')->getSeosuiteProductCanonicalType(); if ($productCanonicalType) { if ($productCanonicalType == 1 || $productCanonicalType == 4) { $sort = 'DESC'; } elseif ($productCanonicalType == 2 || $productCanonicalType == 5) { $sort = 'ASC'; } elseif ($productCanonicalType == 3) { } else { $productUrlToCategory = 'yes'; } } } if ($productUrlToCategory == 'no') { if (!Mage::helper('xsitemap')->isProductUrlUseCategory()) { $sort = ''; $cropCategory = true; } } elseif ($productUrlToCategory == 'yes') { } $canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url'); $urlPathAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('url_path'); if (Mage::helper('xsitemap')->isEnterpriseSince113()) { $urlSuffix = Mage::helper('catalog/product')->getProductUrlSuffix($storeId); if ($urlSuffix) { $urlSuffix = '.' . $urlSuffix; } else { $urlSuffix = ''; } $this->_select->joinLeft(array('ecp' => $this->getTable('enterprise_catalog/product')), 'ecp.product_id = e.entity_id ' . 'AND ecp.store_id = ' . $storeId, array())->joinLeft(array('euur' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp.url_rewrite_id = euur.url_rewrite_id AND euur.is_system = 1', array())->joinLeft(array('ecp2' => $this->getTable('enterprise_catalog/product')), 'ecp2.product_id = e.entity_id AND ecp2.store_id = 0', array())->joinLeft(array('euur2' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp2.url_rewrite_id = euur2.url_rewrite_id', array('url' => 'concat( ' . $this->_getWriteAdapter()->getIfNullSql('euur.request_path', 'euur2.request_path') . ',"' . $urlSuffix . '")')); } elseif ($canonicalAttr && $productUrlToCategory == 'canonical') { $this->_select->columns(array('url' => new Zend_Db_Expr("\n IFNULL(\n (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() . " AND canonical_url_rewrite.`store_id` IN (0," . $storeId . ") {$suffix}" . ($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` IN (0," . $storeId . ") AND `is_system`=1 AND `request_path` IS NOT NULL {$suffix2}" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)\n )),\n (SELECT p.`value` FROM `" . $urlPathAttr->getBackend()->getTable() . "` AS p\n WHERE p.`entity_id` = e.`entity_id` AND p.`attribute_id` = " . $urlPathAttr->getAttributeId() . " AND p.`store_id` IN (0," . $storeId . ") LIMIT 1\n )\n )"))); } else { $this->_select->columns(array('url' => new Zend_Db_Expr("(\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 `category_id`= " . intval($catId) . " AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)"))); } $crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain'); if ($crossDomainAttr && !empty($productCanonicalType)) { $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')); } $sortOrder = Mage::helper('xsitemap')->getHtmlSitemapSort(); if ($sortOrder == 'position') { $sortOrder = 'e.entity_id'; } if ($sortOrder) { $this->_select->order($sortOrder); } //echo $this->_select->assemble(); $query = $read->query($this->_select); $collection = new Varien_Data_Collection(); while ($row = $query->fetch()) { $product = $this->_prepareProduct($row); //If use root canonical or Product url without category. if (isset($productCanonicalType) && $productCanonicalType == 3 || isset($cropCategory) && $cropCategory) { $urlArr = explode('/', $product->getUrl()); $product->setUrl(end($urlArr)); } if (!in_array($product->getId(), $collection->getAllIds())) { $collection->addItem($product); } } return $collection; }
protected function _addRelationJoins(Zend_Db_Select $sql, $alias = null) { $alias = $alias ?: 'i'; $sql->join(array('r_project' => 'project'), "r_project.project_id = {$alias}.project", array('project.project_id' => 'project_id', 'project.name' => 'name', 'project.private' => 'private')); $sql->join(array('r_createdby' => 'user'), "r_createdby.user_id = {$alias}.created_by", array('created_by.user_id' => 'user_id', 'created_by.username' => 'username', 'created_by.password' => 'password', 'created_by.last_login' => 'last_login', 'created_by.last_ip' => new Zend_Db_Expr('INET_NTOA(`r_createdby`.`last_ip`)'), 'created_by.register_time' => 'register_time', 'created_by.register_ip' => new Zend_Db_Expr('INET_NTOA(`r_createdby`.`register_ip`)'))); $sql->joinLeft(array('r_assignedto' => 'user'), "r_assignedto.user_id = {$alias}.assigned_to", array('assigned_to.user_id' => 'user_id', 'assigned_to.username' => 'username', 'assigned_to.password' => 'password', 'assigned_to.last_login' => 'last_login', 'assigned_to.last_ip' => new Zend_Db_Expr('INET_NTOA(`r_assignedto`.`last_ip`)'), 'assigned_to.register_time' => 'register_time', 'assigned_to.register_ip' => new Zend_Db_Expr('INET_NTOA(`r_assignedto`.`register_ip`)'))); return $sql; }
/** * appends container_acl sql * * @param Zend_Db_Select $_select * @param integer $iteration * @return string table identifier to work on */ public static function addGrantsSqlCallback($_select, $iteration) { $db = $_select->getAdapter(); $_select->join(array('container_acl' . $iteration => SQL_TABLE_PREFIX . 'container_acl'), $db->quoteIdentifier('container_acl' . $iteration . '.container_id') . ' = ' . $db->quoteIdentifier('container.id'), array()); return 'container_acl' . $iteration; }
*/ $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]); } } $installer->getConnection()->insert($this->getTable('sales_order'), $order);
public static function _setSelectRules(Zend_Db_Select $select) { $select->join(parent::TABLE_NAME, join(' = ', array(parent::TABLE_NAME . '.idPage', self::TABLE_NAME . '.idPage')), Application_Model_Page::_getDbAttributes()); parent::_setSelectRules($select); }
/** * Add attribute to filter * * @param int $storeId * @param string $attributeCode * @param mixed $value * @param string $type * @return Zend_Db_Select */ protected function _addFilter($storeId, $attributeCode, $value, $type = '=') { if (!isset($this->_attributesCache[$attributeCode])) { $attribute = Mage::getSingleton('catalog/product')->getResource()->getAttribute($attributeCode); $this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType()); } $attribute = $this->_attributesCache[$attributeCode]; if (!$this->_select instanceof Zend_Db_Select) { return false; } switch ($type) { case '=': $conditionRule = '=?'; break; case 'in': $conditionRule = ' IN(?)'; break; default: return false; break; } $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; }