From f9ec2da662f04427ca0b386f40fa6bbaf14e2a03 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Tue, 4 Aug 2015 00:51:04 -0700 Subject: [PATCH] CRM-13244 - Split prepareMailingQuery() into entity-specific pieces. --- CRM/Activity/Tokens.php | 24 ++++++ CRM/Core/BAO/ActionSchedule.php | 72 +++++------------- CRM/Event/Tokens.php | 18 +++++ CRM/Member/Tokens.php | 12 +++ .../Event/MailingQueryEvent.php | 76 +++++++++++++++++++ Civi/ActionSchedule/Events.php | 4 + Civi/Token/AbstractTokenSubscriber.php | 23 +++++- 7 files changed, 175 insertions(+), 54 deletions(-) create mode 100644 Civi/ActionSchedule/Event/MailingQueryEvent.php diff --git a/CRM/Activity/Tokens.php b/CRM/Activity/Tokens.php index 3a5f99756b..159b1a1a8c 100644 --- a/CRM/Activity/Tokens.php +++ b/CRM/Activity/Tokens.php @@ -57,6 +57,30 @@ class CRM_Activity_Tokens extends \Civi\Token\AbstractTokenSubscriber { && $processor->context['actionMapping']->getEntity() === 'civicrm_activity'; } + public function alterActionScheduleQuery(\Civi\ActionSchedule\Event\MailingQueryEvent $e) { + if ($e->mapping->getEntity() !== 'civicrm_activity') { + return; + } + + // The join expression for activities needs some extra nuance to handle + // multiple revisions of the activity. Q: Could we simplify & move the + // extra AND clauses into `where(...)`? + $e->query->param('casEntityJoinExpr', 'e.id = reminder.entity_id AND e.is_current_revision = 1 AND e.is_deleted = 0'); + + $e->query->select('e.*'); // FIXME: seems too broad. + $e->query->select('ov.label as activity_type, e.id as activity_id'); + + $e->query->join("og", "!casMailingJoinType civicrm_option_group og ON og.name = 'activity_type'"); + $e->query->join("ov", "!casMailingJoinType civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id"); + + // if CiviCase component is enabled, join for caseId. + $compInfo = CRM_Core_Component::getEnabledComponents(); + if (array_key_exists('CiviCase', $compInfo)) { + $e->query->select("civicrm_case_activity.case_id as case_id"); + $e->query->join('civicrm_case_activity', "LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`"); + } + } + /** * Evaluate the content of a single token. * diff --git a/CRM/Core/BAO/ActionSchedule.php b/CRM/Core/BAO/ActionSchedule.php index 95391467b5..736dc3bcc5 100755 --- a/CRM/Core/BAO/ActionSchedule.php +++ b/CRM/Core/BAO/ActionSchedule.php @@ -535,66 +535,34 @@ FROM civicrm_action_schedule cas } /** - * @param $mapping - * @param $actionSchedule + * @param \Civi\ActionSchedule\MappingInterface $mapping + * @param \CRM_Core_DAO_ActionSchedule $actionSchedule * @return string */ protected static function prepareMailingQuery($mapping, $actionSchedule) { - $select = CRM_Utils_SQL_Select::from('civicrm_action_log reminder', array('mode' => 'out')) - ->select("reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.*") - ->where("reminder.action_schedule_id = #casScheduleId") - ->param('casScheduleId', $actionSchedule->id) - ->where("reminder.action_date_time IS NULL"); + $select = CRM_Utils_SQL_Select::from('civicrm_action_log reminder') + ->select("reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id AS entityID") + ->join('e', "!casMailingJoinType !casMappingEntity e ON !casEntityJoinExpr") + ->select("e.id as entityID, e.*") + ->where("reminder.action_schedule_id = #casActionScheduleId") + ->where("reminder.action_date_time IS NULL") + ->param(array( + 'casActionScheduleId' => $actionSchedule->id, + 'casMailingJoinType' => ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN', + 'casMappingId' => $mapping->getId(), + 'casMappingEntity' => $mapping->getEntity(), + 'casEntityJoinExpr' => 'e.id = reminder.entity_id', + )); if ($actionSchedule->limit_to == 0) { - $entityJoinClause = "LEFT JOIN {$mapping->getEntity()} e ON e.id = reminder.entity_id"; $select->where("e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact'"); } - else { - $entityJoinClause = "INNER JOIN {$mapping->getEntity()} e ON e.id = reminder.entity_id"; - } - if ($mapping->getEntity() == 'civicrm_activity') { - $entityJoinClause .= ' AND e.is_current_revision = 1 AND e.is_deleted = 0 '; - } - $select->join('a', $entityJoinClause); - - if ($mapping->getEntity() == 'civicrm_activity') { - $compInfo = CRM_Core_Component::getEnabledComponents(); - $select->select('ov.label as activity_type, e.id as activity_id'); - - $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN'; - $select->join("og", "$JOIN_TYPE civicrm_option_group og ON og.name = 'activity_type'"); - $select->join("ov", "$JOIN_TYPE civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id"); - - // if CiviCase component is enabled, join for caseId. - if (array_key_exists('CiviCase', $compInfo)) { - $select->select("civicrm_case_activity.case_id as case_id"); - $select->join('civicrm_case_activity', "LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`"); - } - } - - if ($mapping->getEntity() == 'civicrm_participant') { - $select->select('ov.label as event_type, ev.title, ev.id as event_id, ev.start_date, ev.end_date, ev.summary, ev.description, address.street_address, address.city, address.state_province_id, address.postal_code, email.email as contact_email, phone.phone as contact_phone'); - - $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN'; - $select->join('participant_stuff', " -$JOIN_TYPE civicrm_event ev ON e.event_id = ev.id -$JOIN_TYPE civicrm_option_group og ON og.name = 'event_type' -$JOIN_TYPE civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id -LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id -LEFT JOIN civicrm_address address ON address.id = lb.address_id -LEFT JOIN civicrm_email email ON email.id = lb.email_id -LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id -"); - } - if ($mapping->getEntity() == 'civicrm_membership') { - $select->select('mt.minimum_fee as fee, e.id as id , e.join_date, e.start_date, e.end_date, ms.name as status, mt.name as type'); - - $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN'; - $select->join('mt', "$JOIN_TYPE civicrm_membership_type mt ON e.membership_type_id = mt.id"); - $select->join('ms', "$JOIN_TYPE civicrm_membership_status ms ON e.status_id = ms.id"); - } + \Civi\Core\Container::singleton()->get('dispatcher') + ->dispatch( + \Civi\ActionSchedule\Events::MAILING_QUERY, + new \Civi\ActionSchedule\Event\MailingQueryEvent($actionSchedule, $mapping, $select) + ); return $select->toSQL(); } diff --git a/CRM/Event/Tokens.php b/CRM/Event/Tokens.php index 0d3e2953bb..ba51432e7d 100644 --- a/CRM/Event/Tokens.php +++ b/CRM/Event/Tokens.php @@ -66,6 +66,24 @@ class CRM_Event_Tokens extends \Civi\Token\AbstractTokenSubscriber { && $processor->context['actionMapping']->getEntity() === 'civicrm_participant'; } + public function alterActionScheduleQuery(\Civi\ActionSchedule\Event\MailingQueryEvent $e) { + if ($e->mapping->getEntity() !== 'civicrm_participant') { + return; + } + + $e->query->select('e.*'); // FIXME: seems too broad. + $e->query->select('ov.label as event_type, ev.title, ev.id as event_id, ev.start_date, ev.end_date, ev.summary, ev.description, address.street_address, address.city, address.state_province_id, address.postal_code, email.email as contact_email, phone.phone as contact_phone'); + $e->query->join('participant_stuff', " +!casMailingJoinType civicrm_event ev ON e.event_id = ev.id +!casMailingJoinType civicrm_option_group og ON og.name = 'event_type' +!casMailingJoinType civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id +LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id +LEFT JOIN civicrm_address address ON address.id = lb.address_id +LEFT JOIN civicrm_email email ON email.id = lb.email_id +LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id +"); + } + /** * Evaluate the content of a single token. * diff --git a/CRM/Member/Tokens.php b/CRM/Member/Tokens.php index f8eb45add1..c14da03acf 100644 --- a/CRM/Member/Tokens.php +++ b/CRM/Member/Tokens.php @@ -59,6 +59,18 @@ class CRM_Member_Tokens extends \Civi\Token\AbstractTokenSubscriber { && $processor->context['actionMapping']->getEntity() === 'civicrm_membership'; } + public function alterActionScheduleQuery(\Civi\ActionSchedule\Event\MailingQueryEvent $e) { + if ($e->mapping->getEntity() !== 'civicrm_membership') { + return; + } + + $e->query + ->select('e.*') // FIXME: seems too broad. + ->select('mt.minimum_fee as fee, e.id as id , e.join_date, e.start_date, e.end_date, ms.name as status, mt.name as type') + ->join('mt', "!casMailingJoinType civicrm_membership_type mt ON e.membership_type_id = mt.id") + ->join('ms', "!casMailingJoinType civicrm_membership_status ms ON e.status_id = ms.id"); + } + /** * Evaluate the content of a single token. * diff --git a/Civi/ActionSchedule/Event/MailingQueryEvent.php b/Civi/ActionSchedule/Event/MailingQueryEvent.php new file mode 100644 index 0000000000..472c1c3bcd --- /dev/null +++ b/Civi/ActionSchedule/Event/MailingQueryEvent.php @@ -0,0 +1,76 @@ +query->join('foo', '!casMailingJoinType civicrm_foo foo ON foo.myentity_id = e.id') + * ->select('foo.bar_value AS bar'); + * @endcode + * + * There are several parameters pre-set for use in queries: + * - 'casActionScheduleId' + * - 'casEntityJoinExpr' - eg 'e.id = reminder.entity_id' + * - 'casMailingJoinType' - eg 'LEFT JOIN' or 'INNER JOIN' (depending on configuration) + * - 'casMappingId' + * - 'casMappingEntity' + * + * (Note: When adding more JOINs, it seems typical to use !casMailingJoinType, although + * some hard-code a LEFT JOIN. Don't have an explanation for why.) + */ +class MailingQueryEvent extends Event { + + /** + * The schedule record which produced this mailing. + * + * @var \CRM_Core_DAO_ActionSchedule + */ + public $actionSchedule; + + /** + * The mapping record which produced this mailing. + * + * @var MappingInterface + */ + public $mapping; + + /** + * The alterable query. For details, see the class description. + * @var \CRM_Utils_SQL_Select + * @see MailingQueryEvent + */ + public $query; + + /** + * @param \CRM_Core_DAO_ActionSchedule $actionSchedule + * @param MappingInterface $mapping + * @param \CRM_Utils_SQL_Select $query + */ + public function __construct($actionSchedule, $mapping, $query) { + $this->actionSchedule = $actionSchedule; + $this->mapping = $mapping; + $this->query = $query; + } + +} diff --git a/Civi/ActionSchedule/Events.php b/Civi/ActionSchedule/Events.php index 16662614b4..15e49f71db 100644 --- a/Civi/ActionSchedule/Events.php +++ b/Civi/ActionSchedule/Events.php @@ -10,6 +10,10 @@ class Events { */ const MAPPINGS = 'actionSchedule.getMappings'; + /** + * Prepare the pre-mailing query. This query loads details about + * the contact/entity so that they're available for mail-merge. + */ const MAILING_QUERY = 'actionSchedule.prepareMailingQuery'; } diff --git a/Civi/Token/AbstractTokenSubscriber.php b/Civi/Token/AbstractTokenSubscriber.php index ad20e375c3..44498a6672 100644 --- a/Civi/Token/AbstractTokenSubscriber.php +++ b/Civi/Token/AbstractTokenSubscriber.php @@ -27,6 +27,7 @@ namespace Civi\Token; +use Civi\ActionSchedule\Event\MailingQueryEvent; use Civi\Token\Event\TokenRegisterEvent; use Civi\Token\Event\TokenValueEvent; use Symfony\Component\EventDispatcher\EventSubscriberInterface; @@ -42,8 +43,11 @@ use Symfony\Component\EventDispatcher\EventSubscriberInterface; * 1. Create a subclass. * 2. Override the constructor and set values for $entity and $tokenNames. * 3. Implement the evaluateToken() method. - * 4. Optionally, override checkActive() and/or prefetch(). - * 4. Register the new class with the event-dispatcher. + * 4. Optionally, override others: + * + checkActive() + * + prefetch() + * + alterActionScheduleMailing() + * 5. Register the new class with the event-dispatcher. * * Note: There's no obligation to use this base class. You could implement * your own class anew -- just subscribe the proper events. @@ -54,6 +58,7 @@ abstract class AbstractTokenSubscriber implements EventSubscriberInterface { return array( Events::TOKEN_REGISTER => 'registerTokens', Events::TOKEN_EVALUATE => 'evaluateTokens', + \Civi\ActionSchedule\Events::MAILING_QUERY => 'alterActionScheduleQuery', ); } @@ -112,6 +117,20 @@ abstract class AbstractTokenSubscriber implements EventSubscriberInterface { } } + /** + * Alter the query which prepopulates mailing data + * for scheduled reminders. + * + * This is method is not always appropriate, but if you're specifically + * focused on scheduled reminders, it can be convenient. + * + * @param MailingQueryEvent $e + * The pending query which may be modified. See discussion on + * MailingQueryEvent::$query. + */ + public function alterActionScheduleQuery(MailingQueryEvent $e) { + } + /** * Populate the token data. * -- 2.25.1