Author Topic: CiviCase upcoming/recent activity listing error  (Read 968 times)

Offline jern

  • I post occasionally
  • **
  • Posts: 46
  • Karma: 0
    • SEN Magazine
  • CiviCRM version: 4.5.8
  • CMS version: Joomla 2.5.28
  • MySQL version: 5.5.40
  • PHP version: 5.4.33
CiviCase upcoming/recent activity listing error
« on: September 05, 2016, 01:15:06 am »
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:

Code: [Select]
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:

Code: [Select]
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;
    }



Offline jern

  • I post occasionally
  • **
  • Posts: 46
  • Karma: 0
    • SEN Magazine
  • CiviCRM version: 4.5.8
  • CMS version: Joomla 2.5.28
  • MySQL version: 5.5.40
  • PHP version: 5.4.33
Re: CiviCase upcoming/recent activity listing error
« Reply #1 on: September 05, 2016, 05:19:50 am »
Just realised how I should have reported this error, so please ignore this. I have filed a bug report here: https://issues.civicrm.org/jira/browse/CRM-19312.