2017113000_combineusersupport.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 CombineUserSupport extends AbstractMigration {

  /*
   * remove the column "stale" from account and loan balance tables
   */
  public function up() {

    $hasTableCUA = $this->hasTable("cuadminallow");
    $hasTableCUP = $this->hasTable("cuadminprogs");

    if ($hasTableCUA) {
      // update cuadmin allow: any users with access to addchange and not usersupport
      // set access to user support.
      $sqlSub = "
        SELECT sua.cu, sua.user_name, sua.program FROM cuadminallow sua 
        WHERE LOWER(program)='usersupport' 
          AND sua.user_name=cua.user_name";
      $sql = "
        INSERT INTO cuadminallow (
          SELECT DISTINCT ON (cua.user_name) cua.cu, cua.user_name, 'userSupport'
          FROM cuadminallow cua 
          WHERE LOWER(program) IN ('addchange', 'usermfa')
            AND NOT EXISTS ($sqlSub))";
      $this->query($sql);

      // delete usermfa, addchange from cuadminallow
      $sql = "
        DELETE FROM cuadminallow 
        WHERE LOWER(program) IN ('addchange', 'usermfa')";
      $this->query($sql);
    }      

    if ($hasTableCUP) {
      // update cuadminprogs, change displaytext of usersupport
      // change displaytext to User Management Screens
      $sql = "
        UPDATE cuadminprogs SET displaytext='User Management Screens' 
        WHERE LOWER(program)='usersupport'";
      $this->query($sql);

      // delete usermfa, addchange from cuadminprogs
      $sql = "
        DELETE FROM cuadminprogs 
        WHERE LOWER(program) IN ('addchange', 'usermfa')";
      $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() {

  }
}

?>