PHP CRM_Contact_BAO_Query::fromClause方法代码示例

 /**
  * Get from where email (whatever that means!).
  *
  * @param int $id
  *
  * @return array
  */
 public static function fromWhereEmail($id)
 {
     $params = self::getSearchParams($id);
     if ($params) {
         if (!empty($params['customSearchID'])) {
             return CRM_Contact_BAO_SearchCustom::fromWhereEmail(NULL, $id);
         } else {
             $tables = $whereTables = array('civicrm_contact' => 1, 'civicrm_email' => 1);
             $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
             $from = CRM_Contact_BAO_Query::fromClause($whereTables);
             return array($from, $where);
         }
     } else {
         // fix for CRM-7240
         $from = "\nFROM      civicrm_contact contact_a\nLEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)\n";
         $where = " ( 1 ) ";
         $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
         $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
         return array($from, $where);
     }
 }

 /**
  * Fill the acl contact cache for this contact id if empty.
  *
  * @param int $userID
  * @param int|string $type the type of operation (view|edit)
  * @param bool $force
  *   Should we force a recompute.
  */
 public static function cache($userID, $type = CRM_Core_Permission::VIEW, $force = FALSE)
 {
     static $_processed = array();
     if ($type = CRM_Core_Permission::VIEW) {
         $operationClause = " operation IN ( 'Edit', 'View' ) ";
         $operation = 'View';
     } else {
         $operationClause = " operation = 'Edit' ";
         $operation = 'Edit';
     }
     if (!$force) {
         if (!empty($_processed[$userID])) {
             return;
         }
         // run a query to see if the cache is filled
         $sql = "\nSELECT count(id)\nFROM   civicrm_acl_contact_cache\nWHERE  user_id = %1\nAND    {$operationClause}\n";
         $params = array(1 => array($userID, 'Integer'));
         $count = CRM_Core_DAO::singleValueQuery($sql, $params);
         if ($count > 0) {
             $_processed[$userID] = 1;
             return;
         }
     }
     $tables = array();
     $whereTables = array();
     $permission = CRM_ACL_API::whereClause($type, $tables, $whereTables, $userID);
     $from = CRM_Contact_BAO_Query::fromClause($whereTables);
     CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_acl_contact_cache ( user_id, contact_id, operation )\nSELECT      {$userID} as user_id, contact_a.id as contact_id, '{$operation}' as operation\n         {$from}\nWHERE    {$permission}\nGROUP BY contact_a.id\nON DUPLICATE KEY UPDATE\n         user_id=VALUES(user_id),\n         contact_id=VALUES(contact_id),\n         operation=VALUES(operation)");
     $_processed[$userID] = 1;
 }

 /**
  * Returns array of contacts who are members of the specified group.
  *
  * @param CRM_Contact $group                A valid group object (passed by reference)
  * @param array       $returnProperties     Which properties
  *                    should be included in the returned Contact object(s). If NULL,
  *                    the default set of contact properties will be
  *                    included. group_contact properties (such as 'status',
  * '                  in_date', etc.) are included automatically.Note:Do not inclue
  *                    Id releted properties.  
  * @param text        $status               A valid status value ('Added', 'Pending', 'Removed').
  * @param text        $sort                 Associative array of
  *                    one or more "property_name"=>"sort direction"
  *                    pairs which will control order of Contact objects returned.
  * @param Int         $offset               Starting row index.
  * @param Int         $row_count            Maximum number of rows to returns.
  *
  *
  * @return            $contactArray         Array of contacts who are members of the specified group
  *
  * @access public
  */
 static function getGroupContacts(&$group, $returnProperties = null, $status = 'Added', $sort = null, $offset = null, $row_count = null, $includeChildGroups = false)
 {
     $groupDAO =& new CRM_Contact_DAO_Group();
     $groupDAO->id = $group->id;
     if (!$groupDAO->find(true)) {
         return CRM_Core_Error::createError("Could not locate group with id: {$id}");
     }
     // make sure user has got permission to view this group
     require_once 'CRM/Contact/BAO/Group.php';
     if (!CRM_Contact_BAO_Group::checkPermission($groupDAO->id, $groupDAO->title)) {
         return CRM_Core_Error::createError("You do not have permission to access group with id: {$id}");
     }
     $query = '';
     if (empty($returnProperties)) {
         $query = "SELECT contact_a.id as contact_id,\n                      civicrm_email.email as email";
     } else {
         $query = "SELECT contact_a.id as contact_id , {$grpStatus} as status,";
         $query .= implode(',', $returnProperties);
     }
     $params = array();
     if ($includeChildGroups) {
         require_once 'CRM/Contact/BAO/GroupNesting.php';
         $groupIds = CRM_Contact_BAO_GroupNesting::getDescendentGroupIds(array($group->id));
     } else {
         $groupIds = array($group->id);
     }
     foreach ($groupIds as $groupId) {
         $params[] = array('group', 'IN', array($group->id => true), 0, 0);
     }
     require_once 'CRM/Core/BAO/Email.php';
     require_once 'CRM/Contact/BAO/Contact.php';
     $tables = array(CRM_Core_BAO_Email::getTableName() => true, CRM_Contact_BAO_Contact::getTableName() => true);
     $inner = array();
     $whereTables = array();
     $where = CRM_Contact_BAO_Query::getWhereClause($params, null, $tables, $whereTables);
     $permission = CRM_Core_Permission::whereClause(CRM_Core_Permission::VIEW, $tables, $whereTables);
     $from = CRM_Contact_BAO_Query::fromClause($tables, $inner);
     $query .= " {$from} WHERE {$permission} AND {$where} ";
     if ($sort != null) {
         $order = array();
         foreach ($sort as $key => $direction) {
             $order[] = " {$key} {$direction} ";
         }
         $query .= " ORDER BY " . implode(',', $order);
     }
     if (!is_null($offset) && !is_null($row_count)) {
         $query .= " LIMIT {$offset}, {$row_count}";
     }
     $dao =& new CRM_Contact_DAO_Contact();
     $dao->query($query);
     // this is quite inefficient, we need to change the return
     // values in docs
     $contactArray = array();
     while ($dao->fetch()) {
         $contactArray[] = clone $dao;
     }
     return $contactArray;
 }

 /**
  * Get the list of groups for contact based on status of group membership.
  *
  * @param int $contactId
  *   Contact id.
  * @param string $status
  *   State of membership.
  * @param int $numGroupContact
  *   Number of groups for a contact that should be shown.
  * @param bool $count
  *   True if we are interested only in the count.
  * @param bool $ignorePermission
  *   True if we should ignore permissions for the current user.
  *                                   useful in profile where permissions are limited for the user. If left
  *                                   at false only groups viewable by the current user are returned
  * @param bool $onlyPublicGroups
  *   True if we want to hide system groups.
  *
  * @param bool $excludeHidden
  *
  * @return array (reference)|int $values
  *   the relevant data object values for the contact or
  *   the total count when $count is TRUE
  */
 public static function &getContactGroup($contactId, $status = NULL, $numGroupContact = NULL, $count = FALSE, $ignorePermission = FALSE, $onlyPublicGroups = FALSE, $excludeHidden = TRUE, $groupId = NULL)
 {
     if ($count) {
         $select = 'SELECT count(DISTINCT civicrm_group_contact.id)';
     } else {
         $select = 'SELECT
                 civicrm_group_contact.id as civicrm_group_contact_id,
                 civicrm_group.title as group_title,
                 civicrm_group.visibility as visibility,
                 civicrm_group_contact.status as status,
                 civicrm_group.id as group_id,
                 civicrm_group.is_hidden as is_hidden,
                 civicrm_subscription_history.date as date,
                 civicrm_subscription_history.method as method';
     }
     $where = " WHERE contact_a.id = %1 AND civicrm_group.is_active = 1";
     if ($excludeHidden) {
         $where .= " AND civicrm_group.is_hidden = 0 ";
     }
     $params = array(1 => array($contactId, 'Integer'));
     if (!empty($status)) {
         $where .= ' AND civicrm_group_contact.status = %2';
         $params[2] = array($status, 'String');
     }
     if (!empty($groupId)) {
         $where .= " AND civicrm_group.id = %3 ";
         $params[3] = array($groupId, 'Integer');
     }
     $tables = array('civicrm_group_contact' => 1, 'civicrm_group' => 1, 'civicrm_subscription_history' => 1);
     $whereTables = array();
     if ($ignorePermission) {
         $permission = ' ( 1 ) ';
     } else {
         $permission = CRM_Core_Permission::getPermissionedStaticGroupClause(CRM_Core_Permission::VIEW, $tables, $whereTables);
     }
     $from = CRM_Contact_BAO_Query::fromClause($tables);
     //CRM-16945: seems hackish but as per CRM-16483 of using group criteria for Search Builder it is mandatory
     //to include group_contact_cache clause when group table is present, so following code remove duplicacy
     $from = str_replace("OR civicrm_group.id = civicrm_group_contact_cache.group_id", 'AND civicrm_group.saved_search_id IS NULL', $from);
     $where .= " AND {$permission} ";
     if ($onlyPublicGroups) {
         $where .= " AND civicrm_group.visibility != 'User and User Admin Only' ";
     }
     $order = $limit = '';
     if (!$count) {
         $order = ' ORDER BY civicrm_group.title, civicrm_subscription_history.date ASC';
         if ($numGroupContact) {
             $limit = " LIMIT 0, {$numGroupContact}";
         }
     }
     $sql = $select . $from . $where . $order . $limit;
     if ($count) {
         $result = CRM_Core_DAO::singleValueQuery($sql, $params);
         return $result;
     } else {
         $dao = CRM_Core_DAO::executeQuery($sql, $params);
         $values = array();
         while ($dao->fetch()) {
             $id = $dao->civicrm_group_contact_id;
             $values[$id]['id'] = $id;
             $values[$id]['group_id'] = $dao->group_id;
             $values[$id]['title'] = $dao->group_title;
             $values[$id]['visibility'] = $dao->visibility;
             $values[$id]['is_hidden'] = $dao->is_hidden;
             switch ($dao->status) {
                 case 'Added':
                     $prefix = 'in_';
                     break;
                 case 'Removed':
                     $prefix = 'out_';
                     break;
                 default:
                     $prefix = 'pending_';
             }
             $values[$id][$prefix . 'date'] = $dao->date;
             $values[$id][$prefix . 'method'] = $dao->method;
//.........这里部分代码省略.........

 static function fromWhereEmail($id)
 {
     $params =& self::getSearchParams($id);
     if ($params) {
         if (CRM_Utils_Array::value('customSearchID', $params)) {
             require_once 'CRM/Contact/BAO/SearchCustom.php';
             return CRM_Contact_BAO_SearchCustom::fromWhereEmail(null, $id);
         } else {
             $tables = $whereTables = array('civicrm_contact' => 1, 'civicrm_email' => 1);
             $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
             $from = CRM_Contact_BAO_Query::fromClause($whereTables);
             return array($from, $where);
         }
     } else {
         // fix for CRM-7240
         $from = "\nFROM      civicrm_contact contact_a \nLEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)\n";
         $where = " ( 1 ) ";
         $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
         $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
         return array($from, $where);
     }
 }

 /**
  * fill the acl contact cache for this contact id if empty
  *
  * @param int     $id     contact id
  * @param string  $type   the type of operation (view|edit)
  * @param boolean $force  should we force a recompute
  *
  * @return void
  * @access public
  * @static
  */
 static function cache($userID, $type = CRM_Core_Permission::VIEW, $force = false)
 {
     static $_processed = array();
     if ($type = CRM_Core_Permission::VIEW) {
         $operationClause = " operation IN ( 'Edit', 'View' ) ";
         $operation = 'View';
     } else {
         $operationClause = " operation = 'Edit' ";
         $operation = 'Edit';
     }
     if (!$force) {
         if (CRM_Utils_Array::value($userID, $_processed)) {
             return;
         }
         // run a query to see if the cache is filled
         $sql = "\nSELECT count(id)\nFROM   civicrm_acl_contact_cache\nWHERE  user_id = %1\nAND    {$operationClause}\n";
         $params = array(1 => array($userID, 'Integer'));
         $count = CRM_Core_DAO::singleValueQuery($sql, $params);
         if ($count > 0) {
             $_processed[$userID] = 1;
             return;
         }
     }
     $tables = array();
     $whereTables = array();
     require_once 'CRM/ACL/API.php';
     $permission = CRM_ACL_API::whereClause($type, $tables, $whereTables, $userID);
     require_once "CRM/Contact/BAO/Query.php";
     $from = CRM_Contact_BAO_Query::fromClause($whereTables);
     $query = "\nSELECT DISTINCT(contact_a.id) as id\n       {$from}\nWHERE {$permission}\n";
     $values = array();
     $dao = CRM_Core_DAO::executeQuery($query);
     while ($dao->fetch()) {
         $values[] = "( {$userID}, {$dao->id}, '{$operation}' )";
     }
     // now store this in the table
     while (!empty($values)) {
         $processed = true;
         $input = array_splice($values, 0, self::NUM_CONTACTS_TO_INSERT);
         $str = implode(',', $input);
         $sql = "REPLACE INTO civicrm_acl_contact_cache ( user_id, contact_id, operation ) VALUES {$str};";
         CRM_Core_DAO::executeQuery($sql);
     }
     CRM_Core_DAO::executeQuery('DELETE FROM civicrm_acl_contact_cache WHERE contact_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)');
     $_processed[$userID] = 1;
     return;
 }

 /**
  * Returns array of contacts who are members of the specified group.
  *
  * @param CRM_Contact $group                A valid group object (passed by reference)
  * @param array       $returnProperties     Which properties
  *                    should be included in the returned Contact object(s). If NULL,
  *                    the default set of contact properties will be
  *                    included. group_contact properties (such as 'status',
  * '                  in_date', etc.) are included automatically.Note:Do not inclue
  *                    Id releted properties.  
  * @param text        $status               A valid status value ('Added', 'Pending', 'Removed').
  * @param text        $sort                 Associative array of
  *                    one or more "property_name"=>"sort direction"
  *                    pairs which will control order of Contact objects returned.
  * @param Int         $offset               Starting row index.
  * @param Int         $row_count            Maximum number of rows to returns.
  *
  *
  * @return            $contactArray         Array of contacts who are members of the specified group
  *
  * @access public
  */
 function getGroupContacts(&$group, $returnProperties = null, $status = 'Added', $sort = null, $offset = null, $row_count = null)
 {
     $query = "SELECT * FROM civicrm_group WHERE id = " . CRM_Utils_Type::escape($group->id, 'Integer');
     $groupDAO =& new CRM_Contact_DAO_Group();
     $groupDAO->id = $group->id;
     if (!$groupDAO->find(true)) {
         return CRM_Core_Error::createError("Could not locate group with id: {$id}");
     }
     // make sure user has got permission to view this group
     if (!CRM_Contact_BAO_Group::checkPermission($groupDAO->id, $groupDAO->title)) {
         return CRM_Core_Error::createError("You do not have permission to access group with id: {$id}");
     }
     $query = '';
     if (empty($returnProperties)) {
         $query = "SELECT civicrm_contact.id as contact_id,\n                      civicrm_email.email as email";
         //$query = "SELECT *,civicrm_contact.id as contact_id, (talk to lobo before re-enabling this)
         //civicrm_email.email as email";
     } else {
         $query = "SELECT civicrm_contact.id as contact_id ,";
         $query .= implode(',', $returnProperties);
     }
     $fv = array('group' => array($group->id => true));
     if ($status) {
         $fv['group_contact_status'] = array($status => true);
     } else {
         $fv['group_contact_status'] = array('Added' => true, 'Removed' => true, 'Pending' => true);
     }
     $tables = array(CRM_Contact_BAO_GroupContact::getTableName() => true, CRM_Core_BAO_Email::getTableName() => true, CRM_Contact_BAO_Contact::getTableName() => true, CRM_Contact_BAO_Group::getTableName() => true);
     $inner = array();
     $whereTables = array();
     $where = CRM_Contact_BAO_Query::getWhereClause($fv, null, $tables, $whereTables);
     $permission = CRM_Core_Permission::whereClause(CRM_CORE_PERMISSION_VIEW, $tables, $whereTables);
     $from = CRM_Contact_BAO_Query::fromClause($tables, $inner);
     $query .= " {$from} WHERE {$permission} AND {$where} ";
     if ($sort != null) {
         $order = array();
         foreach ($sort as $key => $direction) {
             $order[] = " {$key} {$direction} ";
         }
         $query .= " ORDER BY " . implode(',', $order);
     }
     if ($offset != null && $row_count != null) {
         $query .= " LIMIT {$offset}, {$row_count}";
     }
     // CRM_Core_Error::debug( 'q', $query );
     $dao =& new CRM_Contact_DAO_Contact();
     $dao->query($query);
     // this is quite inefficient, we need to change the return
     // values in docs
     $contactArray = array();
     while ($dao->fetch()) {
         $contactArray[] = clone $dao;
     }
     return $contactArray;
 }

 /**
  * create and query the db for an contact search
  *
  * @param int      $offset   the offset for the query
  * @param int      $rowCount the number of rows to return
  * @param string   $sort     the order by string
  * @param boolean  $count    is this a count only query ?
  * @param boolean  $includeContactIds should we include contact ids?
  * @param boolean  $sortByChar if true returns the distinct array of first characters for search results
  * @param boolean  $groupContacts if true, use a single mysql group_concat statement to get the contact ids
  * @param boolean  $returnQuery   should we return the query as a string
  * @param string   $additionalWhereClause if the caller wants to further restrict the search (used in contributions)
  *
  * @return CRM_Contact_DAO_Contact 
  * @access public
  */
 function searchQuery($offset = 0, $rowCount = 0, $sort = null, $count = false, $includeContactIds = false, $sortByChar = false, $groupContacts = false, $returnQuery = false, $additionalWhereClause = null)
 {
     require_once 'CRM/Core/Permission.php';
     if ($includeContactIds) {
         $this->_includeContactIds = true;
         $this->includeContactIds();
     }
     // hack for now, add permission only if we are in search
     $permission = ' ( 1 ) ';
     if ($this->_search) {
         $permission = CRM_Core_Permission::whereClause(CRM_CORE_PERMISSION_VIEW, $this->_tables, $this->_whereTables);
         // regenerate fromClause since permission might have added tables
         if ($permission) {
             $this->_fromClause = CRM_Contact_BAO_Query::fromClause($this->_tables, null, null, $this->_primaryLocation, $this->_mode);
             $this->_simpleFromClause = CRM_Contact_BAO_Query::fromClause($this->_whereTables, null, null, $this->_primaryLocation, $this->_mode);
         }
     }
     list($select, $from, $where) = $this->query($count, $sortByChar, $groupContacts);
     if (empty($where)) {
         $where = 'WHERE ' . $permission;
     } else {
         $where = $where . ' AND ' . $permission;
     }
     if ($additionalWhereClause) {
         $where = $where . ' AND ' . $additionalWhereClause;
     }
     $order = $limit = '';
     if (!$count) {
         if ($sort) {
             $orderBy = trim($sort->orderBy());
             if (!empty($orderBy)) {
                 $order = " ORDER BY {$orderBy}";
             }
         } else {
             if ($sortByChar) {
                 $order = " ORDER BY LEFT(civicrm_contact.sort_name, 1) ";
             }
         }
         if ($rowCount > 0 && $offset >= 0) {
             $limit = " LIMIT {$offset}, {$rowCount} ";
         }
     }
     // building the query string
     $query = "{$select} {$from} {$where} {$order} {$limit}";
     //CRM_Core_Error::debug( 'q', $query );
     if ($returnQuery) {
         return $query;
     }
     if ($count) {
         return CRM_Core_DAO::singleValueQuery($query);
     }
     // CRM_Core_Error::debug( 'q', $query );
     $dao =& CRM_Core_DAO::executeQuery($query);
     if ($groupContacts) {
         $ids = array();
         while ($dao->fetch()) {
             $ids[] = $dao->id;
         }
         return implode(',', $ids);
     }
     return $dao;
 }

 static function fromWhereEmail($id)
 {
     $params =& self::getSearchParams($id);
     if ($params) {
         if (CRM_Utils_Array::value('customSearchID', $params)) {
             require_once 'CRM/Contact/BAO/SearchCustom.php';
             return CRM_Contact_BAO_SearchCustom::fromWhereEmail(null, $id);
         } else {
             $tables = $whereTables = array('civicrm_contact' => 1, 'civicrm_email' => 1);
             $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
             $from = CRM_Contact_BAO_Query::fromClause($whereTables);
             return array($from, $where);
         }
     } else {
         CRM_Core_Error::fatal('No contactID clause');
     }
 }

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

 /**
  * Find contacts which match the criteria
  *
  * @param string $matchClause the matching clause
  * @param  array $tables (reference ) add the tables that are needed for the select clause
  * @param int    $id          the current contact id (hence excluded from matching)
  *
  * @return string                contact ids if match found, else null
  * @static
  * @access public
  */
 function matchContact($matchClause, &$tables, $id = null)
 {
     $config =& CRM_Core_Config::singleton();
     $query = "SELECT DISTINCT civicrm_contact.id as id";
     $query .= CRM_Contact_BAO_Query::fromClause($tables);
     $query .= " WHERE {$matchClause} ";
     if ($id) {
         $query .= " AND civicrm_contact.id != " . CRM_Utils_Type::escape($id, 'Integer');
     }
     $ids = array();
     $dao =& CRM_Core_DAO::executeQuery($query);
     while ($dao->fetch()) {
         $ids[] = $dao->id;
     }
     return implode(',', $ids);
 }