[civicrm-core.git] / CRM / Upgrade / TwoOne / sql / group_values.mysql

-- /************************************************************************
-- *
-- * MySQL Script for civicrm database/tables - upgradation from 2.0 -> 2.1
-- *
-- * Changes / updates related to option group & values
-- *
-- *************************************************************************/


-- Insert new option value 'Pledge Acknowledgment', 'Pledge Reminder' for option_group - activity_type (CRM-3244, CRM-3270)
-- First get max weight and value

SELECT   @option_act := civicrm_option_group.id,
   @max_val    := max(ROUND(civicrm_option_value.value)),
         @max_wt     := max(civicrm_option_value.weight)
FROM     civicrm_option_value, civicrm_option_group
WHERE    civicrm_option_group.name = 'activity_type'
AND      civicrm_option_value.option_group_id = civicrm_option_group.id
GROUP BY civicrm_option_group.id;

-- Insert new option values for option_group - activity_type

INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name` , `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_act, 'Pledge Acknowledgment', (SELECT @max_val := @max_val+1), 'Pledge Acknowledgment', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Acknowledgment.', 0, 1, 1, NULL),
  (@option_act, 'Pledge Reminder', (SELECT @max_val := @max_val+1), 'Pledge Reminder', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Reminder.', 0, 1, 1, NULL);

-- Insert new option value for option_group - user_dashboard_options

SELECT @option_ud    := id from civicrm_option_group where name = 'user_dashboard_options';
INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `weight`, `is_active`, `is_default`) VALUES
( @option_ud, 'My Contacts / Organizations', 5, 5, 1, NULL);


-- add option group & values (CRM-2488)

INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('recur_frequency_units', '{ts escape="sql"}Recurring Frequency Units{/ts}', 0, 1);

SELECT @option_group_id_fu := max(id) from civicrm_option_group where name = 'recur_frequency_units';

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_fu, 'daily'    , 'day'  ,    'day',  NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'weekly'   , 'week' ,   'week',  NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'monthly'  , 'month',  'month',  NULL, 0, NULL, 3, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'yearly'   , 'year' ,   'year',  NULL, 0, NULL, 4, NULL, 0, 1, 1, NULL);


-- add option group and values CRM-2781

INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('mapping_type', '{ts escape="sql"}Mapping Type{/ts}', 0, 1);

SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type';

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_mt, '{ts escape="sql"}Search Builder{/ts}',      1, 'Search Builder',      NULL, 0, 0,    1, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Contact{/ts}',      2, 'Import Contact',      NULL, 0, 0,    2, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Activity{/ts}',     3, 'Import Activity',     NULL, 0, 0,    3, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Contribution{/ts}', 4, 'Import Contribution', NULL, 0, 0,    4, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Membership{/ts}',   5, 'Import Membership',   NULL, 0, 0,    5, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Participant{/ts}',  6, 'Import Participant',  NULL, 0, 0,    6, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Contact{/ts}',      7, 'Export Contact',      NULL, 0, 0,    7, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Contribution{/ts}', 8, 'Export Contribution', NULL, 0, 0,    8, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Membership{/ts}',   9, 'Export Membership',   NULL, 0, 0,    9, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Participant{/ts}', 10, 'Export Participant',  NULL, 0, 0,   10, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Pledge{/ts}',      11, 'Export Pledge',       NULL, 0, 0,   11, NULL, 0, 1, 1, NULL);

-- fix too much escaping in civicrm_option_value

UPDATE civicrm_option_value SET label = 'Addt\'l Address 1' WHERE label = 'Addt\\\'l Address 1';
UPDATE civicrm_option_value SET label = 'Addt\'l Address 2' WHERE label = 'Addt\\\'l Address 2';


-- fix for CRM-3209

UPDATE civicrm_option_value v,
       civicrm_option_group g
SET    v.description = v.name, v.name = v.value
WHERE  v.option_group_id = g.id
AND    g.name LIKE 'civicrm_price_field.amount.%';


-- CRM-3217

ALTER TABLE `civicrm_option_value` MODIFY `label` VARCHAR(255) NULL DEFAULT '' COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.';


-- Insert new option value for option_group = 'contribution_status'

SELECT @option_cs    := id from civicrm_option_group where name = 'contribution_status';
INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `weight`, `is_active`, `is_default`) VALUES
( @option_cs, 'Overdue', 6, 'Overdue', 6, 1, NULL);


ALTER TABLE `civicrm_option_group`
    ADD UNIQUE `UI_name` (name);

ALTER TABLE `civicrm_option_value`
    MODIFY label varchar(255) NOT NULL DEFAULT '';


-- option group / value changes

-- those affiliated to contact_view_options group need updation for name column

SELECT @option_group_id_cvOpt := max(id) from civicrm_option_group where name = 'contact_view_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_cvOpt;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_cvOpt, 'Activities'   ,   1, 'activity', NULL, 0, NULL,  1,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Relationships',   2, 'rel', NULL, 0, NULL,  2,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Groups'       ,   3, 'group', NULL, 0, NULL,  3,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Notes'        ,   4, 'note', NULL, 0, NULL,  4,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Tags'         ,   5, 'tag', NULL, 0, NULL,  5,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Change Log'   ,   6, 'log', NULL, 0, NULL,  6,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Contributions',   7, 'CiviContribute', NULL, 0, NULL,  7,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Memberships'  ,   8, 'CiviMember', NULL, 0, NULL,  8,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Events'       ,   9, 'CiviEvent', NULL, 0, NULL,  9,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Cases'        ,  10, 'CiviCase', NULL, 0, NULL,  10, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Grants'       ,  11, 'CiviGrant', NULL, 0, NULL,  11, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'PledgeBank'   ,  12, 'PledgeBank', NULL, 0, NULL,  12, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Pledges'      ,  13, 'CiviPledge', NULL, 0, NULL,  13, NULL, 0, 0, 1, NULL);

-- those affiliated to contact_edit_options group need updation for name column

SELECT @option_group_id_ceOpt          := max(id) from civicrm_option_group where name = 'contact_edit_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_ceOpt;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_ceOpt, 'Communication Preferences',   1, 'CommBlock', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Demographics'             ,   2, 'Demographics', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Tags and Groups'          ,   3, 'TagsAndGroups', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Notes'                    ,   4, 'Notes', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL);


-- those affiliated to advanced_search_options group

SELECT @option_group_id_asOpt          := max(id) from civicrm_option_group where name = 'advanced_search_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_asOpt;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_asOpt, 'Address Fields'          ,   1, 'location', NULL, 0, NULL,  1, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Custom Fields'           ,   2, 'custom', NULL, 0, NULL,  2, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Activities'              ,   3, 'activity', NULL, 0, NULL,  3, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Relationships'           ,   4, 'relationship', NULL, 0, NULL,  4, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Notes'                   ,   5, 'notes', NULL, 0, NULL,  5, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Change Log'              ,   6, 'changeLog', NULL, 0, NULL,  6, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Contributions'           ,   7, 'CiviContribute', NULL, 0, NULL,  7, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Memberships'             ,   8, 'CiviMember', NULL, 0, NULL,  8, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Events'                  ,   9, 'CiviEvent', NULL, 0, NULL, 9, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Cases'                   ,  10, 'CiviCase', NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Grants'                  ,  12, 'CiviGrant', NULL, 0, NULL, 12, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Demographics'            ,  13, 'demographics', NULL, 0, NULL, 13, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'PledgeBank'              ,  14, 'PledgeBank', NULL, 0, NULL, 14, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Pledges'                 ,  15, 'CiviPledge', NULL, 0, NULL, 15, NULL, 0, 0, 1, NULL);


-- those affiliated to user_dashboard_options group

SELECT @option_group_id_udOpt          := max(id) from civicrm_option_group where name = 'user_dashboard_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_udOpt;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_udOpt, 'Groups'                     , 1, 'Groups', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Contributions'              , 2, 'CiviContribute', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Memberships'                , 3, 'CiviMember', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Events'                     , 4, 'CiviEvent', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'My Contacts / Organizations', 5, 'Permissioned Orgs', NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'PledgeBank'                 , 6, 'PledgeBank', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Pledges'                    , 7, 'CiviPledge', NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL);


-- those affiliated to address_options group

SELECT @option_group_id_adOpt          := max(id) from civicrm_option_group where name = 'address_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_adOpt;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_adOpt, 'Street Address'    ,  1, 'street_address', NULL, 0, NULL,  1, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Addt\'l Address 1'  ,  2, 'supplemental_address_1', NULL, 0, NULL,  2, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Addt\'l Address 2'  ,  3, 'supplemental_address_2', NULL, 0, NULL,  3, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'City'              ,  4, 'city'          , NULL, 0, NULL,  4, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Zip / Postal Code' ,  5, 'postal_code'   , NULL, 0, NULL,  5, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Postal Code Suffix',  6, 'postal_code_suffix', NULL, 0, NULL,  6, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'County'            ,  7, 'county'        , NULL, 0, NULL,  7, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'State / Province'  ,  8, 'state_province', NULL, 0, NULL,  8, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Country'           ,  9, 'country'       , NULL, 0, NULL,  9, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Latitude'          , 10, 'geo_code_1'    , NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Longitude'         , 11, 'geo_code_2', NULL, 0, NULL, 11, NULL, 0, 0, 1, NULL);


-- those affiliated to custom_search group

SELECT @option_group_id_csearch        := max(id) from civicrm_option_group where name = 'custom_search';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_csearch;

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Sample'               , 1, 'CRM_Contact_Form_Search_Custom_Sample'      , NULL, 0, NULL, 1, 'Household Name and State', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ContributionAggregate', 2, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', NULL, 0, NULL, 2, 'Contribution Aggregate', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Basic'                , 3, 'CRM_Contact_Form_Search_Custom_Basic'       , NULL, 0, NULL, 3, 'Basic Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Group'                , 4, 'CRM_Contact_Form_Search_Custom_Group'       , NULL, 0, NULL, 4, 'Include / Exclude Contacts in a Group / Tag', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PostalMailing'        , 5, 'CRM_Contact_Form_Search_Custom_PostalMailing', NULL, 0, NULL, 5, 'Postal Mailing', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Proximity'            , 6, 'CRM_Contact_Form_Search_Custom_Proximity', NULL, 0, NULL, 6, 'Proximity Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_EventAggregate', 7, 'CRM_Contact_Form_Search_Custom_EventAggregate', NULL, 0, NULL, 7, 'Event Aggregate', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ActivitySearch', 8, 'CRM_Contact_Form_Search_Custom_ActivitySearch', NULL, 0, NULL, 8, 'Activity Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PriceSet', 9, 'CRM_Contact_Form_Search_Custom_PriceSet', NULL, 0, NULL, 9, 'Price Set Details for Event Participants', 0, 0, 1, NULL);

-- option groups and values for 'wysiwyg_editor' , 'from_email_address'.
INSERT INTO
   `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`)
VALUES
   ('from_email_address', 'From Email Address', 0, 1),
   ('wysiwyg_editor'    , 'WYSIWYG Editor'    , 0, 1);
SELECT @option_we := max(id) from civicrm_option_group where name = 'wysiwyg_editor';

INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`,`name` , `grouping`, `filter`,`is_default`,`weight`, `description`,`is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_we, 'TinyMCE'    , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
  (@option_we, 'FCKEditor'  , 2, NULL, NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL);

-- Insert new option value address_name for option_group = 'address_options'

SELECT   @option_act := civicrm_option_group.id,
   @max_val    := max(ROUND(civicrm_option_value.value)),
         @max_wt     := max(civicrm_option_value.weight)
FROM     civicrm_option_value, civicrm_option_group
WHERE    civicrm_option_group.name = 'address_options'
AND      civicrm_option_value.option_group_id = civicrm_option_group.id
GROUP BY civicrm_option_group.id;


INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name` , `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
VALUES
  (@option_act, 'Address Name', (SELECT @max_val := @max_val+1), 'address_name', NULL, 0, NULL, (SELECT @max_wt := @max_wt+1), NULL, 0, 0, 1, NULL);

-- update filter for participant-status option values

SELECT @og_ps_id := id from civicrm_option_group where name = 'participant_status';

UPDATE civicrm_option_value SET filter=1 WHERE option_group_id=@og_ps_id AND label IN ('Registered', 'Attended');
Commit	Line	Data
6a488035 TO	1	-- /************************************************************************
	2	-- *
	3	-- * MySQL Script for civicrm database/tables - upgradation from 2.0 -> 2.1
	4	-- *
	5	-- * Changes / updates related to option group & values
	6	-- *
	7	-- *************************************************************************/
	8
	9
	10	-- Insert new option value 'Pledge Acknowledgment', 'Pledge Reminder' for option_group - activity_type (CRM-3244, CRM-3270)
	11	-- First get max weight and value
	12
	13	SELECT @option_act := civicrm_option_group.id,
10824d34	14	@max_val := max(ROUND(civicrm_option_value.value)),
10824d34	15	@max_wt := max(civicrm_option_value.weight)
6a488035 TO	16	FROM civicrm_option_value, civicrm_option_group
	17	WHERE civicrm_option_group.name = 'activity_type'
	18	AND civicrm_option_value.option_group_id = civicrm_option_group.id
	19	GROUP BY civicrm_option_group.id;
	20
	21	-- Insert new option values for option_group - activity_type
	22
10824d34	23	INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name` , `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	24	VALUES
	25	(@option_act, 'Pledge Acknowledgment', (SELECT @max_val := @max_val+1), 'Pledge Acknowledgment', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Acknowledgment.', 0, 1, 1, NULL),
	26	(@option_act, 'Pledge Reminder', (SELECT @max_val := @max_val+1), 'Pledge Reminder', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Reminder.', 0, 1, 1, NULL);
	27
	28	-- Insert new option value for option_group - user_dashboard_options
	29
	30	SELECT @option_ud := id from civicrm_option_group where name = 'user_dashboard_options';
	31	INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `weight`, `is_active`, `is_default`) VALUES
	32	( @option_ud, 'My Contacts / Organizations', 5, 5, 1, NULL);
	33
	34
	35	-- add option group & values (CRM-2488)
	36
	37	INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('recur_frequency_units', '{ts escape="sql"}Recurring Frequency Units{/ts}', 0, 1);
	38
	39	SELECT @option_group_id_fu := max(id) from civicrm_option_group where name = 'recur_frequency_units';
	40
10824d34	41	INSERT INTO
10824d34	42	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	43	VALUES
	44	(@option_group_id_fu, 'daily' , 'day' , 'day', NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
	45	(@option_group_id_fu, 'weekly' , 'week' , 'week', NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL),
	46	(@option_group_id_fu, 'monthly' , 'month', 'month', NULL, 0, NULL, 3, NULL, 0, 1, 1, NULL),
	47	(@option_group_id_fu, 'yearly' , 'year' , 'year', NULL, 0, NULL, 4, NULL, 0, 1, 1, NULL);
	48
	49
	50	-- add option group and values CRM-2781
	51
	52	INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('mapping_type', '{ts escape="sql"}Mapping Type{/ts}', 0, 1);
	53
	54	SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type';
	55
10824d34	56	INSERT INTO
10824d34	57	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	58	VALUES
	59	(@option_group_id_mt, '{ts escape="sql"}Search Builder{/ts}', 1, 'Search Builder', NULL, 0, 0, 1, NULL, 0, 1, 1, NULL),
	60	(@option_group_id_mt, '{ts escape="sql"}Import Contact{/ts}', 2, 'Import Contact', NULL, 0, 0, 2, NULL, 0, 1, 1, NULL),
	61	(@option_group_id_mt, '{ts escape="sql"}Import Activity{/ts}', 3, 'Import Activity', NULL, 0, 0, 3, NULL, 0, 1, 1, NULL),
	62	(@option_group_id_mt, '{ts escape="sql"}Import Contribution{/ts}', 4, 'Import Contribution', NULL, 0, 0, 4, NULL, 0, 1, 1, NULL),
	63	(@option_group_id_mt, '{ts escape="sql"}Import Membership{/ts}', 5, 'Import Membership', NULL, 0, 0, 5, NULL, 0, 1, 1, NULL),
	64	(@option_group_id_mt, '{ts escape="sql"}Import Participant{/ts}', 6, 'Import Participant', NULL, 0, 0, 6, NULL, 0, 1, 1, NULL),
	65	(@option_group_id_mt, '{ts escape="sql"}Export Contact{/ts}', 7, 'Export Contact', NULL, 0, 0, 7, NULL, 0, 1, 1, NULL),
	66	(@option_group_id_mt, '{ts escape="sql"}Export Contribution{/ts}', 8, 'Export Contribution', NULL, 0, 0, 8, NULL, 0, 1, 1, NULL),
	67	(@option_group_id_mt, '{ts escape="sql"}Export Membership{/ts}', 9, 'Export Membership', NULL, 0, 0, 9, NULL, 0, 1, 1, NULL),
	68	(@option_group_id_mt, '{ts escape="sql"}Export Participant{/ts}', 10, 'Export Participant', NULL, 0, 0, 10, NULL, 0, 1, 1, NULL),
	69	(@option_group_id_mt, '{ts escape="sql"}Export Pledge{/ts}', 11, 'Export Pledge', NULL, 0, 0, 11, NULL, 0, 1, 1, NULL);
	70
	71	-- fix too much escaping in civicrm_option_value
	72
	73	UPDATE civicrm_option_value SET label = 'Addt\'l Address 1' WHERE label = 'Addt\\\'l Address 1';
	74	UPDATE civicrm_option_value SET label = 'Addt\'l Address 2' WHERE label = 'Addt\\\'l Address 2';
	75
	76
	77	-- fix for CRM-3209
	78
	79	UPDATE civicrm_option_value v,
	80	civicrm_option_group g
	81	SET v.description = v.name, v.name = v.value
	82	WHERE v.option_group_id = g.id
	83	AND g.name LIKE 'civicrm_price_field.amount.%';
	84
	85
	86	-- CRM-3217
	87
	88	ALTER TABLE `civicrm_option_value` MODIFY `label` VARCHAR(255) NULL DEFAULT '' COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.';
	89
	90
	91	-- Insert new option value for option_group = 'contribution_status'
	92
	93	SELECT @option_cs := id from civicrm_option_group where name = 'contribution_status';
	94	INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `weight`, `is_active`, `is_default`) VALUES
	95	( @option_cs, 'Overdue', 6, 'Overdue', 6, 1, NULL);
	96
	97
	98	ALTER TABLE `civicrm_option_group`
	99	ADD UNIQUE `UI_name` (name);
	100
	101	ALTER TABLE `civicrm_option_value`
	102	MODIFY label varchar(255) NOT NULL DEFAULT '';
	103
	104
	105	-- option group / value changes
	106
	107	-- those affiliated to contact_view_options group need updation for name column
	108
	109	SELECT @option_group_id_cvOpt := max(id) from civicrm_option_group where name = 'contact_view_options';
	110
	111	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_cvOpt;
	112
10824d34	113	INSERT INTO
10824d34	114	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	115	VALUES
	116	(@option_group_id_cvOpt, 'Activities' , 1, 'activity', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
	117	(@option_group_id_cvOpt, 'Relationships', 2, 'rel', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
	118	(@option_group_id_cvOpt, 'Groups' , 3, 'group', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
	119	(@option_group_id_cvOpt, 'Notes' , 4, 'note', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
	120	(@option_group_id_cvOpt, 'Tags' , 5, 'tag', NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
	121	(@option_group_id_cvOpt, 'Change Log' , 6, 'log', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
	122	(@option_group_id_cvOpt, 'Contributions', 7, 'CiviContribute', NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL),
	123	(@option_group_id_cvOpt, 'Memberships' , 8, 'CiviMember', NULL, 0, NULL, 8, NULL, 0, 0, 1, NULL),
	124	(@option_group_id_cvOpt, 'Events' , 9, 'CiviEvent', NULL, 0, NULL, 9, NULL, 0, 0, 1, NULL),
	125	(@option_group_id_cvOpt, 'Cases' , 10, 'CiviCase', NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
	126	(@option_group_id_cvOpt, 'Grants' , 11, 'CiviGrant', NULL, 0, NULL, 11, NULL, 0, 0, 1, NULL),
	127	(@option_group_id_cvOpt, 'PledgeBank' , 12, 'PledgeBank', NULL, 0, NULL, 12, NULL, 0, 0, 1, NULL),
	128	(@option_group_id_cvOpt, 'Pledges' , 13, 'CiviPledge', NULL, 0, NULL, 13, NULL, 0, 0, 1, NULL);
	129
	130	-- those affiliated to contact_edit_options group need updation for name column
	131
	132	SELECT @option_group_id_ceOpt := max(id) from civicrm_option_group where name = 'contact_edit_options';
	133
	134	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_ceOpt;
	135
10824d34	136	INSERT INTO
10824d34	137	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	138	VALUES
	139	(@option_group_id_ceOpt, 'Communication Preferences', 1, 'CommBlock', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
	140	(@option_group_id_ceOpt, 'Demographics' , 2, 'Demographics', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
	141	(@option_group_id_ceOpt, 'Tags and Groups' , 3, 'TagsAndGroups', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
	142	(@option_group_id_ceOpt, 'Notes' , 4, 'Notes', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL);
	143
	144
	145	-- those affiliated to advanced_search_options group
	146
	147	SELECT @option_group_id_asOpt := max(id) from civicrm_option_group where name = 'advanced_search_options';
	148
	149	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_asOpt;
	150
10824d34	151	INSERT INTO
10824d34	152	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	153	VALUES
	154	(@option_group_id_asOpt, 'Address Fields' , 1, 'location', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
	155	(@option_group_id_asOpt, 'Custom Fields' , 2, 'custom', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
	156	(@option_group_id_asOpt, 'Activities' , 3, 'activity', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
	157	(@option_group_id_asOpt, 'Relationships' , 4, 'relationship', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
	158	(@option_group_id_asOpt, 'Notes' , 5, 'notes', NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
	159	(@option_group_id_asOpt, 'Change Log' , 6, 'changeLog', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
	160	(@option_group_id_asOpt, 'Contributions' , 7, 'CiviContribute', NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL),
	161	(@option_group_id_asOpt, 'Memberships' , 8, 'CiviMember', NULL, 0, NULL, 8, NULL, 0, 0, 1, NULL),
	162	(@option_group_id_asOpt, 'Events' , 9, 'CiviEvent', NULL, 0, NULL, 9, NULL, 0, 0, 1, NULL),
	163	(@option_group_id_asOpt, 'Cases' , 10, 'CiviCase', NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
	164	(@option_group_id_asOpt, 'Grants' , 12, 'CiviGrant', NULL, 0, NULL, 12, NULL, 0, 0, 1, NULL),
	165	(@option_group_id_asOpt, 'Demographics' , 13, 'demographics', NULL, 0, NULL, 13, NULL, 0, 0, 1, NULL),
	166	(@option_group_id_asOpt, 'PledgeBank' , 14, 'PledgeBank', NULL, 0, NULL, 14, NULL, 0, 0, 1, NULL),
	167	(@option_group_id_asOpt, 'Pledges' , 15, 'CiviPledge', NULL, 0, NULL, 15, NULL, 0, 0, 1, NULL);
	168
	169
	170	-- those affiliated to user_dashboard_options group
	171
	172	SELECT @option_group_id_udOpt := max(id) from civicrm_option_group where name = 'user_dashboard_options';
	173
	174	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_udOpt;
	175
10824d34	176	INSERT INTO
10824d34	177	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	178	VALUES
	179	(@option_group_id_udOpt, 'Groups' , 1, 'Groups', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
	180	(@option_group_id_udOpt, 'Contributions' , 2, 'CiviContribute', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
	181	(@option_group_id_udOpt, 'Memberships' , 3, 'CiviMember', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
	182	(@option_group_id_udOpt, 'Events' , 4, 'CiviEvent', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
	183	(@option_group_id_udOpt, 'My Contacts / Organizations', 5, 'Permissioned Orgs', NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
	184	(@option_group_id_udOpt, 'PledgeBank' , 6, 'PledgeBank', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
	185	(@option_group_id_udOpt, 'Pledges' , 7, 'CiviPledge', NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL);
	186
	187
	188	-- those affiliated to address_options group
	189
	190	SELECT @option_group_id_adOpt := max(id) from civicrm_option_group where name = 'address_options';
	191
	192	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_adOpt;
	193
10824d34	194	INSERT INTO
10824d34	195	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	196	VALUES
	197	(@option_group_id_adOpt, 'Street Address' , 1, 'street_address', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
	198	(@option_group_id_adOpt, 'Addt\'l Address 1' , 2, 'supplemental_address_1', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
	199	(@option_group_id_adOpt, 'Addt\'l Address 2' , 3, 'supplemental_address_2', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
	200	(@option_group_id_adOpt, 'City' , 4, 'city' , NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
	201	(@option_group_id_adOpt, 'Zip / Postal Code' , 5, 'postal_code' , NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
	202	(@option_group_id_adOpt, 'Postal Code Suffix', 6, 'postal_code_suffix', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
	203	(@option_group_id_adOpt, 'County' , 7, 'county' , NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL),
	204	(@option_group_id_adOpt, 'State / Province' , 8, 'state_province', NULL, 0, NULL, 8, NULL, 0, 0, 1, NULL),
	205	(@option_group_id_adOpt, 'Country' , 9, 'country' , NULL, 0, NULL, 9, NULL, 0, 0, 1, NULL),
	206	(@option_group_id_adOpt, 'Latitude' , 10, 'geo_code_1' , NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
	207	(@option_group_id_adOpt, 'Longitude' , 11, 'geo_code_2', NULL, 0, NULL, 11, NULL, 0, 0, 1, NULL);
	208
	209
	210	-- those affiliated to custom_search group
	211
	212	SELECT @option_group_id_csearch := max(id) from civicrm_option_group where name = 'custom_search';
	213
	214	DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_csearch;
	215
10824d34	216	INSERT INTO
10824d34	217	`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	218	VALUES
	219	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Sample' , 1, 'CRM_Contact_Form_Search_Custom_Sample' , NULL, 0, NULL, 1, 'Household Name and State', 0, 0, 1, NULL),
	220	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ContributionAggregate', 2, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', NULL, 0, NULL, 2, 'Contribution Aggregate', 0, 0, 1, NULL),
	221	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Basic' , 3, 'CRM_Contact_Form_Search_Custom_Basic' , NULL, 0, NULL, 3, 'Basic Search', 0, 0, 1, NULL),
	222	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Group' , 4, 'CRM_Contact_Form_Search_Custom_Group' , NULL, 0, NULL, 4, 'Include / Exclude Contacts in a Group / Tag', 0, 0, 1, NULL),
	223	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PostalMailing' , 5, 'CRM_Contact_Form_Search_Custom_PostalMailing', NULL, 0, NULL, 5, 'Postal Mailing', 0, 0, 1, NULL),
	224	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Proximity' , 6, 'CRM_Contact_Form_Search_Custom_Proximity', NULL, 0, NULL, 6, 'Proximity Search', 0, 0, 1, NULL),
	225	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_EventAggregate', 7, 'CRM_Contact_Form_Search_Custom_EventAggregate', NULL, 0, NULL, 7, 'Event Aggregate', 0, 0, 1, NULL),
	226	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ActivitySearch', 8, 'CRM_Contact_Form_Search_Custom_ActivitySearch', NULL, 0, NULL, 8, 'Activity Search', 0, 0, 1, NULL),
	227	(@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PriceSet', 9, 'CRM_Contact_Form_Search_Custom_PriceSet', NULL, 0, NULL, 9, 'Price Set Details for Event Participants', 0, 0, 1, NULL);
	228
	229	-- option groups and values for 'wysiwyg_editor' , 'from_email_address'.
10824d34	230	INSERT INTO
6a488035	231	`civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`)
10824d34	232	VALUES
6a488035 TO	233	('from_email_address', 'From Email Address', 0, 1),
	234	('wysiwyg_editor' , 'WYSIWYG Editor' , 0, 1);
	235	SELECT @option_we := max(id) from civicrm_option_group where name = 'wysiwyg_editor';
	236
10824d34	237	INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`,`name` , `grouping`, `filter`,`is_default`,`weight`, `description`,`is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	238	VALUES
	239	(@option_we, 'TinyMCE' , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
	240	(@option_we, 'FCKEditor' , 2, NULL, NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL);
	241
	242	-- Insert new option value address_name for option_group = 'address_options'
	243
	244	SELECT @option_act := civicrm_option_group.id,
10824d34	245	@max_val := max(ROUND(civicrm_option_value.value)),
10824d34	246	@max_wt := max(civicrm_option_value.weight)
6a488035 TO	247	FROM civicrm_option_value, civicrm_option_group
	248	WHERE civicrm_option_group.name = 'address_options'
	249	AND civicrm_option_value.option_group_id = civicrm_option_group.id
	250	GROUP BY civicrm_option_group.id;
	251
	252
10824d34	253	INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name` , `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`)
6a488035 TO	254	VALUES
	255	(@option_act, 'Address Name', (SELECT @max_val := @max_val+1), 'address_name', NULL, 0, NULL, (SELECT @max_wt := @max_wt+1), NULL, 0, 0, 1, NULL);
	256
	257	-- update filter for participant-status option values
	258
	259	SELECT @og_ps_id := id from civicrm_option_group where name = 'participant_status';
	260
	261	UPDATE civicrm_option_value SET filter=1 WHERE option_group_id=@og_ps_id AND label IN ('Registered', 'Attended');