CRM/Upgrade/Incremental/sql/3.3.2.mysql.tpl

   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
  25 DROP FOREIGN KEY parent_id,
  26 DROP INDEX parent_id ,
  27 ADD CONSTRAINT FK_civicrm_mailing_job_parent_id
  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 )
  63
  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 ),
  67   (@option_group_id_approvalStatus , '{ts escape="sql"}None{/ts}',     'None',    3,  3,   1, @mailCompId, 0 );
  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,
  82      payment_processor_type =  'AuthNet'
  83  WHERE  payment_processor_type = 'AuthNet_AIM';
  84
  85 UPDATE  civicrm_payment_processor_type
  86    SET  is_recur = 1,
  87         name = 'AuthNet',
  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' )
  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
 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
 111  ALTER TABLE `civicrm_membership_type`
 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
 117
 118   {include file='../CRM/Upgrade/3.3.2.msg_template/civicrm_msg_template.tpl'}