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;
}
