CRM/Upgrade/Incremental/sql/3.2.alpha1.mysql.tpl

   1 -- CRM-5536, CRM-5535
   2
   3 INSERT INTO civicrm_payment_processor_type
   4 ( name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_api_default, url_recur_default, url_button_default, url_site_test_default, url_api_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur, payment_type)
   5 VALUES
   6 ( 'PayflowPro', '{ts escape="sql"}PayflowPro{/ts}', NULL, 1, 0, 'Vendor ID', 'Password', 'Partner (merchant)', 'User', 'Payment_PayflowPro', 'https://Payflowpro.paypal.com', NULL, NULL, NULL, 'https://pilot-Payflowpro.paypal.com', NULL, NULL, NULL, 1, 0, 1),
   7 ( 'FirstData', '{ts escape="sql"}FirstData (aka linkpoint){/ts}', '{ts escape="sql"}FirstData (aka linkpoint){/ts}', 1, 0, 'Store Name', 'Certificate Path', NULL, NULL, 'Payment_FirstData', 'https://secure.linkpt.net', NULL, NULL, NULL, 'https://staging.linkpt.net', NULL, NULL, NULL, 1, NULL, 1);
   8
   9 -- CRM-5461
  10     SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type';
  11     SELECT @activity_type_max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act;
  12     SELECT @activity_type_max_wt  := MAX(ROUND(val.weight)) FROM civicrm_option_value val where val.option_group_id = @option_group_id_act;
  13
  14     INSERT INTO civicrm_option_value
  15         ( `option_group_id`,{localize field='label'}`label`{/localize},`value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `domain_id`, `visibility_id`)
  16     VALUES
  17         ( @option_group_id_act, {localize}'Print PDF Letter'{/localize}, (SELECT @activity_type_max_val := @activity_type_max_val + 1 ), 'Print PDF Letter', NULL, 1, NULL, (SELECT @activity_type_max_wt := @activity_type_max_wt + 1 ), {localize}'Print PDF Letter.'{/localize}, 0, 1, 1, NULL, NULL, NULL);
  18
  19 -- CRM-5344
  20     ALTER TABLE civicrm_uf_group
  21     MODIFY notify text;
  22
  23 -- CRM-5598
  24
  25 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
  26
  27 SELECT @atOpt_max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type;
  28
  29 SELECT @atOpt_max_wt  := MAX(ROUND(val.weight)) FROM civicrm_option_value val where val.option_group_id = @option_group_id_activity_type;
  30
  31 SELECT @caseCompId    := max(id) FROM civicrm_component where name = 'CiviCase';
  32
  33 INSERT INTO
  34    civicrm_option_value(`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`)
  35 VALUES(@option_group_id_activity_type, {localize}'Merge Case'{/localize}, (SELECT @atOpt_max_val := @atOpt_max_val+1), 'Merge Case', NULL, 0, NULL, (SELECT @atOpt_max_wt := @atOpt_max_wt + 1 ), 0, 1, 1, @caseCompId, NULL ),
  36       (@option_group_id_activity_type, {localize}'Reassigned Case'{/localize}, (SELECT @atOpt_max_val := @atOpt_max_val+1), 'Reassigned Case', NULL, 0, NULL, (SELECT @atOpt_max_wt := @atOpt_max_wt + 1 ), 0, 1, 1, @caseCompId, NULL ),
  37       (@option_group_id_activity_type, {localize}'Link Cases'{/localize}, (SELECT @atOpt_max_val := @atOpt_max_val+1), 'Link Cases', NULL, 0, NULL, (SELECT @atOpt_max_wt := @atOpt_max_wt + 1 ), 0, 1, 1, @caseCompId, NULL );
  38
  39
  40 -- CRM-5752
  41     UPDATE civicrm_option_value val
  42         LEFT JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
  43         SET val.is_reserved = 1
  44         WHERE gr.name = 'contribution_status' AND val.name IN ( 'Completed', 'Pending', 'Cancelled', 'Failed', 'In Progress', 'Overdue' );
  45
  46 -- CRM-5831
  47     ALTER TABLE civicrm_email
  48       ADD `signature_text` text COLLATE utf8_unicode_ci COMMENT 'Text formatted signature for the email.',
  49   ADD `signature_html` text COLLATE utf8_unicode_ci COMMENT 'HTML formatted signature for the email.';
  50
  51 -- CRM-5787
  52    UPDATE civicrm_option_value val
  53          INNER JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
  54   SET val.grouping = 'Opened'
  55   WHERE gr.name = 'case_status' AND val.name IN ( 'Open', 'Urgent' );
  56
  57    UPDATE civicrm_option_value val
  58          INNER JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
  59   SET val.grouping = 'Closed'
  60   WHERE gr.name = 'case_status' AND val.name = 'Closed';
  61
  62    SELECT @domain_id := min(id) FROM civicrm_domain;
  63    SELECT @nav_case    := id FROM civicrm_navigation WHERE name = 'CiviCase';
  64    SELECT @nav_case_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_case;
  65
  66    INSERT INTO civicrm_navigation
  67         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
  68    VALUES
  69   ( @domain_id, 'civicrm/admin/options/case_status&group=case_status&reset=1', '{ts escape="sql"}Case Statuses{/ts}','Case Statuses',  'administer CiviCase', NULL, @nav_case, '1', NULL, @nav_case_weight+1 );
  70
  71 -- CRM-5766
  72    ALTER TABLE civicrm_price_field
  73    ADD `visibility_id` int(10) unsigned default 1 COMMENT 'Implicit FK to civicrm_option_group with name = visibility.';
  74
  75 -- CRM-5612
  76    ALTER TABLE civicrm_cache
  77    MODIFY path varchar(255) COMMENT 'Unique path name for cache element';
  78
  79 -- CRM-5874
  80    ALTER TABLE civicrm_uf_group
  81    ADD `is_proximity_search` tinyint(4) unsigned default 0 COMMENT 'Should proximity search be included in profile search form?';
  82
  83 -- CRM-5724
  84
  85    ALTER TABLE civicrm_price_field
  86    ADD `count` int(10) unsigned default NULL COMMENT 'Participant count for field.';
  87
  88    ALTER TABLE civicrm_line_item
  89    ADD `participant_count` int(10) unsigned default NULL COMMENT 'Number of Participants Per field.';
  90
  91 -- CRM-5970
  92 -- civicrm_entity_financial_trxn
  93    CREATE TABLE `civicrm_entity_financial_trxn` (
  94   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  95   `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  96   `entity_id` int(10) unsigned NOT NULL,
  97   `financial_trxn_id` int(10) unsigned DEFAULT NULL,
  98   `amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity',
  99   PRIMARY KEY (`id`),
 100   KEY `FK_civicrm_entity_financial_trxn_financial_trxn_id` (`financial_trxn_id`)
 101 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 102
 103 -- civicrm_financial_account
 104    CREATE TABLE `civicrm_financial_account` (
 105   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 106   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 107   `account_type_id` int(10) unsigned NOT NULL,
 108   PRIMARY KEY (`id`)
 109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 110
 111 -- Constraints for table `civicrm_entity_financial_trxn`
 112    ALTER TABLE `civicrm_entity_financial_trxn`
 113      ADD CONSTRAINT `FK_civicrm_entity_financial_trxn_financial_trxn_id` FOREIGN KEY (`financial_trxn_id`) REFERENCES `civicrm_financial_trxn` (`id`) ON DELETE SET NULL;
 114
 115 -- Insert financial_trxn_id.contribution_id values into new rows in civicrm_entity_financial_trxn to preserve existing linkages
 116     INSERT INTO civicrm_entity_financial_trxn (financial_trxn_id, amount, entity_id, entity_table)
 117     SELECT id, total_amount, contribution_id, 'civicrm_contribution'
 118     FROM   civicrm_financial_trxn ft
 119     ON DUPLICATE KEY UPDATE civicrm_entity_financial_trxn.entity_id = ft.contribution_id;
 120
 121 -- ALTER civicrm_financial_trxn
 122    ALTER TABLE `civicrm_financial_trxn`
 123        DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id`  ;
 124    ALTER TABLE `civicrm_financial_trxn`
 125        DROP `contribution_id`;
 126    ALTER TABLE `civicrm_financial_trxn`
 127        ADD `from_account_id` INT( 10 ) unsigned NULL,
 128        ADD `to_account_id` INT( 10 ) unsigned NULL;
 129    ALTER TABLE `civicrm_financial_trxn`
 130        ADD FOREIGN KEY `FK_civicrm_financial_trxn_from_account_id` ( `from_account_id` ) REFERENCES `civicrm_financial_account`  (`id`) ,
 131        ADD FOREIGN KEY `FK_civicrm_financial_trxn_to_account_id` (`to_account_id`) REFERENCES `civicrm_financial_account`(`id`);
 132
 133 -- INSERT civicrm_option_group
 134    INSERT INTO
 135    `civicrm_option_group` (`name`, {localize field='description'}`description`{/localize} , `is_reserved`, `is_active`)
 136 VALUES
 137     ('account_type',{localize}'{ts escape="sql"}Account type{/ts}'{/localize}, 0, 1);
 138
 139 -- INSERT Account types
 140    SELECT @option_group_id_accTp          := max(id) from civicrm_option_group where name = 'account_type';
 141    INSERT INTO
 142    `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
 143    VALUES
 144    (@option_group_id_accTp, {localize}'{ts escape="sql"}Asset{/ts}'{/localize}, 1, 'Asset',  NULL, 0, NULL, 1,{localize} NULL{/localize} , 0, 0, 1, NULL, NULL),
 145    (@option_group_id_accTp,{localize}'{ts escape="sql"}Liability{/ts}'{/localize}, 2, 'Liability',  NULL, 0, NULL, 1,{localize} NULL{/localize}  , 0, 0, 1, NULL, NULL),
 146    (@option_group_id_accTp,{localize}'{ts escape="sql"}Income{/ts}'{/localize}, 3, 'Income',  NULL, 0, NULL, 1,{localize} NULL {/localize}, 0, 0, 1, NULL, NULL),
 147    (@option_group_id_accTp, {localize}'{ts escape="sql"}Expense{/ts}'{/localize}, 4, 'Expense',  NULL, 0, NULL, 1,{localize}  NULL {/localize}, 0, 0, 1, NULL, NULL);
 148
 149 --  CRM-5883
 150
 151 --  add table civicrm_website
 152     CREATE TABLE civicrm_website (
 153         id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Website Id.',
 154         contact_id int unsigned NULL DEFAULT NULL COMMENT 'FK To Contact ID.',
 155         url varchar(128) NULL DEFAULT NULL COMMENT 'Website.',
 156         website_type_id int unsigned NULL DEFAULT NULL COMMENT 'Which Website type does this website belong to.',
 157         PRIMARY KEY ( id ),
 158   INDEX UI_website_type_id( website_type_id ),
 159   CONSTRAINT FK_civicrm_website_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE
 160     )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
 161
 162 --  insert home_URL and image_URL for already exists contacts
 163     INSERT INTO civicrm_website ( contact_id, url, website_type_id ) SELECT cc.id, cc.home_URL, 1 FROM civicrm_contact cc WHERE cc.home_URL IS NOT NULL ;
 164
 165 --  drop columns home_URL
 166     ALTER TABLE civicrm_contact DROP home_URL;
 167
 168 --  add option group website_type
 169     INSERT INTO civicrm_option_group
 170         (name, {localize field='description'}description{/localize}, is_reserved, is_active)
 171     VALUES
 172         ('website_type', {localize}'Website Type'{/localize} , 0, 1),
 173         ('tag_used_for', {localize}'Tag Used For'{/localize}, 0, 1);
 174     SELECT @option_group_id_website := max(id) FROM civicrm_option_group WHERE name = 'website_type' ;
 175     SELECT @option_group_id_tuf := max(id) FROM civicrm_option_group WHERE name = 'tag_used_for' ;
 176
 177     INSERT INTO civicrm_option_value
 178       (option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, is_default, weight, {localize field='description'}description{/localize} , is_optgroup, is_reserved, is_active, component_id, visibility_id)
 179     VALUES
 180        (@option_group_id_website, {localize}'Home' {/localize},    1, 'Home',     NULL, 0, NULL, 1,{localize} NULL{/localize}, 0, 0, 1, NULL, NULL),
 181        (@option_group_id_website, {localize}'Work'{/localize},     2, 'Work',     NULL, 0, NULL, 2, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 182        (@option_group_id_website, {localize}'Facebook'{/localize}, 3, 'Facebook', NULL, 0, NULL, 3, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 183        (@option_group_id_website, {localize}'Twitter'{/localize},  4, 'Twitter',  NULL, 0, NULL, 4,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 184        (@option_group_id_website, {localize}'MySpace'{/localize},  5, 'MySpace',  NULL, 0, NULL, 5, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 185        (@option_group_id_tuf, {localize}'Contacts'{/localize}, 'civicrm_contact', 'Contacts', NULL, 0, NULL, 1,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 186        (@option_group_id_tuf, {localize}'Activities'{/localize}, 'civicrm_activity', 'Activities',  NULL, 0, NULL, 2,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 187        (@option_group_id_tuf, {localize}'Cases'{/localize}, 'civicrm_case', 'Cases', NULL, 0, NULL, 3,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL);
 188
 189 --  CRM-5962
 190
 191 --  add columns entity_table , entity_id in civicrm_entity_tag
 192     ALTER TABLE civicrm_entity_tag
 193     ADD entity_table varchar(64) NULL DEFAULT NULL COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact' AFTER id,
 194     DROP FOREIGN KEY FK_civicrm_entity_tag_contact_id,
 195     DROP INDEX UI_contact_id_tag_id, CHANGE contact_id entity_id int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
 196     ADD INDEX index_entity (entity_table, entity_id) ;
 197
 198 --  entity_table field for exists records is civicrm_contact
 199     UPDATE civicrm_entity_tag
 200     SET entity_table ='civicrm_contact' ;
 201
 202 --  add is_reserved, is_hidden, used_for in civicrm_tag
 203     ALTER TABLE civicrm_tag
 204     ADD is_reserved tinyint DEFAULT 0,
 205     ADD is_hidden tinyint DEFAULT 0,
 206     ADD used_for varchar(64) NULL DEFAULT NULL;
 207
 208    UPDATE civicrm_tag
 209    SET used_for ='civicrm_contact';
 210
 211 -- Add new activity type Change Case Tag
 212  SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
 213  SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
 214  SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
 215  SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
 216  INSERT INTO civicrm_option_value
 217       (option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, is_default, weight,is_reserved, is_active, component_id )
 218 VALUES
 219        ( @option_group_id_activity_type,{localize}'Change Case Tags'{/localize},(SELECT @max_val := @max_val+1),'Change Case Tags','NULL',0,0,(SELECT @max_wt := @max_wt+1),1,1,@caseCompId);
 220
 221    {include file='../CRM/Upgrade/3.2.alpha1.msg_template/civicrm_msg_template.tpl'}
 222
 223 -- CRM-6024
 224    UPDATE civicrm_participant_status_type
 225        SET is_counted = 0
 226        WHERE name = 'Pending from incomplete transaction';
 227
 228 -- CRM-6004
 229 ALTER TABLE civicrm_uf_field
 230   ADD help_pre text COLLATE utf8_unicode_ci COMMENT 'Description and/or help text to display before this field.';
 231
 232 -- CRM-6002
 233     INSERT INTO `civicrm_state_province`
 234         (`name`, `abbreviation`, `country_id`)
 235     VALUES
 236         ('La Rioja', 'F', 1010 );
 237
 238 -- CRM-6037
 239 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Host';
 240 INSERT INTO civicrm_mailing_bounce_pattern
 241         (bounce_type_id, pattern)
 242         VALUES
 243     (@bounceTypeID, 'not connected');
 244
 245 -- CRM-6045
 246 UPDATE civicrm_payment_processor_type
 247 SET url_site_test_default = 'https://www.payjunctionlabs.com/quick_link' WHERE name = 'PayJunction';
 248
 249 -- CRM-5803
 250    SELECT @domain_id := min(id) FROM civicrm_domain;
 251    SELECT @nav_search    := id FROM civicrm_navigation WHERE name = 'Search...';
 252    SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_search;
 253    SELECT @nav_find_pledge_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_search AND has_separator = 1;
 254    SELECT @pledge_id := id from civicrm_navigation WHERE parent_id = @nav_search AND weight = @nav_find_pledge_weight;
 255
 256    UPDATE civicrm_navigation SET has_separator = NULL WHERE id = @pledge_id LIMIT 1;
 257    UPDATE civicrm_navigation SET weight =  @nav_max_weight+1 WHERE parent_id = @nav_search AND weight = @nav_max_weight;
 258
 259    INSERT INTO civicrm_navigation
 260         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 261    VALUES
 262         ( @domain_id,  'civicrm/activity/search&reset=1', '{ts escape="sql"}Find Activities{/ts}','Find Activities', NULL,
 263 '', @nav_search, '1', 1, @nav_find_pledge_weight );
 264
 265   SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type';
 266   SELECT @max_val            := MAX(ROUND(op.value))   FROM civicrm_option_value op  WHERE op.option_group_id  = @option_group_id_mt;
 267   SELECT @max_wt             := MAX(ROUND(val.weight)) FROM civicrm_option_value val WHERE val.option_group_id = @option_group_id_mt;
 268
 269    INSERT INTO civicrm_option_value
 270    (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`)
 271 VALUES
 272    (@option_group_id_mt, {localize}'Export Activities'{/localize},  @max_val+1, 'Export Activity', NULL, 0, 0, @max_wt+1, 0, 1, 1);
 273
 274 -- CRM-6063
 275    INSERT IGNORE INTO civicrm_state_province
 276         (`name`, `abbreviation`, `country_id` )
 277    VALUES
 278         ( 'Andorra la Vella', '07', 1005 ),
 279         ( 'Canillo', '02', 1005 ),
 280         ( 'Encamp', '03', 1005 ),
 281         ( 'Escaldes-Engordany', '08', 1005 ),
 282         ( 'La Massana', '04', 1005 ),
 283         ( 'Ordino','05', 1005 ),
 284         ( 'Sant Julia de Loria', '06', 1005 );
 285
 286 -- CRM-5673
 287 ALTER TABLE civicrm_contact ADD is_deleted TINYINT;
 288 ALTER TABLE civicrm_contact ADD INDEX index_is_deleted(is_deleted);
 289
 290 -- CRM-5467
 291    ALTER TABLE civicrm_contact
 292    MODIFY image_URL varchar(255) COMMENT 'Optional URL for preferred image (photo, logo, etc.) to display for this contact';
 293
 294 -- CRM-6095
 295    UPDATE civicrm_navigation SET permission ='access my cases and activities,access all cases and activities', permission_operator='OR' WHERE civicrm_navigation.name= 'Dashboard' AND url='civicrm/case&reset=1';
 296    UPDATE civicrm_navigation SET permission ='access my cases and activities,access all cases and activities', permission_operator='OR' WHERE civicrm_navigation.name IN ( 'Find Cases','Cases');
 297    UPDATE civicrm_navigation SET permission ='access all cases and activities' WHERE permission='access CiviCase';
 298    UPDATE civicrm_navigation SET permission ='access CiviGrant,administer CiviCase,access my cases and activities,access all cases and activities' WHERE civicrm_navigation.name= 'Other';
 299    UPDATE civicrm_navigation SET permission ='administer CiviCase', permission_operator= NULL WHERE civicrm_navigation.name IN ( 'CiviCase','Case Types', 'Redaction Rules');
 300    UPDATE civicrm_report_instance SET permission ='access all cases and activities' WHERE permission='access CiviCase';