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