From e3c74b61451b4d8991ac054b11331a45e7680834 Mon Sep 17 00:00:00 2001 From: eileen Date: Thu, 22 Mar 2018 13:31:06 +1300 Subject: [PATCH] CRM-21677 - reduce unnecessary address joins on activity & contact detail reports --- CRM/Report/Form.php | 87 +++++++++++++++++++++++++++++ CRM/Report/Form/Activity.php | 2 +- CRM/Report/Form/ActivitySummary.php | 14 +---- CRM/Report/Form/Contact/Detail.php | 33 ++--------- CRM/Report/Form/Contact/Summary.php | 33 +---------- 5 files changed, 97 insertions(+), 72 deletions(-) diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index d9de18c085..98b0ed3e0e 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -4334,6 +4334,93 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a } } + /** + * Add Address into From Table if required. + * + * Prefix will be added to both tables as + * it is assumed you are using it to get address of a secondary contact. + * + * @param string $prefix + * @param array $extra Additional options. + * Not currently used in core but may be used in override extensions. + */ + protected function joinAddressFromContact($prefix = '', $extra = array()) { + $addressTables = ['civicrm_address', 'civicrm_country', 'civicrm_worldregion', 'civicrm_state_province']; + $isJoinRequired = $this->_addressField; + foreach ($addressTables as $addressTable) { + if ($this->isTableSelected($prefix . $addressTable)) { + $isJoinRequired = TRUE; + } + } + if ($isJoinRequired) { + $this->_from .= " + LEFT JOIN civicrm_address {$this->_aliases[$prefix . 'civicrm_address']} + ON ({$this->_aliases[$prefix . 'civicrm_contact']}.id = + {$this->_aliases[$prefix . 'civicrm_address']}.contact_id) AND + {$this->_aliases[$prefix . 'civicrm_address']}.is_primary = 1\n"; + } + } + + /** + * Add Country into From Table if required. + * + * Prefix will be added to both tables as + * it is assumed you are using it to get address of a secondary contact. + * + * @param string $prefix + * @param array $extra Additional options. + * Not currently used in core but may be used in override extensions. + */ + protected function joinCountryFromAddress($prefix = '', $extra = array()) { + // include country field if country column is to be included + if ($this->isTableSelected($prefix . 'civicrm_country')) { + $this->_from .= " + LEFT JOIN civicrm_country {$this->_aliases[$prefix . 'civicrm_country']} + ON {$this->_aliases[$prefix . 'civicrm_address']}.country_id = {$this->_aliases[$prefix . 'civicrm_country']}.id AND + {$this->_aliases[$prefix . 'civicrm_address']}.is_primary = 1 "; + } + } + + /** + * Add Phone into From Table if required. + * + * Prefix will be added to both tables as + * it is assumed you are using it to get address of a secondary contact. + * + * @param string $prefix + * @param array $extra Additional options. + * Not currently used in core but may be used in override extensions. + */ + protected function joinPhoneFromContact($prefix = '', $extra = array()) { + // include phone field if phone column is to be included + if ($this->isTableSelected($prefix . 'civicrm_phone')) { + $this->_from .= " + LEFT JOIN civicrm_phone {$this->_aliases[$prefix . 'civicrm_phone']} + ON {$this->_aliases[$prefix . 'civicrm_contact']}.id = {$this->_aliases[$prefix . 'civicrm_phone']}.contact_id AND + {$this->_aliases[$prefix . 'civicrm_phone']}.is_primary = 1\n"; + } + } + + /** + * Add Email into From Table if required. + * + * Prefix will be added to both tables as + * it is assumed you are using it to get address of a secondary contact. + * + * @param string $prefix + * @param array $extra Additional options. + * Not currently used in core but may be used in override extensions. + */ + protected function joinEmailFromContact($prefix = '', $extra = array()) { + // include email field if email column is to be included + if ($this->isTableSelected($prefix . 'civicrm_email')) { + $this->_from .= " + LEFT JOIN civicrm_email {$this->_aliases[$prefix . 'civicrm_email']} + ON ({$this->_aliases[$prefix . 'civicrm_contact']}.id = {$this->_aliases[$prefix . 'civicrm_email']}.contact_id AND + {$this->_aliases[$prefix . 'civicrm_email']}.is_primary = 1) "; + } + } + /** * Add Financial Transaction into From Table if required. */ diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index dd52e784aa..8532ae9717 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -582,7 +582,7 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { $this->_aliases['civicrm_contact'] = 'civicrm_contact_source'; } - $this->addAddressFromClause(); + $this->joinAddressFromContact(); } /** diff --git a/CRM/Report/Form/ActivitySummary.php b/CRM/Report/Form/ActivitySummary.php index 9ce524b30f..29960096af 100644 --- a/CRM/Report/Form/ActivitySummary.php +++ b/CRM/Report/Form/ActivitySummary.php @@ -337,12 +337,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { LEFT JOIN civicrm_case_contact ON civicrm_case_contact.case_id = civicrm_case.id "; - if ($this->_phoneField) { - $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->joinPhoneFromContact(); } else { $this->_from = " @@ -355,12 +350,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { {$this->_aclFrom}"; } - if ($this->_emailField) { - $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 "; - } + $this->joinEmailFromContact(); } /** diff --git a/CRM/Report/Form/Contact/Detail.php b/CRM/Report/Form/Contact/Detail.php index 8ddef41d96..c16977d5c7 100644 --- a/CRM/Report/Form/Contact/Detail.php +++ b/CRM/Report/Form/Contact/Detail.php @@ -457,35 +457,10 @@ class CRM_Report_Form_Contact_Detail extends CRM_Report_Form { $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}"; - if ($this->isTableSelected('civicrm_country') || - $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 ) "; - } - - 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_country')) { - $this->_from .= " - LEFT JOIN civicrm_country {$this->_aliases['civicrm_country']} - ON {$this->_aliases['civicrm_address']}.country_id = {$this->_aliases['civicrm_country']}.id AND - {$this->_aliases['civicrm_address']}.is_primary = 1 "; - } + $this->joinAddressFromContact(); + $this->joinCountryFromAddress(); + $this->joinPhoneFromContact(); + $this->joinEmailFromContact(); $this->_from .= "{$group}"; diff --git a/CRM/Report/Form/Contact/Summary.php b/CRM/Report/Form/Contact/Summary.php index a37ce608d5..706d992981 100644 --- a/CRM/Report/Form/Contact/Summary.php +++ b/CRM/Report/Form/Contact/Summary.php @@ -151,15 +151,6 @@ class CRM_Report_Form_Contact_Summary extends CRM_Report_Form { if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName]) ) { - if ($tableName == 'civicrm_email') { - $this->_emailField = TRUE; - } - elseif ($tableName == 'civicrm_phone') { - $this->_phoneField = TRUE; - } - elseif ($tableName == 'civicrm_country') { - $this->_countryField = TRUE; - } $alias = "{$tableName}_{$fieldName}"; $select[] = "{$field['dbAlias']} as {$alias}"; @@ -192,27 +183,9 @@ class CRM_Report_Form_Contact_Summary extends CRM_Report_Form { 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 ) "; - - 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->_phoneField) { - $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_country')) { - $this->_from .= " - LEFT JOIN civicrm_country {$this->_aliases['civicrm_country']} - ON {$this->_aliases['civicrm_address']}.country_id = {$this->_aliases['civicrm_country']}.id AND - {$this->_aliases['civicrm_address']}.is_primary = 1 "; - } + $this->joinPhoneFromContact(); + $this->joinEmailFromContact(); + $this->joinCountryFromAddress(); } public function postProcess() { -- 2.25.1