From 2b95a25b82177c6c2dacdac22a603c31d1a5245b Mon Sep 17 00:00:00 2001 From: jernic Date: Sun, 11 Sep 2016 23:03:06 +0200 Subject: [PATCH] Select correct activity if more than one in upcoming or recent period Currently if there are two or more "upcoming" or "recent" activities, the wrong activity is being selected. Changed the SQL query to make sure that the earliest upcoming activity, and the latest recent activity is selected. --- CRM/Case/BAO/Case.php | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) diff --git a/CRM/Case/BAO/Case.php b/CRM/Case/BAO/Case.php index 3af84c56a4..00f09efc55 100644 --- a/CRM/Case/BAO/Case.php +++ b/CRM/Case/BAO/Case.php @@ -3001,8 +3001,12 @@ WHERE id IN (' . implode(',', $copiedActivityIds) . ')'; AS SELECT ca.case_id, a.id, a.activity_date_time, a.status_id, a.activity_type_id FROM civicrm_case_activity ca INNER JOIN civicrm_activity a ON ca.activity_id=a.id - WHERE a.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY ) - AND a.is_current_revision = 1 AND a.is_deleted=0 AND a.status_id = $scheduled_id"; + WHERE a.activity_date_time = +(SELECT b.activity_date_time FROM civicrm_case_activity bca + INNER JOIN civicrm_activity b ON bca.activity_id=b.id + WHERE b.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY ) + AND b.is_current_revision = 1 AND b.is_deleted=0 AND b.status_id = $scheduled_id + AND bca.case_id = ca.case_id ORDER BY b.activity_date_time ASC LIMIT 1)"; break; case 'recent': @@ -3010,9 +3014,12 @@ WHERE id IN (' . implode(',', $copiedActivityIds) . ')'; AS SELECT ca.case_id, a.id, a.activity_date_time, a.status_id, a.activity_type_id FROM civicrm_case_activity ca INNER JOIN civicrm_activity a ON ca.activity_id=a.id - WHERE a.activity_date_time <= NOW() - AND a.activity_date_time >= DATE_SUB( NOW(), INTERVAL 14 DAY ) - AND a.is_current_revision = 1 AND a.is_deleted=0 AND a.status_id <> $scheduled_id"; + WHERE a.activity_date_time = +(SELECT b.activity_date_time FROM civicrm_case_activity bca + INNER JOIN civicrm_activity b ON bca.activity_id=b.id + WHERE b.activity_date_time >= DATE_SUB( NOW(), INTERVAL 14 DAY ) + AND b.is_current_revision = 1 AND b.is_deleted=0 AND b.status_id <> $scheduled_id + AND bca.case_id = ca.case_id ORDER BY b.activity_date_time DESC LIMIT 1)"; break; } return $sql; -- 2.25.1