877 lines
53 KiB
Smarty
877 lines
53 KiB
Smarty
|
{include file='../CRM/Upgrade/4.3.alpha1.msg_template/civicrm_msg_template.tpl'}
|
|||
|
|
|
|||
|
|
-- CRM-10999
|
|||
|
|
ALTER TABLE `civicrm_premiums`
|
|||
|
|
ADD COLUMN `premiums_nothankyou_position` int(10) unsigned DEFAULT '1';
|
|||
|
|
|
|||
|
|
-- CRM-11514 if contribution type name is null, assign it a name
|
|||
|
|
UPDATE civicrm_contribution_type
|
|||
|
|
SET name = CONCAT('Unknown_', id)
|
|||
|
|
WHERE name IS NULL OR TRIM(name) = '';
|
|||
|
|
|
|||
|
|
-- CRM-8507
|
|||
|
|
ALTER TABLE civicrm_custom_field
|
|||
|
|
ADD UNIQUE INDEX `UI_name_custom_group_id` (`name`, `custom_group_id`);
|
|||
|
|
|
|||
|
|
--CRM-10473 Added Missing Provinces of Ningxia Autonomous Region of China
|
|||
|
|
INSERT INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES
|
|||
|
|
(1045, 'YN', 'Yinchuan'),
|
|||
|
|
(1045, 'SZ', 'Shizuishan'),
|
|||
|
|
(1045, 'WZ', 'Wuzhong'),
|
|||
|
|
(1045, 'GY', 'Guyuan'),
|
|||
|
|
(1045, 'ZW', 'Zhongwei');
|
|||
|
|
|
|||
|
|
-- CRM-10553
|
|||
|
|
ALTER TABLE civicrm_contact
|
|||
|
|
ADD COLUMN `created_date` timestamp NULL DEFAULT NULL
|
|||
|
|
COMMENT 'When was the contact was created.';
|
|||
|
|
ALTER TABLE civicrm_contact
|
|||
|
|
ADD COLUMN `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|||
|
|
COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.';
|
|||
|
|
|
|||
|
|
-- CRM-10296
|
|||
|
|
DELETE FROM civicrm_job WHERE `api_action` = 'process_membership_reminder_date';
|
|||
|
|
ALTER TABLE civicrm_membership DROP COLUMN reminder_date;
|
|||
|
|
ALTER TABLE civicrm_membership_log DROP COLUMN renewal_reminder_date;
|
|||
|
|
ALTER TABLE civicrm_membership_type
|
|||
|
|
DROP COLUMN renewal_reminder_day,
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_membership_type_renewal_msg_id,
|
|||
|
|
DROP INDEX FK_civicrm_membership_type_renewal_msg_id,
|
|||
|
|
DROP COLUMN renewal_msg_id,
|
|||
|
|
DROP COLUMN autorenewal_msg_id;
|
|||
|
|
|
|||
|
|
-- CRM-10738
|
|||
|
|
ALTER TABLE civicrm_msg_template
|
|||
|
|
CHANGE msg_text msg_text LONGTEXT NULL COMMENT 'Text formatted message',
|
|||
|
|
CHANGE msg_html msg_html LONGTEXT NULL COMMENT 'HTML formatted message';
|
|||
|
|
|
|||
|
|
-- CRM-10860
|
|||
|
|
ALTER TABLE civicrm_contribution_page ADD COLUMN is_recur_installments tinyint(4) DEFAULT '0';
|
|||
|
|
UPDATE civicrm_contribution_page SET is_recur_installments='1';
|
|||
|
|
|
|||
|
|
-- CRM-10863
|
|||
|
|
SELECT @country_id := id from civicrm_country where name = 'Luxembourg' AND iso_code = 'LU';
|
|||
|
|
INSERT IGNORE INTO `civicrm_state_province`(`country_id`, `abbreviation`, `name`) VALUES
|
|||
|
|
(@country_id, 'L', 'Luxembourg');
|
|||
|
|
|
|||
|
|
-- CRM-10899 and CRM-10999
|
|||
|
|
{if $multilingual}
|
|||
|
|
{foreach from=$locales item=locale}
|
|||
|
|
UPDATE civicrm_option_group SET title_{$locale} = '{ts escape="sql"}Currencies Enabled{/ts}' WHERE name = "currencies_enabled";
|
|||
|
|
ALTER TABLE `civicrm_premiums`
|
|||
|
|
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)';
|
|||
|
|
{/foreach}
|
|||
|
|
{else}
|
|||
|
|
UPDATE civicrm_option_group SET title = '{ts escape="sql"}Currencies Enabled{/ts}' WHERE name = "currencies_enabled";
|
|||
|
|
{/if}
|
|||
|
|
|
|||
|
|
-- CRM-11047
|
|||
|
|
ALTER TABLE civicrm_job DROP COLUMN api_prefix;
|
|||
|
|
|
|||
|
|
-- CRM-11068, CRM-10678, CRM-11759
|
|||
|
|
ALTER TABLE civicrm_group
|
|||
|
|
ADD refresh_date datetime default NULL COMMENT 'Date and time when we need to refresh the cache next.' AFTER `cache_date`,
|
|||
|
|
ADD COLUMN `created_id` INT(10) unsigned DEFAULT NULL COMMENT 'FK to contact table, creator of the group.';
|
|||
|
|
|
|||
|
|
-- CRM-11759
|
|||
|
|
ALTER TABLE civicrm_group
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
INSERT INTO `civicrm_job`
|
|||
|
|
( domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active )
|
|||
|
|
VALUES
|
|||
|
|
( {$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),
|
|||
|
|
( {$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);
|
|||
|
|
|
|||
|
|
-- CRM-11117
|
|||
|
|
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');
|
|||
|
|
|
|||
|
|
-- CRM-10885
|
|||
|
|
ALTER TABLE civicrm_dedupe_rule_group
|
|||
|
|
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;
|
|||
|
|
|
|||
|
|
UPDATE civicrm_dedupe_rule_group
|
|||
|
|
SET used = 'General' WHERE is_default = 0;
|
|||
|
|
|
|||
|
|
UPDATE civicrm_dedupe_rule_group
|
|||
|
|
SET used = CASE level
|
|||
|
|
WHEN 'Fuzzy' THEN 'Supervised'
|
|||
|
|
WHEN 'Strict' THEN 'Unsupervised'
|
|||
|
|
END
|
|||
|
|
WHERE is_default = 1;
|
|||
|
|
|
|||
|
|
UPDATE civicrm_dedupe_rule_group
|
|||
|
|
SET name = CONCAT_WS('', `contact_type`, `used`)
|
|||
|
|
WHERE is_default = 1 OR is_reserved = 1;
|
|||
|
|
|
|||
|
|
UPDATE civicrm_dedupe_rule_group
|
|||
|
|
SET title = 'Name and Email'
|
|||
|
|
WHERE contact_type IN ('Organization', 'Household') AND used IN ('Unsupervised', 'Supervised');
|
|||
|
|
|
|||
|
|
UPDATE civicrm_dedupe_rule_group
|
|||
|
|
SET title = CASE used
|
|||
|
|
WHEN 'Supervised' THEN 'Name and Email (reserved)'
|
|||
|
|
WHEN 'Unsupervised' THEN 'Email (reserved)'
|
|||
|
|
WHEN 'General' THEN 'Name and Address (reserved)'
|
|||
|
|
END
|
|||
|
|
WHERE contact_type = 'Individual' AND is_reserved = 1;
|
|||
|
|
|
|||
|
|
ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN level;
|
|||
|
|
|
|||
|
|
-- CRM-10771
|
|||
|
|
ALTER TABLE civicrm_uf_field
|
|||
|
|
ADD `is_multi_summary` tinyint(4) DEFAULT '0' COMMENT 'Include in multi-record listing?';
|
|||
|
|
|
|||
|
|
-- CRM-1115
|
|||
|
|
-- note that country names are not translated in the DB
|
|||
|
|
SELECT @region_id := max(id) from civicrm_worldregion where name = "Europe and Central Asia";
|
|||
|
|
INSERT IGNORE INTO civicrm_country (name,iso_code,region_id,is_province_abbreviated) VALUES("Kosovo", "XK", @region_id, 0);
|
|||
|
|
|
|||
|
|
UPDATE civicrm_country SET name = 'Libya' WHERE name LIKE 'Libyan%';
|
|||
|
|
UPDATE civicrm_country SET name = 'Congo, Republic of the' WHERE name = 'Congo';
|
|||
|
|
|
|||
|
|
-- CRM-10621 Add component report links to reports menu for upgrade
|
|||
|
|
SELECT @reportlastID := MAX(id) FROM civicrm_navigation where name = 'Reports' AND domain_id = {$domainID};
|
|||
|
|
SELECT @max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @reportlastID;
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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) );
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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) );
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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) );
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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));
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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));
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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));
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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));
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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) );
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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) );
|
|||
|
|
|
|||
|
|
-- CRM-11148 Multiple terms membership signup and renewal via price set
|
|||
|
|
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`;
|
|||
|
|
|
|||
|
|
-- CRM-11070
|
|||
|
|
SELECT @option_group_id_tuf := max(id) from civicrm_option_group where name = 'tag_used_for';
|
|||
|
|
SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_tuf;
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
|
|||
|
|
VALUES
|
|||
|
|
(@option_group_id_tuf, {localize}'Attachments'{/localize}, 'civicrm_file', 'Attachments', NULL, 0, 0, @weight = @weight + 1);
|
|||
|
|
|
|||
|
|
ALTER TABLE civicrm_extension MODIFY COLUMN type ENUM( 'payment', 'search', 'report', 'module','sms') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ;
|
|||
|
|
|
|||
|
|
-- CRM-9914
|
|||
|
|
SELECT @option_group_id_sms_provider_name := max(id) from civicrm_option_group where name = 'sms_provider_name';
|
|||
|
|
DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_sms_provider_name AND name = 'Clickatell';
|
|||
|
|
|
|||
|
|
-- CRM-11292
|
|||
|
|
ALTER TABLE `civicrm_phone`
|
|||
|
|
ADD `phone_numeric` varchar(32)
|
|||
|
|
COMMENT 'Phone number stripped of all whitespace, letters, and punctuation.'
|
|||
|
|
AFTER `phone_ext`,
|
|||
|
|
ADD INDEX phone_numeric_index(`phone_numeric`);
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- civiaccounts upgrade
|
|||
|
|
|
|||
|
|
-- ADD fields w.r.t 10.6 mwb
|
|||
|
|
ALTER TABLE `civicrm_financial_account`
|
|||
|
|
CHANGE `account_type_id` financial_account_type_id int(10) unsigned NOT NULL DEFAULT '3' COMMENT 'Version identifier of financial_type',
|
|||
|
|
ADD `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Financial Type Description.',
|
|||
|
|
ADD `parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent ID in account hierarchy',
|
|||
|
|
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?',
|
|||
|
|
ADD `accounting_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
|
|||
|
|
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).',
|
|||
|
|
ADD `is_deductible` tinyint(4) DEFAULT '1' COMMENT 'Is this account tax-deductible?',
|
|||
|
|
ADD `is_tax` tinyint(4) DEFAULT '0' COMMENT 'Is this account for taxes?',
|
|||
|
|
ADD `tax_rate` decimal(9,8) DEFAULT '0.00' COMMENT 'The percentage of the total_amount that is due for this tax.',
|
|||
|
|
ADD `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this a predefined system object?',
|
|||
|
|
ADD `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this property active?',
|
|||
|
|
ADD `is_default` tinyint(4) DEFAULT NULL COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?',
|
|||
|
|
ADD CONSTRAINT `UI_name` UNIQUE INDEX (`name`),
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_financial_account_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(id);
|
|||
|
|
|
|||
|
|
-- CRM-8425
|
|||
|
|
-- Rename table civicrm_contribution_type to civicrm_financial_type
|
|||
|
|
RENAME TABLE `civicrm_contribution_type` TO `civicrm_financial_type`;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_financial_type`
|
|||
|
|
CHANGE `name` `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Financial Type Name.',
|
|||
|
|
ADD CONSTRAINT `UI_id` UNIQUE INDEX(id),
|
|||
|
|
DROP INDEX UI_name;
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS `civicrm_entity_financial_account` (
|
|||
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|||
|
|
`entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Links to an entity_table like civicrm_financial_type',
|
|||
|
|
`entity_id` int(10) unsigned NOT NULL COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
|
|||
|
|
`account_relationship` int(10) unsigned NOT NULL COMMENT 'FK to a new civicrm_option_value (account_relationship)',
|
|||
|
|
`financial_account_id` int(10) unsigned NOT NULL COMMENT 'FK to the financial_account_id',
|
|||
|
|
PRIMARY KEY (`id`),
|
|||
|
|
KEY `FK_civicrm_entity_financial_account_financial_account_id` (`financial_account_id`)
|
|||
|
|
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|||
|
|
|
|||
|
|
-- Constraints for table `civicrm_entity_financial_account`
|
|||
|
|
ALTER TABLE `civicrm_entity_financial_account`
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_entity_financial_account_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);
|
|||
|
|
|
|||
|
|
-- CRM-9730 Table structure for table `civicrm_financial_item`
|
|||
|
|
--
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS `civicrm_financial_item` (
|
|||
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|||
|
|
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the item was created',
|
|||
|
|
`transaction_date` datetime NOT NULL COMMENT 'Date and time of the source transaction',
|
|||
|
|
`contact_id` int(10) unsigned NOT NULL COMMENT 'FK to Contact ID of contact the item is from',
|
|||
|
|
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Human readable description of this item, to ease display without lookup of source item.',
|
|||
|
|
`amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT 'Total amount of this item',
|
|||
|
|
`currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Currency for the amount',
|
|||
|
|
`financial_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_financial_account',
|
|||
|
|
`status_id` int(10) unsigned DEFAULT NULL COMMENT 'Payment status: test, paid, part_paid, unpaid (if empty assume unpaid)',
|
|||
|
|
`entity_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The table providing the source of this item such as civicrm_line_item',
|
|||
|
|
`entity_id` int(10) unsigned DEFAULT NULL COMMENT 'The specific source item that is responsible for the creation of this financial_item',
|
|||
|
|
PRIMARY KEY (`id`),
|
|||
|
|
UNIQUE KEY `UI_id` (`id`),
|
|||
|
|
KEY `IX_created_date` (`created_date`),
|
|||
|
|
KEY `IX_transaction_date` (`transaction_date`),
|
|||
|
|
KEY `IX_entity` (`entity_table`,`entity_id`),
|
|||
|
|
KEY `FK_civicrm_financial_item_contact_id` (`contact_id`),
|
|||
|
|
KEY `FK_civicrm_financial_item_financial_account_id` (`financial_account_id`)
|
|||
|
|
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_batch`
|
|||
|
|
ADD `payment_instrument_id` int(10) unsigned DEFAULT NULL COMMENT 'fk to Payment Instrument options in civicrm_option_values',
|
|||
|
|
ADD `exported_date` datetime DEFAULT NULL;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_financial_item`
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`),
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_financial_item_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_entity_financial_trxn`
|
|||
|
|
DROP currency;
|
|||
|
|
|
|||
|
|
-- CRM-12312
|
|||
|
|
UPDATE civicrm_event SET contribution_type_id = NULL WHERE contribution_type_id = 0;
|
|||
|
|
|
|||
|
|
-- 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
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_pledge`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id,
|
|||
|
|
DROP INDEX FK_civicrm_pledge_contribution_type_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_pledge`
|
|||
|
|
CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_pledge`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_pledge_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_membership_type`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_membership_type_contribution_type_id,
|
|||
|
|
DROP INDEX FK_civicrm_membership_type_contribution_type_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_membership_type`
|
|||
|
|
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';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_membership_type`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_membership_type_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_price_set`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_price_set_contribution_type_id,
|
|||
|
|
DROP INDEX FK_civicrm_price_set_contribution_type_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_price_set`
|
|||
|
|
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';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_price_set`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_price_set_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_event`
|
|||
|
|
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.';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_contribution_contribution_type_id,
|
|||
|
|
DROP INDEX FK_civicrm_contribution_contribution_type_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution`
|
|||
|
|
CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_page`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_contribution_page_contribution_type_id,
|
|||
|
|
DROP INDEX FK_civicrm_contribution_page_contribution_type_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_page`
|
|||
|
|
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',
|
|||
|
|
ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
|
|||
|
|
ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
|
|||
|
|
|
|||
|
|
{if $multilingual}
|
|||
|
|
{foreach from=$locales item=loc}
|
|||
|
|
ALTER TABLE `civicrm_contribution_page`
|
|||
|
|
ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
|
|||
|
|
ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
|
|||
|
|
{/foreach}
|
|||
|
|
{else}
|
|||
|
|
ALTER TABLE `civicrm_contribution_page`
|
|||
|
|
ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
|
|||
|
|
ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
|
|||
|
|
{/if}
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_page`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_contribution_page_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_recur`
|
|||
|
|
CHANGE `contribution_type_id` `financial_type_id` int unsigned COMMENT 'FK to Financial Type';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_recur`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_contribution_recur_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES civicrm_financial_type (id);
|
|||
|
|
|
|||
|
|
-- CRM-9083
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` CHANGE `to_account_id` `to_financial_account_id` int unsigned COMMENT 'FK to financial_financial_account table.',
|
|||
|
|
CHANGE `from_account_id` `from_financial_account_id` int unsigned COMMENT 'FK to financial_account table.',
|
|||
|
|
ADD `status_id` int(10) unsigned DEFAULT NULL,
|
|||
|
|
CHANGE `trxn_id` trxn_id varchar(255) COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
|
|||
|
|
CHANGE `trxn_date` trxn_date datetime DEFAULT NULL,
|
|||
|
|
ADD `payment_instrument_id` int unsigned DEFAULT NULL COMMENT 'FK to payment_instrument option group values',
|
|||
|
|
ADD `check_number` VARCHAR( 255 ) NULL DEFAULT NULL,
|
|||
|
|
ADD INDEX `UI_ftrxn_check_number` (`check_number`),
|
|||
|
|
ADD INDEX `UI_ftrxn_payment_instrument_id` (`payment_instrument_id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn`
|
|||
|
|
ADD CONSTRAINT FK_civicrm_financial_trxn_to_financial_account_id FOREIGN KEY (`to_financial_account_id`) REFERENCES civicrm_financial_account (id),
|
|||
|
|
ADD CONSTRAINT FK_civicrm_financial_trxn_from_financial_account_id FOREIGN KEY (`from_financial_account_id`) REFERENCES civicrm_financial_account (id);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` ADD `payment_processor_id` int unsigned COMMENT 'Payment Processor for this contribution Page';
|
|||
|
|
|
|||
|
|
-- Fill in the payment_processor_id based on a lookup using the payment_processor field
|
|||
|
|
UPDATE `civicrm_payment_processor` cppt, `civicrm_financial_trxn` cft
|
|||
|
|
SET cft.`payment_processor_id` = cppt.`id`
|
|||
|
|
WHERE cft.`payment_processor` = cppt.`payment_processor_type` and `is_test` = 0;
|
|||
|
|
|
|||
|
|
-- remove payment_processor field
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` DROP `payment_processor`;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn`
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_financial_trxn_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
-- Drop index for civicrm_financial_trxn.trxn_id and set default to null
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` varchar( 255 ) DEFAULT NULL ;
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` DROP INDEX UI_ft_trxn_id;
|
|||
|
|
|
|||
|
|
-- remove trxn_type field
|
|||
|
|
ALTER TABLE `civicrm_financial_trxn` DROP `trxn_type`;
|
|||
|
|
|
|||
|
|
-- CRM-9731
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_payment_processor` ADD `payment_processor_type_id` int(10) unsigned NULL AFTER `description`,
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_payment_processor_payment_processor_type_id` FOREIGN KEY (`payment_processor_type_id`) REFERENCES `civicrm_payment_processor_type` (`id`);
|
|||
|
|
|
|||
|
|
UPDATE `civicrm_payment_processor` , `civicrm_payment_processor_type`
|
|||
|
|
SET payment_processor_type_id = `civicrm_payment_processor_type`.id
|
|||
|
|
WHERE payment_processor_type = `civicrm_payment_processor_type`.name;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_payment_processor` DROP `payment_processor_type`;
|
|||
|
|
|
|||
|
|
-- CRM-9730
|
|||
|
|
ALTER TABLE `civicrm_price_field_value` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_line_item` ADD `deductible_amount` DECIMAL( 20, 2 ) NOT NULL DEFAULT '0.00' COMMENT 'Tax-deductible portion of the amount';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_price_field_value` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_price_field_value_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_line_item` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_line_item_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_grant` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_grant_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_product` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_premiums_product` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_premiums_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_contribution_product` ADD
|
|||
|
|
`financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type.',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_contribution_product_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`);
|
|||
|
|
|
|||
|
|
-- CRM-11122
|
|||
|
|
ALTER TABLE `civicrm_discount`
|
|||
|
|
DROP FOREIGN KEY FK_civicrm_discount_option_group_id,
|
|||
|
|
DROP INDEX FK_civicrm_discount_option_group_id;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_discount` CHANGE `option_group_id` `price_set_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'FK to civicrm_price_set';
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_discount`
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_discount_price_set_id` FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set` (`id`) ON DELETE CASCADE;
|
|||
|
|
|
|||
|
|
-- CRM-8425
|
|||
|
|
|
|||
|
|
UPDATE civicrm_navigation SET `label` = 'Financial Types', `name` = 'Financial Types', `url` = 'civicrm/admin/financial/financialType?reset=1' WHERE `name` = 'Contribution Types';
|
|||
|
|
|
|||
|
|
-- CRM-9199
|
|||
|
|
-- Insert menu item at Administer > CiviContribute, below the section break below Premiums (Thank-you Gifts), just below Financial Account.
|
|||
|
|
|
|||
|
|
SELECT @parent_id := id from `civicrm_navigation` where name = 'CiviContribute' AND domain_id = {$domainID};
|
|||
|
|
SELECT @add_weight_id := weight from `civicrm_navigation` where `name` = 'Financial Types' and `parent_id` = @parent_id;
|
|||
|
|
|
|||
|
|
UPDATE `civicrm_navigation`
|
|||
|
|
SET `weight` = `weight`+1
|
|||
|
|
WHERE `parent_id` = @parent_id
|
|||
|
|
AND `weight` > @add_weight_id;
|
|||
|
|
|
|||
|
|
INSERT INTO `civicrm_navigation`
|
|||
|
|
( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
( {$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 );
|
|||
|
|
|
|||
|
|
-- CRM-10944
|
|||
|
|
SELECT @contributionlastID := max(id) from civicrm_navigation where name = 'Contributions' AND domain_id = {$domainID};
|
|||
|
|
|
|||
|
|
SELECT @pledgeWeight := weight from civicrm_navigation where name = 'Pledges' and parent_id = @contributionlastID;
|
|||
|
|
|
|||
|
|
UPDATE `civicrm_navigation`
|
|||
|
|
SET `weight` = `weight`+1
|
|||
|
|
WHERE `parent_id` = @contributionlastID
|
|||
|
|
AND `weight` > @pledgeWeight;
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
(domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight)
|
|||
|
|
VALUES
|
|||
|
|
({$domainID}, NULL, '{ts escape="sql" skip="true"}Accounting Batches{/ts}', 'Accounting Batches', 'access CiviContribute', '', @contributionlastID, '1', 1, @pledgeWeight+1);
|
|||
|
|
SET @financialTransactionID:=LAST_INSERT_ID();
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_navigation
|
|||
|
|
(domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
|
|||
|
|
VALUES
|
|||
|
|
({$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),
|
|||
|
|
({$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),
|
|||
|
|
({$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),
|
|||
|
|
({$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);
|
|||
|
|
|
|||
|
|
-- 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
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_option_group` (`name`, {localize field='title'}title{/localize}, `is_reserved`, `is_active`)
|
|||
|
|
VALUES
|
|||
|
|
('financial_account_type', {localize}'{ts escape="sql"}Financial Account Type{/ts}'{/localize}, 1, 1),
|
|||
|
|
('account_relationship', {localize}'{ts escape="sql"}Account Relationship{/ts}'{/localize}, 1, 1),
|
|||
|
|
('financial_item_status', {localize}'{ts escape="sql"}Financial Item Status{/ts}'{/localize}, 1, 1),
|
|||
|
|
('batch_mode', {localize}'{ts escape="sql"}Batch Mode{/ts}'{/localize}, 1, 1);
|
|||
|
|
|
|||
|
|
SELECT @option_group_id_fat := max(id) from civicrm_option_group where name = 'financial_account_type';
|
|||
|
|
SELECT @option_group_id_arel := max(id) from civicrm_option_group where name = 'account_relationship';
|
|||
|
|
SELECT @option_group_id_financial_item_status := max(id) from civicrm_option_group where name = 'financial_item_status';
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`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`)
|
|||
|
|
VALUES
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
|
|||
|
|
-- Financial account relationship
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
|
|||
|
|
-- Financial Item Status
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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);
|
|||
|
|
|
|||
|
|
-- Data migration from civicrm_contibution_type to civicrm_financial_account, civicrm_financial_type, civicrm_entity_financial_account
|
|||
|
|
SELECT @opval := value FROM civicrm_option_value WHERE name = 'Revenue' and option_group_id = @option_group_id_fat;
|
|||
|
|
SELECT @domainContactId := contact_id from civicrm_domain where id = {$domainID};
|
|||
|
|
|
|||
|
|
INSERT INTO `civicrm_financial_account`
|
|||
|
|
(`id`, `name`, `description`, `is_deductible`, `is_reserved`, `is_active`, `financial_account_type_id`, `contact_id`, accounting_code)
|
|||
|
|
SELECT id, name, CONCAT('Default account for ', name), is_deductible, is_reserved, is_active, @opval, @domainContactId, accounting_code
|
|||
|
|
FROM `civicrm_financial_type`;
|
|||
|
|
|
|||
|
|
-- CRM-9306 and CRM-11657
|
|||
|
|
UPDATE `civicrm_financial_account` SET `is_default` = 0, `account_type_code` = 'INC';
|
|||
|
|
|
|||
|
|
SELECT @option_value_rel_id := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Income Account is';
|
|||
|
|
SELECT @opexp := value FROM civicrm_option_value WHERE name = 'Expenses' and option_group_id = @option_group_id_fat;
|
|||
|
|
SELECT @opAsset := value FROM civicrm_option_value WHERE name = 'Asset' and option_group_id = @option_group_id_fat;
|
|||
|
|
SELECT @opLiability := value FROM civicrm_option_value WHERE name = 'Liability' and option_group_id = @option_group_id_fat;
|
|||
|
|
SELECT @opCost := value FROM civicrm_option_value WHERE name = 'Cost of Sales' and option_group_id = @option_group_id_fat;
|
|||
|
|
|
|||
|
|
-- CRM-11522 drop accounting_code after coping its values into financial_account
|
|||
|
|
ALTER TABLE civicrm_financial_type DROP accounting_code;
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_financial_account` (`name`, `contact_id`, `financial_account_type_id`, `description`, `accounting_code`, `account_type_code`, `is_reserved`, `is_active`, `is_deductible`, `is_default`)
|
|||
|
|
VALUES
|
|||
|
|
('Banking Fees' , @domainContactId, @opexp, 'Payment processor fees and manually recorded banking fees', '5200', 'EXP', 0, 1, 0, 0),
|
|||
|
|
('Deposit Bank Account' , @domainContactId, @opAsset, 'All manually recorded cash and cheques go to this account', '1100', 'BANK', 0, 1, 0, 1),
|
|||
|
|
('Accounts Receivable' , @domainContactId, @opAsset, 'Amounts to be received later (eg pay later event revenues)', '1200', 'AR', 0, 1, 0, 0),
|
|||
|
|
('Accounts Payable' , @domainContactId, @opLiability, 'Amounts to be paid out such as grants and refunds', '2200', 'AP', 0, 1, 0, 0),
|
|||
|
|
('Premiums' , @domainContactId, @opCost, 'Account to record cost of premiums provided to payors', '5100', 'COGS', 0, 1, 0, 0),
|
|||
|
|
('Premiums Inventory' , @domainContactId, @opAsset, 'Account representing value of premiums inventory', '1375', 'OCASSET', 0, 1, 0, 0),
|
|||
|
|
('Discounts' , @domainContactId, @opval, 'Contra-revenue account for amounts discounted from sales', '4900', 'INC', 0, 1, 0, 0),
|
|||
|
|
('Payment Processor Account', @domainContactId, @opAsset, 'Account to record payments into a payment processor merchant account', '1150', 'BANK', 0, 1, 0, 0);
|
|||
|
|
|
|||
|
|
-- CRM-10926
|
|||
|
|
SELECT @option_value_rel_id_exp := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Expense Account is';
|
|||
|
|
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';
|
|||
|
|
SELECT @option_value_rel_id_as := value FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_arel AND `name` = 'Asset Account is';
|
|||
|
|
|
|||
|
|
SELECT @financial_account_id_bf := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Banking Fees';
|
|||
|
|
SELECT @financial_account_id_ap := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Accounts Receivable';
|
|||
|
|
|
|||
|
|
INSERT INTO `civicrm_entity_financial_account`
|
|||
|
|
( entity_table, entity_id, account_relationship, financial_account_id )
|
|||
|
|
SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id, fa.id
|
|||
|
|
FROM `civicrm_financial_type` as ft LEFT JOIN `civicrm_financial_account` as fa ON ft.id = fa.id;
|
|||
|
|
|
|||
|
|
-- Banking Fees
|
|||
|
|
INSERT INTO `civicrm_entity_financial_account`
|
|||
|
|
( entity_table, entity_id, account_relationship, financial_account_id )
|
|||
|
|
SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_exp, @financial_account_id_bf
|
|||
|
|
FROM `civicrm_financial_type` as ft;
|
|||
|
|
|
|||
|
|
-- Accounts Receivable
|
|||
|
|
INSERT INTO `civicrm_entity_financial_account`
|
|||
|
|
( entity_table, entity_id, account_relationship, financial_account_id )
|
|||
|
|
SELECT 'civicrm_financial_type', ft.id, @option_value_rel_id_ar, @financial_account_id_ap
|
|||
|
|
FROM `civicrm_financial_type` as ft;
|
|||
|
|
|
|||
|
|
-- CRM-11516
|
|||
|
|
SELECT @financial_account_id_ar := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Deposit Bank Account';
|
|||
|
|
SELECT @financial_account_id_pp := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
|
|||
|
|
SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_ar FROM `civicrm_option_group` cog
|
|||
|
|
LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
|
|||
|
|
WHERE cog.name = 'payment_instrument' AND cov.name NOT IN ('Credit Card', 'Debit Card');
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
|
|||
|
|
SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_pp FROM `civicrm_option_group` cog
|
|||
|
|
LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
|
|||
|
|
WHERE cog.name = 'payment_instrument' AND cov.name IN ('Credit Card', 'Debit Card');
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- CRM-11515
|
|||
|
|
SELECT @financial_account_id_ppa := max(id) FROM `civicrm_financial_account` WHERE `name` = 'Payment Processor Account';
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_entity_financial_account (`entity_table`, `entity_id`, `account_relationship`, `financial_account_id`)
|
|||
|
|
SELECT 'civicrm_payment_processor', id, @option_value_rel_id_as, @financial_account_id_ppa FROM `civicrm_payment_processor`;
|
|||
|
|
|
|||
|
|
-- CRM-9923 and CRM-11037
|
|||
|
|
SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status';
|
|||
|
|
|
|||
|
|
SELECT @weight := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
|
|||
|
|
VALUES
|
|||
|
|
(@option_group_id_batch_status, {localize}'Data Entry'{/localize}, @weight + 1, 'Data Entry', NULL, 0, 0, @weight + 1),
|
|||
|
|
(@option_group_id_batch_status, {localize}'Reopened'{/localize}, @weight + 2, 'Reopened', NULL, 0, 0, @weight + 2),
|
|||
|
|
(@option_group_id_batch_status, {localize}'Exported'{/localize}, @weight + 3, 'Exported' , NULL, 0, 0, @weight + 3);
|
|||
|
|
|
|||
|
|
-- Insert Batch Modes.
|
|||
|
|
SELECT @option_group_id_batch_modes := max(id) from civicrm_option_group where name = 'batch_mode';
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`)
|
|||
|
|
VALUES
|
|||
|
|
(@option_group_id_batch_modes, {localize}'Manual Batch'{/localize}, 1, 'Manual Batch', NULL, 0, 0, 1),
|
|||
|
|
(@option_group_id_batch_modes, {localize}'Automatic Batch'{/localize}, 2, 'Automatic Batch' , NULL, 0, 0, 2);
|
|||
|
|
|
|||
|
|
-- End of civiaccounts upgrade
|
|||
|
|
|
|||
|
|
-- CRM-10933
|
|||
|
|
ALTER TABLE `civicrm_report_instance`
|
|||
|
|
ADD COLUMN `drilldown_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to instance ID drilldown to',
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_report_instance_drilldown_id` FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance` (`id`) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
-- CRM-10012
|
|||
|
|
ALTER TABLE `civicrm_membership_type`
|
|||
|
|
ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `relationship_direction`;
|
|||
|
|
ALTER TABLE `civicrm_membership`
|
|||
|
|
ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships (membership_type override).' AFTER `owner_membership_id`;
|
|||
|
|
ALTER TABLE `civicrm_membership_log`
|
|||
|
|
ADD COLUMN `max_related` INT(10) unsigned DEFAULT NULL COMMENT 'Maximum number of related memberships.' AFTER `membership_type_id`;
|
|||
|
|
|
|||
|
|
-- CRM-11358
|
|||
|
|
DELETE FROM civicrm_dashboard_contact WHERE contact_id NOT IN (SELECT id FROM civicrm_contact);
|
|||
|
|
INSERT INTO `civicrm_dashboard`
|
|||
|
|
(`domain_id`, {localize field='label'}`label`{/localize}, `url`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `fullscreen_url`, `is_fullscreen`, `is_reserved`)
|
|||
|
|
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
|
|||
|
|
FROM `civicrm_domain`;
|
|||
|
|
|
|||
|
|
INSERT INTO `civicrm_dashboard_contact` (dashboard_id, contact_id, column_no, is_active)
|
|||
|
|
SELECT (SELECT MAX(id) FROM `civicrm_dashboard`), contact_id, 1, IF (SUM(is_active) > 0, 0, 1)
|
|||
|
|
FROM `civicrm_dashboard_contact` WHERE 1 GROUP BY contact_id;
|
|||
|
|
|
|||
|
|
-- CRM-11387
|
|||
|
|
ALTER TABLE `civicrm_event`
|
|||
|
|
ADD `is_partial_payment` tinyint(4) DEFAULT '0' COMMENT 'is partial payment enabled for this event',
|
|||
|
|
ADD `min_initial_amount` decimal(20,2) DEFAULT NULL COMMENT 'Minimum initial amount for partial payment';
|
|||
|
|
|
|||
|
|
{if $multilingual}
|
|||
|
|
{foreach from=$locales item=loc}
|
|||
|
|
ALTER TABLE `civicrm_event`
|
|||
|
|
ADD `initial_amount_label_{$loc}` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
|
|||
|
|
ADD `initial_amount_help_text_{$loc}` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
|
|||
|
|
{/foreach}
|
|||
|
|
{else}
|
|||
|
|
ALTER TABLE `civicrm_event`
|
|||
|
|
ADD `initial_amount_label` varchar(255) COLLATE utf8_unicode_ci COMMENT 'Initial amount label for partial payment',
|
|||
|
|
ADD `initial_amount_help_text` text COLLATE utf8_unicode_ci COMMENT 'Initial amount help text for partial payment';
|
|||
|
|
{/if}
|
|||
|
|
|
|||
|
|
-- CRM-11347
|
|||
|
|
UPDATE `civicrm_option_value` SET is_reserved = 0
|
|||
|
|
WHERE name = 'Urgent' AND option_group_id = (SELECT id FROM `civicrm_option_group` WHERE name = 'case_status');
|
|||
|
|
|
|||
|
|
-- CRM-11400
|
|||
|
|
UPDATE `civicrm_state_province` SET name = 'Distrito Federal' WHERE name = 'Diatrito Federal';
|
|||
|
|
|
|||
|
|
-- CRM-9379 and CRM-11539
|
|||
|
|
SELECT @option_group_id_act := MAX(id) FROM civicrm_option_group WHERE name = 'activity_type';
|
|||
|
|
SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act;
|
|||
|
|
SELECT @max_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
|
|||
|
|
SELECT @CompId := MAX(id) FROM civicrm_component where name = 'CiviContribute';
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_option_value
|
|||
|
|
(option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, is_reserved, component_id, filter)
|
|||
|
|
VALUES
|
|||
|
|
(@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),
|
|||
|
|
(@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),
|
|||
|
|
(@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);
|
|||
|
|
|
|||
|
|
-- CRM-11341
|
|||
|
|
INSERT INTO
|
|||
|
|
`civicrm_job` (domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active)
|
|||
|
|
SELECT
|
|||
|
|
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
|
|||
|
|
FROM `civicrm_domain`;
|
|||
|
|
|
|||
|
|
-- CRM-11367
|
|||
|
|
SELECT @country_id := max(id) from civicrm_country where name = 'Latvia';
|
|||
|
|
|
|||
|
|
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');
|
|||
|
|
|
|||
|
|
INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES
|
|||
|
|
(@country_id, '002', 'Aizkraukles novads'),
|
|||
|
|
(@country_id, '038', 'Jaunjelgavas novads'),
|
|||
|
|
(@country_id, '072', 'Pļaviņu novads'),
|
|||
|
|
(@country_id, '046', 'Kokneses novads'),
|
|||
|
|
(@country_id, '065', 'Neretas novads'),
|
|||
|
|
(@country_id, '092', 'Skrīveru novads'),
|
|||
|
|
(@country_id, '007', 'Alūksnes novads'),
|
|||
|
|
(@country_id, '009', 'Apes novads'),
|
|||
|
|
(@country_id, '015', 'Balvu novads'),
|
|||
|
|
(@country_id, '108', 'Viļakas novads'),
|
|||
|
|
(@country_id, '014', 'Baltinavas novads'),
|
|||
|
|
(@country_id, '082', 'RugÄ<67>ju novads'),
|
|||
|
|
(@country_id, '016', 'Bauskas novads'),
|
|||
|
|
(@country_id, '034', 'Iecavas novads'),
|
|||
|
|
(@country_id, '083', 'RundÄ<64>les novads'),
|
|||
|
|
(@country_id, '105', 'Vecumnieku novads'),
|
|||
|
|
(@country_id, '022', 'CÄ“su novads'),
|
|||
|
|
(@country_id, '055', 'LÄ«gatnes novads'),
|
|||
|
|
(@country_id, '008', 'Amatas novads'),
|
|||
|
|
(@country_id, '039', 'Jaunpiebalgas novads'),
|
|||
|
|
(@country_id, '075', 'Priekuļu novads'),
|
|||
|
|
(@country_id, '070', 'PÄ<50>rgaujas novads'),
|
|||
|
|
(@country_id, '076', 'Raunas novads'),
|
|||
|
|
(@country_id, '104', 'Vecpiebalgas novads'),
|
|||
|
|
(@country_id, '025', 'Daugavpils novads'),
|
|||
|
|
(@country_id, '036', 'Ilūkstes novads'),
|
|||
|
|
(@country_id, '026', 'Dobeles novads'),
|
|||
|
|
(@country_id, '010', 'Auces novads'),
|
|||
|
|
(@country_id, '098', 'TÄ“rvetes novads'),
|
|||
|
|
(@country_id, '033', 'Gulbenes novads'),
|
|||
|
|
(@country_id, '041', 'Jelgavas novads'),
|
|||
|
|
(@country_id, '069', 'Ozolnieku novads'),
|
|||
|
|
(@country_id, '042', 'JÄ“kabpils novads'),
|
|||
|
|
(@country_id, '004', 'Aknīstes novads'),
|
|||
|
|
(@country_id, '107', 'Viesītes novads'),
|
|||
|
|
(@country_id, '049', 'Krustpils novads'),
|
|||
|
|
(@country_id, '085', 'Salas novads'),
|
|||
|
|
(@country_id, '047', 'KrÄ<72>slavas novads'),
|
|||
|
|
(@country_id, '024', 'Dagdas novads'),
|
|||
|
|
(@country_id, '001', 'Aglonas novads'),
|
|||
|
|
(@country_id, '050', 'Kuldīgas novads'),
|
|||
|
|
(@country_id, '093', 'Skrundas novads'),
|
|||
|
|
(@country_id, '006', 'Alsungas novads'),
|
|||
|
|
(@country_id, '003', 'Aizputes novads'),
|
|||
|
|
(@country_id, '028', 'Durbes novads'),
|
|||
|
|
(@country_id, '032', 'Grobiņas novads'),
|
|||
|
|
(@country_id, '071', 'PÄ<50>vilostas novads'),
|
|||
|
|
(@country_id, '074', 'Priekules novads'),
|
|||
|
|
(@country_id, '066', 'NÄ«cas novads'),
|
|||
|
|
(@country_id, '081', 'Rucavas novads'),
|
|||
|
|
(@country_id, '100', 'Vaiņodes novads'),
|
|||
|
|
(@country_id, '054', 'Limbažu novads'),
|
|||
|
|
(@country_id, '005', 'Alojas novads'),
|
|||
|
|
(@country_id, '086', 'Salacgrīvas novads'),
|
|||
|
|
(@country_id, '058', 'Ludzas novads'),
|
|||
|
|
(@country_id, '044', 'KÄ<4B>rsavas novads'),
|
|||
|
|
(@country_id, '110', 'Zilupes novads'),
|
|||
|
|
(@country_id, '023', 'Ciblas novads'),
|
|||
|
|
(@country_id, '059', 'Madonas novads'),
|
|||
|
|
(@country_id, '021', 'Cesvaines novads'),
|
|||
|
|
(@country_id, '057', 'LubÄ<62>nas novads'),
|
|||
|
|
(@country_id, '102', 'VarakļÄ<C2BC>nu novads'),
|
|||
|
|
(@country_id, '030', 'Ērgļu novads'),
|
|||
|
|
(@country_id, '067', 'Ogres novads'),
|
|||
|
|
(@country_id, '035', 'Ikšķiles novads'),
|
|||
|
|
(@country_id, '051', 'Ķeguma novads'),
|
|||
|
|
(@country_id, '053', 'LielvÄ<76>rdes novads'),
|
|||
|
|
(@country_id, '073', 'Preiļu novads'),
|
|||
|
|
(@country_id, '056', 'LÄ«vÄ<76>nu novads'),
|
|||
|
|
(@country_id, '078', 'Riebiņu novads'),
|
|||
|
|
(@country_id, '103', 'VÄ<56>rkavas novads'),
|
|||
|
|
(@country_id, '077', 'RÄ“zeknes novads'),
|
|||
|
|
(@country_id, '109', 'ViļÄ<C2BC>nu novads'),
|
|||
|
|
(@country_id, '013', 'Baldones novads'),
|
|||
|
|
(@country_id, '052', 'Ķekavas novads'),
|
|||
|
|
(@country_id, '068', 'Olaines novads'),
|
|||
|
|
(@country_id, '087', 'Salaspils novads'),
|
|||
|
|
(@country_id, '089', 'Saulkrastu novads'),
|
|||
|
|
(@country_id, '091', 'Siguldas novads'),
|
|||
|
|
(@country_id, '037', 'InÄ<6E>ukalna novads'),
|
|||
|
|
(@country_id, '011', 'Ādažu novads'),
|
|||
|
|
(@country_id, '012', 'Babītes novads'),
|
|||
|
|
(@country_id, '020', 'Carnikavas novads'),
|
|||
|
|
(@country_id, '031', 'Garkalnes novads'),
|
|||
|
|
(@country_id, '048', 'Krimuldas novads'),
|
|||
|
|
(@country_id, '061', 'MÄ<4D>lpils novads'),
|
|||
|
|
(@country_id, '062', 'MÄ<4D>rupes novads'),
|
|||
|
|
(@country_id, '080', 'Ropažu novads'),
|
|||
|
|
(@country_id, '090', 'SÄ“jas novads'),
|
|||
|
|
(@country_id, '095', 'Stopiņu novads'),
|
|||
|
|
(@country_id, '088', 'Saldus novads'),
|
|||
|
|
(@country_id, '018', 'Brocēnu novads'),
|
|||
|
|
(@country_id, '097', 'Talsu novads'),
|
|||
|
|
(@country_id, '027', 'Dundagas novads'),
|
|||
|
|
(@country_id, '063', 'MÄ“rsraga novads'),
|
|||
|
|
(@country_id, '079', 'Rojas novads'),
|
|||
|
|
(@country_id, '099', 'Tukuma novads'),
|
|||
|
|
(@country_id, '043', 'Kandavas novads'),
|
|||
|
|
(@country_id, '029', 'Engures novads'),
|
|||
|
|
(@country_id, '040', 'Jaunpils novads'),
|
|||
|
|
(@country_id, '101', 'Valkas novads'),
|
|||
|
|
(@country_id, '094', 'Smiltenes novads'),
|
|||
|
|
(@country_id, '096', 'StrenÄ<6E>u novads'),
|
|||
|
|
(@country_id, '045', 'Kocēnu novads'),
|
|||
|
|
(@country_id, '060', 'Mazsalacas novads'),
|
|||
|
|
(@country_id, '084', 'RÅ«jienas novads'),
|
|||
|
|
(@country_id, '017', 'Beverīnas novads'),
|
|||
|
|
(@country_id, '019', 'Burtnieku novads'),
|
|||
|
|
(@country_id, '064', 'Naukšēnu novads'),
|
|||
|
|
(@country_id, '106', 'Ventspils novads'),
|
|||
|
|
(@country_id, 'JKB', 'JÄ“kabpils'),
|
|||
|
|
(@country_id, 'VMR', 'Valmiera');
|
|||
|
|
|
|||
|
|
-- CRM-11507
|
|||
|
|
ALTER TABLE `civicrm_batch` CHANGE `type_id` `type_id` INT( 10 ) UNSIGNED NULL COMMENT 'fk to Batch Type options in civicrm_option_values';
|
|||
|
|
UPDATE `civicrm_batch` SET `mode_id` = '1';
|
|||
|
|
|
|||
|
|
-- add Refunded in contribution status
|
|||
|
|
SELECT @option_group_id_cs := MAX(id) FROM civicrm_option_group WHERE name = 'contribution_status';
|
|||
|
|
|
|||
|
|
SELECT @max_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
|
|||
|
|
|
|||
|
|
INSERT INTO
|
|||
|
|
`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`)
|
|||
|
|
VALUES
|
|||
|
|
(@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);
|
|||
|
|
|
|||
|
|
-- Payprocs from extensions may have long titles
|
|||
|
|
ALTER TABLE civicrm_payment_processor_type MODIFY COLUMN title varchar(127);
|
|||
|
|
|
|||
|
|
-- CRM-11665
|
|||
|
|
ALTER TABLE `civicrm_address`
|
|||
|
|
ADD COLUMN manual_geo_code tinyint(4) DEFAULT '0' COMMENT 'Is this a manually entered geo code.';
|
|||
|
|
|
|||
|
|
-- CRM-11761
|
|||
|
|
UPDATE `civicrm_setting` SET `group_name` = 'Personal Preferences' WHERE `group_name` = 'Navigation Menu';
|
|||
|
|
|
|||
|
|
-- CRM-11779
|
|||
|
|
|
|||
|
|
INSERT INTO civicrm_action_mapping ( entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient )
|
|||
|
|
VALUES
|
|||
|
|
( 'civicrm_participant', 'event_template', 'Event Template', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
|
|||
|
|
|
|||
|
|
-- CRM-11802 Fix ON DELETE CASCADE constraint for line_item.price_field_id
|
|||
|
|
ALTER TABLE `civicrm_line_item`
|
|||
|
|
DROP FOREIGN KEY `FK_civicrm_line_item_price_field_id`,
|
|||
|
|
CHANGE `price_field_id` `price_field_id` INT( 10 ) UNSIGNED DEFAULT NULL;
|
|||
|
|
|
|||
|
|
ALTER TABLE `civicrm_line_item`
|
|||
|
|
ADD CONSTRAINT `FK_civicrm_line_item_price_field_id` FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(id) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
-- CRM-11821
|
|||
|
|
-- update all location info of domain
|
|||
|
|
-- like address, email, phone etc.
|
|||
|
|
UPDATE civicrm_domain cd
|
|||
|
|
LEFT JOIN civicrm_loc_block clb ON cd.loc_block_id = clb.id
|
|||
|
|
LEFT JOIN civicrm_address ca ON clb.address_id = ca.id
|
|||
|
|
LEFT JOIN civicrm_phone cp ON cp.id = clb.phone_id
|
|||
|
|
LEFT JOIN civicrm_email ce ON ce.id = clb.email_id
|
|||
|
|
SET
|
|||
|
|
ca.contact_id = cd.contact_id, cp.contact_id = cd.contact_id, ce.contact_id = cd.contact_id;
|
|||
|
|
|
|||
|
|
-- Delete rows from civicrm_loc_block used for civicrm_domain
|
|||
|
|
DELETE clb.* FROM civicrm_domain cd
|
|||
|
|
LEFT JOIN civicrm_loc_block clb ON clb.id = cd.loc_block_id;
|
|||
|
|
|
|||
|
|
-- Delete loc_block_id from civicrm_domain
|
|||
|
|
ALTER TABLE `civicrm_domain` DROP loc_block_id;
|
|||
|
|
|
|||
|
|
-- CRM11818
|
|||
|
|
-- pledge payments should not be cancelled if the contribution was
|
|||
|
|
-- compledged but the pledge is cancelled
|
|||
|
|
UPDATE
|
|||
|
|
civicrm_pledge_payment pp
|
|||
|
|
INNER JOIN civicrm_contribution c ON
|
|||
|
|
c.id = pp.contribution_id AND pp.status_id =3
|
|||
|
|
AND contribution_status_id = 1
|
|||
|
|
SET pp.status_id = contribution_status_id
|
|||
|
|
|