/**
* Retrieve cms page collection array
*
* @param unknown_type $storeId
* @return Zend_Db_Statement_Interface
*/
public function getCollection($storeId, $includeEEHierarchy = false)
{
$this->_select = $this->_getWriteAdapter()->select();
$this->_select->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName(), 'DATE(main_table.update_time) as updated_at'))->join(array('store_table' => $this->getTable('cms/page_store')), 'main_table.page_id=store_table.page_id', array())->where('main_table.is_active=1')->where('store_table.store_id IN(?)', array(0, $storeId));
if ($includeEEHierarchy) {
$this->_addHierarchy();
} else {
$this->_select->columns('main_table.identifier as url');
}
// die((string)$this->_select);
$query = $this->_getWriteAdapter()->query($this->_select);
return $query;
}
/**
* Saving information about customer
*
* @param Mage_Log_Model_Visitor $visitor
*
* @return Mage_Log_Model_Resource_Visitor
*/
protected function _saveCustomerInfo($visitor)
{
$adapter = $this->_getWriteAdapter();
if ($visitor->getDoCustomerLogout() && ($logId = $visitor->getCustomerLogId())) {
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Zend_Db_Select($connection);
$select->from($resource->getTableName('log/customer'));
$select->reset(Zend_Db_Select::COLUMNS);
$select->columns('login_at');
$select->where('log_id = ?', $logId);
$loginAt = $connection->fetchOne($select);
if (!$loginAt) {
return parent::_saveCustomerInfo($visitor);
}
$data = new Varien_Object(array('login_at' => $loginAt, 'logout_at' => Mage::getSingleton('core/date')->gmtDate(), 'store_id' => (int) Mage::app()->getStore()->getId()));
$bind = $this->_prepareDataForTable($data, $this->getTable('log/customer'));
$condition = array('log_id = ?' => (int) $logId);
$adapter->update($this->getTable('log/customer'), $bind, $condition);
$visitor->setDoCustomerLogout(false);
$visitor->setCustomerId(null);
$visitor->setCustomerLogId(null);
} else {
return parent::_saveCustomerInfo($visitor);
}
return $this;
}
/**
* Get category collection array
*
* @param null|string|bool|int|\Magento\Store\Model\Store $storeId
* @return array|bool
*/
public function getCollection($storeId)
{
$products = [];
/* @var $store \Magento\Store\Model\Store */
$store = $this->_storeManager->getStore($storeId);
if (!$store) {
return false;
}
$adapter = $this->_getWriteAdapter();
$this->_select = $adapter->select()->from(['e' => $this->getMainTable()], [$this->getIdFieldName(), 'updated_at'])->joinInner(['w' => $this->getTable('catalog_product_website')], 'e.entity_id = w.product_id', [])->joinLeft(['url_rewrite' => $this->getTable('url_rewrite')], 'e.entity_id = url_rewrite.entity_id AND url_rewrite.is_autogenerated = 1' . $adapter->quoteInto(' AND url_rewrite.store_id = ?', $store->getId()) . $adapter->quoteInto(' AND url_rewrite.entity_type = ?', ProductUrlRewriteGenerator::ENTITY_TYPE), ['url' => 'request_path'])->where('w.website_id = ?', $store->getWebsiteId());
$this->_addFilter($store->getId(), 'visibility', $this->_productVisibility->getVisibleInSiteIds(), 'in');
$this->_addFilter($store->getId(), 'status', $this->_productStatus->getVisibleStatusIds(), 'in');
// Join product images required attributes
$imageIncludePolicy = $this->_sitemapData->getProductImageIncludePolicy($store->getId());
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_NONE != $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'name');
$this->_select->columns(['name' => $this->getReadConnection()->getIfNullSql('t2_name.value', 't1_name.value')]);
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_ALL == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'thumbnail');
$this->_select->columns(['thumbnail' => $this->getReadConnection()->getIfNullSql('t2_thumbnail.value', 't1_thumbnail.value')]);
} elseif (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_BASE == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'image');
$this->_select->columns(['image' => $this->getReadConnection()->getIfNullSql('t2_image.value', 't1_image.value')]);
}
}
$query = $adapter->query($this->_select);
while ($row = $query->fetch()) {
$product = $this->_prepareProduct($row, $store->getId());
$products[$product->getId()] = $product;
}
return $products;
}
/**
* Get category collection array
*
* @param null|string|bool|int|\Magento\Store\Model\Store $storeId
* @return array|bool
*/
public function getCollection($storeId)
{
$products = array();
/* @var $store \Magento\Store\Model\Store */
$store = $this->_storeManager->getStore($storeId);
if (!$store) {
return false;
}
$urConditions = array('e.entity_id = ur.product_id', 'ur.category_id IS NULL', $this->_getWriteAdapter()->quoteInto('ur.store_id = ?', $store->getId()), $this->_getWriteAdapter()->quoteInto('ur.is_system = ?', 1));
$this->_select = $this->_getWriteAdapter()->select()->from(array('e' => $this->getMainTable()), array($this->getIdFieldName(), 'updated_at'))->joinInner(array('w' => $this->getTable('catalog_product_website')), 'e.entity_id = w.product_id', array())->joinLeft(array('ur' => $this->getTable('core_url_rewrite')), join(' AND ', $urConditions), array('url' => 'request_path'))->where('w.website_id = ?', $store->getWebsiteId());
$this->_addFilter($store->getId(), 'visibility', $this->_productVisibility->getVisibleInSiteIds(), 'in');
$this->_addFilter($store->getId(), 'status', $this->_productStatus->getVisibleStatusIds(), 'in');
// Join product images required attributes
$imageIncludePolicy = $this->_sitemapData->getProductImageIncludePolicy($store->getId());
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_NONE != $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'name');
$this->_select->columns(array('name' => $this->getReadConnection()->getIfNullSql('t2_name.value', 't1_name.value')));
if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_ALL == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'thumbnail');
$this->_select->columns(array('thumbnail' => $this->getReadConnection()->getIfNullSql('t2_thumbnail.value', 't1_thumbnail.value')));
} elseif (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_BASE == $imageIncludePolicy) {
$this->_joinAttribute($store->getId(), 'image');
$this->_select->columns(array('image' => $this->getReadConnection()->getIfNullSql('t2_image.value', 't1_image.value')));
}
}
$query = $this->_getWriteAdapter()->query($this->_select);
while ($row = $query->fetch()) {
$product = $this->_prepareProduct($row, $store->getId());
$products[$product->getId()] = $product;
}
return $products;
}
/**
* Columns
*
* @param unknown_type $name
* @param unknown_type $cond
* @return WeFlex_Db_Model
*/
public function columns($cols)
{
$this->_selector->reset(Zend_Db_Select::COLUMNS);
foreach ($cols as $col) {
$this->_selector->columns($col, null);
}
return $this;
}
protected function _prepareExecute()
{
if ($this->_server == 'mysql') {
$ghostColumn = $this->getColumns();
$this->_select->reset('columns');
$this->_select->columns(array('ZFG_GHOST' => new Zend_Db_Expr("SQL_CALC_FOUND_ROWS 1+1")));
foreach ($ghostColumn as $value) {
if ($value[2] == 'ZFG_GHOST') {
continue;
}
if (is_object($value[1])) {
$this->_select->columns(array($value[2] => $value[1]), $value[0]);
} elseif ($value[2] != '') {
$this->_select->columns(array($value[2] => $value[1]), $value[0]);
} else {
$this->_select->columns($value[1], $value[0]);
}
}
}
$where = $this->_select->getPart('where');
$replaced = false;
if (count($where) > count($this->_where)) {
foreach ($this->_where as $value) {
$key = array_search($value, $where);
if ($key !== false) {
unset($where[$key]);
$replaced = true;
}
}
if ($replaced === true) {
$where = array_values($where);
$where[0] = substr($where[0], strpos(trim($where[0]), ' ') + 1);
}
if (count($where) > 0) {
$this->_select->reset('where');
$this->_select->where(new Zend_Db_Expr(implode(' ', $where)));
}
if (count($this->_where) > 0) {
$this->_select->where(new Zend_Db_Expr(implode(' ', $this->_where)));
}
}
}
protected function _prepareExecute()
{
if ($this->_server == 'mysql') {
$ghostColumn = $this->getColumns();
$this->_select->reset('columns');
$this->_select->columns(array('ZFG_GHOST' => new Zend_Db_Expr("SQL_CALC_FOUND_ROWS 1+1")));
foreach ($ghostColumn as $value) {
if ($value[2] == 'ZFG_GHOST') {
continue;
}
if (is_object($value[1])) {
$this->_select->columns(array($value[2] => $value[1]), $value[0]);
} elseif ($value[2] != '') {
$this->_select->columns(array($value[2] => $value[1]), $value[0]);
} else {
$this->_select->columns($value[1], $value[0]);
}
}
}
}
protected function _prepareSelect(Zend_Db_Select $select)
{
$fromPart = $select->getPart(Zend_Db_Select::FROM);
// the recipient table is required!
if (!isset($fromPart['recipient'])) {
return false;
}
foreach ($this->_where as $where) {
$select->where($where);
}
$select->columns($this->_columns, 'recipient');
return true;
}
protected function _addLabelConcat(Zend_Db_Select $sql, $alias = null)
{
$alias = $alias ?: $this->getTableName();
// have to have array() as the last param or issue_id will get
// overwritten with a 0 if there are no issues to join
$sql->joinLeft(array('ill' => 'issue_label_linker'), "{$alias}.issue_id = ill.issue_id", array());
$sql->columns(array('labels' => 'GROUP_CONCAT(DISTINCT ill.label_id SEPARATOR \' \')'));
$sql->group($alias . '.issue_id');
return $sql;
}
/**
* (non-PHPdoc)
* @see Tinebase_Backend_Sql_Abstract::_appendForeignSort()
*
* @todo generalize this: find a place (in model config?) for foreign record sorting information
* @todo maybe we can use a temp table with joins here
* @todo allow to to use it with keyfields, too (and/or switch those settings to keyfield configs)
*/
protected function _appendForeignSort(Tinebase_Model_Pagination $pagination, Zend_Db_Select $select)
{
$virtualSortColumns = array('leadstate_id' => Crm_Config::LEAD_STATES, 'leadsource_id' => Crm_Config::LEAD_SOURCES, 'leadtype_id' => Crm_Config::LEAD_TYPES);
$col = $pagination->sort;
if (isset($virtualSortColumns[$col])) {
$config = Crm_Config::getInstance()->get($virtualSortColumns[$col]);
// create cases (when => then) for sql switch (CASE) command
$cases = array();
foreach ($config['records'] as $settingRecord) {
$cases[$settingRecord['id']] = $settingRecord['value'];
}
$foreignSortCase = $this->_dbCommand->getSwitch($col, $cases);
$select->columns(array('foreignSortCol' => $foreignSortCase));
$pagination->sort = 'foreignSortCol';
}
}
/**
* 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;
}
/**
* appends effective grant calculation to select object
*
* @param Zend_Db_Select $_select
*/
protected function _appendEffectiveGrantCalculationSql($_select, $_attendeeFilters = NULL)
{
// groupmemberships of current user, needed to compute phys and inherited grants
$_select->joinLeft(array('groupmemberships' => $this->_tablePrefix . 'group_members'), $this->_db->quoteInto($this->_db->quoteIdentifier('groupmemberships.account_id') . ' = ?', Tinebase_Core::getUser()->getId()), array());
// attendee joins the attendee we need to compute the curr users effective grants
// NOTE: 2010-04 the behaviour changed. Now, only the attendee the client filters for are
// taken into account for grants calculation
$attendeeWhere = FALSE;
if (is_array($_attendeeFilters) && !empty($_attendeeFilters)) {
$attendeeSelect = $this->_db->select();
foreach ((array) $_attendeeFilters as $attendeeFilter) {
if ($attendeeFilter instanceof Calendar_Model_AttenderFilter) {
$attendeeFilter->appendFilterSql($attendeeSelect, $this);
}
}
$whereArray = $attendeeSelect->getPart(Zend_Db_Select::SQL_WHERE);
if (!empty($whereArray)) {
$attendeeWhere = ' AND ' . Tinebase_Helper::array_value(0, $whereArray);
}
}
$_select->joinLeft(array('attendee' => $this->_tablePrefix . 'cal_attendee'), $this->_db->quoteIdentifier('attendee.cal_event_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.id') . $attendeeWhere, array());
$_select->joinLeft(array('attendeeaccounts' => $this->_tablePrefix . 'accounts'), $this->_db->quoteIdentifier('attendeeaccounts.contact_id') . ' = ' . $this->_db->quoteIdentifier('attendee.user_id') . ' AND (' . $this->_db->quoteInto($this->_db->quoteIdentifier('attendee.user_type') . '= ?', Calendar_Model_Attender::USERTYPE_USER) . ' OR ' . $this->_db->quoteInto($this->_db->quoteIdentifier('attendee.user_type') . '= ?', Calendar_Model_Attender::USERTYPE_GROUPMEMBER) . ')', array());
$_select->joinLeft(array('attendeegroupmemberships' => $this->_tablePrefix . 'group_members'), $this->_db->quoteIdentifier('attendeegroupmemberships.account_id') . ' = ' . $this->_db->quoteIdentifier('attendeeaccounts.contact_id'), array());
$_select->joinLeft(array('dispgrants' => $this->_tablePrefix . 'container_acl'), $this->_db->quoteIdentifier('dispgrants.container_id') . ' = ' . $this->_db->quoteIdentifier('attendee.displaycontainer_id') . ' AND ' . $this->_getContainGrantCondition('dispgrants', 'groupmemberships'), array());
$_select->joinLeft(array('physgrants' => $this->_tablePrefix . 'container_acl'), $this->_db->quoteIdentifier('physgrants.container_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.container_id'), array());
$allGrants = Tinebase_Model_Grants::getAllGrants();
foreach ($allGrants as $grant) {
if (in_array($grant, $this->_recordBasedGrants)) {
$_select->columns(array($grant => "\n MAX( CASE WHEN ( \n" . ' /* physgrant */' . $this->_getContainGrantCondition('physgrants', 'groupmemberships', $grant) . " OR \n" . ' /* implicit */' . $this->_getImplicitGrantCondition($grant) . " OR \n" . ' /* inherited */' . $this->_getInheritedGrantCondition($grant) . " \n" . ") THEN 1 ELSE 0 END ) "));
} else {
$_select->columns(array($grant => "\n MAX( CASE WHEN ( \n" . ' /* physgrant */' . $this->_getContainGrantCondition('physgrants', 'groupmemberships', $grant) . "\n" . ") THEN 1 ELSE 0 END ) "));
}
}
}
/**
* Padrão de Colunas para o relatório de Beneficiários
*
* @access protected
* @param Zend_Db_Select $select
* @return void
*/
protected function _columnsDefault(Zend_Db_Select $select)
{
$select->reset(Zend_Db_Select::COLUMNS);
$select->columns(array('FEFOP_Contract.id_fefop_contract', 'FEFOP_Contract.num_district', 'FEFOP_Contract.num_program', 'FEFOP_Contract.num_module', 'AddDistrict.id_adddistrict', 'AddDistrict.District', 's.status_description', 'id_perdata' => 'b.id', 'b.code', 'b.name', 'target' => new Zend_Db_Expr("CASE WHEN b.target = 1 THEN 'Sin' ELSE 'Lae' END"), 'module' => new Zend_Db_Expr("CONCAT(FEFOP_Modules.acronym, ' - ', FEFOP_Modules.description)"), 'program' => new Zend_Db_Expr("CONCAT(FEFOP_Programs.acronym, ' - ', FEFOP_Programs.description)"), 'cod_contract' => new Zend_Db_Expr("CONCAT(FEFOP_Contract.num_program, '-', FEFOP_Contract.num_module, '-', FEFOP_Contract.num_district, '-', FEFOP_Contract.num_year, '-', FEFOP_Contract.num_sequence)"), 'disability' => new Zend_Db_Expr('(' . $this->_columnDisability() . ')'), 'gender' => new Zend_Db_Expr('(' . $this->_columnGender() . ')'), 'amount_contracted' => new Zend_Db_Expr('(' . $this->_columnAmountContracted() . ')'), 'amount_payment' => new Zend_Db_Expr('(' . $this->_columnAmouontPayment() . ')'), 'amount_real' => new Zend_Db_Expr('(' . $this->_columnAmountReal() . ')'), 'amount_addcosts' => new Zend_Db_Expr('(' . $this->_columnAdditional() . ')')));
$select->group(array('FEFOP_Contract.id_fefop_contract'));
}
/**
* (non-PHPdoc)
* @see Tinebase_Backend_Sql_Abstract::_appendForeignSort()
*
* @todo generalize this: find a place (in model config?) for foreign record sorting information
* @todo maybe we can use a temp table with joins here
* @todo allow to to use it with keyfields, too (and/or switch those settings to keyfield configs)
*/
protected function _appendForeignSort(Tinebase_Model_Pagination $pagination, Zend_Db_Select $select)
{
$virtualSortColumns = array('leadstate_id' => Crm_Model_Config::LEADSTATES, 'leadsource_id' => Crm_Model_Config::LEADSOURCES, 'leadtype_id' => Crm_Model_Config::LEADTYPES);
$col = $pagination->sort;
if (isset($virtualSortColumns[$col])) {
$settings = Crm_Controller::getInstance()->getConfigSettings();
$setting = $settings->{$virtualSortColumns[$col]};
// create cases (when => then) for sql switch (CASE) command
$cases = array();
foreach ($setting as $settingRecord) {
$cases[$settingRecord['id']] = $settingRecord[str_replace('_id', '', $col)];
}
$foreignSortCase = $this->_dbCommand->getSwitch($col, $cases);
$select->columns(array('foreignSortCol' => $foreignSortCase));
$pagination->sort = 'foreignSortCol';
}
}
/**
* Returns an array of objects queried from the given t41_Object_Collection instance parameters
*
* The given collection is populated if it comes empty of members.
*
* In any other case, this method doesn't directly populate the collection. This action is under the responsability of
* the caller. For example, the t41_Object_Collection::find() method takes care of it.
*
* @param t41\ObjectModel\Collection $collection
* @param boolean|array $returnCount true = counting, array = stats on listed properties
* @param string $subOp complex operation like SUM or AVG
* @return array
*/
public function find(ObjectModel\Collection $collection, $returnCount = false, $subOp = null)
{
$this->_class = $class = $collection->getDataObject()->getClass();
$table = $this->_getTableFromClass($class);
if (!$table) {
throw new Exception('MISSING_DBTABLE_PARAM');
}
// primary key is either part of the mapper configuration or 'id'
$pkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : \t41\Backend::DEFAULT_PKEY;
if (is_array($pkey)) {
$composite = array();
/* @var $obj t41\Backend\Key */
foreach ($pkey as $obj) {
$composite[] = sprintf('TRIM(%s)', $table . '.' . $obj->getName());
$composite[] = Backend\Mapper::VALUES_SEPARATOR;
}
$pkey = sprintf("CONCAT(%s) AS %s", implode(',', $composite), Backend::DEFAULT_PKEY);
} else {
$pkey = $table . '.' . $pkey;
}
$this->_connect();
/* @var $select \Zend_Db_Select */
$this->_select = $this->_ressource->select();
// detect if query is of stat-kind
if ($returnCount) {
switch ($subOp) {
case ObjectModel::CALC_SUM:
$expressions = array();
foreach ($returnCount as $propKey => $property) {
$prop = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $propKey) : $propKey;
$expressions[] = sprintf('SUM(%s.%s)', $table, $prop);
}
$subOpExpr = implode('+', $expressions);
break;
case ObjectModel::CALC_AVG:
$subOpExpr = sprintf('AVG(%s)', $returnCount);
break;
default:
$subOpExpr = 'COUNT(*)';
break;
}
$this->_select->from($table, new \Zend_Db_Expr($subOpExpr . " AS " . \t41\Backend::MAX_ROWS_IDENTIFIER));
} else {
$this->_select->distinct();
$this->_select->from($table, $pkey);
}
$this->_alreadyJoined = array();
/* @var $condition t41\Backend\Condition */
foreach ($collection->getConditions() as $conditionArray) {
// combo conditions
if ($conditionArray[0] instanceof Condition\Combo) {
$statement = array();
foreach ($conditionArray[0]->getConditions() as $condition) {
$statement[] = $this->_parseCondition($condition[0], $this->_select, $table);
}
$statement = implode(' OR ', $statement);
switch ($conditionArray[1]) {
case Condition::MODE_OR:
$this->_select->orWhere($statement);
break;
case Condition::MODE_AND:
default:
$this->_select->where($statement);
break;
}
continue;
}
// optional table where the column may be
$jtable = '';
// condition object is in the first key
$condition = $conditionArray[0];
/* does condition contain another condition object ? */
if ($condition->isRecursive()) {
while ($condition->isRecursive()) {
$property = $condition->getProperty();
$parent = $property->getParent() ? $property->getParent()->getId() : $table;
$condition = $condition->getCondition();
if ($jtable) {
$parentTable = $jtable;
} else {
if ($parent) {
$parentTable = $this->_mapper ? $this->_mapper->getDatastore($parent) : $parent;
} else {
$parentTable = $table;
}
}
$jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
/* column name in left table */
$jlkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId();
$uniqext = $jtable . '__joined_for__' . $jlkey;
if (in_array($uniqext, $this->_alreadyJoined)) {
$class = $property->getParameter('instanceof');
$jtable = $uniqext;
continue;
}
/* pkey name in joined table */
$jpkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$join = sprintf("%s.%s = %s.%s", $parentTable, $jlkey, $uniqext, $jpkey);
$this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array());
$this->_alreadyJoined[$jtable] = $uniqext;
//$jtable;
$jtable = $uniqext;
$class = $property->getParameter('instanceof');
}
}
$property = $condition->getProperty();
if ($property instanceof Property\ObjectProperty) {
// no join if object is stored in a different backend !
// @todo improve this part
if (ObjectModel::getObjectBackend($property->getParameter('instanceof'))->getAlias() != $this->_uri->getAlias()) {
$clauses = $condition->getClauses();
if ($clauses[0]['value'] != Condition::NO_VALUE) {
$clauses[0]['operator'] = Condition::OPERATOR_ENDSWITH | Condition::OPERATOR_EQUAL;
$condition->setClauses($clauses);
}
$field = $this->_mapper ? $this->_mapper->propertyToDatastoreName($this->_class, $property->getId()) : $property->getId();
} else {
// which table to join with ? (in case of condition is last element of a recursion)
$jtable2 = $jtable ? $jtable : $table;
$jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId();
$field = $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$uniqext = $jtable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
$join = sprintf("%s.%s = %s.%s", $jtable2, $leftkey, $uniqext, is_array($rightkey) ? $rightkey[0] : $rightkey);
$this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array());
$this->_alreadyJoined[$jtable] = $uniqext;
}
$jtable = $uniqext;
}
} else {
if ($property instanceof Property\CollectionProperty) {
// handling of conditions based on collection limited to withMembers() and withoutMembers()
$leftkey = $property->getParameter('keyprop');
$field = $property->getId();
$subSelect = $this->_ressource->select();
$subseltbl = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
$subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey)));
$join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey);
$subSelect->where($join);
$statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]);
$this->_select->where($statement);
continue;
} else {
$field = $property->getId();
if ($this->_mapper) {
$field = $this->_mapper->propertyToDatastoreName($class, $field);
}
}
}
/* convert identifier tag to the valid primary key */
if ($field == ObjectUri::IDENTIFIER) {
// @todo handle multiple keys from mapper
$field = $table . '.';
$key = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$field .= is_array($key) ? $key[0] : $key;
} else {
if ($jtable) {
if (array_key_exists($jtable, $this->_alreadyJoined)) {
$field = $this->_alreadyJoined[$jtable] . '.' . $field;
} else {
$tmp = $jtable . '.';
$tmp .= is_array($field) ? $field[0] : $field;
$field = $tmp;
}
} else {
if (array_key_exists($table, $this->_alreadyJoined)) {
$field = $this->_alreadyJoined[$table] . '.' . $field;
} else {
$field = $table . '.' . $field;
}
}
}
if ($field instanceof Key) {
$field = $table . '.' . $field->getName();
}
// protect DateProperty() with setted timepart parameter from misuse
if ($property instanceof DateProperty && $property->getParameter('timepart') == true) {
$field = "DATE({$field})";
}
$statement = $this->_buildConditionStatement($field, $condition->getClauses(), $conditionArray[1]);
switch ($conditionArray[1]) {
case Condition::MODE_OR:
$this->_select->orWhere($statement);
break;
case Condition::MODE_AND:
default:
$this->_select->where($statement);
break;
}
}
// Adjust query based on returnCount
if ($returnCount) {
if (is_array($returnCount)) {
if ($subOp) {
} else {
// return count on grouped columns
foreach ($returnCount as $key => $property) {
$fieldmodifier = null;
if ($this->_mapper) {
$class = $property->getParent() ? $property->getParent()->getId() : $collection->getDataObject()->getClass();
$field = $this->_mapper->propertyToDatastoreName($class, $property->getId());
} else {
$field = $property->getId();
}
if ($property instanceof ObjectProperty) {
// join with $key if necessary
if (strstr($key, '.') !== false) {
$leftPart = substr($key, 0, strpos($key, '.'));
$intermediateProp = $collection->getDataObject()->getProperty($leftPart);
$fieldmodifier = $this->_join($intermediateProp, $table) . '.' . $field;
}
}
// limit date grouping to date part, omitting possible hour part
if ($property instanceof DateProperty) {
$fieldmodifier = "DATE({$field})";
}
$this->_select->group($fieldmodifier ? $fieldmodifier : $field);
$this->_select->columns(array($field => $fieldmodifier ? $fieldmodifier : $field));
}
}
} else {
$this->_select->reset('group');
}
} else {
$this->_select->limit($collection->getBoundaryBatch() != -1 ? $collection->getBoundaryBatch() : null, $collection->getBoundaryOffset());
/**
* Sorting part
*/
foreach ($collection->getSortings() as $sorting) {
$slUniqext = $slTable = null;
// Specific cases first
// @todo find a better way to sort on meta properties
if ($sorting[0]->getId() == ObjectUri::IDENTIFIER || $sorting[0] instanceof MetaProperty) {
$id = Backend::DEFAULT_PKEY;
$this->_select->order(new \Zend_Db_Expr($table . '.' . $id . ' ' . $sorting[1]));
continue;
} else {
if ($sorting[0] instanceof Property\CollectionProperty) {
// handling of conditions based on collection limited to withMembers() and withoutMembers()
$leftkey = $sorting[0]->getParameter('keyprop');
//$field = $property->getId();
$subSelect = $this->_ressource->select();
$subseltbl = $this->_mapper ? $this->_mapper->getDatastore($sorting[0]->getParameter('instanceof')) : $this->_getTableFromClass($sorting[0]->getParameter('instanceof'));
$subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey)));
$join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey);
$subSelect->where($join);
// $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]);
$this->_select->order(new \Zend_Db_Expr('(' . $subSelect->__toString() . ') ' . $sorting[1]));
continue;
} else {
if ($sorting[0] instanceof Property\ObjectProperty) {
// find which property to sort by
if ($sorting[0]->getParameter('sorting')) {
$sprops = array_keys($sorting[0]->getParameter('sorting'));
} else {
// try to sort with properties used to display value
if (substr($sorting[0]->getParameter('display'), 0, 1) == '[') {
// @todo extract elements of pattern to order from them ?
$sprops = array('id');
} else {
$sprops = explode(',', $sorting[0]->getParameter('display'));
}
}
// sorting property belongs to a second-level join
if ($sorting[0]->getParent()->getClass() != $collection->getClass()) {
$leftkey = 'commande';
//$this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getParent()getId()) : $sorting[0]->getId();
$class = $sorting[0]->getParent()->getClass();
$stable = $this->_getTableFromClass($class);
$sbackend = ObjectModel::getObjectBackend($class);
// Property to sort from is in a different backend from current one
if ($sbackend->getAlias() != $this->getAlias()) {
// We presume that the current backend is allowed to connect to the remote one
// Should we raise an exception instead ?
$stable = $sbackend->getUri()->getDatabase() . '.' . $stable;
}
$field = $sorting[0]->getId();
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
if (is_array($rightkey)) {
foreach ($rightkey as $rightkeyObj) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName());
}
} else {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey);
}
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
$slTable = $this->_getTableFromClass($sorting[0]->getParameter('instanceof'));
$slUniqext = $uniqext;
}
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getId()) : $sorting[0]->getId();
$class = $sorting[0]->getParameter('instanceof');
$stable = isset($slTable) ? $slTable : $this->_getTableFromClass($class);
$sbackend = ObjectModel::getObjectBackend($class);
// Property to sort from is in a different backend from current one
if ($sbackend->getAlias() != $this->getAlias()) {
// We presume that the current backend is allowed to connect to the remote one
// Should we raise an exception instead ?
$stable = $sbackend->getUri()->getDatabase() . '.' . $stable;
}
$field = $sorting[0]->getId();
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
if (is_array($rightkey)) {
foreach ($rightkey as $rightkeyObj) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName());
}
} else {
$join = sprintf("%s.%s = %s.%s", isset($slUniqext) ? $slUniqext : $table, $leftkey, $uniqext, $rightkey);
}
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
foreach ($sprops as $sprop) {
if ($this->_mapper) {
$sfield = $this->_mapper->propertyToDatastoreName($class, $sprop);
} else {
$sfield = $sprop;
}
$sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield;
if (isset($sorting[2]) && !empty($sorting[2])) {
$sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr);
}
$this->_select->order(new \Zend_Db_Expr($sortingExpr . ' ' . $sorting[1]));
}
continue;
}
}
}
// default sorting on a different table
$class = $sorting[0]->getParent() ? $sorting[0]->getParent()->getClass() : $collection->getDataObject()->getClass();
$stable = $this->_getTableFromClass($class);
if ($this->_mapper) {
$sfield = $this->_mapper->propertyToDatastoreName($class, $sorting[0]->getId());
} else {
$field = $sorting[0];
$sfield = $field->getId();
}
// add a left join if the sorting field belongs to a table not yet part of the query
if ($stable != $table) {
// get the property id from the class name
$tfield = isset($sorting[3]) ? $sorting[3] : $collection->getDataObject()->getObjectPropertyId($class);
$leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $tfield) : $tfield;
$rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($field->getParameter('instanceof')) : Backend::DEFAULT_PKEY;
$uniqext = $stable . '__joined_for__' . $leftkey;
if (!in_array($uniqext, $this->_alreadyJoined)) {
$join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey);
$this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array());
$this->_alreadyJoined[$stable] = $uniqext;
}
$sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield;
} else {
$sortingExpr = $stable . '.' . $sfield;
}
if (isset($sorting[2]) && !empty($sorting[2])) {
$sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr);
}
$this->_select->order(new \Zend_Db_Expr('TRIM(' . $sortingExpr . ') ' . $sorting[1]));
}
}
$result = array();
$context = array('table' => $table);
try {
if (true && $returnCount == false) {
$this->_select->columns($this->_getColumns($collection->getDataObject()));
}
$result = $this->_ressource->fetchAll($this->_select);
} catch (\Zend_Db_Exception $e) {
$context['error'] = $e->getMessage();
$this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context);
return false;
}
$this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context);
if ($returnCount !== false) {
return is_array($returnCount) ? $result : $result[0][Backend::MAX_ROWS_IDENTIFIER];
}
// convert array of primary keys to strings
foreach ($result as $key => $val) {
// $result[$key] = implode(Backend\Mapper::VALUES_SEPARATOR, $val);
}
/* prepare base of object uri */
$uri = new ObjectModel\ObjectUri();
$uri->setBackendUri($this->_uri);
$uri->setClass($collection->getDataObject()->getClass());
$uri->setUrl($this->_database . '/' . $table . '/');
return $collection->populate($result, $uri);
//return $this->_populateCollection($result, $collection, $uri);
}
/**
* 选择合适的游戏类型
*
* @param Zend_Db_Select $select
* @param boolean $group
* @return Zend_Db_Select
*/
protected function _selectGameTypes(Zend_Db_Select $select, $group = false)
{
$tableName = $this->_tableDAO->info('name');
$column = (strstr($tableName, '_', true) ?: $tableName) . '_gametype';
if (is_array(self::$_allowedGameTypes) && is_array($this->_gameTypes)) {
$gameTypes = array_intersect($this->_gameTypes, self::$_allowedGameTypes);
} else {
if (is_array(self::$_allowedGameTypes)) {
$gameTypes = self::$_allowedGameTypes;
} else {
if (is_array($this->_gameTypes)) {
$gameTypes = $this->_gameTypes;
}
}
}
if (isset($gameTypes) && !empty($gameTypes)) {
count($gameTypes) === 1 ? $select->where("{$column} = ?", $gameTypes) : $select->where("{$column} IN (?)", $gameTypes);
}
if (true === $group) {
if (!in_array(ZtChart_Model_Assemble::GAMETYPE, $this->_merge)) {
array_unshift($this->_merge, ZtChart_Model_Assemble::GAMETYPE);
}
$select->columns("{$column} AS " . ZtChart_Model_Assemble::GAMETYPE)->group(ZtChart_Model_Assemble::GAMETYPE);
}
return $select;
}
/**
* Get category collection array
*
* @return array
*/
public function getCollection($storeId, $onlyCount = false, $limit = 4000000000.0, $from = 0)
{
$products = array();
$store = Mage::app()->getStore($storeId);
/* @var $store Mage_Core_Model_Store */
if (!$store) {
return false;
}
$read = $this->_getReadAdapter();
$this->_select = $read->select()->distinct()->from(array('e' => $this->getMainTable()), array($onlyCount ? 'COUNT(*)' : $this->getIdFieldName()))->join(array('w' => $this->getTable('catalog/product_website')), 'e.entity_id=w.product_id', array())->where('w.website_id=?', $store->getWebsiteId())->limit($limit, $from);
$excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_sitemap');
if ($excludeAttr) {
$this->_select->joinLeft(array('exclude_tbl' => $excludeAttr->getBackend()->getTable()), 'exclude_tbl.entity_id = e.entity_id AND exclude_tbl.attribute_id = ' . $excludeAttr->getAttributeId() . ' AND exclude_tbl.store_id = 0', array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL');
}
$this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in');
$this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in');
if ($onlyCount) {
return $read->fetchOne($this->_select);
}
$productCanonicalUrl = Mage::getStoreConfig('mageworx_seo/seosuite/product_canonical_url');
$suffix = '';
if ($productCanonicalUrl) {
$suffix = "AND canonical_url_rewrite.category_id IS NOT NULL";
$suffix2 = "AND category_id IS NOT NULL";
}
if ($productCanonicalUrl == 1) {
$sort = 'DESC';
} else {
if ($productCanonicalUrl == 2) {
$sort = 'ASC';
} else {
$sort = '';
}
}
$canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url');
$urlPathAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('url_path');
if ($canonicalAttr) {
$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("(SELECT `request_path` \n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)")));
}
$crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain');
if ($crossDomainAttr) {
$this->_select->joinLeft(array('cross_domain_tbl' => $crossDomainAttr->getBackend()->getTable()), 'cross_domain_tbl.entity_id = e.entity_id AND cross_domain_tbl.attribute_id = ' . $crossDomainAttr->getAttributeId(), array('canonical_cross_domain' => 'cross_domain_tbl.value'));
}
$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 ($productCanonicalUrl == 3) {
// use root
$urlArr = explode('/', $product->getUrl());
$product->setUrl(end($urlArr));
}
$products[$product->getId()] = $product;
}
return $products;
}
public static function whereProximity(Zend_Db_Select $select, $centerLat, $centerLong, $fieldLat, $fieldLong, $distance, $calculatedDistanceAs = "calcDistance")
{
self::init();
if (empty($centerLat) || empty($centerLong) || empty($fieldLat) || empty($fieldLong) || empty($distance)) {
return $select;
}
$centerLat = floatval($centerLat);
$centerLong = floatval($centerLong);
$distance = floatval($distance);
if ($distance >= 0 && $distance <= 12450.775) {
$longOffset = $distance / abs(cos(deg2rad($centerLat)) * 69);
$rectLong1 = $centerLong - $longOffset;
$rectLong2 = $centerLong + $longOffset;
$latOffset = $distance / 69;
$rectLat1 = $centerLat - $latOffset;
$rectLat2 = $centerLat + $latOffset;
/**
* @author Pavel Shutin
*/
$select->where("{$fieldLat} >= ? ", $rectLat1)->where("{$fieldLat} <= ? ", $rectLat2)->where("{$fieldLong} >= ? ", $rectLong1)->where("{$fieldLong} <= ? ", $rectLong2);
}
if (!empty($calculatedDistanceAs)) {
/* Distance Formula : 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) * pi()/180 / 2), 2) + COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))as distance */
$earthDiameter = 7912;
$piOver180 = 0.01745329;
$select->columns(array($calculatedDistanceAs => new Zend_Db_Expr("{$earthDiameter} * ASIN(SQRT( POWER(SIN(({$centerLat} - {$fieldLat}) * {$piOver180} / 2), 2) + COS({$centerLat} * {$piOver180}) * COS({$fieldLat} * {$piOver180}) * POWER(SIN(({$centerLong} - {$fieldLong}) * {$piOver180} / 2), 2) ))")));
/**
* @author Pavel Shutin
*/
$select->having($calculatedDistanceAs . ' <= ?', $distance);
}
//var_dump($select->__toString());exit;
return $select;
}
/**
* Get category collection array
*
* @return array
*/
public function getCollection($storeId, $onlyCount = false, $limit = 4000000000.0, $from = 0)
{
$products = array();
$store = Mage::app()->getStore($storeId);
/* @var $store Mage_Core_Model_Store */
if (!$store) {
return false;
}
$read = $this->_getReadAdapter();
// $useCategories = Mage::getStoreConfigFlag('catalog/seo/product_use_categories');
// $urCondions = array(
// 'e.entity_id=ur.product_id',
// //'ur.category_id IS ' . ($useCategories ? 'NOT' : '') . ' NULL',
// $read->quoteInto('ur.store_id=?', $store->getId()),
// $read->quoteInto('ur.is_system=?', 1),
// );
$this->_select = $read->select()->distinct()->from(array('e' => $this->getMainTable()), array($onlyCount ? 'COUNT(*)' : $this->getIdFieldName()))->join(array('w' => $this->getTable('catalog/product_website')), 'e.entity_id=w.product_id', array())->where('w.website_id=?', $store->getWebsiteId())->limit($limit, $from);
$excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_sitemap');
if ($excludeAttr) {
$this->_select->joinLeft(array('exclude_tbl' => $excludeAttr->getBackend()->getTable()), 'exclude_tbl.entity_id = e.entity_id AND exclude_tbl.attribute_id = ' . $excludeAttr->getAttributeId() . ' AND exclude_tbl.store_id = 0', array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL');
}
$this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in');
$this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in');
if ($onlyCount) {
return $read->fetchOne($this->_select);
}
$productCanonicalUrl = Mage::getStoreConfig('mageworx_seo/seosuite/product_canonical_url');
// $useLongest = ($productCanonicalUrl==1) ? true : false;
if ($productCanonicalUrl == 1) {
$sort = 'DESC';
} else {
if ($productCanonicalUrl == 2) {
$sort = 'ASC';
} else {
$sort = '';
}
}
$canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url');
if ($canonicalAttr) {
$this->_select->columns(array('url' => new Zend_Db_Expr("IFNULL((SELECT canonical_url_rewrite.`request_path`\n FROM `" . $canonicalAttr->getBackend()->getTable() . "` AS canonical_path\n LEFT JOIN `" . $this->getTable('core/url_rewrite') . "` AS canonical_url_rewrite ON canonical_url_rewrite.`id_path` = canonical_path.`value`\n WHERE canonical_path.`entity_id` = e.`entity_id` AND canonical_path.`attribute_id` = " . $canonicalAttr->getAttributeId() . ($sort ? " ORDER BY LENGTH(canonical_url_rewrite.`request_path`) " . $sort : "") . " LIMIT 1),\n (SELECT `request_path` \n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1) \n )")));
// $this->_select->joinLeft(
// array('ur' => $this->getTable('core/url_rewrite')),
// join(' AND ', $urCondions),
// array('url' => 'IFNULL(`canonical_url_rewrite`.`request_path`, `ur`.`request_path`)')
// )
// ->joinLeft(
// array('canonical_path' => $canonicalAttr->getBackend()->getTable()),
// 'canonical_path.entity_id = e.entity_id AND canonical_path.attribute_id = ' . $canonicalAttr->getAttributeId() ,
// array()
// )
// ->joinLeft(
// array('canonical_url_rewrite' => $this->getTable('core/url_rewrite')),
// '`canonical_url_rewrite`.`id_path` = `canonical_path`.value',
// array()
// )
// ->where('ur.request_path IS NOT NULL OR `canonical_url_rewrite`.`request_path` IS NOT NULL');
} else {
$this->_select->columns(array('url' => new Zend_Db_Expr("(SELECT `request_path` \n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)")));
// $this->_select->joinLeft(
// array('ur' => $this->getTable('core/url_rewrite')),
// join(' AND ', $urCondions),
// array('url' => 'ur.request_path')
// )
// ->where('ur.request_path IS NOT NULL');
}
$crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain');
if ($crossDomainAttr) {
$this->_select->joinLeft(array('cross_domain_tbl' => $crossDomainAttr->getBackend()->getTable()), 'cross_domain_tbl.entity_id = e.entity_id AND cross_domain_tbl.attribute_id = ' . $crossDomainAttr->getAttributeId(), array('canonical_cross_domain' => 'cross_domain_tbl.value'));
}
//echo $this->_select->assemble(); exit;
$query = $read->query($this->_select);
while ($row = $query->fetch()) {
$product = $this->_prepareProduct($row);
// if (isset($products[$product->getId()])) {
// if (($useLongest && strlen($product->getUrl()) < strlen($products[$product->getId()]->getUrl()))
// || (!$useLongest && strlen($product->getUrl()) > strlen($products[$product->getId()]->getUrl()))) {
// $product->setUrl($products[$product->getId()]->getUrl());
// }
// }
if ($productCanonicalUrl == 3) {
// use root
$urlArr = explode('/', $product->getUrl());
$product->setUrl(end($urlArr));
}
$products[$product->getId()] = $product;
}
return $products;
}
/**
* remove column from select to avoid duplicates
*
* @param Zend_Db_Select $_select
* @param array|string $_cols
* @param string $_column
*/
protected function _removeColFromSelect(Zend_Db_Select $_select, &$_cols, $_column)
{
if (!is_array($_cols)) {
return;
}
foreach ($_cols as $name => $correlation) {
if ($name == $_column) {
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Removing ' . $_column . ' from columns.');
}
unset($_cols[$_column]);
$_select->reset(Zend_Db_Select::COLUMNS);
$_select->columns($_cols);
}
}
}
/**
* Public service for grouping treatment
*
* @param Zend_Db_Select $select
*/
public static function traitGroup(Zend_Db_Select $select)
{
// not needed for MySQL backends
if ($select->getAdapter() instanceof Zend_Db_Adapter_Pdo_Mysql) {
return;
}
$group = $select->getPart(Zend_Db_Select::GROUP);
if (empty($group)) {
return;
}
$columns = $select->getPart(Zend_Db_Select::COLUMNS);
$updatedColumns = array();
//$column is an array where 0 is table, 1 is field and 2 is alias
foreach ($columns as $key => $column) {
if ($column[1] instanceof Zend_Db_Expr) {
if (preg_match('/^\\(.*\\)/', $column[1])) {
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
} else {
$updatedColumns[] = $column;
}
continue;
}
if (preg_match('/^\\(.*\\)/', $column[1])) {
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
continue;
}
// resolve * to single columns
if ($column[1] == '*') {
$tableFields = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $column[0], $select->getAdapter());
foreach ($tableFields as $columnName => $schema) {
// adds columns into group by clause (table.field)
// checks if field has a function (that must be an aggregation)
$fieldName = "{$column[0]}.{$columnName}";
if (in_array($fieldName, $group)) {
$updatedColumns[] = array($column[0], $fieldName, $columnName);
} else {
// any selected field which is not in the group by clause must have an aggregate function
// we choose MIN() as default. In practice the affected columns will have only one value anyways.
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $columnName);
}
}
continue;
}
$fieldName = $column[0] . '.' . $column[1];
if (in_array($fieldName, $group)) {
$updatedColumns[] = $column;
} else {
// any selected field which is not in the group by clause must have an aggregate function
// we choose MIN() as default. In practice the affected columns will have only one value anyways.
$updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $column[2] ? $column[2] : $column[1]);
}
}
$select->reset(Zend_Db_Select::COLUMNS);
foreach ($updatedColumns as $column) {
$select->columns(!empty($column[2]) ? array($column[2] => $column[1]) : $column[1], $column[0]);
}
// add order by columns to group by
$order = $select->getPart(Zend_Db_Select::ORDER);
foreach ($order as $column) {
$field = $column[0];
if (preg_match('/.*\\..*/', $field) && !in_array($field, $group)) {
// adds column into group by clause (table.field)
$group[] = $field;
}
}
$select->reset(Zend_Db_Select::GROUP);
$select->group($group);
}
/**
* @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;
}
/**
* Add store price join
*
* @param Zend_Db_Select $select
*
* @return Innoexts_StorePricing_Model_Mysql4_Catalogrule_Rule
*/
protected function addStorePriceJoin($select)
{
$helper = $this->getStorePricingHelper();
$productPriceHelper = $this->getProductPriceHelper();
$tableAlias = 'pp_store';
$fieldAlias = 'store_price';
$storeId = new Zend_Db_Expr('rp.store_id');
$select->joinLeft(array($tableAlias => $productPriceHelper->getPriceAttributeTable()), sprintf($this->getPriceJoinCondition(), $tableAlias, $storeId), array());
$price = new Zend_Db_Expr($tableAlias . '.value');
$select->columns(array($fieldAlias => $price));
return $this;
}
protected function _prepareSelect(Zend_Db_Select $select)
{
$fromPart = $select->getPart(Zend_Db_Select::FROM);
// the recipient table is required!
if (!isset($fromPart['recipient'])) {
return false;
}
foreach ($this->_where as $where) {
$select->where($where);
}
/*
$select->where('`recipient`.`sent_at` IS NOT NULL');
if($this->_campaignFilter !== null) {
$select->where('`recipient`.`campaign_id` = ?', $this->_campaignFilter);
}
if($this->_variationFilter !== null) {
$select->where('`recipient`.`variation_id` = ?', $this->_variationFilter);
}
*/
$select->columns($this->_columns, 'recipient');
return true;
}
