Details
Description
Based on decision with Dave D implement below query for activity grid on Manage Case screen.
SELECT ca.id as id, ca.activity_type_id as type, cc.sort_name as reporter,
IF(COALESCE(ca.activity_date_time, ca.due_date_time) < NOW() AND ca.status_id=ov.value,
COALESCE(ca.activity_date_time, ca.due_date_time),
DATE_ADD(NOW(), INTERVAL 1 YEAR)
) as overdue_date,
COALESCE(ca.activity_date_time, ca.due_date_time) as display_date,
ca.status_id as status, ca.subject as subject, ca.is_deleted as deleted
FROM civicrm_case_activity cca INNER JOIN civicrm_activity ca ON ca.id = cca.activity_id
INNER JOIN civicrm_contact cc ON cc.id = ca.source_contact_id
LEFT OUTER JOIN civicrm_option_group og ON og.name='activity_status'
LEFT OUTER JOIN civicrm_option_value ov ON ov.option_group_id=og.id AND ov.name='Scheduled'
WHERE cca.case_id=%1 AND ca.is_current_revision = 1 AND ca.is_deleted = 0
ORDER BY overdue_date ASC, display_date DESC LIMIT 0, 10