/**
* generate the query based on what type of query we need
*
* @param boolean $count
* @param boolean $sortByChar
* @param boolean $groupContacts
*
* @return the sql string for that query (this will most likely
* change soon)
* @access public
*/
function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE)
{
if ($count) {
if (isset($this->_distinctComponentClause)) {
// we add distinct to get the right count for components
// for the more complex result set, we use GROUP BY the same id
// CRM-9630
$select = "SELECT count( DISTINCT {$this->_distinctComponentClause} )";
} else {
$select = 'SELECT count(DISTINCT contact_a.id)';
}
$from = $this->_simpleFromClause;
if ($this->_useDistinct) {
$this->_useGroupBy = TRUE;
}
} elseif ($sortByChar) {
$select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name';
$from = $this->_simpleFromClause;
} elseif ($groupContacts) {
$select = 'SELECT contact_a.id as id';
if ($this->_useDistinct) {
$this->_useGroupBy = TRUE;
}
$from = $this->_simpleFromClause;
} else {
if (CRM_Utils_Array::value('group', $this->_paramLookup)) {
// make sure there is only one element
// this is used when we are running under smog and need to know
// how the contact was added (CRM-1203)
if (count($this->_paramLookup['group']) == 1 && count($this->_paramLookup['group'][0][2]) == 1) {
$groups = array_keys($this->_paramLookup['group'][0][2]);
$groupId = $groups[0];
//check if group is saved search
$group = new CRM_Contact_BAO_Group();
$group->id = $groupId;
$group->find(TRUE);
if (!isset($group->saved_search_id)) {
$tbName = "`civicrm_group_contact-{$groupId}`";
$this->_select['group_contact_id'] = "{$tbName}.id as group_contact_id";
$this->_element['group_contact_id'] = 1;
$this->_select['status'] = "{$tbName}.status as status";
$this->_element['status'] = 1;
$this->_tables[$tbName] = 1;
}
}
$this->_useGroupBy = TRUE;
}
if ($this->_useDistinct && !isset($this->_distinctComponentClause)) {
if (!($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY)) {
// CRM-5954
$this->_select['contact_id'] = 'contact_a.id as contact_id';
$this->_useDistinct = FALSE;
$this->_useGroupBy = TRUE;
}
}
$select = "SELECT ";
if (isset($this->_distinctComponentClause)) {
$select .= "{$this->_distinctComponentClause}, ";
}
$select .= implode(', ', $this->_select);
$from = $this->_fromClause;
}
$where = '';
if (!empty($this->_whereClause)) {
$where = "WHERE {$this->_whereClause}";
}
$having = '';
if (!empty($this->_having)) {
foreach ($this->_having as $havingsets) {
foreach ($havingsets as $havingset) {
$havingvalue[] = $havingset;
}
}
$having = ' HAVING ' . implode(' AND ', $havingvalue);
}
// if we are doing a transform, do it here
// use the $from, $where and $having to get the contact ID
if ($this->_displayRelationshipType) {
$this->filterRelatedContacts($from, $where, $having);
}
return array($select, $from, $where, $having);
}
/**
* Ask a contact for subscription confirmation (opt-in)
*
* @param string $email
* The email address.
*
* @return void
*/
public function send_confirm_request($email)
{
$config = CRM_Core_Config::singleton();
$domain = CRM_Core_BAO_Domain::getDomain();
//get the default domain email address.
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
$localpart = CRM_Core_BAO_MailSettings::defaultLocalpart();
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$confirm = implode($config->verpSeparator, array($localpart . 'c', $this->contact_id, $this->id, $this->hash)) . "@{$emailDomain}";
$group = new CRM_Contact_BAO_Group();
$group->id = $this->group_id;
$group->find(TRUE);
$component = new CRM_Mailing_BAO_Component();
$component->is_default = 1;
$component->is_active = 1;
$component->component_type = 'Subscribe';
$component->find(TRUE);
$headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <{$domainEmailAddress}>", 'To' => $email, 'Reply-To' => $confirm, 'Return-Path' => "do-not-reply@{$emailDomain}");
$url = CRM_Utils_System::url('civicrm/mailing/confirm', "reset=1&cid={$this->contact_id}&sid={$this->id}&h={$this->hash}", TRUE);
$html = $component->body_html;
if ($component->body_text) {
$text = $component->body_text;
} else {
$text = CRM_Utils_String::htmlToText($component->body_html);
}
$bao = new CRM_Mailing_BAO_Mailing();
$bao->body_text = $text;
$bao->body_html = $html;
$tokens = $bao->getTokens();
$html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']);
$html = CRM_Utils_Token::replaceSubscribeTokens($html, $group->title, $url, TRUE);
$text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
$text = CRM_Utils_Token::replaceSubscribeTokens($text, $group->title, $url, FALSE);
// render the & entities in text mode, so that the links work
$text = str_replace('&', '&', $text);
$message = new Mail_mime("\n");
$message->setHTMLBody($html);
$message->setTxtBody($text);
$b = CRM_Utils_Mail::setMimeParams($message);
$h = $message->headers($headers);
CRM_Mailing_BAO_Mailing::addMessageIdHeader($h, 's', $this->contact_id, $this->id, $this->hash);
$mailer = \Civi::service('pear_mail');
if (is_object($mailer)) {
$errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
$mailer->send($email, $h, $b);
unset($errorScope);
}
}
/**
* Generate the query based on what type of query we need.
*
* @param bool $count
* @param bool $sortByChar
* @param bool $groupContacts
* @param bool $onlyDeleted
*
* @return array
* sql query parts as an array
*/
public function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE, $onlyDeleted = FALSE)
{
// build permission clause
$this->generatePermissionClause($onlyDeleted, $count);
if ($count) {
if (isset($this->_rowCountClause)) {
$select = "SELECT {$this->_rowCountClause}";
} elseif (isset($this->_distinctComponentClause)) {
// we add distinct to get the right count for components
// for the more complex result set, we use GROUP BY the same id
// CRM-9630
$select = "SELECT count( DISTINCT {$this->_distinctComponentClause} )";
} else {
$select = 'SELECT count(DISTINCT contact_a.id) as rowCount';
}
$from = $this->_simpleFromClause;
if ($this->_useDistinct) {
$this->_useGroupBy = TRUE;
}
} elseif ($sortByChar) {
$select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name';
$from = $this->_simpleFromClause;
} elseif ($groupContacts) {
$select = 'SELECT contact_a.id as id';
if ($this->_useDistinct) {
$this->_useGroupBy = TRUE;
}
$from = $this->_simpleFromClause;
} else {
if (!empty($this->_paramLookup['group'])) {
list($name, $op, $value, $grouping, $wildcard) = $this->_paramLookup['group'][0];
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$this->_paramLookup['group'][0][1] = key($value);
}
// make sure there is only one element
// this is used when we are running under smog and need to know
// how the contact was added (CRM-1203)
$groups = (array) CRM_Utils_Array::value($this->_paramLookup['group'][0][1], $this->_paramLookup['group'][0][2], $this->_paramLookup['group'][0][2]);
if (count($this->_paramLookup['group']) == 1 && count($groups) == 1) {
$groupId = $groups[0];
//check if group is saved search
$group = new CRM_Contact_BAO_Group();
$group->id = $groupId;
$group->find(TRUE);
if (!isset($group->saved_search_id)) {
$tbName = "`civicrm_group_contact-{$groupId}`";
// CRM-17254 don't retrieve extra fields if contact_id is specifically requested
// as this will add load to an intentionally light query.
// ideally this code would be removed as it appears to be to support CRM-1203
// and passing in the required returnProperties from the url would
// make more sense that globally applying the requirements of one form.
if ($this->_returnProperties != array('contact_id')) {
$this->_select['group_contact_id'] = "{$tbName}.id as group_contact_id";
$this->_element['group_contact_id'] = 1;
$this->_select['status'] = "{$tbName}.status as status";
$this->_element['status'] = 1;
}
}
}
$this->_useGroupBy = TRUE;
}
if ($this->_useDistinct && !isset($this->_distinctComponentClause)) {
if (!($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY)) {
// CRM-5954
$this->_select['contact_id'] = 'contact_a.id as contact_id';
$this->_useDistinct = FALSE;
$this->_useGroupBy = TRUE;
}
}
$select = "SELECT ";
if (isset($this->_distinctComponentClause)) {
$select .= "{$this->_distinctComponentClause}, ";
}
$select .= implode(', ', $this->_select);
$from = $this->_fromClause;
}
$where = '';
if (!empty($this->_whereClause)) {
$where = "WHERE {$this->_whereClause}";
}
if (!empty($this->_permissionWhereClause) && empty($this->_displayRelationshipType)) {
if (empty($where)) {
$where = "WHERE {$this->_permissionWhereClause}";
} else {
$where = "{$where} AND {$this->_permissionWhereClause}";
}
}
$having = '';
if (!empty($this->_having)) {
foreach ($this->_having as $havingSets) {
foreach ($havingSets as $havingSet) {
$havingValue[] = $havingSet;
}
}
$having = ' HAVING ' . implode(' AND ', $havingValue);
}
// if we are doing a transform, do it here
// use the $from, $where and $having to get the contact ID
if ($this->_displayRelationshipType) {
$this->filterRelatedContacts($from, $where, $having);
}
return array($select, $from, $where, $having);
}
/**
* 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);
}
}
/**
* Make the cache for the group stale, resetting it to before the timeout period.
*
* @param CRM_Contact_BAO_Group $group
*/
protected function makeCacheStale(&$group)
{
CRM_Core_DAO::executeQuery('UPDATE civicrm_group SET cache_date = DATE_SUB(NOW(), INTERVAL 7 MINUTE) WHERE id = ' . $group->id);
unset($group->cache_date);
$group->find(TRUE);
Civi::$statics['CRM_Contact_BAO_GroupContactCache']['is_refresh_init'] = FALSE;
}
/**
* @param null $group
*
* @return CRM_Contact_BAO_Group|null
*/
public function _getNextParentlessGroup(&$group = NULL)
{
$lastParentlessGroup = $this->_lastParentlessGroup;
$nextGroup = new CRM_Contact_BAO_Group();
$nextGroup->order_by = 'title ' . self::$_sortOrder;
$nextGroup->find();
if ($group == NULL) {
$sawLast = TRUE;
} else {
$sawLast = FALSE;
}
while ($nextGroup->fetch()) {
if (!self::hasParentGroups($nextGroup->id) && $sawLast) {
return $nextGroup;
} elseif ($lastParentlessGroup->id == $nextGroup->id) {
$sawLast = TRUE;
}
}
return NULL;
}
/**
* generate the query based on what type of query we need
*
* @param boolean $count
* @param boolean $sortByChar
* @param boolean $groupContacts
*
* @return the sql string for that query (this will most likely
* change soon)
* @access public
*/
function query($count = false, $sortByChar = false, $groupContacts = false)
{
if ($count) {
if (isset($this->_distinctComponentClause)) {
$select = "SELECT count( {$this->_distinctComponentClause} )";
} else {
$select = $this->_useDistinct ? 'SELECT count(DISTINCT contact_a.id)' : 'SELECT count(*)';
}
$from = $this->_simpleFromClause;
} else {
if ($sortByChar) {
$select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name';
$from = $this->_simpleFromClause;
} else {
if ($groupContacts) {
//CRM-5954 - changing SELECT DISTINCT( contact_a.id ) -> SELECT ... GROUP BY contact_a.id
// but need to measure performance
$select = $this->_useDistinct ? 'SELECT DISTINCT(contact_a.id) as id' : 'SELECT contact_a.id as id';
// $select = 'SELECT contact_a.id as id';
// if ( $this->_useDistinct ) {
// $this->_useGroupBy = true;
// }
$from = $this->_simpleFromClause;
} else {
if (CRM_Utils_Array::value('group', $this->_paramLookup)) {
// make sure there is only one element
// this is used when we are running under smog and need to know
// how the contact was added (CRM-1203)
if (count($this->_paramLookup['group']) == 1 && count($this->_paramLookup['group'][0][2]) == 1) {
$groups = array_keys($this->_paramLookup['group'][0][2]);
$groupId = $groups[0];
//check if group is saved search
$group = new CRM_Contact_BAO_Group();
$group->id = $groupId;
$group->find(true);
if (!isset($group->saved_search_id)) {
$tbName = "`civicrm_group_contact-{$groupId}`";
$this->_select['group_contact_id'] = "{$tbName}.id as group_contact_id";
$this->_element['group_contact_id'] = 1;
$this->_select['status'] = "{$tbName}.status as status";
$this->_element['status'] = 1;
$this->_tables[$tbName] = 1;
}
}
}
if ($this->_useDistinct && !isset($this->_distinctComponentClause)) {
if (!($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY)) {
//CRM-5954
$this->_select['contact_id'] = 'DISTINCT(contact_a.id) as contact_id';
// $this->_useGroupBy = true;
// $this->_select['contact_id'] ='contact_a.id as contact_id';
}
}
$select = "SELECT ";
if (isset($this->_distinctComponentClause)) {
$select .= "{$this->_distinctComponentClause}, ";
}
$select .= implode(', ', $this->_select);
$from = $this->_fromClause;
}
}
}
$where = '';
if (!empty($this->_whereClause)) {
$where = "WHERE {$this->_whereClause}";
}
return array($select, $from, $where);
}
/**
* Ask a contact for subscription confirmation (opt-in)
*
* @param string $email The email address
* @return void
* @access public
*/
public function send_confirm_request($email)
{
$config = CRM_Core_Config::singleton();
require_once 'CRM/Core/BAO/Domain.php';
$domain =& CRM_Core_BAO_Domain::getDomain();
//get the default domain email address.
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
require_once 'CRM/Core/BAO/MailSettings.php';
$localpart = CRM_Core_BAO_MailSettings::defaultLocalpart();
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
require_once 'CRM/Utils/Verp.php';
$confirm = implode($config->verpSeparator, array($localpart . 'c', $this->contact_id, $this->id, $this->hash)) . "@{$emailDomain}";
require_once 'CRM/Contact/BAO/Group.php';
$group = new CRM_Contact_BAO_Group();
$group->id = $this->group_id;
$group->find(true);
require_once 'CRM/Mailing/BAO/Component.php';
$component = new CRM_Mailing_BAO_Component();
$component->is_default = 1;
$component->is_active = 1;
$component->component_type = 'Subscribe';
$component->find(true);
$headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <{$domainEmailAddress}>", 'To' => $email, 'Reply-To' => $confirm, 'Return-Path' => "do-not-reply@{$emailDomain}");
$url = CRM_Utils_System::url('civicrm/mailing/confirm', "reset=1&cid={$this->contact_id}&sid={$this->id}&h={$this->hash}", true);
$html = $component->body_html;
if ($component->body_text) {
$text = $component->body_text;
} else {
$text = CRM_Utils_String::htmlToText($component->body_html);
}
require_once 'CRM/Mailing/BAO/Mailing.php';
$bao = new CRM_Mailing_BAO_Mailing();
$bao->body_text = $text;
$bao->body_html = $html;
$tokens = $bao->getTokens();
require_once 'CRM/Utils/Token.php';
$html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true, $tokens['html']);
$html = CRM_Utils_Token::replaceSubscribeTokens($html, $group->title, $url, true);
$text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false, $tokens['text']);
$text = CRM_Utils_Token::replaceSubscribeTokens($text, $group->title, $url, false);
// render the & entities in text mode, so that the links work
$text = str_replace('&', '&', $text);
$message = new Mail_mime("\n");
$message->setHTMLBody($html);
$message->setTxtBody($text);
$b =& CRM_Utils_Mail::setMimeParams($message);
$h =& $message->headers($headers);
$mailer =& $config->getMailer();
require_once 'CRM/Mailing/BAO/Mailing.php';
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Core_Error', 'nullHandler'));
if (is_object($mailer)) {
$mailer->send($email, $h, $b);
CRM_Core_Error::setCallback();
}
}
function _getNextParentlessGroup(&$group = null)
{
require_once 'CRM/Contact/BAO/Group.php';
$lastParentlessGroup = $this->_lastParentlessGroup;
$nextGroup = new CRM_Contact_BAO_Group();
$nextGroup->order_by = "title " . self::$_sortOrder;
$nextGroup->find();
if ($group == null) {
$sawLast = true;
} else {
$sawLast = false;
}
while ($nextGroup->fetch()) {
if (!self::hasParentGroups($nextGroup->id) && $sawLast) {
return $nextGroup;
} else {
if ($lastParentlessGroup->id == $nextGroup->id) {
$sawLast = true;
}
}
}
return null;
}
/**
* Get Group DAO object for a given group id.
*/
static function getGroupById($group_id)
{
$group = new CRM_Contact_BAO_Group();
$group->id = $group_id;
if (!$group->find(TRUE)) {
throw new CRM_CiviMailchimp_Exception("Could not find Group record with ID {$group_id}");
}
return $group;
}
