From bac4cd354e55574d7884cf71391869898aedd139 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Wed, 12 Nov 2014 18:06:27 +0530 Subject: [PATCH] CRM-15361 fix - allow selection of location type when sending bulk email https://issues.civicrm.org/jira/browse/CRM-15361 --- CRM/Core/SelectValues.php | 14 +++- CRM/Mailing/BAO/Mailing.php | 67 ++++++++++++++++--- CRM/Mailing/Form/Group.php | 23 +++++-- .../Incremental/sql/4.6.alpha1.mysql.tpl | 4 ++ templates/CRM/Mailing/Form/Group.hlp | 11 +++ templates/CRM/Mailing/Form/Group.tpl | 9 +++ xml/schema/Mailing/Mailing.xml | 16 +++++ 7 files changed, 128 insertions(+), 16 deletions(-) diff --git a/CRM/Core/SelectValues.php b/CRM/Core/SelectValues.php index 70d3eba173..aad0c836ff 100644 --- a/CRM/Core/SelectValues.php +++ b/CRM/Core/SelectValues.php @@ -111,6 +111,19 @@ class CRM_Core_SelectValues { ); } + /** + * various pre defined email selection methods + * @static + */ + static function emailSelectMethods() { + return array( + 'automatic' => ts("Automatic"), + 'location-only' => ts("Only send to email addresses assigned to the specified location"), + 'location-prefer' => ts("Prefer email addresses assigned to the specified location"), + 'location-exclude' => ts("Exclude email addresses assigned to the specified location") + ); + } + /** * various pre defined member visibility options * @static @@ -900,4 +913,3 @@ class CRM_Core_SelectValues { return $scheduleReminderFrequencyUnits; } } - diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index 09cfd0e076..8d84f67b1b 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -148,6 +148,51 @@ class CRM_Mailing_BAO_Mailing extends CRM_Mailing_DAO_Mailing { $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); + $m = new CRM_Mailing_DAO_Mailing(); + $m->id = $mailing_id; + $m->find(TRUE); + + $email_selection_method = $m->email_selection_method; + $location_type_id = $m->location_type_id; + + // Note: When determining the ORDER that results are returned, it's + // the record that comes last that counts. That's because we are + // INSERT'ing INTO a table with a primary id so that last record + // over writes any previous record. + switch($email_selection_method) { + case 'location-exclude': + $location_filter = "($email.location_type_id != $location_type_id)"; + // If there is more than one email that doesn't match the location, + // prefer the one marked is_bulkmail, followed by is_primary. + $order_by = "ORDER BY $email.is_bulkmail, $email.is_primary"; + break; + case 'location-only': + $location_filter = "($email.location_type_id = $location_type_id)"; + // If there is more than one email of the desired location, prefer + // the one marked is_bulkmail, followed by is_primary. + $order_by = "ORDER BY $email.is_bulkmail, $email.is_primary"; + break; + case 'location-prefer': + $location_filter = "($email.is_bulkmail = 1 OR $email.is_primary = 1 OR $email.location_type_id = $location_type_id)"; + + // ORDER BY is more complicated because we have to set an arbitrary + // order that prefers the location that we want. We do that using + // the FIELD function. For more info, see: + // https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field + // We assign the location type we want the value "1" by putting it + // in the first position after we name the field. All other location + // types are left out, so they will be assigned the value 0. That + // means, they will all be equally tied for first place, with our + // location being last. + $order_by = "ORDER BY FIELD($email.location_type_id, $location_type_id), $email.is_bulkmail, $email.is_primary"; + break; + case 'automatic': + // fall through to default + default: + $location_filter = "($email.is_bulkmail = 1 OR $email.is_primary = 1)"; + $order_by = "ORDER BY $email.is_bulkmail"; + } + /* Create a temp table for contact exclusion */ $mailingGroup->query( "CREATE TEMPORARY TABLE X_$job_id @@ -264,13 +309,13 @@ WHERE c.group_id = {$groupDAO->id} AND $contact.do_not_email = 0 AND $contact.is_opt_out = 0 AND $contact.is_deceased = 0 - AND ($email.is_bulkmail = 1 OR $email.is_primary = 1) + AND $location_filter AND $email.email IS NOT NULL AND $email.email != '' AND $email.on_hold = 0 AND $mg.mailing_id = {$mailing_id} AND X_$job_id.contact_id IS null - ORDER BY $email.is_bulkmail"; + $order_by"; if ($mode == 'sms') { $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); @@ -324,11 +369,11 @@ WHERE c.group_id = {$groupDAO->id} AND $contact.do_not_email = 0 AND $contact.is_opt_out = 0 AND $contact.is_deceased = 0 - AND ($email.is_bulkmail = 1 OR $email.is_primary = 1) + AND $location_filter AND $email.on_hold = 0 AND $mg.mailing_id = {$mailing_id} AND X_$job_id.contact_id IS null - ORDER BY $email.is_bulkmail"; + $order_by"; if ($mode == 'sms') { $query = "REPLACE INTO I_$job_id (phone_id, contact_id) @@ -377,19 +422,19 @@ WHERE $mg.entity_table = '$group' $smartGroupInclude = " INSERT IGNORE INTO I_$job_id (email_id, contact_id) -SELECT e.id as email_id, c.id as contact_id +SELECT civicrm_email.id as email_id, c.id as contact_id FROM civicrm_contact c -INNER JOIN civicrm_email e ON e.contact_id = c.id +INNER JOIN civicrm_email ON civicrm_email.contact_id = c.id INNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id LEFT JOIN X_$job_id ON X_$job_id.contact_id = c.id WHERE gc.group_id = {$groupDAO->id} AND c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 - AND (e.is_bulkmail = 1 OR e.is_primary = 1) - AND e.on_hold = 0 + AND $location_filter + AND civicrm_email.on_hold = 0 AND X_$job_id.contact_id IS null -ORDER BY e.is_bulkmail +$order_by "; if ($mode == 'sms') { $smartGroupInclude = " @@ -450,11 +495,11 @@ AND $mg.mailing_id = {$mailing_id} AND $contact.do_not_email = 0 AND $contact.is_opt_out = 0 AND $contact.is_deceased = 0 - AND ($email.is_bulkmail = 1 OR $email.is_primary = 1) + AND $location_filter AND $email.on_hold = 0 AND $mg.mailing_id = {$mailing_id} AND X_$job_id.contact_id IS null - ORDER BY $email.is_bulkmail"; + $order_by"; if ($mode == "sms") { $query = "REPLACE INTO I_$job_id (phone_id, contact_id) SELECT DISTINCT $phone.id as phone_id, diff --git a/CRM/Mailing/Form/Group.php b/CRM/Mailing/Form/Group.php index 51f013d432..2d22e0d848 100644 --- a/CRM/Mailing/Form/Group.php +++ b/CRM/Mailing/Form/Group.php @@ -103,7 +103,6 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { $defaults['dedupe_email'] = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::MAILING_PREFERENCES_NAME, 'dedupe_email_default', NULL, FALSE ); - if ($this->_mailingID) { // check that the user has permission to access mailing id CRM_Mailing_BAO_Mailing::checkPermission($this->_mailingID); @@ -124,6 +123,8 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { $defaults['campaign_id'] = $mailing->campaign_id; $defaults['dedupe_email'] = $mailing->dedupe_email; + $defaults['location_type_id'] = $mailing->location_type_id; + $defaults['email_selection_method'] = $mailing->email_selection_method; $dao = new CRM_Mailing_DAO_MailingGroup(); @@ -213,6 +214,13 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { //dedupe on email option $this->addElement('checkbox', 'dedupe_email', ts('Remove duplicate emails?')); + // location types + $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id', array('id' => 'display_name')); + $this->addElement('select', 'location_type_id', ts("Location Type"), array('' => '-select-') + $locationTypes); + + $methods = CRM_Core_SelectValues::emailSelectMethods(); + $this->addElement('select', 'email_selection_method', ts("Email Selection Method"), $methods); + //get the mailing groups. $groups = CRM_Core_PseudoConstant::nestedGroup('Mailing'); if ($hiddenMailingGroup) { @@ -373,7 +381,7 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { } foreach ( - array('name', 'group_id', 'search_id', 'search_args', 'campaign_id', 'dedupe_email') as $n + array('name', 'group_id', 'search_id', 'search_args', 'campaign_id', 'dedupe_email', 'location_type_id', 'email_selection_method') as $n ) { if (!empty($values[$n])) { $params[$n] = $values[$n]; @@ -449,7 +457,6 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { $params['created_id'] = $session->get('userID'); $params['created_date'] = date('YmdHis'); } - $mailing = CRM_Mailing_BAO_Mailing::create($params, $ids); $this->set('mailing_id', $mailing->id); @@ -580,7 +587,15 @@ class CRM_Mailing_Form_Group extends CRM_Contact_Form_Task { $errors['search_id'] = ts('You must select a search to filter'); } + if (!empty($fields['location_type_id'])) { + if ($fields['email_selection_method'] == 'automatic') { + $errors['location_type_id'] = ts("If 'Email Selection Method' is automatic, you are not allowed to choose any 'Location Type'"); + } + } + elseif ($fields['email_selection_method'] != 'automatic') { + $errors['email_selection_method'] = ts("If 'Location Type' is not selected, you must set the 'Email Selection Method' to automatic as well."); + } + return empty($errors) ? TRUE : $errors; } } - diff --git a/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl index d12ff42a69..d8cada58e1 100644 --- a/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl @@ -74,3 +74,7 @@ UPDATE `civicrm_state_province` SET `name` = (N'Jūrmala') where `id` = 3552; UPDATE `civicrm_state_province` SET `name` = (N'Liepāja') WHERE `id` = 3553; UPDATE `civicrm_state_province` SET `name` = (N'Rēzekne') WHERE `id` = 3554; UPDATE `civicrm_state_province` SET `name` = (N'Rīga') WHERE `id` = 3555; + +--CRM-15361: Allow selection of location type when sending bulk email +ALTER TABLE civicrm_mailing ADD COLUMN location_type_id INT(10) unsigned DEFAULT 0 COMMENT 'With email_selection_method, determines which email address to use'; +ALTER TABLE civicrm_mailing ADD COLUMN email_selection_method varchar(20) DEFAULT 'automatic' COMMENT 'With location_type_id, determine how to choose the email address to use.'; diff --git a/templates/CRM/Mailing/Form/Group.hlp b/templates/CRM/Mailing/Form/Group.hlp index 2271169268..68e5d3900f 100644 --- a/templates/CRM/Mailing/Form/Group.hlp +++ b/templates/CRM/Mailing/Form/Group.hlp @@ -54,6 +54,17 @@

{ts}Selecting this option will ensure only one email is sent to each address. Note that when using this option and including contact tokens in your email content, the email with it's respective tokens will only be created for one of the matching contacts.{/ts}

{/htxt} +{htxt id="email-selection-title"} + {ts}Email selection{/ts} +{/htxt} +{htxt id="email-selection"} +

{ts}Some of your contacts may have more than one email address. By selecting a location and a selection method, you can tell CiviCRM which email address to use for this mailing.{/ts}

+

{ts}Automatic: If you choose automatic for both the location and the selection method, CiviCRM will choose the best option for each user: If an email is marked "Bulk" then it will be used; otherwise, the email marked "Primary" will be used.{/ts}

+

{ts}If "Only send to email addresses assigned to the specified location" is selected, then the recipients will be restricted to only include contacts that have an email address assigned to the given location. This option may limit the number of recipients in your mailing.{/ts}

+

{ts}If "Prefer email addresses assigned to the specified location" is selected, then recipients that have an email address assigned to the given location will have that email used. Other recipients will have their email selected using the automatic method.{/ts}

+

{ts}If "Exclude email addresses assigned to the specified location" is chosen, then recipients who only have an email address assigned to the given location will be excluded. Everyone else will have their email chosen from the list of emails that are not assigned to the given location, using the automatic method.{/ts}

+{/htxt} + {htxt id="include-groups-title"} {ts}Include Groups{/ts} {/htxt} diff --git a/templates/CRM/Mailing/Form/Group.tpl b/templates/CRM/Mailing/Form/Group.tpl index 9e091fa0ad..b4b031b222 100644 --- a/templates/CRM/Mailing/Form/Group.tpl +++ b/templates/CRM/Mailing/Form/Group.tpl @@ -44,6 +44,15 @@ {$form.dedupe_email.label} {$form.dedupe_email.html} {help id="dedupe-email"} + + {$form.location_type_id.label} + {$form.location_type_id.html} + + + {$form.email_selection_method.label} + {$form.email_selection_method.html} {help id="email-selection"} + + {if ($groupCount > 0|| $mailingCount > 0)} diff --git a/xml/schema/Mailing/Mailing.xml b/xml/schema/Mailing/Mailing.xml index cefea95c39..d3005524ab 100644 --- a/xml/schema/Mailing/Mailing.xml +++ b/xml/schema/Mailing/Mailing.xml @@ -395,4 +395,20 @@ hash 4.5 + + location_type_id + Location Type + int unsigned + 0 + With email_selection_method, determines which email address to use + 4.5 + + + email_selection_method + varchar + 20 + 'automatic' + With location_type_id, determine how to choose the email address to use. + 4.4 + -- 2.25.1