1 {include file='../CRM/Upgrade/4.3.alpha1.msg_template/civicrm_msg_template.tpl'}
4 ALTER TABLE `civicrm_premiums`
5 ADD COLUMN `premiums_nothankyou_position` int(10) unsigned DEFAULT '1';
7 -- CRM-11514 if contribution type name is null, assign it a name
8 UPDATE civicrm_contribution_type
9 SET name = CONCAT('Unknown_', id)
10 WHERE name IS NULL OR TRIM(name) = '';
13 ALTER TABLE civicrm_custom_field
14 ADD UNIQUE INDEX `UI_name_custom_group_id` (`name`, `custom_group_id`);
16 --CRM-10473 Added Missing Provinces of Ningxia Autonomous Region of China
17 INSERT INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES
18 (1045, 'YN', 'Yinchuan'),
19 (1045, 'SZ', 'Shizuishan'),
20 (1045, 'WZ', 'Wuzhong'),
21 (1045, 'GY', 'Guyuan'),
22 (1045, 'ZW', 'Zhongwei');
25 ALTER TABLE civicrm_contact
26 ADD COLUMN `created_date` timestamp NULL DEFAULT NULL
27 COMMENT 'When was the contact was created.';
28 ALTER TABLE civicrm_contact
29 ADD COLUMN `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
30 COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.';
33 DELETE FROM civicrm_job WHERE `api_action` = 'process_membership_reminder_date';
34 ALTER TABLE civicrm_membership DROP COLUMN reminder_date;
35 ALTER TABLE civicrm_membership_log DROP COLUMN renewal_reminder_date;
36 ALTER TABLE civicrm_membership_type
37 DROP COLUMN renewal_reminder_day,
38 DROP FOREIGN KEY FK_civicrm_membership_type_renewal_msg_id,
39 DROP INDEX FK_civicrm_membership_type_renewal_msg_id,
40 DROP COLUMN renewal_msg_id,
41 DROP COLUMN autorenewal_msg_id;
44 ALTER TABLE civicrm_msg_template
45 CHANGE msg_text msg_text LONGTEXT NULL COMMENT 'Text formatted message',
46 CHANGE msg_html msg_html LONGTEXT NULL COMMENT 'HTML formatted message';
49 ALTER TABLE civicrm_contribution_page ADD COLUMN is_recur_installments tinyint(4) DEFAULT '0';
50 UPDATE civicrm_contribution_page SET is_recur_installments='1';
53 SELECT @country_id := id from civicrm_country where name = 'Luxembourg' AND iso_code = 'LU';
54 INSERT IGNORE INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES
55 (@country_id, 'L', 'Luxembourg');
57 -- CRM-10899 and CRM-10999
59 {foreach from=$locales item=locale}
60 UPDATE civicrm_option_group SET title_{$locale} = '{ts escape="sql"}Currencies Enabled{/ts}' WHERE name = "currencies_enabled";
61 ALTER TABLE `civicrm_premiums`
62 ADD COLUMN premiums_nothankyou_label_{$locale} varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)';
65 UPDATE civicrm_option_group SET title = '{ts escape="sql"}Currencies Enabled{/ts}' WHERE name = "currencies_enabled";
69 ALTER TABLE civicrm_job DROP COLUMN api_prefix;
71 -- CRM-11068, CRM-10678, CRM-11759
72 ALTER TABLE civicrm_group
73 ADD refresh_date datetime default NULL COMMENT 'Date and time when we need to refresh the cache next.' AFTER `cache_date`,
74 ADD COLUMN `created_id` INT(10) unsigned DEFAULT NULL COMMENT 'FK to contact table, creator of the group.';
77 ALTER TABLE civicrm_group
78 ADD CONSTRAINT `FK_civicrm_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL;
80 INSERT INTO `civicrm_job`
81 ( domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active )
83 ( {$domainID}, 'Always' , NULL, '{ts escape="sql" skip="true"}Rebuild Smart Group Cache{/ts}', '{ts escape="sql" skip="true"}Rebuilds the smart group cache.{/ts}', 'job', 'group_rebuild', '{ts escape="sql" skip="true"}limit=Number optional-Limit the number of smart groups rebuild{/ts}', 0),
84 ( {$domainID}, 'Daily' , NULL, '{ts escape="sql" skip="true"}Validate Email Address from Mailings.{/ts}', '{ts escape="sql" skip="true"}Updates the reset_date on an email address to indicate that there was a valid delivery to this email address.{/ts}', 'mailing', 'update_email_resetdate', '{ts escape="sql" skip="true"}minDays, maxDays=Consider mailings that have completed between minDays and maxDays{/ts}', 0);
87 INSERT IGNORE INTO `civicrm_setting` (`group_name`, `name`, `value`, `domain_id`, `is_domain`) VALUES ('CiviCRM Preferences', 'activity_assignee_notification_ics', 's:1:"0";', {$domainID}, '1');
90 ALTER TABLE civicrm_dedupe_rule_group
91 ADD used enum('Unsupervised','Supervised','General') COLLATE utf8_unicode_ci NOT NULL COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)' AFTER threshold;
93 UPDATE civicrm_dedupe_rule_group
94 SET used = 'General' WHERE is_default = 0;
96 UPDATE civicrm_dedupe_rule_group
98 WHEN 'Fuzzy' THEN 'Supervised'
99 WHEN 'Strict' THEN 'Unsupervised'
101 WHERE is_default = 1;
103 UPDATE civicrm_dedupe_rule_group
104 SET name = CONCAT_WS('', `contact_type`, `used`)
105 WHERE is_default = 1 OR is_reserved = 1;
107 UPDATE civicrm_dedupe_rule_group
108 SET title = 'Name and Email'
109 WHERE contact_type IN ('Organization', 'Household') AND used IN ('Unsupervised', 'Supervised');
111 UPDATE civicrm_dedupe_rule_group
112 SET title = CASE used
113 WHEN 'Supervised' THEN 'Name and Email (reserved)'
114 WHEN 'Unsupervised' THEN 'Email (reserved)'
115 WHEN 'General' THEN 'Name and Address (reserved)'
117 WHERE contact_type = 'Individual' AND is_reserved = 1;
119 ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN level;
122 ALTER TABLE civicrm_uf_field
123 ADD `is_multi_summary` tinyint(4) DEFAULT '0' COMMENT 'Include in multi-record listing?';
126 -- note that country names are not translated in the DB
127 SELECT @region_id := max(id) from civicrm_worldregion where name = "Europe and Central Asia";
128 INSERT IGNORE INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Kosovo", "XK", @region_id, 0);
130 UPDATE civicrm_country SET name = 'Libya' WHERE name LIKE 'Libyan%';
131 UPDATE civicrm_country SET name = 'Congo, Republic of the' WHERE name = 'Congo';
133 -- CRM-10621 Add component report links to reports menu for upgrade
134 SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports' AND domain_id = {$domainID};
135 SELECT @max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID;
137 INSERT INTO civicrm_navigation
138 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
140 ( {$domainID}, 'civicrm/report/list&compid=99&reset=1', '{ts escape="sql" skip="true"}Contact Reports{/ts}', 'Contact Reports', 'administer CiviCRM', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1) );
141 INSERT INTO civicrm_navigation
142 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
144 ( {$domainID}, 'civicrm/report/list&compid=2&reset=1', '{ts escape="sql" skip="true"}Contribution Reports{/ts}', 'Contribution Reports', 'access CiviContribute', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1) );
145 INSERT INTO civicrm_navigation
146 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
148 ( {$domainID}, 'civicrm/report/list&compid=6&reset=1', '{ts escape="sql" skip="true"}Pledge Reports{/ts}', 'Pledge Reports', 'access CiviPledge', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1) );
149 INSERT INTO civicrm_navigation
150 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
152 ( {$domainID}, 'civicrm/report/list&compid=1&reset=1', '{ts escape="sql" skip="true"}Event Reports{/ts}', 'Event Reports', 'access CiviEvent', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1));
153 INSERT INTO civicrm_navigation
154 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
156 ( {$domainID}, 'civicrm/report/list&compid=4&reset=1', '{ts escape="sql" skip="true"}Mailing Reports{/ts}', 'Mailing Reports', 'access CiviMail', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1));
157 INSERT INTO civicrm_navigation
158 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
160 ( {$domainID}, 'civicrm/report/list&compid=3&reset=1', '{ts escape="sql" skip="true"}Membership Reports{/ts}', 'Membership Reports', 'access CiviMember', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1));
161 INSERT INTO civicrm_navigation
162 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
164 ( {$domainID}, 'civicrm/report/list&compid=9&reset=1', '{ts escape="sql" skip="true"}Campaign Reports{/ts}', 'Campaign Reports', 'interview campaign contacts,release campaign contacts,reserve campaign contacts,manage campaign,administer CiviCampaign,gotv campaign contacts', 'OR', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1));
165 INSERT INTO civicrm_navigation
166 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
168 ( {$domainID}, 'civicrm/report/list&compid=7&reset=1', '{ts escape="sql" skip="true"}Case Reports{/ts}', 'Case Reports', 'access my cases and activities,access all cases and activities,administer CiviCase', 'OR', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1) );
169 INSERT INTO civicrm_navigation
170 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
172 ( {$domainID}, 'civicrm/report/list&compid=5&reset=1', '{ts escape="sql" skip="true"}Grant Reports{/ts}', 'Grant Reports', 'access CiviGrant', '', @reportlastID, '1', 0, (SELECT @max_weight := @max_weight+1) );
174 -- CRM-11148 Multiple terms membership signup and renewal via price set
175 ALTER TABLE `civicrm_price_field_value` ADD COLUMN `membership_num_terms` INT(10) NULL DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `membership_type_id`;
178 SELECT @option_group_id_tuf := max(id) from civicrm_option_group where name = 'tag_used_for';
179 SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_tuf;
182 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
184 (@option_group_id_tuf, {localize}'Attachments'{/localize}, 'civicrm_file', 'Attachments', NULL, 0, 0, @weight = @weight + 1);
186 ALTER TABLE civicrm_extension MODIFY COLUMN type ENUM( 'payment', 'search', 'report', 'module','sms') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ;
189 SELECT @option_group_id_sms_provider_name := max(id) from civicrm_option_group where name = 'sms_provider_name';
190 DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_sms_provider_name AND name = 'Clickatell';
193 ALTER TABLE `civicrm_phone`
194 ADD `phone_numeric` varchar(32)
195 COMMENT 'Phone number stripped of all whitespace, letters, and punctuation.'
197 ADD INDEX phone_numeric_index(`phone_numeric`);
200 -- civiaccounts upgrade
202 -- ADD fields w.r.t 10.6 mwb
203 ALTER TABLE `civicrm_financial_account`
204 CHANGE `account_type_id` financial_account_type_id int(10) unsigned NOT NULL DEFAULT '3' COMMENT 'Version identifier of financial_type',
205 ADD `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Financial Type Description.',
206 ADD `parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent ID in account hierarchy',
207 ADD `is_header_account` tinyint(4) DEFAULT NULL COMMENT 'Is this a header account which does not allow transactions to be posted against it directly, but only to its sub-accounts?',
208 ADD `accounting_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
209 ADD `account_type_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Optional value for mapping account types to accounting system account categories (QuickBooks Account Type Codes for example).',
210 ADD `is_deductible` tinyint(4) DEFAULT '1' COMMENT 'Is this account tax-deductible?',
211 ADD `is_tax` tinyint(4) DEFAULT '0' COMMENT 'Is this account for taxes?',
212 ADD `tax_rate` decimal(9,8) DEFAULT '0.00' COMMENT 'The percentage of the total_amount that is due for this tax.',
213 ADD `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this a predefined system object?',
214 ADD `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this property active?',
215 ADD `is_default` tinyint(4) DEFAULT NULL COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?',
216 ADD CONSTRAINT `UI_name` UNIQUE INDEX (`name`),
217 ADD CONSTRAINT `FK_civicrm_financial_account_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(id);
220 -- Rename table civicrm_contribution_type to civicrm_financial_type
221 RENAME TABLE `civicrm_contribution_type` TO `civicrm_financial_type`;
223 ALTER TABLE `civicrm_financial_type`
224 CHANGE `name` `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Financial Type Name.',
225 ADD CONSTRAINT `UI_id` UNIQUE INDEX(id),
228 CREATE TABLE IF NOT EXISTS `civicrm_entity_financial_account` (
229 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
230 `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Links to an entity_table like civicrm_financial_type',
231 `entity_id` int(10) unsigned NOT NULL COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
232 `account_relationship` int(10) unsigned NOT NULL COMMENT 'FK to a new civicrm_option_value (account_relationship)',
233 `financial_account_id` int(10) unsigned NOT NULL COMMENT 'FK to the financial_account_id',
235 KEY `FK_civicrm_entity_financial_account_financial_account_id` (`financial_account_id`)
236 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
238 -- Constraints for table `civicrm_entity_financial_account`
239 ALTER TABLE `civicrm_entity_financial_account`
240 ADD CONSTRAINT `FK_civicrm_entity_financial_account_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);
242 -- CRM-9730 Table structure for table `civicrm_financial_item`
245 CREATE TABLE IF NOT EXISTS `civicrm_financial_item` (
246 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
247 `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the item was created',
248 `transaction_date` datetime NOT NULL COMMENT 'Date and time of the source transaction',
249 `contact_id` int(10) unsigned NOT NULL COMMENT 'FK to Contact ID of contact the item is from',
250 `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Human readable description of this item, to ease display without lookup of source item.',
251 `amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT 'Total amount of this item',
252 `currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Currency for the amount',
253 `financial_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_financial_account',
254 `status_id` int(10) unsigned DEFAULT NULL COMMENT 'Payment status: test, paid, part_paid, unpaid (if empty assume unpaid)',
255 `entity_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The table providing the source of this item such as civicrm_line_item',
256 `entity_id` int(10) unsigned DEFAULT NULL COMMENT 'The specific source item that is responsible for the creation of this financial_item',
258 UNIQUE KEY `UI_id` (`id`),
259 KEY `IX_created_date` (`created_date`),
260 KEY `IX_transaction_date` (`transaction_date`),
261 KEY `IX_entity` (`entity_table`,`entity_id`),
262 KEY `FK_civicrm_financial_item_contact_id` (`contact_id`),
263 KEY `FK_civicrm_financial_item_financial_account_id` (`financial_account_id`)
264 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
266 ALTER TABLE `civicrm_batch`
267 ADD `payment_instrument_id` int(10) unsigned DEFAULT NULL COMMENT 'fk to Payment Instrument options in civicrm_option_values',
268 ADD `exported_date` datetime DEFAULT NULL;
270 ALTER TABLE `civicrm_financial_item`
271 ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`),
272 ADD CONSTRAINT `FK_civicrm_financial_item_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);
274 ALTER TABLE `civicrm_entity_financial_trxn`
278 UPDATE civicrm_event SET contribution_type_id = NULL WHERE contribution_type_id = 0;
280 -- CRM-9189 and CRM-8425 change fk's to financial_account.id in our branch that will need to be changed to an fk to financial_type.id
282 ALTER TABLE `civicrm_pledge`
283 DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id,
284 DROP INDEX FK_civicrm_pledge_contribution_type_id;
286 ALTER TABLE `civicrm_pledge`
287 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
289 ALTER TABLE `civicrm_pledge`
290 ADD CONSTRAINT FK_civicrm_pledge_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
292 ALTER TABLE `civicrm_membership_type`
293 DROP FOREIGN KEY FK_civicrm_membership_type_contribution_type_id,
294 DROP INDEX FK_civicrm_membership_type_contribution_type_id;
296 ALTER TABLE `civicrm_membership_type`
297 CHANGE `contribution_type_id` `financial_type_id` int unsigned NOT NULL COMMENT 'If membership is paid by a contribution - what financial type should be used. FK to civicrm_financial_type.id';
299 ALTER TABLE `civicrm_membership_type`
300 ADD CONSTRAINT FK_civicrm_membership_type_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
302 ALTER TABLE `civicrm_price_set`
303 DROP FOREIGN KEY FK_civicrm_price_set_contribution_type_id,
304 DROP INDEX FK_civicrm_price_set_contribution_type_id;
306 ALTER TABLE `civicrm_price_set`
307 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'If membership is paid by a contribution - what financial type should be used. FK to civicrm_financial_type.id';
309 ALTER TABLE `civicrm_price_set`
310 ADD CONSTRAINT FK_civicrm_price_set_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
312 ALTER TABLE `civicrm_event`
313 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'Financial type assigned to paid event registrations for this event. Required if is_monetary is true.';
315 ALTER TABLE `civicrm_contribution`
316 DROP FOREIGN KEY FK_civicrm_contribution_contribution_type_id,
317 DROP INDEX FK_civicrm_contribution_contribution_type_id;
319 ALTER TABLE `civicrm_contribution`
320 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).';
322 ALTER TABLE `civicrm_contribution`
323 ADD CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
325 ALTER TABLE `civicrm_contribution_page`
326 DROP FOREIGN KEY FK_civicrm_contribution_page_contribution_type_id,
327 DROP INDEX FK_civicrm_contribution_page_contribution_type_id;
329 ALTER TABLE `civicrm_contribution_page`
330 CHANGE `contribution_type_id` `financial_type_id` int unsigned DEFAULT NULL COMMENT 'default financial type assigned to contributions submitted via this page, e.g. Contribution, Campaign Contribution',
331 ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
332 ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
335 {foreach from=$locales item=loc}
336 ALTER TABLE `civicrm_contribution_page`
337 ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
338 ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
341 ALTER TABLE `civicrm_contribution_page`
342 ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
343 ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
346 ALTER TABLE `civicrm_contribution_page`
347 ADD CONSTRAINT FK_civicrm_contribution_page_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
349 ALTER TABLE `civicrm_contribution_recur`
350 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
352 ALTER TABLE `civicrm_contribution_recur`
353 ADD CONSTRAINT FK_civicrm_contribution_recur_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
356 ALTER TABLE `civicrm_financial_trxn` CHANGE `to_account_id` `to_financial_account_id` int unsigned COMMENT 'FK to financial_financial_account table.',
357 CHANGE `from_account_id` `from_financial_account_id` int unsigned COMMENT 'FK to financial_account table.',
358 ADD `status_id` int(10) unsigned DEFAULT NULL,
359 CHANGE `trxn_id` trxn_id varchar(255) COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
360 CHANGE `trxn_date` trxn_date datetime DEFAULT NULL,
361 ADD `payment_instrument_id` int unsigned DEFAULT NULL COMMENT 'FK to payment_instrument option group values',
362 ADD `check_number` VARCHAR( 255 ) NULL DEFAULT NULL,
363 ADD INDEX `UI_ftrxn_check_number` (`check_number`),
364 ADD INDEX `UI_ftrxn_payment_instrument_id` (`payment_instrument_id`);
366 ALTER TABLE `civicrm_financial_trxn`
367 ADD CONSTRAINT FK_civicrm_financial_trxn_to_financial_account_id FOREIGN KEY (`to_financial_account_id`) REFERENCES civicrm_financial_account (id),
368 ADD CONSTRAINT FK_civicrm_financial_trxn_from_financial_account_id FOREIGN KEY (`from_financial_account_id`) REFERENCES civicrm_financial_account (id);
370 ALTER TABLE `civicrm_financial_trxn` ADD `payment_processor_id` int unsigned COMMENT 'Payment Processor for this contribution Page';
372 -- Fill in the payment_processor_id based on a lookup using the payment_processor field
373 UPDATE `civicrm_payment_processor` cppt, `civicrm_financial_trxn` cft
374 SET cft.`payment_processor_id` = cppt.`id`
375 WHERE cft.`payment_processor` = cppt.`payment_processor_type` and `is_test` = 0;
377 -- remove payment_processor field
378 ALTER TABLE `civicrm_financial_trxn` DROP `payment_processor`;
380 ALTER TABLE `civicrm_financial_trxn`
381 ADD CONSTRAINT `FK_civicrm_financial_trxn_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL;
383 -- Drop index for civicrm_financial_trxn.trxn_id and set default to null
384 ALTER TABLE `civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` varchar( 255 ) DEFAULT NULL ;
385 ALTER TABLE `civicrm_financial_trxn` DROP INDEX UI_ft_trxn_id;
387 -- remove trxn_type field
388 ALTER TABLE `civicrm_financial_trxn` DROP `trxn_type`;
392 ALTER TABLE `civicrm_payment_processor` ADD `payment_processor_type_id` int(10) unsigned NULL AFTER `description`,
393 ADD CONSTRAINT `FK_civicrm_payment_processor_payment_processor_type_id` FOREIGN KEY (`payment_processor_type_id`) REFERENCES `civicrm_payment_processor_type` (`id`);
395 UPDATE `civicrm_payment_processor` , `civicrm_payment_processor_type`
396 SET payment_processor_type_id = `civicrm_payment_processor_type`.id
397 WHERE payment_processor_type = `civicrm_payment_processor_type`.name;
399 ALTER TABLE `civicrm_payment_processor` DROP `payment_processor_type`;
402 ALTER TABLE `civicrm_price_field_value` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
404 ALTER TABLE `civicrm_line_item` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
406 ALTER TABLE `civicrm_price_field_value` ADD
407 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
408 ADD CONSTRAINT `FK_civicrm_price_field_value_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
410 ALTER TABLE `civicrm_line_item` ADD
411 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
412 ADD CONSTRAINT `FK_civicrm_line_item_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
414 ALTER TABLE `civicrm_grant` ADD
415 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
416 ADD CONSTRAINT `FK_civicrm_grant_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
418 ALTER TABLE `civicrm_product` ADD
419 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
420 ADD CONSTRAINT `FK_civicrm_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
422 ALTER TABLE `civicrm_premiums_product` ADD
423 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
424 ADD CONSTRAINT `FK_civicrm_premiums_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
426 ALTER TABLE `civicrm_contribution_product` ADD
427 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
428 ADD CONSTRAINT `FK_civicrm_contribution_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
431 ALTER TABLE `civicrm_discount`
432 DROP FOREIGN KEY FK_civicrm_discount_option_group_id,
433 DROP INDEX FK_civicrm_discount_option_group_id;
435 ALTER TABLE `civicrm_discount` CHANGE `option_group_id` `price_set_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'FK to civicrm_price_set';
437 ALTER TABLE `civicrm_discount`
438 ADD CONSTRAINT `FK_civicrm_discount_price_set_id` FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set` (`id`) ON DELETE CASCADE;
442 UPDATE civicrm_navigation SET `label` = 'Financial Types', `name` = 'Financial Types', `url` = 'civicrm/admin/financial/financialType?reset=1' WHERE `name` = 'Contribution Types';
445 -- Insert menu item at Administer > CiviContribute, below the section break below Premiums (Thank-you Gifts), just below Financial Account.
447 SELECT @parent_id := id from `civicrm_navigation` where name = 'CiviContribute' AND domain_id = {$domainID};
448 SELECT @add_weight_id := weight from `civicrm_navigation` where `name` = 'Financial Types' and `parent_id` = @parent_id;
450 UPDATE `civicrm_navigation`
451 SET `weight` = `weight`+1
452 WHERE `parent_id` = @parent_id
453 AND `weight` > @add_weight_id;
455 INSERT INTO `civicrm_navigation`
456 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
458 ( {$domainID}, 'civicrm/admin/financial/financialAccount&reset=1', '{ts escape="sql" skip="true"}Financial Account{/ts}', 'Financial Account', 'access CiviContribute,administer CiviCRM', 'AND', @parent_id, '1', NULL, @add_weight_id + 1 );
461 SELECT @contributionlastID := max(id) from civicrm_navigation where name = 'Contributions' AND domain_id = {$domainID};
463 SELECT @pledgeWeight := weight from civicrm_navigation where name = 'Pledges' and parent_id = @contributionlastID;
465 UPDATE `civicrm_navigation`
466 SET `weight` = `weight`+1
467 WHERE `parent_id` = @contributionlastID
468 AND `weight` > @pledgeWeight;
470 INSERT INTO civicrm_navigation
471 (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight)
473 ({$domainID}, NULL, '{ts escape="sql" skip="true"}Accounting Batches{/ts}', 'Accounting Batches', 'access CiviContribute', '', @contributionlastID, '1', 1, @pledgeWeight+1);
474 SET @financialTransactionID:=LAST_INSERT_ID();
476 INSERT INTO civicrm_navigation
477 (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
479 ({$domainID}, 'civicrm/financial/batch&reset=1&action=add', '{ts escape="sql" skip="true"}New Batch{/ts}', 'New Batch', 'access CiviContribute', 'AND', @financialTransactionID, '1', NULL, 1),
480 ({$domainID}, 'civicrm/financial/financialbatches?reset=1&batchStatus=1', '{ts escape="sql" skip="true"}Open Batches{/ts}', 'Open Batches', 'access CiviContribute', 'AND', @financialTransactionID, '1', NULL, 2),
481 ({$domainID}, 'civicrm/financial/financialbatches?reset=1&batchStatus=2', '{ts escape="sql" skip="true"}Closed Batches{/ts}', 'Closed Batches', 'access CiviContribute', 'AND', @financialTransactionID, '1', NULL, 3),
482 ({$domainID}, 'civicrm/financial/financialbatches?reset=1&batchStatus=5', '{ts escape="sql" skip="true"}Exported Batches{/ts}', 'Exported Batches', 'access CiviContribute', 'AND', @financialTransactionID, '1', NULL, 4);
484 -- Insert an entry for financial_account_type in civicrm_option_group and for the the following financial account types in civicrm_option_value as per CRM-8425
486 `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
488 ('financial_account_type', {localize}'{ts escape="sql"}Financial Account Type{/ts}'{/localize}, 1, 1),
489 ('account_relationship', {localize}'{ts escape="sql"}Account Relationship{/ts}'{/localize}, 1, 1),
490 ('financial_item_status', {localize}'{ts escape="sql"}Financial Item Status{/ts}'{/localize}, 1, 1),
491 ('batch_mode', {localize}'{ts escape="sql"}Batch Mode{/ts}'{/localize}, 1, 1);
493 SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type';
494 SELECT @option_group_id_arel := max(id) from civicrm_option_group where name = 'account_relationship';
495 SELECT @option_group_id_financial_item_status := max(id) from civicrm_option_group where name = 'financial_item_status';
498 `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`)
500 (@option_group_id_fat, {localize}'{ts escape="sql"}Asset{/ts}'{/localize}, 1, 'Asset', NULL, 0, 0, 1, {localize}'Things you own'{/localize}, 0, 1, 1, 2, NULL),
501 (@option_group_id_fat, {localize}'{ts escape="sql"}Liability{/ts}'{/localize}, 2, 'Liability', NULL, 0, 0, 2, {localize}'Things you own, like a grant still to be disbursed'{/localize}, 0, 1, 1, 2, NULL),
502 (@option_group_id_fat, {localize}'{ts escape="sql"}Revenue{/ts}'{/localize}, 3, 'Revenue', NULL, 0, 1, 3, {localize}'Income from contributions and sales of tickets and memberships'{/localize}, 0, 1, 1, 2, NULL),
503 (@option_group_id_fat, {localize}'{ts escape="sql"}Cost of Sales{/ts}'{/localize}, 4, 'Cost of Sales', NULL, 0, 0, 4, {localize}'Costs incurred to get revenue, e.g. premiums for donations, dinner for a fundraising dinner ticket'{/localize}, 0, 1, 1, 2, NULL),
504 (@option_group_id_fat, {localize}'{ts escape="sql"}Expenses{/ts}'{/localize}, 5, 'Expenses', NULL, 0, 0, 5, {localize}'Things that are paid for that are consumable, e.g. grants disbursed'{/localize}, 0, 1, 1, 2, NULL),
506 -- Financial account relationship
507 (@option_group_id_arel, {localize}'{ts escape="sql"}Income Account is{/ts}'{/localize}, 1, 'Income Account is', NULL, 0, 1, 1, {localize}'Income Account is'{/localize}, 0, 1, 1, 2, NULL),
508 (@option_group_id_arel, {localize}'{ts escape="sql"}Credit/Contra Account is{/ts}'{/localize}, 2, 'Credit/Contra Account is', NULL, 0, 0, 2, {localize}'Credit/Contra Account is'{/localize}, 0, 1, 0, 2, NULL),
509 (@option_group_id_arel, {localize}'{ts escape="sql"}Accounts Receivable Account is{/ts}'{/localize}, 3, 'Accounts Receivable Account is', NULL, 0, 0, 3, {localize}'Accounts Receivable Account is'{/localize}, 0, 1, 1, 2, NULL),
510 (@option_group_id_arel, {localize}'{ts escape="sql"}Credit Liability Account is{/ts}'{/localize}, 4, 'Credit Liability Account is', NULL, 0, 0, 4, {localize}'Credit Liability Account is'{/localize}, 0, 1, 0, 2, NULL),
511 (@option_group_id_arel, {localize}'{ts escape="sql"}Expense Account is{/ts}'{/localize}, 5, 'Expense Account is', NULL, 0, 0, 5, {localize}'Expense Account is'{/localize}, 0, 1, 1, 2, NULL),
512 (@option_group_id_arel, {localize}'{ts escape="sql"}Asset Account is{/ts}'{/localize}, 6, 'Asset Account is', NULL, 0, 0, 6, {localize}'Asset Account is'{/localize}, 0, 1, 1, 2, NULL),
513 (@option_group_id_arel, {localize}'{ts escape="sql"}Cost of Sales Account is{/ts}'{/localize}, 7, 'Cost of Sales Account is', NULL, 0, 0, 7, {localize}'Cost of Sales Account is'{/localize}, 0, 1, 1, 2, NULL),
514 (@option_group_id_arel, {localize}'{ts escape="sql"}Premiums Inventory Account is{/ts}'{/localize}, 8, 'Premiums Inventory Account is', NULL, 0, 0, 8, {localize}'Premiums Inventory Account is'{/localize}, 0, 1, 1, 2, NULL),
515 (@option_group_id_arel, {localize}'{ts escape="sql"}Discounts Account is{/ts}'{/localize}, 9, 'Discounts Account is', NULL, 0, 0, 9, {localize}'Discounts Account is'{/localize}, 0, 1, 1, 2, NULL),
517 -- Financial Item Status
518 (@option_group_id_financial_item_status, {localize}'{ts escape="sql"}Paid{/ts}'{/localize}, 1, 'Paid', NULL, 0, 0, 1, {localize}'Paid'{/localize}, 0, 1, 1, 2, NULL),
519 (@option_group_id_financial_item_status, {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 2, 'Partially paid', NULL, 0, 0, 2, {localize}'Partially paid'{/localize}, 0, 1, 1, 2, NULL),
520 (@option_group_id_financial_item_status, {localize}'{ts escape="sql"}Unpaid{/ts}'{/localize}, 3, 'Unpaid', NULL, 0, 0, 1, {localize}'Unpaid'{/localize}, 0, 1, 1, 2, NULL);
522 -- Data migration from civicrm_contibution_type to civicrm_financial_account, civicrm_financial_type, civicrm_entity_financial_account
523 SELECT @opval := value FROM civicrm_option_value WHERE name = 'Revenue' and option_group_id = @option_group_id_fat;
524 SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID};
526 INSERT INTO `civicrm_financial_account`
527 (`id`, `name`, `description`, `is_deductible`, `is_reserved`, `is_active`, `financial_account_type_id`, `contact_id`, accounting_code)
528 SELECT id, name, CONCAT('Default account for ', name), is_deductible, is_reserved, is_active, @opval, @domainContactId, accounting_code
529 FROM `civicrm_financial_type`;
531 -- CRM-9306 and CRM-11657
532 UPDATE `civicrm_financial_account` SET `is_default` = 0, `account_type_code` = 'INC';
534 SELECT @option_value_rel_id := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Income Account is';
535 SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat;
536 SELECT @opAsset := value FROM civicrm_option_value WHERE name = 'Asset' and option_group_id = @option_group_id_fat;
537 SELECT @opLiability := value FROM civicrm_option_value WHERE name = 'Liability' and option_group_id = @option_group_id_fat;
538 SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat;
540 -- CRM-11522 drop accounting_code after coping its values into financial_account
541 ALTER TABLE civicrm_financial_type DROP accounting_code;
544 `civicrm_financial_account` (`name`, `contact_id`, `financial_account_type_id`, `description`, `accounting_code`, `account_type_code`, `is_reserved`, `is_active`, `is_deductible`, `is_default`)
546 ('Banking Fees' , @domainContactId, @opexp, 'Payment processor fees and manually recorded banking fees', '5200', 'EXP', 0, 1, 0, 0),
547 ('Deposit Bank Account' , @domainContactId, @opAsset, 'All manually recorded cash and cheques go to this account', '1100', 'BANK', 0, 1, 0, 1),
548 ('Accounts Receivable' , @domainContactId, @opAsset, 'Amounts to be received later (eg pay later event revenues)', '1200', 'AR', 0, 1, 0, 0),
549 ('Accounts Payable' , @domainContactId, @opLiability, 'Amounts to be paid out such as grants and refunds', '2200', 'AP', 0, 1, 0, 0),
550 ('Premiums' , @domainContactId, @opCost, 'Account to record cost of premiums provided to payors', '5100', 'COGS', 0, 1, 0, 0),
551 ('Premiums Inventory' , @domainContactId, @opAsset, 'Account representing value of premiums inventory', '1375', 'OCASSET', 0, 1, 0, 0),
552 ('Discounts' , @domainContactId, @opval, 'Contra-revenue account for amounts discounted from sales', '4900', 'INC', 0, 1, 0, 0),
553 ('Payment Processor Account', @domainContactId, @opAsset, 'Account to record payments into a payment processor merchant account', '1150', 'BANK', 0, 1, 0, 0);
556 SELECT @option_value_rel_id_exp := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Expense Account is';
557 SELECT @option_value_rel_id_ar := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Accounts Receivable Account is';
558 SELECT @option_value_rel_id_as := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Asset Account is';
560 SELECT @financial_account_id_bf := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Banking Fees';
561 SELECT @financial_account_id_ap := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Accounts Receivable';
563 INSERT INTO `civicrm_entity_financial_account`
564 ( entity_table, entity_id, account_relationship, financial_account_id )
565 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id, fa.id
566 FROM `civicrm_financial_type` as ft LEFT JOIN `civicrm_financial_account` as fa ON ft.id = fa.id;
569 INSERT INTO `civicrm_entity_financial_account`
570 ( entity_table, entity_id, account_relationship, financial_account_id )
571 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_exp, @financial_account_id_bf
572 FROM `civicrm_financial_type` as ft;
574 -- Accounts Receivable
575 INSERT INTO `civicrm_entity_financial_account`
576 ( entity_table, entity_id, account_relationship, financial_account_id )
577 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_ar, @financial_account_id_ap
578 FROM `civicrm_financial_type` as ft;
581 SELECT @financial_account_id_ar := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Deposit Bank Account';
582 SELECT @financial_account_id_pp := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
584 INSERT INTO civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
585 SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_ar FROM `civicrm_option_group` cog
586 LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
587 WHERE cog.name = 'payment_instrument' AND cov.name NOT IN ('Credit Card', 'Debit Card');
589 INSERT INTO civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
590 SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_pp FROM `civicrm_option_group` cog
591 LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
592 WHERE cog.name = 'payment_instrument' AND cov.name IN ('Credit Card', 'Debit Card');
596 SELECT @financial_account_id_ppa := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
598 INSERT INTO civicrm_entity_financial_account (`entity_table`, `entity_id`, `account_relationship`, `financial_account_id`)
599 SELECT 'civicrm_payment_processor', id, @option_value_rel_id_as, @financial_account_id_ppa FROM `civicrm_payment_processor`;
601 -- CRM-9923 and CRM-11037
602 SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status';
604 SELECT @weight := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
607 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
609 (@option_group_id_batch_status, {localize}'Data Entry'{/localize}, @weight + 1, 'Data Entry', NULL, 0, 0, @weight + 1),
610 (@option_group_id_batch_status, {localize}'Reopened'{/localize}, @weight + 2, 'Reopened', NULL, 0, 0, @weight + 2),
611 (@option_group_id_batch_status, {localize}'Exported'{/localize}, @weight + 3, 'Exported' , NULL, 0, 0, @weight + 3);
613 -- Insert Batch Modes.
614 SELECT @option_group_id_batch_modes := max(id) from civicrm_option_group where name = 'batch_mode';
617 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
619 (@option_group_id_batch_modes, {localize}'Manual Batch'{/localize}, 1, 'Manual Batch', NULL, 0, 0, 1),
620 (@option_group_id_batch_modes, {localize}'Automatic Batch'{/localize}, 2, 'Automatic Batch' , NULL, 0, 0, 2);
622 -- End of civiaccounts upgrade
625 ALTER TABLE `civicrm_report_instance`
626 ADD COLUMN `drilldown_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to instance ID drilldown to',
627 ADD CONSTRAINT `FK_civicrm_report_instance_drilldown_id` FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance` (`id`) ON DELETE SET NULL;
630 ALTER TABLE `civicrm_membership_type`
631 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `relationship_direction`;
632 ALTER TABLE `civicrm_membership`
633 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships (membership_type override).' AFTER `owner_membership_id`;
634 ALTER TABLE `civicrm_membership_log`
635 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `membership_type_id`;
638 DELETE FROM civicrm_dashboard_contact WHERE contact_id NOT IN (SELECT id FROM civicrm_contact);
639 INSERT INTO `civicrm_dashboard`
640 (`domain_id`, {localize field='label'}`label`{/localize}, `url`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `fullscreen_url`, `is_fullscreen`, `is_reserved`)
641 SELECT id, {localize}'{ts escape="sql"}CiviCRM News{/ts}'{/localize}, 'civicrm/dashlet/blog&reset=1&snippet=5', 'access CiviCRM', NULL, 0, 0, 1, 0, 'civicrm/dashlet/blog&reset=1&snippet=5&context=dashletFullscreen', 1, 1
642 FROM `civicrm_domain`;
644 INSERT INTO `civicrm_dashboard_contact` (dashboard_id, contact_id, column_no, is_active)
645 SELECT (SELECT MAX(id) FROM `civicrm_dashboard`), contact_id, 1, IF (SUM(is_active) > 0, 0, 1)
646 FROM `civicrm_dashboard_contact` WHERE 1 GROUP BY contact_id;
649 ALTER TABLE `civicrm_event`
650 ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
651 ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
654 {foreach from=$locales item=loc}
655 ALTER TABLE `civicrm_event`
656 ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
657 ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
660 ALTER TABLE `civicrm_event`
661 ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
662 ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
666 UPDATE `civicrm_option_value` SET is_reserved = 0
667 WHERE name = 'Urgent' AND option_group_id = (SELECT id FROM `civicrm_option_group` WHERE name = 'case_status');
670 UPDATE `civicrm_state_province` SET name = 'Distrito Federal' WHERE name = 'Diatrito Federal';
672 -- CRM-9379 and CRM-11539
673 SELECT @option_group_id_act := MAX(id) FROM civicrm_option_group WHERE name = 'activity_type';
674 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act;
675 SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
676 SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviContribute';
678 INSERT INTO civicrm_option_value
679 (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, is_reserved, component_id, filter)
681 (@option_group_id_act, {localize field='label'}'Export Accounting Batch'{/localize}, @max_val+1, 'Export Accounting Batch', @max_wt+1, {localize field='description'}'Export Accounting Batch'{/localize}, 1, 1, @CompId, 1),
682 (@option_group_id_act, {localize field='label'}'Create Batch'{/localize}, @max_val+2, 'Create Batch', @max_wt+2, {localize field='description'}'Create Batch'{/localize}, 1, 1, @CompId, 1),
683 (@option_group_id_act, {localize field='label'}'Edit Batch'{/localize}, @max_val+3, 'Edit Batch', @max_wt+3, {localize field='description'}'Edit Batch'{/localize}, 1, 1, @CompId, 1);
687 `civicrm_job` (domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active)
689 id, 'Daily' , NULL, '{ts escape="sql" skip="true"}Disable expired relationships{/ts}', '{ts escape="sql" skip="true"}Disables relationships that have expired (ie. those relationships whose end date is in the past).{/ts}', 'job', 'disable_expired_relationships', NULL, 0
690 FROM `civicrm_domain`;
693 SELECT @country_id := max(id) from civicrm_country where name = 'Latvia';
695 DELETE FROM civicrm_state_province WHERE name IN ('Ventspils Apripkis', 'Aizkraukles Apripkis', 'Alkanes Apripkis', 'Balvu Apripkis', 'Bauskas Apripkis', 'Cesu Aprikis', 'Daugavpile Apripkis', 'Dobeles Apripkis', 'Gulbenes Aprlpkis', 'Jelgavas Apripkis', 'Jekabpils Apripkis', 'Kraslavas Apripkis', 'Kuldlgas Apripkis', 'Limbazu Apripkis', 'Liepajas Apripkis', 'Ludzas Apripkis', 'Madonas Apripkis', 'Ogres Apripkis', 'Preilu Apripkis', 'Rezaknes Apripkis', 'Rigas Apripkis', 'Saldus Apripkis', 'Talsu Apripkis', 'Tukuma Apriplcis', 'Valkas Apripkis', 'Valmieras Apripkis');
697 INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES
698 (@country_id, '002', 'Aizkraukles novads'),
699 (@country_id, '038', 'Jaunjelgavas novads'),
700 (@country_id, '072', 'Pļaviņu novads'),
701 (@country_id, '046', 'Kokneses novads'),
702 (@country_id, '065', 'Neretas novads'),
703 (@country_id, '092', 'Skrīveru novads'),
704 (@country_id, '007', 'Alūksnes novads'),
705 (@country_id, '009', 'Apes novads'),
706 (@country_id, '015', 'Balvu novads'),
707 (@country_id, '108', 'Viļakas novads'),
708 (@country_id, '014', 'Baltinavas novads'),
709 (@country_id, '082', 'Rug�ju novads'),
710 (@country_id, '016', 'Bauskas novads'),
711 (@country_id, '034', 'Iecavas novads'),
712 (@country_id, '083', 'Rund�les novads'),
713 (@country_id, '105', 'Vecumnieku novads'),
714 (@country_id, '022', 'CÄ“su novads'),
715 (@country_id, '055', 'LÄ«gatnes novads'),
716 (@country_id, '008', 'Amatas novads'),
717 (@country_id, '039', 'Jaunpiebalgas novads'),
718 (@country_id, '075', 'Priekuļu novads'),
719 (@country_id, '070', 'P�rgaujas novads'),
720 (@country_id, '076', 'Raunas novads'),
721 (@country_id, '104', 'Vecpiebalgas novads'),
722 (@country_id, '025', 'Daugavpils novads'),
723 (@country_id, '036', 'Ilūkstes novads'),
724 (@country_id, '026', 'Dobeles novads'),
725 (@country_id, '010', 'Auces novads'),
726 (@country_id, '098', 'TÄ“rvetes novads'),
727 (@country_id, '033', 'Gulbenes novads'),
728 (@country_id, '041', 'Jelgavas novads'),
729 (@country_id, '069', 'Ozolnieku novads'),
730 (@country_id, '042', 'JÄ“kabpils novads'),
731 (@country_id, '004', 'Aknīstes novads'),
732 (@country_id, '107', 'Viesītes novads'),
733 (@country_id, '049', 'Krustpils novads'),
734 (@country_id, '085', 'Salas novads'),
735 (@country_id, '047', 'Kr�slavas novads'),
736 (@country_id, '024', 'Dagdas novads'),
737 (@country_id, '001', 'Aglonas novads'),
738 (@country_id, '050', 'Kuldīgas novads'),
739 (@country_id, '093', 'Skrundas novads'),
740 (@country_id, '006', 'Alsungas novads'),
741 (@country_id, '003', 'Aizputes novads'),
742 (@country_id, '028', 'Durbes novads'),
743 (@country_id, '032', 'Grobiņas novads'),
744 (@country_id, '071', 'P�vilostas novads'),
745 (@country_id, '074', 'Priekules novads'),
746 (@country_id, '066', 'NÄ«cas novads'),
747 (@country_id, '081', 'Rucavas novads'),
748 (@country_id, '100', 'Vaiņodes novads'),
749 (@country_id, '054', 'Limbažu novads'),
750 (@country_id, '005', 'Alojas novads'),
751 (@country_id, '086', 'Salacgrīvas novads'),
752 (@country_id, '058', 'Ludzas novads'),
753 (@country_id, '044', 'K�rsavas novads'),
754 (@country_id, '110', 'Zilupes novads'),
755 (@country_id, '023', 'Ciblas novads'),
756 (@country_id, '059', 'Madonas novads'),
757 (@country_id, '021', 'Cesvaines novads'),
758 (@country_id, '057', 'Lub�nas novads'),
759 (@country_id, '102', 'Varakļ�nu novads'),
760 (@country_id, '030', 'Ērgļu novads'),
761 (@country_id, '067', 'Ogres novads'),
762 (@country_id, '035', 'Ikšķiles novads'),
763 (@country_id, '051', 'Ķeguma novads'),
764 (@country_id, '053', 'Lielv�rdes novads'),
765 (@country_id, '073', 'Preiļu novads'),
766 (@country_id, '056', 'Līv�nu novads'),
767 (@country_id, '078', 'Riebiņu novads'),
768 (@country_id, '103', 'V�rkavas novads'),
769 (@country_id, '077', 'RÄ“zeknes novads'),
770 (@country_id, '109', 'Viļ�nu novads'),
771 (@country_id, '013', 'Baldones novads'),
772 (@country_id, '052', 'Ķekavas novads'),
773 (@country_id, '068', 'Olaines novads'),
774 (@country_id, '087', 'Salaspils novads'),
775 (@country_id, '089', 'Saulkrastu novads'),
776 (@country_id, '091', 'Siguldas novads'),
777 (@country_id, '037', 'In�ukalna novads'),
778 (@country_id, '011', 'Ādažu novads'),
779 (@country_id, '012', 'Babītes novads'),
780 (@country_id, '020', 'Carnikavas novads'),
781 (@country_id, '031', 'Garkalnes novads'),
782 (@country_id, '048', 'Krimuldas novads'),
783 (@country_id, '061', 'M�lpils novads'),
784 (@country_id, '062', 'M�rupes novads'),
785 (@country_id, '080', 'Ropažu novads'),
786 (@country_id, '090', 'SÄ“jas novads'),
787 (@country_id, '095', 'Stopiņu novads'),
788 (@country_id, '088', 'Saldus novads'),
789 (@country_id, '018', 'Brocēnu novads'),
790 (@country_id, '097', 'Talsu novads'),
791 (@country_id, '027', 'Dundagas novads'),
792 (@country_id, '063', 'MÄ“rsraga novads'),
793 (@country_id, '079', 'Rojas novads'),
794 (@country_id, '099', 'Tukuma novads'),
795 (@country_id, '043', 'Kandavas novads'),
796 (@country_id, '029', 'Engures novads'),
797 (@country_id, '040', 'Jaunpils novads'),
798 (@country_id, '101', 'Valkas novads'),
799 (@country_id, '094', 'Smiltenes novads'),
800 (@country_id, '096', 'Stren�u novads'),
801 (@country_id, '045', 'Kocēnu novads'),
802 (@country_id, '060', 'Mazsalacas novads'),
803 (@country_id, '084', 'RÅ«jienas novads'),
804 (@country_id, '017', 'Beverīnas novads'),
805 (@country_id, '019', 'Burtnieku novads'),
806 (@country_id, '064', 'Naukšēnu novads'),
807 (@country_id, '106', 'Ventspils novads'),
808 (@country_id, 'JKB', 'JÄ“kabpils'),
809 (@country_id, 'VMR', 'Valmiera');
812 ALTER TABLE `civicrm_batch` CHANGE `type_id` `type_id` INT( 10 ) UNSIGNED NULL COMMENT 'fk to Batch Type options in civicrm_option_values';
813 UPDATE `civicrm_batch` SET `mode_id` = '1';
815 -- add Refunded in contribution status
816 SELECT @option_group_id_cs := MAX(id) FROM civicrm_option_group WHERE name = 'contribution_status';
818 SELECT @max_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
821 `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`)
823 (@option_group_id_cs, {localize}'{ts escape="sql"}Refunded{/ts}'{/localize}, @max_weight + 1, 'Refunded', NULL, 0, NULL, @max_weight + 1, 0, 1, 1, NULL, NULL);
825 -- Payprocs from extensions may have long titles
826 ALTER TABLE civicrm_payment_processor_type MODIFY COLUMN title varchar(127);
829 ALTER TABLE `civicrm_address`
830 ADD COLUMN manual_geo_code tinyint(4) DEFAULT '0' COMMENT 'Is this a manually entered geo code.';
833 UPDATE `civicrm_setting` SET `group_name` = 'Personal Preferences' WHERE `group_name` = 'Navigation Menu';
837 INSERT INTO civicrm_action_mapping ( entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient )
839 ( 'civicrm_participant', 'event_template', 'Event Template', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
841 -- CRM-11802 Fix ON DELETE CASCADE constraint for line_item.price_field_id
842 ALTER TABLE `civicrm_line_item`
843 DROP FOREIGN KEY `FK_civicrm_line_item_price_field_id`,
844 CHANGE `price_field_id` `price_field_id` INT( 10 ) UNSIGNED DEFAULT NULL;
846 ALTER TABLE `civicrm_line_item`
847 ADD CONSTRAINT `FK_civicrm_line_item_price_field_id` FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(id) ON DELETE SET NULL;
850 -- update all location info of domain
851 -- like address, email, phone etc.
852 UPDATE civicrm_domain cd
853 LEFT JOIN civicrm_loc_block clb ON cd.loc_block_id = clb.id
854 LEFT JOIN civicrm_address ca ON clb.address_id = ca.id
855 LEFT JOIN civicrm_phone cp ON cp.id = clb.phone_id
856 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id
858 ca.contact_id = cd.contact_id, cp.contact_id = cd.contact_id, ce.contact_id = cd.contact_id;
860 -- Delete rows from civicrm_loc_block used for civicrm_domain
861 DELETE clb.* FROM civicrm_domain cd
862 LEFT JOIN civicrm_loc_block clb ON clb.id = cd.loc_block_id;
864 -- Delete loc_block_id from civicrm_domain
865 ALTER TABLE `civicrm_domain` DROP loc_block_id;
868 -- pledge payments should not be cancelled if the contribution was
869 -- compledged but the pledge is cancelled
871 civicrm_pledge_payment pp
872 INNER JOIN civicrm_contribution c ON
873 c.id = pp.contribution_id AND pp.status_id =3
874 AND contribution_status_id = 1
875 SET pp.status_id = contribution_status_id