Feb 20, 2013
kalpesh

Magento: Reset download limit for downloadable product item/order

In Magento, downloadable products are the products which can be downloaded and does not require any shipping. When customer purchases this type of products, they get certain download limits to download their purchased items. This can be set in Products page in admin under “Downloadable Products” tab. In many cases store owner only want to give certain amount of downloads, so customers can’t download beyond that limit. But it may happen that due to bad internet or some interrupts downloads can’t be successful and customer want to reset that limit. For doing this, there is no option in backend to reset the download limits for the downloadable product items or for an entire order, and unfortunately admin use to re-order to achieve this.

Here is the query which will reset download limits for an entire order.

1
2
3
4
5
6
7
8
9
10
11
12
13
$q = Mage::getModel('sales/order_item')->getCollection()->addFieldToSelect('order_id');
$q->getSelect()->joinRight('downloadable_link_purchased_item as d', 'main_table.item_id = d.order_item_id',
        array('item_id','number_of_downloads_used'));
$q->getSelect()->where('main_table.order_id = '. (int)$orderId);
$q->load();

//echo $q->getSelect()->__toString();exit;

$items = $q->getColumnValues('item_id');
foreach($items as $id) {
        Mage::getModel('downloadable/link_purchased_item')->load($id)
                ->setStatus('available')->setNumberOfDownloadsUsed(0)->save();
}

The raw SQL query for the above Magento Convention query is:
UPDATE sales_flat_order_item s RIGHT JOIN downloadable_link_purchased_item d ON s.item_id = d.order_item_id SET d.number_of_downloads_used = 0 WHERE s.order_id = $orderId;

Here is another query that will allow you to reset download limits for a particular item in an order

1
2
3
4
5
6
7
8
9
10
11
12
13
$q = Mage::getModel('sales/order_item')->getCollection()->addFieldToSelect('order_id');
$q->getSelect()->joinRight('downloadable_link_purchased_item as d', 'main_table.item_id = d.order_item_id',
        array('item_id','number_of_downloads_used'));
$q->getSelect()->where('main_table.order_id = '. (int)$orderId .' AND main_table.item_id = '. (int)$orderItemId);
$q->load();

//echo $q->getSelect()->__toString();exit;

$items = $q->getColumnValues('item_id');
foreach($items as $id) {
        Mage::getModel('downloadable/link_purchased_item')->load($id)
                ->setStatus('available')->setNumberOfDownloadsUsed(0)->save();
}

The raw SQL query for the above Magento Convention query is:
UPDATE sales_flat_order_item s RIGHT JOIN downloadable_link_purchased_item d ON s.item_id = d.order_item_id SET d.number_of_downloads_used = 0 WHERE s.order_id = $orderId AND s.item_id = $orderItemId;

HTH!

1 Comment

  • Thanks for the query.

    However, being new to Magento, I have no idea where I would put this query so the Store owner can reset the download limit on their own.

    Is there a way that I can add this to the backend for the Store owner’s use?

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications

Recognition

Magento top 50 contributors

Honor

Contributions