resily Database

Parameters

Name	Type	Mode
organization_id	bigint	IN
old_okr_term_id	bigint	IN
new_okr_term_id	bigint	IN
checkin_summary_id_mapping	USER-DEFINED	IN
key_result_id_mapping	USER-DEFINED	IN

Definition

<<copy_checkin_summary_key_results>>
DECLARE
  old_checkin_summary_key_result RECORD;
  new_key_results RECORD;
  record_count int8 := 0;
BEGIN
  FOR old_checkin_summary_key_result IN
    SELECT checkin_summary_key_results.*
    FROM checkin_summary_key_results
      INNER JOIN checkin_summaries
        ON checkin_summaries.id = checkin_summary_key_results.checkin_summary_id
      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
      INNER JOIN key_results
        ON key_results.id = checkin_summary_key_results.key_result_id
        AND key_results.disabled_at IS NULL
    WHERE checkin_summaries.status = 'PREVIOUS' OR checkin_summaries.status = 'NOW'
  LOOP
    record_count := record_count + 1;
    EXECUTE
      $SQL$
        INSERT INTO checkin_summary_key_results (
          "organization_id",
          "checkin_summary_id",
          "key_result_id",
          "progress_rate",
          "confidence",
          "created_at",
          "updated_at",
          "name",
          "display_order",
          "target_setting"
        ) VALUES ($1, $2, $3, $4, $5, current_timestamp, current_timestamp, $6, $7, $8)
      $SQL$
      USING
        organization_id,
        (checkin_summary_id_mapping -> old_checkin_summary_key_result.checkin_summary_id::text)::int8,
        (key_result_id_mapping -> old_checkin_summary_key_result.key_result_id::text)::int8,
        old_checkin_summary_key_result.progress_rate,
        old_checkin_summary_key_result.confidence,
        old_checkin_summary_key_result.name,
        old_checkin_summary_key_result.display_order,
        old_checkin_summary_key_result.target_setting;
  END LOOP;

-- コピーしたcheckin_summary_key_resultsの件数が0件だった場合、コピーした日を元に新しいcheckin_summary_key_resultsを作成する
  IF record_count = 0 THEN
    FOR new_key_results 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 = new_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
      EXECUTE
        $SQL$
          INSERT INTO checkin_summary_key_results (
            "organization_id",
            "checkin_summary_id",
            "key_result_id",
            "progress_rate",
            "confidence",
            "created_at",
            "updated_at",
            "name",
            "display_order",
            "target_setting"
          ) VALUES ($1, $2, $3, 0, 0, current_timestamp, current_timestamp, $4, $5, $6)
        $SQL$
        USING
          organization_id,
          (checkin_summary_id_mapping -> new_key_results.okr_node_id::text)::int8,
          new_key_results.id,
          new_key_results.name,
          new_key_results.display_order,
          new_key_results.target_setting;
    END LOOP;
  END IF;
END;