2017121800_addcrossaccountstable.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 AddCrossAccountsTable 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 the new 'CrossAccounts' table for each CU that exists in the current database
    // 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 for existence of file name
       */
      $tableName = "{$cu}crossaccounts";

      $exists = $this->hasTable($tableName);

      if (!$exists) {
        /**
         * Table does not exist --
         * CREATE
         */

        $sql = "
          CREATE TABLE {$tableName} (
            accountnumber character (12) NOT NULL,
            tomember character(12) NOT NULL,
            accounttype character(12) NOT NULL,
            deposittype character (1) NOT NULL,
            description character varying (255),
            misc1 character varying (255)
          );";
        $sqlRs = $this->query($sql);

        /* ADD PRIMARY KEY */
        $sql = "ALTER TABLE ONLY {$tableName} ADD CONSTRAINT {$tableName}_pkey PRIMARY KEY (accountnumber, tomember, accounttype, deposittype);";
        $sqlRs = $this->query($sql);

      }
    }
  }

}