
-- SQL TO CREATE A REDCAP DEMO PROJECT --
set @project_title = 'Project Dashboards, Smart Functions, Smart Tables, & Smart Charts';


-- Obtain default values --
set @institution = (select value from redcap_config where field_name = 'institution' limit 1);
set @site_org_type = (select value from redcap_config where field_name = 'site_org_type' limit 1);
set @grant_cite = (select value from redcap_config where field_name = 'grant_cite' limit 1);
set @project_contact_name = (select value from redcap_config where field_name = 'project_contact_name' limit 1);
set @project_contact_email = (select value from redcap_config where field_name = 'project_contact_email' limit 1);
set @headerlogo = (select value from redcap_config where field_name = 'headerlogo' limit 1);
set @auth_meth = (select value from redcap_config where field_name = 'auth_meth_global' limit 1);
-- Create project --
INSERT INTO `redcap_projects`
(project_name, app_title, status, count_project, auth_meth, creation_time, production_time, institution, site_org_type, grant_cite, project_contact_name, project_contact_email, headerlogo, display_project_logo_institution, auto_inc_set) VALUES
(concat('redcap_demo_',LEFT(sha1(rand()),6)), @project_title, 1, 0, @auth_meth, now(), now(), @institution, @site_org_type, @grant_cite, @project_contact_name, @project_contact_email, @headerlogo, 0, 1);
set @project_id = LAST_INSERT_ID();
-- Create single arm --
INSERT INTO redcap_events_arms (project_id, arm_num, arm_name) VALUES (@project_id, 1, 'Arm 1');
set @arm_id = LAST_INSERT_ID();
-- Create single event --
INSERT INTO redcap_events_metadata (arm_id, day_offset, offset_min, offset_max, descrip) VALUES (@arm_id, 0, 0, 0, 'Event 1');
set @event_id = LAST_INSERT_ID();
-- Insert into redcap_metadata --
INSERT INTO `redcap_metadata` (`project_id`, `field_name`, `field_phi`, `form_name`, `form_menu_description`, `field_order`, `field_units`, `element_preceding_header`, `element_type`, `element_label`, `element_enum`, `element_note`, `element_validation_type`, `element_validation_min`, `element_validation_max`, `element_validation_checktype`, `branching_logic`, `field_req`, `edoc_id`, `edoc_display_img`, `custom_alignment`, `stop_actions`, `question_num`, `grid_name`, `grid_rank`, `misc`, `video_url`, `video_display_inline`) VALUES
(@project_id, 'study_id', NULL, 'demographics', 'Demographics', 1, NULL, NULL, 'text', 'Study ID', NULL, NULL, NULL, NULL, NULL, 'soft_typed', NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'age', NULL, 'demographics', NULL, 2, NULL, NULL, 'text', 'Age (years)', NULL, NULL, 'int', NULL, NULL, 'soft_typed', NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'ethnicity', NULL, 'demographics', NULL, 3, NULL, NULL, 'radio', 'Ethnicity', 'H, Hispanic or Latino\\nN, NOT Hispanic or Latino\\nU, Unknown / Not Reported', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'LH', NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'race', NULL, 'demographics', NULL, 4, NULL, NULL, 'select', 'Race', 'AI, American Indian/Alaska Native\\nA, Asian\\nP, Native Hawaiian or Other Pacific Islander\\nB, Black or African American\\nW, White\\nM, More Than One Race\\nU, Unknown / Not Reported', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'gender', NULL, 'demographics', NULL, 5, NULL, NULL, 'radio', 'Gender', 'F, Female\\nM, Male\\nO, Other\\nN, Prefer not to say', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'height', NULL, 'demographics', NULL, 6, NULL, NULL, 'text', 'Height (cm)', NULL, NULL, 'float', '130', '215', 'soft_typed', NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'weight', NULL, 'demographics', NULL, 7, NULL, NULL, 'text', 'Weight (kilograms)', NULL, NULL, 'int', '35', '200', 'soft_typed', NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0),
(@project_id, 'demographics_complete', NULL, 'demographics', NULL, 8, NULL, 'Form Status', 'select', 'Complete?', '0, Incomplete \\n 1, Unverified \\n 2, Complete', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0);
-- Insert DAGS
INSERT INTO `redcap_data_access_groups` (`project_id`, `group_name`) VALUES
(@project_id, 'Duke'),
(@project_id, 'Harvard');
INSERT INTO `redcap_data_access_groups` (`project_id`, `group_name`) VALUES
(@project_id, 'Vanderbilt');
set @dag_id = LAST_INSERT_ID();
-- Insert into redcap_data
INSERT INTO `redcap_data` (`project_id`, `event_id`, `record`, `field_name`, `value`, `instance`) VALUES
(@project_id, @event_id, '101', 'age', '30', NULL),
(@project_id, @event_id, '101', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '101', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '101', 'gender', 'F', NULL),
(@project_id, @event_id, '101', 'height', '198', NULL),
(@project_id, @event_id, '101', 'race', 'A', NULL),
(@project_id, @event_id, '101', 'study_id', '101', NULL),
(@project_id, @event_id, '101', 'weight', '75', NULL),
(@project_id, @event_id, '102', 'age', '27', NULL),
(@project_id, @event_id, '102', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '102', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '102', 'gender', 'M', NULL),
(@project_id, @event_id, '102', 'height', '168', NULL),
(@project_id, @event_id, '102', 'race', 'B', NULL),
(@project_id, @event_id, '102', 'study_id', '102', NULL),
(@project_id, @event_id, '102', 'weight', '182', NULL),
(@project_id, @event_id, '103', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '103', 'age', '102', NULL),
(@project_id, @event_id, '103', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '103', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '103', 'gender', 'F', NULL),
(@project_id, @event_id, '103', 'height', '142', NULL),
(@project_id, @event_id, '103', 'race', 'A', NULL),
(@project_id, @event_id, '103', 'study_id', '103', NULL),
(@project_id, @event_id, '103', 'weight', '169', NULL),
(@project_id, @event_id, '104', 'age', '87', NULL),
(@project_id, @event_id, '104', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '104', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '104', 'gender', 'M', NULL),
(@project_id, @event_id, '104', 'height', '202', NULL),
(@project_id, @event_id, '104', 'race', 'AI', NULL),
(@project_id, @event_id, '104', 'study_id', '104', NULL),
(@project_id, @event_id, '104', 'weight', '91', NULL),
(@project_id, @event_id, '105', 'age', '25', NULL),
(@project_id, @event_id, '105', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '105', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '105', 'gender', 'M', NULL),
(@project_id, @event_id, '105', 'height', '211', NULL),
(@project_id, @event_id, '105', 'race', 'A', NULL),
(@project_id, @event_id, '105', 'study_id', '105', NULL),
(@project_id, @event_id, '105', 'weight', '126', NULL),
(@project_id, @event_id, '106', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '106', 'age', '6', NULL),
(@project_id, @event_id, '106', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '106', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '106', 'gender', 'M', NULL),
(@project_id, @event_id, '106', 'height', '196', NULL),
(@project_id, @event_id, '106', 'race', 'W', NULL),
(@project_id, @event_id, '106', 'study_id', '106', NULL),
(@project_id, @event_id, '106', 'weight', '193', NULL),
(@project_id, @event_id, '107', 'age', '93', NULL),
(@project_id, @event_id, '107', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '107', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '107', 'gender', 'M', NULL),
(@project_id, @event_id, '107', 'height', '148', NULL),
(@project_id, @event_id, '107', 'race', 'A', NULL),
(@project_id, @event_id, '107', 'study_id', '107', NULL),
(@project_id, @event_id, '107', 'weight', '88', NULL),
(@project_id, @event_id, '108', 'age', '62', NULL),
(@project_id, @event_id, '108', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '108', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '108', 'gender', 'F', NULL),
(@project_id, @event_id, '108', 'height', '133', NULL),
(@project_id, @event_id, '108', 'race', 'P', NULL),
(@project_id, @event_id, '108', 'study_id', '108', NULL),
(@project_id, @event_id, '108', 'weight', '103', NULL),
(@project_id, @event_id, '109', 'age', '87', NULL),
(@project_id, @event_id, '109', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '109', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '109', 'gender', 'F', NULL),
(@project_id, @event_id, '109', 'height', '192', NULL),
(@project_id, @event_id, '109', 'race', 'B', NULL),
(@project_id, @event_id, '109', 'study_id', '109', NULL),
(@project_id, @event_id, '109', 'weight', '49', NULL),
(@project_id, @event_id, '110', 'age', '5', NULL),
(@project_id, @event_id, '110', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '110', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '110', 'gender', 'M', NULL),
(@project_id, @event_id, '110', 'height', '202', NULL),
(@project_id, @event_id, '110', 'race', 'U', NULL),
(@project_id, @event_id, '110', 'study_id', '110', NULL),
(@project_id, @event_id, '110', 'weight', '179', NULL),
(@project_id, @event_id, '111', 'age', '53', NULL),
(@project_id, @event_id, '111', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '111', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '111', 'gender', 'M', NULL),
(@project_id, @event_id, '111', 'height', '182', NULL),
(@project_id, @event_id, '111', 'race', 'B', NULL),
(@project_id, @event_id, '111', 'study_id', '111', NULL),
(@project_id, @event_id, '111', 'weight', '98', NULL),
(@project_id, @event_id, '112', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '112', 'age', '82', NULL),
(@project_id, @event_id, '112', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '112', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '112', 'gender', 'M', NULL),
(@project_id, @event_id, '112', 'height', '202', NULL),
(@project_id, @event_id, '112', 'race', 'AI', NULL),
(@project_id, @event_id, '112', 'study_id', '112', NULL),
(@project_id, @event_id, '112', 'weight', '47', NULL),
(@project_id, @event_id, '113', 'age', '21', NULL),
(@project_id, @event_id, '113', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '113', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '113', 'gender', 'M', NULL),
(@project_id, @event_id, '113', 'height', '194', NULL),
(@project_id, @event_id, '113', 'race', 'U', NULL),
(@project_id, @event_id, '113', 'study_id', '113', NULL),
(@project_id, @event_id, '113', 'weight', '118', NULL),
(@project_id, @event_id, '114', 'age', '22', NULL),
(@project_id, @event_id, '114', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '114', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '114', 'gender', 'F', NULL),
(@project_id, @event_id, '114', 'height', '160', NULL),
(@project_id, @event_id, '114', 'race', 'B', NULL),
(@project_id, @event_id, '114', 'study_id', '114', NULL),
(@project_id, @event_id, '114', 'weight', '93', NULL),
(@project_id, @event_id, '115', 'age', '48', NULL),
(@project_id, @event_id, '115', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '115', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '115', 'gender', 'F', NULL),
(@project_id, @event_id, '115', 'height', '202', NULL),
(@project_id, @event_id, '115', 'race', 'A', NULL),
(@project_id, @event_id, '115', 'study_id', '115', NULL),
(@project_id, @event_id, '115', 'weight', '117', NULL),
(@project_id, @event_id, '116', 'age', '35', NULL),
(@project_id, @event_id, '116', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '116', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '116', 'gender', 'F', NULL),
(@project_id, @event_id, '116', 'height', '155', NULL),
(@project_id, @event_id, '116', 'race', 'P', NULL),
(@project_id, @event_id, '116', 'study_id', '116', NULL),
(@project_id, @event_id, '116', 'weight', '124', NULL),
(@project_id, @event_id, '117', 'age', '72', NULL),
(@project_id, @event_id, '117', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '117', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '117', 'gender', 'M', NULL),
(@project_id, @event_id, '117', 'height', '179', NULL),
(@project_id, @event_id, '117', 'race', 'P', NULL),
(@project_id, @event_id, '117', 'study_id', '117', NULL),
(@project_id, @event_id, '117', 'weight', '69', NULL),
(@project_id, @event_id, '118', 'age', '46', NULL),
(@project_id, @event_id, '118', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '118', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '118', 'gender', 'M', NULL),
(@project_id, @event_id, '118', 'height', '184', NULL),
(@project_id, @event_id, '118', 'race', 'M', NULL),
(@project_id, @event_id, '118', 'study_id', '118', NULL),
(@project_id, @event_id, '118', 'weight', '81', NULL),
(@project_id, @event_id, '119', 'age', '28', NULL),
(@project_id, @event_id, '119', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '119', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '119', 'gender', 'M', NULL),
(@project_id, @event_id, '119', 'height', '173', NULL),
(@project_id, @event_id, '119', 'race', 'U', NULL),
(@project_id, @event_id, '119', 'study_id', '119', NULL),
(@project_id, @event_id, '119', 'weight', '136', NULL),
(@project_id, @event_id, '120', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '120', 'age', '21', NULL),
(@project_id, @event_id, '120', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '120', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '120', 'gender', 'M', NULL),
(@project_id, @event_id, '120', 'height', '187', NULL),
(@project_id, @event_id, '120', 'race', 'W', NULL),
(@project_id, @event_id, '120', 'study_id', '120', NULL),
(@project_id, @event_id, '120', 'weight', '190', NULL),
(@project_id, @event_id, '121', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '121', 'age', '81', NULL),
(@project_id, @event_id, '121', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '121', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '121', 'gender', 'M', NULL),
(@project_id, @event_id, '121', 'height', '173', NULL),
(@project_id, @event_id, '121', 'race', 'B', NULL),
(@project_id, @event_id, '121', 'study_id', '121', NULL),
(@project_id, @event_id, '121', 'weight', '58', NULL),
(@project_id, @event_id, '122', '__GROUPID__', @dag_id, NULL),
(@project_id, @event_id, '122', 'age', '85', NULL),
(@project_id, @event_id, '122', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '122', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '122', 'gender', 'M', NULL),
(@project_id, @event_id, '122', 'height', '173', NULL),
(@project_id, @event_id, '122', 'race', 'W', NULL),
(@project_id, @event_id, '122', 'study_id', '122', NULL),
(@project_id, @event_id, '122', 'weight', '105', NULL),
(@project_id, @event_id, '123', 'age', '49', NULL),
(@project_id, @event_id, '123', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '123', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '123', 'gender', 'M', NULL),
(@project_id, @event_id, '123', 'height', '212', NULL),
(@project_id, @event_id, '123', 'race', 'B', NULL),
(@project_id, @event_id, '123', 'study_id', '123', NULL),
(@project_id, @event_id, '123', 'weight', '115', NULL),
(@project_id, @event_id, '124', 'age', '27', NULL),
(@project_id, @event_id, '124', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '124', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '124', 'gender', 'F', NULL),
(@project_id, @event_id, '124', 'height', '136', NULL),
(@project_id, @event_id, '124', 'race', 'W', NULL),
(@project_id, @event_id, '124', 'study_id', '124', NULL),
(@project_id, @event_id, '124', 'weight', '105', NULL),
(@project_id, @event_id, '125', 'age', '11', NULL),
(@project_id, @event_id, '125', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '125', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '125', 'gender', 'F', NULL),
(@project_id, @event_id, '125', 'height', '212', NULL),
(@project_id, @event_id, '125', 'race', 'U', NULL),
(@project_id, @event_id, '125', 'study_id', '125', NULL),
(@project_id, @event_id, '125', 'weight', '116', NULL),
(@project_id, @event_id, '126', 'age', '31', NULL),
(@project_id, @event_id, '126', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '126', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '126', 'gender', 'M', NULL),
(@project_id, @event_id, '126', 'height', '160', NULL),
(@project_id, @event_id, '126', 'race', 'W', NULL),
(@project_id, @event_id, '126', 'study_id', '126', NULL),
(@project_id, @event_id, '126', 'weight', '49', NULL),
(@project_id, @event_id, '127', 'age', '96', NULL),
(@project_id, @event_id, '127', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '127', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '127', 'gender', 'F', NULL),
(@project_id, @event_id, '127', 'height', '150', NULL),
(@project_id, @event_id, '127', 'race', 'B', NULL),
(@project_id, @event_id, '127', 'study_id', '127', NULL),
(@project_id, @event_id, '127', 'weight', '77', NULL),
(@project_id, @event_id, '128', 'age', '8', NULL),
(@project_id, @event_id, '128', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '128', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '128', 'gender', 'M', NULL),
(@project_id, @event_id, '128', 'height', '182', NULL),
(@project_id, @event_id, '128', 'race', 'M', NULL),
(@project_id, @event_id, '128', 'study_id', '128', NULL),
(@project_id, @event_id, '128', 'weight', '125', NULL),
(@project_id, @event_id, '129', 'age', '55', NULL),
(@project_id, @event_id, '129', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '129', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '129', 'gender', 'M', NULL),
(@project_id, @event_id, '129', 'height', '171', NULL),
(@project_id, @event_id, '129', 'race', 'U', NULL),
(@project_id, @event_id, '129', 'study_id', '129', NULL),
(@project_id, @event_id, '129', 'weight', '194', NULL),
(@project_id, @event_id, '130', 'age', '16', NULL),
(@project_id, @event_id, '130', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '130', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '130', 'gender', 'M', NULL),
(@project_id, @event_id, '130', 'height', '160', NULL),
(@project_id, @event_id, '130', 'race', 'M', NULL),
(@project_id, @event_id, '130', 'study_id', '130', NULL),
(@project_id, @event_id, '130', 'weight', '197', NULL),
(@project_id, @event_id, '131', 'age', '91', NULL),
(@project_id, @event_id, '131', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '131', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '131', 'gender', 'M', NULL),
(@project_id, @event_id, '131', 'height', '165', NULL),
(@project_id, @event_id, '131', 'race', 'P', NULL),
(@project_id, @event_id, '131', 'study_id', '131', NULL),
(@project_id, @event_id, '131', 'weight', '82', NULL),
(@project_id, @event_id, '132', 'age', '87', NULL),
(@project_id, @event_id, '132', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '132', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '132', 'gender', 'M', NULL),
(@project_id, @event_id, '132', 'height', '214', NULL),
(@project_id, @event_id, '132', 'race', 'W', NULL),
(@project_id, @event_id, '132', 'study_id', '132', NULL),
(@project_id, @event_id, '132', 'weight', '76', NULL),
(@project_id, @event_id, '133', 'age', '45', NULL),
(@project_id, @event_id, '133', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '133', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '133', 'gender', 'M', NULL),
(@project_id, @event_id, '133', 'height', '194', NULL),
(@project_id, @event_id, '133', 'race', 'U', NULL),
(@project_id, @event_id, '133', 'study_id', '133', NULL),
(@project_id, @event_id, '133', 'weight', '121', NULL),
(@project_id, @event_id, '134', 'age', '82', NULL),
(@project_id, @event_id, '134', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '134', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '134', 'gender', 'M', NULL),
(@project_id, @event_id, '134', 'height', '141', NULL),
(@project_id, @event_id, '134', 'race', 'W', NULL),
(@project_id, @event_id, '134', 'study_id', '134', NULL),
(@project_id, @event_id, '134', 'weight', '110', NULL),
(@project_id, @event_id, '135', 'age', '68', NULL),
(@project_id, @event_id, '135', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '135', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '135', 'gender', 'M', NULL),
(@project_id, @event_id, '135', 'height', '210', NULL),
(@project_id, @event_id, '135', 'race', 'AI', NULL),
(@project_id, @event_id, '135', 'study_id', '135', NULL),
(@project_id, @event_id, '135', 'weight', '80', NULL),
(@project_id, @event_id, '136', 'age', '11', NULL),
(@project_id, @event_id, '136', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '136', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '136', 'gender', 'F', NULL),
(@project_id, @event_id, '136', 'height', '196', NULL),
(@project_id, @event_id, '136', 'race', 'B', NULL),
(@project_id, @event_id, '136', 'study_id', '136', NULL),
(@project_id, @event_id, '136', 'weight', '108', NULL),
(@project_id, @event_id, '137', 'age', '90', NULL),
(@project_id, @event_id, '137', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '137', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '137', 'gender', 'M', NULL),
(@project_id, @event_id, '137', 'height', '157', NULL),
(@project_id, @event_id, '137', 'race', 'A', NULL),
(@project_id, @event_id, '137', 'study_id', '137', NULL),
(@project_id, @event_id, '137', 'weight', '70', NULL),
(@project_id, @event_id, '138', 'age', '28', NULL),
(@project_id, @event_id, '138', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '138', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '138', 'gender', 'M', NULL),
(@project_id, @event_id, '138', 'height', '197', NULL),
(@project_id, @event_id, '138', 'race', 'A', NULL),
(@project_id, @event_id, '138', 'study_id', '138', NULL),
(@project_id, @event_id, '138', 'weight', '84', NULL),
(@project_id, @event_id, '139', 'age', '35', NULL),
(@project_id, @event_id, '139', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '139', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '139', 'gender', 'M', NULL),
(@project_id, @event_id, '139', 'height', '174', NULL),
(@project_id, @event_id, '139', 'race', 'W', NULL),
(@project_id, @event_id, '139', 'study_id', '139', NULL),
(@project_id, @event_id, '139', 'weight', '196', NULL),
(@project_id, @event_id, '140', 'age', '10', NULL),
(@project_id, @event_id, '140', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '140', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '140', 'gender', 'M', NULL),
(@project_id, @event_id, '140', 'height', '134', NULL),
(@project_id, @event_id, '140', 'race', 'U', NULL),
(@project_id, @event_id, '140', 'study_id', '140', NULL),
(@project_id, @event_id, '140', 'weight', '142', NULL),
(@project_id, @event_id, '141', 'age', '97', NULL),
(@project_id, @event_id, '141', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '141', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '141', 'gender', 'M', NULL),
(@project_id, @event_id, '141', 'height', '201', NULL),
(@project_id, @event_id, '141', 'race', 'U', NULL),
(@project_id, @event_id, '141', 'study_id', '141', NULL),
(@project_id, @event_id, '141', 'weight', '134', NULL),
(@project_id, @event_id, '142', 'age', '4', NULL),
(@project_id, @event_id, '142', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '142', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '142', 'gender', 'M', NULL),
(@project_id, @event_id, '142', 'height', '205', NULL),
(@project_id, @event_id, '142', 'race', 'M', NULL),
(@project_id, @event_id, '142', 'study_id', '142', NULL),
(@project_id, @event_id, '142', 'weight', '175', NULL),
(@project_id, @event_id, '143', 'age', '94', NULL),
(@project_id, @event_id, '143', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '143', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '143', 'gender', 'M', NULL),
(@project_id, @event_id, '143', 'height', '137', NULL),
(@project_id, @event_id, '143', 'race', 'U', NULL),
(@project_id, @event_id, '143', 'study_id', '143', NULL),
(@project_id, @event_id, '143', 'weight', '136', NULL),
(@project_id, @event_id, '144', 'age', '36', NULL),
(@project_id, @event_id, '144', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '144', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '144', 'gender', 'F', NULL),
(@project_id, @event_id, '144', 'height', '179', NULL),
(@project_id, @event_id, '144', 'race', 'A', NULL),
(@project_id, @event_id, '144', 'study_id', '144', NULL),
(@project_id, @event_id, '144', 'weight', '164', NULL),
(@project_id, @event_id, '145', 'age', '45', NULL),
(@project_id, @event_id, '145', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '145', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '145', 'gender', 'M', NULL),
(@project_id, @event_id, '145', 'height', '181', NULL),
(@project_id, @event_id, '145', 'race', 'M', NULL),
(@project_id, @event_id, '145', 'study_id', '145', NULL),
(@project_id, @event_id, '145', 'weight', '134', NULL),
(@project_id, @event_id, '146', 'age', '43', NULL),
(@project_id, @event_id, '146', 'demographics_complete', '1', NULL),
(@project_id, @event_id, '146', 'ethnicity', 'N', NULL),
(@project_id, @event_id, '146', 'gender', 'F', NULL),
(@project_id, @event_id, '146', 'height', '163', NULL),
(@project_id, @event_id, '146', 'race', 'W', NULL),
(@project_id, @event_id, '146', 'study_id', '146', NULL),
(@project_id, @event_id, '146', 'weight', '64', NULL),
(@project_id, @event_id, '147', 'age', '29', NULL),
(@project_id, @event_id, '147', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '147', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '147', 'gender', 'M', NULL),
(@project_id, @event_id, '147', 'height', '142', NULL),
(@project_id, @event_id, '147', 'race', 'A', NULL),
(@project_id, @event_id, '147', 'study_id', '147', NULL),
(@project_id, @event_id, '147', 'weight', '83', NULL),
(@project_id, @event_id, '148', 'age', '6', NULL),
(@project_id, @event_id, '148', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '148', 'ethnicity', 'H', NULL),
(@project_id, @event_id, '148', 'gender', 'M', NULL),
(@project_id, @event_id, '148', 'height', '201', NULL),
(@project_id, @event_id, '148', 'race', 'AI', NULL),
(@project_id, @event_id, '148', 'study_id', '148', NULL),
(@project_id, @event_id, '148', 'weight', '70', NULL),
(@project_id, @event_id, '149', 'age', '18', NULL),
(@project_id, @event_id, '149', 'demographics_complete', '0', NULL),
(@project_id, @event_id, '149', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '149', 'gender', 'F', NULL),
(@project_id, @event_id, '149', 'height', '195', NULL),
(@project_id, @event_id, '149', 'race', 'M', NULL),
(@project_id, @event_id, '149', 'study_id', '149', NULL),
(@project_id, @event_id, '149', 'weight', '105', NULL),
(@project_id, @event_id, '150', 'age', '39', NULL),
(@project_id, @event_id, '150', 'demographics_complete', '2', NULL),
(@project_id, @event_id, '150', 'ethnicity', 'U', NULL),
(@project_id, @event_id, '150', 'gender', 'M', NULL),
(@project_id, @event_id, '150', 'height', '136', NULL),
(@project_id, @event_id, '150', 'race', 'AI', NULL),
(@project_id, @event_id, '150', 'study_id', '150', NULL),
(@project_id, @event_id, '150', 'weight', '133', NULL);
-- reports
set @unique_report_name = (select LEFT(UPPER(CONCAT('R-', CAST(FLOOR(10*RAND()) as CHAR), CAST(FLOOR(10*RAND()) as CHAR), CAST(FLOOR(10*RAND()) as CHAR), SHA1(RAND()))), 12) limit 1);
INSERT INTO `redcap_reports` (`project_id`, `title`, `unique_report_name`, `report_order`, `user_access`, `user_edit_access`, `description`, `combine_checkbox_values`, `output_dags`, `output_survey_fields`, `output_missing_data_codes`, `remove_line_breaks_in_values`, `orderby_field1`, `orderby_sort1`, `orderby_field2`, `orderby_sort2`, `orderby_field3`, `orderby_sort3`, `advanced_logic`, `filter_type`, `dynamic_filter1`, `dynamic_filter2`, `dynamic_filter3`) VALUES
(@project_id, 'All Participants', null, 1, 'ALL', 'ALL', NULL, 0, 0, 0, 0, 1, 'study_id', 'ASC', NULL, NULL, NULL, NULL, NULL, 'RECORD', NULL, NULL, NULL),
(@project_id, 'Participants (age > 80)', @unique_report_name, 2, 'ALL', 'ALL', NULL, 0, 0, 0, 0, 1, 'study_id', 'ASC', NULL, NULL, NULL, NULL, '([age] > 80)', 'EVENT', NULL, NULL, NULL);
-- project dashboards
INSERT INTO `redcap_project_dashboards` (`project_id`, `title`, `body`) VALUES
(@project_id, 'Project Dashboard Example 1', '<p><strong>SMART FUNCTIONS</strong> are aggregate mathematical functions that are applied across ALL records in a project.</p>\r\n<p>Smart Functions include <strong>min</strong>, <strong>max</strong>, <strong>mean</strong>, <strong>median</strong>, <strong>sum</strong>, <strong>stdev</strong>, <strong>count</strong>, and <strong>unique</strong>.</p>\r\n<p>This project contains <strong>[aggregate-count:study_id] records</strong>. The average age of all participants is <strong>[aggregate-mean:age]</strong> (stdev=[aggregate-stdev:age]). The median weight is <strong>[aggregate-median:weight]</strong> (min: [aggregate-min:weight], max: [aggregate-max:weight]).</p>\r\n<hr />\r\n<p><strong>SMART TABLES</strong> display descriptive statistics for fields with each field as a row in the table.</p>\r\n<table style=\"border-collapse: collapse; width: 100%;\" border=\"0\">\r\n<tbody>\r\n<tr>\r\n<td style=\"width: 43.0038%; vertical-align: top;\">\r\n<p>Smart Tables can be displayed with ALL columns by default:</p>\r\n<p>[stats-table:height,weight,race,gender]</p>\r\n</td>\r\n<td style=\"width: 31.3222%; vertical-align: top;\">\r\n<p>Or with only specified columns:</p>\r\n<p>[stats-table:age,weight,height:mean,stdev]</p>\r\n</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<hr />\r\n<p><strong>SMART CHARTS</strong> can be used to display many types of charts for one or more fields in the project.</p>\r\n<table style=\"border-collapse: collapse; width: 100%; height: 68px;\" border=\"0\" cellspacing=\"10\" cellpadding=\"10\">\r\n<tbody>\r\n<tr>\r\n<td style=\"width: 47.4989%; vertical-align: top;\">\r\n<p>Display a <strong>scatter plot</strong> of two fields (x vs y):</p>\r\n<p>[scatter-plot:height,weight]</p>\r\n<p>Add a third field for grouping (by color):</p>\r\n<p>[scatter-plot:height,weight,gender]</p>\r\n</td>\r\n<td style=\"width: 47.4989%; height: 16px; vertical-align: top;\">\r\n<p>Use <strong>line charts</strong> with two fields (x vs y):</p>\r\n<p>[line-chart:height,weight]</p>\r\n<p>Add a third field for grouping (by color):</p>\r\n<p>[line-chart:height,weight,gender]</p>\r\n</td>\r\n</tr>\r\n<tr style=\"height: 52px;\">\r\n<td style=\"width: 47.4989%; height: 52px; vertical-align: top;\">\r\n<p>Display a <strong>bar chart</strong> with a single multiple choice field:</p>\r\n<p>[bar-chart:race]</p>\r\n</td>\r\n<td style=\"width: 47.4989%; height: 52px; vertical-align: top;\">\r\n<p>Display bar charts vertically, and add a second field for grouping:</p>\r\n<p>[bar-chart:race,gender:bar-vertical]</p>\r\n</td>\r\n</tr>\r\n<tr>\r\n<td style=\"width: 47.4989%; vertical-align: top;\">\r\n<p>Display a <strong>pie chart</strong></p>\r\n<p>[pie-chart:race]</p>\r\n</td>\r\n<td style=\"width: 47.4989%; vertical-align: top;\">\r\n<p>Or a <strong>donut chart</strong></p>\r\n<p>[donut-chart:race]</p>\r\n</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<p> </p>'),
(@project_id, 'Project Dashboard Example 2', concat('<p><strong>Use data filtering via Reports or DAGs to limit the data used by Smart Functions, Smart Tables, or Smart Charts:</strong></p>\r\n<p>There are [aggregate-count:study_id] records in the project.</p>\r\n<p>There are [aggregate-count:age:vanderbilt] records assigned to the Vanderbilt data access group.</p>\r\n<p>There are [aggregate-count:study_id:', @unique_report_name, '] records where age > 80.</p>'));
-- Add template
INSERT INTO `redcap_projects_templates` (`project_id`, `title`, `description`, `enabled`, copy_records)
	VALUES (@project_id,  @project_title,  'Example of Project Dashboards, Smart Functions, Smart Tables, & Smart Charts with fifty records included.', 1, 1);