2017053100_hcumbrlogintrack.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 HCUMbrLoginTrack extends AbstractMigration
{
    /* This is the process for moving forward. We are re-creating the function
     * drop existing function, create new
     * rename the temporary table.
     */
    public function up()
    {
          // announce it
          print "Converting hcumbrlogintrack\n";

          // create the SQL for the temp table
          $sql = "
CREATE OR REPLACE FUNCTION hcumbrlogintrack(cucode character, member character, mustchange character, pwlastchange timestamp with time zone, product character, usermfaquest character varying, OUT logtime character) RETURNS character
    LANGUAGE plpgsql
    AS $$
  DECLARE
  tz char(25);
  ym char(6);
  retries integer;
  grace integer;
  pwdays integer;
  results record;
  rec_affected int;
  logtime char(20);
  homecuuserstable varchar(24);
  BEGIN
    /*
    * SET CU USERS TABLE VALUE
    */
    homecuuserstable := lower(cucode) || 'user';
    /*
    * ***********
    * Get cuadmin record for given credit union
    */
    SELECT INTO results * FROM cuadmin WHERE cu = cucode;
    IF results.tz is null OR trim(results.tz) = '' THEN
      tz:='US/Mountain';
    ELSIF position('/' IN results.tz) > 0 THEN
      tz:=results.tz;
    ELSE
      tz:='US/' || results.tz;
    END IF;
    IF results.retrylimit = 0 OR results.retrylimit IS NULL THEN
      retries:=5;
    ELSE
      retries:=results.retrylimit;
    END IF;
    IF results.gracelimit = 0 OR results.gracelimit IS NULL THEN
      grace:=5;
    ELSE
      grace:=results.gracelimit;
    END IF;
    IF results.pwdays IS NULL THEN
      pwdays:=0;
    ELSE
      pwdays:=results.pwdays;
    END IF;
    EXECUTE 'set time zone ' || quote_literal(tz);
    logtime:=to_char(now(),'YYYY/MM/DD HH24:MI:SS');
    ym:=to_char(now(),'YYYYMM');
   /*
    * CHECK FOR EXISTENCE OF TABLE IN PG TABLE?
    */
    IF EXISTS (SELECT relname FROM pg_class WHERE relname = quote_ident(homecuuserstable))
    THEN
      IF mustchange='Y'
      THEN
        EXECUTE FORMAT('UPDATE %I '
        || 'SET failedremain=%L, '
        || ' priorlogin=lastlogin, '
        || ' forceremain=(forceremain - 1), '
        || ' lastlogin=%L, '
        || ' challenge_quest_id=%L, '
        || ' mfaquest=%L '
        || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 0, userMfaQuest, member);

      ELSE
        IF pwdays > 0 AND pwlastchange < (now()- \"interval\"(pwdays || ' days')) THEN
          EXECUTE FORMAT('UPDATE %I '
          || 'SET failedremain=%L, '
          || ' priorlogin=lastlogin, '
          || ' lastlogin=%L, '
          || ' forcechange=%L, '
          || ' forceremain=%L, '
          || ' challenge_quest_id=%L, '
          || ' mfaquest=%L '
          || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 'Y', grace, 0, userMfaQuest, member);
        ELSE
          EXECUTE FORMAT('UPDATE %I '
          || 'SET failedremain=%L, '
          || ' priorlogin=lastlogin, '
          || ' lastlogin=%L, '
          || ' challenge_quest_id=%L, '
          || ' mfaquest=%L '
          || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 0, userMfaQuest, member);
        END IF;
      END IF;
    ELSE
    /* ** ERROR -- SHOULDN'T BE HERE. */
    END IF;

    EXECUTE 'set time zone ' || quote_literal('US/Mountain');
END;$$;
                  ";

          // create or update function
          $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()
    {
          // announce it
          print "Converting hcumbrlogintrack\n";

          // create the SQL for the temp table
          $sql = "
CREATE OR REPLACE FUNCTION hcumbrlogintrack(cucode character, member character, mustchange character, pwlastchange timestamp with time zone, product character, usermfaquest character varying, OUT logtime character) RETURNS character
    LANGUAGE plpgsql
    AS $$
  DECLARE
  tz char(25);
  ym char(6);
  retries integer;
  grace integer;
  pwdays integer;
  results record;
  rec_affected int;
  logtime char(20);
  homecuuserstable varchar(24);
  BEGIN
    /*
    * SET CU USERS TABLE VALUE
    */
    homecuuserstable := lower(cucode) || 'user';
    /*
    * ***********
    * Get cuadmin record for given credit union
    */
    SELECT INTO results * FROM cuadmin WHERE cu = cucode;
    IF results.tz is null OR trim(results.tz) = '' THEN
      tz:='US/Mountain';
    ELSIF position('/' IN results.tz) > 0 THEN
      tz:=results.tz;
    ELSE
      tz:='US/' || results.tz;
    END IF;
    IF results.retrylimit = 0 OR results.retrylimit IS NULL THEN
      retries:=5;
    ELSE
      retries:=results.retrylimit;
    END IF;
    IF results.gracelimit = 0 OR results.gracelimit IS NULL THEN
      grace:=5;
    ELSE
      grace:=results.gracelimit;
    END IF;
    IF results.pwdays IS NULL THEN
      pwdays:=0;
    ELSE
      pwdays:=results.pwdays;
    END IF;
    EXECUTE 'set time zone ' || quote_literal(tz);
    logtime:=to_char(now(),'YYYY/MM/DD HH24:MI:SS');
    ym:=to_char(now(),'YYYYMM');
    UPDATE culogtrack SET logins=logins +1
    WHERE cu=cucode AND accountnumber=member AND yearmo=ym AND hcucode=product;
      get diagnostics rec_affected = ROW_COUNT;
    IF rec_affected = 0 THEN
     INSERT INTO culogtrack values (cucode, member, ym, 1, product);
    END IF;
   /*
    * CHECK FOR EXISTENCE OF TABLE IN PG TABLE?
    */
    IF EXISTS (SELECT relname FROM pg_class WHERE relname = quote_ident(homecuuserstable))
    THEN
      IF mustchange='Y'
      THEN
        EXECUTE FORMAT('UPDATE %I '
        || 'SET failedremain=%L, '
        || ' priorlogin=lastlogin, '
        || ' forceremain=(forceremain - 1), '
        || ' lastlogin=%L, '
        || ' challenge_quest_id=%L, '
        || ' mfaquest=%L '
        || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 0, userMfaQuest, member);

      ELSE
        IF pwdays > 0 AND pwlastchange < (now()- \"interval\"(pwdays || ' days')) THEN
          EXECUTE FORMAT('UPDATE %I '
          || 'SET failedremain=%L, '
          || ' priorlogin=lastlogin, '
          || ' lastlogin=%L, '
          || ' forcechange=%L, '
          || ' forceremain=%L, '
          || ' challenge_quest_id=%L, '
          || ' mfaquest=%L '
          || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 'Y', grace, 0, userMfaQuest, member);
        ELSE
          EXECUTE FORMAT('UPDATE %I '
          || 'SET failedremain=%L, '
          || ' priorlogin=lastlogin, '
          || ' lastlogin=%L, '
          || ' challenge_quest_id=%L, '
          || ' mfaquest=%L '
          || 'WHERE user_name = %L;', homecuuserstable, retries, logtime, 0, userMfaQuest, member);
        END IF;
      END IF;
    ELSE
    /* ** ERROR -- SHOULDN'T BE HERE. */
    END IF;

    EXECUTE 'set time zone ' || quote_literal('US/Mountain');
END;$$;
                  ";

          // create or update function
          $rows = $this->query( $sql );

    }
}