From 389cfee738061043c53775e13c2c6c42ea7067f7 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Fri, 13 Dec 2013 22:02:48 +0530 Subject: [PATCH] CRM-13738 related fix ---------------------------------------- * CRM-13738: Activity Report fails if filtering on any contact http://issues.civicrm.org/jira/browse/CRM-13738 --- CRM/Report/Form/Activity.php | 84 ++++++++++++++---------------------- 1 file changed, 32 insertions(+), 52 deletions(-) diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index 1d42d0b158..8e7b3a9086 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -465,7 +465,7 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { } else { $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); - if ($op) { + if ($op && ($op != 'nnll' || $op != 'nll')) { $clause = $this->whereClause($field, $op, CRM_Utils_Array::value("{$fieldName}_value", $this->_params), @@ -480,7 +480,8 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { // get current user $session = CRM_Core_Session::singleton(); if ($contactID = $session->get('userID')) { - $clause = "civicrm_contact_{$recordType}.id = " . $contactID; + $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN + (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})"; } else { $clause = NULL; @@ -543,28 +544,23 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { $this->buildACLClause(array('civicrm_contact_source', 'civicrm_contact_target', 'civicrm_contact_assignee')); $this->beginPostProcess(); - //Assign those recordtype to array which have filter value or operator as 'Is not empty' - //which will be further used for where clause buildup of temp tables - $sortnameFilters = array(); + //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty' + $nullFilters = array(); foreach (array('target', 'source', 'assignee') as $type) { - if (CRM_Utils_Array::value("contact_{$type}_value", $this->_params) || - CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nnll' - ) { - $sortnameFilters[$type] = 1; + if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nnll' || + CRM_Utils_Array::value("contact_{$type}_value", $this->_params)) { + $nullFilters[] = " civicrm_contact_contact_{$type} IS NOT NULL "; + } + else if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nll') { + $nullFilters[] = " civicrm_contact_contact_{$type} IS NULL "; } } - // 1. fill temp table with target results + // 1. fill temp table with target results $this->select('target'); $this->from('target'); $this->customDataFrom(); - if (!empty($sortnameFilters) && !array_key_exists('target', $sortnameFilters) - ) { - $this->_where = "WHERE FALSE"; - } - else { - $this->where('target'); - } + $this->where('target'); $insertCols = implode(',', $this->_selectAliases); $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS {$this->_select} {$this->_from} {$this->_where} "; @@ -583,55 +579,39 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { CRM_Core_DAO::executeQuery($tempQuery); // 3. fill temp table with assignee results - if (CRM_Utils_Array::value("contact_assignee", $this->_params['fields']) || - CRM_Utils_Array::value("contact_assignee_email", $this->_params['fields']) || - CRM_Utils_Array::value("contact_assignee_value", $this->_params)) { - $this->select('assignee'); - $this->from('assignee'); - $this->customDataFrom(); - if (!empty($sortnameFilters) && !array_key_exists('assignee', $sortnameFilters) - ) { - $this->_where = "WHERE FALSE"; - } - else { - $this->where('assignee'); - } - $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) + $this->select('assignee'); + $this->from('assignee'); + $this->customDataFrom(); + $this->where('assignee'); + $insertCols = implode(',', $this->_selectAliases); + $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) {$this->_select} {$this->_from} {$this->_where}"; - CRM_Core_DAO::executeQuery($tempQuery); - } + CRM_Core_DAO::executeQuery($tempQuery); // 4. fill temp table with source results - if (CRM_Utils_Array::value("contact_source", $this->_params['fields']) || - CRM_Utils_Array::value("contact_source_email", $this->_params['fields']) || - CRM_Utils_Array::value("contact_source_value", $this->_params)) { - $this->select('source'); - $this->from('source'); - $this->customDataFrom(); - if (!empty($sortnameFilters) && !array_key_exists('source', $sortnameFilters) - ) { - $this->_where = "WHERE FALSE"; - } - else { - $this->where('source'); - } - $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) + $this->select('source'); + $this->from('source'); + $this->customDataFrom(); + $this->where('source'); + $insertCols = implode(',', $this->_selectAliases); + $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) {$this->_select} {$this->_from} {$this->_where}"; - CRM_Core_DAO::executeQuery($tempQuery); - } + CRM_Core_DAO::executeQuery($tempQuery); // 5. show final result set from temp table $rows = array(); $this->select('final'); + $this->_having = ""; + if (!empty($nullFilters)) { + $this->_having = "HAVING " . implode(' AND ', $nullFilters); + } $this->orderBy(); $this->limit(); $sql = "{$this->_select} FROM civireport_activity_temp_target tar -GROUP BY civicrm_activity_id {$this->_orderBy} {$this->_limit}"; +GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}"; $this->buildRows($sql, $rows); // format result set. -- 2.25.1