| 1 | -- +--------------------------------------------------------------------+ |
| 2 | -- | CiviCRM version 4.6 | |
| 3 | -- +--------------------------------------------------------------------+ |
| 4 | -- | Copyright CiviCRM LLC (c) 2004-2015 | |
| 5 | -- +--------------------------------------------------------------------+ |
| 6 | -- | This file is a part of CiviCRM. | |
| 7 | -- | | |
| 8 | -- | CiviCRM is free software; you can copy, modify, and distribute it | |
| 9 | -- | under the terms of the GNU Affero General Public License | |
| 10 | -- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | |
| 11 | -- | | |
| 12 | -- | CiviCRM is distributed in the hope that it will be useful, but | |
| 13 | -- | WITHOUT ANY WARRANTY; without even the implied warranty of | |
| 14 | -- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | |
| 15 | -- | See the GNU Affero General Public License for more details. | |
| 16 | -- | | |
| 17 | -- | You should have received a copy of the GNU Affero General Public | |
| 18 | -- | License and the CiviCRM Licensing Exception along | |
| 19 | -- | with this program; if not, contact CiviCRM LLC | |
| 20 | -- | at info[AT]civicrm[DOT]org. If you have questions about the | |
| 21 | -- | GNU Affero General Public License or the licensing of CiviCRM, | |
| 22 | -- | see the CiviCRM license FAQ at http://civicrm.org/licensing | |
| 23 | -- +--------------------------------------------------------------------+ |
| 24 | |
| 25 | |
| 26 | -- /******************************************************* |
| 27 | -- * |
| 28 | -- * Sample Custom Data |
| 29 | -- * |
| 30 | -- *******************************************************/ |
| 31 | |
| 32 | -- /******************************************************* |
| 33 | -- * |
| 34 | -- * create custom group |
| 35 | -- * |
| 36 | -- *******************************************************/ |
| 37 | INSERT INTO `civicrm_custom_group` (`name`, `title`, `extends`, `style`, `collapse_display`, `help_pre`, `weight`, `is_active`, `table_name`, `is_multiple`) VALUES ( 'constituent_information', 'Constituent Information', 'Individual', 'Inline', 1, 'Please enter additional constituent information as data becomes available for this contact.', 1, 1,'civicrm_value_constituent_information_1', 0); |
| 38 | |
| 39 | |
| 40 | |
| 41 | -- /******************************************************* |
| 42 | -- * |
| 43 | -- * create option group for storing custom options for custom fields |
| 44 | -- * |
| 45 | -- *******************************************************/ |
| 46 | INSERT INTO `civicrm_option_group` (`name`, `title`, `is_reserved`, `is_active`) VALUES ('custom_most_important_issue', 'Most Important Issue', 1, 1), ( 'custom_marital_status', 'Marital Status', 1, 1); |
| 47 | |
| 48 | SELECT @option_most_id := max(id) from civicrm_option_group where name = 'custom_most_important_issue'; |
| 49 | SELECT @option_marital_id := max(id) from civicrm_option_group where name = 'custom_marital_status'; |
| 50 | |
| 51 | |
| 52 | |
| 53 | -- /******************************************************* |
| 54 | -- * |
| 55 | -- * create option values (custom options for custom fields) |
| 56 | -- * |
| 57 | -- *******************************************************/ |
| 58 | INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `weight`, `is_active`, `is_default`) VALUES (@option_most_id , 'Education', 'Edu', 1, 1,0), (@option_most_id , 'Environment', 'Env', 2, 1,0), (@option_most_id , 'Social Justice', 'SocJus', 3, 1,0),(@option_marital_id, 'Single', 'S', 1, 1,0),(@option_marital_id, 'Married', 'M', 2, 1,0), (@option_marital_id, 'Domestic Partner', 'D', 3, 1,0), (@option_marital_id, 'Widowed', 'W', 4, 1,0), (@option_marital_id, 'Other', 'O', 5, 1,0); |
| 59 | |
| 60 | |
| 61 | -- /******************************************************* |
| 62 | -- * |
| 63 | -- * create custom field |
| 64 | -- * |
| 65 | -- *******************************************************/ |
| 66 | INSERT INTO `civicrm_custom_field` (`custom_group_id`, `name`, `label`, `data_type`, `html_type`, `text_length`, `is_required`, `weight`, `help_post`, `is_active`, `is_view`, `is_searchable`, `options_per_line`, `column_name`, `option_group_id`, `start_date_years`, `end_date_years`, `date_format`, `time_format` ) VALUES (1, 'Most_Important_Issue', 'Most Important Issue', 'String', 'Radio', 255, 0, 1, '', 1, 0, 1, NULL, 'most_important_issue_1', @option_most_id, NULL, NULL, NULL, NULL), (1, 'Marital_Status', 'Marital Status', 'String', 'Select', 255, 0, 2, '', 1, 0, 1, NULL, 'marital_status_2', @option_marital_id, NULL, NULL, NULL, NULL), (1, 'Marriage_Date', 'Marriage Date', 'Date', 'Select Date', NULL, 0, 3, '', 1, 0, 1, NULL, 'marriage_date_3', NULL, 30, 0, 'mm/dd/yy', 0); |
| 67 | |
| 68 | |
| 69 | -- /******************************************************* |
| 70 | -- * |
| 71 | -- * create table to store custom values of a custom group |
| 72 | -- * |
| 73 | -- *******************************************************/ |
| 74 | DROP TABLE IF EXISTS `civicrm_value_constituent_information_1`; |
| 75 | CREATE TABLE `civicrm_value_constituent_information_1` (`id` int(10) unsigned NOT NULL auto_increment, `entity_id` int(10) unsigned NOT NULL, `most_important_issue_1` varchar(255) default NULL, `marital_status_2` varchar(255) default NULL, `marriage_date_3` datetime default NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_entity_id` (`entity_id`), INDEX `INDEX_most_important_issue_1` (`most_important_issue_1`), INDEX `INDEX_marital_status_2` (`marital_status_2`), INDEX `INDEX_marriage_date_3` (`marriage_date_3`), CONSTRAINT `FK_civicrm_value_constituent_information_1_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 76 | |
| 77 | |
| 78 | |
| 79 | -- Fall Fundraiser Dinner participants’ custom data |
| 80 | |
| 81 | INSERT INTO civicrm_option_group ( name, title, is_active, is_reserved ) VALUES ('soup_selection', 'Soup Selection', 1, 1); |
| 82 | SELECT @ogid := MAX(id) FROM civicrm_option_group; |
| 83 | |
| 84 | INSERT INTO civicrm_custom_group ( name, title, extends, extends_entity_column_id, extends_entity_column_value, style, is_active, table_name) VALUES ('Food_Preference', 'Food Preference', 'Participant', 2, '\ 11\ 1', 'Inline', 1, 'civicrm_value_food_preference_2'); |
| 85 | SELECT @cgid := MAX(id) FROM civicrm_custom_group; |
| 86 | |
| 87 | INSERT INTO civicrm_custom_field ( custom_group_id, label, name, data_type, html_type, is_active, text_length, note_columns, note_rows, column_name, option_group_id, is_searchable ) VALUES ( @cgid, 'Soup Selection', 'Soup_Selection', 'String', 'Radio', 1, 255, 60, 4, 'soup_selection_4', @ogid, 1); |
| 88 | |
| 89 | INSERT INTO civicrm_option_value ( option_group_id, label, value, name, weight ) VALUES ( @ogid, 'Bean Broth', 'bean', 'Bean_Broth', 1 ), ( @ogid, 'Chicken Combo', 'chicken', 'Chicken_Combo', 2), ( @ogid, 'Salmon Stew', 'salmon', 'Salmon_Stew', 3); |
| 90 | |
| 91 | DROP TABLE IF EXISTS `civicrm_value_food_preference_2`; |
| 92 | CREATE TABLE civicrm_value_food_preference_2 ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key', entity_id INT(10) UNSIGNED NOT NULL COMMENT 'Table that this extends', soup_selection_4 VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY unique_entity_id (entity_id), CONSTRAINT `FK_civicrm_value_food_preference_2_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_participant` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 93 | |
| 94 | -- Donors’ custom data |
| 95 | |
| 96 | INSERT INTO `civicrm_custom_group` (`name`, `title`, `extends`, `extends_entity_column_id`, `extends_entity_column_value`, `style`, `is_active`, `table_name`) VALUES ('Donor_Information', 'Donor Information', 'Contribution', NULL, NULL, 'Inline', 1, 'civicrm_value_donor_information_3'); |
| 97 | |
| 98 | SELECT @cgid_contribution := MAX(id) FROM civicrm_custom_group; |
| 99 | |
| 100 | INSERT INTO civicrm_option_group ( name, title, is_active ) VALUES ('how_long_have_you_been_a_donor', 'How long have you been a donor?', 1); |
| 101 | |
| 102 | SELECT @ogid_contribution := MAX(id) FROM civicrm_option_group; |
| 103 | |
| 104 | INSERT INTO `civicrm_custom_field` (`custom_group_id`, `label`, `name`, `data_type`, `html_type`, `is_active`, `text_length`,`note_columns`, `note_rows`, `column_name`, `option_group_id`) VALUES (@cgid_contribution, 'Known areas of interest', 'Known_areas_of_interest', 'Memo', 'TextArea', 1, 255, 60, 4, 'known_areas_of_interest_5', NULL),(@cgid_contribution, 'How long have you been a donor?', 'How_long_have_you_been_a_donor_', 'String', 'Radio', 1, 255, 60, 4, 'how_long_have_you_been_a_donor_6', @ogid_contribution); |
| 105 | |
| 106 | INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `weight`) VALUES (@ogid_contribution, 'Less than 1 year', '1', 'Less_than_1_year', 1), (@ogid_contribution, '1-3 years', '2', '1_3_years', 2),(@ogid_contribution, '4-6 years', '3', '4_6_years', 3),(@ogid_contribution, '7-9 years', '4', '7_9_years', 4),(@ogid_contribution, 'More than 9 years', '5', 'More_than_9_years', 5); |
| 107 | |
| 108 | DROP TABLE IF EXISTS `civicrm_value_donor_information_3`; |
| 109 | CREATE TABLE IF NOT EXISTS `civicrm_value_donor_information_3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key', `entity_id` int(10) unsigned NOT NULL COMMENT 'Table that this extends', `known_areas_of_interest_5` text COLLATE utf8_unicode_ci, `how_long_have_you_been_a_donor_6` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_entity_id` (`entity_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |