From ba8974f492a903e464e793b69e132352e491ff1d Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Tue, 16 May 2023 20:30:28 -0700 Subject: [PATCH] (WIP) sql/ - Convert OptionGroups from literal SQL to arrays/methods --- CRM/Core/CodeGen/OptionGroup.php | 126 +++++++++++++++++++++++++++++++ sql/civicrm_option_groups.php | 68 +++++++++++++++++ xml/templates/civicrm_data.tpl | 62 ++------------- 3 files changed, 202 insertions(+), 54 deletions(-) create mode 100644 CRM/Core/CodeGen/OptionGroup.php create mode 100644 sql/civicrm_option_groups.php diff --git a/CRM/Core/CodeGen/OptionGroup.php b/CRM/Core/CodeGen/OptionGroup.php new file mode 100644 index 0000000000..0f862be135 --- /dev/null +++ b/CRM/Core/CodeGen/OptionGroup.php @@ -0,0 +1,126 @@ + 1, + 'is_reserved' => 1, + 'option_value_fields' => 'name,label,description', + ]; + + protected $defaults = [ + 'color' => NULL, + 'component_id' => NULL, + 'description' => NULL, + 'domain_id' => NULL, + 'filter' => 0, + 'grouping' => NULL, + 'icon' => NULL, + 'is_active' => 1, + 'is_default' => 0, + 'is_optgroup' => 0, + 'is_reserved' => 0, + 'visibility_id' => NULL, + ]; + + protected $var; + + protected $rows = []; + + public static function create(string $name): CRM_Core_CodeGen_OptionGroup { + $og = new static(); + $og->metadata['name'] = $name; + // $og->var = '@option_group_id_' . $name; + $og->var = '@this_option_group_id'; + return $og; + } + + /** + * @param array $fields + * List of OptionGroup fields/values. + * Ex: ['is_reserved' => 0, 'description' => 'Store the stuff'] + * @return $this + */ + public function addMetadata(array $fields): CRM_Core_CodeGen_OptionGroup { + $this->metadata = array_merge($this->metadata, $fields); + return $this; + } + + public function addDefaults(array $fields): CRM_Core_CodeGen_OptionGroup { + $this->defaults = array_merge($this->defaults, $fields); + return $this; + } + + /** + * Add a bunch of values to the option-group. + * + * @param array $header + * Ex: ['name', 'label'] + * @param array $optionValues + * A list of option-value records. + * + * Generally, each record should match the header. + * Ex: [ + * ['foo_bar', ts('Foo Bar')] + * ['whiz_bang', ts('Whiz Bang')] + * ] + * + * Additionally, you may add extra key-value pairs + * + * Ex: [ + * ['foo_bar', ts('Foo Bar')] + * ['whiz_bang', ts('Whiz Bang'), 'is_active' => 0, 'component_id' => 100] + * ] + * + * @return $this + */ + public function addValues(array $header, array $optionValues) : CRM_Core_CodeGen_OptionGroup { + foreach ($optionValues as $optionValue) { + $row = []; + foreach ($optionValue as $key => $value) { + if (is_numeric($key)) { + $key = $header[$key]; + } + $row[$key] = $value; + } + $this->rows[] = $row; + } + return $this; + } + + public function toArray(): array { + $position = 1; + $result = []; + foreach ($this->rows as $row) { + $result[] = array_merge( + ['option_group_id' => new CRM_Utils_SQL_Literal($this->var), 'value' => $position, 'weight' => $position], + $this->defaults, + $row + ); + $position++; + } + return $result; + } + + public function toSQL(): string { + $result = ''; + $result .= CRM_Utils_SQL_Insert::into('civicrm_option_group') + ->row($this->metadata) + ->toSQL() . ";\n"; + + $rows = $this->toArray(); + if ($rows) { + $result .= CRM_Utils_SQL_Select::from('civicrm_option_group') + ->select("{$this->var} := max(id)") + ->where('name = @NAME', ['NAME' => $this->metadata['name']]) + ->toSQL() . ";\n"; + + $result .= CRM_Utils_SQL_Insert::into('civicrm_option_value') + ->allowLiterals() + // ->columns(['option_group_id', 'label', 'value', 'name', 'grouping', 'filter', 'is_default', 'weight', 'description', 'is_optgroup', 'is_reserved', 'is_active', 'component_id', 'visibility_id']) + ->rows($rows) + ->toSQL() . ";\n"; + } + return $result; + } + +} diff --git a/sql/civicrm_option_groups.php b/sql/civicrm_option_groups.php new file mode 100644 index 0000000000..4a63267ac7 --- /dev/null +++ b/sql/civicrm_option_groups.php @@ -0,0 +1,68 @@ + CRM_Core_CodeGen_OptionGroup::create('encounter_medium') + ->addMetadata([ + // Shouldn't these be translated? + 'title' => 'Encounter Medium', + 'description' => 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)', + ]) + ->addValues(['label', 'name'], [ + [ts('In Person'), 'in_person'], + [ts('Phone'), 'phone', 'is_default' => 1], + [ts('Email'), 'email'], + [ts('Fax'), 'fax'], + [ts('Letter Mail'), 'letter_mail'], + ]) + ->addDefaults([ + 'is_reserved' => 1, + ]), + + // CRM-13833 + 'soft_credit_type' => CRM_Core_CodeGen_OptionGroup::create('soft_credit_type') + ->addMetadata([ + 'title' => ts('Soft Credit Types'), + ]) + ->addValues(['label', 'value', 'name'], [ + [ts('In Honor of'), 1, 'in_honor_of', 'is_reserved' => 1], + [ts('In Memory of'), 2, 'in_memory_of', 'is_reserved' => 1], + [ts('Solicited'), 3, 'solicited', 'is_reserved' => 1, 'is_default' => 1], + [ts('Household'), 4, 'household'], + [ts('Workplace Giving'), 5, 'workplace'], + [ts('Foundation Affiliate'), 6, 'foundation_affiliate'], + [ts('3rd-party Service'), 7, '3rd-party_service'], + [ts('Donor-advised Fund'), 8, 'donor-advised_fund'], + [ts('Matched Gift'), 9, 'matched_gift'], + [ts('Personal Campaign Page'), 10, 'pcp', 'is_reserved' => 1], + [ts('Gift'), 11, 'gift', 'is_reserved' => 1], + ]) + ->addDefaults([]), + + // dev/core#3783 Recent Items providers + 'recent_items_providers' => CRM_Core_CodeGen_OptionGroup::create('recent_items_providers') + ->addMetadata([ + 'title' => ts('Recent Items Providers'), + ]) + ->addValues(['label', 'value', 'name'], [ + [ts('Contacts'), 'Contact', 'Contact'], + [ts('Relationships'), 'Relationship', 'Relationship'], + [ts('Activities'), 'Activity', 'Activity'], + [ts('Notes'), 'Note', 'Note'], + [ts('Groups'), 'Group', 'Group'], + [ts('Cases'), 'Case', 'Case'], + [ts('Contributions'), 'Contribution', 'Contribution'], + [ts('Participants'), 'Participant', 'Participant'], + [ts('Memberships'), 'Membership', 'Membership'], + [ts('Pledges'), 'Pledge', 'Pledge'], + [ts('Events'), 'Event', 'Event'], + [ts('Campaigns'), 'Campaign', 'Campaign'], + ]) + ->addDefaults([ + 'description' => '', + 'filter' => NULL, + 'weight' => 1, + // Why do these all have the same weight? Shrug. + 'is_reserved' => 1, + ]), +]; diff --git a/xml/templates/civicrm_data.tpl b/xml/templates/civicrm_data.tpl index fc70ddad89..ec604914b2 100644 --- a/xml/templates/civicrm_data.tpl +++ b/xml/templates/civicrm_data.tpl @@ -11,6 +11,11 @@ -- -- This file provides template to civicrm_data.mysql. Inserts all base data needed for a new CiviCRM DB +{php} + $optionGroups = include 'sql/civicrm_option_groups.php'; +{/php} + + SET @domainName := 'Default Domain Name'; SET @defaultOrganization := 'Default Organization'; @@ -1118,22 +1123,7 @@ VALUES -- CRM-6138 {include file='languages.tpl'} --- /******************************************************* --- * --- * Encounter Medium Option Values (for case activities) --- * --- *******************************************************/ -INSERT INTO `civicrm_option_group` (name, title, description, is_reserved, is_active) - VALUES ('encounter_medium', 'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)', 1, 1); -SELECT @option_group_id_medium := max(id) from civicrm_option_group where name = 'encounter_medium'; -INSERT INTO - `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`) -VALUES - (@option_group_id_medium, '{ts escape="sql"}In Person{/ts}', 1, 'in_person', NULL, 0, 0, 1, NULL, 0, 1, 1), - (@option_group_id_medium, '{ts escape="sql"}Phone{/ts}', 2, 'phone', NULL, 0, 1, 2, NULL, 0, 1, 1), - (@option_group_id_medium, '{ts escape="sql"}Email{/ts}', 3, 'email', NULL, 0, 0, 3, NULL, 0, 1, 1), - (@option_group_id_medium, '{ts escape="sql"}Fax{/ts}', 4, 'fax', NULL, 0, 0, 4, NULL, 0, 1, 1), - (@option_group_id_medium, '{ts escape="sql"}Letter Mail{/ts}', 5, 'letter_mail', NULL, 0, 0, 5, NULL, 0, 1, 1); +{php}echo $optionGroups['encounter_medium']->toSQL();{/php} -- sample membership status entries INSERT INTO @@ -1768,25 +1758,6 @@ SELECT @fieldID := max(id) FROM civicrm_price_field WHERE name = 'contribution_a INSERT INTO `civicrm_price_field_value` ( `price_field_id`, `name`, `label`, `amount`, `weight`, `is_default`, `is_active`, `financial_type_id`) VALUES ( @fieldID, 'contribution_amount', 'Contribution Amount', '1', '1', '0', '1', 1); --- CRM-13833 -INSERT INTO civicrm_option_group (`name`, `title`, `is_reserved`, `is_active`) VALUES ('soft_credit_type', '{ts escape="sql"}Soft Credit Types{/ts}', 1, 1); - -SELECT @option_group_id_soft_credit_type := max(id) from civicrm_option_group where name = 'soft_credit_type'; - -INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`) -VALUES - (@option_group_id_soft_credit_type , '{ts escape="sql"}In Honor of{/ts}', 1, 'in_honor_of', 1, 0, 1, 1), - (@option_group_id_soft_credit_type , '{ts escape="sql"}In Memory of{/ts}', 2, 'in_memory_of', 2, 0, 1, 1), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Solicited{/ts}', 3, 'solicited', 3, 1, 1, 1), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Household{/ts}', 4, 'household', 4, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Workplace Giving{/ts}', 5, 'workplace', 5, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Foundation Affiliate{/ts}', 6, 'foundation_affiliate', 6, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}3rd-party Service{/ts}', 7, '3rd-party_service', 7, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Donor-advised Fund{/ts}', 8, 'donor-advised_fund', 8, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Matched Gift{/ts}', 9, 'matched_gift', 9, 0, 1, 0), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Personal Campaign Page{/ts}', 10, 'pcp', 10, 0, 1, 1), - (@option_group_id_soft_credit_type , '{ts escape="sql"}Gift{/ts}', 11, 'gift', 11, 0, 1, 1); - -- Auto-install core extension. -- Note this is a limited interim technique for installing core extensions - the goal is that core extensions would be installed -- in the setup routine based on their tags & using the standard extension install api. @@ -1800,22 +1771,5 @@ INSERT IGNORE INTO civicrm_extension (type, full_name, name, label, file, is_act INSERT IGNORE INTO civicrm_extension (type, full_name, name, label, file, is_active) VALUES ('module', 'legacycustomsearches', 'Custom search framework', 'Custom search framework', 'legacycustomsearches', 1); INSERT IGNORE INTO civicrm_extension (type, full_name, name, label, file, is_active) VALUES ('module', 'org.civicrm.flexmailer', 'FlexMailer', 'FlexMailer', 'flexmailer', 1); --- dev/core#3783 Recent Items providers -INSERT INTO civicrm_option_group (`name`, `title`, `is_reserved`, `is_active`) VALUES ('recent_items_providers', '{ts escape="sql"}Recent Items Providers{/ts}', 1, 1); - -SELECT @option_group_id_recent := max(id) from civicrm_option_group where name = 'recent_items_providers'; - -INSERT INTO civicrm_option_value (`option_group_id`, label, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, description, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) - VALUES - (@option_group_id_recent, '{ts escape="sql"}Contacts{/ts}', 'Contact', 'Contact', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Relationships{/ts}', 'Relationship', 'Relationship', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Activities{/ts}', 'Activity', 'Activity', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Notes{/ts}', 'Note', 'Note', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Groups{/ts}', 'Group', 'Group', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Cases{/ts}', 'Case', 'Case', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Contributions{/ts}', 'Contribution', 'Contribution', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Participants{/ts}', 'Participant', 'Participant', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Memberships{/ts}', 'Membership', 'Membership', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Pledges{/ts}', 'Pledge', 'Pledge', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Events{/ts}', 'Event', 'Event', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL), - (@option_group_id_recent, '{ts escape="sql"}Campaigns{/ts}', 'Campaign', 'Campaign', NULL, NULL, 0, 1, '', 0, 1, 1, NULL, NULL); +{php}echo $optionGroups['soft_credit_type']->toSQL();{/php} +{php}echo $optionGroups['recent_items_providers']->toSQL();{/php} -- 2.25.1