| 1 | {* file to handle db changes in 4.5.alpha1 during upgrade *} |
| 2 | {include file='../CRM/Upgrade/4.5.alpha1.msg_template/civicrm_msg_template.tpl'} |
| 3 | |
| 4 | ALTER TABLE `civicrm_contact` |
| 5 | ADD COLUMN `formal_title` varchar(64) COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)' AFTER `suffix_id`; |
| 6 | |
| 7 | ALTER TABLE `civicrm_contact` |
| 8 | ADD COLUMN `communication_style_id` int(10) unsigned COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.' AFTER `formal_title`, |
| 9 | ADD INDEX `index_communication_style_id` (`communication_style_id`); |
| 10 | |
| 11 | INSERT INTO |
| 12 | `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`) |
| 13 | VALUES |
| 14 | ('communication_style', {localize}'{ts escape="sql"}Communication Style{/ts}'{/localize}, 1, 1); |
| 15 | |
| 16 | SELECT @option_group_id_communication_style := max(id) from civicrm_option_group where name = 'communication_style'; |
| 17 | |
| 18 | INSERT INTO |
| 19 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
| 20 | VALUES |
| 21 | (@option_group_id_communication_style, {localize}'{ts escape="sql"}Formal{/ts}'{/localize}, 1, 'formal' , NULL, 0, 1, 1, 0, 0, 1, NULL, NULL), |
| 22 | (@option_group_id_communication_style, {localize}'{ts escape="sql"}Familiar{/ts}'{/localize}, 2, 'familiar', NULL, 0, 0, 2, 0, 0, 1, NULL, NULL); |
| 23 | |
| 24 | -- Insert menu item at Administer > Communications, above the various Greeting Formats |
| 25 | |
| 26 | SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Communications' AND `domain_id` = {$domainID}; |
| 27 | SELECT @add_weight := MIN(`weight`) FROM `civicrm_navigation` WHERE `name` IN('Email Greeting Formats', 'Postal Greeting Formats', 'Addressee Formats') AND `parent_id` = @parent_id; |
| 28 | |
| 29 | UPDATE `civicrm_navigation` |
| 30 | SET `weight` = `weight`+1 |
| 31 | WHERE `parent_id` = @parent_id |
| 32 | AND `weight` >= @add_weight; |
| 33 | |
| 34 | INSERT INTO `civicrm_navigation` |
| 35 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 36 | VALUES |
| 37 | ( {$domainID}, 'civicrm/admin/options/communication_style&group=communication_style&reset=1', '{ts escape="sql" skip="true"}Communication Style Options{/ts}', 'Communication Style Options', 'administer CiviCRM', '', @parent_id, '1', NULL, @add_weight ); |
| 38 | |
| 39 | -- CRM-9988 Change world region of Panama country to America South, Central, North and Caribbean |
| 40 | UPDATE `civicrm_country` SET `region_id` = 2 WHERE `id` = 1166; |
| 41 | |
| 42 | SELECT @option_group_id_contact_edit_options := max(id) from civicrm_option_group where name = 'contact_edit_options'; |
| 43 | |
| 44 | INSERT INTO |
| 45 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
| 46 | VALUES |
| 47 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Prefix{/ts}'{/localize} , 12, 'Prefix' , NULL, 2, NULL, 12, 0, 0, 1, NULL, NULL), |
| 48 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Formal Title{/ts}'{/localize}, 13, 'Formal Title', NULL, 2, NULL, 13, 0, 0, 1, NULL, NULL), |
| 49 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}First Name{/ts}'{/localize} , 14, 'First Name' , NULL, 2, NULL, 14, 0, 0, 1, NULL, NULL), |
| 50 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Middle Name{/ts}'{/localize} , 15, 'Middle Name' , NULL, 2, NULL, 15, 0, 0, 1, NULL, NULL), |
| 51 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize} , 16, 'Last Name' , NULL, 2, NULL, 16, 0, 0, 1, NULL, NULL), |
| 52 | (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Suffix{/ts}'{/localize} , 17, 'Suffix' , NULL, 2, NULL, 17, 0, 0, 1, NULL, NULL); |
| 53 | |
| 54 | -- CRM-13857 |
| 55 | ALTER TABLE civicrm_group |
| 56 | ADD COLUMN `modified_id` INT(10) unsigned DEFAULT NULL COMMENT 'FK to contact table, modifier of the group.', |
| 57 | ADD CONSTRAINT `FK_civicrm_group_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL; |
| 58 | |
| 59 | -- CRM-13913 |
| 60 | ALTER TABLE civicrm_word_replacement |
| 61 | ALTER COLUMN `is_active` SET DEFAULT 1; |
| 62 | |
| 63 | --CRM-13833 Implement Soft Credit Type for Contribution |
| 64 | INSERT INTO civicrm_option_group |
| 65 | (name, {localize field='title'}title{/localize}, is_reserved, is_active) VALUES ('soft_credit_type', {localize}'{ts escape="sql"}Soft Credit Types{/ts}'{/localize}, 1, 1); |
| 66 | |
| 67 | SELECT @option_group_id_soft_credit_type := max(id) from civicrm_option_group where name = 'soft_credit_type'; |
| 68 | |
| 69 | INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`) |
| 70 | VALUES |
| 71 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}In Honor of{/ts}'{/localize}, 1, 'in_honor_of', 1, 0, 1, 1), |
| 72 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}In Memory of{/ts}'{/localize}, 2, 'in_memory_of', 2, 0, 1, 1), |
| 73 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Solicited{/ts}'{/localize}, 3, 'solicited', 3, 0, 1, 1), |
| 74 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Household{/ts}'{/localize}, 4, 'household', 4, 0, 1, 0), |
| 75 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Workplace Giving{/ts}'{/localize}, 5, 'workplace', 5, 0, 1, 0), |
| 76 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Foundation Affiliate{/ts}'{/localize}, 6, 'foundation_affiliate', 6, 0, 1, 0), |
| 77 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}3rd-party Service{/ts}'{/localize}, 7, '3rd-party_service', 7, 0, 1, 0), |
| 78 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Donor-advised Fund{/ts}'{/localize}, 8, 'donor-advised_fund', 8, 0, 1, 0), |
| 79 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Matched Gift{/ts}'{/localize}, 9, 'matched_gift', 9, 0, 1, 0), |
| 80 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Personal Campaign Page{/ts}'{/localize}, 10, 'pcp', 10, 0, 1, 1), |
| 81 | (@option_group_id_soft_credit_type , {localize}'{ts escape="sql"}Gift{/ts}'{/localize}, 11, 'gift', 11, 0, 1, 1); |
| 82 | |
| 83 | ALTER TABLE `civicrm_contribution_soft` |
| 84 | ADD COLUMN `soft_credit_type_id` int(10) unsigned COMMENT 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.'; |
| 85 | |
| 86 | INSERT INTO civicrm_contribution_soft(contribution_id, contact_id, amount, currency, soft_credit_type_id) |
| 87 | SELECT id, honor_contact_id, total_amount, currency, honor_type_id |
| 88 | FROM civicrm_contribution |
| 89 | WHERE honor_contact_id IS NOT NULL; |
| 90 | |
| 91 | SELECT @sct_pcp_id := value from civicrm_option_value where name = 'pcp' and option_group_id = @option_group_id_soft_credit_type; |
| 92 | |
| 93 | UPDATE `civicrm_contribution_soft` |
| 94 | SET soft_credit_type_id = @sct_pcp_id |
| 95 | WHERE pcp_id IS NOT NULL; |
| 96 | |
| 97 | --CRM-13734 make basic Case Activity Types reserved |
| 98 | SELECT @option_group_id_activity_type := id from civicrm_option_group where name = 'activity_type'; |
| 99 | SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase'; |
| 100 | |
| 101 | UPDATE `civicrm_option_value` |
| 102 | SET is_reserved = 1 |
| 103 | WHERE is_reserved = 0 AND option_group_id = @option_group_id_activity_type AND component_id = @caseCompId; |
| 104 | |
| 105 | -- CRM-13912 |
| 106 | ALTER TABLE civicrm_action_schedule |
| 107 | ADD COLUMN `mode` varchar(128) COLLATE utf8_unicode_ci DEFAULT 'Email' COMMENT 'Send the message as email or sms or both.'; |
| 108 | |
| 109 | INSERT INTO |
| 110 | civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active) |
| 111 | VALUES |
| 112 | ('msg_mode', {localize}'{ts escape="sql"}Message Mode{/ts}'{/localize}, 1, 1); |
| 113 | |
| 114 | SELECT @option_group_id_msg_mode := max(id) from civicrm_option_group where name = 'msg_mode'; |
| 115 | |
| 116 | INSERT INTO |
| 117 | civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active) |
| 118 | VALUES |
| 119 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}Email{/ts}'{/localize}, 'Email', 'Email', 1, 1, 1, 1), |
| 120 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}SMS{/ts}'{/localize},'SMS', 'SMS', 0, 2, 1, 1), |
| 121 | (@option_group_id_msg_mode, {localize}'{ts escape="sql"}User Preference{/ts}'{/localize}, 'User_Preference', 'User Preference', 0, 3, 1, 1); |
| 122 | |
| 123 | ALTER TABLE civicrm_action_schedule ADD sms_provider_id int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id '; |
| 124 | ALTER TABLE civicrm_action_schedule ADD CONSTRAINT FK_civicrm_action_schedule_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider` (`id`) ON DELETE SET NULL; |
| 125 | |
| 126 | --CRM-13981 migrate 'In Honor of' to Soft Credits |
| 127 | INSERT INTO `civicrm_uf_group` |
| 128 | (`name`, `group_type`, {localize field='title'}`title`{/localize}, `is_cms_user`, `is_reserved`) |
| 129 | VALUES |
| 130 | ('honoree_individual', 'Individual,Contact', {localize}'{ts escape="sql"}Honoree Individual{/ts}'{/localize}, 0, 1); |
| 131 | |
| 132 | SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual'; |
| 133 | SELECT @primaryLocation := id FROM civicrm_location_type WHERE is_default = 1; |
| 134 | |
| 135 | INSERT INTO `civicrm_uf_field` |
| 136 | (`uf_group_id`, `field_name`, `is_required`, `is_reserved`, `weight`, `visibility`, `in_selector`, `is_searchable`, `location_type_id`, {localize field='label'}`label`{/localize}, field_type) |
| 137 | VALUES |
| 138 | (@uf_group_id_honoree_individual, 'prefix_id', 0, 1, 1, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Individual Prefix{/ts}'{/localize}, 'Individual'), |
| 139 | (@uf_group_id_honoree_individual, 'first_name', 0, 1, 2, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}First Name{/ts}'{/localize}, 'Individual'), |
| 140 | (@uf_group_id_honoree_individual, 'last_name', 0, 1, 3, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize}, 'Individual'), |
| 141 | (@uf_group_id_honoree_individual, 'email', 0, 1, 4, 'User and User Admin Only', 0, 1, @primaryLocation, {localize}'{ts escape="sql"}Email Address{/ts}'{/localize}, 'Individual'); |
| 142 | |
| 143 | UPDATE civicrm_uf_join SET uf_group_id = @uf_group_id_honoree_individual WHERE module = 'soft_credit'; |
| 144 | |
| 145 | {if $multilingual} |
| 146 | {foreach from=$locales item=loc} |
| 147 | ALTER TABLE civicrm_contribution_page DROP honor_block_title_{$loc}; |
| 148 | ALTER TABLE civicrm_contribution_page DROP honor_block_text_{$loc}; |
| 149 | {/foreach} |
| 150 | {else} |
| 151 | ALTER TABLE civicrm_contribution_page DROP honor_block_title; |
| 152 | ALTER TABLE civicrm_contribution_page DROP honor_block_text; |
| 153 | {/if} |
| 154 | ALTER TABLE civicrm_contribution_page DROP honor_block_is_active; |
| 155 | |
| 156 | ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`; |
| 157 | ALTER TABLE civicrm_contribution DROP honor_contact_id; |
| 158 | ALTER TABLE civicrm_contribution DROP honor_type_id; |
| 159 | |
| 160 | ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`; |
| 161 | ALTER TABLE civicrm_pledge DROP honor_contact_id; |
| 162 | ALTER TABLE civicrm_pledge DROP honor_type_id; |
| 163 | |
| 164 | -- CRM-13964 and CRM-13965 |
| 165 | SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status'; |
| 166 | SELECT @option_val_id_cs_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; |
| 167 | SELECT @option_val_id_cs_val := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; |
| 168 | |
| 169 | INSERT INTO |
| 170 | `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
| 171 | VALUES |
| 172 | (@option_group_id_cs, {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, @option_val_id_cs_val+1, 'Partially paid', NULL, 0, NULL, @option_val_id_cs_wt+1, 0, 1, 1, NULL, NULL), |
| 173 | (@option_group_id_cs, {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, @option_val_id_cs_val+2, 'Pending refund', NULL, 0, NULL, @option_val_id_cs_wt+2, 0, 1, 1, NULL, NULL); |
| 174 | |
| 175 | -- participant status adding |
| 176 | SELECT @participant_status_wt := max(id) from civicrm_participant_status_type; |
| 177 | |
| 178 | INSERT INTO civicrm_participant_status_type (name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id) |
| 179 | VALUES |
| 180 | ('Partially paid', {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+1, 2), |
| 181 | ('Pending refund', {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+2, 2); |
| 182 | |
| 183 | -- new activity types required for partial payments |
| 184 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; |
| 185 | SELECT @option_group_id_act_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; |
| 186 | SELECT @option_group_id_act_val := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; |
| 187 | SELECT @contributeCompId := max(id) FROM civicrm_component where name = 'CiviContribute'; |
| 188 | |
| 189 | INSERT INTO |
| 190 | `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) |
| 191 | VALUES |
| 192 | (@option_group_id_act, {localize}'{ts escape="sql"}Payment{/ts}'{/localize}, @option_group_id_act_val+1, 'Payment', NULL, 1, NULL, @option_group_id_act_wt+1, {localize}'{ts escape="sql"}Additional payment recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL), |
| 193 | (@option_group_id_act, {localize}'{ts escape="sql"}Refund{/ts}'{/localize}, @option_group_id_act_val+2, 'Refund', NULL, 1, NULL, @option_group_id_act_wt+2, {localize}'{ts escape="sql"}Refund recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL), |
| 194 | (@option_group_id_act, {localize}'{ts escape="sql"}Change Registration{/ts}'{/localize}, @option_group_id_act_val+3, 'Change Registration', NULL, 1, NULL, @option_group_id_act_wt+3, {localize}'{ts escape="sql"}Changes to an existing event registration.{/ts}'{/localize}, 0, 1, 1, @eventCompId, NULL); |
| 195 | |
| 196 | -- CRM-13970 |
| 197 | UPDATE civicrm_navigation set url = 'civicrm/admin/options/from_email_address&reset=1' WHERE url LIKE 'civicrm/admin/options/from_email%'; |
| 198 | UPDATE civicrm_navigation set url = CONCAT(SUBSTRING_INDEX(url, '&', 1), '&reset=1') WHERE url LIKE 'civicrm/admin/options/%'; |
| 199 | |
| 200 | -- CRM-14181 |
| 201 | ALTER TABLE civicrm_acl CHANGE operation operation VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?'; |
| 202 | ALTER TABLE civicrm_campaign_group CHANGE group_type group_type VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Type of Group.'; |
| 203 | ALTER TABLE `civicrm_acl_contact_cache` CHANGE `operation` `operation` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this user have permission on?'; |
| 204 | ALTER TABLE `civicrm_price_field` CHANGE `html_type` `html_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; |
| 205 | ALTER TABLE `civicrm_pledge` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Time units for recurrence of pledge payments.'; |
| 206 | ALTER TABLE `civicrm_membership_type` CHANGE `duration_unit` `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit in which membership period is expressed.'; |
| 207 | ALTER TABLE `civicrm_membership_type` CHANGE `period_type` `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.'; |
| 208 | ALTER TABLE `civicrm_membership_status` CHANGE `start_event` `start_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Event when this status starts.'; |
| 209 | ALTER TABLE `civicrm_membership_status` CHANGE `start_event_adjust_unit` `start_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit used for adjusting from start_event.'; |
| 210 | ALTER TABLE `civicrm_membership_status` CHANGE `end_event` `end_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Event after which this status ends.'; |
| 211 | ALTER TABLE `civicrm_membership_status` CHANGE `end_event_adjust_unit` `end_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Unit used for adjusting from the ending event.'; |
| 212 | ALTER TABLE `civicrm_mailing_job` CHANGE `status` `status` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of this job'; |
| 213 | ALTER TABLE `civicrm_mailing_group` CHANGE `group_type` `group_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Are the members of the group included or excluded?.'; |
| 214 | ALTER TABLE `civicrm_mailing` CHANGE `visibility` `visibility` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is the mailing contents visible (online viewing)'; |
| 215 | ALTER TABLE `civicrm_mailing_component` CHANGE `component_type` `component_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Type of Component.'; |
| 216 | ALTER TABLE `civicrm_mailing_bounce_type` CHANGE `name` `name` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type of bounce'; |
| 217 | ALTER TABLE `civicrm_participant_status_type` CHANGE `class` `class` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'the general group of status type this one belongs to'; |
| 218 | ALTER TABLE `civicrm_dedupe_rule_group` CHANGE `contact_type` `contact_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The type of contacts this group applies to'; |
| 219 | ALTER TABLE `civicrm_dedupe_rule_group` CHANGE `used` `used` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)'; |
| 220 | ALTER TABLE `civicrm_word_replacement` CHANGE `match_type` `match_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'wildcardMatch'; |
| 221 | ALTER TABLE `civicrm_uf_field` CHANGE `visibility` `visibility` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.'; |
| 222 | ALTER TABLE `civicrm_mapping_field` CHANGE `operator` `operator` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).'; |
| 223 | ALTER TABLE `civicrm_job` CHANGE `run_frequency` `run_frequency` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'Daily' COMMENT 'Scheduled job run frequency.'; |
| 224 | ALTER TABLE `civicrm_extension` CHANGE `type` `type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; |
| 225 | ALTER TABLE `civicrm_custom_group` CHANGE `style` `style` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.'; |
| 226 | ALTER TABLE `civicrm_custom_field` CHANGE `data_type` `data_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Controls location of data storage in extended_data table.'; |
| 227 | ALTER TABLE `civicrm_custom_field` CHANGE `html_type` `html_type` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'HTML types plus several built-in extended types.'; |
| 228 | ALTER TABLE `civicrm_action_schedule` CHANGE `start_action_unit` `start_action_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units for reminder.'; |
| 229 | ALTER TABLE `civicrm_action_schedule` CHANGE `repetition_frequency_unit` `repetition_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units for repetition of reminder.'; |
| 230 | ALTER TABLE `civicrm_action_schedule` CHANGE `end_frequency_unit` `end_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Time units till repetition of reminder.'; |
| 231 | ALTER TABLE `civicrm_product` CHANGE `period_type` `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) '; |
| 232 | ALTER TABLE `civicrm_product` CHANGE `duration_unit` `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'year'; |
| 233 | ALTER TABLE `civicrm_product` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.'; |
| 234 | ALTER TABLE `civicrm_contribution_recur` CHANGE `frequency_unit` `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'month' COMMENT 'Time units for recurrence of payment.'; |
| 235 | ALTER TABLE `civicrm_subscription_history` CHANGE `method` `method` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'How the (un)subscription was triggered'; |
| 236 | ALTER TABLE `civicrm_subscription_history` CHANGE `status` `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of the contact within the group'; |
| 237 | ALTER TABLE `civicrm_relationship_type` CHANGE `contact_type_a` `contact_type_a` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.'; |
| 238 | ALTER TABLE `civicrm_relationship_type` CHANGE `contact_type_b` `contact_type_b` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_b in a relationship of this type must be a specific contact_type.'; |
| 239 | ALTER TABLE `civicrm_group_contact` CHANGE `status` `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'status of contact relative to membership in group'; |
| 240 | ALTER TABLE `civicrm_group` CHANGE `visibility` `visibility` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.'; |
| 241 | ALTER TABLE `civicrm_contact` CHANGE `preferred_mail_format` `preferred_mail_format` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'Both' COMMENT 'What is the preferred mode of sending an email.'; |
| 242 | |
| 243 | -- CRM-14183 |
| 244 | INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1157, "PL", "Plateau"); |
| 245 | UPDATE civicrm_state_province SET name = "Abuja Federal Capital Territory" WHERE name = "Abuja Capital Territory"; |
| 246 | |
| 247 | -- CRM-13992 |
| 248 | ALTER TABLE `civicrm_custom_field` |
| 249 | ADD COLUMN `in_selector` tinyint(4) DEFAULT '0' COMMENT 'Should the multi-record custom field values be displayed in tab table listing'; |
| 250 | UPDATE civicrm_custom_field cf |
| 251 | LEFT JOIN civicrm_custom_group cg |
| 252 | ON cf.custom_group_id = cg.id |
| 253 | SET cf.in_selector = 1 |
| 254 | WHERE cg.is_multiple = 1 AND cf.html_type != 'TextArea'; |
| 255 | ALTER TABLE `civicrm_custom_group` |
| 256 | CHANGE COLUMN `style` `style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.'; |
| 257 | |
| 258 | -- Add "developer" help menu |
| 259 | SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID}; |
| 260 | |
| 261 | INSERT INTO civicrm_navigation |
| 262 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 263 | VALUES |
| 264 | ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 ); |
| 265 | |
| 266 | SET @devellastID:=LAST_INSERT_ID(); |
| 267 | INSERT INTO civicrm_navigation |
| 268 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 269 | VALUES |
| 270 | ( {$domainID}, 'civicrm/api', '{ts escape="sql" skip="true"}API Explorer{/ts}','API Explorer', 'administer CiviCRM', '', @devellastID, '1', NULL, 1 ), |
| 271 | ( {$domainID}, 'http://wiki.civicrm.org/confluence/display/CRMDOC/Develop', '{ts escape="sql" skip="true"}Developer Docs{/ts}', 'Developer Docs', 'administer CiviCRM', '', @devellastID, '1', NULL, 3 ); |
| 272 | |
| 273 | -- CRM-14435 |
| 274 | ALTER TABLE `civicrm_mail_settings` |
| 275 | ADD CONSTRAINT `FK_civicrm_mail_settings_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE; |
| 276 | |
| 277 | -- CRM-14436 |
| 278 | ALTER TABLE `civicrm_mailing` |
| 279 | ADD COLUMN `hash` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this mailing.', |
| 280 | ADD INDEX `index_hash` (`hash`); |
| 281 | |
| 282 | -- CRM-14300 |
| 283 | UPDATE `civicrm_event` SET is_template = 0 WHERE is_template IS NULL; |
| 284 | ALTER TABLE `civicrm_event` |
| 285 | CHANGE is_template is_template tinyint(4) DEFAULT '0' COMMENT 'whether the event has template'; |
| 286 | |
| 287 | -- CRM-14493 |
| 288 | INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria"); |
| 289 | |
| 290 | -- CRM-14445 |
| 291 | ALTER TABLE `civicrm_option_group` |
| 292 | ADD COLUMN `is_locked` int(1) DEFAULT 0 COMMENT 'A lock to remove the ability to add new options via the UI'; |
| 293 | |
| 294 | UPDATE `civicrm_option_group` SET is_locked = 1 WHERE name IN ('contribution_status','activity_contacts','advanced_search_options','auto_renew_options','contact_autocomplete_options','batch_status','batch_type','batch_mode','contact_edit_options','contact_reference_options','contact_smart_group_display','contact_view_options','financial_item_status','mapping_type','pcp_status','user_dashboard_options','tag_used_for'); |
| 295 | |
| 296 | -- CRM-14449 |
| 297 | CREATE TABLE IF NOT EXISTS `civicrm_system_log` ( |
| 298 | `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: ID.', |
| 299 | `message` VARCHAR(128) NOT NULL COMMENT 'Standardized message', |
| 300 | `context` LONGTEXT NULL COMMENT 'JSON encoded data', |
| 301 | `level` VARCHAR(9) NOT NULL DEFAULT 'info' COMMENT 'error level per PSR3', |
| 302 | `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.', |
| 303 | `contact_id` INT(11) NULL DEFAULT NULL COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless', |
| 304 | hostname VARCHAR(128) NOT NULL COMMENT 'Optional Name of logging host', |
| 305 | PRIMARY KEY (`id`), |
| 306 | INDEX `message` (`message`), |
| 307 | INDEX `contact_id` (`contact_id`), |
| 308 | INDEX `level` (`level`) |
| 309 | ) |
| 310 | COMMENT='Table that contains logs of all system events.' |
| 311 | COLLATE='utf8_general_ci'; |
| 312 | |
| 313 | -- CRM-14473 civicrm_case_type table creation and migration |
| 314 | CREATE TABLE IF NOT EXISTS `civicrm_case_type` ( |
| 315 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id', |
| 316 | `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type', |
| 317 | {localize field='title'}title varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type'{/localize}, |
| 318 | {localize field='description'}description varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type'{/localize}, |
| 319 | `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?', |
| 320 | `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?', |
| 321 | `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types', |
| 322 | `definition` blob COMMENT 'xml definition of case type', |
| 323 | PRIMARY KEY (`id`), |
| 324 | UNIQUE KEY `case_type_name` (`name`) |
| 325 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
| 326 | |
| 327 | SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type'; |
| 328 | |
| 329 | INSERT IGNORE INTO civicrm_case_type |
| 330 | (id, name, {localize field='title'}title{/localize}, {localize field='description'}description{/localize}, is_active, is_reserved, weight) |
| 331 | SELECT |
| 332 | value, |
| 333 | name, |
| 334 | {localize field='label'}label{/localize}, |
| 335 | {localize field='description'}description{/localize}, |
| 336 | is_active, |
| 337 | is_reserved, |
| 338 | weight |
| 339 | FROM civicrm_option_value |
| 340 | WHERE |
| 341 | option_group_id = @option_group_id_case_type; |
| 342 | |
| 343 | -- Remove the special character, earlier used as a separator and reference to civicrm_case_type.id |
| 344 | UPDATE civicrm_case SET case_type_id = replace(case_type_id, 0x01, ''); |
| 345 | |
| 346 | ALTER TABLE civicrm_case |
| 347 | MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id', |
| 348 | ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL; |
| 349 | |
| 350 | -- CRM-15343 set the auto increment civicrm_case_type.id start point to max id to avoid conflict in future insertion |
| 351 | SELECT @max_case_type_id := max(id) from civicrm_case_type; |
| 352 | SET @query = CONCAT("ALTER TABLE civicrm_case_type AUTO_INCREMENT = ", IFNULL(@max_case_type_id,1)); |
| 353 | PREPARE alter_case_type_auto_inc FROM @query; |
| 354 | EXECUTE alter_case_type_auto_inc; |
| 355 | DEALLOCATE PREPARE alter_case_type_auto_inc; |
| 356 | |
| 357 | DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type; |
| 358 | |
| 359 | DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type; |
| 360 | |
| 361 | -- CRM-14611 |
| 362 | {if $multilingual} |
| 363 | {foreach from=$locales item=locale} |
| 364 | ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255); |
| 365 | UPDATE civicrm_survey SET title_{$locale} = title; |
| 366 | |
| 367 | ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT; |
| 368 | UPDATE civicrm_survey SET instructions_{$locale} = instructions; |
| 369 | {/foreach} |
| 370 | |
| 371 | ALTER TABLE civicrm_survey DROP title; |
| 372 | ALTER TABLE civicrm_survey DROP instructions; |
| 373 | {/if} |
| 374 | |
| 375 | -- CRM-11182 -- Make event confirmation page optional |
| 376 | ALTER TABLE civicrm_event |
| 377 | ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1'; |
| 378 | |
| 379 | UPDATE civicrm_event |
| 380 | SET is_confirm_enabled = 1 |
| 381 | WHERE is_monetary = 1; |
| 382 | |
| 383 | UPDATE civicrm_event |
| 384 | SET is_confirm_enabled = 0 |
| 385 | WHERE is_monetary = 0; |
| 386 | |
| 387 | -- CRM-11182 |
| 388 | ALTER TABLE civicrm_event |
| 389 | ADD COLUMN dedupe_rule_group_id int(10) unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event', |
| 390 | ADD CONSTRAINT `FK_civicrm_event_dedupe_rule_group_id` FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group` (`id`); |
| 391 | |
| 392 | -- CRM-9288 |
| 393 | SELECT @option_web_id := id FROM civicrm_option_group WHERE name = 'website_type'; |
| 394 | |
| 395 | SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1; |
| 396 | |
| 397 | SELECT @website_work := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and name= 'Work'; |
| 398 | |
| 399 | UPDATE civicrm_option_value |
| 400 | SET is_default = 1 WHERE option_group_id = @option_web_id and value = IFNULL(@website_default , @website_work); |
| 401 | |
| 402 | SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1; |
| 403 | |
| 404 | ALTER TABLE civicrm_uf_field |
| 405 | ADD COLUMN `website_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Website Type Id, if required' AFTER phone_type_id, |
| 406 | ADD INDEX `IX_website_type_id` (`website_type_id`); |
| 407 | |
| 408 | UPDATE civicrm_uf_field |
| 409 | SET website_type_id = @website_default, |
| 410 | field_name = 'url' WHERE field_name LIKE 'url%'; |
| 411 | |
| 412 | SELECT @website_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_web_id; |
| 413 | SELECT @website_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_web_id; |
| 414 | |
| 415 | INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight) |
| 416 | SELECT @option_web_id, {localize}website{/localize}, website, (@website_value := @website_value + 1), (@website_weight := @website_weight + 1) FROM ( |
| 417 | SELECT 'Google+' AS website |
| 418 | UNION ALL |
| 419 | SELECT 'Instagram' AS website |
| 420 | UNION ALL |
| 421 | SELECT 'LinkedIn' AS website |
| 422 | UNION ALL |
| 423 | SELECT 'Pinterest' AS website |
| 424 | UNION ALL |
| 425 | SELECT 'Tumblr' AS website |
| 426 | UNION ALL |
| 427 | SELECT 'SnapChat' AS website |
| 428 | UNION ALL |
| 429 | SELECT 'Vine' AS website |
| 430 | ) AS temp |
| 431 | LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website) |
| 432 | AND option_group_id = @option_web_id |
| 433 | WHERE co.id IS NULL; |
| 434 | |
| 435 | -- CRM-14627 civicrm navigation inconsistent |
| 436 | UPDATE civicrm_navigation |
| 437 | SET civicrm_navigation.url = CONCAT(SUBSTRING(url FROM 1 FOR LOCATE('&', url) - 1), '?', SUBSTRING(url FROM LOCATE('&', url) + 1)) |
| 438 | WHERE civicrm_navigation.url LIKE "%&%" AND civicrm_navigation.url NOT LIKE "%?%"; |
| 439 | |
| 440 | -- CRM-14478 Add a "cleanup" policy for managed entities |
| 441 | ALTER TABLE `civicrm_managed` |
| 442 | ADD COLUMN `cleanup` varchar(32) COMMENT 'Policy on when to cleanup entity (always, never, unused)'; |
| 443 | |
| 444 | -- CRM-14639 |
| 445 | |
| 446 | SELECT @option_grant_status := id FROM civicrm_option_group WHERE name = 'grant_status'; |
| 447 | UPDATE civicrm_option_value |
| 448 | SET |
| 449 | {if !$multilingual} |
| 450 | label = |
| 451 | CASE |
| 452 | WHEN lower(name) = 'granted' |
| 453 | THEN 'Paid' |
| 454 | WHEN lower(name) = 'approved' |
| 455 | THEN 'Eligible' |
| 456 | WHEN lower(name) = 'rejected' |
| 457 | THEN 'Ineligible' |
| 458 | ELSE 'Submitted' |
| 459 | END, |
| 460 | {else} |
| 461 | {foreach from=$locales item=locale} |
| 462 | label_{$locale} = |
| 463 | CASE |
| 464 | WHEN lower(name) = 'granted' |
| 465 | THEN 'Paid' |
| 466 | WHEN lower(name) = 'approved' |
| 467 | THEN 'Eligible' |
| 468 | WHEN lower(name) = 'rejected' |
| 469 | THEN 'Ineligible' |
| 470 | ELSE 'Submitted' |
| 471 | END, |
| 472 | {/foreach} |
| 473 | {/if} |
| 474 | name = |
| 475 | CASE |
| 476 | WHEN lower(name) = 'granted' |
| 477 | THEN 'Paid' |
| 478 | WHEN lower(name) = 'approved' |
| 479 | THEN 'Eligible' |
| 480 | WHEN lower(name) = 'rejected' |
| 481 | THEN 'Ineligible' |
| 482 | ELSE 'Submitted' |
| 483 | END |
| 484 | WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected'); |
| 485 | |
| 486 | SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; |
| 487 | SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; |
| 488 | |
| 489 | INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight) |
| 490 | SELECT @option_grant_status, {localize}grantstatus{/localize}, grantstatus, @grant_value := @grant_value + 1, @grant_weight := @grant_weight + 1 FROM ( |
| 491 | SELECT 'Submitted' AS grantstatus |
| 492 | UNION ALL |
| 493 | SELECT 'Approved for Payment' AS grantstatus |
| 494 | UNION ALL |
| 495 | SELECT 'Eligible' AS grantstatus |
| 496 | UNION ALL |
| 497 | SELECT 'Awaiting Information' AS grantstatus |
| 498 | UNION ALL |
| 499 | SELECT 'Withdrawn' AS grantstatus |
| 500 | ) AS temp |
| 501 | LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus) |
| 502 | AND option_group_id = @option_grant_status |
| 503 | WHERE co.id IS NULL; |
| 504 | |
| 505 | -- Fix trailing single quote in grant status label |
| 506 | {if !$multilingual} |
| 507 | UPDATE civicrm_option_value v |
| 508 | INNER JOIN civicrm_option_group g |
| 509 | ON v.option_group_id = g.id AND g.name = 'grant_status' |
| 510 | SET label = 'Awaiting Information' |
| 511 | WHERE v.label = 'Awaiting Information\'' and v.name = 'Awaiting Information'; |
| 512 | {else} |
| 513 | UPDATE civicrm_option_value v |
| 514 | INNER JOIN civicrm_option_group g |
| 515 | ON v.option_group_id = g.id AND g.name = 'grant_status' |
| 516 | SET |
| 517 | {foreach from=$locales item=locale} |
| 518 | v.label_{$locale} = CASE |
| 519 | WHEN v.label_{$locale} = 'Awaiting Information\'' THEN 'Awaiting Information' |
| 520 | ELSE v.label_{$locale} |
| 521 | END, |
| 522 | {/foreach} |
| 523 | v.name = v.name |
| 524 | WHERE v.name = 'Awaiting Information'; |
| 525 | {/if} |
| 526 | |
| 527 | -- CRM-14197 Add contribution_id to civicrm_line_item |
| 528 | |
| 529 | ALTER TABLE civicrm_line_item ADD contribution_id INT(10) unsigned COMMENT 'Contribution ID' NULL AFTER entity_id; |
| 530 | |
| 531 | -- FK to civicrm_contribution |
| 532 | |
| 533 | ALTER TABLE civicrm_line_item |
| 534 | ADD CONSTRAINT `FK_civicrm_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES civicrm_contribution (`id`) ON DELETE SET NULL; |
| 535 | |
| 536 | ALTER TABLE `civicrm_line_item` |
| 537 | DROP INDEX `UI_line_item_value`, |
| 538 | ADD UNIQUE INDEX `UI_line_item_value` (`entity_table`, `entity_id`, `contribution_id`, `price_field_value_id`, `price_field_id`); |
| 539 | |
| 540 | -- update case type menu |
| 541 | UPDATE civicrm_navigation set url = 'civicrm/a/#/caseType' WHERE url LIKE 'civicrm/admin/options/case_type%'; |