Magento was not recording correct number of times a shopping cart coupon was used. When I ran below query in database, it was giving wrong information as far as I know.
mysql> select u.coupon_id, c.code, count(*) as total from salesrule_coupon_usage u, salesrule_coupon c where u.coupon_id=c.coupon_id and u.coupon_id IN (119, 132, 91) group by u.coupon_id;
May be I was wrong to use that query to find usage :), so tried another query, which gave me better report.
mysql> select UCASE(coupon_code) as `Coupon Code`, status as `Order Status`, count(*) as `Times Used` from sales_flat_order where coupon_code = 'APPLE14' group by coupon_code, status;
And this is what I got.
I also, wrote quick shell script to test, which looks like this:
<?php /* * author : Damodar Bashyal * url: http://twitter.com/dbashyal * usage: php couponusage.php --coupon 'coupon1,coupon2,coupon3' */ require_once 'abstract.php'; class DSE_Shell_coupon_usage extends Mage_Shell_Abstract { public function run() { $coupons = $this->getArg('coupon'); if(!$coupons){ echo "\nNo coupon code provided.\n"; return; } $coupon_code = explode(',', $coupons); /** @var $coupon Mage_SalesRule_Model_Coupon */ $coupon = Mage::getModel('salesrule/coupon'); $collection = $coupon->getCollection(); $collection->addFieldToFilter('code', array('in' => $coupon_code)); echo "\nCoupons and usage count:\n------------------------"; foreach($collection as $coupon){ $times = 0; if ($coupon->getId()) { $times = $coupon->getTimesUsed(); } echo "\n" . $coupon->getCode() . ": used {$times} times."; } echo "\n-- Complete!!! \n\n"; return; } } $shell = new DSE_Shell_coupon_usage(); $shell->run();
Bonus:
Set all sales rules that already expired to inactive. CURDATE() returns current date in mysql.
update salesrule set is_active=0 where to_date < CURDATE() and to_date IS NOT NULL and is_active=1;
Set all catalogue rules that already expired to inactive.
update catalogrule set is_active=0 where to_date < CURDATE() and is_active = 1;
Please comment below with your tricks that worked.
zwangerschapsjurken posted on - Tuesday 16th of December 2014 08:09:55 AM