From 5c4d65599fee6dfdf661b4932d12c5905e60ac90 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Fri, 26 Dec 2014 19:45:01 +0530 Subject: [PATCH] CRM-15735 fix - Update payment status for pay-later membership renewal ignores Received date https://issues.civicrm.org/jira/browse/CRM-15735 --- CRM/Core/BAO/ActionSchedule.php | 43 ++++++++++++- CRM/Upgrade/Incremental/php/FourSix.php | 54 ++++++++++++++++ .../CRM/Core/BAO/ActionScheduleTest.php | 61 ++++++++++++++++++- xml/schema/Core/ActionLog.xml | 7 +++ 4 files changed, 162 insertions(+), 3 deletions(-) diff --git a/CRM/Core/BAO/ActionSchedule.php b/CRM/Core/BAO/ActionSchedule.php index 48096aac15..ee849744b3 100644 --- a/CRM/Core/BAO/ActionSchedule.php +++ b/CRM/Core/BAO/ActionSchedule.php @@ -961,6 +961,7 @@ WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL // $limitWhere - this filtering applies only for // 'limit to' option $select = $join = $where = $limitWhere = array(); + $selectColumns = "contact_id, entity_id, entity_table, action_schedule_id"; $limitTo = $actionSchedule->limit_to; $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR) @@ -1097,6 +1098,13 @@ WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id'); $mStatus = implode(',', $membershipStatus); $where[] = "e.status_id IN ({$mStatus})"; + + // We are not tracking the reference date for 'repeated' schedule reminders as + // it will violate the repeat use-case, for further details please check CRM-15376 + if ($actionSchedule->start_action_date && $actionSchedule->is_repeat == FALSE) { + $select[] = $dateField; + $selectColumns = "reference_date, " . $selectColumns; + } } if ($mapping->entity == 'civicrm_contact') { @@ -1250,14 +1258,47 @@ reminder.action_schedule_id = %1"; } $query = " -INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) +INSERT INTO civicrm_action_log ({$selectColumns}) {$selectClause} {$fromClause} {$joinClause} LEFT JOIN {$reminderJoinClause} {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause} "; + + // In some cases reference_date got outdated due to many reason + // e.g. In Membership renewal end_date got extended which means reference date mismatches with the end_date + // in other words reference_date != end_date where end_date may be used as the start_action_date criteria + // for some schedule reminder so in order to send new reminder we INSERT new record with new reference_date value + // via UNION operation + if (strpos($selectColumns, 'reference_date') !== FALSE) { + $query .= " +UNION +{$selectClause} +{$fromClause} +{$joinClause} +LEFT JOIN {$reminderJoinClause} +{$whereClause} {$limitWhereClause} {$notINClause} AND + (reminder.reference_date IS NOT NULL AND reminder.reference_date != {$dateField}) +"; + + //Those reminders which are sent in past and there reference_date doesn't reflect the + //newly changed entity's action_start_date, we need to update those so that we never + //get new reminder redundantly as because of the above usage of UNION clause + $updateQuery = "UPDATE civicrm_action_log reminder + INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id AND + reminder.reference_date IS NOT NULL AND reminder.action_date_time IS NOT NULL + SET reminder.reference_date = {$dateField} + WHERE reminder.action_schedule_id = %1 AND reminder.reference_date IS NOT NULL AND reminder.reference_date != {$dateField} +"; + } + CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer'))); + + if (!empty($updateQuery)) { + CRM_Core_DAO::executeQuery($updateQuery, array(1 => array($actionSchedule->id, 'Integer'))); + } + $isSendToAdditionalContacts = (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) || !empty($addWhere))) ? TRUE : FALSE; if ($isSendToAdditionalContacts) { $contactTable = "civicrm_contact c"; diff --git a/CRM/Upgrade/Incremental/php/FourSix.php b/CRM/Upgrade/Incremental/php/FourSix.php index 21d17c652f..6676062ba3 100644 --- a/CRM/Upgrade/Incremental/php/FourSix.php +++ b/CRM/Upgrade/Incremental/php/FourSix.php @@ -112,4 +112,58 @@ class CRM_Upgrade_Incremental_php_FourSix { $queue->createItem($task, array('weight' => -1)); } + function upgrade_4_6_alpha3($rev) { + // task to process sql + $this->addTask(ts('Adding column reference date and updating with '), 'updateReferenceDate'); + } + + // CRM-15728, Add new column reference_date to civicrm_action_log in order to track + // actual action_start_date for membership entity for only those schedule reminders which are not repeatable + static function updateReferenceDate(CRM_Queue_TaskContext $ctx) { + $query = "ALTER TABLE `civicrm_action_log` + ADD COLUMN `reference_date` date COMMENT 'Stores the date from the entity which triggered this reminder action (e.g. membership.end_date for most membership renewal reminders)'"; + CRM_Core_DAO::executeQuery($query); + + $query = "SELECT schedule.* FROM civicrm_action_schedule schedule + LEFT JOIN civicrm_action_mapping mapper ON mapper.id = schedule.mapping_id AND + mapper.entity = 'civicrm_membership' AND is_repeat = 0"; + $dao = CRM_Core_DAO::executeQuery($query); + while($dao->fetch()) { + if (empty($dao->start_action_date)) { + continue; + } + + $referenceColumn = str_replace('membership_', "m.", $dao->start_action_date); + $where = array( + 'reminder.reference_date IS NOT NULL', + '( m.is_override IS NULL OR m.is_override = 0 )', + 'reminder.action_date_time >= DATE_SUB(reminder.action_date_time, INTERVAL 9 MONTH)' + ); + $value = implode(', ', explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($dao->entity_value, CRM_Core_DAO::VALUE_SEPARATOR))); + if (!empty($value)) { + $where[] = "m.membership_type_id IN ({$value})"; + } + else { + $where[] = "m.membership_type_id IS NULL"; + } + + // Update reference_date with action_start_date chosen, + // only to those which falls under date limits configured on schedule reminder + $startDateClause = array(); + $op = ($dao->start_action_condition == 'before' ? '<=' : '>='); + $operator = ($dao->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD'); + $date = $operator . "({$referenceColumn}, INTERVAL {$dao->start_action_offset} {$dao->start_action_unit})"; + $where[] = "NOW() >= {$date}"; + $where[] = "DATE_SUB(NOW(), INTERVAL 1 DAY ) <= {$date}"; + + $sql = "UPDATE civicrm_action_log reminder + LEFT JOIN civicrm_membership m ON reminder.entity_id = m.id + SET reminder.reference_date = {$referenceColumn} + + WHERE " . implode(" AND ", $where); + CRM_Core_DAO::executeQuery($sql); + } + + return TRUE; + } } diff --git a/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php b/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php index 1439e6f196..0313d4faaf 100644 --- a/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php +++ b/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php @@ -361,7 +361,7 @@ class CRM_Core_BAO_ActionScheduleTest extends CiviUnitTestCase { 'body_html' => '

body sched_membership_end_2month

', 'body_text' => 'body sched_membership_end_2month', 'end_action' => '', - 'end_date' => '', + 'end_date' => 'membership_end_date', 'end_frequency_interval' => '4', 'end_frequency_unit' => 'month', 'entity_status' => '', @@ -700,7 +700,7 @@ class CRM_Core_BAO_ActionScheduleTest extends CiviUnitTestCase { */ public function testMembershipEndDateNoMatch() { // creates membership with end_date = 20120615 - $membership = $this->createTestObject('CRM_Member_DAO_Membership', array_merge($this->fixtures['rolling_membership_past'], array('status_id' => 3))); + $membership = $this->createTestObject('CRM_Member_DAO_Membership', array_merge($this->fixtures['rolling_membership'], array('status_id' => 3))); $this->assertTrue(is_numeric($membership->id)); $result = $this->callAPISuccess('Email', 'create', array( 'contact_id' => $membership->contact_id, @@ -843,6 +843,63 @@ class CRM_Core_BAO_ActionScheduleTest extends CiviUnitTestCase { )); } + function testMembership_referenceDate() { + $membership = $this->createTestObject('CRM_Member_DAO_Membership', array_merge($this->fixtures['rolling_membership'], array('status_id' => 2))); + + $this->assertTrue(is_numeric($membership->id)); + $result = $this->callAPISuccess('Email', 'create', array( + 'contact_id' => $membership->contact_id, + 'email' => 'member@example.com', + )); + + $result = $this->callAPISuccess('contact', 'create', array_merge($this->fixtures['contact'], array('contact_id' => $membership->contact_id))); + $this->assertAPISuccess($result); + + $actionSchedule = $this->fixtures['sched_membership_join_2week']; + $actionSchedule['entity_value'] = $membership->membership_type_id; + $actionScheduleDao = CRM_Core_BAO_ActionSchedule::add($actionSchedule); + $this->assertTrue(is_numeric($actionScheduleDao->id)); + + // start_date=2012-03-15 ; schedule is 2 weeks after start_date + $this->assertCronRuns(array( + array( // After the 2-week mark, send an email + 'time' => '2012-03-29 01:00:00', + 'recipients' => array(array('member@example.com')), + ) + )); + + //check if reference date is set to membership's join date + //as per the action_start_date chosen for current schedule reminder + $this->assertEquals('2012-03-15', + CRM_Core_DAO::getFieldValue('CRM_Core_DAO_ActionLog', $membership->contact_id, 'reference_date', 'contact_id') + ); + + //change current membership join date that may signifies as memberhip renewal activity + $membership->join_date = '2012-03-29'; + $membership->save(); + + //change the email id of chosen membership contact to assert + //recipient of not the previously sent mail but the new one + $result = $this->callAPISuccess('Email', 'create', array( + 'is_primary' => 1, + 'contact_id' => $membership->contact_id, + 'email' => 'member2@example.com' + )); + $this->assertAPISuccess($result); + + $this->assertCronRuns(array( + array( // After the 2-week of the changed join date 2012-03-29, send an email + 'time' => '2012-04-12 01:00:00', + 'recipients' => array(array('member2@example.com') +), + ), + )); + + //To check whether the reference date is being changed to new join_date + $this->assertEquals('2012-03-29', + CRM_Core_DAO::getFieldValue('CRM_Core_DAO_ActionLog', $membership->contact_id, 'reference_date', 'contact_id', TRUE) + ); + } public function testContactCustomDateAnniv() { $group = array( diff --git a/xml/schema/Core/ActionLog.xml b/xml/schema/Core/ActionLog.xml index 17c7aa0da1..f0faf45bbb 100644 --- a/xml/schema/Core/ActionLog.xml +++ b/xml/schema/Core/ActionLog.xml @@ -87,4 +87,11 @@ Keeps track of the sequence number of this repetition. 3.4 + + reference_date + date + NULL + Stores the date from the entity which triggered this reminder action (e.g. membership.end_date for most membership renewal reminders) + 4.6 + -- 2.25.1