/** * 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; if ($status == 'Removed') { $query = "SELECT `date` as `date_added` FROM civicrm_subscription_history WHERE id = (SELECT max(id) FROM civicrm_subscription_history WHERE contact_id = %1 AND status = \"Added\" AND group_id = {$dao->group_id} )"; $dateDAO = CRM_Core_DAO::executeQuery($query, $params); if ($dateDAO->fetch()) { $values[$id]['date_added'] = $dateDAO->date_added; } } } return $values; } }
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); }