/**
* Browse all saved searches.
*
* @return mixed
* content of the parents run method
*/
public function browse()
{
$rows = array();
$savedSearch = new CRM_Contact_DAO_SavedSearch();
$savedSearch->is_active = 1;
$savedSearch->selectAdd();
$savedSearch->selectAdd('id, form_values');
$savedSearch->find();
$properties = array('id', 'name', 'description');
while ($savedSearch->fetch()) {
// get name and description from group object
$group = new CRM_Contact_DAO_Group();
$group->saved_search_id = $savedSearch->id;
if ($group->find(TRUE)) {
$permissions = CRM_Group_Page_Group::checkPermission($group->id, $group->title);
if (!CRM_Utils_System::isNull($permissions)) {
$row = array();
$row['name'] = $group->title;
$row['description'] = $group->description;
$row['id'] = $savedSearch->id;
$formValues = unserialize($savedSearch->form_values);
$query = new CRM_Contact_BAO_Query($formValues);
$row['query_detail'] = $query->qill();
$action = array_sum(array_keys(self::links()));
$action = $action & CRM_Core_Action::mask($permissions);
$row['action'] = CRM_Core_Action::formLink(self::links(), $action, array('id' => $row['id']), ts('more'), FALSE, 'savedSearch.manage.action', 'SavedSearch', $row['id']);
$rows[] = $row;
}
}
}
$this->assign('rows', $rows);
return parent::run();
}
/**
* Confirm a pending subscription
*
* @param int $contact_id The id of the contact
* @param int $subscribe_id The id of the subscription event
* @param string $hash The hash
*
* @return boolean True on success
* @access public
* @static
*/
public static function confirm($contact_id, $subscribe_id, $hash)
{
$se =& CRM_Mailing_Event_BAO_Subscribe::verify($contact_id, $subscribe_id, $hash);
if (!$se) {
return FALSE;
}
// before we proceed lets just check if this contact is already 'Added'
// if so, we should ignore this request and hence avoid sending multiple
// emails - CRM-11157
$details = CRM_Contact_BAO_GroupContact::getMembershipDetail($contact_id, $se->group_id);
if ($details && $details->status == 'Added') {
// This contact is already subscribed
// lets return the group title
return CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $se->group_id, 'title');
}
$transaction = new CRM_Core_Transaction();
$ce = new CRM_Mailing_Event_BAO_Confirm();
$ce->event_subscribe_id = $se->id;
$ce->time_stamp = date('YmdHis');
$ce->save();
CRM_Contact_BAO_GroupContact::addContactsToGroup(array($contact_id), $se->group_id, 'Email', 'Added', $ce->id);
$transaction->commit();
$config = CRM_Core_Config::singleton();
$domain = CRM_Core_BAO_Domain::getDomain();
list($domainEmailName, $_) = CRM_Core_BAO_Domain::getNameAndEmail();
list($display_name, $email) = CRM_Contact_BAO_Contact_Location::getEmailDetails($se->contact_id);
$group = new CRM_Contact_DAO_Group();
$group->id = $se->group_id;
$group->find(TRUE);
$component = new CRM_Mailing_BAO_Component();
$component->is_default = 1;
$component->is_active = 1;
$component->component_type = 'Welcome';
$component->find(TRUE);
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$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::replaceWelcomeTokens($html, $group->title, TRUE);
$text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
$text = CRM_Utils_Token::replaceWelcomeTokens($text, $group->title, FALSE);
$mailParams = array('groupName' => 'Mailing Event ' . $component->component_type, 'subject' => $component->subject, 'from' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'toEmail' => $email, 'toName' => $display_name, 'replyTo' => "do-not-reply@{$emailDomain}", 'returnPath' => "do-not-reply@{$emailDomain}", 'html' => $html, 'text' => $text);
// send - ignore errors because the desired status change has already been successful
$unused_result = CRM_Utils_Mail::send($mailParams);
return $group->title;
}
/**
* Subscribe from mailing group
*
* @param array $params Associative array of property
* name/value pairs to insert in new 'survey'
*
* @throws API_Exception
* @return array api result array
* {@getfields mailing_event_subscribe_create}
* @access public
*/
function civicrm_api3_mailing_event_subscribe_create($params)
{
$email = $params['email'];
$group_id = $params['group_id'];
$contact_id = CRM_Utils_Array::value('contact_id', $params);
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->id = (int) $group_id;
if (!$group->find(TRUE)) {
throw new API_Exception('Invalid Group id');
}
$subscribe = CRM_Mailing_Event_BAO_Subscribe::subscribe($group_id, $email, $contact_id);
if ($subscribe !== NULL) {
/* Ask the contact for confirmation */
$subscribe->send_confirm_request($email);
$values = array();
$values[$subscribe->id]['contact_id'] = $subscribe->contact_id;
$values[$subscribe->id]['subscribe_id'] = $subscribe->id;
$values[$subscribe->id]['hash'] = $subscribe->hash;
return civicrm_api3_create_success($values);
}
return civicrm_api3_create_error('Subscription failed');
}
/**
* 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;
}
/**
* Given an id, get the name of the saved search.
*
* @param int $id
* The id of the saved search.
*
* @param string $value
*
* @return string
* the name of the saved search
*/
public static function getName($id, $value = 'name')
{
$group = new CRM_Contact_DAO_Group();
$group->saved_search_id = $id;
if ($group->find(TRUE)) {
return $group->{$value};
}
return NULL;
}
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} ) ";
}
/**
* Handle a subscription event
*
* @param array $params
*
* @return array
*/
function civicrm_mailer_event_subscribe($params)
{
$errors = _civicrm_mailer_check_params($params, array('email', 'group_id'));
if (!empty($errors)) {
return $errors;
}
$email = $params['email'];
$group_id = $params['group_id'];
$contact_id = CRM_Utils_Array::value('contact_id', $params);
$group = new CRM_Contact_DAO_Group();
$group->is_active = 1;
$group->id = (int) $group_id;
if (!$group->find(TRUE)) {
return civicrm_create_error(ts('Invalid Group id'));
}
$subscribe = CRM_Mailing_Event_BAO_Subscribe::subscribe($group_id, $email, $contact_id);
if ($subscribe !== NULL) {
/* Ask the contact for confirmation */
$subscribe->send_confirm_request($email);
$values = array();
$values['contact_id'] = $subscribe->contact_id;
$values['subscribe_id'] = $subscribe->id;
$values['hash'] = $subscribe->hash;
$values['is_error'] = 0;
return $values;
}
return civicrm_create_error(ts('Subscription failed'));
}
/**
* 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;
}
/**
* @param int $groupId
* @return bool
*/
protected function isSmartGroup($groupId)
{
// Then decide which table to join onto the query
$group = \CRM_Contact_DAO_Group::getTableName();
// Get the group information
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nWHERE {$group}.id = {$groupId}\n";
$groupDAO = \CRM_Core_DAO::executeQuery($sql);
if ($groupDAO->fetch() && !empty($groupDAO->saved_search_id)) {
return TRUE;
}
return FALSE;
}
/**
* 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} ) ";
}
/**
* Confirm a pending subscription
*
* @param int $contact_id The id of the contact
* @param int $subscribe_id The id of the subscription event
* @param string $hash The hash
* @return boolean True on success
* @access public
* @static
*/
public static function confirm($contact_id, $subscribe_id, $hash)
{
require_once 'CRM/Mailing/Event/BAO/Subscribe.php';
$se =& CRM_Mailing_Event_BAO_Subscribe::verify($contact_id, $subscribe_id, $hash);
if (!$se) {
return false;
}
require_once 'CRM/Core/Transaction.php';
$transaction = new CRM_Core_Transaction();
$ce = new CRM_Mailing_Event_BAO_Confirm();
$ce->event_subscribe_id = $se->id;
$ce->time_stamp = date('YmdHis');
$ce->save();
require_once 'CRM/Contact/BAO/GroupContact.php';
CRM_Contact_BAO_GroupContact::updateGroupMembershipStatus($contact_id, $se->group_id, 'Email', $ce->id);
$transaction->commit();
$config = CRM_Core_Config::singleton();
require_once 'CRM/Core/BAO/Domain.php';
$domain =& CRM_Core_BAO_Domain::getDomain();
list($domainEmailName, $_) = CRM_Core_BAO_Domain::getNameAndEmail();
require_once 'CRM/Contact/BAO/Contact/Location.php';
list($display_name, $email) = CRM_Contact_BAO_Contact_Location::getEmailDetails($se->contact_id);
require_once 'CRM/Contact/DAO/Group.php';
$group = new CRM_Contact_DAO_Group();
$group->id = $se->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 = 'Welcome';
$component->find(true);
require_once 'CRM/Core/BAO/MailSettings.php';
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'To' => $email, 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}");
$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::replaceWelcomeTokens($html, $group->title, true);
$text = CRM_Utils_Token::replaceDomainTokens($text, $domain, false, $tokens['text']);
$text = CRM_Utils_Token::replaceWelcomeTokens($text, $group->title, false);
$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();
}
return $group->title;
}
/**
* Get group clause for this user
*
* @param int $type the type of permission needed
* @param array $tables (reference ) add the tables that are needed for the select clause
* @param array $whereTables (reference ) add the tables that are needed for the where clause
*
* @return string the group where clause for this user
* @access public
*/
public static function groupClause($type, &$tables, &$whereTables)
{
if (!isset(self::$_viewPermissionedGroups)) {
self::group();
}
if ($type == CRM_Core_Permission::EDIT) {
if (self::$_editAdminUser) {
$clause = ' ( 1 ) ';
} else {
if (empty(self::$_editPermissionedGroups)) {
$clause = ' ( 0 ) ';
} else {
$clauses = array();
$groups = implode(', ', self::$_editPermissionedGroups);
$clauses[] = ' ( civicrm_group_contact.group_id IN ( ' . implode(', ', array_keys(self::$_editPermissionedGroups)) . " ) AND civicrm_group_contact.status = 'Added' ) ";
$tables['civicrm_group_contact'] = 1;
$whereTables['civicrm_group_contact'] = 1;
// foreach group that is potentially a saved search, add the saved search clause
foreach (array_keys(self::$_editPermissionedGroups) as $id) {
$group = new CRM_Contact_DAO_Group();
$group->id = $id;
if ($group->find(true) && $group->saved_search_id) {
require_once 'CRM/Contact/BAO/SavedSearch.php';
$clause = CRM_Contact_BAO_SavedSearch::whereClause($group->saved_search_id, $tables, $whereTables);
if (trim($clause)) {
$clauses[] = $clause;
}
}
}
$clause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
}
} else {
if (self::$_viewAdminUser) {
$clause = ' ( 1 ) ';
} else {
if (empty(self::$_viewPermissionedGroups)) {
$clause = ' ( 0 ) ';
} else {
$clauses = array();
$groups = implode(', ', self::$_viewPermissionedGroups);
$clauses[] = ' ( civicrm_group_contact.group_id IN (' . implode(', ', array_keys(self::$_viewPermissionedGroups)) . " ) AND civicrm_group_contact.status = 'Added' ) ";
$tables['civicrm_group_contact'] = 1;
$whereTables['civicrm_group_contact'] = 1;
// foreach group that is potentially a saved search, add the saved search clause
foreach (array_keys(self::$_viewPermissionedGroups) as $id) {
$group = new CRM_Contact_DAO_Group();
$group->id = $id;
if ($group->find(true) && $group->saved_search_id) {
require_once 'CRM/Contact/BAO/SavedSearch.php';
$clause = CRM_Contact_BAO_SavedSearch::whereClause($group->saved_search_id, $tables, $whereTables);
if (trim($clause)) {
$clauses[] = $clause;
}
}
}
$clause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
}
}
return $clause;
}
/**
* We need to do slightly different things for groups vs saved search groups, hence we
* reimplement browse from Page_Basic
* @param int $action
*
* @return void
* @access public
*/
function browse($action = null)
{
require_once 'CRM/Contact/BAO/GroupNesting.php';
$this->_sortByCharacter = CRM_Utils_Request::retrieve('sortByCharacter', 'String', $this);
if ($this->_sortByCharacter == 1 || !empty($_POST)) {
$this->_sortByCharacter = '';
$this->set('sortByCharacter', '');
}
$query = " SELECT COUNT(*) FROM civicrm_group";
$groupExists = CRM_Core_DAO::singleValueQuery($query);
$this->assign('groupExists', $groupExists);
$this->search();
$config =& CRM_Core_Config::singleton();
$params = array();
$whereClause = $this->whereClause($params, false);
$this->pagerAToZ($whereClause, $params);
$params = array();
$whereClause = $this->whereClause($params, true);
$this->pager($whereClause, $params);
list($offset, $rowCount) = $this->_pager->getOffsetAndRowCount();
$select = $from = $where = "";
if (defined('CIVICRM_MULTISITE') && CIVICRM_MULTISITE && CRM_Core_Permission::check('administer Multiple Organizations')) {
$select = ", contact.display_name as orgName, contact.id as orgID";
$from = " LEFT JOIN civicrm_group_organization gOrg\n ON gOrg.group_id = groups.id \n LEFT JOIN civicrm_contact contact\n ON contact.id = gOrg.organization_id ";
//get the Organization ID
$orgID = CRM_Utils_Request::retrieve('oid', 'Positive', CRM_Core_DAO::$_nullObject);
if ($orgID) {
$where = " AND gOrg.organization_id = {$orgID}";
}
$this->assign('groupOrg', true);
}
$query = "\n SELECT groups.* {$select}\n FROM civicrm_group groups \n {$from}\n WHERE {$whereClause} {$where}\n ORDER BY groups.title asc\n LIMIT {$offset}, {$rowCount}";
$object = CRM_Core_DAO::executeQuery($query, $params, true, 'CRM_Contact_DAO_Group');
$groupPermission = CRM_Core_Permission::check('edit groups') ? CRM_Core_Permission::EDIT : CRM_Core_Permission::VIEW;
$this->assign('groupPermission', $groupPermission);
//FIXME CRM-4418, now we are handling delete separately
//if we introduce 'delete for group' make sure to handle here.
$groupPermissions = array(CRM_Core_Permission::VIEW);
if (CRM_Core_Permission::check('edit groups')) {
$groupPermissions[] = CRM_Core_Permission::EDIT;
$groupPermissions[] = CRM_Core_Permission::DELETE;
}
require_once 'CRM/Core/OptionGroup.php';
$links =& $this->links();
$allTypes = CRM_Core_OptionGroup::values('group_type');
$values = array();
while ($object->fetch()) {
$permission = $this->checkPermission($object->id, $object->title);
if ($permission) {
$newLinks = $links;
$values[$object->id] = array();
CRM_Core_DAO::storeValues($object, $values[$object->id]);
if ($object->saved_search_id) {
$values[$object->id]['title'] .= ' (' . ts('Smart Group') . ')';
// check if custom search, if so fix view link
$customSearchID = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $object->saved_search_id, 'search_custom_id');
if ($customSearchID) {
$newLinks[CRM_Core_Action::VIEW]['url'] = 'civicrm/contact/search/custom';
$newLinks[CRM_Core_Action::VIEW]['qs'] = "reset=1&force=1&ssID={$object->saved_search_id}";
}
}
$action = array_sum(array_keys($newLinks));
if (array_key_exists('is_active', $object)) {
if ($object->is_active) {
$action -= CRM_Core_Action::ENABLE;
} else {
$action -= CRM_Core_Action::VIEW;
$action -= CRM_Core_Action::DISABLE;
}
}
$action = $action & CRM_Core_Action::mask($groupPermissions);
$values[$object->id]['visibility'] = CRM_Contact_DAO_Group::tsEnum('visibility', $values[$object->id]['visibility']);
if (isset($values[$object->id]['group_type'])) {
$groupTypes = explode(CRM_Core_DAO::VALUE_SEPARATOR, substr($values[$object->id]['group_type'], 1, -1));
$types = array();
foreach ($groupTypes as $type) {
$types[] = $allTypes[$type];
}
$values[$object->id]['group_type'] = implode(', ', $types);
}
$values[$object->id]['action'] = CRM_Core_Action::formLink($newLinks, $action, array('id' => $object->id, 'ssid' => $object->saved_search_id));
if (array_key_exists('orgName', $object)) {
if ($object->orgName) {
$values[$object->id]['org_name'] = $object->orgName;
$values[$object->id]['org_id'] = $object->orgID;
}
}
}
}
if (isset($values)) {
$this->assign('rows', $values);
}
}
/**
* This function to get list of groups
*
* @param array $params associated array for params
* @access public
*/
static function getGroupList(&$params)
{
$config = CRM_Core_Config::singleton();
$whereClause = self::whereClause($params, FALSE);
//$this->pagerAToZ( $whereClause, $params );
if (!empty($params['rowCount']) && $params['rowCount'] > 0) {
$limit = " LIMIT {$params['offset']}, {$params['rowCount']} ";
}
$orderBy = ' ORDER BY groups.title asc';
if (CRM_Utils_Array::value('sort', $params)) {
$orderBy = ' ORDER BY ' . CRM_Utils_Array::value('sort', $params);
}
$select = $from = $where = "";
$groupOrg = FALSE;
if (CRM_Core_Permission::check('administer Multiple Organizations') && CRM_Core_Permission::isMultisiteEnabled()) {
$select = ", contact.display_name as org_name, contact.id as org_id";
$from = " LEFT JOIN civicrm_group_organization gOrg\n ON gOrg.group_id = groups.id\n LEFT JOIN civicrm_contact contact\n ON contact.id = gOrg.organization_id ";
//get the Organization ID
$orgID = CRM_Utils_Request::retrieve('oid', 'Positive', CRM_Core_DAO::$_nullObject);
if ($orgID) {
$where = " AND gOrg.organization_id = {$orgID}";
}
$groupOrg = TRUE;
}
$query = "\n SELECT groups.* {$select}\n FROM civicrm_group groups\n {$from}\n WHERE {$whereClause} {$where}\n {$orderBy}\n {$limit}";
$object = CRM_Core_DAO::executeQuery($query, $params, TRUE, 'CRM_Contact_DAO_Group');
//FIXME CRM-4418, now we are handling delete separately
//if we introduce 'delete for group' make sure to handle here.
$groupPermissions = array(CRM_Core_Permission::VIEW);
if (CRM_Core_Permission::check('edit groups')) {
$groupPermissions[] = CRM_Core_Permission::EDIT;
$groupPermissions[] = CRM_Core_Permission::DELETE;
}
// CRM-9936
$reservedPermission = CRM_Core_Permission::check('administer reserved groups');
$links = self::actionLinks();
$allTypes = CRM_Core_OptionGroup::values('group_type');
$values = array();
while ($object->fetch()) {
$permission = CRM_Contact_BAO_Group::checkPermission($object->id, $object->title);
if ($permission) {
$newLinks = $links;
$values[$object->id] = array();
CRM_Core_DAO::storeValues($object, $values[$object->id]);
if ($object->saved_search_id) {
$values[$object->id]['title'] .= ' (' . ts('Smart Group') . ')';
// check if custom search, if so fix view link
$customSearchID = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $object->saved_search_id, 'search_custom_id');
if ($customSearchID) {
$newLinks[CRM_Core_Action::VIEW]['url'] = 'civicrm/contact/search/custom';
$newLinks[CRM_Core_Action::VIEW]['qs'] = "reset=1&force=1&ssID={$object->saved_search_id}";
}
}
$action = array_sum(array_keys($newLinks));
// CRM-9936
if (array_key_exists('is_reserved', $object)) {
//if group is reserved and I don't have reserved permission, suppress delete/edit
if ($object->is_reserved && !$reservedPermission) {
$action -= CRM_Core_Action::DELETE;
$action -= CRM_Core_Action::UPDATE;
$action -= CRM_Core_Action::DISABLE;
}
}
$values[$object->id]['class'] = '';
if (array_key_exists('is_active', $object)) {
if ($object->is_active) {
$action -= CRM_Core_Action::ENABLE;
} else {
$values[$object->id]['class'] = 'disabled';
$action -= CRM_Core_Action::VIEW;
$action -= CRM_Core_Action::DISABLE;
}
}
$action = $action & CRM_Core_Action::mask($groupPermissions);
$values[$object->id]['visibility'] = CRM_Contact_DAO_Group::tsEnum('visibility', $values[$object->id]['visibility']);
if (isset($values[$object->id]['group_type'])) {
$groupTypes = explode(CRM_Core_DAO::VALUE_SEPARATOR, substr($values[$object->id]['group_type'], 1, -1));
$types = array();
foreach ($groupTypes as $type) {
$types[] = CRM_Utils_Array::value($type, $allTypes);
}
$values[$object->id]['group_type'] = implode(', ', $types);
}
$values[$object->id]['action'] = CRM_Core_Action::formLink($newLinks, $action, array('id' => $object->id, 'ssid' => $object->saved_search_id));
if ($groupOrg) {
if ($object->org_id) {
$contactUrl = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$object->org_id}");
$values[$object->id]['org_info'] = "<a href='{$contactUrl}'>{$object->org_name}</a>";
} else {
$values[$object->id]['org_info'] = '';
// Empty cell
}
} else {
$values[$object->id]['org_info'] = NULL;
// Collapsed column if all cells are NULL
}
}
}
return $values;
}
/**
* 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) . '"');
} else {
if (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;
}
/**
* @param string $type
*
* @return array
*/
private function _getMailingGroupIds($type = 'Include')
{
$mailingGroup = new CRM_Mailing_DAO_MailingGroup();
$group = CRM_Contact_DAO_Group::getTableName();
if (!isset($this->id)) {
// we're just testing tokens, so return any group
$query = "SELECT id AS entity_id\n FROM {$group}\n ORDER BY id\n LIMIT 1";
} else {
$query = "SELECT entity_id\n FROM {$mg}\n WHERE mailing_id = {$this->id}\n AND group_type = '{$type}'\n AND entity_table = '{$group}'";
}
$mailingGroup->query($query);
$groupIds = array();
while ($mailingGroup->fetch()) {
$groupIds[] = $mailingGroup->entity_id;
}
return $groupIds;
}
/**
* takes an associative array and sends a thank you or email verification email
*
* @param array $params (reference ) an assoc array of name/value pairs
*
* @param $sendEmailMode
*
* @throws Exception
* @return void
@access public
* @static
*/
public static function sendEmail($params, $sendEmailMode)
{
/* sendEmailMode
* CRM_Campaign_Form_Petition_Signature::EMAIL_THANK
* connected user via login/pwd - thank you
* or dedupe contact matched who doesn't have a tag CIVICRM_TAG_UNCONFIRMED - thank you
* or login using fb connect - thank you + click to add msg to fb wall
*
* CRM_Campaign_Form_Petition_Signature::EMAIL_CONFIRM
* send a confirmation request email
*/
// check if the group defined by CIVICRM_PETITION_CONTACTS exists, else create it
$petitionGroupName = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'petition_contacts', NULL, 'Petition Contacts');
$dao = new CRM_Contact_DAO_Group();
$dao->title = $petitionGroupName;
if (!$dao->find(TRUE)) {
$dao->is_active = 1;
$dao->visibility = 'User and User Admin Only';
$dao->save();
}
$group_id = $dao->id;
// get petition info
$petitionParams['id'] = $params['sid'];
$petitionInfo = array();
CRM_Campaign_BAO_Survey::retrieve($petitionParams, $petitionInfo);
if (empty($petitionInfo)) {
CRM_Core_Error::fatal('Petition doesn\'t exist.');
}
//get the default domain email address.
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$toName = CRM_Contact_BAO_Contact::displayName($params['contactId']);
$replyTo = "do-not-reply@{$emailDomain}";
// set additional general message template params (custom tokens to use in email msg templates)
// tokens then available in msg template as {$petition.title}, etc
$petitionTokens['title'] = $petitionInfo['title'];
$petitionTokens['petitionId'] = $params['sid'];
$tplParams['petition'] = $petitionTokens;
switch ($sendEmailMode) {
case CRM_Campaign_Form_Petition_Signature::EMAIL_THANK:
// add this contact to the CIVICRM_PETITION_CONTACTS group
// Cannot pass parameter 1 by reference
$p = array($params['contactId']);
CRM_Contact_BAO_GroupContact::addContactsToGroup($p, $group_id, 'API');
if ($params['email-Primary']) {
CRM_Core_BAO_MessageTemplate::sendTemplate(array('groupName' => 'msg_tpl_workflow_petition', 'valueName' => 'petition_sign', 'contactId' => $params['contactId'], 'tplParams' => $tplParams, 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>", 'toName' => $toName, 'toEmail' => $params['email-Primary'], 'replyTo' => $replyTo, 'petitionId' => $params['sid'], 'petitionTitle' => $petitionInfo['title']));
}
break;
case CRM_Campaign_Form_Petition_Signature::EMAIL_CONFIRM:
// create mailing event subscription record for this contact
// this will allow using a hash key to confirm email address by sending a url link
$se = CRM_Mailing_Event_BAO_Subscribe::subscribe($group_id, $params['email-Primary'], $params['contactId'], 'profile');
// require_once 'CRM/Core/BAO/Domain.php';
// $domain = CRM_Core_BAO_Domain::getDomain();
$config = CRM_Core_Config::singleton();
$localpart = CRM_Core_BAO_MailSettings::defaultLocalpart();
$replyTo = implode($config->verpSeparator, array($localpart . 'c', $se->contact_id, $se->id, $se->hash)) . "@{$emailDomain}";
$confirmUrl = CRM_Utils_System::url('civicrm/petition/confirm', "reset=1&cid={$se->contact_id}&sid={$se->id}&h={$se->hash}&a={$params['activityId']}&p={$params['sid']}", TRUE);
$confirmUrlPlainText = CRM_Utils_System::url('civicrm/petition/confirm', "reset=1&cid={$se->contact_id}&sid={$se->id}&h={$se->hash}&a={$params['activityId']}&p={$params['sid']}", TRUE, NULL, FALSE);
// set email specific message template params and assign to tplParams
$petitionTokens['confirmUrl'] = $confirmUrl;
$petitionTokens['confirmUrlPlainText'] = $confirmUrlPlainText;
$tplParams['petition'] = $petitionTokens;
if ($params['email-Primary']) {
CRM_Core_BAO_MessageTemplate::sendTemplate(array('groupName' => 'msg_tpl_workflow_petition', 'valueName' => 'petition_confirmation_needed', 'contactId' => $params['contactId'], 'tplParams' => $tplParams, 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>", 'toName' => $toName, 'toEmail' => $params['email-Primary'], 'replyTo' => $replyTo, 'petitionId' => $params['sid'], 'petitionTitle' => $petitionInfo['title'], 'confirmUrl' => $confirmUrl));
}
break;
}
}
/**
* class constructor
*/
function __construct()
{
parent::__construct();
}
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;
}
/**
* @param $mappingID
* @param $now
* @param array $params
*
* @throws API_Exception
*/
static function buildRecipientContacts($mappingID, $now, $params = array())
{
$actionSchedule = new CRM_Core_DAO_ActionSchedule();
$actionSchedule->mapping_id = $mappingID;
$actionSchedule->is_active = 1;
if (!empty($params)) {
_civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE, 'ActionSchedule');
}
$actionSchedule->find();
while ($actionSchedule->fetch()) {
$mapping = new CRM_Core_DAO_ActionMapping();
$mapping->id = $mappingID;
$mapping->find(TRUE);
// note: $where - this filtering applies for both
// 'limit to' and 'addition to' options
// $limitWhere - this filtering applies only for
// 'limit to' option
$select = $join = $where = $limitWhere = array();
$limitTo = $actionSchedule->limit_to;
$value = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR));
$value = implode(',', $value);
$status = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($actionSchedule->entity_status, CRM_Core_DAO::VALUE_SEPARATOR));
$status = implode(',', $status);
$anniversary = false;
if (!CRM_Utils_System::isNull($mapping->entity_recipient)) {
$recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name');
}
$from = "{$mapping->entity} e";
if ($mapping->entity == 'civicrm_activity') {
$contactField = 'r.contact_id';
$table = 'civicrm_activity e';
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
if ($limitTo == 0) {
// including the activity target contacts if 'in addition' is defined
$join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
} else {
switch (CRM_Utils_Array::value($actionSchedule->recipient, $recipientOptions)) {
case 'Activity Assignees':
$join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
break;
case 'Activity Source':
$join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
break;
default:
case 'Activity Targets':
$join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
break;
}
}
// build where clause
if (!empty($value)) {
$where[] = "e.activity_type_id IN ({$value})";
} else {
$where[] = "e.activity_type_id IS NULL";
}
if (!empty($status)) {
$where[] = "e.status_id IN ({$status})";
}
$where[] = ' e.is_current_revision = 1 ';
$where[] = ' e.is_deleted = 0 ';
$dateField = 'e.activity_date_time';
}
if ($mapping->entity == 'civicrm_participant') {
$table = 'civicrm_event r';
$contactField = 'e.contact_id';
$join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
if ($actionSchedule->recipient_listing && $limitTo) {
$rList = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($actionSchedule->recipient_listing, CRM_Core_DAO::VALUE_SEPARATOR));
$rList = implode(',', $rList);
switch ($recipientOptions[$actionSchedule->recipient]) {
case 'participant_role':
$where[] = "e.role_id IN ({$rList})";
break;
default:
break;
}
}
// build where clause
if (!empty($value)) {
$where[] = $mapping->entity_value == 'event_type' ? "r.event_type_id IN ({$value})" : "r.id IN ({$value})";
} else {
$where[] = $mapping->entity_value == 'event_type' ? "r.event_type_id IS NULL" : "r.id IS NULL";
}
// participant status criteria not to be implemented
// for additional recipients
if (!empty($status)) {
$limitWhere[] = "e.status_id IN ({$status})";
}
$where[] = 'r.is_active = 1';
$where[] = 'r.is_template = 0';
$dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date);
}
$notINClause = '';
if ($mapping->entity == 'civicrm_membership') {
$contactField = 'e.contact_id';
$table = 'civicrm_membership e';
// build where clause
if ($status == 2) {
//auto-renew memberships
$where[] = "e.contribution_recur_id IS NOT NULL ";
} elseif ($status == 1) {
$where[] = "e.contribution_recur_id IS NULL ";
}
// build where clause
if (!empty($value)) {
$where[] = "e.membership_type_id IN ({$value})";
} else {
$where[] = "e.membership_type_id IS NULL";
}
$where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
$dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date);
$notINClause = self::permissionedRelationships($contactField);
$membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
$mStatus = implode(',', $membershipStatus);
$where[] = "e.status_id IN ({$mStatus})";
}
if ($mapping->entity == 'civicrm_contact') {
if ($value == 'birth_date') {
$dateDBField = 'birth_date';
$table = 'civicrm_contact e';
$contactField = 'e.id';
$where[] = 'e.is_deleted = 0';
$where[] = 'e.is_deceased = 0';
} else {
//custom field
$customFieldParams = array('id' => substr($value, 7));
$customGroup = $customField = array();
CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField);
$dateDBField = $customField['column_name'];
$customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup);
$from = $table = "{$customGroup['table_name']} e";
$contactField = 'e.entity_id';
$where[] = '1';
// possible to have no "where" in this case
}
$status_ = explode(',', $status);
if (in_array(2, $status_)) {
// anniversary mode:
$dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
$anniversary = true;
} else {
// regular mode:
$dateField = 'e.' . $dateDBField;
}
// TODO get this working
// TODO: Make sure everything's provided for repetition, etc.
}
// CRM-13577 Introduce Smart Groups Handling
if ($actionSchedule->group_id) {
// Need to check if its a smart group or not
// Then decide which table to join onto the query
$group = CRM_Contact_DAO_Group::getTableName();
// Get the group information
$sql = "\nSELECT {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM {$group}\nWHERE {$group}.id = {$actionSchedule->group_id}\n";
$groupDAO = CRM_Core_DAO::executeQuery($sql);
$isSmartGroup = FALSE;
if ($groupDAO->fetch() && !empty($groupDAO->saved_search_id)) {
// Check that the group is in place in the cache and up to date
CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
// Set smart group flag
$isSmartGroup = TRUE;
}
}
// CRM-13577 End Introduce Smart Groups Handling
if ($limitTo) {
if ($actionSchedule->group_id) {
// CRM-13577 If smart group then use Cache table
if ($isSmartGroup) {
$join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
$where[] = "grp.group_id IN ({$actionSchedule->group_id})";
} else {
$join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
$where[] = "grp.group_id IN ({$actionSchedule->group_id})";
}
} elseif (!empty($actionSchedule->recipient_manual)) {
$rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
$where[] = "{$contactField} IN ({$rList})";
}
} else {
$addGroup = $addWhere = '';
if ($actionSchedule->group_id) {
// CRM-13577 If smart group then use Cache table
if ($isSmartGroup) {
$addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
$addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
} else {
$addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
$addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
}
}
if (!empty($actionSchedule->recipient_manual)) {
$rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
$addWhere = "c.id IN ({$rList})";
}
}
$select[] = "{$contactField} as contact_id";
$select[] = 'e.id as entity_id';
$select[] = "'{$mapping->entity}' as entity_table";
$select[] = "{$actionSchedule->id} as action_schedule_id";
$reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND\nreminder.entity_id = e.id AND\nreminder.entity_table = '{$mapping->entity}' AND\nreminder.action_schedule_id = %1";
if ($anniversary) {
// only consider reminders less than 11 months ago
$reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
}
if ($table != 'civicrm_contact e') {
$join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
}
if ($actionSchedule->start_action_date) {
$startDateClause = array();
$op = $actionSchedule->start_action_condition == 'before' ? '<=' : '>=';
$operator = $actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD';
$date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
$startDateClause[] = "'{$now}' >= {$date}";
if ($mapping->entity == 'civicrm_participant') {
$startDateClause[] = $operator . "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
} else {
$startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
}
$startDate = implode(' AND ', $startDateClause);
} elseif ($actionSchedule->absolute_date) {
$startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
}
// ( now >= date_built_from_start_time ) OR ( now = absolute_date )
$dateClause = "reminder.id IS NULL AND {$startDate}";
// start composing query
$selectClause = 'SELECT ' . implode(', ', $select);
$fromClause = "FROM {$from}";
$joinClause = !empty($join) ? implode(' ', $join) : '';
$whereClause = 'WHERE ' . implode(' AND ', $where);
$limitWhereClause = '';
if (!empty($limitWhere)) {
$limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
}
$query = "\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)\n{$selectClause}\n{$fromClause}\n{$joinClause}\nLEFT JOIN {$reminderJoinClause}\n{$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}\n";
CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
if ($limitTo == 0) {
$additionWhere = ' WHERE ';
if ($actionSchedule->start_action_date) {
$additionWhere = $whereClause . ' AND ';
}
$contactTable = "civicrm_contact c";
$addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
$additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND\n reminder.entity_id = c.id AND\n reminder.entity_table = 'civicrm_contact' AND\n reminder.action_schedule_id = {$actionSchedule->id}";
$addWhereClause = '';
if ($addWhere) {
$addWhereClause = "AND {$addWhere}";
}
$insertAdditionalSql = "\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)\n{$addSelect}\nFROM ({$contactTable})\nLEFT JOIN {$additionReminderClause}\n{$addGroup}\nWHERE c.is_deleted = 0 AND c.is_deceased = 0\n{$addWhereClause}\n\nAND c.id NOT IN (\n SELECT rem.contact_id\n FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id\n WHERE rem.action_schedule_id = {$actionSchedule->id}\n AND rem.entity_table = '{$mapping->entity}'\n )\nGROUP BY c.id\n";
CRM_Core_DAO::executeQuery($insertAdditionalSql);
}
// if repeat is turned ON:
if ($actionSchedule->is_repeat) {
$repeatEvent = ($actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
if ($actionSchedule->repetition_frequency_unit == 'day') {
$hrs = 24 * $actionSchedule->repetition_frequency_interval;
} elseif ($actionSchedule->repetition_frequency_unit == 'week') {
$hrs = 24 * $actionSchedule->repetition_frequency_interval * 7;
} elseif ($actionSchedule->repetition_frequency_unit == 'month') {
$hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 MONTH ), latest_log_time))";
} elseif ($actionSchedule->repetition_frequency_unit == 'year') {
$hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 YEAR ), latest_log_time))";
} else {
$hrs = $actionSchedule->repetition_frequency_interval;
}
// (now <= repeat_end_time )
$repeatEventClause = "'{$now}' <= {$repeatEvent}";
// diff(now && logged_date_time) >= repeat_interval
$havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
$groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
$selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
$sqlInsertValues = "{$selectClause}\n{$fromClause}\n{$joinClause}\nINNER JOIN {$reminderJoinClause}\n{$whereClause} {$limitWhereClause} AND {$repeatEventClause}\n{$groupByClause}\n{$havingClause}";
$valsqlInsertValues = CRM_Core_DAO::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id, 'Integer')));
$arrValues = array();
while ($valsqlInsertValues->fetch()) {
$arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
}
$valString = implode(',', $arrValues);
if ($valString) {
$query = '
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
}
if ($limitTo == 0) {
$addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
$sqlEndEventCheck = "\nSELECT * FROM {$table}\n{$whereClause} AND {$repeatEventClause} LIMIT 1";
$daoCheck = CRM_Core_DAO::executeQuery($sqlEndEventCheck);
if ($daoCheck->fetch()) {
$valSqlAdditionInsert = "\n{$addSelect}\nFROM {$contactTable}\n{$addGroup}\nINNER JOIN {$additionReminderClause}\nWHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0\nGROUP BY reminder.contact_id\n{$havingClause}\n";
$daoForVals = CRM_Core_DAO::executeQuery($valSqlAdditionInsert);
$addValues = array();
while ($daoForVals->fetch()) {
$addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
}
$valString = implode(',', $addValues);
if ($valString) {
$query = '
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
CRM_Core_DAO::executeQuery($query);
}
}
}
}
}
}
/**
* adds $value['foo_display'] for each $value['foo'] enum from civicrm_group
*
* @param array $values (reference) the array up for enhancing
* @return void
*/
static function addDisplayEnums(&$values)
{
$enumFields =& CRM_Contact_DAO_Group::getEnums();
foreach ($enumFields as $enum) {
if (isset($values[$enum])) {
$values[$enum . '_display'] = CRM_Contact_DAO_Group::tsEnum($enum, $values[$enum]);
}
}
}
/**
* Construct a WHERE clause to handle permissions to $object_*
*
* @param array ref $tables - Any tables that may be needed in the FROM
* @param string $operation - The operation being attempted
* @param string $object_table - The table of the object in question
* @param int $object_id - The ID of the object in question
* @param int $acl_id - If it's a grant/revoke operation, the ACL ID
* @param boolean $acl_role - For grant operations, this flag determines if we're granting a single acl (false) or an entire group.
*
* @return string - The WHERE clause, or 0 on failure
* @access public
* @static
*/
public static function permissionClause(&$tables, $operation, $object_table = NULL, $object_id = NULL, $acl_id = NULL, $acl_role = FALSE)
{
$dao = new CRM_ACL_DAO_ACL();
$t = array('ACL' => self::getTableName(), 'ACLRole' => 'civicrm_acl_role', 'ACLEntityRole' => CRM_ACL_DAO_EntityRole::getTableName(), 'Contact' => CRM_Contact_DAO_Contact::getTableName(), 'Group' => CRM_Contact_DAO_Group::getTableName(), 'GroupContact' => CRM_Contact_DAO_GroupContact::getTableName());
$session = CRM_Core_Session::singleton();
$contact_id = $session->get('userID');
$where = " {$t['ACL']}.operation = '" . CRM_Utils_Type::escape($operation, 'String') . "'";
/* Include clause if we're looking for a specific table/id permission */
if (!empty($object_table)) {
$where .= " AND ( {$t['ACL']}.object_table IS null\n OR ({$t['ACL']}.object_table = '" . CRM_Utils_Type::escape($object_table, 'String') . "'";
if (!empty($object_id)) {
$where .= " AND ({$t['ACL']}.object_id IS null\n OR {$t['ACL']}.object_id = " . CRM_Utils_Type::escape($object_id, 'Integer') . ')';
}
$where .= '))';
}
/* Include clause if we're granting an ACL or ACL Role */
if (!empty($acl_id)) {
$where .= " AND ({$t['ACL']}.acl_id IS null\n OR {$t['ACL']}.acl_id = " . CRM_Utils_Type::escape($acl_id, 'Integer') . ')';
if ($acl_role) {
$where .= " AND {$t['ACL']}.acl_table = '{$t['ACLRole']}'";
} else {
$where .= " AND {$t['ACL']}.acl_table = '{$t['ACL']}'";
}
}
$query = array();
/* Query for permissions granted to all contacts in the domain */
$query[] = "SELECT {$t['ACL']}.*, 0 as override\n FROM {$t['ACL']}\n\n WHERE {$t['ACL']}.entity_table = '{$t['Domain']}'\n AND ({$where})";
/* Query for permissions granted to all contacts through an ACL group */
$query[] = "SELECT {$t['ACL']}.*, 0 as override\n FROM {$t['ACL']}\n\n INNER JOIN {$t['ACLEntityRole']}\n ON ({$t['ACL']}.entity_table = '{$t['ACLRole']}'\n AND {$t['ACL']}.entity_id =\n {$t['ACLEntityRole']}.acl_role_id)\n\n INNER JOIN {$t['ACLRole']}\n ON {$t['ACL']}.entity_id =\n {$t['ACLRole']}.id\n\n WHERE {$t['ACLEntityRole']}.entity_table =\n '{$t['Domain']}'\n AND {$t['ACLRole']}.is_active = 1\n AND ({$where})";
/* Query for permissions granted directly to the contact */
$query[] = "SELECT {$t['ACL']}.*, 1 as override\n FROM {$t['ACL']}\n\n INNER JOIN {$t['Contact']}\n ON ({$t['ACL']}.entity_table = '{$t['Contact']}'\n AND {$t['ACL']}.entity_id = {$t['Contact']}.id)\n\n WHERE {$t['Contact']}.id = {$contact_id}\n AND ({$where})";
/* Query for permissions granted to the contact through an ACL group */
$query[] = "SELECT {$t['ACL']}.*, 1 as override\n FROM {$t['ACL']}\n\n INNER JOIN {$t['ACLEntityRole']}\n ON ({$t['ACL']}.entity_table = '{$t['ACLRole']}'\n AND {$t['ACL']}.entity_id =\n {$t['ACLEntityRole']}.acl_role_id)\n\n INNER JOIN {$t['ACLRole']}\n ON {$t['ACL']}.entity_id = {$t['ACLRole']}.id\n\n WHERE {$t['ACLEntityRole']}.entity_table =\n '{$t['Contact']}'\n AND {$t['ACLRole']}.is_active = 1\n AND {$t['ACLEntityRole']}.entity_id = {$contact_id}\n AND ({$where})";
/* Query for permissions granted to the contact through a group */
$query[] = "SELECT {$t['ACL']}.*, 0 as override\n FROM {$t['ACL']}\n\n INNER JOIN {$t['GroupContact']}\n ON ({$t['ACL']}.entity_table = '{$t['Group']}'\n AND {$t['ACL']}.entity_id =\n {$t['GroupContact']}.group_id)\n\n WHERE ({$where})\n AND {$t['GroupContact']}.contact_id = {$contact_id}\n AND {$t['GroupContact']}.status = 'Added')";
/* Query for permissions granted through an ACL group to a Contact
* group */
$query[] = "SELECT {$t['ACL']}.*, 0 as override\n FROM {$t['ACL']}\n\n INNER JOIN {$t['ACLEntityRole']}\n ON ({$t['ACL']}.entity_table = '{$t['ACLRole']}'\n AND {$t['ACL']}.entity_id =\n {$t['ACLEntityRole']}.acl_role_id)\n\n INNER JOIN {$t['ACLRole']}\n ON {$t['ACL']}.entity_id = {$t['ACLRole']}.id\n\n INNER JOIN {$t['GroupContact']}\n ON ({$t['ACLEntityRole']}.entity_table =\n '{$t['Group']}'\n AND {$t['ACLEntityRole']}.entity_id =\n {$t['GroupContact']}.group_id)\n\n WHERE ({$where})\n AND {$t['ACLRole']}.is_active = 1\n AND {$t['GroupContact']}.contact_id = {$contact_id}\n AND {$t['GroupContact']}.status = 'Added'";
$union = '(' . implode(') UNION DISTINCT (', $query) . ')';
$dao->query($union);
$allow = array(0);
$deny = array(0);
$override = array();
while ($dao->fetch()) {
/* Instant bypass for the following cases:
* 1) the rule governs all tables
* 2) the rule governs all objects in the table in question
* 3) the rule governs the specific object we want
*/
if (empty($dao->object_table) || $dao->object_table == $object_table && (empty($dao->object_id) || $dao->object_id == $object_id)) {
$clause = 1;
} else {
/* Otherwise try to generate a clause for this rule */
$clause = self::getClause($dao->object_table, $dao->object_id, $tables);
/* If the clause returned is null, then the rule is a blanket
* (id is null) on a table other than the one we're interested
* in. So skip it. */
if (empty($clause)) {
continue;
}
}
/* Now we figure out if this is an allow or deny rule, and possibly
* a contact-level override */
if ($dao->deny) {
$deny[] = $clause;
} else {
$allow[] = $clause;
if ($dao->override) {
$override[] = $clause;
}
}
}
$allows = '(' . implode(' OR ', $allow) . ')';
$denies = '(' . implode(' OR ', $deny) . ')';
if (!empty($override)) {
$denies = '(NOT (' . implode(' OR ', $override) . ") AND {$denies})";
}
return "({$allows} AND NOT {$denies})";
}
/**
* 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";
}
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 group clause for this user. The group Clause filters the
* list of groups that the user is permitted to see in a group listing.
* For example it will filter both the list on the 'Manage Groups' page
* and on the contact 'Groups' tab
*
* the aclGroup hook & configured ACLs contribute to this data.
* If the contact is allowed to see all contacts the function will return ( 1 )
*
* @todo the history of this function is that there was some confusion as to
* whether it was filtering contacts or groups & some cruft may remain
*
* @param int $type
* The type of permission needed.
* @param array $tables
* (reference) add the tables that are needed for the select clause.
* @param array $whereTables
* (reference) add the tables that are needed for the where clause.
*
* @return string
* the clause to add to the query retrieving viewable groups
*/
public function groupClause($type, &$tables, &$whereTables)
{
if (!isset($this->_viewPermissionedGroups)) {
$this->group();
}
// we basically get all the groups here
$groupKey = 'all';
if ($type == CRM_Core_Permission::EDIT) {
if ($this->_editAdminUser) {
$clause = ' ( 1 ) ';
} elseif (empty($this->_editPermissionedGroups[$groupKey])) {
$clause = ' ( 0 ) ';
} else {
$clauses = array();
$groups = implode(', ', $this->_editPermissionedGroups[$groupKey]);
$clauses[] = ' ( civicrm_group_contact.group_id IN ( ' . implode(', ', array_keys($this->_editPermissionedGroups[$groupKey])) . " ) AND civicrm_group_contact.status = 'Added' ) ";
$tables['civicrm_group_contact'] = 1;
$whereTables['civicrm_group_contact'] = 1;
// foreach group that is potentially a saved search, add the saved search clause
foreach (array_keys($this->_editPermissionedGroups[$groupKey]) as $id) {
$group = new CRM_Contact_DAO_Group();
$group->id = $id;
if ($group->find(TRUE) && $group->saved_search_id) {
$clause = CRM_Contact_BAO_SavedSearch::whereClause($group->saved_search_id, $tables, $whereTables);
if (trim($clause)) {
$clauses[] = $clause;
}
}
}
$clause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
} else {
if ($this->_viewAdminUser) {
$clause = ' ( 1 ) ';
} elseif (empty($this->_viewPermissionedGroups[$groupKey])) {
$clause = ' ( 0 ) ';
} else {
$clauses = array();
$groups = implode(', ', $this->_viewPermissionedGroups[$groupKey]);
$clauses[] = ' civicrm_group.id IN (' . implode(', ', array_keys($this->_viewPermissionedGroups[$groupKey])) . " ) ";
$tables['civicrm_group'] = 1;
$whereTables['civicrm_group'] = 1;
$clause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
}
}
return $clause;
}
/**
* 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;
}
/**
* given an id, get the name of the saved search
*
* @param int $id the id of the saved search
*
* @return string the name of the saved search
* @access public
* @static
*/
static function getName($id, $value = 'name')
{
require_once 'CRM/Contact/DAO/Group.php';
$group = new CRM_Contact_DAO_Group();
$group->saved_search_id = $id;
if ($group->find(true)) {
return $group->{$value};
}
return null;
}
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 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;
}
}
