4

My Boss has asked my to provide a list of all products that have not sold in the last 3 months listed in order of most stock

I have hunted for some code I can hack already but found very little, I am surprised no one has asked this question.

I have been trying to modify the reports/product_collection but the results are inaccurate, I was thinking it might be as simple as changing the DESC to ASC in the best sellers listing and then add a date range ->setOrder('ordered_qty', 'asc')

however I think the collection is limited to products that have at least sold one of, which is not what we want

This is what I have been using but I am starting to think I should be using the sales/order_item_collection instead?

<?php

require_once 'app/Mage.php';
Mage::app('default');

?>

<ul>
<?php
$from = date("Y-m-d H:i:s", (time()-2592000));
$to = date("Y-m-d H:i:s", time());

$_productCollection = Mage::getResourceModel('reports/product_collection')
->addAttributeToSelect('*')
->addOrderedQty($from, $to, true)
->addAttributeToFilter('status', 1)
//->addAttributeToFilter('sku', $sku)
->setOrder('ordered_qty', 'asc')
->getSelect()->limit(50)->query();


echo ' 50 Products not sold from '.$from.' until '.$to.'<br><br><br>'; 

foreach ( $_productCollection as $prod ) :
$_product = Mage::getModel('catalog/product');
$_product->load($prod['entity_id']);

$num = (int) Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product)->getQty(); 

($i%2) ? $even = "class='even-li'" : $even = "";
?>
<li <?php echo $even ?>>
<div><a href="<?php echo $_product->getUrlPath() ?>" title="View <?php echo $_product->name ?>"></a>
<a href="<?php echo $_product->getUrlPath() ?>" title="View <?php echo $_product->name ?>"><?php echo Mage::helper('core/string')->truncate($_product->name, 150) ?></a> ( <?php echo 'Qty in Stock '.$num ?> )
<p><?php //echo Mage::helper('core/string')->truncate(strip_tags($_product->description), 130); ?></p></div>
</li>
<?php
endforeach;
?>
</ul>

cheers FireHorse

1
  $collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
            ->addStoreFilter()
            ->addPriceData()
            ->addTaxPercents()
            ->addUrlRewrite()
            ->setPageSize(6);
        $collection->getSelect()
            ->joinLeft(
                array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
                "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'",
                array('SUM(aggregation.qty_ordered) AS sold_quantity')
            )
            ->group('e.entity_id')
            ->order(array('sold_quantity ASC', 'e.created_at'));
        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
        return $collection;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.