function from()
{
$iGroups = $xGroups = $iTags = $xTags = 0;
//define table name
$randomNum = md5(uniqid());
$this->_tableName = "civicrm_temp_custom_{$randomNum}";
//block for Group search
$smartGroup = array();
if ($this->_groups || $this->_allSearch) {
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->find();
while ($group->fetch()) {
$allGroups[] = $group->id;
if ($group->saved_search_id) {
$smartGroup[$group->saved_search_id] = $group->id;
}
}
$includedGroups = implode(',', $allGroups);
if (!empty($this->_includeGroups)) {
$iGroups = implode(',', $this->_includeGroups);
} else {
//if no group selected search for all groups
$iGroups = NULL;
}
if (is_array($this->_excludeGroups)) {
$xGroups = implode(',', $this->_excludeGroups);
} else {
$xGroups = 0;
}
$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude group is selected
if ($xGroups != 0) {
$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, civicrm_contact\n WHERE\n civicrm_contact.id = civicrm_group_contact.contact_id AND\n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN( {$xGroups})";
CRM_Core_DAO::executeQuery($excludeGroup);
//search for smart group contacts
foreach ($this->_excludeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssGroup = new CRM_Contact_DAO_Group();
$ssGroup->id = $values;
if (!$ssGroup->find(TRUE)) {
CRM_Core_Error::fatal();
}
CRM_Contact_BAO_GroupContactCache::load($ssGroup);
$smartSql = "\nSELECT gcc.contact_id\nFROM civicrm_group_contact_cache gcc\nWHERE gcc.group_id = {$ssGroup->id}\n";
$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
}
}
}
$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=MyISAM";
CRM_Core_DAO::executeQuery($sql);
if ($iGroups) {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name\n FROM civicrm_contact\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = civicrm_contact.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id";
} else {
$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
}
if ($iGroups) {
$includeGroup .= " WHERE\n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})";
} else {
$includeGroup .= " WHERE ( 1 ) ";
}
//used only when exclude group is selected
if ($xGroups != 0) {
$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeGroup);
//search for smart group contacts
foreach ($this->_includeGroups as $keys => $values) {
if (in_array($values, $smartGroup)) {
$ssGroup = new CRM_Contact_DAO_Group();
$ssGroup->id = $values;
if (!$ssGroup->find(TRUE)) {
CRM_Core_Error::fatal();
}
CRM_Contact_BAO_GroupContactCache::load($ssGroup);
$smartSql = "\nSELECT gcc.contact_id\nFROM civicrm_group_contact_cache gcc\nWHERE gcc.group_id = {$ssGroup->id}\n";
//used only when exclude group is selected
if ($xGroups != 0) {
$smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
}
$smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)\n {$smartSql}";
CRM_Core_DAO::executeQuery($smartGroupQuery);
$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL\n AND Ig_{$this->_tableName}.group_names IS NULL";
CRM_Core_DAO::executeQuery($insertGroupNameQuery);
}
}
}
//group contact search end here;
//block for Tags search
if ($this->_tags || $this->_allSearch) {
//find all tags
$tag = new CRM_Core_DAO_Tag();
$tag->is_active = 1;
$tag->find();
while ($tag->fetch()) {
$allTags[] = $tag->id;
}
$includedTags = implode(',', $allTags);
if (!empty($this->_includeTags)) {
$iTags = implode(',', $this->_includeTags);
} else {
//if no group selected search for all groups
$iTags = NULL;
}
if (is_array($this->_excludeTags)) {
$xTags = implode(',', $this->_excludeTags);
} else {
$xTags = 0;
}
$sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
CRM_Core_DAO::executeQuery($sql);
//used only when exclude tag is selected
if ($xTags != 0) {
$excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_entity_tag.entity_id\n FROM civicrm_entity_tag, civicrm_contact\n WHERE\n civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_contact.id = civicrm_entity_tag.entity_id AND\n civicrm_entity_tag.tag_id IN( {$xTags})";
CRM_Core_DAO::executeQuery($excludeTag);
}
$sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n tag_names varchar(64)) ENGINE=MyISAM";
CRM_Core_DAO::executeQuery($sql);
if ($iTags) {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name\n FROM civicrm_contact\n INNER JOIN civicrm_entity_tag\n ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = civicrm_contact.id )\n LEFT JOIN civicrm_tag\n ON civicrm_entity_tag.tag_id = civicrm_tag.id";
} else {
$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " LEFT JOIN Xt_{$this->_tableName}\n ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
}
if ($iTags) {
$includeTag .= " WHERE civicrm_entity_tag.tag_id IN({$iTags})";
} else {
$includeTag .= " WHERE ( 1 ) ";
}
//used only when exclude tag is selected
if ($xTags != 0) {
$includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
}
CRM_Core_DAO::executeQuery($includeTag);
}
$from = " FROM civicrm_contact contact_a";
/*
* CRM-10850 / CRM-10848
* If we use include / exclude groups as smart groups for ACL's having the below causes
* a cycle which messes things up. Hence commenting out for now
* $this->buildACLClause('contact_a');
*/
/*
* check the situation and set booleans
*/
$Ig = $iGroups != 0;
$It = $iTags != 0;
$Xg = $xGroups != 0;
$Xt = $xTags != 0;
//PICK UP FROM HERE
if (!$this->_groups && !$this->_tags) {
$this->_andOr = 1;
}
/*
* Set from statement depending on array sel
*/
$whereitems = array();
foreach (array('Ig', 'It') as $inc) {
if ($this->_andOr == 1) {
if (${$inc}) {
$from .= " INNER JOIN {$inc}_{$this->_tableName} temptable{$inc} ON (contact_a.id = temptable{$inc}.contact_id)";
}
} else {
if (${$inc}) {
$from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable{$inc} ON (contact_a.id = temptable{$inc}.contact_id)";
}
}
if (${$inc}) {
$whereitems[] = "temptable{$inc}.contact_id IS NOT NULL";
}
}
$this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)';
foreach (array('Xg', 'Xt') as $exc) {
if (${$exc}) {
$from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable{$exc} ON (contact_a.id = temptable{$exc}.contact_id)";
$this->_where .= " AND temptable{$exc}.contact_id IS NULL";
}
}
$from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) ) {$this->_aclFrom}";
if ($this->_aclWhere) {
$this->_where .= " AND {$this->_aclWhere} ";
}
// also exclude all contacts that are deleted
// CRM-11627
$this->_where .= " AND (contact_a.is_deleted != 1) ";
return $from;
}
function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a")
{
$config = CRM_Core_Config::singleton();
// find all the groups that are part of a saved search
$groupIDs = implode(',', $groups);
if (empty($groupIDs)) {
return NULL;
}
$sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM civicrm_group\nWHERE id IN ( {$groupIDs} )\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$group = CRM_Core_DAO::executeQuery($sql);
$ssWhere = array();
while ($group->fetch()) {
if ($tableAlias == NULL) {
$alias = "`civicrm_group_contact_cache_{$group->id}`";
} else {
$alias = $tableAlias;
}
$this->_useDistinct = TRUE;
if (!$this->_smartGroupCache || $group->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($group);
}
$this->_tables[$alias] = $this->_whereTables[$alias] = " LEFT JOIN civicrm_group_contact_cache {$alias} ON {$joinTable}.id = {$alias}.contact_id ";
$ssWhere[] = "{$alias}.group_id = {$group->id}";
}
if (!empty($ssWhere)) {
return implode(' OR ', $ssWhere);
}
return NULL;
}
/**
* note that $job_id is used only as a variable in the temp table construction
* and does not play a role in the queries generated
* @param int $job_id
* (misnomer) a nonce value used to name temporary tables.
* @param int $mailing_id
* @param bool $storeRecipients
* @param bool $dedupeEmail
* @param null $mode
*
* @return CRM_Mailing_Event_BAO_Queue|string
*/
public static function getRecipients($job_id, $mailing_id = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL)
{
$mailingGroup = new CRM_Mailing_DAO_MailingGroup();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mg = CRM_Mailing_DAO_MailingGroup::getTableName();
$eq = CRM_Mailing_Event_DAO_Queue::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
if ($mode == 'sms') {
$phone = CRM_Core_DAO_Phone::getTableName();
}
$contact = CRM_Contact_DAO_Contact::getTableName();
$group = CRM_Contact_DAO_Group::getTableName();
$g2contact = CRM_Contact_DAO_GroupContact::getTableName();
$m = new CRM_Mailing_DAO_Mailing();
$m->id = $mailing_id;
$m->find(TRUE);
$email_selection_method = $m->email_selection_method;
$location_type_id = $m->location_type_id;
// Note: When determining the ORDER that results are returned, it's
// the record that comes last that counts. That's because we are
// INSERT'ing INTO a table with a primary id so that last record
// over writes any previous record.
switch ($email_selection_method) {
case 'location-exclude':
$location_filter = "({$email}.location_type_id != {$location_type_id})";
// If there is more than one email that doesn't match the location,
// prefer the one marked is_bulkmail, followed by is_primary.
$order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'location-only':
$location_filter = "({$email}.location_type_id = {$location_type_id})";
// If there is more than one email of the desired location, prefer
// the one marked is_bulkmail, followed by is_primary.
$order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'location-prefer':
$location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1 OR {$email}.location_type_id = {$location_type_id})";
// ORDER BY is more complicated because we have to set an arbitrary
// order that prefers the location that we want. We do that using
// the FIELD function. For more info, see:
// https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field
// We assign the location type we want the value "1" by putting it
// in the first position after we name the field. All other location
// types are left out, so they will be assigned the value 0. That
// means, they will all be equally tied for first place, with our
// location being last.
$order_by = "ORDER BY FIELD({$email}.location_type_id, {$location_type_id}), {$email}.is_bulkmail, {$email}.is_primary";
break;
case 'automatic':
// fall through to default
// fall through to default
default:
$location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)";
$order_by = "ORDER BY {$email}.is_bulkmail";
}
/* Create a temp table for contact exclusion */
$mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP");
/* Add all the members of groups excluded from this mailing to the temp
* table */
$excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubGroup);
/* Add all unsubscribe members of base group from this mailing to the temp
* table */
$unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'";
$mailingGroup->query($unSubscribeBaseGroup);
/* Add all the (intended) recipients of an excluded prior mailing to
* the temp table */
$excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubMailing);
// get all the saved searches AND hierarchical groups
// and load them in the cache
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n";
$mailingGroup->query($smartGroupExclude);
}
$tempColumn = 'email_id';
if ($mode == 'sms') {
$tempColumn = 'phone_id';
}
/* Get all the group contacts we want to include */
$mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP");
/* Get the group contacts, but only those which are not in the
* exclusion temp table */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == 'sms') {
$phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name');
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
/* Query prior mailings */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == 'sms') {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupInclude = "\nREPLACE INTO I_{$job_id} (email_id, contact_id)\nSELECT civicrm_email.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email ON civicrm_email.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND {$location_filter}\n AND civicrm_email.on_hold = 0\n AND X_{$job_id}.contact_id IS null\n{$order_by}\n";
if ($mode == 'sms') {
$smartGroupInclude = "\nREPLACE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased <> 1\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($smartGroupInclude);
}
/**
* Construct the filtered search queries
*/
$query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id);
$query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}";
$mailingGroup->query($query);
}
/* Get the emails with only location override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$location_filter}\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n {$order_by}";
if ($mode == "sms") {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased <> 1\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$eq = new CRM_Mailing_Event_BAO_Queue();
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause();
$aclWhere = $aclWhere ? "WHERE {$aclWhere}" : '';
if ($storeRecipients && $mailing_id) {
$sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n";
$params = array(1 => array($mailing_id, 'Integer'));
CRM_Core_DAO::executeQuery($sql, $params);
// CRM-3975
$groupBy = $groupJoin = '';
if ($dedupeEmail) {
$groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id";
$groupBy = " GROUP BY e.email ";
}
$sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n";
CRM_Core_DAO::executeQuery($sql, $params);
// if we need to add all emails marked bulk, do it as a post filter
// on the mailing recipients table
if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
self::addMultipleEmails($mailing_id);
}
}
/* Delete the temp table */
$mailingGroup->reset();
$mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}");
$mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}");
return $eq;
}
public static function whereClause($type, &$tables, &$whereTables, $contactID = NULL)
{
$acls = CRM_ACL_BAO_Cache::build($contactID);
//CRM_Core_Error::debug( "a: $contactID", $acls );
$whereClause = NULL;
$clauses = array();
if (!empty($acls)) {
$aclKeys = array_keys($acls);
$aclKeys = implode(',', $aclKeys);
$query = "\nSELECT a.operation, a.object_id\n FROM civicrm_acl_cache c, civicrm_acl a\n WHERE c.acl_id = a.id\n AND a.is_active = 1\n AND a.object_table = 'civicrm_saved_search'\n AND a.id IN ( {$aclKeys} )\nORDER BY a.object_id\n";
$dao = CRM_Core_DAO::executeQuery($query);
// do an or of all the where clauses u see
$ids = array();
while ($dao->fetch()) {
// make sure operation matches the type TODO
if (self::matchType($type, $dao->operation)) {
if (!$dao->object_id) {
$ids = array();
$whereClause = ' ( 1 ) ';
break;
}
$ids[] = $dao->object_id;
}
}
if (!empty($ids)) {
$ids = implode(',', $ids);
$query = "\nSELECT g.*\n FROM civicrm_group g\n WHERE g.id IN ( {$ids} )\n AND g.is_active = 1\n";
$dao = CRM_Core_DAO::executeQuery($query);
$staticGroupIDs = array();
$cachedGroupIDs = array();
while ($dao->fetch()) {
// currently operation is restrcited to VIEW/EDIT
if ($dao->where_clause) {
if ($dao->select_tables) {
$tmpTables = array();
foreach (unserialize($dao->select_tables) as $tmpName => $tmpInfo) {
if ($tmpName == '`civicrm_group_contact-' . $dao->id . '`') {
$tmpName = '`civicrm_group_contact-ACL`';
$tmpInfo = str_replace('civicrm_group_contact-' . $dao->id, 'civicrm_group_contact-ACL', $tmpInfo);
} elseif ($tmpName == '`civicrm_group_contact_cache_' . $dao->id . '`') {
$tmpName = '`civicrm_group_contact_cache-ACL`';
$tmpInfo = str_replace('civicrm_group_contact_cache_' . $dao->id, 'civicrm_group_contact_cache-ACL', $tmpInfo);
}
$tmpTables[$tmpName] = $tmpInfo;
}
$tables = array_merge($tables, $tmpTables);
}
if ($dao->where_tables) {
$tmpTables = array();
foreach (unserialize($dao->where_tables) as $tmpName => $tmpInfo) {
if ($tmpName == '`civicrm_group_contact-' . $dao->id . '`') {
$tmpName = '`civicrm_group_contact-ACL`';
$tmpInfo = str_replace('civicrm_group_contact-' . $dao->id, 'civicrm_group_contact-ACL', $tmpInfo);
$staticGroupIDs[] = $dao->id;
} elseif ($tmpName == '`civicrm_group_contact_cache_' . $dao->id . '`') {
$tmpName = '`civicrm_group_contact_cache-ACL`';
$tmpInfo = str_replace('civicrm_group_contact_cache_' . $dao->id, 'civicrm_group_contact_cache-ACL', $tmpInfo);
$cachedGroupIDs[] = $dao->id;
}
$tmpTables[$tmpName] = $tmpInfo;
}
$whereTables = array_merge($whereTables, $tmpTables);
}
}
if (($dao->saved_search_id || $dao->children || $dao->parents) && $dao->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($dao);
}
}
if ($staticGroupIDs) {
$clauses[] = '( `civicrm_group_contact-ACL`.group_id IN (' . join(', ', $staticGroupIDs) . ') AND `civicrm_group_contact-ACL`.status IN ("Added") )';
}
if ($cachedGroupIDs) {
$clauses[] = '`civicrm_group_contact_cache-ACL`.group_id IN (' . join(', ', $cachedGroupIDs) . ')';
}
}
}
if (!empty($clauses)) {
$whereClause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
// call the hook to get additional whereClauses
CRM_Utils_Hook::aclWhereClause($type, $tables, $whereTables, $contactID, $whereClause);
if (empty($whereClause)) {
$whereClause = ' ( 0 ) ';
}
return $whereClause;
}
/**
* Allow removing contact from a parent group even if contact is in
* a child group. (CRM-8858)
*/
function testRemoveFromParentSmartGroup()
{
// Create smart group $parent
$params = array('name' => 'Deceased Contacts', 'title' => 'Deceased Contacts', 'is_active' => 1, 'formValues' => array('is_deceased' => 1));
$parent = CRM_Contact_BAO_Group::createSmartGroup($params);
$this->registerTestObjects(array($parent));
// Create group $child in $parent
$params = array('name' => 'Child Group', 'title' => 'Child Group', 'is_active' => 1, 'parents' => array($parent->id => 1));
$child = CRM_Contact_BAO_Group::create($params);
$this->registerTestObjects(array($child));
// Create $c1, $c2, $c3
$deceased = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 1), 3);
// Add $c1, $c2, $c3 to $child
foreach ($deceased as $contact) {
$result = $this->callAPISuccess('group_contact', 'create', array('contact_id' => $contact->id, 'group_id' => $child->id));
}
// GroupContactCache::load()
CRM_Contact_BAO_GroupContactCache::load($parent, TRUE);
$this->assertCacheMatches(array($deceased[0]->id, $deceased[1]->id, $deceased[2]->id), $parent->id);
// Remove $c1 from $parent
$result = civicrm_api('group_contact', 'create', array('contact_id' => $deceased[0]->id, 'group_id' => $parent->id, 'status' => 'Removed', 'version' => '3'));
$this->assertAPISuccess($result);
// Assert $c1 not in $parent
CRM_Contact_BAO_GroupContactCache::load($parent, TRUE);
$this->assertCacheMatches(array($deceased[1]->id, $deceased[2]->id), $parent->id);
// Assert $c1 still in $child
$this->assertDBQuery(1, 'select count(*) from civicrm_group_contact where group_id=%1 and contact_id=%2 and status=%3', array(1 => array($child->id, 'Integer'), 2 => array($deceased[0]->id, 'Integer'), 3 => array('Added', 'String')));
}
/**
* @param array $groups
* @param string $tableAlias
* @param string $joinTable
* @param string $op
*
* @return null|string
*/
public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op)
{
$isNullOp = strpos($op, 'NULL') !== FALSE;
$groupsIds = $groups;
if (!$isNullOp && !$groups) {
return NULL;
} elseif (strpos($op, 'IN') !== FALSE) {
$groups = array($op => $groups);
} elseif (is_array($groups) && count($groups)) {
$groups = array('IN' => $groups);
}
// Find all the groups that are part of a saved search.
$smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
$sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM civicrm_group\nWHERE {$smartGroupClause}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$group = CRM_Core_DAO::executeQuery($sql);
while ($group->fetch()) {
$this->_useDistinct = TRUE;
if (!$this->_smartGroupCache || $group->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($group);
}
}
if (!$tableAlias) {
$tableAlias = "`civicrm_group_contact_cache_";
$tableAlias .= $isNullOp ? "a`" : implode(',', (array) $groupsIds) . "`";
}
$this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
}
static function &getRecipients($job_id, $mailing_id = NULL, $offset = NULL, $limit = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL)
{
$mailingGroup = new CRM_Mailing_DAO_MailingGroup();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mg = CRM_Mailing_DAO_MailingGroup::getTableName();
$eq = CRM_Mailing_Event_DAO_Queue::getTableName();
$ed = CRM_Mailing_Event_DAO_Delivered::getTableName();
$eb = CRM_Mailing_Event_DAO_Bounce::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
if ($mode == 'sms') {
$phone = CRM_Core_DAO_Phone::getTableName();
}
$contact = CRM_Contact_DAO_Contact::getTableName();
$group = CRM_Contact_DAO_Group::getTableName();
$g2contact = CRM_Contact_DAO_GroupContact::getTableName();
/* Create a temp table for contact exclusion */
$mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n (contact_id int primary key)\n ENGINE=HEAP");
/* Add all the members of groups excluded from this mailing to the temp
* table */
$excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubGroup);
/* Add all unsubscribe members of base group from this mailing to the temp
* table */
$unSubscribeBaseGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$g2contact}.status = 'Removed'\n AND {$mg}.group_type = 'Base'";
$mailingGroup->query($unSubscribeBaseGroup);
/* Add all the (intended) recipients of an excluded prior mailing to
* the temp table */
$excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT DISTINCT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$mailing_id}\n AND {$mg}.group_type = 'Exclude'";
$mailingGroup->query($excludeSubMailing);
// get all the saved searches AND hierarchical groups
// and load them in the cache
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM civicrm_group_contact_cache c\nWHERE c.group_id = {$groupDAO->id}\n";
$mailingGroup->query($smartGroupExclude);
}
$tempColumn = 'email_id';
if ($mode == 'sms') {
$tempColumn = 'phone_id';
}
/* Get all the group contacts we want to include */
$mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n ({$tempColumn} int, contact_id int primary key)\n ENGINE=HEAP");
/* Get the group contacts, but only those which are not in the
* exclusion temp table */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.email IS NOT NULL\n AND {$email}.email != ''\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == 'sms') {
$phoneTypes = CRM_Core_OptionGroup::values('phone_type', TRUE, FALSE, FALSE, NULL, 'name');
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$mg}.search_id IS NULL\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$phone}.phone IS NOT NULL\n AND {$phone}.phone != ''\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
/* Query prior mailings */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == 'sms') {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n ({$mg}.group_type = 'Include')\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$mg}.search_id IS NULL\n AND {$mg}.mailing_id = {$mailing_id}\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
while ($groupDAO->fetch()) {
if ($groupDAO->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($groupDAO);
}
$smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\nSELECT e.id as email_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_email e ON e.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_email = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND (e.is_bulkmail = 1 OR e.is_primary = 1)\n AND e.on_hold = 0\n AND X_{$job_id}.contact_id IS null\nORDER BY e.is_bulkmail\n";
if ($mode == 'sms') {
$smartGroupInclude = "\nINSERT IGNORE INTO I_{$job_id} (phone_id, contact_id)\nSELECT p.id as phone_id, c.id as contact_id\nFROM civicrm_contact c\nINNER JOIN civicrm_phone p ON p.contact_id = c.id\nINNER JOIN civicrm_group_contact_cache gc ON gc.contact_id = c.id\nLEFT JOIN X_{$job_id} ON X_{$job_id}.contact_id = c.id\nWHERE gc.group_id = {$groupDAO->id}\n AND c.do_not_sms = 0\n AND c.is_opt_out = 0\n AND c.is_deceased = 0\n AND p.phone_type_id = {$phoneTypes['Mobile']}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($smartGroupInclude);
}
/**
* Construct the filtered search queries
*/
$query = "\nSELECT search_id, search_args, entity_id\nFROM {$mg}\nWHERE {$mg}.search_id IS NOT NULL\nAND {$mg}.mailing_id = {$mailing_id}\n";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
$customSQL = CRM_Contact_BAO_SearchCustom::civiMailSQL($dao->search_id, $dao->search_args, $dao->entity_id);
$query = "REPLACE INTO I_{$job_id} ({$tempColumn}, contact_id)\n {$customSQL}";
$mailingGroup->query($query);
}
/* Get the emails with only location override */
$query = "REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND ({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null\n ORDER BY {$email}.is_bulkmail";
if ($mode == "sms") {
$query = "REPLACE INTO I_{$job_id} (phone_id, contact_id)\n SELECT DISTINCT {$phone}.id as phone_id,\n {$contact}.id as contact_id\n FROM {$phone}\n INNER JOIN {$contact}\n ON {$phone}.contact_id = {$contact}.id\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$contact}.do_not_sms = 0\n AND {$contact}.is_opt_out = 0\n AND {$contact}.is_deceased = 0\n AND {$phone}.phone_type_id = {$phoneTypes['Mobile']}\n AND {$mg}.mailing_id = {$mailing_id}\n AND X_{$job_id}.contact_id IS null";
}
$mailingGroup->query($query);
$results = array();
$eq = new CRM_Mailing_Event_BAO_Queue();
list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause();
$aclWhere = $aclWhere ? "WHERE {$aclWhere}" : '';
$limitString = NULL;
if ($limit && $offset !== NULL) {
$offset = CRM_Utils_Type::escape($offset, 'Int');
$limit = CRM_Utils_Type::escape($limit, 'Int');
$limitString = "LIMIT {$offset}, {$limit}";
}
if ($storeRecipients && $mailing_id) {
$sql = "\nDELETE\nFROM civicrm_mailing_recipients\nWHERE mailing_id = %1\n";
$params = array(1 => array($mailing_id, 'Integer'));
CRM_Core_DAO::executeQuery($sql, $params);
// CRM-3975
$groupBy = $groupJoin = '';
if ($dedupeEmail) {
$groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id";
$groupBy = " GROUP BY e.email ";
}
$sql = "\nINSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} )\nSELECT %1, i.contact_id, i.{$tempColumn}\nFROM civicrm_contact contact_a\nINNER JOIN I_{$job_id} i ON contact_a.id = i.contact_id\n {$groupJoin}\n {$aclFrom}\n {$aclWhere}\n {$groupBy}\nORDER BY i.contact_id, i.{$tempColumn}\n";
CRM_Core_DAO::executeQuery($sql, $params);
// if we need to add all emails marked bulk, do it as a post filter
// on the mailing recipients table
if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
self::addMultipleEmails($mailing_id);
}
}
/* Delete the temp table */
$mailingGroup->reset();
$mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}");
$mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}");
return $eq;
}
/**
* Set up a smart group testing scenario.
*
* @return array
*/
protected function setupSmartGroup()
{
$params = array('name' => 'Deceased Contacts', 'title' => 'Deceased Contacts', 'is_active' => 1, 'formValues' => array('is_deceased' => 1));
$group = CRM_Contact_BAO_Group::createSmartGroup($params);
$this->registerTestObjects(array($group));
// Create contacts $y1, $y2, $y3 which do match $g; create $n1, $n2, $n3 which do not match $g
$living = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 0), 3);
$deceased = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 1), 3);
$this->assertEquals(3, count($deceased));
$this->assertEquals(3, count($living));
// Assert: $g cache has exactly $y1, $y2, $y3
CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
$group->find(TRUE);
$this->assertCacheMatches(array($deceased[0]->id, $deceased[1]->id, $deceased[2]->id), $group->id);
// Reload the group so we have the cache_date & refresh_date.
return array($group, $living, $deceased);
}
/**
* Check we can load smart groups based on config from 'real DBs' without fatal errors.
*
* Note that we are only testing lack of errors at this stage
* @todo - for some reason the data was getting truncated from the group table using dataprovider - would be preferable to get that working
* //@notdataProvider dataProviderSavedSearch
* //@notparam integer $groupID
*
* To add to this dataset do
*
* SET @groupID = x;
* SELECT mapping_id FROM civicrm_group g LEFT JOIN civicrm_saved_search s ON saved_search_id = s.id WHERE g.id = @groupID INTO @mappingID;
* SELECT * FROM civicrm_mapping WHERE id = @mappingID;
* SELECT * FROM civicrm_mapping_field WHERE mapping_id = @mappingID;
* SELECT * FROM civicrm_saved_search WHERE mapping_id = @mappingID;
* SELECT g.* FROM civicrm_saved_search s LEFT JOIN civicrm_group g ON g.saved_search_id = s.id WHERE mapping_id = @mappingID;
*
* Copy the output to a single sql file and place in the SavedSearchDataSets folder - use the group number as the prefix.
* Try to keep as much of the real world irregular glory as you can! Don't change the table ids to be number 1 as this can hide errors
*/
public function testGroupData()
{
$groups = $this->dataProviderSavedSearch();
foreach ($groups[0] as $groupID) {
$group = new CRM_Contact_BAO_Group();
$group->id = $groupID;
$group->find(TRUE);
CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
}
}
/**
* @param array $groups
* @param string $tableAlias
* @param string $joinTable
*
* @return null|string
*/
function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a")
{
$config = CRM_Core_Config::singleton();
// Find all the groups that are part of a saved search.
$groupIDs = implode(',', $groups);
if (empty($groupIDs)) {
return NULL;
}
$sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM civicrm_group\nWHERE id IN ( {$groupIDs} )\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$group = CRM_Core_DAO::executeQuery($sql);
$groupsFiltered = array();
while ($group->fetch()) {
$groupsFiltered[] = $group->id;
$this->_useDistinct = TRUE;
if (!$this->_smartGroupCache || $group->cache_date == NULL) {
CRM_Contact_BAO_GroupContactCache::load($group);
}
}
if (count($groupsFiltered)) {
$groupIDsFiltered = implode(',', $groupsFiltered);
if ($tableAlias == NULL) {
$tableAlias = "civicrm_group_contact_cache_{$groupIDsFiltered}";
}
$this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache `{$tableAlias}` ON {$joinTable}.id = `{$tableAlias}`.contact_id ";
return "`{$tableAlias}`.group_id IN (" . $groupIDsFiltered . ")";
}
return NULL;
}
/**
* where / qill clause for smart groups
*
* @return void
* @access public
*/
function savedSearch(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$config = CRM_Core_Config::singleton();
// find all the groups that are part of a saved search
$groupIDs = implode(',', array_keys($value));
$sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM civicrm_group\nWHERE id IN ( {$groupIDs} )\n AND ( saved_search_id != 0\n OR saved_search_id IS NOT NULL\n OR children IS NOT NULL )\n";
$group = CRM_Core_DAO::executeQuery($sql);
$ssWhere = array();
while ($group->fetch()) {
$this->_useDistinct = true;
if (!$this->_smartGroupCache || $group->cache_date == null) {
require_once 'CRM/Contact/BAO/GroupContactCache.php';
CRM_Contact_BAO_GroupContactCache::load($group);
}
$gcTable = "`civicrm_group_contact_cache_{$group->id}`";
$this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact_cache {$gcTable} ON contact_a.id = {$gcTable}.contact_id ";
$ssWhere[] = "{$gcTable}.group_id = {$group->id}";
}
if (!empty($ssWhere)) {
return implode(' OR ', $ssWhere);
}
return null;
}
/**
* Test smart groups with non-numeric don't fail on range queries.
*
* CRM-14720
*/
public function testNumericPostal()
{
$this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
$this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
$this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
$this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
$params = array(array('postal_code_low', '=', 5, 0, 0));
CRM_Contact_BAO_Query::convertFormValues($params);
$query = new CRM_Contact_BAO_Query($params, array('contact_id'), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE);
$sql = $query->query(FALSE);
$result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
$this->assertEquals(2, $result->N);
// We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
// results in mysql warnings & hence fatal errors.
/// I was unable to get mysql warnings to activate in the context of the unit tests - but
// felt this code still provided a useful bit of coverage as it runs the various queries to load
// the group & could generate invalid sql if a bug were introduced.
$groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
$group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
}
public static function whereClause($type, &$tables, &$whereTables, $contactID = null)
{
require_once 'CRM/ACL/BAO/Cache.php';
$acls =& CRM_ACL_BAO_Cache::build($contactID);
//CRM_Core_Error::debug( "a: $contactID", $acls );
$whereClause = null;
$clauses = array();
if (!empty($acls)) {
$aclKeys = array_keys($acls);
$aclKeys = implode(',', $aclKeys);
$query = "\nSELECT a.operation, a.object_id\n FROM civicrm_acl_cache c, civicrm_acl a\n WHERE c.acl_id = a.id\n AND a.is_active = 1\n AND a.object_table = 'civicrm_saved_search'\n AND a.id IN ( {$aclKeys} )\nORDER BY a.object_id\n";
$dao =& CRM_Core_DAO::executeQuery($query);
// do an or of all the where clauses u see
$ids = array();
while ($dao->fetch()) {
// make sure operation matches the type TODO
if (self::matchType($type, $dao->operation)) {
if (!$dao->object_id) {
$ids = array();
$whereClause = ' ( 1 ) ';
break;
}
$ids[] = $dao->object_id;
}
}
if (!empty($ids)) {
$ids = implode(',', $ids);
$query = "\nSELECT g.*\n FROM civicrm_group g\n WHERE g.id IN ( {$ids} )\n";
$dao =& CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
// currently operation is restrcited to VIEW/EDIT
if ($dao->where_clause) {
$clauses[] = $dao->where_clause;
if ($dao->select_tables) {
$tables = array_merge($tables, unserialize($dao->select_tables));
}
if ($dao->where_tables) {
$whereTables = array_merge($whereTables, unserialize($dao->where_tables));
}
}
if (($dao->saved_search_id || $dao->children || $dao->parents) && $dao->cache_date == null) {
require_once 'CRM/Contact/BAO/GroupContactCache.php';
CRM_Contact_BAO_GroupContactCache::load($dao);
}
}
}
}
if (!empty($clauses)) {
$whereClause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
// call the hook to get additional whereClauses
require_once 'CRM/Utils/Hook.php';
CRM_Utils_Hook::aclWhereClause($type, $tables, $whereTables, $contactID, $whereClause);
if (empty($whereClause)) {
$whereClause = ' ( 0 ) ';
}
return $whereClause;
}
