/**
* takes an associative array and adds email
*
* @param array $params (reference ) an assoc array of name/value pairs
*
* @return object CRM_Core_BAO_Email object on success, null otherwise
* @access public
* @static
*/
static function add(&$params)
{
$email = new CRM_Core_DAO_Email();
$email->copyValues($params);
// CRM-11006 move calls to pre hook from create function to add function
if (!empty($params['id'])) {
CRM_Utils_Hook::pre('edit', 'Email', $params['id'], $email);
} else {
CRM_Utils_Hook::pre('create', 'Email', NULL, $e);
}
// lower case email field to optimize queries
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$email->email = $strtolower($email->email);
// since we're setting bulkmail for 1 of this contact's emails, first reset all their emails to is_bulkmail false
// (only 1 email address can have is_bulkmail = true)
if ($email->is_bulkmail != 'null' && $params['contact_id'] && !self::isMultipleBulkMail()) {
$sql = "\nUPDATE civicrm_email\nSET is_bulkmail = 0\nWHERE contact_id = {$params['contact_id']}\n";
CRM_Core_DAO::executeQuery($sql);
}
// handle if email is on hold
self::holdEmail($email);
$email->save();
// CRM-11006 move calls to pre hook from create function to add function
if (!empty($params['id'])) {
CRM_Utils_Hook::post('edit', 'Email', $email->id, $email);
} else {
CRM_Utils_Hook::post('create', 'Email', $email->id, $email);
}
return $email;
}
/**
* Takes an associative array and adds email.
*
* @param array $params
* (reference ) an assoc array of name/value pairs.
*
* @return object
* CRM_Core_BAO_Email object on success, null otherwise
*/
public static function add(&$params)
{
$hook = empty($params['id']) ? 'create' : 'edit';
CRM_Utils_Hook::pre($hook, 'Email', CRM_Utils_Array::value('id', $params), $params);
$email = new CRM_Core_DAO_Email();
$email->copyValues($params);
// lower case email field to optimize queries
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$email->email = $strtolower($email->email);
/*
* since we're setting bulkmail for 1 of this contact's emails, first reset all their other emails to is_bulkmail false
* We shouldn't not set the current email to false even though we
* are about to reset it to avoid contaminating the changelog if logging is enabled
* (only 1 email address can have is_bulkmail = true)
*/
if ($email->is_bulkmail != 'null' && $params['contact_id'] && !self::isMultipleBulkMail()) {
$sql = "\nUPDATE civicrm_email\nSET is_bulkmail = 0\nWHERE contact_id = {$params['contact_id']}\n";
if ($hook == 'edit') {
$sql .= " AND id <> {$params['id']}";
}
CRM_Core_DAO::executeQuery($sql);
}
// handle if email is on hold
self::holdEmail($email);
$email->save();
if ($email->is_primary) {
// update the UF user email if that has changed
CRM_Core_BAO_UFMatch::updateUFName($email->contact_id);
}
CRM_Utils_Hook::post($hook, 'Email', $email->id, $email);
return $email;
}
/**
* takes an associative array and adds email
*
* @param array $params (reference ) an assoc array of name/value pairs
*
* @return object CRM_Core_BAO_Email object on success, null otherwise
* @access public
* @static
*/
static function add(&$params)
{
$email = new CRM_Core_DAO_Email();
$email->copyValues($params);
// lower case email field to optimize queries
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$email->email = $strtolower($email->email);
// since we're setting bulkmail for 1 of this contact's emails, first reset all their emails to is_bulkmail false
// (only 1 email address can have is_bulkmail = true)
if ($email->is_bulkmail != 'null' && $params['contact_id']) {
$sql = "\nUPDATE civicrm_email \nSET is_bulkmail = 0\nWHERE \ncontact_id = {$params['contact_id']}";
CRM_Core_DAO::executeQuery($sql);
}
// handle if email is on hold
self::holdEmail($email);
return $email->save();
}
function testGetReferenceColumns()
{
// choose CRM_Core_DAO_Email as an arbitrary example
$emailRefs = CRM_Core_DAO_Email::getReferenceColumns();
$refsByTarget = array();
foreach ($emailRefs as $refSpec) {
$refsByTarget[$refSpec->getTargetTable()] = $refSpec;
}
$this->assertTrue(array_key_exists('civicrm_contact', $refsByTarget));
$contactRef = $refsByTarget['civicrm_contact'];
$this->assertEquals('contact_id', $contactRef->getReferenceKey());
$this->assertEquals('id', $contactRef->getTargetKey());
$this->assertEquals(FALSE, $contactRef->isGeneric());
}
/**
* Ensure that hook_civicrm_entityTypes runs and correctly handles the
* 'fields_callback' option.
*/
public function testHook()
{
// 1. First, check the baseline fields()...
$fields = CRM_Core_DAO_Email::fields();
$this->assertFalse(isset($fields['location_type_id']['foo']));
$exports = CRM_Core_DAO_Email::export();
$this->assertFalse(isset($exports['contact_id']));
// 2. Now, let's hook into it...
$this->hookClass->setHook('civicrm_entityTypes', array($this, '_hook_civicrm_entityTypes'));
unset(Civi::$statics['CRM_Core_DAO_Email']);
CRM_Core_DAO_AllCoreTables::init(1);
// 3. And see if the data has changed...
$fields = CRM_Core_DAO_Email::fields();
$this->assertEquals('bar', $fields['location_type_id']['foo']);
$exports = CRM_Core_DAO_Email::export();
$this->assertTrue(is_array($exports['contact_id']));
}
/**
* This function adds the contact variable in $values to the
* parameter list $params. For most cases, $values should have length 1. If
* the variable being added is a child of Location, a location_type_id must
* also be included. If it is a child of phone, a phone_type must be included.
*
* @param array $values The variable(s) to be added
* @param array $params The structured parameter list
*
* @return bool|CRM_Utils_Error
* @access public
*/
function _crm_add_formatted_param(&$values, &$params)
{
/* Crawl through the possible classes:
* Contact
* Individual
* Household
* Organization
* Location
* Address
* Email
* Phone
* IM
* Note
* Custom
*/
/* Cache the various object fields */
static $fields = null;
if ($fields == null) {
$fields = array();
}
if (isset($values['contact_type'])) {
/* we're an individual/household/org property */
if (!isset($fields[$values['contact_type']])) {
require_once str_replace('_', DIRECTORY_SEPARATOR, 'CRM_Contact_DAO_' . $values['contact_type']) . '.php';
eval('$fields[' . $values['contact_type'] . '] =&
CRM_Contact_DAO_' . $values['contact_type'] . '::fields();');
}
_crm_store_values($fields[$values['contact_type']], $values, $params);
return true;
}
if (isset($values['individual_prefix'])) {
$params['prefix'] = $values['individual_prefix'];
return true;
}
if (isset($values['individual_suffix'])) {
$params['suffix'] = $values['individual_suffix'];
return true;
}
if (isset($values['gender'])) {
$params['gender'] = $values['gender'];
return true;
}
if (isset($values['location_type_id'])) {
/* find and/or initialize the correct location block in $params */
$locBlock = null;
if (!isset($params['location'])) {
/* if we don't have a location field yet, make one */
$locBlock = 1;
$params['location'][$locBlock] = array('location_type_id' => $values['location_type_id'], 'is_primary' => true);
} else {
/* search through the location array for a matching loc. type */
foreach ($params['location'] as $key => $loc) {
if ($loc['location_type_id'] == $values['location_type_id']) {
$locBlock = $key;
}
}
/* if no locBlock has the correct type, make a new one */
if ($locBlock == null) {
$locBlock = count($params['location']) + 1;
$params['location'][$locBlock] = array('location_type_id' => $values['location_type_id']);
}
}
//add location name
if (isset($values['name'])) {
$params['location'][$locBlock]['name'] = $values['name'];
}
/* if this is a phone value, find or create the correct block */
if (isset($values['phone'])) {
if (!isset($params['location'][$locBlock]['phone'])) {
/* if we don't have a phone array yet, make one */
$params['location'][$locBlock]['phone'] = array();
}
/* add a new phone block to the array */
$phoneBlock = count($params['location'][$locBlock]['phone']) + 1;
$params['location'][$locBlock]['phone'][$phoneBlock] = array();
if (!isset($fields['Phone'])) {
$fields['Phone'] = CRM_Core_DAO_Phone::fields();
}
_crm_store_values($fields['Phone'], $values, $params['location'][$locBlock]['phone'][$phoneBlock]);
if ($phoneBlock == 1) {
$params['location'][$locBlock]['phone'][$phoneBlock]['is_primary'] = true;
}
return true;
}
/* If this is an email value, create a new block to store it */
if (isset($values['email'])) {
if (!isset($params['location'][$locBlock]['email'])) {
$params['location'][$locBlock]['email'] = array();
}
/* add a new email block */
$emailBlock = count($params['location'][$locBlock]['email']) + 1;
$params['location'][$locBlock]['email'][$emailBlock] = array();
if (!isset($fields['Email'])) {
$fields['Email'] = CRM_Core_DAO_Email::fields();
}
_crm_store_values($fields['Email'], $values, $params['location'][$locBlock]['email'][$emailBlock]);
if ($emailBlock == 1) {
$params['location'][$locBlock]['email'][$emailBlock]['is_primary'] = true;
}
return true;
}
/* if this is an IM value, create a new block */
if (isset($values['im'])) {
if (!isset($params['location'][$locBlock]['im'])) {
$params['location'][$locBlock]['im'] = array();
}
/* add a new IM block */
$imBlock = count($params['location'][$locBlock]['im']) + 1;
$params['location'][$locBlock]['im'][$imBlock] = array();
if (!isset($fields['IM'])) {
$fields['IM'] = CRM_Core_DAO_IM::fields();
}
_crm_store_values($fields['IM'], $values, $params['location'][$locBlock]['im'][$imBlock]);
if ($imBlock == 1) {
$params['location'][$locBlock]['im'][$imBlock]['is_primary'] = true;
}
return true;
}
/* Otherwise we must be an address */
if (!isset($params['location'][$locBlock]['address'])) {
$params['location'][$locBlock]['address'] = array();
}
if (!isset($fields['Address'])) {
$fields['Address'] = CRM_Core_DAO_Address::fields();
}
_crm_store_values($fields['Address'], $values, $params['location'][$locBlock]['address']);
$ids = array('county', 'country', 'state_province', 'supplemental_address_1', 'supplemental_address_2', 'StateProvince.name');
foreach ($ids as $id) {
if (array_key_exists($id, $values)) {
$params['location'][$locBlock]['address'][$id] = $values[$id];
}
}
return true;
}
if (isset($values['note'])) {
/* add a note field */
if (!isset($params['note'])) {
$params['note'] = array();
}
$noteBlock = count($params['note']) + 1;
$params['note'][$noteBlock] = array();
if (!isset($fields['Note'])) {
$fields['Note'] = CRM_Core_DAO_Note::fields();
}
_crm_store_values($fields['Note'], $values, $params['note'][$noteBlock]);
return true;
}
/* Check for custom field values */
if ($fields['custom'] == null) {
$fields['custom'] =& CRM_Core_BAO_CustomField::getFields($values['contact_type']);
}
foreach ($values as $key => $value) {
if ($customFieldID = CRM_Core_BAO_CustomField::getKeyID($key)) {
/* check if it's a valid custom field id */
if (!array_key_exists($customFieldID, $fields['custom'])) {
return _crm_error('Invalid custom field ID');
}
if (!isset($params['custom'])) {
$params['custom'] = array();
}
// fixed for Import
$newMulValues = array();
if ($fields['custom'][$customFieldID][3] == 'CheckBox' || $fields['custom'][$customFieldID][3] == 'Multi-Select') {
$value = str_replace("|", ",", $value);
$mulValues = explode(',', $value);
$custuomOption = CRM_Core_BAO_CustomOption::getCustomOption($customFieldID, true);
foreach ($mulValues as $v1) {
foreach ($custuomOption as $v2) {
if (strtolower($v2['label']) == strtolower(trim($v1))) {
$newMulValues[] = $v2['value'];
}
}
}
$value = implode(CRM_CORE_BAO_CUSTOMOPTION_VALUE_SEPERATOR, $newMulValues);
} else {
if ($fields['custom'][$customFieldID][3] == 'Select' || $fields['custom'][$customFieldID][3] == 'Radio') {
$custuomOption = CRM_Core_BAO_CustomOption::getCustomOption($customFieldID, true);
foreach ($custuomOption as $v2) {
if (strtolower($v2['label']) == strtolower(trim($value))) {
$value = $v2['value'];
break;
}
}
}
}
$customBlock = count($params['custom']) + 1;
$params['custom'][$customBlock] = array('custom_field_id' => $customFieldID, 'value' => $value, 'type' => $fields['custom'][$customFieldID][2], 'name' => $fields['custom'][$customFieldID][0]);
}
}
/* Finally, check for contact fields */
if (!isset($fields['Contact'])) {
$fields['Contact'] =& CRM_Contact_DAO_Contact::fields();
}
_crm_store_values($fields['Contact'], $values, $params);
}
/**
* 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);
}
}
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;
}
/**
* Update the email value for the contact and user profile.
*
* @param int $contactId
* Contact ID of the user.
* @param string $emailAddress
* Email to be modified for the user.
*/
public static function updateContactEmail($contactId, $emailAddress)
{
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$emailAddress = $strtolower($emailAddress);
$ufmatch = new CRM_Core_DAO_UFMatch();
$ufmatch->contact_id = $contactId;
$ufmatch->domain_id = CRM_Core_Config::domainID();
if ($ufmatch->find(TRUE)) {
// Save the email in UF Match table
$ufmatch->uf_name = $emailAddress;
CRM_Core_BAO_UFMatch::create((array) $ufmatch);
//check if the primary email for the contact exists
//$contactDetails[1] - email
//$contactDetails[3] - email id
$contactDetails = CRM_Contact_BAO_Contact_Location::getEmailDetails($contactId);
if (trim($contactDetails[1])) {
$emailID = $contactDetails[3];
//update if record is found
$query = "UPDATE civicrm_email\n SET email = %1\n WHERE id = %2";
$p = array(1 => array($emailAddress, 'String'), 2 => array($emailID, 'Integer'));
$dao = CRM_Core_DAO::executeQuery($query, $p);
} else {
//else insert a new email record
$email = new CRM_Core_DAO_Email();
$email->contact_id = $contactId;
$email->is_primary = 1;
$email->email = $emailAddress;
$email->save();
$emailID = $email->id;
}
CRM_Core_BAO_Log::register($contactId, 'civicrm_email', $emailID);
}
}
/**
* 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;
}
/**
* 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();
}
}
/**
* combine all the exportable fields from the lower levels object
*
* currentlty we are using importable fields as exportable fields
*
* @param int $contactType contact Type
* $param boolean $status true while exporting primary contacts
* $param boolean $export true when used during export
*
* @return array array of exportable Fields
* @access public
*/
function &exportableFields($contactType = 'Individual', $status = false, $export = false)
{
if (empty($contactType)) {
$contactType = 'All';
}
if (!$GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields'] || !CRM_Utils_Array::value($contactType, $GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields'])) {
if (!$GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields']) {
$GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields'] = array();
}
if (!$status) {
$fields = array();
} else {
$fields = array('' => array('title' => ts('- Contact Fields -')));
}
if ($contactType != 'All') {
require_once str_replace('_', DIRECTORY_SEPARATOR, "CRM_Contact_DAO_" . $contactType) . ".php";
eval('$fields = array_merge($fields, CRM_Contact_DAO_' . $contactType . '::export( ));');
} else {
foreach (array('Individual', 'Household', 'Organization') as $type) {
require_once str_replace('_', DIRECTORY_SEPARATOR, "CRM_Contact_DAO_" . $type) . ".php";
eval('$fields = array_merge($fields, CRM_Contact_DAO_' . $type . '::export( ));');
if ($type == 'Individual') {
$fields = array_merge($fields, CRM_Core_DAO_IndividualPrefix::export(true), CRM_Core_DAO_IndividualSuffix::export(true), CRM_Core_DAO_Gender::export(true));
}
}
}
// the fields are only meant for Individual contact type
if ($contactType == 'Individual') {
$fields = array_merge($fields, CRM_Core_DAO_IndividualPrefix::export(true), CRM_Core_DAO_IndividualSuffix::export(true), CRM_Core_DAO_Gender::export(true));
}
$locationType = array();
if ($status) {
$locationType['location_type'] = array('name' => 'location_type', 'where' => 'civicrm_location_type.name', 'title' => 'Location Type');
}
$IMProvider = array();
if ($status) {
$IMProvider['im_provider'] = array('name' => 'im_provider', 'where' => 'civicrm_im_provider.name', 'title' => 'IM Provider');
}
$locationFields = array_merge($locationType, CRM_Core_DAO_Location::export(), CRM_Core_DAO_Address::export(), CRM_Core_DAO_Phone::export(), CRM_Core_DAO_Email::export(), $IMProvider, CRM_Core_DAO_IM::export(true));
foreach ($locationFields as $key => $field) {
$locationFields[$key]['hasLocationType'] = true;
}
$fields = array_merge($fields, $locationFields);
$fields = array_merge($fields, CRM_Contact_DAO_Contact::export());
$fields = array_merge($fields, CRM_Core_DAO_Note::export());
if ($contactType != 'All') {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($contactType, $status));
} else {
foreach (array('Individual', 'Household', 'Organization') as $type) {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($type));
}
}
//fix for CRM-791
if ($export) {
$fields = array_merge($fields, array('groups' => array('title' => ts('Group(s)'))));
$fields = array_merge($fields, array('tags' => array('title' => ts('Tag(s)'))));
} else {
$fields = array_merge($fields, array('group' => array('title' => ts('Group(s)'))));
$fields = array_merge($fields, array('tag' => array('title' => ts('Tag(s)'))));
}
$GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields'][$contactType] = $fields;
}
return $GLOBALS['_CRM_CONTACT_BAO_CONTACT']['_exportableFields'][$contactType];
}
/**
* Create a new forward event, create a new contact if necessary
*
* @param $job_id
* @param $queue_id
* @param $hash
* @param $forward_email
* @param null $fromEmail
* @param null $comment
*
* @return bool
*/
public static function &forward($job_id, $queue_id, $hash, $forward_email, $fromEmail = NULL, $comment = NULL)
{
$q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
$successfulForward = FALSE;
$contact_id = NULL;
if (!$q) {
return $successfulForward;
}
/* Find the email address/contact, if it exists */
$contact = CRM_Contact_BAO_Contact::getTableName();
$location = CRM_Core_BAO_Location::getTableName();
$email = CRM_Core_BAO_Email::getTableName();
$queueTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$forward = self::getTableName();
$domain = CRM_Core_BAO_Domain::getDomain();
$dao = new CRM_Core_Dao();
$dao->query("\n SELECT {$contact}.id as contact_id,\n {$email}.id as email_id,\n {$contact}.do_not_email as do_not_email,\n {$queueTable}.id as queue_id\n FROM ({$email}, {$job} as temp_job)\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n LEFT JOIN {$queueTable}\n ON {$email}.id = {$queueTable}.email_id\n LEFT JOIN {$job}\n ON {$queueTable}.job_id = {$job}.id\n AND temp_job.mailing_id = {$job}.mailing_id\n WHERE {$queueTable}.job_id = {$job_id}\n AND {$email}.email = '" . CRM_Utils_Type::escape($forward_email, 'String') . "'");
$dao->fetch();
$transaction = new CRM_Core_Transaction();
if (isset($dao->queue_id) || isset($dao->do_not_email) && $dao->do_not_email == 1) {
/* We already sent this mailing to $forward_email, or we should
* never email this contact. Give up. */
return $successfulForward;
}
require_once 'api/api.php';
$contactParams = array('email' => $forward_email, 'version' => 3);
$contactValues = civicrm_api('contact', 'get', $contactParams);
$count = $contactValues['count'];
if ($count == 0) {
/* If the contact does not exist, create one. */
$formatted = array('contact_type' => 'Individual', 'version' => 3);
$locationType = CRM_Core_BAO_LocationType::getDefault();
$value = array('email' => $forward_email, 'location_type_id' => $locationType->id);
require_once 'CRM/Utils/DeprecatedUtils.php';
_civicrm_api3_deprecated_add_formatted_param($value, $formatted);
$formatted['onDuplicate'] = CRM_Import_Parser::DUPLICATE_SKIP;
$formatted['fixAddress'] = TRUE;
$contact = civicrm_api('contact', 'create', $formatted);
if (civicrm_error($contact)) {
return $successfulForward;
}
$contact_id = $contact['id'];
}
$email = new CRM_Core_DAO_Email();
$email->email = $forward_email;
$email->find(TRUE);
$email_id = $email->id;
if (!$contact_id) {
$contact_id = $email->contact_id;
}
/* Create a new queue event */
$queue_params = array('email_id' => $email_id, 'contact_id' => $contact_id, 'job_id' => $job_id);
$queue = CRM_Mailing_Event_BAO_Queue::create($queue_params);
$forward = new CRM_Mailing_Event_BAO_Forward();
$forward->time_stamp = date('YmdHis');
$forward->event_queue_id = $queue_id;
$forward->dest_queue_id = $queue->id;
$forward->save();
$dao->reset();
$dao->query(" SELECT {$job}.mailing_id as mailing_id\n FROM {$job}\n WHERE {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
$dao->fetch();
$mailing_obj = new CRM_Mailing_BAO_Mailing();
$mailing_obj->id = $dao->mailing_id;
$mailing_obj->find(TRUE);
$config = CRM_Core_Config::singleton();
$mailer = $config->getMailer();
$recipient = NULL;
$attachments = NULL;
$message = $mailing_obj->compose($job_id, $queue->id, $queue->hash, $queue->contact_id, $forward_email, $recipient, FALSE, NULL, $attachments, TRUE, $fromEmail);
//append comment if added while forwarding.
if (count($comment)) {
$message->_txtbody = CRM_Utils_Array::value('body_text', $comment) . $message->_txtbody;
if (!empty($comment['body_html'])) {
$message->_htmlbody = $comment['body_html'] . '<br />---------------Original message---------------------<br />' . $message->_htmlbody;
}
}
$body = $message->get();
$headers = $message->headers();
$result = NULL;
if (is_object($mailer)) {
$errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
$result = $mailer->send($recipient, $headers, $body);
unset($errorScope);
}
$params = array('event_queue_id' => $queue->id, 'job_id' => $job_id, 'hash' => $queue->hash);
if (is_a($result, 'PEAR_Error')) {
/* Register the bounce event */
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
$successfulForward = TRUE;
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
$transaction->commit();
return $successfulForward;
}
/**
* Combine all the exportable fields from the lower levels object.
*
* Currently we are using importable fields as exportable fields
*
* @param int|string $contactType contact Type
* @param bool $status
* True while exporting primary contacts.
* @param bool $export
* True when used during export.
* @param bool $search
* True when used during search, might conflict with export param?.
*
* @param bool $withMultiRecord
*
* @return array
* array of exportable Fields
*/
public static function &exportableFields($contactType = 'Individual', $status = FALSE, $export = FALSE, $search = FALSE, $withMultiRecord = FALSE)
{
if (empty($contactType)) {
$contactType = 'All';
}
$cacheKeyString = "exportableFields {$contactType}";
$cacheKeyString .= $export ? '_1' : '_0';
$cacheKeyString .= $status ? '_1' : '_0';
$cacheKeyString .= $search ? '_1' : '_0';
//CRM-14501 it turns out that the impact of permissioning here is sometimes inconsistent. The field that
//calculates custom fields takes into account the logged in user & caches that for all users
//as an interim fix we will cache the fields by contact
$cacheKeyString .= '_' . CRM_Core_Session::getLoggedInContactID();
if (!self::$_exportableFields || !CRM_Utils_Array::value($cacheKeyString, self::$_exportableFields)) {
if (!self::$_exportableFields) {
self::$_exportableFields = array();
}
// check if we can retrieve from database cache
$fields = CRM_Core_BAO_Cache::getItem('contact fields', $cacheKeyString);
if (!$fields) {
$fields = CRM_Contact_DAO_Contact::export();
// The fields are meant for contact types.
if (in_array($contactType, array('Individual', 'Household', 'Organization', 'All'))) {
$fields = array_merge($fields, CRM_Core_OptionValue::getFields('', $contactType));
}
// add current employer for individuals
$fields = array_merge($fields, array('current_employer' => array('name' => 'organization_name', 'title' => ts('Current Employer'))));
$locationType = array('location_type' => array('name' => 'location_type', 'where' => 'civicrm_location_type.name', 'title' => ts('Location Type')));
$IMProvider = array('im_provider' => array('name' => 'im_provider', 'where' => 'civicrm_im.provider_id', 'title' => ts('IM Provider')));
$locationFields = array_merge($locationType, CRM_Core_DAO_Address::export(), CRM_Core_DAO_Phone::export(), CRM_Core_DAO_Email::export(), $IMProvider, CRM_Core_DAO_IM::export(TRUE), CRM_Core_DAO_OpenID::export());
$locationFields = array_merge($locationFields, CRM_Core_BAO_CustomField::getFieldsForImport('Address'));
foreach ($locationFields as $key => $field) {
$locationFields[$key]['hasLocationType'] = TRUE;
}
$fields = array_merge($fields, $locationFields);
//add world region
$fields = array_merge($fields, CRM_Core_DAO_Worldregion::export());
$fields = array_merge($fields, CRM_Contact_DAO_Contact::export());
//website fields
$fields = array_merge($fields, CRM_Core_DAO_Website::export());
if ($contactType != 'All') {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($contactType, $status, FALSE, $search, TRUE, $withMultiRecord));
} else {
foreach (array('Individual', 'Household', 'Organization') as $type) {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($type, FALSE, FALSE, $search, TRUE, $withMultiRecord));
}
}
//fix for CRM-791
if ($export) {
$fields = array_merge($fields, array('groups' => array('title' => ts('Group(s)'), 'name' => 'groups'), 'tags' => array('title' => ts('Tag(s)'), 'name' => 'tags'), 'notes' => array('title' => ts('Note(s)'), 'name' => 'notes')));
} else {
$fields = array_merge($fields, array('group' => array('title' => ts('Group(s)'), 'name' => 'group'), 'tag' => array('title' => ts('Tag(s)'), 'name' => 'tag'), 'note' => array('title' => ts('Note(s)'), 'name' => 'note')));
}
//Sorting fields in alphabetical order(CRM-1507)
foreach ($fields as $k => $v) {
$sortArray[$k] = CRM_Utils_Array::value('title', $v);
}
$fields = array_merge($sortArray, $fields);
//unset the field which are not related to their contact type.
if ($contactType != 'All') {
$commonValues = array('Individual' => array('household_name', 'legal_name', 'sic_code', 'organization_name', 'email_greeting_custom', 'postal_greeting_custom', 'addressee_custom'), 'Household' => array('first_name', 'middle_name', 'last_name', 'formal_title', 'job_title', 'gender_id', 'prefix_id', 'suffix_id', 'birth_date', 'organization_name', 'legal_name', 'legal_identifier', 'sic_code', 'home_URL', 'is_deceased', 'deceased_date', 'current_employer', 'email_greeting_custom', 'postal_greeting_custom', 'addressee_custom', 'prefix_id', 'suffix_id'), 'Organization' => array('first_name', 'middle_name', 'last_name', 'formal_title', 'job_title', 'gender_id', 'prefix_id', 'suffix_id', 'birth_date', 'household_name', 'email_greeting_custom', 'postal_greeting_custom', 'prefix_id', 'suffix_id', 'gender_id', 'addressee_custom', 'is_deceased', 'deceased_date', 'current_employer'));
foreach ($commonValues[$contactType] as $value) {
unset($fields[$value]);
}
}
CRM_Core_BAO_Cache::setItem($fields, 'contact fields', $cacheKeyString);
}
self::$_exportableFields[$cacheKeyString] = $fields;
}
if (!$status) {
$fields = self::$_exportableFields[$cacheKeyString];
} else {
$fields = array_merge(array('' => array('title' => ts('- Contact Fields -'))), self::$_exportableFields[$cacheKeyString]);
}
return $fields;
}
/**
* returns the list of fields that can be exported
*
* @access public
* return array
*/
function &export($prefix = false)
{
if (!$GLOBALS['_CRM_CORE_DAO_EMAIL']['_export']) {
$GLOBALS['_CRM_CORE_DAO_EMAIL']['_export'] = array();
$fields =& CRM_Core_DAO_Email::fields();
foreach ($fields as $name => $field) {
if (CRM_Utils_Array::value('export', $field)) {
if ($prefix) {
$GLOBALS['_CRM_CORE_DAO_EMAIL']['_export']['email'] =& $fields[$name];
} else {
$GLOBALS['_CRM_CORE_DAO_EMAIL']['_export'][$name] =& $fields[$name];
}
}
}
}
return $GLOBALS['_CRM_CORE_DAO_EMAIL']['_export'];
}
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();
}
}
/**
* combine all the exportable fields from the lower levels object
*
* currentlty we are using importable fields as exportable fields
*
* @param int $contactType contact Type
* $param boolean $status true while exporting primary contacts
* $param boolean $export true when used during export
*
* @return array array of exportable Fields
* @access public
*/
function &exportableFields($contactType = 'Individual', $status = false, $export = false)
{
if (empty($contactType)) {
$contactType = 'All';
}
$cacheKeyString = "exportableFields {$contactType}";
$cacheKeyString .= $export ? "_1" : "_0";
$cacheKeyString .= $status ? "_1" : "_0";
if (!self::$_exportableFields || !CRM_Utils_Array::value($cacheKeyString, self::$_exportableFields)) {
if (!self::$_exportableFields) {
self::$_exportableFields = array();
}
// check if we can retrieve from database cache
require_once 'CRM/Core/BAO/Cache.php';
$fields =& CRM_Core_BAO_Cache::getItem('contact fields', $cacheKeyString);
if (!$fields) {
$fields = array();
$fields = array_merge($fields, CRM_Contact_DAO_Contact::export());
// the fields are meant for contact types
if (in_array($contactType, array('Individual', 'Household', 'Organization', 'All'))) {
require_once 'CRM/Core/OptionValue.php';
$fields = array_merge($fields, CRM_Core_OptionValue::getFields('', $contactType));
}
// add current employer for individuals
$fields = array_merge($fields, array('current_employer' => array('name' => 'organization_name', 'title' => ts('Current Employer'))));
$locationType = array();
if ($status) {
$locationType['location_type'] = array('name' => 'location_type', 'where' => 'civicrm_location_type.name', 'title' => ts('Location Type'));
}
$IMProvider = array();
if ($status) {
$IMProvider['im_provider'] = array('name' => 'im_provider', 'where' => 'im_provider.name', 'title' => ts('IM Provider'));
}
$locationFields = array_merge($locationType, CRM_Core_DAO_Address::export(), CRM_Core_DAO_Phone::export(), CRM_Core_DAO_Email::export(), $IMProvider, CRM_Core_DAO_IM::export(true), CRM_Core_DAO_OpenID::export());
foreach ($locationFields as $key => $field) {
$locationFields[$key]['hasLocationType'] = true;
}
$fields = array_merge($fields, $locationFields);
//add world region
require_once "CRM/Core/DAO/Worldregion.php";
$fields = array_merge($fields, CRM_Core_DAO_Worldregion::export());
$fields = array_merge($fields, CRM_Contact_DAO_Contact::export());
if ($contactType != 'All') {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($contactType, $status, true));
} else {
foreach (array('Individual', 'Household', 'Organization') as $type) {
$fields = array_merge($fields, CRM_Core_BAO_CustomField::getFieldsForImport($type));
}
}
//fix for CRM-791
if ($export) {
$fields = array_merge($fields, array('groups' => array('title' => ts('Group(s)')), 'tags' => array('title' => ts('Tag(s)')), 'notes' => array('title' => ts('Note(s)'))));
} else {
$fields = array_merge($fields, array('group' => array('title' => ts('Group(s)')), 'tag' => array('title' => ts('Tag(s)')), 'note' => array('title' => ts('Note(s)'))));
}
//Sorting fields in alphabetical order(CRM-1507)
foreach ($fields as $k => $v) {
$sortArray[$k] = CRM_Utils_Array::value('title', $v);
}
$fields = array_merge($sortArray, $fields);
//unset the field which are not related to their contact type.
if ($contactType != 'All') {
$commonValues = array('Individual' => array('household_name', 'legal_name', 'sic_code', 'organization_name', 'email_greeting_custom', 'postal_greeting_custom', 'addressee_custom'), 'Household' => array('first_name', 'middle_name', 'last_name', 'job_title', 'gender_id', 'birth_date', 'organization_name', 'legal_name', 'legal_identifier', 'sic_code', 'home_URL', 'is_deceased', 'deceased_date', 'current_employer', 'email_greeting_custom', 'postal_greeting_custom', 'addressee_custom', 'individual_prefix', 'individual_suffix', 'gender'), 'Organization' => array('first_name', 'middle_name', 'last_name', 'job_title', 'gender_id', 'birth_date', 'household_name', 'email_greeting', 'postal_greeting', 'email_greeting_custom', 'postal_greeting_custom', 'individual_prefix', 'individual_suffix', 'gender', 'addressee_custom', 'is_deceased', 'deceased_date', 'current_employer'));
foreach ($commonValues[$contactType] as $value) {
unset($fields[$value]);
}
}
CRM_Core_BAO_Cache::setItem($fields, 'contact fields', $cacheKeyString);
}
self::$_exportableFields[$cacheKeyString] = $fields;
}
if (!$status) {
$fields = self::$_exportableFields[$cacheKeyString];
} else {
$fields = array_merge(array('' => array('title' => ts('- Contact Fields -'))), self::$_exportableFields[$cacheKeyString]);
}
return $fields;
}
/**
* 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();
}
/**
* 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 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);
}
}
/**
* Returns the list of fields that can be exported
*
* @param bool $prefix
*
* @return array
*/
static function &export($prefix = false)
{
if (!self::$_export) {
self::$_export = array();
$fields = self::fields();
foreach ($fields as $name => $field) {
if (CRM_Utils_Array::value('export', $field)) {
if ($prefix) {
self::$_export['email'] =& $fields[$name];
} else {
self::$_export[$name] =& $fields[$name];
}
}
}
}
return self::$_export;
}
/**
* 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();
}
