Commit | Line | Data |
---|---|---|
296342b1 | 1 | {* file to handle db changes in 4.5.alpha1 during upgrade *} |
1421174e | 2 | {include file='../CRM/Upgrade/4.5.alpha1.msg_template/civicrm_msg_template.tpl'} |
3 | ||
e171748b OB |
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`; | |
aa62b355 OB |
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 | |
6c559730 | 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`) |
aa62b355 | 20 | VALUES |
6c559730 ML |
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); | |
d4311a7a OB |
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 ); | |
b97b088e | 38 | |
39 | -- CRM-9988 Change world region of Panama country to America South, Central, North and Caribbean | |
8b49cb50 OB |
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 | |
6c559730 | 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`) |
8b49cb50 | 46 | VALUES |
6c559730 ML |
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); | |
66055e1b | 53 | |
d0dfb649 PJ |
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.', | |
50d42431 TO |
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; | |
51fa20cb | 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 | ||
5fec2f6c | 69 | INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`) |
51fa20cb | 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), | |
12792976 | 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); | |
51fa20cb | 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 | ||
f7727289 | 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 | ||
51fa20cb | 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; | |
61a46288 | 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 | |
a3e3eea1 | 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 | |
43d1a3df | 117 | civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active) |
a3e3eea1 | 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); | |
0ac434a3 | 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; | |
133e2c99 | 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 | ||
1421174e | 132 | SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual'; |
133e2c99 | 133 | |
134 | INSERT INTO `civicrm_uf_field` | |
8381af80 | 135 | (`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) |
133e2c99 | 136 | VALUES |
6c559730 ML |
137 | (@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'), |
138 | (@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'), | |
139 | (@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'), | |
140 | (@uf_group_id_honoree_individual, 'email', 0, 1, 4, 'User and User Admin Only', 0, 1, 1, {localize}'{ts escape="sql"}Email Address{/ts}'{/localize}, 'Individual'); | |
133e2c99 | 141 | |
f7727289 | 142 | UPDATE civicrm_uf_join SET uf_group_id = @uf_group_id_honoree_individual WHERE module = 'soft_credit'; |
143 | ||
133e2c99 | 144 | {if $multilingual} |
145 | {foreach from=$locales item=loc} | |
146 | ALTER TABLE civicrm_contribution_page DROP honor_block_title_{$loc}; | |
147 | ALTER TABLE civicrm_contribution_page DROP honor_block_text_{$loc}; | |
148 | {/foreach} | |
149 | {else} | |
150 | ALTER TABLE civicrm_contribution_page DROP honor_block_title; | |
151 | ALTER TABLE civicrm_contribution_page DROP honor_block_text; | |
152 | {/if} | |
8af73472 | 153 | ALTER TABLE civicrm_contribution_page DROP honor_block_is_active; |
133e2c99 | 154 | |
1421174e | 155 | ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`; |
133e2c99 | 156 | ALTER TABLE civicrm_contribution DROP honor_contact_id; |
157 | ALTER TABLE civicrm_contribution DROP honor_type_id; | |
158 | ||
1421174e | 159 | ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`; |
133e2c99 | 160 | ALTER TABLE civicrm_pledge DROP honor_contact_id; |
161 | ALTER TABLE civicrm_pledge DROP honor_type_id; | |
5ce9cbe9 | 162 | |
0f602e3f | 163 | -- CRM-13964 and CRM-13965 |
59e44db1 PJ |
164 | SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status'; |
165 | SELECT @option_val_id_cs_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; | |
166 | SELECT @option_val_id_cs_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; | |
167 | ||
168 | INSERT INTO | |
169 | `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`) | |
170 | VALUES | |
0f602e3f PJ |
171 | (@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), |
172 | (@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); | |
173 | ||
59e44db1 PJ |
174 | -- participant status adding |
175 | SELECT @participant_status_wt := max(id) from civicrm_participant_status_type; | |
176 | ||
0f602e3f PJ |
177 | INSERT INTO civicrm_participant_status_type (name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id) |
178 | VALUES | |
179 | ('Partially paid', {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+1, 2), | |
180 | ('Pending refund', {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+2, 2); | |
181 | ||
182 | -- new activity types required for partial payments | |
183 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; | |
184 | SELECT @option_group_id_act_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
0f1ecc2b | 185 | SELECT @option_group_id_act_val := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; |
0f602e3f PJ |
186 | SELECT @contributeCompId := max(id) FROM civicrm_component where name = 'CiviContribute'; |
187 | ||
188 | INSERT INTO | |
189 | `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`) | |
59e44db1 | 190 | VALUES |
0f602e3f | 191 | (@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), |
2429e40c | 192 | (@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), |
5ce9cbe9 | 193 | (@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); |
194 | ||
6c2473d5 CW |
195 | -- CRM-13970 |
196 | UPDATE civicrm_navigation set url = 'civicrm/admin/options/from_email_address&reset=1' WHERE url LIKE 'civicrm/admin/options/from_email%'; | |
6c2473d5 | 197 | UPDATE civicrm_navigation set url = CONCAT(SUBSTRING_INDEX(url, '&', 1), '&reset=1') WHERE url LIKE 'civicrm/admin/options/%'; |
be622aca | 198 | |
199 | -- CRM-14181 | |
200 | 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?'; | |
201 | 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.'; | |
202 | 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?'; | |
203 | ALTER TABLE `civicrm_price_field` CHANGE `html_type` `html_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; | |
204 | 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.'; | |
205 | 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.'; | |
206 | 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.'; | |
207 | 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.'; | |
208 | 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.'; | |
209 | 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.'; | |
210 | 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.'; | |
f3800cf8 | 211 | 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'; |
212 | 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?.'; | |
213 | 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)'; | |
214 | 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.'; | |
b1a243c1 | 215 | ALTER TABLE `civicrm_mailing_bounce_type` CHANGE `name` `name` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type of bounce'; |
909ca5f6 | 216 | 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'; |
217 | 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'; | |
218 | 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)'; | |
219 | ALTER TABLE `civicrm_word_replacement` CHANGE `match_type` `match_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'wildcardMatch'; | |
220 | 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.'; | |
221 | 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).'; | |
d9cf711e | 222 | 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.'; |
223 | ALTER TABLE `civicrm_extension` CHANGE `type` `type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL; | |
224 | 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.'; | |
225 | 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.'; | |
226 | 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.'; | |
227 | 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.'; | |
228 | 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.'; | |
229 | 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.'; | |
dc73c80d | 230 | 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) '; |
231 | ALTER TABLE `civicrm_product` CHANGE `duration_unit` `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'year'; | |
232 | 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.'; | |
233 | 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.'; | |
234 | 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'; | |
235 | 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'; | |
236 | 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.'; | |
237 | 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.'; | |
238 | 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'; | |
239 | 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.'; | |
240 | 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.'; | |
909ca5f6 | 241 | |
50d45c74 | 242 | -- CRM-14183 |
ce1a3b9c | 243 | INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1157, "PL", "Plateau"); |
50d45c74 | 244 | UPDATE civicrm_state_province SET name = "Abuja Federal Capital Territory" WHERE name = "Abuja Capital Territory"; |
5a205b89 PJ |
245 | |
246 | -- CRM-13992 | |
247 | ALTER TABLE `civicrm_custom_field` | |
248 | ADD COLUMN `in_selector` tinyint(4) DEFAULT '0' COMMENT 'Should the multi-record custom field values be displayed in tab table listing'; | |
249 | UPDATE civicrm_custom_field cf | |
250 | LEFT JOIN civicrm_custom_group cg | |
251 | ON cf.custom_group_id = cg.id | |
252 | SET cf.in_selector = 1 | |
b05a0fb6 PJ |
253 | WHERE cg.is_multiple = 1 AND cf.html_type != 'TextArea'; |
254 | ALTER TABLE `civicrm_custom_group` | |
12792976 | 255 | CHANGE COLUMN `style` `style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.'; |
ab4c268a CW |
256 | |
257 | -- Add "developer" help menu | |
258 | SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID}; | |
259 | ||
260 | INSERT INTO civicrm_navigation | |
261 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
262 | VALUES | |
263 | ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 ); | |
264 | ||
265 | SET @devellastID:=LAST_INSERT_ID(); | |
266 | INSERT INTO civicrm_navigation | |
267 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
268 | VALUES | |
269 | ( {$domainID}, 'civicrm/api/explorer', '{ts escape="sql" skip="true"}API Explorer{/ts}','API Explorer', 'administer CiviCRM', '', @devellastID, '1', NULL, 1 ), | |
270 | ( {$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 ); | |
271 | ||
41876fd8 PJ |
272 | -- CRM-14435 |
273 | ALTER TABLE `civicrm_mail_settings` | |
52068ff5 | 274 | ADD CONSTRAINT `FK_civicrm_mail_settings_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE; |
c57f36a1 PJ |
275 | |
276 | -- CRM-14436 | |
277 | ALTER TABLE `civicrm_mailing` | |
278 | ADD COLUMN `hash` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this mailing.', | |
4ab2e0d4 | 279 | ADD INDEX `index_hash` (`hash`); |
280 | ||
99f13d8d | 281 | -- CRM-14300 |
282 | UPDATE `civicrm_event` SET is_template = 0 WHERE is_template IS NULL; | |
283 | ALTER TABLE `civicrm_event` | |
284 | CHANGE is_template is_template tinyint(4) DEFAULT '0' COMMENT 'whether the event has template'; | |
7ad9c1df | 285 | |
286 | -- CRM-14493 | |
1971831e | 287 | INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria"); |
cede6590 DG |
288 | |
289 | -- CRM-14445 | |
290 | ALTER TABLE `civicrm_option_group` | |
9e08bf23 | 291 | ADD COLUMN `is_locked` int(1) DEFAULT 0 COMMENT 'A lock to remove the ability to add new options via the UI'; |
cede6590 | 292 | |
5fec2f6c EM |
293 | 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'); |
294 | ||
e2bef985 | 295 | -- CRM-14449 |
0af59d41 | 296 | CREATE TABLE IF NOT EXISTS `civicrm_system_log` ( |
e2bef985 | 297 | `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: ID.', |
298 | `message` VARCHAR(128) NOT NULL COMMENT 'Standardized message', | |
299 | `context` LONGTEXT NULL COMMENT 'JSON encoded data', | |
300 | `level` VARCHAR(9) NOT NULL DEFAULT 'info' COMMENT 'error level per PSR3', | |
301 | `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.', | |
302 | `contact_id` INT(11) NULL DEFAULT NULL COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless', | |
303 | hostname VARCHAR(128) NOT NULL COMMENT 'Optional Name of logging host', | |
304 | PRIMARY KEY (`id`), | |
305 | INDEX `message` (`message`), | |
306 | INDEX `contact_id` (`contact_id`), | |
307 | INDEX `level` (`level`) | |
308 | ) | |
309 | COMMENT='Table that contains logs of all system events.' | |
310 | COLLATE='utf8_general_ci'; | |
311 | ||
fc9e7e51 ARW |
312 | -- CRM-14473 civicrm_case_type table creation and migration |
313 | CREATE TABLE IF NOT EXISTS `civicrm_case_type` ( | |
314 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id', | |
315 | `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type', | |
c0776792 ARW |
316 | {localize field='title'}title varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type'{/localize}, |
317 | {localize field='description'}description varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type'{/localize}, | |
fc9e7e51 ARW |
318 | `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?', |
319 | `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?', | |
320 | `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types', | |
12341e79 | 321 | `definition` blob COMMENT 'xml definition of case type', |
fc9e7e51 ARW |
322 | PRIMARY KEY (`id`), |
323 | UNIQUE KEY `case_type_name` (`name`) | |
324 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; | |
325 | ||
326 | SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type'; | |
327 | ||
328 | INSERT IGNORE INTO civicrm_case_type | |
0cc5fafb | 329 | (id, name, {localize field='title'}title{/localize}, {localize field='description'}description{/localize}, is_active, is_reserved, weight) |
fc9e7e51 | 330 | SELECT |
0cc5fafb | 331 | value, |
fc9e7e51 | 332 | name, |
c0776792 ARW |
333 | {localize field='label'}label{/localize}, |
334 | {localize field='description'}description{/localize}, | |
fc9e7e51 ARW |
335 | is_active, |
336 | is_reserved, | |
337 | weight | |
338 | FROM civicrm_option_value | |
339 | WHERE | |
340 | option_group_id = @option_group_id_case_type; | |
341 | ||
0cc5fafb | 342 | --- Remove the special character, earlier used as a separator and reference to civicrm_case_type.id |
343 | UPDATE civicrm_case SET case_type_id = replace(case_type_id, 0x01, ''); | |
fc9e7e51 ARW |
344 | |
345 | ALTER TABLE civicrm_case | |
d6465502 ARW |
346 | MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id', |
347 | ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL; | |
fc9e7e51 | 348 | |
0cc5fafb | 349 | -- CRM-15343 set the auto increment civicrm_case_type.id start point to max id to avoid conflict in future insertion |
350 | SELECT @max_case_type_id := max(id) from civicrm_case_type; | |
351 | SET @query = CONCAT("ALTER TABLE civicrm_case_type AUTO_INCREMENT = ", @max_case_type_id); | |
0b0c13a3 | 352 | PREPARE alter_case_type_auto_inc FROM @query; |
353 | EXECUTE alter_case_type_auto_inc; | |
354 | DEALLOCATE PREPARE alter_case_type_auto_inc; | |
0cc5fafb | 355 | |
fc9e7e51 ARW |
356 | DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type; |
357 | ||
358 | DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type; | |
5d542dcd ML |
359 | |
360 | -- CRM-14611 | |
361 | {if $multilingual} | |
362 | {foreach from=$locales item=locale} | |
363 | ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255); | |
364 | UPDATE civicrm_survey SET title_{$locale} = title; | |
365 | ||
366 | ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT; | |
367 | UPDATE civicrm_survey SET instructions_{$locale} = instructions; | |
368 | {/foreach} | |
369 | ||
370 | ALTER TABLE civicrm_survey DROP title; | |
371 | ALTER TABLE civicrm_survey DROP instructions; | |
372 | {/if} | |
1909126f | 373 | |
374 | -- CRM-11182 -- Make event confirmation page optional | |
375 | ALTER TABLE civicrm_event | |
376 | ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1'; | |
377 | ||
378 | UPDATE civicrm_event | |
379 | SET is_confirm_enabled = 1 | |
380 | WHERE is_monetary = 1; | |
381 | ||
382 | UPDATE civicrm_event | |
383 | SET is_confirm_enabled = 0 | |
112d6be5 | 384 | WHERE is_monetary = 0; |
03390e26 | 385 | |
386 | -- CRM-11182 | |
387 | ALTER TABLE civicrm_event | |
388 | ADD COLUMN dedupe_rule_group_id int(10) unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event', | |
389 | ADD CONSTRAINT `FK_civicrm_event_dedupe_rule_group_id` FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group` (`id`); | |
c8458510 PN |
390 | |
391 | -- CRM-9288 | |
392 | SELECT @option_web_id := id FROM civicrm_option_group WHERE name = 'website_type'; | |
393 | ||
394 | SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1; | |
395 | ||
396 | SELECT @website_work := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and name= 'Work'; | |
397 | ||
398 | UPDATE civicrm_option_value | |
399 | SET is_default = 1 WHERE option_group_id = @option_web_id and value = IFNULL(@website_default , @website_work); | |
400 | ||
401 | SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1; | |
402 | ||
403 | ALTER TABLE civicrm_uf_field | |
404 | ADD COLUMN `website_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Website Type Id, if required' AFTER phone_type_id, | |
405 | ADD INDEX `IX_website_type_id` (`website_type_id`); | |
406 | ||
407 | UPDATE civicrm_uf_field | |
408 | SET website_type_id = @website_default, | |
409 | field_name = 'url' WHERE field_name LIKE 'url%'; | |
410 | ||
411 | SELECT @website_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_web_id; | |
412 | SELECT @website_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_web_id; | |
413 | ||
414 | INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight) | |
415 | SELECT @option_web_id, {localize}website{/localize}, website, (@website_value := @website_value + 1), (@website_weight := @website_weight + 1) FROM ( | |
416 | SELECT 'Google+' AS website | |
417 | UNION ALL | |
418 | SELECT 'Instagram' AS website | |
419 | UNION ALL | |
420 | SELECT 'LinkedIn' AS website | |
421 | UNION ALL | |
422 | SELECT 'Pinterest' AS website | |
423 | UNION ALL | |
424 | SELECT 'Tumblr' AS website | |
425 | UNION ALL | |
426 | SELECT 'SnapChat' AS website | |
427 | UNION ALL | |
428 | SELECT 'Vine' AS website | |
429 | ) AS temp | |
430 | LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website) | |
431 | AND option_group_id = @option_web_id | |
432 | WHERE co.id IS NULL; | |
710199c8 | 433 | |
434 | -- CRM-14627 civicrm navigation inconsistent | |
435 | UPDATE civicrm_navigation | |
436 | SET civicrm_navigation.url = CONCAT(SUBSTRING(url FROM 1 FOR LOCATE('&', url) - 1), '?', SUBSTRING(url FROM LOCATE('&', url) + 1)) | |
437 | WHERE civicrm_navigation.url LIKE "%&%" AND civicrm_navigation.url NOT LIKE "%?%"; | |
5948c543 | 438 | |
1f103dc4 TO |
439 | -- CRM-14478 Add a "cleanup" policy for managed entities |
440 | ALTER TABLE `civicrm_managed` | |
441 | ADD COLUMN `cleanup` varchar(32) COMMENT 'Policy on when to cleanup entity (always, never, unused)'; | |
442 | ||
5948c543 DG |
443 | -- CRM-14639 |
444 | ||
445 | SELECT @option_grant_status := id FROM civicrm_option_group WHERE name = 'grant_status'; | |
9e08bf23 | 446 | UPDATE civicrm_option_value |
5948c543 DG |
447 | SET |
448 | {if !$multilingual} | |
9e08bf23 EM |
449 | label = |
450 | CASE | |
5948c543 DG |
451 | WHEN lower(name) = 'granted' |
452 | THEN 'Paid' | |
453 | WHEN lower(name) = 'approved' | |
454 | THEN 'Eligible' | |
455 | WHEN lower(name) = 'rejected' | |
456 | THEN 'Ineligible' | |
457 | ELSE 'Submitted' | |
9e08bf23 | 458 | END, |
5948c543 DG |
459 | {else} |
460 | {foreach from=$locales item=locale} | |
9e08bf23 EM |
461 | label_{$locale} = |
462 | CASE | |
5948c543 | 463 | WHEN lower(name) = 'granted' |
9e08bf23 EM |
464 | THEN 'Paid' |
465 | WHEN lower(name) = 'approved' | |
466 | THEN 'Eligible' | |
467 | WHEN lower(name) = 'rejected' | |
468 | THEN 'Ineligible' | |
469 | ELSE 'Submitted' | |
5948c543 DG |
470 | END, |
471 | {/foreach} | |
472 | {/if} | |
9e08bf23 EM |
473 | name = |
474 | CASE | |
5948c543 DG |
475 | WHEN lower(name) = 'granted' |
476 | THEN 'Paid' | |
477 | WHEN lower(name) = 'approved' | |
478 | THEN 'Eligible' | |
479 | WHEN lower(name) = 'rejected' | |
480 | THEN 'Ineligible' | |
481 | ELSE 'Submitted' | |
482 | END | |
483 | WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected'); | |
484 | ||
485 | SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; | |
486 | SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status; | |
487 | ||
488 | INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight) | |
3144f16c | 489 | SELECT @option_grant_status, {localize}grantstatus{/localize}, grantstatus, @grant_value := @grant_value + 1, @grant_weight := @grant_weight + 1 FROM ( |
5948c543 DG |
490 | SELECT 'Submitted' AS grantstatus |
491 | UNION ALL | |
492 | SELECT 'Approved for Payment' AS grantstatus | |
493 | UNION ALL | |
494 | SELECT 'Eligible' AS grantstatus | |
495 | UNION ALL | |
496 | SELECT 'Awaiting Information' AS grantstatus | |
497 | UNION ALL | |
498 | SELECT 'Withdrawn' AS grantstatus | |
499 | ) AS temp | |
500 | LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus) | |
501 | AND option_group_id = @option_grant_status | |
502 | WHERE co.id IS NULL; | |
74cbd7fc DG |
503 | |
504 | -- Fix trailing single quote in grant status label | |
3144f16c PN |
505 | {if !$multilingual} |
506 | UPDATE civicrm_option_value v | |
9e08bf23 EM |
507 | INNER JOIN civicrm_option_group g |
508 | ON v.option_group_id = g.id AND g.name = 'grant_status' | |
509 | SET label = 'Awaiting Information' | |
510 | WHERE v.label = 'Awaiting Information\'' and v.name = 'Awaiting Information'; | |
3144f16c PN |
511 | {else} |
512 | UPDATE civicrm_option_value v | |
513 | INNER JOIN civicrm_option_group g | |
514 | ON v.option_group_id = g.id AND g.name = 'grant_status' | |
515 | SET | |
516 | {foreach from=$locales item=locale} | |
9e08bf23 | 517 | v.label_{$locale} = CASE |
3144f16c PN |
518 | WHEN v.label_{$locale} = 'Awaiting Information\'' THEN 'Awaiting Information' |
519 | ELSE v.label_{$locale} | |
520 | END, | |
521 | {/foreach} | |
522 | v.name = v.name | |
523 | WHERE v.name = 'Awaiting Information'; | |
9e08bf23 | 524 | {/if} |
77bec56d | 525 | |
a7886853 E |
526 | -- CRM-14197 Add contribution_id to civicrm_line_item |
527 | ||
161c15aa | 528 | ALTER TABLE civicrm_line_item ADD contribution_id INT(10) unsigned COMMENT 'Contribution ID' NULL AFTER entity_id; |
329682dd E |
529 | |
530 | -- FK to civicrm_contribution | |
a7886853 E |
531 | |
532 | ALTER TABLE civicrm_line_item | |
533 | ADD CONSTRAINT `FK_civicrm_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES civicrm_contribution (`id`) ON DELETE SET NULL; | |
534 | ||
c0da3432 EM |
535 | ALTER TABLE `civicrm_line_item` |
536 | DROP INDEX `UI_line_item_value`, | |
537 | ADD UNIQUE INDEX `UI_line_item_value` (`entity_table`, `entity_id`, `contribution_id`, `price_field_value_id`, `price_field_id`); | |
538 | ||
716749d1 | 539 | -- update case type menu |
540 | UPDATE civicrm_navigation set url = 'civicrm/a/#/caseType' WHERE url LIKE 'civicrm/admin/options/case_type%'; | |
541 |