2017033000_scheduledtxn.php

<?php 
/* Notes:
 * 1. Anything complicated needs to be done with SQL.
 * 2. Always have a check to know if the migration needs to occur (up or down).
 * 3. Use up() and down(), not change(), because of the SQL
 */
use Phinx\Migration\AbstractMigration;

class ScheduledTxn extends AbstractMigration {

  /* This is the process for moving forward. We are re-creating the table so if there is
   * existing data will need to create a temporary table, copy the data, drop the old table,
   * rename the temporary table.
   */
  public function up() {

    // create scheduledtxn table if it doesn't exist
    $exists = $this->hasTable("cu_scheduledtxn");
    print_r($exists, true);

    if (!$exists) {
      $sql = "
        CREATE TABLE cu_scheduledtxn (
          id integer,
          cu varchar(10) NOT NULL,
          name varchar(100),
          feature_code varchar(10) NOT NULL,
          user_id integer NOT NULL,
          create_date timestamp without time zone,
          approved_by integer,
          approved_date timestamp without time zone,
          last_edit_by integer,
          last_edit_date date,
          start_date date,
          end_date date,
          next_trigger_date date,
          interval_count smallint,
          failure_count smallint,
          status char(1),
          repeating_parameters varchar,
          txn_data varchar
        );";
      $sqlRs = $this->query($sql);

      // create sequence
      $sql = "CREATE SEQUENCE cu_scheduledtxn_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1";
      $sqlRs = $this->query($sql);

      // create sequence onwership
      $sql = "ALTER SEQUENCE cu_scheduledtxn_id_seq OWNED BY cu_scheduledtxn.id";
      $sqlRs = $this->query($sql);

      // set default value for id column
      $sql = "ALTER TABLE ONLY cu_scheduledtxn ALTER COLUMN id SET DEFAULT nextval('cu_scheduledtxn_id_seq'::regclass)";
      $sqlRs = $this->query($sql);

      // set primary key constraint on id column
      $sql = "ALTER TABLE ONLY cu_scheduledtxn ADD CONSTRAINT cu_scheduledtxn_pkey PRIMARY KEY (id)";
      $sqlRs = $this->query($sql);

      // get the list of credit unions
      $cuList = $this->fetchAll('SELECT cu FROM cuadmin');
      for ( $i = 0; $i < count( $cuList ); $i++ ) {
        $cu = strtolower( trim( $cuList[$i]["cu"] ) );

        // drop cu memorize hdr/dtl tables
        if ($this->hasTable("{$cu}memorizedhdr")) {
          $this->dropTable("{$cu}memorizedhdr");
        }
        if ($this->hasTable("{$cu}memorizeddtl")) {
          $this->dropTable("{$cu}memorizeddtl");
        }
      }
    }
  }

  /* To execute this you need to run the following command:
   * php vendor/bin/phinx rollback (using correct path to phinx)
   * Or, to roll back to a specific time:
   * php vendor/bin/phinx rollback -t 20151123211207
   */
  public function down() {
    // drop cu_scheduledtxn table
    $this->dropTable("cu_scheduledtxn");

    // get the list of credit unions
    $cuList = $this->fetchAll('SELECT cu FROM cuadmin');
    for ( $i = 0; $i < count( $cuList ); $i++ ) {
      $cu = strtolower( trim( $cuList[$i]["cu"] ) );

      // drop cu memorize hdr/dtl tables
      if ($this->hasTable("{$cu}memorizedhdr")) {
        $this->dropTable("{$cu}memorizedhdr");
      }
      if ($this->hasTable("{$cu}memorizeddtl")) {
        $this->dropTable("{$cu}memorizeddtl");
      }
    }
  }
}

?>