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;
}
