/**
* Join information for last staging logs
*
* @param string $table
* @param Varien_Db_Select $select
* @return Varien_Db_Select $select
*/
public function getLastStagingLogQuery($table, $select)
{
$subSelect = clone $select;
$subSelect->from($table, array('staging_id', 'log_id', 'action'))->order('log_id DESC');
$select->from(array('t' => new Zend_Db_Expr('(' . $subSelect . ')')))->group('staging_id');
return $select;
}
protected function applyMultipleValuesFilter($ids)
{
$collection = $this->getLayer()->getProductCollection();
$attribute = $this->getAttributeModel();
$table = Mage::getSingleton('core/resource')->getTableName('catalogindex/eav');
//check for prefix
$helper = Mage::helper('adjnav');
$alias = 'attr_index_' . $attribute->getId();
$collection->getSelect()->join(array($alias => $table), $alias . '.entity_id=e.entity_id', array())->where($alias . '.store_id = ?', Mage::app()->getStore()->getId())->where($alias . '.attribute_id = ?', $attribute->getId())->where($alias . '.value IN (?)', $ids);
if (is_array($ids) && ($size = count($ids))) {
$adapter = $collection->getConnection();
$subQuery = new Varien_Db_Select($adapter);
$subQuery->from(array('e' => Mage::getModel('catalog/product')->getResource()->getTable('catalog/product')), 'entity_id')->join(array('a' => Mage::getModel('catalog/product')->getResource()->getTable('catalog/product_index_eav')), 'a.entity_id = e.entity_id', array());
$SBBStatus = $helper->getShopByBrandsStatus();
$forbidConfigurables = $SBBStatus && Mage::helper('aitmanufacturers')->canUseLayeredNavigation(Mage::registry('shopby_attribute'), true);
if (!$forbidConfigurables) {
$subQuery->where('e.type_id != ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE);
}
$subQuery->where('a.store_id = ?', Mage::app()->getStore()->getId())->where('a.attribute_id = ?', $attribute->getId())->where('a.value IN (?)', $ids)->group(array('a.entity_id', 'a.attribute_id', 'a.store_id'));
$res = $adapter->fetchCol($subQuery);
/**
* @author ksenevich@aitoc.com
*/
self::_addFilterValues($attribute->getId(), $res, $ids);
}
if (count($ids) > 1) {
$collection->getSelect()->distinct(true);
}
return $this;
}
protected function applyMultipleValuesFilter($ids)
{
$collection = $this->getLayer()->getProductCollection();
$attribute = $this->getAttributeModel();
$table = Mage::getSingleton('core/resource')->getTableName('catalogindex/eav');
//check for prefix
$alias = 'attr_index_' . $attribute->getId();
$collection->getSelect()->join(array($alias => $table), $alias . '.entity_id=e.entity_id', array())->where($alias . '.store_id = ?', Mage::app()->getStore()->getId())->where($alias . '.attribute_id = ?', $attribute->getId())->where($alias . '.value IN (?)', $ids);
switch (Mage::getStoreConfig('design/adjnav/filtering_logic')) {
case 'AND':
if (is_array($ids) && ($size = count($ids))) {
$adapter = $collection->getConnection();
$adapter = $collection->getConnection();
$subQuery = new Varien_Db_Select($adapter);
$subQuery->from(Mage::getResourceModel('catalogindex/attribute')->getMainTable(), 'entity_id')->where('store_id = ?', Mage::app()->getStore()->getId())->where('attribute_id = ?', $attribute->getId())->where('value IN (?)', $ids)->group(array('entity_id', 'attribute_id', 'store_id'))->having($size . ' = COUNT(value)');
$res = $adapter->fetchCol($subQuery);
if ($res) {
$collection->getSelect()->where($alias . '.entity_id IN (?)', $res);
} else {
$collection->getSelect()->where($alias . '.entity_id IN (-1)');
}
}
break;
case 'OR':
default:
break;
}
if (count($ids) > 1) {
$collection->getSelect()->distinct(true);
}
return $this;
}
public function export(Mage_Core_Model_Store $oStore, $vFeedname, Mage_Core_Model_Config_Element $oConfig)
{
Mage::getSingleton('aligent_feeds/log')->log("Beginning {$vFeedname} export for store #" . $oStore->getId() . " - " . $oStore->getName());
Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
Mage::getSingleton('aligent_feeds/log')->log("Initialising file writers...");
$this->_initWriters($oStore, $vFeedname, $oConfig);
// Prepare the csv file header
Mage::getSingleton('aligent_feeds/log')->log("Begin preparing header rows...");
Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
$this->_prepareHeaders($oConfig);
// Initialise the formatter
Mage::getSingleton('aligent_feeds/log')->log("Initialising Feed Formatter...");
Mage::getSingleton('aligent_feeds/feed_formatter')->init($oStore, $oConfig);
Mage::getSingleton('aligent_feeds/log')->log("Initialised Feed Formatter.");
Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
$oConn = Mage::getModel('core/resource')->getConnection('catalog_read');
$vCategoryProductTable = Mage::getModel('core/resource_setup', 'core_setup')->getTable('catalog/category_product');
$vCategoryFlatTable = Mage::getResourceSingleton('catalog/category_flat')->getMainStoreTable($oStore->getId());
$vProductFlatTable = Mage::getResourceModel('catalog/product_flat_indexer')->getFlatTableName($oStore->getId());
// Complicated subquery to get the most deeply nested category that this
// product is assigned to. Picking the most deeply nested on the assumption
// that the deepest category is most likely to be the most specific.
$oSubSelect = new Varien_Db_Select($oConn);
$oSubSelect->from(array('ccf' => $vCategoryFlatTable), 'entity_id')->joinInner(array('ccp2' => 'catalog_category_product'), 'ccf.entity_id=ccp2.category_id', array())->where('ccp2.product_id=main_table.entity_id')->where('ccf.is_active=1')->order('level', Zend_Db_Select::SQL_DESC)->limit(1);
$oSelect = new Varien_Db_Select($oConn);
$oSelect->from(array('main_table' => $vProductFlatTable), array('main_table.*', 'category_id' => new Zend_Db_Expr('(' . $oSubSelect . ')')))->where('visibility IN (?)', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG, Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH));
// Allow the feed definition to include a "before_query_filter". This method
// will be allowed to modify the query before it's executed.
if ($oConfig->before_query_filter) {
Mage::getSingleton('aligent_feeds/log')->log("Calling before query filter...");
$vClass = (string) $oConfig->before_query_filter->class;
$vMethod = (string) $oConfig->before_query_filter->method;
$aParams = (array) $oConfig->before_query_filter->params;
Mage::getSingleton($vClass)->{$vMethod}($oSelect, $oStore, $aParams);
Mage::getSingleton('aligent_feeds/log')->log("Before query filter done.");
}
Mage::getSingleton('aligent_feeds/log')->log("Exporting products...");
$oResource = Mage::getModel('core/resource_iterator')->walk($oSelect, array(function ($aArgs) {
Mage::getSingleton('aligent_feeds/log')->log("Exporting product #" . $aArgs['idx'] . " SKU: " . $aArgs['row']['sku'], Zend_Log::DEBUG, true);
if ($aArgs['idx'] % 100 == 0) {
Mage::getSingleton('aligent_feeds/log')->log("Exporting product #" . $aArgs['idx'] . "...", Zend_Log::INFO);
Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
}
$aRows = Mage::getSingleton('aligent_feeds/feed_formatter')->prepareRow($aArgs['row']);
if (count($aRows) > 0) {
foreach ($aRows as $aRow) {
foreach ($aArgs['writers'] as $oWriter) {
$oWriter->writeDataRow($aRow);
}
}
}
}), array('writers' => $this->_oWriters, 'config' => $oConfig, 'store' => $oStore));
$this->_closeWriters();
$this->_sendFeed();
Mage::getSingleton('aligent_feeds/status')->addSuccess("Generated {$vFeedname} data for store #" . $oStore->getId() . " - " . $oStore->getName());
Mage::getSingleton('aligent_feeds/log')->log("Finished {$vFeedname} data export for store #" . $oStore->getId() . " - " . $oStore->getName());
Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
return $this;
}
public function newAttributeSetsCollection()
{
$r = Mage::getSingleton('core/resource');
// all product attribute sets
$allProductSets = Mage::getResourceModel('eav/entity_attribute_set_collection')->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId());
// the sets already in the table
$model = new Varien_Db_Select(Mage::getResourceModel('listrak/product_attribute_set_map')->getReadConnection());
$model->from(array('current' => $r->getTableName('listrak/product_attribute_set_map')))->where('main_table.attribute_set_id = current.attribute_set_id');
// new product attribute sets
$allProductSets->getSelect()->where('NOT EXISTS (' . $model . ')');
return $allProductSets;
}
/**
* (non-PHPdoc)
* @see Zend_Db_Select::from()
*/
public function from($name, $cols = null, $schema = null)
{
if (is_array($name)) {
foreach ($name as $correlationName => $tableName) {
$this->_tableAlias = $correlationName;
break;
}
} else {
$name = array($this->_tableAlias => $name);
}
return parent::from($name, $cols, $schema);
}
/** Add filters after all filters have applied for configurable products
*
* @param Varien_Event_Observer $observer
* @author ksenevich@aitoc.com
*/
public function onCatalogProductCollectionLoadBefore(Varien_Event_Observer $observer)
{
/* @var $versionHelper AdjustWare_Nav_Helper_Version */
$versionHelper = Mage::helper('adjnav/version');
/* @var $collection Varien_Data_Collection_Db */
$collection = $observer->getEvent()->getCollection();
$adapter = $collection->getConnection();
$helper = Mage::helper('adjnav');
$filterAttributes = AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::getFilterAttributes();
$filterProducts = AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::getFilterProducts();
$configurableProducts = array();
$childByAttribute = array();
$child2parent = array();
$productModel = Mage::getModel('catalog/product')->getResource();
$attributesCount = count($filterAttributes);
if ($versionHelper->hasConfigurableFix()) {
foreach ($filterAttributes as $attributeId => $attributeValues) {
$configurableQuery = new Varien_Db_Select($adapter);
$configurableQuery->from(array('e' => $productModel->getTable('catalog/product')), 'entity_id')->join(array('l' => $productModel->getTable($versionHelper->getProductRelationTable())), 'l.parent_id = e.entity_id', array('child_id' => $versionHelper->getProductIdChildColumn()))->join(array('a' => Mage::getResourceModel('adjnav/catalog_product_indexer_configurable')->getMainTable()), 'a.entity_id = l.' . $versionHelper->getProductIdChildColumn(), array())->where('e.type_id = ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE)->where('a.store_id = ?', Mage::app()->getStore()->getId())->where('a.attribute_id = ?', $attributeId)->where('a.value IN (?)', $attributeValues)->group(array('e.entity_id', 'l.' . $versionHelper->getProductIdChildColumn(), 'a.store_id'));
$statement = $adapter->query($configurableQuery);
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$child2parent[$row['child_id']][] = $row['entity_id'];
$childByAttribute[$row['child_id']][$attributeId] = true;
}
}
foreach ($childByAttribute as $childId => $attributeIds) {
if (count($attributeIds) == $attributesCount) {
$configurableProducts[] = $childId;
foreach ($child2parent[$childId] as $parentId) {
$configurableProducts[] = $parentId;
}
}
}
}
$configurableProducts = array_unique($configurableProducts);
/* Commenting this section as it was causing issue */
/*
foreach ($filterProducts as $attributeId => $filterProducts)
{
$alias = 'attr_index_'.$attributeId;
$filterProducts = array_merge($filterProducts, $configurableProducts);
if (empty($filterProducts))
{
$filterProducts = array(-1);
}
$collection->getSelect()->where($alias.'.entity_id IN (?)', $filterProducts);
}
*/
AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::cleanFilterAttributes();
}
/**
* Clean logs
*
* @return Mage_Log_Model_Cron
*/
public function streamClean()
{
$adapter = Mage::getSingleton('core/resource')->getConnection('write');
$activityTableName = Mage::getSingleton('core/resource')->getTableName('activitystream/activity');
$select = new Varien_Db_Select($adapter);
$select->from($activityTableName)->reset(Zend_Db_Select::COLUMNS)->columns(array('id'))->order('id DESC')->limit(100);
try {
$liveActivityList = $adapter->fetchCol($select);
$adapter->delete($activityTableName, array('id NOT IN (?)' => $liveActivityList));
} catch (Exception $e) {
Mage::logException($e);
}
return $this;
}
public function testWhere()
{
$select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'5'"));
$select->from('test')->where('field = ?', 5);
$this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '5')", $select->assemble());
$select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "''"));
$select->from('test')->where('field = ?');
$this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '')", $select->assemble());
$select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'%?%'"));
$select->from('test')->where('field LIKE ?', '%value?%');
$this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%?%')", $select->assemble());
$select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(0));
$select->from('test')->where("field LIKE '%value?%'", null, Varien_Db_Select::TYPE_CONDITION);
$this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%value?%')", $select->assemble());
$select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'1', '2', '4', '8'"));
$select->from('test')->where("id IN (?)", array(1, 2, 4, 8));
$this->assertEquals("SELECT `test`.* FROM `test` WHERE (id IN ('1', '2', '4', '8'))", $select->assemble());
}
/**
* Retrieves a collection of all new attribute sets
*
* @return Mage_Eav_Model_Resource_Entity_Attribute_Set_Collection
*/
public function newAttributeSetsCollection()
{
/* @var Mage_Core_Model_Resource $resource */
$resource = Mage::getSingleton('core/resource');
/* @var Mage_Catalog_Model_Resource_Product $productResource */
$productResource = Mage::getModel('catalog/product')->getResource();
/* @var Mage_Eav_Model_Resource_Entity_Attribute_Set_Collection $sets */
$sets = Mage::getResourceModel('eav/entity_attribute_set_collection');
$sets->setEntityTypeFilter($productResource->getTypeId());
/* @var Listrak_Remarketing_Model_Mysql4_Product_Attribute_Set_Map $setResource */
$setResource = Mage::getResourceModel('listrak/product_attribute_set_map');
// the sets already in the table
$model = new Varien_Db_Select($setResource->getReadConnection());
$model->from(array('current' => $resource->getTableName('listrak/product_attribute_set_map')), array("*"))->where('main_table.attribute_set_id = current.attribute_set_id');
// new product attribute sets
$sets->getSelect()->where('NOT EXISTS (' . $model . ')');
return $sets;
}
/**
* @param Varien_Event_Observer $observer
* @return $this
*/
public function loadAttributeData($observer)
{
$attribute = $observer->getAttribute();
$attribute_id = (int) $attribute->getAttributeId();
$connection = Mage::getSingleton('core/resource')->getConnection('read');
$table = Mage::getSingleton('core/resource')->getTableName('ecommerceteam_sln_attribute_data');
$select = new Varien_Db_Select($connection);
$select->from($table, array('group_id', 'frontend_type', 'comment', 'options_limit'));
$select->where('attribute_id = ?', $attribute_id);
$data = $connection->fetchRow($select);
if ($data && is_array($data) && !empty($data)) {
$attribute->addData($data);
}
return $this;
}
public function getStorageSize()
{
if ($this->_storageSize === null) {
$adapter = $this->_getResource()->getReadConnection();
$config = $adapter->getConfig();
$select = new Varien_Db_Select($adapter);
$select->from('information_schema.TABLES', '')->columns('(data_length + index_length)')->where('table_schema = ?', $config['dbname'])->where('table_name = ?', $this->_getResource()->getTable('core/cache'));
$this->_storageSize = $adapter->fetchOne($select);
}
return $this->_storageSize / 1024 / 1024;
}
