[civicrm-core.git] / CRM / Upgrade / Incremental / sql / 3.2.alpha1.mysql.tpl

-- CRM-5536, CRM-5535

INSERT INTO civicrm_payment_processor_type
( 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)
VALUES
( '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),
( '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);

-- CRM-5461
    SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type';
    SELECT @activity_type_max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_act;
    SELECT @activity_type_max_wt  := MAX(ROUND(val.weight)) FROM civicrm_option_value val where val.option_group_id = @option_group_id_act;

    INSERT INTO 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`, `domain_id`, `visibility_id`)
    VALUES
        ( @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);

-- CRM-5344
    ALTER TABLE civicrm_uf_group
    MODIFY notify text;

-- CRM-5598

SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';

SELECT @atOpt_max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type;

SELECT @atOpt_max_wt  := MAX(ROUND(val.weight)) FROM civicrm_option_value val where val.option_group_id = @option_group_id_activity_type;

SELECT @caseCompId    := max(id) FROM civicrm_component where name = 'CiviCase';

INSERT INTO
   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`)
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 ),
      (@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 ),
      (@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 );


-- CRM-5752
    UPDATE civicrm_option_value val
        LEFT JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
        SET val.is_reserved = 1
        WHERE gr.name = 'contribution_status' AND val.name IN ( 'Completed', 'Pending', 'Cancelled', 'Failed', 'In Progress', 'Overdue' );

-- CRM-5831
    ALTER TABLE civicrm_email
      ADD `signature_text` text COLLATE utf8_unicode_ci COMMENT 'Text formatted signature for the email.',
  ADD `signature_html` text COLLATE utf8_unicode_ci COMMENT 'HTML formatted signature for the email.';

-- CRM-5787
   UPDATE civicrm_option_value val
         INNER JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
  SET val.grouping = 'Opened'
  WHERE gr.name = 'case_status' AND val.name IN ( 'Open', 'Urgent' );

   UPDATE civicrm_option_value val
         INNER JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
  SET val.grouping = 'Closed'
  WHERE gr.name = 'case_status' AND val.name = 'Closed';

   SELECT @domain_id := min(id) FROM civicrm_domain;
   SELECT @nav_case    := id FROM civicrm_navigation WHERE name = 'CiviCase';
   SELECT @nav_case_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_case;

   INSERT INTO civicrm_navigation
        ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
   VALUES
  ( @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 );

-- CRM-5766
   ALTER TABLE civicrm_price_field
   ADD `visibility_id` int(10) unsigned default 1 COMMENT 'Implicit FK to civicrm_option_group with name = visibility.';

-- CRM-5612
   ALTER TABLE civicrm_cache
   MODIFY path varchar(255) COMMENT 'Unique path name for cache element';

-- CRM-5874
   ALTER TABLE civicrm_uf_group
   ADD `is_proximity_search` tinyint(4) unsigned default 0 COMMENT 'Should proximity search be included in profile search form?';

-- CRM-5724

   ALTER TABLE civicrm_price_field
   ADD `count` int(10) unsigned default NULL COMMENT 'Participant count for field.';

   ALTER TABLE civicrm_line_item
   ADD `participant_count` int(10) unsigned default NULL COMMENT 'Number of Participants Per field.';

-- CRM-5970
-- civicrm_entity_financial_trxn
   CREATE TABLE `civicrm_entity_financial_trxn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `entity_id` int(10) unsigned NOT NULL,
  `financial_trxn_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity',
  PRIMARY KEY (`id`),
  KEY `FK_civicrm_entity_financial_trxn_financial_trxn_id` (`financial_trxn_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- civicrm_financial_account
   CREATE TABLE `civicrm_financial_account` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `account_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Constraints for table `civicrm_entity_financial_trxn`
   ALTER TABLE `civicrm_entity_financial_trxn`
     ADD CONSTRAINT `FK_civicrm_entity_financial_trxn_financial_trxn_id` FOREIGN KEY (`financial_trxn_id`) REFERENCES `civicrm_financial_trxn` (`id`) ON DELETE SET NULL;

-- Insert financial_trxn_id.contribution_id values into new rows in civicrm_entity_financial_trxn to preserve existing linkages
    INSERT INTO civicrm_entity_financial_trxn (financial_trxn_id, amount, entity_id, entity_table)
    SELECT id, total_amount, contribution_id, 'civicrm_contribution'
    FROM   civicrm_financial_trxn ft
    ON DUPLICATE KEY UPDATE civicrm_entity_financial_trxn.entity_id = ft.contribution_id;

-- ALTER civicrm_financial_trxn
   ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id`  ;
   ALTER TABLE `civicrm_financial_trxn`
       DROP `contribution_id`;
   ALTER TABLE `civicrm_financial_trxn`
       ADD `from_account_id` INT( 10 ) unsigned NULL,
       ADD `to_account_id` INT( 10 ) unsigned NULL;
   ALTER TABLE `civicrm_financial_trxn`
       ADD FOREIGN KEY `FK_civicrm_financial_trxn_from_account_id` ( `from_account_id` ) REFERENCES `civicrm_financial_account`  (`id`) ,
       ADD FOREIGN KEY `FK_civicrm_financial_trxn_to_account_id` (`to_account_id`) REFERENCES `civicrm_financial_account`(`id`);

-- INSERT civicrm_option_group
   INSERT INTO
   `civicrm_option_group` (`name`, {localize field='description'}`description`{/localize} , `is_reserved`, `is_active`)
VALUES
    ('account_type',{localize}'{ts escape="sql"}Account type{/ts}'{/localize}, 0, 1);

-- INSERT Account types
   SELECT @option_group_id_accTp          := max(id) from civicrm_option_group where name = 'account_type';
   INSERT INTO
   `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`)
   VALUES
   (@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),
   (@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),
   (@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),
   (@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);

--  CRM-5883

--  add table civicrm_website
    CREATE TABLE civicrm_website (
        id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Website Id.',
        contact_id int unsigned NULL DEFAULT NULL COMMENT 'FK To Contact ID.',
        url varchar(128) NULL DEFAULT NULL COMMENT 'Website.',
        website_type_id int unsigned NULL DEFAULT NULL COMMENT 'Which Website type does this website belong to.',
        PRIMARY KEY ( id ),
  INDEX UI_website_type_id( website_type_id ),
  CONSTRAINT FK_civicrm_website_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE
    )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;

--  insert home_URL and image_URL for already exists contacts
    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 ;

--  drop columns home_URL
    ALTER TABLE civicrm_contact DROP home_URL;

--  add option group website_type
    INSERT INTO civicrm_option_group
        (name, {localize field='description'}description{/localize}, is_reserved, is_active)
    VALUES
        ('website_type', {localize}'Website Type'{/localize} , 0, 1),
        ('tag_used_for', {localize}'Tag Used For'{/localize}, 0, 1);
    SELECT @option_group_id_website := max(id) FROM civicrm_option_group WHERE name = 'website_type' ;
    SELECT @option_group_id_tuf := max(id) FROM civicrm_option_group WHERE name = 'tag_used_for' ;

    INSERT INTO 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)
    VALUES
       (@option_group_id_website, {localize}'Home' {/localize},    1, 'Home',     NULL, 0, NULL, 1,{localize} NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_website, {localize}'Work'{/localize},     2, 'Work',     NULL, 0, NULL, 2, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_website, {localize}'Facebook'{/localize}, 3, 'Facebook', NULL, 0, NULL, 3, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_website, {localize}'Twitter'{/localize},  4, 'Twitter',  NULL, 0, NULL, 4,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_website, {localize}'MySpace'{/localize},  5, 'MySpace',  NULL, 0, NULL, 5, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_tuf, {localize}'Contacts'{/localize}, 'civicrm_contact', 'Contacts', NULL, 0, NULL, 1,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_tuf, {localize}'Activities'{/localize}, 'civicrm_activity', 'Activities',  NULL, 0, NULL, 2,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
       (@option_group_id_tuf, {localize}'Cases'{/localize}, 'civicrm_case', 'Cases', NULL, 0, NULL, 3,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL);

--  CRM-5962

--  add columns entity_table , entity_id in civicrm_entity_tag
    ALTER TABLE civicrm_entity_tag
    ADD entity_table varchar(64) NULL DEFAULT NULL COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact' AFTER id,
    DROP FOREIGN KEY FK_civicrm_entity_tag_contact_id,
    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.',
    ADD INDEX index_entity (entity_table, entity_id) ;

--  entity_table field for exists records is civicrm_contact
    UPDATE civicrm_entity_tag
    SET entity_table ='civicrm_contact' ;

--  add is_reserved, is_hidden, used_for in civicrm_tag
    ALTER TABLE civicrm_tag
    ADD is_reserved tinyint DEFAULT 0,
    ADD is_hidden tinyint DEFAULT 0,
    ADD used_for varchar(64) NULL DEFAULT NULL;

   UPDATE civicrm_tag
   SET used_for ='civicrm_contact';

-- Add new activity type Change Case Tag
 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
 SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
 SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
 INSERT INTO civicrm_option_value
      (option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, is_default, weight,is_reserved, is_active, component_id )
VALUES
       ( @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);

   {include file='../CRM/Upgrade/3.2.alpha1.msg_template/civicrm_msg_template.tpl'}

-- CRM-6024
   UPDATE civicrm_participant_status_type
       SET is_counted = 0
       WHERE name = 'Pending from incomplete transaction';

-- CRM-6004
ALTER TABLE civicrm_uf_field
  ADD help_pre text COLLATE utf8_unicode_ci COMMENT 'Description and/or help text to display before this field.';

-- CRM-6002
    INSERT INTO `civicrm_state_province`
        (`name`, `abbreviation`, `country_id`)
    VALUES
        ('La Rioja', 'F', 1010 );

-- CRM-6037
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Host';
INSERT INTO civicrm_mailing_bounce_pattern
        (bounce_type_id, pattern)
        VALUES
    (@bounceTypeID, 'not connected');

-- CRM-6045
UPDATE civicrm_payment_processor_type
SET url_site_test_default = 'https://www.payjunctionlabs.com/quick_link' WHERE name = 'PayJunction';

-- CRM-5803
   SELECT @domain_id := min(id) FROM civicrm_domain;
   SELECT @nav_search    := id FROM civicrm_navigation WHERE name = 'Search...';
   SELECT @nav_max_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_search;
   SELECT @nav_find_pledge_weight := MAX(ROUND(weight)) from civicrm_navigation WHERE parent_id = @nav_search AND has_separator = 1;
   SELECT @pledge_id := id from civicrm_navigation WHERE parent_id = @nav_search AND weight = @nav_find_pledge_weight;

   UPDATE civicrm_navigation SET has_separator = NULL WHERE id = @pledge_id LIMIT 1;
   UPDATE civicrm_navigation SET weight =  @nav_max_weight+1 WHERE parent_id = @nav_search AND weight = @nav_max_weight;

   INSERT INTO civicrm_navigation
        ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
   VALUES
        ( @domain_id,  'civicrm/activity/search&reset=1', '{ts escape="sql"}Find Activities{/ts}','Find Activities', NULL,
'', @nav_search, '1', 1, @nav_find_pledge_weight );

  SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type';
  SELECT @max_val            := MAX(ROUND(op.value))   FROM civicrm_option_value op  WHERE op.option_group_id  = @option_group_id_mt;
  SELECT @max_wt             := MAX(ROUND(val.weight)) FROM civicrm_option_value val WHERE val.option_group_id = @option_group_id_mt;

   INSERT INTO civicrm_option_value
   (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`)
VALUES
   (@option_group_id_mt, {localize}'Export Activities'{/localize},  @max_val+1, 'Export Activity', NULL, 0, 0, @max_wt+1, 0, 1, 1);

-- CRM-6063
   INSERT IGNORE INTO civicrm_state_province
        (`name`, `abbreviation`, `country_id` )
   VALUES
        ( 'Andorra la Vella', '07', 1005 ),
        ( 'Canillo', '02', 1005 ),
        ( 'Encamp', '03', 1005 ),
        ( 'Escaldes-Engordany', '08', 1005 ),
        ( 'La Massana', '04', 1005 ),
        ( 'Ordino','05', 1005 ),
        ( 'Sant Julia de Loria', '06', 1005 );

-- CRM-5673
ALTER TABLE civicrm_contact ADD is_deleted TINYINT;
ALTER TABLE civicrm_contact ADD INDEX index_is_deleted(is_deleted);

-- CRM-5467
   ALTER TABLE civicrm_contact
   MODIFY image_URL varchar(255) COMMENT 'Optional URL for preferred image (photo, logo, etc.) to display for this contact';

-- CRM-6095
   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';
   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');
   UPDATE civicrm_navigation SET permission ='access all cases and activities' WHERE permission='access CiviCase';
   UPDATE civicrm_navigation SET permission ='access CiviGrant,administer CiviCase,access my cases and activities,access all cases and activities' WHERE civicrm_navigation.name= 'Other';
   UPDATE civicrm_navigation SET permission ='administer CiviCase', permission_operator= NULL WHERE civicrm_navigation.name IN ( 'CiviCase','Case Types', 'Redaction Rules');
   UPDATE civicrm_report_instance SET permission ='access all cases and activities' WHERE permission='access CiviCase';
Commit	Line	Data
6a488035 TO	1	-- CRM-5536, CRM-5535
6a488035 TO	2
10824d34	3	INSERT INTO civicrm_payment_processor_type
10824d34	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)
6a488035 TO	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
10824d34	9	-- CRM-5461
6a488035 TO	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
10824d34	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 ),
6a488035 TO	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 ),
6a488035 TO	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 );
10824d34	38
6a488035 TO	39
6a488035 TO	40	-- CRM-5752
10824d34	41	UPDATE civicrm_option_value val
10824d34	42	LEFT JOIN civicrm_option_group gr ON ( gr.id = val.option_group_id )
6a488035 TO	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
10824d34	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.';
6a488035 TO	50
	51	-- CRM-5787
	52	UPDATE civicrm_option_value val
10824d34	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
6a488035	57	UPDATE civicrm_option_value val
10824d34	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';
6a488035 TO	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
10824d34	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 );
6a488035 TO	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';
10824d34	78
6a488035 TO	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.';
10824d34	90
6a488035 TO	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;
10824d34	114
6a488035 TO	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
10824d34	122	ALTER TABLE `civicrm_financial_trxn`
6a488035	123	DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` ;
10824d34	124	ALTER TABLE `civicrm_financial_trxn`
6a488035 TO	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`
10824d34	130	ADD FOREIGN KEY `FK_civicrm_financial_trxn_from_account_id` ( `from_account_id` ) REFERENCES `civicrm_financial_account` (`id`) ,
6a488035	131	ADD FOREIGN KEY `FK_civicrm_financial_trxn_to_account_id` (`to_account_id`) REFERENCES `civicrm_financial_account`(`id`);
10824d34	132
6a488035	133	-- INSERT civicrm_option_group
10824d34	134	INSERT INTO
	135	`civicrm_option_group` (`name`, {localize field='description'}`description`{/localize} , `is_reserved`, `is_active`)
	136	VALUES
6a488035	137	('account_type',{localize}'{ts escape="sql"}Account type{/ts}'{/localize}, 0, 1);
10824d34	138
6a488035 TO	139	-- INSERT Account types
6a488035 TO	140	SELECT @option_group_id_accTp := max(id) from civicrm_option_group where name = 'account_type';
10824d34	141	INSERT INTO
10824d34	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`)
6a488035 TO	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
10824d34	151	-- add table civicrm_website
6a488035 TO	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.',
10824d34	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
6a488035	160	) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
10824d34	161
6a488035 TO	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)
10824d34	171	VALUES
6a488035 TO	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' ;
10824d34	176
6a488035	177	INSERT INTO civicrm_option_value
10824d34	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)
6a488035 TO	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),
10824d34	186	(@option_group_id_tuf, {localize}'Activities'{/localize}, 'civicrm_activity', 'Activities', NULL, 0, NULL, 2,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
6a488035	187	(@option_group_id_tuf, {localize}'Cases'{/localize}, 'civicrm_case', 'Cases', NULL, 0, NULL, 3,{localize}NULL{/localize}, 0, 0, 1, NULL, NULL);
10824d34	188
6a488035 TO	189	-- CRM-5962
	190
	191	-- add columns entity_table , entity_id in civicrm_entity_tag
10824d34	192	ALTER TABLE civicrm_entity_tag
6a488035 TO	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
10824d34	199	UPDATE civicrm_entity_tag
6a488035 TO	200	SET entity_table ='civicrm_contact' ;
	201
	202	-- add is_reserved, is_hidden, used_for in civicrm_tag
10824d34	203	ALTER TABLE civicrm_tag
	204	ADD is_reserved tinyint DEFAULT 0,
	205	ADD is_hidden tinyint DEFAULT 0,
6a488035 TO	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
10824d34	217	(option_group_id, {localize field='label'}label{/localize}, value, name, grouping, filter, is_default, weight,is_reserved, is_active, component_id )
10824d34	218	VALUES
6a488035 TO	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
10824d34	225	SET is_counted = 0
10824d34	226	WHERE name = 'Pending from incomplete transaction';
6a488035 TO	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
10824d34	246	UPDATE civicrm_payment_processor_type
6a488035 TO	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;
10824d34	255
6a488035 TO	256	UPDATE civicrm_navigation SET has_separator = NULL WHERE id = @pledge_id LIMIT 1;
6a488035 TO	257	UPDATE civicrm_navigation SET weight = @nav_max_weight+1 WHERE parent_id = @nav_search AND weight = @nav_max_weight;
10824d34	258
6a488035 TO	259	INSERT INTO civicrm_navigation
	260	( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
	261	VALUES
10824d34	262	( @domain_id, 'civicrm/activity/search&reset=1', '{ts escape="sql"}Find Activities{/ts}','Find Activities', NULL,
6a488035	263	'', @nav_search, '1', 1, @nav_find_pledge_weight );
10824d34	264
6a488035 TO	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
10824d34	270	(`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`)
6a488035 TO	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
cff67e98	275	INSERT IGNORE INTO civicrm_state_province
6a488035 TO	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';