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