/** * Send a reponse email informing the contact of the groups from which he * has been unsubscribed. * * @param string $queue_id The queue event ID * @param array $groups List of group IDs * @param bool $is_domain Is this domain-level? * @param int $job The job ID * @return void * @access public * @static */ public static function send_unsub_response($queue_id, $groups, $is_domain = false, $job) { $config =& CRM_Core_Config::singleton(); $domain =& CRM_Core_BAO_Domain::getDomain(); $jobTable = CRM_Mailing_BAO_Job::getTableName(); $mailingTable = CRM_Mailing_DAO_Mailing::getTableName(); $contacts = CRM_Contact_DAO_Contact::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $queue = CRM_Mailing_Event_BAO_Queue::getTableName(); //get the default domain email address. list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail(); $dao =& new CRM_Mailing_BAO_Mailing(); $dao->query(" SELECT * FROM {$mailingTable} \n INNER JOIN {$jobTable} ON\n {$jobTable}.mailing_id = {$mailingTable}.id \n WHERE {$jobTable}.id = {$job}"); $dao->fetch(); $component =& new CRM_Mailing_BAO_Component(); if ($is_domain) { $component->id = $dao->optout_id; } else { $component->id = $dao->unsubscribe_id; } $component->find(true); $html = $component->body_html; if ($component->body_text) { $text = $component->body_text; } else { $text = CRM_Utils_String::htmlToText($component->body_html); } $eq =& new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$contacts}.id as contact_id,\n {$email}.email as email,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); if ($groups) { foreach ($groups as $key => $value) { if (!$value) { unset($groups[$key]); } } } $message =& new Mail_Mime("\n"); list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email); $bao =& new CRM_Mailing_BAO_Mailing(); $bao->body_text = $text; $bao->body_html = $html; $tokens = $bao->getTokens(); require_once 'CRM/Utils/Token.php'; if ($eq->format == 'HTML' || $eq->format == 'Both') { $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true, $tokens['html']); $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, true, $eq->contact_id, $eq->hash); $html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, true, $tokens['html']); $html = CRM_Utils_Token::replaceMailingTokens($html, $dao, null, $tokens['html']); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false, $tokens['text']); $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, false, $eq->contact_id, $eq->hash); $text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, false, $tokens['text']); $text = CRM_Utils_Token::replaceMailingTokens($text, $dao, null, $tokens['text']); $message->setTxtBody($text); } require_once 'CRM/Core/BAO/MailSettings.php'; $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain(); $headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'To' => $eq->email, 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}"); $b =& CRM_Utils_Mail::setMimeParams($message); $h =& $message->headers($headers); $mailer =& $config->getMailer(); PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Core_Error', 'nullHandler')); if (is_object($mailer)) { $mailer->send($eq->email, $h, $b); CRM_Core_Error::setCallback(); } }
/** * Send a response email informing the contact of the groups from which he. * has been unsubscribed. * * @param string $queue_id * The queue event ID. * @param array $groups * List of group IDs. * @param bool $is_domain * Is this domain-level?. * @param int $job * The job ID. */ public static function send_unsub_response($queue_id, $groups, $is_domain = FALSE, $job) { $config = CRM_Core_Config::singleton(); $domain = CRM_Core_BAO_Domain::getDomain(); $jobObject = new CRM_Mailing_BAO_MailingJob(); $jobTable = $jobObject->getTableName(); $mailingObject = new CRM_Mailing_DAO_Mailing(); $mailingTable = $mailingObject->getTableName(); $contactsObject = new CRM_Contact_DAO_Contact(); $contacts = $contactsObject->getTableName(); $emailObject = new CRM_Core_DAO_Email(); $email = $emailObject->getTableName(); $queueObject = new CRM_Mailing_Event_BAO_Queue(); $queue = $queueObject->getTableName(); //get the default domain email address. list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail(); $dao = new CRM_Mailing_BAO_Mailing(); $dao->query(" SELECT * FROM {$mailingTable}\n INNER JOIN {$jobTable} ON\n {$jobTable}.mailing_id = {$mailingTable}.id\n WHERE {$jobTable}.id = {$job}"); $dao->fetch(); $component = new CRM_Mailing_BAO_Component(); if ($is_domain) { $component->id = $dao->optout_id; } else { $component->id = $dao->unsubscribe_id; } $component->find(TRUE); $html = $component->body_html; if ($component->body_text) { $text = $component->body_text; } else { $text = CRM_Utils_String::htmlToText($component->body_html); } $eq = new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$contacts}.id as contact_id,\n {$email}.email as email,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); if ($groups) { foreach ($groups as $key => $value) { if (!$value) { unset($groups[$key]); } } } $message = new Mail_mime("\n"); list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email); $bao = new CRM_Mailing_BAO_Mailing(); $bao->body_text = $text; $bao->body_html = $html; $tokens = $bao->getTokens(); if ($eq->format == 'HTML' || $eq->format == 'Both') { $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']); $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id, $eq->hash); $html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']); $html = CRM_Utils_Token::replaceMailingTokens($html, $dao, NULL, $tokens['html']); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']); $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id, $eq->hash); $text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']); $text = CRM_Utils_Token::replaceMailingTokens($text, $dao, NULL, $tokens['text']); $message->setTxtBody($text); } $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain(); $headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'To' => $eq->email, 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}"); CRM_Mailing_BAO_Mailing::addMessageIdHeader($headers, 'u', $job, $queue_id, $eq->hash); $b = CRM_Utils_Mail::setMimeParams($message); $h = $message->headers($headers); $mailer = \Civi::service('pear_mail'); if (is_object($mailer)) { $errorScope = CRM_Core_TemporaryErrorScope::ignoreException(); $mailer->send($eq->email, $h, $b); unset($errorScope); } }
/** * 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; }
/** * Send a reponse email informing the contact of the groups from which he * has been unsubscribed. * * @param string $queue_id The queue event ID * @param array $groups List of group IDs * @param bool $is_domain Is this domain-level? * @param int $job The job ID * @return void * @access public * @static */ function send_unsub_response($queue_id, $groups, $is_domain = false, $job) { $config =& CRM_Core_Config::singleton(); $domain =& CRM_Mailing_Event_BAO_Queue::getDomain($queue_id); $jobTable = CRM_Mailing_BAO_Job::getTableName(); $mailingTable = CRM_Mailing_DAO_Mailing::getTableName(); $contacts = CRM_Contact_DAO_Contact::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $queue = CRM_Mailing_Event_BAO_Queue::getTableName(); $dao =& new CRM_Mailing_DAO_Mailing(); $dao->query(" SELECT * FROM {$mailingTable} \n INNER JOIN {$jobTable} ON\n {$jobTable}.mailing_id = {$mailingTable}.id \n WHERE {$jobTable}.id = {$job}"); $dao->fetch(); $component =& new CRM_Mailing_BAO_Component(); if ($is_domain) { $component->id = $dao->optout_id; } else { $component->id = $dao->unsubscribe_id; } $component->find(true); $html = $component->body_html; $text = $component->body_text; $eq =& new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$contacts}.id as contact_id,\n {$email}.email as email,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); $message =& new Mail_Mime("\n"); require_once 'CRM/Utils/Token.php'; if ($eq->format == 'HTML' || $eq->format == 'Both') { $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true); $html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, true, $eq->contact_id, $eq->hash); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false); $text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, false, $eq->contact_id, $eq->hash); $message->setTxtBody($text); } $headers = array('Subject' => $component->subject, 'From' => ts('"%1 Administrator" <%2>', array(1 => $domain->name, 2 => "do-not-reply@{$domain->email_domain}")), 'To' => $eq->email, 'Reply-To' => "do-not-reply@{$domain->email_domain}", 'Return-Path' => "do-not-reply@{$domain->email_domain}"); $b = $message->get(); $h = $message->headers($headers); $mailer =& $config->getMailer(); PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Mailing_BAO_Mailing', 'catchSMTP')); $mailer->send($eq->email, $h, $b); CRM_Core_Error::setCallback(); }
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; }
/** * Send an automated response. * * @param object $mailing * The mailing object. * @param int $queue_id * The queue ID. * @param string $replyto * Optional reply-to from the reply. * * @return void */ private static function autoRespond(&$mailing, $queue_id, $replyto) { $config = CRM_Core_Config::singleton(); $contacts = CRM_Contact_DAO_Contact::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $queue = CRM_Mailing_Event_DAO_Queue::getTableName(); $eq = new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$email}.email as email,\n {$queue}.job_id as job_id,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); $to = empty($replyto) ? $eq->email : $replyto; $component = new CRM_Mailing_BAO_Component(); $component->id = $mailing->reply_id; $component->find(TRUE); $message = new Mail_Mime("\n"); $domain = CRM_Core_BAO_Domain::getDomain(); list($domainEmailName, $_) = CRM_Core_BAO_Domain::getNameAndEmail(); $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain(); $headers = array('Subject' => $component->subject, 'To' => $to, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}"); /* TODO: do we need reply tokens? */ $html = $component->body_html; if ($component->body_text) { $text = $component->body_text; } else { $text = CRM_Utils_String::htmlToText($component->body_html); } $bao = new CRM_Mailing_BAO_Mailing(); $bao->body_text = $text; $bao->body_html = $html; $tokens = $bao->getTokens(); if ($eq->format == 'HTML' || $eq->format == 'Both') { $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']); $html = CRM_Utils_Token::replaceMailingTokens($html, $mailing, NULL, $tokens['html']); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']); $text = CRM_Utils_Token::replaceMailingTokens($text, $mailing, NULL, $tokens['text']); $message->setTxtBody($text); } $b = CRM_Utils_Mail::setMimeParams($message); $h = $message->headers($headers); CRM_Mailing_BAO_Mailing::addMessageIdHeader($h, 'a', $eq->job_id, queue_id, $eq->hash); $mailer = $config->getMailer(); if (is_object($mailer)) { $errorScope = CRM_Core_TemporaryErrorScope::ignoreException(); $mailer->send($to, $h, $b); unset($errorScope); } }
function &getRecipients($job_id, $includeDelivered = false, $mailing_id = null, $offset = NULL, $limit = NULL) { $mailingGroup = new CRM_Mailing_DAO_Group(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_Job::getTableName(); $mg = CRM_Mailing_DAO_Group::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(); $contact = CRM_Contact_DAO_Contact::getTableName(); require_once 'CRM/Contact/DAO/Group.php'; $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); $ss = new CRM_Core_DAO(); $ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n WHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { /* run the saved search query and dump result contacts into the temp * table */ $tables = array($contact => 1); $sql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ss->saved_search_id); $sql = $sql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed')"; $mailingGroup->query("INSERT IGNORE INTO X_{$job_id} (contact_id) {$sql}"); } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id} \n (email_id int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ /* Get the emails with no override */ $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 {$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' OR {$mg}.group_type = 'Base')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.email_id IS null\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"; $mailingGroup->query($query); /* Query prior mailings */ $mailingGroup->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' OR {$mg}.group_type = 'Base')\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"); /* Construct the saved-search queries */ $ss->query("SELECT {$group}.saved_search_id as saved_search_id,\n {$group}.id as id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n AND {$mg}.entity_table = '{$group}'\n WHERE \n {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { $tables = array($contact => 1, $location => 1, $email => 1); list($from, $where) = CRM_Contact_BAO_SavedSearch::fromWhereEmail($ss->saved_search_id); $where = trim($where); if ($where) { $where = " AND {$where} "; } $ssq = "INSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n contact_a.id as contact_id \n {$from}\n LEFT JOIN X_{$job_id}\n ON contact_a.id = X_{$job_id}.contact_id\n WHERE \n contact_a.do_not_email = 0\n AND contact_a.is_opt_out = 0\n AND contact_a.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n {$where}\n AND contact_a.id NOT IN ( \n SELECT contact_id FROM {$g2contact} \n WHERE {$g2contact}.group_id = {$ss->id} AND {$g2contact}.status = 'Removed') \n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail"; $mailingGroup->query($ssq); } /** * 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); require_once 'CRM/Contact/BAO/SearchCustom.php'; while ($dao->fetch()) { $customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id); $query = "REPLACE INTO I_{$job_id} (email_id, 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 {$g2contact}.email_id is null\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"; $mailingGroup->query($query); /* Get the emails with full override */ $mailingGroup->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 {$g2contact}\n ON {$email}.id = {$g2contact}.email_id\n INNER JOIN {$contact}\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 {$g2contact}.email_id IS NOT null\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"); $results = array(); $eq = new CRM_Mailing_Event_BAO_Queue(); require_once 'CRM/Contact/BAO/Contact/Permission.php'; list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(); $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; $limitString = null; if ($limit && $offset !== null) { $limitString = "LIMIT {$offset}, {$limit}"; } $eq->query("SELECT i.contact_id, i.email_id \n FROM civicrm_contact contact_a\n INNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$aclFrom}\n {$aclWhere}\n ORDER BY i.contact_id, i.email_id\n {$limitString}"); /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }
/** * Send an automated response * * @param object $mailing The mailing object * @param int $queue_id The queue ID * @param string $replyto Optional reply-to from the reply * @return void * @access private * @static */ private static function autoRespond(&$mailing, $queue_id, $replyto) { $config =& CRM_Core_Config::singleton(); $contacts = CRM_Contact_DAO_Contact::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $queue = CRM_Mailing_Event_DAO_Queue::getTableName(); $eq =& new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$email}.email as email\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); $to = empty($replyto) ? $eq->email : $replyto; $component =& new CRM_Mailing_BAO_Component(); $component->id = $mailing->reply_id; $component->find(true); $message =& new Mail_Mime("\n"); require_once 'CRM/Core/BAO/Domain.php'; $domain =& CRM_Core_BAO_Domain::getDomain(); list($domainEmailName, $_) = CRM_Core_BAO_Domain::getNameAndEmail(); require_once 'CRM/Core/BAO/MailSettings.php'; $emailDomain = CRM_Core_BAO_MailSettings::defaultDomain(); $headers = array('Subject' => $component->subject, 'To' => $to, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}"); /* TODO: do we need reply tokens? */ $html = $component->body_html; if ($component->body_text) { $text = $component->body_text; } else { $text = CRM_Utils_String::htmlToText($component->body_html); } require_once 'CRM/Mailing/BAO/Mailing.php'; $bao =& new CRM_Mailing_BAO_Mailing(); $bao->body_text = $text; $bao->body_html = $html; $tokens = $bao->getTokens(); if ($eq->format == 'HTML' || $eq->format == 'Both') { require_once 'CRM/Utils/Token.php'; $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true, $tokens['html']); $html = CRM_Utils_Token::replaceMailingTokens($html, $mailing, null, $tokens['html']); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { require_once 'CRM/Utils/Token.php'; $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false, $tokens['text']); $text = CRM_Utils_Token::replaceMailingTokens($text, $mailing, null, $tokens['text']); $message->setTxtBody($text); } $b =& CRM_Utils_Mail::setMimeParams($message); $h =& $message->headers($headers); $mailer =& $config->getMailer(); PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Core_Error', 'nullHandler')); if (is_object($mailer)) { $mailer->send($to, $h, $b); CRM_Core_Error::setCallback(); } }
/** * Send an automated response * * @param object $mailing The mailing object * @param int $queue_id The queue ID * @param string $replyto Optional reply-to from the reply * @return void * @access private * @static */ function autoRespond(&$mailing, $queue_id, $replyto) { $config =& CRM_Core_Config::singleton(); $contacts = CRM_Contact_DAO_Contact::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $queue = CRM_Mailing_Event_DAO_Queue::getTableName(); $eq =& new CRM_Core_DAO(); $eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$email}.email as email\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer')); $eq->fetch(); $to = empty($replyto) ? $eq->email : $replyto; $component =& new CRM_Mailing_BAO_Component(); $component->id = $mailing->reply_id; $component->find(true); $message =& new Mail_Mime("\n"); require_once 'CRM/Core/BAO/Domain.php'; $domain =& CRM_Core_BAO_Domain::getDomainById($mailing->domain_id); $headers = array('Subject' => $component->subject, 'To' => $to, 'From' => ts('"%1 Administrator" <%2>', array(1 => $domain->name, 2 => "do-not-reply@{$domain->email_domain}")), 'Reply-To' => "do-not-reply@{$domain->email_domain}", 'Return-Path' => "do-not-reply@{$domain->email_domain}"); /* TODO: do we need reply tokens? */ if ($eq->format == 'HTML' || $eq->format == 'Both') { $html = $component->body_html; require_once 'CRM/Utils/Token.php'; $html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true); $message->setHTMLBody($html); } if (!$html || $eq->format == 'Text' || $eq->format == 'Both') { $text = $component->body_text; require_once 'CRM/Utils/Token.php'; $text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false); $message->setTxtBody($text); } $b = $message->get(); $h = $message->headers($headers); $mailer =& $config->getMailer(); PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Mailing_BAO_Mailing', 'catchSMTP')); $mailer->send($to, $h, $b); CRM_Core_Error::setCallback(); }
/** * Find all intended recipients of a mailing * * @param int $job_id Job ID * @return object A DAO loaded with results of the form * (email_id, contact_id) */ function &getRecipients($job_id) { $mailingGroup =& new CRM_Mailing_DAO_Group(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_Job::getTableName(); $mg = CRM_Mailing_DAO_Group::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(); $contact = CRM_Contact_DAO_Contact::getTableName(); $location = CRM_Core_DAO_Location::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 {$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 = {$this->id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* 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 {$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 = {$this->id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); /* Add all the succesful deliveries of this mailing (but any job/retry) * to the exclude temp table */ $excludeRetry = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$ed}\n ON {$eq}.id = {$ed}.event_queue_id\n LEFT JOIN {$eb}\n ON {$eq}.id = {$eb}.event_queue_id\n WHERE\n {$job}.mailing_id = {$this->id}\n AND {$eb}.id IS null"; $mailingGroup->query($excludeRetry); $ss =& new CRM_Core_DAO(); $ss->query("SELECT {$group}.saved_search_id as saved_search_id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n WHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$this->id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { /* run the saved search query and dump result contacts into the temp * table */ $tables = array($contact => 1); $where = CRM_Contact_BAO_SavedSearch::whereClause($ss->saved_search_id, $tables, $whereTables); $from = CRM_Contact_BAO_Query::fromClause($tables); $mailingGroup->query("INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT {$contact}.id\n {$from}\n WHERE {$where}"); } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id} \n (email_id int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ /* Get the emails with no override */ $mailingGroup->query("INSERT 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 {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\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 {$g2contact}.status = 'Added'\n AND {$g2contact}.location_id IS null\n AND {$g2contact}.email_id IS null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Query prior mailings */ $mailingGroup->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 {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\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 {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Construct the saved-search queries */ $ss->query("SELECT {$group}.saved_search_id as saved_search_id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n AND {$mg}.entity_table = '{$group}'\n WHERE \n {$mg}.group_type = 'Include'\n AND {$mg}.mailing_id = {$this->id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { $tables = array($contact => 1, $location => 1, $email => 1); $where = CRM_Contact_BAO_SavedSearch::whereClause($ss->saved_search_id, $tables, $whereTables); $from = CRM_Contact_BAO_Query::fromClause($tables); $ssq = "INSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id \n {$from}\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$where}\n AND X_{$job_id}.contact_id IS null "; $mailingGroup->query($ssq); } /* Get the emails with only location override */ $mailingGroup->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 {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n AND {$location}.id = {$g2contact}.location_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 {$g2contact}.location_id IS NOT null\n AND {$g2contact}.email_id is null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Get the emails with full override */ $mailingGroup->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 {$g2contact}\n ON {$email}.id = {$g2contact}.email_id\n INNER JOIN {$contact}\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 {$g2contact}.location_id IS NOT null\n AND {$g2contact}.email_id IS NOT null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); $results = array(); $eq =& new CRM_Mailing_Event_BAO_Queue(); $eq->query("SELECT contact_id, email_id \n FROM I_{$job_id} \n ORDER BY contact_id, email_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; }