00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030 class Mage_CatalogRule_Model_Mysql4_Rule extends Mage_Core_Model_Mysql4_Abstract
00031 {
00032 const SECONDS_IN_DAY = 86400;
00033
00034
00035
00036
00037 protected function _construct()
00038 {
00039 $this->_init('catalogrule/rule', 'rule_id');
00040 }
00041
00042
00043
00044
00045
00046
00047 public function _beforeSave(Mage_Core_Model_Abstract $object)
00048 {
00049 if (!$object->getFromDate()) {
00050 $date = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
00051 $date->setHour(0)
00052 ->setMinute(0)
00053 ->setSecond(0);
00054 $object->setFromDate($date);
00055 }
00056 if ($object->getFromDate() instanceof Zend_Date) {
00057 $object->setFromDate($object->getFromDate()->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
00058 }
00059
00060 if (!$object->getToDate()) {
00061 $object->setToDate(new Zend_Db_Expr('NULL'));
00062 }
00063 else {
00064 if ($object->getToDate() instanceof Zend_Date) {
00065 $object->setToDate($object->getToDate()->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
00066 }
00067 }
00068 parent::_beforeSave($object);
00069 }
00070
00071
00072
00073
00074
00075
00076
00077 public function updateRuleProductData(Mage_CatalogRule_Model_Rule $rule)
00078 {
00079 $ruleId = $rule->getId();
00080 $write = $this->_getWriteAdapter();
00081 $write->beginTransaction();
00082
00083 $write->delete($this->getTable('catalogrule/rule_product'), $write->quoteInto('rule_id=?', $ruleId));
00084
00085 if (!$rule->getIsActive()) {
00086 $write->commit();
00087 return $this;
00088 }
00089
00090 $websiteIds = explode(',', $rule->getWebsiteIds());
00091 if (empty($websiteIds)) {
00092 return $this;
00093 }
00094
00095 $productIds = $rule->getMatchingProductIds();
00096 $customerGroupIds = $rule->getCustomerGroupIds();
00097
00098 $fromTime = strtotime($rule->getFromDate());
00099 $toTime = strtotime($rule->getToDate());
00100 $toTime = $toTime ? ($toTime + self::SECONDS_IN_DAY - 1) : 0;
00101
00102 $sortOrder = (int)$rule->getSortOrder();
00103 $actionOperator = $rule->getSimpleAction();
00104 $actionAmount = $rule->getDiscountAmount();
00105 $actionStop = $rule->getStopRulesProcessing();
00106
00107 $rows = array();
00108 $header = 'replace into '.$this->getTable('catalogrule/rule_product').' (
00109 rule_id,
00110 from_time,
00111 to_time,
00112 website_id,
00113 customer_group_id,
00114 product_id,
00115 action_operator,
00116 action_amount,
00117 action_stop,
00118 sort_order
00119 ) values ';
00120
00121 try {
00122 foreach ($productIds as $productId) {
00123 foreach ($websiteIds as $websiteId) {
00124 foreach ($customerGroupIds as $customerGroupId) {
00125 $rows[] = "(
00126 '$ruleId',
00127 '$fromTime',
00128 '$toTime',
00129 '$websiteId',
00130 '$customerGroupId',
00131 '$productId',
00132 '$actionOperator',
00133 '$actionAmount',
00134 '$actionStop',
00135 '$sortOrder')";
00136 if (sizeof($rows)==100) {
00137 $sql = $header.join(',', $rows);
00138 $write->query($sql);
00139 $rows = array();
00140 }
00141 }
00142 }
00143 }
00144 if (!empty($rows)) {
00145 $sql = $header.join(',', $rows);
00146 $write->query($sql);
00147 }
00148
00149 $write->commit();
00150 } catch (Exception $e) {
00151 $write->rollback();
00152 throw $e;
00153 }
00154
00155 return $this;
00156 }
00157
00158
00159
00160
00161
00162
00163
00164 public function getRuleProductIds($ruleId)
00165 {
00166 $read = $this->_getReadAdapter();
00167 $select = $read->select()->from($this->getTable('catalogrule/rule_product'), 'product_id')
00168 ->where('rule_id=?', $ruleId);
00169 return $read->fetchCol($select);
00170 }
00171
00172
00173
00174
00175
00176
00177
00178
00179
00180 public function removeCatalogPricesForDateRange($fromDate, $toDate, $productId=null)
00181 {
00182 $write = $this->_getWriteAdapter();
00183 $conds = array();
00184 $cond = $write->quoteInto('rule_date between ?', $this->formatDate($fromDate));
00185 $cond = $write->quoteInto($cond.' and ?', $this->formatDate($toDate));
00186 $conds[] = $cond;
00187 if (!is_null($productId)) {
00188 $conds[] = $write->quoteInto('product_id=?', $productId);
00189 }
00190
00191
00192
00193
00194
00195 $select = $this->_getWriteAdapter()->select()
00196 ->from($this->getTable('catalogrule/rule_product_price'), 'product_id')
00197 ->where(implode(' AND ', $conds));
00198 $insertQuery = 'REPLACE INTO ' . $this->getTable('catalogrule/affected_product') . ' (product_id)' . $select->__toString();
00199 $this->_getWriteAdapter()->query($insertQuery);
00200 $write->delete($this->getTable('catalogrule/rule_product_price'), $conds);
00201 return $this;
00202 }
00203
00204
00205
00206
00207
00208
00209
00210
00211 public function deleteOldData($date, $productId=null)
00212 {
00213 $write = $this->_getWriteAdapter();
00214 $conds = array();
00215 $conds[] = $write->quoteInto('rule_date<?', $this->formatDate($date));
00216 if (!is_null($productId)) {
00217 $conds[] = $write->quoteInto('product_id=?', $productId);
00218 }
00219 $write->delete($this->getTable('catalogrule/rule_product_price'), $conds);
00220 return $this;
00221 }
00222
00223
00224
00225
00226
00227
00228
00229
00230
00231
00232
00233 public function getRuleProductsForDateRange($fromDate, $toDate, $productId=null)
00234 {
00235 $read = $this->_getReadAdapter();
00236
00237 $select = $read->select()
00238 ->from($this->getTable('catalogrule/rule_product'))
00239 ->where($read->quoteInto('from_time=0 or from_time<=?', strtotime($toDate))
00240 ." or ".$read->quoteInto('to_time=0 or to_time>=?', strtotime($fromDate)))
00241 ->order(array('website_id', 'customer_group_id', 'product_id', 'sort_order'));
00242
00243
00244 if (!is_null($productId)) {
00245 $select->where('product_id=?', $productId);
00246 }
00247
00248 if (!$ruleProducts = $read->fetchAll($select)) {
00249 return false;
00250 }
00251 $productIds = array();
00252 foreach ($ruleProducts as $p) {
00253 $productIds[] = $p['product_id'];
00254 }
00255
00256 $priceAttr = Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'price');
00257
00258 $select = $read->select()
00259 ->from($priceAttr->getBackend()->getTable(), array('entity_id', 'store_id', 'value'))
00260 ->where('attribute_id=?', $priceAttr->getAttributeId())
00261 ->where('entity_id in (?)', $productIds)
00262 ->order('store_id');
00263
00264 $prices = $read->fetchAll($select);
00265
00266
00267
00268
00269 $productPrices = array();
00270 foreach ($prices as $index => $priceData) {
00271 $websiteId = Mage::app()->getStore($priceData['store_id'])->getWebsiteId();
00272
00273 if (!isset($productPrices[$priceData['entity_id']])) {
00274 $productPrices[$priceData['entity_id']] = array(
00275 'default' => $priceData['value'],
00276 'websites' => array($websiteId=>$priceData['value'])
00277 );
00278 }
00279 else {
00280 $productPrices[$priceData['entity_id']]['websites'][$websiteId] = $priceData['value'];
00281 }
00282 }
00283
00284 foreach ($ruleProducts as &$p) {
00285 if (isset($productPrices[$p['product_id']]['websites'][$p['website_id']])) {
00286 $p['price'] = $productPrices[$p['product_id']]['websites'][$p['website_id']];
00287 }
00288 elseif (isset($productPrices[$p['product_id']]['default'])) {
00289 $p['price'] = $productPrices[$p['product_id']]['default'];
00290 }
00291 }
00292
00293 return $ruleProducts;
00294 }
00295
00296
00297
00298
00299
00300
00301
00302
00303
00304
00305 protected function _getRuleProductsStmt($fromDate, $toDate, $productId=null, $websiteId = null)
00306 {
00307 $read = $this->_getReadAdapter();
00308
00309
00310
00311
00312
00313
00314
00315
00316
00317
00318 $select = $read->select()
00319 ->from(array('rp'=>$this->getTable('catalogrule/rule_product')))
00320 ->where($read->quoteInto('rp.from_time=0 or rp.from_time<=?', $toDate)
00321 ." or ".$read->quoteInto('rp.to_time=0 or rp.to_time>=?', $fromDate))
00322 ->order(array('rp.website_id', 'rp.customer_group_id', 'rp.product_id', 'rp.sort_order'));
00323
00324 if (!is_null($productId)) {
00325 $select->where('rp.product_id=?', $productId);
00326 }
00327
00328
00329
00330
00331 $priceAttr = Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'price');
00332 $priceTable = $priceAttr->getBackend()->getTable();
00333 $attributeId= $priceAttr->getId();
00334
00335 $joinCondition = '%1$s.entity_id=rp.product_id AND (%1$s.attribute_id='.$attributeId.') and %1$s.store_id=%2$s';
00336
00337 $select->join(
00338 array('pp_default'=>$priceTable),
00339 sprintf($joinCondition, 'pp_default', Mage_Core_Model_App::ADMIN_STORE_ID),
00340 array('default_price'=>'pp_default.value')
00341 );
00342
00343 if ($websiteId !== null) {
00344 $website = Mage::app()->getWebsite($websiteId);
00345 $defaultGroup = $website->getDefaultGroup();
00346 if ($defaultGroup instanceof Mage_Core_Model_Store_Group) {
00347 $storeId = $defaultGroup->getDefaultStoreId();
00348 } else {
00349 $storeId = Mage_Core_Model_App::ADMIN_STORE_ID;
00350 }
00351
00352 $select->joinInner(
00353 array('product_website'=>$this->getTable('catalog/product_website')),
00354 'product_website.product_id=rp.product_id AND product_website.website_id='.$websiteId,
00355 array()
00356 );
00357
00358 $tableAlias = 'pp'.$websiteId;
00359 $fieldAlias = 'website_'.$websiteId.'_price';
00360 $select->joinLeft(
00361 array($tableAlias=>$priceTable),
00362 sprintf($joinCondition, $tableAlias, $storeId),
00363 array($fieldAlias=>$tableAlias.'.value')
00364 );
00365 } else {
00366 foreach (Mage::app()->getWebsites() as $website) {
00367 $websiteId = $website->getId();
00368 $defaultGroup = $website->getDefaultGroup();
00369 if ($defaultGroup instanceof Mage_Core_Model_Store_Group) {
00370 $storeId = $defaultGroup->getDefaultStoreId();
00371 } else {
00372 $storeId = Mage_Core_Model_App::ADMIN_STORE_ID;
00373 }
00374
00375 $storeId = $defaultGroup->getDefaultStoreId();
00376 $tableAlias = 'pp'.$websiteId;
00377 $fieldAlias = 'website_'.$websiteId.'_price';
00378 $select->joinLeft(
00379 array($tableAlias=>$priceTable),
00380 sprintf($joinCondition, $tableAlias, $storeId),
00381 array($fieldAlias=>$tableAlias.'.value')
00382 );
00383 }
00384 }
00385 return $read->query($select);
00386 }
00387
00388
00389
00390
00391
00392
00393
00394
00395
00396
00397
00398 public function applyAllRulesForDateRange($fromDate=null, $toDate=null, $productId=null)
00399 {
00400 $write = $this->_getWriteAdapter();
00401 $write->beginTransaction();
00402
00403 Mage::dispatchEvent('catalogrule_before_apply', array('resource'=>$this));
00404
00405 $clearOldData = false;
00406 if ($fromDate === null) {
00407 $fromDate = mktime(0,0,0,date('m'),date('d')-1);
00408
00409
00410
00411
00412
00413 $clearOldData = true;
00414 }
00415 if (is_string($fromDate)) {
00416 $fromDate = strtotime($fromDate);
00417 }
00418 if ($toDate === null) {
00419 $toDate = mktime(0,0,0,date('m'),date('d')+1);
00420 }
00421 if (is_string($toDate)) {
00422 $toDate = strtotime($toDate);
00423 }
00424
00425 $product = null;
00426 if ($productId instanceof Mage_Catalog_Model_Product) {
00427 $product = $productId;
00428 $productId = $productId->getId();
00429 }
00430
00431 $this->removeCatalogPricesForDateRange($fromDate, $toDate, $productId);
00432 if ($clearOldData) {
00433 $this->deleteOldData($fromDate, $productId);
00434 }
00435
00436 try {
00437
00438
00439
00440
00441 foreach (Mage::app()->getWebsites(false) as $website) {
00442 $productsStmt = $this->_getRuleProductsStmt(
00443 $fromDate,
00444 $toDate,
00445 $productId,
00446 $website->getId()
00447 );
00448
00449 $dayPrices = array();
00450 $stopFlags = array();
00451 $prevKey = null;
00452
00453 while ($ruleData = $productsStmt->fetch()) {
00454 $ruleProductId = $ruleData['product_id'];
00455 $productKey= $ruleProductId . '_'
00456 . $ruleData['website_id'] . '_'
00457 . $ruleData['customer_group_id'];
00458
00459 if ($prevKey && ($prevKey != $productKey)) {
00460 $stopFlags = array();
00461 }
00462
00463
00464
00465
00466 for ($time=$fromDate; $time<=$toDate; $time+=self::SECONDS_IN_DAY) {
00467 if (($ruleData['from_time']==0 || $time >= $ruleData['from_time'])
00468 && ($ruleData['to_time']==0 || $time <=$ruleData['to_time'])) {
00469
00470 $priceKey = $time . '_' . $productKey;
00471
00472 if (isset($stopFlags[$priceKey])) {
00473 continue;
00474 }
00475
00476 if (!isset($dayPrices[$priceKey])) {
00477 $dayPrices[$priceKey] = array(
00478 'rule_date' => $time,
00479 'website_id' => $ruleData['website_id'],
00480 'customer_group_id' => $ruleData['customer_group_id'],
00481 'product_id' => $ruleProductId,
00482 'rule_price' => $this->_calcRuleProductPrice($ruleData),
00483 'latest_start_date' => $ruleData['from_time'],
00484 'earliest_end_date' => $ruleData['to_time'],
00485 );
00486 }
00487 else {
00488 $dayPrices[$priceKey]['rule_price'] = $this->_calcRuleProductPrice(
00489 $ruleData,
00490 $dayPrices[$priceKey]
00491 );
00492 $dayPrices[$priceKey]['latest_start_date'] = max(
00493 $dayPrices[$priceKey]['latest_start_date'],
00494 $ruleData['from_time']
00495 );
00496 $dayPrices[$priceKey]['earliest_end_date'] = min(
00497 $dayPrices[$priceKey]['earliest_end_date'],
00498 $ruleData['to_time']
00499 );
00500 }
00501
00502 if ($ruleData['action_stop']) {
00503 $stopFlags[$priceKey] = true;
00504 }
00505 }
00506 }
00507
00508 $prevKey = $productKey;
00509
00510 if (count($dayPrices)>100) {
00511 $this->_saveRuleProductPrices($dayPrices);
00512 $dayPrices = array();
00513 }
00514 }
00515 $this->_saveRuleProductPrices($dayPrices);
00516 }
00517 $this->_saveRuleProductPrices($dayPrices);
00518 $write->commit();
00519
00520
00521
00522
00523
00524
00525
00526
00527
00528
00529
00530
00531
00532
00533
00534
00535
00536
00537
00538
00539
00540
00541
00542
00543
00544
00545
00546
00547
00548
00549
00550
00551
00552
00553
00554
00555
00556
00557
00558
00559
00560
00561
00562
00563
00564
00565
00566
00567
00568
00569
00570
00571
00572
00573
00574
00575
00576
00577
00578
00579
00580
00581
00582
00583
00584
00585
00586
00587 } catch (Exception $e) {
00588 $write->rollback();
00589 throw $e;
00590 }
00591
00592 $productCondition = Mage::getModel('catalog/product_condition')
00593 ->setTable($this->getTable('catalogrule/affected_product'))
00594 ->setPkFieldName('product_id');
00595 Mage::dispatchEvent('catalogrule_after_apply', array(
00596 'product'=>$product,
00597 'product_condition' => $productCondition
00598 ));
00599 $write->delete($this->getTable('catalogrule/affected_product'));
00600
00601 return $this;
00602 }
00603
00604
00605
00606
00607
00608
00609
00610
00611 protected function _calcRuleProductPrice($ruleData, $productData=null)
00612 {
00613 if ($productData !== null && isset($productData['rule_price'])) {
00614 $productPrice = $productData['rule_price'];
00615 }
00616 else {
00617 $websiteId = $ruleData['website_id'];
00618 if (isset($ruleData['website_'.$websiteId.'_price'])) {
00619 $productPrice = $ruleData['website_'.$websiteId.'_price'];
00620 }
00621 else {
00622 $productPrice = $ruleData['default_price'];
00623 }
00624 }
00625
00626 $amount = $ruleData['action_amount'];
00627 switch ($ruleData['action_operator']) {
00628 case 'to_fixed':
00629 $productPrice = $amount;
00630 break;
00631 case 'to_percent':
00632 $productPrice= $productPrice*$amount/100;
00633 break;
00634 case 'by_fixed':
00635 $productPrice -= $amount;
00636 break;
00637 case 'by_percent':
00638 $productPrice = $productPrice*(1-$amount/100);
00639 break;
00640 }
00641
00642 $productPrice = max($productPrice, 0);
00643 return Mage::app()->getStore()->roundPrice($productPrice);
00644 }
00645
00646
00647
00648
00649
00650
00651
00652 protected function _saveRuleProductPrices($arrData)
00653 {
00654 if (empty($arrData)) {
00655 return $this;
00656 }
00657 $header = 'replace into '.$this->getTable('catalogrule/rule_product_price').' (
00658 rule_date,
00659 website_id,
00660 customer_group_id,
00661 product_id,
00662 rule_price,
00663 latest_start_date,
00664 earliest_end_date
00665 ) values ';
00666 $rows = array();
00667 $productIds = array();
00668 foreach ($arrData as $data) {
00669 $productIds[$data['product_id']] = true;
00670 $data['rule_date'] = $this->formatDate($data['rule_date'], false);
00671 $data['latest_start_date'] = $this->formatDate($data['latest_start_date'], false);
00672 $data['earliest_end_date'] = $this->formatDate($data['earliest_end_date'], false);
00673 $rows[] = '(' . $this->_getWriteAdapter()->quote($data) . ')';
00674 }
00675 $query = $header.join(',', $rows);
00676 $insertQuery = 'REPLACE INTO ' . $this->getTable('catalogrule/affected_product') . ' (product_id) VALUES ' .
00677 '(' . join('),(', array_keys($productIds)) . ')';
00678 $this->_getWriteAdapter()->query($insertQuery);
00679 $this->_getWriteAdapter()->query($query);
00680 return $this;
00681 }
00682
00683
00684
00685
00686
00687
00688
00689
00690
00691
00692
00693 public function getRulePrice($date, $wId, $gId, $pId)
00694 {
00695 $read = $this->_getReadAdapter();
00696 $select = $read->select()
00697 ->from($this->getTable('catalogrule/rule_product_price'), 'rule_price')
00698 ->where('rule_date=?', $this->formatDate($date, false))
00699 ->where('website_id=?', $wId)
00700 ->where('customer_group_id=?', $gId)
00701 ->where('product_id=?', $pId);
00702 return $read->fetchOne($select);
00703 }
00704
00705
00706
00707
00708
00709
00710
00711
00712
00713 public function getRulesForProduct($date, $wId, $pId)
00714 {
00715 $read = $this->_getReadAdapter();
00716 $select = $read->select()
00717 ->from($this->getTable('catalogrule/rule_product_price'), '*')
00718 ->where('rule_date=?', $this->formatDate($date, false))
00719 ->where('website_id=?', $wId)
00720 ->where('product_id=?', $pId);
00721 return $read->fetchAll($select);
00722 }
00723
00724
00725
00726
00727
00728
00729
00730
00731
00732 public function applyToProduct($rule, $product, $websiteIds)
00733 {
00734 if (!$rule->getIsActive()) {
00735 return $this;
00736 }
00737
00738 $ruleId = $rule->getId();
00739 $productId = $product->getId();
00740
00741 $write = $this->_getWriteAdapter();
00742 $write->beginTransaction();
00743
00744 $write->delete($this->getTable('catalogrule/rule_product'), array(
00745 $write->quoteInto('rule_id=?', $ruleId),
00746 $write->quoteInto('product_id=?', $productId),
00747 ));
00748
00749 if (!$rule->getConditions()->validate($product)) {
00750 $write->delete($this->getTable('catalogrule/rule_product_price'), array(
00751 $write->quoteInto('product_id=?', $productId),
00752 ));
00753 $write->commit();
00754 return $this;
00755 }
00756
00757 $customerGroupIds = $rule->getCustomerGroupIds();
00758
00759 $fromTime = strtotime($rule->getFromDate());
00760 $toTime = strtotime($rule->getToDate());
00761 $toTime = $toTime ? $toTime+self::SECONDS_IN_DAY-1 : 0;
00762
00763 $sortOrder = (int)$rule->getSortOrder();
00764 $actionOperator = $rule->getSimpleAction();
00765 $actionAmount = $rule->getDiscountAmount();
00766 $actionStop = $rule->getStopRulesProcessing();
00767
00768 $rows = array();
00769 $header = 'replace into '.$this->getTable('catalogrule/rule_product').' (
00770 rule_id,
00771 from_time,
00772 to_time,
00773 website_id,
00774 customer_group_id,
00775 product_id,
00776 action_operator,
00777 action_amount,
00778 action_stop,
00779 sort_order
00780 ) values ';
00781 try {
00782 foreach ($websiteIds as $websiteId) {
00783 foreach ($customerGroupIds as $customerGroupId) {
00784 $rows[] = "(
00785 '$ruleId',
00786 '$fromTime',
00787 '$toTime',
00788 '$websiteId',
00789 '$customerGroupId',
00790 '$productId',
00791 '$actionOperator',
00792 '$actionAmount',
00793 '$actionStop',
00794 '$sortOrder'
00795 )";
00796 if (sizeof($rows)==100) {
00797 $sql = $header.join(',', $rows);
00798 $write->query($sql);
00799 $rows = array();
00800 }
00801 }
00802 }
00803
00804 if (!empty($rows)) {
00805 $sql = $header.join(',', $rows);
00806 $write->query($sql);
00807 }
00808 } catch (Exception $e) {
00809 $write->rollback();
00810 throw $e;
00811
00812 }
00813 $this->applyAllRulesForDateRange(null, null, $product);
00814 $write->commit();
00815 return $this;
00816 }
00817 }