I have created custom from and to dates to validate the shopping cart rule coupons.
For this I have overridden the Mage_SalesRule_Model_Mysql4_Rule_Collection setValidationFilter().
protected function setValidationFilter($websiteId, $customerGroupId, $couponCode='', $now=null)
{
if ($this->getFlag('validation_filter')) {
return $this;
}
if ($couponCode)
$couponCode = explode(',', $couponCode); // multiple coupon compatibility
$this->getSelect()->reset();
$this->getSelect()->from(array('main_table' => $this->getTable('salesrule/rule')));
//$this->getSelect()->reset(Zend_Db_Select::WHERE);
$this->addWebsiteGroupDateFilter($websiteId, $customerGroupId, $now);
if ($couponCode) {
$this->getSelect()->joinLeft(array('c' => $this->getTable('salesrule/coupon')),
'main_table.rule_id = c.rule_id ',
array('code')
);
// Mage_SalesRule_Model_Rule::COUPON_TYPE_NO_COUPON is not defined
$this->getSelect()->where(
$this->getSelect()->getAdapter()->quoteInto(' main_table.coupon_type = ?', 1)
.
$this->getSelect()->getAdapter()->quoteInto(' OR c.code IN(?)', $couponCode)
);
//modify rule date to validate with custom dates
if (is_null($now)) {
$now = Mage::getModel('core/date')->date('Y-m-d');
}
$this->getSelect()->join(array('cd' => $this->getTable('couponenhancement/couponenhancement')),
'main_table.rule_id = cd.rule_id ',
array('cd.coupon_valid_from','cd.coupon_valid_to')
);
$this->getSelect()->where('cd.coupon_valid_from is null or cd.coupon_valid_from<=?', $now);
$this->getSelect()->where('cd.coupon_valid_to is null or cd.coupon_valid_to>=?', $now);
//ends
$this->getSelect()->group('main_table.rule_id');
}
else {
$this->getSelect()->where('main_table.coupon_type = ?', 1);
}
$this->getSelect()->order('sort_order');
$this->setFlag('validation_filter', true);
return $this;
}
Printing the query gives me this..
SELECT `main_table`.*, `c`.`code`, `cd`.`coupon_valid_from`, `cd`.`coupon_valid_to` FROM `salesrule` AS `main_table` INNER JOIN `salesrule_customer_group` AS `customer_group_ids` ON main_table.rule_id = customer_group_ids.rule_id AND customer_group_ids.customer_group_id = 1 LEFT JOIN `salesrule_coupon` AS `c` ON main_table.rule_id = c.rule_id INNER JOIN `couponenhancement` AS `cd` ON main_table.rule_id = cd.rule_id WHERE (EXISTS (SELECT 1 FROM `salesrule_website` AS `website` WHERE (website.website_id IN ('3')) AND (main_table.rule_id = website.rule_id))) AND (**from_date** is null or from_date = '2015-03-09') AND (is_active = '1') AND ( main_table.coupon_type = 1 OR c.code IN('APRGE05-2687-3459')) AND (cd.coupon_valid_from is null or cd.coupon_valid_from='2015-03-09') GROUP BY `main_table`.`rule_id` ORDER BY `sort_order` ASC
PROBLEM HERE IS the query also includes the default date columns(from_date & to_date) as well as My custom column for dates (coupon_valid_from & coupon_valid_to).
Now I need to Remove ONLY the default date columns from_date & to_date from the query. and my validation will work fine.
Is there a way to do it. $this->getSelect()->reset(Zend_Db_Select::WHERE)
removed full where condition.. but I only need to remove those particular from_date and to_date where fields.
Thanks