2018032900_auditMember.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 AuditMember extends AbstractMigration {
    public function up() {
      // get the list of credit unions
      $cuList = $this->fetchAll('SELECT cu FROM cuadmin');


      foreach ($cuList as $cuRow) {
        $cu = strtolower(trim($cuRow["cu"]));
        $indexName = "${cu}idx_audituser";

        // check if we already made the change
        $tableName = "{$cu}audituser";

        if ($this->hasTable($tableName)) { // Not all Credit Unions have this table.  Webonly Credit Unions don't have a lot of the CU-specific tables.
          $table = $this->table( $tableName );

          $hasColumn = $table->hasColumn('accountnumber');

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

            // Add column
            $this->query("alter table $tableName add column accountnumber varchar(12) default ''");

            // Drop primary key (doesn't have account_number in it.)
            $this->query("alter table $tableName drop constraint $indexName");

            // Add primary key with account_number in it.
            $this->query("alter table $tableName add constraint $indexName primary key (user_id, accountnumber, auditdate, auditaction)");

            // Add accountnumber to previous e-statement start/stop audits.
            $this->query("update ${cu}audituser u set accountnumber = a.accountnumber from
                (select user_id, accountnumber as prevaccount, auditdate, auditaction, auditrecbefore::json->'memberacct'->0->>'accountnumber' as accountnumber from ${cu}audituser
                where auditfulldesc = 'E-Statement Update' and auditsrccode_context in ('hcuStatement.prg', 'hcuStatementStop.prg') and nullif(trim(accountnumber), '') is null) a
                where u.user_id = a.user_id and u.accountnumber = a.prevaccount and u.auditdate = a.auditdate and u.auditaction = a.auditaction");
          }
        } else {
          print "Skip $cu which doesn't have the user audit table.\n";
        }

      }
    } // End "up" function.

    public function down() {
      // get the list of credit unions
      $cuList = $this->fetchAll('SELECT cu FROM cuadmin');


      foreach ($cuList as $cuRow) {
        $cu = strtolower(trim($cuRow["cu"]));
        $indexName = "${cu}idx_audituser";

        // check if we already made the change
        $tableName = "{$cu}audituser";

        if ($this->hasTable($tableName)) { // Not all Credit Unions have this table.  Webonly Credit Unions don't have a lot of the CU-specific tables.
          $table = $this->table( $tableName );
          $hasColumn = $table->hasColumn('accountnumber');

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

            // Drop primary key
            $this->query("alter table $tableName drop constraint $indexName");

            // Add primary key without account_number in it.
            $this->query("alter table $tableName add constraint $indexName primary key (user_id, auditdate, auditaction)");

            // drop column
            $this->query("alter table $tableName drop column accountnumber");
          }
        } else {
          print "Skip $cu which doesn't have the user audit table.\n";
        }

      }
    } // End "down" function
}