Magento BestSeller 模块 - 汇总可配置产品并将其添加回

Question

这已经困扰我很长一段时间了。基本上，我们想要实现的目标是在首页的畅销书上列出产品的销量。对于简单产品，这工作正常，但是对于可配置产品，它们将显示为订购数量为 0。

我以某种方式需要找到一种方法来获取可配置产品，找到附加到它们的简单产品，将这些简单产品的销售量相加产品，将其添加回可配置产品 ID 并将此信息反馈回来，以便它将列出已售出的正确数量的可配置产品。

我认为，我已经放置了需要更改的代码区域。如果有人可以提供帮助，将不胜感激！

集合.php

class Luxe_Bestsellers_Model_Mysql4_Product_Collection extends Mage_Reports_Model_Mysql4_Product_Collection
{
public function addOrderedQty($from = '', $to = '', $getComplexProducts=false)
{
    $qtyOrderedTableName = $this->getTable('sales/order_item');
    $qtyOrderedFieldName = 'qty_ordered';

    $productIdFieldName = 'product_id';

    if (!$getComplexProducts) {
        $compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
        $productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);
    } else {
        $productTypes = '';
    }

    if ($from != '' && $to != '') {
        $dateFilter = " AND `order`.created_at BETWEEN '{$from}' AND '{$to}'";
    } else {
        $dateFilter = "";
    }

    $this->getSelect()->reset()->from(
        array('order_items' => $qtyOrderedTableName),
        array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})")
    );

     $_joinCondition = $this->getConnection()->quoteInto(
            'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
     );
     $_joinCondition .= $dateFilter;
     $this->getSelect()->joinInner(
        array('order' => $this->getTable('sales/order')),
        $_joinCondition,
        array()
     );


    $this->getSelect()
        ->joinInner(array('e' => $this->getProductEntityTableName()),
            "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}")
        ->group('e.entity_id')
        ->having('ordered_qty > 0');

    return $this;
}

}

列表.php

class Luxe_Bestsellers_Block_List extends Mage_Catalog_Block_Product_List
{
protected $_defaultToolbarBlock = 'bestsellers/list_toolbar';

protected function _beforeToHtml() {
    $this->addPriceBlockType('bundle', 'bundle/catalog_product_price', 'bundle/catalog/product/price.phtml');
    return parent::_beforeToHtml();    
}

public function _toHtml()
{
    if ($this->_productCollection->count()) {
        return parent::_toHtml();
    } else {
        return '';
    }
}

public function getTimeLimit()
{
    if ($this->getData('time_limit_in_days')) {
        return intval($this->getData('time_limit_in_days'));
    } else {
        return intval(Mage::getStoreConfig('bestsellers/bestsellers/time_limit_in_days'));
    }
}

public function getBlockTitle()
{
    if ($this->getData('title')) {
        return $this->getData('title');
    } else {
        return Mage::getStoreConfig('bestsellers/bestsellers/title');
    }
}

public function isShowOutOfStock() {
    return (bool)Mage::getStoreConfig('bestsellers/bestsellers/show_out_of_stock');
}

public function getProductsLimit()
{
    if ($this->getData('limit')) {
        return intval($this->getData('limit'));
    } else {
        return $this->getToolbarBlock()->getLimit();
    }
}

public function getDisplayMode()
{
    return $this->getData('display_mode');
}

/**
 * Retrieve loaded category collection
 *
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
protected function _getProductCollection()
{
    if (is_null($this->_productCollection)) {
        $layer = Mage::getModel('catalog/layer');
        $bestsellers = Mage::getResourceModel('reports/product_collection');
        if ($this->getTimeLimit()) {
            $product = Mage::getModel('catalog/product');
            $todayDate = $product->getResource()->formatDate(time());
            $startDate = $product->getResource()->formatDate(time() - 60 * 60 * 24 * $this->getTimeLimit());
            $bestsellers->addOrderedQty($startDate, $todayDate, true);
        } else {
            $bestsellers->addOrderedQty('', '', true);
        }

        $bestsellers->addStoreFilter()
                ->setOrder('ordered_qty', 'desc')
                ->setPageSize($this->getProductsLimit());

        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($bestsellers);

        if ($layer->getCurrentCategory()->getId() != Mage::app()->getStore()->getRootCategoryId()) {
            $bestsellers->addCategoryFilter($layer->getCurrentCategory());
            Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($bestsellers);
        }

        if (!$this->isShowOutOfStock()) {
            Mage::getModel('cataloginventory/stock')->addInStockFilterToCollection($bestsellers);
        }

        $bestsellers->getSelect()->where('order.store_id = ?', Mage::app()->getStore()->getId());

        $productIds = array();
        foreach ($bestsellers as $p) {
            $productIds[] = $p->getId();
        }
        $collection = Mage::getResourceModel('catalog/product_collection');

        Mage::getModel('catalog/layer')->prepareProductCollection($collection);

        $attributes = Mage::getSingleton('catalog/config')->getProductAttributes();
        $collection->addIdFilter($productIds)
                   ->addAttributeToSelect($attributes)
                   ->addMinimalPrice()
                   ->addFinalPrice();
        $this->_productCollection = $collection;
    }
    return $this->_productCollection;
}

/**
 * Translate block sentence
 *
 * @return string
 */
public function __()
{
    $args = func_get_args();
    $expr = new Mage_Core_Model_Translate_Expr(array_shift($args), 'Mage_Catalog');
    array_unshift($args, $expr);
    return Mage::app()->getTranslator()->translate($args);
}
}

Answer 1

感谢您发布示例代码！我能够使用它来创建一个对我们双方都适用的解决方案。

我发现可配置产品销售额已正确汇总，但未包含在结果中；相反，他们的子产品出现了。我的解决方案是包含可配置产品，在 catalog_product_super_link 表上执行左连接，并过滤掉任何具有 parent_id 的内容。以下是您需要进行的更改：

Collection.php：

    public function addOrderedQty($from = '', $to = '', $getComplexProducts=false, $getComplexChildProducts = true, $getRemovedProducts = true)
    {
        $qtyOrderedTableName = $this->getTable('sales/order_item');
        $qtyOrderedFieldName = 'qty_ordered';

        $productIdFieldName = 'product_id';

        if (!$getComplexProducts) {
            $compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
            $productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);
        } else {
            $productTypes = '';
        }

        if ($from != '' && $to != '') {
            $dateFilter = " AND `order`.created_at BETWEEN '{$from}' AND '{$to}'";
        } else {
            $dateFilter = "";
        }

        $this->getSelect()->reset()->from(
            array('order_items' => $qtyOrderedTableName),
            array(
                'ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})",
                'order_items_name' => 'order_items.name'
            )
        );

         $_joinCondition = $this->getConnection()->quoteInto(
                'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
         );
         $_joinCondition .= $dateFilter;
         $this->getSelect()->joinInner(
            array('order' => $this->getTable('sales/order')),
            $_joinCondition,
            array()
         );

         // Add join to get the parent id for configurables
         $this->getSelect()->joinLeft(
             array('cpsl' => $this->getTable('catalog/product_super_link')),
             'cpsl.product_id = order_items.product_id',
             'cpsl.parent_id'
         );

        if(!$getComplexChildProducts)
            $this->getSelect()->having('parent_id IS NULL');

        if($getRemovedProducts)
        {
             $this->getSelect()
                ->joinLeft(array('e' => $this->getProductEntityTableName()),
                    "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}")
                ->group('order_items.product_id');
        }
        else
        {
            $this->getSelect()
                ->joinInner(array('e' => $this->getProductEntityTableName()),
                    "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}")
                ->group('e.entity_id');
        }


        $this->getSelect()->having('ordered_qty > 0');

        // This line is for debug purposes, in case you'd like to see what the SQL looks like
        // $x = $this->getSelect()->__toString();

        return $this;
    }

List.php - 找到以下两行...

$bestsellers->addOrderedQty($startDate, $todayDate, true);
$bestsellers->addOrderedQty('', '', true);

...并将其更改为：

$bestsellers->addOrderedQty($startDate, $todayDate, true, false, false);
$bestsellers->addOrderedQty('', '', true, false, false);

我的更改添加了两个新的可选参数，它们都默认为 true，以免破坏现有功能。

• 当 $getComplexChildProducts 设置为 false 时，可配置产品的所有子项将从结果中删除。
• $getRemovedProducts 确定以前订购的产品（已从 Magento 中删除）是否也应显示。

请注意，您的报告统计数据需要是最新的才能获得准确的结果。

希望这有帮助！如果您有任何疑问，请告诉我。

Answer 2

您可以使用以下代码片段将简单产品附加到可配置产品。我不确定这是否100%正确，我自己没有尝试过。

$simpleProducts = Mage::getModel('catalog/product_type_configurable')->getUsedProducts(null, $product);