/**
* 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 the mailing.
*
* @param object $mailer
* A Mail object to send the messages.
*
* @param array $testParams
*
* @return void
*/
public function deliver(&$mailer, $testParams = NULL)
{
$mailing = new CRM_Mailing_BAO_Mailing();
$mailing->id = $this->mailing_id;
$mailing->find(TRUE);
$mailing->free();
$eq = new CRM_Mailing_Event_BAO_Queue();
$eqTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$emailTable = CRM_Core_BAO_Email::getTableName();
$phoneTable = CRM_Core_DAO_Phone::getTableName();
$contactTable = CRM_Contact_BAO_Contact::getTableName();
$edTable = CRM_Mailing_Event_BAO_Delivered::getTableName();
$ebTable = CRM_Mailing_Event_BAO_Bounce::getTableName();
$query = " SELECT {$eqTable}.id,\n {$emailTable}.email as email,\n {$eqTable}.contact_id,\n {$eqTable}.hash,\n NULL as phone\n FROM {$eqTable}\n INNER JOIN {$emailTable}\n ON {$eqTable}.email_id = {$emailTable}.id\n INNER JOIN {$contactTable}\n ON {$contactTable}.id = {$emailTable}.contact_id\n LEFT JOIN {$edTable}\n ON {$eqTable}.id = {$edTable}.event_queue_id\n LEFT JOIN {$ebTable}\n ON {$eqTable}.id = {$ebTable}.event_queue_id\n WHERE {$eqTable}.job_id = " . $this->id . "\n AND {$edTable}.id IS null\n AND {$ebTable}.id IS null\n AND {$contactTable}.is_opt_out = 0";
if ($mailing->sms_provider_id) {
$query = "\n SELECT {$eqTable}.id,\n {$phoneTable}.phone as phone,\n {$eqTable}.contact_id,\n {$eqTable}.hash,\n NULL as email\n FROM {$eqTable}\n INNER JOIN {$phoneTable}\n ON {$eqTable}.phone_id = {$phoneTable}.id\n INNER JOIN {$contactTable}\n ON {$contactTable}.id = {$phoneTable}.contact_id\n LEFT JOIN {$edTable}\n ON {$eqTable}.id = {$edTable}.event_queue_id\n LEFT JOIN {$ebTable}\n ON {$eqTable}.id = {$ebTable}.event_queue_id\n WHERE {$eqTable}.job_id = " . $this->id . "\n AND {$edTable}.id IS null\n AND {$ebTable}.id IS null\n AND ( {$contactTable}.is_opt_out = 0\n OR {$contactTable}.do_not_sms = 0 )";
}
$eq->query($query);
$config = NULL;
if ($config == NULL) {
$config = CRM_Core_Config::singleton();
}
$job_date = CRM_Utils_Date::isoToMysql($this->scheduled_date);
$fields = array();
if (!empty($testParams)) {
$mailing->subject = ts('[CiviMail Draft]') . ' ' . $mailing->subject;
}
CRM_Mailing_BAO_Mailing::tokenReplace($mailing);
// get and format attachments
$attachments = CRM_Core_BAO_File::getEntityFile('civicrm_mailing', $mailing->id);
if (defined('CIVICRM_MAIL_SMARTY') && CIVICRM_MAIL_SMARTY) {
CRM_Core_Smarty::registerStringResource();
}
// CRM-12376
// This handles the edge case scenario where all the mails
// have been delivered in prior jobs
$isDelivered = TRUE;
// make sure that there's no more than $config->mailerBatchLimit mails processed in a run
while ($eq->fetch()) {
// if ( ( $mailsProcessed % 100 ) == 0 ) {
// CRM_Utils_System::xMemory( "$mailsProcessed: " );
// }
if ($config->mailerBatchLimit > 0 && self::$mailsProcessed >= $config->mailerBatchLimit) {
if (!empty($fields)) {
$this->deliverGroup($fields, $mailing, $mailer, $job_date, $attachments);
}
$eq->free();
return FALSE;
}
self::$mailsProcessed++;
$fields[] = array('id' => $eq->id, 'hash' => $eq->hash, 'contact_id' => $eq->contact_id, 'email' => $eq->email, 'phone' => $eq->phone);
if (count($fields) == self::MAX_CONTACTS_TO_PROCESS) {
$isDelivered = $this->deliverGroup($fields, $mailing, $mailer, $job_date, $attachments);
if (!$isDelivered) {
$eq->free();
return $isDelivered;
}
$fields = array();
}
}
$eq->free();
if (!empty($fields)) {
$isDelivered = $this->deliverGroup($fields, $mailing, $mailer, $job_date, $attachments);
}
return $isDelivered;
}
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;
}
