/** * Replace unsubscribe tokens. * * @param string $str * The string with tokens to be replaced. * @param object $domain * The domain BAO. * @param array $groups * The groups (if any) being unsubscribed. * @param bool $html * Replace tokens with html or plain text. * @param int $contact_id * The contact ID. * @param string $hash The security hash of the unsub event * * @return string * The processed string */ public static function &replaceUnsubscribeTokens($str, &$domain, &$groups, $html, $contact_id, $hash) { if (self::token_match('unsubscribe', 'group', $str)) { if (!empty($groups)) { $config = CRM_Core_Config::singleton(); $base = CRM_Utils_System::baseURL(); // FIXME: an ugly hack for CRM-2035, to be dropped once CRM-1799 is implemented $dao = new CRM_Contact_DAO_Group(); $dao->find(); while ($dao->fetch()) { if (substr($dao->visibility, 0, 6) == 'Public') { $visibleGroups[] = $dao->id; } } $value = implode(', ', $groups); self::token_replace('unsubscribe', 'group', $value, $str); } } return $str; }
function from() { //define table name $randomNum = md5(uniqid()); $this->_tableName = "civicrm_temp_custom_{$randomNum}"; //grab the contacts added in the date range first $sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP"; if ($this->_debug > 0) { print "-- Date range query: <pre>"; print "{$sql};"; print "</pre>"; } CRM_Core_DAO::executeQuery($sql); $startDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['start_date'])); $endDateFix = NULL; if (!empty($this->_formValues['end_date'])) { $endDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['end_date'])); # tack 11:59pm on to make search inclusive of the end date $endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'"; } $dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )\n SELECT\n civicrm_contact.id,\n min(civicrm_log.modified_date) AS date_added\n FROM\n civicrm_contact LEFT JOIN civicrm_log\n ON (civicrm_contact.id = civicrm_log.entity_id AND\n civicrm_log.entity_table = 'civicrm_contact')\n GROUP BY\n civicrm_contact.id\n HAVING\n date_added >= '{$startDate}' \n {$endDateFix}"; if ($this->_debug > 0) { print "-- Date range query: <pre>"; print "{$dateRange};"; print "</pre>"; } CRM_Core_DAO::executeQuery($dateRange, CRM_Core_DAO::$_nullArray); // Only include groups in the search query of one or more Include OR Exclude groups has been selected. // CRM-6356 if ($this->_groups) { //block for Group search $smartGroup = array(); require_once 'CRM/Contact/DAO/Group.php'; $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 = $includedGroups; } if (is_array($this->_excludeGroups)) { $xGroups = implode(',', $this->_excludeGroups); } else { $xGroups = 0; } $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}"; CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray); $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP"; CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray); //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, dates_{$this->_tableName} AS d\n WHERE \n d.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, CRM_Core_DAO::$_nullArray); //search for smart group contacts foreach ($this->_excludeGroups as $keys => $values) { if (in_array($values, $smartGroup)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}"; CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray); } } } $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}"; CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray); $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}\n ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP"; if ($this->_debug > 0) { print "-- Include groups query: <pre>"; print "{$sql};"; print "</pre>"; } CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray); $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT d.id as contact_id, civicrm_group.name as group_name\n FROM dates_{$this->_tableName} AS d\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = d.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id"; //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON d.id = Xg_{$this->_tableName}.contact_id"; } $includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})"; //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null"; } if ($this->_debug > 0) { print "-- Include groups query: <pre>"; print "{$includeGroup};"; print "</pre>"; } CRM_Core_DAO::executeQuery($includeGroup, CRM_Core_DAO::$_nullArray); //search for smart group contacts foreach ($this->_includeGroups as $keys => $values) { if (in_array($values, $smartGroup)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql .= " AND contact_a.id IN (\n SELECT id AS contact_id\n FROM dates_{$this->_tableName} )"; $smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; //used only when exclude group is selected if ($xGroups != 0) { $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})"; } $smartGroupQuery = " INSERT IGNORE INTO\n Ig_{$this->_tableName}(contact_id) \n {$smartSql}"; CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray); if ($this->_debug > 0) { print "-- Smart group query: <pre>"; print "{$smartGroupQuery};"; print "</pre>"; } $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, CRM_Core_DAO::$_nullArray); if ($this->_debug > 0) { print "-- Smart group query: <pre>"; print "{$insertGroupNameQuery};"; print "</pre>"; } } } } // end if( $this->_groups ) condition $from = "FROM civicrm_contact contact_a"; /* We need to join to this again to get the date_added value */ $from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id)"; // Only include groups in the search query of one or more Include OR Exclude groups has been selected. // CRM-6356 if ($this->_groups) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; } //this makes smart groups using this search compatible w/ CiviMail $from .= " LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id)"; return $from; }
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; }
/** * Get list of all the groups and groups for a contact. * * @param int $contactId * Contact id. * * @param bool $visibility * * * @return array * this array has key-> group id and value group title */ public static function getGroupList($contactId = 0, $visibility = FALSE) { $group = new CRM_Contact_DAO_Group(); $select = $from = $where = ''; $select = 'SELECT DISTINCT civicrm_group.id, civicrm_group.title '; $from = ' FROM civicrm_group '; $where = " WHERE civicrm_group.is_active = 1 "; if ($contactId) { $from .= ' , civicrm_group_contact '; $where .= " AND civicrm_group.id = civicrm_group_contact.group_id\n AND civicrm_group_contact.contact_id = " . CRM_Utils_Type::escape($contactId, 'Integer'); } if ($visibility) { $where .= " AND civicrm_group.visibility != 'User and User Admin Only'"; } $orderby = " ORDER BY civicrm_group.name"; $sql = $select . $from . $where . $orderby; $group->query($sql); $values = array(); while ($group->fetch()) { $values[$group->id] = $group->title; } return $values; }
/** * Build where clause for groups. * * @param string $field * @param mixed $value * @param string $op * * @return string */ public function whereGroupClause($field, $value, $op) { $smartGroupQuery = ""; $group = new CRM_Contact_DAO_Group(); $group->is_active = 1; $group->find(); $smartGroups = array(); while ($group->fetch()) { if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) { $smartGroups[] = $group->id; } } CRM_Contact_BAO_GroupContactCache::check($smartGroups); $smartGroupQuery = ''; if (!empty($smartGroups)) { $smartGroups = implode(',', $smartGroups); $smartGroupQuery = " UNION DISTINCT\n SELECT DISTINCT smartgroup_contact.contact_id\n FROM civicrm_group_contact_cache smartgroup_contact\n WHERE smartgroup_contact.group_id IN ({$smartGroups}) "; } $sqlOp = $this->getSQLOperator($op); if (!is_array($value)) { $value = array($value); } $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")"; $contactAlias = $this->_aliases['civicrm_contact']; if (!empty($this->relationType) && $this->relationType == 'b_a') { $contactAlias = $this->_aliases['civicrm_contact_b']; } return " {$contactAlias}.id {$sqlOp} (\n SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id\n FROM civicrm_group_contact {$this->_aliases['civicrm_group']}\n WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'\n {$smartGroupQuery} ) "; }
function from() { //define table name $randomNum = md5(uniqid()); $this->_tableName = "civicrm_temp_custom_{$randomNum}"; //block for Group search $smartGroup = array(); $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 = $includedGroups; } if (is_array($this->_excludeGroups)) { $xGroups = implode(',', $this->_excludeGroups); } else { $xGroups = 0; } $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}"; CRM_Core_DAO::executeQuery($sql); $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP"; 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\n WHERE \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)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) {$smartSql}"; CRM_Core_DAO::executeQuery($smartGroupQuery); } } } $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}"; CRM_Core_DAO::executeQuery($sql); $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}\n ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP"; if ($this->_debug > 0) { print "-- Include groups query: <pre>"; print "{$sql};"; print "</pre>"; } CRM_Core_DAO::executeQuery($sql); $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_group_contact.contact_id, civicrm_group.name as group_name\n FROM civicrm_group_contact\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id"; //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_group_contact.contact_id = Xg_{$this->_tableName}.contact_id"; } $includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n civicrm_group_contact.group_id IN({$iGroups})"; //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null"; } if ($this->_debug > 0) { print "-- Include groups query: <pre>"; print "{$includeGroup};"; print "</pre>"; } CRM_Core_DAO::executeQuery($includeGroup); //search for smart group contacts foreach ($this->_includeGroups as $keys => $values) { if (in_array($values, $smartGroup)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; //used only when exclude group is selected if ($xGroups != 0) { $smartSql .= " AND contact_a.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); } } $from = "FROM civicrm_contact contact_a"; $fromTail = "LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1 )"; $fromTail .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; // now create a temp table to store the randomized contacts $sql = "DROP TEMPORARY TABLE IF EXISTS random_{$this->_tableName}"; CRM_Core_DAO::executeQuery($sql); $sql = "CREATE TEMPORARY TABLE random_{$this->_tableName} ( id int primary key ) ENGINE=HEAP"; CRM_Core_DAO::executeQuery($sql); if (substr($this->_segmentSize, -1) == '%') { $countSql = "SELECT DISTINCT contact_a.id {$from} {$fromTail}\n WHERE " . $this->where(); $dao = CRM_Core_DAO::executeQuery($countSql); $totalSize = $dao->N; $multiplier = substr($this->_segmentSize, 0, strlen($this->_segmentSize) - 1); $multiplier /= 100; //CRM_Core_Error::debug( "Total size: $totalSize<br/>Multiplier: $multiplier<br/>"); $this->_segmentSize = round($totalSize * $multiplier); } $sql = "INSERT INTO random_{$this->_tableName} ( id )\n SELECT DISTINCT contact_a.id {$from} {$fromTail}\n WHERE " . $this->where() . "\n ORDER BY RAND()\n LIMIT {$this->_segmentSize}"; CRM_Core_DAO::executeQuery($sql); $from = "FROM random_{$this->_tableName} random"; $from .= " INNER JOIN civicrm_contact contact_a ON random.id = contact_a.id"; $from .= " {$fromTail}"; return $from; }
/** * Returns array of group object(s) matching a set of one or Group properties. * * @param array $params * Limits the set of groups returned. * @param array $returnProperties * Which properties should be included in the returned group objects. * (member_count should be last element.) * @param string $sort * @param int $offset * @param int $rowCount * * @return array * Array of group objects. * * * @todo other BAO functions that use returnProperties (e.g. Query Objects) receive the array flipped & filled with 1s and * add in essential fields (e.g. id). This should follow a regular pattern like the others */ public static function getGroups($params = NULL, $returnProperties = NULL, $sort = NULL, $offset = NULL, $rowCount = NULL) { $dao = new CRM_Contact_DAO_Group(); if (!isset($params['is_active'])) { $dao->is_active = 1; } if ($params) { foreach ($params as $k => $v) { if ($k == 'name' || $k == 'title') { $dao->whereAdd($k . ' LIKE "' . CRM_Core_DAO::escapeString($v) . '"'); } elseif ($k == 'group_type') { foreach ((array) $v as $type) { $dao->whereAdd($k . " LIKE '%" . CRM_Core_DAO::VALUE_SEPARATOR . (int) $type . CRM_Core_DAO::VALUE_SEPARATOR . "%'"); } } elseif (is_array($v)) { foreach ($v as &$num) { $num = (int) $num; } $dao->whereAdd($k . ' IN (' . implode(',', $v) . ')'); } else { $dao->{$k} = $v; } } } if ($offset || $rowCount) { $offset = $offset > 0 ? $offset : 0; $rowCount = $rowCount > 0 ? $rowCount : 25; $dao->limit($offset, $rowCount); } if ($sort) { $dao->orderBy($sort); } // return only specific fields if returnproperties are sent if (!empty($returnProperties)) { $dao->selectAdd(); $dao->selectAdd(implode(',', $returnProperties)); } $dao->find(); $flag = $returnProperties && in_array('member_count', $returnProperties) ? 1 : 0; $groups = array(); while ($dao->fetch()) { $group = new CRM_Contact_DAO_Group(); if ($flag) { $dao->member_count = CRM_Contact_BAO_Group::memberCount($dao->id); } $groups[] = clone $dao; } return $groups; }
function from() { //define table name $randomNum = md5(uniqid()); $this->_tableName = "civicrm_temp_custom_{$randomNum}"; //block for Group search $smartGroup = array(); if ($this->_groups || $this->_allSearch) { require_once 'CRM/Contact/DAO/Group.php'; $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=HEAP"; 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)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; $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=HEAP"; 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)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')"; //used only when exclude group is selected if ($xGroups != 0) { $smartSql .= " AND contact_a.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 require_once 'CRM/Core/DAO/Tag.php'; $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=HEAP"; 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=HEAP"; 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"; /* * check the situation and set booleans */ if ($iGroups != 0) { $iG = true; } else { $iG = false; } if ($iTags != 0) { $iT = true; } else { $iT = false; } if ($xGroups != 0) { $xG = true; } else { $xG = false; } if ($xTags != 0) { $xT = true; } else { $xT = false; } if (!$this->_groups || !$this->_tags) { $this->_andOr = 1; } /* * Set from statement depending on array sel */ if ($iG && $iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable4 ON (contact_a.id = temptable4.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL OR temptable4.contact_id IS NOT NULL)"; } } if ($iG && $iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && $iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && $iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && !$iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } } if (!$iG && $iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if (!$iG && $iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && $iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && $iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && $xG && $xT) { if ($this->_andOr == 1) { $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Xt_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT 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 ) )"; return $from; }
/** * Build where clause for groups. * * This has been overridden in order to: * 1) only build the group clause when filtering * 2) render the id field as id rather than contact_id in * order to allow us to join on hte created temp table as if it * were the contact table. * * Further refactoring could break down the parent function so it can be selectively * leveraged. * * @param string $field * @param mixed $value * @param string $op * * @return string */ public function whereGroupClause($field, $value, $op) { if ($op == 'notin') { // We do not have an optimisation for this scenario at this stage. Use // parent. return parent::whereGroupClause($field, $value, $op); } if (empty($this->groupTempTable)) { $group = new CRM_Contact_DAO_Group(); $group->is_active = 1; $group->find(); $smartGroups = array(); while ($group->fetch()) { if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) { $smartGroups[] = $group->id; } } CRM_Contact_BAO_GroupContactCache::check($smartGroups); $smartGroupQuery = ''; if (!empty($smartGroups)) { $smartGroups = implode(',', $smartGroups); $smartGroupQuery = " UNION DISTINCT\n SELECT DISTINCT smartgroup_contact.contact_id as id\n FROM civicrm_group_contact_cache smartgroup_contact\n WHERE smartgroup_contact.group_id IN ({$smartGroups}) "; } $sqlOp = $this->getSQLOperator($op); if (!is_array($value)) { $value = array($value); } $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")"; $query = "SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id as id\n FROM civicrm_group_contact {$this->_aliases['civicrm_group']}\n WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'\n {$smartGroupQuery} "; $this->buildGroupTempTable($query); } return "1"; }
/** * Returns array of group object(s) matching a set of one or Group properties. * * @param array $param Array of one or more valid property_name=>value pairs. * Limits the set of groups returned. * @param array $returnProperties Which properties should be included in the returned group objects. * (member_count should be last element.) * * @return An array of group objects. * * @access public */ static function getGroups($params = NULL, $returnProperties = NULL) { $dao = new CRM_Contact_DAO_Group(); $dao->is_active = 1; if ($params) { foreach ($params as $k => $v) { if ($k == 'name' || $k == 'title') { $dao->whereAdd($k . ' LIKE "' . CRM_Core_DAO::escapeString($v) . '"'); } elseif (is_array($v)) { $dao->whereAdd($k . ' IN (' . implode(',', $v) . ')'); } else { $dao->{$k} = $v; } } } // return only specific fields if returnproperties are sent if (!empty($returnProperties)) { $dao->selectAdd(); $dao->selectAdd(implode(',', $returnProperties)); } $dao->find(); $flag = $returnProperties && in_array('member_count', $returnProperties) ? 1 : 0; $groups = array(); while ($dao->fetch()) { $group = new CRM_Contact_DAO_Group(); if ($flag) { $dao->member_count = CRM_Contact_BAO_Group::memberCount($dao->id); } $groups[] = clone $dao; } return $groups; }
function whereGroupClause($clause) { $smartGroupQuery = ""; require_once 'CRM/Contact/DAO/Group.php'; require_once 'CRM/Contact/BAO/SavedSearch.php'; $group = new CRM_Contact_DAO_Group(); $group->is_active = 1; $group->find(); while ($group->fetch()) { if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) { $smartGroups[] = $group->id; } } require_once 'CRM/Contact/BAO/GroupContactCache.php'; CRM_Contact_BAO_GroupContactCache::check($smartGroups); if (!empty($smartGroups)) { $smartGroups = implode(',', $smartGroups); $smartGroupQuery = " UNION DISTINCT \n SELECT DISTINCT smartgroup_contact.contact_id \n FROM civicrm_group_contact_cache smartgroup_contact \n WHERE smartgroup_contact.group_id IN ({$smartGroups}) "; } return " {$this->_aliases['civicrm_contact']}.id IN ( \n SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id \n FROM civicrm_group_contact {$this->_aliases['civicrm_group']}\n WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added' \n {$smartGroupQuery} ) "; }
function engageWhereGroupClause($clause) { $smartGroupQuery = ""; require_once 'CRM/Contact/DAO/Group.php'; require_once 'CRM/Contact/BAO/SavedSearch.php'; $group = new CRM_Contact_DAO_Group(); $group->is_active = 1; $group->find(); while ($group->fetch()) { if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) { $smartGroups[] = $group->id; } } if (!empty($smartGroups)) { $smartGroups = implode(',', $smartGroups); $smartGroupQuery = " UNION DISTINCT \n SELECT DISTINCT smartgroup_contact.contact_id \n FROM civicrm_group_contact_cache smartgroup_contact \n WHERE smartgroup_contact.group_id IN ({$smartGroups}) "; } if ($this->_params['gid_op'] == 'in') { return " {$this->_aliases['civicrm_contact']}.id IN ( \n SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id \n FROM civicrm_group_contact {$this->_aliases['civicrm_group']}\n WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added' \n {$smartGroupQuery} ) "; } elseif ($this->_params['gid_op'] == 'mand') { $query = " {$this->_aliases['civicrm_contact']}.id IN ( \n SELECT DISTINCT {$this->_aliases['civicrm_group']}1.contact_id \n FROM civicrm_group_contact {$this->_aliases['civicrm_group']}1\n"; for ($i = 2; $i <= count($this->_params['gid_value']); $i++) { $j = $i - 1; $status[] = "{$this->_aliases['civicrm_group']}{$i}.group_id != {$this->_aliases['civicrm_group']}{$j}.group_id"; $query .= " INNER JOIN civicrm_group_contact {$this->_aliases['civicrm_group']}{$i} \n ON {$this->_aliases['civicrm_group']}{$i}.contact_id = {$this->_aliases['civicrm_group']}{$j}.contact_id AND " . implode(" AND ", $status) . "\n"; } $query .= " WHERE "; for ($i = 1; $i <= count($this->_params['gid_value']); $i++) { $query .= $i > 1 ? " AND " : ""; $query .= " {$this->_aliases['civicrm_group']}{$i}.group_id IN ( '" . implode("' , '", $this->_params['gid_value']) . "') AND {$this->_aliases['civicrm_group']}{$i}.status = 'Added'\n"; } $query .= " {$smartGroupQuery} ) "; return $query; } }
/** * Function to call syncontacts with smart groups and static groups xxx delete * * Returns object that can iterate over a slice of the live contacts in given group. */ static function getGroupMemberships($groupIDs) { CRM_Mailchimp_Utils::checkDebug('Start-CRM_Mailchimp_Utils getGroupMemberships $groupIDs', $groupIDs); $group = new CRM_Contact_DAO_Group(); $group->id = $groupID; $group->find(); if ($group->fetch()) { //Check smart groups if ($group->saved_search_id) { $groupContactCache = new CRM_Contact_BAO_GroupContactCache(); $groupContactCache->group_id = $groupID; if ($start !== null) { $groupContactCache->limit($start, CRM_Mailchimp_Form_Sync::BATCH_COUNT); } $groupContactCache->find(); return $groupContactCache; } else { $groupContact = new CRM_Contact_BAO_GroupContact(); $groupContact->group_id = $groupID; $groupContact->whereAdd("status = 'Added'"); if ($start !== null) { $groupContact->limit($start, CRM_Mailchimp_Form_Sync::BATCH_COUNT); } $groupContact->find(); return $groupContact; } } CRM_Mailchimp_Utils::checkDebug('End-CRM_Mailchimp_Utils getGroupMemberships $groupIDs', $groupIDs); return FALSE; }