function where()
{
$petition_id = intval($this->_params['petition_id_value']);
$group_id = NULL;
if (array_key_exists('group_id_value', $this->_params)) {
$group_id = intval($this->_params['group_id_value']);
}
$petition_activity_type_id = intval(CRM_Core_OptionGroup::getValue('activity_type', 'Petition', 'name'));
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$source_activity_record_type_id = intval(CRM_Utils_Array::key('Activity Source', $activityContacts));
$this->_where = "WHERE ";
$signed = '';
$group = '';
// Include people who have signed the petition OR people who are in the passed in group
// First the signers.
$signed = "{$this->_aliases['civicrm_contact']}.id IN (SELECT contact_id\n FROM civicrm_activity_contact ac JOIN civicrm_activity a ON\n ac.activity_id = a.id WHERE ac.record_type_id = {$source_activity_record_type_id}\n AND source_record_id = {$petition_id} AND a.activity_type_id = {$petition_activity_type_id})";
// Now the people in the specified group
if ($group_id) {
// Check if we are a smart group or regular group
$results = civicrm_api3('Group', 'getsingle', array('id' => $group_id));
if (!empty($results['id'])) {
$group = "{$this->_aliases['civicrm_contact']}.id IN (SELECT contact_id FROM ";
if (!empty($results['saved_search_id'])) {
// Populate the cache
CRM_Contact_BAO_GroupContactCache::check($group_id);
$group .= "civicrm_group_contact_cache cc WHERE cc.group_id = {$group_id})";
} else {
$group .= "civicrm_group_contact gc WHERE gc.group_id = {$group_id}\n AND gc.status = 'Added')";
}
}
}
if (!empty($group)) {
$this->_where .= " ({$signed}) OR ({$group}) ";
} else {
$this->_where .= "{$signed}";
}
}
/**
* @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);
}
}
}
}
}
}
/**
* 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} ) ";
}
/**
* Create a table of the contact ids included by the group filter.
*
* This function is called by both the api (tests) and the UI.
*/
public function buildGroupTempTable()
{
if (!empty($this->groupTempTable) || empty($this->_params['gid_value']) || $this->groupFilterNotOptimised) {
return;
}
$filteredGroups = (array) $this->_params['gid_value'];
$groups = civicrm_api3('Group', 'get', array('is_active' => 1, 'id' => array('IN' => $filteredGroups), 'saved_search_id' => array('>' => 0), 'return' => 'id'));
$smartGroups = array_keys($groups['values']);
$query = "\n SELECT group_contact.contact_id as id\n FROM civicrm_group_contact group_contact\n WHERE group_contact.group_id IN (" . implode(', ', $filteredGroups) . ")\n AND group_contact.status = 'Added' ";
if (!empty($smartGroups)) {
CRM_Contact_BAO_GroupContactCache::check($smartGroups);
$smartGroups = implode(',', $smartGroups);
$query .= "\n UNION DISTINCT\n SELECT smartgroup_contact.contact_id as id\n FROM civicrm_group_contact_cache smartgroup_contact\n WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
}
$this->groupTempTable = 'civicrm_report_temp_group_' . date('Ymd_') . uniqid();
$this->executeReportQuery("\n CREATE TEMPORARY TABLE {$this->groupTempTable}\n {$query}\n ");
CRM_Core_DAO::executeQuery("ALTER TABLE {$this->groupTempTable} ADD INDEX i_id(id)");
}
/**
* 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";
}
/**
* Prepare filter options for limiting by contact ID or group ID.
*
* @param string $contactIdField
* @return \CRM_Utils_SQL_Select
*/
protected function prepareContactFilter($contactIdField)
{
$actionSchedule = $this->actionSchedule;
if ($actionSchedule->group_id) {
if ($this->isSmartGroup($actionSchedule->group_id)) {
// Check that the group is in place in the cache and up to date
\CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
return \CRM_Utils_SQL_Select::fragment()->join('grp', "INNER JOIN civicrm_group_contact_cache grp ON {$contactIdField} = grp.contact_id")->where(" grp.group_id IN ({$actionSchedule->group_id})");
} else {
return \CRM_Utils_SQL_Select::fragment()->join('grp', " INNER JOIN civicrm_group_contact grp ON {$contactIdField} = 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');
return \CRM_Utils_SQL_Select::fragment()->where("{$contactIdField} IN ({$rList})");
}
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} ) ";
}
function petitionemail_get_recipients($contact_id, $petition_id)
{
$petition_vars = petitionemail_get_petition_details($petition_id);
if (!$petition_vars) {
// Not an email target enabled petition
return;
}
$ret = array();
// First, parse the additional recipients, if any. These get the email
// regarldess of who signs it.
if (!empty($petition_vars['recipients'])) {
$recipients = explode("\n", $petition_vars['recipients']);
while (list(, $recipient) = each($recipients)) {
$email_parts = petitionemail_parse_email_line($recipient);
if (FALSE !== $email_parts) {
$ret[] = array('contact_id' => NULL, 'name' => $email_parts['name'], 'email' => $email_parts['email']);
}
}
}
// If there are any matching criteria (for a dynamic lookup) we do a
// complex query to figure out which members of the group should be
// included as recipients.
if (count($petition_vars['matching']) > 0) {
// This comes as an array with the key being the matching field and
// the value being the matching_group_id.
$matching_fields = $petition_vars['matching'];
// Get the values of the matching fields for the contact. These values
// are used to match the contact who signed the petition with the
// contact or contacts in the target group.
// Given the matching fields, we're going to do an API call against
// the contact to get the values that we will be matching on.
// Build a return_fields array that we will pass to the api call to
// specify the fields we want returned with this query.
$field_names = array_keys($matching_fields);
$return_fields = array();
reset($field_names);
while (list(, $field_name) = each($field_names)) {
// If the field_name starts with custom_ we can add it straight
// away.
if (preg_match('/^custom_/', $field_name)) {
$return_fields[] = $field_name;
continue;
}
// Look for field names with a - in them - that's an indication
// that it's an address field which will have the location part
// stuck into the name.
$field_pieces = petitionemail_split_address_field($field_name);
if ($field_pieces) {
if ($field_pieces['location_name'] == 'Primary') {
// Primary will be included via the api call, so we just need
// the field name. If it's not primary, we'll have to do a
// manual SQL call below to get the value.
$return_fields[] = $field_pieces['field_name'];
continue;
}
}
// FIXME If we get here, this is an error
}
$contact_params = array('return' => $return_fields, 'id' => $contact_id);
$contact = civicrm_api3('Contact', 'getsingle', $contact_params);
while (list($matching_field) = each($matching_fields)) {
// Check if the field was returned. If not, it's probably an address field
if (array_key_exists($matching_field, $contact)) {
$matching_fields[$matching_field] = $contact[$matching_field];
continue;
}
// This means it's probably an address field.
$field_pieces = petitionemail_split_address_field($matching_field);
if (!$field_pieces) {
// FIXME This is an error
continue;
}
$location_name = $field_pieces['location_name'];
$field_name = $field_pieces['field_name'];
// NOTE: we only work with primary fields.
if ($location_name == 'Primary' && array_key_exists($field_name, $contact)) {
// The field name returned by the API won't have the -location part.
$matching_fields[$matching_field] = $contact[$field_name];
continue;
} else {
// FIXME This is an error
continue;
}
}
// Initialize variables to build the SQL statement
$from = array();
// The master $where clause will be put together using AND
$where = array();
$params = array();
$added_tables = array();
// Initialize the from clause and where clause
$from[] = 'civicrm_contact c';
$where[] = 'c.is_deleted = 0';
// We build a sub where clause that limits results based on the
// matching group and matching field that will be put together using
// OR since we match any any of the matching field => group
// combinations.
$sub_where = array();
reset($matching_fields);
$id = 0;
while (list($matching_field, $value) = each($matching_fields)) {
// The $where_fragment will be put together using AND because
// you have to match both the group and the field.
$where_fragment = array();
// Gather information about the group that is paired with this
// matching field.
$group_id = $petition_vars['matching'][$matching_field];
// Retrieve details (specifically, find out if it's a smart group)
$results = civicrm_api3('Group', 'getsingle', array('id' => $group_id));
if (!empty($results['id'])) {
if (!empty($results['saved_search_id'])) {
// Populate the cache
CRM_Contact_BAO_GroupContactCache::check($group_id);
if (!in_array('civicrm_group_contact_cache', $added_tables)) {
$from[] = 'LEFT JOIN civicrm_group_contact_cache cc ON
c.id = cc.contact_id';
$added_tables[] = 'civicrm_group_contact_cache';
}
$where_fragment[] = 'cc.group_id = %' . $id;
$params[$id] = array($group_id, 'Integer');
$id++;
} else {
if (!in_array('civicrm_group_contact', $added_tables)) {
$from[] = 'LEFT JOIN civicrm_group_contact gc ON
c.id = gc.contact_id';
$added_tables[] = 'civicrm_group_contact';
}
$where_fragment[] = 'gc.group_id = %' . $id;
$where_fragment[] = 'gc.status = "Added"';
$params[$id] = array($group_id, 'Integer');
$id++;
}
// Now add in the matching field
if (empty($value)) {
// We should never match in this case
$where_fragment[] = "(0)";
} else {
if (preg_match('/^custom_/', $matching_field)) {
$sql = "SELECT column_name, table_name FROM civicrm_custom_group g \n JOIN civicrm_custom_field f ON g.id = f.custom_group_id WHERE \n f.id = %0";
$custom_field_id = str_replace('custom_', '', $matching_field);
$dao = CRM_Core_DAO::executeQuery($sql, array(0 => array($custom_field_id, 'Integer')));
$dao->fetch();
if (!in_array($dao->table_name, $added_tables)) {
$from[] = "LEFT JOIN " . $dao->table_name . " ON " . $dao->table_name . ".entity_id = \n c.id";
$added_tables[] = $dao->table_name;
}
$where_fragment[] = $dao->column_name . ' = %' . $id;
// Fixme - we should use the proper data type for each custom field
$params[$id] = array($value, 'String');
$id++;
} else {
// Handle non-custom fields (address fields)
// We only support primary address.
$field_pieces = petitionemail_split_address_field($matching_field);
$field_name = $field_pieces['field_name'];
if (!in_array('civicrm_address', $added_tables)) {
$from[] = "LEFT JOIN civicrm_address a ON a.contact_id = c.id";
$added_tables[] = 'civicrm_address';
}
// We have to make a special case for states, since the value we get
// from the user is the abbreviation rather than the state_province_id
// that is in the civicrm_address table.
if ($field_name == 'state_province') {
if (!in_array('civicrm_state_province', $added_tables)) {
$from[] = "LEFT JOIN civicrm_state_province sp ON a.state_province_id = sp.id";
$added_tables[] = 'civicrm_state_province';
}
$field_name = 'sp.abbreviation';
}
$where_fragment[] = $field_name . ' = %' . $id;
$where_fragment[] = 'a.is_primary = 1';
$params[$id] = array($value, 'String');
$id++;
}
}
$sub_where[] = '(' . implode(' AND ', $where_fragment) . ')';
} else {
// This is an error
}
}
if (count($sub_where) > 0) {
$where[] = '(' . implode(' OR ', $sub_where) . ')';
}
// put it all together
$sql = "SELECT DISTINCT c.id, c.display_name ";
$sql .= "FROM " . implode("\n", $from) . " ";
$sql .= "WHERE " . implode(" AND\n", $where);
$dao = CRM_Core_DAO::executeQuery($sql, $params);
$location_type_id = $petition_vars['location_type_id'];
while ($dao->fetch()) {
// Lookup the best email address.
// ORDER BY FIELD allows us to arbitrarily set the location type id
// we want to be set the highest.
$sql = "SELECT e.email FROM civicrm_email e WHERE contact_id = %0 " . "AND (location_type_id = %1 OR is_primary = 1) " . "ORDER BY FIELD(e.location_type_id, %2) DESC, e.is_primary LIMIT 1";
$email_params = array(0 => array($dao->id, 'Integer'), 1 => array($petition_vars['location_type_id'], 'Integer'), 2 => array($petition_vars['location_type_id'], 'Integer'));
$email_dao = CRM_Core_DAO::executeQuery($sql, $email_params);
$email_dao->fetch();
$ret[] = array('contact_id' => $dao->id, 'name' => $dao->display_name, 'email' => $email_dao->email);
}
}
return $ret;
}
