sql/case_sample.mysql

   1 -- /**********************************************************************
   2 -- *
   3 -- * Configuration Data for CiviCase Component
   4 -- * For: Sample Case Types - Housing Support and Adult Day Care Referral
   5 -- *
   6 -- **********************************************************************/
   7
   8 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
   9
  10 -- /*******************************************************
  11 -- *
  12 -- * Case Types
  13 -- *
  14 -- *******************************************************/
  15 SELECT @option_group_id_case_type        := max(id) from civicrm_option_group where name = 'case_type';
  16 SELECT @max_val :=  IF ( value <> 'NULL',max(value) , 0 ) from civicrm_option_value where option_group_id=@option_group_id_case_type;
  17 SELECT @max_wt  :=  IF ( value <> 'NULL',max(weight), 0 ) from civicrm_option_value where option_group_id=@option_group_id_case_type;
  18
  19 INSERT INTO `civicrm_option_value` (  `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
  20 (SELECT @option_group_id_case_type, 'Housing Support',  @max_val + 1, 'housing_support', NULL, 0,  0, @max_wt + 1, 'Help homeless individuals obtain temporary and long-term housing', 0, 0, 1
  21  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'housing_support'));
  22
  23 INSERT INTO `civicrm_option_value` (  `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
  24 (SELECT @option_group_id_case_type, 'Adult Day Care Referral',  @max_val + 2, 'adult_day_care_referral', NULL, 0,  0, @max_wt + 2, 'Arranging adult day care for senior individuals', 0, 0, 1
  25  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'adult_day_care_referral'));
  26
  27
  28 -- /*******************************************************
  29 -- *
  30 -- * Case Status - Set names for Open and Closed
  31 -- *
  32 -- *******************************************************/
  33 SELECT @csgId        := max(id) from civicrm_option_group where name = 'case_status';
  34 UPDATE civicrm_option_value SET name = 'Open' where option_group_id = @csgId AND label = 'Ongoing';
  35 UPDATE civicrm_option_value SET name = 'Closed' where option_group_id = @csgId AND label = 'Resolved';
  36
  37 -- /*******************************************************
  38 -- *
  39 -- * Activity Types
  40 -- *
  41 -- *******************************************************/
  42 SELECT @option_group_id_activity_type        := max(id) from civicrm_option_group where name = 'activity_type';
  43
  44 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
  45
  46 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` )
  47 (SELECT @option_group_id_activity_type, 'Medical evaluation', (SELECT @max_val := @max_val+1), 'Medical evaluation',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  48  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Medical evaluation'));
  49
  50 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` )
  51 (SELECT @option_group_id_activity_type, 'Mental health evaluation', (SELECT @max_val := @max_val+1), 'Mental health evaluation',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  52  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Mental health evaluation'));
  53
  54 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` )
  55 (SELECT @option_group_id_activity_type, 'Secure temporary housing', (SELECT @max_val := @max_val+1), 'Secure temporary housing',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  56  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Secure temporary housing'));
  57
  58 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` )
  59 (SELECT @option_group_id_activity_type, 'Income and benefits stabilization', (SELECT @max_val := @max_val+1), 'Income and benefits stabilization',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  60  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Income and benefits stabilization'));
  61
  62 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` )
  63 (SELECT @option_group_id_activity_type, 'Long-term housing plan', (SELECT @max_val := @max_val+1), 'Long-term housing plan',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  64  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Long-term housing plan'));
  65
  66 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` )
  67 (SELECT @option_group_id_activity_type, 'ADC referral', (SELECT @max_val := @max_val+1), 'ADC referral',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
  68  FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'ADC referral'));