| 1 | {include file='../CRM/Upgrade/4.1.alpha1.msg_template/civicrm_msg_template.tpl'} |
| 2 | |
| 3 | -- get domain id |
| 4 | SELECT @domainID := min(id) FROM civicrm_domain; |
| 5 | |
| 6 | -- CRM-8356 |
| 7 | -- Add filter column 'filter' for 'civicrm_custom_field' |
| 8 | ALTER TABLE `civicrm_custom_field` ADD `filter` VARCHAR(255) NULL COMMENT 'Stores Contact Get API params contact reference custom fields. May be used for other filters in the future.'; |
| 9 | |
| 10 | -- CRM-8062 |
| 11 | ALTER TABLE `civicrm_subscription_history` CHANGE `status` `status` ENUM( 'Added', 'Removed', 'Pending', 'Deleted' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'The state of the contact within the group'; |
| 12 | |
| 13 | -- CRM-8510 |
| 14 | ALTER TABLE civicrm_currency |
| 15 | ADD UNIQUE INDEX UI_name ( name ); |
| 16 | |
| 17 | -- CRM-8616 |
| 18 | DELETE FROM civicrm_currency WHERE name = 'EEK'; |
| 19 | |
| 20 | -- CRM-8769 |
| 21 | INSERT IGNORE INTO civicrm_state_province |
| 22 | (`name`, `abbreviation`, `country_id`) |
| 23 | VALUES |
| 24 | ('Metropolitan Manila' , 'MNL', '1170'); |
| 25 | |
| 26 | -- CRM-8902 |
| 27 | UPDATE civicrm_navigation SET permission ='add cases,access all cases and activities', permission_operator = 'OR' |
| 28 | WHERE civicrm_navigation.name= 'New Case'; |
| 29 | |
| 30 | -- CRM-8780 |
| 31 | |
| 32 | -- add the settings table |
| 33 | CREATE TABLE `civicrm_setting` ( |
| 34 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
| 35 | `group_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'group name for setting element, useful in caching setting elements', |
| 36 | `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Unique name for setting', |
| 37 | `value` text COLLATE utf8_unicode_ci COMMENT 'data associated with this group / name combo', |
| 38 | `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this menu item for', |
| 39 | `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Contact ID if the setting is localized to a contact', |
| 40 | `is_domain` tinyint(4) DEFAULT NULL COMMENT 'Is this setting a contact specific or site wide setting?', |
| 41 | `component_id` int(10) unsigned DEFAULT NULL COMMENT 'Component that this menu item belongs to', |
| 42 | `created_date` datetime DEFAULT NULL COMMENT 'When was the setting created', |
| 43 | `created_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this setting', |
| 44 | PRIMARY KEY (`id`), |
| 45 | KEY `index_group_name` (`group_name`,`name`), |
| 46 | KEY `FK_civicrm_setting_domain_id` (`domain_id`), |
| 47 | KEY `FK_civicrm_setting_contact_id` (`contact_id`), |
| 48 | KEY `FK_civicrm_setting_component_id` (`component_id`), |
| 49 | KEY `FK_civicrm_setting_created_id` (`created_id`), |
| 50 | CONSTRAINT `FK_civicrm_setting_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE, |
| 51 | CONSTRAINT `FK_civicrm_setting_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE, |
| 52 | CONSTRAINT `FK_civicrm_setting_component_id` FOREIGN KEY (`component_id`) REFERENCES `civicrm_component` (`id`), |
| 53 | CONSTRAINT `FK_civicrm_setting_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL |
| 54 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; |
| 55 | |
| 56 | -- CRM-8508 |
| 57 | SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase'; |
| 58 | |
| 59 | SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type'; |
| 60 | SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type; |
| 61 | SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type; |
| 62 | |
| 63 | INSERT INTO civicrm_option_value |
| 64 | (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight, filter, component_id) |
| 65 | VALUES |
| 66 | (@option_group_id_activity_type, {localize}'Change Custom Data'{/localize},{localize}''{/localize}, (SELECT @max_val := @max_val+1), 'Change Custom Data', (SELECT @max_wt := @max_wt+1), 0, @caseCompId); |
| 67 | |
| 68 | -- CRM-8739 |
| 69 | Update civicrm_menu set title = 'Cleanup Caches and Update Paths' where path = 'civicrm/admin/setting/updateConfigBackend'; |
| 70 | |
| 71 | -- CRM-8855 |
| 72 | SELECT @option_group_id_udOpt := max(id) from civicrm_option_group where name = 'user_dashboard_options'; |
| 73 | SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_udOpt; |
| 74 | SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_udOpt; |
| 75 | |
| 76 | INSERT INTO civicrm_option_value |
| 77 | (option_group_id, {localize field='label'}label{/localize}, value, name, weight, filter, is_default, component_id) |
| 78 | VALUES |
| 79 | (@option_group_id_udOpt, {localize}'Assigned Activities'{/localize}, (SELECT @max_val := @max_val+1), 'Assigned Activities', (SELECT @max_wt := @max_wt+1), 0, NULL, NULL); |
| 80 | |
| 81 | -- CRM-8737 |
| 82 | ALTER TABLE `civicrm_event` ADD `is_share` TINYINT( 4 ) NULL DEFAULT '1' COMMENT 'Can people share the event through social media?'; |
| 83 | ALTER TABLE `civicrm_contribution_page` ADD `is_share` TINYINT(4) NULL DEFAULT '1' COMMENT 'Can people share the contribution page through social media?'; |
| 84 | |
| 85 | -- CRM-8357 |
| 86 | ALTER TABLE `civicrm_contact` CHANGE `contact_sub_type` `contact_sub_type` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.'; |
| 87 | |
| 88 | UPDATE civicrm_contact SET contact_sub_type = CONCAT('\ 1', contact_sub_type, '\ 1'); |
| 89 | |
| 90 | -- CRM-6811 |
| 91 | INSERT INTO `civicrm_dashboard` |
| 92 | ( `domain_id`, {localize field='label'}`label`{/localize}, `url`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `fullscreen_url`, `is_fullscreen`, `is_reserved`) |
| 93 | VALUES |
| 94 | ( @domainID, {localize}'Case Dashboard Dashlet'{/localize}, 'civicrm/dashlet/casedashboard&reset=1&snippet=4', 'access CiviCase', NULL , 0, 0, 1, 4, 'civicrm/dashlet/casedashboard&reset=1&snippet=4&context=dashletFullscreen', 1, 1); |
| 95 | |
| 96 | -- CRM-9059 Admin menu rebuild |
| 97 | SELECT @domainID := min(id) FROM civicrm_domain; |
| 98 | SELECT @adminlastID := id FROM civicrm_navigation where name = 'Administer' AND domain_id = @domainID; |
| 99 | SELECT @customizeOld := id FROM civicrm_navigation where name = 'Customize' AND domain_id = @domainID; |
| 100 | SELECT @configureOld := id FROM civicrm_navigation where name = 'Configure' AND domain_id = @domainID; |
| 101 | SELECT @globalOld := id FROM civicrm_navigation where name = 'Global Settings' AND domain_id = @domainID; |
| 102 | SELECT @manageOld := id FROM civicrm_navigation where name = 'Manage' AND domain_id = @domainID; |
| 103 | SELECT @optionsOld := id FROM civicrm_navigation where name = 'Option Lists' AND domain_id = @domainID; |
| 104 | SELECT @customizeOld := id FROM civicrm_navigation where name = 'Customize' AND domain_id = @domainID; |
| 105 | |
| 106 | DELETE from civicrm_navigation WHERE parent_id = @globalOld; |
| 107 | DELETE from civicrm_navigation WHERE parent_id IN (@customizeOld, @configureOld, @manageOld, @optionsOld); |
| 108 | DELETE from civicrm_navigation WHERE id IN (@customizeOld, @configureOld, @manageOld, @optionsOld); |
| 109 | UPDATE civicrm_navigation SET weight = 9 WHERE name = 'CiviCampaign' AND parent_id = @adminlastID; |
| 110 | UPDATE civicrm_navigation SET weight = 10 WHERE name = 'CiviCase' AND parent_id = @adminlastID; |
| 111 | UPDATE civicrm_navigation SET weight = 11 WHERE name = 'CiviContribute' AND parent_id = @adminlastID; |
| 112 | UPDATE civicrm_navigation SET weight = 12 WHERE name = 'CiviEvent' AND parent_id = @adminlastID; |
| 113 | UPDATE civicrm_navigation SET weight = 13 WHERE name = 'CiviGrant' AND parent_id = @adminlastID; |
| 114 | UPDATE civicrm_navigation SET weight = 14 WHERE name = 'CiviMail' AND parent_id = @adminlastID; |
| 115 | UPDATE civicrm_navigation SET weight = 15 WHERE name = 'CiviMember' AND parent_id = @adminlastID; |
| 116 | UPDATE civicrm_navigation SET weight = 16 WHERE name = 'CiviReport' AND parent_id = @adminlastID; |
| 117 | |
| 118 | DELETE FROM civicrm_navigation WHERE name = 'Administration Console'; |
| 119 | |
| 120 | INSERT INTO civicrm_navigation |
| 121 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 122 | VALUES |
| 123 | ( @domainID, 'civicrm/admin&reset=1', '{ts escape="sql" skip="true"}Administration Console{/ts}', 'Administration Console', 'administer CiviCRM', '', @adminlastID, '1', NULL, 1 ); |
| 124 | |
| 125 | SET @adminConsolelastID:=LAST_INSERT_ID(); |
| 126 | INSERT INTO civicrm_navigation |
| 127 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 128 | VALUES |
| 129 | ( @domainID, 'civicrm/admin/configtask&reset=1', '{ts escape="sql" skip="true"}Configuration Checklist{/ts}', 'Configuration Checklist', 'administer CiviCRM', '', @adminConsolelastID, '1', NULL, 1 ); |
| 130 | |
| 131 | INSERT INTO civicrm_navigation |
| 132 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 133 | VALUES |
| 134 | ( @domainID, NULL, '{ts escape="sql" skip="true"}Customize Data and Screens{/ts}', 'Customize Data and Screens', 'administer CiviCRM', '', @adminlastID, '1', NULL, 3 ); |
| 135 | |
| 136 | SET @CustomizelastID:=LAST_INSERT_ID(); |
| 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/admin/custom/group&reset=1', '{ts escape="sql" skip="true"}Custom Fields{/ts}', 'Custom Fields', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 1 ), |
| 141 | ( @domainID, 'civicrm/admin/uf/group&reset=1', '{ts escape="sql" skip="true"}Profiles{/ts}', 'Profiles', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 2 ), |
| 142 | ( @domainID, 'civicrm/admin/tag&reset=1', '{ts escape="sql" skip="true"}Tags (Categories){/ts}', 'Tags (Categories)', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 3 ), |
| 143 | ( @domainID, 'civicrm/admin/options/activity_type&reset=1&group=activity_type', '{ts escape="sql" skip="true"}Activity Types{/ts}', 'Activity Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 4 ), |
| 144 | ( @domainID, 'civicrm/admin/reltype&reset=1', '{ts escape="sql" skip="true"}Relationship Types{/ts}', 'Relationship Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 5 ), |
| 145 | ( @domainID, 'civicrm/admin/options/subtype&reset=1', '{ts escape="sql" skip="true"}Contact Types{/ts}','Contact Types', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 6 ), |
| 146 | ( @domainID, 'civicrm/admin/setting/preferences/display&reset=1', '{ts escape="sql" skip="true"}Display Preferences{/ts}', 'Display Preferences', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 9 ), |
| 147 | ( @domainID, 'civicrm/admin/setting/search&reset=1', '{ts escape="sql" skip="true"}Search Preferences{/ts}', 'Search Preferences', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 10 ), |
| 148 | ( @domainID, 'civicrm/admin/menu&reset=1', '{ts escape="sql" skip="true"}Navigation Menu{/ts}', 'Navigation Menu', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 11 ), |
| 149 | ( @domainID, 'civicrm/admin/options/wordreplacements&reset=1','{ts escape="sql" skip="true"}Word Replacements{/ts}','Word Replacements', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 12 ), |
| 150 | ( @domainID, 'civicrm/admin/options/custom_search&reset=1&group=custom_search', '{ts escape="sql" skip="true"}Manage Custom Searches{/ts}', 'Manage Custom Searches', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 13 ), |
| 151 | ( @domainID, 'civicrm/admin/extensions&reset=1', '{ts escape="sql" skip="true"}Manage Extensions{/ts}', 'Manage Extensions', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 14 ); |
| 152 | |
| 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, NULL, '{ts escape="sql" skip="true"}Dropdown Options{/ts}', 'Dropdown Options', 'administer CiviCRM', '', @CustomizelastID, '1', NULL, 8 ); |
| 157 | |
| 158 | SET @optionListlastID:=LAST_INSERT_ID(); |
| 159 | INSERT INTO civicrm_navigation |
| 160 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 161 | VALUES |
| 162 | ( @domainID, 'civicrm/admin/options/gender&reset=1&group=gender', '{ts escape="sql" skip="true"}Gender Options{/ts}', 'Gender Options', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 1 ), |
| 163 | ( @domainID, 'civicrm/admin/options/individual_prefix&group=individual_prefix&reset=1', '{ts escape="sql" skip="true"}Individual Prefixes (Ms, Mr...){/ts}', 'Individual Prefixes (Ms, Mr...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 2 ), |
| 164 | ( @domainID, 'civicrm/admin/options/individual_suffix&group=individual_suffix&reset=1', '{ts escape="sql" skip="true"}Individual Suffixes (Jr, Sr...){/ts}', 'Individual Suffixes (Jr, Sr...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 3 ), |
| 165 | ( @domainID, 'civicrm/admin/options/instant_messenger_service&group=instant_messenger_service&reset=1', '{ts escape="sql" skip="true"}Instant Messenger Services{/ts}', 'Instant Messenger Services', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 4 ), |
| 166 | ( @domainID, 'civicrm/admin/locationType&reset=1', '{ts escape="sql" skip="true"}Location Types (Home, Work...){/ts}', 'Location Types (Home, Work...)', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 5 ), |
| 167 | ( @domainID, 'civicrm/admin/options/mobile_provider&group=mobile_provider&reset=1', '{ts escape="sql" skip="true"}Mobile Phone Providers{/ts}', 'Mobile Phone Providers', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 6 ), |
| 168 | ( @domainID, 'civicrm/admin/options/phone_type&group=phone_type&reset=1', '{ts escape="sql" skip="true"}Phone Types{/ts}', 'Phone Types', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 7 ), |
| 169 | ( @domainID, 'civicrm/admin/options/website_type&group=website_type&reset=1', '{ts escape="sql" skip="true"}Website Types{/ts}', 'Website Types', 'administer CiviCRM', '', @optionListlastID, '1', NULL, 8 ); |
| 170 | |
| 171 | INSERT INTO civicrm_navigation |
| 172 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 173 | VALUES |
| 174 | ( @domainID, NULL, '{ts escape="sql" skip="true"}Communications{/ts}', 'Communications', 'administer CiviCRM', '', @adminlastID, '1', NULL, 4 ); |
| 175 | |
| 176 | SET @communicationslastID:=LAST_INSERT_ID(); |
| 177 | INSERT INTO civicrm_navigation |
| 178 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 179 | VALUES |
| 180 | ( @domainID, 'civicrm/admin/domain&action=update&reset=1', '{ts escape="sql" skip="true"}Organization Address and Contact Info{/ts}', 'Organization Address and Contact Info', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 1 ), |
| 181 | ( @domainID, 'civicrm/admin/options/from_email_address&group=from_email_address&reset=1', '{ts escape="sql" skip="true"}FROM Email Addresses{/ts}', 'FROM Email Addresses', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 2 ), |
| 182 | ( @domainID, 'civicrm/admin/messageTemplates&reset=1', '{ts escape="sql" skip="true"}Message Templates{/ts}', 'Message Templates', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 3 ), |
| 183 | ( @domainID, 'civicrm/admin/scheduleReminders&reset=1', '{ts escape="sql" skip="true"}Schedule Reminders{/ts}', 'Schedule Reminders', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 4 ), |
| 184 | ( @domainID, 'civicrm/admin/options/preferred_communication_method&group=preferred_communication_method&reset=1', '{ts escape="sql" skip="true"}Preferred Communication Methods{/ts}', 'Preferred Communication Methods', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 5 ), |
| 185 | ( @domainID, 'civicrm/admin/labelFormats&reset=1', '{ts escape="sql" skip="true"}Label Formats{/ts}', 'Label Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 6 ), |
| 186 | ( @domainID, 'civicrm/admin/pdfFormats&reset=1', '{ts escape="sql" skip="true"}Print Page (PDF) Formats{/ts}', 'Print Page (PDF) Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 7 ), |
| 187 | ( @domainID, 'civicrm/admin/options/email_greeting&group=email_greeting&reset=1', '{ts escape="sql" skip="true"}Email Greeting Formats{/ts}', 'Email Greeting Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 8 ), |
| 188 | ( @domainID, 'civicrm/admin/options/postal_greeting&group=postal_greeting&reset=1', '{ts escape="sql" skip="true"}Postal Greeting Formats{/ts}', 'Postal Greeting Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 9 ), |
| 189 | ( @domainID, 'civicrm/admin/options/addressee&group=addressee&reset=1', '{ts escape="sql" skip="true"}Addressee Formats{/ts}', 'Addressee Formats', 'administer CiviCRM', '', @communicationslastID, '1', NULL, 10 ); |
| 190 | |
| 191 | INSERT INTO civicrm_navigation |
| 192 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 193 | VALUES |
| 194 | ( @domainID, NULL, '{ts escape="sql" skip="true"}Localization{/ts}', 'Localization', 'administer CiviCRM', '', @adminlastID, '1', NULL, 6 ); |
| 195 | |
| 196 | SET @locallastID:=LAST_INSERT_ID(); |
| 197 | INSERT INTO civicrm_navigation |
| 198 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 199 | VALUES |
| 200 | ( @domainID, 'civicrm/admin/setting/localization&reset=1', '{ts escape="sql" skip="true"}Languages, Currency, Locations{/ts}', 'Languages, Currency, Locations', 'administer CiviCRM', '', @locallastID, '1', NULL, 1 ), |
| 201 | ( @domainID, 'civicrm/admin/setting/preferences/address&reset=1', '{ts escape="sql" skip="true"}Address Settings{/ts}', 'Address Settings', 'administer CiviCRM', '', @locallastID, '1', NULL, 2 ), |
| 202 | ( @domainID, 'civicrm/admin/setting/date&reset=1', '{ts escape="sql" skip="true"}Date Format{/ts}', 'Date Formats', 'administer CiviCRM', '', @locallastID, '1', NULL, 3 ), |
| 203 | ( @domainID, 'civicrm/admin/options/languages&group=languages&reset=1', '{ts escape="sql" skip="true"}Preferred Language Options{/ts}', 'Preferred Language Options', 'administer CiviCRM', '', @locallastID, '1', NULL, 4 ); |
| 204 | |
| 205 | INSERT INTO civicrm_navigation |
| 206 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 207 | VALUES |
| 208 | ( @domainID, NULL, '{ts escape="sql" skip="true"}Users and Permissions{/ts}', 'Users and Permissions', 'administer CiviCRM', '', @adminlastID, '1', NULL, 7 ); |
| 209 | |
| 210 | SET @usersPermslastID:=LAST_INSERT_ID(); |
| 211 | INSERT INTO civicrm_navigation |
| 212 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 213 | VALUES |
| 214 | ( @domainID, 'civicrm/admin/access&reset=1', '{ts escape="sql" skip="true"}Permissions (Access Control){/ts}', 'Permissions (Access Control)', 'administer CiviCRM', '', @usersPermslastID, '1', NULL, 1 ), |
| 215 | ( @domainID, 'civicrm/admin/synchUser&reset=1', '{ts escape="sql" skip="true"}Synchronize Users to Contacts{/ts}', 'Synchronize Users to Contacts', 'administer CiviCRM', '', @usersPermslastID, '1', NULL, 2 ); |
| 216 | |
| 217 | INSERT INTO civicrm_navigation |
| 218 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 219 | VALUES |
| 220 | ( @domainID, NULL, '{ts escape="sql" skip="true"}System Settings{/ts}', 'System Settings', 'administer CiviCRM', '', @adminlastID, '1', NULL, 8 ); |
| 221 | |
| 222 | SET @systemSettingslastID:=LAST_INSERT_ID(); |
| 223 | INSERT INTO civicrm_navigation |
| 224 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 225 | VALUES |
| 226 | ( @domainID, 'civicrm/admin/setting/component&reset=1', '{ts escape="sql" skip="true"}Enable CiviCRM Components{/ts}', 'Enable Components', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 1 ), |
| 227 | ( @domainID, 'civicrm/admin/setting/smtp&reset=1', '{ts escape="sql" skip="true"}Outbound Email (SMTP/Sendmail){/ts}', 'Outbound Email', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 2 ), |
| 228 | ( @domainID, 'civicrm/admin/paymentProcessor&reset=1', '{ts escape="sql" skip="true"}Payment Processors{/ts}', 'Payment Processors', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 3 ), |
| 229 | ( @domainID, 'civicrm/admin/setting/mapping&reset=1', '{ts escape="sql" skip="true"}Mapping and Geocoding{/ts}', 'Mapping and Geocoding', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 4 ), |
| 230 | ( @domainID, 'civicrm/admin/setting/misc&reset=1', '{ts escape="sql" skip="true"}Undelete, Logging and ReCAPTCHA{/ts}', 'Undelete, Logging and ReCAPTCHA', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 5 ), |
| 231 | ( @domainID, 'civicrm/admin/setting/path&reset=1', '{ts escape="sql" skip="true"}Directories{/ts}', 'Directories', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 6 ), |
| 232 | ( @domainID, 'civicrm/admin/setting/url&reset=1', '{ts escape="sql" skip="true"}Resource URLs{/ts}', 'Resource URLs', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 7 ), |
| 233 | ( @domainID, 'civicrm/admin/setting/updateConfigBackend&reset=1', '{ts escape="sql" skip="true"}Cleanup Caches and Update Paths{/ts}', 'Cleanup Caches and Update Paths', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 8 ), |
| 234 | ( @domainID, 'civicrm/admin/setting/uf&reset=1', '{ts escape="sql" skip="true"}CMS Database Integration{/ts}', 'CMS Integration', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 9 ), |
| 235 | ( @domainID, 'civicrm/admin/options/safe_file_extension&group=safe_file_extension&reset=1', '{ts escape="sql" skip="true"}Safe File Extensions{/ts}', 'Safe File Extensions','administer CiviCRM', '',@systemSettingslastID, '1', NULL, 10 ), |
| 236 | ( @domainID, 'civicrm/admin/options?reset=1', '{ts escape="sql" skip="true"}Option Groups{/ts}', 'Option Groups', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 11 ), |
| 237 | ( @domainID, 'civicrm/admin/mapping&reset=1', '{ts escape="sql" skip="true"}Import/Export Mappings{/ts}', 'Import/Export Mappings', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 12 ), |
| 238 | ( @domainID, 'civicrm/admin/setting/debug&reset=1', '{ts escape="sql" skip="true"}Debugging and Error Handling{/ts}','Debugging and Error Handling', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 13 ), |
| 239 | ( @domainID, 'civicrm/admin/setting/preferences/multisite&reset=1', '{ts escape="sql" skip="true"}Multi Site Settings{/ts}', 'Multi Site Settings', 'administer CiviCRM', '', @systemSettingslastID, '1', NULL, 14 ); |
| 240 | |
| 241 | SELECT @campaignAdminID := id FROM civicrm_navigation where name = 'CiviCampaign' AND parent_id = @adminlastID; |
| 242 | SELECT @eventAdminID := id FROM civicrm_navigation where name = 'CiviEvent' AND parent_id = @adminlastID; |
| 243 | SELECT @mailAdminID := id FROM civicrm_navigation where name = 'CiviMail' AND parent_id = @adminlastID; |
| 244 | SELECT @memberAdminID := id FROM civicrm_navigation where name = 'CiviMember' AND parent_id = @adminlastID; |
| 245 | |
| 246 | INSERT INTO civicrm_navigation |
| 247 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 248 | VALUES |
| 249 | ( @domainID, 'civicrm/admin/setting/preferences/campaign&reset=1', '{ts escape="sql" skip="true"}CiviCampaign Component Settings{/ts}', 'CiviCampaign Component Settings','administer CiviCampaign', '', @campaignAdminID, '1', NULL, 5 ), |
| 250 | ( @domainID, 'civicrm/admin/setting/preferences/event&reset=1', '{ts escape="sql" skip="true"}CiviEvent Component Settings{/ts}', 'CiviEvent Component Settings','access CiviEvent,administer CiviCRM', 'AND', @eventAdminID, '1', NULL, 13 ), |
| 251 | ( @domainID, 'civicrm/admin/setting/preferences/mailing&reset=1', '{ts escape="sql" skip="true"}CiviMail Component Settings{/ts}', 'CiviMail Component Settings','access CiviMail,administer CiviCRM', 'AND', @mailAdminID, '1', NULL, 6 ), |
| 252 | ( @domainID, 'civicrm/admin/setting/preferences/member&reset=1', '{ts escape="sql" skip="true"}CiviMember Component Settings{/ts}', 'CiviMember Component Settings','access CiviMember,administer CiviCRM', 'AND', @memberAdminID, '1', NULL, 5 ), |
| 253 | ( @domainID, 'civicrm/admin/options/event_badge&group=event_badge&reset=1', '{ts escape="sql" skip="true"}Event Badge Formats{/ts}', 'Event Badge Formats', 'access CiviEvent,administer CiviCRM', 'AND', @eventAdminID, '1', NULL, 11 ); |
| 254 | |
| 255 | -- CRM-9113 |
| 256 | ALTER TABLE `civicrm_report_instance` ADD `grouprole` VARCHAR( 1024 ) NULL AFTER `permission`; |
| 257 | |
| 258 | -- CRM-8762 Fix option_group table |
| 259 | ALTER TABLE civicrm_option_group CHANGE `is_reserved` `is_reserved` TINYINT DEFAULT 1; |
| 260 | UPDATE civicrm_option_group SET `is_reserved` = 1; |
| 261 | {if $multilingual} |
| 262 | {foreach from=$locales item=locale} |
| 263 | UPDATE civicrm_option_group SET label_{$locale} = description_{$locale} WHERE label_{$locale} IS NULL; |
| 264 | {/foreach} |
| 265 | {else} |
| 266 | UPDATE civicrm_option_group SET `label` = `description` WHERE `label` IS NULL; |
| 267 | {/if} |
| 268 | |
| 269 | -- CRM-9112 |
| 270 | ALTER TABLE `civicrm_dedupe_rule_group` ADD `title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Label of the rule group'; |
| 271 | |
| 272 | ALTER TABLE `civicrm_dedupe_rule_group` ADD `is_reserved` TINYINT( 4 ) NULL DEFAULT NULL COMMENT 'Is this a reserved rule - a rule group that has been optimized and cannot be changed by the admin'; |
| 273 | |
| 274 | UPDATE `civicrm_dedupe_rule_group` SET `name` = CONCAT( REPLACE( `name`, '-', '' ), '-', id ); |
| 275 | |
| 276 | -- the fuzzy default dedupe rules |
| 277 | INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved) |
| 278 | VALUES ('Individual', 20, 'Fuzzy', 1, 'IndividualFuzzy', 'Individual Fuzzy In-built', 1); |
| 279 | |
| 280 | SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group; |
| 281 | INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight) |
| 282 | VALUES (@drgid, 'civicrm_contact', 'first_name', 5), |
| 283 | (@drgid, 'civicrm_contact', 'last_name', 7), |
| 284 | (@drgid, 'civicrm_email' , 'email', 10); |
| 285 | |
| 286 | -- the strict dedupe rules |
| 287 | INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved) |
| 288 | VALUES ('Individual', 10, 'Strict', 1, 'IndividualStrict', 'Individual Strict In-built', 1); |
| 289 | |
| 290 | SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group; |
| 291 | INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight) |
| 292 | VALUES (@drgid, 'civicrm_email', 'email', 10); |
| 293 | |
| 294 | INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default, name, title, is_reserved) |
| 295 | VALUES ('Individual', 15, 'Strict', 0, 'IndividualComplete', 'Individual Complete Inbuilt', 1); |
| 296 | |
| 297 | SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group; |
| 298 | INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight) |
| 299 | VALUES (@drgid, 'civicrm_contact', 'first_name', '5'), |
| 300 | (@drgid, 'civicrm_contact', 'last_name', '5'), |
| 301 | (@drgid, 'civicrm_address', 'street_address', '5'), |
| 302 | (@drgid, 'civicrm_contact', 'middle_name', '1'), |
| 303 | (@drgid, 'civicrm_contact', 'suffix_id', '1'); |
| 304 | |
| 305 | -- CRM-9120 |
| 306 | {if $multilingual} |
| 307 | {foreach from=$locales item=locale} |
| 308 | ALTER TABLE `civicrm_location_type` ADD display_name_{$locale} VARCHAR( 64 ) DEFAULT NULL COMMENT 'Location Type Display Name.' AFTER `name`; |
| 309 | UPDATE `civicrm_location_type` SET display_name_{$locale} = `name`; |
| 310 | {/foreach} |
| 311 | {else} |
| 312 | ALTER TABLE `civicrm_location_type` ADD `display_name` VARCHAR( 64 ) DEFAULT NULL COMMENT 'Location Type Display Name.' AFTER `name`; |
| 313 | UPDATE `civicrm_location_type` SET `display_name` = `name`; |
| 314 | {/if} |
| 315 | |
| 316 | |
| 317 | -- CRM-9125 |
| 318 | ALTER TABLE `civicrm_contribution_recur` ADD `contribution_type_id` int(10) unsigned NULL COMMENT 'FK to Contribution Type'; |
| 319 | ALTER TABLE `civicrm_contribution_recur` ADD CONSTRAINT `FK_civicrm_contribution_recur_contribution_type_id` FOREIGN KEY (`contribution_type_id`) REFERENCES `civicrm_contribution_type` (`id`) ON DELETE SET NULL; |
| 320 | |
| 321 | ALTER TABLE `civicrm_contribution_recur` ADD `payment_instrument_id` int(10) unsigned NULL COMMENT 'FK to Payment Instrument'; |
| 322 | ALTER TABLE `civicrm_contribution_recur` ADD INDEX UI_contribution_recur_payment_instrument_id ( payment_instrument_id ); |
| 323 | |
| 324 | ALTER TABLE `civicrm_contribution_recur` ADD `campaign_id` int(10) unsigned NULL COMMENT 'The campaign for which this contribution has been triggered.'; |
| 325 | ALTER TABLE `civicrm_contribution_recur` ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL; |
| 326 | |
| 327 | UPDATE `civicrm_contribution_recur` ccr INNER JOIN `civicrm_contribution` cc ON ccr.id = cc.contribution_recur_id SET ccr.campaign_id = cc.campaign_id, ccr.payment_instrument_id = cc.payment_instrument_id, ccr.contribution_type_id = cc.contribution_type_id; |
| 328 | |
| 329 | -- CRM-8962 |
| 330 | INSERT INTO civicrm_action_mapping ( entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient ) |
| 331 | VALUES |
| 332 | ('civicrm_participant', 'event_type', 'Event Type', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts'), |
| 333 | ('civicrm_participant', 'civicrm_event', 'Event Name', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts'); |
| 334 | |
| 335 | INSERT INTO civicrm_option_group |
| 336 | (name, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, is_reserved, is_active) |
| 337 | VALUES |
| 338 | ('event_contacts', {localize}'{ts escape="sql"}Event Recipients{/ts}'{/localize}, {localize}'{ts escape="sql"}Event Recipients{/ts}'{/localize}, 1, 1), |
| 339 | ('contact_reference_options', {localize}'{ts escape="sql"}Contact Reference Autocomplete Options{/ts}'{/localize}, {localize}'{ts escape="sql"}Contact Reference Autocomplete Options{/ts}'{/localize}, 1, 1); |
| 340 | |
| 341 | SELECT @option_group_id_ere := max(id) from civicrm_option_group where name = 'event_contacts'; |
| 342 | |
| 343 | INSERT INTO civicrm_option_value |
| 344 | (option_group_id, {localize field='label'}label{/localize}, value, name, filter, weight, is_active ) |
| 345 | VALUES |
| 346 | (@option_group_id_ere, {localize}'{ts escape="sql"}Participant Role{/ts}'{/localize}, 1, 'participant_role', 0, 1, 1 ); |
| 347 | |
| 348 | ALTER TABLE civicrm_action_schedule ADD `absolute_date` date DEFAULT NULL COMMENT 'Date on which the reminder be sent.'; |
| 349 | ALTER TABLE civicrm_action_schedule ADD `recipient_listing` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'listing based on recipient field.'; |
| 350 | |
| 351 | |
| 352 | -- CRM-8534 |
| 353 | ALTER TABLE civicrm_pcp_block |
| 354 | DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id, |
| 355 | DROP INDEX FK_civicrm_pcp_block_entity_id; |
| 356 | |
| 357 | ALTER TABLE civicrm_pcp |
| 358 | DROP FOREIGN KEY FK_civicrm_pcp_contribution_page_id, |
| 359 | DROP INDEX FK_civicrm_pcp_contribution_page_id; |
| 360 | |
| 361 | ALTER TABLE `civicrm_pcp` |
| 362 | ADD `page_type` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'contribute' AFTER `contribution_page_id`; |
| 363 | ALTER TABLE `civicrm_pcp` |
| 364 | CHANGE `contribution_page_id` `page_id` INT( 10 ) UNSIGNED NOT NULL COMMENT 'The Page which triggered this pcp'; |
| 365 | ALTER TABLE `civicrm_pcp` |
| 366 | ADD `pcp_block_id` int(10) unsigned NOT NULL COMMENT 'The pcp block that this pcp page was created from' AFTER `page_type`; |
| 367 | |
| 368 | UPDATE `civicrm_pcp` |
| 369 | SET `page_type` = 'contribute' WHERE `page_type` = '' OR `page_type` IS NULL; |
| 370 | UPDATE `civicrm_pcp` `pcp` |
| 371 | SET `pcp_block_id` = (SELECT `id` FROM `civicrm_pcp_block` `pb` WHERE `pb`.`entity_id` = `pcp`.`page_id`); |
| 372 | |
| 373 | ALTER TABLE `civicrm_pcp_block` |
| 374 | ADD `target_entity_type` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'contribute' AFTER `entity_id`; |
| 375 | ALTER TABLE `civicrm_pcp_block` |
| 376 | ADD `target_entity_id` int(10) unsigned NOT NULL COMMENT 'The entity that this pcp targets' AFTER `target_entity_type`; |
| 377 | UPDATE `civicrm_pcp_block` |
| 378 | SET `target_entity_id` = `entity_id` WHERE `target_entity_id` = '' OR `target_entity_id` IS NULL; |
| 379 | |
| 380 | ALTER TABLE `civicrm_pcp` DROP COLUMN `referer`; |
| 381 | |
| 382 | UPDATE civicrm_navigation SET url = 'civicrm/admin/pcp?reset=1&page_type=contribute' WHERE url = 'civicrm/admin/pcp&reset=1'; |
| 383 | |
| 384 | SELECT @lastEventId := MAX(id) FROM civicrm_navigation where name = 'Events' AND domain_id = @domainID; |
| 385 | SELECT @adminEventId := MAX(id) FROM civicrm_navigation where name = 'CiviEvent' AND domain_id = @domainID; |
| 386 | SELECT @lastEventIdWeight := MAX(weight)+1 FROM civicrm_navigation where parent_id = @lastEventId; |
| 387 | SELECT @adminEventIdWeight := MAX(weight)+1 FROM civicrm_navigation where parent_id = @adminEventId; |
| 388 | |
| 389 | INSERT INTO civicrm_navigation |
| 390 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) |
| 391 | VALUES |
| 392 | ( @domainID, 'civicrm/admin/pcp?reset=1&page_type=event', '{ts escape="sql" skip="true"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', 'access CiviEvent,administer CiviCRM', 'AND', @lastEventId, '1', 1, @lastEventIdWeight ), |
| 393 | ( @domainID, 'civicrm/admin/pcp?reset=1&page_type=event', '{ts escape="sql" skip="true"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', 'access CiviEvent,administer CiviCRM', 'AND', @adminEventId, '1', 1, @adminEventIdWeight ); |
| 394 | |
| 395 | -- CRM-8358 - consolidated cron jobs |
| 396 | |
| 397 | CREATE TABLE `civicrm_job` ( |
| 398 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job Id', |
| 399 | `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for', |
| 400 | `run_frequency` enum('Hourly','Daily','Always') COLLATE utf8_unicode_ci DEFAULT 'Daily' COMMENT 'Scheduled job run frequency.', |
| 401 | `last_run` datetime DEFAULT NULL COMMENT 'When was this cron entry last run', |
| 402 | `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job', |
| 403 | `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the job', |
| 404 | `api_prefix` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Prefix of the job api call', |
| 405 | `api_entity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity of the job api call', |
| 406 | `api_action` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Action of the job api call', |
| 407 | `parameters` text COLLATE utf8_unicode_ci COMMENT 'List of parameters to the command.', |
| 408 | `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this job active?', |
| 409 | PRIMARY KEY (`id`), |
| 410 | KEY `FK_civicrm_job_domain_id` (`domain_id`), |
| 411 | CONSTRAINT `FK_civicrm_job_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) |
| 412 | ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 413 | |
| 414 | CREATE TABLE `civicrm_job_log` ( |
| 415 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Job log entry Id', |
| 416 | `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for', |
| 417 | `run_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Log entry date', |
| 418 | `job_id` int(10) unsigned DEFAULT NULL COMMENT 'Pointer to job id - not a FK though, just for logging purposes', |
| 419 | `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job', |
| 420 | `command` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Full path to file containing job script', |
| 421 | `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title line of log entry', |
| 422 | `data` text COLLATE utf8_unicode_ci COMMENT 'Potential extended data for specific job run (e.g. tracebacks).', |
| 423 | PRIMARY KEY (`id`), |
| 424 | KEY `FK_civicrm_job_log_domain_id` (`domain_id`), |
| 425 | CONSTRAINT `FK_civicrm_job_log_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) |
| 426 | ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 427 | |
| 428 | INSERT INTO `civicrm_job` |
| 429 | ( domain_id, run_frequency, last_run, name, description, api_prefix, api_entity, api_action, parameters, is_active ) |
| 430 | VALUES |
| 431 | ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Mailings scheduler{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled mailings{/ts}', 'civicrm_api3', 'job', 'process_mailing', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0), |
| 432 | ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Bounces fetcher{/ts}', '{ts escape="sql" skip="true"}Fetches bounces from mailings and writes them to mailing statistics{/ts}', 'civicrm_api3', 'job', 'fetch_bounces', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0), |
| 433 | ( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Activity processor{/ts}', '{ts escape="sql" skip="true"}{/ts}', 'civicrm_api3', 'job', 'fetch_activities', 'user=USERNAME\r\npassword=PASSWORD\r\nkey=SITE_KEY', 0), |
| 434 | ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Pledge record processor{/ts}', '{ts escape="sql" skip="true"}Updates pledge records and sends out reminders{/ts}', 'civicrm_api3', 'job', 'process_pledge', 'version=3\r\n', 0), |
| 435 | ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Address geocoder{/ts}', '{ts escape="sql" skip="true"}Goes through addresses and geocodes them (requires Geocoding API on){/ts}', 'civicrm_api3', 'job', 'geocode', 'version=3\r\n', 0), |
| 436 | ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Greeting updater{/ts}', '{ts escape="sql" skip="true"}Goes through contact records and updates greeting settings{/ts}', 'civicrm_api3', 'job', 'update_greeting', 'version=3\r\n', 0), |
| 437 | ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Report sender{/ts}', '{ts escape="sql" skip="true"}Generates and sends out reports via email{/ts}', 'civicrm_api3', 'job', 'mail_reports', 'version=3\r\n', 0), |
| 438 | ( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Scheduled reminders sender{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled reminders via email.{/ts}', 'civicrm_api3', 'job', 'send_reminder', 'version=3\r\n', 0), |
| 439 | ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Participant status processor{/ts}','{ts escape="sql" skip="true"}Adjusts event participant statuses based on time{/ts}', 'civicrm_api3', 'job', 'process_participant', 'version=3\r\n', 0), |
| 440 | ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Membership status processor{/ts}', '{ts escape="sql" skip="true"}Adjusts event membership statuses based on time{/ts}', 'civicrm_api3', 'job', 'process_membership', 'version=3\r\n', 0), |
| 441 | ( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Membership reminder date processor{/ts}','{ts escape="sql" skip="true"}Adjusts membership reminder dates based on time{/ts}', 'civicrm_api3', 'job', 'process_process_membership_reminder_date', 'version=3\r\n', 0); |
| 442 | |
| 443 | --CRM 9135 |
| 444 | ALTER TABLE civicrm_contribution_recur |
| 445 | ADD is_email_receipt TINYINT (4) COMMENT 'if true, receipt is automatically emailed to contact on each successful payment' AFTER payment_processor_id; |
| 446 | |
| 447 | -- /***** Civicrm Multi-Event Registration ***********/ |
| 448 | |
| 449 | CREATE TABLE civicrm_event_carts ( |
| 450 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Cart Id', |
| 451 | user_id int unsigned COMMENT 'FK to civicrm_contact who created this cart', |
| 452 | coupon_code varchar(255) DEFAULT NULL, |
| 453 | completed tinyint DEFAULT 0, |
| 454 | PRIMARY KEY ( id ), |
| 455 | CONSTRAINT FK_civicrm_event_carts_user_id FOREIGN KEY (user_id) |
| 456 | REFERENCES civicrm_contact(id) ON DELETE SET NULL |
| 457 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 458 | |
| 459 | CREATE TABLE civicrm_events_in_carts ( |
| 460 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event In Cart Id', |
| 461 | event_id int unsigned COMMENT 'FK to Event ID', |
| 462 | event_cart_id int unsigned COMMENT 'FK to Event Cart ID', |
| 463 | PRIMARY KEY ( id ), |
| 464 | CONSTRAINT FK_civicrm_events_in_carts_event_id FOREIGN KEY (event_id) |
| 465 | REFERENCES civicrm_event(id) ON DELETE CASCADE, |
| 466 | CONSTRAINT FK_civicrm_events_in_carts_event_cart_id FOREIGN KEY |
| 467 | (event_cart_id) REFERENCES civicrm_event_carts(id) ON DELETE CASCADE |
| 468 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 469 | |
| 470 | |
| 471 | ALTER TABLE civicrm_participant |
| 472 | ADD discount_amount int unsigned DEFAULT 0 COMMENT 'Discount Amount'; |
| 473 | ALTER TABLE civicrm_participant |
| 474 | ADD cart_id int unsigned DEFAULT NULL COMMENT 'FK to civicrm_event_carts'; |
| 475 | ALTER TABLE civicrm_participant |
| 476 | ADD CONSTRAINT FK_civicrm_participant_cart_id FOREIGN KEY (cart_id) |
| 477 | REFERENCES civicrm_event_carts(id) ON DELETE SET NULL; |
| 478 | |
| 479 | -- XXX a hint to the payment form. Can someone make this go away? |
| 480 | ALTER TABLE civicrm_participant |
| 481 | ADD must_wait TINYINT DEFAULT 0 COMMENT 'On Waiting List'; |
| 482 | |
| 483 | |
| 484 | SELECT @pending_id := MAX(id) + 1 FROM civicrm_participant_status_type; |
| 485 | INSERT INTO civicrm_participant_status_type |
| 486 | (id, name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id) |
| 487 | VALUES |
| 488 | (@pending_id, 'Pending in cart', {localize}'{ts escape="sql"}Pending in cart{/ts}'{/localize}, 'Pending', 1, 1, 0, @pending_id, 2 ); |
| 489 | |
| 490 | |
| 491 | ALTER TABLE civicrm_event |
| 492 | ADD parent_event_id int unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_event: parent event'; |
| 493 | ALTER TABLE civicrm_event |
| 494 | ADD slot_label_id int unsigned DEFAULT NULL COMMENT 'Subevent slot label. Implicit FK to civicrm_option_value where option_group = conference_slot.'; |
| 495 | |
| 496 | INSERT INTO |
| 497 | `civicrm_option_group` (`name`, {localize field='label'}`label`{/localize}, {localize field='description'}`description`{/localize}, `is_reserved`, `is_active`) |
| 498 | VALUES |
| 499 | ('conference_slot' , {localize}'{ts escape="sql"}Conference Slot{/ts}'{/localize}, {localize}'{ts escape="sql"}Conference Slot{/ts}'{/localize} , 1, 1); |
| 500 | |
| 501 | SELECT @option_group_id_conference_slot := max(id) from civicrm_option_group where name = 'conference_slot'; |
| 502 | |
| 503 | INSERT INTO |
| 504 | `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`) |
| 505 | VALUES |
| 506 | (@option_group_id_conference_slot, {localize}'{ts escape="sql"}Morning Sessions{/ts}'{/localize}, 1, '{ts escape="sql"}Morning Sessions{/ts}', NULL, 0, NULL, 1, {localize}'{ts escape="sql"}NULL{/ts}'{/localize}, 0, 0, 1, NULL, NULL), |
| 507 | (@option_group_id_conference_slot, {localize}'{ts escape="sql"}Evening Sessions{/ts}'{/localize}, 2, '{ts escape="sql"}Evening Sessions{/ts}', NULL, 0, NULL, 2, {localize}'{ts escape="sql"}NULL{/ts}'{/localize}, 0, 0, 1, NULL, NULL); |
| 508 | |
| 509 | SELECT @msg_tpl_workflow_event := MAX(id) FROM civicrm_option_group WHERE name = 'msg_tpl_workflow_event'; |
| 510 | SELECT @weight := MAX(weight) + 1 FROM civicrm_option_value WHERE option_group_id = @msg_tpl_workflow_event; |
| 511 | |
| 512 | INSERT INTO civicrm_option_value |
| 513 | (option_group_id, name, {localize field='label'}label{/localize}, value, weight) |
| 514 | VALUES |
| 515 | (@msg_tpl_workflow_event, 'event_registration_receipt', {localize}'{ts escape="sql"}Events - Receipt only{/ts}'{/localize}, @weight, @weight); |
| 516 | |
| 517 | {* SELECT @tpl_ovid_$vName := MAX(id) FROM civicrm_option_value WHERE option_group_id = @tpl_ogid_$gName AND name = '$vName'; *} |
| 518 | {* INSERT INTO civicrm_msg_template *} |
| 519 | |
| 520 | -- CRM-8532 |
| 521 | UPDATE `civicrm_dashboard` SET url = REPLACE( url, 'snippet=4', 'snippet=5' ), fullscreen_url = REPLACE( fullscreen_url, 'snippet=4', 'snippet=5' ); |
| 522 | |
| 523 | {if $multilingual} |
| 524 | {foreach from=$locales item=locale} |
| 525 | ALTER TABLE civicrm_option_group CHANGE label_{$locale} title_{$locale} varchar(255); |
| 526 | {/foreach} |
| 527 | {else} |
| 528 | ALTER TABLE civicrm_option_group CHANGE label title varchar(255); |
| 529 | {/if} |