CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl

   1 {include file='../CRM/Upgrade/4.3.alpha1.msg_template/civicrm_msg_template.tpl'}
   2
   3 -- CRM-10999
   4 ALTER TABLE `civicrm_premiums`
   5 ADD COLUMN `premiums_nothankyou_position` int(10) unsigned DEFAULT '1';
   6
   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) = '';
  11
  12 -- CRM-8507
  13 ALTER TABLE civicrm_custom_field
  14   ADD UNIQUE INDEX `UI_name_custom_group_id` (`name`, `custom_group_id`);
  15
  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');
  23
  24 -- CRM-10553
  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.';
  31
  32 -- CRM-10296
  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;
  42
  43 -- CRM-10738
  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';
  47
  48 -- CRM-10860
  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';
  51
  52 -- CRM-10863
  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');
  56
  57 -- CRM-10899 and CRM-10999
  58 {if $multilingual}
  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)';
  63   {/foreach}
  64 {else}
  65     UPDATE civicrm_option_group SET title = '{ts escape="sql"}Currencies Enabled{/ts}' WHERE name = "currencies_enabled";
  66 {/if}
  67
  68 -- CRM-11047
  69 ALTER TABLE civicrm_job DROP COLUMN api_prefix;
  70
  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.';
  75
  76 -- CRM-11759
  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;
  79
  80 INSERT INTO `civicrm_job`
  81     ( domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active )
  82 VALUES
  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);
  85
  86 -- CRM-11117
  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');
  88
  89 -- CRM-10885
  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;
  92
  93 UPDATE civicrm_dedupe_rule_group
  94   SET used = 'General' WHERE is_default = 0;
  95
  96 UPDATE civicrm_dedupe_rule_group
  97     SET used = CASE level
  98         WHEN 'Fuzzy' THEN 'Supervised'
  99         WHEN 'Strict'   THEN 'Unsupervised'
 100     END
 101 WHERE is_default = 1;
 102
 103 UPDATE civicrm_dedupe_rule_group
 104   SET name = CONCAT_WS('', `contact_type`, `used`)
 105 WHERE is_default = 1 OR is_reserved = 1;
 106
 107 UPDATE civicrm_dedupe_rule_group
 108   SET  title = 'Name and Email'
 109 WHERE contact_type IN ('Organization', 'Household') AND used IN ('Unsupervised', 'Supervised');
 110
 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)'
 116     END
 117 WHERE contact_type = 'Individual' AND is_reserved = 1;
 118
 119 ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN level;
 120
 121 -- CRM-10771
 122 ALTER TABLE civicrm_uf_field
 123   ADD `is_multi_summary` tinyint(4) DEFAULT '0' COMMENT 'Include in multi-record listing?';
 124
 125 -- CRM-1115
 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 INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Kosovo", "XK", @region_id, 0);
 129
 130 UPDATE civicrm_country SET name = 'Libya' WHERE name LIKE 'Libyan%';
 131 UPDATE civicrm_country SET name = 'Congo, Republic of the' WHERE name = 'Congo';
 132
 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;
 136
 137 INSERT INTO civicrm_navigation
 138     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 139 VALUES
 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 )
 143 VALUES
 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 )
 147 VALUES
 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 )
 151 VALUES
 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 )
 155 VALUES
 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 )
 159 VALUES
 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 )
 163 VALUES
 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 )
 167 VALUES
 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 )
 171 VALUES
 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) );
 173
 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`;
 176
 177 -- CRM-11070
 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;
 180
 181 INSERT INTO
 182 `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
 183 VALUES
 184 (@option_group_id_tuf, {localize}'Attachments'{/localize}, 'civicrm_file', 'Attachments', NULL, 0, 0, @weight = @weight + 1);
 185
 186 ALTER TABLE civicrm_extension MODIFY COLUMN type ENUM( 'payment', 'search', 'report', 'module','sms') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ;
 187
 188 -- CRM-9914
 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';
 191
 192 -- CRM-11292
 193 ALTER TABLE `civicrm_phone`
 194 ADD `phone_numeric` varchar(32)
 195 COMMENT 'Phone number stripped of all whitespace, letters, and punctuation.'
 196 AFTER `phone_ext`,
 197 ADD INDEX phone_numeric_index(`phone_numeric`);
 198
 199
 200 -- civiaccounts upgrade
 201
 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);
 218
 219 -- CRM-8425
 220 -- Rename table civicrm_contribution_type to civicrm_financial_type
 221 RENAME TABLE `civicrm_contribution_type` TO `civicrm_financial_type`;
 222
 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),
 226 DROP INDEX UI_name;
 227
 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',
 234   PRIMARY KEY (`id`),
 235 KEY `FK_civicrm_entity_financial_account_financial_account_id` (`financial_account_id`)
 236 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 237
 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`);
 241
 242 -- CRM-9730 Table structure for table `civicrm_financial_item`
 243 --
 244
 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',
 257   PRIMARY KEY (`id`),
 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;
 265
 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;
 269
 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`);
 273
 274 ALTER TABLE `civicrm_entity_financial_trxn`
 275 DROP currency;
 276
 277 -- CRM-12312
 278 UPDATE civicrm_event SET contribution_type_id = NULL WHERE contribution_type_id = 0;
 279
 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
 281
 282 ALTER TABLE `civicrm_pledge`
 283 DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id,
 284 DROP INDEX FK_civicrm_pledge_contribution_type_id;
 285
 286 ALTER TABLE `civicrm_pledge`
 287 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
 288
 289 ALTER TABLE `civicrm_pledge`
 290 ADD CONSTRAINT FK_civicrm_pledge_financial_type_id  FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 291
 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;
 295
 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';
 298
 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);
 301
 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;
 305
 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';
 308
 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);
 311
 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.';
 314
 315 ALTER TABLE `civicrm_contribution`
 316 DROP FOREIGN KEY FK_civicrm_contribution_contribution_type_id,
 317 DROP INDEX FK_civicrm_contribution_contribution_type_id;
 318
 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).';
 321
 322 ALTER TABLE `civicrm_contribution`
 323 ADD CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 324
 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;
 328
 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';
 333
 334 {if $multilingual}
 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';
 339   {/foreach}
 340 {else}
 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';
 344 {/if}
 345
 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);
 348
 349 ALTER TABLE `civicrm_contribution_recur`
 350 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
 351
 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);
 354
 355 -- CRM-9083
 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`);
 365
 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);
 369
 370 ALTER TABLE `civicrm_financial_trxn` ADD `payment_processor_id` int unsigned COMMENT 'Payment Processor for this contribution Page';
 371
 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;
 376
 377 -- remove payment_processor field
 378 ALTER TABLE `civicrm_financial_trxn` DROP `payment_processor`;
 379
 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;
 382
 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;
 386
 387 -- remove trxn_type field
 388 ALTER TABLE `civicrm_financial_trxn` DROP `trxn_type`;
 389
 390 -- CRM-9731
 391
 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`);
 394
 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;
 398
 399 ALTER TABLE `civicrm_payment_processor` DROP `payment_processor_type`;
 400
 401 -- CRM-9730
 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';
 403
 404 ALTER TABLE `civicrm_line_item` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
 405
 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`);
 409
 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`);
 413
 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`);
 417
 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`);
 421
 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`);
 425
 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`);
 429
 430 -- CRM-11122
 431 ALTER TABLE `civicrm_discount`
 432 DROP FOREIGN KEY FK_civicrm_discount_option_group_id,
 433 DROP INDEX FK_civicrm_discount_option_group_id;
 434
 435 ALTER TABLE `civicrm_discount` CHANGE `option_group_id` `price_set_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'FK to civicrm_price_set';
 436
 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;
 439
 440 -- CRM-8425
 441
 442 UPDATE civicrm_navigation SET  `label` = 'Financial Types', `name` = 'Financial Types', `url` = 'civicrm/admin/financial/financialType?reset=1' WHERE `name` = 'Contribution Types';
 443
 444 -- CRM-9199
 445 -- Insert menu item at Administer > CiviContribute, below the section break below Premiums (Thank-you Gifts), just below Financial Account.
 446
 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;
 449
 450 UPDATE `civicrm_navigation`
 451 SET `weight` = `weight`+1
 452 WHERE `parent_id` = @parent_id
 453 AND `weight` > @add_weight_id;
 454
 455 INSERT INTO `civicrm_navigation`
 456         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 457 VALUES
 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 );
 459
 460 -- CRM-10944
 461 SELECT @contributionlastID := max(id) from civicrm_navigation where name = 'Contributions' AND domain_id = {$domainID};
 462
 463 SELECT @pledgeWeight := weight from civicrm_navigation where name = 'Pledges' and parent_id = @contributionlastID;
 464
 465 UPDATE `civicrm_navigation`
 466 SET `weight` = `weight`+1
 467 WHERE `parent_id` = @contributionlastID
 468 AND `weight` > @pledgeWeight;
 469
 470 INSERT INTO civicrm_navigation
 471     (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight)
 472 VALUES
 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();
 475
 476 INSERT INTO civicrm_navigation
 477     (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 478 VALUES
 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);
 483
 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
 485 INSERT INTO
 486    `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
 487 VALUES
 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);
 492
 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';
 496
 497 INSERT INTO
 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`)
 499 VALUES
 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),
 505
 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),
 516
 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);
 521
 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};
 525
 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`;
 530
 531 -- CRM-9306 and CRM-11657
 532 UPDATE `civicrm_financial_account` SET `is_default` = 0, `account_type_code` = 'INC';
 533
 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;
 539
 540 -- CRM-11522 drop accounting_code after coping its values into financial_account
 541 ALTER TABLE civicrm_financial_type DROP accounting_code;
 542
 543 INSERT INTO
 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`)
 545 VALUES
 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);
 554
 555 -- CRM-10926
 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';
 559
 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';
 562
 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;
 567
 568 -- Banking Fees
 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;
 573
 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;
 579
 580 -- CRM-11516
 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';
 583
 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');
 588
 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');
 593
 594
 595 -- CRM-11515
 596 SELECT @financial_account_id_ppa := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
 597
 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`;
 600
 601 -- CRM-9923 and CRM-11037
 602 SELECT @option_group_id_batch_status   := max(id) from civicrm_option_group where name = 'batch_status';
 603
 604 SELECT @weight                 := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
 605
 606 INSERT INTO
 607    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
 608 VALUES
 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);
 612
 613 -- Insert Batch Modes.
 614 SELECT @option_group_id_batch_modes := max(id) from civicrm_option_group where name = 'batch_mode';
 615
 616 INSERT INTO
 617    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
 618 VALUES
 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);
 621
 622 -- End of civiaccounts upgrade
 623
 624 -- CRM-10933
 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;
 628
 629 -- CRM-10012
 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`;
 636
 637 -- CRM-11358
 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`;
 643
 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;
 647
 648 -- CRM-11387
 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';
 652
 653 {if $multilingual}
 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';
 658   {/foreach}
 659 {else}
 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';
 663 {/if}
 664
 665 -- CRM-11347
 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');
 668
 669 -- CRM-11400
 670 UPDATE `civicrm_state_province` SET name = 'Distrito Federal' WHERE name = 'Diatrito Federal';
 671
 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';
 677
 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)
 680 VALUES
 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);
 684
 685 -- CRM-11341
 686 INSERT INTO
 687   `civicrm_job` (domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active)
 688 SELECT
 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`;
 691
 692 -- CRM-11367
 693 SELECT @country_id   := max(id) from civicrm_country where name = 'Latvia';
 694
 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');
 696
 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');
 810
 811 -- CRM-11507
 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';
 814
 815 -- add Refunded in contribution status
 816 SELECT @option_group_id_cs := MAX(id) FROM civicrm_option_group WHERE name = 'contribution_status';
 817
 818 SELECT @max_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 819
 820 INSERT INTO
 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`)
 822 VALUES
 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);
 824
 825 -- Payprocs from extensions may have long titles
 826 ALTER TABLE civicrm_payment_processor_type MODIFY COLUMN title varchar(127);
 827
 828 -- CRM-11665
 829 ALTER TABLE `civicrm_address`
 830   ADD COLUMN manual_geo_code tinyint(4) DEFAULT '0' COMMENT 'Is this a manually entered geo code.';
 831
 832 -- CRM-11761
 833 UPDATE `civicrm_setting` SET `group_name` = 'Personal Preferences' WHERE `group_name` = 'Navigation Menu';
 834
 835 -- CRM-11779
 836
 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 )
 838 VALUES
 839 ( 'civicrm_participant', 'event_template', 'Event Template', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
 840
 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;
 845
 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;
 848
 849 -- CRM-11821
 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
 857 SET
 858 ca.contact_id = cd.contact_id, cp.contact_id = cd.contact_id, ce.contact_id = cd.contact_id;
 859
 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;
 863
 864 -- Delete loc_block_id from civicrm_domain
 865 ALTER TABLE `civicrm_domain` DROP loc_block_id;
 866
 867 -- CRM11818
 868 -- pledge payments should not be cancelled if the contribution was
 869 -- compledged but the pledge is cancelled
 870 UPDATE
 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
 876