resily Database

Parameters

Name	Type	Mode
organization_id	bigint	IN
old_okr_term_id	bigint	IN
okr_node_id_mapping	USER-DEFINED	IN

Definition

<<copy_key_results>>
DECLARE
  old_key_result RECORD;
  new_key_result RECORD;
  new_node_id int8;
  mapping hstore DEFAULT '';
BEGIN
  FOR old_key_result IN
    SELECT key_results.*
    FROM
      key_results
      INNER JOIN okr_nodes
        ON okr_nodes.id = key_results.okr_node_id
        AND okr_nodes.okr_term_id = old_okr_term_id
      INNER JOIN objectives
        ON objectives.okr_node_id = okr_nodes.id
        AND objectives.disabled_at IS NULL
      WHERE key_results.disabled_at IS NULL
  LOOP
    -- key_results のコピー
    EXECUTE
      $SQL$
        INSERT INTO key_results
          ("name", "description", "plain_text", "update_type", "progress_rate", "target_value", "actual_value", "unit", "initial_value", "organization_id", "okr_node_id", "created_at", "updated_at", "display_order", "disabled_at", "weight", "is_auto_aggregate", "target_setting")
        VALUES
          ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
        RETURNING *;
      $SQL$
      INTO new_key_result
      USING old_key_result.name, old_key_result.description, old_key_result.plain_text, old_key_result.update_type, old_key_result.progress_rate, old_key_result.target_value, old_key_result.actual_value, old_key_result.unit, old_key_result.initial_value, organization_id, (okr_node_id_mapping -> old_key_result.okr_node_id::text)::int8, old_key_result.created_at, old_key_result.updated_at, old_key_result.display_order, old_key_result.disabled_at, old_key_result.weight, old_key_result.is_auto_aggregate, old_key_result.target_setting;

-- TODO テストデータがない
    -- TODO GCP上のデータもコピーしたほうが良さそう
    EXECUTE
      $SQL$
        INSERT INTO key_result_attachments
          ("organization_id", "key_result_id", "name", "file_path", "created_at", "updated_at")
        SELECT
          $2, $3, kra.name, kra.file_path, kra.created_at, kra.updated_at
        FROM key_result_attachments AS kra
        WHERE key_result_id = $1;
      $SQL$
      USING old_key_result.id, organization_id, new_key_result.id;

-- key_result_members のコピー
    EXECUTE
      $SQL$
        INSERT INTO key_result_members
          ("organization_id", "key_result_id", "role", "user_id", "created_at", "updated_at")
        SELECT
          $2, $3, krm.role, krm.user_id, krm.created_at, krm.updated_at
        FROM key_result_members AS krm
        WHERE key_result_id = $1;
      $SQL$
      USING old_key_result.id, organization_id, new_key_result.id;

-- key_result id のmappingを作成
    mapping := mapping || hstore(old_key_result.id::text, new_key_result.id::text);
  END LOOP;

RETURN mapping;
END;