<?php
/*
+--------------------------------------------------------------------+
- | CiviCRM version 4.3 |
+ | CiviCRM version 4.4 |
+--------------------------------------------------------------------+
| Copyright (C) 2011 Marty Wright |
| Licensed to CiviCRM under the Academic Free License version 3.0. |
}
}
-
return array(
'sel1' => $sel1,
'sel2' => $sel2,
$session = CRM_Core_Session::singleton();
while ($actionSchedule->fetch()) {
- $extraSelect = $extraJoin = $extraWhere = '';
+ $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
if ($actionSchedule->record_activity) {
if ($mapping->entity == 'civicrm_membership') {
$extraJoin = "
INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
- $extraWhere = 'AND e.is_current_revision = 1 AND e.is_deleted = 0';
+ $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
if ($actionSchedule->limit_to == 0) {
$extraJoin = "
LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
- $extraWhere = '';
}
}
$entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
$extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
}
+ $entityJoinClause .= $extraOn;
$query = "
SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.*, e.id as entityID, e.* {$extraSelect}
$mapping->id = $mappingID;
$mapping->find(TRUE);
- $select = $join = $where = array();
+ // 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)
$status = implode(',', $status);
if (!CRM_Utils_System::isNull($mapping->entity_recipient)) {
- $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient);
+ $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name');
}
$from = "{$mapping->entity} e";
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
- 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;
+ 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;
- default:
- case 'Activity Targets':
- $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
- 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)) {
$table = 'civicrm_event r';
$contactField = 'e.contact_id';
$join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
- if ($actionSchedule->recipient_listing) {
+ 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':
+ case 'participant_role':
$where[] = "e.role_id IN ({$rList})";
break;
$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)) {
- $where[] = "e.status_id IN ({$status})";
+ $limitWhere[] = "e.status_id IN ({$status})";
}
$where[] = 'r.is_active = 1';
$membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
$mStatus = implode (',', $membershipStatus);
$where[] = "e.status_id IN ({$mStatus})";
-
}
+
if ($limitTo) {
if ($actionSchedule->group_id) {
$join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
$addWhere = "c.id IN ({$rList})";
}
}
+
$select[] = "{$contactField} as contact_id";
$select[] = 'e.id as entity_id';
$select[] = "'{$mapping->entity}' as entity_table";
$fromClause = "FROM $from";
$joinClause = !empty($join) ? implode(' ', $join) : '';
$whereClause = 'WHERE ' . implode(' AND ', $where);
- $union = '';
-
- if ($limitTo == 0) {
- $union ="
-UNION
- SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id
-FROM (civicrm_contact c, {$table})
-LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = c.id AND
- reminder.entity_id = c.id AND
- reminder.entity_table = 'civicrm_contact' AND
- reminder.action_schedule_id = {$actionSchedule->id}
-{$addGroup}
-WHERE (reminder.id IS NULL AND c.is_deleted = 0 AND c.is_deceased = 0 AND {$addWhere}) AND
- {$dateClause}";
- }
+ $limitWhereClause = '';
+ if (!empty($limitWhere)) {
+ $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
+ }
+
$query = "
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
{$selectClause}
{$fromClause}
{$joinClause}
LEFT JOIN {$reminderJoinClause}
-{$whereClause} AND {$dateClause} {$notINClause}
-{$union}";
-
+{$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
+";
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
+ reminder.entity_id = c.id AND
+ reminder.entity_table = 'civicrm_contact' AND
+ reminder.action_schedule_id = {$actionSchedule->id}";
+
+ $insertAdditionalSql ="
+INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
+{$addSelect}
+FROM ({$contactTable}, {$table})
+LEFT JOIN {$additionReminderClause}
+{$addGroup}
+{$additionWhere} c.is_deleted = 0 AND c.is_deceased = 0 AND {$addWhere}
+AND {$dateClause}
+AND c.id NOT IN (
+ SELECT rem.contact_id
+ FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
+ WHERE rem.action_schedule_id = {$actionSchedule->id}
+ AND rem.entity_table = '{$mapping->entity}'
+ )
+GROUP BY c.id
+";
+ 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})";
$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}
{$fromClause}
{$joinClause}
INNER JOIN {$reminderJoinClause}
-{$whereClause} AND {$repeatEventClause}
+{$whereClause} {$limitWhereClause} AND {$repeatEventClause}
{$groupByClause}
{$havingClause}";
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 = "
+SELECT * FROM {$table}
+{$whereClause} AND {$repeatEventClause} LIMIT 1";
+
+ $daoCheck = CRM_Core_DAO::executeQuery($sqlEndEventCheck);
+ if ($daoCheck->fetch()) {
+ $valSqlAdditionInsert = "
+{$addSelect}
+FROM {$contactTable}
+{$addGroup}
+INNER JOIN {$additionReminderClause}
+WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
+GROUP BY reminder.contact_id
+{$havingClause}
+";
+ $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);
+ }
+ }
+ }
}
}
}
if (!CRM_Utils_Array::value($recipientType, $eventContacts)) {
return $options;
}
- if ($eventContacts[$recipientType] == 'Participant Role') {
+ if ($eventContacts[$recipientType] == 'participant_role') {
$options = CRM_Event_PseudoConstant::participantRole();
}
break;