protected function _prepareCollection()
{
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
$collection->joinTable(array('cisi' => 'cataloginventory_stock_item'), 'product_id=entity_id', array('qty' => 'qty', 'is_in_stock' => 'is_in_stock'), '{{table}}.stock_id=1', 'left');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'amazon_status' => 'status', 'component_mode' => 'component_mode', 'additional_data' => 'additional_data'), array('listing_id' => (int) $this->listing['id'], 'status' => array(\Ess\M2ePro\Model\Listing\Product::STATUS_LISTED, \Ess\M2ePro\Model\Listing\Product::STATUS_STOPPED, \Ess\M2ePro\Model\Listing\Product::STATUS_BLOCKED, \Ess\M2ePro\Model\Listing\Product::STATUS_UNKNOWN)));
$alpTable = $this->activeRecordFactory->getObject('Amazon\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('alp' => $alpTable), 'listing_product_id=id', array('general_id' => 'general_id', 'search_settings_status' => 'search_settings_status', 'amazon_sku' => 'sku', 'online_qty' => 'online_qty', 'online_price' => 'online_price', 'online_sale_price' => 'IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
NULL
)', 'online_sale_price_start_date' => 'online_sale_price_start_date', 'online_sale_price_end_date' => 'online_sale_price_end_date', 'is_afn_channel' => 'is_afn_channel', 'is_general_id_owner' => 'is_general_id_owner', 'is_variation_parent' => 'is_variation_parent', 'variation_child_statuses' => 'variation_child_statuses', 'variation_parent_id' => 'variation_parent_id', 'defected_messages' => 'defected_messages', 'min_online_price' => 'IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_start_date` <= CURRENT_DATE() AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
`alp`.`online_price`
)'), '{{table}}.is_variation_parent = 0');
// TODO
// $collection->getSelect()->joinLeft(
// array('malpr' => Mage::getResourceModel('M2ePro/Amazon_Listing_Product_Repricing')->getMainTable()),
// '(`alp`.`listing_product_id` = `malpr`.`listing_product_id`)',
// array(
// 'is_repricing' => 'listing_product_id',
// 'is_repricing_disabled' => 'is_online_disabled',
// )
// );
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->getSelect()->group('e.entity_id');
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
$collection->joinTable(array('cisi' => 'cataloginventory_stock_item'), 'product_id=entity_id', array('qty' => 'qty', 'is_in_stock' => 'is_in_stock'), '{{table}}.stock_id=1', 'left');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'amazon_status' => 'status', 'additional_data' => 'additional_data'), array('listing_id' => (int) $this->listing['id']));
$alpTable = $this->activeRecordFactory->getObject('Amazon\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('alp' => $alpTable), 'listing_product_id=id', array('general_id' => 'general_id', 'amazon_sku' => 'sku', 'online_qty' => 'online_qty', 'online_price' => 'online_price', 'online_sale_price' => 'online_sale_price', 'is_afn_channel' => 'is_afn_channel'), NULL, 'left');
// ---------------------------------------
// Set filter store
// ---------------------------------------
$store = $this->_getStore();
if ($store->getId()) {
$collection->joinAttribute('name', 'catalog_product/name', 'entity_id', NULL, 'left', $store->getId());
$collection->joinAttribute('magento_price', 'catalog_product/price', 'entity_id', NULL, 'left', $store->getId());
$collection->joinAttribute('status', 'catalog_product/status', 'entity_id', NULL, 'inner', $store->getId());
$collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', NULL, 'inner', $store->getId());
$collection->joinAttribute('thumbnail', 'catalog_product/thumbnail', 'entity_id', NULL, 'left', $store->getId());
} else {
$collection->addAttributeToSelect('price');
$collection->addAttributeToSelect('status');
$collection->addAttributeToSelect('visibility');
$collection->addAttributeToSelect('thumbnail');
}
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
$this->getCollection()->addWebsiteNamesToResult();
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
/** @var \Ess\M2ePro\Model\Listing $listing */
$listing = $this->getHelper('Data\\GlobalData')->getValue('view_listing');
// Get collection
// ---------------------------------------
$collection = $this->productFactory->create()->getCollection();
$collection->addAttributeToSelect('sku')->addAttributeToSelect('name')->addAttributeToSelect('type_id')->joinTable(array('cisi' => $this->resourceConnection->getTableName('cataloginventory_stock_item')), 'product_id=entity_id', array('qty' => 'qty', 'is_in_stock' => 'is_in_stock'), '{{table}}.stock_id=1', 'left');
// ---------------------------------------
// ---------------------------------------
$collection->joinTable(array('lp' => $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable()), 'product_id=entity_id', array('id' => 'id', 'ebay_status' => 'status', 'additional_data' => 'additional_data'), '{{table}}.listing_id=' . (int) $listing->getId());
$collection->joinTable(array('elp' => $this->activeRecordFactory->getObject('Ebay\\Listing\\Product')->getResource()->getMainTable()), 'listing_product_id=id', array('end_date' => 'end_date', 'start_date' => 'start_date', 'online_title' => 'online_title', 'online_sku' => 'online_sku', 'available_qty' => new \Zend_Db_Expr('(online_qty - online_qty_sold)'), 'ebay_item_id' => 'ebay_item_id', 'online_category' => 'online_category', 'online_qty_sold' => 'online_qty_sold', 'online_start_price' => 'online_start_price', 'online_current_price' => 'online_current_price', 'online_reserve_price' => 'online_reserve_price', 'online_buyitnow_price' => 'online_buyitnow_price', 'min_online_price' => 'IF(
(`t`.`variation_min_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_min_price`
)', 'max_online_price' => 'IF(
(`t`.`variation_max_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_max_price`
)'), NULL, 'left');
$collection->joinTable(array('ei' => $this->activeRecordFactory->getObject('Ebay\\Item')->getResource()->getMainTable()), 'id=ebay_item_id', array('item_id' => 'item_id'), NULL, 'left');
$collection->getSelect()->joinLeft(new \Zend_Db_Expr('(
SELECT
`mlpv`.`listing_product_id`,
MIN(`melpv`.`online_price`) as variation_min_price,
MAX(`melpv`.`online_price`) as variation_max_price
FROM `' . $this->activeRecordFactory->getObject('Listing\\Product\\Variation')->getResource()->getMainTable() . '` AS `mlpv`
INNER JOIN `' . $this->activeRecordFactory->getObject('Ebay\\Listing\\Product\\Variation')->getResource()->getMainTable() . '` AS `melpv`
ON (`mlpv`.`id` = `melpv`.`listing_product_variation_id`)
WHERE `melpv`.`status` != ' . \Ess\M2ePro\Model\Listing\Product::STATUS_NOT_LISTED . '
GROUP BY `mlpv`.`listing_product_id`
)'), 'elp.listing_product_id=t.listing_product_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price'));
// ---------------------------------------
// Set filter store
// ---------------------------------------
$store = $this->_getStore();
if ($store->getId()) {
$collection->joinAttribute('price', 'catalog_product/price', 'entity_id', NULL, 'left', $store->getId());
$collection->joinAttribute('status', 'catalog_product/status', 'entity_id', NULL, 'inner', $store->getId());
$collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', NULL, 'inner', $store->getId());
$collection->joinAttribute('thumbnail', 'catalog_product/thumbnail', 'entity_id', NULL, 'left', $store->getId());
} else {
$collection->addAttributeToSelect('price');
$collection->addAttributeToSelect('status');
$collection->addAttributeToSelect('visibility');
$collection->addAttributeToSelect('thumbnail');
}
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
$this->getCollection()->addWebsiteNamesToResult();
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
$collection->joinTable(array('cisi' => 'cataloginventory_stock_item'), 'product_id=entity_id', array('qty' => 'qty'), '{{table}}.stock_id=1', 'left');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'amazon_status' => 'status', 'component_mode' => 'component_mode', 'additional_data' => 'additional_data'), array('listing_id' => (int) $this->listing['id']));
$alpTable = $this->activeRecordFactory->getObject('Amazon\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('alp' => $alpTable), 'listing_product_id=id', array('template_shipping_override_id' => 'template_shipping_override_id', 'template_description_id' => 'template_description_id', 'general_id' => 'general_id', 'general_id_search_info' => 'general_id_search_info', 'search_settings_status' => 'search_settings_status', 'search_settings_data' => 'search_settings_data', 'variation_child_statuses' => 'variation_child_statuses', 'amazon_sku' => 'sku', 'online_qty' => 'online_qty', 'online_price' => 'online_price', 'online_sale_price' => 'IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
NULL
)', 'online_sale_price_start_date' => 'online_sale_price_start_date', 'online_sale_price_end_date' => 'online_sale_price_end_date', 'is_afn_channel' => 'is_afn_channel', 'is_general_id_owner' => 'is_general_id_owner', 'is_variation_parent' => 'is_variation_parent', 'defected_messages' => 'defected_messages', 'min_online_price' => 'IF(
(`t`.`variation_min_price` IS NULL),
IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_start_date` <= CURRENT_DATE() AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
`alp`.`online_price`
),
`t`.`variation_min_price`
)', 'max_online_price' => 'IF(
(`t`.`variation_max_price` IS NULL),
IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_start_date` <= CURRENT_DATE() AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
`alp`.`online_price`
),
`t`.`variation_max_price`
)'), '{{table}}.variation_parent_id is NULL');
$collection->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 `' . $alpTable . '` as malp
INNER JOIN `' . $lpTable . '` 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`
)'), 'alp.listing_product_id=t.variation_parent_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price'));
$tdTable = $this->activeRecordFactory->getObject('Template\\Description')->getResource()->getMainTable();
$collection->joinTable(array('td' => $tdTable), 'id=template_description_id', array('template_description_title' => 'title'), null, 'left');
// TODO
// $atsTable = $this->activeRecordFactory->getObject('Amazon\Template\ShippingOverride')
//->getResource()->getMainTable();
// $collection->joinTable(
// array('ats' => $atsTable),
// 'id=template_shipping_override_id',
// array(
// 'template_shipping_override_title' => 'title'
// ),
// null,
// 'left'
// );
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
$listingData = $this->listing->getData();
// ---------------------------------------
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'ebay_status' => 'status', 'component_mode' => 'component_mode', 'additional_data' => 'additional_data'), '{{table}}.listing_id=' . (int) $listingData['id']);
$elpTable = $this->activeRecordFactory->getObject('Ebay\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('elp' => $elpTable), 'listing_product_id=id', array('listing_product_id' => 'listing_product_id', 'end_date' => 'end_date', 'start_date' => 'start_date', 'online_title' => 'online_title', 'online_sku' => 'online_sku', 'available_qty' => new \Zend_Db_Expr('(elp.online_qty - elp.online_qty_sold)'), 'ebay_item_id' => 'ebay_item_id', 'online_category' => 'online_category', 'online_qty_sold' => 'online_qty_sold', 'online_bids' => 'online_bids', 'online_start_price' => 'online_start_price', 'online_current_price' => 'online_current_price', 'online_reserve_price' => 'online_reserve_price', 'online_buyitnow_price' => 'online_buyitnow_price', 'template_category_id' => 'template_category_id', 'min_online_price' => 'IF(
(`t`.`variation_min_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_min_price`
)', 'max_online_price' => 'IF(
(`t`.`variation_max_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_max_price`
)'));
$eiTable = $this->activeRecordFactory->getObject('Ebay\\Item')->getResource()->getMainTable();
$collection->joinTable(array('ei' => $eiTable), 'id=ebay_item_id', array('item_id' => 'item_id'), NULL, 'left');
$lpvTable = $this->activeRecordFactory->getObject('Listing\\Product\\Variation')->getResource()->getMainTable();
$elpvTable = $this->activeRecordFactory->getObject('Ebay\\Listing\\Product\\Variation')->getResource()->getMainTable();
$collection->getSelect()->joinLeft(new \Zend_Db_Expr('(
SELECT
`mlpv`.`listing_product_id`,
MIN(`melpv`.`online_price`) as variation_min_price,
MAX(`melpv`.`online_price`) as variation_max_price
FROM `' . $lpvTable . '` AS `mlpv`
INNER JOIN `' . $elpvTable . '` AS `melpv`
ON (`mlpv`.`id` = `melpv`.`listing_product_variation_id`)
WHERE `melpv`.`status` != ' . \Ess\M2ePro\Model\Listing\Product::STATUS_NOT_LISTED . '
GROUP BY `mlpv`.`listing_product_id`
)'), 'elp.listing_product_id=t.listing_product_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price'));
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
// ---------------------------------------
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'ebay_status' => 'status', 'additional_data' => 'additional_data'), '{{table}}.listing_id=' . (int) $this->listing->getId());
$elpTable = $this->activeRecordFactory->getObject('Ebay\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('elp' => $elpTable), 'listing_product_id=id', array('listing_product_id' => 'listing_product_id', 'template_category_id' => 'template_category_id', 'template_other_category_id' => 'template_other_category_id', 'template_payment_mode' => 'template_payment_mode', 'template_payment_id' => 'template_payment_id', 'template_payment_custom_id' => 'template_payment_custom_id', 'template_shipping_mode' => 'template_shipping_mode', 'template_shipping_id' => 'template_shipping_id', 'template_shipping_custom_id' => 'template_shipping_custom_id', 'template_return_policy_mode' => 'template_return_policy_mode', 'template_return_policy_id' => 'template_return_policy_id', 'template_return_policy_custom_id' => 'template_return_policy_custom_id', 'template_description_mode' => 'template_description_mode', 'template_description_id' => 'template_description_id', 'template_description_custom_id' => 'template_description_custom_id', 'template_selling_format_mode' => 'template_selling_format_mode', 'template_selling_format_id' => 'template_selling_format_id', 'template_selling_format_custom_id' => 'template_selling_format_custom_id', 'template_synchronization_mode' => 'template_synchronization_mode', 'template_synchronization_id' => 'template_synchronization_id', 'template_synchronization_custom_id' => 'template_synchronization_custom_id', 'end_date' => 'end_date', 'start_date' => 'start_date', 'online_title' => 'online_title', 'online_sku' => 'online_sku', 'available_qty' => new \Zend_Db_Expr('(online_qty - online_qty_sold)'), 'ebay_item_id' => 'ebay_item_id', 'online_category' => 'online_category', 'online_qty_sold' => 'online_qty_sold', 'online_start_price' => 'online_start_price', 'online_current_price' => 'online_current_price', 'online_reserve_price' => 'online_reserve_price', 'online_buyitnow_price' => 'online_buyitnow_price', 'min_online_price' => 'IF(
(`t`.`variation_min_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_min_price`
)', 'max_online_price' => 'IF(
(`t`.`variation_max_price` IS NULL),
`elp`.`online_current_price`,
`t`.`variation_max_price`
)'));
$eiTable = $this->activeRecordFactory->getObject('Ebay\\Item')->getResource()->getMainTable();
$collection->joinTable(array('ei' => $eiTable), 'id=ebay_item_id', array('item_id' => 'item_id'), NULL, 'left');
$etcTable = $this->activeRecordFactory->getObject('Ebay\\Template\\Category')->getResource()->getMainTable();
$collection->joinTable(array('etc' => $etcTable), 'id=template_category_id', array('category_main_mode' => 'category_main_mode', 'category_main_id' => 'category_main_id', 'category_main_path' => 'category_main_path', 'category_main_attribute' => 'category_main_attribute'), NULL, 'left');
$etocTable = $this->activeRecordFactory->getObject('Ebay\\Template\\OtherCategory')->getResource()->getMainTable();
$collection->joinTable(array('etoc' => $etocTable), 'id=template_other_category_id', array('category_secondary_mode' => 'category_secondary_mode', 'category_secondary_id' => 'category_secondary_id', 'category_secondary_path' => 'category_secondary_path', 'category_secondary_attribute' => 'category_secondary_attribute', 'store_category_main_mode' => 'store_category_main_mode', 'store_category_main_id' => 'store_category_main_id', 'store_category_main_path' => 'store_category_main_path', 'store_category_main_attribute' => 'store_category_main_attribute', 'store_category_secondary_mode' => 'store_category_secondary_mode', 'store_category_secondary_id' => 'store_category_secondary_id', 'store_category_secondary_path' => 'store_category_secondary_path', 'store_category_secondary_attribute' => 'store_category_secondary_attribute'), NULL, 'left');
// TODO NOT SUPPORTED FEATURES "ebay motors"
// if ($this->motorsAttribute) {
// $collection->addAttributeToSelect($this->motorsAttribute->getAttributeCode());
//
// $collection->joinTable(
// array('eea' => Mage::getSingleton('core/resource')->getTableName('eav_entity_attribute')),
// 'attribute_set_id=attribute_set_id',
// array(
// 'is_motors_attribute_in_product_attribute_set' => 'entity_attribute_id',
// ),
// '{{table}}.attribute_id = ' . $this->motorsAttribute->getAttributeId(),
// 'left'
// );
// }
$lpvTable = $this->activeRecordFactory->getObject('Listing\\Product\\Variation')->getResource()->getMainTable();
$elpvTable = $this->activeRecordFactory->getObject('Ebay\\Listing\\Product\\Variation')->getResource()->getMainTable();
$collection->getSelect()->joinLeft(new \Zend_Db_Expr('(
SELECT
`mlpv`.`listing_product_id`,
MIN(`melpv`.`online_price`) as variation_min_price,
MAX(`melpv`.`online_price`) as variation_max_price
FROM `' . $lpvTable . '` AS `mlpv`
INNER JOIN `' . $elpvTable . '` AS `melpv`
ON (`mlpv`.`id` = `melpv`.`listing_product_variation_id`)
WHERE `melpv`.`status` != ' . \Ess\M2ePro\Model\Listing\Product::STATUS_NOT_LISTED . '
GROUP BY `mlpv`.`listing_product_id`
)'), 'elp.listing_product_id=t.listing_product_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price'));
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
parent::_prepareCollection();
// TODO NOT SUPPORTED FEATURES "ebay motors"
// if ($this->isMotorsAvailable()) {
// $this->prepareExistingMotorsData();
// }
return $this;
}
protected function _prepareCollection()
{
// Get collection
// ---------------------------------------
/* @var $collection \Ess\M2ePro\Model\ResourceModel\Magento\Product\Collection */
$collection = $this->magentoProductCollectionFactory->create();
$collection->setListingProductModeOn();
$collection->addAttributeToSelect('sku');
$collection->addAttributeToSelect('name');
$collection->joinTable(array('cisi' => 'cataloginventory_stock_item'), 'product_id=entity_id', array('qty' => 'qty'), '{{table}}.stock_id=1', 'left');
// ---------------------------------------
// Join listing product tables
// ---------------------------------------
$lpTable = $this->activeRecordFactory->getObject('Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('lp' => $lpTable), 'product_id=entity_id', array('id' => 'id', 'amazon_status' => 'status', 'component_mode' => 'component_mode', 'additional_data' => 'additional_data'), array('listing_id' => (int) $this->listing['id']));
$alpTable = $this->activeRecordFactory->getObject('Amazon\\Listing\\Product')->getResource()->getMainTable();
$collection->joinTable(array('alp' => $alpTable), 'listing_product_id=id', array('general_id' => 'general_id', 'general_id_search_info' => 'general_id_search_info', 'search_settings_status' => 'search_settings_status', 'search_settings_data' => 'search_settings_data', 'variation_child_statuses' => 'variation_child_statuses', 'amazon_sku' => 'sku', 'online_qty' => 'online_qty', 'online_price' => 'online_price', 'online_sale_price' => 'IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
NULL
)', 'online_sale_price_start_date' => 'online_sale_price_start_date', 'online_sale_price_end_date' => 'online_sale_price_end_date', 'is_afn_channel' => 'is_afn_channel', 'is_general_id_owner' => 'is_general_id_owner', 'is_variation_parent' => 'is_variation_parent', 'defected_messages' => 'defected_messages', 'min_online_price' => 'IF(
(`t`.`variation_min_price` IS NULL),
IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_start_date` <= CURRENT_DATE() AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
`alp`.`online_price`
),
`t`.`variation_min_price`
)', 'max_online_price' => 'IF(
(`t`.`variation_max_price` IS NULL),
IF(
`alp`.`online_sale_price_start_date` IS NOT NULL AND
`alp`.`online_sale_price_end_date` IS NOT NULL AND
`alp`.`online_sale_price_start_date` <= CURRENT_DATE() AND
`alp`.`online_sale_price_end_date` >= CURRENT_DATE(),
`alp`.`online_sale_price`,
`alp`.`online_price`
),
`t`.`variation_max_price`
)'), '{{table}}.variation_parent_id is NULL');
// TODO
// $collection->getSelect()->joinLeft(
// array('malpr' => Mage::getResourceModel('M2ePro/Amazon_Listing_Product_Repricing')->getMainTable()),
// '(`alp`.`listing_product_id` = `malpr`.`listing_product_id`)',
// array(
// 'is_repricing' => 'listing_product_id',
// 'is_repricing_disabled' => 'is_online_disabled',
// )
// );
$collection->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 `' . $alpTable . '` as malp
INNER JOIN `' . $lpTable . '` 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 . ',
' . \Ess\M2ePro\Model\Listing\Product::STATUS_UNKNOWN . '
) AND `malp`.`variation_parent_id` IS NOT NULL
GROUP BY `malp`.`variation_parent_id`
)'), 'alp.listing_product_id=t.variation_parent_id', array('variation_min_price' => 'variation_min_price', 'variation_max_price' => 'variation_max_price'));
// TODO
// $collection->getSelect()->joinLeft(
// new Zend_Db_Expr('(
// SELECT
// `malp`.`variation_parent_id`,
// COUNT(*) AS `variation_repricing_enabled_count`
// FROM `'. Mage::getResourceModel('M2ePro/Amazon_Listing_Product')->getMainTable() .'` as malp
// LEFT JOIN `'.Mage::getResourceModel('M2ePro/Amazon_Listing_Product_Repricing')->getMainTable().'`
// AS `malpr` ON (`malp`.`listing_product_id` = `malpr`.`listing_product_id`)
// WHERE `malp`.`listing_product_id` IS NOT NULL AND `malpr`.`is_online_disabled` = 0
// GROUP BY `malp`.`variation_parent_id`
// )'),
// 'lp.id=t_2.variation_parent_id',
// array(
// 'variation_repricing_enabled_count' => 'variation_repricing_enabled_count',
// )
// );
// TODO
// $collection->getSelect()->joinLeft(
// new Zend_Db_Expr('(
// SELECT
// `malp`.`variation_parent_id`,
// COUNT(*) AS `variation_repricing_disabled_count`
// FROM `'. Mage::getResourceModel('M2ePro/Amazon_Listing_Product')->getMainTable() .'` as malp
// LEFT JOIN `'.Mage::getResourceModel('M2ePro/Amazon_Listing_Product_Repricing')->getMainTable().'`
// AS `malpr` ON (`malp`.`listing_product_id` = `malpr`.`listing_product_id`)
// WHERE `malp`.`listing_product_id` IS NOT NULL AND `malpr`.`is_online_disabled` = 1
// GROUP BY `malp`.`variation_parent_id`
// )'),
// 'lp.id=t_3.variation_parent_id',
// array(
// 'variation_repricing_disabled_count' => 'variation_repricing_disabled_count',
// )
// );
// TODO
// $collection->getSelect()->joinLeft(
// new \Zend_Db_Expr('(
// SELECT
// `malp`.`variation_parent_id`,
// COUNT(*) AS `variation_afn_count`
// FROM `'. $alpTable .'` as malp
// WHERE `malp`.`is_afn_channel` = 1
// GROUP BY `malp`.`variation_parent_id`
// )'),
// 'lp.id=t_4.variation_parent_id',
// array(
// 'variation_afn_count' => 'variation_afn_count',
// )
// );
// ---------------------------------------
// Set collection to grid
$this->setCollection($collection);
return parent::_prepareCollection();
}
