projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Merge pull request #4054 from eileenmcnaughton/CRM-15237
[civicrm-core.git] / CRM / Upgrade / Incremental / sql / 3.0.alpha1.mysql.tpl
CommitLineData
6a488035
TO		 1 SELECT @domain_id := min(id) FROM civicrm_domain;
2
3 -- CRM-4048
4 -- modify visibility of civicrm_group
10824d34 5 ALTER TABLE `civicrm_group`
6a488035
TO		 6 MODIFY `visibility` enum('User and User Admin Only','Public User Pages','Public User Pages and Listings', 'Public Pages') collate utf8_unicode_ci default 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.';
7
8 UPDATE civicrm_group SET visibility = 'Public Pages' WHERE visibility IN ('Public User Pages', 'Public User Pages and Listings');
9
10824d34 10 ALTER TABLE `civicrm_group`
6a488035
TO		 11 MODIFY `visibility` enum('User and User Admin Only', 'Public Pages') collate utf8_unicode_ci default 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.';
12
13 -- Modify visibility of civicrm_uf_field
10824d34 14 ALTER TABLE `civicrm_uf_field`
6a488035
TO		 15 MODIFY `visibility` enum('User and User Admin Only','Public User Pages','Public User Pages and Listings', 'Public Pages', 'Public Pages and Listings') collate utf8_unicode_ci default 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.';
16
17 UPDATE civicrm_uf_field SET visibility = 'Public Pages' WHERE visibility = 'Public User Pages';
18 UPDATE civicrm_uf_field SET visibility = 'Public Pages and Listings' WHERE visibility = 'Public User Pages and Listings';
19
10824d34 20 ALTER TABLE `civicrm_uf_field`
6a488035
TO		 21 MODIFY `visibility` enum('User and User Admin Only', 'Public Pages', 'Public Pages and Listings') collate utf8_unicode_ci default 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.';
22
23
24 -- CRM-3336
10824d34 25 -- Add two label_a_b and label_b_a column in civicrm_relationship_type table
6a488035
TO		 26 ALTER TABLE `civicrm_relationship_type`
27 ADD `label_a_b` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT 'label for relationship of contact_a to contact_b.' AFTER `name_a_b`,
28 ADD `label_b_a` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT 'Optional label for relationship of contact_b to contact_a.' AFTER `name_b_a`;
29
30 -- Copy value from name_a_b to label_a_b and name_b_a to label_b_a column in civicrm_relationship_type.
31 UPDATE civicrm_relationship_type
32 SET civicrm_relationship_type.label_a_b = civicrm_relationship_type.name_a_b, civicrm_relationship_type.label_b_a = civicrm_relationship_type.name_b_a;
33
10824d34 34 -- Alter comment of name_a_b and name_b_a column in civicrm_relationship_type table
6a488035
TO		 35 ALTER TABLE `civicrm_relationship_type`
36 CHANGE `name_a_b` `name_a_b` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'name for relationship of contact_a to contact_b.' ,
37 CHANGE `name_b_a` `name_b_a` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Optional name for relationship of contact_b to contact_a.';
38
39 -- CRM-3140
40 ALTER TABLE `civicrm_mapping_field`
41 ADD `im_provider_id` int(10) unsigned default NULL COMMENT 'Which type of IM Provider does this name belong' AFTER `phone_type_id`;
42
43
44 -- migrate participant status types, CRM-4321
10824d34 45
6a488035 46 -- /*******************************************************
10824d34 47 -- * civicrm_participant_status_type
48 -- * various types of CiviEvent participant statuses
6a488035
TO		 49 -- *******************************************************/
50 BEGIN;
51 CREATE TABLE civicrm_participant_status_type (
52 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique participant status type id',
53 name varchar(64) COMMENT 'non-localized name of the status type',
54 {localize field='label'}label varchar(255) COMMENT 'localized label for display of this status type'{/localize},
55 class enum('Positive', 'Pending', 'Waiting', 'Negative') COMMENT 'the general group of status type this one belongs to',
56 is_reserved tinyint COMMENT 'whether this is a status type required by the system',
57 is_active tinyint DEFAULT 1 COMMENT 'whether this status type is active',
58 is_counted tinyint COMMENT 'whether this status type is counted against event size limit',
59 weight int unsigned NOT NULL COMMENT 'controls sort order',
60 visibility_id int unsigned COMMENT 'whether the status type is visible to the public, an implicit foreign key to option_value.value related to the `visibility` option_group',
61 PRIMARY KEY ( id )
62 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
63
64 SELECT @ps_ogid := id FROM civicrm_option_group WHERE name = 'participant_status';
65 INSERT INTO civicrm_participant_status_type
66 (id, name, {localize field='label'}label{/localize}, is_reserved, is_active, is_counted, weight, visibility_id)
67 SELECT value, name, {localize field='label'}label{/localize}, is_reserved, is_active, filter, weight, visibility_id
68 FROM civicrm_option_value WHERE option_group_id = @ps_ogid;
69
70 UPDATE civicrm_participant_status_type
10824d34 71 SET class = CASE name
6a488035
TO		 72 WHEN 'Registered' THEN 'Positive'
73 WHEN 'Attended' THEN 'Positive'
74 WHEN 'No-show' THEN 'Negative'
75 WHEN 'Cancelled' THEN 'Negative'
76 ELSE 'Pending'
77 END;
10824d34 78
6a488035
TO		 79 UPDATE civicrm_participant_status_type
80 SET name = 'Pending from pay later', {localize field='label'}label = 'Pending from pay later'{/localize}
81 WHERE name = 'Pending';
82
83 INSERT INTO civicrm_participant_status_type
84 (name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id)
85 VALUES
86 ('Pending from incomplete transaction', {localize}'Pending from incomplete transaction'{/localize}, 'Pending', 1, 1, 1, 6, 2 ),
87 ('On waitlist', {localize}'On waitlist'{/localize}, 'Waiting', 1, 0, 0, 7, 2 ),
88 ('Awaiting approval', {localize}'Awaiting approval'{/localize}, 'Waiting', 1, 0, 1, 8, 2 ),
89 ('Pending from waitlist', {localize}'Pending from waitlist'{/localize}, 'Pending', 1, 0, 1, 9, 2 ),
90 ('Pending from approval', {localize}'Pending from approval'{/localize}, 'Pending', 1, 0, 1, 10, 2 ),
91 ('Rejected', {localize}'Rejected'{/localize}, 'Negative', 1, 0, 0, 11, 2 ),
92 ('Expired', {localize}'Expired'{/localize}, 'Negative', 1, 1, 0, 12, 2 );
93
94 -- CRM-4321 migration: Pending from pay later + false is_pay_later ==> Pending from incomplete transaction
95 SELECT @ps_ppl := id FROM civicrm_participant_status_type WHERE name = 'Pending from pay later';
96 SELECT @ps_pit := id FROM civicrm_participant_status_type WHERE name = 'Pending from incomplete transaction';
97 UPDATE civicrm_participant SET status_id = @ps_pit WHERE status_id = @ps_ppl AND is_pay_later = 0;
98
99 DELETE FROM civicrm_option_value WHERE option_group_id = @ps_ogid;
100 DELETE FROM civicrm_option_group WHERE id = @ps_ogid;
10824d34 101
6a488035
TO		 102 UPDATE civicrm_participant SET status_id = 1 WHERE status_id IS NULL;
103 ALTER TABLE `civicrm_participant`
104 CHANGE `status_id` `status_id` INT( 10 ) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Participant status ID. FK to civicrm_participant_status_type. Default of 1 should map to status = Registered.',
105 ADD CONSTRAINT FK_civicrm_participant_status_id FOREIGN KEY (status_id) REFERENCES civicrm_participant_status_type (id);
106
107 COMMIT;
108
109 -- Add is_reserved, name column to civicrm_uf_group table.
10824d34 110 ALTER TABLE `civicrm_uf_group`
6a488035
TO		 111 ADD `is_reserved` TINYINT( 4 ) NULL DEFAULT NULL COMMENT 'Is this group reserved for use by some other CiviCRM functionality?',
112 ADD `name` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT 'Name of the UF group for directly addressing it in the codebase';
113
114 -- Add is_reserved column to civicrm_uf_field.
10824d34 115 ALTER TABLE `civicrm_uf_field`
6a488035
TO		 116 ADD `is_reserved` TINYINT( 4 ) NULL DEFAULT NULL COMMENT 'Is this group reserved for use by some other CiviCRM functionality?';
117
118 -- add a profile for CRM-4323
119 BEGIN;
120 INSERT INTO civicrm_uf_group
121 (name, group_type, {localize field='title'}title{/localize}, is_reserved)
122 VALUES
123 ('participant_status', 'Participant', {localize}'Participant Status'{/localize}, 1);
124 SELECT @ufgid := id FROM civicrm_uf_group WHERE name = 'participant_status';
125 INSERT INTO civicrm_uf_field
126 (uf_group_id, field_name, is_required, is_reserved, {localize field='label'}label{/localize}, field_type)
127 VALUES
128 (@ufgid, 'participant_status_id', 1, 1, {localize}'Participant Status'{/localize}, 'Participant');
129 COMMIT;
130
131 -- CRM-4407
132 ALTER TABLE `civicrm_preferences` ADD `navigation` TEXT NULL AFTER `mailing_backend` ;
133
134 -- CRM-3553
135 -- Activity Type for bulk email
136 -- CRM-4480
137 -- Activity Type for case role assignment
138
139 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
140 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type;
141 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
142 SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
143
144 INSERT INTO civicrm_option_value
145 (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight, filter, component_id)
146 VALUES
147 (@option_group_id_activity_type, {localize}'Bulk Email'{/localize}, {localize}'Bulk Email Sent.'{/localize}, (SELECT @max_val := @max_val+1), 'Bulk Email', (SELECT @max_wt := @max_wt+1), 1, NULL),
148 (@option_group_id_activity_type, {localize}'Assign Case Role'{/localize}, {localize}''{/localize}, (SELECT @max_val := @max_val+2), 'Assign Case Role', (SELECT @max_wt := @max_wt+2), 0, @caseCompId),
149 (@option_group_id_activity_type, {localize}'Remove Case Role'{/localize}, {localize}''{/localize}, (SELECT @max_val := @max_val+3), 'Remove Case Role', (SELECT @max_wt := @max_wt+3), 0, @caseCompId);
150
151 -- CRM-5333
152 -- Drop unique indexes of activity_target and activity_assignment
10824d34 153
154 ALTER TABLE civicrm_activity_assignment
6a488035
TO		 155 DROP INDEX `UI_activity_assignee_contact_id` ,
156 ADD INDEX `UI_activity_assignee_contact_id` (`assignee_contact_id`,`activity_id`);
157
10824d34 158 ALTER TABLE civicrm_activity_target
6a488035
TO		 159 DROP INDEX `UI_activity_target_contact_id` ,
160 ADD INDEX `UI_activity_target_contact_id` (`target_contact_id`,`activity_id`);
161
162 -- delete unnecessary activities
163 SELECT @bulkEmailID := op.value from civicrm_option_value op where op.name = 'Bulk Email' and op.option_group_id = @option_group_id_activity_type;
164
165 UPDATE civicrm_activity ca
166 SET ca.activity_type_id = @bulkEmailID
167 WHERE ca.activity_type_id = 3
168 AND ca.source_record_id IS NOT NULL
169 AND ca.id NOT IN ( SELECT cca.activity_id FROM civicrm_case_activity cca );
170
171 -- CRM-4478 CRM-4569
172 INSERT INTO civicrm_option_group
173 (name, {localize field='description'}description{/localize}, is_reserved, is_active)
10824d34 174 VALUES
6a488035
TO		 175 ('priority', {localize}'Priority'{/localize}, 0, 1),
176 ('redaction_rule', {localize}'Redaction Rule'{/localize}, 0, 1),
177 ('email_greeting', {localize}'Email Greeting Type'{/localize}, 0, 1),
178 ('postal_greeting', {localize}'Postal Greeting Type'{/localize}, 0, 1),
179 ('addressee', {localize}'Addressee Type'{/localize}, 0, 1);
10824d34 180
6a488035
TO		 181 SELECT @og_id_pr := id FROM civicrm_option_group WHERE name = 'priority';
182 SELECT @og_id_rr := id FROM civicrm_option_group WHERE name = 'redaction_rule';
183 SELECT @og_id_rt := id FROM civicrm_option_group WHERE name = 'report_template';
184 INSERT INTO civicrm_option_value
185 (option_group_id, {localize field='label'}label{/localize}, value, name, filter, weight, is_active)
10824d34 186 VALUES
6a488035
TO		 187 (@og_id_pr, {localize}'Urgent'{/localize}, 1, 'Urgent', 0, 1, 1),
188 (@og_id_pr, {localize}'Normal'{/localize}, 2, 'Normal', 0, 2, 1),
189 (@og_id_pr, {localize}'Low'{/localize}, 3, 'Low', 0, 3, 1),
10824d34 190
6a488035
TO		 191 (@og_id_rr, {localize}'Vancouver'{/localize}, 'city_', NULL, 0, 1, 1),
192 (@og_id_rr, {localize}'{literal}/(19|20)(\\d{2})-(\\d{1,2})-(\\d{1,2})/{/literal}'{/localize}, 'date_', NULL, 1, 2, 1);
193
194
195 -- CRM-4461
196 -- Add a new custom html type advanced multi-select
197 -- CRM-4679
198 -- Add a new custom data type Auto-Complete & html type Contact Reference
10824d34 199 ALTER TABLE `civicrm_custom_field`
6a488035
TO		 200 MODIFY `data_type` enum ('String', 'Int', 'Float', 'Money', 'Memo', 'Date', 'Boolean', 'StateProvince', 'Country', 'File', 'Link', 'ContactReference')NOT NULL COMMENT 'Controls location of data storage in extended_data table.',
201 MODIFY `html_type` enum ('Text', 'TextArea', 'Select', 'Multi-Select', 'AdvMulti-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country', 'Multi-Select Country', 'Multi-Select State/Province', 'File', 'Link', 'RichTextEditor', 'Autocomplete-Select')NOT NULL COMMENT 'HTML types plus several built-in extended types.';
202
203 -- CRM-4407
204 -- Add civicrm_navigation table for CiviCRM Menu
205 CREATE TABLE civicrm_navigation (
206 id int unsigned NOT NULL AUTO_INCREMENT ,
207 domain_id int unsigned NOT NULL COMMENT 'Which Domain is this navigation item for',
208 label varchar(255) COMMENT 'Navigation Title',
209 name varchar(255) COMMENT 'Internal Name',
210 url varchar(255) COMMENT 'url in case of custom navigation link',
211 permission varchar(255) COMMENT 'Permission for menu item',
212 permission_operator varchar(3) COMMENT 'Permission Operator',
213 parent_id int unsigned COMMENT 'Parent navigation item, used for grouping',
214 is_active tinyint COMMENT 'Is this navigation item active?',
215 has_separator tinyint COMMENT 'If separator needs to be added after this menu item',
10824d34 216 weight int COMMENT 'Ordering of the navigation items in various blocks.'
6a488035
TO		 217 ,
218 PRIMARY KEY ( id )
10824d34 219 ,
220 CONSTRAINT FK_civicrm_navigation_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) ,
221 CONSTRAINT FK_civicrm_navigation_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_navigation(id) ON DELETE CASCADE
6a488035 222 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
10824d34 223
6a488035
TO		 224 -- Insert default menu to table
225 INSERT INTO civicrm_navigation
226 ( id, domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
227 VALUES
10824d34 228 ( 1, @domain_id, NULL, '{ts escape="sql"}Search...{/ts}', 'Search...', NULL, '', NULL, '1', NULL, 1 ),
229 ( 2, @domain_id, 'civicrm/contact/search&reset=1', '{ts escape="sql"}Find Contacts{/ts}', 'Find Contacts', NULL, '', '1', '1', NULL, 1 ),
230 ( 3, @domain_id, 'civicrm/contact/search/advanced&reset=1', '{ts escape="sql"}Find Contacts - Advanced Search{/ts}', 'Find Contacts - Advanced Search', NULL, '', '1', '1', NULL, 2 ),
231 ( 4, @domain_id, 'civicrm/contact/search/custom&csid=15&reset=1', '{ts escape="sql"}Full-text Search{/ts}', 'Full-text Search', NULL, '', '1', '1', NULL, 3 ),
232 ( 5, @domain_id, 'civicrm/contact/search/builder&reset=1', '{ts escape="sql"}Search Builder{/ts}', 'Search Builder', NULL, '', '1', '1', '1', 4 ),
233 ( 6, @domain_id, 'civicrm/case/search&reset=1', '{ts escape="sql"}Find Cases{/ts}', 'Find Cases', 'access CiviCase', '', '1', '1', NULL, 5 ),
234 ( 7, @domain_id, 'civicrm/contribute/search&reset=1', '{ts escape="sql"}Find Contributions{/ts}', 'Find Contributions', 'access CiviContribute', '', '1', '1', NULL, 6 ),
235 ( 8, @domain_id, 'civicrm/mailing&reset=1', '{ts escape="sql"}Find Mailings{/ts}', 'Find Mailings', 'access CiviMail', '', '1', '1', NULL, 7 ),
236 ( 9, @domain_id, 'civicrm/member/search&reset=1', '{ts escape="sql"}Find Members{/ts}', 'Find Members', 'access CiviMember', '', '1', '1', NULL, 8 ),
237 ( 10, @domain_id, 'civicrm/event/search&reset=1', '{ts escape="sql"}Find Participants{/ts}', 'Find Participants', 'access CiviEvent', '', '1', '1', NULL, 9 ),
238 ( 11, @domain_id, 'civicrm/pledge/search&reset=1', '{ts escape="sql"}Find Pledges{/ts}', 'Find Pledges', 'access CiviPledge', '', '1', '1', 1, 10 ),
239
240 ( 12, @domain_id, 'civicrm/contact/search/custom/list&reset=1', '{ts escape="sql"}Custom Searches...{/ts}', 'Custom Searches...', NULL, '', '1', '1', NULL, 11 ),
241 ( 13, @domain_id, 'civicrm/contact/search/custom&reset=1&csid=8', '{ts escape="sql"}Activity Search{/ts}', 'Activity Search', NULL, '', '12', '1', NULL, 1 ),
242 ( 14, @domain_id, 'civicrm/contact/search/custom&reset=1&csid=11', '{ts escape="sql"}Contacts by Date Added{/ts}', 'Contacts by Date Added', NULL, '', '12', '1', NULL, 2 ),
243 ( 15, @domain_id, 'civicrm/contact/search/custom&reset=1&csid=2', '{ts escape="sql"}Contributors by Aggregate Totals{/ts}', 'Contributors by Aggregate Totals', NULL, '', '12', '1', NULL, 3 ),
244 ( 16, @domain_id, 'civicrm/contact/search/custom&reset=1&csid=6', '{ts escape="sql"}Proximity Search{/ts}', 'Proximity Search', NULL, '', '12', '1', NULL, 4 ),
245
246 ( 17, @domain_id, NULL, '{ts escape="sql"}Contacts{/ts}', 'Contacts', NULL, '', NULL, '1', NULL, 3 ),
247 ( 18, @domain_id, 'civicrm/contact/add&reset=1&ct=Individual', '{ts escape="sql"}New Individual{/ts}', 'New Individual', NULL, '', '17', '1', NULL, 1 ),
248 ( 19, @domain_id, 'civicrm/contact/add&reset=1&ct=Household', '{ts escape="sql"}New Household{/ts}', 'New Household', NULL, '', '17', '1', NULL, 2 ),
249 ( 20, @domain_id, 'civicrm/contact/add&reset=1&ct=Organization', '{ts escape="sql"}New Organization{/ts}', 'New Organization', NULL, '', '17', '1', 1, 3 ),
250 ( 21, @domain_id, 'civicrm/activity&reset=1&action=add&context=standalone', '{ts escape="sql"}New Activity{/ts}', 'New Activity', NULL, '', '17', '1', NULL, 4 ),
251 ( 22, @domain_id, 'civicrm/contact/view/activity&atype=3&action=add&reset=1&context=standalone', '{ts escape="sql"}New Email{/ts}', 'New Email', NULL, '', '17', '1', '1', 5 ),
252 ( 23, @domain_id, 'civicrm/import/contact&reset=1', '{ts escape="sql"}Import Contacts{/ts}', 'Import Contacts', NULL, '', '17', '1', NULL, 6 ),
253 ( 24, @domain_id, 'civicrm/import/activity&reset=1', '{ts escape="sql"}Import Activities{/ts}', 'Import Activities', NULL, '', '17', '1', '1', 7 ),
254 ( 25, @domain_id, 'civicrm/group/add&reset=1', '{ts escape="sql"}New Group{/ts}', 'New Group', NULL, '', '17', '1', NULL, 8 ),
255 ( 26, @domain_id, 'civicrm/group&reset=1', '{ts escape="sql"}Manage Groups{/ts}', 'Manage Groups', NULL, '', '17', '1', '1', 9 ),
256 ( 27, @domain_id, 'civicrm/admin/tag&reset=1&action=add', '{ts escape="sql"}New Tag{/ts}', 'New Tag', NULL, '', '17', '1', NULL, 10 ),
257 ( 28, @domain_id, 'civicrm/admin/tag&reset=1', '{ts escape="sql"}Manage Tags (Categories){/ts}', 'Manage Tags (Categories)', NULL, '', '17', '1', NULL, 11 ),
258
259 ( 29, @domain_id, NULL, '{ts escape="sql"}Contributions{/ts}', 'Contributions', 'access CiviContribute', '', NULL, '1', NULL, 4 ),
260 ( 30, @domain_id, 'civicrm/contribute&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', NULL, '', '29', '1', NULL, 1 ),
261 ( 31, @domain_id, 'civicrm/contact/view/contribution&reset=1&action=add&context=standalone', '{ts escape="sql"}New Contribution{/ts}', 'New Contribution', NULL, '', '29', '1', NULL, 2 ),
262 ( 32, @domain_id, 'civicrm/contribute/search&reset=1', '{ts escape="sql"}Find Contributions{/ts}', 'Find Contributions', NULL, '', '29', '1', NULL, 3 ),
6a488035 263 ( 33, @domain_id, 'civicrm/contribute/import&reset=1', '{ts escape="sql"}Import Contributions{/ts}', 'Import Contributions', NULL, '', '29', '1', '1', 4 ),
10824d34 264
265 ( 34, @domain_id, NULL, '{ts escape="sql"}Pledges{/ts}', 'Pledges', 'access CiviPledge', '', 29, '1', 1, 5 ),
266 ( 35, @domain_id, 'civicrm/pledge&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', NULL, '', '34', '1', NULL, 1 ),
267 ( 36, @domain_id, 'civicrm/pledge/search&reset=1', '{ts escape="sql"}Find Pledges{/ts}', 'Find Pledges', NULL, '', '34', '1', NULL, 2 ),
268 ( 37, @domain_id, 'civicrm/admin/contribute&reset=1&action=add', '{ts escape="sql"}New Contribution Page{/ts}', 'New Contribution Page', NULL, '', '29', '1', NULL, 6 ),
269 ( 38, @domain_id, 'civicrm/admin/contribute&reset=1', '{ts escape="sql"}Manage Contribution Pages{/ts}', 'Manage Contribution Pages', NULL, '', '29', '1', '1', 7 ),
270 ( 39, @domain_id, 'civicrm/admin/pcp&reset=1', '{ts escape="sql"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', NULL, '', '29', '1', NULL, 8 ),
271 ( 40, @domain_id, 'civicrm/admin/contribute/managePremiums&reset=1', '{ts escape="sql"}Premiums (Thank-you Gifts){/ts}', 'Premiums', NULL, '', '29', '1', NULL, 9 ),
272
273 ( 41, @domain_id, NULL, '{ts escape="sql"}Events{/ts}', 'Events', 'access CiviEvent', '', NULL, '1', NULL, 5 ),
274 ( 42, @domain_id, 'civicrm/event&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'CiviEvent Dashboard', NULL, '', '41', '1', NULL, 1 ),
275 ( 43, @domain_id, 'civicrm/contact/view/participant&reset=1&action=add&context=standalone', '{ts escape="sql"}Register Event Participant{/ts}', 'Register Event Participant', NULL, '', '41', '1', NULL, 2 ),
276 ( 44, @domain_id, 'civicrm/event/search&reset=1', '{ts escape="sql"}Find Participants{/ts}', 'Find Participants', NULL, '', '41', '1', NULL, 3 ),
277 ( 45, @domain_id, 'civicrm/event/import&reset=1', '{ts escape="sql"}Import Participants{/ts}', 'Import Participants', NULL, '', '41', '1', '1', 4 ),
278 ( 46, @domain_id, 'civicrm/event/add&reset=1&action=add', '{ts escape="sql"}New Event{/ts}', 'New Event', NULL, '', '41', '1', NULL, 5 ),
279 ( 47, @domain_id, 'civicrm/event/manage&reset=1', '{ts escape="sql"}Manage Events{/ts}', 'Manage Events', NULL, '', '41', '1', 1, 6 ),
280 ( 48, @domain_id, 'civicrm/admin/eventTemplate&reset=1', '{ts escape="sql"}Event Templates{/ts}', 'Event Templates', 'access CiviEvent,administer CiviCRM', '', '41', '1', 1, 7 ),
281 ( 49, @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}', 'New Price Set', NULL, '', '41', '1', NULL, 8 ),
6a488035 282 ( 50, @domain_id, 'civicrm/event/price&reset=1', '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', NULL, '', '41', '1', NULL, 9 ),
10824d34 283
284 ( 51, @domain_id, NULL, '{ts escape="sql"}Mailings{/ts}', 'Mailings', 'access CiviMail', '', NULL, '1', NULL, 6 ),
285 ( 52, @domain_id, 'civicrm/mailing/send&reset=1', '{ts escape="sql"}New Mailing{/ts}', 'New Mailing', NULL, '', '51', '1', NULL, 1 ),
286 ( 53, @domain_id, 'civicrm/mailing/browse/unscheduled&reset=1&scheduled=false', '{ts escape="sql"}Draft and Unscheduled Mailings{/ts}', 'Draft and Unscheduled Mailings', NULL, '', '51', '1', NULL, 2 ),
287 ( 54, @domain_id, 'civicrm/mailing/browse/scheduled&reset=1&scheduled=true', '{ts escape="sql"}Scheduled and Sent Mailings{/ts}', 'Scheduled and Sent Mailings', NULL, '', '51', '1', NULL, 3 ),
288 ( 55, @domain_id, 'civicrm/mailing/browse/archived&reset=1', '{ts escape="sql"}Archived Mailings{/ts}', 'Archived Mailings', NULL, '', '51', '1', 1, 4 ),
289 ( 56, @domain_id, 'civicrm/admin/component&reset=1', '{ts escape="sql"}Headers, Footers, and Automated Messages{/ts}', 'Headers, Footers, and Automated Messages', NULL, '', '51', '1', NULL, 5 ),
290 ( 57, @domain_id, 'civicrm/admin/messageTemplates&reset=1', '{ts escape="sql"}Message Templates{/ts}', 'Message Templates', NULL, '', '51', '1', NULL, 6 ),
291 ( 58, @domain_id, 'civicrm/admin/options/from_email&group=from_email_address&reset=1', '{ts escape="sql"}From Email Addresses{/ts}', 'From Email Addresses', NULL, '', '51', '1', NULL, 7 ),
292
293 ( 59, @domain_id, NULL, '{ts escape="sql"}Memberships{/ts}', 'Memberships', 'access CiviMember', '', NULL, '1', NULL, 7 ),
294 ( 60, @domain_id, 'civicrm/member&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', NULL, '', '59', '1', NULL, 1 ),
295 ( 61, @domain_id, 'civicrm/contact/view/membership&reset=1&action=add&context=standalone', '{ts escape="sql"}New Membership{/ts}', 'New Membership', NULL, '', '59', '1', NULL, 2 ),
296 ( 62, @domain_id, 'civicrm/member/search&reset=1', '{ts escape="sql"}Find Members{/ts}', 'Find Members', NULL, '', '59', '1', NULL, 3 ),
297 ( 63, @domain_id, 'civicrm/member/import&reset=1', '{ts escape="sql"}Import Members{/ts}', 'Import Members', NULL, '', '59', '1', NULL, 4 ),
298
299 ( 64, @domain_id, NULL, '{ts escape="sql"}Other{/ts}', 'Other', 'access CiviGrant,access CiviCase', 'OR', NULL, '1', NULL, 8 ),
300 ( 65, @domain_id, NULL, '{ts escape="sql"}Cases{/ts}', 'Cases', 'access CiviCase', '', '64', '1', NULL, 1 ),
301 ( 66, @domain_id, 'civicrm/case&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', NULL, '', '65', '1', NULL, 1 ),
302 ( 67, @domain_id, 'civicrm/contact/view/case&reset=1&action=add&atype=13&context=standalone', '{ts escape="sql"}New Case{/ts}', 'New Case', NULL, '', '65', '1', NULL, 2 ),
303 ( 68, @domain_id, 'civicrm/case/search&reset=1', '{ts escape="sql"}Find Cases{/ts}', 'Find Cases', NULL, '', '65', '1', 1, 3 ),
304
6a488035 305 ( 69, @domain_id, NULL, '{ts escape="sql"}Grants{/ts}', 'Grants', 'access CiviGrant', '', '64', '1', NULL, 2 ),
10824d34 306 ( 70, @domain_id, 'civicrm/grant&reset=1', '{ts escape="sql"}Dashboard{/ts}', 'Dashboard', NULL, '', '69', '1', NULL, 1 ),
307 ( 71, @domain_id, 'civicrm/contact/view/grant&reset=1&action=add&context=standalone', '{ts escape="sql"}New Grant{/ts}', 'New Grant', NULL, '', '69', '1', NULL, 2 ),
308 ( 72, @domain_id, 'civicrm/grant/search&reset=1', '{ts escape="sql"}Find Grants{/ts}', 'Find Grants', NULL, '', '69', '1', 1, 3 ),
309
310 ( 73, @domain_id, NULL, '{ts escape="sql"}Administer{/ts}', 'Administer', 'administer CiviCRM', '', NULL, '1', NULL, 9 ),
311 ( 74, @domain_id, 'civicrm/admin&reset=1', '{ts escape="sql"}Administration Console{/ts}', 'Administration Console', NULL, '', '73', '1', NULL, 1 ),
312
313 ( 75, @domain_id, NULL, '{ts escape="sql"}Customize{/ts}', 'Customize', NULL, '', '73', '1', NULL, 2 ),
314 ( 76, @domain_id, 'civicrm/admin/custom/group&reset=1', '{ts escape="sql"}Custom Data{/ts}', 'Custom Data', NULL, '', '75', '1', NULL, 1 ),
315 ( 77, @domain_id, 'civicrm/admin/uf/group&reset=1', '{ts escape="sql"}CiviCRM Profile{/ts}', 'CiviCRM Profile', NULL, '', '75', '1', NULL, 2 ),
316 ( 78, @domain_id, 'civicrm/admin/menu&reset=1', '{ts escape="sql"}Navigation Menu{/ts}', 'Navigation Menu', NULL, '', '75', '1', NULL, 3 ),
317 ( 79, @domain_id, 'civicrm/admin/options/custom_search&reset=1&group=custom_search', '{ts escape="sql"}Manage Custom Searches{/ts}', 'Manage Custom Searches', NULL, '', '75', '1', NULL, 4 ),
318
319 ( 80, @domain_id, NULL, '{ts escape="sql"}Configure{/ts}', 'Configure', NULL, '', '73', '1', NULL, 3 ),
320 ( 81, @domain_id, 'civicrm/admin/configtask&reset=1', '{ts escape="sql"}Configuration Checklist{/ts}', 'Configuration Checklist', NULL, '', '80', '1', NULL, 1 ),
321
322 ( 82, @domain_id, 'civicrm/admin/setting&reset=1', '{ts escape="sql"}Global Settings{/ts}', 'Global Settings', NULL, '', '80', '1', NULL, 2 ),
323 ( 83, @domain_id, 'civicrm/admin/setting/component&reset=1', '{ts escape="sql"}Enable CiviCRM Components{/ts}', 'Enable Components', NULL, '', '82', '1', NULL, 1 ),
324 ( 84, @domain_id, 'civicrm/admin/setting/preferences/display&reset=1', '{ts escape="sql"}Site Preferences (screen and form configuration){/ts}', 'Site Preferences', NULL, '', '82', '1', NULL, 2 ),
325 ( 85, @domain_id, 'civicrm/admin/setting/path&reset=1', '{ts escape="sql"}Directories{/ts}', 'Directories', NULL, '', '82', '1', NULL, 3 ),
326 ( 86, @domain_id, 'civicrm/admin/setting/url&reset=1', '{ts escape="sql"}Resource URLs{/ts}', 'Resource URLs', NULL, '', '82', '1', NULL, 4 ),
327 ( 87, @domain_id, 'civicrm/admin/setting/smtp&reset=1', '{ts escape="sql"}Outbound Email (SMTP/Sendmail){/ts}', 'Outbound Email', NULL, '', '82', '1', NULL, 5 ),
328 ( 88, @domain_id, 'civicrm/admin/setting/mapping&reset=1', '{ts escape="sql"}Mapping and Geocoding{/ts}', 'Mapping and Geocoding', NULL, '', '82', '1', NULL, 6 ),
329 ( 89, @domain_id, 'civicrm/admin/paymentProcessor&reset=1', '{ts escape="sql"}Payment Processors{/ts}', 'Payment Processors', NULL, '', '82', '1', NULL, 7 ),
330 ( 90, @domain_id, 'civicrm/admin/setting/localization&reset=1', '{ts escape="sql"}Localization{/ts}', 'Localization', NULL, '', '82', '1', NULL, 8 ),
331 ( 91, @domain_id, 'civicrm/admin/setting/preferences/address&reset=1', '{ts escape="sql"}Address Settings{/ts}', 'Address Settings', NULL, '', '82', '1', NULL, 9 ),
332 ( 92, @domain_id, 'civicrm/admin/setting/date&reset=1', '{ts escape="sql"}Date Formats{/ts}', 'Date Formats', NULL, '', '82', '1', NULL, 10 ),
333 ( 93, @domain_id, 'civicrm/admin/setting/uf&reset=1', '{ts escape="sql"}CMS Integration{/ts}', 'CMS Integration', NULL, '', '82', '1', NULL, 11 ),
334 ( 94, @domain_id, 'civicrm/admin/setting/misc&reset=1', '{ts escape="sql"}Miscellaneous (version check, search, reCAPTCHA...){/ts}', 'Miscellaneous', NULL, '', '82', '1', NULL, 12 ),
335 ( 95, @domain_id, 'civicrm/admin/options/safe_file_extension&group=safe_file_extension&reset=1', '{ts escape="sql"}Safe File Extensions{/ts}', 'Safe File Extensions', NULL, '', '82', '1', NULL, 13 ),
336 ( 96, @domain_id, 'civicrm/admin/setting/debug&reset=1', '{ts escape="sql"}Debugging{/ts}', 'Debugging', NULL, '', '82', '1', NULL, 14 ),
337
338 ( 97, @domain_id, 'civicrm/admin/mapping&reset=1', '{ts escape="sql"}Import/Export Mappings{/ts}', 'Import/Export Mappings', NULL, '', '80', '1', NULL, 3 ),
339 ( 98, @domain_id, 'civicrm/admin/messageTemplates&reset=1', '{ts escape="sql"}Message Templates{/ts}', 'Message Templates', NULL, '', '80', '1', NULL, 4 ),
340 ( 99, @domain_id, 'civicrm/admin/domain&action=update&reset=1', '{ts escape="sql"}Domain Information{/ts}', 'Domain Information', NULL, '', '80', '1', NULL, 5 ),
341 ( 100,@domain_id, 'civicrm/admin/options/from_email_address&group=from_email_address&reset=1', '{ts escape="sql"}FROM Email Addresses{/ts}', 'FROM Email Addresses', NULL, '', '80', '1', NULL, 6 ),
342 ( 101,@domain_id, 'civicrm/admin/setting/updateConfigBackend&reset=1', '{ts escape="sql"}Update Directory Path and URL{/ts}', 'Update Directory Path and URL', NULL, '', '80', '1', NULL, 7 ),
343
344 ( 102, @domain_id, NULL, '{ts escape="sql"}Manage{/ts}', 'Manage', NULL, '', '73', '1', NULL, 4 ),
345 ( 103, @domain_id, 'civicrm/admin/deduperules&reset=1', '{ts escape="sql"}Find and Merge Duplicate Contacts{/ts}', 'Find and Merge Duplicate Contacts', '', '', '102', '1', NULL, 1 ),
346 ( 104, @domain_id, 'civicrm/admin/access&reset=1', '{ts escape="sql"}Access Control{/ts}', 'Access Control', NULL, '', '102', '1', NULL, 2 ),
347 ( 105, @domain_id, 'civicrm/admin/synchUser&reset=1', '{ts escape="sql"}Synchronize Users to Contacts{/ts}', 'Synchronize Users to Contacts', NULL, '', '102', '1', NULL, 3 ),
348
349 ( 106, @domain_id, NULL, '{ts escape="sql"}Option Lists{/ts}', 'Option Lists', NULL, '', '73', '1', NULL, 5 ),
350 ( 107, @domain_id, 'civicrm/admin/options/activity_type&reset=1&group=activity_type', '{ts escape="sql"}Activity Types{/ts}', 'Activity Types', NULL, '', '106', '1', NULL, 1 ),
351 ( 108, @domain_id, 'civicrm/admin/reltype&reset=1', '{ts escape="sql"}Relationship Types{/ts}', 'Relationship Types', NULL, '', '106', '1', NULL, 2 ),
352 ( 109, @domain_id, 'civicrm/admin/tag&reset=1', '{ts escape="sql"}Tags (Categories){/ts}', 'Tags (Categories)', NULL, '', '106', '1', 1, 3 ),
353 ( 110, @domain_id, 'civicrm/admin/options/gender&reset=1&group=gender', '{ts escape="sql"}Gender Options{/ts}', 'Gender Options', NULL, '', '106', '1', NULL, 4 ),
354 ( 111, @domain_id, 'civicrm/admin/options/individual_prefix&group=individual_prefix&reset=1', '{ts escape="sql"}Individual Prefixes (Ms, Mr...){/ts}', 'Individual Prefixes (Ms, Mr...)', NULL, '', '106', '1', NULL, 5 ),
355 ( 112, @domain_id, 'civicrm/admin/options/individual_suffix&group=individual_suffix&reset=1', '{ts escape="sql"}Individual Suffixes (Jr, Sr...){/ts}', 'Individual Suffixes (Jr, Sr...)', NULL, '', '106', '1', 1, 6 ),
356 ( 113, @domain_id, 'civicrm/admin/options/addressee&group=addressee&reset=1', '{ts escape="sql"}Addressee Formats{/ts}', 'Addressee Formats', NULL, '', '106', '1', NULL, 7 ),
357 ( 114, @domain_id, 'civicrm/admin/options/email_greeting&group=email_greeting&reset=1', '{ts escape="sql"}Email Greetings{/ts}', 'Email Greetings', NULL, '', '106', '1', NULL, 8 ),
358 ( 115, @domain_id, 'civicrm/admin/options/postal_greeting&group=postal_greeting&reset=1', '{ts escape="sql"}Postal Greetings{/ts}', 'Postal Greetings', NULL, '', '106', '1', 1, 9 ),
359 ( 116, @domain_id, 'civicrm/admin/options/instant_messenger_service&group=instant_messenger_service&reset=1', '{ts escape="sql"}Instant Messenger Services{/ts}', 'Instant Messenger Services', NULL, '', '106', '1', NULL, 10 ),
360 ( 117, @domain_id, 'civicrm/admin/locationType&reset=1', '{ts escape="sql"}Location Types (Home, Work...){/ts}', 'Location Types (Home, Work...)', NULL, '', '106', '1', NULL, 11 ),
361 ( 118, @domain_id, 'civicrm/admin/options/mobile_provider&group=mobile_provider&reset=1', '{ts escape="sql"}Mobile Phone Providers{/ts}', 'Mobile Phone Providers', NULL, '', '106', '1', NULL, 12 ),
362 ( 119, @domain_id, 'civicrm/admin/options/phone_type&group=phone_type&reset=1', '{ts escape="sql"}Phone Types{/ts}', 'Phone Types', NULL, '', '106', '1', NULL, 13 ),
363 ( 120, @domain_id, 'civicrm/admin/options/preferred_communication_method&group=preferred_communication_method&reset=1', '{ts escape="sql"}Preferred Communication Methods{/ts}', 'Preferred Communication Methods', NULL, '', '106', '1', NULL, 14 ),
364
365 ( 121, @domain_id, NULL, '{ts escape="sql"}CiviCase{/ts}', 'CiviCase', 'access CiviCase,administer CiviCRM', 'AND', '73', '1', NULL, 6 ),
366 ( 122, @domain_id, 'civicrm/admin/options/case_type&group=case_type&reset=1', '{ts escape="sql"}Case Types{/ts}', 'Case Types', 'access CiviCase,administer CiviCRM', '', '121', '1', NULL, 1 ),
367 ( 123, @domain_id, 'civicrm/admin/options/redaction_rule&group=redaction_rule&reset=1', '{ts escape="sql"}Redaction Rules{/ts}', 'Redaction Rules', 'access CiviCase,administer CiviCRM', '', '121', '1', NULL, 2 ),
368
369 ( 124, @domain_id, NULL, '{ts escape="sql"}CiviContribute{/ts}', 'CiviContribute', 'access CiviContribute,administer CiviCRM', 'AND', '73', '1', NULL, 7 ),
370 ( 125, @domain_id, 'civicrm/admin/contribute&reset=1&action=add', '{ts escape="sql"}New Contribution Page{/ts}', 'New Contribution Page', NULL, '', '124', '1', NULL, 6 ),
371 ( 126, @domain_id, 'civicrm/admin/contribute&reset=1', '{ts escape="sql"}Manage Contribution Pages{/ts}', 'Manage Contribution Pages', NULL, '', '124', '1', '1', 7 ),
372 ( 127, @domain_id, 'civicrm/admin/pcp&reset=1', '{ts escape="sql"}Personal Campaign Pages{/ts}', 'Personal Campaign Pages', NULL, '', '124', '1', NULL, 8 ),
373 ( 128, @domain_id, 'civicrm/admin/contribute/managePremiums&reset=1', '{ts escape="sql"}Premiums (Thank-you Gifts){/ts}', 'Premiums', NULL, '', '124', '1', 1, 9 ),
374 ( 129, @domain_id, 'civicrm/admin/contribute/contributionType&reset=1', '{ts escape="sql"}Contribution Types{/ts}', 'Contribution Types', NULL, '', '124', '1', NULL, 10 ),
375 ( 130, @domain_id, 'civicrm/admin/options/payment_instrument&group=payment_instrument&reset=1', '{ts escape="sql"}Payment Instruments{/ts}', 'Payment Instruments', NULL, '', '124', '1', NULL, 11 ),
376 ( 131, @domain_id, 'civicrm/admin/options/accept_creditcard&group=accept_creditcard&reset=1', '{ts escape="sql"}Accepted Credit Cards{/ts}', 'Accepted Credit Cards', NULL, '', '124', '1', NULL, 12 ),
377
378 ( 132, @domain_id, NULL, '{ts escape="sql"}CiviEvent{/ts}', 'CiviEvent', 'access CiviEvent,administer CiviCRM', 'AND', '73', '1', NULL, 8 ),
379 ( 133, @domain_id, 'civicrm/event/add&reset=1&action=add', '{ts escape="sql"}New Event{/ts}', 'New Event', NULL, '', '132', '1', NULL, 1 ),
380 ( 134, @domain_id, 'civicrm/event/manage&reset=1', '{ts escape="sql"}Manage Events{/ts}', 'Manage Events', NULL, '', '132', '1', 1, 2 ),
381 ( 135, @domain_id, 'civicrm/admin/eventTemplate&reset=1', '{ts escape="sql"}Event Templates{/ts}', 'Event Templates', 'access CiviEvent,administer CiviCRM', '', '132', '1', 1, 3 ),
382 ( 136, @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}', 'New Price Set', NULL, '', '132', '1', NULL, 4 ),
6a488035 383 ( 137, @domain_id, 'civicrm/event/price&reset=1', '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', NULL, '', '132', '1', 1, 5 ),
10824d34 384 ( 138, @domain_id, 'civicrm/admin/options/participant_listing&group=participant_listing&reset=1', '{ts escape="sql"}Participant Listing Templates{/ts}', 'Participant Listing Templates', NULL, '', '132', '1', NULL, 6 ),
385 ( 139, @domain_id, 'civicrm/admin/options/event_type&group=event_type&reset=1', '{ts escape="sql"}Event Types{/ts}', 'Event Types', NULL, '', '132', '1', NULL, 7 ),
386 ( 140, @domain_id, 'civicrm/admin/participant_status&reset=1', '{ts escape="sql"}Participant Statuses{/ts}', 'Participant Statuses', NULL, '', '132', '1', NULL, 8 ),
387 ( 141, @domain_id, 'civicrm/admin/options/participant_role&group=participant_role&reset=1', '{ts escape="sql"}Participant Roles{/ts}', 'Participant Roles', NULL, '', '132', '1', NULL, 9 ),
388
389 ( 142, @domain_id, NULL, '{ts escape="sql"}CiviGrant{/ts}', 'CiviGrant', 'access CiviGrant,administer CiviCRM', 'AND', '73', '1', NULL, 9 ),
390 ( 143, @domain_id, 'civicrm/admin/options/grant_type&group=grant_type&reset=1', '{ts escape="sql"}Grant Types{/ts}', 'Grant Types', 'access CiviGrant,administer CiviCRM', '', '142', '1', NULL, 1 ),
391
392 ( 144, @domain_id, NULL, '{ts escape="sql"}CiviMail{/ts}', 'CiviMail', 'access CiviMail,administer CiviCRM', 'AND', '73', '1', NULL, 10 ),
393 ( 145, @domain_id, 'civicrm/admin/component&reset=1', '{ts escape="sql"}Headers, Footers, and Automated Messages{/ts}', 'Headers, Footers, and Automated Messages', NULL, '', '144', '1', NULL, 1 ),
394 ( 146, @domain_id, 'civicrm/admin/messageTemplates&reset=1', '{ts escape="sql"}Message Templates{/ts}', 'Message Templates', NULL, '', '144', '1', NULL, 2 ),
395 ( 147, @domain_id, 'civicrm/admin/options/from_email&group=from_email_address&reset=1', '{ts escape="sql"}From Email Addresses{/ts}', 'From Email Addresses', NULL, '', '144', '1', NULL, 3 ),
396 ( 148, @domain_id, 'civicrm/admin/mailSettings&reset=1', '{ts escape="sql"}Mail Accounts{/ts}', 'Mail Accounts', NULL, '', '144', '1', NULL, 4 ),
397
398 ( 149, @domain_id, NULL, '{ts escape="sql"}CiviMember{/ts}', 'CiviMember', 'access CiviMember,administer CiviCRM', 'AND', '73', '1', NULL, 11 ),
399 ( 150, @domain_id, 'civicrm/admin/member/membershipType&reset=1', '{ts escape="sql"}Membership Types{/ts}', 'Membership Types', 'access CiviMember,administer CiviCRM', '', '149', '1', NULL, 1 ),
400 ( 151, @domain_id, 'civicrm/admin/member/membershipStatus&reset=1', '{ts escape="sql"}Membership Status Rules{/ts}', 'Membership Status Rules', 'access CiviMember,administer CiviCRM', '', '149', '1', NULL, 2 ),
401
402 ( 152, @domain_id, NULL, '{ts escape="sql"}CiviReport{/ts}', 'CiviReport', 'access CiviReport,administer CiviCRM', 'AND', '73', '1', NULL, 12 ),
403 ( 153, @domain_id, 'civicrm/report/list&reset=1', '{ts escape="sql"}Reports Listing{/ts}', 'Reports Listing', NULL, '', '152', '1', NULL, 1 ),
404 ( 154, @domain_id, 'civicrm/admin/report/template/list&reset=1', '{ts escape="sql"}Create Reports from Templates{/ts}', 'Create Reports from Templates', NULL, '', '152', '1', NULL, 2 ),
405 ( 155, @domain_id, 'civicrm/admin/report/options/report_template&reset=1', '{ts escape="sql"}Manage Templates{/ts}', 'Manage Templates', NULL, '', '152', '1', NULL, 3 ),
6a488035
TO		 406 ( 156, @domain_id, 'civicrm/admin/report/register&reset=1', '{ts escape="sql"}Register Report{/ts}', 'Register Report', 'administer Reports', '', '152', '1', NULL, 4 ),
407
10824d34 408 ( 157, @domain_id, NULL, '{ts escape="sql"}Help{/ts}', 'Help', NULL, '', NULL, '1', NULL, 10),
409 ( 158, @domain_id, 'http://documentation.civicrm.org', '{ts escape="sql"}Documentation{/ts}', 'Documentation', NULL, 'AND', '157', '1', NULL, 1 ),
410 ( 159, @domain_id, 'http://forum.civicrm.org', '{ts escape="sql"}Community Forums{/ts}', 'Community Forums', NULL, 'AND', '157', '1', NULL, 2 ),
411 ( 160, @domain_id, 'http://civicrm.org/participate', '{ts escape="sql"}Participate{/ts}', 'Participate', NULL, 'AND', '157', '1', NULL, 3 ),
6a488035
TO		 412 ( 161, @domain_id, 'http://civicrm.org/aboutcivicrm', '{ts escape="sql"}About{/ts}', 'About', NULL, 'AND', '157', '1', NULL, 4 ),
413
414 ( 162, @domain_id, 'civicrm/admin/setting/search?reset=1', '{ts escape="sql"}Search Settings{/ts}', 'Search Settings', NULL, '', '82', '1', NULL, 10 ),
10824d34 415
416 ( 163, @domain_id, NULL, '{ts escape="sql"}Reports{/ts}', 'Reports', 'access CiviReport', '', NULL, '1', NULL, 8 ),
6a488035 417 ( 164, @domain_id, 'civicrm/report/list&reset=1', '{ts escape="sql"}Reports Listing{/ts}', 'Reports Listing', NULL, '', '163', '1', NULL, 1 ),
10824d34 418 ( 165, @domain_id, 'civicrm/admin/report/template/list&reset=1', '{ts escape="sql"}Create Reports from Templates{/ts}', 'Create Reports from Templates', 'administer Reports', '', '163', '1', 1, 2 ),
419
6a488035
TO		 420 ( 166, @domain_id, 'civicrm/report/instance/1&reset=1', '{ts escape="sql"}Constituent Report (Summary){/ts}', '{literal}Constituent Report (Summary){/literal}', 'administer CiviCRM', '', 163, '1', NULL, 3 ),
421 ( 167, @domain_id, 'civicrm/report/instance/2&reset=1', '{ts escape="sql"}Constituent Report (Detail){/ts}', '{literal}Constituent Report (Detail){/literal}', 'administer CiviCRM', '', 163, '1', NULL, 4 ),
422 ( 168, @domain_id, 'civicrm/report/instance/3&reset=1', '{ts escape="sql"}Donor Report (Summary){/ts}', '{literal}Donor Report (Summary){/literal}', 'access CiviContribute', '', 163, '1', NULL, 5 ),
423 ( 169, @domain_id, 'civicrm/report/instance/4&reset=1', '{ts escape="sql"}Donor Report (Detail){/ts}', '{literal}Donor Report (Detail){/literal}', 'access CiviContribute', '', 163, '1', NULL, 6 ),
424 ( 170, @domain_id, 'civicrm/report/instance/5&reset=1', '{ts escape="sql"}Donation Summary Report (Repeat){/ts}', '{literal}Donation Summary Report (Repeat){/literal}', 'access CiviContribute', '', 163, '1', NULL, 7 ),
425 ( 171, @domain_id, 'civicrm/report/instance/6&reset=1', '{ts escape="sql"}SYBUNT Report{/ts}', 'SYBUNT Report', 'access CiviContribute', '', 163, '1', NULL, 8 ),
426 ( 172, @domain_id, 'civicrm/report/instance/7&reset=1', '{ts escape="sql"}LYBUNT Report{/ts}', 'LYBUNT Report', 'access CiviContribute', '', 163, '1', NULL, 9 ),
427 ( 173, @domain_id, 'civicrm/report/instance/8&reset=1', '{ts escape="sql"}Soft Credit Report{/ts}', 'Soft Credit Report', 'access CiviContribute', '', 163, '1', NULL, 10 ),
428 ( 174, @domain_id, 'civicrm/report/instance/9&reset=1', '{ts escape="sql"}Membership Report (Summary){/ts}', '{literal}Membership Report (Summary){/literal}', 'access CiviMember', '', 163, '1', NULL, 11 ),
429 ( 175, @domain_id, 'civicrm/report/instance/10&reset=1', '{ts escape="sql"}Membership Report (Detail){/ts}', '{literal}Membership Report (Detail){/literal}', 'access CiviMember', '', 163, '1', NULL, 12 ),
430 ( 176, @domain_id, 'civicrm/report/instance/11&reset=1', '{ts escape="sql"}Membership Report (Lapsed){/ts}', '{literal}Membership Report (Lapsed){/literal}', 'access CiviMember', '', 163, '1', NULL, 13 ),
431 ( 177, @domain_id, 'civicrm/report/instance/12&reset=1', '{ts escape="sql"}Event Participant Report (List){/ts}', '{literal}Event Participant Report (List){/literal}', 'access CiviEvent', '', 163, '1', NULL, 14 ),
432 ( 178, @domain_id, 'civicrm/report/instance/13&reset=1', '{ts escape="sql"}Event Income Report (Summary){/ts}', '{literal}Event Income Report (Summary){/literal}', 'access CiviEvent', '', 163, '1', NULL, 15 ),
433 ( 179, @domain_id, 'civicrm/report/instance/14&reset=1', '{ts escape="sql"}Event Income Report (Detail){/ts}', '{literal}Event Income Report (Detail){/literal}', 'access CiviEvent', '', 163, '1', NULL, 16 ),
434 ( 180, @domain_id, 'civicrm/report/instance/15&reset=1', '{ts escape="sql"}Attendee List{/ts}', 'Attendee List', 'access CiviEvent', '', 163, '1', NULL, 17 ),
435 ( 181, @domain_id, 'civicrm/report/instance/16&reset=1', '{ts escape="sql"}Activity Report{/ts}', 'activity', 'administer CiviCRM', '', 163, '1', NULL, 18 ),
10824d34 436 ( 182, @domain_id, 'civicrm/report/instance/17&reset=1', '{ts escape="sql"}Relationship Report{/ts}', 'Relationship Report', 'administer CiviCRM', '', 163, '1', NULL, 19 ),
6a488035
TO		 437 ( 183, @domain_id, 'civicrm/report/instance/18&reset=1', '{ts escape="sql"}Donation Summary Report (Organization){/ts}', '{literal}Donation Summary Report (Organization){/literal}', 'access CiviContribute', '', 163, '1', NULL, 20 ),
438 ( 184, @domain_id, 'civicrm/report/instance/19&reset=1', '{ts escape="sql"}Donation Summary Report (Household){/ts}', '{literal}Donation Summary Report (Household){/literal}', 'access CiviContribute', '', 163, '1', NULL, 21 ),
10824d34 439 ( 185, @domain_id, 'civicrm/report/instance/20&reset=1', '{ts escape="sql"}Top Donors Report{/ts}', 'Top Donors Report', 'access CiviContribute', '', 163, '1', NULL, 22 ),
6a488035
TO		 440 ( 186, @domain_id, 'civicrm/report/instance/21&reset=1', '{ts escape="sql"}Pledge Summary Report{/ts}', 'Pledge Summary Report', 'access CiviPledge', '', 163, '1', NULL, 23 ),
441 ( 187, @domain_id, 'civicrm/report/instance/22&reset=1', '{ts escape="sql"}Pledged But not Paid Report{/ts}', 'Pledged But not Paid Report', 'access CiviPledge', '', 163, '1', NULL, 24 );
442
443
444 -- End navigation
10824d34 445
6a488035
TO		 446 -- Enabled the Report Templates
447 SELECT @option_group_id_report := max(id) from civicrm_option_group where name = 'report_template';
448
449 UPDATE civicrm_option_value
450 SET is_active = 1
451 WHERE option_group_id = @option_group_id_report AND
452 value IN ('activity','contribute/organizationSummary','contribute/householdSummary','contribute/topDonor','pledge/summary','pledge/pbnp');
453
454 -- Add New templates
455 INSERT INTO civicrm_option_value
456 (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id)
457 VALUES
458 (@option_group_id_report, {localize}'Relationship Report'{/localize}, 'contact/relationship', 'CRM_Report_Form_Contact_Relationship', 23, {localize}'Relationship Report'{/localize}, 1, NULL);
459 -- adde new reports
460 INSERT INTO `civicrm_report_instance`
461 (title, report_id, description, permission, form_values)
462 VALUES
463 ('{ts escape="sql"}Activity Report{/ts}', 'activity', 'Provides a list of constituent activity including activity statistics for one/all contacts during a given date range(required)', 'administer CiviCRM', '{literal}a:22:{s:6:"fields"#a:7:{s:14:"contact_source"#s:1:"1"#s:16:"contact_assignee"#s:1:"1"#s:14:"contact_target"#s:1:"1"#s:16:"activity_type_id"#s:1:"1"#s:7:"subject"#s:1:"1"#s:18:"activity_date_time"#s:1:"1"#s:9:"status_id"#s:1:"1"#}s:17:"contact_source_op"#s:3:"has"#s:20:"contact_source_value"#s:0:""#s:19:"contact_assignee_op"#s:3:"has"#s:22:"contact_assignee_value"#s:0:""#s:17:"contact_target_op"#s:3:"has"#s:20:"contact_target_value"#s:0:""#s:27:"activity_date_time_relative"#s:10:"this.month"#s:23:"activity_date_time_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:21:"activity_date_time_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:10:"subject_op"#s:3:"has"#s:13:"subject_value"#s:0:""#s:19:"activity_type_id_op"#s:2:"in"#s:22:"activity_type_id_value"#a:0:{}s:12:"status_id_op"#s:2:"in"#s:15:"status_id_value"#a:0:{}s:11:"description"#s:126:"Provides a list of constituent activity including activity statistics for one/all contacts during a given date range(required)"#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:18:"administer CiviCRM"#s:6:"groups"#s:0:""#}{/literal}'),
464 ('{ts escape="sql"}Relationship Report{/ts}', 'contact/relationship', 'Gives relationship details between two contacats', 'administer CiviCRM', '{literal}a:27:{s:6:"fields"#a:4:{s:14:"display_name_a"#s:1:"1"#s:14:"display_name_b"#s:1:"1"#s:9:"label_a_b"#s:1:"1"#s:7:"email_a"#s:1:"1"#}s:17:"contact_name_a_op"#s:3:"has"#s:20:"contact_name_a_value"#s:0:""#s:17:"contact_name_b_op"#s:3:"has"#s:20:"contact_name_b_value"#s:0:""#s:6:"id_min"#s:0:""#s:6:"id_max"#s:0:""#s:5:"id_op"#s:3:"lte"#s:8:"id_value"#s:0:""#s:17:"contact_type_a_op"#s:2:"in"#s:20:"contact_type_a_value"#a:0:{}s:17:"contact_type_b_op"#s:2:"in"#s:20:"contact_type_b_value"#a:0:{}s:8:"label_op"#s:2:"eq"#s:11:"label_value"#s:1:" "#s:13:"country_id_op"#s:2:"in"#s:16:"country_id_value"#a:0:{}s:20:"state_province_id_op"#s:2:"in"#s:23:"state_province_id_value"#a:0:{}s:6:"gid_op"#s:2:"in"#s:9:"gid_value"#a:0:{}s:11:"description"#s:48:"Gives relationship details between two contacts"#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:18:"administer CiviCRM"#s:6:"groups"#s:0:""#}{/literal}'),
465 ('{ts escape="sql"}Donation Summary Report (Organization){/ts}', 'contribute/organizationSummary', 'Displays a detailed contribution report for Organization relationships with contributors, as to if contribution done was from an employee of some organization or from that Organization itself.', 'access CiviContribute', '{literal}a:20:{s:6:"fields"#a:5:{s:17:"organization_name"#s:1:"1"#s:12:"display_name"#s:1:"1"#s:12:"total_amount"#s:1:"1"#s:22:"contribution_status_id"#s:1:"1"#s:12:"receive_date"#s:1:"1"#}s:20:"organization_name_op"#s:3:"has"#s:23:"organization_name_value"#s:0:""#s:23:"relationship_type_id_op"#s:2:"eq"#s:26:"relationship_type_id_value"#s:5:"4_b_a"#s:21:"receive_date_relative"#s:1:"0"#s:17:"receive_date_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:15:"receive_date_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:16:"total_amount_min"#s:0:""#s:16:"total_amount_max"#s:0:""#s:15:"total_amount_op"#s:3:"lte"#s:18:"total_amount_value"#s:0:""#s:25:"contribution_status_id_op"#s:2:"in"#s:28:"contribution_status_id_value"#a:1:{i:0#s:1:"1"#}s:11:"description"#s:193:"Displays a detailed contribution report for Organization relationships with contributors, as to if contribution done was from an employee of some organization or from that Organization itself."#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:21:"access CiviContribute"#s:6:"groups"#s:0:""#}{/literal}'),
466 ('{ts escape="sql"}Donation Summary Report (Household){/ts}', 'contribute/householdSummary', 'Provides a detailed report for Contributions made by contributors(Or Household itself) who are having a relationship with household (For ex a Contributor is Head of Household for some household or is a member of.)', 'access CiviContribute', '{literal}a:20:{s:6:"fields"#a:5:{s:14:"household_name"#s:1:"1"#s:12:"display_name"#s:1:"1"#s:12:"total_amount"#s:1:"1"#s:22:"contribution_status_id"#s:1:"1"#s:12:"receive_date"#s:1:"1"#}s:17:"household_name_op"#s:3:"has"#s:20:"household_name_value"#s:0:""#s:23:"relationship_type_id_op"#s:2:"eq"#s:26:"relationship_type_id_value"#s:5:"6_b_a"#s:21:"receive_date_relative"#s:1:"0"#s:17:"receive_date_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:15:"receive_date_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:16:"total_amount_min"#s:0:""#s:16:"total_amount_max"#s:0:""#s:15:"total_amount_op"#s:3:"lte"#s:18:"total_amount_value"#s:0:""#s:25:"contribution_status_id_op"#s:2:"in"#s:28:"contribution_status_id_value"#a:1:{i:0#s:1:"1"#}s:11:"description"#s:213:"Provides a detailed report for Contributions made by contributors(Or Household itself) who are having a relationship with household (For ex a Contributor is Head of Household for some household or is a member of.)"#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:21:"access CiviContribute"#s:6:"groups"#s:0:""#}{/literal}'),
467 ('{ts escape="sql"}Top Donors Report{/ts}', 'contribute/topDonor', 'Provides a list of the top donors during a time period you define. You can include as many donors as you want (for example, top 100 of your donors).', 'access CiviContribute', '{literal}a:20:{s:6:"fields"#a:2:{s:12:"display_name"#s:1:"1"#s:12:"total_amount"#s:1:"1"#}s:21:"receive_date_relative"#s:9:"this.year"#s:17:"receive_date_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:15:"receive_date_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:15:"total_range_min"#s:0:""#s:15:"total_range_max"#s:0:""#s:14:"total_range_op"#s:2:"eq"#s:17:"total_range_value"#s:0:""#s:23:"contribution_type_id_op"#s:2:"in"#s:26:"contribution_type_id_value"#a:0:{}s:25:"contribution_status_id_op"#s:2:"in"#s:28:"contribution_status_id_value"#a:1:{i:0#s:1:"1"#}s:6:"gid_op"#s:2:"in"#s:9:"gid_value"#a:0:{}s:11:"description"#s:148:"Provides a list of the top donors during a time period you define. You can include as many donors as you want (for example, top 100 of your donors)."#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:21:"access CiviContribute"#s:6:"groups"#s:0:""#}{/literal}'),
468 ('{ts escape="sql"}Pledge Summary Report{/ts}', 'pledge/summary', 'Updates you with your Pledge Summary (if any) such as your pledge status, next payment date, amount, payment due, total amount paid etc.', 'access CiviPledge', '{literal}a:24:{s:6:"fields"#a:4:{s:12:"display_name"#s:1:"1"#s:10:"country_id"#s:1:"1"#s:6:"amount"#s:1:"1"#s:9:"status_id"#s:1:"1"#}s:12:"sort_name_op"#s:3:"has"#s:15:"sort_name_value"#s:0:""#s:6:"id_min"#s:0:""#s:6:"id_max"#s:0:""#s:5:"id_op"#s:3:"lte"#s:8:"id_value"#s:0:""#s:27:"pledge_create_date_relative"#s:1:"0"#s:23:"pledge_create_date_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:21:"pledge_create_date_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:17:"pledge_amount_min"#s:0:""#s:17:"pledge_amount_max"#s:0:""#s:16:"pledge_amount_op"#s:3:"lte"#s:19:"pledge_amount_value"#s:0:""#s:6:"sid_op"#s:2:"in"#s:9:"sid_value"#a:0:{}s:6:"gid_op"#s:2:"in"#s:9:"gid_value"#a:0:{}s:11:"description"#s:136:"Updates you with your Pledge Summary (if any) such as your pledge status, next payment date, amount, payment due, total amount paid etc."#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:17:"access CiviPledge"#s:6:"groups"#s:0:""#}{/literal}'),
469 ('{ts escape="sql"}Pledged But not Paid Report{/ts}', 'pledge/pbnp', 'Pledged but not Paid Report', 'access CiviPledge', '{literal}a:14:{s:6:"fields"#a:6:{s:12:"display_name"#s:1:"1"#s:18:"pledge_create_date"#s:1:"1"#s:6:"amount"#s:1:"1"#s:14:"scheduled_date"#s:1:"1"#s:10:"country_id"#s:1:"1"#s:5:"email"#s:1:"1"#}s:27:"pledge_create_date_relative"#s:1:"0"#s:23:"pledge_create_date_from"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:21:"pledge_create_date_to"#a:3:{s:1:"M"#s:0:""#s:1:"d"#s:0:""#s:1:"Y"#s:0:""#}s:23:"contribution_type_id_op"#s:2:"in"#s:26:"contribution_type_id_value"#a:0:{}s:6:"gid_op"#s:2:"in"#s:9:"gid_value"#a:0:{}s:11:"description"#s:27:"Pledged but not Paid Report"#s:13:"email_subject"#s:0:""#s:8:"email_to"#s:0:""#s:8:"email_cc"#s:0:""#s:10:"permission"#s:17:"access CiviPledge"#s:6:"groups"#s:0:""#}{/literal}');
470
471
472 -- CRM-4114
473 ALTER TABLE `civicrm_report_instance`
474 ADD navigation_id int unsigned COMMENT 'FK to navigation ID',
475 ADD CONSTRAINT `FK_civicrm_report_instance_navigation_id` FOREIGN KEY (`navigation_id`) REFERENCES `civicrm_navigation` (`id`) ON DELETE SET NULL;
476
477 UPDATE civicrm_report_instance
10824d34 478 SET navigation_id = CASE id
6a488035
TO		 479 WHEN 1 THEN 166
480 WHEN 2 THEN 167
481 WHEN 3 THEN 168
482 WHEN 4 THEN 169
483 WHEN 5 THEN 170
484 WHEN 6 THEN 171
485 WHEN 7 THEN 172
486 WHEN 8 THEN 173
487 WHEN 9 THEN 174
488 WHEN 10 THEN 175
489 WHEN 11 THEN 176
490 WHEN 12 THEN 177
491 WHEN 13 THEN 178
492 WHEN 14 THEN 179
493 WHEN 15 THEN 180
494 WHEN 16 THEN 181
495 WHEN 17 THEN 182
496 WHEN 18 THEN 183
497 WHEN 19 THEN 184
498 WHEN 20 THEN 185
499 WHEN 21 THEN 186
500 WHEN 22 THEN 187
501 ELSE navigation_id
502 END;
10824d34 503
6a488035
TO		 504 -- CRM-4414
505 -- Add individual, organization and household default profile
506 INSERT INTO civicrm_uf_group
507 (name, group_type, {localize field='title'}title{/localize}, is_reserved)
508 VALUES
509 ('new_individual', 'Individual,Contact', {localize}'New Individual'{/localize}, 1),
510 ('new_organization', 'Organization,Contact', {localize}'New Organization'{/localize}, 1),
511 ('new_household', 'Household,Contact', {localize}'New Household'{/localize}, 1);
512
513 SELECT @uf_group_id_individual := max(id) from civicrm_uf_group where name = 'new_individual';
514 SELECT @uf_group_id_organization := max(id) from civicrm_uf_group where name = 'new_organization';
515 SELECT @uf_group_id_household := max(id) from civicrm_uf_group where name = 'new_household';
516
517 INSERT INTO `civicrm_uf_join`
518 ( `is_active`, `module`, `entity_table`, `entity_id`, `weight`, `uf_group_id` )
519 VALUES
520 ( 1, 'Profile', NULL, NULL, 3, @uf_group_id_individual ),
521 ( 1, 'Profile', NULL, NULL, 4, @uf_group_id_organization ),
522 ( 1, 'Profile', NULL, NULL, 5, @uf_group_id_household );
10824d34 523
6a488035
TO		 524 INSERT INTO civicrm_uf_field
525 (uf_group_id, field_name, is_required, is_reserved, weight, {localize field='label'}label{/localize}, field_type)
526 VALUES
527 (@uf_group_id_individual, 'first_name', 1, 0, 1, {localize}'First Name'{/localize}, 'Individual' ),
528 (@uf_group_id_individual, 'last_name', 1, 0, 2, {localize}'Last Name'{/localize}, 'Individual' ),
529 (@uf_group_id_individual, 'email', 1, 0, 3, {localize}'Email Address'{/localize}, 'Contact' ),
530 (@uf_group_id_organization, 'organization_name', 1, 0, 1, {localize}'Organization Name'{/localize}, 'Organization'),
531 (@uf_group_id_organization, 'email', 1, 0, 2, {localize}'Email Address'{/localize}, 'Contact' ),
532 (@uf_group_id_household, 'household_name', 1, 0, 1, {localize}'Household Name'{/localize}, 'Household' ),
533 (@uf_group_id_household, 'email', 1, 0, 2, {localize}'Email Address'{/localize}, 'Contact' );
10824d34 534
6a488035
TO		 535 -- State / province
536 -- CRM-4534 CRM-4686 CRM-4769
cff67e98 537 INSERT IGNORE INTO civicrm_state_province
6a488035
TO		 538 (id, name, abbreviation, country_id)
539 VALUES
540 (5218, 'Distrito Federal', 'DIF', 1140),
541 (10004, 'Bonaire', 'BON', 1151),
542 (10005, 'Curaçao', 'CUR', 1151),
543 (10006, 'Saba', 'SAB', 1151),
544 (10007, 'St. Eustatius', 'EUA', 1151),
545 (10008, 'St. Maarten', 'SXM', 1151),
10824d34 546 (10009, "Jura", "39" , 1076);
6a488035
TO		 547
548 -- CRM-4587 CRM-4534 CRM-4762 CRM-4394
549 -- Update the name
550 UPDATE civicrm_state_province
551 SET name = CASE id
552 WHEN 1859 THEN "Sofia"
553 WHEN 3707 THEN "Ulaanbaatar"
554 WHEN 2879 THEN "Achaïa"
555 WHEN 3808 THEN "Coahuila"
556 WHEN 3809 THEN "Colima"
557 WHEN 3811 THEN "Chihuahua"
10824d34 558 ELSE name
559 END,
6a488035 560 abbreviation = CASE id
10824d34 561 WHEN 2543 THEN "36"
562 WHEN 2544 THEN "35"
563 ELSE abbreviation
564 END,
565 country_id = CASE id
566 WHEN 1637 THEN 1008
567 ELSE country_id
6a488035
TO		 568
569 END;
570
571 -- CRM-4633
572 ALTER TABLE `civicrm_contact`
573 ADD `do_not_sms` tinyint(4) default '0' AFTER `do_not_mail`;
574
575 -- CRM-4664
576 ALTER TABLE `civicrm_option_value`
577 MODIFY `name` VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.';
578
579 -- CRM-4687
580 -- set activity_date = due_date and drop due_date_time column from civicrm_activity.
10824d34 581 UPDATE civicrm_activity ca INNER JOIN civicrm_case_activity cca ON ca.id = cca.activity_id
6a488035
TO		 582 SET activity_date_time = COALESCE( ca.activity_date_time, ca.due_date_time );
583
584 ALTER TABLE civicrm_activity DROP COLUMN due_date_time;
585
586 -- CRM-4120, CRM-4319, CRM-4326, CRM-4464
587 ALTER TABLE civicrm_event
588 CHANGE default_discount_id default_discount_fee_id INT( 10 ) UNSIGNED NULL DEFAULT NULL COMMENT 'FK to civicrm_option_value.',
589 ADD COLUMN is_template tinyint(4) default NULL COMMENT 'whether the event has template',
590 {localize field='template_title'}ADD COLUMN template_title varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Event Template Title'{/localize},
591 ADD COLUMN has_waitlist tinyint(4) default NULL COMMENT 'Whether the event has waitlist support',
592 ADD COLUMN requires_approval tinyint(4) default NULL COMMENT 'Whether participants require approval before they can finish registering.',
593 ADD COLUMN expiration_time int unsigned DEFAULT NULL COMMENT 'Expire pending but unconfirmed registrations after this many hours.',
594 {localize field='waitlist_text'}ADD COLUMN waitlist_text text collate utf8_unicode_ci default NULL COMMENT 'Text to display when the event is full, but participants can signup for a waitlist.'{/localize},
595 {localize field='approval_req_text'}ADD COLUMN approval_req_text text collate utf8_unicode_ci default NULL COMMENT 'Text to display when the approval is required to complete registration for an event.'{/localize};
596
10824d34 597 INSERT INTO civicrm_event
6a488035
TO		 598 (is_template, {localize field='template_title'}template_title{/localize}, event_type_id, default_role_id, participant_listing_id, is_public, is_monetary, is_online_registration, is_multiple_registrations, allow_same_participant_emails, is_email_confirm, contribution_type_id, {localize field='fee_label'}fee_label{/localize}, {localize field='confirm_title'}confirm_title{/localize}, {localize field='thankyou_title'}thankyou_title{/localize}, {localize field='confirm_from_name'}confirm_from_name{/localize}, confirm_from_email, is_active)
599 VALUES
10824d34 600 (1, {localize}'Free Meeting without Online Registration'{/localize}, 4, 1, 1, 1, 0, 0, null, null, null, null, {localize}null{/localize}, {localize}null{/localize}, {localize}null{/localize}, {localize}null{/localize}, null, 1),
6a488035
TO		 601 (1, {localize}'Free Meeting with Online Registration'{/localize}, 4, 1, 1, 1, 0, 1, 1, 1, 0, null, {localize}null{/localize}, {localize}'Confirm Your Registration Information'{/localize}, {localize}'Thanks for Registering!'{/localize}, {localize}null{/localize}, null, 1),
602 (1, {localize}'Paid Conference with Online Registration'{/localize}, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, {localize}'Conference Fee'{/localize}, {localize}'Confirm Your Registration Information'{/localize}, {localize}'Thanks for Registering!'{/localize}, {localize}'Event Template Dept.'{/localize}, 'event_templates@example.org', 1);
10824d34 603
6a488035
TO		 604 -- CRM-4138
605 ALTER TABLE `civicrm_payment_processor_type`
606 ADD COLUMN `payment_type` int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit';
607 ALTER TABLE `civicrm_payment_processor`
608 ADD COLUMN `payment_type` int unsigned DEFAULT 1 COMMENT 'Payment Type: Credit or Debit';
609
610 -- CRM-4605
611 -- A. upgrade wt and val by 2
10824d34 612 -- B. Insert Custom data and Address as group for first two empty location
613 -- C. Update Communication Pref name
6a488035
TO		 614 -- D. Swap wt and value for Comm Pref, Notes, Demographics and make sure these record has to have wt and val 3, 4, 5 in sequence.
615
616 -- get option group id for contact_edit_options
617 SELECT @option_group_id_ceOpt := max(id) from civicrm_option_group where name = 'contact_edit_options';
618
619 -- increment all wt and val by 2 and make first two location empty.
620 UPDATE civicrm_option_value
621 SET value = value + 2, weight = weight + 2
622 WHERE option_group_id = @option_group_id_ceOpt;
623
624 -- insert value for Custom Data and Address at first two locations.
625 INSERT INTO civicrm_option_value
626 (option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, is_default, weight, is_optgroup, is_reserved, is_active)
627 VALUES
628 (@option_group_id_ceOpt, {localize}'Custom Data'{/localize}, 1, 'CustomData', NULL, 0, NULL, 1, 0, 0, 1),
629 (@option_group_id_ceOpt, {localize}'Address'{/localize}, 2, 'Address', NULL, 0, NULL, 2, 0, 0, 1);
10824d34 630
6a488035
TO		 631 -- update Comm pref group name.
632 UPDATE civicrm_option_value
633 SET name = 'CommunicationPreferences'
634 WHERE option_group_id=@option_group_id_ceOpt AND name = 'CommBlock';
635
636 -- 1. Communication pref.
637 -- swap wt and val and make commumication pref wt and val = 3
638 Update civicrm_option_value otherRecord, civicrm_option_value commPref
639 SET otherRecord.value = commPref.value, otherRecord.weight = commPref.weight, commPref.value = 3, commPref.weight = 3
640 WHERE otherRecord.value = 3 AND commPref.name = 'CommunicationPreferences' AND commPref.option_group_id = @option_group_id_ceOpt AND otherRecord.option_group_id = @option_group_id_ceOpt;
641
10824d34 642 -- make sure comm has val and wt = 3
6a488035
TO		 643 Update civicrm_option_value
644 SET value = 3, weight = 3
645 WHERE name = 'CommunicationPreferences' and option_group_id = @option_group_id_ceOpt;
646
647 -- 2. Notes.
648 -- swap wt and val and make notes wt and val = 4
649 Update civicrm_option_value otherRecord, civicrm_option_value notes
650 SET otherRecord.value = notes.value, otherRecord.weight = notes.weight, notes.value = 4, notes.weight=4
651 WHERE otherRecord.value = 4 AND notes.name = 'Notes' AND notes.option_group_id = @option_group_id_ceOpt AND otherRecord.option_group_id = @option_group_id_ceOpt;
652
653 -- make sure Notes has val and wt = 4
654 Update civicrm_option_value
655 SET value = 4, weight = 4
656 WHERE name = 'Notes' and option_group_id = @option_group_id_ceOpt;
657
658 -- 3. Demographics.
659 -- swap wt and val and make demographics wt and val = 5
660 Update civicrm_option_value otherRecord, civicrm_option_value demographics
661 SET otherRecord.value = demographics.value, otherRecord.weight = demographics.weight, demographics.value = 5, demographics.weight=5
662 WHERE otherRecord.value = 5 AND demographics.name = 'Demographics' AND demographics.option_group_id = @option_group_id_ceOpt AND otherRecord.option_group_id = @option_group_id_ceOpt;
663
10824d34 664 -- make sure Demoghraphics has val and wt = 5
6a488035
TO		 665 Update civicrm_option_value
666 SET value = 5, weight = 5
667 WHERE name = 'Demographics' and option_group_id = @option_group_id_ceOpt;
668
669 -- move location blocks to contact_edit_options.
670 SELECT @max_wt := max(weight) from civicrm_option_value where option_group_id=@option_group_id_ceOpt;
671 SELECT @max_val := max(weight) from civicrm_option_value where option_group_id=@option_group_id_ceOpt;
672 INSERT INTO civicrm_option_value
673 (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)
674 VALUES
675 (@option_group_id_ceOpt, {localize}'Email'{/localize}, (SELECT @max_val := @max_val+1), 'Email', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 0, 0, 1, NULL, NULL),
676 (@option_group_id_ceOpt, {localize}'Phone'{/localize}, (SELECT @max_val := @max_val+1), 'Phone', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 0, 0, 1, NULL, NULL),
677 (@option_group_id_ceOpt, {localize}'Instant Messenger'{/localize}, (SELECT @max_val := @max_val+1), 'IM', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 0, 0, 1, NULL, NULL),
678 (@option_group_id_ceOpt, {localize}'Open ID'{/localize}, (SELECT @max_val := @max_val+1), 'OpenID', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 0, 0, 1, NULL, NULL);
10824d34 679
6a488035
TO		 680 -- remove location blocks from address_options.
681 SELECT @option_group_id_adOpt := max(id) from civicrm_option_group where name = 'address_options';
682 DELETE FROM civicrm_option_value where option_group_id = @option_group_id_adOpt AND name IN ( 'im', 'openid' );
683
684 -- update civicrm_preferences.contact_edit_options.
685 -- ideally we should append value, but we did changed wt and values so lets reset it to default.
10824d34 686 UPDATE civicrm_preferences
687 SET contact_edit_options = ( SELECT CONCAT( GROUP_CONCAT('\ 1',value SEPARATOR ''), '\ 1' )
688 FROM civicrm_option_value
689 WHERE option_group_id = @option_group_id_ceOpt
6a488035
TO		 690 Group by option_group_id
691 )
692 WHERE is_domain = 1 AND contact_id IS NULL;
693 -- End of CRM-4605
694
695
696 -- CRM-4575
697 -- drop column greeting type and custom greeting from civicrm_contact
698 ALTER TABLE `civicrm_contact`
699 DROP `greeting_type_id`,
700 DROP `custom_greeting`;
701
702 -- delete greeting type option group
703 SELECT @og_id_greeting := max(id) from civicrm_option_group where name = 'greeting_type';
704 DELETE FROM civicrm_option_value WHERE option_group_id = @og_id_greeting;
705 DELETE FROM civicrm_option_group WHERE id = @og_id_greeting;
706
707 -- add email greeting, postal greeting and addressee fields
10824d34 708 ALTER TABLE `civicrm_contact`
6a488035 709 ADD `email_greeting_id` INT(10) UNSIGNED DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.' AFTER `suffix_id`,
10824d34 710 ADD `postal_greeting_id` INT(10) UNSIGNED DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.' AFTER `email_greeting_id`,
711 ADD `addressee_id` INT(10) UNSIGNED DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.' AFTER `postal_greeting_id`,
6a488035
TO		 712 {localize field='email_greeting_display'}ADD email_greeting_display VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Email greeting.' AFTER email_greeting_id{/localize},
713 {localize field='postal_greeting_display'}ADD postal_greeting_display VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Postal greeting.' AFTER postal_greeting_id{/localize},
714 {localize field='addressee_display'}ADD addressee_display VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Addressee.' AFTER addressee_id{/localize},
715 ADD `email_greeting_custom` VARCHAR(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Email Greeting.' AFTER `email_greeting_id`,
716 ADD `postal_greeting_custom` VARCHAR(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Postal greeting.' AFTER `postal_greeting_id`,
717 ADD `addressee_custom` VARCHAR(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Addressee.' AFTER `addressee_id`;
10824d34 718
6a488035
TO		 719
720 SELECT @og_id_emailGreeting := max(id) FROM civicrm_option_group WHERE name = 'email_greeting';
721 SELECT @og_id_postalGreeting := max(id) FROM civicrm_option_group WHERE name = 'postal_greeting';
722 SELECT @og_id_addressee := max(id) FROM civicrm_option_group WHERE name = 'addressee';
723
724 -- add option values for email greeting, postal greeting and addressee
725 INSERT INTO civicrm_option_value
726 (option_group_id, value, filter, is_default, weight, is_reserved, is_active, {localize field='label'}label{/localize}, name) VALUES
727
728 -- email greetings.
729 (@og_id_emailGreeting, 1, 1, 1, 1, 0, 1, {localize}'Dear {ldelim}contact.first_name{rdelim}'{/localize}, 'Dear {ldelim}contact.first_name{rdelim}'),
730 (@og_id_emailGreeting, 2, 1, 0, 2, 0, 1, {localize}'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.first_name{rdelim} {ldelim}contact.last_name{rdelim}'{/localize}, 'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.first_name{rdelim} {ldelim}contact.last_name{rdelim}'),
731 (@og_id_emailGreeting, 3, 1, 0, 3, 0, 1, {localize}'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.last_name{rdelim}'{/localize}, 'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.last_name{rdelim}'),
732 (@og_id_emailGreeting, 4, 0, 0, 4, 1, 1, {localize}'Customized'{/localize}, 'Customized'),
733 (@og_id_emailGreeting, 5, 2, 1, 5, 0, 1, {localize}'Dear {ldelim}contact.household_name{rdelim}'{/localize}, 'Dear {ldelim}contact.househols_name{rdelim}'),
734
735 -- postal greeting.
736 (@og_id_postalGreeting, 1, 1, 1, 1, 0, 1, {localize}'Dear {ldelim}contact.first_name{rdelim}'{/localize}, 'Dear {ldelim}contact.first_name{rdelim}'),
737 (@og_id_postalGreeting, 2, 1, 0, 2, 0, 1, {localize}'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.first_name{rdelim} {ldelim}contact.last_name{rdelim}'{/localize}, 'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.first_name{rdelim} {ldelim}contact.last_name{rdelim}'),
738 (@og_id_postalGreeting, 3, 1, 0, 3, 0, 1, {localize}'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.last_name{rdelim}'{/localize}, 'Dear {ldelim}contact.individual_prefix{rdelim} {ldelim}contact.last_name{rdelim}'),
739 (@og_id_postalGreeting, 4, 0, 0, 4, 1, 1, {localize}'Customized'{/localize}, 'Customized'),
740 (@og_id_postalGreeting, 5, 2, 1, 5, 0, 1, {localize}'Dear {ldelim}contact.household_name{rdelim}'{/localize}, 'Dear {ldelim}contact.househols_name{rdelim}'),
741
742 -- addressee.
743 (@og_id_addressee, 1, 1, 1, 1, 0, 1, {localize}'{ldelim}contact.individual_prefix{rdelim}{ldelim} {rdelim} {ldelim}contact.first_name{rdelim}{ldelim} {rdelim}{ldelim}contact.middle_name{rdelim}{ldelim} {rdelim}{ldelim}contact.last_name{rdelim}{ldelim} {rdelim}{ldelim}contact.individual_suffix{rdelim}'{/localize}, '{ldelim}contact.individual_prefix{rdelim}{ldelim} {rdelim} {ldelim}contact.first_name{rdelim}{ldelim} {rdelim}{ldelim}contact.middle_name{rdelim}{ldelim} {rdelim}{ldelim}contact.last_name{rdelim}{ldelim} {rdelim}{ldelim}contact.individual_suffix{rdelim}'),
744 (@og_id_addressee, 2, 2, 1, 2, 0, 1, {localize}'{ldelim}contact.household_name{rdelim}'{/localize}, '{ldelim}contact.household_name{rdelim}'),
745 (@og_id_addressee, 3, 3, 1, 3, 0, 1, {localize}'{ldelim}contact.organization_name{rdelim}'{/localize}, '{ldelim}contact.organization_name{rdelim}'),
746 (@og_id_addressee, 4, 0, 0, 4, 1, 1, {localize}'Customized'{/localize}, 'Customized');
10824d34 747
748 -- Set civicrm_contact.email_greeting_id and postal_greeting_id to default value for the given contact type.
6a488035 749 SELECT @value := value
10824d34 750 FROM civicrm_option_value
6a488035 751 WHERE civicrm_option_value.option_group_id = @og_id_emailGreeting
10824d34 752 AND civicrm_option_value.filter = 1
6a488035
TO		 753 AND civicrm_option_value.is_default = 1;
754 UPDATE civicrm_contact
10824d34 755 SET
6a488035
TO		 756 {localize field='email_greeting_display, first_name'}email_greeting_display = CONCAT("Dear ", first_name){/localize},
757 {localize field='postal_greeting_display, first_name'}postal_greeting_display = CONCAT("Dear ", first_name){/localize},
758 email_greeting_id = @value,
10824d34 759 postal_greeting_id = @value
6a488035
TO		 760 WHERE contact_type = 'Individual';
761
762 SELECT @value := value
763 FROM civicrm_option_value
764 WHERE civicrm_option_value.option_group_id = @og_id_emailGreeting
10824d34 765 AND civicrm_option_value.filter = 2
6a488035
TO		 766 AND civicrm_option_value.is_default = 1;
767 UPDATE civicrm_contact
10824d34 768 SET
6a488035
TO		 769 {localize field='email_greeting_display, household_name'}email_greeting_display = CONCAT("Dear ", household_name){/localize},
770 {localize field='postal_greeting_display, household_name'}postal_greeting_display = CONCAT("Dear ", household_name){/localize},
771 email_greeting_id = @value,
772 postal_greeting_id = @value
773 WHERE contact_type = 'Household';
10824d34 774 -- Set civicrm_contact.addressee_id to default value for the given contact type.
6a488035
TO		 775 SELECT @value := value
776 FROM civicrm_option_value
777 WHERE civicrm_option_value.option_group_id = @og_id_addressee
778 AND civicrm_option_value.filter = 1
779 AND civicrm_option_value.is_default = 1;
780 UPDATE civicrm_contact
781 SET
782 {localize field='addressee_display, display_name'}addressee_display = display_name{/localize},
783 addressee_id = @value
784 WHERE contact_type = 'Individual';
785
786 SELECT @value := value
787 FROM civicrm_option_value
788 WHERE civicrm_option_value.option_group_id = @og_id_addressee
789 AND civicrm_option_value.filter = 2
790 AND civicrm_option_value.is_default = 1;
791 UPDATE civicrm_contact
792 SET
793 {localize field='addressee_display, household_name'}addressee_display = household_name{/localize},
794 addressee_id = @value
795 WHERE contact_type = 'Household';
796
797 SELECT @value := value
10824d34 798 FROM civicrm_option_value
6a488035 799 WHERE civicrm_option_value.option_group_id = @og_id_addressee
10824d34 800 AND civicrm_option_value.filter = 3
6a488035
TO		 801 AND civicrm_option_value.is_default = 1;
802 UPDATE civicrm_contact
10824d34 803 SET
6a488035
TO		 804 {localize field='addressee_display, organization_name'}addressee_display = organization_name{/localize},
805 addressee_id = @value
806 WHERE contact_type = 'Organization';
807
808 -- CRM-4610
809 ALTER TABLE `civicrm_group_organization`
810 DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
811 DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id`;
10824d34 812
6a488035
TO		 813 ALTER TABLE `civicrm_group_organization`
814 ADD CONSTRAINT `FK_civicrm_group_organization_group_id` FOREIGN KEY (`group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE,
815 ADD CONSTRAINT `FK_civicrm_group_organization_organization_id` FOREIGN KEY (`organization_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
816 ADD UNIQUE `UI_group_organization` ( `group_id` , `organization_id` );
10824d34 817
6a488035 818 -- CRM-4697
10824d34 819
6a488035
TO		 820 ALTER TABLE `civicrm_payment_processor`
821 ADD `domain_id` INT(10) UNSIGNED NOT NULL COMMENT 'Which Domain is this match entry for' AFTER `id`;
822 UPDATE `civicrm_payment_processor` SET domain_id = @domain_id;
10824d34 823 ALTER TABLE `civicrm_payment_processor`
6a488035 824 ADD CONSTRAINT `FK_civicrm_payment_processor_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);
10824d34 825
6a488035
TO		 826 ALTER TABLE `civicrm_membership_type`
827 ADD `domain_id` INT(10) UNSIGNED NOT NULL COMMENT 'Which Domain is this match entry for' AFTER `id`;
828 UPDATE `civicrm_membership_type` SET domain_id = @domain_id;
829 ALTER TABLE `civicrm_membership_type`
830 ADD CONSTRAINT `FK_civicrm_membership_type_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);
10824d34 831
6a488035
TO		 832 ALTER TABLE `civicrm_menu`
833 ADD `domain_id` INT(10) UNSIGNED NOT NULL COMMENT 'Which Domain is this match entry for' AFTER `id`;
834 UPDATE `civicrm_menu` SET domain_id = @domain_id;
835 ALTER TABLE `civicrm_menu`
836 ADD CONSTRAINT `FK_civicrm_menu_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);
10824d34 837
6a488035
TO		 838 -- CRM-4813
839
840 ALTER TABLE `civicrm_menu`
10824d34 841 DROP INDEX `UI_path`,
6a488035
TO		 842 ADD UNIQUE `UI_path_domain_id` ( `path`,`domain_id` );
843
844 ALTER TABLE `civicrm_payment_processor`
845 DROP INDEX `UI_name_test`,
846 ADD UNIQUE `UI_name_test_domain_id` ( `name`,`is_test`,`domain_id` );
847
848 ALTER TABLE `civicrm_preferences`
849 ADD `domain_id` INT(10) UNSIGNED NOT NULL COMMENT 'Which Domain is this match entry for' AFTER `id`,
850 DROP FOREIGN KEY `FK_civicrm_preferences_contact_id`;
851
852 UPDATE `civicrm_preferences` SET domain_id = @domain_id;
853 ALTER TABLE `civicrm_preferences`
854 ADD CONSTRAINT `FK_civicrm_preferences_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE,
855 ADD CONSTRAINT `FK_civicrm_preferences_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
10824d34 856
6a488035
TO		 857 ALTER TABLE `civicrm_uf_match`
858 DROP FOREIGN KEY `FK_civicrm_uf_match_contact_id`,
859 DROP INDEX `UI_uf_name` ,
860 DROP INDEX `UI_contact` ;
10824d34 861
6a488035
TO		 862 ALTER TABLE `civicrm_uf_match`
863 ADD `domain_id` INT(10) UNSIGNED NOT NULL COMMENT 'Which Domain is this match entry for' AFTER `id`;
864 UPDATE `civicrm_uf_match` SET domain_id = @domain_id;
865 ALTER TABLE `civicrm_uf_match`
866 ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
867 ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
868 ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
869 ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`);
10824d34 870
6a488035
TO		 871 ALTER TABLE `civicrm_mailing`
872 ADD `created_date` datetime default NULL COMMENT 'Date and time this mailing was created.' AFTER `created_id`;
10824d34 873
6a488035
TO		 874 ALTER TABLE `civicrm_contribution_page`
875 ADD `created_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_contact, who created this contribution page',
876 ADD `created_date` datetime default NULL COMMENT 'Date and time that contribution page was created.',
877 ADD CONSTRAINT `FK_civicrm_contribution_page_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
10824d34 878
6a488035
TO		 879 ALTER TABLE `civicrm_event`
880 ADD `created_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_contact, who created this event',
881 ADD `created_date` datetime default NULL COMMENT 'Date and time that event was created.',
882 ADD CONSTRAINT `FK_civicrm_event_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
883
884 -- CRM-4842
885 ALTER TABLE `civicrm_mailing`
886 DROP FOREIGN KEY `FK_civicrm_mailing_created_id`,
10824d34 887 DROP FOREIGN KEY `FK_civicrm_mailing_scheduled_id`;
6a488035
TO		 888
889 ALTER TABLE `civicrm_mailing`
10824d34 890 ADD CONSTRAINT `FK_civicrm_mailing_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL,
891 ADD CONSTRAINT `FK_civicrm_mailing_scheduled_id` FOREIGN KEY (`scheduled_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
892
6a488035
TO		 893 -- CRM-4469
894 -- Add collapse_adv_search column to civicrm_custom_group
895 ALTER TABLE `civicrm_custom_group`
896 ADD `collapse_adv_display` int(10) unsigned default '0' COMMENT 'Will this group be in collapsed or expanded mode on advanced search display ?' AFTER `max_multiple`,
897 ADD `created_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_contact, who created this custom group',
898 ADD `created_date` datetime default NULL COMMENT 'Date and time this custom group was created.',
899 ADD CONSTRAINT `FK_civicrm_custom_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
10824d34 900
6a488035
TO		 901 ALTER TABLE `civicrm_uf_group`
902 ADD `created_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_contact, who created this UF group',
903 ADD `created_date` datetime default NULL COMMENT 'Date and time this UF group was created.',
904 ADD CONSTRAINT `FK_civicrm_uf_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;