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

   1 -- CRM-6134
   2
   3 {include file='../CRM/Upgrade/3.4.3.msg_template/civicrm_msg_template.tpl'}
   4
   5 --added on behalf of organization profile
   6
   7 INSERT INTO civicrm_uf_group
   8     ( name, group_type, {localize field='title'}title{/localize}, is_reserved )
   9
  10 VALUES
  11     ( 'on_behalf_organization', 'Contact,Organization,Contribution,Membership',  {localize}'{ts escape="sql"}On Behalf Of Organization{/ts}'{/localize}, 1 );
  12
  13 SELECT @uf_group_id_onBehalfOrganization := max(id) from civicrm_uf_group where name = 'on_behalf_organization';
  14
  15 INSERT INTO civicrm_uf_join
  16    ( is_active, module, entity_table, entity_id, weight, uf_group_id )
  17
  18 VALUES
  19    ( 1, 'Profile', NULL, NULL, 7, @uf_group_id_onBehalfOrganization );
  20
  21 SELECT @maxId := id FROM civicrm_location_type WHERE name = 'Main';
  22
  23 INSERT INTO civicrm_uf_field
  24    ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, {localize field='label'}label{/localize}, field_type, {localize field='help_post'}help_post{/localize}, phone_type_id )
  25
  26 VALUES
  27    ( @uf_group_id_onBehalfOrganization,   'organization_name',  1, 0, 1, 'User and User Admin Only',  0, 0, NULL,
  28             {localize}'Organization Name'{/localize}, 'Organization', {localize}NULL{/localize},  NULL ),
  29    ( @uf_group_id_onBehalfOrganization,   'phone',              1, 0, 2, 'User and User Admin Only',  0, 0, @maxId,
  30             {localize}'Phone (Main) '{/localize},     'Contact',      {localize}NULL{/localize},  1 ),
  31    ( @uf_group_id_onBehalfOrganization,   'email',              1, 0, 3, 'User and User Admin Only',  0, 0, @maxId,
  32             {localize}'Email (Main) '{/localize},     'Contact',      {localize}NULL{/localize},  NULL ),
  33    ( @uf_group_id_onBehalfOrganization,   'street_address',     1, 0, 4, 'User and User Admin Only',  0, 0, @maxId,
  34             {localize}'Street Address'{/localize},    'Contact',      {localize}NULL{/localize},  NULL ),
  35    ( @uf_group_id_onBehalfOrganization,   'city',               1, 0, 5, 'User and User Admin Only',  0, 0, @maxId,
  36             {localize}'City'{/localize},              'Contact',      {localize}NULL{/localize},  NULL ),
  37    ( @uf_group_id_onBehalfOrganization,   'postal_code',        1, 0, 6, 'User and User Admin Only',  0, 0, @maxId,
  38             {localize}'Postal Code'{/localize},       'Contact',      {localize}NULL{/localize},  NULL ),
  39    ( @uf_group_id_onBehalfOrganization,   'country',            1, 0, 7, 'User and User Admin Only',  0, 0, @maxId,
  40             {localize}'Country'{/localize},           'Contact',      {localize}NULL{/localize},  NULL ),
  41    ( @uf_group_id_onBehalfOrganization,   'state_province',     1, 0, 8, 'User and User Admin Only',  0, 0, @maxId,
  42             {localize}'State/Province'{/localize},  'Contact',      {localize}NULL{/localize},  NULL );
  43
  44 -- CRM-8150
  45 CREATE TABLE IF NOT EXISTS `civicrm_action_mapping` (
  46   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  47   `entity` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity for which the reminder is created',
  48   `entity_value` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value',
  49   `entity_value_label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value label',
  50   `entity_status` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status',
  51   `entity_status_label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status label',
  52   `entity_date_start` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date',
  53   `entity_date_end` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date',
  54   `entity_recipient` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity recipient',
  55   PRIMARY KEY (`id`)
  56 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  57
  58
  59 INSERT INTO civicrm_action_mapping
  60         (entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient)
  61 VALUES
  62   ('civicrm_activity', 'activity_type', 'Type', 'activity_status', 'Status', 'activity_date_time', NULL, 'activity_contacts');
  63
  64 CREATE TABLE IF NOT EXISTS `civicrm_action_schedule` (
  65   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  66   `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name of the action(reminder)',
  67   `title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the action(reminder)',
  68   `recipient` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Recipient',
  69   `entity_value` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity value',
  70   `entity_status` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity status',
  71   `start_action_offset` int(10) unsigned DEFAULT NULL COMMENT 'Reminder Interval.',
  72   `start_action_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units for reminder.',
  73   `start_action_condition` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Reminder Action',
  74   `start_action_date` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity date',
  75   `is_repeat` tinyint(4) DEFAULT '0',
  76   `repetition_frequency_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units for repetition of reminder.',
  77   `repetition_frequency_interval` int(10) unsigned DEFAULT NULL COMMENT 'Time interval for repeating the reminder.',
  78   `end_frequency_unit` enum('hour','day','week','month','year') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Time units till repetition of reminder.',
  79   `end_frequency_interval` int(10) unsigned DEFAULT NULL COMMENT 'Time interval till repeating the reminder.',
  80   `end_action` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Reminder Action till repeating the reminder.',
  81   `end_date` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Entity end date',
  82   `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this option active?',
  83   `recipient_manual` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Contact IDs to which reminder should be sent.',
  84   `body_text` longtext COLLATE utf8_unicode_ci COMMENT 'Body of the mailing in text format.',
  85   `body_html` longtext COLLATE utf8_unicode_ci COMMENT 'Body of the mailing in html format.',
  86   `subject` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Subject of mailing',
  87   `record_activity` tinyint(4) DEFAULT NULL COMMENT 'Record Activity for this reminder?',
  88   `mapping_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to mapping which is being used by this reminder',
  89   `group_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Group',
  90   `msg_template_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to the message template.',
  91   PRIMARY KEY (`id`),
  92   CONSTRAINT `FK_civicrm_action_schedule_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES civicrm_action_mapping(id) ON DELETE SET NULL,
  93   CONSTRAINT `FK_civicrm_action_schedule_group_id` FOREIGN KEY (`group_id`) REFERENCES civicrm_group(id) ON DELETE SET NULL,
  94   CONSTRAINT `FK_civicrm_action_schedule_msg_template_id` FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template` (`id`) ON DELETE SET NULL
  95 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  96
  97
  98 INSERT INTO civicrm_option_group
  99       (name, {localize field='description'}description{/localize}, is_reserved, is_active)
 100 VALUES
 101       ('activity_contacts', {localize}'{ts escape="sql"}Activity Contacts{/ts}'{/localize}, 0, 1);
 102
 103 SELECT @option_group_id_aco := max(id) from civicrm_option_group where name = 'activity_contacts';
 104 SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type';
 105 SELECT @act_value           := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 106 SELECT @act_weight          := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 107
 108 -- CRM-8209
 109 SELECT @option_group_id_adv_search_opts := max(id) from civicrm_option_group where name = 'advanced_search_options';
 110
 111 INSERT INTO civicrm_option_value
 112    (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)
 113 VALUES
 114    (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Assignees{/ts}'{/localize}, 1, 'Activity Assignees', NULL, 0, NULL, 1, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 115    (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Source{/ts}'{/localize}, 2, 'Activity Source', NULL, 0, NULL, 2, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 116    (@option_group_id_aco, {localize}'{ts escape="sql"}Activity Targets{/ts}'{/localize}, 3, 'Activity Targets', NULL, 0, NULL, 3, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 117    (@option_group_id_adv_search_opts, {localize}'{ts escape="sql"}Mailing{/ts}'{/localize}, '19',   'CiviMail', NULL, 0, NULL, 21, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL),
 118    (@option_group_id_act, {localize}'{ts escape="sql"}Reminder Sent{/ts}'{/localize}, @act_value+1, 'Reminder Sent', NULL, 0, NULL, @act_weight+1, {localize}NULL{/localize}, 0, 0, 1, NULL, NULL);
 119
 120 SELECT @domainID := min(id) FROM civicrm_domain;
 121 SELECT @configureID := max(id) FROM civicrm_navigation WHERE name = 'Configure';
 122 SELECT @nav_c_wt := max(weight) from civicrm_navigation WHERE parent_id = @configureID;
 123
 124 INSERT INTO civicrm_navigation
 125     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 126 VALUES
 127     ( @domainID, 'civicrm/admin/scheduleReminders&reset=1', '{ts escape="sql" skip="true"}Schedule Reminders{/ts}', 'Schedule Reminders', 'administer CiviCRM', '',  @configureID, '1', NULL, @nav_c_wt );
 128
 129 -- CRM-8148, rename uf field 'activity_status' to 'activity_status_id'
 130 UPDATE civicrm_uf_field SET field_name = 'activity_type_id' WHERE field_name= 'activity_type';
 131
 132 -- CRM-7988 allow negative start and end date offsets for custom fields
 133 ALTER TABLE civicrm_custom_field MODIFY start_date_years INT(10);
 134 ALTER TABLE civicrm_custom_field MODIFY end_date_years INT(10);
 135
 136 -- CRM-8146 Supply names for existing dupe matching rules (now that name is required)
 137 UPDATE civicrm_dedupe_rule_group
 138 SET name = CONCAT(contact_type, '-', level, '-', id)
 139 WHERE name IS NULL;