From f90f330b95643447a82090e810e73174d50e00a4 Mon Sep 17 00:00:00 2001 From: eileen Date: Thu, 27 Feb 2020 14:51:13 +1300 Subject: [PATCH] Remove unused columns from civicrm_saved_search We identified these as unused in 5.20 & removed the places still saving to them - https://github.com/civicrm/civicrm-core/pull/14891 This removes the actual columns. A couple of other tables still have similar columns, not sure if they are required still --- CRM/Contact/DAO/SavedSearch.php | 58 +------------------ .../Incremental/php/FiveTwentyFour.php | 19 +++++- CRM/Upgrade/Incremental/php/FourThree.php | 13 ----- api/v3/SavedSearch.php | 16 +++-- api/v3/examples/SavedSearch/Create.ex.php | 6 -- .../SavedSearchDataSets/242_email_on_hold.sql | 2 +- .../251_empty_email_on_hold.sql | 2 +- .../801_pre44_billing_email_smartgroup.sql | 2 +- .../94_legacy_state_province.sql | 4 +- xml/schema/Contact/SavedSearch.xml | 3 + 10 files changed, 37 insertions(+), 88 deletions(-) diff --git a/CRM/Contact/DAO/SavedSearch.php b/CRM/Contact/DAO/SavedSearch.php index ce0af2a050..5933323c54 100644 --- a/CRM/Contact/DAO/SavedSearch.php +++ b/CRM/Contact/DAO/SavedSearch.php @@ -6,7 +6,7 @@ * * Generated from xml/schema/CRM/Contact/SavedSearch.xml * DO NOT EDIT. Generated by CRM_Core_CodeGen - * (GenCodeChecksum:c0d6f65e0f3115ac3349b5fb1ad0baac) + * (GenCodeChecksum:d2d8b39f5f8869d8c5295810f8f2ad78) */ /** @@ -56,27 +56,6 @@ class CRM_Contact_DAO_SavedSearch extends CRM_Core_DAO { */ public $search_custom_id; - /** - * the sql where clause if a saved search acl - * - * @var text - */ - public $where_clause; - - /** - * the tables to be included in a select data - * - * @var text - */ - public $select_tables; - - /** - * the tables to be included in the count statement - * - * @var text - */ - public $where_tables; - /** * Class constructor. */ @@ -157,41 +136,6 @@ class CRM_Contact_DAO_SavedSearch extends CRM_Core_DAO { 'bao' => 'CRM_Contact_BAO_SavedSearch', 'localizable' => 0, ], - 'where_clause' => [ - 'name' => 'where_clause', - 'type' => CRM_Utils_Type::T_TEXT, - 'title' => ts('Where Clause'), - 'description' => ts('the sql where clause if a saved search acl'), - 'where' => 'civicrm_saved_search.where_clause', - 'table_name' => 'civicrm_saved_search', - 'entity' => 'SavedSearch', - 'bao' => 'CRM_Contact_BAO_SavedSearch', - 'localizable' => 0, - ], - 'select_tables' => [ - 'name' => 'select_tables', - 'type' => CRM_Utils_Type::T_TEXT, - 'title' => ts('Select Tables'), - 'description' => ts('the tables to be included in a select data'), - 'where' => 'civicrm_saved_search.select_tables', - 'table_name' => 'civicrm_saved_search', - 'entity' => 'SavedSearch', - 'bao' => 'CRM_Contact_BAO_SavedSearch', - 'localizable' => 0, - 'serialize' => self::SERIALIZE_PHP, - ], - 'where_tables' => [ - 'name' => 'where_tables', - 'type' => CRM_Utils_Type::T_TEXT, - 'title' => ts('Where Tables'), - 'description' => ts('the tables to be included in the count statement'), - 'where' => 'civicrm_saved_search.where_tables', - 'table_name' => 'civicrm_saved_search', - 'entity' => 'SavedSearch', - 'bao' => 'CRM_Contact_BAO_SavedSearch', - 'localizable' => 0, - 'serialize' => self::SERIALIZE_PHP, - ], ]; CRM_Core_DAO_AllCoreTables::invoke(__CLASS__, 'fields_callback', Civi::$statics[__CLASS__]['fields']); } diff --git a/CRM/Upgrade/Incremental/php/FiveTwentyFour.php b/CRM/Upgrade/Incremental/php/FiveTwentyFour.php index dc96840a26..a84ea286b6 100644 --- a/CRM/Upgrade/Incremental/php/FiveTwentyFour.php +++ b/CRM/Upgrade/Incremental/php/FiveTwentyFour.php @@ -60,6 +60,7 @@ class CRM_Upgrade_Incremental_php_FiveTwentyFour extends CRM_Upgrade_Incremental public function upgrade_5_24_alpha1($rev) { $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev); $this->addTask('Install sequential creditnote extension', 'installCreditNotes'); + $this->addTask('Drop obsolete columns from saved_searc table', 'dropSavedSearchColumns'); } /** @@ -78,8 +79,20 @@ class CRM_Upgrade_Incremental_php_FiveTwentyFour extends CRM_Upgrade_Incremental return TRUE; } - // public static function taskFoo(CRM_Queue_TaskContext $ctx, ...) { - // return TRUE; - // } + /** + * Delete unused columns from civicrm_saved_search. + * + * Follow up on https://github.com/civicrm/civicrm-core/pull/14891 + * + * @param \CRM_Queue_TaskContext $ctx + * + * @return bool + */ + public static function dropSavedSearchColumns(CRM_Queue_TaskContext $ctx) { + self::dropColumn($ctx, 'civicrm_saved_search', 'select_tables'); + self::dropColumn($ctx, 'civicrm_saved_search', 'where_tables'); + self::dropColumn($ctx, 'civicrm_saved_search', 'where_clause'); + return TRUE; + } } diff --git a/CRM/Upgrade/Incremental/php/FourThree.php b/CRM/Upgrade/Incremental/php/FourThree.php index 9883c425a3..eabdbe968e 100644 --- a/CRM/Upgrade/Incremental/php/FourThree.php +++ b/CRM/Upgrade/Incremental/php/FourThree.php @@ -1188,19 +1188,6 @@ AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $v } } } - foreach (['select_tables', 'where_tables'] as $value) { - if (preg_match('/contribution_type/', $dao->$value)) { - $tempValue = unserialize($dao->$value); - if (array_key_exists('civicrm_contribution_type', $tempValue)) { - $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type']; - unset($tempValue['civicrm_contribution_type']); - } - $saveDao->$value = serialize($tempValue); - } - } - if (preg_match('/contribution_type/', $dao->where_clause)) { - $saveDao->where_clause = preg_replace('/contribution_type/', 'financial_type', $dao->where_clause); - } } $saveDao->form_values = serialize($formValues); diff --git a/api/v3/SavedSearch.php b/api/v3/SavedSearch.php index 14a6f59983..1381022019 100644 --- a/api/v3/SavedSearch.php +++ b/api/v3/SavedSearch.php @@ -37,15 +37,17 @@ * * @param array $params * Associative array of property name-value pairs to insert in new saved search. - * @example SavedSearch/Create.php Std create example. + * * @return array * api result array * {@getfields saved_search_create} + * + * @throws \API_Exception + * + * @example SavedSearch/Create.php Std create example. * @access public */ function civicrm_api3_saved_search_create($params) { - civicrm_api3_verify_one_mandatory($params, NULL, ['form_values', 'where_clause']); - $result = _civicrm_api3_basic_create(_civicrm_api3_get_BAO(__FUNCTION__), $params, 'SavedSearch'); _civicrm_api3_saved_search_result_cleanup($result); return $result; @@ -56,6 +58,7 @@ function civicrm_api3_saved_search_create($params) { */ function _civicrm_api3_saved_search_create_spec(&$fields) { $fields['form_values']['api.aliases'][] = 'formValues'; + $fields['form_values']['api.required'] = TRUE; } /** @@ -64,10 +67,15 @@ function _civicrm_api3_saved_search_create_spec(&$fields) { * @param array $params * Associative array of property name-value pairs. $params['id'] should be * the ID of the saved search to be deleted. - * @example SavedSearch/Delete.php Std delete example. + * * @return array * api result array * {@getfields saved_search_delete} + * + * @throws \API_Exception + * @throws \CiviCRM_API3_Exception + * + * @example SavedSearch/Delete.php Std delete example. * @access public */ function civicrm_api3_saved_search_delete($params) { diff --git a/api/v3/examples/SavedSearch/Create.ex.php b/api/v3/examples/SavedSearch/Create.ex.php index 4667b1f808..8edb9f8a62 100644 --- a/api/v3/examples/SavedSearch/Create.ex.php +++ b/api/v3/examples/SavedSearch/Create.ex.php @@ -64,9 +64,6 @@ function saved_search_create_expectedresult() { ], 'mapping_id' => '', 'search_custom_id' => '', - 'where_clause' => '', - 'select_tables' => '', - 'where_tables' => '', 'api.Group.create' => [ 'is_error' => 0, 'version' => 3, @@ -82,9 +79,6 @@ function saved_search_create_expectedresult() { 'saved_search_id' => '3', 'is_active' => '1', 'visibility' => 'User and User Admin Only', - 'where_clause' => ' ( ( ( civicrm_group_contact_cache_5d5bbe284d3e9.group_id IN (\"1\") ) ) ) ', - 'select_tables' => 'a:8:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:41:\"civicrm_group_contact_cache_5d5bbe284d3e9\";s:152:\" LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5d5bbe284d3e9 ON contact_a.id = civicrm_group_contact_cache_5d5bbe284d3e9.contact_id \";}', - 'where_tables' => 'a:2:{s:15:\"civicrm_contact\";i:1;s:41:\"civicrm_group_contact_cache_5d5bbe284d3e9\";s:152:\" LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5d5bbe284d3e9 ON contact_a.id = civicrm_group_contact_cache_5d5bbe284d3e9.contact_id \";}', 'group_type' => '', 'cache_date' => '', 'refresh_date' => '', diff --git a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/242_email_on_hold.sql b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/242_email_on_hold.sql index de06d5d444..ab73e404d0 100644 --- a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/242_email_on_hold.sql +++ b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/242_email_on_hold.sql @@ -3,6 +3,6 @@ INSERT INTO `civicrm_mapping` (`id`, `name`, `description`, `mapping_type_id`) V INSERT INTO `civicrm_mapping_field` (`id`, `mapping_id`, `name`, `contact_type`, `column_number`, `location_type_id`, `phone_type_id`, `im_provider_id`, `relationship_type_id`, `relationship_direction`, `grouping`, `operator`, `value`, `website_type_id`) VALUES (3538, 149, 'email', 'Individual', 0, NULL, NULL, NULL, NULL, NULL, 1, '', '', NULL); INSERT INTO `civicrm_mapping_field` (`id`, `mapping_id`, `name`, `contact_type`, `column_number`, `location_type_id`, `phone_type_id`, `im_provider_id`, `relationship_type_id`, `relationship_direction`, `grouping`, `operator`, `value`, `website_type_id`) VALUES (3539, 149, 'on_hold', 'Individual', 1, NULL, NULL, NULL, NULL, NULL, 1, '=', '1', NULL); -INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`, `where_clause`, `select_tables`, `where_tables`) VALUES (75, 'a:7:{s:5:"qfKey";s:32:"30ba24b74dd8ceb5710384b962004599";s:6:"mapper";a:2:{i:1;a:2:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:" ";}i:1;a:3:{i:0;s:10:"Individual";i:1;s:7:"on_hold";i:2;s:1:" ";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:2:{i:0;s:11:"IS NOT NULL";i:1;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:2:{i:0;s:0:"";i:1;s:1:"1";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";}', 149, NULL, ' ( ( LOWER(civicrm_email.email) IS NOT NULL AND LOWER(civicrm_email.on_hold) = \'1\' AND contact_a.contact_type IN (\'Individual\') ) ) ', 'a:9:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:6:"gender";i:1;}', 'a:2:{s:15:"civicrm_contact";i:1;s:13:"civicrm_email";i:1;}'); +INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`) VALUES (75, 'a:7:{s:5:"qfKey";s:32:"30ba24b74dd8ceb5710384b962004599";s:6:"mapper";a:2:{i:1;a:2:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:" ";}i:1;a:3:{i:0;s:10:"Individual";i:1;s:7:"on_hold";i:2;s:1:" ";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:2:{i:0;s:11:"IS NOT NULL";i:1;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:2:{i:0;s:0:"";i:1;s:1:"1";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";}', 149, NULL); INSERT INTO `civicrm_group` (`id`, `name`, `title`, `description`, `source`, `saved_search_id`, `is_active`, `visibility`, `where_clause`, `select_tables`, `where_tables`, `group_type`, `cache_date`, `refresh_date`, `parents`, `children`, `is_hidden`, `is_reserved`, `created_id`) VALUES (242, '_Integrity__emails_on_hold', '@ Integrity - emails on hold', NULL, NULL, 75, 1, 'User and User Admin Only', ' ( `civicrm_group_contact_cache_242`.group_id = 242 ) ', 'a:10:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:33:"`civicrm_group_contact_cache_242`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_242` ON contact_a.id = `civicrm_group_contact_cache_242`.contact_id ";s:6:"gender";i:1;}', 'a:2:{s:15:"civicrm_contact";i:1;s:33:"`civicrm_group_contact_cache_242`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_242` ON contact_a.id = `civicrm_group_contact_cache_242`.contact_id ";}', NULL, NULL, NULL, NULL, NULL, 0, 0, NULL); diff --git a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/251_empty_email_on_hold.sql b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/251_empty_email_on_hold.sql index 7942934873..780bc171d3 100644 --- a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/251_empty_email_on_hold.sql +++ b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/251_empty_email_on_hold.sql @@ -1,3 +1,3 @@ -INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`, `where_clause`, `select_tables`, `where_tables`) VALUES (77, 'a:58:{s:5:"qfKey";s:37:"27e1084e725e5a1d4335639ceb45e4bc_3852";s:12:"hidden_basic";s:1:"1";s:12:"contact_type";a:0:{}s:5:"group";a:0:{}s:10:"group_type";a:0:{}s:21:"group_search_selected";s:5:"group";s:12:"contact_tags";a:0:{}s:9:"sort_name";s:0:"";s:5:"email";s:0:"";s:14:"contact_source";s:0:"";s:9:"job_title";s:0:"";s:10:"contact_id";s:0:"";s:19:"external_identifier";s:0:"";s:7:"uf_user";s:0:"";s:10:"tag_search";s:0:"";s:11:"uf_group_id";s:0:"";s:14:"component_mode";s:1:"1";s:8:"operator";s:3:"AND";s:25:"display_relationship_type";s:0:"";s:15:"privacy_options";a:0:{}s:16:"privacy_operator";s:3:"AND";s:14:"privacy_toggle";s:1:"1";s:13:"email_on_hold";a:1:{s:7:"on_hold";N;}s:30:"preferred_communication_method";a:6:{i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";i:5;s:0:"";i:6;s:0:"";}s:18:"preferred_language";s:0:"";s:13:"phone_numeric";s:0:"";s:22:"phone_location_type_id";s:0:"";s:19:"phone_phone_type_id";s:0:"";s:15:"hidden_location";s:1:"1";s:14:"street_address";s:0:"";s:15:"postal_code_low";s:0:"";s:16:"postal_code_high";s:0:"";s:4:"city";s:0:"";s:11:"postal_code";s:0:"";s:14:"state_province";a:1:{i:0;s:4:"1641";}s:7:"country";s:0:"";s:13:"prox_distance";s:0:"";s:18:"prox_distance_unit";s:5:"miles";s:12:"world_region";s:0:"";s:21:"hidden_CiviContribute";s:1:"1";s:19:"contribution_source";s:0:"";s:21:"contribution_date_low";s:0:"";s:22:"contribution_date_high";s:0:"";s:23:"contribution_amount_low";s:0:"";s:24:"contribution_amount_high";s:0:"";s:22:"contribution_status_id";a:0:{}s:38:"contribution_thankyou_date_is_not_null";s:0:"";s:37:"contribution_receipt_date_is_not_null";s:0:"";s:24:"contribution_in_honor_of";s:0:"";s:22:"contribution_pay_later";s:0:"";s:22:"contribution_recurring";s:0:"";s:17:"contribution_test";s:0:"";s:27:"contribution_transaction_id";s:0:"";s:25:"contribution_check_number";s:0:"";s:32:"contribution_pcp_display_in_roll";s:0:"";s:24:"contribution_campaign_id";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";}', NULL, NULL, ' ( civicrm_address.state_province_id IN (1641) ) ', 'a:7:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;}', 'a:2:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;}'); +INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`) VALUES (77, 'a:58:{s:5:"qfKey";s:37:"27e1084e725e5a1d4335639ceb45e4bc_3852";s:12:"hidden_basic";s:1:"1";s:12:"contact_type";a:0:{}s:5:"group";a:0:{}s:10:"group_type";a:0:{}s:21:"group_search_selected";s:5:"group";s:12:"contact_tags";a:0:{}s:9:"sort_name";s:0:"";s:5:"email";s:0:"";s:14:"contact_source";s:0:"";s:9:"job_title";s:0:"";s:10:"contact_id";s:0:"";s:19:"external_identifier";s:0:"";s:7:"uf_user";s:0:"";s:10:"tag_search";s:0:"";s:11:"uf_group_id";s:0:"";s:14:"component_mode";s:1:"1";s:8:"operator";s:3:"AND";s:25:"display_relationship_type";s:0:"";s:15:"privacy_options";a:0:{}s:16:"privacy_operator";s:3:"AND";s:14:"privacy_toggle";s:1:"1";s:13:"email_on_hold";a:1:{s:7:"on_hold";N;}s:30:"preferred_communication_method";a:6:{i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";i:5;s:0:"";i:6;s:0:"";}s:18:"preferred_language";s:0:"";s:13:"phone_numeric";s:0:"";s:22:"phone_location_type_id";s:0:"";s:19:"phone_phone_type_id";s:0:"";s:15:"hidden_location";s:1:"1";s:14:"street_address";s:0:"";s:15:"postal_code_low";s:0:"";s:16:"postal_code_high";s:0:"";s:4:"city";s:0:"";s:11:"postal_code";s:0:"";s:14:"state_province";a:1:{i:0;s:4:"1641";}s:7:"country";s:0:"";s:13:"prox_distance";s:0:"";s:18:"prox_distance_unit";s:5:"miles";s:12:"world_region";s:0:"";s:21:"hidden_CiviContribute";s:1:"1";s:19:"contribution_source";s:0:"";s:21:"contribution_date_low";s:0:"";s:22:"contribution_date_high";s:0:"";s:23:"contribution_amount_low";s:0:"";s:24:"contribution_amount_high";s:0:"";s:22:"contribution_status_id";a:0:{}s:38:"contribution_thankyou_date_is_not_null";s:0:"";s:37:"contribution_receipt_date_is_not_null";s:0:"";s:24:"contribution_in_honor_of";s:0:"";s:22:"contribution_pay_later";s:0:"";s:22:"contribution_recurring";s:0:"";s:17:"contribution_test";s:0:"";s:27:"contribution_transaction_id";s:0:"";s:25:"contribution_check_number";s:0:"";s:32:"contribution_pcp_display_in_roll";s:0:"";s:24:"contribution_campaign_id";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";}', NULL, NULL); INSERT INTO `civicrm_group` (`id`, `name`, `title`, `description`, `source`, `saved_search_id`, `is_active`, `visibility`, `where_clause`, `select_tables`, `where_tables`, `group_type`, `cache_date`, `refresh_date`, `parents`, `children`, `is_hidden`, `is_reserved`, `created_id`) VALUES (251, 'Qld_All', 'Qld All', 'All contacts with Qld as the State', NULL, 77, 1, 'User and User Admin Only', ' ( `civicrm_group_contact_cache_251`.group_id = 251 ) ', 'a:8:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:33:"`civicrm_group_contact_cache_251`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_251` ON contact_a.id = `civicrm_group_contact_cache_251`.contact_id ";}', 'a:2:{s:15:"civicrm_contact";i:1;s:33:"`civicrm_group_contact_cache_251`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_251` ON contact_a.id = `civicrm_group_contact_cache_251`.contact_id ";}', NULL, NULL, NULL, NULL, NULL, 0, 0, NULL); diff --git a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/801_pre44_billing_email_smartgroup.sql b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/801_pre44_billing_email_smartgroup.sql index c41e347e6a..0a5b83a600 100644 --- a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/801_pre44_billing_email_smartgroup.sql +++ b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/801_pre44_billing_email_smartgroup.sql @@ -7,7 +7,7 @@ INSERT IGNORE INTO `civicrm_mapping_field` (`id`, `mapping_id`, `name`, `contact INSERT IGNORE INTO `civicrm_mapping_field` (`id`, `mapping_id`, `name`, `contact_type`, `column_number`, `location_type_id`, `phone_type_id`, `im_provider_id`, `relationship_type_id`, `relationship_direction`, `grouping`, `operator`, `value`, `website_type_id`) VALUES (7459, 315, 'on_hold', 'Individual', 4, 5, NULL, NULL, NULL, NULL, 1, '=', '0', NULL); -INSERT IGNORE INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`, `where_clause`, `select_tables`, `where_tables`) VALUES (286, 'a:8:{s:5:"qfKey";s:37:"92bf8874b4cd856436833fba5526a10f_9684";s:6:"mapper";a:2:{i:1;a:5:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:"5";}i:1;a:2:{i:0;s:10:"Individual";i:1;s:12:"do_not_email";}i:2;a:3:{i:0;s:10:"Individual";i:1;s:14:"state_province";i:2;s:1:"5";}i:3;a:2:{i:0;s:10:"Individual";i:1;s:10:"is_opt_out";}i:4;a:3:{i:0;s:10:"Individual";i:1;s:7:"on_hold";i:2;s:1:"5";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:5:{i:0;s:11:"IS NOT NULL";i:1;s:1:"=";i:2;s:1:"=";i:3;s:1:"=";i:4;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:5:{i:0;s:0:"";i:1;s:1:"0";i:2;s:5:"Idaho";i:3;s:1:"0";i:4;s:1:"0";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";s:14:"component_mode";i:1;}', 315, NULL, ' ( ( ( civicrm_email.email IS NOT NULL ) AND contact_a.do_not_email = 0 AND civicrm_state_province.name IN (\'Idaho\') AND contact_a.is_opt_out = 0 AND LOWER(civicrm_email.on_hold) = \'0\' AND contact_a.contact_type IN (\'Individual\') ) ) ', 'a:11:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:6:"gender";i:1;s:17:"individual_prefix";i:1;s:17:"individual_suffix";i:1;}', 'a:4:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:13:"civicrm_email";i:1;}'); +INSERT IGNORE INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`) VALUES (286, 'a:8:{s:5:"qfKey";s:37:"92bf8874b4cd856436833fba5526a10f_9684";s:6:"mapper";a:2:{i:1;a:5:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:"5";}i:1;a:2:{i:0;s:10:"Individual";i:1;s:12:"do_not_email";}i:2;a:3:{i:0;s:10:"Individual";i:1;s:14:"state_province";i:2;s:1:"5";}i:3;a:2:{i:0;s:10:"Individual";i:1;s:10:"is_opt_out";}i:4;a:3:{i:0;s:10:"Individual";i:1;s:7:"on_hold";i:2;s:1:"5";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:5:{i:0;s:11:"IS NOT NULL";i:1;s:1:"=";i:2;s:1:"=";i:3;s:1:"=";i:4;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:5:{i:0;s:0:"";i:1;s:1:"0";i:2;s:5:"Idaho";i:3;s:1:"0";i:4;s:1:"0";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";s:14:"component_mode";i:1;}', 315, NULL); INSERT IGNORE INTO `civicrm_group` (`id`, `name`, `title`, `description`, `source`, `saved_search_id`, `is_active`, `visibility`, `where_clause`, `select_tables`, `where_tables`, `group_type`, `cache_date`, `refresh_date`, `parents`, `children`, `is_hidden`, `is_reserved`, `created_id`) VALUES (801, 'Idaho EmailContacts', 'Idaho Email Contacts', NULL, NULL, 286, 1, 'User and User Admin Only', ' ( `civicrm_group_contact_cache_801`.group_id = 801 ) ', 'a:12:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:33:"`civicrm_group_contact_cache_801`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_801` ON contact_a.id = `civicrm_group_contact_cache_801`.contact_id ";s:6:"gender";i:1;s:17:"individual_prefix";i:1;s:17:"individual_suffix";i:1;}', 'a:2:{s:15:"civicrm_contact";i:1;s:33:"`civicrm_group_contact_cache_801`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_801` ON contact_a.id = `civicrm_group_contact_cache_801`.contact_id ";}', '2', '2014-06-26 03:31:03', NULL, NULL, NULL, 0, 0, NULL); diff --git a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/94_legacy_state_province.sql b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/94_legacy_state_province.sql index bf480c03ea..b311a84eda 100644 --- a/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/94_legacy_state_province.sql +++ b/tests/phpunit/CRM/Contact/BAO/SavedSearchDataSets/94_legacy_state_province.sql @@ -6,6 +6,6 @@ INSERT INTO `civicrm_mapping_field` (`id`, `mapping_id`, `name`, `contact_type`, (2843, 113, 'is_opt_out', 'Individual', 2, NULL, NULL, NULL, NULL, NULL, 1, '!=', '1', NULL), (2844, 113, 'first_name', 'Individual', 3, NULL, NULL, NULL, NULL, NULL, 1, '', '', NULL), (2845, 113, 'state_province', 'Individual', 4, NULL, NULL, NULL, NULL, NULL, 1, '=', 'New York', NULL); -INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`, `where_clause`, `select_tables`, `where_tables`) VALUES -(38, 'a:6:{s:6:"mapper";a:2:{i:1;a:5:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:" ";}i:1;a:2:{i:0;s:10:"Individual";i:1;s:12:"do_not_email";}i:2;a:2:{i:0;s:10:"Individual";i:1;s:10:"is_opt_out";}i:3;a:2:{i:0;s:10:"Individual";i:1;s:10:"first_name";}i:4;a:3:{i:0;s:10:"Individual";i:1;s:14:"state_province";i:2;s:1:" ";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:5:{i:0;s:11:"IS NOT NULL";i:1;s:2:"!=";i:2;s:2:"!=";i:3;s:11:"IS NOT NULL";i:4;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:5:{i:0;s:0:"";i:1;s:1:"1";i:2;s:1:"1";i:3;s:0:"";i:4;s:8:"New York";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";}', '113', NULL, " ( ( LOWER(civicrm_email.email) IS NOT NULL AND contact_a.do_not_email != 1 AND contact_a.is_opt_out != 1 AND LOWER(contact_a.first_name) IS NOT NULL AND LOWER(civicrm_state_province.name) = 'New York' AND contact_a.contact_type IN ('Individual') ) ) ", 'a:9:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:6:"gender";i:1;}', 'a:4:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:13:"civicrm_email";i:1;}'); +INSERT INTO `civicrm_saved_search` (`id`, `form_values`, `mapping_id`, `search_custom_id`) VALUES +(38, 'a:6:{s:6:"mapper";a:2:{i:1;a:5:{i:0;a:3:{i:0;s:10:"Individual";i:1;s:5:"email";i:2;s:1:" ";}i:1;a:2:{i:0;s:10:"Individual";i:1;s:12:"do_not_email";}i:2;a:2:{i:0;s:10:"Individual";i:1;s:10:"is_opt_out";}i:3;a:2:{i:0;s:10:"Individual";i:1;s:10:"first_name";}i:4;a:3:{i:0;s:10:"Individual";i:1;s:14:"state_province";i:2;s:1:" ";}}i:2;a:1:{i:0;a:1:{i:0;s:0:"";}}}s:8:"operator";a:2:{i:1;a:5:{i:0;s:11:"IS NOT NULL";i:1;s:2:"!=";i:2;s:2:"!=";i:3;s:11:"IS NOT NULL";i:4;s:1:"=";}i:2;a:1:{i:0;s:0:"";}}s:5:"value";a:2:{i:1;a:5:{i:0;s:0:"";i:1;s:1:"1";i:2;s:1:"1";i:3;s:0:"";i:4;s:8:"New York";}i:2;a:1:{i:0;s:0:"";}}s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_all";s:11:"uf_group_id";s:0:"";}', '113', NULL); INSERT INTO `civicrm_group` (`id`, `name`, `title`, `description`, `source`, `saved_search_id`, `is_active`, `visibility`, `where_clause`, `select_tables`, `where_tables`, `group_type`, `cache_date`, `refresh_date`, `parents`, `children`, `is_hidden`, `is_reserved`, `created_id`) VALUES (94, '_null_streets', '@ null streets', NULL, NULL, 38, 1, 'User and User Admin Only', ' ( `civicrm_group_contact_cache_242`.group_id = 94 ) ', 'a:10:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:22:"civicrm_state_province";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:33:"`civicrm_group_contact_cache_94`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_94` ON contact_a.id = `civicrm_group_contact_cache_94`.contact_id ";s:6:"gender";i:1;}', 'a:2:{s:15:"civicrm_contact";i:1;s:33:"`civicrm_group_contact_cache_94`";s:136:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_94` ON contact_a.id = `civicrm_group_contact_cache_94`.contact_id ";}', NULL, NULL, NULL, NULL, NULL, 0, 0, NULL); diff --git a/xml/schema/Contact/SavedSearch.xml b/xml/schema/Contact/SavedSearch.xml index e8b7dbd2d1..e8437a08b9 100644 --- a/xml/schema/Contact/SavedSearch.xml +++ b/xml/schema/Contact/SavedSearch.xml @@ -71,6 +71,7 @@ Where Clause the sql where clause if a saved search acl 1.6 + 5.24 select_tables @@ -79,6 +80,7 @@ the tables to be included in a select data PHP 1.6 + 5.24 where_tables @@ -87,5 +89,6 @@ the tables to be included in the count statement PHP 1.6 + 5.24 -- 2.25.1