From 546a1eccdf2714172a1d1a7e2d146fc2f59ef4c3 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Sat, 1 Aug 2015 05:45:07 -0700 Subject: [PATCH] CRM-13244 - RecipientBuilder - Move entity-specific bits to ActionMappings --- CRM/Activity/ActionMapping.php | 83 +++++ CRM/Contact/ActionMapping.php | 106 ++++++ CRM/Core/ActionScheduleTmp.php | 38 +- CRM/Event/ActionMapping.php | 119 +++++++ CRM/Member/ActionMapping.php | 127 +++++++ Civi/ActionSchedule/Mapping.php | 105 ++++-- Civi/ActionSchedule/MappingInterface.php | 95 +++++ Civi/ActionSchedule/RecipientBuilder.php | 430 ++++++++--------------- 8 files changed, 802 insertions(+), 301 deletions(-) create mode 100644 CRM/Activity/ActionMapping.php create mode 100644 CRM/Contact/ActionMapping.php create mode 100644 CRM/Event/ActionMapping.php create mode 100644 CRM/Member/ActionMapping.php create mode 100644 Civi/ActionSchedule/MappingInterface.php diff --git a/CRM/Activity/ActionMapping.php b/CRM/Activity/ActionMapping.php new file mode 100644 index 0000000000..6026061f7d --- /dev/null +++ b/CRM/Activity/ActionMapping.php @@ -0,0 +1,83 @@ +entity_value); + $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); + + $query = \CRM_Utils_SQL_Select::from("{$this->entity} e"); + $query['casAddlCheckFrom'] = 'civicrm_activity e'; + $query['casContactIdField'] = 'r.contact_id'; + $query['casEntityIdField'] = 'e.id'; + $query['casContactTableAlias'] = NULL; + $query['casDateField'] = 'e.activity_date_time'; + + if (!is_null($schedule->limit_to)) { + $activityContacts = \CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); + if ($schedule->limit_to == 0 || !isset($activityContacts[$schedule->recipient])) { + $recipientTypeId = \CRM_Utils_Array::key('Activity Targets', $activityContacts); + } + else { + $recipientTypeId = $schedule->recipient; + } + $query->join('r', "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$recipientTypeId}"); + } + // build where clause + if (!empty($selectedValues)) { + $query->where("e.activity_type_id IN (#selectedValues)") + ->param('selectedValues', $selectedValues); + } + else { + $query->where("e.activity_type_id IS NULL"); + } + + if (!empty($selectedStatuses)) { + $query->where("e.status_id IN (#selectedStatuss)") + ->param('selectedStatuss', $selectedStatuses); + } + $query->where('e.is_current_revision = 1 AND e.is_deleted = 0'); + + return $query; + } + +} diff --git a/CRM/Contact/ActionMapping.php b/CRM/Contact/ActionMapping.php new file mode 100644 index 0000000000..b448ef2e44 --- /dev/null +++ b/CRM/Contact/ActionMapping.php @@ -0,0 +1,106 @@ +entity_value); + $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); + + // FIXME: This assumes that $values only has one field, but UI shows multiselect. + // Properly supporting multiselect would require total rewrite of this function. + if (count($selectedValues) != 1 || !isset($selectedValues[0])) { + throw new \CRM_Core_Exception("Error: Scheduled reminders may only have one contact field."); + } + elseif (in_array($selectedValues[0], $this->contactDateFields)) { + $dateDBField = $selectedValues[0]; + $query = \CRM_Utils_SQL_Select::from("{$this->entity} e"); + $query->param(array( + 'casAddlCheckFrom' => 'civicrm_contact e', + 'casContactIdField' => 'e.id', + 'casEntityIdField' => 'e.id', + 'casContactTableAlias' => 'e', + )); + $query->where('e.is_deleted = 0 AND e.is_deceased = 0'); + } + else { + //custom field + $customFieldParams = array('id' => substr($selectedValues[0], 7)); + $customGroup = $customField = array(); + \CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField); + $dateDBField = $customField['column_name']; + $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup); + \CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup); + $query = \CRM_Utils_SQL_Select::from("{$customGroup['table_name']} e"); + $query->param(array( + 'casAddlCheckFrom' => "{$customGroup['table_name']} e", + 'casContactIdField' => 'e.entity_id', + 'casEntityIdField' => 'e.id', + 'casContactTableAlias' => NULL, + )); + $query->where('1'); // possible to have no "where" in this case + } + + $query['casDateField'] = 'e.' . $dateDBField; + + if (in_array(2, $selectedStatuses)) { + $query['casAnniversaryMode'] = 1; + $query['casDateField'] = 'DATE_ADD(' . $query['casDateField'] . ', INTERVAL ROUND(DATEDIFF(DATE(!casNow), ' . $query['casDateField'] . ') / 365) YEAR)'; + } + + return $query; + } + +} diff --git a/CRM/Core/ActionScheduleTmp.php b/CRM/Core/ActionScheduleTmp.php index 8c21a3988f..124f565f1c 100644 --- a/CRM/Core/ActionScheduleTmp.php +++ b/CRM/Core/ActionScheduleTmp.php @@ -1,4 +1,30 @@ register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Activity_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::ACTIVITY_MAPPING_ID, 'entity' => 'civicrm_activity', 'entity_label' => ts('Activity'), @@ -44,7 +70,7 @@ class CRM_Core_ActionScheduleTmp implements EventSubscriberInterface { 'entity_date_start' => 'activity_date_time', 'entity_recipient' => 'activity_contacts', ))); - $registrations->register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Event_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID, 'entity' => 'civicrm_participant', 'entity_label' => ts('Event Type'), @@ -56,7 +82,7 @@ class CRM_Core_ActionScheduleTmp implements EventSubscriberInterface { 'entity_date_end' => 'event_end_date', 'entity_recipient' => 'event_contacts', ))); - $registrations->register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Event_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::EVENT_NAME_MAPPING_ID, 'entity' => 'civicrm_participant', 'entity_label' => ts('Event Name'), @@ -68,7 +94,7 @@ class CRM_Core_ActionScheduleTmp implements EventSubscriberInterface { 'entity_date_end' => 'event_end_date', 'entity_recipient' => 'event_contacts', ))); - $registrations->register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Member_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::MEMBERSHIP_TYPE_MAPPING_ID, 'entity' => 'civicrm_membership', 'entity_label' => ts('Membership'), @@ -79,7 +105,7 @@ class CRM_Core_ActionScheduleTmp implements EventSubscriberInterface { 'entity_date_start' => 'membership_join_date', 'entity_date_end' => 'membership_end_date', ))); - $registrations->register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Event_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::EVENT_TPL_MAPPING_ID, 'entity' => 'civicrm_participant', 'entity_label' => ts('Event Template'), @@ -91,7 +117,7 @@ class CRM_Core_ActionScheduleTmp implements EventSubscriberInterface { 'entity_date_end' => 'event_end_date', 'entity_recipient' => 'event_contacts', ))); - $registrations->register(\Civi\ActionSchedule\Mapping::create(array( + $registrations->register(CRM_Contact_ActionMapping::create(array( 'id' => CRM_Core_ActionScheduleTmp::CONTACT_MAPPING_ID, 'entity' => 'civicrm_contact', 'entity_label' => ts('Contact'), diff --git a/CRM/Event/ActionMapping.php b/CRM/Event/ActionMapping.php new file mode 100644 index 0000000000..ecb24a2e1e --- /dev/null +++ b/CRM/Event/ActionMapping.php @@ -0,0 +1,119 @@ +entity_value); + $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); + + $query = \CRM_Utils_SQL_Select::from("{$this->entity} e"); + $query['casAddlCheckFrom'] = 'civicrm_event r'; + $query['casContactIdField'] = 'e.contact_id'; + $query['casEntityIdField'] = 'e.id'; + $query['casContactTableAlias'] = NULL; + $query['casDateField'] = str_replace('event_', 'r.', $schedule->start_action_date); + + $query->join('r', 'INNER JOIN civicrm_event r ON e.event_id = r.id'); + if ($schedule->recipient_listing && $schedule->limit_to) { + switch (\CRM_Utils_Array::value($schedule->recipient, $this->getRecipientOptions())) { + case 'participant_role': + $query->where("e.role_id IN (#recipList)") + ->param('recipList', \CRM_Utils_Array::explodePadded($schedule->recipient_listing)); + break; + + default: + break; + } + } + + // build where clause + if (!empty($selectedValues)) { + $valueField = ($this->id == \CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? 'event_type_id' : 'id'; + $query->where("r.{$valueField} IN (@selectedValues)") + ->param('selectedValues', $selectedValues); + } + else { + $query->where(($this->id == \CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? "r.event_type_id IS NULL" : "r.id IS NULL"); + } + + $query->where('r.is_active = 1'); + $query->where('r.is_template = 0'); + + // participant status criteria not to be implemented for additional recipients + if (!empty($selectedStatuses)) { + switch ($phase) { + case RecipientBuilder::PHASE_RELATION_FIRST: + case RecipientBuilder::PHASE_RELATION_REPEAT: + $query->where("e.status_id IN (#selectedStatuses)") + ->param('selectedStatuses', $selectedStatuses); + break; + + } + } + return $query; + } + + /** + * FIXME: Seems to duplicate getRecipientTypes? + * @return array|null + */ + public function getRecipientOptions() { + $recipientOptions = NULL; + if (!\CRM_Utils_System::isNull($this->entity_recipient)) { + if ($this->entity_recipient == 'event_contacts') { + $recipientOptions = \CRM_Core_OptionGroup::values($this->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name'); + } + else { + $recipientOptions = \CRM_Core_OptionGroup::values($this->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name'); + } + } + return $recipientOptions; + } + +} diff --git a/CRM/Member/ActionMapping.php b/CRM/Member/ActionMapping.php new file mode 100644 index 0000000000..03654cc4cc --- /dev/null +++ b/CRM/Member/ActionMapping.php @@ -0,0 +1,127 @@ +entity_value); + $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status); + + $query = \CRM_Utils_SQL_Select::from("{$this->entity} e"); + $query['casAddlCheckFrom'] = 'civicrm_membership e'; + $query['casContactIdField'] = 'e.contact_id'; + $query['casEntityIdField'] = 'e.id'; + $query['casContactTableAlias'] = NULL; + $query['casDateField'] = str_replace('membership_', 'e.', $schedule->start_action_date); + + // FIXME: Numbers should be constants. + if (in_array(2, $selectedStatuses)) { + //auto-renew memberships + $query->where("e.contribution_recur_id IS NOT NULL"); + } + elseif (in_array(1, $selectedStatuses)) { + $query->where("e.contribution_recur_id IS NULL"); + } + + if (!empty($selectedValues)) { + $query->where("e.membership_type_id IN (@memberTypeValues)") + ->param('memberTypeValues', $selectedValues); + } + else { + $query->where("e.membership_type_id IS NULL"); + } + + $query->where("( e.is_override IS NULL OR e.is_override = 0 )"); + $query->merge($this->prepareMembershipPermissionsFilter()); + $query->where("e.status_id IN (#memberStatus)") + ->param('memberStatus', \CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id')); + + // Why is this only for civicrm_membership? + if ($schedule->start_action_date && $schedule->is_repeat == FALSE) { + $query['casUseReferenceDate'] = TRUE; + } + + return $query; + } + + /** + * @return array + */ + protected function prepareMembershipPermissionsFilter() { + $query = ' +SELECT cm.id AS owner_id, cm.contact_id AS owner_contact, m.id AS slave_id, m.contact_id AS slave_contact, cmt.relationship_type_id AS relation_type, rel.contact_id_a, rel.contact_id_b, rel.is_permission_a_b, rel.is_permission_b_a +FROM civicrm_membership m +LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id +LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id +LEFT JOIN civicrm_relationship rel ON ( ( rel.contact_id_a = m.contact_id AND rel.contact_id_b = cm.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) + OR ( rel.contact_id_a = cm.contact_id AND rel.contact_id_b = m.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) ) +WHERE m.owner_membership_id IS NOT NULL AND + ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0) + +'; + $excludeIds = array(); + $dao = \CRM_Core_DAO::executeQuery($query, array()); + while ($dao->fetch()) { + if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_a_b == 0) { + $excludeIds[] = $dao->slave_contact; + } + elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_b_a == 0) { + $excludeIds[] = $dao->slave_contact; + } + } + + if (!empty($excludeIds)) { + return \CRM_Utils_SQL_Select::fragment() + ->where("!casContactIdField NOT IN (#excludeMemberIds)") + ->param(array( + '#excludeMemberIds' => $excludeIds, + )); + } + return NULL; + } + +} diff --git a/Civi/ActionSchedule/Mapping.php b/Civi/ActionSchedule/Mapping.php index 65c1358881..39db1efe48 100644 --- a/Civi/ActionSchedule/Mapping.php +++ b/Civi/ActionSchedule/Mapping.php @@ -1,7 +1,65 @@ string $fieldLabel). */ @@ -145,6 +205,14 @@ class Mapping { return $dateFieldLabels; } + /** + * Unsure. Not sure how it differs from getRecipientTypes... but it does... + * + * @param string $recipientType + * @return array + * Array(mixed $name => string $label). + * Ex: array(1 => 'Attendee', 2 => 'Volunteer'). + */ public function getRecipientListing($recipientType) { if (!$recipientType) { return array(); @@ -163,6 +231,8 @@ class Mapping { } /** + * Unsure. Not sure how it differs from getRecipientListing... but it does... + * * @param bool|NULL $noThanksJustKidding * This is ridiculous and should not exist. * If true, don't do our main job. @@ -192,22 +262,6 @@ class Mapping { return $entityRecipientLabels; } - /** - * FIXME: Seems to duplicate getRecipientTypes? - * @return array|null - */ - public function getRecipientOptions() { - $recipientOptions = NULL; - if (!\CRM_Utils_System::isNull($this->entity_recipient)) { - if ($this->entity_recipient == 'event_contacts') { - $recipientOptions = \CRM_Core_OptionGroup::values($this->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name'); - } - else { - $recipientOptions = \CRM_Core_OptionGroup::values($this->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name'); - } - } - return $recipientOptions; - } protected static function getValueLabelMap($name) { static $valueLabelMap = NULL; @@ -241,4 +295,15 @@ class Mapping { return $valueLabelMap[$name]; } + /** + * Generate a query to locate contacts who match the given + * schedule. + * + * @param \CRM_Core_DAO_ActionSchedule $schedule + * @param string $phase + * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. + * @return \CRM_Utils_SQL_Select + */ + public abstract function createQuery($schedule, $phase); + } diff --git a/Civi/ActionSchedule/MappingInterface.php b/Civi/ActionSchedule/MappingInterface.php new file mode 100644 index 0000000000..b1acb7b30d --- /dev/null +++ b/Civi/ActionSchedule/MappingInterface.php @@ -0,0 +1,95 @@ + string $label). + * Ex: array(123 => 'Phone Call', 456 => 'Meeting'). + */ + public function getValueLabels(); + + /** + * Get a list of status options. + * + * @param string|int $value + * The list of status options may be contingent upon the selected filter value. + * This is the selected filter value. + * @return array + * Array(string $value => string $label). + * Ex: Array(123 => 'Completed', 456 => 'Scheduled'). + */ + public function getStatusLabels($value); + + /** + * Get a list of available date fields. + * + * @return array + * Array(string $fieldName => string $fieldLabel). + */ + public function getDateFields(); + + /** + * FIXME: Unsure. Not sure how it differs from getRecipientTypes... but it does... + * + * @param string $recipientType + * @return array + * Array(mixed $name => string $label). + * Ex: array(1 => 'Attendee', 2 => 'Volunteer'). + */ + public function getRecipientListing($recipientType); + + /** + * FIXME: Unsure. Not sure how it differs from getRecipientListing... but it does... + * + * @param bool|NULL $noThanksJustKidding + * This is ridiculous and should not exist. + * If true, don't do our main job. + * @return array + * array(mixed $value => string $label). + * Ex: array('assignee' => 'Activity Assignee'). + */ + public function getRecipientTypes($noThanksJustKidding = FALSE); + + /** + * Generate a query to locate contacts who match the given + * schedule. + * + * @param \CRM_Core_DAO_ActionSchedule $schedule + * @param string $phase + * See, e.g., RecipientBuilder::PHASE_RELATION_FIRST. + * @return \CRM_Utils_SQL_Select + * @see RecipientBuilder + */ + public function createQuery($schedule, $phase); + +} diff --git a/Civi/ActionSchedule/RecipientBuilder.php b/Civi/ActionSchedule/RecipientBuilder.php index a3896724f5..9bedcad836 100644 --- a/Civi/ActionSchedule/RecipientBuilder.php +++ b/Civi/ActionSchedule/RecipientBuilder.php @@ -1,14 +1,129 @@ join('event', 'INNER JOIN civicrm_event event ON e.event_id = event.id') + * ->where('e.is_pay_later = 1') + * ->where('event.event_type_id IN (#myEventTypes)') + * ->param('myEventTypes', array(2, 5)) + * ->param('casDateField', 'e.register_date') + * ...etc... + * @endcode + * + * In the RELATION_FIRST phase, RecipientBuilder adds a LEFT-JOIN+WHERE to find + * participants who have *not* yet received any reminder, and filters those + * participants based on whether X days have passed since "e.register_date". + * + * Notice that the query may define several SQL elements directly (eg + * via `from()`, `where()`, `join()`, `groupBy()`). Additionally, it + * must define some parameters (eg `casDateField`). These parameters will be + * read by RecipientBuilder and used in other parts of the query. + * + * At time of writing, these parameters are required: + * - casAddlCheckFrom: string, SQL FROM expression + * - casContactIdField: string, SQL column expression + * - casDateField: string, SQL column expression + * - casEntityIdField: string, SQL column expression + * + * Some parameters are optional: + * - casContactTableAlias: string, SQL table alias + * - casAnniversaryMode: bool + * - casUseReferenceDate: bool + * + * Additionally, some parameters are automatically predefined: + * - casNow + * - casMappingEntity: string, SQL table name + * - casMappingId: int + * - casActionScheduleId: int + * + * Note: Any parameters defined by the core Civi\ActionSchedule subsystem + * use the prefix `cas`. If you define new parameters (like `myEventTypes` + * above), then use a different name (to avoid conflicts). + */ class RecipientBuilder { private $now; - private $contactDateFields = array( - 'birth_date', - 'created_date', - 'modified_date', - ); + + /** + * Generate action_log's for new, first-time alerts to related contacts. + * + * @see buildRelFirstPass + */ + const PHASE_RELATION_FIRST = 'rel-first'; + + /** + * Generate action_log's for new, first-time alerts to additional contacts. + * + * @see buildAddlFirstPass + */ + const PHASE_ADDITION_FIRST = 'addl-first'; + + /** + * Generate action_log's for repeated, follow-up alerts to related contacts. + * + * @see buildRelRepeatPass + */ + const PHASE_RELATION_REPEAT = 'rel-repeat'; + + /** + * Generate action_log's for repeated, follow-up alerts to additional contacts. + * + * @see buildAddlRepeatPass + */ + const PHASE_ADDITION_REPEAT = 'addl-repeat'; /** * @var \CRM_Core_DAO_ActionSchedule @@ -16,10 +131,15 @@ class RecipientBuilder { private $actionSchedule; /** - * @var Mapping + * @var MappingInterface */ private $mapping; + /** + * @param $now + * @param \CRM_Core_DAO_ActionSchedule $actionSchedule + * @param MappingInterface $mapping + */ public function __construct($now, $actionSchedule, $mapping) { $this->now = $now; $this->actionSchedule = $actionSchedule; @@ -32,35 +152,33 @@ class RecipientBuilder { * @throws \CRM_Core_Exception */ public function build() { - // Generate action_log's for new, first-time alerts to related contacts. $this->buildRelFirstPass(); - // Generate action_log's for new, first-time alerts to additional contacts. if ($this->prepareAddlFilter('c.id')) { $this->buildAddlFirstPass(); } - // Generate action_log's for repeated, follow-up alerts to related contacts. if ($this->actionSchedule->is_repeat) { $this->buildRelRepeatPass(); } - // Generate action_log's for repeated, follow-up alerts to additional contacts. if ($this->actionSchedule->is_repeat && $this->prepareAddlFilter('c.id')) { $this->buildAddlRepeatPass(); } } /** + * Generate action_log's for new, first-time alerts to related contacts. + * * @throws \Exception */ protected function buildRelFirstPass() { - $query = $this->prepareQuery('rel-first'); + $query = $this->prepareQuery(self::PHASE_RELATION_FIRST); $startDateClauses = $this->prepareStartDateClauses($query['casDateField']); $firstQuery = $query->copy() - ->merge($this->selectIntoActionLog('rel-first', $query)) + ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) ->merge($this->joinReminder('LEFT JOIN', 'rel', $query)) ->where("reminder.id IS NULL") ->where($startDateClauses) @@ -74,7 +192,7 @@ class RecipientBuilder { // value via UNION operation if (!empty($query['casUseReferenceDate'])) { $referenceQuery = $query->copy() - ->merge($this->selectIntoActionLog('rel-first', $query)) + ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) ->merge($this->joinReminder('LEFT JOIN', 'rel', $query)) ->where("reminder.id IS NOT NULL") ->where($startDateClauses) @@ -88,13 +206,15 @@ class RecipientBuilder { } /** + * Generate action_log's for new, first-time alerts to additional contacts. + * * @throws \Exception */ protected function buildAddlFirstPass() { - $query = $this->prepareQuery('addl-first'); + $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST); $insertAdditionalSql = \CRM_Utils_SQL_Select::from("civicrm_contact c") - ->merge($this->selectIntoActionLog('addl-first', $query)) + ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_FIRST, $query)) ->merge($this->joinReminder('LEFT JOIN', 'addl', $query)) ->where("c.is_deleted = 0 AND c.is_deceased = 0") ->merge($this->prepareAddlFilter('c.id')) @@ -112,11 +232,13 @@ class RecipientBuilder { } /** + * Generate action_log's for repeated, follow-up alerts to related contacts. + * * @throws \CRM_Core_Exception * @throws \Exception */ protected function buildRelRepeatPass() { - $query = $this->prepareQuery('rel-repeat'); + $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT); $startDateClauses = $this->prepareStartDateClauses($query['casDateField']); // CRM-15376 - do not send our reminders if original criteria no longer applies @@ -125,14 +247,13 @@ class RecipientBuilder { // @todo - this only handles events that get moved later. Potentially they might get moved earlier $repeatInsert = $query ->merge($this->joinReminder('INNER JOIN', 'rel', $query)) - ->merge($this->selectActionLogFields('rel-repeat', $query)) + ->merge($this->selectActionLogFields(self::PHASE_RELATION_REPEAT, $query)) ->select("MAX(reminder.action_date_time) as latest_log_time") ->merge($this->prepareRepetitionEndFilter($query['casDateField'])) ->where($this->actionSchedule->start_action_date ? $startDateClauses[0] : array()) ->groupBy("reminder.contact_id, reminder.entity_id, reminder.entity_table") - ->having("TIMEDIFF(!now, latest_log_time) >= !hrs") + ->having("TIMEDIFF(!casNow, latest_log_time) >= !hrs") ->param(array( - '!now' => $this->now, // why not @now ? '!hrs' => $this->parseSqlHrs(), )) ->strict() @@ -151,12 +272,15 @@ class RecipientBuilder { ); } } + /** + * Generate action_log's for repeated, follow-up alerts to additional contacts. + * * @throws \CRM_Core_Exception * @throws \Exception */ protected function buildAddlRepeatPass() { - $query = $this->prepareQuery('addl-repeat'); + $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT); $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom']) ->select('*') @@ -169,15 +293,14 @@ class RecipientBuilder { $daoCheck = \CRM_Core_DAO::executeQuery($addlCheck); if ($daoCheck->fetch()) { $repeatInsertAddl = \CRM_Utils_SQL_Select::from('civicrm_contact c') - ->merge($this->selectActionLogFields('addl-repeat', $query)) + ->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query)) ->merge($this->joinReminder('INNER JOIN', 'addl', $query)) ->select("MAX(reminder.action_date_time) as latest_log_time") ->merge($this->prepareAddlFilter('c.id')) ->where("c.is_deleted = 0 AND c.is_deceased = 0") ->groupBy("reminder.contact_id") - ->having("TIMEDIFF(!now, latest_log_time) >= !hrs") + ->having("TIMEDIFF(!casNow, latest_log_time) >= !hrs") ->param(array( - '!now' => $this->now, // FIXME: use @now ? '!hrs' => $this->parseSqlHrs(), )) ->strict() @@ -205,27 +328,12 @@ class RecipientBuilder { */ protected function prepareQuery($phase) { /** @var \CRM_Utils_SQL_Select $query */ - - if ($this->mapping->entity == 'civicrm_activity') { - $query = $this->prepareActivityQuery($phase); - } - elseif ($this->mapping->entity == 'civicrm_participant') { - $query = $this->prepareParticipantQuery($phase); - } - elseif ($this->mapping->entity == 'civicrm_membership') { - $query = $this->prepareMembershipQuery($phase); - } - elseif ($this->mapping->entity == 'civicrm_contact') { - $query = $this->prepareContactQuery($phase); - } - else { - throw new \CRM_Core_Exception("Unrecognized entity: {$this->mapping->entity}"); - } - + $query = $this->mapping->createQuery($this->actionSchedule, $phase); $query->param(array( 'casActionScheduleId' => $this->actionSchedule->id, 'casMappingId' => $this->mapping->id, 'casMappingEntity' => $this->mapping->entity, + 'casNow' => $this->now, )); if ($this->actionSchedule->limit_to /*1*/) { @@ -383,49 +491,12 @@ WHERE $group.id = {$groupId} . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})"; return \CRM_Utils_SQL_Select::fragment() - ->where("@now <= !repetitionEndDate") + ->where("@casNow <= !repetitionEndDate") ->param(array( - '@now' => $this->now, '!repetitionEndDate' => $repeatEventDateExpr, )); } - /** - * @return array - */ - protected function prepareMembershipPermissionsFilter() { - $query = ' -SELECT cm.id AS owner_id, cm.contact_id AS owner_contact, m.id AS slave_id, m.contact_id AS slave_contact, cmt.relationship_type_id AS relation_type, rel.contact_id_a, rel.contact_id_b, rel.is_permission_a_b, rel.is_permission_b_a -FROM civicrm_membership m -LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id -LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id -LEFT JOIN civicrm_relationship rel ON ( ( rel.contact_id_a = m.contact_id AND rel.contact_id_b = cm.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) - OR ( rel.contact_id_a = cm.contact_id AND rel.contact_id_b = m.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) ) -WHERE m.owner_membership_id IS NOT NULL AND - ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0) - -'; - $excludeIds = array(); - $dao = \CRM_Core_DAO::executeQuery($query, array()); - while ($dao->fetch()) { - if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_a_b == 0) { - $excludeIds[] = $dao->slave_contact; - } - elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_b_a == 0) { - $excludeIds[] = $dao->slave_contact; - } - } - - if (!empty($excludeIds)) { - return \CRM_Utils_SQL_Select::fragment() - ->where("!casContactIdField NOT IN (#excludeMemberIds)") - ->param(array( - '#excludeMemberIds' => $excludeIds, - )); - } - return NULL; - } - /** * @param string $contactIdField * @return \CRM_Utils_SQL_Select|null @@ -438,197 +509,6 @@ WHERE m.owner_membership_id IS NOT NULL AND return $contactAddlFilter; } - /** - * @return \CRM_Utils_SQL_Select - * @throws \CRM_Core_Exception - */ - protected function prepareContactQuery($phase) { - $selectedValues = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_value); - $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_status); - - // FIXME: This assumes that $values only has one field, but UI shows multiselect. - if (count($selectedValues) != 1 || !isset($selectedValues[0])) { - throw new \CRM_Core_Exception("Error: Scheduled reminders may only have one contact field."); - } - elseif (in_array($selectedValues[0], $this->contactDateFields)) { - $dateDBField = $selectedValues[0]; - $query = \CRM_Utils_SQL_Select::from("{$this->mapping->entity} e"); - $query->param(array( - 'casAddlCheckFrom' => 'civicrm_contact e', - 'casContactIdField' => 'e.id', - 'casEntityIdField' => 'e.id', - 'casContactTableAlias' => 'e', - )); - $query->where('e.is_deleted = 0 AND e.is_deceased = 0'); - } - else { - //custom field - $customFieldParams = array('id' => substr($selectedValues[0], 7)); - $customGroup = $customField = array(); - \CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField); - $dateDBField = $customField['column_name']; - $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup); - \CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup); - $query = \CRM_Utils_SQL_Select::from("{$customGroup['table_name']} e"); - $query->param(array( - 'casAddlCheckFrom' => "{$customGroup['table_name']} e", - 'casContactIdField' => 'e.entity_id', - 'casEntityIdField' => 'e.id', - 'casContactTableAlias' => NULL, - )); - $query->where('1'); // possible to have no "where" in this case - } - - $query['casDateField'] = 'e.' . $dateDBField; - - if (in_array(2, $selectedStatuses)) { - $query['casAnniversaryMode'] = 1; - $query['casDateField'] = 'DATE_ADD(' . $query['casDateField'] . ', INTERVAL ROUND(DATEDIFF(DATE(' . $this->now . '), ' . $query['casDateField'] . ') / 365) YEAR)'; - } - - return $query; - } - - /** - * @return \CRM_Utils_SQL_Select - */ - protected function prepareMembershipQuery($phase) { - $selectedValues = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_value); - $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_status); - - $query = \CRM_Utils_SQL_Select::from("{$this->mapping->entity} e"); - $query['casAddlCheckFrom'] = 'civicrm_membership e'; - $query['casContactIdField'] = 'e.contact_id'; - $query['casEntityIdField'] = 'e.id'; - $query['casContactTableAlias'] = NULL; - $query['casDateField'] = str_replace('membership_', 'e.', $this->actionSchedule->start_action_date); - - if (in_array(2, $selectedStatuses)) { - //auto-renew memberships - $query->where("e.contribution_recur_id IS NOT NULL"); - } - elseif (in_array(1, $selectedStatuses)) { - $query->where("e.contribution_recur_id IS NULL"); - } - - if (!empty($selectedValues)) { - $query->where("e.membership_type_id IN (@memberTypeValues)") - ->param('memberTypeValues', $selectedValues); - } - else { - $query->where("e.membership_type_id IS NULL"); - } - - $query->where("( e.is_override IS NULL OR e.is_override = 0 )"); - $query->merge($this->prepareMembershipPermissionsFilter()); - $query->where("e.status_id IN (#memberStatus)") - ->param('memberStatus', \CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id')); - - // Why is this only for civicrm_membership? - if ($this->actionSchedule->start_action_date && $this->actionSchedule->is_repeat == FALSE) { - $query['casUseReferenceDate'] = TRUE; - } - - return $query; - } - - /** - * @return \CRM_Utils_SQL_Select - */ - protected function prepareParticipantQuery($phase) { - $selectedValues = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_value); - $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_status); - - $query = \CRM_Utils_SQL_Select::from("{$this->mapping->entity} e"); - $query['casAddlCheckFrom'] = 'civicrm_event r'; - $query['casContactIdField'] = 'e.contact_id'; - $query['casEntityIdField'] = 'e.id'; - $query['casContactTableAlias'] = NULL; - $query['casDateField'] = str_replace('event_', 'r.', $this->actionSchedule->start_action_date); - - $query->join('r', 'INNER JOIN civicrm_event r ON e.event_id = r.id'); - if ($this->actionSchedule->recipient_listing && $this->actionSchedule->limit_to) { - switch (\CRM_Utils_Array::value($this->actionSchedule->recipient, $this->mapping->getRecipientOptions())) { - case 'participant_role': - $query->where("e.role_id IN (#recipList)") - ->param('recipList', \CRM_Utils_Array::explodePadded($this->actionSchedule->recipient_listing)); - break; - - default: - break; - } - } - - // build where clause - if (!empty($selectedValues)) { - $valueField = ($this->mapping->id == \CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? 'event_type_id' : 'id'; - $query->where("r.{$valueField} IN (@selectedValues)") - ->param('selectedValues', $selectedValues); - } - else { - $query->where(($this->mapping->id == \CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? "r.event_type_id IS NULL" : "r.id IS NULL"); - } - - $query->where('r.is_active = 1'); - $query->where('r.is_template = 0'); - - // participant status criteria not to be implemented for additional recipients - if (!empty($selectedStatuses)) { - switch ($phase) { - case 'rel-first': - case 'rel-repeat': - $query->where("e.status_id IN (#selectedStatuses)") - ->param('selectedStatuses', $selectedStatuses); - break; - - } - - } - return $query; - } - - /** - * @return \CRM_Utils_SQL_Select - */ - protected function prepareActivityQuery($phase) { - $selectedValues = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_value); - $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($this->actionSchedule->entity_status); - - $query = \CRM_Utils_SQL_Select::from("{$this->mapping->entity} e"); - $query['casAddlCheckFrom'] = 'civicrm_activity e'; - $query['casContactIdField'] = 'r.contact_id'; - $query['casEntityIdField'] = 'e.id'; - $query['casContactTableAlias'] = NULL; - $query['casDateField'] = 'e.activity_date_time'; - - if (!is_null($this->actionSchedule->limit_to)) { - $activityContacts = \CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); - if ($this->actionSchedule->limit_to == 0 || !isset($activityContacts[$this->actionSchedule->recipient])) { - $recipientTypeId = \CRM_Utils_Array::key('Activity Targets', $activityContacts); - } - else { - $recipientTypeId = $this->actionSchedule->recipient; - } - $query->join('r', "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$recipientTypeId}"); - } - // build where clause - if (!empty($selectedValues)) { - $query->where("e.activity_type_id IN (#selectedValues)") - ->param('selectedValues', $selectedValues); - } - else { - $query->where("e.activity_type_id IS NULL"); - } - - if (!empty($selectedStatuses)) { - $query->where("e.status_id IN (#selectedStatuss)") - ->param('selectedStatuss', $selectedStatuses); - } - $query->where('e.is_current_revision = 1 AND e.is_deleted = 0'); - - return $query; - } - /** * Generate a query fragment like for populating * action logs, e.g. @@ -642,8 +522,8 @@ WHERE m.owner_membership_id IS NOT NULL AND */ protected function selectActionLogFields($phase, $query) { switch ($phase) { - case 'rel-first': - case 'rel-repeat': + case self::PHASE_RELATION_FIRST: + case self::PHASE_RELATION_REPEAT: $fragment = \CRM_Utils_SQL_Select::fragment(); // CRM-15376: We are not tracking the reference date for 'repeated' schedule reminders. if (!empty($query['casUseReferenceDate'])) { @@ -659,8 +539,8 @@ WHERE m.owner_membership_id IS NOT NULL AND ); break; - case 'addl-first': - case 'addl-repeat': + case self::PHASE_ADDITION_FIRST: + case self::PHASE_ADDITION_REPEAT: $fragment = \CRM_Utils_SQL_Select::fragment(); $fragment->select( array( @@ -696,7 +576,7 @@ WHERE m.owner_membership_id IS NOT NULL AND "entity_table", "action_schedule_id", ); - if ($phase === 'rel-first' || $phase === 'rel-repeat') { + if ($phase === self::PHASE_RELATION_FIRST || $phase === self::PHASE_RELATION_REPEAT) { if (!empty($query['casUseReferenceDate'])) { array_unshift($actionLogColumns, 'reference_date'); } @@ -743,7 +623,7 @@ reminder.action_schedule_id = {$this->actionSchedule->id}"; // Why do we only include anniversary clause for 'rel' queries? if ($for === 'rel' && !empty($query['casAnniversaryMode'])) { // only consider reminders less than 11 months ago - $joinClause .= " AND reminder.action_date_time > DATE_SUB($this->now, INTERVAL 11 MONTH)"; + $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)"; } return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause"); -- 2.25.1