Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | {include file='../CRM/Upgrade/4.2.alpha1.msg_template/civicrm_msg_template.tpl'} |
2 | ||
3 | -- CRM-9542 mailing detail report template | |
4 | SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template'; | |
5 | SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report; | |
6 | SELECT @mailCompId := MAX(id) FROM civicrm_component where name = 'CiviMail'; | |
7 | INSERT INTO civicrm_option_value | |
8 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES | |
9 | (@option_group_id_report, {localize}'Mail Detail Report'{/localize}, 'mailing/detail', 'CRM_Report_Form_Mailing_Detail', @weight := @weight + 1, {localize}'Provides reporting on Intended and Successful Deliveries, Unsubscribes and Opt-outs, Replies and Forwards.'{/localize}, 1, @mailCompId); | |
10 | ||
11 | INSERT INTO `civicrm_report_instance` | |
12 | ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`) | |
13 | VALUES | |
14 | ( {$domainID}, 'Mailing Detail Report', 'mailing/detail', 'Provides reporting on Intended and Successful Deliveries, Unsubscribes and Opt-outs, Replies and Forwards.', 'access CiviMail', '{literal}a:30:{s:6:"fields";a:6:{s:9:"sort_name";s:1:"1";s:12:"mailing_name";s:1:"1";s:11:"delivery_id";s:1:"1";s:14:"unsubscribe_id";s:1:"1";s:9:"optout_id";s:1:"1";s:5:"email";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:6:"id_min";s:0:"";s:6:"id_max";s:0:"";s:5:"id_op";s:3:"lte";s:8:"id_value";s:0:"";s:13:"mailing_id_op";s:2:"in";s:16:"mailing_id_value";a:0:{}s:18:"delivery_status_op";s:2:"eq";s:21:"delivery_status_value";s:0:"";s:18:"is_unsubscribed_op";s:2:"eq";s:21:"is_unsubscribed_value";s:0:"";s:12:"is_optout_op";s:2:"eq";s:15:"is_optout_value";s:0:"";s:13:"is_replied_op";s:2:"eq";s:16:"is_replied_value";s:0:"";s:15:"is_forwarded_op";s:2:"eq";s:18:"is_forwarded_value";s:0:"";s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:9:"order_bys";a:1:{i:1;a:2:{s:6:"column";s:9:"sort_name";s:5:"order";s:3:"ASC";}}s:11:"description";s:21:"Mailing Detail Report";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:15:"access CiviMail";s:9:"parent_id";s:0:"";s:6:"groups";s:0:"";s:9:"domain_id";i:1;}{/literal}'); | |
15 | ||
c55ee104 | 16 | SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports' AND domain_id = {$domainID}; |
6a488035 TO |
17 | SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID; |
18 | ||
19 | SET @instanceID:=LAST_INSERT_ID(); | |
20 | INSERT INTO civicrm_navigation | |
21 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
22 | VALUES | |
23 | ( {$domainID}, CONCAT('civicrm/report/instance/', @instanceID,'&reset=1'), '{ts escape="sql"}Mailing Detail Report{/ts}', 'Mailing Detail Report', 'access CiviMail', 'OR', @reportlastID, '1', NULL, @nav_max_weight+1 ); | |
24 | UPDATE civicrm_report_instance SET navigation_id = LAST_INSERT_ID() WHERE id = @instanceID; | |
25 | ||
26 | -- CRM-9600 | |
27 | ALTER TABLE `civicrm_custom_group` CHANGE `extends_entity_column_value` `extends_entity_column_value` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'linking custom group for dynamic object.'; | |
28 | ||
29 | -- CRM-9534 | |
30 | ALTER TABLE `civicrm_prevnext_cache` ADD COLUMN is_selected tinyint(4) DEFAULT '0'; | |
31 | ||
32 | -- CRM-9834 | |
33 | -- civicrm_batch table changes | |
34 | ALTER TABLE `civicrm_batch` ADD UNIQUE KEY `UI_name` ( name ); | |
35 | ||
36 | ||
37 | ALTER TABLE `civicrm_batch` ADD `saved_search_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Saved Search ID'; | |
38 | ALTER TABLE `civicrm_batch` ADD `status_id` int(10) unsigned NOT NULL COMMENT 'fk to Batch Status options in civicrm_option_values'; | |
39 | ALTER TABLE `civicrm_batch` ADD `type_id` int(10) unsigned NOT NULL COMMENT 'fk to Batch Type options in civicrm_option_values'; | |
40 | ALTER TABLE `civicrm_batch` ADD `mode_id` int(10) unsigned DEFAULT NULL COMMENT 'fk to Batch mode options in civicrm_option_values'; | |
41 | ALTER TABLE `civicrm_batch` ADD `total` decimal(20,2) DEFAULT NULL COMMENT 'Total amount for this batch.'; | |
42 | ALTER TABLE `civicrm_batch` ADD `item_count` int(10) unsigned NOT NULL COMMENT 'Number of items in a batch.'; | |
43 | ||
44 | ALTER TABLE `civicrm_batch` ADD CONSTRAINT `FK_civicrm_batch_saved_search_id` FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search` (`id`) ON DELETE SET NULL; | |
1393e47e | 45 | |
6a488035 TO |
46 | --batch type and batch status option groups |
47 | INSERT INTO | |
48 | `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`) | |
49 | VALUES | |
50 | ('batch_type' , {localize}'Batch Type'{/localize} , 1, 1), | |
51 | ('batch_status' , {localize}'Batch Status'{/localize} , 1, 1); | |
52 | ||
53 | SELECT @option_group_id_batch_type := max(id) from civicrm_option_group where name = 'batch_type'; | |
54 | SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status'; | |
55 | ||
56 | INSERT INTO | |
57 | `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`) | |
58 | VALUES | |
59 | (@option_group_id_batch_type, {localize}'Contribution'{/localize}, 1, 'Contribution', NULL, 0, 0, 1), | |
60 | (@option_group_id_batch_type, {localize}'Membership'{/localize}, 2, 'Membership', NULL, 0, 0, 2), | |
61 | (@option_group_id_batch_status, {localize}'Open'{/localize}, 1, 'Open', NULL, 0, 0, 1), | |
62 | (@option_group_id_batch_status, {localize}'Closed'{/localize}, 2, 'Closed', NULL, 0, 0, 2); | |
63 | ||
64 | --default profile for contribution and membership batch entry | |
65 | INSERT INTO civicrm_uf_group | |
66 | ( name, group_type, {localize field='title'}title{/localize}, is_cms_user, is_reserved) | |
67 | VALUES | |
68 | ( 'contribution_batch_entry', 'Contribution', {localize}'Contribution Batch Entry'{/localize} , 0, 1), | |
69 | ( 'membership_batch_entry', 'Membership', {localize}'Membership Batch Entry'{/localize} , 0, 1); | |
70 | ||
71 | SELECT @uf_group_contribution_batch_entry := max(id) FROM civicrm_uf_group WHERE name = 'contribution_batch_entry'; | |
72 | SELECT @uf_group_membership_batch_entry := max(id) FROM civicrm_uf_group WHERE name = 'membership_batch_entry'; | |
73 | ||
74 | INSERT INTO civicrm_uf_join | |
75 | (is_active, module, entity_table, entity_id, weight, uf_group_id) | |
76 | VALUES | |
77 | (1, 'Profile', NULL, NULL, 9, @uf_group_contribution_batch_entry), | |
78 | (1, 'Profile', NULL, NULL, 9, @uf_group_membership_batch_entry); | |
79 | ||
80 | INSERT INTO civicrm_uf_field | |
81 | ( 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 ) | |
82 | VALUES | |
83 | ( @uf_group_contribution_batch_entry, 'contribution_type', 1, 1, 1, 'User and User Admin Only', 0, 0, NULL, {localize}'Type'{/localize}, 'Contribution'), | |
84 | ( @uf_group_contribution_batch_entry, 'total_amount', 1, 1, 2, 'User and User Admin Only', 0, 0, NULL, {localize}'Amount'{/localize}, 'Contribution' ), | |
85 | ( @uf_group_contribution_batch_entry, 'contribution_status_id', 1, 1, 3, 'User and User Admin Only', 0, 0, NULL, {localize}'Status'{/localize}, 'Contribution' ), | |
86 | ( @uf_group_contribution_batch_entry, 'receive_date', 1, 1, 4, 'User and User Admin Only', 0, 0, NULL, {localize}'Received'{/localize}, 'Contribution'), | |
87 | ( @uf_group_contribution_batch_entry, 'contribution_source', 0, 0, 5, 'User and User Admin Only', 0, 0, NULL, {localize}'Source'{/localize}, 'Contribution' ), | |
88 | ( @uf_group_contribution_batch_entry, 'payment_instrument', 0, 0, 6, 'User and User Admin Only', 0, 0, NULL, {localize}'Payment Instrument'{/localize}, 'Contribution' ), | |
89 | ( @uf_group_contribution_batch_entry, 'check_number', 0, 0, 7, 'User and User Admin Only', 0, 0, NULL, {localize}'Check Number'{/localize}, 'Contribution' ), | |
90 | ( @uf_group_contribution_batch_entry, 'send_receipt', 0, 0, 8, 'User and User Admin Only', 0, 0, NULL, {localize}'Send Receipt'{/localize}, 'Contribution' ), | |
91 | ( @uf_group_contribution_batch_entry, 'invoice_id', 0, 0, 9, 'User and User Admin Only', 0, 0, NULL, {localize}'Invoice ID'{/localize}, 'Contribution' ), | |
92 | ( @uf_group_membership_batch_entry, 'membership_type', 1, 1, 1, 'User and User Admin Only', 0, 0, NULL, {localize}'Type'{/localize}, 'Membership' ), | |
93 | ( @uf_group_membership_batch_entry, 'join_date', 1, 1, 2, 'User and User Admin Only', 0, 0, NULL, {localize}'Member Since'{/localize}, 'Membership' ), | |
94 | ( @uf_group_membership_batch_entry, 'membership_start_date', 0, 1, 3, 'User and User Admin Only', 0, 0, NULL, {localize}'Start Date'{/localize}, 'Membership' ), | |
95 | ( @uf_group_membership_batch_entry, 'membership_end_date', 0, 1, 4, 'User and User Admin Only', 0, 0, NULL, {localize}'End Date'{/localize}, 'Membership' ), | |
96 | ( @uf_group_membership_batch_entry, 'membership_source', 0, 0, 5, 'User and User Admin Only', 0, 0, NULL, {localize}'Source'{/localize}, 'Membership' ), | |
97 | ( @uf_group_membership_batch_entry, 'send_receipt', 0, 0, 6, 'User and User Admin Only', 0, 0, NULL, {localize}'Send Receipt'{/localize}, 'Membership' ), | |
98 | ( @uf_group_membership_batch_entry, 'contribution_type', 1, 1, 7, 'User and User Admin Only', 0, 0, NULL, {localize}'Contribution Type'{/localize}, 'Membership' ), | |
99 | ( @uf_group_membership_batch_entry, 'total_amount', 1, 1, 8, 'User and User Admin Only', 0, 0, NULL, {localize}'Amount'{/localize}, 'Membership' ), | |
100 | ( @uf_group_membership_batch_entry, 'receive_date', 1, 1, 9, 'User and User Admin Only', 0, 0, NULL, {localize}'Received'{/localize}, 'Membership' ), | |
101 | ( @uf_group_membership_batch_entry, 'payment_instrument', 0, 0, 10, 'User and User Admin Only', 0, 0, NULL, {localize}'Payment Instrument'{/localize}, 'Membership' ), | |
102 | ( @uf_group_membership_batch_entry, 'contribution_status_id', 1, 1, 11, 'User and User Admin Only', 0, 0, NULL, {localize}'Payment Status'{/localize}, 'Membership' ); | |
103 | ||
104 | --navigation menu entries | |
c55ee104 | 105 | SELECT @navContributionsID := MAX(id) FROM civicrm_navigation where name = 'Contributions' AND domain_id = {$domainID}; |
106 | SELECT @navMembershipsID := MAX(id) FROM civicrm_navigation where name = 'Memberships' AND domain_id = {$domainID}; | |
1393e47e | 107 | |
6a488035 TO |
108 | INSERT INTO civicrm_navigation |
109 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
1393e47e | 110 | VALUES |
6a488035 TO |
111 | ( {$domainID}, 'civicrm/batch&reset=1', '{ts escape="sql" skip="true"}Batches{/ts}', 'Batches', 'access CiviContribute,access CiviMember', '', @navContributionsID, '1', NULL, 4 ), |
112 | ( {$domainID}, 'civicrm/batch&reset=1', '{ts escape="sql" skip="true"}Batches{/ts}', 'Batches', 'access CiviMember,acess CiviContribute', '', @navMembershipsID, '1', NULL, 4 ); | |
113 | ||
114 | -- CRM-9686 | |
115 | INSERT INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES(1097, "LP", "La Paz"); | |
116 | ||
117 | -- CRM-9905 | |
118 | ALTER TABLE civicrm_contribution_page CHANGE COLUMN is_email_receipt is_email_receipt TINYINT(4) DEFAULT 0; | |
119 | ||
120 | -- CRM-9850 | |
121 | ALTER TABLE `civicrm_contribution_page` CHANGE `payment_processor_id` `payment_processor` VARCHAR( 128 ) NULL DEFAULT NULL COMMENT 'Payment Processor for this contribution Page '; | |
122 | ||
123 | ALTER TABLE `civicrm_event` CHANGE `payment_processor_id` `payment_processor` VARCHAR( 128 ) NULL DEFAULT NULL COMMENT 'Payment Processor for this event '; | |
124 | ||
125 | -- CRM-9783 | |
126 | CREATE TABLE `civicrm_sms_provider` ( | |
127 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'SMS Provider ID', | |
128 | `name` varchar(64) DEFAULT NULL COMMENT 'Provider internal name points to option_value of option_group sms_provider_name', | |
129 | `title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Provider name visible to user', | |
130 | `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, | |
131 | `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, | |
132 | `api_type` int(10) unsigned NOT NULL COMMENT 'points to value in civicrm_option_value for group sms_api_type', | |
133 | `api_url` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | |
134 | `api_params` text COLLATE utf8_unicode_ci COMMENT 'the api params in xml, http or smtp format', | |
135 | `is_default` tinyint(4) DEFAULT '0', | |
136 | `is_active` tinyint(4) DEFAULT '0', | |
137 | PRIMARY KEY (`id`) | |
138 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; | |
139 | ||
140 | ALTER TABLE `civicrm_mailing` ADD `sms_provider_id` int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id '; | |
141 | ALTER TABLE `civicrm_mailing` ADD CONSTRAINT `FK_civicrm_mailing_sms_provider_id` FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider` (`id`) ON DELETE SET NULL; | |
142 | ||
143 | INSERT INTO | |
144 | `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`) | |
145 | VALUES | |
146 | ('sms_provider_name', {localize}'Sms provider Internal Name'{/localize} , 1, 1); | |
147 | SELECT @option_group_id_sms_provider_name := max(id) from civicrm_option_group where name = 'sms_provider_name'; | |
148 | ||
149 | INSERT INTO civicrm_option_value | |
150 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, component_id) | |
151 | VALUES | |
152 | (@option_group_id_sms_provider_name, {localize}'Clickatell'{/localize}, 'Clickatell', 'Clickatell', 1, 0, NULL, NULL); | |
153 | ||
154 | INSERT INTO | |
155 | `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`) | |
156 | VALUES | |
157 | ( 'sms_api_type', {localize}'{ts escape="sql"}Api Type{/ts}'{/localize} , 1, 1 ); | |
158 | SELECT @option_group_id_sms_api_type := max(id) from civicrm_option_group where name = 'sms_api_type'; | |
159 | ||
160 | INSERT INTO civicrm_option_value | |
161 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, is_reserved, component_id) | |
162 | VALUES | |
163 | (@option_group_id_sms_api_type, {localize}'http'{/localize}, 1, 'http', 1, NULL, 0, 1, NULL), | |
164 | (@option_group_id_sms_api_type, {localize}'xml'{/localize}, 2, 'xml', 2, NULL, 0, 1, NULL), | |
165 | (@option_group_id_sms_api_type, {localize}'smtp'{/localize}, 3, 'smtp', 3, NULL, 0, 1, NULL); | |
166 | ||
167 | -- CRM-9784 | |
c55ee104 | 168 | SELECT @adminSystemSettingsID := MAX(id) FROM civicrm_navigation where name = 'System Settings' AND domain_id = {$domainID}; |
6a488035 TO |
169 | |
170 | INSERT INTO civicrm_navigation | |
171 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
172 | VALUES | |
173 | ( {$domainID}, 'civicrm/admin/sms/provider?reset=1', '{ts escape="sql" skip="true"}SMS Providers{/ts}', 'SMS Providers', 'administer CiviCRM', '', @adminSystemSettingsID, '1', NULL, 16 ); | |
174 | ||
175 | -- CRM-9799 | |
176 | ||
c55ee104 | 177 | SELECT @mailingsID := MAX(id) FROM civicrm_navigation where name = 'Mailings' AND domain_id = {$domainID}; |
6a488035 TO |
178 | |
179 | INSERT INTO civicrm_navigation | |
180 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
181 | VALUES | |
182 | ( {$domainID}, 'civicrm/sms/send?reset=1', '{ts escape="sql" skip="true"}New SMS{/ts}', 'New SMS', 'administer CiviCRM', NULL, @mailingsID, '1', 1, 8 ); | |
183 | ||
c55ee104 | 184 | SELECT @fromEmailAddressesID := MAX(id) FROM civicrm_navigation where name = 'From Email Addresses' AND domain_id = {$domainID}; |
6a488035 TO |
185 | |
186 | UPDATE civicrm_navigation SET has_separator = 1 WHERE parent_id = @mailingsID AND name = 'From Email Addresses'; | |
187 | ||
188 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; | |
189 | SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
190 | ||
191 | INSERT INTO | |
192 | `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`) | |
193 | VALUES | |
194 | (@option_group_id_act, {localize}'BULK SMS'{/localize}, @max_wt, 'BULK SMS', NULL, 1, NULL, @max_wt, {localize}'BULK SMS'{/localize}, 0, 1, 1, NULL, NULL); | |
195 | ||
196 | ALTER TABLE `civicrm_mailing_recipients` ADD `phone_id` int(10) unsigned DEFAULT NULL; | |
197 | ||
198 | ALTER TABLE `civicrm_mailing_recipients` ADD CONSTRAINT `FK_civicrm_mailing_recipients_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE CASCADE; | |
199 | ||
200 | ALTER TABLE `civicrm_mailing_event_queue` ADD `phone_id` int(10) unsigned DEFAULT NULL; | |
201 | ||
202 | ALTER TABLE `civicrm_mailing_event_queue` ADD CONSTRAINT `FK_civicrm_mailing_event_queue_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE CASCADE; | |
203 | ||
204 | ALTER TABLE `civicrm_mailing_event_queue` CHANGE `email_id` `email_id` int(10) unsigned DEFAULT NULL; | |
205 | ALTER TABLE `civicrm_mailing_recipients` CHANGE `email_id` `email_id` int(10) unsigned DEFAULT NULL; | |
206 | ||
207 | -- CRM-9982 | |
208 | ALTER TABLE `civicrm_contribution_page` ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1'; | |
209 | ||
210 | -- CRM-9980 | |
211 | {if $multilingual} | |
212 | {foreach from=$locales item=locale} | |
213 | {if !$loc} | |
214 | {assign var=loc value="_$locale"} | |
215 | {/if} | |
216 | ALTER TABLE civicrm_group ADD title_{$locale} VARCHAR(64); | |
217 | ALTER TABLE civicrm_group ADD UNIQUE KEY `UI_title_{$locale}` (title_{$locale}); | |
1393e47e | 218 | |
219 | ALTER TABLE `civicrm_batch` CHANGE `label_{$locale}` `title_{$locale}` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Friendly Name.'; | |
6a488035 TO |
220 | UPDATE civicrm_group SET title_{$locale} = title; |
221 | ||
222 | ALTER TABLE civicrm_survey | |
223 | ADD COLUMN thankyou_title_{$locale} varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).', | |
1393e47e | 224 | ADD COLUMN thankyou_text_{$locale} text COMMENT 'text and html allowed. displayed above result on success page'; |
6a488035 TO |
225 | {/foreach} |
226 | ||
227 | ALTER TABLE civicrm_group DROP INDEX `UI_title`; | |
228 | ALTER TABLE civicrm_group DROP title; | |
229 | {else} | |
230 | ALTER TABLE `civicrm_batch` CHANGE `label` `title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Friendly Name.'; | |
231 | ALTER TABLE civicrm_survey | |
232 | ADD COLUMN thankyou_title varchar(255) COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).', | |
233 | ADD COLUMN thankyou_text text COMMENT 'text and html allowed. displayed above result on success page'; | |
234 | {/if} | |
235 | ||
236 | -- CRM-9780 | |
237 | SELECT @country_id := max(id) from civicrm_country where iso_code = "AN"; | |
238 | DELETE FROM civicrm_state_province WHERE country_id = @country_id; | |
239 | DELETE FROM civicrm_country WHERE id = @country_id; | |
240 | ||
241 | SELECT @region_id := max(id) from civicrm_worldregion where name = "America South, Central, North and Caribbean"; | |
242 | ||
243 | INSERT INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Curaçao", "CW", @region_id, 0); | |
244 | INSERT INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Sint Maarten (Dutch Part)", "SX", @region_id, 0); | |
245 | INSERT INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Bonaire, Saint Eustatius and Saba", "BQ", @region_id, 0); | |
246 | ||
26140cd5 DS |
247 | -- CRM-12428 |
248 | {if $multilingual} | |
249 | {foreach from=$locales item=locale} | |
1393e47e | 250 | ALTER TABLE `civicrm_price_field_value` CHANGE label_{$locale} label_{$locale} VARCHAR(255) NULL DEFAULT NULL; |
26140cd5 DS |
251 | {/foreach} |
252 | {else} | |
1393e47e | 253 | ALTER TABLE `civicrm_price_field_value` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL; |
26140cd5 DS |
254 | {/if} |
255 | ||
6a488035 TO |
256 | -- CRM-9714 create a default price set for contribution and membership |
257 | ALTER TABLE `civicrm_price_set` | |
258 | ADD `is_quick_config` TINYINT(4) NOT NULL DEFAULT '0' | |
259 | COMMENT 'Is set if edited on Contribution or Event Page rather than through Manage Price Sets' | |
260 | AFTER `contribution_type_id`, | |
261 | ADD `is_reserved` TINYINT(4) DEFAULT '0' | |
262 | COMMENT 'Is this a predefined system price set (i.e. it can not be deleted, edited)?'; | |
263 | ||
1393e47e | 264 | SELECT @contribution_type_id := max(id) FROM `civicrm_contribution_type` WHERE `name` = 'Member Dues'; |
6a488035 TO |
265 | INSERT INTO `civicrm_price_set` ( `name`, {localize field='title'}`title`{/localize}, `is_active`, `extends`, `is_quick_config`, `is_reserved`, `contribution_type_id`) |
266 | VALUES ( 'default_contribution_amount', {localize}'Contribution Amount'{/localize}, '1', '2', '1', '1', null), | |
267 | ( 'default_membership_type_amount', {localize}'Membership Amount'{/localize}, '1', '3', '1', '1', @contribution_type_id); | |
268 | ||
269 | SELECT @setID := max(id) FROM civicrm_price_set WHERE name = 'default_contribution_amount' AND extends = 2 AND is_quick_config = 1 ; | |
270 | ||
271 | INSERT INTO `civicrm_price_field` (`price_set_id`, `name`, {localize field='label'}`label`{/localize}, `html_type`,`weight`, `is_display_amounts`, `options_per_line`, `is_active`, `is_required`,`visibility_id` ) | |
272 | VALUES ( @setID, 'contribution_amount', {localize}'Contribution Amount'{/localize}, 'Text', '1', '1', '1', '1', '1', '1' ); | |
273 | ||
274 | SELECT @fieldID := max(id) FROM civicrm_price_field WHERE name = 'contribution_amount' AND price_set_id = @setID; | |
275 | ||
276 | INSERT INTO `civicrm_price_field_value` ( `price_field_id`, `name`, {localize field='label'}`label`{/localize}, `amount`, `weight`, `is_default`, `is_active`) | |
277 | VALUES ( @fieldID, 'contribution_amount', {localize}'Contribution Amount'{/localize}, '1', '1', '0', '1'); | |
278 | ALTER TABLE `civicrm_custom_group` CHANGE `extends_entity_column_value` `extends_entity_column_value` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'linking custom group for dynamic object.'; | |
279 | ||
280 | -- CRM-9714 create price fields for all membershiptype | |
281 | SELECT @setID := max(id) FROM civicrm_price_set WHERE name = 'default_membership_type_amount' AND extends = 3 AND is_quick_config = 1 ; | |
282 | ||
283 | INSERT INTO civicrm_price_field ( price_set_id, name, {localize field='label'}label{/localize}, html_type, is_display_amounts, is_required ) | |
284 | SELECT @setID as price_set_id, cmt.member_of_contact_id as name, {localize}'Membership Amount'{/localize}, 'Radio' as html_type, 0 as is_display_amounts, 0 as is_required | |
285 | FROM `civicrm_membership_type` cmt | |
286 | GROUP BY cmt.member_of_contact_id; | |
287 | ||
288 | INSERT INTO civicrm_price_field_value ( price_field_id, name, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, amount, membership_type_id) | |
289 | SELECT | |
290 | cpf.id, cmt.name{$loc} as label1, {localize field='name'}cmt.name as label2{/localize},{localize field='description'}cmt.description{/localize}, cmt.minimum_fee, cmt.id | |
291 | FROM `civicrm_membership_type` cmt | |
292 | LEFT JOIN civicrm_price_field cpf ON cmt.member_of_contact_id = BINARY cpf.name; | |
293 | ||
294 | -- CRM-9714 | |
295 | SELECT @fieldID := cpf.id, @fieldValueID := cpfv.id FROM civicrm_price_set cps | |
296 | LEFT JOIN civicrm_price_field cpf ON cps.id = cpf.price_set_id | |
297 | LEFT JOIN civicrm_price_field_value cpfv ON cpf.id = cpfv.price_field_id | |
298 | WHERE cps.name = 'default_contribution_amount'; | |
299 | ||
300 | INSERT INTO civicrm_line_item ( entity_table, entity_id, price_field_id, label, qty, unit_price, line_total, participant_count, price_field_value_id ) | |
301 | SELECT 'civicrm_contribution', cc.id, @fieldID, 'Contribution Amount', 1, total_amount, total_amount , 0, @fieldValueID | |
302 | FROM `civicrm_contribution` cc | |
303 | LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id and cli.entity_table = 'civicrm_contribution' | |
304 | LEFT JOIN civicrm_membership_payment cmp ON cc.id = cmp.contribution_id | |
305 | LEFT JOIN civicrm_participant_payment cpp ON cc.id = cpp.contribution_id | |
306 | WHERE cli.entity_id IS NULL AND cc.contribution_page_id IS NULL AND cmp.contribution_id IS NULL AND cpp.contribution_id IS NULL | |
307 | GROUP BY cc.id; | |
308 | ||
309 | -- CRM-10071 contribution membership detail report template | |
310 | SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template'; | |
311 | SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report; | |
312 | SELECT @memberCompId := max(id) FROM civicrm_component where name = 'CiviMember'; | |
313 | INSERT INTO civicrm_option_value | |
314 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES | |
315 | (@option_group_id_report, {localize}'Contribution and Membership Details'{/localize}, 'member/contributionDetail', 'CRM_Report_Form_Member_ContributionDetail', @weight := @weight + 1, {localize}'Contribution details for any type of contribution, plus associated membership information for contributions which are in payment for memberships.'{/localize}, 1, @memberCompId); | |
316 | ||
317 | INSERT INTO `civicrm_report_instance` | |
318 | ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`) | |
319 | VALUES | |
320 | ( {$domainID}, 'Contribution and Membership Details', 'member/contributionDetail', 'Contribution details for any type of contribution, plus associated membership information for contributions which are in payment for memberships.', 'access CiviMember', '{literal}a:67:{s:6:"fields";a:12:{s:9:"sort_name";s:1:"1";s:5:"email";s:1:"1";s:5:"phone";s:1:"1";s:20:"contribution_type_id";s:1:"1";s:12:"receive_date";s:1:"1";s:12:"total_amount";s:1:"1";s:18:"membership_type_id";s:1:"1";s:21:"membership_start_date";s:1:"1";s:19:"membership_end_date";s:1:"1";s:9:"join_date";s:1:"1";s:22:"membership_status_name";s:1:"1";s:10:"country_id";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:6:"id_min";s:0:"";s:6:"id_max";s:0:"";s:5:"id_op";s:3:"lte";s:8:"id_value";s:0:"";s:21:"receive_date_relative";s:1:"0";s:17:"receive_date_from";s:0:"";s:15:"receive_date_to";s:0:"";s:23:"contribution_type_id_op";s:2:"in";s:26:"contribution_type_id_value";a:0:{}s:24:"payment_instrument_id_op";s:2:"in";s:27:"payment_instrument_id_value";a:0:{}s:25:"contribution_status_id_op";s:2:"in";s:28:"contribution_status_id_value";a:0:{}s:16:"total_amount_min";s:0:"";s:16:"total_amount_max";s:0:"";s:15:"total_amount_op";s:3:"lte";s:18:"total_amount_value";s:0:"";s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:13:"ordinality_op";s:2:"in";s:16:"ordinality_value";a:0:{}s:18:"join_date_relative";s:1:"0";s:14:"join_date_from";s:0:"";s:12:"join_date_to";s:0:"";s:30:"membership_start_date_relative";s:1:"0";s:26:"membership_start_date_from";s:0:"";s:24:"membership_start_date_to";s:0:"";s:28:"membership_end_date_relative";s:1:"0";s:24:"membership_end_date_from";s:0:"";s:22:"membership_end_date_to";s:0:"";s:23:"owner_membership_id_min";s:0:"";s:23:"owner_membership_id_max";s:0:"";s:22:"owner_membership_id_op";s:3:"lte";s:25:"owner_membership_id_value";s:0:"";s:6:"tid_op";s:2:"in";s:9:"tid_value";a:0:{}s:6:"sid_op";s:2:"in";s:9:"sid_value";a:0:{}s:17:"street_number_min";s:0:"";s:17:"street_number_max";s:0:"";s:16:"street_number_op";s:3:"lte";s:19:"street_number_value";s:0:"";s:14:"street_name_op";s:3:"has";s:17:"street_name_value";s:0:"";s:15:"postal_code_min";s:0:"";s:15:"postal_code_max";s:0:"";s:14:"postal_code_op";s:3:"lte";s:17:"postal_code_value";s:0:"";s:7:"city_op";s:3:"has";s:10:"city_value";s:0:"";s:12:"county_id_op";s:2:"in";s:15:"county_id_value";a:0:{}s:20:"state_province_id_op";s:2:"in";s:23:"state_province_id_value";a:0:{}s:13:"country_id_op";s:2:"in";s:16:"country_id_value";a:0:{}s:8:"tagid_op";s:2:"in";s:11:"tagid_value";a:0:{}s:11:"description";s:35:"Contribution and Membership Details";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:17:"access CiviMember";s:9:"domain_id";i:1;}{s:6:"fields";a:12:{s:9:"sort_name";s:1:"1";s:5:"email";s:1:"1";s:5:"phone";s:1:"1";s:20:"contribution_type_id";s:1:"1";s:12:"receive_date";s:1:"1";s:12:"total_amount";s:1:"1";s:18:"membership_type_id";s:1:"1";s:21:"membership_start_date";s:1:"1";s:19:"membership_end_date";s:1:"1";s:9:"join_date";s:1:"1";s:22:"membership_status_name";s:1:"1";s:10:"country_id";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:6:"id_min";s:0:"";s:6:"id_max";s:0:"";s:5:"id_op";s:3:"lte";s:8:"id_value";s:0:"";s:21:"receive_date_relative";s:1:"0";s:17:"receive_date_from";s:0:"";s:15:"receive_date_to";s:0:"";s:23:"contribution_type_id_op";s:2:"in";s:26:"contribution_type_id_value";a:0:{}s:24:"payment_instrument_id_op";s:2:"in";s:27:"payment_instrument_id_value";a:0:{}s:25:"contribution_status_id_op";s:2:"in";s:28:"contribution_status_id_value";a:0:{}s:16:"total_amount_min";s:0:"";s:16:"total_amount_max";s:0:"";s:15:"total_amount_op";s:3:"lte";s:18:"total_amount_value";s:0:"";s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:13:"ordinality_op";s:2:"in";s:16:"ordinality_value";a:0:{}s:18:"join_date_relative";s:1:"0";s:14:"join_date_from";s:0:"";s:12:"join_date_to";s:0:"";s:30:"membership_start_date_relative";s:1:"0";s:26:"membership_start_date_from";s:0:"";s:24:"membership_start_date_to";s:0:"";s:28:"membership_end_date_relative";s:1:"0";s:24:"membership_end_date_from";s:0:"";s:22:"membership_end_date_to";s:0:"";s:23:"owner_membership_id_min";s:0:"";s:23:"owner_membership_id_max";s:0:"";s:22:"owner_membership_id_op";s:3:"lte";s:25:"owner_membership_id_value";s:0:"";s:6:"tid_op";s:2:"in";s:9:"tid_value";a:0:{}s:6:"sid_op";s:2:"in";s:9:"sid_value";a:0:{}s:17:"street_number_min";s:0:"";s:17:"street_number_max";s:0:"";s:16:"street_number_op";s:3:"lte";s:19:"street_number_value";s:0:"";s:14:"street_name_op";s:3:"has";s:17:"street_name_value";s:0:"";s:15:"postal_code_min";s:0:"";s:15:"postal_code_max";s:0:"";s:14:"postal_code_op";s:3:"lte";s:17:"postal_code_value";s:0:"";s:7:"city_op";s:3:"has";s:10:"city_value";s:0:"";s:12:"county_id_op";s:2:"in";s:15:"county_id_value";a:0:{}s:20:"state_province_id_op";s:2:"in";s:23:"state_province_id_value";a:0:{}s:13:"country_id_op";s:2:"in";s:16:"country_id_value";a:0:{}s:8:"tagid_op";s:2:"in";s:11:"tagid_value";a:0:{}s:11:"description";s:35:"Contribution and Membership Details";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:1:"0";s:9:"domain_id";i:1;}{/literal}'); | |
321 | ||
c55ee104 | 322 | SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports' AND domain_id = {$domainID}; |
6a488035 TO |
323 | SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID; |
324 | ||
325 | SET @instanceID:=LAST_INSERT_ID(); | |
326 | INSERT INTO civicrm_navigation | |
327 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
328 | VALUES | |
329 | ( {$domainID}, CONCAT('civicrm/report/instance/', @instanceID,'&reset=1'), '{ts escape="sql" skip="true"}Contribution and Membership Details{/ts}', 'Contribution and Membership Details', 'access CiviMember', 'AND', @reportlastID, '1', NULL, @instanceID+2 ); | |
330 | UPDATE civicrm_report_instance SET navigation_id = LAST_INSERT_ID() WHERE id = @instanceID; | |
331 | ||
332 | -- CRM-9936 | |
333 | ALTER TABLE civicrm_group ADD is_reserved TINYINT( 4 ) NULL DEFAULT '0' ; | |
334 | ||
335 | -- CRM-9501 price set report templates | |
336 | SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template'; | |
337 | SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report; | |
338 | SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviContribute'; | |
339 | INSERT INTO civicrm_option_value | |
340 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES | |
341 | (@option_group_id_report, {localize}'Line Item Report'{/localize}, 'price/lineitem', 'CRM_Report_Form_Price_Lineitem', @weight := @weight + 1, {localize}'Price set report by line item.'{/localize}, 1, @CompId); | |
342 | SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report; | |
343 | INSERT INTO civicrm_option_value | |
344 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES | |
345 | (@option_group_id_report, {localize}'Contribution Report with Line Item information'{/localize}, 'price/contributionbased', 'CRM_Report_Form_Price_Contributionbased', @weight := @weight + 1, {localize}'Contribution Line Item Report.'{/localize}, 1, @CompId); | |
346 | SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report; | |
347 | INSERT INTO civicrm_option_value | |
348 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES | |
349 | (@option_group_id_report, {localize}'Participant Report with Line Item information'{/localize}, 'price/lineitemparticipant', 'CRM_Report_Form_Price_Lineitemparticipant', @weight := @weight + 1, {localize}'Participant Line Item Report.'{/localize}, 1, @CompId); | |
350 | ||
351 | -- CRM-10148 | |
352 | ALTER TABLE civicrm_report_instance ADD is_reserved TINYINT( 4 ) NULL DEFAULT '0' ; | |
353 | ||
354 | -- CRM-9438 | |
355 | SELECT @option_group_id_act := MAX(id) FROM civicrm_option_group WHERE name = 'activity_type'; | |
356 | SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act; | |
357 | SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
358 | SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviMember'; | |
359 | INSERT INTO civicrm_option_value | |
360 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, is_reserved, component_id) | |
361 | VALUES | |
362 | (@option_group_id_act, {localize field='label'}'Change Membership Status'{/localize}, (SELECT @max_val := @max_val+1), 'Change Membership Status', (SELECT @max_wt := @max_wt+1), {localize field='description'}'Change Membership Status.'{/localize}, 1, 1, @CompId), | |
363 | (@option_group_id_act, {localize field='label'}'Change Membership Type'{/localize}, (SELECT @max_val := @max_val+1), 'Change Membership Type', (SELECT @max_wt := @max_wt+1), {localize field='description'}'Change Membership Type.'{/localize}, 1, 1, @CompId); | |
364 | ||
365 | -- CRM-10084 | |
1393e47e | 366 | INSERT INTO |
367 | `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`) | |
6a488035 TO |
368 | VALUES |
369 | (@option_group_id_act, {localize}'Cancel Recurring Contribution'{/localize}, (SELECT @max_val := @max_val+1), 'Cancel Recurring Contribution', NULL,1, 0, (SELECT @max_wt := @max_wt+1), {localize}''{/localize}, 0, 1, 1, NULL, NULL); | |
370 | ||
371 | -- CRM-10090 | |
1393e47e | 372 | INSERT INTO |
6a488035 TO |
373 | `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`) |
374 | VALUES | |
375 | (@option_group_id_act, {localize}'Update Recurring Contribution Billing Details'{/localize}, (SELECT @max_val := @max_val+1), 'Update Recurring Contribution Billing Details', NULL,1, 0, (SELECT @max_wt := @max_wt+1), {localize}''{/localize}, 0, 1, 1, NULL, NULL), | |
376 | (@option_group_id_act, {localize}'Update Recurring Contribution'{/localize}, (SELECT @max_val := @max_val+1), 'Update Recurring Contribution', NULL,1, 0, (SELECT @max_wt := @max_wt+1), {localize}''{/localize}, 0, 1, 1, NULL, NULL); | |
377 | ||
1393e47e | 378 | -- CRM-10117 |
6a488035 TO |
379 | ALTER TABLE `civicrm_price_field_value` CHANGE `is_active` `is_active` TINYINT( 4 ) NULL DEFAULT '1' COMMENT 'Is this price field value active'; |
380 | ||
381 | ||
382 | -- CRM-8359 | |
383 | INSERT INTO | |
384 | `civicrm_action_mapping` (`entity`, `entity_value`, `entity_value_label`, `entity_status`, `entity_status_label`, `entity_date_start`, `entity_date_end`, `entity_recipient`) VALUES | |
385 | ('civicrm_membership', 'civicrm_membership_type', 'Membership Type', 'auto_renew_options', 'Auto Renew Options', 'membership_join_date', 'membership_end_date', NULL); | |
386 | ||
387 | INSERT INTO | |
388 | `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`) | |
389 | VALUES | |
390 | ('auto_renew_options', {localize}'Auto Renew Options'{/localize}, 1, 1); | |
391 | ||
392 | SELECT @option_group_id_aro := max(id) from civicrm_option_group where name = 'auto_renew_options'; | |
393 | ||
394 | INSERT INTO | |
395 | `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`) | |
396 | VALUES | |
397 | (@option_group_id_aro, {localize}'Renewal Reminder (non-auto-renew memberships only)'{/localize}, 1, 'Renewal Reminder (non-auto-renew memberships only)', NULL, 0, 0, 1), | |
398 | (@option_group_id_aro, {localize}'Auto-renew Memberships Only'{/localize}, 2, 'Auto-renew Memberships Only', NULL, 0, 0, 2), | |
399 | (@option_group_id_aro, {localize}'Reminder for Both'{/localize}, 3, 'Reminder for Both', NULL, 0, 0, 3); | |
400 | ||
401 | -- CRM-10335, truncate cache to begin to speed the alter table | |
402 | TRUNCATE civicrm_cache; | |
403 | ALTER TABLE civicrm_cache | |
404 | DROP INDEX UI_group_path, | |
405 | ADD UNIQUE INDEX `UI_group_path_date` (`group_name`, `path`, `created_date`); | |
406 | ||
407 | -- CRM-10337 | |
408 | ALTER TABLE civicrm_survey | |
409 | ADD COLUMN bypass_confirm tinyint(4) DEFAULT '0' COMMENT 'Used to store option group id.'; |