Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | -- CRM-7171 |
2 | ||
3 | ALTER TABLE `civicrm_mailing` | |
4 | ADD `scheduled_date` datetime default NULL COMMENT 'Date and time this mailing was scheduled.', | |
5 | ADD `approver_id` int(10) unsigned default NULL COMMENT 'FK to Contact ID who approved this mailing', | |
6 | ADD CONSTRAINT `FK_civicrm_mailing_approver_id` FOREIGN KEY (`approver_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL, | |
7 | ADD `approval_date` datetime default NULL COMMENT 'Date and time this mailing was approved.', | |
8 | ADD `approval_status_id` int unsigned default NULL COMMENT 'The status of this mailing. values: none, approved, rejected', | |
9 | ADD `approval_note` longtext default NULL COMMENT 'Note behind the decision.', | |
10 | ADD `visibilty` enum('User and User Admin Only','Public User Pages') default 'User and User Admin Only' COMMENT 'In what context(s) is the mailing contents visible (online viewing)'; | |
11 | ||
12 | UPDATE `civicrm_navigation` SET `permission` = 'access CiviMail,create mailings,approve mailings,schedule mailings', `permission_operator` = 'OR' WHERE name = 'Mailings'; | |
13 | ||
14 | UPDATE `civicrm_navigation` SET `permission` = 'access CiviMail,create mailings', `permission_operator` = 'OR' WHERE name = 'Draft and Unscheduled Mailings'; | |
15 | ||
16 | UPDATE `civicrm_navigation` SET `permission` = 'access CiviMail,approve mailings', `permission_operator` = 'OR' WHERE name = 'Scheduled and Sent Mailings'; | |
17 | ||
18 | --CRM-7180, Change Participant Listing Templates menu title` | |
19 | ||
20 | UPDATE `civicrm_navigation` SET `label` = '{ts escape="sql"}Participant Listing Options{/ts}', `name`= 'Participant Listing Options' WHERE name = 'Participant Listing Templates'; | |
21 | ||
22 | --CRM--7197 | |
23 | {if $dropMailingIndex} | |
24 | ALTER TABLE civicrm_mailing_job | |
10824d34 | 25 | DROP FOREIGN KEY parent_id, |
6a488035 | 26 | DROP INDEX parent_id , |
10824d34 | 27 | ADD CONSTRAINT FK_civicrm_mailing_job_parent_id |
6a488035 TO |
28 | FOREIGN KEY (parent_id) REFERENCES civicrm_mailing_job (id) ON DELETE CASCADE; |
29 | {/if} | |
30 | -- CRM-7206 | |
31 | UPDATE civicrm_membership_type | |
32 | SET relationship_type_id = NULL, relationship_direction = NULL | |
33 | WHERE relationship_type_id = 'Array' OR relationship_type_id IS NULL; | |
34 | ||
35 | -- CRM-7171, Rules Mailing integration | |
36 | {if $multilingual} | |
37 | INSERT INTO civicrm_option_group | |
38 | ( name, {foreach from=$locales item=locale}description_{$locale}, {/foreach} is_reserved, is_active) | |
39 | VALUES | |
40 | ( 'mail_approval_status', {foreach from=$locales item=locale}'CiviMail Approval Status', {/foreach} 0, 1 ); | |
41 | {else} | |
42 | INSERT INTO civicrm_option_group | |
43 | (name, description, is_reserved, is_active ) | |
44 | VALUES | |
45 | ('mail_approval_status', 'CiviMail Approval Status', 0, 1 ); | |
46 | {/if} | |
47 | ||
48 | SELECT @mailCompId := max(id) FROM civicrm_component where name = 'CiviMail'; | |
49 | SELECT @option_group_id_approvalStatus := max(id) from civicrm_option_group where name = 'mail_approval_status'; | |
50 | ||
51 | {if $multilingual} | |
52 | INSERT INTO civicrm_option_value | |
53 | (option_group_id, {foreach from=$locales item=locale}label_{$locale}, {/foreach} name, value, weight, is_active, component_id, is_default ) | |
54 | ||
55 | VALUES | |
56 | (@option_group_id_approvalStatus, {foreach from=$locales item=locale}'Approved', {/foreach} 'Approved', 1, 1, 1, @mailCompId, 1 ), | |
57 | (@option_group_id_approvalStatus, {foreach from=$locales item=locale}'Rejected', {/foreach} 'Rejected', 2, 2, 1, @mailCompId, 0 ), | |
58 | (@option_group_id_approvalStatus, {foreach from=$locales item=locale}'None', {/foreach} 'None', 3, 3, 1, @mailCompId, 0 ); | |
59 | ||
60 | {else} | |
61 | INSERT INTO civicrm_option_value | |
62 | (option_group_id, label, name, value, weight, is_active, component_id, is_default ) | |
10824d34 | 63 | |
6a488035 TO |
64 | VALUES |
65 | (@option_group_id_approvalStatus , '{ts escape="sql"}Approved{/ts}', 'Approved', 1, 1, 1, @mailCompId, 1 ), | |
66 | (@option_group_id_approvalStatus , '{ts escape="sql"}Rejected{/ts}', 'Rejected', 2, 2, 1, @mailCompId, 0 ), | |
10824d34 | 67 | (@option_group_id_approvalStatus , '{ts escape="sql"}None{/ts}', 'None', 3, 3, 1, @mailCompId, 0 ); |
6a488035 TO |
68 | {/if} |
69 | ||
70 | -- CRM-7170 | |
71 | UPDATE civicrm_report_instance SET form_values = '{literal}a:39:{s:6:"fields";a:5:{s:2:"id";s:1:"1";s:10:"first_name";s:1:"1";s:9:"last_name";s:1:"1";s:11:"bounce_name";s:1:"1";s:5:"email";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:9:"source_op";s:3:"has";s:12:"source_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:15:"mailing_name_op";s:2:"in";s:18:"mailing_name_value";a:0:{}s:19:"bounce_type_name_op";s:2:"eq";s:22:"bounce_type_name_value";s:0:"";s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:8:"tagid_op";s:2:"in";s:11:"tagid_value";a:0:{}s:11:"custom_1_op";s:2:"in";s:14:"custom_1_value";a:0:{}s:11:"custom_2_op";s:2:"in";s:14:"custom_2_value";a:0:{}s:17:"custom_3_relative";s:1:"0";s:13:"custom_3_from";s:0:"";s:11:"custom_3_to";s:0:"";s:17:"custom_9_relative";s:1:"0";s:13:"custom_9_from";s:0:"";s:11:"custom_9_to";s:0:"";s:12:"custom_10_op";s:2:"in";s:15:"custom_10_value";a:0:{}s:12:"custom_11_op";s:3:"has";s:15:"custom_11_value";s:0:"";s:11:"description";s:26:"Bounce Report for mailings";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:15:"access CiviMail";s:6:"groups";s:0:"";s:6:"charts";s:0:"";s:9:"domain_id";i:1;}{/literal}' WHERE report_id = 'Mailing/bounce'; | |
72 | ||
73 | UPDATE civicrm_report_instance SET form_values = '{literal}a:25:{s:6:"fields";a:5:{s:4:"name";s:1:"1";s:11:"queue_count";s:1:"1";s:15:"delivered_count";s:1:"1";s:12:"bounce_count";s:1:"1";s:10:"open_count";s:1:"1";}s:15:"is_completed_op";s:2:"eq";s:18:"is_completed_value";s:1:"1";s:15:"mailing_name_op";s:2:"in";s:18:"mailing_name_value";a:0:{}s:9:"status_op";s:3:"has";s:12:"status_value";s:8:"Complete";s:11:"is_test_min";s:0:"";s:11:"is_test_max";s:0:"";s:10:"is_test_op";s:3:"lte";s:13:"is_test_value";s:1:"0";s:19:"start_date_relative";s:9:"this.year";s:15:"start_date_from";s:0:"";s:13:"start_date_to";s:0:"";s:17:"end_date_relative";s:9:"this.year";s:13:"end_date_from";s:0:"";s:11:"end_date_to";s:0:"";s:11:"description";s:31:"Summary statistics for mailings";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:15:"access CiviMail";s:6:"groups";s:0:"";s:6:"charts";s:0:"";s:9:"domain_id";i:1;}{/literal}' WHERE report_id = 'Mailing/summary'; | |
74 | ||
75 | UPDATE civicrm_report_instance SET form_values = '{literal}a:37:{s:6:"fields";a:4:{s:2:"id";s:1:"1";s:10:"first_name";s:1:"1";s:9:"last_name";s:1:"1";s:5:"email";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:9:"source_op";s:3:"has";s:12:"source_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:15:"mailing_name_op";s:2:"in";s:18:"mailing_name_value";a:0:{}s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:8:"tagid_op";s:2:"in";s:11:"tagid_value";a:0:{}s:11:"custom_1_op";s:2:"in";s:14:"custom_1_value";a:0:{}s:11:"custom_2_op";s:2:"in";s:14:"custom_2_value";a:0:{}s:17:"custom_3_relative";s:1:"0";s:13:"custom_3_from";s:0:"";s:11:"custom_3_to";s:0:"";s:17:"custom_9_relative";s:1:"0";s:13:"custom_9_from";s:0:"";s:11:"custom_9_to";s:0:"";s:12:"custom_10_op";s:2:"in";s:15:"custom_10_value";a:0:{}s:12:"custom_11_op";s:3:"has";s:15:"custom_11_value";s:0:"";s:11:"description";s:49:"Display contacts who opened emails from a mailing";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:15:"access CiviMail";s:6:"groups";s:0:"";s:6:"charts";s:0:"";s:9:"domain_id";i:1;}{/literal}' WHERE report_id = 'Mailing/opened'; | |
76 | ||
77 | UPDATE civicrm_report_instance SET form_values = '{literal}a:37:{s:6:"fields";a:5:{s:2:"id";s:1:"1";s:10:"first_name";s:1:"1";s:9:"last_name";s:1:"1";s:5:"email";s:1:"1";s:3:"url";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:9:"source_op";s:3:"has";s:12:"source_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:15:"mailing_name_op";s:2:"in";s:18:"mailing_name_value";a:0:{}s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:8:"tagid_op";s:2:"in";s:11:"tagid_value";a:0:{}s:11:"custom_1_op";s:2:"in";s:14:"custom_1_value";a:0:{}s:11:"custom_2_op";s:2:"in";s:14:"custom_2_value";a:0:{}s:17:"custom_3_relative";s:1:"0";s:13:"custom_3_from";s:0:"";s:11:"custom_3_to";s:0:"";s:17:"custom_9_relative";s:1:"0";s:13:"custom_9_from";s:0:"";s:11:"custom_9_to";s:0:"";s:12:"custom_10_op";s:2:"in";s:15:"custom_10_value";a:0:{}s:12:"custom_11_op";s:3:"has";s:15:"custom_11_value";s:0:"";s:11:"description";s:32:"Display clicks from each mailing";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:15:"access CiviMail";s:6:"groups";s:0:"";s:6:"charts";s:0:"";s:9:"domain_id";i:1;}{/literal}' WHERE report_id = 'Mailing/clicks'; | |
78 | ||
79 | -- CRM-7115 | |
80 | UPDATE civicrm_payment_processor | |
81 | SET is_recur = 1, | |
10824d34 | 82 | payment_processor_type = 'AuthNet' |
6a488035 TO |
83 | WHERE payment_processor_type = 'AuthNet_AIM'; |
84 | ||
85 | UPDATE civicrm_payment_processor_type | |
10824d34 | 86 | SET is_recur = 1, |
87 | name = 'AuthNet', | |
6a488035 TO |
88 | title = '{ts escape="sql"}Authorize.Net{/ts}' |
89 | WHERE name = 'AuthNet_AIM'; | |
90 | ||
91 | ALTER TABLE `civicrm_contribution_recur` ADD `payment_processor_id` INT( 10 ) UNSIGNED NULL COMMENT 'Foreign key to civicrm_payment_processor.id'; | |
92 | ALTER TABLE `civicrm_contribution_recur` ADD CONSTRAINT `FK_civicrm_contribution_recur_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL; | |
93 | ||
94 | -- Pickup payment processor and fill payment processor id in recur contrib table. | |
95 | ||
96 | UPDATE civicrm_contribution_recur recur | |
97 | INNER JOIN civicrm_contribution contrib ON ( contrib.contribution_recur_id = recur.id ) | |
98 | INNER JOIN civicrm_entity_financial_trxn eft ON ( eft.entity_id = contrib.id AND entity_table = 'civicrm_contribution' ) | |
10824d34 | 99 | INNER JOIN civicrm_financial_trxn trxn ON ( trxn.id = eft.financial_trxn_id ) |
100 | INNER JOIN civicrm_payment_processor processor ON ( processor.payment_processor_type = trxn.payment_processor | |
6a488035 TO |
101 | AND processor.is_test = recur.is_test ) |
102 | SET recur.payment_processor_id = processor.id; | |
103 | ||
104 | -- done w/ CRM-7115 | |
105 | ||
106 | -- CRM-7137 | |
107 | ALTER TABLE `civicrm_membership` | |
108 | ADD `contribution_recur_id` int(10) unsigned default NULL COMMENT 'Conditional foreign key to civicrm_contribution_recur.id.', | |
109 | ADD CONSTRAINT `FK_civicrm_membership_contribution_recur_id` FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur` (`id`) ON DELETE SET NULL; | |
110 | ||
10824d34 | 111 | ALTER TABLE `civicrm_membership_type` |
6a488035 TO |
112 | ADD `auto_renew` TINYINT (4) NULL DEFAULT '0', |
113 | ADD `autorenewal_msg_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_msg_template.id', | |
114 | ADD CONSTRAINT `FK_civicrm_membership_autorenewal_msg_id` FOREIGN KEY (`autorenewal_msg_id`) REFERENCES `civicrm_msg_template` (`id`) ON DELETE SET NULL; | |
115 | ||
116 | -- CRM-7137 | |
10824d34 | 117 | |
6a488035 | 118 | {include file='../CRM/Upgrade/3.3.2.msg_template/civicrm_msg_template.tpl'} |