2017051500_m2m.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 M2M 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()
    {
      // add to common tables
        $tableName = "cu_feature";
        $row = $this->fetchRow( "SELECT * FROM cu_feature WHERE feature_code = 'TRNM2M'" );
        if ( !$row ) {
          $singleRow = ["feature_code" => "TRNM2M",
                                  "description" => "Member to member transfer",
                                  "limit_type" => "B",
                                  "category" => null,
                                  "enabled" => true ];
          $table = $this->table( $tableName );
          $table->insert($singleRow)->saveData();
        }

      // 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"] ) );
        $CU = strtoupper( $cu );

        $tableName = "cu_featuremenu";
        $row = $this->fetchRow( "SELECT * FROM cu_featuremenu WHERE feature_code = 'TRNM2M' AND cu='{$CU}'" );
        if ( !$row ) {
          $singleRow = ["feature_code" => "TRNM2M",
                                  "cu" => $CU,
                                  "display_order" => 12,
                                  "parent_item_id" => 32,
                                  "menu_item_type" => "D",
                                  "menu_item_platform" => '["D","M"]',
                                  "menu_item_attr" => '{"display":{"en_US":"M2M Accounts","es_US":""},"fa-icon":"","href":"hcuM2MAccts.prg","hrefUrlQuery":1,"target":0,"hrefExtraParam":""}' ];
          $table = $this->table( $tableName );
          $table->insert($singleRow)->saveData();
        }

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

        // 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}extaccounttemp (
                  id SERIAL PRIMARY KEY,
                  user_id integer NOT NULL,
                  type char(3),
                  display_name varchar(20),
                  status character(1),
                  remote_info varchar
                  )";

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

          // move existing data
          $sql = "INSERT INTO {$cu}extaccounttemp
                    (id, user_id, type, display_name,
                     status, remote_info)
                     SELECT id, user_id, 'EXT' as type, display_name,
                     status, remote_info 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}extaccounttemp" );
          $table->rename( $tableName );

          // add an index so the display name is unique per user (for either type of table usage)
          $sql = "CREATE UNIQUE INDEX {$cu}extaccount_name_key on {$cu}extaccount USING btree ( user_id, display_name );";
          $rows = $this->query( $sql );

          // add some comments
          $sql = "COMMENT ON COLUMN {$cu}extaccount.user_id is 'User who set it up; foreign key to <cu>user.user_id';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.type is 'EXT, M2M';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.display_name is 'Name that displays in Banking';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.status is 'Pending/Active/Inactive flag';";
          $rows = $this->query( $sql );

          $sql = "COMMENT ON COLUMN {$cu}extaccount.remote_info is 'JSON object that holds the remote account information';";
          $rows = $this->query( $sql );

        }
      }
    }

    /* 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}extaccount";
        $hasColumn = $table->hasColumn('type');

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

          // drop the columns that was added
          $table->removeColumn('type')->save();
        }
      }
    }
}