protected function _prepareCollection() { /** @var $connRead Varien_Db_Adapter_Pdo_Mysql */ $connRead = Mage::getSingleton('core/resource')->getConnection('core_read'); // Prepare ebay main category // ---------------------------------- $ebayPrimarySelect = $connRead->select(); $ebayPrimarySelect->from(array('etc' => Mage::getModel('M2ePro/Ebay_Template_Category')->getResource()->getMainTable()))->reset(Zend_Db_Select::COLUMNS)->columns(array('category_main_mode as mode', new Zend_Db_Expr('IF (`category_main_mode` = ' . Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_EBAY . ', `category_main_id`, `category_main_attribute`) as `value`'), 'category_main_path as path', new Zend_Db_Expr(Ess_M2ePro_Helper_Component_Ebay_Category::TYPE_EBAY_MAIN . ' as `type`'), 'marketplace_id as marketplace', new Zend_Db_Expr('\'\' as `account`')))->where('category_main_mode != ?', Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_NONE)->group(array('mode', 'value', 'marketplace')); // ---------------------------------- // Prepare ebay secondary category // ---------------------------------- $ebaySecondarySelect = $connRead->select(); $ebaySecondarySelect->from(array('etc' => Mage::getModel('M2ePro/Ebay_Template_OtherCategory')->getResource()->getMainTable()))->reset(Zend_Db_Select::COLUMNS)->columns(array('category_secondary_mode as mode', new Zend_Db_Expr('IF (`category_secondary_mode` = ' . Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_EBAY . ', `category_secondary_id`, `category_secondary_attribute`) as `value`'), 'category_secondary_path as path', new Zend_Db_Expr(Ess_M2ePro_Helper_Component_Ebay_Category::TYPE_EBAY_SECONDARY . ' as `type`'), 'marketplace_id as marketplace', new Zend_Db_Expr('\'\' as `account`')))->where('category_secondary_mode != ?', Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_NONE)->group(array('mode', 'value', 'marketplace')); // ---------------------------------- // Prepare store main category // ---------------------------------- $storePrimarySelect = $connRead->select(); $storePrimarySelect->from(array('etc' => Mage::getModel('M2ePro/Ebay_Template_OtherCategory')->getResource()->getMainTable()))->reset(Zend_Db_Select::COLUMNS)->columns(array('store_category_main_mode as mode', new Zend_Db_Expr('IF (`store_category_main_mode` = ' . Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_EBAY . ', `store_category_main_id`, `store_category_main_attribute`) as `value`'), 'store_category_main_path as path', new Zend_Db_Expr(Ess_M2ePro_Helper_Component_Ebay_Category::TYPE_STORE_MAIN . ' as `type`'), new Zend_Db_Expr('\'\' as `marketplace`'), 'account_id as account'))->where('store_category_main_mode != ?', Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_NONE)->group(array('mode', 'value', 'account')); // ---------------------------------- // Prepare store secondary category // ---------------------------------- $categoryModeEbay = Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_EBAY; $categoryModeNone = Ess_M2ePro_Model_Ebay_Template_Category::CATEGORY_MODE_NONE; $storeSecondarySelect = $connRead->select(); $storeSecondarySelect->from(array('etc' => Mage::getModel('M2ePro/Ebay_Template_OtherCategory')->getResource()->getMainTable()))->reset(Zend_Db_Select::COLUMNS)->columns(array('store_category_secondary_mode as mode', new Zend_Db_Expr('IF (`store_category_secondary_mode` = ' . $categoryModeEbay . ', `store_category_secondary_id`, `store_category_secondary_attribute`) as `value`'), 'store_category_secondary_path as path', new Zend_Db_Expr(Ess_M2ePro_Helper_Component_Ebay_Category::TYPE_STORE_SECONDARY . ' as `type`'), new Zend_Db_Expr('\'\' as `marketplace`'), 'account_id as account'))->where('store_category_secondary_mode != ?', $categoryModeNone)->group(array('mode', 'value', 'account')); // ---------------------------------- // Prepare union select // ---------------------------------- $unionSelect = $connRead->select(); $unionSelect->union(array($ebayPrimarySelect, $ebaySecondarySelect, $storePrimarySelect, $storeSecondarySelect)); // ---------------------------------- // Prepare result collection // ---------------------------------- $resultCollection = new Varien_Data_Collection_Db($connRead); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect)); // ---------------------------------- // Join dictionary tables // ---------------------------------- $edcTable = Mage::getSingleton('core/resource')->getTableName('m2epro_ebay_dictionary_category'); $eascTable = Mage::getSingleton('core/resource')->getTableName('m2epro_ebay_account_store_category'); $resultCollection->getSelect()->joinLeft(array('edc' => $edcTable), 'edc.category_id = main_table.value AND edc.marketplace_id = main_table.marketplace'); $resultCollection->getSelect()->joinLeft(array('easc' => $eascTable), 'easc.category_id = main_table.value AND easc.account_id = main_table.account'); // ---------------------------------- // ---------------------------------- $resultCollection->getSelect()->reset(Zend_Db_Select::COLUMNS)->columns(array('mode', 'value', 'path', 'type', 'marketplace', 'account', 'edc.category_id as state_ebay', 'easc.category_id as state_store')); // ---------------------------------- // var_dump($resultCollection->getSelectSql(true)); exit; $this->setCollection($resultCollection); return parent::_prepareCollection(); }
/** * @param PHPUnit_Framework_MockObject_MockObject|Zend_Db_Adapter_Abstract $adapter * @depends testSetAddOrder */ public function testUnshiftOrder($adapter) { $this->_collection->setConnection($adapter); $this->_collection->addOrder('some_field', Varien_Data_Collection::SORT_ORDER_ASC); $this->_collection->unshiftOrder('other_field', Varien_Data_Collection::SORT_ORDER_ASC); $this->_collection->load(); $selectOrders = $this->_collection->getSelect()->getPart(Zend_Db_Select::ORDER); $this->assertEquals('other_field ASC', (string) array_shift($selectOrders)); $this->assertEquals('some_field ASC', (string) array_shift($selectOrders)); $this->assertEmpty(array_shift($selectOrders)); }
/** * Add throttle parameter to collection to limit output to X number of rows * * @param Varien_Data_Collection_Db $collection Collection of data which will be spit out as feed * @param int|string $throttle Number representing maximum record count which should be included in this feed generation run * @param int|string $minEntityId Number representing minimum value for entity Id to export - This acts as a placeholder for where the feed export left off */ protected function addThrottleFilter($collection, $throttle, $minEntityId) { // Add mim entity id filter if ($minEntityId > 0) { $collection->getSelect()->where("product_id >= {$minEntityId}"); } // Add throttle param if ($throttle > 0) { $collection->getSelect()->limit($throttle); } // Return the modified collection return $collection; }
protected function _prepareCollection() { // Get collection products in listing // --------------------------------------- $listingProductCollection = Mage::helper('M2ePro/Component_Buy')->getCollection('Listing_Product'); $listingProductCollection->getSelect()->distinct(); $listingProductCollection->getSelect()->join(array('l' => Mage::getResourceModel('M2ePro/Listing')->getMainTable()), '(`l`.`id` = `main_table`.`listing_id`)', array('listing_title' => 'title', 'store_id'))->join(array('bl' => Mage::getResourceModel('M2ePro/Buy_Listing')->getMainTable()), '(`bl`.`listing_id` = `l`.`id`)', array('template_selling_format_id')); // --------------------------------------- // Communicate with magento product table // --------------------------------------- $dbSelect = Mage::getResourceModel('core/config')->getReadConnection()->select()->from(Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar'), new Zend_Db_Expr('MAX(`store_id`)'))->where("`entity_id` = `main_table`.`product_id`")->where("`attribute_id` = `ea`.`attribute_id`")->where("`store_id` = 0 OR `store_id` = `l`.`store_id`"); $listingProductCollection->getSelect()->join(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('magento_sku' => 'sku'))->join(array('cisi' => Mage::getSingleton('core/resource')->getTableName('cataloginventory_stock_item')), '(cisi.product_id = `main_table`.product_id AND cisi.stock_id = 1)', array('is_in_stock'))->join(array('cpev' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar')), "(`cpev`.`entity_id` = `main_table`.product_id)", array('value'))->join(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), '(`cpev`.`attribute_id` = `ea`.`attribute_id` AND `ea`.`attribute_code` = \'name\')', array())->where('`cpev`.`store_id` = (' . $dbSelect->__toString() . ')'); // --------------------------------------- $listingProductCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingProductCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr('1'), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 'cpev.value', 'listing_title' => 'l.title', 'store_id' => 'l.store_id', 'account_id' => 'l.account_id', 'marketplace_id' => 'l.marketplace_id', 'listing_product_id' => 'main_table.id', 'product_id' => 'main_table.product_id', 'listing_id' => 'main_table.listing_id', 'status' => 'main_table.status', 'template_new_product_id' => 'second_table.template_new_product_id', 'general_id' => 'second_table.general_id', 'online_sku' => 'second_table.sku', 'online_qty' => 'second_table.online_qty', 'online_price' => 'second_table.online_price')); // --------------------------------------- $listingOtherCollection = Mage::helper('M2ePro/Component_Buy')->getCollection('Listing_Other'); $listingOtherCollection->getSelect()->distinct(); // add stock availability, type id, status & visibility to select // --------------------------------------- $listingOtherCollection->getSelect()->joinLeft(array('cisi' => Mage::getResourceModel('cataloginventory/stock_item')->getMainTable()), '(`cisi`.`product_id` = `main_table`.`product_id` AND cisi.stock_id = 1)', array('is_in_stock'))->joinLeft(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('magento_sku' => 'sku')); // --------------------------------------- $listingOtherCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingOtherCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr(0), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 'second_table.title', 'listing_title' => new Zend_Db_Expr('NULL'), 'store_id' => new Zend_Db_Expr(0), 'account_id' => 'main_table.account_id', 'marketplace_id' => 'main_table.marketplace_id', 'listing_product_id' => new Zend_Db_Expr('NULL'), 'product_id' => 'main_table.product_id', 'listing_id' => new Zend_Db_Expr('NULL'), 'status' => 'main_table.status', 'template_new_product_id' => new Zend_Db_Expr('NULL'), 'general_id' => 'second_table.general_id', 'online_sku' => 'second_table.sku', 'online_qty' => 'second_table.online_qty', 'online_price' => 'second_table.online_price')); // --------------------------------------- // --------------------------------------- $selects = array($listingProductCollection->getSelect(), $listingOtherCollection->getSelect()); $unionSelect = Mage::getResourceModel('core/config')->getReadConnection()->select(); $unionSelect->union($selects); $resultCollection = new Varien_Data_Collection_Db(Mage::getResourceModel('core/config')->getReadConnection()); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('is_m2epro_listing', 'magento_sku', 'is_in_stock', 'product_name', 'listing_title', 'store_id', 'account_id', 'marketplace_id', 'listing_product_id', 'product_id', 'listing_id', 'status', 'template_new_product_id', 'general_id', 'online_sku', 'online_qty', 'online_price')); // Set collection to grid $this->setCollection($resultCollection); return parent::_prepareCollection(); }
protected function _prepareCollection() { /** @var $connRead Varien_Db_Adapter_Pdo_Mysql */ $connRead = Mage::getSingleton('core/resource')->getConnection('core_read'); // Prepare selling format collection // --------------------------------------- $collectionSellingFormat = Mage::getModel('M2ePro/Template_SellingFormat')->getCollection(); $collectionSellingFormat->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSellingFormat->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_SELLING_FORMAT . '\' as `type`'), 'create_date', 'update_date')); $collectionSellingFormat->getSelect()->where('component_mode = (?)', $this->nick); // --------------------------------------- // Prepare synchronization collection // --------------------------------------- $collectionSynchronization = Mage::getModel('M2ePro/Template_Synchronization')->getCollection(); $collectionSynchronization->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSynchronization->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_SYNCHRONIZATION . '\' as `type`'), 'create_date', 'update_date')); $collectionSynchronization->getSelect()->where('component_mode = (?)', $this->nick); // --------------------------------------- // Prepare union select // --------------------------------------- $unionSelect = $connRead->select(); $unionSelect->union(array($collectionSellingFormat->getSelect(), $collectionSynchronization->getSelect())); // --------------------------------------- // Prepare result collection // --------------------------------------- $resultCollection = new Varien_Data_Collection_Db($connRead); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('template_id', 'title', 'type', 'create_date', 'update_date')); // --------------------------------------- // echo $resultCollection->getSelectSql(true); exit; $this->setCollection($resultCollection); return parent::_prepareCollection(); }
/** * Adding item data using second query because: * * - Join causes use of temporary table == slow * - Wrapping the main query as a subquery is too complex * - We want to show all order items for orders that matched filter by sku/name * * @return $this */ protected function _prepareCollection() { parent::_prepareCollection(); if (Mage::getStoreConfig(self::XML_PATH_RENDER_COLUMN) && !$this->_isExport) { $orderIds = array(); $orderCollection = $this->getCollection(); /** @var $orderCollection Mage_Sales_Model_Mysql4_Order_Grid_Collection */ foreach ($orderCollection as $order) { $orderIds[] = $order->getEntityId(); } $conn = Mage::getSingleton('core/resource')->getConnection('read'); /* @var $conn Zend_Db_Adapter_Pdo_Abstract */ // Increase max length of group concat fields for long product names $conn->exec('SET SESSION group_concat_max_len = 4096;'); $itemsCollection = new Varien_Data_Collection_Db($conn); $itemsCollection->getSelect()->from(array('soi' => $orderCollection->getTable('sales/order_item')), array('order_id', 'skus' => new Zend_Db_Expr('group_concat(`soi`.sku SEPARATOR " ^ ")'), 'qtys' => new Zend_Db_Expr('group_concat(`soi`.qty_ordered SEPARATOR " ^ ")'), 'names' => new Zend_Db_Expr('group_concat(`soi`.name SEPARATOR " ^ ")')))->where('order_id IN (?)', $orderIds)->group('order_id'); foreach ($itemsCollection as $object) { $order = $orderCollection->getItemById($object->getOrderId()); $order->setSkus($object->getSkus()); $order->setQtys($object->getQtys()); $order->setNames($object->getNames()); } } Mage::app()->dispatchEvent('cm_orderproducts_sales_order_grid_prepareCollection', ['block' => $this]); return $this; }
private function addMotorsSpecificsAttributeToSelect(Varien_Data_Collection_Db $collection) { if (!$this->isMotorsSpecificsAttributeAvailable()) { return; } $attribute = $this->getMotorsSpecificsAttribute(); $attributeId = (int) $attribute->getAttributeId(); $collection->getSelect()->joinLeft(array('cpet' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_text')), '(`cpet`.`entity_id` = `main_table`.product_id AND `cpet`.`attribute_id` = ' . $attributeId . ')', array('motors_specifics_attribute_value' => 'value')); }
/** * Test that after cloning collection $this->_select in initial and cloned collections * do not reference the same object * * @covers Varien_Data_Collection_Db::__clone */ public function testClone() { $adapter = $this->_getAdapterMock('Zend_Db_Adapter_Pdo_Mysql', null, null); $this->_collection->setConnection($adapter); $this->assertInstanceOf('Zend_Db_Select', $this->_collection->getSelect()); $clonedCollection = clone $this->_collection; $this->assertInstanceOf('Zend_Db_Select', $clonedCollection->getSelect()); $this->assertNotSame($clonedCollection->getSelect(), $this->_collection->getSelect(), 'Collection was cloned but $this->_select in both initial and cloned collections reference the same object'); }
/** * Test that after cloning collection $this->_select in initial and cloned collections * do not reference the same object * * @covers Varien_Data_Collection_Db::__clone */ public function testClone() { $adapter = $this->getMockForAbstractClass('Zend_Db_Adapter_Abstract', array(), '', false); $this->_collection->setConnection($adapter); $this->assertInstanceOf('Zend_Db_Select', $this->_collection->getSelect()); $clonedCollection = clone $this->_collection; $this->assertInstanceOf('Zend_Db_Select', $clonedCollection->getSelect()); $this->assertNotSame($clonedCollection->getSelect(), $this->_collection->getSelect(), 'Collection was cloned but $this->_select in both initial and cloned collections reference the same object'); }
/** * Add events log to a collection * The collection id field is used without corellation, so it must be unique. * DESC ordering by event will be added to the collection * * @param Varien_Data_Collection_Db $collection * @param int $eventTypeId * @param int $eventSubjectId * @param int $subtype * @param array $skipIds */ public function applyLogToCollection(Varien_Data_Collection_Db $collection, $eventTypeId, $eventSubjectId, $subtype, $skipIds = array()) { $idFieldName = $collection->getResource()->getIdFieldName(); $derivedSelect = $this->getReadConnection()->select()->from($this->getTable('reports/event'), array('event_id' => new Zend_Db_Expr('MAX(event_id)'), 'object_id'))->where('event_type_id=?', (int) $eventTypeId)->where('subject_id=?', (int) $eventSubjectId)->where('subtype=?', (int) $subtype)->where('store_id IN(?)', $this->getCurrentStoreIds())->group('object_id'); if ($skipIds) { if (!is_array($skipIds)) { $skipIds = array((int) $skipIds); } $derivedSelect->where('object_id NOT IN(?)', $skipIds); } $collection->getSelect()->joinInner(array('evt' => new Zend_Db_Expr("({$derivedSelect})")), "`{$idFieldName}`=evt.object_id", array())->order('evt.event_id DESC'); }
protected function _prepareCollection() { /** @var $connRead Varien_Db_Adapter_Pdo_Mysql */ $connRead = Mage::getSingleton('core/resource')->getConnection('core_read'); // Prepare selling format collection // --------------------------------------- $collectionSellingFormat = Mage::getModel('M2ePro/Template_SellingFormat')->getCollection(); $collectionSellingFormat->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSellingFormat->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_SELLING_FORMAT . '\' as `type`'), new Zend_Db_Expr('\'0\' as `marketplace_id`'), 'create_date', 'update_date', new Zend_Db_Expr('NULL as `category_path`'), new Zend_Db_Expr('NULL as `browsenode_id`'), new Zend_Db_Expr('NULL as `is_new_asin_accepted`'))); $collectionSellingFormat->getSelect()->where('component_mode = (?)', $this->nick); // --------------------------------------- // Prepare synchronization collection // --------------------------------------- $collectionSynchronization = Mage::getModel('M2ePro/Template_Synchronization')->getCollection(); $collectionSynchronization->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSynchronization->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_SYNCHRONIZATION . '\' as `type`'), new Zend_Db_Expr('\'0\' as `marketplace_id`'), 'create_date', 'update_date', new Zend_Db_Expr('NULL as `category_path`'), new Zend_Db_Expr('NULL as `browsenode_id`'), new Zend_Db_Expr('NULL as `is_new_asin_accepted`'))); $collectionSynchronization->getSelect()->where('component_mode = (?)', $this->nick); // --------------------------------------- // Prepare shipping override collection // --------------------------------------- $collectionShippingOverride = Mage::getModel('M2ePro/Amazon_Template_ShippingOverride')->getCollection(); $collectionShippingOverride->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionShippingOverride->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_SHIPPING_OVERRIDE . '\' as `type`'), 'marketplace_id', 'create_date', 'update_date', new Zend_Db_Expr('NULL as `category_path`'), new Zend_Db_Expr('NULL as `browsenode_id`'), new Zend_Db_Expr('NULL as `is_new_asin_accepted`'))); // --------------------------------------- // Prepare shipping override collection // --------------------------------------- $collectionDescription = Mage::helper('M2ePro/Component_Amazon')->getCollection('Template_Description'); $collectionDescription->getSelect()->join(array('mm' => Mage::getModel('M2ePro/Marketplace')->getResource()->getMainTable()), 'mm.id=second_table.marketplace_id', array()); $collectionDescription->addFieldToFilter('mm.status', Ess_M2ePro_Model_Marketplace::STATUS_ENABLE); $collectionDescription->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionDescription->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'' . self::TEMPLATE_DESCRIPTION . '\' as `type`'), 'second_table.marketplace_id', 'create_date', 'update_date', 'second_table.category_path', 'second_table.browsenode_id', 'second_table.is_new_asin_accepted')); // --------------------------------------- // Prepare union select // --------------------------------------- $unionSelect = $connRead->select(); $unionSelect->union(array($collectionSellingFormat->getSelect(), $collectionSynchronization->getSelect(), $collectionDescription->getSelect(), $collectionShippingOverride->getSelect())); // --------------------------------------- // Prepare result collection // --------------------------------------- $resultCollection = new Varien_Data_Collection_Db($connRead); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('template_id', 'title', 'type', 'marketplace_id', 'create_date', 'update_date', 'category_path', 'browsenode_id', 'is_new_asin_accepted')); // --------------------------------------- // echo $resultCollection->getSelectSql(true); exit; $this->setCollection($resultCollection); return parent::_prepareCollection(); }
protected function _prepareCollection() { // Get collection products in listing //-------------------------------- $nameAttribute = Mage::getResourceModel('catalog/product')->getAttribute('name'); $nameAttributeId = $nameAttribute ? (int) $nameAttribute->getId() : 0; $listingProductCollection = Mage::helper('M2ePro/Component_Ebay')->getCollection('Listing_Product'); $listingProductCollection->getSelect()->distinct(); $listingProductCollection->getSelect()->join(array('l' => Mage::getResourceModel('M2ePro/Listing')->getMainTable()), '`l`.`id` = `main_table`.`listing_id`'); $listingProductCollection->getSelect()->join(array('em' => Mage::getResourceModel('M2ePro/Ebay_Marketplace')->getMainTable()), '`em`.`marketplace_id` = `l`.`marketplace_id`'); $listingProductCollection->getSelect()->join(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), 'cpe.entity_id = `main_table`.product_id'); $listingProductCollection->getSelect()->joinLeft(array('cpev' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar')), '`cpev`.`entity_id` = `main_table`.`product_id`' . ' AND `cpev`.`attribute_id` = ' . $nameAttributeId . ' AND `cpev`.`store_id` = 0'); $listingProductCollection->getSelect()->joinLeft(array('ebit' => Mage::getResourceModel('M2ePro/Ebay_Item')->getMainTable()), '(`ebit`.`id` = `second_table`.`ebay_item_id`)', array('item_id')); //------------------------------ // add stock availability, status & visibility to select //------------------------------ $listingProductCollection->getSelect()->joinLeft(array('cisi' => Mage::getResourceModel('cataloginventory/stock_item')->getMainTable()), '(`cisi`.`product_id` = `main_table`.`product_id` AND `cisi`.`stock_id` = 1)', array('is_in_stock')); //------------------------------ $listingProductCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingProductCollection->getSelect()->columns(array('account_id' => 'l.account_id', 'marketplace_id' => 'l.marketplace_id', 'product_id' => 'main_table.product_id', 'product_name' => 'cpev.value', 'product_sku' => 'cpe.sku', 'currency' => 'em.currency', 'ebay_item_id' => 'ebit.item_id', 'status' => 'main_table.status', 'online_sku' => 'second_table.online_sku', 'online_title' => 'second_table.online_title', 'online_qty' => new Zend_Db_Expr('(second_table.online_qty - second_table.online_qty_sold)'), 'online_qty_sold' => 'second_table.online_qty_sold', 'online_buyitnow_price' => 'second_table.online_buyitnow_price', 'listing_id' => 'l.id', 'listing_title' => 'l.title', 'is_m2epro_listing' => new Zend_Db_Expr(1), 'is_in_stock' => 'cisi.is_in_stock')); //------------------------------ //------------------------------ $listingOtherCollection = Mage::helper('M2ePro/Component_Ebay')->getCollection('Listing_Other'); $listingOtherCollection->getSelect()->distinct(); // add stock availability, type id, status & visibility to select //------------------------------ $listingOtherCollection->getSelect()->joinLeft(array('cisi' => Mage::getResourceModel('cataloginventory/stock_item')->getMainTable()), '(`cisi`.`product_id` = `main_table`.`product_id` AND cisi.stock_id = 1)', array('is_in_stock')); //------------------------------ $listingOtherCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingOtherCollection->getSelect()->columns(array('account_id' => 'main_table.account_id', 'marketplace_id' => 'main_table.marketplace_id', 'product_id' => 'main_table.product_id', 'product_name' => 'second_table.title', 'product_sku' => 'second_table.sku', 'currency' => 'second_table.currency', 'ebay_item_id' => 'second_table.item_id', 'status' => 'main_table.status', 'online_sku' => new Zend_Db_Expr('NULL'), 'online_title' => new Zend_Db_Expr('NULL'), 'online_qty' => new Zend_Db_Expr('(second_table.online_qty - second_table.online_qty_sold)'), 'online_qty_sold' => 'second_table.online_qty_sold', 'online_buyitnow_price' => 'second_table.online_price', 'listing_id' => new Zend_Db_Expr('NULL'), 'listing_title' => new Zend_Db_Expr('NULL'), 'is_m2epro_listing' => new Zend_Db_Expr(0), 'is_in_stock' => 'cisi.is_in_stock')); //------------------------------ //------------------------------ $selects = array($listingProductCollection->getSelect()); if (Mage::helper('M2ePro/View_Ebay')->isAdvancedMode()) { $selects[] = $listingOtherCollection->getSelect(); } $unionSelect = Mage::getResourceModel('core/config')->getReadConnection()->select(); $unionSelect->union($selects); $resultCollection = new Varien_Data_Collection_Db(Mage::getResourceModel('core/config')->getReadConnection()); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('account_id', 'marketplace_id', 'product_id', 'product_name', 'product_sku', 'currency', 'ebay_item_id', 'status', 'online_sku', 'online_title', 'online_qty', 'online_qty_sold', 'online_buyitnow_price', 'listing_id', 'listing_title', 'is_m2epro_listing', 'is_in_stock')); //------------------------------ $this->setCollection($resultCollection); // exit($resultCollection->getSelect().''); return parent::_prepareCollection(); }
protected function _prepareCollection() { // Get collection // --------------------------------------- /** @var Ess_M2ePro_Model_Mysql4_Ebay_Listing_Product_Collection $collection */ $collection = Mage::helper('M2ePro/Component_Ebay')->getCollection('Listing_Product_Variation'); $collection->getSelect()->where('main_table.listing_product_id = ?', (int) $this->getListingProductId()); $collection->getSelect()->group('main_table.id'); // --------------------------------------- // Join variation option tables // --------------------------------------- $collection->getSelect()->join(array('mlpvo' => Mage::getResourceModel('M2ePro/Listing_Product_Variation_Option')->getMainTable()), '`mlpvo`.`listing_product_variation_id`=`main_table`.`id`', array()); $collection->getSelect()->reset(Zend_Db_Select::COLUMNS); $collection->getSelect()->columns(array('id' => 'main_table.id', 'listing_product_id' => 'main_table.listing_product_id', 'additional_data' => 'main_table.additional_data', 'add' => 'second_table.add', 'delete' => 'second_table.delete', 'online_price' => 'second_table.online_price', 'online_sku' => 'second_table.online_sku', 'available_qty' => new Zend_Db_Expr('(second_table.online_qty - second_table.online_qty_sold)'), 'online_qty_sold' => 'second_table.online_qty_sold', 'status' => 'second_table.status', 'attributes' => 'GROUP_CONCAT(`mlpvo`.`attribute`, \'==\', `mlpvo`.`option` SEPARATOR \'||\')', 'products_ids' => 'GROUP_CONCAT(`mlpvo`.`attribute`, \'==\', `mlpvo`.`product_id` SEPARATOR \'||\')')); $resultCollection = new Varien_Data_Collection_Db(Mage::getResourceModel('core/config')->getReadConnection()); $resultCollection->getSelect()->reset()->from(array('main_table' => $collection->getSelect()), array('id', 'listing_product_id', 'additional_data', 'add', 'delete', 'online_price', 'available_qty', 'online_sku', 'online_qty_sold', 'status', 'attributes', 'products_ids')); // Set collection to grid $this->setCollection($resultCollection); return parent::_prepareCollection(); }
protected function callbackFilterBuyer(Varien_Data_Collection_Db $collection, $column) { $value = $column->getFilter()->getValue(); if ($value == null) { return; } $collection->getSelect()->where('buyer_email LIKE ? OR buyer_name LIKE ?', '%' . $value . '%'); }
/** * Get Zend_Db_Select instance and applies fields to select if needed * * @return Varien_Db_Select */ public function getSelect() { if ($this->_select && $this->_fieldsToSelectChanged) { $this->_fieldsToSelectChanged = false; $this->_initSelectFields(); } return parent::getSelect(); }
/** * Constructs the query returning purchasable products * * @param Varien_Db_Adapter_Interface $dbRead Read connection * @param Varien_Data_Collection_Db $productCollection Products * @param Varien_Db_Select $productToParentQuery Parent resolution * @param Varien_Data_Collection_Db $parentCollection Parents * @param array $purchasableFilter Visibility filter * @param bool $resetColumns Flat product fix * * @return Varien_Db_Select */ private function _purchasableQueryHelper($dbRead, $productCollection, $productToParentQuery, $parentCollection, $purchasableFilter, $resetColumns) { $productQuery = $productCollection->getSelect(); if ($resetColumns) { $productQuery->reset(Zend_Db_Select::COLUMNS)->columns(array('e.entity_id', 'e.sku', 'e.attribute_set_id', 'e.type_id')); } $query = $dbRead->select(); $query->from(array('product' => new Zend_Db_Expr("({$productQuery})")), array('entity_id', 'sku'))->joinLeft(array('product_to_parent' => new Zend_Db_Expr("({$productToParentQuery})")), 'product_to_parent.product_id = product.entity_id', array())->joinLeft(array('parent' => new Zend_Db_Expr("({$parentCollection->getSelect()})")), $dbRead->quoteInto('parent.entity_id = product_to_parent.parent_id ' . 'AND parent.type_id = ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE), array())->where('parent.status IS NULL OR parent.status = ?', Mage_Catalog_Model_Product_Status::STATUS_ENABLED); return $query; }
protected function _prepareCollection() { // Get collection products in listing // --------------------------------------- $listingProductCollection = Mage::helper('M2ePro/Component_Amazon')->getCollection('Listing_Product'); $listingProductCollection->getSelect()->distinct(); $listingProductCollection->getSelect()->join(array('l' => Mage::getResourceModel('M2ePro/Listing')->getMainTable()), '(`l`.`id` = `main_table`.`listing_id`)', array('listing_title' => 'title', 'store_id', 'marketplace_id'))->join(array('al' => Mage::getResourceModel('M2ePro/Amazon_Listing')->getMainTable()), '(`al`.`listing_id` = `l`.`id`)', array('template_selling_format_id')); // --------------------------------------- // only parents and individuals $listingProductCollection->getSelect()->where('second_table.variation_parent_id IS NULL'); // Communicate with magento product table // --------------------------------------- $dbSelect = Mage::getResourceModel('core/config')->getReadConnection()->select()->from(Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar'), new Zend_Db_Expr('MAX(`store_id`)'))->where("`entity_id` = `main_table`.`product_id`")->where("`attribute_id` = `ea`.`attribute_id`")->where("`store_id` = 0 OR `store_id` = `l`.`store_id`"); $listingProductCollection->getSelect()->join(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('magento_sku' => 'sku'))->join(array('cisi' => Mage::getSingleton('core/resource')->getTableName('cataloginventory_stock_item')), '(cisi.product_id = `main_table`.product_id AND cisi.stock_id = 1)', array('is_in_stock'))->join(array('cpev' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar')), "(`cpev`.`entity_id` = `main_table`.product_id)", array('value'))->join(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), '(`cpev`.`attribute_id` = `ea`.`attribute_id` AND `ea`.`attribute_code` = \'name\')', array())->where('`cpev`.`store_id` = (' . $dbSelect->__toString() . ')'); // --------------------------------------- $listingProductCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingProductCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr('1'), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 'cpev.value', 'listing_title' => 'l.title', 'store_id' => 'l.store_id', 'account_id' => 'l.account_id', 'marketplace_id' => 'l.marketplace_id', 'template_selling_format_id' => 'al.template_selling_format_id', 'listing_product_id' => 'main_table.id', 'product_id' => 'main_table.product_id', 'listing_id' => 'main_table.listing_id', 'status' => 'main_table.status', 'is_general_id_owner' => 'second_table.is_general_id_owner', 'general_id' => 'second_table.general_id', 'is_afn_channel' => 'second_table.is_afn_channel', 'is_variation_parent' => 'second_table.is_variation_parent', 'is_repricing' => 'second_table.is_repricing', 'variation_child_statuses' => 'second_table.variation_child_statuses', 'online_sku' => 'second_table.sku', 'online_qty' => 'second_table.online_qty', 'online_price' => 'second_table.online_price', 'online_sale_price' => 'second_table.online_sale_price', 'online_sale_price_start_date' => 'second_table.online_sale_price_start_date', 'online_sale_price_end_date' => 'second_table.online_sale_price_end_date', 'min_online_price' => 'IF( (`t`.`variation_min_price` IS NULL), IF( `second_table`.`online_sale_price_start_date` IS NOT NULL AND `second_table`.`online_sale_price_end_date` IS NOT NULL AND `second_table`.`online_sale_price_start_date` <= CURRENT_DATE() AND `second_table`.`online_sale_price_end_date` >= CURRENT_DATE(), `second_table`.`online_sale_price`, `second_table`.`online_price` ), `t`.`variation_min_price` )', 'max_online_price' => 'IF( (`t`.`variation_max_price` IS NULL), IF( `second_table`.`online_sale_price_start_date` IS NOT NULL AND `second_table`.`online_sale_price_end_date` IS NOT NULL AND `second_table`.`online_sale_price_start_date` <= CURRENT_DATE() AND `second_table`.`online_sale_price_end_date` >= CURRENT_DATE(), `second_table`.`online_sale_price`, `second_table`.`online_price` ), `t`.`variation_max_price` )')); $listingProductCollection->getSelect()->joinLeft(new Zend_Db_Expr('( SELECT `malp`.`variation_parent_id`, MIN( IF( `malp`.`online_sale_price_start_date` IS NOT NULL AND `malp`.`online_sale_price_end_date` IS NOT NULL AND `malp`.`online_sale_price_start_date` <= CURRENT_DATE() AND `malp`.`online_sale_price_end_date` >= CURRENT_DATE(), `malp`.`online_sale_price`, `malp`.`online_price` ) ) as variation_min_price, MAX( IF( `malp`.`online_sale_price_start_date` IS NOT NULL AND `malp`.`online_sale_price_end_date` IS NOT NULL AND `malp`.`online_sale_price_start_date` <= CURRENT_DATE() AND `malp`.`online_sale_price_end_date` >= CURRENT_DATE(), `malp`.`online_sale_price`, `malp`.`online_price` ) ) as variation_max_price FROM `' . Mage::getResourceModel('M2ePro/Amazon_Listing_Product')->getMainTable() . '` as malp INNER JOIN `' . Mage::getResourceModel('M2ePro/Listing_Product')->getMainTable() . '` AS `mlp` ON (`malp`.`listing_product_id` = `mlp`.`id`) WHERE `mlp`.`status` IN ( ' . Ess_M2ePro_Model_Listing_Product::STATUS_LISTED . ', ' . Ess_M2ePro_Model_Listing_Product::STATUS_STOPPED . ' ) AND `malp`.`variation_parent_id` IS NOT NULL GROUP BY `malp`.`variation_parent_id` )'), 'second_table.listing_product_id=t.variation_parent_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price')); // --------------------------------------- $listingOtherCollection = Mage::helper('M2ePro/Component_Amazon')->getCollection('Listing_Other'); $listingOtherCollection->getSelect()->distinct(); // add stock availability, type id, status & visibility to select // --------------------------------------- $listingOtherCollection->getSelect()->joinLeft(array('cisi' => Mage::getResourceModel('cataloginventory/stock_item')->getMainTable()), '(`cisi`.`product_id` = `main_table`.`product_id` AND cisi.stock_id = 1)', array('is_in_stock'))->joinLeft(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('magento_sku' => 'sku')); // --------------------------------------- $listingOtherCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingOtherCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr(0), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 'second_table.title', 'listing_title' => new Zend_Db_Expr('NULL'), 'store_id' => new Zend_Db_Expr(0), 'account_id' => 'main_table.account_id', 'marketplace_id' => 'main_table.marketplace_id', 'template_selling_format_id' => new Zend_Db_Expr('NULL'), 'listing_product_id' => new Zend_Db_Expr('NULL'), 'product_id' => 'main_table.product_id', 'listing_id' => new Zend_Db_Expr('NULL'), 'status' => 'main_table.status', 'is_general_id_owner' => new Zend_Db_Expr('NULL'), 'general_id' => 'second_table.general_id', 'is_afn_channel' => 'second_table.is_afn_channel', 'is_variation_parent' => new Zend_Db_Expr('NULL'), 'is_repricing' => 'second_table.is_repricing', 'variation_child_statuses' => new Zend_Db_Expr('NULL'), 'online_sku' => 'second_table.sku', 'online_qty' => 'second_table.online_qty', 'online_price' => 'second_table.online_price', 'online_sale_price' => new Zend_Db_Expr('NULL'), 'online_sale_price_start_date' => new Zend_Db_Expr('NULL'), 'online_sale_price_end_date' => new Zend_Db_Expr('NULL'), 'min_online_price' => 'second_table.online_price', 'max_online_price' => 'second_table.online_price', 'variation_min_price' => new Zend_Db_Expr('NULL'), 'variation_max_price' => new Zend_Db_Expr('NULL'))); // --------------------------------------- // --------------------------------------- $selects = array($listingProductCollection->getSelect(), $listingOtherCollection->getSelect()); $unionSelect = Mage::getResourceModel('core/config')->getReadConnection()->select(); $unionSelect->union($selects); $resultCollection = new Varien_Data_Collection_Db(Mage::getResourceModel('core/config')->getReadConnection()); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('is_m2epro_listing', 'magento_sku', 'is_in_stock', 'product_name', 'listing_title', 'store_id', 'account_id', 'marketplace_id', 'template_selling_format_id', 'listing_product_id', 'product_id', 'listing_id', 'status', 'is_general_id_owner', 'general_id', 'is_afn_channel', 'is_variation_parent', 'is_repricing', 'variation_child_statuses', 'online_sku', 'online_qty', 'online_price', 'online_sale_price', 'online_sale_price_start_date', 'online_sale_price_end_date', 'min_online_price', 'max_online_price', 'variation_min_price', 'variation_max_price')); // Set collection to grid $this->setCollection($resultCollection); return parent::_prepareCollection(); }
protected function _prepareCollection() { /** @var $connRead Varien_Db_Adapter_Pdo_Mysql */ $connRead = Mage::getSingleton('core/resource')->getConnection('core_read'); // Prepare selling format collection // ---------------------------------- $collectionSellingFormat = Mage::getModel('M2ePro/Template_SellingFormat')->getCollection(); $collectionSellingFormat->getSelect()->join(array('etsf' => Mage::getModel('M2ePro/Ebay_Template_SellingFormat')->getResource()->getMainTable()), 'main_table.id=etsf.template_selling_format_id', array('is_custom_template')); $collectionSellingFormat->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSellingFormat->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'0\' as `marketplace`'), new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_SELLING_FORMAT . '\' as `nick`'), 'create_date', 'update_date')); $collectionSellingFormat->addFieldToFilter('component_mode', Ess_M2ePro_Helper_Component_Ebay::NICK); $collectionSellingFormat->addFieldToFilter('is_custom_template', 0); // ---------------------------------- // Prepare synchronization collection // ---------------------------------- $collectionSynchronization = Mage::getModel('M2ePro/Template_Synchronization')->getCollection(); $collectionSynchronization->getSelect()->join(array('ets' => Mage::getModel('M2ePro/Ebay_Template_Synchronization')->getResource()->getMainTable()), 'main_table.id=ets.template_synchronization_id', array('is_custom_template')); $collectionSynchronization->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionSynchronization->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'0\' as `marketplace`'), new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_SYNCHRONIZATION . '\' as `nick`'), 'create_date', 'update_date')); $collectionSynchronization->addFieldToFilter('component_mode', Ess_M2ePro_Helper_Component_Ebay::NICK); $collectionSynchronization->addFieldToFilter('is_custom_template', 0); // ---------------------------------- // Prepare description collection // ---------------------------------- $collectionDescription = Mage::getModel('M2ePro/Ebay_Template_Description')->getCollection(); $collectionDescription->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionDescription->getSelect()->columns(array('id as template_id', 'title', new Zend_Db_Expr('\'0\' as `marketplace`'), new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_DESCRIPTION . '\' as `nick`'), 'create_date', 'update_date')); $collectionDescription->addFieldToFilter('is_custom_template', 0); // ---------------------------------- // Prepare payment collection // ---------------------------------- $collectionPayment = Mage::getModel('M2ePro/Ebay_Template_Payment')->getCollection(); $collectionPayment->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionPayment->getSelect()->columns(array('id as template_id', 'title', 'marketplace_id as marketplace', new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_PAYMENT . '\' as `nick`'), 'create_date', 'update_date')); $collectionPayment->addFieldToFilter('is_custom_template', 0); $collectionPayment->addFieldToFilter('marketplace_id', array('in' => $this->getEnabledMarketplacesIds())); // ---------------------------------- // Prepare shipping collection // ---------------------------------- $collectionShipping = Mage::getModel('M2ePro/Ebay_Template_Shipping')->getCollection(); $collectionShipping->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionShipping->getSelect()->columns(array('id as template_id', 'title', 'marketplace_id as marketplace', new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_SHIPPING . '\' as `nick`'), 'create_date', 'update_date')); $collectionShipping->addFieldToFilter('is_custom_template', 0); $collectionShipping->addFieldToFilter('marketplace_id', array('in' => $this->getEnabledMarketplacesIds())); // ---------------------------------- // Prepare return collection // ---------------------------------- $collectionReturn = Mage::getModel('M2ePro/Ebay_Template_Return')->getCollection(); $collectionReturn->getSelect()->reset(Varien_Db_Select::COLUMNS); $collectionReturn->getSelect()->columns(array('id as template_id', 'title', 'marketplace_id as marketplace', new Zend_Db_Expr('\'' . Ess_M2ePro_Model_Ebay_Template_Manager::TEMPLATE_RETURN . '\' as `nick`'), 'create_date', 'update_date')); $collectionReturn->addFieldToFilter('is_custom_template', 0); $collectionReturn->addFieldToFilter('marketplace_id', array('in' => $this->getEnabledMarketplacesIds())); // ---------------------------------- // Prepare union select // ---------------------------------- $unionSelect = $connRead->select(); $unionSelect->union(array($collectionSellingFormat->getSelect(), $collectionSynchronization->getSelect(), $collectionDescription->getSelect(), $collectionPayment->getSelect(), $collectionShipping->getSelect(), $collectionReturn->getSelect())); // ---------------------------------- // Prepare result collection // ---------------------------------- $resultCollection = new Varien_Data_Collection_Db($connRead); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('template_id', 'title', 'nick', 'marketplace', 'create_date', 'update_date')); // ---------------------------------- // var_dump($resultCollection->getSelectSql(true)); exit; $this->setCollection($resultCollection); return parent::_prepareCollection(); }
public function callbackFilterMotorsAttribute(Varien_Data_Collection_Db $collection, $column) { $value = $column->getFilter()->getValue(); if (is_null($value)) { return; } if (!$this->motorsAttribute) { return; } if ($value == 1) { $attributeCode = $this->motorsAttribute->getAttributeCode(); $collection->addFieldToFilter($attributeCode, array('notnull' => true)); $collection->addFieldToFilter($attributeCode, array('neq' => '')); $collection->addFieldToFilter('is_motors_attribute_in_product_attribute_set', array('notnull' => true)); } else { $attributeId = $this->motorsAttribute->getId(); $storeId = $this->getListing()->getStoreId(); $joinCondition = 'eaa.entity_id = e.entity_id and eaa.attribute_id = ' . $attributeId; if (!$this->motorsAttribute->isScopeGlobal()) { $joinCondition .= ' and eaa.store_id = ' . $storeId; } $collection->getSelect()->joinLeft(array('eaa' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_text')), $joinCondition, array('value')); $collection->getSelect()->orWhere('eaa.value IS NULL'); $collection->getSelect()->orWhere('eaa.value = \'\''); $collection->getSelect()->orWhere('eea.entity_attribute_id IS NULL'); } }
protected function _prepareCollection() { // Get collection products in listing // --------------------------------------- $activeComponents = Mage::helper('M2ePro/View_Common_Component')->getActiveComponents(); /** @var Ess_M2ePro_Model_Mysql4_Listing_Product_Collection $listingProductCollection */ $listingProductCollection = Mage::getModel('M2ePro/Listing_Product')->getCollection(); $listingProductCollection->addFieldToFilter('main_table.component_mode', array('in' => $activeComponents)); $listingProductCollection->getSelect()->distinct(); $listingProductCollection->getSelect()->join(array('l' => Mage::getResourceModel('M2ePro/Listing')->getMainTable()), '(`l`.`id` = `main_table`.`listing_id`)', array('listing_title' => 'title', 'store_id')); // --------------------------------------- $listingProductCollection->getSelect()->joinLeft(array('alp' => Mage::getResourceModel('M2ePro/Amazon_Listing_Product')->getMainTable()), 'main_table.id=alp.listing_product_id', array()); $listingProductCollection->getSelect()->where('( (`main_table`.`component_mode` = "' . Ess_M2ePro_Helper_Component_Amazon::NICK . '" AND `alp`.variation_parent_id IS NULL) OR `main_table`.`component_mode` IN ("' . Ess_M2ePro_Helper_Component_Buy::NICK . '") )'); // Communicate with magento product table // --------------------------------------- $table = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar'); $dbSelect = Mage::getResourceModel('core/config')->getReadConnection()->select()->from($table, new Zend_Db_Expr('MAX(`store_id`)'))->where("`entity_id` = `main_table`.`product_id`")->where("`attribute_id` = `ea`.`attribute_id`")->where("`store_id` = 0 OR `store_id` = `l`.`store_id`"); $listingProductCollection->getSelect()->join(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('sku')); $listingProductCollection->getSelect()->join(array('cisi' => Mage::getSingleton('core/resource')->getTableName('cataloginventory_stock_item')), '(cisi.product_id = `main_table`.product_id AND cisi.stock_id = 1)', array('is_in_stock')); $listingProductCollection->getSelect()->join(array('cpev' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar')), "(`cpev`.`entity_id` = `main_table`.product_id)", array('value')); $listingProductCollection->getSelect()->join(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), '(`cpev`.`attribute_id` = `ea`.`attribute_id` AND `ea`.`attribute_code` = \'name\')', array()); $listingProductCollection->getSelect()->where('`cpev`.`store_id` = (' . $dbSelect->__toString() . ')'); // --------------------------------------- $listingProductCollection->getSelect()->joinLeft(new Zend_Db_Expr('( SELECT lp.listing_product_id, lp.general_id_owner, lp.general_id, lp.sku FROM ( SELECT listing_product_id, is_general_id_owner as general_id_owner, general_id, sku FROM ' . Mage::getResourceModel('M2ePro/Amazon_Listing_Product')->getMainTable() . ' WHERE variation_parent_id IS NULL UNION SELECT listing_product_id, template_new_product_id as general_id_owner, general_id, sku FROM ' . Mage::getResourceModel('M2ePro/Buy_Listing_Product')->getMainTable() . ' ) as lp )'), 'main_table.id=t.listing_product_id', array('general_id' => 'general_id', 'sku' => 'sku')); $listingProductCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingProductCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr('1'), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 'cpev.value', 'listing_title' => 'l.title', 'store_id' => 'l.store_id', 'account_id' => 'l.account_id', 'marketplace_id' => 'l.marketplace_id', 'listing_product_id' => 'main_table.id', 'component_mode' => 'main_table.component_mode', 'product_id' => 'main_table.product_id', 'listing_id' => 'main_table.listing_id', 'status' => 'main_table.status', 'general_id_owner' => 't.general_id_owner', 'general_id' => 't.general_id', 'online_sku' => 't.sku')); // --------------------------------------- $listingOtherCollection = Mage::getModel('M2ePro/Listing_Other')->getCollection(); $listingOtherCollection->addFieldToFilter('main_table.component_mode', array('in' => $activeComponents)); $listingOtherCollection->getSelect()->distinct(); // add stock availability, type id, status & visibility to select // --------------------------------------- $listingOtherCollection->getSelect()->joinLeft(array('cisi' => Mage::getResourceModel('cataloginventory/stock_item')->getMainTable()), '(`cisi`.`product_id` = `main_table`.`product_id` AND cisi.stock_id = 1)', array('is_in_stock'))->joinLeft(array('cpe' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity')), '(cpe.entity_id = `main_table`.product_id)', array('magento_sku' => 'sku')); // --------------------------------------- $listingOtherCollection->getSelect()->joinLeft(new Zend_Db_Expr('( SELECT lo.listing_other_id, lo.title, lo.general_id, lo.sku FROM ( SELECT listing_other_id, title, general_id, sku FROM ' . Mage::getResourceModel('M2ePro/Amazon_Listing_Other')->getMainTable() . ' UNION SELECT listing_other_id, title, general_id, sku FROM ' . Mage::getResourceModel('M2ePro/Buy_Listing_Other')->getMainTable() . ' ) as lo )'), 'main_table.id=t.listing_other_id', array('title' => 'title', 'general_id' => 'general_id', 'sku' => 'sku')); $listingOtherCollection->getSelect()->reset(Zend_Db_Select::COLUMNS); $listingOtherCollection->getSelect()->columns(array('is_m2epro_listing' => new Zend_Db_Expr(0), 'magento_sku' => 'cpe.sku', 'is_in_stock' => 'cisi.is_in_stock', 'product_name' => 't.title', 'listing_title' => new Zend_Db_Expr('NULL'), 'store_id' => new Zend_Db_Expr(0), 'account_id' => 'main_table.account_id', 'marketplace_id' => 'main_table.marketplace_id', 'listing_product_id' => new Zend_Db_Expr('NULL'), 'component_mode' => 'main_table.component_mode', 'product_id' => 'main_table.product_id', 'listing_id' => new Zend_Db_Expr('NULL'), 'status' => 'main_table.status', 'general_id_owner' => new Zend_Db_Expr('NULL'), 'general_id' => 't.general_id', 'online_sku' => 't.sku')); // --------------------------------------- // --------------------------------------- $selects = array($listingProductCollection->getSelect(), $listingOtherCollection->getSelect()); $unionSelect = Mage::getResourceModel('core/config')->getReadConnection()->select(); $unionSelect->union($selects); $resultCollection = new Varien_Data_Collection_Db(Mage::getResourceModel('core/config')->getReadConnection()); $resultCollection->getSelect()->reset()->from(array('main_table' => $unionSelect), array('is_m2epro_listing', 'magento_sku', 'is_in_stock', 'product_name', 'listing_title', 'store_id', 'account_id', 'marketplace_id', 'listing_product_id', 'component_mode', 'product_id', 'listing_id', 'status', 'general_id_owner', 'general_id', 'online_sku')); // Set collection to grid $this->setCollection($resultCollection); return parent::_prepareCollection(); }
public function callbackFilterPartsCompatibilityAttribute(Varien_Data_Collection_Db $collection, $column) { $value = $column->getFilter()->getValue(); if ($value == null) { return; } if (!$this->partsCompatibilityAttribute) { return; } if ($value == 1) { $attributeCode = $this->partsCompatibilityAttribute->getAttributeCode(); $collection->addFieldToFilter($attributeCode, array('notnull' => true)); $collection->addFieldToFilter($attributeCode, array('neq' => '\'\'')); $collection->addFieldToFilter('is_parts_compatibility_attribute_in_product_attribute_set', array('notnull' => true)); } else { $attributeId = $this->partsCompatibilityAttribute->getId(); $storeId = $this->getListing()->getStoreId(); $collection->getSelect()->joinLeft(array('eaa' => Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_text')), 'eaa.entity_id = e.entity_id and eaa.attribute_id = ' . $attributeId . ' and eaa.store_id = ' . $storeId); $collection->getSelect()->orWhere('eaa.value IS NULL'); $collection->getSelect()->orWhere('eaa.value = \'\''); $collection->getSelect()->orWhere('eea.entity_attribute_id IS NULL'); } }
/** * @param string $query * @param Varien_Data_Collection_Db $collection * @param string $mainTableKeyField * * @return $this */ public function joinMatched($query, $collection, $mainTableKeyField = 'e.entity_id') { $matchedIds = $this->_getMatchedIds($query); $this->_createTemporaryTable($matchedIds); $collection->getSelect()->joinLeft(array('tmp_table' => $this->_getTemporaryTableName()), '(tmp_table.entity_id=' . $mainTableKeyField . ')', array('relevance' => 'tmp_table.relevance')); $collection->getSelect()->where('tmp_table.id IS NOT NULL'); return $this; }