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';
 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 CREATE TABLE IF NOT EXISTS `civicrm_payment` (
 267   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
 268   `payment_batch_number` int(10) unsigned NOT NULL COMMENT 'Payment Batch Nnumber',
 269   `payment_number` int(10) unsigned NOT NULL COMMENT 'Payment Number',
 270   `financial_type_id` int(10) unsigned NOT NULL COMMENT 'Financial Type ID',
 271   `contact_id` int(10) unsigned NOT NULL COMMENT 'Contact ID',
 272   `payment_created_date` date DEFAULT NULL COMMENT 'Payment Created Date.',
 273   `payment_date` date DEFAULT NULL COMMENT 'Payment Date.',
 274   `payable_to_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Payable To Name.',
 275   `payable_to_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Payable To Address.',
 276   `amount` decimal(20,2) NOT NULL COMMENT 'Requested grant amount, in default currency.',
 277   `currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
 278   `payment_reason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Payment Reason.',
 279   `replaces_payment_id` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Replaces Payment Id.',
 280   PRIMARY KEY (`id`)
 281 )ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 282
 283 ALTER TABLE `civicrm_batch`
 284 ADD `payment_instrument_id` int(10) unsigned DEFAULT NULL COMMENT 'fk to Payment Instrument options in civicrm_option_values',
 285 ADD `exported_date` datetime DEFAULT NULL;
 286
 287 ALTER TABLE `civicrm_financial_item`
 288   ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`),
 289   ADD CONSTRAINT `FK_civicrm_financial_item_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);
 290
 291 ALTER TABLE `civicrm_entity_financial_trxn`
 292 DROP currency;
 293
 294 -- CRM-12312
 295 UPDATE civicrm_event SET contribution_type_id = NULL WHERE contribution_type_id = 0;
 296
 297 -- 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
 298
 299 ALTER TABLE `civicrm_pledge`
 300 DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id,
 301 DROP INDEX FK_civicrm_pledge_contribution_type_id;
 302
 303 ALTER TABLE `civicrm_pledge`
 304 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
 305
 306 ALTER TABLE `civicrm_pledge`
 307 ADD CONSTRAINT FK_civicrm_pledge_financial_type_id  FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 308
 309 ALTER TABLE `civicrm_membership_type`
 310 DROP FOREIGN KEY FK_civicrm_membership_type_contribution_type_id,
 311 DROP INDEX FK_civicrm_membership_type_contribution_type_id;
 312
 313 ALTER TABLE `civicrm_membership_type`
 314 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';
 315
 316 ALTER TABLE `civicrm_membership_type`
 317 ADD CONSTRAINT FK_civicrm_membership_type_financial_type_id  FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 318
 319 ALTER TABLE `civicrm_price_set`
 320 DROP FOREIGN KEY FK_civicrm_price_set_contribution_type_id,
 321 DROP INDEX FK_civicrm_price_set_contribution_type_id;
 322
 323 ALTER TABLE `civicrm_price_set`
 324 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';
 325
 326 ALTER TABLE `civicrm_price_set`
 327 ADD CONSTRAINT FK_civicrm_price_set_financial_type_id  FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 328
 329 ALTER TABLE `civicrm_event`
 330 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.';
 331
 332 ALTER TABLE `civicrm_contribution`
 333 DROP FOREIGN KEY FK_civicrm_contribution_contribution_type_id,
 334 DROP INDEX FK_civicrm_contribution_contribution_type_id;
 335
 336 ALTER TABLE `civicrm_contribution`
 337 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).';
 338
 339 ALTER TABLE `civicrm_contribution`
 340 ADD CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 341
 342 ALTER TABLE `civicrm_contribution_page`
 343 DROP FOREIGN KEY FK_civicrm_contribution_page_contribution_type_id,
 344 DROP INDEX FK_civicrm_contribution_page_contribution_type_id;
 345
 346 ALTER TABLE `civicrm_contribution_page`
 347 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',
 348 ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
 349 ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
 350
 351 {if $multilingual}
 352   {foreach from=$locales item=loc}
 353     ALTER TABLE `civicrm_contribution_page`
 354       ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
 355       ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
 356   {/foreach}
 357 {else}
 358   ALTER TABLE `civicrm_contribution_page`
 359     ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
 360     ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
 361 {/if}
 362
 363 ALTER TABLE `civicrm_contribution_page`
 364 ADD CONSTRAINT  FK_civicrm_contribution_page_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 365
 366 ALTER TABLE `civicrm_contribution_recur`
 367 CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
 368
 369 ALTER TABLE `civicrm_contribution_recur`
 370 ADD CONSTRAINT FK_civicrm_contribution_recur_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
 371
 372 -- CRM-9083
 373 ALTER TABLE `civicrm_financial_trxn` CHANGE `to_account_id` `to_financial_account_id` int unsigned COMMENT 'FK to financial_financial_account table.',
 374 CHANGE `from_account_id` `from_financial_account_id` int unsigned COMMENT 'FK to financial_account table.',
 375 ADD `status_id` int(10) unsigned DEFAULT NULL,
 376 CHANGE `trxn_id` trxn_id varchar(255) COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
 377 CHANGE `trxn_date` trxn_date datetime DEFAULT NULL,
 378 ADD `payment_instrument_id` int unsigned DEFAULT NULL COMMENT 'FK to payment_instrument option group values',
 379 ADD `check_number` VARCHAR( 255 ) NULL DEFAULT NULL,
 380 ADD INDEX `UI_ftrxn_check_number` (`check_number`),
 381 ADD INDEX `UI_ftrxn_payment_instrument_id` (`payment_instrument_id`);
 382
 383 ALTER TABLE `civicrm_financial_trxn`
 384 ADD CONSTRAINT FK_civicrm_financial_trxn_to_financial_account_id FOREIGN KEY (`to_financial_account_id`) REFERENCES civicrm_financial_account (id),
 385 ADD CONSTRAINT FK_civicrm_financial_trxn_from_financial_account_id FOREIGN KEY (`from_financial_account_id`) REFERENCES civicrm_financial_account (id);
 386
 387 ALTER TABLE `civicrm_financial_trxn` ADD `payment_processor_id` int unsigned COMMENT 'Payment Processor for this contribution Page';
 388
 389 -- Fill in the payment_processor_id based on a lookup using the payment_processor field
 390 UPDATE `civicrm_payment_processor` cppt,  `civicrm_financial_trxn` cft
 391 SET cft.`payment_processor_id` = cppt.`id`
 392 WHERE cft.`payment_processor` = cppt.`payment_processor_type` and `is_test` = 0;
 393
 394 -- remove payment_processor field
 395 ALTER TABLE `civicrm_financial_trxn` DROP `payment_processor`;
 396
 397 ALTER TABLE `civicrm_financial_trxn`
 398   ADD CONSTRAINT `FK_civicrm_financial_trxn_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL;
 399
 400 -- Drop index for civicrm_financial_trxn.trxn_id and set default to null
 401 ALTER TABLE `civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` varchar( 255 ) DEFAULT NULL ;
 402 ALTER TABLE `civicrm_financial_trxn` DROP INDEX UI_ft_trxn_id;
 403
 404 -- remove trxn_type field
 405 ALTER TABLE `civicrm_financial_trxn` DROP `trxn_type`;
 406
 407 -- CRM-9731
 408
 409 ALTER TABLE `civicrm_payment_processor` ADD `payment_processor_type_id` int(10) unsigned NULL AFTER `description`,
 410 ADD CONSTRAINT `FK_civicrm_payment_processor_payment_processor_type_id` FOREIGN KEY (`payment_processor_type_id`) REFERENCES `civicrm_payment_processor_type` (`id`);
 411
 412 UPDATE `civicrm_payment_processor` , `civicrm_payment_processor_type`
 413 SET payment_processor_type_id = `civicrm_payment_processor_type`.id
 414 WHERE payment_processor_type = `civicrm_payment_processor_type`.name;
 415
 416 ALTER TABLE `civicrm_payment_processor` DROP `payment_processor_type`;
 417
 418 -- CRM-9730
 419 ALTER TABLE `civicrm_price_field_value` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
 420
 421 ALTER TABLE `civicrm_line_item` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
 422
 423 ALTER TABLE `civicrm_price_field_value` ADD
 424 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 425  ADD CONSTRAINT `FK_civicrm_price_field_value_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 426
 427 ALTER TABLE `civicrm_line_item` ADD
 428 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 429  ADD CONSTRAINT `FK_civicrm_line_item_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 430
 431 ALTER TABLE `civicrm_grant` ADD
 432 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 433  ADD CONSTRAINT `FK_civicrm_grant_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 434
 435 ALTER TABLE `civicrm_product` ADD
 436 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 437 ADD CONSTRAINT `FK_civicrm_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 438
 439 ALTER TABLE `civicrm_premiums_product` ADD
 440 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 441 ADD CONSTRAINT `FK_civicrm_premiums_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 442
 443 ALTER TABLE `civicrm_contribution_product` ADD
 444 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
 445 ADD CONSTRAINT `FK_civicrm_contribution_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
 446
 447 -- CRM-11122
 448 ALTER TABLE `civicrm_discount`
 449 DROP FOREIGN KEY FK_civicrm_discount_option_group_id,
 450 DROP INDEX FK_civicrm_discount_option_group_id;
 451
 452 ALTER TABLE `civicrm_discount` CHANGE `option_group_id` `price_set_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'FK to civicrm_price_set';
 453
 454 ALTER TABLE `civicrm_discount`
 455   ADD CONSTRAINT `FK_civicrm_discount_price_set_id` FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set` (`id`) ON DELETE CASCADE;
 456
 457 -- CRM-8425
 458
 459 UPDATE civicrm_navigation SET  `label` = 'Financial Types', `name` = 'Financial Types', `url` = 'civicrm/admin/financial/financialType?reset=1' WHERE `name` = 'Contribution Types';
 460
 461 -- CRM-9199
 462 -- Insert menu item at Administer > CiviContribute, below the section break below Premiums (Thank-you Gifts), just below Financial Account.
 463
 464 SELECT @parent_id := id from `civicrm_navigation` where name = 'CiviContribute';
 465 SELECT @add_weight_id := weight from `civicrm_navigation` where `name` = 'Financial Types' and `parent_id` = @parent_id;
 466
 467 UPDATE `civicrm_navigation`
 468 SET `weight` = `weight`+1
 469 WHERE `parent_id` = @parent_id
 470 AND `weight` > @add_weight_id;
 471
 472 INSERT INTO `civicrm_navigation`
 473         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 474 VALUES
 475   ( {$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 );
 476
 477 -- CRM-10944
 478 SELECT @contributionlastID := max(id) from civicrm_navigation where name = 'Contributions';
 479
 480 SELECT @pledgeWeight := weight from civicrm_navigation where name = 'Pledges' and parent_id = @contributionlastID;
 481
 482 UPDATE `civicrm_navigation`
 483 SET `weight` = `weight`+1
 484 WHERE `parent_id` = @contributionlastID
 485 AND `weight` > @pledgeWeight;
 486
 487 INSERT INTO civicrm_navigation
 488     (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight)
 489 VALUES
 490     ({$domainID}, NULL, '{ts escape="sql" skip="true"}Accounting Batches{/ts}',  'Accounting Batches', 'access CiviContribute', '', @contributionlastID, '1',  1,   @pledgeWeight+1);
 491 SET @financialTransactionID:=LAST_INSERT_ID();
 492
 493 INSERT INTO civicrm_navigation
 494     (domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 495 VALUES
 496     ({$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),
 497     ({$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),
 498     ({$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),
 499     ({$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);
 500
 501 -- 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
 502 INSERT INTO
 503    `civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
 504 VALUES
 505    ('financial_account_type', {localize}'{ts escape="sql"}Financial Account Type{/ts}'{/localize}, 1, 1),
 506    ('account_relationship', {localize}'{ts escape="sql"}Account Relationship{/ts}'{/localize}, 1, 1),
 507    ('financial_item_status', {localize}'{ts escape="sql"}Financial Item Status{/ts}'{/localize}, 1, 1),
 508    ('batch_mode', {localize}'{ts escape="sql"}Batch Mode{/ts}'{/localize}, 1, 1);
 509
 510 SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type';
 511 SELECT @option_group_id_arel           := max(id) from civicrm_option_group where name = 'account_relationship';
 512 SELECT @option_group_id_financial_item_status := max(id) from civicrm_option_group where name = 'financial_item_status';
 513
 514 INSERT INTO
 515    `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`)
 516 VALUES
 517    (@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),
 518    (@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),
 519    (@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),
 520    (@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),
 521    (@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),
 522
 523 -- Financial account relationship
 524    (@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),
 525    (@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),
 526    (@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),
 527    (@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),
 528    (@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),
 529    (@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),
 530    (@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),
 531    (@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),
 532    (@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),
 533
 534 -- Financial Item Status
 535    (@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),
 536    (@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),
 537    (@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);
 538
 539 -- Data migration from civicrm_contibution_type to civicrm_financial_account, civicrm_financial_type, civicrm_entity_financial_account
 540 SELECT @opval := value FROM civicrm_option_value WHERE name = 'Revenue' and option_group_id = @option_group_id_fat;
 541 SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID};
 542
 543 INSERT INTO `civicrm_financial_account`
 544   (`id`, `name`, `description`, `is_deductible`, `is_reserved`, `is_active`, `financial_account_type_id`, `contact_id`, accounting_code)
 545   SELECT id, name, CONCAT('Default account for ', name), is_deductible, is_reserved, is_active, @opval, @domainContactId, accounting_code
 546   FROM `civicrm_financial_type`;
 547
 548 -- CRM-9306 and CRM-11657
 549 UPDATE `civicrm_financial_account` SET `is_default` = 0, `account_type_code` = 'INC';
 550
 551 SELECT @option_value_rel_id  := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Income Account is';
 552 SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat;
 553 SELECT @opAsset := value FROM civicrm_option_value WHERE name = 'Asset' and option_group_id = @option_group_id_fat;
 554 SELECT @opLiability := value FROM civicrm_option_value WHERE name = 'Liability' and option_group_id = @option_group_id_fat;
 555 SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat;
 556
 557 -- CRM-11522 drop accounting_code after coping its values into financial_account
 558 ALTER TABLE civicrm_financial_type DROP accounting_code;
 559
 560 INSERT INTO
 561    `civicrm_financial_account` (`name`, `contact_id`, `financial_account_type_id`, `description`, `accounting_code`, `account_type_code`, `is_reserved`, `is_active`, `is_deductible`, `is_default`)
 562 VALUES
 563   ('{ts escape="sql"}Banking Fees{/ts}'         , @domainContactId, @opexp, 'Payment processor fees and manually recorded banking fees', '5200', 'EXP', 0, 1, 0, 0),
 564   ('{ts escape="sql"}Deposit Bank Account{/ts}' , @domainContactId, @opAsset, 'All manually recorded cash and cheques go to this account', '1100', 'BANK', 0, 1, 0, 1),
 565   ('{ts escape="sql"}Accounts Receivable{/ts}'  , @domainContactId, @opAsset, 'Amounts to be received later (eg pay later event revenues)', '1200', 'AR', 0, 1, 0, 0),
 566   ('{ts escape="sql"}Accounts Payable{/ts}'     , @domainContactId, @opLiability, 'Amounts to be paid out such as grants and refunds', '2200', 'AP', 0, 1, 0, 0),
 567   ('{ts escape="sql"}Premiums{/ts}'             , @domainContactId, @opCost, 'Account to record cost of premiums provided to payors', '5100', 'COGS', 0, 1, 0, 0),
 568   ('{ts escape="sql"}Premiums Inventory{/ts}'   , @domainContactId, @opAsset, 'Account representing value of premiums inventory', '1375', 'OCASSET', 0, 1, 0, 0),
 569   ('{ts escape="sql"}Discounts{/ts}'            , @domainContactId, @opval, 'Contra-revenue account for amounts discounted from sales', '4900', 'INC', 0, 1, 0, 0),
 570   ('{ts escape="sql"}Payment Processor Account{/ts}', @domainContactId, @opAsset, 'Account to record payments into a payment processor merchant account', '1150', 'BANK', 0, 1, 0, 0);
 571
 572 -- CRM-10926
 573 SELECT @option_value_rel_id_exp  := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Expense Account is';
 574 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';
 575 SELECT @option_value_rel_id_as  := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Asset Account is';
 576
 577 SELECT @financial_account_id_bf         := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Banking Fees';
 578 SELECT @financial_account_id_ap         := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Accounts Receivable';
 579
 580 INSERT INTO `civicrm_entity_financial_account`
 581      ( entity_table, entity_id, account_relationship, financial_account_id )
 582 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id, fa.id
 583 FROM `civicrm_financial_type` as ft LEFT JOIN `civicrm_financial_account` as fa ON ft.id = fa.id;
 584
 585 -- Banking Fees
 586 INSERT INTO `civicrm_entity_financial_account`
 587      ( entity_table, entity_id, account_relationship, financial_account_id )
 588 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_exp,  @financial_account_id_bf
 589 FROM `civicrm_financial_type` as ft;
 590
 591 -- Accounts Receivable
 592 INSERT INTO `civicrm_entity_financial_account`
 593      ( entity_table, entity_id, account_relationship, financial_account_id )
 594 SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_ar, @financial_account_id_ap
 595 FROM `civicrm_financial_type` as ft;
 596
 597 -- CRM-11516
 598 SELECT @financial_account_id_ar := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Deposit Bank Account';
 599 SELECT @financial_account_id_pp := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
 600
 601 INSERT INTO  civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
 602 SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_ar  FROM `civicrm_option_group` cog
 603 LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
 604 WHERE cog.name = 'payment_instrument' AND cov.name NOT IN ('Credit Card', 'Debit Card');
 605
 606 INSERT INTO  civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
 607 SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_pp  FROM `civicrm_option_group` cog
 608 LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
 609 WHERE cog.name = 'payment_instrument' AND cov.name IN ('Credit Card', 'Debit Card');
 610
 611
 612 -- CRM-11515
 613 SELECT @financial_account_id_ppa := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
 614
 615 INSERT INTO civicrm_entity_financial_account (`entity_table`, `entity_id`, `account_relationship`, `financial_account_id`)
 616 SELECT 'civicrm_payment_processor', id, @option_value_rel_id_as, @financial_account_id_ppa FROM `civicrm_payment_processor`;
 617
 618 -- CRM-9923 and CRM-11037
 619 SELECT @option_group_id_batch_status   := max(id) from civicrm_option_group where name = 'batch_status';
 620
 621 SELECT @weight                 := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
 622
 623 INSERT INTO
 624    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
 625 VALUES
 626    (@option_group_id_batch_status, {localize}'Data Entry'{/localize}, @weight = @weight + 1, 'Data Entry', NULL, 0, 0, @weight = @weight + 1),
 627    (@option_group_id_batch_status, {localize}'Reopened'{/localize}, @weight = @weight + 1, 'Reopened', NULL, 0, 0, @weight = @weight + 1),
 628    (@option_group_id_batch_status, {localize}'Exported'{/localize}, @weight = @weight + 1, 'Exported' , NULL, 0, 0, @weight = @weight + 1);
 629
 630 -- Insert Batch Modes.
 631
 632 SELECT @option_group_id_batch_modes   := max(id) from civicrm_option_group where name = 'batch_mode';
 633 SELECT @weight := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
 634 INSERT INTO
 635    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
 636 VALUES
 637    (@option_group_id_batch_modes, {localize}'Manual Batch'{/localize}, @weight = @weight + 1, 'Manual Batch', NULL, 0, 0, @weight = @weight + 1),
 638    (@option_group_id_batch_modes, {localize}'Automatic Batch'{/localize}, @weight = @weight + 1, 'Automatic Batch' , NULL, 0, 0, @weight = @weight + 1);
 639
 640 -- End of civiaccounts upgrade
 641
 642 -- CRM-10933
 643 ALTER TABLE `civicrm_report_instance`
 644 ADD COLUMN  `drilldown_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to instance ID drilldown to',
 645 ADD CONSTRAINT `FK_civicrm_report_instance_drilldown_id` FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance` (`id`) ON DELETE SET NULL;
 646
 647 -- CRM-10012
 648 ALTER TABLE `civicrm_membership_type`
 649 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `relationship_direction`;
 650 ALTER TABLE `civicrm_membership`
 651 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships (membership_type override).' AFTER `owner_membership_id`;
 652 ALTER TABLE `civicrm_membership_log`
 653 ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `membership_type_id`;
 654
 655 -- CRM-11358
 656 INSERT INTO `civicrm_dashboard`
 657 (`domain_id`, {localize field='label'}`label`{/localize}, `url`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `fullscreen_url`, `is_fullscreen`, `is_reserved`)
 658 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
 659 FROM `civicrm_domain`;
 660
 661 INSERT INTO `civicrm_dashboard_contact` (dashboard_id, contact_id, column_no, is_active)
 662 SELECT (SELECT MAX(id) FROM `civicrm_dashboard`), contact_id, 1, IF (SUM(is_active) > 0, 0, 1)
 663 FROM `civicrm_dashboard_contact` WHERE 1 GROUP BY contact_id;
 664
 665 -- CRM-11387
 666 ALTER TABLE `civicrm_event`
 667   ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
 668   ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
 669
 670 {if $multilingual}
 671   {foreach from=$locales item=loc}
 672     ALTER TABLE `civicrm_event`
 673       ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
 674       ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
 675   {/foreach}
 676 {else}
 677   ALTER TABLE `civicrm_event`
 678     ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
 679     ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
 680 {/if}
 681
 682 -- CRM-11347
 683 UPDATE `civicrm_option_value` SET is_reserved = 0
 684 WHERE name = 'Urgent' AND option_group_id = (SELECT id FROM `civicrm_option_group` WHERE name = 'case_status');
 685
 686 -- CRM-11400
 687 UPDATE `civicrm_state_province` SET name = 'Distrito Federal' WHERE name = 'Diatrito Federal';
 688
 689 -- CRM-9379 and CRM-11539
 690 SELECT @option_group_id_act := MAX(id) FROM civicrm_option_group WHERE name = 'activity_type';
 691 SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_act;
 692 SELECT @max_wt     := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 693 SELECT @CompId     := MAX(id)     FROM civicrm_component where name = 'CiviContribute';
 694
 695 INSERT INTO civicrm_option_value
 696   (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, is_reserved, component_id, filter)
 697 VALUES
 698  (@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),
 699  (@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),
 700  (@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);
 701
 702 -- CRM-11341
 703 INSERT INTO
 704   `civicrm_job` (domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active)
 705 SELECT
 706   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
 707 FROM `civicrm_domain`;
 708
 709 -- CRM-11367
 710 SELECT @country_id   := max(id) from civicrm_country where name = 'Latvia';
 711
 712 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');
 713
 714 INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES
 715 (@country_id, '002', 'Aizkraukles novads'),
 716 (@country_id, '038', 'Jaunjelgavas novads'),
 717 (@country_id, '072', 'PÄ¼aviÅ†u novads'),
 718 (@country_id, '046', 'Kokneses novads'),
 719 (@country_id, '065', 'Neretas novads'),
 720 (@country_id, '092', 'SkrÄ«veru novads'),
 721 (@country_id, '007', 'AlÅ«ksnes novads'),
 722 (@country_id, '009', 'Apes novads'),
 723 (@country_id, '015', 'Balvu novads'),
 724 (@country_id, '108', 'ViÄ¼akas novads'),
 725 (@country_id, '014', 'Baltinavas novads'),
 726 (@country_id, '082', 'RugÄ�ju novads'),
 727 (@country_id, '016', 'Bauskas novads'),
 728 (@country_id, '034', 'Iecavas novads'),
 729 (@country_id, '083', 'RundÄ�les novads'),
 730 (@country_id, '105', 'Vecumnieku novads'),
 731 (@country_id, '022', 'CÄ“su novads'),
 732 (@country_id, '055', 'LÄ«gatnes novads'),
 733 (@country_id, '008', 'Amatas novads'),
 734 (@country_id, '039', 'Jaunpiebalgas novads'),
 735 (@country_id, '075', 'PriekuÄ¼u novads'),
 736 (@country_id, '070', 'PÄ�rgaujas novads'),
 737 (@country_id, '076', 'Raunas novads'),
 738 (@country_id, '104', 'Vecpiebalgas novads'),
 739 (@country_id, '025', 'Daugavpils novads'),
 740 (@country_id, '036', 'IlÅ«kstes novads'),
 741 (@country_id, '026', 'Dobeles novads'),
 742 (@country_id, '010', 'Auces novads'),
 743 (@country_id, '098', 'TÄ“rvetes novads'),
 744 (@country_id, '033', 'Gulbenes novads'),
 745 (@country_id, '041', 'Jelgavas novads'),
 746 (@country_id, '069', 'Ozolnieku novads'),
 747 (@country_id, '042', 'JÄ“kabpils novads'),
 748 (@country_id, '004', 'AknÄ«stes novads'),
 749 (@country_id, '107', 'ViesÄ«tes novads'),
 750 (@country_id, '049', 'Krustpils novads'),
 751 (@country_id, '085', 'Salas novads'),
 752 (@country_id, '047', 'KrÄ�slavas novads'),
 753 (@country_id, '024', 'Dagdas novads'),
 754 (@country_id, '001', 'Aglonas novads'),
 755 (@country_id, '050', 'KuldÄ«gas novads'),
 756 (@country_id, '093', 'Skrundas novads'),
 757 (@country_id, '006', 'Alsungas novads'),
 758 (@country_id, '003', 'Aizputes novads'),
 759 (@country_id, '028', 'Durbes novads'),
 760 (@country_id, '032', 'GrobiÅ†as novads'),
 761 (@country_id, '071', 'PÄ�vilostas novads'),
 762 (@country_id, '074', 'Priekules novads'),
 763 (@country_id, '066', 'NÄ«cas novads'),
 764 (@country_id, '081', 'Rucavas novads'),
 765 (@country_id, '100', 'VaiÅ†odes novads'),
 766 (@country_id, '054', 'LimbaÅ¾u novads'),
 767 (@country_id, '005', 'Alojas novads'),
 768 (@country_id, '086', 'SalacgrÄ«vas novads'),
 769 (@country_id, '058', 'Ludzas novads'),
 770 (@country_id, '044', 'KÄ�rsavas novads'),
 771 (@country_id, '110', 'Zilupes novads'),
 772 (@country_id, '023', 'Ciblas novads'),
 773 (@country_id, '059', 'Madonas novads'),
 774 (@country_id, '021', 'Cesvaines novads'),
 775 (@country_id, '057', 'LubÄ�nas novads'),
 776 (@country_id, '102', 'VarakÄ¼Ä�nu novads'),
 777 (@country_id, '030', 'Ä’rgÄ¼u novads'),
 778 (@country_id, '067', 'Ogres novads'),
 779 (@country_id, '035', 'IkÅ¡Ä·iles novads'),
 780 (@country_id, '051', 'Ä¶eguma novads'),
 781 (@country_id, '053', 'LielvÄ�rdes novads'),
 782 (@country_id, '073', 'PreiÄ¼u novads'),
 783 (@country_id, '056', 'LÄ«vÄ�nu novads'),
 784 (@country_id, '078', 'RiebiÅ†u novads'),
 785 (@country_id, '103', 'VÄ�rkavas novads'),
 786 (@country_id, '077', 'RÄ“zeknes novads'),
 787 (@country_id, '109', 'ViÄ¼Ä�nu novads'),
 788 (@country_id, '013', 'Baldones novads'),
 789 (@country_id, '052', 'Ä¶ekavas novads'),
 790 (@country_id, '068', 'Olaines novads'),
 791 (@country_id, '087', 'Salaspils novads'),
 792 (@country_id, '089', 'Saulkrastu novads'),
 793 (@country_id, '091', 'Siguldas novads'),
 794 (@country_id, '037', 'InÄ�ukalna novads'),
 795 (@country_id, '011', 'Ä€daÅ¾u novads'),
 796 (@country_id, '012', 'BabÄ«tes novads'),
 797 (@country_id, '020', 'Carnikavas novads'),
 798 (@country_id, '031', 'Garkalnes novads'),
 799 (@country_id, '048', 'Krimuldas novads'),
 800 (@country_id, '061', 'MÄ�lpils novads'),
 801 (@country_id, '062', 'MÄ�rupes novads'),
 802 (@country_id, '080', 'RopaÅ¾u novads'),
 803 (@country_id, '090', 'SÄ“jas novads'),
 804 (@country_id, '095', 'StopiÅ†u novads'),
 805 (@country_id, '088', 'Saldus novads'),
 806 (@country_id, '018', 'BrocÄ“nu novads'),
 807 (@country_id, '097', 'Talsu novads'),
 808 (@country_id, '027', 'Dundagas novads'),
 809 (@country_id, '063', 'MÄ“rsraga novads'),
 810 (@country_id, '079', 'Rojas novads'),
 811 (@country_id, '099', 'Tukuma novads'),
 812 (@country_id, '043', 'Kandavas novads'),
 813 (@country_id, '029', 'Engures novads'),
 814 (@country_id, '040', 'Jaunpils novads'),
 815 (@country_id, '101', 'Valkas novads'),
 816 (@country_id, '094', 'Smiltenes novads'),
 817 (@country_id, '096', 'StrenÄ�u novads'),
 818 (@country_id, '045', 'KocÄ“nu novads'),
 819 (@country_id, '060', 'Mazsalacas novads'),
 820 (@country_id, '084', 'RÅ«jienas novads'),
 821 (@country_id, '017', 'BeverÄ«nas novads'),
 822 (@country_id, '019', 'Burtnieku novads'),
 823 (@country_id, '064', 'NaukÅ¡Ä“nu novads'),
 824 (@country_id, '106', 'Ventspils novads'),
 825 (@country_id, 'JKB', 'JÄ“kabpils'),
 826 (@country_id, 'VMR', 'Valmiera');
 827
 828 -- CRM-11507
 829 ALTER TABLE `civicrm_batch` CHANGE `type_id` `type_id` INT( 10 ) UNSIGNED NULL COMMENT 'fk to Batch Type options in civicrm_option_values';
 830 UPDATE `civicrm_batch` SET `mode_id` = '1';
 831
 832 -- add Refunded in contribution status
 833 SELECT @option_group_id_cs := MAX(id) FROM civicrm_option_group WHERE name = 'contribution_status';
 834
 835 SELECT @max_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 836
 837 INSERT INTO
 838   `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`)
 839 VALUES
 840   (@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);
 841
 842 -- Payprocs from extensions may have long titles
 843 ALTER TABLE civicrm_payment_processor_type MODIFY COLUMN title varchar(127);
 844
 845 -- CRM-11665
 846 ALTER TABLE `civicrm_address`
 847   ADD COLUMN manual_geo_code tinyint(4) DEFAULT '0' COMMENT 'Is this a manually entered geo code.';
 848
 849 -- CRM-11761
 850 UPDATE `civicrm_setting` SET `group_name` = 'Personal Preferences' WHERE `group_name` = 'Navigation Menu';
 851
 852 -- CRM-11779
 853
 854 INSERT INTO civicrm_action_mapping ( entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient )
 855 VALUES
 856 ( 'civicrm_participant', 'event_template', 'Event Template', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
 857
 858 -- CRM-11802 Fix ON DELETE CASCADE constraint for line_item.price_field_id
 859 ALTER TABLE `civicrm_line_item`
 860   DROP FOREIGN KEY `FK_civicrm_line_item_price_field_id`,
 861   CHANGE `price_field_id` `price_field_id` INT( 10 ) UNSIGNED DEFAULT NULL;
 862
 863 ALTER TABLE `civicrm_line_item`
 864   ADD CONSTRAINT `FK_civicrm_line_item_price_field_id` FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(id) ON DELETE SET NULL;
 865
 866 -- CRM-11821
 867 -- update all location info of domain
 868 -- like address, email, phone etc.
 869 UPDATE civicrm_domain cd
 870 LEFT JOIN civicrm_loc_block clb ON cd.loc_block_id = clb.id
 871 LEFT JOIN civicrm_address ca ON clb.address_id = ca.id
 872 LEFT JOIN civicrm_phone cp ON cp.id = clb.phone_id
 873 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id
 874 SET
 875 ca.contact_id = cd.contact_id, cp.contact_id = cd.contact_id, ce.contact_id = cd.contact_id;
 876
 877 -- Delete rows from civicrm_loc_block used for civicrm_domain
 878 DELETE clb.* FROM civicrm_domain cd
 879 LEFT JOIN civicrm_loc_block clb ON clb.id = cd.loc_block_id;
 880
 881 -- Delete loc_block_id from civicrm_domain
 882 ALTER TABLE `civicrm_domain` DROP loc_block_id;
 883
 884 -- CRM11818
 885 -- pledge payments should not be cancelled if the contribution was
 886 -- compledged but the pledge is cancelled
 887 UPDATE
 888 civicrm_pledge_payment pp
 889 INNER JOIN civicrm_contribution c ON
 890 c.id = pp.contribution_id AND pp.status_id =3
 891 AND contribution_status_id = 1
 892 SET pp.status_id = contribution_status_id
 893