/** * class constructor */ function __construct() { parent::__construct(); }
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; }
/** * class constructor */ function CRM_Mailing_Event_BAO_Bounce() { parent::CRM_Mailing_Event_DAO_Bounce(); }
/** * returns the list of fields that can be exported * * @access public * return array */ function &export($prefix = false) { if (!$GLOBALS['_CRM_MAILING_EVENT_DAO_BOUNCE']['_export']) { $GLOBALS['_CRM_MAILING_EVENT_DAO_BOUNCE']['_export'] = array(); $fields =& CRM_Mailing_Event_DAO_Bounce::fields(); foreach ($fields as $name => $field) { if (CRM_Utils_Array::value('export', $field)) { if ($prefix) { $GLOBALS['_CRM_MAILING_EVENT_DAO_BOUNCE']['_export']['mailing_event_bounce'] =& $fields[$name]; } else { $GLOBALS['_CRM_MAILING_EVENT_DAO_BOUNCE']['_export'][$name] =& $fields[$name]; } } } } return $GLOBALS['_CRM_MAILING_EVENT_DAO_BOUNCE']['_export']; }
/** * 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 null $offset * @param null $limit * @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, $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(); $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 = 0\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 = 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 {$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 = 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 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 = 0\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 = "\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 {$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 = 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; }
/** * returns the list of fields that can be exported * * @access public * return array */ 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['mailing_event_bounce'] =& $fields[$name]; } else { self::$_export[$name] =& $fields[$name]; } } } } return self::$_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; }
/** * 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; }