/** * Unsubscribe a contact from the domain. * * @param int $job_id * The job ID. * @param int $queue_id * The Queue Event ID of the recipient. * @param string $hash * The hash. * * @return bool * Was the contact successfully unsubscribed? */ public static function unsub_from_domain($job_id, $queue_id, $hash) { $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash); if (!$q) { return FALSE; } $transaction = new CRM_Core_Transaction(); $now = date('YmdHis'); if (CRM_Core_BAO_Email::isMultipleBulkMail()) { $email = new CRM_Core_BAO_Email(); $email->id = $q->email_id; if ($email->find(TRUE)) { $sql = "\nUPDATE civicrm_email\nSET on_hold = 2,\n hold_date = %1\nWHERE email = %2\n"; $sqlParams = array(1 => array($now, 'Timestamp'), 2 => array($email->email, 'String')); CRM_Core_DAO::executeQuery($sql, $sqlParams); } } else { $contact = new CRM_Contact_BAO_Contact(); $contact->id = $q->contact_id; $contact->is_opt_out = TRUE; $contact->save(); } $ue = new CRM_Mailing_Event_BAO_Unsubscribe(); $ue->event_queue_id = $queue_id; $ue->org_unsubscribe = 1; $ue->time_stamp = $now; $ue->save(); $shParams = array('contact_id' => $q->contact_id, 'group_id' => NULL, 'status' => 'Removed', 'method' => 'Email', 'tracking' => $ue->id); CRM_Contact_BAO_SubscriptionHistory::create($shParams); $transaction->commit(); return TRUE; }
/** * Build the form object elements for an email object. * * @param CRM_Core_Form $form * Reference to the form object. * @param int $blockCount * Block number to build. * @param bool $blockEdit * Is it block edit. */ public static function buildQuickForm(&$form, $blockCount = NULL, $blockEdit = FALSE) { // passing this via the session is AWFUL. we need to fix this if (!$blockCount) { $blockId = $form->get('Email_Block_Count') ? $form->get('Email_Block_Count') : 1; } else { $blockId = $blockCount; } $form->applyFilter('__ALL__', 'trim'); //Email box $form->addField("email[{$blockId}][email]", array('entity' => 'email')); $form->addRule("email[{$blockId}][email]", ts('Email is not valid.'), 'email'); if (isset($form->_contactType) || $blockEdit) { //Block type $form->addField("email[{$blockId}][location_type_id]", array('entity' => 'email', 'placeholder' => NULL, 'class' => 'eight')); //TODO: Refactor on_hold field to select. $multipleBulk = CRM_Core_BAO_Email::isMultipleBulkMail(); //On-hold select if ($multipleBulk) { $holdOptions = array(0 => ts('- select -'), 1 => ts('On Hold Bounce'), 2 => ts('On Hold Opt Out')); $form->addElement('select', "email[{$blockId}][on_hold]", '', $holdOptions); } else { $form->addField("email[{$blockId}][on_hold]", array('entity' => 'email', 'type' => 'advcheckbox')); } //Bulkmail checkbox $form->assign('multipleBulk', $multipleBulk); if ($multipleBulk) { $js = array('id' => "Email_" . $blockId . "_IsBulkmail"); $form->addElement('advcheckbox', "email[{$blockId}][is_bulkmail]", NULL, '', $js); } else { $js = array('id' => "Email_" . $blockId . "_IsBulkmail"); if (!$blockEdit) { $js['onClick'] = 'singleSelect( this.id );'; } $form->addElement('radio', "email[{$blockId}][is_bulkmail]", '', '', '1', $js); } //is_Primary radio $js = array('id' => "Email_" . $blockId . "_IsPrimary"); if (!$blockEdit) { $js['onClick'] = 'singleSelect( this.id );'; } $form->addElement('radio', "email[{$blockId}][is_primary]", '', '', '1', $js); if (CRM_Utils_System::getClassName($form) == 'CRM_Contact_Form_Contact') { $form->add('textarea', "email[{$blockId}][signature_text]", ts('Signature (Text)'), array('rows' => 2, 'cols' => 40)); $form->add('wysiwyg', "email[{$blockId}][signature_html]", ts('Signature (HTML)'), array('rows' => 2, 'cols' => 40)); } } }
/** * @param array $deliveredParams * @param array $targetParams * @param $mailing * @param $job_date * * @return bool * @throws CRM_Core_Exception * @throws Exception */ public function writeToDB(&$deliveredParams, &$targetParams, &$mailing, $job_date) { static $activityTypeID = NULL; static $writeActivity = NULL; if (!empty($deliveredParams)) { CRM_Mailing_Event_BAO_Delivered::bulkCreate($deliveredParams); $deliveredParams = array(); } if ($writeActivity === NULL) { $writeActivity = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::MAILING_PREFERENCES_NAME, 'write_activity_record', NULL, TRUE); } if (!$writeActivity) { return TRUE; } $result = TRUE; if (!empty($targetParams) && !empty($mailing->scheduled_id)) { if (!$activityTypeID) { if ($mailing->sms_provider_id) { $mailing->subject = $mailing->name; $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Mass SMS', 'name'); } else { $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name'); } if (!$activityTypeID) { CRM_Core_Error::fatal(); } } $activity = array('source_contact_id' => $mailing->scheduled_id, 'target_contact_id' => array_unique($targetParams), 'activity_type_id' => $activityTypeID, 'source_record_id' => $this->mailing_id, 'activity_date_time' => $job_date, 'subject' => $mailing->subject, 'status_id' => 2, 'deleteActivityTarget' => FALSE, 'campaign_id' => $mailing->campaign_id); //check whether activity is already created for this mailing. //if yes then create only target contact record. $query = "\nSELECT id\nFROM civicrm_activity\nWHERE civicrm_activity.activity_type_id = %1\nAND civicrm_activity.source_record_id = %2\n"; $queryParams = array(1 => array($activityTypeID, 'Integer'), 2 => array($this->mailing_id, 'Integer')); $activityID = CRM_Core_DAO::singleValueQuery($query, $queryParams); if ($activityID) { $activity['id'] = $activityID; // CRM-9519 if (CRM_Core_BAO_Email::isMultipleBulkMail()) { static $targetRecordID = NULL; if (!$targetRecordID) { $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); $targetRecordID = CRM_Utils_Array::key('Activity Targets', $activityContacts); } // make sure we don't attempt to duplicate the target activity foreach ($activity['target_contact_id'] as $key => $targetID) { $sql = "\nSELECT id\nFROM civicrm_activity_contact\nWHERE activity_id = {$activityID}\nAND contact_id = {$targetID}\nAND record_type_id = {$targetRecordID}\n"; if (CRM_Core_DAO::singleValueQuery($sql)) { unset($activity['target_contact_id'][$key]); } } } } if (is_a(CRM_Activity_BAO_Activity::create($activity), 'CRM_Core_Error')) { $result = FALSE; } $targetParams = array(); } return $result; }
/** * note that $job_id is used only as a variable in the temp table construction * and does not play a role in the queries generated * @param int $job_id * (misnomer) a nonce value used to name temporary tables. * @param int $mailing_id * @param bool $storeRecipients * @param bool $dedupeEmail * @param null $mode * * @return CRM_Mailing_Event_BAO_Queue|string */ public static function getRecipients($job_id, $mailing_id = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL) { $mailingGroup = new CRM_Mailing_DAO_MailingGroup(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_MailingJob::getTableName(); $mg = CRM_Mailing_DAO_MailingGroup::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); if ($mode == 'sms') { $phone = CRM_Core_DAO_Phone::getTableName(); } $contact = CRM_Contact_DAO_Contact::getTableName(); $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 // 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}\n (contact_id int primary key)\n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all unsubscribe members of base group from this mailing to the temp * table */ $unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'"; $mailingGroup->query($unSubscribeBaseGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); // get all the saved searches AND hierarchical groups // and load them in the cache $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n"; $mailingGroup->query($smartGroupExclude); } $tempColumn = 'email_id'; if ($mode == 'sms') { $tempColumn = 'phone_id'; } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == 'sms') { $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); /* Query prior mailings */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == 'sms') { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupInclude = "\nREPLACE INTO I_{$job_id} (email_id, contact_id)\nSELECT civicrm_email.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email ON civicrm_email.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND {$location_filter}\n AND civicrm_email.on_hold = 0\n AND X_{$job_id}.contact_id IS null\n{$order_by}\n"; if ($mode == 'sms') { $smartGroupInclude = "\nREPLACE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($smartGroupInclude); } /** * Construct the filtered search queries */ $query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}"; $mailingGroup->query($query); } /* Get the emails with only location override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}"; if ($mode == "sms") { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $eq = new CRM_Mailing_Event_BAO_Queue(); list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; if ($storeRecipients && $mailing_id) { $sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n"; $params = array(1 => array($mailing_id, 'Integer')); CRM_Core_DAO::executeQuery($sql, $params); // CRM-3975 $groupBy = $groupJoin = ''; if ($dedupeEmail) { $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; $groupBy = " GROUP BY e.email "; } $sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n"; CRM_Core_DAO::executeQuery($sql, $params); // if we need to add all emails marked bulk, do it as a post filter // on the mailing recipients table if (CRM_Core_BAO_Email::isMultipleBulkMail()) { self::addMultipleEmails($mailing_id); } } /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }
static function &getRecipients($job_id, $mailing_id = NULL, $offset = NULL, $limit = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL) { $mailingGroup = new CRM_Mailing_DAO_MailingGroup(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_MailingJob::getTableName(); $mg = CRM_Mailing_DAO_MailingGroup::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $ed = CRM_Mailing_Event_DAO_Delivered::getTableName(); $eb = CRM_Mailing_Event_DAO_Bounce::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); if ($mode == 'sms') { $phone = CRM_Core_DAO_Phone::getTableName(); } $contact = CRM_Contact_DAO_Contact::getTableName(); $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); /* Create a temp table for contact exclusion */ $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all unsubscribe members of base group from this mailing to the temp * table */ $unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'"; $mailingGroup->query($unSubscribeBaseGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); // get all the saved searches AND hierarchical groups // and load them in the cache $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n"; $mailingGroup->query($smartGroupExclude); } $tempColumn = 'email_id'; if ($mode == 'sms') { $tempColumn = 'phone_id'; } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == 'sms') { $phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name'); $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); /* Query prior mailings */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == 'sms') { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n"; $groupDAO = CRM_Core_DAO::executeQuery($sql); while ($groupDAO->fetch()) { if ($groupDAO->cache_date == NULL) { CRM_Contact_BAO_GroupContactCache::load($groupDAO); } $smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\nSELECT e.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email e ON e.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND (e.is_bulkmail = 1 OR e.is_primary = 1)\n AND e.on_hold = 0\n AND X_{$job_id}.contact_id IS null\nORDER BY e.is_bulkmail\n"; if ($mode == 'sms') { $smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($smartGroupInclude); } /** * Construct the filtered search queries */ $query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}"; $mailingGroup->query($query); } /* Get the emails with only location override */ $query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; if ($mode == "sms") { $query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null"; } $mailingGroup->query($query); $results = array(); $eq = new CRM_Mailing_Event_BAO_Queue(); list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; $limitString = NULL; if ($limit && $offset !== NULL) { $offset = CRM_Utils_Type::escape($offset, 'Int'); $limit = CRM_Utils_Type::escape($limit, 'Int'); $limitString = "LIMIT {$offset}, {$limit}"; } if ($storeRecipients && $mailing_id) { $sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n"; $params = array(1 => array($mailing_id, 'Integer')); CRM_Core_DAO::executeQuery($sql, $params); // CRM-3975 $groupBy = $groupJoin = ''; if ($dedupeEmail) { $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; $groupBy = " GROUP BY e.email "; } $sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n"; CRM_Core_DAO::executeQuery($sql, $params); // if we need to add all emails marked bulk, do it as a post filter // on the mailing recipients table if (CRM_Core_BAO_Email::isMultipleBulkMail()) { self::addMultipleEmails($mailing_id); } } /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }