projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
remove tabs (whitespace only)
[civicrm-core.git] / CRM / Upgrade / Incremental / sql / 4.2.alpha1.mysql.tpl
CommitLineData
6a488035
TO		 1{include file='../CRM/Upgrade/4.2.alpha1.msg_template/civicrm_msg_template.tpl'}
2
3-- CRM-9542 mailing detail report template
4SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template';
5SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report;
6SELECT @mailCompId := MAX(id) FROM civicrm_component where name = 'CiviMail';
7INSERT 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
11INSERT INTO `civicrm_report_instance`
12 ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`)
13VALUES
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
16SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports';
17SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID;
18
19SET @instanceID:=LAST_INSERT_ID();
20INSERT INTO civicrm_navigation
21 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
22VALUES
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 );
24UPDATE civicrm_report_instance SET navigation_id = LAST_INSERT_ID() WHERE id = @instanceID;
25
26-- CRM-9600
27ALTER 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
30ALTER TABLE `civicrm_prevnext_cache` ADD COLUMN is_selected tinyint(4) DEFAULT '0';
31
32-- CRM-9834
33-- civicrm_batch table changes
34ALTER TABLE `civicrm_batch` ADD UNIQUE KEY `UI_name` ( name );
35
36
37ALTER TABLE `civicrm_batch` ADD `saved_search_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Saved Search ID';
38ALTER TABLE `civicrm_batch` ADD `status_id` int(10) unsigned NOT NULL COMMENT 'fk to Batch Status options in civicrm_option_values';
39ALTER TABLE `civicrm_batch` ADD `type_id` int(10) unsigned NOT NULL COMMENT 'fk to Batch Type options in civicrm_option_values';
40ALTER TABLE `civicrm_batch` ADD `mode_id` int(10) unsigned DEFAULT NULL COMMENT 'fk to Batch mode options in civicrm_option_values';
41ALTER TABLE `civicrm_batch` ADD `total` decimal(20,2) DEFAULT NULL COMMENT 'Total amount for this batch.';
42ALTER TABLE `civicrm_batch` ADD `item_count` int(10) unsigned NOT NULL COMMENT 'Number of items in a batch.';
43
44ALTER 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
47INSERT INTO
48 `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
49VALUES
50 ('batch_type' , {localize}'Batch Type'{/localize} , 1, 1),
51 ('batch_status' , {localize}'Batch Status'{/localize} , 1, 1);
52
53SELECT @option_group_id_batch_type := max(id) from civicrm_option_group where name = 'batch_type';
54SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status';
55
56INSERT INTO
57 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
58VALUES
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
65INSERT 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
71SELECT @uf_group_contribution_batch_entry := max(id) FROM civicrm_uf_group WHERE name = 'contribution_batch_entry';
72SELECT @uf_group_membership_batch_entry := max(id) FROM civicrm_uf_group WHERE name = 'membership_batch_entry';
73
74INSERT INTO civicrm_uf_join
75 (is_active, module, entity_table, entity_id, weight, uf_group_id)
76VALUES
77 (1, 'Profile', NULL, NULL, 9, @uf_group_contribution_batch_entry),
78 (1, 'Profile', NULL, NULL, 9, @uf_group_membership_batch_entry);
79
80INSERT 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 )
82VALUES
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
105SELECT @navContributionsID := MAX(id) FROM civicrm_navigation where name = 'Contributions';
106SELECT @navMembershipsID := MAX(id) FROM civicrm_navigation where name = 'Memberships';
1393e47e 107
6a488035
TO		 108INSERT INTO civicrm_navigation
109 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
1393e47e 110VALUES
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
115INSERT INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES(1097, "LP", "La Paz");
116
117-- CRM-9905
118ALTER 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
126CREATE 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
140ALTER TABLE `civicrm_mailing` ADD `sms_provider_id` int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id ';
141ALTER 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
143INSERT INTO
144 `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`)
145VALUES
146 ('sms_provider_name', {localize}'Sms provider Internal Name'{/localize} , 1, 1);
147SELECT @option_group_id_sms_provider_name := max(id) from civicrm_option_group where name = 'sms_provider_name';
148
149INSERT INTO civicrm_option_value
150 (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, component_id)
151VALUES
152 (@option_group_id_sms_provider_name, {localize}'Clickatell'{/localize}, 'Clickatell', 'Clickatell', 1, 0, NULL, NULL);
153
154INSERT INTO
155 `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`)
156VALUES
157 ( 'sms_api_type', {localize}'{ts escape="sql"}Api Type{/ts}'{/localize} , 1, 1 );
158SELECT @option_group_id_sms_api_type := max(id) from civicrm_option_group where name = 'sms_api_type';
159
160INSERT INTO civicrm_option_value
161 (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, is_reserved, component_id)
162VALUES
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
168SELECT @adminSystemSettingsID := MAX(id) FROM civicrm_navigation where name = 'System Settings';
169
170INSERT INTO civicrm_navigation
171 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
172VALUES
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
177SELECT @mailingsID := MAX(id) FROM civicrm_navigation where name = 'Mailings';
178
179INSERT INTO civicrm_navigation
180 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
181VALUES
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
184SELECT @fromEmailAddressesID := MAX(id) FROM civicrm_navigation where name = 'From Email Addresses';
185
186UPDATE civicrm_navigation SET has_separator = 1 WHERE parent_id = @mailingsID AND name = 'From Email Addresses';
187
188SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type';
189SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
190
191INSERT 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`)
193VALUES
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
196ALTER TABLE `civicrm_mailing_recipients` ADD `phone_id` int(10) unsigned DEFAULT NULL;
197
198ALTER TABLE `civicrm_mailing_recipients` ADD CONSTRAINT `FK_civicrm_mailing_recipients_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE CASCADE;
199
200ALTER TABLE `civicrm_mailing_event_queue` ADD `phone_id` int(10) unsigned DEFAULT NULL;
201
202ALTER 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
204ALTER TABLE `civicrm_mailing_event_queue` CHANGE `email_id` `email_id` int(10) unsigned DEFAULT NULL;
205ALTER TABLE `civicrm_mailing_recipients` CHANGE `email_id` `email_id` int(10) unsigned DEFAULT NULL;
206
207-- CRM-9982
208ALTER 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
237SELECT @country_id := max(id) from civicrm_country where iso_code = "AN";
238DELETE FROM civicrm_state_province WHERE country_id = @country_id;
239DELETE FROM civicrm_country WHERE id = @country_id;
240
241SELECT @region_id := max(id) from civicrm_worldregion where name = "America South, Central, North and Caribbean";
242
243INSERT INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Curaçao", "CW", @region_id, 0);
244INSERT INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Sint Maarten (Dutch Part)", "SX", @region_id, 0);
245INSERT 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
257ALTER TABLE `civicrm_price_set`
258ADD `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`,
261ADD `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 264SELECT @contribution_type_id := max(id) FROM `civicrm_contribution_type` WHERE `name` = 'Member Dues';
6a488035
TO		 265INSERT INTO `civicrm_price_set` ( `name`, {localize field='title'}`title`{/localize}, `is_active`, `extends`, `is_quick_config`, `is_reserved`, `contribution_type_id`)
266VALUES ( '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
269SELECT @setID := max(id) FROM civicrm_price_set WHERE name = 'default_contribution_amount' AND extends = 2 AND is_quick_config = 1 ;
270
271INSERT 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` )
272VALUES ( @setID, 'contribution_amount', {localize}'Contribution Amount'{/localize}, 'Text', '1', '1', '1', '1', '1', '1' );
273
274SELECT @fieldID := max(id) FROM civicrm_price_field WHERE name = 'contribution_amount' AND price_set_id = @setID;
275
276INSERT INTO `civicrm_price_field_value` ( `price_field_id`, `name`, {localize field='label'}`label`{/localize}, `amount`, `weight`, `is_default`, `is_active`)
277VALUES ( @fieldID, 'contribution_amount', {localize}'Contribution Amount'{/localize}, '1', '1', '0', '1');
278ALTER 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
281SELECT @setID := max(id) FROM civicrm_price_set WHERE name = 'default_membership_type_amount' AND extends = 3 AND is_quick_config = 1 ;
282
283INSERT INTO civicrm_price_field ( price_set_id, name, {localize field='label'}label{/localize}, html_type, is_display_amounts, is_required )
284SELECT @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
285FROM `civicrm_membership_type` cmt
286GROUP BY cmt.member_of_contact_id;
287
288INSERT INTO civicrm_price_field_value ( price_field_id, name, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, amount, membership_type_id)
289SELECT
290cpf.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
291FROM `civicrm_membership_type` cmt
292LEFT JOIN civicrm_price_field cpf ON cmt.member_of_contact_id = BINARY cpf.name;
293
294-- CRM-9714
295SELECT @fieldID := cpf.id, @fieldValueID := cpfv.id FROM civicrm_price_set cps
296LEFT JOIN civicrm_price_field cpf ON cps.id = cpf.price_set_id
297LEFT JOIN civicrm_price_field_value cpfv ON cpf.id = cpfv.price_field_id
298WHERE cps.name = 'default_contribution_amount';
299
300INSERT INTO civicrm_line_item ( entity_table, entity_id, price_field_id, label, qty, unit_price, line_total, participant_count, price_field_value_id )
301SELECT 'civicrm_contribution', cc.id, @fieldID, 'Contribution Amount', 1, total_amount, total_amount , 0, @fieldValueID
302FROM `civicrm_contribution` cc
303LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
304LEFT JOIN civicrm_membership_payment cmp ON cc.id = cmp.contribution_id
305LEFT JOIN civicrm_participant_payment cpp ON cc.id = cpp.contribution_id
306WHERE cli.entity_id IS NULL AND cc.contribution_page_id IS NULL AND cmp.contribution_id IS NULL AND cpp.contribution_id IS NULL
307GROUP BY cc.id;
308
309-- CRM-10071 contribution membership detail report template
310SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template';
311SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report;
312SELECT @memberCompId := max(id) FROM civicrm_component where name = 'CiviMember';
313INSERT 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
317INSERT INTO `civicrm_report_instance`
318 ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`)
319VALUES
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
322SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports';
323SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID;
324
325SET @instanceID:=LAST_INSERT_ID();
326INSERT INTO civicrm_navigation
327 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
328VALUES
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 );
330UPDATE civicrm_report_instance SET navigation_id = LAST_INSERT_ID() WHERE id = @instanceID;
331
332-- CRM-9936
333ALTER TABLE civicrm_group ADD is_reserved TINYINT( 4 ) NULL DEFAULT '0' ;
334
335-- CRM-9501 price set report templates
336SELECT @option_group_id_report := MAX(id) FROM civicrm_option_group WHERE name = 'report_template';
337SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report;
338SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviContribute';
339INSERT 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);
342SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report;
343INSERT 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);
346SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_report;
347INSERT 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
352ALTER TABLE civicrm_report_instance ADD is_reserved TINYINT( 4 ) NULL DEFAULT '0' ;
353
354-- CRM-9438
355SELECT @option_group_id_act := MAX(id) FROM civicrm_option_group WHERE name = 'activity_type';
356SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act;
357SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
358SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviMember';
359INSERT 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)
361VALUES
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 366INSERT 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		 368VALUES
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 372INSERT 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`)
374VALUES
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		 379ALTER 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
383INSERT 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
387INSERT INTO
388 `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
389VALUES
390 ('auto_renew_options', {localize}'Auto Renew Options'{/localize}, 1, 1);
391
392SELECT @option_group_id_aro := max(id) from civicrm_option_group where name = 'auto_renew_options';
393
394INSERT INTO
395 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
396VALUES
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
402TRUNCATE civicrm_cache;
403ALTER 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
408ALTER TABLE civicrm_survey
409 ADD COLUMN bypass_confirm tinyint(4) DEFAULT '0' COMMENT 'Used to store option group id.';