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_checkin_summaries>>
DECLARE
  old_checkin_summary RECORD;
  new_checkin_summary RECORD;
  checkin_summary_id_mapping hstore default '';
  related_okr_node_ids int8[];
  related_okr_node_id int8;
BEGIN
  FOR old_checkin_summary IN
    SELECT checkin_summaries.*
    FROM checkin_summaries
      INNER JOIN okr_nodes
        ON okr_nodes.id = checkin_summaries.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 checkin_summaries.status IN ('PREVIOUS', 'NOW')
  LOOP
    EXECUTE
      $SQL$
        INSERT INTO checkin_summaries
	("organization_id", "okr_node_id", "status", "description", "plain_text", "start_date", "end_date", "created_at", "updated_at")
        VALUES
          ($1, $2, 'PREVIOUS', $3, $4, $5, $6, $7, $8)
        RETURNING *
      $SQL$
      INTO new_checkin_summary
      USING organization_id, (okr_node_id_mapping -> old_checkin_summary.okr_node_id::text)::int8, old_checkin_summary.description, old_checkin_summary.plain_text, old_checkin_summary.start_date, old_checkin_summary.end_date, old_checkin_summary.created_at, old_checkin_summary.updated_at;

-- old_checkin_summaryのidとnew_checkin_summaryのidを紐づける
    checkin_summary_id_mapping := checkin_summary_id_mapping || hstore(old_checkin_summary.id::text, new_checkin_summary.id::text);
  END LOOP;

-- コピー済みの対象になるold_checkin_summaryのokr_node_idを配列にする
  SELECT ARRAY_AGG(okr_node_id)
  INTO related_okr_node_ids
  FROM checkin_summaries
  WHERE status IN ('PREVIOUS', 'NOW')
  AND okr_node_id = ANY(ARRAY(SELECT key::bigint FROM each(okr_node_id_mapping)));

-- コピーしたokr_nodeからcheckin_summariesを作成
  FOREACH related_okr_node_id IN ARRAY akeys(okr_node_id_mapping)
  LOOP
    -- 既にコピー済みのcheckin_summariesは作成しない
    IF related_okr_node_id = ANY(related_okr_node_ids) THEN
      CONTINUE;
    END IF;

EXECUTE
      $SQL$
        INSERT INTO checkin_summaries
          ("organization_id", "okr_node_id", "status", "description", "plain_text", "start_date", "end_date", "created_at", "updated_at")
        VALUES
          ($1, $2, 'PREVIOUS', NULL, NULL, $3, $4, current_timestamp, current_timestamp)
        RETURNING *
      $SQL$
      INTO new_checkin_summary
      USING
        organization_id, (okr_node_id_mapping -> related_okr_node_id::text)::int8,
        date_trunc('week', current_timestamp) - interval '9 hours',
        date_trunc('week', current_timestamp) + interval '7 day' - interval '9 hours 1 second';

-- new_checkin_summaryのokr_node_idとidを紐づける
    checkin_summary_id_mapping := checkin_summary_id_mapping || hstore(new_checkin_summary.okr_node_id::text, new_checkin_summary.id::text);
  END LOOP;

RETURN checkin_summary_id_mapping;
END;