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

   1 {* file to handle db changes in 4.4.5 during upgrade *}
   2 -- CRM-14191
   3 SELECT @option_group_id_batch_status   := max(id) from civicrm_option_group where name = 'batch_status';
   4 SELECT @weight := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status;
   5
   6 UPDATE civicrm_option_value
   7 SET value = (Select @weight := @weight +1),
   8 weight = @weight
   9 WHERE option_group_id = @option_group_id_batch_status AND name IN ('Data Entry', 'Reopened', 'Exported') AND value = 0 ORDER BY id;
  10
  11 SELECT @option_group_id_batch_modes := max(id) from civicrm_option_group where name = 'batch_mode';
  12 SELECT @weights := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_modes;
  13
  14 UPDATE civicrm_option_value
  15 SET value = (Select @weights := @weights +1),
  16 weight = @weights
  17 WHERE option_group_id = @option_group_id_batch_modes AND name IN ('Manual Batch', 'Automatic Batch') AND value = 0;
  18
  19 SELECT @manual_mode_id := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_modes AND name = 'Manual Batch';
  20 UPDATE civicrm_batch SET mode_id = @manual_mode_id WHERE (mode_id IS NULL OR mode_id = 0) AND type_id IS NULL;
  21
  22 SELECT @data_entry_status_id := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status AND name = 'Data Entry';
  23 UPDATE civicrm_batch SET status_id = @data_entry_status_id WHERE status_id = 3 AND type_id IS NOT NULL;
  24
  25 SELECT @exported_status_id := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status AND name = 'Exported';
  26 UPDATE civicrm_navigation SET url = CONCAT('civicrm/financial/financialbatches?reset=1&batchStatus=', @exported_status_id) WHERE name = 'Exported Batches';
  27
  28 -- update status_id to Exported
  29 SELECT @export_activity_type := max(value) FROM civicrm_option_value cov
  30 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
  31 WHERE cog.name = 'activity_type' AND cov.name = 'Export Accounting Batch';
  32
  33 UPDATE civicrm_batch cb
  34 INNER JOIN civicrm_activity ca ON ca.source_record_id = cb.id
  35 SET cb.status_id = @exported_status_id
  36 WHERE cb.status_id = 0 AND ca.activity_type_id = @export_activity_type;