From a27c90f27ca249e385b6b3c11241c29309fbe7d9 Mon Sep 17 00:00:00 2001 From: Nicolas Ganivet Date: Mon, 26 Aug 2013 16:21:13 -0600 Subject: [PATCH] Full fix for CRM-13293. Also includes query optimization in where() function. --- CRM/Report/Form/Contribute/Lybunt.php | 56 +++++++++++++++++++-- CRM/Report/Form/Contribute/Sybunt.php | 70 +++++++++++++++++++++++---- 2 files changed, 112 insertions(+), 14 deletions(-) diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index 870fd4e223..44c21ad122 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -124,6 +124,32 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { ), ), ), + 'civicrm_address' => + array( + 'dao' => 'CRM_Core_DAO_Address', + 'grouping' => 'contact-fields', + 'fields' => + array( + 'street_address' => array('default' => TRUE), + 'city' => array('default' => TRUE), + 'postal_code' => NULL, + 'state_province_id' => array('title' => ts('State/Province')), + 'country_id' => array('title' => ts('Country')), + ), + 'filters' => + array( + 'country_id' => + array('title' => ts('Country'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Core_PseudoConstant::country(), + ), + 'state_province_id' => + array('title' => ts('State / Province'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Core_PseudoConstant::stateProvince(), + ), + ), + ), 'civicrm_contribution' => array( 'dao' => 'CRM_Contribute_DAO_Contribution', @@ -259,13 +285,26 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { FROM civicrm_contribution {$this->_aliases['civicrm_contribution']} INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id - {$this->_aclFrom} + {$this->_aclFrom}"; + + if ($this->isTableSelected('civicrm_email')) { + $this->_from .= " LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND - {$this->_aliases['civicrm_email']}.is_primary = 1 + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id + AND {$this->_aliases['civicrm_email']}.is_primary = 1"; + } + if ($this->isTableSelected('civicrm_phone')) { + $this->_from .= " LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND - {$this->_aliases['civicrm_phone']}.is_primary = 1 "; + {$this->_aliases['civicrm_phone']}.is_primary = 1"; + } + if ($this->isTableSelected('civicrm_address')) { + $this->_from .= " + LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND + {$this->_aliases['civicrm_address']}.is_primary = 1"; + } } function where() { @@ -470,6 +509,15 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { $entryFound = TRUE; } } + + if ($value = CRM_Utils_Array::value('civicrm_address_state_province_id', $row)) { + $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); + $entryFound = TRUE; + } + if ($value = CRM_Utils_Array::value('civicrm_address_country_id', $row)) { + $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); + $entryFound = TRUE; + } } } diff --git a/CRM/Report/Form/Contribute/Sybunt.php b/CRM/Report/Form/Contribute/Sybunt.php index 42b6f3653c..d39c486ecc 100644 --- a/CRM/Report/Form/Contribute/Sybunt.php +++ b/CRM/Report/Form/Contribute/Sybunt.php @@ -120,6 +120,34 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { ), ), ), + 'civicrm_address' => + array( + 'dao' => 'CRM_Core_DAO_Address', + 'grouping' => 'contact-fields', + 'fields' => + array( + 'street_address' => array('default' => TRUE), + 'city' => array('default' => TRUE), + 'postal_code' => NULL, + 'state_province_id' => array('title' => ts('State/Province')), + 'country_id' => array('title' => ts('Country')), + ), + 'filters' => + array( + 'country_id' => + array( + 'title' => ts('Country'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Core_PseudoConstant::country(), + ), + 'state_province_id' => + array( + 'title' => ts('State / Province'), + 'operatorType' => CRM_Report_Form::OP_MULTISELECT, + 'options' => CRM_Core_PseudoConstant::stateProvince(), + ), + ), + ), 'civicrm_contribution' => array( 'dao' => 'CRM_Contribute_DAO_Contribution', @@ -260,17 +288,30 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { function from() { - $this->_from = " + $this->_from = " FROM civicrm_contribution {$this->_aliases['civicrm_contribution']} - INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id - {$this->_aclFrom} - LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id - AND {$this->_aliases['civicrm_email']}.is_primary = 1 - LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND - {$this->_aliases['civicrm_phone']}.is_primary = 1 "; + INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id + {$this->_aclFrom}"; + + if ($this->isTableSelected('civicrm_email')) { + $this->_from .= " + LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id + AND {$this->_aliases['civicrm_email']}.is_primary = 1"; + } + if ($this->isTableSelected('civicrm_phone')) { + $this->_from .= " + LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND + {$this->_aliases['civicrm_phone']}.is_primary = 1"; + } + if ($this->isTableSelected('civicrm_address')) { + $this->_from .= " + LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND + {$this->_aliases['civicrm_address']}.is_primary = 1"; + } } function where() { @@ -484,6 +525,15 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { $entryFound = TRUE; } } + + if ($value = CRM_Utils_Array::value('civicrm_address_state_province_id', $row)) { + $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); + $entryFound = TRUE; + } + if ($value = CRM_Utils_Array::value('civicrm_address_country_id', $row)) { + $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); + $entryFound = TRUE; + } } } -- 2.25.1