2017030100_transhdr.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 TransHdr 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()
    {
      // 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"] ) );

        // check if we already made the change
        $tableName = "{$cu}transhdr";
        $table = $this->table( $tableName );
        $hasColumn = $table->hasColumn('approved_status');

        // test to see if change has already happened
        if ( !$hasColumn ) {
          // announce it
          print "Converting $cu {$tableName}\n";

          // create the SQL for the temp table
          $sql = "CREATE TABLE {$cu}transhdrtemp (
                    id SERIAL PRIMARY KEY,
                    feature_code character varying(10) NOT NULL,
                    effective_date date,
                    posted_by integer,
                    posted_date timestamp without time zone,
                    approved_by integer,
                    approved_date timestamp without time zone,
                    approved_status smallint,
                    processed_by character varying(50),
                    processed_date timestamp without time zone,
                    processed_status smallint,
                    accountnumber character(12),
                    accounttype character(12),
                    deposittype character(1),
                    transactioncode character(2),
                    memo character varying(255)
                  )";

          // create the temp table
          $rows = $this->query( $sql );

          // add some comments
          $sql = "COMMENT ON COLUMN {$cu}transhdrtemp.feature_code is 'Same feature code as menuing'";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}transhdrtemp.approved_status is 'Approved(10) or cancelled(99)'";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}transhdrtemp.processed_status is 'Waiting(0), Approved/in-batch(10), Emailed(20), or cancelled(99)';";
          $rows = $this->query( $sql );

          // move existing data
          $sql = "INSERT INTO {$cu}transhdrtemp
                    (id, feature_code, effective_date,
                     posted_by, posted_date, approved_by, approved_date,
                     accountnumber, accounttype,
                     deposittype, transactioncode, memo)
                     SELECT id, feature_code, effective_date date,
                     posted_by, posted_date, approved_by, approved_date,
                     accountnumber, accounttype,
                     deposittype, transactioncode, memo FROM $tableName";

          // copy the data
          $count = $this->execute( $sql );

          print "$count row(s) copied from existing $tableName";

          // drop the old table
          $this->dropTable( $tableName );

          // rename the temporary table
          $table = $this->table( "{$cu}transhdrtemp" );
          $table->rename( $tableName );
        }
      }
    }

    /* 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()
    {
      // 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"] ) );

        // check if we already made the change
        $tableName = "{$cu}transhdr";
        $table = $this->table( $tableName );
        $hasColumn = $table->hasColumn('approved_status');

        // test to see if table has NOT been reversed yet
        if ( $hasColumn ) {
          // announce it
          print "Reverting $cu {$tableName}\n";

          // change the column back (need to cast to get character changed to integer)
          $sql = "ALTER TABLE $tableName ALTER COLUMN processed_by TYPE integer USING processed_by::integer";
          $rows = $this->query( $sql );

          // drop the columns that were added
          $table->removeColumn('processed_status')
              ->save();
          $table->removeColumn('approved_status')
              ->save();
        }
      }
    }
}