/** * 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})"; }
/** * @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); } } } } } }
/** * @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; }
/** * @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; }
/** * Find all intended recipients of a mailing * * @param int $job_id Job ID * @return object A DAO loaded with results of the form * (email_id, contact_id) */ function &getRecipients($job_id) { $mailingGroup =& new CRM_Mailing_DAO_Group(); $mailing = CRM_Mailing_BAO_Mailing::getTableName(); $job = CRM_Mailing_BAO_Job::getTableName(); $mg = CRM_Mailing_DAO_Group::getTableName(); $eq = CRM_Mailing_Event_DAO_Queue::getTableName(); $ed = CRM_Mailing_Event_DAO_Delivered::getTableName(); $eb = CRM_Mailing_Event_DAO_Bounce::getTableName(); $email = CRM_Core_DAO_Email::getTableName(); $contact = CRM_Contact_DAO_Contact::getTableName(); $location = CRM_Core_DAO_Location::getTableName(); $group = CRM_Contact_DAO_Group::getTableName(); $g2contact = CRM_Contact_DAO_GroupContact::getTableName(); /* Create a temp table for contact exclusion */ $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id} \n (contact_id int primary key) \n ENGINE=HEAP"); /* Add all the members of groups excluded from this mailing to the temp * table */ $excludeSubGroup = "INSERT INTO X_{$job_id} (contact_id)\n SELECT {$g2contact}.contact_id\n FROM {$g2contact}\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n WHERE\n {$mg}.mailing_id = {$this->id}\n AND {$g2contact}.status = 'Added'\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubGroup); /* Add all the (intended) recipients of an excluded prior mailing to * the temp table */ $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n WHERE\n {$mg}.mailing_id = {$this->id}\n AND {$mg}.group_type = 'Exclude'"; $mailingGroup->query($excludeSubMailing); /* Add all the succesful deliveries of this mailing (but any job/retry) * to the exclude temp table */ $excludeRetry = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT {$eq}.contact_id\n FROM {$eq}\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$ed}\n ON {$eq}.id = {$ed}.event_queue_id\n LEFT JOIN {$eb}\n ON {$eq}.id = {$eb}.event_queue_id\n WHERE\n {$job}.mailing_id = {$this->id}\n AND {$eb}.id IS null"; $mailingGroup->query($excludeRetry); $ss =& new CRM_Core_DAO(); $ss->query("SELECT {$group}.saved_search_id as saved_search_id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n WHERE {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Exclude'\n AND {$mg}.mailing_id = {$this->id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { /* run the saved search query and dump result contacts into the temp * table */ $tables = array($contact => 1); $where = CRM_Contact_BAO_SavedSearch::whereClause($ss->saved_search_id, $tables, $whereTables); $from = CRM_Contact_BAO_Query::fromClause($tables); $mailingGroup->query("INSERT IGNORE INTO X_{$job_id} (contact_id)\n SELECT {$contact}.id\n {$from}\n WHERE {$where}"); } /* Get all the group contacts we want to include */ $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id} \n (email_id int, contact_id int primary key)\n ENGINE=HEAP"); /* Get the group contacts, but only those which are not in the * exclusion temp table */ /* Get the emails with no override */ $mailingGroup->query("INSERT INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n AND {$mg}.entity_table = '{$group}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.location_id IS null\n AND {$g2contact}.email_id IS null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Query prior mailings */ $mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\n INNER JOIN {$eq}\n ON {$eq}.contact_id = {$contact}.id\n INNER JOIN {$job}\n ON {$eq}.job_id = {$job}.id\n INNER JOIN {$mg}\n ON {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE\n {$mg}.group_type = 'Include'\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Construct the saved-search queries */ $ss->query("SELECT {$group}.saved_search_id as saved_search_id\n FROM {$group}\n INNER JOIN {$mg}\n ON {$mg}.entity_id = {$group}.id\n AND {$mg}.entity_table = '{$group}'\n WHERE \n {$mg}.group_type = 'Include'\n AND {$mg}.mailing_id = {$this->id}\n AND {$group}.saved_search_id IS NOT null"); $whereTables = array(); while ($ss->fetch()) { $tables = array($contact => 1, $location => 1, $email => 1); $where = CRM_Contact_BAO_SavedSearch::whereClause($ss->saved_search_id, $tables, $whereTables); $from = CRM_Contact_BAO_Query::fromClause($tables); $ssq = "INSERT IGNORE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id \n {$from}\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$location}.is_primary = 1\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$where}\n AND X_{$job_id}.contact_id IS null "; $mailingGroup->query($ssq); } /* Get the emails with only location override */ $mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as local_email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_id = {$contact}.id\n AND {$location}.entity_table = '{$contact}'\n INNER JOIN {$g2contact}\n ON {$contact}.id = {$g2contact}.contact_id\n AND {$location}.id = {$g2contact}.location_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.location_id IS NOT null\n AND {$g2contact}.email_id is null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$email}.is_primary = 1\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); /* Get the emails with full override */ $mailingGroup->query("REPLACE INTO I_{$job_id} (email_id, contact_id)\n SELECT DISTINCT {$email}.id as email_id,\n {$contact}.id as contact_id\n FROM {$email}\n INNER JOIN {$g2contact}\n ON {$email}.id = {$g2contact}.email_id\n INNER JOIN {$contact}\n ON {$contact}.id = {$g2contact}.contact_id\n INNER JOIN {$mg}\n ON {$g2contact}.group_id = {$mg}.entity_id\n LEFT JOIN X_{$job_id}\n ON {$contact}.id = X_{$job_id}.contact_id\n WHERE \n {$mg}.entity_table = '{$group}'\n AND {$mg}.group_type = 'Include'\n AND {$g2contact}.status = 'Added'\n AND {$g2contact}.location_id IS NOT null\n AND {$g2contact}.email_id IS NOT null\n AND {$contact}.do_not_email = 0\n AND {$contact}.is_opt_out = 0\n AND {$email}.on_hold = 0\n AND {$mg}.mailing_id = {$this->id}\n AND X_{$job_id}.contact_id IS null"); $results = array(); $eq =& new CRM_Mailing_Event_BAO_Queue(); $eq->query("SELECT contact_id, email_id \n FROM I_{$job_id} \n ORDER BY contact_id, email_id"); /* Delete the temp table */ $mailingGroup->reset(); $mailingGroup->query("DROP TEMPORARY TABLE X_{$job_id}"); $mailingGroup->query("DROP TEMPORARY TABLE I_{$job_id}"); return $eq; }