resily Database

Parameters

Name	Type	Mode
organization_id	bigint	IN
old_okr_term_id	bigint	IN
name	character	IN
start_date	date	IN
end_date	date	IN

Definition

<<copy_okr_term>>
DECLARE
  old_okr_term RECORD;
  new_okr_term_id int8;
  okr_node_id_mapping hstore;
  objective_id_mapping hstore;
  key_result_id_mapping hstore;
  checkin_summary_id_mapping hstore;
BEGIN
  -- 古い okr_term を取得
  SELECT *
    INTO old_okr_term
    FROM okr_terms
    WHERE id = old_okr_term_id;

-- # 新しい okr_term を作成
  EXECUTE
    $SQL$
      INSERT INTO okr_terms
        ("organization_id", "name", "start_date", "end_date", "copy_source_id", "created_at", "updated_at")
      VALUES
        ($1, $2, $3, $4, $5, current_timestamp, current_timestamp)
      RETURNING id;
    $SQL$
    INTO new_okr_term_id
    USING organization_id, name, start_date, end_date, old_okr_term.id;

-- # okr_term に紐づく okr_nodes のコピー。新旧のIDのmappingを取得
  SELECT copy_okr_nodes(organization_id, old_okr_term_id, new_okr_term_id)
    INTO okr_node_id_mapping;

-- # okr_nodes に紐づく objectives をコピー。新旧IDのmappingを取得
  SELECT copy_objectives(organization_id, old_okr_term_id, okr_node_id_mapping)
    INTO objective_id_mapping;

-- # okr_nodes に紐づく key_results をコピー。新旧IDのmappingを取得
  SELECT copy_key_results(organization_id, old_okr_term_id, okr_node_id_mapping)
    INTO key_result_id_mapping;

-- # objectives と key_results のマッピングから objective_parent_key_results をコピー
  CALL copy_objective_parent_key_results(organization_id, objective_id_mapping, key_result_id_mapping);

-- # okr_nodes に紐づく group_okr_nodes をコピー
  CALL copy_group_okr_nodes(organization_id, old_okr_term_id, okr_node_id_mapping);

-- # okr_nodes に紐づく checkin_summaries をコピー
  SELECT copy_checkin_summaries(organization_id, old_okr_term_id, okr_node_id_mapping)
    INTO checkin_summary_id_mapping;

-- # okr_nodes に紐づく checkin_summary_key_results をコピー
  CALL copy_checkin_summary_key_results(organization_id, old_okr_term_id, new_okr_term_id, checkin_summary_id_mapping, key_result_id_mapping);

RETURN new_okr_term_id;
END;