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