Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | -- CRM-6134 |
2 | ||
3 | {include file='../CRM/Upgrade/3.4.3.msg_template/civicrm_msg_template.tpl'} | |
4 | ||
5 | --added on behalf of organization profile | |
6 | ||
7 | INSERT INTO civicrm_uf_group | |
8 | ( name, group_type, {localize field='title'}title{/localize}, is_reserved ) | |
9 | ||
10 | VALUES | |
11 | ( 'on_behalf_organization', 'Contact,Organization,Contribution,Membership', {localize}'{ts escape="sql"}On Behalf Of Organization{/ts}'{/localize}, 1 ); | |
10824d34 | 12 | |
6a488035 TO |
13 | SELECT @uf_group_id_onBehalfOrganization := max(id) from civicrm_uf_group where name = 'on_behalf_organization'; |
14 | ||
15 | INSERT INTO civicrm_uf_join | |
16 | ( is_active, module, entity_table, entity_id, weight, uf_group_id ) | |
17 | ||
18 | VALUES | |
19 | ( 1, 'Profile', NULL, NULL, 7, @uf_group_id_onBehalfOrganization ); | |
20 | ||
21 | SELECT @maxId := id FROM civicrm_location_type WHERE name = 'Main'; | |
10824d34 | 22 | |
6a488035 TO |
23 | INSERT INTO civicrm_uf_field |
24 | ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, {localize field='label'}label{/localize}, field_type, {localize field='help_post'}help_post{/localize}, phone_type_id ) | |
25 | ||
26 | VALUES | |
10824d34 | 27 | ( @uf_group_id_onBehalfOrganization, 'organization_name', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, |
6a488035 | 28 | {localize}'Organization Name'{/localize}, 'Organization', {localize}NULL{/localize}, NULL ), |
10824d34 | 29 | ( @uf_group_id_onBehalfOrganization, 'phone', 1, 0, 2, 'User and User Admin Only', 0, 0, @maxId, |
6a488035 | 30 | {localize}'Phone (Main) '{/localize}, 'Contact', {localize}NULL{/localize}, 1 ), |
10824d34 | 31 | ( @uf_group_id_onBehalfOrganization, 'email', 1, 0, 3, 'User and User Admin Only', 0, 0, @maxId, |
6a488035 | 32 | {localize}'Email (Main) '{/localize}, 'Contact', {localize}NULL{/localize}, NULL ), |
10824d34 | 33 | ( @uf_group_id_onBehalfOrganization, 'street_address', 1, 0, 4, 'User and User Admin Only', 0, 0, @maxId, |
6a488035 | 34 | {localize}'Street Address'{/localize}, 'Contact', {localize}NULL{/localize}, NULL ), |
10824d34 | 35 | ( @uf_group_id_onBehalfOrganization, 'city', 1, 0, 5, 'User and User Admin Only', 0, 0, @maxId, |
6a488035 TO |
36 | {localize}'City'{/localize}, 'Contact', {localize}NULL{/localize}, NULL ), |
37 | ( @uf_group_id_onBehalfOrganization, 'postal_code', 1, 0, 6, 'User and User Admin Only', 0, 0, @maxId, | |
38 | {localize}'Postal Code'{/localize}, 'Contact', {localize}NULL{/localize}, NULL ), | |
39 | ( @uf_group_id_onBehalfOrganization, 'country', 1, 0, 7, 'User and User Admin Only', 0, 0, @maxId, | |
40 | {localize}'Country'{/localize}, 'Contact', {localize}NULL{/localize}, NULL ), | |
10824d34 | 41 | ( @uf_group_id_onBehalfOrganization, 'state_province', 1, 0, 8, 'User and User Admin Only', 0, 0, @maxId, |
757069de | 42 | {localize}'State/Province'{/localize}, 'Contact', {localize}NULL{/localize}, NULL ); |
6a488035 TO |
43 | |
44 | -- CRM-8150 | |
45 | CREATE TABLE IF NOT EXISTS `civicrm_action_mapping` ( | |
46 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
47 | `entity` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity for which the reminder is created', | |
48 | `entity_value` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value', | |
49 | `entity_value_label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value label', | |
50 | `entity_status` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status', | |
51 | `entity_status_label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status label', | |
52 | `entity_date_start` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date', | |
53 | `entity_date_end` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date', | |
54 | `entity_recipient` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity recipient', | |
55 | PRIMARY KEY (`id`) | |
56 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; | |
57 | ||
58 | ||
10824d34 | 59 | INSERT INTO civicrm_action_mapping |
60 | (entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient) | |
6a488035 | 61 | VALUES |
10824d34 | 62 | ('civicrm_activity', 'activity_type', 'Type', 'activity_status', 'Status', 'activity_date_time', NULL, 'activity_contacts'); |
6a488035 TO |
63 | |
64 | CREATE TABLE IF NOT EXISTS `civicrm_action_schedule` ( | |
65 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
66 | `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name of the action(reminder)', | |
67 | `title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the action(reminder)', | |
68 | `recipient` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Recipient', | |
69 | `entity_value` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value', | |
70 | `entity_status` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status', | |
71 | `start_action_offset` int(10) unsigned DEFAULT NULL COMMENT 'Reminder Interval.', | |
72 | `start_action_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units for reminder.', | |
73 | `start_action_condition` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Reminder Action', | |
74 | `start_action_date` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date', | |
75 | `is_repeat` tinyint(4) DEFAULT '0', | |
76 | `repetition_frequency_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units for repetition of reminder.', | |
77 | `repetition_frequency_interval` int(10) unsigned DEFAULT NULL COMMENT 'Time interval for repeating the reminder.', | |
78 | `end_frequency_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units till repetition of reminder.', | |
79 | `end_frequency_interval` int(10) unsigned DEFAULT NULL COMMENT 'Time interval till repeating the reminder.', | |
80 | `end_action` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Reminder Action till repeating the reminder.', | |
81 | `end_date` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity end date', | |
82 | `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this option active?', | |
83 | `recipient_manual` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Contact IDs to which reminder should be sent.', | |
84 | `body_text` longtext COLLATE utf8_unicode_ci COMMENT 'Body of the mailing in text format.', | |
85 | `body_html` longtext COLLATE utf8_unicode_ci COMMENT 'Body of the mailing in html format.', | |
86 | `subject` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Subject of mailing', | |
87 | `record_activity` tinyint(4) DEFAULT NULL COMMENT 'Record Activity for this reminder?', | |
88 | `mapping_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to mapping which is being used by this reminder', | |
89 | `group_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Group', | |
90 | `msg_template_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to the message template.', | |
91 | PRIMARY KEY (`id`), | |
92 | CONSTRAINT `FK_civicrm_action_schedule_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES civicrm_action_mapping(id) ON DELETE SET NULL, | |
93 | CONSTRAINT `FK_civicrm_action_schedule_group_id` FOREIGN KEY (`group_id`) REFERENCES civicrm_group(id) ON DELETE SET NULL, | |
94 | CONSTRAINT `FK_civicrm_action_schedule_msg_template_id` FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template` (`id`) ON DELETE SET NULL | |
95 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; | |
96 | ||
97 | ||
98 | INSERT INTO civicrm_option_group | |
99 | (name, {localize field='description'}description{/localize}, is_reserved, is_active) | |
100 | VALUES | |
101 | ('activity_contacts', {localize}'{ts escape="sql"}Activity Contacts{/ts}'{/localize}, 0, 1); | |
102 | ||
103 | SELECT @option_group_id_aco := max(id) from civicrm_option_group where name = 'activity_contacts'; | |
104 | SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; | |
105 | SELECT @act_value := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
106 | SELECT @act_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; | |
107 | ||
108 | -- CRM-8209 | |
109 | SELECT @option_group_id_adv_search_opts := max(id) from civicrm_option_group where name = 'advanced_search_options'; | |
110 | ||
10824d34 | 111 | INSERT INTO civicrm_option_value |
112 | (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) | |
6a488035 TO |
113 | VALUES |
114 | (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Assignees{/ts}'{/localize}, 1, 'Activity Assignees', NULL, 0, NULL, 1, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL), | |
115 | (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Source{/ts}'{/localize}, 2, 'Activity Source', NULL, 0, NULL, 2, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL), | |
116 | (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Targets{/ts}'{/localize}, 3, 'Activity Targets', NULL, 0, NULL, 3, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL), | |
117 | (@option_group_id_adv_search_opts, {localize}'{ts escape="sql"}Mailing{/ts}'{/localize}, '19', 'CiviMail', NULL, 0, NULL, 21, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL), | |
118 | (@option_group_id_act, {localize}'{ts escape="sql"}Reminder Sent{/ts}'{/localize}, @act_value+1, 'Reminder Sent', NULL, 0, NULL, @act_weight+1, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL); | |
119 | ||
120 | SELECT @domainID := min(id) FROM civicrm_domain; | |
121 | SELECT @configureID := max(id) FROM civicrm_navigation WHERE name = 'Configure'; | |
122 | SELECT @nav_c_wt := max(weight) from civicrm_navigation WHERE parent_id = @configureID; | |
123 | ||
124 | INSERT INTO civicrm_navigation | |
125 | ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight ) | |
126 | VALUES | |
127 | ( @domainID, 'civicrm/admin/scheduleReminders&reset=1', '{ts escape="sql" skip="true"}Schedule Reminders{/ts}', 'Schedule Reminders', 'administer CiviCRM', '', @configureID, '1', NULL, @nav_c_wt ); | |
128 | ||
129 | -- CRM-8148, rename uf field 'activity_status' to 'activity_status_id' | |
130 | UPDATE civicrm_uf_field SET field_name = 'activity_type_id' WHERE field_name= 'activity_type'; | |
131 | ||
132 | -- CRM-7988 allow negative start and end date offsets for custom fields | |
133 | ALTER TABLE civicrm_custom_field MODIFY start_date_years INT(10); | |
134 | ALTER TABLE civicrm_custom_field MODIFY end_date_years INT(10); | |
135 | ||
136 | -- CRM-8146 Supply names for existing dupe matching rules (now that name is required) | |
137 | UPDATE civicrm_dedupe_rule_group | |
138 | SET name = CONCAT(contact_type, '-', level, '-', id) | |
139 | WHERE name IS NULL; |