CiviCRM Community Forums (archive)

Hi, I noticed a bug in the definition of upcoming activities in com_civicrm/civicrm/CRM/Case/BAO/Case.php - if there are two (or more) activities which match the criteria for upcoming activities, the later activity is chosen. This is wrong - the user would expect to see the upcoming activity which will occur soonest. A corresponding error probably exists in the definition of the most recent activity - I didn't check this, but I made the corresponding change anyway.

I made the following change at line 3000 of the above file (version 4.7.10). It may not be the most efficient query, but it seems to work:

Changed from:

switch ($section) {
      case 'upcoming':
        $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_upcoming`
 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 = 1";
        break;

      case 'recent':
        $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_recent`
 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 <> 1";
        break;
    }
To:

switch ($section) {
      case 'upcoming':
        $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_upcoming`
 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 =
(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 = 1
 AND bca.case_id = ca.case_id ORDER BY b.activity_date_time ASC LIMIT 1)";
        break;

      case 'recent':
        $sql = "CREATE OR REPLACE VIEW `civicrm_view_case_activity_recent`
 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 =
(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 <> 1
 AND bca.case_id = ca.case_id ORDER BY b.activity_date_time DESC LIMIT 1)";
        break;
    }