2018090400_keepAccessUseAllowed.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 KeepAccessUseAllowed extends AbstractMigration {
    public function up() {

      $cuList = $this->fetchAll('select cu from cuadmin');
      for ($i = 0; $i < count($cuList); $i ++) {
        $cu = strtolower( trim( $cuList[$i]["cu"] ) );
        $tableRights = $this->hasTable("${cu}memberacctrights");

        if ($tableRights) {
          $sql = "insert into ${cu}memberacctrights (user_id, accountnumber, whichright, allowed, platform)
                  select a.user_id, a.accountnumber, 'ACCESS', false, null from (select row_number() over (partition by user_id, accountnumber) as rown, user_id, accountnumber
                  from ${cu}useraccounts) a
                  left join ${cu}memberacctrights b on a.user_id = b.user_id and a.accountnumber = b.accountnumber and b.whichright = 'ACCESS'
                  where a.rown = 1 and b.user_id is null";

          $this->query($sql);
        }
      }
    }
}